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_PURGE_INVOICE_LIST

Line 202: from ap_purge_invoice_list

198:
199: -- count_invs
200: select count(1)
201: into invoice_count
202: from ap_purge_invoice_list
203: where double_check_flag = 'Y';
204:
205: if (invoice_count = 0) then
206:

Line 291: debug_info := 'THe AP_PURGE_INVOICE_LIST table contains records. ';

287: end if; -- po_count
288:
289: else -- invoice_count
290:
291: debug_info := 'THe AP_PURGE_INVOICE_LIST table contains records. ';
292: Print('Check_no_purge_in_process' || debug_info);
293: Print('Please make sure no purges are running and clear');
294: Print(' this table. Process terminating.');
295:

Line 438: from ap_purge_invoice_list pl,

434: invoices which are related to payment.
435: */
436: CURSOR c_main IS
437: select pl.invoice_id
438: from ap_purge_invoice_list pl,
439: ap_invoice_payments ip
440: where pl.invoice_id = ip.invoice_id;
441:
442: CURSOR c_main_check(l_invoice_id NUMBER) IS

Line 444: from ap_purge_invoice_list

440: where pl.invoice_id = ip.invoice_id;
441:
442: CURSOR c_main_check(l_invoice_id NUMBER) IS
443: select invoice_id
444: from ap_purge_invoice_list
445: where invoice_id = l_invoice_id
446: and double_check_flag = 'Y';
447:
448: p_count integer;

Line 467: ap_purge_invoice_list pil

463:
464: CURSOR c_inv IS
465: select pil.invoice_id
466: from ap_invoice_payments ip,
467: ap_purge_invoice_list pil
468: where ip.check_id = l_check_id
469: and ip.invoice_id = pil.invoice_id (+) ;
470:
471: l_flag BOOLEAN := FALSE;

Line 472: l_inv_id ap_purge_invoice_list.invoice_id%TYPE;

468: where ip.check_id = l_check_id
469: and ip.invoice_id = pil.invoice_id (+) ;
470:
471: l_flag BOOLEAN := FALSE;
472: l_inv_id ap_purge_invoice_list.invoice_id%TYPE;
473:
474: BEGIN
475:
476: OPEN c_inv ;

Line 583: UPDATE ap_purge_invoice_list

579: p_id := 0 ;
580:
581: FOR y IN 1..p_count LOOP
582: p_id := tab_inv.next(p_id) ;
583: UPDATE ap_purge_invoice_list
584: SET double_check_flag = 'S'
585: WHERE invoice_id = p_id ;
586: END LOOP;
587:

Line 598: DELETE FROM ap_purge_invoice_list

594: p_id := 0 ;
595:
596: FOR y IN 1..p_count LOOP
597: p_id := tab_inv.next(p_id) ;
598: DELETE FROM ap_purge_invoice_list
599: WHERE invoice_id = p_id ;
600: END LOOP;
601: end if;
602:

Line 611: DELETE FROM ap_purge_invoice_list

607: p_id := 0 ;
608:
609: FOR y IN 1..p_count LOOP
610: p_id := tab_check.next(p_id) ;
611: DELETE FROM ap_purge_invoice_list
612: WHERE invoice_id in ( select invoice_id
613: from ap_invoice_payments
614: where check_id = p_id);
615: END LOOP;

Line 625: update ap_purge_invoice_list

621:
622: END LOOP;
623:
624: /* Set flag 'Y' back */
625: update ap_purge_invoice_list
626: set double_check_flag = 'Y'
627: where double_check_flag = 'S' ;
628:
629: debug_info := 'End Of Invoice Validations';

Line 679: FROM ap_purge_invoice_list PL

675: --
676: -- Test Payment Schedules
677:
678: DELETE
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

Line 701: FROM ap_purge_invoice_list PL

