UFN_REVENUE_GENERATEDEFAULTGIFTFEESWITHTRANSACTIONAMOUNT_3

CREATE function dbo.UFN_REVENUE_GENERATEDEFAULTGIFTFEESWITHTRANSACTIONAMOUNT_3 (
    @REVENUEID uniqueidentifier
    ,@CONSTITUENTID uniqueidentifier
    ,@PAYMENTMETHOD tinyint
    ,@TRANSACTIONAMOUNT money
    ,@RECEIPTAMOUNT money
    ,@BASECURRENCYID uniqueidentifier
    ,@TRANSACTIONCURRENCYID uniqueidentifier
    ,@BASEEXCHANGERATEID uniqueidentifier
    ,@ORGANIZATIONEXCHANGERATEID uniqueidentifier
    ,@SPLITS xml
    ,@ORGANIZATIONAMOUNT money
    )
returns @GIFTFEES table (
    ID uniqueidentifier
    ,FEE money
    ,WAIVED bit
    ,SPLITRECEIPTAMOUNT money
    ,BASECURRENCYID uniqueidentifier
    ,ORGANIZATIONAMOUNT money
    ,ORGANIZATIONEXCHANGERATEID uniqueidentifier
    ,TRANSACTIONAMOUNT money
    ,TRANSACTIONCURRENCYID uniqueidentifier
    ,BASEEXCHANGERATEID uniqueidentifier
    )
