UFN_MAILING_GETMEMBERNAMEFORMATS

create function dbo.UFN_MAILING_GETMEMBERNAMEFORMATS(
    @MEMBERID uniqueidentifier,
    @GROUPADDRESSEES xml,  
    @GROUPSALUTATIONS xml)
returns table  
as   
return   
(  
with 
GROUP_ADDRESSEE_CTE as (
  select
  NAMEFORMAT.CONSTITUENTID,
  NAMEFORMAT.ID NAMEFORMATID,
   T.c.value('SEQUENCE[1]', 'int') SEQUENCE  
  from dbo.NAMEFORMAT
   inner join @GROUPADDRESSEES.nodes('/GROUPADDRESSEES/ITEM') T(c) on
   (  
    (T.c.value('ADDRESSEEFORMATISPRIMARY[1]', 'bit') = 1 and NAMEFORMAT.PRIMARYADDRESSEE = 1)  
    or  
    (NAMEFORMAT.NAMEFORMATTYPECODEID = T.c.value('NAMEFORMATTYPECODEID[1]', 'uniqueidentifier'))  
   )
),   
GROUP_SALUTATION_CTE as (   
  select
  NAMEFORMAT.CONSTITUENTID,
  NAMEFORMAT.ID NAMEFORMATID,
   T.c.value('SEQUENCE[1]', 'int') SEQUENCE  
  from dbo.NAMEFORMAT
   inner join @GROUPSALUTATIONS.nodes('/GROUPSALUTATIONS/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   
 GROUP_ADDRESSEE.NAMEFORMATID ADDRESSEE_NAMEFORMATID,  
 GROUP_SALUTATION.NAMEFORMATID SALUTATION_NAMEFORMATID
from dbo.CONSTITUENT CONSTITUENT  
outer apply   
 (select top 1 GROUP_ADDRESSEE_CTE.CONSTITUENTID, GROUP_ADDRESSEE_CTE.NAMEFORMATID from GROUP_ADDRESSEE_CTE where GROUP_ADDRESSEE_CTE.CONSTITUENTID = @MEMBERID order by GROUP_ADDRESSEE_CTE.SEQUENCE) GROUP_ADDRESSEE  
outer apply   
 (select top 1 GROUP_SALUTATION_CTE.CONSTITUENTID, GROUP_SALUTATION_CTE.NAMEFORMATID from GROUP_SALUTATION_CTE where GROUP_SALUTATION_CTE.CONSTITUENTID = @MEMBERID order by GROUP_SALUTATION_CTE.SEQUENCE) GROUP_SALUTATION  
where CONSTITUENT.ID = @MEMBERID
)