UFN_INSTALLMENTSPLIT_GETREVALUEDBALANCEINCURRENCY_BULK
Returns the unpaid balance, as a revalued amount in the given currency, for revenue in the system.
Return
| Return Type |
|---|
| table |
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @CURRENCYID | uniqueidentifier | IN | |
| @ORGANIZATIONCURRENCYID | uniqueidentifier | IN | |
| @DECIMALDIGITS | tinyint | IN | |
| @ROUNDINGTYPECODE | tinyint | IN | |
| @ASOFDATE | datetime | IN | |
| @ORIGINCODE | tinyint | IN | |
| @CURRENCYCODE | tinyint | IN |
Definition
Copy
CREATE function dbo.UFN_INSTALLMENTSPLIT_GETREVALUEDBALANCEINCURRENCY_BULK
(
-- If you want to convert to...
-- The base currency of each row:
-- set @CURRENCYCODE = 0
-- set @ORGANIZATIONCURRENCYID, @ASOFDATE, and @ORIGINCODE appropriately
-- The organization currency:
-- set @CURRENCYCODE = 1
-- set @ORGANIZATIONCURRENCYID, @ASOFDATE, and @ORIGINCODE appropriately
-- The transaction currency of each row:
-- set @CURRENCYCODE = 2
-- set @ORGANIZATIONCURRENCYID, @ASOFDATE and @ORIGINCODE appropriately
-- An arbitrary currency:
-- set @CURRENCYCODE = anything but 0 or 2
-- set @CURRENCYID = the currency's ID
-- set @DECIMALDIGITS and @ROUNDINGTYPECODE according to the currency
-- set @ORGANIZATIONCURRENCYID, @ASOFDATE and @ORIGINCODE appropriately
@CURRENCYID uniqueidentifier,
@ORGANIZATIONCURRENCYID uniqueidentifier,
@DECIMALDIGITS tinyint,
@ROUNDINGTYPECODE tinyint,
@ASOFDATE datetime,
@ORIGINCODE tinyint,
@CURRENCYCODE tinyint
)
returns table
as
return
(
with CTE_REVENUELASTINSTALLMENT
as(
select
REVENUEID,
max(SEQUENCE) LASTINSTALLMENTSEQUNCE
from dbo.INSTALLMENT
group by REVENUEID
),
CTE_INSTALLMENTSPLIT
as(
select
INSTALLMENTSPLIT.PLEDGEID,
INSTALLMENT.ID INSTALLMENTID,
INSTALLMENT.SEQUENCE INSTALLMENTSEQUENCE,
FINANCIALTRANSACTIONLINEITEM.ID SPLITID,
FINANCIALTRANSACTIONLINEITEM.SEQUENCE,
FINANCIALTRANSACTIONLINEITEM.TS,
INSTALLMENTSPLIT.ID,
INSTALLMENTSPLIT.TRANSACTIONAMOUNT
- coalesce((
select sum(INSTALLMENTSPLITPAYMENT.AMOUNT)
from INSTALLMENTSPLITPAYMENT
where INSTALLMENTSPLITPAYMENT.INSTALLMENTSPLITID = INSTALLMENTSPLIT.ID)
,0)
- coalesce((
select sum(INSTALLMENTSPLITWRITEOFF.TRANSACTIONAMOUNT)
from INSTALLMENTSPLITWRITEOFF
where INSTALLMENTSPLITWRITEOFF.INSTALLMENTSPLITID = INSTALLMENTSPLIT.ID)
,0)
as TRANSACTIONBALANCE,
INSTALLMENT.DATE,
INSTALLMENTSPLIT.DESIGNATIONID,
INSTALLMENTSPLIT.AMOUNT,
INSTALLMENTSPLIT.BASECURRENCYID,
INSTALLMENTSPLIT.ORGANIZATIONAMOUNT,
INSTALLMENTSPLIT.ORGANIZATIONEXCHANGERATEID,
INSTALLMENTSPLIT.TRANSACTIONAMOUNT,
INSTALLMENTSPLIT.TRANSACTIONCURRENCYID,
INSTALLMENTSPLIT.BASEEXCHANGERATEID
from dbo.INSTALLMENTSPLIT
inner join dbo.INSTALLMENT on INSTALLMENT.ID = INSTALLMENTSPLIT.INSTALLMENTID
inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = INSTALLMENTSPLIT.PLEDGEID
inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
where INSTALLMENTSPLIT.DESIGNATIONID = REVENUESPLIT_EXT.DESIGNATIONID
and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
and FINANCIALTRANSACTIONLINEITEM.TYPECODE != 1
)
--select *
--from CTE_INSTALLMENTSPLIT
,
CTE_INSTALLMENTROLLUP
as(
select
CTE_INSTALLMENTSPLIT.PLEDGEID,
CTE_INSTALLMENTSPLIT.INSTALLMENTID,
CTE_INSTALLMENTSPLIT.INSTALLMENTSEQUENCE,
CTE_INSTALLMENTSPLIT.SPLITID,
CTE_INSTALLMENTSPLIT.ID,
CTE_INSTALLMENTSPLIT.TRANSACTIONBALANCE,
coalesce(SUM(PREV.TRANSACTIONBALANCE),0) - CTE_INSTALLMENTSPLIT.TRANSACTIONBALANCE PREVIOUSTRANSACTIONRUNNINGTOTAL,
coalesce(SUM(PREV.TRANSACTIONBALANCE),0) TRANSACTIONRUNNINGTOTAL,
CTE_INSTALLMENTSPLIT.DATE,
CTE_INSTALLMENTSPLIT.DESIGNATIONID,
CTE_INSTALLMENTSPLIT.AMOUNT,
CTE_INSTALLMENTSPLIT.BASECURRENCYID,
CTE_INSTALLMENTSPLIT.ORGANIZATIONAMOUNT,
CTE_INSTALLMENTSPLIT.ORGANIZATIONEXCHANGERATEID,
CTE_INSTALLMENTSPLIT.TRANSACTIONAMOUNT,
CTE_INSTALLMENTSPLIT.TRANSACTIONCURRENCYID,
CTE_INSTALLMENTSPLIT.BASEEXCHANGERATEID
from CTE_INSTALLMENTSPLIT
cross join CTE_INSTALLMENTSPLIT PREV
where PREV.INSTALLMENTID = CTE_INSTALLMENTSPLIT.INSTALLMENTID
and(
PREV.SEQUENCE < CTE_INSTALLMENTSPLIT.SEQUENCE
or(
PREV.SEQUENCE = CTE_INSTALLMENTSPLIT.SEQUENCE
and PREV.TS <= CTE_INSTALLMENTSPLIT.TS
)
)
group by
CTE_INSTALLMENTSPLIT.PLEDGEID,
CTE_INSTALLMENTSPLIT.INSTALLMENTID,
CTE_INSTALLMENTSPLIT.INSTALLMENTSEQUENCE,
CTE_INSTALLMENTSPLIT.SPLITID,
CTE_INSTALLMENTSPLIT.ID,
CTE_INSTALLMENTSPLIT.TRANSACTIONBALANCE,
CTE_INSTALLMENTSPLIT.DATE,
CTE_INSTALLMENTSPLIT.DESIGNATIONID,
CTE_INSTALLMENTSPLIT.AMOUNT,
CTE_INSTALLMENTSPLIT.BASECURRENCYID,
CTE_INSTALLMENTSPLIT.ORGANIZATIONAMOUNT,
CTE_INSTALLMENTSPLIT.ORGANIZATIONEXCHANGERATEID,
CTE_INSTALLMENTSPLIT.TRANSACTIONAMOUNT,
CTE_INSTALLMENTSPLIT.TRANSACTIONCURRENCYID,
CTE_INSTALLMENTSPLIT.BASEEXCHANGERATEID
--cross apply(
-- select SUM(PREV.TRANSACTIONBALANCE) TRANSACTIONBALANCE
-- from CTE_INSTALLMENTSPLIT PREV
-- where PREV.INSTALLMENTID = CTE_INSTALLMENTSPLIT.INSTALLMENTID
-- and(
-- PREV.SEQUENCE < CTE_INSTALLMENTSPLIT.SEQUENCE
-- or(
-- PREV.SEQUENCE = CTE_INSTALLMENTSPLIT.SEQUENCE
-- and PREV.TS < CTE_INSTALLMENTSPLIT.TS
-- )
-- )
--) PREVS
)
--select * from CTE_INSTALLMENTROLLUP
,
CTE_WITHINSTALLMENTBALANCE
as(
select
CTE_INSTALLMENTROLLUP.PLEDGEID,
CTE_INSTALLMENTROLLUP.INSTALLMENTID,
CTE_INSTALLMENTROLLUP.INSTALLMENTSEQUENCE,
INSTALLMENT.TRANSACTIONBALANCE TRANSACTIONINSTALLMENTBALANCE,
INSTALLMENT.INTERMEDIATEBALANCE INTERMEDIATEINSTALLMENTBALANCE,
INSTALLMENT.BALANCEINCURRENCY INSTALLMENTBALANCEINCURRENCY,
INSTALLMENT.CURRENCYID,
CTE_INSTALLMENTROLLUP.SPLITID,
CTE_INSTALLMENTROLLUP.ID,
CTE_INSTALLMENTROLLUP.TRANSACTIONBALANCE,
CTE_INSTALLMENTROLLUP.PREVIOUSTRANSACTIONRUNNINGTOTAL,
CTE_INSTALLMENTROLLUP.TRANSACTIONRUNNINGTOTAL,
CTE_INSTALLMENTROLLUP.DATE,
CTE_INSTALLMENTROLLUP.DESIGNATIONID,
CTE_INSTALLMENTROLLUP.AMOUNT,
CTE_INSTALLMENTROLLUP.BASECURRENCYID,
CTE_INSTALLMENTROLLUP.ORGANIZATIONAMOUNT,
CTE_INSTALLMENTROLLUP.ORGANIZATIONEXCHANGERATEID,
CTE_INSTALLMENTROLLUP.TRANSACTIONAMOUNT,
CTE_INSTALLMENTROLLUP.TRANSACTIONCURRENCYID,
CTE_INSTALLMENTROLLUP.BASEEXCHANGERATEID
from CTE_INSTALLMENTROLLUP
inner join dbo.UFN_INSTALLMENT_GETREVALUEDBALANCEINCURRENCY_BULK(
@CURRENCYID,
@ORGANIZATIONCURRENCYID,
@DECIMALDIGITS,
@ROUNDINGTYPECODE,
@ASOFDATE,
@ORIGINCODE,
@CURRENCYCODE
) INSTALLMENT on INSTALLMENT.ID = CTE_INSTALLMENTROLLUP.INSTALLMENTID
)
--select * From CTE_WITHINSTALLMENTBALANCE
,
CTE_BALANCEBYINSTALLMENT
as(
select
CTE_WITHINSTALLMENTBALANCE.PLEDGEID,
CTE_WITHINSTALLMENTBALANCE.INSTALLMENTID,
CTE_WITHINSTALLMENTBALANCE.INSTALLMENTSEQUENCE,
CTE_WITHINSTALLMENTBALANCE.TRANSACTIONINSTALLMENTBALANCE,
CTE_WITHINSTALLMENTBALANCE.INSTALLMENTBALANCEINCURRENCY,
CTE_WITHINSTALLMENTBALANCE.CURRENCYID,
CTE_WITHINSTALLMENTBALANCE.SPLITID,
CTE_WITHINSTALLMENTBALANCE.ID,
CTE_WITHINSTALLMENTBALANCE.TRANSACTIONBALANCE,
case
when CTE_WITHINSTALLMENTBALANCE.TRANSACTIONINSTALLMENTBALANCE = 0
or CTE_WITHINSTALLMENTBALANCE.INSTALLMENTBALANCEINCURRENCY = 0
or CTE_WITHINSTALLMENTBALANCE.INSTALLMENTSEQUENCE = CTE_REVENUELASTINSTALLMENT.LASTINSTALLMENTSEQUNCE
then convert(money,0)
when CTE_WITHINSTALLMENTBALANCE.TRANSACTIONINSTALLMENTBALANCE = CTE_WITHINSTALLMENTBALANCE.INSTALLMENTBALANCEINCURRENCY
then CTE_WITHINSTALLMENTBALANCE.TRANSACTIONBALANCE
when CTE_WITHINSTALLMENTBALANCE.INTERMEDIATEINSTALLMENTBALANCE = 0
then
dbo.UFN_CURRENCY_ROUND(
convert(decimal(20,8),CTE_WITHINSTALLMENTBALANCE.TRANSACTIONRUNNINGTOTAL)
/ CTE_WITHINSTALLMENTBALANCE.TRANSACTIONINSTALLMENTBALANCE
* CTE_WITHINSTALLMENTBALANCE.INSTALLMENTBALANCEINCURRENCY
,@DECIMALDIGITS,@ROUNDINGTYPECODE)
- coalesce(dbo.UFN_CURRENCY_ROUND(
convert(decimal(20,8),CTE_WITHINSTALLMENTBALANCE.PREVIOUSTRANSACTIONRUNNINGTOTAL)
/ CTE_WITHINSTALLMENTBALANCE.TRANSACTIONINSTALLMENTBALANCE
* CTE_WITHINSTALLMENTBALANCE.INSTALLMENTBALANCEINCURRENCY
,@DECIMALDIGITS,@ROUNDINGTYPECODE)
,0)
else
dbo.UFN_CURRENCY_ROUND(
convert(decimal(20,8),
dbo.UFN_CURRENCY_ROUND(
convert(decimal(20,8),CTE_WITHINSTALLMENTBALANCE.TRANSACTIONRUNNINGTOTAL)
/ CTE_WITHINSTALLMENTBALANCE.TRANSACTIONINSTALLMENTBALANCE
* CTE_WITHINSTALLMENTBALANCE.INTERMEDIATEINSTALLMENTBALANCE
,@DECIMALDIGITS,@ROUNDINGTYPECODE)
)
/ CTE_WITHINSTALLMENTBALANCE.INTERMEDIATEINSTALLMENTBALANCE
* CTE_WITHINSTALLMENTBALANCE.INSTALLMENTBALANCEINCURRENCY
,@DECIMALDIGITS,@ROUNDINGTYPECODE)
- coalesce(dbo.UFN_CURRENCY_ROUND(
convert(decimal(20,8),
dbo.UFN_CURRENCY_ROUND(
convert(decimal(20,8),CTE_WITHINSTALLMENTBALANCE.PREVIOUSTRANSACTIONRUNNINGTOTAL)
/ CTE_WITHINSTALLMENTBALANCE.TRANSACTIONINSTALLMENTBALANCE
* CTE_WITHINSTALLMENTBALANCE.INTERMEDIATEINSTALLMENTBALANCE
,@DECIMALDIGITS,@ROUNDINGTYPECODE)
)
/ CTE_WITHINSTALLMENTBALANCE.INTERMEDIATEINSTALLMENTBALANCE
* CTE_WITHINSTALLMENTBALANCE.INSTALLMENTBALANCEINCURRENCY
,@DECIMALDIGITS,@ROUNDINGTYPECODE)
,0)
end BALANCEINCURRENCY,
CTE_REVENUELASTINSTALLMENT.LASTINSTALLMENTSEQUNCE,
CTE_WITHINSTALLMENTBALANCE.DATE,
CTE_WITHINSTALLMENTBALANCE.DESIGNATIONID,
CTE_WITHINSTALLMENTBALANCE.AMOUNT,
CTE_WITHINSTALLMENTBALANCE.BASECURRENCYID,
CTE_WITHINSTALLMENTBALANCE.ORGANIZATIONAMOUNT,
CTE_WITHINSTALLMENTBALANCE.ORGANIZATIONEXCHANGERATEID,
CTE_WITHINSTALLMENTBALANCE.TRANSACTIONAMOUNT,
CTE_WITHINSTALLMENTBALANCE.TRANSACTIONCURRENCYID,
CTE_WITHINSTALLMENTBALANCE.BASEEXCHANGERATEID
from CTE_WITHINSTALLMENTBALANCE
inner join CTE_REVENUELASTINSTALLMENT on CTE_REVENUELASTINSTALLMENT.REVENUEID = CTE_WITHINSTALLMENTBALANCE.PLEDGEID
)
--select * From CTE_BALANCEBYINSTALLMENT
,
CTE_SPLITROLLUP
as(
select
CTE_BALANCEBYINSTALLMENT.SPLITID,
sum(CTE_BALANCEBYINSTALLMENT.BALANCEINCURRENCY) BALANCEINCURRENCYROLLUP,
CTE_BALANCEBYINSTALLMENT.CURRENCYID,
CTE_BALANCEBYINSTALLMENT.LASTINSTALLMENTSEQUNCE
from CTE_BALANCEBYINSTALLMENT
group by
CTE_BALANCEBYINSTALLMENT.SPLITID,
CTE_BALANCEBYINSTALLMENT.CURRENCYID,
CTE_BALANCEBYINSTALLMENT.LASTINSTALLMENTSEQUNCE
)
select
CTE_BALANCEBYINSTALLMENT.ID,
CTE_BALANCEBYINSTALLMENT.BALANCEINCURRENCY,
CTE_BALANCEBYINSTALLMENT.TRANSACTIONBALANCE,
CTE_BALANCEBYINSTALLMENT.CURRENCYID,
CTE_BALANCEBYINSTALLMENT.DATE,
CTE_BALANCEBYINSTALLMENT.INSTALLMENTID,
CTE_BALANCEBYINSTALLMENT.PLEDGEID,
CTE_BALANCEBYINSTALLMENT.DESIGNATIONID,
CTE_BALANCEBYINSTALLMENT.AMOUNT,
CTE_BALANCEBYINSTALLMENT.BASECURRENCYID,
CTE_BALANCEBYINSTALLMENT.ORGANIZATIONAMOUNT,
CTE_BALANCEBYINSTALLMENT.ORGANIZATIONEXCHANGERATEID,
CTE_BALANCEBYINSTALLMENT.TRANSACTIONAMOUNT,
CTE_BALANCEBYINSTALLMENT.TRANSACTIONCURRENCYID,
CTE_BALANCEBYINSTALLMENT.BASEEXCHANGERATEID,
CTE_BALANCEBYINSTALLMENT.SPLITID
from CTE_BALANCEBYINSTALLMENT
where CTE_BALANCEBYINSTALLMENT.INSTALLMENTSEQUENCE < CTE_BALANCEBYINSTALLMENT.LASTINSTALLMENTSEQUNCE
union all
select
CTE_INSTALLMENTSPLIT.ID,
SPLIT.BALANCEINCURRENCY - CTE_SPLITROLLUP.BALANCEINCURRENCYROLLUP BALANCEINCURRENCY,
CTE_INSTALLMENTSPLIT.TRANSACTIONBALANCE,
CTE_SPLITROLLUP.CURRENCYID,
CTE_INSTALLMENTSPLIT.DATE,
CTE_INSTALLMENTSPLIT.INSTALLMENTID,
CTE_INSTALLMENTSPLIT.PLEDGEID,
CTE_INSTALLMENTSPLIT.DESIGNATIONID,
CTE_INSTALLMENTSPLIT.AMOUNT,
CTE_INSTALLMENTSPLIT.BASECURRENCYID,
CTE_INSTALLMENTSPLIT.ORGANIZATIONAMOUNT,
CTE_INSTALLMENTSPLIT.ORGANIZATIONEXCHANGERATEID,
CTE_INSTALLMENTSPLIT.TRANSACTIONAMOUNT,
CTE_INSTALLMENTSPLIT.TRANSACTIONCURRENCYID,
CTE_INSTALLMENTSPLIT.BASEEXCHANGERATEID,
CTE_INSTALLMENTSPLIT.SPLITID
from CTE_SPLITROLLUP
inner join CTE_INSTALLMENTSPLIT on CTE_INSTALLMENTSPLIT.SPLITID = CTE_SPLITROLLUP.SPLITID
inner join dbo.UFN_SPLIT_GETREVALUEDBALANCEINCURRENCY_BULK(
@CURRENCYID,
@ORGANIZATIONCURRENCYID,
@DECIMALDIGITS,
@ROUNDINGTYPECODE,
@ASOFDATE,
@ORIGINCODE,
@CURRENCYCODE
) SPLIT on CTE_INSTALLMENTSPLIT.SPLITID = SPLIT.ID
where CTE_INSTALLMENTSPLIT.INSTALLMENTSEQUENCE = CTE_SPLITROLLUP.LASTINSTALLMENTSEQUNCE
)