[Home] [Help]
PACKAGE BODY: APPS.PO_DOCUMENT_ARCHIVE_PVT
Source
1 PACKAGE BODY PO_DOCUMENT_ARCHIVE_PVT AS
2 /* $Header: POXPIARB.pls 120.10.12010000.3 2008/11/09 11:33:45 vdurbhak ship $ */
3
4 G_PKG_NAME CONSTANT varchar2(30) := 'PO_DOCUMENT_ARCHIVE_PVT';
5 G_MODULE_PREFIX CONSTANT VARCHAR2(60) := 'po.plsql.' || G_PKG_NAME || '.';
6 G_FND_DEBUG VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
7 G_FND_DEBUG_LEVEL VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_LEVEL'),'0');
8
9 D_PACKAGE_BASE CONSTANT VARCHAR2(50) := PO_LOG.get_package_base(G_PKG_NAME);
10 D_archive_attribute_values CONSTANT VARCHAR2(100) := PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'archive_attribute_values');
11 D_archive_attr_values_tlp CONSTANT VARCHAR2(100) := PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'archive_attr_values_tlp');
12
13 PROCEDURE archive_attribute_values
14 (
15 p_po_header_id IN NUMBER
16 , p_revision_num IN NUMBER
17 );
18
19 PROCEDURE archive_attr_values_tlp
20 (
21 p_po_header_id IN NUMBER
22 , p_revision_num IN NUMBER
23 );
24
25 -------------------------------------------------------------------------------
26 --Start of Comments
27 --Name: CHECK_PO_ARCHIVE
28 --Pre-reqs:
29 -- None.
30 --Modifies:
31 -- None.
32 --Locks:
33 -- None.
34 --Function:
35 -- Get the current revision number and check if it is already archived
36 --Parameters:
37 --IN:
38 --p_document_id
39 -- The id of the document that needs to be archived
40 --OUT:
41 --x_revision_num
42 -- The revision number of the PO
43 --x_return_status
44 -- 'Y' if archive needed
45 -- 'N' if archive not needed
46 --Testing:
47 -- None.
48 --End of Comments
49 -------------------------------------------------------------------------------
50 PROCEDURE CHECK_PO_ARCHIVE(p_document_id IN NUMBER,
51 x_revision_num OUT NOCOPY NUMBER,
52 x_return_status OUT NOCOPY VARCHAR2)
53 IS
54
55 l_api_name CONSTANT VARCHAR2(30) := 'CHECK_PO_ARCHIVE';
56 l_module VARCHAR2(100);
57 l_progress VARCHAR2(3);
58 l_arch_revision_num NUMBER;
59
60 BEGIN
61
62 l_progress := '000';
63 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
64 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_PROCEDURE THEN
65 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, l_module,
66 'Entering ' || G_PKG_NAME || '.' || l_api_name);
67 END IF;
68
69 l_progress := '010';
70 -- SQL What: Select current revision number and archived revision number
71 -- SQL Why : Check if the document is approved and not archived
72 SELECT NVL(ph.revision_num, 0),
73 NVL(pha.revision_num, -1)
74 INTO x_revision_num, l_arch_revision_num
75 FROM PO_HEADERS_ALL PH,
76 PO_HEADERS_ARCHIVE_ALL PHA
77 WHERE ph.po_header_id = p_document_id
78 AND ph.approved_date IS NOT NULL
79 AND ph.approved_flag = 'Y'
80 AND ph.po_header_id = pha.po_header_id (+)
81 AND pha.latest_external_flag(+) = 'Y';
82
83 l_progress := '020';
84 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
85 IF x_revision_num = l_arch_revision_num THEN
86 x_return_status := 'N';
87 ELSE
88 x_return_status := 'Y';
89 END IF; /*x_revision_num = l_arch_revision_num*/
90
91 EXCEPTION
92 WHEN OTHERS THEN
93 po_message_s.sql_error('Exception of CHECK_PO_ARCHIVE()',
94 l_progress , sqlcode);
95 FND_MSG_PUB.Add;
96 IF (G_FND_DEBUG = 'Y') THEN
97 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
98 FND_LOG.string(FND_LOG.level_unexpected, l_module,
99 'EXCEPTION: '||sqlerrm);
100 END IF;
101 END IF;
102 x_return_status := 'N';
103 END CHECK_PO_ARCHIVE;
104
105 -------------------------------------------------------------------------------
106 --Start of Comments
107 --Name: CHECK_RELEASE_ARCHIVE
108 --Pre-reqs:
109 -- None.
110 --Modifies:
111 -- None.
112 --Locks:
113 -- None.
114 --Function:
115 -- Get the current revision number and check if it is already archived
116 --Parameters:
117 --IN:
118 --p_document_id
119 -- The id of the document that needs to be archived
120 --OUT:
121 --x_revision_num
122 -- The revision number of the Release
123 --x_return_status
124 -- 'Y' if archive needed
125 -- 'N' if archive not needed
126 --Testing:
127 -- None.
128 --End of Comments
129 -------------------------------------------------------------------------------
130 PROCEDURE CHECK_RELEASE_ARCHIVE(p_document_id IN NUMBER,
131 x_revision_num OUT NOCOPY NUMBER,
132 x_return_status OUT NOCOPY VARCHAR2)
133 IS
134
135 l_api_name CONSTANT VARCHAR2(30) := 'CHECK_RELEASE_ARCHIVE';
136 l_module VARCHAR2(100);
137 l_progress VARCHAR2(3);
138 l_arch_revision_num NUMBER;
139
140 BEGIN
141
142 l_progress := '000';
143 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
144 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_PROCEDURE THEN
145 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, l_module,
146 'Entering ' || G_PKG_NAME || '.' || l_api_name);
147 END IF;
148
149 l_progress := '010';
150 -- SQL What: Select current revision number and archived revision number
151 -- SQL Why : Check if the document is approved and not archived
152 SELECT NVL(ph.revision_num, 0),
153 NVL(pha.revision_num, -1)
154 INTO x_revision_num, l_arch_revision_num
155 FROM PO_RELEASES_ALL PH,
156 PO_RELEASES_ARCHIVE_ALL PHA
157 WHERE ph.po_release_id = p_document_id
158 AND ph.approved_date IS NOT NULL
159 AND ph.approved_flag = 'Y'
160 AND ph.po_release_id = pha.po_release_id (+)
161 AND pha.latest_external_flag(+) = 'Y';
162
163 l_progress := '020';
164 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
165 IF x_revision_num = l_arch_revision_num THEN
166 x_return_status := 'N';
167 ELSE
168 x_return_status := 'Y';
169 END IF; /*x_revision_num = l_arch_revision_num*/
170
171 EXCEPTION
172 WHEN OTHERS THEN
173 po_message_s.sql_error('Exception of CHECK_RELEASE_ARCHIVE()',
174 l_progress , sqlcode);
175 FND_MSG_PUB.Add;
176 IF (G_FND_DEBUG = 'Y') THEN
177 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
178 FND_LOG.string(FND_LOG.level_unexpected, l_module,
179 'EXCEPTION: '||sqlerrm);
180 END IF;
181 END IF;
182 x_return_status := 'N';
183 END CHECK_RELEASE_ARCHIVE;
184
185 -------------------------------------------------------------------------------
186 --Start of Comments
187 --Name: ARCHIVE_HEADER
188 --Pre-reqs:
189 -- None.
190 --Modifies:
191 -- PO_HEADERS_ARCHIVE
192 --Locks:
193 -- None.
194 --Function:
195 -- Archive PO Header
196 --Parameters:
197 --IN:
198 --p_document_id
199 -- The id of the document that needs to be archived
200 --Testing:
201 -- None.
202 --End of Comments
203 -------------------------------------------------------------------------------
204 PROCEDURE ARCHIVE_HEADER(p_document_id IN NUMBER)
205 IS
206
207 l_api_name CONSTANT VARCHAR2(30) := 'ARCHIVE_HEADER';
208 l_module VARCHAR2(100);
209 l_progress VARCHAR2(3);
210
211 BEGIN
212
213 l_progress := '000';
214 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
215 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_PROCEDURE THEN
216 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, l_module,
217 'Entering ' || G_PKG_NAME || '.' || l_api_name);
218 END IF;
219
220 l_progress := '010';
221 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
222 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
223 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
224 'Update PO_HEADERS_ARCHIVE to reset latest_external_flag');
225 END IF;
226
227 UPDATE PO_HEADERS_ARCHIVE_ALL
228 SET latest_external_flag = 'N'
229 WHERE po_header_id = p_document_id
230 AND latest_external_flag = 'Y';
231
232 l_progress := '020';
233 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
234 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
235 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
236 'Insert PO_HEADERS_ARCHIVE ');
237 END IF;
238
239 INSERT INTO PO_HEADERS_ARCHIVE_ALL
240 (acceptance_due_date ,
241 acceptance_required_flag ,
242 agent_id ,
243 amount_limit ,
244 approval_required_flag ,
245 approved_date ,
246 approved_flag ,
247 attribute1 ,
248 attribute10 ,
249 attribute11 ,
250 attribute12 ,
251 attribute13 ,
252 attribute14 ,
253 attribute15 ,
254 attribute2 ,
255 attribute3 ,
256 attribute4 ,
257 attribute5 ,
258 attribute6 ,
259 attribute7 ,
260 attribute8 ,
261 attribute9 ,
262 attribute_category ,
263 authorization_status ,
264 bill_to_location_id ,
265 blanket_total_amount ,
266 cancel_flag ,
267 cbc_accounting_date ,
268 change_requested_by ,
269 change_summary ,
270 closed_code ,
271 closed_date ,
272 comments ,
273 confirming_order_flag ,
274 consigned_consumption_flag ,
275 consume_req_demand_flag ,
276 conterms_articles_upd_date ,
277 conterms_deliv_upd_date ,
278 conterms_exist_flag ,
279 created_by ,
280 creation_date ,
281 currency_code ,
282 edi_processed_flag ,
283 edi_processed_status ,
284 enabled_flag ,
285 encumbrance_required_flag ,
286 end_date ,
287 end_date_active ,
288 firm_date ,
289 firm_status_lookup_code ,
290 fob_lookup_code ,
291 freight_terms_lookup_code ,
292 from_header_id ,
293 from_type_lookup_code ,
294 frozen_flag ,
295 global_agreement_flag ,
296 global_attribute1 ,
297 global_attribute10 ,
298 global_attribute11 ,
299 global_attribute12 ,
300 global_attribute13 ,
301 global_attribute14 ,
302 global_attribute15 ,
303 global_attribute16 ,
304 global_attribute17 ,
305 global_attribute18 ,
306 global_attribute19 ,
307 global_attribute2 ,
308 global_attribute20 ,
309 global_attribute3 ,
310 global_attribute4 ,
311 global_attribute5 ,
312 global_attribute6 ,
313 global_attribute7 ,
314 global_attribute8 ,
315 global_attribute9 ,
316 global_attribute_category ,
317 government_context ,
318 interface_source_code ,
319 last_update_date ,
320 last_update_login ,
321 last_updated_by ,
322 min_release_amount ,
323 mrc_rate ,
324 mrc_rate_date ,
325 mrc_rate_type ,
326 note_to_authorizer ,
327 note_to_receiver ,
328 note_to_vendor ,
329 org_id ,
330 pay_on_code ,
331 pcard_id ,
332 pending_signature_flag ,
333 po_header_id ,
334 price_update_tolerance ,
335 print_count ,
336 printed_date ,
337 program_application_id ,
338 program_id ,
339 program_update_date ,
340 quotation_class_code ,
341 quote_type_lookup_code ,
342 quote_vendor_quote_number ,
343 quote_warning_delay ,
344 quote_warning_delay_unit ,
345 rate ,
346 rate_date ,
347 rate_type ,
348 reference_num ,
349 reply_date ,
350 reply_method_lookup_code ,
351 request_id ,
352 revised_date ,
353 revision_num ,
354 rfq_close_date ,
355 segment1 ,
356 segment2 ,
357 segment3 ,
358 segment4 ,
359 segment5 ,
360 ship_to_location_id ,
361 ship_via_lookup_code ,
362 shipping_control ,
363 start_date ,
364 start_date_active ,
365 status_lookup_code ,
366 summary_flag ,
367 supply_agreement_flag ,
368 terms_id ,
369 type_lookup_code ,
370 user_hold_flag ,
371 vendor_contact_id ,
372 vendor_id ,
373 vendor_order_num ,
374 vendor_site_id ,
375 wf_item_key ,
376 wf_item_type ,
377 xml_change_send_date ,
378 xml_flag ,
379 xml_send_date ,
380 latest_external_flag,
381 document_creation_method -- <DBI FPJ>
382 ,submit_date --<DBI Req Fulfillment 11.5.11>
383 ,style_id --<R12 STYLES PHASE II>
384 , created_language --<Unified Catalog R12>
385 , cpa_reference --<Unified Catalog R12>
386 , last_updated_program --<Unified Catalog R12>
387 ,pay_when_paid -- E and C ER
388 )
389 SELECT
390 acceptance_due_date ,
391 acceptance_required_flag ,
392 agent_id ,
393 amount_limit ,
394 approval_required_flag ,
395 approved_date ,
396 approved_flag ,
397 attribute1 ,
398 attribute10 ,
399 attribute11 ,
400 attribute12 ,
401 attribute13 ,
402 attribute14 ,
403 attribute15 ,
404 attribute2 ,
405 attribute3 ,
406 attribute4 ,
407 attribute5 ,
408 attribute6 ,
409 attribute7 ,
410 attribute8 ,
411 attribute9 ,
412 attribute_category ,
413 authorization_status ,
414 bill_to_location_id ,
415 blanket_total_amount ,
416 cancel_flag ,
417 cbc_accounting_date ,
418 change_requested_by ,
419 change_summary ,
420 closed_code ,
421 closed_date ,
422 comments ,
423 confirming_order_flag ,
424 consigned_consumption_flag ,
425 consume_req_demand_flag ,
426 conterms_articles_upd_date ,
427 conterms_deliv_upd_date ,
428 conterms_exist_flag ,
429 created_by ,
430 creation_date ,
431 currency_code ,
432 -- Bug 3438383, EDI Team expects EDI columns NULL
433 -- edi_processed_flag ,
434 -- edi_processed_status ,
435 NULL ,
436 NULL ,
437 enabled_flag ,
438 encumbrance_required_flag ,
439 end_date ,
440 end_date_active ,
441 firm_date ,
442 firm_status_lookup_code ,
443 fob_lookup_code ,
444 freight_terms_lookup_code ,
445 from_header_id ,
446 from_type_lookup_code ,
447 frozen_flag ,
448 global_agreement_flag ,
449 global_attribute1 ,
450 global_attribute10 ,
451 global_attribute11 ,
452 global_attribute12 ,
453 global_attribute13 ,
454 global_attribute14 ,
455 global_attribute15 ,
456 global_attribute16 ,
457 global_attribute17 ,
458 global_attribute18 ,
459 global_attribute19 ,
460 global_attribute2 ,
461 global_attribute20 ,
462 global_attribute3 ,
463 global_attribute4 ,
464 global_attribute5 ,
465 global_attribute6 ,
466 global_attribute7 ,
467 global_attribute8 ,
468 global_attribute9 ,
469 global_attribute_category ,
470 government_context ,
471 interface_source_code ,
472 last_update_date ,
473 last_update_login ,
474 last_updated_by ,
475 min_release_amount ,
476 mrc_rate ,
477 mrc_rate_date ,
478 mrc_rate_type ,
479 note_to_authorizer ,
480 note_to_receiver ,
481 note_to_vendor ,
482 org_id ,
483 pay_on_code ,
484 pcard_id ,
485 pending_signature_flag ,
486 po_header_id ,
487 price_update_tolerance ,
488 print_count ,
489 printed_date ,
490 program_application_id ,
491 program_id ,
492 program_update_date ,
493 quotation_class_code ,
494 quote_type_lookup_code ,
495 quote_vendor_quote_number ,
496 quote_warning_delay ,
497 quote_warning_delay_unit ,
498 rate ,
499 rate_date ,
500 rate_type ,
501 reference_num ,
502 reply_date ,
503 reply_method_lookup_code ,
504 request_id ,
505 revised_date ,
506 revision_num ,
507 rfq_close_date ,
508 segment1 ,
509 segment2 ,
510 segment3 ,
511 segment4 ,
512 segment5 ,
513 ship_to_location_id ,
514 ship_via_lookup_code ,
515 shipping_control ,
516 start_date ,
517 start_date_active ,
518 status_lookup_code ,
519 summary_flag ,
520 supply_agreement_flag ,
521 terms_id ,
522 type_lookup_code ,
523 user_hold_flag ,
524 vendor_contact_id ,
525 vendor_id ,
526 vendor_order_num ,
527 vendor_site_id ,
528 wf_item_key ,
529 wf_item_type ,
530 xml_change_send_date ,
531 xml_flag ,
532 xml_send_date ,
533 'Y',
534 document_creation_method -- <DBI FPJ>
535 ,submit_date --<DBI Req Fulfillment 11.5.11>
536 ,style_id --<R12 STYLES PHASE II>
537 , created_language --<Unified Catalog R12>
538 , cpa_reference --<Unified Catalog R12>
539 , last_updated_program --<Unified Catalog R12>
540 , pay_when_paid -- E and C ER
541 FROM PO_HEADERS_ALL
542 WHERE po_header_id = p_document_id;
543
544 l_progress := '030';
545
546 EXCEPTION
547 WHEN OTHERS THEN
548 po_message_s.sql_error('Exception of ARCHIVE_HEADER()',
549 l_progress , sqlcode);
550 FND_MSG_PUB.Add;
551 IF (G_FND_DEBUG = 'Y') THEN
552 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
553 FND_LOG.string(FND_LOG.level_unexpected, l_module,
554 'EXCEPTION: '||sqlerrm);
555 END IF;
556 END IF;
557 RAISE;
558 END ARCHIVE_HEADER;
559
560 -------------------------------------------------------------------------------
561 --Start of Comments
562 --Name: ARCHIVE_RELEASE
563 --Pre-reqs:
564 -- None.
565 --Modifies:
566 -- PO_RELEASES_ARCHIVE
567 --Locks:
568 -- None.
569 --Function:
570 -- Archive the release header
571 --Parameters:
572 --IN:
573 --p_document_id
574 -- The id of the document that needs to be archived
575 --Testing:
576 -- None.
577 --End of Comments
578 -------------------------------------------------------------------------------
579 PROCEDURE ARCHIVE_RELEASE(p_document_id IN NUMBER)
580 IS
581
582 l_api_name CONSTANT VARCHAR2(30) := 'ARCHIVE_RELEASE';
583 l_module VARCHAR2(100);
584 l_progress VARCHAR2(3);
585
586 BEGIN
587
588 l_progress := '000';
589 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
590 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_PROCEDURE THEN
591 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, l_module,
592 'Entering ' || G_PKG_NAME || '.' || l_api_name);
593 END IF;
594
595 l_progress := '010';
596 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
597 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
598 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
599 'Update PO_HEADERS_ARCHIVE to reset latest_external_flag');
600 END IF;
601
602 UPDATE PO_RELEASES_ARCHIVE_ALL
603 SET latest_external_flag = 'N'
604 WHERE po_release_id = p_document_id
605 AND latest_external_flag = 'Y';
606
607 l_progress := '020';
608 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
609 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
610 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
611 'Insert PO_RELEASES_ARCHIVE ');
612 END IF;
613
614 -- Archiving the releases
615 INSERT INTO PO_RELEASES_ARCHIVE_ALL
616 (acceptance_due_date ,
617 acceptance_required_flag ,
618 agent_id ,
619 approved_date ,
620 approved_flag ,
621 attribute1 ,
622 attribute10 ,
623 attribute11 ,
624 attribute12 ,
625 attribute13 ,
626 attribute14 ,
627 attribute15 ,
628 attribute2 ,
629 attribute3 ,
630 attribute4 ,
631 attribute5 ,
632 attribute6 ,
633 attribute7 ,
634 attribute8 ,
635 attribute9 ,
636 attribute_category ,
637 authorization_status ,
638 cancel_date ,
639 cancel_flag ,
640 cancel_reason ,
641 cancelled_by ,
642 cbc_accounting_date ,
643 change_requested_by ,
644 change_summary ,
645 closed_code ,
646 consigned_consumption_flag ,
647 created_by ,
648 creation_date ,
649 edi_processed_flag ,
650 firm_date ,
651 firm_status_lookup_code ,
652 frozen_flag ,
653 global_attribute1 ,
654 global_attribute10 ,
655 global_attribute11 ,
656 global_attribute12 ,
657 global_attribute13 ,
658 global_attribute14 ,
659 global_attribute15 ,
660 global_attribute16 ,
661 global_attribute17 ,
662 global_attribute18 ,
663 global_attribute19 ,
664 global_attribute2 ,
665 global_attribute20 ,
666 global_attribute3 ,
667 global_attribute4 ,
668 global_attribute5 ,
669 global_attribute6 ,
670 global_attribute7 ,
671 global_attribute8 ,
672 global_attribute9 ,
673 global_attribute_category ,
674 government_context ,
675 hold_by ,
676 hold_date ,
677 hold_flag ,
678 hold_reason ,
679 last_update_date ,
680 last_update_login ,
681 last_updated_by ,
682 note_to_vendor ,
683 org_id ,
684 pay_on_code ,
685 pcard_id ,
686 po_header_id ,
687 po_release_id ,
688 print_count ,
689 printed_date ,
690 program_application_id ,
691 program_id ,
692 program_update_date ,
693 release_date ,
694 release_num ,
695 release_type ,
696 request_id ,
697 revised_date ,
698 revision_num ,
699 shipping_control ,
700 vendor_order_num ,
701 wf_item_key ,
702 wf_item_type ,
703 xml_change_send_date ,
704 xml_flag ,
705 xml_send_date ,
706 latest_external_flag,
707 document_creation_method -- <DBI FPJ>
708 , submit_date --<DBI Req Fulfillment 11.5.11>
709 )
710 SELECT
711 acceptance_due_date ,
712 acceptance_required_flag ,
713 agent_id ,
714 approved_date ,
715 approved_flag ,
716 attribute1 ,
717 attribute10 ,
718 attribute11 ,
719 attribute12 ,
720 attribute13 ,
721 attribute14 ,
722 attribute15 ,
723 attribute2 ,
724 attribute3 ,
725 attribute4 ,
726 attribute5 ,
727 attribute6 ,
728 attribute7 ,
729 attribute8 ,
730 attribute9 ,
731 attribute_category ,
732 authorization_status ,
733 cancel_date ,
734 cancel_flag ,
735 cancel_reason ,
736 cancelled_by ,
737 cbc_accounting_date ,
738 change_requested_by ,
739 change_summary ,
740 closed_code ,
741 consigned_consumption_flag ,
742 created_by ,
743 creation_date ,
744 -- Bug 3438383, EDI Team expects EDI columns NULL
745 -- edi_processed_flag ,
746 NULL ,
747 firm_date ,
748 firm_status_lookup_code ,
749 frozen_flag ,
750 global_attribute1 ,
751 global_attribute10 ,
752 global_attribute11 ,
753 global_attribute12 ,
754 global_attribute13 ,
755 global_attribute14 ,
756 global_attribute15 ,
757 global_attribute16 ,
758 global_attribute17 ,
759 global_attribute18 ,
760 global_attribute19 ,
761 global_attribute2 ,
762 global_attribute20 ,
763 global_attribute3 ,
764 global_attribute4 ,
765 global_attribute5 ,
766 global_attribute6 ,
767 global_attribute7 ,
768 global_attribute8 ,
769 global_attribute9 ,
770 global_attribute_category ,
771 government_context ,
772 hold_by ,
773 hold_date ,
774 hold_flag ,
775 hold_reason ,
776 last_update_date ,
777 last_update_login ,
778 last_updated_by ,
779 note_to_vendor ,
780 org_id ,
781 pay_on_code ,
782 pcard_id ,
783 po_header_id ,
784 po_release_id ,
785 print_count ,
786 printed_date ,
787 program_application_id ,
788 program_id ,
789 program_update_date ,
790 release_date ,
791 release_num ,
792 release_type ,
793 request_id ,
794 revised_date ,
795 revision_num ,
796 shipping_control ,
797 vendor_order_num ,
798 wf_item_key ,
799 wf_item_type ,
800 xml_change_send_date ,
801 xml_flag ,
802 xml_send_date ,
803 'Y',
804 document_creation_method -- <DBI FPJ>
805 , submit_date --<DBI Req Fulfillment 11.5.11>
806 FROM PO_RELEASES_ALL
807 WHERE po_release_id = p_document_id;
808
809 l_progress := '030';
810
811 EXCEPTION
812 WHEN OTHERS THEN
813 po_message_s.sql_error('Exception of ARCHIVE_RELEASE()',
814 l_progress , sqlcode);
815 FND_MSG_PUB.Add;
816 IF (G_FND_DEBUG = 'Y') THEN
817 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
818 FND_LOG.string(FND_LOG.level_unexpected, l_module,
819 'EXCEPTION: '||sqlerrm);
820 END IF;
821 END IF;
822 RAISE;
823 END ARCHIVE_RELEASE;
824
825 -------------------------------------------------------------------------------
826 --Start of Comments
827 --Name: ARCHIVE_LINES
828 --Pre-reqs:
829 -- None.
830 --Modifies:
831 -- PO_LINES_ARCHIVE
832 --Locks:
833 -- None.
834 --Function:
835 -- Arcives the po document lines.
836 --Parameters:
837 --IN:
838 --p_document_id
839 -- The id of the document that needs to be archived.
840 --p_revision_num
841 -- The revision of the document that needs to be archived.
842 --Testing:
843 -- None.
844 --End of Comments
845 -------------------------------------------------------------------------------
846 PROCEDURE ARCHIVE_LINES(p_document_id IN NUMBER,
847 p_revision_num IN NUMBER)
848 IS
849 l_api_name CONSTANT VARCHAR2(30) := 'ARCHIVE_LINES';
850 l_module VARCHAR2(100);
851 l_progress VARCHAR2(3);
852 l_revision_num NUMBER;
853 l_continue BOOLEAN := FALSE;
854
855 BEGIN
856
857 l_progress := '000';
858 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
859 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_PROCEDURE THEN
860 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, l_module,
861 'Entering ' || G_PKG_NAME || '.' || l_api_name);
862 END IF;
863
864 l_progress := '010';
865 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
866 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
867 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
868 'INSERT PO_LINES_ARCHIVE');
869 END IF;
870
871 /*Bug7286203 - Added some fields to archival check so that cancellation and archival are in sync*/
872
873 INSERT INTO PO_LINES_ARCHIVE_ALL
874 (allow_price_override_flag,
875 amount,
876 attribute1,
877 attribute10,
878 attribute11,
879 attribute12,
880 attribute13,
881 attribute14,
882 attribute15,
883 attribute2,
884 attribute3,
885 attribute4,
886 attribute5,
887 attribute6,
888 attribute7,
889 attribute8,
890 attribute9,
891 attribute_category,
892 auction_display_number,
893 auction_header_id,
894 auction_line_number,
895 base_qty,
896 base_unit_price, -- <FPJ Advanced Price>
897 base_uom,
898 bid_line_number,
899 bid_number,
900 cancel_date,
901 cancel_flag,
902 cancel_reason,
903 cancelled_by,
904 capital_expense_flag,
905 category_id,
906 closed_by,
907 closed_code,
908 closed_date,
909 closed_flag,
910 closed_reason,
911 committed_amount,
912 contract_id,
913 contract_num,
914 contractor_first_name,
915 contractor_last_name,
916 created_by,
917 creation_date,
918 expiration_date,
919 firm_date,
920 firm_status_lookup_code,
921 from_header_id,
922 from_line_id,
923 global_attribute1,
924 global_attribute10,
925 global_attribute11,
926 global_attribute12,
927 global_attribute13,
928 global_attribute14,
929 global_attribute15,
930 global_attribute16,
931 global_attribute17,
932 global_attribute18,
933 global_attribute19,
934 global_attribute2,
935 global_attribute20,
936 global_attribute3,
937 global_attribute4,
938 global_attribute5,
939 global_attribute6,
940 global_attribute7,
941 global_attribute8,
942 global_attribute9,
943 global_attribute_category,
944 government_context,
945 hazard_class_id,
946 item_description,
947 item_id,
948 item_revision,
949 job_id,
950 last_update_date,
951 last_update_login,
952 last_updated_by,
953 line_num,
954 line_reference_num,
955 line_type_id,
956 list_price_per_unit,
957 manual_price_change_flag, --<MANUAL PRICE OVERRIDE FPJ>
958 market_price,
959 max_order_quantity,
960 min_order_quantity,
961 min_release_amount,
962 negotiated_by_preparer_flag,
963 not_to_exceed_price,
964 note_to_vendor,
965 oke_contract_header_id,
966 oke_contract_version_id,
967 org_id,
968 over_tolerance_error_flag,
969 po_header_id,
970 po_line_id,
971 preferred_grade,
972 price_break_lookup_code,
973 price_type_lookup_code,
974 program_application_id,
975 program_id,
976 program_update_date,
977 project_id,
978 qc_grade,
979 qty_rcv_tolerance,
980 quantity,
981 quantity_committed,
982 reference_num,
983 request_id,
984 retroactive_date,
985 secondary_qty,
986 secondary_quantity,
987 secondary_unit_of_measure,
988 secondary_uom,
989 start_date,
990 -- <SVC_NOTIFICATIONS START>
991 svc_amount_notif_sent,
992 svc_completion_notif_sent,
993 -- <SVC_NOTIFICATIONS END>
994 supplier_ref_number,
995 task_id,
996 tax_code_id,
997 tax_name,
998 taxable_flag,
999 transaction_reason_code,
1000 type_1099,
1001 un_number_id,
1002 unit_meas_lookup_code,
1003 unit_price,
1004 unordered_flag,
1005 user_hold_flag,
1006 vendor_product_num,
1007 latest_external_flag,
1008 revision_num,
1009 order_type_lookup_code, -- <Complex Work R12>
1010 matching_basis, -- <Complex Work R12>
1011 purchase_basis, -- <Complex Work R12>
1012 max_retainage_amount, -- <Complex Work R12>
1013 retainage_rate, -- <Complex Work R12>
1014 progress_payment_rate, -- <Complex Work R12>
1015 recoupment_rate -- <Complex Work R12>
1016 , catalog_name --<Unified Catalog R12>
1017 , supplier_part_auxid --<Unified Catalog R12>
1018 , ip_category_id --<Unified Catalog R12>
1019 , last_updated_program --<Unified Catalog R12>
1020 )
1021 SELECT
1022 POL.allow_price_override_flag,
1023 POL.amount,
1024 POL.attribute1,
1025 POL.attribute10,
1026 POL.attribute11,
1027 POL.attribute12,
1028 POL.attribute13,
1029 POL.attribute14,
1030 POL.attribute15,
1031 POL.attribute2,
1032 POL.attribute3,
1033 POL.attribute4,
1034 POL.attribute5,
1035 POL.attribute6,
1036 POL.attribute7,
1037 POL.attribute8,
1038 POL.attribute9,
1039 POL.attribute_category,
1040 POL.auction_display_number,
1041 POL.auction_header_id,
1042 POL.auction_line_number,
1043 POL.base_qty,
1044 POL.base_unit_price, -- <FPJ Advanced Price>
1045 POL.base_uom,
1046 POL.bid_line_number,
1047 POL.bid_number,
1048 POL.cancel_date,
1049 POL.cancel_flag,
1050 POL.cancel_reason,
1051 POL.cancelled_by,
1052 POL.capital_expense_flag,
1053 POL.category_id,
1054 POL.closed_by,
1055 POL.closed_code,
1056 POL.closed_date,
1057 POL.closed_flag,
1058 POL.closed_reason,
1059 POL.committed_amount,
1060 POL.contract_id,
1061 POL.contract_num,
1062 POL.contractor_first_name,
1063 POL.contractor_last_name,
1064 POL.created_by,
1065 POL.creation_date,
1066 POL.expiration_date,
1067 POL.firm_date,
1068 POL.firm_status_lookup_code,
1069 POL.from_header_id,
1070 POL.from_line_id,
1071 POL.global_attribute1,
1072 POL.global_attribute10,
1073 POL.global_attribute11,
1074 POL.global_attribute12,
1075 POL.global_attribute13,
1076 POL.global_attribute14,
1077 POL.global_attribute15,
1078 POL.global_attribute16,
1079 POL.global_attribute17,
1080 POL.global_attribute18,
1081 POL.global_attribute19,
1082 POL.global_attribute2,
1083 POL.global_attribute20,
1084 POL.global_attribute3,
1085 POL.global_attribute4,
1086 POL.global_attribute5,
1087 POL.global_attribute6,
1088 POL.global_attribute7,
1089 POL.global_attribute8,
1090 POL.global_attribute9,
1091 POL.global_attribute_category,
1092 POL.government_context,
1093 POL.hazard_class_id,
1094 POL.item_description,
1095 POL.item_id,
1096 POL.item_revision,
1097 POL.job_id,
1098 POL.last_update_date,
1099 POL.last_update_login,
1100 POL.last_updated_by,
1101 POL.line_num,
1102 POL.line_reference_num,
1103 POL.line_type_id,
1104 POL.list_price_per_unit,
1105 POL.manual_price_change_flag, --<MANUAL PRICE OVERRIDE FPJ>
1106 POL.market_price,
1107 POL.max_order_quantity,
1108 POL.min_order_quantity,
1109 POL.min_release_amount,
1110 POL.negotiated_by_preparer_flag,
1111 POL.not_to_exceed_price,
1112 POL.note_to_vendor,
1113 POL.oke_contract_header_id,
1114 POL.oke_contract_version_id,
1115 POL.org_id,
1116 POL.over_tolerance_error_flag,
1117 POL.po_header_id,
1118 POL.po_line_id,
1119 POL.preferred_grade,
1120 POL.price_break_lookup_code,
1121 POL.price_type_lookup_code,
1122 POL.program_application_id,
1123 POL.program_id,
1124 POL.program_update_date,
1125 POL.project_id,
1126 POL.qc_grade,
1127 POL.qty_rcv_tolerance,
1128 POL.quantity,
1129 POL.quantity_committed,
1130 POL.reference_num,
1131 POL.request_id,
1132 POL.retroactive_date,
1133 POL.secondary_qty,
1134 POL.secondary_quantity,
1135 POL.secondary_unit_of_measure,
1136 POL.secondary_uom,
1137 POL.start_date,
1138 -- <SVC_NOTIFICATIONS START>
1139 POL.svc_amount_notif_sent,
1140 POL.svc_completion_notif_sent,
1141 -- <SVC_NOTIFICATIONS END>
1142 POL.supplier_ref_number,
1143 POL.task_id,
1144 POL.tax_code_id,
1145 POL.tax_name,
1146 POL.taxable_flag,
1147 POL.transaction_reason_code,
1148 POL.type_1099,
1149 POL.un_number_id,
1150 POL.unit_meas_lookup_code,
1151 POL.unit_price,
1152 POL.unordered_flag,
1153 POL.user_hold_flag,
1154 POL.vendor_product_num,
1155 'Y',
1156 p_revision_num,
1157 POL.order_type_lookup_code, -- <Complex Work R12>
1158 POL.matching_basis, -- <Complex Work R12>
1159 POL.purchase_basis, -- <Complex Work R12>
1160 POL.max_retainage_amount, -- <Complex Work R12>
1161 POL.retainage_rate, -- <Complex Work R12>
1162 POL.progress_payment_rate, -- <Complex Work R12>
1163 POL.recoupment_rate -- <Complex Work R12>
1164 , POL.catalog_name --<Unified Catalog R12>
1165 , POL.supplier_part_auxid --<Unified Catalog R12>
1166 , POL.ip_category_id --<Unified Catalog R12>
1167 , POL.last_updated_program --<Unified Catalog R12>
1168 FROM PO_LINES_ALL POL,
1169 PO_LINES_ARCHIVE_ALL POLA
1170 WHERE POL.po_header_id = p_document_id
1171 AND POL.po_line_id = POLA.po_line_id (+)
1172 AND POLA.latest_external_flag (+) = 'Y'
1173 AND ((POLA.po_line_id is NULL) OR
1174 (POL.amount IS NULL AND POLA.amount IS NOT NULL OR
1175 POL.amount IS NOT NULL AND POLA.amount IS NULL OR
1176 POL.amount <> POLA.amount) OR
1177 -- <FPJ Advanced Price START>
1178 (POL.base_unit_price IS NULL AND POLA.base_unit_price IS NOT NULL OR
1179 POL.base_unit_price IS NOT NULL AND POLA.base_unit_price IS NULL OR
1180 POL.base_unit_price <> POLA.base_unit_price) OR
1181 -- <FPJ Advanced Price END>
1182 (POL.cancel_flag IS NULL AND POLA.cancel_flag IS NOT NULL OR
1183 POL.cancel_flag IS NOT NULL AND POLA.cancel_flag IS NULL OR
1184 POL.cancel_flag <> POLA.cancel_flag) OR
1185 (POL.closed_flag IS NULL AND POLA.closed_flag IS NOT NULL OR
1186 POL.closed_flag IS NOT NULL AND POLA.closed_flag IS NULL OR
1187 POL.closed_flag <> POLA.closed_flag) OR
1188 (POL.committed_amount IS NULL AND POLA.committed_amount IS NOT NULL OR
1189 POL.committed_amount IS NOT NULL AND POLA.committed_amount IS NULL OR
1190 POL.committed_amount <> POLA.committed_amount) OR
1191 (POL.contract_id IS NULL AND POLA.contract_id IS NOT NULL OR
1192 POL.contract_id IS NOT NULL AND POLA.contract_id IS NULL OR
1193 POL.contract_id <> POLA.contract_id) OR
1194 (POL.contractor_first_name IS NULL AND POLA.contractor_first_name IS NOT NULL OR
1195 POL.contractor_first_name IS NOT NULL AND POLA.contractor_first_name IS NULL OR
1196 POL.contractor_first_name <> POLA.contractor_first_name) OR
1197 (POL.contractor_last_name IS NULL AND POLA.contractor_last_name IS NOT NULL OR
1198 POL.contractor_last_name IS NOT NULL AND POLA.contractor_last_name IS NULL OR
1199 POL.contractor_last_name <> POLA.contractor_last_name) OR
1200 (POL.expiration_date IS NULL AND POLA.expiration_date IS NOT NULL OR
1201 POL.expiration_date IS NOT NULL AND POLA.expiration_date IS NULL OR
1202 POL.expiration_date <> POLA.expiration_date) OR
1203 (POL.from_header_id IS NULL AND POLA.from_header_id IS NOT NULL OR
1204 POL.from_header_id IS NOT NULL AND POLA.from_header_id IS NULL OR
1205 POL.from_header_id <> POLA.from_header_id) OR
1206 (POL.from_line_id IS NULL AND POLA.from_line_id IS NOT NULL OR
1207 POL.from_line_id IS NOT NULL AND POLA.from_line_id IS NULL OR
1208 POL.from_line_id <> POLA.from_line_id) OR
1209 (POL.hazard_class_id IS NULL AND POLA.hazard_class_id IS NOT NULL OR
1210 POL.hazard_class_id IS NOT NULL AND POLA.hazard_class_id IS NULL OR
1211 POL.hazard_class_id <> POLA.hazard_class_id) OR
1212 (POL.item_description IS NULL AND POLA.item_description IS NOT NULL OR
1213 POL.item_description IS NOT NULL AND POLA.item_description IS NULL OR
1214 POL.item_description <> POLA.item_description) OR
1215 (POL.item_id IS NULL AND POLA.item_id IS NOT NULL OR
1216 POL.item_id IS NOT NULL AND POLA.item_id IS NULL OR
1217 POL.item_id <> POLA.item_id) OR
1218 (POL.item_revision IS NULL AND POLA.item_revision IS NOT NULL OR
1219 POL.item_revision IS NOT NULL AND POLA.item_revision IS NULL OR
1220 POL.item_revision <> POLA.item_revision) OR
1221 (POL.job_id IS NULL AND POLA.job_id IS NOT NULL OR
1222 POL.job_id IS NOT NULL AND POLA.job_id IS NULL OR
1223 POL.job_id <> POLA.job_id) OR
1224 (POL.line_num IS NULL AND POLA.line_num IS NOT NULL OR
1225 POL.line_num IS NOT NULL AND POLA.line_num IS NULL OR
1226 POL.line_num <> POLA.line_num) OR
1227 (POL.note_to_vendor IS NULL AND POLA.note_to_vendor IS NOT NULL OR
1228 POL.note_to_vendor IS NOT NULL AND POLA.note_to_vendor IS NULL OR
1229 POL.note_to_vendor <> POLA.note_to_vendor) OR
1230 (POL.price_type_lookup_code IS NULL AND POLA.price_type_lookup_code IS NOT NULL OR
1231 POL.price_type_lookup_code IS NOT NULL AND POLA.price_type_lookup_code IS NULL OR
1232 POL.price_type_lookup_code <> POLA.price_type_lookup_code) OR
1233 (POL.quantity IS NULL AND POLA.quantity IS NOT NULL OR
1234 POL.quantity IS NOT NULL AND POLA.quantity IS NULL OR
1235 POL.quantity <> POLA.quantity) OR
1236 (POL.quantity_committed IS NULL AND POLA.quantity_committed IS NOT NULL OR
1237 POL.quantity_committed IS NOT NULL AND POLA.quantity_committed IS NULL OR
1238 POL.quantity_committed <> POLA.quantity_committed) OR
1239 (POL.start_date IS NULL AND POLA.start_date IS NOT NULL OR
1240 POL.start_date IS NOT NULL AND POLA.start_date IS NULL OR
1241 POL.start_date <> POLA.start_date) OR
1242 (POL.unit_meas_lookup_code IS NULL AND POLA.unit_meas_lookup_code IS NOT NULL OR
1243 POL.unit_meas_lookup_code IS NOT NULL AND POLA.unit_meas_lookup_code IS NULL OR
1244 POL.unit_meas_lookup_code <> POLA.unit_meas_lookup_code) OR
1245 (POL.unit_price IS NULL AND POLA.unit_price IS NOT NULL OR
1246 POL.unit_price IS NOT NULL AND POLA.unit_price IS NULL OR
1247 POL.unit_price <> POLA.unit_price) OR
1248 -- Bug 3471211
1249 (POL.not_to_exceed_price IS NULL AND POLA.not_to_exceed_price IS NOT NULL OR
1250 POL.not_to_exceed_price IS NOT NULL AND POLA.not_to_exceed_price IS NULL OR
1251 POL.not_to_exceed_price <> POLA.not_to_exceed_price) OR
1252 (POL.un_number_id IS NULL AND POLA.un_number_id IS NOT NULL OR
1253 POL.un_number_id IS NOT NULL AND POLA.un_number_id IS NULL OR
1254 POL.un_number_id <> POLA.un_number_id) OR
1255 (POL.vendor_product_num IS NULL AND POLA.vendor_product_num IS NOT NULL OR
1256 POL.vendor_product_num IS NOT NULL AND POLA.vendor_product_num IS NULL OR
1257 POL.vendor_product_num <> POLA.vendor_product_num) OR
1258 -- <Complex Work R12 Start>
1259 (POL.max_retainage_amount IS NULL AND POLA.max_retainage_amount IS NOT NULL OR
1260 POL.max_retainage_amount IS NOT NULL AND POLA.max_retainage_amount IS NULL OR
1261 POL.max_retainage_amount <> POLA.max_retainage_amount) OR
1262 (POL.retainage_rate IS NULL AND POLA.retainage_rate IS NOT NULL OR
1263 POL.retainage_rate IS NOT NULL AND POLA.retainage_rate IS NULL OR
1264 POL.retainage_rate <> POLA.retainage_rate) OR
1265 (POL.progress_payment_rate IS NULL AND POLA.progress_payment_rate IS NOT NULL OR
1266 POL.progress_payment_rate IS NOT NULL AND POLA.progress_payment_rate IS NULL OR
1267 POL.progress_payment_rate <> POLA.progress_payment_rate) OR
1268 (POL.recoupment_rate IS NULL AND POLA.recoupment_rate IS NOT NULL OR
1269 POL.recoupment_rate IS NOT NULL AND POLA.recoupment_rate IS NULL OR
1270 POL.recoupment_rate <> POLA.recoupment_rate) OR
1271 -- <Complex Work R12 End>
1272 -- <SVC_NOTIFICATIONS START>
1273 ((POL.svc_amount_notif_sent IS NULL AND
1274 POLA.svc_amount_notif_sent IS NOT NULL) OR
1275 (POL.svc_amount_notif_sent IS NOT NULL AND
1276 POLA.svc_amount_notif_sent IS NULL) OR
1277 (POL.svc_amount_notif_sent <> POLA.svc_amount_notif_sent)) OR
1278 ((POL.svc_completion_notif_sent IS NULL AND
1279 POLA.svc_completion_notif_sent IS NOT NULL) OR
1280 (POL.svc_completion_notif_sent IS NOT NULL AND
1281 POLA.svc_completion_notif_sent IS NULL) OR
1282 (POL.svc_completion_notif_sent <> POLA.svc_completion_notif_sent))OR
1283 -- <SVC_NOTIFICATIONS END>
1284 (POL.closed_code IS NULL AND POLA.closed_code IS NOT NULL OR
1285 POL.closed_code IS NOT NULL AND POLA.closed_code IS NULL OR
1286 POL.closed_code <> POLA.closed_code) --Bug7286203
1287 );
1288
1289 l_continue := (SQL%ROWCOUNT > 0);
1290
1291 IF l_continue THEN
1292 l_progress := '020';
1293 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
1294 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
1295 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
1296 'Update PO_LINES_ARCHIVE to reset latest_external_flag');
1297 END IF;
1298
1299 -- If a row was inserted into PO_LINES_ARCHIVE, then set the appropriate flags
1300 UPDATE PO_LINES_ARCHIVE_ALL POL1
1301 SET latest_external_flag = 'N'
1302 WHERE po_header_id = p_document_id
1303 AND latest_external_flag = 'Y'
1304 AND revision_num < p_revision_num
1305 AND EXISTS
1306 (SELECT 'A new archived row'
1307 FROM PO_LINES_ARCHIVE_ALL POL2
1308 WHERE POL2.po_line_id = POL1.po_line_id
1309 AND POL2.latest_external_flag = 'Y'
1310 AND POL2.revision_num = p_revision_num);
1311 ELSE
1312 l_progress := '030';
1313 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
1314 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
1315 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
1316 'No need to reset latest_external_flag');
1317 END IF;
1318 END IF; /* IF l_continue */
1319
1320 l_progress := '030';
1321
1322 EXCEPTION
1323 WHEN OTHERS THEN
1324 po_message_s.sql_error('Exception of ARCHIVE_LINES()',
1325 l_progress , sqlcode);
1326 FND_MSG_PUB.Add;
1327 IF (G_FND_DEBUG = 'Y') THEN
1328 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
1329 FND_LOG.string(FND_LOG.level_unexpected, l_module,
1330 'EXCEPTION: '||sqlerrm);
1331 END IF;
1332 END IF;
1333 RAISE;
1334 END ARCHIVE_LINES;
1335
1336 -------------------------------------------------------------------------------
1337 --Start of Comments
1338 --Name: ARCHIVE_LINE_LOCATIONS
1339 --Pre-reqs:
1340 -- None.
1341 --Modifies:
1342 -- PO_LINE_LOCATIONS_ARCHIVE
1343 --Locks:
1344 -- None.
1345 --Function:
1346 -- Arcives the po document line locations.
1347 --Parameters:
1348 --IN:
1349 --p_document_id
1350 -- The id of the document that needs to be archived.
1351 --p_document_type
1352 -- The type of the document to archive
1353 -- PO : For Standard/Planned
1354 -- PA : For Blanket/Contract
1355 -- RELEASE : Release
1356 --p_revision_num
1357 -- The revision of the document that needs to be archived.
1358 --Testing:
1359 -- None.
1360 --End of Comments
1361 -------------------------------------------------------------------------------
1362 PROCEDURE ARCHIVE_LINE_LOCATIONS(p_document_id IN NUMBER,
1363 p_document_type IN VARCHAR2,
1364 p_revision_num IN NUMBER)
1365 IS
1366 l_api_name CONSTANT VARCHAR2(30) := 'ARCHIVE_LINE_LOCATIONS';
1367 l_module VARCHAR2(100);
1368 l_progress VARCHAR2(3);
1369 l_revision_num NUMBER;
1370 l_continue BOOLEAN := FALSE;
1371
1372 BEGIN
1373
1374 l_progress := '000';
1375 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
1376 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_PROCEDURE THEN
1377 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, l_module,
1378 'Entering ' || G_PKG_NAME || '.' || l_api_name);
1379 END IF;
1380
1381 l_progress := '010';
1382 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
1383 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
1384 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
1385 'INSERT PO_LINE_LOCATIONS_ARCHIVE');
1386 END IF;
1387
1388 INSERT INTO PO_LINE_LOCATIONS_ARCHIVE_ALL
1389 (accrue_on_receipt_flag,
1390 allow_substitute_receipts_flag,
1391 amount,
1392 amount_accepted,
1393 amount_billed,
1394 amount_cancelled,
1395 amount_received,
1396 amount_rejected,
1397 approved_date,
1398 approved_flag,
1399 attribute1,
1400 attribute10,
1401 attribute11,
1402 attribute12,
1403 attribute13,
1404 attribute14,
1405 attribute15,
1406 attribute2,
1407 attribute3,
1408 attribute4,
1409 attribute5,
1410 attribute6,
1411 attribute7,
1412 attribute8,
1413 attribute9,
1414 attribute_category,
1415 calculate_tax_flag,
1416 cancel_date,
1417 cancel_flag,
1418 cancel_reason,
1419 cancelled_by,
1420 change_promised_date_reason,
1421 closed_by,
1422 closed_code,
1423 closed_date,
1424 closed_flag,
1425 closed_reason,
1426 consigned_flag,
1427 country_of_origin_code,
1428 created_by,
1429 creation_date,
1430 days_early_receipt_allowed,
1431 days_late_receipt_allowed,
1432 drop_ship_flag,
1433 encumber_now,
1434 encumbered_date,
1435 encumbered_flag,
1436 end_date,
1437 enforce_ship_to_location_code,
1438 estimated_tax_amount,
1439 final_match_flag, --<BUG 3431828>
1440 firm_date,
1441 firm_status_lookup_code,
1442 fob_lookup_code,
1443 freight_terms_lookup_code,
1444 from_header_id,
1445 from_line_id,
1446 from_line_location_id,
1447 global_attribute1,
1448 global_attribute10,
1449 global_attribute11,
1450 global_attribute12,
1451 global_attribute13,
1452 global_attribute14,
1453 global_attribute15,
1454 global_attribute16,
1455 global_attribute17,
1456 global_attribute18,
1457 global_attribute19,
1458 global_attribute2,
1459 global_attribute20,
1460 global_attribute3,
1461 global_attribute4,
1462 global_attribute5,
1463 global_attribute6,
1464 global_attribute7,
1465 global_attribute8,
1466 global_attribute9,
1467 global_attribute_category,
1468 government_context,
1469 inspection_required_flag,
1470 invoice_close_tolerance,
1471 last_accept_date,
1472 last_update_date,
1473 last_update_login,
1474 last_updated_by,
1475 lead_time,
1476 lead_time_unit,
1477 line_location_id,
1478 manual_price_change_flag, --<MANUAL PRICE OVERRIDE FPJ>
1479 match_option,
1480 need_by_date,
1481 note_to_receiver,
1482 org_id,
1483 po_header_id,
1484 po_line_id,
1485 po_release_id,
1486 preferred_grade,
1487 price_discount,
1488 price_override,
1489 program_application_id,
1490 program_id,
1491 program_update_date,
1492 promised_date,
1493 qty_rcv_exception_code,
1494 qty_rcv_tolerance,
1495 quantity,
1496 quantity_accepted,
1497 quantity_billed,
1498 quantity_cancelled,
1499 quantity_received,
1500 quantity_rejected,
1501 quantity_shipped,
1502 receipt_days_exception_code,
1503 receipt_required_flag,
1504 receive_close_tolerance,
1505 receiving_routing_id,
1506 request_id,
1507 retroactive_date,
1508 sales_order_update_date,
1509 secondary_quantity,
1510 secondary_quantity_accepted,
1511 secondary_quantity_cancelled,
1512 secondary_quantity_received,
1513 secondary_quantity_rejected,
1514 secondary_unit_of_measure,
1515 ship_to_location_id,
1516 ship_to_organization_id,
1517 ship_via_lookup_code,
1518 shipment_num,
1519 shipment_type,
1520 source_shipment_id,
1521 start_date,
1522 supplier_order_line_number,
1523 tax_code_id,
1524 tax_user_override_flag,
1525 taxable_flag,
1526 terms_id,
1527 transaction_flow_header_id,
1528 unencumbered_quantity,
1529 unit_meas_lookup_code,
1530 unit_of_measure_class,
1531 vmi_flag,
1532 latest_external_flag,
1533 revision_num
1534 --<DBI Req Fulfillment 11.5.11 Start>
1535 ,shipment_closed_date
1536 ,closed_for_receiving_date
1537 ,closed_for_invoice_date
1538 --<DBI Req Fulfillment 11.5.11 End>
1539 -- <Complex Work R12 Start>
1540 , value_basis
1541 , matching_basis
1542 , description
1543 , payment_type
1544 , work_approver_id
1545 , bid_payment_id
1546 , quantity_financed
1547 , amount_financed
1548 , quantity_recouped
1549 , amount_recouped
1550 , retainage_withheld_amount
1551 , retainage_released_amount
1552 -- <Complex work R12 End>
1553 , outsourced_assembly --<SHIKYU R12>
1554 )
1555 SELECT
1556 POL.accrue_on_receipt_flag,
1557 POL.allow_substitute_receipts_flag,
1558 POL.amount,
1559 POL.amount_accepted,
1560 POL.amount_billed,
1561 POL.amount_cancelled,
1562 POL.amount_received,
1563 POL.amount_rejected,
1564 POL.approved_date,
1565 POL.approved_flag,
1566 POL.attribute1,
1567 POL.attribute10,
1568 POL.attribute11,
1569 POL.attribute12,
1570 POL.attribute13,
1571 POL.attribute14,
1572 POL.attribute15,
1573 POL.attribute2,
1574 POL.attribute3,
1575 POL.attribute4,
1576 POL.attribute5,
1577 POL.attribute6,
1578 POL.attribute7,
1579 POL.attribute8,
1580 POL.attribute9,
1581 POL.attribute_category,
1582 POL.calculate_tax_flag,
1583 POL.cancel_date,
1584 POL.cancel_flag,
1585 POL.cancel_reason,
1586 POL.cancelled_by,
1587 POL.change_promised_date_reason,
1588 POL.closed_by,
1589 POL.closed_code,
1590 POL.closed_date,
1591 POL.closed_flag,
1592 POL.closed_reason,
1593 POL.consigned_flag,
1594 POL.country_of_origin_code,
1595 POL.created_by,
1596 POL.creation_date,
1597 POL.days_early_receipt_allowed,
1598 POL.days_late_receipt_allowed,
1599 POL.drop_ship_flag,
1600 POL.encumber_now,
1601 POL.encumbered_date,
1602 POL.encumbered_flag,
1603 POL.end_date,
1604 POL.enforce_ship_to_location_code,
1605 POL.estimated_tax_amount,
1606 POL.final_match_flag, --<BUG 3431828>
1607 POL.firm_date,
1608 POL.firm_status_lookup_code,
1609 POL.fob_lookup_code,
1610 POL.freight_terms_lookup_code,
1611 POL.from_header_id,
1612 POL.from_line_id,
1613 POL.from_line_location_id,
1614 POL.global_attribute1,
1615 POL.global_attribute10,
1616 POL.global_attribute11,
1617 POL.global_attribute12,
1618 POL.global_attribute13,
1619 POL.global_attribute14,
1620 POL.global_attribute15,
1621 POL.global_attribute16,
1622 POL.global_attribute17,
1623 POL.global_attribute18,
1624 POL.global_attribute19,
1625 POL.global_attribute2,
1626 POL.global_attribute20,
1627 POL.global_attribute3,
1628 POL.global_attribute4,
1629 POL.global_attribute5,
1630 POL.global_attribute6,
1631 POL.global_attribute7,
1632 POL.global_attribute8,
1633 POL.global_attribute9,
1634 POL.global_attribute_category,
1635 POL.government_context,
1636 POL.inspection_required_flag,
1637 POL.invoice_close_tolerance,
1638 POL.last_accept_date,
1639 POL.last_update_date,
1640 POL.last_update_login,
1641 POL.last_updated_by,
1642 POL.lead_time,
1643 POL.lead_time_unit,
1644 POL.line_location_id,
1645 POL.manual_price_change_flag, --<MANUAL PRICE OVERRIDE FPJ>
1646 POL.match_option,
1647 POL.need_by_date,
1648 POL.note_to_receiver,
1649 POL.org_id,
1650 POL.po_header_id,
1651 POL.po_line_id,
1652 POL.po_release_id,
1653 POL.preferred_grade,
1654 POL.price_discount,
1655 POL.price_override,
1656 POL.program_application_id,
1657 POL.program_id,
1658 POL.program_update_date,
1659 POL.promised_date,
1660 POL.qty_rcv_exception_code,
1661 POL.qty_rcv_tolerance,
1662 POL.quantity,
1663 POL.quantity_accepted,
1664 POL.quantity_billed,
1665 POL.quantity_cancelled,
1666 POL.quantity_received,
1667 POL.quantity_rejected,
1668 POL.quantity_shipped,
1669 POL.receipt_days_exception_code,
1670 POL.receipt_required_flag,
1671 POL.receive_close_tolerance,
1672 POL.receiving_routing_id,
1673 POL.request_id,
1674 POL.retroactive_date,
1675 POL.sales_order_update_date,
1676 POL.secondary_quantity,
1677 POL.secondary_quantity_accepted,
1678 POL.secondary_quantity_cancelled,
1679 POL.secondary_quantity_received,
1680 POL.secondary_quantity_rejected,
1681 POL.secondary_unit_of_measure,
1682 POL.ship_to_location_id,
1683 POL.ship_to_organization_id,
1684 POL.ship_via_lookup_code,
1685 POL.shipment_num,
1686 POL.shipment_type,
1687 POL.source_shipment_id,
1688 POL.start_date,
1689 POL.supplier_order_line_number,
1690 POL.tax_code_id,
1691 POL.tax_user_override_flag,
1692 POL.taxable_flag,
1693 POL.terms_id,
1694 POL.transaction_flow_header_id,
1695 POL.unencumbered_quantity,
1696 POL.unit_meas_lookup_code,
1697 POL.unit_of_measure_class,
1698 POL.vmi_flag,
1699 'Y',
1700 p_revision_num
1701 --<DBI Req Fulfillment 11.5.11 Start>
1702 ,POL.shipment_closed_date
1703 ,POL.closed_for_receiving_date
1704 ,POL.closed_for_invoice_date
1705 --<DBI Req Fulfillment 11.5.11 End>
1706 -- <Complex Work R12 Start>
1707 , POL.value_basis
1708 , POL.matching_basis
1709 , POL.description
1710 , POL.payment_type
1711 , POL.work_approver_id
1712 , POL.bid_payment_id
1713 , POL.quantity_financed
1714 , POL.amount_financed
1715 , POL.quantity_recouped
1716 , POL.amount_recouped
1717 , POL.retainage_withheld_amount
1718 , POL.retainage_released_amount
1719 -- <Complex work R12 End>
1720 , POL.outsourced_assembly --<SHIKYU R12>
1721 FROM PO_LINE_LOCATIONS_ALL POL,
1722 PO_LINE_LOCATIONS_ARCHIVE_ALL POLA
1723 WHERE ((p_document_type = 'RELEASE' AND
1724 POL.po_release_id = p_document_id) OR
1725 (p_document_type <> 'RELEASE' AND -- Bug 3210749
1726 POL.po_header_id = p_document_id AND
1727 POL.po_release_id IS NULL))
1728 AND POL.line_location_id = POLA.line_location_id (+)
1729 AND POLA.latest_external_flag (+) = 'Y'
1730 AND ((POLA.line_location_id is NULL) OR
1731 (POL.amount IS NULL AND POLA.amount IS NOT NULL OR
1732 POL.amount IS NOT NULL AND POLA.amount IS NULL OR
1733 POL.amount <> POLA.amount) OR
1734 (POL.cancel_flag IS NULL AND POLA.cancel_flag IS NOT NULL OR
1735 POL.cancel_flag IS NOT NULL AND POLA.cancel_flag IS NULL OR
1736 POL.cancel_flag <> POLA.cancel_flag) OR
1737 (POL.end_date IS NULL AND POLA.end_date IS NOT NULL OR
1738 POL.end_date IS NOT NULL AND POLA.end_date IS NULL OR
1739 POL.end_date <> POLA.end_date) OR
1740 (POL.last_accept_date IS NULL AND POLA.last_accept_date IS NOT NULL OR
1741 POL.last_accept_date IS NOT NULL AND POLA.last_accept_date IS NULL OR
1742 POL.last_accept_date <> POLA.last_accept_date) OR
1743 (POL.need_by_date IS NULL AND POLA.need_by_date IS NOT NULL OR
1744 POL.need_by_date IS NOT NULL AND POLA.need_by_date IS NULL OR
1745 POL.need_by_date <> POLA.need_by_date) OR
1746 (POL.price_override IS NULL AND POLA.price_override IS NOT NULL OR
1747 POL.price_override IS NOT NULL AND POLA.price_override IS NULL OR
1748 POL.price_override <> POLA.price_override) OR
1749 (POL.promised_date IS NULL AND POLA.promised_date IS NOT NULL OR
1750 POL.promised_date IS NOT NULL AND POLA.promised_date IS NULL OR
1751 POL.promised_date <> POLA.promised_date) OR
1752 (POL.quantity IS NULL AND POLA.quantity IS NOT NULL OR
1753 POL.quantity IS NOT NULL AND POLA.quantity IS NULL OR
1754 POL.quantity <> POLA.quantity) OR
1755 (POL.shipment_num IS NULL AND POLA.shipment_num IS NOT NULL OR
1756 POL.shipment_num IS NOT NULL AND POLA.shipment_num IS NULL OR
1757 POL.shipment_num <> POLA.shipment_num) OR
1758 --<Complex Work R12 Start>
1759 (POL.payment_type IS NULL AND POLA.payment_type IS NOT NULL OR
1760 POL.payment_type IS NOT NULL AND POLA.payment_type IS NULL OR
1761 POL.payment_type <> POLA.payment_type) OR
1762 (POL.description IS NULL AND POLA.description IS NOT NULL OR
1763 POL.description IS NOT NULL AND POLA.description IS NULL OR
1764 POL.description <> POLA.description) OR
1765 (POL.work_approver_id IS NULL AND POLA.work_approver_id IS NOT NULL OR
1766 POL.work_approver_id IS NOT NULL AND POLA.work_approver_id IS NULL OR
1767 POL.work_approver_id <> POLA.work_approver_id) OR
1768 --<Complex Work R12 End>
1769 (POL.ship_to_location_id IS NULL AND POLA.ship_to_location_id IS NOT NULL OR
1770 POL.ship_to_location_id IS NOT NULL AND POLA.ship_to_location_id IS NULL OR
1771 POL.ship_to_location_id <> POLA.ship_to_location_id) OR
1772 (POL.start_date IS NULL AND POLA.start_date IS NOT NULL OR
1773 POL.start_date IS NOT NULL AND POLA.start_date IS NULL OR
1774 POL.start_date <> POLA.start_date) OR
1775 (POL.taxable_flag IS NULL AND POLA.taxable_flag IS NOT NULL OR
1776 POL.taxable_flag IS NOT NULL AND POLA.taxable_flag IS NULL OR
1777 POL.taxable_flag <> POLA.taxable_flag) OR
1778 (POL.sales_order_update_date IS NULL AND POLA.sales_order_update_date IS NOT NULL OR --BUG7286203
1779 POL.sales_order_update_date IS NOT NULL AND POLA.sales_order_update_date IS NULL OR
1780 POL.sales_order_update_date <> POLA.sales_order_update_date));
1781
1782 l_continue := (SQL%ROWCOUNT > 0);
1783
1784 IF l_continue THEN
1785 l_progress := '020';
1786 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
1787 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
1788 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
1789 'Update PO_LINE_LOCATIONS_ARCHIVE to reset latest_external_flag');
1790 END IF;
1791
1792
1793 -- If a row was inserted into PO_LINES_ARCHIVE, then set the appropriate flags
1794
1795 -- Start Bug 3648767: Split up update statement on p_document_type
1796 -- so that the the cost-based optimizer will choose to
1797 -- use the indexes on po_release_id and po_header_id.
1798 -- Comments removed for bug 3210749
1799
1800 IF (p_document_type = 'RELEASE') THEN
1801
1802 UPDATE PO_LINE_LOCATIONS_ARCHIVE_ALL POL1
1803 SET latest_external_flag = 'N'
1804 WHERE po_release_id = p_document_id
1805 AND latest_external_flag = 'Y'
1806 AND revision_num < p_revision_num
1807 AND EXISTS
1808 (SELECT 'A new archived row'
1809 FROM PO_LINE_LOCATIONS_ARCHIVE_ALL POL2
1810 WHERE POL2.line_location_id = POL1.line_location_id
1811 AND POL2.latest_external_flag = 'Y'
1812 AND POL2.revision_num = p_revision_num
1813 );
1814
1815 ELSE
1816 -- p_document_type <> 'RELEASE'
1817
1818 UPDATE PO_LINE_LOCATIONS_ARCHIVE_ALL POL1
1819 SET latest_external_flag = 'N'
1820 WHERE ((po_header_id = p_document_id) AND (po_release_id IS NULL))
1821 AND latest_external_flag = 'Y'
1822 AND revision_num < p_revision_num
1823 AND EXISTS
1824 (SELECT 'A new archived row'
1825 FROM PO_LINE_LOCATIONS_ARCHIVE_ALL POL2
1826 WHERE POL2.line_location_id = POL1.line_location_id
1827 AND POL2.latest_external_flag = 'Y'
1828 AND POL2.revision_num = p_revision_num
1829 );
1830
1831 END IF; -- IF p_document_type = 'RELEASE'
1832
1833 -- End Bug 3648767
1834
1835 ELSE
1836 l_progress := '030';
1837 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
1838 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
1839 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
1840 'No need to reset latest_external_flag');
1841 END IF;
1842 END IF; /* IF l_continue */
1843
1844 l_progress := '030';
1845
1846 EXCEPTION
1847 WHEN OTHERS THEN
1848 po_message_s.sql_error('Exception of ARCHIVE_LINE_LOCATIONS()',
1849 l_progress , sqlcode);
1850 FND_MSG_PUB.Add;
1851 IF (G_FND_DEBUG = 'Y') THEN
1852 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
1853 FND_LOG.string(FND_LOG.level_unexpected, l_module,
1854 'EXCEPTION: '||sqlerrm);
1855 END IF;
1856 END IF;
1857 RAISE;
1858
1859 END ARCHIVE_LINE_LOCATIONS;
1860
1861 -------------------------------------------------------------------------------
1862 --Start of Comments
1863 --Name: ARCHIVE_DISTRIBUTIONS
1864 --Pre-reqs:
1865 -- None.
1866 --Modifies:
1867 -- PO_DISTRIBUTIONS_ARCHIVE
1868 --Locks:
1869 -- None.
1870 --Function:
1871 -- Arcives the po document distributions.
1872 --Parameters:
1873 --IN:
1874 --p_document_id
1875 -- The id of the document that needs to be archived.
1876 --p_document_type
1877 -- The type of the document to archive
1878 -- PO : For Standard/Planned
1879 -- PA : For Blanket/Contract
1880 -- RELEASE : Release
1881 --p_revision_num
1882 -- The revision of the document that needs to be archived.
1883 --Testing:
1884 -- None.
1885 --End of Comments
1886 -------------------------------------------------------------------------------
1887 PROCEDURE ARCHIVE_DISTRIBUTIONS(p_document_id IN NUMBER,
1888 p_document_type IN VARCHAR2,
1889 p_revision_num IN NUMBER)
1890 IS
1891 l_api_name CONSTANT VARCHAR2(30) := 'ARCHIVE_DISTRIBUTIONS';
1892 l_module VARCHAR2(100);
1893 l_progress VARCHAR2(3);
1894 l_revision_num NUMBER;
1895 l_continue BOOLEAN := FALSE;
1896
1897 -- Bug 3648552
1898 l_po_header_id PO_HEADERS_ALL.po_header_id%TYPE;
1899 l_po_release_id PO_RELEASES_ALL.po_release_id%TYPE;
1900
1901
1902 BEGIN
1903
1904 l_progress := '000';
1905 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
1906 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_PROCEDURE THEN
1907 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, l_module,
1908 'Entering ' || G_PKG_NAME || '.' || l_api_name);
1909 END IF;
1910
1911 l_progress := '010';
1912 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
1913 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
1914 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
1915 'INSERT PO_DISTRIBUTIONS_ARCHIVE');
1916 END IF;
1917
1918 INSERT INTO PO_DISTRIBUTIONS_ARCHIVE_ALL
1919 (accrual_account_id,
1920 accrue_on_receipt_flag,
1921 accrued_flag,
1922 amount_billed,
1923 amount_cancelled,
1924 amount_delivered,
1925 amount_ordered,
1926 amount_to_encumber,
1927 attribute1,
1928 attribute10,
1929 attribute11,
1930 attribute12,
1931 attribute13,
1932 attribute14,
1933 attribute15,
1934 attribute2,
1935 attribute3,
1936 attribute4,
1937 attribute5,
1938 attribute6,
1939 attribute7,
1940 attribute8,
1941 attribute9,
1942 attribute_category,
1943 award_id,
1944 bom_resource_id,
1945 budget_account_id,
1946 code_combination_id,
1947 created_by,
1948 creation_date,
1949 deliver_to_location_id,
1950 deliver_to_person_id,
1951 destination_context,
1952 destination_organization_id,
1953 destination_subinventory,
1954 destination_type_code,
1955 distribution_num,
1956 distribution_type,
1957 encumbered_amount,
1958 encumbered_flag,
1959 end_item_unit_number,
1960 expenditure_item_date,
1961 expenditure_organization_id,
1962 expenditure_type,
1963 failed_funds_lookup_code,
1964 gl_cancelled_date,
1965 gl_closed_date,
1966 gl_encumbered_date,
1967 gl_encumbered_period_name,
1968 government_context,
1969 invoice_adjustment_flag,
1970 kanban_card_id,
1971 last_update_date,
1972 last_update_login,
1973 last_updated_by,
1974 line_location_id,
1975 mrc_encumbered_amount,
1976 mrc_rate,
1977 mrc_rate_date,
1978 mrc_unencumbered_amount,
1979 nonrecoverable_tax,
1980 oke_contract_deliverable_id,
1981 oke_contract_line_id,
1982 org_id,
1983 po_distribution_id,
1984 po_header_id,
1985 po_line_id,
1986 po_release_id,
1987 prevent_encumbrance_flag,
1988 program_application_id,
1989 program_id,
1990 program_update_date,
1991 project_accounting_context,
1992 project_id,
1993 quantity_billed,
1994 quantity_cancelled,
1995 quantity_delivered,
1996 quantity_ordered,
1997 rate,
1998 rate_date,
1999 recoverable_tax,
2000 recovery_rate,
2001 req_distribution_id,
2002 req_header_reference_num,
2003 req_line_reference_num,
2004 request_id,
2005 set_of_books_id,
2006 source_distribution_id,
2007 task_id,
2008 tax_recovery_override_flag,
2009 unencumbered_amount,
2010 unencumbered_quantity,
2011 variance_account_id,
2012 wip_entity_id,
2013 wip_line_id,
2014 wip_operation_seq_num,
2015 wip_repetitive_schedule_id,
2016 wip_resource_seq_num,
2017 latest_external_flag,
2018 revision_num,
2019 -- <Complex Work R12 Start>
2020 quantity_financed,
2021 amount_financed,
2022 quantity_recouped,
2023 amount_recouped,
2024 retainage_withheld_amount,
2025 retainage_released_amount
2026 -- <Complex Work R12 End>
2027 )
2028 SELECT
2029 POD.accrual_account_id,
2030 POD.accrue_on_receipt_flag,
2031 POD.accrued_flag,
2032 POD.amount_billed,
2033 POD.amount_cancelled,
2034 POD.amount_delivered,
2035 POD.amount_ordered,
2036 POD.amount_to_encumber,
2037 POD.attribute1,
2038 POD.attribute10,
2039 POD.attribute11,
2040 POD.attribute12,
2041 POD.attribute13,
2042 POD.attribute14,
2043 POD.attribute15,
2044 POD.attribute2,
2045 POD.attribute3,
2046 POD.attribute4,
2047 POD.attribute5,
2048 POD.attribute6,
2049 POD.attribute7,
2050 POD.attribute8,
2051 POD.attribute9,
2052 POD.attribute_category,
2053 POD.award_id,
2054 POD.bom_resource_id,
2055 POD.budget_account_id,
2056 POD.code_combination_id,
2057 POD.created_by,
2058 POD.creation_date,
2059 POD.deliver_to_location_id,
2060 POD.deliver_to_person_id,
2061 POD.destination_context,
2062 POD.destination_organization_id,
2063 POD.destination_subinventory,
2064 POD.destination_type_code,
2065 POD.distribution_num,
2066 POD.distribution_type,
2067 POD.encumbered_amount,
2068 POD.encumbered_flag,
2069 POD.end_item_unit_number,
2070 POD.expenditure_item_date,
2071 POD.expenditure_organization_id,
2072 POD.expenditure_type,
2073 POD.failed_funds_lookup_code,
2074 POD.gl_cancelled_date,
2075 POD.gl_closed_date,
2076 POD.gl_encumbered_date,
2077 POD.gl_encumbered_period_name,
2078 POD.government_context,
2079 POD.invoice_adjustment_flag,
2080 POD.kanban_card_id,
2081 POD.last_update_date,
2082 POD.last_update_login,
2083 POD.last_updated_by,
2084 POD.line_location_id,
2085 POD.mrc_encumbered_amount,
2086 POD.mrc_rate,
2087 POD.mrc_rate_date,
2088 POD.mrc_unencumbered_amount,
2089 POD.nonrecoverable_tax,
2090 POD.oke_contract_deliverable_id,
2091 POD.oke_contract_line_id,
2092 POD.org_id,
2093 POD.po_distribution_id,
2094 POD.po_header_id,
2095 POD.po_line_id,
2096 POD.po_release_id,
2097 POD.prevent_encumbrance_flag,
2098 POD.program_application_id,
2099 POD.program_id,
2100 POD.program_update_date,
2101 POD.project_accounting_context,
2102 POD.project_id,
2103 POD.quantity_billed,
2104 POD.quantity_cancelled,
2105 POD.quantity_delivered,
2106 POD.quantity_ordered,
2107 POD.rate,
2108 POD.rate_date,
2109 POD.recoverable_tax,
2110 POD.recovery_rate,
2111 POD.req_distribution_id,
2112 POD.req_header_reference_num,
2113 POD.req_line_reference_num,
2114 POD.request_id,
2115 POD.set_of_books_id,
2116 POD.source_distribution_id,
2117 POD.task_id,
2118 POD.tax_recovery_override_flag,
2119 POD.unencumbered_amount,
2120 POD.unencumbered_quantity,
2121 POD.variance_account_id,
2122 POD.wip_entity_id,
2123 POD.wip_line_id,
2124 POD.wip_operation_seq_num,
2125 POD.wip_repetitive_schedule_id,
2126 POD.wip_resource_seq_num,
2127 'Y',
2128 p_revision_num,
2129 -- <Complex Work R12 Start>
2130 POD.quantity_financed,
2131 POD.amount_financed,
2132 POD.quantity_recouped,
2133 POD.amount_recouped,
2134 POD.retainage_withheld_amount,
2135 POD.retainage_released_amount
2136 -- <Complex Work R12 End>
2137 FROM PO_DISTRIBUTIONS_ALL POD,
2138 PO_DISTRIBUTIONS_ARCHIVE_ALL PODA
2139 WHERE ((p_document_type = 'RELEASE' AND
2140 POD.po_release_id = p_document_id) OR
2141 (p_document_type <> 'RELEASE' AND -- Bug 3210749
2142 POD.po_header_id = p_document_id AND
2143 POD.po_release_id IS NULL))
2144 AND POD.po_distribution_id = PODA.po_distribution_id (+)
2145 AND PODA.latest_external_flag (+) = 'Y'
2146 AND ((PODA.po_distribution_id is NULL) OR
2147 -- <Bug 4723667 start Removing the check for amount billed in version 120.9>
2148 (POD.amount_ordered IS NULL AND PODA.amount_ordered IS NOT NULL OR
2149 POD.amount_ordered IS NOT NULL AND PODA.amount_ordered IS NULL OR
2150 POD.amount_ordered <> PODA.amount_ordered) OR
2151 -- <Bug 3862108 START>
2152 (POD.amount_cancelled IS NULL AND PODA.amount_cancelled IS NOT NULL OR
2153 POD.amount_cancelled IS NOT NULL AND PODA.amount_cancelled IS NULL OR
2154 POD.amount_cancelled <> PODA.amount_cancelled) OR
2155 -- <Bug 3862108 END>
2156 (POD.deliver_to_person_id IS NULL AND PODA.deliver_to_person_id IS NOT NULL OR
2157 POD.deliver_to_person_id IS NOT NULL AND PODA.deliver_to_person_id IS NULL OR
2158 POD.deliver_to_person_id <> PODA.deliver_to_person_id) OR
2159 (POD.distribution_num IS NULL AND PODA.distribution_num IS NOT NULL OR
2160 POD.distribution_num IS NOT NULL AND PODA.distribution_num IS NULL OR
2161 POD.distribution_num <> PODA.distribution_num) OR
2162 -- <Bug 4723667 start Removing the check for quantity billed in version 120.9>
2163 (POD.quantity_ordered IS NULL AND PODA.quantity_ordered IS NOT NULL OR
2164 POD.quantity_ordered IS NOT NULL AND PODA.quantity_ordered IS NULL OR
2165 POD.quantity_ordered <> PODA.quantity_ordered) OR
2166 -- <Complex Work R12 Start>
2167 (POD.quantity_financed IS NULL AND PODA.quantity_financed IS NOT NULL OR
2168 POD.quantity_financed IS NOT NULL AND PODA.quantity_financed IS NULL OR
2169 POD.quantity_financed <> PODA.quantity_financed) OR
2170 (POD.amount_financed IS NULL AND PODA.amount_financed IS NOT NULL OR
2171 POD.amount_financed IS NOT NULL AND PODA.amount_financed IS NULL OR
2172 POD.amount_financed <> PODA.amount_financed) OR
2173 (POD.quantity_recouped IS NULL AND PODA.quantity_recouped IS NOT NULL OR
2174 POD.quantity_recouped IS NOT NULL AND PODA.quantity_recouped IS NULL OR
2175 POD.quantity_recouped <> PODA.quantity_recouped) OR
2176 (POD.amount_recouped IS NULL AND PODA.amount_recouped IS NOT NULL OR
2177 POD.amount_recouped IS NOT NULL AND PODA.amount_recouped IS NULL OR
2178 POD.amount_recouped <> PODA.amount_recouped) OR
2179 (POD.retainage_withheld_amount IS NULL AND PODA.retainage_withheld_amount IS NOT NULL OR
2180 POD.retainage_withheld_amount IS NOT NULL AND PODA.retainage_withheld_amount IS NULL OR
2181 POD.retainage_withheld_amount <> PODA.retainage_withheld_amount) OR
2182 (POD.retainage_released_amount IS NULL AND PODA.retainage_released_amount IS NOT NULL OR
2183 POD.retainage_released_amount IS NOT NULL AND PODA.retainage_released_amount IS NULL OR
2184 POD.retainage_released_amount <> PODA.retainage_released_amount) OR
2185 -- <Complex Work R12 End>
2186 -- <Bug 3862108 START>
2187 (POD.quantity_cancelled IS NULL AND PODA.quantity_cancelled IS NOT NULL OR
2188 POD.quantity_cancelled IS NOT NULL AND PODA.quantity_cancelled IS NULL OR
2189 POD.quantity_cancelled <> PODA.quantity_cancelled) OR
2190 -- <Bug 3862108 END>
2191 -- <Bug 3191712 START>
2192 (POD.nonrecoverable_tax IS NULL AND PODA.nonrecoverable_tax IS NOT NULL OR
2193 POD.nonrecoverable_tax IS NOT NULL AND PODA.nonrecoverable_tax IS NULL OR
2194 POD.nonrecoverable_tax <> PODA.nonrecoverable_tax) OR
2195 (POD.recoverable_tax IS NULL AND PODA.recoverable_tax IS NOT NULL OR
2196 POD.recoverable_tax IS NOT NULL AND PODA.recoverable_tax IS NULL OR
2197 POD.recoverable_tax <> PODA.recoverable_tax) OR
2198 -- <Bug 3191712 END>
2199 (POD.recovery_rate IS NULL AND PODA.recovery_rate IS NOT NULL OR --BUG7286203
2200 POD.recovery_rate IS NOT NULL AND PODA.recovery_rate IS NULL OR
2201 POD.recovery_rate <> PODA.recovery_rate)
2202 );
2203
2204 l_continue := (SQL%ROWCOUNT > 0);
2205
2206 IF l_continue THEN
2207 l_progress := '020';
2208 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
2209 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
2210 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
2211 'Update PO_DISTRIBUTIONS_ARCHIVE to reset latest_external_flag');
2212 END IF;
2213
2214 -- Bug 3648552 START
2215 -- Get PO_HEADER_ID for Release since there is no index on
2216 -- PO_DISTRIBUTIONS_ARCHIVE_ALL.po_release_id
2217 IF (p_document_type = 'RELEASE') THEN
2218 SELECT po_header_id
2219 INTO l_po_header_id
2220 FROM po_releases_all
2221 WHERE po_release_id = p_document_id;
2222 l_po_release_id := p_document_id;
2223 ELSE
2224 l_po_header_id := p_document_id;
2225 l_po_release_id := NULL;
2226 END IF;
2227 -- Bug 3648552 END
2228
2229 -- If a row was inserted into PO_DISTRIBUTIONS_ARCHIVE, then set the appropriate flags
2230 UPDATE PO_DISTRIBUTIONS_ARCHIVE_ALL POD1
2231 SET latest_external_flag = 'N'
2232 -- Bug 3648552 START
2233 WHERE po_header_id = l_po_header_id
2234 -- Bug 3713788: fixed regression caused by bug 3648552
2235 AND NVL(po_release_id, -99) = NVL(l_po_release_id, -99)
2236 -- Bug 3648552 END
2237 AND latest_external_flag = 'Y'
2238 AND revision_num < p_revision_num
2239 AND EXISTS
2240 (SELECT 'A new archived row'
2241 FROM PO_DISTRIBUTIONS_ARCHIVE_ALL POD2
2242 WHERE POD2.po_distribution_id = POD1.po_distribution_id
2243 AND POD2.latest_external_flag = 'Y'
2244 AND POD2.revision_num = p_revision_num);
2245 ELSE
2246 l_progress := '030';
2247 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
2248 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
2249 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
2250 'No need to reset latest_external_flag');
2251 END IF;
2252 END IF; /* IF l_continue */
2253
2254 l_progress := '030';
2255
2256 EXCEPTION
2257 WHEN OTHERS THEN
2258 po_message_s.sql_error('Exception of ARCHIVE_DISTRIBUTIONS()',
2259 l_progress , sqlcode);
2260 FND_MSG_PUB.Add;
2261 IF (G_FND_DEBUG = 'Y') THEN
2262 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
2263 FND_LOG.string(FND_LOG.level_unexpected, l_module,
2264 'EXCEPTION: '||sqlerrm);
2265 END IF;
2266 END IF;
2267 RAISE;
2268 END ARCHIVE_DISTRIBUTIONS;
2269
2270 -------------------------------------------------------------------------------
2271 --Start of Comments
2272 --Name: ARCHIVE_ORG_ASSIGNMENTS
2273 --Pre-reqs:
2274 -- None.
2275 --Modifies:
2276 -- PO_GA_ORG_ASSIGNMENTS_ARCHIVE
2277 --Locks:
2278 -- None.
2279 --Function:
2280 -- Arcives the global agreement org assignments.
2281 --Parameters:
2282 --IN:
2283 --p_document_id
2284 -- The id of the document that needs to be archived.
2285 --p_revision_num
2286 -- The revision of the document that needs to be archived.
2287 --Testing:
2288 -- None.
2289 --End of Comments
2290 -------------------------------------------------------------------------------
2291 PROCEDURE ARCHIVE_ORG_ASSIGNMENTS(p_document_id IN NUMBER,
2292 p_revision_num IN NUMBER)
2293 IS
2294 l_api_name CONSTANT VARCHAR2(30) := 'ARCHIVE_ORG_ASSIGNMENTS';
2295 l_module VARCHAR2(100);
2296 l_progress VARCHAR2(3);
2297 l_revision_num NUMBER;
2298 l_continue BOOLEAN := FALSE;
2299
2300 BEGIN
2301
2302 l_progress := '000';
2303 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
2304 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_PROCEDURE THEN
2305 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, l_module,
2306 'Entering ' || G_PKG_NAME || '.' || l_api_name);
2307 END IF;
2308
2309 l_progress := '010';
2310 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
2311 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
2312 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
2313 'INSERT PO_GA_ORG_ASSIGNMENTS_ARCHIVE');
2314 END IF;
2315
2316 INSERT INTO PO_GA_ORG_ASSIGNMENTS_ARCHIVE
2317 (org_assignment_id , --<HTML Agreement R12>
2318 created_by ,
2319 creation_date ,
2320 enabled_flag ,
2321 last_updated_by ,
2322 last_update_date ,
2323 last_update_login ,
2324 organization_id ,
2325 po_header_id ,
2326 purchasing_org_id ,
2327 vendor_site_id ,
2328 latest_external_flag ,
2329 revision_num )
2330 SELECT
2331 POG.org_assignment_id , --<HTML Agreement R12>
2332 POG.created_by ,
2333 POG.creation_date ,
2334 POG.enabled_flag ,
2335 POG.last_updated_by ,
2336 POG.last_update_date ,
2337 POG.last_update_login ,
2338 POG.organization_id ,
2339 POG.po_header_id ,
2340 POG.purchasing_org_id ,
2341 POG.vendor_site_id ,
2342 'Y' ,
2343 p_revision_num
2344 FROM PO_GA_ORG_ASSIGNMENTS POG,
2345 PO_GA_ORG_ASSIGNMENTS_ARCHIVE POGA
2346 WHERE POG.po_header_id = p_document_id
2347 --AND POG.po_header_id = POGA.po_header_id (+)
2348 --AND POG.organization_id = POGA.organization_id (+)
2349 AND POG.org_assignment_id = POGA.org_assignment_id (+) --<HTML Agreement R12>
2350 AND POGA.latest_external_flag (+) = 'Y'
2351 AND ((POGA.po_header_id is NULL) OR
2352 (POG.enabled_flag IS NULL AND POGA.enabled_flag IS NOT NULL OR
2353 POG.enabled_flag IS NOT NULL AND POGA.enabled_flag IS NULL OR
2354 POG.enabled_flag <> POGA.enabled_flag) OR
2355 (POG.purchasing_org_id IS NULL AND POGA.purchasing_org_id IS NOT NULL OR
2356 POG.purchasing_org_id IS NOT NULL AND POGA.purchasing_org_id IS NULL OR
2357 POG.purchasing_org_id <> POGA.purchasing_org_id) OR
2358 (POG.vendor_site_id IS NULL AND POGA.vendor_site_id IS NOT NULL OR
2359 POG.vendor_site_id IS NOT NULL AND POGA.vendor_site_id IS NULL OR
2360 POG.vendor_site_id <> POGA.vendor_site_id));
2361
2362 l_continue := (SQL%ROWCOUNT > 0);
2363
2364 IF l_continue THEN
2365 l_progress := '020';
2366 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
2367 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
2368 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
2369 'Update PO_GA_ORG_ASSIGNMENTS_ARCHIVE to reset latest_external_flag');
2370 END IF;
2371
2372 -- If a row was inserted into PO_GA_ORG_ASSIGNMENTS_ARCHIVE, then set the appropriate flags
2373 UPDATE PO_GA_ORG_ASSIGNMENTS_ARCHIVE POG1
2374 SET latest_external_flag = 'N'
2375 WHERE po_header_id = p_document_id
2376 AND latest_external_flag = 'Y'
2377 AND revision_num < p_revision_num
2378 AND EXISTS
2379 (SELECT 'A new archived row'
2380 FROM PO_GA_ORG_ASSIGNMENTS_ARCHIVE POG2
2381 WHERE POG2.org_assignment_id = POG1.org_assignment_id --<HTML Agreement R12>
2382 -- POG2.po_header_id = POG1.po_header_id
2383 --AND POG2.organization_id = POG1.organization_id
2384 AND POG2.latest_external_flag = 'Y'
2385 AND POG2.revision_num = p_revision_num);
2386 ELSE
2387 l_progress := '030';
2388 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
2389 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
2390 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
2391 'No need to reset latest_external_flag');
2392 END IF;
2393 END IF; /* IF l_continue */
2394
2395 l_progress := '030';
2396
2397 EXCEPTION
2398 WHEN OTHERS THEN
2399 po_message_s.sql_error('Exception of ARCHIVE_ORG_ASSIGNMENTS()',
2400 l_progress , sqlcode);
2401 FND_MSG_PUB.Add;
2402 IF (G_FND_DEBUG = 'Y') THEN
2403 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
2404 FND_LOG.string(FND_LOG.level_unexpected, l_module,
2405 'EXCEPTION: '||sqlerrm);
2406 END IF;
2407 END IF;
2408 RAISE;
2409 END ARCHIVE_ORG_ASSIGNMENTS;
2410
2411 -------------------------------------------------------------------------------
2412 --Start of Comments
2413 --Name: ARCHIVE_PRICE_DIFFS
2414 --Pre-reqs:
2415 -- None.
2416 --Modifies:
2417 -- PO_PRICE_DIFFERENTIALS_ARCHIVE
2418 --Locks:
2419 -- None.
2420 --Function:
2421 -- Arcives the price differentials.
2422 --Parameters:
2423 --IN:
2424 --p_document_id
2425 -- The id of the document that needs to be archived.
2426 --p_entity_type
2427 -- The entity type of the document that needs to be archived.
2428 --p_revision_num
2429 -- The revision of the document that needs to be archived.
2430 --Testing:
2431 -- None.
2432 --End of Comments
2433 -------------------------------------------------------------------------------
2434 PROCEDURE ARCHIVE_PRICE_DIFFS(p_document_id IN NUMBER,
2435 p_entity_type IN VARCHAR,
2436 p_revision_num IN NUMBER)
2437 IS
2438 l_api_name CONSTANT VARCHAR2(30) := 'ARCHIVE_PRICE_DIFFS';
2439 l_module VARCHAR2(100);
2440 l_progress VARCHAR2(3);
2441 l_continue BOOLEAN := FALSE;
2442
2443 BEGIN
2444
2445 l_progress := '000';
2446 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
2447 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_PROCEDURE THEN
2448 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, l_module,
2449 'Entering ' || G_PKG_NAME || '.' || l_api_name);
2450 END IF;
2451
2452 l_progress := '010';
2453 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
2454 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
2455 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
2456 'INSERT PO_PRICE_DIFFERENTIALS_ARCHIVE');
2457 END IF;
2458
2459 INSERT INTO PO_PRICE_DIFFERENTIALS_ARCHIVE
2460 (created_by ,
2461 creation_date ,
2462 enabled_flag ,
2463 entity_id ,
2464 entity_type ,
2465 last_update_date ,
2466 last_update_login ,
2467 last_updated_by ,
2468 max_multiplier ,
2469 min_multiplier ,
2470 multiplier ,
2471 price_differential_id ,
2472 price_differential_num ,
2473 price_type ,
2474 latest_external_flag ,
2475 revision_num )
2476 SELECT
2477 POR.created_by ,
2478 POR.creation_date ,
2479 POR.enabled_flag ,
2480 POR.entity_id ,
2481 POR.entity_type ,
2482 POR.last_update_date ,
2483 POR.last_update_login ,
2484 POR.last_updated_by ,
2485 POR.max_multiplier ,
2486 POR.min_multiplier ,
2487 POR.multiplier ,
2488 POR.price_differential_id ,
2489 POR.price_differential_num ,
2490 POR.price_type ,
2491 'Y' ,
2492 p_revision_num
2493 FROM PO_PRICE_DIFFERENTIALS POR,
2494 PO_PRICE_DIFFERENTIALS_ARCHIVE PORA
2495 WHERE ((p_entity_type = 'PRICE BREAK' AND
2496 POR.entity_id IN (SELECT line_location_id
2497 FROM PO_LINE_LOCATIONS_ALL
2498 WHERE po_header_id = p_document_id)) OR
2499 (p_entity_type IN ('PO LINE', 'BLANKET LINE') AND
2500 POR.entity_id IN (SELECT po_line_id
2501 FROM PO_LINES_ALL
2502 WHERE po_header_id = p_document_id)))
2503 AND POR.entity_type = p_entity_type
2504 AND POR.price_differential_id = PORA.price_differential_id (+)
2505 AND PORA.latest_external_flag (+) = 'Y'
2506 AND ((PORA.price_differential_id is NULL) OR
2507 (POR.enabled_flag IS NULL AND PORA.enabled_flag IS NOT NULL OR
2508 POR.enabled_flag IS NOT NULL AND PORA.enabled_flag IS NULL OR
2509 POR.enabled_flag <> PORA.enabled_flag) OR
2510 (POR.price_type IS NULL AND PORA.price_type IS NOT NULL OR
2511 POR.price_type IS NOT NULL AND PORA.price_type IS NULL OR
2512 POR.price_type <> PORA.price_type) OR
2513 (POR.min_multiplier IS NULL AND PORA.min_multiplier IS NOT NULL OR
2514 POR.min_multiplier IS NOT NULL AND PORA.min_multiplier IS NULL OR
2515 POR.min_multiplier <> PORA.min_multiplier) OR
2516 (POR.max_multiplier IS NULL AND PORA.max_multiplier IS NOT NULL OR
2517 POR.max_multiplier IS NOT NULL AND PORA.max_multiplier IS NULL OR
2518 POR.max_multiplier <> PORA.max_multiplier) OR
2519 (POR.multiplier IS NULL AND PORA.multiplier IS NOT NULL OR
2520 POR.multiplier IS NOT NULL AND PORA.multiplier IS NULL OR
2521 POR.multiplier <> PORA.multiplier) OR
2522 (POR.price_differential_num IS NULL AND PORA.price_differential_num IS NOT NULL OR --BUG7286203
2523 POR.price_differential_num IS NOT NULL AND PORA.price_differential_num IS NULL OR
2524 POR.price_differential_num <> PORA.price_differential_num));
2525
2526 l_continue := (SQL%ROWCOUNT > 0);
2527
2528 IF l_continue THEN
2529 l_progress := '020';
2530 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
2531 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
2532 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
2533 'Update PO_PRICE_DIFFERENTIALS_ARCHIVE to reset latest_external_flag');
2534 END IF;
2535
2536 -- If a row was inserted into PO_PRICE_DIFFERENTIALS_ARCHIVE, then set the appropriate flags
2537 UPDATE PO_PRICE_DIFFERENTIALS_ARCHIVE POR1
2538 SET latest_external_flag = 'N'
2539 WHERE ((p_entity_type = 'PRICE BREAK' AND
2540 entity_id IN (SELECT line_location_id
2541 FROM PO_LINE_LOCATIONS_ALL
2542 WHERE po_header_id = p_document_id)) OR
2543 (p_entity_type IN ('PO LINE', 'BLANKET LINE') AND
2544 entity_id IN (SELECT po_line_id
2545 FROM PO_LINES_ALL
2546 WHERE po_header_id = p_document_id)))
2547 AND entity_type = p_entity_type
2548 AND latest_external_flag = 'Y'
2549 AND revision_num < p_revision_num
2550 AND EXISTS
2551 (SELECT 'A new archived row'
2552 FROM PO_PRICE_DIFFERENTIALS_ARCHIVE POR2
2553 WHERE POR2.price_differential_id = POR1.price_differential_id
2554 AND POR2.latest_external_flag = 'Y'
2555 AND POR2.revision_num = p_revision_num);
2556 ELSE
2557 l_progress := '030';
2558 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
2559 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
2560 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
2561 'No need to reset latest_external_flag');
2562 END IF;
2563 END IF; /* IF l_continue */
2564
2565 l_progress := '030';
2566
2567 EXCEPTION
2568 WHEN OTHERS THEN
2569 po_message_s.sql_error('Exception of ARCHIVE_PRICE_DIFFS()',
2570 l_progress , sqlcode);
2571 FND_MSG_PUB.Add;
2572 IF (G_FND_DEBUG = 'Y') THEN
2573 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
2574 FND_LOG.string(FND_LOG.level_unexpected, l_module,
2575 'EXCEPTION: '||sqlerrm);
2576 END IF;
2577 END IF;
2578 RAISE;
2579 END ARCHIVE_PRICE_DIFFS;
2580
2581
2582 -------------------------------------------------------------------------------
2583 --Start of Comments
2584 --Name: ARCHIVE_CONTRACT_TERMS
2585 --Pre-reqs:
2586 -- None.
2587 --Modifies:
2588 -- OKC Tables
2589 --Locks:
2590 -- None.
2591 --Function:
2592 -- Call OKC procedure to arcive the contract terms
2593 --Parameters:
2594 --IN:
2595 --p_document_id
2596 -- The id of the document that needs to be archived.
2597 --p_document_type
2598 -- The entity type of the document that needs to be archived.
2599 --p_document_subtype
2600 -- The entity subtype of the document that needs to be archived.
2601 --p_revision_num
2602 -- The revision of the document that needs to be archived.
2603 --Testing:
2604 -- None.
2605 --End of Comments
2606 -------------------------------------------------------------------------------
2607 PROCEDURE ARCHIVE_CONTRACT_TERMS(p_document_id IN NUMBER,
2608 p_document_type IN VARCHAR,
2609 p_document_subtype IN VARCHAR,
2610 p_revision_num IN NUMBER)
2611 IS
2612 l_api_name CONSTANT VARCHAR2(30) := 'ARCHIVE_CONTRACT_TERMS';
2613 l_module VARCHAR2(100);
2614 l_progress VARCHAR2(3);
2615 l_return_status VARCHAR2(1);
2616 l_msg_count NUMBER;
2617 l_msg_data VARCHAR2(2000);
2618 l_conterms_exist_flag PO_HEADERS_ALL.conterms_exist_flag%TYPE;
2619 l_pending_signature_flag PO_HEADERS_ALL.pending_signature_flag%TYPE;
2620 l_clear_amendment VARCHAR2(1);
2621
2622
2623 BEGIN
2624
2625 l_progress := '000';
2626 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
2627 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_PROCEDURE THEN
2628 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, l_module,
2629 'Entering ' || G_PKG_NAME || '.' || l_api_name);
2630 END IF;
2631
2632 l_progress := '010';
2633 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
2634 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
2635 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
2636 'Select conterms_exist_flag - po_header_id: '||p_document_id);
2637 END IF;
2638 -- SQL What:Find out if contract terms exist
2639 -- SQL Why :Archive Contract Terms if needed
2640 SELECT NVL(conterms_exist_flag, 'N'),
2641 NVL(pending_signature_flag, 'N')
2642 INTO l_conterms_exist_flag,
2643 l_pending_signature_flag
2644 FROM po_headers_all
2645 WHERE po_header_id = p_document_id;
2646
2647 IF (l_conterms_exist_flag = 'Y') THEN
2648 l_progress := '020';
2649 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
2650 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
2651 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
2652 'OKC_TERMS_VERSION_GRP.version_doc() p_document_id: '||
2653 p_document_id||', p_revision_num: '||p_revision_num);
2654 END IF;
2655
2656 -- Bug 3616320 START
2657 -- Always call the OKC_TERMS_VERSION_GRP.VERSION_DOC with
2658 -- p_clear_amendments = 'N' to version the document
2659 -- IF l_pending_signature_flag <> 'Y' THEN
2660 -- l_clear_amendment := 'Y';
2661 -- ELSE
2662 -- l_clear_amendment := 'N';
2663 -- END IF; /*IF l_pending_signature_flag <> 'Y'*/
2664 l_clear_amendment := 'N';
2665 -- Bug 3616320 END
2666
2667 OKC_TERMS_VERSION_GRP.version_doc(p_api_version => 1.0,
2668 p_doc_id => p_document_id,
2669 p_doc_type => p_document_type||'_'||
2670 p_document_subtype,
2671 p_version_number => p_revision_num,
2672 p_clear_amendment => l_clear_amendment,
2673 x_return_status => l_return_status,
2674 x_msg_data => l_msg_data,
2675 x_msg_count => l_msg_count,
2676 p_include_gen_attach => 'N' );
2677
2678 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) Then
2679 IF (l_return_status = FND_API.G_RET_STS_ERROR) Then
2680 RAISE FND_API.G_EXC_ERROR;
2681 ELSE
2682 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2683 END IF; /* IF (l_return_status = FND_API.G_RET_STS_ERROR) */
2684 END IF; /* IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) */
2685
2686 IF (l_pending_signature_flag <> 'Y') THEN
2687
2688 l_progress := '030';
2689 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
2690 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
2691 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
2692 'PO_CONTERMS_WF_PVT.update_contract_terms() p_po_header_id: '||
2693 p_document_id);
2694 END IF;
2695 -- Activate Contract Terms Deliverables now that PO revision is archived
2696 --
2697 -- Inform Contracts to activate deliverable, now that PO is successfully
2698 -- archived
2699 PO_CONTERMS_WF_PVT.UPDATE_CONTRACT_TERMS(
2700 p_po_header_id => p_document_id,
2701 p_signed_date => SYSDATE,
2702 x_return_status => l_return_status,
2703 x_msg_data => l_msg_data,
2704 x_msg_count => l_msg_count);
2705
2706 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) Then
2707 IF (l_return_status = FND_API.G_RET_STS_ERROR) Then
2708 RAISE FND_API.G_EXC_ERROR;
2709 ELSE
2710 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2711 END IF; /* IF (l_return_status = FND_API.G_RET_STS_ERROR) */
2712 END IF; /* IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) */
2713
2714 END IF; /*IF (l_pending_signature_flag <> 'Y')*/
2715
2716 END IF; /*IF (l_conterms_exist_flag = 'Y')*/
2717
2718 l_progress := '050';
2719
2720 EXCEPTION
2721 WHEN OTHERS THEN
2722 po_message_s.sql_error('Exception of ARCHIVE_CONTRACT_TERMS()',
2723 l_progress , sqlcode);
2724 FND_MSG_PUB.Add;
2725 IF (G_FND_DEBUG = 'Y') THEN
2726 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
2727 FND_LOG.string(FND_LOG.level_unexpected, l_module,
2728 'EXCEPTION: '||sqlerrm);
2729 END IF;
2730 END IF;
2731 RAISE;
2732 END ARCHIVE_CONTRACT_TERMS;
2733
2734
2735 -------------------------------------------------------------------------------
2736 --Start of Comments
2737 --Name: archive_po
2738 --Pre-reqs:
2739 -- None.
2740 --Modifies:
2741 -- None.
2742 --Locks:
2743 -- None.
2744 --Function:
2745 -- Arcives the document. Inserts an copy of the document in the
2746 -- archive tables.
2747 --Parameters:
2748 --IN:
2749 --p_api_version
2750 -- Version number of API that caller expects. It should match the
2751 -- l_api_version defined in the procedure (expected value : 1.0)
2752 --p_document_id
2753 -- The id of the document that needs to be archived.
2754 --p_document_type
2755 -- The type of the document to archive
2756 -- PO : For Standard/Planned
2757 -- PA : For Blanket/Contract
2758 -- RELEASE : Release
2759 --p_document_subtype
2760 -- The subtype of the document.
2761 -- Valid Document types and Document subtypes are
2762 -- Document Type Document Subtype
2763 -- RELEASE ---> SCHEDULED/BLANKET
2764 -- PO ---> PLANNED/STANDARD
2765 -- PA ---> CONTRACT/BLANKET
2766 --OUT:
2767 --x_return_status
2768 -- FND_API.G_RET_STS_SUCCESS if API succeeds
2769 -- FND_API.G_RET_STS_ERROR if API fails
2770 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
2771 --x_msg_count
2772 -- returns count of messages in the stack.
2773 --x_msg_data
2774 -- Contains error msg in case x_return_status returned
2775 -- FND_API.G_RET_STS_ERROR or FND_API.G_RET_STS_UNEXP_ERROR
2776 --Testing:
2777 -- None.
2778 --End of Comments
2779 -------------------------------------------------------------------------------
2780 /*
2781 * Set a savepoint;
2782 *
2783 * Check if the current revision is already archived.
2784 * Case entity.document_type is
2785 * When PO
2786 * Case entity.document_subtype is
2787 * When STANDARD or PLANNED
2788 * archive PO_HEADERS
2789 * when modified archive PO_LINES, PO_LINE_LOCATIONS,
2790 * PO_PRICE_DIFFERENTIALS and PO_DISTRIBUTIONS.
2791 * End Case
2792 * When PA
2793 * Case entity.document_subtype is
2794 * When BLANKET
2795 * archive PO_HEADERS
2796 * when modified archive PO_LINES, PO_LINE_LOCATIONS, PO_PRICE_DIFFERENTIALS
2797 * (for price breaks)
2798 * When CONTRACT
2799 * archive PO_HEADERS
2800 * End Case
2801 * If global_agreement_flag = Y (i.e. global blanket or global contract) --<BUG 3290647>
2802 * When modified, archive PO_GA_ORG_ASSIGNMENT
2803 * When RELEASE
2804 * archive PO_RELEASES
2805 * when modified archive PO_LINE_LOCATIONS and PO_DISTRIBUTIONS.
2806 * End Case
2807 *
2808 * IF error happens, rollback to the savepoint;
2809 *
2810 */
2811
2812 PROCEDURE archive_po(p_api_version IN NUMBER,
2813 p_document_id IN NUMBER,
2814 p_document_type IN VARCHAR2,
2815 p_document_subtype IN VARCHAR2,
2816 x_return_status OUT NOCOPY VARCHAR2,
2817 x_msg_count OUT NOCOPY NUMBER,
2818 x_msg_data OUT NOCOPY VARCHAR2)
2819 IS
2820 l_api_name CONSTANT VARCHAR2(30) := 'ARCHIVE_PO';
2821 l_api_version CONSTANT NUMBER := 1.0;
2822 l_module VARCHAR2(100);
2823 l_progress VARCHAR2(3);
2824 l_revision_num NUMBER;
2825 l_return_status VARCHAR2(1);
2826 l_ga_flag PO_HEADERS_ALL.global_agreement_flag%TYPE;
2827
2828 BEGIN
2829
2830 -- Standard call to check for call compatibility
2831 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME)
2832 THEN
2833 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2834 END IF;
2835
2836 l_progress := '000';
2837 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
2838 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_PROCEDURE THEN
2839 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, l_module,
2840 'Entering ' || G_PKG_NAME || '.' || l_api_name);
2841 END IF;
2842
2843 l_progress := '010';
2844 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
2845 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
2846 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
2847 'Set SavePoint');
2848 END IF;
2849
2850 --Standard Start API savepoint
2851 SAVEPOINT PO_ARCHIVE_SP;
2852
2853 l_progress := '020';
2854 IF (p_document_type = 'PO') THEN
2855 l_progress := '030';
2856 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
2857 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
2858 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
2859 'check_po_archive(PO) p_document_id: '||p_document_id);
2860 END IF;
2861 check_po_archive(p_document_id, l_revision_num, l_return_status);
2862
2863 IF (l_return_status = 'Y') THEN
2864 l_progress := '040';
2865 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
2866 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
2867 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
2868 'archive_header() p_document_id: '||p_document_id);
2869 END IF;
2870 archive_header(p_document_id);
2871
2872 l_progress := '050';
2873 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
2874 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
2875 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
2876 'archive_lines() p_document_id: '||p_document_id||
2877 ', l_revision_num: '||l_revision_num);
2878 END IF;
2879 archive_lines(p_document_id, l_revision_num);
2880
2881 l_progress := '060';
2882 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
2883 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
2884 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
2885 'archive_price_diffs() p_document_id: '||p_document_id||
2886 ', p_entity_type: PO LINE'||
2887 ', l_revision_num: '||l_revision_num);
2888 END IF;
2889 archive_price_diffs(p_document_id, 'PO LINE', l_revision_num);
2890
2891 l_progress := '070';
2892 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
2893 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
2894 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
2895 'archive_line_locations() p_document_id: '||p_document_id||
2896 ', p_document_type: '||p_document_type||
2897 ', l_revision_num: '||l_revision_num);
2898 END IF;
2899 archive_line_locations(p_document_id, p_document_type, l_revision_num);
2900
2901 l_progress := '080';
2902 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
2903 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
2904 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
2905 'archive_distributions() p_document_id: '||p_document_id||
2906 ', p_document_type: '||p_document_type||
2907 ', l_revision_num: '||l_revision_num);
2908 END IF;
2909 archive_distributions(p_document_id, p_document_type, l_revision_num);
2910
2911 l_progress := '090';
2912 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
2913 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
2914 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
2915 'archive_contract_terms() p_document_id: '||p_document_id||
2916 ', p_document_type: '||p_document_type||
2917 ', p_document_subtype: '||p_document_subtype||
2918 ', p_revision_num: '||l_revision_num);
2919 END IF;
2920 archive_contract_terms(p_document_id, p_document_type,
2921 p_document_subtype, l_revision_num);
2922
2923 END IF; /*IF (l_return_status = 'Y')*/
2924 ELSIF (p_document_type = 'PA') THEN
2925 l_progress := '100';
2926 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
2927 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
2928 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
2929 'check_po_archive(PA) p_document_id: '||p_document_id);
2930 END IF;
2931 check_po_archive(p_document_id, l_revision_num, l_return_status);
2932
2933 IF (l_return_status = 'Y') THEN
2934 l_progress := '110';
2935 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
2936 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
2937 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
2938 'archive_header() p_document_id: '||p_document_id);
2939 END IF;
2940 archive_header(p_document_id);
2941
2942 IF (p_document_subtype = 'BLANKET') THEN
2943 l_progress := '120';
2944 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
2945 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
2946 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
2947 'archive_lines() p_document_id: '||p_document_id||
2948 ', l_revision_num: '||l_revision_num);
2949 END IF;
2950 archive_lines(p_document_id, l_revision_num);
2951
2952 --<Unified catalog R12: Start>
2953 -- Archive the Attribute Values and TLP rows (for BPA/GBPA only)
2954 archive_attribute_values
2955 (
2956 p_po_header_id => p_document_id
2957 , p_revision_num => l_revision_num
2958 );
2959
2960 archive_attr_values_tlp
2961 (
2962 p_po_header_id => p_document_id
2963 , p_revision_num => l_revision_num
2964 );
2965 --<Unified catalog R12: End>
2966
2967 l_progress := '130';
2968 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
2969 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
2970 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
2971 'archive_price_diffs() p_document_id: '||p_document_id||
2972 ', p_entity_type: BLANKET LINE'||
2973 ', l_revision_num: '||l_revision_num);
2974 END IF;
2975 archive_price_diffs(p_document_id, 'BLANKET LINE', l_revision_num);
2976
2977
2978 l_progress := '140';
2979 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
2980 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
2981 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
2982 'archive_line_locations() p_document_id: '||p_document_id||
2983 ', p_document_type: '||p_document_type||
2984 ', l_revision_num: '||l_revision_num);
2985 END IF;
2986 archive_line_locations(p_document_id, p_document_type, l_revision_num);
2987
2988 l_progress := '150';
2989 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
2990 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
2991 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
2992 'archive_price_diffs() p_document_id: '||p_document_id||
2993 ', p_entity_type: BLANKET LINE'||
2994 ', l_revision_num: '||l_revision_num);
2995 END IF;
2996 archive_price_diffs(p_document_id, 'PRICE BREAK', l_revision_num);
2997
2998 -- Bug 3215784 START
2999 -- Since Encumbrance code will create distribution record for BPA,
3000 -- archive_distribution routine should be called
3001 l_progress := '155';
3002 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
3003 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
3004 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
3005 'archive_distributions() p_document_id: '||p_document_id||
3006 ', p_document_type: '||p_document_type||
3007 ', l_revision_num: '||l_revision_num);
3008 END IF;
3009 archive_distributions(p_document_id, p_document_type, l_revision_num);
3010 -- Bug 3215784 END
3011
3012 END IF; /*IF (p_document_subtype = 'BLANKET')*/ --<BUG 3290647>
3013
3014 --<BUG 3290647>
3015 --Archive org assignments for global contracts as well as global blankets.
3016
3017 l_progress := '160';
3018 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
3019 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
3020 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
3021 'Select global agreement flag - po_header_id: '||p_document_id);
3022 END IF;
3023 -- SQL What:Find out if it is a global agreement
3024 -- SQL Why :Archive org_assignment table if needed
3025 SELECT NVL(global_agreement_flag, 'N')
3026 INTO l_ga_flag
3027 FROM po_headers_all
3028 WHERE po_header_id = p_document_id;
3029
3030 IF (l_ga_flag = 'Y') THEN
3031 l_progress := '170';
3032 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
3033 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
3034 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
3035 'archive_org_assignments() p_document_id: '||p_document_id||
3036 ', l_revision_num: '||l_revision_num);
3037 END IF;
3038 archive_org_assignments(p_document_id, l_revision_num);
3039
3040 END IF; /*IF (l_ga_flag = 'Y') THEN*/
3041
3042 l_progress := '180';
3043 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
3044 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
3045 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
3046 'archive_contract_terms() p_document_id: '||p_document_id||
3047 ', p_document_type: '||p_document_type||
3048 ', p_document_subtype: '||p_document_subtype||
3049 ', p_revision_num: '||l_revision_num);
3050 END IF;
3051 archive_contract_terms(p_document_id, p_document_type,
3052 p_document_subtype, l_revision_num);
3053
3054 END IF; /*IF (l_return_status = 'Y')*/
3055 ELSE
3056 l_progress := '200';
3057 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
3058 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
3059 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
3060 'check_release_archive() p_document_id: '||p_document_id);
3061 END IF;
3062 check_release_archive(p_document_id, l_revision_num, l_return_status);
3063
3064 IF (l_return_status = 'Y') THEN
3065 l_progress := '210';
3066 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
3067 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
3068 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
3069 'archive_release() p_document_id: '||p_document_id);
3070 END IF;
3071 archive_release(p_document_id);
3072
3073 l_progress := '220';
3074 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
3075 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
3076 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
3077 'archive_line_locations() p_document_id: '||p_document_id||
3078 ', p_document_type: '||p_document_type||
3079 ', l_revision_num: '||l_revision_num);
3080 END IF;
3081 archive_line_locations(p_document_id, p_document_type, l_revision_num);
3082
3083 l_progress := '230';
3084 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
3085 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
3086 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
3087 'archive_distributions() p_document_id: '||p_document_id||
3088 ', p_document_type: '||p_document_type||
3089 ', l_revision_num: '||l_revision_num);
3090 END IF;
3091 archive_distributions(p_document_id, p_document_type, l_revision_num);
3092 END IF; /*IF (l_return_status = 'Y')*/
3093 END IF; /*IF (p_document_type = 'PO')*/
3094
3095 -- Standard call to get message count and if count is 1,
3096 -- get message info.
3097 FND_MSG_PUB.Count_And_Get
3098 (p_count => x_msg_count,
3099 p_data => x_msg_data
3100 );
3101 l_progress := '300';
3102 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
3103 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
3104 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
3105 'Returning from PVT package');
3106 END IF;
3107 x_return_status := FND_API.G_RET_STS_SUCCESS;
3108
3109 l_progress := '310';
3110 EXCEPTION
3111 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3112 ROLLBACK TO PO_ARCHIVE_SP;
3113 IF (G_FND_DEBUG = 'Y') THEN
3114 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
3115 FND_LOG.string(FND_LOG.level_unexpected, l_module,
3116 'EXCEPTION: '||sqlerrm);
3117 END IF;
3118 END IF;
3119 -- Standard call to get message count and if count is 1,
3120 -- get message info.
3121 FND_MSG_PUB.Count_And_Get
3122 (p_count => x_msg_count,
3123 p_data => x_msg_data
3124 );
3125 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3126 WHEN FND_API.G_EXC_ERROR THEN
3127 ROLLBACK TO PO_ARCHIVE_SP;
3128 IF (G_FND_DEBUG = 'Y') THEN
3129 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
3130 FND_LOG.string(FND_LOG.level_unexpected, l_module,
3131 'EXCEPTION: '||sqlerrm);
3132 END IF;
3133 END IF;
3134 -- Standard call to get message count and if count is 1,
3135 -- get message info.
3136 FND_MSG_PUB.Count_And_Get
3137 (p_count => x_msg_count,
3138 p_data => x_msg_data
3139 );
3140 x_return_status := FND_API.G_RET_STS_ERROR;
3141 WHEN OTHERS THEN
3142 ROLLBACK TO PO_ARCHIVE_SP;
3143 IF (G_FND_DEBUG = 'Y') THEN
3144 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
3145 FND_LOG.string(FND_LOG.level_unexpected, l_module,
3146 'EXCEPTION: '||sqlerrm);
3147 END IF;
3148 END IF;
3149 -- Standard call to get message count and if count is 1,
3150 -- get message info.
3151 FND_MSG_PUB.Count_And_Get
3152 (p_count => x_msg_count,
3153 p_data => x_msg_data
3154 );
3155 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3156 END ARCHIVE_PO;
3157
3158 -----------------------------------------------------------------<SERVICES FPJ>
3159 -------------------------------------------------------------------------------
3160 --Start of Comments
3161 --Name: is_line_archived
3162 --Pre-reqs:
3163 -- None.
3164 --Modifies:
3165 -- None.
3166 --Locks:
3167 -- None.
3168 --Function:
3169 -- Determines if the given line is archived.
3170 --Parameters:
3171 --IN:
3172 --p_po_line_id
3173 -- Unique ID of line to check for archival.
3174 --Returns:
3175 -- TRUE if the line exists in the Archive table. FALSE, otherwise.
3176 --Testing:
3177 -- None.
3178 --End of Comments
3179 -------------------------------------------------------------------------------
3180 -------------------------------------------------------------------------------
3181 FUNCTION is_line_archived
3182 (
3183 p_po_line_id IN NUMBER
3184 )
3185 RETURN BOOLEAN
3186 IS
3187 CURSOR archived_line_csr IS
3188 SELECT 'Line archive records'
3189 FROM po_lines_archive_all
3190 WHERE po_line_id = p_po_line_id;
3191
3192 l_archived_line_csr_type archived_line_csr%ROWTYPE;
3193 l_line_is_archived BOOLEAN;
3194
3195 BEGIN
3196
3197 OPEN archived_line_csr;
3198 FETCH archived_line_csr INTO l_archived_line_csr_type;
3199 l_line_is_archived := archived_line_csr%FOUND;
3200 CLOSE archived_line_csr;
3201
3202 return (l_line_is_archived);
3203
3204 EXCEPTION
3205
3206 WHEN OTHERS THEN
3207 PO_MESSAGE_S.sql_error('PO_DOCUMENT_ARCHIVE_PVT.is_line_archived','000',sqlcode);
3208 RAISE;
3209
3210 END is_line_archived;
3211
3212
3213 -----------------------------------------------------------------<SERVICES FPJ>
3214 -------------------------------------------------------------------------------
3215 --Start of Comments
3216 --Name: is_line_location_archived
3217 --Pre-reqs:
3218 -- None.
3219 --Modifies:
3220 -- None.
3221 --Locks:
3222 -- None.
3223 --Function:
3224 -- Determines if the given line is archived.
3225 --Parameters:
3226 --IN:
3227 --p_po_line_id
3228 -- Unique ID of line to check for archival.
3229 --Returns:
3230 -- TRUE if the line exists in the Archive table. FALSE, otherwise.
3231 --Testing:
3232 -- None.
3233 --End of Comments
3234 -------------------------------------------------------------------------------
3235 -------------------------------------------------------------------------------
3236 FUNCTION is_line_location_archived
3237 (
3238 p_line_location_id IN NUMBER
3239 )
3240 RETURN BOOLEAN
3241 IS
3242 CURSOR archived_line_location_csr IS
3243 SELECT 'Line archive records'
3244 FROM po_line_locations_archive_all
3245 WHERE line_location_id = p_line_location_id;
3246
3247 l_archive_csr_type archived_line_location_csr%ROWTYPE;
3248 l_line_location_is_archived BOOLEAN;
3249
3250 BEGIN
3251
3252 OPEN archived_line_location_csr;
3253 FETCH archived_line_location_csr INTO l_archive_csr_type;
3254 l_line_location_is_archived := archived_line_location_csr%FOUND;
3255 CLOSE archived_line_location_csr;
3256
3257 return (l_line_location_is_archived);
3258
3259 EXCEPTION
3260
3261 WHEN OTHERS THEN
3262 PO_MESSAGE_S.sql_error('PO_DOCUMENT_ARCHIVE_PVT.is_line_location_archived','000',sqlcode);
3263 RAISE;
3264
3265 END is_line_location_archived;
3266
3267
3268 -----------------------------------------------------------------<SERVICES FPJ>
3269 -------------------------------------------------------------------------------
3270 --Start of Comments
3271 --Name: is_price_differential_archived
3272 --Pre-reqs:
3273 -- None.
3274 --Modifies:
3275 -- None.
3276 --Locks:
3277 -- None.
3278 --Function:
3279 -- Determines if the given line is archived.
3280 --Parameters:
3281 --IN:
3282 --p_po_line_id
3283 -- Unique ID of line to check for archival.
3284 --Returns:
3285 -- TRUE if the line exists in the Archive table. FALSE, otherwise.
3286 --Testing:
3287 -- None.
3288 --End of Comments
3289 -------------------------------------------------------------------------------
3290 -------------------------------------------------------------------------------
3291 FUNCTION is_price_differential_archived
3292 (
3293 p_price_differential_id IN NUMBER
3294 )
3295 RETURN BOOLEAN
3296 IS
3297 CURSOR archived_price_diff_csr IS
3298 SELECT 'Price Differential archive records'
3299 FROM po_price_differentials_archive
3300 WHERE price_differential_id = p_price_differential_id;
3301
3302 l_archive_csr_type archived_price_diff_csr%ROWTYPE;
3303 l_price_diff_is_archived BOOLEAN;
3304
3305 BEGIN
3306
3307 OPEN archived_price_diff_csr;
3308 FETCH archived_price_diff_csr INTO l_archive_csr_type;
3309 l_price_diff_is_archived := archived_price_diff_csr%FOUND;
3310 CLOSE archived_price_diff_csr;
3311
3312 return (l_price_diff_is_archived);
3313
3314 EXCEPTION
3315
3316 WHEN OTHERS THEN
3317 PO_MESSAGE_S.sql_error('PO_DOCUMENT_ARCHIVE_PVT.is_price_differential_archived','000',sqlcode);
3318 RAISE;
3319
3320 END is_price_differential_archived;
3321
3322 ------------------------------------------------------------------- Bug 3565522
3323 -------------------------------------------------------------------------------
3324 --Start of Comments
3325 --Name: get_archive_mode
3326 --Pre-reqs:
3327 -- None.
3328 --Modifies:
3329 -- None.
3330 --Locks:
3331 -- None.
3332 --Function:
3333 -- Determines if the given document is archived on approve or communicate
3334 --Parameters:
3335 --IN:
3336 --p_doc_type
3337 -- type of the document to be checked - PO or BLANKET
3338 --p_doc_subtype
3339 -- sub type of the document to be checked - RELEASE or STANDARD
3340 --Return
3341 -- Archive mode - APPROVE or PRINT
3342 --Testing:
3343 -- None.
3344 --End of Comments
3345 -------------------------------------------------------------------------------
3346 -------------------------------------------------------------------------------
3347 FUNCTION get_archive_mode
3348 ( p_doc_type IN VARCHAR2 ,
3349 p_doc_subtype IN VARCHAR2
3350 ) RETURN VARCHAR2
3351 IS
3352
3353 l_archive_mode PO_DOCUMENT_TYPES.archive_external_revision_code%TYPE;
3354
3355 BEGIN
3356
3357 -- SQL What: Get archive mode for Standard PO, Release
3358 -- SQL Why : To Determine when the document is approved
3359
3360 SELECT nvl(archive_external_revision_code,'PRINT')
3361 INTO l_archive_mode
3362 FROM po_document_types
3363 WHERE document_type_code = p_doc_type
3364 AND document_subtype = p_doc_subtype;
3365
3366 RETURN l_archive_mode;
3367
3368 EXCEPTION
3369 WHEN OTHERS THEN
3370 PO_MESSAGE_S.sql_error('PO_DOCUMENT_ARCHIVE_PVT.get_archive_mode','000',sqlcode);
3371 RAISE;
3372 END;
3373
3374 -------------------------------------------------------------------------------
3375 --Start of Comments
3376 --Name: archive_attribute_values
3377 --Pre-reqs:
3378 -- None.
3379 --Modifies:
3380 -- PO_ATTR_VALUES_ARCHIVE
3381 --Locks:
3382 -- None.
3383 --Function:
3384 -- Archive Item Attribute Values
3385 --Parameters:
3386 --IN:
3387 --p_po_header_id
3388 -- The PO_HEADER_ID of the document that needs to be archived
3389 --Testing:
3390 -- None.
3391 --End of Comments
3392 -------------------------------------------------------------------------------
3393 PROCEDURE archive_attribute_values
3394 (
3395 p_po_header_id IN NUMBER
3396 , p_revision_num IN NUMBER
3397 )
3398 IS
3399 d_mod CONSTANT VARCHAR2(100) := D_archive_attribute_values;
3400 l_progress VARCHAR2(4) := '000';
3401 BEGIN
3402 l_progress := '000';
3403
3404 IF PO_LOG.d_proc THEN
3405 PO_LOG.proc_begin(d_mod,'p_po_header_id',p_po_header_id);
3406 PO_LOG.proc_begin(d_mod,'p_revision_num',p_revision_num);
3407 END IF;
3408
3409 IF PO_LOG.d_stmt THEN PO_LOG.stmt(d_mod,l_progress,'Calling INSERT'); END IF;
3410
3411 l_progress := '010';
3412
3413 INSERT INTO PO_ATTR_VALUES_ARCHIVE
3414 (
3415 attribute_values_id,
3416 revision_num,
3417 po_line_id,
3418 req_template_name,
3419 req_template_line_num,
3420 ip_category_id,
3421 inventory_item_id,
3422 org_id,
3423 manufacturer_part_num,
3424 thumbnail_image,
3425 supplier_url,
3426 manufacturer_url,
3427 attachment_url,
3428 unspsc,
3429 availability,
3430 lead_time,
3431 text_base_attribute1,
3432 text_base_attribute2,
3433 text_base_attribute3,
3434 text_base_attribute4,
3435 text_base_attribute5,
3436 text_base_attribute6,
3437 text_base_attribute7,
3438 text_base_attribute8,
3439 text_base_attribute9,
3440 text_base_attribute10,
3441 text_base_attribute11,
3442 text_base_attribute12,
3443 text_base_attribute13,
3444 text_base_attribute14,
3445 text_base_attribute15,
3446 text_base_attribute16,
3447 text_base_attribute17,
3448 text_base_attribute18,
3449 text_base_attribute19,
3450 text_base_attribute20,
3451 text_base_attribute21,
3452 text_base_attribute22,
3453 text_base_attribute23,
3454 text_base_attribute24,
3455 text_base_attribute25,
3456 text_base_attribute26,
3457 text_base_attribute27,
3458 text_base_attribute28,
3459 text_base_attribute29,
3460 text_base_attribute30,
3461 text_base_attribute31,
3462 text_base_attribute32,
3463 text_base_attribute33,
3464 text_base_attribute34,
3465 text_base_attribute35,
3466 text_base_attribute36,
3467 text_base_attribute37,
3468 text_base_attribute38,
3469 text_base_attribute39,
3470 text_base_attribute40,
3471 text_base_attribute41,
3472 text_base_attribute42,
3473 text_base_attribute43,
3474 text_base_attribute44,
3475 text_base_attribute45,
3476 text_base_attribute46,
3477 text_base_attribute47,
3478 text_base_attribute48,
3479 text_base_attribute49,
3480 text_base_attribute50,
3481 text_base_attribute51,
3482 text_base_attribute52,
3483 text_base_attribute53,
3484 text_base_attribute54,
3485 text_base_attribute55,
3486 text_base_attribute56,
3487 text_base_attribute57,
3488 text_base_attribute58,
3489 text_base_attribute59,
3490 text_base_attribute60,
3491 text_base_attribute61,
3492 text_base_attribute62,
3493 text_base_attribute63,
3494 text_base_attribute64,
3495 text_base_attribute65,
3496 text_base_attribute66,
3497 text_base_attribute67,
3498 text_base_attribute68,
3499 text_base_attribute69,
3500 text_base_attribute70,
3501 text_base_attribute71,
3502 text_base_attribute72,
3503 text_base_attribute73,
3504 text_base_attribute74,
3505 text_base_attribute75,
3506 text_base_attribute76,
3507 text_base_attribute77,
3508 text_base_attribute78,
3509 text_base_attribute79,
3510 text_base_attribute80,
3511 text_base_attribute81,
3512 text_base_attribute82,
3513 text_base_attribute83,
3514 text_base_attribute84,
3515 text_base_attribute85,
3516 text_base_attribute86,
3517 text_base_attribute87,
3518 text_base_attribute88,
3519 text_base_attribute89,
3520 text_base_attribute90,
3521 text_base_attribute91,
3522 text_base_attribute92,
3523 text_base_attribute93,
3524 text_base_attribute94,
3525 text_base_attribute95,
3526 text_base_attribute96,
3527 text_base_attribute97,
3528 text_base_attribute98,
3529 text_base_attribute99,
3530 text_base_attribute100,
3531 num_base_attribute1,
3532 num_base_attribute2,
3533 num_base_attribute3,
3534 num_base_attribute4,
3535 num_base_attribute5,
3536 num_base_attribute6,
3537 num_base_attribute7,
3538 num_base_attribute8,
3539 num_base_attribute9,
3540 num_base_attribute10,
3541 num_base_attribute11,
3542 num_base_attribute12,
3543 num_base_attribute13,
3544 num_base_attribute14,
3545 num_base_attribute15,
3546 num_base_attribute16,
3547 num_base_attribute17,
3548 num_base_attribute18,
3549 num_base_attribute19,
3550 num_base_attribute20,
3551 num_base_attribute21,
3552 num_base_attribute22,
3553 num_base_attribute23,
3554 num_base_attribute24,
3555 num_base_attribute25,
3556 num_base_attribute26,
3557 num_base_attribute27,
3558 num_base_attribute28,
3559 num_base_attribute29,
3560 num_base_attribute30,
3561 num_base_attribute31,
3562 num_base_attribute32,
3563 num_base_attribute33,
3564 num_base_attribute34,
3565 num_base_attribute35,
3566 num_base_attribute36,
3567 num_base_attribute37,
3568 num_base_attribute38,
3569 num_base_attribute39,
3570 num_base_attribute40,
3571 num_base_attribute41,
3572 num_base_attribute42,
3573 num_base_attribute43,
3574 num_base_attribute44,
3575 num_base_attribute45,
3576 num_base_attribute46,
3577 num_base_attribute47,
3578 num_base_attribute48,
3579 num_base_attribute49,
3580 num_base_attribute50,
3581 num_base_attribute51,
3582 num_base_attribute52,
3583 num_base_attribute53,
3584 num_base_attribute54,
3585 num_base_attribute55,
3586 num_base_attribute56,
3587 num_base_attribute57,
3588 num_base_attribute58,
3589 num_base_attribute59,
3590 num_base_attribute60,
3591 num_base_attribute61,
3592 num_base_attribute62,
3593 num_base_attribute63,
3594 num_base_attribute64,
3595 num_base_attribute65,
3596 num_base_attribute66,
3597 num_base_attribute67,
3598 num_base_attribute68,
3599 num_base_attribute69,
3600 num_base_attribute70,
3601 num_base_attribute71,
3602 num_base_attribute72,
3603 num_base_attribute73,
3604 num_base_attribute74,
3605 num_base_attribute75,
3606 num_base_attribute76,
3607 num_base_attribute77,
3608 num_base_attribute78,
3609 num_base_attribute79,
3610 num_base_attribute80,
3611 num_base_attribute81,
3612 num_base_attribute82,
3613 num_base_attribute83,
3614 num_base_attribute84,
3615 num_base_attribute85,
3616 num_base_attribute86,
3617 num_base_attribute87,
3618 num_base_attribute88,
3619 num_base_attribute89,
3620 num_base_attribute90,
3621 num_base_attribute91,
3622 num_base_attribute92,
3623 num_base_attribute93,
3624 num_base_attribute94,
3625 num_base_attribute95,
3626 num_base_attribute96,
3627 num_base_attribute97,
3628 num_base_attribute98,
3629 num_base_attribute99,
3630 num_base_attribute100,
3631 text_cat_attribute1,
3632 text_cat_attribute2,
3633 text_cat_attribute3,
3634 text_cat_attribute4,
3635 text_cat_attribute5,
3636 text_cat_attribute6,
3637 text_cat_attribute7,
3638 text_cat_attribute8,
3639 text_cat_attribute9,
3640 text_cat_attribute10,
3641 text_cat_attribute11,
3642 text_cat_attribute12,
3643 text_cat_attribute13,
3644 text_cat_attribute14,
3645 text_cat_attribute15,
3646 text_cat_attribute16,
3647 text_cat_attribute17,
3648 text_cat_attribute18,
3649 text_cat_attribute19,
3650 text_cat_attribute20,
3651 text_cat_attribute21,
3652 text_cat_attribute22,
3653 text_cat_attribute23,
3654 text_cat_attribute24,
3655 text_cat_attribute25,
3656 text_cat_attribute26,
3657 text_cat_attribute27,
3658 text_cat_attribute28,
3659 text_cat_attribute29,
3660 text_cat_attribute30,
3661 text_cat_attribute31,
3662 text_cat_attribute32,
3663 text_cat_attribute33,
3664 text_cat_attribute34,
3665 text_cat_attribute35,
3666 text_cat_attribute36,
3667 text_cat_attribute37,
3668 text_cat_attribute38,
3669 text_cat_attribute39,
3670 text_cat_attribute40,
3671 text_cat_attribute41,
3672 text_cat_attribute42,
3673 text_cat_attribute43,
3674 text_cat_attribute44,
3675 text_cat_attribute45,
3676 text_cat_attribute46,
3677 text_cat_attribute47,
3678 text_cat_attribute48,
3679 text_cat_attribute49,
3680 text_cat_attribute50,
3681 num_cat_attribute1,
3682 num_cat_attribute2,
3683 num_cat_attribute3,
3684 num_cat_attribute4,
3685 num_cat_attribute5,
3686 num_cat_attribute6,
3687 num_cat_attribute7,
3688 num_cat_attribute8,
3689 num_cat_attribute9,
3690 num_cat_attribute10,
3691 num_cat_attribute11,
3692 num_cat_attribute12,
3693 num_cat_attribute13,
3694 num_cat_attribute14,
3695 num_cat_attribute15,
3696 num_cat_attribute16,
3697 num_cat_attribute17,
3698 num_cat_attribute18,
3699 num_cat_attribute19,
3700 num_cat_attribute20,
3701 num_cat_attribute21,
3702 num_cat_attribute22,
3703 num_cat_attribute23,
3704 num_cat_attribute24,
3705 num_cat_attribute25,
3706 num_cat_attribute26,
3707 num_cat_attribute27,
3708 num_cat_attribute28,
3709 num_cat_attribute29,
3710 num_cat_attribute30,
3711 num_cat_attribute31,
3712 num_cat_attribute32,
3713 num_cat_attribute33,
3714 num_cat_attribute34,
3715 num_cat_attribute35,
3716 num_cat_attribute36,
3717 num_cat_attribute37,
3718 num_cat_attribute38,
3719 num_cat_attribute39,
3720 num_cat_attribute40,
3721 num_cat_attribute41,
3722 num_cat_attribute42,
3723 num_cat_attribute43,
3724 num_cat_attribute44,
3725 num_cat_attribute45,
3726 num_cat_attribute46,
3727 num_cat_attribute47,
3728 num_cat_attribute48,
3729 num_cat_attribute49,
3730 num_cat_attribute50,
3731 last_update_login,
3732 last_updated_by,
3733 last_update_date,
3734 created_by,
3735 creation_date,
3736 request_id,
3737 program_application_id,
3738 program_id,
3739 program_update_date,
3740 last_updated_program,
3741 latest_external_flag
3742 )
3743 SELECT
3744 ATTR.attribute_values_id,
3745 p_revision_num,
3746 ATTR.po_line_id,
3747 ATTR.req_template_name,
3748 ATTR.req_template_line_num,
3749 ATTR.ip_category_id,
3750 ATTR.inventory_item_id,
3751 ATTR.org_id,
3752 ATTR.manufacturer_part_num,
3753 ATTR.thumbnail_image,
3754 ATTR.supplier_url,
3755 ATTR.manufacturer_url,
3756 ATTR.attachment_url,
3757 ATTR.unspsc,
3758 ATTR.availability,
3759 ATTR.lead_time,
3760 ATTR.text_base_attribute1,
3761 ATTR.text_base_attribute2,
3762 ATTR.text_base_attribute3,
3763 ATTR.text_base_attribute4,
3764 ATTR.text_base_attribute5,
3765 ATTR.text_base_attribute6,
3766 ATTR.text_base_attribute7,
3767 ATTR.text_base_attribute8,
3768 ATTR.text_base_attribute9,
3769 ATTR.text_base_attribute10,
3770 ATTR.text_base_attribute11,
3771 ATTR.text_base_attribute12,
3772 ATTR.text_base_attribute13,
3773 ATTR.text_base_attribute14,
3774 ATTR.text_base_attribute15,
3775 ATTR.text_base_attribute16,
3776 ATTR.text_base_attribute17,
3777 ATTR.text_base_attribute18,
3778 ATTR.text_base_attribute19,
3779 ATTR.text_base_attribute20,
3780 ATTR.text_base_attribute21,
3781 ATTR.text_base_attribute22,
3782 ATTR.text_base_attribute23,
3783 ATTR.text_base_attribute24,
3784 ATTR.text_base_attribute25,
3785 ATTR.text_base_attribute26,
3786 ATTR.text_base_attribute27,
3787 ATTR.text_base_attribute28,
3788 ATTR.text_base_attribute29,
3789 ATTR.text_base_attribute30,
3790 ATTR.text_base_attribute31,
3791 ATTR.text_base_attribute32,
3792 ATTR.text_base_attribute33,
3793 ATTR.text_base_attribute34,
3794 ATTR.text_base_attribute35,
3795 ATTR.text_base_attribute36,
3796 ATTR.text_base_attribute37,
3797 ATTR.text_base_attribute38,
3798 ATTR.text_base_attribute39,
3799 ATTR.text_base_attribute40,
3800 ATTR.text_base_attribute41,
3801 ATTR.text_base_attribute42,
3802 ATTR.text_base_attribute43,
3803 ATTR.text_base_attribute44,
3804 ATTR.text_base_attribute45,
3805 ATTR.text_base_attribute46,
3806 ATTR.text_base_attribute47,
3807 ATTR.text_base_attribute48,
3808 ATTR.text_base_attribute49,
3809 ATTR.text_base_attribute50,
3810 ATTR.text_base_attribute51,
3811 ATTR.text_base_attribute52,
3812 ATTR.text_base_attribute53,
3813 ATTR.text_base_attribute54,
3814 ATTR.text_base_attribute55,
3815 ATTR.text_base_attribute56,
3816 ATTR.text_base_attribute57,
3817 ATTR.text_base_attribute58,
3818 ATTR.text_base_attribute59,
3819 ATTR.text_base_attribute60,
3820 ATTR.text_base_attribute61,
3821 ATTR.text_base_attribute62,
3822 ATTR.text_base_attribute63,
3823 ATTR.text_base_attribute64,
3824 ATTR.text_base_attribute65,
3825 ATTR.text_base_attribute66,
3826 ATTR.text_base_attribute67,
3827 ATTR.text_base_attribute68,
3828 ATTR.text_base_attribute69,
3829 ATTR.text_base_attribute70,
3830 ATTR.text_base_attribute71,
3831 ATTR.text_base_attribute72,
3832 ATTR.text_base_attribute73,
3833 ATTR.text_base_attribute74,
3834 ATTR.text_base_attribute75,
3835 ATTR.text_base_attribute76,
3836 ATTR.text_base_attribute77,
3837 ATTR.text_base_attribute78,
3838 ATTR.text_base_attribute79,
3839 ATTR.text_base_attribute80,
3840 ATTR.text_base_attribute81,
3841 ATTR.text_base_attribute82,
3842 ATTR.text_base_attribute83,
3843 ATTR.text_base_attribute84,
3844 ATTR.text_base_attribute85,
3845 ATTR.text_base_attribute86,
3846 ATTR.text_base_attribute87,
3847 ATTR.text_base_attribute88,
3848 ATTR.text_base_attribute89,
3849 ATTR.text_base_attribute90,
3850 ATTR.text_base_attribute91,
3851 ATTR.text_base_attribute92,
3852 ATTR.text_base_attribute93,
3853 ATTR.text_base_attribute94,
3854 ATTR.text_base_attribute95,
3855 ATTR.text_base_attribute96,
3856 ATTR.text_base_attribute97,
3857 ATTR.text_base_attribute98,
3858 ATTR.text_base_attribute99,
3859 ATTR.text_base_attribute100,
3860 ATTR.num_base_attribute1,
3861 ATTR.num_base_attribute2,
3862 ATTR.num_base_attribute3,
3863 ATTR.num_base_attribute4,
3864 ATTR.num_base_attribute5,
3865 ATTR.num_base_attribute6,
3866 ATTR.num_base_attribute7,
3867 ATTR.num_base_attribute8,
3868 ATTR.num_base_attribute9,
3869 ATTR.num_base_attribute10,
3870 ATTR.num_base_attribute11,
3871 ATTR.num_base_attribute12,
3872 ATTR.num_base_attribute13,
3873 ATTR.num_base_attribute14,
3874 ATTR.num_base_attribute15,
3875 ATTR.num_base_attribute16,
3876 ATTR.num_base_attribute17,
3877 ATTR.num_base_attribute18,
3878 ATTR.num_base_attribute19,
3879 ATTR.num_base_attribute20,
3880 ATTR.num_base_attribute21,
3881 ATTR.num_base_attribute22,
3882 ATTR.num_base_attribute23,
3883 ATTR.num_base_attribute24,
3884 ATTR.num_base_attribute25,
3885 ATTR.num_base_attribute26,
3886 ATTR.num_base_attribute27,
3887 ATTR.num_base_attribute28,
3888 ATTR.num_base_attribute29,
3889 ATTR.num_base_attribute30,
3890 ATTR.num_base_attribute31,
3891 ATTR.num_base_attribute32,
3892 ATTR.num_base_attribute33,
3893 ATTR.num_base_attribute34,
3894 ATTR.num_base_attribute35,
3895 ATTR.num_base_attribute36,
3896 ATTR.num_base_attribute37,
3897 ATTR.num_base_attribute38,
3898 ATTR.num_base_attribute39,
3899 ATTR.num_base_attribute40,
3900 ATTR.num_base_attribute41,
3901 ATTR.num_base_attribute42,
3902 ATTR.num_base_attribute43,
3903 ATTR.num_base_attribute44,
3904 ATTR.num_base_attribute45,
3905 ATTR.num_base_attribute46,
3906 ATTR.num_base_attribute47,
3907 ATTR.num_base_attribute48,
3908 ATTR.num_base_attribute49,
3909 ATTR.num_base_attribute50,
3910 ATTR.num_base_attribute51,
3911 ATTR.num_base_attribute52,
3912 ATTR.num_base_attribute53,
3913 ATTR.num_base_attribute54,
3914 ATTR.num_base_attribute55,
3915 ATTR.num_base_attribute56,
3916 ATTR.num_base_attribute57,
3917 ATTR.num_base_attribute58,
3918 ATTR.num_base_attribute59,
3919 ATTR.num_base_attribute60,
3920 ATTR.num_base_attribute61,
3921 ATTR.num_base_attribute62,
3922 ATTR.num_base_attribute63,
3923 ATTR.num_base_attribute64,
3924 ATTR.num_base_attribute65,
3925 ATTR.num_base_attribute66,
3926 ATTR.num_base_attribute67,
3927 ATTR.num_base_attribute68,
3928 ATTR.num_base_attribute69,
3929 ATTR.num_base_attribute70,
3930 ATTR.num_base_attribute71,
3931 ATTR.num_base_attribute72,
3932 ATTR.num_base_attribute73,
3933 ATTR.num_base_attribute74,
3934 ATTR.num_base_attribute75,
3935 ATTR.num_base_attribute76,
3936 ATTR.num_base_attribute77,
3937 ATTR.num_base_attribute78,
3938 ATTR.num_base_attribute79,
3939 ATTR.num_base_attribute80,
3940 ATTR.num_base_attribute81,
3941 ATTR.num_base_attribute82,
3942 ATTR.num_base_attribute83,
3943 ATTR.num_base_attribute84,
3944 ATTR.num_base_attribute85,
3945 ATTR.num_base_attribute86,
3946 ATTR.num_base_attribute87,
3947 ATTR.num_base_attribute88,
3948 ATTR.num_base_attribute89,
3949 ATTR.num_base_attribute90,
3950 ATTR.num_base_attribute91,
3951 ATTR.num_base_attribute92,
3952 ATTR.num_base_attribute93,
3953 ATTR.num_base_attribute94,
3954 ATTR.num_base_attribute95,
3955 ATTR.num_base_attribute96,
3956 ATTR.num_base_attribute97,
3957 ATTR.num_base_attribute98,
3958 ATTR.num_base_attribute99,
3959 ATTR.num_base_attribute100,
3960 ATTR.text_cat_attribute1,
3961 ATTR.text_cat_attribute2,
3962 ATTR.text_cat_attribute3,
3963 ATTR.text_cat_attribute4,
3964 ATTR.text_cat_attribute5,
3965 ATTR.text_cat_attribute6,
3966 ATTR.text_cat_attribute7,
3967 ATTR.text_cat_attribute8,
3968 ATTR.text_cat_attribute9,
3969 ATTR.text_cat_attribute10,
3970 ATTR.text_cat_attribute11,
3971 ATTR.text_cat_attribute12,
3972 ATTR.text_cat_attribute13,
3973 ATTR.text_cat_attribute14,
3974 ATTR.text_cat_attribute15,
3975 ATTR.text_cat_attribute16,
3976 ATTR.text_cat_attribute17,
3977 ATTR.text_cat_attribute18,
3978 ATTR.text_cat_attribute19,
3979 ATTR.text_cat_attribute20,
3980 ATTR.text_cat_attribute21,
3981 ATTR.text_cat_attribute22,
3982 ATTR.text_cat_attribute23,
3983 ATTR.text_cat_attribute24,
3984 ATTR.text_cat_attribute25,
3985 ATTR.text_cat_attribute26,
3986 ATTR.text_cat_attribute27,
3987 ATTR.text_cat_attribute28,
3988 ATTR.text_cat_attribute29,
3989 ATTR.text_cat_attribute30,
3990 ATTR.text_cat_attribute31,
3991 ATTR.text_cat_attribute32,
3992 ATTR.text_cat_attribute33,
3993 ATTR.text_cat_attribute34,
3994 ATTR.text_cat_attribute35,
3995 ATTR.text_cat_attribute36,
3996 ATTR.text_cat_attribute37,
3997 ATTR.text_cat_attribute38,
3998 ATTR.text_cat_attribute39,
3999 ATTR.text_cat_attribute40,
4000 ATTR.text_cat_attribute41,
4001 ATTR.text_cat_attribute42,
4002 ATTR.text_cat_attribute43,
4003 ATTR.text_cat_attribute44,
4004 ATTR.text_cat_attribute45,
4005 ATTR.text_cat_attribute46,
4006 ATTR.text_cat_attribute47,
4007 ATTR.text_cat_attribute48,
4008 ATTR.text_cat_attribute49,
4009 ATTR.text_cat_attribute50,
4010 ATTR.num_cat_attribute1,
4011 ATTR.num_cat_attribute2,
4012 ATTR.num_cat_attribute3,
4013 ATTR.num_cat_attribute4,
4014 ATTR.num_cat_attribute5,
4015 ATTR.num_cat_attribute6,
4016 ATTR.num_cat_attribute7,
4017 ATTR.num_cat_attribute8,
4018 ATTR.num_cat_attribute9,
4019 ATTR.num_cat_attribute10,
4020 ATTR.num_cat_attribute11,
4021 ATTR.num_cat_attribute12,
4022 ATTR.num_cat_attribute13,
4023 ATTR.num_cat_attribute14,
4024 ATTR.num_cat_attribute15,
4025 ATTR.num_cat_attribute16,
4026 ATTR.num_cat_attribute17,
4027 ATTR.num_cat_attribute18,
4028 ATTR.num_cat_attribute19,
4029 ATTR.num_cat_attribute20,
4030 ATTR.num_cat_attribute21,
4031 ATTR.num_cat_attribute22,
4032 ATTR.num_cat_attribute23,
4033 ATTR.num_cat_attribute24,
4034 ATTR.num_cat_attribute25,
4035 ATTR.num_cat_attribute26,
4036 ATTR.num_cat_attribute27,
4037 ATTR.num_cat_attribute28,
4038 ATTR.num_cat_attribute29,
4039 ATTR.num_cat_attribute30,
4040 ATTR.num_cat_attribute31,
4041 ATTR.num_cat_attribute32,
4042 ATTR.num_cat_attribute33,
4043 ATTR.num_cat_attribute34,
4044 ATTR.num_cat_attribute35,
4045 ATTR.num_cat_attribute36,
4046 ATTR.num_cat_attribute37,
4047 ATTR.num_cat_attribute38,
4048 ATTR.num_cat_attribute39,
4049 ATTR.num_cat_attribute40,
4050 ATTR.num_cat_attribute41,
4051 ATTR.num_cat_attribute42,
4052 ATTR.num_cat_attribute43,
4053 ATTR.num_cat_attribute44,
4054 ATTR.num_cat_attribute45,
4055 ATTR.num_cat_attribute46,
4056 ATTR.num_cat_attribute47,
4057 ATTR.num_cat_attribute48,
4058 ATTR.num_cat_attribute49,
4059 ATTR.num_cat_attribute50,
4060 ATTR.last_update_login,
4061 ATTR.last_updated_by,
4062 ATTR.last_update_date,
4063 ATTR.created_by,
4064 ATTR.creation_date,
4065 ATTR.request_id,
4066 ATTR.program_application_id,
4067 ATTR.program_id,
4068 ATTR.program_update_date,
4069 ATTR.last_updated_program,
4070 'Y' -- latest_external_flag
4071 FROM PO_ATTRIBUTE_VALUES ATTR,
4072 PO_LINES_ALL POL
4073 WHERE ATTR.po_line_id = POL.po_line_id
4074 AND POL.po_header_id = p_po_header_id;
4075
4076 IF PO_LOG.d_stmt THEN PO_LOG.stmt(d_mod,l_progress,'Number of rows INSERTed into ATTR archive='||SQL%rowcount); END IF;
4077
4078 IF PO_LOG.d_proc THEN PO_LOG.proc_end(d_mod); END IF;
4079 EXCEPTION
4080 WHEN OTHERS THEN
4081 IF PO_LOG.d_exc THEN PO_LOG.exc(d_mod,l_progress,'Unhandled exception'); END IF;
4082 RAISE;
4083 END archive_attribute_values;
4084
4085 -------------------------------------------------------------------------------
4086 --Start of Comments
4087 --Name: archive_attr_values_tlp
4088 --Pre-reqs:
4089 -- None.
4090 --Modifies:
4091 -- PO_ATTR_VALUES_TLP_ARCHIVE
4092 --Locks:
4093 -- None.
4094 --Function:
4095 -- Archive Item Attribute Values TLP
4096 --Parameters:
4097 --IN:
4098 --p_po_header_id
4099 -- The PO_HEADER_ID of the document that needs to be archived
4100 --Testing:
4101 -- None.
4102 --End of Comments
4103 -------------------------------------------------------------------------------
4104 PROCEDURE archive_attr_values_tlp
4105 (
4106 p_po_header_id IN NUMBER
4107 , p_revision_num IN NUMBER
4108 )
4109 IS
4110 d_mod CONSTANT VARCHAR2(100) := D_archive_attr_values_tlp;
4111 l_progress VARCHAR2(4) := '000';
4112 BEGIN
4113 l_progress := '000';
4114
4115 IF PO_LOG.d_proc THEN
4116 PO_LOG.proc_begin(d_mod,'p_po_header_id',p_po_header_id);
4117 PO_LOG.proc_begin(d_mod,'p_revision_num',p_revision_num);
4118 END IF;
4119
4120 IF PO_LOG.d_stmt THEN PO_LOG.stmt(d_mod,l_progress,'Calling INSERT'); END IF;
4121
4122 l_progress := '010';
4123
4124 INSERT INTO PO_ATTR_VALUES_TLP_ARCHIVE
4125 (
4126 attribute_values_tlp_id,
4127 revision_num,
4128 latest_external_flag,
4129 po_line_id,
4130 req_template_name,
4131 req_template_line_num,
4132 ip_category_id,
4133 inventory_item_id,
4134 org_id,
4135 language,
4136 description,
4137 manufacturer,
4138 comments,
4139 alias,
4140 long_description,
4141 tl_text_base_attribute1,
4142 tl_text_base_attribute2,
4143 tl_text_base_attribute3,
4144 tl_text_base_attribute4,
4145 tl_text_base_attribute5,
4146 tl_text_base_attribute6,
4147 tl_text_base_attribute7,
4148 tl_text_base_attribute8,
4149 tl_text_base_attribute9,
4150 tl_text_base_attribute10,
4151 tl_text_base_attribute11,
4152 tl_text_base_attribute12,
4153 tl_text_base_attribute13,
4154 tl_text_base_attribute14,
4155 tl_text_base_attribute15,
4156 tl_text_base_attribute16,
4157 tl_text_base_attribute17,
4158 tl_text_base_attribute18,
4159 tl_text_base_attribute19,
4160 tl_text_base_attribute20,
4161 tl_text_base_attribute21,
4162 tl_text_base_attribute22,
4163 tl_text_base_attribute23,
4164 tl_text_base_attribute24,
4165 tl_text_base_attribute25,
4166 tl_text_base_attribute26,
4167 tl_text_base_attribute27,
4168 tl_text_base_attribute28,
4169 tl_text_base_attribute29,
4170 tl_text_base_attribute30,
4171 tl_text_base_attribute31,
4172 tl_text_base_attribute32,
4173 tl_text_base_attribute33,
4174 tl_text_base_attribute34,
4175 tl_text_base_attribute35,
4176 tl_text_base_attribute36,
4177 tl_text_base_attribute37,
4178 tl_text_base_attribute38,
4179 tl_text_base_attribute39,
4180 tl_text_base_attribute40,
4181 tl_text_base_attribute41,
4182 tl_text_base_attribute42,
4183 tl_text_base_attribute43,
4184 tl_text_base_attribute44,
4185 tl_text_base_attribute45,
4186 tl_text_base_attribute46,
4187 tl_text_base_attribute47,
4188 tl_text_base_attribute48,
4189 tl_text_base_attribute49,
4190 tl_text_base_attribute50,
4191 tl_text_base_attribute51,
4192 tl_text_base_attribute52,
4193 tl_text_base_attribute53,
4194 tl_text_base_attribute54,
4195 tl_text_base_attribute55,
4196 tl_text_base_attribute56,
4197 tl_text_base_attribute57,
4198 tl_text_base_attribute58,
4199 tl_text_base_attribute59,
4200 tl_text_base_attribute60,
4201 tl_text_base_attribute61,
4202 tl_text_base_attribute62,
4203 tl_text_base_attribute63,
4204 tl_text_base_attribute64,
4205 tl_text_base_attribute65,
4206 tl_text_base_attribute66,
4207 tl_text_base_attribute67,
4208 tl_text_base_attribute68,
4209 tl_text_base_attribute69,
4210 tl_text_base_attribute70,
4211 tl_text_base_attribute71,
4212 tl_text_base_attribute72,
4213 tl_text_base_attribute73,
4214 tl_text_base_attribute74,
4215 tl_text_base_attribute75,
4216 tl_text_base_attribute76,
4217 tl_text_base_attribute77,
4218 tl_text_base_attribute78,
4219 tl_text_base_attribute79,
4220 tl_text_base_attribute80,
4221 tl_text_base_attribute81,
4222 tl_text_base_attribute82,
4223 tl_text_base_attribute83,
4224 tl_text_base_attribute84,
4225 tl_text_base_attribute85,
4226 tl_text_base_attribute86,
4227 tl_text_base_attribute87,
4228 tl_text_base_attribute88,
4229 tl_text_base_attribute89,
4230 tl_text_base_attribute90,
4231 tl_text_base_attribute91,
4232 tl_text_base_attribute92,
4233 tl_text_base_attribute93,
4234 tl_text_base_attribute94,
4235 tl_text_base_attribute95,
4236 tl_text_base_attribute96,
4237 tl_text_base_attribute97,
4238 tl_text_base_attribute98,
4239 tl_text_base_attribute99,
4240 tl_text_base_attribute100,
4241 tl_text_cat_attribute1,
4242 tl_text_cat_attribute2,
4243 tl_text_cat_attribute3,
4244 tl_text_cat_attribute4,
4245 tl_text_cat_attribute5,
4246 tl_text_cat_attribute6,
4247 tl_text_cat_attribute7,
4248 tl_text_cat_attribute8,
4249 tl_text_cat_attribute9,
4250 tl_text_cat_attribute10,
4251 tl_text_cat_attribute11,
4252 tl_text_cat_attribute12,
4253 tl_text_cat_attribute13,
4254 tl_text_cat_attribute14,
4255 tl_text_cat_attribute15,
4256 tl_text_cat_attribute16,
4257 tl_text_cat_attribute17,
4258 tl_text_cat_attribute18,
4259 tl_text_cat_attribute19,
4260 tl_text_cat_attribute20,
4261 tl_text_cat_attribute21,
4262 tl_text_cat_attribute22,
4263 tl_text_cat_attribute23,
4264 tl_text_cat_attribute24,
4265 tl_text_cat_attribute25,
4266 tl_text_cat_attribute26,
4267 tl_text_cat_attribute27,
4268 tl_text_cat_attribute28,
4269 tl_text_cat_attribute29,
4270 tl_text_cat_attribute30,
4271 tl_text_cat_attribute31,
4272 tl_text_cat_attribute32,
4273 tl_text_cat_attribute33,
4274 tl_text_cat_attribute34,
4275 tl_text_cat_attribute35,
4276 tl_text_cat_attribute36,
4277 tl_text_cat_attribute37,
4278 tl_text_cat_attribute38,
4279 tl_text_cat_attribute39,
4280 tl_text_cat_attribute40,
4281 tl_text_cat_attribute41,
4282 tl_text_cat_attribute42,
4283 tl_text_cat_attribute43,
4284 tl_text_cat_attribute44,
4285 tl_text_cat_attribute45,
4286 tl_text_cat_attribute46,
4287 tl_text_cat_attribute47,
4288 tl_text_cat_attribute48,
4289 tl_text_cat_attribute49,
4290 tl_text_cat_attribute50,
4291 last_update_login,
4292 last_updated_by,
4293 last_update_date,
4294 created_by,
4295 creation_date,
4296 request_id,
4297 program_application_id,
4298 program_id,
4299 program_update_date,
4300 last_updated_program
4301 )
4302 SELECT
4303 TLP.attribute_values_tlp_id,
4304 p_revision_num, -- revision_num
4305 'Y', -- latest_external_flag,
4306 TLP.po_line_id,
4307 TLP.req_template_name,
4308 TLP.req_template_line_num,
4309 TLP.ip_category_id,
4310 TLP.inventory_item_id,
4311 TLP.org_id,
4312 TLP.language,
4313 TLP.description,
4314 TLP.manufacturer,
4315 TLP.comments,
4316 TLP.alias,
4317 TLP.long_description,
4318 TLP.tl_text_base_attribute1,
4319 TLP.tl_text_base_attribute2,
4320 TLP.tl_text_base_attribute3,
4321 TLP.tl_text_base_attribute4,
4322 TLP.tl_text_base_attribute5,
4323 TLP.tl_text_base_attribute6,
4324 TLP.tl_text_base_attribute7,
4325 TLP.tl_text_base_attribute8,
4326 TLP.tl_text_base_attribute9,
4327 TLP.tl_text_base_attribute10,
4328 TLP.tl_text_base_attribute11,
4329 TLP.tl_text_base_attribute12,
4330 TLP.tl_text_base_attribute13,
4331 TLP.tl_text_base_attribute14,
4332 TLP.tl_text_base_attribute15,
4333 TLP.tl_text_base_attribute16,
4334 TLP.tl_text_base_attribute17,
4335 TLP.tl_text_base_attribute18,
4336 TLP.tl_text_base_attribute19,
4337 TLP.tl_text_base_attribute20,
4338 TLP.tl_text_base_attribute21,
4339 TLP.tl_text_base_attribute22,
4340 TLP.tl_text_base_attribute23,
4341 TLP.tl_text_base_attribute24,
4342 TLP.tl_text_base_attribute25,
4343 TLP.tl_text_base_attribute26,
4344 TLP.tl_text_base_attribute27,
4345 TLP.tl_text_base_attribute28,
4346 TLP.tl_text_base_attribute29,
4347 TLP.tl_text_base_attribute30,
4348 TLP.tl_text_base_attribute31,
4349 TLP.tl_text_base_attribute32,
4350 TLP.tl_text_base_attribute33,
4351 TLP.tl_text_base_attribute34,
4352 TLP.tl_text_base_attribute35,
4353 TLP.tl_text_base_attribute36,
4354 TLP.tl_text_base_attribute37,
4355 TLP.tl_text_base_attribute38,
4356 TLP.tl_text_base_attribute39,
4357 TLP.tl_text_base_attribute40,
4358 TLP.tl_text_base_attribute41,
4359 TLP.tl_text_base_attribute42,
4360 TLP.tl_text_base_attribute43,
4361 TLP.tl_text_base_attribute44,
4362 TLP.tl_text_base_attribute45,
4363 TLP.tl_text_base_attribute46,
4364 TLP.tl_text_base_attribute47,
4365 TLP.tl_text_base_attribute48,
4366 TLP.tl_text_base_attribute49,
4367 TLP.tl_text_base_attribute50,
4368 TLP.tl_text_base_attribute51,
4369 TLP.tl_text_base_attribute52,
4370 TLP.tl_text_base_attribute53,
4371 TLP.tl_text_base_attribute54,
4372 TLP.tl_text_base_attribute55,
4373 TLP.tl_text_base_attribute56,
4374 TLP.tl_text_base_attribute57,
4375 TLP.tl_text_base_attribute58,
4376 TLP.tl_text_base_attribute59,
4377 TLP.tl_text_base_attribute60,
4378 TLP.tl_text_base_attribute61,
4379 TLP.tl_text_base_attribute62,
4380 TLP.tl_text_base_attribute63,
4381 TLP.tl_text_base_attribute64,
4382 TLP.tl_text_base_attribute65,
4383 TLP.tl_text_base_attribute66,
4384 TLP.tl_text_base_attribute67,
4385 TLP.tl_text_base_attribute68,
4386 TLP.tl_text_base_attribute69,
4387 TLP.tl_text_base_attribute70,
4388 TLP.tl_text_base_attribute71,
4389 TLP.tl_text_base_attribute72,
4390 TLP.tl_text_base_attribute73,
4391 TLP.tl_text_base_attribute74,
4392 TLP.tl_text_base_attribute75,
4393 TLP.tl_text_base_attribute76,
4394 TLP.tl_text_base_attribute77,
4395 TLP.tl_text_base_attribute78,
4396 TLP.tl_text_base_attribute79,
4397 TLP.tl_text_base_attribute80,
4398 TLP.tl_text_base_attribute81,
4399 TLP.tl_text_base_attribute82,
4400 TLP.tl_text_base_attribute83,
4401 TLP.tl_text_base_attribute84,
4402 TLP.tl_text_base_attribute85,
4403 TLP.tl_text_base_attribute86,
4404 TLP.tl_text_base_attribute87,
4405 TLP.tl_text_base_attribute88,
4406 TLP.tl_text_base_attribute89,
4407 TLP.tl_text_base_attribute90,
4408 TLP.tl_text_base_attribute91,
4409 TLP.tl_text_base_attribute92,
4410 TLP.tl_text_base_attribute93,
4411 TLP.tl_text_base_attribute94,
4412 TLP.tl_text_base_attribute95,
4413 TLP.tl_text_base_attribute96,
4414 TLP.tl_text_base_attribute97,
4415 TLP.tl_text_base_attribute98,
4416 TLP.tl_text_base_attribute99,
4417 TLP.tl_text_base_attribute100,
4418 TLP.tl_text_cat_attribute1,
4419 TLP.tl_text_cat_attribute2,
4420 TLP.tl_text_cat_attribute3,
4421 TLP.tl_text_cat_attribute4,
4422 TLP.tl_text_cat_attribute5,
4423 TLP.tl_text_cat_attribute6,
4424 TLP.tl_text_cat_attribute7,
4425 TLP.tl_text_cat_attribute8,
4426 TLP.tl_text_cat_attribute9,
4427 TLP.tl_text_cat_attribute10,
4428 TLP.tl_text_cat_attribute11,
4429 TLP.tl_text_cat_attribute12,
4430 TLP.tl_text_cat_attribute13,
4431 TLP.tl_text_cat_attribute14,
4432 TLP.tl_text_cat_attribute15,
4433 TLP.tl_text_cat_attribute16,
4434 TLP.tl_text_cat_attribute17,
4435 TLP.tl_text_cat_attribute18,
4436 TLP.tl_text_cat_attribute19,
4437 TLP.tl_text_cat_attribute20,
4438 TLP.tl_text_cat_attribute21,
4439 TLP.tl_text_cat_attribute22,
4440 TLP.tl_text_cat_attribute23,
4441 TLP.tl_text_cat_attribute24,
4442 TLP.tl_text_cat_attribute25,
4443 TLP.tl_text_cat_attribute26,
4444 TLP.tl_text_cat_attribute27,
4445 TLP.tl_text_cat_attribute28,
4446 TLP.tl_text_cat_attribute29,
4447 TLP.tl_text_cat_attribute30,
4448 TLP.tl_text_cat_attribute31,
4449 TLP.tl_text_cat_attribute32,
4450 TLP.tl_text_cat_attribute33,
4451 TLP.tl_text_cat_attribute34,
4452 TLP.tl_text_cat_attribute35,
4453 TLP.tl_text_cat_attribute36,
4454 TLP.tl_text_cat_attribute37,
4455 TLP.tl_text_cat_attribute38,
4456 TLP.tl_text_cat_attribute39,
4457 TLP.tl_text_cat_attribute40,
4458 TLP.tl_text_cat_attribute41,
4459 TLP.tl_text_cat_attribute42,
4460 TLP.tl_text_cat_attribute43,
4461 TLP.tl_text_cat_attribute44,
4462 TLP.tl_text_cat_attribute45,
4463 TLP.tl_text_cat_attribute46,
4464 TLP.tl_text_cat_attribute47,
4465 TLP.tl_text_cat_attribute48,
4466 TLP.tl_text_cat_attribute49,
4467 TLP.tl_text_cat_attribute50,
4468 TLP.last_update_login,
4469 TLP.last_updated_by,
4470 TLP.last_update_date,
4471 TLP.created_by,
4472 TLP.creation_date,
4473 TLP.request_id,
4474 TLP.program_application_id,
4475 TLP.program_id,
4476 TLP.program_update_date,
4477 TLP.last_updated_program
4478 FROM PO_ATTRIBUTE_VALUES_TLP TLP,
4479 PO_LINES_ALL POL
4480 WHERE TLP.po_line_id = POL.po_line_id
4481 AND POL.po_header_id = p_po_header_id;
4482
4483 IF PO_LOG.d_stmt THEN PO_LOG.stmt(d_mod,l_progress,'Number of rows INSERTed into TLP archive='||SQL%rowcount); END IF;
4484
4485 IF PO_LOG.d_proc THEN PO_LOG.proc_end(d_mod); END IF;
4486 EXCEPTION
4487 WHEN OTHERS THEN
4488 IF PO_LOG.d_exc THEN PO_LOG.exc(d_mod,l_progress,'Unhandled exception'); END IF;
4489 RAISE;
4490 END archive_attr_values_tlp;
4491
4492 END PO_DOCUMENT_ARCHIVE_PVT;