as
begin
    --Do nothing if this is turned off.

    if dbo.UFN_GIFTFEE_ENABLED() = 0
        return;

    if @PAYMENTMETHOD is not null
    begin
        if dbo.UFN_GIFTFEE_PAYMENTMETHODEXCLUDED_2(@PAYMENTMETHOD) = 1
            return;
    end
    else
    begin
        if dbo.UFN_GIFTFEE_PAYMENTMETHODEXCLUDED(@REVENUEID) = 1
            return;
    end

    if dbo.UFN_GIFTFEE_CONSTITUENTEXCLUDED(@CONSTITUENTID) = 1
        return;

    declare @SPLITSTABLE table (
        ID uniqueidentifier
        ,TRANSACTIONAMOUNT money
        ,APPLICATIONCODE int
        ,DESIGNATIONLEVELID uniqueidentifier
        ,RECEIPTAMOUNT money
        ,ORGANIZATIONTOTALAMOUNT money
        ,BASECURRENCYID uniqueidentifier
        ,TRANSACTIONCURRENCYID uniqueidentifier
        ,BASEEXCHANGERATEID uniqueidentifier
        ,ORGANIZATIONEXCHANGERATEID uniqueidentifier
        );

    if @SPLITS is not null
    begin
        insert into @SPLITSTABLE
        select T.c.value('(ID)[1]', 'uniqueidentifier') as 'ID'
            ,T.c.value('(TRANSACTIONAMOUNT)[1]', 'money') as 'TRANSACTIONAMOUNT'
            ,T.c.value('(APPLICATIONCODE)[1]', 'int') as 'APPLICATIONCODE'
            ,T.c.value('(DESIGNATIONLEVELID)[1]', 'uniqueidentifier') as 'DESIGNATIONLEVELID'
            ,@RECEIPTAMOUNT as 'RECEIPTAMOUT'
            ,@ORGANIZATIONAMOUNT as 'ORGANIZATIONTOTALAMOUNT'
            ,@BASECURRENCYID as 'BASECURRENCYID'
            ,@TRANSACTIONCURRENCYID as 'TRANSACTIONCURRENCYID'
            ,@BASEEXCHANGERATEID as 'BASEEXCHANGERATEID'
            ,@ORGANIZATIONEXCHANGERATEID as 'ORGANIZATIONEXCHANGERATEID'
        from @SPLITS.nodes('/SPLITS/ITEM') T(c);

        declare @SPLITID uniqueidentifier;
        declare @SPLITAMOUNT money;
        declare @ORGANIZATIONTOTALAMOUNT money;
        declare @APPLICATIONCODE tinyint;
        declare @DESIGNATIONLEVELID uniqueidentifier;
        declare @FEETRANSACTIONAMOUNT money;
        declare @FEEBASEAMOUNT money;
        declare @FEEORGANIZATIONAMOUNT money;

        declare APPLICATIONCURSOR cursor local fast_forward
        for
        select *
        from @SPLITSTABLE

        open APPLICATIONCURSOR;

        fetch next
        from APPLICATIONCURSOR
        into @SPLITID
            ,@SPLITAMOUNT
            ,@APPLICATIONCODE
            ,@DESIGNATIONLEVELID
            ,@RECEIPTAMOUNT
            ,@ORGANIZATIONTOTALAMOUNT
            ,@BASECURRENCYID
            ,@TRANSACTIONCURRENCYID
            ,@BASEEXCHANGERATEID
            ,@ORGANIZATIONEXCHANGERATEID

        while (@@FETCH_STATUS = 0)
        begin
            --since receipt amount does not live on the split level we need to calculate receipt amount weight and apply

            --it to the split amount to account for this.

            --doesn't need this if APPLICATIONCODE is 10 (order) since the split amount will match receipt amount based on transaction type code

            -- TylerAr, 1/12/2011, NOTICE: the above strategy does NOT accurately reconstruct the receipt amounts

            -- on sales order items, receipt amounts are calculated based on many factors. This is an arbitrary approximation.

            --LeeCh, 1/2/2011, receipt amount is not always the same as split amount on an order.

            --if @APPLICATIONCODE <> 10

            --begin

            if @TRANSACTIONAMOUNT <= 0 --account for divide by zero

            begin
                set @SPLITAMOUNT = 0
                set @ORGANIZATIONTOTALAMOUNT = 0
            end
            else
            begin
                --This calculation can cause rounding issues but since the fees relative to the splits will

                --usually be small the rounding should be negligible.

                set @SPLITAMOUNT = (cast(@SPLITAMOUNT as decimal(30, 5)) * cast(@RECEIPTAMOUNT as decimal(30, 5))) / @TRANSACTIONAMOUNT

                if (@ORGANIZATIONAMOUNT is null)
                    set @ORGANIZATIONTOTALAMOUNT = (cast(@TRANSACTIONAMOUNT as decimal(30, 5)) * cast(@RECEIPTAMOUNT as decimal(30, 5))) / @TRANSACTIONAMOUNT
                else
                    set @ORGANIZATIONTOTALAMOUNT = (cast(@ORGANIZATIONTOTALAMOUNT as decimal(30, 5)) * cast(@RECEIPTAMOUNT as decimal(30, 5))) / @TRANSACTIONAMOUNT
            end

            --end

            -- Multicurrency RobertDi 6/1/2010 - This function requires a total amount in org currency

            --                                   and a split amount in transaction currency.

            --                                   It returns a fee in transaction currency.

            set @FEETRANSACTIONAMOUNT = dbo.UFN_PAYMENTAPPLICATION_GETGIFTFEE(@SPLITID, @SPLITAMOUNT, @ORGANIZATIONTOTALAMOUNT, @APPLICATIONCODE, @DESIGNATIONLEVELID);

            -- Get the fee amounts in other currencies.

            declare @DATE datetime;

            set @DATE = getdate();

            select @FEEBASEAMOUNT = BASEAMOUNT
                ,@FEEORGANIZATIONAMOUNT = ORGANIZATIONAMOUNT
                ,@BASEEXCHANGERATEID = BASEEXCHANGERATEID
                ,@TRANSACTIONCURRENCYID = TRANSACTIONCURRENCYID
            from dbo.UFN_CURRENCY_GETCURRENCYVALUES(@FEETRANSACTIONAMOUNT, @DATE, @BASECURRENCYID, @BASEEXCHANGERATEID, @TRANSACTIONCURRENCYID);

            --Insert gift fee

            if @FEETRANSACTIONAMOUNT is not null
                insert into @GIFTFEES (
                    ID
                    ,FEE
                    ,WAIVED
                    ,SPLITRECEIPTAMOUNT
                    ,BASECURRENCYID
                    ,ORGANIZATIONAMOUNT
                    ,ORGANIZATIONEXCHANGERATEID
                    ,TRANSACTIONAMOUNT
                    ,TRANSACTIONCURRENCYID
                    ,BASEEXCHANGERATEID
                    )
                values (
                    @SPLITID
                    ,@FEEBASEAMOUNT
                    ,case 
                        when @FEETRANSACTIONAMOUNT = 0
                            then 1
                        else 0
                        end
                    ,@SPLITAMOUNT
                    ,@BASECURRENCYID
                    ,@FEEORGANIZATIONAMOUNT
                    ,@ORGANIZATIONEXCHANGERATEID
                    ,@FEETRANSACTIONAMOUNT
                    ,@TRANSACTIONCURRENCYID
                    ,@BASEEXCHANGERATEID
                    );

            fetch next
            from APPLICATIONCURSOR
            into @SPLITID
                ,@SPLITAMOUNT
                ,@APPLICATIONCODE
                ,@DESIGNATIONLEVELID
                ,@RECEIPTAMOUNT
                ,@ORGANIZATIONTOTALAMOUNT
                ,@BASECURRENCYID
                ,@TRANSACTIONCURRENCYID
                ,@BASEEXCHANGERATEID
                ,@ORGANIZATIONEXCHANGERATEID;
        end

        close APPLICATIONCURSOR;

        deallocate APPLICATIONCURSOR;
    end

    return;
end