UFN_PLEDGE_GETREVALUEDSPLITBALANCESINCURRENCY_BULK_2
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 | |
| @DESIGNATIONID | uniqueidentifier | IN | |
| @STARTDATE | datetime | IN | |
| @ENDDATE | datetime | IN | |
| @STARTDATEEARLIEST | datetime | IN | |
| @ENDDATELATEST | datetime | IN |
Definition
Copy
CREATE function dbo.UFN_PLEDGE_GETREVALUEDSPLITBALANCESINCURRENCY_BULK_2(
-- 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,
@DESIGNATIONID uniqueidentifier,
@STARTDATE datetime,
@ENDDATE datetime,
@STARTDATEEARLIEST datetime,
@ENDDATELATEST datetime
)
returns table
as return(
with REVENUECTE
as
(
select
FT.ID,
FT.TRANSACTIONAMOUNT
- coalesce(
(
select sum(INSTALLMENTSPLITPAYMENT.AMOUNT) as AMOUNT
from dbo.INSTALLMENTSPLITPAYMENT
inner join dbo.FINANCIALTRANSACTIONLINEITEM as PAYMENTLINEITEM on PAYMENTLINEITEM.ID = INSTALLMENTSPLITPAYMENT.PAYMENTID
inner join dbo.FINANCIALTRANSACTION PAYMENT on PAYMENT.ID = PAYMENTLINEITEM.FINANCIALTRANSACTIONID
where
PAYMENT.CALCULATEDDATE <= @ASOFDATE and
PLEDGEID = FT.ID
and PAYMENT.TYPECODE in (0,1,2,3,4,5,6,7,8,9,15)
and PAYMENT.DELETEDON is NULL
),0)
- coalesce((
select sum(INSTALLMENTSPLITWRITEOFF.TRANSACTIONAMOUNT) AMOUNT
from dbo.INSTALLMENTSPLITWRITEOFF
inner join dbo.FINANCIALTRANSACTION as WRITEOFF on INSTALLMENTSPLITWRITEOFF.WRITEOFFID = WRITEOFF.ID
where
WRITEOFF.CALCULATEDDATE <= @ASOFDATE and
WRITEOFF.PARENTID = FT.ID
and WRITEOFF.TYPECODE = 20
and WRITEOFF.DELETEDON is NULL
),0)
as BALANCEINCURRENCY,
FT.TRANSACTIONCURRENCYID,
FTBC.BASECURRENCYID,
FT.CALCULATEDDATE [DATE],
FT.TYPECODE,
BASEEXCHANGERATE.RATE as ORIGINALBASERATE,
ORGEXCHANGERATE.RATE as ORIGINALORGANIZATIONRATE,
case @CURRENCYCODE
when 0 then FTBC.BASECURRENCYID
when 1 then @ORGANIZATIONCURRENCYID
when 2 then FT.TRANSACTIONCURRENCYID
else @CURRENCYID
end as CURRENCYID
from dbo.FINANCIALTRANSACTION as FT with (nolock)
inner join (select F.ID from dbo.FINANCIALTRANSACTION F
join dbo.FINANCIALTRANSACTIONLINEITEM L on L.FINANCIALTRANSACTIONID = F.ID
join dbo.REVENUESPLIT_EXT R on L.ID = R.ID where L.TYPECODE != 1 and
R.DESIGNATIONID = ISNULL(@DESIGNATIONID,'99999999-9999-9999-9999-999999999999')
and (@STARTDATE is null or F.CALCULATEDDATE >= @STARTDATEEARLIEST)
and (@ENDDATE is null or F.CALCULATEDDATE <= @ENDDATELATEST)
) V1 on V1.ID = FT.ID
inner join dbo.V_BASECURRENCYFORFINANCIALTRANSACTION_I as FTBC on FT.ID = FTBC.FINANCIALTRANSACTIONID
left join dbo.CURRENCYEXCHANGERATE BASEEXCHANGERATE on BASEEXCHANGERATE.ID = FT.BASEEXCHANGERATEID
left join dbo.CURRENCYEXCHANGERATE ORGEXCHANGERATE on ORGEXCHANGERATE.ID = FT.ORGEXCHANGERATEID
where
FT.TYPECODE in (1,3,4,6,8)
and FT.DELETEDON is null
),
CTE_PLEDGEBALANCE as(
select -- Transaction currency
REVENUECTE.ID,
REVENUECTE.BALANCEINCURRENCY,
REVENUECTE.BALANCEINCURRENCY TRANSACTIONBALANCE,
REVENUECTE.CURRENCYID
from REVENUECTE
where (REVENUECTE.CURRENCYID is null)
or @CURRENCYCODE = 2 --We want transaction balance
or ( --We want base balance and transaction = base currency
@CURRENCYCODE = 0
and REVENUECTE.TRANSACTIONCURRENCYID = REVENUECTE.BASECURRENCYID
)
or ( --We want organization balance.
@CURRENCYCODE = 1
and (
( --The origin is T->B->O, and transaction = base = organization currency
@ORIGINCODE = 0
and REVENUECTE.TRANSACTIONCURRENCYID = REVENUECTE.BASECURRENCYID
and REVENUECTE.BASECURRENCYID = @ORGANIZATIONCURRENCYID
)
or( --The origin is T->O, and transaction = organization currency
@ORIGINCODE = 1
and REVENUECTE.TRANSACTIONCURRENCYID = @ORGANIZATIONCURRENCYID
)
)
)
or ( --We want an arbitrary balance and transaction = arbitrary currency
(@CURRENCYCODE is null or not @CURRENCYCODE in (0,1,2))
and REVENUECTE.TRANSACTIONCURRENCYID = REVENUECTE.CURRENCYID
)
or REVENUECTE.BALANCEINCURRENCY is null
or REVENUECTE.BALANCEINCURRENCY = 0
union all -- Base or org currency
select
REVENUECTE.ID,
case
when REVENUECTE.CURRENCYID = REVENUECTE.BASECURRENCYID
then dbo.UFN_CURRENCY_ROUND(dbo.UFN_CURRENCY_APPLYRATE(REVENUECTE.BALANCEINCURRENCY, coalesce(REVALUATION.BASERATE,REVENUECTE.ORIGINALBASERATE)), BASECURRENCY.DECIMALDIGITS, BASECURRENCY.ROUNDINGTYPECODE)
when REVENUECTE.CURRENCYID = @ORGANIZATIONCURRENCYID
then
case @ORIGINCODE
when 0
then
case
when REVENUECTE.TRANSACTIONCURRENCYID = REVENUECTE.BASECURRENCYID
then dbo.UFN_CURRENCY_ROUND(dbo.UFN_CURRENCY_APPLYRATE(REVENUECTE.BALANCEINCURRENCY, coalesce(REVALUATION.ORGANIZATIONRATE,REVENUECTE.ORIGINALORGANIZATIONRATE)), @DECIMALDIGITS, @ROUNDINGTYPECODE)
else dbo.UFN_CURRENCY_ROUND(dbo.UFN_CURRENCY_APPLYRATE(dbo.UFN_CURRENCY_ROUND(dbo.UFN_CURRENCY_APPLYRATE(REVENUECTE.BALANCEINCURRENCY, coalesce(REVALUATION.BASERATE, REVENUECTE.ORIGINALBASERATE)), BASECURRENCY.DECIMALDIGITS, BASECURRENCY.ROUNDINGTYPECODE), coalesce(REVALUATION.ORGANIZATIONRATE, REVENUECTE.ORIGINALORGANIZATIONRATE)), @DECIMALDIGITS, @ROUNDINGTYPECODE)
end
when 1
then dbo.UFN_CURRENCY_ROUND(dbo.UFN_CURRENCY_APPLYRATE(REVENUECTE.BALANCEINCURRENCY, coalesce(REVALUATION.ORGANIZATIONRATE,REVENUECTE.ORIGINALORGANIZATIONRATE)), @DECIMALDIGITS, @ROUNDINGTYPECODE)
end
else 0
end [BALANCEINCURRENCY],
REVENUECTE.BALANCEINCURRENCY TRANSACTIONBALANCE,
REVENUECTE.CURRENCYID
from REVENUECTE
outer apply(
select top 1
REVENUECOMMITMENTREVALUATION.ID,
COMMITMENTREVALUATION.BASEEXCHANGERATEID,
COMMITMENTREVALUATION.ORGANIZATIONEXCHANGERATEID,
BASERATE.RATE BASERATE,
ORGANIZATIONRATE.RATE ORGANIZATIONRATE
from dbo.REVENUECOMMITMENTREVALUATION
inner join dbo.COMMITMENTREVALUATION on REVENUECOMMITMENTREVALUATION.COMMITMENTREVALUATIONID = COMMITMENTREVALUATION.ID
left join dbo.CURRENCYEXCHANGERATE BASERATE on BASERATE.ID = COMMITMENTREVALUATION.BASEEXCHANGERATEID
left join dbo.CURRENCYEXCHANGERATE ORGANIZATIONRATE on ORGANIZATIONRATE.ID = COMMITMENTREVALUATION.ORGANIZATIONEXCHANGERATEID
where REVENUECOMMITMENTREVALUATION.REVENUEID = REVENUECTE.ID
and COMMITMENTREVALUATION.DATE <= @ASOFDATE
order by
COMMITMENTREVALUATION.DATE desc,
COMMITMENTREVALUATION.SEQUENCE desc
) REVALUATION
left join dbo.CURRENCY BASECURRENCY on BASECURRENCY.ID = REVENUECTE.BASECURRENCYID
where (REVENUECTE.CURRENCYID is not null)
and
REVENUECTE.TRANSACTIONCURRENCYID <> REVENUECTE.CURRENCYID
and (
( --We want base balance and base <> transaction currency
(
@CURRENCYCODE = 0
or (
(@CURRENCYCODE is null or @CURRENCYCODE not in (0,1,2))
and @CURRENCYID = REVENUECTE.BASECURRENCYID
)
)
and REVENUECTE.TRANSACTIONCURRENCYID <> REVENUECTE.BASECURRENCYID
)
or ( --We want organization balance.
(
@CURRENCYCODE = 1
or (
(@CURRENCYCODE is null or @CURRENCYCODE not in (0,1,2))
and @CURRENCYID = @ORGANIZATIONCURRENCYID
)
)
and (
( --The origin is T->B->O, but transaction <> base or base <> organization currency
@ORIGINCODE = 0
and (
REVENUECTE.TRANSACTIONCURRENCYID <> REVENUECTE.BASECURRENCYID
or REVENUECTE.BASECURRENCYID <> @ORGANIZATIONCURRENCYID
)
)
or( --The origin is T->O, and transaction <> organization currency
@ORIGINCODE = 1
and REVENUECTE.TRANSACTIONCURRENCYID <> @ORGANIZATIONCURRENCYID
)
)
)
)
and REVENUECTE.BALANCEINCURRENCY is not null
and REVENUECTE.BALANCEINCURRENCY <> 0
union all -- Arbitrary currency
select
REVENUECTE.ID,
case
when LATESTEXCHANGERATE.RATE is not null
then dbo.UFN_CURRENCY_ROUND(dbo.UFN_CURRENCY_APPLYRATE(REVENUECTE.BALANCEINCURRENCY, LATESTEXCHANGERATE.RATE), @DECIMALDIGITS, @ROUNDINGTYPECODE)
when LATESTINVERSEEXCHANGERATE.RATE is not null
then dbo.UFN_CURRENCY_ROUND(dbo.UFN_CURRENCY_APPLYRATE(REVENUECTE.BALANCEINCURRENCY, cast((1 / LATESTINVERSEEXCHANGERATE.RATE) as decimal(20,8))), @DECIMALDIGITS, @ROUNDINGTYPECODE)
else 0
end [BALANCEINCURRENCY],
REVENUECTE.BALANCEINCURRENCY TRANSACTIONBALANCE,
REVENUECTE.CURRENCYID
from REVENUECTE
outer apply
(
select
RATE
from
dbo.CURRENCYEXCHANGERATE
where
CURRENCYEXCHANGERATE.FROMCURRENCYID = REVENUECTE.TRANSACTIONCURRENCYID
and CURRENCYEXCHANGERATE.TOCURRENCYID = REVENUECTE.CURRENCYID
and CURRENCYEXCHANGERATE.TYPECODE in (0,1)
and CURRENCYEXCHANGERATE.ISLATESTRATEFORASOFDATE = 1
and CURRENCYEXCHANGERATE.ASOFDATESDTZ <= dateadd(ms, 86399996, REVENUECTE.DATE)
and CURRENCYEXCHANGERATE.NEXTRATEASOFDATEORMAXSDTZ >= dateadd(ms, 86399996, REVENUECTE.DATE)
) LATESTEXCHANGERATE
outer apply
(
select
RATE
from
dbo.CURRENCYEXCHANGERATE
where
CURRENCYEXCHANGERATE.FROMCURRENCYID = REVENUECTE.CURRENCYID
and CURRENCYEXCHANGERATE.TOCURRENCYID = REVENUECTE.TRANSACTIONCURRENCYID
and CURRENCYEXCHANGERATE.TYPECODE in (0,1)
and CURRENCYEXCHANGERATE.ISLATESTRATEFORASOFDATE = 1
and CURRENCYEXCHANGERATE.ASOFDATESDTZ <= dateadd(ms, 86399996, REVENUECTE.DATE)
and CURRENCYEXCHANGERATE.NEXTRATEASOFDATEORMAXSDTZ >= dateadd(ms, 86399996, REVENUECTE.DATE)
) LATESTINVERSEEXCHANGERATE
where (REVENUECTE.CURRENCYID is not null)
and ( --We want an arbitrary balance and arbitrary <> transaction currency
not @CURRENCYCODE in (0,1,2)
and REVENUECTE.TRANSACTIONCURRENCYID <> REVENUECTE.CURRENCYID
and REVENUECTE.BASECURRENCYID <> REVENUECTE.CURRENCYID
and @ORGANIZATIONCURRENCYID <> REVENUECTE.CURRENCYID
)
and REVENUECTE.BALANCEINCURRENCY is not null
and REVENUECTE.BALANCEINCURRENCY <> 0
)
select
SPLITBALANCES.ID,
SPLITBALANCES.TOAMOUNT BALANCE
from CTE_PLEDGEBALANCE
cross apply dbo.UFN_PLEDGE_CONVERTSPLITBALANCESBYPROPORTION(
CTE_PLEDGEBALANCE.ID,
@ASOFDATE,
CTE_PLEDGEBALANCE.TRANSACTIONBALANCE,
CTE_PLEDGEBALANCE.BALANCEINCURRENCY,
@DECIMALDIGITS
) SPLITBALANCES
)