697: --
698: -- Test PA Invoices
699:
700: DELETE
701: FROM ap_purge_invoice_list PL
702: WHERE EXISTS
703: (SELECT 'project-related vendor invoices'
704: FROM ap_invoice_distributions d
705: WHERE d.invoice_id = pl.invoice_id

Line 738: FROM ap_purge_invoice_list PL

734:
735: Print('Test Simple Invoice Distributions');
736: -- Test Simple Invoice Distributions
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

Line 768: FROM ap_purge_invoice_list PL

764: ELSE
765: Print('Test All Invoice Distributions');
766: -- Test All Invoice Distributions
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

Line 805: FROM ap_purge_invoice_list PL

801: -- Test Payments
802: -- Perf bug 5052674 -- go to base table AP_INVOICE_PAYMENTS_ALL for
803: -- main SELECT query and base table CE_STATEMENT_RECONCILS_ALL for sub-query
804: DELETE
805: FROM ap_purge_invoice_list PL
806: WHERE EXISTS
807: (SELECT 'payment not purgeable'
808: FROM ap_invoice_payments_all P,
809: ap_checks C

Line 868: FROM ap_purge_invoice_list PL

864: -- Delete Inoivces that have applied Prepayments
865: -- Keep this Statement for Invoices upgrated from 11.0
866:
867: DELETE
868: FROM ap_purge_invoice_list PL
869: WHERE EXISTS
870: (SELECT 'related to prepayment'
871: FROM ap_invoice_prepays IP
872: WHERE PL.invoice_id = IP.invoice_id

Line 879: FROM ap_purge_invoice_list PL

875: -- Bug 2153132 by ISartawi add the Delete Statement to exclude
876: -- invoices with applied Prepayments
877:
878: DELETE
879: FROM ap_purge_invoice_list PL
880: WHERE EXISTS
881: (SELECT 'X'
882: FROM ap_invoice_distributions ID
883: WHERE PL.invoice_id = ID.invoice_id

Line 896: DELETE FROM ap_purge_invoice_list PL

892: IF g_debug_switch in ('y','Y') THEN
893: Print('(Do_independent_inv_checks)' ||debug_info);
894: END IF;
895:
896: DELETE FROM ap_purge_invoice_list PL
897: where EXISTS(
898: select 'history not purgeable'
899: from ap_invoice_payments aip
900: , ap_payment_history aph

Line 917: DELETE FROM ap_purge_invoice_list PL

913:
914:
915: -- Fix for bug 2652768 made changes to below DELETE statement
916: -- Fix for bug 2963666 Added condition to check description is not MRC upgrade
917: DELETE FROM ap_purge_invoice_list PL
918: WHERE EXISTS (
919: Select 'invoice accounting not purgeable'
920: from xla_events xe, --Bug 4588031
921: xla_transaction_entities xte, --Bug 4588031

Line 962: DELETE FROM ap_purge_invoice_list PL

958: IF g_debug_switch in ('y','Y') THEN
959: Print('(Do_independent_inv_checks)' ||debug_info);
960: END IF;
961:
962: DELETE FROM ap_purge_invoice_list PL
963: WHERE EXISTS (
964: select 'matched'
965: from ap_invoice_distributions aid, rcv_transactions rcv
966: where aid.invoice_id = PL.invoice_id

Line 970: DELETE FROM ap_purge_invoice_list PL

966: where aid.invoice_id = PL.invoice_id
967: and aid.rcv_transaction_id = rcv.transaction_id
968: and rcv.last_update_date > g_activity_date);
969:
970: DELETE FROM ap_purge_invoice_list PL
971: WHERE EXISTS
972: (select null
973: from ap_invoice_distributions ad
974: where ad.invoice_id = PL.invoice_id

Line 981: from ap_purge_invoice_list

977: select 'matching' from ap_invoice_distributions ad2
978: where ad2.rcv_transaction_id = ad.rcv_transaction_id
979: and ad2.invoice_id NOT IN (
980: select invoice_id
981: from ap_purge_invoice_list
982: where double_check_flag = 'Y')));
983:
984: -- debug info....
985: SELECT count(*) INTO l_list_count FROM ap_purge_invoice_list;

Line 985: SELECT count(*) INTO l_list_count FROM ap_purge_invoice_list;

981: from ap_purge_invoice_list
982: where double_check_flag = 'Y')));
983:
984: -- debug info....
985: SELECT count(*) INTO l_list_count FROM ap_purge_invoice_list;
986: Print(to_char(l_list_count)||' records in ap_purge_invoice_list table');
987:
988:
989: RETURN (TRUE);

Line 986: Print(to_char(l_list_count)||' records in ap_purge_invoice_list table');

982: where double_check_flag = 'Y')));
983:
984: -- debug info....
985: SELECT count(*) INTO l_list_count FROM ap_purge_invoice_list;
986: Print(to_char(l_list_count)||' records in ap_purge_invoice_list table');
987:
988:
989: RETURN (TRUE);
990: RETURN NULL;

Line 1046: from ap_purge_invoice_list

1042:
1043: -- count_invs
1044: select count(1)
1045: into invoice_count
1046: from ap_purge_invoice_list
1047: where double_check_flag = 'Y';
1048:
1049: l_first_iteration := TRUE;
1050:

