[Home] [Help]
PACKAGE BODY: APPS.PO_AP_PURGE_PVT
Source
1 PACKAGE BODY PO_AP_PURGE_PVT AS
2 /* $Header: POXVPUDB.pls 120.7.12020000.3 2013/02/10 12:22:12 vegajula ship $ */
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 PO_CONSTANTS_SV.APPLICATION_ID,
5173 /* Bug 14004400: Applicaton id being passed to EB Tax was responsibility id rather than 201 which
5174 is pased when the tax lines are created. Same should be passed when they are deleted. */
5175 PO_CONSTANTS_SV.PO_ENTITY_CODE,
5176 PO_CONSTANTS_SV.PO_EVENT_CLASS_CODE,
5177 l_po_header_id_tbl(i)
5178 from dual
5179 where l_type_lookup_code_tbl(i) IN ('STANDARD', 'PLANNED');
5180
5181 FORALL i in 1..l_po_release_id_tbl.count
5182 insert into zx_purge_transactions_gt(
5183 application_id,
5184 entity_code,
5185 event_class_code,
5186 trx_id
5187 )
5188 select
5189 PO_CONSTANTS_SV.APPLICATION_ID,
5190 /* Bug 14004400: Applicaton id being passed to EB Tax was responsibility id rather than 201 which
5191 is pased when the tax lines are created. Same should be passed when they are deleted. */
5192 PO_CONSTANTS_SV.REL_ENTITY_CODE,
5193 PO_CONSTANTS_SV.REL_EVENT_CLASS_CODE,
5194 l_po_release_id_tbl(i)
5195 from dual;
5196
5197
5198 -- call ZX_API_PUB.PURGE_TAX_REPOSITORY in PURGE mode
5199 ZX_API_PUB.PURGE_TAX_REPOSITORY(
5200 p_api_version => 1.0,
5201 p_init_msg_list => FND_API.G_FALSE,
5202 p_commit => FND_API.G_FALSE,
5203 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
5204 x_return_status => l_return_status,
5205 x_msg_count => l_msg_count,
5206 x_msg_data => l_msg_data );
5207
5208 --<R12 eTax Integration End> Purge corresponding tax lines
5209
5210 COMMIT;
5211
5212 l_range_low := l_range_high + 1;
5213
5214 IF (l_range_low > p_po_upper_limit) THEN
5215 EXIT;
5216 END IF;
5217
5218 END LOOP;
5219
5220 l_progress := '150';
5221
5222 --<ACTION FOR 11iX START>
5223 --Initiated by: BAO
5224 --The check for Code Level below will be unnecessary in 11iX.
5225 --Plan is to only remove the if-else condition and only call
5226 --POX_SUP_PROF_PRG_GRP.handle_purge instead
5227
5228 -- PO_AP_PURGE_GRP.purge does two things: Delete from org assignments
5229 -- and call a POS purge api.
5230 -- If PO FP level is larger than 11i FPJ, then we should have taken
5231 -- care of deleting org assignments somewhere else. Hence we only
5232 -- need to call POS purge api.
5233
5234 IF (PO_CODE_RELEASE_GRP.Current_Release <
5235 PO_CODE_RELEASE_GRP.PRC_11i_Family_Pack_J) THEN
5236
5237 l_progress := '160';
5238
5239 PO_AP_PURGE_GRP.purge
5240 ( p_api_version => 1.0,
5241 x_return_status => l_return_status
5242 );
5243
5244 ELSE
5245 l_progress := '170';
5246
5247 -- Bug 4459947
5248 -- Do not hardcode schema name.
5249 -- GSCC checker parses POS as schema name even though it's text
5250 -- due to extra _. Modified message from _POS_. to POS API.
5251 IF (g_fnd_debug = 'Y') THEN
5252 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
5253 FND_LOG.string
5254 ( log_level => FND_LOG.LEVEL_STATEMENT,
5255 module => l_module || l_progress,
5256 message => 'PO Code Level >= FPJ. Call POS API handle_purge only'
5257 );
5258 END IF;
5259 END IF;
5260
5261 -- bug3209532
5262 -- We need to use dynamic sql here because POS is not present in 11i
5263 -- In order to avoid compilation error, we need to put in in dynamic
5264 -- sql. If this dynamic sql is erroring out due to non-existence
5265 -- of the package, ignore the error.
5266
5267 IF (g_fnd_debug = 'Y') THEN
5268 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
5269 FND_LOG.string
5270 ( log_level => FND_LOG.LEVEL_STATEMENT,
5271 module => l_module || l_progress,
5272 message => 'Before calling POS Purge API'
5273 );
5274 END IF;
5275 END IF;
5276
5277 -- Call the iSP API to handle events after purge
5278 l_pos_dynamic_call :=
5279 'BEGIN
5280 POS_SUP_PROF_PRG_GRP.handle_purge (:l_return_status);
5281 END;';
5282
5283 BEGIN
5284 EXECUTE IMMEDIATE l_pos_dynamic_call
5285 USING OUT l_return_status;
5286 EXCEPTION
5287 WHEN OTHERS THEN
5288 IF (SQLCODE = -6550) THEN
5289 l_return_status := FND_API.G_RET_STS_SUCCESS;
5290
5291 IF (g_fnd_debug = 'Y') THEN
5292 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
5293 FND_LOG.string
5294 ( log_level => FND_LOG.LEVEL_PROCEDURE,
5295 module => l_module || l_progress,
5296 message => 'Ignore exception from POS call. SQLERRM: '||
5297 SQLERRM
5298 );
5299 END IF;
5300 END IF;
5301 ELSE
5302 RAISE;
5303 END IF;
5304 END;
5305
5306 IF (g_fnd_debug = 'Y') THEN
5307 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
5308 FND_LOG.string
5309 ( log_level => FND_LOG.LEVEL_STATEMENT,
5310 module => l_module || l_progress,
5311 message => 'After calling POS Purge API'
5312 );
5313 END IF;
5314 END IF;
5315
5316 END IF; -- Current Release < FPJ
5317
5318 --<ACTION FOR 11iX END>
5319
5320 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
5321 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5322 END IF;
5323
5324 IF (g_fnd_debug = 'Y') THEN
5325 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
5326 FND_LOG.string
5327 ( log_level => FND_LOG.LEVEL_PROCEDURE,
5328 module => l_module || l_progress,
5329 message => 'Quitting ' || g_pkg_name || '.' || l_api_name
5330 );
5331 END IF;
5332 END IF;
5333
5334 EXCEPTION
5335 WHEN OTHERS THEN
5336 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5337 FND_MSG_PUB.add_exc_msg
5338 ( p_pkg_name => g_pkg_name,
5339 p_procedure_name => l_api_name || '.' || l_progress
5340 );
5341
5342 END delete_pos;
5343
5344 --bug3256316
5345 --Added procedure dump_msg_to_log for more detail debug messages
5346 --when exception happens
5347
5348 -----------------------------------------------------------------------
5349 --Start of Comments
5350 --Name: dump_msg_to_log
5351 --Pre-reqs:
5352 --Modifies:
5353 --Locks:
5354 -- None
5355 --Function:
5356 -- Dump all the messages from message stack to FND_LOG
5357 --Parameters:
5358 --IN:
5359 --p_module
5360 -- Place where this procedure is called
5361 --IN OUT:
5362 --OUT:
5363 --Returns:
5364 --Notes:
5365 --Testing:
5366 --End of Comments
5367 ------------------------------------------------------------------------
5368 PROCEDURE dump_msg_to_log
5369 ( p_module IN VARCHAR2
5370 ) IS
5371 BEGIN
5372
5373 IF (g_fnd_debug = 'Y') THEN
5374 FND_MSG_PUB.reset;
5375
5376 FOR i IN 1..FND_MSG_PUB.count_msg LOOP
5377 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
5378 FND_LOG.string
5379 ( log_level => FND_LOG.LEVEL_STATEMENT,
5380 module => p_module,
5381 message => 'DUMPMSG: ' ||
5382 FND_MSG_PUB.get
5383 ( p_msg_index => i,
5384 p_encoded => 'F')
5385 );
5386 END IF;
5387 END LOOP;
5388 END IF;
5389
5390 END dump_msg_to_log;
5391
5392 -- <DOC PURGE FPJ END>
5393
5394 END PO_AP_PURGE_PVT;