[Home] [Help]
PACKAGE BODY: APPS.PO_AP_PURGE_PVT
Source
1 PACKAGE BODY PO_AP_PURGE_PVT AS
2 /* $Header: POXVPUDB.pls 120.7 2006/08/08 00:29:02 bao noship $ */
3
4 -- <DOC PURGE FPJ START>
5
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'PO_AP_PURGE_PVT';
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
10 -- product installation status
11 g_inv_install_status VARCHAR2(1);
12 g_wip_install_status VARCHAR2(1);
13 g_mrp_install_status VARCHAR2(1);
14 g_chv_install_status VARCHAR2(1);
15 g_pa_install_status VARCHAR2(1);
16 g_pjm_install_status VARCHAR2(1);
17 g_set_product_inst_status VARCHAR2(1) := FND_API.G_FALSE;
18
19
20 --*********************************************************************
21 ----------------- Private Procedure Prototypes-------------------------
22 --*********************************************************************
23
24 PROCEDURE seed_simple_req
25 ( x_return_status OUT NOCOPY VARCHAR2,
26 p_purge_name IN VARCHAR2,
27 p_last_activity_date IN DATE
28 );
29
30 PROCEDURE seed_po
31 ( x_return_status OUT NOCOPY VARCHAR2,
32 p_purge_category IN VARCHAR2,
33 p_purge_name IN VARCHAR2,
34 p_last_activity_date IN DATE
35 );
36
37 PROCEDURE set_product_inst_status
38 ( x_return_status OUT NOCOPY VARCHAR2
39 );
40
41 PROCEDURE get_installation_status
42 ( x_return_status OUT NOCOPY VARCHAR2,
43 p_appl_id IN NUMBER,
44 x_inst_status OUT NOCOPY VARCHAR2
45 );
46
47 PROCEDURE filter_referenced_req
48 ( x_return_status OUT NOCOPY VARCHAR2
49 );
50
51 PROCEDURE filter_referenced_po
52 ( x_return_status OUT NOCOPY VARCHAR2
53 );
54
55 PROCEDURE filter_dependent_po_req_list
56 ( x_return_status OUT NOCOPY VARCHAR2,
57 x_po_records_filtered OUT NOCOPY VARCHAR2
58 );
59
60 PROCEDURE filter_dependent_po_ap_list
61 ( x_return_status OUT NOCOPY VARCHAR2,
62 x_po_records_filtered OUT NOCOPY VARCHAR2
63 );
64
65 PROCEDURE remove_filtered_records
66 ( x_return_status OUT NOCOPY VARCHAR2,
67 p_purge_category IN VARCHAR2
68 );
69
70 PROCEDURE confirm_simple_req
71 ( x_return_status OUT NOCOPY VARCHAR2,
72 p_last_activity_date IN DATE
73 );
74
75 PROCEDURE confirm_po
76 ( x_return_status OUT NOCOPY VARCHAR2,
77 p_purge_category IN VARCHAR2,
78 p_last_activity_date IN DATE
79 );
80
81 PROCEDURE get_purge_list_range
82 ( x_return_status OUT NOCOPY VARCHAR2,
83 p_category IN VARCHAR2,
84 x_lower_limit OUT NOCOPY NUMBER,
85 x_upper_limit OUT NOCOPY NUMBER
86 );
87
88 PROCEDURE summarize_reqs
89 ( x_return_status OUT NOCOPY VARCHAR2,
90 p_purge_name IN VARCHAR2,
91 p_range_size IN NUMBER,
92 p_req_lower_limit IN NUMBER,
93 p_req_upper_limit IN NUMBER
94 );
95
96 PROCEDURE summarize_pos
97 ( x_return_status OUT NOCOPY VARCHAR2,
98 p_purge_name IN VARCHAR2,
99 p_range_size IN NUMBER,
100 p_po_lower_limit IN NUMBER,
101 p_po_upper_limit IN NUMBER
102 );
103
104 PROCEDURE delete_reqs
105 ( x_return_status OUT NOCOPY VARCHAR2,
106 p_range_size IN NUMBER,
107 p_req_lower_limit IN NUMBER,
108 p_req_upper_limit IN NUMBER
109 );
110
111 PROCEDURE delete_pos
112 ( x_return_status OUT NOCOPY VARCHAR2,
113 p_range_size IN NUMBER,
114 p_po_lower_limit IN NUMBER,
115 p_po_upper_limit IN NUMBER
116 );
117
118 --bug3256316
119 --Dump All messages to FND_LOG
120
121 PROCEDURE dump_msg_to_log
122 ( p_module IN VARCHAR2
123 );
124
125 --*********************************************************************
126 -------------------------- Public Procedures --------------------------
127 --*********************************************************************
128
129 -----------------------------------------------------------------------
130 --Start of Comments
131 --Name: seed_records
132 --Pre-reqs: None
133 --Modifies:
134 --Locks:
135 -- None
136 --Function:
137 -- Based on the purge category, insert requisition or PO document header
138 -- information into purge list
139 --Parameters:
140 --IN:
141 --p_api_version
142 -- Version of the api the caller is assuming
143 --p_init_msg_list
144 -- FND_API.G_TRUE: initialize the message list
145 -- FND_API.G_FALSE: do not initialize the message list
146 --p_commit
147 -- Whether the API should commit
148 --p_purge_name
149 -- Name of this purge process
150 --p_purge_category
151 -- Purge Category
152 --p_last_activity_date
153 -- cutoff date for a document to be purged
154 --IN OUT:
155 --OUT:
156 --x_return_status
157 -- status of the procedure
158 --x_msg_data
159 -- return any error message
160 --Returns:
161 --Notes:
162 --Testing:
163 --End of Comments
164 ------------------------------------------------------------------------
165 PROCEDURE seed_records
166 ( p_api_version IN NUMBER,
167 p_init_msg_list IN VARCHAR2,
168 p_commit IN VARCHAR2,
169 x_return_status OUT NOCOPY VARCHAR2,
170 x_msg_data OUT NOCOPY VARCHAR2,
171 p_purge_name IN VARCHAR2,
172 p_purge_category IN VARCHAR2,
173 p_last_activity_date IN DATE
174 ) IS
175
176 l_api_name CONSTANT VARCHAR2(50) := 'seed_records';
177 l_api_version CONSTANT NUMBER := 1.0;
178 l_progress VARCHAR2(3);
179 l_module CONSTANT FND_LOG_MESSAGES.module%TYPE :=
180 G_MODULE_PREFIX || l_api_name || '.';
181 l_msg_idx NUMBER;
182
183 BEGIN
184
185 l_progress := '000';
186
187 IF (g_fnd_debug = 'Y') THEN
188 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
189 FND_LOG.string
190 ( log_level => FND_LOG.LEVEL_PROCEDURE,
191 module => l_module || l_progress,
192 message => 'Entering ' || g_pkg_name || '.' || l_api_name
193 );
194 END IF;
195 END IF;
196
197 SAVEPOINT seed_records_pvt;
198
199 IF (FND_API.to_boolean(p_init_msg_list)) THEN
200 FND_MSG_PUB.initialize;
201 END IF;
202
203 l_msg_idx := FND_MSG_PUB.count_msg + 1;
204
205 IF (NOT FND_API.Compatible_API_Call
206 ( p_current_version_number => l_api_version,
207 p_caller_version_number => p_api_version,
208 p_api_name => l_api_name,
209 p_pkg_name => g_pkg_name
210 )
211 ) THEN
212
213 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
214 END IF; -- not compatible_api
215
216 x_return_status := FND_API.G_RET_STS_SUCCESS;
217
218 l_progress := '010';
219
220 IF (p_purge_category = PO_AP_PURGE_GRP.G_PUR_CAT_SIMPLE_REQ) THEN
221
222 l_progress := '020';
223
224 seed_simple_req
225 ( x_return_status => x_return_status,
226 p_purge_name => p_purge_name,
227 p_last_activity_date => p_last_activity_date
228 );
229
230 ELSIF (p_purge_category IN (PO_AP_PURGE_GRP.G_PUR_CAT_SIMPLE_PO,
231 PO_AP_PURGE_GRP.G_PUR_CAT_MATCHED_PO_INV)) THEN
232
233 l_progress := '030';
234
235 seed_po
236 ( x_return_status => x_return_status,
237 p_purge_category => p_purge_category,
238 p_purge_name => p_purge_name,
239 p_last_activity_date => p_last_activity_date
240 );
241
242 ELSE
243 -- wrong purge_category
244 l_progress := '040';
245
246 FND_MSG_PUB.add_exc_msg
247 ( p_pkg_name => g_pkg_name,
248 p_procedure_name => l_api_name || '.' || l_progress,
249 p_error_text => l_progress || ': Param Mismatch' );
250
251 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
252
253 END IF; -- p_purge_category
254
255 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
256
257 l_progress := '050';
258 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
259
260 END IF;
261
262 IF (FND_API.to_boolean(p_commit)) THEN
263
264 l_progress := '060';
265 COMMIT;
266
267 END IF;
268
269 IF (g_fnd_debug = 'Y') THEN
270 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
271 FND_LOG.string
272 ( log_level => FND_LOG.LEVEL_PROCEDURE,
273 module => l_module || l_progress,
274 message => 'Quitting ' || g_pkg_name || '.' || l_api_name
275 );
276 END IF;
277 END IF;
278
279 EXCEPTION
280 WHEN OTHERS THEN
281 ROLLBACK TO seed_records_pvt;
282 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
283 FND_MSG_PUB.add_exc_msg
284 ( p_pkg_name => g_pkg_name,
285 p_procedure_name => l_api_name || '.' || l_progress
286
287 );
288 x_msg_data := FND_MSG_PUB.get(p_msg_index => l_msg_idx,
289 p_encoded => 'F');
290 dump_msg_to_log( p_module => l_module || l_progress );
291 END seed_records;
292
293
294 -----------------------------------------------------------------------
295 --Start of Comments
296 --Name: filter_records
297 --Pre-reqs: seed_recores is already called
298 -- set_product_inst_status is already called
299 --Modifies:
300 --Locks:
301 -- None
302 --Function:
303 -- Eliminate records from the purge list if they do not satisfies
304 -- additional purge criteria
305 --Parameters:
306 --IN:
307 --p_api_version
308 -- Version of the api the caller is assuming
309 --p_init_msg_list
310 -- FND_API.G_TRUE: initialize the message list
311 -- FND_API.G_FALSE: do not initialize the message list
312 --p_commit
313 -- FND_API.G_TRUE: commit changes
314 -- FND_API.G_FALSE: do not commit changes
315 --p_purge_status
316 -- Current stage of the purge process
317 --p_purge_name
318 -- Name of this purge process
319 --p_purge_category
320 -- Purge Category
321 --p_action
322 -- Applicable when purge category = 'MATCHED PO AND INVOICES'.
323 -- Possible values:
324 -- 'FILTER REF PO AND REQ': remove PO/REQ from the purge list if they
325 -- are referenced by other products
326 -- 'FILTER DEPENDENT PO AND REQ': Filter Purge list so that for all
327 -- PO/CONTRACT/REQ remaining in the purge list, all the dependent
328 -- PO/CONTRACT/REQ are in the purge list as well
329 -- 'FILTER DEPENDENT PO AND AP': Remove PO from purge list if the
330 -- corresponding invoice is not in the purge list
331 --IN OUT:
332 --OUT:
333 --x_return_status
334 -- status of the procedure
335 --x_msg_data
336 -- return any error msg
337 --x_records_filtered
338 -- indicate whether there is any PO getting excluded after this call
339 -- Applicable for purge category = 'MATCHED PO AND INVOICES', when
340 -- p_action is 'FILTER DEPENDENT PO AND REQ' or 'FILTER DEPENDENT PO AND AP'
341 --Returns:
342 --Notes:
343 --Testing:
344 --End of Comments
345 ------------------------------------------------------------------------
346
347 PROCEDURE filter_records
348 ( p_api_version IN NUMBER,
349 p_init_msg_list IN VARCHAR2,
350 p_commit IN VARCHAR2,
351 x_return_status OUT NOCOPY VARCHAR2,
352 x_msg_data OUT NOCOPY VARCHAR2,
353 p_purge_status IN VARCHAR2,
354 p_purge_name IN VARCHAR2,
355 p_purge_category IN VARCHAR2,
356 p_action IN VARCHAR2,
357 x_po_records_filtered OUT NOCOPY VARCHAR2
358 ) IS
359
360 l_api_name CONSTANT VARCHAR2(50) := 'filter_records';
361 l_api_version CONSTANT NUMBER := 1.0;
362 l_progress VARCHAR2(3);
363 l_return_status VARCHAR2(1);
364 l_module CONSTANT FND_LOG_MESSAGES.module%TYPE :=
365 G_MODULE_PREFIX || l_api_name || '.';
366 l_msg_idx NUMBER;
367
368 BEGIN
369
370 l_progress := '000';
371
372 IF (g_fnd_debug = 'Y') THEN
373 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
374 FND_LOG.string
375 ( log_level => FND_LOG.LEVEL_PROCEDURE,
376 module => l_module || l_progress,
377 message => 'Entering ' || g_pkg_name || '.' || l_api_name
378 );
379 END IF;
380 END IF;
381
382 SAVEPOINT filter_records_pvt;
383
384 IF (FND_API.to_boolean(p_init_msg_list)) THEN
385 FND_MSG_PUB.initialize;
386 END IF;
387
388 l_msg_idx := FND_MSG_PUB.count_msg + 1;
389
390 IF (NOT FND_API.Compatible_API_Call
391 ( p_current_version_number => l_api_version,
392 p_caller_version_number => p_api_version,
393 p_api_name => l_api_name,
394 p_pkg_name => g_pkg_name
395 )
396 ) THEN
397
398 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
399 END IF; -- not compatible_api
400
401 x_return_status := FND_API.G_RET_STS_SUCCESS;
402
403 x_po_records_filtered := FND_API.G_FALSE;
404
405 l_progress := '010';
406
407 -- if product installation statuses have not been set, set them
408 IF (NOT FND_API.to_boolean(g_set_product_inst_status)) THEN
409 l_progress := '020';
410
411 set_product_inst_status
412 ( x_return_status => l_return_status
413 );
414
415 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
416 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
417 END IF;
418 END IF; -- not g_set_product_inst_status
419
420 IF (p_purge_category = PO_AP_PURGE_GRP.G_PUR_CAT_SIMPLE_REQ) THEN
421 l_progress := '030';
422
423 filter_referenced_req (x_return_status => l_return_status );
424
425 ELSIF (p_purge_category = PO_AP_PURGE_GRP.G_PUR_CAT_SIMPLE_PO) THEN
426 l_progress := '040';
427
428 filter_referenced_req (x_return_status => l_return_status);
429
430 IF (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
431
432 filter_referenced_po (x_return_status => l_return_status);
433 END IF;
434
435 IF (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
436
437 filter_dependent_po_req_list
438 ( x_return_status => l_return_status,
439 x_po_records_filtered => x_po_records_filtered);
440 END IF;
441
442 ELSIF (p_purge_category = PO_AP_PURGE_GRP.G_PUR_CAT_MATCHED_PO_INV) THEN
443 l_progress := '050';
444
445 IF (p_action = PO_AP_PURGE_GRP.G_FILTER_ACT_REF_PO_REQ) THEN
446 l_progress := '060';
447
448 filter_referenced_req
449 ( x_return_status => l_return_status );
450
451 IF (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
452 l_progress := '070';
453
454 filter_referenced_po
455 ( x_return_status => l_return_status );
456 END IF;
457
458 ELSIF (p_action = PO_AP_PURGE_GRP.G_FILTER_ACT_DEP_PO_REQ) THEN
459 l_progress := '080';
460
461 filter_dependent_po_req_list
462 ( x_return_status => l_return_status,
463 x_po_records_filtered => x_po_records_filtered );
464
465 ELSIF (p_action = PO_AP_PURGE_GRP.G_FILTER_ACT_DEP_PO_AP) THEN
466 l_progress := '090';
467
468 filter_dependent_po_ap_list
469 ( x_return_status => l_return_status,
470 x_po_records_filtered => x_po_records_filtered );
471 ELSE
472 l_progress := '100';
473
474 FND_MSG_PUB.add_exc_msg
475 ( p_pkg_name => g_pkg_name,
476 p_procedure_name => l_api_name || '.' || l_progress,
477 p_error_text => l_progress || ': Param Mismatch' );
478
479 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
480 END IF; -- p_action = ...
481
482 ELSE
483 l_progress := '110';
484
485 FND_MSG_PUB.add_exc_msg
486 ( p_pkg_name => g_pkg_name,
487 p_procedure_name => l_api_name || '.' || l_progress,
488 p_error_text => l_progress || ': Param Mismatch' );
489
490 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
491 END IF; -- p_purge_category
492
493 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
494 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
495 END IF;
496
497 l_progress := '120';
498
499 IF (p_purge_status = 'INITIATING') THEN
500 l_progress := '130';
501
502 remove_filtered_records
503 ( x_return_status => l_return_status,
504 p_purge_category => p_purge_category
505 );
506
507 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
508 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
509 END IF;
510
511 END IF; -- p_purge_status = 'INITIATING'
512
513 IF (FND_API.to_boolean(p_commit)) THEN
514 COMMIT;
515 END IF;
516
517 IF (g_fnd_debug = 'Y') THEN
518 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
519 FND_LOG.string
520 ( log_level => FND_LOG.LEVEL_PROCEDURE,
521 module => l_module || l_progress,
522 message => 'Quitting ' || g_pkg_name || '.' || l_api_name
523 );
524 END IF;
525 END IF;
526
527 EXCEPTION
528 WHEN OTHERS THEN
529 ROLLBACK TO filter_records_pvt;
530 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
531 FND_MSG_PUB.add_exc_msg
532 ( p_pkg_name => g_pkg_name,
533 p_procedure_name => l_api_name || '.' || l_progress
534
535 );
536 x_msg_data := FND_MSG_PUB.get(p_msg_index => l_msg_idx,
537 p_encoded => 'F');
538 dump_msg_to_log( p_module => l_module || l_progress );
539 END filter_records;
540
541
542
543 -----------------------------------------------------------------------
544 --Start of Comments
545 --Name: confirm_records
546 --Pre-reqs: It is only called from AP Purge program during confirmation
547 -- stage
548 --Modifies:
549 --Locks:
550 -- None
551 --Function:
552 -- Remove records from the purge list by setting double_check_flag = 'Y'
553 -- if the records do not satisfy the initial purge criteria anymore
554 --Parameters:
555 --IN:
556 --p_api_version
557 -- Version of the api the caller is assuming
558 --p_init_msg_list
559 -- FND_API.G_TRUE: initialize the message list
560 -- FND_API.G_FALSE: do not initialize the message list
561 --p_commit
562 -- FND_API.G_TRUE: procedure should commit
563 -- FND_API.G_FALSE: procedure should not commit
564 --p_purge_name
565 -- Name of the purge
566 --p_purge_category
567 -- Purge Category
568 --p_last_activity_date
569 -- Cutoff date for a document to be purged
570 --IN OUT:
571 --OUT:
572 --x_return_status
573 -- status of the procedure
574 --x_msg_data
575 -- This parameter will be not null if an error happens
576 --Returns:
577 --Notes:
578 --Testing:
579 --End of Comments
580 ------------------------------------------------------------------------
581 PROCEDURE confirm_records
582 ( p_api_version IN NUMBER,
583 p_init_msg_list IN VARCHAR2,
584 p_commit IN VARCHAR2,
585 x_return_status OUT NOCOPY VARCHAR2,
586 x_msg_data OUT NOCOPY VARCHAR2,
587 p_purge_name IN VARCHAR2,
588 p_purge_category IN VARCHAR2,
589 p_last_activity_date IN DATE
590 ) IS
591
592 l_api_name CONSTANT VARCHAR2(50) := 'confirm_records';
593 l_api_version CONSTANT NUMBER := 1.0;
594 l_progress VARCHAR2(3);
595 l_return_status VARCHAR2(1);
596 l_module CONSTANT FND_LOG_MESSAGES.module%TYPE :=
597 G_MODULE_PREFIX || l_api_name || '.';
598 l_msg_idx NUMBER;
599 BEGIN
600
601 l_progress := '000';
602
603 IF (g_fnd_debug = 'Y') THEN
604 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
605 FND_LOG.string
606 ( log_level => FND_LOG.LEVEL_PROCEDURE,
607 module => l_module || l_progress,
608 message => 'Entering ' || g_pkg_name || '.' || l_api_name
609 );
610 END IF;
611 END IF;
612
613 SAVEPOINT confirm_records_pvt;
614
615 IF (FND_API.to_boolean(p_init_msg_list)) THEN
616 FND_MSG_PUB.initialize;
617 END IF;
618
619 l_msg_idx := FND_MSG_PUB.count_msg + 1;
620
621 IF (NOT FND_API.Compatible_API_Call
622 ( p_current_version_number => l_api_version,
623 p_caller_version_number => p_api_version,
624 p_api_name => l_api_name,
625 p_pkg_name => g_pkg_name
626 )
627 ) THEN
628
629 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
630 END IF;
631
632 x_return_status := FND_API.G_RET_STS_SUCCESS;
633
634 l_progress := '010';
635
636 IF (p_purge_category = PO_AP_PURGE_GRP.G_PUR_CAT_SIMPLE_REQ) THEN
637 l_progress := '020';
638
639 confirm_simple_req
640 ( x_return_status => l_return_status,
641 p_last_activity_date => p_last_activity_date );
642
643 ELSIF (p_purge_category IN (PO_AP_PURGE_GRP.G_PUR_CAT_SIMPLE_PO,
644 PO_AP_PURGE_GRP.G_PUR_CAT_MATCHED_PO_INV)) THEN
645 l_progress := '030';
646
647 confirm_po
648 ( x_return_status => l_return_status,
649 p_purge_category => p_purge_category,
650 p_last_activity_date => p_last_activity_date );
651 ELSE
652 -- wrong purge_category
653 l_progress := '040';
654
655 FND_MSG_PUB.add_exc_msg
656 ( p_pkg_name => g_pkg_name,
657 p_procedure_name => l_api_name || '.' || l_progress,
658 p_error_text => l_progress || ': Param Mismatch' );
659
660 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
661 END IF; -- p_purge_category
662
663 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
664 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
665 END IF;
666
667 IF (FND_API.to_boolean(p_commit)) THEN
668 COMMIT;
669 END IF;
670
671 IF (g_fnd_debug = 'Y') THEN
672 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
673 FND_LOG.string
674 ( log_level => FND_LOG.LEVEL_PROCEDURE,
675 module => l_module || l_progress,
676 message => 'Quitting ' || g_pkg_name || '.' || l_api_name
677 );
678 END IF;
679 END IF;
680
681 EXCEPTION
682 WHEN OTHERS THEN
683 ROLLBACK TO confirm_records_pvt;
684 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
685 FND_MSG_PUB.add_exc_msg
686 ( p_pkg_name => g_pkg_name,
687 p_procedure_name => l_api_name || '.' || l_progress
688 );
689 x_msg_data := FND_MSG_PUB.get (p_msg_index => l_msg_idx,
690 p_encoded => 'F');
691 dump_msg_to_log( p_module => l_module || l_progress );
692 END confirm_records;
693
694
695
696 -----------------------------------------------------------------------
697 --Start of Comments
698 --Name: summarize_records
699 --Pre-reqs: It is only called from AP Purge program during summarization
700 -- stage
701 --Modifies:
702 --Locks:
703 -- None
704 --Function:
705 -- Insert the document information into history tables before actual
706 -- deletion happens. Documents to be recorded include reqs, pos and
707 -- receipts
708 --Parameters:
709 --IN:
710 --p_api_version
711 -- Version of the api the caller is assuming
712 --p_init_msg_list
713 -- FND_API.G_TRUE: initialize the message list
714 -- FND_API.G_FALSE: do not initialize the message list
715 --p_commit
716 -- FND_API.G_TRUE: procedure should commit
717 -- FND_API.G_FALSE: procedure should not commit
718 --p_purge_name
719 -- Name of the purge
720 --p_purge_category
721 -- Purge Category
722 --p_range_size
723 -- This program inserts data in batches. This parameter specifies
724 -- the size of the id range per commit cycle
725 --IN OUT:
726 --OUT:
727 --x_return_status
728 -- status of the procedure
729 --x_msg_data
730 -- This parameter will be not null if an error happens
731 --Returns:
732 --Notes:
733 --Testing:
734 --End of Comments
735 ------------------------------------------------------------------------
736
737 PROCEDURE summarize_records
738 ( p_api_version IN NUMBER,
739 p_init_msg_list IN VARCHAR2,
740 p_commit IN VARCHAR2,
741 x_return_status OUT NOCOPY VARCHAR2,
742 x_msg_data OUT NOCOPY VARCHAR2,
743 p_purge_name IN VARCHAR2,
744 p_purge_category IN VARCHAR2,
745 p_range_size IN NUMBER
746 ) IS
747
748 l_api_name CONSTANT VARCHAR2(50) := 'summarize_records';
749 l_api_version CONSTANT NUMBER := 1.0;
750 l_progress VARCHAR2(3);
751 l_return_status VARCHAR2(1);
752 l_module CONSTANT FND_LOG_MESSAGES.module%TYPE :=
753 G_MODULE_PREFIX || l_api_name || '.';
754
755 l_req_lower_limit NUMBER;
756 l_req_upper_limit NUMBER;
757 l_po_lower_limit NUMBER;
758 l_po_upper_limit NUMBER;
759 l_msg_idx NUMBER;
760 BEGIN
761
762 l_progress := '000';
763
764 IF (g_fnd_debug = 'Y') THEN
765 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
766 FND_LOG.string
767 ( log_level => FND_LOG.LEVEL_PROCEDURE,
768 module => l_module || l_progress,
769 message => 'Entering ' || g_pkg_name || '.' || l_api_name
770 );
771 END IF;
772 END IF;
773
774 IF (FND_API.to_boolean(p_init_msg_list)) THEN
775 FND_MSG_PUB.initialize;
776 END IF;
777
778 l_msg_idx := FND_MSG_PUB.count_msg + 1;
779
780 IF (NOT FND_API.Compatible_API_Call
781 ( p_current_version_number => l_api_version,
782 p_caller_version_number => p_api_version,
783 p_api_name => l_api_name,
784 p_pkg_name => g_pkg_name
785 )
786 ) THEN
787
788 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
789 END IF;
790
791 x_return_status := FND_API.G_RET_STS_SUCCESS;
792
793 l_progress := '010';
794
795 -- This API will commit for each batch it processes. The p_commit
796 -- parameter has to be FND_API.G_TRUE
797
798 IF (p_commit <> FND_API.G_TRUE) THEN
799
800 FND_MSG_PUB.add_exc_msg
801 ( p_pkg_name => g_pkg_name,
802 p_procedure_name => l_api_name || '.' || l_progress,
803 p_error_text => 'Internal Error. summarize_records must commit'
804 );
805
806 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
807 END IF;
808
809 l_progress := '020';
810
811 get_purge_list_range
812 ( x_return_status => l_return_status,
813 p_category => 'REQ',
814 x_lower_limit => l_req_lower_limit,
815 x_upper_limit => l_req_upper_limit
816 );
817
818 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
819 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
820 END IF;
821
822 summarize_reqs
823 ( x_return_status => l_return_status,
824 p_purge_name => p_purge_name,
825 p_range_size => p_range_size,
826 p_req_lower_limit => l_req_lower_limit,
827 p_req_upper_limit => l_req_upper_limit
828 );
829
830 IF (g_fnd_debug = 'Y') THEN
831 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
832 FND_LOG.string
833 ( log_level => FND_LOG.LEVEL_STATEMENT,
834 module => l_module || l_progress,
835 message => 'After summ_reqs. rtn_status = ' || l_return_status
836 );
837 END IF;
838 END IF;
839
840 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
841 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
842 END IF;
843
844 IF (p_purge_category IN (PO_AP_PURGE_GRP.G_PUR_CAT_SIMPLE_PO,
845 PO_AP_PURGE_GRP.G_PUR_CAT_MATCHED_PO_INV)) THEN
846
847 l_progress := '030';
848
849 get_purge_list_range
850 ( x_return_status => l_return_status,
851 p_category => 'PO',
852 x_lower_limit => l_po_lower_limit,
853 x_upper_limit => l_po_upper_limit
854 );
855
856 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
857 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
858 END IF;
859
860 l_progress := '040';
861
862 summarize_pos
863 ( x_return_status => l_return_status,
864 p_purge_name => p_purge_name,
865 p_range_size => p_range_size,
866 p_po_lower_limit => l_po_lower_limit,
867 p_po_upper_limit => l_po_upper_limit
868 );
869
870 IF (g_fnd_debug = 'Y') THEN
871 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
872 FND_LOG.string
873 ( log_level => FND_LOG.LEVEL_STATEMENT,
874 module => l_module || l_progress,
875 message => 'After summ_pos. rtn_status = ' || l_return_status
876 );
877 END IF;
878 END IF;
879
880 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
881 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
882 END IF;
883
884 l_progress := '050';
885
886 RCV_AP_PURGE_PVT.summarize_receipts
887 ( x_return_status => l_return_status,
888 p_purge_name => p_purge_name,
889 p_range_size => p_range_size,
890 p_po_lower_limit => l_po_lower_limit,
891 p_po_upper_limit => l_po_upper_limit
892 );
893
894 IF (g_fnd_debug = 'Y') THEN
895 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
896 FND_LOG.string
897 ( log_level => FND_LOG.LEVEL_STATEMENT,
898 module => l_module || l_progress,
899 message => 'After summ_rcv. rtn_status = ' || l_return_status
900 );
901 END IF;
902 END IF;
903
904 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
905 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
906 END IF;
907
908 END IF; -- p_purge_category IN (simple_po, matched_po_inv)
909
910 IF (g_fnd_debug = 'Y') THEN
911 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
912 FND_LOG.string
913 ( log_level => FND_LOG.LEVEL_PROCEDURE,
914 module => l_module || l_progress,
915 message => 'Quitting ' || g_pkg_name || '.' || l_api_name
916 );
917 END IF;
918 END IF;
919
920 EXCEPTION
921 WHEN OTHERS THEN
922 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
923
924 IF (g_fnd_debug = 'Y') THEN
925 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
926 FND_LOG.string
927 ( log_level => FND_LOG.LEVEL_STATEMENT,
928 module => l_module || l_progress,
929 message => 'Exception in ' || l_api_name || '.' ||
930 l_progress
931 );
932 END IF;
933 END IF;
934
935 FND_MSG_PUB.add_exc_msg
936 ( p_pkg_name => g_pkg_name,
937 p_procedure_name => l_api_name || '.' || l_progress
938 );
939 x_msg_data := FND_MSG_PUB.get (p_msg_index => l_msg_idx,
940 p_encoded => 'F');
941 dump_msg_to_log( p_module => l_module || l_progress );
942 END summarize_records;
943
944
945 -----------------------------------------------------------------------
946 --Start of Comments
947 --Name: delete_records
948 --Pre-reqs: It is only called from AP Purge program during deletion
949 -- stage
950 --Modifies:
951 --Locks:
952 -- None
953 --Function:
954 -- Delete REQ, PO and RCV transaction tables if the corresponding
955 -- documents are in the purge list
956 --Parameters:
957 --IN:
958 --p_api_version
959 -- Version of the api the caller is assuming
960 --p_init_msg_list
961 -- FND_API.G_TRUE: initialize the message list
962 -- FND_API.G_FALSE: do not initialize the message list
963 --p_commit
964 -- FND_API.G_TRUE: procedure should commit
965 -- FND_API.G_FALSE: procedure should not commit
966 --p_purge_name
967 -- Name of the purge
968 --p_purge_category
969 -- Purge Category
970 --p_range_size
971 -- This program deletes data in batches. This parameter specifies
972 -- the number of documents to be purged per commit cycle
973 --IN OUT:
974 --OUT:
975 --x_return_status
976 -- status of the procedure
977 --x_msg_data
978 -- This parameter will be not null if an error happens
979 --Returns:
980 --Notes:
981 --Testing:
982 --End of Comments
983 ------------------------------------------------------------------------
984
985 PROCEDURE delete_records
986 ( p_api_version IN NUMBER,
987 p_init_msg_list IN VARCHAR2,
988 p_commit IN VARCHAR2,
989 x_return_status OUT NOCOPY VARCHAR2,
990 x_msg_data OUT NOCOPY VARCHAR2,
991 p_purge_name IN VARCHAR2,
992 p_purge_category IN VARCHAR2,
993 p_range_size IN NUMBER
994 ) IS
995
996 l_api_name CONSTANT VARCHAR2(50) := 'delete_records';
997 l_api_version CONSTANT NUMBER := 1.0;
998 l_progress VARCHAR2(3);
999 l_return_status VARCHAR2(1);
1000 l_module CONSTANT FND_LOG_MESSAGES.module%TYPE :=
1001 G_MODULE_PREFIX || l_api_name || '.';
1002 l_msg_idx NUMBER;
1003
1004 l_req_lower_limit NUMBER;
1005 l_req_upper_limit NUMBER;
1006 l_po_lower_limit NUMBER;
1007 l_po_upper_limit NUMBER;
1008
1009 BEGIN
1010
1011 l_progress := '000';
1012
1013 IF (g_fnd_debug = 'Y') THEN
1014 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1015 FND_LOG.string
1016 ( log_level => FND_LOG.LEVEL_PROCEDURE,
1017 module => l_module || l_progress,
1018 message => 'Entering ' || g_pkg_name || '.' || l_api_name
1019 );
1020 END IF;
1021 END IF;
1022
1023 IF (FND_API.to_boolean(p_init_msg_list)) THEN
1024 FND_MSG_PUB.initialize;
1025 END IF;
1026
1027 l_msg_idx := FND_MSG_PUB.count_msg + 1;
1028
1029 IF (NOT FND_API.Compatible_API_Call
1030 ( p_current_version_number => l_api_version,
1031 p_caller_version_number => p_api_version,
1032 p_api_name => l_api_name,
1033 p_pkg_name => g_pkg_name
1034 )
1035 ) THEN
1036
1037 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1038 END IF;
1039
1040 x_return_status := FND_API.G_RET_STS_SUCCESS;
1041
1042 l_progress := '010';
1043
1044 -- This API will commit for each batch it processes. The p_commit
1045 -- parameter has to be FND_API.G_TRUE
1046 IF (p_commit <> FND_API.G_TRUE) THEN
1047
1048 FND_MSG_PUB.add_exc_msg
1049 ( p_pkg_name => g_pkg_name,
1050 p_procedure_name => l_api_name || '.' || l_progress,
1051 p_error_text => 'Internal Error. delete_records must commit'
1052 );
1053
1054 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1055 END IF;
1056
1057 l_progress := '020';
1058
1059 get_purge_list_range
1060 ( x_return_status => l_return_status,
1061 p_category => 'REQ',
1062 x_lower_limit => l_req_lower_limit,
1063 x_upper_limit => l_req_upper_limit
1064 );
1065
1066 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1067 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1068 END IF;
1069
1070 delete_reqs
1071 ( x_return_status => l_return_status,
1072 p_range_size => p_range_size,
1073 p_req_lower_limit => l_req_lower_limit,
1074 p_req_upper_limit => l_req_upper_limit
1075 );
1076
1077 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1078 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1079 END IF;
1080
1081 IF (p_purge_category IN (PO_AP_PURGE_GRP.G_PUR_CAT_SIMPLE_PO,
1082 PO_AP_PURGE_GRP.G_PUR_CAT_MATCHED_PO_INV)) THEN
1083
1084 l_progress := '030';
1085
1086 get_purge_list_range
1087 ( x_return_status => l_return_status,
1088 p_category => 'PO',
1089 x_lower_limit => l_po_lower_limit,
1090 x_upper_limit => l_po_upper_limit
1091 );
1092
1093 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1094 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1095 END IF;
1096
1097 l_progress := '040';
1098
1099 delete_pos
1100 ( x_return_status => l_return_status,
1101 p_range_size => p_range_size,
1102 p_po_lower_limit => l_po_lower_limit,
1103 p_po_upper_limit => l_po_upper_limit
1104 );
1105
1106 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1107 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1108 END IF;
1109
1110 l_progress := '050';
1111
1112 RCV_AP_PURGE_PVT.delete_receipts
1113 ( x_return_status => l_return_status,
1114 p_range_size => p_range_size,
1115 p_po_lower_limit => l_po_lower_limit,
1116 p_po_upper_limit => l_po_upper_limit
1117 );
1118
1119 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1120 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1121 END IF;
1122
1123 END IF; -- if purge category = simple_po or matched po_inv
1124
1125 IF (g_fnd_debug = 'Y') THEN
1126 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1127 FND_LOG.string
1128 ( log_level => FND_LOG.LEVEL_PROCEDURE,
1129 module => l_module || l_progress,
1130 message => 'Quitting ' || g_pkg_name || '.' || l_api_name
1131 );
1132 END IF;
1133 END IF;
1134
1135 EXCEPTION
1136 WHEN OTHERS THEN
1137 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1138 FND_MSG_PUB.add_exc_msg
1139 ( p_pkg_name => g_pkg_name,
1140 p_procedure_name => l_api_name || '.' || l_progress
1141 );
1142 x_msg_data := FND_MSG_PUB.get (p_msg_index => l_msg_idx,
1143 p_encoded => 'F');
1144 dump_msg_to_log( p_module => l_module || l_progress );
1145 END delete_records;
1146
1147
1148 -----------------------------------------------------------------------
1149 --Start of Comments
1150 --Name: delete_purge_lists
1151 --Pre-reqs: It is only called from AP Purge program during summarization
1152 -- or abortion stage
1153 --Modifies:
1154 --Locks:
1155 -- None
1156 --Function:
1157 -- Truncate REQ/PO purge list
1158 --Parameters:
1159 --IN:
1160 --p_api_version
1161 -- Version of the api the caller is assuming
1162 --p_init_msg_list
1163 -- FND_API.G_TRUE: initialize the message list
1164 -- FND_API.G_FALSE: do not initialize the message list
1165 --p_commit
1166 -- FND_API.G_TRUE: procedure should commit
1167 -- FND_API.G_FALSE: procedure should not commit
1168 --p_purge_name
1169 -- Name of the purge
1170 --IN OUT:
1171 --OUT:
1172 --x_return_status
1173 -- status of the procedure
1174 --x_msg_data
1175 -- This parameter will be not null if an error happens
1176 --Returns:
1177 --Notes:
1178 --Testing:
1179 --End of Comments
1180 ------------------------------------------------------------------------
1181
1182 PROCEDURE delete_purge_lists
1183 ( p_api_version IN NUMBER,
1184 p_init_msg_list IN VARCHAR2,
1185 p_commit IN VARCHAR2,
1186 x_return_status OUT NOCOPY VARCHAR2,
1187 x_msg_data OUT NOCOPY VARCHAR2,
1188 p_purge_name IN VARCHAR2
1189 ) IS
1190
1191
1192 l_api_name CONSTANT VARCHAR2(50) := 'delete_purge_lists';
1193 l_api_version CONSTANT NUMBER := 1.0;
1194 l_progress VARCHAR2(3);
1195 l_module CONSTANT FND_LOG_MESSAGES.module%TYPE :=
1196 G_MODULE_PREFIX || l_api_name || '.';
1197 l_msg_idx NUMBER;
1198
1199 BEGIN
1200
1201 l_progress := '000';
1202
1203 IF (g_fnd_debug = 'Y') THEN
1204 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1205 FND_LOG.string
1206 ( log_level => FND_LOG.LEVEL_PROCEDURE,
1207 module => l_module || l_progress,
1208 message => 'Entering ' || g_pkg_name || '.' || l_api_name
1209 );
1210 END IF;
1211 END IF;
1212
1213 SAVEPOINT delete_purge_lists_pvt;
1214
1215 IF (FND_API.to_boolean(p_init_msg_list)) THEN
1216 FND_MSG_PUB.initialize;
1217 END IF;
1218
1219 l_msg_idx := FND_MSG_PUB.count_msg + 1;
1220
1221 IF (NOT FND_API.Compatible_API_Call
1222 ( p_current_version_number => l_api_version,
1223 p_caller_version_number => p_api_version,
1224 p_api_name => l_api_name,
1225 p_pkg_name => g_pkg_name
1226 )
1227 ) THEN
1228
1229 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1230 END IF;
1231
1232 x_return_status := FND_API.G_RET_STS_SUCCESS;
1233
1234 l_progress := '010';
1235
1236 -- clear the purge lists
1237
1238 DELETE
1239 FROM po_purge_req_list;
1240
1241 DELETE
1242 FROM po_purge_po_list;
1243
1244 IF (FND_API.to_boolean(p_commit)) THEN
1245 COMMIT;
1246 END IF;
1247
1248 IF (g_fnd_debug = 'Y') THEN
1249 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1250 FND_LOG.string
1251 ( log_level => FND_LOG.LEVEL_PROCEDURE,
1252 module => l_module || l_progress,
1253 message => 'Quitting ' || g_pkg_name || '.' || l_api_name
1254 );
1255 END IF;
1256 END IF;
1257
1258 EXCEPTION
1259 WHEN OTHERS THEN
1260 ROLLBACK TO delete_purge_lists_pvt;
1261 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1262 FND_MSG_PUB.add_exc_msg
1263 ( p_pkg_name => g_pkg_name,
1264 p_procedure_name => l_api_name || '.' || l_progress
1265 );
1266 x_msg_data := FND_MSG_PUB.get (p_msg_index => l_msg_idx,
1267 p_encoded => 'F');
1268 dump_msg_to_log( p_module => l_module || l_progress );
1269 END delete_purge_lists;
1270
1271
1272 -----------------------------------------------------------------------
1273 --Start of Comments
1274 --Name: delete_history_tables
1275 --Pre-reqs: It is only called from AP Purge program during abort
1276 -- stage
1277 --Modifies:
1278 --Locks:
1279 -- None
1280 --Function:
1281 -- Delete records from history tables that were inserted by the current
1282 -- purge process (identified by p_purge_name)
1283 --Parameters:
1284 --IN:
1285 --p_api_version
1286 -- Version of the api the caller is assuming
1287 --p_init_msg_list
1288 -- FND_API.G_TRUE: initialize the message list
1289 -- FND_API.G_FALSE: do not initialize the message list
1290 --p_commit
1291 -- FND_API.G_TRUE: procedure should commit
1292 -- FND_API.G_FALSE: procedure should not commit
1293 --p_purge_name
1294 -- Name of the purge
1295 --IN OUT:
1296 --OUT:
1297 --x_return_status
1298 -- status of the procedure
1299 --x_msg_data
1300 -- This parameter will be not null if an error happens
1301 --Returns:
1302 --Notes:
1303 --Testing:
1304 --End of Comments
1305 ------------------------------------------------------------------------
1306
1307 PROCEDURE delete_history_tables
1308 ( p_api_version IN NUMBER,
1309 p_init_msg_list IN VARCHAR2,
1310 p_commit IN VARCHAR2,
1311 x_return_status OUT NOCOPY VARCHAR2,
1312 x_msg_data OUT NOCOPY VARCHAR2,
1313 p_purge_name IN VARCHAR2
1314 ) IS
1315
1316
1317 l_api_name CONSTANT VARCHAR2(50) := 'delete_history_tables';
1318 l_api_version CONSTANT NUMBER := 1.0;
1319 l_progress VARCHAR2(3);
1320 l_return_status VARCHAR2(1);
1321 l_module CONSTANT FND_LOG_MESSAGES.module%TYPE :=
1322 G_MODULE_PREFIX || l_api_name || '.';
1323 l_msg_idx NUMBER;
1324
1325 BEGIN
1326
1327 l_progress := '000';
1328
1329 IF (g_fnd_debug = 'Y') THEN
1330 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1331 FND_LOG.string
1332 ( log_level => FND_LOG.LEVEL_PROCEDURE,
1333 module => l_module || l_progress,
1334 message => 'Entering ' || g_pkg_name || '.' || l_api_name
1335 );
1336 END IF;
1337 END IF;
1338
1339 SAVEPOINT delete_history_tables_pvt;
1340
1341 l_progress := '010';
1342
1343 IF (FND_API.to_boolean(p_init_msg_list)) THEN
1344 FND_MSG_PUB.initialize;
1345 END IF;
1346
1347 l_msg_idx := FND_MSG_PUB.count_msg + 1;
1348
1349 IF (NOT FND_API.Compatible_API_Call
1350 ( p_current_version_number => l_api_version,
1351 p_caller_version_number => p_api_version,
1352 p_api_name => l_api_name,
1353 p_pkg_name => g_pkg_name
1354 )
1355 ) THEN
1356
1357 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1358 END IF;
1359
1360 x_return_status := FND_API.G_RET_STS_SUCCESS;
1361
1362 l_progress := '020';
1363
1364 DELETE
1365 FROM po_history_pos
1366 WHERE purge_name = p_purge_name;
1367
1368 l_progress := '030';
1369
1370 DELETE
1371 FROM po_history_requisitions
1372 WHERE purge_name = p_purge_name;
1373
1374 l_progress := '040';
1375
1376 DELETE
1377 FROM po_history_receipts
1378 WHERE purge_name = p_purge_name;
1379
1380 IF (FND_API.to_boolean(p_commit)) THEN
1381 COMMIT;
1382 END IF;
1383
1384 IF (g_fnd_debug = 'Y') THEN
1385 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1386 FND_LOG.string
1387 ( log_level => FND_LOG.LEVEL_PROCEDURE,
1388 module => l_module || l_progress,
1389 message => 'Quitting ' || g_pkg_name || '.' || l_api_name
1390 );
1391 END IF;
1392 END IF;
1393
1394 EXCEPTION
1395 WHEN OTHERS THEN
1396 ROLLBACK TO delete_history_tables_pvt;
1397 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1398 FND_MSG_PUB.add_exc_msg
1399 ( p_pkg_name => g_pkg_name,
1400 p_procedure_name => l_api_name || '.' || l_progress
1401 );
1402 x_msg_data := FND_MSG_PUB.get (p_msg_index => l_msg_idx,
1403 p_encoded => 'F');
1404 dump_msg_to_log( p_module => l_module || l_progress );
1405 END delete_history_tables;
1406
1407
1408 -----------------------------------------------------------------------
1409 --Start of Comments
1410 --Name: count_po_rows
1411 --Pre-reqs:
1412 --Modifies:
1413 --Locks:
1414 -- None
1415 --Function: Get the count of some PO transaction tables for reporting
1416 --Parameters:
1417 --p_api_version
1418 -- Version of the api the caller is assuming
1419 --p_init_msg_list
1420 -- FND_API.G_TRUE: initialize the message list
1421 -- FND_API.G_FALSE: do not initialize the message list
1422 --IN OUT:
1423 --OUT:
1424 --x_return_status
1425 -- status of the procedure
1426 --x_msg_data
1427 -- This parameter will be not null if an error happens
1428 --x_po_hdr_coun
1429 -- Number of records in po_headers
1430 --x_rcv_line_count
1431 -- Number of records in rcv_shipment_lines
1432 --x_req_hdr_count
1433 -- Number of records in po_requisition_headers
1434 --x_vendor_count
1435 -- Number of records in po_vendors
1436 --x_asl_count
1437 -- Number of records in po_approved_supplier_list
1438 --x_asl_attr_count
1439 -- Number of records in po_asl_attributes
1440 --x_asl_doc_count
1441 -- Number of records in po_asl_documents
1442 --Returns:
1443 --Notes:
1444 --Testing:
1445 --End of Comments
1446 ------------------------------------------------------------------------
1447
1448 PROCEDURE count_po_rows
1449 ( p_api_version IN NUMBER,
1450 p_init_msg_list IN VARCHAR2,
1451 x_return_status OUT NOCOPY VARCHAR2,
1452 x_msg_data OUT NOCOPY VARCHAR2,
1453 x_po_hdr_count OUT NOCOPY NUMBER,
1454 x_rcv_line_count OUT NOCOPY NUMBER,
1455 x_req_hdr_count OUT NOCOPY NUMBER,
1456 x_vendor_count OUT NOCOPY NUMBER,
1457 x_asl_count OUT NOCOPY NUMBER,
1458 x_asl_attr_count OUT NOCOPY NUMBER,
1459 x_asl_doc_count OUT NOCOPY NUMBER
1460 ) IS
1461
1462 l_api_name VARCHAR2(50) := 'count_po_rows';
1463 l_api_version NUMBER := 1.0;
1464 l_progress VARCHAR2(3);
1465 l_return_status VARCHAR2(1);
1466 l_module CONSTANT FND_LOG_MESSAGES.module%TYPE :=
1467 G_MODULE_PREFIX || l_api_name || '.';
1468 l_msg_idx NUMBER;
1469
1470 BEGIN
1471
1472 l_progress := '000';
1473
1474 IF (g_fnd_debug = 'Y') THEN
1475 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1476 FND_LOG.string
1477 ( log_level => FND_LOG.LEVEL_PROCEDURE,
1478 module => l_module || l_progress,
1479 message => 'Entering ' || g_pkg_name || '.' || l_api_name
1480 );
1481 END IF;
1482 END IF;
1483
1484 IF (FND_API.to_boolean(p_init_msg_list)) THEN
1485 FND_MSG_PUB.initialize;
1486 END IF;
1487
1488 l_msg_idx := FND_MSG_PUB.count_msg + 1;
1489
1490 IF (NOT FND_API.Compatible_API_Call
1491 ( p_current_version_number => l_api_version,
1492 p_caller_version_number => p_api_version,
1493 p_api_name => l_api_name,
1494 p_pkg_name => g_pkg_name
1495 )
1496 ) THEN
1497
1498 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1499 END IF;
1500
1501 x_return_status := FND_API.G_RET_STS_SUCCESS;
1502
1503 SELECT COUNT(1)
1504 INTO x_po_hdr_count
1505 FROM po_headers;
1506
1507 l_progress := '010';
1508
1509 SELECT COUNT(1)
1510 INTO x_rcv_line_count
1511 FROM rcv_shipment_lines;
1512
1513 l_progress := '020';
1514
1515 SELECT COUNT(1)
1516 INTO x_req_hdr_count
1517 FROM po_requisition_headers;
1518
1519 l_progress := '030';
1520
1521 SELECT COUNT(1)
1522 INTO x_vendor_count
1523 FROM po_vendors;
1524
1525 l_progress := '040';
1526
1527 SELECT COUNT(1)
1528 INTO x_asl_count
1529 FROM po_approved_supplier_list;
1530
1531 l_progress := '050';
1532
1533 SELECT COUNT(1)
1534 INTO x_asl_attr_count
1535 FROM po_asl_attributes;
1536
1537 l_progress := '060';
1538
1539 SELECT COUNT(1)
1540 INTO x_asl_doc_count
1541 FROM po_asl_documents;
1542
1543 IF (g_fnd_debug = 'Y') THEN
1544 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1545 FND_LOG.string
1546 ( log_level => FND_LOG.LEVEL_PROCEDURE,
1547 module => l_module || l_progress,
1548 message => 'Quitting ' || g_pkg_name || '.' || l_api_name
1549 );
1550 END IF;
1551 END IF;
1552
1553 EXCEPTION
1554 WHEN OTHERS THEN
1555 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1556 FND_MSG_PUB.add_exc_msg
1557 ( p_pkg_name => g_pkg_name,
1558 p_procedure_name => l_api_name || '.' || l_progress
1559 );
1560
1561 x_msg_data := FND_MSG_PUB.get (p_msg_index => l_msg_idx,
1562 p_encoded => 'F');
1563 dump_msg_to_log( p_module => l_module || l_progress );
1564 END count_po_rows;
1565
1566 --*********************************************************************
1567 -------------------------- Private Procedures -------------------------
1568 --*********************************************************************
1569
1570
1571 -----------------------------------------------------------------------
1572 --Start of Comments
1573 --Name: seed_simple_req
1574 --Pre-reqs:
1575 --Modifies: po_purge_req_list
1576 --Locks:
1577 -- None
1578 --Function:
1579 -- Populate req purge list with eligible reqs that have not been updated
1580 -- since last activity date
1581 --Parameters:
1582 --IN:
1583 --p_purge_name
1584 -- Name of this purge process
1585 --p_last_activity_date
1586 -- Cutoff date of the purge process. req will not be purged if it has been
1587 -- updated since this date
1588 --IN OUT:
1589 --OUT:
1590 --x_return_status
1591 -- status of the procedure
1592 --Returns:
1593 --Notes:
1594 --Testing:
1595 --End of Comments
1596 ------------------------------------------------------------------------
1597 PROCEDURE seed_simple_req
1598 ( x_return_status OUT NOCOPY VARCHAR2,
1599 p_purge_name IN VARCHAR2,
1600 p_last_activity_date IN DATE
1601 ) IS
1602
1603 l_api_name CONSTANT VARCHAR2(50) := 'seed_simple_req';
1604 l_progress VARCHAR2(3);
1605 l_module CONSTANT FND_LOG_MESSAGES.module%TYPE :=
1606 G_MODULE_PREFIX || l_api_name || '.';
1607
1608 BEGIN
1609 l_progress := '000';
1610
1611 IF (g_fnd_debug = 'Y') THEN
1612 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1613 FND_LOG.string
1614 ( log_level => FND_LOG.LEVEL_PROCEDURE,
1615 module => l_module || l_progress,
1616 message => 'Entering ' || g_pkg_name || '.' || l_api_name
1617 );
1618 END IF;
1619 END IF;
1620
1621 x_return_status := FND_API.G_RET_STS_SUCCESS;
1622
1623 -- SQL What: Generate a list of requisitions that are eligible for purging
1624 -- SQL Why: This is the initial list of reqs to be purged. Later on the
1625 -- records in this list will be removed if the records are no
1626 -- longer eligible for purging after additional rules are applied
1627
1628 INSERT INTO po_purge_req_list
1629 ( requisition_header_id,
1630 purge_name,
1631 double_check_flag
1632 )
1633 SELECT PRH.requisition_header_id,
1634 p_purge_name,
1635 'Y'
1636 FROM po_requisition_headers PRH
1637 WHERE PRH.last_update_date <= p_last_activity_date
1638 AND (PRH.closed_code = 'FINALLY CLOSED'
1639 OR PRH.authorization_status = 'CANCELLED')
1640 AND NOT EXISTS
1641 (SELECT NULL
1642 FROM po_requisition_lines PRL
1643 WHERE PRL.requisition_header_id = PRH.requisition_header_id
1644 AND NVL(PRL.modified_by_agent_flag, 'N') = 'N'
1645 AND (PRL.last_update_date > p_last_activity_date
1646 OR
1647 PRL.line_location_id IS NOT NULL
1648 OR
1649 PRL.source_type_code = 'INVENTORY'
1650 OR
1651 EXISTS (
1652 SELECT NULL
1653 FROM po_price_differentials PPD
1654 WHERE PPD.entity_type = 'REQ LINE'
1655 AND PPD.entity_id = PRL.requisition_line_id
1656 AND PPD.last_update_date >
1657 p_last_activity_date)
1658 OR
1659 EXISTS (
1660 SELECT NULL
1661 FROM po_req_distributions PRD
1662 WHERE PRD.requisition_line_id =
1663 PRL.requisition_line_id
1664 AND PRD.last_update_date >
1665 p_last_activity_date)));
1666
1667 IF (g_fnd_debug = 'Y') THEN
1668 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1669 FND_LOG.string
1670 ( log_level => FND_LOG.LEVEL_STATEMENT,
1671 module => l_module || l_progress,
1672 message => 'Inserted ' || SQL%ROWCOUNT || ' Reqs to purge list'
1673 );
1674 END IF;
1675 END IF;
1676
1677 l_progress := '010';
1678
1679 PO_AP_PURGE_UTIL_PVT.log_purge_list_count
1680 ( p_module => l_module || l_progress,
1681 p_entity => 'REQ'
1682 );
1683
1684 IF (g_fnd_debug = 'Y') THEN
1685 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1686 FND_LOG.string
1687 ( log_level => FND_LOG.LEVEL_PROCEDURE,
1688 module => l_module || l_progress,
1689 message => 'Quitting ' || g_pkg_name || '.' || l_api_name
1690 );
1691 END IF;
1692 END IF;
1693
1694 EXCEPTION
1695 WHEN OTHERS THEN
1696 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1697 FND_MSG_PUB.add_exc_msg
1698 ( p_pkg_name => g_pkg_name,
1699 p_procedure_name => l_api_name || '.' || l_progress
1700 );
1701
1702 END seed_simple_req;
1703
1704
1705
1706 -----------------------------------------------------------------------
1707 --Start of Comments
1708 --Name: seed_po
1709 --Pre-reqs:
1710 --Modifies: po_purge_po_list
1711 --Locks:
1712 -- None
1713 --Function:
1714 -- Populate po purge list with eligible pos that have not been updated
1715 -- since last activity date. It will also populate req list as well
1716 --Parameters:
1717 --IN:
1718 --p_purge_category
1719 -- Describe what type of documents user wants to purge.
1720 --p_purge_name
1721 -- Name of this purge process
1722 --p_last_activity_date
1723 -- Cutoff date of the purge process. po will not be purged if it has been
1724 -- updated since this date
1725 --IN OUT:
1726 --OUT:
1727 --x_return_status
1728 -- status of the procedure
1729 --Returns:
1730 --Notes:
1731 --Testing:
1732 --End of Comments
1733 ------------------------------------------------------------------------
1734 PROCEDURE seed_po
1735 ( x_return_status OUT NOCOPY VARCHAR2,
1736 p_purge_category IN VARCHAR2,
1737 p_purge_name IN VARCHAR2,
1738 p_last_activity_date IN DATE
1739 ) IS
1740
1741 l_api_name CONSTANT VARCHAR2(50) := 'seed_po';
1742 l_progress VARCHAR2(3);
1743 l_return_status VARCHAR2(1);
1744 l_module CONSTANT FND_LOG_MESSAGES.module%TYPE :=
1745 G_MODULE_PREFIX || l_api_name || '.';
1746
1747 BEGIN
1748
1749 l_progress := '000';
1750
1751 IF (g_fnd_debug = 'Y') THEN
1752 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1753 FND_LOG.string
1754 ( log_level => FND_LOG.LEVEL_PROCEDURE,
1755 module => l_module || l_progress,
1756 message => 'Entering ' || g_pkg_name || '.' || l_api_name
1757 );
1758 END IF;
1759 END IF;
1760
1761 x_return_status := FND_API.G_RET_STS_SUCCESS;
1762
1763 --<ACTION FOR 11iX START>
1764 --Initiated by: BAO
1765 --The check for Code Level below will be unnecessary in 11iX.
1766 --The code from PO_AP_PURGE_UTIL_PVT.seed_po will be moved to
1767 --here and there will be no need to branch the logic based on code level
1768
1769 IF (PO_CODE_RELEASE_GRP.Current_Release <
1770 PO_CODE_RELEASE_GRP.PRC_11i_Family_Pack_J) THEN
1771
1772 IF (g_fnd_debug = 'Y') THEN
1773 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1774 FND_LOG.string
1775 ( log_level => FND_LOG.LEVEL_STATEMENT,
1776 module => l_module || l_progress,
1777 message => 'PO level is less than FPJ '
1778 );
1779 END IF;
1780 END IF;
1781
1782 IF (p_purge_category = PO_AP_PURGE_GRP.G_PUR_CAT_SIMPLE_PO) THEN
1783
1784 l_progress := '010';
1785
1786 -- The subquery on po_distributions is to make sure that no invoice
1787 -- has matched to this PO yet
1788
1789 --SQL What: Generate a list of POs that are eligibible for purge
1790 --SQL Why: This is the initial list of pos to be purged. There
1791 -- will be additional rules getting applied to this table
1792 -- to remove records that are actually not eligible for
1793 -- purge
1794
1795 INSERT INTO po_purge_po_list
1796 ( po_header_id,
1797 purge_name,
1798 double_check_flag
1799 )
1800 SELECT PH.po_header_id,
1801 p_purge_name,
1802 'Y'
1803 FROM po_headers PH
1804 WHERE PH.type_lookup_code IN ('STANDARD', 'PLANNED',
1805 'BLANKET', 'CONTRACT')
1806 AND PH.last_update_date <= p_last_activity_date
1807 AND (PH.closed_code = 'FINALLY CLOSED'
1808 OR PH.cancel_flag = 'Y')
1809 AND NOT EXISTS
1810 (SELECT NULL
1811 FROM po_releases PR
1812 WHERE PR.po_header_id = PH.po_header_id
1813 AND PR.last_update_date > p_last_activity_date)
1814 AND NOT EXISTS
1815 (SELECT NULL
1816 FROM po_lines PL
1817 WHERE PL.po_header_id = PH.po_header_id
1818 AND PL.last_update_date > p_last_activity_date)
1819 AND NOT EXISTS
1820 (SELECT NULL
1821 FROM po_line_locations PLL
1822 WHERE PLL.po_header_id = PH.po_header_id
1823 AND PLL.last_update_date > p_last_activity_date)
1824 AND NOT EXISTS
1825 (SELECT NULL
1826 FROM po_distributions PD
1827 WHERE PD.po_header_id = PH.po_header_id
1828 AND (PD.last_update_date > p_last_activity_date
1829 OR
1830 EXISTS
1831 (SELECT NULL
1832 FROM ap_invoice_distributions AD
1833 WHERE AD.po_distribution_id =
1834 PD.po_distribution_id)))
1835 AND NOT EXISTS
1836 (SELECT NULL
1837 FROM rcv_transactions RT
1838 WHERE RT.po_header_id = PH.po_header_id
1839 AND RT.last_update_date > p_last_activity_date)
1840 AND PO_AP_PURGE_GRP.validate_purge(PH.po_header_id) = 'T';
1841
1842
1843 ELSIF (p_purge_category =PO_AP_PURGE_GRP.G_PUR_CAT_MATCHED_PO_INV) THEN
1844
1845 l_progress := '020';
1846
1847 -- POs that have invoices are still candidates for purging when
1848 -- purge category = 'MATCHED POS AND INVOICES'
1849
1850 --SQL What: Generate a list of POs that are eligibible for purge
1851 --SQL Why: This is the initial list of pos to be purged. There
1852 -- will be additional rules getting applied to this table
1853 -- to remove records that are actually not eligible for
1854 -- purge
1855
1856 INSERT INTO po_purge_po_list
1857 ( po_header_id,
1858 purge_name,
1859 double_check_flag
1860 )
1861 SELECT PH.po_header_id,
1862 p_purge_name,
1863 'Y'
1864 FROM po_headers PH
1865 WHERE PH.type_lookup_code IN ('STANDARD', 'PLANNED',
1866 'BLANKET', 'CONTRACT')
1867 AND PH.last_update_date <= p_last_activity_date
1868 AND (PH.closed_code = 'FINALLY CLOSED'
1869 OR PH.cancel_flag = 'Y')
1870 AND NOT EXISTS
1871 (SELECT NULL
1872 FROM po_releases PR
1873 WHERE PR.po_header_id = PH.po_header_id
1874 AND PR.last_update_date > p_last_activity_date)
1875 AND NOT EXISTS
1876 (SELECT NULL
1877 FROM po_lines PL
1878 WHERE PL.po_header_id = PH.po_header_id
1879 AND PL.last_update_date > p_last_activity_date)
1880 AND NOT EXISTS
1881 (SELECT NULL
1882 FROM po_line_locations PLL
1883 WHERE PLL.po_header_id = PH.po_header_id
1884 AND PLL.last_update_date > p_last_activity_date)
1885 AND NOT EXISTS
1886 (SELECT NULL
1887 FROM po_distributions PD
1888 WHERE PD.po_header_id = PH.po_header_id
1889 AND PD.last_update_date > p_last_activity_date)
1890 AND NOT EXISTS
1891 (SELECT NULL
1892 FROM rcv_transactions RT
1893 WHERE RT.po_header_id = PH.po_header_id
1894 AND RT.last_update_date > p_last_activity_date)
1895 AND PO_AP_PURGE_GRP.validate_purge(PH.po_header_id) = 'T';
1896
1897 END IF; -- p_purge_category = ...
1898
1899 IF (g_fnd_debug = 'Y') THEN
1900 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1901 FND_LOG.string
1902 ( log_level => FND_LOG.LEVEL_STATEMENT,
1903 module => l_module || l_progress,
1904 message => 'inserted ' || SQL%ROWCOUNT || ' POs to purge'
1905 || ' list. purge_cat = ' || p_purge_category
1906 );
1907 END IF;
1908 END IF;
1909
1910 l_progress := '030';
1911
1912 -- We will put the req in the purge list even if it has turned into
1913 -- a PO.
1914
1915 -- SQL What: Generate a list of requisitions that are eligible for
1916 -- purging
1917 -- SQL Why: This is the initial list of reqs to be purged. Later on
1918 -- the records in this list will be removed if the records
1919 -- are no longer eligible for purging after additional rules
1920 -- are applied
1921
1922 INSERT INTO po_purge_req_list
1923 ( requisition_header_id,
1924 purge_name,
1925 double_check_flag
1926 )
1927 SELECT PRH.requisition_header_id,
1928 p_purge_name,
1929 'Y'
1930 FROM po_requisition_headers PRH
1931 WHERE PRH.last_update_date <= p_last_activity_date
1932 AND (PRH.closed_code = 'FINALLY CLOSED'
1933 OR PRH.authorization_status = 'CANCELLED')
1934 AND NOT EXISTS
1935 (SELECT NULL
1936 FROM po_requisition_lines PRL
1937 WHERE PRL.requisition_header_id =
1938 PRH.requisition_header_id
1939 AND NVL(PRL.modified_by_agent_flag, 'N') = 'N'
1940 AND (PRL.last_update_date > p_last_activity_date
1941 OR
1942 PRL.source_type_code = 'INVENTORY'
1943 OR
1944 EXISTS (
1945 SELECT NULL
1946 FROM po_req_distributions PRD
1947 WHERE PRD.requisition_line_id =
1948 PRL.requisition_line_id
1949 AND PRD.last_update_date >
1950 p_last_activity_date)));
1951
1952 IF (g_fnd_debug = 'Y') THEN
1953 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1954 FND_LOG.string
1955 ( log_level => FND_LOG.LEVEL_STATEMENT,
1956 module => l_module || l_progress,
1957 message => 'inserted ' || SQL%ROWCOUNT || ' REQs to purge list'
1958 );
1959 END IF;
1960 END IF;
1961
1962
1963 l_progress := '040';
1964
1965 ELSE -- Family Pack level >= 11i FPJ
1966
1967 l_progress := '050';
1968
1969 IF (g_fnd_debug = 'Y') THEN
1970 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1971 FND_LOG.string
1972 ( log_level => FND_LOG.LEVEL_STATEMENT,
1973 module => l_module || l_progress,
1974 message => 'PO Code Level > FPJ'
1975 );
1976 END IF;
1977 END IF;
1978
1979 PO_AP_PURGE_UTIL_PVT.seed_po
1980 ( x_return_status => l_return_status,
1981 p_purge_category => p_purge_category,
1982 p_purge_name => p_purge_name,
1983 p_last_activity_date => p_last_activity_date
1984 );
1985
1986 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1987 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1988 END IF;
1989
1990 END IF; -- current release < 11i FPJ
1991
1992 --<ACTION FOR 11iX END>
1993
1994 PO_AP_PURGE_UTIL_PVT.log_purge_list_count
1995 ( p_module => l_module || l_progress,
1996 p_entity => 'REQ'
1997 );
1998
1999 PO_AP_PURGE_UTIL_PVT.log_purge_list_count
2000 ( p_module => l_module || l_progress,
2001 p_entity => 'PO'
2002 );
2003
2004
2005 IF (g_fnd_debug = 'Y') THEN
2006 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
2007 FND_LOG.string
2008 ( log_level => FND_LOG.LEVEL_PROCEDURE,
2009 module => l_module || l_progress,
2010 message => 'Quitting ' || g_pkg_name || '.' || l_api_name
2011 );
2012 END IF;
2013 END IF;
2014
2015 EXCEPTION
2016 WHEN OTHERS THEN
2017 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2018 FND_MSG_PUB.add_exc_msg
2019 ( p_pkg_name => g_pkg_name,
2020 p_procedure_name => l_api_name || '.' || l_progress
2021 );
2022 END seed_po;
2023
2024
2025 -----------------------------------------------------------------------
2026 --Start of Comments
2027 --Name: set_product_inst_status
2028 --Pre-reqs:
2029 --Modifies:
2030 --Locks:
2031 -- None
2032 --Function:
2033 -- derive product installation status and store the results into package
2034 -- variables
2035 --Parameters:
2036 --IN:
2037 --IN OUT:
2038 --OUT:
2039 --x_return_status
2040 -- status of the procedure
2041 --Returns:
2042 --Notes:
2043 --Testing:
2044 --End of Comments
2045 ------------------------------------------------------------------------
2046 PROCEDURE set_product_inst_status
2047 ( x_return_status OUT NOCOPY VARCHAR2
2048 ) IS
2049
2050 l_api_name CONSTANT VARCHAR2(50) := 'set_product_inst_status';
2051 l_api_version CONSTANT NUMBER := 1.0;
2052 l_progress VARCHAR2(3);
2053 l_return_status VARCHAR2(1);
2054 l_module CONSTANT FND_LOG_MESSAGES.module%TYPE :=
2055 G_MODULE_PREFIX || l_api_name || '.';
2056
2057 BEGIN
2058
2059 l_progress := '000';
2060
2061 IF (g_fnd_debug = 'Y') THEN
2062 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
2063 FND_LOG.string
2064 ( log_level => FND_LOG.LEVEL_PROCEDURE,
2065 module => l_module || l_progress,
2066 message => 'Entering ' || g_pkg_name || '.' || l_api_name
2067 );
2068 END IF;
2069 END IF;
2070
2071 x_return_status := FND_API.G_RET_STS_SUCCESS;
2072
2073 -- inv installation status
2074 get_installation_status
2075 ( x_return_status => l_return_status,
2076 p_appl_id => 401,
2077 x_inst_status => g_inv_install_status
2078 );
2079
2080 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2081 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2082 END IF;
2083 l_progress := '000';
2084
2085 -- wip installation status
2086 get_installation_status
2087 ( x_return_status => l_return_status,
2088 p_appl_id => 706,
2089 x_inst_status => g_wip_install_status
2090 );
2091
2092 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2093 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2094 END IF;
2095 l_progress := '000';
2096
2097 -- mrp installation status
2098 get_installation_status
2099 ( x_return_status => l_return_status,
2100 p_appl_id => 704,
2101 x_inst_status => g_mrp_install_status
2102 );
2103
2104 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2105 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2106 END IF;
2107 l_progress := '000';
2108
2109 -- pa installation status
2110 get_installation_status
2111 ( x_return_status => l_return_status,
2112 p_appl_id => 275,
2113 x_inst_status => g_pa_install_status
2114 );
2115
2116 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2117 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2118 END IF;
2119 l_progress := '000';
2120
2121 -- chv installation status
2122 get_installation_status
2123 ( x_return_status => l_return_status,
2124 p_appl_id => 202,
2125 x_inst_status => g_chv_install_status
2126 );
2127
2128 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2129 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2130 END IF;
2131 l_progress := '000';
2132
2133 -- pjm installation_status
2134 get_installation_status
2135 ( x_return_status => l_return_status,
2136 p_appl_id => 712,
2137 x_inst_status => g_pjm_install_status
2138 );
2139
2140 IF (g_fnd_debug = 'Y') THEN
2141 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2142 FND_LOG.string
2143 ( log_level => FND_LOG.LEVEL_STATEMENT,
2144 module => l_module || l_progress,
2145 message => 'inv = ' || g_inv_install_status ||
2146 ',wip = ' || g_wip_install_status ||
2147 ',mrp = ' || g_mrp_install_status ||
2148 ',pa = ' || g_pa_install_status ||
2149 ',chv = ' || g_chv_install_status ||
2150 ',pjm = ' || g_pjm_install_status
2151 );
2152 END IF;
2153 END IF;
2154
2155 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2156 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2157 END IF;
2158 l_progress := '010';
2159
2160 -- set g_set_product_inst_status to true to indicate that this procedure
2161 -- has been called and all the product installation status have been set
2162 g_set_product_inst_status := FND_API.G_TRUE;
2163
2164 IF (g_fnd_debug = 'Y') THEN
2165 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
2166 FND_LOG.string
2167 ( log_level => FND_LOG.LEVEL_PROCEDURE,
2168 module => l_module || l_progress,
2169 message => 'Quitting ' || g_pkg_name || '.' || l_api_name
2170 );
2171 END IF;
2172 END IF;
2173
2174 EXCEPTION
2175 WHEN OTHERS THEN
2176 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2177 FND_MSG_PUB.add_exc_msg
2178 ( p_pkg_name => g_pkg_name,
2179 p_procedure_name => l_api_name || '.' || l_progress
2180 );
2181
2182 END set_product_inst_status;
2183
2184
2185 -----------------------------------------------------------------------
2186 --Start of Comments
2187 --Name: get_installation_status
2188 --Pre-reqs:
2189 --Modifies:
2190 --Locks:
2191 -- None
2192 --Function:
2193 -- get the installation status of a specific product
2194 --Parameters:
2195 --IN:
2196 --p_appl_id
2197 -- ID for the product
2198 --IN OUT:
2199 --OUT:
2200 --x_return_status
2201 -- status of the procedure
2202 --x_inst_status
2203 -- indicate whether the product is installed or not. 'Y' represents that
2204 -- the product is isntalled
2205 --Returns:
2206 --Notes:
2207 --Testing:
2208 --End of Comments
2209 ------------------------------------------------------------------------
2210
2211 PROCEDURE get_installation_status
2212 ( x_return_status OUT NOCOPY VARCHAR2,
2213 p_appl_id IN NUMBER,
2214 x_inst_status OUT NOCOPY VARCHAR2
2215 ) IS
2216
2217 l_api_name VARCHAR2(50) := 'get_installation_status';
2218 l_progress VARCHAR2(3);
2219 l_module CONSTANT FND_LOG_MESSAGES.module%TYPE :=
2220 G_MODULE_PREFIX || l_api_name || '.';
2221
2222 l_inst_check VARCHAR2(1);
2223 l_dummy VARCHAR2(1);
2224
2225 l_fnd_inst_exception EXCEPTION;
2226 BEGIN
2227
2228 l_progress := '000';
2229
2230 IF (g_fnd_debug = 'Y') THEN
2231 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
2232 FND_LOG.string
2233 ( log_level => FND_LOG.LEVEL_PROCEDURE,
2234 module => l_module || l_progress,
2235 message => 'Entering ' || g_pkg_name || '.' || l_api_name
2236 );
2237 END IF;
2238 END IF;
2239
2240 x_return_status := FND_API.G_RET_STS_SUCCESS;
2241
2242 IF (FND_INSTALLATION.get( appl_id => p_appl_id,
2243 dep_appl_id => p_appl_id,
2244 status => l_inst_check,
2245 industry => l_dummy
2246 ))
2247 THEN
2248 l_progress := '010';
2249
2250 IF (l_inst_check = 'I') THEN
2251 x_inst_status := 'Y';
2252 ELSE
2253 x_inst_status := 'N';
2254 END IF;
2255 ELSE
2256 -- FND_INSTALLATION.get returns an error
2257
2258 l_progress := '020';
2259 RAISE l_fnd_inst_exception;
2260
2261 END IF; -- FND_INSTALLATION.get()
2262
2263 IF (g_fnd_debug = 'Y') THEN
2264 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
2265 FND_LOG.string
2266 ( log_level => FND_LOG.LEVEL_PROCEDURE,
2267 module => l_module || l_progress,
2268 message => 'Quitting ' || g_pkg_name || '.' || l_api_name
2269 );
2270 END IF;
2271 END IF;
2272
2273 EXCEPTION
2274 WHEN OTHERS THEN
2275 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2276 FND_MSG_PUB.add_exc_msg
2277 ( p_pkg_name => g_pkg_name,
2278 p_procedure_name => l_api_name || '.' || l_progress
2279 );
2280
2281 END get_installation_status;
2282
2283
2284 -----------------------------------------------------------------------
2285 --Start of Comments
2286 --Name: filter_referenced_req
2287 --Pre-reqs:
2288 --Modifies: po_purge_req_list
2289 --Locks:
2290 -- None
2291 --Function:
2292 -- Remove req from the req purge list if it is referenced by records
2293 -- that will not purged
2294 --Parameters:
2295 --IN:
2296 --IN OUT:
2297 --OUT:
2298 --x_return_status
2299 -- status of the procedure
2300 --Returns:
2301 --Notes:
2302 --Testing:
2303 --End of Comments
2304 ------------------------------------------------------------------------
2305
2306 PROCEDURE filter_referenced_req
2307 ( x_return_status OUT NOCOPY VARCHAR2
2308 )IS
2309
2310 l_api_name VARCHAR2(50) := 'filter_referenced_req';
2311 l_progress VARCHAR2(3);
2312 l_module CONSTANT FND_LOG_MESSAGES.module%TYPE :=
2313 G_MODULE_PREFIX || l_api_name || '.';
2314
2315 l_return_status VARCHAR2(1);
2316
2317 BEGIN
2318
2319 l_progress := '000';
2320
2321 IF (g_fnd_debug = 'Y') THEN
2322 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
2323 FND_LOG.string
2324 ( log_level => FND_LOG.LEVEL_PROCEDURE,
2325 module => l_module || l_progress,
2326 message => 'Entering ' || g_pkg_name || '.' || l_api_name
2327 );
2328 END IF;
2329 END IF;
2330
2331 x_return_status := FND_API.G_RET_STS_SUCCESS;
2332
2333 IF (g_chv_install_status = 'Y') THEN
2334 l_progress := '010';
2335
2336 UPDATE po_purge_req_list PPRL
2337 SET PPRL.double_check_flag = 'N'
2338 WHERE PPRL.double_check_flag = 'Y'
2339 AND EXISTS
2340 (SELECT NULL
2341 FROM chv_item_orders CIO
2342 WHERE CIO.document_header_id = PPRL.requisition_header_id
2343 AND CIO.supply_document_type = 'REQUISITION');
2344
2345 IF (g_fnd_debug = 'Y') THEN
2346 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2347 FND_LOG.string
2348 ( log_level => FND_LOG.LEVEL_STATEMENT,
2349 module => l_module || l_progress,
2350 message => 'Check CHV. Updated rowcount = ' || SQL%ROWCOUNT
2351 );
2352 END IF;
2353 END IF;
2354
2355 END IF; -- g_chv_install_status = 'Y'
2356
2357 IF (g_pa_install_status = 'Y') THEN
2358 l_progress := '020';
2359
2360 UPDATE po_purge_req_list PPRL
2361 SET PPRL.double_check_flag = 'N'
2362 WHERE PPRL.double_check_flag = 'Y'
2363 AND EXISTS
2364 (SELECT NULL
2365 FROM po_req_distributions RD,
2366 po_requisition_lines RL
2367 WHERE RL.requisition_header_id =
2368 PPRL.requisition_header_id
2369 AND RD.requisition_line_id =
2370 RL.requisition_line_id
2371 AND RD.project_id IS NOT NULL
2372 AND RL.destination_type_code = 'EXPENSE');
2373
2374 -- Bug 4459947
2375 -- Do not hardcode schema name.
2376 -- GSCC checker parses PA as schema name even though it's text
2377 -- due to extra _. Modified message from _PA_ to PA product.
2378 IF (g_fnd_debug = 'Y') THEN
2379 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2380 FND_LOG.string
2381 ( log_level => FND_LOG.LEVEL_STATEMENT,
2382 module => l_module || l_progress,
2383 message => 'Check PA product. ' ||
2384 'Updated rowcount = ' || SQL%ROWCOUNT
2385 );
2386 END IF;
2387 END IF;
2388
2389 END IF; -- g_pa_install_status = 'Y'
2390
2391 IF (g_pjm_install_status = 'Y') THEN
2392 l_progress := '030';
2393
2394 UPDATE po_purge_req_list PPRL
2395 SET PPRL.double_check_flag = 'N'
2396 WHERE PPRL.double_check_flag = 'Y'
2397 AND EXISTS
2398 (SELECT NULL
2399 FROM po_req_distributions RD,
2400 po_requisition_lines RL
2401 WHERE RL.requisition_header_id =
2402 PPRL.requisition_header_id
2403 AND RD.requisition_line_id =
2404 RL.requisition_line_id
2405 AND RD.project_id IS NOT NULL
2406 AND RL.destination_type_code IN ('INVENTORY',
2407 'SHOP FLOOR'));
2408 -- Bug 4459947
2409 -- Do not hardcode schema name.
2410 -- GSCC checker parses PJM as schema name even though it's text
2411 -- due to extra _. Modified message from _PJM_ to PJM product.
2412 IF (g_fnd_debug = 'Y') THEN
2413 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2414 FND_LOG.string
2415 ( log_level => FND_LOG.LEVEL_STATEMENT,
2416 module => l_module || l_progress,
2417 message => 'Check PJM product, ' ||
2418 'Updated rowcount = ' || SQL%ROWCOUNT
2419 );
2420 END IF;
2421 END IF;
2422
2423 END IF; -- g_pjm_install_status = 'Y'
2424
2425 PO_AP_PURGE_UTIL_PVT.log_purge_list_count
2426 ( p_module => l_module || l_progress,
2427 p_entity => 'REQ'
2428 );
2429
2430 l_progress := '040';
2431
2432 PO_AP_PURGE_UTIL_PVT.filter_more_referenced_req
2433 ( x_return_status => l_return_status
2434 );
2435
2436 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2437 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2438 END IF;
2439
2440 l_progress := '050';
2441
2442 PO_AP_PURGE_UTIL_PVT.log_purge_list_count
2443 ( p_module => l_module || l_progress,
2444 p_entity => 'REQ'
2445 );
2446
2447 IF (g_fnd_debug = 'Y') THEN
2448 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
2449 FND_LOG.string
2450 ( log_level => FND_LOG.LEVEL_PROCEDURE,
2451 module => l_module || l_progress,
2452 message => 'Quitting ' || g_pkg_name || '.' || l_api_name
2453 );
2454 END IF;
2455 END IF;
2456
2457 EXCEPTION
2458 WHEN OTHERS THEN
2459 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2460 FND_MSG_PUB.add_exc_msg
2461 ( p_pkg_name => g_pkg_name,
2462 p_procedure_name => l_api_name || '.' || l_progress
2463 );
2464
2465 END filter_referenced_req;
2466
2467
2468 -----------------------------------------------------------------------
2469 --Start of Comments
2470 --Name: filter_referenced_po
2471 --Pre-reqs:
2472 --Modifies: po_purge_po_list
2473 --Locks:
2474 -- None
2475 --Function:
2476 -- Remove po from the req purge list if it is referenced by records
2477 -- that will not purged
2478 --Parameters:
2479 --IN:
2480 --IN OUT:
2481 --OUT:
2482 --x_return_status
2483 -- status of the procedure
2484 --Returns:
2485 --Notes:
2486 --Testing:
2487 --End of Comments
2488 ------------------------------------------------------------------------
2489
2490 PROCEDURE filter_referenced_po
2491 ( x_return_status OUT NOCOPY VARCHAR2
2492 )IS
2493
2494 l_api_name VARCHAR2(50) := 'filter_referenced_po';
2495 l_progress VARCHAR2(3);
2496 l_return_status VARCHAR2(1);
2497 l_module CONSTANT FND_LOG_MESSAGES.module%TYPE :=
2498 G_MODULE_PREFIX || l_api_name || '.';
2499
2500 BEGIN
2501
2502 l_progress := '000';
2503
2504 IF (g_fnd_debug = 'Y') THEN
2505 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
2506 FND_LOG.string
2507 ( log_level => FND_LOG.LEVEL_PROCEDURE,
2508 module => l_module || l_progress,
2509 message => 'Entering ' || g_pkg_name || '.' || l_api_name
2510 );
2511 END IF;
2512 END IF;
2513
2514 x_return_status := FND_API.G_RET_STS_SUCCESS;
2515
2516 IF (g_inv_install_status = 'Y') THEN
2517 l_progress := '010';
2518
2519 UPDATE po_purge_po_list PPL
2520 SET PPL.double_check_flag = 'N'
2521 WHERE PPL.double_check_flag = 'Y'
2522 AND EXISTS (
2523 SELECT NULL
2524 FROM mtl_material_transactions MMT
2525 WHERE MMT.transaction_source_type_id = 1
2526 AND MMT.transaction_source_id = PPL.po_header_id);
2527
2528 -- Bug 4459947
2529 -- Do not hardcode schema name.
2530 -- GSCC checker parses INV as schema name even though it's text
2531 -- due to extra _. Modified message from _INV_ to INV product.
2532 IF (g_fnd_debug = 'Y') THEN
2533 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2534 FND_LOG.string
2535 ( log_level => FND_LOG.LEVEL_STATEMENT,
2536 module => l_module || l_progress,
2537 message => 'Check INV product. ' ||
2538 'Updated rowcount = ' || SQL%ROWCOUNT
2539 );
2540 END IF;
2541 END IF;
2542
2543 END IF; -- g_inv_install_status = 'Y'
2544
2545 IF (g_wip_install_status = 'Y') THEN
2546 l_progress := '020';
2547
2548 UPDATE po_purge_po_list PPL
2549 SET PPL.double_check_flag = 'N'
2550 WHERE PPL.double_check_flag = 'Y'
2551 AND EXISTS (
2552 SELECT NULL
2553 FROM wip_transactions WT
2554 WHERE WT.po_header_id = PPL.po_header_id);
2555
2556 -- Bug 4459947
2557 -- Do not hardcode schema name.
2558 -- GSCC checker parses WIP as schema name even though it's text
2559 -- due to extra _. Modified message from _WIP_ to WIP product.
2560 IF (g_fnd_debug = 'Y') THEN
2561 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2562 FND_LOG.string
2563 ( log_level => FND_LOG.LEVEL_STATEMENT,
2564 module => l_module || l_progress,
2565 message => 'Check WIP product. ' ||
2566 'Updated rowcount = ' || SQL%ROWCOUNT
2567 );
2568 END IF;
2569 END IF;
2570
2571 END IF; -- g_wip_install_status = 'Y'
2572
2573 IF (g_mrp_install_status = 'Y') THEN
2574 l_progress := '030';
2575
2576 UPDATE po_purge_po_list PPL
2577 SET PPL.double_check_flag = 'N'
2578 WHERE PPL.double_check_flag = 'Y'
2579 AND EXISTS (
2580 SELECT NULL
2581 FROM mrp_schedule_consumptions MSC
2582 WHERE MSC.disposition_type IN (2,6)
2583 AND MSC.disposition_id = PPL.po_header_id);
2584
2585 -- Bug 4459947
2586 -- Do not hardcode schema name.
2587 -- GSCC checker parses MRP as schema name even though it's text
2588 -- due to extra _. Modified message from _MRP_ to MRP product.
2589 IF (g_fnd_debug = 'Y') THEN
2590 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2591 FND_LOG.string
2592 ( log_level => FND_LOG.LEVEL_STATEMENT,
2593 module => l_module || l_progress,
2594 message => 'Check MRP product. ' ||
2595 'Updated rowcount = ' || SQL%ROWCOUNT
2596 );
2597 END IF;
2598 END IF;
2599
2600 END IF; -- g_mrp_install_status = 'Y'
2601
2602 IF (g_chv_install_status = 'Y') THEN
2603 l_progress := '040';
2604
2605 UPDATE po_purge_po_list PPL
2606 SET PPL.double_check_flag = 'N'
2607 WHERE PPL.double_check_flag = 'Y'
2608 AND EXISTS(
2609 SELECT NULL
2610 FROM chv_item_orders CIO
2611 WHERE CIO.document_header_id = PPL.po_header_id
2612 AND CIO.supply_document_type = 'RELEASE');
2613
2614 IF (g_fnd_debug = 'Y') THEN
2615 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2616 FND_LOG.string
2617 ( log_level => FND_LOG.LEVEL_STATEMENT,
2618 module => l_module || l_progress,
2619 message => 'Check CHV. Updated rowcount = ' || SQL%ROWCOUNT
2620 );
2621 END IF;
2622 END IF;
2623
2624 END IF; -- g_chv_install_status
2625
2626 IF (g_pa_install_status = 'Y') THEN
2627 l_progress := '050';
2628
2629 UPDATE po_purge_po_list PPL
2630 SET PPL.double_check_flag = 'N'
2631 WHERE PPL.double_check_flag = 'Y'
2632 AND EXISTS (
2633 SELECT NULL
2634 FROM po_distributions PD
2635 WHERE PPL.po_header_id = PD.po_header_id
2636 AND PD.project_id IS NOT NULL
2637 AND PD.destination_type_code = 'EXPENSE');
2638
2639 -- Bug 4459947
2640 -- Do not hardcode schema name.
2641 -- GSCC checker parses PA as schema name even though it's text
2642 -- due to extra _. Modified message from _PA_ to PA product.
2643 IF (g_fnd_debug = 'Y') THEN
2644 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2645 FND_LOG.string
2646 ( log_level => FND_LOG.LEVEL_STATEMENT,
2647 module => l_module || l_progress,
2648 message => 'Check PA product. ' ||
2649 'Updated rowcount = ' || SQL%ROWCOUNT
2650 );
2651 END IF;
2652 END IF;
2653
2654 END IF; -- g_pa_install_status = 'Y'
2655
2656 IF (g_pjm_install_status = 'Y') THEN
2657 l_progress := '060';
2658
2659 UPDATE po_purge_po_list PPL
2660 SET PPL.double_check_flag = 'N'
2661 WHERE PPL.double_check_flag = 'Y'
2662 AND EXISTS (
2663 SELECT NULL
2664 FROM po_distributions PD
2665 WHERE PPL.po_header_id = PD.po_header_id
2666 AND PD.project_id IS NOT NULL
2667 AND PD.destination_type_code IN ('INVENTORY',
2668 'SHOP FLOOR'));
2669 -- Bug 4459947
2670 -- Do not hardcode schema name.
2671 -- GSCC checker parses PJM as schema name even though it's text
2672 -- due to extra _. Modified message from _PJM_ to PJM product.
2673 IF (g_fnd_debug = 'Y') THEN
2674 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2675 FND_LOG.string
2676 ( log_level => FND_LOG.LEVEL_STATEMENT,
2677 module => l_module || l_progress,
2678 message => 'Check PJM product. ' ||
2679 'Updated rowcount = ' || SQL%ROWCOUNT
2680 );
2681 END IF;
2682 END IF;
2683 END IF; -- g_pjm_install_status
2684
2685 PO_AP_PURGE_UTIL_PVT.log_purge_list_count
2686 ( p_module => l_module || l_progress,
2687 p_entity => 'PO'
2688 );
2689
2690 l_progress := '070';
2691
2692 PO_AP_PURGE_UTIL_PVT.filter_more_referenced_po
2693 ( x_return_status => l_return_status
2694 );
2695
2696 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2697 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2698 END IF;
2699
2700 PO_AP_PURGE_UTIL_PVT.log_purge_list_count
2701 ( p_module => l_module || l_progress,
2702 p_entity => 'PO'
2703 );
2704
2705 IF (g_fnd_debug = 'Y') THEN
2706 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
2707 FND_LOG.string
2708 ( log_level => FND_LOG.LEVEL_PROCEDURE,
2709 module => l_module || l_progress,
2710 message => 'Quitting ' || g_pkg_name || '.' || l_api_name
2711 );
2712 END IF;
2713 END IF;
2714
2715 EXCEPTION
2716 WHEN OTHERS THEN
2717 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2718 FND_MSG_PUB.add_exc_msg
2719 ( p_pkg_name => g_pkg_name,
2720 p_procedure_name => l_api_name || '.' || l_progress
2721 );
2722
2723 END filter_referenced_po;
2724
2725
2726 -----------------------------------------------------------------------
2727 --Start of Comments
2728 --Name: filter_dependent_po_req_list
2729 --Pre-reqs:
2730 --Modifies: po_purge_po_list, po_purge_req_list
2731 --Locks:
2732 -- None
2733 --Function:
2734 -- Remove documents (req, po, ga, contract) from the purge list if their
2735 -- dependent documents are not in the purge list
2736 --Parameters:
2737 --IN:
2738 --IN OUT:
2739 --OUT:
2740 --x_return_status
2741 -- status of the procedure
2742 --x_po_records_filtered
2743 -- indicate whether a po has been excluded from the list after the procedure
2744 -- is executed
2745 --Returns:
2746 --Notes:
2747 --Testing:
2748 --End of Comments
2749 ------------------------------------------------------------------------
2750
2751 PROCEDURE filter_dependent_po_req_list
2752 ( x_return_status OUT NOCOPY VARCHAR2,
2753 x_po_records_filtered OUT NOCOPY VARCHAR2
2754 ) IS
2755
2756 l_api_name VARCHAR2(50) := 'filter_dependent_po_req_list';
2757 l_progress VARCHAR2(3);
2758 l_module CONSTANT FND_LOG_MESSAGES.module%TYPE :=
2759 G_MODULE_PREFIX || l_api_name || '.';
2760
2761 l_po_count NUMBER;
2762 l_req_count NUMBER;
2763
2764 BEGIN
2765 l_progress := '000';
2766
2767 IF (g_fnd_debug = 'Y') THEN
2768 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
2769 FND_LOG.string
2770 ( log_level => FND_LOG.LEVEL_PROCEDURE,
2771 module => l_module || l_progress,
2772 message => 'Entering ' || g_pkg_name || '.' || l_api_name
2773 );
2774 END IF;
2775 END IF;
2776
2777 x_return_status := FND_API.G_RET_STS_SUCCESS;
2778
2779 x_po_records_filtered := FND_API.G_FALSE;
2780
2781 LOOP
2782
2783 -- SQL What: 1) Remove PO from purge list if the corresponding
2784 -- requisition is not in the req purge list, and
2785 -- 2) Remove PO from purge list if the corresponding contract
2786 -- not in the PO purge list
2787 -- 3) Remove PO from purge list if the referencing GBPA is
2788 -- not in the purge list
2789 -- 4) Remove Contract from purge list if the po referencing
2790 -- it is not in the po purge list
2791 -- 5) Remove GBPA from purge list if the po referencing it
2792 -- is not in the purge list
2793 -- SQL Why: If PO has backing req, either both of them are purged or
2794 -- none of them are purged. Same for the PO-Contract and
2795 -- PO-GA relationship
2796
2797 UPDATE po_purge_po_list PPL
2798 SET PPL.double_check_flag = 'N'
2799 WHERE PPL.double_check_flag = 'Y'
2800 AND (EXISTS ( -- rm po if req not in purge list
2801 SELECT NULL
2802 FROM po_line_locations_all PLL,
2803 po_requisition_lines_all RL
2804 WHERE PLL.po_header_id = PPL.po_header_id
2805 AND PLL.line_location_id = RL.line_location_id
2806 AND NOT EXISTS (
2807 SELECT NULL
2808 FROM po_purge_req_list PRL
2809 WHERE PRL.requisition_header_id =
2810 RL.requisition_header_id
2811 AND PRL.double_check_flag = 'Y'))
2812 OR
2813 EXISTS ( -- rm po if contract not in purge list
2814 SELECT NULL
2815 FROM po_lines_all POL
2816 WHERE POL.po_header_id = PPL.po_header_id
2817 AND POL.contract_id IS NOT NULL
2818 AND NOT EXISTS (
2819 SELECT NULL
2820 FROM po_purge_po_list PPL1
2821 WHERE PPL1.double_check_flag = 'Y'
2822 AND PPL1.po_header_id =
2823 POL.contract_id))
2824 OR
2825 EXISTS ( -- rm po if ga not in purge list
2826 SELECT NULL
2827 FROM po_lines_all POL,
2828 po_headers_all POH
2829 WHERE PPL.po_header_id = POL.po_header_id
2830 AND POL.from_header_id = POH.po_header_id
2831 AND POH.type_lookup_code = 'BLANKET'
2832 AND POH.global_agreement_flag = 'Y'
2833 AND NOT EXISTS (
2834 SELECT NULL
2835 FROM po_purge_po_list PPL1
2836 WHERE PPL1.double_check_flag = 'Y'
2837 AND POH.po_header_id =
2838 PPL1.po_Header_id))
2839 OR
2840 EXISTS ( -- rm contract if po not in purge list
2841 SELECT NULL
2842 FROM po_lines_all POL
2843 WHERE PPL.po_header_id = POL.contract_id
2844 AND NOT EXISTS (
2845 SELECT NULL
2846 FROM po_purge_po_list PPL1
2847 WHERE PPL1.double_check_flag = 'Y'
2848 AND POL.po_header_id =
2849 PPL1.po_header_id))
2850 OR
2851 EXISTS ( -- rm ga if po not in purge list
2852 SELECT NULL
2853 FROM po_headers_all POH,
2854 po_lines_all POL
2855 WHERE POH.po_header_id = PPL.po_header_id
2856 AND POH.type_lookup_code = 'BLANKET'
2857 AND POH.global_agreement_flag = 'Y'
2858 AND POL.from_header_id = POH.po_header_id
2859 AND NOT EXISTS (
2860 SELECT NULL
2861 FROM po_purge_po_list PPL1
2862 WHERE PPL1.double_check_flag = 'Y'
2863 AND POL.po_header_id =
2864 PPL1.po_header_id)));
2865
2866 l_po_count := SQL%ROWCOUNT;
2867
2868
2869 -- SQL What: Remove REQ from purge list if the corresponding po
2870 -- is not in the po purge list
2871 -- SQL Why: If PO and REQ are linked, either both of them are purged or
2872 -- none of them are purged
2873
2874 UPDATE po_purge_req_list PPRL
2875 SET PPRL.double_check_flag = 'N'
2876 WHERE PPRL.double_check_flag = 'Y'
2877 AND EXISTS (
2878 SELECT NULL
2879 FROM po_requisition_lines_all RL,
2880 po_line_locations_all PLL
2881 WHERE RL.requisition_header_id =
2882 PPRL.requisition_header_id
2883 AND RL.line_location_id = PLL.line_location_id
2884 AND NOT EXISTS (
2885 SELECT NULL
2886 FROM po_purge_po_list PPL
2887 WHERE PPL.double_check_flag = 'Y'
2888 AND PPL.po_header_id =
2889 PLL.po_header_id));
2890 l_req_count := SQL%ROWCOUNT;
2891
2892 IF (g_fnd_debug = 'Y') THEN
2893 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2894 FND_LOG.string
2895 ( log_level => FND_LOG.LEVEL_STATEMENT,
2896 module => l_module || l_progress,
2897 message => 'Updated po_count = ' || l_po_count ||
2898 ', Updated req_count = ' || l_req_count
2899 );
2900 END IF;
2901 END IF;
2902
2903 IF (l_po_count > 0) THEN
2904 x_po_records_filtered := FND_API.G_TRUE;
2905 END IF;
2906
2907 PO_AP_PURGE_UTIL_PVT.log_purge_list_count
2908 ( p_module => l_module || l_progress,
2909 p_entity => 'REQ'
2910 );
2911
2912 PO_AP_PURGE_UTIL_PVT.log_purge_list_count
2913 ( p_module => l_module || l_progress,
2914 p_entity => 'PO'
2915 );
2916
2917 IF (l_req_count = 0 AND l_po_count = 0) THEN
2918 EXIT;
2919 END IF;
2920
2921 END LOOP;
2922
2923 IF (g_fnd_debug = 'Y') THEN
2924 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
2925 FND_LOG.string
2926 ( log_level => FND_LOG.LEVEL_PROCEDURE,
2927 module => l_module || l_progress,
2928 message => 'Quitting ' || g_pkg_name || '.' || l_api_name
2929 );
2930 END IF;
2931 END IF;
2932
2933 EXCEPTION
2934 WHEN OTHERS THEN
2935 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2936 FND_MSG_PUB.add_exc_msg
2937 ( p_pkg_name => g_pkg_name,
2938 p_procedure_name => l_api_name || '.' || l_progress
2939 );
2940
2941 END filter_dependent_po_req_list;
2942
2943
2944
2945 -----------------------------------------------------------------------
2946 --Start of Comments
2947 --Name: filter_dependent_po_ap_list
2948 --Pre-reqs:
2949 --Modifies: po_purge_po_list
2950 --Locks:
2951 -- None
2952 --Function:
2953 -- Remove PO from the list if its depending invoice is not in the purge list
2954 --Parameters:
2955 --IN:
2956 --IN OUT:
2957 --OUT:
2958 --x_return_status
2959 -- status of the procedure
2960 --x_po_records_filtered
2961 -- indicate whether any PO has been excluded after this procedure is executed
2962 --Returns:
2963 --Notes:
2964 --Testing:
2965 --End of Comments
2966 ------------------------------------------------------------------------
2967 PROCEDURE filter_dependent_po_ap_list
2968 ( x_return_status OUT NOCOPY VARCHAR2,
2969 x_po_records_filtered OUT NOCOPY VARCHAR2
2970 ) IS
2971
2972 l_api_name VARCHAR2(50) := 'filter_dependent_po_ap_list';
2973 l_progress VARCHAR2(3);
2974 l_module CONSTANT FND_LOG_MESSAGES.module%TYPE :=
2975 G_MODULE_PREFIX || l_api_name || '.';
2976
2977 l_po_count NUMBER;
2978
2979 BEGIN
2980 l_progress := '000';
2981
2982 IF (g_fnd_debug = 'Y') THEN
2983 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
2984 FND_LOG.string
2985 ( log_level => FND_LOG.LEVEL_PROCEDURE,
2986 module => l_module || l_progress,
2987 message => 'Entering ' || g_pkg_name || '.' || l_api_name
2988 );
2989 END IF;
2990 END IF;
2991
2992 x_return_status := FND_API.G_RET_STS_SUCCESS;
2993
2994 x_po_records_filtered := FND_API.G_FALSE;
2995
2996 -- SQL What: Remove PO from purge list if the Invoices referencing it
2997 -- is not in the po purge list
2998 -- SQL Why: If PO and Invoice are linked, either both of them are
2999 -- purged or none of them is purged
3000
3001 UPDATE po_purge_po_list PPL
3002 SET PPL.double_check_flag = 'N'
3003 WHERE PPL.double_check_flag = 'Y'
3004 AND EXISTS (
3005 SELECT NULL
3006 FROM po_distributions PD,
3007 ap_invoice_distributions AD
3008 WHERE PD.po_header_id = PPL.po_header_id
3009 AND AD.po_distribution_id = PD.po_distribution_id
3010 AND NOT EXISTS (
3011 SELECT NULL
3012 FROM ap_purge_invoice_list APL
3013 WHERE APL.invoice_id = AD.invoice_id));
3014
3015 l_po_count := SQL%ROWCOUNT;
3016
3017 IF (g_fnd_debug = 'Y') THEN
3018 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3019 FND_LOG.string
3020 ( log_level => FND_LOG.LEVEL_STATEMENT,
3021 module => l_module || l_progress,
3022 message => 'Updated po rowcount = ' || l_po_count
3023 );
3024 END IF;
3025 END IF;
3026
3027 IF (l_po_count > 0) THEN
3028 x_po_records_filtered := FND_API.G_TRUE;
3029 END IF;
3030
3031 PO_AP_PURGE_UTIL_PVT.log_purge_list_count
3032 ( p_module => l_module || l_progress,
3033 p_entity => 'PO'
3034 );
3035
3036 IF (g_fnd_debug = 'Y') THEN
3037 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
3038 FND_LOG.string
3039 ( log_level => FND_LOG.LEVEL_PROCEDURE,
3040 module => l_module || l_progress,
3041 message => 'Quitting ' || g_pkg_name || '.' || l_api_name
3042 );
3043 END IF;
3044 END IF;
3045
3046 EXCEPTION
3047 WHEN OTHERS THEN
3048 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3049 FND_MSG_PUB.add_exc_msg
3050 ( p_pkg_name => g_pkg_name,
3051 p_procedure_name => l_api_name || '.' || l_progress
3052 );
3053
3054 END filter_dependent_po_ap_list;
3055
3056
3057 -----------------------------------------------------------------------
3058 --Start of Comments
3059 --Name: remove_filtered_records
3060 --Pre-reqs:
3061 --Modifies: po_purge_po_list, po_purge_req_list
3062 --Locks:
3063 -- None
3064 --Function:
3065 -- Delete records from purge list if the record has double_check_flag = 'N'
3066 -- This is supposed to be called during initiation stage of the purge process
3067 --Parameters:
3068 --IN:
3069 --p_purge_category
3070 -- Describe what type of documents user wants to purge.
3071 --IN OUT:
3072 --OUT:
3073 --x_return_status
3074 -- status of the procedure
3075 --Returns:
3076 --Notes:
3077 --Testing:
3078 --End of Comments
3079 ------------------------------------------------------------------------
3080
3081 PROCEDURE remove_filtered_records
3082 ( x_return_status OUT NOCOPY VARCHAR2,
3083 p_purge_category IN VARCHAR2
3084 ) IS
3085
3086 l_api_name VARCHAR2(50) := 'remove_filtered_records';
3087 l_progress VARCHAR2(3);
3088 l_module CONSTANT FND_LOG_MESSAGES.module%TYPE :=
3089 G_MODULE_PREFIX || l_api_name || '.';
3090
3091 BEGIN
3092
3093 l_progress := '000';
3094
3095 IF (g_fnd_debug = 'Y') THEN
3096 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
3097 FND_LOG.string
3098 ( log_level => FND_LOG.LEVEL_PROCEDURE,
3099 module => l_module || l_progress,
3100 message => 'Entering ' || g_pkg_name || '.' || l_api_name
3101 );
3102 END IF;
3103 END IF;
3104
3105 x_return_status := FND_API.G_RET_STS_SUCCESS;
3106
3107 DELETE FROM po_purge_req_list
3108 WHERE double_check_flag = 'N';
3109
3110 IF (g_fnd_debug = 'Y') THEN
3111 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3112 FND_LOG.string
3113 ( log_level => FND_LOG.LEVEL_STATEMENT,
3114 module => l_module || l_progress,
3115 message => 'Deleted ' || SQL%ROWCOUNT || ' reqs from purge list'
3116 );
3117 END IF;
3118 END IF;
3119
3120 l_progress := '010';
3121
3122 IF (p_purge_category IN (PO_AP_PURGE_GRP.G_PUR_CAT_SIMPLE_PO,
3123 PO_AP_PURGE_GRP.G_PUR_CAT_MATCHED_PO_INV)) THEN
3124 l_progress := '020';
3125
3126 DELETE FROM po_purge_po_list
3127 WHERE double_check_flag = 'N';
3128
3129 IF (g_fnd_debug = 'Y') THEN
3130 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3131 FND_LOG.string
3132 ( log_level => FND_LOG.LEVEL_STATEMENT,
3133 module => l_module || l_progress,
3134 message => 'Deleted ' || SQL%ROWCOUNT || ' pos from purge list'
3135 );
3136 END IF;
3137 END IF;
3138
3139 END IF; -- p_purge_category
3140
3141 IF (g_fnd_debug = 'Y') THEN
3142 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
3143 FND_LOG.string
3144 ( log_level => FND_LOG.LEVEL_PROCEDURE,
3145 module => l_module || l_progress,
3146 message => 'Quitting ' || g_pkg_name || '.' || l_api_name
3147 );
3148 END IF;
3149 END IF;
3150
3151 EXCEPTION
3152 WHEN OTHERS THEN
3153 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3154 FND_MSG_PUB.add_exc_msg
3155 ( p_pkg_name => g_pkg_name,
3156 p_procedure_name => l_api_name || '.' || l_progress
3157 );
3158
3159 END remove_filtered_records;
3160
3161
3162 -----------------------------------------------------------------------
3163 --Start of Comments
3164 --Name: confirm_simple_req
3165 --Pre-reqs:
3166 --Modifies: po_purge_req_list
3167 --Locks:
3168 -- None
3169 --Function:
3170 -- Exclude req records from the purge list that have been updated recently
3171 --Parameters:
3172 --IN:
3173 --p_last_activity_date
3174 -- Cutoff date of the purge process. req will not be purged if it has been
3175 -- updated since this date
3176 --IN OUT:
3177 --OUT:
3178 --x_return_status
3179 -- status of the procedure
3180 --Returns:
3181 --Notes:
3182 --Testing:
3183 --End of Comments
3184 ------------------------------------------------------------------------
3185
3186 PROCEDURE confirm_simple_req
3187 ( x_return_status OUT NOCOPY VARCHAR2,
3188 p_last_activity_date IN DATE
3189 ) IS
3190
3191 l_api_name VARCHAR2(50) := 'confirm_simple_req';
3192 l_progress VARCHAR2(3);
3193 l_module CONSTANT FND_LOG_MESSAGES.module%TYPE :=
3194 G_MODULE_PREFIX || l_api_name || '.';
3195
3196 BEGIN
3197
3198 l_progress := '000';
3199
3200 IF (g_fnd_debug = 'Y') THEN
3201 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
3202 FND_LOG.string
3203 ( log_level => FND_LOG.LEVEL_PROCEDURE,
3204 module => l_module || l_progress,
3205 message => 'Entering ' || g_pkg_name || '.' || l_api_name
3206 );
3207 END IF;
3208 END IF;
3209
3210 x_return_status := FND_API.G_RET_STS_SUCCESS;
3211
3212 -- SQL What: Exclude Reqs that are no longer eligible for purging. The logic
3213 -- is similar to the one during initiation stage, but this time
3214 -- we are excluding records that are not eligible rather than
3215 -- including records that are eligible
3216 -- SQL Why: At Confirmation Stage, we need to make sure that all the
3217 -- remaining records in the purge list are still eligible for
3218 -- purging
3219
3220 UPDATE po_purge_req_list PPRL
3221 SET double_check_flag = 'N'
3222 WHERE double_check_flag = 'Y'
3223 AND NOT EXISTS (
3224 SELECT NULL
3225 FROM po_requisition_headers RH
3226 WHERE RH.requisition_header_id =
3227 PPRL.requisition_header_id
3228 AND RH.last_update_date <= p_last_activity_date
3229 AND (RH.closed_code = 'FINALLY CLOSED'
3230 OR RH.authorization_status = 'CANCELLED')
3231 AND NOT EXISTS (
3232 SELECT NULL
3233 FROM po_requisition_lines RL
3234 WHERE RL.requisition_header_id =
3235 RH.requisition_header_id
3236 AND NVL(RL.modified_by_agent_flag,'N') = 'N'
3237 AND (RL.last_update_date > p_last_activity_date
3238 OR
3239 RL.line_location_id IS NOT NULL
3240 OR
3241 RL.source_type_code = 'INVENTORY'
3242 OR
3243 EXISTS (
3244 SELECT NULL
3245 FROM po_price_differentials PPD
3246 WHERE PPD.entity_type = 'REQ LINE'
3247 AND PPD.entity_id =
3248 RL.requisition_line_id
3249 AND PPD.last_update_date >
3250 p_last_activity_date)
3251 OR
3252 EXISTS (
3253 SELECT NULL
3254 FROM po_req_distributions RD
3255 WHERE RD.requisition_line_id =
3256 RL.requisition_line_id
3257 AND RD.last_update_date >
3258 p_last_activity_date))));
3259
3260 IF (g_fnd_debug = 'Y') THEN
3261 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3262 FND_LOG.string
3263 ( log_level => FND_LOG.LEVEL_STATEMENT,
3264 module => l_module || l_progress,
3265 message => 'Excluded ' || SQL%ROWCOUNT || ' reqs in purge list'
3266 );
3267 END IF;
3268 END IF;
3269
3270 l_progress := '010';
3271
3272 PO_AP_PURGE_UTIL_PVT.log_purge_list_count
3273 ( p_module => l_module || l_progress,
3274 p_entity => 'REQ'
3275 );
3276
3277 IF (g_fnd_debug = 'Y') THEN
3278 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
3279 FND_LOG.string
3280 ( log_level => FND_LOG.LEVEL_PROCEDURE,
3281 module => l_module || l_progress,
3282 message => 'Quitting ' || g_pkg_name || '.' || l_api_name
3283 );
3284 END IF;
3285 END IF;
3286
3287 EXCEPTION
3288 WHEN OTHERS THEN
3289 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3290 FND_MSG_PUB.add_exc_msg
3291 ( p_pkg_name => g_pkg_name,
3292 p_procedure_name => l_api_name || '.' || l_progress
3293 );
3294
3295 END confirm_simple_req;
3296
3297
3298 -----------------------------------------------------------------------
3299 --Start of Comments
3300 --Name: confirm_po
3301 --Pre-reqs:
3302 --Modifies: po_purge_po_list
3303 --Locks:
3304 -- None
3305 --Function:
3306 -- Exclude pos that have been updated recently from the purge list
3307 --Parameters:
3308 --IN:
3309 --p_purge_category
3310 -- Describe what type of documents user wants to purge.
3311 --p_last_activity_date
3312 -- Cutoff date of the purge process. req will not be purged if it has been
3313 -- updated since this date
3314 --IN OUT:
3315 --OUT:
3316 --x_return_status
3317 -- status of the procedure
3318 --Returns:
3319 --Notes:
3320 --Testing:
3321 --End of Comments
3322 ------------------------------------------------------------------------
3323
3324 PROCEDURE confirm_po
3325 ( x_return_status OUT NOCOPY VARCHAR2,
3326 p_purge_category IN VARCHAR2,
3327 p_last_activity_date IN DATE
3328 ) IS
3329
3330 l_api_name VARCHAR2(50) := 'confirm_po';
3331 l_progress VARCHAR2(3);
3332 l_module CONSTANT FND_LOG_MESSAGES.module%TYPE :=
3333 G_MODULE_PREFIX || l_api_name || '.';
3334
3335 BEGIN
3336
3337 l_progress := '000';
3338
3339 IF (g_fnd_debug = 'Y') THEN
3340 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
3341 FND_LOG.string
3342 ( log_level => FND_LOG.LEVEL_PROCEDURE,
3343 module => l_module || l_progress,
3344 message => 'Entering ' || g_pkg_name || '.' || l_api_name
3345 );
3346 END IF;
3347 END IF;
3348
3349 x_return_status := FND_API.G_RET_STS_SUCCESS;
3350
3351 IF (p_purge_category = PO_AP_PURGE_GRP.G_PUR_CAT_SIMPLE_PO) THEN
3352
3353 l_progress := '010';
3354
3355 -- SQL What: Exclude POs that are no longer eligible for purging. The
3356 -- logic is similar to the one during initiation stage, but
3357 -- this time we are excluding records that are not eligible
3358 -- rather than including records that are eligible
3359 -- SQL Why: At Confirmation Stage, we need to make sure that all the
3360 -- remaining records in the purge list are still eligible for
3361 -- purging
3362
3363 UPDATE po_purge_po_list PPL
3364 SET double_check_flag = 'N'
3365 WHERE double_check_flag = 'Y'
3366 AND NOT EXISTS (
3367 SELECT NULL
3368 FROM po_headers PH
3369 WHERE PH.po_header_id = PPL.po_header_id
3370 AND PH.type_lookup_code IN ('STANDARD', 'BLANKET',
3371 'PLANNED', 'CONTRACT')
3372 AND (PH.cancel_flag = 'Y'
3373 OR PH.closed_code = 'FINALLY CLOSED')
3374 AND PH.last_update_date <= p_last_activity_date
3375 AND NOT EXISTS
3376 (SELECT NULL
3377 FROM po_releases PR
3378 WHERE PR.po_header_id = PPL.po_header_id
3379 AND PR.last_update_date > p_last_activity_date)
3380 AND NOT EXISTS
3381 (SELECT NULL
3382 FROM po_lines PL
3383 WHERE PL.po_header_id = PPL.po_header_id
3384 AND (PL.last_update_date > p_last_activity_date
3385 OR
3386 EXISTS
3387 (SELECT NULL
3388 FROM po_price_differentials PPD
3389 WHERE PPD.entity_type IN ('PO LINE',
3390 'BLANKET LINE')
3391 AND PPD.entity_id = PL.po_line_id
3392 AND PPD.last_update_date >
3393 p_last_activity_date)))
3394 AND NOT EXISTS
3395 (SELECT NULL
3396 FROM po_line_locations PLL
3397 WHERE PLL.po_header_id = PPL.po_header_id
3398 AND (PLL.last_update_date >p_last_activity_date
3399 OR
3400 EXISTS
3401 (SELECT NULL
3402 FROM po_price_differentials PPD
3403 WHERE PPD.entity_type = 'PRICE BREAK'
3404 AND PPD.entity_id =
3405 PLL.line_location_id
3406 AND PPD.last_update_date >
3407 p_last_activity_date)))
3408 AND NOT EXISTS
3409 (SELECT NULL
3410 FROM po_distributions PD
3411 WHERE PD.po_header_id = PPL.po_header_id
3412 AND (PD.last_update_date > p_last_activity_date
3413 OR
3414 EXISTS
3415 (SELECT NULL
3416 FROM ap_invoice_distributions AD
3417 WHERE AD.po_distribution_id =
3418 PD.po_distribution_id))));
3419
3420 ELSIF (p_purge_category = PO_AP_PURGE_GRP.G_PUR_CAT_MATCHED_PO_INV) THEN
3421
3422 l_progress := '020';
3423
3424 -- SQL What: Exclude POs that are no longer eligible for purging. The
3425 -- logic is similar to the one during initiation stage, but
3426 -- this time we are excluding records that are not eligible
3427 -- rather than including records that are eligible
3428 -- SQL Why: At Confirmation Stage, we need to make sure that all the
3429 -- remaining records in the purge list are still eligible for
3430 -- purging
3431
3432 UPDATE po_purge_po_list PPL
3433 SET double_check_flag = 'N'
3434 WHERE double_check_flag = 'Y'
3435 AND NOT EXISTS (
3436 SELECT NULL
3437 FROM po_headers PH
3438 WHERE PH.po_header_id = PPL.po_header_id
3439 AND PH.type_lookup_code IN ('STANDARD', 'BLANKET',
3440 'PLANNED', 'CONTRACT')
3441 AND (PH.cancel_flag = 'Y'
3442 OR PH.closed_code = 'FINALLY CLOSED')
3443 AND PH.last_update_date <= p_last_activity_date
3444 AND NOT EXISTS
3445 (SELECT NULL
3446 FROM po_releases PR
3447 WHERE PR.po_header_id = PH.po_header_id
3448 AND PR.last_update_date > p_last_activity_date)
3449 AND NOT EXISTS
3450 (SELECT NULL
3451 FROM po_lines PL
3452 WHERE PL.po_header_id = PH.po_header_id
3453 AND (PL.last_update_date > p_last_activity_date
3454 OR
3455 EXISTS
3456 (SELECT NULL
3457 FROM po_price_differentials PPD
3458 WHERE PPD.entity_type IN ('PO LINE',
3459 'BLANKET LINE')
3460 AND PPD.entity_id = PL.po_line_id
3461 AND PPD.last_update_date >
3462 p_last_activity_date)))
3463 AND NOT EXISTS
3464 (SELECT NULL
3465 FROM po_line_locations PLL
3466 WHERE PLL.po_header_id = PH.po_header_id
3467 AND (PLL.last_update_date > p_last_activity_date
3468 OR
3469 EXISTS
3470 (SELECT NULL
3471 FROM po_price_differentials PPD
3472 WHERE PPD.entity_type = 'PRICE BREAK'
3473 AND PPD.entity_id =
3474 PLL.line_location_id
3475 AND PPD.last_update_date >
3476 p_last_activity_date)))
3477 AND NOT EXISTS
3478 (SELECT NULL
3479 FROM po_distributions PD
3480 WHERE PD.po_header_id = PH.po_header_id
3481 AND PD.last_update_date >
3482 p_last_activity_date));
3483
3484 END IF; -- purge_category = 'SIMPLE PO'
3485
3486
3487 IF (g_fnd_debug = 'Y') THEN
3488 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3489 FND_LOG.string
3490 ( log_level => FND_LOG.LEVEL_STATEMENT,
3491 module => l_module || l_progress,
3492 message => 'Excluded ' || SQL%ROWCOUNT || ' pos frm purge ' ||
3493 'list. p_purge_category = ' || p_purge_category
3494 );
3495 END IF;
3496 END IF;
3497
3498 l_progress := '030';
3499
3500 -- SQL What: Exclude REQs that are no longer eligible for purging. The
3501 -- logic is similar to the one during initiation stage, but
3502 -- this time we are excluding records that are not eligible
3503 -- rather than including records that are eligible
3504 -- SQL Why: At Confirmation Stage, we need to make sure that all the
3505 -- remaining records in the purge list are still eligible for
3506 -- purging
3507 UPDATE po_purge_req_list PPRL
3508 SET double_check_flag = 'N'
3509 WHERE double_check_flag = 'Y'
3510 AND NOT EXISTS (
3511 SELECT NULL
3512 FROM po_requisition_headers RH
3513 WHERE RH.requisition_header_id =
3514 PPRL.requisition_header_id
3515 AND RH.last_update_date <= p_last_activity_date
3516 AND (RH.closed_code = 'FINALLY CLOSED'
3517 OR RH.authorization_status = 'CANCELLED')
3518 AND NOT EXISTS (
3519 SELECT NULL
3520 FROM po_requisition_lines RL
3521 WHERE RL.requisition_header_id =
3522 RH.requisition_header_id
3523 AND NVL(RL.modified_by_agent_flag,'N') = 'N'
3524 AND (RL.last_update_date > p_last_activity_date
3525 OR
3526 RL.source_type_code = 'INVENTORY'
3527 OR
3528 EXISTS (
3529 SELECT NULL
3530 FROM po_price_differentials PPD
3531 WHERE PPD.entity_type = 'REQ LINE'
3532 AND PPD.entity_id =
3533 RL.requisition_line_id
3534 AND PPD.last_update_date >
3535 p_last_activity_date)
3536 OR
3537 EXISTS (
3538 SELECT NULL
3539 FROM po_req_distributions RD
3540 WHERE RD.requisition_line_id =
3541 RL.requisition_line_id
3542 AND RD.last_update_date >
3543 p_last_activity_date))));
3544
3545
3546 IF (g_fnd_debug = 'Y') THEN
3547 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3548 FND_LOG.string
3549 ( log_level => FND_LOG.LEVEL_STATEMENT,
3550 module => l_module || l_progress,
3551 message => 'Excluded ' || SQL%ROWCOUNT || ' reqs frm purge ' ||
3552 'list'
3553 );
3554 END IF;
3555 END IF;
3556
3557 PO_AP_PURGE_UTIL_PVT.log_purge_list_count
3558 ( p_module => l_module || l_progress,
3559 p_entity => 'REQ'
3560 );
3561
3562 PO_AP_PURGE_UTIL_PVT.log_purge_list_count
3563 ( p_module => l_module || l_progress,
3564 p_entity => 'PO'
3565 );
3566
3567 IF (g_fnd_debug = 'Y') THEN
3568 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
3569 FND_LOG.string
3570 ( log_level => FND_LOG.LEVEL_PROCEDURE,
3571 module => l_module || l_progress,
3572 message => 'Quitting ' || g_pkg_name || '.' || l_api_name
3573 );
3574 END IF;
3575 END IF;
3576
3577 EXCEPTION
3578 WHEN OTHERS THEN
3579 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3580 FND_MSG_PUB.add_exc_msg
3581 ( p_pkg_name => g_pkg_name,
3582 p_procedure_name => l_api_name || '.' || l_progress
3583 );
3584
3585 END confirm_po;
3586
3587
3588 -----------------------------------------------------------------------
3589 --Start of Comments
3590 --Name: get_purge_list_range
3591 --Pre-reqs:
3592 --Modifies: None
3593 --Locks:
3594 -- None
3595 --Function:
3596 -- return the min id and max id among all documents in the purge list
3597 --Parameters:
3598 --IN:
3599 --p_category
3600 -- REQ - get the values from req purge list
3601 -- PO - get the values from po purge list
3602 --IN OUT:
3603 --OUT:
3604 --x_return_status
3605 -- status of the procedure
3606 --x_lower_limit
3607 -- min id in the purge list
3608 --x_upper_limit
3609 -- max id in the purge list
3610 --Returns:
3611 --Notes:
3612 --Testing:
3613 --End of Comments
3614 ------------------------------------------------------------------------
3615
3616 PROCEDURE get_purge_list_range
3617 ( x_return_status OUT NOCOPY VARCHAR2,
3618 p_category IN VARCHAR2,
3619 x_lower_limit OUT NOCOPY NUMBER,
3620 x_upper_limit OUT NOCOPY NUMBER
3621 ) IS
3622
3623 l_api_name VARCHAR2(50) := 'get_purge_list_range';
3624 l_progress VARCHAR2(3);
3625 l_module CONSTANT FND_LOG_MESSAGES.module%TYPE :=
3626 G_MODULE_PREFIX || l_api_name || '.';
3627
3628 BEGIN
3629
3630 l_progress := '000';
3631
3632 IF (g_fnd_debug = 'Y') THEN
3633 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
3634 FND_LOG.string
3635 ( log_level => FND_LOG.LEVEL_PROCEDURE,
3636 module => l_module || l_progress,
3637 message => 'Entering ' || g_pkg_name || '.' || l_api_name
3638 );
3639 END IF;
3640 END IF;
3641
3642 x_return_status := FND_API.G_RET_STS_SUCCESS;
3643
3644 IF (p_category = 'REQ') THEN
3645 l_progress := '010';
3646
3647 -- SQL What: Get the lower and upper bound of the req header ids in
3648 -- req purge list
3649 -- SQL Why: They are return values
3650 SELECT NVL ( MIN (PPRL.requisition_header_id), -1 ),
3651 NVL ( MAX (PPRL.requisition_header_id), -1 )
3652 INTO x_lower_limit,
3653 x_upper_limit
3654 FROM po_purge_req_list PPRL
3655 WHERE PPRL.double_check_flag = 'Y';
3656
3657 ELSIF (p_category = 'PO') THEN
3658 l_progress := '020';
3659
3660 -- SQL What: Get the lower and upper bound of the po header ids in
3661 -- po purge list
3662 -- SQL Why: They are return values
3663 SELECT NVL ( MIN (PPL.po_header_id), -1 ),
3664 NVL ( MAX (PPL.po_header_id), -1 )
3665 INTO x_lower_limit,
3666 x_upper_limit
3667 FROM po_purge_po_list PPL
3668 WHERE PPL.double_check_flag = 'Y';
3669
3670 END IF; -- p_category
3671
3672
3673 IF (g_fnd_debug = 'Y') THEN
3674 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3675 FND_LOG.string
3676 ( log_level => FND_LOG.LEVEL_STATEMENT,
3677 module => l_module || l_progress,
3678 message => 'p_category = ' || p_category || ', lower_limit = ' ||
3679 x_lower_limit || ', higher_limit = ' || x_upper_limit
3680 );
3681 END IF;
3682 END IF;
3683
3684 IF (g_fnd_debug = 'Y') THEN
3685 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
3686 FND_LOG.string
3687 ( log_level => FND_LOG.LEVEL_PROCEDURE,
3688 module => l_module || l_progress,
3689 message => 'Quitting ' || g_pkg_name || '.' || l_api_name
3690 );
3691 END IF;
3692 END IF;
3693
3694 EXCEPTION
3695 WHEN OTHERS THEN
3696 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3697 FND_MSG_PUB.add_exc_msg
3698 ( p_pkg_name => g_pkg_name,
3699 p_procedure_name => l_api_name || '.' || l_progress
3700 );
3701
3702 END get_purge_list_range;
3703
3704
3705 -----------------------------------------------------------------------
3706 --Start of Comments
3707 --Name: summarize_reqs
3708 --Pre-reqs:
3709 --Modifies: po_history_requisitions
3710 --Locks:
3711 -- None
3712 --Function:
3713 -- Record necessary information for requisitions that are about to be purged
3714 --Parameters:
3715 --IN:
3716 --p_purge_name
3717 -- Name of this purge process
3718 --p_range_size
3719 -- The id range size of the documents being inserted into history tables
3720 -- per commit cycle
3721 --p_req_lower_limit
3722 -- min id among all reqs to be purged
3723 --p_req_upper_limit
3724 -- max id among all reqs to be purged
3725 --IN OUT:
3726 --OUT:
3727 --x_return_status
3728 -- status of the procedure
3729 --Returns:
3730 --Notes:
3731 --Testing:
3732 --End of Comments
3733 ------------------------------------------------------------------------
3734
3735 PROCEDURE summarize_reqs
3736 ( x_return_status OUT NOCOPY VARCHAR2,
3737 p_purge_name IN VARCHAR2,
3738 p_range_size IN NUMBER,
3739 p_req_lower_limit IN NUMBER,
3740 p_req_upper_limit IN NUMBER
3741 ) IS
3742
3743 l_api_name VARCHAR2(50) := 'summarize_reqs';
3744 l_progress VARCHAR2(3);
3745 l_module CONSTANT FND_LOG_MESSAGES.module%TYPE :=
3746 G_MODULE_PREFIX || l_api_name || '.';
3747
3748 l_range_inserted VARCHAR2(1);
3749 l_range_low NUMBER;
3750 l_range_high NUMBER;
3751
3752 BEGIN
3753
3754 l_progress := '000';
3755
3756 IF (g_fnd_debug = 'Y') THEN
3757 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
3758 FND_LOG.string
3759 ( log_level => FND_LOG.LEVEL_PROCEDURE,
3760 module => l_module || l_progress,
3761 message => 'Entering ' || g_pkg_name || '.' || l_api_name
3762 );
3763 END IF;
3764 END IF;
3765
3766 x_return_status := FND_API.G_RET_STS_SUCCESS;
3767
3768 IF (p_req_lower_limit = -1) THEN
3769 RETURN;
3770 END IF;
3771
3772 l_range_low := p_req_lower_limit;
3773 l_range_high := p_req_lower_limit + p_range_size;
3774
3775 LOOP
3776 l_progress := '010';
3777
3778 IF (g_fnd_debug = 'Y') THEN
3779 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3780 FND_LOG.string
3781 ( log_level => FND_LOG.LEVEL_STATEMENT,
3782 module => l_module || l_progress,
3783 message => 'range_low=' || l_range_low ||
3784 ' range_high=' || l_range_high
3785 );
3786 END IF;
3787 END IF;
3788
3789 BEGIN
3790
3791 -- SQL What: Return 'Y' if there are req records being inserted
3792 -- inot req history table by the current batch
3793 -- SQL Why: We need to make sure that the current batch of records
3794 -- has not been inserted into history tables; otherwise
3795 -- we may end up inserting the same records multiple
3796 -- times.
3797 SELECT 'Y'
3798 INTO l_range_inserted
3799 FROM dual
3800 WHERE EXISTS (
3801 SELECT NULL
3802 FROM po_requisition_headers PRH,
3803 po_history_requisitions PHR
3804 WHERE PRH.segment1 = PHR.segment1
3805 AND PRH.type_lookup_code = PHR.type_lookup_code
3806 AND PHR.purge_name = p_purge_name
3807 AND PRH.requisition_header_id BETWEEN l_range_low
3808 AND l_range_high);
3809
3810 EXCEPTION
3811 WHEN NO_DATA_FOUND THEN
3812 l_progress := '020';
3813 l_range_inserted := 'N';
3814 END;
3815
3816
3817 IF (g_fnd_debug = 'Y') THEN
3818 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3819 FND_LOG.string
3820 ( log_level => FND_LOG.LEVEL_STATEMENT,
3821 module => l_module || l_progress,
3822 message => 'range inserted = ' || l_range_inserted
3823 );
3824 END IF;
3825 END IF;
3826
3827 IF (l_range_inserted = 'N') THEN
3828 l_progress := '030';
3829
3830 -- bug3256316
3831 -- Also calculate amount for amount based lines
3832
3833 -- SQL What: Insert requisition information into history tables
3834 -- SQL Why: Summarization functionality
3835
3836 INSERT INTO po_history_requisitions
3837 ( segment1,
3838 type_lookup_code,
3839 creation_date,
3840 requisition_total,
3841 preparer_name,
3842 purge_name,
3843 org_id -- bug5446437
3844 )
3845 SELECT RH.segment1,
3846 RH.type_lookup_code,
3847 RH.creation_date,
3848 SUM( DECODE (RL.amount,
3849 NULL, RL.quantity * RL.unit_price,
3850 RL.amount)),
3851 PAPF.full_name,
3852 p_purge_name,
3853 RH.org_id -- bug5446437
3854 FROM po_purge_req_list PRL,
3855 per_all_people_f PAPF,
3856 po_requisition_headers RH,
3857 po_requisition_lines RL
3858 WHERE PRL.requisition_header_id = RH.requisition_header_id
3859 AND RH.requisition_header_id = RL.requisition_header_id
3860 AND RH.preparer_id = PAPF.person_id
3861 AND TRUNC(SYSDATE) BETWEEN PAPF.effective_start_date
3862 AND PAPF.effective_end_date
3863 AND PRL.double_check_flag = 'Y'
3864 AND PRL.requisition_header_id BETWEEN l_range_low
3865 AND l_range_high
3866 GROUP BY RH.segment1,
3867 RH.type_lookup_code,
3868 RH.creation_date,
3869 PAPF.full_name,
3870 RH.org_id; -- bug5446437
3871 COMMIT;
3872
3873 END IF; -- l_range_inserted = 'N'
3874
3875
3876 l_range_low := l_range_high + 1;
3877 l_range_high := l_range_low + p_range_size;
3878
3879 IF (l_range_low > p_req_upper_limit) THEN
3880 l_progress := '040';
3881
3882 EXIT;
3883 END IF;
3884
3885 END LOOP;
3886
3887 IF (g_fnd_debug = 'Y') THEN
3888 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
3889 FND_LOG.string
3890 ( log_level => FND_LOG.LEVEL_PROCEDURE,
3891 module => l_module || l_progress,
3892 message => 'Quitting ' || g_pkg_name || '.' || l_api_name
3893 );
3894 END IF;
3895 END IF;
3896
3897 EXCEPTION
3898 WHEN OTHERS THEN
3899 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3900 FND_MSG_PUB.add_exc_msg
3901 ( p_pkg_name => g_pkg_name,
3902 p_procedure_name => l_api_name || '.' || l_progress
3903 );
3904
3905 END summarize_reqs;
3906
3907
3908 -----------------------------------------------------------------------
3909 --Start of Comments
3910 --Name: summarize_pos
3911 --Pre-reqs:
3912 --Modifies: po_history_pos
3913 --Locks:
3914 -- None
3915 --Function:
3916 -- Record necessary information for pos that are about to be purged
3917 --Parameters:
3918 --IN:
3919 --p_purge_name
3920 -- Name of this purge process
3921 --p_range_size
3922 -- The id range size of the documents being inserted into history tables
3923 -- per commit cycle
3924 --p_po_lower_limit
3925 -- min id among all pos to be purged
3926 --p_po_upper_limit
3927 -- max id among all pos to be purged
3928 --IN OUT:
3929 --OUT:
3930 --x_return_status
3931 -- status of the procedure
3932 --Returns:
3933 --Notes:
3934 --Testing:
3935 --End of Comments
3936 ------------------------------------------------------------------------
3937
3938 PROCEDURE summarize_pos
3939 ( x_return_status OUT NOCOPY VARCHAR2,
3940 p_purge_name IN VARCHAR2,
3941 p_range_size IN NUMBER,
3942 p_po_lower_limit IN NUMBER,
3943 p_po_upper_limit IN NUMBER
3944 ) IS
3945
3946 l_api_name VARCHAR2(50) := 'summarize_pos';
3947 l_progress VARCHAR2(3);
3948 l_module CONSTANT FND_LOG_MESSAGES.module%TYPE :=
3949 G_MODULE_PREFIX || l_api_name || '.';
3950
3951 l_range_inserted VARCHAR2(1);
3952 l_range_low NUMBER;
3953 l_range_high NUMBER;
3954
3955 BEGIN
3956
3957 l_progress := '000';
3958
3959 IF (g_fnd_debug = 'Y') THEN
3960 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
3961 FND_LOG.string
3962 ( log_level => FND_LOG.LEVEL_PROCEDURE,
3963 module => l_module || l_progress,
3964 message => 'Entering ' || g_pkg_name || '.' || l_api_name
3965 );
3966 END IF;
3967 END IF;
3968
3969 x_return_status := FND_API.G_RET_STS_SUCCESS;
3970
3971 IF (p_po_lower_limit = -1) THEN
3972 RETURN;
3973 END IF;
3974
3975 l_range_low := p_po_lower_limit;
3976 l_range_high := p_po_lower_limit + p_range_size;
3977
3978 LOOP
3979 l_progress := '010';
3980
3981 IF (g_fnd_debug = 'Y') THEN
3982 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3983 FND_LOG.string
3984 ( log_level => FND_LOG.LEVEL_STATEMENT,
3985 module => l_module || l_progress,
3986 message => 'range_low=' || l_range_low ||
3987 ' range_high=' || l_range_high
3988 );
3989 END IF;
3990 END IF;
3991
3992 BEGIN
3993
3994 -- SQL What: Return 'Y' if there are po records being inserted
3995 -- inot po history table by the current batch
3996 -- SQL Why: We need to make sure that the current batch of records
3997 -- has not been inserted into history tables; otherwise
3998 -- we may end up inserting the same records multiple
3999 -- times.
4000 SELECT 'Y'
4001 INTO l_range_inserted
4002 FROM dual
4003 WHERE EXISTS (
4004 SELECT NULL
4005 FROM po_headers PH,
4006 po_history_pos PHP
4007 WHERE PH.segment1 = PHP.segment1
4008 AND PH.type_lookup_code = PHP.type_lookup_code
4009 AND PHP.purge_name = p_purge_name
4010 AND PH.po_header_id BETWEEN l_range_low
4011 AND l_range_high);
4012
4013 EXCEPTION
4014 WHEN NO_DATA_FOUND THEN
4015 l_progress := '020';
4016 l_range_inserted := 'N';
4017 END;
4018
4019
4020 IF (g_fnd_debug = 'Y') THEN
4021 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
4022 FND_LOG.string
4023 ( log_level => FND_LOG.LEVEL_STATEMENT,
4024 module => l_module || l_progress,
4025 message => 'range inserted = ' || l_range_inserted
4026 );
4027 END IF;
4028 END IF;
4029
4030 IF (l_range_inserted = 'N') THEN -- current batch is not inserted
4031 l_progress := '030';
4032
4033 -- SQL What: Insert data into history tables for records in the
4034 -- purge list that are Standard POs, Planned POs, and
4035 -- Local Blanket PA
4036 -- SQL Why: Need to record data in history table before actual
4037 -- purge happens
4038
4039 INSERT INTO po_history_pos
4040 ( segment1,
4041 type_lookup_code,
4042 vendor_id,
4043 vendor_site_code,
4044 po_total,
4045 currency_code,
4046 agent_name,
4047 creation_date,
4048 purge_name,
4049 org_id -- bug5446437
4050 )
4051 SELECT PH.segment1,
4052 PH.type_lookup_code,
4053 PH.vendor_id,
4054 VDS.vendor_site_code,
4055 NVL (ROUND (
4056 SUM (
4057 DECODE (
4058 PLL.quantity,
4059 NULL,
4060 PLL.amount - NVL(PLL.amount_cancelled, 0),
4061 (PLL.quantity - NVL(PLL.quantity_cancelled,
4062 0)) *
4063 PLL.price_override)),
4064 2),
4065 0),
4066 PH.currency_code,
4067 PAPF.full_name,
4068 PH.creation_date,
4069 p_purge_name,
4070 PH.org_id -- bug5446437
4071 FROM per_all_people_f PAPF,
4072 po_vendor_sites VDS,
4073 po_headers PH,
4074 po_line_locations PLL,
4075 po_purge_po_list PPL
4076 WHERE PPL.po_header_id BETWEEN l_range_low AND l_range_high
4077 AND PPL.double_check_flag = 'Y'
4078 AND PPL.po_header_id = PH.po_header_id
4079 AND NVL(PH.global_agreement_flag, 'N') = 'N'
4080 AND PH.type_lookup_code IN ('STANDARD', 'PLANNED', 'BLANKET')
4081 AND PH.po_header_id = PLL.po_header_id(+)
4082 AND PLL.shipment_type (+) <> 'PRICE BREAK'
4083 AND PH.agent_id = PAPF.person_id
4084 AND TRUNC(SYSDATE) BETWEEN PAPF.effective_start_date
4085 AND PAPF.effective_end_date
4086 AND PH.vendor_site_id = VDS.vendor_site_id
4087 GROUP BY PH.segment1,
4088 PH.type_lookup_code,
4089 PH.vendor_id,
4090 VDS.vendor_site_code,
4091 PH.currency_code,
4092 PAPF.full_name,
4093 PH.creation_date,
4094 PH.org_id; -- bug5446437
4095
4096
4097 IF (g_fnd_debug = 'Y') THEN
4098 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
4099 FND_LOG.string
4100 ( log_level => FND_LOG.LEVEL_STATEMENT,
4101 module => l_module || l_progress,
4102 message => 'Rows inserted for std/planned po/local bpa:' ||
4103 SQL%ROWCOUNT
4104 );
4105 END IF;
4106 END IF;
4107
4108 l_progress := '040';
4109
4110 -- SQL What: Insert data into history tables for records in the
4111 -- purge list that are Global Blanket PA
4112 -- SQL Why: Need to record data in history table before actual
4113 -- purge happens
4114
4115 INSERT INTO po_history_pos
4116 ( segment1,
4117 type_lookup_code,
4118 vendor_id,
4119 vendor_site_code,
4120 po_total,
4121 currency_code,
4122 agent_name,
4123 creation_date,
4124 purge_name,
4125 org_id -- bug5446437
4126 )
4127 SELECT PH.segment1,
4128 PH.type_lookup_code,
4129 PH.vendor_id,
4130 VDS.vendor_site_code,
4131 NVL (ROUND (
4132 SUM (
4133 DECODE (
4134 PLL.quantity,
4135 NULL,
4136 PLL.amount - NVL(PLL.amount_cancelled, 0),
4137 (PLL.quantity - NVL(PLL.quantity_cancelled,
4138 0)) *
4139 PLL.price_override)),
4140 2),
4141 0),
4142 PH.currency_code,
4143 PAPF.full_name,
4144 PH.creation_date,
4145 p_purge_name,
4146 PH.org_id -- bug5446437
4147 FROM per_all_people_f PAPF,
4148 po_vendor_sites VDS,
4149 po_headers PH,
4150 po_line_locations_all PLL,
4151 po_purge_po_list PPL
4152 WHERE PPL.po_header_id BETWEEN l_range_low AND l_range_high
4153 AND PPL.double_check_flag = 'Y'
4154 AND PPL.po_header_id = PH.po_header_id
4155 AND PH.global_agreement_flag = 'Y'
4156 AND PH.type_lookup_code = 'BLANKET'
4157 AND PH.po_header_id = PLL.from_header_id(+)
4158 AND PH.agent_id = PAPF.person_id
4159 AND TRUNC(SYSDATE) BETWEEN PAPF.effective_start_date
4160 AND PAPF.effective_end_date
4161 AND PH.vendor_site_id = VDS.vendor_site_id
4162 GROUP BY PH.segment1,
4163 PH.type_lookup_code,
4164 PH.vendor_id,
4165 VDS.vendor_site_code,
4166 PH.currency_code,
4167 PAPF.full_name,
4168 PH.creation_date,
4169 PH.org_id; -- bug5446437
4170
4171 IF (g_fnd_debug = 'Y') THEN
4172 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
4173 FND_LOG.string
4174 ( log_level => FND_LOG.LEVEL_STATEMENT,
4175 module => l_module || l_progress,
4176 message => 'Rows inserted for gbpa:' || SQL%ROWCOUNT
4177 );
4178 END IF;
4179 END IF;
4180
4181 l_progress := '050';
4182
4183 -- SQL What: Insert data into history tables for records in the
4184 -- purge list that are contracts
4185 -- SQL Why: Need to record data in history table before actual
4186 -- purge happens
4187
4188 INSERT INTO po_history_pos
4189 ( segment1,
4190 type_lookup_code,
4191 vendor_id,
4192 vendor_site_code,
4193 po_total,
4194 currency_code,
4195 agent_name,
4196 creation_date,
4197 purge_name,
4198 org_id -- bug5446437
4199 )
4200 SELECT PH.segment1,
4201 PH.type_lookup_code,
4202 PH.vendor_id,
4203 VDS.vendor_site_code,
4204 NVL (ROUND (
4205 SUM (
4206 DECODE (
4207 PLL.quantity,
4208 NULL,
4209 PLL.amount - NVL(PLL.amount_cancelled, 0),
4210 (PLL.quantity - NVL(PLL.quantity_cancelled,
4211 0)) *
4212 PLL.price_override)),
4213 2),
4214 0),
4215 PH.currency_code,
4216 PAPF.full_name,
4217 PH.creation_date,
4218 p_purge_name,
4219 PH.org_id -- bug5446437
4220 FROM per_all_people_f PAPF,
4221 po_vendor_sites VDS,
4222 po_headers PH,
4223 po_lines_all POL,
4224 po_line_locations_all PLL,
4225 po_purge_po_list PPL
4226 WHERE PPL.po_header_id BETWEEN l_range_low AND l_range_high
4227 AND PPL.double_check_flag = 'Y'
4228 AND PPL.po_header_id = PH.po_header_id
4229 AND PH.type_lookup_code = 'CONTRACT'
4230 AND PH.po_header_id = POL.contract_id (+)
4231 AND POL.po_line_id = PLL.po_line_id (+)
4232 AND PH.agent_id = PAPF.person_id
4233 AND TRUNC(SYSDATE) BETWEEN PAPF.effective_start_date
4234 AND PAPF.effective_end_date
4235 AND PH.vendor_site_id = VDS.vendor_site_id
4236 GROUP BY PH.segment1,
4237 PH.type_lookup_code,
4238 PH.vendor_id,
4239 VDS.vendor_site_code,
4240 PH.currency_code,
4241 PAPF.full_name,
4242 PH.creation_date,
4243 PH.org_id; -- bug5446437
4244
4245 IF (g_fnd_debug = 'Y') THEN
4246 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
4247 FND_LOG.string
4248 ( log_level => FND_LOG.LEVEL_STATEMENT,
4249 module => l_module || l_progress,
4250 message => 'Rows inserted for contracts:' || SQL%ROWCOUNT
4251 );
4252 END IF;
4253 END IF;
4254
4255 l_progress := '060';
4256
4257 -- SQL What: For each release that is going to be purged, insert
4258 -- its related information into purge history table
4259 -- SQl Why: Need to record data in history table before actual
4260 -- purge happens
4261
4262 INSERT INTO po_history_pos
4263 ( segment1,
4264 type_lookup_code,
4265 vendor_id,
4266 vendor_site_code,
4267 release_num,
4268 po_total,
4269 currency_code,
4270 agent_name,
4271 creation_date,
4272 purge_name,
4273 org_id -- bug5446437
4274 )
4275 SELECT PH.segment1,
4276 PH.type_lookup_code,
4277 PH.vendor_id,
4278 VDS.vendor_site_code,
4279 PREL.release_num,
4280 NVL (ROUND (
4281 SUM (
4282 DECODE (
4283 PLL.quantity,
4284 NULL,
4285 PLL.amount - NVL(PLL.amount_cancelled, 0),
4286 (PLL.quantity - NVL(PLL.quantity_cancelled,
4287 0)) *
4288 PLL.price_override)),
4289 2),
4290 0),
4291 PH.currency_code,
4292 PAPF.full_name,
4293 PH.creation_date,
4294 p_purge_name,
4295 PH.org_id -- bug5446437
4296 FROM po_vendor_sites VDS,
4297 per_all_people_f PAPF,
4298 po_releases PREL,
4299 po_headers PH,
4300 po_line_locations PLL,
4301 po_purge_po_list PPL
4302 WHERE PPL.po_header_id = PH.po_header_id
4303 AND PH.po_header_id = PREL.po_header_id
4304 AND PREL.po_release_id = PLL.po_release_id
4305 AND PLL.shipment_type IN ('SCHEDULED', 'BLANKET')
4306 AND PH.vendor_site_id = VDS.vendor_site_id
4307 AND PH.agent_id = PAPF.person_id
4308 AND TRUNC(SYSDATE) BETWEEN PAPF.effective_start_date
4309 AND PAPF.effective_end_date
4310 AND PPL.double_check_flag = 'Y'
4311 AND PPL.po_header_id BETWEEN l_range_low
4312 AND l_range_high
4313 GROUP BY PH.segment1,
4314 PH.type_lookup_code,
4315 PH.vendor_id,
4316 VDS.vendor_site_code,
4317 PREL.release_num,
4318 PH.currency_code,
4319 PAPF.full_name,
4320 PH.creation_date,
4321 PH.org_id; -- bug5446437
4322
4323 IF (g_fnd_debug = 'Y') THEN
4324 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
4325 FND_LOG.string
4326 ( log_level => FND_LOG.LEVEL_STATEMENT,
4327 module => l_module || l_progress,
4328 message => 'Rows inserted for releases: ' || SQL%ROWCOUNT
4329 );
4330 END IF;
4331 END IF;
4332
4333 COMMIT;
4334 END IF; -- l_range_inserted = 'N'
4335
4336 l_range_low := l_range_high + 1;
4337 l_range_high := l_range_low + p_range_size;
4338
4339 IF (l_range_low > p_po_upper_limit) THEN
4340 l_progress := '070';
4341
4342 EXIT;
4343 END IF;
4344
4345 END LOOP;
4346
4347 IF (g_fnd_debug = 'Y') THEN
4348 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
4349 FND_LOG.string
4350 ( log_level => FND_LOG.LEVEL_PROCEDURE,
4351 module => l_module || l_progress,
4352 message => 'Quitting ' || g_pkg_name || '.' || l_api_name
4353 );
4354 END IF;
4355 END IF;
4356
4357 EXCEPTION
4358 WHEN OTHERS THEN
4359 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4360 FND_MSG_PUB.add_exc_msg
4361 ( p_pkg_name => g_pkg_name,
4362 p_procedure_name => l_api_name || '.' || l_progress
4363 );
4364
4365 END summarize_pos;
4366
4367
4368 -----------------------------------------------------------------------
4369 --Start of Comments
4370 --Name: delete_reqs
4371 --Pre-reqs:
4372 --Modifies: Multiple REQ transaction tables
4373 --Locks:
4374 -- None
4375 --Function:
4376 -- Purge POs that are remaining in the purge list
4377 --Parameters:
4378 --IN:
4379 --p_range_size
4380 -- The id range size of the documents being purged per commit cycle
4381 --p_req_lower_limit
4382 -- min id among all reqs to be purged
4383 --p_req_upper_limit
4384 -- max id among all reqs to be purged
4385 --IN OUT:
4386 --OUT:
4387 --x_return_status
4388 -- status of the procedure
4389 --Returns:
4390 --Notes:
4391 --Testing:
4392 --End of Comments
4393 ------------------------------------------------------------------------
4394
4395 PROCEDURE delete_reqs
4396 ( x_return_status OUT NOCOPY VARCHAR2,
4397 p_range_size IN NUMBER,
4398 p_req_lower_limit IN NUMBER,
4399 p_req_upper_limit IN NUMBER
4400 ) IS
4401
4402 TYPE num_tbltyp IS TABLE OF NUMBER;
4403 l_ids_tbl num_tbltyp;
4404
4405
4406 l_api_name VARCHAR2(50) := 'delete_reqs';
4407 l_progress VARCHAR2(3);
4408 l_return_status VARCHAR2(1);
4409 l_module CONSTANT FND_LOG_MESSAGES.module%TYPE :=
4410 G_MODULE_PREFIX || l_api_name || '.';
4411
4412 l_range_low NUMBER;
4413 l_range_high NUMBER;
4414
4415 BEGIN
4416
4417 l_progress := '000';
4418
4419 IF (g_fnd_debug = 'Y') THEN
4420 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
4421 FND_LOG.string
4422 ( log_level => FND_LOG.LEVEL_PROCEDURE,
4423 module => l_module || l_progress,
4424 message => 'Entering ' || g_pkg_name || '.' || l_api_name
4425 );
4426 END IF;
4427 END IF;
4428
4429 x_return_status := FND_API.G_RET_STS_SUCCESS;
4430
4431 IF (p_req_lower_limit = -1) THEN
4432 RETURN;
4433 END IF;
4434
4435 --SQL What: This cursor will divide po_purge_req_list into groups of
4436 -- records with size p_range_size. Each fetch will return the
4437 -- highest req id of that group
4438 --SQL Why: We want to delete data in smaller groups to avoid running
4439 -- out of rollback segments
4440
4441 SELECT PPRL2.req_header_id
4442 BULK COLLECT INTO l_ids_tbl
4443 FROM (SELECT PPRL.requisition_header_id req_header_id,
4444 MOD(ROWNUM, p_range_size) mod_result
4445 FROM po_purge_req_list PPRL
4446 WHERE PPRL.double_check_flag = 'Y'
4447 ORDER BY PPRL.requisition_header_id) PPRL2
4448 WHERE PPRL2.mod_result = 0;
4449
4450 l_progress := '010';
4451
4452 l_range_low := p_req_lower_limit;
4453
4454 FOR i IN 0..l_ids_tbl.COUNT LOOP
4455
4456 IF i = l_ids_tbl.COUNT THEN
4457 l_range_high := p_req_upper_limit;
4458 ELSE
4459 l_range_high := l_ids_tbl(i+1);
4460 END IF;
4461
4462 IF (g_fnd_debug = 'Y') THEN
4463 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
4464 FND_LOG.string
4465 ( log_level => FND_LOG.LEVEL_STATEMENT,
4466 module => l_module || l_progress,
4467 message => 'range_low = ' || l_range_low ||
4468 ', range_high = ' || l_range_high
4469 );
4470 END IF;
4471 END IF;
4472
4473 l_progress := '015';
4474
4475 -- Before deleting reqs, delate the records that
4476 -- reference these documents.
4477 PO_AP_PURGE_UTIL_PVT.delete_req_related_records
4478 ( x_return_status => l_return_status,
4479 p_range_low => l_range_low,
4480 p_range_high => l_range_high
4481 );
4482
4483 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
4484 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4485 END IF;
4486
4487 l_progress := '020';
4488
4489 --SQL What: Delete req headers with id within the range
4490 DELETE
4491 FROM po_requisition_headers RH
4492 WHERE EXISTS (
4493 SELECT NULL
4494 FROM po_purge_req_list PPRL
4495 WHERE PPRL.requisition_header_id =
4496 RH.requisition_header_id
4497 AND PPRL.double_check_flag = 'Y'
4498 AND PPRL.requisition_header_id BETWEEN l_range_low
4499 AND l_range_high);
4500
4501 IF (g_fnd_debug = 'Y') THEN
4502 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
4503 FND_LOG.string
4504 ( log_level => FND_LOG.LEVEL_STATEMENT,
4505 module => l_module || l_progress,
4506 message => 'Deleted ' || SQL%ROWCOUNT || ' rec from RH'
4507 );
4508 END IF;
4509 END IF;
4510
4511 l_progress := '030';
4512
4513 --SQL What: Delete req distributions with header within the range
4514 DELETE
4515 FROM po_req_distributions RD
4516 WHERE EXISTS (
4517 SELECT NULL
4518 FROM po_purge_req_list PPRL,
4519 po_requisition_lines RL
4520 WHERE PPRL.requisition_header_id =
4521 RL.requisition_header_id
4522 AND RL.requisition_line_id =
4523 RD.requisition_line_id
4524 AND PPRL.double_check_flag = 'Y'
4525 AND PPRL.requisition_header_id BETWEEN l_range_low
4526 AND l_range_high);
4527
4528 IF (g_fnd_debug = 'Y') THEN
4529 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
4530 FND_LOG.string
4531 ( log_level => FND_LOG.LEVEL_STATEMENT,
4532 module => l_module || l_progress,
4533 message => 'Deleted ' || SQL%ROWCOUNT || ' rec from RD'
4534 );
4535 END IF;
4536 END IF;
4537
4538 l_progress := '040';
4539
4540 --SQL What: Delete req lines with header within the range
4541 DELETE
4542 FROM po_requisition_lines RL
4543 WHERE EXISTS (
4544 SELECT NULL
4545 FROM po_purge_req_list PPRL
4546 WHERE PPRL.requisition_header_id =
4547 RL.requisition_header_id
4548 AND PPRL.double_check_flag = 'Y'
4549 AND PPRL.requisition_header_id BETWEEN l_range_low
4550 AND l_range_high);
4551
4552 IF (g_fnd_debug = 'Y') THEN
4553 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
4554 FND_LOG.string
4555 ( log_level => FND_LOG.LEVEL_STATEMENT,
4556 module => l_module || l_progress,
4557 message => 'Deleted ' || SQL%ROWCOUNT || ' rec from RL'
4558 );
4559 END IF;
4560 END IF;
4561
4562 l_progress := '050';
4563 --SQL What: Delete req action history with header within the range
4564 DELETE
4565 FROM po_action_history PA
4566 WHERE PA.object_type_code = 'REQUISITION'
4567 AND EXISTS (
4568 SELECT NULL
4569 FROM po_purge_req_list PPRL
4570 WHERE PPRL.requisition_header_id = PA.object_id
4571 AND PPRL.double_check_flag = 'Y'
4572 AND PPRL.requisition_header_id BETWEEN l_range_low
4573 AND l_range_high);
4574
4575 IF (g_fnd_debug = 'Y') THEN
4576 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
4577 FND_LOG.string
4578 ( log_level => FND_LOG.LEVEL_STATEMENT,
4579 module => l_module || l_progress,
4580 message => 'Deleted ' || SQL%ROWCOUNT || ' rec frm act_history'
4581 );
4582 END IF;
4583 END IF;
4584
4585 l_progress := '060';
4586
4587 COMMIT;
4588
4589 l_range_low := l_range_high + 1;
4590
4591 IF (l_range_low > p_req_upper_limit) THEN
4592 EXIT;
4593 END IF;
4594
4595 END LOOP;
4596
4597 IF (g_fnd_debug = 'Y') THEN
4598 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
4599 FND_LOG.string
4600 ( log_level => FND_LOG.LEVEL_PROCEDURE,
4601 module => l_module || l_progress,
4602 message => 'Quitting ' || g_pkg_name || '.' || l_api_name
4603 );
4604 END IF;
4605 END IF;
4606
4607 EXCEPTION
4608 WHEN OTHERS THEN
4609 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4610 FND_MSG_PUB.add_exc_msg
4611 ( p_pkg_name => g_pkg_name,
4612 p_procedure_name => l_api_name || '.' || l_progress
4613 );
4614
4615 END delete_reqs;
4616
4617
4618 -----------------------------------------------------------------------
4619 --Start of Comments
4620 --Name: delete_pos
4621 --Pre-reqs:
4622 --Modifies: Multiple PO transaction tables
4623 --Locks:
4624 -- None
4625 --Function:
4626 -- Purge POs that are remaining in the purge list
4627 --Parameters:
4628 --IN:
4629 --p_range_size
4630 -- The number of documents being purged per commit cycle
4631 --p_po_lower_limit
4632 -- min id among all reqs to be purged
4633 --p_po_upper_limit
4634 -- max id among all reqs to be purged
4635 --IN OUT:
4636 --OUT:
4637 --x_return_status
4638 -- status of the procedure
4639 --Returns:
4640 --Notes:
4641 --Testing:
4642 --End of Comments
4643 ------------------------------------------------------------------------
4644
4645 PROCEDURE delete_pos
4646 ( x_return_status OUT NOCOPY VARCHAR2,
4647 p_range_size IN NUMBER,
4648 p_po_lower_limit IN NUMBER,
4649 p_po_upper_limit IN NUMBER
4650 ) IS
4651
4652 TYPE num_tbltyp IS TABLE OF NUMBER;
4653 l_ids_tbl num_tbltyp;
4654
4655 --<R12 eTax Integration Start>
4656 l_po_header_id_tbl PO_TBL_NUMBER;
4657 l_type_lookup_code_tbl PO_TBL_VARCHAR30; -- bug5446437
4658 l_po_release_id_tbl PO_TBL_NUMBER;
4659 l_msg_count NUMBER;
4660 l_msg_data VARCHAR2(2000);
4661 --<R12 eTax Integration End>
4662
4663 l_api_name VARCHAR2(50) := 'delete_pos';
4664 l_progress VARCHAR2(3);
4665 l_module CONSTANT FND_LOG_MESSAGES.module%TYPE :=
4666 G_MODULE_PREFIX || l_api_name || '.';
4667
4668 -- bug3209532
4669 l_pos_dynamic_call VARCHAR2(2000);
4670
4671 l_range_low NUMBER;
4672 l_range_high NUMBER;
4673 l_return_status VARCHAR2(1);
4674 BEGIN
4675
4676 l_progress := '000';
4677
4678 IF (g_fnd_debug = 'Y') THEN
4679 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
4680 FND_LOG.string
4681 ( log_level => FND_LOG.LEVEL_PROCEDURE,
4682 module => l_module || l_progress,
4683 message => 'Entering ' || g_pkg_name || '.' || l_api_name
4684 );
4685 END IF;
4686 END IF;
4687
4688 x_return_status := FND_API.G_RET_STS_SUCCESS;
4689
4690 IF (p_po_lower_limit = -1) THEN
4691 RETURN;
4692 END IF;
4693
4694 --SQL What: This cursor will divide po_purge_po_list into groups of
4695 -- records with size p_range_size. Each fetch will return the
4696 -- highest req id of that group
4697 --SQL Why: We want to delete data in smaller groups to avoid running
4698 -- out of rollback segments
4699
4700 SELECT PPL2.po_header_id
4701 BULK COLLECT INTO l_ids_tbl
4702 FROM (SELECT PPL.po_header_id po_header_id,
4703 MOD(ROWNUM, p_range_size) mod_result
4704 FROM po_purge_po_list PPL
4705 WHERE PPL.double_check_flag = 'Y'
4706 ORDER BY PPL.po_header_id) PPL2
4707 WHERE PPL2.mod_result = 0;
4708
4709 l_progress := '010';
4710
4711 l_range_low := p_po_lower_limit;
4712
4713 FOR i IN 0..l_ids_tbl.COUNT LOOP
4714
4715 IF i = l_ids_tbl.COUNT THEN
4716 l_range_high := p_po_upper_limit;
4717 ELSE
4718 l_range_high := l_ids_tbl(i+1);
4719 END IF;
4720
4721 l_progress := '015';
4722
4723 IF (g_fnd_debug = 'Y') THEN
4724 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
4725 FND_LOG.string
4726 ( log_level => FND_LOG.LEVEL_STATEMENT,
4727 module => l_module || l_progress,
4728 message => 'range_low = ' || l_range_low ||
4729 ', range_high = ' || l_range_high
4730 );
4731 END IF;
4732 END IF;
4733
4734 -- Before deleting the po documents, delate the records that
4735 -- reference these documents.
4736 PO_AP_PURGE_UTIL_PVT.delete_po_related_records
4737 ( x_return_status => l_return_status,
4738 p_range_low => l_range_low,
4739 p_range_high => l_range_high
4740 );
4741
4742 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
4743 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4744 END IF;
4745
4746 l_progress := '020';
4747
4748 --SQL What: Delete po headers within the range
4749 DELETE
4750 FROM po_headers PH
4751 WHERE EXISTS (
4752 SELECT NULL
4753 FROM po_purge_po_list PPL
4754 WHERE PPL.po_header_id = PH.po_header_id
4755 AND PPL.double_check_flag = 'Y'
4756 AND PPL.po_header_id BETWEEN l_range_low
4757 AND l_range_high)
4758 --<R12 eTax Integration Start>
4759 RETURNING po_header_id, type_lookup_code
4760 BULK COLLECT INTO l_po_header_id_tbl, l_type_lookup_code_tbl;
4761 --<R12 eTax Integration End>
4762
4763 IF (g_fnd_debug = 'Y') THEN
4764 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
4765 FND_LOG.string
4766 ( log_level => FND_LOG.LEVEL_STATEMENT,
4767 module => l_module || l_progress,
4768 message => 'Deleted ' || SQL%ROWCOUNT || ' rec from PH'
4769 );
4770 END IF;
4771 END IF;
4772
4773 l_progress := '030';
4774
4775 --SQL What: Delete po headers archive within the range
4776 DELETE
4777 FROM po_headers_archive PHA
4778 WHERE EXISTS (
4779 SELECT NULL
4780 FROM po_purge_po_list PPL
4781 WHERE PPL.po_header_id = PHA.po_header_id
4782 AND PPL.double_check_flag = 'Y'
4783 AND PPL.po_header_id BETWEEN l_range_low
4784 AND l_range_high);
4785
4786 IF (g_fnd_debug = 'Y') THEN
4787 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
4788 FND_LOG.string
4789 ( log_level => FND_LOG.LEVEL_STATEMENT,
4790 module => l_module || l_progress,
4791 message => 'Deleted ' || SQL%ROWCOUNT || ' rec from PHA'
4792 );
4793 END IF;
4794 END IF;
4795
4796 l_progress := '040';
4797
4798 --SQL What: Delete po lines with header within the range
4799 DELETE
4800 FROM po_lines PL
4801 WHERE EXISTS (
4802 SELECT NULL
4803 FROM po_purge_po_list PPL
4804 WHERE PPL.po_header_id = PL.po_header_id
4805 AND PPL.double_check_flag = 'Y'
4806 AND PPL.po_header_id BETWEEN l_range_low
4807 AND l_range_high);
4808
4809 IF (g_fnd_debug = 'Y') THEN
4810 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
4811 FND_LOG.string
4812 ( log_level => FND_LOG.LEVEL_STATEMENT,
4813 module => l_module || l_progress,
4814 message => 'Deleted ' || SQL%ROWCOUNT || ' rec from PL'
4815 );
4816 END IF;
4817 END IF;
4818
4819 --<Unified Catalog R12: Start>
4820 l_progress := '045';
4821
4822 --SQL What: Delete PO Attr with line_ids within the range
4823 DELETE
4824 FROM PO_ATTRIBUTE_VALUES POATR
4825 WHERE EXISTS (
4826 SELECT NULL
4827 FROM po_purge_po_list PPL,
4828 po_lines_all POL
4829 WHERE PPL.po_header_id = POL.po_header_id
4830 AND POATR.po_line_id = POL.po_line_id
4831 AND PPL.double_check_flag = 'Y'
4832 AND PPL.po_header_id BETWEEN l_range_low
4833 AND l_range_high);
4834
4835 IF (g_fnd_debug = 'Y') THEN
4836 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
4837 FND_LOG.string
4838 ( log_level => FND_LOG.LEVEL_STATEMENT,
4839 module => l_module || l_progress,
4840 message => 'Deleted ' || SQL%ROWCOUNT || ' rec from POATR'
4841 );
4842 END IF;
4843 END IF;
4844
4845 l_progress := '048';
4846
4847 --SQL What:Delete po TLP with line_ids within the range
4848 DELETE
4849 FROM PO_ATTRIBUTE_VALUES_TLP POTLP
4850 WHERE EXISTS (
4851 SELECT NULL
4852 FROM po_purge_po_list PPL,
4853 po_lines_all POL
4854 WHERE PPL.po_header_id = POL.po_header_id
4855 AND POTLP.po_line_id = POL.po_line_id
4856 AND PPL.double_check_flag = 'Y'
4857 AND PPL.po_header_id BETWEEN l_range_low
4858 AND l_range_high);
4859
4860 IF (g_fnd_debug = 'Y') THEN
4861 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
4862 FND_LOG.string
4863 ( log_level => FND_LOG.LEVEL_STATEMENT,
4864 module => l_module || l_progress,
4865 message => 'Deleted ' || SQL%ROWCOUNT || ' rec from POTLP'
4866 );
4867 END IF;
4868 END IF;
4869 --<Unified Catalog R12: End>
4870
4871 l_progress := '050';
4872
4873 --SQL What: Delete po lines archive with header within the range
4874 DELETE
4875 FROM po_lines_archive PLA
4876 WHERE EXISTS (
4877 SELECT NULL
4878 FROM po_purge_po_list PPL
4879 WHERE PPL.po_header_id = PLA.po_header_id
4880 AND PPL.double_check_flag = 'Y'
4881 AND PPL.po_header_id BETWEEN l_range_low
4882 AND l_range_high);
4883
4884 IF (g_fnd_debug = 'Y') THEN
4885 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
4886 FND_LOG.string
4887 ( log_level => FND_LOG.LEVEL_STATEMENT,
4888 module => l_module || l_progress,
4889 message => 'Deleted ' || SQL%ROWCOUNT || ' rec from PLA'
4890 );
4891 END IF;
4892 END IF;
4893
4894 --<Unified Catalog R12: Start>
4895 l_progress := '055';
4896
4897 --SQL What: Delete po Attr Archive with line_ids within the range
4898 DELETE
4899 FROM PO_ATTR_VALUES_ARCHIVE POATRA
4900 WHERE EXISTS (
4901 SELECT NULL
4902 FROM po_purge_po_list PPL,
4903 po_lines_all POL
4904 WHERE PPL.po_header_id = POL.po_header_id
4905 AND POATRA.po_line_id = POL.po_line_id
4906 AND PPL.double_check_flag = 'Y'
4907 AND PPL.po_header_id BETWEEN l_range_low
4908 AND l_range_high);
4909
4910 IF (g_fnd_debug = 'Y') THEN
4911 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
4912 FND_LOG.string
4913 ( log_level => FND_LOG.LEVEL_STATEMENT,
4914 module => l_module || l_progress,
4915 message => 'Deleted ' || SQL%ROWCOUNT || ' rec from POATRA'
4916 );
4917 END IF;
4918 END IF;
4919
4920 --SQL What: Delete po TLP Archive with line_ids within the range
4921 l_progress := '058';
4922
4923 DELETE
4924 FROM PO_ATTR_VALUES_TLP_ARCHIVE POTLPA
4925 WHERE EXISTS (
4926 SELECT NULL
4927 FROM po_purge_po_list PPL,
4928 po_lines_all POL
4929 WHERE PPL.po_header_id = POL.po_header_id
4930 AND POTLPA.po_line_id = POL.po_line_id
4931 AND PPL.double_check_flag = 'Y'
4932 AND PPL.po_header_id BETWEEN l_range_low
4933 AND l_range_high);
4934
4935 IF (g_fnd_debug = 'Y') THEN
4936 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
4937 FND_LOG.string
4938 ( log_level => FND_LOG.LEVEL_STATEMENT,
4939 module => l_module || l_progress,
4940 message => 'Deleted ' || SQL%ROWCOUNT || ' rec from POTLPA'
4941 );
4942 END IF;
4943 END IF;
4944 --<Unified Catalog R12: End>
4945
4946 l_progress := '060';
4947
4948 --SQL What: Delete po line_loc with header within the range
4949 DELETE
4950 FROM po_line_locations PLL
4951 WHERE EXISTS (
4952 SELECT NULL
4953 FROM po_purge_po_list PPL
4954 WHERE PPL.po_header_id = PLL.po_header_id
4955 AND PPL.double_check_flag = 'Y'
4956 AND PPL.po_header_id BETWEEN l_range_low
4957 AND l_range_high);
4958
4959 IF (g_fnd_debug = 'Y') THEN
4960 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
4961 FND_LOG.string
4962 ( log_level => FND_LOG.LEVEL_STATEMENT,
4963 module => l_module || l_progress,
4964 message => 'Deleted ' || SQL%ROWCOUNT || ' rec from PLL'
4965 );
4966 END IF;
4967 END IF;
4968
4969 l_progress := '070';
4970
4971 --SQL What: Delete po line_loc archive with header within the range
4972 DELETE
4973 FROM po_line_locations_archive PLLA
4974 WHERE EXISTS (
4975 SELECT NULL
4976 FROM po_purge_po_list PPL
4977 WHERE PPL.po_header_id = PLLA.po_header_id
4978 AND PPL.double_check_flag = 'Y'
4979 AND PPL.po_header_id BETWEEN l_range_low
4980 AND l_range_high);
4981
4982 IF (g_fnd_debug = 'Y') THEN
4983 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
4984 FND_LOG.string
4985 ( log_level => FND_LOG.LEVEL_STATEMENT,
4986 module => l_module || l_progress,
4987 message => 'Deleted ' || SQL%ROWCOUNT || ' rec from PLLA'
4988 );
4989 END IF;
4990 END IF;
4991
4992 l_progress := '080';
4993
4994 --SQL What: Delete po distributions with header within the range
4995 DELETE
4996 FROM po_distributions PD
4997 WHERE EXISTS (
4998 SELECT NULL
4999 FROM po_purge_po_list PPL
5000 WHERE PPL.po_header_id = PD.po_header_id
5001 AND PPL.double_check_flag = 'Y'
5002 AND PPL.po_header_id BETWEEN l_range_low
5003 AND l_range_high);
5004
5005 IF (g_fnd_debug = 'Y') THEN
5006 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
5007 FND_LOG.string
5008 ( log_level => FND_LOG.LEVEL_STATEMENT,
5009 module => l_module || l_progress,
5010 message => 'Deleted ' || SQL%ROWCOUNT || ' rec from PD'
5011 );
5012 END IF;
5013 END IF;
5014
5015 l_progress := '090';
5016
5017 --SQL What: Delete po dist archive with header within the range
5018 DELETE
5019 FROM po_distributions_archive PDA
5020 WHERE EXISTS (
5021 SELECT NULL
5022 FROM po_purge_po_list PPL
5023 WHERE PPL.po_header_id = PDA.po_header_id
5024 AND PPL.double_check_flag = 'Y'
5025 AND PPL.po_header_id BETWEEN l_range_low
5026 AND l_range_high);
5027
5028 IF (g_fnd_debug = 'Y') THEN
5029 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
5030 FND_LOG.string
5031 ( log_level => FND_LOG.LEVEL_STATEMENT,
5032 module => l_module || l_progress,
5033 message => 'Deleted ' || SQL%ROWCOUNT || ' rec from PDA'
5034 );
5035 END IF;
5036 END IF;
5037
5038 l_progress := '100';
5039
5040 --SQL What: Delete po releases with header within the range
5041 DELETE
5042 FROM po_releases PR
5043 WHERE EXISTS (
5044 SELECT NULL
5045 FROM po_purge_po_list PPL
5046 WHERE PPL.po_header_id = PR.po_header_id
5047 AND PPL.double_check_flag = 'Y'
5048 AND PPL.po_header_id BETWEEN l_range_low
5049 AND l_range_high)
5050 --<R12 eTax Integration Start>
5051 RETURNING po_release_id
5052 BULK COLLECT INTO l_po_release_id_tbl;
5053 --<R12 eTax Integration End>
5054
5055
5056 IF (g_fnd_debug = 'Y') THEN
5057 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
5058 FND_LOG.string
5059 ( log_level => FND_LOG.LEVEL_STATEMENT,
5060 module => l_module || l_progress,
5061 message => 'Deleted ' || SQL%ROWCOUNT || ' rec from PR'
5062 );
5063 END IF;
5064 END IF;
5065
5066 l_progress := '110';
5067
5068 --SQL What: Delete po releases archive with header within the range
5069 DELETE
5070 FROM po_releases_archive PRA
5071 WHERE EXISTS (
5072 SELECT NULL
5073 FROM po_purge_po_list PPL
5074 WHERE PPL.po_header_id = PRA.po_header_id
5075 AND PPL.double_check_flag = 'Y'
5076 AND PPL.po_header_id BETWEEN l_range_low
5077 AND l_range_high);
5078
5079 IF (g_fnd_debug = 'Y') THEN
5080 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
5081 FND_LOG.string
5082 ( log_level => FND_LOG.LEVEL_STATEMENT,
5083 module => l_module || l_progress,
5084 message => 'Deleted ' || SQL%ROWCOUNT || ' rec from PRA'
5085 );
5086 END IF;
5087 END IF;
5088
5089 l_progress := '120';
5090
5091 --SQL What: Delete po acceptances with header within the range
5092 DELETE
5093 FROM po_acceptances PA
5094 WHERE EXISTS (
5095 SELECT NULL
5096 FROM po_purge_po_list PPL
5097 WHERE PPL.po_header_id = PA.po_header_id
5098 AND PPL.double_check_flag = 'Y'
5099 AND PPL.po_header_id BETWEEN l_range_low
5100 AND l_range_high);
5101
5102 IF (g_fnd_debug = 'Y') THEN
5103 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
5104 FND_LOG.string
5105 ( log_level => FND_LOG.LEVEL_STATEMENT,
5106 module => l_module || l_progress,
5107 message => 'Deleted ' || SQL%ROWCOUNT || ' rec from po_accept'
5108 );
5109 END IF;
5110 END IF;
5111
5112 l_progress := '130';
5113
5114 --SQL What: Delete po action history with header within the range
5115 DELETE
5116 FROM po_action_history PAH
5117 WHERE PAH.object_type_code = 'PO'
5118 AND EXISTS (
5119 SELECT NULL
5120 FROM po_purge_po_list PPL
5121 WHERE PPL.po_header_id = PAH.object_id
5122 AND PPL.double_check_flag = 'Y'
5123 AND PPL.po_header_id BETWEEN l_range_low
5124 AND l_range_high);
5125
5126 IF (g_fnd_debug = 'Y') THEN
5127 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
5128 FND_LOG.string
5129 ( log_level => FND_LOG.LEVEL_STATEMENT,
5130 module => l_module || l_progress,
5131 message => 'Deleted ' || SQL%ROWCOUNT || ' rec frm act_history'
5132 );
5133 END IF;
5134 END IF;
5135
5136 l_progress := '140';
5137
5138 --SQL What: NULL out src doc reference for reqs if the src doc has been
5139 -- purged
5140 UPDATE po_requisition_lines_all PRL
5141 SET PRL.blanket_po_header_id = NULL,
5142 PRL.blanket_po_line_num = NULL,
5143 PRL.last_update_date = SYSDATE,
5144 PRL.last_updated_by = FND_GLOBAL.user_id,
5145 PRL.last_update_login = FND_GLOBAL.login_id
5146 WHERE PRL.blanket_po_header_id IS NOT NULL
5147 AND PRL.blanket_po_header_id IN (
5148 SELECT po_header_id
5149 FROM po_purge_po_list PPL
5150 WHERE PPL.double_check_flag = 'Y'
5151 AND PPL.po_header_id BETWEEN l_range_low
5152 AND l_range_high);
5153
5154 IF (g_fnd_debug = 'Y') THEN
5155 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
5156 FND_LOG.string
5157 ( log_level => FND_LOG.LEVEL_STATEMENT,
5158 module => l_module || l_progress,
5159 message => 'Null out DocRef on ' || SQL%ROWCOUNT || 'req lines'
5160 );
5161 END IF;
5162 END IF;
5163 --<R12 eTax Integration Start> Purge corresponding tax lines
5164 FORALL i in 1..l_po_header_id_tbl.count
5165 insert into zx_purge_transactions_gt(
5166 application_id,
5167 entity_code,
5168 event_class_code,
5169 trx_id
5170 )
5171 select
5172 fnd_global.resp_appl_id(),
5173 PO_CONSTANTS_SV.PO_ENTITY_CODE,
5174 PO_CONSTANTS_SV.PO_EVENT_CLASS_CODE,
5175 l_po_header_id_tbl(i)
5176 from dual
5177 where l_type_lookup_code_tbl(i) IN ('STANDARD', 'PLANNED');
5178
5179 FORALL i in 1..l_po_release_id_tbl.count
5180 insert into zx_purge_transactions_gt(
5181 application_id,
5182 entity_code,
5183 event_class_code,
5184 trx_id
5185 )
5186 select
5187 fnd_global.resp_appl_id(),
5188 PO_CONSTANTS_SV.REL_ENTITY_CODE,
5189 PO_CONSTANTS_SV.REL_EVENT_CLASS_CODE,
5190 l_po_release_id_tbl(i)
5191 from dual;
5192
5193
5194 -- call ZX_API_PUB.PURGE_TAX_REPOSITORY in PURGE mode
5195 ZX_API_PUB.PURGE_TAX_REPOSITORY(
5196 p_api_version => 1.0,
5197 p_init_msg_list => FND_API.G_FALSE,
5198 p_commit => FND_API.G_FALSE,
5199 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
5200 x_return_status => l_return_status,
5201 x_msg_count => l_msg_count,
5202 x_msg_data => l_msg_data );
5203
5204 --<R12 eTax Integration End> Purge corresponding tax lines
5205
5206 COMMIT;
5207
5208 l_range_low := l_range_high + 1;
5209
5210 IF (l_range_low > p_po_upper_limit) THEN
5211 EXIT;
5212 END IF;
5213
5214 END LOOP;
5215
5216 l_progress := '150';
5217
5218 --<ACTION FOR 11iX START>
5219 --Initiated by: BAO
5220 --The check for Code Level below will be unnecessary in 11iX.
5221 --Plan is to only remove the if-else condition and only call
5222 --POX_SUP_PROF_PRG_GRP.handle_purge instead
5223
5224 -- PO_AP_PURGE_GRP.purge does two things: Delete from org assignments
5225 -- and call a POS purge api.
5226 -- If PO FP level is larger than 11i FPJ, then we should have taken
5227 -- care of deleting org assignments somewhere else. Hence we only
5228 -- need to call POS purge api.
5229
5230 IF (PO_CODE_RELEASE_GRP.Current_Release <
5231 PO_CODE_RELEASE_GRP.PRC_11i_Family_Pack_J) THEN
5232
5233 l_progress := '160';
5234
5235 PO_AP_PURGE_GRP.purge
5236 ( p_api_version => 1.0,
5237 x_return_status => l_return_status
5238 );
5239
5240 ELSE
5241 l_progress := '170';
5242
5243 -- Bug 4459947
5244 -- Do not hardcode schema name.
5245 -- GSCC checker parses POS as schema name even though it's text
5246 -- due to extra _. Modified message from _POS_. to POS API.
5247 IF (g_fnd_debug = 'Y') THEN
5248 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
5249 FND_LOG.string
5250 ( log_level => FND_LOG.LEVEL_STATEMENT,
5251 module => l_module || l_progress,
5252 message => 'PO Code Level >= FPJ. Call POS API handle_purge only'
5253 );
5254 END IF;
5255 END IF;
5256
5257 -- bug3209532
5258 -- We need to use dynamic sql here because POS is not present in 11i
5259 -- In order to avoid compilation error, we need to put in in dynamic
5260 -- sql. If this dynamic sql is erroring out due to non-existence
5261 -- of the package, ignore the error.
5262
5263 IF (g_fnd_debug = 'Y') THEN
5264 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
5265 FND_LOG.string
5266 ( log_level => FND_LOG.LEVEL_STATEMENT,
5267 module => l_module || l_progress,
5268 message => 'Before calling POS Purge API'
5269 );
5270 END IF;
5271 END IF;
5272
5273 -- Call the iSP API to handle events after purge
5274 l_pos_dynamic_call :=
5275 'BEGIN
5276 POS_SUP_PROF_PRG_GRP.handle_purge (:l_return_status);
5277 END;';
5278
5279 BEGIN
5280 EXECUTE IMMEDIATE l_pos_dynamic_call
5281 USING OUT l_return_status;
5282 EXCEPTION
5283 WHEN OTHERS THEN
5284 IF (SQLCODE = -6550) THEN
5285 l_return_status := FND_API.G_RET_STS_SUCCESS;
5286
5287 IF (g_fnd_debug = 'Y') THEN
5288 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
5289 FND_LOG.string
5290 ( log_level => FND_LOG.LEVEL_PROCEDURE,
5291 module => l_module || l_progress,
5292 message => 'Ignore exception from POS call. SQLERRM: '||
5293 SQLERRM
5294 );
5295 END IF;
5296 END IF;
5297 ELSE
5298 RAISE;
5299 END IF;
5300 END;
5301
5302 IF (g_fnd_debug = 'Y') THEN
5303 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
5304 FND_LOG.string
5305 ( log_level => FND_LOG.LEVEL_STATEMENT,
5306 module => l_module || l_progress,
5307 message => 'After calling POS Purge API'
5308 );
5309 END IF;
5310 END IF;
5311
5312 END IF; -- Current Release < FPJ
5313
5314 --<ACTION FOR 11iX END>
5315
5316 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
5317 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5318 END IF;
5319
5320 IF (g_fnd_debug = 'Y') THEN
5321 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
5322 FND_LOG.string
5323 ( log_level => FND_LOG.LEVEL_PROCEDURE,
5324 module => l_module || l_progress,
5325 message => 'Quitting ' || g_pkg_name || '.' || l_api_name
5326 );
5327 END IF;
5328 END IF;
5329
5330 EXCEPTION
5331 WHEN OTHERS THEN
5332 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5333 FND_MSG_PUB.add_exc_msg
5334 ( p_pkg_name => g_pkg_name,
5335 p_procedure_name => l_api_name || '.' || l_progress
5336 );
5337
5338 END delete_pos;
5339
5340 --bug3256316
5341 --Added procedure dump_msg_to_log for more detail debug messages
5342 --when exception happens
5343
5344 -----------------------------------------------------------------------
5345 --Start of Comments
5346 --Name: dump_msg_to_log
5347 --Pre-reqs:
5348 --Modifies:
5349 --Locks:
5350 -- None
5351 --Function:
5352 -- Dump all the messages from message stack to FND_LOG
5353 --Parameters:
5354 --IN:
5355 --p_module
5356 -- Place where this procedure is called
5357 --IN OUT:
5358 --OUT:
5359 --Returns:
5360 --Notes:
5361 --Testing:
5362 --End of Comments
5363 ------------------------------------------------------------------------
5364 PROCEDURE dump_msg_to_log
5365 ( p_module IN VARCHAR2
5366 ) IS
5367 BEGIN
5368
5369 IF (g_fnd_debug = 'Y') THEN
5370 FND_MSG_PUB.reset;
5371
5372 FOR i IN 1..FND_MSG_PUB.count_msg LOOP
5373 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
5374 FND_LOG.string
5375 ( log_level => FND_LOG.LEVEL_STATEMENT,
5376 module => p_module,
5377 message => 'DUMPMSG: ' ||
5378 FND_MSG_PUB.get
5379 ( p_msg_index => i,
5380 p_encoded => 'F')
5381 );
5382 END IF;
5383 END LOOP;
5384 END IF;
5385
5386 END dump_msg_to_log;
5387
5388 -- <DOC PURGE FPJ END>
5389
5390 END PO_AP_PURGE_PVT;