USP_DATAFORMTEMPLATE_EDITLOAD_MATCHINGGIFTPAYMENTADJUST

                CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_MATCHINGGIFTPAYMENTADJUST
                (
                    @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,
                    @PAYMENTMETHODCODE tinyint = null output,
                    @ADJUSTMENTDATE datetime = null output,
                    @ADJUSTMENTPOSTDATE datetime = null output,
                    @ADJUSTMENTREASON nvarchar(300) = null output,
                    @ADJUSTMENTREASONCODEID uniqueidentifier = null output,
                    @ADJUSTMENTPOSTSTATUSCODE tinyint = null output,
                    @PENDINGADJUSTMENTCODE tinyint = null output,
                    @POSTDATE datetime = null output,
                    @ADJUSTMENTID uniqueidentifier = null output,
                    @DISABLEADJUSTMENTPOSTSTATUS bit = null output,
                    @ALLOWGLDISTRIBUTION bit = null output,
                    @PDACCOUNTSYSTEMID uniqueidentifier = null output,
                    @REQUIREDEPOSIT bit = null output,
                    @DEPOSITID uniqueidentifier = 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
                    begin
                        select
                            @TSLONG = FINANCIALTRANSACTION.TSLONG,
                            @CONSTITUENTID = FINANCIALTRANSACTION.CONSTITUENTID,
                            @CONSTITUENTNAME = NF.NAME,
                            @TRANSACTIONCURRENCYID = FINANCIALTRANSACTION.TRANSACTIONCURRENCYID,
                            @PAYMENTDATE = FINANCIALTRANSACTION.DATE,
                            @BASECURRENCYID = CURRENCYSET.BASECURRENCYID,
                            @BASEEXCHANGERATEID = FINANCIALTRANSACTION.BASEEXCHANGERATEID,
                            @POSTDATE = FINANCIALTRANSACTION.POSTDATE
                        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;


                        select
                            @PAYMENTMETHODCODE = REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE
                        from dbo.REVENUEPAYMENTMETHOD
                        where REVENUEPAYMENTMETHOD.REVENUEID = @ID;

                        select  
                            @ADJUSTMENTDATE = getdate(),
                            @ADJUSTMENTPOSTSTATUSCODE = 0;

                        select top 1
                            @ADJUSTMENTDATE = ADJUSTMENT.DATE,
                            @ADJUSTMENTPOSTDATE = ADJUSTMENT.POSTDATE,
                            @ADJUSTMENTPOSTSTATUSCODE = ADJUSTMENT.POSTSTATUSCODE,
                            @ADJUSTMENTREASON = ADJUSTMENT.REASON,
                            @ADJUSTMENTREASONCODEID = ADJUSTMENT.REASONCODEID,
                            @ADJUSTMENTID = ADJUSTMENT.ID,
                            @DISABLEADJUSTMENTPOSTSTATUS = case when (ADJUSTMENT.POSTSTATUSCODE <> 0 and ADJUSTMENT.ADJUSTMENTCODE <> 0) then 1 else 0 end
                        from dbo.ADJUSTMENT
                        where ADJUSTMENT.REVENUEID = @ID
                        order by dateadded desc;

                        if @ADJUSTMENTREASONCODEID is null
                        begin
                            select
                                @ADJUSTMENTDATE = DATE,
                                @ADJUSTMENTPOSTDATE = POSTDATE,
                                @ADJUSTMENTPOSTSTATUSCODE = 1,
                                @ADJUSTMENTREASON = REASON,
                                @ADJUSTMENTREASONCODEID = REASONCODEID,
                                @ADJUSTMENTID = ID
                            from dbo.BENEFITADJUSTMENT
                            where REVENUEID = @ID and POSTSTATUSCODE = 1
                        end

                        declare @REVENUEPAYMENTMETHODID uniqueidentifier
                        select @REVENUEPAYMENTMETHODID = ID
                        from dbo.REVENUEPAYMENTMETHOD
                        where REVENUEID = @ID;

                        set @PENDINGADJUSTMENTCODE = 0;

                        if @PENDINGADJUSTMENTCODE = 0
                            begin
                                if ((select count(STOCKSALEADJUSTMENT.ID) from dbo.STOCKSALEADJUSTMENT
                                        inner join dbo.STOCKSALE on STOCKSALEADJUSTMENT.STOCKSALEID = STOCKSALE.ID
                                        where STOCKSALE.STOCKDETAILID = @REVENUEPAYMENTMETHODID and STOCKSALEADJUSTMENT.POSTSTATUSCODE <> 0) > 0)
                                    set @PENDINGADJUSTMENTCODE = 1;    
                            end
                        if @PENDINGADJUSTMENTCODE = 0
                            begin
                                if ((select count(GIFTINKINDSALEADJUSTMENT.ID) from dbo.GIFTINKINDSALEADJUSTMENT
                                        inner join dbo.GIFTINKINDSALE on GIFTINKINDSALEADJUSTMENT.GIFTINKINDSALEID = GIFTINKINDSALE.ID
                                        where GIFTINKINDSALE.GIFTINKINDPAYMENTMETHODDETAILID = @REVENUEPAYMENTMETHODID and GIFTINKINDSALEADJUSTMENT.POSTSTATUSCODE <> 0) > 0)
                                    set @PENDINGADJUSTMENTCODE = 1;    
                            end
                        if @PENDINGADJUSTMENTCODE = 0
                            begin
                                if ((select count(PROPERTYDETAILADJUSTMENT.ID) from dbo.PROPERTYDETAILADJUSTMENT 
                                        where PROPERTYDETAILADJUSTMENT.PROPERTYDETAILID = @REVENUEPAYMENTMETHODID and PROPERTYDETAILADJUSTMENT.POSTSTATUSCODE <> 0) > 0)
                                    set @PENDINGADJUSTMENTCODE = 1;    
                            end

                        select @DEPOSITID = DEPOSITID
                        from dbo.BANKACCOUNTDEPOSITPAYMENT
                        where ID = @ID;

                        if @ADJUSTMENTPOSTDATE is null
                        begin
                            if not @DEPOSITID is null
                                select @ADJUSTMENTPOSTDATE = POSTDATE 
                                from dbo.BANKACCOUNTDEPOSITPAYMENT 
                                    inner join dbo.BANKACCOUNTDEPOSIT on BANKACCOUNTDEPOSITPAYMENT.DEPOSITID = BANKACCOUNTDEPOSIT.ID
                                    inner join dbo.BANKACCOUNTTRANSACTION on BANKACCOUNTDEPOSIT.ID = BANKACCOUNTTRANSACTION.ID
                                where BANKACCOUNTDEPOSITPAYMENT.ID = @ID;
                            else
                                select @ADJUSTMENTPOSTDATE = dbo.UFN_DATE_GETEARLIESTTIME(getdate());
                        end

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

                        select @PDACCOUNTSYSTEMID = PDACCOUNTSYSTEM.ID, @REQUIREDEPOSIT = PDACCOUNTSYSTEM.REQUIREDPOSIT 
                        from dbo.PDACCOUNTSYSTEMFORREVENUE 
                            inner join dbo.PDACCOUNTSYSTEM on PDACCOUNTSYSTEMFORREVENUE.PDACCOUNTSYSTEMID = PDACCOUNTSYSTEM.ID
                        where PDACCOUNTSYSTEMFORREVENUE.ID = @ID;

                        set @ALLOWGLDISTRIBUTION = dbo.UFN_PDACCOUNTSYSTEM_ALLOWGLDISTRIBUTIONS(@PDACCOUNTSYSTEMID);
                    end

                    return 0;