UFN_MAILING_GETCONTACTNAMEFORMATS

create function dbo.UFN_MAILING_GETCONTACTNAMEFORMATS  
    (
    @CONTACTID uniqueidentifier,
    @ORGADDRESSEES xml,  
    @ORGSALUTATIONS xml)
returns table  
as   
return   
(  
with 
ORG_ADDRESSEE_CTE as (
  select
  NAMEFORMAT.CONSTITUENTID,
  NAMEFORMAT.ID NAMEFORMATID,
   T.c.value('SEQUENCE[1]', 'int') SEQUENCE  
  from dbo.NAMEFORMAT
   inner join @ORGADDRESSEES.nodes('/ORGANIZATIONADDRESSEES/ITEM') T(c) on
   (  
    (T.c.value('ADDRESSEEFORMATISPRIMARY[1]', 'bit') = 1 and NAMEFORMAT.PRIMARYADDRESSEE = 1)  
    or  
    (NAMEFORMAT.NAMEFORMATTYPECODEID = T.c.value('NAMEFORMATTYPECODEID[1]', 'uniqueidentifier'))  
   )
),   
ORG_SALUTATION_CTE as (   
  select
  NAMEFORMAT.CONSTITUENTID,
  NAMEFORMAT.ID NAMEFORMATID,
   T.c.value('SEQUENCE[1]', 'int') SEQUENCE  
  from dbo.NAMEFORMAT
   inner join @ORGSALUTATIONS.nodes('/ORGANIZATIONSALUTATIONS/ITEM') T(c) on 
   (  
    (T.c.value('SALUTATIONFORMATISPRIMARY[1]', 'bit') = 1 and NAMEFORMAT.PRIMARYSALUTATION = 1)  
    or  
    (NAMEFORMAT.NAMEFORMATTYPECODEID = T.c.value('NAMEFORMATTYPECODEID[1]', 'uniqueidentifier'))  
   )  
)
select   
 ORG_ADDRESSEE.NAMEFORMATID ADDRESSEE_NAMEFORMATID,  
 ORG_SALUTATION.NAMEFORMATID SALUTATION_NAMEFORMATID
from dbo.CONSTITUENT  
outer apply   
 (select top 1 ORG_ADDRESSEE_CTE.CONSTITUENTID, ORG_ADDRESSEE_CTE.NAMEFORMATID from ORG_ADDRESSEE_CTE where ORG_ADDRESSEE_CTE.CONSTITUENTID = @CONTACTID order by ORG_ADDRESSEE_CTE.SEQUENCE) ORG_ADDRESSEE  
outer apply   
 (select top 1 ORG_SALUTATION_CTE.CONSTITUENTID, ORG_SALUTATION_CTE.NAMEFORMATID from ORG_SALUTATION_CTE where ORG_SALUTATION_CTE.CONSTITUENTID = @CONTACTID order by ORG_SALUTATION_CTE.SEQUENCE) ORG_SALUTATION  
where CONSTITUENT.ID = @CONTACTID
)