[Home] [Help]
210:
211: -- count_invs
212: select count(1)
213: into invoice_count
214: from ap_purge_invoice_list
215: where double_check_flag = 'Y';
216:
217: if (invoice_count = 0) then
218:
299: end if; -- po_count
300:
301: else -- invoice_count
302:
303: debug_info := 'THe AP_PURGE_INVOICE_LIST table contains records. ';
304: Print('Check_no_purge_in_process' || debug_info);
305: Print('Please make sure no purges are running and clear');
306: Print(' this table. Process terminating.');
307:
456: invoices which are related to payment.
457: */
458: CURSOR c_main IS
459: select pl.invoice_id
460: from ap_purge_invoice_list pl,
461: ap_invoice_payments ip
462: where pl.invoice_id = ip.invoice_id;
463:
464: CURSOR c_main_check(l_invoice_id NUMBER) IS
462: where pl.invoice_id = ip.invoice_id;
463:
464: CURSOR c_main_check(l_invoice_id NUMBER) IS
465: select invoice_id
466: from ap_purge_invoice_list
467: where invoice_id = l_invoice_id
468: and double_check_flag = 'Y';
469:
470: p_count integer;
486:
487: CURSOR c_inv IS
488: select pil.invoice_id
489: from ap_invoice_payments ip,
490: ap_purge_invoice_list pil
491: where ip.check_id = l_check_id
492: and ip.invoice_id = pil.invoice_id (+) ;
493:
494: l_flag BOOLEAN := FALSE;
491: where ip.check_id = l_check_id
492: and ip.invoice_id = pil.invoice_id (+) ;
493:
494: l_flag BOOLEAN := FALSE;
495: l_inv_id ap_purge_invoice_list.invoice_id%TYPE;
496:
497: BEGIN
498:
499: OPEN c_inv ;
661:
662: FOR y IN 1..p_count LOOP
663: IF use_vc2 THEN -- Bug 8942883
664: p_id_vc2 := tab_inv_vc2.next(p_id_vc2) ;
665: UPDATE ap_purge_invoice_list
666: SET double_check_flag = 'S'
667: WHERE invoice_id = p_id_vc2 ;
668: ELSE
669: p_id := tab_inv.next(p_id) ;
666: SET double_check_flag = 'S'
667: WHERE invoice_id = p_id_vc2 ;
668: ELSE
669: p_id := tab_inv.next(p_id) ;
670: UPDATE ap_purge_invoice_list
671: SET double_check_flag = 'S'
672: WHERE invoice_id = p_id ;
673: END IF ;
674: END LOOP;
688:
689: FOR y IN 1..p_count LOOP
690: IF use_vc2 THEN -- Bug 8942883
691: p_id_vc2 := tab_inv_vc2.next(p_id_vc2) ;
692: DELETE FROM ap_purge_invoice_list
693: WHERE invoice_id = p_id_vc2 ;
694: ELSE
695: p_id := tab_inv.next(p_id) ;
696: DELETE FROM ap_purge_invoice_list
692: DELETE FROM ap_purge_invoice_list
693: WHERE invoice_id = p_id_vc2 ;
694: ELSE
695: p_id := tab_inv.next(p_id) ;
696: DELETE FROM ap_purge_invoice_list
697: WHERE invoice_id = p_id ;
698: END IF ;
699: END LOOP;
700: end if;
712:
713: FOR y IN 1..p_count LOOP
714: IF use_vc2 THEN -- Bug 8942883
715: p_id_vc2 := tab_check_vc2.next(p_id_vc2) ;
716: DELETE FROM ap_purge_invoice_list
717: WHERE invoice_id in ( select invoice_id
718: from ap_invoice_payments
719: where check_id = p_id_vc2);
720: ELSE
718: from ap_invoice_payments
719: where check_id = p_id_vc2);
720: ELSE
721: p_id := tab_check.next(p_id) ;
722: DELETE FROM ap_purge_invoice_list
723: WHERE invoice_id in ( select invoice_id
724: from ap_invoice_payments
725: where check_id = p_id);
726: END IF ;
733:
734: END LOOP;
735:
736: /* Set flag 'Y' back */
737: update ap_purge_invoice_list
738: set double_check_flag = 'Y'
739: where double_check_flag = 'S' ;
740:
741: debug_info := 'End Of Invoice Validations';
769:
770: /* bug 11722321 incorporating changes of bug 8842960 and 8979828 in R12 start */
771: CURSOR pa_related_invoices IS
772: SELECT invoice_id
773: FROM ap_purge_invoice_list PL
774: WHERE EXISTS
775: (SELECT 'project-related vendor invoices'
776: FROM ap_invoice_distributions d
777: WHERE d.invoice_id = pl.invoice_id
807: -- Test Payment Schedules
808: /*bug9944247-modified the sql to improve the performance*/
809:
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
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
819: WHERE i.invoice_id = pl1.invoice_id
820: AND ps.invoice_id = i.invoice_id
833:
834: /* bug 11722321 incorporating changes of bug 8842960 and 8979828 in R12 start */
835:
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
844: AND I.source = 'Oracle Project Accounting'
845: AND D.pa_addition_flag not in ('Y','T','E','Z') );
846:
847: /* DELETE
848: FROM ap_purge_invoice_list PL
849: WHERE EXISTS
850: (SELECT 'project-related vendor invoices'
851: FROM ap_invoice_distributions d
852: WHERE d.invoice_id = pl.invoice_id
871: --allow purge
872: null;
873: else
874: DELETE
875: FROM ap_purge_invoice_list
876: WHERE invoice_id = l_invoice_id;
877: end if;
878: END LOOP;
879: CLOSE pa_related_invoices;
907:
908: /*bug9944247-modified the sql to improve the performance*/
909:
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
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
919: WHERE i.invoice_id = pl1.invoice_id
920: AND i.invoice_id = d.invoice_id
932: ELSE
933: Print('Test All Invoice Distributions');
934: -- Test All Invoice Distributions
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
975: on ce_statement_reconciliations being applied as a filter.
976: It also allows more control using hints. */
977:
978: DELETE /*+ PARALLEL(pl) ROWID(pl) */
979: FROM ap_purge_invoice_list pl
980: WHERE pl.rowid IN
981: (SELECT /*+ ORDERED PARALLEL(pl1) FULL(pl1) USE_NL(p,c)
982: INDEX(p AP_INVOICE_PAYMENTS_N1) INDEX(c AP_CHECKS_U1) */
983: pl1.rowid
980: WHERE pl.rowid IN
981: (SELECT /*+ ORDERED PARALLEL(pl1) FULL(pl1) USE_NL(p,c)
982: INDEX(p AP_INVOICE_PAYMENTS_N1) INDEX(c AP_CHECKS_U1) */
983: pl1.rowid
984: FROM ap_purge_invoice_list pl1,
985: ap_invoice_payments p,
986: ap_checks c
987: WHERE p.invoice_id = pl1.invoice_id
988: AND p.check_id = c.check_id
996: nvl(c.void_date, to_date('12/31/2999' ,'MM/DD/YYYY')))) > g_activity_date))
997: );
998:
999: DELETE /*+ PARALLEL(pl) ROWID(pl) */
1000: FROM ap_purge_invoice_list pl
1001: WHERE pl.rowid IN
1002: (SELECT /*+ ORDERED PARALLEL(pl1) FULL(pl1) USE_NL(p, sr)
1003: INDEX(p AP_INVOICE_PAYMENTS_N1) INDEX(sr CE_STATEMENT_RECONS_N2) */
1004: DISTINCT pl1.rowid
1001: WHERE pl.rowid IN
1002: (SELECT /*+ ORDERED PARALLEL(pl1) FULL(pl1) USE_NL(p, sr)
1003: INDEX(p AP_INVOICE_PAYMENTS_N1) INDEX(sr CE_STATEMENT_RECONS_N2) */
1004: DISTINCT pl1.rowid
1005: FROM ap_purge_invoice_list pl1,
1006: ap_invoice_payments p,
1007: ce_statement_reconciliations sr
1008: WHERE p.invoice_id = pl1.invoice_id
1009: AND p.check_id = sr.reference_id
1020: -- Delete Inoivces that have applied Prepayments
1021: -- Keep this Statement for Invoices upgrated from 11.0
1022:
1023: DELETE
1024: FROM ap_purge_invoice_list PL
1025: WHERE EXISTS
1026: (SELECT /*+ no_unnest */ 'related to prepayment' -- 7759218
1027: FROM ap_invoice_prepays IP
1028: WHERE PL.invoice_id = IP.invoice_id
1031: -- Bug 2153132 by ISartawi add the Delete Statement to exclude
1032: -- invoices with applied Prepayments
1033:
1034: DELETE
1035: FROM ap_purge_invoice_list PL
1036: WHERE EXISTS
1037: (SELECT 'X'
1038: FROM ap_invoice_distributions ID
1039: WHERE PL.invoice_id = ID.invoice_id
1048: IF g_debug_switch in ('y','Y') THEN
1049: Print('(Do_independent_inv_checks)' ||debug_info);
1050: END IF;
1051:
1052: DELETE FROM ap_purge_invoice_list PL
1053: where EXISTS(
1054: select 'history not purgeable'
1055: from ap_invoice_payments aip
1056: , ap_payment_history aph
1070:
1071: -- Fix for bug 2652768 made changes to below DELETE statement
1072: -- Fix for bug 2963666 Added condition to check description is not MRC upgrade
1073: DELETE /*+ PARALLEL(pl) ROWID(pl) */
1074: FROM ap_purge_invoice_list PL
1075: WHERE pl.rowid IN (
1076: Select /*+ ORDERED PARALLEL(pl1) FULL(pl1) */
1077: pl1.rowid -- 7759218
1078: from ap_purge_invoice_list pl1,
1074: FROM ap_purge_invoice_list PL
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
1095: OR ( xeh.last_update_date > g_activity_date ))
1096: UNION
1097: Select /*+ ORDERED PARALLEL(pl1) FULL(pl1) */
1098: pl1.rowid -- 7759218
1099: from ap_purge_invoice_list pl1,
1100: xla_events xe, --Bug 4588031
1101: xla_transaction_entities xte, --Bug 4588031
1102: ap_invoice_payments aip,
1103: ap_system_parameters_all asp,--bug5052478
1122: IF g_debug_switch in ('y','Y') THEN
1123: Print('(Do_independent_inv_checks)' ||debug_info);
1124: END IF;
1125:
1126: DELETE FROM ap_purge_invoice_list PL
1127: WHERE EXISTS (
1128: select 'matched'
1129: from ap_invoice_distributions aid, rcv_transactions rcv
1130: where aid.invoice_id = PL.invoice_id
1130: where aid.invoice_id = PL.invoice_id
1131: and aid.rcv_transaction_id = rcv.transaction_id
1132: and rcv.last_update_date > g_activity_date);
1133:
1134: DELETE FROM ap_purge_invoice_list PL
1135: WHERE EXISTS
1136: (select null
1137: from ap_invoice_distributions ad
1138: where ad.invoice_id = PL.invoice_id
1141: select 'matching' from ap_invoice_distributions ad2
1142: where ad2.rcv_transaction_id = ad.rcv_transaction_id
1143: and ad2.invoice_id NOT IN (
1144: select invoice_id
1145: from ap_purge_invoice_list
1146: where double_check_flag = 'Y')));
1147:
1148: -- debug info....
1149: SELECT count(*) INTO l_list_count FROM ap_purge_invoice_list;
1145: from ap_purge_invoice_list
1146: where double_check_flag = 'Y')));
1147:
1148: -- debug info....
1149: SELECT count(*) INTO l_list_count FROM ap_purge_invoice_list;
1150: Print(to_char(l_list_count)||' records in ap_purge_invoice_list table');
1151:
1152:
1153: RETURN (TRUE);
1146: where double_check_flag = 'Y')));
1147:
1148: -- debug info....
1149: SELECT count(*) INTO l_list_count FROM ap_purge_invoice_list;
1150: Print(to_char(l_list_count)||' records in ap_purge_invoice_list table');
1151:
1152:
1153: RETURN (TRUE);
1154: RETURN NULL;
1206:
1207: -- count_invs
1208: select count(1)
1209: into invoice_count
1210: from ap_purge_invoice_list
1211: where double_check_flag = 'Y';
1212:
1213: l_first_iteration := TRUE;
1214:
1257:
1258:
1259: -- match_invoices_to_pos
1260: IF p_purge_status = 'INITIATING' THEN
1261: delete from ap_purge_invoice_list apl
1262: where exists
1263: (select /*+ no_unnest */ null -- 7759218
1264: from ap_invoice_distributions ad
1265: where ad.invoice_id = apl.invoice_id
1273: ad.po_distribution_id));
1274: ELSE
1275: --bug5052748
1276: -- re_match_invoices_to_pos
1277: update ap_purge_invoice_list apl
1278: set double_check_flag = 'N'
1279: where double_check_flag = 'Y'
1280: and exists (select /*+NO_UNNEST*/ null
1281: from ap_invoice_distributions ad,po_distributions pd
1293:
1294: -- count invs
1295: select count(1)
1296: into invoice_count
1297: from ap_purge_invoice_list
1298: where double_check_flag = 'Y';
1299:
1300: IF (invoice_count = invs_before_po_match AND
1301: l_po_records_filtered_tmp <> 'T') THEN
1319:
1320: -- Get invoice list count
1321: SELECT count(*)
1322: INTO start_list_count
1323: FROM ap_purge_invoice_list
1324: WHERE double_check_flag = DECODE(p_purge_status, 'INITIATING', 'Y',
1325: double_check_flag);
1326:
1327: IF p_purge_status = 'INITIATING' THEN
1326:
1327: IF p_purge_status = 'INITIATING' THEN
1328: -- Test Check Relationships
1329: DELETE
1330: FROM ap_purge_invoice_list PL
1331: WHERE EXISTS (
1332: SELECT 'relational problem'
1333: FROM ap_invoice_payments IP1,
1334: ap_invoice_payments IP2
1335: WHERE PL.invoice_id = IP1.invoice_id
1336: AND IP1.check_id = IP2.check_id
1337: AND IP2.invoice_id NOT IN (
1338: SELECT PL2.invoice_id
1339: FROM ap_purge_invoice_list PL2
1340: WHERE PL2.invoice_id =
1341: IP2.invoice_id)
1342: );
1343:
1343:
1344: ELSE
1345: --bug5052748
1346: -- retest_check_relationships
1347: UPDATE ap_purge_invoice_list PL
1348: SET PL.double_check_flag = 'N'
1349: WHERE PL.double_check_flag = 'Y'
1350: AND EXISTS (
1351: SELECT /*+NO_UNNEST*/'relational problem'
1353: WHERE PL.invoice_id = IP1.invoice_id
1354: AND IP1.check_id = IP2.check_id
1355: AND IP2.invoice_id NOT IN (
1356: SELECT PL2.invoice_id
1357: FROM ap_purge_invoice_list PL2
1358: WHERE PL2.invoice_id = IP2.invoice_id
1359: AND PL2.double_check_flag ='Y'));
1360:
1361: END IF;
1362:
1363: -- get invoice list count
1364: SELECT count(*)
1365: INTO list_count
1366: FROM ap_purge_invoice_list
1367: WHERE double_check_flag = DECODE(p_purge_status, 'INITIATING', 'Y',
1368: double_check_flag);
1369:
1370: if start_list_count = list_count then
1565: 'Seed_invoices<-'||P_calling_sequence;
1566: --
1567: debug_info := 'Starting Seed_invoices';
1568: IF g_debug_switch in ('y','Y') THEN
1569: Print('(Inserting into ap_purge_invoice_list)' ||debug_info);
1570: Print('P_Purge_Name:'||p_purge_name);
1571: Print('P_Using_Accrual_Basis:'||p_using_accrual_basis);
1572: Print('P_Using_Cash_Basis:'||p_using_cash_basis);
1573: Print('g_activity_date:'||g_activity_date);
1594: EXECUTE IMMEDIATE 'ALTER SESSION FORCE PARALLEL QUERY';
1595:
1596: -- modified the below query for bug13799066
1597: /* Bug 10391241 added join for ap_invoice_lines_all */
1598: INSERT INTO ap_purge_invoice_list pl
1599: (
1600: invoice_id,
1601: purge_name,
1602: double_check_flag
1670: EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL QUERY';
1671:
1672: /*bug9944247-end*/
1673:
1674: -- Bug 9268290 select count(*) into temp from ap_purge_invoice_list;
1675: temp := SQL%ROWCOUNT ;
1676:
1677: Print(to_char(temp)||' records in ap_purge_invoice list table');
1678:
1673:
1674: -- Bug 9268290 select count(*) into temp from ap_purge_invoice_list;
1675: temp := SQL%ROWCOUNT ;
1676:
1677: Print(to_char(temp)||' records in ap_purge_invoice list table');
1678:
1679: debug_info := 'End Seed_invoices';
1680: IF g_debug_switch in ('y','Y') THEN
1681: Print('(Done inserting into ap_purge_invoice_list)' ||debug_info);
1677: Print(to_char(temp)||' records in ap_purge_invoice list table');
1678:
1679: debug_info := 'End Seed_invoices';
1680: IF g_debug_switch in ('y','Y') THEN
1681: Print('(Done inserting into ap_purge_invoice_list)' ||debug_info);
1682: END IF;
1683:
1684: RETURN(TRUE);
1685: RETURN NULL;
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
2398: WHERE PL.double_check_flag = 'Y'
2399: AND PL.invoice_id BETWEEN range_low AND
2400: range_high);
2401:
2418: p_purge_name, ac.doc_sequence_id, ac.doc_sequence_value, ac.payment_id,
2419: ac.org_id
2420: FROM ap_checks_all AC,
2421: ap_invoice_payments_all IP,
2422: ap_purge_invoice_list PL
2423: WHERE PL.invoice_id = IP.invoice_id
2424: AND IP.check_id = AC.check_id
2425: AND PL.double_check_flag = 'Y'
2426: AND PL.invoice_id BETWEEN range_low AND range_high
2444: INSERT INTO ap_history_inv_payments_all
2445: (invoice_id, check_id, amount,org_id)
2446: SELECT
2447: IP.invoice_id, IP.check_id, SUM(IP.amount),IP.org_id
2448: FROM ap_invoice_payments_all IP, ap_purge_invoice_list PL
2449: WHERE IP.invoice_id = PL.invoice_id
2450: AND PL.double_check_flag = 'Y'
2451: AND PL.invoice_id BETWEEN range_low AND range_high
2452: GROUP BY IP.invoice_id, IP.check_id,
2769: -- get_ap_range
2770: select nvl(min(invoice_id),-1),
2771: nvl(max(invoice_id),-1)
2772: into p_inv_lower_limit, p_inv_upper_limit
2773: from ap_purge_invoice_list
2774: where double_check_flag = 'Y';
2775:
2776: -- get_po_range
2777: select nvl(min(po_header_id),-1),
2970:
2971: /* bug 11722321 incorporating changes of bug8842960 in R12 start */
2972: CURSOR pa_related_invoices IS
2973: SELECT invoice_id
2974: FROM ap_purge_invoice_list PL
2975: WHERE EXISTS
2976: (SELECT 'project-related vendor invoices'
2977: FROM ap_invoice_distributions d
2978: WHERE d.invoice_id = pl.invoice_id
3000:
3001: --
3002:
3003: -- Retest invoices
3004: UPDATE ap_purge_invoice_list PL
3005: SET PL.double_check_flag = 'N'
3006: WHERE PL.double_check_flag = 'Y'
3007: AND EXISTS(
3008: SELECT 'invoice no longer purgeable'
3021: Print('(Retest Invoice Independents)'||debug_info);
3022: END IF;
3023:
3024: -- Retest invoice lines
3025: UPDATE ap_purge_invoice_list PL
3026: SET PL.double_check_flag = 'N'
3027: WHERE PL.double_check_flag = 'Y'
3028: AND EXISTS(
3029: SELECT 'lines no longer purgeable'
3040:
3041:
3042: -- Retest PA Invoices
3043: /* bug 11722321 incorporating changes of bug 8842960 and 8979828 in R12 start */
3044: UPDATE ap_purge_invoice_list PL
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'
3052: AND I.invoice_id = D.invoice_id
3053: AND I.source <> 'Oracle Project Accounting'
3054: AND D.pa_addition_flag not in ('Y','T','E','Z') );
3055:
3056: /* UPDATE ap_purge_invoice_list PL
3057: SET PL.double_check_flag = 'N'
3058: WHERE PL.double_check_flag = 'Y'
3059: AND (EXISTS
3060: (SELECT 'project-related vendor invoices'
3085: -- allow purge
3086: null ;
3087:
3088: else
3089: UPDATE ap_purge_invoice_list PL
3090: SET PL.double_check_flag = 'N'
3091: WHERE PL.double_check_flag = 'Y'
3092: AND invoice_id = l_invoice_id;
3093:
3109: --
3110:
3111: -- Retest Payment Schedules
3112: --bug5052748
3113: UPDATE ap_purge_invoice_list PL
3114: SET PL.double_check_flag = 'N'
3115: WHERE PL.double_check_flag = 'Y'
3116: AND EXISTS (
3117: SELECT /*+NO_UNNEST*/ 'payment schedule no longer purgeable'
3141: */
3142: if g_category = 'SIMPLE INVOICES' then
3143: --bug5052748
3144: -- Retest simple Invoice Distributions
3145: UPDATE ap_purge_invoice_list PL
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'
3161: AND I.cancelled_date IS NULL)));
3162: else
3163: --bug5052748
3164: -- Retest all Invoice Distributions
3165: UPDATE ap_purge_invoice_list PL
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'
3188:
3189: --
3190: --bug5052748
3191: -- Retest Payments
3192: UPDATE ap_purge_invoice_list PL
3193: SET PL.double_check_flag = 'N'
3194: WHERE PL.double_check_flag = 'Y'
3195: AND EXISTS (
3196: SELECT /*+NO_UNNEST*/'payment no longer purgeable'
3211: END IF;
3212:
3213: --
3214:
3215: UPDATE ap_purge_invoice_list PL
3216: SET PL.double_check_flag = 'N'
3217: WHERE PL.double_check_flag = 'Y'
3218: AND EXISTS (
3219: SELECT 'recently related to prepayment'
3227: Print('(Retest Invoice Independents)'||debug_info);
3228: END IF;
3229: --
3230:
3231: UPDATE ap_purge_invoice_list PL
3232: SET PL.double_check_flag = 'N'
3233: WHERE EXISTS (
3234: SELECT 'matched'
3235: FROM ap_invoice_distributions aid
3246: Print('(Retest Invoice Independents)'||debug_info);
3247: END IF;
3248: --
3249:
3250: UPDATE ap_purge_invoice_list PL
3251: SET double_check_flag = 'N'
3252: WHERE EXISTS (
3253: SELECT null
3254: FROM ap_invoice_distributions ad
3259: FROM ap_invoice_distributions ad2
3260: where ad2.rcv_transaction_id = ad.rcv_transaction_id
3261: and ad2.invoice_id NOT IN (
3262: SELECT invoice_id
3263: FROM ap_purge_invoice_list
3264: WHERE double_check_flag = 'Y')));
3265:
3266:
3267: --
3272: -- RETURN (TRUE);
3273: --
3274: -- Fix for bug 2652768 made changes to below UPDATE statement
3275: -- Fix for bug 2963666 added an check for MRC upgraded data
3276: UPDATE ap_purge_invoice_list PL
3277: SET PL.double_check_flag = 'N'
3278: WHERE EXISTS (
3279: SELECT 'invoice accounting not purgeable'
3280: FROM xla_events xe,
3385: -- Bug 8942883 End
3386:
3387: CURSOR c_main IS
3388: select pl.invoice_id
3389: from ap_purge_invoice_list pl,
3390: ap_invoice_payments ip
3391: where pl.invoice_id = ip.invoice_id;
3392:
3393: CURSOR c_main_check(l_invoice_id NUMBER) IS
3391: where pl.invoice_id = ip.invoice_id;
3392:
3393: CURSOR c_main_check(l_invoice_id NUMBER) IS
3394: select invoice_id
3395: from ap_purge_invoice_list
3396: where invoice_id = l_invoice_id
3397: and double_check_flag = 'Y';
3398:
3399: p_count integer;
3415:
3416: CURSOR c_inv IS
3417: select pil.invoice_id
3418: from ap_invoice_payments ip,
3419: ap_purge_invoice_list pil
3420: where ip.check_id = l_check_id
3421: and ip.invoice_id = pil.invoice_id (+)
3422: and pil.double_check_flag = 'Y';
3423:
3421: and ip.invoice_id = pil.invoice_id (+)
3422: and pil.double_check_flag = 'Y';
3423:
3424: l_flag BOOLEAN := FALSE;
3425: l_inv_id ap_purge_invoice_list.invoice_id%TYPE;
3426:
3427: BEGIN
3428:
3429: OPEN c_inv ;
3553: (SELECT DISTINCT check_id
3554: FROM ap_invoice_payments_all
3555: WHERE invoice_id IN
3556: (SELECT invoice_id
3557: FROM ap_purge_invoice_list
3558: WHERE double_check_flag = 'Y') )
3559: AND NOT EXISTS
3560: (SELECT 'CHECK'
3561: FROM ap_purge_invoice_list pil
3557: FROM ap_purge_invoice_list
3558: WHERE double_check_flag = 'Y') )
3559: AND NOT EXISTS
3560: (SELECT 'CHECK'
3561: FROM ap_purge_invoice_list pil
3562: WHERE pil.invoice_id = aip.invoice_id)
3563: AND ROWNUM =1;
3564:
3565: IF (l_dummy = 0) Then
3634:
3635: FOR y IN 1..p_count LOOP
3636: IF use_vc2 THEN -- Bug 8942883
3637: p_id_vc2 := tab_inv_vc2.next(p_id_vc2) ;
3638: UPDATE ap_purge_invoice_list
3639: SET double_check_flag = 'S'
3640: WHERE invoice_id = p_id_vc2 ;
3641: ELSE
3642: p_id := tab_inv.next(p_id) ;
3639: SET double_check_flag = 'S'
3640: WHERE invoice_id = p_id_vc2 ;
3641: ELSE
3642: p_id := tab_inv.next(p_id) ;
3643: UPDATE ap_purge_invoice_list
3644: SET double_check_flag = 'S'
3645: WHERE invoice_id = p_id ;
3646: END IF ;
3647: END LOOP;
3661:
3662: FOR y IN 1..p_count LOOP
3663: IF use_vc2 THEN -- Bug 8942883
3664: p_id_vc2 := tab_inv_vc2.next(p_id_vc2) ;
3665: UPDATE ap_purge_invoice_list
3666: SET double_check_flag = 'N'
3667: WHERE invoice_id = p_id_vc2 ;
3668: ELSE
3669: p_id := tab_inv.next(p_id) ;
3666: SET double_check_flag = 'N'
3667: WHERE invoice_id = p_id_vc2 ;
3668: ELSE
3669: p_id := tab_inv.next(p_id) ;
3670: UPDATE ap_purge_invoice_list
3671: SET double_check_flag = 'N'
3672: WHERE invoice_id = p_id ;
3673: END IF ;
3674: END LOOP;
3687:
3688: FOR y IN 1..p_count LOOP
3689: IF use_vc2 THEN -- Bug 8942883
3690: p_id_vc2 := tab_check_vc2.next(p_id_vc2) ;
3691: UPDATE ap_purge_invoice_list
3692: SET double_check_flag = 'N'
3693: WHERE invoice_id in ( select invoice_id
3694: from ap_invoice_payments
3695: where check_id = p_id_vc2);
3694: from ap_invoice_payments
3695: where check_id = p_id_vc2);
3696: ELSE
3697: p_id := tab_check.next(p_id) ;
3698: UPDATE ap_purge_invoice_list
3699: SET double_check_flag = 'N'
3700: WHERE invoice_id in ( select invoice_id
3701: from ap_invoice_payments
3702: where check_id = p_id);
3710:
3711: END LOOP;
3712:
3713: /* Set flag 'Y' back */
3714: update ap_purge_invoice_list
3715: set double_check_flag = 'Y'
3716: where double_check_flag = 'S' ;
3717:
3718: debug_info := 'End of Invoice Validations';
4561: END IF;
4562:
4563: --
4564:
4565: UPDATE ap_purge_invoice_list PL
4566: SET PL.double_check_flag = 'N'
4567: WHERE EXISTS (
4568: SELECT 'history not purgeable'
4569: FROM ap_invoice_payments aip
5178: RETURN BOOLEAN IS
5179:
5180: CURSOR overflow_select is
5181: SELECT C.check_stock_id,C.check_number
5182: FROM ap_invoice_payments P, ap_purge_invoice_list PL,
5183: ap_checks C
5184: WHERE P.invoice_id = PL.invoice_id
5185: AND P.check_id = C.check_id
5186: AND PL.double_check_flag = 'Y'
5422:
5423: /* Bug 8913560
5424: CURSOR range (low_inv_id IN NUMBER) IS
5425: SELECT invoice_id
5426: FROM ap_purge_invoice_list
5427: WHERE double_check_flag = 'Y'
5428: and invoice_id > low_inv_id
5429: ORDER BY invoice_id asc;
5430: */
5431:
5432: CURSOR ap_invoice_cur ( p_start_rowid ROWID, -- Bug 8913560 low_inv_id IN NUMBER,
5433: p_end_rowid ROWID /* Bug 8913560 high_inv_id IN NUMBER*/ ) IS
5434: SELECT PL.invoice_id
5435: FROM ap_purge_invoice_list PL
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:
5473:
5474: select nvl(min(invoice_id),-1)
5475: , nvl(max(invoice_id),-1)
5476: into range_low, range_high
5477: from ap_purge_invoice_list
5478: where double_check_flag = 'Y';
5479:
5480: --Bug2382623 Changed the paramter to range_low
5481: OPEN range(range_low);
5504: delete from ap_chrg_allocations aca
5505: where exists (
5506: select 'allocations'
5507: from ap_invoice_distributions aid
5508: , ap_purge_invoice_list pil
5509: where aca.item_dist_id = aid.invoice_distribution_id
5510: and pil.invoice_id = aid.invoice_id
5511: and pil.invoice_id BETWEEN range_low and range_high
5512: and pil.double_check_flag = 'Y');
5516: /* bug3068811 : Changed from EXISTS to IN for performance */
5517: DELETE FROM ap_doc_sequence_audit AUD
5518: WHERE (AUD.doc_sequence_id , AUD.doc_sequence_value)
5519: IN (SELECT C.doc_sequence_id , C.doc_sequence_value
5520: FROM ap_purge_invoice_list PL,
5521: ap_checks C,
5522: ap_invoice_payments IP
5523: WHERE PL.double_check_flag = 'Y'
5524: AND -- Bug 8913560 PL.invoice_id BETWEEN range_low AND range_high
5552: -- bug 5052764 - go to base table ap_checks_all to remove FTS
5553: DELETE FROM ap_checks_all C
5554: WHERE C.check_id IN (
5555: SELECT P.check_id
5556: FROM ap_invoice_payments P, ap_purge_invoice_list PL
5557: WHERE P.invoice_id = PL.invoice_id
5558: AND PL.double_check_flag = 'Y'
5559: AND -- Bug 8913560 PL.invoice_id BETWEEN range_low AND range_high
5560: PL.rowid BETWEEN p_start_rowid AND p_end_rowid );
5583: DELETE FROM ap_payment_history aph
5584: WHERE EXISTS (
5585: SELECT 'history purgeable'
5586: FROM ap_invoice_payments aip
5587: , ap_purge_invoice_list PL
5588: WHERE aip.invoice_id = PL.invoice_id
5589: and aip.check_id = aph.check_id
5590: and PL.double_check_flag = 'Y'
5591: and PL.rowid BETWEEN p_start_rowid AND p_end_rowid); --9481539
5600:
5601: DELETE FROM ap_invoice_payments
5602: WHERE invoice_id IN (
5603: SELECT PL.invoice_id
5604: FROM ap_purge_invoice_list PL
5605: WHERE PL.double_check_flag = 'Y'
5606: AND -- Bug 8913560 PL.invoice_id BETWEEN range_low AND range_high
5607: PL.rowid BETWEEN p_start_rowid AND p_end_rowid);
5608:
5618:
5619: DELETE FROM ap_payment_schedules
5620: WHERE invoice_id IN (
5621: SELECT PL.invoice_id
5622: FROM ap_purge_invoice_list PL
5623: WHERE PL.double_check_flag = 'Y'
5624: AND -- Bug 8913560 PL.invoice_id BETWEEN range_low AND range_high
5625: PL.rowid BETWEEN p_start_rowid AND p_end_rowid );
5626:
5634:
5635: DELETE FROM ap_trial_balance
5636: WHERE invoice_id IN (
5637: SELECT PL.invoice_id
5638: FROM ap_purge_invoice_list PL
5639: WHERE PL.double_check_flag = 'Y'
5640: AND -- Bug 8913560 PL.invoice_id BETWEEN range_low AND range_high
5641: PL.rowid BETWEEN p_start_rowid AND p_end_rowid );
5642:
5650:
5651: DELETE FROM ap_holds
5652: WHERE invoice_id IN (
5653: SELECT PL.invoice_id
5654: FROM ap_purge_invoice_list PL
5655: WHERE PL.double_check_flag = 'Y'
5656: AND -- Bug 8913560 PL.invoice_id BETWEEN range_low AND range_high
5657: PL.rowid BETWEEN p_start_rowid AND p_end_rowid );
5658:
5666:
5667: DELETE FROM ap_inv_aprvl_hist
5668: WHERE invoice_id IN (
5669: SELECT PL.invoice_id
5670: FROM ap_purge_invoice_list PL
5671: WHERE PL.double_check_flag = 'Y'
5672: AND -- Bug 8913560 PL.invoice_id BETWEEN range_low AND range_high
5673: PL.rowid BETWEEN p_start_rowid AND p_end_rowid );
5674:
5681:
5682: DELETE FROM ap_invoice_distributions
5683: WHERE invoice_id IN (
5684: SELECT PL.invoice_id
5685: FROM ap_purge_invoice_list PL
5686: WHERE PL.double_check_flag = 'Y'
5687: AND -- Bug 8913560 PL.invoice_id BETWEEN range_low AND range_high
5688: PL.rowid BETWEEN p_start_rowid AND p_end_rowid );
5689:
5698:
5699: DELETE FROM ap_invoice_lines
5700: WHERE invoice_id IN (
5701: SELECT PL.invoice_id
5702: FROM ap_purge_invoice_list PL
5703: WHERE PL.double_check_flag = 'Y'
5704: AND -- Bug 8913560 PL.invoice_id BETWEEN range_low AND range_high
5705: PL.rowid BETWEEN p_start_rowid AND p_end_rowid );
5706:
5718: /* bug3284915 : Changed from EXISTS to IN for performance */
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
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'
5761: AND -- Bug 8913560 PL.invoice_id BETWEEN range_low AND range_high
5762: PL.rowid BETWEEN p_start_rowid AND p_end_rowid );
5763:
5857: l_count number := 0;
5858: /* Bug 8913560
5859: CURSOR range (low_inv_id IN NUMBER) IS
5860: SELECT invoice_id
5861: FROM ap_purge_invoice_list
5862: WHERE double_check_flag = 'Y'
5863: and invoice_id > low_inv_id
5864: ORDER BY invoice_id asc; */
5865: -- Bug 8913560 : Added the 3 variables given below
5876:
5877: SELECT nvl(min(invoice_id),-1)
5878: , nvl(max(invoice_id),-1)
5879: into range_low, range_high
5880: FROM ap_purge_invoice_list
5881: WHERE double_check_flag = 'Y';
5882:
5883: OPEN range(range_low); --Bug2711759
5884: WHILE l_count < g_range_size
5914:
5915: DELETE FROM ap_liability_balance alb
5916: WHERE EXISTS (
5917: SELECT 'records exist'
5918: FROM ap_purge_invoice_list pil
5919: WHERE alb.invoice_id = pil.invoice_id
5920: AND pil.double_check_flag = 'Y'
5921: AND /* Bug 8913560 pil.invoice_id BETWEEN range_low AND range_high */
5922: pil.rowid BETWEEN p_start_rowid AND p_end_rowid )
5930: WHERE ael.ae_header_id in
5931: ( SELECT aeh.ae_header_id
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'
5943: ( SELECT aeh.ae_header_id
5944: FROM ap_ae_headers aeh -- bug 2153117 added
5945: ,ap_accounting_events aae
5946: ,ap_invoice_payments aip
5947: ,ap_purge_invoice_list pil
5948: WHERE aae.source_id = aip.check_id
5949: and aae.source_table = 'AP_CHECKS'
5950: and pil.double_check_flag = 'Y'
5951: and aae.accounting_event_id = aeh.accounting_event_id
5961: DELETE FROM ap_ae_headers aeh
5962: WHERE aeh.accounting_event_id IN
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
5976: WHERE aeh.accounting_event_id in
5977: ( SELECT aae.accounting_event_id
5978: FROM ap_accounting_events aae
5979: , ap_invoice_payments aip
5980: , ap_purge_invoice_list pil
5981: -- bug2153117 removed
5982: -- , ap_ae_headers aeh
5983: WHERE aae.source_id = aip.check_id
5984: and aae.source_table = 'AP_CHECKS'
5997:
5998: DELETE FROM ap_encumbrance_lines aen
5999: WHERE EXISTS (
6000: SELECT 'dist'
6001: FROM ap_purge_invoice_list pil
6002: , ap_invoice_distributions aid
6003: WHERE aen.invoice_distribution_id = aid.invoice_distribution_id
6004: and aid.invoice_id = pil.invoice_id
6005: and pil.double_check_flag = 'Y'
6012: -- below 3 delete statement
6013:
6014: DELETE FROM AP_ACCOUNTING_EVENTS AAE
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: ;
6020: ;
6021:
6022: DELETE FROM AP_ACCOUNTING_EVENTS AAE WHERE
6023: aae.source_id in ( SELECT APC.CHECK_ID
6024: FROM AP_PURGE_INVOICE_LIST PIL,
6025: AP_CHECKS APC,
6026: AP_INVOICE_PAYMENTS AIP
6027: WHERE PIL.DOUBLE_CHECK_FLAG = 'Y'
6028: AND APC.CHECK_ID = AIP.CHECK_ID
6033:
6034:
6035: DELETE FROM AP_ACCOUNTING_EVENTS AAE WHERE
6036: AAE.source_id IN ( SELECT APH.CHECK_ID
6037: FROM AP_PURGE_INVOICE_LIST PIL,
6038: AP_INVOICE_PAYMENTS AIP,
6039: AP_PAYMENT_HISTORY APH
6040: WHERE PIL.DOUBLE_CHECK_FLAG = 'Y'
6041: AND APH.CHECK_ID = AIP.CHECK_ID
7245: WHERE I.batch_id = B.batch_id);
7246:
7247: -- clear_invoice_purge_list
7248: -- 9481539
7249: /* delete from ap_purge_invoice_list; */
7250: BEGIN
7251: l_sql_stmt :='T'||'RUNCATE '||'TABLE a'||'p.ap_purge_invoice_list';
7252: EXECUTE IMMEDIATE l_sql_stmt;
7253: EXCEPTION
7247: -- clear_invoice_purge_list
7248: -- 9481539
7249: /* delete from ap_purge_invoice_list; */
7250: BEGIN
7251: l_sql_stmt :='T'||'RUNCATE '||'TABLE a'||'p.ap_purge_invoice_list';
7252: EXECUTE IMMEDIATE l_sql_stmt;
7253: EXCEPTION
7254: WHEN OTHERS THEN
7255: Print('purge of ap_purge_invoice_list failed!'||sqlerrm);
7251: l_sql_stmt :='T'||'RUNCATE '||'TABLE a'||'p.ap_purge_invoice_list';
7252: EXECUTE IMMEDIATE l_sql_stmt;
7253: EXCEPTION
7254: WHEN OTHERS THEN
7255: Print('purge of ap_purge_invoice_list failed!'||sqlerrm);
7256: END;
7257:
7258: PO_AP_PURGE_GRP.delete_purge_lists
7259: ( p_api_version => 1.0,
7427: FUNCTION clear_invoice_purge_list RETURN BOOLEAN IS
7428:
7429: BEGIN
7430:
7431: delete from ap_purge_invoice_list;
7432:
7433: RETURN (TRUE);
7434:
7435: RETURN NULL; EXCEPTION
7715:
7716: IF l_batch_size IS NULL THEN
7717: SELECT COUNT(0)
7718: INTO l_total_recs
7719: FROM ap_purge_invoice_list
7720: WHERE double_check_flag = 'Y';
7721:
7722: l_batch_size := l_total_recs / ( 8 * l_num_workers ) ;
7723: END IF ;
7834: Procedure : Purge_acctg_and_ap_table
7835: Purpose : This is called from the child requests spawned by the procedure
7836: Submit_Multiple_Requests().
7837: It uses the AD Parallel architecture to divide the table
7838: AP_PURGE_INVOICE_LIST and uses the row_id range to call the
7839: purge_accounting() and delete_ap_tables() for deleting records
7840: *==========================================================================*/
7841: PROCEDURE Purge_acctg_and_ap_table
7842: (errbuf IN OUT NOCOPY VARCHAR2,
7871: l_industry VARCHAR2(30);
7872:
7873: BEGIN
7874:
7875: l_table_name := 'AP_PURGE_INVOICE_LIST';
7876: l_final_count := 0;
7877:
7878: --9481539 assigned the values to below global variables
7879: g_debug_switch := P_Debug_Switch ;