USP_DATAFORMTEMPLATE_ADD_GRANTAWARDPAYMENT

Stored procedure used to save a grant award payment.

Parameters

Parameter	Parameter Type	Mode
@APPLICATIONID	uniqueidentifier	IN
@ID	uniqueidentifier	INOUT
@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
@CURRENTAPPUSERID	uniqueidentifier	IN
@SEPAMANDATEID	uniqueidentifier	IN
@TRANSACTIONID	uniqueidentifier	IN
@MERCHANTACCOUNTID	uniqueidentifier	IN
@VENDORID	nvarchar(50)	IN

Definition

Copy



            CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_GRANTAWARDPAYMENT
                (
                    @APPLICATIONID uniqueidentifier,
                    @ID uniqueidentifier = null output,
                    @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,
                    @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;

                --TODO WHAT DO WE DO WITH THIS NOW

                declare @UNAPPLIEDMATCHINGGIFTSPLITS xml

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

                if @POSTSTATUSCODE is null
                    set @POSTSTATUSCODE = 1;

                set @CURRENTDATE = GetDate();

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

                    -- 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;

                    --Set currency parameters for backwards compatibility

                    if @TRANSACTIONCURRENCYID is null
                        set @TRANSACTIONCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();

                    --Multicurrency - Retrieve base currency from the account system's currency set.

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

                    if @CURRENCYSETID is null
                        set @CURRENCYSETID = dbo.UFN_CURRENCYSET_GETAPPUSERDEFAULTCURRENCYSET();

                    declare @BASECURRENCYID uniqueidentifier;
                    select @BASECURRENCYID = BASECURRENCYID from dbo.CURRENCYSET where ID = @CURRENCYSETID;

                    -- 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;

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

                    declare @AMOUNTPAID money
                    set @AMOUNTPAID = @AMOUNT;

                    declare @CREATEDSPLITS xml
                    exec dbo.USP_PLEDGE_ADDPAYMENT
                        @REVENUEID = @ID,
                        @APPLICATIONID = @APPLICATIONID,
                        @APPLIEDAMOUNT = @AMOUNT,
                        @CONSTITUENTID = @CONSTITUENTID,
                        @DATE = @DATE,
                        @UNAPPLIEDMATCHINGGIFTSPLITS = @UNAPPLIEDMATCHINGGIFTSPLITS,
                        @APPLICATIONTYPE = 8,--Grant award

                        @AMOUNTPAID = @AMOUNTPAID output,
                        @CREATIONDATE = @CURRENTDATE,
                        @CHANGEAGENTID = @CHANGEAGENTID,
                        @CREATEDSPLITS = @CREATEDSPLITS output

                    -- Copy declines gift aid and is covenant from the pledge to the pledge payment


                    declare @SPLITSDECLININGGIFTAID xml
                    set @SPLITSDECLININGGIFTAID = (    select
                                                        T.c.value('(ID)[1]','uniqueidentifier') AS 'REVENUESPLITID'
                                                    from @CREATEDSPLITS.nodes('/SPLITS/ITEM') T(c)
                                                    inner join dbo.REVENUESPLITGIFTAID on T.c.value('(SOURCEREVENUESPLITID)[1]','uniqueidentifier') = REVENUESPLITGIFTAID.ID
                                                    where REVENUESPLITGIFTAID.DECLINESGIFTAID = 1
                                                    for xml raw('ITEM'),type,elements,root('SPLITSDECLININGGIFTAID'),BINARY BASE64)

                    declare @COVENANTGIFTSPLITS xml
                    set @COVENANTGIFTSPLITS = (    select
                                                        T.c.value('(ID)[1]','uniqueidentifier') AS 'REVENUESPLITID'
                                                    from @CREATEDSPLITS.nodes('/SPLITS/ITEM') T(c)
                                                    inner join dbo.REVENUESPLITGIFTAID on T.c.value('(SOURCEREVENUESPLITID)[1]','uniqueidentifier') = REVENUESPLITGIFTAID.ID
                                                    where REVENUESPLITGIFTAID.ISCOVENANT = 1
                                                    for xml raw('ITEM'),type,elements,root('COVENANTGIFTSPLITS'),BINARY BASE64)

                    exec dbo.USP_GIFTAIDREVENUESPLIT_ADDUPDATESPLITS @ID, @APPEALID, @PAYMENTMETHODCODE, @CREDITTYPECODEID, @CHANGEAGENTID, @DATE, 0, @SPLITSDECLININGGIFTAID, @COVENANTGIFTSPLITS; --revenue transaction type code for payment is 0


                    if @AMOUNT < (select sum(TRANSACTIONAMOUNT) from dbo.REVENUESPLIT where REVENUEID = @ID)
                        raiserror('BBERR_AMOUNTLESSTHANAPPLIEDAMOUNT', 13, 1);

                    if @AMOUNT <> (select sum(TRANSACTIONAMOUNT) from dbo.REVENUESPLIT where REVENUEID = @ID)
                        raiserror('BBERR_ALLMONEYNOTAPPLIED', 13, 1);

                    if (select count(*) from dbo.REVENUESPLIT where REVENUEID = @ID) = 0
                        raiserror('BBERR_NOAPPLICATIONS', 13, 1);                

                    --Save the GL distributions

                    if @POSTSTATUSCODE <> 2
                        exec dbo.USP_SAVE_REVENUEGLDISTRIBUTION @ID, @CHANGEAGENTID, @CURRENTDATE;
                    -- Add gift fees

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

                begin catch
                    exec dbo.USP_RAISE_ERROR;

                    return 1;
                end catch

                return 0;