UFN_PAYMENTAPPLICATION_GETGIFTFEE

CREATE function dbo.UFN_PAYMENTAPPLICATION_GETGIFTFEE(
        @SPLITID uniqueidentifier,
        @SPLITAMOUNT money,                -- SPLITAMOUNT must be an amount in the transaction currency.

        @TOTALAMOUNT money,                -- TOTALAMOUNT must be an amount in the organization currency.

        @APPLICATIONCODE tinyint,
        @DESIGNATIONLEVELID uniqueidentifier)
returns money
as

begin
/*
    returns null when no fee is accessed
    returns 0# when the fee is waived

*/

declare @PLEDGEID uniqueidentifier;
declare @FEEPERCENT numeric(10,5);
declare @FEE money;                        -- Fee amount in transaction currency.

declare @USECUSTOM bit;
declare @APPLYFEE bit;
declare @WAIVED bit

--Set default values

select @WAIVED = 0, @FEEPERCENT = 0.00, @USECUSTOM = 0, @FEE = 0.00;

--no fees possible

if @SPLITAMOUNT = 0 
    return null;

--Check if gift fee enabled globally

if dbo.UFN_GIFTFEE_ENABLED() = 0
    return null;

--check if application type is covered

set @APPLYFEE = dbo.UFN_GIFTFEE_APPLICATIONCODEINCLUDED(@APPLICATIONCODE, @DESIGNATIONLEVELID);

--If this is a pledge check for overrides on pledge

if @APPLICATIONCODE = 2 --Pledge

begin
    select top 1 @PLEDGEID = PLEDGEID 
    from dbo.INSTALLMENTSPLITPAYMENT where PAYMENTID = @SPLITID;

    select 
        @APPLYFEE = 1,
        @FEEPERCENT = CUSTOMFEE/100,
        @WAIVED = WAIVEFEE,
        @USECUSTOM = USECUSTOM
    from dbo.PLEDGEGIFTFEEOVERRIDE
    where ID = @PLEDGEID and 
        (WAIVEFEE = 1 or USECUSTOM = 1);
end

--This application type does not have gift fees

--and there is no override on the pledge

if @APPLYFEE = 0 
    return null;

--Otherwise use setup

if @USECUSTOM = 0 and @WAIVED = 0
begin
    if dbo.UFN_DESIGNATIONLEVELGIFTFEE_ENABLED(@DESIGNATIONLEVELID) = 1
    begin
        -- Multicurrency - RobertDi 6/1/2010 - Gift fee "from amounts" are expressed in organization

        --                                     currency, so TOTALAMOUNT must be in organization currency.

        --                    RobBr 9/22/2010 - Gift fee "from amounts" can now be in the base currency

        --                                        of the designation level, so we will use ORGANIZATIONFROMAMOUNT

        --                                        for comparison.


        --Use structure from designation level if it exists otherwise use defaults

        if dbo.UFN_DESIGNATIONLEVELGIFTFEE_OVERRIDESTRUCTURE(@DESIGNATIONLEVELID) = 1
            select top 1 
                @FEEPERCENT = FEE/100
            from dbo.DESIGNATIONLEVELGIFTFEESTRUCTURE
            where DESIGNATIONLEVELID = @DESIGNATIONLEVELID
                    and ORGANIZATIONFROMAMOUNT <= @TOTALAMOUNT
            order by ORGANIZATIONFROMAMOUNT desc;
        else
        begin
            select top 1 
                @FEEPERCENT = FEE/100
            from dbo.GIFTFEESTRUCTURE
            where FROMAMOUNT <= @TOTALAMOUNT
            order by FROMAMOUNT desc;
        end
    end
end

-- Calculate and round the fee amount.


declare @CURRENCYID uniqueidentifier;
select @CURRENCYID = TRANSACTIONCURRENCYID from dbo.REVENUESPLIT where ID = @SPLITID;

declare @DECIMALDIGITS tinyint;
declare @ROUNDINGTYPECODE tinyint;

select
    @DECIMALDIGITS = DECIMALDIGITS, 
    @ROUNDINGTYPECODE = ROUNDINGTYPECODE
from dbo.UFN_CURRENCY_GETPROPERTIES(@CURRENCYID);

declare @PAYMENTID uniqueidentifier;
declare @TOTALAMOUNT_TRANSACTION money;

if exists(select 1 from dbo.FINANCIALTRANSACTIONLINEITEM LI where LI.ID = @SPLITID)
begin
    select 
          @PAYMENTID = FINANCIALTRANSACTIONID 
         ,@TOTALAMOUNT_TRANSACTION = FT.TRANSACTIONAMOUNT
    from FINANCIALTRANSACTIONLINEITEM FT where ID = @SPLITID

    declare @SPLITSXML xml = (
            select ftli.ID, ftli.TRANSACTIONAMOUNT AMOUNT
            from FINANCIALTRANSACTION ft 
            inner join FINANCIALTRANSACTIONLINEITEM ftli on ft.ID = ftli.FINANCIALTRANSACTIONID
            inner join dbo.REVENUESPLIT_EXT RSE on RSE.ID = ftli.ID
            where ft.ID = @PAYMENTID and ftli.DELETEDON is null and ftli.TYPECODE != 1
            for xml raw('ITEM'),type,elements,root('AMOUNTSTOPRORATE'),BINARY BASE64)

    set @FEE = (
            select AMOUNT 
            from dbo.UFN_SPLITS_PRORATEAMOUNTS(@TOTALAMOUNT_TRANSACTION, (@SPLITAMOUNT * @FEEPERCENT), @DECIMALDIGITS, @SPLITSXML)
            where ID = @SPLITID
    );
end
else
    set @FEE = (@SPLITAMOUNT * @FEEPERCENT);

if @WAIVED = 1
    return 0;

if @FEE > 0
    return @FEE

return null;

end