USP_R68_SAVEGIFTAIDGLDISTRIBUTION_SINGLEREFUND

CREATE procedure dbo.USP_R68_SAVEGIFTAIDGLDISTRIBUTION_SINGLEREFUND
(
    @PDACCOUNTSYSTEMID uniqueidentifier,
    @REVENUESPLITID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier,
    @LOWEREDID varchar(36),
  @ID uniqueidentifier,
  @REFUNDID uniqueidentifier = null
)
as 
set nocount on;
    begin try
        begin tran
            declare @SQL nvarchar(4000),
                        @CHANGEDATE datetime,
              @RUNGIFTAIDSPONSORSHIPSONLY bit,
              @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,
          @RUNGIFTAIDSPONSORSHIPSONLY = RUNGIFTAIDSPONSORSHIPSONLY,
        @TAXCLAIMNUMBER = TAXCLAIMNUMBER
      from dbo.R68ARCHIVEDPARAMETERS
      where ID = @ID;

      declare @NEWLINE char(2) = char(13) + char(10);

      -- create/update refunds

      set @SQL = 'merge dbo.REVENUESPLITGIFTAIDREFUND as refunds' + @NEWLINE;

      -- Use REFUNDID when it was provided, otherwise use SPLITID

      if @REFUNDID is null
      begin
        set @SQL = @SQL + 'using (select * from dbo.R68REFUNDDETAILS_'+ @LOWEREDID + ' WHERE REVENUESPLITID = @SPLITID) as newRefunds' + @NEWLINE;
      end
      else
      begin
        set @SQL = @SQL + 'using (select * from dbo.R68REFUNDDETAILS_'+ @LOWEREDID + ' WHERE ID = @REFUNDID) as newRefunds' + @NEWLINE;
      end

            set @SQL = @SQL +
        '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);'

      -- Use REFUNDID when it was provided, otherwise use SPLITID

      if @REFUNDID is null
      begin
        exec sp_executesql @SQL, N'@REFUNDTAXCLAIMNUMBER nvarchar(10), @CHANGEAGENTIDIN uniqueidentifier, @CHANGEDATEIN datetime, @SPLITID uniqueidentifier', @REFUNDTAXCLAIMNUMBER = @TAXCLAIMNUMBER, @CHANGEAGENTIDIN = @CHANGEAGENTID, @CHANGEDATEIN = @CHANGEDATE, @SPLITID = @REVENUESPLITID;
      end
      else
      begin
        exec sp_executesql @SQL, N'@REFUNDTAXCLAIMNUMBER nvarchar(10), @CHANGEAGENTIDIN uniqueidentifier, @CHANGEDATEIN datetime, @REFUNDID uniqueidentifier', @REFUNDTAXCLAIMNUMBER = @TAXCLAIMNUMBER, @CHANGEAGENTIDIN = @CHANGEAGENTID, @CHANGEDATEIN = @CHANGEDATE, @REFUNDID = @REFUNDID;
      end

            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.R68REFUNDDETAILS_' + @LOWEREDID + ' refunds on rsga.ID = refunds.REVENUESPLITID'
                         + ' where rsga.ID = @REVENUESPLITID'

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

            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;    

if @@ERROR <> 0
        ROLLBACK TRAN
    else
        COMMIT TRAN
end try

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