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. Using the Data Vault Service JDBC Driver to Connect to the Data Vault
  21. Using Multiple Engines in an ILM Environment
  22. Using PowerExchange ODBC Connections in a Data Archive Retirement Project
  23. Discovering Foreign Key Relationships in Enterprise Data Manager

Data Archive How-To Guide

Data Archive How-To Guide

Invoice Check Relationships:

Invoice Check Relationships:

INSERT INTO XA04149_PO4 (INVOICE_ID, CHECK_ID, INV_PURGEABLE_FLAG) SELECT ip.invoice_id, ip.check_id, xa_function_pkg.get_purgeable_flag_number('XA04149_PO3','INVOICE_ID', ip.invoice_id) FROM ap.ap_invoice_payments_all ip WHERE invoice_id in (select x.invoice_id from XA04149_PO16 x); INSERT INTO XA04149_PO4 (INVOICE_ID, CHECK_ID, INV_PURGEABLE_FLAG) SELECT ip.invoice_id, ip.check_id, xa_function_pkg.get_purgeable_flag_number('XA04149_PO3','INVOICE_ID', ip.invoice_id) FROM ap.ap_invoice_payments_all ip WHERE check_id in (select ip.check_id from ap.ap_invoice_payments_all ip, XA04149_PO3 i where i.invoice_id = ip.invoice_id);
First, note the use of the function - xa_function_pkg.get_purgeable_flag_number. This function determines if the transaction is purgeable or not. So, each INVOICE-CHECK combination is being marked as purgeable or not purgeable. This function is part of the archive engine and can be used in custom entities.
Second, note that the select does not necessarily retrieve invoices or checks that have been identified as candidates for purge. That is, they may not exist in the interim table because they did not meet the policy scoping criteria (e.g., the invoice date may fall outside the retention period). The purpose of the select statements is to identify all INVOICE-CHECK combinations regardless of both invoice and check being identified as a candidate for purge.
Third, note that the first INSERT retrieves INVOICE-CHECK combinations based on the invoices identified as candidates for purge (that is, the invoices that do exist in the invoice interim table). The second INSERT retrieves INVOICE-CHECK combinations based on the checks that paid the invoices identified as candidates for purge. This covers both sides of the INVOICE-CHECK relationship.
Examination of the other transaction relationships will show a similar construct.
Invoice PO Relationships
INSERT INTO XA04149_PO8 (PO_HEADER_ID, INVOICE_ID, PO_PURGEABLE_FLAG, PURGEABLE_FLAG) SELECT D.po_header_id, i.invoice_id, xa_function_pkg.get_purgeable_flag_number('XA04149_PO12', 'PO_HEADER_ID', d.po_header_id), ‘N’ FROM PO.po_distributions_all D, AP.ap_invoice_distributions_all I WHERE I.invoice_id in (SELECT X.invoice_id FROM XA04149_PO4 X ) AND D.po_distribution_id = I.po_distribution_id; INSERT INTO XA04149_PO8 (PO_HEADER_ID, INVOICE_ID, PO_PURGEABLE_FLAG, PURGEABLE_FLAG) SELECT D.po_header_id, i.invoice_id, xa_function_pkg.get_purgeable_flag_number('XA04149_PO12', 'PO_HEADER_ID',d.po_header_id), 'N' FROM PO.po_distributions_all D, AP.ap_invoice_distributions_all I WHERE D.po_header_id in (SELECT X.po_header_id FROM XA04149_PO12 X ) AND D.po_distribution_id = I.po_distribution_id;
The same three principles exist here as they did for the INVOICE-CHECK relationship. First, the xa_function_pkg is used to identify the purgeability of the PO-INVOICE combination. Second, both statements retrieve PO-INVOICE combination without regard for both transactions being candidates for purge. Third, PO-INVOICE combinations are being retrieved for both PO candidates and INVOICE candidates.
PO Requisition Relationship
INSERT /*+ APPEND PARALLEL(XA04149_PO15,8) */ INTO XA04149_PO15 (po_header_id, requisition_header_id, req_purgeable_flag) SELECT /*+ FULL(pll) PARALLEL(pll,4) */ pll.po_header_id, prl.requisition_header_id, xa_function_pkg.get_purgeable_flag_number('XA04149_PO14', 'REQUISITION_HEADER_ID',prl.requisition_header_id) FROM PO.po_line_locations_all pll, PO.po_requisition_lines_all prl WHERE prl.line_location_id = pll.line_location_id and prl.requisition_header_id in (select requisition_header_id from XA04149_PO14); INSERT /*+ APPEND */ INTO XA04149_PO15 (po_header_id, requisition_header_id, req_purgeable_flag) SELECT pll.po_header_id, prl.requisition_header_id, xa_function_pkg.get_purgeable_flag_number('XA04149_PO14', 'REQUISITION_HEADER_ID',prl.requisition_header_id) FROM PO.po_line_locations_all pll, PO.po_requisition_lines_all prl WHERE prl.line_location_id = pll.line_location_id and pll.po_header_id in (select po_header_id from XA04149_PO12);
The PO-REQUISITION relationship uses the same approach as the INVOICE-CHECK and PO-INVOICE relationships. First, the xa_function_pkg is used to identify the purgeability of the PO-REQUISITION combination. Second, both statements retrieve PO-REQUISITION combinations without regard for both transactions being candidates for purge. Third, PO-REQUISITION combinations are being retrieved for both PO candidates and REQUISITION candidates.
PO Shipment Relationship
INSERT /*+ APPEND */ INTO XA04149_PO13 (po_header_id, shipment_header_id, rcpt_purgeable_flag) SELECT A.po_header_id, A.shipment_header_id, xa_function_pkg.get_purgeable_flag_number('XA04149_PO12', 'PO_HEADER_ID',A.po_header_id) FROM po.rcv_shipment_lines A WHERE A.shipment_header_id in (select shipment_header_id from po.rcv_shipment_lines sl, XA04149_PO12 x where sl.po_header_id = x.po_header_id );
The PO-RECEIPT relationship is treated differently than the other relationships. Receipts are not tracked separately as candidates for purge. They are part of a PO transaction, and consequently, they are candidates for purge through the PO only. Therefore, PO-RECEIPT combinations are retrieved only for PO candidates - that is, all receipts for PO candidates are identified as part of the transaction chain. However, the statement above still uses the xa_function_pkg to determine purgeability of each PO-RECEIPT combination, and the statement retrieves PO-RECEIPT combinations without regard for both transactions being candidates for purge.
The Transaction Chain Interim
Once all of the combination pairs have been identified, these combinations can be put together into one interim table - the transaction chain interim table. The statements below show how the previous interim tables are combined into a single transaction chain interim table. There are two INSERT statements used to reduce the complexity of the SQL statement which also results in improved performance.
INSERT /*+ APPEND */ INTO XA04149_PO10 (PO_HEADER_ID, REQUISITION_HEADER_ID, purgeable_flag, purge_PO_HEADER_ID, purge_REQUISITION_HEADER_ID) SELECT r.po_header_id, r.requisition_header_id, decode(r.req_purgeable_flag,'Y',decode(nvl(x.po_header_id,-1),-1,null,x.purgeable_flag),null) flag, r.po_header_id, r.requisition_header_id FROM XA04149_PO12 x, XA04149_PO15 r WHERE r.po_header_id = x.po_header_id (+) UNION SELECT DISTINCT null, null, s.po_header_id, null, s.shipment_header_id, decode(s.rcpt_purgeable_flag,'Y', decode(nvl(x.po_header_id,-1),-1,null,x.purgeable_flag),null) flag, null, null, s.po_header_id, null, s.shipment_header_id FROM XA04149_PO12 x, XA04149_PO13 s where s.po_header_id = x.po_header_id (+); INSERT /*+ APPEND PARALLEL(XA04149_PO10,8) */ INTO XA04149_PO10 (INVOICE_ID, CHECK_ID, PO_HEADER_ID, purgeable_flag, purge_INVOICE_ID, purge_CHECK_ID, purge_PO_HEADER_ID) SELECT /*+ full(i) cardinality(i,1) parallel(i,#) */ DISTINCT i.invoice_id, i.check_id, a.po_header_id, decode(matched_to_po_flag,0,decode(inv_purgeable_flag,'Y',po_purgeable_flag,null),inv_purgeable_flag) flag, i.invoice_id, i.check_id, a.po_header_id FROM XA04149_PO4 i, XA04149_PO8 a WHERE i.invoice_id = a.invoice_id (+) union select /*+ parallel (a,#) */ distinct a.invoice_id, null, a.po_header_id, null, null, decode(a.purgeable_flag,'Y',a.purgeable_flag,null) flag, a.invoice_id, null, a.po_header_id, null, null from XA04149_PO8 a where not exists (select 1 from XA04149_PO4 b where b.invoice_id=a.invoice_id);
The end result is table that looks the below:
TRX CHAIN INTERIM
REQ
PO
VCHR
VCHR
PURGE_FLAG
1A
10A
Y
1A
10B
N
1B
10A
Y
1B
10A
Y
1C
10C
Y
10A
100A
Y
10B
100A
N
10A
100B
Y
10A
100C
Y
10C
100D
Y
100A
1000A
Y
100A
1000A
N
100B
1000A
Y
100C
1000B
Y
100D
1000D
Y
Stepping through the LOOP rules again, the end result of the transaction chain interim is the same as the initial example in the previous section:
TRX CHAIN INTERIM
REQ
PO
VCHR
VCHR
PURGE_FLAG
1A
10A
N
1A
10B
N
1B
10A
N
1B
10A
N
1C
10C
Y
10A
100A
N
10B
100A
N
10A
100B
N
10A
100C
N
10C
100D
Y
100A
1000A
N
100A
1000A
N
100B
1000A
N
100C
1000B
N
100D
1000D
Y
Although the chain spans multiple rows, the transaction chain of 1C-10C-100D-1000D is still purgeable in the above interim table.
The final step of the transaction chain logic is to update the transaction interim tables with the results of the transaction chain interim:
NOT EXISTS (SELECT 1 FROM XA04149_PO10 X WHERE X.purge_po_header_id = A.po_header_id AND X.purgeable_flag = 'Y') NOT EXISTS (SELECT 1 FROM XA04149_PO10 X WHERE X.purge_invoice_id = A.invoice_id AND X.purgeable_flag = 'Y') NOT EXISTS (SELECT 1 FROM XA04149_PO10 X WHERE X.purge_requisition_header_id = A.requisition_header_id AND X.purgeable_flag = 'Y')