USP_DATAFORMTEMPLATE_ADD_GIFTAIDREFUND

CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_GIFTAIDREFUND
(
    @ID uniqueidentifier = null output,
    @REVENUESPLITID uniqueidentifier,
    @REASON nvarchar(255) = null,
    @CHANGEAGENTID uniqueidentifier = null,
    @CURRENTAPPUSERID uniqueidentifier
)
as
begin
    set nocount on;

    declare @CURRENTDATE datetime;

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

    set @CURRENTDATE = getdate();

    insert into dbo.REVENUESPLITGIFTAIDREFUND
        (ID, REVENUESPLITID, CONSTITUENTID, ORIGINALTAXCLAIMNUMBER, ORIGINALCHARITYCLAIMREFERENCENUMBER, ORIGINALGIFTDATE, 
        ORIGINALGIFTAMOUNT, ORIGINALBASETAXCLAIMAMOUNT, ORIGINALTRANSITIONALTAXCLAIMAMOUNT, INCLUDETRANSITIONALAMOUNTCODE, 
        DATEREFUNDED, REFUNDSOURCECODE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, REASON, ORIGINALCLAIMEDASSPONSORSHIP, 
        ORIGINALTRANSACTIONBASETAXCLAIMAMOUNT, ORIGINALTRANSACTIONCURRENCYID, ORIGINALTRANSACTIONTRANSITIONALTAXCLAIMAMOUNT,
        ORIGINALORGANIZATIONBASETAXCLAIMAMOUNT, ORIGINALORGANIZATIONEXCHANGERATEID, ORIGINALORGANIZATIONTRANSITIONALTAXCLAIMAMOUNT,
        ORIGINALBASECURRENCYID, ORIGINALBASEEXCHANGERATEID, ORIGINALTRANSACTIONGIFTAMOUNT, ORIGINALORGANIZATIONGIFTAMOUNT)
        select 
            newID(),
            FINANCIALTRANSACTIONLINEITEM.ID,
            FINANCIALTRANSACTION.CONSTITUENTID,
            REVENUESPLITGIFTAID.TAXCLAIMNUMBER,
            REVENUESPLITGIFTAID.CHARITYCLAIMREFERENCENUMBER,
            cast(FINANCIALTRANSACTION.DATE as datetime),
            FINANCIALTRANSACTIONLINEITEM.BASEAMOUNT,            
            REVENUESPLITGIFTAID.BASETAXCLAIMAMOUNT,
            REVENUESPLITGIFTAID.TRANSITIONALTAXCLAIMAMOUNT,
            REVENUESPLITGIFTAID.INCLUDETRANSITIONALAMOUNTCODE,
            @CURRENTDATE, 
            1, 
            @CHANGEAGENTID, 
            @CHANGEAGENTID, 
            @CURRENTDATE, 
            @CURRENTDATE, 
            @REASON,
            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.REVENUESPLITGIFTAID
            on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLITGIFTAID.ID
        inner join dbo.FINANCIALTRANSACTION
            on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
        inner join dbo.REVENUE_EXT
            on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
        where FINANCIALTRANSACTIONLINEITEM.ID = @REVENUESPLITID
            and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
            and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1
            and FINANCIALTRANSACTION.DELETEDON is null;

        update dbo.REVENUESPLITGIFTAID set
            TAXCLAIMNUMBER = '',
            INCLUDETRANSITIONALAMOUNTCODE = 0,
            CHARITYCLAIMREFERENCENUMBER = '',
            DECLINESGIFTAID = 1,
            BASETAXCLAIMAMOUNT = 0,
            TRANSITIONALTAXCLAIMAMOUNT = 0,
            TRANSACTIONBASETAXCLAIMAMOUNT = 0,
            TRANSACTIONTRANSITIONALTAXCLAIMAMOUNT = 0,
            ORGANIZATIONBASETAXCLAIMAMOUNT = 0,
            ORGANIZATIONTRANSITIONALTAXCLAIMAMOUNT = 0,
            DATECHANGED = @CURRENTDATE,
            CHANGEDBYID = @CHANGEAGENTID,
            ISSPONSORSHIP = 0
        where ID = @REVENUESPLITID;

end