USP_R68PROCESS_COMMIT_VALIDATION

CREATE procedure dbo.USP_R68PROCESS_COMMIT_VALIDATION
(
    @ID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier
)
as
    set nocount on;
    /*
    this is the validation from the SP USP_R68PROCESS_COMMIT_WITHOUTPUT
    */

    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(40);
        declare @RUNGIFTAIDSPONSORSHIPSONLY bit = 0;
        declare @TAXCLAIMNUMBER nvarchar(20);
        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
        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';

                end try

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