The loop condition statements themselves are simple to construct. The examples used above can be used in any transaction chain interim. The LOOP condition type represents the WHERE clause of an UPDATE statement. In this respect it operates exactly like the WHERE condition type. The difference is that the LOOP condition type will be repeated many times whereas the WHERE condition type is executed only once.

The key to building LOOP metadata is building the transaction chain interim itself. The end result of any transaction chain interim is to have a row of data that shows the related transactions. The example we have been using is Procure to Pay, which involves requisitions, purchase orders, vouchers/invoices and payments/checks. The complexity of the source application will dictate how complicated it will be to construct the transaction chain interim.

An example of a complicated transaction chain metadata is the Matched PO/AP entity for Oracle Applications. As we go through this example, you will begin to see how LOOP metadata is constructed.

In the Procure to Pay transaction chain there are many transaction relationships:

Req to PO
PO to Receipt
PO to Invoice
Invoice to Check

In Oracle AP and PO transaction relationships are defined at the line level except for Invoice to Check which uses a cross reference table. So the first step in building LOOP metadata is to identify where the relationships are managed.

These relationships highlight how complex this transaction chain is. To populate an interim table with each transaction in the chain in a single query is too complex. There are too many tables involved. Adding to the complexity is the likelihood of these tables being high volume tables. Finally, there can be "broken" links in the chain. A check can pay an invoice that is matched to a PO. It can also pay an invoice that is not matched. A receipt can contain items for a PO that is matched to an Invoice and for a PO that is not matched to an invoice. The same is true for requisitions - they can be promoted to a PO that is matched to an invoice and to a PO that is not matched to an invoice. In addition, an invoice might be paid by two checks - one that is a candidate for purge, and one that is not a candidate for purge (where candidate for purge means it does not exist in the individual transaction interim table).

For these reasons the transaction chain is broken down into multiple steps. Each transaction relationship in the chain is addressed separately. The process for building the Matched AP/PO transaction chain is:

Identify Invoice-Check relationships in interim table 1.
Identify Invoice-PO relationships in interim table 2.
Identify PO-Receipt relationships in interim table 3.
Identify PO-Requisition relationships in interim table 4.
Combine results from interim tables 1-4 into a single transaction chain interim table.

So to build the transaction chain interim table, four other interim tables are used. To complicate matters even more, the relationships have to be looked at two ways - an invoice might be paid by more than 1 check; a check might pay more than 1 invoice; therefore, when identifying a relationship it must be examined by both sides. So the four steps are:

Identify Invoice-Check relationships in interim table 1.
Identify all checks that paid invoices which are candidates for purge.
Identify all invoices that were paid by checks which are candidates for purge.
Identify Invoice-PO relationships in interim table 2.
Identify all POs matched to the invoices which are candidates for purge.
Identify all invoices matched to POs which are candidate for purge.
Identify PO-Receipt relationships in interim table 3.
Identify all receipts matched to POs which are candidate for purge.
Identify all POs matched to receipts which are candidate for purge.
Identify PO-Requisition relationships in interim table 4.
Identify all requisitions matched to POs which are candidate for purge.
Identify all POs matched to requisitions which are candidate for purge.
Combine results from interim tables 1-4 into a single transaction chain interim table.

Technically, the metadata is constructed using the below statements.