USP_REVENUE_CREATEREFUNDSONDELETE

            CREATE procedure dbo.USP_REVENUE_CREATEREFUNDSONDELETE
            (
                @REVENUEID uniqueidentifier,
                @CHANGEAGENTID uniqueidentifier
            )
            as
                set nocount on

                declare @CURRENTDATE datetime
                set @CURRENTDATE = getdate()

                -- Create gift aid refund records for splits that were already claimed on the R68 report

                insert into dbo.REVENUESPLITGIFTAIDREFUND 
                (
                    REVENUESPLITID,
                    CONSTITUENTID,
                    ORIGINALTAXCLAIMNUMBER,
                    ORIGINALCHARITYCLAIMREFERENCENUMBER,
                    ORIGINALGIFTDATE,
                    ORIGINALGIFTAMOUNT, 
                    ORIGINALBASETAXCLAIMAMOUNT, 
                    ORIGINALTRANSITIONALTAXCLAIMAMOUNT, 
                    INCLUDETRANSITIONALAMOUNTCODE, 
                    DATEREFUNDED, 
                    REFUNDSOURCECODE,                     
                    ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, 
                    ORIGINALCLAIMEDASSPONSORSHIP,
                    ORIGINALTRANSACTIONBASETAXCLAIMAMOUNT, 
                    ORIGINALTRANSACTIONCURRENCYID, 
                    ORIGINALTRANSACTIONTRANSITIONALTAXCLAIMAMOUNT,
                    ORIGINALORGANIZATIONBASETAXCLAIMAMOUNT, 
                    ORIGINALORGANIZATIONEXCHANGERATEID, 
                    ORIGINALORGANIZATIONTRANSITIONALTAXCLAIMAMOUNT,
                    ORIGINALBASECURRENCYID, 
                    ORIGINALBASEEXCHANGERATEID,
                    ORIGINALTRANSACTIONGIFTAMOUNT,
                    ORIGINALORGANIZATIONGIFTAMOUNT
                )
                select
                    FINANCIALTRANSACTIONLINEITEM.ID,
                    FINANCIALTRANSACTION.CONSTITUENTID,
                    REVENUESPLITGIFTAID.TAXCLAIMNUMBER,
                    REVENUESPLITGIFTAID.CHARITYCLAIMREFERENCENUMBER,
                    cast(FINANCIALTRANSACTION.DATE as datetime),
                    FINANCIALTRANSACTIONLINEITEM.BASEAMOUNT,
                    REVENUESPLITGIFTAID.BASETAXCLAIMAMOUNT,
                    REVENUESPLITGIFTAID.TRANSITIONALTAXCLAIMAMOUNT,
                    REVENUESPLITGIFTAID.INCLUDETRANSITIONALAMOUNTCODE,
                    @CURRENTDATE,
                    3, -- Revenue application deleted

                    @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE,
                    REVENUESPLITGIFTAID.CLAIMEDASSPONSORSHIP,
                    REVENUESPLITGIFTAID.TRANSACTIONBASETAXCLAIMAMOUNT,
                    REVENUESPLITGIFTAID.TRANSACTIONCURRENCYID,
                    REVENUESPLITGIFTAID.TRANSACTIONTRANSITIONALTAXCLAIMAMOUNT,
                    REVENUESPLITGIFTAID.ORGANIZATIONBASETAXCLAIMAMOUNT,
                    REVENUESPLITGIFTAID.ORGANIZATIONEXCHANGERATEID,
                    REVENUESPLITGIFTAID.ORGANIZATIONTRANSITIONALTAXCLAIMAMOUNT,
                    REVENUESPLITGIFTAID.BASECURRENCYID,
                    REVENUESPLITGIFTAID.BASEEXCHANGERATEID,
                    FINANCIALTRANSACTIONLINEITEM.TRANSACTIONAMOUNT,
                    FINANCIALTRANSACTIONLINEITEM.ORGAMOUNT
                from dbo.FINANCIALTRANSACTIONLINEITEM
                inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
                inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
                inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
                inner join dbo.REVENUESPLITGIFTAID on REVENUESPLIT_EXT.ID = REVENUESPLITGIFTAID.ID
                where
                    FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @REVENUEID and
                    coalesce(REVENUESPLITGIFTAID.TAXCLAIMNUMBER, '') <> '' and
                    FINANCIALTRANSACTION.DELETEDON is null and
                    FINANCIALTRANSACTIONLINEITEM.DELETEDON is null and
                    FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1