UFN_BANKACCOUNTDEPOSIT_CALCULATEAMOUNTS
Calculates the bank account transaction amounts based on the total payment amount of the deposit.
Return
| Return Type |
|---|
| table |
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @DEPOSITS | UDT_GENERICID | IN | |
| @ORGAMOUNTORIGINCODE | tinyint | IN | |
| @ORGCURRENCYID | uniqueidentifier | IN |
Definition
Copy
create function dbo.UFN_BANKACCOUNTDEPOSIT_CALCULATEAMOUNTS
(
@DEPOSITS UDT_GENERICID readonly
,@ORGAMOUNTORIGINCODE tinyint
,@ORGCURRENCYID uniqueidentifier
)
returns table
as return
(
select
SUM(isnull(T.TRANSACTIONAMOUNT, 0)) [TRANSACTIONAMOUNT]
,SUM(isnull(T.BASEAMOUNT, 0)) [BASEAMOUNT]
,SUM(isnull(T.ORGAMOUNT, 0)) [ORGAMOUNT]
, T.ID
from (
select
SUM(isnull(
case when D.TRANSACTIONCURRENCYID = BA.TRANSACTIONCURRENCYID
then R.TRANSACTIONAMOUNT
else dbo.UFN_CURRENCY_CONVERT(R.TRANSACTIONAMOUNT, D.TRANSACTIONEXCHANGERATEID)
end, 0)
) as [TRANSACTIONAMOUNT]
,SUM(isnull(
case when BA.TRANSACTIONCURRENCYID = BAT.BASECURRENCYID
then
case when D.TRANSACTIONCURRENCYID = BA.TRANSACTIONCURRENCYID
then R.TRANSACTIONAMOUNT
else dbo.UFN_CURRENCY_CONVERT(R.TRANSACTIONAMOUNT, D.TRANSACTIONEXCHANGERATEID)
end
else
dbo.UFN_CURRENCY_CONVERT(
case when D.TRANSACTIONCURRENCYID = BA.TRANSACTIONCURRENCYID
then R.TRANSACTIONAMOUNT
else dbo.UFN_CURRENCY_CONVERT(R.TRANSACTIONAMOUNT, D.TRANSACTIONEXCHANGERATEID)
end
,BAT.BASEEXCHANGERATEID)
end, 0)
) as [BASEAMOUNT]
,SUM(isnull(
case when @ORGAMOUNTORIGINCODE = 1
then
case when BA.TRANSACTIONCURRENCYID = @ORGCURRENCYID
then
case when D.TRANSACTIONCURRENCYID = BA.TRANSACTIONCURRENCYID
then R.TRANSACTIONAMOUNT
else dbo.UFN_CURRENCY_CONVERT(R.TRANSACTIONAMOUNT, D.TRANSACTIONEXCHANGERATEID)
end
else
dbo.UFN_CURRENCY_CONVERT(
case when D.TRANSACTIONCURRENCYID = BA.TRANSACTIONCURRENCYID
then R.TRANSACTIONAMOUNT
else dbo.UFN_CURRENCY_CONVERT(R.TRANSACTIONAMOUNT, D.TRANSACTIONEXCHANGERATEID)
end
,BAT.ORGANIZATIONEXCHANGERATEID)
end
else
case when BAT.BASECURRENCYID = @ORGCURRENCYID
then
case when BA.TRANSACTIONCURRENCYID = BAT.BASECURRENCYID
then
case when D.TRANSACTIONCURRENCYID = BA.TRANSACTIONCURRENCYID
then R.TRANSACTIONAMOUNT
else dbo.UFN_CURRENCY_CONVERT(R.TRANSACTIONAMOUNT, D.TRANSACTIONEXCHANGERATEID)
end
else
dbo.UFN_CURRENCY_CONVERT(
case when D.TRANSACTIONCURRENCYID = BA.TRANSACTIONCURRENCYID
then R.TRANSACTIONAMOUNT
else dbo.UFN_CURRENCY_CONVERT(R.TRANSACTIONAMOUNT, D.TRANSACTIONEXCHANGERATEID)
end
,BAT.BASEEXCHANGERATEID)
end
else
dbo.UFN_CURRENCY_CONVERT(
case when BA.TRANSACTIONCURRENCYID = BAT.BASECURRENCYID
then
case when D.TRANSACTIONCURRENCYID = BA.TRANSACTIONCURRENCYID
then R.TRANSACTIONAMOUNT
else dbo.UFN_CURRENCY_CONVERT(R.TRANSACTIONAMOUNT, D.TRANSACTIONEXCHANGERATEID)
end
else
dbo.UFN_CURRENCY_CONVERT(
case when D.TRANSACTIONCURRENCYID = BA.TRANSACTIONCURRENCYID
then R.TRANSACTIONAMOUNT
else dbo.UFN_CURRENCY_CONVERT(R.TRANSACTIONAMOUNT, D.TRANSACTIONEXCHANGERATEID)
end
,BAT.BASEEXCHANGERATEID)
end
,BAT.ORGANIZATIONEXCHANGERATEID)
end
end, 0)) as [ORGAMOUNT]
,D.ID
from dbo.BANKACCOUNTDEPOSIT D
left join dbo.BANKACCOUNTDEPOSITPAYMENT DP on D.ID = DP.DEPOSITID
left join dbo.REVENUE R on R.ID = DP.ID
inner join dbo.BANKACCOUNTTRANSACTION BAT on BAT.ID = D.ID
inner join dbo.BANKACCOUNT BA on BA.ID = BAT.BANKACCOUNTID
where D.ID in (select ID from @DEPOSITS)
group by D.ID
union all
select
SUM(isnull(case when R.CORRECTIONTYPECODE in (0, 2) then -1 else 1 end *
case when D.TRANSACTIONCURRENCYID = BA.TRANSACTIONCURRENCYID
then R.TRANSACTIONAMOUNT
else dbo.UFN_CURRENCY_CONVERT(R.TRANSACTIONAMOUNT, D.TRANSACTIONEXCHANGERATEID)
end, 0)
) as [TRANSACTIONAMOUNT]
,SUM(isnull(case when R.CORRECTIONTYPECODE in (0, 2) then -1 else 1 end *
case when BA.TRANSACTIONCURRENCYID = BAT.BASECURRENCYID
then
case when D.TRANSACTIONCURRENCYID = BA.TRANSACTIONCURRENCYID
then R.TRANSACTIONAMOUNT
else dbo.UFN_CURRENCY_CONVERT(R.TRANSACTIONAMOUNT, D.TRANSACTIONEXCHANGERATEID)
end
else
dbo.UFN_CURRENCY_CONVERT(
case when D.TRANSACTIONCURRENCYID = BA.TRANSACTIONCURRENCYID
then R.TRANSACTIONAMOUNT
else dbo.UFN_CURRENCY_CONVERT(R.TRANSACTIONAMOUNT, D.TRANSACTIONEXCHANGERATEID)
end
,BAT.BASEEXCHANGERATEID)
end, 0)
) as [BASEAMOUNT]
,SUM(isnull(case when R.CORRECTIONTYPECODE in (0, 2) then -1 else 1 end *
case when @ORGAMOUNTORIGINCODE = 1
then
case when BA.TRANSACTIONCURRENCYID = @ORGCURRENCYID
then
case when D.TRANSACTIONCURRENCYID = BA.TRANSACTIONCURRENCYID
then R.TRANSACTIONAMOUNT
else dbo.UFN_CURRENCY_CONVERT(R.TRANSACTIONAMOUNT, D.TRANSACTIONEXCHANGERATEID)
end
else
dbo.UFN_CURRENCY_CONVERT(
case when D.TRANSACTIONCURRENCYID = BA.TRANSACTIONCURRENCYID
then R.TRANSACTIONAMOUNT
else dbo.UFN_CURRENCY_CONVERT(R.TRANSACTIONAMOUNT, D.TRANSACTIONEXCHANGERATEID)
end
,BAT.ORGANIZATIONEXCHANGERATEID)
end
else
case when BAT.BASECURRENCYID = @ORGCURRENCYID
then
case when BA.TRANSACTIONCURRENCYID = BAT.BASECURRENCYID
then
case when D.TRANSACTIONCURRENCYID = BA.TRANSACTIONCURRENCYID
then R.TRANSACTIONAMOUNT
else dbo.UFN_CURRENCY_CONVERT(R.TRANSACTIONAMOUNT, D.TRANSACTIONEXCHANGERATEID)
end
else
dbo.UFN_CURRENCY_CONVERT(
case when D.TRANSACTIONCURRENCYID = BA.TRANSACTIONCURRENCYID
then R.TRANSACTIONAMOUNT
else dbo.UFN_CURRENCY_CONVERT(R.TRANSACTIONAMOUNT, D.TRANSACTIONEXCHANGERATEID)
end
,BAT.BASEEXCHANGERATEID)
end
else
dbo.UFN_CURRENCY_CONVERT(
case when BA.TRANSACTIONCURRENCYID = BAT.BASECURRENCYID
then
case when D.TRANSACTIONCURRENCYID = BA.TRANSACTIONCURRENCYID
then R.TRANSACTIONAMOUNT
else dbo.UFN_CURRENCY_CONVERT(R.TRANSACTIONAMOUNT, D.TRANSACTIONEXCHANGERATEID)
end
else
dbo.UFN_CURRENCY_CONVERT(
case when D.TRANSACTIONCURRENCYID = BA.TRANSACTIONCURRENCYID
then R.TRANSACTIONAMOUNT
else dbo.UFN_CURRENCY_CONVERT(R.TRANSACTIONAMOUNT, D.TRANSACTIONEXCHANGERATEID)
end
,BAT.BASEEXCHANGERATEID)
end
,BAT.ORGANIZATIONEXCHANGERATEID)
end
end, 0)) as [ORGAMOUNT]
,D.ID
from dbo.BANKACCOUNTDEPOSIT D
inner join dbo.BANKACCOUNTDEPOSITCORRECTION R on R.DEPOSITID = D.ID
inner join dbo.BANKACCOUNTTRANSACTION BAT on BAT.ID = D.ID
inner join dbo.BANKACCOUNT BA on BA.ID = BAT.BANKACCOUNTID
where D.ID in (select ID from @DEPOSITS)
group by D.ID
union all
--The below needs to be rewritten once ticketing supports multicurrency.
select -1 * SUM(P.AMOUNT) as [TRANSACTIONAMOUNT]
, -1 * SUM(P.AMOUNT) as [BASEAMOUNT]
, -1 * SUM(P.AMOUNT) as [ORGAMOUNT]
,C.DEPOSITID as [ID]
from dbo.BANKACCOUNTDEPOSITCREDITPAYMENT C
inner join dbo.CREDITPAYMENT P on C.ID = P.ID
where C.DEPOSITID in (select ID from @DEPOSITS)
group by C.DEPOSITID) as T
group by T.ID
)