[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.8 2006/09/28 23:04:15 bao noship $ */
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
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 DELETE FROM po_lines_draft_all
49 WHERE draft_id = p_draft_id
50 AND po_line_id = NVL(p_po_line_id, po_line_id);
51
52 d_position := 10;
53 EXCEPTION
54 WHEN OTHERS THEN
55 PO_MESSAGE_S.add_exc_msg
56 ( p_pkg_name => d_pkg_name,
57 p_procedure_name => d_api_name || '.' || d_position
58 );
59 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
60 END delete_rows;
61
62 -----------------------------------------------------------------------
63 --Start of Comments
64 --Name: sync_draft_from_txn
65 --Pre-reqs: None
66 --Modifies:
67 --Locks:
68 -- None
69 --Function:
70 -- Copy data from transaction table to draft table, if the corresponding
71 -- record in draft table does not exist. It also sets the delete flag of
72 -- the draft record according to the parameter.
73 --Parameters:
74 --IN:
75 --p_po_line_id_tbl
76 -- table of po header unique identifier
77 --p_draft_id_tbl
78 -- table of draft ids this sync up will be done for
79 --p_delete_flag_tbl
80 -- table fo flags to indicate whether the draft record should be maked as
81 -- "to be deleted"
82 --IN OUT:
83 --OUT:
84 --x_record_already_exist_tbl
85 -- Returns whether the record was already in draft table or not
86 --Returns:
87 --Notes:
88 --Testing:
89 --End of Comments
90 ------------------------------------------------------------------------
91 PROCEDURE sync_draft_from_txn
92 ( p_po_line_id_tbl IN PO_TBL_NUMBER,
93 p_draft_id_tbl IN PO_TBL_NUMBER,
94 p_delete_flag_tbl IN PO_TBL_VARCHAR1,
95 x_record_already_exist_tbl OUT NOCOPY PO_TBL_VARCHAR1
96 ) IS
97
98 d_api_name CONSTANT VARCHAR2(30) := 'sync_draft_from_txn';
99 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
100 d_position NUMBER;
101
102 l_distinct_id_list DBMS_SQL.NUMBER_TABLE;
103 l_duplicate_flag_tbl PO_TBL_VARCHAR1 := PO_TBL_VARCHAR1();
104
105 BEGIN
106 d_position := 0;
107 IF (PO_LOG.d_proc) THEN
108 PO_LOG.proc_begin(d_module);
109 END IF;
110
111 x_record_already_exist_tbl :=
112 PO_LINES_DRAFT_PVT.draft_changes_exist
113 ( p_draft_id_tbl => p_draft_id_tbl,
114 p_po_line_id_tbl => p_po_line_id_tbl
115 );
116
117 -- bug5471513 START
118 -- If there're duplicate entries in the id table,
119 -- we do not want to insert multiple entries
120 -- Created an associative array to store what id has appeared.
121 l_duplicate_flag_tbl.EXTEND(p_po_line_id_tbl.COUNT);
122
123 FOR i IN 1..p_po_line_id_tbl.COUNT LOOP
124 IF (x_record_already_exist_tbl(i) = FND_API.G_FALSE) THEN
125
126 IF (l_distinct_id_list.EXISTS(p_po_line_id_tbl(i))) THEN
127
128 l_duplicate_flag_tbl(i) := FND_API.G_TRUE;
129 ELSE
130 l_duplicate_flag_tbl(i) := FND_API.G_FALSE;
131
132 l_distinct_id_list(p_po_line_id_tbl(i)) := 1;
133 END IF;
134
135 ELSE
136
137 l_duplicate_flag_tbl(i) := NULL;
138
139 END IF;
140 END LOOP;
141 -- bug5471513 END
142
143
144 d_position := 10;
145 IF (PO_LOG.d_stmt) THEN
146 PO_LOG.stmt(d_module, d_position, 'transfer records from txn to dft');
147 END IF;
148
149 FORALL i IN 1..p_po_line_id_tbl.COUNT
150 INSERT INTO po_lines_draft_all
151 (
152 draft_id,
153 delete_flag,
154 change_accepted_flag,
155 po_line_id,
156 last_update_date,
157 last_updated_by,
158 po_header_id,
159 line_type_id,
160 line_num,
161 last_update_login,
162 creation_date,
163 created_by,
164 item_id,
165 item_revision,
166 category_id,
167 item_description,
168 unit_meas_lookup_code,
169 quantity_committed,
170 committed_amount,
171 allow_price_override_flag,
172 not_to_exceed_price,
173 list_price_per_unit,
174 unit_price,
175 quantity,
176 un_number_id,
177 hazard_class_id,
178 note_to_vendor,
179 from_header_id,
180 from_line_id,
181 from_line_location_id,
182 min_order_quantity,
183 max_order_quantity,
184 qty_rcv_tolerance,
185 over_tolerance_error_flag,
186 market_price,
187 unordered_flag,
188 closed_flag,
189 user_hold_flag,
190 cancel_flag,
191 cancelled_by,
192 cancel_date,
193 cancel_reason,
194 firm_status_lookup_code,
195 firm_date,
196 vendor_product_num,
197 contract_num,
198 taxable_flag,
199 tax_name,
200 type_1099,
201 capital_expense_flag,
202 negotiated_by_preparer_flag,
203 attribute_category,
204 attribute1,
205 attribute2,
206 attribute3,
207 attribute4,
208 attribute5,
209 attribute6,
210 attribute7,
211 attribute8,
212 attribute9,
213 attribute10,
214 reference_num,
215 attribute11,
216 attribute12,
217 attribute13,
218 attribute14,
219 attribute15,
220 min_release_amount,
221 price_type_lookup_code,
222 closed_code,
223 price_break_lookup_code,
224 ussgl_transaction_code,
225 government_context,
226 request_id,
227 program_application_id,
228 program_id,
229 program_update_date,
230 closed_date,
231 closed_reason,
232 closed_by,
233 transaction_reason_code,
234 org_id,
235 qc_grade,
236 base_uom,
237 base_qty,
238 secondary_uom,
239 secondary_qty,
240 global_attribute_category,
241 global_attribute1,
242 global_attribute2,
243 global_attribute3,
244 global_attribute4,
245 global_attribute5,
246 global_attribute6,
247 global_attribute7,
248 global_attribute8,
249 global_attribute9,
250 global_attribute10,
251 global_attribute11,
252 global_attribute12,
253 global_attribute13,
254 global_attribute14,
255 global_attribute15,
256 global_attribute16,
257 global_attribute17,
258 global_attribute18,
259 global_attribute19,
260 global_attribute20,
261 line_reference_num,
262 project_id,
263 task_id,
264 expiration_date,
265 tax_code_id,
266 oke_contract_header_id,
267 oke_contract_version_id,
268 secondary_quantity,
269 secondary_unit_of_measure,
270 preferred_grade,
271 auction_header_id,
272 auction_display_number,
273 auction_line_number,
274 bid_number,
275 bid_line_number,
276 retroactive_date,
277 supplier_ref_number,
278 contract_id,
279 start_date,
280 amount,
281 job_id,
282 contractor_first_name,
283 contractor_last_name,
284 order_type_lookup_code,
285 purchase_basis,
286 matching_basis,
287 svc_amount_notif_sent,
288 svc_completion_notif_sent,
289 base_unit_price,
290 manual_price_change_flag,
291 -- <Complex Work R12 Start>
292 retainage_rate,
293 max_retainage_amount,
294 progress_payment_rate,
295 recoupment_rate,
296 -- <Complex Work R12 End>
297 catalog_name,
298 supplier_part_auxid,
299 ip_category_id,
300 tax_attribute_update_code -- <ETAX R12>
301 )
302 SELECT
303 p_draft_id_tbl(i),
304 p_delete_flag_tbl(i),
305 NULL,
306 po_line_id,
307 last_update_date,
308 last_updated_by,
309 po_header_id,
310 line_type_id,
311 line_num,
312 last_update_login,
313 creation_date,
314 created_by,
315 item_id,
316 item_revision,
317 category_id,
318 item_description,
319 unit_meas_lookup_code,
320 quantity_committed,
321 committed_amount,
322 allow_price_override_flag,
323 not_to_exceed_price,
324 list_price_per_unit,
325 unit_price,
326 quantity,
327 un_number_id,
328 hazard_class_id,
329 note_to_vendor,
330 from_header_id,
331 from_line_id,
332 from_line_location_id,
333 min_order_quantity,
334 max_order_quantity,
335 qty_rcv_tolerance,
336 over_tolerance_error_flag,
337 market_price,
338 unordered_flag,
339 closed_flag,
340 user_hold_flag,
341 cancel_flag,
342 cancelled_by,
343 cancel_date,
344 cancel_reason,
345 firm_status_lookup_code,
346 firm_date,
347 vendor_product_num,
348 contract_num,
349 taxable_flag,
350 tax_name,
351 type_1099,
352 capital_expense_flag,
353 negotiated_by_preparer_flag,
354 attribute_category,
355 attribute1,
356 attribute2,
357 attribute3,
358 attribute4,
359 attribute5,
360 attribute6,
361 attribute7,
362 attribute8,
363 attribute9,
364 attribute10,
365 reference_num,
366 attribute11,
367 attribute12,
368 attribute13,
369 attribute14,
370 attribute15,
371 min_release_amount,
372 price_type_lookup_code,
373 closed_code,
374 price_break_lookup_code,
375 ussgl_transaction_code,
376 government_context,
377 request_id,
378 program_application_id,
379 program_id,
380 program_update_date,
381 closed_date,
382 closed_reason,
383 closed_by,
384 transaction_reason_code,
385 org_id,
386 qc_grade,
387 base_uom,
388 base_qty,
389 secondary_uom,
390 secondary_qty,
391 global_attribute_category,
392 global_attribute1,
393 global_attribute2,
394 global_attribute3,
395 global_attribute4,
396 global_attribute5,
397 global_attribute6,
398 global_attribute7,
399 global_attribute8,
400 global_attribute9,
401 global_attribute10,
402 global_attribute11,
403 global_attribute12,
404 global_attribute13,
405 global_attribute14,
406 global_attribute15,
407 global_attribute16,
408 global_attribute17,
409 global_attribute18,
410 global_attribute19,
411 global_attribute20,
412 line_reference_num,
413 project_id,
414 task_id,
415 expiration_date,
416 tax_code_id,
417 oke_contract_header_id,
418 oke_contract_version_id,
419 secondary_quantity,
420 secondary_unit_of_measure,
421 preferred_grade,
422 auction_header_id,
423 auction_display_number,
424 auction_line_number,
425 bid_number,
426 bid_line_number,
427 retroactive_date,
428 supplier_ref_number,
432 job_id,
429 contract_id,
430 start_date,
431 amount,
433 contractor_first_name,
434 contractor_last_name,
435 order_type_lookup_code,
436 purchase_basis,
437 matching_basis,
438 svc_amount_notif_sent,
439 svc_completion_notif_sent,
440 base_unit_price,
441 manual_price_change_flag,
442 -- <Complex Work R12 Start>
443 retainage_rate,
444 max_retainage_amount,
445 progress_payment_rate,
446 recoupment_rate,
447 -- <Complex Work R12 End>
448 catalog_name,
449 supplier_part_auxid,
450 ip_category_id,
451 tax_attribute_update_code -- <ETAX R12>
452 FROM po_lines_all
453 WHERE po_line_id = p_po_line_id_tbl(i)
454 AND x_record_already_exist_tbl(i) = FND_API.G_FALSE
455 AND l_duplicate_flag_tbl(i) = FND_API.G_FALSE;
456
457 d_position := 20;
458 IF (PO_LOG.d_stmt) THEN
459 PO_LOG.stmt(d_module, d_position, 'transfer count = ' || SQL%ROWCOUNT);
460 END IF;
461
462 FORALL i IN 1..p_po_line_id_tbl.COUNT
463 UPDATE po_lines_draft_all
464 SET delete_flag = p_delete_flag_tbl(i)
465 WHERE po_line_id = p_po_line_id_tbl(i)
466 AND draft_id = p_draft_id_tbl(i)
467 AND NVL(delete_flag, 'N') <> 'Y' -- bug5570989
468 AND x_record_already_exist_tbl(i) = FND_API.G_TRUE;
469
470 d_position := 30;
471
472 IF (PO_LOG.d_stmt) THEN
473 PO_LOG.stmt(d_module, d_position, 'update draft records that are already' ||
474 ' in draft table. Count = ' || SQL%ROWCOUNT);
475 END IF;
476
477 d_position := 40;
478
479 IF (PO_LOG.d_proc) THEN
480 PO_LOG.proc_end(d_module);
481 END IF;
482
483 EXCEPTION
484 WHEN OTHERS THEN
485 PO_MESSAGE_S.add_exc_msg
486 ( p_pkg_name => d_pkg_name,
487 p_procedure_name => d_api_name || '.' || d_position
488 );
489 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
490 END sync_draft_from_txn;
491
492
493
494 -----------------------------------------------------------------------
495 --Start of Comments
496 --Name: sync_draft_from_txn
497 --Pre-reqs: None
498 --Modifies:
499 --Locks:
500 -- None
501 --Function:
502 -- Same functionality as the bulk version of this procedure
503 --Parameters:
504 --IN:
505 --p_po_line_id
506 -- po line unique identifier
507 --p_draft_id
508 -- the draft this sync up will be done for
509 --p_delete_flag
510 -- flag to indicate whether the draft record should be maked as "to be
511 -- deleted"
512 --IN OUT:
513 --OUT:
514 --x_record_already_exist
515 -- Returns whether the record was already in draft table or not
516 --Returns:
517 --Notes:
518 --Testing:
519 --End of Comments
520 ------------------------------------------------------------------------
521 PROCEDURE sync_draft_from_txn
522 ( p_po_line_id IN NUMBER,
523 p_draft_id IN NUMBER,
524 p_delete_flag IN VARCHAR2,
525 x_record_already_exist OUT NOCOPY VARCHAR2
526 ) IS
527
528 d_api_name CONSTANT VARCHAR2(30) := 'sync_draft_from_txn';
529 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
530 d_position NUMBER;
531
532 l_record_already_exist_tbl PO_TBL_VARCHAR1;
533
534 BEGIN
535 d_position := 0;
536 IF (PO_LOG.d_proc) THEN
537 PO_LOG.proc_begin(d_module);
538 PO_LOG.proc_begin(d_module, 'p_po_line_id', p_po_line_id);
539 END IF;
540
541 sync_draft_from_txn
542 ( p_po_line_id_tbl => PO_TBL_NUMBER(p_po_line_id),
543 p_draft_id_tbl => PO_TBL_NUMBER(p_draft_id),
544 p_delete_flag_tbl => PO_TBL_VARCHAR1(p_delete_flag),
545 x_record_already_exist_tbl => l_record_already_exist_tbl
546 );
547
548 x_record_already_exist := l_record_already_exist_tbl(1);
549
550 d_position := 10;
551 IF (PO_LOG.d_proc) THEN
552 PO_LOG.proc_end(d_module);
553 PO_LOG.proc_end(d_module, 'x_record_already_exist', x_record_already_exist);
554 END IF;
555
556 EXCEPTION
557 WHEN OTHERS THEN
558 PO_MESSAGE_S.add_exc_msg
559 ( p_pkg_name => d_pkg_name,
560 p_procedure_name => d_api_name || '.' || d_position
561 );
562 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
563 END sync_draft_from_txn;
564
565 -----------------------------------------------------------------------
566 --Start of Comments
567 --Name: merge_changes
568 --Pre-reqs: None
569 --Modifies:
570 --Locks:
571 -- None
572 --Function:
573 -- Merge the records in draft table to transaction table
574 -- Either insert, update or delete will be performed on top of transaction
575 -- table, depending on the delete_flag on the draft record and whether the
576 -- record already exists in transaction table
577 --
578 --Parameters:
579 --IN:
580 --p_draft_id
581 -- draft unique identifier
582 --IN OUT:
583 --OUT:
584 --Returns:
585 --Notes:
586 --Testing:
587 --End of Comments
588 ------------------------------------------------------------------------
589 PROCEDURE merge_changes
590 ( p_draft_id IN NUMBER
591 ) IS
592
593 d_api_name CONSTANT VARCHAR2(30) := 'merge_changes';
594 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
595 d_position NUMBER;
596
597 BEGIN
598 d_position := 0;
599 IF (PO_LOG.d_proc) THEN
600 PO_LOG.proc_begin(d_module);
601 END IF;
602
603 -- Since putting DELETE within MERGE statement is causing database
607
604 -- to thrown internal error, for now we just separate the DELETE statement.
605 -- Once this is fixed we'll move the delete statement back to the merge
606 -- statement
608 -- bug5187544
609 -- Delete only records that have not been rejected
610
611 DELETE FROM po_lines_all PL
612 WHERE PL.po_line_id IN
613 ( SELECT PLD.po_line_id
614 FROM po_lines_draft_all PLD
615 WHERE PLD.draft_id = p_draft_id
616 AND PLD.delete_flag = 'Y'
617 AND NVL(PLD.change_accepted_flag, 'Y') = 'Y' );
618
619 d_position := 10;
620
621 -- Merge PO Line changes
622 -- For update case, the following columns will be skipped:
623 --PL.po_line_id
624 --PL.creation_date
625 --PL.created_by
626 --PL.tax_name
627 --PL.request_id
628 --PL.program_application_id
629 --PL.program_id
630 --PL.program_update_date
631 --PL.base_uom
632 --PL.base_qty
633 --PL.project_id
634 --PL.task_id
635 --PL.auction_header_id
636 --PL.auction_display_number
637 --PL.auction_line_number
638 --PL.bid_number
639 --PL.bid_line_number
640 --PL.svc_amount_notif_sent
641 --PL.svc_completion_notif_sent
642 MERGE INTO po_lines_all PL
643 USING (
644 SELECT
645 PLD.draft_id,
646 PLD.delete_flag,
647 PLD.change_accepted_flag,
648 PLD.po_line_id,
649 PLD.last_update_date,
650 PLD.last_updated_by,
651 PLD.po_header_id,
652 PLD.line_type_id,
653 PLD.line_num,
654 PLD.last_update_login,
655 PLD.creation_date,
656 PLD.created_by,
657 PLD.item_id,
658 PLD.item_revision,
659 PLD.category_id,
660 PLD.item_description,
661 PLD.unit_meas_lookup_code,
662 PLD.quantity_committed,
663 PLD.committed_amount,
664 PLD.allow_price_override_flag,
665 PLD.not_to_exceed_price,
666 PLD.list_price_per_unit,
667 PLD.unit_price,
668 PLD.quantity,
669 PLD.un_number_id,
670 PLD.hazard_class_id,
671 PLD.note_to_vendor,
672 PLD.from_header_id,
673 PLD.from_line_id,
674 PLD.from_line_location_id,
675 PLD.min_order_quantity,
676 PLD.max_order_quantity,
677 PLD.qty_rcv_tolerance,
678 PLD.over_tolerance_error_flag,
679 PLD.market_price,
680 PLD.unordered_flag,
681 PLD.closed_flag,
682 PLD.user_hold_flag,
683 PLD.cancel_flag,
684 PLD.cancelled_by,
685 PLD.cancel_date,
686 PLD.cancel_reason,
687 PLD.firm_status_lookup_code,
688 PLD.firm_date,
689 PLD.vendor_product_num,
690 PLD.contract_num,
691 PLD.taxable_flag,
692 PLD.tax_name,
693 PLD.type_1099,
694 PLD.capital_expense_flag,
695 PLD.negotiated_by_preparer_flag,
696 PLD.attribute_category,
697 PLD.attribute1,
698 PLD.attribute2,
699 PLD.attribute3,
700 PLD.attribute4,
701 PLD.attribute5,
702 PLD.attribute6,
703 PLD.attribute7,
704 PLD.attribute8,
705 PLD.attribute9,
706 PLD.attribute10,
707 PLD.reference_num,
708 PLD.attribute11,
709 PLD.attribute12,
710 PLD.attribute13,
711 PLD.attribute14,
712 PLD.attribute15,
713 PLD.min_release_amount,
714 PLD.price_type_lookup_code,
715 PLD.closed_code,
716 PLD.price_break_lookup_code,
717 PLD.ussgl_transaction_code,
718 PLD.government_context,
719 PLD.request_id,
720 PLD.program_application_id,
721 PLD.program_id,
722 PLD.program_update_date,
723 PLD.closed_date,
724 PLD.closed_reason,
725 PLD.closed_by,
726 PLD.transaction_reason_code,
727 PLD.org_id,
728 PLD.qc_grade,
729 PLD.base_uom,
730 PLD.base_qty,
731 PLD.secondary_uom,
732 PLD.secondary_qty,
733 PLD.global_attribute_category,
734 PLD.global_attribute1,
735 PLD.global_attribute2,
736 PLD.global_attribute3,
737 PLD.global_attribute4,
738 PLD.global_attribute5,
739 PLD.global_attribute6,
740 PLD.global_attribute7,
741 PLD.global_attribute8,
742 PLD.global_attribute9,
743 PLD.global_attribute10,
744 PLD.global_attribute11,
745 PLD.global_attribute12,
746 PLD.global_attribute13,
747 PLD.global_attribute14,
748 PLD.global_attribute15,
749 PLD.global_attribute16,
750 PLD.global_attribute17,
751 PLD.global_attribute18,
752 PLD.global_attribute19,
753 PLD.global_attribute20,
754 PLD.line_reference_num,
755 PLD.project_id,
756 PLD.task_id,
757 PLD.expiration_date,
758 PLD.tax_code_id,
759 PLD.oke_contract_header_id,
760 PLD.oke_contract_version_id,
761 PLD.secondary_quantity,
762 PLD.secondary_unit_of_measure,
763 PLD.preferred_grade,
764 PLD.auction_header_id,
765 PLD.auction_display_number,
766 PLD.auction_line_number,
767 PLD.bid_number,
768 PLD.bid_line_number,
769 PLD.retroactive_date,
770 PLD.supplier_ref_number,
771 PLD.contract_id,
772 PLD.start_date,
773 PLD.amount,
774 PLD.job_id,
775 PLD.contractor_first_name,
776 PLD.contractor_last_name,
777 PLD.order_type_lookup_code,
781 PLD.svc_completion_notif_sent,
778 PLD.purchase_basis,
779 PLD.matching_basis,
780 PLD.svc_amount_notif_sent,
782 PLD.base_unit_price,
783 PLD.manual_price_change_flag,
784 -- <Complex Work R12 Start>
785 PLD.retainage_rate,
786 PLD.max_retainage_amount,
787 PLD.progress_payment_rate,
788 PLD.recoupment_rate,
789 -- <Complex Work R12 End>
790 PLD.catalog_name,
791 PLD.supplier_part_auxid,
792 PLD.ip_category_id,
793 PLD.tax_attribute_update_code -- <ETAX R12>
794 FROM po_lines_draft_all PLD
795 WHERE PLD.draft_id = p_draft_id
796 AND NVL(PLD.change_accepted_flag, 'Y') = 'Y'
797 ) PLDV
798 ON (PL.po_line_id = PLDV.po_line_id)
799 WHEN MATCHED THEN
800 UPDATE
801 SET
802 PL.last_update_date = PLDV.last_update_date,
803 PL.last_updated_by = PLDV.last_updated_by,
804 PL.po_header_id = PLDV.po_header_id,
805 PL.line_type_id = PLDV.line_type_id,
806 PL.line_num = PLDV.line_num,
807 PL.last_update_login = PLDV.last_update_login,
808 PL.item_id = PLDV.item_id,
809 PL.item_revision = PLDV.item_revision,
810 PL.category_id = PLDV.category_id,
811 PL.item_description = PLDV.item_description,
812 PL.unit_meas_lookup_code = PLDV.unit_meas_lookup_code,
813 PL.quantity_committed = PLDV.quantity_committed,
814 PL.committed_amount = PLDV.committed_amount,
815 PL.allow_price_override_flag = PLDV.allow_price_override_flag,
816 PL.not_to_exceed_price = PLDV.not_to_exceed_price,
817 PL.list_price_per_unit = PLDV.list_price_per_unit,
818 PL.unit_price = PLDV.unit_price,
819 PL.quantity = PLDV.quantity,
820 PL.un_number_id = PLDV.un_number_id,
821 PL.hazard_class_id = PLDV.hazard_class_id,
822 PL.note_to_vendor = PLDV.note_to_vendor,
823 PL.from_header_id = PLDV.from_header_id,
824 PL.from_line_id = PLDV.from_line_id,
825 PL.from_line_location_id = PLDV.from_line_location_id,
826 PL.min_order_quantity = PLDV.min_order_quantity,
827 PL.max_order_quantity = PLDV.max_order_quantity,
828 PL.qty_rcv_tolerance = PLDV.qty_rcv_tolerance,
829 PL.over_tolerance_error_flag = PLDV.over_tolerance_error_flag,
830 PL.market_price = PLDV.market_price,
831 PL.unordered_flag = PLDV.unordered_flag,
832 PL.closed_flag = PLDV.closed_flag,
833 PL.user_hold_flag = PLDV.user_hold_flag,
834 PL.cancel_flag = PLDV.cancel_flag,
835 PL.cancelled_by = PLDV.cancelled_by,
836 PL.cancel_date = PLDV.cancel_date,
837 PL.cancel_reason = PLDV.cancel_reason,
838 PL.firm_status_lookup_code = PLDV.firm_status_lookup_code,
839 PL.firm_date = PLDV.firm_date,
840 PL.vendor_product_num = PLDV.vendor_product_num,
841 PL.contract_num = PLDV.contract_num,
842 PL.taxable_flag = PLDV.taxable_flag,
843 PL.type_1099 = PLDV.type_1099,
844 PL.capital_expense_flag = PLDV.capital_expense_flag,
845 PL.negotiated_by_preparer_flag = PLDV.negotiated_by_preparer_flag,
846 PL.attribute_category = PLDV.attribute_category,
847 PL.attribute1 = PLDV.attribute1,
848 PL.attribute2 = PLDV.attribute2,
849 PL.attribute3 = PLDV.attribute3,
850 PL.attribute4 = PLDV.attribute4,
851 PL.attribute5 = PLDV.attribute5,
852 PL.attribute6 = PLDV.attribute6,
853 PL.attribute7 = PLDV.attribute7,
854 PL.attribute8 = PLDV.attribute8,
855 PL.attribute9 = PLDV.attribute9,
856 PL.attribute10 = PLDV.attribute10,
857 PL.reference_num = PLDV.reference_num,
858 PL.attribute11 = PLDV.attribute11,
859 PL.attribute12 = PLDV.attribute12,
860 PL.attribute13 = PLDV.attribute13,
861 PL.attribute14 = PLDV.attribute14,
862 PL.attribute15 = PLDV.attribute15,
863 PL.min_release_amount = PLDV.min_release_amount,
864 PL.price_type_lookup_code = PLDV.price_type_lookup_code,
865 PL.closed_code = PLDV.closed_code,
866 PL.price_break_lookup_code = PLDV.price_break_lookup_code,
867 PL.ussgl_transaction_code = PLDV.ussgl_transaction_code,
868 PL.government_context = PLDV.government_context,
869 PL.closed_date = PLDV.closed_date,
870 PL.closed_reason = PLDV.closed_reason,
871 PL.closed_by = PLDV.closed_by,
872 PL.transaction_reason_code = PLDV.transaction_reason_code,
873 PL.org_id = PLDV.org_id,
874 PL.qc_grade = PLDV.qc_grade,
875 PL.secondary_uom = PLDV.secondary_uom,
876 PL.secondary_qty = PLDV.secondary_qty,
877 PL.global_attribute_category = PLDV.global_attribute_category,
878 PL.global_attribute1 = PLDV.global_attribute1,
879 PL.global_attribute2 = PLDV.global_attribute2,
880 PL.global_attribute3 = PLDV.global_attribute3,
881 PL.global_attribute4 = PLDV.global_attribute4,
882 PL.global_attribute5 = PLDV.global_attribute5,
883 PL.global_attribute6 = PLDV.global_attribute6,
884 PL.global_attribute7 = PLDV.global_attribute7,
885 PL.global_attribute8 = PLDV.global_attribute8,
886 PL.global_attribute9 = PLDV.global_attribute9,
887 PL.global_attribute10 = PLDV.global_attribute10,
888 PL.global_attribute11 = PLDV.global_attribute11,
889 PL.global_attribute12 = PLDV.global_attribute12,
890 PL.global_attribute13 = PLDV.global_attribute13,
891 PL.global_attribute14 = PLDV.global_attribute14,
892 PL.global_attribute15 = PLDV.global_attribute15,
893 PL.global_attribute16 = PLDV.global_attribute16,
894 PL.global_attribute17 = PLDV.global_attribute17,
895 PL.global_attribute18 = PLDV.global_attribute18,
896 PL.global_attribute19 = PLDV.global_attribute19,
897 PL.global_attribute20 = PLDV.global_attribute20,
898 PL.line_reference_num = PLDV.line_reference_num,
902 PL.oke_contract_version_id = PLDV.oke_contract_version_id,
899 PL.expiration_date = PLDV.expiration_date,
900 PL.tax_code_id = PLDV.tax_code_id,
901 PL.oke_contract_header_id = PLDV.oke_contract_header_id,
903 PL.secondary_quantity = PLDV.secondary_quantity,
904 PL.secondary_unit_of_measure = PLDV.secondary_unit_of_measure,
905 PL.preferred_grade = PLDV.preferred_grade,
906 PL.retroactive_date = PLDV.retroactive_date,
907 PL.supplier_ref_number = PLDV.supplier_ref_number,
908 PL.contract_id = PLDV.contract_id,
909 PL.start_date = PLDV.start_date,
910 PL.amount = PLDV.amount,
911 PL.job_id = PLDV.job_id,
912 PL.contractor_first_name = PLDV.contractor_first_name,
913 PL.contractor_last_name = PLDV.contractor_last_name,
914 PL.order_type_lookup_code = PLDV.order_type_lookup_code,
915 PL.purchase_basis = PLDV.purchase_basis,
916 PL.matching_basis = PLDV.matching_basis,
917 PL.base_unit_price = PLDV.base_unit_price,
918 PL.manual_price_change_flag = PLDV.manual_price_change_flag,
919 -- <Complex Work R12 Start>
920 PL.retainage_rate = PLDV.retainage_rate,
921 PL.max_retainage_amount = PLDV.max_retainage_amount,
922 PL.progress_payment_rate = PLDV.progress_payment_rate,
923 PL.recoupment_rate = PLDV.recoupment_rate,
924 -- <Complex Work R12 End>
925 PL.catalog_name = PLDV.catalog_name,
926 PL.supplier_part_auxid = PLDV.supplier_part_auxid,
927 PL.ip_category_id = PLDV.ip_category_id,
928 PL.tax_attribute_update_code = PLDV.tax_attribute_update_code -- <ETAX R12>
929 -- DELETE WHERE PLDV.delete_flag = 'Y'
930 WHEN NOT MATCHED THEN
931 INSERT
932 (
933 PL.po_line_id,
934 PL.last_update_date,
935 PL.last_updated_by,
936 PL.po_header_id,
937 PL.line_type_id,
938 PL.line_num,
939 PL.last_update_login,
940 PL.creation_date,
941 PL.created_by,
942 PL.item_id,
943 PL.item_revision,
944 PL.category_id,
945 PL.item_description,
946 PL.unit_meas_lookup_code,
947 PL.quantity_committed,
948 PL.committed_amount,
949 PL.allow_price_override_flag,
950 PL.not_to_exceed_price,
951 PL.list_price_per_unit,
952 PL.unit_price,
953 PL.quantity,
954 PL.un_number_id,
955 PL.hazard_class_id,
956 PL.note_to_vendor,
957 PL.from_header_id,
958 PL.from_line_id,
959 PL.from_line_location_id,
960 PL.min_order_quantity,
961 PL.max_order_quantity,
962 PL.qty_rcv_tolerance,
963 PL.over_tolerance_error_flag,
964 PL.market_price,
965 PL.unordered_flag,
966 PL.closed_flag,
967 PL.user_hold_flag,
968 PL.cancel_flag,
969 PL.cancelled_by,
970 PL.cancel_date,
971 PL.cancel_reason,
972 PL.firm_status_lookup_code,
973 PL.firm_date,
974 PL.vendor_product_num,
975 PL.contract_num,
976 PL.taxable_flag,
977 PL.tax_name,
978 PL.type_1099,
979 PL.capital_expense_flag,
980 PL.negotiated_by_preparer_flag,
981 PL.attribute_category,
982 PL.attribute1,
983 PL.attribute2,
984 PL.attribute3,
985 PL.attribute4,
986 PL.attribute5,
987 PL.attribute6,
988 PL.attribute7,
989 PL.attribute8,
990 PL.attribute9,
991 PL.attribute10,
992 PL.reference_num,
993 PL.attribute11,
994 PL.attribute12,
995 PL.attribute13,
996 PL.attribute14,
997 PL.attribute15,
998 PL.min_release_amount,
999 PL.price_type_lookup_code,
1000 PL.closed_code,
1001 PL.price_break_lookup_code,
1002 PL.ussgl_transaction_code,
1003 PL.government_context,
1004 PL.request_id,
1005 PL.program_application_id,
1006 PL.program_id,
1007 PL.program_update_date,
1008 PL.closed_date,
1009 PL.closed_reason,
1010 PL.closed_by,
1011 PL.transaction_reason_code,
1012 PL.org_id,
1013 PL.qc_grade,
1014 PL.base_uom,
1015 PL.base_qty,
1016 PL.secondary_uom,
1017 PL.secondary_qty,
1018 PL.global_attribute_category,
1019 PL.global_attribute1,
1020 PL.global_attribute2,
1021 PL.global_attribute3,
1022 PL.global_attribute4,
1023 PL.global_attribute5,
1024 PL.global_attribute6,
1025 PL.global_attribute7,
1026 PL.global_attribute8,
1027 PL.global_attribute9,
1028 PL.global_attribute10,
1029 PL.global_attribute11,
1030 PL.global_attribute12,
1031 PL.global_attribute13,
1032 PL.global_attribute14,
1033 PL.global_attribute15,
1034 PL.global_attribute16,
1035 PL.global_attribute17,
1036 PL.global_attribute18,
1037 PL.global_attribute19,
1038 PL.global_attribute20,
1039 PL.line_reference_num,
1040 PL.project_id,
1041 PL.task_id,
1042 PL.expiration_date,
1043 PL.tax_code_id,
1044 PL.oke_contract_header_id,
1045 PL.oke_contract_version_id,
1046 PL.secondary_quantity,
1047 PL.secondary_unit_of_measure,
1048 PL.preferred_grade,
1049 PL.auction_header_id,
1050 PL.auction_display_number,
1051 PL.auction_line_number,
1052 PL.bid_number,
1053 PL.bid_line_number,
1054 PL.retroactive_date,
1055 PL.supplier_ref_number,
1056 PL.contract_id,
1057 PL.start_date,
1058 PL.amount,
1062 PL.order_type_lookup_code,
1059 PL.job_id,
1060 PL.contractor_first_name,
1061 PL.contractor_last_name,
1063 PL.purchase_basis,
1064 PL.matching_basis,
1065 PL.svc_amount_notif_sent,
1066 PL.svc_completion_notif_sent,
1067 PL.base_unit_price,
1068 PL.manual_price_change_flag,
1069 -- <Complex Work R12 Start>
1070 PL.retainage_rate,
1071 PL.max_retainage_amount,
1072 PL.progress_payment_rate,
1073 PL.recoupment_rate,
1074 -- <Complex Work R12 End>
1075 PL.catalog_name,
1076 PL.supplier_part_auxid,
1077 PL.ip_category_id,
1078 PL.tax_attribute_update_code -- <ETAX R12>
1079 )
1080 VALUES
1081 (
1082 PLDV.po_line_id,
1083 PLDV.last_update_date,
1084 PLDV.last_updated_by,
1085 PLDV.po_header_id,
1086 PLDV.line_type_id,
1087 PLDV.line_num,
1088 PLDV.last_update_login,
1089 PLDV.creation_date,
1090 PLDV.created_by,
1091 PLDV.item_id,
1092 PLDV.item_revision,
1093 PLDV.category_id,
1094 PLDV.item_description,
1095 PLDV.unit_meas_lookup_code,
1096 PLDV.quantity_committed,
1097 PLDV.committed_amount,
1098 PLDV.allow_price_override_flag,
1099 PLDV.not_to_exceed_price,
1100 PLDV.list_price_per_unit,
1101 PLDV.unit_price,
1102 PLDV.quantity,
1103 PLDV.un_number_id,
1104 PLDV.hazard_class_id,
1105 PLDV.note_to_vendor,
1106 PLDV.from_header_id,
1107 PLDV.from_line_id,
1108 PLDV.from_line_location_id,
1109 PLDV.min_order_quantity,
1110 PLDV.max_order_quantity,
1111 PLDV.qty_rcv_tolerance,
1112 PLDV.over_tolerance_error_flag,
1113 PLDV.market_price,
1114 PLDV.unordered_flag,
1115 PLDV.closed_flag,
1116 PLDV.user_hold_flag,
1117 PLDV.cancel_flag,
1118 PLDV.cancelled_by,
1119 PLDV.cancel_date,
1120 PLDV.cancel_reason,
1121 PLDV.firm_status_lookup_code,
1122 PLDV.firm_date,
1123 PLDV.vendor_product_num,
1124 PLDV.contract_num,
1125 PLDV.taxable_flag,
1126 PLDV.tax_name,
1127 PLDV.type_1099,
1128 PLDV.capital_expense_flag,
1129 PLDV.negotiated_by_preparer_flag,
1130 PLDV.attribute_category,
1131 PLDV.attribute1,
1132 PLDV.attribute2,
1133 PLDV.attribute3,
1134 PLDV.attribute4,
1135 PLDV.attribute5,
1136 PLDV.attribute6,
1137 PLDV.attribute7,
1138 PLDV.attribute8,
1139 PLDV.attribute9,
1140 PLDV.attribute10,
1141 PLDV.reference_num,
1142 PLDV.attribute11,
1143 PLDV.attribute12,
1144 PLDV.attribute13,
1145 PLDV.attribute14,
1146 PLDV.attribute15,
1147 PLDV.min_release_amount,
1148 PLDV.price_type_lookup_code,
1149 PLDV.closed_code,
1150 PLDV.price_break_lookup_code,
1151 PLDV.ussgl_transaction_code,
1152 PLDV.government_context,
1153 PLDV.request_id,
1154 PLDV.program_application_id,
1155 PLDV.program_id,
1156 PLDV.program_update_date,
1157 PLDV.closed_date,
1158 PLDV.closed_reason,
1159 PLDV.closed_by,
1160 PLDV.transaction_reason_code,
1161 PLDV.org_id,
1162 PLDV.qc_grade,
1163 PLDV.base_uom,
1164 PLDV.base_qty,
1165 PLDV.secondary_uom,
1166 PLDV.secondary_qty,
1167 PLDV.global_attribute_category,
1168 PLDV.global_attribute1,
1169 PLDV.global_attribute2,
1170 PLDV.global_attribute3,
1171 PLDV.global_attribute4,
1172 PLDV.global_attribute5,
1173 PLDV.global_attribute6,
1174 PLDV.global_attribute7,
1175 PLDV.global_attribute8,
1176 PLDV.global_attribute9,
1177 PLDV.global_attribute10,
1178 PLDV.global_attribute11,
1179 PLDV.global_attribute12,
1180 PLDV.global_attribute13,
1181 PLDV.global_attribute14,
1182 PLDV.global_attribute15,
1183 PLDV.global_attribute16,
1184 PLDV.global_attribute17,
1185 PLDV.global_attribute18,
1186 PLDV.global_attribute19,
1187 PLDV.global_attribute20,
1188 PLDV.line_reference_num,
1189 PLDV.project_id,
1190 PLDV.task_id,
1191 PLDV.expiration_date,
1192 PLDV.tax_code_id,
1193 PLDV.oke_contract_header_id,
1194 PLDV.oke_contract_version_id,
1195 PLDV.secondary_quantity,
1196 PLDV.secondary_unit_of_measure,
1197 PLDV.preferred_grade,
1198 PLDV.auction_header_id,
1199 PLDV.auction_display_number,
1200 PLDV.auction_line_number,
1201 PLDV.bid_number,
1202 PLDV.bid_line_number,
1203 PLDV.retroactive_date,
1204 PLDV.supplier_ref_number,
1205 PLDV.contract_id,
1206 PLDV.start_date,
1207 PLDV.amount,
1208 PLDV.job_id,
1209 PLDV.contractor_first_name,
1210 PLDV.contractor_last_name,
1211 PLDV.order_type_lookup_code,
1212 PLDV.purchase_basis,
1213 PLDV.matching_basis,
1214 PLDV.svc_amount_notif_sent,
1215 PLDV.svc_completion_notif_sent,
1216 PLDV.base_unit_price,
1217 PLDV.manual_price_change_flag,
1218 -- <Complex Work R12 Start>
1219 PLDV.retainage_rate,
1220 PLDV.max_retainage_amount,
1221 PLDV.progress_payment_rate,
1222 PLDV.recoupment_rate,
1223 -- <Complex Work R12 End>
1224 PLDV.catalog_name,
1225 PLDV.supplier_part_auxid,
1226 PLDV.ip_category_id,
1227 PLDV.tax_attribute_update_code -- <ETAX R12>
1228 ) WHERE NVL(PLDV.delete_flag, 'N') <> 'Y';
1229
1230
1231 d_position := 10;
1232 EXCEPTION
1233 WHEN OTHERS THEN
1234 PO_MESSAGE_S.add_exc_msg
1235 ( p_pkg_name => d_pkg_name,
1236 p_procedure_name => d_api_name || '.' || d_position
1237 );
1238 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1239 END merge_changes;
1240
1241 -----------------------------------------------------------------------
1242 --Start of Comments
1243 --Name: lock_draft_record
1244 --Function:
1245 -- Obtain database lock for the record in draft table
1246 --Parameters:
1247 --IN:
1248 --p_po_line_id
1249 -- id for po line record
1250 --p_draft_id
1251 -- draft unique identifier
1252 --RETURN:
1253 --End of Comments
1254 ------------------------------------------------------------------------
1255 PROCEDURE lock_draft_record
1256 ( p_po_line_id IN NUMBER,
1257 p_draft_id IN NUMBER
1258 ) IS
1259
1260 d_api_name CONSTANT VARCHAR2(30) := 'lock_draft_record';
1261 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
1262 d_position NUMBER;
1263
1264 l_dummy NUMBER;
1265
1266 BEGIN
1267 d_position := 0;
1268 IF (PO_LOG.d_proc) THEN
1269 PO_LOG.proc_begin(d_module);
1270 END IF;
1271
1272 SELECT 1
1273 INTO l_dummy
1274 FROM po_lines_draft_all
1275 WHERE po_line_id = p_po_line_id
1276 AND draft_id = p_draft_id
1277 FOR UPDATE NOWAIT;
1278
1279 IF (PO_LOG.d_proc) THEN
1280 PO_LOG.proc_end(d_module);
1281 END IF;
1282
1283 EXCEPTION
1284 WHEN NO_DATA_FOUND THEN
1285 NULL;
1286 END lock_draft_record;
1287
1288 -----------------------------------------------------------------------
1289 --Start of Comments
1290 --Name: lock_transaction_record
1291 --Function:
1292 -- Obtain database lock for the record in transaction table
1293 --Parameters:
1294 --IN:
1295 --p_po_line_id
1296 -- id for po line record
1297 --RETURN:
1298 --End of Comments
1299 ------------------------------------------------------------------------
1300 PROCEDURE lock_transaction_record
1301 ( p_po_line_id IN NUMBER
1302 ) IS
1303
1304 d_api_name CONSTANT VARCHAR2(30) := 'lock_transaction_record';
1305 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
1306 d_position NUMBER;
1307
1308 l_dummy NUMBER;
1309
1310 BEGIN
1311 d_position := 0;
1312 IF (PO_LOG.d_proc) THEN
1313 PO_LOG.proc_begin(d_module);
1314 END IF;
1315
1316 SELECT 1
1317 INTO l_dummy
1318 FROM po_lines_all
1319 WHERE po_line_id = p_po_line_id
1320 FOR UPDATE NOWAIT;
1321
1322 IF (PO_LOG.d_proc) THEN
1323 PO_LOG.proc_end(d_module);
1324 END IF;
1325
1326 EXCEPTION
1327 WHEN NO_DATA_FOUND THEN
1328 NULL;
1329 END lock_transaction_record;
1330
1331 END PO_LINES_DRAFT_PKG;