USP_EDITPLANNEDGIFTREVENUE_RECREATEPAYMENTGLDISTRIBUTIONS

CREATE procedure dbo.USP_EDITPLANNEDGIFTREVENUE_RECREATEPAYMENTGLDISTRIBUTIONS
(
    @ID uniqueidentifier
    ,@CHANGEAGENTID uniqueidentifier = null
)
as
begin
        declare @contextCache varbinary(128);
        declare @CURRENTDATE datetime;

        if @CHANGEAGENTID is not null 
        set CONTEXT_INFO @CHANGEAGENTID;

        declare @PAYMENTREVENUES table
        (
            ID uniqueidentifier
        )

        insert into @PAYMENTREVENUES(ID)
        select PAYMENTREVENUE.ID from dbo.INSTALLMENTPAYMENT
            inner join dbo.REVENUE on REVENUE.ID = INSTALLMENTPAYMENT.PLEDGEID
            inner join dbo.REVENUESPLIT as PAYMENTREVENUESPLIT on PAYMENTREVENUESPLIT.ID = INSTALLMENTPAYMENT.PAYMENTID
            inner join dbo.REVENUE as PAYMENTREVENUE on PAYMENTREVENUE.ID = PAYMENTREVENUESPLIT.REVENUEID
        where REVENUE.ID = @ID;                        

      delete from dbo.GLTRANSACTION where GLTRANSACTION.ID in 
        (
            select GLTRANSACTION.ID from dbo.REVENUE
                inner join dbo.REVENUEGLDISTRIBUTION on REVENUEGLDISTRIBUTION.REVENUEID = REVENUE.ID
                inner join dbo.GLTRANSACTION on GLTRANSACTION.ID = REVENUEGLDISTRIBUTION.GLTRANSACTIONID
            where REVENUE.ID in (select ID from @PAYMENTREVENUES) and GLTRANSACTION.POSTSTATUSCODE <> 0
        )

        if not @CONTEXTCACHE is null 
            set CONTEXT_INFO @CONTEXTCACHE;

        declare @PAYMENTREVENUEID uniqueidentifier
        declare recreatedistributions_cursor cursor for select ID from @PAYMENTREVENUES

        open recreatedistributions_cursor

        fetch next from recreatedistributions_cursor into @PAYMENTREVENUEID

        while @@FETCH_STATUS=0
        begin
            -- Add new GL distributions

            exec dbo.USP_SAVE_REVENUEGLDISTRIBUTION @PAYMENTREVENUEID, @CHANGEAGENTID, @CURRENTDATE;

            -- Add new stock detail GL distributions

            exec dbo.USP_SAVE_STOCKDETAILGLDISTRIBUTION @PAYMENTREVENUEID, @CHANGEAGENTID, @CURRENTDATE;

            -- Add new gift-in-kind detail GL distributions

            exec dbo.USP_SAVE_GIFTINKINDPAYMENTMETHODDETAILGLDISTRIBUTION @PAYMENTREVENUEID, @CHANGEAGENTID, @CURRENTDATE;

            -- Add new property detail GL distributions

            exec dbo.USP_SAVE_PROPERTYDETAILGLDISTRIBUTION @PAYMENTREVENUEID, @CHANGEAGENTID, @CURRENTDATE;

            -- add benefit distributions

            exec dbo.USP_SAVE_PAYMENT_BENEFITGLDISTRIBUTION @PAYMENTREVENUEID, @CHANGEAGENTID, @CURRENTDATE;

            declare @DEPOSITID uniqueidentifier;
            select @DEPOSITID = DEPOSITID
            from dbo.BANKACCOUNTDEPOSITPAYMENT
            where ID = @PAYMENTREVENUEID;
            if @DEPOSITID is not null
            exec dbo.USP_BANKACCOUNTDEPOSIT_OVERWRITEPAYMENTDEBITACCOUNTS @PAYMENTREVENUEID, @DEPOSITID, @CHANGEAGENTID, @CURRENTDATE;

            fetch next from recreatedistributions_cursor into @PAYMENTREVENUEID
        end

        close recreatedistributions_cursor
        deallocate recreatedistributions_cursor
end