Data Archive
- Data Archive 6.5
- All Products
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);
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;
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);
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 );
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);
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 | ||
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 | ||
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')