UFN_PLEDGEINSTALLMENTSPLIT_CALCULATETAXCLAIMAMOUNTSINCURRENCY_BULK

            CREATE function [dbo].[UFN_PLEDGEINSTALLMENTSPLIT_CALCULATETAXCLAIMAMOUNTSINCURRENCY_BULK]
            (
                @CURRENCYID uniqueidentifier,
                @ORGANIZATIONCURRENCYID uniqueidentifier,
                @DECIMALDIGITS tinyint,
                @ROUNDINGTYPECODE tinyint
            )
            returns table
            as
            return
            (
                select
                    INSTALLMENTSWITHRATE.REVENUESPLITID,
                    coalesce(sum(INSTALLMENTSPLITAMOUNT * ((BASETAXRATE + TRANSITIONALTAXRATE) / (100 - (BASETAXRATE + TRANSITIONALTAXRATE)))), 0) as TAXCLAIMAMOUNTINCURRENCY
                from
                (
                    select
                        INSTALLMENTS.REVENUESPLITID,
                        INSTALLMENTS.INSTALLMENTSPLITAMOUNT,
                        case 
                            when ELIGIBILEANDVALIDDECLARATION = 1 then (select TAXRATE from dbo.UFN_GETGIFTAIDBASETAXRATE_INLINE(INSTALLMENTS.DATE))
                            else 0
                        end BASETAXRATE,
                        case 
                            when ELIGIBILEANDVALIDDECLARATION = 1 then (select TAXRATE from dbo.UFN_GETGIFTAIDTRANSITIONALTAXRATE_INLINE(INSTALLMENTS.DATE))
                            else 0
                        end TRANSITIONALTAXRATE
                    from
                    (
                        select 
                            INSTALLMENT.DATE,
                            case
                                when (@CURRENCYID is null) or (@CURRENCYID = @ORGANIZATIONCURRENCYID)
                                    then INSTALLMENTSPLIT.ORGANIZATIONAMOUNT - coalesce((select sum(ORGANIZATIONAMOUNT) from dbo.INSTALLMENTSPLITWRITEOFF where INSTALLMENTSPLITID = INSTALLMENTSPLIT.ID), 0)
                                else INSTALLMENTSPLIT.AMOUNT - coalesce((select sum(AMOUNT) from dbo.INSTALLMENTSPLITWRITEOFF where INSTALLMENTSPLITID = INSTALLMENTSPLIT.ID), 0)
                            end as INSTALLMENTSPLITAMOUNT,
                            case when (REVENUESPLITGIFTAID.RULES_STATUS & REVENUESPLITGIFTAID.ATTRIBUTES_STATUS) = 1 and (dbo.UFN_VALIDDECLARATION(INSTALLMENT.DATE, FINANCIALTRANSACTION.CONSTITUENTID, REVENUESPLIT_EXT.DESIGNATIONID, null, null) = 2 or ISCOVENANT = 1)
                                then 1
                                else 0
                            end ELIGIBILEANDVALIDDECLARATION,
                            FINANCIALTRANSACTIONLINEITEM.ID REVENUESPLITID
                        from dbo.FINANCIALTRANSACTIONLINEITEM 
                          inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = FINANCIALTRANSACTIONLINEITEM.ID
                            inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
                            inner join dbo.INSTALLMENTSPLIT on FINANCIALTRANSACTIONLINEITEM.ID = INSTALLMENTSPLIT.REVENUESPLITID
                            inner join dbo.INSTALLMENT on INSTALLMENTSPLIT.INSTALLMENTID = INSTALLMENT.ID
                            left join dbo.REVENUESPLITGIFTAID on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLITGIFTAID.ID
                        where 
                            REVENUESPLITGIFTAID.DECLINESGIFTAID = 0
                            and (
                                (@CURRENCYID is null) 
                                or (@CURRENCYID = @ORGANIZATIONCURRENCYID)
                                or (@CURRENCYID = INSTALLMENTSPLIT.BASECURRENCYID)
                            )
                            and ((FINANCIALTRANSACTION.TYPECODE between 0 and 9) or (FINANCIALTRANSACTION.TYPECODE = 15)) -- revenue

                            and FINANCIALTRANSACTION.DELETEDON is null
                            and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
                            and FINANCIALTRANSACTIONLINEITEM.TYPECODE = 0 -- standard                                                                                    

                    ) as INSTALLMENTS
                ) as INSTALLMENTSWITHRATE
                group by INSTALLMENTSWITHRATE.REVENUESPLITID

                union all

                select
                    INSTALLMENTSWITHRATE.REVENUESPLITID,
                    coalesce(sum(INSTALLMENTSPLITAMOUNT * ((BASETAXRATE + TRANSITIONALTAXRATE) / (100 - (BASETAXRATE + TRANSITIONALTAXRATE)))), 0) as TAXCLAIMAMOUNTINCURRENCY
                from
                (
                    select
                        INSTALLMENTS.REVENUESPLITID,
                        INSTALLMENTS.INSTALLMENTSPLITAMOUNT,
                        case 
                            when ELIGIBILEANDVALIDDECLARATION = 1 then (select TAXRATE from dbo.UFN_GETGIFTAIDBASETAXRATE_INLINE(INSTALLMENTS.DATE))
                            else 0
                        end BASETAXRATE,
                        case 
                            when ELIGIBILEANDVALIDDECLARATION = 1 then (select TAXRATE from dbo.UFN_GETGIFTAIDTRANSITIONALTAXRATE_INLINE(INSTALLMENTS.DATE))
                            else 0
                        end TRANSITIONALTAXRATE
                    from
                    (
                        select 
                            INSTALLMENT.DATE,
                            case
                                when [LATESTORGANIZATIONEXCHANGERATE].RATE is not null
                                    then dbo.UFN_CURRENCY_ROUND(
                                        dbo.UFN_CURRENCY_APPLYRATE(INSTALLMENTSPLIT.AMOUNT - coalesce((select sum(AMOUNT) from dbo.INSTALLMENTSPLITWRITEOFF where INSTALLMENTSPLITID = INSTALLMENTSPLIT.ID), 0), [LATESTORGANIZATIONEXCHANGERATE].RATE), 
                                        @DECIMALDIGITS, @ROUNDINGTYPECODE)
                                when [LATESTINVERSEORGANIZATIONEXCHANGERATE].RATE is not null
                                    then dbo.UFN_CURRENCY_ROUND(
                                        dbo.UFN_CURRENCY_APPLYRATE(INSTALLMENTSPLIT.AMOUNT - coalesce((select sum(AMOUNT) from dbo.INSTALLMENTSPLITWRITEOFF where INSTALLMENTSPLITID = INSTALLMENTSPLIT.ID), 0), cast((1 / [LATESTINVERSEORGANIZATIONEXCHANGERATE].RATE) as decimal(20,8))), 
                                        @DECIMALDIGITS, @ROUNDINGTYPECODE)
                                else 0
                            end as INSTALLMENTSPLITAMOUNT,
                            case when (REVENUESPLITGIFTAID.RULES_STATUS & REVENUESPLITGIFTAID.ATTRIBUTES_STATUS) = 1 and (dbo.UFN_VALIDDECLARATION(INSTALLMENT.DATE, FINANCIALTRANSACTION.CONSTITUENTID, REVENUESPLIT_EXT.DESIGNATIONID, null, null) = 2 or ISCOVENANT = 1)
                                then 1
                                else 0
                            end ELIGIBILEANDVALIDDECLARATION,
                            FINANCIALTRANSACTIONLINEITEM.ID REVENUESPLITID
                        from dbo.FINANCIALTRANSACTIONLINEITEM
                          inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
                            inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID= FINANCIALTRANSACTION.ID
                            inner join dbo.INSTALLMENTSPLIT on FINANCIALTRANSACTIONLINEITEM.ID = INSTALLMENTSPLIT.REVENUESPLITID
                            inner join dbo.INSTALLMENT on INSTALLMENTSPLIT.INSTALLMENTID = INSTALLMENT.ID
                            left join dbo.REVENUESPLITGIFTAID on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLITGIFTAID.ID
                            left outer join dbo.CURRENCYEXCHANGERATE as LATESTORGANIZATIONEXCHANGERATE
                            on @ORGANIZATIONCURRENCYID = LATESTORGANIZATIONEXCHANGERATE.FROMCURRENCYID
                                    and @CURRENCYID = LATESTORGANIZATIONEXCHANGERATE.TOCURRENCYID
                                    and LATESTORGANIZATIONEXCHANGERATE.TYPECODE in (0,1)
                                    and LATESTORGANIZATIONEXCHANGERATE.ISLATESTRATEFORASOFDATE = 1
                                    and dateadd(ms, 86399996, FINANCIALTRANSACTION.DATE) >= LATESTORGANIZATIONEXCHANGERATE.ASOFDATESDTZ
                  and dateadd(ms, 86399996, FINANCIALTRANSACTION.DATE) <= LATESTORGANIZATIONEXCHANGERATE.NEXTRATEASOFDATEORMAXSDTZ
              left outer join dbo.CURRENCYEXCHANGERATE as LATESTINVERSEORGANIZATIONEXCHANGERATE
                                on @CURRENCYID = LATESTINVERSEORGANIZATIONEXCHANGERATE.FROMCURRENCYID
                                    and @ORGANIZATIONCURRENCYID= LATESTINVERSEORGANIZATIONEXCHANGERATE.TOCURRENCYID
                                    and LATESTINVERSEORGANIZATIONEXCHANGERATE.TYPECODE in (0,1)
                                    and LATESTINVERSEORGANIZATIONEXCHANGERATE.ISLATESTRATEFORASOFDATE = 1
                                    and dateadd(ms, 86399996, FINANCIALTRANSACTION.DATE) >= LATESTINVERSEORGANIZATIONEXCHANGERATE.ASOFDATESDTZ
                  and dateadd(ms, 86399996, FINANCIALTRANSACTION.DATE) <= LATESTINVERSEORGANIZATIONEXCHANGERATE.NEXTRATEASOFDATEORMAXSDTZ
                        where 
                            REVENUESPLITGIFTAID.DECLINESGIFTAID = 0
                            and (
                                (@CURRENCYID is not null) 
                                and (@CURRENCYID <> @ORGANIZATIONCURRENCYID)
                                and (@CURRENCYID <> REVENUESPLITGIFTAID.BASECURRENCYID)
                            )
                    ) as INSTALLMENTS
                ) as INSTALLMENTSWITHRATE
                group by INSTALLMENTSWITHRATE.REVENUESPLITID
            )