[Home] [Help]
PACKAGE BODY: APPS.PO_DOCUMENT_ARCHIVE_PVT
Source
1 PACKAGE BODY po_document_archive_pvt AS
2 /* $Header: POXPIARB.pls 120.24.12020000.2 2013/02/10 13:21:41 vegajula 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 --<Enhanced Pricing Start:>
14 D_archive_price_adjustments CONSTANT VARCHAR2(100) := PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'archive_price_adjustments');
15 D_archive_price_adj_attribs CONSTANT VARCHAR2(100) := PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'archive_price_adj_attribs');
16 --<Enhanced Pricing End>
17
18 PROCEDURE archive_attribute_values
19 (
20 p_po_header_id IN NUMBER
21 , p_revision_num IN NUMBER
22 );
23
24 PROCEDURE archive_attr_values_tlp
25 (
26 p_po_header_id IN NUMBER
27 , p_revision_num IN NUMBER
28 );
29
30 --<Enhanced Pricing Start:>
31 PROCEDURE archive_price_adjustments
32 (
33 p_po_header_id IN NUMBER
34 , p_revision_num IN NUMBER
35 );
36 PROCEDURE archive_price_adj_attribs
37 (
38 p_po_header_id IN NUMBER
39 , p_revision_num IN NUMBER
40 );
41 --<Enhanced Pricing End>
42
43 --<Start CLM View Base Document>
44 PROCEDURE archive_uda_attribs
45 (
46 p_po_header_id IN NUMBER
47 , p_document_type IN VARCHAR2
48 , p_document_subtype IN VARCHAR2
49 , p_revision_num IN NUMBER
50 );
51
52 PROCEDURE archive_attachments
53 (
54 p_po_header_id IN NUMBER
55 , p_document_type IN VARCHAR2
56 , p_document_subtype IN VARCHAR2
57 , p_revision_num IN NUMBER
58 , p_draft_id IN NUMBER
59 );
60
61 PROCEDURE sync_contract_terms_to_draft
62 (
63 p_po_header_id IN NUMBER
64 , p_document_type IN VARCHAR2
65 , p_document_subtype IN VARCHAR2
66 , p_revision_num IN NUMBER
67 );
68 --<End CLM View Base Document>
69
70 -- CLM Phase 4 - Elins project
71 PROCEDURE ARCHIVE_EXHIBITS
72 (
73 p_document_id IN NUMBER,
74 p_revision_num IN NUMBER
75 );
76
77
78 -------------------------------------------------------------------------------
79 --Start of Comments
80 --Name: CHECK_PO_ARCHIVE
81 --Pre-reqs:
82 -- None.
83 --Modifies:
84 -- None.
85 --Locks:
86 -- None.
87 --Function:
88 -- Get the current revision number and check if it is already archived
89 --Parameters:
90 --IN:
91 --p_document_id
92 -- The id of the document that needs to be archived
93 --OUT:
94 --x_revision_num
95 -- The revision number of the PO
96 --x_return_status
97 -- 'Y' if archive needed
98 -- 'N' if archive not needed
99 --Testing:
100 -- None.
101 --End of Comments
102 -------------------------------------------------------------------------------
103 PROCEDURE CHECK_PO_ARCHIVE(p_document_id IN NUMBER,
104 x_revision_num OUT NOCOPY NUMBER,
105 x_return_status OUT NOCOPY VARCHAR2)
106 IS
107
108 l_api_name CONSTANT VARCHAR2(30) := 'CHECK_PO_ARCHIVE';
109 l_module VARCHAR2(100);
110 l_progress VARCHAR2(3);
111 l_arch_revision_num NUMBER;
112
113 BEGIN
114
115 l_progress := '000';
116 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
117 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_PROCEDURE THEN
118 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, l_module,
119 'Entering ' || G_PKG_NAME || '.' || l_api_name);
120 END IF;
121
122 l_progress := '010';
123 -- SQL What: Select current revision number and archived revision number
124 -- SQL Why : Check if the document is approved and not archived
125 SELECT NVL(ph.revision_num, 0),
126 NVL(pha.revision_num, -1)
127 INTO x_revision_num, l_arch_revision_num
128 FROM PO_HEADERS_ALL PH,
129 PO_HEADERS_ARCHIVE_ALL PHA
130 WHERE ph.po_header_id = p_document_id
131 AND ph.approved_date IS NOT NULL
132 AND ph.approved_flag = 'Y'
133 AND ph.po_header_id = pha.po_header_id (+)
134 AND pha.latest_external_flag(+) = 'Y';
135
136 l_progress := '020';
137 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
138 IF x_revision_num = l_arch_revision_num THEN
139 x_return_status := 'N';
140 ELSE
141 x_return_status := 'Y';
142 END IF; /*x_revision_num = l_arch_revision_num*/
143
144 EXCEPTION
145 WHEN OTHERS THEN
146 po_message_s.sql_error('Exception of CHECK_PO_ARCHIVE()',
147 l_progress , sqlcode);
148 FND_MSG_PUB.Add;
149 IF (G_FND_DEBUG = 'Y') THEN
150 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
151 FND_LOG.string(FND_LOG.level_unexpected, l_module,
152 'EXCEPTION: '||sqlerrm);
153 END IF;
154 END IF;
155 x_return_status := 'N';
156 END CHECK_PO_ARCHIVE;
157
158 -------------------------------------------------------------------------------
159 --Start of Comments
160 --Name: CHECK_RELEASE_ARCHIVE
161 --Pre-reqs:
162 -- None.
163 --Modifies:
164 -- None.
165 --Locks:
166 -- None.
167 --Function:
168 -- Get the current revision number and check if it is already archived
169 --Parameters:
170 --IN:
171 --p_document_id
172 -- The id of the document that needs to be archived
173 --OUT:
174 --x_revision_num
175 -- The revision number of the Release
176 --x_return_status
177 -- 'Y' if archive needed
178 -- 'N' if archive not needed
179 --Testing:
180 -- None.
181 --End of Comments
182 -------------------------------------------------------------------------------
183 PROCEDURE CHECK_RELEASE_ARCHIVE(p_document_id IN NUMBER,
184 x_revision_num OUT NOCOPY NUMBER,
185 x_return_status OUT NOCOPY VARCHAR2)
186 IS
187
188 l_api_name CONSTANT VARCHAR2(30) := 'CHECK_RELEASE_ARCHIVE';
189 l_module VARCHAR2(100);
190 l_progress VARCHAR2(3);
191 l_arch_revision_num NUMBER;
192
193 BEGIN
194
195 l_progress := '000';
196 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
197 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_PROCEDURE THEN
198 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, l_module,
199 'Entering ' || G_PKG_NAME || '.' || l_api_name);
200 END IF;
201
202 l_progress := '010';
203 -- SQL What: Select current revision number and archived revision number
204 -- SQL Why : Check if the document is approved and not archived
205 SELECT NVL(ph.revision_num, 0),
206 NVL(pha.revision_num, -1)
207 INTO x_revision_num, l_arch_revision_num
208 FROM PO_RELEASES_ALL PH,
209 PO_RELEASES_ARCHIVE_ALL PHA
210 WHERE ph.po_release_id = p_document_id
211 AND ph.approved_date IS NOT NULL
212 AND ph.approved_flag = 'Y'
213 AND ph.po_release_id = pha.po_release_id (+)
214 AND pha.latest_external_flag(+) = 'Y';
215
216 l_progress := '020';
217 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
218 IF x_revision_num = l_arch_revision_num THEN
219 x_return_status := 'N';
220 ELSE
221 x_return_status := 'Y';
222 END IF; /*x_revision_num = l_arch_revision_num*/
223
224 EXCEPTION
225 WHEN OTHERS THEN
226 po_message_s.sql_error('Exception of CHECK_RELEASE_ARCHIVE()',
227 l_progress , sqlcode);
228 FND_MSG_PUB.Add;
229 IF (G_FND_DEBUG = 'Y') THEN
230 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
231 FND_LOG.string(FND_LOG.level_unexpected, l_module,
232 'EXCEPTION: '||sqlerrm);
233 END IF;
234 END IF;
235 x_return_status := 'N';
236 END CHECK_RELEASE_ARCHIVE;
237
238 -------------------------------------------------------------------------------
239 --Start of Comments
240 --Name: ARCHIVE_HEADER
241 --Pre-reqs:
242 -- None.
243 --Modifies:
244 -- PO_HEADERS_ARCHIVE
245 --Locks:
246 -- None.
247 --Function:
248 -- Archive PO Header
249 --Parameters:
250 --IN:
251 --p_document_id
252 -- The id of the document that needs to be archived
253 --Testing:
254 -- None.
255 --End of Comments
256 -------------------------------------------------------------------------------
257 PROCEDURE ARCHIVE_HEADER(p_document_id IN NUMBER)
258 IS
259
260 l_api_name CONSTANT VARCHAR2(30) := 'ARCHIVE_HEADER';
261 l_module VARCHAR2(100);
262 l_progress VARCHAR2(3);
263
264 BEGIN
265
266 l_progress := '000';
267 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
268 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_PROCEDURE THEN
269 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, l_module,
270 'Entering ' || G_PKG_NAME || '.' || l_api_name);
271 END IF;
272
273 l_progress := '010';
274 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
275 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
276 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
277 'Update PO_HEADERS_ARCHIVE to reset latest_external_flag');
278 END IF;
279
280 UPDATE PO_HEADERS_ARCHIVE_ALL
281 SET latest_external_flag = 'N'
282 WHERE po_header_id = p_document_id
283 AND latest_external_flag = 'Y';
284
285 l_progress := '020';
286 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
287 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
288 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
289 'Insert PO_HEADERS_ARCHIVE ');
290 END IF;
291
292 INSERT INTO PO_HEADERS_ARCHIVE_ALL
293 (acceptance_due_date ,
294 acceptance_required_flag ,
295 agent_id ,
296 amount_limit ,
297 approval_required_flag ,
298 approved_date ,
299 approved_flag ,
300 attribute1 ,
301 attribute10 ,
302 attribute11 ,
303 attribute12 ,
304 attribute13 ,
305 attribute14 ,
306 attribute15 ,
307 attribute2 ,
308 attribute3 ,
309 attribute4 ,
310 attribute5 ,
311 attribute6 ,
312 attribute7 ,
313 attribute8 ,
314 attribute9 ,
315 attribute_category ,
316 authorization_status ,
317 bill_to_location_id ,
318 blanket_total_amount ,
319 cancel_flag ,
320 cbc_accounting_date ,
321 change_requested_by ,
322 change_summary ,
323 closed_code ,
324 closed_date ,
325 comments ,
326 confirming_order_flag ,
327 consigned_consumption_flag ,
328 consume_req_demand_flag ,
329 conterms_articles_upd_date ,
330 conterms_deliv_upd_date ,
331 conterms_exist_flag ,
332 created_by ,
333 creation_date ,
334 currency_code ,
335 edi_processed_flag ,
336 edi_processed_status ,
337 enabled_flag ,
338 encumbrance_required_flag ,
339 end_date ,
340 end_date_active ,
341 firm_date ,
342 firm_status_lookup_code ,
343 fob_lookup_code ,
344 freight_terms_lookup_code ,
345 from_header_id ,
346 from_type_lookup_code ,
347 frozen_flag ,
348 global_agreement_flag ,
349 global_attribute1 ,
350 global_attribute10 ,
351 global_attribute11 ,
352 global_attribute12 ,
353 global_attribute13 ,
354 global_attribute14 ,
355 global_attribute15 ,
356 global_attribute16 ,
357 global_attribute17 ,
358 global_attribute18 ,
359 global_attribute19 ,
360 global_attribute2 ,
361 global_attribute20 ,
362 global_attribute3 ,
363 global_attribute4 ,
364 global_attribute5 ,
365 global_attribute6 ,
366 global_attribute7 ,
367 global_attribute8 ,
368 global_attribute9 ,
369 global_attribute_category ,
370 government_context ,
371 interface_source_code ,
372 last_update_date ,
373 last_update_login ,
374 last_updated_by ,
375 min_release_amount ,
376 mrc_rate ,
377 mrc_rate_date ,
378 mrc_rate_type ,
379 note_to_authorizer ,
380 note_to_receiver ,
381 note_to_vendor ,
382 org_id ,
383 pay_on_code ,
384 pcard_id ,
385 pending_signature_flag ,
386 po_header_id ,
387 price_update_tolerance ,
388 print_count ,
389 printed_date ,
390 program_application_id ,
391 program_id ,
392 program_update_date ,
393 quotation_class_code ,
394 quote_type_lookup_code ,
395 quote_vendor_quote_number ,
396 quote_warning_delay ,
397 quote_warning_delay_unit ,
398 rate ,
399 rate_date ,
400 rate_type ,
401 reference_num ,
402 reply_date ,
403 reply_method_lookup_code ,
404 request_id ,
405 revised_date ,
406 revision_num ,
407 rfq_close_date ,
408 segment1 ,
409 segment2 ,
410 segment3 ,
411 segment4 ,
412 segment5 ,
413 ship_to_location_id ,
414 ship_via_lookup_code ,
415 shipping_control ,
416 start_date ,
417 start_date_active ,
418 status_lookup_code ,
419 summary_flag ,
420 supply_agreement_flag ,
421 terms_id ,
422 type_lookup_code ,
423 user_hold_flag ,
424 vendor_contact_id ,
425 vendor_id ,
426 vendor_order_num ,
427 vendor_site_id ,
428 wf_item_key ,
429 wf_item_type ,
430 xml_change_send_date ,
431 xml_flag ,
432 xml_send_date ,
433 latest_external_flag,
434 document_creation_method -- <DBI FPJ>
435 ,submit_date --<DBI Req Fulfillment 11.5.11>
436 ,style_id --<R12 STYLES PHASE II>
437 ,created_language --<Unified Catalog R12>
438 ,cpa_reference --<Unified Catalog R12>
439 ,last_updated_program --<Unified Catalog R12>
440 ,pay_when_paid -- E and C ER
441 , user_document_status --Mod project
442 , ame_approval_id -- Mod Project
443 , clm_document_number -- Document Nubering
444 -- <CLM View Base Document Start>
445 ,TAX_ATTRIBUTE_UPDATE_CODE
446 ,ENABLE_ALL_SITES
447 ,COMM_REV_NUM
448 ,OTM_STATUS_CODE
449 ,OTM_RECOVERY_FLAG
450 ,UDA_TEMPLATE_ID
451 ,UDA_TEMPLATE_DATE
452 ,CLM_EFFECTIVE_DATE
453 ,CLM_VENDOR_OFFER_NUMBER
454 ,CLM_AWARD_ADMINISTRATOR
455 ,CLM_NO_SIGNED_COPIES_TO_RETURN
456 ,CLM_MIN_GUARANTEE_AWARD_AMT
457 ,CLM_MIN_GUAR_AWARD_AMT_PERCENT
458 ,CLM_MIN_ORDER_AMOUNT
459 ,CLM_MAX_ORDER_AMOUNT
460 ,CLM_AMT_SYNCED_TO_AGREEMENT
461 ,CLM_AMOUNT_RELEASED
462 ,CLM_EXTERNAL_IDV
463 ,CLM_SUPPLIER_NAME
464 ,CLM_SUPPLIER_SITE_NAME
465 ,CLM_SOURCE_DOCUMENT_ID
466 ,CLM_ISSUING_OFFICE
467 ,CLM_COTR_OFFICE
468 ,CLM_COTR_CONTACT
469 ,CLM_PRIORITY_CODE
470 ,CLM_MOD_ISSUING_OFFICE
471 ,CLM_STANDARD_FORM
472 ,CLM_DOCUMENT_FORMAT
473 ,AME_TRANSACTION_TYPE
474 ,CLM_AWARD_TYPE
475 ,CLM_CONTRACT_OFFICER
476 ,CLM_CLOSEOUT_STATUS
477 ,USSGL_TRANSACTION_CODE
478 ,SUPPLIER_NOTIF_METHOD
479 ,FAX
480 ,EMAIL_ADDRESS
481 ,RETRO_PRICE_COMM_UPDATES_FLAG
482 ,RETRO_PRICE_APPLY_UPDATES_FLAG
483 ,UPDATE_SOURCING_RULES_FLAG
484 ,AUTO_SOURCING_FLAG
485 ,SUPPLIER_AUTH_ENABLED_FLAG
486 ,CAT_ADMIN_AUTH_ENABLED_FLAG
487 -- <CLM View Base Document End>
488 --Bug 13247538
489 ,umbrella_program_id
490 ,fon_ref_id
491 ,clm_payment_instr_code -- <Payment instruction>
492 ,clm_contract_finance_code -- <Payment instruction>
493 ,clm_special_contract_type -- <Sunset : Contingency Contract >
494 )
495 SELECT
496 acceptance_due_date ,
497 acceptance_required_flag ,
498 agent_id ,
499 amount_limit ,
500 approval_required_flag ,
501 approved_date ,
502 approved_flag ,
503 attribute1 ,
504 attribute10 ,
505 attribute11 ,
506 attribute12 ,
507 attribute13 ,
508 attribute14 ,
509 attribute15 ,
510 attribute2 ,
511 attribute3 ,
512 attribute4 ,
513 attribute5 ,
514 attribute6 ,
515 attribute7 ,
516 attribute8 ,
517 attribute9 ,
518 attribute_category ,
519 authorization_status ,
520 bill_to_location_id ,
521 blanket_total_amount ,
522 cancel_flag ,
523 cbc_accounting_date ,
524 change_requested_by ,
525 change_summary ,
526 closed_code ,
527 closed_date ,
528 comments ,
529 confirming_order_flag ,
530 consigned_consumption_flag ,
531 consume_req_demand_flag ,
532 conterms_articles_upd_date ,
533 conterms_deliv_upd_date ,
534 conterms_exist_flag ,
535 created_by ,
536 creation_date ,
537 currency_code ,
538 -- Bug 3438383, EDI Team expects EDI columns NULL
539 -- edi_processed_flag ,
540 -- edi_processed_status ,
541 NULL ,
542 NULL ,
543 enabled_flag ,
544 encumbrance_required_flag ,
545 end_date ,
546 end_date_active ,
547 firm_date ,
548 firm_status_lookup_code ,
549 fob_lookup_code ,
550 freight_terms_lookup_code ,
551 from_header_id ,
552 from_type_lookup_code ,
553 frozen_flag ,
554 global_agreement_flag ,
555 global_attribute1 ,
556 global_attribute10 ,
557 global_attribute11 ,
558 global_attribute12 ,
559 global_attribute13 ,
560 global_attribute14 ,
561 global_attribute15 ,
562 global_attribute16 ,
563 global_attribute17 ,
564 global_attribute18 ,
565 global_attribute19 ,
566 global_attribute2 ,
567 global_attribute20 ,
568 global_attribute3 ,
569 global_attribute4 ,
570 global_attribute5 ,
571 global_attribute6 ,
572 global_attribute7 ,
573 global_attribute8 ,
574 global_attribute9 ,
575 global_attribute_category ,
576 government_context ,
577 interface_source_code ,
578 last_update_date ,
579 last_update_login ,
580 last_updated_by ,
581 min_release_amount ,
582 mrc_rate ,
583 mrc_rate_date ,
584 mrc_rate_type ,
585 note_to_authorizer ,
586 note_to_receiver ,
587 note_to_vendor ,
588 org_id ,
589 pay_on_code ,
590 pcard_id ,
591 pending_signature_flag ,
592 po_header_id ,
593 price_update_tolerance ,
594 print_count ,
595 printed_date ,
596 program_application_id ,
597 program_id ,
598 program_update_date ,
599 quotation_class_code ,
600 quote_type_lookup_code ,
601 quote_vendor_quote_number ,
602 quote_warning_delay ,
603 quote_warning_delay_unit ,
604 rate ,
605 rate_date ,
606 rate_type ,
607 reference_num ,
608 reply_date ,
609 reply_method_lookup_code ,
610 request_id ,
611 revised_date ,
612 revision_num ,
613 rfq_close_date ,
614 segment1 ,
615 segment2 ,
616 segment3 ,
617 segment4 ,
618 segment5 ,
619 ship_to_location_id ,
620 ship_via_lookup_code ,
621 shipping_control ,
622 start_date ,
623 start_date_active ,
624 status_lookup_code ,
625 summary_flag ,
626 supply_agreement_flag ,
627 terms_id ,
628 type_lookup_code ,
629 user_hold_flag ,
630 vendor_contact_id ,
631 vendor_id ,
632 vendor_order_num ,
633 vendor_site_id ,
634 wf_item_key ,
635 wf_item_type ,
636 xml_change_send_date ,
637 xml_flag ,
638 xml_send_date ,
639 'Y',
640 document_creation_method -- <DBI FPJ>
641 ,submit_date --<DBI Req Fulfillment 11.5.11>
642 ,style_id --<R12 STYLES PHASE II>
643 , created_language --<Unified Catalog R12>
644 , cpa_reference --<Unified Catalog R12>
645 , last_updated_program --<Unified Catalog R12>
646 , pay_when_paid -- E and C ER
647 , user_document_status --Mod project
648 , ame_approval_id -- Mod Project
649 , clm_document_number -- Document Nubering
650 -- <CLM View Base Document Start>
651 ,TAX_ATTRIBUTE_UPDATE_CODE
652 ,ENABLE_ALL_SITES
653 ,COMM_REV_NUM
654 ,OTM_STATUS_CODE
655 ,OTM_RECOVERY_FLAG
656 ,UDA_TEMPLATE_ID
657 ,UDA_TEMPLATE_DATE
658 ,CLM_EFFECTIVE_DATE
659 ,CLM_VENDOR_OFFER_NUMBER
660 ,CLM_AWARD_ADMINISTRATOR
661 ,CLM_NO_SIGNED_COPIES_TO_RETURN
662 ,CLM_MIN_GUARANTEE_AWARD_AMT
663 ,CLM_MIN_GUAR_AWARD_AMT_PERCENT
664 ,CLM_MIN_ORDER_AMOUNT
665 ,CLM_MAX_ORDER_AMOUNT
666 ,CLM_AMT_SYNCED_TO_AGREEMENT
667 ,CLM_AMOUNT_RELEASED
668 ,CLM_EXTERNAL_IDV
669 ,CLM_SUPPLIER_NAME
670 ,CLM_SUPPLIER_SITE_NAME
671 ,CLM_SOURCE_DOCUMENT_ID
672 ,CLM_ISSUING_OFFICE
673 ,CLM_COTR_OFFICE
674 ,CLM_COTR_CONTACT
675 ,CLM_PRIORITY_CODE
676 ,CLM_MOD_ISSUING_OFFICE
677 ,CLM_STANDARD_FORM
678 ,CLM_DOCUMENT_FORMAT
679 ,AME_TRANSACTION_TYPE
680 ,CLM_AWARD_TYPE
681 ,CLM_CONTRACT_OFFICER
682 ,CLM_CLOSEOUT_STATUS
683 ,USSGL_TRANSACTION_CODE
684 ,SUPPLIER_NOTIF_METHOD
685 ,FAX
686 ,EMAIL_ADDRESS
687 ,RETRO_PRICE_COMM_UPDATES_FLAG
688 ,RETRO_PRICE_APPLY_UPDATES_FLAG
689 ,UPDATE_SOURCING_RULES_FLAG
690 ,AUTO_SOURCING_FLAG
691 ,SUPPLIER_AUTH_ENABLED_FLAG
692 ,CAT_ADMIN_AUTH_ENABLED_FLAG
693 -- <CLM View Base Document End>
694 --Bug 13247538
695 ,umbrella_program_id
696 ,fon_ref_id
697 ,clm_payment_instr_code -- <Payment instruction>
698 ,clm_contract_finance_code -- <Payment instruction>
699 ,clm_special_contract_type -- <Sunset : Contingency Contract >
700 FROM PO_HEADERS_ALL
701 WHERE po_header_id = p_document_id;
702
703 l_progress := '030';
704
705 EXCEPTION
706 WHEN OTHERS THEN
707 po_message_s.sql_error('Exception of ARCHIVE_HEADER()',
708 l_progress , sqlcode);
709 FND_MSG_PUB.Add;
710 IF (G_FND_DEBUG = 'Y') THEN
711 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
712 FND_LOG.string(FND_LOG.level_unexpected, l_module,
713 'EXCEPTION: '||sqlerrm);
714 END IF;
715 END IF;
716 RAISE;
717 END ARCHIVE_HEADER;
718
719 -------------------------------------------------------------------------------
720 --Start of Comments
721 --Name: ARCHIVE_RELEASE
722 --Pre-reqs:
723 -- None.
724 --Modifies:
725 -- PO_RELEASES_ARCHIVE
726 --Locks:
727 -- None.
728 --Function:
729 -- Archive the release header
730 --Parameters:
731 --IN:
732 --p_document_id
733 -- The id of the document that needs to be archived
734 --Testing:
735 -- None.
736 --End of Comments
737 -------------------------------------------------------------------------------
738 PROCEDURE ARCHIVE_RELEASE(p_document_id IN NUMBER)
739 IS
740
741 l_api_name CONSTANT VARCHAR2(30) := 'ARCHIVE_RELEASE';
742 l_module VARCHAR2(100);
743 l_progress VARCHAR2(3);
744
745 BEGIN
746
747 l_progress := '000';
748 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
749 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_PROCEDURE THEN
750 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, l_module,
751 'Entering ' || G_PKG_NAME || '.' || l_api_name);
752 END IF;
753
754 l_progress := '010';
755 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
756 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
757 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
758 'Update PO_HEADERS_ARCHIVE to reset latest_external_flag');
759 END IF;
760
761 UPDATE PO_RELEASES_ARCHIVE_ALL
762 SET latest_external_flag = 'N'
763 WHERE po_release_id = p_document_id
764 AND latest_external_flag = 'Y';
765
766 l_progress := '020';
767 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
768 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
769 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
770 'Insert PO_RELEASES_ARCHIVE ');
771 END IF;
772
773 -- Archiving the releases
774 INSERT INTO PO_RELEASES_ARCHIVE_ALL
775 (acceptance_due_date ,
776 acceptance_required_flag ,
777 agent_id ,
778 approved_date ,
779 approved_flag ,
780 attribute1 ,
781 attribute10 ,
782 attribute11 ,
783 attribute12 ,
784 attribute13 ,
785 attribute14 ,
786 attribute15 ,
787 attribute2 ,
788 attribute3 ,
789 attribute4 ,
790 attribute5 ,
791 attribute6 ,
792 attribute7 ,
793 attribute8 ,
794 attribute9 ,
795 attribute_category ,
796 authorization_status ,
797 cancel_date ,
798 cancel_flag ,
799 cancel_reason ,
800 cancelled_by ,
801 cbc_accounting_date ,
802 change_requested_by ,
803 change_summary ,
804 closed_code ,
805 consigned_consumption_flag ,
806 created_by ,
807 creation_date ,
808 edi_processed_flag ,
809 firm_date ,
810 firm_status_lookup_code ,
811 frozen_flag ,
812 global_attribute1 ,
813 global_attribute10 ,
814 global_attribute11 ,
815 global_attribute12 ,
816 global_attribute13 ,
817 global_attribute14 ,
818 global_attribute15 ,
819 global_attribute16 ,
820 global_attribute17 ,
821 global_attribute18 ,
822 global_attribute19 ,
823 global_attribute2 ,
824 global_attribute20 ,
825 global_attribute3 ,
826 global_attribute4 ,
827 global_attribute5 ,
828 global_attribute6 ,
829 global_attribute7 ,
830 global_attribute8 ,
831 global_attribute9 ,
832 global_attribute_category ,
833 government_context ,
834 hold_by ,
835 hold_date ,
836 hold_flag ,
837 hold_reason ,
838 last_update_date ,
839 last_update_login ,
840 last_updated_by ,
841 note_to_vendor ,
842 org_id ,
843 pay_on_code ,
844 pcard_id ,
845 po_header_id ,
846 po_release_id ,
847 print_count ,
848 printed_date ,
849 program_application_id ,
850 program_id ,
851 program_update_date ,
852 release_date ,
853 release_num ,
854 release_type ,
855 request_id ,
856 revised_date ,
857 revision_num ,
858 shipping_control ,
859 vendor_order_num ,
860 wf_item_key ,
861 wf_item_type ,
862 xml_change_send_date ,
863 xml_flag ,
864 xml_send_date ,
865 latest_external_flag,
866 document_creation_method -- <DBI FPJ>
867 , submit_date --<DBI Req Fulfillment 11.5.11>
868 )
869 SELECT
870 acceptance_due_date ,
871 acceptance_required_flag ,
872 agent_id ,
873 approved_date ,
874 approved_flag ,
875 attribute1 ,
876 attribute10 ,
877 attribute11 ,
878 attribute12 ,
879 attribute13 ,
880 attribute14 ,
881 attribute15 ,
882 attribute2 ,
883 attribute3 ,
884 attribute4 ,
885 attribute5 ,
886 attribute6 ,
887 attribute7 ,
888 attribute8 ,
889 attribute9 ,
890 attribute_category ,
891 authorization_status ,
892 cancel_date ,
893 cancel_flag ,
894 cancel_reason ,
895 cancelled_by ,
896 cbc_accounting_date ,
897 change_requested_by ,
898 change_summary ,
899 closed_code ,
900 consigned_consumption_flag ,
901 created_by ,
902 creation_date ,
903 -- Bug 3438383, EDI Team expects EDI columns NULL
904 -- edi_processed_flag ,
905 NULL ,
906 firm_date ,
907 firm_status_lookup_code ,
908 frozen_flag ,
909 global_attribute1 ,
910 global_attribute10 ,
911 global_attribute11 ,
912 global_attribute12 ,
913 global_attribute13 ,
914 global_attribute14 ,
915 global_attribute15 ,
916 global_attribute16 ,
917 global_attribute17 ,
918 global_attribute18 ,
919 global_attribute19 ,
920 global_attribute2 ,
921 global_attribute20 ,
922 global_attribute3 ,
923 global_attribute4 ,
924 global_attribute5 ,
925 global_attribute6 ,
926 global_attribute7 ,
927 global_attribute8 ,
928 global_attribute9 ,
929 global_attribute_category ,
930 government_context ,
931 hold_by ,
932 hold_date ,
933 hold_flag ,
934 hold_reason ,
935 last_update_date ,
936 last_update_login ,
937 last_updated_by ,
938 note_to_vendor ,
939 org_id ,
940 pay_on_code ,
941 pcard_id ,
942 po_header_id ,
943 po_release_id ,
944 print_count ,
945 printed_date ,
946 program_application_id ,
947 program_id ,
948 program_update_date ,
949 release_date ,
950 release_num ,
951 release_type ,
952 request_id ,
953 revised_date ,
954 revision_num ,
955 shipping_control ,
956 vendor_order_num ,
957 wf_item_key ,
958 wf_item_type ,
959 xml_change_send_date ,
960 xml_flag ,
961 xml_send_date ,
962 'Y',
963 document_creation_method -- <DBI FPJ>
964 , submit_date --<DBI Req Fulfillment 11.5.11>
965 FROM PO_RELEASES_ALL
966 WHERE po_release_id = p_document_id;
967
968 l_progress := '030';
969
970 EXCEPTION
971 WHEN OTHERS THEN
972 po_message_s.sql_error('Exception of ARCHIVE_RELEASE()',
973 l_progress , sqlcode);
974 FND_MSG_PUB.Add;
975 IF (G_FND_DEBUG = 'Y') THEN
976 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
977 FND_LOG.string(FND_LOG.level_unexpected, l_module,
978 'EXCEPTION: '||sqlerrm);
979 END IF;
980 END IF;
981 RAISE;
982 END ARCHIVE_RELEASE;
983
984 -------------------------------------------------------------------------------
985 --Start of Comments
986 --Name: ARCHIVE_LINES
987 --Pre-reqs:
988 -- None.
989 --Modifies:
990 -- PO_LINES_ARCHIVE
991 --Locks:
992 -- None.
993 --Function:
994 -- Arcives the po document lines.
995 --Parameters:
996 --IN:
997 --p_document_id
998 -- The id of the document that needs to be archived.
999 --p_revision_num
1000 -- The revision of the document that needs to be archived.
1001 --Testing:
1002 -- None.
1003 --End of Comments
1004 -------------------------------------------------------------------------------
1005 PROCEDURE ARCHIVE_LINES(p_document_id IN NUMBER,
1006 p_revision_num IN NUMBER)
1007 IS
1008 l_api_name CONSTANT VARCHAR2(30) := 'ARCHIVE_LINES';
1009 l_module VARCHAR2(100);
1010 l_progress VARCHAR2(3);
1011 l_revision_num NUMBER;
1012 l_continue BOOLEAN := FALSE;
1013 l_clm_flag VARCHAR2(1) := 'N';
1014
1015 BEGIN
1016
1017 l_progress := '000';
1018 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
1019 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_PROCEDURE THEN
1020 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, l_module,
1021 'Entering ' || G_PKG_NAME || '.' || l_api_name);
1022 END IF;
1023
1024 l_progress := '010';
1025 --<PAR Project>
1026 l_clm_flag := PO_CLM_INTG_GRP.is_clm_po(p_document_id);
1027
1028 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
1029 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
1030 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
1031 'INSERT PO_LINES_ARCHIVE');
1032 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
1033 'l_clm_flag : ' || l_clm_flag);
1034 END IF;
1035
1036 /*Bug7286203 - Added some fields to archival check so that cancellation and archival are in sync*/
1037
1038 INSERT INTO PO_LINES_ARCHIVE_ALL
1039 (allow_price_override_flag,
1040 amount,
1041 attribute1,
1042 attribute10,
1043 attribute11,
1044 attribute12,
1045 attribute13,
1046 attribute14,
1047 attribute15,
1048 attribute2,
1049 attribute3,
1050 attribute4,
1051 attribute5,
1052 attribute6,
1053 attribute7,
1054 attribute8,
1055 attribute9,
1056 attribute_category,
1057 auction_display_number,
1058 auction_header_id,
1059 auction_line_number,
1060 base_qty,
1061 base_unit_price, -- <FPJ Advanced Price>
1062 base_uom,
1063 bid_line_number,
1064 bid_number,
1065 cancel_date,
1066 cancel_flag,
1067 cancel_reason,
1068 cancelled_by,
1069 capital_expense_flag,
1070 category_id,
1071 closed_by,
1072 closed_code,
1073 closed_date,
1074 closed_flag,
1075 closed_reason,
1076 committed_amount,
1077 contract_id,
1078 contract_num,
1079 contractor_first_name,
1080 contractor_last_name,
1081 created_by,
1082 creation_date,
1083 expiration_date,
1084 firm_date,
1085 firm_status_lookup_code,
1086 from_header_id,
1087 from_line_id,
1088 global_attribute1,
1089 global_attribute10,
1090 global_attribute11,
1091 global_attribute12,
1092 global_attribute13,
1093 global_attribute14,
1094 global_attribute15,
1095 global_attribute16,
1096 global_attribute17,
1097 global_attribute18,
1098 global_attribute19,
1099 global_attribute2,
1100 global_attribute20,
1101 global_attribute3,
1102 global_attribute4,
1103 global_attribute5,
1104 global_attribute6,
1105 global_attribute7,
1106 global_attribute8,
1107 global_attribute9,
1108 global_attribute_category,
1109 government_context,
1110 hazard_class_id,
1111 item_description,
1112 item_id,
1113 item_revision,
1114 job_id,
1115 last_update_date,
1116 last_update_login,
1117 last_updated_by,
1118 line_num,
1119 line_reference_num,
1120 line_type_id,
1121 list_price_per_unit,
1122 manual_price_change_flag, --<MANUAL PRICE OVERRIDE FPJ>
1123 market_price,
1124 max_order_quantity,
1125 min_order_quantity,
1126 min_release_amount,
1127 negotiated_by_preparer_flag,
1128 not_to_exceed_price,
1129 note_to_vendor,
1130 oke_contract_header_id,
1131 oke_contract_version_id,
1132 org_id,
1133 over_tolerance_error_flag,
1134 po_header_id,
1135 po_line_id,
1136 preferred_grade,
1137 price_break_lookup_code,
1138 price_type_lookup_code,
1139 program_application_id,
1140 program_id,
1141 program_update_date,
1142 project_id,
1143 qc_grade,
1144 qty_rcv_tolerance,
1145 quantity,
1146 quantity_committed,
1147 reference_num,
1148 request_id,
1149 retroactive_date,
1150 secondary_qty,
1151 secondary_quantity,
1152 secondary_unit_of_measure,
1153 secondary_uom,
1154 start_date,
1155 -- <SVC_NOTIFICATIONS START>
1156 svc_amount_notif_sent,
1157 svc_completion_notif_sent,
1158 -- <SVC_NOTIFICATIONS END>
1159 supplier_ref_number,
1160 task_id,
1161 tax_code_id,
1162 tax_name,
1163 taxable_flag,
1164 transaction_reason_code,
1165 type_1099,
1166 un_number_id,
1167 unit_meas_lookup_code,
1168 unit_price,
1169 unordered_flag,
1170 user_hold_flag,
1171 vendor_product_num,
1172 latest_external_flag,
1173 revision_num,
1174 order_type_lookup_code, -- <Complex Work R12>
1175 matching_basis, -- <Complex Work R12>
1176 purchase_basis, -- <Complex Work R12>
1177 max_retainage_amount, -- <Complex Work R12>
1178 retainage_rate, -- <Complex Work R12>
1179 progress_payment_rate, -- <Complex Work R12>
1180 recoupment_rate -- <Complex Work R12>
1181 , catalog_name --<Unified Catalog R12>
1182 , supplier_part_auxid --<Unified Catalog R12>
1183 , ip_category_id --<Unified Catalog R12>
1184 , last_updated_program --<Unified Catalog R12>
1185 /* CLM Changes start - serukull */
1186 ,LINE_NUM_DISPLAY,
1187 GROUP_LINE_ID ,
1188 CLM_INFO_FLAG ,
1189 CLM_OPTION_INDICATOR,
1190 CLM_OPTION_NUM ,
1191 CLM_OPTION_FROM_DATE ,
1192 CLM_OPTION_TO_DATE ,
1193 CLM_FUNDED_FLAG ,
1194 CLM_BASE_LINE_NUM
1195 ,CONTRACT_TYPE
1196 ,COST_CONSTRAINT /* CLM Changes End - serukull */
1197 , user_document_status --Mod project
1198 , CLM_EXERCISED_FLAG
1199 , CLM_EXERCISED_DATE
1200 -- <CLM View Base Document Start>
1201 ,FROM_LINE_LOCATION_ID
1202 ,TAX_ATTRIBUTE_UPDATE_CODE
1203 ,UDA_TEMPLATE_ID
1204 ,CLM_MIN_TOTAL_AMOUNT
1205 ,CLM_MAX_TOTAL_AMOUNT
1206 ,CLM_MIN_TOTAL_QUANTITY
1207 ,CLM_MAX_TOTAL_QUANTITY
1208 ,CLM_MIN_ORDER_AMOUNT
1209 ,CLM_MAX_ORDER_AMOUNT
1210 ,CLM_MIN_ORDER_QUANTITY
1211 ,CLM_MAX_ORDER_QUANTITY
1212 ,CLM_TOTAL_AMOUNT_ORDERED
1213 ,CLM_TOTAL_QUANTITY_ORDERED
1214 ,CLM_FSC_PSC
1215 ,CLM_MDAPS_MAIS
1216 ,CLM_NAICS
1217 ,CLM_ORDER_START_DATE
1218 ,CLM_ORDER_END_DATE
1219 ,CLM_IDC_TYPE
1220 ,USSGL_TRANSACTION_CODE
1221 -- <CLM View Base Document End>
1222 ,CLM_DELIVERY_EVENT_CODE -- <Event Based Delivery>
1223 ,CLM_POP_EXCEPTION_REASON, --<Sunset Memo POP Validation>
1224 CLM_UNDEF_FLAG, -- UCA Project Begin
1225 CLM_UNDEF_ACTION_CODE,
1226 CLM_APPROVED_UNDEF_AMOUNT,
1227 CLM_UDA_PRICING_TOTAL, --UCA PRoject End
1228 CLM_PAYMENT_INSTR_CODE, -- <Payment_instruction>
1229 CLM_EXHIBIT_NAME -- Elins project
1230 )
1231 SELECT
1232 POL.allow_price_override_flag,
1233 POL.amount,
1234 POL.attribute1,
1235 POL.attribute10,
1236 POL.attribute11,
1237 POL.attribute12,
1238 POL.attribute13,
1239 POL.attribute14,
1240 POL.attribute15,
1241 POL.attribute2,
1242 POL.attribute3,
1243 POL.attribute4,
1244 POL.attribute5,
1245 POL.attribute6,
1246 POL.attribute7,
1247 POL.attribute8,
1248 POL.attribute9,
1249 POL.attribute_category,
1250 POL.auction_display_number,
1251 POL.auction_header_id,
1252 POL.auction_line_number,
1253 POL.base_qty,
1254 POL.base_unit_price, -- <FPJ Advanced Price>
1255 POL.base_uom,
1256 POL.bid_line_number,
1257 POL.bid_number,
1258 POL.cancel_date,
1259 POL.cancel_flag,
1260 POL.cancel_reason,
1261 POL.cancelled_by,
1262 POL.capital_expense_flag,
1263 POL.category_id,
1264 POL.closed_by,
1265 POL.closed_code,
1266 POL.closed_date,
1267 POL.closed_flag,
1268 POL.closed_reason,
1269 POL.committed_amount,
1270 POL.contract_id,
1271 POL.contract_num,
1272 POL.contractor_first_name,
1273 POL.contractor_last_name,
1274 POL.created_by,
1275 POL.creation_date,
1276 POL.expiration_date,
1277 POL.firm_date,
1278 POL.firm_status_lookup_code,
1279 POL.from_header_id,
1280 POL.from_line_id,
1281 POL.global_attribute1,
1282 POL.global_attribute10,
1283 POL.global_attribute11,
1284 POL.global_attribute12,
1285 POL.global_attribute13,
1286 POL.global_attribute14,
1287 POL.global_attribute15,
1288 POL.global_attribute16,
1289 POL.global_attribute17,
1290 POL.global_attribute18,
1291 POL.global_attribute19,
1292 POL.global_attribute2,
1293 POL.global_attribute20,
1294 POL.global_attribute3,
1295 POL.global_attribute4,
1296 POL.global_attribute5,
1297 POL.global_attribute6,
1298 POL.global_attribute7,
1299 POL.global_attribute8,
1300 POL.global_attribute9,
1301 POL.global_attribute_category,
1302 POL.government_context,
1303 POL.hazard_class_id,
1304 POL.item_description,
1305 POL.item_id,
1306 POL.item_revision,
1307 POL.job_id,
1308 POL.last_update_date,
1309 POL.last_update_login,
1310 POL.last_updated_by,
1311 POL.line_num,
1312 POL.line_reference_num,
1313 POL.line_type_id,
1314 POL.list_price_per_unit,
1315 POL.manual_price_change_flag, --<MANUAL PRICE OVERRIDE FPJ>
1316 POL.market_price,
1317 POL.max_order_quantity,
1318 POL.min_order_quantity,
1319 POL.min_release_amount,
1320 POL.negotiated_by_preparer_flag,
1321 POL.not_to_exceed_price,
1322 POL.note_to_vendor,
1323 POL.oke_contract_header_id,
1324 POL.oke_contract_version_id,
1325 POL.org_id,
1326 POL.over_tolerance_error_flag,
1327 POL.po_header_id,
1328 POL.po_line_id,
1329 POL.preferred_grade,
1330 POL.price_break_lookup_code,
1331 POL.price_type_lookup_code,
1332 POL.program_application_id,
1333 POL.program_id,
1334 POL.program_update_date,
1335 POL.project_id,
1336 POL.qc_grade,
1337 POL.qty_rcv_tolerance,
1338 POL.quantity,
1339 POL.quantity_committed,
1340 POL.reference_num,
1341 POL.request_id,
1342 POL.retroactive_date,
1343 POL.secondary_qty,
1344 POL.secondary_quantity,
1345 POL.secondary_unit_of_measure,
1346 POL.secondary_uom,
1347 POL.start_date,
1348 -- <SVC_NOTIFICATIONS START>
1349 POL.svc_amount_notif_sent,
1350 POL.svc_completion_notif_sent,
1351 -- <SVC_NOTIFICATIONS END>
1352 POL.supplier_ref_number,
1353 POL.task_id,
1354 POL.tax_code_id,
1355 POL.tax_name,
1356 POL.taxable_flag,
1357 POL.transaction_reason_code,
1358 POL.type_1099,
1359 POL.un_number_id,
1360 POL.unit_meas_lookup_code,
1361 POL.unit_price,
1362 POL.unordered_flag,
1363 POL.user_hold_flag,
1364 POL.vendor_product_num,
1365 'Y',
1366 p_revision_num,
1367 POL.order_type_lookup_code, -- <Complex Work R12>
1368 POL.matching_basis, -- <Complex Work R12>
1369 POL.purchase_basis, -- <Complex Work R12>
1370 POL.max_retainage_amount, -- <Complex Work R12>
1371 POL.retainage_rate, -- <Complex Work R12>
1372 POL.progress_payment_rate, -- <Complex Work R12>
1373 POL.recoupment_rate -- <Complex Work R12>
1374 , POL.catalog_name --<Unified Catalog R12>
1375 , POL.supplier_part_auxid --<Unified Catalog R12>
1376 , POL.ip_category_id --<Unified Catalog R12>
1377 , POL.last_updated_program --<Unified Catalog R12>
1378 /* CLM Changes start - serukull */
1379 ,POL.LINE_NUM_DISPLAY,
1380 POL.GROUP_LINE_ID ,
1381 POL.CLM_INFO_FLAG ,
1382 POL.CLM_OPTION_INDICATOR,
1383 POL.CLM_OPTION_NUM ,
1384 POL.CLM_OPTION_FROM_DATE ,
1385 POL.CLM_OPTION_TO_DATE ,
1386 POL.CLM_FUNDED_FLAG ,
1387 POL.CLM_BASE_LINE_NUM
1388 ,POL.CONTRACT_TYPE
1389 ,POL.COST_CONSTRAINT /* CLM Changes End - serukull */
1390 , POL.user_document_status --Mod project
1391 , POL.CLM_EXERCISED_FLAG
1392 , POL.CLM_EXERCISED_DATE
1393 -- <CLM View Base Document Start>
1394 ,POL.FROM_LINE_LOCATION_ID
1395 ,POL.TAX_ATTRIBUTE_UPDATE_CODE
1396 ,POL.UDA_TEMPLATE_ID
1397 ,POL.CLM_MIN_TOTAL_AMOUNT
1398 ,POL.CLM_MAX_TOTAL_AMOUNT
1399 ,POL.CLM_MIN_TOTAL_QUANTITY
1400 ,POL.CLM_MAX_TOTAL_QUANTITY
1401 ,POL.CLM_MIN_ORDER_AMOUNT
1402 ,POL.CLM_MAX_ORDER_AMOUNT
1403 ,POL.CLM_MIN_ORDER_QUANTITY
1404 ,POL.CLM_MAX_ORDER_QUANTITY
1405 ,POL.CLM_TOTAL_AMOUNT_ORDERED
1406 ,POL.CLM_TOTAL_QUANTITY_ORDERED
1407 ,POL.CLM_FSC_PSC
1408 ,POL.CLM_MDAPS_MAIS
1409 ,POL.CLM_NAICS
1410 ,POL.CLM_ORDER_START_DATE
1411 ,POL.CLM_ORDER_END_DATE
1412 ,POL.CLM_IDC_TYPE
1413 ,POL.USSGL_TRANSACTION_CODE
1414 -- <CLM View Base Document End>
1415 ,POL.CLM_DELIVERY_EVENT_CODE -- <Event Based Delivery>
1416 ,POL.CLM_POP_EXCEPTION_REASON, --<Sunset Memo POP Validation>
1417 POL.CLM_UNDEF_FLAG, -- UCA Project Begin
1418 POL.CLM_UNDEF_ACTION_CODE,
1419 POL.CLM_APPROVED_UNDEF_AMOUNT,
1420 POL.CLM_UDA_PRICING_TOTAL, --UCA PRoject End
1421 POL.CLM_PAYMENT_INSTR_CODE, -- <Payment_instruction>
1422 POL.CLM_EXHIBIT_NAME -- Elins project
1423
1424 FROM PO_LINES_ALL POL,
1425 PO_LINES_ARCHIVE_ALL POLA
1426 WHERE POL.po_header_id = p_document_id
1427 AND POL.po_line_id = POLA.po_line_id (+)
1428 AND POLA.latest_external_flag (+) = 'Y'
1429 AND (--<PAR Project>:Copy all lines in case of CLM where revision num in base
1430 --and archive table do not match
1431 (l_clm_flag = 'Y' AND POL.revision_num <> POLA.revision_num) OR
1432 (POLA.po_line_id is NULL) OR
1433 (POL.amount IS NULL AND POLA.amount IS NOT NULL OR
1434 POL.amount IS NOT NULL AND POLA.amount IS NULL OR
1435 POL.amount <> POLA.amount) OR
1436 -- <FPJ Advanced Price START>
1437 (POL.base_unit_price IS NULL AND POLA.base_unit_price IS NOT NULL OR
1438 POL.base_unit_price IS NOT NULL AND POLA.base_unit_price IS NULL OR
1439 POL.base_unit_price <> POLA.base_unit_price) OR
1440 -- <FPJ Advanced Price END>
1441 (POL.cancel_flag IS NULL AND POLA.cancel_flag IS NOT NULL OR
1442 POL.cancel_flag IS NOT NULL AND POLA.cancel_flag IS NULL OR
1443 POL.cancel_flag <> POLA.cancel_flag) OR
1444 (POL.closed_flag IS NULL AND POLA.closed_flag IS NOT NULL OR
1445 POL.closed_flag IS NOT NULL AND POLA.closed_flag IS NULL OR
1446 POL.closed_flag <> POLA.closed_flag) OR
1447 (POL.committed_amount IS NULL AND POLA.committed_amount IS NOT NULL OR
1448 POL.committed_amount IS NOT NULL AND POLA.committed_amount IS NULL OR
1449 POL.committed_amount <> POLA.committed_amount) OR
1450 (POL.contract_id IS NULL AND POLA.contract_id IS NOT NULL OR
1451 POL.contract_id IS NOT NULL AND POLA.contract_id IS NULL OR
1452 POL.contract_id <> POLA.contract_id) OR
1453 (POL.contractor_first_name IS NULL AND POLA.contractor_first_name IS NOT NULL OR
1454 POL.contractor_first_name IS NOT NULL AND POLA.contractor_first_name IS NULL OR
1455 POL.contractor_first_name <> POLA.contractor_first_name) OR
1456 (POL.contractor_last_name IS NULL AND POLA.contractor_last_name IS NOT NULL OR
1457 POL.contractor_last_name IS NOT NULL AND POLA.contractor_last_name IS NULL OR
1458 POL.contractor_last_name <> POLA.contractor_last_name) OR
1459 (POL.expiration_date IS NULL AND POLA.expiration_date IS NOT NULL OR
1460 POL.expiration_date IS NOT NULL AND POLA.expiration_date IS NULL OR
1461 POL.expiration_date <> POLA.expiration_date) OR
1462 (POL.from_header_id IS NULL AND POLA.from_header_id IS NOT NULL OR
1463 POL.from_header_id IS NOT NULL AND POLA.from_header_id IS NULL OR
1464 POL.from_header_id <> POLA.from_header_id) OR
1465 (POL.from_line_id IS NULL AND POLA.from_line_id IS NOT NULL OR
1466 POL.from_line_id IS NOT NULL AND POLA.from_line_id IS NULL OR
1467 POL.from_line_id <> POLA.from_line_id) OR
1468 (POL.hazard_class_id IS NULL AND POLA.hazard_class_id IS NOT NULL OR
1469 POL.hazard_class_id IS NOT NULL AND POLA.hazard_class_id IS NULL OR
1470 POL.hazard_class_id <> POLA.hazard_class_id) OR
1471 (POL.item_description IS NULL AND POLA.item_description IS NOT NULL OR
1472 POL.item_description IS NOT NULL AND POLA.item_description IS NULL OR
1473 POL.item_description <> POLA.item_description) OR
1474 (POL.item_id IS NULL AND POLA.item_id IS NOT NULL OR
1475 POL.item_id IS NOT NULL AND POLA.item_id IS NULL OR
1476 POL.item_id <> POLA.item_id) OR
1477 (POL.item_revision IS NULL AND POLA.item_revision IS NOT NULL OR
1478 POL.item_revision IS NOT NULL AND POLA.item_revision IS NULL OR
1479 POL.item_revision <> POLA.item_revision) OR
1480 (POL.job_id IS NULL AND POLA.job_id IS NOT NULL OR
1481 POL.job_id IS NOT NULL AND POLA.job_id IS NULL OR
1482 POL.job_id <> POLA.job_id) OR
1483 (POL.line_num IS NULL AND POLA.line_num IS NOT NULL OR
1484 POL.line_num IS NOT NULL AND POLA.line_num IS NULL OR
1485 POL.line_num <> POLA.line_num) OR
1486 (POL.note_to_vendor IS NULL AND POLA.note_to_vendor IS NOT NULL OR
1487 POL.note_to_vendor IS NOT NULL AND POLA.note_to_vendor IS NULL OR
1488 POL.note_to_vendor <> POLA.note_to_vendor) OR
1489 (POL.price_type_lookup_code IS NULL AND POLA.price_type_lookup_code IS NOT NULL OR
1490 POL.price_type_lookup_code IS NOT NULL AND POLA.price_type_lookup_code IS NULL OR
1491 POL.price_type_lookup_code <> POLA.price_type_lookup_code) OR
1492 (POL.quantity IS NULL AND POLA.quantity IS NOT NULL OR
1493 POL.quantity IS NOT NULL AND POLA.quantity IS NULL OR
1494 POL.quantity <> POLA.quantity) OR
1495 (POL.quantity_committed IS NULL AND POLA.quantity_committed IS NOT NULL OR
1496 POL.quantity_committed IS NOT NULL AND POLA.quantity_committed IS NULL OR
1497 POL.quantity_committed <> POLA.quantity_committed) OR
1498 (POL.start_date IS NULL AND POLA.start_date IS NOT NULL OR
1499 POL.start_date IS NOT NULL AND POLA.start_date IS NULL OR
1500 POL.start_date <> POLA.start_date) OR
1501 (POL.unit_meas_lookup_code IS NULL AND POLA.unit_meas_lookup_code IS NOT NULL OR
1502 POL.unit_meas_lookup_code IS NOT NULL AND POLA.unit_meas_lookup_code IS NULL OR
1503 POL.unit_meas_lookup_code <> POLA.unit_meas_lookup_code) OR
1504 (POL.unit_price IS NULL AND POLA.unit_price IS NOT NULL OR
1505 POL.unit_price IS NOT NULL AND POLA.unit_price IS NULL OR
1506 POL.unit_price <> POLA.unit_price) OR
1507 -- Bug 3471211
1508 (POL.not_to_exceed_price IS NULL AND POLA.not_to_exceed_price IS NOT NULL OR
1509 POL.not_to_exceed_price IS NOT NULL AND POLA.not_to_exceed_price IS NULL OR
1510 POL.not_to_exceed_price <> POLA.not_to_exceed_price) OR
1511 (POL.un_number_id IS NULL AND POLA.un_number_id IS NOT NULL OR
1512 POL.un_number_id IS NOT NULL AND POLA.un_number_id IS NULL OR
1513 POL.un_number_id <> POLA.un_number_id) OR
1514 (POL.vendor_product_num IS NULL AND POLA.vendor_product_num IS NOT NULL OR
1515 POL.vendor_product_num IS NOT NULL AND POLA.vendor_product_num IS NULL OR
1516 POL.vendor_product_num <> POLA.vendor_product_num) OR
1517 -- <Complex Work R12 Start>
1518 (POL.max_retainage_amount IS NULL AND POLA.max_retainage_amount IS NOT NULL OR
1519 POL.max_retainage_amount IS NOT NULL AND POLA.max_retainage_amount IS NULL OR
1520 POL.max_retainage_amount <> POLA.max_retainage_amount) OR
1521 (POL.retainage_rate IS NULL AND POLA.retainage_rate IS NOT NULL OR
1522 POL.retainage_rate IS NOT NULL AND POLA.retainage_rate IS NULL OR
1523 POL.retainage_rate <> POLA.retainage_rate) OR
1524 (POL.progress_payment_rate IS NULL AND POLA.progress_payment_rate IS NOT NULL OR
1525 POL.progress_payment_rate IS NOT NULL AND POLA.progress_payment_rate IS NULL OR
1526 POL.progress_payment_rate <> POLA.progress_payment_rate) OR
1527 (POL.recoupment_rate IS NULL AND POLA.recoupment_rate IS NOT NULL OR
1528 POL.recoupment_rate IS NOT NULL AND POLA.recoupment_rate IS NULL OR
1529 POL.recoupment_rate <> POLA.recoupment_rate) OR
1530 -- <Complex Work R12 End>
1531 -- <SVC_NOTIFICATIONS START>
1532 ((POL.svc_amount_notif_sent IS NULL AND
1533 POLA.svc_amount_notif_sent IS NOT NULL) OR
1534 (POL.svc_amount_notif_sent IS NOT NULL AND
1535 POLA.svc_amount_notif_sent IS NULL) OR
1536 (POL.svc_amount_notif_sent <> POLA.svc_amount_notif_sent)) OR
1537 ((POL.svc_completion_notif_sent IS NULL AND
1538 POLA.svc_completion_notif_sent IS NOT NULL) OR
1539 (POL.svc_completion_notif_sent IS NOT NULL AND
1540 POLA.svc_completion_notif_sent IS NULL) OR
1541 (POL.svc_completion_notif_sent <> POLA.svc_completion_notif_sent))
1542 -- <SVC_NOTIFICATIONS END>
1543 -- Bug 14736363: Remove the closed_code checking for archiving PO
1544 );
1545
1546 l_continue := (SQL%ROWCOUNT > 0);
1547
1548 IF l_continue THEN
1549 l_progress := '020';
1550 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
1551 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
1552 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
1553 'Update PO_LINES_ARCHIVE to reset latest_external_flag');
1554 END IF;
1555
1556 -- If a row was inserted into PO_LINES_ARCHIVE, then set the appropriate flags
1557 UPDATE PO_LINES_ARCHIVE_ALL POL1
1558 SET latest_external_flag = 'N'
1559 WHERE po_header_id = p_document_id
1560 AND latest_external_flag = 'Y'
1561 AND revision_num < p_revision_num
1562 AND EXISTS
1563 (SELECT 'A new archived row'
1564 FROM PO_LINES_ARCHIVE_ALL POL2
1565 WHERE POL2.po_line_id = POL1.po_line_id
1566 AND POL2.latest_external_flag = 'Y'
1567 AND POL2.revision_num = p_revision_num);
1568 ELSE
1569 l_progress := '030';
1570 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
1571 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
1572 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
1573 'No need to reset latest_external_flag');
1574 END IF;
1575 END IF; /* IF l_continue */
1576
1577 l_progress := '030';
1578
1579 EXCEPTION
1580 WHEN OTHERS THEN
1581 po_message_s.sql_error('Exception of ARCHIVE_LINES()',
1582 l_progress , sqlcode);
1583 FND_MSG_PUB.Add;
1584 IF (G_FND_DEBUG = 'Y') THEN
1585 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
1586 FND_LOG.string(FND_LOG.level_unexpected, l_module,
1587 'EXCEPTION: '||sqlerrm);
1588 END IF;
1589 END IF;
1590 RAISE;
1591 END ARCHIVE_LINES;
1592
1593 -------------------------------------------------------------------------------
1594 --Start of Comments
1595 --Name: ARCHIVE_LINE_LOCATIONS
1596 --Pre-reqs:
1597 -- None.
1598 --Modifies:
1599 -- PO_LINE_LOCATIONS_ARCHIVE
1600 --Locks:
1601 -- None.
1602 --Function:
1603 -- Arcives the po document line locations.
1604 --Parameters:
1605 --IN:
1606 --p_document_id
1607 -- The id of the document that needs to be archived.
1608 --p_document_type
1609 -- The type of the document to archive
1610 -- PO : For Standard/Planned
1611 -- PA : For Blanket/Contract
1612 -- RELEASE : Release
1613 --p_revision_num
1614 -- The revision of the document that needs to be archived.
1615 --Testing:
1616 -- None.
1617 --End of Comments
1618 -------------------------------------------------------------------------------
1619 PROCEDURE ARCHIVE_LINE_LOCATIONS(p_document_id IN NUMBER,
1620 p_document_type IN VARCHAR2,
1621 p_revision_num IN NUMBER)
1622 IS
1623 l_api_name CONSTANT VARCHAR2(30) := 'ARCHIVE_LINE_LOCATIONS';
1624 l_module VARCHAR2(100);
1625 l_progress VARCHAR2(3);
1626 l_revision_num NUMBER;
1627 l_continue BOOLEAN := FALSE;
1628 l_clm_flag VARCHAR2(1) := 'N';
1629 BEGIN
1630
1631 l_progress := '000';
1632 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
1633 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_PROCEDURE THEN
1634 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, l_module,
1635 'Entering ' || G_PKG_NAME || '.' || l_api_name);
1636 END IF;
1637
1638 l_progress := '010';
1639 --<PAR Project>
1640 l_clm_flag := PO_CLM_INTG_GRP.is_clm_po(p_document_id);
1641
1642 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
1643 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
1644 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
1645 'INSERT PO_LINE_LOCATIONS_ARCHIVE');
1646 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
1647 'CLM Flag : '|| l_clm_flag);
1648 END IF;
1649
1650 INSERT INTO PO_LINE_LOCATIONS_ARCHIVE_ALL
1651 (accrue_on_receipt_flag,
1652 allow_substitute_receipts_flag,
1653 amount,
1654 amount_accepted,
1655 amount_billed,
1656 amount_cancelled,
1657 amount_received,
1658 amount_rejected,
1659 approved_date,
1660 approved_flag,
1661 attribute1,
1662 attribute10,
1663 attribute11,
1664 attribute12,
1665 attribute13,
1666 attribute14,
1667 attribute15,
1668 attribute2,
1669 attribute3,
1670 attribute4,
1671 attribute5,
1672 attribute6,
1673 attribute7,
1674 attribute8,
1675 attribute9,
1676 attribute_category,
1677 calculate_tax_flag,
1678 cancel_date,
1679 cancel_flag,
1680 cancel_reason,
1681 cancelled_by,
1682 change_promised_date_reason,
1683 closed_by,
1684 closed_code,
1685 closed_date,
1686 closed_flag,
1687 closed_reason,
1688 consigned_flag,
1689 country_of_origin_code,
1690 created_by,
1691 creation_date,
1692 days_early_receipt_allowed,
1693 days_late_receipt_allowed,
1694 drop_ship_flag,
1695 encumber_now,
1696 encumbered_date,
1697 encumbered_flag,
1698 end_date,
1699 enforce_ship_to_location_code,
1700 estimated_tax_amount,
1701 final_match_flag, --<BUG 3431828>
1702 firm_date,
1703 firm_status_lookup_code,
1704 fob_lookup_code,
1705 freight_terms_lookup_code,
1706 from_header_id,
1707 from_line_id,
1708 from_line_location_id,
1709 global_attribute1,
1710 global_attribute10,
1711 global_attribute11,
1712 global_attribute12,
1713 global_attribute13,
1714 global_attribute14,
1715 global_attribute15,
1716 global_attribute16,
1717 global_attribute17,
1718 global_attribute18,
1719 global_attribute19,
1720 global_attribute2,
1721 global_attribute20,
1722 global_attribute3,
1723 global_attribute4,
1724 global_attribute5,
1725 global_attribute6,
1726 global_attribute7,
1727 global_attribute8,
1728 global_attribute9,
1729 global_attribute_category,
1730 government_context,
1731 inspection_required_flag,
1732 invoice_close_tolerance,
1733 last_accept_date,
1734 last_update_date,
1735 last_update_login,
1736 last_updated_by,
1737 lead_time,
1738 lead_time_unit,
1739 line_location_id,
1740 manual_price_change_flag, --<MANUAL PRICE OVERRIDE FPJ>
1741 match_option,
1742 need_by_date,
1743 note_to_receiver,
1744 org_id,
1745 po_header_id,
1746 po_line_id,
1747 po_release_id,
1748 preferred_grade,
1749 price_discount,
1750 price_override,
1751 program_application_id,
1752 program_id,
1753 program_update_date,
1754 promised_date,
1755 qty_rcv_exception_code,
1756 qty_rcv_tolerance,
1757 quantity,
1758 quantity_accepted,
1759 quantity_billed,
1760 quantity_cancelled,
1761 quantity_received,
1762 quantity_rejected,
1763 quantity_shipped,
1764 receipt_days_exception_code,
1765 receipt_required_flag,
1766 receive_close_tolerance,
1767 receiving_routing_id,
1768 request_id,
1769 retroactive_date,
1770 sales_order_update_date,
1771 secondary_quantity,
1772 secondary_quantity_accepted,
1773 secondary_quantity_cancelled,
1774 secondary_quantity_received,
1775 secondary_quantity_rejected,
1776 secondary_unit_of_measure,
1777 ship_to_location_id,
1778 ship_to_organization_id,
1779 ship_via_lookup_code,
1780 shipment_num,
1781 shipment_type,
1782 source_shipment_id,
1783 start_date,
1784 supplier_order_line_number,
1785 tax_code_id,
1786 tax_user_override_flag,
1787 taxable_flag,
1788 terms_id,
1789 transaction_flow_header_id,
1790 unencumbered_quantity,
1791 unit_meas_lookup_code,
1792 unit_of_measure_class,
1793 vmi_flag,
1794 latest_external_flag,
1795 revision_num
1796 --<DBI Req Fulfillment 11.5.11 Start>
1797 ,shipment_closed_date
1798 ,closed_for_receiving_date
1799 ,closed_for_invoice_date
1800 --<DBI Req Fulfillment 11.5.11 End>
1801 -- <Complex Work R12 Start>
1802 , value_basis
1803 , matching_basis
1804 , description
1805 , payment_type
1806 , work_approver_id
1807 , bid_payment_id
1808 , quantity_financed
1809 , amount_financed
1810 , quantity_recouped
1811 , amount_recouped
1812 , retainage_withheld_amount
1813 , retainage_released_amount
1814 -- <Complex work R12 End>
1815 , outsourced_assembly --<SHIKYU R12>
1816 -- <CLM View Base Document Start>
1817 ,TAX_ATTRIBUTE_UPDATE_CODE
1818 ,ORIGINAL_SHIPMENT_ID
1819 ,LCM_FLAG
1820 ,UDA_TEMPLATE_ID
1821 ,CLM_PERIOD_PERF_END_DATE
1822 ,CLM_PERIOD_PERF_START_DATE
1823 ,SECONDARY_QUANTITY_SHIPPED
1824 ,AMOUNT_SHIPPED
1825 ,TAX_NAME
1826 ,USSGL_TRANSACTION_CODE
1827 -- <CLM View Base Document End>
1828 ,CLM_DELIVERY_PERIOD --<Event Based Delivery>
1829 ,CLM_DELIVERY_PERIOD_UOM
1830 ,CLM_PROMISE_PERIOD
1831 ,CLM_PROMISE_PERIOD_UOM
1832 ,CLM_POP_DURATION
1833 ,CLM_POP_DURATION_UOM
1834
1835 )
1836 SELECT
1837 POL.accrue_on_receipt_flag,
1838 POL.allow_substitute_receipts_flag,
1839 POL.amount,
1840 POL.amount_accepted,
1841 POL.amount_billed,
1842 POL.amount_cancelled,
1843 POL.amount_received,
1844 POL.amount_rejected,
1845 POL.approved_date,
1846 POL.approved_flag,
1847 POL.attribute1,
1848 POL.attribute10,
1849 POL.attribute11,
1850 POL.attribute12,
1851 POL.attribute13,
1852 POL.attribute14,
1853 POL.attribute15,
1854 POL.attribute2,
1855 POL.attribute3,
1856 POL.attribute4,
1857 POL.attribute5,
1858 POL.attribute6,
1859 POL.attribute7,
1860 POL.attribute8,
1861 POL.attribute9,
1862 POL.attribute_category,
1863 POL.calculate_tax_flag,
1864 POL.cancel_date,
1865 POL.cancel_flag,
1866 POL.cancel_reason,
1867 POL.cancelled_by,
1868 POL.change_promised_date_reason,
1869 POL.closed_by,
1870 POL.closed_code,
1871 POL.closed_date,
1872 POL.closed_flag,
1873 POL.closed_reason,
1874 POL.consigned_flag,
1875 POL.country_of_origin_code,
1876 POL.created_by,
1877 POL.creation_date,
1878 POL.days_early_receipt_allowed,
1879 POL.days_late_receipt_allowed,
1880 POL.drop_ship_flag,
1881 POL.encumber_now,
1882 POL.encumbered_date,
1883 POL.encumbered_flag,
1884 POL.end_date,
1885 POL.enforce_ship_to_location_code,
1886 POL.estimated_tax_amount,
1887 POL.final_match_flag, --<BUG 3431828>
1888 POL.firm_date,
1889 POL.firm_status_lookup_code,
1890 POL.fob_lookup_code,
1891 POL.freight_terms_lookup_code,
1892 POL.from_header_id,
1893 POL.from_line_id,
1894 POL.from_line_location_id,
1895 POL.global_attribute1,
1896 POL.global_attribute10,
1897 POL.global_attribute11,
1898 POL.global_attribute12,
1899 POL.global_attribute13,
1900 POL.global_attribute14,
1901 POL.global_attribute15,
1902 POL.global_attribute16,
1903 POL.global_attribute17,
1904 POL.global_attribute18,
1905 POL.global_attribute19,
1906 POL.global_attribute2,
1907 POL.global_attribute20,
1908 POL.global_attribute3,
1909 POL.global_attribute4,
1910 POL.global_attribute5,
1911 POL.global_attribute6,
1912 POL.global_attribute7,
1913 POL.global_attribute8,
1914 POL.global_attribute9,
1915 POL.global_attribute_category,
1916 POL.government_context,
1917 POL.inspection_required_flag,
1918 POL.invoice_close_tolerance,
1919 POL.last_accept_date,
1920 POL.last_update_date,
1921 POL.last_update_login,
1922 POL.last_updated_by,
1923 POL.lead_time,
1924 POL.lead_time_unit,
1925 POL.line_location_id,
1926 POL.manual_price_change_flag, --<MANUAL PRICE OVERRIDE FPJ>
1927 POL.match_option,
1928 POL.need_by_date,
1929 POL.note_to_receiver,
1930 POL.org_id,
1931 POL.po_header_id,
1932 POL.po_line_id,
1933 POL.po_release_id,
1934 POL.preferred_grade,
1935 POL.price_discount,
1936 POL.price_override,
1937 POL.program_application_id,
1938 POL.program_id,
1939 POL.program_update_date,
1940 POL.promised_date,
1941 POL.qty_rcv_exception_code,
1942 POL.qty_rcv_tolerance,
1943 POL.quantity,
1944 POL.quantity_accepted,
1945 POL.quantity_billed,
1946 POL.quantity_cancelled,
1947 POL.quantity_received,
1948 POL.quantity_rejected,
1949 POL.quantity_shipped,
1950 POL.receipt_days_exception_code,
1951 POL.receipt_required_flag,
1952 POL.receive_close_tolerance,
1953 POL.receiving_routing_id,
1954 POL.request_id,
1955 POL.retroactive_date,
1956 POL.sales_order_update_date,
1957 POL.secondary_quantity,
1958 POL.secondary_quantity_accepted,
1959 POL.secondary_quantity_cancelled,
1960 POL.secondary_quantity_received,
1961 POL.secondary_quantity_rejected,
1962 POL.secondary_unit_of_measure,
1963 POL.ship_to_location_id,
1964 POL.ship_to_organization_id,
1965 POL.ship_via_lookup_code,
1966 POL.shipment_num,
1967 POL.shipment_type,
1968 POL.source_shipment_id,
1969 POL.start_date,
1970 POL.supplier_order_line_number,
1971 POL.tax_code_id,
1972 POL.tax_user_override_flag,
1973 POL.taxable_flag,
1974 POL.terms_id,
1975 POL.transaction_flow_header_id,
1976 POL.unencumbered_quantity,
1977 POL.unit_meas_lookup_code,
1978 POL.unit_of_measure_class,
1979 POL.vmi_flag,
1980 'Y',
1981 p_revision_num
1982 --<DBI Req Fulfillment 11.5.11 Start>
1983 ,POL.shipment_closed_date
1984 ,POL.closed_for_receiving_date
1985 ,POL.closed_for_invoice_date
1986 --<DBI Req Fulfillment 11.5.11 End>
1987 -- <Complex Work R12 Start>
1988 , POL.value_basis
1989 , POL.matching_basis
1990 , POL.description
1991 , POL.payment_type
1992 , POL.work_approver_id
1993 , POL.bid_payment_id
1994 , POL.quantity_financed
1995 , POL.amount_financed
1996 , POL.quantity_recouped
1997 , POL.amount_recouped
1998 , POL.retainage_withheld_amount
1999 , POL.retainage_released_amount
2000 -- <Complex work R12 End>
2001 , POL.outsourced_assembly --<SHIKYU R12>
2002 -- <CLM View Base Document Start>
2003 ,POL.TAX_ATTRIBUTE_UPDATE_CODE
2004 ,POL.ORIGINAL_SHIPMENT_ID
2005 ,POL.LCM_FLAG
2006 ,POL.UDA_TEMPLATE_ID
2007 ,POL.CLM_PERIOD_PERF_END_DATE
2008 ,POL.CLM_PERIOD_PERF_START_DATE
2009 ,POL.SECONDARY_QUANTITY_SHIPPED
2010 ,POL.AMOUNT_SHIPPED
2011 ,POL.TAX_NAME
2012 ,POL.USSGL_TRANSACTION_CODE
2013 -- <CLM View Base Document End>
2014 ,POL.CLM_DELIVERY_PERIOD --<Event Based Delivery>
2015 ,POL.CLM_DELIVERY_PERIOD_UOM
2016 ,POL.CLM_PROMISE_PERIOD
2017 ,POL.CLM_PROMISE_PERIOD_UOM
2018 ,POL.CLM_POP_DURATION
2019 ,POL.CLM_POP_DURATION_UOM
2020 FROM PO_LINE_LOCATIONS_ALL POL,
2021 PO_LINE_LOCATIONS_ARCHIVE_ALL POLA
2022 WHERE ((p_document_type = 'RELEASE' AND
2023 POL.po_release_id = p_document_id) OR
2024 (p_document_type <> 'RELEASE' AND -- Bug 3210749
2025 POL.po_header_id = p_document_id AND
2026 POL.po_release_id IS NULL))
2027 AND POL.line_location_id = POLA.line_location_id (+)
2028 AND POLA.latest_external_flag (+) = 'Y'
2029 AND (--<PAR Project>:Copy all lines in case of CLM where revision num in base
2030 --and archive table do not match
2031 (l_clm_flag = 'Y' AND POL.revision_num <> POLA.revision_num) OR
2032 (POLA.line_location_id is NULL) OR
2033 (POL.amount IS NULL AND POLA.amount IS NOT NULL OR
2034 POL.amount IS NOT NULL AND POLA.amount IS NULL OR
2035 POL.amount <> POLA.amount) OR
2036 (POL.cancel_flag IS NULL AND POLA.cancel_flag IS NOT NULL OR
2037 POL.cancel_flag IS NOT NULL AND POLA.cancel_flag IS NULL OR
2038 POL.cancel_flag <> POLA.cancel_flag) OR
2039 (POL.end_date IS NULL AND POLA.end_date IS NOT NULL OR
2040 POL.end_date IS NOT NULL AND POLA.end_date IS NULL OR
2041 POL.end_date <> POLA.end_date) OR
2042 (POL.last_accept_date IS NULL AND POLA.last_accept_date IS NOT NULL OR
2043 POL.last_accept_date IS NOT NULL AND POLA.last_accept_date IS NULL OR
2044 POL.last_accept_date <> POLA.last_accept_date) OR
2045 (POL.need_by_date IS NULL AND POLA.need_by_date IS NOT NULL OR
2046 POL.need_by_date IS NOT NULL AND POLA.need_by_date IS NULL OR
2047 POL.need_by_date <> POLA.need_by_date) OR
2048 (POL.price_override IS NULL AND POLA.price_override IS NOT NULL OR
2049 POL.price_override IS NOT NULL AND POLA.price_override IS NULL OR
2050 POL.price_override <> POLA.price_override) OR
2051 (POL.promised_date IS NULL AND POLA.promised_date IS NOT NULL OR
2052 POL.promised_date IS NOT NULL AND POLA.promised_date IS NULL OR
2053 POL.promised_date <> POLA.promised_date) OR
2054 (POL.quantity IS NULL AND POLA.quantity IS NOT NULL OR
2055 POL.quantity IS NOT NULL AND POLA.quantity IS NULL OR
2056 POL.quantity <> POLA.quantity) OR
2057 (POL.shipment_num IS NULL AND POLA.shipment_num IS NOT NULL OR
2058 POL.shipment_num IS NOT NULL AND POLA.shipment_num IS NULL OR
2059 POL.shipment_num <> POLA.shipment_num) OR
2060 --<Complex Work R12 Start>
2061 (POL.payment_type IS NULL AND POLA.payment_type IS NOT NULL OR
2062 POL.payment_type IS NOT NULL AND POLA.payment_type IS NULL OR
2063 POL.payment_type <> POLA.payment_type) OR
2064 (POL.description IS NULL AND POLA.description IS NOT NULL OR
2065 POL.description IS NOT NULL AND POLA.description IS NULL OR
2066 POL.description <> POLA.description) OR
2067 (POL.work_approver_id IS NULL AND POLA.work_approver_id IS NOT NULL OR
2068 POL.work_approver_id IS NOT NULL AND POLA.work_approver_id IS NULL OR
2069 POL.work_approver_id <> POLA.work_approver_id) OR
2070 --<Complex Work R12 End>
2071 (POL.ship_to_location_id IS NULL AND POLA.ship_to_location_id IS NOT NULL OR
2072 POL.ship_to_location_id IS NOT NULL AND POLA.ship_to_location_id IS NULL OR
2073 POL.ship_to_location_id <> POLA.ship_to_location_id) OR
2074 (POL.start_date IS NULL AND POLA.start_date IS NOT NULL OR
2075 POL.start_date IS NOT NULL AND POLA.start_date IS NULL OR
2076 POL.start_date <> POLA.start_date) OR
2077 (POL.taxable_flag IS NULL AND POLA.taxable_flag IS NOT NULL OR
2078 POL.taxable_flag IS NOT NULL AND POLA.taxable_flag IS NULL OR
2079 POL.taxable_flag <> POLA.taxable_flag) OR
2080 (POL.sales_order_update_date IS NULL AND POLA.sales_order_update_date IS NOT NULL OR --BUG7286203
2081 POL.sales_order_update_date IS NOT NULL AND POLA.sales_order_update_date IS NULL OR
2082 POL.sales_order_update_date <> POLA.sales_order_update_date));
2083
2084 l_continue := (SQL%ROWCOUNT > 0);
2085
2086 IF l_continue THEN
2087 l_progress := '020';
2088 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
2089 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
2090 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
2091 'Update PO_LINE_LOCATIONS_ARCHIVE to reset latest_external_flag');
2092 END IF;
2093
2094
2095 -- If a row was inserted into PO_LINES_ARCHIVE, then set the appropriate flags
2096
2097 -- Start Bug 3648767: Split up update statement on p_document_type
2098 -- so that the the cost-based optimizer will choose to
2099 -- use the indexes on po_release_id and po_header_id.
2100 -- Comments removed for bug 3210749
2101
2102 IF (p_document_type = 'RELEASE') THEN
2103
2104 UPDATE PO_LINE_LOCATIONS_ARCHIVE_ALL POL1
2105 SET latest_external_flag = 'N'
2106 WHERE po_release_id = p_document_id
2107 AND latest_external_flag = 'Y'
2108 AND revision_num < p_revision_num
2109 AND EXISTS
2110 (SELECT 'A new archived row'
2111 FROM PO_LINE_LOCATIONS_ARCHIVE_ALL POL2
2112 WHERE POL2.line_location_id = POL1.line_location_id
2113 AND POL2.latest_external_flag = 'Y'
2114 AND POL2.revision_num = p_revision_num
2115 );
2116
2117 ELSE
2118 -- p_document_type <> 'RELEASE'
2119
2120 UPDATE PO_LINE_LOCATIONS_ARCHIVE_ALL POL1
2121 SET latest_external_flag = 'N'
2122 WHERE ((po_header_id = p_document_id) AND (po_release_id IS NULL))
2123 AND latest_external_flag = 'Y'
2124 AND revision_num < p_revision_num
2125 AND EXISTS
2126 (SELECT 'A new archived row'
2127 FROM PO_LINE_LOCATIONS_ARCHIVE_ALL POL2
2128 WHERE POL2.line_location_id = POL1.line_location_id
2129 AND POL2.latest_external_flag = 'Y'
2130 AND POL2.revision_num = p_revision_num
2131 );
2132
2133 END IF; -- IF p_document_type = 'RELEASE'
2134
2135 -- End Bug 3648767
2136
2137 ELSE
2138 l_progress := '030';
2139 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
2140 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
2141 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
2142 'No need to reset latest_external_flag');
2143 END IF;
2144 END IF; /* IF l_continue */
2145
2146 l_progress := '030';
2147
2148 EXCEPTION
2149 WHEN OTHERS THEN
2150 po_message_s.sql_error('Exception of ARCHIVE_LINE_LOCATIONS()',
2151 l_progress , sqlcode);
2152 FND_MSG_PUB.Add;
2153 IF (G_FND_DEBUG = 'Y') THEN
2154 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
2155 FND_LOG.string(FND_LOG.level_unexpected, l_module,
2156 'EXCEPTION: '||sqlerrm);
2157 END IF;
2158 END IF;
2159 RAISE;
2160
2161 END ARCHIVE_LINE_LOCATIONS;
2162
2163 -------------------------------------------------------------------------------
2164 --Start of Comments
2165 --Name: ARCHIVE_DISTRIBUTIONS
2166 --Pre-reqs:
2167 -- None.
2168 --Modifies:
2169 -- PO_DISTRIBUTIONS_ARCHIVE
2170 --Locks:
2171 -- None.
2172 --Function:
2173 -- Arcives the po document distributions.
2174 --Parameters:
2175 --IN:
2176 --p_document_id
2177 -- The id of the document that needs to be archived.
2178 --p_document_type
2179 -- The type of the document to archive
2180 -- PO : For Standard/Planned
2181 -- PA : For Blanket/Contract
2182 -- RELEASE : Release
2183 --p_revision_num
2184 -- The revision of the document that needs to be archived.
2185 --Testing:
2186 -- None.
2187 --End of Comments
2188 -------------------------------------------------------------------------------
2189 PROCEDURE ARCHIVE_DISTRIBUTIONS(p_document_id IN NUMBER,
2190 p_document_type IN VARCHAR2,
2191 p_revision_num IN NUMBER)
2192 IS
2193 l_api_name CONSTANT VARCHAR2(30) := 'ARCHIVE_DISTRIBUTIONS';
2194 l_module VARCHAR2(100);
2195 l_progress VARCHAR2(3);
2196 l_revision_num NUMBER;
2197 l_continue BOOLEAN := FALSE;
2198 l_clm_flag VARCHAR2(1) := 'N';
2199
2200 -- Bug 3648552
2201 l_po_header_id PO_HEADERS_ALL.po_header_id%TYPE;
2202 l_po_release_id PO_RELEASES_ALL.po_release_id%TYPE;
2203
2204
2205 BEGIN
2206
2207 l_progress := '000';
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_PROCEDURE THEN
2210 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, l_module,
2211 'Entering ' || G_PKG_NAME || '.' || l_api_name);
2212 END IF;
2213
2214 l_progress := '010';
2215 l_clm_flag := PO_CLM_INTG_GRP.is_clm_po(p_document_id);
2216
2217 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
2218 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
2219 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
2220 'INSERT PO_DISTRIBUTIONS_ARCHIVE');
2221 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
2222 'CLM Flag : ' || l_clm_flag);
2223 END IF;
2224
2225 INSERT INTO PO_DISTRIBUTIONS_ARCHIVE_ALL
2226 (accrual_account_id,
2227 accrue_on_receipt_flag,
2228 accrued_flag,
2229 amount_billed,
2230 amount_cancelled,
2231 amount_delivered,
2232 amount_ordered,
2233 amount_to_encumber,
2234 attribute1,
2235 attribute10,
2236 attribute11,
2237 attribute12,
2238 attribute13,
2239 attribute14,
2240 attribute15,
2241 attribute2,
2242 attribute3,
2243 attribute4,
2244 attribute5,
2245 attribute6,
2246 attribute7,
2247 attribute8,
2248 attribute9,
2249 attribute_category,
2250 award_id,
2251 bom_resource_id,
2252 budget_account_id,
2253 code_combination_id,
2254 created_by,
2255 creation_date,
2256 deliver_to_location_id,
2257 deliver_to_person_id,
2258 destination_context,
2259 destination_organization_id,
2260 destination_subinventory,
2261 destination_type_code,
2262 distribution_num,
2263 distribution_type,
2264 encumbered_amount,
2265 encumbered_flag,
2266 end_item_unit_number,
2267 expenditure_item_date,
2268 expenditure_organization_id,
2269 expenditure_type,
2270 failed_funds_lookup_code,
2271 gl_cancelled_date,
2272 gl_closed_date,
2273 gl_encumbered_date,
2274 gl_encumbered_period_name,
2275 government_context,
2276 invoice_adjustment_flag,
2277 kanban_card_id,
2278 last_update_date,
2279 last_update_login,
2280 last_updated_by,
2281 line_location_id,
2282 mrc_encumbered_amount,
2283 mrc_rate,
2284 mrc_rate_date,
2285 mrc_unencumbered_amount,
2286 nonrecoverable_tax,
2287 oke_contract_deliverable_id,
2288 oke_contract_line_id,
2289 org_id,
2290 po_distribution_id,
2291 po_header_id,
2292 po_line_id,
2293 po_release_id,
2294 prevent_encumbrance_flag,
2295 program_application_id,
2296 program_id,
2297 program_update_date,
2298 project_accounting_context,
2299 project_id,
2300 quantity_billed,
2301 quantity_cancelled,
2302 quantity_delivered,
2303 quantity_ordered,
2304 rate,
2305 rate_date,
2306 recoverable_tax,
2307 recovery_rate,
2308 req_distribution_id,
2309 req_header_reference_num,
2310 req_line_reference_num,
2311 request_id,
2312 set_of_books_id,
2313 source_distribution_id,
2314 task_id,
2315 tax_recovery_override_flag,
2316 unencumbered_amount,
2317 unencumbered_quantity,
2318 variance_account_id,
2319 wip_entity_id,
2320 wip_line_id,
2321 wip_operation_seq_num,
2322 wip_repetitive_schedule_id,
2323 wip_resource_seq_num,
2324 latest_external_flag,
2325 revision_num,
2326 -- <Complex Work R12 Start>
2327 quantity_financed,
2328 amount_financed,
2329 quantity_recouped,
2330 amount_recouped,
2331 retainage_withheld_amount,
2332 retainage_released_amount
2333 -- <Complex Work R12 End>
2334 -- <CLM View Base Document Start>
2335 ,WF_ITEM_KEY
2336 ,INVOICED_VAL_IN_NTFN
2337 ,TAX_ATTRIBUTE_UPDATE_CODE
2338 --,EVENT_ID Bug # 11817705
2339 ,INTERFACE_DISTRIBUTION_REF
2340 ,LCM_FLAG
2341 ,UDA_TEMPLATE_ID
2342 ,CLM_MISC_LOA
2343 ,CLM_DEFENCE_FUNDING
2344 ,CLM_FMS_CASE_NUMBER
2345 ,CLM_AGENCY_ACCT_IDENTIFIER
2346 ,DEST_CHARGE_ACCOUNT_ID
2347 ,DEST_VARIANCE_ACCOUNT_ID
2348 ,GROUP_LINE_ID
2349 ,AMOUNT_FUNDED
2350 ,FUNDED_VALUE
2351 ,PARTIAL_FUNDED_FLAG
2352 ,QUANTITY_FUNDED
2353 ,CHANGE_IN_FUNDED_VALUE
2354 ,USSGL_TRANSACTION_CODE
2355 -- <CLM View Base Document End>
2356 ,clm_payment_sequence_num -- <Payment instruction>
2357
2358
2359 )
2360 SELECT
2361 POD.accrual_account_id,
2362 POD.accrue_on_receipt_flag,
2363 POD.accrued_flag,
2364 POD.amount_billed,
2365 POD.amount_cancelled,
2366 POD.amount_delivered,
2367 POD.amount_ordered,
2368 POD.amount_to_encumber,
2369 POD.attribute1,
2370 POD.attribute10,
2371 POD.attribute11,
2372 POD.attribute12,
2373 POD.attribute13,
2374 POD.attribute14,
2375 POD.attribute15,
2376 POD.attribute2,
2377 POD.attribute3,
2378 POD.attribute4,
2379 POD.attribute5,
2380 POD.attribute6,
2381 POD.attribute7,
2382 POD.attribute8,
2383 POD.attribute9,
2384 POD.attribute_category,
2385 POD.award_id,
2386 POD.bom_resource_id,
2387 POD.budget_account_id,
2388 POD.code_combination_id,
2389 POD.created_by,
2390 POD.creation_date,
2391 POD.deliver_to_location_id,
2392 POD.deliver_to_person_id,
2393 POD.destination_context,
2394 POD.destination_organization_id,
2395 POD.destination_subinventory,
2396 POD.destination_type_code,
2397 POD.distribution_num,
2398 POD.distribution_type,
2399 POD.encumbered_amount,
2400 POD.encumbered_flag,
2401 POD.end_item_unit_number,
2402 POD.expenditure_item_date,
2403 POD.expenditure_organization_id,
2404 POD.expenditure_type,
2405 POD.failed_funds_lookup_code,
2406 POD.gl_cancelled_date,
2407 POD.gl_closed_date,
2408 POD.gl_encumbered_date,
2409 POD.gl_encumbered_period_name,
2410 POD.government_context,
2411 POD.invoice_adjustment_flag,
2412 POD.kanban_card_id,
2413 POD.last_update_date,
2414 POD.last_update_login,
2415 POD.last_updated_by,
2416 POD.line_location_id,
2417 POD.mrc_encumbered_amount,
2418 POD.mrc_rate,
2419 POD.mrc_rate_date,
2420 POD.mrc_unencumbered_amount,
2421 POD.nonrecoverable_tax,
2422 POD.oke_contract_deliverable_id,
2423 POD.oke_contract_line_id,
2424 POD.org_id,
2425 POD.po_distribution_id,
2426 POD.po_header_id,
2427 POD.po_line_id,
2428 POD.po_release_id,
2429 POD.prevent_encumbrance_flag,
2430 POD.program_application_id,
2431 POD.program_id,
2432 POD.program_update_date,
2433 POD.project_accounting_context,
2434 POD.project_id,
2435 POD.quantity_billed,
2436 POD.quantity_cancelled,
2437 POD.quantity_delivered,
2438 POD.quantity_ordered,
2439 POD.rate,
2440 POD.rate_date,
2441 POD.recoverable_tax,
2442 POD.recovery_rate,
2443 POD.req_distribution_id,
2444 POD.req_header_reference_num,
2445 POD.req_line_reference_num,
2446 POD.request_id,
2447 POD.set_of_books_id,
2448 POD.source_distribution_id,
2449 POD.task_id,
2450 POD.tax_recovery_override_flag,
2451 POD.unencumbered_amount,
2452 POD.unencumbered_quantity,
2453 POD.variance_account_id,
2454 POD.wip_entity_id,
2455 POD.wip_line_id,
2456 POD.wip_operation_seq_num,
2457 POD.wip_repetitive_schedule_id,
2458 POD.wip_resource_seq_num,
2459 'Y',
2460 p_revision_num,
2461 -- <Complex Work R12 Start>
2462 POD.quantity_financed,
2463 POD.amount_financed,
2464 POD.quantity_recouped,
2465 POD.amount_recouped,
2466 POD.retainage_withheld_amount,
2467 POD.retainage_released_amount
2468 -- <Complex Work R12 End>
2469 -- <CLM View Base Document Start>
2470 ,POD.WF_ITEM_KEY
2471 ,POD.INVOICED_VAL_IN_NTFN
2472 ,POD.TAX_ATTRIBUTE_UPDATE_CODE
2473 -- ,POD.EVENT_ID Bug # 11817705
2474 ,POD.INTERFACE_DISTRIBUTION_REF
2475 ,POD.LCM_FLAG
2476 ,POD.UDA_TEMPLATE_ID
2477 ,POD.CLM_MISC_LOA
2478 ,POD.CLM_DEFENCE_FUNDING
2479 ,POD.CLM_FMS_CASE_NUMBER
2480 ,POD.CLM_AGENCY_ACCT_IDENTIFIER
2481 ,POD.DEST_CHARGE_ACCOUNT_ID
2482 ,POD.DEST_VARIANCE_ACCOUNT_ID
2483 ,POD.GROUP_LINE_ID
2484 ,POD.AMOUNT_FUNDED
2485 ,POD.FUNDED_VALUE
2486 ,POD.PARTIAL_FUNDED_FLAG
2487 ,POD.QUANTITY_FUNDED
2488 ,POD.CHANGE_IN_FUNDED_VALUE
2489 ,POD.USSGL_TRANSACTION_CODE
2490 -- <CLM View Base Document End>
2491 ,POD.clm_payment_sequence_num -- <Payment instruction>
2492 FROM PO_DISTRIBUTIONS_ALL POD,
2493 PO_DISTRIBUTIONS_ARCHIVE_ALL PODA
2494 WHERE ((p_document_type = 'RELEASE' AND
2495 POD.po_release_id = p_document_id) OR
2496 (p_document_type <> 'RELEASE' AND -- Bug 3210749
2497 POD.po_header_id = p_document_id AND
2498 POD.po_release_id IS NULL))
2499 AND POD.po_distribution_id = PODA.po_distribution_id (+)
2500 AND PODA.latest_external_flag (+) = 'Y'
2501 AND (--<PAR Project>:Copy all lines in case of CLM where revision num in base
2502 --and archive table do not match
2503 (l_clm_flag = 'Y' AND POD.revision_num <> PODA.revision_num) OR
2504 (PODA.po_distribution_id is NULL) OR
2505 -- <Bug 4723667 start Removing the check for amount billed in version 120.9>
2506 (POD.amount_ordered IS NULL AND PODA.amount_ordered IS NOT NULL OR
2507 POD.amount_ordered IS NOT NULL AND PODA.amount_ordered IS NULL OR
2508 POD.amount_ordered <> PODA.amount_ordered) OR
2509 -- <Bug 3862108 START>
2510 (POD.amount_cancelled IS NULL AND PODA.amount_cancelled IS NOT NULL OR
2511 POD.amount_cancelled IS NOT NULL AND PODA.amount_cancelled IS NULL OR
2512 POD.amount_cancelled <> PODA.amount_cancelled) OR
2513 -- <Bug 3862108 END>
2514 (POD.deliver_to_person_id IS NULL AND PODA.deliver_to_person_id IS NOT NULL OR
2515 POD.deliver_to_person_id IS NOT NULL AND PODA.deliver_to_person_id IS NULL OR
2516 POD.deliver_to_person_id <> PODA.deliver_to_person_id) OR
2517 (POD.distribution_num IS NULL AND PODA.distribution_num IS NOT NULL OR
2518 POD.distribution_num IS NOT NULL AND PODA.distribution_num IS NULL OR
2519 POD.distribution_num <> PODA.distribution_num) OR
2520 -- <Bug 4723667 start Removing the check for quantity billed in version 120.9>
2521 (POD.quantity_ordered IS NULL AND PODA.quantity_ordered IS NOT NULL OR
2522 POD.quantity_ordered IS NOT NULL AND PODA.quantity_ordered IS NULL OR
2523 POD.quantity_ordered <> PODA.quantity_ordered) OR
2524 -- <Complex Work R12 Start>
2525 (POD.quantity_financed IS NULL AND PODA.quantity_financed IS NOT NULL OR
2526 POD.quantity_financed IS NOT NULL AND PODA.quantity_financed IS NULL OR
2527 POD.quantity_financed <> PODA.quantity_financed) OR
2528 (POD.amount_financed IS NULL AND PODA.amount_financed IS NOT NULL OR
2529 POD.amount_financed IS NOT NULL AND PODA.amount_financed IS NULL OR
2530 POD.amount_financed <> PODA.amount_financed) OR
2531 (POD.quantity_recouped IS NULL AND PODA.quantity_recouped IS NOT NULL OR
2532 POD.quantity_recouped IS NOT NULL AND PODA.quantity_recouped IS NULL OR
2533 POD.quantity_recouped <> PODA.quantity_recouped) OR
2534 (POD.amount_recouped IS NULL AND PODA.amount_recouped IS NOT NULL OR
2535 POD.amount_recouped IS NOT NULL AND PODA.amount_recouped IS NULL OR
2536 POD.amount_recouped <> PODA.amount_recouped) OR
2537 (POD.retainage_withheld_amount IS NULL AND PODA.retainage_withheld_amount IS NOT NULL OR
2538 POD.retainage_withheld_amount IS NOT NULL AND PODA.retainage_withheld_amount IS NULL OR
2539 POD.retainage_withheld_amount <> PODA.retainage_withheld_amount) OR
2540 (POD.retainage_released_amount IS NULL AND PODA.retainage_released_amount IS NOT NULL OR
2541 POD.retainage_released_amount IS NOT NULL AND PODA.retainage_released_amount IS NULL OR
2542 POD.retainage_released_amount <> PODA.retainage_released_amount) OR
2543 -- <Complex Work R12 End>
2544 -- <Bug 3862108 START>
2545 (POD.quantity_cancelled IS NULL AND PODA.quantity_cancelled IS NOT NULL OR
2546 POD.quantity_cancelled IS NOT NULL AND PODA.quantity_cancelled IS NULL OR
2547 POD.quantity_cancelled <> PODA.quantity_cancelled) OR
2548 -- <Bug 3862108 END>
2549 -- <Bug 3191712 START>
2550 (POD.nonrecoverable_tax IS NULL AND PODA.nonrecoverable_tax IS NOT NULL OR
2551 POD.nonrecoverable_tax IS NOT NULL AND PODA.nonrecoverable_tax IS NULL OR
2552 POD.nonrecoverable_tax <> PODA.nonrecoverable_tax) OR
2553 (POD.recoverable_tax IS NULL AND PODA.recoverable_tax IS NOT NULL OR
2554 POD.recoverable_tax IS NOT NULL AND PODA.recoverable_tax IS NULL OR
2555 POD.recoverable_tax <> PODA.recoverable_tax) OR
2556 -- <Bug 3191712 END>
2557 (POD.recovery_rate IS NULL AND PODA.recovery_rate IS NOT NULL OR --BUG7286203
2558 POD.recovery_rate IS NOT NULL AND PODA.recovery_rate IS NULL OR
2559 POD.recovery_rate <> PODA.recovery_rate)
2560 );
2561
2562 l_continue := (SQL%ROWCOUNT > 0);
2563
2564 IF l_continue THEN
2565 l_progress := '020';
2566 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
2567 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
2568 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
2569 'Update PO_DISTRIBUTIONS_ARCHIVE to reset latest_external_flag');
2570 END IF;
2571
2572 -- Bug 3648552 START
2573 -- Get PO_HEADER_ID for Release since there is no index on
2574 -- PO_DISTRIBUTIONS_ARCHIVE_ALL.po_release_id
2575 IF (p_document_type = 'RELEASE') THEN
2576 SELECT po_header_id
2577 INTO l_po_header_id
2578 FROM po_releases_all
2579 WHERE po_release_id = p_document_id;
2580 l_po_release_id := p_document_id;
2581 ELSE
2582 l_po_header_id := p_document_id;
2583 l_po_release_id := NULL;
2584 END IF;
2585 -- Bug 3648552 END
2586
2587 -- If a row was inserted into PO_DISTRIBUTIONS_ARCHIVE, then set the appropriate flags
2588 UPDATE PO_DISTRIBUTIONS_ARCHIVE_ALL POD1
2589 SET latest_external_flag = 'N'
2590 -- Bug 3648552 START
2591 WHERE po_header_id = l_po_header_id
2592 -- Bug 3713788: fixed regression caused by bug 3648552
2593 AND NVL(po_release_id, -99) = NVL(l_po_release_id, -99)
2594 -- Bug 3648552 END
2595 AND latest_external_flag = 'Y'
2596 AND revision_num < p_revision_num
2597 AND EXISTS
2598 (SELECT 'A new archived row'
2599 FROM PO_DISTRIBUTIONS_ARCHIVE_ALL POD2
2600 WHERE POD2.po_distribution_id = POD1.po_distribution_id
2601 AND POD2.latest_external_flag = 'Y'
2602 AND POD2.revision_num = p_revision_num);
2603 ELSE
2604 l_progress := '030';
2605 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
2606 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
2607 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
2608 'No need to reset latest_external_flag');
2609 END IF;
2610 END IF; /* IF l_continue */
2611
2612 l_progress := '030';
2613
2614 EXCEPTION
2615 WHEN OTHERS THEN
2616 po_message_s.sql_error('Exception of ARCHIVE_DISTRIBUTIONS()',
2617 l_progress , sqlcode);
2618 FND_MSG_PUB.Add;
2619 IF (G_FND_DEBUG = 'Y') THEN
2620 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
2621 FND_LOG.string(FND_LOG.level_unexpected, l_module,
2622 'EXCEPTION: '||sqlerrm);
2623 END IF;
2624 END IF;
2625 RAISE;
2626 END ARCHIVE_DISTRIBUTIONS;
2627
2628 -------------------------------------------------------------------------------
2629 --Start of Comments
2630 --Name: ARCHIVE_ORG_ASSIGNMENTS
2631 --Pre-reqs:
2632 -- None.
2633 --Modifies:
2634 -- PO_GA_ORG_ASSIGNMENTS_ARCHIVE
2635 --Locks:
2636 -- None.
2637 --Function:
2638 -- Arcives the global agreement org assignments.
2639 --Parameters:
2640 --IN:
2641 --p_document_id
2642 -- The id of the document that needs to be archived.
2643 --p_revision_num
2644 -- The revision of the document that needs to be archived.
2645 --Testing:
2646 -- None.
2647 --End of Comments
2648 -------------------------------------------------------------------------------
2649 PROCEDURE ARCHIVE_ORG_ASSIGNMENTS(p_document_id IN NUMBER,
2650 p_revision_num IN NUMBER)
2651 IS
2652 l_api_name CONSTANT VARCHAR2(30) := 'ARCHIVE_ORG_ASSIGNMENTS';
2653 l_module VARCHAR2(100);
2654 l_progress VARCHAR2(3);
2655 l_revision_num NUMBER;
2656 l_continue BOOLEAN := FALSE;
2657
2658 BEGIN
2659
2660 l_progress := '000';
2661 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
2662 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_PROCEDURE THEN
2663 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, l_module,
2664 'Entering ' || G_PKG_NAME || '.' || l_api_name);
2665 END IF;
2666
2667 l_progress := '010';
2668 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
2669 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
2670 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
2671 'INSERT PO_GA_ORG_ASSIGNMENTS_ARCHIVE');
2672 END IF;
2673
2674 INSERT INTO PO_GA_ORG_ASSIGNMENTS_ARCHIVE
2675 (org_assignment_id , --<HTML Agreement R12>
2676 created_by ,
2677 creation_date ,
2678 enabled_flag ,
2679 last_updated_by ,
2680 last_update_date ,
2681 last_update_login ,
2682 organization_id ,
2683 po_header_id ,
2684 purchasing_org_id ,
2685 vendor_site_id ,
2686 latest_external_flag ,
2687 revision_num )
2688 SELECT
2689 POG.org_assignment_id , --<HTML Agreement R12>
2690 POG.created_by ,
2691 POG.creation_date ,
2692 POG.enabled_flag ,
2693 POG.last_updated_by ,
2694 POG.last_update_date ,
2695 POG.last_update_login ,
2696 POG.organization_id ,
2697 POG.po_header_id ,
2698 POG.purchasing_org_id ,
2699 POG.vendor_site_id ,
2700 'Y' ,
2701 p_revision_num
2702 FROM PO_GA_ORG_ASSIGNMENTS POG,
2703 PO_GA_ORG_ASSIGNMENTS_ARCHIVE POGA
2704 WHERE POG.po_header_id = p_document_id
2705 --AND POG.po_header_id = POGA.po_header_id (+)
2706 --AND POG.organization_id = POGA.organization_id (+)
2707 AND POG.org_assignment_id = POGA.org_assignment_id (+) --<HTML Agreement R12>
2708 AND POGA.latest_external_flag (+) = 'Y'
2709 AND ((POGA.po_header_id is NULL) OR
2710 (POG.enabled_flag IS NULL AND POGA.enabled_flag IS NOT NULL OR
2711 POG.enabled_flag IS NOT NULL AND POGA.enabled_flag IS NULL OR
2712 POG.enabled_flag <> POGA.enabled_flag) OR
2713 (POG.purchasing_org_id IS NULL AND POGA.purchasing_org_id IS NOT NULL OR
2714 POG.purchasing_org_id IS NOT NULL AND POGA.purchasing_org_id IS NULL OR
2715 POG.purchasing_org_id <> POGA.purchasing_org_id) OR
2716 (POG.vendor_site_id IS NULL AND POGA.vendor_site_id IS NOT NULL OR
2717 POG.vendor_site_id IS NOT NULL AND POGA.vendor_site_id IS NULL OR
2718 POG.vendor_site_id <> POGA.vendor_site_id));
2719
2720 l_continue := (SQL%ROWCOUNT > 0);
2721
2722 IF l_continue THEN
2723 l_progress := '020';
2724 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
2725 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
2726 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
2727 'Update PO_GA_ORG_ASSIGNMENTS_ARCHIVE to reset latest_external_flag');
2728 END IF;
2729
2730 -- If a row was inserted into PO_GA_ORG_ASSIGNMENTS_ARCHIVE, then set the appropriate flags
2731 UPDATE PO_GA_ORG_ASSIGNMENTS_ARCHIVE POG1
2732 SET latest_external_flag = 'N'
2733 WHERE po_header_id = p_document_id
2734 AND latest_external_flag = 'Y'
2735 AND revision_num < p_revision_num
2736 AND EXISTS
2737 (SELECT 'A new archived row'
2738 FROM PO_GA_ORG_ASSIGNMENTS_ARCHIVE POG2
2739 WHERE POG2.org_assignment_id = POG1.org_assignment_id --<HTML Agreement R12>
2740 -- POG2.po_header_id = POG1.po_header_id
2741 --AND POG2.organization_id = POG1.organization_id
2742 AND POG2.latest_external_flag = 'Y'
2743 AND POG2.revision_num = p_revision_num);
2744 ELSE
2745 l_progress := '030';
2746 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
2747 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
2748 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
2749 'No need to reset latest_external_flag');
2750 END IF;
2751 END IF; /* IF l_continue */
2752
2753 l_progress := '030';
2754
2755 EXCEPTION
2756 WHEN OTHERS THEN
2757 po_message_s.sql_error('Exception of ARCHIVE_ORG_ASSIGNMENTS()',
2758 l_progress , sqlcode);
2759 FND_MSG_PUB.Add;
2760 IF (G_FND_DEBUG = 'Y') THEN
2761 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
2762 FND_LOG.string(FND_LOG.level_unexpected, l_module,
2763 'EXCEPTION: '||sqlerrm);
2764 END IF;
2765 END IF;
2766 RAISE;
2767 END ARCHIVE_ORG_ASSIGNMENTS;
2768
2769 -------------------------------------------------------------------------------
2770 --Start of Comments
2771 --Name: ARCHIVE_PRICE_DIFFS
2772 --Pre-reqs:
2773 -- None.
2774 --Modifies:
2775 -- PO_PRICE_DIFFERENTIALS_ARCHIVE
2776 --Locks:
2777 -- None.
2778 --Function:
2779 -- Arcives the price differentials.
2780 --Parameters:
2781 --IN:
2782 --p_document_id
2783 -- The id of the document that needs to be archived.
2784 --p_entity_type
2785 -- The entity type of the document that needs to be archived.
2786 --p_revision_num
2787 -- The revision of the document that needs to be archived.
2788 --Testing:
2789 -- None.
2790 --End of Comments
2791 -------------------------------------------------------------------------------
2792 PROCEDURE ARCHIVE_PRICE_DIFFS(p_document_id IN NUMBER,
2793 p_entity_type IN VARCHAR,
2794 p_revision_num IN NUMBER)
2795 IS
2796 l_api_name CONSTANT VARCHAR2(30) := 'ARCHIVE_PRICE_DIFFS';
2797 l_module VARCHAR2(100);
2798 l_progress VARCHAR2(3);
2799 l_continue BOOLEAN := FALSE;
2800
2801 BEGIN
2802
2803 l_progress := '000';
2804 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
2805 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_PROCEDURE THEN
2806 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, l_module,
2807 'Entering ' || G_PKG_NAME || '.' || l_api_name);
2808 END IF;
2809
2810 l_progress := '010';
2811 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
2812 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
2813 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
2814 'INSERT PO_PRICE_DIFFERENTIALS_ARCHIVE');
2815 END IF;
2816
2817 INSERT INTO PO_PRICE_DIFFERENTIALS_ARCHIVE
2818 (created_by ,
2819 creation_date ,
2820 enabled_flag ,
2821 entity_id ,
2822 entity_type ,
2823 last_update_date ,
2824 last_update_login ,
2825 last_updated_by ,
2826 max_multiplier ,
2827 min_multiplier ,
2828 multiplier ,
2829 price_differential_id ,
2830 price_differential_num ,
2831 price_type ,
2832 latest_external_flag ,
2833 revision_num )
2834 SELECT
2835 POR.created_by ,
2836 POR.creation_date ,
2837 POR.enabled_flag ,
2838 POR.entity_id ,
2839 POR.entity_type ,
2840 POR.last_update_date ,
2841 POR.last_update_login ,
2842 POR.last_updated_by ,
2843 POR.max_multiplier ,
2844 POR.min_multiplier ,
2845 POR.multiplier ,
2846 POR.price_differential_id ,
2847 POR.price_differential_num ,
2848 POR.price_type ,
2849 'Y' ,
2850 p_revision_num
2851 FROM PO_PRICE_DIFFERENTIALS POR,
2852 PO_PRICE_DIFFERENTIALS_ARCHIVE PORA
2853 WHERE ((p_entity_type = 'PRICE BREAK' AND
2854 POR.entity_id IN (SELECT line_location_id
2855 FROM PO_LINE_LOCATIONS_ALL
2856 WHERE po_header_id = p_document_id)) OR
2857 (p_entity_type IN ('PO LINE', 'BLANKET LINE') AND
2858 POR.entity_id IN (SELECT po_line_id
2859 FROM PO_LINES_ALL
2860 WHERE po_header_id = p_document_id)))
2861 AND POR.entity_type = p_entity_type
2862 AND POR.price_differential_id = PORA.price_differential_id (+)
2863 AND PORA.latest_external_flag (+) = 'Y'
2864 AND ((PORA.price_differential_id is NULL) OR
2865 (POR.enabled_flag IS NULL AND PORA.enabled_flag IS NOT NULL OR
2866 POR.enabled_flag IS NOT NULL AND PORA.enabled_flag IS NULL OR
2867 POR.enabled_flag <> PORA.enabled_flag) OR
2868 (POR.price_type IS NULL AND PORA.price_type IS NOT NULL OR
2869 POR.price_type IS NOT NULL AND PORA.price_type IS NULL OR
2870 POR.price_type <> PORA.price_type) OR
2871 (POR.min_multiplier IS NULL AND PORA.min_multiplier IS NOT NULL OR
2872 POR.min_multiplier IS NOT NULL AND PORA.min_multiplier IS NULL OR
2873 POR.min_multiplier <> PORA.min_multiplier) OR
2874 (POR.max_multiplier IS NULL AND PORA.max_multiplier IS NOT NULL OR
2875 POR.max_multiplier IS NOT NULL AND PORA.max_multiplier IS NULL OR
2876 POR.max_multiplier <> PORA.max_multiplier) OR
2877 (POR.multiplier IS NULL AND PORA.multiplier IS NOT NULL OR
2878 POR.multiplier IS NOT NULL AND PORA.multiplier IS NULL OR
2879 POR.multiplier <> PORA.multiplier) OR
2880 (POR.price_differential_num IS NULL AND PORA.price_differential_num IS NOT NULL OR --BUG7286203
2881 POR.price_differential_num IS NOT NULL AND PORA.price_differential_num IS NULL OR
2882 POR.price_differential_num <> PORA.price_differential_num));
2883
2884 l_continue := (SQL%ROWCOUNT > 0);
2885
2886 IF l_continue THEN
2887 l_progress := '020';
2888 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
2889 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
2890 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
2891 'Update PO_PRICE_DIFFERENTIALS_ARCHIVE to reset latest_external_flag');
2892 END IF;
2893
2894 -- If a row was inserted into PO_PRICE_DIFFERENTIALS_ARCHIVE, then set the appropriate flags
2895 UPDATE PO_PRICE_DIFFERENTIALS_ARCHIVE POR1
2896 SET latest_external_flag = 'N'
2897 WHERE ((p_entity_type = 'PRICE BREAK' AND
2898 entity_id IN (SELECT line_location_id
2899 FROM PO_LINE_LOCATIONS_ALL
2900 WHERE po_header_id = p_document_id)) OR
2901 (p_entity_type IN ('PO LINE', 'BLANKET LINE') AND
2902 entity_id IN (SELECT po_line_id
2903 FROM PO_LINES_ALL
2904 WHERE po_header_id = p_document_id)))
2905 AND entity_type = p_entity_type
2906 AND latest_external_flag = 'Y'
2907 AND revision_num < p_revision_num
2908 AND EXISTS
2909 (SELECT 'A new archived row'
2910 FROM PO_PRICE_DIFFERENTIALS_ARCHIVE POR2
2911 WHERE POR2.price_differential_id = POR1.price_differential_id
2912 AND POR2.latest_external_flag = 'Y'
2913 AND POR2.revision_num = p_revision_num);
2914 ELSE
2915 l_progress := '030';
2916 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
2917 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
2918 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
2919 'No need to reset latest_external_flag');
2920 END IF;
2921 END IF; /* IF l_continue */
2922
2923 l_progress := '030';
2924
2925 EXCEPTION
2926 WHEN OTHERS THEN
2927 po_message_s.sql_error('Exception of ARCHIVE_PRICE_DIFFS()',
2928 l_progress , sqlcode);
2929 FND_MSG_PUB.Add;
2930 IF (G_FND_DEBUG = 'Y') THEN
2931 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
2932 FND_LOG.string(FND_LOG.level_unexpected, l_module,
2933 'EXCEPTION: '||sqlerrm);
2934 END IF;
2935 END IF;
2936 RAISE;
2937 END ARCHIVE_PRICE_DIFFS;
2938
2939
2940 -------------------------------------------------------------------------------
2941 --Start of Comments
2942 --Name: ARCHIVE_CONTRACT_TERMS
2943 --Pre-reqs:
2944 -- None.
2945 --Modifies:
2946 -- OKC Tables
2947 --Locks:
2948 -- None.
2949 --Function:
2950 -- Call OKC procedure to arcive the contract terms
2951 --Parameters:
2952 --IN:
2953 --p_document_id
2954 -- The id of the document that needs to be archived.
2955 --p_document_type
2956 -- The entity type of the document that needs to be archived.
2957 --p_document_subtype
2958 -- The entity subtype of the document that needs to be archived.
2959 --p_revision_num
2960 -- The revision of the document that needs to be archived.
2961 --Testing:
2962 -- None.
2963 --End of Comments
2964 -------------------------------------------------------------------------------
2965 PROCEDURE ARCHIVE_CONTRACT_TERMS(p_document_id IN NUMBER,
2966 p_document_type IN VARCHAR,
2967 p_document_subtype IN VARCHAR,
2968 p_revision_num IN NUMBER)
2969 IS
2970 l_api_name CONSTANT VARCHAR2(30) := 'ARCHIVE_CONTRACT_TERMS';
2971 l_module VARCHAR2(100);
2972 l_progress VARCHAR2(3);
2973 l_return_status VARCHAR2(1);
2974 l_msg_count NUMBER;
2975 l_msg_data VARCHAR2(2000);
2976 l_conterms_exist_flag PO_HEADERS_ALL.conterms_exist_flag%TYPE;
2977 l_pending_signature_flag PO_HEADERS_ALL.pending_signature_flag%TYPE;
2978 l_clear_amendment VARCHAR2(1);
2979
2980
2981 BEGIN
2982
2983 l_progress := '000';
2984 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
2985 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_PROCEDURE THEN
2986 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, l_module,
2987 'Entering ' || G_PKG_NAME || '.' || l_api_name);
2988 END IF;
2989
2990 l_progress := '010';
2991 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
2992 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
2993 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
2994 'Select conterms_exist_flag - po_header_id: '||p_document_id);
2995 END IF;
2996 -- SQL What:Find out if contract terms exist
2997 -- SQL Why :Archive Contract Terms if needed
2998 SELECT NVL(conterms_exist_flag, 'N'),
2999 NVL(pending_signature_flag, 'N')
3000 INTO l_conterms_exist_flag,
3001 l_pending_signature_flag
3002 FROM po_headers_all
3003 WHERE po_header_id = p_document_id;
3004
3005 IF (l_conterms_exist_flag = 'Y') THEN
3006 l_progress := '020';
3007 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
3008 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
3009 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
3010 'OKC_TERMS_VERSION_GRP.version_doc() p_document_id: '||
3011 p_document_id||', p_revision_num: '||p_revision_num);
3012 END IF;
3013
3014 -- Bug 3616320 START
3015 -- Always call the OKC_TERMS_VERSION_GRP.VERSION_DOC with
3016 -- p_clear_amendments = 'N' to version the document
3017 -- IF l_pending_signature_flag <> 'Y' THEN
3018 -- l_clear_amendment := 'Y';
3019 -- ELSE
3020 -- l_clear_amendment := 'N';
3021 -- END IF; /*IF l_pending_signature_flag <> 'Y'*/
3022 l_clear_amendment := 'N';
3023 -- Bug 3616320 END
3024
3025 OKC_TERMS_VERSION_GRP.version_doc(p_api_version => 1.0,
3026 p_doc_id => p_document_id,
3027 p_doc_type => p_document_type||'_'||
3028 p_document_subtype,
3029 p_version_number => p_revision_num,
3030 p_clear_amendment => l_clear_amendment,
3031 x_return_status => l_return_status,
3032 x_msg_data => l_msg_data,
3033 x_msg_count => l_msg_count,
3034 p_include_gen_attach => 'N' );
3035
3036 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) Then
3037 IF (l_return_status = FND_API.G_RET_STS_ERROR) Then
3038 RAISE FND_API.G_EXC_ERROR;
3039 ELSE
3040 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3041 END IF; /* IF (l_return_status = FND_API.G_RET_STS_ERROR) */
3042 END IF; /* IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) */
3043
3044 IF (l_pending_signature_flag <> 'Y') THEN
3045
3046 l_progress := '030';
3047 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
3048 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
3049 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
3050 'PO_CONTERMS_WF_PVT.update_contract_terms() p_po_header_id: '||
3051 p_document_id);
3052 END IF;
3053 -- Activate Contract Terms Deliverables now that PO revision is archived
3054 --
3055 -- Inform Contracts to activate deliverable, now that PO is successfully
3056 -- archived
3057 PO_CONTERMS_WF_PVT.UPDATE_CONTRACT_TERMS(
3058 p_po_header_id => p_document_id,
3059 p_signed_date => SYSDATE,
3060 x_return_status => l_return_status,
3061 x_msg_data => l_msg_data,
3062 x_msg_count => l_msg_count);
3063
3064 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) Then
3065 IF (l_return_status = FND_API.G_RET_STS_ERROR) Then
3066 RAISE FND_API.G_EXC_ERROR;
3067 ELSE
3068 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3069 END IF; /* IF (l_return_status = FND_API.G_RET_STS_ERROR) */
3070 END IF; /* IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) */
3071
3072 END IF; /*IF (l_pending_signature_flag <> 'Y')*/
3073
3074 END IF; /*IF (l_conterms_exist_flag = 'Y')*/
3075
3076 l_progress := '050';
3077
3078 EXCEPTION
3079 WHEN OTHERS THEN
3080 po_message_s.sql_error('Exception of ARCHIVE_CONTRACT_TERMS()',
3081 l_progress , sqlcode);
3082 FND_MSG_PUB.Add;
3083 IF (G_FND_DEBUG = 'Y') THEN
3084 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
3085 FND_LOG.string(FND_LOG.level_unexpected, l_module,
3086 'EXCEPTION: '||sqlerrm);
3087 END IF;
3088 END IF;
3089 RAISE;
3090 END ARCHIVE_CONTRACT_TERMS;
3091
3092
3093 -------------------------------------------------------------------------------
3094 --Start of Comments
3095 --Name: archive_po
3096 --Pre-reqs:
3097 -- None.
3098 --Modifies:
3099 -- None.
3100 --Locks:
3101 -- None.
3102 --Function:
3103 -- Arcives the document. Inserts an copy of the document in the
3104 -- archive tables.
3105 --Parameters:
3106 --IN:
3107 --p_api_version
3108 -- Version number of API that caller expects. It should match the
3109 -- l_api_version defined in the procedure (expected value : 1.0)
3110 --p_document_id
3111 -- The id of the document that needs to be archived.
3112 --p_document_type
3113 -- The type of the document to archive
3114 -- PO : For Standard/Planned
3115 -- PA : For Blanket/Contract
3116 -- RELEASE : Release
3117 --p_document_subtype
3118 -- The subtype of the document.
3119 -- Valid Document types and Document subtypes are
3120 -- Document Type Document Subtype
3121 -- RELEASE ---> SCHEDULED/BLANKET
3122 -- PO ---> PLANNED/STANDARD
3123 -- PA ---> CONTRACT/BLANKET
3124 --OUT:
3125 --x_return_status
3126 -- FND_API.G_RET_STS_SUCCESS if API succeeds
3127 -- FND_API.G_RET_STS_ERROR if API fails
3128 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
3129 --x_msg_count
3130 -- returns count of messages in the stack.
3131 --x_msg_data
3132 -- Contains error msg in case x_return_status returned
3133 -- FND_API.G_RET_STS_ERROR or FND_API.G_RET_STS_UNEXP_ERROR
3134 --Testing:
3135 -- None.
3136 --End of Comments
3137 -------------------------------------------------------------------------------
3138 /*
3139 * Set a savepoint;
3140 *
3141 * Check if the current revision is already archived.
3142 * Case entity.document_type is
3143 * When PO
3144 * Case entity.document_subtype is
3145 * When STANDARD or PLANNED
3146 * archive PO_HEADERS
3147 * when modified archive PO_LINES, PO_LINE_LOCATIONS,
3148 * PO_PRICE_DIFFERENTIALS and PO_DISTRIBUTIONS.
3149 * End Case
3150 * When PA
3151 * Case entity.document_subtype is
3152 * When BLANKET
3153 * archive PO_HEADERS
3154 * when modified archive PO_LINES, PO_LINE_LOCATIONS, PO_PRICE_DIFFERENTIALS
3155 * (for price breaks)
3156 * When CONTRACT
3157 * archive PO_HEADERS
3158 * End Case
3159 * If global_agreement_flag = Y (i.e. global blanket or global contract) --<BUG 3290647>
3160 * When modified, archive PO_GA_ORG_ASSIGNMENT
3161 * When RELEASE
3162 * archive PO_RELEASES
3163 * when modified archive PO_LINE_LOCATIONS and PO_DISTRIBUTIONS.
3164 * End Case
3165 *
3166 * IF error happens, rollback to the savepoint;
3167 *
3168 */
3169
3170 PROCEDURE archive_po(p_api_version IN NUMBER,
3171 p_document_id IN NUMBER,
3172 p_document_type IN VARCHAR2,
3173 p_document_subtype IN VARCHAR2,
3174 x_return_status OUT NOCOPY VARCHAR2,
3175 x_msg_count OUT NOCOPY NUMBER,
3176 x_msg_data OUT NOCOPY VARCHAR2)
3177 IS
3178 l_api_name CONSTANT VARCHAR2(30) := 'ARCHIVE_PO';
3179 l_api_version CONSTANT NUMBER := 1.0;
3180 l_module VARCHAR2(100);
3181 l_progress VARCHAR2(3);
3182 l_revision_num NUMBER;
3183 l_return_status VARCHAR2(1);
3184 l_ga_flag PO_HEADERS_ALL.global_agreement_flag%TYPE;
3185 l_draft_id NUMBER := 0;
3186 l_clm_flag VARCHAR2(1);
3187
3188 BEGIN
3189
3190 -- Standard call to check for call compatibility
3191 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME)
3192 THEN
3193 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3194 END IF;
3195
3196 l_progress := '000';
3197 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
3198 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_PROCEDURE THEN
3199 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, l_module,
3200 'Entering ' || G_PKG_NAME || '.' || l_api_name);
3201 END IF;
3202
3203 l_progress := '010';
3204 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
3205 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
3206 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
3207 'Set SavePoint');
3208 END IF;
3209
3210 --Standard Start API savepoint
3211 SAVEPOINT PO_ARCHIVE_SP;
3212
3213 l_progress := '020';
3214 IF (p_document_type = 'PO') THEN
3215 l_progress := '030';
3216 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
3217 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
3218 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
3219 'check_po_archive(PO) p_document_id: '||p_document_id);
3220 END IF;
3221 check_po_archive(p_document_id, l_revision_num, l_return_status);
3222
3223 IF (l_return_status = 'Y') THEN
3224 l_progress := '040';
3225 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
3226 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
3227 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
3228 'archive_header() p_document_id: '||p_document_id);
3229 END IF;
3230
3231 --<PAR Project Starts> : Moving call to archive_uda at the begining.
3232 l_progress := '045';
3233 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
3234 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
3235 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
3236 'archive_uda_attribs() p_document_id: '||p_document_id||
3237 ', p_document_type: '||p_document_type||
3238 ', p_document_subtype: '||p_document_subtype||
3239 ', p_revision_num: '||l_revision_num);
3240 END IF;
3241
3242 archive_uda_attribs(p_document_id, p_document_type,
3243 p_document_subtype, l_revision_num);
3244 --<PAR Project Ends>
3245 archive_header(p_document_id);
3246
3247 l_progress := '050';
3248 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
3249 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
3250 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
3251 'archive_lines() p_document_id: '||p_document_id||
3252 ', l_revision_num: '||l_revision_num);
3253 END IF;
3254 archive_lines(p_document_id, l_revision_num);
3255
3256 -- CLM Phase 4 - Elins project
3257 l_progress := '051';
3258 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
3259 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
3260 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
3261 'archive_exhibits() p_document_id: '||p_document_id||
3262 ', l_revision_num: '||l_revision_num);
3263 END IF;
3264 archive_exhibits(p_document_id, l_revision_num);
3265
3266 --<Enhanced Pricing Start: Archive Price Adjustments>
3267 l_progress := '52';
3268 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
3269 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
3270 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
3271 'archive_price_adjustments() p_document_id: '||p_document_id||
3272 ', l_revision_num: '||l_revision_num);
3273 END IF;
3274 archive_price_adjustments
3275 (
3276 p_po_header_id => p_document_id
3277 , p_revision_num => l_revision_num
3278 );
3279
3280 l_progress := '56';
3281 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
3282 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
3283 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
3284 'archive_price_adj_attribs() p_document_id: '||p_document_id||
3285 ', l_revision_num: '||l_revision_num);
3286 END IF;
3287 archive_price_adj_attribs
3288 (
3289 p_po_header_id => p_document_id
3290 , p_revision_num => l_revision_num
3291 );
3292 --<Enhanced Pricing End>
3293
3294 l_progress := '060';
3295 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
3296 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
3297 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
3298 'archive_price_diffs() p_document_id: '||p_document_id||
3299 ', p_entity_type: PO LINE'||
3300 ', l_revision_num: '||l_revision_num);
3301 END IF;
3302 archive_price_diffs(p_document_id, 'PO LINE', l_revision_num);
3303
3304 l_progress := '070';
3305 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
3306 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
3307 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
3308 'archive_line_locations() p_document_id: '||p_document_id||
3309 ', p_document_type: '||p_document_type||
3310 ', l_revision_num: '||l_revision_num);
3311 END IF;
3312 archive_line_locations(p_document_id, p_document_type, l_revision_num);
3313
3314 l_progress := '080';
3315 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
3316 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
3317 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
3318 'archive_distributions() p_document_id: '||p_document_id||
3319 ', p_document_type: '||p_document_type||
3320 ', l_revision_num: '||l_revision_num);
3321 END IF;
3322 archive_distributions(p_document_id, p_document_type, l_revision_num);
3323
3324 l_progress := '090';
3325 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
3326 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
3327 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
3328 'archive_contract_terms() p_document_id: '||p_document_id||
3329 ', p_document_type: '||p_document_type||
3330 ', p_document_subtype: '||p_document_subtype||
3331 ', p_revision_num: '||l_revision_num);
3332 END IF;
3333 archive_contract_terms(p_document_id, p_document_type,
3334 p_document_subtype, l_revision_num);
3335
3336 --<Start CLM View Base Document>
3337 -- Archive UDA Data/Attachments for UDA/CLM Enabled Documents, and for Revision 0 only
3338 IF l_revision_num = 0 THEN
3339 l_progress := '095';
3340 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
3341 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
3342 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
3343 'archive_uda_attribs() p_document_id: '||p_document_id||
3344 ', p_document_type: '||p_document_type||
3345 ', p_document_subtype: '||p_document_subtype||
3346 ', p_revision_num: '||l_revision_num);
3347 END IF;
3348 --<PAR Project> : Shifted the call to archive_uda in the begining.
3349 l_clm_flag := PO_CLM_INTG_GRP.is_clm_po(p_document_id);
3350
3351 IF l_clm_flag = 'Y' THEN
3352
3353 l_progress := '097';
3354 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
3355 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
3356 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
3357 'archive_attachments() p_document_id: '||p_document_id||
3358 ', p_document_type: '||p_document_type||
3359 ', p_document_subtype: '||p_document_subtype||
3360 ', p_revision_num: '||l_revision_num);
3361 END IF;
3362
3363 archive_attachments(p_document_id, p_document_type,
3364 p_document_subtype, l_revision_num, l_draft_id);
3365
3366 l_progress := '099';
3367 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
3368 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
3369 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
3370 'sync_contract_terms_to_draft() p_document_id: '||p_document_id||
3371 ', p_document_type: '||p_document_type||
3372 ', p_document_subtype: '||p_document_subtype||
3373 ', p_revision_num: '||l_revision_num);
3374 END IF;
3375
3376
3377 sync_contract_terms_to_draft(p_document_id, p_document_type,
3378 p_document_subtype, l_revision_num);
3379
3380 END IF; --IF l_clm_flag = 'Y'
3381 END IF; --IF l_revision_num = 0
3382 --<End CLM View Base Document>
3383
3384 END IF; /*IF (l_return_status = 'Y')*/
3385 ELSIF (p_document_type = 'PA') THEN
3386 l_progress := '100';
3387 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
3388 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
3389 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
3390 'check_po_archive(PA) p_document_id: '||p_document_id);
3391 END IF;
3392 check_po_archive(p_document_id, l_revision_num, l_return_status);
3393
3394 IF (l_return_status = 'Y') THEN
3395 l_progress := '110';
3396 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
3397 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
3398 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
3399 'archive_header() p_document_id: '||p_document_id);
3400 END IF;
3401
3402 --<PAR Project>: Shifint the call to archive_uda to begining.
3403 l_progress := '115';
3404 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
3405 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
3406 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
3407 'archive_uda_attribs() p_document_id: '||p_document_id||
3408 ', p_document_type: '||p_document_type||
3409 ', p_document_subtype: '||p_document_subtype||
3410 ', p_revision_num: '||l_revision_num);
3411 END IF;
3412
3413 archive_uda_attribs(p_document_id, p_document_type,
3414 p_document_subtype, l_revision_num);
3415
3416 archive_header(p_document_id);
3417
3418 IF (p_document_subtype = 'BLANKET') THEN
3419 l_progress := '120';
3420 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
3421 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
3422 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
3423 'archive_lines() p_document_id: '||p_document_id||
3424 ', l_revision_num: '||l_revision_num);
3425 END IF;
3426 archive_lines(p_document_id, l_revision_num);
3427
3428 -- CLM Phase 4 - Elins project
3429 l_progress := '121';
3430 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
3431 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
3432 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
3433 'archive_exhibits() p_document_id: '||p_document_id||
3434 ', l_revision_num: '||l_revision_num);
3435 END IF;
3436 archive_exhibits(p_document_id, l_revision_num);
3437
3438 --<Enhanced Pricing Start: Archive Price Adjustments>
3439 l_progress := '122';
3440 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
3441 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
3442 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
3443 'archive_price_adjustments() p_document_id: '||p_document_id||
3444 ', l_revision_num: '||l_revision_num);
3445 END IF;
3446 archive_price_adjustments
3447 (
3448 p_po_header_id => p_document_id
3449 , p_revision_num => l_revision_num
3450 );
3451
3452 l_progress := '126';
3453 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
3454 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
3455 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
3456 'archive_price_adj_attribs() p_document_id: '||p_document_id||
3457 ', l_revision_num: '||l_revision_num);
3458 END IF;
3459 archive_price_adj_attribs
3460 (
3461 p_po_header_id => p_document_id
3462 , p_revision_num => l_revision_num
3463 );
3464 --<Enhanced Pricing End>
3465
3466 --<Unified catalog R12: Start>
3467 -- Archive the Attribute Values and TLP rows (for BPA/GBPA only)
3468 archive_attribute_values
3469 (
3470 p_po_header_id => p_document_id
3471 , p_revision_num => l_revision_num
3472 );
3473
3474 archive_attr_values_tlp
3475 (
3476 p_po_header_id => p_document_id
3477 , p_revision_num => l_revision_num
3478 );
3479 --<Unified catalog R12: End>
3480
3481 l_progress := '130';
3482 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
3483 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
3484 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
3485 'archive_price_diffs() p_document_id: '||p_document_id||
3486 ', p_entity_type: BLANKET LINE'||
3487 ', l_revision_num: '||l_revision_num);
3488 END IF;
3489 archive_price_diffs(p_document_id, 'BLANKET LINE', l_revision_num);
3490
3491
3492 l_progress := '140';
3493 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
3494 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
3495 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
3496 'archive_line_locations() p_document_id: '||p_document_id||
3497 ', p_document_type: '||p_document_type||
3498 ', l_revision_num: '||l_revision_num);
3499 END IF;
3500 archive_line_locations(p_document_id, p_document_type, l_revision_num);
3501
3502 l_progress := '150';
3503 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
3504 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
3505 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
3506 'archive_price_diffs() p_document_id: '||p_document_id||
3507 ', p_entity_type: BLANKET LINE'||
3508 ', l_revision_num: '||l_revision_num);
3509 END IF;
3510 archive_price_diffs(p_document_id, 'PRICE BREAK', l_revision_num);
3511
3512 -- Bug 3215784 START
3513 -- Since Encumbrance code will create distribution record for BPA,
3514 -- archive_distribution routine should be called
3515 l_progress := '155';
3516 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
3517 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
3518 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
3519 'archive_distributions() p_document_id: '||p_document_id||
3520 ', p_document_type: '||p_document_type||
3521 ', l_revision_num: '||l_revision_num);
3522 END IF;
3523 archive_distributions(p_document_id, p_document_type, l_revision_num);
3524 -- Bug 3215784 END
3525
3526 END IF; /*IF (p_document_subtype = 'BLANKET')*/ --<BUG 3290647>
3527
3528 --<BUG 3290647>
3529 --Archive org assignments for global contracts as well as global blankets.
3530
3531 l_progress := '160';
3532 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
3533 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
3534 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
3535 'Select global agreement flag - po_header_id: '||p_document_id);
3536 END IF;
3537 -- SQL What:Find out if it is a global agreement
3538 -- SQL Why :Archive org_assignment table if needed
3539 SELECT NVL(global_agreement_flag, 'N')
3540 INTO l_ga_flag
3541 FROM po_headers_all
3542 WHERE po_header_id = p_document_id;
3543
3544 IF (l_ga_flag = 'Y') THEN
3545 l_progress := '170';
3546 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
3547 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
3548 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
3549 'archive_org_assignments() p_document_id: '||p_document_id||
3550 ', l_revision_num: '||l_revision_num);
3551 END IF;
3552 archive_org_assignments(p_document_id, l_revision_num);
3553
3554 END IF; /*IF (l_ga_flag = 'Y') THEN*/
3555
3556 l_progress := '180';
3557 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
3558 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
3559 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
3560 'archive_contract_terms() p_document_id: '||p_document_id||
3561 ', p_document_type: '||p_document_type||
3562 ', p_document_subtype: '||p_document_subtype||
3563 ', p_revision_num: '||l_revision_num);
3564 END IF;
3565 archive_contract_terms(p_document_id, p_document_type,
3566 p_document_subtype, l_revision_num);
3567
3568 --<Start CLM View Base Document>
3569 -- Archive UDA Data/Attachments for UDA/CLM Enabled Documents, and for Revision 0 only
3570 IF l_revision_num = 0 THEN
3571
3572 l_progress := '185';
3573 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
3574 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
3575 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
3576 'archive_uda_attribs() p_document_id: '||p_document_id||
3577 ', p_document_type: '||p_document_type||
3578 ', p_document_subtype: '||p_document_subtype||
3579 ', p_revision_num: '||l_revision_num);
3580 END IF;
3581 --<PAR Project> : Removed call to archive_uda. Shifted to begining.
3582
3583 l_clm_flag := PO_CLM_INTG_GRP.is_clm_po(p_document_id);
3584
3585 IF l_clm_flag = 'Y' THEN
3586
3587 l_progress := '190';
3588 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
3589 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
3590 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
3591 'archive_attachments() p_document_id: '||p_document_id||
3592 ', p_document_type: '||p_document_type||
3593 ', p_document_subtype: '||p_document_subtype||
3594 ', p_revision_num: '||l_revision_num);
3595 END IF;
3596
3597 archive_attachments(p_document_id, p_document_type,
3598 p_document_subtype, l_revision_num, l_draft_id);
3599
3600 l_progress := '195';
3601 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
3602 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
3603 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
3604 'sync_contract_terms_to_draft() p_document_id: '||p_document_id||
3605 ', p_document_type: '||p_document_type||
3606 ', p_document_subtype: '||p_document_subtype||
3607 ', p_revision_num: '||l_revision_num);
3608 END IF;
3609
3610
3611 sync_contract_terms_to_draft(p_document_id, p_document_type,
3612 p_document_subtype, l_revision_num);
3613
3614 END IF; --IF l_clm_flag = 'Y'
3615 END IF; --IF l_revision_num = 0
3616 --<End CLM View Base Document>
3617
3618 END IF; /*IF (l_return_status = 'Y')*/
3619 ELSE
3620 l_progress := '200';
3621 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
3622 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
3623 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
3624 'check_release_archive() p_document_id: '||p_document_id);
3625 END IF;
3626 check_release_archive(p_document_id, l_revision_num, l_return_status);
3627
3628 IF (l_return_status = 'Y') THEN
3629 l_progress := '210';
3630 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
3631 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
3632 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
3633 'archive_release() p_document_id: '||p_document_id);
3634 END IF;
3635 archive_release(p_document_id);
3636
3637 l_progress := '220';
3638 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
3639 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
3640 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
3641 'archive_line_locations() p_document_id: '||p_document_id||
3642 ', p_document_type: '||p_document_type||
3643 ', l_revision_num: '||l_revision_num);
3644 END IF;
3645 archive_line_locations(p_document_id, p_document_type, l_revision_num);
3646
3647 l_progress := '230';
3648 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
3649 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
3650 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
3651 'archive_distributions() p_document_id: '||p_document_id||
3652 ', p_document_type: '||p_document_type||
3653 ', l_revision_num: '||l_revision_num);
3654 END IF;
3655 archive_distributions(p_document_id, p_document_type, l_revision_num);
3656 END IF; /*IF (l_return_status = 'Y')*/
3657 END IF; /*IF (p_document_type = 'PO')*/
3658
3659 -- Standard call to get message count and if count is 1,
3660 -- get message info.
3661 FND_MSG_PUB.Count_And_Get
3662 (p_count => x_msg_count,
3663 p_data => x_msg_data
3664 );
3665 l_progress := '300';
3666 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
3667 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
3668 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
3669 'Returning from PVT package');
3670 END IF;
3671 x_return_status := FND_API.G_RET_STS_SUCCESS;
3672
3673 l_progress := '310';
3674 EXCEPTION
3675 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3676 ROLLBACK TO PO_ARCHIVE_SP;
3677 IF (G_FND_DEBUG = 'Y') THEN
3678 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
3679 FND_LOG.string(FND_LOG.level_unexpected, l_module,
3680 'EXCEPTION: '||sqlerrm);
3681 END IF;
3682 END IF;
3683 -- Standard call to get message count and if count is 1,
3684 -- get message info.
3685 FND_MSG_PUB.Count_And_Get
3686 (p_count => x_msg_count,
3687 p_data => x_msg_data
3688 );
3689 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3690 WHEN FND_API.G_EXC_ERROR THEN
3691 ROLLBACK TO PO_ARCHIVE_SP;
3692 IF (G_FND_DEBUG = 'Y') THEN
3693 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
3694 FND_LOG.string(FND_LOG.level_unexpected, l_module,
3695 'EXCEPTION: '||sqlerrm);
3696 END IF;
3697 END IF;
3698 -- Standard call to get message count and if count is 1,
3699 -- get message info.
3700 FND_MSG_PUB.Count_And_Get
3701 (p_count => x_msg_count,
3702 p_data => x_msg_data
3703 );
3704 x_return_status := FND_API.G_RET_STS_ERROR;
3705 WHEN OTHERS THEN
3706 ROLLBACK TO PO_ARCHIVE_SP;
3707 IF (G_FND_DEBUG = 'Y') THEN
3708 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
3709 FND_LOG.string(FND_LOG.level_unexpected, l_module,
3710 'EXCEPTION: '||sqlerrm);
3711 END IF;
3712 END IF;
3713 -- Standard call to get message count and if count is 1,
3714 -- get message info.
3715 FND_MSG_PUB.Count_And_Get
3716 (p_count => x_msg_count,
3717 p_data => x_msg_data
3718 );
3719 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3720 END ARCHIVE_PO;
3721
3722 -----------------------------------------------------------------<SERVICES FPJ>
3723 -------------------------------------------------------------------------------
3724 --Start of Comments
3725 --Name: is_line_archived
3726 --Pre-reqs:
3727 -- None.
3728 --Modifies:
3729 -- None.
3730 --Locks:
3731 -- None.
3732 --Function:
3733 -- Determines if the given line is archived.
3734 --Parameters:
3735 --IN:
3736 --p_po_line_id
3737 -- Unique ID of line to check for archival.
3738 --Returns:
3739 -- TRUE if the line exists in the Archive table. FALSE, otherwise.
3740 --Testing:
3741 -- None.
3742 --End of Comments
3743 -------------------------------------------------------------------------------
3744 -------------------------------------------------------------------------------
3745 FUNCTION is_line_archived
3746 (
3747 p_po_line_id IN NUMBER
3748 )
3749 RETURN BOOLEAN
3750 IS
3751 CURSOR archived_line_csr IS
3752 SELECT 'Line archive records'
3753 FROM po_lines_archive_all
3754 WHERE po_line_id = p_po_line_id;
3755
3756 l_archived_line_csr_type archived_line_csr%ROWTYPE;
3757 l_line_is_archived BOOLEAN;
3758
3759 BEGIN
3760
3761 OPEN archived_line_csr;
3762 FETCH archived_line_csr INTO l_archived_line_csr_type;
3763 l_line_is_archived := archived_line_csr%FOUND;
3764 CLOSE archived_line_csr;
3765
3766 return (l_line_is_archived);
3767
3768 EXCEPTION
3769
3770 WHEN OTHERS THEN
3771 PO_MESSAGE_S.sql_error('PO_DOCUMENT_ARCHIVE_PVT.is_line_archived','000',sqlcode);
3772 RAISE;
3773
3774 END is_line_archived;
3775
3776
3777 -----------------------------------------------------------------<SERVICES FPJ>
3778 -------------------------------------------------------------------------------
3779 --Start of Comments
3780 --Name: is_line_location_archived
3781 --Pre-reqs:
3782 -- None.
3783 --Modifies:
3784 -- None.
3785 --Locks:
3786 -- None.
3787 --Function:
3788 -- Determines if the given line is archived.
3789 --Parameters:
3790 --IN:
3791 --p_po_line_id
3792 -- Unique ID of line to check for archival.
3793 --Returns:
3794 -- TRUE if the line exists in the Archive table. FALSE, otherwise.
3795 --Testing:
3796 -- None.
3797 --End of Comments
3798 -------------------------------------------------------------------------------
3799 -------------------------------------------------------------------------------
3800 FUNCTION is_line_location_archived
3801 (
3802 p_line_location_id IN NUMBER
3803 )
3804 RETURN BOOLEAN
3805 IS
3806 CURSOR archived_line_location_csr IS
3807 SELECT 'Line archive records'
3808 FROM po_line_locations_archive_all
3809 WHERE line_location_id = p_line_location_id;
3810
3811 l_archive_csr_type archived_line_location_csr%ROWTYPE;
3812 l_line_location_is_archived BOOLEAN;
3813
3814 BEGIN
3815
3816 OPEN archived_line_location_csr;
3817 FETCH archived_line_location_csr INTO l_archive_csr_type;
3818 l_line_location_is_archived := archived_line_location_csr%FOUND;
3819 CLOSE archived_line_location_csr;
3820
3821 return (l_line_location_is_archived);
3822
3823 EXCEPTION
3824
3825 WHEN OTHERS THEN
3826 PO_MESSAGE_S.sql_error('PO_DOCUMENT_ARCHIVE_PVT.is_line_location_archived','000',sqlcode);
3827 RAISE;
3828
3829 END is_line_location_archived;
3830
3831
3832 -----------------------------------------------------------------<SERVICES FPJ>
3833 -------------------------------------------------------------------------------
3834 --Start of Comments
3835 --Name: is_price_differential_archived
3836 --Pre-reqs:
3837 -- None.
3838 --Modifies:
3839 -- None.
3840 --Locks:
3841 -- None.
3842 --Function:
3843 -- Determines if the given line is archived.
3844 --Parameters:
3845 --IN:
3846 --p_po_line_id
3847 -- Unique ID of line to check for archival.
3848 --Returns:
3849 -- TRUE if the line exists in the Archive table. FALSE, otherwise.
3850 --Testing:
3851 -- None.
3852 --End of Comments
3853 -------------------------------------------------------------------------------
3854 -------------------------------------------------------------------------------
3855 FUNCTION is_price_differential_archived
3856 (
3857 p_price_differential_id IN NUMBER
3858 )
3859 RETURN BOOLEAN
3860 IS
3861 CURSOR archived_price_diff_csr IS
3862 SELECT 'Price Differential archive records'
3863 FROM po_price_differentials_archive
3864 WHERE price_differential_id = p_price_differential_id;
3865
3866 l_archive_csr_type archived_price_diff_csr%ROWTYPE;
3867 l_price_diff_is_archived BOOLEAN;
3868
3869 BEGIN
3870
3871 OPEN archived_price_diff_csr;
3872 FETCH archived_price_diff_csr INTO l_archive_csr_type;
3873 l_price_diff_is_archived := archived_price_diff_csr%FOUND;
3874 CLOSE archived_price_diff_csr;
3875
3876 return (l_price_diff_is_archived);
3877
3878 EXCEPTION
3879
3880 WHEN OTHERS THEN
3881 PO_MESSAGE_S.sql_error('PO_DOCUMENT_ARCHIVE_PVT.is_price_differential_archived','000',sqlcode);
3882 RAISE;
3883
3884 END is_price_differential_archived;
3885
3886 ------------------------------------------------------------------- Bug 3565522
3887 -------------------------------------------------------------------------------
3888 --Start of Comments
3889 --Name: get_archive_mode
3890 --Pre-reqs:
3891 -- None.
3892 --Modifies:
3893 -- None.
3894 --Locks:
3895 -- None.
3896 --Function:
3897 -- Determines if the given document is archived on approve or communicate
3898 --Parameters:
3899 --IN:
3900 --p_doc_type
3901 -- type of the document to be checked - PO or BLANKET
3902 --p_doc_subtype
3903 -- sub type of the document to be checked - RELEASE or STANDARD
3904 --Return
3905 -- Archive mode - APPROVE or PRINT
3906 --Testing:
3907 -- None.
3908 --End of Comments
3909 -------------------------------------------------------------------------------
3910 -------------------------------------------------------------------------------
3911 FUNCTION get_archive_mode
3912 ( p_doc_type IN VARCHAR2 ,
3913 p_doc_subtype IN VARCHAR2
3914 ) RETURN VARCHAR2
3915 IS
3916
3917 l_archive_mode PO_DOCUMENT_TYPES.archive_external_revision_code%TYPE;
3918
3919 BEGIN
3920
3921 -- SQL What: Get archive mode for Standard PO, Release
3922 -- SQL Why : To Determine when the document is approved
3923
3924 SELECT nvl(archive_external_revision_code,'PRINT')
3925 INTO l_archive_mode
3926 FROM po_document_types
3927 WHERE document_type_code = p_doc_type
3928 AND document_subtype = p_doc_subtype;
3929
3930 RETURN l_archive_mode;
3931
3932 EXCEPTION
3933 WHEN OTHERS THEN
3934 PO_MESSAGE_S.sql_error('PO_DOCUMENT_ARCHIVE_PVT.get_archive_mode','000',sqlcode);
3935 RAISE;
3936 END;
3937
3938 -------------------------------------------------------------------------------
3939 --Start of Comments
3940 --Name: archive_attribute_values
3941 --Pre-reqs:
3942 -- None.
3943 --Modifies:
3944 -- PO_ATTR_VALUES_ARCHIVE
3945 --Locks:
3946 -- None.
3947 --Function:
3948 -- Archive Item Attribute Values
3949 --Parameters:
3950 --IN:
3951 --p_po_header_id
3952 -- The PO_HEADER_ID of the document that needs to be archived
3953 --Testing:
3954 -- None.
3955 --End of Comments
3956 -------------------------------------------------------------------------------
3957 PROCEDURE archive_attribute_values
3958 (
3959 p_po_header_id IN NUMBER
3960 , p_revision_num IN NUMBER
3961 )
3962 IS
3963 d_mod CONSTANT VARCHAR2(100) := D_archive_attribute_values;
3964 l_progress VARCHAR2(4) := '000';
3965
3966 --bug 10349444
3967 cursor c is select po_line_id from po_lines_archive_all
3968 where po_header_id = p_po_header_id
3969 and revision_num = p_revision_num;
3970
3971 l_po_line_id po_lines_all.po_line_id%type;
3972
3973 BEGIN
3974 l_progress := '000';
3975
3976 IF PO_LOG.d_proc THEN
3977 PO_LOG.proc_begin(d_mod,'p_po_header_id',p_po_header_id);
3978 PO_LOG.proc_begin(d_mod,'p_revision_num',p_revision_num);
3979 END IF;
3980
3981 IF PO_LOG.d_stmt THEN PO_LOG.stmt(d_mod,l_progress,'Calling INSERT'); END IF;
3982
3983 l_progress := '010';
3984
3985 -- bug 10349444
3986 OPEN c;
3987 LOOP
3988 FETCH c INTO l_po_line_id;
3989 EXIT WHEN c%notfound;
3990
3991 INSERT INTO PO_ATTR_VALUES_ARCHIVE
3992 (
3993 attribute_values_id,
3994 revision_num,
3995 po_line_id,
3996 req_template_name,
3997 req_template_line_num,
3998 ip_category_id,
3999 inventory_item_id,
4000 org_id,
4001 manufacturer_part_num,
4002 thumbnail_image,
4003 supplier_url,
4004 manufacturer_url,
4005 attachment_url,
4006 unspsc,
4007 availability,
4008 lead_time,
4009 text_base_attribute1,
4010 text_base_attribute2,
4011 text_base_attribute3,
4012 text_base_attribute4,
4013 text_base_attribute5,
4014 text_base_attribute6,
4015 text_base_attribute7,
4016 text_base_attribute8,
4017 text_base_attribute9,
4018 text_base_attribute10,
4019 text_base_attribute11,
4020 text_base_attribute12,
4021 text_base_attribute13,
4022 text_base_attribute14,
4023 text_base_attribute15,
4024 text_base_attribute16,
4025 text_base_attribute17,
4026 text_base_attribute18,
4027 text_base_attribute19,
4028 text_base_attribute20,
4029 text_base_attribute21,
4030 text_base_attribute22,
4031 text_base_attribute23,
4032 text_base_attribute24,
4033 text_base_attribute25,
4034 text_base_attribute26,
4035 text_base_attribute27,
4036 text_base_attribute28,
4037 text_base_attribute29,
4038 text_base_attribute30,
4039 text_base_attribute31,
4040 text_base_attribute32,
4041 text_base_attribute33,
4042 text_base_attribute34,
4043 text_base_attribute35,
4044 text_base_attribute36,
4045 text_base_attribute37,
4046 text_base_attribute38,
4047 text_base_attribute39,
4048 text_base_attribute40,
4049 text_base_attribute41,
4050 text_base_attribute42,
4051 text_base_attribute43,
4052 text_base_attribute44,
4053 text_base_attribute45,
4054 text_base_attribute46,
4055 text_base_attribute47,
4056 text_base_attribute48,
4057 text_base_attribute49,
4058 text_base_attribute50,
4059 text_base_attribute51,
4060 text_base_attribute52,
4061 text_base_attribute53,
4062 text_base_attribute54,
4063 text_base_attribute55,
4064 text_base_attribute56,
4065 text_base_attribute57,
4066 text_base_attribute58,
4067 text_base_attribute59,
4068 text_base_attribute60,
4069 text_base_attribute61,
4070 text_base_attribute62,
4071 text_base_attribute63,
4072 text_base_attribute64,
4073 text_base_attribute65,
4074 text_base_attribute66,
4075 text_base_attribute67,
4076 text_base_attribute68,
4077 text_base_attribute69,
4078 text_base_attribute70,
4079 text_base_attribute71,
4080 text_base_attribute72,
4081 text_base_attribute73,
4082 text_base_attribute74,
4083 text_base_attribute75,
4084 text_base_attribute76,
4085 text_base_attribute77,
4086 text_base_attribute78,
4087 text_base_attribute79,
4088 text_base_attribute80,
4089 text_base_attribute81,
4090 text_base_attribute82,
4091 text_base_attribute83,
4092 text_base_attribute84,
4093 text_base_attribute85,
4094 text_base_attribute86,
4095 text_base_attribute87,
4096 text_base_attribute88,
4097 text_base_attribute89,
4098 text_base_attribute90,
4099 text_base_attribute91,
4100 text_base_attribute92,
4101 text_base_attribute93,
4102 text_base_attribute94,
4103 text_base_attribute95,
4104 text_base_attribute96,
4105 text_base_attribute97,
4106 text_base_attribute98,
4107 text_base_attribute99,
4108 text_base_attribute100,
4109 num_base_attribute1,
4110 num_base_attribute2,
4111 num_base_attribute3,
4112 num_base_attribute4,
4113 num_base_attribute5,
4114 num_base_attribute6,
4115 num_base_attribute7,
4116 num_base_attribute8,
4117 num_base_attribute9,
4118 num_base_attribute10,
4119 num_base_attribute11,
4120 num_base_attribute12,
4121 num_base_attribute13,
4122 num_base_attribute14,
4123 num_base_attribute15,
4124 num_base_attribute16,
4125 num_base_attribute17,
4126 num_base_attribute18,
4127 num_base_attribute19,
4128 num_base_attribute20,
4129 num_base_attribute21,
4130 num_base_attribute22,
4131 num_base_attribute23,
4132 num_base_attribute24,
4133 num_base_attribute25,
4134 num_base_attribute26,
4135 num_base_attribute27,
4136 num_base_attribute28,
4137 num_base_attribute29,
4138 num_base_attribute30,
4139 num_base_attribute31,
4140 num_base_attribute32,
4141 num_base_attribute33,
4142 num_base_attribute34,
4143 num_base_attribute35,
4144 num_base_attribute36,
4145 num_base_attribute37,
4146 num_base_attribute38,
4147 num_base_attribute39,
4148 num_base_attribute40,
4149 num_base_attribute41,
4150 num_base_attribute42,
4151 num_base_attribute43,
4152 num_base_attribute44,
4153 num_base_attribute45,
4154 num_base_attribute46,
4155 num_base_attribute47,
4156 num_base_attribute48,
4157 num_base_attribute49,
4158 num_base_attribute50,
4159 num_base_attribute51,
4160 num_base_attribute52,
4161 num_base_attribute53,
4162 num_base_attribute54,
4163 num_base_attribute55,
4164 num_base_attribute56,
4165 num_base_attribute57,
4166 num_base_attribute58,
4167 num_base_attribute59,
4168 num_base_attribute60,
4169 num_base_attribute61,
4170 num_base_attribute62,
4171 num_base_attribute63,
4172 num_base_attribute64,
4173 num_base_attribute65,
4174 num_base_attribute66,
4175 num_base_attribute67,
4176 num_base_attribute68,
4177 num_base_attribute69,
4178 num_base_attribute70,
4179 num_base_attribute71,
4180 num_base_attribute72,
4181 num_base_attribute73,
4182 num_base_attribute74,
4183 num_base_attribute75,
4184 num_base_attribute76,
4185 num_base_attribute77,
4186 num_base_attribute78,
4187 num_base_attribute79,
4188 num_base_attribute80,
4189 num_base_attribute81,
4190 num_base_attribute82,
4191 num_base_attribute83,
4192 num_base_attribute84,
4193 num_base_attribute85,
4194 num_base_attribute86,
4195 num_base_attribute87,
4196 num_base_attribute88,
4197 num_base_attribute89,
4198 num_base_attribute90,
4199 num_base_attribute91,
4200 num_base_attribute92,
4201 num_base_attribute93,
4202 num_base_attribute94,
4203 num_base_attribute95,
4204 num_base_attribute96,
4205 num_base_attribute97,
4206 num_base_attribute98,
4207 num_base_attribute99,
4208 num_base_attribute100,
4209 text_cat_attribute1,
4210 text_cat_attribute2,
4211 text_cat_attribute3,
4212 text_cat_attribute4,
4213 text_cat_attribute5,
4214 text_cat_attribute6,
4215 text_cat_attribute7,
4216 text_cat_attribute8,
4217 text_cat_attribute9,
4218 text_cat_attribute10,
4219 text_cat_attribute11,
4220 text_cat_attribute12,
4221 text_cat_attribute13,
4222 text_cat_attribute14,
4223 text_cat_attribute15,
4224 text_cat_attribute16,
4225 text_cat_attribute17,
4226 text_cat_attribute18,
4227 text_cat_attribute19,
4228 text_cat_attribute20,
4229 text_cat_attribute21,
4230 text_cat_attribute22,
4231 text_cat_attribute23,
4232 text_cat_attribute24,
4233 text_cat_attribute25,
4234 text_cat_attribute26,
4235 text_cat_attribute27,
4236 text_cat_attribute28,
4237 text_cat_attribute29,
4238 text_cat_attribute30,
4239 text_cat_attribute31,
4240 text_cat_attribute32,
4241 text_cat_attribute33,
4242 text_cat_attribute34,
4243 text_cat_attribute35,
4244 text_cat_attribute36,
4245 text_cat_attribute37,
4246 text_cat_attribute38,
4247 text_cat_attribute39,
4248 text_cat_attribute40,
4249 text_cat_attribute41,
4250 text_cat_attribute42,
4251 text_cat_attribute43,
4252 text_cat_attribute44,
4253 text_cat_attribute45,
4254 text_cat_attribute46,
4255 text_cat_attribute47,
4256 text_cat_attribute48,
4257 text_cat_attribute49,
4258 text_cat_attribute50,
4259 num_cat_attribute1,
4260 num_cat_attribute2,
4261 num_cat_attribute3,
4262 num_cat_attribute4,
4263 num_cat_attribute5,
4264 num_cat_attribute6,
4265 num_cat_attribute7,
4266 num_cat_attribute8,
4267 num_cat_attribute9,
4268 num_cat_attribute10,
4269 num_cat_attribute11,
4270 num_cat_attribute12,
4271 num_cat_attribute13,
4272 num_cat_attribute14,
4273 num_cat_attribute15,
4274 num_cat_attribute16,
4275 num_cat_attribute17,
4276 num_cat_attribute18,
4277 num_cat_attribute19,
4278 num_cat_attribute20,
4279 num_cat_attribute21,
4280 num_cat_attribute22,
4281 num_cat_attribute23,
4282 num_cat_attribute24,
4283 num_cat_attribute25,
4284 num_cat_attribute26,
4285 num_cat_attribute27,
4286 num_cat_attribute28,
4287 num_cat_attribute29,
4288 num_cat_attribute30,
4289 num_cat_attribute31,
4290 num_cat_attribute32,
4291 num_cat_attribute33,
4292 num_cat_attribute34,
4293 num_cat_attribute35,
4294 num_cat_attribute36,
4295 num_cat_attribute37,
4296 num_cat_attribute38,
4297 num_cat_attribute39,
4298 num_cat_attribute40,
4299 num_cat_attribute41,
4300 num_cat_attribute42,
4301 num_cat_attribute43,
4302 num_cat_attribute44,
4303 num_cat_attribute45,
4304 num_cat_attribute46,
4305 num_cat_attribute47,
4306 num_cat_attribute48,
4307 num_cat_attribute49,
4308 num_cat_attribute50,
4309 last_update_login,
4310 last_updated_by,
4311 last_update_date,
4312 created_by,
4313 creation_date,
4314 request_id,
4315 program_application_id,
4316 program_id,
4317 program_update_date,
4318 last_updated_program,
4319 latest_external_flag,
4320 PICTURE -- <CLM View Base Document>
4321 )
4322 SELECT
4323 ATTR.attribute_values_id,
4324 p_revision_num,
4325 ATTR.po_line_id,
4326 ATTR.req_template_name,
4327 ATTR.req_template_line_num,
4328 ATTR.ip_category_id,
4329 ATTR.inventory_item_id,
4330 ATTR.org_id,
4331 ATTR.manufacturer_part_num,
4332 ATTR.thumbnail_image,
4333 ATTR.supplier_url,
4334 ATTR.manufacturer_url,
4335 ATTR.attachment_url,
4336 ATTR.unspsc,
4337 ATTR.availability,
4338 ATTR.lead_time,
4339 ATTR.text_base_attribute1,
4340 ATTR.text_base_attribute2,
4341 ATTR.text_base_attribute3,
4342 ATTR.text_base_attribute4,
4343 ATTR.text_base_attribute5,
4344 ATTR.text_base_attribute6,
4345 ATTR.text_base_attribute7,
4346 ATTR.text_base_attribute8,
4347 ATTR.text_base_attribute9,
4348 ATTR.text_base_attribute10,
4349 ATTR.text_base_attribute11,
4350 ATTR.text_base_attribute12,
4351 ATTR.text_base_attribute13,
4352 ATTR.text_base_attribute14,
4353 ATTR.text_base_attribute15,
4354 ATTR.text_base_attribute16,
4355 ATTR.text_base_attribute17,
4356 ATTR.text_base_attribute18,
4357 ATTR.text_base_attribute19,
4358 ATTR.text_base_attribute20,
4359 ATTR.text_base_attribute21,
4360 ATTR.text_base_attribute22,
4361 ATTR.text_base_attribute23,
4362 ATTR.text_base_attribute24,
4363 ATTR.text_base_attribute25,
4364 ATTR.text_base_attribute26,
4365 ATTR.text_base_attribute27,
4366 ATTR.text_base_attribute28,
4367 ATTR.text_base_attribute29,
4368 ATTR.text_base_attribute30,
4369 ATTR.text_base_attribute31,
4370 ATTR.text_base_attribute32,
4371 ATTR.text_base_attribute33,
4372 ATTR.text_base_attribute34,
4373 ATTR.text_base_attribute35,
4374 ATTR.text_base_attribute36,
4375 ATTR.text_base_attribute37,
4376 ATTR.text_base_attribute38,
4377 ATTR.text_base_attribute39,
4378 ATTR.text_base_attribute40,
4379 ATTR.text_base_attribute41,
4380 ATTR.text_base_attribute42,
4381 ATTR.text_base_attribute43,
4382 ATTR.text_base_attribute44,
4383 ATTR.text_base_attribute45,
4384 ATTR.text_base_attribute46,
4385 ATTR.text_base_attribute47,
4386 ATTR.text_base_attribute48,
4387 ATTR.text_base_attribute49,
4388 ATTR.text_base_attribute50,
4389 ATTR.text_base_attribute51,
4390 ATTR.text_base_attribute52,
4391 ATTR.text_base_attribute53,
4392 ATTR.text_base_attribute54,
4393 ATTR.text_base_attribute55,
4394 ATTR.text_base_attribute56,
4395 ATTR.text_base_attribute57,
4396 ATTR.text_base_attribute58,
4397 ATTR.text_base_attribute59,
4398 ATTR.text_base_attribute60,
4399 ATTR.text_base_attribute61,
4400 ATTR.text_base_attribute62,
4401 ATTR.text_base_attribute63,
4402 ATTR.text_base_attribute64,
4403 ATTR.text_base_attribute65,
4404 ATTR.text_base_attribute66,
4405 ATTR.text_base_attribute67,
4406 ATTR.text_base_attribute68,
4407 ATTR.text_base_attribute69,
4408 ATTR.text_base_attribute70,
4409 ATTR.text_base_attribute71,
4410 ATTR.text_base_attribute72,
4411 ATTR.text_base_attribute73,
4412 ATTR.text_base_attribute74,
4413 ATTR.text_base_attribute75,
4414 ATTR.text_base_attribute76,
4415 ATTR.text_base_attribute77,
4416 ATTR.text_base_attribute78,
4417 ATTR.text_base_attribute79,
4418 ATTR.text_base_attribute80,
4419 ATTR.text_base_attribute81,
4420 ATTR.text_base_attribute82,
4421 ATTR.text_base_attribute83,
4422 ATTR.text_base_attribute84,
4423 ATTR.text_base_attribute85,
4424 ATTR.text_base_attribute86,
4425 ATTR.text_base_attribute87,
4426 ATTR.text_base_attribute88,
4427 ATTR.text_base_attribute89,
4428 ATTR.text_base_attribute90,
4429 ATTR.text_base_attribute91,
4430 ATTR.text_base_attribute92,
4431 ATTR.text_base_attribute93,
4432 ATTR.text_base_attribute94,
4433 ATTR.text_base_attribute95,
4434 ATTR.text_base_attribute96,
4435 ATTR.text_base_attribute97,
4436 ATTR.text_base_attribute98,
4437 ATTR.text_base_attribute99,
4438 ATTR.text_base_attribute100,
4439 ATTR.num_base_attribute1,
4440 ATTR.num_base_attribute2,
4441 ATTR.num_base_attribute3,
4442 ATTR.num_base_attribute4,
4443 ATTR.num_base_attribute5,
4444 ATTR.num_base_attribute6,
4445 ATTR.num_base_attribute7,
4446 ATTR.num_base_attribute8,
4447 ATTR.num_base_attribute9,
4448 ATTR.num_base_attribute10,
4449 ATTR.num_base_attribute11,
4450 ATTR.num_base_attribute12,
4451 ATTR.num_base_attribute13,
4452 ATTR.num_base_attribute14,
4453 ATTR.num_base_attribute15,
4454 ATTR.num_base_attribute16,
4455 ATTR.num_base_attribute17,
4456 ATTR.num_base_attribute18,
4457 ATTR.num_base_attribute19,
4458 ATTR.num_base_attribute20,
4459 ATTR.num_base_attribute21,
4460 ATTR.num_base_attribute22,
4461 ATTR.num_base_attribute23,
4462 ATTR.num_base_attribute24,
4463 ATTR.num_base_attribute25,
4464 ATTR.num_base_attribute26,
4465 ATTR.num_base_attribute27,
4466 ATTR.num_base_attribute28,
4467 ATTR.num_base_attribute29,
4468 ATTR.num_base_attribute30,
4469 ATTR.num_base_attribute31,
4470 ATTR.num_base_attribute32,
4471 ATTR.num_base_attribute33,
4472 ATTR.num_base_attribute34,
4473 ATTR.num_base_attribute35,
4474 ATTR.num_base_attribute36,
4475 ATTR.num_base_attribute37,
4476 ATTR.num_base_attribute38,
4477 ATTR.num_base_attribute39,
4478 ATTR.num_base_attribute40,
4479 ATTR.num_base_attribute41,
4480 ATTR.num_base_attribute42,
4481 ATTR.num_base_attribute43,
4482 ATTR.num_base_attribute44,
4483 ATTR.num_base_attribute45,
4484 ATTR.num_base_attribute46,
4485 ATTR.num_base_attribute47,
4486 ATTR.num_base_attribute48,
4487 ATTR.num_base_attribute49,
4488 ATTR.num_base_attribute50,
4489 ATTR.num_base_attribute51,
4490 ATTR.num_base_attribute52,
4491 ATTR.num_base_attribute53,
4492 ATTR.num_base_attribute54,
4493 ATTR.num_base_attribute55,
4494 ATTR.num_base_attribute56,
4495 ATTR.num_base_attribute57,
4496 ATTR.num_base_attribute58,
4497 ATTR.num_base_attribute59,
4498 ATTR.num_base_attribute60,
4499 ATTR.num_base_attribute61,
4500 ATTR.num_base_attribute62,
4501 ATTR.num_base_attribute63,
4502 ATTR.num_base_attribute64,
4503 ATTR.num_base_attribute65,
4504 ATTR.num_base_attribute66,
4505 ATTR.num_base_attribute67,
4506 ATTR.num_base_attribute68,
4507 ATTR.num_base_attribute69,
4508 ATTR.num_base_attribute70,
4509 ATTR.num_base_attribute71,
4510 ATTR.num_base_attribute72,
4511 ATTR.num_base_attribute73,
4512 ATTR.num_base_attribute74,
4513 ATTR.num_base_attribute75,
4514 ATTR.num_base_attribute76,
4515 ATTR.num_base_attribute77,
4516 ATTR.num_base_attribute78,
4517 ATTR.num_base_attribute79,
4518 ATTR.num_base_attribute80,
4519 ATTR.num_base_attribute81,
4520 ATTR.num_base_attribute82,
4521 ATTR.num_base_attribute83,
4522 ATTR.num_base_attribute84,
4523 ATTR.num_base_attribute85,
4524 ATTR.num_base_attribute86,
4525 ATTR.num_base_attribute87,
4526 ATTR.num_base_attribute88,
4527 ATTR.num_base_attribute89,
4528 ATTR.num_base_attribute90,
4529 ATTR.num_base_attribute91,
4530 ATTR.num_base_attribute92,
4531 ATTR.num_base_attribute93,
4532 ATTR.num_base_attribute94,
4533 ATTR.num_base_attribute95,
4534 ATTR.num_base_attribute96,
4535 ATTR.num_base_attribute97,
4536 ATTR.num_base_attribute98,
4537 ATTR.num_base_attribute99,
4538 ATTR.num_base_attribute100,
4539 ATTR.text_cat_attribute1,
4540 ATTR.text_cat_attribute2,
4541 ATTR.text_cat_attribute3,
4542 ATTR.text_cat_attribute4,
4543 ATTR.text_cat_attribute5,
4544 ATTR.text_cat_attribute6,
4545 ATTR.text_cat_attribute7,
4546 ATTR.text_cat_attribute8,
4547 ATTR.text_cat_attribute9,
4548 ATTR.text_cat_attribute10,
4549 ATTR.text_cat_attribute11,
4550 ATTR.text_cat_attribute12,
4551 ATTR.text_cat_attribute13,
4552 ATTR.text_cat_attribute14,
4553 ATTR.text_cat_attribute15,
4554 ATTR.text_cat_attribute16,
4555 ATTR.text_cat_attribute17,
4556 ATTR.text_cat_attribute18,
4557 ATTR.text_cat_attribute19,
4558 ATTR.text_cat_attribute20,
4559 ATTR.text_cat_attribute21,
4560 ATTR.text_cat_attribute22,
4561 ATTR.text_cat_attribute23,
4562 ATTR.text_cat_attribute24,
4563 ATTR.text_cat_attribute25,
4564 ATTR.text_cat_attribute26,
4565 ATTR.text_cat_attribute27,
4566 ATTR.text_cat_attribute28,
4567 ATTR.text_cat_attribute29,
4568 ATTR.text_cat_attribute30,
4569 ATTR.text_cat_attribute31,
4570 ATTR.text_cat_attribute32,
4571 ATTR.text_cat_attribute33,
4572 ATTR.text_cat_attribute34,
4573 ATTR.text_cat_attribute35,
4574 ATTR.text_cat_attribute36,
4575 ATTR.text_cat_attribute37,
4576 ATTR.text_cat_attribute38,
4577 ATTR.text_cat_attribute39,
4578 ATTR.text_cat_attribute40,
4579 ATTR.text_cat_attribute41,
4580 ATTR.text_cat_attribute42,
4581 ATTR.text_cat_attribute43,
4582 ATTR.text_cat_attribute44,
4583 ATTR.text_cat_attribute45,
4584 ATTR.text_cat_attribute46,
4585 ATTR.text_cat_attribute47,
4586 ATTR.text_cat_attribute48,
4587 ATTR.text_cat_attribute49,
4588 ATTR.text_cat_attribute50,
4589 ATTR.num_cat_attribute1,
4590 ATTR.num_cat_attribute2,
4591 ATTR.num_cat_attribute3,
4592 ATTR.num_cat_attribute4,
4593 ATTR.num_cat_attribute5,
4594 ATTR.num_cat_attribute6,
4595 ATTR.num_cat_attribute7,
4596 ATTR.num_cat_attribute8,
4597 ATTR.num_cat_attribute9,
4598 ATTR.num_cat_attribute10,
4599 ATTR.num_cat_attribute11,
4600 ATTR.num_cat_attribute12,
4601 ATTR.num_cat_attribute13,
4602 ATTR.num_cat_attribute14,
4603 ATTR.num_cat_attribute15,
4604 ATTR.num_cat_attribute16,
4605 ATTR.num_cat_attribute17,
4606 ATTR.num_cat_attribute18,
4607 ATTR.num_cat_attribute19,
4608 ATTR.num_cat_attribute20,
4609 ATTR.num_cat_attribute21,
4610 ATTR.num_cat_attribute22,
4611 ATTR.num_cat_attribute23,
4612 ATTR.num_cat_attribute24,
4613 ATTR.num_cat_attribute25,
4614 ATTR.num_cat_attribute26,
4615 ATTR.num_cat_attribute27,
4616 ATTR.num_cat_attribute28,
4617 ATTR.num_cat_attribute29,
4618 ATTR.num_cat_attribute30,
4619 ATTR.num_cat_attribute31,
4620 ATTR.num_cat_attribute32,
4621 ATTR.num_cat_attribute33,
4622 ATTR.num_cat_attribute34,
4623 ATTR.num_cat_attribute35,
4624 ATTR.num_cat_attribute36,
4625 ATTR.num_cat_attribute37,
4626 ATTR.num_cat_attribute38,
4627 ATTR.num_cat_attribute39,
4628 ATTR.num_cat_attribute40,
4629 ATTR.num_cat_attribute41,
4630 ATTR.num_cat_attribute42,
4631 ATTR.num_cat_attribute43,
4632 ATTR.num_cat_attribute44,
4633 ATTR.num_cat_attribute45,
4634 ATTR.num_cat_attribute46,
4635 ATTR.num_cat_attribute47,
4636 ATTR.num_cat_attribute48,
4637 ATTR.num_cat_attribute49,
4638 ATTR.num_cat_attribute50,
4639 ATTR.last_update_login,
4640 ATTR.last_updated_by,
4641 ATTR.last_update_date,
4642 ATTR.created_by,
4643 ATTR.creation_date,
4644 ATTR.request_id,
4645 ATTR.program_application_id,
4646 ATTR.program_id,
4647 ATTR.program_update_date,
4648 ATTR.last_updated_program,
4649 'Y', -- latest_external_flag
4650 ATTR.PICTURE -- <CLM View Base Document>
4651 FROM PO_ATTRIBUTE_VALUES ATTR,
4652 PO_LINES_ALL POL
4653 WHERE ATTR.po_line_id = POL.po_line_id
4654 AND POL.po_header_id = p_po_header_id
4655 AND POL.po_line_id = l_po_line_id; -- bug 10349444
4656 end loop;
4657 close c;
4658
4659 OPEN c;
4660 LOOP
4661 FETCH c INTO l_po_line_id;
4662 EXIT WHEN c%notfound;
4663
4664 UPDATE PO_ATTR_VALUES_ARCHIVE PAVA1
4665 SET PAVA1.latest_external_flag = 'N'
4666 WHERE PAVA1.po_line_id = l_po_line_id
4667 AND PAVA1.latest_external_flag = 'Y'
4668 AND PAVA1.revision_num < p_revision_num
4669 AND EXISTS
4670 (SELECT 'A new archived row'
4671 FROM PO_ATTR_VALUES_ARCHIVE PAVA2
4672 WHERE PAVA2.po_line_id = PAVA1.po_line_id
4673 AND PAVA2.latest_external_flag = 'Y'
4674 AND PAVA2.revision_num = p_revision_num);
4675 END LOOP;
4676 CLOSE c;
4677
4678 IF PO_LOG.d_stmt THEN PO_LOG.stmt(d_mod,l_progress,'Number of rows INSERTed into ATTR archive='||SQL%rowcount); END IF;
4679
4680 IF PO_LOG.d_proc THEN PO_LOG.proc_end(d_mod); END IF;
4681 EXCEPTION
4682 WHEN OTHERS THEN
4683 IF PO_LOG.d_exc THEN PO_LOG.exc(d_mod,l_progress,'Unhandled exception'); END IF;
4684 RAISE;
4685 END archive_attribute_values;
4686
4687 -------------------------------------------------------------------------------
4688 --Start of Comments
4689 --Name: archive_attr_values_tlp
4690 --Pre-reqs:
4691 -- None.
4692 --Modifies:
4693 -- PO_ATTR_VALUES_TLP_ARCHIVE
4694 --Locks:
4695 -- None.
4696 --Function:
4697 -- Archive Item Attribute Values TLP
4698 --Parameters:
4699 --IN:
4700 --p_po_header_id
4701 -- The PO_HEADER_ID of the document that needs to be archived
4702 --Testing:
4703 -- None.
4704 --End of Comments
4705 -------------------------------------------------------------------------------
4706 PROCEDURE archive_attr_values_tlp
4707 (
4708 p_po_header_id IN NUMBER
4709 , p_revision_num IN NUMBER
4710 )
4711 IS
4712 d_mod CONSTANT VARCHAR2(100) := D_archive_attr_values_tlp;
4713 l_progress VARCHAR2(4) := '000';
4714
4715 -- bug 10349444
4716 cursor c is select po_line_id from po_lines_archive_all
4717 where po_header_id = p_po_header_id
4718 and revision_num = p_revision_num;
4719
4720 l_po_line_id po_lines_all.po_line_id%type;
4721
4722 BEGIN
4723 l_progress := '000';
4724
4725 IF PO_LOG.d_proc THEN
4726 PO_LOG.proc_begin(d_mod,'p_po_header_id',p_po_header_id);
4727 PO_LOG.proc_begin(d_mod,'p_revision_num',p_revision_num);
4728 END IF;
4729
4730 IF PO_LOG.d_stmt THEN PO_LOG.stmt(d_mod,l_progress,'Calling INSERT'); END IF;
4731
4732 l_progress := '010';
4733
4734 -- bug 10349444
4735 OPEN c;
4736 LOOP
4737 FETCH c INTO l_po_line_id;
4738 EXIT WHEN c%notfound;
4739
4740 INSERT INTO PO_ATTR_VALUES_TLP_ARCHIVE
4741 (
4742 attribute_values_tlp_id,
4743 revision_num,
4744 latest_external_flag,
4745 po_line_id,
4746 req_template_name,
4747 req_template_line_num,
4748 ip_category_id,
4749 inventory_item_id,
4750 org_id,
4751 language,
4752 description,
4753 manufacturer,
4754 comments,
4755 alias,
4756 long_description,
4757 tl_text_base_attribute1,
4758 tl_text_base_attribute2,
4759 tl_text_base_attribute3,
4760 tl_text_base_attribute4,
4761 tl_text_base_attribute5,
4762 tl_text_base_attribute6,
4763 tl_text_base_attribute7,
4764 tl_text_base_attribute8,
4765 tl_text_base_attribute9,
4766 tl_text_base_attribute10,
4767 tl_text_base_attribute11,
4768 tl_text_base_attribute12,
4769 tl_text_base_attribute13,
4770 tl_text_base_attribute14,
4771 tl_text_base_attribute15,
4772 tl_text_base_attribute16,
4773 tl_text_base_attribute17,
4774 tl_text_base_attribute18,
4775 tl_text_base_attribute19,
4776 tl_text_base_attribute20,
4777 tl_text_base_attribute21,
4778 tl_text_base_attribute22,
4779 tl_text_base_attribute23,
4780 tl_text_base_attribute24,
4781 tl_text_base_attribute25,
4782 tl_text_base_attribute26,
4783 tl_text_base_attribute27,
4784 tl_text_base_attribute28,
4785 tl_text_base_attribute29,
4786 tl_text_base_attribute30,
4787 tl_text_base_attribute31,
4788 tl_text_base_attribute32,
4789 tl_text_base_attribute33,
4790 tl_text_base_attribute34,
4791 tl_text_base_attribute35,
4792 tl_text_base_attribute36,
4793 tl_text_base_attribute37,
4794 tl_text_base_attribute38,
4795 tl_text_base_attribute39,
4796 tl_text_base_attribute40,
4797 tl_text_base_attribute41,
4798 tl_text_base_attribute42,
4799 tl_text_base_attribute43,
4800 tl_text_base_attribute44,
4801 tl_text_base_attribute45,
4802 tl_text_base_attribute46,
4803 tl_text_base_attribute47,
4804 tl_text_base_attribute48,
4805 tl_text_base_attribute49,
4806 tl_text_base_attribute50,
4807 tl_text_base_attribute51,
4808 tl_text_base_attribute52,
4809 tl_text_base_attribute53,
4810 tl_text_base_attribute54,
4811 tl_text_base_attribute55,
4812 tl_text_base_attribute56,
4813 tl_text_base_attribute57,
4814 tl_text_base_attribute58,
4815 tl_text_base_attribute59,
4816 tl_text_base_attribute60,
4817 tl_text_base_attribute61,
4818 tl_text_base_attribute62,
4819 tl_text_base_attribute63,
4820 tl_text_base_attribute64,
4821 tl_text_base_attribute65,
4822 tl_text_base_attribute66,
4823 tl_text_base_attribute67,
4824 tl_text_base_attribute68,
4825 tl_text_base_attribute69,
4826 tl_text_base_attribute70,
4827 tl_text_base_attribute71,
4828 tl_text_base_attribute72,
4829 tl_text_base_attribute73,
4830 tl_text_base_attribute74,
4831 tl_text_base_attribute75,
4832 tl_text_base_attribute76,
4833 tl_text_base_attribute77,
4834 tl_text_base_attribute78,
4835 tl_text_base_attribute79,
4836 tl_text_base_attribute80,
4837 tl_text_base_attribute81,
4838 tl_text_base_attribute82,
4839 tl_text_base_attribute83,
4840 tl_text_base_attribute84,
4841 tl_text_base_attribute85,
4842 tl_text_base_attribute86,
4843 tl_text_base_attribute87,
4844 tl_text_base_attribute88,
4845 tl_text_base_attribute89,
4846 tl_text_base_attribute90,
4847 tl_text_base_attribute91,
4848 tl_text_base_attribute92,
4849 tl_text_base_attribute93,
4850 tl_text_base_attribute94,
4851 tl_text_base_attribute95,
4852 tl_text_base_attribute96,
4853 tl_text_base_attribute97,
4854 tl_text_base_attribute98,
4855 tl_text_base_attribute99,
4856 tl_text_base_attribute100,
4857 tl_text_cat_attribute1,
4858 tl_text_cat_attribute2,
4859 tl_text_cat_attribute3,
4860 tl_text_cat_attribute4,
4861 tl_text_cat_attribute5,
4862 tl_text_cat_attribute6,
4863 tl_text_cat_attribute7,
4864 tl_text_cat_attribute8,
4865 tl_text_cat_attribute9,
4866 tl_text_cat_attribute10,
4867 tl_text_cat_attribute11,
4868 tl_text_cat_attribute12,
4869 tl_text_cat_attribute13,
4870 tl_text_cat_attribute14,
4871 tl_text_cat_attribute15,
4872 tl_text_cat_attribute16,
4873 tl_text_cat_attribute17,
4874 tl_text_cat_attribute18,
4875 tl_text_cat_attribute19,
4876 tl_text_cat_attribute20,
4877 tl_text_cat_attribute21,
4878 tl_text_cat_attribute22,
4879 tl_text_cat_attribute23,
4880 tl_text_cat_attribute24,
4881 tl_text_cat_attribute25,
4882 tl_text_cat_attribute26,
4883 tl_text_cat_attribute27,
4884 tl_text_cat_attribute28,
4885 tl_text_cat_attribute29,
4886 tl_text_cat_attribute30,
4887 tl_text_cat_attribute31,
4888 tl_text_cat_attribute32,
4889 tl_text_cat_attribute33,
4890 tl_text_cat_attribute34,
4891 tl_text_cat_attribute35,
4892 tl_text_cat_attribute36,
4893 tl_text_cat_attribute37,
4894 tl_text_cat_attribute38,
4895 tl_text_cat_attribute39,
4896 tl_text_cat_attribute40,
4897 tl_text_cat_attribute41,
4898 tl_text_cat_attribute42,
4899 tl_text_cat_attribute43,
4900 tl_text_cat_attribute44,
4901 tl_text_cat_attribute45,
4902 tl_text_cat_attribute46,
4903 tl_text_cat_attribute47,
4904 tl_text_cat_attribute48,
4905 tl_text_cat_attribute49,
4906 tl_text_cat_attribute50,
4907 last_update_login,
4908 last_updated_by,
4909 last_update_date,
4910 created_by,
4911 creation_date,
4912 request_id,
4913 program_application_id,
4914 program_id,
4915 program_update_date,
4916 last_updated_program
4917 )
4918 SELECT
4919 TLP.attribute_values_tlp_id,
4920 p_revision_num, -- revision_num
4921 'Y', -- latest_external_flag,
4922 TLP.po_line_id,
4923 TLP.req_template_name,
4924 TLP.req_template_line_num,
4925 TLP.ip_category_id,
4926 TLP.inventory_item_id,
4927 TLP.org_id,
4928 TLP.language,
4929 TLP.description,
4930 TLP.manufacturer,
4931 TLP.comments,
4932 TLP.alias,
4933 TLP.long_description,
4934 TLP.tl_text_base_attribute1,
4935 TLP.tl_text_base_attribute2,
4936 TLP.tl_text_base_attribute3,
4937 TLP.tl_text_base_attribute4,
4938 TLP.tl_text_base_attribute5,
4939 TLP.tl_text_base_attribute6,
4940 TLP.tl_text_base_attribute7,
4941 TLP.tl_text_base_attribute8,
4942 TLP.tl_text_base_attribute9,
4943 TLP.tl_text_base_attribute10,
4944 TLP.tl_text_base_attribute11,
4945 TLP.tl_text_base_attribute12,
4946 TLP.tl_text_base_attribute13,
4947 TLP.tl_text_base_attribute14,
4948 TLP.tl_text_base_attribute15,
4949 TLP.tl_text_base_attribute16,
4950 TLP.tl_text_base_attribute17,
4951 TLP.tl_text_base_attribute18,
4952 TLP.tl_text_base_attribute19,
4953 TLP.tl_text_base_attribute20,
4954 TLP.tl_text_base_attribute21,
4955 TLP.tl_text_base_attribute22,
4956 TLP.tl_text_base_attribute23,
4957 TLP.tl_text_base_attribute24,
4958 TLP.tl_text_base_attribute25,
4959 TLP.tl_text_base_attribute26,
4960 TLP.tl_text_base_attribute27,
4961 TLP.tl_text_base_attribute28,
4962 TLP.tl_text_base_attribute29,
4963 TLP.tl_text_base_attribute30,
4964 TLP.tl_text_base_attribute31,
4965 TLP.tl_text_base_attribute32,
4966 TLP.tl_text_base_attribute33,
4967 TLP.tl_text_base_attribute34,
4968 TLP.tl_text_base_attribute35,
4969 TLP.tl_text_base_attribute36,
4970 TLP.tl_text_base_attribute37,
4971 TLP.tl_text_base_attribute38,
4972 TLP.tl_text_base_attribute39,
4973 TLP.tl_text_base_attribute40,
4974 TLP.tl_text_base_attribute41,
4975 TLP.tl_text_base_attribute42,
4976 TLP.tl_text_base_attribute43,
4977 TLP.tl_text_base_attribute44,
4978 TLP.tl_text_base_attribute45,
4979 TLP.tl_text_base_attribute46,
4980 TLP.tl_text_base_attribute47,
4981 TLP.tl_text_base_attribute48,
4982 TLP.tl_text_base_attribute49,
4983 TLP.tl_text_base_attribute50,
4984 TLP.tl_text_base_attribute51,
4985 TLP.tl_text_base_attribute52,
4986 TLP.tl_text_base_attribute53,
4987 TLP.tl_text_base_attribute54,
4988 TLP.tl_text_base_attribute55,
4989 TLP.tl_text_base_attribute56,
4990 TLP.tl_text_base_attribute57,
4991 TLP.tl_text_base_attribute58,
4992 TLP.tl_text_base_attribute59,
4993 TLP.tl_text_base_attribute60,
4994 TLP.tl_text_base_attribute61,
4995 TLP.tl_text_base_attribute62,
4996 TLP.tl_text_base_attribute63,
4997 TLP.tl_text_base_attribute64,
4998 TLP.tl_text_base_attribute65,
4999 TLP.tl_text_base_attribute66,
5000 TLP.tl_text_base_attribute67,
5001 TLP.tl_text_base_attribute68,
5002 TLP.tl_text_base_attribute69,
5003 TLP.tl_text_base_attribute70,
5004 TLP.tl_text_base_attribute71,
5005 TLP.tl_text_base_attribute72,
5006 TLP.tl_text_base_attribute73,
5007 TLP.tl_text_base_attribute74,
5008 TLP.tl_text_base_attribute75,
5009 TLP.tl_text_base_attribute76,
5010 TLP.tl_text_base_attribute77,
5011 TLP.tl_text_base_attribute78,
5012 TLP.tl_text_base_attribute79,
5013 TLP.tl_text_base_attribute80,
5014 TLP.tl_text_base_attribute81,
5015 TLP.tl_text_base_attribute82,
5016 TLP.tl_text_base_attribute83,
5017 TLP.tl_text_base_attribute84,
5018 TLP.tl_text_base_attribute85,
5019 TLP.tl_text_base_attribute86,
5020 TLP.tl_text_base_attribute87,
5021 TLP.tl_text_base_attribute88,
5022 TLP.tl_text_base_attribute89,
5023 TLP.tl_text_base_attribute90,
5024 TLP.tl_text_base_attribute91,
5025 TLP.tl_text_base_attribute92,
5026 TLP.tl_text_base_attribute93,
5027 TLP.tl_text_base_attribute94,
5028 TLP.tl_text_base_attribute95,
5029 TLP.tl_text_base_attribute96,
5030 TLP.tl_text_base_attribute97,
5031 TLP.tl_text_base_attribute98,
5032 TLP.tl_text_base_attribute99,
5033 TLP.tl_text_base_attribute100,
5034 TLP.tl_text_cat_attribute1,
5035 TLP.tl_text_cat_attribute2,
5036 TLP.tl_text_cat_attribute3,
5037 TLP.tl_text_cat_attribute4,
5038 TLP.tl_text_cat_attribute5,
5039 TLP.tl_text_cat_attribute6,
5040 TLP.tl_text_cat_attribute7,
5041 TLP.tl_text_cat_attribute8,
5042 TLP.tl_text_cat_attribute9,
5043 TLP.tl_text_cat_attribute10,
5044 TLP.tl_text_cat_attribute11,
5045 TLP.tl_text_cat_attribute12,
5046 TLP.tl_text_cat_attribute13,
5047 TLP.tl_text_cat_attribute14,
5048 TLP.tl_text_cat_attribute15,
5049 TLP.tl_text_cat_attribute16,
5050 TLP.tl_text_cat_attribute17,
5051 TLP.tl_text_cat_attribute18,
5052 TLP.tl_text_cat_attribute19,
5053 TLP.tl_text_cat_attribute20,
5054 TLP.tl_text_cat_attribute21,
5055 TLP.tl_text_cat_attribute22,
5056 TLP.tl_text_cat_attribute23,
5057 TLP.tl_text_cat_attribute24,
5058 TLP.tl_text_cat_attribute25,
5059 TLP.tl_text_cat_attribute26,
5060 TLP.tl_text_cat_attribute27,
5061 TLP.tl_text_cat_attribute28,
5062 TLP.tl_text_cat_attribute29,
5063 TLP.tl_text_cat_attribute30,
5064 TLP.tl_text_cat_attribute31,
5065 TLP.tl_text_cat_attribute32,
5066 TLP.tl_text_cat_attribute33,
5067 TLP.tl_text_cat_attribute34,
5068 TLP.tl_text_cat_attribute35,
5069 TLP.tl_text_cat_attribute36,
5070 TLP.tl_text_cat_attribute37,
5071 TLP.tl_text_cat_attribute38,
5072 TLP.tl_text_cat_attribute39,
5073 TLP.tl_text_cat_attribute40,
5074 TLP.tl_text_cat_attribute41,
5075 TLP.tl_text_cat_attribute42,
5076 TLP.tl_text_cat_attribute43,
5077 TLP.tl_text_cat_attribute44,
5078 TLP.tl_text_cat_attribute45,
5079 TLP.tl_text_cat_attribute46,
5080 TLP.tl_text_cat_attribute47,
5081 TLP.tl_text_cat_attribute48,
5082 TLP.tl_text_cat_attribute49,
5083 TLP.tl_text_cat_attribute50,
5084 TLP.last_update_login,
5085 TLP.last_updated_by,
5086 TLP.last_update_date,
5087 TLP.created_by,
5088 TLP.creation_date,
5089 TLP.request_id,
5090 TLP.program_application_id,
5091 TLP.program_id,
5092 TLP.program_update_date,
5093 TLP.last_updated_program
5094 FROM PO_ATTRIBUTE_VALUES_TLP TLP,
5095 PO_LINES_ALL POL
5096 WHERE TLP.po_line_id = POL.po_line_id
5097 AND POL.po_header_id = p_po_header_id
5098 AND pol.po_line_id = l_po_line_id; -- bug 10349444
5099 end loop;
5100 CLOSE c;
5101
5102 OPEN c;
5103 LOOP
5104 FETCH c INTO l_po_line_id;
5105 EXIT WHEN c%notfound;
5106
5107 UPDATE PO_ATTR_VALUES_TLP_ARCHIVE PAVTA1
5108 SET PAVTA1.latest_external_flag = 'N'
5109 WHERE PAVTA1.po_line_id = l_po_line_id
5110 AND PAVTA1.latest_external_flag = 'Y'
5111 AND PAVTA1.revision_num < p_revision_num
5112 AND EXISTS
5113 (SELECT 'A new archived row'
5114 FROM PO_ATTR_VALUES_TLP_ARCHIVE PAVTA2
5115 WHERE PAVTA2.po_line_id = PAVTA1.po_line_id
5116 AND PAVTA2.latest_external_flag = 'Y'
5117 AND PAVTA2.revision_num = p_revision_num);
5118 END LOOP;
5119 CLOSE c;
5120
5121 IF PO_LOG.d_stmt THEN PO_LOG.stmt(d_mod,l_progress,'Number of rows INSERTed into TLP archive='||SQL%rowcount); END IF;
5122
5123 IF PO_LOG.d_proc THEN PO_LOG.proc_end(d_mod); END IF;
5124 EXCEPTION
5125 WHEN OTHERS THEN
5126 IF PO_LOG.d_exc THEN PO_LOG.exc(d_mod,l_progress,'Unhandled exception'); END IF;
5127 RAISE;
5128 END archive_attr_values_tlp;
5129
5130 --<Enhanced Pricing Start: Archive Price Adjustments>
5131 -------------------------------------------------------------------------------
5132 --Start of Comments
5133 --Name: archive_price_adjustments
5134 --Pre-reqs:
5135 -- None.
5136 --Modifies:
5137 -- PO_PRICE_ADJUSTMENTS_ARCHIVE
5138 --Locks:
5139 -- None.
5140 --Function:
5141 -- Archive Price Adjustments
5142 --Parameters:
5143 --IN:
5144 --p_po_header_id
5145 -- The PO_HEADER_ID of the document that needs to be archived
5146 --Testing:
5147 -- None.
5148 --End of Comments
5149 -------------------------------------------------------------------------------
5150 PROCEDURE archive_price_adjustments
5151 ( p_po_header_id IN NUMBER
5152 , p_revision_num IN NUMBER
5153 )
5154 IS
5155 d_mod CONSTANT VARCHAR2(100) := D_archive_price_adjustments;
5156 l_progress VARCHAR2(4) := '000';
5157 BEGIN
5158 l_progress := '000';
5159
5160 IF PO_LOG.d_proc THEN
5161 PO_LOG.proc_begin(d_mod,'p_po_header_id',p_po_header_id);
5162 PO_LOG.proc_begin(d_mod,'p_revision_num',p_revision_num);
5163 END IF;
5164
5165 IF PO_LOG.d_stmt THEN PO_LOG.stmt(d_mod,l_progress,'Calling INSERT'); END IF;
5166
5167 l_progress := '010';
5168 INSERT INTO PO_PRICE_ADJUSTMENTS_ARCHIVE
5169 (
5170 price_adjustment_id,
5171 creation_date,
5172 created_by,
5173 last_update_date,
5174 last_updated_by,
5175 last_update_login,
5176 program_application_id,
5177 program_id,
5178 program_update_date,
5179 request_id,
5180 po_header_id,
5181 automatic_flag,
5182 po_line_id,
5183 context,
5184 attribute1,
5185 attribute2,
5186 attribute3,
5187 attribute4,
5188 attribute5,
5189 attribute6,
5190 attribute7,
5191 attribute8,
5192 attribute9,
5193 attribute10,
5194 attribute11,
5195 attribute12,
5196 attribute13,
5197 attribute14,
5198 attribute15,
5199 orig_sys_discount_ref,
5200 change_sequence,
5201 list_header_id,
5202 list_type_code,
5203 list_line_id,
5204 list_line_type_code,
5205 modified_from,
5206 modified_to,
5207 update_allowed,
5208 change_reason_code,
5209 change_reason_text,
5210 updated_flag,
5211 applied_flag,
5212 operand,
5213 arithmetic_operator,
5214 cost_id,
5215 tax_code,
5216 tax_exempt_flag,
5217 tax_exempt_number,
5218 tax_exempt_reason_code,
5219 parent_adjustment_id,
5220 invoiced_flag,
5221 estimated_flag,
5222 inc_in_sales_performance,
5223 adjusted_amount,
5224 pricing_phase_id,
5225 charge_type_code,
5226 charge_subtype_code,
5227 range_break_quantity,
5228 accrual_conversion_rate,
5229 pricing_group_sequence,
5230 accrual_flag,
5231 list_line_no,
5232 source_system_code,
5233 benefit_qty,
5234 benefit_uom_code,
5235 print_on_invoice_flag,
5236 expiration_date,
5237 rebate_transaction_type_code,
5238 rebate_transaction_reference,
5239 rebate_payment_system_code,
5240 redeemed_date,
5241 redeemed_flag,
5242 modifier_level_code,
5243 price_break_type_code,
5244 substitution_attribute,
5245 proration_type_code,
5246 include_on_returns_flag,
5247 credit_or_charge_flag,
5248 ac_context,
5249 ac_attribute1,
5250 ac_attribute2,
5251 ac_attribute3,
5252 ac_attribute4,
5253 ac_attribute5,
5254 ac_attribute6,
5255 ac_attribute7,
5256 ac_attribute8,
5257 ac_attribute9,
5258 ac_attribute10,
5259 ac_attribute11,
5260 ac_attribute12,
5261 ac_attribute13,
5262 ac_attribute14,
5263 ac_attribute15,
5264 operand_per_pqty,
5265 adjusted_amount_per_pqty,
5266 interco_invoiced_flag,
5267 invoiced_amount,
5268 retrobill_request_id,
5269 tax_rate_id,
5270 latest_external_flag,
5271 revision_num,
5272 adj_line_num
5273 )
5274 SELECT
5275 ADJ.price_adjustment_id,
5276 ADJ.creation_date,
5277 ADJ.created_by,
5278 ADJ.last_update_date,
5279 ADJ.last_updated_by,
5280 ADJ.last_update_login,
5281 ADJ.program_application_id,
5282 ADJ.program_id,
5283 ADJ.program_update_date,
5284 ADJ.request_id,
5285 ADJ.po_header_id,
5286 ADJ.automatic_flag,
5287 ADJ.po_line_id,
5288 ADJ.context,
5289 ADJ.attribute1,
5290 ADJ.attribute2,
5291 ADJ.attribute3,
5292 ADJ.attribute4,
5293 ADJ.attribute5,
5294 ADJ.attribute6,
5295 ADJ.attribute7,
5296 ADJ.attribute8,
5297 ADJ.attribute9,
5298 ADJ.attribute10,
5299 ADJ.attribute11,
5300 ADJ.attribute12,
5301 ADJ.attribute13,
5302 ADJ.attribute14,
5303 ADJ.attribute15,
5304 ADJ.orig_sys_discount_ref,
5305 ADJ.change_sequence,
5306 ADJ.list_header_id,
5307 ADJ.list_type_code,
5308 ADJ.list_line_id,
5309 ADJ.list_line_type_code,
5310 ADJ.modified_from,
5311 ADJ.modified_to,
5312 ADJ.update_allowed,
5313 ADJ.change_reason_code,
5314 ADJ.change_reason_text,
5315 ADJ.updated_flag,
5316 ADJ.applied_flag,
5317 ADJ.operand,
5318 ADJ.arithmetic_operator,
5319 ADJ.cost_id,
5320 ADJ.tax_code,
5321 ADJ.tax_exempt_flag,
5322 ADJ.tax_exempt_number,
5323 ADJ.tax_exempt_reason_code,
5324 ADJ.parent_adjustment_id,
5325 ADJ.invoiced_flag,
5326 ADJ.estimated_flag,
5327 ADJ.inc_in_sales_performance,
5328 ADJ.adjusted_amount,
5329 ADJ.pricing_phase_id,
5330 ADJ.charge_type_code,
5331 ADJ.charge_subtype_code,
5332 ADJ.range_break_quantity,
5333 ADJ.accrual_conversion_rate,
5334 ADJ.pricing_group_sequence,
5335 ADJ.accrual_flag,
5336 ADJ.list_line_no,
5337 ADJ.source_system_code,
5338 ADJ.benefit_qty,
5339 ADJ.benefit_uom_code,
5340 ADJ.print_on_invoice_flag,
5341 ADJ.expiration_date,
5342 ADJ.rebate_transaction_type_code,
5343 ADJ.rebate_transaction_reference,
5344 ADJ.rebate_payment_system_code,
5345 ADJ.redeemed_date,
5346 ADJ.redeemed_flag,
5347 ADJ.modifier_level_code,
5348 ADJ.price_break_type_code,
5349 ADJ.substitution_attribute,
5350 ADJ.proration_type_code,
5351 ADJ.include_on_returns_flag,
5352 ADJ.credit_or_charge_flag,
5353 ADJ.ac_context,
5354 ADJ.ac_attribute1,
5355 ADJ.ac_attribute2,
5356 ADJ.ac_attribute3,
5357 ADJ.ac_attribute4,
5358 ADJ.ac_attribute5,
5359 ADJ.ac_attribute6,
5360 ADJ.ac_attribute7,
5361 ADJ.ac_attribute8,
5362 ADJ.ac_attribute9,
5363 ADJ.ac_attribute10,
5364 ADJ.ac_attribute11,
5365 ADJ.ac_attribute12,
5366 ADJ.ac_attribute13,
5367 ADJ.ac_attribute14,
5368 ADJ.ac_attribute15,
5369 ADJ.operand_per_pqty,
5370 ADJ.adjusted_amount_per_pqty,
5371 ADJ.interco_invoiced_flag,
5372 ADJ.invoiced_amount,
5373 ADJ.retrobill_request_id,
5374 ADJ.tax_rate_id,
5375 'Y', -- latest_external_flag
5376 p_revision_num,
5377 ADJ.adj_line_num
5378 FROM PO_PRICE_ADJUSTMENTS ADJ,
5379 PO_LINES_ALL POL
5380 WHERE ADJ.po_line_id = POL.po_line_id
5381 AND POL.po_header_id = p_po_header_id;
5382
5383 IF PO_LOG.d_stmt THEN PO_LOG.stmt(d_mod,l_progress,'Number of rows INSERTed into PRICE ADJUSTMENTS archive='||SQL%rowcount); END IF;
5384
5385 IF PO_LOG.d_proc THEN PO_LOG.proc_end(d_mod); END IF;
5386 EXCEPTION
5387 WHEN OTHERS THEN
5388 IF PO_LOG.d_exc THEN PO_LOG.exc(d_mod,l_progress,'Unhandled exception'); END IF;
5389 RAISE;
5390 END archive_price_adjustments;
5391
5392 -------------------------------------------------------------------------------
5393 --Start of Comments
5394 --Name: archive_price_adj_attribs
5395 --Pre-reqs:
5396 -- None.
5397 --Modifies:
5398 -- PO_PRICE_ADJ_ATTRIBS_ARCHIVE
5399 --Locks:
5400 -- None.
5401 --Function:
5402 -- Archive Price Adjustment Attributes
5403 --Parameters:
5404 --IN:
5405 --p_po_header_id
5406 -- The PO_HEADER_ID of the document that needs to be archived
5407 --Testing:
5408 -- None.
5409 --End of Comments
5410 -------------------------------------------------------------------------------
5411 PROCEDURE archive_price_adj_attribs
5412 (
5413 p_po_header_id IN NUMBER
5414 , p_revision_num IN NUMBER
5415 )
5416 IS
5417 d_mod CONSTANT VARCHAR2(100) := D_archive_price_adj_attribs;
5418 l_progress VARCHAR2(4) := '000';
5419 BEGIN
5420 l_progress := '000';
5421
5422 IF PO_LOG.d_proc THEN
5423 PO_LOG.proc_begin(d_mod,'p_po_header_id',p_po_header_id);
5424 PO_LOG.proc_begin(d_mod,'p_revision_num',p_revision_num);
5425 END IF;
5426
5427 IF PO_LOG.d_stmt THEN PO_LOG.stmt(d_mod,l_progress,'Calling INSERT'); END IF;
5428
5429 l_progress := '010';
5430 INSERT INTO PO_PRICE_ADJ_ATTRIBS_ARCHIVE
5431 (
5432 price_adjustment_id,
5433 pricing_context,
5434 pricing_attribute,
5435 creation_date,
5436 created_by,
5437 last_update_date,
5438 last_updated_by,
5439 last_update_login,
5440 program_application_id,
5441 program_id,
5442 program_update_date,
5443 request_id,
5444 pricing_attr_value_from,
5445 pricing_attr_value_to,
5446 comparison_operator,
5447 flex_title,
5448 price_adj_attrib_id,
5449 latest_external_flag,
5450 revision_num
5451 )
5452 SELECT
5453 ATTR.price_adjustment_id,
5454 ATTR.pricing_context,
5455 ATTR.pricing_attribute,
5456 ATTR.creation_date,
5457 ATTR.created_by,
5458 ATTR.last_update_date,
5459 ATTR.last_updated_by,
5460 ATTR.last_update_login,
5461 ATTR.program_application_id,
5462 ATTR.program_id,
5463 ATTR.program_update_date,
5464 ATTR.request_id,
5465 ATTR.pricing_attr_value_from,
5466 ATTR.pricing_attr_value_to,
5467 ATTR.comparison_operator,
5468 ATTR.flex_title,
5469 ATTR.price_adj_attrib_id,
5470 'Y', -- latest_external_flag
5471 p_revision_num
5472 FROM PO_PRICE_ADJ_ATTRIBS ATTR,
5473 PO_PRICE_ADJUSTMENTS ADJ,
5474 PO_LINES_ALL POL
5475 WHERE ATTR.price_adjustment_id = ADJ.price_adjustment_id
5476 AND ADJ.po_line_id = POL.po_line_id
5477 AND POL.po_header_id = p_po_header_id;
5478
5479 IF PO_LOG.d_stmt THEN PO_LOG.stmt(d_mod,l_progress,'Number of rows INSERTed into PRICE ADJUSTMENT ATTRIBUTES archive='||SQL%rowcount); END IF;
5480
5481 IF PO_LOG.d_proc THEN PO_LOG.proc_end(d_mod); END IF;
5482 EXCEPTION
5483 WHEN OTHERS THEN
5484 IF PO_LOG.d_exc THEN PO_LOG.exc(d_mod,l_progress,'Unhandled exception'); END IF;
5485 RAISE;
5486 END archive_price_adj_attribs;
5487 --<Enhanced Pricing End>
5488
5489 --<CLM View Base Document>
5490 -------------------------------------------------------------------------------
5491 --Start of Comments
5492 --Name: archive_uda_attribs
5493 --Pre-reqs:
5494 -- None.
5495 --Modifies:
5496 -- PO_HEADERS_ALL_EXT_B_ARCHIVE / PO_HEADERS_ALL_EXT_TL_ARCHIVE
5497 -- PO_LINES_ALL_EXT_B_ARCHIVE / PO_LINES_ALL_EXT_TL_ARCHIVE
5498 -- PO_LINE_LOC_ALL_EXT_B_ARCHIVE / PO_LINE_LOC_ALL_EXT_TL_ARCHIVE
5499 --Locks:
5500 -- None.
5501 --Function:
5502 -- Archive UDA Data associated with the Document
5503 --Parameters:
5504 --IN:
5505 --p_po_header_id
5506 -- The PO_HEADER_ID of the document that needs to be archived
5507 --p_document_type
5508 -- The Document Type of the document that needs to be archived
5509 --p_document_subtype
5510 -- The Document Subtype of the document that needs to be archived
5511 --p_revision_num
5512 -- The revision number of the document that is being archived.
5513 -- In the current Scope of View Base Document project,
5514 -- only the 1st version (Revision 0) is being archived for UDA's
5515 --Testing:
5516 -- None.
5517 --End of Comments
5518 -------------------------------------------------------------------------------
5519 PROCEDURE archive_uda_attribs
5520 (
5521 p_po_header_id IN NUMBER
5522 , p_document_type IN VARCHAR2
5523 , p_document_subtype IN VARCHAR2
5524 , p_revision_num IN NUMBER
5525 )
5526 IS
5527 d_mod CONSTANT VARCHAR2(100) := 'archive_uda_attribs';
5528 l_progress VARCHAR2(4) := '000';
5529 l_functional_area_code VARCHAR2(500);
5530 l_document_type_code VARCHAR2(500);
5531 l_document_style_id NUMBER;
5532 l_msg_count NUMBER;
5533 l_msg_data VARCHAR2(4000);
5534 l_enabled_flag VARCHAR2(5);
5535 l_errorcode VARCHAR2(50);
5536 l_return_status VARCHAR2(1);
5537
5538 BEGIN
5539 l_progress := '000';
5540
5541 IF PO_LOG.d_proc THEN
5542 PO_LOG.proc_begin(d_mod,'p_po_header_id',p_po_header_id);
5543 PO_LOG.proc_begin(d_mod,'p_revision_num',p_revision_num);
5544 END IF;
5545
5546 -- Check if Document is UDA Enabled
5547 SELECT 'PURCHASING', TYPE_LOOKUP_CODE, STYLE_ID
5548 INTO l_functional_area_code, l_document_type_code, l_document_style_id
5549 FROM po_headers_all
5550 WHERE PO_HEADER_ID = p_po_header_id;
5551
5552 l_progress := '010';
5553 IF PO_LOG.d_stmt THEN
5554 PO_LOG.stmt(d_mod,l_progress,'Before Check_Uda_Enabled');
5555 END IF;
5556
5557 PO_CLM_CLO_UTIL.Check_Uda_Enabled
5558 (
5559 p_functional_area_code => l_functional_area_code
5560 ,p_document_type_code => l_document_type_code
5561 ,p_document_style_id => l_document_style_id
5562 ,x_enabled_flag => l_enabled_flag
5563 ,x_return_status => l_return_status
5564 ,x_errorcode => l_errorcode
5565 ,x_msg_count => l_msg_count
5566 ,x_msg_data => l_msg_data
5567 );
5568
5569 l_progress := '020';
5570 IF PO_LOG.d_stmt THEN
5571 PO_LOG.stmt(d_mod,l_progress,'After Check_Uda_Enabled with x_return_status : ' || l_return_status);
5572 END IF;
5573
5574 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
5575 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5576 END IF;
5577 -- End Check if Document is UDA Enabled
5578
5579 -- Archive UDA data only for UDA_enabled Docs, and only for DocTypes PO and PA
5580 IF l_enabled_flag = 'Y' AND p_document_type IN ('PO', 'PA') THEN
5581 -- Start PO_HEADERS_ALL_EXT_B_ARCHIVE
5582 l_progress := '030';
5583 IF PO_LOG.d_stmt THEN
5584 PO_LOG.stmt(d_mod,l_progress,'Archive PO_HEADERS_ALL_EXT_B');
5585 END IF;
5586
5587 --<PAR Project> : Setting the latest_external_flag to N for all old records.
5588 UPDATE PO_HEADERS_ALL_EXT_B_ARCHIVE
5589 SET latest_external_flag = 'N'
5590 WHERE po_header_id = p_po_header_id
5591 AND latest_external_flag = 'Y';
5592
5593 INSERT INTO PO_HEADERS_ALL_EXT_B_ARCHIVE
5594 (EXTENSION_ID
5595 ,ATTR_GROUP_ID
5596 ,PO_HEADER_ID
5597 ,DATA_LEVEL_ID
5598 ,PK1_VALUE
5599 ,PK2_VALUE
5600 ,PK3_VALUE
5601 ,PK4_VALUE
5602 ,PK5_VALUE
5603 ,LAST_UPDATE_DATE
5604 ,LAST_UPDATED_BY
5605 ,LAST_UPDATE_LOGIN
5606 ,CREATED_BY
5607 ,CREATION_DATE
5608 ,C_EXT_ATTR1
5609 ,C_EXT_ATTR2
5610 ,C_EXT_ATTR3
5611 ,C_EXT_ATTR4
5612 ,C_EXT_ATTR5
5613 ,C_EXT_ATTR6
5614 ,C_EXT_ATTR7
5615 ,C_EXT_ATTR8
5616 ,C_EXT_ATTR9
5617 ,C_EXT_ATTR10
5618 ,C_EXT_ATTR11
5619 ,C_EXT_ATTR12
5620 ,C_EXT_ATTR13
5621 ,C_EXT_ATTR14
5622 ,C_EXT_ATTR15
5623 ,C_EXT_ATTR16
5624 ,C_EXT_ATTR17
5625 ,C_EXT_ATTR18
5626 ,C_EXT_ATTR19
5627 ,C_EXT_ATTR20
5628 ,C_EXT_ATTR21
5629 ,C_EXT_ATTR22
5630 ,C_EXT_ATTR23
5631 ,C_EXT_ATTR24
5632 ,C_EXT_ATTR25
5633 ,C_EXT_ATTR26
5634 ,C_EXT_ATTR27
5635 ,C_EXT_ATTR28
5636 ,C_EXT_ATTR29
5637 ,C_EXT_ATTR30
5638 ,C_EXT_ATTR31
5639 ,C_EXT_ATTR32
5640 ,C_EXT_ATTR33
5641 ,C_EXT_ATTR34
5642 ,C_EXT_ATTR35
5643 ,C_EXT_ATTR36
5644 ,C_EXT_ATTR37
5645 ,C_EXT_ATTR38
5646 ,C_EXT_ATTR39
5647 ,C_EXT_ATTR40
5648 ,N_EXT_ATTR1
5649 ,N_EXT_ATTR2
5650 ,N_EXT_ATTR3
5651 ,N_EXT_ATTR4
5652 ,N_EXT_ATTR5
5653 ,N_EXT_ATTR6
5654 ,N_EXT_ATTR7
5655 ,N_EXT_ATTR8
5656 ,N_EXT_ATTR9
5657 ,N_EXT_ATTR10
5658 ,N_EXT_ATTR11
5659 ,N_EXT_ATTR12
5660 ,N_EXT_ATTR13
5661 ,N_EXT_ATTR14
5662 ,N_EXT_ATTR15
5663 ,N_EXT_ATTR16
5664 ,N_EXT_ATTR17
5665 ,N_EXT_ATTR18
5666 ,N_EXT_ATTR19
5667 ,N_EXT_ATTR20
5668 ,UOM_EXT_ATTR1
5669 ,UOM_EXT_ATTR2
5670 ,UOM_EXT_ATTR3
5671 ,UOM_EXT_ATTR4
5672 ,UOM_EXT_ATTR5
5673 ,UOM_EXT_ATTR6
5674 ,UOM_EXT_ATTR7
5675 ,UOM_EXT_ATTR8
5676 ,UOM_EXT_ATTR9
5677 ,UOM_EXT_ATTR10
5678 ,UOM_EXT_ATTR11
5679 ,UOM_EXT_ATTR12
5680 ,UOM_EXT_ATTR13
5681 ,UOM_EXT_ATTR14
5682 ,UOM_EXT_ATTR15
5683 ,UOM_EXT_ATTR16
5684 ,UOM_EXT_ATTR17
5685 ,UOM_EXT_ATTR18
5686 ,UOM_EXT_ATTR19
5687 ,UOM_EXT_ATTR20
5688 ,D_EXT_ATTR1
5689 ,D_EXT_ATTR2
5690 ,D_EXT_ATTR3
5691 ,D_EXT_ATTR4
5692 ,D_EXT_ATTR5
5693 ,D_EXT_ATTR6
5694 ,D_EXT_ATTR7
5695 ,D_EXT_ATTR8
5696 ,D_EXT_ATTR9
5697 ,D_EXT_ATTR10
5698 ,UDA_TEMPLATE_ID
5699 ,DRAFT_ID
5700 ,REVISION_NUM
5701 ,LATEST_EXTERNAL_FLAG
5702 )
5703 SELECT
5704 EXTENSION_ID
5705 ,ATTR_GROUP_ID
5706 ,PO_HEADER_ID
5707 ,DATA_LEVEL_ID
5708 ,PK1_VALUE
5709 ,PK2_VALUE
5710 ,PK3_VALUE
5711 ,PK4_VALUE
5712 ,PK5_VALUE
5713 ,LAST_UPDATE_DATE
5714 ,LAST_UPDATED_BY
5715 ,LAST_UPDATE_LOGIN
5716 ,CREATED_BY
5717 ,CREATION_DATE
5718 ,C_EXT_ATTR1
5719 ,C_EXT_ATTR2
5720 ,C_EXT_ATTR3
5721 ,C_EXT_ATTR4
5722 ,C_EXT_ATTR5
5723 ,C_EXT_ATTR6
5724 ,C_EXT_ATTR7
5725 ,C_EXT_ATTR8
5726 ,C_EXT_ATTR9
5727 ,C_EXT_ATTR10
5728 ,C_EXT_ATTR11
5729 ,C_EXT_ATTR12
5730 ,C_EXT_ATTR13
5731 ,C_EXT_ATTR14
5732 ,C_EXT_ATTR15
5733 ,C_EXT_ATTR16
5734 ,C_EXT_ATTR17
5735 ,C_EXT_ATTR18
5736 ,C_EXT_ATTR19
5737 ,C_EXT_ATTR20
5738 ,C_EXT_ATTR21
5739 ,C_EXT_ATTR22
5740 ,C_EXT_ATTR23
5741 ,C_EXT_ATTR24
5742 ,C_EXT_ATTR25
5743 ,C_EXT_ATTR26
5744 ,C_EXT_ATTR27
5745 ,C_EXT_ATTR28
5746 ,C_EXT_ATTR29
5747 ,C_EXT_ATTR30
5748 ,C_EXT_ATTR31
5749 ,C_EXT_ATTR32
5750 ,C_EXT_ATTR33
5751 ,C_EXT_ATTR34
5752 ,C_EXT_ATTR35
5753 ,C_EXT_ATTR36
5754 ,C_EXT_ATTR37
5755 ,C_EXT_ATTR38
5756 ,C_EXT_ATTR39
5757 ,C_EXT_ATTR40
5758 ,N_EXT_ATTR1
5759 ,N_EXT_ATTR2
5760 ,N_EXT_ATTR3
5761 ,N_EXT_ATTR4
5762 ,N_EXT_ATTR5
5763 ,N_EXT_ATTR6
5764 ,N_EXT_ATTR7
5765 ,N_EXT_ATTR8
5766 ,N_EXT_ATTR9
5767 ,N_EXT_ATTR10
5768 ,N_EXT_ATTR11
5769 ,N_EXT_ATTR12
5770 ,N_EXT_ATTR13
5771 ,N_EXT_ATTR14
5772 ,N_EXT_ATTR15
5773 ,N_EXT_ATTR16
5774 ,N_EXT_ATTR17
5775 ,N_EXT_ATTR18
5776 ,N_EXT_ATTR19
5777 ,N_EXT_ATTR20
5778 ,UOM_EXT_ATTR1
5779 ,UOM_EXT_ATTR2
5780 ,UOM_EXT_ATTR3
5781 ,UOM_EXT_ATTR4
5782 ,UOM_EXT_ATTR5
5783 ,UOM_EXT_ATTR6
5784 ,UOM_EXT_ATTR7
5785 ,UOM_EXT_ATTR8
5786 ,UOM_EXT_ATTR9
5787 ,UOM_EXT_ATTR10
5788 ,UOM_EXT_ATTR11
5789 ,UOM_EXT_ATTR12
5790 ,UOM_EXT_ATTR13
5791 ,UOM_EXT_ATTR14
5792 ,UOM_EXT_ATTR15
5793 ,UOM_EXT_ATTR16
5794 ,UOM_EXT_ATTR17
5795 ,UOM_EXT_ATTR18
5796 ,UOM_EXT_ATTR19
5797 ,UOM_EXT_ATTR20
5798 ,D_EXT_ATTR1
5799 ,D_EXT_ATTR2
5800 ,D_EXT_ATTR3
5801 ,D_EXT_ATTR4
5802 ,D_EXT_ATTR5
5803 ,D_EXT_ATTR6
5804 ,D_EXT_ATTR7
5805 ,D_EXT_ATTR8
5806 ,D_EXT_ATTR9
5807 ,D_EXT_ATTR10
5808 ,UDA_TEMPLATE_ID
5809 ,DRAFT_ID
5810 ,p_revision_num
5811 ,'Y' --<PAR Project>
5812 FROM PO_HEADERS_ALL_EXT_B
5813 WHERE po_header_id = p_po_header_id
5814 AND draft_id = -1;
5815
5816 l_progress := '040';
5817 IF PO_LOG.d_stmt THEN
5818 PO_LOG.stmt(d_mod,l_progress,'Number of rows inserted: '||SQL%rowcount);
5819 END IF;
5820 -- End PO_HEADERS_ALL_EXT_B_ARCHIVE
5821
5822 -- Start PO_HEADERS_ALL_EXT_TL_ARCHIVE
5823 l_progress := '050';
5824 IF PO_LOG.d_stmt THEN
5825 PO_LOG.stmt(d_mod,l_progress,'Archive PO_HEADERS_ALL_EXT_TL');
5826 END IF;
5827
5828 INSERT INTO PO_HEADERS_ALL_EXT_TL_ARCHIVE
5829 (EXTENSION_ID
5830 ,ATTR_GROUP_ID
5831 ,PO_HEADER_ID
5832 ,DATA_LEVEL_ID
5833 ,PK1_VALUE
5834 ,PK2_VALUE
5835 ,PK3_VALUE
5836 ,PK4_VALUE
5837 ,PK5_VALUE
5838 ,SOURCE_LANG
5839 ,LANGUAGE
5840 ,LAST_UPDATE_DATE
5841 ,LAST_UPDATED_BY
5842 ,LAST_UPDATE_LOGIN
5843 ,CREATED_BY
5844 ,CREATION_DATE
5845 ,TL_EXT_ATTR1
5846 ,TL_EXT_ATTR2
5847 ,TL_EXT_ATTR3
5848 ,TL_EXT_ATTR4
5849 ,TL_EXT_ATTR5
5850 ,TL_EXT_ATTR6
5851 ,TL_EXT_ATTR7
5852 ,TL_EXT_ATTR8
5853 ,TL_EXT_ATTR9
5854 ,TL_EXT_ATTR10
5855 ,TL_EXT_ATTR11
5856 ,TL_EXT_ATTR12
5857 ,TL_EXT_ATTR13
5858 ,TL_EXT_ATTR14
5859 ,TL_EXT_ATTR15
5860 ,TL_EXT_ATTR16
5861 ,TL_EXT_ATTR17
5862 ,TL_EXT_ATTR18
5863 ,TL_EXT_ATTR19
5864 ,TL_EXT_ATTR20
5865 ,TL_EXT_ATTR21
5866 ,TL_EXT_ATTR22
5867 ,TL_EXT_ATTR23
5868 ,TL_EXT_ATTR24
5869 ,TL_EXT_ATTR25
5870 ,TL_EXT_ATTR26
5871 ,TL_EXT_ATTR27
5872 ,TL_EXT_ATTR28
5873 ,TL_EXT_ATTR29
5874 ,TL_EXT_ATTR30
5875 ,TL_EXT_ATTR31
5876 ,TL_EXT_ATTR32
5877 ,TL_EXT_ATTR33
5878 ,TL_EXT_ATTR34
5879 ,TL_EXT_ATTR35
5880 ,TL_EXT_ATTR36
5881 ,TL_EXT_ATTR37
5882 ,TL_EXT_ATTR38
5883 ,TL_EXT_ATTR39
5884 ,TL_EXT_ATTR40
5885 ,UDA_TEMPLATE_ID
5886 ,DRAFT_ID
5887 ,REVISION_NUM
5888 ,LATEST_EXTERNAL_FLAG
5889 )
5890 SELECT
5891 EXTENSION_ID
5892 ,ATTR_GROUP_ID
5893 ,PO_HEADER_ID
5894 ,DATA_LEVEL_ID
5895 ,PK1_VALUE
5896 ,PK2_VALUE
5897 ,PK3_VALUE
5898 ,PK4_VALUE
5899 ,PK5_VALUE
5900 ,SOURCE_LANG
5901 ,LANGUAGE
5902 ,LAST_UPDATE_DATE
5903 ,LAST_UPDATED_BY
5904 ,LAST_UPDATE_LOGIN
5905 ,CREATED_BY
5906 ,CREATION_DATE
5907 ,TL_EXT_ATTR1
5908 ,TL_EXT_ATTR2
5909 ,TL_EXT_ATTR3
5910 ,TL_EXT_ATTR4
5911 ,TL_EXT_ATTR5
5912 ,TL_EXT_ATTR6
5913 ,TL_EXT_ATTR7
5914 ,TL_EXT_ATTR8
5915 ,TL_EXT_ATTR9
5916 ,TL_EXT_ATTR10
5917 ,TL_EXT_ATTR11
5918 ,TL_EXT_ATTR12
5919 ,TL_EXT_ATTR13
5920 ,TL_EXT_ATTR14
5921 ,TL_EXT_ATTR15
5922 ,TL_EXT_ATTR16
5923 ,TL_EXT_ATTR17
5924 ,TL_EXT_ATTR18
5925 ,TL_EXT_ATTR19
5926 ,TL_EXT_ATTR20
5927 ,TL_EXT_ATTR21
5928 ,TL_EXT_ATTR22
5929 ,TL_EXT_ATTR23
5930 ,TL_EXT_ATTR24
5931 ,TL_EXT_ATTR25
5932 ,TL_EXT_ATTR26
5933 ,TL_EXT_ATTR27
5934 ,TL_EXT_ATTR28
5935 ,TL_EXT_ATTR29
5936 ,TL_EXT_ATTR30
5937 ,TL_EXT_ATTR31
5938 ,TL_EXT_ATTR32
5939 ,TL_EXT_ATTR33
5940 ,TL_EXT_ATTR34
5941 ,TL_EXT_ATTR35
5942 ,TL_EXT_ATTR36
5943 ,TL_EXT_ATTR37
5944 ,TL_EXT_ATTR38
5945 ,TL_EXT_ATTR39
5946 ,TL_EXT_ATTR40
5947 ,UDA_TEMPLATE_ID
5948 ,DRAFT_ID
5949 ,p_revision_num
5950 ,NULL
5951 FROM PO_HEADERS_ALL_EXT_TL
5952 WHERE po_header_id = p_po_header_id
5953 AND draft_id = -1;
5954
5955 l_progress := '060';
5956 IF PO_LOG.d_stmt THEN
5957 PO_LOG.stmt(d_mod,l_progress,'Number of rows inserted: '||SQL%rowcount);
5958 END IF;
5959 -- End PO_HEADERS_ALL_EXT_TL_ARCHIVE
5960
5961 -- Archive UDA Line and Line Locations data only for PO or PA BLANKETS
5962 IF (p_document_type = 'PO') OR
5963 (p_document_type = 'PA' AND p_document_subtype='BLANKET') THEN
5964
5965 -- Start PO_LINES_ALL_EXT_B_ARCHIVE
5966 l_progress := '070';
5967 IF PO_LOG.d_stmt THEN
5968 PO_LOG.stmt(d_mod,l_progress,'Archive PO_LINES_ALL_EXT_B');
5969 END IF;
5970
5971 INSERT INTO PO_LINES_ALL_EXT_B_ARCHIVE
5972 (EXTENSION_ID
5973 ,ATTR_GROUP_ID
5974 ,PO_LINE_ID
5975 ,DATA_LEVEL_ID
5976 ,PK1_VALUE
5977 ,PK2_VALUE
5978 ,PK3_VALUE
5979 ,PK4_VALUE
5980 ,PK5_VALUE
5981 ,LAST_UPDATE_DATE
5982 ,LAST_UPDATED_BY
5983 ,LAST_UPDATE_LOGIN
5984 ,CREATED_BY
5985 ,CREATION_DATE
5986 ,C_EXT_ATTR1
5987 ,C_EXT_ATTR2
5988 ,C_EXT_ATTR3
5989 ,C_EXT_ATTR4
5990 ,C_EXT_ATTR5
5991 ,C_EXT_ATTR6
5992 ,C_EXT_ATTR7
5993 ,C_EXT_ATTR8
5994 ,C_EXT_ATTR9
5995 ,C_EXT_ATTR10
5996 ,C_EXT_ATTR11
5997 ,C_EXT_ATTR12
5998 ,C_EXT_ATTR13
5999 ,C_EXT_ATTR14
6000 ,C_EXT_ATTR15
6001 ,C_EXT_ATTR16
6002 ,C_EXT_ATTR17
6003 ,C_EXT_ATTR18
6004 ,C_EXT_ATTR19
6005 ,C_EXT_ATTR20
6006 ,C_EXT_ATTR21
6007 ,C_EXT_ATTR22
6008 ,C_EXT_ATTR23
6009 ,C_EXT_ATTR24
6010 ,C_EXT_ATTR25
6011 ,C_EXT_ATTR26
6012 ,C_EXT_ATTR27
6013 ,C_EXT_ATTR28
6014 ,C_EXT_ATTR29
6015 ,C_EXT_ATTR30
6016 ,C_EXT_ATTR31
6017 ,C_EXT_ATTR32
6018 ,C_EXT_ATTR33
6019 ,C_EXT_ATTR34
6020 ,C_EXT_ATTR35
6021 ,C_EXT_ATTR36
6022 ,C_EXT_ATTR37
6023 ,C_EXT_ATTR38
6024 ,C_EXT_ATTR39
6025 ,C_EXT_ATTR40
6026 ,N_EXT_ATTR1
6027 ,N_EXT_ATTR2
6028 ,N_EXT_ATTR3
6029 ,N_EXT_ATTR4
6030 ,N_EXT_ATTR5
6031 ,N_EXT_ATTR6
6032 ,N_EXT_ATTR7
6033 ,N_EXT_ATTR8
6034 ,N_EXT_ATTR9
6035 ,N_EXT_ATTR10
6036 ,N_EXT_ATTR11
6037 ,N_EXT_ATTR12
6038 ,N_EXT_ATTR13
6039 ,N_EXT_ATTR14
6040 ,N_EXT_ATTR15
6041 ,N_EXT_ATTR16
6042 ,N_EXT_ATTR17
6043 ,N_EXT_ATTR18
6044 ,N_EXT_ATTR19
6045 ,N_EXT_ATTR20
6046 ,UOM_EXT_ATTR1
6047 ,UOM_EXT_ATTR2
6048 ,UOM_EXT_ATTR3
6049 ,UOM_EXT_ATTR4
6050 ,UOM_EXT_ATTR5
6051 ,UOM_EXT_ATTR6
6052 ,UOM_EXT_ATTR7
6053 ,UOM_EXT_ATTR8
6054 ,UOM_EXT_ATTR9
6055 ,UOM_EXT_ATTR10
6056 ,UOM_EXT_ATTR11
6057 ,UOM_EXT_ATTR12
6058 ,UOM_EXT_ATTR13
6059 ,UOM_EXT_ATTR14
6060 ,UOM_EXT_ATTR15
6061 ,UOM_EXT_ATTR16
6062 ,UOM_EXT_ATTR17
6063 ,UOM_EXT_ATTR18
6064 ,UOM_EXT_ATTR19
6065 ,UOM_EXT_ATTR20
6066 ,D_EXT_ATTR1
6067 ,D_EXT_ATTR2
6068 ,D_EXT_ATTR3
6069 ,D_EXT_ATTR4
6070 ,D_EXT_ATTR5
6071 ,D_EXT_ATTR6
6072 ,D_EXT_ATTR7
6073 ,D_EXT_ATTR8
6074 ,D_EXT_ATTR9
6075 ,D_EXT_ATTR10
6076 ,UDA_TEMPLATE_ID
6077 ,DRAFT_ID
6078 ,REVISION_NUM
6079 ,LATEST_EXTERNAL_FLAG
6080 )
6081 SELECT
6082 POLE.EXTENSION_ID
6083 ,POLE.ATTR_GROUP_ID
6084 ,POLE.PO_LINE_ID
6085 ,POLE.DATA_LEVEL_ID
6086 ,POLE.PK1_VALUE
6087 ,POLE.PK2_VALUE
6088 ,POLE.PK3_VALUE
6089 ,POLE.PK4_VALUE
6090 ,POLE.PK5_VALUE
6091 ,POLE.LAST_UPDATE_DATE
6092 ,POLE.LAST_UPDATED_BY
6093 ,POLE.LAST_UPDATE_LOGIN
6094 ,POLE.CREATED_BY
6095 ,POLE.CREATION_DATE
6096 ,POLE.C_EXT_ATTR1
6097 ,POLE.C_EXT_ATTR2
6098 ,POLE.C_EXT_ATTR3
6099 ,POLE.C_EXT_ATTR4
6100 ,POLE.C_EXT_ATTR5
6101 ,POLE.C_EXT_ATTR6
6102 ,POLE.C_EXT_ATTR7
6103 ,POLE.C_EXT_ATTR8
6104 ,POLE.C_EXT_ATTR9
6105 ,POLE.C_EXT_ATTR10
6106 ,POLE.C_EXT_ATTR11
6107 ,POLE.C_EXT_ATTR12
6108 ,POLE.C_EXT_ATTR13
6109 ,POLE.C_EXT_ATTR14
6110 ,POLE.C_EXT_ATTR15
6111 ,POLE.C_EXT_ATTR16
6112 ,POLE.C_EXT_ATTR17
6113 ,POLE.C_EXT_ATTR18
6114 ,POLE.C_EXT_ATTR19
6115 ,POLE.C_EXT_ATTR20
6116 ,POLE.C_EXT_ATTR21
6117 ,POLE.C_EXT_ATTR22
6118 ,POLE.C_EXT_ATTR23
6119 ,POLE.C_EXT_ATTR24
6120 ,POLE.C_EXT_ATTR25
6121 ,POLE.C_EXT_ATTR26
6122 ,POLE.C_EXT_ATTR27
6123 ,POLE.C_EXT_ATTR28
6124 ,POLE.C_EXT_ATTR29
6125 ,POLE.C_EXT_ATTR30
6126 ,POLE.C_EXT_ATTR31
6127 ,POLE.C_EXT_ATTR32
6128 ,POLE.C_EXT_ATTR33
6129 ,POLE.C_EXT_ATTR34
6130 ,POLE.C_EXT_ATTR35
6131 ,POLE.C_EXT_ATTR36
6132 ,POLE.C_EXT_ATTR37
6133 ,POLE.C_EXT_ATTR38
6134 ,POLE.C_EXT_ATTR39
6135 ,POLE.C_EXT_ATTR40
6136 ,POLE.N_EXT_ATTR1
6137 ,POLE.N_EXT_ATTR2
6138 ,POLE.N_EXT_ATTR3
6139 ,POLE.N_EXT_ATTR4
6140 ,POLE.N_EXT_ATTR5
6141 ,POLE.N_EXT_ATTR6
6142 ,POLE.N_EXT_ATTR7
6143 ,POLE.N_EXT_ATTR8
6144 ,POLE.N_EXT_ATTR9
6145 ,POLE.N_EXT_ATTR10
6146 ,POLE.N_EXT_ATTR11
6147 ,POLE.N_EXT_ATTR12
6148 ,POLE.N_EXT_ATTR13
6149 ,POLE.N_EXT_ATTR14
6150 ,POLE.N_EXT_ATTR15
6151 ,POLE.N_EXT_ATTR16
6152 ,POLE.N_EXT_ATTR17
6153 ,POLE.N_EXT_ATTR18
6154 ,POLE.N_EXT_ATTR19
6155 ,POLE.N_EXT_ATTR20
6156 ,POLE.UOM_EXT_ATTR1
6157 ,POLE.UOM_EXT_ATTR2
6158 ,POLE.UOM_EXT_ATTR3
6159 ,POLE.UOM_EXT_ATTR4
6160 ,POLE.UOM_EXT_ATTR5
6161 ,POLE.UOM_EXT_ATTR6
6162 ,POLE.UOM_EXT_ATTR7
6163 ,POLE.UOM_EXT_ATTR8
6164 ,POLE.UOM_EXT_ATTR9
6165 ,POLE.UOM_EXT_ATTR10
6166 ,POLE.UOM_EXT_ATTR11
6167 ,POLE.UOM_EXT_ATTR12
6168 ,POLE.UOM_EXT_ATTR13
6169 ,POLE.UOM_EXT_ATTR14
6170 ,POLE.UOM_EXT_ATTR15
6171 ,POLE.UOM_EXT_ATTR16
6172 ,POLE.UOM_EXT_ATTR17
6173 ,POLE.UOM_EXT_ATTR18
6174 ,POLE.UOM_EXT_ATTR19
6175 ,POLE.UOM_EXT_ATTR20
6176 ,POLE.D_EXT_ATTR1
6177 ,POLE.D_EXT_ATTR2
6178 ,POLE.D_EXT_ATTR3
6179 ,POLE.D_EXT_ATTR4
6180 ,POLE.D_EXT_ATTR5
6181 ,POLE.D_EXT_ATTR6
6182 ,POLE.D_EXT_ATTR7
6183 ,POLE.D_EXT_ATTR8
6184 ,POLE.D_EXT_ATTR9
6185 ,POLE.D_EXT_ATTR10
6186 ,POLE.UDA_TEMPLATE_ID
6187 ,POLE.DRAFT_ID
6188 ,p_revision_num
6189 ,'Y'
6190 FROM PO_LINES_ALL_EXT_B POLE,
6191 PO_LINES_ALL POL, PO_LINES_ARCHIVE_ALL POLA
6192 WHERE POL.po_header_id = p_po_header_id
6193 AND POL.po_line_id = POLE.po_line_id
6194 AND POL.po_line_id = POLA.po_line_id (+)
6195 AND Nvl(POL.revision_num,0) = p_revision_num
6196 AND POL.revision_num <> POLA.revision_num (+)
6197 AND POLA.latest_external_flag (+) = 'Y'
6198 AND POLE.draft_id = -1
6199 AND POLE.pk1_value IS NULL;
6200
6201 --<PAR Project Starts>
6202 IF (SQL%ROWCOUNT > 0) THEN
6203
6204 l_progress := '080';
6205 IF PO_LOG.d_stmt THEN
6206 PO_LOG.stmt(d_mod,l_progress,'Number of rows inserted: '||SQL%rowcount);
6207 PO_LOG.stmt(d_mod,l_progress,'Update PO_LINES_ALL_EXT_B_ARCHIVE to reset latest_external_flag');
6208 END IF;
6209
6210 -- If a row was inserted into PO_LINES_ARCHIVE, then set the appropriate flags
6211 UPDATE PO_LINES_ALL_EXT_B_ARCHIVE POL1
6212 SET latest_external_flag = 'N'
6213 WHERE latest_external_flag = 'Y'
6214 AND revision_num < p_revision_num
6215 AND EXISTS
6216 (SELECT 'A new archived row'
6217 FROM PO_LINES_ALL_EXT_B_ARCHIVE POL2, PO_LINES_ARCHIVE_ALL PLA
6218 WHERE PLA.po_header_id = p_po_header_id
6219 AND PLA.po_line_id = POL1.po_line_id
6220 AND POL2.po_line_id = POL1.po_line_id
6221 AND POL2.latest_external_flag = 'Y'
6222 AND POL2.revision_num = p_revision_num
6223 AND POL2.attr_group_id = POL1.attr_group_id);
6224
6225 IF PO_LOG.d_stmt THEN
6226 PO_LOG.stmt(d_mod,l_progress,'Number of rows updated: '||SQL%rowcount);
6227 END IF;
6228 END IF;
6229 --<PAR Project Ends >
6230 -- End PO_LINES_ALL_EXT_B_ARCHIVE
6231
6232 -- Start PO_LINES_ALL_EXT_TL_ARCHIVE
6233 l_progress := '090';
6234 IF PO_LOG.d_stmt THEN
6235 PO_LOG.stmt(d_mod,l_progress,'Archive PO_LINES_ALL_EXT_TL');
6236 END IF;
6237
6238 INSERT INTO PO_LINES_ALL_EXT_TL_ARCHIVE
6239 (EXTENSION_ID
6240 ,ATTR_GROUP_ID
6241 ,PO_LINE_ID
6242 ,DATA_LEVEL_ID
6243 ,PK1_VALUE
6244 ,PK2_VALUE
6245 ,PK3_VALUE
6246 ,PK4_VALUE
6247 ,PK5_VALUE
6248 ,SOURCE_LANG
6249 ,LANGUAGE
6250 ,LAST_UPDATE_DATE
6251 ,LAST_UPDATED_BY
6252 ,LAST_UPDATE_LOGIN
6253 ,CREATED_BY
6254 ,CREATION_DATE
6255 ,TL_EXT_ATTR1
6256 ,TL_EXT_ATTR2
6257 ,TL_EXT_ATTR3
6258 ,TL_EXT_ATTR4
6259 ,TL_EXT_ATTR5
6260 ,TL_EXT_ATTR6
6261 ,TL_EXT_ATTR7
6262 ,TL_EXT_ATTR8
6263 ,TL_EXT_ATTR9
6264 ,TL_EXT_ATTR10
6265 ,TL_EXT_ATTR11
6266 ,TL_EXT_ATTR12
6267 ,TL_EXT_ATTR13
6268 ,TL_EXT_ATTR14
6269 ,TL_EXT_ATTR15
6270 ,TL_EXT_ATTR16
6271 ,TL_EXT_ATTR17
6272 ,TL_EXT_ATTR18
6273 ,TL_EXT_ATTR19
6274 ,TL_EXT_ATTR20
6275 ,TL_EXT_ATTR21
6276 ,TL_EXT_ATTR22
6277 ,TL_EXT_ATTR23
6278 ,TL_EXT_ATTR24
6279 ,TL_EXT_ATTR25
6280 ,TL_EXT_ATTR26
6281 ,TL_EXT_ATTR27
6282 ,TL_EXT_ATTR28
6283 ,TL_EXT_ATTR29
6284 ,TL_EXT_ATTR30
6285 ,TL_EXT_ATTR31
6286 ,TL_EXT_ATTR32
6287 ,TL_EXT_ATTR33
6288 ,TL_EXT_ATTR34
6289 ,TL_EXT_ATTR35
6290 ,TL_EXT_ATTR36
6291 ,TL_EXT_ATTR37
6292 ,TL_EXT_ATTR38
6293 ,TL_EXT_ATTR39
6294 ,TL_EXT_ATTR40
6295 ,UDA_TEMPLATE_ID
6296 ,DRAFT_ID
6297 ,REVISION_NUM
6298 ,LATEST_EXTERNAL_FLAG
6299 )
6300 SELECT
6301 POLE.EXTENSION_ID
6302 ,POLE.ATTR_GROUP_ID
6303 ,POLE.PO_LINE_ID
6304 ,POLE.DATA_LEVEL_ID
6305 ,POLE.PK1_VALUE
6306 ,POLE.PK2_VALUE
6307 ,POLE.PK3_VALUE
6308 ,POLE.PK4_VALUE
6309 ,POLE.PK5_VALUE
6310 ,POLE.SOURCE_LANG
6311 ,POLE.LANGUAGE
6312 ,POLE.LAST_UPDATE_DATE
6313 ,POLE.LAST_UPDATED_BY
6314 ,POLE.LAST_UPDATE_LOGIN
6315 ,POLE.CREATED_BY
6316 ,POLE.CREATION_DATE
6317 ,POLE.TL_EXT_ATTR1
6318 ,POLE.TL_EXT_ATTR2
6319 ,POLE.TL_EXT_ATTR3
6320 ,POLE.TL_EXT_ATTR4
6321 ,POLE.TL_EXT_ATTR5
6322 ,POLE.TL_EXT_ATTR6
6323 ,POLE.TL_EXT_ATTR7
6324 ,POLE.TL_EXT_ATTR8
6325 ,POLE.TL_EXT_ATTR9
6326 ,POLE.TL_EXT_ATTR10
6327 ,POLE.TL_EXT_ATTR11
6328 ,POLE.TL_EXT_ATTR12
6329 ,POLE.TL_EXT_ATTR13
6330 ,POLE.TL_EXT_ATTR14
6331 ,POLE.TL_EXT_ATTR15
6332 ,POLE.TL_EXT_ATTR16
6333 ,POLE.TL_EXT_ATTR17
6334 ,POLE.TL_EXT_ATTR18
6335 ,POLE.TL_EXT_ATTR19
6336 ,POLE.TL_EXT_ATTR20
6337 ,POLE.TL_EXT_ATTR21
6338 ,POLE.TL_EXT_ATTR22
6339 ,POLE.TL_EXT_ATTR23
6340 ,POLE.TL_EXT_ATTR24
6341 ,POLE.TL_EXT_ATTR25
6342 ,POLE.TL_EXT_ATTR26
6343 ,POLE.TL_EXT_ATTR27
6344 ,POLE.TL_EXT_ATTR28
6345 ,POLE.TL_EXT_ATTR29
6346 ,POLE.TL_EXT_ATTR30
6347 ,POLE.TL_EXT_ATTR31
6348 ,POLE.TL_EXT_ATTR32
6349 ,POLE.TL_EXT_ATTR33
6350 ,POLE.TL_EXT_ATTR34
6351 ,POLE.TL_EXT_ATTR35
6352 ,POLE.TL_EXT_ATTR36
6353 ,POLE.TL_EXT_ATTR37
6354 ,POLE.TL_EXT_ATTR38
6355 ,POLE.TL_EXT_ATTR39
6356 ,POLE.TL_EXT_ATTR40
6357 ,POLE.UDA_TEMPLATE_ID
6358 ,POLE.DRAFT_ID
6359 ,p_revision_num
6360 ,NULL
6361 FROM PO_LINES_ALL_EXT_TL POLE,
6362 PO_LINES_ALL POL
6363 WHERE POL.po_header_id = p_po_header_id
6364 AND POL.po_line_id = POLE.po_line_id
6365 AND POLE.draft_id = -1;
6366
6367 l_progress := '100';
6368 IF PO_LOG.d_stmt THEN
6369 PO_LOG.stmt(d_mod,l_progress,'Number of rows inserted: '||SQL%rowcount);
6370 END IF;
6371 -- End PO_LINES_ALL_EXT_TL_ARCHIVE
6372
6373 -- Start PO_LINE_LOC_ALL_EXT_B_ARCHIVE
6374 l_progress := '110';
6375 IF PO_LOG.d_stmt THEN
6376 PO_LOG.stmt(d_mod,l_progress,'Archive PO_LINE_LOCATIONS_ALL_EXT_B');
6377 END IF;
6378
6379 INSERT INTO PO_LINE_LOC_ALL_EXT_B_ARCHIVE
6380 (EXTENSION_ID
6381 ,ATTR_GROUP_ID
6382 ,LINE_LOCATION_ID
6383 ,DATA_LEVEL_ID
6384 ,PK1_VALUE
6385 ,PK2_VALUE
6386 ,PK3_VALUE
6387 ,PK4_VALUE
6388 ,PK5_VALUE
6389 ,LAST_UPDATE_DATE
6390 ,LAST_UPDATED_BY
6391 ,LAST_UPDATE_LOGIN
6392 ,CREATED_BY
6393 ,CREATION_DATE
6394 ,C_EXT_ATTR1
6395 ,C_EXT_ATTR2
6396 ,C_EXT_ATTR3
6397 ,C_EXT_ATTR4
6398 ,C_EXT_ATTR5
6399 ,C_EXT_ATTR6
6400 ,C_EXT_ATTR7
6401 ,C_EXT_ATTR8
6402 ,C_EXT_ATTR9
6403 ,C_EXT_ATTR10
6404 ,C_EXT_ATTR11
6405 ,C_EXT_ATTR12
6406 ,C_EXT_ATTR13
6407 ,C_EXT_ATTR14
6408 ,C_EXT_ATTR15
6409 ,C_EXT_ATTR16
6410 ,C_EXT_ATTR17
6411 ,C_EXT_ATTR18
6412 ,C_EXT_ATTR19
6413 ,C_EXT_ATTR20
6414 ,C_EXT_ATTR21
6415 ,C_EXT_ATTR22
6416 ,C_EXT_ATTR23
6417 ,C_EXT_ATTR24
6418 ,C_EXT_ATTR25
6419 ,C_EXT_ATTR26
6420 ,C_EXT_ATTR27
6421 ,C_EXT_ATTR28
6422 ,C_EXT_ATTR29
6423 ,C_EXT_ATTR30
6424 ,C_EXT_ATTR31
6425 ,C_EXT_ATTR32
6426 ,C_EXT_ATTR33
6427 ,C_EXT_ATTR34
6428 ,C_EXT_ATTR35
6429 ,C_EXT_ATTR36
6430 ,C_EXT_ATTR37
6431 ,C_EXT_ATTR38
6432 ,C_EXT_ATTR39
6433 ,C_EXT_ATTR40
6434 ,N_EXT_ATTR1
6435 ,N_EXT_ATTR2
6436 ,N_EXT_ATTR3
6437 ,N_EXT_ATTR4
6438 ,N_EXT_ATTR5
6439 ,N_EXT_ATTR6
6440 ,N_EXT_ATTR7
6441 ,N_EXT_ATTR8
6442 ,N_EXT_ATTR9
6443 ,N_EXT_ATTR10
6444 ,N_EXT_ATTR11
6445 ,N_EXT_ATTR12
6446 ,N_EXT_ATTR13
6447 ,N_EXT_ATTR14
6448 ,N_EXT_ATTR15
6449 ,N_EXT_ATTR16
6450 ,N_EXT_ATTR17
6451 ,N_EXT_ATTR18
6452 ,N_EXT_ATTR19
6453 ,N_EXT_ATTR20
6454 ,UOM_EXT_ATTR1
6455 ,UOM_EXT_ATTR2
6456 ,UOM_EXT_ATTR3
6457 ,UOM_EXT_ATTR4
6458 ,UOM_EXT_ATTR5
6459 ,UOM_EXT_ATTR6
6460 ,UOM_EXT_ATTR7
6461 ,UOM_EXT_ATTR8
6462 ,UOM_EXT_ATTR9
6463 ,UOM_EXT_ATTR10
6464 ,UOM_EXT_ATTR11
6465 ,UOM_EXT_ATTR12
6466 ,UOM_EXT_ATTR13
6467 ,UOM_EXT_ATTR14
6468 ,UOM_EXT_ATTR15
6469 ,UOM_EXT_ATTR16
6470 ,UOM_EXT_ATTR17
6471 ,UOM_EXT_ATTR18
6472 ,UOM_EXT_ATTR19
6473 ,UOM_EXT_ATTR20
6474 ,D_EXT_ATTR1
6475 ,D_EXT_ATTR2
6476 ,D_EXT_ATTR3
6477 ,D_EXT_ATTR4
6478 ,D_EXT_ATTR5
6479 ,D_EXT_ATTR6
6480 ,D_EXT_ATTR7
6481 ,D_EXT_ATTR8
6482 ,D_EXT_ATTR9
6483 ,D_EXT_ATTR10
6484 ,UDA_TEMPLATE_ID
6485 ,DRAFT_ID
6486 ,REVISION_NUM
6487 ,LATEST_EXTERNAL_FLAG
6488 )
6489 SELECT
6490 POLE.EXTENSION_ID
6491 ,POLE.ATTR_GROUP_ID
6492 ,POLE.LINE_LOCATION_ID
6493 ,POLE.DATA_LEVEL_ID
6494 ,POLE.PK1_VALUE
6495 ,POLE.PK2_VALUE
6496 ,POLE.PK3_VALUE
6497 ,POLE.PK4_VALUE
6498 ,POLE.PK5_VALUE
6499 ,POLE.LAST_UPDATE_DATE
6500 ,POLE.LAST_UPDATED_BY
6501 ,POLE.LAST_UPDATE_LOGIN
6502 ,POLE.CREATED_BY
6503 ,POLE.CREATION_DATE
6504 ,POLE.C_EXT_ATTR1
6505 ,POLE.C_EXT_ATTR2
6506 ,POLE.C_EXT_ATTR3
6507 ,POLE.C_EXT_ATTR4
6508 ,POLE.C_EXT_ATTR5
6509 ,POLE.C_EXT_ATTR6
6510 ,POLE.C_EXT_ATTR7
6511 ,POLE.C_EXT_ATTR8
6512 ,POLE.C_EXT_ATTR9
6513 ,POLE.C_EXT_ATTR10
6514 ,POLE.C_EXT_ATTR11
6515 ,POLE.C_EXT_ATTR12
6516 ,POLE.C_EXT_ATTR13
6517 ,POLE.C_EXT_ATTR14
6518 ,POLE.C_EXT_ATTR15
6519 ,POLE.C_EXT_ATTR16
6520 ,POLE.C_EXT_ATTR17
6521 ,POLE.C_EXT_ATTR18
6522 ,POLE.C_EXT_ATTR19
6523 ,POLE.C_EXT_ATTR20
6524 ,POLE.C_EXT_ATTR21
6525 ,POLE.C_EXT_ATTR22
6526 ,POLE.C_EXT_ATTR23
6527 ,POLE.C_EXT_ATTR24
6528 ,POLE.C_EXT_ATTR25
6529 ,POLE.C_EXT_ATTR26
6530 ,POLE.C_EXT_ATTR27
6531 ,POLE.C_EXT_ATTR28
6532 ,POLE.C_EXT_ATTR29
6533 ,POLE.C_EXT_ATTR30
6534 ,POLE.C_EXT_ATTR31
6535 ,POLE.C_EXT_ATTR32
6536 ,POLE.C_EXT_ATTR33
6537 ,POLE.C_EXT_ATTR34
6538 ,POLE.C_EXT_ATTR35
6539 ,POLE.C_EXT_ATTR36
6540 ,POLE.C_EXT_ATTR37
6541 ,POLE.C_EXT_ATTR38
6542 ,POLE.C_EXT_ATTR39
6543 ,POLE.C_EXT_ATTR40
6544 ,POLE.N_EXT_ATTR1
6545 ,POLE.N_EXT_ATTR2
6546 ,POLE.N_EXT_ATTR3
6547 ,POLE.N_EXT_ATTR4
6548 ,POLE.N_EXT_ATTR5
6549 ,POLE.N_EXT_ATTR6
6550 ,POLE.N_EXT_ATTR7
6551 ,POLE.N_EXT_ATTR8
6552 ,POLE.N_EXT_ATTR9
6553 ,POLE.N_EXT_ATTR10
6554 ,POLE.N_EXT_ATTR11
6555 ,POLE.N_EXT_ATTR12
6556 ,POLE.N_EXT_ATTR13
6557 ,POLE.N_EXT_ATTR14
6558 ,POLE.N_EXT_ATTR15
6559 ,POLE.N_EXT_ATTR16
6560 ,POLE.N_EXT_ATTR17
6561 ,POLE.N_EXT_ATTR18
6562 ,POLE.N_EXT_ATTR19
6563 ,POLE.N_EXT_ATTR20
6564 ,POLE.UOM_EXT_ATTR1
6565 ,POLE.UOM_EXT_ATTR2
6566 ,POLE.UOM_EXT_ATTR3
6567 ,POLE.UOM_EXT_ATTR4
6568 ,POLE.UOM_EXT_ATTR5
6569 ,POLE.UOM_EXT_ATTR6
6570 ,POLE.UOM_EXT_ATTR7
6571 ,POLE.UOM_EXT_ATTR8
6572 ,POLE.UOM_EXT_ATTR9
6573 ,POLE.UOM_EXT_ATTR10
6574 ,POLE.UOM_EXT_ATTR11
6575 ,POLE.UOM_EXT_ATTR12
6576 ,POLE.UOM_EXT_ATTR13
6577 ,POLE.UOM_EXT_ATTR14
6578 ,POLE.UOM_EXT_ATTR15
6579 ,POLE.UOM_EXT_ATTR16
6580 ,POLE.UOM_EXT_ATTR17
6581 ,POLE.UOM_EXT_ATTR18
6582 ,POLE.UOM_EXT_ATTR19
6583 ,POLE.UOM_EXT_ATTR20
6584 ,POLE.D_EXT_ATTR1
6585 ,POLE.D_EXT_ATTR2
6586 ,POLE.D_EXT_ATTR3
6587 ,POLE.D_EXT_ATTR4
6588 ,POLE.D_EXT_ATTR5
6589 ,POLE.D_EXT_ATTR6
6590 ,POLE.D_EXT_ATTR7
6591 ,POLE.D_EXT_ATTR8
6592 ,POLE.D_EXT_ATTR9
6593 ,POLE.D_EXT_ATTR10
6594 ,POLE.UDA_TEMPLATE_ID
6595 ,POLE.DRAFT_ID
6596 ,p_revision_num
6597 ,'Y' --<PAR Project>
6598 FROM PO_LINE_LOCATIONS_ALL_EXT_B POLE,
6599 PO_LINE_LOCATIONS_ALL POL, PO_LINE_LOCATIONS_ARCHIVE_ALL POLLA
6600 --<PAR Project Starts>
6601 WHERE POL.po_header_id = p_po_header_id
6602 AND POL.line_location_id = POLE.line_location_id
6603 AND POL.line_location_id = POLLA.line_location_id (+)
6604 AND Nvl(POL.revision_num,0) = p_revision_num
6605 AND POL.revision_num <> POLLA.revision_num (+)
6606 AND POLLA.latest_external_flag (+) = 'Y'
6607 AND POLE.draft_id = -1;
6608
6609 IF (SQL%ROWCOUNT > 0) THEN
6610
6611 l_progress := '120';
6612 IF PO_LOG.d_stmt THEN
6613 PO_LOG.stmt(d_mod,l_progress,'Number of rows inserted: '||SQL%rowcount);
6614 PO_LOG.stmt(d_mod,l_progress,'Update PO_LINES_ALL_EXT_B_ARCHIVE to reset latest_external_flag');
6615 END IF;
6616
6617 -- If a row was inserted into PO_LINES_ARCHIVE, then set the appropriate flags
6618 UPDATE PO_LINE_LOC_ALL_EXT_B_ARCHIVE POL1
6619 SET latest_external_flag = 'N'
6620 WHERE latest_external_flag = 'Y'
6621 AND revision_num < p_revision_num
6622 AND EXISTS
6623 (SELECT 'A new archived row'
6624 FROM PO_LINE_LOC_ALL_EXT_B_ARCHIVE POL2, PO_LINE_LOCATIONS_ARCHIVE_ALL PLA
6625 WHERE PLA.po_header_id = p_po_header_id
6626 AND PLA.line_location_id = POL1.line_location_id
6627 AND POL2.line_location_id = POL1.line_location_id
6628 AND POL2.latest_external_flag = 'Y'
6629 AND POL2.revision_num = p_revision_num
6630 AND POL2.attr_group_id = POL1.attr_group_id);
6631
6632 IF PO_LOG.d_stmt THEN
6633 PO_LOG.stmt(d_mod,l_progress,'Number of rows updated: '||SQL%rowcount);
6634 END IF;
6635 END IF;
6636 --<PAR Project Ends>
6637 -- End PO_LINE_LOC_ALL_EXT_B_ARCHIVE
6638
6639 -- Start PO_LINE_LOC_ALL_EXT_TL_ARCHIVE
6640 l_progress := '130';
6641 IF PO_LOG.d_stmt THEN
6642 PO_LOG.stmt(d_mod,l_progress,'Archive PO_LINE_LOCATIONS_ALL_EXT_TL');
6643 END IF;
6644
6645 INSERT INTO PO_LINE_LOC_ALL_EXT_TL_ARCHIVE
6646 (EXTENSION_ID
6647 ,ATTR_GROUP_ID
6648 ,LINE_LOCATION_ID
6649 ,DATA_LEVEL_ID
6650 ,PK1_VALUE
6651 ,PK2_VALUE
6652 ,PK3_VALUE
6653 ,PK4_VALUE
6654 ,PK5_VALUE
6655 ,SOURCE_LANG
6656 ,LANGUAGE
6657 ,LAST_UPDATE_DATE
6658 ,LAST_UPDATED_BY
6659 ,LAST_UPDATE_LOGIN
6660 ,CREATED_BY
6661 ,CREATION_DATE
6662 ,TL_EXT_ATTR1
6663 ,TL_EXT_ATTR2
6664 ,TL_EXT_ATTR3
6665 ,TL_EXT_ATTR4
6666 ,TL_EXT_ATTR5
6667 ,TL_EXT_ATTR6
6668 ,TL_EXT_ATTR7
6669 ,TL_EXT_ATTR8
6670 ,TL_EXT_ATTR9
6671 ,TL_EXT_ATTR10
6672 ,TL_EXT_ATTR11
6673 ,TL_EXT_ATTR12
6674 ,TL_EXT_ATTR13
6675 ,TL_EXT_ATTR14
6676 ,TL_EXT_ATTR15
6677 ,TL_EXT_ATTR16
6678 ,TL_EXT_ATTR17
6679 ,TL_EXT_ATTR18
6680 ,TL_EXT_ATTR19
6681 ,TL_EXT_ATTR20
6682 ,TL_EXT_ATTR21
6683 ,TL_EXT_ATTR22
6684 ,TL_EXT_ATTR23
6685 ,TL_EXT_ATTR24
6686 ,TL_EXT_ATTR25
6687 ,TL_EXT_ATTR26
6688 ,TL_EXT_ATTR27
6689 ,TL_EXT_ATTR28
6690 ,TL_EXT_ATTR29
6691 ,TL_EXT_ATTR30
6692 ,TL_EXT_ATTR31
6693 ,TL_EXT_ATTR32
6694 ,TL_EXT_ATTR33
6695 ,TL_EXT_ATTR34
6696 ,TL_EXT_ATTR35
6697 ,TL_EXT_ATTR36
6698 ,TL_EXT_ATTR37
6699 ,TL_EXT_ATTR38
6700 ,TL_EXT_ATTR39
6701 ,TL_EXT_ATTR40
6702 ,UDA_TEMPLATE_ID
6703 ,DRAFT_ID
6704 ,REVISION_NUM
6705 ,LATEST_EXTERNAL_FLAG
6706 )
6707 SELECT
6708 POLE.EXTENSION_ID
6709 ,POLE.ATTR_GROUP_ID
6710 ,POLE.LINE_LOCATION_ID
6711 ,POLE.DATA_LEVEL_ID
6712 ,POLE.PK1_VALUE
6713 ,POLE.PK2_VALUE
6714 ,POLE.PK3_VALUE
6715 ,POLE.PK4_VALUE
6716 ,POLE.PK5_VALUE
6717 ,POLE.SOURCE_LANG
6718 ,POLE.LANGUAGE
6719 ,POLE.LAST_UPDATE_DATE
6720 ,POLE.LAST_UPDATED_BY
6721 ,POLE.LAST_UPDATE_LOGIN
6722 ,POLE.CREATED_BY
6723 ,POLE.CREATION_DATE
6724 ,POLE.TL_EXT_ATTR1
6725 ,POLE.TL_EXT_ATTR2
6726 ,POLE.TL_EXT_ATTR3
6727 ,POLE.TL_EXT_ATTR4
6728 ,POLE.TL_EXT_ATTR5
6729 ,POLE.TL_EXT_ATTR6
6730 ,POLE.TL_EXT_ATTR7
6731 ,POLE.TL_EXT_ATTR8
6732 ,POLE.TL_EXT_ATTR9
6733 ,POLE.TL_EXT_ATTR10
6734 ,POLE.TL_EXT_ATTR11
6735 ,POLE.TL_EXT_ATTR12
6736 ,POLE.TL_EXT_ATTR13
6737 ,POLE.TL_EXT_ATTR14
6738 ,POLE.TL_EXT_ATTR15
6739 ,POLE.TL_EXT_ATTR16
6740 ,POLE.TL_EXT_ATTR17
6741 ,POLE.TL_EXT_ATTR18
6742 ,POLE.TL_EXT_ATTR19
6743 ,POLE.TL_EXT_ATTR20
6744 ,POLE.TL_EXT_ATTR21
6745 ,POLE.TL_EXT_ATTR22
6746 ,POLE.TL_EXT_ATTR23
6747 ,POLE.TL_EXT_ATTR24
6748 ,POLE.TL_EXT_ATTR25
6749 ,POLE.TL_EXT_ATTR26
6750 ,POLE.TL_EXT_ATTR27
6751 ,POLE.TL_EXT_ATTR28
6752 ,POLE.TL_EXT_ATTR29
6753 ,POLE.TL_EXT_ATTR30
6754 ,POLE.TL_EXT_ATTR31
6755 ,POLE.TL_EXT_ATTR32
6756 ,POLE.TL_EXT_ATTR33
6757 ,POLE.TL_EXT_ATTR34
6758 ,POLE.TL_EXT_ATTR35
6759 ,POLE.TL_EXT_ATTR36
6760 ,POLE.TL_EXT_ATTR37
6761 ,POLE.TL_EXT_ATTR38
6762 ,POLE.TL_EXT_ATTR39
6763 ,POLE.TL_EXT_ATTR40
6764 ,POLE.UDA_TEMPLATE_ID
6765 ,POLE.DRAFT_ID
6766 ,p_revision_num
6767 ,NULL
6768 FROM PO_LINE_LOCATIONS_ALL_EXT_TL POLE,
6769 PO_LINE_LOCATIONS_ALL POL
6770 WHERE POL.po_header_id = p_po_header_id
6771 AND POL.line_location_id = POLE.line_location_id
6772 AND POLE.draft_id = -1;
6773
6774 l_progress := '140';
6775 IF PO_LOG.d_stmt THEN
6776 PO_LOG.stmt(d_mod,l_progress,'Number of rows inserted: '||SQL%rowcount);
6777 END IF;
6778 -- End PO_LINE_LOC_ALL_EXT_TL_ARCHIVE
6779
6780 END IF; --IF p_document_subtype='BLANKET'
6781
6782 END IF; --IF l_enabled_flag = 'Y' AND p_document_type IN ('PO', 'PA')
6783
6784 IF PO_LOG.d_proc THEN
6785 PO_LOG.proc_end(d_mod);
6786 END IF;
6787
6788 EXCEPTION
6789 WHEN OTHERS THEN
6790 IF PO_LOG.d_exc THEN
6791 PO_LOG.exc(d_mod,l_progress,'Unhandled exception');
6792 END IF;
6793 RAISE;
6794 END archive_uda_attribs;
6795 --<CLM View Base Document>
6796
6797 --<CLM View Base Document>
6798 -------------------------------------------------------------------------------
6799 --Start of Comments
6800 --Name: archive_attachments
6801 --Pre-reqs:
6802 -- None.
6803 --Modifies:
6804 -- FND_ATTACHED_DOCUMENTS / FND_DOCUMENTS
6805 --Locks:
6806 -- None.
6807 --Function:
6808 -- Archives the Attachments with pk1_value = po_header_id-draft_id
6809 -- In the current scope of View Base Document Project, this archival is done
6810 -- only for version 0 of the document, with draft_id = 0
6811 --Parameters:
6812 --IN:
6813 --p_po_header_id
6814 -- The PO_HEADER_ID of the document that needs to be archived
6815 --p_document_type
6816 -- The Document Type of the document that needs to be archived
6817 --p_document_subtype
6818 -- The Document Subtype of the document that needs to be archived
6819 --p_revision_num
6820 -- The revision number of the document that is being archived.
6821 -- In the current Scope of View Base Document project,
6822 -- only the 1st version (Revision 0) is being archived for UDA's
6823 --Testing:
6824 -- None.
6825 --End of Comments
6826 -------------------------------------------------------------------------------
6827 PROCEDURE archive_attachments
6828 (
6829 p_po_header_id IN NUMBER
6830 , p_document_type IN VARCHAR2
6831 , p_document_subtype IN VARCHAR2
6832 , p_revision_num IN NUMBER
6833 , p_draft_id IN NUMBER
6834 )
6835 IS
6836 d_mod CONSTANT VARCHAR2(100) := 'archive_attachments';
6837 l_progress VARCHAR2(4) := '000';
6838
6839 l_from_po_header_id PO_HEADERS_ALL.po_header_id%TYPE;
6840 l_to_po_header_id FND_ATTACHED_DOCUMENTS.pk2_value%TYPE;
6841 l_from_po_line_id PO_LINES_ALL.po_line_id%TYPE;
6842 l_to_po_line_id FND_ATTACHED_DOCUMENTS.pk2_value%TYPE;
6843 l_from_line_location_id PO_LINE_LOCATIONS_ALL.line_location_id%TYPE;
6844 l_to_line_location_id FND_ATTACHED_DOCUMENTS.pk2_value%TYPE;
6845 l_from_po_distribution_id PO_DISTRIBUTIONS_ALL.po_distribution_id%TYPE;
6846 l_to_po_distribution_id FND_ATTACHED_DOCUMENTS.pk2_value%TYPE;
6847
6848 -- Cursor definitions:
6849
6850 CURSOR po_line_cursor(p_po_header_id po_lines.po_header_id%TYPE) IS
6851 SELECT po_line_id
6852 FROM PO_LINES_ALL
6853 WHERE po_header_id = p_po_header_id
6854 ORDER BY line_num;
6855
6856 CURSOR po_shipment_cursor(p_po_line_id po_line_locations.po_line_id%TYPE) IS
6857 SELECT line_location_id
6858 FROM PO_LINE_LOCATIONS_ALL
6859 WHERE po_line_id = p_po_line_id
6860 ORDER BY shipment_num;
6861
6862 CURSOR po_distribution_cursor(p_line_location_id po_distributions.line_location_id%TYPE) IS
6863 SELECT po_distribution_id
6864 FROM PO_DISTRIBUTIONS_ALL
6865 WHERE line_location_id = p_line_location_id
6866 ORDER BY distribution_num;
6867
6868 BEGIN
6869 l_progress := '000';
6870
6871 IF PO_LOG.d_proc THEN
6872 PO_LOG.proc_begin(d_mod,'p_po_header_id',p_po_header_id);
6873 PO_LOG.proc_begin(d_mod,'p_revision_num',p_revision_num);
6874 END IF;
6875
6876 -- Copy Header Attachments Start
6877 l_from_po_header_id := p_po_header_id;
6878 l_to_po_header_id := l_from_po_header_id || '-' || p_draft_id;
6879
6880 l_progress := '010';
6881 IF PO_LOG.d_stmt THEN
6882 PO_LOG.stmt(d_mod,l_progress,
6883 'Copy Header Attachment from pk1_value: '|| l_from_po_header_id
6884 || ' to pk1_value: '||l_to_po_header_id);
6885 END IF;
6886
6887 fnd_attached_documents2_pkg.copy_attachments('PO_HEADERS',
6888 l_from_po_header_id,
6889 '',
6890 '',
6891 '',
6892 '',
6893 'PO_HEADERS',
6894 l_to_po_header_id,
6895 '',
6896 '',
6897 '',
6898 '',
6899 fnd_global.user_id,
6900 fnd_global.login_id,
6901 '',
6902 '',
6903 '');
6904 -- Copy Header Attachments End
6905
6906 -- Archive Line and Line Locations data only for PO or PA BLANKETS
6907 IF (p_document_type = 'PO') OR
6908 (p_document_type = 'PA' AND p_document_subtype='BLANKET') THEN
6909 -- Start Copy Line, Shipments, Distributions Attachments
6910
6911 OPEN po_line_cursor(l_from_po_header_id);
6912
6913 --Repeat above steps until all lines have been fetched and processed
6914 <<LINES>>
6915 LOOP
6916
6917 -- Start Copy Line Attachments
6918 FETCH po_line_cursor INTO l_from_po_line_id;
6919 EXIT LINES WHEN po_line_cursor%NOTFOUND;
6920
6921 l_to_po_line_id := l_from_po_line_id || '-' || p_draft_id;
6922
6923 l_progress := '020';
6924 IF PO_LOG.d_stmt THEN
6925 PO_LOG.stmt(d_mod,l_progress,
6926 'Copy Line Attachments from pk1_value: '|| l_from_po_line_id
6927 || ' to pk1_value: '||l_to_po_line_id);
6928 END IF;
6929
6930 fnd_attached_documents2_pkg.copy_attachments('PO_LINES',
6931 l_from_po_line_id,
6932 '',
6933 '',
6934 '',
6935 '',
6936 'PO_LINES',
6937 l_to_po_line_id,
6938 '',
6939 '',
6940 '',
6941 '',
6942 fnd_global.user_id,
6943 fnd_global.login_id,
6944 '',
6945 '',
6946 '');
6947 -- End Copy Line Attachments
6948
6949 OPEN po_shipment_cursor(l_from_po_line_id);
6950
6951 <<SHIPMENTS>>
6952 LOOP
6953
6954 -- Start Copy Shipment Attachments
6955 FETCH po_shipment_cursor INTO l_from_line_location_id;
6956 EXIT SHIPMENTS WHEN po_shipment_cursor%NOTFOUND;
6957
6958 l_to_line_location_id := l_from_line_location_id || '-' || p_draft_id;
6959
6960 l_progress := '030';
6961 IF PO_LOG.d_stmt THEN
6962 PO_LOG.stmt(d_mod,l_progress,
6963 'Copy Shipment Attachments from pk1_value: '|| l_from_line_location_id
6964 || ' to pk1_value: '||l_to_line_location_id);
6965 END IF;
6966
6967 fnd_attached_documents2_pkg.copy_attachments('PO_SHIPMENTS',
6968 l_from_line_location_id,
6969 '',
6970 '',
6971 '',
6972 '',
6973 'PO_SHIPMENTS',
6974 l_to_line_location_id,
6975 '',
6976 '',
6977 '',
6978 '',
6979 fnd_global.user_id,
6980 fnd_global.login_id,
6981 '',
6982 '',
6983 '');
6984
6985 OPEN po_distribution_cursor(l_from_line_location_id);
6986
6987 <<DISTRIBUTIONS>>
6988 LOOP
6989
6990 -- Start Copy Shipment Attachments
6991 FETCH po_distribution_cursor INTO l_from_po_distribution_id;
6992 EXIT DISTRIBUTIONS WHEN po_distribution_cursor%NOTFOUND;
6993
6994 l_to_po_distribution_id := l_from_po_distribution_id || '-' || p_draft_id;
6995
6996 l_progress := '040';
6997 IF PO_LOG.d_stmt THEN
6998 PO_LOG.stmt(d_mod,l_progress,
6999 'Copy Distribution Attachments from pk1_value: '|| l_from_po_distribution_id
7000 || ' to pk1_value: '||l_to_po_distribution_id);
7001 END IF;
7002
7003 fnd_attached_documents2_pkg.copy_attachments('PO_DISTRIBUTIONS',
7004 l_from_po_distribution_id,
7005 '',
7006 '',
7007 '',
7008 '',
7009 'PO_DISTRIBUTIONS',
7010 l_to_po_distribution_id,
7011 '',
7012 '',
7013 '',
7014 '',
7015 fnd_global.user_id,
7016 fnd_global.login_id,
7017 '',
7018 '',
7019 '');
7020 -- End Copy Shipment Attachments
7021
7022 END LOOP DISTRIBUTIONS;
7023 CLOSE po_distribution_cursor;
7024
7025 END LOOP SHIPMENTS;
7026 CLOSE po_shipment_cursor;
7027
7028 END LOOP LINES;
7029 CLOSE po_line_cursor;
7030 -- End Copy Line, Shipments, Distributions Attachments
7031 END IF; --IF p_document_type = 'PO' || p_document_subtype='BLANKET'
7032
7033 IF PO_LOG.d_proc THEN
7034 PO_LOG.proc_end(d_mod);
7035 END IF;
7036
7037 EXCEPTION
7038 WHEN OTHERS THEN
7039 IF PO_LOG.d_exc THEN
7040 PO_LOG.exc(d_mod,l_progress,'Unhandled exception');
7041 END IF;
7042
7043 IF (po_line_cursor%ISOPEN) THEN
7044 CLOSE po_line_cursor;
7045 END IF;
7046 IF (po_shipment_cursor%ISOPEN) THEN
7047 CLOSE po_shipment_cursor;
7048 END IF;
7049 IF (po_distribution_cursor%ISOPEN) THEN
7050 CLOSE po_distribution_cursor;
7051 END IF;
7052
7053 RAISE;
7054
7055 END archive_attachments;
7056 --<CLM View Base Document>
7057
7058 --<CLM View Base Document>
7059 -------------------------------------------------------------------------------
7060 --Start of Comments
7061 --Name: sync_contract_terms_to_draft
7062 --Pre-reqs:
7063 -- None.
7064 --Modifies:
7065 -- Contracts Transaction Tables
7066 --Locks:
7067 -- None.
7068 --Function:
7069 -- In order to view contract terms while handling View Base Document,
7070 -- Contract Terms on the Base Document are duplicated with a new
7071 -- HeaderId = DraftId generated and stored in PO_HEADERS_ARCHIVE_ALL
7072 -- This DRAFT_ID uniquely identifies the 0th revision of the PO for Contracts
7073 --Parameters:
7074 --IN:
7075 --p_po_header_id
7076 -- The PO_HEADER_ID of the document that needs to be archived
7077 --p_document_type
7078 -- The Document Type of the document that needs to be archived
7079 --p_document_subtype
7080 -- The Document Subtype of the document that needs to be archived
7081 --p_revision_num
7082 -- The revision number of the document that is being archived.
7083 -- In the current Scope of View Base Document project,
7084 -- only the 1st version (Revision 0) is being archived
7085 --Testing:
7086 -- None.
7087 --End of Comments
7088 -------------------------------------------------------------------------------
7089 PROCEDURE sync_contract_terms_to_draft
7090 (
7091 p_po_header_id IN NUMBER
7092 , p_document_type IN VARCHAR2
7093 , p_document_subtype IN VARCHAR2
7094 , p_revision_num IN NUMBER
7095 )
7096 IS
7097 d_mod CONSTANT VARCHAR2(100) := 'sync_contract_terms_to_draft';
7098 l_progress VARCHAR2(4) := '000';
7099
7100 l_conterms_exist_flag PO_HEADERS_ALL.conterms_exist_flag%TYPE;
7101 l_draft_id PO_HEADERS_ARCHIVE_ALL.DRAFT_ID%TYPE;
7102 l_return_status VARCHAR2(1);
7103
7104
7105 BEGIN
7106 l_progress := '000';
7107
7108 IF PO_LOG.d_proc THEN
7109 PO_LOG.proc_begin(d_mod,'p_po_header_id',p_po_header_id);
7110 PO_LOG.proc_begin(d_mod,'p_revision_num',p_revision_num);
7111 END IF;
7112
7113 l_progress := '010';
7114 IF PO_LOG.d_stmt THEN
7115 PO_LOG.stmt(d_mod,l_progress,
7116 'Select conterms_exist_flag - po_header_id: '||p_po_header_id);
7117 END IF;
7118 -- SQL What:Find out if contract terms exist
7119 -- SQL Why :Sync Contract Terms if needed
7120 SELECT NVL(conterms_exist_flag, 'N')
7121 INTO l_conterms_exist_flag
7122 FROM po_headers_all
7123 WHERE po_header_id = p_po_header_id;
7124
7125 IF (l_conterms_exist_flag = 'Y') THEN
7126 l_progress := '020';
7127 IF PO_LOG.d_stmt THEN
7128 PO_LOG.stmt(d_mod,l_progress,
7129 'ConTerms Exist. Generate and store a new Draft Id in PO_HEADERS_ARCHIVE_ALL');
7130 END IF;
7131
7132 --Generate new Draft Id and store in PO_HEADERS_ARCHIVE_ALL
7133 --This is generated to uniquely identify the 0th revision of the PO for Contracts
7134 l_draft_id := PO_DRAFTS_PVT.draft_id_nextval;
7135
7136 --Update PO_HEADERS_ARCHIVE_ALL with this DRAFT_ID
7137 UPDATE PO_HEADERS_ARCHIVE_ALL
7138 SET draft_id = l_draft_id
7139 WHERE po_header_id = p_po_header_id
7140 AND revision_num = p_revision_num;
7141
7142 l_progress := '030';
7143 IF PO_LOG.d_stmt THEN
7144 PO_LOG.stmt(d_mod,l_progress,
7145 'Calling PO_CONTERMS_UTL_GRP.Copy_Conterms_Mod with p_document_id = '||
7146 p_po_header_id || ' and p_draft_id = '||l_draft_id);
7147 END IF;
7148
7149 PO_CONTERMS_UTL_GRP.Copy_Conterms_Mod(p_document_id => p_po_header_id,
7150 p_draft_id => l_draft_id ,
7151 p_to_mod => 'Y' ,
7152 x_return_status => l_return_status);
7153
7154 END IF;--IF (l_conterms_exist_flag = 'Y') THEN
7155
7156
7157 IF PO_LOG.d_proc THEN
7158 PO_LOG.proc_end(d_mod);
7159 END IF;
7160
7161 EXCEPTION
7162 WHEN OTHERS THEN
7163 IF PO_LOG.d_exc THEN
7164 PO_LOG.exc(d_mod,l_progress,'Unhandled exception');
7165 END IF;
7166
7167 RAISE;
7168
7169 END sync_contract_terms_to_draft;
7170 --<CLM View Base Document>
7171
7172
7173 -------------------------------------------------------------------------------
7174 --Start of Comments
7175 --Name: ARCHIVE_EXHIBITS
7176 --Pre-reqs:
7177 -- None.
7178 --Modifies:
7179 -- PO_EXHIBIT_DETAILS_ARCHIVE
7180 --Locks:
7181 -- None.
7182 --Function:
7183 -- Arcives the po document exhibits.
7184 --Parameters:
7185 --IN:
7186 --p_document_id
7187 -- The id of the document that needs to be archived.
7188 --p_revision_num
7189 -- The revision of the document that needs to be archived.
7190 --Testing:
7191 -- None.
7192 --End of Comments
7193 -------------------------------------------------------------------------------
7194 PROCEDURE ARCHIVE_EXHIBITS(p_document_id IN NUMBER,
7195 p_revision_num IN NUMBER)
7196 IS
7197 l_api_name CONSTANT VARCHAR2(30) := 'ARCHIVE_EXHIBITS';
7198 l_module VARCHAR2(100);
7199 l_progress VARCHAR2(3);
7200 l_revision_num NUMBER;
7201 l_continue BOOLEAN := FALSE;
7202 l_clm_flag VARCHAR2(1) := 'N';
7203
7204 BEGIN
7205
7206 l_progress := '000';
7207 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
7208 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_PROCEDURE THEN
7209 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, l_module,
7210 'Entering ' || G_PKG_NAME || '.' || l_api_name);
7211 END IF;
7212
7213 l_progress := '010';
7214 --<PAR Project>
7215 l_clm_flag := PO_CLM_INTG_GRP.is_clm_po(p_document_id);
7216
7217 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
7218 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
7219 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
7220 'INSERT PO_EXHIBIT_DETAILS_ARCHIVE');
7221 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
7222 'l_clm_flag : ' || l_clm_flag);
7223 END IF;
7224
7225 INSERT INTO po_exhibit_details_archive
7226 (
7227 po_exhibit_details_id,
7228 po_header_id,
7229 exhibit_name,
7230 exhibit_description,
7231 is_cdrl,
7232 REFERENCE_LINE_ID,
7233 latest_external_flag,
7234 revision_num,
7235 LAST_UPDATE_DATE,
7236 LAST_UPDATED_BY,
7237 CREATION_DATE,
7238 CREATED_BY,
7239 LAST_UPDATE_LOGIN
7240 )
7241 SELECT
7242 pex.po_exhibit_details_id,
7243 pex.po_header_id,
7244 pex.exhibit_name,
7245 pex.exhibit_description,
7246 pex.is_cdrl,
7247 pex.REFERENCE_LINE_ID,
7248 'Y',
7249 p_revision_num,
7250 pex.LAST_UPDATE_DATE,
7251 pex.LAST_UPDATED_BY,
7252 pex.CREATION_DATE,
7253 pex.CREATED_BY,
7254 pex.LAST_UPDATE_LOGIN
7255 FROM po_exhibit_details pex,
7256 po_exhibit_details_archive pexa
7257 WHERE pex.po_header_id = p_document_id
7258 AND pex.po_exhibit_details_id = pexa.po_exhibit_details_id (+)
7259 AND pexa.latest_external_flag (+) = 'Y'
7260 AND (
7261 (pex.exhibit_description IS NULL AND pexa.exhibit_description IS NOT NULL OR
7262 pex.exhibit_description IS NOT NULL AND pexa.exhibit_description IS NULL OR
7263 pex.exhibit_description <> pexa.exhibit_description) OR
7264
7265 (pex.is_cdrl IS NULL AND pexa.is_cdrl IS NOT NULL OR
7266 pex.is_cdrl IS NOT NULL AND pexa.is_cdrl IS NULL OR
7267 pex.is_cdrl <> pexa.is_cdrl)
7268 ) ;
7269
7270
7271 l_continue := (SQL%ROWCOUNT > 0);
7272
7273 IF l_continue THEN
7274 l_progress := '020';
7275 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
7276 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
7277 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
7278 'Update po_exhibit_details_archive to reset latest_external_flag');
7279 END IF;
7280
7281 -- If a row was inserted into po_exhibit_details_archive, then set the appropriate flags
7282 UPDATE po_exhibit_details_archive PEXA
7283 SET latest_external_flag = 'N'
7284 WHERE po_header_id = p_document_id
7285 AND latest_external_flag = 'Y'
7286 AND revision_num < p_revision_num
7287 AND EXISTS
7288 (SELECT 'A new archived row'
7289 FROM po_exhibit_details_archive PEXA2
7290 WHERE PEXA2.po_exhibit_details_id = PEXA.po_exhibit_details_id
7291 AND PEXA2.latest_external_flag = 'Y'
7292 AND PEXA2.revision_num = p_revision_num);
7293 ELSE
7294 l_progress := '030';
7295 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
7296 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
7297 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
7298 'No need to reset latest_external_flag');
7299 END IF;
7300 END IF; /* IF l_continue */
7301
7302 l_progress := '030';
7303
7304 EXCEPTION
7305 WHEN OTHERS THEN
7306 po_message_s.sql_error('Exception of ARCHIVE_EXHIBITS()',
7307 l_progress , sqlcode);
7308 FND_MSG_PUB.Add;
7309 IF (G_FND_DEBUG = 'Y') THEN
7310 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
7311 FND_LOG.string(FND_LOG.level_unexpected, l_module,
7312 'EXCEPTION: '||sqlerrm);
7313 END IF;
7314 END IF;
7315 RAISE;
7316 END ARCHIVE_EXHIBITS;
7317
7318 END PO_DOCUMENT_ARCHIVE_PVT;