USP_DATAFORMTEMPLATE_EDITLOAD_MATCHINGGIFTPAYMENT

Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	IN
@DATALOADED	bit	INOUT
@TSLONG	bigint	INOUT
@CONSTITUENTID	uniqueidentifier	INOUT
@CONSTITUENTNAME	nvarchar(255)	INOUT
@AMOUNT	money	INOUT
@TRANSACTIONCURRENCYID	uniqueidentifier	INOUT
@PAYMENTDATE	date	INOUT
@BASECURRENCYID	uniqueidentifier	INOUT
@BASEEXCHANGERATEID	uniqueidentifier	INOUT
@REVENUESTREAMS	xml	INOUT
@REMOVEDREVENUESTREAMS	xml	INOUT
@MATCHINGGIFTCLAIMSPLITS	xml	INOUT
Definition

Copy


                CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_MATCHINGGIFTPAYMENT
                (
                    @ID uniqueidentifier,
                    @DATALOADED bit = 0 output,
                    @TSLONG bigint = 0 output,
                    @CONSTITUENTID uniqueidentifier = null output,
                    @CONSTITUENTNAME nvarchar(255) = null output,
                    @AMOUNT money = null output,
                    @TRANSACTIONCURRENCYID uniqueidentifier = null output,
                    @PAYMENTDATE date = null output,
                    @BASECURRENCYID uniqueidentifier = null output,
                    @BASEEXCHANGERATEID uniqueidentifier = null output,
                    @REVENUESTREAMS xml = null output,
                    @REMOVEDREVENUESTREAMS xml = null output,
                    @MATCHINGGIFTCLAIMSPLITS xml = null output
                )
                as 
                    set nocount on;

                    set @DATALOADED = 0;

                    select
                        @DATALOADED = 1,
                        @AMOUNT = sum(FINANCIALTRANSACTIONLINEITEM.TRANSACTIONAMOUNT)
                    from
                        dbo.FINANCIALTRANSACTION
                        inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
                        inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
                        --Get only applications paying a matching gift claim by inner joining to dbo.INSTALLMENTSPLITPAYMENT.

                        -- Unapplied matching gift payments would not have an installment split payment row because there is

                        -- no commitment being paid.

                        inner join dbo.INSTALLMENTSPLITPAYMENT on REVENUESPLIT_EXT.ID = INSTALLMENTSPLITPAYMENT.PAYMENTID
                    where
                        FINANCIALTRANSACTION.ID = @ID
                        and FINANCIALTRANSACTION.TYPECODE = 0 --Payment

                        and REVENUESPLIT_EXT.TYPECODE = 0 --Gift

                        and REVENUESPLIT_EXT.APPLICATIONCODE = 7; --Matching gift


                    if @DATALOADED = 1
                        select
                            @TSLONG = FINANCIALTRANSACTION.TSLONG,
                            @CONSTITUENTID = FINANCIALTRANSACTION.CONSTITUENTID,
                            @CONSTITUENTNAME = NF.NAME,
                            @TRANSACTIONCURRENCYID = FINANCIALTRANSACTION.TRANSACTIONCURRENCYID,
                            @PAYMENTDATE = FINANCIALTRANSACTION.DATE,
                            @BASECURRENCYID = CURRENCYSET.BASECURRENCYID,
                            @BASEEXCHANGERATEID = FINANCIALTRANSACTION.BASEEXCHANGERATEID
                        from
                            dbo.FINANCIALTRANSACTION
                            inner join dbo.PDACCOUNTSYSTEM on FINANCIALTRANSACTION.PDACCOUNTSYSTEMID = PDACCOUNTSYSTEM.ID
                            inner join dbo.CURRENCYSET on PDACCOUNTSYSTEM.CURRENCYSETID = CURRENCYSET.ID
                            outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(FINANCIALTRANSACTION.CONSTITUENTID) as NF
                        where
                            FINANCIALTRANSACTION.ID = @ID;

                    return 0;