[Home] [Help]
PACKAGE BODY: APPS.PO_AP_PURGE_UTIL_PVT
Source
1 PACKAGE BODY PO_AP_PURGE_UTIL_PVT AS
2 /* $Header: POXVPUUB.pls 120.3 2006/01/31 09:50:50 dedelgad noship $ */
3
4
5 -- <DOC PURGE FPJ START>
6
7 G_PKG_NAME CONSTANT VARCHAR2(30) := 'PO_AP_PURGE_UTIL_PVT';
8 g_fnd_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
9 G_MODULE_PREFIX CONSTANT VARCHAR2(40) := 'po.plsql.' || g_pkg_name || '.';
10
11 g_batch_limit CONSTANT NUMBER := 10000;
12
13 --*********************************************************************
14 ----------------- Private Procedure Prototypes-------------------------
15 --*********************************************************************
16
17 PROCEDURE filter_req_pon_validation
18 ( x_return_status OUT NOCOPY VARCHAR2
19 );
20
21 PROCEDURE filter_po_fte_validation
22 ( x_return_status OUT NOCOPY VARCHAR2
23 );
24
25 PROCEDURE filter_po_cst_validation
26 ( x_return_status OUT NOCOPY VARCHAR2
27 );
28
29 PROCEDURE filter_po_oe_validation
30 ( x_return_status OUT NOCOPY VARCHAR2
31 );
32
33 PROCEDURE filter_po_pon_validation
34 ( x_return_status OUT NOCOPY VARCHAR2
35 );
36
37 PROCEDURE filter_po_hr_validation
38 ( x_return_status OUT NOCOPY VARCHAR2
39 );
40
41 PROCEDURE delete_asl_ref
42 ( x_return_status OUT NOCOPY VARCHAR2,
43 p_range_low IN NUMBER,
44 p_range_high IN NUMBER
45 );
46
47 PROCEDURE delete_org_assignments
48 ( x_return_status OUT NOCOPY VARCHAR2,
49 p_range_low IN NUMBER,
50 p_range_high IN NUMBER
51 );
52
53 PROCEDURE delete_drop_ship_po_links
54 ( x_return_status OUT NOCOPY VARCHAR2,
55 p_range_low IN NUMBER,
56 p_range_high IN NUMBER
57 );
58
59 PROCEDURE delete_fte
60 ( x_return_status OUT NOCOPY VARCHAR2,
61 p_range_low IN NUMBER,
62 p_range_high IN NUMBER
63 );
64
65 PROCEDURE delete_pon
66 ( x_return_status OUT NOCOPY VARCHAR2,
67 p_purge_entity IN VARCHAR2,
68 p_range_low IN NUMBER,
69 p_range_high IN NUMBER
70 );
71
72 PROCEDURE delete_contract_terms
73 ( x_return_status OUT NOCOPY VARCHAR2,
74 p_range_low IN NUMBER,
75 p_range_high IN NUMBER
76 );
77
78
79 PROCEDURE delete_price_differentials
80 ( x_return_status OUT NOCOPY VARCHAR2,
81 p_purge_entity IN VARCHAR2,
82 p_range_low IN NUMBER,
83 p_range_high IN NUMBER
84 );
85
86 PROCEDURE delete_attr_values
87 ( x_return_status OUT NOCOPY VARCHAR2,
88 p_range_low IN NUMBER,
89 p_range_high IN NUMBER
90 );
91
92 -- bug3231186
93 PROCEDURE delete_po_approval_list
94 ( x_return_status OUT NOCOPY VARCHAR2,
95 p_purge_entity IN VARCHAR2,
96 p_range_low IN NUMBER,
97 p_range_high IN NUMBER
98 );
99
100 PROCEDURE delete_req_attachments
101 ( x_return_status OUT NOCOPY VARCHAR2,
102 p_range_low IN NUMBER,
103 p_range_high IN NUMBER
104 );
105
106 PROCEDURE delete_po_attachments
107 ( x_return_status OUT NOCOPY VARCHAR2,
108 p_range_low IN NUMBER,
109 p_range_high IN NUMBER
110 );
111
112 PROCEDURE delete_po_drafts
113 ( x_return_status OUT NOCOPY VARCHAR2,
114 p_range_low IN NUMBER,
115 p_range_high IN NUMBER
116 );
117
118 --*********************************************************************
119 -------------------------- Public Procedures --------------------------
120 --*********************************************************************
121
122
123 -----------------------------------------------------------------------
124 --Start of Comments
125 --Name: seed_po
126 --Pre-reqs:
127 --Modifies: po_purge_po_list, po_purge_req_list
128 --Locks:
129 -- None
130 --Function: Construct po purge list for eligible pos that have not been
131 -- updated since last_activity_date. This is a version from
132 -- 11i FPJ and beyond. it will also populate req purgelist
133 --Parameters:
134 --IN:
135 --p_purge_category
136 -- Indicate types of records user wants to purge
137 --p_purge_name
138 -- name of the purge process
139 --p_last_activity_date
140 -- Date which determines whether an already eligible PO should be inserted
141 -- to purge list or not. POs that have not been updated since this date
142 -- will not get purged.
143 --IN OUT:
144 --OUT:
145 --x_return_status
146 -- status of the procedure
147 --Returns:
148 --Notes:
149 --Testing:
150 --End of Comments
151 ------------------------------------------------------------------------
152
153 --<ACTION FOR 11iX START>
154 --Initiated by: BAO
155 --Plan is to move the logic in this procedure PO_AP_PURGE_PVT.seed_po to 11iX
156
157 PROCEDURE seed_po
158 ( x_return_status OUT NOCOPY VARCHAR2,
159 p_purge_category IN VARCHAR2,
160 p_purge_name IN VARCHAR2,
161 p_last_activity_date IN DATE
162 ) IS
163
164 l_api_name CONSTANT VARCHAR2(50) := 'seed_po';
165 l_progress VARCHAR2(3);
166 l_module CONSTANT FND_LOG_MESSAGES.module%TYPE :=
167 G_MODULE_PREFIX || l_api_name || '.';
168
169 BEGIN
170
171 l_progress := '000';
172
173 IF (g_fnd_debug = 'Y') THEN
174 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
175 FND_LOG.string
176 ( log_level => FND_LOG.LEVEL_PROCEDURE,
177 module => l_module || l_progress,
178 message => 'Entering ' || g_pkg_name || '.' || l_api_name
179 );
180 END IF;
181 END IF;
182
183 x_return_status := FND_API.G_RET_STS_SUCCESS;
184
185 IF (p_purge_category = PO_AP_PURGE_GRP.G_PUR_CAT_SIMPLE_PO) THEN
186
187 l_progress := '010';
188
189 -- The subquery on po_distributions is to make sure that no invoice
190 -- has matched to this PO yet
191
192 --SQL What: Generate a list of POs that are eligibible for purge
193 --SQL Why: This is the initial list of pos to be purged. There will
194 -- be additional rules getting applied to this table to
195 -- remove records that are actually not eligible for purge
196
197 INSERT INTO po_purge_po_list
198 ( po_header_id,
199 purge_name,
200 double_check_flag
201 )
202 SELECT PH.po_header_id,
203 p_purge_name,
204 'Y'
205 FROM po_headers PH
206 WHERE PH.type_lookup_code IN ('STANDARD', 'PLANNED',
207 'BLANKET', 'CONTRACT')
208 AND PH.last_update_date <= p_last_activity_date
209 AND (PH.closed_code = 'FINALLY CLOSED'
210 OR PH.cancel_flag = 'Y')
211 AND NOT EXISTS
212 (SELECT NULL
213 FROM po_releases PR
214 WHERE PR.po_header_id = PH.po_header_id
215 AND PR.last_update_date > p_last_activity_date)
216 AND NOT EXISTS
217 (SELECT NULL
218 FROM po_lines PL
219 WHERE PL.po_header_id = PH.po_header_id
220 AND (
221 PL.last_update_date > p_last_activity_date
222 OR
223 EXISTS (
224 SELECT NULL
225 FROM po_price_differentials PPD
226 WHERE PPD.entity_type IN ('PO LINE',
227 'BLANKET LINE')
228 AND PPD.entity_id = PL.po_line_id
229 AND PPD.last_update_date >
230 p_last_activity_date)))
231 AND NOT EXISTS
232 (SELECT NULL
233 FROM po_line_locations PLL
234 WHERE PLL.po_header_id = PH.po_header_id
235 AND (
236 PLL.last_update_date > p_last_activity_date
237 OR
238 EXISTS (
239 SELECT NULL
240 FROM po_price_differentials PPD
241 WHERE PPD.entity_type = 'PRICE BREAK'
242 AND PPD.entity_id = PLL.line_location_id
243 AND PPD.last_update_date >
244 p_last_activity_date)))
245 AND NOT EXISTS
246 (SELECT NULL
247 FROM po_distributions PD
248 WHERE PD.po_header_id = PH.po_header_id
249 AND (PD.last_update_date > p_last_activity_date
250 OR
251 EXISTS
252 (SELECT NULL
253 FROM ap_invoice_distributions AD
254 WHERE AD.po_distribution_id =
255 PD.po_distribution_id)))
256 AND NOT EXISTS
257 (SELECT NULL
258 FROM rcv_transactions RT
259 WHERE RT.po_header_id = PH.po_header_id
260 AND RT.last_update_date > p_last_activity_date);
261
262 IF (g_fnd_debug = 'Y') THEN
263 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
264 FND_LOG.string
265 ( log_level => FND_LOG.LEVEL_STATEMENT,
266 module => l_module || l_progress,
267 message => 'Done Seeded PO for ' || p_purge_category
268 );
269 END IF;
270 END IF;
271
272 ELSIF (p_purge_category = PO_AP_PURGE_GRP.G_PUR_CAT_MATCHED_PO_INV) THEN
273
274 l_progress := '020';
275
276 -- POs that have invoices are still candidates for purging when
277 -- purge category = 'MATCHED POS AND INVOICES'
278
279 --SQL What: Generate a list of POs that are eligibible for purge
280 --SQL Why: This is the initial list of pos to be purged. There will
281 -- be additional rules getting applied to this table to
282 -- remove records that are actually not eligible for purge
283
284 INSERT INTO po_purge_po_list
285 ( po_header_id,
286 purge_name,
287 double_check_flag
288 )
289 SELECT PH.po_header_id,
290 p_purge_name,
291 'Y'
292 FROM po_headers PH
293 WHERE PH.type_lookup_code IN ('STANDARD', 'PLANNED',
294 'BLANKET', 'CONTRACT')
295 AND PH.last_update_date <= p_last_activity_date
296 AND (PH.closed_code = 'FINALLY CLOSED'
297 OR PH.cancel_flag = 'Y')
298 AND NOT EXISTS
299 (SELECT NULL
300 FROM po_releases PR
301 WHERE PR.po_header_id = PH.po_header_id
302 AND PR.last_update_date > p_last_activity_date)
303 AND NOT EXISTS
304 (SELECT NULL
305 FROM po_lines PL
306 WHERE PL.po_header_id = PH.po_header_id
307 AND (
308 PL.last_update_date > p_last_activity_date
309 OR
310 EXISTS (
311 SELECT NULL
312 FROM po_price_differentials PPD
313 WHERE PPD.entity_type IN ('PO LINE',
314 'BLANKET LINE')
315 AND PPD.entity_id = PL.po_line_id
316 AND PPD.last_update_date >
317 p_last_activity_date)))
318 AND NOT EXISTS
319 (SELECT NULL
320 FROM po_line_locations PLL
321 WHERE PLL.po_header_id = PH.po_header_id
322 AND (
323 PLL.last_update_date > p_last_activity_date
324 OR
325 EXISTS (
326 SELECT NULL
327 FROM po_price_differentials PPD
328 WHERE PPD.entity_type = 'PRICE BREAK'
329 AND PPD.entity_id = PLL.line_location_id
330 AND PPD.last_update_date >
331 p_last_activity_date)))
332 AND NOT EXISTS
333 (SELECT NULL
334 FROM po_distributions PD
335 WHERE PD.po_header_id = PH.po_header_id
336 AND PD.last_update_date > p_last_activity_date)
337 AND NOT EXISTS
338 (SELECT NULL
339 FROM rcv_transactions RT
340 WHERE RT.po_header_id = PH.po_header_id
341 AND RT.last_update_date > p_last_activity_date);
342
343 IF (g_fnd_debug = 'Y') THEN
344 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
345 FND_LOG.string
346 ( log_level => FND_LOG.LEVEL_STATEMENT,
347 module => l_module || l_progress,
348 message => 'Done seed po for ' || p_purge_category
349 );
350 END IF;
351 END IF;
352
353 END IF; -- p_purge_category = ...
354
355 l_progress := '030';
356
357 -- We will put the req in the purge list even if it has turned into
358 -- a PO.
359
360 -- SQL What: Generate a list of requisitions that are eligible for purging
361 -- This query is specific for customers at FP level that is
362 -- 11i FPJ or above
363 -- SQL Why: This is the initial list of reqs to be purged. Later on the
364 -- records in this list will be removed if the records are no
365 -- longer eligible for purging after additional rules are applied
366
367 INSERT INTO po_purge_req_list
368 ( requisition_header_id,
369 purge_name,
370 double_check_flag
371 )
372 SELECT PRH.requisition_header_id,
373 p_purge_name,
374 'Y'
375 FROM po_requisition_headers PRH
376 WHERE PRH.last_update_date <= p_last_activity_date
377 AND (PRH.closed_code = 'FINALLY CLOSED'
378 OR PRH.authorization_status = 'CANCELLED')
379 AND NOT EXISTS
380 (SELECT NULL
381 FROM po_requisition_lines PRL
382 WHERE PRL.requisition_header_id = PRH.requisition_header_id
383 AND NVL(PRL.modified_by_agent_flag, 'N') = 'N'
384 AND (PRL.last_update_date > p_last_activity_date
385 OR
386 PRL.source_type_code = 'INVENTORY'
387 OR
388 EXISTS (
389 SELECT NULL
390 FROM po_price_differentials PPD
391 WHERE PPD.entity_type = 'REQ LINE'
392 AND PPD.entity_id = PRL.requisition_line_id
393 AND PPD.last_update_date >
394 p_last_activity_date)
395 OR
396 EXISTS (
397 SELECT NULL
398 FROM po_req_distributions PRD
399 WHERE PRD.requisition_line_id =
400 PRL.requisition_line_id
401 AND PRD.last_update_date >
402 p_last_activity_date)));
403
404
405 l_progress := '040';
406
407 IF (g_fnd_debug = 'Y') THEN
408 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
409 FND_LOG.string
410 ( log_level => FND_LOG.LEVEL_PROCEDURE,
411 module => l_module || l_progress,
412 message => 'Quitting ' || g_pkg_name || '.' || l_api_name
413 );
414 END IF;
415 END IF;
416
417 EXCEPTION
418 WHEN OTHERS THEN
419 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
420
421 FND_MSG_PUB.add_exc_msg
422 ( p_pkg_name => g_pkg_name,
423 p_procedure_name => l_api_name || '.' || l_progress
424 );
425
426 END seed_po;
427
428 --<ACTION FOR 11iX END>
429
430 -----------------------------------------------------------------------
431 --Start of Comments
432 --Name: filter_more_referenced_req
433 --Pre-reqs:
434 --Modifies: po_purge_req_list
435 --Locks:
436 -- None
437 --Function: Exclude REQs that are referenced by records that will not get
438 -- purged.
439 --Parameters:
440 --IN:
441 --IN OUT:
442 --OUT:
443 --x_return_status
444 -- status of the procedure
445 --Returns:
446 --Notes:
447 --Testing:
448 --End of Comments
449 ------------------------------------------------------------------------
450
451 PROCEDURE filter_more_referenced_req
452 ( x_return_status OUT NOCOPY VARCHAR2
453 ) IS
454
455 l_api_name VARCHAR2(50) := 'filter_more_referenced_po';
456 l_progress VARCHAR2(3);
457 l_return_status VARCHAR2(1);
458 l_module CONSTANT FND_LOG_MESSAGES.module%TYPE :=
459 G_MODULE_PREFIX || l_api_name || '.';
460
461 BEGIN
462
463 l_progress := '000';
464
465 IF (g_fnd_debug = 'Y') THEN
466 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
467 FND_LOG.string
468 ( log_level => FND_LOG.LEVEL_PROCEDURE,
469 module => l_module || l_progress,
470 message => 'Entering ' || g_pkg_name || '.' || l_api_name
471 );
472 END IF;
473 END IF;
474
475 x_return_status := FND_API.G_RET_STS_SUCCESS;
476
477 l_progress := '010';
478
479 filter_req_pon_validation
480 ( x_return_status => l_return_status
481 );
482
483 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
484 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
485 END IF;
486
487 IF (g_fnd_debug = 'Y') THEN
488 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
489 FND_LOG.string
490 ( log_level => FND_LOG.LEVEL_PROCEDURE,
491 module => l_module || l_progress,
492 message => 'Quitting ' || g_pkg_name || '.' || l_api_name
493 );
494 END IF;
495 END IF;
496
497 EXCEPTION
498 WHEN OTHERS THEN
499 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
500 FND_MSG_PUB.add_exc_msg
501 ( p_pkg_name => g_pkg_name,
502 p_procedure_name => l_api_name || '.' || l_progress
503 );
504
505 END filter_more_referenced_req;
506
507
508 -----------------------------------------------------------------------
509 --Start of Comments
510 --Name: filter_more_referenced_po
511 --Pre-reqs:
512 --Modifies: po_purge_po_list
513 --Locks:
514 -- None
515 --Function: Exclude POs that are referenced by records that will not get
516 -- purged.
517 --Parameters:
518 --IN:
519 --IN OUT:
520 --OUT:
521 --x_return_status
522 -- status of the procedure
523 --Returns:
524 --Notes:
525 --Testing:
526 --End of Comments
527 ------------------------------------------------------------------------
528
529 PROCEDURE filter_more_referenced_po
530 ( x_return_status OUT NOCOPY VARCHAR2
531 ) IS
532
533 l_api_name VARCHAR2(50) := 'filter_more_referenced_po';
534 l_progress VARCHAR2(3);
535 l_return_status VARCHAR2(1);
536 l_module CONSTANT FND_LOG_MESSAGES.module%TYPE :=
537 G_MODULE_PREFIX || l_api_name || '.';
538
539 BEGIN
540
541 l_progress := '000';
542
543 IF (g_fnd_debug = 'Y') THEN
544 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
545 FND_LOG.string
546 ( log_level => FND_LOG.LEVEL_PROCEDURE,
547 module => l_module || l_progress,
548 message => 'Entering ' || g_pkg_name || '.' || l_api_name
549 );
550 END IF;
551 END IF;
552
553 x_return_status := FND_API.G_RET_STS_SUCCESS;
554
555 filter_po_fte_validation
556 ( x_return_status => l_return_status
557 );
558
559 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
560 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
561 END IF;
562
563 l_progress := '010';
564
565 filter_po_cst_validation
566 ( x_return_status => l_return_status
567 );
568
569 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
570 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
571 END IF;
572
573 l_progress := '020';
574
575 filter_po_oe_validation
576 ( x_return_status => l_return_status
577 );
578
579 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
580 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
581 END IF;
582
583 l_progress := '030';
584
585 filter_po_pon_validation
586 ( x_return_status => l_return_status
587 );
588
589 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
590 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
591 END IF;
592
593 -- bug3365311
594 -- removed filter_po_gms_validation as the check is unnecessary
595
596 l_progress := '050';
597
598 filter_po_hr_validation
599 ( x_return_status => l_return_status
600 );
601
602 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
603 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
604 END IF;
605
606 IF (g_fnd_debug = 'Y') THEN
607 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
608 FND_LOG.string
609 ( log_level => FND_LOG.LEVEL_PROCEDURE,
610 module => l_module || l_progress,
611 message => 'Quitting ' || g_pkg_name || '.' || l_api_name
612 );
613 END IF;
614 END IF;
615
616 EXCEPTION
617 WHEN OTHERS THEN
618 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
619 FND_MSG_PUB.add_exc_msg
620 ( p_pkg_name => g_pkg_name,
621 p_procedure_name => l_api_name || '.' || l_progress
622 );
623
624 END filter_more_referenced_po;
625
626
627 -----------------------------------------------------------------------
628 --Start of Comments
629 --Name: delete_req_related_records
630 --Pre-reqs:
631 --Modifies:
632 --Locks:
633 -- None
634 --Function: Call various procedures to delete additional records when
635 -- a requisition is purged
636 --Parameters:
637 --IN:
638 --p_range_low
639 -- lower bound of the req to be purged
640 --p_range_high
641 -- upper bound of the req to be purged
642 --IN OUT:
643 --OUT:
644 --x_return_status
645 -- status of the procedure
646 --Returns:
647 --Notes:
648 --Testing:
649 --End of Comments
650 ------------------------------------------------------------------------
651
652 PROCEDURE delete_req_related_records
653 ( x_return_status OUT NOCOPY VARCHAR2,
654 p_range_low IN NUMBER,
655 p_range_high IN NUMBER
656 ) IS
657
658 l_api_name VARCHAR2(50) := 'delete_req_related_records';
659 l_progress VARCHAR2(3);
660 l_return_status VARCHAR2(1);
661 l_module CONSTANT FND_LOG_MESSAGES.module%TYPE :=
662 G_MODULE_PREFIX || l_api_name || '.';
663
664 BEGIN
665
666 l_progress := '000';
667
668 IF (g_fnd_debug = 'Y') THEN
669 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
670 FND_LOG.string
671 ( log_level => FND_LOG.LEVEL_PROCEDURE,
672 module => l_module || l_progress,
673 message => 'Entering ' || g_pkg_name || '.' || l_api_name
674 );
675 END IF;
676 END IF;
677
678 x_return_status := FND_API.G_RET_STS_SUCCESS;
679
680 l_progress := '010';
681
682 delete_price_differentials
683 ( x_return_status => l_return_status,
684 p_purge_entity => 'REQ',
685 p_range_low => p_range_low,
686 p_range_high => p_range_high
687 );
688
689 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
690 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
691 END IF;
692
693 l_progress := '020';
694
695 delete_pon
696 ( x_return_status => l_return_status,
697 p_purge_entity => 'REQ',
698 p_range_low => p_range_low,
699 p_range_high => p_range_high
700 );
701
702 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
703 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
704 END IF;
705
706 l_progress := '030';
707
708 --bug3231186
709 delete_po_approval_list
710 ( x_return_status => l_return_status,
711 p_purge_entity => 'REQ',
712 p_range_low => p_range_low,
713 p_range_high => p_range_high
714 );
715
716 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
717 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
718 END IF;
719
720 l_progress := '040';
721
722 delete_req_attachments
723 ( x_return_status => l_return_status,
724 p_range_low => p_range_low,
725 p_range_high => p_range_high
726 );
727
728 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
729 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
730 END IF;
731
732 IF (g_fnd_debug = 'Y') THEN
733 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
734 FND_LOG.string
735 ( log_level => FND_LOG.LEVEL_PROCEDURE,
736 module => l_module || l_progress,
737 message => 'Quitting ' || g_pkg_name || '.' || l_api_name
738 );
739 END IF;
740 END IF;
741
742 EXCEPTION
743 WHEN OTHERS THEN
744 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
745 FND_MSG_PUB.add_exc_msg
746 ( p_pkg_name => g_pkg_name,
747 p_procedure_name => l_api_name || '.' || l_progress
748 );
749
750 END delete_req_related_records;
751
752
753 -----------------------------------------------------------------------
754 --Start of Comments
755 --Name: delete_po_related_records
756 --Pre-reqs:
757 --Modifies:
758 --Locks:
759 -- None
760 --Function: Call various procedures to delete additional records when
761 -- a po is purged
762 --Parameters:
763 --IN:
764 --p_range_low
765 -- lower bound of the po to be purged
766 --p_range_high
767 -- upper bound of the po to be purged
768 --IN OUT:
769 --OUT:
770 --x_return_status
771 -- status of the procedure
772 --Returns:
773 --Notes:
774 --Testing:
775 --End of Comments
776 ------------------------------------------------------------------------
777
778 PROCEDURE delete_po_related_records
779 ( x_return_status OUT NOCOPY VARCHAR2,
780 p_range_low IN NUMBER,
781 p_range_high IN NUMBER
782 ) IS
783
784 l_api_name VARCHAR2(50) := 'delete_po_related_records';
785 l_progress VARCHAR2(3);
786 l_return_status VARCHAR2(1);
787 l_module CONSTANT FND_LOG_MESSAGES.module%TYPE :=
788 G_MODULE_PREFIX || l_api_name || '.';
789
790 BEGIN
791
792 l_progress := '000';
793
794 IF (g_fnd_debug = 'Y') THEN
795 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
796 FND_LOG.string
797 ( log_level => FND_LOG.LEVEL_PROCEDURE,
798 module => l_module || l_progress,
799 message => 'Entering ' || g_pkg_name || '.' || l_api_name
800 );
801 END IF;
802 END IF;
803
804 x_return_status := FND_API.G_RET_STS_SUCCESS;
805
806 l_progress := '010';
807
808 delete_asl_ref
809 ( x_return_status => l_return_status,
810 p_range_low => p_range_low,
811 p_range_high => p_range_high
812 );
813
814 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
815 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
816 END IF;
817
818 l_progress := '020';
819
820 delete_org_assignments
821 ( x_return_status => l_return_status,
822 p_range_low => p_range_low,
823 p_range_high => p_range_high
824 );
825
826 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
827 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
828 END IF;
829
830 l_progress := '030';
831
832 delete_drop_ship_po_links
833 ( x_return_status => l_return_status,
834 p_range_low => p_range_low,
835 p_range_high => p_range_high
836 );
837
838 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
839 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
840 END IF;
841
842 l_progress := '040';
843
844 delete_fte
845 ( x_return_status => l_return_status,
846 p_range_low => p_range_low,
847 p_range_high => p_range_high
848 );
849
850 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
851 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
852 END IF;
853
854 l_progress := '050';
855
856 delete_pon
857 ( x_return_status => l_return_status,
858 p_purge_entity => 'PO',
859 p_range_low => p_range_low,
860 p_range_high => p_range_high
861 );
862
863 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
864 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
865 END IF;
866
867 l_progress := '060';
868
869 delete_contract_terms
870 ( x_return_status => l_return_status,
871 p_range_low => p_range_low,
872 p_range_high => p_range_high
873 );
874
875 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
876 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
877 END IF;
878
879 -- bug3365311
880 -- deleted the procedure delete_gms as the call is unnecessary
881
882 l_progress := '080';
883
884 l_progress := '090';
885
886 delete_price_differentials
887 ( x_return_status => l_return_status,
888 p_purge_entity => 'PO',
889 p_range_low => p_range_low,
890 p_range_high => p_range_high
891 );
892
893 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
894 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
895 END IF;
896
897 -- <HTML Agreement R12 START>
898 l_progress := '100';
899
900 delete_attr_values
901 ( x_return_status => l_return_status,
902 p_range_low => p_range_low,
903 p_range_high => p_range_high
904 );
905
906 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
907 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
908 END IF;
909 -- <HTML Agreement R12 END>
910
911 l_progress := '110';
912
913 delete_po_attachments
914 ( x_return_status => l_return_status,
915 p_range_low => p_range_low,
916 p_range_high => p_range_high
917 );
918
919 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
920 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
921 END IF;
922
923 l_progress := '120';
924
925 -- <HTML Agreement R12 START>
926 -- Delete Draft documents when we purge pos
927 delete_po_drafts
928 ( x_return_status => l_return_status,
929 p_range_low => p_range_low,
930 p_range_high => p_range_high
931 );
932
933 -- <HTML Agreement R12 END>
934
935 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
936 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
937 END IF;
938
939 IF (g_fnd_debug = 'Y') THEN
940 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
941 FND_LOG.string
942 ( log_level => FND_LOG.LEVEL_PROCEDURE,
943 module => l_module || l_progress,
944 message => 'Quitting ' || g_pkg_name || '.' || l_api_name
945 );
946 END IF;
947 END IF;
948
949 EXCEPTION
950 WHEN OTHERS THEN
951 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
952 FND_MSG_PUB.add_exc_msg
953 ( p_pkg_name => g_pkg_name,
954 p_procedure_name => l_api_name || '.' || l_progress
955 );
956
957 END delete_po_related_records;
958
959
960 --*********************************************************************
961 -------------------------- Private Procedures -------------------------
962 --*********************************************************************
963
964 -----------------------------------------------------------------------
965 --Start of Comments
966 --Name: filter_req_pon_validation
967 --Pre-reqs:
968 --Modifies: po_purge_po_list
969 --Locks:
970 -- None
971 --Function: Call Sourcing API to determine whether the records in the
972 -- purge list violates the rules defined in PON if the records are
973 -- purged.
974 -- If so, exclude those records from the purge list
975 --Parameters:
976 --IN:
977 --IN OUT:
978 --OUT:
979 --x_return_status
980 -- status of the procedure
981 --Returns:
982 --Notes:
983 --Testing:
984 --End of Comments
985 ------------------------------------------------------------------------
986
987 PROCEDURE filter_req_pon_validation
988 ( x_return_status OUT NOCOPY VARCHAR2
989 ) IS
990
991 l_api_name VARCHAR2(50) := 'filter_req_pon_validation';
992 l_progress VARCHAR2(3);
993 l_module CONSTANT FND_LOG_MESSAGES.module%TYPE :=
994 G_MODULE_PREFIX || l_api_name || '.';
995
996 l_in_rec PON_PO_INTEGRATION_GRP.PURGE_IN_RECTYPE;
997 l_out_rec PON_PO_INTEGRATION_GRP.PURGE_OUT_RECTYPE;
998
999 l_return_status VARCHAR2(1);
1000 l_msg_count NUMBER;
1001 l_msg_data VARCHAR2(2000);
1002
1003 CURSOR c_po_list IS
1004 SELECT requisition_header_id
1005 FROM po_purge_req_list
1006 WHERE double_check_flag = 'Y';
1007
1008 BEGIN
1009
1010 l_progress := '000';
1011
1012 IF (g_fnd_debug = 'Y') THEN
1013 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1014 FND_LOG.string
1015 ( log_level => FND_LOG.LEVEL_PROCEDURE,
1016 module => l_module || l_progress,
1017 message => 'Entering ' || g_pkg_name || '.' || l_api_name
1018 );
1019 END IF;
1020 END IF;
1021
1022 x_return_status := FND_API.G_RET_STS_SUCCESS;
1023
1024 l_in_rec.entity_name := 'PO_REQUISITION_HEADERS';
1025
1026 OPEN c_po_list;
1027
1028 LOOP
1029 l_progress := '010';
1030
1031 FETCH c_po_list
1032 BULK COLLECT INTO l_in_rec.entity_ids
1033 LIMIT g_batch_limit;
1034
1035 EXIT WHEN l_in_rec.entity_ids.COUNT = 0;
1036
1037 l_progress := '020';
1038
1039 PON_PO_INTEGRATION_GRP.validate_po_purge
1040 ( p_api_version => 1.0,
1041 p_init_msg_list => FND_API.G_TRUE,
1042 p_commit => FND_API.G_FALSE,
1043 x_return_status => l_return_status,
1044 x_msg_count => l_msg_count,
1045 x_msg_data => l_msg_data,
1046 p_in_rec => l_in_rec,
1047 x_out_rec => l_out_rec
1048 );
1049
1050 IF (g_fnd_debug = 'Y') THEN
1051 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1052 FND_LOG.string
1053 ( log_level => FND_LOG.LEVEL_STATEMENT,
1054 module => l_module || l_progress,
1055 message => 'After calling pon val api. rtn status = ' ||
1056 l_return_status
1057 );
1058 END IF;
1059 END IF;
1060
1061 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1062 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1063 END IF;
1064
1065 l_progress := '030';
1066
1067 FORALL i IN 1..l_out_rec.purge_allowed.COUNT
1068 UPDATE po_purge_req_list PPRL
1069 SET PPRL.double_check_flag = 'N'
1070 WHERE PPRL.requisition_header_id = l_in_rec.entity_ids(i)
1071 AND l_out_rec.purge_allowed(i) <> 'Y';
1072
1073 END LOOP;
1074
1075 l_progress := '040';
1076
1077 CLOSE c_po_list;
1078
1079 log_purge_list_count
1080 ( p_module => l_module || l_progress,
1081 p_entity => 'REQ'
1082 );
1083
1084 IF (g_fnd_debug = 'Y') THEN
1085 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1086 FND_LOG.string
1087 ( log_level => FND_LOG.LEVEL_PROCEDURE,
1088 module => l_module || l_progress,
1089 message => 'Quitting ' || g_pkg_name || '.' || l_api_name
1090 );
1091 END IF;
1092 END IF;
1093
1094 EXCEPTION
1095 WHEN OTHERS THEN
1096 IF (c_po_list%ISOPEN) THEN
1097 CLOSE c_po_list;
1098 END IF;
1099
1100 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1101 FND_MSG_PUB.add_exc_msg
1102 ( p_pkg_name => g_pkg_name,
1103 p_procedure_name => l_api_name || '.' || l_progress
1104 );
1105
1106 END filter_req_pon_validation;
1107
1108 -----------------------------------------------------------------------
1109 --Start of Comments
1110 --Name: filter_po_fte_validation
1111 --Pre-reqs:
1112 --Modifies: po_purge_po_list
1113 --Locks:
1114 -- None
1115 --Function: Call FTE API to determine whether the record in the purge list
1116 -- violates the rules defined in FTE if the record is purged.
1117 -- If so, exclude the record from the purge list
1118 --Parameters:
1119 --IN:
1120 --IN OUT:
1121 --OUT:
1122 --x_return_status
1123 -- status of the procedure
1124 --Returns:
1125 --Notes:
1126 --Testing:
1127 --End of Comments
1128 ------------------------------------------------------------------------
1129
1130 PROCEDURE filter_po_fte_validation
1131 ( x_return_status OUT NOCOPY VARCHAR2
1132 ) IS
1133
1134 l_api_name VARCHAR2(50) := 'filter_po_fte_validation';
1135 l_progress VARCHAR2(3);
1136 l_module CONSTANT FND_LOG_MESSAGES.module%TYPE :=
1137 G_MODULE_PREFIX || l_api_name || '.';
1138
1139 CURSOR c_po_list IS
1140 SELECT po_header_id
1141 FROM po_purge_po_list
1142 WHERE double_check_flag = 'Y';
1143
1144 l_in_rec WSH_PO_INTG_TYPES_GRP.purge_in_rectype;
1145 l_out_rec WSH_PO_INTG_TYPES_GRP.purge_out_rectype;
1146 l_return_status VARCHAR2(1);
1147 l_msg_count NUMBER;
1148 l_msg_data VARCHAR2(2000);
1149
1150 BEGIN
1151 l_progress := '000';
1152
1153 IF (g_fnd_debug = 'Y') THEN
1154 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1155 FND_LOG.string
1156 ( log_level => FND_LOG.LEVEL_PROCEDURE,
1157 module => l_module || l_progress,
1158 message => 'Entering ' || g_pkg_name || '.' || l_api_name
1159 );
1160 END IF;
1161 END IF;
1162
1163 x_return_status := FND_API.G_RET_STS_SUCCESS;
1164
1165 -- Do not call FTE API if FTE is not installed or their code level
1166 -- is lower than that of 11.5.10
1167
1168 IF (PO_CORE_S.get_product_install_status('FTE') <> 'I'
1169 OR
1170 WSH_CODE_CONTROL.Get_Code_Release_Level < '110510') THEN
1171
1172 IF (g_fnd_debug = 'Y') THEN
1173 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1174 FND_LOG.string
1175 ( log_level => FND_LOG.LEVEL_STATEMENT,
1176 module => l_module || l_progress,
1177 message => 'Either FTE is not installed or WSH code level ' ||
1178 'is not at 11.5.10 level or above. Quitting'
1179 );
1180 END IF;
1181 END IF;
1182
1183 RETURN;
1184 END IF; -- if FTP notinstalled or WSH code < 110510
1185
1186 l_in_rec.caller := 'PO_DOC_PURGE';
1187
1188 OPEN c_po_list;
1189
1190 LOOP
1191 l_progress := '010';
1192
1193 FETCH c_po_list
1194 BULK COLLECT INTO l_in_rec.header_ids
1195 LIMIT g_batch_limit;
1196
1197 EXIT WHEN l_in_rec.header_ids.COUNT = 0;
1198
1199 l_progress := '020';
1200
1201 WSH_PO_INTEGRATION_GRP.Check_Purge
1202 ( p_api_version_number => 1.0,
1203 p_init_msg_list => FND_API.G_TRUE,
1204 p_commit => FND_API.G_FALSE,
1205 x_return_status => l_return_status,
1206 x_msg_count => l_msg_count,
1207 x_msg_data => l_msg_data,
1208 p_in_rec => l_in_rec,
1209 x_out_rec => l_out_rec
1210 );
1211
1212 IF (g_fnd_debug = 'Y') THEN
1213 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1214 FND_LOG.string
1215 ( log_level => FND_LOG.LEVEL_STATEMENT,
1216 module => l_module || l_progress,
1217 message => 'After calling FTE Validation API. rtn status= ' ||
1218 l_return_status
1219 );
1220 END IF;
1221 END IF;
1222
1223 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1224 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1225 END IF;
1226
1227 l_progress := '030';
1228
1229 FORALL i IN 1..l_out_rec.purge_allowed.COUNT
1230 UPDATE po_purge_po_list PPL
1231 SET PPL.double_check_flag = 'N'
1232 WHERE PPL.po_header_id = l_in_rec.header_ids(i)
1233 AND l_out_rec.purge_allowed(i) <> 'Y';
1234
1235 END LOOP;
1236
1237 l_progress := '040';
1238
1239 CLOSE c_po_list;
1240
1241 log_purge_list_count
1242 ( p_module => l_module || l_progress,
1243 p_entity => 'PO'
1244 );
1245
1246 IF (g_fnd_debug = 'Y') THEN
1247 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1248 FND_LOG.string
1249 ( log_level => FND_LOG.LEVEL_PROCEDURE,
1250 module => l_module || l_progress,
1251 message => 'Quitting ' || g_pkg_name || '.' || l_api_name
1252 );
1253 END IF;
1254 END IF;
1255
1256 EXCEPTION
1257 WHEN OTHERS THEN
1258 IF (c_po_list%ISOPEN) THEN
1259 CLOSE c_po_list;
1260 END IF;
1261
1262 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1263 FND_MSG_PUB.add_exc_msg
1264 ( p_pkg_name => g_pkg_name,
1265 p_procedure_name => l_api_name || '.' || l_progress
1266 );
1267
1268 END filter_po_fte_validation;
1269
1270
1271 -----------------------------------------------------------------------
1272 --Start of Comments
1273 --Name: filter_po_cst_validation
1274 --Pre-reqs:
1275 --Modifies: po_purge_po_list
1276 --Locks:
1277 -- None
1278 --Function: Call Costing API to determine whether the records in the purge list
1279 -- violates the rules defined in CST if the records are purged.
1280 -- If so, exclude those records from the purge list
1281 --Parameters:
1282 --IN:
1283 --IN OUT:
1284 --OUT:
1285 --x_return_status
1286 -- status of the procedure
1287 --Returns:
1288 --Notes:
1289 --Testing:
1290 --End of Comments
1291 ------------------------------------------------------------------------
1292
1293 PROCEDURE filter_po_cst_validation
1294 ( x_return_status OUT NOCOPY VARCHAR2
1295 ) IS
1296
1297 l_api_name VARCHAR2(50) := 'filter_po_cst_validation';
1298 l_progress VARCHAR2(3);
1299 l_module CONSTANT FND_LOG_MESSAGES.module%TYPE :=
1300 G_MODULE_PREFIX || l_api_name || '.';
1301
1302 CURSOR c_po_list IS
1303 SELECT po_header_id
1304 FROM po_purge_po_list
1305 WHERE double_check_flag = 'Y';
1306
1307
1308 l_in_rec RCV_AccrualUtilities_GRP.purge_in_rectype;
1309 l_out_rec RCV_AccrualUtilities_GRP.purge_out_rectype;
1310
1311 l_return_status VARCHAR2(1);
1312 l_msg_count NUMBER;
1313 l_msg_data VARCHAR2(2000);
1314
1315 BEGIN
1316
1317 l_progress := '000';
1318
1319 IF (g_fnd_debug = 'Y') THEN
1320 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1321 FND_LOG.string
1322 ( log_level => FND_LOG.LEVEL_PROCEDURE,
1323 module => l_module || l_progress,
1324 message => 'Entering ' || g_pkg_name || '.' || l_api_name
1325 );
1326 END IF;
1327 END IF;
1328
1329 x_return_status := FND_API.G_RET_STS_SUCCESS;
1330
1331 OPEN c_po_list;
1332
1333 LOOP
1334 l_progress := '010';
1335
1336 FETCH c_po_list
1337 BULK COLLECT INTO l_in_rec.entity_ids
1338 LIMIT g_batch_limit;
1339
1340 EXIT WHEN l_in_rec.entity_ids.COUNT = 0;
1341
1342 l_progress := '020';
1343
1344 RCV_AccrualUtilities_GRP.Validate_PO_Purge
1345 ( p_api_version => 1.0,
1346 p_init_msg_list => FND_API.G_TRUE,
1347 p_commit => FND_API.G_FALSE,
1348 x_return_status => l_return_status,
1349 x_msg_count => l_msg_count,
1350 x_msg_data => l_msg_data,
1351 p_purge_entity_type => 'PO_HEADERS',
1352 p_purge_in_rec => l_in_rec,
1353 x_purge_out_rec => l_out_rec
1354 );
1355
1356 IF (g_fnd_debug = 'Y') THEN
1357 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1358 FND_LOG.string
1359 ( log_level => FND_LOG.LEVEL_STATEMENT,
1360 module => l_module || l_progress,
1361 message => 'After calling CST Val API. rtn status = ' ||
1362 l_return_status
1363 );
1364 END IF;
1365 END IF;
1366
1367 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1368 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1369 END IF;
1370
1371 l_progress := '030';
1372
1373 FORALL i IN 1..l_out_rec.purge_allowed.COUNT
1374 UPDATE po_purge_po_list PPL
1375 SET PPL.double_check_flag = 'N'
1376 WHERE PPL.po_header_id = l_in_rec.entity_ids(i)
1377 AND l_out_rec.purge_allowed(i) <> 'Y';
1378
1379 END LOOP;
1380
1381 l_progress := '040';
1382
1383 CLOSE c_po_list;
1384
1385 log_purge_list_count
1386 ( p_module => l_module || l_progress,
1387 p_entity => 'PO'
1388 );
1389
1390 IF (g_fnd_debug = 'Y') THEN
1391 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1392 FND_LOG.string
1393 ( log_level => FND_LOG.LEVEL_PROCEDURE,
1394 module => l_module || l_progress,
1395 message => 'Quitting ' || g_pkg_name || '.' || l_api_name
1396 );
1397 END IF;
1398 END IF;
1399
1400 EXCEPTION
1401 WHEN OTHERS THEN
1402 IF (c_po_list%ISOPEN) THEN
1403 CLOSE c_po_list;
1404 END IF;
1405
1406 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1407 FND_MSG_PUB.add_exc_msg
1408 ( p_pkg_name => g_pkg_name,
1409 p_procedure_name => l_api_name || '.' || l_progress
1410 );
1411
1412 END filter_po_cst_validation;
1413
1414
1415 -----------------------------------------------------------------------
1416 --Start of Comments
1417 --Name: filter_po_oe_validation
1418 --Pre-reqs:
1419 --Modifies: po_purge_po_list
1420 --Locks:
1421 -- None
1422 --Function: Call OE Drop Ship API to determine whether the records in the
1423 -- purge list violates the rules defined in OE if the records are
1424 -- purged.
1425 -- If so, exclude those records from the purge list
1426 --Parameters:
1427 --IN:
1428 --IN OUT:
1429 --OUT:
1430 --x_return_status
1431 -- status of the procedure
1432 --Returns:
1433 --Notes:
1434 --Testing:
1435 --End of Comments
1436 ------------------------------------------------------------------------
1437
1438 PROCEDURE filter_po_oe_validation
1439 ( x_return_status OUT NOCOPY VARCHAR2
1440 ) IS
1441
1442 l_api_name VARCHAR2(50) := 'filter_po_oe_validation';
1443 l_progress VARCHAR2(3);
1444 l_module CONSTANT FND_LOG_MESSAGES.module%TYPE :=
1445 G_MODULE_PREFIX || l_api_name || '.';
1446
1447 l_hdr_id_tbl OE_DROP_SHIP_GRP.PO_ENTITY_ID_TBL_TYPE;
1448 l_purge_allowed_tbl OE_DROP_SHIP_GRP.VAL_STATUS_TBL_TYPE;
1449
1450 l_return_status VARCHAR2(1);
1451 l_msg_count NUMBER;
1452 l_msg_data VARCHAR2(2000);
1453
1454 CURSOR c_po_list IS
1455 SELECT po_header_id
1456 FROM po_purge_po_list
1457 WHERE double_check_flag = 'Y';
1458
1459 BEGIN
1460
1461 l_progress := '000';
1462
1463 IF (g_fnd_debug = 'Y') THEN
1464 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1465 FND_LOG.string
1466 ( log_level => FND_LOG.LEVEL_PROCEDURE,
1467 module => l_module || l_progress,
1468 message => 'Entering ' || g_pkg_name || '.' || l_api_name
1469 );
1470 END IF;
1471 END IF;
1472
1473 x_return_status := FND_API.G_RET_STS_SUCCESS;
1474
1475 -- No need to do validation if PO or OM is not at FPJ level or above
1476
1477 IF (PO_CODE_RELEASE_GRP.Current_Release <
1478 PO_CODE_RELEASE_GRP.PRC_11i_Family_Pack_J
1479 OR OE_CODE_CONTROL.code_release_level < '110510') THEN
1480
1481 IF (g_fnd_debug = 'Y') THEN
1482 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1483 FND_LOG.string
1484 ( log_level => FND_LOG.LEVEL_STATEMENT,
1485 module => l_module || l_progress,
1486 message => 'Either PO is not at FPJ or above or OM code ' ||
1487 'level is not at 11.5.10 level or above. Quitting'
1488 );
1489 END IF;
1490 END IF;
1491
1492 RETURN;
1493
1494 END IF;
1495
1496 OPEN c_po_list;
1497
1498 LOOP
1499 l_progress := '010';
1500
1501 FETCH c_po_list
1502 BULK COLLECT INTO l_hdr_id_tbl
1503 LIMIT g_batch_limit;
1504
1505 EXIT WHEN l_hdr_id_tbl.COUNT = 0;
1506
1507 l_progress := '020';
1508
1509 l_purge_allowed_tbl := OE_DROP_SHIP_GRP.VAL_STATUS_TBL_TYPE();
1510
1511 OE_DROP_SHIP_GRP.purge_drop_ship_po_validation
1512 ( p_api_version => 1.0,
1513 p_init_msg_list => FND_API.G_TRUE,
1514 p_commit => FND_API.G_FALSE,
1515 x_return_status => l_return_status,
1516 x_msg_count => l_msg_count,
1517 x_msg_data => l_msg_data,
1518 p_entity => 'PO_HEADERS',
1519 p_entity_id_tbl => l_hdr_id_tbl,
1520 x_purge_allowed_tbl => l_purge_allowed_tbl
1521 );
1522
1523 IF (g_fnd_debug = 'Y') THEN
1524 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1525 FND_LOG.string
1526 ( log_level => FND_LOG.LEVEL_STATEMENT,
1527 module => l_module || l_progress,
1528 message => 'After calling OE Val API. rtn status = ' ||
1529 l_return_status
1530 );
1531 END IF;
1532 END IF;
1533
1534 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1535 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1536 END IF;
1537
1538 l_progress := '030';
1539
1540 IF (l_purge_allowed_tbl IS NOT NULL) THEN
1541
1542 FORALL i IN 1..l_purge_allowed_tbl.COUNT
1543 UPDATE po_purge_po_list PPL
1544 SET PPL.double_check_flag = 'N'
1545 WHERE PPL.po_header_id = l_hdr_id_tbl(i)
1546 AND l_purge_allowed_tbl(i) <> 'Y';
1547
1548 END IF;
1549
1550 END LOOP;
1551
1552 l_progress := '040';
1553
1554 CLOSE c_po_list;
1555
1556 log_purge_list_count
1557 ( p_module => l_module || l_progress,
1558 p_entity => 'PO'
1559 );
1560
1561 IF (g_fnd_debug = 'Y') THEN
1562 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1563 FND_LOG.string
1564 ( log_level => FND_LOG.LEVEL_PROCEDURE,
1565 module => l_module || l_progress,
1566 message => 'Quitting ' || g_pkg_name || '.' || l_api_name
1567 );
1568 END IF;
1569 END IF;
1570
1571 EXCEPTION
1572 WHEN OTHERS THEN
1573 IF (c_po_list%ISOPEN) THEN
1574 CLOSE c_po_list;
1575 END IF;
1576
1577 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1578 FND_MSG_PUB.add_exc_msg
1579 ( p_pkg_name => g_pkg_name,
1580 p_procedure_name => l_api_name || '.' || l_progress
1581 );
1582
1583 END filter_po_oe_validation;
1584
1585
1586 -----------------------------------------------------------------------
1587 --Start of Comments
1588 --Name: filter_po_pon_validation
1589 --Pre-reqs:
1590 --Modifies: po_purge_po_list
1591 --Locks:
1592 -- None
1593 --Function: Call Sourcing API to determine whether the records in the
1594 -- purge list violates the rules defined in PON if the records are
1595 -- purged.
1596 -- If so, exclude those records from the purge list
1597 --Parameters:
1598 --IN:
1599 --IN OUT:
1600 --OUT:
1601 --x_return_status
1602 -- status of the procedure
1603 --Returns:
1604 --Notes:
1605 --Testing:
1606 --End of Comments
1607 ------------------------------------------------------------------------
1608
1609 PROCEDURE filter_po_pon_validation
1610 ( x_return_status OUT NOCOPY VARCHAR2
1611 ) IS
1612
1613 l_api_name VARCHAR2(50) := 'filter_po_pon_validation';
1614 l_progress VARCHAR2(3);
1615 l_module CONSTANT FND_LOG_MESSAGES.module%TYPE :=
1616 G_MODULE_PREFIX || l_api_name || '.';
1617
1618 l_in_rec PON_PO_INTEGRATION_GRP.PURGE_IN_RECTYPE;
1619 l_out_rec PON_PO_INTEGRATION_GRP.PURGE_OUT_RECTYPE;
1620
1621 l_return_status VARCHAR2(1);
1622 l_msg_count NUMBER;
1623 l_msg_data VARCHAR2(2000);
1624
1625 CURSOR c_po_list IS
1626 SELECT po_header_id
1627 FROM po_purge_po_list
1628 WHERE double_check_flag = 'Y';
1629
1630 BEGIN
1631
1632 l_progress := '000';
1633
1634 IF (g_fnd_debug = 'Y') THEN
1635 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1636 FND_LOG.string
1637 ( log_level => FND_LOG.LEVEL_PROCEDURE,
1638 module => l_module || l_progress,
1639 message => 'Entering ' || g_pkg_name || '.' || l_api_name
1640 );
1641 END IF;
1642 END IF;
1643
1644 x_return_status := FND_API.G_RET_STS_SUCCESS;
1645
1646 l_in_rec.entity_name := 'PO_HEADERS';
1647
1648 OPEN c_po_list;
1649
1650 LOOP
1651 l_progress := '010';
1652
1653 FETCH c_po_list
1654 BULK COLLECT INTO l_in_rec.entity_ids
1655 LIMIT g_batch_limit;
1656
1657 EXIT WHEN l_in_rec.entity_ids.COUNT = 0;
1658
1659 l_progress := '020';
1660
1661 PON_PO_INTEGRATION_GRP.validate_po_purge
1662 ( p_api_version => 1.0,
1663 p_init_msg_list => FND_API.G_TRUE,
1664 p_commit => FND_API.G_FALSE,
1665 x_return_status => l_return_status,
1666 x_msg_count => l_msg_count,
1667 x_msg_data => l_msg_data,
1668 p_in_rec => l_in_rec,
1669 x_out_rec => l_out_rec
1670 );
1671
1672 IF (g_fnd_debug = 'Y') THEN
1673 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1674 FND_LOG.string
1675 ( log_level => FND_LOG.LEVEL_STATEMENT,
1676 module => l_module || l_progress,
1677 message => 'After calling pon val api. rtn status = ' ||
1678 l_return_status
1679 );
1680 END IF;
1681 END IF;
1682
1683 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1684 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1685 END IF;
1686
1687 l_progress := '030';
1688
1689 FORALL i IN 1..l_out_rec.purge_allowed.COUNT
1690 UPDATE po_purge_po_list PPL
1691 SET PPL.double_check_flag = 'N'
1692 WHERE PPL.po_header_id = l_in_rec.entity_ids(i)
1693 AND l_out_rec.purge_allowed(i) <> 'Y';
1694
1695 END LOOP;
1696
1697 l_progress := '040';
1698
1699 CLOSE c_po_list;
1700
1701 log_purge_list_count
1702 ( p_module => l_module || l_progress,
1703 p_entity => 'PO'
1704 );
1705
1706 IF (g_fnd_debug = 'Y') THEN
1707 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1708 FND_LOG.string
1709 ( log_level => FND_LOG.LEVEL_PROCEDURE,
1710 module => l_module || l_progress,
1711 message => 'Quitting ' || g_pkg_name || '.' || l_api_name
1712 );
1713 END IF;
1714 END IF;
1715
1716 EXCEPTION
1717 WHEN OTHERS THEN
1718 IF (c_po_list%ISOPEN) THEN
1719 CLOSE c_po_list;
1720 END IF;
1721
1722 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1723 FND_MSG_PUB.add_exc_msg
1724 ( p_pkg_name => g_pkg_name,
1725 p_procedure_name => l_api_name || '.' || l_progress
1726 );
1727
1728 END filter_po_pon_validation;
1729
1730 -----------------------------------------------------------------------
1731 --Start of Comments
1732 --Name: filter_po_hr_validation
1733 --Pre-reqs:
1734 --Modifies: po_purge_po_list
1735 --Locks:
1736 -- None
1737 --Function: Call HR API to determine whether the records in the
1738 -- purge list violates the rules defined in HR if the records are
1739 -- purged.
1740 -- If so, exclude those records from the purge list
1741 --Parameters:
1742 --IN:
1743 --IN OUT:
1744 --OUT:
1745 --x_return_status
1746 -- status of the procedure
1747 --Returns:
1748 --Notes:
1749 --Testing:
1750 --End of Comments
1751 ------------------------------------------------------------------------
1752
1753 PROCEDURE filter_po_hr_validation
1754 ( x_return_status OUT NOCOPY VARCHAR2
1755 ) IS
1756
1757 l_api_name VARCHAR2(50) := 'filter_po_hr_validation';
1758 l_progress VARCHAR2(3);
1759 l_module CONSTANT FND_LOG_MESSAGES.module%TYPE :=
1760 G_MODULE_PREFIX || l_api_name || '.';
1761
1762 l_in_tbl HR_PO_INFO.g_table_numbers_t;
1763 l_out_tbl HR_PO_INFO.g_table_numbers_t;
1764
1765 CURSOR c_po_list IS
1766 SELECT po_header_id
1767 FROM po_purge_po_list
1768 WHERE double_check_flag = 'Y';
1769
1770 BEGIN
1771
1772 l_progress := '000';
1773
1774 IF (g_fnd_debug = 'Y') THEN
1775 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1776 FND_LOG.string
1777 ( log_level => FND_LOG.LEVEL_PROCEDURE,
1778 module => l_module || l_progress,
1779 message => 'Entering ' || g_pkg_name || '.' || l_api_name
1780 );
1781 END IF;
1782 END IF;
1783
1784 x_return_status := FND_API.G_RET_STS_SUCCESS;
1785
1786 OPEN c_po_list;
1787
1788 LOOP
1789 l_progress := '010';
1790
1791 FETCH c_po_list
1792 BULK COLLECT INTO l_in_tbl
1793 LIMIT g_batch_limit;
1794
1795 EXIT WHEN l_in_tbl.COUNT = 0;
1796
1797 l_progress := '020';
1798
1799 HR_PO_INFO.asgs_exist_for_pos
1800 ( p_po_in_tbl => l_in_tbl,
1801 p_po_out_tbl => l_out_tbl
1802 );
1803
1804 IF (g_fnd_debug = 'Y') THEN
1805 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1806 FND_LOG.string
1807 ( log_level => FND_LOG.LEVEL_STATEMENT,
1808 module => l_module || l_progress,
1809 message => 'After calling hr val api.'
1810 );
1811 END IF;
1812 END IF;
1813
1814 l_progress := '030';
1815
1816 FORALL i IN 1..l_out_tbl.COUNT
1817 UPDATE po_purge_po_list PPL
1818 SET PPL.double_check_flag = 'N'
1819 WHERE PPL.po_header_id = l_out_tbl(i);
1820
1821 END LOOP;
1822
1823 l_progress := '040';
1824
1825 CLOSE c_po_list;
1826
1827 log_purge_list_count
1828 ( p_module => l_module || l_progress,
1829 p_entity => 'PO'
1830 );
1831
1832 IF (g_fnd_debug = 'Y') THEN
1833 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1834 FND_LOG.string
1835 ( log_level => FND_LOG.LEVEL_PROCEDURE,
1836 module => l_module || l_progress,
1837 message => 'Quitting ' || g_pkg_name || '.' || l_api_name
1838 );
1839 END IF;
1840 END IF;
1841
1842 EXCEPTION
1843 WHEN OTHERS THEN
1844 IF (c_po_list%ISOPEN) THEN
1845 CLOSE c_po_list;
1846 END IF;
1847
1848 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1849 FND_MSG_PUB.add_exc_msg
1850 ( p_pkg_name => g_pkg_name,
1851 p_procedure_name => l_api_name || '.' || l_progress
1852 );
1853
1854 END filter_po_hr_validation;
1855
1856
1857
1858
1859 -----------------------------------------------------------------------
1860 --Start of Comments
1861 --Name: delete_asl_ref
1862 --Pre-reqs:
1863 --Modifies: PO_ASL_DOCUMENTS
1864 --Locks:
1865 -- None
1866 --Function: Delete referenced documents on ASL when the document is purged
1867 --Parameters:
1868 --IN:
1869 --p_range_low
1870 -- lower bound of the po to be purged
1871 --p_range_high
1872 -- upper bound of the po to be purged
1873 --IN OUT:
1874 --OUT:
1875 --x_return_status
1876 -- status of the procedure
1877 --Returns:
1878 --Notes:
1879 --Testing:
1880 --End of Comments
1881 ------------------------------------------------------------------------
1882
1883 PROCEDURE delete_asl_ref
1884 ( x_return_status OUT NOCOPY VARCHAR2,
1885 p_range_low IN NUMBER,
1886 p_range_high IN NUMBER
1887 ) IS
1888
1889 l_api_name VARCHAR2(50) := 'delete_asl_ref';
1890 l_progress VARCHAR2(3);
1891 l_module CONSTANT FND_LOG_MESSAGES.module%TYPE :=
1892 G_MODULE_PREFIX || l_api_name || '.';
1893
1894 BEGIN
1895
1896 l_progress := '000';
1897
1898 IF (g_fnd_debug = 'Y') THEN
1899 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1900 FND_LOG.string
1901 ( log_level => FND_LOG.LEVEL_PROCEDURE,
1902 module => l_module || l_progress,
1903 message => 'Entering ' || g_pkg_name || '.' || l_api_name
1904 );
1905 END IF;
1906 END IF;
1907
1908 x_return_status := FND_API.G_RET_STS_SUCCESS;
1909
1910 -- bug3256316
1911 -- We should be selecting PPL.po_Header_id instead of NULL
1912
1913 --SQL What: Delete ASL doc reference for documents that are in the purge
1914 -- List
1915 --SQL Why: When document is purged,the ASL reference becomes useless.
1916 -- Hence it can be deleted
1917 DELETE
1918 FROM po_asl_documents PAD
1919 WHERE PAD.document_header_id IN
1920 ( SELECT PPL.po_header_id
1921 FROM po_purge_po_list PPL
1922 WHERE PPL.double_check_flag = 'Y'
1923 AND PPL.po_header_id BETWEEN p_range_low
1924 AND p_range_high);
1925
1926 IF (g_fnd_debug = 'Y') THEN
1927 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1928 FND_LOG.string
1929 ( log_level => FND_LOG.LEVEL_PROCEDURE,
1930 module => l_module || l_progress,
1931 message => 'Quitting ' || g_pkg_name || '.' || l_api_name
1932 );
1933 END IF;
1934 END IF;
1935
1936 EXCEPTION
1937 WHEN OTHERS THEN
1938 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1939 FND_MSG_PUB.add_exc_msg
1940 ( p_pkg_name => g_pkg_name,
1941 p_procedure_name => l_api_name || '.' || l_progress
1942 );
1943
1944 END delete_asl_ref;
1945
1946
1947 -----------------------------------------------------------------------
1948 --Start of Comments
1949 --Name: delete_org_assignments
1950 --Pre-reqs:
1951 --Modifies: PO_GA_ORG_ASSSIGNMENTS, PO_GA_ORG_ASSIGNMENTS_ARCHIVE
1952 --Locks:
1953 -- None
1954 --Function: This procedure removes org assignments for documents that
1955 -- are being purged
1956 --Parameters:
1957 --IN:
1958 --p_range_low
1959 -- lower bound of the po to be purged
1960 --p_range_high
1961 -- upper bound of the po to be purged
1962 --IN OUT:
1963 --OUT:
1964 --x_return_status
1965 -- status of the procedure
1966 --Returns:
1967 --Notes:
1968 --Testing:
1969 --End of Comments
1970 ------------------------------------------------------------------------
1971
1972 PROCEDURE delete_org_assignments
1973 ( x_return_status OUT NOCOPY VARCHAR2,
1974 p_range_low IN NUMBER,
1975 p_range_high IN NUMBER
1976 ) IS
1977
1978 l_api_name VARCHAR2(50) := 'delete_org_assignments';
1979 l_progress VARCHAR2(3);
1980 l_module CONSTANT FND_LOG_MESSAGES.module%TYPE :=
1981 G_MODULE_PREFIX || l_api_name || '.';
1982
1983 BEGIN
1984
1985 l_progress := '000';
1986
1987 IF (g_fnd_debug = 'Y') THEN
1988 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1989 FND_LOG.string
1990 ( log_level => FND_LOG.LEVEL_PROCEDURE,
1991 module => l_module || l_progress,
1992 message => 'Entering ' || g_pkg_name || '.' || l_api_name
1993 );
1994 END IF;
1995 END IF;
1996
1997 x_return_status := FND_API.G_RET_STS_SUCCESS;
1998
1999 DELETE
2000 FROM po_ga_org_assignments PGOA
2001 WHERE PGOA.po_header_id IN
2002 (SELECT PPL.po_header_id
2003 FROM po_purge_po_list PPL
2004 WHERE PPL.double_check_flag = 'Y'
2005 AND PPL.po_header_id BETWEEN p_range_low
2006 AND p_range_high);
2007
2008 IF (g_fnd_debug = 'Y') THEN
2009 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2010 FND_LOG.string
2011 ( log_level => FND_LOG.LEVEL_STATEMENT,
2012 module => l_module || l_progress,
2013 message => 'Deleted PGOA rowcount = ' || SQL%ROWCOUNT
2014 );
2015 END IF;
2016 END IF;
2017
2018 l_progress := '010';
2019
2020 DELETE
2021 FROM po_ga_org_assignments_archive PGOAA
2022 WHERE PGOAA.po_header_id IN
2023 (SELECT PPL.po_header_id
2024 FROM po_purge_po_list PPL
2025 WHERE PPL.double_check_flag = 'Y'
2026 AND PPL.po_header_id BETWEEN p_range_low
2027 AND p_range_high);
2028
2029 IF (g_fnd_debug = 'Y') THEN
2030 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2031 FND_LOG.string
2032 ( log_level => FND_LOG.LEVEL_STATEMENT,
2033 module => l_module || l_progress,
2034 message => 'Deleted PGOAA rowcount = ' || SQL%ROWCOUNT
2035 );
2036 END IF;
2037 END IF;
2038
2039 IF (g_fnd_debug = 'Y') THEN
2040 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
2041 FND_LOG.string
2042 ( log_level => FND_LOG.LEVEL_PROCEDURE,
2043 module => l_module || l_progress,
2044 message => 'Quitting ' || g_pkg_name || '.' || l_api_name
2045 );
2046 END IF;
2047 END IF;
2048
2049 EXCEPTION
2050 WHEN OTHERS THEN
2051 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2052 FND_MSG_PUB.add_exc_msg
2053 ( p_pkg_name => g_pkg_name,
2054 p_procedure_name => l_api_name || '.' || l_progress
2055 );
2056
2057 END delete_org_assignments;
2058
2059
2060 -----------------------------------------------------------------------
2061 --Start of Comments
2062 --Name: delete_drop_ship_po_links
2063 --Pre-reqs:
2064 --Modifies:
2065 --Locks:
2066 -- None
2067 --Function: Call OM API to remove the relationship between OM and PO
2068 -- when PO document is purged
2069 --Parameters:
2070 --IN:
2071 --p_range_low
2072 -- lower bound of the po to be purged
2073 --p_range_high
2074 -- upper bound of the po to be purged
2075 --IN OUT:
2076 --OUT:
2077 --x_return_status
2078 -- status of the procedure
2079 --Returns:
2080 --Notes:
2081 --Testing:
2082 --End of Comments
2083 ------------------------------------------------------------------------
2084
2085 PROCEDURE delete_drop_ship_po_links
2086 ( x_return_status OUT NOCOPY VARCHAR2,
2087 p_range_low IN NUMBER,
2088 p_range_high IN NUMBER
2089 ) IS
2090
2091 l_api_name VARCHAR2(50) := 'delete_drop_ship_po_links';
2092 l_progress VARCHAR2(3);
2093 l_return_status VARCHAR2(1);
2094 l_module CONSTANT FND_LOG_MESSAGES.module%TYPE :=
2095 G_MODULE_PREFIX || l_api_name || '.';
2096
2097 l_po_hdr_tbl OE_DROP_SHIP_GRP.PO_ENTITY_ID_TBL_TYPE;
2098
2099 l_msg_count NUMBER;
2100 l_msg_data VARCHAR2(2000);
2101
2102 BEGIN
2103 l_progress := '000';
2104
2105 IF (g_fnd_debug = 'Y') THEN
2106 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
2107 FND_LOG.string
2108 ( log_level => FND_LOG.LEVEL_PROCEDURE,
2109 module => l_module || l_progress,
2110 message => 'Entering ' || g_pkg_name || '.' || l_api_name
2111 );
2112 END IF;
2113 END IF;
2114
2115 x_return_status := FND_API.G_RET_STS_SUCCESS;
2116
2117 SELECT PPL.po_header_id
2118 BULK COLLECT
2119 INTO l_po_hdr_tbl
2120 FROM po_purge_po_list PPL
2121 WHERE PPL.po_header_id BETWEEN p_range_low AND p_range_high
2122 AND PPL.double_check_flag = 'Y';
2123
2124 l_progress := '010';
2125
2126 -- Call OM Drop Ship Purge API only if both PO and OM are at FPJ level
2127 -- or above
2128
2129 IF (PO_CODE_RELEASE_GRP.Current_Release >=
2130 PO_CODE_RELEASE_GRP.PRC_11i_Family_Pack_J
2131 AND OE_CODE_CONTROL.code_release_level >= '110510') THEN
2132
2133 l_progress := '020';
2134
2135 OE_DROP_SHIP_GRP.purge_drop_ship_po_links
2136 ( p_api_version => 1.0,
2137 p_init_msg_list => FND_API.G_TRUE,
2138 p_commit => FND_API.G_FALSE,
2139 x_return_status => l_return_status,
2140 x_msg_count => l_msg_count,
2141 x_msg_data => l_msg_data,
2142 p_entity => 'PO_HEADERS',
2143 p_entity_id_tbl => l_po_hdr_tbl
2144 );
2145
2146 IF (g_fnd_debug = 'Y') THEN
2147 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2148 FND_LOG.string
2149 ( log_level => FND_LOG.LEVEL_STATEMENT,
2150 module => l_module || l_progress,
2151 message => 'After calling OE purge API. rtn status = ' ||
2152 l_return_status
2153 );
2154 END IF;
2155 END IF;
2156
2157 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2158 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2159 END IF;
2160
2161 END IF; -- IF PO Level > FPJ and OM level > 110510
2162
2163 IF (g_fnd_debug = 'Y') THEN
2164 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
2165 FND_LOG.string
2166 ( log_level => FND_LOG.LEVEL_PROCEDURE,
2167 module => l_module || l_progress,
2168 message => 'Quitting ' || g_pkg_name || '.' || l_api_name
2169 );
2170 END IF;
2171 END IF;
2172
2173 EXCEPTION
2174 WHEN OTHERS THEN
2175 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2176 FND_MSG_PUB.add_exc_msg
2177 ( p_pkg_name => g_pkg_name,
2178 p_procedure_name => l_api_name || '.' || l_progress
2179 );
2180
2181 END delete_drop_ship_po_links;
2182
2183
2184 -----------------------------------------------------------------------
2185 --Start of Comments
2186 --Name: delete_fte
2187 --Pre-reqs:
2188 --Modifies:
2189 --Locks:
2190 -- None
2191 --Function: Call FTE API to nofity them what documents have been purged
2192 --Parameters:
2193 --IN:
2194 --p_range_low
2195 -- lower bound of the po to be purged
2196 --p_range_high
2197 -- upper bound of the po to be purged
2198 --IN OUT:
2199 --OUT:
2200 --x_return_status
2201 -- status of the procedure
2202 --Returns:
2203 --Notes:
2204 --Testing:
2205 --End of Comments
2206 ------------------------------------------------------------------------
2207
2208 PROCEDURE delete_fte
2209 ( x_return_status OUT NOCOPY VARCHAR2,
2210 p_range_low IN NUMBER,
2211 p_range_high IN NUMBER
2212 ) IS
2213
2214 l_api_name VARCHAR2(50) := 'delete_fte';
2215 l_progress VARCHAR2(3);
2216 l_return_status VARCHAR2(1);
2217 l_module CONSTANT FND_LOG_MESSAGES.module%TYPE :=
2218 G_MODULE_PREFIX || l_api_name || '.';
2219
2220 l_fte_in_rec WSH_PO_INTG_TYPES_GRP.purge_in_rectype;
2221 l_msg_count NUMBER;
2222 l_msg_data VARCHAR2(2000);
2223
2224 BEGIN
2225 l_progress := '000';
2226
2227 IF (g_fnd_debug = 'Y') THEN
2228 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
2229 FND_LOG.string
2230 ( log_level => FND_LOG.LEVEL_PROCEDURE,
2231 module => l_module || l_progress,
2232 message => 'Entering ' || g_pkg_name || '.' || l_api_name
2233 );
2234 END IF;
2235 END IF;
2236
2237 x_return_status := FND_API.G_RET_STS_SUCCESS;
2238
2239 IF (PO_CORE_S.get_product_install_status('FTE') <> 'I'
2240 OR
2241 WSH_CODE_CONTROL.Get_Code_Release_Level < '110510') THEN
2242
2243 IF (g_fnd_debug = 'Y') THEN
2244 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2245 FND_LOG.string
2246 ( log_level => FND_LOG.LEVEL_STATEMENT,
2247 module => l_module || l_progress,
2248 message => 'Either FTE is not installed or WSH code level '||
2249 'is not at 11.5.10 level. Quitting'
2250 );
2251 END IF;
2252 END IF;
2253
2254 RETURN;
2255 END IF; -- fte is not installed or wsh code < 110510
2256
2257 l_fte_in_rec.caller := 'PO_DOC_PURGE';
2258
2259 SELECT PPL.po_header_id
2260 BULK COLLECT
2261 INTO l_fte_in_rec.header_ids
2262 FROM po_purge_po_list PPL
2263 WHERE PPL.double_check_flag = 'Y'
2264 AND PPL.po_header_id BETWEEN p_range_low AND p_range_high;
2265
2266 l_progress := '010';
2267
2268 WSH_PO_INTEGRATION_GRP.purge
2269 ( p_api_version_number => 1.0,
2270 p_init_msg_list => FND_API.G_TRUE,
2271 p_commit => FND_API.G_FALSE,
2272 x_return_status => l_return_status,
2273 x_msg_count => l_msg_count,
2274 x_msg_data => l_msg_data,
2275 p_in_rec => l_fte_in_rec
2276 );
2277
2278 IF (g_fnd_debug = 'Y') THEN
2279 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2280 FND_LOG.string
2281 ( log_level => FND_LOG.LEVEL_STATEMENT,
2282 module => l_module || l_progress,
2283 message => 'After calling FTE Purge API. rtn status = ' ||
2284 l_return_status
2285 );
2286 END IF;
2287 END IF;
2288
2289 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2290 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2291 END IF;
2292
2293 IF (g_fnd_debug = 'Y') THEN
2294 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
2295 FND_LOG.string
2296 ( log_level => FND_LOG.LEVEL_PROCEDURE,
2297 module => l_module || l_progress,
2298 message => 'Quitting ' || g_pkg_name || '.' || l_api_name
2299 );
2300 END IF;
2301 END IF;
2302
2303 EXCEPTION
2304 WHEN OTHERS THEN
2305 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2306 FND_MSG_PUB.add_exc_msg
2307 ( p_pkg_name => g_pkg_name,
2308 p_procedure_name => l_api_name || '.' || l_progress
2309 );
2310
2311 END delete_fte;
2312
2313
2314 -----------------------------------------------------------------------
2315 --Start of Comments
2316 --Name: delete_pon
2317 --Pre-reqs:
2318 --Modifies:
2319 --Locks:
2320 -- None
2321 --Function: Call PON API to nofity them what documents have been purged
2322 --Parameters:
2323 --IN:
2324 --p_purge_entity
2325 -- 'PO' for PO documents
2326 -- 'REQ' for Requisition documents
2327 --p_range_low
2328 -- lower bound of the po to be purged
2329 --p_range_high
2330 -- upper bound of the po to be purged
2331 --IN OUT:
2332 --OUT:
2333 --x_return_status
2334 -- status of the procedure
2335 --Returns:
2336 --Notes:
2337 --Testing:
2338 --End of Comments
2339 ------------------------------------------------------------------------
2340
2341 PROCEDURE delete_pon
2342 ( x_return_status OUT NOCOPY VARCHAR2,
2343 p_purge_entity IN VARCHAR2,
2344 p_range_low IN NUMBER,
2345 p_range_high IN NUMBER
2346 ) IS
2347
2348 l_api_name VARCHAR2(50) := 'delete_pon';
2349 l_progress VARCHAR2(3);
2350 l_return_status VARCHAR2(1);
2351 l_module CONSTANT FND_LOG_MESSAGES.module%TYPE :=
2352 G_MODULE_PREFIX || l_api_name || '.';
2353
2354 l_in_rec PON_PO_INTEGRATION_GRP.purge_in_rectype;
2355 l_msg_count NUMBER;
2356 l_msg_data VARCHAR2(2000);
2357
2358 BEGIN
2359 l_progress := '000';
2360
2361 IF (g_fnd_debug = 'Y') THEN
2362 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
2363 FND_LOG.string
2364 ( log_level => FND_LOG.LEVEL_PROCEDURE,
2365 module => l_module || l_progress,
2366 message => 'Entering ' || g_pkg_name || '.' || l_api_name
2367 );
2368 END IF;
2369 END IF;
2370
2371 x_return_status := FND_API.G_RET_STS_SUCCESS;
2372
2373 IF (p_purge_entity = 'PO') THEN
2374 l_in_rec.entity_name := 'PO_HEADERS';
2375
2376 SELECT PPL.po_header_id
2377 BULK COLLECT
2378 INTO l_in_rec.entity_ids
2379 FROM po_purge_po_list PPL
2380 WHERE PPL.double_check_flag = 'Y'
2381 AND PPL.po_header_id BETWEEN p_range_low AND p_range_high;
2382
2383 l_progress := '010';
2384 ELSE
2385 l_in_rec.entity_name := 'PO_REQUISITION_HEADERS';
2386
2387 SELECT PPRL.requisition_header_id
2388 BULK COLLECT
2389 INTO l_in_rec.entity_ids
2390 FROM po_purge_req_list PPRL
2391 WHERE PPRL.double_check_flag = 'Y'
2392 AND PPRL.requisition_header_id BETWEEN p_range_low
2393 AND p_range_high;
2394
2395 l_progress := '020';
2396 END IF;
2397
2398 PON_PO_INTEGRATION_GRP.po_purge
2399 ( p_api_version => 1.0,
2400 p_init_msg_list => FND_API.G_TRUE,
2401 p_commit => FND_API.G_FALSE,
2402 x_return_status => l_return_status,
2403 x_msg_count => l_msg_count,
2404 x_msg_data => l_msg_data,
2405 p_in_rec => l_in_rec
2406 );
2407
2408 IF (g_fnd_debug = 'Y') THEN
2409 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2410 FND_LOG.string
2411 ( log_level => FND_LOG.LEVEL_STATEMENT,
2412 module => l_module || l_progress,
2413 message => 'After calling PON Purge API. rtn status = ' ||
2414 l_return_status
2415 );
2416 END IF;
2417 END IF;
2418
2419 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2420 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2421 END IF;
2422
2423 IF (g_fnd_debug = 'Y') THEN
2424 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
2425 FND_LOG.string
2426 ( log_level => FND_LOG.LEVEL_PROCEDURE,
2427 module => l_module || l_progress,
2428 message => 'Quitting ' || g_pkg_name || '.' || l_api_name
2429 );
2430 END IF;
2431 END IF;
2432
2433 EXCEPTION
2434 WHEN OTHERS THEN
2435 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2436 FND_MSG_PUB.add_exc_msg
2437 ( p_pkg_name => g_pkg_name,
2438 p_procedure_name => l_api_name || '.' || l_progress
2439 );
2440
2441 END delete_pon;
2442
2443
2444
2445 -----------------------------------------------------------------------
2446 --Start of Comments
2447 --Name: delete_contract_terms
2448 --Pre-reqs:
2449 --Modifies:
2450 --Locks:
2451 -- None
2452 --Function: Call OKC API to remove associated contract terms when
2453 -- documents are purged
2454 --Parameters:
2455 --IN:
2456 --p_range_low
2457 -- lower bound of the po to be purged
2458 --p_range_high
2459 -- upper bound of the po to be purged
2460 --IN OUT:
2461 --OUT:
2462 --x_return_status
2463 -- status of the procedure
2464 --Returns:
2465 --Notes:
2466 --Testing:
2467 --End of Comments
2468 ------------------------------------------------------------------------
2469
2470 PROCEDURE delete_contract_terms
2471 ( x_return_status OUT NOCOPY VARCHAR2,
2472 p_range_low IN NUMBER,
2473 p_range_high IN NUMBER
2474 ) IS
2475
2476 l_api_name VARCHAR2(50) := 'delete_contract_terms';
2477 l_progress VARCHAR2(3);
2478 l_return_status VARCHAR2(1);
2479 l_module CONSTANT FND_LOG_MESSAGES.module%TYPE :=
2480 G_MODULE_PREFIX || l_api_name || '.';
2481
2482 l_header_tbl OKC_TERMS_UTIL_GRP.doc_tbl_type;
2483 l_msg_count NUMBER;
2484 l_msg_data VARCHAR2(2000);
2485
2486
2487 -- bug3293282
2488 -- Sinec bulk collect into a table of records does not work in 8i, we need
2489 -- to change the select statement into a cursor and fetch the record one by
2490 -- one
2491
2492 -- SQL What: Construct l_header_tbl with doc type and id information
2493 -- for records in the purge list
2494 -- SQL Why: Prepare input parameter for okc purge api.
2495 CURSOR c_po_list IS
2496 SELECT DECODE (PH.type_lookup_code, 'STANDARD', 'PO_STANDARD',
2497 'BLANKET' , 'PA_BLANKET',
2498 'CONTRACT', 'PA_CONTRACT') DOC_TYPE,
2499 PH.po_header_id DOC_ID
2500 FROM po_headers PH,
2501 po_purge_po_list PPL
2502 WHERE PH.po_header_id = PPL.po_header_id
2503 AND PPL.double_check_flag = 'Y'
2504 AND PPL.po_header_id BETWEEN p_range_low AND p_range_high
2505 AND PH.type_lookup_code IN ('STANDARD', 'BLANKET', 'CONTRACT')
2506 AND PH.conterms_exist_flag = 'Y';
2507
2508 l_po_list_rec c_po_list%ROWTYPE;
2509 l_rec_count NUMBER := 0;
2510
2511 BEGIN
2512 l_progress := '000';
2513
2514 IF (g_fnd_debug = 'Y') THEN
2515 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
2516 FND_LOG.string
2517 ( log_level => FND_LOG.LEVEL_PROCEDURE,
2518 module => l_module || l_progress,
2519 message => 'Entering ' || g_pkg_name || '.' || l_api_name
2520 );
2521 END IF;
2522 END IF;
2523
2524 x_return_status := FND_API.G_RET_STS_SUCCESS;
2525
2526 IF (PO_CONTERMS_UTL_GRP.is_contracts_enabled = 'N') THEN
2527 IF (g_fnd_debug = 'Y') THEN
2528 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2529 FND_LOG.string
2530 ( log_level => FND_LOG.LEVEL_STATEMENT,
2531 module => l_module || l_progress,
2532 message => 'Procurement Contract is not available. Exit ' ||
2533 'delete_contract_terms'
2534 );
2535 END IF;
2536 END IF;
2537
2538 RETURN;
2539 END IF;
2540
2541 -- Loop through each record in cursor c_po_list and populate the
2542 -- same into l_header_tbl. It is done this way since bulk Collect into
2543 -- table of records is not supported in 8i.
2544
2545 FOR l_po_list_rec IN c_po_list LOOP
2546 l_rec_count := l_rec_count + 1;
2547 l_header_tbl(l_rec_count).doc_type := l_po_list_rec.doc_type;
2548 l_header_tbl(l_rec_count).doc_id := l_po_list_rec.doc_id;
2549 END LOOP;
2550
2551 IF (l_header_tbl.COUNT = 0) THEN
2552 RETURN;
2553 END IF;
2554
2555 OKC_TERMS_UTIL_GRP.purge_doc
2556 ( p_api_version => 1.0,
2557 p_init_msg_list => FND_API.G_TRUE,
2558 p_commit => FND_API.G_FALSE,
2559 x_return_status => l_return_status,
2560 x_msg_data => l_msg_data,
2561 x_msg_count => l_msg_count,
2562 p_doc_tbl => l_header_tbl
2563 );
2564
2565 IF (g_fnd_debug = 'Y') THEN
2566 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2567 FND_LOG.string
2568 ( log_level => FND_LOG.LEVEL_STATEMENT,
2569 module => l_module || l_progress,
2570 message => 'After calling okc purge API. rtn status = ' ||
2571 l_return_status
2572 );
2573 END IF;
2574 END IF;
2575
2576 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2577 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2578 END IF;
2579
2580 IF (g_fnd_debug = 'Y') THEN
2581 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
2582 FND_LOG.string
2583 ( log_level => FND_LOG.LEVEL_PROCEDURE,
2584 module => l_module || l_progress,
2585 message => 'Quitting ' || g_pkg_name || '.' || l_api_name
2586 );
2587 END IF;
2588 END IF;
2589
2590 EXCEPTION
2591 WHEN OTHERS THEN
2592 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2593 FND_MSG_PUB.add_exc_msg
2594 ( p_pkg_name => g_pkg_name,
2595 p_procedure_name => l_api_name || '.' || l_progress
2596 );
2597
2598 END delete_contract_terms;
2599
2600
2601 -----------------------------------------------------------------------
2602 --Start of Comments
2603 --Name: delete_price_differentials
2604 --Pre-reqs:
2605 --Modifies:
2606 --Locks:
2607 -- None
2608 --Function: Remove records from po_price_differentials when documetns they
2609 -- associate with are getting purged
2610 --Parameters:
2611 --IN:
2612 --p_purge_entity
2613 -- REQ: purge price differentials associated to REQs
2614 -- PO: purge price differentials associated to POs
2615 --p_range_low
2616 -- lower bound of the req/po to be purged
2617 --p_range_high
2618 -- upper bound of the req/po to be purged
2619 --IN OUT:
2620 --OUT:
2621 --x_return_status
2622 -- status of the procedure
2623 --Returns:
2624 --Notes:
2625 --Testing:
2626 --End of Comments
2627 ------------------------------------------------------------------------
2628
2629 PROCEDURE delete_price_differentials
2630 ( x_return_status OUT NOCOPY VARCHAR2,
2631 p_purge_entity IN VARCHAR2,
2632 p_range_low IN NUMBER,
2633 p_range_high IN NUMBER
2634 ) IS
2635
2636 l_api_name VARCHAR2(50) := 'delete_price_differentials';
2637 l_progress VARCHAR2(3);
2638 l_return_status VARCHAR2(1);
2639 l_module CONSTANT FND_LOG_MESSAGES.module%TYPE :=
2640 G_MODULE_PREFIX || l_api_name || '.';
2641
2642 BEGIN
2643
2644 l_progress := '000';
2645
2646 IF (g_fnd_debug = 'Y') THEN
2647 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
2648 FND_LOG.string
2649 ( log_level => FND_LOG.LEVEL_PROCEDURE,
2650 module => l_module || l_progress,
2651 message => 'Entering ' || g_pkg_name || '.' || l_api_name
2652 );
2653 END IF;
2654 END IF;
2655
2656 x_return_status := FND_API.G_RET_STS_SUCCESS;
2657
2658 IF (p_purge_entity = 'REQ') THEN
2659 l_progress := '010';
2660
2661 DELETE
2662 FROM po_price_differentials PPD
2663 WHERE PPD.entity_type = 'REQ LINE'
2664 AND EXISTS (
2665 SELECT NULL
2666 FROM po_purge_req_list PPRL,
2667 po_requisition_lines RL
2668 WHERE PPRL.requisition_header_id =
2669 RL.requisition_header_id
2670 AND RL.requisition_line_id = PPD.entity_id
2671 AND PPRL.double_check_flag = 'Y'
2672 AND PPRL.requisition_header_id BETWEEN p_range_low
2673 AND p_range_high);
2674
2675 IF (g_fnd_debug = 'Y') THEN
2676 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2677 FND_LOG.string
2678 ( log_level => FND_LOG.LEVEL_STATEMENT,
2679 module => l_module || l_progress,
2680 message => 'Deleted PPD rowcount = ' || SQL%ROWCOUNT
2681 );
2682 END IF;
2683 END IF;
2684
2685 ELSIF (p_purge_entity = 'PO') THEN
2686 l_progress := '020';
2687
2688 DELETE
2689 FROM po_price_differentials PPD
2690 WHERE PPD.entity_type IN ('PO LINE', 'BLANKET LINE')
2691 AND EXISTS (
2692 SELECT NULL
2693 FROM po_purge_po_list PPL,
2694 po_lines POL
2695 WHERE PPL.po_header_id = POL.po_header_id
2696 AND POL.po_line_id = PPD.entity_id
2697 AND PPL.double_check_flag = 'Y'
2698 AND PPL.po_header_id BETWEEN p_range_low
2699 AND p_range_high);
2700
2701 IF (g_fnd_debug = 'Y') THEN
2702 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2703 FND_LOG.string
2704 ( log_level => FND_LOG.LEVEL_STATEMENT,
2705 module => l_module || l_progress,
2706 message => 'Deleted PPD rowcount = ' || SQL%ROWCOUNT
2707 );
2708 END IF;
2709 END IF;
2710
2711 l_progress := '030';
2712
2713 DELETE
2714 FROM po_price_differentials_archive PPD
2715 WHERE PPD.entity_type IN ('PO LINE', 'BLANKET LINE')
2716 AND EXISTS (
2717 SELECT NULL
2718 FROM po_purge_po_list PPL,
2719 po_lines POL
2720 WHERE PPL.po_header_id = POL.po_header_id
2721 AND POL.po_line_id = PPD.entity_id
2722 AND PPL.double_check_flag = 'Y'
2723 AND PPL.po_header_id BETWEEN p_range_low
2724 AND p_range_high);
2725
2726 IF (g_fnd_debug = 'Y') THEN
2727 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2728 FND_LOG.string
2729 ( log_level => FND_LOG.LEVEL_STATEMENT,
2730 module => l_module || l_progress,
2731 message => 'Deleted PPDA rowcount = ' || SQL%ROWCOUNT
2732 );
2733 END IF;
2734 END IF;
2735
2736 l_progress := '040';
2737
2738 DELETE
2739 FROM po_price_differentials PPD
2740 WHERE PPD.entity_type = 'PRICE BREAK'
2741 AND EXISTS (
2742 SELECT NULL
2743 FROM po_purge_po_list PPL,
2744 po_line_locations PLL
2745 WHERE PPL.po_header_id = PLL.po_header_id
2746 AND PLL.line_location_id = PPD.entity_id
2747 AND PPL.double_check_flag = 'Y'
2748 AND PPL.po_header_id BETWEEN p_range_low
2749 AND p_range_high);
2750
2751 IF (g_fnd_debug = 'Y') THEN
2752 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2753 FND_LOG.string
2754 ( log_level => FND_LOG.LEVEL_STATEMENT,
2755 module => l_module || l_progress,
2756 message => 'Deleted PPD rowcount = ' || SQL%ROWCOUNT
2757 );
2758 END IF;
2759 END IF;
2760
2761 l_progress := '050';
2762
2763 DELETE
2764 FROM po_price_differentials_archive PPD
2765 WHERE PPD.entity_type = 'PRICE BREAK'
2766 AND EXISTS (
2767 SELECT NULL
2768 FROM po_purge_po_list PPL,
2769 po_line_locations PLL
2770 WHERE PPL.po_header_id = PLL.po_header_id
2771 AND PLL.line_location_id = PPD.entity_id
2772 AND PPL.double_check_flag = 'Y'
2773 AND PPL.po_header_id BETWEEN p_range_low
2774 AND p_range_high);
2775
2776 IF (g_fnd_debug = 'Y') THEN
2777 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2778 FND_LOG.string
2779 ( log_level => FND_LOG.LEVEL_STATEMENT,
2780 module => l_module || l_progress,
2781 message => 'Deleted PPDA rowcount = ' || SQL%ROWCOUNT
2782 );
2783 END IF;
2784 END IF;
2785
2786 END IF; -- p_purge_category = 'REQ'
2787
2788 IF (g_fnd_debug = 'Y') THEN
2789 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
2790 FND_LOG.string
2791 ( log_level => FND_LOG.LEVEL_PROCEDURE,
2792 module => l_module || l_progress,
2793 message => 'Quitting ' || g_pkg_name || '.' || l_api_name
2794 );
2795 END IF;
2796 END IF;
2797
2798 EXCEPTION
2799 WHEN OTHERS THEN
2800 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2801 FND_MSG_PUB.add_exc_msg
2802 ( p_pkg_name => g_pkg_name,
2803 p_procedure_name => l_api_name || '.' || l_progress
2804 );
2805
2806 END delete_price_differentials;
2807
2808
2809 -----------------------------------------------------------------------
2810 --Start of Comments
2811 --Name: delete_attr_values
2812 --Pre-reqs:
2813 --Modifies:
2814 --Locks:
2815 -- None
2816 --Function: Remove records from po_attribute_values and
2817 -- po_attribute_values_tlp when documetns they
2818 -- associate with are getting purged
2819 --Parameters:
2820 --IN:
2821 --p_range_low
2822 -- lower bound of the po to be purged
2823 --p_range_high
2824 -- upper bound of the po to be purged
2825 --IN OUT:
2826 --OUT:
2827 --x_return_status
2828 -- status of the procedure
2829 --Returns:
2830 --Notes:
2831 --Testing:
2832 --End of Comments
2833 ------------------------------------------------------------------------
2834
2835 PROCEDURE delete_attr_values
2836 ( x_return_status OUT NOCOPY VARCHAR2,
2837 p_range_low IN NUMBER,
2838 p_range_high IN NUMBER
2839 ) IS
2840
2841 l_api_name VARCHAR2(50) := 'delete_attr_values';
2842 l_progress VARCHAR2(3);
2843 l_return_status VARCHAR2(1);
2844 l_module CONSTANT FND_LOG_MESSAGES.module%TYPE :=
2845 G_MODULE_PREFIX || l_api_name || '.';
2846
2847 BEGIN
2848
2849 l_progress := '000';
2850
2851 IF (g_fnd_debug = 'Y') THEN
2852 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
2853 FND_LOG.string
2854 ( log_level => FND_LOG.LEVEL_PROCEDURE,
2855 module => l_module || l_progress,
2856 message => 'Entering ' || g_pkg_name || '.' || l_api_name
2857 );
2858 END IF;
2859 END IF;
2860
2861 x_return_status := FND_API.G_RET_STS_SUCCESS;
2862
2863 l_progress := '010';
2864
2865 DELETE
2866 FROM po_price_differentials PPD
2867 WHERE PPD.entity_type IN ('PO LINE', 'BLANKET LINE')
2868 AND EXISTS (
2869 SELECT NULL
2870 FROM po_purge_po_list PPL,
2871 po_lines POL
2872 WHERE PPL.po_header_id = POL.po_header_id
2873 AND POL.po_line_id = PPD.entity_id
2874 AND PPL.double_check_flag = 'Y'
2875 AND PPL.po_header_id BETWEEN p_range_low
2876 AND p_range_high);
2877
2878 IF (g_fnd_debug = 'Y') THEN
2879 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2880 FND_LOG.string
2881 ( log_level => FND_LOG.LEVEL_STATEMENT,
2882 module => l_module || l_progress,
2883 message => 'Deleted PPD rowcount = ' || SQL%ROWCOUNT
2884 );
2885 END IF;
2886 END IF;
2887
2888 l_progress := '020';
2889
2890 DELETE
2891 FROM po_attribute_values PAV
2892 WHERE PAV.po_line_id IN
2893 ( SELECT po_line_id
2894 FROM po_purge_po_list PPL,
2895 po_lines POL
2896 WHERE PPL.po_header_id = POL.po_header_id
2897 AND PPL.double_check_flag = 'Y'
2898 AND PPL.po_header_id BETWEEN p_range_low
2899 AND p_range_high);
2900
2901 IF (g_fnd_debug = 'Y') THEN
2902 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2903 FND_LOG.string
2904 ( log_level => FND_LOG.LEVEL_STATEMENT,
2905 module => l_module || l_progress,
2906 message => 'Deleted PAV rowcount = ' || SQL%ROWCOUNT
2907 );
2908 END IF;
2909 END IF;
2910
2911 l_progress := '030';
2912
2913 DELETE
2914 FROM po_attribute_values_tlp PAVT
2915 WHERE PAVT.po_line_id IN
2916 ( SELECT po_line_id
2917 FROM po_purge_po_list PPL,
2918 po_lines POL
2919 WHERE PPL.po_header_id = POL.po_header_id
2920 AND PPL.double_check_flag = 'Y'
2921 AND PPL.po_header_id BETWEEN p_range_low
2922 AND p_range_high);
2923
2924 IF (g_fnd_debug = 'Y') THEN
2925 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2926 FND_LOG.string
2927 ( log_level => FND_LOG.LEVEL_STATEMENT,
2928 module => l_module || l_progress,
2929 message => 'Deleted PAVT rowcount = ' || SQL%ROWCOUNT
2930 );
2931 END IF;
2932 END IF;
2933
2934 IF (g_fnd_debug = 'Y') THEN
2935 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
2936 FND_LOG.string
2937 ( log_level => FND_LOG.LEVEL_PROCEDURE,
2938 module => l_module || l_progress,
2939 message => 'Quitting ' || g_pkg_name || '.' || l_api_name
2940 );
2941 END IF;
2942 END IF;
2943
2944 EXCEPTION
2945 WHEN OTHERS THEN
2946 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2947 FND_MSG_PUB.add_exc_msg
2948 ( p_pkg_name => g_pkg_name,
2949 p_procedure_name => l_api_name || '.' || l_progress
2950 );
2951
2952 END delete_attr_values;
2953
2954
2955 -----------------------------------------------------------------------
2956 --Start of Comments
2957 --Name: delete_req_attachments
2958 --Pre-reqs:
2959 --Modifies:
2960 -- FND Attachments related tables
2961 --Locks:
2962 -- None
2963 --Function: Delete all the attachments for the reqs in the purge list that
2964 -- fall within the range
2965 --Parameters:
2966 --IN:
2967 --p_range_low
2968 -- lower bound of the req/po to be purged
2969 --p_range_high
2970 -- upper bound of the req/po to be purged
2971 --IN OUT:
2972 --OUT:
2973 --x_return_status
2974 -- status of the procedure
2975 --Returns:
2976 --Notes:
2977 --Testing:
2978 --End of Comments
2979 ------------------------------------------------------------------------
2980
2981 PROCEDURE delete_req_attachments
2982 ( x_return_status OUT NOCOPY VARCHAR2,
2983 p_range_low IN NUMBER,
2984 p_range_high IN NUMBER
2985 ) IS
2986
2987 l_api_name VARCHAR2(50) := 'delete_req_attachments';
2988 l_progress VARCHAR2(3);
2989 l_module CONSTANT FND_LOG_MESSAGES.module%TYPE :=
2990 G_MODULE_PREFIX || l_api_name || '.';
2991
2992 CURSOR c_req_header IS
2993 SELECT PPRL.requisition_header_id
2994 FROM po_purge_req_list PPRL
2995 WHERE PPRL.requisition_header_id BETWEEN p_range_low AND p_range_high
2996 AND PPRL.double_check_flag = 'Y';
2997
2998 CURSOR c_req_line IS
2999 SELECT PRL.requisition_line_id
3000 FROM po_purge_req_list PPRL,
3001 po_requisition_lines PRL
3002 WHERE PPRL.requisition_header_id BETWEEN p_range_low AND p_range_high
3003 AND PPRL.double_check_flag = 'Y'
3004 AND PPRL.requisition_header_id = PRL.requisition_header_id;
3005
3006 BEGIN
3007
3008 l_progress := '000';
3009
3010 IF (g_fnd_debug = 'Y') THEN
3011 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
3012 FND_LOG.string
3013 ( log_level => FND_LOG.LEVEL_PROCEDURE,
3014 module => l_module || l_progress,
3015 message => 'Entering ' || g_pkg_name || '.' || l_api_name
3016 );
3017 END IF;
3018 END IF;
3019
3020 x_return_status := FND_API.G_RET_STS_SUCCESS;
3021
3022 -- Delete Req Header Attachments
3023
3024 FOR rec IN c_req_header LOOP
3025 l_progress := '010';
3026
3027 FND_ATTACHED_DOCUMENTS2_PKG.delete_attachments
3028 ( x_entity_name => 'REQ_HEADERS',
3029 x_pk1_value => rec.requisition_header_id,
3030 x_delete_document_flag => 'Y'
3031 );
3032
3033 END LOOP;
3034
3035 l_progress := '020';
3036
3037 -- Delete Req Line Attachments
3038
3039 FOR rec IN c_req_line LOOP
3040 l_progress := '030';
3041
3042 FND_ATTACHED_DOCUMENTS2_PKG.delete_attachments
3043 ( x_entity_name => 'REQ_LINES',
3044 x_pk1_value => rec.requisition_line_id,
3045 x_delete_document_flag => 'Y'
3046 );
3047
3048 END LOOP;
3049
3050 IF (g_fnd_debug = 'Y') THEN
3051 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
3052 FND_LOG.string
3053 ( log_level => FND_LOG.LEVEL_PROCEDURE,
3054 module => l_module || l_progress,
3055 message => 'Quitting ' || g_pkg_name || '.' || l_api_name
3056 );
3057 END IF;
3058 END IF;
3059
3060 EXCEPTION
3061 WHEN OTHERS THEN
3062 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3063
3064 IF (c_req_header%ISOPEN) THEN
3065 CLOSE c_req_header;
3066 END IF;
3067
3068 IF (c_req_line%ISOPEN) THEN
3069 CLOSE c_req_line;
3070 END IF;
3071
3072 FND_MSG_PUB.add_exc_msg
3073 ( p_pkg_name => g_pkg_name,
3074 p_procedure_name => l_api_name || '.' || l_progress
3075 );
3076
3077 END delete_req_attachments;
3078
3079
3080 -----------------------------------------------------------------------
3081 --Start of Comments
3082 --Name: delete_po_attachments
3083 --Pre-reqs:
3084 --Modifies:
3085 -- FND Attachments related tables
3086 --Locks:
3087 -- None
3088 --Function: Delete all the attachments for the pos (including releases) in the
3089 -- purge list that fall within the range
3090 --Parameters:
3091 --IN:
3092 --p_range_low
3093 -- lower bound of the req/po to be purged
3094 --p_range_high
3095 -- upper bound of the req/po to be purged
3096 --IN OUT:
3097 --OUT:
3098 --x_return_status
3099 -- status of the procedure
3100 --Returns:
3101 --Notes:
3102 --Testing:
3103 --End of Comments
3104 ------------------------------------------------------------------------
3105
3106 PROCEDURE delete_po_attachments
3107 ( x_return_status OUT NOCOPY VARCHAR2,
3108 p_range_low IN NUMBER,
3109 p_range_high IN NUMBER
3110 ) IS
3111
3112 l_api_name VARCHAR2(50) := 'delete_po_attachments';
3113 l_progress VARCHAR2(3);
3114 l_module CONSTANT FND_LOG_MESSAGES.module%TYPE :=
3115 G_MODULE_PREFIX || l_api_name || '.';
3116
3117 CURSOR c_po_header IS
3118 SELECT PPL.po_header_id
3119 FROM po_purge_po_list PPL
3120 WHERE PPL.po_header_id BETWEEN p_range_low AND p_range_high
3121 AND PPL.double_check_flag = 'Y';
3122
3123 CURSOR c_po_release IS
3124 SELECT PR.po_release_id
3125 FROM po_purge_po_list PPL,
3126 po_releases PR
3127 WHERE PPL.po_header_id BETWEEN p_range_low AND p_range_high
3128 AND PPL.double_check_flag = 'Y'
3129 AND PPL.po_header_id = PR.po_header_id;
3130
3131 CURSOR c_po_line IS
3132 SELECT POL.po_line_id
3133 FROM po_purge_po_list PPL,
3134 po_lines POL
3135 WHERE PPL.po_header_id BETWEEN p_range_low AND p_range_high
3136 AND PPL.double_check_flag = 'Y'
3137 AND PPL.po_header_id = POL.po_header_id;
3138
3139 CURSOR c_po_shipment IS
3140 SELECT POLL.line_location_id
3141 FROM po_purge_po_list PPL,
3142 po_line_locations POLL
3143 WHERE PPL.po_header_id BETWEEN p_range_low AND p_range_high
3144 AND PPL.double_check_flag = 'Y'
3145 AND PPL.po_header_id = POLL.po_header_id;
3146
3147 BEGIN
3148
3149 l_progress := '000';
3150
3151 IF (g_fnd_debug = 'Y') THEN
3152 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
3153 FND_LOG.string
3154 ( log_level => FND_LOG.LEVEL_PROCEDURE,
3155 module => l_module || l_progress,
3156 message => 'Entering ' || g_pkg_name || '.' || l_api_name
3157 );
3158 END IF;
3159 END IF;
3160
3161 x_return_status := FND_API.G_RET_STS_SUCCESS;
3162
3163 -- Delete po header attachments
3164
3165 FOR rec IN c_po_header LOOP
3166 l_progress := '010';
3167
3168 FND_ATTACHED_DOCUMENTS2_PKG.delete_attachments
3169 ( x_entity_name => 'PO_HEADERS',
3170 x_pk1_value => rec.po_header_id,
3171 x_delete_document_flag => 'Y'
3172 );
3173
3174 -- Delete PDF doc for the PO
3175
3176 FND_ATTACHED_DOCUMENTS2_PKG.delete_attachments
3177 ( x_entity_name => 'PO_HEAD',
3178 x_pk1_value => rec.po_header_id,
3179 x_delete_document_flag => 'Y'
3180 );
3181
3182 END LOOP;
3183
3184 l_progress := '020';
3185
3186 -- Delete po releases attachments
3187
3188 FOR rec IN c_po_release LOOP
3189 l_progress := '030';
3190
3191 FND_ATTACHED_DOCUMENTS2_PKG.delete_attachments
3192 ( x_entity_name => 'PO_RELEASES',
3193 x_pk1_value => rec.po_release_id,
3194 x_delete_document_flag => 'Y'
3195 );
3196
3197 -- Delete PDF doc for the release
3198
3199 FND_ATTACHED_DOCUMENTS2_PKG.delete_attachments
3200 ( x_entity_name => 'PO_REL',
3201 x_pk1_value => rec.po_release_id,
3202 x_delete_document_flag => 'Y'
3203 );
3204
3205 END LOOP;
3206
3207 l_progress := '040';
3208
3209 -- Delete po line attachments
3210
3211 FOR rec IN c_po_line LOOP
3212 l_progress := '050';
3213
3214 -- If a GA line contains attachements, the references for these
3215 -- attachments will be under entity PO_LINES and PO_IN_GA_LINES.
3216 -- When deleting the reference in PO_IN_GA_LINES, we do not need to
3217 -- remove the actual attachments as they will be taken care of when
3218 -- we delete reference under entity PO_LINES.
3219
3220 FND_ATTACHED_DOCUMENTS2_PKG.delete_attachments
3221 ( x_entity_name => 'PO_IN_GA_LINES',
3222 x_pk1_value => rec.po_line_id,
3223 x_delete_document_flag => 'N',
3224 x_automatically_added_flag=> 'Y'
3225 );
3226
3227 FND_ATTACHED_DOCUMENTS2_PKG.delete_attachments
3228 ( x_entity_name => 'PO_LINES',
3229 x_pk1_value => rec.po_line_id,
3230 x_delete_document_flag => 'Y'
3231 );
3232
3233 END LOOP;
3234
3235 l_progress := '060';
3236
3237 -- Delete po shipment attachments
3238
3239 FOR rec IN c_po_shipment LOOP
3240 l_progress := '070';
3241
3242 FND_ATTACHED_DOCUMENTS2_PKG.delete_attachments
3243 ( x_entity_name => 'PO_SHIPMENTS',
3244 x_pk1_value => rec.line_location_id,
3245 x_delete_document_flag => 'Y'
3246 );
3247
3248 END LOOP;
3249
3250 IF (g_fnd_debug = 'Y') THEN
3251 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
3252 FND_LOG.string
3253 ( log_level => FND_LOG.LEVEL_PROCEDURE,
3254 module => l_module || l_progress,
3255 message => 'Quitting ' || g_pkg_name || '.' || l_api_name
3256 );
3257 END IF;
3258 END IF;
3259
3260 EXCEPTION
3261 WHEN OTHERS THEN
3262 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3263
3264 IF (c_po_header%ISOPEN) THEN
3265 CLOSE c_po_header;
3266 END IF;
3267
3268 IF (c_po_release%ISOPEN) THEN
3269 CLOSE c_po_release;
3270 END IF;
3271
3272 IF (c_po_line%ISOPEN) THEN
3273 CLOSE c_po_line;
3274 END IF;
3275
3276 IF (c_po_shipment%ISOPEN) THEN
3277 CLOSE c_po_shipment;
3278 END IF;
3279
3280 FND_MSG_PUB.add_exc_msg
3281 ( p_pkg_name => g_pkg_name,
3282 p_procedure_name => l_api_name || '.' || l_progress
3283 );
3284
3285 END delete_po_attachments;
3286
3287 -- <HTML Agreement R12 START>
3288
3289 -----------------------------------------------------------------------
3290 --Start of Comments
3291 --Name: delete_po_drafts
3292 --Pre-reqs:
3293 --Modifies:
3294 --
3295 --Locks:
3296 -- None
3297 --Function: Delete all existing drafts for the documents from draft tables
3298 --Parameters:
3299 --IN:
3300 --p_range_low
3301 -- lower bound of the req/po to be purged
3302 --p_range_high
3303 -- upper bound of the req/po to be purged
3304 --IN OUT:
3305 --OUT:
3306 --x_return_status
3307 -- status of the procedure
3308 --Returns:
3309 --Notes:
3310 --Testing:
3311 --End of Comments
3312 ------------------------------------------------------------------------
3313
3314 PROCEDURE delete_po_drafts
3315 ( x_return_status OUT NOCOPY VARCHAR2,
3316 p_range_low IN NUMBER,
3317 p_range_high IN NUMBER
3318 ) IS
3319
3320 l_api_name VARCHAR2(50) := 'delete_drafts';
3321 l_progress VARCHAR2(3);
3322 l_module CONSTANT FND_LOG_MESSAGES.module%TYPE :=
3323 G_MODULE_PREFIX || l_api_name || '.';
3324
3325 l_dft_id_tbl PO_TBL_NUMBER;
3326
3327 BEGIN
3328
3329 l_progress := '000';
3330
3331 IF (g_fnd_debug = 'Y') THEN
3332 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
3333 FND_LOG.string
3334 ( log_level => FND_LOG.LEVEL_PROCEDURE,
3335 module => l_module || l_progress,
3336 message => 'Entering ' || g_pkg_name || '.' || l_api_name
3337 );
3338 END IF;
3339 END IF;
3340
3341 x_return_status := FND_API.G_RET_STS_SUCCESS;
3342
3343 SELECT DFT.draft_id
3344 BULK COLLECT
3345 INTO l_dft_id_tbl
3346 FROM po_drafts DFT,
3347 po_purge_po_list PPL
3348 WHERE DFT.document_id = PPL.po_header_id
3349 AND PPL.po_header_id BETWEEN p_range_low AND p_range_high
3350 AND PPL.double_check_flag = 'Y';
3351
3352 -- Delete all drafts for documents in purge list
3353 FOR i IN 1..l_dft_id_tbl.COUNT LOOP
3354 PO_DRAFTS_PVT.remove_draft_changes
3355 ( p_draft_id => l_dft_id_tbl(i),
3356 p_exclude_ctrl_tbl => FND_API.G_FALSE,
3357 x_return_status => x_return_status
3358 );
3359 END LOOP;
3360
3361 IF (g_fnd_debug = 'Y') THEN
3362 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
3363 FND_LOG.string
3364 ( log_level => FND_LOG.LEVEL_PROCEDURE,
3365 module => l_module || l_progress,
3366 message => 'Quitting ' || g_pkg_name || '.' || l_api_name
3367 );
3368 END IF;
3369 END IF;
3370
3371 EXCEPTION
3372 WHEN OTHERS THEN
3373 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3374
3375 FND_MSG_PUB.add_exc_msg
3376 ( p_pkg_name => g_pkg_name,
3377 p_procedure_name => l_api_name || '.' || l_progress
3378 );
3379
3380 END delete_po_drafts;
3381
3382 -- <HTML Agreement R12 END>
3383
3384 --<bug3231186 START>
3385
3386 -----------------------------------------------------------------------
3387 --Start of Comments
3388 --Name: delete_po_approval_list
3389 --Pre-reqs:
3390 --Modifies:
3391 -- FND Attachments related tables
3392 --Locks:
3393 -- None
3394 --Function: Delete records in PO_APPROVAL_LIST_LINES and
3395 -- PO_APPROVAL_LIST_HEADERS where reqs are purged
3396 --Parameters:
3397 --IN:
3398 --p_purge_entity
3399 -- Currently only 'REQ' is supported
3400 --p_range_low
3401 -- lower bound of the req to be purged
3402 --p_range_high
3403 -- upper bound of the req to be purged
3404 --IN OUT:
3405 --OUT:
3406 --x_return_status
3407 -- status of the procedure
3408 --Returns:
3409 --Notes:
3410 --Testing:
3411 --End of Comments
3412 ------------------------------------------------------------------------
3413
3414 PROCEDURE delete_po_approval_list
3415 ( x_return_status OUT NOCOPY VARCHAR2,
3416 p_purge_entity IN VARCHAR2,
3417 p_range_low IN NUMBER,
3418 p_range_high IN NUMBER
3419 ) IS
3420
3421 l_api_name VARCHAR2(50) := 'delete_po_approval_list';
3422 l_progress VARCHAR2(3);
3423 l_module CONSTANT FND_LOG_MESSAGES.module%TYPE :=
3424 G_MODULE_PREFIX || l_api_name || '.';
3425 BEGIN
3426
3427 l_progress := '000';
3428
3429 IF (g_fnd_debug = 'Y') THEN
3430 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
3431 FND_LOG.string
3432 ( log_level => FND_LOG.LEVEL_PROCEDURE,
3433 module => l_module || l_progress,
3434 message => 'Entering ' || g_pkg_name || '.' || l_api_name
3435 );
3436 END IF;
3437 END IF;
3438
3439 x_return_status := FND_API.G_RET_STS_SUCCESS;
3440
3441 IF (p_purge_entity = 'REQ') THEN
3442 l_progress := '010';
3443
3444 --SQL What: Delete PO_APPROVAL_LIST_LINES for the REQS being purged
3445 --SQL Why: These records should get purged together with the reqs
3446 DELETE
3447 FROM po_approval_list_lines PALL
3448 WHERE PALL.approval_list_header_id
3449 IN ( SELECT PALH.approval_list_header_id
3450 FROM po_approval_list_headers PALH,
3451 po_purge_req_list PPRL
3452 WHERE PPRL.requisition_header_id =
3453 PALH.document_id
3454 AND PPRL.double_check_flag = 'Y'
3455 AND PPRL.requisition_header_id BETWEEN p_range_low
3456 AND p_range_high
3457 AND PALH.document_type = 'REQUISITION'
3458 );
3459
3460 IF (g_fnd_debug = 'Y') THEN
3461 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3462 FND_LOG.string
3463 ( log_level => FND_LOG.LEVEL_STATEMENT,
3464 module => l_module || l_progress,
3465 message => 'Deleted po_appr_lines rowcount = ' || SQL%ROWCOUNT
3466 );
3467 END IF;
3468 END IF;
3469
3470 l_progress := '020';
3471
3472
3473 --SQL What: Delete PO_APPROVAL_LIST_HEADERS for the REQS being purged
3474 --SQL Why: These records should get purged together with the reqs
3475 DELETE
3476 FROM po_approval_list_headers PALH
3477 WHERE PALH.document_type = 'REQUISITION'
3478 AND PALH.document_id
3479 IN ( SELECT PPRL.requisition_header_id
3480 FROM po_purge_req_list PPRL
3481 WHERE PPRL.double_check_flag = 'Y'
3482 AND PPRL.requisition_header_Id BETWEEN p_range_low
3483 AND p_range_high
3484 );
3485
3486 IF (g_fnd_debug = 'Y') THEN
3487 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3488 FND_LOG.string
3489 ( log_level => FND_LOG.LEVEL_STATEMENT,
3490 module => l_module || l_progress,
3491 message => 'Deleted po_appr_hdrs rowcount = ' || SQL%ROWCOUNT
3492 );
3493 END IF;
3494 END IF;
3495 END IF;
3496
3497 IF (g_fnd_debug = 'Y') THEN
3498 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
3499 FND_LOG.string
3500 ( log_level => FND_LOG.LEVEL_PROCEDURE,
3501 module => l_module || l_progress,
3502 message => 'Quitting ' || g_pkg_name || '.' || l_api_name
3503 );
3504 END IF;
3505 END IF;
3506
3507 EXCEPTION
3508 WHEN OTHERS THEN
3509 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3510 FND_MSG_PUB.add_exc_msg
3511 ( p_pkg_name => g_pkg_name,
3512 p_procedure_name => l_api_name || '.' || l_progress
3513 );
3514 END delete_po_approval_list;
3515
3516 --<bug3231186 END>
3517
3518 -----------------------------------------------------------------------
3519 --Start of Comments
3520 --Name: log_purge_list_count
3521 --Pre-reqs:
3522 --Modifies:
3523 -- None
3524 --Locks:
3525 -- None
3526 --Function: Put the record count of the purge table into FND_LOG_MESSAGES,
3527 -- if logging is turned on
3528 --Parameters:
3529 --IN:
3530 --p_module
3531 -- the location where this is called from.
3532 --p_entity
3533 -- the table to report. 'REQ' for PO_PURGE_REQ_LIST
3534 -- 'PO' for PO_PURGE_PO_LIST
3535 --IN OUT:
3536 --OUT:
3537 --Returns:
3538 --Notes:
3539 -- This procedure will not return any error
3540 --Testing:
3541 --End of Comments
3542 ------------------------------------------------------------------------
3543
3544 PROCEDURE log_purge_list_count
3545 ( p_module IN VARCHAR2,
3546 p_entity IN VARCHAR2
3547 ) IS
3548
3549 l_count NUMBER;
3550
3551 BEGIN
3552
3553 IF (g_fnd_debug = 'Y') THEN
3554 IF (p_entity = 'REQ') THEN
3555
3556 SELECT COUNT(*)
3557 INTO l_count
3558 FROM po_purge_req_list
3559 WHERE double_check_flag = 'Y';
3560
3561 ELSIF (p_entity = 'PO') THEN
3562
3563 SELECT COUNT(*)
3564 INTO l_count
3565 FROM po_purge_po_list
3566 WHERE double_check_flag = 'Y';
3567
3568 END IF;
3569 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3570 FND_LOG.string
3571 ( log_level => FND_LOG.LEVEL_STATEMENT,
3572 module => p_module,
3573 message => p_entity || ' purge list count = ' || l_count
3574 );
3575 END IF;
3576
3577 END IF;
3578
3579 EXCEPTION
3580 WHEN OTHERS THEN
3581 NULL;
3582 END log_purge_list_count;
3583
3584 -- <DOC PURGE FPJ END>
3585
3586 END PO_AP_PURGE_UTIL_PVT;