UFN_QUERY_PLANNEDGIFTACKNOWLEDGEMENTOUTPUT

                CREATE function [dbo].[UFN_QUERY_PLANNEDGIFTACKNOWLEDGEMENTOUTPUT]
                    (
                        @NAMEFORMATPARAMETERID uniqueidentifier,
                        @ADDRESSPROCESSINGOPTIONID uniqueidentifier,
                        @SEASONALDATE datetime = null
                    )
                returns table
                as
                return
                (
                select 
                    PLANNEDGIFT.ID as PLANNEDGIFTID,
                    PLANNEDGIFT.CONSTITUENTID as CONSTITUENTID, 
                    NF.NAME as CONSTITUENTNAME,
                    A.ADDRESSEE,    
                    A.SALUTATION,
                    A.CONTACT as CONTACTADDRESSEE,
                    A.POSITION,
                    A.ADDRESSBLOCK,
                    A.CITY,
                    A.STATE,
                    A.POSTCODE,
                    A.COUNTRY,
                    -- PLANNEDGIFT INFORMATION ***********,

                    PLANNEDGIFT.VEHICLECODE as VEHICLECODE,
                    PLANNEDGIFT.VEHICLE as VEHICLE,
                    PLANNEDGIFT.EXPECTEDMATURITY as EXPECTEDMATURITY,
                    PLANNEDGIFT.ISREVOCABLE as ISREVOCABLE,
                    case when PLANNEDGIFT.ISANONYMOUS = 0 then '' else 'anonymous' end as ISANONYMOUS,
                    PLANNEDGIFT.TRANSACTIONNETPRESENTVALUE as NETPRESENTVALUE,
                    PLANNEDGIFT.NETPRESENTVALUEDATE as NETPRESENTVALUEDATE,
                    PLANNEDGIFT.TRANSACTIONREMAINDERVALUE as REMAINDERVALUE,
                    PLANNEDGIFT.REMAINDERVALUEDATE as REMAINDERVALUEDATE,
                    PLANNEDGIFT.TRANSACTIONRECOGNITIONAMOUNT as RECOGNITIONAMOUNT,
                    PLANNEDGIFT.TRANSACTIONGIFTAMOUNT as GIFTAMOUNT,
                    PLANNEDGIFT.GIFTDATE as GIFTDATE,
                    -- PAYOUT INFORMATION ***********,

                    PLANNEDGIFT.PAYOUTRATE as PAYOUTRATE,
                    PLANNEDGIFT.TRANSACTIONPAYOUTAMOUNT as PAYOUTAMOUNT,
                    PLANNEDGIFT.PAYMENTFREQUENCYCODE as PAYMENTFREQUENCYCODE,
                    PLANNEDGIFT.PAYMENTPERIODSTART as PAYMENTPERIODSTART,
                    PLANNEDGIFT.PAYMENTPERIODEND as PAYMENTPERIODEND,
                    PLANNEDGIFT.DISCOUNTRATE as DISCOUNTRATE,
                    -- DESIGNATION 1 INFORMATION ***********,

                    (select DESIGNATIONNAME from dbo.UFN_PLANNEDGIFT_GETORDEREDDESIGNATIONS_2(PLANNEDGIFT.ID) where ROWNUMBER = 1) as DESIGNATION1NAME,
                    (select TRANSACTIONAMOUNT from dbo.UFN_PLANNEDGIFT_GETORDEREDDESIGNATIONS_2(PLANNEDGIFT.ID) where ROWNUMBER = 1) as DESIGNATION1AMOUNT,
                    -- DESIGNATION 2 INFORMATION ***********,

                    (select DESIGNATIONNAME from dbo.UFN_PLANNEDGIFT_GETORDEREDDESIGNATIONS_2(PLANNEDGIFT.ID) where ROWNUMBER = 2) as DESIGNATION2NAME,
                    (select TRANSACTIONAMOUNT from dbo.UFN_PLANNEDGIFT_GETORDEREDDESIGNATIONS_2(PLANNEDGIFT.ID) where ROWNUMBER = 2) as DESIGNATION2AMOUNT,
                    -- BENEFICIARY 1 INFORMATION ***********,

                    (select BENEFICIARYNAME from dbo.UFN_PLANNEDGIFT_GETORDEREDBENEFICIARIES(PLANNEDGIFT.ID) where ROWNUMBER = 1) as BENEFICIARY1NAME,
                    --(select BENEFICIARYRELATIONSHIP from dbo.UFN_PLANNEDGIFT_GETORDEREDBENEFICIARIES(PLANNEDGIFT.ID) where ROWNUMBER = 1) as BENEFICIARY1RELATIONSHIP,

                    (select BENEFICIARYAGE from dbo.UFN_PLANNEDGIFT_GETORDEREDBENEFICIARIES(PLANNEDGIFT.ID) where ROWNUMBER = 1) as BENEFICIARY1AGE,
                    (select BENEFICIARYDEPENDENCY from dbo.UFN_PLANNEDGIFT_GETORDEREDBENEFICIARIES(PLANNEDGIFT.ID) where ROWNUMBER = 1) as BENEFICIARY1DEPENDENCY,
                    (select BENEFICIARYTYPE from dbo.UFN_PLANNEDGIFT_GETORDEREDBENEFICIARIES(PLANNEDGIFT.ID) where ROWNUMBER = 1) as BENEFICIARY1TYPE,
                    --(select CONSTITUENTISBENEFICIARY from dbo.UFN_PLANNEDGIFT_GETORDEREDBENEFICIARIES(PLANNEDGIFT.ID) where ROWNUMBER = 1) as CONSTITUENTISBENEFICIARY1,

                    -- BENEFICIARY 2 INFORMATION ***********,

                    (select BENEFICIARYNAME from dbo.UFN_PLANNEDGIFT_GETORDEREDBENEFICIARIES(PLANNEDGIFT.ID) where ROWNUMBER = 2) as BENEFICIARY2NAME,
                    --(select BENEFICIARYRELATIONSHIP from dbo.UFN_PLANNEDGIFT_GETORDEREDBENEFICIARIES(PLANNEDGIFT.ID) where ROWNUMBER = 2) as BENEFICIARY2RELATIONSHIP,

                    (select BENEFICIARYAGE from dbo.UFN_PLANNEDGIFT_GETORDEREDBENEFICIARIES(PLANNEDGIFT.ID) where ROWNUMBER = 2) as BENEFICIARY2AGE,
                    (select BENEFICIARYDEPENDENCY from dbo.UFN_PLANNEDGIFT_GETORDEREDBENEFICIARIES(PLANNEDGIFT.ID) where ROWNUMBER = 2) as BENEFICIARY2DEPENDENCY,
                    (select BENEFICIARYTYPE from dbo.UFN_PLANNEDGIFT_GETORDEREDBENEFICIARIES(PLANNEDGIFT.ID) where ROWNUMBER = 2) as BENEFICIARY2TYPE,
                    --(select CONSTITUENTISBENEFICIARY from dbo.UFN_PLANNEDGIFT_GETORDEREDBENEFICIARIES(PLANNEDGIFT.ID) where ROWNUMBER = 2) as CONSTITUENTISBENEFICIARY2,

                    -- POOLED INCOME FUND INFORMATION ***********,

                    PLANNEDGIFT.POOLEDINCOMEFUNDCODEID as POOLEDINCOMEFUNDCODEID,
                    PLANNEDGIFT.POOLEDINCOMEFUNDTOTALUNITS as POOLEDINCOMEFUNDTOTALUNITS,
                    PLANNEDGIFT.POOLEDINCOMEFUNDPERCENT as POOLEDINCOMEFUNDPERCENT,
                    -- ASSET INFORMATION ***********,

                    dbo.UFN_ASSETTYPECODE_GETDESCRIPTION(ASSET.ASSETTYPECODEID) as ASSETTYPE,
                    ASSET.DESCRIPTION as ASSETDESCRIPTION,
                    ASSET.TRANSACTIONVALUE as ASSETVALUE,
                    ASSET.TRANSACTIONCOSTBASIS as ASSETCOSTBASIS,
                    dbo.UFN_ASSETVALUATIONMETHODCODE_GETDESCRIPTION(ASSET.ASSETVALUATIONMETHODCODEID) as ASSETVALUATIONMETHODCODE,
                    ASSET.VALUATIONSOURCE as ASSETVALUATIONSOURCE,
                    -- RELATIONSHIP 1 INFORMATION ***********,

                    (select RELATIONSHIPNAME from dbo.UFN_PLANNEDGIFT_GETORDEREDRELATIONSHIPS(PLANNEDGIFT.ID) where ROWNUMBER = 1) as RELATIONSHIP1NAME,
                    (select RELATIONSHIPTYPE from dbo.UFN_PLANNEDGIFT_GETORDEREDRELATIONSHIPS(PLANNEDGIFT.ID) where ROWNUMBER = 1) as RELATIONSHIP1TYPE,
                    -- RELATIONSHIP 2 INFORMATION ***********,

                    (select RELATIONSHIPNAME from dbo.UFN_PLANNEDGIFT_GETORDEREDRELATIONSHIPS(PLANNEDGIFT.ID) where ROWNUMBER = 2) as RELATIONSHIP2NAME,
                    (select RELATIONSHIPTYPE from dbo.UFN_PLANNEDGIFT_GETORDEREDRELATIONSHIPS(PLANNEDGIFT.ID) where ROWNUMBER = 2) as RELATIONSHIP2TYPE,
                    (select POOLEDINCOMEFUNDCODE.DESCRIPTION from POOLEDINCOMEFUNDCODE where PLANNEDGIFT.POOLEDINCOMEFUNDCODEID = POOLEDINCOMEFUNDCODE.ID) [POOLEDINCOMEFUND],
                    PLANNEDGIFTREVENUE.REVENUEID,
                    REVENUE.DONOTRECEIPT,
                    PLANNEDGIFT.TRANSACTIONCURRENCYID as PLANNEDGIFTTRANSACTIONCURRENCYID,
                    ASSET.TRANSACTIONCURRENCYID as ASSETTRANSACTIONCURRENCYID

                from 
                    dbo.PLANNEDGIFT
                    left join dbo.PROSPECTPLAN on PLANNEDGIFT.PROSPECTPLANID = PROSPECTPLAN.ID
                    inner join dbo.UFN_CONTACTPREFERENCES_GETFORMATS(@NAMEFORMATPARAMETERID,@ADDRESSPROCESSINGOPTIONID,6,null,@SEASONALDATE,0) as A on PLANNEDGIFT.CONSTITUENTID = A.CONSTITUENTID
                    left join dbo.PLANNEDGIFTASSET as ASSET on PLANNEDGIFT.ID = ASSET.PLANNEDGIFTID
                    cross apply dbo.UFN_CONSTITUENT_DISPLAYNAME(PLANNEDGIFT.CONSTITUENTID) NF
                    left join dbo.PLANNEDGIFTREVENUE on PLANNEDGIFT.ID = PLANNEDGIFTREVENUE.ID
                    left join dbo.REVENUE on PLANNEDGIFTREVENUE.REVENUEID = REVENUE.ID
            )