USP_DATAFORMTEMPLATE_ADD_PLANNEDGIFTPAYMENT

Stored procedure used to save payment on the planned gift payment add form.
Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	INOUT
@PLANNEDGIFTID	uniqueidentifier	IN
@CHANGEAGENTID	uniqueidentifier	IN
@CONSTITUENTID	uniqueidentifier	IN
@DATE	datetime	IN
@AMOUNT	money	IN
@PAYMENTMETHODCODE	tinyint	IN
@CHECKDATE	UDT_FUZZYDATE	IN
@CHECKNUMBER	nvarchar(20)	IN
@REFERENCEDATE	UDT_FUZZYDATE	IN
@REFERENCENUMBER	nvarchar(20)	IN
@CARDHOLDERNAME	nvarchar(255)	IN
@CREDITCARDNUMBER	nvarchar(4)	IN
@CREDITTYPECODEID	uniqueidentifier	IN
@AUTHORIZATIONCODE	nvarchar(20)	IN
@EXPIRESON	UDT_FUZZYDATE	IN
@ISSUER	nvarchar(100)	IN
@NUMBEROFUNITS	decimal(20, 3)	IN
@SYMBOL	nvarchar(25)	IN
@MEDIANPRICE	decimal(19, 4)	IN
@GIFTINKINDSUBTYPECODEID	uniqueidentifier	IN
@PROPERTYSUBTYPECODEID	uniqueidentifier	IN
@RECEIPTAMOUNT	money	IN
@CONSTITUENTACCOUNTID	uniqueidentifier	IN
@POSTSTATUSCODE	tinyint	IN
@POSTDATE	datetime	IN
@FINDERNUMBER	bigint	IN
@SOURCECODE	nvarchar(50)	IN
@APPEALID	uniqueidentifier	IN
@TRIBUTEID	uniqueidentifier	IN
@BENEFITS	xml	IN
@BENEFITSWAIVED	bit	IN
@GIVENANONYMOUSLY	bit	IN
@MAILINGID	uniqueidentifier	IN
@LETTERCODEID	uniqueidentifier	IN
@CHANNELCODEID	uniqueidentifier	IN
@DONOTACKNOWLEDGE	bit	IN
@DONOTRECEIPT	bit	IN
@OTHERPAYMENTMETHODCODEID	uniqueidentifier	IN
@REFERENCE	nvarchar(255)	IN
@LOWPRICE	decimal(19, 4)	IN
@HIGHPRICE	decimal(19, 4)	IN
@DIRECTDEBITRESULTCODE	nvarchar(10)	IN
@GIFTINKINDITEMNAME	nvarchar(100)	IN
@GIFTINKINDDISPOSITIONCODE	tinyint	IN
@GIFTINKINDNUMBEROFUNITS	int	IN
@GIFTINKINDFAIRMARKETVALUE	money	IN
@PERCENTAGEBENEFITS	xml	IN
@PDACCOUNTSYSTEMID	uniqueidentifier	IN
@TRANSACTIONCURRENCYID	uniqueidentifier	IN
@BASEEXCHANGERATEID	uniqueidentifier	IN
@EXCHANGERATE	decimal(20, 8)	IN
@RECOGNITIONAMOUNT	money	IN
@CURRENTAPPUSERID	uniqueidentifier	IN
@SEPAMANDATEID	uniqueidentifier	IN
@TRANSACTIONID	uniqueidentifier	IN
@MERCHANTACCOUNTID	uniqueidentifier	IN
@VENDORID	nvarchar(50)	IN
Definition

