1 PACKAGE BODY PO_AP_PURGE_GRP AS
2 /* $Header: POXGPUDB.pls 120.1 2005/06/29 18:36:25 shsiung noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'PO_AP_PURGE_GRP';
5
6 -- <DOC PURGE FPJ START>
7 g_fnd_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
8 G_MODULE_PREFIX CONSTANT VARCHAR2(40) := 'po.plsql.' || g_pkg_name || '.';
9 -- <DOC PURGE FPJ END>
10
11
12 INVALID_PO_HEADER_ID EXCEPTION;
13
14 /*=========================================================================*/
15 /*====================== SPECIFICATIONS (PRIVATE) =========================*/
16 /*=========================================================================*/
17
18 FUNCTION is_global_agreement
19 ( p_po_header_id IN PO_HEADERS.po_header_id%TYPE
20 ) RETURN BOOLEAN;
21
22 FUNCTION referencing_asl_exist
23 ( p_po_header_id IN PO_HEADERS_ALL.po_header_id%TYPE
24 ) RETURN BOOLEAN;
25
26 FUNCTION referencing_po_exist
27 ( p_po_header_id IN PO_HEADERS_ALL.po_header_id%TYPE
28 ) RETURN BOOLEAN;
29
30 FUNCTION referencing_req_exist
31 ( p_po_header_id IN PO_HEADERS_ALL.po_header_id%TYPE
32 ) RETURN BOOLEAN;
33
34 FUNCTION stdpo_ref_ga_check
35 ( p_po_header_id IN PO_HEADERS_ALL.po_header_id%TYPE
36 ) RETURN BOOLEAN;
37
38 /*=========================================================================*/
39 /*========================== BODY (PUBLIC) ================================*/
40 /*=========================================================================*/
41 /**==========================================================================
42 *
43 * PUBLIC FUNCTION: validate_purge <GA FPI>
44 *
45 * EFFECTS:
46 * Determines if it is ok to purge the given document.
47 * It is NOT ok to purge the given document if it is a Global Agreement
48 * and there exist any documents (in any status) in enabled OUs referencing
49 * the Global Agreement.
50 *
51 * RETURNS:
52 * FND_API.G_TRUE ('T') if it is ok to purge the given document.
53 * FND_API.G_FALSE ('F') otherwise.
54 *
55 *===========================================================================
56 */
57
58 FUNCTION validate_purge
59 (
60 p_po_header_id IN PO_HEADERS_ALL.po_header_id%TYPE
61 )
62 RETURN VARCHAR2
63 IS
64 BEGIN
65
66 IF ( is_global_agreement(p_po_header_id) ) THEN
67
68 IF ( PO_AP_PURGE_GRP.referencing_docs_exist(p_po_header_id) ) OR
69 ( referencing_asl_exist(p_po_header_id) ) THEN
70 -- IF no documents/Asl's reference
71 return (FND_API.G_FALSE); -- this GA from another OU
72 -- THEN, OK to purge document
73 ELSE -- ELSE, POs exist
74 -- and NOT OK to purge
75 return (FND_API.G_TRUE);
76
77 END IF;
78
79 ELSE -- if not a GA then check if its std PO referencing an open GA
80
81 IF ( stdpo_ref_ga_check (p_po_header_id) ) THEN -- Bug 2812416
82
83 return (FND_API.G_TRUE);
84
85 ELSE -- for all other cases OK to purge
86
87 return (FND_API.G_FALSE);
88
89 END IF;
90
91 END IF;
92
93 return (FND_API.G_TRUE);
94
95 EXCEPTION
96
97 WHEN OTHERS THEN
98 return (FND_API.G_FALSE);
99
100 END validate_purge;
101
102
103 /**==========================================================================
104 *
105 * PUBLIC PROCEDURE: purge <GA FPI>
106 *
107 * MODIFIES:
108 * API Message List - any messages will be appended to the API Message List
109 *
110 * EFFECTS:
111 * Purges all entries of the given Document ID from the Global Agreements'
112 * Organization Assignment table. (This procedure does not commit.)
113 *
114 * RETURNS:
115 * x_return_status - (a) FND_API.G_RET_STS_SUCCESS if purge successful
116 * (b) FND_API.G_RET_STS_ERROR if error during purge
117 * (c) FND_API.G_RET_STS_UNEXP_ERROR if unexpected error
118 *
119 *===========================================================================
120 */
121 PROCEDURE purge
122 (
123 p_api_version IN NUMBER,
124 x_return_status OUT NOCOPY VARCHAR2
125 )
126 IS
127 l_api_name CONSTANT VARCHAR2(30) := 'purge';
128 l_api_version CONSTANT NUMBER := 1.0;
129
130 l_module CONSTANT FND_LOG_MESSAGES.module%TYPE :=
131 G_MODULE_PREFIX || l_api_name || '.';
132
133 l_progress VARCHAR2(3);
134
135 -- bug3209532
136 l_pos_dynamic_call VARCHAR2(2000);
137
138 BEGIN
139
140 x_return_status := FND_API.G_RET_STS_SUCCESS; -- Initialize return status
141
142 IF ( NOT FND_API.compatible_api_call(l_api_version,p_api_version,l_api_name,G_PKG_NAME) ) THEN
143 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
144 END IF;
145
146 l_progress := '000';
147
148 DELETE FROM po_ga_org_assignments
149 WHERE po_header_id in (select po_header_id
150 from po_purge_po_list
151 where double_check_flag = 'Y');
152
153 l_progress := '010';
154
155 -- bug3209532
156 -- We need to use dynamic sql here because POS is not present in 11i
157 -- In order to avoid compilation error, we need to put in in dynamic
158 -- sql. If this dynamic sql is erroring out due to non-existence
159 -- of the package, ignore the error.
160
161 IF (g_fnd_debug = 'Y') THEN
162 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
163 FND_LOG.string
164 ( log_level => FND_LOG.LEVEL_STATEMENT,
165 module => l_module || l_progress,
166 message => 'Before calling POS Purge API'
167 );
168 END IF;
169 END IF;
170
171 -- Call the iSP API to handle events after purge
172 l_pos_dynamic_call :=
173 'BEGIN
174 POS_SUP_PROF_PRG_GRP.handle_purge (:l_return_status);
175 END;';
176
177 BEGIN
178 EXECUTE IMMEDIATE l_pos_dynamic_call
179 USING OUT x_return_status;
180 EXCEPTION
181 WHEN OTHERS THEN
182 IF (SQLCODE = -6550) THEN
183 x_return_status := FND_API.G_RET_STS_SUCCESS;
184
185 IF (g_fnd_debug = 'Y') THEN
186 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
187 FND_LOG.string
188 ( log_level => FND_LOG.LEVEL_STATEMENT,
189 module => l_module || l_progress,
190 message => 'Ignore exception from POS call. SQLERRM: '||
191 SQLERRM
192 );
193 END IF;
194 END IF;
195 ELSE
196 RAISE;
197 END IF;
198 END;
199
200 IF (g_fnd_debug = 'Y') THEN
201 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
202 FND_LOG.string
203 ( log_level => FND_LOG.LEVEL_PROCEDURE,
204 module => l_module || l_progress,
205 message => 'After calling POS Purge API'
206 );
207 END IF;
208 END IF;
209
210 /*
211 -- Call the iSP API to handle events after purge
212 POS_SUP_PROF_PRG_GRP.handle_purge (
213 x_return_status
214 );
215 */
216
217 EXCEPTION
218
219 WHEN FND_API.G_EXC_ERROR THEN
220 x_return_status := FND_API.G_RET_STS_ERROR;
221
222 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
223 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
224
225 WHEN OTHERS THEN
226 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
227
228 END purge;
229
230
231 /*=========================================================================*/
232 /*=========================== BODY (PRIVATE) ==============================*/
233 /*=========================================================================*/
234
235 /**==========================================================================
236 *
237 * PRIVATE FUNCTION: is_global_agreement <GA FPI>
238 *
239 * MODIFIES:
240 * API Message List
241 *
242 * EFFECTS:
243 * Determines if input document is a Global Agreement in the current OU.
244 *
245 * PARAMETERS:
246 * p_po_header_id - po_header_id of document
247 *
248 * RETURNS:
249 * TRUE if doc is a Global Agreement owned by current OU. FALSE otherwise.
250 *
251 * EXCEPTIONS:
252 * INVALID_PO_HEADER_ID - p_po_header_id was not found in PO_HEADERS table.
253 *
254 *===========================================================================
255 */
256 FUNCTION is_global_agreement
257 (
258 p_po_header_id IN PO_HEADERS.po_header_id%TYPE
259 )
260 RETURN BOOLEAN
261 IS
262 l_global_agreement_flag PO_HEADERS.global_agreement_flag%TYPE;
263 BEGIN
264
265 SELECT global_agreement_flag
266 INTO l_global_agreement_flag
267 FROM po_headers -- only look in current OU
268 WHERE po_header_id = p_po_header_id;
269
270 IF ( nvl(l_global_agreement_flag,'N') = 'Y' )
271 THEN
272 return (TRUE);
273 ELSE
274 return (FALSE);
275 END IF;
276
277 EXCEPTION
278
279 WHEN OTHERS THEN
280 return (FALSE);
281
282 END is_global_agreement;
283
284
285 /**==========================================================================
286 *
287 * FUNCTION: referencing_docs_exist <GA FPI>
288 *
289 * REQUIRES:
290 * p_po_header_id should correspond to a Global Agreement.
291 *
292 * EFFECTS:
293 * Determines if any documents - Standard POs, Requisitions, ASLs -
294 * (in any status and from any org) still reference the GA from another OU.
295 *
296 * PARAMETERS:
297 * p_po_header_id - Document ID of Global Agreement
298 *
299 * RETURNS:
300 * TRUE if referencing documents exist for the GA in another OU.
301 * FALSE otherwise.
302 *
303 *===========================================================================
304 */
305 FUNCTION referencing_docs_exist
306 (
307 p_po_header_id IN PO_HEADERS_ALL.po_header_id%TYPE
308 )
309 RETURN BOOLEAN
310 IS
311 BEGIN
312
313 IF ( ( referencing_po_exist(p_po_header_id) )
314 OR ( referencing_req_exist(p_po_header_id) ) )
315 THEN
316 return (TRUE);
317 ELSE
318 return (FALSE);
319 END IF;
320
321 END referencing_docs_exist;
322
323
324 /**==========================================================================
325 *
326 * FUNCTION: referencing_asl_exist <GA FPI>
327 *
328 * REQUIRES:
329 * p_po_header_id should correspond to a Global Agreement.
330 *
331 * EFFECTS:
332 * Determines if any ASLs (in any status and from any org)
333 * still reference the GA *from another OU*.
334 *
335 * PARAMETERS:
336 * p_po_header_id - Document ID of Global Agreement
337 *
338 * RETURNS:
339 * TRUE if referencing ASL exists for the GA. FALSE otherwise.
340 *
341 *===========================================================================
342 */
343 FUNCTION referencing_asl_exist
344 (
345 p_po_header_id IN PO_HEADERS_ALL.po_header_id%TYPE
346 )
347 RETURN BOOLEAN
348 IS
349 l_dummy VARCHAR2(20);
350 BEGIN
351
352 SELECT 'ASLs'
353 INTO l_dummy
354 FROM po_asl_documents pasl,
355 po_system_parameters psp
356 WHERE pasl.document_header_id = p_po_header_id;
357
358 return (TRUE); -- one record found for each doc type
359
360 EXCEPTION
361
362 WHEN NO_DATA_FOUND THEN -- no records founds
363 return (FALSE);
364
365 WHEN TOO_MANY_ROWS THEN -- multiple records founds
366 return (TRUE);
367
368 WHEN OTHERS THEN
369 return (FALSE);
370
371 END referencing_asl_exist;
372
373
374 /**==========================================================================
375 *
376 * FUNCTION: referencing_req_exist <GA FPI>
377 *
378 * REQUIRES:
379 * p_po_header_id should correspond to a Global Agreement.
380 *
381 * EFFECTS:
382 * Determines if any reqs (in any status and from any org)
383 * still reference the GA *from another OU*.
384 *
385 * PARAMETERS:
386 * p_po_header_id - Document ID of Global Agreement
387 *
388 * RETURNS:
389 * TRUE if referencing Reqs exists for the GA. FALSE otherwise.
390 *
391 *===========================================================================
392 */
393 FUNCTION referencing_req_exist
394 (
395 p_po_header_id IN PO_HEADERS_ALL.po_header_id%TYPE
396 )
397 RETURN BOOLEAN
398 IS
399 l_dummy VARCHAR2(20);
400 BEGIN
401
402 SELECT 'Requisitions'
403 INTO l_dummy
404 FROM po_requisition_lines_all prl,
405 po_system_parameters psp
406 WHERE prl.blanket_po_header_id = p_po_header_id
407 AND prl.org_id <> psp.org_id;
408
409 return (TRUE); -- one record found for each doc type
410
411 EXCEPTION
412
413 WHEN NO_DATA_FOUND THEN -- no records founds
414 return (FALSE);
415
416 WHEN TOO_MANY_ROWS THEN -- multiple records founds
417 return (TRUE);
418
419 WHEN OTHERS THEN
420 return (FALSE);
421
422 END referencing_req_exist;
423
424
425 /**==========================================================================
426 *
427 * FUNCTION: referencing_po_exist <GA FPI>
428 *
429 * REQUIRES:
430 * p_po_header_id should correspond to a Global Agreement.
434 * still reference the GA *from another OU*.
431 *
432 * EFFECTS:
433 * Determines if any PO's (in any status and from any org)
435 *
436 * PARAMETERS:
437 * p_po_header_id - Document ID of Global Agreement
438 *
439 * RETURNS:
440 * TRUE if referencing PO's exists for the GA. FALSE otherwise.
441 *
442 *===========================================================================
443 */
444 FUNCTION referencing_po_exist
445 (
446 p_po_header_id IN PO_HEADERS_ALL.po_header_id%TYPE
447 )
448 RETURN BOOLEAN
449 IS
450 l_dummy VARCHAR2(20);
451 BEGIN
452
453 SELECT 'Standard POs'
454 INTO l_dummy
455 FROM po_lines_all pol,
456 po_system_parameters psp
457 WHERE pol.from_header_id = p_po_header_id
458 AND pol.org_id <> psp.org_id;
459
460 return (TRUE); -- one record found for each doc type
461
462 EXCEPTION
463
464 WHEN NO_DATA_FOUND THEN -- no records founds
465 return (FALSE);
466
467 WHEN TOO_MANY_ROWS THEN -- multiple records founds
468 return (TRUE);
469
470 WHEN OTHERS THEN
471 return (FALSE);
472 END referencing_po_exist;
473
474 /**==========================================================================
475 *
476 * FUNCTION: stdpo_ref_open_ga <Bug 2812416>
477 *
478 * REQUIRES:
479 * p_po_header_id should correspond to a standard PO
480 *
481 * EFFECTS:
482 * Determines if this standard PO references any open GA from
483 * another OU or current OU
484 *
485 * PARAMETERS:
486 * p_po_header_id - Document ID of the std PO
487 *
488 * RETURNS:
489 * TRUE if if this standard PO references any open GA
490 *
491 *===========================================================================
492 */
493 FUNCTION stdpo_ref_ga_check
494 (
495 p_po_header_id IN PO_HEADERS_ALL.po_header_id%TYPE
496 )
497 RETURN BOOLEAN
498 IS
499 l_src_doc_id PO_LINES_ALL.from_header_id%TYPE;
500 l_ga_flag PO_HEADERS_ALL.global_agreement_flag%TYPE;
501 l_closed_code PO_HEADERS_ALL.closed_code%TYPE;
502 l_cancel_flag PO_HEADERS_ALL.cancel_flag%TYPE;
503 l_purge_doc varchar2(1) := 'Y';
504
505 CURSOR C1 is
506 SELECT distinct from_header_id
507 FROM po_lines
508 WHERE po_header_id = p_po_header_id
509 AND from_header_id is not null;
510
511 BEGIN
512
513 OPEN C1;
514 LOOP
515
516 -- get all the source documents referenced by the std PO
517
518 FETCH C1 into l_src_doc_id;
519 EXIT WHEN C1%NOTFOUND;
520
521 -- Sql What : check the type, closed status and cancel status of the source
522 -- Sql Why : To determine if the std PO referencing this is eligible for purge
523
524 SELECT nvl(ph.global_agreement_flag,'N'),
525 nvl(ph.closed_code,'OPEN'),
526 nvl(ph.cancel_flag,'N')
527 INTO l_ga_flag,
528 l_closed_code,
529 l_cancel_flag
530 FROM po_headers_all ph
531 WHERE ph.po_header_id = l_src_doc_id;
532
533 -- if the source is not a GA or if its finally closed/cancelled then
534 -- the std PO can be purged
535
536 IF l_ga_flag = 'N' OR
537 l_closed_code = 'FINALLY CLOSED' OR
538 l_cancel_flag = 'Y' THEN
539
540 l_purge_doc := 'Y';
541
542 -- if not you cannot purge. even if there is one line with a source doc
543 -- which does not satisfy the above conditions we do not purge and exit
544 -- out of the loop
545 ELSE
546 l_purge_doc := 'N';
547 exit;
548
549 END IF;
550
551
552 END LOOP;
553 CLOSE C1;
554
555 IF l_purge_doc = 'Y'
556 THEN
557 return (TRUE);
558 ELSE
559 return (FALSE);
560 END IF;
561
562 EXCEPTION
563
564 WHEN OTHERS THEN
565 CLOSE C1;
566 return (FALSE);
567
568 END stdpo_ref_ga_check;
569
570
571 -- <DOC PURGE FPJ START>
572 -----------------------------------------------------------------------
573 -------------------------- <DOC PURGE FPJ> ------------------------------
574 -----------------------------------------------------------------------
575
576 -----------------------------------------------------------------------
577 --Start of Comments
578 --Name: seed_records
579 --Pre-reqs: None
580 --Modifies:
581 --Locks:
582 -- None
583 --Function:
584 -- Based on the purge category, insert requisition or PO document header
585 -- information into purge list
586 --Parameters:
587 --IN:
588 --p_api_version
589 -- Version of the api the caller is assuming
590 --p_init_msg_list
591 -- FND_API.G_TRUE: initialize the message list
592 -- FND_API.G_FALSE: do not initialize the message list
593 --p_commit
594 -- Whether the API should commit
595 --p_purge_name
596 -- Name of this purge process
597 --p_purge_category
598 -- Purge Category
599 --p_last_activity_date
603 --x_return_status
600 -- cutoff date for a document to be purged
601 --IN OUT:
602 --OUT:
604 -- status of the procedure
605 --x_msg_data
606 -- return any error message
607 --Returns:
608 --Notes:
609 --Testing:
610 --End of Comments
611 ------------------------------------------------------------------------
612
613 PROCEDURE seed_records
614 ( p_api_version IN NUMBER,
615 p_init_msg_list IN VARCHAR2,
616 p_commit IN VARCHAR2,
617 x_return_status OUT NOCOPY VARCHAR2,
618 x_msg_data OUT NOCOPY VARCHAR2,
619 p_purge_name IN VARCHAR2,
620 p_purge_category IN VARCHAR2,
621 p_last_activity_date IN DATE
622 ) IS
623
624 l_api_name CONSTANT VARCHAR2(50) := 'seed_records';
625 l_api_version CONSTANT NUMBER := 1.0;
626 l_progress VARCHAR2(3);
627 l_module CONSTANT FND_LOG_MESSAGES.module%TYPE :=
628 G_MODULE_PREFIX || l_api_name || '.';
629 BEGIN
630
631 l_progress := '000';
632
633 IF (g_fnd_debug = 'Y') THEN
634 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
635 FND_LOG.string
636 ( log_level => FND_LOG.LEVEL_PROCEDURE,
637 module => l_module || l_progress,
638 message => 'Entering ' || g_pkg_name || '.' || l_api_name
639 );
640 END IF;
641 END IF;
642
643 SAVEPOINT seed_records_grp;
644
645 IF (FND_API.to_boolean (p_init_msg_list)) THEN
646 FND_MSG_PUB.initialize;
647 END IF;
648
649 IF (NOT FND_API.Compatible_API_Call
650 ( p_current_version_number => l_api_version,
651 p_caller_version_number => p_api_version,
652 p_api_name => l_api_name,
653 p_pkg_name => g_pkg_name
654 )
655 ) THEN
656
657 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
658 END IF;
659
660 x_return_status := FND_API.G_RET_STS_SUCCESS;
661
662 l_progress := '010';
663
664 PO_AP_PURGE_PVT.seed_records
665 ( p_api_version => 1.0,
666 p_init_msg_list => FND_API.G_FALSE,
667 p_commit => p_commit,
668 x_return_status => x_return_status,
669 x_msg_data => x_msg_data,
670 p_purge_name => p_purge_name,
671 p_purge_category => p_purge_category,
672 p_last_activity_date => p_last_activity_date
673 );
674
675 l_progress := '020';
676
677 IF (g_fnd_debug = 'Y') THEN
678 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
679 FND_LOG.string
680 ( log_level => FND_LOG.LEVEL_PROCEDURE,
681 module => l_module || l_progress,
682 message => 'Quitting ' || l_api_name
683 );
684 END IF;
685 END IF;
686
687 EXCEPTION
688 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
689 ROLLBACK TO seed_records_grp;
690 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
691 x_msg_data := FND_MSG_PUB.get (p_encoded => 'F');
692
693 WHEN OTHERS THEN
694 ROLLBACK TO seed_records_grp;
695 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
696 FND_MSG_PUB.add_exc_msg
697 ( p_pkg_name => g_pkg_name,
698 p_procedure_name => l_api_name || '.' || l_progress
699 );
700 x_msg_data := FND_MSG_PUB.get (p_encoded => 'F');
701
702 END seed_records;
703
704
705 -----------------------------------------------------------------------
706 --Start of Comments
707 --Name: filter_records
708 --Pre-reqs: seed_recores is already called
709 -- set_product_inst_status is already called
710 --Modifies:
711 --Locks:
712 -- None
713 --Function:
714 -- Eliminate records from the purge list if they do not satisfies
715 -- additional purge criteria
716 --Parameters:
717 --IN:
718 --p_api_version
719 -- Version of the api the caller is assuming
720 --p_init_msg_list
721 -- FND_API.G_TRUE: initialize the message list
725 -- FND_API.G_FALSE: do not commit changes
722 -- FND_API.G_FALSE: do not initialize the message list
723 --p_commit
724 -- FND_API.G_TRUE: commit changes
726 --p_purge_status
727 -- Current stage of the purge process
728 --p_purge_name
729 -- Name of this purge process
730 --p_purge_category
731 -- Purge Category
732 --p_action
733 -- Applicable when purge category = 'MATCHED PO AND INVOICES'.
734 -- Possible values:
735 -- 'FILTER REF PO AND REQ': remove PO/REQ from the purge list if they
736 -- are referenced by other products
737 -- 'FILTER DEPENDENT PO AND REQ': Filter Purge list so that for all
738 -- PO/CONTRACT/REQ remaining in the purge list, all the dependent
739 -- PO/CONTRACT/REQ are in the purge list as well
740 -- 'FILTER DEPENDENT PO AND AP': Remove PO from purge list if the
741 -- corresponding invoice is not in the purge list
742 --IN OUT:
743 --OUT:
744 --x_return_status
745 -- status of the procedure
746 --x_msg_data
747 -- return any error msg
748 --x_records_filtered
749 -- indicate whether there is any PO getting excluded after this call
750 -- Applicable for purge category = 'MATCHED PO AND INVOICES', when
751 -- p_action is 'FILTER DEPENDENT PO AND REQ' or 'FILTER DEPENDENT PO AND AP'
752 --Returns:
753 --Notes:
754 --Testing:
755 --End of Comments
756 ------------------------------------------------------------------------
757
758
759 PROCEDURE filter_records
760 ( p_api_version IN NUMBER,
761 p_init_msg_list IN VARCHAR2,
762 p_commit IN VARCHAR2,
763 x_return_status OUT NOCOPY VARCHAR2,
764 x_msg_data OUT NOCOPY VARCHAR2,
765 p_purge_status IN VARCHAR2,
766 p_purge_name IN VARCHAR2,
767 p_purge_category IN VARCHAR2,
768 p_action IN VARCHAR2,
769 x_po_records_filtered OUT NOCOPY VARCHAR2
770 ) IS
771
772 l_api_name CONSTANT VARCHAR2(50) := 'filter_records';
773 l_api_version CONSTANT NUMBER := 1.0;
774 l_progress VARCHAR2(3);
775 l_module CONSTANT FND_LOG_MESSAGES.module%TYPE :=
776 G_MODULE_PREFIX || l_api_name || '.';
777
778 BEGIN
779
780 l_progress := '000';
781
782 IF (g_fnd_debug = 'Y') THEN
783 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
784 FND_LOG.string
785 ( log_level => FND_LOG.LEVEL_PROCEDURE,
786 module => l_module || l_progress,
787 message => 'Entering ' || g_pkg_name || '.' || l_api_name
788 );
789 END IF;
790 END IF;
791
792 SAVEPOINT filter_records_grp;
793
794 IF (FND_API.to_boolean (p_init_msg_list)) THEN
795 FND_MSG_PUB.initialize;
796 END IF;
797
798 IF (NOT FND_API.Compatible_API_Call
799 ( p_current_version_number => l_api_version,
800 p_caller_version_number => p_api_version,
801 p_api_name => l_api_name,
802 p_pkg_name => g_pkg_name
803 )
804 ) THEN
805
806 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
807 END IF;
808
809 x_return_status := FND_API.G_RET_STS_SUCCESS;
810
811 l_progress := '010';
812
813 PO_AP_PURGE_PVT.filter_records
814 ( p_api_version => 1.0,
815 p_init_msg_list => FND_API.G_FALSE,
816 p_commit => p_commit,
817 x_return_status => x_return_status,
818 x_msg_data => x_msg_data,
819 p_purge_status => p_purge_status,
823 x_po_records_filtered => x_po_records_filtered
820 p_purge_name => p_purge_name,
821 p_purge_category => p_purge_category,
822 p_action => p_action,
824 );
825
826 l_progress := '020';
827
828 IF (g_fnd_debug = 'Y') THEN
829 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
830 FND_LOG.string
831 ( log_level => FND_LOG.LEVEL_PROCEDURE,
832 module => l_module || l_progress,
833 message => 'Quitting ' || l_api_name
834 );
835 END IF;
836 END IF;
837
838 EXCEPTION
839 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
840 ROLLBACK TO filter_records_grp;
841 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
842 x_msg_data := FND_MSG_PUB.get (p_encoded => 'F');
843
844 WHEN OTHERS THEN
845 ROLLBACK TO filter_records_grp;
846 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
847 FND_MSG_PUB.add_exc_msg
848 ( p_pkg_name => g_pkg_name,
849 p_procedure_name => l_api_name || '.' || l_progress
850 );
851 x_msg_data := FND_MSG_PUB.get( p_encoded => 'F');
852
853 END filter_records;
854
855
856
857
858 -----------------------------------------------------------------------
859 --Start of Comments
860 --Name: confirm_records
861 --Pre-reqs: It is only called from AP Purge program during confirmation
862 -- stage
863 --Modifies:
864 --Locks:
865 -- None
866 --Function:
867 -- Remove records from the purge list by setting double_check_flag = 'Y'
868 -- if the records do not satisfy the initial purge criteria anymore
869 --Parameters:
870 --IN:
871 --p_api_version
872 -- Version of the api the caller is assuming
873 --p_init_msg_list
874 -- FND_API.G_TRUE: initialize the message list
875 -- FND_API.G_FALSE: do not initialize the message list
876 --p_commit
877 -- FND_API.G_TRUE: procedure should commit
878 -- FND_API.G_FALSE: procedure should not commit
879 --p_purge_name
880 -- Name of the purge
881 --p_purge_category
882 -- Purge Category
883 --p_last_activity_date
884 -- Cutoff date for a document to be purged
885 --IN OUT:
886 --OUT:
887 --x_return_status
888 -- status of the procedure
889 --x_msg_data
890 -- This parameter will be not null if an error happens
891 --Returns:
892 --Notes:
893 --Testing:
894 --End of Comments
895 ------------------------------------------------------------------------
896 PROCEDURE confirm_records
897 ( p_api_version IN NUMBER,
898 p_init_msg_list IN VARCHAR2,
899 p_commit IN VARCHAR2,
900 x_return_status OUT NOCOPY VARCHAR2,
901 x_msg_data OUT NOCOPY VARCHAR2,
902 p_purge_name IN VARCHAR2,
903 p_purge_category IN VARCHAR2,
904 p_last_activity_date IN DATE
905 ) IS
906
907 l_api_name CONSTANT VARCHAR2(50) := 'confirm_records';
908 l_api_version CONSTANT NUMBER := 1.0;
909 l_progress VARCHAR2(3);
910 l_module CONSTANT FND_LOG_MESSAGES.module%TYPE :=
911 G_MODULE_PREFIX || l_api_name || '.';
912
913 BEGIN
914
915 l_progress := '000';
916
917 IF (g_fnd_debug = 'Y') THEN
918 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
919 FND_LOG.string
923 );
920 ( log_level => FND_LOG.LEVEL_PROCEDURE,
921 module => l_module || l_progress,
922 message => 'Entering ' || g_pkg_name || '.' || l_api_name
924 END IF;
925 END IF;
926
927 SAVEPOINT confirm_records_grp;
928
929 IF (FND_API.to_boolean (p_init_msg_list)) THEN
930 FND_MSG_PUB.initialize;
931 END IF;
932
933 IF (NOT FND_API.Compatible_API_Call
934 ( p_current_version_number => l_api_version,
935 p_caller_version_number => p_api_version,
936 p_api_name => l_api_name,
937 p_pkg_name => g_pkg_name
938 )
939 ) THEN
940
941 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
942 END IF;
943
944 x_return_status := FND_API.G_RET_STS_SUCCESS;
945
946 l_progress := '010';
947
948 PO_AP_PURGE_PVT.confirm_records
949 ( p_api_version => 1.0,
950 p_init_msg_list => FND_API.G_FALSE,
951 p_commit => p_commit,
952 x_return_status => x_return_status,
953 x_msg_data => x_msg_data,
954 p_purge_name => p_purge_name,
955 p_purge_category => p_purge_category,
956 p_last_activity_date => p_last_activity_date
957 );
958
959 l_progress := '020';
960
961 IF (g_fnd_debug = 'Y') THEN
962 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
963 FND_LOG.string
964 ( log_level => FND_LOG.LEVEL_PROCEDURE,
965 module => l_module || l_progress,
966 message => 'Quitting ' || l_api_name
967 );
968 END IF;
969 END IF;
970
971 EXCEPTION
972 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
973 ROLLBACK TO confirm_records_grp;
974 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
975 x_msg_data := FND_MSG_PUB.get (p_encoded => 'F');
976
977 WHEN OTHERS THEN
978 ROLLBACK TO confirm_records_grp;
979 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
980 FND_MSG_PUB.add_exc_msg
981 ( p_pkg_name => g_pkg_name,
982 p_procedure_name => l_api_name || '.' || l_progress
983 );
984 x_msg_data := FND_MSG_PUB.get (p_encoded => 'F');
985
986
987 END confirm_records;
988
989
990 -----------------------------------------------------------------------
991 --Start of Comments
992 --Name: summarize_records
993 --Pre-reqs: It is only called from AP Purge program during summarization
994 -- stage
995 --Modifies:
996 --Locks:
997 -- None
998 --Function:
999 -- Insert the document information into history tables before actual
1000 -- deletion happens. Documents to be recorded include reqs, pos and
1001 -- receipts
1002 --Parameters:
1003 --IN:
1004 --p_api_version
1005 -- Version of the api the caller is assuming
1006 --p_init_msg_list
1007 -- FND_API.G_TRUE: initialize the message list
1008 -- FND_API.G_FALSE: do not initialize the message list
1009 --p_commit
1010 -- FND_API.G_TRUE: procedure should commit
1011 -- FND_API.G_FALSE: procedure should not commit
1012 --p_purge_name
1013 -- Name of the purge
1014 --p_purge_category
1015 -- Purge Category
1019 --IN OUT:
1016 --p_range_size
1017 -- This program inserts data in batches. This parameter specifies
1018 -- the size of the id range per commit cycle
1020 --OUT:
1021 --x_return_status
1022 -- status of the procedure
1023 --x_msg_data
1024 -- This parameter will be not null if an error happens
1025 --Returns:
1026 --Notes:
1027 --Testing:
1028 --End of Comments
1029 ------------------------------------------------------------------------
1030
1031 PROCEDURE summarize_records
1032 ( p_api_version IN NUMBER,
1033 p_init_msg_list IN VARCHAR2,
1034 p_commit IN VARCHAR2,
1035 x_return_status OUT NOCOPY VARCHAR2,
1036 x_msg_data OUT NOCOPY VARCHAR2,
1037 p_purge_name IN VARCHAR2,
1038 p_purge_category IN VARCHAR2,
1039 p_range_size IN NUMBER
1040 ) IS
1041
1042 l_api_name CONSTANT VARCHAR2(50) := 'summarize_records';
1043 l_api_version CONSTANT NUMBER := 1.0;
1044 l_progress VARCHAR2(3);
1045 l_module CONSTANT FND_LOG_MESSAGES.module%TYPE :=
1046 G_MODULE_PREFIX || l_api_name || '.';
1047
1048 BEGIN
1049
1050 l_progress := '000';
1051
1052 IF (g_fnd_debug = 'Y') THEN
1053 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1054 FND_LOG.string
1055 ( log_level => FND_LOG.LEVEL_PROCEDURE,
1056 module => l_module || l_progress,
1057 message => 'Entering ' || g_pkg_name || '.' || l_api_name
1058 );
1059 END IF;
1060 END IF;
1061
1062 IF (FND_API.to_boolean (p_init_msg_list)) THEN
1063 FND_MSG_PUB.initialize;
1064 END IF;
1065
1066 IF (NOT FND_API.Compatible_API_Call
1067 ( p_current_version_number => l_api_version,
1068 p_caller_version_number => p_api_version,
1069 p_api_name => l_api_name,
1070 p_pkg_name => g_pkg_name
1071 )
1072 ) THEN
1073
1074 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1075 END IF;
1076
1077 x_return_status := FND_API.G_RET_STS_SUCCESS;
1078
1079 l_progress := '010';
1080
1081 PO_AP_PURGE_PVT.summarize_records
1082 ( p_api_version => 1.0,
1083 p_init_msg_list => FND_API.G_FALSE,
1084 p_commit => p_commit,
1085 x_return_status => x_return_status,
1086 x_msg_data => x_msg_data,
1087 p_purge_name => p_purge_name,
1088 p_purge_category => p_purge_category,
1089 p_range_size => p_range_size
1090 );
1091
1092 l_progress := '020';
1093
1094 IF (g_fnd_debug = 'Y') THEN
1095 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1096 FND_LOG.string
1097 ( log_level => FND_LOG.LEVEL_PROCEDURE,
1098 module => l_module || l_progress,
1099 message => 'Quitting ' || l_api_name
1103
1100 );
1101 END IF;
1102 END IF;
1104 EXCEPTION
1105 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1106 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1107 x_msg_data := FND_MSG_PUB.get (p_encoded => 'F');
1108
1109 WHEN OTHERS THEN
1110 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1111 FND_MSG_PUB.add_exc_msg
1112 ( p_pkg_name => g_pkg_name,
1113 p_procedure_name => l_api_name || '.' || l_progress
1114 );
1115 x_msg_data := FND_MSG_PUB.get (p_encoded => 'F');
1116
1117 END summarize_records;
1118
1119
1120 -----------------------------------------------------------------------
1121 --Start of Comments
1122 --Name: delete_records
1123 --Pre-reqs: It is only called from AP Purge program during deletion
1124 -- stage
1125 --Modifies:
1126 --Locks:
1127 -- None
1128 --Function:
1129 -- Delete REQ, PO and RCV transaction tables if the corresponding
1130 -- documents are in the purge list
1131 --Parameters:
1132 --IN:
1133 --p_api_version
1134 -- Version of the api the caller is assuming
1135 --p_init_msg_list
1136 -- FND_API.G_TRUE: initialize the message list
1137 -- FND_API.G_FALSE: do not initialize the message list
1138 --p_commit
1139 -- FND_API.G_TRUE: procedure should commit
1140 -- FND_API.G_FALSE: procedure should not commit
1141 --p_purge_name
1142 -- Name of the purge
1143 --p_purge_category
1144 -- Purge Category
1145 --p_range_size
1146 -- This program deletes data in batches. This parameter specifies
1147 -- the number of documents to be purged per commit cycle
1148 --IN OUT:
1149 --OUT:
1150 --x_return_status
1151 -- status of the procedure
1152 --x_msg_data
1153 -- This parameter will be not null if an error happens
1154 --Returns:
1155 --Notes:
1156 --Testing:
1157 --End of Comments
1158 ------------------------------------------------------------------------
1159
1160 PROCEDURE delete_records
1161 ( p_api_version IN NUMBER,
1162 p_init_msg_list IN VARCHAR2,
1163 p_commit IN VARCHAR2,
1164 x_return_status OUT NOCOPY VARCHAR2,
1165 x_msg_data OUT NOCOPY VARCHAR2,
1166 p_purge_name IN VARCHAR2,
1167 p_purge_category IN VARCHAR2,
1168 p_range_size IN NUMBER
1169 ) IS
1170
1171 l_api_name CONSTANT VARCHAR2(50) := 'delete_records';
1172 l_api_version CONSTANT NUMBER := 1.0;
1173 l_progress VARCHAR2(3);
1174 l_module CONSTANT FND_LOG_MESSAGES.module%TYPE :=
1175 G_MODULE_PREFIX || l_api_name || '.';
1176
1177 BEGIN
1178
1179 l_progress := '000';
1180
1181 IF (g_fnd_debug = 'Y') THEN
1182 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1183 FND_LOG.string
1184 ( log_level => FND_LOG.LEVEL_PROCEDURE,
1185 module => l_module || l_progress,
1186 message => 'Entering ' || g_pkg_name || '.' || l_api_name
1187 );
1188 END IF;
1189 END IF;
1190
1191 IF (FND_API.to_boolean (p_init_msg_list)) THEN
1192 FND_MSG_PUB.initialize;
1193 END IF;
1194
1195 IF (NOT FND_API.Compatible_API_Call
1196 ( p_current_version_number => l_api_version,
1197 p_caller_version_number => p_api_version,
1198 p_api_name => l_api_name,
1199 p_pkg_name => g_pkg_name
1200 )
1201 ) THEN
1202
1203 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1204 END IF;
1205
1206 x_return_status := FND_API.G_RET_STS_SUCCESS;
1207
1208 l_progress := '010';
1209
1210 PO_AP_PURGE_PVT.delete_records
1211 ( p_api_version => 1.0,
1212 p_init_msg_list => FND_API.G_FALSE,
1213 p_commit => p_commit,
1214 x_return_status => x_return_status,
1215 x_msg_data => x_msg_data,
1216 p_purge_name => p_purge_name,
1217 p_purge_category => p_purge_category,
1218 p_range_size => p_range_size
1219 );
1220
1221 l_progress := '020';
1222
1223 IF (g_fnd_debug = 'Y') THEN
1224 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1225 FND_LOG.string
1226 ( log_level => FND_LOG.LEVEL_PROCEDURE,
1227 module => l_module || l_progress,
1228 message => 'Quitting ' || l_api_name
1229 );
1230 END IF;
1231 END IF;
1232
1233 EXCEPTION
1234 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1235 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1236 x_msg_data := FND_MSG_PUB.get (p_encoded => 'F');
1237
1238 WHEN OTHERS THEN
1239 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1240 FND_MSG_PUB.add_exc_msg
1241 ( p_pkg_name => g_pkg_name,
1242 p_procedure_name => l_api_name || '.' || l_progress
1243 );
1244 x_msg_data := FND_MSG_PUB.get (p_encoded => 'F');
1245
1246 END delete_records;
1247
1251 --Name: delete_purge_list
1248
1249 -----------------------------------------------------------------------
1250 --Start of Comments
1252 --Pre-reqs: It is only called from AP Purge program during summarization
1253 -- or abortion stage
1254 --Modifies:
1255 --Locks:
1256 -- None
1257 --Function:
1258 -- Truncate REQ/PO purge list
1259 --Parameters:
1260 --IN:
1261 --p_api_version
1262 -- Version of the api the caller is assuming
1263 --p_init_msg_list
1264 -- FND_API.G_TRUE: initialize the message list
1265 -- FND_API.G_FALSE: do not initialize the message list
1266 --p_commit
1267 -- FND_API.G_TRUE: procedure should commit
1268 -- FND_API.G_FALSE: procedure should not commit
1269 --p_purge_name
1270 -- Name of the purge
1271 --IN OUT:
1272 --OUT:
1273 --x_return_status
1274 -- status of the procedure
1275 --x_msg_data
1276 -- This parameter will be not null if an error happens
1277 --Returns:
1278 --Notes:
1279 --Testing:
1280 --End of Comments
1281 ------------------------------------------------------------------------
1282
1283 PROCEDURE delete_purge_lists
1284 ( p_api_version IN NUMBER,
1285 p_init_msg_list IN VARCHAR2,
1286 p_commit IN VARCHAR2,
1287 x_return_status OUT NOCOPY VARCHAR2,
1288 x_msg_data OUT NOCOPY VARCHAR2,
1289 p_purge_name IN VARCHAR2
1290 ) IS
1291
1292 l_api_name CONSTANT VARCHAR2(50) := 'delete_purge_lists';
1293 l_api_version CONSTANT NUMBER := 1.0;
1294 l_progress VARCHAR2(3);
1295 l_module CONSTANT FND_LOG_MESSAGES.module%TYPE :=
1296 G_MODULE_PREFIX || l_api_name || '.';
1297
1298 BEGIN
1299
1300 l_progress := '000';
1301
1302 IF (g_fnd_debug = 'Y') THEN
1303 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1304 FND_LOG.string
1305 ( log_level => FND_LOG.LEVEL_PROCEDURE,
1306 module => l_module || l_progress,
1307 message => 'Entering ' || g_pkg_name || '.' || l_api_name
1308 );
1309 END IF;
1310 END IF;
1311
1312 SAVEPOINT delete_purge_lists_grp;
1313
1314 IF (FND_API.to_boolean (p_init_msg_list)) THEN
1315 FND_MSG_PUB.initialize;
1316 END IF;
1317
1318 IF (NOT FND_API.Compatible_API_Call
1319 ( p_current_version_number => l_api_version,
1320 p_caller_version_number => p_api_version,
1321 p_api_name => l_api_name,
1322 p_pkg_name => g_pkg_name
1323 )
1324 ) THEN
1325
1326 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1327 END IF;
1328
1329 x_return_status := FND_API.G_RET_STS_SUCCESS;
1330
1331 l_progress := '010';
1332
1333 PO_AP_PURGE_PVT.delete_purge_lists
1334 ( p_api_version => 1.0,
1335 p_init_msg_list => FND_API.G_FALSE,
1336 p_commit => p_commit,
1337 x_return_status => x_return_status,
1338 x_msg_data => x_msg_data,
1339 p_purge_name => p_purge_name
1340 );
1341
1342 l_progress := '020';
1343
1344 IF (g_fnd_debug = 'Y') THEN
1345 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1346 FND_LOG.string
1347 ( log_level => FND_LOG.LEVEL_PROCEDURE,
1348 module => l_module || l_progress,
1349 message => 'Quitting ' || l_api_name
1350 );
1351 END IF;
1352 END IF;
1353
1354 EXCEPTION
1355 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1356 ROLLBACK TO delete_purge_lists_grp;
1357 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1358 x_msg_data := FND_MSG_PUB.get (p_encoded => 'F');
1359
1360 WHEN OTHERS THEN
1361 ROLLBACK TO delete_purge_lists_grp;
1362 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1363 FND_MSG_PUB.add_exc_msg
1364 ( p_pkg_name => g_pkg_name,
1365 p_procedure_name => l_api_name || '.' || l_progress
1366 );
1367 x_msg_data := FND_MSG_PUB.get (p_encoded => 'F');
1368
1369 END delete_purge_lists;
1370
1371
1372 -----------------------------------------------------------------------
1373 --Start of Comments
1374 --Name: delete_history_tables
1375 --Pre-reqs: It is only called from AP Purge program during abort
1376 -- stage
1377 --Modifies:
1378 --Locks:
1379 -- None
1380 --Function:
1381 -- Delete records from history tables that were inserted by the current
1382 -- purge process (identified by p_purge_name)
1383 --Parameters:
1384 --IN:
1385 --p_api_version
1386 -- Version of the api the caller is assuming
1387 --p_init_msg_list
1388 -- FND_API.G_TRUE: initialize the message list
1389 -- FND_API.G_FALSE: do not initialize the message list
1390 --p_commit
1391 -- FND_API.G_TRUE: procedure should commit
1392 -- FND_API.G_FALSE: procedure should not commit
1393 --p_purge_name
1394 -- Name of the purge
1395 --IN OUT:
1396 --OUT:
1397 --x_return_status
1398 -- status of the procedure
1399 --x_msg_data
1400 -- This parameter will be not null if an error happens
1401 --Returns:
1402 --Notes:
1403 --Testing:
1407 PROCEDURE delete_history_tables
1404 --End of Comments
1405 ------------------------------------------------------------------------
1406
1408 ( p_api_version IN NUMBER,
1409 p_init_msg_list IN VARCHAR2,
1410 p_commit IN VARCHAR2,
1411 x_return_status OUT NOCOPY VARCHAR2,
1412 x_msg_data OUT NOCOPY VARCHAR2,
1413 p_purge_name IN VARCHAR2
1414 ) IS
1415
1416 l_api_name CONSTANT VARCHAR2(50) := 'delete_history_tables';
1417 l_api_version CONSTANT NUMBER := 1.0;
1418 l_progress VARCHAR2(3);
1419 l_module CONSTANT FND_LOG_MESSAGES.module%TYPE :=
1420 G_MODULE_PREFIX || l_api_name || '.';
1421
1422 BEGIN
1423
1424 l_progress := '000';
1425
1426 IF (g_fnd_debug = 'Y') THEN
1427 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1428 FND_LOG.string
1429 ( log_level => FND_LOG.LEVEL_PROCEDURE,
1430 module => l_module || l_progress,
1431 message => 'Entering ' || g_pkg_name || '.' || l_api_name
1432 );
1433 END IF;
1434 END IF;
1435
1436 SAVEPOINT delete_history_tables_grp;
1437
1438 IF (FND_API.to_boolean (p_init_msg_list)) THEN
1439 FND_MSG_PUB.initialize;
1440 END IF;
1441
1442 IF (NOT FND_API.Compatible_API_Call
1443 ( p_current_version_number => l_api_version,
1444 p_caller_version_number => p_api_version,
1445 p_api_name => l_api_name,
1446 p_pkg_name => g_pkg_name
1447 )
1448 ) THEN
1449
1450 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1451 END IF;
1452
1453 x_return_status := FND_API.G_RET_STS_SUCCESS;
1454
1455 l_progress := '010';
1456
1457 PO_AP_PURGE_PVT.delete_history_tables
1458 ( p_api_version => 1.0,
1459 p_init_msg_list => FND_API.G_FALSE,
1460 p_commit => p_commit,
1461 x_return_status => x_return_status,
1462 x_msg_data => x_msg_data,
1463 p_purge_name => p_purge_name
1464 );
1465
1466 l_progress := '020';
1467
1468 IF (g_fnd_debug = 'Y') THEN
1469 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1470 FND_LOG.string
1471 ( log_level => FND_LOG.LEVEL_PROCEDURE,
1472 module => l_module || l_progress,
1473 message => 'Quitting ' || l_api_name
1474 );
1475 END IF;
1476 END IF;
1477
1478 EXCEPTION
1479 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1480 ROLLBACK TO delete_history_tables_grp;
1481 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1482 x_msg_data := FND_MSG_PUB.get (p_encoded => 'F');
1483
1484 WHEN OTHERS THEN
1485 ROLLBACK TO delete_history_tables_grp;
1486 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1487 FND_MSG_PUB.add_exc_msg
1488 ( p_pkg_name => g_pkg_name,
1489 p_procedure_name => l_api_name || '.' || l_progress
1490 );
1491 x_msg_data := FND_MSG_PUB.get (p_encoded => 'F');
1492
1493 END delete_history_tables;
1494
1495
1496
1497 -----------------------------------------------------------------------
1498 --Start of Comments
1499 --Name: count_po_rows
1500 --Pre-reqs:
1501 --Modifies:
1502 --Locks:
1503 -- None
1504 --Function: Get the count of some PO transaction tables for reporting
1505 --Parameters:
1506 --p_api_version
1507 -- Version of the api the caller is assuming
1508 --p_init_msg_list
1509 -- FND_API.G_TRUE: initialize the message list
1510 -- FND_API.G_FALSE: do not initialize the message list
1511 --IN OUT:
1512 --OUT:
1513 --x_return_status
1514 -- status of the procedure
1515 --x_msg_data
1516 -- This parameter will be not null if an error happens
1520 -- Number of records in rcv_shipment_lines
1517 --x_po_hdr_coun
1518 -- Number of records in po_headers
1519 --x_rcv_line_count
1521 --x_req_hdr_count
1522 -- Number of records in po_requisition_headers
1523 --x_vendor_count
1524 -- Number of records in po_vendors
1525 --x_asl_count
1526 -- Number of records in po_approved_supplier_list
1527 --x_asl_attr_count
1528 -- Number of records in po_asl_attributes
1529 --x_asl_doc_count
1530 -- Number of records in po_asl_documents
1531 --Returns:
1532 --Notes:
1533 --Testing:
1534 --End of Comments
1535 ------------------------------------------------------------------------
1536
1537 PROCEDURE count_po_rows
1538 ( p_api_version IN NUMBER,
1539 p_init_msg_list IN VARCHAR2,
1540 x_return_status OUT NOCOPY VARCHAR2,
1541 x_msg_data OUT NOCOPY VARCHAR2,
1542 x_po_hdr_count OUT NOCOPY NUMBER,
1543 x_rcv_line_count OUT NOCOPY NUMBER,
1544 x_req_hdr_count OUT NOCOPY NUMBER,
1545 x_vendor_count OUT NOCOPY NUMBER,
1546 x_asl_count OUT NOCOPY NUMBER,
1547 x_asl_attr_count OUT NOCOPY NUMBER,
1548 x_asl_doc_count OUT NOCOPY NUMBER
1549 ) IS
1550
1551 l_api_name CONSTANT VARCHAR2(50) := 'count_po_rows';
1552 l_api_version CONSTANT NUMBER := 1.0;
1553 l_progress VARCHAR2(3);
1554 l_module CONSTANT FND_LOG_MESSAGES.module%TYPE :=
1555 G_MODULE_PREFIX || l_api_name || '.';
1556
1557 BEGIN
1558
1559 l_progress := '000';
1560
1561 IF (g_fnd_debug = 'Y') THEN
1562 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1563 FND_LOG.string
1564 ( log_level => FND_LOG.LEVEL_PROCEDURE,
1565 module => l_module || l_progress,
1566 message => 'Entering ' || g_pkg_name || '.' || l_api_name
1567 );
1568 END IF;
1569 END IF;
1570
1571 IF (FND_API.to_boolean (p_init_msg_list)) THEN
1572 FND_MSG_PUB.initialize;
1573 END IF;
1574
1575 IF (NOT FND_API.Compatible_API_Call
1576 ( p_current_version_number => l_api_version,
1577 p_caller_version_number => p_api_version,
1578 p_api_name => l_api_name,
1579 p_pkg_name => g_pkg_name
1580 )
1581 ) THEN
1582
1583 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1584 END IF;
1585
1586 x_return_status := FND_API.G_RET_STS_SUCCESS;
1587
1588 l_progress := '010';
1589
1590 PO_AP_PURGE_PVT.count_po_rows
1591 ( p_api_version => 1.0,
1592 p_init_msg_list => FND_API.G_FALSE,
1593 x_return_status => x_return_status,
1594 x_msg_data => x_msg_data,
1595 x_po_hdr_count => x_po_hdr_count,
1596 x_rcv_line_count => x_rcv_line_count,
1597 x_req_hdr_count => x_req_hdr_count,
1598 x_vendor_count => x_vendor_count,
1599 x_asl_count => x_asl_count,
1600 x_asl_attr_count => x_asl_attr_count,
1601 x_asl_doc_count => x_asl_doc_count
1602 );
1603
1604 l_progress := '020';
1605
1606 IF (g_fnd_debug = 'Y') THEN
1607 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1608 FND_LOG.string
1609 ( log_level => FND_LOG.LEVEL_PROCEDURE,
1610 module => l_module || l_progress,
1611 message => 'Quitting ' || l_api_name
1612 );
1613 END IF;
1614 END IF;
1615
1616 EXCEPTION
1617 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1618 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1619 x_msg_data := FND_MSG_PUB.get (p_encoded => 'F');
1620
1621 WHEN OTHERS THEN
1622 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1623 FND_MSG_PUB.add_exc_msg
1624 ( p_pkg_name => g_pkg_name,
1625 p_procedure_name => l_api_name || '.' || l_progress
1626 );
1627 x_msg_data := FND_MSG_PUB.get (p_encoded => 'F');
1628
1629 END count_po_rows;
1630
1631 -- <DOC PURGE FPJ END>
1632
1633
1634 END PO_AP_PURGE_GRP;
[Home] [Help]