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:
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:
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')
