Hi Tom,
Need help in coming up with SQL Query for below scenario….
I have an account and for every account there are 1 or more virtual accounts.
For each virtual account there is credit percentage and debit priority defined.
Business Rules
1. At initial load/ the balance in account table is split across virtual accounts using CREDIT_PCT field.
2.Transactions do happen at Account
a.whenever credits happen, the balances of virtual accounts will be credited as per credit_pct defined.
b.whenever debits happen, the balance will be debited in order of debit_priority, if one of the virtual account does not have sufficient balance, it will be debited from next virtual account and so on..
Need your help to come up with single query to show report having the below columns.
TXN_ID,ACCOUNT_ID, TXN_TYPE, TXN_AMOUNT, ACCOUNT_BALANCE_AFTER_TXN, VACCOUNT_ID, DEBIT_PRIORITY CREDIT_PCT, V_ACCOUNT_TXN_AMOUNT, V_ACCOUNT_BAL_AFTER_TXN
Here are the setup for required data
create table account
(
account_id number,
account_name varchar2(35),
balance number(38,5)
);
create table vaccount
(
vaccount_id number,
account_id number,
credit_pct number(5,2),
debit_priority number,
vbalance number(38,5)
);
create table txns
(
txn_id number,
account_id number,
txn_amount number(38,5),
txn_type varchar2(35)
);
insert into account(account_id, account_name, balance) values (1, 'TEST 1', 100.00);
insert into account(account_id, account_name, balance) values (2, 'TEST 2', 100.00);
insert into vaccount (vaccount_id, account_id, credit_pct, debit_priority, vbalance) values (1, 1, 10, 1, 10);
insert into vaccount (vaccount_id, account_id, credit_pct, debit_priority, vbalance) values (2, 1, 20, 4, 20);
insert into vaccount (vaccount_id, account_id, credit_pct, debit_priority, vbalance) values (3, 1, 30, 3, 30);
insert into vaccount (vaccount_id, account_id, credit_pct, debit_priority, vbalance) values (4, 1, 15, 5, 15);
insert into vaccount (vaccount_id, account_id, credit_pct, debit_priority, vbalance) values (5, 1, 25, 2, 25);
insert into vaccount (vaccount_id, account_id, credit_pct, debit_priority, vbalance) values (6, 2, 11, 1, 11);
insert into vaccount (vaccount_id, account_id, credit_pct, debit_priority, vbalance) values (7, 2, 12, 4, 12);
insert into vaccount (vaccount_id, account_id, credit_pct, debit_priority, vbalance) values (8, 2, 13, 3, 13);
insert into vaccount (vaccount_id, account_id, credit_pct, debit_priority, vbalance) values (9, 2, 14, 5, 14);
insert into vaccount (vaccount_id, account_id, credit_pct, debit_priority, vbalance) values (10, 2, 50, 2, 50);
insert into txns (txn_id, account_id, txn_amount, txn_type) values (1, 1, 30, 'DEBIT');
insert into txns (txn_id, account_id, txn_amount, txn_type) values (2, 1, 15, 'CREDIT');
insert into txns (txn_id, account_id, txn_amount, txn_type) values (3, 2, 85, 'DEBIT');
insert into txns (txn_id, account_id, txn_amount, txn_type) values (4, 2, 35, 'CREDIT');
commit;
This gets complicated quickly. To help, I'd also create a virtual transactions table:
create table vtxns (
txn_id number,
vaccount_id number,
vamount number(38,5)
);
Then for each real transaction, store the virtual breakdown. This allows you to calculate the virtual balances from some known point by summing up the virtual transactions after this.
For credits, this is
fairly easy. Just divide the total by 100 and multiply by the percentage:
with amounts as (
select tx.txn_id,
va.vaccount_id,
round ( txn_amount / 100 * credit_pct, 2) rounded_pct
from txns tx
join vaccount va
on va.account_id = tx.account_id
where tx.txn_id = :txn_id
)
select txn_id, vaccount_id, rounded_pct
from amounts a;Though I've not accounted for the "missing cent" problem you'll get when rounding monetary values. For ways to do this, see
https://nuijten.blogspot.com/2016/05/rounding-amounts-divide-cents-over.html To debit accounts based on their priority, you can:
- Get the running total for the virtual accounts, sorted by debit_priorty
- Compare this to the transaction amount
- If the transaction amount is greater than the current running total, debit the virtual balance
- If the running balance for the previous row is greater than the transaction amount, debit nothing (zero)
- Otherwise take the difference between the running balance and the transaction amount
Which gives something like:
with amounts as (
select case
when txn_amount >
sum ( va.vbalance )
over ( order by debit_priority )
then
va.vbalance
when txn_amount <
sum ( va.vbalance ) over (
order by debit_priority
rows between unbounded preceding and 1 preceding
)
then 0
else
sum ( va.vbalance )
over ( order by debit_priority ) - txn_amount
end vamount,
va.account_id,
va.vaccount_id,
tx.txn_id
from txns tx
join vaccount va
on va.account_id = tx.account_id
where tx.txn_id = :txn_id
)
select txn_id, vaccount_id, -vamount
from amounts a;This relies on you updating the balance for each virtual account after every transaction. If you're not doing this, you'll have to adjust this to apply the previous transactions too.
Put this all together and you get something like:
declare
procedure credits ( txn_id int, account_id int ) as
begin
insert into vtxns ( txn_id, vaccount_id, vamount )
with amounts as (
select tx.txn_id,
va.vaccount_id,
round ( txn_amount / 100 * credit_pct, 2) rounded_pct
from txns tx
join vaccount va
on va.account_id = tx.account_id
where tx.txn_id = credits.txn_id
)
select txn_id, vaccount_id, rounded_pct
from amounts a;
end credits;
procedure debits ( txn_id int, account_id int ) as
begin
insert into vtxns ( txn_id, vaccount_id, vamount )
with amounts as (
select case
when txn_amount >
sum ( va.vbalance )
over ( order by debit_priority )
then
va.vbalance
when txn_amount <
sum ( va.vbalance ) over (
order by debit_priority
rows between unbounded preceding and 1 preceding
)
then 0
else
sum ( va.vbalance )
over ( order by debit_priority ) - txn_amount
end vamount,
va.account_id,
va.vaccount_id,
tx.txn_id
from txns tx
join vaccount va
on va.account_id = tx.account_id
where tx.txn_id = debits.txn_id
)
select txn_id, vaccount_id, -vamount
from amounts a;
end debits;
begin
for txs in (
select * from txns
) loop
case txs.txn_type
when 'CREDIT' then
credits ( txs.txn_id, txs.account_id );
when 'DEBIT' then
debits ( txs.txn_id, txs.account_id );
end case;
end loop;
end;
/
select va.account_id,
tx.txn_id,
tx.txn_amount,
vtx.vamount,
va.vbalance +
sum ( vtx.vamount ) over (
partition by va.vaccount_id
order by tx.txn_id
) new_vbalance,
va.vbalance,
debit_priority
from txns tx
join vtxns vtx
on tx.txn_id = vtx.txn_id
join vaccount va
on va.account_id = tx.account_id
and va.vaccount_id = vtx.vaccount_id
order by tx.account_id, tx.txn_id, va.debit_priority;
ACCOUNT_ID TXN_ID TXN_AMOUNT VAMOUNT NEW_VBALANCE VBALANCE DEBIT_PRIORITY
1 1 30 -10 0 10 1
1 1 30 -5 20 25 2
1 1 30 0 30 30 3
1 1 30 0 20 20 4
1 1 30 0 15 15 5
1 2 15 1.5 1.5 10 1
1 2 15 3.75 23.75 25 2
1 2 15 4.5 34.5 30 3
1 2 15 3 23 20 4
1 2 15 2.25 17.25 15 5
2 3 85 -11 0 11 1
2 3 85 -50 0 50 2
2 3 85 -13 0 13 3
2 3 85 -1 11 12 4
2 3 85 0 14 14 5
2 4 35 3.85 3.85 11 1
2 4 35 17.5 17.5 50 2
2 4 35 4.55 4.55 13 3
2 4 35 4.2 15.2 12 4
2 4 35 4.9 18.9 14 5 PS - I'd also add a lot more constraints to this... In particular, primary keys, foreign keys, and not null constraints.
You may also want to add constraints to validate that the sum of the virtual account account credit %s = 100. And the virtual transaction amounts = the total amount. For discussion on how to do this, see:
https://asktom.oracle.com/pls/apex/asktom.search?tag=best-way-to-enforce-cross-row-constraints