USP_R68_SAVEGIFTAIDGLDISTRIBUTIONS

            CREATE procedure dbo.USP_R68_SAVEGIFTAIDGLDISTRIBUTIONS
            (
                @BUSINESSPROCESSSTATUSID uniqueidentifier,
                @CHANGEAGENTID uniqueidentifier = null
            )
            as 
            set nocount on;

            declare @R68DETAILTABLENAME nvarchar(255);
            declare @REFUNDDETAILTABLENAME nvarchar(255);
            declare @SQL nvarchar(4000);

            declare @REVENUESPLITID uniqueidentifier;
            declare @GL_CURSOR CURSOR;

            begin try
                declare @contextCache varbinary(128);

                --cache current context information

                set @contextCache = CONTEXT_INFO();

                --set CONTEXT_INFO to @CHANGEAGENTID

                set CONTEXT_INFO @CHANGEAGENTID;

                -- Retrieve dynamic table names

                select 
                    @R68DETAILTABLENAME = TABLENAME            
                from 
                    dbo.BUSINESSPROCESSOUTPUT 
                where 
                    BUSINESSPROCESSSTATUSID = @BUSINESSPROCESSSTATUSID 
                    and TABLEKEY = 'R68_OUTPUT';

                select 
                    @REFUNDDETAILTABLENAME = BUSINESSPROCESSOUTPUT.TABLENAME
                from 
                    dbo.BUSINESSPROCESSOUTPUT 
                where 
                    BUSINESSPROCESSSTATUSID = @BUSINESSPROCESSSTATUSID 
                    and TABLEKEY = 'R68REFUNDS_OUTPUT';    

                declare @PDACCOUNTSYSTEMID uniqueidentifier;

                -- Iterate through R68 Refund Details for splits being refunded

                set @SQL =
              'set @GL_CURSOR = cursor static for
                  select REFUNDDETAILS.REVENUESPLITID, pd.PDACCOUNTSYSTEMID  
                  from dbo.' + @REFUNDDETAILTABLENAME + ' as REFUNDDETAILS 
                  inner join REVENUESPLIT as rs on REFUNDDETAILS.REVENUESPLITID = rs.ID 
                  inner join PDACCOUNTSYSTEMFORREVENUE as pd on rs.REVENUEID = pd.ID; open @GL_CURSOR;'                

                exec sp_executesql @SQL, N'@GL_CURSOR cursor OUTPUT', @GL_CURSOR OUTPUT;

                fetch next from @GL_CURSOR into @REVENUESPLITID, @PDACCOUNTSYSTEMID;

                -- Create reversals for refunds.

                while (@@FETCH_STATUS = 0)
                begin
                    if exists(
                        select
                            1
                        from
                            dbo.FINANCIALTRANSACTIONLINEITEM GA
                            inner join dbo.FINANCIALTRANSACTIONLINEITEM SOURCE on SOURCE.ID = GA.SOURCELINEITEMID
                            left join dbo.FINANCIALTRANSACTIONLINEITEM NEWSOURCE on NEWSOURCE.ID = SOURCE.REVERSEDLINEITEMID
                        where
                            @REVENUESPLITID = isnull(NEWSOURCE.ID, SOURCE.ID)
                            and GA.TYPECODE = 8
                            and GA.POSTSTATUSCODE = 2
                    )
                    begin
                        declare @PAYMENTADJUSTMENTID uniqueidentifier;
                        declare @ADJUSTMENTPOSTDATE date;
                        declare @ADJUSTMENTPOSTSTATUS tinyint = 1;

                        select top 1
                            @PAYMENTADJUSTMENTID = LIA.ID,
                            @ADJUSTMENTPOSTDATE = coalesce(A.POSTDATE, LIA.DATE, LI.POSTDATE),
                            @ADJUSTMENTPOSTSTATUS = case when A.POSTSTATUSCODE = 2 then 3 else 1 end
                        from
                            dbo.FINANCIALTRANSACTIONLINEITEM LI
                            left join dbo.FINANCIALTRANSACTIONLINEITEMADJUSTMENT LIA on LIA.ID = LI.FINANCIALTRANSACTIONLINEITEMADJUSTMENTID
                            left join dbo.ADJUSTMENT A on A.REVENUEID = LI.FINANCIALTRANSACTIONID
                        where
                            LI.ID = @REVENUESPLITID
                            and LIA.ADJUSTMENTREASONCODEID is not null
                            and not exists (select 1 from dbo.FINANCIALTRANSACTIONLINEITEM GA where GA.SOURCELINEITEMID = LI.ID 
                                and GA.FINANCIALTRANSACTIONLINEITEMADJUSTMENTID = LIA.ID
                                and GA.TYPECODE = 8)
                        order by A.DATEADDED DESC;

                        if dbo.UFN_PDACCOUNTSYSTEM_ALLOWGLDISTRIBUTIONS(@PDACCOUNTSYSTEMID) = 1
                          exec dbo.USP_SAVE_GIFTAID_ADJUSTMENT @REVENUESPLITID, @ADJUSTMENTPOSTSTATUS, @ADJUSTMENTPOSTDATE, null, @PAYMENTADJUSTMENTID, @CHANGEAGENTID, null;
                    end
                    else
                    begin
                        delete from
                            dbo.FINANCIALTRANSACTIONLINEITEM
                        where
                            ID in (
                                select
                                    GA.ID
                                from
                                    dbo.FINANCIALTRANSACTIONLINEITEM GA
                                    inner join dbo.FINANCIALTRANSACTIONLINEITEM SOURCE on SOURCE.ID = GA.SOURCELINEITEMID
                                    left join dbo.FINANCIALTRANSACTIONLINEITEM NEWSOURCE on NEWSOURCE.ID = SOURCE.REVERSEDLINEITEMID
                                where
                                    @REVENUESPLITID = isnull(NEWSOURCE.ID, SOURCE.ID)
                                    and GA.TYPECODE = 8
                                    and GA.POSTSTATUSCODE = 1
                                    and GA.FINANCIALTRANSACTIONLINEITEMADJUSTMENTID is null
                                    and (GA.ID <> SOURCE.REVERSEDLINEITEMID  or SOURCE.REVERSEDLINEITEMID is null)
                                    and (GA.ID <> NEWSOURCE.REVERSEDLINEITEMID or  NEWSOURCE.REVERSEDLINEITEMID is null)
                                    and GA.DELETEDON is null)
                    end

                    -- Clear outdated gift aid GL distributions.

                    --delete from dbo.GIFTAIDGLDISTRIBUTION where REVENUESPLITGIFTAIDID = @REVENUESPLITID and OUTDATED = 0;    


                    fetch next from @GL_CURSOR into @REVENUESPLITID, @PDACCOUNTSYSTEMID;
                end

                close @GL_CURSOR;
                deallocate @GL_CURSOR;

                -- Iterate through R68 Details for splits being claimed

                set @SQL =
              'set @GL_CURSOR = cursor static for 
                  select R68DETAILS.REVENUESPLITID, pd.PDACCOUNTSYSTEMID  
                  from dbo.' + @R68DETAILTABLENAME + ' as R68DETAILS 
                  inner join REVENUESPLIT as rs on R68DETAILS.revenuesplitid = rs.ID 
                  inner join PDACCOUNTSYSTEMFORREVENUE as pd on rs.REVENUEID = pd.ID; open @GL_CURSOR;'

                exec sp_executesql @SQL, N'@GL_CURSOR cursor OUTPUT', @GL_CURSOR OUTPUT;

                fetch next from @GL_CURSOR into @REVENUESPLITID, @PDACCOUNTSYSTEMID;

                -- Create new GL distributions for new gift aid being claimed.

                while (@@FETCH_STATUS = 0)
                begin                    
                    -- Clear outdated gift aid GL distributions.

                    --delete from dbo.GIFTAIDGLDISTRIBUTION where REVENUESPLITGIFTAIDID = @REVENUESPLITID and OUTDATED = 0;    


                    -- Create new gift aid GL distributions.

                    if dbo.UFN_PDACCOUNTSYSTEM_ALLOWGLDISTRIBUTIONS(@PDACCOUNTSYSTEMID) = 1
                      exec dbo.USP_SAVE_GIFTAIDGLDISTRIBUTION @REVENUESPLITID, @CHANGEAGENTID;

                    fetch next from @GL_CURSOR into @REVENUESPLITID, @PDACCOUNTSYSTEMID;
                end

                close @GL_CURSOR;
                deallocate @GL_CURSOR;

                --reset CONTEXT_INFO to previous value

                if not @contextCache is null
                    set CONTEXT_INFO @contextCache;


            end try

            begin catch
                exec dbo.USP_RAISE_ERROR;
                return 1;
            end catch