Table of Contents

Search

  1. Preface
  2. Backing Up and Restoring the Data Vault
  3. Configuring Centera as a Remote Data Vault Store
  4. Configuring Data Archive for a Legacy Source Using Legacy Adapters
  5. Data Archive Seamless Access for PeopleSoft
  6. Data Archive Transaction Restore API
  7. Dropping and Truncating Partitions in Data Archive
  8. High Availability Configuration for the Data Archive and File Archive Service Versions 6.1 to 6.3
  9. 0955-High Availability Configuration for the Data Vault Version 6.4 and Later
  10. How to Create Business Rules to Archive and Purge Transactional Data
  11. How to Uninstall Data Archive 5.1
  12. How to Uninstall Data Archive 5.3
  13. How to Use Scripts to Change Database User Passwords in the ILM Repository
  14. IBM DB2 Database Connectivity and Setup for Data Archive
  15. Installing Data Visualization
  16. Integrating Third-Party Schedulers in ILM Engine
  17. Parallel Processing in Data Archive
  18. Seamless Access Configuration for Siebel Applications
  19. Seamless Access Setup for Oracle E-Business Suite
  20. Seamless Access Setup for Oracle R12 in Data Archive
  21. Using the Data Vault Service JDBC Driver to Connect to the Data Vault
  22. Using Multiple Engines in an ILM Environment
  23. Using PowerExchange ODBC Connections in a Data Archive Retirement Project
  24. Discovering Foreign Key Relationships in Enterprise Data Manager

Data Archive How-To Guide

Data Archive How-To Guide

Creating Loop Metadata

Creating Loop Metadata

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.
  • Req to PO: The PO Line is stored in the Requisition Line. From a technical perspective the relationship is defined as:
    PO.PO_LINE_LOCATIONS_ALL.LINE_LOCATION_ID = PO.PO_REQUISITION_LINE_ALL.LINE_LOCATION_ID
  • PO to Receipt: The PO ID is stored in the Receipt Line. The technical representation of this relationship is:
    PO.PO_HEADERS_ALL.PO_HEADER_ID = PO.RCV_SHIPMENT_LINES.PO_HEADER_ID
  • PO to Invoice: The PO Line is stored on the Invoice Line. From a technical perspective the relationship is defined as:
    PO.PO_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID = AP.AP_INVOICE_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID
  • Invoice to Check: A cross reference table provides the relationship between what checks paid what invoices:
    AP.AP_INVOICE_PAYMENTS_ALL.INVOICE_ID AP.AP_INVOICE_PAYMENTS_ALL.CHECK_ID
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:
  1. Identify Invoice-Check relationships in interim table 1.
  2. Identify Invoice-PO relationships in interim table 2.
  3. Identify PO-Receipt relationships in interim table 3.
  4. Identify PO-Requisition relationships in interim table 4.
  5. 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:
  1. Identify Invoice-Check relationships in interim table 1.
    1. Identify all checks that paid invoices which are candidates for purge.
    2. Identify all invoices that were paid by checks which are candidates for purge.
  2. Identify Invoice-PO relationships in interim table 2.
    1. Identify all POs matched to the invoices which are candidates for purge.
    2. Identify all invoices matched to POs which are candidate for purge.
  3. Identify PO-Receipt relationships in interim table 3.
    1. Identify all receipts matched to POs which are candidate for purge.
    2. Identify all POs matched to receipts which are candidate for purge.
  4. Identify PO-Requisition relationships in interim table 4.
    1. Identify all requisitions matched to POs which are candidate for purge.
    2. Identify all POs matched to requisitions which are candidate for purge.
  5. Combine results from interim tables 1-4 into a single transaction chain interim table.
Technically, the metadata is constructed using the below statements.