UFN_GIFTAIDREVENUESPLIT_CALCULATEPLEDGETAXCLAIMAMOUNT

            CREATE function dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATEPLEDGETAXCLAIMAMOUNT
            (
                @PLEDGESPLITID uniqueidentifier
            )
            returns table
            as
            return
            (
                select 
                    coalesce(sum(INSTALLMENTSPLITAMOUNT * (BASETAXRATE / (100 - BASETAXRATE))), 0) as BASETAXCLAIMAMOUNT,
                    coalesce(sum(INSTALLMENTSPLITAMOUNT * ((BASETAXRATE + TRANSITIONALTAXRATE) / (100 - (BASETAXRATE + TRANSITIONALTAXRATE)))), 0) - coalesce(sum(INSTALLMENTSPLITAMOUNT * (BASETAXRATE / (100 - BASETAXRATE))), 0) as TRANSITIONALTAXCLAIMAMOUNT
                from
                (
                    select
                        INSTALLMENTSPLITAMOUNT,
                        case 
                            when ELIGIBILEANDVALIDDECLARATION = 1 then dbo.UFN_GETGIFTAIDBASETAXRATE(INSTALLMENTS.DATE)
                            else 0
                        end BASETAXRATE,
                        case 
                            when ELIGIBILEANDVALIDDECLARATION = 1 then dbo.UFN_GETGIFTAIDTRANSITIONALTAXRATE(INSTALLMENTS.DATE)
                            else 0
                        end TRANSITIONALTAXRATE
                    from
                    (
                        select 
                            INSTALLMENT.DATE,
                            INSTALLMENTSPLIT.AMOUNT - coalesce((select sum(AMOUNT) from dbo.INSTALLMENTSPLITWRITEOFF where INSTALLMENTSPLITID = INSTALLMENTSPLIT.ID), 0) as INSTALLMENTSPLITAMOUNT,
                            case when (REVENUESPLITGIFTAID.RULES_STATUS & REVENUESPLITGIFTAID.ATTRIBUTES_STATUS) = 1 and (dbo.UFN_VALIDDECLARATION(INSTALLMENT.DATE, REVENUE.CONSTITUENTID, REVENUESPLIT.DESIGNATIONID, null, null) = 2 or ISCOVENANT = 1)
                                then 1
                                else 0
                            end ELIGIBILEANDVALIDDECLARATION
                        from dbo.REVENUESPLIT
                        inner join dbo.REVENUE on REVENUESPLIT.REVENUEID = REVENUE.ID
                        inner join dbo.INSTALLMENTSPLIT on REVENUESPLIT.REVENUEID = INSTALLMENTSPLIT.PLEDGEID and REVENUESPLIT.DESIGNATIONID = INSTALLMENTSPLIT.DESIGNATIONID
                        inner join dbo.INSTALLMENT on INSTALLMENTSPLIT.INSTALLMENTID = INSTALLMENT.ID
                        left join dbo.REVENUESPLITGIFTAID on REVENUESPLIT.ID = REVENUESPLITGIFTAID.ID
                        where 
                            REVENUESPLIT.ID = @PLEDGESPLITID and
                            REVENUESPLITGIFTAID.DECLINESGIFTAID = 0
                    ) as INSTALLMENTS
                ) as INSTALLMENTSWITHRATE
            )