UFN_PLANNEDGIFTACKNOWLEDGEMENT_NAMEFORMATS

create function dbo.UFN_PLANNEDGIFTACKNOWLEDGEMENT_NAMEFORMATS(@PLANNEDGIFTACKNOWLEDGEMENTPROCESSID uniqueidentifier)
returns @NAMEFORMAT table
    (
     CONSTITUENTID uniqueidentifier NOT NULL,
     ADDRESSEE nvarchar(700),
     SALUTATION nvarchar(700),
     CONTACT nvarchar(700),
     POSITION nvarchar(50)
    )                
as
begin
    declare @ADDRESSEEFORMATID uniqueidentifier;
    declare @ALTADDRESSEEFORMATID uniqueidentifier;
    declare    @ADDRESSEEFUNCTIONID uniqueidentifier;
    declare @SALUTATIONFORMATID uniqueidentifier;
    declare @ALTSALUTATIONFORMATID uniqueidentifier;
    declare @SALUTATIONFUNCTIONID uniqueidentifier;
    declare @CONTACTADDRESSEEFORMATID uniqueidentifier;
    declare @ALTCONTACTADDRESSEEFORMATID uniqueidentifier;
    declare @CONTACTADDRESSEEFUNCTIONID uniqueidentifier;
    declare @ORGSALUTATIONCODE tinyint;
    declare @CONTACTSALUTATIONFORMATID uniqueidentifier;
    declare @ALTCONTACTSALUTATIONFORMATID uniqueidentifier;
    declare @CONTACTSALUTATIONFUNCTIONID uniqueidentifier;
    declare @CONTACTSALUTATIONOPTIONCODE bit;
    declare @CUSTOMNAME nvarchar(100);

    if exists(select ID from dbo.PLANNEDGIFTACKNOWLEDGEMENTPROCESSNAMEFORMAT where PLANNEDGIFTACKNOWLEDGEMENTPROCESSID = @PLANNEDGIFTACKNOWLEDGEMENTPROCESSID)
        begin
            select
                @ADDRESSEEFORMATID = ADDRESSEEFORMATID,
                @ALTADDRESSEEFORMATID = ALTADDRESSEEFORMATID,
                @ADDRESSEEFUNCTIONID = ADDRESSEEFUNCTIONID,
                @SALUTATIONFORMATID = SALUTATIONFORMATID,
                @ALTSALUTATIONFORMATID = ALTSALUTATIONFORMATID,
                @SALUTATIONFUNCTIONID = SALUTATIONFUNCTIONID,
                @CONTACTADDRESSEEFORMATID = CONTACTADDRESSEEFORMATID,
                @ALTCONTACTADDRESSEEFORMATID = ALTCONTACTADDRESSEEFORMATID,
                @CONTACTADDRESSEEFUNCTIONID = CONTACTADDRESSEEFUNCTIONID,
                @ORGSALUTATIONCODE = ORGSALUTATIONCODE,
                @CONTACTSALUTATIONFORMATID = CONTACTSALUTATIONFORMATID,
                @ALTCONTACTSALUTATIONFORMATID = ALTCONTACTSALUTATIONFORMATID,
                @CONTACTSALUTATIONFUNCTIONID = CONTACTSALUTATIONFUNCTIONID,
                @CONTACTSALUTATIONOPTIONCODE = CONTACTSALUTATIONOPTIONCODE,
                @CUSTOMNAME = CUSTOMNAME
            from
                dbo.PLANNEDGIFTACKNOWLEDGEMENTPROCESSNAMEFORMAT
            where
                PLANNEDGIFTACKNOWLEDGEMENTPROCESSID = @PLANNEDGIFTACKNOWLEDGEMENTPROCESSID; 
        end
    else
        begin
            select
                @ADDRESSEEFORMATID = ADDRESSEEFORMATID,
                @ALTADDRESSEEFORMATID = ALTADDRESSEEFORMATID,
                @ADDRESSEEFUNCTIONID = ADDRESSEEFUNCTIONID,
                @SALUTATIONFORMATID = SALUTATIONFORMATID,
                @ALTSALUTATIONFORMATID = ALTSALUTATIONFORMATID,
                @SALUTATIONFUNCTIONID = SALUTATIONFUNCTIONID,
                @CONTACTADDRESSEEFORMATID = CONTACTADDRESSEEFORMATID,
                @ALTCONTACTADDRESSEEFORMATID = ALTCONTACTADDRESSEEFORMATID,
                @CONTACTADDRESSEEFUNCTIONID = CONTACTADDRESSEEFUNCTIONID,
                @ORGSALUTATIONCODE = ORGSALUTATIONCODE,
                @CONTACTSALUTATIONFORMATID = CONTACTSALUTATIONFORMATID,
                @ALTCONTACTSALUTATIONFORMATID = ALTCONTACTSALUTATIONFORMATID,
                @CONTACTSALUTATIONFUNCTIONID = CONTACTSALUTATIONFUNCTIONID,
                @CONTACTSALUTATIONOPTIONCODE = CONTACTSALUTATIONOPTIONCODE,
                @CUSTOMNAME = CUSTOMNAME
            from
                dbo.NAMEFORMATPARAMETER
        end

    insert @NAMEFORMAT
    select 
        CONSTITUENT.ID,
        --ADDRESSEE

        case when CONSTITUENT.ISORGANIZATION = 1 then
            CONSTITUENT.NAME
        else
            case when @ADDRESSEEFORMATID is null then
                dbo.UFN_NAMEFORMAT_FROMID(@ADDRESSEEFUNCTIONID, CONSTITUENT.ID)
            else
                coalesce((select FORMATTEDNAME from dbo.NAMEFORMAT where NAMEFORMAT.CONSTITUENTID = CONSTITUENT.ID and NAMEFORMAT.NAMEFORMATTYPECODEID = @ADDRESSEEFORMATID),
                         (select FORMATTEDNAME from dbo.NAMEFORMAT where NAMEFORMAT.CONSTITUENTID = CONSTITUENT.ID and NAMEFORMAT.NAMEFORMATTYPECODEID = @ALTADDRESSEEFORMATID),
                        CONSTITUENT.NAME)            
            end
        end,    
        --SALUTATION

        case when CONSTITUENT.ISORGANIZATION = 1 then
                --process salutation option

                case when @ORGSALUTATIONCODE = 0 then --use organization name

                    CONSTITUENT.NAME
                when @ORGSALUTATIONCODE = 1 then -- use contact name

                    coalesce((select top (1)
                                case when @CONTACTSALUTATIONFORMATID is null then
                                    dbo.UFN_NAMEFORMAT_FROMID(@CONTACTSALUTATIONFUNCTIONID, CONTACT.ID)
                                else 
                                    coalesce((select FORMATTEDNAME from dbo.NAMEFORMAT where NAMEFORMAT.CONSTITUENTID = CONTACT.ID and NAMEFORMAT.NAMEFORMATTYPECODEID = @CONTACTSALUTATIONFORMATID),
                                             (select FORMATTEDNAME from dbo.NAMEFORMAT where NAMEFORMAT.CONSTITUENTID = CONTACT.ID and NAMEFORMAT.NAMEFORMATTYPECODEID = @ALTCONTACTSALUTATIONFORMATID),
                                                CONTACT.NAME)
                                end
                            from dbo.RELATIONSHIP                        
                            inner join dbo.CONSTITUENT CONTACT on RELATIONSHIP.RECIPROCALCONSTITUENTID = CONTACT.ID
                            where RELATIONSHIP.ISPRIMARYCONTACT = 1
                                and RELATIONSHIP.RELATIONSHIPCONSTITUENTID = CONSTITUENT.ID),
                            @CUSTOMNAME, CONSTITUENT.NAME)
                else    --use custom name

                    @CUSTOMNAME
                end
        else
            case when @SALUTATIONFORMATID is null then
                dbo.UFN_NAMEFORMAT_FROMID(@SALUTATIONFUNCTIONID, CONSTITUENT.ID)
            else
                coalesce((select FORMATTEDNAME from dbo.NAMEFORMAT where NAMEFORMAT.CONSTITUENTID = CONSTITUENT.ID and NAMEFORMAT.NAMEFORMATTYPECODEID = @SALUTATIONFORMATID),
                         (select FORMATTEDNAME from dbo.NAMEFORMAT where NAMEFORMAT.CONSTITUENTID = CONSTITUENT.ID and NAMEFORMAT.NAMEFORMATTYPECODEID = @ALTSALUTATIONFORMATID),
                            CONSTITUENT.NAME)            
            end
        end,
        --CONTACT

        case when CONSTITUENT.ISORGANIZATION = 1 then 
            (select top (1) 
                case when @CONTACTADDRESSEEFORMATID is null then
                    dbo.UFN_NAMEFORMAT_FROMID(@CONTACTADDRESSEEFUNCTIONID, CONTACT.ID)
                else
                    coalesce((select FORMATTEDNAME from dbo.NAMEFORMAT where NAMEFORMAT.CONSTITUENTID = CONTACT.ID and NAMEFORMAT.NAMEFORMATTYPECODEID = @CONTACTADDRESSEEFORMATID),
                             (select FORMATTEDNAME from dbo.NAMEFORMAT where NAMEFORMAT.CONSTITUENTID = CONTACT.ID and NAMEFORMAT.NAMEFORMATTYPECODEID = @ALTCONTACTADDRESSEEFORMATID),
                                CONTACT.NAME)
                end
            from dbo.RELATIONSHIP                        
            inner join dbo.CONSTITUENT CONTACT on RELATIONSHIP.RECIPROCALCONSTITUENTID = CONTACT.ID
            where RELATIONSHIP.ISPRIMARYCONTACT = 1
                and RELATIONSHIP.RELATIONSHIPCONSTITUENTID = CONSTITUENT.ID)
        else
            null
        end,
        --POSITION

        case when CONSTITUENT.ISORGANIZATION = 1 then
            (select top (1) RELATIONSHIP.POSITION
            from dbo.RELATIONSHIP                        
            inner join dbo.CONSTITUENT CONTACT on RELATIONSHIP.RECIPROCALCONSTITUENTID = CONTACT.ID
            where RELATIONSHIP.ISPRIMARYCONTACT = 1
                and RELATIONSHIP.RELATIONSHIPCONSTITUENTID = CONSTITUENT.ID)
        else
            null
        end as POSITION
    from dbo.CONSTITUENT;

    return
end