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