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