## Question and Answer

## You Asked

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.

/

So far, I've got this

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.

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.

with LiveSQL Test Case:

## and Chris said...

In the variable definition, you can check running totals with sum, count, etc.

So you can define the payment variable as:

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:

You can then filter the results to the paid invoices in a where clause:

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...

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...

## Rating

Is this answer out of date? If it is, please let us know via a Comment

# Comments

@Chris,

Thank you for showing this amazing idea of using MATCH_RECOGNIZE.

If I may ask, in the above example, would it be possible to define a pattern that would eliminate the "11,12,13 DOES not MATCH to 10" set of records?

Thank you for showing this amazing idea of using MATCH_RECOGNIZE.

If I may ask, in the above example, would it be possible to define a pattern that would eliminate the "11,12,13 DOES not MATCH to 10" set of records?

You can split the payment totals into two variables:

one checking the running total is strictly less than the invoice amount; there are zero or more of these

the other that the running total is exactly equal to the invoice total; there's exactly one of these

The pattern and definition for these look something like:

This assumes all payments come after the invoice they're for. If they can appear out-of-sequence it gets much fiddlier!

one checking the running total is strictly less than the invoice amount; there are zero or more of these

the other that the running total is exactly equal to the invoice total; there's exactly one of these

The pattern and definition for these look something like:

pattern ( ( inv pmt* ptot ) ) define ptot as ptot.trx_type = 'PMT' and nvl ( sum ( -pmt.amount ), 0 ) - nvl ( ptot.amount, 0 ) = inv.amount

This assumes all payments come after the invoice they're for. If they can appear out-of-sequence it gets much fiddlier!

Thanks Chris...

If I may bother with one more question...

In the above example, would it be possible to define a pattern that would classify the "11,12,13 DOES not MATCH to 10" set of records?

Thanks in advance

If I may bother with one more question...

In the above example, would it be possible to define a pattern that would classify the "11,12,13 DOES not MATCH to 10" set of records?

Thanks in advance

Classify them how? The original answer sets them all to OUTSTANDING; the outer query filters these out.

What exactly are you looking for?

What exactly are you looking for?

*Classify them how? The original answer sets them all to OUTSTANDING; the outer query filters these out.*

What exactly are you looking for?

What exactly are you looking for?

Apologies...I should have been clearer with my question.

The solution that you have provided is using below construct in MEASURES clause to categorize the individual records as either PAID or OUTSTANDING

case when final sum ( -pmt.amount ) = inv.amount or final sum ( -prepmt.amount ) = postinv.amount then 'PAID' else 'OUTSTANDING'

My question is whether there is a way to categorize PAID or OUTSTANDING using a pattern definition instead? Does that make sense?

Thanks in advance

You can use the CLASSIFIER function to rename the name of the pattern variable. So it's a matter of making the pattern along with naming and defining the variables to give the output you want.

If there's something specific you're trying to understand here, please post a new question. The original has gnarly requirements - matching payments made before and after an invoice. I (would hope that!) this is rare in the real world. Generally, invoices are issued first and payments are made against them. So you can sort by date and likely have an invoice_id or similar to match; this simplifies the logic vastly.

Trying to make generalizations from a specific, hard problem is difficult!

If there's something specific you're trying to understand here, please post a new question. The original has gnarly requirements - matching payments made before and after an invoice. I (would hope that!) this is rare in the real world. Generally, invoices are issued first and payments are made against them. So you can sort by date and likely have an invoice_id or similar to match; this simplifies the logic vastly.

Trying to make generalizations from a specific, hard problem is difficult!