Line 1097: delete from ap_purge_invoice_list apl

1093:
1094:
1095: -- match_invoices_to_pos
1096: IF p_purge_status = 'INITIATING' THEN
1097: delete from ap_purge_invoice_list apl
1098: where exists
1099: (select null
1100: from ap_invoice_distributions ad
1101: where ad.invoice_id = apl.invoice_id

Line 1113: update ap_purge_invoice_list apl

1109: ad.po_distribution_id));
1110: ELSE
1111: --bug5052748
1112: -- re_match_invoices_to_pos
1113: update ap_purge_invoice_list apl
1114: set double_check_flag = 'N'
1115: where double_check_flag = 'Y'
1116: and exists (select /*+NO_UNNEST*/ null
1117: from ap_invoice_distributions ad,po_distributions pd

Line 1133: from ap_purge_invoice_list

1129:
1130: -- count invs
1131: select count(1)
1132: into invoice_count
1133: from ap_purge_invoice_list
1134: where double_check_flag = 'Y';
1135:
1136: IF (invoice_count = invs_before_po_match AND
1137: l_po_records_filtered_tmp <> 'T') THEN

Line 1159: FROM ap_purge_invoice_list

1155:
1156: -- Get invoice list count
1157: SELECT count(*)
1158: INTO start_list_count
1159: FROM ap_purge_invoice_list
1160: WHERE double_check_flag = DECODE(p_purge_status, 'INITIATING', 'Y',
1161: double_check_flag);
1162:
1163: IF p_purge_status = 'INITIATING' THEN

Line 1166: FROM ap_purge_invoice_list PL

1162:
1163: IF p_purge_status = 'INITIATING' THEN
1164: -- Test Check Relationships
1165: DELETE
1166: FROM ap_purge_invoice_list PL
1167: WHERE EXISTS (
1168: SELECT 'relational problem'
1169: FROM ap_invoice_payments IP1,
1170: ap_invoice_payments IP2

Line 1175: FROM ap_purge_invoice_list PL2

1171: WHERE PL.invoice_id = IP1.invoice_id
1172: AND IP1.check_id = IP2.check_id
1173: AND IP2.invoice_id NOT IN (
1174: SELECT PL2.invoice_id
1175: FROM ap_purge_invoice_list PL2
1176: WHERE PL2.invoice_id =
1177: IP2.invoice_id)
1178: );
1179:

Line 1183: UPDATE ap_purge_invoice_list PL

1179:
1180: ELSE
1181: --bug5052748
1182: -- retest_check_relationships
1183: UPDATE ap_purge_invoice_list PL
1184: SET PL.double_check_flag = 'N'
1185: WHERE PL.double_check_flag = 'Y'
1186: AND EXISTS (
1187: SELECT /*+NO_UNNEST*/'relational problem'

Line 1193: FROM ap_purge_invoice_list PL2

1189: WHERE PL.invoice_id = IP1.invoice_id
1190: AND IP1.check_id = IP2.check_id
1191: AND IP2.invoice_id NOT IN (
1192: SELECT PL2.invoice_id
1193: FROM ap_purge_invoice_list PL2
1194: WHERE PL2.invoice_id = IP2.invoice_id
1195: AND PL2.double_check_flag ='Y'));
1196:
1197: END IF;

Line 1202: FROM ap_purge_invoice_list

1198:
1199: -- get invoice list count
1200: SELECT count(*)
1201: INTO list_count
1202: FROM ap_purge_invoice_list
1203: WHERE double_check_flag = DECODE(p_purge_status, 'INITIATING', 'Y',
1204: double_check_flag);
1205:
1206: if start_list_count = list_count then

Line 1404: Print('(Inserting into ap_purge_invoice_list)' ||debug_info);

1400: 'Seed_invoices<-'||P_calling_sequence;
1401: --
1402: debug_info := 'Starting Seed_invoices';
1403: IF g_debug_switch in ('y','Y') THEN
1404: Print('(Inserting into ap_purge_invoice_list)' ||debug_info);
1405: END IF;
1406:
1407: INSERT INTO ap_purge_invoice_list
1408: (invoice_id, purge_name, double_check_flag)

Line 1407: INSERT INTO ap_purge_invoice_list

1403: IF g_debug_switch in ('y','Y') THEN
1404: Print('(Inserting into ap_purge_invoice_list)' ||debug_info);
1405: END IF;
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

Line 1433: select count(*) into temp from ap_purge_invoice_list;

1429: AND I.invoice_type_lookup_code <> 'PREPAYMENT'
1430: GROUP BY I.invoice_id
1431: HAVING SUM(NVL(D.amount, 0)) = 0;
1432:
1433: select count(*) into temp from ap_purge_invoice_list;
1434:
1435: Print(to_char(temp)||' records in ap_purge_invoice list table');
1436:
1437: debug_info := 'End Seed_invoices';

Line 1435: Print(to_char(temp)||' records in ap_purge_invoice list table');

1431: HAVING SUM(NVL(D.amount, 0)) = 0;
1432:
1433: select count(*) into temp from ap_purge_invoice_list;
1434:
1435: Print(to_char(temp)||' records in ap_purge_invoice list table');
1436:
1437: debug_info := 'End Seed_invoices';
1438: IF g_debug_switch in ('y','Y') THEN
1439: Print('(Done inserting into ap_purge_invoice_list)' ||debug_info);

Line 1439: Print('(Done inserting into ap_purge_invoice_list)' ||debug_info);

1435: Print(to_char(temp)||' records in ap_purge_invoice list table');
1436:
1437: debug_info := 'End Seed_invoices';
1438: IF g_debug_switch in ('y','Y') THEN
1439: Print('(Done inserting into ap_purge_invoice_list)' ||debug_info);
1440: END IF;
1441:
1442: RETURN(TRUE);
1443: RETURN NULL;

Line 2142: FROM ap_purge_invoice_list PL

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
2143: WHERE PL.double_check_flag = 'Y'
2144: AND PL.invoice_id BETWEEN range_low AND
2145: range_high);
2146:

