[Home] [Help]
4: --This bug mainly solves most of the performance related issues reported
5: --in SQLREP.
6: --There are two kinds of fixes.
7: --NO_UNNEST is used in the inner query to prevent FTS on large tables.
8: --Introduction of AP_INVOICES_ALL,AP_SYSTEM_PARAMETERS_ALL to force an
9: --access path.
10: -- Private Variables
11: -- Declaring the global variables
12: g_debug_switch VARCHAR2(1) := 'N';
596: /* Bug 8913560
597: -- Bug 8942883 Begin
598: SELECT MAX( invoice_id )
599: INTO max_inv_id
600: FROM ap_invoices ;
601:
602: SELECT MAX( check_id )
603: INTO max_chk_id
604: FROM ap_invoice_payments ; */
605: SELECT last_number
606: INTO max_inv_id
607: FROM all_sequences
608: WHERE sequence_owner = l_sch_name
609: AND sequence_name = 'AP_INVOICES_S' ;
610:
611: SELECT last_number
612: INTO max_chk_id
613: FROM all_sequences
777: WHERE d.invoice_id = pl.invoice_id
778: AND d.pa_addition_flag in ('Y','T'))
779: OR EXISTS
780: (SELECT 'project-related expense report'
781: FROM ap_invoices i
782: WHERE i.invoice_id = pl.invoice_id
783: AND i.source = 'Oracle Project Accounting');
784:
785: l_invoice_id NUMBER;
810: DELETE /*+ PARALLEL(pl) ROWID(pl) */
811: FROM ap_purge_invoice_list pl
812: WHERE pl.rowid IN
813: (SELECT /*+ ORDERED PARALLEL(pl1) FULL(pl1) USE_NL(i,ps)
814: INDEX(i AP_INVOICES_U1) INDEX(ps AP_PAYMENT_SCHEDULES_U1) */
815: pl1.rowid
816: FROM ap_purge_invoice_list pl1,
817: ap_invoices i,
818: ap_payment_schedules ps
813: (SELECT /*+ ORDERED PARALLEL(pl1) FULL(pl1) USE_NL(i,ps)
814: INDEX(i AP_INVOICES_U1) INDEX(ps AP_PAYMENT_SCHEDULES_U1) */
815: pl1.rowid
816: FROM ap_purge_invoice_list pl1,
817: ap_invoices i,
818: ap_payment_schedules ps
819: WHERE i.invoice_id = pl1.invoice_id
820: AND ps.invoice_id = i.invoice_id
821: AND ((ps.payment_status_flag <> 'Y' AND i.cancelled_date IS NULL)
836: DELETE
837: FROM ap_purge_invoice_list PL
838: WHERE EXISTS (
839: SELECT 'Invoices are not transfered to PA'
840: FROM ap_invoices I,
841: ap_invoice_distributions D
842: WHERE I.invoice_id = PL.invoice_id
843: AND I.invoice_id = D.invoice_id
844: AND I.source = 'Oracle Project Accounting'
852: WHERE d.invoice_id = pl.invoice_id
853: AND d.project_id is not null) -- bug1746226
854: OR EXISTS
855: (SELECT 'project-related expense report'
856: FROM ap_invoices i
857: WHERE i.invoice_id = pl.invoice_id
858: AND i.source = 'Oracle Project Accounting'); */
859:
860: OPEN pa_related_invoices;
910: DELETE /*+ PARALLEL(pl) ROWID(pl) */
911: FROM ap_purge_invoice_list pl
912: WHERE pl.rowid IN
913: (SELECT /*+ ORDERED PARALLEL(pl1) FULL(pl1) USE_NL(i,d)
914: INDEX(i AP_INVOICES_U1) INDEX(d AP_INVOICE_DISTRIBUTIONS_U1) */
915: pl1.rowid
916: FROM ap_purge_invoice_list pl1,
917: ap_invoices i,
918: ap_invoice_distributions d
913: (SELECT /*+ ORDERED PARALLEL(pl1) FULL(pl1) USE_NL(i,d)
914: INDEX(i AP_INVOICES_U1) INDEX(d AP_INVOICE_DISTRIBUTIONS_U1) */
915: pl1.rowid
916: FROM ap_purge_invoice_list pl1,
917: ap_invoices i,
918: ap_invoice_distributions d
919: WHERE i.invoice_id = pl1.invoice_id
920: AND i.invoice_id = d.invoice_id
921: AND (d.last_update_date > g_activity_date
935: DELETE
936: FROM ap_purge_invoice_list PL
937: WHERE EXISTS
938: (SELECT /*+ no_unnest */ 'distributions not purgeable' -- 7759218
939: FROM ap_invoice_distributions D, ap_invoices I
940: WHERE I.invoice_id = D.invoice_id
941: AND PL.invoice_id = D.invoice_id
942: AND ( D.last_update_date > g_activity_date
943: OR D.posted_flag <> 'Y'
1075: WHERE pl.rowid IN (
1076: Select /*+ ORDERED PARALLEL(pl1) FULL(pl1) */
1077: pl1.rowid -- 7759218
1078: from ap_purge_invoice_list pl1,
1079: ap_invoices_all ai,
1080: xla_events xe, --Bug 4588031
1081: xla_transaction_entities xte, --Bug 4588031
1082: xla_ae_headers xeh, --Bug 4588031
1083: ap_system_parameters_all asp--bug5052748
1080: xla_events xe, --Bug 4588031
1081: xla_transaction_entities xte, --Bug 4588031
1082: xla_ae_headers xeh, --Bug 4588031
1083: ap_system_parameters_all asp--bug5052748
1084: where xte.entity_code = 'AP_INVOICES'
1085: and NVL(XTE.SOURCE_ID_INT_1,-99) = PL1.invoice_id --11059839
1086: AND pl1.invoice_id=ai.invoice_id
1087: AND ai.org_id=asp.org_id
1088: AND asp.set_of_books_id=xte.ledger_id
1601: purge_name,
1602: double_check_flag
1603: )
1604: WITH purge_inv AS
1605: (SELECT /*+ MATERIALIZE INDEX(i AP_INVOICES_N5) */ rowid inv_rowid
1606: FROM ap_invoices i
1607: WHERE i.invoice_date <= g_activity_date
1608: ) ,
1609: purge_zero_inv AS
1602: double_check_flag
1603: )
1604: WITH purge_inv AS
1605: (SELECT /*+ MATERIALIZE INDEX(i AP_INVOICES_N5) */ rowid inv_rowid
1606: FROM ap_invoices i
1607: WHERE i.invoice_date <= g_activity_date
1608: ) ,
1609: purge_zero_inv AS
1610: (SELECT /*+ MATERIALIZE INDEX(i AP_INVOICES_N4)*/ rowid inv_rowid
1606: FROM ap_invoices i
1607: WHERE i.invoice_date <= g_activity_date
1608: ) ,
1609: purge_zero_inv AS
1610: (SELECT /*+ MATERIALIZE INDEX(i AP_INVOICES_N4)*/ rowid inv_rowid
1611: FROM ap_invoices i
1612: WHERE i.invoice_amount = 0
1613: )
1614: SELECT invoice_id, p_purge_name purge_name, 'Y' double_check_flag
1607: WHERE i.invoice_date <= g_activity_date
1608: ) ,
1609: purge_zero_inv AS
1610: (SELECT /*+ MATERIALIZE INDEX(i AP_INVOICES_N4)*/ rowid inv_rowid
1611: FROM ap_invoices i
1612: WHERE i.invoice_amount = 0
1613: )
1614: SELECT invoice_id, p_purge_name purge_name, 'Y' double_check_flag
1615: FROM
1619: INDEX(p AP_INVOICE_PAYMENTS_N1) INDEX(c AP_CHECKS_U1) INDEX(d
1620: AP_INVOICE_DISTRIBUTIONS_U1) INDEX(l AP_INVOICE_LINES_U1) */
1621: i.invoice_id, MIN(c.check_id) check_id
1622: FROM purge_inv pi,
1623: ap_invoices i,
1624: ap_invoice_payments p,
1625: ap_checks c,
1626: ap_invoice_distributions d,
1627: ap_invoice_lines l
1648: SELECT /*+ ORDERED PARALLEL(pzi) FULL(pzi) USE_NL(i,p,d)
1649: INDEX(p AP_INVOICE_PAYMENTS_N1) INDEX(d AP_INVOICE_DISTRIBUTIONS_U1) */
1650: i.invoice_id, NULL check_id
1651: FROM purge_zero_inv pzi,
1652: ap_invoices i,
1653: ap_invoice_payments p,
1654: ap_invoice_distributions d
1655: WHERE i.rowid = pzi.inv_rowid
1656: AND p.invoice_id (+) = i.invoice_id
1765: -- test ap vendors
1766: delete from po_purge_vendor_list pvl
1767: where exists
1768: (select null
1769: from ap_invoices_all ai
1770: where ai.vendor_id = pvl.vendor_id)
1771: or exists
1772: (select null
1773: from ap_selected_invoices_all asi,
1871:
1872:
1873: delete from po_purge_vendor_list pvl
1874: where exists (select null
1875: from ap_invoices_all ai
1876: where ai.vendor_id = pvl.vendor_id)
1877: or exists (select null
1878: from ap_selected_invoices_all asi,
1879: ap_supplier_sites_all pvs
2389: doc_sequence_value,org_id)
2390: SELECT i.invoice_id, i.vendor_id, v.vendor_site_code, i.invoice_num,
2391: i.invoice_date, i.invoice_amount, b.batch_name, p_purge_name,
2392: i.doc_sequence_id, i.doc_sequence_value,i.org_id
2393: FROM ap_invoices_all i, ap_supplier_sites_all v, ap_batches_all b
2394: WHERE i.vendor_site_id = v.vendor_site_id
2395: AND i.batch_id = b.batch_id (+)
2396: AND i.invoice_id IN (SELECT PL.invoice_id
2397: FROM ap_purge_invoice_list PL
2978: WHERE d.invoice_id = pl.invoice_id
2979: AND d.pa_addition_flag in ('Y','T'))
2980: OR EXISTS
2981: (SELECT 'project-related expense report'
2982: FROM ap_invoices i
2983: WHERE i.invoice_id = pl.invoice_id
2984: AND i.source = 'Oracle Project Accounting');
2985:
2986: l_invoice_id NUMBER;
3005: SET PL.double_check_flag = 'N'
3006: WHERE PL.double_check_flag = 'Y'
3007: AND EXISTS(
3008: SELECT 'invoice no longer purgeable'
3009: FROM ap_invoices I
3010: WHERE PL.invoice_id = I.invoice_id
3011: AND (( I.payment_status_flag <> 'Y'
3012: AND
3013: I.invoice_amount <> 0)
3045: SET PL.double_check_flag = 'N'
3046: WHERE PL.double_check_flag = 'Y'
3047: AND EXISTS (
3048: SELECT 'Invoices are not transfered to PA'
3049: FROM ap_invoices I,
3050: ap_invoice_distributions D
3051: WHERE I.invoice_id = PL.invoice_id
3052: AND I.invoice_id = D.invoice_id
3053: AND I.source <> 'Oracle Project Accounting'
3063: AND d.project_id is not null -- bug1746226
3064: )
3065: OR EXISTS
3066: (SELECT 'project-related expense report'
3067: FROM ap_invoices i
3068: WHERE i.invoice_id = pl.invoice_id
3069: AND i.source = 'Oracle Project Accounting'
3070: )); */
3071:
3115: WHERE PL.double_check_flag = 'Y'
3116: AND EXISTS (
3117: SELECT /*+NO_UNNEST*/ 'payment schedule no longer purgeable'
3118: FROM ap_payment_schedules PS,
3119: ap_invoices I
3120: WHERE PS.invoice_id = PL.invoice_id
3121: AND PS.invoice_id = I.invoice_id
3122: AND ((PS.payment_status_flag <> 'Y'
3123: AND I.cancelled_date is null)
3146: SET PL.double_check_flag = 'N'
3147: WHERE PL.double_check_flag = 'Y'
3148: AND EXISTS
3149: (SELECT /*+NO_UNNEST*/ 'distribution no longer purgeable'
3150: FROM ap_invoice_distributions D, ap_invoices I
3151: WHERE I.invoice_id = D.invoice_id
3152: AND PL.invoice_id = D.invoice_id
3153: AND ( D.last_update_date > g_activity_date
3154: OR D.posted_flag <> 'Y'
3166: SET PL.double_check_flag = 'N'
3167: WHERE PL.double_check_flag = 'Y'
3168: AND EXISTS
3169: (SELECT /*+NO_UNNEST*/'distribution no longer purgeable'
3170: FROM ap_invoice_distributions D, ap_invoices I
3171: WHERE I.invoice_id = D.invoice_id
3172: AND PL.invoice_id = D.invoice_id
3173: AND ( D.last_update_date > g_activity_date
3174: OR D.posted_flag <> 'Y'
3279: SELECT 'invoice accounting not purgeable'
3280: FROM xla_events xe,
3281: xla_ae_headers xeh,
3282: xla_transaction_entities xte,
3283: ap_invoices_all ai,
3284: ap_system_parameters_all asp --bug5052748
3285: where xte.entity_code = 'AP_INVOICES'
3286: and xte.entity_id = xe.entity_id
3287: and NVL(XTE.SOURCE_ID_INT_1,-99) = PL.invoice_id /* Bug#12615876 */
3281: xla_ae_headers xeh,
3282: xla_transaction_entities xte,
3283: ap_invoices_all ai,
3284: ap_system_parameters_all asp --bug5052748
3285: where xte.entity_code = 'AP_INVOICES'
3286: and xte.entity_id = xe.entity_id
3287: and NVL(XTE.SOURCE_ID_INT_1,-99) = PL.invoice_id /* Bug#12615876 */
3288: AND ai.invoice_id=pl.invoice_id
3289: AND ai.org_id=asp.org_id
3569: /* Bug 8913560
3570: -- Bug 8942883 Begins
3571: SELECT MAX( invoice_id )
3572: INTO max_inv_id
3573: FROM ap_invoices ;
3574:
3575: SELECT MAX( check_id )
3576: INTO max_chk_id
3577: FROM ap_invoice_payments ; */
3578: SELECT last_number
3579: INTO max_inv_id
3580: FROM all_sequences
3581: WHERE sequence_owner = l_sch_name
3582: AND sequence_name = 'AP_INVOICES_S' ;
3583:
3584: SELECT last_number
3585: INTO max_chk_id
3586: FROM all_sequences
3777: INTO fp_invoice_payment_rows
3778: FROM ap_invoice_payments;
3779:
3780: --
3781: debug_info := 'ap_invoices';
3782: IF g_debug_switch in ('y','Y') THEN
3783: Print('(Count_Ap_Rows)'||debug_info);
3784: END IF;
3785:
3785:
3786: --
3787: SELECT count(*)
3788: INTO fp_invoice_rows
3789: FROM ap_invoices;
3790:
3791: RETURN (TRUE);
3792:
3793: RETURN NULL; EXCEPTION
4015: update po_purge_vendor_list pvl
4016: set double_check_flag = 'N'
4017: where pvl.double_check_flag = 'Y'
4018: and (exists (select null
4019: from ap_invoices_all ai
4020: where ai.vendor_id = pvl.vendor_id)
4021: or
4022: exists (select null
4023: from ap_selected_invoices_all asi,
4529: UPDATE financials_purges
4530: SET
4531: ap_checks = fp_check_rows,
4532: ap_invoice_payments = fp_invoice_payment_rows,
4533: ap_invoices = fp_invoice_rows,
4534: po_headers = fp_po_header_rows ,
4535: po_receipts = fp_receipt_line_rows,
4536: po_requisition_headers = fp_req_header_rows,
4537: po_vendors = fp_vendor_rows,
5436: WHERE PL.double_check_flag = 'Y'
5437: AND -- Bug 8913560 PL.invoice_id BETWEEN low_inv_id AND high_inv_id
5438: PL.rowid BETWEEN p_start_rowid AND p_end_rowid ;
5439:
5440: l_invoice_id ap_invoices.invoice_id%TYPE;
5441: l_invoice_dist_id ap_invoice_distributions.invoice_distribution_id%TYPE;
5442: l_check_id ap_checks.check_id%TYPE;
5443: l_payment_history_id ap_payment_history.payment_history_id%TYPE;
5444: l_invoice_payment_id ap_invoice_payments.invoice_payment_id%TYPE;
5719: DELETE FROM ap_doc_sequence_audit AUD
5720: WHERE (AUD.doc_sequence_id , AUD.doc_sequence_value)
5721: IN (SELECT I.doc_sequence_id , I.doc_sequence_value
5722: FROM ap_purge_invoice_list PL,
5723: ap_invoices I
5724: WHERE PL.double_check_flag = 'Y'
5725: AND -- Bug 8913560 PL.invoice_id BETWEEN range_low AND range_high
5726: PL.rowid BETWEEN p_start_rowid AND p_end_rowid
5727: AND PL.invoice_id = I.invoice_id);
5736: --Bug 2840203 DBI logging
5737: --We are only logging the invoice deletion, as the summary code knows to
5738: --delete all related transactions: dists, holds, payment shedules, payments
5739: AP_DBI_PKG.Maintain_DBI_Summary
5740: (p_table_name => 'AP_INVOICES',
5741: p_operation => 'D',
5742: p_key_value1 => l_invoice_id,
5743: p_calling_sequence => current_calling_sequence);
5744:
5744:
5745: END LOOP;
5746: CLOSE ap_invoice_cur;
5747:
5748: debug_info := 'ap_invoices';
5749: IF g_debug_switch in ('y','Y') THEN
5750: Print('(Delete_AP_Tables)'||debug_info);
5751: END IF;
5752:
5752:
5753:
5754: -- delete_invoices
5755:
5756: DELETE FROM ap_invoices
5757: WHERE invoice_id IN (
5758: SELECT PL.invoice_id
5759: FROM ap_purge_invoice_list PL
5760: WHERE PL.double_check_flag = 'Y'
5806: DELETE FROM ap_batches B
5807: WHERE B.last_update_date <= g_activity_date
5808: AND NOT EXISTS (
5809: SELECT null
5810: FROM ap_invoices I
5811: WHERE I.batch_id = B.batch_id); */
5812:
5813: -- Bug 8913560 COMMIT;
5814:
5932: FROM ap_ae_headers aeh
5933: ,ap_accounting_events aae
5934: ,ap_purge_invoice_list pil
5935: WHERE aae.source_id = pil.invoice_id
5936: and aae.source_table = 'AP_INVOICES'
5937: and aae.accounting_event_id = aeh.accounting_event_id
5938: and pil.double_check_flag = 'Y'
5939: and pil.invoice_id BETWEEN range_low AND range_high) ;
5940:
5963: ( SELECT aae.accounting_event_id
5964: FROM ap_accounting_events aae
5965: , ap_purge_invoice_list pil
5966: WHERE aae.source_id = pil.invoice_id
5967: and aae.source_table = 'AP_INVOICES'
5968: and pil.double_check_flag = 'Y'
5969: -- Commented the below line as a fix for bug 2880690
5970: -- and aae.accounting_event_id = aeh.accounting_event_id
5971: and pil.invoice_id BETWEEN range_low AND range_high
6015: WHERE aae.source_id in (SELECT PIL.INVOICE_ID
6016: FROM AP_PURGE_INVOICE_LIST PIL
6017: WHERE PIL.DOUBLE_CHECK_FLAG = 'Y'
6018: AND PIL.INVOICE_ID BETWEEN range_low AND range_high )
6019: AND AAE.SOURCE_TABLE = 'AP_INVOICES'
6020: ;
6021:
6022: DELETE FROM AP_ACCOUNTING_EVENTS AAE WHERE
6023: aae.source_id in ( SELECT APC.CHECK_ID
6889: UPDATE financials_purges
6890: SET
6891: ap_checks = check_rows,
6892: ap_invoice_payments = invoice_payment_rows,
6893: ap_invoices = invoice_rows,
6894: ap_ae_lines = ae_line_rows,
6895: ap_ae_headers = ae_header_rows,
6896: ap_accounting_events = accounting_event_rows
6897: WHERE purge_name = p_purge_name;
6900: ELSE */
6901: IF l_purge_without_review <> 'Y' THEN
6902: SELECT NVL( ap_checks, 0 ) ,
6903: NVL( ap_invoice_payments, 0 ) ,
6904: NVL( ap_invoices, 0 ) ,
6905: NVL( ap_ae_lines, 0 ) ,
6906: NVL( ap_ae_headers, 0 ) ,
6907: NVL( ap_accounting_events, 0 )
6908: INTO check_rows,
7016: */
7017: -- Bug 8913560 Start
7018: SELECT NVL( ap_checks, 0 ) ,
7019: NVL( ap_invoice_payments, 0 ) ,
7020: NVL( ap_invoices, 0 ) ,
7021: NVL( ap_ae_lines, 0 ) ,
7022: NVL( ap_ae_headers, 0 ) ,
7023: NVL( ap_accounting_events, 0 )
7024: INTO check_rows,
7192: UPDATE financials_purges
7193: SET
7194: ap_checks = nvl(ap_checks, 0) - check_rows,
7195: ap_invoice_payments = nvl(ap_invoice_payments, 0) - invoice_payment_rows,
7196: ap_invoices = nvl(ap_invoices, 0) - invoice_rows,
7197: po_headers = nvl(po_headers, 0) - po_header_rows,
7198: po_requisition_headers = nvl(po_requisition_headers, 0) - req_header_rows,
7199: po_vendors = nvl(po_vendors, 0) - vendor_rows,
7200: po_receipts = nvl(po_receipts, 0) - shipment_line_rows,
7240: DELETE FROM ap_batches B
7241: WHERE B.last_update_date <= g_activity_date
7242: AND NOT EXISTS (
7243: SELECT null
7244: FROM ap_invoices_all I --bug13799066
7245: WHERE I.batch_id = B.batch_id);
7246:
7247: -- clear_invoice_purge_list
7248: -- 9481539
7983: BEGIN
7984: UPDATE financials_purges
7985: SET ap_checks = NVL(ap_checks , 0 ) + p_check_rows,
7986: ap_invoice_payments = NVL(ap_invoice_payments , 0 ) + p_invoice_payment_rows,
7987: ap_invoices = NVL(ap_invoices , 0 ) + p_invoice_rows,
7988: ap_invoice_lines = NVL(ap_invoice_lines , 0 ) + p_invoice_lines_rows, --bug 11829621
7989: ap_invoice_distributions = NVL(ap_invoice_distributions , 0 ) + p_invoice_distributions_rows, --bug 11829621
7990: ap_ae_lines = NVL(ap_ae_lines , 0 ) + p_ae_line_rows,
7991: ap_ae_headers = NVL(ap_ae_headers , 0 ) + p_ae_header_rows,