[Home] [Help]
PACKAGE BODY: APPS.PO_HEADERS_DRAFT_PKG
Source
1 PACKAGE BODY po_headers_draft_pkg AS
2 /* $Header: PO_HEADERS_DRAFT_PKG.plb 120.21.12020000.3 2013/02/10 22:23:01 vegajula ship $ */
3
4 d_pkg_name CONSTANT varchar2(50) :=
5 PO_LOG.get_package_base('PO_HEADERS_DRAFT_PKG');
6
7 -----------------------------------------------------------------------
8 --Start of Comments
9 --Name: delete_rows
10 --Pre-reqs: None
11 --Modifies:
12 --Locks:
13 -- None
14 --Function:
15 -- Deletes drafts for headers based on the information given
16 -- If only draft_id is provided, then all headers for the draft will be
17 -- deleted
18 -- If po_header_id is also provided, then the one record that has such
19 -- primary key will be deleted
20 --Parameters:
21 --IN:
22 --p_draft_id
23 -- draft unique identifier
24 --p_po_header_id
25 -- po header unique identifier
26 --IN OUT:
27 --OUT:
28 --Returns:
29 --Notes:
30 --Testing:
31 --End of Comments
32 ------------------------------------------------------------------------
33 PROCEDURE delete_rows
34 ( p_draft_id IN NUMBER,
35 p_po_header_id IN NUMBER
36 ) IS
37 d_api_name CONSTANT VARCHAR2(30) := 'delete_rows';
38 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
39 d_position NUMBER;
40 l_header_id NUMBER;
41
42 BEGIN
43 d_position := 0;
44 IF (PO_LOG.d_proc) THEN
45 PO_LOG.proc_begin(d_module);
46 END IF;
47
48 --Bug 13938456 - Cond Mod Attachments
49 --Fetch header id if it is null
50 IF p_po_header_id IS NULL THEN
51
52 SELECT document_id
53 INTO l_header_id
54 FROM po_drafts
55 WHERE draft_id = p_draft_id;
56
57 ELSE
58
59 l_header_id := p_po_header_id;
60
61 END IF;
62
63 FND_ATTACHED_DOCUMENTS2_PKG.delete_attachments
64 ( 'PO_HEADERS',
65 l_header_id||'-'||p_draft_id,
66 '', '', '', '', '');
67 FND_ATTACHED_DOCUMENTS2_PKG.delete_attachments
68 ( 'PO_HEADERS_DEL',
69 l_header_id||'-'||p_draft_id,
70 '', '', '', '', '');
71
72 DELETE FROM po_headers_draft_all
73 WHERE draft_id = p_draft_id
74 AND po_header_id = NVL(p_po_header_id, po_header_id);
75
76 d_position := 10;
77 EXCEPTION
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 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
84 END delete_rows;
85
86
87 -----------------------------------------------------------------------
88 --Start of Comments
89 --Name: sync_draft_from_txn
90 --Pre-reqs: None
91 --Modifies:
92 --Locks:
93 -- None
94 --Function:
95 -- Copy data from transaction table to draft table, if the corresponding
96 -- record in draft table does not exist. It also sets the delete flag of
97 -- the draft record according to the parameter.
98 --Parameters:
99 --IN:
100 --p_po_header_id_tbl
101 -- table of po header unique identifier
102 --p_draft_id_tbl
103 -- table of draft ids this sync up will be done for
104 --p_delete_flag_tbl
105 -- table fo flags to indicate whether the draft record should be maked as
106 -- "to be deleted"
107 --IN OUT:
108 --OUT:
109 --x_record_already_exist_tbl
110 -- Returns whether the record was already in draft table or not
111 --Returns:
112 --Notes:
113 --Testing:
114 --End of Comments
115 ------------------------------------------------------------------------
116 PROCEDURE sync_draft_from_txn
117 ( p_po_header_id_tbl IN PO_TBL_NUMBER,
118 p_draft_id_tbl IN PO_TBL_NUMBER,
119 p_delete_flag_tbl IN PO_TBL_VARCHAR1,
120 x_record_already_exist_tbl OUT NOCOPY PO_TBL_VARCHAR1
121 ) IS
122
123 d_api_name CONSTANT VARCHAR2(30) := 'sync_draft_from_txn';
124 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
125 d_position NUMBER;
126
127 l_distinct_id_list DBMS_SQL.NUMBER_TABLE;
128 l_duplicate_flag_tbl PO_TBL_VARCHAR1 := PO_TBL_VARCHAR1();
129
130 BEGIN
131 d_position := 0;
132 IF (PO_LOG.d_proc) THEN
133 PO_LOG.proc_begin(d_module);
134 END IF;
135
136 x_record_already_exist_tbl :=
137 PO_HEADERS_DRAFT_PVT.draft_changes_exist
138 ( p_draft_id_tbl => p_draft_id_tbl,
139 p_po_header_id_tbl => p_po_header_id_tbl
140 );
141
142 -- bug5471513 START
143 -- If there're duplicate entries in the id table,
144 -- we do not want to insert multiple entries
145 -- Created an associative array to store what id has appeared.
146 l_duplicate_flag_tbl.EXTEND(p_po_header_id_tbl.COUNT);
147
148 FOR i IN 1..p_po_header_id_tbl.COUNT LOOP
149 IF (x_record_already_exist_tbl(i) = FND_API.G_FALSE) THEN
150
151 IF (l_distinct_id_list.EXISTS(p_po_header_id_tbl(i))) THEN
152
153 l_duplicate_flag_tbl(i) := FND_API.G_TRUE;
154 ELSE
155 l_duplicate_flag_tbl(i) := FND_API.G_FALSE;
156
157 l_distinct_id_list(p_po_header_id_tbl(i)) := 1;
158 END IF;
159
160 ELSE
161
162 l_duplicate_flag_tbl(i) := NULL;
163
164 END IF;
165 END LOOP;
166
167 d_position := 10;
168 IF (PO_LOG.d_stmt) THEN
169 PO_LOG.stmt(d_module, d_position, 'transfer records from txn to dft');
170 END IF;
171
172 FORALL i IN 1..p_po_header_id_tbl.COUNT
173 INSERT INTO po_headers_draft_all
174 ( draft_id,
175 delete_flag,
176 change_accepted_flag,
177 po_header_id,
178 agent_id,
179 type_lookup_code,
180 last_update_date,
181 last_updated_by,
182 segment1,
183 summary_flag,
184 enabled_flag,
185 segment2,
186 segment3,
187 segment4,
188 segment5,
189 start_date_active,
190 end_date_active,
191 last_update_login,
192 creation_date,
193 created_by,
194 vendor_id,
195 vendor_site_id,
196 vendor_contact_id,
197 ship_to_location_id,
198 bill_to_location_id,
199 terms_id,
200 ship_via_lookup_code,
201 fob_lookup_code,
202 freight_terms_lookup_code,
203 status_lookup_code,
204 currency_code,
205 rate_type,
206 rate_date,
207 rate,
208 from_header_id,
209 from_type_lookup_code,
210 start_date,
211 end_date,
212 blanket_total_amount,
213 authorization_status,
214 revision_num,
215 revised_date,
216 approved_flag,
217 approved_date,
218 amount_limit,
219 min_release_amount,
220 note_to_authorizer,
221 note_to_vendor,
222 note_to_receiver,
223 print_count,
224 printed_date,
225 vendor_order_num,
226 confirming_order_flag,
227 comments,
228 reply_date,
229 reply_method_lookup_code,
230 rfq_close_date,
231 quote_type_lookup_code,
232 quotation_class_code,
233 quote_warning_delay_unit,
234 quote_warning_delay,
235 quote_vendor_quote_number,
236 acceptance_required_flag,
237 acceptance_due_date,
238 closed_date,
239 user_hold_flag,
240 approval_required_flag,
241 cancel_flag,
242 firm_status_lookup_code,
243 firm_date,
244 frozen_flag,
245 supply_agreement_flag,
246 edi_processed_flag,
247 edi_processed_status,
248 attribute_category,
249 attribute1,
250 attribute2,
251 attribute3,
252 attribute4,
253 attribute5,
254 attribute6,
255 attribute7,
256 attribute8,
257 attribute9,
258 attribute10,
259 attribute11,
260 attribute12,
261 attribute13,
262 attribute14,
263 attribute15,
264 closed_code,
265 ussgl_transaction_code,
266 government_context,
267 request_id,
268 program_application_id,
269 program_id,
270 program_update_date,
271 org_id,
272 global_attribute_category,
273 global_attribute1,
274 global_attribute2,
275 global_attribute3,
276 global_attribute4,
277 global_attribute5,
278 global_attribute6,
279 global_attribute7,
280 global_attribute8,
281 global_attribute9,
282 global_attribute10,
283 global_attribute11,
284 global_attribute12,
285 global_attribute13,
286 global_attribute14,
287 global_attribute15,
288 global_attribute16,
289 global_attribute17,
290 global_attribute18,
291 global_attribute19,
292 global_attribute20,
293 interface_source_code,
294 reference_num,
295 wf_item_type,
296 wf_item_key,
297 mrc_rate_type,
298 mrc_rate_date,
299 mrc_rate,
300 pcard_id,
301 price_update_tolerance,
302 pay_on_code,
303 xml_flag,
304 xml_send_date,
305 xml_change_send_date,
306 global_agreement_flag,
307 consigned_consumption_flag,
308 cbc_accounting_date,
309 consume_req_demand_flag,
310 change_requested_by,
311 shipping_control,
312 conterms_exist_flag,
313 conterms_articles_upd_date,
314 conterms_deliv_upd_date,
315 encumbrance_required_flag,
316 pending_signature_flag,
317 change_summary,
318 document_creation_method,
319 submit_date,
320 supplier_notif_method,
321 fax,
322 email_address,
323 retro_price_comm_updates_flag,
324 retro_price_apply_updates_flag,
325 update_sourcing_rules_flag,
326 auto_sourcing_flag,
327 created_language,
328 cpa_reference,
329 style_id,
330 tax_attribute_update_code, -- <ETAX INTEGRATION R12>
331 supplier_auth_enabled_flag, -- bug5022835
332 cat_admin_auth_enabled_flag, -- bug5022835
333 pay_when_paid, -- E and C ER
334 uda_template_id,
335 uda_template_date,
336 clm_effective_date,
337 clm_vendor_offer_number,
338 clm_award_administrator,
339 clm_no_signed_copies_to_return,
340 clm_min_guarantee_award_amt,
341 clm_min_guar_award_amt_percent,
342 clm_min_order_amount,
343 clm_max_order_amount,
344 clm_amt_synced_to_agreement,
345 clm_amount_released,
346 clm_external_idv,
347 clm_supplier_name,
348 clm_supplier_site_name,
349 clm_document_number,
350 clm_source_document_id,
351 clm_issuing_office,
352 clm_cotr_office,
353 clm_cotr_contact,
354 clm_priority_code,
355 clm_standard_form,
356 clm_document_format,
357 clm_mod_issuing_office,
358 ame_approval_id,
359 ame_transaction_type,
360 clm_award_type,
361 clm_contract_officer,
362 umbrella_program_id,
363 fon_ref_id,
364 clm_default_dist_flag,
365 old_amount_limit,
366 enable_all_sites, --ER 9824167
367 clm_contract_finance_code, -- CLM Phase 4
368 clm_special_contract_type,
369 clm_payment_instr_code -- CLM Phase 4
370 )
371 SELECT
372 p_draft_id_tbl(i),
373 p_delete_flag_tbl(i),
374 NULL,
375 po_header_id,
376 agent_id,
377 type_lookup_code,
378 last_update_date,
379 last_updated_by,
380 segment1,
381 summary_flag,
382 enabled_flag,
383 segment2,
384 segment3,
385 segment4,
386 segment5,
387 start_date_active,
388 end_date_active,
389 last_update_login,
390 creation_date,
391 created_by,
392 vendor_id,
393 vendor_site_id,
394 vendor_contact_id,
395 ship_to_location_id,
396 bill_to_location_id,
397 terms_id,
398 ship_via_lookup_code,
399 fob_lookup_code,
400 freight_terms_lookup_code,
401 status_lookup_code,
402 currency_code,
403 rate_type,
404 rate_date,
405 rate,
406 from_header_id,
407 from_type_lookup_code,
408 start_date,
409 end_date,
410 blanket_total_amount,
411 authorization_status,
412 revision_num,
413 revised_date,
414 approved_flag,
415 approved_date,
416 amount_limit,
417 min_release_amount,
418 note_to_authorizer,
419 note_to_vendor,
420 note_to_receiver,
421 nvl(print_count, 0), --bug 16225321
422 printed_date,
423 vendor_order_num,
424 confirming_order_flag,
425 comments,
426 reply_date,
427 reply_method_lookup_code,
428 rfq_close_date,
429 quote_type_lookup_code,
430 quotation_class_code,
431 quote_warning_delay_unit,
432 quote_warning_delay,
433 quote_vendor_quote_number,
434 acceptance_required_flag,
435 acceptance_due_date,
436 closed_date,
437 user_hold_flag,
438 approval_required_flag,
439 cancel_flag,
440 firm_status_lookup_code,
441 firm_date,
442 frozen_flag,
443 supply_agreement_flag,
444 edi_processed_flag,
445 edi_processed_status,
446 attribute_category,
447 attribute1,
448 attribute2,
449 attribute3,
450 attribute4,
451 attribute5,
452 attribute6,
453 attribute7,
454 attribute8,
455 attribute9,
456 attribute10,
457 attribute11,
458 attribute12,
459 attribute13,
460 attribute14,
461 attribute15,
462 closed_code,
463 ussgl_transaction_code,
464 government_context,
465 request_id,
466 program_application_id,
467 program_id,
468 program_update_date,
469 org_id,
470 global_attribute_category,
471 global_attribute1,
472 global_attribute2,
473 global_attribute3,
474 global_attribute4,
475 global_attribute5,
476 global_attribute6,
477 global_attribute7,
478 global_attribute8,
479 global_attribute9,
480 global_attribute10,
481 global_attribute11,
482 global_attribute12,
483 global_attribute13,
484 global_attribute14,
485 global_attribute15,
486 global_attribute16,
487 global_attribute17,
488 global_attribute18,
489 global_attribute19,
490 global_attribute20,
491 interface_source_code,
492 reference_num,
493 wf_item_type,
494 wf_item_key,
495 mrc_rate_type,
496 mrc_rate_date,
497 mrc_rate,
498 pcard_id,
499 price_update_tolerance,
500 pay_on_code,
501 xml_flag,
502 xml_send_date,
503 xml_change_send_date,
504 global_agreement_flag,
505 consigned_consumption_flag,
506 cbc_accounting_date,
507 consume_req_demand_flag,
508 change_requested_by,
509 shipping_control,
510 conterms_exist_flag,
511 conterms_articles_upd_date,
512 conterms_deliv_upd_date,
513 encumbrance_required_flag,
514 pending_signature_flag,
515 change_summary,
516 document_creation_method,
517 submit_date,
518 supplier_notif_method,
519 fax,
520 email_address,
521 retro_price_comm_updates_flag,
522 retro_price_apply_updates_flag,
523 update_sourcing_rules_flag,
524 auto_sourcing_flag,
525 created_language,
526 cpa_reference,
527 style_id,
528 tax_attribute_update_code, -- <ETAX INTEGRATION R12>
529 supplier_auth_enabled_flag, -- bug5022835
530 cat_admin_auth_enabled_flag, -- bug5022835
531 pay_when_paid, -- E and C ER
532 uda_template_id,
533 uda_template_date,
534 clm_effective_date,
535 clm_vendor_offer_number,
536 clm_award_administrator,
537 clm_no_signed_copies_to_return,
538 clm_min_guarantee_award_amt,
539 clm_min_guar_award_amt_percent,
540 clm_min_order_amount,
541 clm_max_order_amount,
542 clm_amt_synced_to_agreement,
543 clm_amount_released,
544 clm_external_idv,
545 clm_supplier_name,
546 clm_supplier_site_name,
547 clm_document_number,
548 clm_source_document_id,
549 clm_issuing_office,
550 clm_cotr_office,
551 clm_cotr_contact,
552 clm_priority_code,
553 clm_standard_form,
554 clm_document_format,
555 clm_mod_issuing_office,
556 ame_approval_id,
557 ame_transaction_type,
558 clm_award_type,
559 clm_contract_officer,
560 umbrella_program_id,
561 fon_ref_id,
562 clm_default_dist_flag,
563 amount_limit,
564 enable_all_sites, --ER 9824167
565 clm_contract_finance_code, -- CLM Phase 4
566 clm_special_contract_type,
567 clm_payment_instr_code -- CLM Phase 4
568 FROM po_headers_all
569 WHERE po_header_id = p_po_header_id_tbl(i)
570 AND x_record_already_exist_tbl(i) = FND_API.G_FALSE
571 AND l_duplicate_flag_tbl(i) = FND_API.G_FALSE;
572
573 d_position := 20;
574 IF (PO_LOG.d_stmt) THEN
575 PO_LOG.stmt(d_module, d_position, 'transfer count = ' || SQL%ROWCOUNT);
576 END IF;
577
578 FORALL i IN 1..p_po_header_id_tbl.COUNT
579 UPDATE po_headers_draft_all
580 SET delete_flag = p_delete_flag_tbl(i)
581 WHERE po_header_id = p_po_header_id_tbl(i)
582 AND draft_id = p_draft_id_tbl(i)
583 AND NVL(delete_flag, 'N') <> 'Y' -- bug5570989
584 AND x_record_already_exist_tbl(i) = FND_API.G_TRUE;
585
586 d_position := 30;
587
588 IF (PO_LOG.d_stmt) THEN
589 PO_LOG.stmt(d_module, d_position, 'update draft records that are already' ||
590 ' in draft table. Count = ' || SQL%ROWCOUNT);
591 END IF;
592
593 d_position := 40;
594
595 IF (PO_LOG.d_proc) THEN
596 PO_LOG.proc_end(d_module);
597 END IF;
598
599 EXCEPTION
600 WHEN OTHERS THEN
601 PO_MESSAGE_S.add_exc_msg
602 ( p_pkg_name => d_pkg_name,
603 p_procedure_name => d_api_name || '.' || d_position
604 );
605 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
606 END sync_draft_from_txn;
607
608 -----------------------------------------------------------------------
609 --Start of Comments
610 --Name: sync_draft_from_txn
611 --Pre-reqs: None
612 --Modifies:
613 --Locks:
614 -- None
615 --Function:
616 -- Same functionality as the bulk version of this procedure
617 --Parameters:
618 --IN:
619 --p_po_header_id
620 -- po header unique identifier
621 --p_draft_id
622 -- the draft this sync up will be done for
623 --p_delete_flag
624 -- flag to indicate whether the draft record should be maked as "to be
625 -- deleted"
626 --IN OUT:
627 --OUT:
628 --x_record_already_exist
629 -- Returns whether the record was already in draft table or not
630 --Returns:
631 --Notes:
632 --Testing:
633 --End of Comments
634 ------------------------------------------------------------------------
635 PROCEDURE sync_draft_from_txn
636 ( p_po_header_id IN NUMBER,
637 p_draft_id IN NUMBER,
638 p_delete_flag IN VARCHAR2,
639 x_record_already_exist OUT NOCOPY VARCHAR2
640 ) IS
641
642 d_api_name CONSTANT VARCHAR2(30) := 'sync_draft_from_txn';
643 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
644 d_position NUMBER;
645
646 l_record_already_exist_tbl PO_TBL_VARCHAR1;
647
648 BEGIN
649 d_position := 0;
650 IF (PO_LOG.d_proc) THEN
651 PO_LOG.proc_begin(d_module);
652 PO_LOG.proc_begin(d_module, 'p_po_header_id', p_po_header_id);
653 END IF;
654
655 sync_draft_from_txn
656 ( p_po_header_id_tbl => PO_TBL_NUMBER(p_po_header_id),
657 p_draft_id_tbl => PO_TBL_NUMBER(p_draft_id),
658 p_delete_flag_tbl => PO_TBL_VARCHAR1(p_delete_flag),
659 x_record_already_exist_tbl => l_record_already_exist_tbl
660 );
661
662 x_record_already_exist := l_record_already_exist_tbl(1);
663
664 d_position := 10;
665 IF (PO_LOG.d_proc) THEN
666 PO_LOG.proc_end(d_module);
667 PO_LOG.proc_end(d_module, 'x_record_already_exist', x_record_already_exist);
668 END IF;
669
670 EXCEPTION
671 WHEN OTHERS THEN
672 PO_MESSAGE_S.add_exc_msg
673 ( p_pkg_name => d_pkg_name,
674 p_procedure_name => d_api_name || '.' || d_position
675 );
676 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
677 END sync_draft_from_txn;
678
679 -----------------------------------------------------------------------
680 --Start of Comments
681 --Name: merge_changes
682 --Pre-reqs: None
683 --Modifies:
684 --Locks:
685 -- None
686 --Function:
687 -- Merge the records in draft table to transaction table
688 -- Either insert, update or delete will be performed on top of transaction
689 -- table, depending on the delete_flag on the draft record and whether the
690 -- record already exists in transaction table
691 --
692 --Parameters:
693 --IN:
694 --p_draft_id
695 -- draft unique identifier
696 --IN OUT:
697 --OUT:
698 --Returns:
699 --Notes:
700 --Testing:
701 --End of Comments
702 ------------------------------------------------------------------------
703 PROCEDURE merge_changes
704 ( p_draft_id IN NUMBER,
705 p_draft_type IN PO_DRAFTS.draft_type%TYPE
706 ) IS
707
708 d_api_name CONSTANT VARCHAR2(30) := 'merge_changes';
709 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
710 d_position NUMBER;
711
712 BEGIN
713 d_position := 0;
714 IF (PO_LOG.d_proc) THEN
715 PO_LOG.proc_begin(d_module);
716 END IF;
717
718 -- Since putting DELETE within MERGE statement is causing database
719 -- to thrown internal error, for now we just separate the DELETE statement.
720 -- Once this is fixed we'll move the delete statement back to the merge
721 -- statement
722
723 -- bug5187544
724 -- Delete only lines that have not been rejected
725
726 DELETE FROM po_headers_all PH
727 WHERE PH.po_header_id IN
728 ( SELECT PHD.po_header_id
729 FROM po_headers_draft_all PHD
730 WHERE PHD.draft_id = p_draft_id
731 AND PHD.delete_flag = 'Y'
732 AND NVL(PHD.change_accepted_flag, 'Y') = 'Y');
733
734 -- Merge PO Header changes
735 -- For update case, the following columns will be skipped:
736 -- PH.po_header_id
737 -- PH.creation_date
738 -- PH.created_by
739 -- PH.interface_source_code
740 -- PH.reference_num
741 -- PH.wf_item_type
742 -- PH.wf_item_key
743 -- PH.mrc_rate_type
744 -- PH.mrc_rate_date
745 -- PH.mrc_rate
746 -- PH.xml_flag
747 -- PH.xml_send_date
748 -- PH.xml_change_send_date
749 -- PH.global_agreement_flag
750 -- PH.consigned_consumption_flag
751 -- PH.cbc_accounting_date
752 -- PH.consume_req_demand_flag
753 -- PH.conterms_exist_flag
754 -- PH.pending_signature_flag
755 -- Bug 13628576
756 -- Removing 'change_summary' from the list of columns to be skipped. When a customer
757 -- updates a PO, is when 'change summary' makes sense.
758 -- Added "PH.change_summary = PHDV.change_summary" in the UPDATE SET statment.
759 -- PH.change_summary
760 -- <end> Bug 13628576
761 -- PH.document_creation_method
762 -- PH.submit_date
763 -- PH.edi_processed_flag
764 -- PH.edi_processed_status,
765 -- PH.created_language
766 -- PH.cpa_reference
767 -- PH.style_id
768 -- PH.supplier_auth_enabled_flag
769 -- PH.cat_admin_auth_enabled_flag
770 -- PH.uda_template_id,
771 -- PH.uda_template_date
772 MERGE INTO po_headers_all PH
773 USING (
774 SELECT
775 PHD.draft_id,
776 PHD.delete_flag,
777 PHD.change_accepted_flag,
778 PHD.po_header_id,
779 PHD.agent_id,
780 PHD.type_lookup_code,
781 PHD.last_update_date,
782 PHD.last_updated_by,
783 PHD.segment1,
784 PHD.summary_flag,
785 PHD.enabled_flag,
786 PHD.segment2,
787 PHD.segment3,
788 PHD.segment4,
789 PHD.segment5,
790 PHD.start_date_active,
791 PHD.end_date_active,
792 PHD.last_update_login,
793 PHD.creation_date,
794 PHD.created_by,
795 PHD.vendor_id,
796 PHD.vendor_site_id,
797 PHD.vendor_contact_id,
798 PHD.ship_to_location_id,
799 PHD.bill_to_location_id,
800 PHD.terms_id,
801 PHD.ship_via_lookup_code,
802 PHD.fob_lookup_code,
803 PHD.freight_terms_lookup_code,
804 PHD.status_lookup_code,
805 PHD.currency_code,
806 PHD.rate_type,
807 PHD.rate_date,
808 PHD.rate,
809 PHD.from_header_id,
810 PHD.from_type_lookup_code,
811 PHD.start_date,
812 PHD.end_date,
813 PHD.blanket_total_amount,
814 PHD.authorization_status,
815 PHD.revision_num,
816 PHD.revised_date,
817 PHD.approved_flag,
818 PHD.approved_date,
819 PHD.amount_limit,
820 PHD.min_release_amount,
821 PHD.note_to_authorizer,
822 PHD.note_to_vendor,
823 PHD.note_to_receiver,
824 nvl(PHD.print_count, 0) print_count, --bug 16225321
825 PHD.printed_date,
826 PHD.vendor_order_num,
827 PHD.confirming_order_flag,
828 PHD.comments,
829 PHD.reply_date,
830 PHD.reply_method_lookup_code,
831 PHD.rfq_close_date,
832 PHD.quote_type_lookup_code,
833 PHD.quotation_class_code,
834 PHD.quote_warning_delay_unit,
835 PHD.quote_warning_delay,
836 PHD.quote_vendor_quote_number,
837 PHD.acceptance_required_flag,
838 PHD.acceptance_due_date,
839 PHD.closed_date,
840 PHD.user_hold_flag,
841 PHD.approval_required_flag,
842 PHD.cancel_flag,
843 PHD.firm_status_lookup_code,
844 PHD.firm_date,
845 PHD.frozen_flag,
846 PHD.supply_agreement_flag,
847 PHD.edi_processed_flag,
848 PHD.edi_processed_status,
849 PHD.attribute_category,
850 PHD.attribute1,
851 PHD.attribute2,
852 PHD.attribute3,
853 PHD.attribute4,
854 PHD.attribute5,
855 PHD.attribute6,
856 PHD.attribute7,
860 PHD.attribute11,
857 PHD.attribute8,
858 PHD.attribute9,
859 PHD.attribute10,
861 PHD.attribute12,
862 PHD.attribute13,
863 PHD.attribute14,
864 PHD.attribute15,
865 PHD.closed_code,
866 PHD.ussgl_transaction_code,
867 PHD.government_context,
868 PHD.request_id,
869 PHD.program_application_id,
870 PHD.program_id,
871 PHD.program_update_date,
872 PHD.org_id,
873 PHD.global_attribute_category,
874 PHD.global_attribute1,
875 PHD.global_attribute2,
876 PHD.global_attribute3,
877 PHD.global_attribute4,
878 PHD.global_attribute5,
879 PHD.global_attribute6,
880 PHD.global_attribute7,
881 PHD.global_attribute8,
882 PHD.global_attribute9,
883 PHD.global_attribute10,
884 PHD.global_attribute11,
885 PHD.global_attribute12,
886 PHD.global_attribute13,
887 PHD.global_attribute14,
888 PHD.global_attribute15,
889 PHD.global_attribute16,
890 PHD.global_attribute17,
891 PHD.global_attribute18,
892 PHD.global_attribute19,
893 PHD.global_attribute20,
894 PHD.interface_source_code,
895 PHD.reference_num,
896 PHD.wf_item_type,
897 PHD.wf_item_key,
898 PHD.mrc_rate_type,
899 PHD.mrc_rate_date,
900 PHD.mrc_rate,
901 PHD.pcard_id,
902 PHD.price_update_tolerance,
903 PHD.pay_on_code,
904 PHD.xml_flag,
905 PHD.xml_send_date,
906 PHD.xml_change_send_date,
907 PHD.global_agreement_flag,
908 PHD.consigned_consumption_flag,
909 PHD.cbc_accounting_date,
910 PHD.consume_req_demand_flag,
911 PHD.change_requested_by,
912 PHD.shipping_control,
913 PHD.conterms_exist_flag,
914 PHD.conterms_articles_upd_date,
915 PHD.conterms_deliv_upd_date,
916 PHD.encumbrance_required_flag,
917 PHD.pending_signature_flag,
918 PHD.change_summary,
919 PHD.document_creation_method,
920 PHD.submit_date,
921 PHD.supplier_notif_method,
922 PHD.fax,
923 PHD.email_address,
924 PHD.retro_price_comm_updates_flag,
925 PHD.retro_price_apply_updates_flag,
926 PHD.update_sourcing_rules_flag,
927 PHD.auto_sourcing_flag,
928 PHD.created_language,
929 PHD.cpa_reference,
930 PHD.style_id,
931 PHD.tax_attribute_update_code, -- <ETAX INTEGRATION R12>
932 PHD.pay_when_paid, -- E and C ER
933 PHD.uda_template_id,
934 PHD.uda_template_date,
935 PHD.clm_effective_date,
936 PHD.clm_vendor_offer_number,
937 PHD.clm_award_administrator,
938 PHD.clm_no_signed_copies_to_return,
939 PHD.clm_min_guarantee_award_amt,
940 PHD.clm_min_guar_award_amt_percent,
941 PHD.clm_min_order_amount,
942 PHD.clm_max_order_amount,
943 PHD.clm_amt_synced_to_agreement,
944 PHD.clm_amount_released,
945 PHD.clm_external_idv,
946 PHD.clm_supplier_name,
947 PHD.clm_supplier_site_name,
948 PHD.clm_document_number,
949 PHD.clm_source_document_id,
950 PHD.clm_issuing_office,
951 PHD.clm_cotr_office,
952 PHD.clm_cotr_contact,
953 PHD.clm_priority_code,
954 PHD.clm_standard_form,
955 PHD.clm_document_format,
956 PHD.clm_mod_issuing_office,
957 PHD.ame_approval_id,
958 PHD.ame_transaction_type,
959 PHD.clm_award_type,
960 PHD.clm_contract_officer,
961 PHD.umbrella_program_id,
962 PHD.fon_ref_id,
963 PHD.clm_default_dist_flag,
964 PHD.enable_all_sites, --ER 9824167
965 PHD.clm_contract_finance_code,
966 PHD.clm_special_contract_type, -- CLM Phase 4
967 PHD.clm_payment_instr_code -- CLM Phase 4
968 FROM po_headers_draft_all PHD
969 WHERE PHD.draft_id = p_draft_id
970 AND NVL(PHD.change_accepted_flag, 'Y') = 'Y'
971 ) PHDV
972 ON (PH.po_header_id = PHDV.po_header_id)
973 WHEN MATCHED THEN
974 UPDATE
975 SET
976 PH.agent_id = PHDV.agent_id,
977 PH.type_lookup_code = PHDV.type_lookup_code,
978 PH.last_update_date = PHDV.last_update_date,
979 PH.last_updated_by = PHDV.last_updated_by,
980 PH.segment1 = PHDV.segment1,
981 PH.summary_flag = PHDV.summary_flag,
982 PH.enabled_flag = PHDV.enabled_flag,
983 PH.segment2 = PHDV.segment2,
984 PH.segment3 = PHDV.segment3,
985 PH.segment4 = PHDV.segment4,
986 PH.segment5 = PHDV.segment5,
987 PH.start_date_active = PHDV.start_date_active,
988 PH.end_date_active = PHDV.end_date_active,
989 PH.last_update_login = PHDV.last_update_login,
990 PH.vendor_id = PHDV.vendor_id,
991 PH.vendor_site_id = PHDV.vendor_site_id,
992 PH.vendor_contact_id = PHDV.vendor_contact_id,
993 PH.ship_to_location_id = PHDV.ship_to_location_id,
994 PH.bill_to_location_id = PHDV.bill_to_location_id,
995 PH.terms_id = PHDV.terms_id,
996 PH.ship_via_lookup_code = PHDV.ship_via_lookup_code,
997 PH.fob_lookup_code = PHDV.fob_lookup_code,
998 PH.freight_terms_lookup_code = PHDV.freight_terms_lookup_code,
999 PH.status_lookup_code = PHDV.status_lookup_code,
1000 PH.currency_code = PHDV.currency_code,
1004 PH.from_header_id = PHDV.from_header_id,
1001 PH.rate_type = PHDV.rate_type,
1002 PH.rate_date = PHDV.rate_date,
1003 PH.rate = PHDV.rate,
1005 PH.from_type_lookup_code = PHDV.from_type_lookup_code,
1006 PH.start_date = PHDV.start_date,
1007 PH.end_date = PHDV.end_date,
1008 PH.blanket_total_amount = PHDV.blanket_total_amount,
1009 PH.authorization_status = PHDV.authorization_status,
1010 PH.revision_num = PHDV.revision_num,
1011 PH.revised_date = PHDV.revised_date,
1012 PH.amount_limit = PHDV.amount_limit,
1013 PH.min_release_amount = PHDV.min_release_amount,
1014 PH.note_to_authorizer = PHDV.note_to_authorizer,
1015 PH.note_to_vendor = PHDV.note_to_vendor,
1016 PH.note_to_receiver = PHDV.note_to_receiver,
1017 PH.print_count = PHDV.print_count,
1018 PH.printed_date = PHDV.printed_date,
1019 PH.vendor_order_num = PHDV.vendor_order_num,
1020 PH.confirming_order_flag = PHDV.confirming_order_flag,
1021 PH.comments = PHDV.comments,
1022 PH.reply_date = PHDV.reply_date,
1023 PH.reply_method_lookup_code = PHDV.reply_method_lookup_code,
1024 PH.rfq_close_date = PHDV.rfq_close_date,
1025 PH.quote_type_lookup_code = PHDV.quote_type_lookup_code,
1026 PH.quotation_class_code = PHDV.quotation_class_code,
1027 PH.quote_warning_delay_unit = PHDV.quote_warning_delay_unit,
1028 PH.quote_warning_delay = PHDV.quote_warning_delay,
1029 PH.quote_vendor_quote_number = PHDV.quote_vendor_quote_number,
1030 PH.closed_date = PHDV.closed_date,
1031 PH.user_hold_flag = PHDV.user_hold_flag,
1032 PH.approval_required_flag = PHDV.approval_required_flag,
1033 PH.cancel_flag = PHDV.cancel_flag,
1034 PH.firm_status_lookup_code = PHDV.firm_status_lookup_code,
1035 PH.firm_date = PHDV.firm_date,
1036 PH.frozen_flag = PHDV.frozen_flag,
1037 PH.supply_agreement_flag = PHDV.supply_agreement_flag,
1038 PH.attribute_category = PHDV.attribute_category,
1039 PH.attribute1 = PHDV.attribute1,
1040 PH.attribute2 = PHDV.attribute2,
1041 PH.attribute3 = PHDV.attribute3,
1042 PH.attribute4 = PHDV.attribute4,
1043 PH.attribute5 = PHDV.attribute5,
1044 PH.attribute6 = PHDV.attribute6,
1045 PH.attribute7 = PHDV.attribute7,
1046 PH.attribute8 = PHDV.attribute8,
1047 PH.attribute9 = PHDV.attribute9,
1048 PH.attribute10 = PHDV.attribute10,
1049 PH.attribute11 = PHDV.attribute11,
1050 PH.attribute12 = PHDV.attribute12,
1051 PH.attribute13 = PHDV.attribute13,
1052 PH.attribute14 = PHDV.attribute14,
1053 PH.attribute15 = PHDV.attribute15,
1054 PH.closed_code = PHDV.closed_code,
1055 PH.ussgl_transaction_code = PHDV.ussgl_transaction_code,
1056 PH.government_context = PHDV.government_context,
1057 PH.request_id = PHDV.request_id,
1058 PH.program_application_id = PHDV.program_application_id,
1059 PH.program_id = PHDV.program_id,
1060 PH.program_update_date = PHDV.program_update_date,
1061 PH.org_id = PHDV.org_id,
1062 PH.global_attribute_category = PHDV.global_attribute_category,
1063 PH.global_attribute1 = PHDV.global_attribute1,
1064 PH.global_attribute2 = PHDV.global_attribute2,
1065 PH.global_attribute3 = PHDV.global_attribute3,
1066 PH.global_attribute4 = PHDV.global_attribute4,
1067 PH.global_attribute5 = PHDV.global_attribute5,
1068 PH.global_attribute6 = PHDV.global_attribute6,
1069 PH.global_attribute7 = PHDV.global_attribute7,
1070 PH.global_attribute8 = PHDV.global_attribute8,
1071 PH.global_attribute9 = PHDV.global_attribute9,
1072 PH.global_attribute10 = PHDV.global_attribute10,
1073 PH.global_attribute11 = PHDV.global_attribute11,
1074 PH.global_attribute12 = PHDV.global_attribute12,
1075 PH.global_attribute13 = PHDV.global_attribute13,
1076 PH.global_attribute14 = PHDV.global_attribute14,
1077 PH.global_attribute15 = PHDV.global_attribute15,
1078 PH.global_attribute16 = PHDV.global_attribute16,
1079 PH.global_attribute17 = PHDV.global_attribute17,
1080 PH.global_attribute18 = PHDV.global_attribute18,
1081 PH.global_attribute19 = PHDV.global_attribute19,
1082 PH.global_attribute20 = PHDV.global_attribute20,
1083 PH.pcard_id = PHDV.pcard_id,
1084 PH.price_update_tolerance = PHDV.price_update_tolerance,
1085 PH.pay_on_code = PHDV.pay_on_code,
1086 PH.change_requested_by = PHDV.change_requested_by,
1087 PH.shipping_control = PHDV.shipping_control,
1088 PH.conterms_articles_upd_date = PHDV.conterms_articles_upd_date,
1089 PH.conterms_deliv_upd_date = PHDV.conterms_deliv_upd_date,
1090 PH.encumbrance_required_flag = PHDV.encumbrance_required_flag,
1091 PH.supplier_notif_method = PHDV.supplier_notif_method,
1092 PH.change_summary = PHDV.change_summary, -- Bug 13628576
1093 PH.fax = PHDV.fax,
1094 PH.email_address = PHDV.email_address,
1095 PH.retro_price_comm_updates_flag = PHDV.retro_price_comm_updates_flag,
1096 PH.retro_price_apply_updates_flag = PHDV.retro_price_apply_updates_flag,
1097 PH.update_sourcing_rules_flag = PHDV.update_sourcing_rules_flag,
1098 PH.auto_sourcing_flag = PHDV.auto_sourcing_flag,
1099 PH.tax_attribute_update_code = PHDV.tax_attribute_update_code, -- <ETAX INTEGRATION R12>
1100 PH.pay_when_paid = PHDV.pay_when_paid,
1101 PH.clm_effective_date = PHDV.clm_effective_date,
1102 PH.clm_vendor_offer_number = PHDV.clm_vendor_offer_number,
1103 PH.clm_award_administrator = PHDV.clm_award_administrator,
1104 PH.clm_no_signed_copies_to_return = PHDV.clm_no_signed_copies_to_return,
1108 PH.clm_max_order_amount = PHDV.clm_max_order_amount,
1105 PH.clm_min_guarantee_award_amt = PHDV.clm_min_guarantee_award_amt,
1106 PH.clm_min_guar_award_amt_percent = PHDV.clm_min_guar_award_amt_percent,
1107 PH.clm_min_order_amount = PHDV.clm_min_order_amount,
1109 PH.clm_amt_synced_to_agreement = PHDV.clm_amt_synced_to_agreement,
1110 PH.clm_amount_released = PHDV.clm_amount_released,
1111 PH.clm_external_idv = PHDV.clm_external_idv,
1112 PH.clm_supplier_name = PHDV.clm_supplier_name,
1113 PH.clm_supplier_site_name = PHDV.clm_supplier_site_name,
1114 PH.clm_document_number = PHDV.clm_document_number,
1115 PH.clm_source_document_id = PHDV.clm_source_document_id,
1116 PH.clm_issuing_office = PHDV.clm_issuing_office,
1117 PH.clm_cotr_office = PHDV.clm_cotr_office,
1118 PH.clm_cotr_contact = PHDV.clm_cotr_contact,
1119 PH.clm_priority_code = PHDV.clm_priority_code,
1120 PH.clm_mod_issuing_office = PHDV.clm_mod_issuing_office,
1121 PH.clm_standard_form = PHDV.clm_standard_form,
1122 PH.clm_document_format = PHDV.clm_document_format,
1123 PH.clm_award_type = PHDV.clm_award_type,
1124 PH.clm_contract_officer = PHDV.clm_contract_officer,
1125 PH.umbrella_program_id = PHDV.umbrella_program_id,
1126 PH.fon_ref_id = PHDV.fon_ref_id,
1127 PH.enable_all_sites = PHDV.enable_all_sites, --ER 9824167
1128 -- Bug # 13612774 Added Contract terms
1129 PH.conterms_exist_flag =
1130 DECODE(p_draft_type,
1131 PO_DRAFTS_PVT.g_draft_type_MOD, PHDV.conterms_exist_flag,
1132 PH.conterms_exist_flag),
1133 PH.ame_approval_id =
1134 DECODE(p_draft_type,
1135 PO_DRAFTS_PVT.g_draft_type_MOD, PH.ame_approval_id,
1136 PHDV.ame_approval_id),
1137 PH.ame_transaction_type =
1138 DECODE(p_draft_type,
1139 PO_DRAFTS_PVT.g_draft_type_MOD, PH.ame_transaction_type,
1140 PHDV.ame_transaction_type),
1141 PH.approved_flag =
1142 DECODE(p_draft_type,
1143 PO_DRAFTS_PVT.g_draft_type_MOD, PH.approved_flag,
1144 PHDV.approved_flag),
1145 PH.approved_date =
1146 DECODE(p_draft_type,
1147 PO_DRAFTS_PVT.g_draft_type_MOD, PH.approved_date,
1148 PHDV.approved_date),
1149 PH.acceptance_required_flag =
1150 DECODE(p_draft_type,
1151 PO_DRAFTS_PVT.g_draft_type_MOD, PH.acceptance_required_flag,
1152 PHDV.acceptance_required_flag),
1153 PH.acceptance_due_date =
1154 DECODE(p_draft_type,
1155 PO_DRAFTS_PVT.g_draft_type_MOD, PH.acceptance_due_date,
1156 PHDV.acceptance_due_date),
1157 PH.clm_default_dist_flag =
1158 DECODE(p_draft_type,
1159 PO_DRAFTS_PVT.g_draft_type_MOD, PH.clm_default_dist_flag,
1160 PHDV.clm_default_dist_flag),
1161 PH.clm_contract_finance_code = PHDV.clm_contract_finance_code,
1162 PH.clm_special_contract_type = PHDV.clm_special_contract_type, -- CLM Phase 4
1163 PH.clm_payment_instr_code = PHDV.clm_payment_instr_code-- CLM Phase 4
1164 -- DELETE WHERE PHDV.delete_flag = 'Y'
1165 WHEN NOT MATCHED THEN
1166 INSERT
1167 (
1168 PH.po_header_id,
1169 PH.agent_id,
1170 PH.type_lookup_code,
1171 PH.last_update_date,
1172 PH.last_updated_by,
1173 PH.segment1,
1174 PH.summary_flag,
1175 PH.enabled_flag,
1176 PH.segment2,
1177 PH.segment3,
1178 PH.segment4,
1179 PH.segment5,
1180 PH.start_date_active,
1181 PH.end_date_active,
1182 PH.last_update_login,
1183 PH.creation_date,
1184 PH.created_by,
1185 PH.vendor_id,
1186 PH.vendor_site_id,
1187 PH.vendor_contact_id,
1188 PH.ship_to_location_id,
1189 PH.bill_to_location_id,
1190 PH.terms_id,
1191 PH.ship_via_lookup_code,
1192 PH.fob_lookup_code,
1193 PH.freight_terms_lookup_code,
1194 PH.status_lookup_code,
1195 PH.currency_code,
1196 PH.rate_type,
1197 PH.rate_date,
1198 PH.rate,
1199 PH.from_header_id,
1200 PH.from_type_lookup_code,
1201 PH.start_date,
1202 PH.end_date,
1203 PH.blanket_total_amount,
1204 PH.authorization_status,
1205 PH.revision_num,
1206 PH.revised_date,
1207 PH.approved_flag,
1208 PH.approved_date,
1209 PH.amount_limit,
1210 PH.min_release_amount,
1211 PH.note_to_authorizer,
1212 PH.note_to_vendor,
1213 PH.note_to_receiver,
1214 PH.print_count,
1215 PH.printed_date,
1216 PH.vendor_order_num,
1217 PH.confirming_order_flag,
1218 PH.comments,
1219 PH.reply_date,
1220 PH.reply_method_lookup_code,
1221 PH.rfq_close_date,
1222 PH.quote_type_lookup_code,
1223 PH.quotation_class_code,
1224 PH.quote_warning_delay_unit,
1225 PH.quote_warning_delay,
1226 PH.quote_vendor_quote_number,
1227 PH.acceptance_required_flag,
1228 PH.acceptance_due_date,
1229 PH.closed_date,
1230 PH.user_hold_flag,
1231 PH.approval_required_flag,
1232 PH.cancel_flag,
1233 PH.firm_status_lookup_code,
1234 PH.firm_date,
1235 PH.frozen_flag,
1236 PH.supply_agreement_flag,
1237 PH.edi_processed_flag,
1238 PH.edi_processed_status,
1239 PH.attribute_category,
1240 PH.attribute1,
1241 PH.attribute2,
1242 PH.attribute3,
1243 PH.attribute4,
1244 PH.attribute5,
1248 PH.attribute9,
1245 PH.attribute6,
1246 PH.attribute7,
1247 PH.attribute8,
1249 PH.attribute10,
1250 PH.attribute11,
1251 PH.attribute12,
1252 PH.attribute13,
1253 PH.attribute14,
1254 PH.attribute15,
1255 PH.closed_code,
1256 PH.ussgl_transaction_code,
1257 PH.government_context,
1258 PH.request_id,
1259 PH.program_application_id,
1260 PH.program_id,
1261 PH.program_update_date,
1262 PH.org_id,
1263 PH.global_attribute_category,
1264 PH.global_attribute1,
1265 PH.global_attribute2,
1266 PH.global_attribute3,
1267 PH.global_attribute4,
1268 PH.global_attribute5,
1269 PH.global_attribute6,
1270 PH.global_attribute7,
1271 PH.global_attribute8,
1272 PH.global_attribute9,
1273 PH.global_attribute10,
1274 PH.global_attribute11,
1275 PH.global_attribute12,
1276 PH.global_attribute13,
1277 PH.global_attribute14,
1278 PH.global_attribute15,
1279 PH.global_attribute16,
1280 PH.global_attribute17,
1281 PH.global_attribute18,
1282 PH.global_attribute19,
1283 PH.global_attribute20,
1284 PH.interface_source_code,
1285 PH.reference_num,
1286 PH.wf_item_type,
1287 PH.wf_item_key,
1288 PH.mrc_rate_type,
1289 PH.mrc_rate_date,
1290 PH.mrc_rate,
1291 PH.pcard_id,
1292 PH.price_update_tolerance,
1293 PH.pay_on_code,
1294 PH.xml_flag,
1295 PH.xml_send_date,
1296 PH.xml_change_send_date,
1297 PH.global_agreement_flag,
1298 PH.consigned_consumption_flag,
1299 PH.cbc_accounting_date,
1300 PH.consume_req_demand_flag,
1301 PH.change_requested_by,
1302 PH.shipping_control,
1303 PH.conterms_exist_flag,
1304 PH.conterms_articles_upd_date,
1305 PH.conterms_deliv_upd_date,
1306 PH.encumbrance_required_flag,
1307 PH.pending_signature_flag,
1308 PH.change_summary,
1309 PH.document_creation_method,
1310 PH.submit_date,
1311 PH.supplier_notif_method,
1312 PH.fax,
1313 PH.email_address,
1314 PH.retro_price_comm_updates_flag,
1315 PH.retro_price_apply_updates_flag,
1316 PH.update_sourcing_rules_flag,
1317 PH.auto_sourcing_flag,
1318 PH.created_language,
1319 PH.cpa_reference,
1320 PH.style_id,
1321 PH.tax_attribute_update_code, -- <ETAX INTEGRATION R12>
1322 PH.pay_when_paid, -- E and C ER
1323 PH.uda_template_id,
1324 PH.uda_template_date,
1325 PH.clm_effective_date,
1326 PH.clm_vendor_offer_number,
1327 PH.clm_award_administrator,
1328 PH.clm_no_signed_copies_to_return,
1329 PH.clm_min_guarantee_award_amt,
1330 PH.clm_min_guar_award_amt_percent,
1331 PH.clm_min_order_amount,
1332 PH.clm_max_order_amount,
1333 PH.clm_amt_synced_to_agreement,
1334 PH.clm_amount_released,
1335 PH.clm_external_idv,
1336 PH.clm_supplier_name,
1337 PH.clm_supplier_site_name,
1338 PH.clm_document_number,
1339 PH.clm_source_document_id,
1340 PH.clm_issuing_office,
1341 PH.clm_cotr_office,
1342 PH.clm_cotr_contact,
1343 PH.clm_priority_code,
1344 PH.clm_standard_form,
1345 PH.clm_document_format,
1346 PH.clm_mod_issuing_office,
1347 PH.ame_approval_id,
1348 PH.ame_transaction_type,
1349 PH.clm_award_type,
1350 PH.clm_contract_officer,
1351 PH.umbrella_program_id,
1352 PH.fon_ref_id,
1353 PH.clm_default_dist_flag,
1354 PH.enable_all_sites, --ER 9824167
1355 PH.clm_contract_finance_code,
1356 PH.clm_special_contract_type,
1357 PH.clm_payment_instr_code -- CLM Phase 4
1358 )
1359 VALUES
1360 (
1361 PHDV.po_header_id,
1362 PHDV.agent_id,
1363 PHDV.type_lookup_code,
1364 PHDV.last_update_date,
1365 PHDV.last_updated_by,
1366 PHDV.segment1,
1367 PHDV.summary_flag,
1368 PHDV.enabled_flag,
1369 PHDV.segment2,
1370 PHDV.segment3,
1371 PHDV.segment4,
1372 PHDV.segment5,
1373 PHDV.start_date_active,
1374 PHDV.end_date_active,
1375 PHDV.last_update_login,
1376 PHDV.creation_date,
1377 PHDV.created_by,
1378 PHDV.vendor_id,
1379 PHDV.vendor_site_id,
1380 PHDV.vendor_contact_id,
1381 PHDV.ship_to_location_id,
1382 PHDV.bill_to_location_id,
1383 PHDV.terms_id,
1384 PHDV.ship_via_lookup_code,
1385 PHDV.fob_lookup_code,
1386 PHDV.freight_terms_lookup_code,
1387 PHDV.status_lookup_code,
1388 PHDV.currency_code,
1389 PHDV.rate_type,
1390 PHDV.rate_date,
1391 PHDV.rate,
1392 PHDV.from_header_id,
1393 PHDV.from_type_lookup_code,
1394 PHDV.start_date,
1395 PHDV.end_date,
1396 PHDV.blanket_total_amount,
1397 PHDV.authorization_status,
1398 PHDV.revision_num,
1399 PHDV.revised_date,
1400 PHDV.approved_flag,
1401 PHDV.approved_date,
1402 PHDV.amount_limit,
1403 PHDV.min_release_amount,
1404 PHDV.note_to_authorizer,
1405 PHDV.note_to_vendor,
1406 PHDV.note_to_receiver,
1407 PHDV.print_count,
1408 PHDV.printed_date,
1409 PHDV.vendor_order_num,
1410 PHDV.confirming_order_flag,
1411 PHDV.comments,
1412 PHDV.reply_date,
1413 PHDV.reply_method_lookup_code,
1414 PHDV.rfq_close_date,
1415 PHDV.quote_type_lookup_code,
1416 PHDV.quotation_class_code,
1417 PHDV.quote_warning_delay_unit,
1418 PHDV.quote_warning_delay,
1419 PHDV.quote_vendor_quote_number,
1420 PHDV.acceptance_required_flag,
1421 PHDV.acceptance_due_date,
1422 PHDV.closed_date,
1423 PHDV.user_hold_flag,
1424 PHDV.approval_required_flag,
1425 PHDV.cancel_flag,
1426 PHDV.firm_status_lookup_code,
1427 PHDV.firm_date,
1428 PHDV.frozen_flag,
1429 PHDV.supply_agreement_flag,
1430 PHDV.edi_processed_flag,
1431 PHDV.edi_processed_status,
1432 PHDV.attribute_category,
1433 PHDV.attribute1,
1434 PHDV.attribute2,
1435 PHDV.attribute3,
1436 PHDV.attribute4,
1437 PHDV.attribute5,
1438 PHDV.attribute6,
1439 PHDV.attribute7,
1440 PHDV.attribute8,
1441 PHDV.attribute9,
1442 PHDV.attribute10,
1443 PHDV.attribute11,
1444 PHDV.attribute12,
1445 PHDV.attribute13,
1446 PHDV.attribute14,
1447 PHDV.attribute15,
1448 PHDV.closed_code,
1449 PHDV.ussgl_transaction_code,
1450 PHDV.government_context,
1451 PHDV.request_id,
1452 PHDV.program_application_id,
1453 PHDV.program_id,
1454 PHDV.program_update_date,
1455 PHDV.org_id,
1456 PHDV.global_attribute_category,
1457 PHDV.global_attribute1,
1458 PHDV.global_attribute2,
1459 PHDV.global_attribute3,
1460 PHDV.global_attribute4,
1461 PHDV.global_attribute5,
1462 PHDV.global_attribute6,
1463 PHDV.global_attribute7,
1464 PHDV.global_attribute8,
1465 PHDV.global_attribute9,
1466 PHDV.global_attribute10,
1467 PHDV.global_attribute11,
1468 PHDV.global_attribute12,
1469 PHDV.global_attribute13,
1470 PHDV.global_attribute14,
1471 PHDV.global_attribute15,
1472 PHDV.global_attribute16,
1473 PHDV.global_attribute17,
1474 PHDV.global_attribute18,
1475 PHDV.global_attribute19,
1476 PHDV.global_attribute20,
1477 PHDV.interface_source_code,
1478 PHDV.reference_num,
1479 PHDV.wf_item_type,
1480 PHDV.wf_item_key,
1481 PHDV.mrc_rate_type,
1482 PHDV.mrc_rate_date,
1483 PHDV.mrc_rate,
1484 PHDV.pcard_id,
1485 PHDV.price_update_tolerance,
1486 PHDV.pay_on_code,
1487 PHDV.xml_flag,
1488 PHDV.xml_send_date,
1489 PHDV.xml_change_send_date,
1490 PHDV.global_agreement_flag,
1491 PHDV.consigned_consumption_flag,
1492 PHDV.cbc_accounting_date,
1493 PHDV.consume_req_demand_flag,
1494 PHDV.change_requested_by,
1495 PHDV.shipping_control,
1496 PHDV.conterms_exist_flag,
1497 PHDV.conterms_articles_upd_date,
1498 PHDV.conterms_deliv_upd_date,
1499 PHDV.encumbrance_required_flag,
1500 PHDV.pending_signature_flag,
1501 PHDV.change_summary,
1502 PHDV.document_creation_method,
1503 PHDV.submit_date,
1504 PHDV.supplier_notif_method,
1505 PHDV.fax,
1506 PHDV.email_address,
1507 PHDV.retro_price_comm_updates_flag,
1508 PHDV.retro_price_apply_updates_flag,
1509 PHDV.update_sourcing_rules_flag,
1510 PHDV.auto_sourcing_flag,
1511 PHDV.created_language,
1512 PHDV.cpa_reference,
1513 PHDV.style_id,
1514 PHDV.tax_attribute_update_code, -- <ETAX INTEGRATION R12>
1515 PHDV.pay_when_paid, -- E and C ER
1516 PHDV.uda_template_id,
1517 PHDV.uda_template_date,
1518 PHDV.clm_effective_date,
1519 PHDV.clm_vendor_offer_number,
1520 PHDV.clm_award_administrator,
1521 PHDV.clm_no_signed_copies_to_return,
1522 PHDV.clm_min_guarantee_award_amt,
1523 PHDV.clm_min_guar_award_amt_percent,
1524 PHDV.clm_min_order_amount,
1525 PHDV.clm_max_order_amount,
1526 PHDV.clm_amt_synced_to_agreement,
1527 PHDV.clm_amount_released,
1528 PHDV.clm_external_idv,
1529 PHDV.clm_supplier_name,
1530 PHDV.clm_supplier_site_name,
1531 PHDV.clm_document_number,
1532 PHDV.clm_source_document_id,
1533 PHDV.clm_issuing_office,
1534 PHDV.clm_cotr_office,
1535 PHDV.clm_cotr_contact,
1536 PHDV.clm_priority_code,
1537 PHDV.clm_standard_form,
1538 PHDV.clm_document_format,
1539 PHDV.clm_mod_issuing_office,
1540 PHDV.ame_approval_id,
1541 PHDV.ame_transaction_type,
1542 PHDV.clm_award_type,
1543 PHDV.clm_contract_officer,
1544 PHDV.umbrella_program_id,
1545 PHDV.fon_ref_id,
1546 PHDV.clm_default_dist_flag,
1547 PHDV.enable_all_sites, --ER 9824167
1548 PHDV.clm_contract_finance_code, -- CLM Phase 4
1549 PHDV.clm_special_contract_type,
1550 PHDV.clm_payment_instr_code -- CLM Phase 4
1551 ) WHERE NVL(PHDV.delete_flag, 'N') <> 'Y';
1552
1553 d_position := 10;
1554 EXCEPTION
1555 WHEN OTHERS THEN
1556 PO_MESSAGE_S.add_exc_msg
1557 ( p_pkg_name => d_pkg_name,
1558 p_procedure_name => d_api_name || '.' || d_position
1559 );
1560 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1561 END merge_changes;
1562
1563 -----------------------------------------------------------------------
1564 --Start of Comments
1565 --Name: lock_draft_record
1566 --Function:
1567 -- Obtain database lock for the record in draft table
1568 --Parameters:
1569 --IN:
1570 --p_po_header_id
1571 -- id for po header record
1572 --p_draft_id
1573 -- draft unique identifier
1574 --RETURN:
1575 --End of Comments
1576 ------------------------------------------------------------------------
1577 PROCEDURE lock_draft_record
1578 ( p_po_header_id IN NUMBER,
1579 p_draft_id IN NUMBER
1580 ) IS
1581
1582 d_api_name CONSTANT VARCHAR2(30) := 'lock_draft_record';
1583 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
1584 d_position NUMBER;
1585
1586 l_dummy NUMBER;
1587
1588 BEGIN
1589 d_position := 0;
1590 IF (PO_LOG.d_proc) THEN
1591 PO_LOG.proc_begin(d_module);
1592 END IF;
1593
1594 SELECT 1
1595 INTO l_dummy
1596 FROM po_headers_draft_all
1597 WHERE po_header_id = p_po_header_id
1598 AND draft_id = p_draft_id
1599 FOR UPDATE NOWAIT;
1600
1601 IF (PO_LOG.d_proc) THEN
1602 PO_LOG.proc_end(d_module);
1603 END IF;
1604
1605 EXCEPTION
1606 WHEN NO_DATA_FOUND THEN
1607 NULL;
1608 END lock_draft_record;
1609
1610 -----------------------------------------------------------------------
1611 --Start of Comments
1612 --Name: lock_transaction_record
1613 --Function:
1614 -- Obtain database lock for the record in transaction table
1615 --Parameters:
1616 --IN:
1617 --p_po_header_id
1618 -- id for po header record
1619 --RETURN:
1620 --End of Comments
1621 ------------------------------------------------------------------------
1622 PROCEDURE lock_transaction_record
1623 ( p_po_header_id IN NUMBER
1624 ) IS
1625
1626 d_api_name CONSTANT VARCHAR2(30) := 'lock_transaction_record';
1627 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
1628 d_position NUMBER;
1629
1630 l_dummy NUMBER;
1631
1632 BEGIN
1633 d_position := 0;
1634 IF (PO_LOG.d_proc) THEN
1635 PO_LOG.proc_begin(d_module);
1636 END IF;
1637
1638 SELECT 1
1639 INTO l_dummy
1640 FROM po_headers_all
1641 WHERE po_header_id = p_po_header_id
1642 FOR UPDATE NOWAIT;
1643
1644 IF (PO_LOG.d_proc) THEN
1645 PO_LOG.proc_end(d_module);
1646 END IF;
1647
1648 EXCEPTION
1649 WHEN NO_DATA_FOUND THEN
1650 NULL;
1651 END lock_transaction_record;
1652
1653 END PO_HEADERS_DRAFT_PKG;
1654