UFN_GIFTAID_CALCULATETAXCLAIMAMOUNT

CREATE function dbo.UFN_GIFTAID_CALCULATETAXCLAIMAMOUNT
(
    @SPLITID uniqueidentifier
)
returns @TAXCLAIMAMOUNTS table
(
    BASETAXCLAIMAMOUNT money,
    TRANSITIONALTAXCLAIMAMOUNT money,
    TRANSACTIONBASETAXCLAIMAMOUNT money,
    TRANSACTIONTRANSITIONALTAXCLAIMAMOUNT money,
    ORGANIZATIONBASETAXCLAIMAMOUNT money,
    ORGANIZATIONTRANSITIONALTAXCLAIMAMOUNT money
)
as
begin
    declare @REVENUEID uniqueidentifier;
    declare @CONSTITUENTID uniqueidentifier;
    declare @TRANSACTIONTYPECODE tinyint;
    declare @DESIGNATIONID uniqueidentifier;
    declare @DATE datetime;
    declare @AMOUNT money;
    declare @REGISTRANTID uniqueidentifier;
    declare @MEMBERSHIPID uniqueidentifier;

    declare @STATUS bit;    
    declare @ELIGIBILITY tinyint;

    declare @BASECURRENCYID uniqueidentifier;
    declare @TRANSACTIONCURRENCYID uniqueidentifier;
    declare @BASEEXCHANGERATEID uniqueidentifier;
    declare @ORGANIZATIONEXCHANGERATEID uniqueidentifier;

    select
        @REVENUEID = REVENUE.ID,
        @CONSTITUENTID = REVENUE.CONSTITUENTID,
        @TRANSACTIONTYPECODE = REVENUE.TRANSACTIONTYPECODE,
        @DATE = REVENUE.DATE,
        @DESIGNATIONID = REVENUESPLIT.DESIGNATIONID,
        @AMOUNT = REVENUESPLIT.TRANSACTIONAMOUNT,        
        @STATUS = (REVENUESPLITGIFTAID.RULES_STATUS & REVENUESPLITGIFTAID.ATTRIBUTES_STATUS) ^ REVENUESPLITGIFTAID.DECLINESGIFTAID,
        @BASECURRENCYID = REVENUESPLIT.BASECURRENCYID,
        @TRANSACTIONCURRENCYID = REVENUESPLIT.TRANSACTIONCURRENCYID,
        @BASEEXCHANGERATEID = REVENUESPLIT.BASEEXCHANGERATEID,
        @ORGANIZATIONEXCHANGERATEID = REVENUESPLIT.ORGANIZATIONEXCHANGERATEID
    from dbo.REVENUESPLIT
    inner join dbo.REVENUESPLITGIFTAID
        on REVENUESPLIT.ID = REVENUESPLITGIFTAID.ID
    inner join dbo.REVENUE
        on REVENUESPLIT.REVENUEID = REVENUE.ID
    where REVENUESPLIT.ID = @SPLITID;

    declare @ORGANIZATIONCURRENCYID uniqueidentifier;
    set @ORGANIZATIONCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();

    if @BASECURRENCYID is null
        set @BASECURRENCYID = @ORGANIZATIONCURRENCYID;

    declare @DECIMALDIGITS tinyint;
    declare @ROUNDINGTYPECODE tinyint;

    set @DECIMALDIGITS = 2;
    set @ROUNDINGTYPECODE = 0;

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

    set @ELIGIBILITY = dbo.UFN_VALIDDECLARATION(@DATE, @CONSTITUENTID, @DESIGNATIONID, @REGISTRANTID, @MEMBERSHIPID);

    --if qualified and not 'Not eligible', calculate tax claim amount

    if @STATUS = 1 and @ELIGIBILITY != 1
        if @TRANSACTIONTYPECODE = 0 or @TRANSACTIONTYPECODE = 2
            begin
                declare @BASERATE numeric(30,6);
                declare @TRANSITIONALRATE numeric(30,6);
                declare @RATE numeric(30,6);

                set @BASERATE = dbo.UFN_GETGIFTAIDBASETAXRATE(@DATE);
                set @TRANSITIONALRATE = dbo.UFN_GETGIFTAIDTRANSITIONALTAXRATE(@DATE);
                set @RATE = @BASERATE + @TRANSITIONALRATE

                declare @BASETAXCLAIMAMOUNT money = 0;
                declare @TRANSITIONALTAXCLAIMAMOUNT money = 0;
                declare @TRANSACTIONBASETAXCLAIMAMOUNT money = 0;
                declare @TRANSACTIONTRANSITIONALTAXCLAIMAMOUNT money = 0;
                declare @ORGANIZATIONBASETAXCLAIMAMOUNT money = 0;
                declare @ORGANIZATIONTRANSITIONALTAXCLAIMAMOUNT money = 0;

                set @TRANSACTIONBASETAXCLAIMAMOUNT = dbo.UFN_CURRENCY_ROUND(@AMOUNT * (@BASERATE / (100 - @BASERATE)), @DECIMALDIGITS, @ROUNDINGTYPECODE);
                set @TRANSACTIONTRANSITIONALTAXCLAIMAMOUNT = dbo.UFN_CURRENCY_ROUND((@AMOUNT * (@RATE / (100 - @RATE))) - @TRANSACTIONBASETAXCLAIMAMOUNT, @DECIMALDIGITS, @ROUNDINGTYPECODE);

                select @BASETAXCLAIMAMOUNT = CURRENCYVALUES.BASEAMOUNT, @ORGANIZATIONBASETAXCLAIMAMOUNT = CURRENCYVALUES.ORGANIZATIONAMOUNT
                from dbo.UFN_CURRENCY_GETCURRENCYVALUES_2(@TRANSACTIONBASETAXCLAIMAMOUNT,
                                                            @DATE,
                                                            @BASECURRENCYID,
                                                            @BASEEXCHANGERATEID,
                                                            @TRANSACTIONCURRENCYID,
                                                            null,
                                                            @ORGANIZATIONCURRENCYID,
                                                            null, 
                                                            @ORGANIZATIONEXCHANGERATEID, 
                                                            0) as CURRENCYVALUES;

                select @TRANSITIONALTAXCLAIMAMOUNT = CURRENCYVALUES.BASEAMOUNT, @ORGANIZATIONTRANSITIONALTAXCLAIMAMOUNT = CURRENCYVALUES.ORGANIZATIONAMOUNT
                from dbo.UFN_CURRENCY_GETCURRENCYVALUES_2(@TRANSACTIONTRANSITIONALTAXCLAIMAMOUNT,
                                                            @DATE,
                                                            @BASECURRENCYID,
                                                            @BASEEXCHANGERATEID,
                                                            @TRANSACTIONCURRENCYID,
                                                            null,
                                                            @ORGANIZATIONCURRENCYID,
                                                            null, 
                                                            @ORGANIZATIONEXCHANGERATEID, 
                                                            0) as CURRENCYVALUES;

                insert into @TAXCLAIMAMOUNTS (BASETAXCLAIMAMOUNT, TRANSITIONALTAXCLAIMAMOUNT, TRANSACTIONBASETAXCLAIMAMOUNT, TRANSACTIONTRANSITIONALTAXCLAIMAMOUNT, ORGANIZATIONBASETAXCLAIMAMOUNT, ORGANIZATIONTRANSITIONALTAXCLAIMAMOUNT)
                values (@BASETAXCLAIMAMOUNT, @TRANSITIONALTAXCLAIMAMOUNT, @TRANSACTIONBASETAXCLAIMAMOUNT, @TRANSACTIONTRANSITIONALTAXCLAIMAMOUNT, @ORGANIZATIONBASETAXCLAIMAMOUNT, @ORGANIZATIONTRANSITIONALTAXCLAIMAMOUNT)
            end

    --pledge has its own gross amount calculation, so it has to be a special case

    if @TRANSACTIONTYPECODE = 1 and @STATUS = 1
    begin
        insert into @TAXCLAIMAMOUNTS (BASETAXCLAIMAMOUNT, TRANSITIONALTAXCLAIMAMOUNT, TRANSACTIONBASETAXCLAIMAMOUNT, TRANSACTIONTRANSITIONALTAXCLAIMAMOUNT, ORGANIZATIONBASETAXCLAIMAMOUNT, ORGANIZATIONTRANSITIONALTAXCLAIMAMOUNT)
        select BASEAMOUNT.BASETAXCLAIMAMOUNT, BASEAMOUNT.TRANSITIONALTAXCLAIMAMOUNT, TRANSACTIONAMOUNT.BASETAXCLAIMAMOUNT, TRANSACTIONAMOUNT.TRANSITIONALTAXCLAIMAMOUNT, ORGANIZATIONAMOUNT.BASETAXCLAIMAMOUNT, ORGANIZATIONAMOUNT.TRANSITIONALTAXCLAIMAMOUNT
        from dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATEPLEDGETAXCLAIMAMOUNT_2(@SPLITID, 0) as BASEAMOUNT
        cross apply dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATEPLEDGETAXCLAIMAMOUNT_2(@SPLITID, 1) as TRANSACTIONAMOUNT
        cross apply dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATEPLEDGETAXCLAIMAMOUNT_2(@SPLITID, 2) as ORGANIZATIONAMOUNT

        declare @BASEDECIMALDIGITS tinyint;
        declare @BASEROUNDINGTYPECODE tinyint;
        declare @ORGANIZATIONDECIMALDIGITS tinyint;
        declare @ORGANIZATIONROUNDINGTYPECODE tinyint;

        set @BASEDECIMALDIGITS = 2;
        set @BASEROUNDINGTYPECODE = 0;
        set @ORGANIZATIONDECIMALDIGITS = 2;
        set @ORGANIZATIONROUNDINGTYPECODE = 0;

        select
            @BASEDECIMALDIGITS = DECIMALDIGITS, 
            @BASEROUNDINGTYPECODE = ROUNDINGTYPECODE
        from dbo.UFN_CURRENCY_GETPROPERTIES(@BASECURRENCYID);

        select
            @ORGANIZATIONDECIMALDIGITS = DECIMALDIGITS, 
            @ORGANIZATIONROUNDINGTYPECODE = ROUNDINGTYPECODE
        from dbo.UFN_CURRENCY_GETPROPERTIES(@ORGANIZATIONCURRENCYID);

        update @TAXCLAIMAMOUNTS set
            BASETAXCLAIMAMOUNT = dbo.UFN_CURRENCY_ROUND(coalesce(BASETAXCLAIMAMOUNT, 0), @BASEDECIMALDIGITS, @BASEROUNDINGTYPECODE),
            TRANSITIONALTAXCLAIMAMOUNT = dbo.UFN_CURRENCY_ROUND(coalesce(TRANSITIONALTAXCLAIMAMOUNT, 0), @BASEDECIMALDIGITS, @BASEROUNDINGTYPECODE),
            TRANSACTIONBASETAXCLAIMAMOUNT = dbo.UFN_CURRENCY_ROUND(coalesce(TRANSACTIONBASETAXCLAIMAMOUNT, 0), @DECIMALDIGITS, @ROUNDINGTYPECODE),
            TRANSACTIONTRANSITIONALTAXCLAIMAMOUNT = dbo.UFN_CURRENCY_ROUND(coalesce(TRANSACTIONTRANSITIONALTAXCLAIMAMOUNT, 0), @DECIMALDIGITS, @ROUNDINGTYPECODE),
            ORGANIZATIONBASETAXCLAIMAMOUNT = dbo.UFN_CURRENCY_ROUND(coalesce(ORGANIZATIONBASETAXCLAIMAMOUNT, 0), @ORGANIZATIONDECIMALDIGITS, @ORGANIZATIONROUNDINGTYPECODE),
            ORGANIZATIONTRANSITIONALTAXCLAIMAMOUNT = dbo.UFN_CURRENCY_ROUND(coalesce(ORGANIZATIONTRANSITIONALTAXCLAIMAMOUNT, 0), @ORGANIZATIONDECIMALDIGITS, @ORGANIZATIONROUNDINGTYPECODE)
    end

    return
end