The loop business type allows for recursive validation to be enforced. That is, the same business rule logic can be applied over and over again. The reason you would want to loop a business rule over and over again is to deal with transaction chains, which describe many to many relationships between transactions. An example of a one to many relationship would be header table to lines table. One header record has many lines. An example of many to many would be the order to cash or procure to pay transaction chains:

The same many-to-many relationship is true for POs-Invoices and Invoice-Payments. So, a complex business process like Procure to Pay can involve many transactions:

Many reqs create many POs, many POs create many Invoices, many Invoices create many Payments.

For some transaction chains, it is necessary to keep chains together from an archive and purge perspective. Although these are discrete transactions with its own set of business requirements, the business application requires the full chain to be present to function properly. If parts of the chain are missing, the business application cannot operate as expected. The loop rule type, therefore, exists solely to keep transaction chains together - either they get archived together or they stay in production together. The logic of the loop business rules identifies "links" in the chain that cannot be purged. The recursive looping is necessary to identify all the links in the chain that cannot be purged.

The first step in the transaction chain process is to build an interim table that pieces the transaction chain together. In this example we are using the Procure to Pay transaction chain. Individual interim tables will exist for each discrete transaction: Req, PO, Voucher, Payment. Each discrete transaction will have its own set of rules to pass. Once business rules have been enforced for the discrete transactions, then you build the transaction chain interim table. The transaction chain interim is built through a combination of the discrete transaction interim tables and the source table cross-reference tables. Examples of a cross-reference table would be PS_VOUCHER_PAYMENT_XREF in Peoplesoft or AR_RECEIVABLE_APPLICATIONS_ALL in Oracle Apps.

An example of a Procure to Pay transaction chain interim table may be:

TRX CHAIN INTERIM

When the transaction chain interim is inserted, the purge_flag is set at that time. Therefore, each link in the chain must be marked as purgeable in the individual interim tables for the purge_flag for the chain to be marked as 'Y'.

Let's look at the example above to see if we can piece a chain together.

Req 1A created PO 10A.
PO 10A is matched to Voucher 100A.
Voucher 100A was paid by Payment 1000A.
Req1 1A also created PO 10B which is also matched to Voucher 100A.
We also see that PO 10A was sourced from Req 1B and it was matched to VCHR 100B and VHCR 100C.

So if one of these discrete transactions cannot be archived and purged, all of the related transactions cannot be archived and purged as well. In the example above let's say PO 10B is not purgeable. Since 10B is not purgeable, that "row" of the chain is not purgeable. What is the effect of this part of the chain on the rest of the chain? How will the LOOP business type help us here? Let's step through the recursive logic to see how the LOOP condition statements will navigate through a transaction chain.

As previously stated, the first step in the transaction chain logic is to build the transaction chain interim. Each row in the interim lists every related transaction.

TRX CHAIN INTERIM

Like all of the other Business Rule types, the condition statement for the LOOP type affects a single column in the transaction chain interim table. Therefore, a LOOP business rule exists for each column in the transaction chain interim. The LOOP business rule is constructed as such:

UPDATE XA_PO_CHAIN_INTERIM
   SET purgeable_flag='N'
 WHERE A.PO_ID IN (SELECT X.PO_ID FROM XA_PO_CHAIN_INTERIM X 
                    WHERE X.purgeable_flag ='N')              
   AND A.purgeable_flag = 'Y'

The logic of the rule is simple - find POs where the purgeable_flag is 'N' and update rows where that PO has a purgeable_flag = 'Y'. So let's look at the chain again. Which row would this statement update? It would update 0 rows. Why? Because it's looking for other rows for PO 10B that have a purgeable flag of Y. There aren't any more rows for PO 10B.

TRX CHAIN INTERIM

This is the condition statement for Reqs. It is the same logic but for Reqs instead of POs.

UPDATE XA_PO_CHAIN_INTERIM
   SET purgeable_flag='N'
 WHERE A.REQ_ID IN (SELECT X.REQ_ID FROM XA_PO_CHAIN_INTERIM X 
                     WHERE X.purgeable_flag ='N')
   AND A.purgeable_flag = 'Y'       

Which rows would this statement update? It would update the first row which has Req 1A and purgeable_flag='Y'. So that purgeable flag changes from Y to N.

TRX CHAIN INTERIM

Now let's look at vouchers. Again, the logic is the same - find vouchers where the purgeable_flag is 'N' and update rows where that voucher has a purgeable_flag = 'Y'. Which row would this statement update?

UPDATE XA_PO_CHAIN_INTERIM
   SET purgeable_flag='N'
 WHERE A.VCHR_ID IN (SELECT X.VCHR_ID FROM XA_PO_CHAIN_INTERIM X
                      WHERE X.purgeable_flag ='N')
   AND A.purgeable_flag = 'Y'  

It would update 0 rows. Why? Because it's looking for other rows for Voucher 100A that have a purgeable flag of Y. There aren't any more rows for Voucher 100A that have a purgeable flag = Y.

TRX CHAIN INTERIM

For Payments the same logic is used - find payments that are not purgeable and mark other rows as not purgeable where those payments are purgeable. Which rows would this statement update?

It would update the third row which has Payment 1000A and purgeable_flag='Y'

At this point the LOOP logic has completed its first iteration. Now it "loops" and runs all 4 updates again. It keeps doing this until all 4 update statements update 0 rows. So in the second iteration, what rows will be updated?

The Req update will update row 4. Req 1B has a purgeable flag of 'Y'

TRX CHAIN INTERIM

Will the remaining LOOP statement update any rows? No. Will the LOOP logic loop again? Yes because the first LOOP update updated 1 row. So now we do a third iteration. Will any rows be updated in the 3rd iteration? No. Will the LOOP logic stop after the 3rd iteration? Yes because all 4 LOOP statement updated 0 rows.

So when the loop logic runs, it runs all of the updates one after the other. It loops back to the top and runs them again. It keeps looping until all updates update 0 rows. The number of iterations is an indication of the complexity of the transaction chain. The higher the number of iterations means that the number of transactions that are related to each other is greater. This means that one non-purgeable transaction can lead to thousands and thousands of transactions from being not purgeable.