UFN_GIFTAIDREVENUESPLIT_CALCULATEPLEDGESPLITGROSSAMOUNTBALANCE

            create function dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATEPLEDGESPLITGROSSAMOUNTBALANCE
                        (
                            @PLEDGESPLITID uniqueidentifier,
                            @CURRENCYID uniqueidentifier
                        )
                        returns money
                        with execute as caller
                        as begin

                  declare @PLEDGESPLITGROSSAMOUNTBALANCE money

                if @CURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY()
                  begin 
                    select @PLEDGESPLITGROSSAMOUNTBALANCE = FINANCIALTRANSACTIONLINEITEM.ORGAMOUNT - coalesce(PAYMENTS.AMOUNT, 0) - coalesce(WRITEOFF.TOTALAMOUNT, 0)
                            + coalesce(TAXCLAIM.BASETAXCLAIMAMOUNT + TAXCLAIM.TRANSITIONALTAXCLAIMAMOUNT, 0)
                    from dbo.FINANCIALTRANSACTIONLINEITEM
                    left join (
                        select INSTALLMENTSPLIT.REVENUESPLITID,
                                SUM(dbo.UFN_INSTALLMENTSPLITPAYMENT_GETAMOUNTINCURRENCY(INSTALLMENTSPLITPAYMENT.ID, @CURRENCYID)) AMOUNT
                        from dbo.INSTALLMENTSPLITPAYMENT
                        inner join dbo.INSTALLMENTSPLIT on INSTALLMENTSPLITPAYMENT.INSTALLMENTSPLITID = INSTALLMENTSPLIT.ID
                        inner join dbo.FINANCIALTRANSACTIONLINEITEM on INSTALLMENTSPLIT.REVENUESPLITID = FINANCIALTRANSACTIONLINEITEM.ID
                        inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
                        group by INSTALLMENTSPLIT.REVENUESPLITID
                    ) as PAYMENTS on PAYMENTS.REVENUESPLITID = FINANCIALTRANSACTIONLINEITEM.ID
                    left join (
                        select INSTALLMENTSPLIT.REVENUESPLITID, SUM(INSTALLMENTSPLITWRITEOFF.ORGANIZATIONAMOUNT) as TOTALAMOUNT
                        from dbo.INSTALLMENTSPLITWRITEOFF
                        inner join dbo.INSTALLMENTSPLIT on INSTALLMENTSPLITWRITEOFF.INSTALLMENTSPLITID = INSTALLMENTSPLIT.ID
                        group by INSTALLMENTSPLIT.REVENUESPLITID
                    ) as WRITEOFF on FINANCIALTRANSACTIONLINEITEM.ID = WRITEOFF.REVENUESPLITID
                    cross apply (
                        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.ORGANIZATIONAMOUNT 
                                    - coalesce((select sum(dbo.UFN_INSTALLMENTSPLITPAYMENT_GETAMOUNTINCURRENCY(INSTALLMENTSPLITPAYMENT.ID, @CURRENCYID))
                                                    from dbo.INSTALLMENTSPLITPAYMENT
                                                    where INSTALLMENTSPLITPAYMENT.INSTALLMENTSPLITID = INSTALLMENTSPLIT.ID
                                                    ), 0) 
                                    - coalesce((select sum(ORGANIZATIONAMOUNT) 
                                                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, FINANCIALTRANSACTION.CONSTITUENTID, REVENUESPLIT_EXT.DESIGNATIONID, null, null) = 2 or REVENUESPLITGIFTAID.ISCOVENANT = 1)
                                        then 1
                                        else 0
                                    end ELIGIBILEANDVALIDDECLARATION
                                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.FINANCIALTRANSACTIONID = INSTALLMENTSPLIT.PLEDGEID and REVENUESPLIT_EXT.DESIGNATIONID = INSTALLMENTSPLIT.DESIGNATIONID
                                inner join dbo.INSTALLMENT on INSTALLMENTSPLIT.INSTALLMENTID = INSTALLMENT.ID
                                left join dbo.REVENUESPLITGIFTAID on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLITGIFTAID.ID
                                where 
                                    FINANCIALTRANSACTIONLINEITEM.ID = @PLEDGESPLITID and
                                    REVENUESPLITGIFTAID.DECLINESGIFTAID = 0 and
                                    FINANCIALTRANSACTION.DELETEDON is null
                                ) as INSTALLMENTS
                            ) as INSTALLMENTSWITHRATE
                        ) as TAXCLAIM
                    where FINANCIALTRANSACTIONLINEITEM.ID = @PLEDGESPLITID
                  end
                  else if @CURRENCYID = (select CURRENCYSET.BASECURRENCYID
                    from dbo.FINANCIALTRANSACTIONLINEITEM inner join DBO.FINANCIALTRANSACTION on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
                    inner join dbo.PDACCOUNTSYSTEM on FINANCIALTRANSACTION.PDACCOUNTSYSTEMID = PDACCOUNTSYSTEM.ID
                    inner join dbo.CURRENCYSET on PDACCOUNTSYSTEM.CURRENCYSETID = CURRENCYSET.ID
                    where FINANCIALTRANSACTIONLINEITEM.ID = @PLEDGESPLITID)
                  begin                  
                    select @PLEDGESPLITGROSSAMOUNTBALANCE = FINANCIALTRANSACTIONLINEITEM.BASEAMOUNT - coalesce(PAYMENTS.AMOUNT, 0) - coalesce(WRITEOFF.TOTALAMOUNT, 0)
                            + coalesce(TAXCLAIM.BASETAXCLAIMAMOUNT + TAXCLAIM.TRANSITIONALTAXCLAIMAMOUNT, 0)
                    from dbo.FINANCIALTRANSACTIONLINEITEM
                    left join (
                        select INSTALLMENTSPLIT.REVENUESPLITID,
                                SUM(dbo.UFN_INSTALLMENTSPLITPAYMENT_GETAMOUNTINCURRENCY(INSTALLMENTSPLITPAYMENT.ID, @CURRENCYID)) AMOUNT
                        from dbo.INSTALLMENTSPLITPAYMENT
                        inner join dbo.INSTALLMENTSPLIT on INSTALLMENTSPLITPAYMENT.INSTALLMENTSPLITID = INSTALLMENTSPLIT.ID
                        inner join dbo.FINANCIALTRANSACTIONLINEITEM on INSTALLMENTSPLIT.REVENUESPLITID = FINANCIALTRANSACTIONLINEITEM.ID
                        inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
                        group by INSTALLMENTSPLIT.REVENUESPLITID
                    ) as PAYMENTS on PAYMENTS.REVENUESPLITID = FINANCIALTRANSACTIONLINEITEM.ID
                    left join (
                        select INSTALLMENTSPLIT.REVENUESPLITID, SUM(INSTALLMENTSPLITWRITEOFF.AMOUNT) as TOTALAMOUNT
                        from dbo.INSTALLMENTSPLITWRITEOFF
                        inner join dbo.INSTALLMENTSPLIT on INSTALLMENTSPLITWRITEOFF.INSTALLMENTSPLITID = INSTALLMENTSPLIT.ID
                        group by INSTALLMENTSPLIT.REVENUESPLITID
                    ) as WRITEOFF on FINANCIALTRANSACTIONLINEITEM.ID = WRITEOFF.REVENUESPLITID
                    cross apply (
                        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(dbo.UFN_INSTALLMENTSPLITPAYMENT_GETAMOUNTINCURRENCY(INSTALLMENTSPLITPAYMENT.ID, @CURRENCYID))
                                                from dbo.INSTALLMENTSPLITPAYMENT
                                                where INSTALLMENTSPLITPAYMENT.INSTALLMENTSPLITID = INSTALLMENTSPLIT.ID
                                                ), 0)  
                                    - 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, FINANCIALTRANSACTION.CONSTITUENTID, REVENUESPLIT_EXT.DESIGNATIONID, null, null) = 2 or REVENUESPLITGIFTAID.ISCOVENANT = 1)
                                        then 1
                                        else 0
                                    end ELIGIBILEANDVALIDDECLARATION
                                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.FINANCIALTRANSACTIONID = INSTALLMENTSPLIT.PLEDGEID and REVENUESPLIT_EXT.DESIGNATIONID = INSTALLMENTSPLIT.DESIGNATIONID
                                inner join dbo.INSTALLMENT on INSTALLMENTSPLIT.INSTALLMENTID = INSTALLMENT.ID
                                left join dbo.REVENUESPLITGIFTAID on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLITGIFTAID.ID
                                where 
                                    FINANCIALTRANSACTIONLINEITEM.ID = @PLEDGESPLITID and
                                    REVENUESPLITGIFTAID.DECLINESGIFTAID = 0 and
                                    FINANCIALTRANSACTION.DELETEDON is null
                                ) as INSTALLMENTS
                            ) as INSTALLMENTSWITHRATE
                        ) as TAXCLAIM
                    where FINANCIALTRANSACTIONLINEITEM.ID = @PLEDGESPLITID
                  end
                  else if @CURRENCYID = (select FINANCIALTRANSACTION.TRANSACTIONCURRENCYID 
                    from dbo.FINANCIALTRANSACTIONLINEITEM inner join FINANCIALTRANSACTION on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
                    where FINANCIALTRANSACTIONLINEITEM.ID = @PLEDGESPLITID)
                  begin
                    select @PLEDGESPLITGROSSAMOUNTBALANCE = FINANCIALTRANSACTIONLINEITEM.TRANSACTIONAMOUNT - coalesce(PAYMENTS.AMOUNT, 0) - coalesce(WRITEOFF.TOTALAMOUNT, 0)
                            + coalesce(TAXCLAIM.BASETAXCLAIMAMOUNT + TAXCLAIM.TRANSITIONALTAXCLAIMAMOUNT, 0)
                    from dbo.FINANCIALTRANSACTIONLINEITEM
                    left join (
                        select INSTALLMENTSPLIT.REVENUESPLITID,
                                SUM(INSTALLMENTSPLITPAYMENT.AMOUNT) AMOUNT
                        from dbo.INSTALLMENTSPLITPAYMENT
                        inner join dbo.INSTALLMENTSPLIT on INSTALLMENTSPLITPAYMENT.INSTALLMENTSPLITID = INSTALLMENTSPLIT.ID
                        group by INSTALLMENTSPLIT.REVENUESPLITID
                    ) as PAYMENTS on PAYMENTS.REVENUESPLITID = FINANCIALTRANSACTIONLINEITEM.ID
                    left join (
                        select INSTALLMENTSPLIT.REVENUESPLITID, SUM(INSTALLMENTSPLITWRITEOFF.TRANSACTIONAMOUNT) as TOTALAMOUNT
                        from dbo.INSTALLMENTSPLITWRITEOFF
                        inner join dbo.INSTALLMENTSPLIT on INSTALLMENTSPLITWRITEOFF.INSTALLMENTSPLITID = INSTALLMENTSPLIT.ID
                        group by INSTALLMENTSPLIT.REVENUESPLITID
                    ) as WRITEOFF on FINANCIALTRANSACTIONLINEITEM.ID = WRITEOFF.REVENUESPLITID
                    cross apply (
                        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.TRANSACTIONAMOUNT 
                                    - coalesce((select sum(INSTALLMENTSPLITPAYMENT.AMOUNT)
                                                from dbo.INSTALLMENTSPLITPAYMENT
                                                where INSTALLMENTSPLITPAYMENT.INSTALLMENTSPLITID = INSTALLMENTSPLIT.ID
                                                ), 0)  
                                    - coalesce((select sum(TRANSACTIONAMOUNT) 
                                                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, FINANCIALTRANSACTION.CONSTITUENTID, REVENUESPLIT_EXT.DESIGNATIONID, null, null) = 2 or REVENUESPLITGIFTAID.ISCOVENANT = 1)
                                        then 1
                                        else 0
                                    end ELIGIBILEANDVALIDDECLARATION
                                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.FINANCIALTRANSACTIONID = INSTALLMENTSPLIT.PLEDGEID and REVENUESPLIT_EXT.DESIGNATIONID = INSTALLMENTSPLIT.DESIGNATIONID
                                inner join dbo.INSTALLMENT on INSTALLMENTSPLIT.INSTALLMENTID = INSTALLMENT.ID
                                left join dbo.REVENUESPLITGIFTAID on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLITGIFTAID.ID
                                where 
                                    FINANCIALTRANSACTIONLINEITEM.ID = @PLEDGESPLITID and
                                    REVENUESPLITGIFTAID.DECLINESGIFTAID = 0 and
                                    FINANCIALTRANSACTION.DELETEDON is null
                                ) as INSTALLMENTS
                            ) as INSTALLMENTSWITHRATE
                        ) as TAXCLAIM
                    where FINANCIALTRANSACTIONLINEITEM.ID = @PLEDGESPLITID
                 end
                 else
                 begin
                    select @PLEDGESPLITGROSSAMOUNTBALANCE = FINANCIALTRANSACTIONLINEITEM.ORGAMOUNT - coalesce(PAYMENTS.AMOUNT, 0) - coalesce(WRITEOFF.TOTALAMOUNT, 0)
                            + coalesce(TAXCLAIM.BASETAXCLAIMAMOUNT + TAXCLAIM.TRANSITIONALTAXCLAIMAMOUNT, 0)
                    from dbo.FINANCIALTRANSACTIONLINEITEM
                    left join (
                        select INSTALLMENTSPLIT.REVENUESPLITID,
                                SUM(dbo.UFN_INSTALLMENTSPLITPAYMENT_GETAMOUNTINCURRENCY(INSTALLMENTSPLITPAYMENT.ID, @CURRENCYID)) AMOUNT
                        from dbo.INSTALLMENTSPLITPAYMENT
                        inner join dbo.INSTALLMENTSPLIT on INSTALLMENTSPLITPAYMENT.INSTALLMENTSPLITID = INSTALLMENTSPLIT.ID
                        inner join dbo.FINANCIALTRANSACTIONLINEITEM on INSTALLMENTSPLIT.REVENUESPLITID = FINANCIALTRANSACTIONLINEITEM.ID
                        inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
                        group by INSTALLMENTSPLIT.REVENUESPLITID
                    ) as PAYMENTS on PAYMENTS.REVENUESPLITID = FINANCIALTRANSACTIONLINEITEM.ID
                    left join (
                        select INSTALLMENTSPLIT.REVENUESPLITID, SUM(INSTALLMENTSPLITWRITEOFF.ORGANIZATIONAMOUNT) as TOTALAMOUNT
                        from dbo.INSTALLMENTSPLITWRITEOFF
                        inner join dbo.INSTALLMENTSPLIT on INSTALLMENTSPLITWRITEOFF.INSTALLMENTSPLITID = INSTALLMENTSPLIT.ID
                        group by INSTALLMENTSPLIT.REVENUESPLITID
                    ) as WRITEOFF on FINANCIALTRANSACTIONLINEITEM.ID = WRITEOFF.REVENUESPLITID
                    cross apply (
                        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.ORGANIZATIONAMOUNT 
                                    - coalesce((select sum(dbo.UFN_INSTALLMENTSPLITPAYMENT_GETAMOUNTINCURRENCY(INSTALLMENTSPLITPAYMENT.ID, @CURRENCYID))
                                                from dbo.INSTALLMENTSPLITPAYMENT
                                                where INSTALLMENTSPLITPAYMENT.INSTALLMENTSPLITID = INSTALLMENTSPLIT.ID
                                                ), 0) 
                                    - coalesce((select sum(ORGANIZATIONAMOUNT) 
                                                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, FINANCIALTRANSACTION.CONSTITUENTID, REVENUESPLIT_EXT.DESIGNATIONID, null, null) = 2 or REVENUESPLITGIFTAID.ISCOVENANT = 1)
                                        then 1
                                        else 0
                                    end ELIGIBILEANDVALIDDECLARATION
                                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.FINANCIALTRANSACTIONID = INSTALLMENTSPLIT.PLEDGEID and REVENUESPLIT_EXT.DESIGNATIONID = INSTALLMENTSPLIT.DESIGNATIONID
                                inner join dbo.INSTALLMENT on INSTALLMENTSPLIT.INSTALLMENTID = INSTALLMENT.ID
                                left join dbo.REVENUESPLITGIFTAID on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLITGIFTAID.ID
                                where 
                                    FINANCIALTRANSACTIONLINEITEM.ID = @PLEDGESPLITID and
                                    REVENUESPLITGIFTAID.DECLINESGIFTAID = 0 and
                                    FINANCIALTRANSACTION.DELETEDON is null
                                ) as INSTALLMENTS
                            ) as INSTALLMENTSWITHRATE
                        ) as TAXCLAIM
                    where FINANCIALTRANSACTIONLINEITEM.ID = @PLEDGESPLITID;

                    declare @RECORDDATE date = (select FINANCIALTRANSACTION.DATE from dbo.FINANCIALTRANSACTIONLINEITEM 
                                  inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
                                  where FINANCIALTRANSACTIONLINEITEM.ID = @PLEDGESPLITID);

                      declare @CURRENCYEXCHANGERATEID uniqueidentifier;
                              set @CURRENCYEXCHANGERATEID = dbo.UFN_CURRENCYEXCHANGERATE_GETLATESTINCLUDEEXPIRED(dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY(), @CURRENCYID, @RECORDDATE, 1, null);
                              if (@CURRENCYEXCHANGERATEID is not null)
                      begin
                          set @PLEDGESPLITGROSSAMOUNTBALANCE =  dbo.UFN_CURRENCY_CONVERT(@PLEDGESPLITGROSSAMOUNTBALANCE, @CURRENCYEXCHANGERATEID)
                      end
                      else
                              begin
                                set @CURRENCYEXCHANGERATEID = dbo.UFN_CURRENCYEXCHANGERATE_GETLATESTINCLUDEEXPIRED(@CURRENCYID, dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY(), @RECORDDATE, 1, null);
                                set @PLEDGESPLITGROSSAMOUNTBALANCE =  dbo.UFN_CURRENCY_CONVERTINVERSE(@PLEDGESPLITGROSSAMOUNTBALANCE, @CURRENCYEXCHANGERATEID)
                      end


                 end
                            return @PLEDGESPLITGROSSAMOUNTBALANCE

                end