Line 2167: ap_purge_invoice_list PL

2163: p_purge_name, ac.doc_sequence_id, ac.doc_sequence_value, ac.payment_id,
2164: ac.org_id
2165: FROM ap_checks_all AC,
2166: ap_invoice_payments_all IP,
2167: ap_purge_invoice_list PL
2168: WHERE PL.invoice_id = IP.invoice_id
2169: AND IP.check_id = AC.check_id
2170: AND PL.double_check_flag = 'Y'
2171: AND PL.invoice_id BETWEEN range_low AND range_high

Line 2193: FROM ap_invoice_payments_all IP, ap_purge_invoice_list PL

2189: INSERT INTO ap_history_inv_payments_all
2190: (invoice_id, check_id, amount,org_id)
2191: SELECT
2192: IP.invoice_id, IP.check_id, SUM(IP.amount),IP.org_id
2193: FROM ap_invoice_payments_all IP, ap_purge_invoice_list PL
2194: WHERE IP.invoice_id = PL.invoice_id
2195: AND PL.double_check_flag = 'Y'
2196: AND PL.invoice_id BETWEEN range_low AND range_high
2197: GROUP BY IP.invoice_id, IP.check_id,

Line 2518: from ap_purge_invoice_list

2514: -- get_ap_range
2515: select nvl(min(invoice_id),-1),
2516: nvl(max(invoice_id),-1)
2517: into p_inv_lower_limit, p_inv_upper_limit
2518: from ap_purge_invoice_list
2519: where double_check_flag = 'Y';
2520:
2521: -- get_po_range
2522: select nvl(min(po_header_id),-1),

Line 2730: UPDATE ap_purge_invoice_list PL

