[Home] [Help]
PACKAGE BODY: APPS.PO_SYNC_DRAFT_FROM_ARCHIVE_PVT
Source
1 PACKAGE BODY PO_SYNC_DRAFT_FROM_ARCHIVE_PVT AS
2 /* $Header: PO_SYNC_DRAFT_FROM_ARCHIVE_PVT.plb 120.4.12020000.3 2013/04/30 06:41:49 mabaig ship $ */
3
4 d_pkg_name CONSTANT varchar2(50) :=
5 PO_LOG.get_package_base('PO_SYNC_DRAFT_FROM_ARCHIVE_PVT');
6
7 -------------------------------------------------------------------------------
8 --Start of Comments
9 --Name: CHECK_PO_SYNC
10 --Pre-reqs:
11 -- None.
12 --Modifies:
13 -- None.
14 --Locks:
15 -- None.
16 --Function:
17 -- Check the Draft Table to see if it has already been synced from Archive
18 --Parameters:
19 --IN:
20 --p_document_id
21 -- The id of the document that needs to be synced
22 --p_revision_num
23 -- The revision number of the document that needs to be synced
24 --p_draft_id
25 -- The draft_id against which the check needs to be made
26 --OUT:
27 --x_return_status
28 -- 'Y' if sync needed
29 -- 'N' if sync not needed
30 --Testing:
31 -- None.
32 --End of Comments
33 -------------------------------------------------------------------------------
34 PROCEDURE CHECK_PO_SYNC(p_document_id IN NUMBER,
35 p_revision_num IN NUMBER,
36 p_draft_id IN NUMBER,
37 x_return_status OUT NOCOPY VARCHAR2)
38 IS
39
40 d_api_name CONSTANT VARCHAR2(30) := 'CHECK_PO_SYNC';
41 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
42 d_position NUMBER;
43
44 BEGIN
45
46 d_position := 0;
47 IF (PO_LOG.d_proc) THEN
48 PO_LOG.proc_begin(d_module,'p_po_header_id',p_document_id);
49 END IF;
50
51 d_position := 10;
52 IF (PO_LOG.d_stmt) THEN
53 PO_LOG.stmt(d_module, d_position, 'Check if the PO has already been synced from archive -> dft');
54 END IF;
55 -- SQL What: Select record from Draft which has been copied from Archive
56 -- SQL Why : Check if the document has already been synced from archive to Draft
57 SELECT 'Y' INTO x_return_status
58 FROM PO_HEADERS_ARCHIVE_ALL PHA,
59 PO_DRAFTS PD
60 WHERE pha.po_header_id = p_document_id
61 AND pha.revision_num = p_revision_num
62 AND pd.document_id = pha.po_header_id (+)
63 AND pd.draft_id = p_draft_id; --Always passed as 0 for Revision 0 Documents
64
65 d_position := 20;
66 IF (PO_LOG.d_stmt) THEN
67 PO_LOG.stmt(d_module, d_position, 'Archive -> Dft Sync has already been done');
68 END IF;
69
70 d_position := 30;
71 IF (PO_LOG.d_proc) THEN
72 PO_LOG.proc_end(d_module);
73 END IF;
74
75 EXCEPTION
76 WHEN No_Data_Found THEN
77 x_return_status := 'N'; -- Draft has not been synced from Archive
78 WHEN OTHERS THEN
79 PO_MESSAGE_S.add_exc_msg
80 ( p_pkg_name => d_pkg_name,
81 p_procedure_name => d_api_name || '.' || d_position
82 );
83
84 x_return_status := 'N';
85
86 END CHECK_PO_SYNC;
87
88 -------------------------------------------------------------------------------
89 --Start of Comments
90 --Name: DELETE_DUMMY_DATA_FROM_DRAFT
91 --Pre-reqs:
92 -- None.
93 --Modifies:
94 -- PO_DRAFTS
95 --Locks:
96 -- None.
97 --Function:
98 -- Deletes the Data from Draft Tables for the given Documnt ID, and Draft Id = 0
99 -- This data should ideally not exist as it is created temporarily for "View Base Document" Action
100 -- Clearing this in case it exists
101 --Parameters:
102 --IN:
103 --p_draft_id
104 -- The draft_id using which the dummy record needs to be deleted
105 --Testing:
106 -- None.
107 --End of Comments
108 -------------------------------------------------------------------------------
109 PROCEDURE DELETE_DUMMY_DATA_FROM_DRAFT(p_draft_id IN NUMBER)
110 IS
111
112 d_api_name CONSTANT VARCHAR2(30) := 'DELETE_DUMMY_DATA_FROM_DRAFT';
113 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
114 d_position NUMBER;
115
116 BEGIN
117
118 d_position := 0;
119 IF (PO_LOG.d_proc) THEN
120 PO_LOG.proc_begin(d_module,'p_draft_id',p_draft_id);
121 END IF;
122
123 d_position := 10;
124 IF (PO_LOG.d_proc) THEN
125 PO_LOG.proc_begin(d_module,'Delete all entries from DRAFT Tables having draft_id: ',p_draft_id);
126 END IF;
127
128 --Deleting from po_drafts
129 DELETE FROM po_drafts
130 WHERE draft_id = p_draft_id;
131
132 --Start Deleting Headers/Lines/Shipments/Distributions
133 DELETE FROM po_headers_draft_all
134 WHERE draft_id = p_draft_id;
135
136 DELETE FROM po_lines_draft_all
137 WHERE draft_id = p_draft_id;
138
139 DELETE FROM po_line_locations_draft_all
140 WHERE draft_id = p_draft_id;
141
142 DELETE FROM po_distributions_draft_all
143 WHERE draft_id = p_draft_id;
144 --End Deleting Headers/Lines/Shipments/Distributions
145
146 --Start Deleting UDA Atribs
147 DELETE FROM po_headers_all_ext_b
148 WHERE draft_id = p_draft_id;
149
150 DELETE FROM po_lines_all_ext_b
151 WHERE draft_id = p_draft_id;
152
153 DELETE FROM po_line_locations_all_ext_b
154 WHERE draft_id = p_draft_id;
155
156 DELETE FROM po_headers_all_ext_tl
157 WHERE draft_id = p_draft_id;
158
159 DELETE FROM po_lines_all_ext_tl
160 WHERE draft_id = p_draft_id;
161
162 DELETE FROM po_line_locations_all_ext_tl
163 WHERE draft_id = p_draft_id;
164 --End Deleting UDA Atribs
165
166 --Deleting from po_ga_org_assign_draft
167 DELETE FROM po_ga_org_assign_draft
168 WHERE draft_id = p_draft_id;
169
170 --Deleting from po_attribute_values
171 DELETE FROM po_attribute_values_draft
172 WHERE draft_id = p_draft_id;
173
174 DELETE FROM po_attribute_values_tlp_draft
175 WHERE draft_id = p_draft_id;
176
177 --Deleting from po_price_diff_draft
178 DELETE FROM po_price_diff_draft
179 WHERE entity_type in ('PO LINE','BLANKET LINE','PRICE BREAK')
180 AND draft_id = p_draft_id;
181
182 --Deleting po_price_adjustments_draft
183 DELETE FROM po_price_adjustments_draft
184 WHERE draft_id = p_draft_id;
185
186 DELETE FROM po_price_adj_attribs_draft
187 WHERE draft_id = p_draft_id;
188
189 --Deleting from po_notification_ctrl_draft
190 DELETE FROM po_notification_ctrl_draft
191 WHERE draft_id = p_draft_id;
192
193 -- Delete from po_exhibit_details_draft -Elins project
194 DELETE FROM po_exhibit_details_draft
195 WHERE draft_id = p_draft_id;
196
197 d_position := 20;
198 IF (PO_LOG.d_stmt) THEN
199 PO_LOG.stmt(d_module, d_position, 'Rows deleted from DRAFT Tables');
200 END IF;
201
202 d_position := 30;
203 IF (PO_LOG.d_proc) THEN
204 PO_LOG.proc_end(d_module);
205 END IF;
206
207 EXCEPTION
208 WHEN OTHERS THEN
209 PO_MESSAGE_S.add_exc_msg
210 ( p_pkg_name => d_pkg_name,
211 p_procedure_name => d_api_name || '.' || d_position
212 );
213 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
214
215 END DELETE_DUMMY_DATA_FROM_DRAFT;
216 -------------------------------------------------------------------------------
217 --Start of Comments
218 --Name: CREATE_DUMMY_ROW_IN_DRAFT
219 --Pre-reqs:
220 -- None.
221 --Modifies:
222 -- PO_DRAFTS
223 --Locks:
224 -- None.
225 --Function:
226 -- Creates a dummy row in PO_DRAFTS with the given draft_id (0 in case of View Base Document)
227 --Parameters:
228 --IN:
229 --p_document_id
230 -- The id of the document for which the 'View Base Document' action is being handled
231 --p_revision_num
232 -- The revision number of the document
233 --p_draft_id
234 -- The draft_id using which the dummy record needs to be created
235 --Testing:
236 -- None.
237 --End of Comments
238 -------------------------------------------------------------------------------
239 PROCEDURE CREATE_DUMMY_ROW_IN_DRAFT(p_document_id IN NUMBER,
240 p_revision_num IN NUMBER,
241 p_draft_id IN NUMBER)
242 IS
243
244 d_api_name CONSTANT VARCHAR2(30) := 'CREATE_DUMMY_ROW_IN_DRAFT';
245 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
246 d_position NUMBER;
247
248 BEGIN
249
250 d_position := 0;
251 IF (PO_LOG.d_proc) THEN
252 PO_LOG.proc_begin(d_module,'p_po_header_id',p_document_id);
253 END IF;
254
255 d_position := 10;
256 IF (PO_LOG.d_stmt) THEN
257 PO_LOG.stmt(d_module, d_position, 'Insert Dummy row in PO_DRAFTS with DRAFT_ID = 0');
258 END IF;
259
260 INSERT INTO po_drafts
261 (draft_id,
262 document_id,
263 owner_user_id,
264 owner_role,
265 status,
266 draft_type,
267 last_update_date,
268 last_updated_by,
269 last_update_login,
270 creation_date,
271 created_by
272 )
273 VALUES
274 ( p_draft_id, -- Always 0 for action View Base Document (Revision 0)
275 p_document_id,
276 FND_GLOBAL.user_id,
277 'BUYER',
278 'COMPLETED',
279 'ARCHIVE',
280 SYSDATE,
281 FND_GLOBAL.user_id,
282 FND_GLOBAL.login_id,
283 SYSDATE,
284 FND_GLOBAL.user_id
285 );
286
287 d_position := 20;
288 IF (PO_LOG.d_stmt) THEN
289 PO_LOG.stmt(d_module, d_position, 'Row inserted into PO_DRAFTS');
290 END IF;
291
292 d_position := 30;
293 IF (PO_LOG.d_proc) THEN
294 PO_LOG.proc_end(d_module);
295 END IF;
296
297 EXCEPTION
298 WHEN OTHERS THEN
299 PO_MESSAGE_S.add_exc_msg
300 ( p_pkg_name => d_pkg_name,
301 p_procedure_name => d_api_name || '.' || d_position
302 );
303 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
304
305 END CREATE_DUMMY_ROW_IN_DRAFT;
306
307 -------------------------------------------------------------------------------
308 --Start of Comments
309 --Name: SYNC_HEADER
310 --Pre-reqs:
311 -- None.
312 --Modifies:
313 -- PO_HEADERS_DRAFT_ALL
314 --Locks:
315 -- None.
316 --Function:
317 -- Sync PO Header
318 --Parameters:
319 --IN:
320 --p_document_id
321 -- The id of the document that needs to be synced
322 --p_revision_num
323 -- The revision number of the document that needs to be synced
324 --p_draft_id
325 -- The draft_id using which the record has to be inserted at the time of sync
326 --Testing:
327 -- None.
328 --End of Comments
329 -------------------------------------------------------------------------------
330 PROCEDURE SYNC_HEADER(p_document_id IN NUMBER,
331 p_revision_num IN NUMBER,
332 p_draft_id IN NUMBER)
333 IS
334
335 d_api_name CONSTANT VARCHAR2(30) := 'SYNC_HEADER';
336 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
337 d_position NUMBER;
338
339 BEGIN
340
341 d_position := 0;
342 IF (PO_LOG.d_proc) THEN
343 PO_LOG.proc_begin(d_module,'p_po_header_id',p_document_id);
344 END IF;
345
346 d_position := 10;
347 IF (PO_LOG.d_stmt) THEN
348 PO_LOG.stmt(d_module, d_position, 'transfer records from archive to dft');
349 END IF;
350
351 INSERT INTO po_headers_draft_all
352 ( draft_id,
353 delete_flag,
354 change_accepted_flag,
355 change_status,
356 po_header_id,
357 agent_id,
358 type_lookup_code,
359 last_update_date,
360 last_updated_by,
361 segment1,
362 summary_flag,
363 enabled_flag,
364 segment2,
365 segment3,
366 segment4,
367 segment5,
368 start_date_active,
369 end_date_active,
370 last_update_login,
371 creation_date,
372 created_by,
373 vendor_id,
374 vendor_site_id,
375 vendor_contact_id,
376 ship_to_location_id,
377 bill_to_location_id,
378 terms_id,
379 ship_via_lookup_code,
380 fob_lookup_code,
381 freight_terms_lookup_code,
382 status_lookup_code,
383 currency_code,
384 rate_type,
385 rate_date,
386 rate,
387 from_header_id,
388 from_type_lookup_code,
389 start_date,
390 end_date,
391 blanket_total_amount,
392 authorization_status,
393 revision_num,
394 revised_date,
395 approved_flag,
396 approved_date,
397 amount_limit,
398 min_release_amount,
399 note_to_authorizer,
400 note_to_vendor,
401 note_to_receiver,
402 print_count,
403 printed_date,
404 vendor_order_num,
405 confirming_order_flag,
406 comments,
407 reply_date,
408 reply_method_lookup_code,
409 rfq_close_date,
410 quote_type_lookup_code,
411 quotation_class_code,
412 quote_warning_delay_unit,
413 quote_warning_delay,
414 quote_vendor_quote_number,
415 acceptance_required_flag,
416 acceptance_due_date,
417 closed_date,
418 user_hold_flag,
419 approval_required_flag,
420 cancel_flag,
421 firm_status_lookup_code,
422 firm_date,
423 frozen_flag,
424 supply_agreement_flag,
425 edi_processed_flag,
426 edi_processed_status,
427 attribute_category,
428 attribute1,
429 attribute2,
430 attribute3,
431 attribute4,
432 attribute5,
433 attribute6,
434 attribute7,
435 attribute8,
436 attribute9,
437 attribute10,
438 attribute11,
439 attribute12,
440 attribute13,
441 attribute14,
442 attribute15,
443 closed_code,
444 ussgl_transaction_code,
445 government_context,
446 request_id,
447 program_application_id,
448 program_id,
449 program_update_date,
450 org_id,
451 global_attribute_category,
452 global_attribute1,
453 global_attribute2,
454 global_attribute3,
455 global_attribute4,
456 global_attribute5,
457 global_attribute6,
458 global_attribute7,
459 global_attribute8,
460 global_attribute9,
461 global_attribute10,
462 global_attribute11,
463 global_attribute12,
464 global_attribute13,
465 global_attribute14,
466 global_attribute15,
467 global_attribute16,
468 global_attribute17,
469 global_attribute18,
470 global_attribute19,
471 global_attribute20,
472 interface_source_code,
473 reference_num,
474 wf_item_type,
475 wf_item_key,
476 mrc_rate_type,
477 mrc_rate_date,
478 mrc_rate,
479 pcard_id,
480 price_update_tolerance,
481 pay_on_code,
482 xml_flag,
483 xml_send_date,
484 xml_change_send_date,
485 global_agreement_flag,
486 consigned_consumption_flag,
487 cbc_accounting_date,
488 consume_req_demand_flag,
489 change_requested_by,
490 shipping_control,
491 conterms_exist_flag,
492 conterms_articles_upd_date,
493 conterms_deliv_upd_date,
494 encumbrance_required_flag,
495 pending_signature_flag,
496 change_summary,
497 document_creation_method,
498 submit_date,
499 supplier_notif_method,
500 fax,
501 email_address,
502 retro_price_comm_updates_flag,
503 retro_price_apply_updates_flag,
504 update_sourcing_rules_flag,
505 auto_sourcing_flag,
506 created_language,
507 cpa_reference,
508 style_id,
509 tax_attribute_update_code, -- <ETAX INTEGRATION R12>
510 supplier_auth_enabled_flag, -- bug5022835
511 cat_admin_auth_enabled_flag, -- bug5022835
512 pay_when_paid, -- E and C ER
513 uda_template_id,
514 uda_template_date,
515 clm_effective_date,
516 clm_vendor_offer_number,
517 clm_award_administrator,
518 clm_no_signed_copies_to_return,
519 clm_min_guarantee_award_amt,
520 clm_min_guar_award_amt_percent,
521 clm_min_order_amount,
522 clm_max_order_amount,
523 clm_amt_synced_to_agreement,
524 clm_amount_released,
525 clm_external_idv,
526 clm_supplier_name,
527 clm_supplier_site_name,
528 clm_document_number,
529 clm_source_document_id,
530 clm_issuing_office,
531 clm_cotr_office,
532 clm_cotr_contact,
533 clm_priority_code,
534 clm_standard_form,
535 clm_document_format,
536 clm_mod_issuing_office,
537 ame_approval_id,
538 ame_transaction_type,
539 clm_award_type,
540 clm_contract_officer,
541 umbrella_program_id,
542 fon_ref_id,
543 clm_contract_finance_code, --<Sunset Memo>
544 clm_special_contract_type, --<Sunset Memo>
545 clm_payment_instr_code --<Payment Instructions>
546 )
547 SELECT
548 p_draft_id, -- Always 0 for action View Base Document (Revision 0)
549 NULL,
550 NULL,
551 'NOCHANGE',
552 po_header_id,
553 agent_id,
554 type_lookup_code,
555 last_update_date,
556 last_updated_by,
557 segment1,
558 summary_flag,
559 enabled_flag,
560 segment2,
561 segment3,
562 segment4,
563 segment5,
564 start_date_active,
565 end_date_active,
566 last_update_login,
567 creation_date,
568 created_by,
569 vendor_id,
570 vendor_site_id,
571 vendor_contact_id,
572 ship_to_location_id,
573 bill_to_location_id,
574 terms_id,
575 ship_via_lookup_code,
576 fob_lookup_code,
577 freight_terms_lookup_code,
578 status_lookup_code,
579 currency_code,
580 rate_type,
581 rate_date,
582 rate,
583 from_header_id,
584 from_type_lookup_code,
585 start_date,
586 end_date,
587 blanket_total_amount,
588 authorization_status,
589 revision_num,
590 revised_date,
591 approved_flag,
592 approved_date,
593 amount_limit,
594 min_release_amount,
595 note_to_authorizer,
596 note_to_vendor,
597 note_to_receiver,
598 print_count,
599 printed_date,
600 vendor_order_num,
601 confirming_order_flag,
602 comments,
603 reply_date,
604 reply_method_lookup_code,
605 rfq_close_date,
606 quote_type_lookup_code,
607 quotation_class_code,
608 quote_warning_delay_unit,
609 quote_warning_delay,
610 quote_vendor_quote_number,
611 acceptance_required_flag,
612 acceptance_due_date,
613 closed_date,
614 user_hold_flag,
615 approval_required_flag,
616 cancel_flag,
617 firm_status_lookup_code,
618 firm_date,
619 frozen_flag,
620 supply_agreement_flag,
621 edi_processed_flag,
622 edi_processed_status,
623 attribute_category,
624 attribute1,
625 attribute2,
626 attribute3,
627 attribute4,
628 attribute5,
629 attribute6,
630 attribute7,
631 attribute8,
632 attribute9,
633 attribute10,
634 attribute11,
635 attribute12,
636 attribute13,
637 attribute14,
638 attribute15,
639 closed_code,
640 ussgl_transaction_code,
641 government_context,
642 request_id,
643 program_application_id,
644 program_id,
645 program_update_date,
646 org_id,
647 global_attribute_category,
648 global_attribute1,
649 global_attribute2,
650 global_attribute3,
651 global_attribute4,
652 global_attribute5,
653 global_attribute6,
654 global_attribute7,
655 global_attribute8,
656 global_attribute9,
657 global_attribute10,
658 global_attribute11,
659 global_attribute12,
660 global_attribute13,
661 global_attribute14,
662 global_attribute15,
663 global_attribute16,
664 global_attribute17,
665 global_attribute18,
666 global_attribute19,
667 global_attribute20,
668 interface_source_code,
669 reference_num,
670 wf_item_type,
671 wf_item_key,
672 mrc_rate_type,
673 mrc_rate_date,
674 mrc_rate,
675 pcard_id,
676 price_update_tolerance,
677 pay_on_code,
678 xml_flag,
679 xml_send_date,
680 xml_change_send_date,
681 global_agreement_flag,
682 consigned_consumption_flag,
683 cbc_accounting_date,
684 consume_req_demand_flag,
685 change_requested_by,
686 shipping_control,
687 conterms_exist_flag,
688 conterms_articles_upd_date,
689 conterms_deliv_upd_date,
690 encumbrance_required_flag,
691 pending_signature_flag,
692 change_summary,
693 document_creation_method,
694 submit_date,
695 supplier_notif_method,
696 fax,
697 email_address,
698 retro_price_comm_updates_flag,
699 retro_price_apply_updates_flag,
700 update_sourcing_rules_flag,
701 auto_sourcing_flag,
702 created_language,
703 cpa_reference,
704 style_id,
705 tax_attribute_update_code, -- <ETAX INTEGRATION R12>
706 supplier_auth_enabled_flag, -- bug5022835
707 cat_admin_auth_enabled_flag, -- bug5022835
708 pay_when_paid, -- E and C ER
709 uda_template_id,
710 uda_template_date,
711 clm_effective_date,
712 clm_vendor_offer_number,
713 clm_award_administrator,
714 clm_no_signed_copies_to_return,
715 clm_min_guarantee_award_amt,
716 clm_min_guar_award_amt_percent,
717 clm_min_order_amount,
718 clm_max_order_amount,
719 clm_amt_synced_to_agreement,
720 clm_amount_released,
721 clm_external_idv,
722 clm_supplier_name,
723 clm_supplier_site_name,
724 clm_document_number,
725 clm_source_document_id,
726 clm_issuing_office,
727 clm_cotr_office,
728 clm_cotr_contact,
729 clm_priority_code,
730 clm_standard_form,
731 clm_document_format,
732 clm_mod_issuing_office,
733 ame_approval_id,
734 ame_transaction_type,
735 clm_award_type,
736 clm_contract_officer,
737 umbrella_program_id,
738 fon_ref_id,
739 clm_contract_finance_code, --<Sunset Memo>
740 clm_special_contract_type, --<Sunset Memo>
741 clm_payment_instr_code --<Payment Instructions>
742 FROM po_headers_archive_all
743 WHERE po_header_id = p_document_id
744 AND revision_num = p_revision_num;
745
746
747 d_position := 20;
748 IF (PO_LOG.d_stmt) THEN
749 PO_LOG.stmt(d_module, d_position, 'transfer count = ' || SQL%ROWCOUNT);
750 END IF;
751
752 d_position := 30;
753
754 IF (PO_LOG.d_proc) THEN
755 PO_LOG.proc_end(d_module);
756 END IF;
757
758 EXCEPTION
759 WHEN OTHERS THEN
760 PO_MESSAGE_S.add_exc_msg
761 ( p_pkg_name => d_pkg_name,
762 p_procedure_name => d_api_name || '.' || d_position
763 );
764 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
765 END SYNC_HEADER;
766
767 -------------------------------------------------------------------------------
768 --Start of Comments
769 --Name: SYNC_LINES
770 --Pre-reqs:
771 -- None.
772 --Modifies:
773 -- PO_LINES_DRAFT_ALL
774 --Locks:
775 -- None.
776 --Function:
777 -- Arcives the po document lines.
778 --Parameters:
779 --IN:
780 --p_document_id
781 -- The id of the document that needs to be synced.
782 --p_revision_num
783 -- The revision number of the document that needs to be synced
784 --p_draft_id
785 -- The draft_id using which the record has to be inserted at the time of sync
786
787 --Testing:
788 -- None.
789 --End of Comments
790 -------------------------------------------------------------------------------
791 PROCEDURE SYNC_LINES(p_document_id IN NUMBER,
792 p_revision_num IN NUMBER,
793 p_draft_id IN NUMBER)
794 IS
795 d_api_name CONSTANT VARCHAR2(30) := 'SYNC_LINES';
796 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
797 d_position NUMBER;
798
799 BEGIN
800
801 d_position := 0;
802 IF (PO_LOG.d_proc) THEN
803 PO_LOG.proc_begin(d_module,'p_po_header_id',p_document_id);
804 END IF;
805
806 d_position := 10;
807 IF (PO_LOG.d_stmt) THEN
808 PO_LOG.stmt(d_module, d_position, 'transfer records from archive to dft');
809 END IF;
810
811 INSERT INTO po_lines_draft_all
812 (
813 draft_id,
814 delete_flag,
815 change_accepted_flag,
816 change_status,
817 po_line_id,
818 last_update_date,
819 last_updated_by,
820 po_header_id,
821 line_type_id,
822 line_num,
823 last_update_login,
824 creation_date,
825 created_by,
826 item_id,
827 item_revision,
828 category_id,
829 item_description,
830 unit_meas_lookup_code,
831 quantity_committed,
832 committed_amount,
833 allow_price_override_flag,
834 not_to_exceed_price,
835 list_price_per_unit,
836 unit_price,
837 quantity,
838 un_number_id,
839 hazard_class_id,
840 note_to_vendor,
841 from_header_id,
842 from_line_id,
843 from_line_location_id,
844 min_order_quantity,
845 max_order_quantity,
846 qty_rcv_tolerance,
847 over_tolerance_error_flag,
848 market_price,
849 unordered_flag,
850 closed_flag,
851 user_hold_flag,
852 cancel_flag,
853 cancelled_by,
854 cancel_date,
855 cancel_reason,
856 firm_status_lookup_code,
857 firm_date,
858 vendor_product_num,
859 contract_num,
860 taxable_flag,
861 tax_name,
862 type_1099,
863 capital_expense_flag,
864 negotiated_by_preparer_flag,
865 attribute_category,
866 attribute1,
867 attribute2,
868 attribute3,
869 attribute4,
870 attribute5,
871 attribute6,
872 attribute7,
873 attribute8,
874 attribute9,
875 attribute10,
876 reference_num,
877 attribute11,
878 attribute12,
879 attribute13,
880 attribute14,
881 attribute15,
882 min_release_amount,
883 price_type_lookup_code,
884 closed_code,
885 price_break_lookup_code,
886 ussgl_transaction_code,
887 government_context,
888 request_id,
889 program_application_id,
890 program_id,
891 program_update_date,
892 closed_date,
893 closed_reason,
894 closed_by,
895 transaction_reason_code,
896 org_id,
897 qc_grade,
898 base_uom,
899 base_qty,
900 secondary_uom,
901 secondary_qty,
902 global_attribute_category,
903 global_attribute1,
904 global_attribute2,
905 global_attribute3,
906 global_attribute4,
907 global_attribute5,
908 global_attribute6,
909 global_attribute7,
910 global_attribute8,
911 global_attribute9,
912 global_attribute10,
913 global_attribute11,
914 global_attribute12,
915 global_attribute13,
916 global_attribute14,
917 global_attribute15,
918 global_attribute16,
919 global_attribute17,
920 global_attribute18,
921 global_attribute19,
922 global_attribute20,
923 line_reference_num,
924 project_id,
925 task_id,
926 expiration_date,
927 tax_code_id,
928 oke_contract_header_id,
929 oke_contract_version_id,
930 secondary_quantity,
931 secondary_unit_of_measure,
932 preferred_grade,
933 auction_header_id,
934 auction_display_number,
935 auction_line_number,
936 bid_number,
937 bid_line_number,
938 retroactive_date,
939 supplier_ref_number,
940 contract_id,
941 start_date,
942 amount,
943 job_id,
944 contractor_first_name,
945 contractor_last_name,
946 order_type_lookup_code,
947 purchase_basis,
948 matching_basis,
949 svc_amount_notif_sent,
950 svc_completion_notif_sent,
951 base_unit_price,
952 manual_price_change_flag,
953 -- <Complex Work R12 Start>
954 retainage_rate,
955 max_retainage_amount,
956 progress_payment_rate,
957 recoupment_rate,
958 -- <Complex Work R12 End>
959 catalog_name,
960 supplier_part_auxid,
961 ip_category_id,
962 tax_attribute_update_code, -- <ETAX R12>
963 line_num_display,
964 group_line_id,
965 clm_info_flag,
966 clm_option_indicator,
967 clm_base_line_num,
968 clm_option_num,
969 clm_option_from_date,
970 clm_option_to_date,
971 clm_funded_flag,
972 contract_type,
973 cost_constraint,
974 clm_idc_type,
975 uda_template_id,
976 clm_min_total_amount,
977 clm_max_total_amount,
978 clm_min_total_quantity,
979 clm_max_total_quantity,
980 clm_min_order_amount,
981 clm_max_order_amount,
982 clm_min_order_quantity,
983 clm_max_order_quantity,
984 clm_total_amount_ordered,
985 clm_total_quantity_ordered,
986 clm_fsc_psc,
987 clm_mdaps_mais,
988 clm_naics,
989 clm_order_start_date,
990 clm_order_end_date,
991 clm_exercised_flag,
992 clm_exercised_date,
993 old_quantity,
994 clm_pop_exception_reason, --<Sunset Memo>
995 clm_delivery_event_code, --<Event Based Delivery>
996 clm_payment_instr_code, --<Payment Instructions>
997 CLM_UNDEF_FLAG, -- UCA Project Begin
998 CLM_UNDEF_ACTION_CODE,
999 CLM_APPROVED_UNDEF_AMOUNT,
1000 CLM_UDA_PRICING_TOTAL, --UCA PRoject End
1001 CLM_EXHIBIT_NAME -- Elins project
1002
1003 )
1004 SELECT
1005 p_draft_id, -- Always 0 for action View Base Document (Revision 0)
1006 NULL,
1007 NULL,
1008 'NOCHANGE',
1009 po_line_id,
1010 last_update_date,
1011 last_updated_by,
1012 po_header_id,
1013 line_type_id,
1014 line_num,
1015 last_update_login,
1016 creation_date,
1017 created_by,
1018 item_id,
1019 item_revision,
1020 category_id,
1021 item_description,
1022 unit_meas_lookup_code,
1023 quantity_committed,
1024 committed_amount,
1025 allow_price_override_flag,
1026 not_to_exceed_price,
1027 list_price_per_unit,
1028 unit_price,
1029 quantity,
1030 un_number_id,
1031 hazard_class_id,
1032 note_to_vendor,
1033 from_header_id,
1034 from_line_id,
1035 from_line_location_id,
1036 min_order_quantity,
1037 max_order_quantity,
1038 qty_rcv_tolerance,
1039 over_tolerance_error_flag,
1040 market_price,
1041 unordered_flag,
1042 closed_flag,
1043 user_hold_flag,
1044 cancel_flag,
1045 cancelled_by,
1046 cancel_date,
1047 cancel_reason,
1048 firm_status_lookup_code,
1049 firm_date,
1050 vendor_product_num,
1051 contract_num,
1052 taxable_flag,
1053 tax_name,
1054 type_1099,
1055 capital_expense_flag,
1056 negotiated_by_preparer_flag,
1057 attribute_category,
1058 attribute1,
1059 attribute2,
1060 attribute3,
1061 attribute4,
1062 attribute5,
1063 attribute6,
1064 attribute7,
1065 attribute8,
1066 attribute9,
1067 attribute10,
1068 reference_num,
1069 attribute11,
1070 attribute12,
1071 attribute13,
1072 attribute14,
1073 attribute15,
1074 min_release_amount,
1075 price_type_lookup_code,
1076 closed_code,
1077 price_break_lookup_code,
1078 ussgl_transaction_code,
1079 government_context,
1080 request_id,
1081 program_application_id,
1082 program_id,
1083 program_update_date,
1084 closed_date,
1085 closed_reason,
1086 closed_by,
1087 transaction_reason_code,
1088 org_id,
1089 qc_grade,
1090 base_uom,
1091 base_qty,
1092 secondary_uom,
1093 secondary_qty,
1094 global_attribute_category,
1095 global_attribute1,
1096 global_attribute2,
1097 global_attribute3,
1098 global_attribute4,
1099 global_attribute5,
1100 global_attribute6,
1101 global_attribute7,
1102 global_attribute8,
1103 global_attribute9,
1104 global_attribute10,
1105 global_attribute11,
1106 global_attribute12,
1107 global_attribute13,
1108 global_attribute14,
1109 global_attribute15,
1110 global_attribute16,
1111 global_attribute17,
1112 global_attribute18,
1113 global_attribute19,
1114 global_attribute20,
1115 line_reference_num,
1116 project_id,
1117 task_id,
1118 expiration_date,
1119 tax_code_id,
1120 oke_contract_header_id,
1121 oke_contract_version_id,
1122 secondary_quantity,
1123 secondary_unit_of_measure,
1124 preferred_grade,
1125 auction_header_id,
1126 auction_display_number,
1127 auction_line_number,
1128 bid_number,
1129 bid_line_number,
1130 retroactive_date,
1131 supplier_ref_number,
1132 contract_id,
1133 start_date,
1134 amount,
1135 job_id,
1136 contractor_first_name,
1137 contractor_last_name,
1138 order_type_lookup_code,
1139 purchase_basis,
1140 matching_basis,
1141 svc_amount_notif_sent,
1142 svc_completion_notif_sent,
1143 base_unit_price,
1144 manual_price_change_flag,
1145 -- <Complex Work R12 Start>
1146 retainage_rate,
1147 max_retainage_amount,
1148 progress_payment_rate,
1149 recoupment_rate,
1150 -- <Complex Work R12 End>
1151 catalog_name,
1152 supplier_part_auxid,
1153 ip_category_id,
1154 tax_attribute_update_code, -- <ETAX R12>
1155 line_num_display,
1156 group_line_id,
1157 clm_info_flag,
1158 clm_option_indicator,
1159 clm_base_line_num,
1160 clm_option_num,
1161 clm_option_from_date,
1162 clm_option_to_date,
1163 clm_funded_flag,
1164 contract_type,
1165 cost_constraint,
1166 clm_idc_type,
1167 uda_template_id,
1168 clm_min_total_amount,
1169 clm_max_total_amount,
1170 clm_min_total_quantity,
1171 clm_max_total_quantity,
1172 clm_min_order_amount,
1173 clm_max_order_amount,
1174 clm_min_order_quantity,
1175 clm_max_order_quantity,
1176 clm_total_amount_ordered,
1177 clm_total_quantity_ordered,
1178 clm_fsc_psc,
1179 clm_mdaps_mais,
1180 clm_naics,
1181 clm_order_start_date,
1182 clm_order_end_date,
1183 clm_exercised_flag,
1184 clm_exercised_date,
1185 quantity,
1186 clm_pop_exception_reason, --<Sunset Memo>
1187 clm_delivery_event_code, --<Event Based Delivery>
1188 clm_payment_instr_code, --<Payment Instructions>
1189 CLM_UNDEF_FLAG, -- UCA Project Begin
1190 CLM_UNDEF_ACTION_CODE,
1191 CLM_APPROVED_UNDEF_AMOUNT,
1192 CLM_UDA_PRICING_TOTAL , --UCA PRoject End
1193 CLM_EXHIBIT_NAME -- Elins project
1194
1195 FROM po_lines_archive_all
1196 WHERE po_header_id = p_document_id
1197 AND revision_num = p_revision_num;
1198 --UCA Project - CLM R4 changes Begin;
1199
1200 d_position := 20;
1201 IF (PO_LOG.d_stmt) THEN
1202 PO_LOG.stmt(d_module, d_position, 'transfer count = ' || SQL%ROWCOUNT);
1203 END IF;
1204
1205 d_position := 30;
1206
1207 IF (PO_LOG.d_proc) THEN
1208 PO_LOG.proc_end(d_module);
1209 END IF;
1210
1211 EXCEPTION
1212 WHEN OTHERS THEN
1213 PO_MESSAGE_S.add_exc_msg
1214 ( p_pkg_name => d_pkg_name,
1215 p_procedure_name => d_api_name || '.' || d_position
1216 );
1217 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1218 END SYNC_LINES;
1219
1220 -------------------------------------------------------------------------------
1221 --Start of Comments
1222 --Name: SYNC_LINE_LOCATIONS
1223 --Pre-reqs:
1224 -- None.
1225 --Modifies:
1226 -- PO_LINE_LOCATIONS_DRAFT_ALL
1227 --Locks:
1228 -- None.
1229 --Function:
1230 -- Syncs the po document line locations Draft.
1231 --Parameters:
1232 --IN:
1233 --p_document_id
1234 -- The id of the document that needs to be synced.
1235 --p_revision_num
1236 -- The revision number of the document that needs to be synced
1237 --p_draft_id
1238 -- The draft_id using which the record has to be inserted at the time of sync
1239 --Testing:
1240 -- None.
1241 --End of Comments
1242 -------------------------------------------------------------------------------
1243 PROCEDURE SYNC_LINE_LOCATIONS(p_document_id IN NUMBER,
1244 p_revision_num IN NUMBER,
1245 p_draft_id IN NUMBER)
1246 IS
1247 d_api_name CONSTANT VARCHAR2(30) := 'SYNC_LINE_LOCATIONS';
1248 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
1249 d_position NUMBER;
1250
1251 BEGIN
1252
1253 d_position := 0;
1254 IF (PO_LOG.d_proc) THEN
1255 PO_LOG.proc_begin(d_module,'p_po_header_id',p_document_id);
1256 END IF;
1257
1258 d_position := 10;
1259 IF (PO_LOG.d_stmt) THEN
1260 PO_LOG.stmt(d_module, d_position, 'transfer records from archive to dft');
1261 END IF;
1262
1263 INSERT INTO po_line_locations_draft_all
1264 ( draft_id,
1265 delete_flag,
1266 change_accepted_flag,
1267 change_status,
1268 line_location_id,
1269 last_update_date,
1270 last_updated_by,
1271 po_header_id,
1272 po_line_id,
1273 last_update_login,
1274 creation_date,
1275 created_by,
1276 quantity,
1277 quantity_accepted,
1278 quantity_received,
1279 quantity_rejected,
1280 quantity_billed,
1281 quantity_cancelled,
1282 unit_meas_lookup_code,
1283 po_release_id,
1284 ship_to_location_id,
1285 ship_via_lookup_code,
1286 need_by_date,
1287 promised_date,
1288 last_accept_date,
1289 price_override,
1290 encumbered_flag,
1291 encumbered_date,
1292 unencumbered_quantity,
1293 fob_lookup_code,
1294 freight_terms_lookup_code,
1295 taxable_flag,
1296 tax_name,
1297 estimated_tax_amount,
1298 from_header_id,
1299 from_line_id,
1300 from_line_location_id,
1301 start_date,
1302 end_date,
1303 lead_time,
1304 lead_time_unit,
1305 price_discount,
1306 terms_id,
1307 approved_flag,
1308 approved_date,
1309 closed_flag,
1310 cancel_flag,
1311 cancelled_by,
1312 cancel_date,
1313 cancel_reason,
1314 firm_status_lookup_code,
1315 firm_date,
1316 attribute_category,
1317 attribute1,
1318 attribute2,
1319 attribute3,
1320 attribute4,
1321 attribute5,
1322 attribute6,
1323 attribute7,
1324 attribute8,
1325 attribute9,
1326 attribute10,
1327 unit_of_measure_class,
1328 encumber_now,
1329 attribute11,
1330 attribute12,
1331 attribute13,
1332 attribute14,
1333 attribute15,
1334 inspection_required_flag,
1335 receipt_required_flag,
1336 qty_rcv_tolerance,
1337 qty_rcv_exception_code,
1338 enforce_ship_to_location_code,
1339 allow_substitute_receipts_flag,
1340 days_early_receipt_allowed,
1341 days_late_receipt_allowed,
1342 receipt_days_exception_code,
1343 invoice_close_tolerance,
1344 receive_close_tolerance,
1345 ship_to_organization_id,
1346 shipment_num,
1347 source_shipment_id,
1348 shipment_type,
1349 closed_code,
1350 request_id,
1351 program_application_id,
1352 program_id,
1353 program_update_date,
1354 ussgl_transaction_code,
1355 government_context,
1356 receiving_routing_id,
1357 accrue_on_receipt_flag,
1358 closed_reason,
1359 closed_date,
1360 closed_by,
1361 org_id,
1362 quantity_shipped,
1363 global_attribute_category,
1364 global_attribute1,
1365 global_attribute2,
1366 global_attribute3,
1367 global_attribute4,
1368 global_attribute5,
1369 global_attribute6,
1370 global_attribute7,
1371 global_attribute8,
1372 global_attribute9,
1373 global_attribute10,
1374 global_attribute11,
1375 global_attribute12,
1376 global_attribute13,
1377 global_attribute14,
1378 global_attribute15,
1379 global_attribute16,
1380 global_attribute17,
1381 global_attribute18,
1382 global_attribute19,
1383 global_attribute20,
1384 country_of_origin_code,
1385 tax_user_override_flag,
1386 match_option,
1387 tax_code_id,
1388 calculate_tax_flag,
1389 change_promised_date_reason,
1390 note_to_receiver,
1391 secondary_quantity,
1392 secondary_unit_of_measure,
1393 preferred_grade,
1394 secondary_quantity_received,
1395 secondary_quantity_accepted,
1396 secondary_quantity_rejected,
1397 secondary_quantity_cancelled,
1398 secondary_quantity_shipped,
1399 vmi_flag,
1400 consigned_flag,
1401 retroactive_date,
1402 supplier_order_line_number,
1403 amount,
1404 amount_received,
1405 amount_billed,
1406 amount_cancelled,
1407 amount_rejected,
1408 amount_accepted,
1409 drop_ship_flag,
1410 sales_order_update_date,
1411 transaction_flow_header_id,
1412 final_match_flag,
1413 manual_price_change_flag,
1414 shipment_closed_date,
1415 closed_for_receiving_date,
1416 closed_for_invoice_date,
1417 -- <Complex Work R12 Start>
1418 value_basis,
1419 matching_basis,
1420 payment_type,
1421 description,
1422 work_approver_id,
1423 bid_payment_id,
1424 quantity_financed,
1425 amount_financed,
1426 quantity_recouped,
1427 amount_recouped,
1428 retainage_withheld_amount,
1429 retainage_released_amount,
1430 -- <Complex Work R12 End>
1431 outsourced_assembly,
1432 tax_attribute_update_code, -- <ETAX R12>
1433 uda_template_id,
1434 clm_period_perf_end_date,
1435 clm_period_perf_start_date,
1436 old_quantity,
1437 clm_delivery_period, --<Event Based Delivery>
1438 clm_delivery_period_uom, --<Event Based Delivery>
1439 clm_promise_period, --<Event Based Delivery>
1440 clm_promise_period_uom, --<Event Based Delivery>
1441 clm_pop_duration, --<Event Based Delivery>
1442 clm_pop_duration_uom --<Event Based Delivery>
1443 )
1444 SELECT
1445 p_draft_id, -- Always 0 for action View Base Document (Revision 0)
1446 NULL,
1447 NULL,
1448 'NOCHANGE',
1449 line_location_id,
1450 last_update_date,
1451 last_updated_by,
1452 po_header_id,
1453 po_line_id,
1454 last_update_login,
1455 creation_date,
1456 created_by,
1457 quantity,
1458 quantity_accepted,
1459 quantity_received,
1460 quantity_rejected,
1461 quantity_billed,
1462 quantity_cancelled,
1463 unit_meas_lookup_code,
1464 po_release_id,
1465 ship_to_location_id,
1466 ship_via_lookup_code,
1467 need_by_date,
1468 promised_date,
1469 last_accept_date,
1470 price_override,
1471 encumbered_flag,
1472 encumbered_date,
1473 unencumbered_quantity,
1474 fob_lookup_code,
1475 freight_terms_lookup_code,
1476 taxable_flag,
1477 tax_name,
1478 estimated_tax_amount,
1479 from_header_id,
1480 from_line_id,
1481 from_line_location_id,
1482 start_date,
1483 end_date,
1484 lead_time,
1485 lead_time_unit,
1486 price_discount,
1487 terms_id,
1488 approved_flag,
1489 approved_date,
1490 closed_flag,
1491 cancel_flag,
1492 cancelled_by,
1493 cancel_date,
1494 cancel_reason,
1495 firm_status_lookup_code,
1496 firm_date,
1497 attribute_category,
1498 attribute1,
1499 attribute2,
1500 attribute3,
1501 attribute4,
1502 attribute5,
1503 attribute6,
1504 attribute7,
1505 attribute8,
1506 attribute9,
1507 attribute10,
1508 unit_of_measure_class,
1509 encumber_now,
1510 attribute11,
1511 attribute12,
1512 attribute13,
1513 attribute14,
1514 attribute15,
1515 inspection_required_flag,
1516 receipt_required_flag,
1517 qty_rcv_tolerance,
1518 qty_rcv_exception_code,
1519 enforce_ship_to_location_code,
1520 allow_substitute_receipts_flag,
1521 days_early_receipt_allowed,
1522 days_late_receipt_allowed,
1523 receipt_days_exception_code,
1524 invoice_close_tolerance,
1525 receive_close_tolerance,
1526 ship_to_organization_id,
1527 shipment_num,
1528 source_shipment_id,
1529 shipment_type,
1530 closed_code,
1531 request_id,
1532 program_application_id,
1533 program_id,
1534 program_update_date,
1535 ussgl_transaction_code,
1536 government_context,
1537 receiving_routing_id,
1538 accrue_on_receipt_flag,
1539 closed_reason,
1540 closed_date,
1541 closed_by,
1542 org_id,
1543 quantity_shipped,
1544 global_attribute_category,
1545 global_attribute1,
1546 global_attribute2,
1547 global_attribute3,
1548 global_attribute4,
1549 global_attribute5,
1550 global_attribute6,
1551 global_attribute7,
1552 global_attribute8,
1553 global_attribute9,
1554 global_attribute10,
1555 global_attribute11,
1556 global_attribute12,
1557 global_attribute13,
1558 global_attribute14,
1559 global_attribute15,
1560 global_attribute16,
1561 global_attribute17,
1562 global_attribute18,
1563 global_attribute19,
1564 global_attribute20,
1565 country_of_origin_code,
1566 tax_user_override_flag,
1567 match_option,
1568 tax_code_id,
1569 calculate_tax_flag,
1570 change_promised_date_reason,
1571 note_to_receiver,
1572 secondary_quantity,
1573 secondary_unit_of_measure,
1574 preferred_grade,
1575 secondary_quantity_received,
1576 secondary_quantity_accepted,
1577 secondary_quantity_rejected,
1578 secondary_quantity_cancelled,
1579 secondary_quantity_shipped,
1580 vmi_flag,
1581 consigned_flag,
1582 retroactive_date,
1583 supplier_order_line_number,
1584 amount,
1585 amount_received,
1586 amount_billed,
1587 amount_cancelled,
1588 amount_rejected,
1589 amount_accepted,
1590 drop_ship_flag,
1591 sales_order_update_date,
1592 transaction_flow_header_id,
1593 final_match_flag,
1594 manual_price_change_flag,
1595 shipment_closed_date,
1596 closed_for_receiving_date,
1597 closed_for_invoice_date,
1598 -- <Complex Work R12 Start>
1599 value_basis,
1600 matching_basis,
1601 payment_type,
1602 description,
1603 work_approver_id,
1604 bid_payment_id,
1605 quantity_financed,
1606 amount_financed,
1607 quantity_recouped,
1608 amount_recouped,
1609 retainage_withheld_amount,
1610 retainage_released_amount,
1611 -- <Complex Work R12 End>
1612 outsourced_assembly,
1613 tax_attribute_update_code, -- <ETAX R12>
1614 uda_template_id,
1615 clm_period_perf_end_date,
1616 clm_period_perf_start_date,
1617 quantity,
1618 clm_delivery_period, --<Event Based Delivery>
1619 clm_delivery_period_uom, --<Event Based Delivery>
1620 clm_promise_period, --<Event Based Delivery>
1621 clm_promise_period_uom, --<Event Based Delivery>
1622 clm_pop_duration, --<Event Based Delivery>
1623 clm_pop_duration_uom --<Event Based Delivery>
1624 FROM po_line_locations_archive_all
1625 WHERE po_header_id = p_document_id
1626 AND revision_num = p_revision_num;
1627
1628 d_position := 20;
1629 IF (PO_LOG.d_stmt) THEN
1630 PO_LOG.stmt(d_module, d_position, 'transfer count = ' || SQL%ROWCOUNT);
1631 END IF;
1632
1633 d_position := 30;
1634
1635 IF (PO_LOG.d_proc) THEN
1636 PO_LOG.proc_end(d_module);
1637 END IF;
1638
1639 EXCEPTION
1640 WHEN OTHERS THEN
1641 PO_MESSAGE_S.add_exc_msg
1642 ( p_pkg_name => d_pkg_name,
1643 p_procedure_name => d_api_name || '.' || d_position
1644 );
1645 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1646
1647 END SYNC_LINE_LOCATIONS;
1648
1649 -------------------------------------------------------------------------------
1650 --Start of Comments
1651 --Name: SYNC_DISTRIBUTIONS
1652 --Pre-reqs:
1653 -- None.
1654 --Modifies:
1655 -- PO_DISTRIBUTIONS_DRAFT_ALL
1656 --Locks:
1657 -- None.
1658 --Function:
1659 -- Arcives the po document distributions.
1660 --Parameters:
1661 --IN:
1662 --p_document_id
1663 -- The id of the document that needs to be synced.
1664 --p_revision_num
1665 -- The revision number of the document that needs to be synced
1666 --p_draft_id
1667 -- The draft_id using which the record has to be inserted at the time of sync
1668 --Testing:
1669 -- None.
1670 --End of Comments
1671 -------------------------------------------------------------------------------
1672 PROCEDURE SYNC_DISTRIBUTIONS(p_document_id IN NUMBER,
1673 p_revision_num IN NUMBER,
1674 p_draft_id IN NUMBER)
1675 IS
1676 d_api_name CONSTANT VARCHAR2(30) := 'SYNC_DISTRIBUTIONS';
1677 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
1678 d_position NUMBER;
1679
1680 BEGIN
1681
1682 d_position := 0;
1683 IF (PO_LOG.d_proc) THEN
1684 PO_LOG.proc_begin(d_module,'p_po_header_id',p_document_id);
1685 END IF;
1686
1687 d_position := 10;
1688 IF (PO_LOG.d_stmt) THEN
1689 PO_LOG.stmt(d_module, d_position, 'transfer records from archive to dft');
1690 END IF;
1691
1692 INSERT INTO po_distributions_draft_all
1693 (
1694 draft_id,
1695 delete_flag,
1696 change_accepted_flag,
1697 change_status,
1698 po_distribution_id,
1699 last_update_date,
1700 last_updated_by,
1701 po_header_id,
1702 po_line_id,
1703 line_location_id,
1704 set_of_books_id,
1705 code_combination_id,
1706 quantity_ordered,
1707 last_update_login,
1708 creation_date,
1709 created_by,
1710 po_release_id,
1711 quantity_delivered,
1712 quantity_billed,
1713 quantity_cancelled,
1714 req_header_reference_num,
1715 req_line_reference_num,
1716 req_distribution_id,
1717 deliver_to_location_id,
1718 deliver_to_person_id,
1719 rate_date,
1720 rate,
1721 amount_billed,
1722 accrued_flag,
1723 encumbered_flag,
1724 encumbered_amount,
1725 unencumbered_quantity,
1726 unencumbered_amount,
1727 failed_funds_lookup_code,
1728 gl_encumbered_date,
1729 gl_encumbered_period_name,
1730 gl_cancelled_date,
1731 destination_type_code,
1732 destination_organization_id,
1733 destination_subinventory,
1734 attribute_category,
1735 attribute1,
1736 attribute2,
1737 attribute3,
1738 attribute4,
1739 attribute5,
1740 attribute6,
1741 attribute7,
1742 attribute8,
1743 attribute9,
1744 attribute10,
1745 attribute11,
1746 attribute12,
1747 attribute13,
1748 attribute14,
1749 attribute15,
1750 wip_entity_id,
1751 wip_operation_seq_num,
1752 wip_resource_seq_num,
1753 wip_repetitive_schedule_id,
1754 wip_line_id,
1755 bom_resource_id,
1756 budget_account_id,
1757 accrual_account_id,
1758 variance_account_id,
1759 prevent_encumbrance_flag,
1760 ussgl_transaction_code,
1761 government_context,
1762 destination_context,
1763 distribution_num,
1764 source_distribution_id,
1765 request_id,
1766 program_application_id,
1767 program_id,
1768 program_update_date,
1769 project_id,
1770 task_id,
1771 expenditure_type,
1772 project_accounting_context,
1773 expenditure_organization_id,
1774 gl_closed_date,
1775 accrue_on_receipt_flag,
1776 expenditure_item_date,
1777 org_id,
1778 kanban_card_id,
1779 award_id,
1780 mrc_rate_date,
1781 mrc_rate,
1782 mrc_encumbered_amount,
1783 mrc_unencumbered_amount,
1784 end_item_unit_number,
1785 tax_recovery_override_flag,
1786 recoverable_tax,
1787 nonrecoverable_tax,
1788 recovery_rate,
1789 oke_contract_line_id,
1790 oke_contract_deliverable_id,
1791 amount_ordered,
1792 amount_delivered,
1793 amount_cancelled,
1794 distribution_type,
1795 amount_to_encumber,
1796 invoice_adjustment_flag,
1797 dest_charge_account_id,
1798 dest_variance_account_id,
1799 -- <Complex Work R12 Start>
1800 quantity_financed,
1801 amount_financed,
1802 quantity_recouped,
1803 amount_recouped,
1804 retainage_withheld_amount,
1805 retainage_released_amount,
1806 -- <Complex Work R12 End>
1807 tax_attribute_update_code, -- <ETAX R12>
1808 group_line_id,
1809 uda_template_id,
1810 clm_misc_loa,
1811 clm_defence_funding,
1812 clm_fms_case_number,
1813 clm_agency_acct_identifier,
1814 partial_funded_flag,
1815 funded_value,
1816 quantity_funded,
1817 amount_funded,
1818 change_in_funded_value,
1819 old_quantity_ordered,
1820 clm_payment_sequence_num --<Payment Instructions>
1821 )
1822 SELECT
1823 p_draft_id, -- Always 0 for action View Base Document (Revision 0)
1824 NULL,
1825 NULL,
1826 'NOCHANGE',
1827 po_distribution_id,
1828 last_update_date,
1829 last_updated_by,
1830 po_header_id,
1831 po_line_id,
1832 line_location_id,
1833 set_of_books_id,
1834 code_combination_id,
1835 quantity_ordered,
1836 last_update_login,
1837 creation_date,
1838 created_by,
1839 po_release_id,
1840 quantity_delivered,
1841 quantity_billed,
1842 quantity_cancelled,
1843 req_header_reference_num,
1844 req_line_reference_num,
1845 req_distribution_id,
1846 deliver_to_location_id,
1847 deliver_to_person_id,
1848 rate_date,
1849 rate,
1850 amount_billed,
1851 accrued_flag,
1852 encumbered_flag,
1853 encumbered_amount,
1854 unencumbered_quantity,
1855 unencumbered_amount,
1856 failed_funds_lookup_code,
1857 gl_encumbered_date,
1858 gl_encumbered_period_name,
1859 gl_cancelled_date,
1860 destination_type_code,
1861 destination_organization_id,
1862 destination_subinventory,
1863 attribute_category,
1864 attribute1,
1865 attribute2,
1866 attribute3,
1867 attribute4,
1868 attribute5,
1869 attribute6,
1870 attribute7,
1871 attribute8,
1872 attribute9,
1873 attribute10,
1874 attribute11,
1875 attribute12,
1876 attribute13,
1877 attribute14,
1878 attribute15,
1879 wip_entity_id,
1880 wip_operation_seq_num,
1881 wip_resource_seq_num,
1882 wip_repetitive_schedule_id,
1883 wip_line_id,
1884 bom_resource_id,
1885 budget_account_id,
1886 accrual_account_id,
1887 variance_account_id,
1888 prevent_encumbrance_flag,
1889 ussgl_transaction_code,
1890 government_context,
1891 destination_context,
1892 distribution_num,
1893 source_distribution_id,
1894 request_id,
1895 program_application_id,
1896 program_id,
1897 program_update_date,
1898 project_id,
1899 task_id,
1900 expenditure_type,
1901 project_accounting_context,
1902 expenditure_organization_id,
1903 gl_closed_date,
1904 accrue_on_receipt_flag,
1905 expenditure_item_date,
1906 org_id,
1907 kanban_card_id,
1908 award_id,
1909 mrc_rate_date,
1910 mrc_rate,
1911 mrc_encumbered_amount,
1912 mrc_unencumbered_amount,
1913 end_item_unit_number,
1914 tax_recovery_override_flag,
1915 recoverable_tax,
1916 nonrecoverable_tax,
1917 recovery_rate,
1918 oke_contract_line_id,
1919 oke_contract_deliverable_id,
1920 amount_ordered,
1921 amount_delivered,
1922 amount_cancelled,
1923 distribution_type,
1924 amount_to_encumber,
1925 invoice_adjustment_flag,
1926 dest_charge_account_id,
1927 dest_variance_account_id,
1928 -- <Complex Work R12 Start>
1929 quantity_financed,
1930 amount_financed,
1931 quantity_recouped,
1932 amount_recouped,
1933 retainage_withheld_amount,
1934 retainage_released_amount,
1935 -- <Complex Work R12 End>
1936 tax_attribute_update_code, -- <ETAX R12>
1937 group_line_id,
1938 uda_template_id,
1939 clm_misc_loa,
1940 clm_defence_funding,
1941 clm_fms_case_number,
1942 clm_agency_acct_identifier,
1943 partial_funded_flag,
1944 funded_value,
1945 quantity_funded,
1946 amount_funded,
1947 0, -- CLM Partial Funding Changes
1948 quantity_ordered,
1949 clm_payment_sequence_num --<Payment Instructions>
1950 FROM po_distributions_archive_all
1951 WHERE po_header_id = p_document_id
1952 AND revision_num = p_revision_num;
1953
1954 d_position := 20;
1955 IF (PO_LOG.d_stmt) THEN
1956 PO_LOG.stmt(d_module, d_position, 'transfer count = ' || SQL%ROWCOUNT);
1957 END IF;
1958
1959 d_position := 30;
1960
1961 IF (PO_LOG.d_proc) THEN
1962 PO_LOG.proc_end(d_module);
1963 END IF;
1964
1965 EXCEPTION
1966 WHEN OTHERS THEN
1967 PO_MESSAGE_S.add_exc_msg
1968 ( p_pkg_name => d_pkg_name,
1969 p_procedure_name => d_api_name || '.' || d_position
1970 );
1971 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1972 END SYNC_DISTRIBUTIONS;
1973
1974 -------------------------------------------------------------------------------
1975 --Start of Comments
1976 --Name: SYNC_ORG_ASSIGNMENTS
1977 --Pre-reqs:
1978 -- None.
1979 --Modifies:
1980 -- PO_GA_ORG_ASSIGNMENTS_DRAFT
1981 --Locks:
1982 -- None.
1983 --Function:
1984 -- Syncs the global agreement org assignments.
1985 --Parameters:
1986 --IN:
1987 --p_document_id
1988 -- The id of the document that needs to be synced.
1989 --p_revision_num
1990 -- The revision number of the document that needs to be synced
1991 --p_draft_id
1992 -- The draft_id using which the record has to be inserted at the time of sync
1993 --Testing:
1994 -- None.
1995 --End of Comments
1996 -------------------------------------------------------------------------------
1997 PROCEDURE SYNC_ORG_ASSIGNMENTS(p_document_id IN NUMBER,
1998 p_revision_num IN NUMBER,
1999 p_draft_id IN NUMBER)
2000 IS
2001 d_api_name CONSTANT VARCHAR2(30) := 'SYNC_ORG_ASSIGNMENTS';
2002 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
2003 d_position NUMBER;
2004
2005 BEGIN
2006
2007 d_position := 0;
2008 IF (PO_LOG.d_proc) THEN
2009 PO_LOG.proc_begin(d_module,'p_po_header_id',p_document_id);
2010 END IF;
2011
2012 d_position := 10;
2013 IF (PO_LOG.d_stmt) THEN
2014 PO_LOG.stmt(d_module, d_position, 'transfer records from archive to dft');
2015 END IF;
2016
2017 INSERT INTO po_ga_org_assign_draft
2018 (
2019 draft_id,
2020 delete_flag,
2021 change_accepted_flag,
2022 change_status,
2023 org_assignment_id,
2024 po_header_id,
2025 organization_id,
2026 enabled_flag,
2027 vendor_site_id,
2028 last_update_date,
2029 last_updated_by,
2030 creation_date,
2031 created_by,
2032 last_update_login,
2033 purchasing_org_id
2034 )
2035 SELECT
2036 p_draft_id, -- Always 0 for action View Base Document (Revision 0)
2037 NULL,
2038 NULL,
2039 'NOCHANGE',
2040 org_assignment_id,
2041 po_header_id,
2042 organization_id,
2043 enabled_flag,
2044 vendor_site_id,
2045 last_update_date,
2046 last_updated_by,
2047 creation_date,
2048 created_by,
2049 last_update_login,
2050 purchasing_org_id
2051 FROM po_ga_org_assignments_archive
2052 WHERE po_header_id = p_document_id
2053 AND revision_num = p_revision_num;
2054
2055 d_position := 20;
2056 IF (PO_LOG.d_stmt) THEN
2057 PO_LOG.stmt(d_module, d_position, 'transfer count = ' || SQL%ROWCOUNT);
2058 END IF;
2059
2060 d_position := 30;
2061
2062 IF (PO_LOG.d_proc) THEN
2063 PO_LOG.proc_end(d_module);
2064 END IF;
2065
2066 EXCEPTION
2067 WHEN OTHERS THEN
2068 PO_MESSAGE_S.add_exc_msg
2069 ( p_pkg_name => d_pkg_name,
2070 p_procedure_name => d_api_name || '.' || d_position
2071 );
2072 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2073
2074 END SYNC_ORG_ASSIGNMENTS;
2075
2076 -------------------------------------------------------------------------------
2077 --Start of Comments
2078 --Name: SYNC_PRICE_DIFFS
2079 --Pre-reqs:
2080 -- None.
2081 --Modifies:
2082 -- PO_PRICE_DIFFERENTIALS_DRAFT_ALL
2083 --Locks:
2084 -- None.
2085 --Function:
2086 -- Syncs the price differentials.
2087 --Parameters:
2088 --IN:
2089 --p_document_id
2090 -- The id of the document that needs to be synced.
2091 --p_entity_type
2092 -- The entity type of the document that needs to be synced.
2093 --p_revision_num
2094 -- The revision number of the document that needs to be synced
2095 --p_draft_id
2096 -- The draft_id using which the record has to be inserted at the time of sync
2097 --Testing:
2098 -- None.
2099 --End of Comments
2100 -------------------------------------------------------------------------------
2101 PROCEDURE SYNC_PRICE_DIFFS(p_document_id IN NUMBER,
2102 p_entity_type IN VARCHAR,
2103 p_revision_num IN NUMBER,
2104 p_draft_id IN NUMBER)
2105 IS
2106 d_api_name CONSTANT VARCHAR2(30) := 'SYNC_PRICE_DIFFS';
2107 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
2108 d_position NUMBER;
2109
2110 BEGIN
2111
2112 d_position := 0;
2113 IF (PO_LOG.d_proc) THEN
2114 PO_LOG.proc_begin(d_module,'p_po_header_id',p_document_id);
2115 PO_LOG.proc_begin(d_module,'p_entity_type',p_entity_type);
2116 END IF;
2117
2118 d_position := 10;
2119 IF (PO_LOG.d_stmt) THEN
2120 PO_LOG.stmt(d_module, d_position, 'transfer records from archive to dft');
2121 END IF;
2122
2123 INSERT INTO po_price_diff_draft
2124 (
2125 draft_id,
2126 delete_flag,
2127 change_accepted_flag,
2128 change_status,
2129 price_differential_id,
2130 price_differential_num,
2131 entity_id,
2132 entity_type,
2133 price_type,
2134 enabled_flag,
2135 min_multiplier,
2136 max_multiplier,
2137 multiplier,
2138 creation_date,
2139 created_by,
2140 last_update_date,
2141 last_updated_by,
2142 last_update_login
2143 )
2144 SELECT
2145 p_draft_id, -- Always 0 for action View Base Document (Revision 0)
2146 NULL,
2147 NULL,
2148 'NOCHANGE',
2149 price_differential_id,
2150 price_differential_num,
2151 entity_id,
2152 entity_type,
2153 price_type,
2154 enabled_flag,
2155 min_multiplier,
2156 max_multiplier,
2157 multiplier,
2158 creation_date,
2159 created_by,
2160 last_update_date,
2161 last_updated_by,
2162 last_update_login
2163 FROM po_price_differentials_archive POR
2164 WHERE ((p_entity_type = 'PRICE BREAK' AND
2165 POR.entity_id IN (SELECT line_location_id
2166 FROM PO_LINE_LOCATIONS_ALL
2167 WHERE po_header_id = p_document_id)) OR
2168 (p_entity_type IN ('PO LINE', 'BLANKET LINE') AND
2169 POR.entity_id IN (SELECT po_line_id
2170 FROM PO_LINES_ALL
2171 WHERE po_header_id = p_document_id)))
2172 AND POR.entity_type = p_entity_type
2173 AND POR.revision_num = p_revision_num;
2174
2175 d_position := 20;
2176 IF (PO_LOG.d_stmt) THEN
2177 PO_LOG.stmt(d_module, d_position, 'transfer count = ' || SQL%ROWCOUNT);
2178 END IF;
2179
2180 d_position := 30;
2181
2182 IF (PO_LOG.d_proc) THEN
2183 PO_LOG.proc_end(d_module);
2184 END IF;
2185
2186 EXCEPTION
2187 WHEN OTHERS THEN
2188 PO_MESSAGE_S.add_exc_msg
2189 ( p_pkg_name => d_pkg_name,
2190 p_procedure_name => d_api_name || '.' || d_position
2191 );
2192 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2193
2194 END SYNC_PRICE_DIFFS;
2195
2196 -------------------------------------------------------------------------------
2197 --Start of Comments
2198 --Name: sync_attribute_values
2199 --Pre-reqs:
2200 -- None.
2201 --Modifies:
2202 -- PO_ATTR_VALUES_DRAFT
2203 --Locks:
2204 -- None.
2205 --Function:
2206 -- Sync Item Attribute Values
2207 --Parameters:
2208 --IN:
2209 --p_po_header_id
2210 -- The PO_HEADER_ID of the document that needs to be Synced
2211 --p_revision_num
2212 -- The revision number of the document that needs to be synced
2213 --p_draft_id
2214 -- The draft_id using which the record has to be inserted at the time of sync
2215 --Testing:
2216 -- None.
2217 --End of Comments
2218 -------------------------------------------------------------------------------
2219 PROCEDURE SYNC_ATTRIBUTE_VALUES(p_document_id IN NUMBER,
2220 p_revision_num IN NUMBER,
2221 p_draft_id IN NUMBER)
2222 IS
2223 d_api_name CONSTANT VARCHAR2(30) := 'SYNC_ATTRIBUTE_VALUES';
2224 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
2225 d_position NUMBER;
2226
2227 BEGIN
2228
2229 d_position := 0;
2230 IF (PO_LOG.d_proc) THEN
2231 PO_LOG.proc_begin(d_module,'p_po_header_id',p_document_id);
2232 END IF;
2233
2234 d_position := 10;
2235 IF (PO_LOG.d_stmt) THEN
2236 PO_LOG.stmt(d_module, d_position, 'transfer records from archive to dft');
2237 END IF;
2238
2239 INSERT INTO po_attribute_values_draft
2240 ( draft_id,
2241 delete_flag,
2242 change_accepted_flag,
2243 change_status,
2244 attribute_values_id,
2245 po_line_id,
2246 req_template_name,
2247 req_template_line_num,
2248 ip_category_id,
2249 inventory_item_id,
2250 org_id,
2251 manufacturer_part_num,
2252 picture,
2253 thumbnail_image,
2254 supplier_url,
2255 manufacturer_url,
2256 attachment_url,
2257 unspsc,
2258 availability,
2259 lead_time,
2260 text_base_attribute1,
2261 text_base_attribute2,
2262 text_base_attribute3,
2263 text_base_attribute4,
2264 text_base_attribute5,
2265 text_base_attribute6,
2266 text_base_attribute7,
2267 text_base_attribute8,
2268 text_base_attribute9,
2269 text_base_attribute10,
2270 text_base_attribute11,
2271 text_base_attribute12,
2272 text_base_attribute13,
2273 text_base_attribute14,
2274 text_base_attribute15,
2275 text_base_attribute16,
2276 text_base_attribute17,
2277 text_base_attribute18,
2278 text_base_attribute19,
2279 text_base_attribute20,
2280 text_base_attribute21,
2281 text_base_attribute22,
2282 text_base_attribute23,
2283 text_base_attribute24,
2284 text_base_attribute25,
2285 text_base_attribute26,
2286 text_base_attribute27,
2287 text_base_attribute28,
2288 text_base_attribute29,
2289 text_base_attribute30,
2290 text_base_attribute31,
2291 text_base_attribute32,
2292 text_base_attribute33,
2293 text_base_attribute34,
2294 text_base_attribute35,
2295 text_base_attribute36,
2296 text_base_attribute37,
2297 text_base_attribute38,
2298 text_base_attribute39,
2299 text_base_attribute40,
2300 text_base_attribute41,
2301 text_base_attribute42,
2302 text_base_attribute43,
2303 text_base_attribute44,
2304 text_base_attribute45,
2305 text_base_attribute46,
2306 text_base_attribute47,
2307 text_base_attribute48,
2308 text_base_attribute49,
2309 text_base_attribute50,
2310 text_base_attribute51,
2311 text_base_attribute52,
2312 text_base_attribute53,
2313 text_base_attribute54,
2314 text_base_attribute55,
2315 text_base_attribute56,
2316 text_base_attribute57,
2317 text_base_attribute58,
2318 text_base_attribute59,
2319 text_base_attribute60,
2320 text_base_attribute61,
2321 text_base_attribute62,
2322 text_base_attribute63,
2323 text_base_attribute64,
2324 text_base_attribute65,
2325 text_base_attribute66,
2326 text_base_attribute67,
2327 text_base_attribute68,
2328 text_base_attribute69,
2329 text_base_attribute70,
2330 text_base_attribute71,
2331 text_base_attribute72,
2332 text_base_attribute73,
2333 text_base_attribute74,
2334 text_base_attribute75,
2335 text_base_attribute76,
2336 text_base_attribute77,
2337 text_base_attribute78,
2338 text_base_attribute79,
2339 text_base_attribute80,
2340 text_base_attribute81,
2341 text_base_attribute82,
2342 text_base_attribute83,
2343 text_base_attribute84,
2344 text_base_attribute85,
2345 text_base_attribute86,
2346 text_base_attribute87,
2347 text_base_attribute88,
2348 text_base_attribute89,
2349 text_base_attribute90,
2350 text_base_attribute91,
2351 text_base_attribute92,
2352 text_base_attribute93,
2353 text_base_attribute94,
2354 text_base_attribute95,
2355 text_base_attribute96,
2356 text_base_attribute97,
2357 text_base_attribute98,
2358 text_base_attribute99,
2359 text_base_attribute100,
2360 num_base_attribute1,
2361 num_base_attribute2,
2362 num_base_attribute3,
2363 num_base_attribute4,
2364 num_base_attribute5,
2365 num_base_attribute6,
2366 num_base_attribute7,
2367 num_base_attribute8,
2368 num_base_attribute9,
2369 num_base_attribute10,
2370 num_base_attribute11,
2371 num_base_attribute12,
2372 num_base_attribute13,
2373 num_base_attribute14,
2374 num_base_attribute15,
2375 num_base_attribute16,
2376 num_base_attribute17,
2377 num_base_attribute18,
2378 num_base_attribute19,
2379 num_base_attribute20,
2380 num_base_attribute21,
2381 num_base_attribute22,
2382 num_base_attribute23,
2383 num_base_attribute24,
2384 num_base_attribute25,
2385 num_base_attribute26,
2386 num_base_attribute27,
2387 num_base_attribute28,
2388 num_base_attribute29,
2389 num_base_attribute30,
2390 num_base_attribute31,
2391 num_base_attribute32,
2392 num_base_attribute33,
2393 num_base_attribute34,
2394 num_base_attribute35,
2395 num_base_attribute36,
2396 num_base_attribute37,
2397 num_base_attribute38,
2398 num_base_attribute39,
2399 num_base_attribute40,
2400 num_base_attribute41,
2401 num_base_attribute42,
2402 num_base_attribute43,
2403 num_base_attribute44,
2404 num_base_attribute45,
2405 num_base_attribute46,
2406 num_base_attribute47,
2407 num_base_attribute48,
2408 num_base_attribute49,
2409 num_base_attribute50,
2410 num_base_attribute51,
2411 num_base_attribute52,
2412 num_base_attribute53,
2413 num_base_attribute54,
2414 num_base_attribute55,
2415 num_base_attribute56,
2416 num_base_attribute57,
2417 num_base_attribute58,
2418 num_base_attribute59,
2419 num_base_attribute60,
2420 num_base_attribute61,
2421 num_base_attribute62,
2422 num_base_attribute63,
2423 num_base_attribute64,
2424 num_base_attribute65,
2425 num_base_attribute66,
2426 num_base_attribute67,
2427 num_base_attribute68,
2428 num_base_attribute69,
2429 num_base_attribute70,
2430 num_base_attribute71,
2431 num_base_attribute72,
2432 num_base_attribute73,
2433 num_base_attribute74,
2434 num_base_attribute75,
2435 num_base_attribute76,
2436 num_base_attribute77,
2437 num_base_attribute78,
2438 num_base_attribute79,
2439 num_base_attribute80,
2440 num_base_attribute81,
2441 num_base_attribute82,
2442 num_base_attribute83,
2443 num_base_attribute84,
2444 num_base_attribute85,
2445 num_base_attribute86,
2446 num_base_attribute87,
2447 num_base_attribute88,
2448 num_base_attribute89,
2449 num_base_attribute90,
2450 num_base_attribute91,
2451 num_base_attribute92,
2452 num_base_attribute93,
2453 num_base_attribute94,
2454 num_base_attribute95,
2455 num_base_attribute96,
2456 num_base_attribute97,
2457 num_base_attribute98,
2458 num_base_attribute99,
2459 num_base_attribute100,
2460 text_cat_attribute1,
2461 text_cat_attribute2,
2462 text_cat_attribute3,
2463 text_cat_attribute4,
2464 text_cat_attribute5,
2465 text_cat_attribute6,
2466 text_cat_attribute7,
2467 text_cat_attribute8,
2468 text_cat_attribute9,
2469 text_cat_attribute10,
2470 text_cat_attribute11,
2471 text_cat_attribute12,
2472 text_cat_attribute13,
2473 text_cat_attribute14,
2474 text_cat_attribute15,
2475 text_cat_attribute16,
2476 text_cat_attribute17,
2477 text_cat_attribute18,
2478 text_cat_attribute19,
2479 text_cat_attribute20,
2480 text_cat_attribute21,
2481 text_cat_attribute22,
2482 text_cat_attribute23,
2483 text_cat_attribute24,
2484 text_cat_attribute25,
2485 text_cat_attribute26,
2486 text_cat_attribute27,
2487 text_cat_attribute28,
2488 text_cat_attribute29,
2489 text_cat_attribute30,
2490 text_cat_attribute31,
2491 text_cat_attribute32,
2492 text_cat_attribute33,
2493 text_cat_attribute34,
2494 text_cat_attribute35,
2495 text_cat_attribute36,
2496 text_cat_attribute37,
2497 text_cat_attribute38,
2498 text_cat_attribute39,
2499 text_cat_attribute40,
2500 text_cat_attribute41,
2501 text_cat_attribute42,
2502 text_cat_attribute43,
2503 text_cat_attribute44,
2504 text_cat_attribute45,
2505 text_cat_attribute46,
2506 text_cat_attribute47,
2507 text_cat_attribute48,
2508 text_cat_attribute49,
2509 text_cat_attribute50,
2510 num_cat_attribute1,
2511 num_cat_attribute2,
2512 num_cat_attribute3,
2513 num_cat_attribute4,
2514 num_cat_attribute5,
2515 num_cat_attribute6,
2516 num_cat_attribute7,
2517 num_cat_attribute8,
2518 num_cat_attribute9,
2519 num_cat_attribute10,
2520 num_cat_attribute11,
2521 num_cat_attribute12,
2522 num_cat_attribute13,
2523 num_cat_attribute14,
2524 num_cat_attribute15,
2525 num_cat_attribute16,
2526 num_cat_attribute17,
2527 num_cat_attribute18,
2528 num_cat_attribute19,
2529 num_cat_attribute20,
2530 num_cat_attribute21,
2531 num_cat_attribute22,
2532 num_cat_attribute23,
2533 num_cat_attribute24,
2534 num_cat_attribute25,
2535 num_cat_attribute26,
2536 num_cat_attribute27,
2537 num_cat_attribute28,
2538 num_cat_attribute29,
2539 num_cat_attribute30,
2540 num_cat_attribute31,
2541 num_cat_attribute32,
2542 num_cat_attribute33,
2543 num_cat_attribute34,
2544 num_cat_attribute35,
2545 num_cat_attribute36,
2546 num_cat_attribute37,
2547 num_cat_attribute38,
2548 num_cat_attribute39,
2549 num_cat_attribute40,
2550 num_cat_attribute41,
2551 num_cat_attribute42,
2552 num_cat_attribute43,
2553 num_cat_attribute44,
2554 num_cat_attribute45,
2555 num_cat_attribute46,
2556 num_cat_attribute47,
2557 num_cat_attribute48,
2558 num_cat_attribute49,
2559 num_cat_attribute50,
2560 last_update_login,
2561 last_updated_by,
2562 last_update_date,
2563 created_by,
2564 creation_date,
2565 request_id,
2566 program_application_id,
2567 program_id,
2568 program_update_date
2569 )
2570 SELECT
2571 p_draft_id, -- Always 0 for action View Base Document (Revision 0)
2572 NULL,
2573 NULL,
2574 'NOCHANGE',
2575 ATTR.attribute_values_id,
2576 ATTR.po_line_id,
2577 ATTR.req_template_name,
2578 ATTR.req_template_line_num,
2579 ATTR.ip_category_id,
2580 ATTR.inventory_item_id,
2581 ATTR.org_id,
2582 ATTR.manufacturer_part_num,
2583 ATTR.picture,
2584 ATTR.thumbnail_image,
2585 ATTR.supplier_url,
2586 ATTR.manufacturer_url,
2587 ATTR.attachment_url,
2588 ATTR.unspsc,
2589 ATTR.availability,
2590 ATTR.lead_time,
2591 ATTR.text_base_attribute1,
2592 ATTR.text_base_attribute2,
2593 ATTR.text_base_attribute3,
2594 ATTR.text_base_attribute4,
2595 ATTR.text_base_attribute5,
2596 ATTR.text_base_attribute6,
2597 ATTR.text_base_attribute7,
2598 ATTR.text_base_attribute8,
2599 ATTR.text_base_attribute9,
2600 ATTR.text_base_attribute10,
2601 ATTR.text_base_attribute11,
2602 ATTR.text_base_attribute12,
2603 ATTR.text_base_attribute13,
2604 ATTR.text_base_attribute14,
2605 ATTR.text_base_attribute15,
2606 ATTR.text_base_attribute16,
2607 ATTR.text_base_attribute17,
2608 ATTR.text_base_attribute18,
2609 ATTR.text_base_attribute19,
2610 ATTR.text_base_attribute20,
2611 ATTR.text_base_attribute21,
2612 ATTR.text_base_attribute22,
2613 ATTR.text_base_attribute23,
2614 ATTR.text_base_attribute24,
2615 ATTR.text_base_attribute25,
2616 ATTR.text_base_attribute26,
2617 ATTR.text_base_attribute27,
2618 ATTR.text_base_attribute28,
2619 ATTR.text_base_attribute29,
2620 ATTR.text_base_attribute30,
2621 ATTR.text_base_attribute31,
2622 ATTR.text_base_attribute32,
2623 ATTR.text_base_attribute33,
2624 ATTR.text_base_attribute34,
2625 ATTR.text_base_attribute35,
2626 ATTR.text_base_attribute36,
2627 ATTR.text_base_attribute37,
2628 ATTR.text_base_attribute38,
2629 ATTR.text_base_attribute39,
2630 ATTR.text_base_attribute40,
2631 ATTR.text_base_attribute41,
2632 ATTR.text_base_attribute42,
2633 ATTR.text_base_attribute43,
2634 ATTR.text_base_attribute44,
2635 ATTR.text_base_attribute45,
2636 ATTR.text_base_attribute46,
2637 ATTR.text_base_attribute47,
2638 ATTR.text_base_attribute48,
2639 ATTR.text_base_attribute49,
2640 ATTR.text_base_attribute50,
2641 ATTR.text_base_attribute51,
2642 ATTR.text_base_attribute52,
2643 ATTR.text_base_attribute53,
2644 ATTR.text_base_attribute54,
2645 ATTR.text_base_attribute55,
2646 ATTR.text_base_attribute56,
2647 ATTR.text_base_attribute57,
2648 ATTR.text_base_attribute58,
2649 ATTR.text_base_attribute59,
2650 ATTR.text_base_attribute60,
2651 ATTR.text_base_attribute61,
2652 ATTR.text_base_attribute62,
2653 ATTR.text_base_attribute63,
2654 ATTR.text_base_attribute64,
2655 ATTR.text_base_attribute65,
2656 ATTR.text_base_attribute66,
2657 ATTR.text_base_attribute67,
2658 ATTR.text_base_attribute68,
2659 ATTR.text_base_attribute69,
2660 ATTR.text_base_attribute70,
2661 ATTR.text_base_attribute71,
2662 ATTR.text_base_attribute72,
2663 ATTR.text_base_attribute73,
2664 ATTR.text_base_attribute74,
2665 ATTR.text_base_attribute75,
2666 ATTR.text_base_attribute76,
2667 ATTR.text_base_attribute77,
2668 ATTR.text_base_attribute78,
2669 ATTR.text_base_attribute79,
2670 ATTR.text_base_attribute80,
2671 ATTR.text_base_attribute81,
2672 ATTR.text_base_attribute82,
2673 ATTR.text_base_attribute83,
2674 ATTR.text_base_attribute84,
2675 ATTR.text_base_attribute85,
2676 ATTR.text_base_attribute86,
2677 ATTR.text_base_attribute87,
2678 ATTR.text_base_attribute88,
2679 ATTR.text_base_attribute89,
2680 ATTR.text_base_attribute90,
2681 ATTR.text_base_attribute91,
2682 ATTR.text_base_attribute92,
2683 ATTR.text_base_attribute93,
2684 ATTR.text_base_attribute94,
2685 ATTR.text_base_attribute95,
2686 ATTR.text_base_attribute96,
2687 ATTR.text_base_attribute97,
2688 ATTR.text_base_attribute98,
2689 ATTR.text_base_attribute99,
2690 ATTR.text_base_attribute100,
2691 ATTR.num_base_attribute1,
2692 ATTR.num_base_attribute2,
2693 ATTR.num_base_attribute3,
2694 ATTR.num_base_attribute4,
2695 ATTR.num_base_attribute5,
2696 ATTR.num_base_attribute6,
2697 ATTR.num_base_attribute7,
2698 ATTR.num_base_attribute8,
2699 ATTR.num_base_attribute9,
2700 ATTR.num_base_attribute10,
2701 ATTR.num_base_attribute11,
2702 ATTR.num_base_attribute12,
2703 ATTR.num_base_attribute13,
2704 ATTR.num_base_attribute14,
2705 ATTR.num_base_attribute15,
2706 ATTR.num_base_attribute16,
2707 ATTR.num_base_attribute17,
2708 ATTR.num_base_attribute18,
2709 ATTR.num_base_attribute19,
2710 ATTR.num_base_attribute20,
2711 ATTR.num_base_attribute21,
2712 ATTR.num_base_attribute22,
2713 ATTR.num_base_attribute23,
2714 ATTR.num_base_attribute24,
2715 ATTR.num_base_attribute25,
2716 ATTR.num_base_attribute26,
2717 ATTR.num_base_attribute27,
2718 ATTR.num_base_attribute28,
2719 ATTR.num_base_attribute29,
2720 ATTR.num_base_attribute30,
2721 ATTR.num_base_attribute31,
2722 ATTR.num_base_attribute32,
2723 ATTR.num_base_attribute33,
2724 ATTR.num_base_attribute34,
2725 ATTR.num_base_attribute35,
2726 ATTR.num_base_attribute36,
2727 ATTR.num_base_attribute37,
2728 ATTR.num_base_attribute38,
2729 ATTR.num_base_attribute39,
2730 ATTR.num_base_attribute40,
2731 ATTR.num_base_attribute41,
2732 ATTR.num_base_attribute42,
2733 ATTR.num_base_attribute43,
2734 ATTR.num_base_attribute44,
2735 ATTR.num_base_attribute45,
2736 ATTR.num_base_attribute46,
2737 ATTR.num_base_attribute47,
2738 ATTR.num_base_attribute48,
2739 ATTR.num_base_attribute49,
2740 ATTR.num_base_attribute50,
2741 ATTR.num_base_attribute51,
2742 ATTR.num_base_attribute52,
2743 ATTR.num_base_attribute53,
2744 ATTR.num_base_attribute54,
2745 ATTR.num_base_attribute55,
2746 ATTR.num_base_attribute56,
2747 ATTR.num_base_attribute57,
2748 ATTR.num_base_attribute58,
2749 ATTR.num_base_attribute59,
2750 ATTR.num_base_attribute60,
2751 ATTR.num_base_attribute61,
2752 ATTR.num_base_attribute62,
2753 ATTR.num_base_attribute63,
2754 ATTR.num_base_attribute64,
2755 ATTR.num_base_attribute65,
2756 ATTR.num_base_attribute66,
2757 ATTR.num_base_attribute67,
2758 ATTR.num_base_attribute68,
2759 ATTR.num_base_attribute69,
2760 ATTR.num_base_attribute70,
2761 ATTR.num_base_attribute71,
2762 ATTR.num_base_attribute72,
2763 ATTR.num_base_attribute73,
2764 ATTR.num_base_attribute74,
2765 ATTR.num_base_attribute75,
2766 ATTR.num_base_attribute76,
2767 ATTR.num_base_attribute77,
2768 ATTR.num_base_attribute78,
2769 ATTR.num_base_attribute79,
2770 ATTR.num_base_attribute80,
2771 ATTR.num_base_attribute81,
2772 ATTR.num_base_attribute82,
2773 ATTR.num_base_attribute83,
2774 ATTR.num_base_attribute84,
2775 ATTR.num_base_attribute85,
2776 ATTR.num_base_attribute86,
2777 ATTR.num_base_attribute87,
2778 ATTR.num_base_attribute88,
2779 ATTR.num_base_attribute89,
2780 ATTR.num_base_attribute90,
2781 ATTR.num_base_attribute91,
2782 ATTR.num_base_attribute92,
2783 ATTR.num_base_attribute93,
2784 ATTR.num_base_attribute94,
2785 ATTR.num_base_attribute95,
2786 ATTR.num_base_attribute96,
2787 ATTR.num_base_attribute97,
2788 ATTR.num_base_attribute98,
2789 ATTR.num_base_attribute99,
2790 ATTR.num_base_attribute100,
2791 ATTR.text_cat_attribute1,
2792 ATTR.text_cat_attribute2,
2793 ATTR.text_cat_attribute3,
2794 ATTR.text_cat_attribute4,
2795 ATTR.text_cat_attribute5,
2796 ATTR.text_cat_attribute6,
2797 ATTR.text_cat_attribute7,
2798 ATTR.text_cat_attribute8,
2799 ATTR.text_cat_attribute9,
2800 ATTR.text_cat_attribute10,
2801 ATTR.text_cat_attribute11,
2802 ATTR.text_cat_attribute12,
2803 ATTR.text_cat_attribute13,
2804 ATTR.text_cat_attribute14,
2805 ATTR.text_cat_attribute15,
2806 ATTR.text_cat_attribute16,
2807 ATTR.text_cat_attribute17,
2808 ATTR.text_cat_attribute18,
2809 ATTR.text_cat_attribute19,
2810 ATTR.text_cat_attribute20,
2811 ATTR.text_cat_attribute21,
2812 ATTR.text_cat_attribute22,
2813 ATTR.text_cat_attribute23,
2814 ATTR.text_cat_attribute24,
2815 ATTR.text_cat_attribute25,
2816 ATTR.text_cat_attribute26,
2817 ATTR.text_cat_attribute27,
2818 ATTR.text_cat_attribute28,
2819 ATTR.text_cat_attribute29,
2820 ATTR.text_cat_attribute30,
2821 ATTR.text_cat_attribute31,
2822 ATTR.text_cat_attribute32,
2823 ATTR.text_cat_attribute33,
2824 ATTR.text_cat_attribute34,
2825 ATTR.text_cat_attribute35,
2826 ATTR.text_cat_attribute36,
2827 ATTR.text_cat_attribute37,
2828 ATTR.text_cat_attribute38,
2829 ATTR.text_cat_attribute39,
2830 ATTR.text_cat_attribute40,
2831 ATTR.text_cat_attribute41,
2832 ATTR.text_cat_attribute42,
2833 ATTR.text_cat_attribute43,
2834 ATTR.text_cat_attribute44,
2835 ATTR.text_cat_attribute45,
2836 ATTR.text_cat_attribute46,
2837 ATTR.text_cat_attribute47,
2838 ATTR.text_cat_attribute48,
2839 ATTR.text_cat_attribute49,
2840 ATTR.text_cat_attribute50,
2841 ATTR.num_cat_attribute1,
2842 ATTR.num_cat_attribute2,
2843 ATTR.num_cat_attribute3,
2844 ATTR.num_cat_attribute4,
2845 ATTR.num_cat_attribute5,
2846 ATTR.num_cat_attribute6,
2847 ATTR.num_cat_attribute7,
2848 ATTR.num_cat_attribute8,
2849 ATTR.num_cat_attribute9,
2850 ATTR.num_cat_attribute10,
2851 ATTR.num_cat_attribute11,
2852 ATTR.num_cat_attribute12,
2853 ATTR.num_cat_attribute13,
2854 ATTR.num_cat_attribute14,
2855 ATTR.num_cat_attribute15,
2856 ATTR.num_cat_attribute16,
2857 ATTR.num_cat_attribute17,
2858 ATTR.num_cat_attribute18,
2859 ATTR.num_cat_attribute19,
2860 ATTR.num_cat_attribute20,
2861 ATTR.num_cat_attribute21,
2862 ATTR.num_cat_attribute22,
2863 ATTR.num_cat_attribute23,
2864 ATTR.num_cat_attribute24,
2865 ATTR.num_cat_attribute25,
2866 ATTR.num_cat_attribute26,
2867 ATTR.num_cat_attribute27,
2868 ATTR.num_cat_attribute28,
2869 ATTR.num_cat_attribute29,
2870 ATTR.num_cat_attribute30,
2871 ATTR.num_cat_attribute31,
2872 ATTR.num_cat_attribute32,
2873 ATTR.num_cat_attribute33,
2874 ATTR.num_cat_attribute34,
2875 ATTR.num_cat_attribute35,
2876 ATTR.num_cat_attribute36,
2877 ATTR.num_cat_attribute37,
2878 ATTR.num_cat_attribute38,
2879 ATTR.num_cat_attribute39,
2880 ATTR.num_cat_attribute40,
2881 ATTR.num_cat_attribute41,
2882 ATTR.num_cat_attribute42,
2883 ATTR.num_cat_attribute43,
2884 ATTR.num_cat_attribute44,
2885 ATTR.num_cat_attribute45,
2886 ATTR.num_cat_attribute46,
2887 ATTR.num_cat_attribute47,
2888 ATTR.num_cat_attribute48,
2889 ATTR.num_cat_attribute49,
2890 ATTR.num_cat_attribute50,
2891 ATTR.last_update_login,
2892 ATTR.last_updated_by,
2893 ATTR.last_update_date,
2894 ATTR.created_by,
2895 ATTR.creation_date,
2896 ATTR.request_id,
2897 ATTR.program_application_id,
2898 ATTR.program_id,
2899 ATTR.program_update_date
2900 FROM PO_ATTR_VALUES_ARCHIVE ATTR,
2901 PO_LINES_ALL POL
2902 WHERE ATTR.po_line_id = POL.po_line_id
2903 AND POL.po_header_id = p_document_id
2904 AND ATTR.revision_num = p_revision_num;
2905
2906 d_position := 20;
2907 IF (PO_LOG.d_stmt) THEN
2908 PO_LOG.stmt(d_module, d_position, 'transfer count = ' || SQL%ROWCOUNT);
2909 END IF;
2910
2911 d_position := 30;
2912
2913 IF (PO_LOG.d_proc) THEN
2914 PO_LOG.proc_end(d_module);
2915 END IF;
2916
2917 EXCEPTION
2918 WHEN OTHERS THEN
2919 PO_MESSAGE_S.add_exc_msg
2920 ( p_pkg_name => d_pkg_name,
2921 p_procedure_name => d_api_name || '.' || d_position
2922 );
2923 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2924 END SYNC_ATTRIBUTE_VALUES;
2925
2926 -------------------------------------------------------------------------------
2927 --Start of Comments
2928 --Name: sync_attr_values_tlp
2929 --Pre-reqs:
2930 -- None.
2931 --Modifies:
2932 -- PO_ATTR_VALUES_TLP_DRAFT
2933 --Locks:
2934 -- None.
2935 --Function:
2936 -- Sync Item Attribute Values TLP
2937 --Parameters:
2938 --IN:
2939 --p_po_header_id
2940 -- The PO_HEADER_ID of the document that needs to be synced
2941 --p_revision_num
2942 -- The revision number of the document that needs to be synced
2943 --p_draft_id
2944 -- The draft_id using which the record has to be inserted at the time of sync
2945 --Testing:
2946 -- None.
2947 --End of Comments
2948 -------------------------------------------------------------------------------
2949 PROCEDURE SYNC_ATTR_VALUES_TLP(p_document_id IN NUMBER,
2950 p_revision_num IN NUMBER,
2951 p_draft_id IN NUMBER)
2952 IS
2953 d_api_name CONSTANT VARCHAR2(30) := 'SYNC_ATTR_VALUES_TLP';
2954 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
2955 d_position NUMBER;
2956
2957 BEGIN
2958
2959 d_position := 0;
2960 IF (PO_LOG.d_proc) THEN
2961 PO_LOG.proc_begin(d_module,'p_po_header_id',p_document_id);
2962 END IF;
2963
2964 d_position := 10;
2965 IF (PO_LOG.d_stmt) THEN
2966 PO_LOG.stmt(d_module, d_position, 'transfer records from archive to dft');
2967 END IF;
2968
2969 INSERT INTO po_attribute_values_tlp_draft
2970 (draft_id,
2971 delete_flag,
2972 change_accepted_flag,
2973 change_status,
2974 attribute_values_tlp_id,
2975 po_line_id,
2976 req_template_name,
2977 req_template_line_num,
2978 ip_category_id,
2979 inventory_item_id,
2980 org_id,
2981 language,
2982 description,
2983 manufacturer,
2984 comments,
2985 alias,
2986 long_description,
2987 tl_text_base_attribute1,
2988 tl_text_base_attribute2,
2989 tl_text_base_attribute3,
2990 tl_text_base_attribute4,
2991 tl_text_base_attribute5,
2992 tl_text_base_attribute6,
2993 tl_text_base_attribute7,
2994 tl_text_base_attribute8,
2995 tl_text_base_attribute9,
2996 tl_text_base_attribute10,
2997 tl_text_base_attribute11,
2998 tl_text_base_attribute12,
2999 tl_text_base_attribute13,
3000 tl_text_base_attribute14,
3001 tl_text_base_attribute15,
3002 tl_text_base_attribute16,
3003 tl_text_base_attribute17,
3004 tl_text_base_attribute18,
3005 tl_text_base_attribute19,
3006 tl_text_base_attribute20,
3007 tl_text_base_attribute21,
3008 tl_text_base_attribute22,
3009 tl_text_base_attribute23,
3010 tl_text_base_attribute24,
3011 tl_text_base_attribute25,
3012 tl_text_base_attribute26,
3013 tl_text_base_attribute27,
3014 tl_text_base_attribute28,
3015 tl_text_base_attribute29,
3016 tl_text_base_attribute30,
3017 tl_text_base_attribute31,
3018 tl_text_base_attribute32,
3019 tl_text_base_attribute33,
3020 tl_text_base_attribute34,
3021 tl_text_base_attribute35,
3022 tl_text_base_attribute36,
3023 tl_text_base_attribute37,
3024 tl_text_base_attribute38,
3025 tl_text_base_attribute39,
3026 tl_text_base_attribute40,
3027 tl_text_base_attribute41,
3028 tl_text_base_attribute42,
3029 tl_text_base_attribute43,
3030 tl_text_base_attribute44,
3031 tl_text_base_attribute45,
3032 tl_text_base_attribute46,
3033 tl_text_base_attribute47,
3034 tl_text_base_attribute48,
3035 tl_text_base_attribute49,
3036 tl_text_base_attribute50,
3037 tl_text_base_attribute51,
3038 tl_text_base_attribute52,
3039 tl_text_base_attribute53,
3040 tl_text_base_attribute54,
3041 tl_text_base_attribute55,
3042 tl_text_base_attribute56,
3043 tl_text_base_attribute57,
3044 tl_text_base_attribute58,
3045 tl_text_base_attribute59,
3046 tl_text_base_attribute60,
3047 tl_text_base_attribute61,
3048 tl_text_base_attribute62,
3049 tl_text_base_attribute63,
3050 tl_text_base_attribute64,
3051 tl_text_base_attribute65,
3052 tl_text_base_attribute66,
3053 tl_text_base_attribute67,
3054 tl_text_base_attribute68,
3055 tl_text_base_attribute69,
3056 tl_text_base_attribute70,
3057 tl_text_base_attribute71,
3058 tl_text_base_attribute72,
3059 tl_text_base_attribute73,
3060 tl_text_base_attribute74,
3061 tl_text_base_attribute75,
3062 tl_text_base_attribute76,
3063 tl_text_base_attribute77,
3064 tl_text_base_attribute78,
3065 tl_text_base_attribute79,
3066 tl_text_base_attribute80,
3067 tl_text_base_attribute81,
3068 tl_text_base_attribute82,
3069 tl_text_base_attribute83,
3070 tl_text_base_attribute84,
3071 tl_text_base_attribute85,
3072 tl_text_base_attribute86,
3073 tl_text_base_attribute87,
3074 tl_text_base_attribute88,
3075 tl_text_base_attribute89,
3076 tl_text_base_attribute90,
3077 tl_text_base_attribute91,
3078 tl_text_base_attribute92,
3079 tl_text_base_attribute93,
3080 tl_text_base_attribute94,
3081 tl_text_base_attribute95,
3082 tl_text_base_attribute96,
3083 tl_text_base_attribute97,
3084 tl_text_base_attribute98,
3085 tl_text_base_attribute99,
3086 tl_text_base_attribute100,
3087 tl_text_cat_attribute1,
3088 tl_text_cat_attribute2,
3089 tl_text_cat_attribute3,
3090 tl_text_cat_attribute4,
3091 tl_text_cat_attribute5,
3092 tl_text_cat_attribute6,
3093 tl_text_cat_attribute7,
3094 tl_text_cat_attribute8,
3095 tl_text_cat_attribute9,
3096 tl_text_cat_attribute10,
3097 tl_text_cat_attribute11,
3098 tl_text_cat_attribute12,
3099 tl_text_cat_attribute13,
3100 tl_text_cat_attribute14,
3101 tl_text_cat_attribute15,
3102 tl_text_cat_attribute16,
3103 tl_text_cat_attribute17,
3104 tl_text_cat_attribute18,
3105 tl_text_cat_attribute19,
3106 tl_text_cat_attribute20,
3107 tl_text_cat_attribute21,
3108 tl_text_cat_attribute22,
3109 tl_text_cat_attribute23,
3110 tl_text_cat_attribute24,
3111 tl_text_cat_attribute25,
3112 tl_text_cat_attribute26,
3113 tl_text_cat_attribute27,
3114 tl_text_cat_attribute28,
3115 tl_text_cat_attribute29,
3116 tl_text_cat_attribute30,
3117 tl_text_cat_attribute31,
3118 tl_text_cat_attribute32,
3119 tl_text_cat_attribute33,
3120 tl_text_cat_attribute34,
3121 tl_text_cat_attribute35,
3122 tl_text_cat_attribute36,
3123 tl_text_cat_attribute37,
3124 tl_text_cat_attribute38,
3125 tl_text_cat_attribute39,
3126 tl_text_cat_attribute40,
3127 tl_text_cat_attribute41,
3128 tl_text_cat_attribute42,
3129 tl_text_cat_attribute43,
3130 tl_text_cat_attribute44,
3131 tl_text_cat_attribute45,
3132 tl_text_cat_attribute46,
3133 tl_text_cat_attribute47,
3134 tl_text_cat_attribute48,
3135 tl_text_cat_attribute49,
3136 tl_text_cat_attribute50,
3137 last_update_login,
3138 last_updated_by,
3139 last_update_date,
3140 created_by,
3141 creation_date,
3142 request_id,
3143 program_application_id,
3144 program_id,
3145 program_update_date
3146 )
3147 SELECT
3148 p_draft_id, -- Always 0 for action View Base Document (Revision 0)
3149 NULL,
3150 NULL,
3151 'NOCHANGE',
3152 TLP.attribute_values_tlp_id,
3153 TLP.po_line_id,
3154 TLP.req_template_name,
3155 TLP.req_template_line_num,
3156 TLP.ip_category_id,
3157 TLP.inventory_item_id,
3158 TLP.org_id,
3159 TLP.language,
3160 TLP.description,
3161 TLP.manufacturer,
3162 TLP.comments,
3163 TLP.alias,
3164 TLP.long_description,
3165 TLP.tl_text_base_attribute1,
3166 TLP.tl_text_base_attribute2,
3167 TLP.tl_text_base_attribute3,
3168 TLP.tl_text_base_attribute4,
3169 TLP.tl_text_base_attribute5,
3170 TLP.tl_text_base_attribute6,
3171 TLP.tl_text_base_attribute7,
3172 TLP.tl_text_base_attribute8,
3173 TLP.tl_text_base_attribute9,
3174 TLP.tl_text_base_attribute10,
3175 TLP.tl_text_base_attribute11,
3176 TLP.tl_text_base_attribute12,
3177 TLP.tl_text_base_attribute13,
3178 TLP.tl_text_base_attribute14,
3179 TLP.tl_text_base_attribute15,
3180 TLP.tl_text_base_attribute16,
3181 TLP.tl_text_base_attribute17,
3182 TLP.tl_text_base_attribute18,
3183 TLP.tl_text_base_attribute19,
3184 TLP.tl_text_base_attribute20,
3185 TLP.tl_text_base_attribute21,
3186 TLP.tl_text_base_attribute22,
3187 TLP.tl_text_base_attribute23,
3188 TLP.tl_text_base_attribute24,
3189 TLP.tl_text_base_attribute25,
3190 TLP.tl_text_base_attribute26,
3191 TLP.tl_text_base_attribute27,
3192 TLP.tl_text_base_attribute28,
3193 TLP.tl_text_base_attribute29,
3194 TLP.tl_text_base_attribute30,
3195 TLP.tl_text_base_attribute31,
3196 TLP.tl_text_base_attribute32,
3197 TLP.tl_text_base_attribute33,
3198 TLP.tl_text_base_attribute34,
3199 TLP.tl_text_base_attribute35,
3200 TLP.tl_text_base_attribute36,
3201 TLP.tl_text_base_attribute37,
3202 TLP.tl_text_base_attribute38,
3203 TLP.tl_text_base_attribute39,
3204 TLP.tl_text_base_attribute40,
3205 TLP.tl_text_base_attribute41,
3206 TLP.tl_text_base_attribute42,
3207 TLP.tl_text_base_attribute43,
3208 TLP.tl_text_base_attribute44,
3209 TLP.tl_text_base_attribute45,
3210 TLP.tl_text_base_attribute46,
3211 TLP.tl_text_base_attribute47,
3212 TLP.tl_text_base_attribute48,
3213 TLP.tl_text_base_attribute49,
3214 TLP.tl_text_base_attribute50,
3215 TLP.tl_text_base_attribute51,
3216 TLP.tl_text_base_attribute52,
3217 TLP.tl_text_base_attribute53,
3218 TLP.tl_text_base_attribute54,
3219 TLP.tl_text_base_attribute55,
3220 TLP.tl_text_base_attribute56,
3221 TLP.tl_text_base_attribute57,
3222 TLP.tl_text_base_attribute58,
3223 TLP.tl_text_base_attribute59,
3224 TLP.tl_text_base_attribute60,
3225 TLP.tl_text_base_attribute61,
3226 TLP.tl_text_base_attribute62,
3227 TLP.tl_text_base_attribute63,
3228 TLP.tl_text_base_attribute64,
3229 TLP.tl_text_base_attribute65,
3230 TLP.tl_text_base_attribute66,
3231 TLP.tl_text_base_attribute67,
3232 TLP.tl_text_base_attribute68,
3233 TLP.tl_text_base_attribute69,
3234 TLP.tl_text_base_attribute70,
3235 TLP.tl_text_base_attribute71,
3236 TLP.tl_text_base_attribute72,
3237 TLP.tl_text_base_attribute73,
3238 TLP.tl_text_base_attribute74,
3239 TLP.tl_text_base_attribute75,
3240 TLP.tl_text_base_attribute76,
3241 TLP.tl_text_base_attribute77,
3242 TLP.tl_text_base_attribute78,
3243 TLP.tl_text_base_attribute79,
3244 TLP.tl_text_base_attribute80,
3245 TLP.tl_text_base_attribute81,
3246 TLP.tl_text_base_attribute82,
3247 TLP.tl_text_base_attribute83,
3248 TLP.tl_text_base_attribute84,
3249 TLP.tl_text_base_attribute85,
3250 TLP.tl_text_base_attribute86,
3251 TLP.tl_text_base_attribute87,
3252 TLP.tl_text_base_attribute88,
3253 TLP.tl_text_base_attribute89,
3254 TLP.tl_text_base_attribute90,
3255 TLP.tl_text_base_attribute91,
3256 TLP.tl_text_base_attribute92,
3257 TLP.tl_text_base_attribute93,
3258 TLP.tl_text_base_attribute94,
3259 TLP.tl_text_base_attribute95,
3260 TLP.tl_text_base_attribute96,
3261 TLP.tl_text_base_attribute97,
3262 TLP.tl_text_base_attribute98,
3263 TLP.tl_text_base_attribute99,
3264 TLP.tl_text_base_attribute100,
3265 TLP.tl_text_cat_attribute1,
3266 TLP.tl_text_cat_attribute2,
3267 TLP.tl_text_cat_attribute3,
3268 TLP.tl_text_cat_attribute4,
3269 TLP.tl_text_cat_attribute5,
3270 TLP.tl_text_cat_attribute6,
3271 TLP.tl_text_cat_attribute7,
3272 TLP.tl_text_cat_attribute8,
3273 TLP.tl_text_cat_attribute9,
3274 TLP.tl_text_cat_attribute10,
3275 TLP.tl_text_cat_attribute11,
3276 TLP.tl_text_cat_attribute12,
3277 TLP.tl_text_cat_attribute13,
3278 TLP.tl_text_cat_attribute14,
3279 TLP.tl_text_cat_attribute15,
3280 TLP.tl_text_cat_attribute16,
3281 TLP.tl_text_cat_attribute17,
3282 TLP.tl_text_cat_attribute18,
3283 TLP.tl_text_cat_attribute19,
3284 TLP.tl_text_cat_attribute20,
3285 TLP.tl_text_cat_attribute21,
3286 TLP.tl_text_cat_attribute22,
3287 TLP.tl_text_cat_attribute23,
3288 TLP.tl_text_cat_attribute24,
3289 TLP.tl_text_cat_attribute25,
3290 TLP.tl_text_cat_attribute26,
3291 TLP.tl_text_cat_attribute27,
3292 TLP.tl_text_cat_attribute28,
3293 TLP.tl_text_cat_attribute29,
3294 TLP.tl_text_cat_attribute30,
3295 TLP.tl_text_cat_attribute31,
3296 TLP.tl_text_cat_attribute32,
3297 TLP.tl_text_cat_attribute33,
3298 TLP.tl_text_cat_attribute34,
3299 TLP.tl_text_cat_attribute35,
3300 TLP.tl_text_cat_attribute36,
3301 TLP.tl_text_cat_attribute37,
3302 TLP.tl_text_cat_attribute38,
3303 TLP.tl_text_cat_attribute39,
3304 TLP.tl_text_cat_attribute40,
3305 TLP.tl_text_cat_attribute41,
3306 TLP.tl_text_cat_attribute42,
3307 TLP.tl_text_cat_attribute43,
3308 TLP.tl_text_cat_attribute44,
3309 TLP.tl_text_cat_attribute45,
3310 TLP.tl_text_cat_attribute46,
3311 TLP.tl_text_cat_attribute47,
3312 TLP.tl_text_cat_attribute48,
3313 TLP.tl_text_cat_attribute49,
3314 TLP.tl_text_cat_attribute50,
3315 TLP.last_update_login,
3316 TLP.last_updated_by,
3317 TLP.last_update_date,
3318 TLP.created_by,
3319 TLP.creation_date,
3320 TLP.request_id,
3321 TLP.program_application_id,
3322 TLP.program_id,
3323 TLP.program_update_date
3324 FROM PO_ATTR_VALUES_TLP_ARCHIVE TLP,
3325 PO_LINES_ALL POL
3326 WHERE TLP.po_line_id = POL.po_line_id
3327 AND POL.po_header_id = p_document_id
3328 AND TLP.revision_num = p_revision_num;
3329
3330
3331 d_position := 20;
3332 IF (PO_LOG.d_stmt) THEN
3333 PO_LOG.stmt(d_module, d_position, 'transfer count = ' || SQL%ROWCOUNT);
3334 END IF;
3335
3336 d_position := 30;
3337
3338 IF (PO_LOG.d_proc) THEN
3339 PO_LOG.proc_end(d_module);
3340 END IF;
3341
3342 EXCEPTION
3343 WHEN OTHERS THEN
3344 PO_MESSAGE_S.add_exc_msg
3345 ( p_pkg_name => d_pkg_name,
3346 p_procedure_name => d_api_name || '.' || d_position
3347 );
3348 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3349 END SYNC_ATTR_VALUES_TLP;
3350
3351 -------------------------------------------------------------------------------
3352 --Start of Comments
3353 --Name: sync_price_adjustments
3354 --Pre-reqs:
3355 -- None.
3356 --Modifies:
3357 -- PO_PRICE_ADJUSTMENTS_DRAFT
3358 --Locks:
3359 -- None.
3360 --Function:
3361 -- Sync Price Adjustments
3362 --Parameters:
3363 --IN:
3364 --p_po_header_id
3365 -- The PO_HEADER_ID of the document that needs to be synced
3366 --p_revision_num
3367 -- The revision number of the document that needs to be synced
3368 --p_draft_id
3369 -- The draft_id using which the record has to be inserted at the time of sync
3370 --Testing:
3371 -- None.
3372 --End of Comments
3373 -------------------------------------------------------------------------------
3374 PROCEDURE SYNC_PRICE_ADJUSTMENTS(p_document_id IN NUMBER,
3375 p_revision_num IN NUMBER,
3376 p_draft_id IN NUMBER)
3377 IS
3378 d_api_name CONSTANT VARCHAR2(30) := 'SYNC_PRICE_ADJUSTMENTS';
3379 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
3380 d_position NUMBER;
3381
3382 BEGIN
3383
3384 d_position := 0;
3385 IF (PO_LOG.d_proc) THEN
3386 PO_LOG.proc_begin(d_module,'p_po_header_id',p_document_id);
3387 END IF;
3388
3389 d_position := 10;
3390 IF (PO_LOG.d_stmt) THEN
3391 PO_LOG.stmt(d_module, d_position, 'transfer records from archive to dft');
3392 END IF;
3393
3394 INSERT INTO PO_PRICE_ADJUSTMENTS_DRAFT
3395 (DRAFT_ID
3396 , DELETE_FLAG
3397 , CHANGE_ACCEPTED_FLAG
3398 , CHANGE_STATUS
3399 , PRICE_ADJUSTMENT_ID
3400 , ADJ_LINE_NUM
3401 , CREATION_DATE
3402 , CREATED_BY
3403 , LAST_UPDATE_DATE
3404 , LAST_UPDATED_BY
3405 , LAST_UPDATE_LOGIN
3406 , PROGRAM_APPLICATION_ID
3407 , PROGRAM_ID
3408 , PROGRAM_UPDATE_DATE
3409 , REQUEST_ID
3410 , PO_HEADER_ID
3411 , AUTOMATIC_FLAG
3412 , PO_LINE_ID
3413 , CONTEXT
3414 , ATTRIBUTE1
3415 , ATTRIBUTE2
3416 , ATTRIBUTE3
3417 , ATTRIBUTE4
3418 , ATTRIBUTE5
3419 , ATTRIBUTE6
3420 , ATTRIBUTE7
3421 , ATTRIBUTE8
3422 , ATTRIBUTE9
3423 , ATTRIBUTE10
3424 , ATTRIBUTE11
3425 , ATTRIBUTE12
3426 , ATTRIBUTE13
3427 , ATTRIBUTE14
3428 , ATTRIBUTE15
3429 , ORIG_SYS_DISCOUNT_REF
3430 , LIST_HEADER_ID
3431 , LIST_LINE_ID
3432 , LIST_LINE_TYPE_CODE
3433 , MODIFIED_FROM
3434 , MODIFIED_TO
3435 , UPDATED_FLAG
3436 , UPDATE_ALLOWED
3437 , APPLIED_FLAG
3438 , CHANGE_REASON_CODE
3439 , CHANGE_REASON_TEXT
3440 , operand
3441 , Arithmetic_operator
3442 , COST_ID
3443 , TAX_CODE
3444 , TAX_EXEMPT_FLAG
3445 , TAX_EXEMPT_NUMBER
3446 , TAX_EXEMPT_REASON_CODE
3447 , PARENT_ADJUSTMENT_ID
3448 , INVOICED_FLAG
3449 , ESTIMATED_FLAG
3450 , INC_IN_SALES_PERFORMANCE
3451 , ADJUSTED_AMOUNT
3452 , PRICING_PHASE_ID
3453 , CHARGE_TYPE_CODE
3454 , CHARGE_SUBTYPE_CODE
3455 , list_line_no
3456 , source_system_code
3457 , benefit_qty
3458 , benefit_uom_code
3459 , print_on_invoice_flag
3460 , expiration_date
3461 , rebate_transaction_type_code
3462 , rebate_transaction_reference
3463 , rebate_payment_system_code
3464 , redeemed_date
3465 , redeemed_flag
3466 , accrual_flag
3467 , range_break_quantity
3468 , accrual_conversion_rate
3469 , pricing_group_sequence
3470 , modifier_level_code
3471 , price_break_type_code
3472 , substitution_attribute
3473 , proration_type_code
3474 , CREDIT_OR_CHARGE_FLAG
3475 , INCLUDE_ON_RETURNS_FLAG
3476 , AC_CONTEXT
3477 , AC_ATTRIBUTE1
3478 , AC_ATTRIBUTE2
3479 , AC_ATTRIBUTE3
3480 , AC_ATTRIBUTE4
3481 , AC_ATTRIBUTE5
3482 , AC_ATTRIBUTE6
3483 , AC_ATTRIBUTE7
3484 , AC_ATTRIBUTE8
3485 , AC_ATTRIBUTE9
3486 , AC_ATTRIBUTE10
3487 , AC_ATTRIBUTE11
3488 , AC_ATTRIBUTE12
3489 , AC_ATTRIBUTE13
3490 , AC_ATTRIBUTE14
3491 , AC_ATTRIBUTE15
3492 , OPERAND_PER_PQTY
3493 , ADJUSTED_AMOUNT_PER_PQTY
3494 , LOCK_CONTROL
3495 )
3496 SELECT
3497 p_draft_id -- Always 0 for action View Base Document (Revision 0)
3498 , NULL
3499 , NULL
3500 , 'NOCHANGE'
3501 , ADJ.price_adjustment_id
3502 , ADJ.adj_line_num
3503 , ADJ.creation_date
3504 , ADJ.created_by
3505 , ADJ.last_update_date
3506 , ADJ.last_updated_by
3507 , ADJ.last_update_login
3508 , ADJ.program_application_id
3509 , ADJ.program_id
3510 , ADJ.program_update_date
3511 , ADJ.request_id
3512 , ADJ.po_header_id
3513 , ADJ.automatic_flag
3514 , ADJ.po_line_id
3515 , ADJ.context
3516 , ADJ.attribute1
3517 , ADJ.attribute2
3518 , ADJ.attribute3
3519 , ADJ.attribute4
3520 , ADJ.attribute5
3521 , ADJ.attribute6
3522 , ADJ.attribute7
3523 , ADJ.attribute8
3524 , ADJ.attribute9
3525 , ADJ.attribute10
3526 , ADJ.attribute11
3527 , ADJ.attribute12
3528 , ADJ.attribute13
3529 , ADJ.attribute14
3530 , ADJ.attribute15
3531 , ADJ.orig_sys_discount_ref
3532 , ADJ.list_header_id
3533 , ADJ.list_line_id
3534 , ADJ.list_line_type_code
3535 , ADJ.modified_from
3536 , ADJ.modified_to
3537 , ADJ.updated_flag
3538 , ADJ.update_allowed
3539 , ADJ.applied_flag
3540 , ADJ.change_reason_code
3541 , ADJ.change_reason_text
3542 , ADJ.operand
3543 , ADJ.arithmetic_operator
3544 , ADJ.cost_id
3545 , ADJ.tax_code
3546 , ADJ.tax_exempt_flag
3547 , ADJ.tax_exempt_number
3548 , ADJ.tax_exempt_reason_code
3549 , ADJ.parent_adjustment_id
3550 , ADJ.invoiced_flag
3551 , ADJ.estimated_flag
3552 , ADJ.inc_in_sales_performance
3553 , ADJ.adjusted_amount
3554 , ADJ.pricing_phase_id
3555 , ADJ.charge_type_code
3556 , ADJ.charge_subtype_code
3557 , ADJ.list_line_no
3558 , ADJ.source_system_code
3559 , ADJ.benefit_qty
3560 , ADJ.benefit_uom_code
3561 , ADJ.print_on_invoice_flag
3562 , ADJ.expiration_date
3563 , ADJ.rebate_transaction_type_code
3564 , ADJ.rebate_transaction_reference
3565 , ADJ.rebate_payment_system_code
3566 , ADJ.redeemed_date
3567 , ADJ.redeemed_flag
3568 , ADJ.accrual_flag
3569 , ADJ.range_break_quantity
3570 , ADJ.accrual_conversion_rate
3571 , ADJ.pricing_group_sequence
3572 , ADJ.modifier_level_code
3573 , ADJ.price_break_type_code
3574 , ADJ.substitution_attribute
3575 , ADJ.proration_type_code
3576 , ADJ.credit_or_charge_flag
3577 , ADJ.include_on_returns_flag
3578 , ADJ.ac_context
3579 , ADJ.ac_attribute1
3580 , ADJ.ac_attribute2
3581 , ADJ.ac_attribute3
3582 , ADJ.ac_attribute4
3583 , ADJ.ac_attribute5
3584 , ADJ.ac_attribute6
3585 , ADJ.ac_attribute7
3586 , ADJ.ac_attribute8
3587 , ADJ.ac_attribute9
3588 , ADJ.ac_attribute10
3589 , ADJ.ac_attribute11
3590 , ADJ.ac_attribute12
3591 , ADJ.ac_attribute13
3592 , ADJ.ac_attribute14
3593 , ADJ.ac_attribute15
3594 , ADJ.operand_per_pqty
3595 , ADJ.adjusted_amount_per_pqty
3596 , 1
3597 FROM PO_PRICE_ADJUSTMENTS_ARCHIVE ADJ,
3598 PO_LINES_ALL POL
3599 WHERE ADJ.po_line_id = POL.po_line_id
3600 AND POL.po_header_id = p_document_id
3601 AND ADJ.revision_num = p_revision_num;
3602
3603 d_position := 20;
3604 IF (PO_LOG.d_stmt) THEN
3605 PO_LOG.stmt(d_module, d_position, 'transfer count = ' || SQL%ROWCOUNT);
3606 END IF;
3607
3608 d_position := 30;
3609
3610 IF (PO_LOG.d_proc) THEN
3611 PO_LOG.proc_end(d_module);
3612 END IF;
3613
3614 EXCEPTION
3615 WHEN OTHERS THEN
3616 PO_MESSAGE_S.add_exc_msg
3617 ( p_pkg_name => d_pkg_name,
3618 p_procedure_name => d_api_name || '.' || d_position
3619 );
3620 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3621 END SYNC_PRICE_ADJUSTMENTS;
3622
3623 -------------------------------------------------------------------------------
3624 --Start of Comments
3625 --Name: sync_price_adj_attribs
3626 --Pre-reqs:
3627 -- None.
3628 --Modifies:
3629 -- PO_PRICE_ADJ_ATTRIBS_DRAFT
3630 --Locks:
3631 -- None.
3632 --Function:
3633 -- Sync Price Adjustment Attributes
3634 --Parameters:
3635 --IN:
3636 --p_po_header_id
3637 -- The PO_HEADER_ID of the document that needs to be synced
3638 --p_revision_num
3639 -- The revision number of the document that needs to be synced
3640 --p_draft_id
3641 -- The draft_id using which the record has to be inserted at the time of sync
3642 --Testing:
3643 -- None.
3644 --End of Comments
3645 -------------------------------------------------------------------------------
3646 PROCEDURE SYNC_PRICE_ADJ_ATTRIBS(p_document_id IN NUMBER,
3647 p_revision_num IN NUMBER,
3648 p_draft_id IN NUMBER)
3649 IS
3650 d_api_name CONSTANT VARCHAR2(30) := 'SYNC_PRICE_ADJ_ATTRIBS';
3651 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
3652 d_position NUMBER;
3653
3654 BEGIN
3655
3656 d_position := 0;
3657 IF (PO_LOG.d_proc) THEN
3658 PO_LOG.proc_begin(d_module,'p_po_header_id',p_document_id);
3659 END IF;
3660
3661 d_position := 10;
3662 IF (PO_LOG.d_stmt) THEN
3663 PO_LOG.stmt(d_module, d_position, 'transfer records from archive to dft');
3664 END IF;
3665
3666 INSERT INTO PO_PRICE_ADJ_ATTRIBS_DRAFT
3667 (DRAFT_ID
3668 , PRICE_ADJUSTMENT_ID
3669 , PRICING_CONTEXT
3670 , PRICING_ATTRIBUTE
3671 , CREATION_DATE
3672 , CREATED_BY
3673 , LAST_UPDATE_DATE
3674 , LAST_UPDATED_BY
3675 , LAST_UPDATE_LOGIN
3676 , PROGRAM_APPLICATION_ID
3677 , PROGRAM_ID
3678 , PROGRAM_UPDATE_DATE
3679 , REQUEST_ID
3680 , PRICING_ATTR_VALUE_FROM
3681 , PRICING_ATTR_VALUE_TO
3682 , COMPARISON_OPERATOR
3683 , FLEX_TITLE
3684 , PRICE_ADJ_ATTRIB_ID
3685 , LOCK_CONTROL
3686 )
3687 SELECT
3688 p_draft_id -- Always 0 for action View Base Document (Revision 0)
3689 , ATTR.price_adjustment_id
3690 , ATTR.pricing_context
3691 , ATTR.pricing_attribute
3692 , ATTR.creation_date
3693 , ATTR.created_by
3694 , ATTR.last_update_date
3695 , ATTR.last_updated_by
3696 , ATTR.last_update_login
3697 , ATTR.program_application_id
3698 , ATTR.program_id
3699 , ATTR.program_update_date
3700 , ATTR.request_id
3701 , ATTR.pricing_attr_value_from
3702 , ATTR.pricing_attr_value_to
3703 , ATTR.comparison_operator
3704 , ATTR.flex_title
3705 , ATTR.price_adj_attrib_id
3706 , 1
3707 FROM PO_PRICE_ADJ_ATTRIBS_ARCHIVE ATTR,
3708 PO_PRICE_ADJUSTMENTS ADJ,
3709 PO_LINES_ALL POL
3710 WHERE ATTR.price_adjustment_id = ADJ.price_adjustment_id
3711 AND ADJ.po_line_id = POL.po_line_id
3712 AND POL.po_header_id = p_document_id
3713 AND ATTR.revision_num = p_revision_num;
3714
3715
3716 d_position := 20;
3717 IF (PO_LOG.d_stmt) THEN
3718 PO_LOG.stmt(d_module, d_position, 'transfer count = ' || SQL%ROWCOUNT);
3719 END IF;
3720
3721 d_position := 30;
3722
3723 IF (PO_LOG.d_proc) THEN
3724 PO_LOG.proc_end(d_module);
3725 END IF;
3726
3727 EXCEPTION
3728 WHEN OTHERS THEN
3729 PO_MESSAGE_S.add_exc_msg
3730 ( p_pkg_name => d_pkg_name,
3731 p_procedure_name => d_api_name || '.' || d_position
3732 );
3733 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3734 END SYNC_PRICE_ADJ_ATTRIBS;
3735
3736 -----------------------------------------------------------------------
3737 --Start of Comments
3738 --Name: SYNC_NOTIFICATION_CONTROLS
3739 --Pre-reqs: None
3740 --Modifies:
3741 -- po_notification_ctrl_draft
3742 --Locks:
3743 -- None.
3744 --Function:
3745 -- Sync Notification Controls
3746 --Parameters:
3747 --IN:
3748 --p_document_id
3749 -- The PO_HEADER_ID of the document that needs to be synced
3750 --p_revision_num
3751 -- The revision number of the document that needs to be synced
3752 --p_draft_id
3753 -- The draft_id using which the record has to be inserted at the time of sync
3754 --Testing:
3755 -- None.
3756 --End of Comments
3757
3758 ------------------------------------------------------------------------
3759 PROCEDURE SYNC_NOTIFICATION_CONTROLS(p_document_id IN NUMBER,
3760 p_revision_num IN NUMBER,
3761 p_draft_id IN NUMBER)
3762 IS
3763
3764 d_api_name CONSTANT VARCHAR2(30) := 'SYNC_NOTIFICATION_CONTROLS';
3765 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
3766 d_position NUMBER;
3767
3768
3769 BEGIN
3770 d_position := 0;
3771 IF (PO_LOG.d_proc) THEN
3772 PO_LOG.proc_begin(d_module);
3773 END IF;
3774
3775 d_position := 10;
3776 IF (PO_LOG.d_stmt) THEN
3777 PO_LOG.stmt(d_module, d_position, 'transfer records from archive to dft');
3778 END IF;
3779
3780 INSERT INTO po_notification_ctrl_draft
3781 (
3782 draft_id,
3783 delete_flag,
3784 change_accepted_flag,
3785 change_status,
3786 notification_id,
3787 po_header_id,
3788 start_date_active,
3789 end_date_active,
3790 notification_amount,
3791 notification_condition_code,
3792 notification_qty_percentage,
3793 last_update_date,
3794 last_update_login,
3795 last_updated_by,
3796 program_id,
3797 program_application_id,
3798 program_update_date,
3799 request_id,
3800 created_by,
3801 creation_date,
3802 attribute_category,
3803 attribute1,
3804 attribute10,
3805 attribute11,
3806 attribute12,
3807 attribute13,
3808 attribute14,
3809 attribute15,
3810 attribute2,
3811 attribute3,
3812 attribute4,
3813 attribute5,
3814 attribute6,
3815 attribute7,
3816 attribute8,
3817 attribute9
3818 )
3819 SELECT
3820 p_draft_id, -- Always 0 for action View Base Document (Revision 0)
3821 NULL,
3822 NULL,
3823 'NOCHANGE',
3824 notification_id,
3825 po_header_id,
3826 start_date_active,
3827 end_date_active,
3828 notification_amount,
3829 notification_condition_code,
3830 notification_qty_percentage,
3831 last_update_date,
3832 last_update_login,
3833 last_updated_by,
3834 program_id,
3835 program_application_id,
3836 program_update_date,
3837 request_id,
3838 created_by,
3839 creation_date,
3840 attribute_category,
3841 attribute1,
3842 attribute10,
3843 attribute11,
3844 attribute12,
3845 attribute13,
3846 attribute14,
3847 attribute15,
3848 attribute2,
3849 attribute3,
3850 attribute4,
3851 attribute5,
3852 attribute6,
3853 attribute7,
3854 attribute8,
3855 attribute9
3856 FROM po_notification_controls
3857 WHERE po_header_id = p_document_id;
3858
3859 d_position := 20;
3860 IF (PO_LOG.d_stmt) THEN
3861 PO_LOG.stmt(d_module, d_position, 'transfer count = ' || SQL%ROWCOUNT);
3862 END IF;
3863
3864 d_position := 30;
3865
3866 IF (PO_LOG.d_proc) THEN
3867 PO_LOG.proc_end(d_module);
3868 END IF;
3869
3870 EXCEPTION
3871 WHEN OTHERS THEN
3872 PO_MESSAGE_S.add_exc_msg
3873 ( p_pkg_name => d_pkg_name,
3874 p_procedure_name => d_api_name || '.' || d_position
3875 );
3876 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3877 END SYNC_NOTIFICATION_CONTROLS;
3878
3879 -----------------------------------------------------------------------
3880 --Start of Comments
3881 --Name: SYNC_UDA_ATTRIBS
3882 --Pre-reqs: None
3883 --Modifies:
3884 -- PO_HEADERS_ALL_EXT_B / PO_HEADERS_ALL_EXT_TL
3885 -- PO_LINES_ALL_EXT_B / PO_LINES_ALL_EXT_TL
3886 -- PO_LINE_LOCATIONS_ALL_EXT_B / PO_LINE_LOCATIONS_ALL_EXT_TL
3887 --Locks:
3888 -- None.
3889 --Function:
3890 -- Sync UDA Data from Archive to Transaction Table
3891 --Parameters:
3892 --IN:
3893 --p_document_id
3894 -- The PO_HEADER_ID of the document that needs to be synced
3895 --p_document_type
3896 -- The Document Type of the document that needs to be synced
3897 --p_document_subtype
3898 -- The Document Subtype of the document that needs to be synced
3899 --p_revision_num
3900 -- The revision number of the document that is being synced.
3901 -- In the current Scope of View Base Document project,
3902 -- only the 1st version (Revision 0) will be synced for UDA's
3903 --p_draft_id
3904 -- The draft_id using which the record has to be inserted at the time of sync
3905 --Testing:
3906 -- None.
3907 --End of Comments
3908
3909 ------------------------------------------------------------------------
3910 PROCEDURE SYNC_UDA_ATTRIBS(p_document_id IN NUMBER,
3911 p_document_type IN VARCHAR2,
3912 p_document_subtype IN VARCHAR2,
3913 p_revision_num IN NUMBER,
3914 p_draft_id IN NUMBER)
3915 IS
3916
3917 d_api_name CONSTANT VARCHAR2(30) := 'SYNC_UDA_ATTRIBS';
3918 d_mod CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
3919 d_position NUMBER;
3920 l_functional_area_code VARCHAR2(500);
3921 l_document_type_code VARCHAR2(500);
3922 l_document_style_id NUMBER;
3923 l_msg_count NUMBER;
3924 l_msg_data VARCHAR2(4000);
3925 l_enabled_flag VARCHAR2(5);
3926 l_errorcode VARCHAR2(50);
3927 l_return_status VARCHAR2(1);
3928
3929 l_old_hdr_ext_id_tbl PO_TBL_NUMBER;
3930 l_new_hdr_ext_id_tbl PO_TBL_NUMBER;
3931 l_old_line_ext_id_tbl PO_TBL_NUMBER;
3932 l_new_line_ext_id_tbl PO_TBL_NUMBER;
3933 l_old_line_loc_ext_id_tbl PO_TBL_NUMBER;
3934 l_new_line_loc_ext_id_tbl PO_TBL_NUMBER;
3935
3936 CURSOR po_headers_ext_cursor IS
3937 SELECT extension_id, EGO_EXTFWK_S.NEXTVAL
3938 FROM PO_HEADERS_ALL_EXT_B_ARCHIVE
3939 WHERE po_header_id = p_document_id
3940 AND draft_id = -1
3941 AND revision_num = p_revision_num;
3942
3943 CURSOR po_lines_ext_cursor IS
3944 SELECT POLE.extension_id, EGO_EXTFWK_S.NEXTVAL
3945 FROM PO_LINES_ALL_EXT_B_ARCHIVE POLE,
3946 PO_LINES_ALL POL
3947 WHERE POL.po_header_id = p_document_id
3948 AND POL.po_line_id = POLE.po_line_id
3949 AND POLE.draft_id = -1
3950 AND POLE.revision_num = p_revision_num;
3951
3952 CURSOR po_line_loc_ext_cursor IS
3953 SELECT POLE.extension_id, EGO_EXTFWK_S.NEXTVAL
3954 FROM PO_LINE_LOC_ALL_EXT_B_ARCHIVE POLE,
3955 PO_LINE_LOCATIONS_ALL POL
3956 WHERE POL.po_header_id = p_document_id
3957 AND POL.line_location_id = POLE.line_location_id
3958 AND POLE.draft_id = -1
3959 AND POLE.revision_num = p_revision_num;
3960
3961
3962 BEGIN
3963 d_position := 0;
3964 IF (PO_LOG.d_proc) THEN
3965 PO_LOG.proc_begin(d_mod);
3966 END IF;
3967
3968 d_position := 10;
3969 IF (PO_LOG.d_stmt) THEN
3970 PO_LOG.stmt(d_mod, d_position, 'transfer UDA records from archive to txn');
3971 END IF;
3972
3973 -- Check if Document is UDA Enabled
3974 SELECT 'PURCHASING', TYPE_LOOKUP_CODE, STYLE_ID
3975 INTO l_functional_area_code, l_document_type_code, l_document_style_id
3976 FROM po_headers_all
3977 WHERE PO_HEADER_ID = p_document_id;
3978
3979 d_position := 20;
3980 IF PO_LOG.d_stmt THEN
3981 PO_LOG.stmt(d_mod,d_position,'Before Check_Uda_Enabled');
3982 END IF;
3983
3984 PO_CLM_CLO_UTIL.Check_Uda_Enabled
3985 (
3986 p_functional_area_code => l_functional_area_code
3987 ,p_document_type_code => l_document_type_code
3988 ,p_document_style_id => l_document_style_id
3989 ,x_enabled_flag => l_enabled_flag
3990 ,x_return_status => l_return_status
3991 ,x_errorcode => l_errorcode
3992 ,x_msg_count => l_msg_count
3993 ,x_msg_data => l_msg_data
3994 );
3995
3996 d_position := 30;
3997 IF PO_LOG.d_stmt THEN
3998 PO_LOG.stmt(d_mod,d_position,'After Check_Uda_Enabled with x_return_status : ' || l_return_status);
3999 END IF;
4000
4001 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4002 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4003 END IF;
4004 -- End Check if Document is UDA Enabled
4005
4006 -- Sync UDA data only for UDA_enabled Docs, and only for DocTypes PO and PA
4007 IF l_enabled_flag = 'Y' AND p_document_type IN ('PO', 'PA') THEN
4008
4009 OPEN po_headers_ext_cursor;
4010 FETCH po_headers_ext_cursor BULK COLLECT INTO l_old_hdr_ext_id_tbl, l_new_hdr_ext_id_tbl;
4011 CLOSE po_headers_ext_cursor;
4012
4013 -- Start Sync PO_HEADERS_ALL_EXT_B
4014 d_position := 40;
4015 IF PO_LOG.d_stmt THEN
4016 PO_LOG.stmt(d_mod,d_position,'Sync PO_HEADERS_ALL_EXT_B');
4017 END IF;
4018
4019 FORALL i IN 1..l_old_hdr_ext_id_tbl.Count
4020 INSERT INTO PO_HEADERS_ALL_EXT_B
4021 (EXTENSION_ID
4022 ,ATTR_GROUP_ID
4023 ,PO_HEADER_ID
4024 ,DATA_LEVEL_ID
4025 ,PK1_VALUE
4026 ,PK2_VALUE
4027 ,PK3_VALUE
4028 ,PK4_VALUE
4029 ,PK5_VALUE
4030 ,LAST_UPDATE_DATE
4031 ,LAST_UPDATED_BY
4032 ,LAST_UPDATE_LOGIN
4033 ,CREATED_BY
4034 ,CREATION_DATE
4035 ,C_EXT_ATTR1
4036 ,C_EXT_ATTR2
4037 ,C_EXT_ATTR3
4038 ,C_EXT_ATTR4
4039 ,C_EXT_ATTR5
4040 ,C_EXT_ATTR6
4041 ,C_EXT_ATTR7
4042 ,C_EXT_ATTR8
4043 ,C_EXT_ATTR9
4044 ,C_EXT_ATTR10
4045 ,C_EXT_ATTR11
4046 ,C_EXT_ATTR12
4047 ,C_EXT_ATTR13
4048 ,C_EXT_ATTR14
4049 ,C_EXT_ATTR15
4050 ,C_EXT_ATTR16
4051 ,C_EXT_ATTR17
4052 ,C_EXT_ATTR18
4053 ,C_EXT_ATTR19
4054 ,C_EXT_ATTR20
4055 ,C_EXT_ATTR21
4056 ,C_EXT_ATTR22
4057 ,C_EXT_ATTR23
4058 ,C_EXT_ATTR24
4059 ,C_EXT_ATTR25
4060 ,C_EXT_ATTR26
4061 ,C_EXT_ATTR27
4062 ,C_EXT_ATTR28
4063 ,C_EXT_ATTR29
4064 ,C_EXT_ATTR30
4065 ,C_EXT_ATTR31
4066 ,C_EXT_ATTR32
4067 ,C_EXT_ATTR33
4068 ,C_EXT_ATTR34
4069 ,C_EXT_ATTR35
4070 ,C_EXT_ATTR36
4071 ,C_EXT_ATTR37
4072 ,C_EXT_ATTR38
4073 ,C_EXT_ATTR39
4074 ,C_EXT_ATTR40
4075 ,N_EXT_ATTR1
4076 ,N_EXT_ATTR2
4077 ,N_EXT_ATTR3
4078 ,N_EXT_ATTR4
4079 ,N_EXT_ATTR5
4080 ,N_EXT_ATTR6
4081 ,N_EXT_ATTR7
4082 ,N_EXT_ATTR8
4083 ,N_EXT_ATTR9
4084 ,N_EXT_ATTR10
4085 ,N_EXT_ATTR11
4086 ,N_EXT_ATTR12
4087 ,N_EXT_ATTR13
4088 ,N_EXT_ATTR14
4089 ,N_EXT_ATTR15
4090 ,N_EXT_ATTR16
4091 ,N_EXT_ATTR17
4092 ,N_EXT_ATTR18
4093 ,N_EXT_ATTR19
4094 ,N_EXT_ATTR20
4095 ,UOM_EXT_ATTR1
4096 ,UOM_EXT_ATTR2
4097 ,UOM_EXT_ATTR3
4098 ,UOM_EXT_ATTR4
4099 ,UOM_EXT_ATTR5
4100 ,UOM_EXT_ATTR6
4101 ,UOM_EXT_ATTR7
4102 ,UOM_EXT_ATTR8
4103 ,UOM_EXT_ATTR9
4104 ,UOM_EXT_ATTR10
4105 ,UOM_EXT_ATTR11
4106 ,UOM_EXT_ATTR12
4107 ,UOM_EXT_ATTR13
4108 ,UOM_EXT_ATTR14
4109 ,UOM_EXT_ATTR15
4110 ,UOM_EXT_ATTR16
4111 ,UOM_EXT_ATTR17
4112 ,UOM_EXT_ATTR18
4113 ,UOM_EXT_ATTR19
4114 ,UOM_EXT_ATTR20
4115 ,D_EXT_ATTR1
4116 ,D_EXT_ATTR2
4117 ,D_EXT_ATTR3
4118 ,D_EXT_ATTR4
4119 ,D_EXT_ATTR5
4120 ,D_EXT_ATTR6
4121 ,D_EXT_ATTR7
4122 ,D_EXT_ATTR8
4123 ,D_EXT_ATTR9
4124 ,D_EXT_ATTR10
4125 ,UDA_TEMPLATE_ID
4126 ,DRAFT_ID
4127 )
4128 SELECT
4129 l_new_hdr_ext_id_tbl(i)
4130 ,ATTR_GROUP_ID
4131 ,PO_HEADER_ID
4132 ,DATA_LEVEL_ID
4133 ,PK1_VALUE
4134 ,PK2_VALUE
4135 ,PK3_VALUE
4136 ,PK4_VALUE
4137 ,PK5_VALUE
4138 ,LAST_UPDATE_DATE
4139 ,LAST_UPDATED_BY
4140 ,LAST_UPDATE_LOGIN
4141 ,CREATED_BY
4142 ,CREATION_DATE
4143 ,C_EXT_ATTR1
4144 ,C_EXT_ATTR2
4145 ,C_EXT_ATTR3
4146 ,C_EXT_ATTR4
4147 ,C_EXT_ATTR5
4148 ,C_EXT_ATTR6
4149 ,C_EXT_ATTR7
4150 ,C_EXT_ATTR8
4151 ,C_EXT_ATTR9
4152 ,C_EXT_ATTR10
4153 ,C_EXT_ATTR11
4154 ,C_EXT_ATTR12
4155 ,C_EXT_ATTR13
4156 ,C_EXT_ATTR14
4157 ,C_EXT_ATTR15
4158 ,C_EXT_ATTR16
4159 ,C_EXT_ATTR17
4160 ,C_EXT_ATTR18
4161 ,C_EXT_ATTR19
4162 ,C_EXT_ATTR20
4163 ,C_EXT_ATTR21
4164 ,C_EXT_ATTR22
4165 ,C_EXT_ATTR23
4166 ,C_EXT_ATTR24
4167 ,C_EXT_ATTR25
4168 ,C_EXT_ATTR26
4169 ,C_EXT_ATTR27
4170 ,C_EXT_ATTR28
4171 ,C_EXT_ATTR29
4172 ,C_EXT_ATTR30
4173 ,C_EXT_ATTR31
4174 ,C_EXT_ATTR32
4175 ,C_EXT_ATTR33
4176 ,C_EXT_ATTR34
4177 ,C_EXT_ATTR35
4178 ,C_EXT_ATTR36
4179 ,C_EXT_ATTR37
4180 ,C_EXT_ATTR38
4181 ,C_EXT_ATTR39
4182 ,C_EXT_ATTR40
4183 ,N_EXT_ATTR1
4184 ,N_EXT_ATTR2
4185 ,N_EXT_ATTR3
4186 ,N_EXT_ATTR4
4187 ,N_EXT_ATTR5
4188 ,N_EXT_ATTR6
4189 ,N_EXT_ATTR7
4190 ,N_EXT_ATTR8
4191 ,N_EXT_ATTR9
4192 ,N_EXT_ATTR10
4193 ,N_EXT_ATTR11
4194 ,N_EXT_ATTR12
4195 ,N_EXT_ATTR13
4196 ,N_EXT_ATTR14
4197 ,N_EXT_ATTR15
4198 ,N_EXT_ATTR16
4199 ,N_EXT_ATTR17
4200 ,N_EXT_ATTR18
4201 ,N_EXT_ATTR19
4202 ,N_EXT_ATTR20
4203 ,UOM_EXT_ATTR1
4204 ,UOM_EXT_ATTR2
4205 ,UOM_EXT_ATTR3
4206 ,UOM_EXT_ATTR4
4207 ,UOM_EXT_ATTR5
4208 ,UOM_EXT_ATTR6
4209 ,UOM_EXT_ATTR7
4210 ,UOM_EXT_ATTR8
4211 ,UOM_EXT_ATTR9
4212 ,UOM_EXT_ATTR10
4213 ,UOM_EXT_ATTR11
4214 ,UOM_EXT_ATTR12
4215 ,UOM_EXT_ATTR13
4216 ,UOM_EXT_ATTR14
4217 ,UOM_EXT_ATTR15
4218 ,UOM_EXT_ATTR16
4219 ,UOM_EXT_ATTR17
4220 ,UOM_EXT_ATTR18
4221 ,UOM_EXT_ATTR19
4222 ,UOM_EXT_ATTR20
4223 ,D_EXT_ATTR1
4224 ,D_EXT_ATTR2
4225 ,D_EXT_ATTR3
4226 ,D_EXT_ATTR4
4227 ,D_EXT_ATTR5
4228 ,D_EXT_ATTR6
4229 ,D_EXT_ATTR7
4230 ,D_EXT_ATTR8
4231 ,D_EXT_ATTR9
4232 ,D_EXT_ATTR10
4233 ,UDA_TEMPLATE_ID
4234 ,p_draft_id
4235 FROM PO_HEADERS_ALL_EXT_B_ARCHIVE
4236 WHERE extension_id = l_old_hdr_ext_id_tbl(i)
4237 AND revision_num = p_revision_num;
4238
4239 d_position := 50;
4240 IF PO_LOG.d_stmt THEN
4241 PO_LOG.stmt(d_mod,d_position,'Number of rows inserted: '||SQL%rowcount);
4242 END IF;
4243 -- End PO_HEADERS_ALL_EXT_B
4244
4245 -- Start PO_HEADERS_ALL_EXT_TL
4246 d_position := 60;
4247 IF PO_LOG.d_stmt THEN
4248 PO_LOG.stmt(d_mod,d_position,'Sync PO_HEADERS_ALL_EXT_TL');
4249 END IF;
4250
4251 FORALL i IN 1..l_old_hdr_ext_id_tbl.Count
4252 INSERT INTO PO_HEADERS_ALL_EXT_TL
4253 (EXTENSION_ID
4254 ,ATTR_GROUP_ID
4255 ,PO_HEADER_ID
4256 ,DATA_LEVEL_ID
4257 ,PK1_VALUE
4258 ,PK2_VALUE
4259 ,PK3_VALUE
4260 ,PK4_VALUE
4261 ,PK5_VALUE
4262 ,SOURCE_LANG
4263 ,LANGUAGE
4264 ,LAST_UPDATE_DATE
4265 ,LAST_UPDATED_BY
4266 ,LAST_UPDATE_LOGIN
4267 ,CREATED_BY
4268 ,CREATION_DATE
4269 ,TL_EXT_ATTR1
4270 ,TL_EXT_ATTR2
4271 ,TL_EXT_ATTR3
4272 ,TL_EXT_ATTR4
4273 ,TL_EXT_ATTR5
4274 ,TL_EXT_ATTR6
4275 ,TL_EXT_ATTR7
4276 ,TL_EXT_ATTR8
4277 ,TL_EXT_ATTR9
4278 ,TL_EXT_ATTR10
4279 ,TL_EXT_ATTR11
4280 ,TL_EXT_ATTR12
4281 ,TL_EXT_ATTR13
4282 ,TL_EXT_ATTR14
4283 ,TL_EXT_ATTR15
4284 ,TL_EXT_ATTR16
4285 ,TL_EXT_ATTR17
4286 ,TL_EXT_ATTR18
4287 ,TL_EXT_ATTR19
4288 ,TL_EXT_ATTR20
4289 ,TL_EXT_ATTR21
4290 ,TL_EXT_ATTR22
4291 ,TL_EXT_ATTR23
4292 ,TL_EXT_ATTR24
4293 ,TL_EXT_ATTR25
4294 ,TL_EXT_ATTR26
4295 ,TL_EXT_ATTR27
4296 ,TL_EXT_ATTR28
4297 ,TL_EXT_ATTR29
4298 ,TL_EXT_ATTR30
4299 ,TL_EXT_ATTR31
4300 ,TL_EXT_ATTR32
4301 ,TL_EXT_ATTR33
4302 ,TL_EXT_ATTR34
4303 ,TL_EXT_ATTR35
4304 ,TL_EXT_ATTR36
4305 ,TL_EXT_ATTR37
4306 ,TL_EXT_ATTR38
4307 ,TL_EXT_ATTR39
4308 ,TL_EXT_ATTR40
4309 ,UDA_TEMPLATE_ID
4310 ,DRAFT_ID
4311 )
4312 SELECT
4313 l_new_hdr_ext_id_tbl(i)
4314 ,ATTR_GROUP_ID
4315 ,PO_HEADER_ID
4316 ,DATA_LEVEL_ID
4317 ,PK1_VALUE
4318 ,PK2_VALUE
4319 ,PK3_VALUE
4320 ,PK4_VALUE
4321 ,PK5_VALUE
4322 ,SOURCE_LANG
4323 ,LANGUAGE
4324 ,LAST_UPDATE_DATE
4325 ,LAST_UPDATED_BY
4326 ,LAST_UPDATE_LOGIN
4327 ,CREATED_BY
4328 ,CREATION_DATE
4329 ,TL_EXT_ATTR1
4330 ,TL_EXT_ATTR2
4331 ,TL_EXT_ATTR3
4332 ,TL_EXT_ATTR4
4333 ,TL_EXT_ATTR5
4334 ,TL_EXT_ATTR6
4335 ,TL_EXT_ATTR7
4336 ,TL_EXT_ATTR8
4337 ,TL_EXT_ATTR9
4338 ,TL_EXT_ATTR10
4339 ,TL_EXT_ATTR11
4340 ,TL_EXT_ATTR12
4341 ,TL_EXT_ATTR13
4342 ,TL_EXT_ATTR14
4343 ,TL_EXT_ATTR15
4344 ,TL_EXT_ATTR16
4345 ,TL_EXT_ATTR17
4346 ,TL_EXT_ATTR18
4347 ,TL_EXT_ATTR19
4348 ,TL_EXT_ATTR20
4349 ,TL_EXT_ATTR21
4350 ,TL_EXT_ATTR22
4351 ,TL_EXT_ATTR23
4352 ,TL_EXT_ATTR24
4353 ,TL_EXT_ATTR25
4354 ,TL_EXT_ATTR26
4355 ,TL_EXT_ATTR27
4356 ,TL_EXT_ATTR28
4357 ,TL_EXT_ATTR29
4358 ,TL_EXT_ATTR30
4359 ,TL_EXT_ATTR31
4360 ,TL_EXT_ATTR32
4361 ,TL_EXT_ATTR33
4362 ,TL_EXT_ATTR34
4363 ,TL_EXT_ATTR35
4364 ,TL_EXT_ATTR36
4365 ,TL_EXT_ATTR37
4366 ,TL_EXT_ATTR38
4367 ,TL_EXT_ATTR39
4368 ,TL_EXT_ATTR40
4369 ,UDA_TEMPLATE_ID
4370 ,p_draft_id
4371 FROM PO_HEADERS_ALL_EXT_TL_ARCHIVE
4372 WHERE extension_id = l_old_hdr_ext_id_tbl(i)
4373 AND language = userenv('LANG')
4374 AND revision_num = p_revision_num;
4375
4376 d_position := 70;
4377 IF PO_LOG.d_stmt THEN
4378 PO_LOG.stmt(d_mod,d_position,'Number of rows inserted: '||SQL%rowcount);
4379 END IF;
4380 -- End PO_HEADERS_ALL_EXT_TL
4381
4382 -- Sync UDA Line and Line Locations data only for PO or PA BLANKETS
4383 IF (p_document_type = 'PO') OR
4384 (p_document_type = 'PA' AND p_document_subtype='BLANKET') THEN
4385
4386 OPEN po_lines_ext_cursor;
4387 FETCH po_lines_ext_cursor BULK COLLECT INTO l_old_line_ext_id_tbl, l_new_line_ext_id_tbl;
4388 CLOSE po_lines_ext_cursor;
4389
4390 -- Start PO_LINES_ALL_EXT_B
4391 d_position := 80;
4392 IF PO_LOG.d_stmt THEN
4393 PO_LOG.stmt(d_mod,d_position,'Sync PO_LINES_ALL_EXT_B');
4394 END IF;
4395
4396 FORALL i IN 1..l_old_line_ext_id_tbl.Count
4397 INSERT INTO PO_LINES_ALL_EXT_B
4398 (EXTENSION_ID
4399 ,ATTR_GROUP_ID
4400 ,PO_LINE_ID
4401 ,DATA_LEVEL_ID
4402 ,PK1_VALUE
4403 ,PK2_VALUE
4404 ,PK3_VALUE
4405 ,PK4_VALUE
4406 ,PK5_VALUE
4407 ,LAST_UPDATE_DATE
4408 ,LAST_UPDATED_BY
4409 ,LAST_UPDATE_LOGIN
4410 ,CREATED_BY
4411 ,CREATION_DATE
4412 ,C_EXT_ATTR1
4413 ,C_EXT_ATTR2
4414 ,C_EXT_ATTR3
4415 ,C_EXT_ATTR4
4416 ,C_EXT_ATTR5
4417 ,C_EXT_ATTR6
4418 ,C_EXT_ATTR7
4419 ,C_EXT_ATTR8
4420 ,C_EXT_ATTR9
4421 ,C_EXT_ATTR10
4422 ,C_EXT_ATTR11
4423 ,C_EXT_ATTR12
4424 ,C_EXT_ATTR13
4425 ,C_EXT_ATTR14
4426 ,C_EXT_ATTR15
4427 ,C_EXT_ATTR16
4428 ,C_EXT_ATTR17
4429 ,C_EXT_ATTR18
4430 ,C_EXT_ATTR19
4431 ,C_EXT_ATTR20
4432 ,C_EXT_ATTR21
4433 ,C_EXT_ATTR22
4434 ,C_EXT_ATTR23
4435 ,C_EXT_ATTR24
4436 ,C_EXT_ATTR25
4437 ,C_EXT_ATTR26
4438 ,C_EXT_ATTR27
4439 ,C_EXT_ATTR28
4440 ,C_EXT_ATTR29
4441 ,C_EXT_ATTR30
4442 ,C_EXT_ATTR31
4443 ,C_EXT_ATTR32
4444 ,C_EXT_ATTR33
4445 ,C_EXT_ATTR34
4446 ,C_EXT_ATTR35
4447 ,C_EXT_ATTR36
4448 ,C_EXT_ATTR37
4449 ,C_EXT_ATTR38
4450 ,C_EXT_ATTR39
4451 ,C_EXT_ATTR40
4452 ,N_EXT_ATTR1
4453 ,N_EXT_ATTR2
4454 ,N_EXT_ATTR3
4455 ,N_EXT_ATTR4
4456 ,N_EXT_ATTR5
4457 ,N_EXT_ATTR6
4458 ,N_EXT_ATTR7
4459 ,N_EXT_ATTR8
4460 ,N_EXT_ATTR9
4461 ,N_EXT_ATTR10
4462 ,N_EXT_ATTR11
4463 ,N_EXT_ATTR12
4464 ,N_EXT_ATTR13
4465 ,N_EXT_ATTR14
4466 ,N_EXT_ATTR15
4467 ,N_EXT_ATTR16
4468 ,N_EXT_ATTR17
4469 ,N_EXT_ATTR18
4470 ,N_EXT_ATTR19
4471 ,N_EXT_ATTR20
4472 ,UOM_EXT_ATTR1
4473 ,UOM_EXT_ATTR2
4474 ,UOM_EXT_ATTR3
4475 ,UOM_EXT_ATTR4
4476 ,UOM_EXT_ATTR5
4477 ,UOM_EXT_ATTR6
4478 ,UOM_EXT_ATTR7
4479 ,UOM_EXT_ATTR8
4480 ,UOM_EXT_ATTR9
4481 ,UOM_EXT_ATTR10
4482 ,UOM_EXT_ATTR11
4483 ,UOM_EXT_ATTR12
4484 ,UOM_EXT_ATTR13
4485 ,UOM_EXT_ATTR14
4486 ,UOM_EXT_ATTR15
4487 ,UOM_EXT_ATTR16
4488 ,UOM_EXT_ATTR17
4489 ,UOM_EXT_ATTR18
4490 ,UOM_EXT_ATTR19
4491 ,UOM_EXT_ATTR20
4492 ,D_EXT_ATTR1
4493 ,D_EXT_ATTR2
4494 ,D_EXT_ATTR3
4495 ,D_EXT_ATTR4
4496 ,D_EXT_ATTR5
4497 ,D_EXT_ATTR6
4498 ,D_EXT_ATTR7
4499 ,D_EXT_ATTR8
4500 ,D_EXT_ATTR9
4501 ,D_EXT_ATTR10
4502 ,UDA_TEMPLATE_ID
4503 ,DRAFT_ID
4504 )
4505 SELECT
4506 l_new_line_ext_id_tbl(i)
4507 ,ATTR_GROUP_ID
4508 ,PO_LINE_ID
4509 ,DATA_LEVEL_ID
4510 ,PK1_VALUE
4511 ,PK2_VALUE
4512 ,PK3_VALUE
4513 ,PK4_VALUE
4514 ,PK5_VALUE
4515 ,LAST_UPDATE_DATE
4516 ,LAST_UPDATED_BY
4517 ,LAST_UPDATE_LOGIN
4518 ,CREATED_BY
4519 ,CREATION_DATE
4520 ,C_EXT_ATTR1
4521 ,C_EXT_ATTR2
4522 ,C_EXT_ATTR3
4523 ,C_EXT_ATTR4
4524 ,C_EXT_ATTR5
4525 ,C_EXT_ATTR6
4526 ,C_EXT_ATTR7
4527 ,C_EXT_ATTR8
4528 ,C_EXT_ATTR9
4529 ,C_EXT_ATTR10
4530 ,C_EXT_ATTR11
4531 ,C_EXT_ATTR12
4532 ,C_EXT_ATTR13
4533 ,C_EXT_ATTR14
4534 ,C_EXT_ATTR15
4535 ,C_EXT_ATTR16
4536 ,C_EXT_ATTR17
4537 ,C_EXT_ATTR18
4538 ,C_EXT_ATTR19
4539 ,C_EXT_ATTR20
4540 ,C_EXT_ATTR21
4541 ,C_EXT_ATTR22
4542 ,C_EXT_ATTR23
4543 ,C_EXT_ATTR24
4544 ,C_EXT_ATTR25
4545 ,C_EXT_ATTR26
4546 ,C_EXT_ATTR27
4547 ,C_EXT_ATTR28
4548 ,C_EXT_ATTR29
4549 ,C_EXT_ATTR30
4550 ,C_EXT_ATTR31
4551 ,C_EXT_ATTR32
4552 ,C_EXT_ATTR33
4553 ,C_EXT_ATTR34
4554 ,C_EXT_ATTR35
4555 ,C_EXT_ATTR36
4556 ,C_EXT_ATTR37
4557 ,C_EXT_ATTR38
4558 ,C_EXT_ATTR39
4559 ,C_EXT_ATTR40
4560 ,N_EXT_ATTR1
4561 ,N_EXT_ATTR2
4562 ,N_EXT_ATTR3
4563 ,N_EXT_ATTR4
4564 ,N_EXT_ATTR5
4565 ,N_EXT_ATTR6
4566 ,N_EXT_ATTR7
4567 ,N_EXT_ATTR8
4568 ,N_EXT_ATTR9
4569 ,N_EXT_ATTR10
4570 ,N_EXT_ATTR11
4571 ,N_EXT_ATTR12
4572 ,N_EXT_ATTR13
4573 ,N_EXT_ATTR14
4574 ,N_EXT_ATTR15
4575 ,N_EXT_ATTR16
4576 ,N_EXT_ATTR17
4577 ,N_EXT_ATTR18
4578 ,N_EXT_ATTR19
4579 ,N_EXT_ATTR20
4580 ,UOM_EXT_ATTR1
4581 ,UOM_EXT_ATTR2
4582 ,UOM_EXT_ATTR3
4583 ,UOM_EXT_ATTR4
4584 ,UOM_EXT_ATTR5
4585 ,UOM_EXT_ATTR6
4586 ,UOM_EXT_ATTR7
4587 ,UOM_EXT_ATTR8
4588 ,UOM_EXT_ATTR9
4589 ,UOM_EXT_ATTR10
4590 ,UOM_EXT_ATTR11
4591 ,UOM_EXT_ATTR12
4592 ,UOM_EXT_ATTR13
4593 ,UOM_EXT_ATTR14
4594 ,UOM_EXT_ATTR15
4595 ,UOM_EXT_ATTR16
4596 ,UOM_EXT_ATTR17
4597 ,UOM_EXT_ATTR18
4598 ,UOM_EXT_ATTR19
4599 ,UOM_EXT_ATTR20
4600 ,D_EXT_ATTR1
4601 ,D_EXT_ATTR2
4602 ,D_EXT_ATTR3
4603 ,D_EXT_ATTR4
4604 ,D_EXT_ATTR5
4605 ,D_EXT_ATTR6
4606 ,D_EXT_ATTR7
4607 ,D_EXT_ATTR8
4608 ,D_EXT_ATTR9
4609 ,D_EXT_ATTR10
4610 ,UDA_TEMPLATE_ID
4611 ,p_draft_id
4612 FROM PO_LINES_ALL_EXT_B_ARCHIVE
4613 WHERE extension_id = l_old_line_ext_id_tbl(i)
4614 AND revision_num = p_revision_num;
4615
4616 d_position := 90;
4617 IF PO_LOG.d_stmt THEN
4618 PO_LOG.stmt(d_mod,d_position,'Number of rows inserted: '||SQL%rowcount);
4619 END IF;
4620 -- End PO_LINES_ALL_EXT_B
4621
4622 -- Start PO_LINES_ALL_EXT_TL
4623 d_position := 100;
4624 IF PO_LOG.d_stmt THEN
4625 PO_LOG.stmt(d_mod,d_position,'Sync PO_LINES_ALL_EXT_TL');
4626 END IF;
4627
4628 FORALL i IN 1..l_old_line_ext_id_tbl.Count
4629 INSERT INTO PO_LINES_ALL_EXT_TL
4630 (EXTENSION_ID
4631 ,ATTR_GROUP_ID
4632 ,PO_LINE_ID
4633 ,DATA_LEVEL_ID
4634 ,PK1_VALUE
4635 ,PK2_VALUE
4636 ,PK3_VALUE
4637 ,PK4_VALUE
4638 ,PK5_VALUE
4639 ,SOURCE_LANG
4640 ,LANGUAGE
4641 ,LAST_UPDATE_DATE
4642 ,LAST_UPDATED_BY
4643 ,LAST_UPDATE_LOGIN
4644 ,CREATED_BY
4645 ,CREATION_DATE
4646 ,TL_EXT_ATTR1
4647 ,TL_EXT_ATTR2
4648 ,TL_EXT_ATTR3
4649 ,TL_EXT_ATTR4
4650 ,TL_EXT_ATTR5
4651 ,TL_EXT_ATTR6
4652 ,TL_EXT_ATTR7
4653 ,TL_EXT_ATTR8
4654 ,TL_EXT_ATTR9
4655 ,TL_EXT_ATTR10
4656 ,TL_EXT_ATTR11
4657 ,TL_EXT_ATTR12
4658 ,TL_EXT_ATTR13
4659 ,TL_EXT_ATTR14
4660 ,TL_EXT_ATTR15
4661 ,TL_EXT_ATTR16
4662 ,TL_EXT_ATTR17
4663 ,TL_EXT_ATTR18
4664 ,TL_EXT_ATTR19
4665 ,TL_EXT_ATTR20
4666 ,TL_EXT_ATTR21
4667 ,TL_EXT_ATTR22
4668 ,TL_EXT_ATTR23
4669 ,TL_EXT_ATTR24
4670 ,TL_EXT_ATTR25
4671 ,TL_EXT_ATTR26
4672 ,TL_EXT_ATTR27
4673 ,TL_EXT_ATTR28
4674 ,TL_EXT_ATTR29
4675 ,TL_EXT_ATTR30
4676 ,TL_EXT_ATTR31
4677 ,TL_EXT_ATTR32
4678 ,TL_EXT_ATTR33
4679 ,TL_EXT_ATTR34
4680 ,TL_EXT_ATTR35
4681 ,TL_EXT_ATTR36
4682 ,TL_EXT_ATTR37
4683 ,TL_EXT_ATTR38
4684 ,TL_EXT_ATTR39
4685 ,TL_EXT_ATTR40
4686 ,UDA_TEMPLATE_ID
4687 ,DRAFT_ID
4688 )
4689 SELECT
4690 l_new_line_ext_id_tbl(i)
4691 ,ATTR_GROUP_ID
4692 ,PO_LINE_ID
4693 ,DATA_LEVEL_ID
4694 ,PK1_VALUE
4695 ,PK2_VALUE
4696 ,PK3_VALUE
4697 ,PK4_VALUE
4698 ,PK5_VALUE
4699 ,SOURCE_LANG
4700 ,LANGUAGE
4701 ,LAST_UPDATE_DATE
4702 ,LAST_UPDATED_BY
4703 ,LAST_UPDATE_LOGIN
4704 ,CREATED_BY
4705 ,CREATION_DATE
4706 ,TL_EXT_ATTR1
4707 ,TL_EXT_ATTR2
4708 ,TL_EXT_ATTR3
4709 ,TL_EXT_ATTR4
4710 ,TL_EXT_ATTR5
4711 ,TL_EXT_ATTR6
4712 ,TL_EXT_ATTR7
4713 ,TL_EXT_ATTR8
4714 ,TL_EXT_ATTR9
4715 ,TL_EXT_ATTR10
4716 ,TL_EXT_ATTR11
4717 ,TL_EXT_ATTR12
4718 ,TL_EXT_ATTR13
4719 ,TL_EXT_ATTR14
4720 ,TL_EXT_ATTR15
4721 ,TL_EXT_ATTR16
4722 ,TL_EXT_ATTR17
4723 ,TL_EXT_ATTR18
4724 ,TL_EXT_ATTR19
4725 ,TL_EXT_ATTR20
4726 ,TL_EXT_ATTR21
4727 ,TL_EXT_ATTR22
4728 ,TL_EXT_ATTR23
4729 ,TL_EXT_ATTR24
4730 ,TL_EXT_ATTR25
4731 ,TL_EXT_ATTR26
4732 ,TL_EXT_ATTR27
4733 ,TL_EXT_ATTR28
4734 ,TL_EXT_ATTR29
4735 ,TL_EXT_ATTR30
4736 ,TL_EXT_ATTR31
4737 ,TL_EXT_ATTR32
4738 ,TL_EXT_ATTR33
4739 ,TL_EXT_ATTR34
4740 ,TL_EXT_ATTR35
4741 ,TL_EXT_ATTR36
4742 ,TL_EXT_ATTR37
4743 ,TL_EXT_ATTR38
4744 ,TL_EXT_ATTR39
4745 ,TL_EXT_ATTR40
4746 ,UDA_TEMPLATE_ID
4747 ,p_draft_id
4748 FROM PO_LINES_ALL_EXT_TL_ARCHIVE
4749 WHERE extension_id = l_old_line_ext_id_tbl(i)
4750 AND language = userenv('LANG')
4751 AND revision_num = p_revision_num;
4752
4753 d_position := 110;
4754 IF PO_LOG.d_stmt THEN
4755 PO_LOG.stmt(d_mod,d_position,'Number of rows inserted: '||SQL%rowcount);
4756 END IF;
4757 -- End PO_LINES_ALL_EXT_TL
4758
4759 OPEN po_line_loc_ext_cursor;
4760 FETCH po_line_loc_ext_cursor BULK COLLECT INTO l_old_line_loc_ext_id_tbl, l_new_line_loc_ext_id_tbl;
4761 CLOSE po_line_loc_ext_cursor;
4762
4763 -- Start PO_LINE_LOC_ALL_EXT_B
4764 d_position := 120;
4765 IF PO_LOG.d_stmt THEN
4766 PO_LOG.stmt(d_mod,d_position,'Sync PO_LINE_LOCATIONS_ALL_EXT_B');
4767 END IF;
4768
4769 FORALL i IN 1..l_old_line_loc_ext_id_tbl.Count
4770 INSERT INTO PO_LINE_LOCATIONS_ALL_EXT_B
4771 (EXTENSION_ID
4772 ,ATTR_GROUP_ID
4773 ,LINE_LOCATION_ID
4774 ,DATA_LEVEL_ID
4775 ,PK1_VALUE
4776 ,PK2_VALUE
4777 ,PK3_VALUE
4778 ,PK4_VALUE
4779 ,PK5_VALUE
4780 ,LAST_UPDATE_DATE
4781 ,LAST_UPDATED_BY
4782 ,LAST_UPDATE_LOGIN
4783 ,CREATED_BY
4784 ,CREATION_DATE
4785 ,C_EXT_ATTR1
4786 ,C_EXT_ATTR2
4787 ,C_EXT_ATTR3
4788 ,C_EXT_ATTR4
4789 ,C_EXT_ATTR5
4790 ,C_EXT_ATTR6
4791 ,C_EXT_ATTR7
4792 ,C_EXT_ATTR8
4793 ,C_EXT_ATTR9
4794 ,C_EXT_ATTR10
4795 ,C_EXT_ATTR11
4796 ,C_EXT_ATTR12
4797 ,C_EXT_ATTR13
4798 ,C_EXT_ATTR14
4799 ,C_EXT_ATTR15
4800 ,C_EXT_ATTR16
4801 ,C_EXT_ATTR17
4802 ,C_EXT_ATTR18
4803 ,C_EXT_ATTR19
4804 ,C_EXT_ATTR20
4805 ,C_EXT_ATTR21
4806 ,C_EXT_ATTR22
4807 ,C_EXT_ATTR23
4808 ,C_EXT_ATTR24
4809 ,C_EXT_ATTR25
4810 ,C_EXT_ATTR26
4811 ,C_EXT_ATTR27
4812 ,C_EXT_ATTR28
4813 ,C_EXT_ATTR29
4814 ,C_EXT_ATTR30
4815 ,C_EXT_ATTR31
4816 ,C_EXT_ATTR32
4817 ,C_EXT_ATTR33
4818 ,C_EXT_ATTR34
4819 ,C_EXT_ATTR35
4820 ,C_EXT_ATTR36
4821 ,C_EXT_ATTR37
4822 ,C_EXT_ATTR38
4823 ,C_EXT_ATTR39
4824 ,C_EXT_ATTR40
4825 ,N_EXT_ATTR1
4826 ,N_EXT_ATTR2
4827 ,N_EXT_ATTR3
4828 ,N_EXT_ATTR4
4829 ,N_EXT_ATTR5
4830 ,N_EXT_ATTR6
4831 ,N_EXT_ATTR7
4832 ,N_EXT_ATTR8
4833 ,N_EXT_ATTR9
4834 ,N_EXT_ATTR10
4835 ,N_EXT_ATTR11
4836 ,N_EXT_ATTR12
4837 ,N_EXT_ATTR13
4838 ,N_EXT_ATTR14
4839 ,N_EXT_ATTR15
4840 ,N_EXT_ATTR16
4841 ,N_EXT_ATTR17
4842 ,N_EXT_ATTR18
4843 ,N_EXT_ATTR19
4844 ,N_EXT_ATTR20
4845 ,UOM_EXT_ATTR1
4846 ,UOM_EXT_ATTR2
4847 ,UOM_EXT_ATTR3
4848 ,UOM_EXT_ATTR4
4849 ,UOM_EXT_ATTR5
4850 ,UOM_EXT_ATTR6
4851 ,UOM_EXT_ATTR7
4852 ,UOM_EXT_ATTR8
4853 ,UOM_EXT_ATTR9
4854 ,UOM_EXT_ATTR10
4855 ,UOM_EXT_ATTR11
4856 ,UOM_EXT_ATTR12
4857 ,UOM_EXT_ATTR13
4858 ,UOM_EXT_ATTR14
4859 ,UOM_EXT_ATTR15
4860 ,UOM_EXT_ATTR16
4861 ,UOM_EXT_ATTR17
4862 ,UOM_EXT_ATTR18
4863 ,UOM_EXT_ATTR19
4864 ,UOM_EXT_ATTR20
4865 ,D_EXT_ATTR1
4866 ,D_EXT_ATTR2
4867 ,D_EXT_ATTR3
4868 ,D_EXT_ATTR4
4869 ,D_EXT_ATTR5
4870 ,D_EXT_ATTR6
4871 ,D_EXT_ATTR7
4872 ,D_EXT_ATTR8
4873 ,D_EXT_ATTR9
4874 ,D_EXT_ATTR10
4875 ,UDA_TEMPLATE_ID
4876 ,DRAFT_ID
4877 )
4878 SELECT
4879 l_new_line_loc_ext_id_tbl(i)
4880 ,ATTR_GROUP_ID
4881 ,LINE_LOCATION_ID
4882 ,DATA_LEVEL_ID
4883 ,PK1_VALUE
4884 ,PK2_VALUE
4885 ,PK3_VALUE
4886 ,PK4_VALUE
4887 ,PK5_VALUE
4888 ,LAST_UPDATE_DATE
4889 ,LAST_UPDATED_BY
4890 ,LAST_UPDATE_LOGIN
4891 ,CREATED_BY
4892 ,CREATION_DATE
4893 ,C_EXT_ATTR1
4894 ,C_EXT_ATTR2
4895 ,C_EXT_ATTR3
4896 ,C_EXT_ATTR4
4897 ,C_EXT_ATTR5
4898 ,C_EXT_ATTR6
4899 ,C_EXT_ATTR7
4900 ,C_EXT_ATTR8
4901 ,C_EXT_ATTR9
4902 ,C_EXT_ATTR10
4903 ,C_EXT_ATTR11
4904 ,C_EXT_ATTR12
4905 ,C_EXT_ATTR13
4906 ,C_EXT_ATTR14
4907 ,C_EXT_ATTR15
4908 ,C_EXT_ATTR16
4909 ,C_EXT_ATTR17
4910 ,C_EXT_ATTR18
4911 ,C_EXT_ATTR19
4912 ,C_EXT_ATTR20
4913 ,C_EXT_ATTR21
4914 ,C_EXT_ATTR22
4915 ,C_EXT_ATTR23
4916 ,C_EXT_ATTR24
4917 ,C_EXT_ATTR25
4918 ,C_EXT_ATTR26
4919 ,C_EXT_ATTR27
4920 ,C_EXT_ATTR28
4921 ,C_EXT_ATTR29
4922 ,C_EXT_ATTR30
4923 ,C_EXT_ATTR31
4924 ,C_EXT_ATTR32
4925 ,C_EXT_ATTR33
4926 ,C_EXT_ATTR34
4927 ,C_EXT_ATTR35
4928 ,C_EXT_ATTR36
4929 ,C_EXT_ATTR37
4930 ,C_EXT_ATTR38
4931 ,C_EXT_ATTR39
4932 ,C_EXT_ATTR40
4933 ,N_EXT_ATTR1
4934 ,N_EXT_ATTR2
4935 ,N_EXT_ATTR3
4936 ,N_EXT_ATTR4
4937 ,N_EXT_ATTR5
4938 ,N_EXT_ATTR6
4939 ,N_EXT_ATTR7
4940 ,N_EXT_ATTR8
4941 ,N_EXT_ATTR9
4942 ,N_EXT_ATTR10
4943 ,N_EXT_ATTR11
4944 ,N_EXT_ATTR12
4945 ,N_EXT_ATTR13
4946 ,N_EXT_ATTR14
4947 ,N_EXT_ATTR15
4948 ,N_EXT_ATTR16
4949 ,N_EXT_ATTR17
4950 ,N_EXT_ATTR18
4951 ,N_EXT_ATTR19
4952 ,N_EXT_ATTR20
4953 ,UOM_EXT_ATTR1
4954 ,UOM_EXT_ATTR2
4955 ,UOM_EXT_ATTR3
4956 ,UOM_EXT_ATTR4
4957 ,UOM_EXT_ATTR5
4958 ,UOM_EXT_ATTR6
4959 ,UOM_EXT_ATTR7
4960 ,UOM_EXT_ATTR8
4961 ,UOM_EXT_ATTR9
4962 ,UOM_EXT_ATTR10
4963 ,UOM_EXT_ATTR11
4964 ,UOM_EXT_ATTR12
4965 ,UOM_EXT_ATTR13
4966 ,UOM_EXT_ATTR14
4967 ,UOM_EXT_ATTR15
4968 ,UOM_EXT_ATTR16
4969 ,UOM_EXT_ATTR17
4970 ,UOM_EXT_ATTR18
4971 ,UOM_EXT_ATTR19
4972 ,UOM_EXT_ATTR20
4973 ,D_EXT_ATTR1
4974 ,D_EXT_ATTR2
4975 ,D_EXT_ATTR3
4976 ,D_EXT_ATTR4
4977 ,D_EXT_ATTR5
4978 ,D_EXT_ATTR6
4979 ,D_EXT_ATTR7
4980 ,D_EXT_ATTR8
4981 ,D_EXT_ATTR9
4982 ,D_EXT_ATTR10
4983 ,UDA_TEMPLATE_ID
4984 ,p_draft_id
4985 FROM PO_LINE_LOC_ALL_EXT_B_ARCHIVE
4986 WHERE extension_id = l_old_line_loc_ext_id_tbl(i)
4987 AND revision_num = p_revision_num;
4988
4989 d_position := 130;
4990 IF PO_LOG.d_stmt THEN
4991 PO_LOG.stmt(d_mod,d_position,'Number of rows inserted: '||SQL%rowcount);
4992 END IF;
4993 -- End PO_LINE_LOC_ALL_EXT_B
4994
4995 -- Start PO_LINE_LOC_ALL_EXT_TL
4996 d_position := 140;
4997 IF PO_LOG.d_stmt THEN
4998 PO_LOG.stmt(d_mod,d_position,'Sync PO_LINE_LOCATIONS_ALL_EXT_TL');
4999 END IF;
5000
5001 FORALL i IN 1..l_old_line_loc_ext_id_tbl.Count
5002 INSERT INTO PO_LINE_LOCATIONS_ALL_EXT_TL
5003 (EXTENSION_ID
5004 ,ATTR_GROUP_ID
5005 ,LINE_LOCATION_ID
5006 ,DATA_LEVEL_ID
5007 ,PK1_VALUE
5008 ,PK2_VALUE
5009 ,PK3_VALUE
5010 ,PK4_VALUE
5011 ,PK5_VALUE
5012 ,SOURCE_LANG
5013 ,LANGUAGE
5014 ,LAST_UPDATE_DATE
5015 ,LAST_UPDATED_BY
5016 ,LAST_UPDATE_LOGIN
5017 ,CREATED_BY
5018 ,CREATION_DATE
5019 ,TL_EXT_ATTR1
5020 ,TL_EXT_ATTR2
5021 ,TL_EXT_ATTR3
5022 ,TL_EXT_ATTR4
5023 ,TL_EXT_ATTR5
5024 ,TL_EXT_ATTR6
5025 ,TL_EXT_ATTR7
5026 ,TL_EXT_ATTR8
5027 ,TL_EXT_ATTR9
5028 ,TL_EXT_ATTR10
5029 ,TL_EXT_ATTR11
5030 ,TL_EXT_ATTR12
5031 ,TL_EXT_ATTR13
5032 ,TL_EXT_ATTR14
5033 ,TL_EXT_ATTR15
5034 ,TL_EXT_ATTR16
5035 ,TL_EXT_ATTR17
5036 ,TL_EXT_ATTR18
5037 ,TL_EXT_ATTR19
5038 ,TL_EXT_ATTR20
5039 ,TL_EXT_ATTR21
5040 ,TL_EXT_ATTR22
5041 ,TL_EXT_ATTR23
5042 ,TL_EXT_ATTR24
5043 ,TL_EXT_ATTR25
5044 ,TL_EXT_ATTR26
5045 ,TL_EXT_ATTR27
5046 ,TL_EXT_ATTR28
5047 ,TL_EXT_ATTR29
5048 ,TL_EXT_ATTR30
5049 ,TL_EXT_ATTR31
5050 ,TL_EXT_ATTR32
5051 ,TL_EXT_ATTR33
5052 ,TL_EXT_ATTR34
5053 ,TL_EXT_ATTR35
5054 ,TL_EXT_ATTR36
5055 ,TL_EXT_ATTR37
5056 ,TL_EXT_ATTR38
5057 ,TL_EXT_ATTR39
5058 ,TL_EXT_ATTR40
5059 ,UDA_TEMPLATE_ID
5060 ,DRAFT_ID
5061 )
5062 SELECT
5063 l_new_line_loc_ext_id_tbl(i)
5064 ,ATTR_GROUP_ID
5065 ,LINE_LOCATION_ID
5066 ,DATA_LEVEL_ID
5067 ,PK1_VALUE
5068 ,PK2_VALUE
5069 ,PK3_VALUE
5070 ,PK4_VALUE
5071 ,PK5_VALUE
5072 ,SOURCE_LANG
5073 ,LANGUAGE
5074 ,LAST_UPDATE_DATE
5075 ,LAST_UPDATED_BY
5076 ,LAST_UPDATE_LOGIN
5077 ,CREATED_BY
5078 ,CREATION_DATE
5079 ,TL_EXT_ATTR1
5080 ,TL_EXT_ATTR2
5081 ,TL_EXT_ATTR3
5082 ,TL_EXT_ATTR4
5083 ,TL_EXT_ATTR5
5084 ,TL_EXT_ATTR6
5085 ,TL_EXT_ATTR7
5086 ,TL_EXT_ATTR8
5087 ,TL_EXT_ATTR9
5088 ,TL_EXT_ATTR10
5089 ,TL_EXT_ATTR11
5090 ,TL_EXT_ATTR12
5091 ,TL_EXT_ATTR13
5092 ,TL_EXT_ATTR14
5093 ,TL_EXT_ATTR15
5094 ,TL_EXT_ATTR16
5095 ,TL_EXT_ATTR17
5096 ,TL_EXT_ATTR18
5097 ,TL_EXT_ATTR19
5098 ,TL_EXT_ATTR20
5099 ,TL_EXT_ATTR21
5100 ,TL_EXT_ATTR22
5101 ,TL_EXT_ATTR23
5102 ,TL_EXT_ATTR24
5103 ,TL_EXT_ATTR25
5104 ,TL_EXT_ATTR26
5105 ,TL_EXT_ATTR27
5106 ,TL_EXT_ATTR28
5107 ,TL_EXT_ATTR29
5108 ,TL_EXT_ATTR30
5109 ,TL_EXT_ATTR31
5110 ,TL_EXT_ATTR32
5111 ,TL_EXT_ATTR33
5112 ,TL_EXT_ATTR34
5113 ,TL_EXT_ATTR35
5114 ,TL_EXT_ATTR36
5115 ,TL_EXT_ATTR37
5116 ,TL_EXT_ATTR38
5117 ,TL_EXT_ATTR39
5118 ,TL_EXT_ATTR40
5119 ,UDA_TEMPLATE_ID
5120 ,p_draft_id
5121 FROM PO_LINE_LOC_ALL_EXT_TL_ARCHIVE
5122 WHERE extension_id = l_old_line_loc_ext_id_tbl(i)
5123 AND language = userenv('LANG')
5124 AND revision_num = p_revision_num;
5125
5126 d_position := 150;
5127 IF PO_LOG.d_stmt THEN
5128 PO_LOG.stmt(d_mod,d_position,'Number of rows inserted: '||SQL%rowcount);
5129 END IF;
5130 -- End PO_LINE_LOC_ALL_EXT_TL
5131
5132 END IF; --IF p_document_subtype='BLANKET'
5133
5134 END IF; --IF l_enabled_flag = 'Y' AND p_document_type IN ('PO', 'PA')
5135
5136 d_position := 160;
5137
5138 IF (PO_LOG.d_proc) THEN
5139 PO_LOG.proc_end(d_mod);
5140 END IF;
5141
5142 EXCEPTION
5143 WHEN OTHERS THEN
5144 PO_MESSAGE_S.add_exc_msg
5145 ( p_pkg_name => d_pkg_name,
5146 p_procedure_name => d_api_name || '.' || d_position
5147 );
5148
5149 IF (po_headers_ext_cursor%ISOPEN) THEN
5150 CLOSE po_headers_ext_cursor;
5151 END IF;
5152 IF (po_lines_ext_cursor%ISOPEN) THEN
5153 CLOSE po_lines_ext_cursor;
5154 END IF;
5155 IF (po_line_loc_ext_cursor%ISOPEN) THEN
5156 CLOSE po_line_loc_ext_cursor;
5157 END IF;
5158
5159 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5160 END SYNC_UDA_ATTRIBS;
5161
5162 -------------------------------------------------------------------------------
5163 --UCA Project - CLM R4 Changes Begin
5164 -----------------------------------------------------------------------
5165 --Start of Comments
5166 --Name: SYNC_LINE_UCAS
5167 --Pre-reqs: None
5168 --Modifies:
5169 --Po_line_ucas
5170 --Locks:
5171 -- None.
5172 --Function:
5173 -- Syncs Line UCas
5174 --Parameters:
5175 --IN:
5176 --p_document_id
5177 -- The PO_HEADER_ID of the document that needs to be synced
5178 --p_draft_id
5179 -- The draft_id using which the record has to be inserted at the time of sync
5180 --Testing:
5181 -- None.
5182 --End of Comments
5183
5184 ------------------------------------------------------------------------
5185 PROCEDURE SYNC_LINE_UCAS(p_document_id IN NUMBER,
5186 p_draft_id IN NUMBER)
5187 IS
5188 d_api_name CONSTANT VARCHAR2(30) := 'SYNC_LINE_UCAS';
5189 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
5190 d_position NUMBER;
5191
5192 BEGIN
5193 d_position := 10;
5194 INSERT INTO po_line_ucas
5195 (po_line_uca_id,
5196 po_header_id,
5197 po_line_id,
5198 undef_draft_id,
5199 undef_amount,
5200 not_to_exceed_amount,
5201 planned_def_date,
5202 undef_description,
5203 undef_approved_date,
5204 def_draft_id,
5205 def_description,
5206 def_approved_date,
5207 last_update_date,
5208 last_updated_by,
5209 creation_date,
5210 created_by,
5211 last_update_login,
5212 undef_clm_document_number,
5213 limit_gov_liability_amt,
5214 def_par_draft_id)
5215 SELECT po_line_ucas_s.NEXTVAL,
5216 po_header_id,
5217 po_line_id,
5218 p_draft_id,
5219 undef_amount,
5220 not_to_exceed_amount,
5221 planned_def_date,
5222 undef_description,
5223 undef_approved_date,
5224 def_draft_id,
5225 def_description,
5226 def_approved_date,
5227 last_update_date,
5228 last_updated_by,
5229 creation_date,
5230 created_by,
5231 last_update_login,
5232 undef_clm_document_number,
5233 limit_gov_liability_amt,
5234 def_par_draft_id
5235 FROM po_line_ucas
5236 WHERE po_header_id = p_document_id
5237 AND undef_draft_id = -1;
5238 d_position :=20;
5239 IF (PO_LOG.d_stmt) THEN
5240 PO_LOG.stmt(d_module, d_position, 'transfer count = ' || SQL%ROWCOUNT);
5241 END IF;
5242
5243 d_position := 30;
5244
5245 IF (PO_LOG.d_proc) THEN
5246 PO_LOG.proc_end(d_module);
5247 END IF;
5248
5249 EXCEPTION
5250 WHEN OTHERS THEN
5251 PO_MESSAGE_S.add_exc_msg
5252 ( p_pkg_name => d_pkg_name,
5253 p_procedure_name => d_api_name || '.' || d_position
5254 );
5255 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5256
5257 END SYNC_LINE_UCAS;
5258 --UCA Project - CLM R4 Changes End
5259
5260
5261 -------------------------------------------------------------------------------
5262 --CLM Phase 4 - Elins project
5263 -----------------------------------------------------------------------
5264 --Start of Comments
5265 --Name: SYNC_EXHIBITS
5266 --Pre-reqs: None
5267 --Modifies:
5268 --Po_exhibit_details_draft
5269 --Locks:
5270 -- None.
5271 --Function:
5272 -- Syncs exhibits
5273 --Parameters:
5274 --IN:
5275 --p_document_id
5276 -- The PO_HEADER_ID of the document that needs to be synced
5277 --p_draft_id
5278 -- The draft_id using which the record has to be inserted at the time of sync
5279 --Testing:
5280 -- None.
5281 --End of Comments
5282
5283 ------------------------------------------------------------------------
5284 PROCEDURE SYNC_EXHIBITS(p_document_id IN NUMBER,
5285 p_revision_num IN NUMBER,
5286 p_draft_id IN NUMBER)
5287 IS
5288 d_api_name CONSTANT VARCHAR2(30) := 'SYNC_EXHIBITS';
5289 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
5290 d_position NUMBER;
5291
5292 BEGIN
5293
5294 d_position := 0;
5295
5296 IF (PO_LOG.d_proc) THEN
5297 PO_LOG.proc_begin(d_module,'p_po_header_id',p_document_id);
5298 END IF;
5299
5300 d_position := 10;
5301 IF (PO_LOG.d_stmt) THEN
5302 PO_LOG.stmt(d_module, d_position, 'transfer records from archive to dft');
5303 END IF;
5304
5305 INSERT INTO po_exhibit_details_draft
5306 (
5307 po_exhibit_details_id,
5308 po_header_id,
5309 draft_id,
5310 delete_flag,
5311 change_accepted_flag,
5312 exhibit_name,
5313 exhibit_description,
5314 is_cdrl,
5315 reference_line_id,
5316 change_status, --16698658
5317 LAST_UPDATE_DATE,
5318 LAST_UPDATED_BY,
5319 CREATION_DATE,
5320 CREATED_BY,
5321 LAST_UPDATE_LOGIN
5322 )
5323 SELECT
5324 po_exhibit_details_id,
5325 po_header_id,
5326 p_draft_id,
5327 NULL,
5328 NULL,
5329 exhibit_name,
5330 exhibit_description,
5331 is_cdrl,
5332 reference_line_id,
5333 'NOCHANGE', --16698658
5334 LAST_UPDATE_DATE,
5335 LAST_UPDATED_BY,
5336 CREATION_DATE,
5337 CREATED_BY,
5338 LAST_UPDATE_LOGIN
5339 FROM po_exhibit_details_archive
5340 WHERE po_header_id = p_document_id
5341 AND revision_num = p_revision_num;
5342
5343
5344 d_position :=20;
5345 IF (PO_LOG.d_stmt) THEN
5346 PO_LOG.stmt(d_module, d_position, 'transfer count = ' || SQL%ROWCOUNT);
5347 END IF;
5348
5349 d_position := 30;
5350
5351 IF (PO_LOG.d_proc) THEN
5352 PO_LOG.proc_end(d_module);
5353 END IF;
5354
5355 EXCEPTION
5356 WHEN OTHERS THEN
5357 PO_MESSAGE_S.add_exc_msg
5358 ( p_pkg_name => d_pkg_name,
5359 p_procedure_name => d_api_name || '.' || d_position
5360 );
5361 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5362
5363 END SYNC_EXHIBITS;
5364
5365
5366 -------------------------------------------------------------------------------
5367 --Start of Comments
5368 --Name: SYNC_DRAFT_FROM_ARCHIVE
5369 --Pre-reqs:
5370 -- None.
5371 --Modifies:
5372 -- None.
5373 --Locks:
5374 -- None.
5375 --Function:
5376 -- Copy data from Archive table to draft table, if the corresponding
5377 -- record in draft table does not exist.
5378 -- This is done to handle the View Base Document.
5379 --Parameters:
5380 --IN:
5381 --p_document_id
5382 -- The id of the document that needs to be handled for View Base Document.
5383 --p_document_type
5384 -- The type of the document to sync
5385 -- PO : For Standard/Planned
5386 -- PA : For Blanket/Contract
5387 --p_document_subtype
5388 -- The subtype of the document.
5389 -- Valid Document types and Document subtypes are
5390 -- Document Type Document Subtype
5391 -- PO ---> STANDARD
5392 -- PA ---> CONTRACT/BLANKET
5393 --p_revision_num
5394 -- The version of the document that is being handled for View Base Document
5395 -- In the current scope of the project, this is always 0, corr. to the base doc
5396 --p_draft_id
5397 -- The draft_id using which the record has to be inserted at the time of sync
5398 -- In the current scope of the project, this is always 0, corr. to the base doc
5399 --OUT:
5400 --x_return_status
5401 -- FND_API.G_RET_STS_SUCCESS if API succeeds
5402 -- FND_API.G_RET_STS_ERROR if API fails
5403 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
5404 --x_msg_count
5405 -- returns count of messages in the stack.
5406 --x_msg_data
5407 -- Contains error msg in case x_return_status returned
5408 -- FND_API.G_RET_STS_ERROR or FND_API.G_RET_STS_UNEXP_ERROR
5409 --Testing:
5410 -- None.
5411 --End of Comments
5412 -------------------------------------------------------------------------------
5413 PROCEDURE SYNC_DRAFT_FROM_ARCHIVE(p_document_id IN NUMBER,
5414 p_document_type IN VARCHAR2,
5415 p_document_subtype IN VARCHAR2,
5416 p_revision_num IN NUMBER DEFAULT 0,
5417 p_draft_id IN NUMBER DEFAULT 0,
5418 x_return_status OUT NOCOPY VARCHAR2)
5419 IS
5420 d_api_name CONSTANT VARCHAR2(30) := 'SYNC_DRAFT_FROM_ARCHIVE';
5421 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
5422 d_position NUMBER;
5423
5424 l_ga_flag PO_HEADERS_ALL.global_agreement_flag%TYPE;
5425 l_return_status VARCHAR2(1);
5426 l_isUcaEnabled Varchar2(1); -- UCA Project Changes.
5427
5428 BEGIN
5429
5430 d_position := 0;
5431 IF (PO_LOG.d_proc) THEN
5432 PO_LOG.proc_begin(d_module,'p_po_header_id',p_document_id);
5433 PO_LOG.proc_begin(d_module,'p_document_type',p_document_type);
5434 PO_LOG.proc_begin(d_module,'p_document_subtype',p_document_subtype);
5435 END IF;
5436
5437 --Standard Start API savepoint
5438 SAVEPOINT PO_SYNC_DRAFT_FROM_ARCHIVE_SP;
5439
5440 -- Check is the Archive->Dft syncing has already been done or not.
5441 -- Proceed only when not done
5442 check_po_sync ( p_document_id, p_revision_num, p_draft_id, l_return_status );
5443
5444 d_position := 10;
5445
5446 IF (PO_LOG.d_stmt) THEN
5447 PO_LOG.stmt(d_module, d_position, 'check_po_sync returned'||l_return_status);
5448 END IF;
5449
5450 IF (l_return_status = 'N') THEN
5451
5452 d_position := 20;
5453 IF (PO_LOG.d_stmt) THEN
5454 PO_LOG.stmt(d_module, d_position, 'Document has not been synced yet');
5455 END IF;
5456
5457 IF (p_document_type = 'PO') THEN
5458
5459 --View Base Document action is being handled for a new document in this transaction
5460 --Delete All Draft records for DRAFT_ID=0 if they exist
5461 d_position := 25;
5462 IF (PO_LOG.d_proc) THEN
5463 PO_LOG.proc_begin(d_module,'Delete all entries from Draft Tables having draft_id: ',p_draft_id);
5464 END IF;
5465
5466 delete_dummy_data_from_draft (p_draft_id);
5467
5468 d_position := 30;
5469 IF (PO_LOG.d_stmt) THEN
5470 PO_LOG.stmt(d_module, d_position, 'sync_draft_from_archive for PO');
5471 END IF;
5472
5473 d_position := 40;
5474 IF (PO_LOG.d_stmt) THEN
5475 PO_LOG.stmt(d_module, d_position, 'Calling CREATE_DUMMY_ROW_IN_DRAFT');
5476 END IF;
5477
5478 create_dummy_row_in_draft (p_document_id, p_revision_num, p_draft_id);
5479
5480 d_position := 50;
5481 IF (PO_LOG.d_stmt) THEN
5482 PO_LOG.stmt(d_module, d_position, 'Calling SYNC_HEADER');
5483 END IF;
5484
5485 sync_header (p_document_id, p_revision_num, p_draft_id);
5486
5487 d_position := 60;
5488 IF (PO_LOG.d_stmt) THEN
5489 PO_LOG.stmt(d_module, d_position, 'Calling SYNC_LINES');
5490 END IF;
5491
5492 sync_lines (p_document_id, p_revision_num, p_draft_id);
5493
5494 d_position := 70;
5495 IF (PO_LOG.d_stmt) THEN
5496 PO_LOG.stmt(d_module, d_position, 'Calling SYNC_PRICE_ADJUSTMENTS');
5497 END IF;
5498
5499 sync_price_adjustments (p_document_id, p_revision_num, p_draft_id);
5500
5501 d_position := 80;
5502 IF (PO_LOG.d_stmt) THEN
5503 PO_LOG.stmt(d_module, d_position, 'Calling SYNC_PRICE_ADJ_ATTRIBS');
5504 END IF;
5505
5506 sync_price_adj_attribs (p_document_id, p_revision_num, p_draft_id);
5507
5508 d_position := 90;
5509 IF (PO_LOG.d_stmt) THEN
5510 PO_LOG.stmt(d_module, d_position, 'Calling SYNC_PRICE_DIFFS with entity type PO LINE');
5511 END IF;
5512
5513 sync_price_diffs (p_document_id => p_document_id,
5514 p_revision_num => p_revision_num,
5515 p_draft_id => p_draft_id,
5516 p_entity_type => 'PO LINE');
5517
5518 d_position := 100;
5519 IF (PO_LOG.d_stmt) THEN
5520 PO_LOG.stmt(d_module, d_position, 'Calling SYNC_LINE_LOCATIONS');
5521 END IF;
5522
5523 sync_line_locations (p_document_id, p_revision_num, p_draft_id);
5524
5525 d_position := 110;
5526 IF (PO_LOG.d_stmt) THEN
5527 PO_LOG.stmt(d_module, d_position, 'Calling SYNC_DISTRIBUTIONS');
5528 END IF;
5529
5530 sync_distributions (p_document_id, p_revision_num, p_draft_id);
5531
5532 d_position := 112;
5533 IF (PO_LOG.d_stmt) THEN
5534 PO_LOG.stmt(d_module, d_position, 'Calling SYNC_EXHIBITS');
5535 END IF;
5536
5537 sync_exhibits (p_document_id, p_revision_num, p_draft_id);
5538
5539 --Sync UDA Data for UDA Enabled Documents
5540 d_position := 115;
5541 IF (PO_LOG.d_stmt) THEN
5542 PO_LOG.stmt(d_module, d_position, 'Calling SYNC_UDA_ATTRIBS');
5543 END IF;
5544
5545 sync_uda_attribs(p_document_id, p_document_type,
5546 p_document_subtype, p_revision_num, p_draft_id);
5547
5548 -- Sync Contract Terms for PO
5549
5550 d_position := 116;
5551 --UCA project - CLM R4 Changes Begin.
5552 SELECT Nvl(po_core_s.Retrieveoptionvalue(org_id,
5553 po_core_s.g_undef_cont_act_col), 'N')
5554 INTO l_isucaenabled
5555 FROM po_headers_archive_all
5556 WHERE po_header_id = p_document_id
5557 and revision_num = p_revision_num;
5558
5559
5560 IF (l_isUcaEnabled = 'Y' ) THEN
5561 SYNC_LINE_UCAS(p_document_id,
5562 p_draft_id);
5563
5564 END IF;
5565 --UCA project - CLM R4 Changes End.
5566
5567 ELSIF (p_document_type = 'PA') THEN --IF (p_document_type = 'PO')
5568
5569 --View Base Document action is being handled for a new document in this transaction
5570 --Delete All Draft records for DRAFT_ID=0 if they exist
5571 d_position := 117;
5572 IF (PO_LOG.d_proc) THEN
5573 PO_LOG.proc_begin(d_module,'Delete all entries from Draft Tables having draft_id: ',p_draft_id);
5574 END IF;
5575
5576 delete_dummy_data_from_draft (p_draft_id);
5577
5578 d_position := 118;
5579 IF (PO_LOG.d_stmt) THEN
5580 PO_LOG.stmt(d_module, d_position, 'Calling CREATE_DUMMY_ROW_IN_DRAFT');
5581 END IF;
5582
5583 create_dummy_row_in_draft (p_document_id, p_revision_num, p_draft_id);
5584
5585 d_position := 120;
5586 IF (PO_LOG.d_stmt) THEN
5587 PO_LOG.stmt(d_module, d_position, 'Calling SYNC_HEADER');
5588 END IF;
5589
5590 sync_header (p_document_id, p_revision_num, p_draft_id);
5591
5592 IF (p_document_subtype = 'BLANKET') THEN
5593
5594 d_position := 130;
5595 IF (PO_LOG.d_stmt) THEN
5596 PO_LOG.stmt(d_module, d_position, 'Calling SYNC_LINES');
5597 END IF;
5598
5599 sync_lines (p_document_id, p_revision_num, p_draft_id);
5600
5601 d_position := 140;
5602 IF (PO_LOG.d_stmt) THEN
5603 PO_LOG.stmt(d_module, d_position, 'Calling SYNC_PRICE_ADJUSTMENTS');
5604 END IF;
5605
5606 sync_price_adjustments (p_document_id, p_revision_num, p_draft_id);
5607
5608 d_position := 150;
5609 IF (PO_LOG.d_stmt) THEN
5610 PO_LOG.stmt(d_module, d_position, 'Calling SYNC_PRICE_ADJ_ATTRIBS');
5611 END IF;
5612
5613 sync_price_adj_attribs (p_document_id, p_revision_num, p_draft_id);
5614
5615 d_position := 160;
5616 IF (PO_LOG.d_stmt) THEN
5617 PO_LOG.stmt(d_module, d_position, 'Calling SYNC_ATTRIBUTE_VALUES');
5618 END IF;
5619
5620 sync_attribute_values (p_document_id, p_revision_num, p_draft_id);
5621
5622 d_position := 170;
5623 IF (PO_LOG.d_stmt) THEN
5624 PO_LOG.stmt(d_module, d_position, 'Calling SYNC_ATTR_VALUES_TLP');
5625 END IF;
5626
5627 sync_attr_values_tlp (p_document_id, p_revision_num, p_draft_id);
5628
5629 d_position := 180;
5630 IF (PO_LOG.d_stmt) THEN
5631 PO_LOG.stmt(d_module, d_position, 'Calling SYNC_PRICE_DIFFS with entity_type BLANKET LINE');
5632 END IF;
5633
5634 sync_price_diffs (p_document_id => p_document_id,
5635 p_revision_num => p_revision_num,
5636 p_draft_id => p_draft_id,
5637 p_entity_type => 'BLANKET LINE');
5638
5639 d_position := 190;
5640 IF (PO_LOG.d_stmt) THEN
5641 PO_LOG.stmt(d_module, d_position, 'Calling SYNC_LINE_LOCATIONS');
5642 END IF;
5643
5644 sync_line_locations (p_document_id, p_revision_num, p_draft_id);
5645
5646 d_position := 200;
5647 IF (PO_LOG.d_stmt) THEN
5648 PO_LOG.stmt(d_module, d_position, 'Calling SYNC_PRICE_DIFFS with entity_type PRICE BREAK');
5649 END IF;
5650
5651 sync_price_diffs (p_document_id => p_document_id,
5652 p_revision_num => p_revision_num,
5653 p_draft_id => p_draft_id,
5654 p_entity_type => 'PRICE BREAK');
5655
5656 d_position := 210;
5657 IF (PO_LOG.d_stmt) THEN
5658 PO_LOG.stmt(d_module, d_position, 'Calling SYNC_DISTRIBUTIONS');
5659 END IF;
5660
5661 sync_distributions (p_document_id, p_revision_num, p_draft_id);
5662
5663 END IF; --IF (p_document_subtype = 'BLANKET')
5664
5665 d_position := 212;
5666 IF (PO_LOG.d_stmt) THEN
5667 PO_LOG.stmt(d_module, d_position, 'Calling SYNC_EXHIBITS');
5668 END IF;
5669
5670 sync_exhibits (p_document_id, p_revision_num, p_draft_id);
5671
5672 --Sync org assignments for global contracts as well as global blankets.
5673 d_position := 220;
5674 IF (PO_LOG.d_stmt) THEN
5675 PO_LOG.stmt(d_module, d_position, 'Select global agreement flag - po_header_id');
5676 END IF;
5677
5678 -- SQL What:Find out if it is a global agreement
5679 -- SQL Why :Sync org_assignment draft table if needed
5680 SELECT NVL(global_agreement_flag, 'N')
5681 INTO l_ga_flag
5682 FROM po_headers_all
5683 WHERE po_header_id = p_document_id;
5684
5685 IF (l_ga_flag = 'Y') THEN
5686
5687 d_position := 230;
5688 IF (PO_LOG.d_stmt) THEN
5689 PO_LOG.stmt(d_module, d_position, 'Calling SYNC_ORG_ASSIGNMENTS');
5690 END IF;
5691
5692 sync_org_assignments (p_document_id, p_revision_num, p_draft_id);
5693
5694 END IF; --IF (l_ga_flag = 'Y')
5695
5696 d_position := 240;
5697 IF (PO_LOG.d_stmt) THEN
5698 PO_LOG.stmt(d_module, d_position, 'Calling SYNC_NOTIFICATION_CONTROLS');
5699 END IF;
5700
5701 sync_notification_controls (p_document_id, p_revision_num, p_draft_id);
5702
5703 --Sync UDA Data for UDA Enabled Documents
5704 d_position := 250;
5705 IF (PO_LOG.d_stmt) THEN
5706 PO_LOG.stmt(d_module, d_position, 'Calling SYNC_UDA_ATTRIBS');
5707 END IF;
5708
5709 sync_uda_attribs(p_document_id, p_document_type,
5710 p_document_subtype, p_revision_num, p_draft_id);
5711
5712 --Sync contract terms for PA
5713
5714 END IF; --ELSE IF (p_document_type = 'PA')
5715
5716 END IF; --IF (l_return_status = 'Y')
5717
5718 x_return_status := FND_API.G_RET_STS_SUCCESS;
5719
5720 EXCEPTION
5721 WHEN OTHERS THEN
5722 ROLLBACK TO PO_SYNC_DRAFT_FROM_ARCHIVE_SP;
5723
5724 PO_MESSAGE_S.add_exc_msg
5725 ( p_pkg_name => d_pkg_name,
5726 p_procedure_name => d_api_name || '.' || d_position
5727 );
5728 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5729
5730 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5731
5732 END SYNC_DRAFT_FROM_ARCHIVE;
5733
5734
5735 END PO_SYNC_DRAFT_FROM_ARCHIVE_PVT;