USP_PLEDGE_PAYMENT_ADDGIFTFEES

CREATE procedure dbo.USP_PLEDGE_PAYMENT_ADDGIFTFEES(
        @REVENUEID uniqueidentifier,
        @SPLITS xml,
        @CONSTITUENTID uniqueidentifier,
        @CHANGEAGENTID uniqueidentifier,
        @DATECHANGED datetime,
        @ADJUSTMENTID uniqueidentifier
)
as

begin

    set nocount on;

    --Do nothing if this is turned off.  

    if dbo.UFN_GIFTFEE_ENABLED() = 0  
        return;  


    declare @GIFTFEES table(
        ID uniqueidentifier,
        FEE money,
        WAIVED bit,
        TRANSACTIONAMOUNT money,
        ORGANIZATIONAMOUNT money,
        BASECURRENCYID uniqueidentifier,
        BASEEXCHANGERATEID uniqueidentifier,
        TRANSACTIONCURRENCYID uniqueidentifier,
        ORGANIZATIONEXCHANGERATEID uniqueidentifier
    )

    insert into @GIFTFEES
    select 
         GIFTFEES.ID
        ,GIFTFEES.FEE
        ,GIFTFEES.WAIVED 
        ,GIFTFEES.TRANSACTIONAMOUNT
        ,GIFTFEES.ORGANIZATIONAMOUNT
        ,GIFTFEES.BASECURRENCYID
        ,GIFTFEES.BASEEXCHANGERATEID
        ,GIFTFEES.TRANSACTIONCURRENCYID
        ,GIFTFEES.ORGANIZATIONEXCHANGERATEID
    from dbo.UFN_REVENUE_GENERATEDEFAULTGIFTFEES(@REVENUEID, @CONSTITUENTID) GIFTFEES
    inner join UFN_REVENUE_GETSPLITS_2_FROMITEMLISTXML(@SPLITS) SPLITS on GIFTFEES.ID = SPLITS.ID

    merge dbo.FINANCIALTRANSACTIONLINEITEM as target
    using
        (select
            GIFTFEES.ID as FINANCIALTRANSACTIONLINEITEMID, 
            @REVENUEID as FINANCIALTRANSACTIONID,
            GIFTFEES.FEE BASEAMOUNT,
            GIFTFEES.TRANSACTIONAMOUNT, 
            GIFTFEES.ORGANIZATIONAMOUNT,
            FINANCIALTRANSACTIONLINEITEM.POSTSTATUSCODE,
            FINANCIALTRANSACTIONLINEITEM.POSTDATE,
            GIFTFEES.ID as SOURCELINEITEMID
        from @GIFTFEES GIFTFEES
        inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.ID = GIFTFEES.ID
        ) as source
    on (source.FINANCIALTRANSACTIONLINEITEMID = target.SOURCELINEITEMID)
    when matched and target.TYPECODE = 7 and target.DELETEDON is null then update
        set 
            target.BASEAMOUNT = source.BASEAMOUNT,
            target.TRANSACTIONAMOUNT = source.TRANSACTIONAMOUNT,
            target.ORGAMOUNT = source.ORGANIZATIONAMOUNT,
            target.POSTSTATUSCODE = source.POSTSTATUSCODE,
            target.POSTDATE = source.POSTDATE,
            target.DATEADDED = @DATECHANGED,
            target.DATECHANGED = @DATECHANGED,
            target.ADDEDBYID = @CHANGEAGENTID,
            target.CHANGEDBYID = @CHANGEAGENTID,
            target.FINANCIALTRANSACTIONLINEITEMADJUSTMENTID = @ADJUSTMENTID,
            target.SOURCELINEITEMID = source.SOURCELINEITEMID,
            target.TYPECODE = 7
    when not matched by target then insert
        (ID, FINANCIALTRANSACTIONID, BASEAMOUNT, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, TRANSACTIONAMOUNT, ORGAMOUNT, POSTSTATUSCODE, POSTDATE, FINANCIALTRANSACTIONLINEITEMADJUSTMENTID, SOURCELINEITEMID, TYPECODE)
        values
        (newid(), source.FINANCIALTRANSACTIONID, source.BASEAMOUNT, @CHANGEAGENTID, @CHANGEAGENTID, @DATECHANGED, @DATECHANGED, source.TRANSACTIONAMOUNT, source.ORGANIZATIONAMOUNT, source.POSTSTATUSCODE, source.POSTDATE, @ADJUSTMENTID, source.SOURCELINEITEMID, 7);            

    merge dbo.REVENUESPLITGIFTFEE as target
    using (
            select
                GIFTFEES.ID, GIFTFEES.FEE, GIFTFEES.WAIVED, GIFTFEES.BASECURRENCYID, GIFTFEES.ORGANIZATIONAMOUNT, GIFTFEES.ORGANIZATIONEXCHANGERATEID, GIFTFEES.TRANSACTIONAMOUNT, GIFTFEES.TRANSACTIONCURRENCYID, GIFTFEES.BASEEXCHANGERATEID
                from @GIFTFEES GIFTFEES    
    ) as source (ID, FEE, WAIVED, BASECURRENCYID, ORGANIZATIONAMOUNT, ORGANIZATIONEXCHANGERATEID, TRANSACTIONAMOUNT, TRANSACTIONCURRENCYID, BASEEXCHANGERATEID)
    on (target.id = source.id)
    when matched then 
        update set
                 FEE = source.FEE
                ,WAIVED = source.WAIVED
                ,BASECURRENCYID = source.BASECURRENCYID
                ,ORGANIZATIONAMOUNT = source.ORGANIZATIONAMOUNT
                ,ORGANIZATIONEXCHANGERATEID = source.ORGANIZATIONEXCHANGERATEID
                ,TRANSACTIONAMOUNT = source.TRANSACTIONAMOUNT
                ,TRANSACTIONCURRENCYID = source.TRANSACTIONCURRENCYID
                ,BASEEXCHANGERATEID = source.BASEEXCHANGERATEID
                ,CHANGEDBYID = @CHANGEAGENTID
                ,DATECHANGED = @DATECHANGED
    when not matched then
        insert (ID, FEE, WAIVED, BASECURRENCYID, ORGANIZATIONAMOUNT, ORGANIZATIONEXCHANGERATEID, TRANSACTIONAMOUNT, TRANSACTIONCURRENCYID, BASEEXCHANGERATEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
        values (source.ID, source.FEE, source.WAIVED, source.BASECURRENCYID, source.ORGANIZATIONAMOUNT, source.ORGANIZATIONEXCHANGERATEID, source.TRANSACTIONAMOUNT, source.TRANSACTIONCURRENCYID, source.BASEEXCHANGERATEID, @CHANGEAGENTID, @CHANGEAGENTID, @DATECHANGED, @DATECHANGED);                

end