[Home] [Help]
PACKAGE BODY: APPS.AP_PURGE_PKG
Source
1 PACKAGE BODY AP_PURGE_PKG AS
2 /* $Header: appurgeb.pls 120.13.12000000.3 2007/08/13 10:45:27 schamaku ship $ */
3 --bug5052748
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';
13
14 g_purge_name VARCHAR2(15);
15 g_chv_status VARCHAR2(1) := 'N';
16 g_payables_status VARCHAR2(1) := 'N';
17 g_purchasing_status VARCHAR2(1) := 'N';
18 g_pa_status VARCHAR2(1) := 'N';
19 g_assets_status VARCHAR2(1) := 'N';
20 g_edi_status VARCHAR2(1) := 'N';
21 g_mrp_status VARCHAR2(1) := 'N';
22 g_activity_date DATE;
23 g_category VARCHAR2(30);
24 g_organization_id NUMBER;
25 g_range_size NUMBER;
26
27
28 ------------------------------------------------------------------
29 -- Procedure: Print
30 -- This is a print procedure to split a message string into 132
31 -- character strings.
32 ------------------------------------------------------------------
33 PROCEDURE Print
34 (P_string IN VARCHAR2) IS
35
36 stemp VARCHAR2(80);
37 nlength NUMBER := 1;
38
39 BEGIN
40
41 WHILE(length(P_string) >= nlength)
42 LOOP
43
44 stemp := substrb(P_string, nlength, 80);
45 fnd_file.put_line(FND_FILE.LOG, stemp);
46 nlength := (nlength + 80);
47
48 END LOOP;
49
50 EXCEPTION
51 WHEN OTHERS THEN
52
53 IF (SQLCODE <> -20001) THEN
54 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
55 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
56 END IF;
57 APP_EXCEPTION.RAISE_EXCEPTION;
58
59 END Print;
60
61
62 ------------------------------------------------------------------
63 -- Procedure: Set_Purge_Status
64 -- This procedure is used to set the status of the purge process
65 ------------------------------------------------------------------
66 FUNCTION Set_Purge_Status
67 (P_Status IN VARCHAR2,
68 P_Purge_Name IN VARCHAR2,
69 P_Debug_Switch IN VARCHAR2,
70 P_Calling_Sequence IN VARCHAR2)
71 RETURN BOOLEAN IS
72
73 debug_info VARCHAR2(200);
74 current_calling_sequence VARCHAR2(2000);
75
76 BEGIN
77 -- Update the calling sequence
78 --
79 current_calling_sequence :=
80 'Set_purge_status<-'||P_calling_sequence;
81 --
82 debug_info := 'Starting Set_purge_status';
83 IF (p_debug_switch in ('y','Y')) THEN
84 Print('(Updating table financials_purges)'||debug_info);
85 END IF;
86
87 UPDATE financials_purges
88 SET status = P_Status
89 WHERE purge_name = P_Purge_Name;
90 --
91 debug_info := 'End Set_purge_status';
92 IF (g_debug_switch in ('y','Y')) THEN
93 Print('(Done updating table financials_purges)'||debug_info);
94 END IF;
95 RETURN(TRUE);
96
97 RETURN NULL;
98
99 EXCEPTION
100 WHEN OTHERS THEN
101 IF (SQLCODE < 0 ) then
102 Print(SQLERRM);
103 END IF;
104 RETURN(FALSE);
105
106 END;
107
108
109 ------------------------------------------------------------------
110 -- Procedure: Get_Accounting_Method
111 -- This routine gets the accounting method options
112 ------------------------------------------------------------------
113
114 FUNCTION Get_Accounting_Method
115 (P_Recon_Acctg_Flag OUT NOCOPY VARCHAR2,
116 P_Using_Accrual_Basis OUT NOCOPY VARCHAR2,
117 P_Using_Cash_Basis OUT NOCOPY VARCHAR2,
118 P_Calling_Sequence IN VARCHAR2)
119
120 RETURN BOOLEAN IS
121
122 debug_info VARCHAR2(200);
123 current_calling_sequence VARCHAR2(2000);
124
125 BEGIN
126 -- Update the calling sequence
127 --
128 current_calling_sequence :=
129 'Get_accounting_method<-'||P_calling_sequence;
130 --
131 debug_info := 'Starting Get_Accounting_Method';
132 IF g_debug_switch in ('y','Y') THEN
133 Print('(Get_Accounting_Method)' ||debug_info);
134 END IF;
135
136 /* Bug#2274656 Selecting Recon Accounting Flag also in this program unit */
137 SELECT DECODE(ASP.accounting_method_option, 'Accrual', 'Y',
138 DECODE(ASP.secondary_accounting_method,
139 'Accrual', 'Y', 'N')),
140 DECODE(ASP.accounting_method_option,'Cash','Y',
141 DECODE(ASP.secondary_accounting_method,
142 'Cash', 'Y', 'N')),
143 nvl(ASP.RECON_ACCOUNTING_FLAG,'N')
144 INTO p_using_accrual_basis,
145 p_using_cash_basis,
146 p_recon_acctg_flag
147 FROM ap_system_parameters ASP;
148
149 --
150 debug_info := 'End Get_Accounting_Method';
151 IF g_debug_switch in ('y','Y') THEN
152 Print('(Get_Accounting_Method)' ||debug_info);
153 END IF;
154
155 RETURN (TRUE);
156 RETURN NULL;
157
158 EXCEPTION
159
160 WHEN OTHERS THEN
161 IF (SQLCODE < 0 ) then
162 Print(SQLERRM);
163 END IF;
164 RETURN (FALSE);
165
166 END Get_Accounting_Method;
167
168
169 ------------------------------------------------------------------
170 -- Procedure: Check_no_purge_in_process
171 -- This process checks if any purge is in process
172 ------------------------------------------------------------------
173 FUNCTION Check_no_purge_in_process
174 (P_Purge_Name IN VARCHAR2,
175 P_Debug_Switch IN VARCHAR2,
176 P_Calling_Sequence IN VARCHAR2)
177 RETURN BOOLEAN IS
178
179 debug_info VARCHAR2(200);
180 current_calling_sequence VARCHAR2(2000);
181 invoice_count NUMBER;
182 po_count NUMBER;
183 req_count NUMBER;
184 vendor_count NUMBER;
185
186 l_status VARCHAR2(30);
187
188 BEGIN
189 -- Update the calling sequence
190 --
191 current_calling_sequence :=
192 'Check_no_purge_in_process<-'||P_calling_sequence;
193
194 debug_info := 'Starting Check_no_purge_in_process';
195 IF p_debug_switch in ('y','Y') THEN
196 Print('(Check_no_purge_in_process)' ||debug_info);
197 END IF;
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
207 -- count_pos
208 select count(1)
209 into po_count
210 from po_purge_po_list
211 where double_check_flag = 'Y';
212
213 if (po_count = 0) then
214
215 -- count_reqs
216 select count(1)
217 into req_count
218 from po_purge_req_list
219 where double_check_flag = 'Y';
220
221 if (req_count = 0) then
222
223 -- count_vendors
224 select count(1)
225 into vendor_count
226 from po_purge_vendor_list
227 where double_check_flag = 'Y';
228
229 if (vendor_count = 0) then
230
231 null;
232 else
233
234 debug_info := 'The PO_PURGE_VENDOR_LIST table contains records. ';
235 Print('(Check_no_purge_in_process)' || debug_info);
236 Print(' Please make sure no purges are running and clear');
237 Print(' this table. Process terminating.');
238
239 l_status := 'COMPLETED-ABORTED';
240 if (Set_Purge_Status (
241 l_status,
242 p_purge_name,
243 p_debug_switch,
244 'Check_no_purge_in_process') <> TRUE) then
245 Print(' Set_purge_status failed');
246 Return (FALSE);
247 end if;
248
249 end if;
250
251 else -- req_count <> 0
252
253 debug_info := 'The PO_PURGE_REQ_LIST table contains records. ';
254 Print('Check_no_purge_in_process' || debug_info);
255 Print('Please make sure no purges are running and clear');
256 Print(' this table. Process terminating.');
257
258 l_status := 'COMPLETED-ABORTED';
259 if (Set_Purge_Status
260 (l_status,
261 p_purge_name,
262 p_debug_switch,
263 'Check_no_purge_in_process') <> TRUE) then
264 Print(' Set_purge_status failed');
265 Return (FALSE);
266 end if;
267
268 end if ; -- req_count
269
270 else
271
272 debug_info := 'The PO_PURGE_PO_LIST table contains records. ';
273 Print('Check_no_purge_in_process' || debug_info);
274 Print('Please make sure no purges are running and clear');
275 Print(' this table. Process terminating.');
276
277 l_status := 'COMPLETED-ABORTED';
278 if (Set_Purge_Status
279 (l_status,
280 p_purge_name,
281 p_debug_switch,
282 'Check_no_purge_in_process') <> TRUE) then
283 Print(' Set_purge_status failed');
284 Return (FALSE);
285 end if;
286
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
296 l_status := 'COMPLETED-ABORTED';
297 if (Set_Purge_Status
298 (l_status,
299 p_purge_name,
300 p_debug_switch,
301 'Check_no_purge_in_process') <> TRUE) then
302 Print(' Set_purge_status failed');
303 Return (FALSE);
304 end if;
305
306 end if; -- invoice_count
307
308 COMMIT;
309 RETURN (TRUE);
310
311 RETURN NULL; EXCEPTION
312 WHEN OTHERS then
313 IF (SQLCODE < 0 ) then
314 Print(SQLERRM);
315 END IF;
316 RETURN (FALSE);
317
318 END Check_no_purge_in_process;
319
320
321 ------------------------------------------------------------------
322 -- Procedure: Check_Chv_In_Cum
323 --
324 ------------------------------------------------------------------
325 FUNCTION Check_chv_in_cum
326 (P_Calling_Sequence IN VARCHAR2)
327 RETURN BOOLEAN IS
328
329 debug_info VARCHAR2(200);
330 current_calling_sequence VARCHAR2(2000);
331
332 BEGIN
333 -- Update the calling sequence
334 --
335 current_calling_sequence := 'check_chv_in_cum<-'||P_calling_sequence;
336
337 debug_info := 'Starting check_chv_in_cum';
338 IF g_debug_switch in ('y','Y') THEN
339 Print('(Check_chv_in_cum)' ||debug_info);
340 END IF;
341
342 --
343 -- test_chv_in_cum
344
345 delete from chv_purge_schedule_list cpsl
346 where exists (select null
347 from chv_cum_periods ccp,
348 chv_schedule_items csi,
349 chv_schedule_headers csh,
350 chv_org_options coo
351 where ccp.organization_id = g_organization_id
352 and sysdate between ccp.cum_period_start_date and
353 NVL(ccp.cum_period_end_date,sysdate + 1)
354 and coo.organization_id = ccp.organization_id
355 and coo.enable_cum_flag = 'Y'
356 and csh.schedule_id = csi.schedule_id
357 and csh.schedule_horizon_start >= ccp.cum_period_start_date
358 and csi.schedule_item_id = cpsl.schedule_item_id);
359
360 RETURN (TRUE);
361 RETURN NULL;
362
363 EXCEPTION
364 WHEN OTHERS THEN
365 IF (SQLCODE < 0 ) THEN
366 Print(SQLERRM);
367 END IF;
368 RETURN (FALSE);
369 END Check_chv_in_cum;
370
371
372 ------------------------------------------------------------------
373 -- Procedure: Check_Chv_In_EDI
374 --
375 ------------------------------------------------------------------
376 FUNCTION Check_chv_in_edi
377 (P_Calling_Sequence IN VARCHAR2)
378 RETURN BOOLEAN IS
379
380 debug_info VARCHAR2(200);
381 current_calling_sequence VARCHAR2(2000);
382
383 BEGIN
384 -- Update the calling sequence
385 --
386 current_calling_sequence := 'check_chv_in_edi<-'||P_calling_sequence;
387
388 debug_info := 'Starting check_chv_in_edi';
389 IF g_debug_switch in ('y','Y') THEN
390 Print('(Check_chv_in_edi)' ||debug_info);
391 END IF;
392
393 --
394 -- test_chv_in_edi
395
396 delete from chv_purge_schedule_list cpsl
397 where exists (select null
398 from chv_schedule_items csi,
399 ece_spso_items esi
400 where csi.schedule_item_id = cpsl.schedule_item_id
401 and csi.schedule_id = esi.schedule_id);
402
403 RETURN (TRUE);
404 RETURN NULL;
405
406 EXCEPTION
407 WHEN OTHERS THEN
408 IF (SQLCODE < 0 ) THEN
409 Print(SQLERRM);
410 END IF;
411 RETURN (FALSE);
412 END Check_chv_in_edi;
413
414
415 ------------------------------------------------------------------
416 -- Procedure: Do_Dependent_Inv_Checks
417 --
418 ------------------------------------------------------------------
419 FUNCTION DO_DEPENDENT_INV_CHECKS
420 (P_Calling_Sequence IN VARCHAR2)
421 RETURN BOOLEAN IS
422
423 /* bug2918268 : Created this function instead of do_dependent_inv_checks function.
424 Because performance of delete stmt in do_dependent_inv_checks was very poor.
425 This function does same check with the delete stmt.
426 */
427
428 TYPE tab_status_type IS TABLE OF VARCHAR2(1) INDEX BY BINARY_INTEGER;
429 tab_inv tab_status_type;
430 tab_check tab_status_type;
431 tab_clear tab_status_type;
432
433 /* bug3136911 added ap_invoice_payments table join in order to check only
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
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;
449 p_id integer;
450
451 l_cnt integer;
452 debug_info VARCHAR2(200);
453 current_calling_sequence VARCHAR2(2000);
454 l_invoice BOOLEAN ;
455 l_dummy NUMBER ;
456
457 Function Check_check(l_invoice_id IN NUMBER ) RETURN BOOLEAN;
458
459 /* Get related invoice_id from check_id and check if the invoice_id is
460 in purge list. If there is, call check_check to get check_id which
461 is related to the invoice_id */
462 Function Check_inv(l_check_id IN NUMBER) RETURN BOOLEAN IS
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;
472 l_inv_id ap_purge_invoice_list.invoice_id%TYPE;
473
474 BEGIN
475
476 OPEN c_inv ;
477 LOOP
478
479 FETCH c_inv into l_inv_id ;
480 EXIT WHEN c_inv%NOTFOUND ;
481
482 /* if related invoice id is not in purge list */
483 IF l_inv_id is null THEN
484 l_flag := FALSE ;
485 ELSE
486
487 /* if the invocie_id is already checked */
488 IF tab_inv.exists(l_inv_id) THEN
489 l_flag := TRUE ;
490 ELSE
491 tab_inv(l_inv_id) := 'X' ;
492 l_flag := check_check(l_inv_id) ;
493 END IF;
494 END IF;
495
496 EXIT WHEN (not l_flag) ;
497
498 END LOOP;
499
500 CLOSE C_inv;
501 RETURN(l_flag) ;
502
503 END ;
504
505 /* Get related check_id from invoice_id and call check_invoice
506 to check if the invoice is in purge list. */
507 Function Check_check(l_invoice_id IN NUMBER ) RETURN BOOLEAN IS
508
509 CURSOR c_check IS
510 select check_id
511 from ap_invoice_payments
512 where invoice_id = l_invoice_id ;
513
514 l_flag BOOLEAN := FALSE;
515 l_check_id number;
516
517 BEGIN
518
519 OPEN c_check ;
520 LOOP
521
522 FETCH c_check into l_check_id ;
523 EXIT WHEN c_check%NOTFOUND ;
524
525 /* if the check_id is already checked */
526 IF tab_check.exists(l_check_id) THEN
527 l_flag := TRUE ;
528 ELSE
529 tab_check(l_check_id) := 'X' ;
530 l_flag := check_inv(l_check_id) ;
531 END IF;
532
533 EXIT WHEN (not l_flag) ;
534
535 END LOOP;
536
537 CLOSE C_check;
538 RETURN(l_flag) ;
539
540 END ;
541
542 /* main process */
543 BEGIN
544 -- Update the calling sequence
545 --
546 current_calling_sequence :=
547 'Do_dependent_inv_checks<-'||P_calling_sequence;
548 --
549
550 debug_info := 'Starting series of debug invoice validations';
551 IF g_debug_switch in ('y','Y') THEN
552 Print('(do_dependent_inv_checks)' ||debug_info);
553 END IF;
554
555
556 FOR l_main IN c_main
557 LOOP
558
559 /* initialization */
560 tab_inv := tab_clear ;
561 tab_check := tab_clear;
562
563 /* check if this invoice is not checked yet */
564 OPEN c_main_check(l_main.invoice_id) ;
565 FETCH c_main_check into l_dummy ;
566 l_invoice := c_main_check%FOUND ;
567 CLOSE c_main_check ;
568
569 /* if this invoice is not checked yet */
570 IF (l_invoice) THEN
571
572 tab_inv(l_main.invoice_id) := 'X' ;
573
574 IF check_check(l_main.invoice_id) THEN
575
576 /* if this chain is purgeable,set flag 'S' for all invoices in this chain */
577 p_count := tab_inv.count;
578 IF p_count <> 0 THEN
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
588 END IF;
589 ELSE
590
591 /* if this chain is not purgeable, delete selected invoice from purge list */
592 p_count := tab_inv.count;
593 IF p_count <> 0 THEN
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
603 /* delete unpurgeable list beforehand for performance */
604 p_count := tab_check.count;
605
606 IF p_count <> 0 THEN
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;
616 END IF;
617
618 END IF;
619
620 END IF;
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';
630 IF g_debug_switch in ('y','Y') THEN
631 Print('(do_dependent_inv_checks)' ||debug_info);
632 END IF;
633
634 commit;
635 return(TRUE) ;
636
637 RETURN NULL;
638
639 EXCEPTION
640 WHEN OTHERS THEN
641 IF (SQLCODE < 0 ) then
642 Print(SQLERRM);
643 END IF;
644 RETURN(FALSE);
645 END ;
646
647 ------------------------------------------------------------------
648 -- Procedure: Do_Independent_Inv_Checks
649 --
650 ------------------------------------------------------------------
651 FUNCTION Do_independent_inv_checks
652 (P_Using_Accrual_Basis IN VARCHAR2,
653 P_Using_Cash_Basis IN VARCHAR2,
654 P_Recon_Acctg_Flag IN VARCHAR2,
655 P_Calling_Sequence IN VARCHAR2)
656 RETURN BOOLEAN IS
657
658 debug_info VARCHAR2(200);
659 current_calling_sequence VARCHAR2(2000);
660 l_list_count number;
661
662 BEGIN
663 -- Update the calling sequence
664 --
665 current_calling_sequence :=
666 'Do_independent_inv_checks<-'||P_calling_sequence;
667
668
669 debug_info :=
670 'Starting independent invoice validations -- Payment Schedules';
671 IF g_debug_switch in ('y','Y') THEN
672 Print('(Do_independent_inv_checks)' ||debug_info);
673 END IF;
674
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
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)
688 OR PS.last_update_date > g_activity_date));
689
690
691
692 IF g_pa_status = 'Y' then
693 debug_info := 'Test PA Invoices';
694 IF g_debug_switch in ('y','Y') THEN
695 Print('(Do_independent_inv_checks)' ||debug_info);
696 END IF;
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
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;
714
715
716
717 --
718 debug_info := 'Test Distributions';
719 IF g_debug_switch in ('y','Y') THEN
720 Print('(Do_independent_inv_checks)' ||debug_info);
721 END IF;
722
723 /*
724 1897941 fbreslin: If an invoice is cancelled, the ASSETS_ADDTION_FLAG is
725 set to "U" so Mass Additions does not include the
726 distribution. We are alos not supposed to purge
727 invoices if any of the distributions have ben passed to
728 FA. Adding a check to see if the invoice is cancelled
729 before we remove an invoice with ASSETS_ADDTION_FLAG = U
730 from the purge list.
731 */
732
733 IF g_category = 'SIMPLE INVOICES' THEN
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
743 AND PL.invoice_id = D.invoice_id
744 AND ( D.last_update_date > g_activity_date
745 OR D.posted_flag <> 'Y'
746 OR D.accrual_posted_flag =
747 DECODE(p_using_accrual_basis,
748 'Y', 'N',
749 'Z')
750 OR D.cash_posted_flag =
751 DECODE(p_using_cash_basis,
752 'Y', DECODE(D.cash_posted_flag,
753 'N', 'N',
754 'P', 'P',
755 'Z'),
756 'Z')
757 OR D.po_distribution_id IS NOT NULL
758 OR ( D.assets_addition_flag||'' =
759 DECODE(g_assets_status,
760 'Y', 'U',
761 'cantequalme')
762 AND I.cancelled_date IS NULL)));
763
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
773 AND PL.invoice_id = D.invoice_id
774 AND ( D.last_update_date > g_activity_date
775 OR D.posted_flag <> 'Y'
776 OR D.accrual_posted_flag =
777 DECODE(p_using_accrual_basis,
778 'Y', 'N',
779 'Z')
780 OR D.cash_posted_flag =
781 DECODE(p_using_cash_basis,
782 'Y', DECODE(D.cash_posted_flag,
783 'N', 'N',
784 'P', 'P',
785 'Z'),
786 'Z')
787 OR ( D.assets_addition_flag||'' =
788 DECODE(g_assets_status,
789 'Y', 'U',
790 'cantequalme')
791 AND I.cancelled_date IS NULL)));
792 END IF;
793
794
795
796 debug_info := 'Test Payments';
797 IF g_debug_switch in ('y','Y') THEN
798 Print('(Do_independent_inv_checks)' ||debug_info);
799 END IF;
800
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
810 WHERE P.invoice_id = PL.invoice_id
811 AND P.check_id = C.check_id
812 AND (((P.posted_flag <> 'Y'
813 OR
814 P.accrual_posted_flag =
815 DECODE(p_using_accrual_basis, 'Y','N','Z')
816 OR
817 P.cash_posted_flag =
818 DECODE(p_using_cash_basis,'Y',
819 DECODE(P.cash_posted_flag,'N',
820 'N','P','P','Z'),'Z')
821 OR
822 P.last_update_date > g_activity_date
823 OR
824 C.last_update_date > g_activity_date
825 OR
826 /*Following two conditions added for bug#2274656 to prevent
827 Future Dated checks being purged before they are matured */
828 (C.future_pay_due_date is not null
829 AND C.status_lookup_code ='ISSUED')
830
831 /* Code Modified by MSWAMINA.
832 Bug 2211285.
833 Payments should not be considered to in the purge list if it has any reference
834 information left in cash management. AP assumes that if a customer uses
835 CE, They have already purged the related data in CE before purging the AP
836 payments information.
837 So Added the following condition back. */
838 /* Fix for bug#2274656
839 Bug#2211285 hard codes the date in case Cleared Date and Void date are null, to an infinite
840 value and makes the condition always true , so the payment records would not
841 get purged if it has not been Cleared, but some customers may not be using
842 recon accounting at all. Now the recon accounting flag is selected
843 in get_accounting_method() and we decide based on that */
844 OR
845 decode(p_recon_acctg_flag,'Y',nvl(c.cleared_date,
846 nvl(c.void_date,to_date('12/31/2999','MM/DD/YYYY'))))
847 > g_activity_date
848 ))
849 OR
850 EXISTS (SELECT 'Referenced by cashbook'
851 from ce_statement_reconcils_all SR
852 where C.check_id=SR.reference_id
853 AND SR.reference_type= 'PAYMENT'
854 AND SR.org_id = C.org_id )));
855
856
857
858 --
859 debug_info := 'Test Prepayments';
860 IF g_debug_switch in ('y','Y') THEN
861 Print('(Do_independent_inv_checks)' ||debug_info);
862 END IF;
863
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
873 OR PL.invoice_id = IP.prepay_id);
874
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
884 AND ID.line_type_lookup_code = 'PREPAY'
885 AND ID.prepay_distribution_id IS NOT NULL);
886
887 /* Testing of Payment History moved to this location while fixing bug#2274656
888 This doesn't make any difference but testing transaction before transfer
889 will reduce number of records tested for Transfer from Acctg tables */
890
891 debug_info := 'Test Payment History';
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
901 where aip.invoice_id = PL.invoice_id
902 and aip.check_id = aph.check_id
903 -- To check for posted_flag added for bug#2274656
904 and nvl(aph.posted_flag,'N') <> 'Y'
905 --Bug 1579474
906 --and aph.last_update_date >= g_activity_date);
907 and aph.last_update_date > g_activity_date);
908
909 debug_info := 'Test Accounting';
910 IF g_debug_switch in ('y','Y') THEN
911 Print('(Do_independent_inv_checks)' ||debug_info);
912 END IF;
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
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
929 and xte.entity_id = xe.entity_id
930 and xe.event_id = xeh.event_id --Bug6318079
931 and xe.application_id = 200
932 and xeh.application_id = 200
933 and xte.application_id = 200
934 and (xeh.gl_transfer_status_code = 'N'
935 OR ( xeh.last_update_date > g_activity_date )))
936 OR EXISTS (
937 Select 'payment accounting not purgeable'
938 from xla_events xe, --Bug 4588031
939 xla_transaction_entities xte, --Bug 4588031
940 ap_invoice_payments aip,
941 ap_system_parameters_all asp,--bug5052478
942 xla_ae_headers xeh --Bug 4588031
943 where xte.entity_code = 'AP_PAYMENTS'
944 and xte.source_id_int_1 = aip.check_id
945 and xte.entity_id = xe.entity_id
946 AND asp.set_of_books_id=xte.ledger_id
947 AND aip.org_id=asp.org_id
948 and PL.invoice_id = aip.invoice_id
949 and xe.event_id = xeh.event_id
950 and xe.application_id = 200
951 and xeh.application_id = 200
952 and xte.application_id = 200
953 and (xeh.gl_transfer_status_code = 'N'
954 OR ( xeh.last_update_date > g_activity_date)));
955
956
957 debug_info := 'Test Invoce matching to receipts';
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
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
975 and ad.rcv_transaction_id is not null
976 and exists (
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;
986 Print(to_char(l_list_count)||' records in ap_purge_invoice_list table');
987
988
989 RETURN (TRUE);
990 RETURN NULL;
991
992 EXCEPTION
993 WHEN OTHERS THEN
994 IF (SQLCODE < 0 ) then
995 Print(SQLERRM);
996 END IF;
997 RETURN(FALSE);
998
999 END Do_independent_inv_checks;
1000
1001
1002 ------------------------------------------------------------------
1003 -- Procedure: Match_pos_to_invoices_ctrl
1004 --
1005 ------------------------------------------------------------------
1006 FUNCTION Match_pos_to_invoices_ctrl
1007 (P_Purge_Name IN VARCHAR2,
1008 P_Purge_Status IN VARCHAR2,
1009 P_Calling_Sequence IN VARCHAR2)
1010 RETURN BOOLEAN IS
1011
1012 debug_info VARCHAR2(200);
1013 current_calling_sequence VARCHAR2(2000);
1014 po_count NUMBER;
1015 invoice_count NUMBER;
1016 invs_before_po_match NUMBER;
1017 pos_before_inv_match NUMBER;
1018 pos_before_dependents NUMBER;
1019 invs_before_dependents NUMBER;
1020 start_list_count NUMBER;
1021 list_count NUMBER;
1022
1023 l_first_iteration BOOLEAN;
1024 l_po_docs_filtered_flag BOOLEAN;
1025
1026 l_po_return_status VARCHAR2(1);
1027 l_po_msg VARCHAR2(2000);
1028 l_po_records_filtered_tmp VARCHAR2(1);
1029
1030
1031 BEGIN
1032 -- Update the calling sequence
1033 --
1034 current_calling_sequence :=
1035 'Match_pos_to_reqs_ctrl<-'||P_calling_sequence;
1036
1037 debug_info := 'Starting Match_pos_to_invoices_ctrl';
1038 IF g_debug_switch in ('y','Y') THEN
1039 Print('(Match_pos_to_invoices_ctrl)' ||debug_info);
1040 END IF;
1041
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
1051 LOOP -- <loop 1>
1052
1053 l_po_docs_filtered_flag := FALSE;
1054
1055 LOOP -- <loop 2>
1056
1057 --
1058 debug_info := 'LOOP Match_pos_to_invoices_ctrl';
1059 IF g_debug_switch in ('y','Y') THEN
1060 Print('(Match_pos_to_invoices_ctrl)' ||debug_info);
1061 END IF;
1062
1063 invs_before_po_match := invoice_count;
1064
1065 debug_info := 'LOOP match_pos_to_invoices';
1066 IF g_debug_switch in ('y','Y') THEN
1067 Print('(Match_pos_to_invoices_ctrl)' ||debug_info);
1068 END IF;
1069
1070 -- match_pos_to_invoices
1071
1072 PO_AP_PURGE_GRP.filter_records
1073 ( p_api_version => 1.0,
1074 p_init_msg_list => 'T',
1075 p_commit => 'F',
1076 x_return_status => l_po_return_status,
1077 x_msg_data => l_po_msg,
1078 p_purge_status => p_purge_status,
1079 p_purge_name => p_purge_name,
1080 p_purge_category => g_category,
1081 p_action => 'FILTER DEPENDENT PO AND AP',
1082 x_po_records_filtered => l_po_records_filtered_tmp
1083 );
1084
1085 IF (l_po_return_status <> 'S') THEN
1086 Print(l_po_msg);
1087 RETURN FALSE;
1088 END IF;
1089
1090 IF (l_po_records_filtered_tmp = 'T') THEN
1091 l_po_docs_filtered_flag := TRUE;
1092 END IF;
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
1102 and ad.po_distribution_id is not null
1103 and not exists (select null
1104 from po_purge_po_list ppl,
1105 po_distributions pd
1106 where ppl.po_header_id =
1107 pd.po_header_id
1108 and pd.po_distribution_id =
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
1118 where ad.invoice_id = apl.invoice_id
1119 AND pd.po_distribution_id=ad.po_distribution_id
1120 and ad.po_distribution_id is not null
1121 and not exists (SELECT null
1122 FROM po_purge_po_list ppl
1123 WHERE ppl.double_check_flag = 'Y'
1124 AND ppl.po_header_id =pd.po_header_id));
1125
1126 END IF;
1127
1128 COMMIT;
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
1138
1139 EXIT;
1140 END IF;
1141
1142 if (invoice_count < invs_before_po_match) then
1143
1144 invs_before_dependents := invoice_count;
1145
1146 debug_info := 'Starting series of dependent invoice validations';
1147 IF g_debug_switch in ('y','Y') THEN
1148 Print('(Match_pos_to_invoices_ctrl)' ||debug_info);
1149 END IF;
1150
1151
1152 -- do_dependent_inv_checks
1153
1154 LOOP -- <loop3>
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
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
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
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'
1188 FROM ap_invoice_payments IP1, ap_invoice_payments IP2
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;
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
1207 invoice_count := list_count;
1208 EXIT;
1209 end if;
1210 END LOOP; -- end <loop 3>
1211 COMMIT;
1212 END IF; -- invoice count < inv_before_po_match
1213 END LOOP; -- end <loop 2>
1214
1215 IF (l_first_iteration OR
1216 l_po_docs_filtered_flag) THEN
1217
1218 PO_AP_PURGE_GRP.filter_records
1219 ( p_api_version => 1.0,
1220 p_init_msg_list => 'T',
1221 p_commit => 'F',
1222 x_return_status => l_po_return_status,
1223 x_msg_data => l_po_msg,
1224 p_purge_status => p_purge_status,
1225 p_purge_name => p_purge_name,
1226 p_purge_category => g_category,
1227 p_action => 'FILTER DEPENDENT PO AND REQ',
1228 x_po_records_filtered => l_po_records_filtered_tmp
1229 );
1230
1231 IF (l_po_return_status <> 'S') THEN
1232 Print(l_po_msg);
1233 RETURN FALSE;
1234 END IF;
1235 IF (l_po_records_filtered_tmp <> 'T') THEN
1236 l_po_docs_filtered_flag := FALSE;
1237 END IF;
1238 END IF;
1239
1240 l_first_iteration := FALSE;
1241
1242 EXIT WHEN NOT l_po_docs_filtered_flag;
1243
1244 END LOOP;
1245
1246 debug_info := 'End Match_pos_to_invoices_ctrl';
1247 IF g_debug_switch in ('y','Y') THEN
1248 Print('(Match_pos_to_invoices_ctrl)' ||debug_info);
1249 END IF;
1250
1251 RETURN (TRUE);
1252
1253 RETURN NULL; EXCEPTION
1254 WHEN OTHERS then
1255 IF (SQLCODE < 0 ) then
1256 Print(SQLERRM);
1257 END IF;
1258 RETURN (FALSE);
1259 END Match_pos_to_invoices_ctrl;
1260
1261
1262 ------------------------------------------------------------------
1263 -- Procedure: Seed_Chv_By_Cum
1264 --
1265 ------------------------------------------------------------------
1266 FUNCTION Seed_chv_by_cum
1267 (P_Purge_Name IN VARCHAR2,
1268 P_Calling_Sequence IN VARCHAR2)
1269 RETURN BOOLEAN IS
1270
1271 debug_info VARCHAR2(200);
1272 current_calling_sequence VARCHAR2(2000);
1273
1274 BEGIN
1275 -- Update the calling sequence
1276 --
1277 current_calling_sequence := 'seed_chv_by_cum<-'||P_calling_sequence;
1278
1279 debug_info := 'Starting seed_chv_by_cum';
1280 IF g_debug_switch in ('y','Y') THEN
1281 Print('(seed_chv_by_cum)' ||debug_info);
1282 END IF;
1283
1284 --
1285 insert into chv_purge_cum_list
1286 (cum_period_id,
1287 purge_name,
1288 double_check_flag)
1289 select ccp.cum_period_id,
1290 p_purge_name,
1291 'Y'
1292 from chv_cum_periods ccp
1293 where ccp.organization_id = g_organization_id
1294 and NVL(ccp.cum_period_end_date, sysdate + 1) <= g_activity_date
1295 and NVL(ccp.cum_period_end_date,sysdate + 1) < sysdate;
1296
1297 debug_info := 'Starting seeding items in CUM';
1298 IF g_debug_switch in ('y','Y') THEN
1299 Print('(seed_chv_by_cum)' ||debug_info);
1300 END IF;
1301
1302 insert into chv_purge_schedule_list
1303 (schedule_item_id,
1304 purge_name,
1305 double_check_flag)
1306 select csi.schedule_item_id,
1307 p_purge_name,
1308 'Y'
1309 from chv_schedule_items csi,
1310 chv_schedule_headers csh,
1311 chv_purge_cum_list cpcl,
1312 chv_cum_periods ccp
1313 where csh.schedule_id = csi.schedule_id
1314 and csh.schedule_horizon_start between ccp.cum_period_start_date
1315 and ccp.cum_period_end_date
1316 and ccp.cum_period_id = cpcl.cum_period_id
1317 and csi.organization_id = g_organization_id;
1318
1319 RETURN (TRUE);
1320
1321 RETURN NULL;
1322
1323 EXCEPTION
1324 WHEN OTHERS THEN
1325 IF (SQLCODE < 0 ) THEN
1326 Print(SQLERRM);
1327 END IF;
1328 RETURN (FALSE);
1329 END Seed_chv_by_cum;
1330
1331
1332 ------------------------------------------------------------------
1333 -- Procedure: Seed_Chv_By_Org
1334 --
1335 ------------------------------------------------------------------
1336 FUNCTION Seed_chv_by_org
1337 (P_Purge_Name IN VARCHAR2,
1338 P_Calling_Sequence IN VARCHAR2)
1339 RETURN BOOLEAN IS
1340
1341 debug_info VARCHAR2(200);
1342 current_calling_sequence VARCHAR2(2000);
1343
1344 BEGIN
1345 -- Update the calling sequence
1346 --
1347 current_calling_sequence := 'seed_chv_by_org<-'||P_calling_sequence;
1348
1349 debug_info := 'Starting seed_chv_by_org';
1350 IF g_debug_switch in ('y','Y') THEN
1351 Print('(seed_chv_by_org)' ||debug_info);
1352 END IF;
1353
1354 --
1355 insert into chv_purge_schedule_list
1356 (schedule_item_id,
1357 purge_name,
1358 double_check_flag)
1359 select csi.schedule_item_id,
1360 p_purge_name,
1361 'Y'
1362 from chv_schedule_items csi,
1363 chv_schedule_headers csh
1364 where csh.schedule_id = csi.schedule_id
1365 and csh.last_update_date <= g_activity_date
1366 and NVL(csi.item_purge_status,'N') <> 'PURGED'
1367 and csi.organization_id = g_organization_id;
1368
1369 RETURN (TRUE);
1370 RETURN NULL;
1371
1372 EXCEPTION
1373 WHEN OTHERS THEN
1374 IF (SQLCODE < 0 ) THEN
1375 Print(SQLERRM);
1376 END IF;
1377 RETURN (FALSE);
1378 END seed_chv_by_org;
1379
1380
1381 ------------------------------------------------------------------
1382 -- Procedure: Seed_Invoices
1383 --
1384 ------------------------------------------------------------------
1385 FUNCTION Seed_Invoices
1386 (P_Purge_Name IN VARCHAR2,
1387 P_Using_Accrual_Basis IN VARCHAR2,
1388 P_Using_Cash_Basis IN VARCHAR2,
1389 P_Calling_Sequence IN VARCHAR2)
1390 RETURN BOOLEAN IS
1391
1392 debug_info VARCHAR2(200);
1393 current_calling_sequence VARCHAR2(2000);
1394 temp number;
1395
1396 BEGIN
1397 -- Update the calling sequence
1398 --
1399 current_calling_sequence :=
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)
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'
1415 AND D.accrual_posted_flag = DECODE(p_using_accrual_basis, 'Y','Y',
1416 D.accrual_posted_flag)
1417 AND D.cash_posted_flag = DECODE(p_using_cash_basis, 'Y','Y',
1418 D.cash_posted_flag)
1419 AND D.last_update_date <= g_activity_date
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
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';
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;
1444
1445 EXCEPTION
1446 WHEN OTHERS THEN
1447 IF (SQLCODE < 0 ) then
1448 Print(SQLERRM);
1449 END IF;
1450 RETURN(FALSE);
1451 END Seed_invoices;
1452
1453
1454 ------------------------------------------------------------------
1455 -- Procedure: Select_Seed_Vendors
1456 --
1457 ------------------------------------------------------------------
1458 FUNCTION Seed_Vendors
1459 (P_Purge_Name IN VARCHAR2,
1460 P_Calling_Sequence IN VARCHAR2)
1461 RETURN BOOLEAN IS
1462
1463 debug_info VARCHAR2(200);
1464 current_calling_sequence VARCHAR2(2000);
1465
1466 BEGIN
1467 -- Update the calling sequence
1468 --
1469 current_calling_sequence :=
1470 'Seed_Vendors<-'||P_calling_sequence;
1471
1472 debug_info := 'Starting Seed_Vendors';
1473 IF g_debug_switch in ('y','Y') THEN
1474 Print('(Seed_Vendors)' ||debug_info);
1475 END IF;
1476
1477 --
1478 insert into po_purge_vendor_list
1479 (vendor_id,
1480 purge_name,
1481 double_check_flag)
1482 select vnd.vendor_id,
1483 p_purge_name,
1484 'Y'
1485 from ap_suppliers vnd
1486 where vnd.end_date_active <= g_activity_date
1487 and not exists (select 'vnd.vendor is a parent of
1488 another vendor'
1489 from ap_suppliers v
1490 where v.parent_vendor_id =
1491 vnd.vendor_id)
1492 --Bug 2653578
1493 and PO_THIRD_PARTY_STOCK_GRP.validate_supplier_purge(
1494 vnd.vendor_id) = 'TRUE';
1495
1496 -- test vendors
1497 if g_payables_status = 'Y' then
1498 if g_assets_status = 'Y' then
1499 debug_info := 'test_fa_vendors';
1500 IF g_debug_switch in ('y','Y') THEN
1501 Print('(Seed_Vendors)' ||debug_info);
1502 END IF;
1503
1504
1505 -- test fa vendors
1506 delete from po_purge_vendor_list pvl
1507 where exists
1508 (select null
1509 from fa_mass_additions fma
1510 where fma.po_vendor_id = pvl.vendor_id)
1511 or exists
1512 (select null
1513 from fa_asset_invoices fai
1514 where fai.po_vendor_id = pvl.vendor_id);
1515 end if;
1516
1517 debug_info := 'test_ap_vendors';
1518 IF g_debug_switch in ('y','Y') THEN
1519 Print('(Seed_Vendors)' ||debug_info);
1520 END IF;
1521
1522
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,
1532 ap_supplier_sites_all pvs
1533 where asi.vendor_site_id =
1534 pvs.vendor_site_id
1535 and pvs.vendor_id = pvl.vendor_id)
1536 or exists
1537 (select null
1538 from ap_recurring_payments_all arp
1539 where arp.vendor_id = pvl.vendor_id);
1540 end if;
1541
1542 if g_purchasing_status = 'Y' then
1543
1544 debug_info := 'test_po_vendors';
1545 IF g_debug_switch in ('y','Y') THEN
1546 Print('(Seed_Vendors)' ||debug_info);
1547 END IF;
1548
1549
1550 -- test_po_vendors
1551 delete from po_purge_vendor_list pvl
1552 where exists (select null
1553 from po_headers_all ph
1554 where ph.vendor_id =
1555 pvl.vendor_id)
1556 or exists (select null
1557 from rcv_shipment_headers
1558 rcvsh
1559 where rcvsh.vendor_id =
1560 pvl.vendor_id)
1561 or exists (select null
1562 from po_rfq_vendors rfq
1563 where rfq.vendor_id =
1564 pvl.vendor_id);
1565 end if;
1566
1567 COMMIT;
1568 debug_info := 'End Seed_vendors';
1569 IF g_debug_switch in ('y','Y') THEN
1570 Print('(Seed_Vendors)' ||debug_info);
1571 END IF;
1572
1573 RETURN (TRUE);
1574
1575 RETURN NULL; EXCEPTION
1576 WHEN OTHERS THEN
1577 IF (SQLCODE < 0 ) THEN
1578 Print(SQLERRM);
1579 END IF;
1580 RETURN (FALSE);
1581 END Seed_vendors;
1582
1583
1584 ------------------------------------------------------------------
1585 -- Procedure: Test_Vendors
1586 --
1587 ------------------------------------------------------------------
1588 FUNCTION Test_Vendors
1589 (P_calling_sequence IN VARCHAR2)
1590 RETURN BOOLEAN IS
1591
1592 debug_info VARCHAR2(200);
1593 current_calling_sequence VARCHAR2(2000);
1594
1595 BEGIN
1596 -- Update the calling sequence
1597 --
1598 current_calling_sequence :=
1599 'Test_vendors<-'||P_calling_sequence;
1600
1601 debug_info := 'Starting Test_vendors';
1602 IF g_debug_switch in ('y','Y') THEN
1603 Print('(Test_Vendors)' ||debug_info);
1604 END IF;
1605
1606
1607 if (g_payables_status = 'Y') then
1608
1609 if (g_assets_status = 'Y') then
1610
1611 debug_info := 'test_fa_vendors';
1612 IF g_debug_switch in ('y','Y') THEN
1613 Print('(Test_Vendors)' ||debug_info);
1614 END IF;
1615
1616 -- test_fa_vendors
1617 delete from po_purge_vendor_list pvl
1618 where exists (select null
1619 from fa_mass_additions fma
1620 where fma.po_vendor_id =
1621 pvl.vendor_id)
1622 or exists (select null
1623 from fa_asset_invoices fai
1624 where fai.po_vendor_id =
1625 pvl.vendor_id);
1626 end if;
1627
1628 -- test_ap_vendors
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
1638 where asi.vendor_site_id =
1639 pvs.vendor_site_id
1640 and pvs.vendor_id = pvl.vendor_id)
1641 or exists (select null
1642 from ap_recurring_payments_all arp
1643 where arp.vendor_id = pvl.vendor_id);
1644 end if;
1645
1646 -- check_po_status
1647
1648 if (g_purchasing_status = 'Y') then
1649
1650 debug_info := 'check_po_status';
1651 IF g_debug_switch in ('y','Y') THEN
1652 Print('(Test_Vendors)' ||debug_info);
1653 END IF;
1654
1655 delete from po_purge_vendor_list pvl
1656 where exists (select null
1657 from po_headers_all ph
1658 where ph.vendor_id = pvl.vendor_id)
1659 or exists (select null
1660 from rcv_shipment_headers rcvsh
1661 where rcvsh.vendor_id = pvl.vendor_id)
1662 or exists (select null
1663 from po_rfq_vendors rfq
1664 where rfq.vendor_id = pvl.vendor_id)
1665 or exists (select null
1666 from rcv_headers_interface rhi
1667 where rhi.vendor_id = pvl.vendor_id)
1668 or exists (select null
1669 from rcv_transactions_interface rti
1670 where rti.vendor_id = pvl.vendor_id);
1671
1672
1673 end if;
1674
1675 -- check_vendors_in_chv
1676
1677 if (g_chv_status = 'Y') then
1678 debug_info := 'Check_chv_status';
1679 IF g_debug_switch in ('y','Y') THEN
1680 Print('(Test_Vendors)' ||debug_info);
1681 END IF;
1682
1683 delete from po_purge_vendor_list pvl
1684 where exists (select null
1685 from chv_schedule_headers csh
1686 where csh.vendor_id = pvl.vendor_id);
1687 end if;
1688
1689 -- check_vendors_in_edi
1690
1691 if (g_edi_status = 'Y') then
1692 debug_info := 'Check_edi_status';
1693 IF g_debug_switch in ('y','Y') THEN
1694 Print('(Test_Vendors)' ||debug_info);
1695 END IF;
1696
1697 delete from po_purge_vendor_list pvl
1698 where exists (select null
1699 from ece_tp_details etd,
1700 ap_supplier_sites_all pvs
1701 where etd.tp_header_id = pvs.tp_header_id
1702 and pvs.vendor_id = pvl.vendor_id
1703 and etd.last_update_date > g_activity_date);
1704 --Bug 1781451 Remove from purge list all vendors with last_update_date
1705 --greater than last activity date in Concurrent request parameters
1706 -- and etd.last_update_date <= g_activity_date);
1707
1708 end if;
1709
1710
1711 -- check_vendors_in_sourcing_rules
1712
1713 if (g_mrp_status = 'Y') then
1714 debug_info := 'Check_vendors_in_sourcing_rules';
1715 IF g_debug_switch in ('y','Y') THEN
1716 Print('(Test_Vendors)' ||debug_info);
1717 END IF;
1718
1719 --1700943, removing the code below that checks for activity
1720 --dates of the sourcing rules. we should not purge the
1721 --vendor if it is tied to an inactive rule
1722
1723 delete from po_purge_vendor_list pvl
1724 where exists (select null
1725 from mrp_sr_source_org msso
1726 where msso.vendor_id = pvl.vendor_id);
1727
1728 end if;
1729
1730 COMMIT;
1731
1732 debug_info := 'End Test_Vendors';
1733 IF g_debug_switch in ('y','Y') THEN
1734 Print('(Test_Vendors)' ||debug_info);
1735 END IF;
1736
1737 RETURN (TRUE);
1738
1739 RETURN NULL;
1740
1741 EXCEPTION
1742 WHEN OTHERS then
1743 IF (SQLCODE < 0 ) then
1744 Print(SQLERRM);
1745 END IF;
1746 RETURN (FALSE);
1747
1748 END Test_Vendors;
1749
1750
1751 ------------------------------------------------------------------
1752 -- Procedure: Seed_Purge_Tables
1753 -- This procedure is used to select the data to be purged and
1754 -- insert into purge tables.
1755 ------------------------------------------------------------------
1756 FUNCTION Seed_purge_tables
1757 (P_Category IN VARCHAR2,
1758 P_Purge_Name IN VARCHAR2,
1759 P_Activity_Date IN DATE,
1760 P_Organization_ID IN NUMBER,
1761 P_PA_Status IN VARCHAR2,
1762 P_Purchasing_Status IN VARCHAR2,
1763 P_Payables_Status IN VARCHAR2,
1764 P_Assets_Status IN VARCHAR2,
1765 P_Chv_Status IN VARCHAR2,
1766 P_EDI_Status IN VARCHAR2,
1767 P_MRP_Status IN VARCHAR2,
1768 P_Debug_Switch IN VARCHAR2,
1769 P_calling_sequence IN VARCHAR2)
1770 RETURN BOOLEAN IS
1771
1772 debug_info VARCHAR2(200);
1773 current_calling_sequence VARCHAR2(2000);
1774 l_status VARCHAR2(30);
1775
1776 l_recon_acctg_flag VARCHAR2(1);
1777 l_using_accrual_basis VARCHAR2(1);
1778 l_using_cash_basis VARCHAR2(1);
1779
1780 l_po_return_status VARCHAR2(1);
1781 l_po_msg VARCHAR2(2000);
1782 l_po_records_filtered VARCHAR2(1);
1783
1784 BEGIN
1785
1786 g_debug_switch := p_debug_switch;
1787
1788 g_activity_date := P_Activity_Date;
1789 g_organization_id := P_Organization_ID;
1790 g_category := P_Category;
1791 g_pa_status := P_PA_Status;
1792 g_purchasing_Status := P_Purchasing_Status;
1793 g_payables_status := P_Payables_Status;
1794 g_assets_status := P_Assets_Status;
1795 g_chv_status := P_Chv_Status;
1796 g_edi_status := P_EDI_Status;
1797 g_mrp_status := P_MRP_Status;
1798
1799 -- Update the calling sequence
1800 --
1801 current_calling_sequence :=
1802 'Seed_purge_tables<-'||P_calling_sequence;
1803
1804
1805 debug_info := 'Get Accounting Methods';
1806 IF g_debug_switch in ('y','Y') THEN
1807 Print('(Seed_purge_tables)'||debug_info);
1808 END IF;
1809
1810 IF (Get_Accounting_Method(
1811 l_recon_acctg_flag,
1812 l_using_accrual_basis,
1813 l_using_cash_basis,
1814 'Get Accounting Method') <> TRUE) THEN
1815 Print('Seed_simple_invoices failed');
1816 Return(FALSE);
1817 END IF;
1818
1819
1820 debug_info := 'Starting Seed_purge_tables';
1821 IF g_debug_switch in ('y','Y') THEN
1822 Print('(Seed_purge_tables)'||debug_info);
1823 END IF;
1824
1825 -- Simple Invoices
1826 if (p_category = 'SIMPLE INVOICES') then
1827
1828 debug_info := 'Simple Invoices';
1829 IF g_debug_switch in ('y','Y') THEN
1830 Print('(Seed_purge_tables)' ||debug_info);
1831 END IF;
1832
1833
1834 if (Seed_Invoices(
1835 p_purge_name,
1836 l_using_accrual_basis,
1837 l_using_cash_basis,
1838 'Seed_purge_tables') <> TRUE) then
1839 Print('Seed_simple_invoices failed');
1840 Return(FALSE);
1841 end if;
1842
1843 if (Do_Independent_Inv_Checks(
1844 l_using_accrual_basis,
1845 l_using_cash_basis,
1846 l_recon_acctg_flag,
1847 'Seed_purge_tables') <> TRUE) then
1848 Print('Do_independent_inv_checks failed');
1849 Return(FALSE);
1850 end if;
1851
1852 if (Do_Dependent_inv_checks('Seed_purge_tables')<>
1853 TRUE) then
1854 Print('Do_dependent_inv_checks failed');
1855 Return(FALSE);
1856 end if;
1857
1858 elsif (p_category IN ('SIMPLE REQUISITIONS', 'SIMPLE POS')) then
1859
1860 debug_info := 'Call PO Purge API';
1861 IF g_debug_switch in ('y','Y') THEN
1862 Print('(Seed_purge_tables)' ||debug_info);
1863 END IF;
1864
1865 PO_AP_PURGE_GRP.seed_records
1866 ( p_api_version => 1.0,
1867 p_init_msg_list => 'T',
1868 p_commit => 'F',
1869 x_return_status => l_po_return_status,
1870 x_msg_data => l_po_msg,
1871 p_purge_name => p_purge_name,
1872 p_purge_category => p_category,
1873 p_last_activity_date => p_activity_date
1874 );
1875
1876 IF (l_po_return_status <> 'S') THEN
1877 Print(l_po_msg);
1878 RETURN FALSE;
1879 END IF;
1880
1881 PO_AP_PURGE_GRP.filter_records
1882 ( p_api_version => 1.0,
1883 p_init_msg_list => 'T',
1884 p_commit => 'F',
1885 x_return_status => l_po_return_status,
1886 x_msg_data => l_po_msg,
1887 p_purge_status => 'INITIATING',
1888 p_purge_name => p_purge_name,
1889 p_purge_category => p_category,
1890 p_action => NULL,
1891 x_po_records_filtered => l_po_records_filtered
1892 );
1893
1894 IF (l_po_return_status <> 'S') THEN
1895 Print(l_po_msg);
1896 RETURN FALSE;
1897 END IF;
1898
1899 elsif (p_category = 'MATCHED POS AND INVOICES') then
1900 debug_info := 'Invoices';
1901 IF g_debug_switch in ('y','Y') THEN
1902 Print('(Seed_purge_tables)' ||debug_info);
1903 END IF;
1904
1905 if (Seed_Invoices(
1906 p_purge_name,
1907 l_using_accrual_basis,
1908 l_using_cash_basis,
1909 'Seed_purge_tables') <> TRUE) then
1910 Print('Seed_invoices failed');
1911 Return(FALSE);
1912 end if;
1913
1914 debug_info := 'Purchase Orders';
1915 IF g_debug_switch in ('y','Y') THEN
1916 Print('(Seed_purge_tables)' ||debug_info);
1917 END IF;
1918
1919
1920 PO_AP_PURGE_GRP.seed_records
1921 ( p_api_version => 1.0,
1922 p_init_msg_list => 'T',
1923 p_commit => 'F',
1924 x_return_status => l_po_return_status,
1925 x_msg_data => l_po_msg,
1926 p_purge_name => p_purge_name,
1927 p_purge_category => p_category,
1928 p_last_activity_date => p_activity_date
1929 );
1930
1931 IF (l_po_return_status <> 'S') THEN
1932 Print(l_po_msg);
1933 RETURN FALSE;
1934 END IF;
1935
1936
1937 PO_AP_PURGE_GRP.filter_records
1938 ( p_api_version => 1.0,
1939 p_init_msg_list => 'T',
1940 p_commit => 'F',
1941 x_return_status => l_po_return_status,
1942 x_msg_data => l_po_msg,
1943 p_purge_status => 'INITIATING',
1944 p_purge_name => p_purge_name,
1945 p_purge_category => p_category,
1946 p_action => 'FILTER REF PO AND REQ',
1947 x_po_records_filtered => l_po_records_filtered
1948 );
1949
1950 IF (l_po_return_status <> 'S') THEN
1951 Print(l_po_msg);
1952 RETURN FALSE;
1953 END IF;
1954
1955 if (Do_Independent_Inv_Checks(
1956 l_using_accrual_basis,
1957 l_using_cash_basis,
1958 l_recon_acctg_flag,
1959 'Seed_purge_tables') <> TRUE) then
1960 Print('Do_independent_inv_checks failed');
1961 Return(FALSE);
1962 end if;
1963
1964 if (Do_Dependent_Inv_Checks('Seed_purge_tables') <> TRUE) then
1965 Print('Do_dependent_inv_checks failed');
1966 Return(FALSE);
1967 end if;
1968
1969 debug_info := 'Matching POs to Invoices';
1970 IF g_debug_switch in ('y','Y') THEN
1971 Print('(Seed_purge_tables)' ||debug_info);
1972 END IF;
1973
1974
1975 if (Match_Pos_To_Invoices_ctrl(
1976 P_Purge_Name,
1977 'INITIATING',
1978 'Seed_purge_tables') <> TRUE) then
1979 Print('Match_pos_to_Invoices_ctrl failed');
1980 Return(FALSE);
1981 end if;
1982
1983 elsif (p_category = 'VENDORS') then
1984
1985 debug_info := 'Vendors';
1986 IF g_debug_switch in ('y','Y') THEN
1987 Print('(Seed_purge_tables)' ||debug_info);
1988 END IF;
1989
1990 if (Seed_Vendors(
1991 P_Purge_Name,
1992 'Seed_purge_tables') <> TRUE) then
1993 Print('Seed_Vendors failed');
1994 Return(FALSE);
1995 end if;
1996
1997 if (Test_Vendors('Seed_purge_tables') <> TRUE) then
1998 Print('Test_Vendors failed');
1999 Return(FALSE);
2000 end if;
2001
2002 elsif (p_category = 'SCHEDULES BY ORGANIZATION') then
2003
2004 debug_info := 'Schedules by Org';
2005 IF g_debug_switch in ('y','Y') THEN
2006 Print('(Seed_purge_tables)' ||debug_info);
2007 END IF;
2008
2009 if (Seed_Chv_By_Org(
2010 p_purge_name,
2011 'Seed_purge_tables') <> TRUE) then
2012 Print('Seed_chv_by_org failed');
2013 Return(FALSE);
2014 end if;
2015
2016 if (Check_Chv_In_Cum('Seed_purge_tables') <> TRUE) then
2017 Print('check_chv_in_cum failed');
2018 Return(FALSE);
2019 end if;
2020
2021 if (Check_Chv_In_Edi('Seed_purge_tables') <> TRUE) then
2022 Print('check_chv_in_edi failed');
2023 Return(FALSE);
2024 end if;
2025
2026 elsif (p_category = 'SCHEDULES BY CUM PERIODS') then
2027
2028 debug_info := 'Schedules by CUM Periods';
2029 IF g_debug_switch in ('y','Y') THEN
2030 Print('(Seed_purge_tables)' ||debug_info);
2031 END IF;
2032
2033 if (Seed_Chv_By_Cum(
2034 p_purge_name,
2035 'Seed_purge_tables') <> TRUE) then
2036 Print('Seed_chv_by_cum failed');
2037 Return(FALSE);
2038 end if;
2039
2040 else
2041
2042 debug_info := 'An invalid purge category was entered.';
2043 Print('(Seed_purge_tables)'||debug_info);
2044 Print(' Valid Categories are : SIMPLE INVOICES, SIMPLE REQUISITIONS ,');
2045 Print('SIMPLE POS, MATCHED POS AND INVOICES ,');
2046 Print('SCHEDULES BY ORGANIZATION and SCHEDULES BY CUM PERIODS');
2047
2048 l_status := 'COMPLETED-ABORTED';
2049
2050 if (Set_Purge_Status(l_status,
2051 p_purge_name,
2052 p_debug_switch,
2053 'Seed_Purge_Tables') <> TRUE) then
2054 Print(' Set_Purge_Status failed.');
2055 Return(FALSE);
2056 end if;
2057
2058 RETURN(TRUE);
2059 end if;
2060
2061 RETURN NULL; EXCEPTION
2062 WHEN OTHERS then
2063 IF (SQLCODE < 0 ) then
2064 Print(SQLERRM);
2065 END IF;
2066 RETURN (FALSE);
2067
2068 END Seed_purge_tables;
2069
2070
2071 /*==========================================================================
2072 Function: Invoice_Summary
2073
2074 *==========================================================================*/
2075 FUNCTION Invoice_Summary( p_inv_lower_limit IN NUMBER,
2076 p_inv_upper_limit IN NUMBER,
2077 p_purge_name IN VARCHAR2,
2078 p_calling_sequence IN VARCHAR2) RETURN BOOLEAN IS
2079
2080 debug_info VARCHAR2(200);
2081 current_calling_sequence VARCHAR2(2000);
2082
2083 range_low NUMBER;
2084 range_high NUMBER;
2085 range_inserted VARCHAR2(1);
2086 range_size NUMBER:=10000;
2087
2088 BEGIN
2089 -- Update the calling sequence
2090 --
2091 current_calling_sequence := 'Invoice_Summary<-'||P_calling_sequence;
2092 --
2093 debug_info := 'Starting Invoice_Summary';
2094 IF g_debug_switch in ('y','Y') THEN
2095 Print('(Invoice_Summary)'||debug_info);
2096 END IF;
2097
2098
2099 /**** Invoice Loop ****/
2100 range_size := g_range_size;
2101 range_high := 0;
2102 range_low := p_inv_lower_limit;
2103 range_high := range_low + range_size;
2104
2105 LOOP
2106 range_inserted := 'N';
2107
2108 -- Check_invoice_Summary
2109 BEGIN
2110 select 'Y'
2111 into range_inserted
2112 from sys.dual
2113 where exists (select null
2114 from ap_history_invoices
2115 where purge_name = p_purge_name
2116 and invoice_id between range_low and range_high);
2117
2118 EXCEPTION
2119 WHEN NO_DATA_FOUND THEN
2120 range_inserted := 'N';
2121 END;
2122
2123 if (range_inserted <> 'Y') then
2124 --
2125 debug_info := 'Summerizing sub-group from Oracle Purchasing -- Invoices';
2126 IF g_debug_switch in ('y','Y') THEN
2127 Print('(Invoice_Summary)'||debug_info);
2128 END IF;
2129 -- summarize_invoices
2130 -- bug5487843, added org_id and changed to _ALL
2131 INSERT INTO ap_history_invoices_all
2132 (invoice_id, vendor_id, vendor_site_code, invoice_num, invoice_date,
2133 invoice_amount, batch_name, purge_name, doc_sequence_id,
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
2143 WHERE PL.double_check_flag = 'Y'
2144 AND PL.invoice_id BETWEEN range_low AND
2145 range_high);
2146
2147 --
2148 debug_info := ' -- Checks';
2149 IF g_debug_switch in ('y','Y') THEN
2150 Print('(Invoice_Summary)'||debug_info);
2151 END IF;
2152
2153 --5007666, added payment_id
2154 -- bug5487843, added org_id and changed to _ALL
2155 -- summarize_checks
2156 INSERT INTO ap_history_checks_all
2157 (check_id, bank_account_id, check_number, check_date, amount,
2158 currency_code, void_flag, purge_name, doc_sequence_id,
2159 doc_sequence_value, payment_id,org_id)
2160 SELECT
2161 ac.check_id, ac.bank_account_id, ac.check_number, ac.check_date,
2162 ac.amount, ac.currency_code, DECODE(void_date, null, null, 'Y'),
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
2172 AND NOT EXISTS (SELECT null
2173 FROM ap_history_checks_all hc
2174 WHERE hc.check_id = AC.check_id)
2175 GROUP BY ac.check_id, ac.bank_account_id, ac.check_number,
2176 ac.check_date, ac.amount, ac.currency_code,
2177 DECODE(void_date, null, null, 'Y'), purge_name,
2178 ac.doc_sequence_id, ac.doc_sequence_value, ac.payment_id,
2179 ac.org_id; --Bug 6277474 added the org_id in group by clause.
2180
2181 --
2182 debug_info := ' -- Invoices Payments';
2183 IF g_debug_switch in ('y','Y') THEN
2184 Print('(Invoice_Summary)'||debug_info);
2185 END IF;
2186
2187 -- summarize_invoice_payments
2188 -- bug5487843, added org_id and changed to _ALL
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,
2198 IP.org_id; --Bug 6277474 added the org_id in group by clause.
2199
2200 COMMIT;
2201 end if;
2202
2203 range_low := range_high + 1;
2204 range_high := range_high + range_size;
2205
2206 if (range_low > p_inv_upper_limit) then
2207 EXIT;
2208 end if;
2209 END LOOP;
2210 --
2211 debug_info := 'End Invoice_Summary';
2212 IF g_debug_switch in ('y','Y') THEN
2213 Print('(Invoice_Summary)'||debug_info);
2214 END IF;
2215
2216 RETURN (TRUE);
2217
2218 RETURN NULL; EXCEPTION
2219 WHEN OTHERS then
2220 IF (SQLCODE < 0 ) then
2221 Print(SQLERRM);
2222 END IF;
2223 RETURN (FALSE);
2224
2225 END Invoice_Summary;
2226
2227 /*==========================================================================
2228 Function: Vendor_Summary
2229
2230 *==========================================================================*/
2231 FUNCTION Vendor_Summary( p_purge_name IN VARCHAR2,
2232 p_calling_sequence IN VARCHAR2) RETURN BOOLEAN IS
2233
2234 debug_info VARCHAR2(200);
2235 current_calling_sequence VARCHAR2(2000);
2236
2237 range_low NUMBER;
2238 range_high NUMBER;
2239 range_inserted VARCHAR2(1);
2240 range_size NUMBER:=10000;
2241
2242 BEGIN
2243 -- Update the calling sequence
2244 --
2245 current_calling_sequence := 'Vendor_Summary<-'||P_calling_sequence;
2246 --
2247 debug_info := 'Starting Vendor_Summary';
2248 IF g_debug_switch in ('y','Y') THEN
2249 Print('(Vendor_Summary)'||debug_info);
2250 END IF;
2251
2252
2253 range_inserted := 'N';
2254
2255 -- Check_vendor_Summary
2256
2257 BEGIN
2258
2259 select 'Y'
2260 into range_inserted
2261 from sys.dual
2262 where exists (select null
2263 from po_history_vendors vnd
2264 where vnd.purge_name = p_purge_name);
2265
2266 EXCEPTION
2267 WHEN NO_DATA_FOUND THEN
2268 range_inserted := 'N';
2269 END;
2270
2271 if (range_inserted <> 'Y') then
2272 --
2273 debug_info := 'Vendors';
2274 IF g_debug_switch in ('y','Y') THEN
2275 Print('(Vendor_Summary)'||debug_info);
2276 END IF;
2277
2278 -- summarize_Vendor
2279 insert into po_history_vendors
2280 (vendor_id,
2281 vendor_name,
2282 segment1,
2283 vendor_type_lookup_code,
2284 purge_name)
2285 select vnd.vendor_id,
2286 vnd.vendor_name,
2287 vnd.segment1,
2288 vnd.vendor_type_lookup_code,
2289 p_purge_name
2290 from po_purge_vendor_list pvl,
2291 ap_suppliers vnd
2292 where pvl.vendor_id = vnd.vendor_id
2293 and pvl.double_check_flag = 'Y';
2294
2295 COMMIT;
2296 end if;
2297
2298 --
2299 debug_info := 'End Vendor_Summary';
2300 IF g_debug_switch in ('y','Y') THEN
2301 Print('(Vendor_Summary)'||debug_info);
2302 END IF;
2303
2304 RETURN (TRUE);
2305
2306 RETURN NULL; EXCEPTION
2307 WHEN OTHERS then
2308 IF (SQLCODE < 0 ) then
2309 Print(SQLERRM);
2310 END IF;
2311 RETURN (FALSE);
2312
2313 END Vendor_Summary;
2314
2315 /*==========================================================================
2316 Function: Schedule_Org_Summary
2317
2318 *==========================================================================*/
2319 FUNCTION Schedule_Org_Summary(
2320 p_chv_lower_limit IN NUMBER,
2321 p_chv_upper_limit IN NUMBER,
2322 p_purge_name IN VARCHAR2,
2323 p_category IN VARCHAR2,
2324 p_calling_sequence IN VARCHAR2) RETURN BOOLEAN IS
2325
2326 debug_info VARCHAR2(200);
2327 current_calling_sequence VARCHAR2(2000);
2328 range_low NUMBER;
2329 range_high NUMBER;
2330 range_inserted VARCHAR2(1);
2331 range_size NUMBER;
2332
2333 BEGIN
2334 -- Update the calling sequence
2335 --
2336 current_calling_sequence := 'Schedule_Org_Summary<-'||P_calling_sequence;
2337 --
2338 debug_info := 'Starting Schedule_Org_Summary';
2339 IF g_debug_switch in ('y','Y') THEN
2340 Print('(Schedule_Org_Summary)'||debug_info);
2341 END IF;
2342
2343
2344 /**** Schedule Loop ****/
2345 range_size := g_range_size;
2346 range_high := 0;
2347 range_low := p_chv_lower_limit;
2348 range_high := range_low + range_size;
2349 Print('(Schedule_Org_Summary-Range Size) '||to_char (range_size));
2350 Print('(Schedule_Org_Summary-Range Low) '||to_char (range_low));
2351 Print('(Schedule_Org_Summary-Range High) '||to_char (range_high));
2352
2353 LOOP
2354 range_inserted := 'N';
2355
2356 -- Check_Chv_Summary
2357 select MAX('Y')
2358 into range_inserted
2359 from sys.dual
2360 where exists (select null
2361 from chv_schedule_items csi,
2362 chv_schedule_headers csh,
2363 chv_history_schedules chs
2364 where csi.item_id = chs.item_id
2365 and csi.schedule_id = chs.schedule_id
2366 and csh.schedule_id = chs.schedule_id
2367 and csh.vendor_id = chs.vendor_id
2368 and csh.vendor_site_id = chs.vendor_site_id
2369 and csi.organization_id = chs.organization_id
2370 and chs.purge_name = p_purge_name
2371 and csi.schedule_item_id between range_low
2372 and range_high);
2373
2374 Print('(Range Inserted) ' || range_inserted);
2375 if (NVL(range_inserted,'N') <> 'Y') then
2376 --
2377 debug_info := 'Summerizing sub-group from Oracle Supplier Scheduling';
2378 IF g_debug_switch in ('y','Y') THEN
2379 Print('(Schedule_Org_Summary)'||debug_info);
2380 END IF;
2381
2382 -- summarize_schedules_by_org
2383 insert into chv_history_schedules
2384 (schedule_id,
2385 vendor_id,
2386 vendor_site_id,
2387 schedule_type,
2388 schedule_subtype,
2389 schedule_horizon_start,
2390 bucket_pattern_id,
2391 creation_date,
2392 schedule_num,
2393 schedule_revision,
2394 schedule_status,
2395 item_id,
2396 organization_id,
2397 purge_name
2398 )
2399 select csh.schedule_id,
2400 csh.vendor_id,
2401 csh.vendor_site_id,
2402 csh.schedule_type,
2403 csh.schedule_subtype,
2404 csh.schedule_horizon_start,
2405 csh.bucket_pattern_id,
2406 csh.creation_date,
2407 csh.schedule_num,
2408 csh.schedule_revision,
2409 csh.schedule_status,
2410 csi.item_id,
2411 csi.organization_id,
2412 p_purge_name
2413 from chv_purge_schedule_list cpsl,
2414 chv_schedule_headers csh,
2415 chv_schedule_items csi
2416 where cpsl.schedule_item_id = csi.schedule_item_id
2417 and csi.schedule_id = csh.schedule_id
2418 and cpsl.double_check_flag = 'Y'
2419 and cpsl.schedule_item_id between range_low and range_high;
2420
2421 COMMIT;
2422 end if;
2423
2424 range_low := range_high + 1;
2425 range_high := range_high + range_size;
2426
2427 if (range_low >= p_chv_upper_limit) then
2428 EXIT;
2429 end if;
2430 END LOOP;
2431
2432 range_inserted := 'N';
2433
2434 -- Check_Chv_Summary_for_CUMs
2435 select MAX('Y')
2436 into range_inserted
2437 from sys.dual
2438 where exists (select null
2439 from chv_cum_periods ccp,
2440 chv_history_cum_periods chcp
2441 where ccp.cum_period_id = chcp.cum_period_id
2442 and chcp.purge_name = p_purge_name);
2443
2444 -- 1783982 fbreslin: Compare using :p_catagory rather than p_purge_name
2445
2446 if (p_category = 'SCHEDULES BY CUM PERIODS' AND
2447 NVL(range_inserted,'N') <> 'Y') then
2448 -- summarize_schedules_by_org
2449 insert into chv_history_cum_periods
2450 (cum_period_id,
2451 cum_period_name,
2452 cum_period_start_date,
2453 cum_period_end_date,
2454 creation_date,
2455 purge_name
2456 )
2457 select ccp.cum_period_id,
2458 ccp.cum_period_name,
2459 ccp.cum_period_start_date,
2460 ccp.cum_period_end_date,
2461 ccp.creation_date,
2462 p_purge_name
2463 from chv_purge_cum_list cpcl,
2464 chv_cum_periods ccp
2465 where cpcl.cum_period_id = ccp.cum_period_id
2466 and cpcl.double_check_flag = 'Y';
2467 COMMIT;
2468 end if;
2469 --
2470 debug_info := 'End schedule_org_summary';
2471 IF g_debug_switch in ('y','Y') THEN
2472 Print('(Schedule_Org_Summary)'||debug_info);
2473 END IF;
2474
2475 RETURN (TRUE);
2476
2477 RETURN NULL; EXCEPTION
2478 WHEN OTHERS then
2479 IF (SQLCODE < 0 ) then
2480 Print(SQLERRM);
2481 END IF;
2482 RETURN (FALSE);
2483
2484 END schedule_org_summary;
2485
2486
2487 /*==========================================================================
2488 Function: Get_Ranges
2489
2490 *==========================================================================*/
2491 FUNCTION Get_Ranges( p_inv_lower_limit OUT NOCOPY NUMBER,
2492 p_inv_upper_limit OUT NOCOPY NUMBER,
2493 p_req_lower_limit OUT NOCOPY NUMBER,
2494 p_req_upper_limit OUT NOCOPY NUMBER,
2495 p_po_lower_limit OUT NOCOPY NUMBER,
2496 p_po_upper_limit OUT NOCOPY NUMBER,
2497 p_chv_lower_limit OUT NOCOPY NUMBER,
2498 p_chv_upper_limit OUT NOCOPY NUMBER,
2499 p_calling_sequence IN VARCHAR2) RETURN BOOLEAN IS
2500
2501 debug_info VARCHAR2(200);
2502 current_calling_sequence VARCHAR2(2000);
2503
2504 BEGIN
2505 -- Update the calling sequence
2506 --
2507 current_calling_sequence := 'Get_Ranges<-'||P_calling_sequence;
2508 --
2509 debug_info := 'Starting Get_Ranges';
2510 IF g_debug_switch in ('y','Y') THEN
2511 Print('(Get_Ranges)'||debug_info);
2512 END IF;
2513
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),
2523 nvl(max(po_header_id),-1)
2524 into p_po_lower_limit, p_po_upper_limit
2525 from po_purge_po_list
2526 where double_check_flag = 'Y';
2527
2528 -- get_req_range
2529 select nvl(min(requisition_header_id),-1),
2530 nvl(max(requisition_header_id),-1)
2531 into p_req_lower_limit, p_req_upper_limit
2532 from po_purge_req_list
2533 where double_check_flag = 'Y';
2534
2535 -- get_chv_range
2536 select nvl(min(schedule_item_id),-1),
2537 nvl(max(schedule_item_id),-1)
2538 into p_chv_lower_limit, p_chv_upper_limit
2539 from chv_purge_schedule_list
2540 where double_check_flag = 'Y';
2541
2542 --
2543 debug_info := 'End Get_Ranges';
2544 IF g_debug_switch in ('y','Y') THEN
2545 Print('(Get_Ranges)'||debug_info);
2546 END IF;
2547
2548 RETURN (TRUE);
2549
2550 RETURN NULL; EXCEPTION
2551 WHEN OTHERS then
2552 IF (SQLCODE < 0 ) then
2553 Print(SQLERRM);
2554 END IF;
2555 RETURN (FALSE);
2556
2557 END Get_Ranges;
2558
2559 /*==========================================================================
2560 Function: Create_Summary_Records
2561
2562 *==========================================================================*/
2563 FUNCTION Create_Summary_Records(p_purge_name IN VARCHAR2,
2564 p_category IN VARCHAR2,
2565 p_range_size IN NUMBER,
2566 p_debug_switch IN VARCHAR2,
2567 p_calling_sequence IN VARCHAR2) RETURN BOOLEAN IS
2568
2569 debug_info VARCHAR2(200);
2570 current_calling_sequence VARCHAR2(2000);
2571
2572 inv_lower_limit NUMBER;
2573 inv_upper_limit NUMBER;
2574 req_lower_limit NUMBER;
2575 req_upper_limit NUMBER;
2576 po_lower_limit NUMBER;
2577 po_upper_limit NUMBER;
2578 chv_lower_limit NUMBER;
2579 chv_upper_limit NUMBER;
2580
2581 l_po_return_status VARCHAR2(1);
2582 l_po_msg VARCHAR2(2000);
2583
2584
2585 BEGIN
2586 -- Update the calling sequence
2587 --
2588 current_calling_sequence := 'Create_Summary_Records<-'||P_calling_sequence;
2589 --
2590 debug_info := 'Starting Create_Summary_Records';
2591 IF g_debug_switch in ('y','Y') THEN
2592 Print('(Create_Summary_Records)'||debug_info);
2593 END IF;
2594
2595 g_debug_switch := p_debug_switch;
2596 g_range_size := p_range_size;
2597
2598 --
2599 if (Get_Ranges( inv_lower_limit,
2600 inv_upper_limit,
2601 req_lower_limit,
2602 req_upper_limit,
2603 po_lower_limit,
2604 po_upper_limit,
2605 chv_lower_limit,
2606 chv_upper_limit,
2607 'Create_Summary_Records') <> TRUE) then
2608 Print('Get_Ranges failed.');
2609 return(FALSE);
2610 end if;
2611
2612 --
2613 debug_info := 'Inserting summary records into history tables';
2614 IF g_debug_switch in ('y','Y') THEN
2615 Print('(Create_Summary_Records)'||debug_info);
2616 END IF;
2617
2618 if (p_category in ( 'SIMPLE INVOICES', 'MATCHED POS AND INVOICES')) then
2619
2620 if (invoice_summary(inv_lower_limit,
2621 inv_upper_limit,
2622 p_purge_name,
2623 'Create_Summary_Records') <> TRUE) then
2624 Print('Invoice_Summary failed.');
2625 return(FALSE);
2626 end if;
2627
2628 if (p_category = 'MATCHED POS AND INVOICES') then
2629
2630 PO_AP_PURGE_GRP.summarize_records
2631 ( p_api_version => 1.0,
2632 p_init_msg_list => 'T',
2633 p_commit => 'T',
2634 x_return_status => l_po_return_status,
2635 x_msg_data => l_po_msg,
2636 p_purge_name => p_purge_name,
2637 p_purge_category => p_category,
2638 p_range_size => p_range_size
2639 );
2640
2641 IF (l_po_return_status <> 'S') THEN
2642 Print(l_po_msg);
2643 RETURN FALSE;
2644 END IF;
2645 end if;
2646
2647 elsif (p_category IN ('SIMPLE REQUISITIONS', 'SIMPLE POS')) THEN
2648
2649 PO_AP_PURGE_GRP.summarize_records
2650 ( p_api_version => 1.0,
2651 p_init_msg_list => 'T',
2652 p_commit => 'T',
2653 x_return_status => l_po_return_status,
2654 x_msg_data => l_po_msg,
2655 p_purge_name => p_purge_name,
2656 p_purge_category => p_category,
2657 p_range_size => p_range_size
2658 );
2659
2660 IF (l_po_return_status <> 'S') THEN
2661 Print(l_po_msg);
2662 RETURN FALSE;
2663 END IF;
2664 --
2665
2666 elsif (p_category = 'VENDORS') then
2667
2668 if (vendor_summary(p_purge_name,
2669 'Create_Summary_Records') <> TRUE) then
2670 Print('Vendor_Summary failed.');
2671 return(FALSE);
2672 end if;
2673 elsif (p_category IN ('SCHEDULES BY ORGANIZATION' ,
2674 'SCHEDULES BY CUM PERIODS')) then
2675
2676 if (schedule_org_summary(chv_lower_limit,
2677 chv_upper_limit,
2678 p_purge_name,
2679 p_category,
2680 'Create_Summary_Records') <> TRUE) then
2681 Print('Schedule_Org_Summary failed.');
2682 return(FALSE);
2683 end if;
2684
2685 end if;
2686 --
2687 debug_info := 'End Create_Summary_Records';
2688 IF g_debug_switch in ('y','Y') THEN
2689 Print('(Create_Summary_Records)'||debug_info);
2690 END IF;
2691
2692
2693 RETURN (TRUE);
2694
2695 RETURN NULL; EXCEPTION
2696 WHEN OTHERS then
2697 IF (SQLCODE < 0 ) then
2698 Print(SQLERRM);
2699 END IF;
2700 RETURN (FALSE);
2701
2702 END Create_Summary_Records;
2703
2704
2705
2706 /*==========================================================================
2707 Function: Retest_Invoice_Independents
2708
2709 *==========================================================================*/
2710 FUNCTION Retest_Invoice_Independents(
2711 P_Calling_Sequence VARCHAR2) RETURN BOOLEAN IS
2712
2713 debug_info VARCHAR2(200);
2714 current_calling_sequence VARCHAR2(2000);
2715
2716 BEGIN
2717 -- Update the calling sequence
2718 --
2719 current_calling_sequence := 'Retest_Invoice_Independents<-'||P_calling_sequence;
2720
2721 --
2722 debug_info := 'Reaffirming invoice candidate listing -- Retest Invoices';
2723 IF g_debug_switch in ('y','Y') THEN
2724 Print('(Retest Invoice Independents)'||debug_info);
2725 END IF;
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'
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)
2740 OR I.last_update_date > g_activity_date
2741 OR I.invoice_date > g_activity_date));
2742
2743 --
2744
2745 if g_pa_status = 'Y' then
2746 debug_info := 'Test PA Invoices';
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'
2756 FROM ap_invoice_distributions d
2757 WHERE d.invoice_id = pl.invoice_id
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;
2767
2768 --
2769 debug_info := 'Payment Schedules';
2770 IF g_debug_switch in ('y','Y') THEN
2771 Print('(Retest Invoice Independents)'||debug_info);
2772 END IF;
2773
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'
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)
2789 OR PS.last_update_date > g_activity_date)
2790 );
2791
2792 --
2793 debug_info := 'Distributions';
2794 IF g_debug_switch in ('y','Y') THEN
2795 Print('(Retest Invoice Independents)'||debug_info);
2796 END IF;
2797
2798 /*
2799 1897941 fbreslin: If an invoice is cancelled, the ASSETS_ADDTION_FLAG is
2800 set to "U" so Mass Additions does not include the
2801 distribution. We are alos not supposed to purge
2802 invoices if any of the distributions have ben passed to
2803 FA. Adding a check to see if the invoice is cancelled
2804 before we remove an invoice with ASSETS_ADDTION_FLAG = U
2805 from the purge list.
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'
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'
2820 OR D.po_distribution_id IS NOT NULL
2821 OR ( D.assets_addition_flag||'' =
2822 Decode(g_Assets_Status,
2823 'Y', 'U',
2824 'cantequalme')
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'
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'
2839 OR ( D.assets_addition_flag||'' =
2840 Decode(g_Assets_Status,
2841 'Y', 'U',
2842 'cantequalme')
2843 AND I.cancelled_date IS NULL)));
2844 end if;
2845
2846 --
2847 debug_info := 'Payment Dates';
2848 IF g_debug_switch in ('y','Y') THEN
2849 Print('(Retest Invoice Independents)'||debug_info);
2850 END IF;
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'
2860 FROM ap_invoice_payments P, ap_checks C
2861 WHERE P.invoice_id = PL.invoice_id
2862 AND P.check_id = C.check_id
2863 AND (P.posted_flag <> 'Y'
2864 OR P.last_update_date > g_activity_date
2865 OR C.last_update_date > g_activity_date
2866 OR (NVL(C.cleared_date, C.void_date) > g_activity_date
2867 AND nvl(C.cleared_date, C.void_date) is not NULL)
2868 ));
2869
2870 --
2871 debug_info := 'Prepayments';
2872 IF g_debug_switch in ('y','Y') THEN
2873 Print('(Retest Invoice Independents)'||debug_info);
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'
2883 FROM ap_invoice_prepays IP
2884 WHERE PL.invoice_id = IP.invoice_id
2885 OR PL.invoice_id = IP.prepay_id);
2886
2887 --
2888 debug_info := 'Matched';
2889 IF g_debug_switch in ('y','Y') THEN
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
2899 , rcv_transactions rcv
2900 WHERE aid.invoice_id = PL.invoice_id
2901 and aid.rcv_transaction_id = rcv.transaction_id
2902 --Bug 1579474
2903 and rcv.last_update_date > g_activity_date
2904 );
2905
2906 --
2907 debug_info := 'Matching Invoices to Receipts';
2908 IF g_debug_switch in ('y','Y') THEN
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
2918 WHERE ad.invoice_id = PL.invoice_id
2919 and ad.rcv_transaction_id IS NOT NULL
2920 and EXISTS (
2921 SELECT 'matching'
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 --
2931 debug_info := 'Invoice accounting not purgeable';
2932 IF g_debug_switch in ('y','Y') THEN
2933 Print('(Retest Invoice Independents)'||debug_info);
2934 END IF;
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,
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
2953 AND asp.set_of_books_id=xte.ledger_id
2954 and xe.event_id = xeh.event_id
2955 and xe.application_id = 200
2956 and xeh.application_id = 200
2957 and xte.application_id = 200
2958 and (xeh.gl_transfer_status_code = 'N'
2959 OR ( xeh.last_update_date > g_activity_date)))
2960 OR EXISTS (
2961 SELECT 'payment accounting not purgeable'
2962 FROM xla_events xe
2963 , ap_invoice_payments aip
2964 , ap_checks apc
2965 , xla_ae_headers xeh
2966 , xla_transaction_entities xte
2967 WHERE xte.entity_code = 'AP_CHECKS'
2968 and xte.source_id_int_1 = apc.check_id
2969 and PL.invoice_id = aip.invoice_id
2970 and aip.check_id = apc.check_id
2971 and xe.event_id = xeh.event_id
2972 and xe.application_id = 200
2973 and xeh.application_id = 200
2974 and xte.application_id = 200
2975 and xe.event_id = xeh.event_id
2976 and (xeh.gl_transfer_status_code = 'N'
2977 OR ( xeh.last_update_date > g_activity_date)));
2978
2979 --
2980 debug_info := 'End Retest_Invoice_Independents';
2981 IF g_debug_switch in ('y','Y') THEN
2982 Print('(Retest Invoice Independents)'||debug_info);
2983 END IF;
2984 RETURN (TRUE);
2985 --
2986
2987 RETURN NULL; EXCEPTION
2988 WHEN OTHERS then
2989 IF (SQLCODE < 0 ) then
2990 Print(SQLERRM);
2991 END IF;
2992 RETURN (FALSE);
2993
2994 END Retest_Invoice_Independents;
2995
2996
2997
2998
2999 /*==========================================================================
3000 Private Function: Redo_Dependent_inv_checks
3001
3002 *==========================================================================*/
3003
3004 FUNCTION REDO_DEPENDENT_INV_CHECKS
3005 (P_Calling_Sequence IN VARCHAR2)
3006 RETURN BOOLEAN IS
3007
3008 /* bug3057900 : Created this function instead of do_dependent_inv_checks function.
3009 Because performance of delete stmt in do_dependent_inv_checks was very poor.
3010 This function does same check with the delete stmt.
3011 */
3012
3013 TYPE tab_status_type IS TABLE OF VARCHAR2(1) INDEX BY BINARY_INTEGER;
3014 tab_inv tab_status_type;
3015 tab_check tab_status_type;
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
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;
3031 p_id integer;
3032
3033 l_cnt integer;
3034 debug_info VARCHAR2(200);
3035 current_calling_sequence VARCHAR2(2000);
3036 l_invoice BOOLEAN ;
3037 l_dummy NUMBER ;
3038
3039 Function Check_check(l_invoice_id IN NUMBER ) RETURN BOOLEAN;
3040
3041 /* Get related invoice_id from check_id and check if the invoice_id is
3042 in purge list. If there is, call check_check to get check_id which
3043 is related to the invoice_id */
3044 Function Check_inv(l_check_id IN NUMBER) RETURN BOOLEAN IS
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
3054 l_flag BOOLEAN := FALSE;
3055 l_inv_id ap_purge_invoice_list.invoice_id%TYPE;
3056
3057 BEGIN
3058
3059 OPEN c_inv ;
3060 LOOP
3061
3062 FETCH c_inv into l_inv_id ;
3063 EXIT WHEN c_inv%NOTFOUND ;
3064
3065 /* if related invoice id is not in purge list */
3066 IF l_inv_id is null THEN
3067 l_flag := FALSE ;
3068 ELSE
3069
3070 /* if the invocie_id is already checked */
3071 IF tab_inv.exists(l_inv_id) THEN
3072 l_flag := TRUE ;
3073 ELSE
3074 tab_inv(l_inv_id) := 'X' ;
3075 l_flag := check_check(l_inv_id) ;
3076 END IF;
3077 END IF;
3078
3079 EXIT WHEN (not l_flag) ;
3080
3081 END LOOP;
3082
3083 CLOSE C_inv;
3084 RETURN(l_flag) ;
3085
3086 END ;
3087
3088 /* Get related check_id from invoice_id and call check_invoice
3089 to check if the invoice is in purge list. */
3090 Function Check_check(l_invoice_id IN NUMBER ) RETURN BOOLEAN IS
3091
3092 CURSOR c_check IS
3093 select check_id
3094 from ap_invoice_payments
3095 where invoice_id = l_invoice_id ;
3096
3097 l_flag BOOLEAN := FALSE;
3098 l_check_id number;
3099
3100 BEGIN
3101
3102 OPEN c_check ;
3103 LOOP
3104
3105 FETCH c_check into l_check_id ;
3106 EXIT WHEN c_check%NOTFOUND ;
3107
3108 /* if the check_id is already checked */
3109 IF tab_check.exists(l_check_id) THEN
3110 l_flag := TRUE ;
3111 ELSE
3112 tab_check(l_check_id) := 'X' ;
3113 l_flag := check_inv(l_check_id) ;
3114 END IF;
3115
3116 EXIT WHEN (not l_flag) ;
3117
3118 END LOOP;
3119
3120 CLOSE C_check;
3121 RETURN(l_flag) ;
3122
3123 END ;
3124
3125 /* main process */
3126 BEGIN
3127 -- Update the calling sequence
3128 --
3129 current_calling_sequence :=
3130 'ReDo_Dependent_Inv_Checks<-'||P_calling_sequence;
3131 --
3132
3133 debug_info := 'Starting series of dependent invoice validations';
3134 IF g_debug_switch in ('y','Y') THEN
3135 Print('(Redo_Dependent_Inv_Checks)'||debug_info);
3136 END IF;
3137
3138 FOR l_main IN c_main
3139 LOOP
3140
3141 /* initialization */
3142 tab_inv := tab_clear ;
3143 tab_check := tab_clear;
3144
3145 /* check if this invoice is not checked yet */
3146 OPEN c_main_check(l_main.invoice_id) ;
3147 FETCH c_main_check into l_dummy ;
3148 l_invoice := c_main_check%FOUND ;
3149 CLOSE c_main_check ;
3150
3151 /* if this invoice is not checked yet */
3152 IF (l_invoice) THEN
3153
3154 tab_inv(l_main.invoice_id) := 'X' ;
3155
3156 IF check_check(l_main.invoice_id) THEN
3157
3158 /* if this chain is purgeable,set flag 'S' for all invoices in this chain */
3159 p_count := tab_inv.count;
3160 IF p_count <> 0 THEN
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
3170 END IF;
3171 ELSE
3172
3173 /* if this chain is not purgeable, delete selected invoice from purge list */
3174 p_count := tab_inv.count;
3175 IF p_count <> 0 THEN
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;
3185
3186 /* delete unpurgeable list beforehand for performance */
3187 p_count := tab_check.count;
3188
3189 IF p_count <> 0 THEN
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);
3199 END LOOP;
3200 END IF;
3201
3202 END IF;
3203
3204 END IF;
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';
3214 IF g_debug_switch in ('y','Y') THEN
3215 Print('(Redo_Dependent_Inv_Checks)'||debug_info);
3216 END IF;
3217
3218 commit;
3219 return(TRUE) ;
3220
3221 RETURN NULL;
3222
3223 EXCEPTION
3224 WHEN OTHERS THEN
3225 IF (SQLCODE < 0 ) then
3226 Print(SQLERRM);
3227 END IF;
3228 RETURN(FALSE);
3229 END ;
3230
3231
3232 /*==========================================================================
3233 Private Function: Count_Ap_Rows
3234
3235 *==========================================================================*/
3236
3237 FUNCTION Count_Ap_Rows(FP_Check_Rows OUT NOCOPY NUMBER,
3238 FP_Invoice_Payment_Rows OUT NOCOPY NUMBER,
3239 FP_Invoice_Rows OUT NOCOPY NUMBER,
3240 P_Calling_Sequence VARCHAR2) RETURN BOOLEAN IS
3241
3242 debug_info VARCHAR2(200);
3243 current_calling_sequence VARCHAR2(2000);
3244
3245
3246 BEGIN
3247 -- Update the calling sequence
3248 --
3249 current_calling_sequence := 'Count_AP_Rows<-'||P_calling_sequence;
3250
3251 --
3252
3253 debug_info := 'ap_checks';
3254 IF g_debug_switch in ('y','Y') THEN
3255 Print('(Count_Ap_Rows)'||debug_info);
3256 END IF;
3257
3258
3259
3260 --
3261 SELECT count(*)
3262 INTO fp_check_rows
3263 FROM ap_checks;
3264
3265 --
3266 debug_info := 'ap_invoice_payments';
3267 IF g_debug_switch in ('y','Y') THEN
3268 Print('(Count_Ap_Rows)'||debug_info);
3269 END IF;
3270
3271 --
3272 SELECT count(*)
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
3282 --
3283 SELECT count(*)
3284 INTO fp_invoice_rows
3285 FROM ap_invoices;
3286
3287 RETURN (TRUE);
3288
3289 RETURN NULL; EXCEPTION
3290 WHEN OTHERS then
3291 IF (SQLCODE < 0 ) then
3292 Print(SQLERRM);
3293 END IF;
3294 RETURN (FALSE);
3295
3296 END Count_Ap_Rows;
3297
3298 /*==========================================================================
3299 Private Function: Count_Accounting_Rows
3300
3301 *==========================================================================*/
3302
3303 FUNCTION Count_Accounting_Rows(FP_Ae_Line_Rows OUT NOCOPY NUMBER,
3304 FP_Ae_Header_Rows OUT NOCOPY NUMBER,
3305 FP_Accounting_Event_Rows OUT NOCOPY NUMBER,
3306 FP_Chrg_Allocation_Rows OUT NOCOPY NUMBER,
3307 FP_Payment_History_Rows OUT NOCOPY NUMBER,
3308 FP_Encumbrance_line_Rows OUT NOCOPY NUMBER,
3309 FP_Rcv_Subledger_Detail_Rows OUT NOCOPY NUMBER,
3310 P_Calling_Sequence VARCHAR2) RETURN BOOLEAN IS
3311
3312 debug_info VARCHAR2(200);
3313 current_calling_sequence VARCHAR2(2000);
3314
3315
3316 BEGIN
3317 -- Update the calling sequence
3318 --
3319 current_calling_sequence := 'Count_Accounting_Rows<-'||P_calling_sequence;
3320
3321 -- Removing references to AP tables for bug 4588031
3322
3323 -- debug_info := 'ap_ae_lines';
3324 -- IF g_debug_switch in ('y','Y') THEN
3325 -- Print('(Count_Accounting_Rows)'||debug_info);
3326 -- END IF;
3327
3328
3329 -- Removing references to AP tables for bug 4588031
3330 -- SELECT count(*)
3331 -- INTO fp_ae_line_rows
3332 -- FROM ap_ae_lines;
3333
3334 -- Removing references to AP tables for bug 4588031
3335 -- debug_info := 'ap_ae_headers';
3336 -- IF g_debug_switch in ('y','Y') THEN
3337 -- Print('(Count_Accounting_Rows)'||debug_info);
3338 -- END IF;
3339
3340 -- Removing references to AP tables for bug 4588031
3341 -- SELECT count(*)
3342 -- INTO fp_ae_header_rows
3343 -- FROM ap_ae_headers;
3344
3345 -- Removing references to AP tables for bug 4588031
3346 -- debug_info := 'ap_accounting_events';
3347 -- IF g_debug_switch in ('y','Y') THEN
3348 -- Print('(Count_Accounting_Rows)'||debug_info);
3349 -- END IF;
3350
3351 -- Removing references to AP tables for bug 4588031
3352 -- SELECT count(*)
3353 -- INTO fp_accounting_event_rows
3354 -- FROM ap_accounting_events;
3355
3356 debug_info := 'ap_chrg_allocations';
3357 IF g_debug_switch in ('y','Y') THEN
3358 Print('(Count_Accounting_Rows)'||debug_info);
3359 END IF;
3360
3361 -- Bug 5118119 -- removed rendundant code as ap_chrg_allocations is obsolete in R12
3362 --
3363 -- SELECT count(*)
3364 -- INTO fp_chrg_allocation_rows
3365 -- FROM ap_chrg_allocations;
3366
3367 --
3368 debug_info := 'ap_payment_history';
3369 IF g_debug_switch in ('y','Y') THEN
3370 Print('(Count_Accounting_Rows)'||debug_info);
3371 END IF;
3372
3373 --
3374 SELECT count(*)
3375 INTO fp_payment_history_rows
3376 FROM ap_payment_history;
3377
3378 --
3379 debug_info := 'ap_encumbrance_lines';
3380 IF g_debug_switch in ('y','Y') THEN
3381 Print('(Count_Accounting_Rows)'||debug_info);
3382 END IF;
3383
3384 --
3385 SELECT count(*)
3386 INTO fp_encumbrance_line_rows
3387 FROM ap_encumbrance_lines;
3388
3389 --
3390 debug_info := 'rcv_subledger_details';
3391 IF g_debug_switch in ('y','Y') THEN
3392 Print('(Count_Accounting_Rows)'||debug_info);
3393 END IF;
3394
3395 --
3396 SELECT count(*)
3397 INTO fp_rcv_subledger_detail_rows
3398 FROM rcv_sub_ledger_details;
3399
3400
3401 RETURN (TRUE);
3402
3403
3404 RETURN NULL; EXCEPTION
3405 WHEN OTHERS then
3406 IF (SQLCODE < 0 ) then
3407 Print(SQLERRM);
3408 END IF;
3409 RETURN (FALSE);
3410
3411 END Count_Accounting_Rows;
3412
3413
3414
3415 /*==========================================================================
3416 Function: Retest_Seeded_Vendors
3417
3418 *==========================================================================*/
3419 FUNCTION Retest_Seeded_Vendors(
3420 p_calling_sequence IN VARCHAR2) RETURN BOOLEAN IS
3421
3422 debug_info VARCHAR2(200);
3423 current_calling_sequence VARCHAR2(2000);
3424
3425 BEGIN
3426 -- Update the calling sequence
3427 --
3428 current_calling_sequence := 'Retest_Seeded_Vendors<-'||P_calling_sequence;
3429 --
3430 debug_info := 'Starting Retest_Seeded_Vendors';
3431 IF g_debug_switch in ('y','Y') THEN
3432 Print('(Retest_Seeded_Vendors)'||debug_info);
3433 END IF;
3434
3435 update po_purge_vendor_list pvl
3436 set double_check_flag = 'N'
3437 where pvl.double_check_flag = 'Y'
3438 and not exists (select null
3439 from ap_suppliers vnd
3440 where vnd.vendor_id = pvl.vendor_id
3441 --and nvl(vnd.vendor_type_lookup_code, 'VENDOR') <> 'EMPLOYEE'
3442 and nvl(vnd.end_date_active,sysdate) <=
3443 g_activity_date);
3444
3445 --
3446 debug_info := 'End Retest_Seeded_Vendors';
3447 IF g_debug_switch in ('y','Y') THEN
3448 Print('(Retest_Seeded_Vendors)'||debug_info);
3449 END IF;
3450
3451 RETURN (TRUE);
3452
3453 RETURN NULL; EXCEPTION
3454 WHEN OTHERS then
3455 IF (SQLCODE < 0 ) then
3456 Print(SQLERRM);
3457 END IF;
3458 RETURN (FALSE);
3459
3460 END Retest_Seeded_Vendors;
3461
3462
3463
3464 /*==========================================================================
3465 Function: Retest_Vendors
3466
3467 *==========================================================================*/
3468 FUNCTION Retest_Vendors(
3469 p_calling_sequence IN VARCHAR2) RETURN BOOLEAN IS
3470
3471 debug_info VARCHAR2(200);
3472 current_calling_sequence VARCHAR2(2000);
3473
3474 BEGIN
3475 -- Update the calling sequence
3476 --
3477 current_calling_sequence := 'Retest_Vendors<-'||P_calling_sequence;
3478 --
3479 debug_info := 'Starting Retest_Vendors';
3480 IF g_debug_switch in ('y','Y') THEN
3481 Print('(Retest_Vendors)'||debug_info);
3482 END IF;
3483
3484 if (g_payables_status = 'Y') then
3485 if (g_assets_status = 'Y') then
3486
3487 debug_info := 'retest_fa_vendors';
3488 IF g_debug_switch in ('y','Y') THEN
3489 Print('(Retest_Vendors)'||debug_info);
3490 END IF;
3491
3492 -- retest_fa_vendors
3493 update po_purge_vendor_list pvl
3494 set double_check_flag = 'N'
3495 where pvl.double_check_flag = 'Y'
3496 and (exists (select null
3497 from fa_mass_additions fma
3498 where fma.po_vendor_id = pvl.vendor_id)
3499 or
3500 exists (select null
3501 from fa_asset_invoices fai
3502 where fai.po_vendor_id = pvl.vendor_id));
3503 end if;
3504
3505 debug_info := 'retest_ap_vendors';
3506 IF g_debug_switch in ('y','Y') THEN
3507 Print('(Retest_Vendors)'||debug_info);
3508 END IF;
3509
3510 -- retest_ap_vendors
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,
3520 ap_supplier_sites_all pvs
3521 where asi.vendor_site_id = pvs.vendor_site_id
3522 and pvs.vendor_id = pvl.vendor_id)
3523 or
3524 exists (select null
3525 from ap_recurring_payments_all arp
3526 where arp.vendor_id = pvl.vendor_id));
3527 end if;
3528
3529 if (g_purchasing_status = 'Y') then
3530
3531 debug_info := 'retest_po_vendors';
3532 IF g_debug_switch in ('y','Y') THEN
3533 Print('(Retest_Vendors)'||debug_info);
3534 END IF;
3535
3536
3537 -- retest_po_vendors
3538 update po_purge_vendor_list pvl
3539 set double_check_flag = 'N'
3540 where pvl.double_check_flag = 'Y'
3541 and (exists (select null
3542 from po_headers_all ph
3543 where ph.vendor_id = pvl.vendor_id)
3544 or
3545 exists (select null
3546 from po_rfq_vendors rfq
3547 where rfq.vendor_id = pvl.vendor_id)
3548 or
3549 exists (select null
3550 from rcv_shipment_headers rcvsh
3551 where rcvsh.vendor_id = pvl.vendor_id)
3552 or
3553 exists (select null
3554 from rcv_headers_interface rhi
3555 where rhi.vendor_id = pvl.vendor_id)
3556 or
3557 exists (select null
3558 from rcv_transactions_interface rti
3559 where rti.vendor_id = pvl.vendor_id));
3560 end if;
3561
3562 if (g_chv_status = 'Y') then
3563
3564 debug_info := 'retest_chv_vendors';
3565 IF g_debug_switch in ('y','Y') THEN
3566 Print('(Retest_Vendors)'||debug_info);
3567 END IF;
3568
3569
3570 -- retest_chv_vendors
3571
3572 update po_purge_vendor_list pvl
3573 set double_check_flag = 'N'
3574 where pvl.double_check_flag = 'Y'
3575 and (exists (select null
3576 from chv_schedule_headers csh
3577 where csh.vendor_id = pvl.vendor_id));
3578
3579 end if;
3580
3581
3582 if (g_mrp_status = 'Y') then
3583
3584 debug_info := 'retest_mrp_vendors';
3585 IF g_debug_switch in ('y','Y') THEN
3586 Print('(Retest_Vendors)'||debug_info);
3587 END IF;
3588
3589 -- retest_mrp_vendors
3590
3591 --1796376, removed check for inactivity dates on sql below
3592
3593 update po_purge_vendor_list pvl
3594 set double_check_flag = 'N'
3595 where pvl.double_check_flag = 'Y'
3596 and (exists (select null
3597 from mrp_sr_source_org msso
3598 where msso.vendor_id = pvl.vendor_id));
3599
3600 end if;
3601
3602
3603 if (g_edi_status = 'Y') then
3604
3605 debug_info := 'retest_edi_vendors';
3606 IF g_debug_switch in ('y','Y') THEN
3607 Print('(Retest_Vendors)'||debug_info);
3608 END IF;
3609
3610
3611 -- retest_edi_vendors
3612
3613 update po_purge_vendor_list pvl
3614 set double_check_flag = 'N'
3615 where pvl.double_check_flag = 'Y'
3616 and (exists (select null
3617 from ece_tp_details etd,
3618 ap_supplier_sites_all pvs
3619 where etd.tp_header_id = pvs.tp_header_id
3620 and pvs.vendor_id = pvl.vendor_id
3621 and etd.last_update_date > g_activity_date));
3622 --Bug 1781451 Update purge list to include only vendors with last_update_date
3623 -- less than last activity date in concurrent request parameters
3624 -- and etd.last_update_date <= g_activity_date));
3625
3626 end if;
3627
3628
3629 COMMIT;
3630
3631 debug_info := 'End Retest_Vendors';
3632 IF g_debug_switch in ('y','Y') THEN
3633 Print('(Retest_Vendors)'||debug_info);
3634 END IF;
3635
3636 RETURN (TRUE);
3637
3638 RETURN NULL; EXCEPTION
3639 WHEN OTHERS then
3640 IF (SQLCODE < 0 ) then
3641 Print(SQLERRM);
3642 END IF;
3643 RETURN (FALSE);
3644
3645 END Retest_Vendors;
3646
3647
3648
3649 /*==========================================================================
3650 Function: Retest_Seeded_Chv_by_Org
3651
3652 *==========================================================================*/
3653
3654 FUNCTION Retest_Seeded_Chv_by_Org(P_Calling_Sequence VARCHAR2) RETURN BOOLEAN IS
3655
3656 debug_info VARCHAR2(200);
3657 current_calling_sequence VARCHAR2(2000);
3658
3659 BEGIN
3660 -- Update the calling sequence
3661 --
3662 current_calling_sequence := 'Retest_Seeded_Chv_by_Org<-'||P_calling_sequence;
3663
3664 --
3665
3666 debug_info := 'Starting Retest Schedules by Org';
3667 IF g_debug_switch in ('y','Y') THEN
3668 Print('(Retest_Seeded_Chv_by_Org)'||debug_info);
3669 END IF;
3670
3671 --
3672
3673 update chv_purge_schedule_list cpsl
3674 set double_check_flag = 'N'
3675 where cpsl.double_check_flag = 'Y'
3676 and not exists (select 'schedule not purgeable' from chv_schedule_items csi,
3677 chv_schedule_headers csh
3678 where csh.schedule_id = csi.schedule_id
3679 and csh.last_update_date <= g_activity_date
3680 and csi.organization_id = g_organization_id
3681 and csi.schedule_item_id = cpsl.schedule_item_id);
3682
3683 RETURN (TRUE);
3684
3685 RETURN NULL; EXCEPTION
3686 WHEN OTHERS then
3687 IF (SQLCODE < 0 ) then
3688 Print(SQLERRM);
3689 END IF;
3690 RETURN (FALSE);
3691
3692 END Retest_Seeded_Chv_by_Org;
3693
3694 /*==========================================================================
3695 Function: Retest_Seeded_Chv_by_CUM
3696
3697 *==========================================================================*/
3698
3699 FUNCTION Retest_Seeded_Chv_by_Cum(P_Calling_Sequence VARCHAR2) RETURN BOOLEAN IS
3700
3701 debug_info VARCHAR2(200);
3702 current_calling_sequence VARCHAR2(2000);
3703
3704 BEGIN
3705 -- Update the calling sequence
3706 --
3707 current_calling_sequence := 'Retest_Seeded_Chv_by_Cum<-'||P_calling_sequence;
3708
3709 --
3710
3711 debug_info := 'Starting Retest Schedules by CUM';
3712 IF g_debug_switch in ('y','Y') THEN
3713 Print('(Retest_Seeded_Chv_by_CUM)'||debug_info);
3714 END IF;
3715
3716 --
3717
3718 update chv_purge_cum_list cpcl
3719 set double_check_flag = 'N'
3720 where cpcl.double_check_flag = 'Y'
3721 and not exists (select null from chv_cum_periods ccp
3722 where ccp.organization_id = g_organization_id
3723 and NVL(ccp.cum_period_end_date, sysdate + 1) <= g_activity_date
3724 and NVL(ccp.cum_period_end_date,sysdate + 1) < sysdate
3725 and ccp.cum_period_id = cpcl.cum_period_id);
3726
3727 debug_info := 'Eliminate Items in CUM';
3728 IF g_debug_switch in ('y','Y') THEN
3729 Print('(Retest_Seeded_Chv_by_CUM)'||debug_info);
3730 END IF;
3731
3732 --
3733
3734 update chv_purge_schedule_list cpsl
3735 set double_check_flag = 'N'
3736 where cpsl.double_check_flag = 'Y'
3737 and not exists (select null from chv_schedule_items csi,
3738 chv_schedule_headers csh,
3739 chv_cum_periods ccp,
3740 chv_purge_cum_list cpcl
3741 where csh.schedule_id = csi.schedule_id
3742 and csh.schedule_horizon_start between ccp.cum_period_start_date
3743 and ccp.cum_period_end_date
3744 and ccp.cum_period_id = cpcl.cum_period_id
3745 and csi.organization_id = g_organization_id
3746 and csi.schedule_item_id = cpsl.schedule_item_id);
3747
3748
3749 RETURN (TRUE);
3750
3751 RETURN NULL; EXCEPTION
3752 WHEN OTHERS then
3753 IF (SQLCODE < 0 ) then
3754 Print(SQLERRM);
3755 END IF;
3756 RETURN (FALSE);
3757
3758 END Retest_Seeded_Chv_by_Cum;
3759
3760 /*==========================================================================
3761 Function: Retest_Chv_in_Cum
3762
3763 *==========================================================================*/
3764
3765 FUNCTION Retest_Chv_in_Cum(P_Calling_Sequence VARCHAR2) RETURN BOOLEAN IS
3766
3767 debug_info VARCHAR2(200);
3768 current_calling_sequence VARCHAR2(2000);
3769
3770 BEGIN
3771 -- Update the calling sequence
3772 --
3773 current_calling_sequence := 'Retest_Chv_in_Cum<-'||P_calling_sequence;
3774
3775 --
3776
3777 debug_info := 'Eliminate Schedules in CUM';
3778 IF g_debug_switch in ('y','Y') THEN
3779 Print('(Retest_Chv_in_Cum)'||debug_info);
3780 END IF;
3781
3782 --
3783 update chv_purge_schedule_list cpsl
3784 set double_check_flag = 'N'
3785 where cpsl.double_check_flag = 'Y'
3786 and exists (select null
3787 from chv_cum_periods ccp,
3788 chv_schedule_items csi,
3789 chv_schedule_headers csh,
3790 chv_org_options coo
3791 where ccp.organization_id = g_organization_id
3792 and sysdate between ccp.cum_period_start_date and
3793 NVL(ccp.cum_period_end_date,sysdate + 1)
3794 and coo.organization_id = ccp.organization_id
3795 and coo.enable_cum_flag = 'Y'
3796 and csh.schedule_id = csi.schedule_id
3797 and csh.schedule_horizon_start >= ccp.cum_period_start_date
3798 and csi.schedule_item_id = cpsl.schedule_item_id);
3799
3800
3801 RETURN (TRUE);
3802
3803 RETURN NULL; EXCEPTION
3804 WHEN OTHERS then
3805 IF (SQLCODE < 0 ) then
3806 Print(SQLERRM);
3807 END IF;
3808 RETURN (FALSE);
3809
3810 END Retest_Chv_in_Cum;
3811
3812 /*==========================================================================
3813 Function: Retest_Chv_in_Edi
3814
3815 *==========================================================================*/
3816
3817 FUNCTION Retest_Chv_in_Edi(P_Calling_Sequence VARCHAR2) RETURN BOOLEAN IS
3818
3819 debug_info VARCHAR2(200);
3820 current_calling_sequence VARCHAR2(2000);
3821
3822 BEGIN
3823 -- Update the calling sequence
3824 --
3825 current_calling_sequence := 'Retest_Chv_in_Edi<-'||P_calling_sequence;
3826
3827 --
3828
3829 debug_info := 'Eliminate Schedules in EDI';
3830 IF g_debug_switch in ('y','Y') THEN
3831 Print('(Retest_Chv_in_edi)'||debug_info);
3832 END IF;
3833
3834 --
3835 update chv_purge_schedule_list cpsl
3836 set double_check_flag = 'N'
3837 where cpsl.double_check_flag = 'Y'
3838 and exists (select null
3839 from chv_schedule_items csi,
3840 ece_spso_items esi
3841 where csi.schedule_item_id = cpsl.schedule_item_id
3842 and csi.schedule_id = esi.schedule_id);
3843
3844
3845 RETURN (TRUE);
3846
3847 RETURN NULL; EXCEPTION
3848 WHEN OTHERS then
3849 IF (SQLCODE < 0 ) then
3850 Print(SQLERRM);
3851 END IF;
3852 RETURN (FALSE);
3853
3854 END Retest_Chv_in_Edi;
3855
3856 /*==========================================================================
3857 Function: Count_Chv_Rows
3858
3859 *==========================================================================*/
3860 FUNCTION Count_Chv_Rows
3861 (chv_auth_rows OUT NOCOPY NUMBER,
3862 chv_cum_adj_rows OUT NOCOPY NUMBER,
3863 chv_cum_rows OUT NOCOPY NUMBER,
3864 chv_hor_rows OUT NOCOPY NUMBER,
3865 chv_ord_rows OUT NOCOPY NUMBER,
3866 chv_head_rows OUT NOCOPY NUMBER,
3867 chv_item_rows OUT NOCOPY NUMBER,
3868 P_Calling_Sequence VARCHAR2)
3869 RETURN BOOLEAN IS
3870
3871 debug_info VARCHAR2(200);
3872 current_calling_sequence VARCHAR2(2000);
3873
3874
3875 BEGIN
3876 -- Update the calling sequence
3877 --
3878 current_calling_sequence := 'Count_Chv_Rows<-'||P_calling_sequence;
3879
3880 debug_info := 'Count Rows in tables affecting Supplier Scheduling';
3881 IF g_debug_switch in ('y','Y') THEN
3882 Print('(Count_Chv_Rows)'||debug_info);
3883 END IF;
3884
3885 --
3886
3887 debug_info := 'chv_auth';
3888 IF g_debug_switch in ('y','Y') THEN
3889 Print('(Count_Chv_Rows)'||debug_info);
3890 END IF;
3891
3892 --
3893 SELECT count(*)
3894 INTO chv_auth_rows
3895 FROM chv_authorizations;
3896
3897 --
3898
3899 debug_info := 'chv_cum_adj';
3900 IF g_debug_switch in ('y','Y') THEN
3901 Print('(Count_Chv_Rows)'||debug_info);
3902 END IF;
3903
3904 --
3905 SELECT count(*)
3906 INTO chv_cum_adj_rows
3907 FROM chv_cum_adjustments;
3908
3909 --
3910
3911 debug_info := 'chv_cum';
3912 IF g_debug_switch in ('y','Y') THEN
3913 Print('(Count_Chv_Rows)'||debug_info);
3914 END IF;
3915
3916 --
3917 SELECT count(*)
3918 INTO chv_cum_rows
3919 FROM chv_cum_periods;
3920 --
3921
3922 debug_info := 'chv_hor';
3923 IF g_debug_switch in ('y','Y') THEN
3924 Print('(Count_Chv_Rows)'||debug_info);
3925 end iF;
3926
3927 --
3928 SELECT count(*)
3929 INTO chv_hor_rows
3930 FROM chv_horizontal_schedules;
3931 --
3932
3933 debug_info := 'chv_ord';
3934 IF g_debug_switch in ('y','Y') THEN
3935 Print('(Count_Chv_Rows)'||debug_info);
3936 END IF;
3937
3938 --
3939 SELECT count(*)
3940 INTO chv_ord_rows
3941 FROM chv_item_orders;
3942 --
3943
3944 debug_info := 'chv_head';
3945 IF g_debug_switch in ('y','Y') THEN
3946 Print('(Count_Chv_Rows)'||debug_info);
3947 END IF;
3948
3949 --
3950 SELECT count(*)
3951 INTO chv_head_rows
3952 FROM chv_schedule_headers;
3953 --
3954
3955 debug_info := 'chv_item';
3956 IF g_debug_switch in ('y','Y') THEN
3957 Print('(Count_Chv_Rows)'||debug_info);
3958 END IF;
3959
3960 --
3961 SELECT count(*)
3962 INTO chv_item_rows
3963 FROM chv_schedule_items
3964 WHERE NVL(item_purge_status,'N') <> 'PURGED';
3965
3966 RETURN (TRUE);
3967
3968 RETURN NULL; EXCEPTION
3969 WHEN OTHERS then
3970 IF (SQLCODE < 0 ) then
3971 Print(SQLERRM);
3972 END IF;
3973 RETURN (FALSE);
3974
3975 END Count_Chv_Rows;
3976
3977
3978 /*==========================================================================
3979 Function: Record_Initial_Statistics
3980
3981 *==========================================================================*/
3982 FUNCTION Record_Initial_Statistics(fp_check_rows IN NUMBER,
3983 fp_invoice_payment_rows IN NUMBER,
3984 fp_invoice_rows IN NUMBER,
3985 fp_po_header_rows IN NUMBER,
3986 fp_receipt_line_rows IN NUMBER,
3987 fp_req_header_rows IN NUMBER,
3988 fp_vendor_rows IN NUMBER,
3989 fp_po_asl_rows IN NUMBER,
3990 fp_po_asl_attr_rows IN NUMBER,
3991 fp_po_asl_doc_rows IN NUMBER,
3992 fp_chv_auth_rows IN NUMBER,
3993 fp_chv_cum_adj_rows IN NUMBER,
3994 fp_chv_cum_rows IN NUMBER,
3995 fp_chv_hor_rows IN NUMBER,
3996 fp_chv_ord_rows IN NUMBER,
3997 fp_chv_head_rows IN NUMBER,
3998 fp_chv_item_rows IN NUMBER,
3999 fp_ae_line_rows IN NUMBER,
4000 fp_ae_header_rows IN NUMBER,
4001 fp_accounting_event_rows IN NUMBER,
4002 fp_chrg_allocation_rows IN NUMBER,
4003 fp_payment_history_rows IN NUMBER,
4004 fp_encumbrance_line_rows IN NUMBER,
4005 fp_rcv_subledger_detail_rows IN NUMBER,
4006 fp_purge_name IN VARCHAR2,
4007 p_calling_sequence IN VARCHAR2) RETURN BOOLEAN IS
4008
4009 debug_info VARCHAR2(200);
4010 current_calling_sequence VARCHAR2(2000);
4011
4012
4013 BEGIN
4014
4015 -- Update the calling sequence
4016 --
4017 current_calling_sequence := 'Record_Initial_Statistics<-'||P_calling_sequence;
4018 --
4019 debug_info := 'Starting Record_Initial_Statistics';
4020 IF g_debug_switch in ('y','Y') THEN
4021 Print('(Record_Initial_Statistics)'||debug_info);
4022 END IF;
4023
4024
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,
4034 po_approved_supplier_list = fp_po_asl_rows,
4035 po_asl_attributes = fp_po_asl_attr_rows,
4036 po_asl_documents = fp_po_asl_doc_rows,
4037 chv_authorizations = fp_chv_auth_rows,
4038 chv_cum_adjustments = fp_chv_cum_adj_rows,
4039 chv_cum_periods = fp_chv_cum_rows,
4040 chv_horizontal_schedules = fp_chv_hor_rows,
4041 chv_item_orders = fp_chv_ord_rows,
4042 chv_schedule_headers = fp_chv_head_rows,
4043 chv_schedule_items = fp_chv_item_rows,
4044 ap_ae_lines = fp_ae_line_rows,
4045 ap_ae_headers = fp_ae_header_rows,
4046 ap_accounting_events = fp_accounting_event_rows,
4047 ap_chrg_allocations = fp_chrg_allocation_rows,
4048 ap_payment_history = fp_payment_history_rows,
4049 ap_encumbrance_lines = fp_encumbrance_line_rows,
4050 rcv_subledger_details = fp_rcv_subledger_detail_rows
4051 WHERE purge_name = fp_purge_name ;
4052
4053 --
4054 debug_info := 'Starting Record_Initial_Statistics';
4055 IF g_debug_switch in ('y','Y') THEN
4056 Print('(Record_Initial_Statistics)'||debug_info);
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
4066 , ap_payment_history aph
4067 WHERE aip.invoice_id = PL.invoice_id
4068 and aip.check_id = aph.check_id
4069 and aph.last_update_date > g_activity_date);
4070
4071 --
4072 debug_info := 'End Record_Initial_Statistics';
4073 IF g_debug_switch in ('y','Y') THEN
4074 Print('(Record_Initial_Statistics)'||debug_info);
4075 END IF;
4076
4077 --
4078 RETURN (TRUE);
4079
4080 RETURN NULL; EXCEPTION
4081 WHEN OTHERS then
4082 IF (SQLCODE < 0 ) then
4083 Print(SQLERRM);
4084 END IF;
4085 RETURN (FALSE);
4086
4087 END Record_Initial_Statistics;
4088
4089
4090 /*==========================================================================
4091 Function: Confirm_Seeded_Data
4092
4093 *==========================================================================*/
4094 FUNCTION Confirm_Seeded_Data(P_Status IN VARCHAR2,
4095 P_Category IN VARCHAR2,
4096 P_Purge_Name IN VARCHAR2,
4097 P_Activity_Date IN DATE,
4098 P_Organization_ID IN NUMBER,
4099 P_PA_Status IN VARCHAR2,
4100 P_Purchasing_Status IN VARCHAR2,
4101 P_Payables_Status IN VARCHAR2,
4102 P_Assets_Status IN VARCHAR2,
4103 P_Chv_Status IN VARCHAR2,
4104 P_EDI_Status IN VARCHAR2,
4105 P_MRP_Status IN VARCHAR2,
4106 P_Debug_Switch IN VARCHAR2,
4107 p_calling_sequence IN VARCHAR2) RETURN BOOLEAN IS
4108
4109 debug_info VARCHAR2(200);
4110 current_calling_sequence VARCHAR2(2000);
4111
4112 check_rows NUMBER;
4113 invoice_payment_rows NUMBER;
4114 invoice_rows NUMBER;
4115 po_header_rows NUMBER;
4116 receipt_line_rows NUMBER;
4117 req_header_rows NUMBER;
4118 vendor_rows NUMBER;
4119 po_asl_rows NUMBER;
4120 po_asl_attr_rows NUMBER;
4121 po_asl_doc_rows NUMBER;
4122 chv_auth_rows NUMBER;
4123 chv_cum_adj_rows NUMBER;
4124 chv_cum_rows NUMBER;
4125 chv_hor_rows NUMBER;
4126 chv_ord_rows NUMBER;
4127 chv_head_rows NUMBER;
4128 chv_item_rows NUMBER;
4129 ae_line_rows NUMBER;
4130 ae_header_rows NUMBER;
4131 accounting_event_rows NUMBER;
4132 chrg_allocation_rows NUMBER;
4133 payment_history_rows NUMBER;
4134 encumbrance_line_rows NUMBER;
4135 rcv_subledger_detail_rows NUMBER;
4136
4137 l_po_return_status VARCHAR2(1);
4138 l_po_msg VARCHAR2(2000);
4139 l_po_records_filtered VARCHAR2(1);
4140
4141 l_status VARCHAR2(30);
4142
4143 BEGIN
4144
4145 g_debug_switch := p_debug_switch;
4146
4147 g_activity_date := P_Activity_Date;
4148 g_organization_id := P_Organization_ID;
4149 g_category := P_Category;
4150 g_pa_status := P_PA_Status;
4151 g_purchasing_Status := P_Purchasing_Status;
4152 g_payables_status := P_Payables_Status;
4153 g_assets_status := P_Assets_Status;
4154 g_chv_status := P_Chv_Status;
4155 g_edi_status := P_EDI_Status;
4156 g_mrp_status := P_MRP_Status;
4157
4158 -- Update the calling sequence
4159 --
4160 current_calling_sequence := 'Confirm_Seeded_Data<-'||P_calling_sequence;
4161 --
4162 debug_info := 'Starting Confirm_Seeded_Data';
4163 IF g_debug_switch in ('y','Y') THEN
4164 Print('(Confirm_Seeded_Data)'||debug_info);
4165 END IF;
4166
4167 -- reset_row_counts
4168 check_rows := 0;
4169 invoice_payment_rows := 0;
4170 invoice_rows := 0;
4171 po_header_rows := 0;
4172 receipt_line_rows := 0;
4173 req_header_rows := 0;
4174 vendor_rows := 0;
4175 po_asl_rows := 0;
4176 po_asl_attr_rows := 0;
4177 po_asl_doc_rows := 0;
4178 chv_auth_rows := 0;
4179 chv_cum_adj_rows := 0;
4180 chv_cum_rows := 0;
4181 chv_hor_rows := 0;
4182 chv_ord_rows := 0;
4183 chv_head_rows := 0;
4184 chv_item_rows := 0;
4185 ae_line_rows := 0;
4186 ae_header_rows := 0;
4187 accounting_event_rows := 0;
4188 chrg_allocation_rows := 0;
4189 payment_history_rows := 0;
4190 encumbrance_line_rows := 0;
4191 rcv_subledger_detail_rows := 0;
4192
4193
4194 --
4195 debug_info := 'Re-validating candidates';
4196 IF g_debug_switch in ('y','Y') THEN
4197 Print('(Confirm_Seeded_Data)'||debug_info);
4198 END IF;
4199
4200 if (p_category = 'SIMPLE INVOICES') then
4201
4202 --
4203 debug_info := ' Invoices';
4204 IF g_debug_switch in ('y','Y') THEN
4205 Print('(Confirm_Seeded_Data)'||debug_info);
4206 END IF;
4207
4208 if (retest_invoice_independents('Confirm_Seeded_Data') <> TRUE) then
4209 Print('retest_invoice_independents failed.');
4210 return(FALSE);
4211 end if;
4212
4213 if (redo_dependent_inv_checks('Confirm_Seeded_Data') <> TRUE) then
4214 Print('redo_dependent_inv_checks failed.' );
4215 return(FALSE);
4216 end if;
4217
4218 --
4219 debug_info := 'Computing initial table size statistics for Payables';
4220 IF g_debug_switch in ('y','Y') THEN
4221 Print('(Confirm_Seeded_Data)'||debug_info);
4222 END IF;
4223
4224 if (count_ap_rows(check_rows,
4225 invoice_payment_rows,
4226 invoice_rows,
4227 'Confirm_Seeded_Data') <> TRUE) then
4228 Print('count_ap_row failed.' );
4229 return(FALSE);
4230 end if;
4231
4232 --
4233 debug_info := 'Computing initial table size statistics for Accounting';
4234 IF g_debug_switch in ('y','Y') THEN
4235 Print('(Confirm_Seeded_Data)'||debug_info);
4236 END IF;
4237
4238 if (count_accounting_rows(ae_line_rows,
4239 ae_header_rows,
4240 accounting_event_rows,
4241 chrg_allocation_rows,
4242 payment_history_rows,
4243 encumbrance_line_rows,
4244 rcv_subledger_detail_rows,
4245 'Confirm_Seeded_Data') <> TRUE) then
4246 Print('count_accounting_rows failed.' );
4247 return(FALSE);
4248 end if;
4249
4250
4251 ELSIF (p_category IN ('SIMPLE REQUISITIONS', 'SIMPLE POS')) THEN
4252
4253 debug_info := ' Call PO Purge API';
4254 IF g_debug_switch in ('y','Y') THEN
4255 Print('(Confirm_Seeded_Data)'||debug_info);
4256 END IF;
4257
4258 PO_AP_PURGE_GRP.confirm_records
4259 ( p_api_version => 1.0,
4260 p_init_msg_list => 'T',
4261 p_commit => 'F',
4262 x_return_status => l_po_return_status,
4263 x_msg_data => l_po_msg,
4264 p_purge_name => p_purge_name,
4265 p_purge_category => p_category,
4266 p_last_activity_date => p_activity_date
4267 );
4268
4269 IF (l_po_return_status <> 'S') THEN
4270 Print(l_po_msg);
4271 RETURN FALSE;
4272 END IF;
4273
4274 PO_AP_PURGE_GRP.filter_records
4275 ( p_api_version => 1.0,
4276 p_init_msg_list => 'T',
4277 p_commit => 'F',
4278 x_return_status => l_po_return_status,
4279 x_msg_data => l_po_msg,
4280 p_purge_status => 'REVALIDATING',
4281 p_purge_name => p_purge_name,
4282 p_purge_category => p_category,
4283 p_action => NULL,
4284 x_po_records_filtered => l_po_records_filtered
4285 );
4286
4287 IF (l_po_return_status <> 'S') THEN
4288 Print(l_po_msg);
4289 RETURN FALSE;
4290 END IF;
4291
4292 debug_info := 'Computing initial table size statistics for Purchasing';
4293 IF g_debug_switch in ('y','Y') THEN
4294 Print('(Confirm_Seeded_Data)'||debug_info);
4295 END IF;
4296
4297 PO_AP_PURGE_GRP.count_po_rows
4298 ( p_api_version => 1.0,
4299 p_init_msg_list => 'T',
4300 x_return_status => l_po_return_status,
4301 x_msg_data => l_po_msg,
4302 x_po_hdr_count => po_header_rows,
4303 x_rcv_line_count => receipt_line_rows,
4304 x_req_hdr_count => req_header_rows,
4305 x_vendor_count => vendor_rows,
4306 x_asl_count => po_asl_rows,
4307 x_asl_attr_count => po_asl_attr_rows,
4308 x_asl_doc_count => po_asl_doc_rows
4309 );
4310
4311 IF (l_po_return_status <> 'S') THEN
4312 Print(l_po_msg);
4313 RETURN FALSE;
4314 END IF;
4315
4316 elsif (p_category = 'MATCHED POS AND INVOICES') then
4317
4318 --
4319 debug_info := ' Invoices';
4320 IF g_debug_switch in ('y','Y') THEN
4321 Print('(Confirm_Seeded_Data)'||debug_info);
4322 END IF;
4323
4324 if (retest_invoice_independents('Confirm_Seeded_Data') <> TRUE) then
4325 Print('retest_invoice_independents failed.' );
4326 return(FALSE);
4327 end if;
4328
4329 if (redo_dependent_inv_checks('Confirm_Seeded_Data') <> TRUE) then
4330 Print('redo_dependent_inv_checks failed.');
4331 return(FALSE);
4332 end if;
4333
4334 --
4335 debug_info := ' Purchase Orders';
4336 IF g_debug_switch in ('y','Y') THEN
4337 Print('(Confirm_Seeded_Data)'||debug_info);
4338 END IF;
4339
4340 PO_AP_PURGE_GRP.confirm_records
4341 ( p_api_version => 1.0,
4342 p_init_msg_list => 'T',
4343 p_commit => 'F',
4344 x_return_status => l_po_return_status,
4345 x_msg_data => l_po_msg,
4346 p_purge_name => p_purge_name,
4347 p_purge_category => p_category,
4348 p_last_activity_date => p_activity_date
4349 );
4350
4351 IF (l_po_return_status <> 'S') THEN
4352 Print(l_po_msg);
4353 RETURN FALSE;
4354 END IF;
4355
4356 PO_AP_PURGE_GRP.filter_records
4357 ( p_api_version => 1.0,
4358 p_init_msg_list => 'T',
4359 p_commit => 'F',
4360 x_return_status => l_po_return_status,
4361 x_msg_data => l_po_msg,
4362 p_purge_status => 'REVALIDATING',
4363 p_purge_name => p_purge_name,
4364 p_purge_category => p_category,
4365 p_action => 'FILTER REF PO AND REQ',
4366 x_po_records_filtered => l_po_records_filtered
4367 );
4368
4369 IF (l_po_return_status <> 'S') THEN
4370 Print(l_po_msg);
4371 RETURN FALSE;
4372 END IF;
4373
4374 --
4375 debug_info := 'Re-matching purchase orders and invoices';
4376 IF g_debug_switch in ('y','Y') THEN
4377 Print('(Confirm_Seeded_Data)'||debug_info);
4378 END IF;
4379
4380 if (match_pos_to_invoices_ctrl(
4381 P_Purge_Name,
4382 'REVALIDATING',
4383 'Confirm_Seeded_Data') <> TRUE) then
4384 Print('match_pos_to_invoices_ctrl failed.' );
4385 return(FALSE);
4386 end if;
4387
4388 --
4389 debug_info := 'Computing initial table size statistics for Payables';
4390 IF g_debug_switch in ('y','Y') THEN
4391 Print('(Confirm_Seeded_Data)'||debug_info);
4392 END IF;
4393
4394 if (count_ap_rows(check_rows,
4395 invoice_payment_rows,
4396 invoice_rows,
4397 'Confirm_Seeded_Data') <> TRUE) then
4398 Print('count_ap_rows failed.' );
4399 return(FALSE);
4400 end if;
4401
4402 --
4403 debug_info := 'Computing initial table size statistics for Purchasing';
4404 IF g_debug_switch in ('y','Y') THEN
4405 Print('(Confirm_Seeded_Data)'||debug_info);
4406 END IF;
4407
4408 PO_AP_PURGE_GRP.count_po_rows
4409 ( p_api_version => 1.0,
4410 p_init_msg_list => 'T',
4411 x_return_status => l_po_return_status,
4412 x_msg_data => l_po_msg,
4413 x_po_hdr_count => po_header_rows,
4414 x_rcv_line_count => receipt_line_rows,
4415 x_req_hdr_count => req_header_rows,
4416 x_vendor_count => vendor_rows,
4417 x_asl_count => po_asl_rows,
4418 x_asl_attr_count => po_asl_attr_rows,
4419 x_asl_doc_count => po_asl_doc_rows
4420 );
4421
4422 IF (l_po_return_status <> 'S') THEN
4423 Print(l_po_msg);
4424 RETURN FALSE;
4425 END IF;
4426
4427 --
4428 debug_info := 'Computing initial table size statistics for Accounting';
4429 IF g_debug_switch in ('y','Y') THEN
4430 Print('(Confirm_Seeded_Data)'||debug_info);
4431 END IF;
4432
4433 if (count_accounting_rows(ae_line_rows,
4434 ae_header_rows,
4435 accounting_event_rows,
4436 chrg_allocation_rows,
4437 payment_history_rows,
4438 encumbrance_line_rows,
4439 rcv_subledger_detail_rows,
4440 'Confirm_Seeded_Data') <> TRUE) then
4441 Print('count_accounting_rows failed.' );
4442 return(FALSE);
4443 end if;
4444
4445
4446 elsif (p_category = 'VENDORS') then
4447 --
4448 debug_info := ' Vendors';
4449 IF g_debug_switch in ('y','Y') THEN
4450 Print('(Confirm_Seeded_Data)'||debug_info);
4451 END IF;
4452
4453 if (retest_seeded_vendors('Confirm_Seeded_Data') <> TRUE) then
4454 Print(' retest_seeded_vendors failed.');
4455 return(FALSE);
4456 end if;
4457
4458 --
4459 debug_info := 'retest_vendors';
4460 IF g_debug_switch in ('y','Y') THEN
4461 Print('(Confirm_Seeded_Data)'||debug_info);
4462 END IF;
4463
4464 if (retest_vendors('Confirm_Seeded_Data') <> TRUE) then
4465 Print('retest_vendors failed.' );
4466 return(FALSE);
4467 end if;
4468
4469 --
4470 debug_info := 'Computing initial table size statistics for Purchasing';
4471 IF g_debug_switch in ('y','Y') THEN
4472 Print('(Confirm_Seeded_Data)'||debug_info);
4473 END IF;
4474
4475 PO_AP_PURGE_GRP.count_po_rows
4476 ( p_api_version => 1.0,
4477 p_init_msg_list => 'T',
4478 x_return_status => l_po_return_status,
4479 x_msg_data => l_po_msg,
4480 x_po_hdr_count => po_header_rows,
4481 x_rcv_line_count => receipt_line_rows,
4482 x_req_hdr_count => req_header_rows,
4483 x_vendor_count => vendor_rows,
4484 x_asl_count => po_asl_rows,
4485 x_asl_attr_count => po_asl_attr_rows,
4486 x_asl_doc_count => po_asl_doc_rows
4487 );
4488
4489 IF (l_po_return_status <> 'S') THEN
4490 Print(l_po_msg);
4491 RETURN FALSE;
4492 END IF;
4493
4494
4495 elsif (p_category = 'SCHEDULES BY ORGANIZATION') then
4496 --
4497 debug_info := ' Schedules';
4498 IF g_debug_switch in ('y','Y') THEN
4499 Print('(Confirm_Seeded_Data)'||debug_info);
4500 END IF;
4501
4502 if (retest_seeded_chv_by_org('Confirm_Seeded_Data') <> TRUE) then
4503 Print('retest_seeded_chv_by_org failed.');
4504 return(FALSE);
4505 end if;
4506
4507 --
4508 debug_info := 'Excluding schedules in cum';
4509 IF g_debug_switch in ('y','Y') THEN
4510 Print('(Confirm_Seeded_Data)'||debug_info);
4511 END IF;
4512
4513 if (retest_chv_in_cum('Confirm_Seeded_Data') <> TRUE) then
4514 Print('Schedules.retest_chv_in_cum failed.' );
4515 return(FALSE);
4516 end if;
4517
4518 --
4519
4520 debug_info := 'Excluding schedules in edi';
4521 IF g_debug_switch in ('y','Y') THEN
4522 Print('(Confirm_Seeded_Data)'||debug_info);
4523 END IF;
4524
4525 if (retest_chv_in_edi('Confirm_Seeded_Data') <> TRUE) then
4526 Print('Schedules.retest_chv_in_edi failed.' );
4527 return(FALSE);
4528 end if;
4529
4530 --
4531
4532 debug_info := 'Computing initial table size statistics for Supplier Scheduling';
4533 IF g_debug_switch in ('y','Y') THEN
4534 Print('(Confirm_Seeded_Data)'||debug_info);
4535 END IF;
4536
4537 -- count_chv_rows
4538 if (count_chv_rows(chv_auth_rows,
4539 chv_cum_adj_rows,
4540 chv_cum_rows,
4541 chv_hor_rows,
4542 chv_ord_rows,
4543 chv_head_rows,
4544 chv_item_rows,
4545 'Delete Seeded Data') <> TRUE) then
4546 Print('purge_schedules_by_cum failed!');
4547 RETURN(FALSE);
4548 end if;
4549
4550 elsif (p_category = 'SCHEDULES BY CUM PERIODS') then
4551
4552 --
4553
4554 debug_info := ' Schedules in CUM Periods';
4555 IF g_debug_switch in ('y','Y') THEN
4556 Print('(Confirm_Seeded_Data)'||debug_info);
4557 END IF;
4558
4559 if (retest_seeded_chv_by_cum('Confirm_Seeded_Data') <> TRUE) then
4560 Print(' Schedules.retest_seeded_chv_by_cum failed.');
4561 return(FALSE);
4562 end if;
4563
4564 --
4565
4566 debug_info := 'Computing initial table size statistics for Supplier Scheduling';
4567 IF g_debug_switch in ('y','Y') THEN
4568 Print('(Confirm_Seeded_Data)'||debug_info);
4569 END IF;
4570
4571 -- count_chv_rows
4572 if (count_chv_rows(chv_auth_rows,
4573 chv_cum_adj_rows,
4574 chv_cum_rows,
4575 chv_hor_rows,
4576 chv_ord_rows,
4577 chv_head_rows,
4578 chv_item_rows,
4579 'Delete Seeded Data') <> TRUE) then
4580 Print('purge_schedules_by_cum failed!');
4581 RETURN(FALSE);
4582 end if;
4583
4584
4585 else
4586 --
4587 debug_info := 'An invalid purge category was entered';
4588 Print('(Confirm_Seeded_Data) '||debug_info);
4589 Print('Valid categories are : SIMPLE INVOICES, SIMPLE REQUISITIONS,');
4590 Print('SIMPLE POS, MATCHED POS AND INVOICES,VENDORS,');
4591 Print('SCHEDULES BY ORGANIZATION and SCHEDULES BY CUM PERIODS');
4592
4593 l_status := 'COMPLETED-ABORTED';
4594
4595 if (Set_Purge_Status(l_status,
4596 p_purge_name,
4597 p_debug_switch,
4598 'Confirm_Seeded_Data') <> TRUE) then
4599 Print(' Set_Purge_Status failed.');
4600 return(FALSE);
4601 end if;
4602
4603 RETURN(TRUE);
4604 end if;
4605
4606 --
4607 debug_info := 'record_initial_statistics';
4608
4609 Print('(Confirm_Seeded_Data) '||debug_info);
4610
4611
4612 if (record_initial_statistics(check_rows,
4613 invoice_payment_rows,
4614 invoice_rows,
4615 po_header_rows,
4616 receipt_line_rows,
4617 req_header_rows,
4618 vendor_rows,
4619 po_asl_rows,
4620 po_asl_attr_rows,
4621 po_asl_doc_rows,
4622 chv_auth_rows,
4623 chv_cum_adj_rows,
4624 chv_cum_rows,
4625 chv_hor_rows,
4626 chv_ord_rows,
4627 chv_head_rows,
4628 chv_item_rows,
4629 ae_line_rows,
4630 ae_header_rows,
4631 accounting_event_rows,
4632 chrg_allocation_rows,
4633 payment_history_rows,
4634 encumbrance_line_rows,
4635 rcv_subledger_detail_rows,
4636 p_purge_name,
4637 'Confirm_Seeded_Data') <> TRUE) then
4638 Print('Confirm_Purge.record_initial_statistics failed.' );
4639 return(FALSE);
4640 end if;
4641
4642 l_status := 'DELETING';
4643
4644 if (Set_Purge_Status(l_status,
4645 p_purge_name,
4646 p_debug_switch,
4647 'Confirm_Seeded_Data') <> TRUE) then
4648 Print('Set_Purge_Status failed.');
4649 return(FALSE);
4650 end if;
4651
4652 RETURN (TRUE);
4653
4654 RETURN NULL; EXCEPTION
4655 WHEN OTHERS then
4656 IF (SQLCODE < 0 ) then
4657 Print(SQLERRM);
4658 END IF;
4659 RETURN (FALSE);
4660
4661 END Confirm_Seeded_Data;
4662
4663
4664 /*==========================================================================
4665 Function: Overflow
4666
4667 *==========================================================================*/
4668 FUNCTION Overflow
4669 (Overflow_Exist OUT NOCOPY VARCHAR2,
4670 range_low IN NUMBER,
4671 range_high IN NUMBER,
4672 P_Calling_Sequence IN VARCHAR2)
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'
4682 AND PL.invoice_id BETWEEN range_low AND range_high;
4683
4684 debug_info VARCHAR2(200);
4685 current_calling_sequence VARCHAR2(2000);
4686 overflow_check_stock_id NUMBER;
4687 to_be_deleted_check_number NUMBER;
4688 overflow_check_number NUMBER;
4689
4690 BEGIN
4691
4692
4693 -- Update the calling sequence
4694 --
4695
4696 current_calling_sequence := 'Overflow<-'||P_Calling_Sequence;
4697 --
4698 debug_info := 'Starting Overflow';
4699 IF g_debug_switch in ('y','Y') THEN
4700 Print('(Overflow)'||debug_info);
4701 END IF;
4702
4703 OPEN overflow_select;
4704
4705 LOOP
4706
4707 debug_info := 'Fetch overflow_select Cursor';
4708 IF g_debug_switch in ('y','Y') THEN
4709 Print('(Overflow)'||debug_info);
4710 END IF;
4711
4712 FETCH overflow_select into overflow_check_stock_id,
4713 to_be_deleted_check_number;
4714 --
4715 EXIT WHEN overflow_select%NOTFOUND OR overflow_select%NOTFOUND IS NULL;
4716
4717 if (g_debug_switch in ('y', 'Y')) then
4718 Print('------------->overflow_check_stock_id = '
4719 ||to_char(overflow_check_stock_id)
4720 ||' to_be_deleted_check_number = '
4721 ||to_char(to_be_deleted_check_number));
4722 end if;
4723
4724 overflow_check_number := to_be_deleted_check_number - 1;
4725
4726 -- Need to have a Begin - End construct so that we still enter the loop and
4727 -- exit gracefully, if the select does not return any rows.
4728
4729 Begin
4730 SELECT 'exist'
4731 INTO overflow_exist
4732 FROM ap_checks C
4733 WHERE C.check_stock_id = overflow_check_stock_id
4734 AND C.check_number = overflow_check_number
4735 AND C.status_lookup_code = 'OVERFLOW';
4736 Exception
4737 WHEN NO_DATA_FOUND THEN
4738 overflow_exist :='does not exist';
4739 Null;
4740 End;
4741
4742
4743 LOOP
4744
4745 if (overflow_exist = 'exist') then
4746
4747 debug_info := 'Delete_Overflow from ap_checks';
4748 IF g_debug_switch in ('y','Y') THEN
4749 Print('(Overflow)'||debug_info);
4750 END IF;
4751
4752 -- delete_overflow
4753
4754 DELETE FROM ap_checks C
4755 WHERE C.check_stock_id = overflow_check_stock_id
4756 AND C.check_number = overflow_check_number
4757 AND C.status_lookup_code = 'OVERFLOW';
4758
4759 overflow_check_number := overflow_check_number - 1;
4760
4761 debug_info := 'Overflow_Exists ';
4762 IF g_debug_switch in ('y','Y') THEN
4763 Print('(Overflow)'||debug_info);
4764 END IF;
4765
4766 -- Need to have a Begin - End construct so that we exit gracefully
4767 -- once we are done deleting all the overflow checks i.e when
4768 -- the select does not return any rows.
4769
4770 Begin
4771 SELECT 'exist'
4772 INTO overflow_exist
4773 FROM ap_checks C
4774 WHERE C.check_stock_id = overflow_check_stock_id
4775 AND C.check_number = overflow_check_number
4776 AND C.status_lookup_code = 'OVERFLOW';
4777 Exception
4778 When NO_DATA_FOUND then
4779 overflow_exist := 'does not exist';
4780 Null;
4781 End;
4782 else
4783 EXIT;
4784 end if;
4785 END LOOP;
4786 END LOOP;
4787
4788 CLOSE overflow_select;
4789
4790 RETURN(TRUE);
4791
4792 EXCEPTION
4793 WHEN OTHERS THEN
4794 IF (SQLCODE < 0 ) then
4795 Print(SQLERRM);
4796 END IF;
4797 RETURN (FALSE);
4798
4799 END Overflow;
4800
4801
4802 /*==========================================================================
4803 Function: Setup_Spoil
4804
4805 *==========================================================================*/
4806 FUNCTION Setup_Spoil
4807 (P_Calling_Sequence IN VARCHAR2)
4808 RETURN BOOLEAN IS
4809
4810 CURSOR setup_spoil_select is
4811 SELECT distinct C.checkrun_name
4812 FROM ap_checks C, ap_invoice_selection_criteria D
4813 WHERE D.LAST_UPDATE_DATE <= g_activity_date
4814 AND C.checkrun_name NOT IN
4815 (SELECT distinct b.checkrun_name
4816 FROM ap_checks a,
4817 ap_invoice_selection_criteria b
4818 WHERE a.checkrun_name = b.checkrun_name
4819 AND a.status_lookup_code not in
4820 ('SET UP', 'SPOILED'))
4821 AND C.checkrun_name = D.checkrun_name
4822 AND C.last_update_date <= g_activity_date;
4823
4824
4825 debug_info VARCHAR2(200);
4826 current_calling_sequence VARCHAR2(2000);
4827 selected_checkrun ap_invoice_selection_criteria.checkrun_name%TYPE;
4828
4829 BEGIN
4830
4831 -- Update the calling sequence
4832 --
4833
4834 current_calling_sequence := 'Setup_Spoil<-'||P_Calling_Sequence;
4835
4836 --
4837
4838 debug_info := 'Starting Setup_Spoil';
4839 IF g_debug_switch in ('y','Y') THEN
4840 Print('(Setup_Spoil)'||debug_info);
4841 END IF;
4842
4843 OPEN setup_spoil_select;
4844
4845 LOOP
4846
4847 debug_info := 'Fetch setup_spoil_select Cursor';
4848 IF g_debug_switch in ('y','Y') THEN
4849 Print('(Setup_Spoil)'||debug_info);
4850 END IF;
4851
4852 FETCH setup_spoil_select into selected_checkrun;
4853 --
4854 EXIT WHEN setup_spoil_select%NOTFOUND OR setup_spoil_select%NOTFOUND IS NULL;
4855
4856 IF g_debug_switch in ('y','Y') THEN
4857 Print('(Setup_Spoil)'||debug_info);
4858 END IF;
4859
4860 -- delete_setup_spoil
4861
4862 debug_info := 'delete_setup_spoil';
4863 IF g_debug_switch in ('y','Y') THEN
4864 Print('(Setup_Spoil)'||debug_info);
4865 END IF;
4866
4867 DELETE FROM ap_checks C
4868 WHERE C.checkrun_name = selected_checkrun
4869 AND C.status_lookup_code in ('SET UP','SPOILED')
4870 AND C.last_update_date <= g_activity_date;
4871
4872 -- delete_invoice_selection
4873
4874 debug_info := 'delete_invoice_selection';
4875 IF g_debug_switch in ('y','Y') THEN
4876 Print('(Setup_Spoil)'||debug_info);
4877 END IF;
4878
4879 DELETE FROM ap_invoice_selection_criteria C
4880 WHERE C.checkrun_name = selected_checkrun
4881 AND C.last_update_date <= g_activity_date;
4882
4883 END LOOP;
4884
4885 debug_info := 'End Setup_Spoil';
4886 IF g_debug_switch in ('y','Y') THEN
4887 Print('(Setup_Spoil)'||debug_info);
4888 END IF;
4889
4890 RETURN (TRUE);
4891
4892 RETURN NULL;
4893
4894 EXCEPTION
4895
4896 WHEN OTHERS THEN
4897 IF (SQLCODE < 0 ) then
4898 Print(SQLERRM);
4899 END IF;
4900 RETURN (FALSE);
4901
4902 END Setup_Spoil;
4903
4904
4905 /*==========================================================================
4906 Function: Delete_AP_Tables
4907
4908 *==========================================================================*/
4909 FUNCTION Delete_AP_Tables
4910 (P_Calling_Sequence IN VARCHAR2)
4911 RETURN BOOLEAN IS
4912
4913 debug_info VARCHAR2(200);
4914 current_calling_sequence VARCHAR2(2000);
4915 range_high NUMBER;
4916 range_low NUMBER;
4917 range_size NUMBER;
4918 inv_lower_limit NUMBER;
4919 inv_upper_limit NUMBER;
4920 overflow_exist VARCHAR2(200);
4921 overflow_check_stock_id NUMBER;
4922 to_be_deleted_check_number NUMBER;
4923 l_key_value_list1 gl_ca_utility_pkg.r_key_value_arr;
4924 l_key_value_list2 gl_ca_utility_pkg.r_key_value_arr;
4925
4926
4927
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
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;
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;
4949
4950 BEGIN
4951
4952 -- Update the calling sequence
4953 --
4954
4955 current_calling_sequence := 'Delete_AP_Tables<-'||P_Calling_Sequence;
4956
4957 debug_info := 'Starting Delete_AP_Tables';
4958 IF g_debug_switch in ('y','Y') THEN
4959 Print('(Delete_AP_Tables)'||debug_info);
4960 END IF;
4961
4962 --
4963 range_high := 0;
4964 range_size := g_range_size;
4965
4966 -- get_ap_range
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);
4976 WHILE l_count < g_range_size
4977 LOOP
4978 FETCH range INTO range_high;
4979 EXIT WHEN range%NOTFOUND;
4980 l_count := l_count + 1;
4981 END LOOP;
4982 CLOSE RANGE;
4983
4984 LOOP
4985
4986 debug_info := 'Deleting one subgroup of Invoices';
4987 IF g_debug_switch in ('y','Y') THEN
4988 Print('(Delete_AP_Tables)'||debug_info);
4989 Print('ap_doc_sequence_audit, Checks');
4990 END IF;
4991
4992 /* Bug 5052709 - removal of obsolete SQL
4993 -- Move the deletion of ap_chrg_allocations from purge_pos to here.
4994 -- Since this is now in the loop with range_low and range_high defined,
4995 -- purge this tables in multiple runs, with each run bounded by range_low
4996 -- and range_high of invoice_id
4997
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');
5007 */
5008 -- delete_check_sequence_audit
5009
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
5019 AND PL.invoice_id = IP.invoice_id
5020 AND IP.check_id = C.check_id ) ;
5021
5022 -- overflow
5023
5024 debug_info := 'ap_checks';
5025 IF g_debug_switch in ('y','Y') THEN
5026 Print('(Delete_AP_Tables)'||debug_info);
5027 END IF;
5028
5029 if (Overflow(Overflow_Exist,
5030 range_low,
5031 range_high,
5032 'delete_ap_tables') <> TRUE) then
5033 Print( 'Overflow failed!');
5034 RETURN(FALSE);
5035 end if;
5036
5037 debug_info := 'delete_checks';
5038 IF g_debug_switch in ('y','Y') THEN
5039 Print('(Delete_AP_Tables)'||debug_info);
5040 END IF;
5041
5042
5043 -- delete_checks
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
5053 debug_info := 'setup_spoil';
5054 IF g_debug_switch in ('y','Y') THEN
5055 Print('(Delete_AP_Tables)'||debug_info);
5056 END IF;
5057
5058
5059 -- setup_spoil
5060
5061 if (Setup_Spoil('delete_ap_tables') <> TRUE) then
5062 Print('Setup_Spoil failed!');
5063 RETURN(FALSE);
5064 end if;
5065
5066 debug_info := 'ap_payment_history';
5067 IF g_debug_switch in ('y','Y') THEN
5068 Print('(Delete_AP_Tables)'||debug_info);
5069 END IF;
5070
5071
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
5081 debug_info := 'ap_invoice_payments';
5082 IF g_debug_switch in ('y','Y') THEN
5083 Print('(Delete_AP_Tables)'||debug_info);
5084 END IF;
5085
5086
5087 -- delete_invoice_payments
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
5097 debug_info := 'ap_payment_schedules';
5098 IF g_debug_switch in ('y','Y') THEN
5099 Print('(Delete_AP_Tables)'||debug_info);
5100 END IF;
5101
5102
5103 -- delete_payment_schedules
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
5113 debug_info := 'ap_trial_balance';
5114 IF g_debug_switch in ('y','Y') THEN
5115 Print('(Delete_AP_Tables)'||debug_info);
5116 END IF;
5117
5118
5119 -- delete_trial_balance
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';
5129 IF g_debug_switch in ('y','Y') THEN
5130 Print('(Delete_AP_Tables)'||debug_info);
5131 END IF;
5132
5133
5134 -- delete_holds
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';
5144 IF g_debug_switch in ('y','Y') THEN
5145 Print('(Delete_AP_Tables)'||debug_info);
5146 END IF;
5147
5148
5149 -- delete approval history
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
5159 debug_info := 'ap_invoice_distributions';
5160 IF g_debug_switch in ('y','Y') THEN
5161 Print('(Delete_AP_Tables)'||debug_info);
5162 END IF;
5163
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';
5173 IF g_debug_switch in ('y','Y') THEN
5174 Print('(Delete_AP_Tables)'||debug_info);
5175 END IF;
5176
5177 -- delete_inv_seq_audit
5178
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);
5188
5189 OPEN ap_invoice_cur(range_low, range_high);
5190 LOOP
5191
5192 FETCH ap_invoice_cur
5193 INTO l_invoice_id;
5194 EXIT WHEN ap_invoice_cur%NOTFOUND;
5195
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
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
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'
5221 AND PL.invoice_id BETWEEN range_low AND range_high);
5222
5223
5224 COMMIT;
5225
5226 l_count :=0;
5227
5228 range_low := range_high +1;
5229
5230 OPEN range(range_low); --Bug2711759
5231 WHILE l_count < g_range_size
5232 LOOP
5233 FETCH range INTO range_high;
5234 EXIT WHEN range%NOTFOUND;
5235 l_count := l_count + 1;
5236 END LOOP;
5237 CLOSE RANGE;
5238
5239 if range_low > range_high then
5240 EXIT;
5241 end if;
5242
5243 END LOOP;
5244
5245 debug_info := 'deleting from ap_batches';
5246 IF g_debug_switch in ('y','Y') THEN
5247 Print('(Delete_AP_Tables)'||debug_info);
5248 END IF;
5249
5250
5251 -- delete_batches
5252
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
5262 debug_info := 'Completed deleteing from Oracle Payables';
5263 IF g_debug_switch in ('y','Y') THEN
5264 Print('(Delete_AP_Tables)'||debug_info);
5265 END IF;
5266
5267 RETURN (TRUE);
5268
5269 RETURN NULL; EXCEPTION
5270
5271 WHEN OTHERS THEN
5272 IF (SQLCODE < 0 ) then
5273 Print(SQLERRM);
5274 END IF;
5275 RETURN (FALSE);
5276
5277 END Delete_AP_Tables;
5278
5279
5280 /*==========================================================================
5281 Function: PURGE_ACCOUNTING
5282
5283 *==========================================================================*/
5284 FUNCTION PURGE_ACCOUNTING
5285
5286 (P_Calling_Sequence IN VARCHAR2)
5287 RETURN BOOLEAN IS
5288
5289 debug_info VARCHAR2(200);
5290 current_calling_sequence VARCHAR2(2000);
5291 range_high NUMBER;
5292 range_low NUMBER;
5293 range_size NUMBER;
5294 inv_lower_limit NUMBER;
5295 inv_upper_limit NUMBER;
5296 overflow_exist VARCHAR2(200);
5297 overflow_check_stock_id NUMBER;
5298 to_be_deleted_check_number NUMBER;
5299
5300
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
5310 BEGIN
5311
5312 range_high := 0;
5313 range_size := g_range_size;
5314
5315 -- get_ap_range
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
5325 LOOP
5326 FETCH range INTO range_high;
5327 EXIT WHEN range%NOTFOUND;
5328 l_count := l_count + 1;
5329 END LOOP;
5330 CLOSE RANGE;
5331
5332
5333 LOOP
5334
5335 -- Update calling sequence
5336
5337 current_calling_sequence := 'Purge Accounting<-'||P_Calling_Sequence;
5338
5339 --
5340
5341 debug_info := 'Starting Purge Accounting';
5342 IF g_debug_switch in ('y','Y') THEN
5343 Print('(Purge_Accounting)'||debug_info);
5344 END IF;
5345
5346 -- Bug 2463233
5347 -- Code Added by MSWAMINA
5348 -- Added logic to purge the ap_liability_balance
5349 --
5350 debug_info := 'ap_liability_balance';
5351 IF g_debug_switch in ('y','Y') THEN
5352 Print('(Purge_Accounting)'||debug_info);
5353 END IF;
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)
5363 AND journal_sequence_id IS NULL;
5364
5365
5366 -- Bug 4588031 - Removing code as AP accoutning tables will not be used in R12
5367 /*
5368 -- Wrote the below 2 delete statements as a fix for bug 2866997
5369 DELETE FROM ap_ae_lines ael
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'
5379 and pil.invoice_id BETWEEN range_low AND range_high) ;
5380
5381 DELETE FROM ap_ae_lines ael
5382 WHERE ael.ae_header_id in
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
5392 and aip.invoice_id = pil.invoice_id
5393 and pil.invoice_id BETWEEN range_low AND range_high);
5394
5395 debug_info := 'ap_ae_headers';
5396 IF g_debug_switch in ('y','Y') THEN
5397 Print('(Purge_Accounting)'||debug_info);
5398 END IF;
5399
5400
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
5410 -- and aae.accounting_event_id = aeh.accounting_event_id
5411 and pil.invoice_id BETWEEN range_low AND range_high
5412 ) ;
5413
5414
5415 DELETE FROM ap_ae_headers aeh
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'
5425 and pil.double_check_flag = 'Y'
5426 -- Commented the below line as a fix for bug 2880690
5427 -- and aae.accounting_event_id = aeh.accounting_event_id
5428 and aip.invoice_id = pil.invoice_id
5429 and pil.invoice_id BETWEEN range_low AND range_high) ;
5430
5431 */ --Bug 4588031
5432
5433 debug_info := 'ap_encumbrance_lines';
5434 IF g_debug_switch in ('y','Y') THEN
5435 Print('(Purge_Accounting)'||debug_info);
5436 END IF;
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'
5446 and pil.invoice_id BETWEEN range_low AND range_high);
5447
5448
5449 -- Bug 4588031 - Removing code as AP accounting tables will not be used in R12
5450 /* -- delete_ap_accounting_events
5451 -- Fix for bug 2545172 , commented above delete statement and wrote
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 ;
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
5469 AND AIP.INVOICE_ID = PIL.INVOICE_ID
5470 AND PIL.INVOICE_ID BETWEEN range_low
5471 AND range_high )
5472 AND AAE.SOURCE_TABLE = 'AP_CHECKS' ;
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
5482 AND AIP.INVOICE_ID = PIL.INVOICE_ID
5483 AND PIL.INVOICE_ID BETWEEN range_low
5484 AND range_high )
5485 and AAE.SOURCE_TABLE = 'AP_PAYMENT_HISTORY' ;
5486 */--Bug 4588031
5487
5488 COMMIT;
5489
5490 l_count :=0;
5491
5492 range_low := range_high +1;
5493
5494 OPEN range(range_low); --Bug2711759
5495 WHILE l_count < g_range_size
5496 LOOP
5497 FETCH range INTO range_high;
5498 EXIT WHEN range%NOTFOUND;
5499 l_count := l_count + 1;
5500 END LOOP;
5501 CLOSE RANGE;
5502
5503 if range_low > range_high then
5504 EXIT;
5505 end if;
5506
5507 END LOOP;
5508
5509
5510 RETURN NULL; EXCEPTION
5511
5512 WHEN OTHERS THEN
5513 IF (SQLCODE < 0 ) then
5514 Print(SQLERRM);
5515 END IF;
5516 RETURN (FALSE);
5517
5518
5519 END;
5520
5521
5522 /*==========================================================================
5523 Function: Purge_Schedules_by_Cum
5524
5525 *==========================================================================*/
5526 FUNCTION Purge_Schedules_by_Cum
5527 (P_Calling_Sequence IN VARCHAR2)
5528 RETURN BOOLEAN IS
5529
5530 debug_info VARCHAR2(200);
5531 current_calling_sequence VARCHAR2(2000);
5532 chv_lower_limit NUMBER;
5533 chv_upper_limit NUMBER;
5534 range_high NUMBER;
5535 range_low NUMBER;
5536 range_size NUMBER;
5537
5538 l_count number := 0;
5539
5540 CURSOR range (low_chv_id IN NUMBER) IS
5541 SELECT schedule_item_id
5542 FROM chv_purge_schedule_list
5543 WHERE double_check_flag = 'Y'
5544 and schedule_item_id > low_chv_id
5545 ORDER BY schedule_item_id asc;
5546
5547
5548 BEGIN
5549
5550 -- Update the calling sequence
5551 --
5552
5553 current_calling_sequence := 'Purge_Schedules_by_Cum<-'||P_Calling_Sequence;
5554
5555 --
5556 debug_info := 'Starting Purge_Schedules_by_Cum';
5557 IF g_debug_switch in ('y','Y') THEN
5558 Print('(Purge_Schedules_by_Cum)'||debug_info);
5559 END IF;
5560
5561 range_size := g_range_size;
5562
5563
5564 range_high := 0;
5565
5566 debug_info := 'get_chv_range';
5567 IF g_debug_switch in ('y','Y') THEN
5568 Print('(Purge_Schedules_by_Cum)'||debug_info);
5569 END IF;
5570
5571 -- get_chv_range
5572
5573 select nvl(min(schedule_item_id),-1)
5574 , nvl(max(schedule_item_id),-1)
5575 into range_low, range_high
5576 from chv_purge_schedule_list
5577 where double_check_flag = 'Y';
5578
5579
5580 OPEN range(range_low); --Bug2711759
5581 WHILE l_count < g_range_size
5582 LOOP
5583 FETCH range INTO range_high;
5584 EXIT WHEN range%NOTFOUND;
5585 l_count := l_count + 1;
5586 END LOOP;
5587 CLOSE RANGE;
5588
5589
5590 -----new code ends-----------
5591 LOOP
5592 debug_info := 'Updating a subgroup of Supplier Schedule Items';
5593 IF g_debug_switch in ('y','Y') THEN
5594 Print('(Purge_Schedules_by_Cum)'||debug_info);
5595 END IF;
5596
5597 -- Update chv_schedule_items
5598
5599 update chv_schedule_items csi
5600 set csi.item_purge_status = 'PURGED'
5601 where exists
5602 (select null
5603 from chv_purge_schedule_list cpsl
5604 where cpsl.schedule_item_id = csi.schedule_item_id
5605 and cpsl.double_check_flag = 'Y'
5606 and cpsl.schedule_item_id between range_low and range_high);
5607
5608 debug_info := 'chv_item_orders';
5609 IF g_debug_switch in ('y','Y') THEN
5610 Print('(Purge_Schedules_by_Cum)'||debug_info);
5611 END IF;
5612
5613 -- delete_chv_item_orders
5614
5615 delete from chv_item_orders cio
5616 where exists
5617 (select null
5618 from chv_purge_schedule_list cpsl
5619 where cpsl.schedule_item_id = cio.schedule_item_id
5620 and cpsl.double_check_flag = 'Y'
5621 and cpsl.schedule_item_id between range_low and range_high);
5622
5623 debug_info := 'po_lines';
5624 IF g_debug_switch in ('y','Y') THEN
5625 Print('(Purge_Schedules_by_Cum)'||debug_info);
5626 END IF;
5627
5628 -- delete_chv_horizontal_schedules
5629
5630 delete from chv_horizontal_Schedules chs
5631 where exists
5632 (select null
5633 from chv_purge_schedule_list cpsl
5634 where cpsl.schedule_item_id = chs.schedule_item_id
5635 and cpsl.double_check_flag = 'Y'
5636 and cpsl.schedule_item_id between range_low and range_high);
5637
5638 debug_info := 'chv_authorizations';
5639 IF g_debug_switch in ('y','Y') THEN
5640 Print('(Purge_Schedules_by_Cum)'||debug_info);
5641 END IF;
5642
5643 -- delete_chv_authorizations
5644
5645 delete from chv_authorizations ca
5646 where exists
5647 (select null
5648 from chv_purge_schedule_list cpsl
5649 where cpsl.schedule_item_id = ca.reference_id
5650 and ca.reference_type = 'SCHEDULE_ITEMS'
5651 and cpsl.double_check_flag = 'Y'
5652 and cpsl.schedule_item_id between range_low and range_high);
5653
5654 COMMIT;
5655
5656 l_count :=0;
5657
5658 range_low := range_high +1;
5659
5660 OPEN range(range_low); --Bug2711759
5661 WHILE l_count < g_range_size
5662 LOOP
5663 FETCH range INTO range_high;
5664 EXIT WHEN range%NOTFOUND;
5665 l_count := l_count + 1;
5666 END LOOP;
5667 CLOSE RANGE;
5668
5669 if range_low > range_high then
5670 EXIT;
5671 end if;
5672
5673 END LOOP;
5674
5675 debug_info := 'chv_auth_cum_periods';
5676 IF g_debug_switch in ('y','Y') THEN
5677 Print('(Purge_Schedules_by_Cum)'||debug_info);
5678 END IF;
5679
5680 -- delete_chv_authorizations
5681
5682 delete from chv_authorizations ca
5683 where exists
5684 (select null
5685 from chv_purge_cum_list cpcl
5686 where cpcl.cum_period_id = ca.reference_id
5687 and cpcl.double_check_flag = 'Y'
5688 and ca.reference_type = 'CUM_PERIODS');
5689
5690 debug_info := 'chv_cum_adjustments';
5691 IF g_debug_switch in ('y','Y') THEN
5692 Print('(Purge_Schedules_by_Cum)'||debug_info);
5693 END IF;
5694
5695 -- delete_chv_cum_adjustments
5696
5697 delete from chv_cum_adjustments cca
5698 where exists
5699 (select null
5700 from chv_purge_cum_list cpcl
5701 where cpcl.cum_period_id = cca.cum_period_id
5702 and cpcl.double_check_flag = 'Y');
5703
5704 debug_info := 'chv_cum_periods';
5705 IF g_debug_switch in ('y','Y') THEN
5706 Print('(Purge_Schedules_by_Cum)'||debug_info);
5707 END IF;
5708
5709 -- delete_chv_cum_periods
5710
5711 delete from chv_cum_periods ccp
5712 where exists
5713 (select null
5714 from chv_purge_cum_list cpcl
5715 where cpcl.cum_period_id = ccp.cum_period_id
5716 and cpcl.double_check_flag = 'Y');
5717
5718 debug_info := 'chv_schedule_items';
5719 IF g_debug_switch in ('y','Y') THEN
5720 Print('(Purge_Schedules_by_Cum)'||debug_info);
5721 END IF;
5722
5723 -- delete_chv_schedule_items
5724
5725 /* bug2067536 Performance Bug
5726 */
5727 delete from chv_schedule_items csi
5728 where not exists (select null
5729 from chv_schedule_items cs
5730 where csi.schedule_id = cs.schedule_id
5731 and nvl(cs.item_purge_status,'ACTIVE') <> 'PURGED');
5732
5733 debug_info := 'chv_schedule_headers';
5734 IF g_debug_switch in ('y','Y') THEN
5735 Print('(Purge_Schedules_by_Cum)'||debug_info);
5736 END IF;
5737
5738 -- delete_chv_schedule_headers
5739
5740 /* bug2067536 Performance Bug
5741 */
5742 delete from chv_schedule_headers csh
5743 where not exists (select null
5744 from chv_schedule_items csi
5745 where csh.schedule_id = csi.schedule_id );
5746
5747 COMMIT;
5748
5749 debug_info := 'End Purge_Schedules_by_Org';
5750 IF g_debug_switch in ('y','Y') THEN
5751 Print('(Purge_Schedules_by_Cum)'||debug_info);
5752 END IF;
5753 RETURN (TRUE);
5754
5755 RETURN NULL; EXCEPTION
5756 WHEN OTHERS THEN
5757 IF (SQLCODE < 0 ) THEN
5758 Print(SQLERRM);
5759 END IF;
5760 RETURN (FALSE);
5761 END Purge_Schedules_by_Cum;
5762
5763
5764 /*==========================================================================
5765 Function: Purge_Schedules_by_Org
5766
5767 *==========================================================================*/
5768 FUNCTION Purge_Schedules_by_Org
5769 (P_Calling_Sequence IN VARCHAR2)
5770 RETURN BOOLEAN IS
5771
5772 debug_info VARCHAR2(200);
5773 current_calling_sequence VARCHAR2(2000);
5774 chv_lower_limit NUMBER;
5775 chv_upper_limit NUMBER;
5776 range_high NUMBER;
5777 range_low NUMBER;
5778 range_size NUMBER;
5779
5780 l_count number := 0;
5781
5782 CURSOR range (low_chv_id IN NUMBER) IS
5783 SELECT schedule_item_id
5784 FROM chv_purge_schedule_list
5785 WHERE double_check_flag = 'Y'
5786 and schedule_item_id > low_chv_id
5787 ORDER BY schedule_item_id asc;
5788
5789
5790 BEGIN
5791
5792 -- Update the calling sequence
5793 --
5794
5795 current_calling_sequence := 'Purge_Schedules_by_Org<-'||P_Calling_Sequence;
5796
5797 --
5798 debug_info := 'Starting Purge_Schedules_by_Org';
5799 IF g_debug_switch in ('y','Y') THEN
5800 Print('(Purge_Schedules_by_Org)'||debug_info);
5801 END IF;
5802
5803 range_size := g_range_size;
5804
5805
5806 range_high := 0;
5807
5808 debug_info := 'get_chv_range';
5809 IF g_debug_switch in ('y','Y') THEN
5810 Print('(Purge_Schedules_by_Org)'||debug_info);
5811 END IF;
5812
5813 -- get_chv_range
5814
5815 select nvl(min(schedule_item_id),-1)
5816 , nvl(max(schedule_item_id),-1)
5817 into range_low, range_high
5818 from chv_purge_schedule_list
5819 where double_check_flag = 'Y';
5820
5821 OPEN range(range_low); --Bug2711759
5822 WHILE l_count < g_range_size
5823 LOOP
5824 FETCH range INTO range_high;
5825 EXIT WHEN range%NOTFOUND;
5826 l_count := l_count + 1;
5827 END LOOP;
5828 CLOSE RANGE;
5829
5830 LOOP
5831 debug_info := 'Updating a subgroup of Supplier Schedule Items';
5832 IF g_debug_switch in ('y','Y') THEN
5833 Print('(Purge_Schedules_by_Org)'||debug_info);
5834 END IF;
5835
5836 -- Update chv_schedule_items
5837
5838 update chv_schedule_items csi
5839 set csi.item_purge_status = 'PURGED'
5840 where exists
5841 (select null
5842 from chv_purge_schedule_list cpsl
5843 where cpsl.schedule_item_id = csi.schedule_item_id
5844 and cpsl.double_check_flag = 'Y'
5845 and cpsl.schedule_item_id between range_low and range_high);
5846
5847 debug_info := 'chv_item_orders';
5848 IF g_debug_switch in ('y','Y') THEN
5849 Print('(Purge_Schedules_by_Org)'||debug_info);
5850 END IF;
5851
5852 -- delete_chv_item_orders
5853
5854 delete from chv_item_orders cio
5855 where exists
5856 (select null
5857 from chv_purge_schedule_list cpsl
5858 where cpsl.schedule_item_id = cio.schedule_item_id
5859 and cpsl.double_check_flag = 'Y'
5860 and cpsl.schedule_item_id between range_low and range_high);
5861
5862 debug_info := 'po_lines';
5863 IF g_debug_switch in ('y','Y') THEN
5864 Print('(Purge_Schedules_by_Org)'||debug_info);
5865 END IF;
5866
5867 -- delete_chv_horizontal_schedules
5868
5869 delete from chv_horizontal_Schedules chs
5870 where exists
5871 (select null
5872 from chv_purge_schedule_list cpsl
5873 where cpsl.schedule_item_id = chs.schedule_item_id
5874 and cpsl.double_check_flag = 'Y'
5875 and cpsl.schedule_item_id between range_low and range_high);
5876
5877 debug_info := 'chv_authorizations';
5878 IF g_debug_switch in ('y','Y') THEN
5879 Print('(Purge_Schedules_by_Org)'||debug_info);
5880 END IF;
5881
5882 -- delete_chv_authorizations
5883
5884 delete from chv_authorizations ca
5885 where exists
5886 (select null
5887 from chv_purge_schedule_list cpsl
5888 where cpsl.schedule_item_id = ca.reference_id
5889 and ca.reference_type = 'SCHEDULE_ITEMS'
5890 and cpsl.double_check_flag = 'Y'
5891 and cpsl.schedule_item_id between range_low and range_high);
5892
5893 COMMIT;
5894
5895 l_count :=0;
5896
5897 range_low := range_high +1;
5898
5899 OPEN range(range_low); --Bug2711759
5900 WHILE l_count < g_range_size
5901 LOOP
5902 FETCH range INTO range_high;
5903 EXIT WHEN range%NOTFOUND;
5904 l_count := l_count + 1;
5905 END LOOP;
5906 CLOSE RANGE;
5907
5908 if range_low > range_high then
5909 EXIT;
5910 end if;
5911
5912 END LOOP;
5913
5914 COMMIT;
5915
5916 /* bug2067536 Performance Bug
5917 */
5918
5919 delete from chv_schedule_items csi
5920 where not exists (select null
5921 from chv_schedule_items cs
5922 where csi.schedule_id = cs.schedule_id
5923 and nvl(cs.item_purge_status,'ACTIVE') <> 'PURGED');
5924
5925 /* bug2067536 Performance Bug
5926 */
5927
5928 delete from chv_schedule_headers csh
5929 where not exists (select null
5930 from chv_schedule_items csi
5931 where csh.schedule_id = csi.schedule_id );
5932
5933 COMMIT;
5934
5935 debug_info := 'End Purge_Schedules_by_Org';
5936 IF g_debug_switch in ('y','Y') THEN
5937 Print('(Purge_Schedules_by_Org)'||debug_info);
5938 END IF;
5939 RETURN (TRUE);
5940
5941 RETURN NULL; EXCEPTION
5942 WHEN OTHERS THEN
5943 IF (SQLCODE < 0 ) THEN
5944 Print(SQLERRM);
5945 END IF;
5946 RETURN (FALSE);
5947 END Purge_Schedules_by_Org;
5948
5949
5950
5951 /*==========================================================================
5952 Function: Purge_Vendors
5953
5954 *==========================================================================*/
5955 FUNCTION Purge_Vendors
5956 (P_Calling_Sequence IN VARCHAR2)
5957 RETURN BOOLEAN IS
5958
5959 debug_info VARCHAR2(2000);
5960 current_calling_sequence VARCHAR2(2000);
5961 l_pos_dynamic_call VARCHAR2(2000);
5962 l_po_return_status VARCHAR2(1);
5963
5964 cursor c_purge_vendors IS
5965 select vendor_id
5966 from po_purge_vendor_list pvl
5967 where pvl.double_check_flag = 'Y';
5968
5969 cursor c_purge_vendor_sites IS
5970 select vendor_id,
5971 vendor_site_id
5972 from po_vendor_sites_all
5973 where vendor_id in (select vendor_id
5974 from po_purge_vendor_list pvl
5975 where pvl.double_check_flag = 'Y');
5976
5977 BEGIN
5978
5979 -- Update the calling sequence
5980 --
5981
5982 current_calling_sequence := 'Purge_Vendors<-'||P_Calling_Sequence;
5983
5984 --
5985
5986 debug_info := 'ap_suppliers';
5987 IF g_debug_switch in ('y','Y') THEN
5988 Print('(Purge_Vendors)'||debug_info);
5989 END IF;
5990
5991 -- delete_ap_suppliers
5992 delete from ap_suppliers vnd
5993 where exists
5994 (select null
5995 from po_purge_vendor_list pvl
5996 where pvl.vendor_id = vnd.vendor_id
5997 and pvl.double_check_flag = 'Y');
5998
5999 /* Bug 4602105: Commented out the call to etax preupgrade control packages
6000 -- Bug 3070584. Added the call to API for etax preupgrade control.
6001 FOR purge_vendors_rec IN c_purge_vendors
6002 LOOP
6003
6004 ZX_UPGRADE_CONTROL_PKG.Sync_Suppliers
6005 (P_Dml_Type => 'D',
6006 P_Vendor_ID => purge_vendors_rec.vendor_id);
6007
6008 END LOOP;
6009
6010 debug_info := 'ap_supplier_sites';
6011 IF g_debug_switch in ('y','Y') THEN
6012 Print('(Purge_Vendors)'||debug_info);
6013 END IF;
6014
6015 -- Bug 3070584. Added the call to API for etax preupgrade control.
6016 FOR purge_sites_rec IN c_purge_vendor_sites
6017 LOOP
6018
6019 ZX_UPGRADE_CONTROL_PKG.Sync_Supplier_Sites
6020 (P_Dml_Type => 'D',
6021 P_Vendor_Site_ID => purge_sites_rec.vendor_site_id,
6022 P_Vendor_ID => purge_sites_rec.vendor_id);
6023
6024 END LOOP;
6025 */
6026
6027 -- delete_ap_supplier_sites
6028 delete from ap_supplier_sites_all vnd
6029 where exists
6030 (select null
6031 from po_purge_vendor_list pvl
6032 where pvl.vendor_id = vnd.vendor_id
6033 and pvl.double_check_flag = 'Y');
6034
6035 debug_info := 'ap_supplier_contacts';
6036 IF g_debug_switch in ('y','Y') THEN
6037 Print('(Purge_Vendors)'||debug_info);
6038 END IF;
6039
6040 -- delete_ap_supplier_contacts
6041 delete from ap_supplier_contacts pc
6042 where not exists
6043 (select null
6044 from ap_supplier_sites_all ps
6045 where ps.vendor_site_id = pc.vendor_site_id);
6046
6047 -- bug 5008627. ap_bank_account_uses is obsolete
6048 /*
6049 debug_info := 'ap_bank_account_uses_all';
6050 IF g_debug_switch in ('y','Y') THEN
6051 Print('(Purge_Vendors)'||debug_info);
6052 END IF;
6053
6054 delete from ap_bank_account_uses_all abau
6055 where exists
6056 (select null
6057 from po_purge_vendor_list pvl
6058 where pvl.vendor_id = abau.vendor_id
6059 and pvl.double_check_flag = 'Y');
6060 */
6061 COMMIT;
6062
6063 IF g_purchasing_status = 'Y' THEN
6064
6065 debug_info := 'po_vendor_list_entries';
6066 IF g_debug_switch in ('y','Y') THEN
6067 Print('(Purge_Vendors)'||debug_info);
6068 END IF;
6069
6070 delete from po_vendor_list_entries pvle
6071 where not exists
6072 (select null
6073 from ap_suppliers vnd
6074 where vnd.vendor_id = pvle.vendor_id);
6075
6076 debug_info := 'po_vendor_list_headers';
6077 IF g_debug_switch in ('y','Y') THEN
6078 Print('(Purge_Vendors)'||debug_info);
6079 END IF;
6080
6081 delete from po_vendor_list_headers h
6082 where not exists
6083 (select null
6084 from po_vendor_list_entries e
6085 where e.vendor_list_header_id =
6086 h.vendor_list_header_id);
6087
6088 debug_info := 'po_asl_attributes';
6089 IF g_debug_switch in ('y','Y') THEN
6090 Print('(Purge_Vendors)'||debug_info);
6091 END IF;
6092
6093 -- delete po_asl_docments
6094
6095 delete from po_asl_documents pad where
6096 exists (select null from po_asl_attributes paa,
6097 po_purge_vendor_list pvl
6098 where pvl.vendor_id = paa.vendor_id
6099 and pvl.double_check_flag = 'Y'
6100 and paa.using_organization_id = pad.using_organization_id
6101 and paa.asl_id = pad.asl_id);
6102
6103 -- delete_po_asl_attributes
6104
6105 delete from po_asl_attributes paa
6106 where exists
6107 (select null
6108 from po_purge_vendor_list pvl
6109 where pvl.vendor_id = paa.vendor_id
6110 and pvl.double_check_flag = 'Y');
6111
6112 debug_info := 'po_approved_supplier_list';
6113 IF g_debug_switch in ('y','Y') THEN
6114 Print('(Purge_Vendors)'||debug_info);
6115 END IF;
6116
6117 -- delete_po_approved_supplier_list
6118
6119 delete from po_approved_supplier_list pasl
6120 where exists
6121 (select null
6122 from po_purge_vendor_list pvl
6123 where pvl.vendor_id = pasl.vendor_id
6124 and pvl.double_check_flag = 'Y');
6125
6126
6127 COMMIT;
6128 END IF;
6129
6130 -- Bug 3603357. Added POS API call to handle purge
6131 debug_info := 'Call to POS_SUP_PROF_PRG_GRP.handle_purge';
6132 IF g_debug_switch in ('y','Y') THEN
6133 Print('(Purge_Vendors)'||debug_info);
6134 END IF;
6135
6136 l_pos_dynamic_call :=
6137 'BEGIN
6138 POS_SUP_PROF_PRG_GRP.handle_purge (:l_return_status);
6139 END;';
6140
6141 BEGIN
6142 EXECUTE IMMEDIATE l_pos_dynamic_call
6143 USING OUT l_po_return_status;
6144
6145 debug_info := 'After call to POS handle_purge';
6146 IF g_debug_switch in ('y','Y') THEN
6147 Print('(Purge_Vendors)');
6148 END IF;
6149
6150 EXCEPTION
6151 WHEN OTHERS THEN
6152 IF (SQLCODE = -6550) THEN
6153 debug_info := 'Ignore exception from POS call. SQLERRM: '|| SQLERRM;
6154 IF g_debug_switch in ('y','Y') THEN
6155 Print('(Purge_Vendors)'||debug_info);
6156 END IF;
6157 ELSE
6158 RAISE;
6159 END IF;
6160 END;
6161
6162
6163 IF g_mrp_status = 'Y' THEN
6164 update mrp_sourcing_rules msr
6165 set planning_active = 2
6166 where exists (select null
6167 from po_purge_vendor_list pvl,
6168 mrp_sr_source_org msso,
6169 mrp_sr_receipt_org msro
6170 where pvl.vendor_id = msso.vendor_id
6171 and msso.sr_receipt_id = msro.sr_receipt_id
6172 and msro.sourcing_rule_id = msr.sourcing_rule_id
6173 and pvl.double_check_flag = 'Y');
6174
6175 update mrp_recommendations mr
6176 set source_vendor_id = null, source_vendor_site_id = null
6177 where exists (select null
6178 from po_purge_vendor_list pvl
6179 where pvl.vendor_id = mr.source_vendor_id
6180 and pvl.double_check_flag = 'Y');
6181
6182 delete from mrp_sr_source_org msso
6183 where exists (select null
6184 from po_purge_vendor_list pvl
6185 where pvl.vendor_id = msso.vendor_id
6186 and pvl.double_check_flag = 'Y');
6187
6188 delete from mrp_item_sourcing mis
6189 where exists (select null
6190 from po_purge_vendor_list pvl
6191 where pvl.vendor_id = mis.vendor_id
6192 and pvl.double_check_flag = 'Y');
6193
6194 COMMIT;
6195 END IF;
6196
6197 debug_info := 'End Purge_Vendors';
6198 IF g_debug_switch in ('y','Y') THEN
6199 Print('(Purge_Vendors)'||debug_info);
6200 END IF;
6201 RETURN(TRUE);
6202
6203 RETURN NULL; EXCEPTION
6204 WHEN OTHERS THEN
6205 IF (SQLCODE < 0 ) then
6206 Print(SQLERRM);
6207 END IF;
6208 RETURN (FALSE);
6209 END Purge_vendors;
6210
6211
6212 /*==========================================================================
6213 Function: Delete_Seeded_Data
6214
6215 *==========================================================================*/
6216 FUNCTION Delete_Seeded_Data
6217 (P_Purge_Name IN VARCHAR2,
6218 P_Category IN VARCHAR2,
6219 P_activity_Date IN DATE,
6220 P_Range_Size IN NUMBER,
6221 P_Purchasing_Status IN VARCHAR2,
6222 P_MRP_Status IN VARCHAR2,
6223 P_Debug_Switch IN VARCHAR2,
6224 P_Calling_Sequence IN VARCHAR2)
6225 RETURN BOOLEAN IS
6226
6227 debug_info VARCHAR2(200);
6228 current_calling_sequence VARCHAR2(2000);
6229 check_rows NUMBER;
6230 invoice_payment_rows NUMBER;
6231 invoice_rows NUMBER;
6232 po_header_rows NUMBER;
6233 shipment_line_rows NUMBER;
6234 req_header_rows NUMBER;
6235 vendor_rows NUMBER;
6236 po_asl_rows NUMBER;
6237 po_asl_attr_rows NUMBER;
6238 po_asl_doc_rows NUMBER;
6239 chv_auth_rows NUMBER;
6240 chv_cum_adj_rows NUMBER;
6241 chv_cum_rows NUMBER;
6242 chv_hor_rows NUMBER;
6243 chv_ord_rows NUMBER;
6244 chv_head_rows NUMBER;
6245 chv_item_rows NUMBER;
6246 ae_line_rows NUMBER;
6247 ae_header_rows NUMBER;
6248 accounting_event_rows NUMBER;
6249 chrg_allocation_rows NUMBER;
6250 payment_history_rows NUMBER;
6251 encumbrance_line_rows NUMBER;
6252 rcv_subledger_detail_rows NUMBER;
6253
6254
6255 l_status VARCHAR2(30);
6256 l_po_return_status VARCHAR2(1);
6257 l_po_msg VARCHAR2(2000);
6258
6259 BEGIN
6260
6261 -- Update the calling sequence
6262 --
6263
6264 g_debug_switch := p_debug_switch;
6265 g_activity_date := p_activity_date;
6266 g_range_size := p_range_size;
6267 g_purchasing_status := p_purchasing_status;
6268 g_mrp_status := p_mrp_status;
6269
6270
6271 current_calling_sequence := 'Delete_Seeded_Data<-'||P_Calling_Sequence;
6272
6273 --
6274
6275 debug_info := 'Starting Delete_Seeded_Data';
6276 IF g_debug_switch in ('y','Y') THEN
6277 Print('(Delete_Seeded_Data)'||debug_info);
6278 END IF;
6279
6280 IF p_category = 'SIMPLE INVOICES' then
6281
6282 -- delete_ap_tables
6283
6284 if (delete_ap_tables('Delete_Seeded_Data') <> TRUE) then
6285 Print('delete_ap_tables failed!');
6286 RETURN(FALSE);
6287 end if;
6288
6289 -- count_ap_rows
6290 if (count_ap_rows(check_rows,
6291 invoice_payment_rows,
6292 invoice_rows,
6293 'Confirm_Seeded_Data') <> TRUE) then
6294 Print('count_ap_row failed.' );
6295 return(FALSE);
6296 end if;
6297
6298 -- purge_accounting
6299 if (purge_accounting('Delete_Seeded_Data') <> TRUE) then
6300 Print('purge_accounting failed!');
6301 RETURN(FALSE);
6302 end if;
6303
6304 -- count_accounting_rows
6305
6306 if (count_accounting_rows(ae_line_rows,
6307 ae_header_rows,
6308 accounting_event_rows,
6309 chrg_allocation_rows,
6310 payment_history_rows,
6311 encumbrance_line_rows,
6312 rcv_subledger_detail_rows,
6313 'Confirm_Seeded_Data') <> TRUE) then
6314 Print('count_accounting_rows failed.' );
6315 return(FALSE);
6316 end if;
6317
6318 ELSIF p_category IN ('SIMPLE REQUISITIONS',
6319 'SIMPLE POS') then
6320
6321 PO_AP_PURGE_GRP.delete_records
6322 ( p_api_version => 1.0,
6323 p_init_msg_list => 'T',
6324 p_commit => 'T',
6325 x_return_status => l_po_return_status,
6326 x_msg_data => l_po_msg,
6327 p_purge_name => p_purge_name,
6328 p_purge_category => p_category,
6329 p_range_size => p_range_size);
6330
6331 IF (l_po_return_status <> 'S') THEN
6332 Print(l_po_msg);
6333 RETURN(FALSE);
6334 END IF;
6335
6336 PO_AP_PURGE_GRP.count_po_rows
6337 ( p_api_version => 1.0,
6338 p_init_msg_list => 'T',
6339 x_return_status => l_po_return_status,
6340 x_msg_data => l_po_msg,
6341 x_po_hdr_count => po_header_rows,
6342 x_rcv_line_count => shipment_line_rows,
6343 x_req_hdr_count => req_header_rows,
6344 x_vendor_count => vendor_rows,
6345 x_asl_count => po_asl_rows,
6346 x_asl_attr_count => po_asl_attr_rows,
6347 x_asl_doc_count => po_asl_doc_rows
6348 );
6349
6350 IF (l_po_return_status <> 'S') THEN
6351 Print(l_po_msg);
6352 RETURN FALSE;
6353 END IF;
6354
6355 ELSIF p_category = 'MATCHED POS AND INVOICES' then
6356
6357 -- delete_ap_tables
6358 if (delete_ap_tables('Delete_Seeded_Data') <> TRUE) then
6359 Print('delete_ap_tables failed!');
6360 RETURN(FALSE);
6361 end if;
6362
6363 PO_AP_PURGE_GRP.delete_records
6364 ( p_api_version => 1.0,
6365 p_init_msg_list => 'T',
6366 p_commit => 'T',
6367 x_return_status => l_po_return_status,
6368 x_msg_data => l_po_msg,
6369 p_purge_name => p_purge_name,
6370 p_purge_category => p_category,
6371 p_range_size => p_range_size);
6372
6373 IF (l_po_return_status <> 'S') THEN
6374 Print(l_po_msg);
6375 RETURN(FALSE);
6376 END IF;
6377
6378 if (count_ap_rows(check_rows,
6379 invoice_payment_rows,
6380 invoice_rows,
6381 'Confirm_Seeded_Data') <> TRUE) then
6382 Print('count_ap_row failed.' );
6383 return(FALSE);
6384 end if;
6385
6386 -- purge_accounting
6387 if (purge_accounting('Delete_Seeded_Data') <> TRUE) then
6388 Print('purge_accounting failed!');
6389 RETURN(FALSE);
6390 end if;
6391
6392 -- count_accounting_rows
6393 if (count_accounting_rows(ae_line_rows,
6394 ae_header_rows,
6395 accounting_event_rows,
6396 chrg_allocation_rows,
6397 payment_history_rows,
6398 encumbrance_line_rows,
6399 rcv_subledger_detail_rows,
6400 'Confirm_Seeded_Data') <> TRUE) then
6401 Print('count_accounting_rows failed.' );
6402 return(FALSE);
6403 end if;
6404
6405
6406 PO_AP_PURGE_GRP.count_po_rows
6407 ( p_api_version => 1.0,
6408 p_init_msg_list => 'T',
6409 x_return_status => l_po_return_status,
6410 x_msg_data => l_po_msg,
6411 x_po_hdr_count => po_header_rows,
6412 x_rcv_line_count => shipment_line_rows,
6413 x_req_hdr_count => req_header_rows,
6414 x_vendor_count => vendor_rows,
6415 x_asl_count => po_asl_rows,
6416 x_asl_attr_count => po_asl_attr_rows,
6417 x_asl_doc_count => po_asl_doc_rows
6418 );
6419
6420 IF (l_po_return_status <> 'S') THEN
6421 Print(l_po_msg);
6422 RETURN FALSE;
6423 END IF;
6424
6425 ELSIF p_category = 'VENDORS' then
6426
6427 -- purge_vendors
6428 if (purge_vendors('Delete_Seeded_Data') <> TRUE) then
6429 Print('purge_vendors failed!');
6430 RETURN(FALSE);
6431 end if;
6432
6433
6434 PO_AP_PURGE_GRP.count_po_rows
6435 ( p_api_version => 1.0,
6436 p_init_msg_list => 'T',
6437 x_return_status => l_po_return_status,
6438 x_msg_data => l_po_msg,
6439 x_po_hdr_count => po_header_rows,
6440 x_rcv_line_count => shipment_line_rows,
6441 x_req_hdr_count => req_header_rows,
6442 x_vendor_count => vendor_rows,
6443 x_asl_count => po_asl_rows,
6444 x_asl_attr_count => po_asl_attr_rows,
6445 x_asl_doc_count => po_asl_doc_rows
6446 );
6447
6448 IF (l_po_return_status <> 'S') THEN
6449 Print(l_po_msg);
6450 RETURN FALSE;
6451 END IF;
6452
6453 ELSIF p_category = 'SCHEDULES BY ORGANIZATION' then
6454
6455 -- purge_schedules
6456
6457 if (purge_schedules_by_org('Delete_Seeded_Data') <> TRUE) then
6458 Print('purge_schedules_by_org failed!');
6459 RETURN(FALSE);
6460 end if;
6461
6462 -- count_chv_rows
6463 if (count_chv_rows(chv_auth_rows,
6464 chv_cum_adj_rows,
6465 chv_cum_rows,
6466 chv_hor_rows,
6467 chv_ord_rows,
6468 chv_head_rows,
6469 chv_item_rows,
6470 'Delete Seeded Data')
6471 <> TRUE) then
6472 Print('purge_schedules_by_org failed!');
6473 RETURN(FALSE);
6474 end if;
6475
6476 ELSIF p_category = 'SCHEDULES BY CUM PERIODS' then
6477
6478 -- purge schedules
6479
6480 if (purge_schedules_by_cum('Delete_Seeded_Data') <> TRUE) then
6481 Print('purge_schedules_by_cum failed!');
6482 RETURN(FALSE);
6483 end if;
6484
6485
6486 -- count_chv_rows
6487 if (count_chv_rows(chv_auth_rows,
6488 chv_cum_adj_rows,
6489 chv_cum_rows,
6490 chv_hor_rows,
6491 chv_ord_rows,
6492 chv_head_rows,
6493 chv_item_rows,
6494 'Delete Seeded Data')
6495 <> TRUE) then
6496 Print('purge_schedules_by_cum failed!');
6497 RETURN(FALSE);
6498 end if;
6499 END IF;
6500
6501 -- record_final_statistics
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,
6511 po_approved_supplier_list = nvl(po_approved_supplier_list,0) - po_asl_rows,
6512 po_asl_attributes = nvl(po_asl_attributes,0) - po_asl_attr_rows,
6513 po_asl_documents = nvl(po_asl_documents,0) - po_asl_doc_rows,
6514 chv_authorizations = nvl(chv_authorizations,0) - chv_auth_rows,
6515 chv_cum_adjustments = nvl(chv_cum_adjustments,0) - chv_cum_adj_rows,
6516 chv_cum_periods = nvl(chv_cum_periods,0) - chv_cum_rows,
6517 chv_horizontal_Schedules = nvl(chv_horizontal_schedules,0) - chv_hor_rows,
6518 chv_item_orders = nvl(chv_item_orders,0) - chv_ord_rows,
6519 chv_schedule_headers = nvl(chv_schedule_headers,0) - chv_head_rows,
6520 chv_schedule_items = nvl(chv_schedule_items,0) - chv_item_rows,
6521 ap_ae_lines = nvl(ap_ae_lines,0) - ae_line_rows,
6522 ap_ae_headers = nvl(ap_ae_headers,0) - ae_header_rows,
6523 ap_accounting_events = nvl(ap_accounting_events,0) - accounting_event_rows
6524 WHERE purge_name = p_purge_name;
6525
6526 -- reset_row_counts
6527 check_rows := 0;
6528 invoice_payment_rows := 0;
6529 invoice_rows := 0;
6530 req_header_rows := 0;
6531 po_header_rows := 0;
6532 vendor_rows := 0;
6533 shipment_line_rows := 0;
6534 po_asl_rows := 0;
6535 po_asl_attr_rows := 0;
6536 po_asl_doc_rows := 0;
6537 chv_auth_rows := 0;
6538 chv_cum_adj_rows := 0;
6539 chv_cum_rows := 0;
6540 chv_hor_rows := 0;
6541 chv_ord_rows := 0;
6542 chv_head_rows := 0;
6543 chv_item_rows := 0;
6544 ae_line_rows := 0;
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',
6554 p_commit => 'F',
6555 x_return_status => l_po_return_status,
6556 x_msg_data => l_po_msg,
6557 p_purge_name => p_purge_name);
6558
6559 -- clear_vendor_purge_list
6560 delete from po_purge_vendor_list;
6561
6562 -- clear_schedule_list
6563 delete from chv_purge_schedule_list;
6564
6565 -- clear_cum_list
6566 delete from chv_purge_cum_list;
6567
6568 l_status := 'COMPLETED-PURGED';
6569
6570 -- set_purge_status
6571 if (set_purge_status(l_status,
6572 p_purge_name,
6573 p_debug_switch,
6574 'Delete_Seeded_Data')
6575 <> TRUE) then
6576 Print('set_purge_status failed!');
6577 RETURN(FALSE);
6578 end if;
6579 COMMIT;
6580 RETURN(TRUE);
6581
6582 RETURN NULL; EXCEPTION
6583 WHEN OTHERS THEN
6584 IF (SQLCODE < 0 ) then
6585 Print(SQLERRM);
6586 END IF;
6587 RETURN (FALSE);
6588
6589 END Delete_Seeded_Data;
6590
6591
6592 /*==========================================================================
6593 Function: clear_check_history
6594
6595 *==========================================================================*/
6596 FUNCTION clear_check_history RETURN BOOLEAN IS
6597
6598 BEGIN
6599
6600 delete from ap_history_checks
6601 where purge_name = g_purge_name;
6602
6603 RETURN (TRUE);
6604
6605 RETURN NULL; EXCEPTION
6606
6607 WHEN OTHERS THEN
6608 RETURN (FALSE);
6609
6610 END;
6611
6612
6613 /*==========================================================================
6614 Function: CLEAR_CHV_CUM_HISTORY
6615
6616 *==========================================================================*/
6617 FUNCTION CLEAR_CHV_CUM_HISTORY RETURN BOOLEAN IS
6618 BEGIN
6619 delete from chv_history_schedules
6620 where purge_name = g_purge_name;
6621
6622 delete from chv_history_cum_periods
6623 where purge_name = g_purge_name;
6624
6625 RETURN (TRUE);
6626
6627 RETURN NULL; EXCEPTION
6628
6629 WHEN OTHERS THEN
6630 RETURN (FALSE);
6631
6632 END;
6633
6634
6635 /*==========================================================================
6636 Function: CLEAR_CHV_CUM_LIST
6637
6638 *==========================================================================*/
6639 FUNCTION CLEAR_CHV_CUM_LIST RETURN BOOLEAN IS
6640 BEGIN
6641
6642 delete from chv_purge_schedule_list;
6643 delete from chv_purge_cum_list;
6644
6645 RETURN (TRUE);
6646
6647 RETURN NULL; EXCEPTION
6648
6649 WHEN OTHERS THEN
6650 RETURN (FALSE);
6651
6652 END;
6653
6654
6655 /*==========================================================================
6656 Function: CLEAR_CHV_SCHED_HISTORY
6657
6658 *==========================================================================*/
6659 FUNCTION CLEAR_CHV_SCHED_HISTORY RETURN BOOLEAN IS
6660 BEGIN
6661
6662 delete from chv_history_schedules
6663 where purge_name = g_purge_name;
6664
6665 RETURN (TRUE);
6666
6667 RETURN NULL; EXCEPTION
6668
6669 WHEN OTHERS THEN
6670 RETURN (FALSE);
6671
6672 END;
6673
6674
6675 /*==========================================================================
6676 Function: clear_chv_sched_list
6677
6678 *==========================================================================*/
6679 FUNCTION clear_chv_sched_list RETURN BOOLEAN IS
6680
6681 BEGIN
6682
6683 delete from chv_purge_schedule_list;
6684
6685 RETURN (TRUE);
6686
6687 RETURN NULL; EXCEPTION
6688
6689 WHEN OTHERS THEN
6690 RETURN (FALSE);
6691
6692 END;
6693
6694
6695 /*==========================================================================
6696 Function: clear_invoice_history
6697
6698 *==========================================================================*/
6699 FUNCTION clear_invoice_history RETURN BOOLEAN IS
6700
6701 BEGIN
6702
6703 delete from ap_history_invoices
6704 where purge_name = g_purge_name;
6705
6706 RETURN (TRUE);
6707
6708 RETURN NULL; EXCEPTION
6709
6710 WHEN OTHERS THEN
6711 RETURN (FALSE);
6712
6713 END;
6714
6715
6716 /*==========================================================================
6717 Function: clear_invoice_purge_list
6718
6719 *==========================================================================*/
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
6729
6730 WHEN OTHERS THEN
6731 RETURN (FALSE);
6732
6733 END;
6734
6735
6736 /*==========================================================================
6737 Function: clear_payment_history
6738
6739 *==========================================================================*/
6740 FUNCTION clear_payment_history RETURN BOOLEAN IS
6741
6742 BEGIN
6743
6744 delete from ap_history_invoice_payments ahp
6745 where not exists (select null
6746 from ap_history_invoices ahi
6747 where ahi.invoice_id = ahp.invoice_id);
6748
6749 RETURN (TRUE);
6750
6751 RETURN NULL; EXCEPTION
6752
6753 WHEN OTHERS THEN
6754 RETURN (FALSE);
6755
6756 END;
6757
6758
6759 /*==========================================================================
6760 Function: clear_vendor_history
6761
6762 *==========================================================================*/
6763 FUNCTION clear_vendor_history RETURN BOOLEAN IS
6764
6765 BEGIN
6766
6767 delete from po_history_vendors
6768 where purge_name = g_purge_name;
6769
6770 RETURN (TRUE);
6771
6772 RETURN NULL; EXCEPTION
6773
6774 WHEN OTHERS THEN
6775 RETURN (FALSE);
6776
6777 END;
6778
6779
6780 /*==========================================================================
6781 Function: clear_vendor_purge_list
6782
6783 *==========================================================================*/
6784 FUNCTION clear_vendor_purge_list RETURN BOOLEAN IS
6785
6786 BEGIN
6787
6788 delete from po_purge_vendor_list;
6789
6790 RETURN (TRUE);
6791
6792 RETURN NULL; EXCEPTION
6793
6794 WHEN OTHERS THEN
6795 RETURN (FALSE);
6796
6797 END;
6798
6799
6800 /*==========================================================================
6801 Function: Abort_Purge
6802
6803 *==========================================================================*/
6804 FUNCTION Abort_Purge
6805 (P_Purge_Name IN VARCHAR2,
6806 P_Original_Status IN VARCHAR2,
6807 P_Debug_Switch IN VARCHAR2,
6808 P_Calling_Sequence IN VARCHAR2)
6809 RETURN BOOLEAN IS
6810
6811 debug_info VARCHAR2(200);
6812 current_calling_sequence VARCHAR2(2000);
6813
6814 l_status VARCHAR2(30);
6815 l_po_return_status VARCHAR2(1);
6816 l_po_msg VARCHAR2(2000);
6817
6818 BEGIN
6819
6820 -- Update the calling sequence
6821 --
6822
6823 current_calling_sequence := 'Abort_Purge<-'||P_Calling_Sequence;
6824
6825 g_debug_switch := p_debug_switch;
6826 g_purge_name := p_purge_name;
6827
6828 --
6829 debug_info := 'Starting Abort_Purge';
6830 IF g_debug_switch in ('y','Y') THEN
6831 Print('(Abort_Purge)'||debug_info);
6832 END IF;
6833
6834
6835 IF(clear_invoice_purge_list <> TRUE) THEN
6836 RETURN (FALSE);
6837 END IF;
6838 COMMIT;
6839
6840 IF g_debug_switch in ('y','Y') THEN
6841 Print('(Abort_Purge)'||debug_info);
6842 END IF;
6843
6844 PO_AP_PURGE_GRP.delete_purge_lists
6845 ( p_api_version => 1.0,
6846 p_init_msg_list => 'T',
6847 p_commit => 'T',
6848 x_return_status => l_po_return_status,
6849 x_msg_data => l_po_msg,
6850 p_purge_name => p_purge_name);
6851
6852 IF (l_po_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
6853 RETURN (FALSE);
6854 END IF;
6855
6856
6857 IF(clear_vendor_purge_list <> TRUE) THEN
6858 RETURN (FALSE);
6859 END IF;
6860 COMMIT;
6861
6862 IF g_debug_switch in ('y','Y') THEN
6863 Print('(Abort_Purge)'||debug_info);
6864 END IF;
6865
6866 IF(clear_chv_sched_list <> TRUE) THEN
6867 RETURN (FALSE);
6868 END IF;
6869 COMMIT;
6870 IF g_debug_switch in ('y','Y') THEN
6871 Print('(Abort_Purge)'||debug_info);
6872 END IF;
6873
6874
6875 IF(clear_chv_cum_list <> TRUE) THEN
6876 RETURN (FALSE);
6877 END IF;
6878 COMMIT;
6879
6880 IF g_debug_switch in ('y','Y') THEN
6881 Print('(Abort_Purge)'||debug_info);
6882 END IF;
6883
6884 IF (p_original_status = 'SUMMARIZING' OR
6885 p_original_status = 'SUMMARIZED') THEN
6886
6887 PO_AP_PURGE_GRP.delete_history_tables
6888 ( p_api_version => 1.0,
6889 p_init_msg_list => 'T',
6890 p_commit => 'T',
6891 x_return_status => l_po_return_status,
6892 x_msg_data => l_po_msg,
6893 p_purge_name => p_purge_name);
6894
6895 IF (l_po_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
6896 RETURN (FALSE);
6897 END IF;
6898
6899 IF(clear_vendor_history <> TRUE) THEN
6900 RETURN (FALSE);
6901 END IF;
6902 COMMIT;
6903
6904 IF g_debug_switch in ('y','Y') THEN
6905 Print('(Abort_Purge)'||debug_info);
6906 END IF;
6907
6908 IF(clear_invoice_history <> TRUE) THEN
6909 RETURN (FALSE);
6910 END IF;
6911 COMMIT;
6912
6913 IF g_debug_switch in ('y','Y') THEN
6914 Print('(Abort_Purge)'||debug_info);
6915 END IF;
6916
6917 IF(clear_check_history <> TRUE) THEN
6918 RETURN (FALSE);
6919 END IF;
6920 COMMIT;
6921 IF g_debug_switch in ('y','Y') THEN
6922 Print('(Abort_Purge)'||debug_info);
6923 END IF;
6924
6925 IF(clear_payment_history <> TRUE) THEN
6926 RETURN (FALSE);
6927 END IF;
6928 COMMIT;
6929 IF g_debug_switch in ('y','Y') THEN
6930 Print('(Abort_Purge)'||debug_info);
6931 END IF;
6932
6933 IF(clear_chv_sched_history <> TRUE) THEN
6934 RETURN (FALSE);
6935 END IF;
6936 COMMIT;
6937 IF g_debug_switch in ('y','Y') THEN
6938 Print('(Abort_Purge)'||debug_info);
6939 END IF;
6940
6941 IF(clear_chv_cum_history <> TRUE) THEN
6942 RETURN (FALSE);
6943 END IF;
6944 COMMIT;
6945 IF g_debug_switch in ('y','Y') THEN
6946 Print('(Abort_Purge)'||debug_info);
6947 END IF;
6948
6949
6950 END IF;
6951
6952 l_status := 'COMPLETED-ABORTED';
6953 IF(set_purge_status(l_status,
6954 p_purge_name,
6955 p_debug_switch,
6956 'Abort_Purge') <> TRUE) THEN
6957 RETURN (FALSE);
6958 END IF;
6959 COMMIT;
6960 IF g_debug_switch in ('y','Y') THEN
6961 Print('(Abort_Purge)'||debug_info);
6962 END IF;
6963 Print('ABORT process commenced');
6964
6965 RETURN(TRUE);
6966
6967 RETURN NULL; EXCEPTION
6968 WHEN OTHERS THEN
6969 IF (SQLCODE < 0 ) then
6970 Print(SQLERRM);
6971 END IF;
6972 RETURN (FALSE);
6973
6974 END Abort_Purge;
6975
6976
6977 END AP_Purge_PKG;