I am trying to apply Match_Recognize to find all the payments that make up the invoice. More than one payment can be matched to one Invoice. I am not sure if this can be achieved using Match Recognize or if this is a right candidate for it.
create table ae_misc_trxs (ae_num number, trx_id number, trx_type varchar2(10), amount number)
/
INSERT ALL
INTO ae_misc_trxs VALUES(123,1, 'INV', +10)
INTO ae_misc_trxs VALUES(123,2, 'PMT', -10)
INTO ae_misc_trxs VALUES(123,3, 'INV',+12)
INTO ae_misc_trxs VALUES(123,4, 'PMT', -10)
INTO ae_misc_trxs VALUES(123,5, 'PMT', -2)
INTO ae_misc_trxs VALUES(123,6, 'PMT', -8)
INTO ae_misc_trxs VALUES(123,7, 'PMT',-3)
INTO ae_misc_trxs VALUES(123,8, 'PMT',-2)
INTO ae_misc_trxs VALUES(123,9, 'INV', +13)
INTO ae_misc_trxs VALUES(123,10, 'INV', +60)
INTO ae_misc_trxs VALUES(123,11, 'PMT', -50)
INTO ae_misc_trxs VALUES(123,12, 'PMT', -5)
INTO ae_misc_trxs VALUES(123,13, 'PMT',-1)
SELECT NULL
FROM DUAL;
/
So far, I've got this
SELECT *
FROM ae_misc_trxs A
MATCH_RECOGNIZE(
PARTITION BY ae_num ORDER BY trx_id
MEASURES inv.trx_id AS inv_trx
,pmt.trx_id AS pmt_trx
,MATCH_NUMBER() AS mno
ALL ROWS PER MATCH
AFTER MATCH SKIP TO LAST INV
PATTERN (inv pmt+)
DEFINE INV AS inv.trx_type='INV' AND (inv.amount + NEXT(pmt.amount) = 0)
,PMT AS pmt.trx_type='PMT'
);
The below rows should be selected
e.g. Trx_id 2 matches to trx_id 1
Trx_id 5,4 matches to Trx_id 3
6,7,8 matches to 9
11,12,13 DOES not MATCH to 10.
In the variable definition, you can check running totals with sum, count, etc.
So you can define the payment variable as:
pmt as pmt.trx_type='PMT' and sum ( -pmt.amount ) <= inv.amount
This keeps adding rows to the pattern until you hit the invoice total.
Making this trickier is the fact you've got payments that precede their invoices. To get around this I've defined another set of pattern variables, doing the check above on the (POST)INV variable.
Which gives something like:
select *
from ae_misc_trxs a
match_recognize (
partition by ae_num order by trx_id
measures
inv.trx_id as inv_trx
,pmt.trx_id as pmt_trx
,match_number() as mno
,coalesce (
sum ( -pmt.amount ),
sum ( -prepmt.amount )
) pmt_tot
,case
when final sum ( -pmt.amount ) = inv.amount or
final sum ( -prepmt.amount ) = postinv.amount
then 'PAID'
else 'OUTSTANDING'
end paid
all rows per match
pattern ( ( inv pmt+ ) | ( prepmt+ postinv ) )
define
inv as inv.trx_type='INV'
,pmt as pmt.trx_type='PMT' and sum ( -pmt.amount ) <= inv.amount
,prepmt as trx_type='PMT'
,postinv as trx_type='INV' and sum ( -prepmt.amount ) <= postinv.amount
);
AE_NUM TRX_ID INV_TRX PMT_TRX MNO PMT_TOT PAID TRX_TYPE AMOUNT
123 1 1 <null> 1 <null> PAID INV 10
123 2 1 2 1 10 PAID PMT -10
123 3 3 <null> 2 <null> PAID INV 12
123 4 3 4 2 10 PAID PMT -10
123 5 3 5 2 12 PAID PMT -2
123 6 <null> <null> 3 8 OUTSTANDING PMT -8
123 7 <null> <null> 3 11 OUTSTANDING PMT -3
123 8 <null> <null> 3 13 OUTSTANDING PMT -2
123 9 <null> <null> 3 13 PAID INV 13
123 10 10 <null> 4 <null> OUTSTANDING INV 60
123 11 10 11 4 50 OUTSTANDING PMT -50
123 12 10 12 4 55 OUTSTANDING PMT -5
123 13 10 13 4 56 OUTSTANDING PMT -1
You can then filter the results to the paid invoices in a where clause:
select *
from ae_misc_trxs a
match_recognize (
partition by ae_num order by trx_id
measures
inv.trx_id as inv_trx
,pmt.trx_id as pmt_trx
,match_number() as mno
,coalesce (
sum ( -pmt.amount ),
sum ( -prepmt.amount )
) pmt_tot
,case
when final sum ( -pmt.amount ) = inv.amount or
final sum ( -prepmt.amount ) = postinv.amount
then 'PAID'
else 'OUTSTANDING'
end paid
all rows per match
pattern ( ( inv pmt+ ) | ( prepmt+ postinv ) )
define
inv as inv.trx_type='INV'
,pmt as pmt.trx_type='PMT' and sum ( -pmt.amount ) <= inv.amount
,prepmt as trx_type='PMT'
,postinv as trx_type='INV' and sum ( -prepmt.amount ) <= postinv.amount
)
where paid = 'PAID' and trx_type = 'INV';
AE_NUM TRX_ID INV_TRX PMT_TRX MNO PMT_TOT PAID TRX_TYPE AMOUNT
123 1 1 <null> 1 <null> PAID INV 10
123 3 3 <null> 2 <null> PAID INV 12
123 9 <null> <null> 3 13 PAID INV 13
Note this does assume that all payments and invoice totals add up neatly! If this isn't true (e.g. you have no payments between issuing two invoices), things get complicated quickly...