UFN_MAILING_GETNAMEFORMATS

CREATE function dbo.UFN_MAILING_GETNAMEFORMATS(
    @CONSTITUENTID uniqueidentifier,  
    @INDIVIDUALADDRESSEES xml,  
    @INDIVIDUALSALUTATIONS xml)
returns table  
as   
return   
(  
with 
ADDRESSEE_CTE as (   
  select
  NAMEFORMAT.CONSTITUENTID,
  NAMEFORMAT.ID NAMEFORMATID,
   T.c.value('SEQUENCE[1]', 'int') SEQUENCE  
  from dbo.NAMEFORMAT
   inner join @INDIVIDUALADDRESSEES.nodes('/INDIVIDUALADDRESSEES/ITEM') T(c) on
   (  
    (T.c.value('ADDRESSEEFORMATISPRIMARY[1]', 'bit') = 1 and NAMEFORMAT.PRIMARYADDRESSEE = 1)  
    or  
    (NAMEFORMAT.NAMEFORMATTYPECODEID = T.c.value('NAMEFORMATTYPECODEID[1]', 'uniqueidentifier'))  
   )
   where NAMEFORMAT.CONSTITUENTID = @CONSTITUENTID
)
,   
SALUTATION_CTE as (   
  select
  NAMEFORMAT.CONSTITUENTID,
  NAMEFORMAT.ID NAMEFORMATID,
   T.c.value('SEQUENCE[1]', 'int') SEQUENCE  
  from dbo.NAMEFORMAT
   inner join @INDIVIDUALSALUTATIONS.nodes('/INDIVIDUALSALUTATIONS/ITEM') T(c) on 
   (  
    (T.c.value('SALUTATIONFORMATISPRIMARY[1]', 'bit') = 1 and NAMEFORMAT.PRIMARYSALUTATION = 1)  
    or  
    (NAMEFORMAT.NAMEFORMATTYPECODEID = T.c.value('NAMEFORMATTYPECODEID[1]', 'uniqueidentifier'))  
   )
   where NAMEFORMAT.CONSTITUENTID = @CONSTITUENTID  
)
select   
 CONSTITUENT.ID CONSTITUENTID,   
 RELATIONSHIP.RECIPROCALCONSTITUENTID SPOUSEID,  
 ADDRESSEE.NAMEFORMATID ADDRESSEE_NAMEFORMATID,  
 SALUTATION.NAMEFORMATID SALUTATION_NAMEFORMATID
from dbo.CONSTITUENT  
left outer join dbo.RELATIONSHIP   
 on CONSTITUENT.ID = RELATIONSHIP.RELATIONSHIPCONSTITUENTID and RELATIONSHIP.ISSPOUSE = 1  
outer apply   
 (select top 1 ADDRESSEE_CTE.CONSTITUENTID, ADDRESSEE_CTE.NAMEFORMATID from ADDRESSEE_CTE where ADDRESSEE_CTE.CONSTITUENTID = CONSTITUENT.ID order by ADDRESSEE_CTE.SEQUENCE) ADDRESSEE  
outer apply   
 (select top 1 SALUTATION_CTE.CONSTITUENTID, SALUTATION_CTE.NAMEFORMATID from SALUTATION_CTE where SALUTATION_CTE.CONSTITUENTID = CONSTITUENT.ID order by SALUTATION_CTE.SEQUENCE) SALUTATION  
where CONSTITUENT.ID = @CONSTITUENTID
)