USP_REVENUESPLITGIFTAID_UPDATEGLDISTRIBUTIONS_FORREVENUE

            CREATE procedure dbo.USP_REVENUESPLITGIFTAID_UPDATEGLDISTRIBUTIONS_FORREVENUE
            (
                @REVENUEID uniqueidentifier,
                @OLDGIFTAID xml,
                @ORIGINALDONOTPOST bit,
                @NEWDONOTPOST bit,
                @CHANGEAGENTID uniqueidentifier = null,
                @CHANGEDATE datetime = null
            )
            with execute as owner
            as
            begin
                set nocount on;

                if @CHANGEDATE is null
                    set @CHANGEDATE = getdate();

                if @CHANGEAGENTID is null  
                    exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

                declare @REVENUESPLITGIFTAIDID uniqueidentifier;
                declare @OLDDESIGNATIONID uniqueidentifier;
                declare @OLDGLREVENUECATEGORYMAPPINGID uniqueidentifier;
                declare @OLDSTATUS nvarchar(30);
                declare @NEWDESIGNATIONID uniqueidentifier;
                declare @NEWGLREVENUECATEGORYMAPPINGID uniqueidentifier;
                declare @NEWSTATUS nvarchar(30);
                declare @GENERATEDISTRIBUTIONSFROMPOSTSTATUSCHANGE bit;
                declare @TAXCLAIMNUMBER nvarchar(10);

                declare GL_CURSOR cursor local fast_forward for
                    select 
                        T.c.value('(ID)[1]','uniqueidentifier') as ID, 
                        T.c.value('(DESIGNATIONID)[1]','uniqueidentifier') as DESIGNATIONID, 
                        T.c.value('(GLREVENUECATEGORYMAPPINGID)[1]','uniqueidentifier') as GLREVENUECATEGORYMAPPINGID, 
                        T.c.value('(STATUS)[1]','nvarchar(30)') as STATUS
                    from @OLDGIFTAID.nodes('/GIFTAIDQUALIFICATIONSTATUS/ITEM') T(c)

                open GL_CURSOR;

                fetch next from GL_CURSOR into @REVENUESPLITGIFTAIDID, @OLDDESIGNATIONID, @OLDGLREVENUECATEGORYMAPPINGID, @OLDSTATUS;

                while (@@FETCH_STATUS = 0)
                begin
                    set @GENERATEDISTRIBUTIONSFROMPOSTSTATUSCHANGE = 0;
                    set @TAXCLAIMNUMBER = '';

                    select 
                        @NEWDESIGNATIONID = REVENUESPLIT_EXT.DESIGNATIONID,
                        @NEWGLREVENUECATEGORYMAPPINGID = coalesce(REVENUECATEGORY.GLREVENUECATEGORYMAPPINGID, '00000000-0000-0000-0000-000000000000'),
                        @NEWSTATUS = dbo.UFN_GIFTAIDREVENUESPLIT_GETQUALIFICATIONSTATUS(FINANCIALTRANSACTIONLINEITEM.ID)
                    from
                        dbo.FINANCIALTRANSACTIONLINEITEM
                        inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
                        left join dbo.REVENUECATEGORY on FINANCIALTRANSACTIONLINEITEM.ID = REVENUECATEGORY.ID
                    where
                        FINANCIALTRANSACTIONLINEITEM.ID = @REVENUESPLITGIFTAIDID
                        and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
                        and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1;

                    --Cache CONTEXT INFO

                    declare @contextCache varbinary(128);
                    set @contextCache = CONTEXT_INFO();

                    if not @CHANGEAGENTID is null
                        set CONTEXT_INFO @CHANGEAGENTID;

                    -- delete any distributions for gift aid tied to splits that were removed and are unposted

                    delete from dbo.GIFTAIDGLDISTRIBUTION where ID in (
                        select GIFTAIDGLDISTRIBUTION.ID
                        from dbo.GIFTAIDGLDISTRIBUTION
                        inner join dbo.GLTRANSACTION on GIFTAIDGLDISTRIBUTION.GLTRANSACTIONID = GLTRANSACTION.ID
                        where GIFTAIDGLDISTRIBUTION.REVENUEID = @REVENUEID 
                        and GIFTAIDGLDISTRIBUTION.REVENUESPLITGIFTAIDID is null
                        and GLTRANSACTION.POSTSTATUSCODE <> 0
                    );

                    --Restore CONTEXT INFO 

                    if not @contextCache is null
                        set CONTEXT_INFO @contextCache; 

                        set @TAXCLAIMNUMBER = (select TAXCLAIMNUMBER from dbo.REVENUESPLITGIFTAID where ID = @REVENUESPLITGIFTAIDID);

                    -- If the post status has changed and either:

                    -- 1) the new post status is Do not post, or

                    -- 2) the new post status is Not posted and the revenue split has gift aid claimed, 

                    -- then update the GL distributions

                    if (@ORIGINALDONOTPOST <> @NEWDONOTPOST) and
                        (
                            (@NEWDONOTPOST = 1) or 
                            (@NEWDONOTPOST = 0 and @TAXCLAIMNUMBER <> '')
                        )
                        set @GENERATEDISTRIBUTIONSFROMPOSTSTATUSCHANGE = 1;

                    if (@NEWSTATUS = @OLDSTATUS) and @TAXCLAIMNUMBER <> '' and ((@NEWDESIGNATIONID <> @OLDDESIGNATIONID) or (@NEWGLREVENUECATEGORYMAPPINGID <> @OLDGLREVENUECATEGORYMAPPINGID) or (@GENERATEDISTRIBUTIONSFROMPOSTSTATUSCHANGE = 1))
                        exec dbo.USP_REVENUESPLITGIFTAID_UPDATEGLDISTRIBUTIONS @REVENUESPLITGIFTAIDID, @CHANGEAGENTID, @CHANGEDATE, @NEWDONOTPOST;

                    fetch next from GL_CURSOR into @REVENUESPLITGIFTAIDID, @OLDDESIGNATIONID, @OLDGLREVENUECATEGORYMAPPINGID, @OLDSTATUS;
                end

                close GL_CURSOR; 
                deallocate GL_CURSOR;

            end