USP_R68_SAVEGIFTAIDGLDISTRIBUTION_SINGLECLAIM

CREATE procedure dbo.USP_R68_SAVEGIFTAIDGLDISTRIBUTION_SINGLECLAIM
(
    @PDACCOUNTSYSTEMID uniqueidentifier,
    @REVENUESPLITID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier,
    @LOWEREDID varchar(36),
    @ID uniqueidentifier
)
as 
set nocount on;

begin try            
    begin tran
        declare @SQL nvarchar(4000),
                    @CHANGEDATE datetime,
                    @CHARITYCLAIMREFERENCENUMBER nvarchar(40),
            @TAXCLAIMNUMBER nvarchar(20);

        select    @CHANGEDATE = getdate()

        -- 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, @TAXCLAIMNUMBER = TAXCLAIMNUMBER
        from dbo.R68ARCHIVEDPARAMETERS
        where ID = @ID;

        -- 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.R68Details_' + @LOWEREDID + ' details on rsga.ID = details.REVENUESPLITID'
             + ' where rsga.ID = @REVENUESPLITID;'

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

        if @@ERROR <> 0
            rollback tran

        -- Create new gift aid GL distributions.

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

if @@ERROR <> 0
        rollback tran
    else
        commit tran
end try

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