Copy


            CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_PLANNEDGIFTPAYMENT
            (
                @ID uniqueidentifier = null output,
                @PLANNEDGIFTID uniqueidentifier,
                @CHANGEAGENTID uniqueidentifier,
                @CONSTITUENTID uniqueidentifier,                    
                @DATE datetime,
                @AMOUNT money = 0,
                @PAYMENTMETHODCODE tinyint = 1,
                @CHECKDATE dbo.UDT_FUZZYDATE = '00000000',
                @CHECKNUMBER nvarchar(20) = '',
                @REFERENCEDATE dbo.UDT_FUZZYDATE = '00000000',
                @REFERENCENUMBER nvarchar(20) = '',
                @CARDHOLDERNAME nvarchar(255) = '',
                @CREDITCARDNUMBER nvarchar(4) = '',
                @CREDITTYPECODEID uniqueidentifier = null,
                @AUTHORIZATIONCODE nvarchar(20) = '',
                @EXPIRESON dbo.UDT_FUZZYDATE = '00000000',
                @ISSUER nvarchar(100) = '',
                @NUMBEROFUNITS decimal(20,3) = 0,
                @SYMBOL nvarchar(25) = '',
                @MEDIANPRICE decimal(19,4) = 0,
                @GIFTINKINDSUBTYPECODEID uniqueidentifier = null,
                @PROPERTYSUBTYPECODEID uniqueidentifier = null,
                @RECEIPTAMOUNT money = 0,
                @CONSTITUENTACCOUNTID uniqueidentifier = null,
                @POSTSTATUSCODE tinyint = 1,
                @POSTDATE datetime = null,
                @FINDERNUMBER bigint = null,
                @SOURCECODE nvarchar(50) = null,
                @APPEALID uniqueidentifier = null,
                @TRIBUTEID uniqueidentifier = null,
                @BENEFITS xml = null,
                @BENEFITSWAIVED bit = 0,
                @GIVENANONYMOUSLY bit = null,
                @MAILINGID uniqueidentifier = null,
                @LETTERCODEID uniqueidentifier = null,
                @CHANNELCODEID uniqueidentifier = null,
                @DONOTACKNOWLEDGE bit = 0,
                @DONOTRECEIPT bit = 0,
                @OTHERPAYMENTMETHODCODEID uniqueidentifier = null,
                @REFERENCE nvarchar(255) = null,
                @LOWPRICE decimal(19,4) = 0,
                @HIGHPRICE decimal(19,4) = 0,
                @DIRECTDEBITRESULTCODE nvarchar(10) = '',
                @GIFTINKINDITEMNAME nvarchar(100) = '',
                @GIFTINKINDDISPOSITIONCODE tinyint = 0,
                @GIFTINKINDNUMBEROFUNITS int = 0,
                @GIFTINKINDFAIRMARKETVALUE money = 0,
                @PERCENTAGEBENEFITS xml = null,
                @PDACCOUNTSYSTEMID uniqueidentifier = null,
                @TRANSACTIONCURRENCYID uniqueidentifier = null,
                @BASEEXCHANGERATEID uniqueidentifier = null,
                @EXCHANGERATE decimal(20,8) = null,
                @RECOGNITIONAMOUNT money = null,
                @CURRENTAPPUSERID uniqueidentifier = null,
                @SEPAMANDATEID uniqueidentifier = null,
                @TRANSACTIONID uniqueidentifier = null,
                @MERCHANTACCOUNTID uniqueidentifier = null,
                @VENDORID nvarchar(50) = ''
            )
            as
            set nocount on;

            if @CHANGEAGENTID is null
                exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

            declare @CURRENTDATE datetime;

            if @ID is null
                set @ID = newid();

            set @CURRENTDATE = GetDate();


            if @PDACCOUNTSYSTEMID is null
                set @PDACCOUNTSYSTEMID = '4B121C2C-CCE6-440D-894C-EA0DEF80D50B'

            --Multicurrency - RobertDi 6/1/10 - Retrieve base currency from the account system's currency set.

            declare @CURRENCYSETID uniqueidentifier
            select 
                @CURRENCYSETID = CURRENCYSETID
            from 
                dbo.PDACCOUNTSYSTEM
            where 
                ID = @PDACCOUNTSYSTEMID

            declare @BASECURRENCYID uniqueidentifier;
            select 
                @BASECURRENCYID = BASECURRENCYID
            from 
                dbo.CURRENCYSET
            where 
                ID = coalesce(@CURRENCYSETID,dbo.UFN_CURRENCYSET_GETAPPUSERDEFAULTCURRENCYSET())


            begin try
                -- Check GL business rule for this account system and set to 'Do not post' if needed.

                if dbo.UFN_PDACCOUNTSYSTEM_ALLOWGLDISTRIBUTIONS(@PDACCOUNTSYSTEMID) = 0
                begin
                  set @POSTSTATUSCODE = 2  -- Do not post

                  set @POSTDATE = null
                end
                else
                  set @POSTDATE = @DATE;

                -- Don't add the planned gift as revenue twice.

                declare @REVENUEEXISTS bit = 0;
                select
                    @REVENUEEXISTS = 1 
                from dbo.PLANNEDGIFTREVENUESPLIT where PLANNEDGIFTID = @PLANNEDGIFTID;
                if @REVENUEEXISTS = 1
                    raiserror('This planned gift has already been added as revenue.',13,1)

                -- Ensure the transaction currency of the payment matches that of the planned gift.

                declare @TRANSACTIONCURRENCYMATCHES bit = 0;
                select
                    @TRANSACTIONCURRENCYMATCHES = 1
                from dbo.PLANNEDGIFT
                where ID = @PLANNEDGIFTID and TRANSACTIONCURRENCYID = @TRANSACTIONCURRENCYID
                if @TRANSACTIONCURRENCYMATCHES <> 1
                    raiserror('TRANSACTIONCURRENCYMUSTMATCHPLANNEDGIFT', 13, 1)

                -- Ensure that we can add a payment of this transaction currency to the account system.

                if not exists(
                    select 1
                    from dbo.CURRENCYSETTRANSACTIONCURRENCY
                        inner join dbo.PDACCOUNTSYSTEM on PDACCOUNTSYSTEM.CURRENCYSETID = CURRENCYSETTRANSACTIONCURRENCY.CURRENCYSETID
                    where PDACCOUNTSYSTEM.ID = @PDACCOUNTSYSTEMID
                        and CURRENCYSETTRANSACTIONCURRENCY.CURRENCYID = @TRANSACTIONCURRENCYID
                )
                begin
                    raiserror('TRANSACTIONCURRENCYINVALIDFORACCOUNTSYSTEM',13,1)
                end


                exec dbo.USP_PAYMENT_ADDBASE @ID output, @CHANGEAGENTID, @CURRENTDATE, @CONSTITUENTID,
                                        @DATE, @AMOUNT, @PAYMENTMETHODCODE, @CHECKDATE, @CHECKNUMBER,
                                        @REFERENCEDATE, @REFERENCENUMBER, @CARDHOLDERNAME, @CREDITCARDNUMBER,
                                        @CREDITTYPECODEID, @AUTHORIZATIONCODE, @EXPIRESON, @ISSUER,
                                        @NUMBEROFUNITS, @SYMBOL, @MEDIANPRICE, @GIFTINKINDSUBTYPECODEID,
                                        @PROPERTYSUBTYPECODEID, @RECEIPTAMOUNT, @CONSTITUENTACCOUNTID,
                                        @POSTSTATUSCODE, @POSTDATE, @FINDERNUMBER,
                                        @SOURCECODE, @APPEALID, @BENEFITS, @BENEFITSWAIVED, @GIVENANONYMOUSLY, 
                                        @MAILINGID, @CHANNELCODEID, @DONOTACKNOWLEDGE,@DONOTRECEIPT,
                                        '', @OTHERPAYMENTMETHODCODEID, @REFERENCE, @TRIBUTEID, @LETTERCODEID,
                                        @DIRECTDEBITRESULTCODE, @LOWPRICE, @HIGHPRICE, @GIFTINKINDITEMNAME, 
                                        @GIFTINKINDDISPOSITIONCODE, @GIFTINKINDNUMBEROFUNITS, 
                                        @GIFTINKINDFAIRMARKETVALUE, @PERCENTAGEBENEFITS, @TRANSACTIONCURRENCYID,
                                        @BASECURRENCYID, @BASEEXCHANGERATEID, @EXCHANGERATE, @CURRENTAPPUSERID,
                                        @SEPAMANDATEID, @TRANSACTIONID, @MERCHANTACCOUNTID, @VENDORID;

                declare @PLANNEDGIFTDESIGNATIONS xml
                set @PLANNEDGIFTDESIGNATIONS =
                (
                    select
                        ID,
                        TRANSACTIONAMOUNT as AMOUNT
                    from dbo.PLANNEDGIFTDESIGNATION
                    where PLANNEDGIFTID = @PLANNEDGIFTID
                    for xml raw('ITEM'),type,elements,root('AMOUNTSTOPRORATE'),binary base64
                )

                declare @SOURCEAMOUNT money;
                declare @DECIMALDIGITS tinyint;
                select
                    @SOURCEAMOUNT = case
                                        when dbo.UFN_PLANNEDGIFT_DESIGNATIONTRANSACTIONAMOUNT(PLANNEDGIFT.ID)>0
                                            then dbo.UFN_PLANNEDGIFT_DESIGNATIONTRANSACTIONAMOUNT(PLANNEDGIFT.ID)
                                        else
                                            PLANNEDGIFT.TRANSACTIONEXPECTEDGIFTAMOUNT
                                    end,
                    @DECIMALDIGITS = CURRENCY.DECIMALDIGITS
                from
                    dbo.PLANNEDGIFT
                left join
                    dbo.CURRENCY on CURRENCY.ID = PLANNEDGIFT.TRANSACTIONCURRENCYID
                where
                    PLANNEDGIFT.ID = @PLANNEDGIFTID;


                declare @SPLITS xml;

                -- Build splits xml for easier processing by the multicurrency splits conversion function.

                -- UFN_SPLITS_PRORATEAMOUNTS does its calculations in the transaction currency, giving us

                -- transaction amounts we can then convert to base and organization amounts.

                set @SPLITS = (
                    select
                        @ID [REVENUEID],
                        PLANNEDGIFTDESIGNATION.DESIGNATIONID [DESIGNATIONID],
                        PRORATEDDESIGNATIONS.AMOUNT [AMOUNT],
                        0 [TYPECODE],            -- Gift

                        0 [APPLICATIONCODE]        -- Designation

                    from dbo.UFN_SPLITS_PRORATEAMOUNTS(@SOURCEAMOUNT, @AMOUNT, @DECIMALDIGITS, @PLANNEDGIFTDESIGNATIONS) PRORATEDDESIGNATIONS
                        inner join dbo.PLANNEDGIFTDESIGNATION on PLANNEDGIFTDESIGNATION.ID = PRORATEDDESIGNATIONS.ID
                    for xml raw('ITEM'),type,elements,root('SPLITS'),BINARY BASE64
                );

                declare @ORGANIZATIONCURRENCYID uniqueidentifier;
                declare @ORGANIZATIONEXCHANGERATEID uniqueidentifier;
                set @ORGANIZATIONCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
                set @ORGANIZATIONEXCHANGERATEID = dbo.UFN_CURRENCYEXCHANGERATE_GETLATESTORGANIZATIONRATE(@BASECURRENCYID, @ORGANIZATIONCURRENCYID, @CURRENTDATE, null, @TRANSACTIONCURRENCYID);

                --Multicurrency - RobertDi 6/1/10 - Process the splits xml to calculate the base and organization amounts and place them in proper nodes.

                set @SPLITS = dbo.UFN_REVENUESPLIT_CONVERTAMOUNTSINXML(@SPLITS,@BASECURRENCYID,@ORGANIZATIONEXCHANGERATEID,@TRANSACTIONCURRENCYID,@BASEEXCHANGERATEID);
                exec dbo.USP_REVENUE_GETSPLITS_2_ADDFROMXML @ID, @SPLITS, @CHANGEAGENTID, @CURRENTDATE;


                exec dbo.USP_PAYMENT_ADDGIFTFEES @ID, @CONSTITUENTID, @CHANGEAGENTID, @CURRENTDATE;

                insert into dbo.PLANNEDGIFTREVENUESPLIT (PLANNEDGIFTID, REVENUESPLITID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                select @PLANNEDGIFTID, REVENUESPLIT.ID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
                from dbo.REVENUESPLIT where REVENUEID = @ID

                -- Create recognitions

                exec dbo.USP_REVENUE_CREATERECOGNITIONS_WITHAMOUNT @ID, @RECOGNITIONAMOUNT, @CHANGEAGENTID, @CURRENTDATE;

                -- Default campaigns from the planned gift

                insert into dbo.REVENUESPLITCAMPAIGN (REVENUESPLITID, CAMPAIGNID, CAMPAIGNSUBPRIORITYID, DATEADDED, DATECHANGED, ADDEDBYID, CHANGEDBYID)
                select
                    REVENUESPLIT.ID,
                    PLANNEDGIFTDESIGNATIONCAMPAIGN.CAMPAIGNID,
                    PLANNEDGIFTDESIGNATIONCAMPAIGN.CAMPAIGNSUBPRIORITYID,
                    @CURRENTDATE, 
                    @CURRENTDATE,
                    @CHANGEAGENTID,
                    @CHANGEAGENTID
                from dbo.REVENUESPLIT
                inner join dbo.PLANNEDGIFTDESIGNATION on REVENUESPLIT.DESIGNATIONID = PLANNEDGIFTDESIGNATION.DESIGNATIONID
                inner join dbo.PLANNEDGIFTDESIGNATIONCAMPAIGN on PLANNEDGIFTDESIGNATION.ID = PLANNEDGIFTDESIGNATIONCAMPAIGN.PLANNEDGIFTDESIGNATIONID
                where
                    REVENUESPLIT.REVENUEID = @ID and 
                    PLANNEDGIFTDESIGNATION.PLANNEDGIFTID = @PLANNEDGIFTID


                exec dbo.USP_PDACCOUNTSYSTEM_LINKTOREVENUE
                    @ID = @ID,
                    @PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID,
                    @CHANGEDATE = @CURRENTDATE,
                    @CHANGEAGENTID = @CHANGEAGENTID    

                if @POSTSTATUSCODE <> 2
                begin
                  -- Save the GL distributions

                  exec dbo.USP_SAVE_REVENUEGLDISTRIBUTION @ID, @CHANGEAGENTID, @CURRENTDATE

                  -- save any benefit distributions                

                  exec dbo.USP_SAVE_PAYMENT_BENEFITGLDISTRIBUTION @ID, @CHANGEAGENTID, @CURRENTDATE;                
                end

                exec dbo.USP_PLANNEDGIFTREVENUE_DEFAULTSOLICITORS @PLANNEDGIFTID, @ID, @CHANGEAGENTID, @CURRENTDATE
            end try

            begin catch
                exec dbo.USP_RAISE_ERROR;

                return 1;
            end catch

            return 0;