eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.AP_PURGE_PKG dependencies on AP_INVOICES

Line 8: --Introduction of AP_INVOICES_ALL,AP_SYSTEM_PARAMETERS_ALL to force an

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';

Line 683: ap_invoices I

679: FROM ap_purge_invoice_list PL
680: WHERE EXISTS (
681: SELECT 'payment schedule not purgeable'
682: FROM ap_payment_schedules PS,
683: ap_invoices I
684: WHERE PS.invoice_id = PL.invoice_id
685: AND PS.invoice_id = I.invoice_id
686: AND ((PS.payment_status_flag <> 'Y'
687: AND I.cancelled_date is null)

Line 709: FROM ap_invoices i

705: WHERE d.invoice_id = pl.invoice_id
706: AND d.project_id is not null) -- bug1746226
707: OR EXISTS
708: (SELECT 'project-related expense report'
709: FROM ap_invoices i
710: WHERE i.invoice_id = pl.invoice_id
711: AND i.source = 'Oracle Project Accounting');
712:
713: END IF;

Line 741: FROM ap_invoice_distributions D, ap_invoices I

737: DELETE
738: FROM ap_purge_invoice_list PL
739: WHERE EXISTS
740: (SELECT 'distributions not purgeable'
741: FROM ap_invoice_distributions D, ap_invoices I
742: WHERE I.invoice_id = D.invoice_id
743: AND PL.invoice_id = D.invoice_id
744: AND ( D.last_update_date > g_activity_date
745: OR D.posted_flag <> 'Y'

Line 771: FROM ap_invoice_distributions D, ap_invoices I

767: DELETE
768: FROM ap_purge_invoice_list PL
769: WHERE EXISTS
770: (SELECT 'distributions not purgeable'
771: FROM ap_invoice_distributions D, ap_invoices I
772: WHERE I.invoice_id = D.invoice_id
773: AND PL.invoice_id = D.invoice_id
774: AND ( D.last_update_date > g_activity_date
775: OR D.posted_flag <> 'Y'

Line 923: ap_invoices_all ai,ap_system_parameters_all asp--bug5052748

919: Select 'invoice accounting not purgeable'
920: from xla_events xe, --Bug 4588031
921: xla_transaction_entities xte, --Bug 4588031
922: xla_ae_headers xeh, --Bug 4588031
923: ap_invoices_all ai,ap_system_parameters_all asp--bug5052748
924: where xte.entity_code = 'AP_INVOICES'
925: and xte.source_id_int_1 = PL.invoice_id
926: AND pl.invoice_id=ai.invoice_id
927: AND ai.org_id=asp.org_id

Line 924: where xte.entity_code = 'AP_INVOICES'

920: from xla_events xe, --Bug 4588031
921: xla_transaction_entities xte, --Bug 4588031
922: xla_ae_headers xeh, --Bug 4588031
923: ap_invoices_all ai,ap_system_parameters_all asp--bug5052748
924: where xte.entity_code = 'AP_INVOICES'
925: and xte.source_id_int_1 = PL.invoice_id
926: AND pl.invoice_id=ai.invoice_id
927: AND ai.org_id=asp.org_id
928: AND asp.set_of_books_id=xte.ledger_id

Line 1410: FROM ap_invoices I, ap_invoice_distributions D

1406:
1407: INSERT INTO ap_purge_invoice_list
1408: (invoice_id, purge_name, double_check_flag)
1409: SELECT DISTINCT I.invoice_id, p_purge_name, 'Y'
1410: FROM ap_invoices I, ap_invoice_distributions D
1411: WHERE I.invoice_id = D.invoice_id
1412: AND I.payment_status_flag || '' = 'Y'
1413: AND I.invoice_type_lookup_code <> 'PREPAYMENT'
1414: AND D.posted_flag || '' = 'Y'

Line 1424: FROM ap_invoices I, ap_invoice_distributions D

1420: AND I.last_update_date <= g_activity_date
1421: AND I.invoice_date <= g_activity_date
1422: UNION
1423: SELECT I.invoice_id, p_purge_name, 'Y'
1424: FROM ap_invoices I, ap_invoice_distributions D
1425: WHERE I.invoice_id = D.invoice_id (+)
1426: AND I.last_update_date <= g_activity_date
1427: AND I.invoice_date <= g_activity_date
1428: AND I.invoice_amount = 0

Line 1527: from ap_invoices_all ai

1523: -- test ap vendors
1524: delete from po_purge_vendor_list pvl
1525: where exists
1526: (select null
1527: from ap_invoices_all ai
1528: where ai.vendor_id = pvl.vendor_id)
1529: or exists
1530: (select null
1531: from ap_selected_invoices_all asi,

Line 1633: from ap_invoices_all ai

1629:
1630:
1631: delete from po_purge_vendor_list pvl
1632: where exists (select null
1633: from ap_invoices_all ai
1634: where ai.vendor_id = pvl.vendor_id)
1635: or exists (select null
1636: from ap_selected_invoices_all asi,
1637: ap_supplier_sites_all pvs

Line 2138: FROM ap_invoices_all i, ap_supplier_sites_all v, ap_batches_all b

2134: doc_sequence_value,org_id)
2135: SELECT i.invoice_id, i.vendor_id, v.vendor_site_code, i.invoice_num,
2136: i.invoice_date, i.invoice_amount, b.batch_name, p_purge_name,
2137: i.doc_sequence_id, i.doc_sequence_value,i.org_id
2138: FROM ap_invoices_all i, ap_supplier_sites_all v, ap_batches_all b
2139: WHERE i.vendor_site_id = v.vendor_site_id
2140: AND i.batch_id = b.batch_id (+)
2141: AND i.invoice_id IN (SELECT PL.invoice_id
2142: FROM ap_purge_invoice_list PL

Line 2735: FROM ap_invoices I

2731: SET PL.double_check_flag = 'N'
2732: WHERE PL.double_check_flag = 'Y'
2733: AND EXISTS(
2734: SELECT 'invoice no longer purgeable'
2735: FROM ap_invoices I
2736: WHERE PL.invoice_id = I.invoice_id
2737: AND (( I.payment_status_flag <> 'Y'
2738: AND
2739: I.invoice_amount <> 0)

Line 2762: FROM ap_invoices i

2758: AND d.project_id is not null -- bug1746226
2759: )
2760: OR EXISTS
2761: (SELECT 'project-related expense report'
2762: FROM ap_invoices i
2763: WHERE i.invoice_id = pl.invoice_id
2764: AND i.source = 'Oracle Project Accounting'
2765: ));
2766: end if;

Line 2784: ap_invoices I

2780: WHERE PL.double_check_flag = 'Y'
2781: AND EXISTS (
2782: SELECT /*+NO_UNNEST*/ 'payment schedule no longer purgeable'
2783: FROM ap_payment_schedules PS,
2784: ap_invoices I
2785: WHERE PS.invoice_id = PL.invoice_id
2786: AND PS.invoice_id = I.invoice_id
2787: AND ((PS.payment_status_flag <> 'Y'
2788: AND I.cancelled_date is null)

Line 2815: FROM ap_invoice_distributions D, ap_invoices I

2811: SET PL.double_check_flag = 'N'
2812: WHERE PL.double_check_flag = 'Y'
2813: AND EXISTS
2814: (SELECT /*+NO_UNNEST*/ 'distribution no longer purgeable'
2815: FROM ap_invoice_distributions D, ap_invoices I
2816: WHERE I.invoice_id = D.invoice_id
2817: AND PL.invoice_id = D.invoice_id
2818: AND ( D.last_update_date > g_activity_date
2819: OR D.posted_flag <> 'Y'

Line 2834: FROM ap_invoice_distributions D, ap_invoices I

2830: SET PL.double_check_flag = 'N'
2831: WHERE PL.double_check_flag = 'Y'
2832: AND EXISTS
2833: (SELECT /*+NO_UNNEST*/'distribution no longer purgeable'
2834: FROM ap_invoice_distributions D, ap_invoices I
2835: WHERE I.invoice_id = D.invoice_id
2836: AND PL.invoice_id = D.invoice_id
2837: AND ( D.last_update_date > g_activity_date
2838: OR D.posted_flag <> 'Y'

Line 2946: ap_invoices_all ai,

2942: SELECT 'invoice accounting not purgeable'
2943: FROM xla_events xe,
2944: xla_ae_headers xeh,
2945: xla_transaction_entities xte,
2946: ap_invoices_all ai,
2947: ap_system_parameters_all asp --bug5052748
2948: where xte.entity_code = 'AP_INVOICES'
2949: and xte.entity_id = xe.entity_id
2950: and xte.source_id_int_1 =PL.invoice_id

Line 2948: where xte.entity_code = 'AP_INVOICES'

2944: xla_ae_headers xeh,
2945: xla_transaction_entities xte,
2946: ap_invoices_all ai,
2947: ap_system_parameters_all asp --bug5052748
2948: where xte.entity_code = 'AP_INVOICES'
2949: and xte.entity_id = xe.entity_id
2950: and xte.source_id_int_1 =PL.invoice_id
2951: AND ai.invoice_id=pl.invoice_id
2952: AND ai.org_id=asp.org_id

Line 3277: debug_info := 'ap_invoices';

3273: INTO fp_invoice_payment_rows
3274: FROM ap_invoice_payments;
3275:
3276: --
3277: debug_info := 'ap_invoices';
3278: IF g_debug_switch in ('y','Y') THEN
3279: Print('(Count_Ap_Rows)'||debug_info);
3280: END IF;
3281:

Line 3285: FROM ap_invoices;

3281:
3282: --
3283: SELECT count(*)
3284: INTO fp_invoice_rows
3285: FROM ap_invoices;
3286:
3287: RETURN (TRUE);
3288:
3289: RETURN NULL; EXCEPTION

Line 3515: from ap_invoices_all ai

3511: update po_purge_vendor_list pvl
3512: set double_check_flag = 'N'
3513: where pvl.double_check_flag = 'Y'
3514: and (exists (select null
3515: from ap_invoices_all ai
3516: where ai.vendor_id = pvl.vendor_id)
3517: or
3518: exists (select null
3519: from ap_selected_invoices_all asi,

Line 4029: ap_invoices = fp_invoice_rows,

4025: UPDATE financials_purges
4026: SET
4027: ap_checks = fp_check_rows,
4028: ap_invoice_payments = fp_invoice_payment_rows,
4029: ap_invoices = fp_invoice_rows,
4030: po_headers = fp_po_header_rows ,
4031: po_receipts = fp_receipt_line_rows,
4032: po_requisition_headers = fp_req_header_rows,
4033: po_vendors = fp_vendor_rows,

Line 4944: l_invoice_id ap_invoices.invoice_id%TYPE;

4940: FROM ap_purge_invoice_list PL
4941: WHERE PL.double_check_flag = 'Y'
4942: AND PL.invoice_id BETWEEN low_inv_id AND high_inv_id;
4943:
4944: l_invoice_id ap_invoices.invoice_id%TYPE;
4945: l_invoice_dist_id ap_invoice_distributions.invoice_distribution_id%TYPE;
4946: l_check_id ap_checks.check_id%TYPE;
4947: l_payment_history_id ap_payment_history.payment_history_id%TYPE;
4948: l_invoice_payment_id ap_invoice_payments.invoice_payment_id%TYPE;

Line 5184: ap_invoices I

5180: DELETE FROM ap_doc_sequence_audit AUD
5181: WHERE (AUD.doc_sequence_id , AUD.doc_sequence_value)
5182: IN (SELECT I.doc_sequence_id , I.doc_sequence_value
5183: FROM ap_purge_invoice_list PL,
5184: ap_invoices I
5185: WHERE PL.double_check_flag = 'Y'
5186: AND PL.invoice_id BETWEEN range_low AND range_high
5187: AND PL.invoice_id = I.invoice_id);
5188:

Line 5200: (p_table_name => 'AP_INVOICES',

5196: --Bug 2840203 DBI logging
5197: --We are only logging the invoice deletion, as the summary code knows to
5198: --delete all related transactions: dists, holds, payment shedules, payments
5199: AP_DBI_PKG.Maintain_DBI_Summary
5200: (p_table_name => 'AP_INVOICES',
5201: p_operation => 'D',
5202: p_key_value1 => l_invoice_id,
5203: p_calling_sequence => current_calling_sequence);
5204:

Line 5208: debug_info := 'ap_invoices';

5204:
5205: END LOOP;
5206: CLOSE ap_invoice_cur;
5207:
5208: debug_info := 'ap_invoices';
5209: IF g_debug_switch in ('y','Y') THEN
5210: Print('(Delete_AP_Tables)'||debug_info);
5211: END IF;
5212:

Line 5216: DELETE FROM ap_invoices

5212:
5213:
5214: -- delete_invoices
5215:
5216: DELETE FROM ap_invoices
5217: WHERE invoice_id IN (
5218: SELECT PL.invoice_id
5219: FROM ap_purge_invoice_list PL
5220: WHERE PL.double_check_flag = 'Y'

Line 5257: FROM ap_invoices I

5253: DELETE FROM ap_batches B
5254: WHERE B.last_update_date <= g_activity_date
5255: AND NOT EXISTS (
5256: SELECT null
5257: FROM ap_invoices I
5258: WHERE I.batch_id = B.batch_id);
5259:
5260: COMMIT;
5261:

Line 5376: and aae.source_table = 'AP_INVOICES'

5372: FROM ap_ae_headers aeh
5373: ,ap_accounting_events aae
5374: ,ap_purge_invoice_list pil
5375: WHERE aae.source_id = pil.invoice_id
5376: and aae.source_table = 'AP_INVOICES'
5377: and aae.accounting_event_id = aeh.accounting_event_id
5378: and pil.double_check_flag = 'Y'
5379: and pil.invoice_id BETWEEN range_low AND range_high) ;
5380:

Line 5407: and aae.source_table = 'AP_INVOICES'

5403: ( SELECT aae.accounting_event_id
5404: FROM ap_accounting_events aae
5405: , ap_purge_invoice_list pil
5406: WHERE aae.source_id = pil.invoice_id
5407: and aae.source_table = 'AP_INVOICES'
5408: and pil.double_check_flag = 'Y'
5409: -- Commented the below line as a fix for bug 2880690
5410: -- and aae.accounting_event_id = aeh.accounting_event_id
5411: and pil.invoice_id BETWEEN range_low AND range_high

Line 5459: AND AAE.SOURCE_TABLE = 'AP_INVOICES'

5455: WHERE aae.source_id in (SELECT PIL.INVOICE_ID
5456: FROM AP_PURGE_INVOICE_LIST PIL
5457: WHERE PIL.DOUBLE_CHECK_FLAG = 'Y'
5458: AND PIL.INVOICE_ID BETWEEN range_low AND range_high )
5459: AND AAE.SOURCE_TABLE = 'AP_INVOICES'
5460: ;
5461:
5462: DELETE FROM AP_ACCOUNTING_EVENTS AAE WHERE
5463: aae.source_id in ( SELECT APC.CHECK_ID

Line 6506: ap_invoices = nvl(ap_invoices, 0) - invoice_rows,

6502: UPDATE financials_purges
6503: SET
6504: ap_checks = nvl(ap_checks, 0) - check_rows,
6505: ap_invoice_payments = nvl(ap_invoice_payments, 0) - invoice_payment_rows,
6506: ap_invoices = nvl(ap_invoices, 0) - invoice_rows,
6507: po_headers = nvl(po_headers, 0) - po_header_rows,
6508: po_requisition_headers = nvl(po_requisition_headers, 0) - req_header_rows,
6509: po_vendors = nvl(po_vendors, 0) - vendor_rows,
6510: po_receipts = nvl(po_receipts, 0) - shipment_line_rows,