USP_REVENUE_CREATEREFUNDS

Create refunds for all claimed revenue splits belonging to a revenue record.
Parameters

Parameter	Parameter Type	Mode
@REVENUEID	uniqueidentifier	IN
@REFUNDSOURCECODE	tinyint	IN
@CHANGEAGENTID	uniqueidentifier	IN
@CHANGEDATE	datetime	IN
Definition

Copy


CREATE procedure dbo.USP_REVENUE_CREATEREFUNDS
(
@REVENUEID uniqueidentifier,
@REFUNDSOURCECODE tinyint,
@CHANGEAGENTID uniqueidentifier,
@CHANGEDATE datetime
)
as
    set nocount on;

    insert into dbo.REVENUESPLITGIFTAIDREFUND
    (
        REVENUESPLITID, 
        CONSTITUENTID,
        ORIGINALGIFTAMOUNT, 
        ORIGINALTAXCLAIMNUMBER, 
        ORIGINALBASETAXCLAIMAMOUNT, 
        ORIGINALTRANSITIONALTAXCLAIMAMOUNT, 
        INCLUDETRANSITIONALAMOUNTCODE, 
        DATEREFUNDED, 
        REFUNDSOURCECODE, 
        ORIGINALCHARITYCLAIMREFERENCENUMBER,
        ORIGINALGIFTDATE,
        ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED,
        REASON, 
        ORIGINALCLAIMEDASSPONSORSHIP, 
        ORIGINALTRANSACTIONBASETAXCLAIMAMOUNT, 
        ORIGINALTRANSACTIONCURRENCYID, 
        ORIGINALTRANSACTIONTRANSITIONALTAXCLAIMAMOUNT,
        ORIGINALORGANIZATIONBASETAXCLAIMAMOUNT, 
        ORIGINALORGANIZATIONEXCHANGERATEID, 
        ORIGINALORGANIZATIONTRANSITIONALTAXCLAIMAMOUNT,
        ORIGINALBASECURRENCYID, 
        ORIGINALBASEEXCHANGERATEID,
        ORIGINALTRANSACTIONGIFTAMOUNT,
        ORIGINALORGANIZATIONGIFTAMOUNT
    )
    select 
        REVENUESPLIT.ID, 
        REVENUE.CONSTITUENTID,
        REVENUESPLIT.AMOUNT,
        REVENUESPLITGIFTAID.TAXCLAIMNUMBER, 
        REVENUESPLITGIFTAID.BASETAXCLAIMAMOUNT, 
        REVENUESPLITGIFTAID.TRANSITIONALTAXCLAIMAMOUNT, 
        REVENUESPLITGIFTAID.INCLUDETRANSITIONALAMOUNTCODE,                    
        @CHANGEDATE, 
        @REFUNDSOURCECODE, 
        REVENUESPLITGIFTAID.CHARITYCLAIMREFERENCENUMBER,
        REVENUE.DATE,
        @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE,
        '',
        REVENUESPLITGIFTAID.CLAIMEDASSPONSORSHIP,
        REVENUESPLITGIFTAID.TRANSACTIONBASETAXCLAIMAMOUNT,
        REVENUESPLITGIFTAID.TRANSACTIONCURRENCYID,
        REVENUESPLITGIFTAID.TRANSACTIONTRANSITIONALTAXCLAIMAMOUNT,
        REVENUESPLITGIFTAID.ORGANIZATIONBASETAXCLAIMAMOUNT,
        REVENUESPLITGIFTAID.ORGANIZATIONEXCHANGERATEID,
        REVENUESPLITGIFTAID.ORGANIZATIONTRANSITIONALTAXCLAIMAMOUNT,
        REVENUESPLITGIFTAID.BASECURRENCYID,
        REVENUESPLITGIFTAID.BASEEXCHANGERATEID,
        REVENUESPLIT.TRANSACTIONAMOUNT,
        REVENUESPLIT.ORGANIZATIONAMOUNT
    from dbo.REVENUE
    inner join dbo.REVENUESPLIT 
        on REVENUE.ID = REVENUESPLIT.REVENUEID
    inner join dbo.REVENUESPLITGIFTAID 
        on REVENUESPLIT.ID = REVENUESPLITGIFTAID.ID
    where REVENUE.ID = @REVENUEID
        and REVENUESPLITGIFTAID.TAXCLAIMNUMBER <> ''

    update rsga set 
        TAXCLAIMNUMBER = '',
        CHARITYCLAIMREFERENCENUMBER = '',
        INCLUDETRANSITIONALAMOUNTCODE = 0    
    from dbo.REVENUESPLITGIFTAID rsga
    inner join dbo.REVENUESPLIT
        on rsga.ID = REVENUESPLIT.ID
    where REVENUESPLIT.REVENUEID = @REVENUEID
        and rsga.TAXCLAIMNUMBER <> '';