UFN_REVENUE_SHOULDUPDATEGIFTFEE

CREATE function dbo.UFN_REVENUE_SHOULDUPDATEGIFTFEE
(
  @REVENUEID uniqueidentifier,
  @CONSTITUENTID uniqueidentifier,
  @PAYMENTMETHOD tinyint,
  @TRANSACTIONCURRENCYID uniqueidentifier,
  @BASECURRENCYID uniqueidentifier,
  @BASEEXCHANGERATEID uniqueidentifier,
  @ORGANIZATIONEXCHANGERATEID uniqueidentifier,
  @REVENUESTREAMS xml,
  @RECEIPTAMOUNT money,
  @TRANSACTIONAMOUNT money
)
returns tinyint --0=No change, 1=Update existing, 2=Add new, 3=Remove existing

with execute as caller
as begin

    --If there is no default override code, never prompt

    if not exists (select 1 from dbo.GIFTFEEOVERRIDEREASONCODE where ISPAYMENTEDITDEFAULT=1 and ACTIVE=1)
        return 0;

    declare @SPLITS table
    (
        ID uniqueidentifier,
        APPLICATIONCODE tinyint,
        TRANSACTIONAMOUNT money,
        DESIGNATIONID uniqueidentifier
    )

    insert into @SPLITS (ID, APPLICATIONCODE, TRANSACTIONAMOUNT, DESIGNATIONID)
    select 
        T.c.value('(ID)[1]','uniqueidentifier') as ID, 
        T.c.value('(APPLICATIONCODE)[1]','tinyint') as APPLICATIONCODE,
        T.c.value('(APPLIED)[1]','money') as AMOUNT,
        T.c.value('(DESIGNATIONID)[1]','uniqueidentifier') as DESIGNATIONID
    from 
        @REVENUESTREAMS.nodes('REVENUESTREAMS/ITEM') T(c)

    declare @REVENUESPLITS table 
    (
        ID uniqueidentifier,
        TRANSACTIONAMOUNT money,
        APPLICATIONCODE int,
        DESIGNATIONLEVELID uniqueidentifier
    );

    insert into @REVENUESPLITS
    select
        REVENUESPLITSTABLE.ID,
        REVENUESPLITSTABLE.TRANSACTIONAMOUNT AS TRANSACTIONAMOUNT,
        case REVENUESPLITSTABLE.APPLICATIONCODE when 100 then 7 else REVENUESPLITSTABLE.APPLICATIONCODE end,
        coalesce(DESIGNATION.DESIGNATIONLEVEL5ID, DESIGNATION.DESIGNATIONLEVEL4ID, DESIGNATION.DESIGNATIONLEVEL3ID, DESIGNATION.DESIGNATIONLEVEL2ID, DESIGNATION.DESIGNATIONLEVEL2ID, DESIGNATION.DESIGNATIONLEVEL1ID) AS DESIGNATIONLEVELID
    from @SPLITS AS REVENUESPLITSTABLE
    left outer join dbo.DESIGNATION on REVENUESPLITSTABLE.DESIGNATIONID = DESIGNATION.ID

    declare @REVENUESPLITSXML xml;
    set @REVENUESPLITSXML = (select * from @REVENUESPLITS order by ID
        for xml raw('ITEM'),type,elements,root('SPLITS'),BINARY BASE64); 

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

        insert into @GIFTFEES
        select
                    ID, 
                    FEE,
                    WAIVED,
                    SPLITRECEIPTAMOUNT,
                    BASECURRENCYID,
                    ORGANIZATIONAMOUNT,
                    ORGANIZATIONEXCHANGERATEID,
                    TRANSACTIONAMOUNT,
                    TRANSACTIONCURRENCYID,
                    BASEEXCHANGERATEID
        from UFN_REVENUE_GENERATEDEFAULTGIFTFEESWITHTRANSACTIONAMOUNT_3(@REVENUEID,
                    @CONSTITUENTID,
                    @PAYMENTMETHOD,
                    @TRANSACTIONAMOUNT,
                    @RECEIPTAMOUNT,
                    @BASECURRENCYID,
                    @TRANSACTIONCURRENCYID,
                    @BASEEXCHANGERATEID,
                    @ORGANIZATIONEXCHANGERATEID,
                    @REVENUESPLITSXML,
                    null) -- ORGANIZATIONAMOUNT


    declare @GIFTFEECOUNTTEMP tinyint
    declare @GIFTFEESUMTEMP money

        --Compute the gift fee sum and number of records for addition/deletion/modification

        select
            @GIFTFEECOUNTTEMP = COUNT(*),
            @GIFTFEESUMTEMP = SUM(FEE)
        from @GIFTFEES;

    declare @GIFTFEECOUNT tinyint
        declare @GIFTFEESUM money

    select
        @GIFTFEECOUNT = COUNT(*),
                @GIFTFEESUM = SUM(FEE)
    from
        dbo.REVENUESPLITGIFTFEE
        inner join dbo.REVENUESPLIT on REVENUESPLITGIFTFEE.ID=REVENUESPLIT.ID
    where
        REVENUESPLIT.REVENUEID = @REVENUEID
        and REVENUESPLITGIFTFEE.WAIVED <> 1

        if @GIFTFEECOUNTTEMP > @GIFTFEECOUNT
            return 2;        --GIFT FEE ADDED

        else if @GIFTFEECOUNTTEMP < @GIFTFEECOUNT
            return 3;        --GIFT FEE REMOVED

        else
        begin
            if @GIFTFEESUMTEMP <> @GIFTFEESUM
                return 1;        --GIFT FEE UPDATED

        end

    return 0;
end