2726:
2727: --
2728:
2729: -- Retest invoices
2730: UPDATE ap_purge_invoice_list PL
2731: SET PL.double_check_flag = 'N'
2732: WHERE PL.double_check_flag = 'Y'
2733: AND EXISTS(
2734: SELECT 'invoice no longer purgeable'

Line 2751: UPDATE ap_purge_invoice_list PL

2747: Print('(Retest_Invoice_Independens) '||debug_info);
2748:
2749:
2750: -- Retest PA Invoices
2751: UPDATE ap_purge_invoice_list PL
2752: SET PL.double_check_flag = 'N'
2753: WHERE PL.double_check_flag = 'Y'
2754: AND (EXISTS
2755: (SELECT 'project-related vendor invoices'

Line 2778: UPDATE ap_purge_invoice_list PL

2774: --
2775:
2776: -- Retest Payment Schedules
2777: --bug5052748
2778: UPDATE ap_purge_invoice_list PL
2779: SET PL.double_check_flag = 'N'
2780: WHERE PL.double_check_flag = 'Y'
2781: AND EXISTS (
2782: SELECT /*+NO_UNNEST*/ 'payment schedule no longer purgeable'

Line 2810: UPDATE ap_purge_invoice_list PL

2806: */
2807: if g_category = 'SIMPLE INVOICES' then
2808: --bug5052748
2809: -- Retest simple Invoice Distributions
2810: UPDATE ap_purge_invoice_list PL
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'

Line 2829: UPDATE ap_purge_invoice_list PL

2825: AND I.cancelled_date IS NULL)));
2826: else
2827: --bug5052748
2828: -- Retest all Invoice Distributions
2829: UPDATE ap_purge_invoice_list PL
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'

Line 2855: UPDATE ap_purge_invoice_list PL

2851:
2852: --
2853: --bug5052748
2854: -- Retest Payments
2855: UPDATE ap_purge_invoice_list PL
2856: SET PL.double_check_flag = 'N'
2857: WHERE PL.double_check_flag = 'Y'
2858: AND EXISTS (
2859: SELECT /*+NO_UNNEST*/'payment no longer purgeable'

Line 2878: UPDATE ap_purge_invoice_list PL

2874: END IF;
2875:
2876: --
2877:
2878: UPDATE ap_purge_invoice_list PL
2879: SET PL.double_check_flag = 'N'
2880: WHERE PL.double_check_flag = 'Y'
2881: AND EXISTS (
2882: SELECT 'recently related to prepayment'

Line 2894: UPDATE ap_purge_invoice_list PL

2890: Print('(Retest Invoice Independents)'||debug_info);
2891: END IF;
2892: --
2893:
2894: UPDATE ap_purge_invoice_list PL
2895: SET PL.double_check_flag = 'N'
2896: WHERE EXISTS (
2897: SELECT 'matched'
2898: FROM ap_invoice_distributions aid

Line 2913: UPDATE ap_purge_invoice_list PL

2909: Print('(Retest Invoice Independents)'||debug_info);
2910: END IF;
2911: --
2912:
2913: UPDATE ap_purge_invoice_list PL
2914: SET double_check_flag = 'N'
2915: WHERE EXISTS (
2916: SELECT null
2917: FROM ap_invoice_distributions ad

Line 2926: FROM ap_purge_invoice_list

2922: FROM ap_invoice_distributions ad2
2923: where ad2.rcv_transaction_id = ad.rcv_transaction_id
2924: and ad2.invoice_id NOT IN (
2925: SELECT invoice_id
2926: FROM ap_purge_invoice_list
2927: WHERE double_check_flag = 'Y')));
2928:
2929:
2930: --

Line 2939: UPDATE ap_purge_invoice_list PL

2935: -- RETURN (TRUE);
2936: --
2937: -- Fix for bug 2652768 made changes to below UPDATE statement
2938: -- Fix for bug 2963666 added an check for MRC upgraded data
2939: UPDATE ap_purge_invoice_list PL
2940: SET PL.double_check_flag = 'N'
2941: WHERE EXISTS (
2942: SELECT 'invoice accounting not purgeable'
2943: FROM xla_events xe,

Line 3020: from ap_purge_invoice_list pl,

3016: tab_clear tab_status_type;
3017:
3018: CURSOR c_main IS
3019: select pl.invoice_id
3020: from ap_purge_invoice_list pl,
3021: ap_invoice_payments ip
3022: where pl.invoice_id = ip.invoice_id;
3023:
3024: CURSOR c_main_check(l_invoice_id NUMBER) IS

Line 3026: from ap_purge_invoice_list

3022: where pl.invoice_id = ip.invoice_id;
3023:
3024: CURSOR c_main_check(l_invoice_id NUMBER) IS
3025: select invoice_id
3026: from ap_purge_invoice_list
3027: where invoice_id = l_invoice_id
3028: and double_check_flag = 'Y';
3029:
3030: p_count integer;

Line 3049: ap_purge_invoice_list pil

3045:
3046: CURSOR c_inv IS
3047: select pil.invoice_id
3048: from ap_invoice_payments ip,
3049: ap_purge_invoice_list pil
3050: where ip.check_id = l_check_id
3051: and ip.invoice_id = pil.invoice_id (+)
3052: and pil.double_check_flag = 'Y';
3053:

Line 3055: l_inv_id ap_purge_invoice_list.invoice_id%TYPE;

3051: and ip.invoice_id = pil.invoice_id (+)
3052: and pil.double_check_flag = 'Y';
3053:
3054: l_flag BOOLEAN := FALSE;
3055: l_inv_id ap_purge_invoice_list.invoice_id%TYPE;
3056:
3057: BEGIN
3058:
3059: OPEN c_inv ;

Line 3165: UPDATE ap_purge_invoice_list

3161: p_id := 0 ;
3162:
3163: FOR y IN 1..p_count LOOP
3164: p_id := tab_inv.next(p_id) ;
3165: UPDATE ap_purge_invoice_list
3166: SET double_check_flag = 'S'
3167: WHERE invoice_id = p_id ;
3168: END LOOP;
3169:

Line 3180: UPDATE ap_purge_invoice_list

3176: p_id := 0 ;
3177:
3178: FOR y IN 1..p_count LOOP
3179: p_id := tab_inv.next(p_id) ;
3180: UPDATE ap_purge_invoice_list
3181: SET double_check_flag = 'N'
3182: WHERE invoice_id = p_id ;
3183: END LOOP;
3184: end if;

Line 3194: UPDATE ap_purge_invoice_list

3190: p_id := 0 ;
3191:
3192: FOR y IN 1..p_count LOOP
3193: p_id := tab_check.next(p_id) ;
3194: UPDATE ap_purge_invoice_list
3195: SET double_check_flag = 'N'
3196: WHERE invoice_id in ( select invoice_id
3197: from ap_invoice_payments
3198: where check_id = p_id);

Line 3209: update ap_purge_invoice_list

3205:
3206: END LOOP;
3207:
3208: /* Set flag 'Y' back */
3209: update ap_purge_invoice_list
3210: set double_check_flag = 'Y'
3211: where double_check_flag = 'S' ;
3212:
3213: debug_info := 'End of Invoice Validations';

Line 4061: UPDATE ap_purge_invoice_list PL

4057: END IF;
4058:
4059: --
4060:
4061: UPDATE ap_purge_invoice_list PL
4062: SET PL.double_check_flag = 'N'
4063: WHERE EXISTS (
4064: SELECT 'history not purgeable'
4065: FROM ap_invoice_payments aip

Line 4677: FROM ap_invoice_payments P, ap_purge_invoice_list PL,

4673: RETURN BOOLEAN IS
4674:
4675: CURSOR overflow_select is
4676: SELECT C.check_stock_id,C.check_number
4677: FROM ap_invoice_payments P, ap_purge_invoice_list PL,
4678: ap_checks C
4679: WHERE P.invoice_id = PL.invoice_id
4680: AND P.check_id = C.check_id
4681: AND PL.double_check_flag = 'Y'

Line 4932: FROM ap_purge_invoice_list

4928: l_count number := 0;
4929:
4930: CURSOR range (low_inv_id IN NUMBER) IS
4931: SELECT invoice_id
4932: FROM ap_purge_invoice_list
4933: WHERE double_check_flag = 'Y'
4934: and invoice_id > low_inv_id
4935: ORDER BY invoice_id asc;
4936:

Line 4940: FROM ap_purge_invoice_list PL

4936:
4937: CURSOR ap_invoice_cur (low_inv_id IN NUMBER,
4938: high_inv_id IN NUMBER) IS
4939: SELECT PL.invoice_id
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;

Line 4971: from ap_purge_invoice_list

4967:
4968: select nvl(min(invoice_id),-1)
4969: , nvl(max(invoice_id),-1)
4970: into range_low, range_high
4971: from ap_purge_invoice_list
4972: where double_check_flag = 'Y';
4973:
4974: --Bug2382623 Changed the paramter to range_low
4975: OPEN range(range_low);

Line 5002: , ap_purge_invoice_list pil

4998: delete from ap_chrg_allocations aca
4999: where exists (
5000: select 'allocations'
5001: from ap_invoice_distributions aid
5002: , ap_purge_invoice_list pil
5003: where aca.item_dist_id = aid.invoice_distribution_id
5004: and pil.invoice_id = aid.invoice_id
5005: and pil.invoice_id BETWEEN range_low and range_high
5006: and pil.double_check_flag = 'Y');

Line 5014: FROM ap_purge_invoice_list PL,

5010: /* bug3068811 : Changed from EXISTS to IN for performance */
5011: DELETE FROM ap_doc_sequence_audit AUD
5012: WHERE (AUD.doc_sequence_id , AUD.doc_sequence_value)
5013: IN (SELECT C.doc_sequence_id , C.doc_sequence_value
5014: FROM ap_purge_invoice_list PL,
5015: ap_checks C,
5016: ap_invoice_payments IP
5017: WHERE PL.double_check_flag = 'Y'
5018: AND PL.invoice_id BETWEEN range_low AND range_high

Line 5048: FROM ap_invoice_payments P, ap_purge_invoice_list PL

5044: -- bug 5052764 - go to base table ap_checks_all to remove FTS
5045: DELETE FROM ap_checks_all C
5046: WHERE C.check_id IN (
5047: SELECT P.check_id
5048: FROM ap_invoice_payments P, ap_purge_invoice_list PL
5049: WHERE P.invoice_id = PL.invoice_id
5050: AND PL.double_check_flag = 'Y'
5051: AND PL.invoice_id BETWEEN range_low AND range_high);
5052:

Line 5076: , ap_purge_invoice_list PL

5072: DELETE FROM ap_payment_history aph
5073: WHERE EXISTS (
5074: SELECT 'history purgeable'
5075: FROM ap_invoice_payments aip
5076: , ap_purge_invoice_list PL
5077: WHERE aip.invoice_id = PL.invoice_id
5078: and aip.check_id = aph.check_id
5079: and PL.double_check_flag = 'Y');
5080:

Line 5092: FROM ap_purge_invoice_list PL

5088:
5089: DELETE FROM ap_invoice_payments
5090: WHERE invoice_id IN (
5091: SELECT PL.invoice_id
5092: FROM ap_purge_invoice_list PL
5093: WHERE PL.double_check_flag = 'Y'
5094: AND PL.invoice_id BETWEEN range_low AND range_high);
5095:
5096:

Line 5108: FROM ap_purge_invoice_list PL

5104:
5105: DELETE FROM ap_payment_schedules
5106: WHERE invoice_id IN (
5107: SELECT PL.invoice_id
5108: FROM ap_purge_invoice_list PL
5109: WHERE PL.double_check_flag = 'Y'
5110: AND PL.invoice_id BETWEEN range_low AND range_high);
5111:
5112:

Line 5124: FROM ap_purge_invoice_list PL

5120:
5121: DELETE FROM ap_trial_balance
5122: WHERE invoice_id IN (
5123: SELECT PL.invoice_id
5124: FROM ap_purge_invoice_list PL
5125: WHERE PL.double_check_flag = 'Y'
5126: AND PL.invoice_id BETWEEN range_low AND range_high);
5127:
5128: debug_info := 'ap_holds';

Line 5139: FROM ap_purge_invoice_list PL

5135:
5136: DELETE FROM ap_holds
5137: WHERE invoice_id IN (
5138: SELECT PL.invoice_id
5139: FROM ap_purge_invoice_list PL
5140: WHERE PL.double_check_flag = 'Y'
5141: AND PL.invoice_id BETWEEN range_low AND range_high);
5142:
5143: debug_info := 'ap_inv_aprvl_hist';

Line 5154: FROM ap_purge_invoice_list PL

5150:
5151: DELETE FROM ap_inv_aprvl_hist
5152: WHERE invoice_id IN (
5153: SELECT PL.invoice_id
5154: FROM ap_purge_invoice_list PL
5155: WHERE PL.double_check_flag = 'Y'
5156: AND PL.invoice_id BETWEEN range_low AND range_high);
5157:
5158:

Line 5168: FROM ap_purge_invoice_list PL

5164:
5165: DELETE FROM ap_invoice_distributions
5166: WHERE invoice_id IN (
5167: SELECT PL.invoice_id
5168: FROM ap_purge_invoice_list PL
5169: WHERE PL.double_check_flag = 'Y'
5170: AND PL.invoice_id BETWEEN range_low AND range_high);
5171:
5172: debug_info := 'ap_doc_sequence_audit, Invoices';

Line 5183: FROM ap_purge_invoice_list PL,

5179: /* bug3284915 : Changed from EXISTS to IN for performance */
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);

Line 5219: FROM ap_purge_invoice_list PL

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'
5221: AND PL.invoice_id BETWEEN range_low AND range_high);
5222:
5223:

Line 5305: FROM ap_purge_invoice_list

5301: l_count number := 0;
5302:
5303: CURSOR range (low_inv_id IN NUMBER) IS
5304: SELECT invoice_id
5305: FROM ap_purge_invoice_list
5306: WHERE double_check_flag = 'Y'
5307: and invoice_id > low_inv_id
5308: ORDER BY invoice_id asc;
5309:

Line 5320: FROM ap_purge_invoice_list

5316:
5317: SELECT nvl(min(invoice_id),-1)
5318: , nvl(max(invoice_id),-1)
5319: into range_low, range_high
5320: FROM ap_purge_invoice_list
5321: WHERE double_check_flag = 'Y';
5322:
5323: OPEN range(range_low); --Bug2711759
5324: WHILE l_count < g_range_size

Line 5358: FROM ap_purge_invoice_list pil

5354:
5355: DELETE FROM ap_liability_balance alb
5356: WHERE EXISTS (
5357: SELECT 'records exist'
5358: FROM ap_purge_invoice_list pil
5359: WHERE alb.invoice_id = pil.invoice_id
5360: AND pil.double_check_flag = 'Y'
5361: AND pil.invoice_id BETWEEN range_low
5362: AND range_high)

Line 5374: ,ap_purge_invoice_list pil

5370: WHERE ael.ae_header_id in
5371: ( SELECT aeh.ae_header_id
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'

Line 5387: ,ap_purge_invoice_list pil

5383: ( SELECT aeh.ae_header_id
5384: FROM ap_ae_headers aeh -- bug 2153117 added
5385: ,ap_accounting_events aae
5386: ,ap_invoice_payments aip
5387: ,ap_purge_invoice_list pil
5388: WHERE aae.source_id = aip.check_id
5389: and aae.source_table = 'AP_CHECKS'
5390: and pil.double_check_flag = 'Y'
5391: and aae.accounting_event_id = aeh.accounting_event_id

Line 5405: , ap_purge_invoice_list pil

5401: DELETE FROM ap_ae_headers aeh
5402: WHERE aeh.accounting_event_id IN
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

Line 5420: , ap_purge_invoice_list pil

5416: WHERE aeh.accounting_event_id in
5417: ( SELECT aae.accounting_event_id
5418: FROM ap_accounting_events aae
5419: , ap_invoice_payments aip
5420: , ap_purge_invoice_list pil
5421: -- bug2153117 removed
5422: -- , ap_ae_headers aeh
5423: WHERE aae.source_id = aip.check_id
5424: and aae.source_table = 'AP_CHECKS'

Line 5441: FROM ap_purge_invoice_list pil

5437:
5438: DELETE FROM ap_encumbrance_lines aen
5439: WHERE EXISTS (
5440: SELECT 'dist'
5441: FROM ap_purge_invoice_list pil
5442: , ap_invoice_distributions aid
5443: WHERE aen.invoice_distribution_id = aid.invoice_distribution_id
5444: and aid.invoice_id = pil.invoice_id
5445: and pil.double_check_flag = 'Y'

Line 5456: FROM AP_PURGE_INVOICE_LIST PIL

5452: -- below 3 delete statement
5453:
5454: DELETE FROM AP_ACCOUNTING_EVENTS AAE
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: ;

Line 5464: FROM AP_PURGE_INVOICE_LIST PIL,

5460: ;
5461:
5462: DELETE FROM AP_ACCOUNTING_EVENTS AAE WHERE
5463: aae.source_id in ( SELECT APC.CHECK_ID
5464: FROM AP_PURGE_INVOICE_LIST PIL,
5465: AP_CHECKS APC,
5466: AP_INVOICE_PAYMENTS AIP
5467: WHERE PIL.DOUBLE_CHECK_FLAG = 'Y'
5468: AND APC.CHECK_ID = AIP.CHECK_ID

Line 5477: FROM AP_PURGE_INVOICE_LIST PIL,

5473:
5474:
5475: DELETE FROM AP_ACCOUNTING_EVENTS AAE WHERE
5476: AAE.source_id IN ( SELECT APH.CHECK_ID
5477: FROM AP_PURGE_INVOICE_LIST PIL,
5478: AP_INVOICE_PAYMENTS AIP,
5479: AP_PAYMENT_HISTORY APH
5480: WHERE PIL.DOUBLE_CHECK_FLAG = 'Y'
5481: AND APH.CHECK_ID = AIP.CHECK_ID

Line 6549: delete from ap_purge_invoice_list;

6545: ae_header_rows := 0;
6546: accounting_event_rows:= 0;
6547:
6548: -- clear_invoice_purge_list
6549: delete from ap_purge_invoice_list;
6550:
6551: PO_AP_PURGE_GRP.delete_purge_lists
6552: ( p_api_version => 1.0,
6553: p_init_msg_list => 'T',

Line 6724: delete from ap_purge_invoice_list;

6720: FUNCTION clear_invoice_purge_list RETURN BOOLEAN IS
6721:
6722: BEGIN
6723:
6724: delete from ap_purge_invoice_list;
6725:
6726: RETURN (TRUE);
6727:
6728: RETURN NULL; EXCEPTION