USP_R68PROCESS_COMMIT_WITHOUTPUT

            CREATE procedure dbo.USP_R68PROCESS_COMMIT_WITHOUTPUT
            (
                @ID uniqueidentifier,
                @CHANGEAGENTID uniqueidentifier,
                @ROWSPROCESSED int output
            )
            as
                set nocount on;

                begin try
                    declare @SQL nvarchar(4000);
                    declare @CHANGEDATE datetime = getDate();

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

                    -- Retrieve Tax claim number and output table names

                    declare @CHARITYCLAIMREFERENCENUMBER nvarchar(20);
                    declare @RUNGIFTAIDSPONSORSHIPSONLY bit = 0;
                    declare @TAXCLAIMNUMBER nvarchar(10);
                    declare @R68DETAILTABLENAME nvarchar(255);
                    declare @REFUNDDETAILTABLENAME nvarchar(255);

                    -- Retrieve most recent tax claim number

                    --    Allow user to change the tax claim number before committing without having to rerun the process

                    select 
                        @TAXCLAIMNUMBER = R68ARCHIVEDPARAMETERS.TAXCLAIMNUMBER,            
                        @R68DETAILTABLENAME = BUSINESSPROCESSOUTPUT.TABLENAME,
                        @ROWSPROCESSED    = BUSINESSPROCESSSTATUS.NUMBERPROCESSED
                    from dbo.BUSINESSPROCESSSTATUS 
                    inner join dbo.BUSINESSPROCESSOUTPUT 
                        on BUSINESSPROCESSSTATUS.ID = BUSINESSPROCESSOUTPUT.BUSINESSPROCESSSTATUSID
                    inner join dbo.R68STATUS 
                        on BUSINESSPROCESSSTATUS.ID= R68STATUS.ID
                    inner join dbo.R68 
                        on R68STATUS.PARAMETERSETID = R68.ID
          inner join R68ONLINESUBMISSIONPARAMETERS on
              R68.ID = R68ONLINESUBMISSIONPARAMETERS.R68PROCESSID
          inner join dbo.R68ARCHIVEDPARAMETERS 
              on R68ARCHIVEDPARAMETERS.ID = R68ONLINESUBMISSIONPARAMETERS.ID
                    where BUSINESSPROCESSSTATUS.ID = @ID 
                        AND BUSINESSPROCESSOUTPUT.TABLEKEY = 'R68_OUTPUT'
            AND R68ARCHIVEDPARAMETERS.ID = @ID;

          declare @GLVALIDATIONSQL nvarchar(2000);
          set @GLVALIDATIONSQL = '
          if exists (select PD.PDACCOUNTSYSTEMID from dbo.' + @R68DETAILTABLENAME + ' as output
          inner join dbo.REVENUESPLIT RS on output.REVENUESPLITID = RS.ID
          inner join dbo.PDACCOUNTSYSTEMFORREVENUE PD on PD.ID = RS.REVENUEID
          where NOT exists (select PDACCOUNTSYSTEMID from dbo.PDACCOUNTCODEMAPPING PDAC where OFFICEID = 12 and PDAC.PDACCOUNTSYSTEMID = PD.PDACCOUNTSYSTEMID)
          and not exists (select * from dbo.PDACCOUNTSYSTEM where PDACCOUNTSYSTEM.ID = PD.PDACCOUNTSYSTEMID and DEFAULTGLACCOUNTID is not null)
          and dbo.UFN_PDACCOUNTSYSTEM_ALLOWGLDISTRIBUTIONS(PD.PDACCOUNTSYSTEMID) = 1)
                    begin
                        declare @ACCOUNTSYSTEM nvarchar(100);

                        select top 1 @ACCOUNTSYSTEM = A.NAME
                        from dbo.' + @R68DETAILTABLENAME + ' as output
                        inner join dbo.REVENUESPLIT RS on output.REVENUESPLITID = RS.ID
                        inner join dbo.PDACCOUNTSYSTEMFORREVENUE PD on PD.ID = RS.REVENUEID
                        inner join dbo.PDACCOUNTSYSTEM A on PD.PDACCOUNTSYSTEMID = A.ID
                        where NOT exists (select PDACCOUNTSYSTEMID from dbo.PDACCOUNTCODEMAPPING PDAC where OFFICEID = 12 and PDAC.PDACCOUNTSYSTEMID = PD.PDACCOUNTSYSTEMID)
                        and not exists (select * from dbo.PDACCOUNTSYSTEM where PDACCOUNTSYSTEM.ID = PD.PDACCOUNTSYSTEMID and DEFAULTGLACCOUNTID is not null);

                        raiserror(''GLMAPPING_%s'',13,1, @ACCOUNTSYSTEM);
          end'

          exec sp_executesql @GLVALIDATIONSQL


                    -- Retrieve the Charity claim reference number from the archived parameters table

                    --    As if user changes this value the process must be restarted to retrieve updated data

                    --    If they commit the latest history, data will have been produced using archived Charity claim reference number

                    select 
                        @CHARITYCLAIMREFERENCENUMBER = REFERENCENUMBER,
                        @RUNGIFTAIDSPONSORSHIPSONLY = RUNGIFTAIDSPONSORSHIPSONLY
                    from dbo.R68ARCHIVEDPARAMETERS
                    where ID = @ID;

                    -- Check to make sure the current Tax claim number has not already been used to commit

                    --    results for this Charity claim reference number

                    if dbo.UFN_R68_CLAIMNUMBERHASBEENCOMMITTED(@CHARITYCLAIMREFERENCENUMBER, @TAXCLAIMNUMBER) = 1
                        begin
                            declare @MSG nvarchar(255) = 'The current tax claim number (' + @TAXCLAIMNUMBER + ') has already been committed for this charity claim reference number (' + @CHARITYCLAIMREFERENCENUMBER + ').' + char(13) + char(10) + 'Please alter the tax claim number and attempt to commit the results again.';
                            raiserror(@MSG, 13, 1);
                        end

                    select 
                        @REFUNDDETAILTABLENAME = BUSINESSPROCESSOUTPUT.TABLENAME
                    from dbo.BUSINESSPROCESSSTATUS 
                    inner join dbo.BUSINESSPROCESSOUTPUT 
                        on BUSINESSPROCESSSTATUS.ID = BUSINESSPROCESSOUTPUT.BUSINESSPROCESSSTATUSID                                            
                    where BUSINESSPROCESSSTATUS.ID = @ID 
                        AND BUSINESSPROCESSOUTPUT.TABLEKEY = 'R68REFUNDS_OUTPUT';

                    -- create/update refunds

                    set @SQL =
                        'merge dbo.REVENUESPLITGIFTAIDREFUND as refunds
                            using (select * from dbo.'
                            + @REFUNDDETAILTABLENAME
                            + ') as newRefunds
                                on refunds.ID = newRefunds.ID                    
                                when matched then update
                                    set refunds.REFUNDTAXCLAIMNUMBER = @REFUNDTAXCLAIMNUMBER,    
                                        refunds.PENDINGR68STATUSID = null,
                                        refunds.CHANGEDBYID = @CHANGEAGENTIDIN,
                                        refunds.DATECHANGED = @CHANGEDATEIN                                            
                            when not matched by target then 
                                        insert (ID, REVENUESPLITID, CONSTITUENTID, ORIGINALTAXCLAIMNUMBER, ORIGINALCHARITYCLAIMREFERENCENUMBER, ORIGINALGIFTDATE, ORIGINALGIFTAMOUNT, ORIGINALBASETAXCLAIMAMOUNT, ORIGINALTRANSITIONALTAXCLAIMAMOUNT, INCLUDETRANSITIONALAMOUNTCODE, REFUNDTAXCLAIMNUMBER, DATEREFUNDED, REFUNDSOURCECODE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, ORIGINALCLAIMEDASSPONSORSHIP, ORIGINALBASECURRENCYID, ORIGINALTRANSACTIONGIFTAMOUNT, ORIGINALTRANSACTIONBASETAXCLAIMAMOUNT, ORIGINALTRANSACTIONTRANSITIONALTAXCLAIMAMOUNT, ORIGINALTRANSACTIONCURRENCYID, ORIGINALORGANIZATIONGIFTAMOUNT, ORIGINALORGANIZATIONBASETAXCLAIMAMOUNT, ORIGINALORGANIZATIONTRANSITIONALTAXCLAIMAMOUNT, ORIGINALBASEEXCHANGERATEID, ORIGINALORGANIZATIONEXCHANGERATEID)
                                        values (newRefunds.ID, newRefunds.REVENUESPLITID, newRefunds.CONSTITUENTID, newRefunds.ORIGINALTAXCLAIMNUMBER, newRefunds.ORIGINALCHARITYCLAIMREFERENCENUMBER, newRefunds.ORIGINALGIFTDATE, newRefunds.BASEGIFTAMOUNT, newRefunds.BASEBASETAXCLAIMAMOUNT, newRefunds.BASETRANSITIONALTAXCLAIMAMOUNT, newRefunds.INCLUDETRANSITIONALAMOUNTCODE, @REFUNDTAXCLAIMNUMBER, @CHANGEDATEIN, newRefunds.REFUNDSOURCECODE, @CHANGEAGENTIDIN, @CHANGEAGENTIDIN, @CHANGEDATEIN, @CHANGEDATEIN, coalesce(newRefunds.ORIGINALCLAIMEDASSPONSORSHIP, 0), newRefunds.BASECURRENCYID, newRefunds.TRANSACTIONGIFTAMOUNT, newRefunds.TRANSACTIONBASETAXCLAIMAMOUNT, newRefunds.TRANSACTIONTRANSITIONALTAXCLAIMAMOUNT, newRefunds.TRANSACTIONCURRENCYID, newRefunds.ORGANIZATIONGIFTAMOUNT, newRefunds.ORGANIZATIONBASETAXCLAIMAMOUNT, newRefunds.ORGANIZATIONTRANSITIONALTAXCLAIMAMOUNT, newRefunds.BASEEXCHANGERATEID, newRefunds.ORGANIZATIONEXCHANGERATEID);'

                    exec sp_executesql @SQL, N'@REFUNDTAXCLAIMNUMBER nvarchar(10), @CHANGEAGENTIDIN uniqueidentifier, @CHANGEDATEIN datetime', @REFUNDTAXCLAIMNUMBER = @TAXCLAIMNUMBER, @CHANGEAGENTIDIN = @CHANGEAGENTID, @CHANGEDATEIN = @CHANGEDATE;

                    -- remove tax claim number from original applications

                    set @SQL =
                        'update rsga set
                            TAXCLAIMNUMBER = '''',
                            CHARITYCLAIMREFERENCENUMBER = '''',
                            INCLUDETRANSITIONALAMOUNTCODE = 0,
                            PENDINGR68STATUSID = null,                
                            CHANGEDBYID = @CHANGEAGENTIDIN,
                            DATECHANGED = @CHANGEDATEIN,
                    CLAIMEDASSPONSORSHIP = case when @RUNGIFTAIDSPONSORSHIPSONLYIN = 1 then 0 else CLAIMEDASSPONSORSHIP end
                         from dbo.REVENUESPLITGIFTAID rsga
                         inner join dbo.'
                         + @REFUNDDETAILTABLENAME
                         + ' refunds on rsga.ID = refunds.REVENUESPLITID'

                    exec sp_executesql @SQL, N'@CHANGEAGENTIDIN uniqueidentifier, @CHANGEDATEIN datetime, @RUNGIFTAIDSPONSORSHIPSONLYIN bit', @CHANGEAGENTIDIN = @CHANGEAGENTID, @CHANGEDATEIN = @CHANGEDATE, @RUNGIFTAIDSPONSORSHIPSONLYIN = @RUNGIFTAIDSPONSORSHIPSONLY;

                    -- Update tax claim numbers

                    --    after handling refunds as it's possible to refund and claim the same application during the same R68 process...

                    set @SQL =
                        'update rsga set 
                            TAXCLAIMNUMBER = @TAXCLAIMNUMBERIN,
                            CHARITYCLAIMREFERENCENUMBER = @CHARITYCLAIMREFERENCENUMBERIN,
                            PENDINGR68STATUSID = null,                
                            CHANGEDBYID = @CHANGEAGENTIDIN,
                            DATECHANGED = @CHANGEDATEIN,
                            INCLUDETRANSITIONALAMOUNTCODE = case when dbo.UFN_REVENUESPLITGIFTAID_TRANSITIONALREFLIEFEXPIRED(rsga.ID, 0) = 0 then 1 else 2 end,
                    CLAIMEDASSPONSORSHIP = ISSPONSORSHIP
                         from dbo.REVENUESPLITGIFTAID rsga 
                         inner join dbo.' + @R68DETAILTABLENAME
                        + ' details    on rsga.ID = details.REVENUESPLITID'

                    exec sp_executesql @SQL, N'@TAXCLAIMNUMBERIN nvarchar(10), @CHARITYCLAIMREFERENCENUMBERIN nvarchar(20), @CHANGEAGENTIDIN uniqueidentifier, @CHANGEDATEIN datetime', @TAXCLAIMNUMBERIN = @TAXCLAIMNUMBER, @CHARITYCLAIMREFERENCENUMBERIN = @CHARITYCLAIMREFERENCENUMBER, @CHANGEAGENTIDIN = @CHANGEAGENTID, @CHANGEDATEIN = @CHANGEDATE;

                    exec dbo.USP_R68_SAVEGIFTAIDGLDISTRIBUTIONS @ID, @CHANGEAGENTID;

                    -- Mark archived parameters as having been committed and set Tax claim number used

                    update dbo.R68ARCHIVEDPARAMETERS set
                        TAXCLAIMNUMBER = @TAXCLAIMNUMBER,
                        COMMITTED = 1,
                        CHANGEDBYID = @CHANGEAGENTID,
                        DATECHANGED = @CHANGEDATE
                    where ID = @ID;




                end try

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