[Home] [Help]
PACKAGE BODY: APPS.OE_UPG_SO
Source
1 PACKAGE BODY OE_Upg_SO as
2 /* $Header: OEXIUSOB.pls 120.1 2011/12/21 08:09:16 rmoharan noship $ */
3
4
5 Procedure Upgrade_Price_adjustments
6 ( L_level_flag IN Varchar2)
7 is
8 cursor padj is
9 select
10 pa.price_adjustment_id,
11 pa.creation_date,
12 pa.created_by,
13 pa.last_update_date,
14 pa.last_updated_by,
15 pa.last_update_login,
16 pa.program_application_id,
17 pa.program_id,
18 pa.program_update_date,
19 pa.request_id,
20 pa.header_id,
21 pa.discount_id,
22 pa.discount_line_id,
23 pa.automatic_flag,
24 pa.percent,
25 pa.line_id,
26 pa.context,
27 pa.attribute1,
28 pa.attribute2,
29 pa.attribute3,
30 pa.attribute4,
31 pa.attribute5,
32 pa.attribute6,
33 pa.attribute7,
34 pa.attribute8,
35 pa.attribute9,
36 pa.attribute10,
37 pa.attribute11,
38 pa.attribute12,
39 pa.attribute13,
40 pa.attribute14,
41 pa.attribute15
42 from
43 so_price_adjustments pa
44 where ( pa.line_id = G_OLD_LINE_ID and
45 pa.header_id = G_HEADER_Id and
46 L_level_flag = 'L') or
47 ( pa.header_id = G_header_id and
48 L_level_flag = 'H' and
49 pa.line_id is null );
50
51 mpa padj%ROWTYPE; /* alias defined for pa (price adjustments)*/
52 v_price_adjustment_id number;
53
54 --
55 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
56 --
57 begin
58 -- dbms_output.enable(999999999999);
59 open padj;
60 G_ERROR_LOCATION := 1;
61 loop /* start loop for Price adjustments*/
62
63 fetch padj into mpa;
64 exit when padj%NOTFOUND;
65
66 G_ERROR_LOCATION := 101;
67
68 begin
69 select
70 price_adjustment_id
71 into
72 v_price_adjustment_id
73 from
74 oe_price_adjustments
75 where price_adjustment_id = mpa.price_adjustment_id;
76
77 G_ERROR_LOCATION := 102;
78
79 select
80 oe_price_adjustments_s.nextval
81 into
82 v_price_adjustment_id
83 from dual;
84 G_ERROR_LOCATION := 103;
85 exception
86 when no_data_found then
87 v_price_adjustment_id := mpa.price_adjustment_id;
88 /*
89 when others then
90 null;
91 */
92 /* Should also process ERRORS here */
93 end;
94
95 -- dbms_output.put_line('ins oe_price_adjs');
96
97 G_ERROR_LOCATION := 104;
98
99 insert into oe_price_adjustments
100 (
101 price_adjustment_id,
102 creation_date,
103 created_by,
104 last_update_date,
105 last_updated_by,
106 last_update_login,
107 program_application_id,
108 program_id,
109 program_update_date,
110 request_id,
111 header_id,
112 discount_id,
113 discount_line_id,
114 automatic_flag,
115 percent,
116 line_id,
117 context,
118 attribute1,
119 attribute2,
120 attribute3,
121 attribute4,
122 attribute5,
123 attribute6,
124 attribute7,
125 attribute8,
126 attribute9,
127 attribute10,
128 attribute11,
129 attribute12,
133 )
130 attribute13,
131 attribute14,
132 attribute15
134 values
135 (
136 v_price_adjustment_id,
137 mpa.creation_date,
138 mpa.created_by,
139 mpa.last_update_date,
140 mpa.last_updated_by,
141 mpa.last_update_login,
142 mpa.program_application_id,
143 mpa.program_id,
144 mpa.program_update_date,
145 mpa.request_id,
146 mpa.header_id,
147 mpa.discount_id,
148 mpa.discount_line_id,
149 mpa.automatic_flag,
150 mpa.percent,
151 decode(L_level_flag,'L',G_LINE_ID,null),
152 mpa.context,
153 mpa.attribute1,
154 mpa.attribute2,
155 mpa.attribute3,
156 mpa.attribute4,
157 mpa.attribute5,
158 mpa.attribute6,
159 mpa.attribute7,
160 mpa.attribute8,
161 mpa.attribute9,
162 mpa.attribute10,
163 mpa.attribute11,
164 mpa.attribute12,
165 mpa.attribute13,
166 mpa.attribute14,
167 mpa.attribute15
168 );
169
170 G_ERROR_LOCATION := 105;
171
172 end loop; /* end loop for price adjustments */
173 G_ERROR_LOCATION := 2;
174 close padj;
175 End upgrade_price_adjustments;
176
177 Procedure Upgrade_Sales_Credits
178 ( L_level_flag IN Varchar2)
179 is
180 cursor sc is
181 select
182 ssc.sales_credit_id,
183 ssc.creation_date,
184 ssc.created_by,
185 ssc.last_update_date,
186 ssc.last_updated_by,
187 ssc.last_update_login,
188 ssc.header_id,
189 ssc.sales_credit_type_id,
190 ssc.salesrep_id,
191 ssc.percent,
192 ssc.line_id,
193 ssc.context,
194 ssc.attribute1,
195 ssc.attribute2,
196 ssc.attribute3,
197 ssc.attribute4,
198 ssc.attribute5,
199 ssc.attribute6,
200 ssc.attribute7,
201 ssc.attribute8,
202 ssc.attribute9,
203 ssc.attribute10,
204 ssc.attribute11,
205 ssc.attribute12,
206 ssc.attribute13,
207 ssc.attribute14,
208 ssc.attribute15,
209 null dw_update_advice_flag,
210 ssc.wh_update_date
211 from
212 so_sales_credits ssc
213 where ( (ssc.line_id = G_OLD_LINE_ID and
214 ssc.header_id = G_header_id and
215 L_level_flag = 'L')
216 or (ssc.header_id = G_header_id and
217 L_level_flag = 'H' and
218 ssc.line_id is null ) );
219
220 l_msc sc%ROWTYPE; /* alias defined for sc (sales credits)*/
221 v_sales_credit_id number;
222
223 --
224 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
225 --
226 begin
227 -- dbms_output.enable(999999999);
228 open sc;
229 G_ERROR_LOCATION := 3;
230 loop /* start loop for sales credits*/
231
232 fetch sc into l_msc;
233 exit when sc%NOTFOUND;
234
235 begin
236 select
237 sales_credit_id
238 into
239 v_sales_credit_id
240 from
241 oe_sales_credits
242 where sales_credit_id = l_msc.sales_credit_id;
243
244 select
245 oe_sales_credits_s.nextval
246 into
247 v_sales_credit_id
248 from dual;
249 exception
250 when no_data_found then
251 v_sales_credit_id := l_msc.sales_credit_id;
252 when others then
253 null;
254 /* Should also process ERRORS here */
255 end;
256
257 -- dbms_output.put_line('Ins oe_sales_credits');
258 insert into oe_sales_credits
259 (
260 sales_credit_id,
261 creation_date,
262 created_by,
263 last_update_date,
264 last_updated_by,
265 last_update_login,
266 header_id,
267 sales_credit_type_id,
268 salesrep_id,
269 percent,
270 line_id,
271 context,
272 attribute1,
273 attribute2,
277 attribute6,
274 attribute3,
275 attribute4,
276 attribute5,
278 attribute7,
279 attribute8,
280 attribute9,
281 attribute10,
282 attribute11,
283 attribute12,
284 attribute13,
285 attribute14,
286 attribute15,
287 dw_update_advice_flag,
288 wh_update_date
289 )
290 values
291 (
292 v_sales_credit_id,
293 l_msc.creation_date,
294 l_msc.created_by,
295 l_msc.last_update_date,
296 l_msc.last_updated_by,
297 l_msc.last_update_login,
298 l_msc.header_id,
299 l_msc.sales_credit_type_id,
300 l_msc.salesrep_id,
301 l_msc.percent,
302 decode(L_level_flag,'L',G_LINE_ID,null),
303 l_msc.context,
304 l_msc.attribute1,
305 l_msc.attribute2,
306 l_msc.attribute3,
307 l_msc.attribute4,
308 l_msc.attribute5,
309 l_msc.attribute6,
310 l_msc.attribute7,
311 l_msc.attribute8,
312 l_msc.attribute9,
313 l_msc.attribute10,
314 l_msc.attribute11,
315 l_msc.attribute12,
316 l_msc.attribute13,
317 l_msc.attribute14,
318 l_msc.attribute15,
319 l_msc.dw_update_advice_flag,
320 l_msc.wh_update_date
321 );
322 end loop; /* end loop for Sales credits*/
323 G_ERROR_LOCATION := 4;
324 close sc;
325 End Upgrade_Sales_Credits;
326
327 Procedure Upgrade_Cancellations
328 is
329 cursor can is
330 select
331 soc.header_id,
332 soc.line_id,
333 soc.created_by,
334 soc.creation_date,
335 soc.last_updated_by,
336 soc.last_update_date,
337 soc.last_update_login,
338 soc.program_application_id,
339 soc.program_id,
340 soc.program_update_date,
341 soc.request_id,
342 soc.cancel_code,
343 soc.cancelled_by,
344 soc.cancel_date,
345 soc.cancelled_quantity,
346 soc.cancel_comment,
347 soc.context,
348 soc.attribute1,
349 soc.attribute2,
350 soc.attribute3,
351 soc.attribute4,
352 soc.attribute5,
353 soc.attribute6,
354 soc.attribute7,
355 soc.attribute8,
356 soc.attribute9,
357 soc.attribute10,
358 soc.attribute11,
359 soc.attribute12,
360 soc.attribute13,
361 soc.attribute14,
362 soc.attribute15
363 from
364 so_order_cancellations soc
365 where soc.line_id = G_old_line_id
366 and soc.header_id = G_header_id;
367
368 --
369 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
370 --
371 begin
372 -- dbms_output.enable(999999999);
373 G_ERROR_LOCATION := 5;
374 for mcan in can loop
375 G_canc_rec := NULL;
376 G_canc_rec := mcan;
377 -- dbms_output.put_line('Ins cancellations');
378 G_ORD_CANC_FLAG := 'Y';
379 OE_Upg_SO.Upgrade_Insert_Lines_History;
380 G_ORD_CANC_FLAG := 'N';
381 end loop; /* end loop for Cancellations */
382 G_ERROR_LOCATION := 6;
383 End Upgrade_Cancellations;
384
385 Procedure Upgrade_Create_Order_Lines
386 is
387 cursor ol is
388 select
389 sla.line_id,
390 sla.org_id,
391 sla.header_id,
392 sla.line_number,
393 sla.date_requested_current,
394 sla.promise_date,
395 nvl(sld.schedule_date,sla.schedule_date) schedule_date,
396 nvl(sld.quantity,(nvl(sla.ordered_quantity,0)-nvl(sla.cancelled_quantity,0))) ordered_quantity,
397 decode(nvl(sla.ordered_quantity,0),nvl(sla.cancelled_quantity,0),'Y','N') line_cancel_flag,
398 sla.cancelled_quantity,
399 decode(
400 nvl(decode(sla.item_type_code,'SERVICE','N',
401 decode(sla.ato_line_id,null,sld.shippable_flag,
402 decode(sla.item_type_code,'CONFIG',sld.shippable_flag,'N'))),'-'), 'Y',
403 decode(sla.source_type_code,'EXTERNAL',
404 decode(sld.receipt_status_code,'INTERFACED',sld.quantity,null),sld.shipped_quantity),
405 decode(nvl(sla.ordered_quantity,0),nvl(sla.shipped_quantity,0),sla.shipped_quantity,
406 null)) shipped_quantity,
407 nvl(sld.invoiced_quantity,sla.invoiced_quantity) invoiced_quantity,
408 sla.tax_exempt_number,
409 sla.tax_exempt_reason_code,
410 nvl(sld.warehouse_id,sla.warehouse_id) warehouse_id,
411 sla.ship_to_site_use_id,
415 sla.demand_stream_id,
412 sla.ship_to_contact_id,
413 decode(G_AUTO_FLAG,'Y',sla.customer_item_id,
414 nvl(sld.customer_item_id,sla.customer_item_id)) customer_item_id,
416 sla.customer_dock_code,
417 sla.customer_job,
418 sla.customer_production_line,
419 sla.customer_model_serial_number,
420 sla.project_id,
421 sla.task_id,
422 nvl(sld.inventory_item_id,sla.inventory_item_id) inventory_item_id,
423 sla.inventory_item_id service_inventory_item_id,
424 sla.tax_code,
425 sla.demand_class_code,
426 sla.price_list_id,
427 sla.agreement_id,
428 sla.shipment_priority_code,
429 sla.ship_method_code,
430 sla.invoicing_rule_id,
431 sla.accounting_rule_id,
432 sla.original_system_line_reference,
433 sla.selling_price,
434 sla.list_price,
435 sla.context,
436 sla.attribute1,
437 sla.attribute2,
438 sla.attribute3,
439 sla.attribute4,
440 sla.attribute5,
441 sla.attribute6,
442 sla.attribute7,
443 sla.attribute8,
444 sla.attribute9,
445 sla.attribute10,
446 sla.attribute11,
447 sla.attribute12,
448 sla.attribute13,
449 sla.attribute14,
450 sla.attribute15,
451 slattr.industry_context,
452 slattr.industry_attribute1,
453 slattr.industry_attribute2,
454 slattr.industry_attribute3,
455 slattr.industry_attribute4,
456 slattr.industry_attribute5,
457 slattr.industry_attribute6,
458 slattr.industry_attribute7,
459 slattr.industry_attribute8,
460 slattr.industry_attribute9,
461 slattr.industry_attribute10,
462 slattr.industry_attribute11,
463 slattr.industry_attribute12,
464 slattr.industry_attribute13,
465 slattr.industry_attribute14,
466 slattr.industry_attribute15,
467 slattr.global_attribute_category,
468 slattr.global_attribute1,
469 slattr.global_attribute2,
470 slattr.global_attribute3,
471 slattr.global_attribute4,
472 slattr.global_attribute5,
473 slattr.global_attribute6,
474 slattr.global_attribute7,
475 slattr.global_attribute8,
476 slattr.global_attribute9,
477 slattr.global_attribute10,
478 slattr.global_attribute11,
479 slattr.global_attribute12,
480 slattr.global_attribute13,
481 slattr.global_attribute14,
482 slattr.global_attribute15,
483 slattr.global_attribute16,
484 slattr.global_attribute17,
485 slattr.global_attribute18,
486 slattr.global_attribute19,
487 slattr.global_attribute20,
488 sla.pricing_context,
489 sla.pricing_attribute1,
490 sla.pricing_attribute2,
491 sla.pricing_attribute3,
492 sla.pricing_attribute4,
493 sla.pricing_attribute5,
494 sla.pricing_attribute6,
495 sla.pricing_attribute7,
496 sla.pricing_attribute8,
497 sla.pricing_attribute9,
498 sla.pricing_attribute10,
499 nvl(sld.creation_date,sla.creation_date) creation_date,
500 nvl(sld.created_by,sla.created_by) created_by,
501 nvl(sld.last_update_date,sla.last_update_date) last_update_date,
502 nvl(sld.last_updated_by,sla.last_updated_by) last_updated_by,
503 nvl(sld.last_update_login,sla.last_update_login) last_update_login,
504 sla.program_application_id,
505 sla.program_id,
506 sla.program_update_date,
507 sla.request_id,
508 decode(sla.item_type_code,'MODEL',
509 decode(sla.parent_line_id,NULL,sla.line_id,sla.parent_line_id),
510 sla.parent_line_id) parent_Line_id,
511 sla.link_to_line_id,
512 nvl(sld.component_sequence_id,sla.component_sequence_id) component_sequence_id,
513 nvl(sld.component_code,sla.component_code) component_code,
514 decode(sla.item_type_code,
515 'STANDARD', decode(sla.option_flag,
516 'Y','OPTION',
517 sla.item_type_code),
518 'MODEL', decode(sla.parent_line_id,
519 NULL,'MODEL',
520 'CLASS'),
521 sla.item_type_code) item_type_code,
522 sla.source_type_code,
523 sla.transaction_reason_code,
524 nvl(sld.latest_acceptable_date, sla.latest_acceptable_date) latest_acceptable_date,
525 sld.dep_plan_required_flag,
526 decode(sla.item_type_code,'SERVICE',NULL,
527 decode(nvl(sld.schedule_status_code,'0'),'0',NULL,'SCHEDULED')) schedule_status_code,
528 sld.configuration_item_flag,
529 sld.delivery,
530 ' ' load_seq_number,
531 sla.ship_set_number,
532 sla.option_flag,
533 sla.unit_code,
534 sld.line_detail_id,
535 sla.credit_invoice_line_id,
536 sld.included_item_flag,
537 decode(sla.item_type_code,
538 'MODEL', decode(sla.ato_line_id,
539 NULL, decode(ato_flag,
540 'Y', sla.line_id,
541 sla.ato_line_id),
542 sla.ato_line_id),
546 sla.ato_line_id),
543 'STANDARD', decode(sla.ato_line_id,
544 NULL, decode(ato_flag,
545 'Y', sla.line_id,
547 sla.ato_line_id),
548 sla.ato_line_id) ato_line_id,
549 decode(sla.line_type_code,'RETURN','RETURN','ORDER') line_category_code,
550 sla.planning_priority,
551 decode(sla.return_reference_type_code,'ORDER','ORDER',
552 'PO','PO','INVOICE','INVOICE',sla.return_reference_type_code)
553 return_reference_type_code,
554 sla.line_type_code,
555 sla.return_reference_id,
556 nvl(sla.open_flag,'N') open_flag,
557 sla.ship_model_complete_flag,
558 sla.standard_component_freeze_date,
559 decode(sla.s1,1,'Y','N') booked_flag,
560 decode(nvl(sld.picking_line_id,0),0,'N','Y') shipping_interfaced_flag,
561 decode(sla.s4,6,'Y',NULL) fulfilled_flag,
562 decode(sla.s5,9,'YES',8,'NOT_ELIGIBLE',24,'NOT_ELIGIBLE',NULL) invoice_interface_status_code,
563 sla.s5,
564 sla.intermediate_ship_to_id,
565 sla.transaction_type_code,
566 sla.transaction_comments,
567 sla.selling_percent,
568 sla.customer_product_id,
569 sla.cp_service_id,
570 nvl(sld.quantity,sla.serviced_quantity) serviced_quantity,
571 sla.service_duration,
572 sla.service_start_date,
573 sla.service_end_date,
574 sla.service_coterminate_flag,
575 sla.service_period_conversion_rate,
576 sla.service_mass_txn_temp_id,
577 sla.service_parent_line_id,
578 sla.list_percent,
579 sla.percent_base_price,
580 sld.picking_line_id,
581 sla.planning_prod_seq_number,
582 sld.actual_departure_date,
583 decode(sla.item_type_code,'SERVICE',NULL,
584 decode(nvl(sld.schedule_status_code,'-'),'-','','Y')) visible_demand_flag,
585 decode(sla.item_type_code,'SERVICE','N',
586 decode(sla.ato_line_id,null,sld.shippable_flag,
587 decode(sla.item_type_code,'CONFIG',sld.shippable_flag,'N'))) shippable_flag
588 /* If you make changes to shippable flag in the above line, make it also on */
589 /* shipped_quantity decode */
590 from
591 so_lines_all sla,
592 so_line_attributes slattr,
593 oe_upgrade_wsh_iface sld
594 where
595 sla.line_id = slattr.line_id (+) and
596 sla.header_id = G_header_id and
597 decode(sla.item_type_code,'SERVICE',
598 nvl(sla.service_parent_line_id,0),sla.line_id) = sld.line_id(+) and
599 sla.line_type_code <> 'PARENT' and
600 not exists
601 (select NE.line_id from so_lines_all NE
602 where NE.line_Id = sla.service_parent_line_id
603 and NE.line_type_code = 'PARENT') /* Standalone service not upgraded */
604 order by
605 sla.ship_set_number,
606 sla.line_id,
607 sld.component_code,
608 sld.line_detail_id;
609
610 mol ol%ROWTYPE; /* alias defined for detail-less lines cursor*/
611
612 v_service_flag varchar2(1);
613 v_system_id number;
614 v_line_id number;
615 v_cancelled_quantity number;
616 v_ship_set_number number;
617
618 v_ins_return_quantity number;
619 v_avl_return_quantity number;
620 v_bal_return_quantity number;
621 v_return_new_line_id number;
622 v_return_new_line_number number;
623 v_return_lctr number;
624 v_return_created_line_id number;
625 v_line_exit_flag number;
626 v_cust_trx_attribute6 number;
627 v_customer_trx_id number;
628 v_received_quantity number;
629 v_actual_ordered_quantity number;
630 r_pto_m_c_k boolean := FALSE;
631 r_upgraded_flag varchar2(1):= null;
632 --
633 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
634 --
635 begin
636 -- dbms_output.enable(999999999);
637
638 G_ERROR_LOCATION := 7;
639 v_line_id := 0;
640 g_line_id_change_flag := 'N';
641 g_last_line_number := 0;
642 v_ship_set_number := 0;
643 g_set_id := NULL;
644
645
646 open ol;
647 <<begining_of_lines_loop>>
648 loop /* start loop for Order lines */
649 G_ERROR_LOCATION := 701;
650
651 r_shipment_number := 1;
652 r_ato_model := FALSE;
653 r_pto_m_c_k := FALSE;
654 r_no_config_item := FALSE;
655 r_ato_option := FALSE;
656 r_upgraded_flag := null;
657 r_line_set_id := null;
658 r_inventory_item_id := null;
659 r_uom_code := null;
660 r_warehouse_id := null;
661 v_return_created_line_id := null;
662
663 fetch ol into mol;
664 exit when ol%NOTFOUND;
665
666 /* Services are not split in terms with its parents' details
667 if the Parent's detail
668 is a Included item or a config item (changed on 12/22/99)*/
669
673 goto begining_of_lines_loop;
670 if mol.item_type_code = 'SERVICE' and
671 (nvl(mol.included_item_flag,'-') = 'Y'
672 or nvl(mol.configuration_item_flag,'-') = 'Y') then
674 end if;
675
676 /* g_line_rec := mol; */
677
678 if mol.item_type_code = 'SERVICE' then
679 v_service_flag := 'Y';
680 else
681 v_service_flag := 'N';
682 end if;
683
684 G_OLD_LINE_ID :=mol.line_id;
685
686 g_line_rec.line_id :=mol.line_id;
687 g_line_rec.org_id :=mol.org_id;
688 g_line_rec.header_id :=mol.header_id;
689 g_line_rec.line_number :=mol.line_number;
690 g_line_rec.date_requested_current :=mol.date_requested_current;
691 g_line_rec.promise_date :=mol.promise_date;
692
693 if mol.schedule_status_code is null then
694 g_line_rec.schedule_date :=null;
695 else
696 g_line_rec.schedule_date :=mol.schedule_date;
697 end if;
698
699 if v_service_flag = 'Y' then
700 g_line_rec.ordered_quantity := mol.serviced_quantity;
701 else
702 g_line_rec.ordered_quantity := mol.ordered_quantity;
703 end if;
704
705 if g_line_rec.ordered_quantity = 0 then
706 g_line_rec.ordered_quantity := NULL;
707 end if;
708
709 g_line_rec.cancelled_quantity :=mol.cancelled_quantity;
710 g_line_rec.shipped_quantity :=mol.shipped_quantity;
711
712 if g_line_rec.shipped_quantity = 0 then
713 g_line_rec.shipped_quantity := NULL;
714 end if;
715
716 if nvl(mol.invoice_interface_status_code,'-') = 'YES' then
717 g_line_rec.invoiced_quantity :=mol.ordered_quantity;
718 else
719 g_line_rec.invoiced_quantity :=mol.invoiced_quantity;
720 end if;
721
722 if nvl(g_line_rec.invoiced_quantity,0) = 0 then
723 g_line_rec.invoiced_quantity := NULL;
724 end if;
725
726 if nvl(mol.item_type_code,'-') = 'SERVICE' then
727 g_line_rec.inventory_item_id :=mol.service_inventory_item_id;
728 else
729 g_line_rec.inventory_item_id :=mol.inventory_item_id;
730 end if;
731
732 g_line_rec.tax_exempt_number :=mol.tax_exempt_number;
733 g_line_rec.tax_exempt_reason_code :=mol.tax_exempt_reason_code;
734 g_line_rec.warehouse_id :=mol.warehouse_id;
735 g_line_rec.ship_to_site_use_id :=mol.ship_to_site_use_id;
736 g_line_rec.ship_to_contact_id :=mol.ship_to_contact_id;
737 g_line_rec.customer_item_id :=mol.customer_item_id;
738 g_line_rec.demand_stream_id :=mol.demand_stream_id;
739 g_line_rec.customer_dock_code :=mol.customer_dock_code;
740 g_line_rec.customer_job :=mol.customer_job;
741 g_line_rec.customer_production_line :=mol.customer_production_line;
742 g_line_rec.customer_model_serial_number :=mol.customer_model_serial_number;
743 g_line_rec.project_id :=mol.project_id;
744 g_line_rec.task_id :=mol.task_id;
745 g_line_rec.tax_code :=mol.tax_code;
746 g_line_rec.demand_class_code :=mol.demand_class_code;
747 g_line_rec.price_list_id :=mol.price_list_id;
748 g_line_rec.agreement_id :=mol.agreement_id;
749 g_line_rec.shipment_priority_code :=mol.shipment_priority_code;
750 g_line_rec.ship_method_code :=mol.ship_method_code;
751 g_line_rec.invoicing_rule_id :=mol.invoicing_rule_id;
752 g_line_rec.accounting_rule_id :=mol.accounting_rule_id;
753 g_line_rec.original_system_line_reference :=mol.original_system_line_reference;
754 g_line_rec.selling_price :=mol.selling_price;
755 g_line_rec.list_price :=mol.list_price;
756 g_line_rec.context :=mol.context;
757 g_line_rec.attribute1 :=mol.attribute1;
758 g_line_rec.attribute2 :=mol.attribute2;
759 g_line_rec.attribute3 :=mol.attribute3;
760 g_line_rec.attribute4 :=mol.attribute4;
761 g_line_rec.attribute5 :=mol.attribute5;
762 g_line_rec.attribute6 :=mol.attribute6;
763 g_line_rec.attribute7 :=mol.attribute7;
764 g_line_rec.attribute8 :=mol.attribute8;
765 g_line_rec.attribute9 :=mol.attribute9;
766 g_line_rec.attribute10 :=mol.attribute10;
767 g_line_rec.attribute11 :=mol.attribute11;
771 g_line_rec.attribute15 :=mol.attribute15;
768 g_line_rec.attribute12 :=mol.attribute12;
769 g_line_rec.attribute13 :=mol.attribute13;
770 g_line_rec.attribute14 :=mol.attribute14;
772 g_line_rec.industry_context :=mol.industry_context;
773 g_line_rec.industry_attribute1 :=mol.industry_attribute1;
774 g_line_rec.industry_attribute2 :=mol.industry_attribute2;
775 g_line_rec.industry_attribute3 :=mol.industry_attribute3;
776 g_line_rec.industry_attribute4 :=mol.industry_attribute4;
777 g_line_rec.industry_attribute5 :=mol.industry_attribute5;
778 g_line_rec.industry_attribute6 :=mol.industry_attribute6;
779 g_line_rec.industry_attribute7 :=mol.industry_attribute7;
780 g_line_rec.industry_attribute8 :=mol.industry_attribute8;
781 g_line_rec.industry_attribute9 :=mol.industry_attribute9;
782 g_line_rec.industry_attribute10 :=mol.industry_attribute10;
783 g_line_rec.industry_attribute11 :=mol.industry_attribute11;
784 g_line_rec.industry_attribute12 :=mol.industry_attribute12;
785 g_line_rec.industry_attribute13 :=mol.industry_attribute13;
786 g_line_rec.industry_attribute14 :=mol.industry_attribute14;
787 g_line_rec.industry_attribute15 :=mol.industry_attribute15;
788 G_ERROR_LOCATION := 702;
789 /*
790 if substr(mol.global_attribute_category,1,5) in ('JL.BR', 'JL.AR', 'JL.CO') then
791 g_line_rec.global_attribute_category :=
792 substr(mol.global_attribute_category,1,5)||'.OEXOEORD.LINES';
793 else
794 g_line_rec.global_attribute_category := mol.global_attribute_category;
795 end if;
796 */
797 G_ERROR_LOCATION := 7021;
798
799 g_line_rec.global_attribute1 :=mol.global_attribute1;
800 g_line_rec.global_attribute2 :=mol.global_attribute2;
801 g_line_rec.global_attribute3 :=mol.global_attribute3;
802 g_line_rec.global_attribute4 :=mol.global_attribute4;
803 g_line_rec.global_attribute5 :=mol.global_attribute5;
804 g_line_rec.global_attribute6 :=mol.global_attribute6;
805 g_line_rec.global_attribute7 :=mol.global_attribute7;
806 g_line_rec.global_attribute8 :=mol.global_attribute8;
807 g_line_rec.global_attribute9 :=mol.global_attribute9;
808 g_line_rec.global_attribute10 :=mol.global_attribute10;
809 g_line_rec.global_attribute11 :=mol.global_attribute11;
810 g_line_rec.global_attribute12 :=mol.global_attribute12;
811 g_line_rec.global_attribute13 :=mol.global_attribute13;
812 g_line_rec.global_attribute14 :=mol.global_attribute14;
813 g_line_rec.global_attribute15 :=mol.global_attribute15;
814 g_line_rec.global_attribute16 :=mol.global_attribute16;
815 g_line_rec.global_attribute17 :=mol.global_attribute17;
816 g_line_rec.global_attribute18 :=mol.global_attribute18;
817 g_line_rec.global_attribute19 :=mol.global_attribute19;
818 g_line_rec.global_attribute20 :=mol.global_attribute20;
819 g_line_rec.pricing_context :=mol.pricing_context;
820 g_line_rec.pricing_attribute1 :=mol.pricing_attribute1;
821 g_line_rec.pricing_attribute2 :=mol.pricing_attribute2;
822 g_line_rec.pricing_attribute3 :=mol.pricing_attribute3;
823 g_line_rec.pricing_attribute4 :=mol.pricing_attribute4;
824 g_line_rec.pricing_attribute5 :=mol.pricing_attribute5;
825 g_line_rec.pricing_attribute6 :=mol.pricing_attribute6;
826 g_line_rec.pricing_attribute7 :=mol.pricing_attribute7;
827 g_line_rec.pricing_attribute8 :=mol.pricing_attribute8;
828 g_line_rec.pricing_attribute9 :=mol.pricing_attribute9;
829 g_line_rec.pricing_attribute10 :=mol.pricing_attribute10;
830 g_line_rec.creation_date :=mol.creation_date;
831 g_line_rec.created_by :=mol.created_by;
832 g_line_rec.last_update_date :=mol.last_update_date;
833 g_line_rec.last_updated_by :=mol.last_updated_by;
834 g_line_rec.last_update_login :=mol.last_update_login;
835 g_line_rec.program_application_id :=mol.program_application_id;
836 g_line_rec.program_id :=mol.program_id;
837 g_line_rec.program_update_date :=mol.program_update_date;
838 g_line_rec.request_id :=mol.request_id;
839
840 G_ERROR_LOCATION := 7022;
841
842 g_line_rec.parent_line_id :=mol.parent_line_id;
843 g_line_rec.link_to_line_id :=mol.link_to_line_id;
847 g_line_rec.source_type_code :=mol.source_type_code;
844 g_line_rec.component_sequence_id :=mol.component_sequence_id;
845 g_line_rec.component_code :=mol.component_code;
846 g_line_rec.item_type_code :=mol.item_type_code;
848 g_line_rec.transaction_reason_code :=mol.transaction_reason_code;
849
850 if mol.latest_acceptable_date is null then
851 begin
852 g_line_rec.latest_acceptable_date := g_line_rec.date_requested_current
853 + G_EARLIEST_SCHEDULE_LIMIT;
854 exception
855 when others then
856 g_line_rec.latest_acceptable_date :=NULL;
857 oe_upg_so.upgrade_insert_errors
858 (
859 L_header_id => g_header_id,
860 L_comments => 'FYI Only: Line ID: '||to_char(g_old_line_id)
861 ||' had corrupted data in Date_requested_current field. Latest_acceptable_date is updated to NULL for all its new lines'
862 );
863
864 end;
865 else
866 g_line_rec.latest_acceptable_date :=mol.latest_acceptable_date;
867 end if;
868
869 g_line_rec.dep_plan_required_flag :=mol.dep_plan_required_flag;
870 g_line_rec.schedule_status_code :=mol.schedule_status_code;
871 g_line_rec.configuration_item_flag :=mol.configuration_item_flag;
872 g_line_rec.ship_set_number :=mol.ship_set_number;
873 g_line_rec.option_flag :=mol.option_flag;
874 g_line_rec.unit_code :=mol.unit_code;
875 g_line_rec.line_detail_id :=mol.line_detail_id;
876 g_line_rec.credit_invoice_line_id :=mol.credit_invoice_line_id;
877 g_line_rec.included_item_flag :=mol.included_item_flag;
878 g_line_rec.ato_line_id :=mol.ato_line_id;
879 g_line_rec.line_category_code :=mol.line_category_code;
880 g_line_rec.planning_priority :=mol.planning_priority;
881 g_line_rec.return_reference_type_code :=mol.return_reference_type_code;
882 g_line_rec.line_type_code :=mol.line_type_code;
883 g_line_rec.return_reference_id :=mol.return_reference_id;
884 g_line_rec.open_flag :=mol.open_flag;
885 g_line_rec.ship_model_complete_flag :=mol.ship_model_complete_flag;
886 g_line_rec.standard_component_freeze_date :=mol.standard_component_freeze_date;
887
888 G_ERROR_LOCATION := 7023;
889
890 g_line_rec.booked_flag :=mol.booked_flag;
891 g_line_rec.shipping_interfaced_flag :=mol.shipping_interfaced_flag;
892 g_line_rec.fulfilled_flag :=mol.fulfilled_flag;
893 g_line_rec.intermediate_ship_to_id :=mol.intermediate_ship_to_id;
894 g_line_rec.transaction_type_code :=mol.transaction_type_code;
895 g_line_rec.transaction_comments :=mol.transaction_comments;
896 g_line_rec.selling_percent :=mol.selling_percent;
897 g_line_rec.customer_product_id :=mol.customer_product_id;
898 g_line_rec.cp_service_id :=mol.cp_service_id;
899 g_line_rec.serviced_quantity :=mol.serviced_quantity;
900 g_line_rec.service_duration :=mol.service_duration;
901 g_line_rec.service_start_date :=mol.service_start_date;
902 g_line_rec.service_end_date :=mol.service_end_date;
903 g_line_rec.service_coterminate_flag :=mol.service_coterminate_flag;
904 g_line_rec.service_period_conversion_rate :=mol.service_period_conversion_rate;
905 g_line_rec.service_mass_txn_temp_id :=mol.service_mass_txn_temp_id;
906 g_line_rec.service_parent_line_id :=mol.service_parent_line_id;
907 g_line_rec.service_period :=NULL;
908 g_line_rec.list_percent :=mol.list_percent;
909 g_line_rec.percent_base_price :=mol.percent_base_price;
910 g_line_rec.picking_line_id :=mol.picking_line_id;
911
912 G_ERROR_LOCATION := 7024;
913
914 g_line_rec.planning_prod_seq_number :=mol.planning_prod_seq_number;
915 g_line_rec.actual_departure_date :=mol.actual_departure_date;
916 g_line_rec.re_source_flag :='N';
917 g_line_rec.tp_context :=null;
918 g_line_rec.tp_attribute1 :=null;
919 g_line_rec.tp_attribute2 :=null;
920 g_line_rec.tp_attribute3 :=null;
921 g_line_rec.tp_attribute4 :=null;
922 g_line_rec.tp_attribute5 :=null;
923 g_line_rec.tp_attribute6 :=null;
924 g_line_rec.tp_attribute7 :=null;
925 g_line_rec.tp_attribute8 :=null;
929 g_line_rec.tp_attribute12 :=null;
926 g_line_rec.tp_attribute9 :=null;
927 g_line_rec.tp_attribute10 :=null;
928 g_line_rec.tp_attribute11 :=null;
930 g_line_rec.tp_attribute13 :=null;
931 g_line_rec.tp_attribute14 :=null;
932 g_line_rec.tp_attribute15 :=null;
933
934 g_line_rec.fulfilled_quantity :=mol.shipped_quantity;
935 if g_line_rec.fulfilled_quantity = 0 then
936 g_line_rec.fulfilled_quantity := NULL;
937 end if;
938
939
940 G_ERROR_LOCATION := 7025;
941
942 g_line_rec.marketing_source_code_id :=null;
943 g_line_rec.calculate_price_flag :='Y';
944
945 G_ERROR_LOCATION := 7026;
946
947 g_line_rec.shippable_flag :=mol.shippable_flag;
948 g_line_rec.fulfillment_method_code :=null;
949
950 G_ERROR_LOCATION := 7027;
951
952 g_line_rec.revenue_amount :=null;
953
954 if nvl(g_line_rec.shipped_quantity,0) > 0 then
955 g_line_rec.shipping_quantity_uom :=mol.unit_code;
956 g_line_rec.fulfillment_date :=mol.actual_departure_date;
957 else
958 g_line_rec.fulfillment_date :=null;
959 g_line_rec.shipping_quantity_uom :=null;
960 end if;
961
962 G_ERROR_LOCATION := 7028;
963
964 g_line_rec.visible_demand_flag :=mol.visible_demand_flag;
965 g_line_rec.flow_status_code :=null;
966
967 G_ERROR_LOCATION := 703;
968
969 if G_CANCELLED_FLAG = 'Y' or (mol.ordered_quantity = mol.cancelled_quantity) then
970 g_line_rec.cancelled_flag :='Y';
971 g_line_rec.flow_status_code :='CANCELLED';
972 else
973 g_line_rec.cancelled_flag :=NULL;
974
975 if mol.open_flag = 'N' then
976 g_line_rec.flow_status_code :='CLOSED';
977 elsif mol.booked_Flag = 'N' then
978 g_line_rec.flow_status_code :='ENTERED';
979 elsif mol.booked_Flag = 'Y' then
980 g_line_rec.flow_status_code :='BOOKED';
981 else
982 g_line_rec.flow_status_code :=NULL;
983 end if;
984 end if;
985
986
987 if v_service_flag = 'Y' then
988 g_line_rec.service_txn_reason_code := mol.transaction_reason_code;
989 g_line_rec.service_txn_comments := mol.transaction_comments;
990 g_line_rec.service_number := mol.line_number;
991 g_line_rec.service_reference_type_Code := 'ORDER';
992 g_line_rec.service_reference_line_id := mol.service_parent_line_id;
993
994 v_system_id := NULL;
995
996 select max(system_id) into v_system_id from so_line_service_details
997 where line_id = mol.line_id;
998
999 g_line_rec.service_reference_system_id :=v_system_id;
1000 else
1001 g_line_rec.service_txn_reason_code := NULL;
1002 g_line_rec.service_txn_comments := NULL;
1003 g_line_rec.service_number := NULL;
1004 g_line_rec.service_reference_type_Code :=NULL;
1005 g_line_rec.service_reference_line_id :=NULL;
1006 g_line_rec.service_reference_system_id :=NULL;
1007 end if;
1008
1009 if (nvl(v_ship_set_number,0) <> nvl(mol.ship_set_number,0)
1010 and mol.ship_set_number is not NULL
1011 and mol.schedule_date is not NULL
1012 and mol.warehouse_id is not NULL
1013 and mol.schedule_status_code is not NULL
1014 and mol.ship_to_site_use_id is not NULL) then
1015 /*
1016 and mol.shipment_priority_code is not NULL
1017 and mol.ship_method_code is not NULL) then
1018 */
1019 v_ship_set_number := mol.ship_set_number;
1020 select
1021 oe_sets_s.nextval
1022 into
1023 g_set_id
1024 from dual;
1025
1026 G_ERROR_LOCATION := 704;
1027
1028 insert into oe_sets
1029 (
1030 set_id,
1031 set_name,
1032 set_type,
1033 header_id,
1034 ship_from_org_id,
1035 ship_to_org_id,
1036 shipment_priority_code,
1037 schedule_ship_date,
1038 schedule_arrival_date,
1039 freight_carrier_code,
1040 shipping_method_code,
1041 set_status,
1042 created_by,
1046 update_login
1043 creation_date,
1044 update_date,
1045 updated_by,
1047 )
1048 values
1049 (
1050 g_set_id, /* SET_ID */
1051 mol.ship_set_number, /* SET_NAME */
1052 'SHIP_SET', /* SET_TYPE */
1053 G_header_id, /* HEADER_ID */
1054 mol.warehouse_id, /* SHIP_FROM_ORG_ID */
1055 NULL, /* SHIP_TO_ORG_ID */
1056 mol.shipment_priority_code, /* SHIPMENT_PRIORITY_CODE */
1057 mol.schedule_date, /* SCHEDULE_SHIP_DATE */
1058 null, /* SCHEDULE_ARRIVAL_DATE */
1059 mol.ship_method_code, /* FREIGHT_CARRIER_CODE */
1060 mol.ship_method_code, /* SHIP_METHOD_CODE */
1061 null, /* SET_STATUS */
1062 1, /* CREATED_BY */
1063 SYSDATE, /* CREATION_DATE */
1064 SYSDATE, /* LAST_UPDATE_DATE */
1065 1, /* LAST_UPDATED_BY */
1066 0 /* LAST_UPDATE_LOGIN */
1067 );
1068 G_ERROR_LOCATION := 705;
1069 elsif mol.ship_set_number is null then
1070 G_ERROR_LOCATION := 706;
1071 g_set_id := NULL;
1072 end if;
1073
1074 g_copied_line_flag := 'N';
1075
1076 if v_line_id = mol.line_id then
1077 select
1078 oe_order_lines_s.nextval
1079 into
1080 g_line_id
1081 from dual;
1082 g_line_id_change_flag := 'N';
1083 else
1084 v_line_id := mol.line_id;
1085 g_line_id := mol.line_id;
1086 g_orig_line_id := mol.line_id;
1087 g_line_id_change_flag := 'Y';
1088
1089 if G_COPIED_FLAG = 'Y' and mol.original_system_line_reference is not null then
1090 G_COPIED_LINE_FLAG := 'Y';
1091 end if;
1092 end if;
1093
1094 if G_AUTO_FLAG = 'Y' then
1095 g_line_rec.source_document_type_id := 5;
1096 elsif G_COPIED_LINE_FLAG = 'Y' then
1097 g_line_rec.source_document_type_id := 2;
1098 elsif G_ORDER_SOURCE_ID in (1,3,4,7,8) then
1099 g_line_rec.source_document_type_id := g_order_source_id;
1100 else
1101 g_line_rec.source_document_type_id := NULL;
1102 end if;
1103
1104 if nvl(mol.included_item_flag,'-') = 'Y' then
1105 g_line_rec.link_to_line_id := g_orig_line_id;
1106 g_line_rec.parent_line_id := g_orig_line_id;
1107 g_line_rec.item_type_code := 'INCLUDED';
1108 elsif nvl(mol.configuration_item_flag,'-') = 'Y' then
1109 g_line_rec.link_to_line_id := g_orig_line_id;
1110 g_line_rec.parent_line_id := g_orig_line_id;
1111 g_line_rec.ato_line_id := g_orig_line_id;
1112 g_line_rec.item_type_code := 'CONFIG';
1113 elsif ((mol.line_id <> mol.parent_line_id) AND
1114 (mol.item_type_code = 'MODEL')) THEN
1115 g_line_rec.link_to_line_id := mol.link_to_line_id;
1116 g_line_rec.parent_line_id := mol.parent_line_id;
1117 g_line_rec.ato_line_id := mol.ato_line_id;
1118 g_line_rec.item_type_code := 'CLASS';
1119 else
1120 g_line_rec.link_to_line_id := mol.link_to_line_id;
1121 g_line_rec.parent_line_id := mol.parent_line_id;
1122 g_line_rec.ato_line_id := mol.ato_line_id;
1123 g_line_rec.item_type_code := mol.item_type_code;
1124 end if;
1125
1126 if g_line_rec.item_type_code = 'SERVICE' then
1127 g_line_rec.top_model_line_id := null;
1128 else
1129 g_line_rec.top_model_line_id := g_line_rec.parent_line_id;
1130 end if;
1131
1132 g_Last_Line_Number := g_Last_Line_Number + 1;
1133
1134 /* Initializing temporary variables */
1135
1136 v_ins_return_quantity := NULL;
1137 v_avl_return_quantity := NULL;
1138 v_bal_return_quantity := NULL;
1139 v_return_new_line_id := NULL;
1140 v_return_new_line_number := NULL;
1141 v_reference_line_id := NULL;
1142 v_reference_header_id := NULL;
1143 v_return_lctr := NULL;
1144 v_return_created_line_id := NULL;
1145 v_line_exit_flag := NULL;
1146 G_log_rec.comments := NULL;
1147
1148
1149 v_ins_return_quantity := g_line_rec.ordered_quantity;
1150 v_avl_return_quantity := 0;
1151
1152 if g_line_rec.line_type_code = 'RETURN' THEN
1156 from mtl_so_rma_interface
1153 v_bal_return_quantity := g_line_rec.ordered_quantity;
1154 begin
1155 select received_quantity into v_received_quantity
1157 where mol.line_id = rma_line_id;
1158
1159 if v_received_quantity = 0 THEN
1160 v_received_quantity := NULL;
1161 end if;
1162 exception
1163 when no_data_found then
1164 v_received_quantity := null;
1165 when others then
1166 v_received_quantity := null;
1167 end;
1168
1169 if g_line_rec.return_reference_type_code is not null
1170 and g_line_rec.return_reference_type_code = 'INVOICE' then
1171 begin
1172 select
1173 customer_trx_id
1174 into
1175 v_customer_trx_id
1176 from
1177 RA_CUSTOMER_TRX_LINES
1178 where customer_trx_line_id = g_line_rec.return_reference_id
1179 and ( interface_line_attribute6 is null or
1180 interface_line_attribute6 between
1181 '000000000000000' and '999999999999999');
1182 exception
1183 when no_data_found then
1184 v_customer_trx_id := NULL;
1185 end;
1186 end if;
1187
1188 v_reference_line_id := mol.link_to_line_id;
1189
1190 if v_reference_line_id is not null then
1191 begin
1192 select header_id into v_reference_header_id
1193 from so_lines_all
1194 where line_id = v_reference_line_id;
1195 exception
1196 when others then
1197 null;
1198 end;
1199 end if;
1200
1201 else
1202 v_bal_return_quantity := 0;
1203 v_reference_line_id := NULL;
1204 end if;
1205
1206 /*
1207 v_actual_ordered_quantity := nvl(mol.ordered_quantity,0) -
1208 nvl(mol.cancelled_quantity,0);
1209 */
1210 v_actual_ordered_quantity := nvl(mol.ordered_quantity,0) ;
1211
1212 if g_hdr_canc_flag = 'Y' or mol.line_cancel_flag = 'Y' then
1213 g_line_rec.ordered_quantity := 0;
1214 else
1215 g_line_rec.ordered_quantity := mol.ordered_quantity;
1216 end if;
1217
1218 G_ERROR_LOCATION := 707;
1219
1220 v_line_exit_flag := 0;
1221 v_return_lctr := 1;
1222 r_lctr := 1;
1223
1224 loop
1225 /* v_return_lctr := v_return_lctr + 1; */
1226
1227 if g_line_rec.line_type_code = 'RETURN' THEN
1228
1229 if g_line_rec.return_reference_type_code is not null then
1230
1231 if r_lctr = 1 then
1232 begin
1233 Select item_type_code, ato_flag,option_flag
1234 into r_original_item_type_code,
1235 r_ato_flag,r_option_flag
1236 from so_lines_all
1237 where line_id = mol.link_to_line_id;
1238 exception
1239 when others then
1240 null;
1241 end;
1242
1243
1244 if r_original_item_type_code = 'MODEL' and
1245 r_ato_flag = 'Y' then
1246 r_ato_model := TRUE;
1247 r_upgraded_flag := 'P';
1248 end if;
1249
1250 if r_option_flag = 'Y' and
1251 r_ato_flag = 'Y' then
1252 r_ato_option := TRUE;
1253 r_upgraded_flag := 'P';
1254 end if;
1255
1256 if r_ato_flag = 'N' and
1257 r_original_item_type_code in
1258 ('MODEL','KIT','CLASS') then
1259 r_pto_m_c_k := TRUE;
1260 r_upgraded_flag := 'P';
1261 end if;
1262
1263 if r_ato_model then
1264 begin
1265 Select
1266 inventory_item_id,
1267 unit_code,
1268 warehouse_id
1269 into
1270 r_inventory_item_id_2,
1271 r_uom_code_2,
1272 r_warehouse_id_2
1273 from so_line_details
1274 where configuration_item_flag = 'Y'
1275 and line_id = mol.link_to_line_id;
1276 exception
1277 when others then
1278 r_no_config_item := TRUE;
1279 end;
1283
1280 end if; /* ato model */
1281 end if; /* r_lctr = 1 */
1282 end if; /* reference_type_code is not null */
1284 if v_received_quantity is not NULL and
1285 v_actual_ordered_quantity > v_received_quantity THEN
1286
1287 If v_return_lctr = 1 then
1288 IF not (r_ato_model or r_ato_option or r_pto_m_c_k) THEN
1289 g_line_rec.shipped_quantity := v_received_quantity;
1290 g_line_rec.ordered_quantity := v_received_quantity;
1291 v_return_lctr := 2;
1292 ELSIF r_ato_option or r_no_config_item or r_pto_m_c_k THEN
1293 g_line_rec.shipped_quantity := v_received_quantity;
1294 v_line_exit_flag := 1;
1295 ELSE /* it is a model */
1296 g_line_rec.shipped_quantity := v_received_quantity;
1297 v_return_lctr := 3;
1298 END IF;
1299 elsif v_return_lctr = 2 then
1300 g_line_rec.shipped_quantity := null;
1301 g_line_rec.ordered_quantity :=
1302 nvl(v_actual_ordered_quantity,0) - nvl(v_received_quantity,0);
1303 g_line_rec.cancelled_quantity := null;
1304 g_line_rec.fulfilled_quantity := null;
1305 g_line_rec.invoiced_quantity := null;
1306 r_shipment_number := 2;
1307 v_return_lctr := 5;
1308 v_line_exit_flag := 1;
1309 elsif v_return_lctr in (3,4) then
1310 r_inventory_item_id := r_inventory_item_id_2;
1311 r_uom_code := r_uom_code_2;
1312 r_warehouse_id := r_warehouse_id_2;
1313 IF v_return_lctr = 3 Then
1314 g_line_rec.shipped_quantity := v_received_quantity;
1315 g_line_rec.ordered_quantity := v_received_quantity;
1316 v_return_lctr := 4;
1317 ELSIF v_return_lctr = 4 Then
1318 g_line_rec.shipped_quantity := null;
1319 g_line_rec.ordered_quantity :=
1320 nvl(v_actual_ordered_quantity,0) - nvl(v_received_quantity,0);
1321 g_line_rec.cancelled_quantity := null;
1322 g_line_rec.fulfilled_quantity := null;
1323 g_line_rec.invoiced_quantity := null;
1324 r_shipment_number := 2;
1325 v_return_lctr := 5;
1326 v_line_exit_flag := 1;
1327 END IF;
1328 end if;
1329 else
1330 IF (not r_ato_model) or r_ato_option
1331 or r_no_config_item or r_pto_m_c_k then
1332 v_line_exit_flag := 1;
1333 ELSIF v_return_lctr = 3 THEN
1334 r_inventory_item_id := r_inventory_item_id_2;
1335 r_uom_code := r_uom_code_2;
1336 r_warehouse_id := r_warehouse_id_2;
1337 v_return_lctr := 4;
1338 v_line_exit_flag := 1;
1339 ELSE
1340 v_return_lctr := 3;
1341 END IF;
1342 end if;
1343
1344 IF (v_received_quantity is not null and
1345 v_actual_ordered_quantity > v_received_quantity)
1346 AND v_return_lctr in (2,4) THEN
1347 select oe_sets_s.nextval
1348 into r_line_set_id from dual;
1349 -- Also Insert into oe_sets
1350 insert into oe_sets
1351 (
1352 SET_ID,
1353 SET_NAME,
1354 SET_TYPE,
1355 HEADER_ID,
1356 SHIP_FROM_ORG_ID,
1357 SHIP_TO_ORG_ID,
1358 SCHEDULE_SHIP_DATE,
1359 SCHEDULE_ARRIVAL_DATE,
1360 FREIGHT_CARRIER_CODE,
1361 SHIPPING_METHOD_CODE,
1362 SHIPMENT_PRIORITY_CODE,
1363 SET_STATUS,
1364 CREATED_BY,
1365 CREATION_DATE,
1366 UPDATED_BY,
1367 UPDATE_DATE,
1368 UPDATE_LOGIN,
1369 INVENTORY_ITEM_ID,
1370 ORDERED_QUANTITY_UOM,
1371 LINE_TYPE_ID,
1372 SHIP_TOLERANCE_ABOVE,
1373 SHIP_TOLERANCE_BELOW
1374 )
1375 values
1376 (
1377 r_line_set_id, /* SET_ID, */
1378 to_char(r_line_set_id), /* SET_NAME, */
1379 'LINE_SET', /* SET_TYPE, */
1383 null, /* SCHEDULE_SHIP_DATE, */
1380 g_line_rec.header_id, /* HEADER_ID,*/
1381 null, /* SHIP_FROM_ORG_ID, */
1382 null, /* SHIP_TO_ORG_ID, */
1384 null, /* SCHEDULE_ARRIVAL_DATE, */
1385 null, /* FREIGHT_CARRIER_CODE, */
1386 null, /* SHIPPING_METHOD_CODE, */
1387 null, /* SHIPMENT_PRIORITY_CODE, */
1388 null, /* SET_STATUS, */
1389 0, /* CREATED_BY, */
1390 sysdate, /* CREATION_DATE, */
1391 0, /* UPDATED_BY, */
1392 sysdate, /* UPDATE_DATE, */
1393 0, /* UPDATE_LOGIN, */
1394 null, /* INVENTORY_ITEM_ID, */
1395 null, /* ORDERED_QUANTITY_UOM,*/
1396 null, /* LINE_TYPE_ID, */
1397 null, /* SHIP_TOLERANCE_ABOVE,*/
1398 null /* SHIP_TOLERANCE_BELOW */
1399 );
1400 END IF;
1401
1402 IF v_return_lctr = 4 THEN
1403 g_last_line_number := g_last_line_number +1;
1404 END IF;
1405 else
1406 v_line_exit_flag := 1;
1407 end if; /* line_type_code = 'RETURN' */
1408
1409 if G_AUTO_FLAG = 'Y' then
1410 begin
1411 select
1412 H.schedule_type_ext,
1413 L.header_id,
1414 L.line_id
1415 into
1416 g_line_rec.rla_schedule_type_code,
1417 g_line_rec.source_document_id,
1418 g_line_rec.source_document_line_id
1419 from
1420 RLM_SCHEDULE_LINES_ALL L,
1421 RLM_SCHEDULE_HEADERS_ALL H
1422 where to_char(mol.demand_stream_id) = L.ITEM_DETAIL_REF_VALUE_3
1423 and L.Header_id = H.Header_id
1424 and L.ITEM_DETAIL_REF_CODE_3 = 'ID';
1425 exception
1426 when no_data_found then
1427 g_line_rec.rla_schedule_type_code := NULL;
1428 g_line_rec.source_document_id := NUll;
1429 g_line_rec.source_document_line_id:= NUll;
1430 when too_many_rows then
1431 g_line_rec.rla_schedule_type_code := NULL;
1432 g_line_rec.source_document_id := NULL;
1433 g_line_rec.source_document_line_id:= NULL;
1434 when others then
1435 NULL;
1436 end;
1437 else
1438 g_line_rec.rla_schedule_type_code := NULL;
1439 if G_COPIED_LINE_FLAG = 'Y' then
1440 g_line_rec.source_document_id := G_SOURCE_DOCUMENT_ID;
1441 g_line_rec.source_document_line_id := g_line_rec.original_system_line_reference;
1442 else
1443 g_line_rec.source_document_id := NULL;
1444 g_line_rec.source_document_line_id:= NULL;
1445 end if;
1446 end if;
1447
1448 G_ERROR_LOCATION := 708;
1449
1450 begin
1451 select
1452 nvl(decode(G_ORDER_CATEGORY_CODE,
1453 'ORDER',DEFAULT_OUTBOUND_LINE_TYPE_ID,
1454 'RETURN',DEFAULT_INBOUND_LINE_TYPE_ID,0),0)
1455 into
1456 g_line_rec.line_type_id
1457 from
1458 oe_transaction_types_all tta
1459 where tta.transaction_type_id = G_ORDER_TYPE_ID;
1460 exception
1461 when others then
1462 g_line_rec.line_type_id := 0;
1463 end;
1464
1465 if r_lctr > 1 then
1466 select
1467 oe_order_lines_s.nextval
1468 into
1469 v_return_created_line_id
1470 from dual;
1471 end if;
1472
1473 if r_lctr = 1 then
1474 g_line_id := g_line_id;
1475 else
1476 g_line_id := v_return_created_line_id;
1477 end if;
1478
1479 if nvl(g_line_rec.invoiced_quantity,0) = nvl(g_line_rec.ordered_quantity,0) and
1480 mol.s5 = 5 then
1481 g_line_rec.invoice_interface_status_code := 'YES';
1482 else
1483 g_line_rec.invoice_interface_status_code := mol.invoice_interface_status_code;
1484 end if;
1485
1486 /* Record insertion into Order Lines table */
1487
1491 org_id,
1488 insert into oe_order_lines_all
1489 (
1490 line_id,
1492 header_id,
1493 line_type_id,
1494 line_number,
1495 ordered_item,
1496 request_date,
1497 promise_date,
1498 schedule_ship_date,
1499 order_quantity_uom,
1500 pricing_quantity,
1501 pricing_quantity_uom,
1502 cancelled_quantity,
1503 shipped_quantity,
1504 ordered_quantity,
1505 fulfilled_quantity,
1506 shipping_quantity,
1507 shipping_quantity_uom,
1508 delivery_lead_time,
1509 tax_exempt_flag,
1510 tax_exempt_number,
1511 tax_exempt_reason_code,
1512 ship_from_org_id,
1513 ship_to_org_id,
1514 invoice_to_org_id,
1515 deliver_to_org_id,
1516 ship_to_contact_id,
1517 deliver_to_contact_id,
1518 invoice_to_contact_id,
1519 sold_to_org_id,
1520 cust_po_number,
1521 ship_tolerance_above,
1522 ship_tolerance_below,
1523 demand_bucket_type_code,
1524 veh_cus_item_cum_key_id,
1525 rla_schedule_type_code,
1526 customer_dock_code,
1527 customer_job,
1528 customer_production_line,
1529 cust_model_serial_number,
1530 project_id,
1531 task_id,
1532 inventory_item_id,
1533 tax_date,
1534 tax_code,
1535 tax_rate,
1536 demand_class_code,
1537 price_list_id,
1538 pricing_date,
1539 shipment_number,
1540 agreement_id,
1541 shipment_priority_code,
1542 shipping_method_code,
1543 freight_carrier_code,
1544 freight_terms_code,
1545 fob_point_code,
1546 tax_point_code,
1547 payment_term_id,
1548 invoicing_rule_id,
1549 accounting_rule_id,
1550 source_document_type_id,
1551 orig_sys_document_ref,
1552 source_document_id,
1553 orig_sys_line_ref,
1554 source_document_line_id,
1555 reference_line_id,
1556 reference_type,
1557 reference_header_id,
1558 item_revision,
1559 unit_selling_price,
1560 unit_list_price,
1561 tax_value,
1562 context,
1563 attribute1,
1564 attribute2,
1565 attribute3,
1566 attribute4,
1567 attribute5,
1568 attribute6,
1569 attribute7,
1570 attribute8,
1571 attribute9,
1572 attribute10,
1573 attribute11,
1574 attribute12,
1575 attribute13,
1576 attribute14,
1577 attribute15,
1578 global_attribute_category,
1579 global_attribute1,
1580 global_attribute2,
1581 global_attribute3,
1582 global_attribute4,
1583 global_attribute5,
1584 global_attribute6,
1585 global_attribute7,
1586 global_attribute8,
1587 global_attribute9,
1588 global_attribute10,
1589 global_attribute11,
1590 global_attribute12,
1591 global_attribute13,
1592 global_attribute14,
1593 global_attribute15,
1594 global_attribute16,
1595 global_attribute17,
1596 global_attribute18,
1597 global_attribute19,
1598 global_attribute20,
1599 pricing_context,
1600 pricing_attribute1,
1601 pricing_attribute2,
1602 pricing_attribute3,
1603 pricing_attribute4,
1604 pricing_attribute5,
1605 pricing_attribute6,
1606 pricing_attribute7,
1607 pricing_attribute8,
1608 pricing_attribute9,
1609 pricing_attribute10,
1610 industry_context,
1611 industry_attribute1,
1612 industry_attribute2,
1613 industry_attribute3,
1614 industry_attribute4,
1615 industry_attribute5,
1616 industry_attribute6,
1617 industry_attribute7,
1618 industry_attribute8,
1619 industry_attribute9,
1620 industry_attribute10,
1621 industry_attribute11,
1622 industry_attribute12,
1623 industry_attribute13,
1624 industry_attribute14,
1625 industry_attribute15,
1626 industry_attribute16,
1627 industry_attribute17,
1628 industry_attribute18,
1629 industry_attribute19,
1630 industry_attribute20,
1631 industry_attribute21,
1632 industry_attribute22,
1633 industry_attribute23,
1634 industry_attribute24,
1635 industry_attribute25,
1636 industry_attribute26,
1637 industry_attribute27,
1638 industry_attribute28,
1639 industry_attribute29,
1640 industry_attribute30,
1641 creation_date,
1642 created_by,
1643 last_update_date,
1644 last_updated_by,
1645 last_update_login,
1646 program_application_id,
1647 program_id,
1648 program_update_date,
1649 request_id,
1650 top_model_line_id,
1651 link_to_line_id,
1652 component_sequence_id,
1653 component_code,
1654 config_display_sequence,
1655 sort_order,
1656 item_type_code,
1657 option_number,
1658 option_flag,
1659 dep_plan_required_flag,
1660 visible_demand_flag,
1661 line_category_code,
1662 actual_shipment_date,
1663 customer_trx_line_id,
1664 return_context,
1665 return_attribute1,
1666 return_attribute2,
1667 return_attribute3,
1668 return_attribute4,
1669 return_attribute5,
1670 return_attribute6,
1671 return_attribute7,
1672 return_attribute8,
1673 return_attribute9,
1674 return_attribute10,
1675 return_attribute11,
1676 return_attribute12,
1677 return_attribute13,
1678 return_attribute14,
1679 return_attribute15,
1680 intmed_ship_to_org_id,
1681 intmed_ship_to_contact_id,
1682 actual_arrival_date,
1683 ato_line_id,
1684 auto_selected_quantity,
1685 component_number,
1686 earliest_acceptable_date,
1687 explosion_date,
1688 latest_acceptable_date,
1689 model_group_number,
1690 schedule_arrival_date,
1691 ship_model_complete_flag,
1692 schedule_status_code,
1693 return_reason_code,
1694 salesrep_id,
1695 split_from_line_id,
1696 cust_production_seq_num,
1697 authorized_to_ship_flag,
1698 invoice_interface_status_code,
1699 ship_set_id,
1700 arrival_set_id,
1704 ordered_item_id,
1701 over_ship_reason_code,
1702 over_ship_resolved_flag,
1703 shipping_interfaced_flag,
1705 item_identifier_type,
1706 configuration_id,
1707 credit_invoice_line_id,
1708 source_type_code,
1709 planning_priority,
1710 open_flag,
1711 booked_flag,
1712 fulfilled_flag,
1713 service_txn_reason_code,
1714 service_txn_comments,
1715 service_duration,
1716 service_start_date,
1717 service_end_date,
1718 service_coterminate_flag,
1719 unit_selling_percent,
1720 unit_list_percent,
1721 unit_percent_base_price,
1722 service_number,
1723 service_period,
1724 order_source_id,
1725 tp_context,
1726 tp_attribute1,
1727 tp_attribute2,
1728 tp_attribute3,
1729 tp_attribute4,
1730 tp_attribute5,
1731 tp_attribute6,
1732 tp_attribute7,
1733 tp_attribute8,
1734 tp_attribute9,
1735 tp_attribute10,
1736 tp_attribute11,
1737 tp_attribute12,
1738 tp_attribute13,
1739 tp_attribute14,
1740 tp_attribute15,
1741 flow_status_code,
1742 re_source_flag,
1743 service_reference_type_Code,
1744 service_reference_line_id,
1745 service_reference_system_id,
1746 calculate_price_flag,
1747 marketing_source_code_id,
1748 shippable_flag,
1749 fulfillment_method_code,
1750 revenue_amount,
1751 fulfillment_date,
1752 cancelled_flag,
1753 sold_from_org_id,
1754 commitment_id,
1755 end_item_unit_number,
1756 mfg_component_sequence_id,
1757 config_header_id,
1758 config_rev_nbr,
1759 packing_instructions,
1760 shipping_instructions,
1761 invoiced_quantity,
1762 reference_customer_trx_line_id,
1763 split_by,
1764 line_set_id,
1765 orig_sys_shipment_ref,
1766 change_sequence,
1767 drop_ship_flag,
1768 customer_line_number,
1769 customer_shipment_number,
1770 customer_item_net_price,
1771 customer_payment_term_id,
1772 first_ack_code,
1773 last_ack_code,
1774 first_ack_date,
1775 last_ack_date,
1776 model_remnant_flag,
1777 upgraded_flag
1778 )
1779 values
1780 (
1781 g_line_id, /* LINE_ID */
1782 g_line_rec.org_id, /* ORG_ID */
1783 g_line_rec.header_id, /* HEADER_ID */
1784 g_line_rec.line_type_id, /* LINE_TYPE_ID, */
1785 g_line_rec.line_number, /* LINE_NUMBER */
1786 -- g_last_line_number, /* LINE_NUMBER */
1787 null, /* ordered_item, */
1788 g_line_rec.date_requested_current, /* REQUEST_DATE */
1789 g_line_rec.promise_date, /* PROMISE_DATE */
1790 g_line_rec.schedule_date, /* SCHEDULE_SHIP_DATE */
1791 nvl(r_uom_code,g_line_rec.unit_code), /* ORDER_QUANTITY_UOM */
1792 g_line_rec.ordered_quantity, /* PRICING_QUANTITY */
1793 nvl(r_uom_code,g_line_rec.unit_code), /* PRICING_QUANTITY_UOM */
1794 decode(g_hdr_canc_flag,'Y', g_line_rec.cancelled_quantity,
1795 decode(g_line_id_Change_flag,'Y',
1796 g_line_rec.cancelled_quantity,0)), /* CANCELLED_QUANTITY */
1797 g_line_rec.shipped_quantity, /* SHIPPED_QUANTITY */
1798 decode(g_hdr_canc_flag,'Y',0,
1799 nvl(g_line_rec.ordered_quantity,0)), /* ORDERED_QUANTITY */
1800 g_line_rec.fulfilled_quantity, /* FULFILLED_QUANTITY */
1801 g_line_rec.shipped_quantity, /* SHIPPING_QUANTITY */
1802 nvl(r_uom_code,
1803 g_line_rec.shipping_quantity_uom), /* SHIPPING_QUANTITY_UOM */
1804 null, /* DELIVERY_LEAD_TIME */
1805 G_TAX_EXEMPT_FLAG, /* TAX_EXEMPT_FLAG */
1806 g_line_rec.tax_exempt_number, /* TAX_EXEMPT_NUMBER */
1810 G_INVOICE_TO_SITE_USE_ID, /* INVOICE_TO_ORG_ID */
1807 g_line_rec.tax_exempt_reason_code, /* TAX_EXEMPT_REASON_CODE */
1808 g_line_rec.warehouse_id, /* SHIP_FROM_ORG_ID */
1809 g_line_rec.ship_to_site_use_id, /* SHIP_TO_ORG_ID */
1811 null, /* DELIVER_TO_ORG_ID */
1812 g_line_rec.ship_to_contact_id, /* SHIP_TO_CONTACT_ID */
1813 null, /* DELIVER_TO_CONTACT_ID */
1814 null, /* INVOICE_TO_CONTACT_ID */
1815 G_CUSTOMER_ID, /* SOLD_TO_ORG_ID */
1816 decode(G_AUTO_FLAG,'Y',
1817 g_line_rec.industry_attribute3,
1818 G_PURCHASE_ORDER_NUM), /* CUST_PO_NUMBER */
1819 null, /* SHIP_TOLERANCE_ABOVE */
1820 null, /* SHIP_TOLERANCE_BELOW */
1821 decode(G_AUTO_FLAG,'Y','DAY',NULL), /* DEMAND_BUCKET_TYPE_CODE */
1822 decode(G_AUTO_FLAG,'Y',-1,NULL), /* VEH_CUS_ITEM_CUM_KEY_ID */
1823 g_line_rec.rla_schedule_type_code, /* RLA_SCHEDULE_TYPE_CODE */
1824 g_line_rec.customer_dock_code, /* CUSTOMER_DOCK_CODE */
1825 g_line_rec.customer_job, /* CUSTOMER_JOB */
1826 g_line_rec.customer_production_line, /* CUSTOMER_PRODUCTION_LINE */
1827 g_line_rec.customer_model_serial_number, /* CUST_MODEL_SERIAL_NUMBER */
1828 g_line_rec.project_id, /* PROJECT_ID */
1829 g_line_rec.task_id, /* TASK_ID */
1830 nvl(r_inventory_item_id,g_line_rec.inventory_item_id), /* INVENTORY_ITEM_ID */
1831 null, /* TAX_DATE */
1832 g_line_rec.tax_code, /* TAX_CODE */
1833 null, /* TAX_RATE */
1834 g_line_rec.demand_class_code, /* DEMAND_CLASS_CODE */
1835 g_line_rec.price_list_id, /* PRICE_LIST_ID */
1836 null, /* PRICING_DATE */
1837 r_shipment_number, /* SHIPMENT_NUMBER */
1838 g_line_rec.agreement_id, /* AGREEMENT_ID */
1839 g_line_rec.shipment_priority_code, /* SHIPMENT_PRIORITY_CODE */
1840 null, /* SHIPPPING_METHOD_CODE */
1841 g_line_rec.ship_method_code, /* FREIGHT_CARRIER_CODE */
1842 G_freight_terms_code, /* FREIGHT_TERMS_CODE */
1843 null, /* FOB_POINT_CODE */
1844 'INVOICE', /* TAX_POINT_CODE */
1845 G_terms_id, /* PAYMENT_TERM_ID */
1846 nvl(g_line_rec.invoicing_rule_id,0), /* INVOICING_RULE_ID */
1847 nvl(g_line_rec.accounting_rule_id,0), /* ACCOUNTING_RULE_ID */
1848 g_line_rec.source_document_type_id, /* SOURCE_DOCUMENT_TYPE_ID */
1849 G_ORIG_SYS_DOCUMENT_REF, /* ORIG_SYS_DOCUMENT_REF */
1850 g_line_rec.source_document_id, /* SOURCE_DOCUMENT_ID */
1851 g_line_rec.original_system_line_reference, /* ORIG_SYS_LINE_REFERENCE */
1852 g_line_rec.source_document_line_id, /* SOURCE_DOCUMENT_LINE_ID */
1853 v_reference_line_id, /* REFERENCE_LINE_ID */
1854 g_line_rec.return_reference_type_code, /* REFERENCE_TYPE */
1855 v_reference_header_id, /* REFERENCE_HEADER_ID */
1856 null, /* ITEM_REVISION */
1857 g_line_rec.selling_price, /* SELLING_PRICE */
1858 g_line_rec.list_price, /* LIST_PRICE */
1859 null, /* TAX_VALUE */
1860 g_line_rec.context, /* CONTEXT */
1861 g_line_rec.attribute1, /* ATTRIBUTE1 */
1862 g_line_rec.attribute2, /* ATTRIBUTE2 */
1863 g_line_rec.attribute3, /* ATTRIBUTE3 */
1864 g_line_rec.attribute4, /* ATTRIBUTE4 */
1865 g_line_rec.attribute5, /* ATTRIBUTE5 */
1866 g_line_rec.attribute6, /* ATTRIBUTE6 */
1867 g_line_rec.attribute7, /* ATTRIBUTE7 */
1868 g_line_rec.attribute8, /* ATTRIBUTE8 */
1869 g_line_rec.attribute9, /* ATTRIBUTE9 */
1870 g_line_rec.attribute10, /* ATTRIBUTE10 */
1871 g_line_rec.attribute11, /* ATTRIBUTE11 */
1872 g_line_rec.attribute12, /* ATTRIBUTE12 */
1873 g_line_rec.attribute13, /* ATTRIBUTE13 */
1874 g_line_rec.attribute14, /* ATTRIBUTE14 */
1875 g_line_rec.attribute15, /* ATTRIBUTE15 */
1876 g_line_rec.global_attribute_category, /* GLOBAL_ATTRIBUTE_CATEGORY */
1880 g_line_rec.global_attribute4, /* GLOBAL_ATTRIBUTE4 */
1877 g_line_rec.global_attribute1, /* GLOBAL_ATTRIBUTE1 */
1878 g_line_rec.global_attribute2, /* GLOBAL_ATTRIBUTE2 */
1879 g_line_rec.global_attribute3, /* GLOBAL_ATTRIBUTE3 */
1881 g_line_rec.global_attribute5, /* GLOBAL_ATTRIBUTE5 */
1882 g_line_rec.global_attribute6, /* GLOBAL_ATTRIBUTE6 */
1883 g_line_rec.global_attribute7, /* GLOBAL_ATTRIBUTE7 */
1884 g_line_rec.global_attribute8, /* GLOBAL_ATTRIBUTE8 */
1885 g_line_rec.global_attribute9, /* GLOBAL_ATTRIBUTE9 */
1886 g_line_rec.global_attribute10, /* GLOBAL_ATTRIBUTE10 */
1887 g_line_rec.global_attribute11, /* GLOBAL_ATTRIBUTE11 */
1888 g_line_rec.global_attribute12, /* GLOBAL_ATTRIBUTE12 */
1889 g_line_rec.global_attribute13, /* GLOBAL_ATTRIBUTE13 */
1890 g_line_rec.global_attribute14, /* GLOBAL_ATTRIBUTE14 */
1891 g_line_rec.global_attribute15, /* GLOBAL_ATTRIBUTE15 */
1892 g_line_rec.global_attribute16, /* GLOBAL_ATTRIBUTE16 */
1893 g_line_rec.global_attribute17, /* GLOBAL_ATTRIBUTE17 */
1894 g_line_rec.global_attribute18, /* GLOBAL_ATTRIBUTE18 */
1895 g_line_rec.global_attribute19, /* GLOBAL-ATTRIBUTE19 */
1896 g_line_rec.global_attribute20, /* GLOBAL_ATTRIBUTE20 */
1897 g_line_rec.pricing_context, /* PRICING_CONTEXT */
1898 g_line_rec.pricing_attribute1, /* PRICING_ATTRIBUTE1 */
1899 g_line_rec.pricing_attribute2, /* PRICING_ATTRIBUTE2 */
1900 g_line_rec.pricing_attribute3, /* PRICING_ATTRIBUTE3 */
1901 g_line_rec.pricing_attribute4, /* PRICING_ATTRIBUTE4 */
1902 g_line_rec.pricing_attribute5, /* PRICING_ATTRIBUTE5 */
1903 g_line_rec.pricing_attribute6, /* PRICING_ATTRIBUTE6 */
1904 g_line_rec.pricing_attribute7, /* PRICING_ATTRIBUTE7 */
1905 g_line_rec.pricing_attribute8, /* PRICING_ATTRIBUTE8 */
1906 g_line_rec.pricing_attribute9, /* PRICING_ATTRIBUTE9 */
1907 g_line_rec.pricing_attribute10, /* PRICING_ATTRIBUTE10*/
1908 g_line_rec.industry_context, /* INDUSTRY_CONTEXT */
1909 g_line_rec.industry_attribute1, /* INDUSTRY_ATTRIBUTE1 */
1910 decode(g_auto_Flag,'Y',
1911 NULL,g_line_rec.industry_attribute2), /* INDUSTRY_ATTRIBUTE2 */
1912 decode(g_auto_Flag,'Y',
1913 NULL,g_line_rec.industry_attribute3), /* INDUSTRY_ATTRIBUTE3 */
1914 decode(g_auto_Flag,'Y',
1915 NULL,g_line_rec.industry_attribute4), /* INDUSTRY_ATTRIBUTE4 */
1916 decode(g_auto_Flag,'Y',
1917 NULL,g_line_rec.industry_attribute5), /* INDUSTRY_ATTRIBUTE5 */
1918 decode(g_auto_Flag,'Y',
1919 NULL,g_line_rec.industry_attribute6), /* INDUSTRY_ATTRIBUTE6 */
1920 decode(g_auto_Flag,'Y',
1921 NULL,g_line_rec.industry_attribute7), /* INDUSTRY_ATTRIBUTE7 */
1922 decode(g_auto_Flag,'Y',
1923 NULL,g_line_rec.industry_attribute8), /* INDUSTRY_ATTRIBUTE8 */
1924 decode(g_auto_Flag,'Y',
1925 NULL,g_line_rec.industry_attribute9), /* INDUSTRY_ATTRIBUTE9 */
1926 decode(g_auto_Flag,'Y',
1927 NULL,g_line_rec.industry_attribute10), /* INDUSTRY_ATTRIBUTE10 */
1928 decode(g_auto_Flag,'Y',
1929 NULL,g_line_rec.industry_attribute11), /* INDUSTRY_ATTRIBUTE11 */
1930 decode(g_auto_Flag,'Y',
1931 NULL,g_line_rec.industry_attribute12), /* INDUSTRY_ATTRIBUTE12 */
1932 decode(g_auto_Flag,'Y',
1933 NULL,g_line_rec.industry_attribute13), /* INDUSTRY_ATTRIBUTE13 */
1934 decode(g_auto_Flag,'Y',
1935 NULL,g_line_rec.industry_attribute14), /* INDUSTRY_ATTRIBUTE14 */
1936 decode(g_auto_Flag,'Y',
1937 NULL,g_line_rec.industry_attribute15), /* INDUSTRY_ATTRIBUTE15 */
1938 NULL, /* INDUSTRY_ATTRIBUTE16 */
1939 NULL, /* INDUSTRY_ATTRIBUTE17 */
1940 NULL, /* INDUSTRY_ATTRIBUTE18 */
1941 NULL, /* INDUSTRY_ATTRIBUTE19 */
1942 NULL, /* INDUSTRY_ATTRIBUTE20 */
1943 NULL, /* INDUSTRY_ATTRIBUTE21 */
1944 NULL, /* INDUSTRY_ATTRIBUTE22 */
1945 NULL, /* INDUSTRY_ATTRIBUTE23 */
1946 NULL, /* INDUSTRY_ATTRIBUTE24 */
1947 NULL, /* INDUSTRY_ATTRIBUTE25 */
1948 NULL, /* INDUSTRY_ATTRIBUTE26 */
1952 NULL, /* INDUSTRY_ATTRIBUTE30 */
1949 NULL, /* INDUSTRY_ATTRIBUTE27 */
1950 NULL, /* INDUSTRY_ATTRIBUTE28 */
1951 NULL, /* INDUSTRY_ATTRIBUTE29 */
1953 g_line_rec.creation_date, /* CREATION_DATE */
1954 g_line_rec.created_by, /* CREATED_BY */
1955 g_line_rec.last_update_date, /* LAST_UPDATE_DATE */
1956 g_line_rec.last_updated_by, /* LAST_UPDATED_BY */
1957 g_line_rec.last_update_login, /* LAST_UPDATE_LOGIN */
1958 nvl(g_line_rec.program_application_id,0), /* PROGRAM_APPLICATION_ID */
1959 g_line_rec.program_id, /* PROGRAM_ID */
1960 g_line_rec.program_update_date, /* PROGRAM_UPDATE_DATE */
1961 g_line_rec.request_id, /* REQUEST_ID */
1962 g_line_rec.top_model_line_id, /* TOP_MODEL_LINE_ID */
1963 g_line_rec.link_to_line_id, /* LINK_TO_LINE_ID */
1964 g_line_rec.component_sequence_id, /* COMPONENT_SEQUENCE_ID */
1965 g_line_rec.component_code, /* COMPONENT_CODE */
1966 null, /* CONFIG_DISPLAY_SEQUENCE */
1967 null, /* SORT_ORDER, */
1968 g_line_rec.item_type_code, /* ITEM_TYPE_CODE */
1969 null, /* OPTION_NUMBER */
1970 g_line_rec.option_flag, /* OPTION_FLAG, */
1971 g_line_rec.dep_plan_required_flag, /* DEP_PLAN_REQUIRED_FLAG */
1972 g_line_rec.visible_demand_flag, /* VISIBLE_DEMAND_FLAG */
1973 g_line_rec.line_category_code, /* LINE_CATEGORY_CODE */
1974 g_line_rec.actual_departure_date, /* ACTUAL_SHIPMENT_DATE */
1975 decode(g_line_rec.line_type_code,'RETURN',
1976 decode(g_line_rec.return_reference_type_code,
1977 'INVOICE',g_line_rec.return_reference_id,NULL), NULL), /* CUSTOMER_TRX_LINE_ID */
1978 g_line_rec.return_reference_type_Code, /* RETURN_CONTEXT */
1979 decode(g_line_rec.line_type_code,'RETURN',
1980 decode(g_line_rec.return_reference_type_code,
1981 'INVOICE',v_customer_trx_id,
1982 v_reference_header_id),NULL), /* RETURN_ATTRIBUTE1 */
1983 decode(g_line_rec.line_type_code,'RETURN',
1984 decode(g_line_rec.return_reference_type_code,
1985 'INVOICE',g_line_rec.return_reference_id,
1986 v_reference_line_id),NULL), /* RETURN_ATTRIBUTE2 */
1987 NULL, /* RETURN_ATTRIBUTE3 */
1988 NULL, /* RETURN_ATTRIBUTE4 */
1989 null, /* RETURN_ATTRIBUTE5 */
1990 null, /* RETURN_ATTRIBUTE6 */
1991 null, /* RETURN_ATTRIBUTE7 */
1992 null, /* RETURN_ATTRIBUTE8 */
1993 null, /* RETURN_ATTRIBUTE9 */
1994 null, /* RETURN_ATTRIBUTE10 */
1995 null, /* RETURN_ATTRIBUTE11 */
1996 null, /* RETURN_ATTRIBUTE12 */
1997 null, /* RETURN_ATTRIBUTE13 */
1998 null, /* RETURN_ATTRIBUTE14 */
1999 null, /* RETURN_ATTRIBUTE15 */
2000 g_line_rec.intermediate_ship_to_id, /* intmed_ship_to_org_id, */
2001 g_line_rec.ship_to_contact_id, /* intmed_ship_to_contact_id, */
2002 null, /* actual_arrival_date, */
2003 g_line_rec.ato_line_id, /* ATO_LINE_ID */
2004 null, /* auto_selected_quantity, */
2005 null, /* component_number, */
2006 null, /* earliest_acceptable_date, */
2007 g_line_rec.standard_component_freeze_date, /* explosion_date, */
2008 g_line_rec.latest_acceptable_date, /* latest_acceptable_date, */
2009 null, /* model_group_number, */
2010 null, /* schedule_arrival_date, */
2011 g_line_rec.ship_model_complete_flag, /* ship_model_complete_flag, */
2012 g_line_rec.schedule_status_code, /* schedule_status_code, */
2013 g_line_rec.transaction_reason_code, /* return_reason_code */
2014 g_salesrep_id, /* salesrep_id */
2015 null, /* split_from_line_id */
2016 g_line_rec.planning_prod_seq_number, /* cust_production_seq_num */
2020 null, /* Arrival_Set_Id */
2017 decode(G_AUTO_FLAG,'Y','Y',NULL), /* authorized_to_ship_flag */
2018 g_line_rec.invoice_interface_status_code, /* invoice_interface_status_code */
2019 g_set_id, /* Ship_Set_Id */
2021 null, /* over_ship_reason_code */
2022 null, /* over_ship_resolved_flag */
2023 g_line_rec.shipping_interfaced_flag, /* shipping_interfaced_flag */
2024 decode(nvl(g_line_rec.customer_item_id,-1),
2025 -1,g_line_rec.inventory_item_id,
2026 g_line_rec.customer_item_id), /* ordered_item_id */
2027 decode(nvl(g_line_rec.customer_item_id,-1),
2028 -1,'INT','CUST'), /* item_identifier_type*/
2029 null, /* configuration_id */
2030 g_line_rec.credit_invoice_line_id, /* credit_invoice_line_id */
2031 g_line_rec.source_type_code, /* source_type_code */
2032 g_line_rec.planning_priority, /* planning_priority */
2033 g_line_rec.open_flag, /* open_flag */
2034 g_line_rec.booked_flag, /* booked_flag */
2035 g_line_rec.fulfilled_flag, /* fulfilled_Flag */
2036 g_line_rec.service_txn_reason_code, /* service_txn_reason_code */
2037 g_line_rec.service_txn_comments, /* service_txn_comments */
2038 g_line_rec.service_duration, /* service_duration */
2039 g_line_rec.service_start_date, /* service_start_date */
2040 g_line_rec.service_end_date, /* service_end_date */
2041 g_line_rec.service_coterminate_flag, /* service_coterminate_flag */
2042 g_line_rec.selling_percent, /* unit_selling_percent */
2043 g_line_rec.list_percent, /* unit_list_percent */
2044 g_line_rec.percent_base_price, /* unit_percent_base_price */
2045 g_line_rec.service_number, /* service_number */
2046 g_line_rec.service_period, /* service_period */
2047 nvl(G_ORDER_SOURCE_ID,0), /* ORDER_SOURCE_ID, */
2048 g_line_rec.tp_context, /* tp_context */
2049 g_line_rec.tp_attribute1, /* tp_attribute1 */
2050 g_line_rec.tp_attribute2, /* tp_attribute2 */
2051 g_line_rec.tp_attribute3, /* tp_attribute3 */
2052 g_line_rec.tp_attribute4, /* tp_attribute4 */
2053 g_line_rec.tp_attribute5, /* tp_attribute5 */
2054 g_line_rec.tp_attribute6, /* tp_attribute6 */
2055 g_line_rec.tp_attribute7, /* tp_attribute7 */
2056 g_line_rec.tp_attribute8, /* tp_attribute8 */
2057 g_line_rec.tp_attribute9, /* tp_attribute9 */
2058 g_line_rec.tp_attribute10, /* tp_attribute10 */
2059 g_line_rec.tp_attribute11, /* tp_attribute11 */
2060 g_line_rec.tp_attribute12, /* tp_attribute12 */
2061 g_line_rec.tp_attribute13, /* tp_attribute13 */
2062 g_line_rec.tp_attribute14, /* tp_attribute14 */
2063 g_line_rec.tp_attribute15, /* tp_attribute15 */
2064 g_line_rec.flow_status_code, /* flow_status_code */
2065 g_line_rec.re_source_flag, /* re_source_flag */
2066 g_line_rec.service_reference_type_Code, /* service_reference_type_code */
2067 g_line_rec.service_reference_line_id, /* service_reference_line_id */
2068 g_line_rec.service_reference_system_id, /* service_reference_system_id */
2069 g_line_rec.calculate_price_flag, /* calculate_price_flag */
2070 g_line_rec.marketing_source_code_id, /* marketing_source_code_id */
2071 g_line_rec.shippable_flag, /* shippable_flag */
2072 g_line_rec.fulfillment_method_code, /* fulfillment_method_code */
2073 g_line_rec.revenue_amount, /* revenue_amount */
2074 g_line_rec.fulfillment_date, /* fulfillment_date */
2075 g_line_rec.cancelled_flag, /* cancelled_flag */
2076 g_line_rec.org_id, /* sold_from_org_id */
2077 null, /* commitment_id */
2078 null, /* end_item_unit_number */
2079 null, /* mfg_component_sequence_id */
2080 null, /* config_header_id */
2081 null, /* config_rev_nbr */
2082 null, /* packing_instructions */
2083 null, /* shipping_instructions */
2084 g_line_rec.invoiced_quantity, /* invoiced_quantity */
2085 null, /* reference_customer_trx_line_id */
2086 null, /* split_by */
2087 nvl(r_line_set_id,null), /* line_set_id */
2088 null, /* orig_sys_shipment_ref */
2089 null, /* change_sequence */
2090 null, /* drop_ship_flag */
2091 null, /* customer_line_number */
2092 null, /* customer_shipment_number */
2093 null, /* customer_item_net_price */
2094 null, /* customer_payment_term_id */
2095 null, /* first_ack_code */
2096 null, /* last_ack_code */
2097 null, /* first_ack_date */
2098 null, /* last_ack_date */
2099 null, /* model_remnant_flag */
2100 nvl(r_upgraded_flag,'Y') /* upgraded_flag */
2101 );
2102
2103 r_lctr := r_lctr + 1;
2104
2105 G_ERROR_LOCATION := 709;
2106
2107 G_Log_Rec.Header_Id := g_header_id;
2108 G_Log_Rec.Old_Line_Id := mol.line_id;
2109 G_Log_Rec.picking_Line_Id := mol.picking_line_id;
2110 G_Log_Rec.Old_Line_Detail_id := mol.line_detail_id;
2111 G_Log_Rec.Delivery := mol.Delivery;
2112
2113 G_Log_Rec.New_Line_ID := g_line_id; -- 2/24/2000
2114 g_log_rec.return_qty_available := g_line_rec.ordered_quantity; -- 2/24/2000
2115
2116 G_Log_Rec.New_Line_Number := g_last_line_number;
2117 G_Log_Rec.mtl_sales_order_id := g_mtl_sales_order_id;
2118
2119 OE_UPG_SO.Upgrade_Insert_Upgrade_Log;
2120
2121 g_log_rec.comments := NULL;
2122
2123 begin
2124
2125 IF v_return_lctr = 4 THEN
2126 -- Update PO Tables
2127 -- rcv_shipment_lines, rcv_transactions,rcv_supply
2128 update rcv_shipment_lines
2129 set oe_order_line_id = v_return_created_line_id
2130 where oe_order_line_id = g_old_line_id;
2131
2132 update rcv_transactions
2133 set oe_order_line_id = v_return_created_line_id
2134 where oe_order_line_id = g_old_line_id;
2135
2136 update rcv_supply
2137 set oe_order_line_id = v_return_created_line_id
2138 where oe_order_line_id = g_old_line_id;
2139 END IF;
2140 exception
2141 when others then
2142 oe_upg_so.upgrade_insert_errors
2143 (
2144 L_header_id => g_header_id,
2145 L_comments => 'Updating RCV shipment failed for line :'
2146 ||to_char(g_old_line_id)||' with oracle error ORA-'
2147 ||to_char(sqlcode));
2148 end;
2149
2150 /* ========== Line Level Sales Credits =========== */
2151 OE_Upg_SO.Upgrade_Sales_Credits
2152 ( L_level_flag => 'L');
2153
2154 /* ========== Line Level Price Adjustments =========== */
2155 OE_Upg_SO.Upgrade_Price_Adjustments
2156 ( L_level_flag => 'L');
2157
2158 if OE_Upg_SO.G_HDR_CANC_FLAG = 'Y' then
2159 G_ERROR_LOCATION := 710;
2160 g_canc_rec := g_hdr_canc_rec;
2161 G_ORD_CANC_FLAG := 'N';
2162 OE_Upg_SO.Upgrade_Insert_Lines_History;
2163 end if;
2164
2165 if v_bal_return_quantity <= 0 or v_line_exit_flag = 1 then
2166 exit;
2167 end if;
2168
2169 end loop; /* extra loop for returns */
2170
2171 /* ========== Line Level Cancellations =========== */
2172 /* if nvl(mol.cancelled_quantity,0) > 0 and OE_Upg_SO.G_HDR_CANC_FLAG <> 'Y' then */
2173
2174 if g_line_id_Change_flag = 'Y' then
2175 if nvl(mol.cancelled_quantity,0) > 0 then
2176 OE_Upg_SO.Upgrade_Cancellations ;
2177 end if;
2178 end if;
2179
2180 end loop; /* end loop for Order lines*/
2181 close ol;
2182 G_ERROR_LOCATION := 8;
2183
2184 End Upgrade_Create_Order_Lines;
2185
2186 Procedure Upgrade_Create_Order_Headers
2187 ( L_Line_Type IN varchar2,
2188 L_Slab IN number )
2189 is
2190 commit_counter number;
2191 v_start_header number;
2192 v_end_header number;
2193
2194 /* The oh cursor is declared to bring Order Headers details */
2195
2196 cursor oh is
2197 select
2198 sha.header_id,
2199 sha.org_id,
2200 sha.order_type_id,
2201 sha.order_number,
2202 sha.credit_card_expiration_date,
2203 sha.original_system_source_code,
2204 sha.original_system_reference,
2205 sha.date_ordered,
2206 sha.date_requested_current,
2207 sha.shipment_priority_code,
2208 sha.demand_class_code,
2209 sha.price_list_id,
2210 sha.tax_exempt_flag,
2211 sha.tax_exempt_num tax_exempt_num,
2212 sha.tax_exempt_reason_code,
2213 sha.conversion_rate,
2214 sha.conversion_type_code,
2215 sha.conversion_date,
2216 sha.ship_partial_flag,
2217 sha.currency_code,
2218 sha.agreement_id,
2219 sha.purchase_order_num,
2220 sha.invoicing_rule_id,
2221 sha.accounting_rule_id,
2222 sha.terms_id,
2223 sha.ship_method_code,
2224 sha.fob_code,
2225 sha.freight_terms_code,
2226 sha.ship_to_contact_id,
2227 sha.invoice_to_contact_id,
2228 sha.creation_date,
2229 sha.created_by,
2230 sha.last_updated_by,
2231 sha.last_update_date,
2232 sha.last_update_login,
2233 sha.program_application_id,
2234 sha.program_id,
2235 sha.program_update_date,
2236 sha.request_id,
2237 sha.customer_id,
2238 sha.salesrep_id,
2239 sha.cancelled_flag,
2240 sha.context,
2241 sha.attribute1,
2242 sha.attribute2,
2243 sha.attribute3,
2244 sha.attribute4,
2245 sha.attribute5,
2246 sha.attribute6,
2247 sha.attribute7,
2248 sha.attribute8,
2249 sha.attribute9,
2250 sha.attribute10,
2251 sha.attribute11,
2252 sha.attribute12,
2253 sha.attribute13,
2254 sha.attribute14,
2255 sha.attribute15,
2256 sha.payment_type_code,
2257 sha.payment_amount,
2258 sha.check_number,
2259 sha.credit_card_code,
2260 sha.credit_card_holder_name,
2261 sha.credit_card_number,
2262 sha.credit_card_approval_code,
2263 shattr.global_attribute_category,
2264 shattr.global_attribute1,
2265 shattr.global_attribute2,
2266 shattr.global_attribute3,
2267 shattr.global_attribute4,
2268 shattr.global_attribute5,
2269 shattr.global_attribute6,
2270 shattr.global_attribute7,
2271 shattr.global_attribute8,
2272 shattr.global_attribute9,
2273 shattr.global_attribute10,
2274 shattr.global_attribute11,
2275 shattr.global_attribute12,
2276 shattr.global_attribute13,
2277 shattr.global_attribute14,
2278 shattr.global_attribute15,
2279 shattr.global_attribute16,
2280 shattr.global_attribute17,
2281 shattr.global_attribute18,
2282 shattr.global_attribute19,
2283 shattr.global_attribute20,
2284 sha.invoice_to_site_use_id,
2285 sha.order_category,
2286 sha.ordered_by_contact_id,
2287 sha.ship_to_site_use_id,
2288 sha.warehouse_id,
2289 nvl(sha.open_flag,'N') open_flag,
2290 decode(sha.s1,1,'Y','N') booked_flag,
2291 sha.s1_date
2292 from
2293 so_headers_all sha,
2294 so_header_attributes shattr
2295 where
2296 sha.upgrade_flag = 'N' and
2297 sha.header_id = shattr.header_id(+) and
2298 /*
2299 (( L_Line_type = 'R' and sha.order_category = 'RMA') or
2300 ( L_Line_type = 'O' and sha.order_category <> 'RMA')) and
2301 */
2302 sha.header_id between v_start_header and v_end_header
2303 order by
2304 sha.header_id;
2305
2306 v_source_code_profile_value varchar2(100);
2307 v_auto_source_code varchar2(30);
2308 v_error_code number;
2309
2310 --
2311 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2312 --
2313 begin
2314 -- dbms_output.enable(999999999);
2315 G_ERROR_LOCATION := 9;
2316 begin
2317 select
2318 start_header_id,
2319 end_header_id
2320 into
2321 v_start_header,
2322 v_end_header
2323 from oe_upgrade_distribution
2324 where slab = L_slab;
2325
2326 /* where nvl(alloted_flag,'N') = 'N'
2327 and nvl(line_type,'O') = L_line_type */
2328
2329 exception
2330 when no_data_found then
2331 oe_upg_so.upgrade_insert_errors
2332 (
2333 L_header_id => 0,
2337 v_end_header := 0;
2334 L_comments => 'FYI Only: Parallel process of Sales Order Upgrade not used for the slab:'||to_char(L_slab)
2335 );
2336 v_start_header := 0;
2338 commit;
2339 return;
2340 end;
2341
2342 commit_counter := 0;
2343
2344 OE_Upg_SO.g_earliest_schedule_limit :=
2345 to_number(FND_PROFILE.VALUE('OE_SCHEDULE_DATE_WINDOW'));
2346
2347 OE_Upg_SO.g_latest_schedule_limit := OE_Upg_SO.g_earliest_schedule_limit;
2348
2349 v_source_code_profile_value := fnd_profile.value('SO_SOURCE_CODE');
2350 v_auto_source_code := NULL;
2351
2352 fnd_profile.get('RLA_ORDERIMPORT_SOURCE',v_auto_source_code);
2353
2354 for moh in oh loop /* start loop for Order Headers */
2355 begin
2356
2357 G_Error_Alert := 'N';
2358 SAVEPOINT HEADER_SAVE_POINT;
2359
2360 -- dbms_output.put_line('Inserting Header ==============>');
2361
2362 G_LOG_Rec := NULL;
2363
2364 G_HEADER_ID := moh.header_id;
2365 G_TAX_EXEMPT_FLAG := moh.tax_exempt_flag;
2366 G_CUSTOMER_ID := moh.customer_id;
2367 G_FREIGHT_TERMS_CODE := moh.freight_terms_code;
2368 G_SOURCE_DOCUMENT_ID := NULL;
2369 G_INVOICE_TO_SITE_USE_ID := moh.invoice_to_site_use_id;
2370 G_PURCHASE_ORDER_NUM := moh.purchase_order_num;
2371 G_SALESREP_ID := moh.salesrep_id;
2372 G_CANCELLED_FLAG := moh.cancelled_flag;
2373 G_ORDER_TYPE_ID := moh.order_type_id;
2374 G_ORIG_SYS_DOCUMENT_REF := moh.original_system_reference;
2375
2376 begin
2377 select
2378 decode(moh.order_category,'P','ORDER',
2379 'R','ORDER','RMA','RETURN','*')
2380 into
2381 G_ORDER_CATEGORY_CODE
2382 from dual;
2383 exception
2384 when others then
2385 G_ORDER_CATEGORY_CODE := NULL;
2386 end;
2387
2388 begin
2389 select 10 into G_ORDER_SOURCE_ID
2390 from so_order_sources
2391 where order_source_id = moh.original_system_source_code
2392 and name = 'Internal';
2393 exception
2394 when no_data_found then
2395 G_ORDER_SOURCE_ID := nvl(moh.original_system_source_code,0);
2396 when others then
2397 G_ORDER_SOURCE_ID := nvl(moh.original_system_source_code,0);
2398 end;
2399
2400 /* Preparing values for COPIED ORDERS */
2401 G_COPIED_FLAG := 'N';
2402
2403 if moh.original_system_source_code = '2' then
2404 G_SOURCE_DOCUMENT_ID := moh.original_system_reference;
2405 G_COPIED_FLAG := 'Y';
2406 end if;
2407
2408
2409 /* Setting Automotive Order Flag */
2410
2411 if moh.original_system_source_code = v_auto_source_code then
2412 G_AUTO_FLAG := 'Y';
2413 else
2414 G_AUTO_FLAG := 'N';
2415 end if;
2416
2417 /* Getting MTL_SALES_ORDER_ID */
2418
2419 begin
2420 select
2421 sales_order_id
2422 into
2423 G_MTL_SALES_ORDER_ID
2424 from
2425 mtl_sales_orders mso,
2426 so_order_types_115_all sota
2427 where segment1 = to_char(moh.order_number)
2428 and segment2 = sota.name
2429 and segment3 = v_source_code_profile_value
2430 and moh.order_type_id = sota.order_type_id
2431 and rownum = 1;
2432 exception
2433 when no_data_found then
2434 G_MTL_SALES_ORDER_ID := NULL;
2435 when others then
2436 G_MTL_SALES_ORDER_ID := NULL;
2437 end;
2438
2439 insert into oe_order_headers_all
2440 (
2441 header_id,
2442 org_id,
2443 order_type_id,
2444 order_number,
2445 version_number,
2446 expiration_date,
2447 order_source_id,
2448 source_document_type_id,
2449 orig_sys_document_ref,
2450 source_document_id,
2451 ordered_date,
2452 request_date,
2453 pricing_date,
2454 shipment_priority_code,
2455 demand_class_code,
2456 price_list_id,
2457 tax_exempt_flag,
2458 tax_exempt_number,
2459 tax_exempt_reason_code,
2460 conversion_rate,
2461 conversion_type_code,
2462 conversion_rate_date,
2466 transactional_curr_code,
2463 partial_shipments_allowed,
2464 ship_tolerance_above,
2465 ship_tolerance_below,
2467 agreement_id,
2468 tax_point_code,
2469 cust_po_number,
2470 invoicing_rule_id,
2471 accounting_rule_id,
2472 payment_term_id,
2473 shipping_method_code,
2474 freight_carrier_code,
2475 fob_point_code,
2476 freight_terms_code,
2477 sold_to_org_id,
2478 ship_from_org_id,
2479 ship_to_org_id,
2480 invoice_to_org_id,
2481 deliver_to_org_id,
2482 sold_to_contact_id,
2483 ship_to_contact_id,
2484 invoice_to_contact_id,
2485 deliver_to_contact_id,
2486 creation_date,
2487 created_by,
2488 last_updated_by,
2489 last_update_date,
2490 last_update_login,
2491 program_application_id,
2492 program_id,
2493 program_update_date,
2494 request_id,
2495 salesrep_id,
2496 return_reason_code,
2497 context,
2498 attribute1,
2499 attribute2,
2500 attribute3,
2501 attribute4,
2502 attribute5,
2503 attribute6,
2504 attribute7,
2505 attribute8,
2506 attribute9,
2507 attribute10,
2508 attribute11,
2509 attribute12,
2510 attribute13,
2511 attribute14,
2512 attribute15,
2513 global_attribute_category,
2514 global_attribute1,
2515 global_attribute2,
2516 global_attribute3,
2517 global_attribute4,
2518 global_attribute5,
2519 global_attribute6,
2520 global_attribute7,
2521 global_attribute8,
2522 global_attribute9,
2523 global_attribute10,
2524 global_attribute11,
2525 global_attribute12,
2526 global_attribute13,
2527 global_attribute14,
2528 global_attribute15,
2529 global_attribute16,
2530 global_attribute17,
2531 global_attribute18,
2532 global_attribute19,
2533 global_attribute20,
2534 order_date_type_code,
2535 earliest_schedule_limit,
2536 latest_schedule_limit,
2537 payment_type_code,
2538 payment_amount,
2539 check_number,
2540 credit_card_code,
2541 credit_card_holder_name,
2542 credit_card_number,
2543 credit_card_expiration_date,
2544 credit_card_approval_code,
2545 credit_card_approval_date,
2546 sales_channel_code,
2547 order_category_code,
2548 cancelled_flag,
2549 open_flag,
2550 booked_flag,
2551 marketing_source_code_id,
2552 tp_context,
2553 tp_attribute1,
2554 tp_attribute2,
2555 tp_attribute3,
2556 tp_attribute4,
2557 tp_attribute5,
2558 tp_attribute6,
2559 tp_attribute7,
2560 tp_attribute8,
2561 tp_attribute9,
2562 tp_attribute10,
2563 tp_attribute11,
2564 tp_attribute12,
2565 tp_attribute13,
2566 tp_attribute14,
2567 tp_attribute15,
2568 flow_status_code,
2569 upgraded_flag,
2570 booked_date,
2571 sold_from_org_id
2572 )
2573 values
2574 (
2575 moh.header_id, /* HEADER_ID */
2576 moh.org_id, /* ORG_ID */
2577 moh.order_type_id, /* ORDER_TYPE_ID */
2578 moh.order_number, /* ORDER_NUMBER */
2579 1, /* VERSION_NUMBER, */
2580 NULL, /* EXPIRATION_DATE, */
2581 G_ORDER_SOURCE_ID, /* ORDER_SOURCE_ID, */
2582 decode(g_copied_flag,'Y',2,NULL), /* SOURCE_DOCUMENT_TYPE_ID, */
2583 moh.original_system_reference, /* ORIG_SYS_DOCUMENT_REF, */
2584 decode(g_copied_flag,'Y',
2585 g_source_document_id,NULL), /* SOURCE_DOCUMENT_ID, */
2589 moh.shipment_priority_code, /* SHIPMENT_PRIORITY_CODE */
2586 moh.date_ordered, /* ORDERED_DATE */
2587 moh.date_requested_current, /* REQUEST_DATE */
2588 null, /* PRICING_DATE, */
2590 moh.demand_class_code, /* DEMAND_CLASS_CODE */
2591 moh.price_list_id, /* PRICE_LIST_ID */
2592 moh.tax_exempt_flag, /* TAX_EXEMPT_FLAG */
2593 moh.tax_exempt_num, /* TAX_EXEMPT_NUMBER */
2594 moh.tax_exempt_reason_code, /* TAX_EXEMPT_REASON_CODE */
2595 moh.conversion_rate, /* CONVERSION_RATE */
2596 moh.conversion_type_code, /* CONVERSION_TYPE_CODE */
2597 moh.conversion_date, /* CONVERSION_RATE_DATE */
2598 moh.ship_partial_flag, /* PARTIAL_SHIPMENTS_ALLOWED */
2599 null, /* SHIP_TOLERANCE_ABOVE, */
2600 null, /* SHIP_TOLERANCE_BELOW, */
2601 moh.currency_code, /* TRANSACTIONAL_CURR_CODE */
2602 moh.agreement_id, /* AGREEMENT_ID */
2603 'INVOICE', /* TAX_POINT_CODE */
2604 decode(G_AUTO_FLAG,'Y',
2605 g_line_rec.industry_attribute3,
2606 G_PURCHASE_ORDER_NUM), /* CUST_PO_NUMBER */
2607 nvl(moh.invoicing_rule_id,0), /* INVOICING_RULE_ID */
2608 nvl(moh.accounting_rule_id,0), /* ACCOUNTING_RULE_ID */
2609 nvl(moh.terms_id,0), /* PAYMENT_TERM_ID */
2610 moh.ship_method_code, /* SHIPING_METHOD_CODE */
2611 null, /* FREIGHT_CARRIER_CODE */
2612 moh.fob_code, /* FOB_POINT_CODE */
2613 moh.freight_terms_code, /* FREIGHT_TERMS_CODE */
2614 G_customer_id, /* SOLD_TO_ORG_ID */
2615 moh.warehouse_id, /* SHIP_FROM_ORG_ID */
2616 moh.ship_to_site_use_id, /* SHIP_TO_ORG_ID */
2617 moh.invoice_to_site_use_id, /* INVOICE_TO_ORG_ID */
2618 null, /* DELIVER_TO_ORG_ID */
2619 moh.ordered_by_contact_id, /* SOLD_TO_CONTACT_ID */
2620 moh.ship_to_contact_id, /* SHIP_TO_CONTACT_ID */
2621 moh.invoice_to_contact_id, /* INVOICE_TO_CONTACT_ID */
2622 null, /* DELIVER_TO_CONTACT_ID */
2623 moh.creation_date, /* CREATION_DATE */
2624 moh.created_by, /* CREATED_BY */
2625 moh.last_updated_by, /* LAST_UPDATED_BY */
2626 moh.last_update_date, /* LAST_UPDATE_DATE */
2627 moh.last_update_login, /* LAST_UPDATE_LOGIN */
2628 nvl(moh.program_application_id,0), /* PROGRAM_APPLICATION_ID */
2629 moh.program_id, /* PROGRAM_ID */
2630 moh.program_update_date, /* PROGRAM_UPDATE_DATE */
2631 moh.request_id, /* REQUEST_ID */
2632 moh.salesrep_id, /* SALESREP_ID */
2633 null, /* RETURN_REASON_CODE */
2634 moh.context, /* CONTEXT */
2635 moh.attribute1, /* ATTRIBUTE1 */
2636 moh.attribute2, /* ATTRIBUTE2 */
2637 moh.attribute3, /* ATTRIBUTE3 */
2638 moh.attribute4, /* ATTRIBUTE4 */
2639 moh.attribute5, /* ATTRIBUTE5 */
2640 moh.attribute6, /* ATTRIBUTE6 */
2641 moh.attribute7, /* ATTRIBUTE7 */
2642 moh.attribute8, /* ATTRIBUTE8 */
2643 moh.attribute9, /* ATTRIBUTE9 */
2644 moh.attribute10, /* ATTRIBUTE10 */
2645 moh.attribute11, /* ATTRIBUTE11 */
2646 moh.attribute12, /* ATTRIBUTE12 */
2647 moh.attribute13, /* ATTRIBUTE13 */
2648 moh.attribute14, /* ATTRIBUTE14 */
2649 moh.attribute15, /* ATTRIBUTE15 */
2650 moh.global_attribute_category, /* GLOBAL_ATTRIBUTE_CATEGORY */
2651 moh.global_attribute1, /* GLOBAL_ATTRIBUTE1 */
2652 moh.global_attribute2, /* GLOBAL_ATTRIBUTE2 */
2653 moh.global_attribute3, /* GLOBAL_ATTRIBUTE3 */
2654 moh.global_attribute4, /* GLOBAL_ATTRIBUTE4 */
2655 moh.global_attribute5, /* GLOBAL_ATTRIBUTE5 */
2656 moh.global_attribute6, /* GLOBAL_ATTRIBUTE6 */
2657 moh.global_attribute7, /* GLOBAL_ATTRIBUTE7 */
2658 moh.global_attribute8, /* GLOBAL_ATTRIBUTE8 */
2662 moh.global_attribute12, /* GLOBAL_ATTRIBUTE12 */
2659 moh.global_attribute9, /* GLOBAL_ATTRIBUTE9 */
2660 moh.global_attribute10, /* GLOBAL_ATTRIBUTE10 */
2661 moh.global_attribute11, /* GLOBAL_ATTRIBUTE11 */
2663 moh.global_attribute13, /* GLOBAL_ATTRIBUTE13 */
2664 moh.global_attribute14, /* GLOBAL_ATTRIBUTE14 */
2665 moh.global_attribute15, /* GLOBAL_ATTRIBUTE15 */
2666 moh.global_attribute16, /* GLOBAL_ATTRIBUTE16 */
2667 moh.global_attribute17, /* GLOBAL_ATTRIBUTE17 */
2668 moh.global_attribute18, /* GLOBAL_ATTRIBUTE18 */
2669 moh.global_attribute19, /* GLOBAL_ATTRIBUTE19 */
2670 moh.global_attribute20, /* GLOBAL_ATTRIBUTE20 */
2671 'SHIP', /* ORDER_DATE_TYPE_CODE */
2672 OE_Upg_SO.G_Earliest_Schedule_Limit, /* EARLIEST_SCHEDULE_LIMIT */
2673 OE_Upg_SO.G_Latest_Schedule_Limit, /* LATEST_SCHEDULE_LIMIT */
2674 moh.payment_type_code, /* PAYMENT_TYPE_CODE */
2675 moh.payment_amount, /* PAYMENT_AMOUNT */
2676 moh.check_number, /* CHECK_NUMBER */
2677 moh.credit_card_code, /* CREDIT_CARD_CODE */
2678 moh.credit_card_holder_name, /* CREDIT_CARD_HOLDER_NAME */
2679 moh.credit_card_number, /* CREDIT_CARD_NUMBER */
2680 moh.credit_card_expiration_date, /* CREDIT_CARD_EXPIRATION_DATE */
2681 moh.credit_card_approval_code, /* CREDIT_CARD_APPROVAL_CODE */
2682 decode(moh.credit_card_approval_code,NULL,NULL,
2683 moh.s1_date), /* CREDIT_CARD_APPROVAL_DATE */
2684 null, /* SALES CHANNEL CODE */
2685 G_ORDER_CATEGORY_CODE, /* ORDER_CATEGORY_CODE */
2686 moh.cancelled_flag, /* CANCELLED_FLAG */
2687 moh.open_flag, /* OPEN_FLAG */
2688 moh.booked_flag, /* BOOKED_FLAG */
2689 null, /* MARKETING_SOURCE_CODE_ID */
2690 null, /* TP_CONTEXT */
2691 null, /* TP_ATTRIBUTE1 */
2692 null, /* TP_ATTRIBUTE2 */
2693 null, /* TP_ATTRIBUTE3 */
2694 null, /* TP_ATTRIBUTE4 */
2695 null, /* TP_ATTRIBUTE5 */
2696 null, /* TP_ATTRIBUTE6 */
2697 null, /* TP_ATTRIBUTE7 */
2698 null, /* TP_ATTRIBUTE8 */
2699 null, /* TP_ATTRIBUTE9 */
2700 null, /* TP_ATTRIBUTE10 */
2701 null, /* TP_ATTRIBUTE11 */
2702 null, /* TP_ATTRIBUTE12 */
2703 null, /* TP_ATTRIBUTE13 */
2704 null, /* TP_ATTRIBUTE14 */
2705 null, /* TP_ATTRIBUTE15 */
2706 decode(G_CANCELLED_FLAG,'Y','CANCELLED',
2707 decode(moh.open_flag,'N','CLOSED',
2708 decode(moh.booked_flag,'N','ENTERED',
2709 'Y','BOOKED',NULL))), /* FLOW_STATUS_CODE */
2710 'Y', /* UPGRADED_FLAG */
2711 decode(nvl(moh.booked_flag,'-'),'Y',
2712 moh.s1_date,NULL), /* BOOKED_DATE */
2713 moh.org_id /* SOLD_FROM_ORG_ID */
2714 );
2715
2716 G_HDR_CANC_FLAG := NULL;
2717 G_HDR_CANC_FLAG := moh.cancelled_flag;
2718
2719 begin
2720 G_canc_rec := NULL;
2721 if nvl(moh.cancelled_Flag,'-') = 'Y' then
2722 select
2723 soc.header_id,
2724 soc.line_id,
2725 soc.created_by,
2726 soc.creation_date,
2727 soc.last_updated_by,
2728 soc.last_update_date,
2729 soc.last_update_login,
2730 soc.program_application_id,
2731 soc.program_id,
2732 soc.program_update_date,
2733 soc.request_id,
2734 soc.cancel_code,
2735 soc.cancelled_by,
2736 soc.cancel_date,
2737 soc.cancelled_quantity,
2738 soc.cancel_comment,
2739 soc.context,
2740 soc.attribute1,
2741 soc.attribute2,
2742 soc.attribute3,
2746 soc.attribute7,
2743 soc.attribute4,
2744 soc.attribute5,
2745 soc.attribute6,
2747 soc.attribute8,
2748 soc.attribute9,
2749 soc.attribute10,
2750 soc.attribute11,
2751 soc.attribute12,
2752 soc.attribute13,
2753 soc.attribute14,
2754 soc.attribute15
2755 into
2756 G_Hdr_Canc_Rec
2757 from
2758 so_order_cancellations soc
2759 where soc.header_id = g_header_id
2760 and soc.line_Id is null
2761 and rownum =1 ;
2762 end if;
2763 exception
2764 when others then
2765 null;
2766 end;
2767
2768 /* ============ Order Lines Creation ===========*/
2769
2770 G_LINE_ID := NULL;
2771 OE_Upg_SO.Upgrade_Create_Order_Lines;
2772
2773 /* ============ Header Level Sales Credits ===========*/
2774 OE_Upg_SO.Upgrade_Sales_Credits
2775 ( L_level_flag => 'H');
2776
2777 /* ============ Header Level Price Adjustments ===========*/
2778 OE_Upg_SO.Upgrade_Price_Adjustments ( L_level_flag => 'H');
2779
2780 /* ============ Upgrade Log Handling ===========*/
2781
2782 g_log_rec := NULL;
2783 g_log_rec.header_id := g_header_id;
2784 g_log_rec.mtl_sales_order_id := g_mtl_sales_order_id;
2785
2786 OE_UPG_SO.Upgrade_Insert_Upgrade_Log;
2787
2788 Update SO_HEADERS_ALL
2789 set upgrade_flag = 'Y'
2790 where header_id = G_HEADER_ID;
2791
2792 G_ERROR_LOCATION := 10;
2793
2794 if G_ERROR_ALERT = 'Y' then
2795 G_ERROR_LOCATION := 11;
2796 ROLLBACK TO HEADER_SAVE_POINT;
2797 v_error_code := sqlcode;
2798 oe_upg_so.upgrade_insert_errors
2799 (
2800 L_header_id => g_header_id,
2801 L_comments => 'Exception tapped: Alert level = '
2802 ||to_char(G_ERROR_LOCATION)||' Code -'
2803 ||to_char(v_error_code)
2804 ||' - Line id '||to_char(g_line_id)
2805 ||' Line detail id'
2806 ||to_char(g_line_rec.line_detail_id)
2807 );
2808 COMMIT;
2809 commit_counter := 0;
2810 G_ERROR_ALERT := 'N';
2811 end if;
2812 exception
2813 when others then
2814 /* G_ERROR_LOCATION := 12; */
2815 ROLLBACK TO HEADER_SAVE_POINT;
2816 v_error_code := sqlcode;
2817 oe_upg_so.upgrade_insert_errors
2818 (
2819 L_header_id => G_HEADER_ID,
2820 L_comments => 'Exception tapped: Exception level ='
2821 ||to_char(G_ERROR_LOCATION)||'code -'
2822 ||to_char(v_error_code)
2823 ||' - Line id '||to_char(G_LINE_ID)
2824 ||' Line detail id'
2825 ||to_char(g_line_rec.line_detail_id)
2826 );
2827 COMMIT;
2828 commit_counter := 0;
2829 G_ERROR_ALERT := 'N';
2830 raise;
2831 end;
2832
2833 G_ERROR_LOCATION := 120;
2834 if commit_counter > 500 then
2835 G_ERROR_LOCATION := 121;
2836 commit;
2837 commit_counter := 0;
2838 else
2839 G_ERROR_LOCATION := 122;
2840 commit_counter := commit_counter + 1;
2841 end if;
2842 end loop; /* end loop for Order Headers */
2843 G_ERROR_LOCATION := 13;
2844 commit;
2845
2846 -- dbms_output.put_line('just ending');
2847 End Upgrade_Create_Order_Headers;
2848
2849 Procedure Upgrade_Insert_Lines_History is
2850 --
2851 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2852 --
2853 begin
2854 G_ERROR_LOCATION := 14;
2855 insert into oe_order_lines_history
2856 (
2857 line_id,
2858 org_id,
2859 header_id,
2860 line_type_id,
2861 line_number,
2862 ordered_item,
2863 request_date,
2864 promise_date,
2865 schedule_ship_date,
2866 order_quantity_uom,
2867 pricing_quantity,
2868 pricing_quantity_uom,
2869 cancelled_quantity,
2870 shipped_quantity,
2871 ordered_quantity,
2872 fulfilled_quantity,
2873 shipping_quantity,
2874 shipping_quantity_uom,
2875 delivery_lead_time,
2876 tax_exempt_flag,
2877 tax_exempt_number,
2881 invoice_to_org_id,
2878 tax_exempt_reason_code,
2879 ship_from_org_id,
2880 ship_to_org_id,
2882 deliver_to_org_id,
2883 ship_to_contact_id,
2884 deliver_to_contact_id,
2885 invoice_to_contact_id,
2886 sold_to_org_id,
2887 cust_po_number,
2888 ship_tolerance_above,
2889 ship_tolerance_below,
2890 demand_bucket_type_code,
2891 veh_cus_item_cum_key_id,
2892 rla_schedule_type_code,
2893 customer_dock_code,
2894 customer_job,
2895 customer_production_line,
2896 cust_model_serial_number,
2897 project_id,
2898 task_id,
2899 inventory_item_id,
2900 tax_date,
2901 tax_code,
2902 tax_rate,
2903 demand_class_code,
2904 price_list_id,
2905 pricing_date,
2906 shipment_number,
2907 agreement_id,
2908 shipment_priority_code,
2909 shipping_method_code,
2910 freight_carrier_code,
2911 freight_terms_code,
2912 fob_point_code,
2913 tax_point_code,
2914 payment_term_id,
2915 invoicing_rule_id,
2916 accounting_rule_id,
2917 source_document_type_id,
2918 orig_sys_document_ref,
2919 source_document_id,
2920 orig_sys_line_ref,
2921 source_document_line_id,
2922 reference_line_id,
2923 reference_type,
2924 reference_header_id,
2925 item_revision,
2926 unit_selling_price,
2927 unit_list_price,
2928 tax_value,
2929 context,
2930 attribute1,
2931 attribute2,
2932 attribute3,
2933 attribute4,
2934 attribute5,
2935 attribute6,
2936 attribute7,
2937 attribute8,
2938 attribute9,
2939 attribute10,
2940 attribute11,
2941 attribute12,
2942 attribute13,
2943 attribute14,
2944 attribute15,
2945 global_attribute_category,
2946 global_attribute1,
2947 global_attribute2,
2948 global_attribute3,
2949 global_attribute4,
2950 global_attribute5,
2951 global_attribute6,
2952 global_attribute7,
2953 global_attribute8,
2954 global_attribute9,
2955 global_attribute10,
2956 global_attribute11,
2957 global_attribute12,
2958 global_attribute13,
2959 global_attribute14,
2960 global_attribute15,
2961 global_attribute16,
2962 global_attribute17,
2963 global_attribute18,
2964 global_attribute19,
2965 global_attribute20,
2966 pricing_context,
2967 pricing_attribute1,
2968 pricing_attribute2,
2969 pricing_attribute3,
2970 pricing_attribute4,
2971 pricing_attribute5,
2972 pricing_attribute6,
2973 pricing_attribute7,
2974 pricing_attribute8,
2975 pricing_attribute9,
2976 pricing_attribute10,
2977 industry_context,
2978 industry_attribute1,
2979 industry_attribute2,
2980 industry_attribute3,
2981 industry_attribute4,
2982 industry_attribute5,
2983 industry_attribute6,
2984 industry_attribute7,
2985 industry_attribute8,
2986 industry_attribute9,
2987 industry_attribute10,
2988 industry_attribute11,
2989 industry_attribute12,
2990 industry_attribute13,
2991 industry_attribute14,
2992 industry_attribute15,
2993 industry_attribute16,
2994 industry_attribute17,
2995 industry_attribute18,
2996 industry_attribute19,
2997 industry_attribute20,
2998 industry_attribute21,
2999 industry_attribute22,
3000 industry_attribute23,
3001 industry_attribute24,
3002 industry_attribute25,
3003 industry_attribute26,
3004 industry_attribute27,
3005 industry_attribute28,
3006 industry_attribute29,
3007 industry_attribute30,
3008 creation_date,
3009 created_by,
3010 last_update_date,
3011 last_updated_by,
3012 last_update_login,
3013 program_application_id,
3014 program_id,
3015 program_update_date,
3016 request_id,
3017 configuration_id,
3018 link_to_line_id,
3019 component_sequence_id,
3020 component_code,
3021 config_display_sequence,
3022 sort_order,
3023 item_type_code,
3024 option_number,
3025 option_flag,
3026 dep_plan_required_flag,
3027 visible_demand_flag,
3028 line_category_code,
3029 actual_shipment_date,
3030 customer_trx_line_id,
3031 return_context,
3032 return_attribute1,
3033 return_attribute2,
3034 return_attribute3,
3035 return_attribute4,
3039 return_attribute8,
3036 return_attribute5,
3037 return_attribute6,
3038 return_attribute7,
3040 return_attribute9,
3041 return_attribute10,
3042 return_attribute11,
3043 return_attribute12,
3044 return_attribute13,
3045 return_attribute14,
3046 return_attribute15,
3047 intmed_ship_to_org_id,
3048 intmed_ship_to_contact_id,
3049 actual_arrival_date,
3050 ato_line_id,
3051 auto_selected_quantity,
3052 component_number,
3053 earliest_acceptable_date,
3054 explosion_date,
3055 latest_acceptable_date,
3056 model_group_number,
3057 schedule_arrival_date,
3058 ship_model_complete_flag,
3059 schedule_status_code,
3060 return_reason_code,
3061 salesrep_id,
3062 split_from_line_id,
3063 cust_production_seq_num,
3064 authorized_to_ship_flag,
3065 invoice_interface_status_code,
3066 ship_set_id,
3067 arrival_set_id,
3068 hist_comments,
3069 hist_type_code,
3070 reason_code,
3071 hist_created_by,
3072 hist_creation_date,
3073 source_type_code,
3074 booked_flag,
3075 fulfilled_flag,
3076 sold_from_org_id,
3077 top_model_line_id,
3078 cancelled_flag,
3079 open_flag,
3080 over_ship_reason_code,
3081 over_ship_resolved_flag,
3082 item_identifier_type,
3083 commitment_id,
3084 shipping_interfaced_flag,
3085 credit_invoice_line_id,
3086 end_item_unit_number,
3087 mfg_component_sequence_id,
3088 config_header_id,
3089 config_rev_nbr,
3090 shipping_instructions,
3091 packing_instructions,
3092 invoiced_quantity,
3093 reference_customer_trx_line_id,
3094 split_by,
3095 line_set_id,
3096 tp_context,
3097 tp_attribute1,
3098 tp_attribute2,
3099 tp_attribute3,
3100 tp_attribute4,
3101 tp_attribute5,
3102 tp_attribute6,
3103 tp_attribute7,
3104 tp_attribute8,
3105 tp_attribute9,
3106 tp_attribute10,
3107 tp_attribute11,
3108 tp_attribute12,
3109 tp_attribute13,
3110 tp_attribute14,
3111 tp_attribute15,
3112 fulfillment_method_code,
3113 service_reference_type_code,
3114 service_reference_line_id,
3115 service_reference_system_id,
3116 ordered_item_id,
3117 service_number,
3118 service_duration,
3119 service_start_date,
3120 re_source_flag,
3121 flow_status_code,
3122 service_end_date,
3123 service_coterminate_flag,
3124 shippable_flag,
3125 order_source_id,
3126 orig_sys_shipment_ref,
3127 change_sequence,
3128 drop_ship_flag,
3129 customer_line_number,
3130 customer_shipment_number,
3131 customer_item_net_price,
3132 customer_payment_term_id,
3133 first_ack_date,
3134 first_ack_code,
3135 last_ack_code,
3136 last_ack_date,
3137 planning_priority,
3138 service_txn_comments,
3139 service_period,
3140 unit_selling_percent,
3141 unit_list_percent,
3142 unit_percent_base_price,
3143 model_remnant_flag,
3144 service_txn_reason_code,
3145 calculate_price_flag,
3146 revenue_amount
3147 )
3148 values
3149 (
3150 g_line_id, /* LINE_ID */
3151 g_line_rec.org_id, /* ORG_ID */
3152 g_line_rec.header_id, /* HEADER_ID */
3153 g_line_rec.line_type_id, /* LINE_TYPE_ID, */
3154 g_last_line_number, /* LINE_NUMBER */
3155 null, /* ordered_item, */
3156 g_line_rec.date_requested_current, /* REQUEST_DATE */
3157 g_line_rec.promise_date, /* PROMISE_DATE */
3158 g_line_rec.schedule_date, /* SCHEDULE_SHIP_DATE */
3159 nvl(r_uom_code,g_line_rec.unit_code), /* ORDER_QUANTITY_UOM */
3160 g_line_rec.ordered_quantity, /* PRICING_QUANTITY */
3161 nvl(r_uom_code,g_line_rec.unit_code), /* PRICING_QUANTITY_UOM */
3162 decode(G_ORD_CANC_FLAG,'Y',G_canc_rec.can_cancelled_quantity,
3163 decode(g_hdr_canc_flag,'Y', g_line_rec.cancelled_quantity,
3164 decode(g_line_id_Change_flag,'Y', g_line_rec.cancelled_quantity,0))),
3165 /* CANCELLED_QUANTITY */
3166 g_line_rec.shipped_quantity, /* SHIPPED_QUANTITY */
3167 decode(g_hdr_canc_flag,'Y',0,
3168 nvl(g_line_rec.ordered_quantity,0)), /* ORDERED_QUANTITY */
3169 g_line_rec.fulfilled_quantity, /* FULFILLED_QUANTITY */
3170 g_line_rec.shipped_quantity, /* SHIPPING_QUANTITY */
3171 nvl(r_uom_code,
3172 g_line_rec.shipping_quantity_uom), /* SHIPPING_QUANTITY_UOM */
3173 null, /* DELIVERY_LEAD_TIME */
3174 G_tax_exempt_flag, /* TAX_EXEMPT_FLAG */
3175 g_line_rec.tax_exempt_number, /* TAX_EXEMPT_NUMBER */
3176 g_line_rec.tax_exempt_reason_code, /* TAX_EXEMPT_REASON_CODE */
3180 null, /* DELIVER_TO_ORG_ID */
3177 g_line_rec.warehouse_id, /* SHIP_FROM_ORG_ID */
3178 g_line_rec.ship_to_site_use_id, /* SHIP_TO_ORG_ID */
3179 g_invoice_to_site_use_id, /* INVOICE_TO_ORG_ID */
3181 g_line_rec.ship_to_contact_id, /* SHIP_TO_CONTACT_ID */
3182 null, /* DELIVER_TO_CONTACT_ID */
3183 null, /* INVOICE_TO_CONTACT_ID */
3184 G_customer_id, /* SOLD_TO_ORG_ID */
3185 G_Purchase_Order_Num, /* CUST_PO_NUMBER */
3186 null, /* SHIP_TOLERANCE_ABOVE */
3187 null, /* SHIP_TOLERANCE_BELOW */
3188 decode(G_AUTO_FLAG,'Y','DAY',NULL), /* DEMAND_BUCKET_TYPE_CODE */
3189 decode(G_AUTO_FLAG,'Y',-1,NULL), /* VEH_CUS_ITEM_CUM_KEY_ID */
3190 g_line_rec.rla_schedule_type_code, /* RLA_SCHEDULE_TYPE_CODE */
3191 g_line_rec.customer_dock_code, /* CUSTOMER_DOCK_CODE */
3192 g_line_rec.customer_job, /* CUSTOMER_JOB */
3193 g_line_rec.customer_production_line, /* CUSTOMER_PRODUCTION_LINE */
3194 g_line_rec.customer_model_serial_number, /* CUST_MODEL_SERIAL_NUMBER */
3195 g_line_rec.project_id, /* PROJECT_ID */
3196 g_line_rec.task_id, /* TASK_ID */
3197 nvl(r_inventory_item_id,g_line_rec.inventory_item_id), /* INVENTORY_ITEM_ID */
3198 null, /* TAX_DATE */
3199 g_line_rec.tax_code, /* TAX_CODE */
3200 null, /* TAX_RATE */
3201 g_line_rec.demand_class_code, /* DEMAND_CLASS_CODE */
3202 g_line_rec.price_list_id, /* PRICE_LIST_ID */
3203 null, /* PRICING_DATE */
3204 r_shipment_number, /* SHIPMENT_NUMBER */
3205 g_line_rec.agreement_id, /* AGREEMENT_ID */
3206 g_line_rec.shipment_priority_code, /* SHIPMENT_PRIORITY_CODE */
3207 null, /* SHIPPPING_METHOD_CODE */
3208 g_line_rec.ship_method_code, /* FREIGHT_CARRIER_CODE */
3209 G_freight_terms_code, /* FREIGHT_TERMS_CODE */
3210 null, /* FOB_POINT_CODE */
3211 'INVOICE', /* TAX_POINT_CODE */
3212 G_terms_id, /* PAYMENT_TERM_ID */
3213 nvl(g_line_rec.invoicing_rule_id,0), /* INVOICING_RULE_ID */
3214 nvl(g_line_rec.accounting_rule_id,0), /* ACCOUNTING_RULE_ID */
3215 g_line_rec.source_document_type_id, /* SOURCE_DOCUMENT_TYPE_ID */
3216 G_ORIG_SYS_DOCUMENT_REF, /* ORIG_SYS_DOCUMENT_REF */
3217 g_line_rec.source_document_id, /* SOURCE_DOCUMENT_ID */
3218 g_line_rec.original_system_line_reference, /* ORIG_SYS_LINE_REFERENCE */
3219 g_line_rec.source_document_line_id, /* SOURCE_DOCUMENT_LINE_ID */
3220 v_reference_line_id, /* REFERENCE_LINE_ID */
3221 g_line_rec.return_reference_type_code, /* REFERENCE_TYPE */
3222 v_reference_header_id, /* REFERENCE_HEADER_ID */
3223 null, /* ITEM_REVISION */
3224 g_line_rec.selling_price, /* SELLING_PRICE */
3225 g_line_rec.list_price, /* LIST_PRICE */
3226 null, /* TAX_VALUE */
3227 g_line_rec.context, /* CONTEXT */
3228 g_line_rec.attribute1, /* ATTRIBUTE1 */
3229 g_line_rec.attribute2, /* ATTRIBUTE2 */
3230 g_line_rec.attribute3, /* ATTRIBUTE3 */
3231 g_line_rec.attribute4, /* ATTRIBUTE4 */
3232 g_line_rec.attribute5, /* ATTRIBUTE5 */
3233 g_line_rec.attribute6, /* ATTRIBUTE6 */
3234 g_line_rec.attribute7, /* ATTRIBUTE7 */
3235 g_line_rec.attribute8, /* ATTRIBUTE8 */
3236 g_line_rec.attribute9, /* ATTRIBUTE9 */
3237 g_line_rec.attribute10, /* ATTRIBUTE10 */
3238 g_line_rec.attribute11, /* ATTRIBUTE11 */
3239 g_line_rec.attribute12, /* ATTRIBUTE12 */
3240 g_line_rec.attribute13, /* ATTRIBUTE13 */
3241 g_line_rec.attribute14, /* ATTRIBUTE14 */
3242 g_line_rec.attribute15, /* ATTRIBUTE15 */
3243 g_line_rec.global_attribute_category, /* GLOBAL_ATTRIBUTE_CATEGORY */
3244 g_line_rec.global_attribute1, /* GLOBAL_ATTRIBUTE1 */
3245 g_line_rec.global_attribute2, /* GLOBAL_ATTRIBUTE2 */
3246 g_line_rec.global_attribute3, /* GLOBAL_ATTRIBUTE3 */
3247 g_line_rec.global_attribute4, /* GLOBAL_ATTRIBUTE4 */
3248 g_line_rec.global_attribute5, /* GLOBAL_ATTRIBUTE5 */
3249 g_line_rec.global_attribute6, /* GLOBAL_ATTRIBUTE6 */
3253 g_line_rec.global_attribute10, /* GLOBAL_ATTRIBUTE10 */
3250 g_line_rec.global_attribute7, /* GLOBAL_ATTRIBUTE7 */
3251 g_line_rec.global_attribute8, /* GLOBAL_ATTRIBUTE8 */
3252 g_line_rec.global_attribute9, /* GLOBAL_ATTRIBUTE9 */
3254 g_line_rec.global_attribute11, /* GLOBAL_ATTRIBUTE11 */
3255 g_line_rec.global_attribute12, /* GLOBAL_ATTRIBUTE12 */
3256 g_line_rec.global_attribute13, /* GLOBAL_ATTRIBUTE13 */
3257 g_line_rec.global_attribute14, /* GLOBAL_ATTRIBUTE14 */
3258 g_line_rec.global_attribute15, /* GLOBAL_ATTRIBUTE15 */
3259 g_line_rec.global_attribute16, /* GLOBAL_ATTRIBUTE16 */
3260 g_line_rec.global_attribute17, /* GLOBAL_ATTRIBUTE17 */
3261 g_line_rec.global_attribute18, /* GLOBAL_ATTRIBUTE18 */
3262 g_line_rec.global_attribute19, /* GLOBAL-ATTRIBUTE19 */
3263 g_line_rec.global_attribute20, /* GLOBAL_ATTRIBUTE20 */
3264 g_line_rec.pricing_context, /* PRICING_CONTEXT */
3265 g_line_rec.pricing_attribute1, /* PRICING_ATTRIBUTE1 */
3266 g_line_rec.pricing_attribute2, /* PRICING_ATTRIBUTE2 */
3267 g_line_rec.pricing_attribute3, /* PRICING_ATTRIBUTE3 */
3268 g_line_rec.pricing_attribute4, /* PRICING_ATTRIBUTE4 */
3269 g_line_rec.pricing_attribute5, /* PRICING_ATTRIBUTE5 */
3270 g_line_rec.pricing_attribute6, /* PRICING_ATTRIBUTE6 */
3271 g_line_rec.pricing_attribute7, /* PRICING_ATTRIBUTE7 */
3272 g_line_rec.pricing_attribute8, /* PRICING_ATTRIBUTE8 */
3273 g_line_rec.pricing_attribute9, /* PRICING_ATTRIBUTE9 */
3274 g_line_rec.pricing_attribute10, /* PRICING_ATTRIBUTE10*/
3275 g_line_rec.industry_context, /* INDUSTRY_CONTEXT */
3276 g_line_rec.industry_attribute1, /* INDUSTRY_ATTRIBUTE1 */
3277 decode(G_AUTO_FLAG,'Y',NULL,
3278 g_line_rec.industry_attribute2), /* INDUSTRY_ATTRIBUTE2 */
3279 decode(G_AUTO_FLAG,'Y',NULL,
3280 g_line_rec.industry_attribute3), /* INDUSTRY_ATTRIBUTE3 */
3281 decode(G_AUTO_FLAG,'Y',NULL,
3282 g_line_rec.industry_attribute4), /* INDUSTRY_ATTRIBUTE4 */
3283 decode(G_AUTO_FLAG,'Y',NULL,
3284 g_line_rec.industry_attribute5), /* INDUSTRY_ATTRIBUTE5 */
3285 decode(G_AUTO_FLAG,'Y',NULL,
3286 g_line_rec.industry_attribute6), /* INDUSTRY_ATTRIBUTE6 */
3287 decode(G_AUTO_FLAG,'Y',NULL,
3288 g_line_rec.industry_attribute7), /* INDUSTRY_ATTRIBUTE7 */
3289 decode(G_AUTO_FLAG,'Y',NULL,
3290 g_line_rec.industry_attribute8), /* INDUSTRY_ATTRIBUTE8 */
3291 decode(G_AUTO_FLAG,'Y',NULL,
3292 g_line_rec.industry_attribute9), /* INDUSTRY_ATTRIBUTE9 */
3293 decode(G_AUTO_FLAG,'Y',NULL,
3294 g_line_rec.industry_attribute10), /* INDUSTRY_ATTRIBUTE10 */
3295 decode(G_AUTO_FLAG,'Y',NULL,
3296 g_line_rec.industry_attribute11), /* INDUSTRY_ATTRIBUTE11 */
3297 decode(G_AUTO_FLAG,'Y',NULL,
3298 g_line_rec.industry_attribute12), /* INDUSTRY_ATTRIBUTE12 */
3299 decode(G_AUTO_FLAG,'Y',NULL,
3300 g_line_rec.industry_attribute13), /* INDUSTRY_ATTRIBUTE13 */
3301 decode(G_AUTO_FLAG,'Y',NULL,
3302 g_line_rec.industry_attribute14), /* INDUSTRY_ATTRIBUTE14 */
3303 decode(G_AUTO_FLAG,'Y',NULL,
3304 g_line_rec.industry_attribute15), /* INDUSTRY_ATTRIBUTE15 */
3305 NULL, /* INDUSTRY_ATTRIBUTE16 */
3306 NULL, /* INDUSTRY_ATTRIBUTE17 */
3307 NULL, /* INDUSTRY_ATTRIBUTE18 */
3308 NULL, /* INDUSTRY_ATTRIBUTE19 */
3309 NULL, /* INDUSTRY_ATTRIBUTE20 */
3310 NULL, /* INDUSTRY_ATTRIBUTE21 */
3311 NULL, /* INDUSTRY_ATTRIBUTE22 */
3312 NULL, /* INDUSTRY_ATTRIBUTE23 */
3313 NULL, /* INDUSTRY_ATTRIBUTE24 */
3314 NULL, /* INDUSTRY_ATTRIBUTE25 */
3315 NULL, /* INDUSTRY_ATTRIBUTE26 */
3316 NULL, /* INDUSTRY_ATTRIBUTE27 */
3317 NULL, /* INDUSTRY_ATTRIBUTE28 */
3318 NULL, /* INDUSTRY_ATTRIBUTE29 */
3319 NULL, /* INDUSTRY_ATTRIBUTE30 */
3320 g_line_rec.creation_date, /* CREATION_DATE */
3321 g_line_rec.created_by, /* CREATED_BY */
3322 g_line_rec.last_update_date, /* LAST_UPDATE_DATE */
3323 g_line_rec.last_updated_by, /* LAST_UPDATED_BY */
3324 g_line_rec.last_update_login, /* LAST_UPDATE_LOGIN */
3325 nvl(g_line_rec.program_application_id,0), /* PROGRAM_APPLICATION_ID */
3326 g_line_rec.program_id, /* PROGRAM_ID */
3327 g_line_rec.program_update_date, /* PROGRAM_UPDATE_DATE */
3331 g_line_rec.component_sequence_id, /* COMPONENT_SEQUENCE_ID */
3328 g_line_rec.request_id, /* REQUEST_ID */
3329 g_line_rec.parent_line_id, /* CONFIGURATION_ID */
3330 g_line_rec.link_to_line_id, /* LINK_TO_LINE_ID */
3332 g_line_rec.component_code, /* COMPONENT_CODE */
3333 null, /* CONFIG_DISPLAY_SEQUENCE */
3334 null, /* SORT_ORDER, */
3335 g_line_rec.item_type_code, /* ITEM_TYPE_CODE */
3336 null, /* OPTION_NUMBER */
3337 g_line_rec.option_flag, /* OPTION_FLAG, */
3338 g_line_rec.dep_plan_required_flag, /* DEP_PLAN_REQUIRED_FLAG */
3339 g_line_rec.visible_demand_flag, /* VISIBLE_DEMAND_FLAG */
3340 g_line_rec.line_category_code, /* LINE_CATEGORY_CODE */
3341 g_line_rec.actual_departure_date, /* ACTUAL_SHIPMENT_DATE */
3342 decode(g_line_rec.line_type_code,'RETURN',
3343 decode(g_line_rec.return_reference_type_code,
3344 'INVOICE',g_line_rec.return_reference_id,NULL),
3345 NULL), /* CUSTOMER_TRX_LINE_ID */
3346 g_line_rec.return_reference_type_Code, /* RETURN_CONTEXT */
3347 decode(g_line_rec.line_type_code,'RETURN',
3348 decode(g_line_rec.return_reference_type_code,
3349 'INVOICE',v_customer_trx_id,
3350 v_reference_header_id),NULL), /* RETURN_ATTRIBUTE1 */
3351 decode(g_line_rec.line_type_code,'RETURN',
3352 decode(g_line_rec.return_reference_type_code,
3353 'INVOICE',g_line_rec.return_reference_id,
3354 v_reference_line_id),NULL), /* RETURN_ATTRIBUTE2 */
3355 null, /* RETURN_ATTRIBUTE3 */
3356 null, /* RETURN_ATTRIBUTE4 */
3357 null, /* RETURN_ATTRIBUTE5 */
3358 null, /* RETURN_ATTRIBUTE6 */
3359 null, /* RETURN_ATTRIBUTE7 */
3360 null, /* RETURN_ATTRIBUTE8 */
3361 null, /* RETURN_ATTRIBUTE9 */
3362 null, /* RETURN_ATTRIBUTE10 */
3363 null, /* RETURN_ATTRIBUTE11 */
3364 null, /* RETURN_ATTRIBUTE12 */
3365 null, /* RETURN_ATTRIBUTE13 */
3366 null, /* RETURN_ATTRIBUTE14 */
3367 null, /* RETURN_ATTRIBUTE15 */
3368 g_line_rec.intermediate_ship_to_id, /* intmed_ship_to_org_id, */
3369 g_line_rec.ship_to_contact_id, /* intmed_ship_to_contact_id, */
3370 null, /* actual_arrival_date, */
3371 g_line_rec.ato_line_id, /* ATO_LINE_ID */
3372 null, /* auto_selected_quantity, */
3373 null, /* component_number, */
3374 null, /* earliest_acceptable_date, */
3375 g_line_rec.standard_component_freeze_date, /* explosion_date, */
3376 g_line_rec.latest_acceptable_date, /* latest_acceptable_date, */
3377 null, /* model_group_number, */
3378 null, /* schedule_arrival_date, */
3379 g_line_rec.ship_model_complete_flag, /* ship_model_complete_flag, */
3380 g_line_rec.schedule_status_code, /* schedule_status_code, */
3381 g_line_rec.transaction_reason_code, /* return_reason_code */
3382 g_salesrep_id, /* salesrep_id */
3383 null, /* split_from_line_id */
3384 g_line_rec.planning_prod_seq_number, /* cust_production_seq_num */
3385 decode(G_AUTO_FLAG,'Y','Y',NULL), /* authorized_to_ship_flag */
3386 g_line_rec.invoice_interface_status_code, /* invoice_interface_status_code */
3387 g_set_id, /* Ship_Set_Id */
3388 null, /* Arrival_Set_Id */
3389 g_canc_rec.can_cancel_comment, /* Hist_Comments */
3390 'CANCELLATION', /* Hist_Type_Code */
3391 g_canc_rec.can_cancel_code, /* Reason_Code */
3392 g_canc_rec.can_cancelled_by, /* Hist_Created_By */
3393 g_canc_rec.can_cancel_date, /* Hist_Creation_Date */
3394 g_line_rec.source_type_code, /* Source_Type_Code */
3395 g_line_rec.Booked_Flag, /* booked_Flag */
3396 g_line_rec.fulfilled_flag, /* fulfilled_flag */
3397 g_line_rec.org_id, /* sold_from_org_id, */
3398 g_line_rec.top_model_line_id, /* top_model_line_id, */
3399 g_line_rec.cancelled_flag, /* cancelled_flag, */
3400 g_line_rec.open_flag, /* open_flag, */
3401 null, /* over_ship_reason_code, */
3402 null, /* over_ship_resolved_flag, */
3406 g_line_rec.shipping_interfaced_flag, /* shipping_interfaced_flag, */
3403 decode(nvl(g_line_rec.customer_item_id,-1),
3404 -1,'INT','CUST'), /* item_identifier_type, */
3405 null, /* commitment_id, */
3407 g_line_rec.credit_invoice_line_id, /* credit_invoice_line_id, */
3408 null, /* end_item_unit_number, */
3409 null, /* mfg_component_sequence_id, */
3410 null, /* config_header_id, */
3411 null, /* config_rev_nbr, */
3412 null, /* shipping_instructions, */
3413 null, /* packing_instructions, */
3414 g_line_rec.invoiced_quantity, /* invoiced_quantity, */
3415 null, /* reference_customer_trx_line_id, */
3416 null, /* split_by, */
3417 null, /* line_set_id, */
3418 g_line_rec.tp_context, /* tp_context */
3419 g_line_rec.tp_attribute1, /* tp_attribute1 */
3420 g_line_rec.tp_attribute2, /* tp_attribute2 */
3421 g_line_rec.tp_attribute3, /* tp_attribute3 */
3422 g_line_rec.tp_attribute4, /* tp_attribute4 */
3423 g_line_rec.tp_attribute5, /* tp_attribute5 */
3424 g_line_rec.tp_attribute6, /* tp_attribute6 */
3425 g_line_rec.tp_attribute7, /* tp_attribute7 */
3426 g_line_rec.tp_attribute8, /* tp_attribute8 */
3427 g_line_rec.tp_attribute9, /* tp_attribute9 */
3428 g_line_rec.tp_attribute10, /* tp_attribute10 */
3429 g_line_rec.tp_attribute11, /* tp_attribute11 */
3430 g_line_rec.tp_attribute12, /* tp_attribute12 */
3431 g_line_rec.tp_attribute13, /* tp_attribute13 */
3432 g_line_rec.tp_attribute14, /* tp_attribute14 */
3433 g_line_rec.tp_attribute15, /* tp_attribute15 */
3434 g_line_rec.fulfillment_method_code, /* fulfillment_method_code, */
3435 g_line_rec.service_reference_type_code, /* service_reference_type_code, */
3436 g_line_rec.service_reference_line_id, /* service_reference_line_id, */
3437 g_line_rec.service_reference_system_id, /* service_reference_system_id, */
3438 decode(nvl(g_line_rec.customer_item_id,-1),
3439 -1,g_line_rec.inventory_item_id,
3440 g_line_rec.customer_item_id), /* ordered_item_id */
3441 g_line_rec.service_number, /* service_number, */
3442 g_line_rec.service_duration, /* service_duration, */
3443 g_line_rec.service_start_date, /* service_start_date, */
3444 g_line_rec.re_source_flag, /* re_source_flag, */
3445 g_line_rec.flow_status_code, /* flow_status_code, */
3446 g_line_rec.service_end_date, /* service_end_date, */
3447 g_line_rec.service_coterminate_flag, /* service_coterminate_flag, */
3448 g_line_rec.shippable_flag, /* shippable_flag, */
3449 nvl(G_ORDER_SOURCE_ID,0), /* order_source_id, */
3450 null, /* orig_sys_shipment_ref, */
3451 null, /* change_sequence, */
3452 null, /* drop_ship_flag, */
3453 null, /* customer_line_number, */
3454 null, /* customer_shipment_number, */
3455 null, /* customer_item_net_price, */
3456 null, /* customer_payment_term_id, */
3457 null, /* first_ack_date, */
3458 null, /* first_ack_code, */
3459 null, /* last_ack_code, */
3460 null, /* last_ack_date, */
3461 g_line_rec.planning_priority, /* planning_priority, */
3462 g_line_rec.service_txn_comments, /* service_txn_comments, */
3463 g_line_rec.service_period, /* service_period, */
3464 g_line_rec.selling_percent, /* unit_selling_percent, */
3465 g_line_rec.list_percent, /* unit_list_percent, */
3466 g_line_rec.percent_base_price, /* unit_percent_base_price, */
3467 null, /* model_remnant_flag, */
3468 g_line_rec.service_txn_reason_code, /* service_txn_reason_code, */
3469 g_line_rec.calculate_price_flag, /* calculate_price_flag, */
3470 g_line_rec.revenue_amount /* revenue_amount, */
3471 );
3472 G_ERROR_LOCATION := 15;
3473 End Upgrade_Insert_Lines_History;
3474
3475 Procedure Upgrade_Insert_Upgrade_log
3476 is
3477 --
3478 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3479 --
3480 begin
3481
3482 G_ERROR_LOCATION := 16;
3483 insert into oe_upgrade_log
3484 (
3485 header_id,
3489 picking_line_id,
3486 old_line_id,
3487 old_line_detail_id,
3488 new_line_id,
3490 new_line_number,
3491 mtl_sales_order_id,
3492 return_qty_available,
3493 comments,
3494 creation_date,
3495 delivery
3496 )
3497 values
3498 (
3499 g_log_rec.header_id, /* HEADER_ID */
3500 g_log_rec.old_line_id, /* OLD_LINE_ID */
3501 g_log_rec.old_line_detail_id, /* OLD_LINE_DETAIL_ID */
3502 g_log_rec.new_line_id, /* NEW_LINE_ID */
3503 g_log_rec.picking_line_id, /* PICKING_LINE_ID */
3504 g_log_rec.new_line_number, /* NEW_LINE_NUMBER */
3505 g_log_rec.mtl_sales_order_id, /* MTL_SALES_ORDER_ID */
3506 g_log_rec.return_qty_available, /* RETURN_QTY_AVAILABLE */
3507 g_log_rec.comments, /* COMMENTS */
3508 sysdate, /* CREATION_DATE */
3509 g_log_rec.delivery /* DELIVERY */
3510 );
3511 G_ERROR_LOCATION := 17;
3512
3513 End Upgrade_Insert_Upgrade_log;
3514
3515 Procedure Upgrade_Process_Distbns
3516 ( L_total_slabs IN number)
3517 is
3518 v_type varchar2(1);
3519 cursor RDis
3520 is
3521 select
3522 sha.header_id
3523 from
3524 so_headers_all sha
3525 where sha.upgrade_flag = 'N'
3526 order by sha.header_id;
3527
3528 /*
3529 and ( (sha.order_category = 'RMA' and v_type = 'R') or
3530 (sha.order_category <> 'RMA' and v_type = 'O') )
3531 */
3532
3533 v_total_headers number;
3534 v_min_header number;
3535 v_max_header number;
3536 v_counter number;
3537 v_gap number;
3538 v_slab_count number;
3539 v_slab_start number;
3540 v_slab_end number;
3541 v_dis_header_id number;
3542 v_start_flag number;
3543 v_total_slabs number;
3544 v_type_ctr number;
3545
3546 --
3547 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3548 --
3549 Begin
3550
3551 delete oe_upgrade_distribution;
3552 commit;
3553 v_type_ctr := 1;
3554
3555 loop
3556 if v_type_ctr = 1 then
3557 v_type := 'O';
3558 /*
3559 elsif v_type_ctr =2 then
3560 v_type := 'R';
3561 */
3562 else
3563 exit;
3564 end if;
3565
3566 begin
3567 select
3568 count(*),
3569 nvl(min(sha.header_id),0),
3570 nvl(max(sha.header_id),0)
3571 into
3572 v_total_headers,
3573 v_min_header,
3574 v_max_header
3575 from
3576 so_headers_all sha
3577 where sha.upgrade_flag = 'N';
3578 /*
3579 and ( (sha.order_category = 'RMA' and v_type = 'R') or
3580 (sha.order_category <> 'RMA' and v_type = 'O') );
3581 */
3582 exception
3583 when others then
3584 null;
3585 end;
3586
3587 if v_total_headers < 5000 or l_total_slabs = 1 then
3588
3589 OE_UPG_SO.Upgrade_Insert_Distbn_Record
3590 (
3591 L_slab => 1,
3592 L_start_header_id => v_min_header,
3593 L_end_Header_id => v_max_header,
3594 L_type_var => v_type
3595 );
3596
3597 else
3598 v_max_header := 0;
3599 v_min_header := 0;
3600 v_total_slabs := L_total_slabs;
3601 v_counter := 0;
3602 v_start_flag := 0;
3603 v_slab_count := 0;
3604 v_gap := round(v_total_headers / v_total_slabs);
3605
3606 for MRdis in Rdis loop
3607
3608 v_dis_header_id := MRdis.header_id;
3609 v_counter := v_counter + 1;
3610
3611 if v_start_flag = 0 then
3612 v_start_flag := 1;
3613 v_min_header := MRdis.header_id;
3614 v_max_header := NULL;
3615 v_slab_count := v_slab_count + 1;
3616 end if;
3617
3618 if v_counter = v_gap and v_slab_count < v_total_slabs then
3619 v_max_header := MRdis.header_id;
3620
3621 OE_UPG_SO.Upgrade_Insert_Distbn_Record
3622 (
3623 L_slab => v_slab_count,
3624 L_start_header_id => v_min_header,
3625 L_end_Header_id => v_max_header,
3626 L_type_var => v_type
3627 );
3628
3629 v_counter := 0;
3630 v_start_flag := 0;
3631 end if;
3632
3633 end loop;
3634 v_max_header := v_dis_header_id;
3635
3639 L_start_header_id => v_min_header,
3636 OE_UPG_SO.Upgrade_Insert_Distbn_Record
3637 (
3638 L_slab => v_slab_count,
3640 L_end_Header_id => v_max_header ,
3641 L_type_var => v_type
3642 );
3643
3644 commit;
3645 end if;
3646 v_type_ctr := v_type_ctr + 1;
3647 end loop;
3648 commit;
3649
3650 End Upgrade_Process_Distbns;
3651
3652 Procedure Upgrade_Insert_Distbn_Record
3653 (
3654 L_slab IN Varchar2,
3655 L_start_Header_id IN Number,
3656 L_end_Header_Id IN Number,
3657 L_type_var IN Varchar2
3658 )
3659 is
3660
3661 --
3662 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3663 --
3664 Begin
3665 insert into oe_upgrade_distribution
3666 (
3667 slab,
3668 start_header_id,
3669 end_header_id,
3670 alloted_flag,
3671 line_type,
3672 creation_date
3673 )
3674 values
3675 (
3676 L_slab,
3677 L_start_Header_id,
3678 L_end_Header_id,
3679 'N',
3680 L_type_var,
3681 sysdate
3682 );
3683
3684 End Upgrade_Insert_Distbn_Record;
3685
3686 Procedure Upgrade_Insert_Errors
3687 (
3688 L_header_id IN Varchar2,
3689 L_comments IN varchar2
3690 )
3691 is
3692
3693 --
3694 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3695 --
3696 Begin
3697 G_ERROR_LOCATION := 18;
3698 insert into oe_upgrade_errors
3699 (
3700 header_id,
3701 comments,
3702 creation_date
3703 )
3704 values
3705 (
3706 l_header_id,
3707 l_comments,
3708 sysdate
3709 );
3710
3711 G_ERROR_LOCATION := 19;
3712 End Upgrade_Insert_Errors;
3713
3714 Procedure Upgrade_Create_Line_Sets
3715 is
3716 v_set_id number;
3717 v_line_number number;
3718 v_shipment_number number;
3719 v_header_id number;
3720 v_line_id number;
3721 v_commit_ctr number;
3722 v_mem_line_num number;
3723
3724 cursor c1 is
3725 select
3726 header_id,
3727 line_id,
3728 line_number,
3729 inventory_item_id,
3730 unit_code
3731 from
3732 so_lines_all
3733 where line_type_code = 'PARENT'
3734 and item_type_code in ('KIT','MODEL','STANDARD')
3735 and parent_line_id is null -- To filter out option lines
3736 and parent_line_id is null -- To filter out option lines
3737 and line_id in
3738 (select shipment_schedule_line_id from so_lines_all
3739 where line_id in
3740 (select old_line_id from oe_upgrade_log_v
3741 where new_line_id in
3742 (select line_id from oe_order_lines_all
3743 where line_set_id is null)))
3744 order by header_id, line_id;
3745
3746 cursor c3 is
3747 select
3748 line_id,
3749 line_number
3750 from oe_order_lines_all oola
3751 where line_id in
3752 (select new_line_id from oe_upgrade_log oul, so_lines_all sla
3753 where oul.old_line_id = sla.line_id
3754 and sla.shipment_schedule_line_id = v_line_id)
3755 and item_type_code in ('KIT','MODEL','STANDARD')
3756 order by line_id;
3757
3758 cursor c5 is
3759 select
3760 header_id,
3761 line_id,
3762 line_number,
3763 inventory_item_id,
3764 unit_code
3765 from
3766 so_lines_all sla
3767 where sla.line_type_code = 'REGULAR'
3768 AND item_type_code in ('KIT','MODEL','STANDARD')
3769 and parent_line_id is null -- To filter out options (included on Leena's instn.)
3770 and parent_line_id is null -- To filter out options (included on Leena's instn.)
3771 and sla.line_id in
3772 (select lg.old_line_id
3773 from oe_upgrade_log_v lg, oe_order_lines_all ln
3774 where lg.new_line_id = ln.line_id
3775 and ln.item_type_code not in ('INCLUDED','CONFIG')
3776 group by lg.old_line_id
3777 having count(*) > 1);
3778
3779 cursor c7 is
3780 select
3781 new_line_id line_id,
3782 new_line_number line_number
3783 from oe_upgrade_log
3784 where old_line_id = v_line_id
3785 and old_line_id is not null;
3786 v_ctr number;
3787 p_line_number number;
3788 v_item_type_code varchar2(200);
3789 --
3790 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3791 --
3792 begin
3793 v_header_id := 0;
3794 v_commit_ctr := 0;
3795
3796 for c2 in c1 loop
3797
3798 v_line_id := c2.line_id;
3799 p_line_number := c2.line_number;
3800
3801 /* Create a set */
3802
3803 select oe_sets_s.nextval into v_set_id from dual;
3804
3808 SET_NAME,
3805 insert into oe_sets
3806 (
3807 SET_ID,
3809 SET_TYPE,
3810 HEADER_ID,
3811 SHIP_FROM_ORG_ID,
3812 SHIP_TO_ORG_ID,
3813 SCHEDULE_SHIP_DATE,
3814 SCHEDULE_ARRIVAL_DATE,
3815 FREIGHT_CARRIER_CODE,
3816 SHIPPING_METHOD_CODE,
3817 SHIPMENT_PRIORITY_CODE,
3818 SET_STATUS,
3819 CREATED_BY,
3820 CREATION_DATE,
3821 UPDATED_BY,
3822 UPDATE_DATE,
3823 UPDATE_LOGIN,
3824 INVENTORY_ITEM_ID,
3825 ORDERED_QUANTITY_UOM,
3826 LINE_TYPE_ID,
3827 SHIP_TOLERANCE_ABOVE,
3828 SHIP_TOLERANCE_BELOW
3829 )
3830 values
3831 (
3832 v_set_id, /* SET_ID, */
3833 to_char(v_set_id), /* SET_NAME, */
3834 'LINE_SET', /* SET_TYPE, */
3835 c2.header_id, /* HEADER_ID,*/
3836 null, /* SHIP_FROM_ORG_ID, */
3837 null, /* SHIP_TO_ORG_ID, */
3838 null, /* SCHEDULE_SHIP_DATE, */
3839 null, /* SCHEDULE_ARRIVAL_DATE, */
3840 null, /* FREIGHT_CARRIER_CODE, */
3841 null, /* SHIPPING_METHOD_CODE, */
3842 null, /* SHIPMENT_PRIORITY_CODE, */
3843 null, /* SET_STATUS, */
3844 0, /* CREATED_BY, */
3845 sysdate, /* CREATION_DATE, */
3846 0, /* UPDATED_BY, */
3847 sysdate, /* UPDATE_DATE, */
3848 0, /* UPDATE_LOGIN, */
3849 c2.inventory_item_id, /* INVENTORY_ITEM_ID, */
3850 c2.unit_code, /* ORDERED_QUANTITY_UOM, */
3851 null, /* LINE_TYPE_ID, */
3852 null, /* SHIP_TOLERANCE_ABOVE, */
3853 null /* SHIP_TOLERANCE_BELOW */
3854 );
3855
3856 v_shipment_number := 0;
3857 v_ctr := 0;
3858 for c4 in c3 loop
3859
3860 v_shipment_number := v_shipment_number + 1;
3861
3862 update oe_order_lines_all
3863 set line_set_id = v_set_id,
3864 line_number = p_line_number,
3865 shipment_number = v_shipment_number
3866 where line_id = c4.line_id;
3867 end loop;
3868 v_commit_ctr := v_commit_ctr + 1;
3869
3870 if v_commit_ctr > 500 then
3871 commit;
3872 v_commit_ctr := 0;
3873 end if;
3874
3875 end loop;
3876
3877 v_line_number := 0;
3878 v_header_id := 0;
3879
3880 for c6 in c5 loop
3881 v_line_id := c6.line_id;
3882 p_line_number := c6.line_number;
3883
3884 select oe_sets_s.nextval into v_set_id from dual;
3885
3886 insert into oe_sets
3887 (
3888 SET_ID,
3889 SET_NAME,
3890 SET_TYPE,
3891 HEADER_ID,
3892 SHIP_FROM_ORG_ID,
3893 SHIP_TO_ORG_ID,
3894 SCHEDULE_SHIP_DATE,
3895 SCHEDULE_ARRIVAL_DATE,
3896 FREIGHT_CARRIER_CODE,
3897 SHIPPING_METHOD_CODE,
3898 SHIPMENT_PRIORITY_CODE,
3899 SET_STATUS,
3900 CREATED_BY,
3901 CREATION_DATE,
3902 UPDATED_BY,
3903 UPDATE_DATE,
3904 UPDATE_LOGIN,
3905 INVENTORY_ITEM_ID,
3906 ORDERED_QUANTITY_UOM,
3907 LINE_TYPE_ID,
3908 SHIP_TOLERANCE_ABOVE,
3909 SHIP_TOLERANCE_BELOW
3910 )
3911 values
3912 (
3913 v_set_id, /* SET_ID, */
3914 to_char(v_set_id), /* SET_NAME, */
3915 'LINE_SET', /* SET_TYPE, */
3916 c6.header_id, /* HEADER_ID,*/
3917 null, /* SHIP_FROM_ORG_ID, */
3918 null, /* SHIP_TO_ORG_ID, */
3919 null, /* SCHEDULE_SHIP_DATE, */
3920 null, /* SCHEDULE_ARRIVAL_DATE, */
3921 null, /* FREIGHT_CARRIER_CODE, */
3922 null, /* SHIPPING_METHOD_CODE, */
3923 null, /* SHIPMENT_PRIORITY_CODE, */
3924 null, /* SET_STATUS, */
3925 0, /* CREATED_BY, */
3926 sysdate, /* CREATION_DATE, */
3927 0, /* UPDATED_BY, */
3931 c6.unit_code, /* ORDERED_QUANTITY_UOM, */
3928 sysdate, /* UPDATE_DATE, */
3929 0, /* UPDATE_LOGIN, */
3930 c6.inventory_item_id, /* INVENTORY_ITEM_ID, */
3932 null, /* LINE_TYPE_ID, */
3933 null, /* SHIP_TOLERANCE_ABOVE, */
3934 null /* SHIP_TOLERANCE_BELOW */
3935 );
3936
3937 v_shipment_number := 0;
3938
3939 v_ctr := 0;
3940 for c8 in c7 loop
3941 begin
3942 v_item_type_code := null;
3943 select item_type_code into
3944 v_item_type_code from
3945 oe_order_lines_all
3946 where
3947 line_id = c8.line_id;
3948 exception
3949 when no_data_found then
3950 null;
3951 end ;
3952 IF v_item_type_code = 'MODEL' OR
3953 v_item_type_code = 'STANDARD' OR
3954 v_item_type_code = 'KIT' THEN
3955
3956 v_shipment_number := v_shipment_number + 1;
3957
3958 update oe_order_lines_all ooal
3959 set line_set_id = v_set_id,
3960 line_number = p_line_number,
3961 shipment_number = v_shipment_number
3962 where ooal.line_id = c8.line_id;
3963 END IF;
3964 end loop;
3965 v_commit_ctr := v_commit_ctr + 1;
3966
3967 if v_commit_ctr > 500 then
3968 commit;
3969 v_commit_ctr := 0;
3970 end if;
3971 end loop;
3972 commit;
3973
3974 exception
3975 when others then
3976 rollback;
3977 oe_upg_so.upgrade_insert_errors
3978 (
3979 L_header_id => 0,
3980 L_comments => 'Line set updation failed on ora error: '||to_char(sqlcode)
3981 );
3982 commit;
3983 End Upgrade_Create_Line_Sets;
3984
3985 Procedure Upgrade_Upd_Serv_Ref_line_id
3986 is
3987 cursor c1 is
3988 select header_id, line_id from oe_order_lines_all
3989 where item_type_code = 'SERVICE';
3990 v_new_line_id number;
3991 --
3992 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3993 --
3994 begin
3995 for c2 in c1 loop
3996 begin
3997 select oul2.new_line_id into v_new_line_id
3998 from
3999 oe_upgrade_log_v oul1,
4000 oe_upgrade_log_v oul2,
4001 so_lines_all sla1,
4002 so_lines_all sla2
4003 where oul1.new_line_id = c2.line_id
4004 and oul1.old_line_id = sla1.line_id
4005 and nvl(sla1.service_parent_line_id,0) = nvl(sla2.line_id,0)
4006 and sla2.line_id = oul2.old_line_id
4007 and nvl(oul1.old_line_detail_id,0) = nvl(oul2.old_line_detail_id,0)
4008 and nvl(oul1.picking_line_id,0) = nvl(oul2.picking_line_id,0);
4009
4010 update oe_order_lines_all
4011 set service_reference_line_id = v_new_line_id
4012 where line_Id = c2.line_id;
4013 exception
4014 when no_data_found then
4015 oe_upg_so.Upgrade_Insert_Errors(
4016 c2.header_id,
4017 'FYI Only: Service Ref.Line id not updated in OM for Line '
4018 ||to_char(c2.line_id)||
4019 'as it is not found in Order Entry table');
4020 end;
4021 end loop;
4022 commit;
4023 Exception
4024 when others then
4025 null;
4026 End Upgrade_Upd_Serv_Ref_line_id;
4027
4028
4029 -- 1. Select all the models which have multiple config details.
4030 -- 2. Select all the options for the model.
4031 -- 3. Create new lines for the options of the model and for the model itself.
4032 -- 4. Attach the config item to the model line.
4033 -- 5. update the option quantity for the original option lines.
4034
4035 PROCEDURE Insert_Row
4036 ( p_line_rec IN OE_Order_PUB.Line_Rec_Type,
4037 p_orig_line_id IN Number,
4038 p_upgraded_flag IN Varchar2,
4039 p_apply_price_adj IN Varchar2 default 'Y'
4040 );
4041
4042 FUNCTION Query_Row
4043 ( p_line_id IN NUMBER) RETURN OE_Order_PUB.Line_Rec_Type;
4044
4045 PROCEDURE insert_multiple_models IS
4046
4047 cursor multiple_cfg_detail(p_ato_line_id IN NUMBER) IS
4048 select line_id,ordered_quantity,shipped_quantity
4049 from oe_order_lines_all
4050 where ato_line_id=p_ato_line_id
4051 and item_type_code = 'CONFIG';
4052
4053 cursor multiple_cfg_parent IS
4054 select ato_line_id
4055 from oe_order_lines_all
4056 group by ato_line_id,item_type_code
4057 having item_type_code = 'CONFIG'
4058 and count(*) > 1;
4059
4060 cursor model_and_options(p_ato_line_id IN NUMBER) IS
4061 select line_id,upgraded_flag
4062 from oe_order_lines_all
4063 where ato_line_id=p_ato_line_id
4064 and item_type_code <> 'CONFIG'
4065 order by component_code;
4066
4067 cursor service_lines(p_service_reference_line_id IN NUMBER) IS
4068 select line_id,upgraded_flag
4069 from oe_order_lines_all
4070 where service_reference_line_id = p_service_reference_line_id;
4074 l_cfg_shipped_quantity NUMBER;
4071
4072 l_cfg_line_id NUMBER;
4073 l_cfg_ordered_quantity NUMBER;
4075 p_ato_line_id NUMBER;
4076 l_line_id NUMBER;
4077 l_service_count NUMBER;
4078 l_service_line_id NUMBER;
4079 l_s_upgraded_flag VARCHAR2(1);
4080
4081 l_model_rec OE_ORDER_PUB.line_rec_type;
4082 l_line_rec OE_ORDER_PUB.line_rec_type;
4083 l_service_line_rec OE_ORDER_PUB.line_rec_type;
4084 l_new_line_rec OE_ORDER_PUB.line_rec_type;
4085 l_upgraded_flag VARCHAR2(1);
4086
4087
4088 --
4089 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
4090 --
4091 BEGIN
4092
4093 -- Get the model line which has multiple config lines.
4094 OPEN multiple_cfg_parent;
4095
4096 LOOP
4097
4098 FETCH multiple_cfg_parent INTO p_ato_line_id;
4099 EXIT WHEN multiple_cfg_parent%notfound;
4100
4101 -- Get the multiple config lines.
4102 OPEN multiple_cfg_detail(p_ato_line_id);
4103 FETCH multiple_cfg_detail
4104 INTO l_cfg_line_id,l_cfg_ordered_quantity,l_cfg_shipped_quantity;
4105
4106 IF multiple_cfg_parent%found
4107 THEN
4108
4109 LOOP
4110
4111 -- Get next config line. You should create new model and options
4112 -- only for the second config detail.
4113
4114 FETCH multiple_cfg_detail
4115 INTO l_cfg_line_id,l_cfg_ordered_quantity,l_cfg_shipped_quantity;
4116 EXIT WHEN multiple_cfg_detail%notfound;
4117
4118
4119 -- dbms_output.put_line('Cfg Line Id: ' || l_cfg_line_id);
4120 IF l_debug_level > 0 THEN
4121 oe_debug_pub.add( 'CFG LINE ID: ' || L_CFG_LINE_ID , 1 ) ;
4122 END IF;
4123
4124 -- Create model and option lines for each of the config line.
4125
4126 OPEN model_and_options(p_ato_line_id);
4127
4128 LOOP
4129
4130 FETCH model_and_options
4131 INTO l_line_id,l_upgraded_flag;
4132 EXIT WHEN model_and_options%notfound;
4133
4134
4135 l_line_rec := Query_Row(l_line_id);
4136
4137 IF l_line_rec.item_type_code = 'MODEL' THEN
4138 IF l_debug_level > 0 THEN
4139 oe_debug_pub.add( 'CREATING A NEW MODEL LINE' ) ;
4140 END IF;
4141 l_new_line_rec := l_line_rec;
4142
4143 SELECT oe_order_lines_s.nextval
4144 INTO l_new_line_rec.line_id
4145 FROM dual;
4146
4147 l_new_line_rec.ato_line_id := l_new_line_rec.line_id;
4148 l_new_line_rec.top_model_line_id := l_new_line_rec.line_id;
4149 l_new_line_rec.link_to_line_id := null;
4150 l_new_line_rec.ordered_quantity := l_cfg_ordered_quantity;
4151 l_model_rec := l_new_line_rec;
4152
4153 IF l_debug_level > 0 THEN
4154 oe_debug_pub.add( 'INSERTING A MODEL LINE :' || L_NEW_LINE_REC.LINE_ID ) ;
4155 END IF;
4156
4157 INSERT_ROW(l_new_line_rec, l_line_id,l_upgraded_flag);
4158
4159 -- Update the config item to point to the new model.
4160
4161 UPDATE oe_order_lines_all
4162 SET ato_line_id = l_model_rec.line_id,
4163 top_model_line_id = l_model_rec.line_id
4164 WHERE line_id=l_cfg_line_id;
4165
4166 UPDATE oe_order_lines_all
4167 SET link_to_line_id = l_model_rec.line_id
4168 WHERE line_id=l_cfg_line_id;
4169
4170 OPEN service_lines(l_line_id);
4171
4172 LOOP
4173 FETCH SERVICE_LINES INTO
4174 l_service_line_id,l_s_upgraded_flag;
4175 EXIT WHEN SERVICE_LINES%NOTFOUND;
4176
4177 l_service_line_rec := Query_Row(l_service_line_id);
4178
4179 SELECT oe_order_lines_s.nextval
4180 INTO l_service_line_rec.line_id
4181 FROM dual;
4182
4183 l_service_line_rec.service_reference_line_id
4184 := l_new_line_rec.line_id;
4185 INSERT_ROW(l_service_line_rec,l_service_line_id,l_s_upgraded_flag);
4186
4187 END LOOP;
4188 CLOSE service_lines;
4189
4190 ELSE
4191 -- dbms_output.put_line('Creating a new option line ');
4192 IF l_debug_level > 0 THEN
4193 oe_debug_pub.add( 'CREATING A NEW OPTION LINE ' , 1 ) ;
4194 END IF;
4195 l_new_line_rec := l_line_rec;
4196
4197 SELECT oe_order_lines_s.nextval
4198 INTO l_new_line_rec.line_id
4199 FROM dual;
4200
4201 l_new_line_rec.ato_line_id := l_model_rec.line_id;
4202 l_new_line_rec.top_model_line_id := l_model_rec.line_id;
4203 l_new_line_rec.link_to_line_id := null;
4204 l_new_line_rec.ordered_quantity :=
4205 (l_new_line_rec.ordered_quantity / l_model_rec.ordered_quantity) *
4206 l_cfg_ordered_quantity;
4207
4208 INSERT_ROW(l_new_line_rec,l_line_id,l_upgraded_flag);
4209
4210 OPEN service_lines(l_line_id);
4211
4212 LOOP
4213 FETCH SERVICE_LINES INTO
4214 l_service_line_id,l_s_upgraded_flag;
4215 EXIT WHEN SERVICE_LINES%NOTFOUND;
4216
4217 l_service_line_rec := Query_Row(l_service_line_id);
4221 FROM dual;
4218
4219 SELECT oe_order_lines_s.nextval
4220 INTO l_service_line_rec.line_id
4222
4223 l_service_line_rec.service_reference_line_id
4224 := l_new_line_rec.line_id;
4225
4226 INSERT_ROW(l_service_line_rec,l_service_line_id,l_s_upgraded_flag);
4227
4228 END LOOP;
4229 CLOSE service_lines;
4230 END IF;
4231
4232 END LOOP;
4233
4234 CLOSE model_and_options;
4235
4236 END LOOP;
4237
4238 END IF;
4239
4240 CLOSE multiple_cfg_detail;
4241
4242 END LOOP;
4243
4244 END insert_multiple_models;
4245
4246
4247 FUNCTION Query_Row
4248 ( p_line_id IN NUMBER) RETURN OE_Order_PUB.Line_Rec_Type
4249 IS
4250 l_line_rec OE_Order_PUB.Line_Rec_Type;
4251 l_line_tbl OE_Order_PUB.Line_Tbl_Type;
4252
4253 CURSOR l_line_csr IS
4254 SELECT ACCOUNTING_RULE_ID
4255 , ACTUAL_ARRIVAL_DATE
4256 , ACTUAL_SHIPMENT_DATE
4257 , AGREEMENT_ID
4258 , ARRIVAL_SET_ID
4259 , ATO_LINE_ID
4260 , ATTRIBUTE1
4261 , ATTRIBUTE10
4262 , ATTRIBUTE11
4263 , ATTRIBUTE12
4264 , ATTRIBUTE13
4265 , ATTRIBUTE14
4266 , ATTRIBUTE15
4267 , ATTRIBUTE2
4268 , ATTRIBUTE3
4269 , ATTRIBUTE4
4270 , ATTRIBUTE5
4271 , ATTRIBUTE6
4272 , ATTRIBUTE7
4273 , ATTRIBUTE8
4274 , ATTRIBUTE9
4275 , AUTO_SELECTED_QUANTITY
4276 , AUTHORIZED_TO_SHIP_FLAG
4277 , BOOKED_FLAG
4278 , CANCELLED_FLAG
4279 , CANCELLED_QUANTITY
4280 , COMPONENT_CODE
4281 , COMPONENT_NUMBER
4282 , COMPONENT_SEQUENCE_ID
4283 , CONFIG_HEADER_ID
4284 , CONFIG_REV_NBR
4285 , CONFIG_DISPLAY_SEQUENCE
4286 , CONFIGURATION_ID
4287 , CONTEXT
4288 , CREATED_BY
4289 , CREATION_DATE
4290 , CREDIT_INVOICE_LINE_ID
4291 , CUSTOMER_DOCK_CODE
4292 , CUSTOMER_JOB
4293 , CUSTOMER_PRODUCTION_LINE
4294 , CUST_PRODUCTION_SEQ_NUM
4295 , CUSTOMER_TRX_LINE_ID
4296 , CUST_MODEL_SERIAL_NUMBER
4297 , CUST_PO_NUMBER
4298 , DELIVERY_LEAD_TIME
4299 , DELIVER_TO_CONTACT_ID
4300 , DELIVER_TO_ORG_ID
4301 , DEMAND_BUCKET_TYPE_CODE
4302 , DEMAND_CLASS_CODE
4303 , DEP_PLAN_REQUIRED_FLAG
4304 , EARLIEST_ACCEPTABLE_DATE
4305 , END_ITEM_UNIT_NUMBER
4306 , EXPLOSION_DATE
4307 , FIRST_ACK_CODE
4308 , FIRST_ACK_DATE
4309 , FOB_POINT_CODE
4310 , FREIGHT_CARRIER_CODE
4311 , FREIGHT_TERMS_CODE
4312 , FULFILLED_QUANTITY
4313 , FULFILLED_FLAG
4314 , FULFILLMENT_METHOD_CODE
4315 , FULFILLMENT_DATE
4316 , GLOBAL_ATTRIBUTE1
4317 , GLOBAL_ATTRIBUTE10
4318 , GLOBAL_ATTRIBUTE11
4319 , GLOBAL_ATTRIBUTE12
4320 , GLOBAL_ATTRIBUTE13
4321 , GLOBAL_ATTRIBUTE14
4322 , GLOBAL_ATTRIBUTE15
4323 , GLOBAL_ATTRIBUTE16
4324 , GLOBAL_ATTRIBUTE17
4325 , GLOBAL_ATTRIBUTE18
4326 , GLOBAL_ATTRIBUTE19
4327 , GLOBAL_ATTRIBUTE2
4328 , GLOBAL_ATTRIBUTE20
4329 , GLOBAL_ATTRIBUTE3
4330 , GLOBAL_ATTRIBUTE4
4331 , GLOBAL_ATTRIBUTE5
4332 , GLOBAL_ATTRIBUTE6
4333 , GLOBAL_ATTRIBUTE7
4334 , GLOBAL_ATTRIBUTE8
4335 , GLOBAL_ATTRIBUTE9
4336 , GLOBAL_ATTRIBUTE_CATEGORY
4337 , HEADER_ID
4338 , INDUSTRY_ATTRIBUTE1
4339 , INDUSTRY_ATTRIBUTE10
4340 , INDUSTRY_ATTRIBUTE11
4341 , INDUSTRY_ATTRIBUTE12
4342 , INDUSTRY_ATTRIBUTE13
4343 , INDUSTRY_ATTRIBUTE14
4344 , INDUSTRY_ATTRIBUTE15
4345 , INDUSTRY_ATTRIBUTE16
4346 , INDUSTRY_ATTRIBUTE17
4347 , INDUSTRY_ATTRIBUTE18
4348 , INDUSTRY_ATTRIBUTE19
4349 , INDUSTRY_ATTRIBUTE20
4350 , INDUSTRY_ATTRIBUTE21
4351 , INDUSTRY_ATTRIBUTE22
4352 , INDUSTRY_ATTRIBUTE23
4353 , INDUSTRY_ATTRIBUTE24
4354 , INDUSTRY_ATTRIBUTE25
4355 , INDUSTRY_ATTRIBUTE26
4356 , INDUSTRY_ATTRIBUTE27
4357 , INDUSTRY_ATTRIBUTE28
4358 , INDUSTRY_ATTRIBUTE29
4359 , INDUSTRY_ATTRIBUTE30
4360 , INDUSTRY_ATTRIBUTE2
4361 , INDUSTRY_ATTRIBUTE3
4362 , INDUSTRY_ATTRIBUTE4
4363 , INDUSTRY_ATTRIBUTE5
4364 , INDUSTRY_ATTRIBUTE6
4365 , INDUSTRY_ATTRIBUTE7
4366 , INDUSTRY_ATTRIBUTE8
4367 , INDUSTRY_ATTRIBUTE9
4368 , INDUSTRY_CONTEXT
4369 , INTMED_SHIP_TO_CONTACT_ID
4370 , INTMED_SHIP_TO_ORG_ID
4371 , INVENTORY_ITEM_ID
4372 , INVOICE_INTERFACE_STATUS_CODE
4373 , INVOICE_TO_CONTACT_ID
4374 , INVOICE_TO_ORG_ID
4375 , INVOICED_QUANTITY
4376 , INVOICING_RULE_ID
4377 , ORDERED_ITEM_ID
4378 , ITEM_IDENTIFIER_TYPE
4379 , ORDERED_ITEM
4380 , ITEM_REVISION
4381 , ITEM_TYPE_CODE
4382 , LAST_ACK_CODE
4383 , LAST_ACK_DATE
4387 , LATEST_ACCEPTABLE_DATE
4384 , LAST_UPDATED_BY
4385 , LAST_UPDATE_DATE
4386 , LAST_UPDATE_LOGIN
4388 , LINE_CATEGORY_CODE
4389 , LINE_ID
4390 , LINE_NUMBER
4391 , LINE_TYPE_ID
4392 , LINK_TO_LINE_ID
4393 , MODEL_GROUP_NUMBER
4394 -- , MFG_COMPONENT_SEQUENCE_ID
4395 , OPEN_FLAG
4396 , OPTION_FLAG
4397 , OPTION_NUMBER
4398 , ORDERED_QUANTITY
4399 , ORDER_QUANTITY_UOM
4400 , ORG_ID
4401 , ORIG_SYS_DOCUMENT_REF
4402 , ORIG_SYS_LINE_REF
4403 , OVER_SHIP_REASON_CODE
4404 , OVER_SHIP_RESOLVED_FLAG
4405 , PAYMENT_TERM_ID
4406 , PLANNING_PRIORITY
4407 , PRICE_LIST_ID
4408 , PRICING_ATTRIBUTE1
4409 , PRICING_ATTRIBUTE10
4410 , PRICING_ATTRIBUTE2
4411 , PRICING_ATTRIBUTE3
4412 , PRICING_ATTRIBUTE4
4413 , PRICING_ATTRIBUTE5
4414 , PRICING_ATTRIBUTE6
4415 , PRICING_ATTRIBUTE7
4416 , PRICING_ATTRIBUTE8
4417 , PRICING_ATTRIBUTE9
4418 , PRICING_CONTEXT
4419 , PRICING_DATE
4420 , PRICING_QUANTITY
4421 , PRICING_QUANTITY_UOM
4422 , PROGRAM_APPLICATION_ID
4423 , PROGRAM_ID
4424 , PROGRAM_UPDATE_DATE
4425 , PROJECT_ID
4426 , PROMISE_DATE
4427 , RE_SOURCE_FLAG
4428 , REFERENCE_CUSTOMER_TRX_LINE_ID
4429 , REFERENCE_HEADER_ID
4430 , REFERENCE_LINE_ID
4431 , REFERENCE_TYPE
4432 , REQUEST_DATE
4433 , REQUEST_ID
4434 , RETURN_ATTRIBUTE1
4435 , RETURN_ATTRIBUTE10
4436 , RETURN_ATTRIBUTE11
4437 , RETURN_ATTRIBUTE12
4438 , RETURN_ATTRIBUTE13
4439 , RETURN_ATTRIBUTE14
4440 , RETURN_ATTRIBUTE15
4441 , RETURN_ATTRIBUTE2
4442 , RETURN_ATTRIBUTE3
4443 , RETURN_ATTRIBUTE4
4444 , RETURN_ATTRIBUTE5
4445 , RETURN_ATTRIBUTE6
4446 , RETURN_ATTRIBUTE7
4447 , RETURN_ATTRIBUTE8
4448 , RETURN_ATTRIBUTE9
4449 , RETURN_CONTEXT
4450 , RETURN_REASON_CODE
4451 , RLA_SCHEDULE_TYPE_CODE
4452 , SALESREP_ID
4453 , SCHEDULE_ARRIVAL_DATE
4454 , SCHEDULE_SHIP_DATE
4455 , SCHEDULE_STATUS_CODE
4456 , SHIPMENT_NUMBER
4457 , SHIPMENT_PRIORITY_CODE
4458 , SHIPPED_QUANTITY
4459 , SHIPPING_METHOD_CODE
4460 , SHIPPING_QUANTITY
4461 , SHIPPING_QUANTITY_UOM
4462 , SHIP_FROM_ORG_ID
4463 , SHIP_SET_ID
4464 , SHIP_TOLERANCE_ABOVE
4465 , SHIP_TOLERANCE_BELOW
4466 , SHIPPABLE_FLAG
4467 , SHIPPING_INTERFACED_FLAG
4468 , SHIP_TO_CONTACT_ID
4469 , SHIP_TO_ORG_ID
4470 , SHIP_MODEL_COMPLETE_FLAG
4471 , SOLD_TO_ORG_ID
4472 , SOLD_FROM_ORG_ID
4473 , SORT_ORDER
4474 , SOURCE_DOCUMENT_ID
4475 , SOURCE_DOCUMENT_LINE_ID
4476 , SOURCE_DOCUMENT_TYPE_ID
4477 , SOURCE_TYPE_CODE
4478 , SPLIT_FROM_LINE_ID
4479 , LINE_SET_ID
4480 , SPLIT_BY
4481 , MODEL_REMNANT_FLAG
4482 , TASK_ID
4483 , TAX_CODE
4484 , TAX_DATE
4485 , TAX_EXEMPT_FLAG
4486 , TAX_EXEMPT_NUMBER
4487 , TAX_EXEMPT_REASON_CODE
4488 , TAX_POINT_CODE
4489 , TAX_RATE
4490 , TAX_VALUE
4491 , TOP_MODEL_LINE_ID
4492 , UNIT_LIST_PRICE
4493 , UNIT_SELLING_PRICE
4494 , VISIBLE_DEMAND_FLAG
4495 , VEH_CUS_ITEM_CUM_KEY_ID
4496 , SHIPPING_INSTRUCTIONS
4497 , PACKING_INSTRUCTIONS
4498 , SERVICE_TXN_REASON_CODE
4499 , SERVICE_TXN_COMMENTS
4500 , SERVICE_DURATION
4501 , SERVICE_PERIOD
4502 , SERVICE_START_DATE
4503 , SERVICE_END_DATE
4504 , SERVICE_COTERMINATE_FLAG
4505 , UNIT_LIST_PERCENT
4506 , UNIT_SELLING_PERCENT
4507 , UNIT_PERCENT_BASE_PRICE
4508 , SERVICE_NUMBER
4509 , SERVICE_REFERENCE_TYPE_CODE
4510 , SERVICE_REFERENCE_LINE_ID
4511 , SERVICE_REFERENCE_SYSTEM_ID
4512 , TP_CONTEXT
4513 , TP_ATTRIBUTE1
4514 , TP_ATTRIBUTE2
4515 , TP_ATTRIBUTE3
4516 , TP_ATTRIBUTE4
4517 , TP_ATTRIBUTE5
4518 , TP_ATTRIBUTE6
4519 , TP_ATTRIBUTE7
4520 , TP_ATTRIBUTE8
4521 , TP_ATTRIBUTE9
4522 , TP_ATTRIBUTE10
4523 , TP_ATTRIBUTE11
4524 , TP_ATTRIBUTE12
4525 , TP_ATTRIBUTE13
4526 , TP_ATTRIBUTE14
4527 , TP_ATTRIBUTE15
4528 , FLOW_STATUS_CODE
4529 , MARKETING_SOURCE_CODE_ID
4530 , CALCULATE_PRICE_FLAG
4531 , COMMITMENT_ID
4532 , ORDER_SOURCE_ID -- aksingh
4533 FROM OE_ORDER_LINES_ALL
4534 WHERE LINE_ID = p_line_id ;
4535
4536
4537 --
4538 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
4539 --
4540 BEGIN
4541
4542 -- Loop over fetched records
4543
4544 FOR l_implicit_rec IN l_line_csr LOOP
4545
4546 l_line_rec.accounting_rule_id := l_implicit_rec.ACCOUNTING_RULE_ID;
4547 l_line_rec.actual_arrival_date := l_implicit_rec.ACTUAL_ARRIVAL_DATE;
4548 l_line_rec.actual_shipment_date := l_implicit_rec.ACTUAL_SHIPMENT_DATE;
4549 l_line_rec.agreement_id := l_implicit_rec.AGREEMENT_ID;
4550 l_line_rec.arrival_Set_id := l_implicit_rec.ARRIVAL_SET_ID;
4551 l_line_rec.ato_line_id := l_implicit_rec.ATO_LINE_ID;
4552 l_line_rec.attribute1 := l_implicit_rec.ATTRIBUTE1;
4553 l_line_rec.attribute10 := l_implicit_rec.ATTRIBUTE10;
4554 l_line_rec.attribute11 := l_implicit_rec.ATTRIBUTE11;
4555 l_line_rec.attribute12 := l_implicit_rec.ATTRIBUTE12;
4556 l_line_rec.attribute13 := l_implicit_rec.ATTRIBUTE13;
4557 l_line_rec.attribute14 := l_implicit_rec.ATTRIBUTE14;
4558 l_line_rec.attribute15 := l_implicit_rec.ATTRIBUTE15;
4559 l_line_rec.attribute2 := l_implicit_rec.ATTRIBUTE2;
4560 l_line_rec.attribute3 := l_implicit_rec.ATTRIBUTE3;
4561 l_line_rec.attribute4 := l_implicit_rec.ATTRIBUTE4;
4562 l_line_rec.attribute5 := l_implicit_rec.ATTRIBUTE5;
4563 l_line_rec.attribute6 := l_implicit_rec.ATTRIBUTE6;
4564 l_line_rec.attribute7 := l_implicit_rec.ATTRIBUTE7;
4565 l_line_rec.attribute8 := l_implicit_rec.ATTRIBUTE8;
4566 l_line_rec.attribute9 := l_implicit_rec.ATTRIBUTE9;
4567 l_line_rec.auto_selected_quantity := l_implicit_rec.AUTO_SELECTED_QUANTITY;
4568 l_line_rec.Authorized_to_ship_flag := l_implicit_rec.Authorized_to_ship_flag;
4569 l_line_rec.booked_flag := l_implicit_rec.booked_flag;
4570 l_line_rec.cancelled_flag := l_implicit_rec.cancelled_flag;
4571 l_line_rec.cancelled_quantity := l_implicit_rec.CANCELLED_QUANTITY;
4572 l_line_rec.component_code := l_implicit_rec.COMPONENT_CODE;
4573 l_line_rec.component_number := l_implicit_rec.COMPONENT_NUMBER;
4574 l_line_rec.component_sequence_id := l_implicit_rec.COMPONENT_SEQUENCE_ID;
4575 l_line_rec.config_header_id := l_implicit_rec.CONFIG_HEADER_ID;
4576 l_line_rec.config_rev_nbr := l_implicit_rec.CONFIG_REV_NBR;
4577 l_line_rec.config_display_sequence := l_implicit_rec.CONFIG_DISPLAY_SEQUENCE;
4578 l_line_rec.configuration_id := l_implicit_rec.CONFIGURATION_ID;
4579 l_line_rec.context := l_implicit_rec.CONTEXT;
4580
4581 l_line_rec.created_by := l_implicit_rec.CREATED_BY;
4582 l_line_rec.creation_date := l_implicit_rec.CREATION_DATE;
4583 l_line_rec.credit_invoice_line_id := l_implicit_rec.CREDIT_INVOICE_LINE_ID;
4584 l_line_rec.customer_dock_code := l_implicit_rec.CUSTOMER_DOCK_CODE;
4585 l_line_rec.customer_job := l_implicit_rec.CUSTOMER_JOB;
4586 l_line_rec.customer_production_line := l_implicit_rec.CUSTOMER_PRODUCTION_LINE;
4587 l_line_rec.cust_production_seq_num := l_implicit_rec.CUST_PRODUCTION_SEQ_NUM;
4588 l_line_rec.customer_trx_line_id := l_implicit_rec.CUSTOMER_TRX_LINE_ID;
4589 l_line_rec.cust_model_serial_number := l_implicit_rec.CUST_MODEL_SERIAL_NUMBER;
4590 l_line_rec.cust_po_number := l_implicit_rec.CUST_PO_NUMBER;
4591 l_line_rec.delivery_lead_time := l_implicit_rec.DELIVERY_LEAD_TIME;
4592 l_line_rec.deliver_to_contact_id := l_implicit_rec.DELIVER_TO_CONTACT_ID;
4593 l_line_rec.deliver_to_org_id := l_implicit_rec.DELIVER_TO_ORG_ID;
4594 l_line_rec.demand_bucket_type_code := l_implicit_rec.DEMAND_BUCKET_TYPE_CODE;
4595 l_line_rec.demand_class_code := l_implicit_rec.DEMAND_CLASS_CODE;
4596 l_line_rec.dep_plan_required_flag := l_implicit_rec.DEP_PLAN_REQUIRED_FLAG;
4597
4598 l_line_rec.earliest_acceptable_date := l_implicit_rec.EARLIEST_ACCEPTABLE_DATE;
4599 l_line_rec.end_item_unit_number := l_implicit_rec.END_ITEM_UNIT_NUMBER;
4600 l_line_rec.explosion_date := l_implicit_rec.EXPLOSION_DATE;
4601 l_line_rec.first_ack_code := l_implicit_rec.FIRST_ACK_CODE;
4602 l_line_rec.first_ack_date := l_implicit_rec.FIRST_ACK_DATE;
4603 l_line_rec.fob_point_code := l_implicit_rec.FOB_POINT_CODE;
4604 l_line_rec.freight_carrier_code := l_implicit_rec.FREIGHT_CARRIER_CODE;
4605 l_line_rec.freight_terms_code := l_implicit_rec.FREIGHT_TERMS_CODE;
4606 l_line_rec.fulfilled_quantity := l_implicit_rec.FULFILLED_QUANTITY;
4607 l_line_rec.fulfilled_flag := l_implicit_rec.FULFILLED_FLAG;
4608 l_line_rec.fulfillment_method_code := l_implicit_rec.FULFILLMENT_METHOD_CODE;
4609 l_line_rec.fulfillment_date := l_implicit_rec.FULFILLMENT_DATE;
4610 l_line_rec.global_attribute1 := l_implicit_rec.GLOBAL_ATTRIBUTE1;
4611 l_line_rec.global_attribute10 := l_implicit_rec.GLOBAL_ATTRIBUTE10;
4612 l_line_rec.global_attribute11 := l_implicit_rec.GLOBAL_ATTRIBUTE11;
4613 l_line_rec.global_attribute12 := l_implicit_rec.GLOBAL_ATTRIBUTE12;
4614 l_line_rec.global_attribute13 := l_implicit_rec.GLOBAL_ATTRIBUTE13;
4615 l_line_rec.global_attribute14 := l_implicit_rec.GLOBAL_ATTRIBUTE14;
4616 l_line_rec.global_attribute15 := l_implicit_rec.GLOBAL_ATTRIBUTE15;
4617 l_line_rec.global_attribute16 := l_implicit_rec.GLOBAL_ATTRIBUTE16;
4621 l_line_rec.global_attribute2 := l_implicit_rec.GLOBAL_ATTRIBUTE2;
4618 l_line_rec.global_attribute17 := l_implicit_rec.GLOBAL_ATTRIBUTE17;
4619 l_line_rec.global_attribute18 := l_implicit_rec.GLOBAL_ATTRIBUTE18;
4620 l_line_rec.global_attribute19 := l_implicit_rec.GLOBAL_ATTRIBUTE19;
4622 l_line_rec.global_attribute20 := l_implicit_rec.GLOBAL_ATTRIBUTE20;
4623 l_line_rec.global_attribute3 := l_implicit_rec.GLOBAL_ATTRIBUTE3;
4624 l_line_rec.global_attribute4 := l_implicit_rec.GLOBAL_ATTRIBUTE4;
4625 l_line_rec.global_attribute5 := l_implicit_rec.GLOBAL_ATTRIBUTE5;
4626 l_line_rec.global_attribute6 := l_implicit_rec.GLOBAL_ATTRIBUTE6;
4627 l_line_rec.global_attribute7 := l_implicit_rec.GLOBAL_ATTRIBUTE7;
4628 l_line_rec.global_attribute8 := l_implicit_rec.GLOBAL_ATTRIBUTE8;
4629 l_line_rec.global_attribute9 := l_implicit_rec.GLOBAL_ATTRIBUTE9;
4630 l_line_rec.global_attribute_category := l_implicit_rec.GLOBAL_ATTRIBUTE_CATEGORY;
4631 l_line_rec.header_id := l_implicit_rec.HEADER_ID;
4632 l_line_rec.industry_attribute1 := l_implicit_rec.INDUSTRY_ATTRIBUTE1;
4633 l_line_rec.industry_attribute10 := l_implicit_rec.INDUSTRY_ATTRIBUTE10;
4634 l_line_rec.industry_attribute11 := l_implicit_rec.INDUSTRY_ATTRIBUTE11;
4635 l_line_rec.industry_attribute12 := l_implicit_rec.INDUSTRY_ATTRIBUTE12;
4636 l_line_rec.industry_attribute13 := l_implicit_rec.INDUSTRY_ATTRIBUTE13;
4637 l_line_rec.industry_attribute14 := l_implicit_rec.INDUSTRY_ATTRIBUTE14;
4638 l_line_rec.industry_attribute15 := l_implicit_rec.INDUSTRY_ATTRIBUTE15;
4639 l_line_rec.industry_attribute16 := l_implicit_rec.INDUSTRY_ATTRIBUTE16;
4640 l_line_rec.industry_attribute17 := l_implicit_rec.INDUSTRY_ATTRIBUTE17;
4641 l_line_rec.industry_attribute18 := l_implicit_rec.INDUSTRY_ATTRIBUTE18;
4642 l_line_rec.industry_attribute19 := l_implicit_rec.INDUSTRY_ATTRIBUTE19;
4643 l_line_rec.industry_attribute20 := l_implicit_rec.INDUSTRY_ATTRIBUTE20;
4644 l_line_rec.industry_attribute21 := l_implicit_rec.INDUSTRY_ATTRIBUTE21;
4645 l_line_rec.industry_attribute22 := l_implicit_rec.INDUSTRY_ATTRIBUTE22;
4646 l_line_rec.industry_attribute23:= l_implicit_rec.INDUSTRY_ATTRIBUTE23;
4647 l_line_rec.industry_attribute24 := l_implicit_rec.INDUSTRY_ATTRIBUTE24;
4648 l_line_rec.industry_attribute25 := l_implicit_rec.INDUSTRY_ATTRIBUTE25;
4649 l_line_rec.industry_attribute26 := l_implicit_rec.INDUSTRY_ATTRIBUTE26;
4650 l_line_rec.industry_attribute27 := l_implicit_rec.INDUSTRY_ATTRIBUTE27;
4651 l_line_rec.industry_attribute28 := l_implicit_rec.INDUSTRY_ATTRIBUTE28;
4652 l_line_rec.industry_attribute29 := l_implicit_rec.INDUSTRY_ATTRIBUTE29;
4653 l_line_rec.industry_attribute30 := l_implicit_rec.INDUSTRY_ATTRIBUTE30;
4654 l_line_rec.industry_attribute2 := l_implicit_rec.INDUSTRY_ATTRIBUTE2;
4655 l_line_rec.industry_attribute3 := l_implicit_rec.INDUSTRY_ATTRIBUTE3;
4656 l_line_rec.industry_attribute4 := l_implicit_rec.INDUSTRY_ATTRIBUTE4;
4657 l_line_rec.industry_attribute5 := l_implicit_rec.INDUSTRY_ATTRIBUTE5;
4658 l_line_rec.industry_attribute6 := l_implicit_rec.INDUSTRY_ATTRIBUTE6;
4659 l_line_rec.industry_attribute7 := l_implicit_rec.INDUSTRY_ATTRIBUTE7;
4660 l_line_rec.industry_attribute8 := l_implicit_rec.INDUSTRY_ATTRIBUTE8;
4661 l_line_rec.industry_attribute9 := l_implicit_rec.INDUSTRY_ATTRIBUTE9;
4662 l_line_rec.industry_context := l_implicit_rec.INDUSTRY_CONTEXT;
4663 l_line_rec.intermed_ship_to_contact_id := l_implicit_rec.INTMED_SHIP_TO_CONTACT_ID;
4664 l_line_rec.intermed_ship_to_org_id := l_implicit_rec.INTMED_SHIP_TO_ORG_ID;
4665 l_line_rec.inventory_item_id := l_implicit_rec.INVENTORY_ITEM_ID;
4666 l_line_rec.invoice_interface_status_code := l_implicit_rec.INVOICE_INTERFACE_STATUS_CODE;
4667
4668 l_line_rec.invoice_to_contact_id := l_implicit_rec.INVOICE_TO_CONTACT_ID;
4669 l_line_rec.invoice_to_org_id := l_implicit_rec.INVOICE_TO_ORG_ID;
4670 l_line_rec.invoiced_quantity := l_implicit_rec.INVOICED_QUANTITY;
4671 l_line_rec.invoicing_rule_id := l_implicit_rec.INVOICING_RULE_ID;
4672 l_line_rec.ordered_item_id := l_implicit_rec.ORDERED_ITEM_ID;
4673 l_line_rec.item_identifier_type := l_implicit_rec.ITEM_IDENTIFIER_TYPE;
4674 l_line_rec.ordered_item := l_implicit_rec.ORDERED_ITEM;
4675 l_line_rec.item_revision := l_implicit_rec.ITEM_REVISION;
4676 l_line_rec.item_type_code := l_implicit_rec.ITEM_TYPE_CODE;
4677 l_line_rec.last_ack_code := l_implicit_rec.LAST_ACK_CODE;
4678 l_line_rec.last_ack_date := l_implicit_rec.LAST_ACK_DATE;
4679 l_line_rec.last_updated_by := l_implicit_rec.LAST_UPDATED_BY;
4680 l_line_rec.last_update_date := l_implicit_rec.LAST_UPDATE_DATE;
4681 l_line_rec.last_update_login := l_implicit_rec.LAST_UPDATE_LOGIN;
4682 l_line_rec.latest_acceptable_date := l_implicit_rec.LATEST_ACCEPTABLE_DATE;
4683 l_line_rec.line_category_code := l_implicit_rec.LINE_CATEGORY_CODE;
4684 l_line_rec.line_id := l_implicit_rec.LINE_ID;
4685 l_line_rec.line_number := l_implicit_rec.LINE_NUMBER;
4686 l_line_rec.line_type_id := l_implicit_rec.LINE_TYPE_ID;
4687 l_line_rec.link_to_line_id := l_implicit_rec.LINK_TO_LINE_ID;
4688
4689 l_line_rec.model_group_number := l_implicit_rec.MODEL_GROUP_NUMBER;
4690 -- l_line_rec.mfg_component_sequence_id := l_implicit_rec.MFG_COMPONENT_SEQUENCE_ID;
4691 l_line_rec.open_flag := l_implicit_rec.open_flag;
4692 l_line_rec.option_flag := l_implicit_rec.OPTION_FLAG;
4693 l_line_rec.option_number := l_implicit_rec.OPTION_NUMBER;
4694 l_line_rec.ordered_quantity := l_implicit_rec.ORDERED_QUANTITY;
4695 l_line_rec.order_quantity_uom := l_implicit_rec.ORDER_QUANTITY_UOM;
4696 l_line_rec.org_id := l_implicit_rec.ORG_ID;
4697 l_line_rec.orig_sys_document_ref := l_implicit_rec.ORIG_SYS_DOCUMENT_REF;
4701 l_line_rec.source_document_line_id := l_implicit_rec.SOURCE_DOCUMENT_LINE_ID;
4698 l_line_rec.orig_sys_line_ref := l_implicit_rec.ORIG_SYS_LINE_REF;
4699 l_line_rec.over_ship_reason_code := l_implicit_rec.OVER_SHIP_REASON_CODE;
4700 l_line_rec.over_ship_resolved_flag := l_implicit_rec.OVER_SHIP_RESOLVED_FLAG;
4702 l_line_rec.payment_term_id := l_implicit_rec.PAYMENT_TERM_ID;
4703 l_line_rec.planning_priority := l_implicit_rec.PLANNING_PRIORITY;
4704 l_line_rec.price_list_id := l_implicit_rec.PRICE_LIST_ID;
4705 l_line_rec.pricing_attribute1 := l_implicit_rec.PRICING_ATTRIBUTE1;
4706 l_line_rec.pricing_attribute10 := l_implicit_rec.PRICING_ATTRIBUTE10;
4707 l_line_rec.pricing_attribute2 := l_implicit_rec.PRICING_ATTRIBUTE2;
4708 l_line_rec.pricing_attribute3 := l_implicit_rec.PRICING_ATTRIBUTE3;
4709 l_line_rec.pricing_attribute4 := l_implicit_rec.PRICING_ATTRIBUTE4;
4710 l_line_rec.pricing_attribute5 := l_implicit_rec.PRICING_ATTRIBUTE5;
4711 l_line_rec.pricing_attribute6 := l_implicit_rec.PRICING_ATTRIBUTE6;
4712 l_line_rec.pricing_attribute7 := l_implicit_rec.PRICING_ATTRIBUTE7;
4713 l_line_rec.pricing_attribute8 := l_implicit_rec.PRICING_ATTRIBUTE8;
4714 l_line_rec.pricing_attribute9 := l_implicit_rec.PRICING_ATTRIBUTE9;
4715 l_line_rec.pricing_context := l_implicit_rec.PRICING_CONTEXT;
4716 l_line_rec.pricing_date := l_implicit_rec.PRICING_DATE;
4717 l_line_rec.pricing_quantity := l_implicit_rec.PRICING_QUANTITY;
4718 l_line_rec.pricing_quantity_uom := l_implicit_rec.PRICING_QUANTITY_UOM;
4719 l_line_rec.program_application_id := l_implicit_rec.PROGRAM_APPLICATION_ID;
4720 l_line_rec.program_id := l_implicit_rec.PROGRAM_ID;
4721 l_line_rec.program_update_date := l_implicit_rec.PROGRAM_UPDATE_DATE;
4722 l_line_rec.project_id := l_implicit_rec.PROJECT_ID;
4723 l_line_rec.promise_date := l_implicit_rec.PROMISE_DATE;
4724 l_line_rec.re_source_flag := l_implicit_rec.RE_SOURCE_FLAG;
4725 l_line_rec.reference_customer_trx_line_id := l_implicit_rec.reference_customer_trx_line_id;
4726 l_line_rec.reference_header_id := l_implicit_rec.REFERENCE_HEADER_ID;
4727 l_line_rec.reference_line_id := l_implicit_rec.REFERENCE_LINE_ID;
4728 l_line_rec.reference_type := l_implicit_rec.REFERENCE_TYPE;
4729
4730 l_line_rec.request_date := l_implicit_rec.REQUEST_DATE;
4731 l_line_rec.request_id := l_implicit_rec.REQUEST_ID;
4732 l_line_rec.return_attribute1 := l_implicit_rec.RETURN_ATTRIBUTE1;
4733 l_line_rec.return_attribute10 := l_implicit_rec.RETURN_ATTRIBUTE10;
4734 l_line_rec.return_attribute11 := l_implicit_rec.RETURN_ATTRIBUTE11;
4735 l_line_rec.return_attribute12 := l_implicit_rec.RETURN_ATTRIBUTE12;
4736 l_line_rec.return_attribute13 := l_implicit_rec.RETURN_ATTRIBUTE13;
4737 l_line_rec.return_attribute14 := l_implicit_rec.RETURN_ATTRIBUTE14;
4738 l_line_rec.return_attribute15 := l_implicit_rec.RETURN_ATTRIBUTE15;
4739 l_line_rec.return_attribute2 := l_implicit_rec.RETURN_ATTRIBUTE2;
4740 l_line_rec.return_attribute3 := l_implicit_rec.RETURN_ATTRIBUTE3;
4741 l_line_rec.return_attribute4 := l_implicit_rec.RETURN_ATTRIBUTE4;
4742 l_line_rec.return_attribute5 := l_implicit_rec.RETURN_ATTRIBUTE5;
4743 l_line_rec.return_attribute6 := l_implicit_rec.RETURN_ATTRIBUTE6;
4744 l_line_rec.return_attribute7 := l_implicit_rec.RETURN_ATTRIBUTE7;
4745 l_line_rec.return_attribute8 := l_implicit_rec.RETURN_ATTRIBUTE8;
4746 l_line_rec.return_attribute9 := l_implicit_rec.RETURN_ATTRIBUTE9;
4747 l_line_rec.return_context := l_implicit_rec.RETURN_CONTEXT;
4748 l_line_rec.return_reason_code := l_implicit_rec.RETURN_REASON_CODE;
4749 l_line_rec.salesrep_id := l_implicit_rec.SALESREP_ID;
4750 l_line_rec.rla_schedule_type_code := l_implicit_rec.RLA_SCHEDULE_TYPE_CODE;
4751 l_line_rec.schedule_arrival_date := l_implicit_rec.SCHEDULE_ARRIVAL_DATE;
4752 l_line_rec.schedule_ship_date := l_implicit_rec.SCHEDULE_SHIP_DATE;
4753 l_line_rec.schedule_status_code := l_implicit_rec.SCHEDULE_STATUS_CODE;
4754 l_line_rec.shipment_number := l_implicit_rec.SHIPMENT_NUMBER;
4755 l_line_rec.shipment_priority_code := l_implicit_rec.SHIPMENT_PRIORITY_CODE;
4756 l_line_rec.shipped_quantity := l_implicit_rec.SHIPPED_QUANTITY;
4757 l_line_rec.shipping_method_code := l_implicit_rec.SHIPPING_METHOD_CODE;
4758 l_line_rec.shipping_quantity := l_implicit_rec.SHIPPING_QUANTITY;
4759 l_line_rec.shipping_quantity_uom := l_implicit_rec.SHIPPING_QUANTITY_UOM;
4760 l_line_rec.ship_from_org_id := l_implicit_rec.SHIP_FROM_ORG_ID;
4761 l_line_rec.ship_set_id := l_implicit_rec.SHIP_SET_ID;
4762 l_line_rec.ship_tolerance_above := l_implicit_rec.SHIP_TOLERANCE_ABOVE;
4763 l_line_rec.ship_tolerance_below := l_implicit_rec.SHIP_TOLERANCE_BELOW;
4764 l_line_rec.shippable_flag := l_implicit_rec.SHIPPABLE_FLAG;
4765 l_line_rec.shipping_interfaced_flag := l_implicit_rec.SHIPPING_INTERFACED_FLAG;
4766 l_line_rec.ship_to_contact_id := l_implicit_rec.SHIP_TO_CONTACT_ID;
4767 l_line_rec.ship_to_org_id := l_implicit_rec.SHIP_TO_ORG_ID;
4768 l_line_rec.ship_model_complete_flag := l_implicit_rec.SHIP_MODEL_COMPLETE_FLAG;
4769
4770 l_line_rec.sold_to_org_id := l_implicit_rec.SOLD_TO_ORG_ID;
4771 l_line_rec.sold_from_org_id := l_implicit_rec.SOLD_FROM_ORG_ID;
4772 l_line_rec.sort_order := l_implicit_rec.SORT_ORDER;
4773 l_line_rec.source_document_id := l_implicit_rec.SOURCE_DOCUMENT_ID;
4774 l_line_rec.source_document_line_id := l_implicit_rec.SOURCE_DOCUMENT_LINE_ID;
4775 l_line_rec.source_document_type_id := l_implicit_rec.SOURCE_DOCUMENT_TYPE_ID;
4776 l_line_rec.source_type_code := l_implicit_rec.SOURCE_TYPE_CODE;
4777 l_line_rec.split_from_line_id := l_implicit_rec.SPLIT_FROM_LINE_ID;
4778 l_line_rec.line_set_id := l_implicit_rec.LINE_SET_ID;
4779 l_line_rec.split_by := l_implicit_rec.SPLIT_BY;
4780 l_line_rec.model_remnant_flag := l_implicit_rec.MODEL_REMNANT_FLAG;
4781 l_line_rec.task_id := l_implicit_rec.TASK_ID;
4782 l_line_rec.tax_code := l_implicit_rec.TAX_CODE;
4783 l_line_rec.tax_date := l_implicit_rec.TAX_DATE;
4784 l_line_rec.tax_exempt_flag := l_implicit_rec.TAX_EXEMPT_FLAG;
4785 l_line_rec.tax_exempt_number := l_implicit_rec.TAX_EXEMPT_NUMBER;
4786 l_line_rec.tax_exempt_reason_code := l_implicit_rec.TAX_EXEMPT_REASON_CODE;
4787 l_line_rec.tax_point_code := l_implicit_rec.TAX_POINT_CODE;
4788 l_line_rec.tax_rate := l_implicit_rec.TAX_RATE;
4789 l_line_rec.tax_value := l_implicit_rec.TAX_VALUE;
4790 l_line_rec.top_model_line_id := l_implicit_rec.TOP_MODEL_LINE_ID;
4791 l_line_rec.unit_list_price := l_implicit_rec.UNIT_LIST_PRICE;
4792 l_line_rec.unit_selling_price := l_implicit_rec.UNIT_SELLING_PRICE;
4793 l_line_rec.visible_demand_flag := l_implicit_rec.VISIBLE_DEMAND_FLAG;
4794 l_line_rec.veh_cus_item_cum_key_id := l_implicit_rec.VEH_CUS_ITEM_CUM_KEY_ID;
4795 l_line_rec.shipping_instructions := l_implicit_rec.shipping_instructions;
4796 l_line_rec.packing_instructions := l_implicit_rec.packing_instructions;
4797 l_line_rec.service_txn_reason_code := l_implicit_rec.service_txn_reason_code;
4798 l_line_rec.service_txn_comments := l_implicit_rec.service_txn_comments;
4799 l_line_rec.service_duration := l_implicit_rec.service_duration;
4800 l_line_rec.service_period := l_implicit_rec.service_period;
4801 l_line_rec.service_start_date := l_implicit_rec.service_start_date;
4802 l_line_rec.service_end_date := l_implicit_rec.service_end_date;
4803 l_line_rec.service_coterminate_flag := l_implicit_rec.service_coterminate_flag;
4804 l_line_rec.unit_list_percent := l_implicit_rec.unit_list_percent;
4805 l_line_rec.unit_selling_percent := l_implicit_rec.unit_selling_percent;
4806 l_line_rec.unit_percent_base_price := l_implicit_rec.unit_percent_base_price;
4807 l_line_rec.service_number := l_implicit_rec.service_number;
4808 l_line_rec.service_reference_type_code := l_implicit_rec.service_reference_type_code;
4809 l_line_rec.service_reference_line_id:= l_implicit_rec.service_reference_line_id;
4810 l_line_rec.service_reference_system_id:= l_implicit_rec.service_reference_system_id;
4811
4812 l_line_rec.tp_context := l_implicit_rec.tp_context;
4813 l_line_rec.tp_attribute1 := l_implicit_rec.tp_attribute1;
4814 l_line_rec.tp_attribute2 := l_implicit_rec.tp_attribute2;
4815 l_line_rec.tp_attribute3 := l_implicit_rec.tp_attribute3;
4816 l_line_rec.tp_attribute4 := l_implicit_rec.tp_attribute4;
4817 l_line_rec.tp_attribute5 := l_implicit_rec.tp_attribute5;
4818 l_line_rec.tp_attribute6 := l_implicit_rec.tp_attribute6;
4819 l_line_rec.tp_attribute7 := l_implicit_rec.tp_attribute7;
4820 l_line_rec.tp_attribute8 := l_implicit_rec.tp_attribute8;
4821 l_line_rec.tp_attribute9 := l_implicit_rec.tp_attribute9;
4822 l_line_rec.tp_attribute10:= l_implicit_rec.tp_attribute10;
4823 l_line_rec.tp_attribute11:= l_implicit_rec.tp_attribute11;
4824 l_line_rec.tp_attribute12:= l_implicit_rec.tp_attribute12;
4825 l_line_rec.tp_attribute13:= l_implicit_rec.tp_attribute13;
4826 l_line_rec.tp_attribute14:= l_implicit_rec.tp_attribute14;
4827 l_line_rec.tp_attribute15:= l_implicit_rec.tp_attribute15;
4828 l_line_rec.flow_status_code := l_implicit_rec.flow_status_code;
4829 l_line_rec.marketing_source_code_id := l_implicit_rec.marketing_source_code_id;
4830 l_line_rec.calculate_price_flag := l_implicit_rec.calculate_price_flag;
4831 l_line_rec.commitment_id := l_implicit_rec.commitment_id;
4832 l_line_rec.order_source_id := l_implicit_rec.order_source_id;
4833 l_line_tbl(l_line_tbl.COUNT + 1) := l_line_rec;
4834
4835 END LOOP;
4836
4837
4838 RETURN l_line_tbl(1);
4839
4840 EXCEPTION
4841
4842 WHEN NO_DATA_FOUND THEN
4843
4844 RAISE NO_DATA_FOUND;
4845
4846 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4847
4848 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4849
4850 WHEN OTHERS THEN
4851
4852 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4853
4854 END Query_Row;
4855
4856 PROCEDURE Insert_Row
4857 ( p_line_rec IN OE_Order_PUB.Line_Rec_Type,
4858 p_orig_line_id IN Number,
4859 p_upgraded_flag IN Varchar2,
4860 p_apply_price_adj IN Varchar2 default 'Y'
4861 )
4862 IS
4863 --
4864 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
4865 --
4866 BEGIN
4867
4868 /* bring old_line_id from log table using the L_Line_id as the new_line_id */
4869
4870 G_OLD_LINE_ID := 0;
4871 begin
4872 select max(old_line_id) into G_OLD_LINE_ID from oe_upgrade_log_v
4873 where new_line_id = p_orig_line_id;
4874 exception
4875 when others then
4876 G_OLD_LINE_ID := 0;
4877 end;
4878
4879 INSERT INTO OE_ORDER_LINES_ALL
4880 ( ORG_ID
4881 , ACCOUNTING_RULE_ID
4882 , ACTUAL_ARRIVAL_DATE
4883 , ACTUAL_SHIPMENT_DATE
4884 , AGREEMENT_ID
4885 , ARRIVAL_SET_ID
4886 , ATO_LINE_ID
4887 , ATTRIBUTE1
4888 , ATTRIBUTE10
4889 , ATTRIBUTE11
4890 , ATTRIBUTE12
4891 , ATTRIBUTE13
4892 , ATTRIBUTE14
4893 , ATTRIBUTE15
4894 , ATTRIBUTE2
4895 , ATTRIBUTE3
4896 , ATTRIBUTE4
4897 , ATTRIBUTE5
4898 , ATTRIBUTE6
4899 , ATTRIBUTE7
4900 , ATTRIBUTE8
4901 , ATTRIBUTE9
4902 , AUTO_SELECTED_QUANTITY
4903 , AUTHORIZED_TO_SHIP_FLAG
4904 , BOOKED_FLAG
4905 , CANCELLED_FLAG
4906 , CANCELLED_QUANTITY
4907 , COMPONENT_CODE
4908 , COMPONENT_NUMBER
4909 , COMPONENT_SEQUENCE_ID
4910 , CONFIG_HEADER_ID
4911 , CONFIG_REV_NBR
4912 , CONFIG_DISPLAY_SEQUENCE
4913 , CONFIGURATION_ID
4914 , CONTEXT
4915 , CREATED_BY
4916 , CREATION_DATE
4917 , CREDIT_INVOICE_LINE_ID
4918 , CUSTOMER_LINE_NUMBER
4919 , CUSTOMER_SHIPMENT_NUMBER
4920 , CUSTOMER_ITEM_NET_PRICE
4921 , CUSTOMER_PAYMENT_TERM_ID
4922 , CUSTOMER_DOCK_CODE
4923 , CUSTOMER_JOB
4924 , CUSTOMER_PRODUCTION_LINE
4925 , CUST_PRODUCTION_SEQ_NUM
4926 , CUSTOMER_TRX_LINE_ID
4927 , CUST_MODEL_SERIAL_NUMBER
4928 , CUST_PO_NUMBER
4929 , DELIVERY_LEAD_TIME
4930 , DELIVER_TO_CONTACT_ID
4931 , DELIVER_TO_ORG_ID
4932 , DEMAND_BUCKET_TYPE_CODE
4933 , DEMAND_CLASS_CODE
4934 , DEP_PLAN_REQUIRED_FLAG
4935 --, DROP_SHIP_FLAG
4936 , EARLIEST_ACCEPTABLE_DATE
4937 , END_ITEM_UNIT_NUMBER
4938 , EXPLOSION_DATE
4939 , FIRST_ACK_CODE
4940 , FIRST_ACK_DATE
4941 , FOB_POINT_CODE
4942 , FREIGHT_CARRIER_CODE
4943 , FREIGHT_TERMS_CODE
4944 , FULFILLED_QUANTITY
4945 , FULFILLED_FLAG
4946 , FULFILLMENT_METHOD_CODE
4947 , FULFILLMENT_DATE
4948 , GLOBAL_ATTRIBUTE1
4949 , GLOBAL_ATTRIBUTE10
4950 , GLOBAL_ATTRIBUTE11
4951 , GLOBAL_ATTRIBUTE12
4952 , GLOBAL_ATTRIBUTE13
4953 , GLOBAL_ATTRIBUTE14
4954 , GLOBAL_ATTRIBUTE15
4955 , GLOBAL_ATTRIBUTE16
4956 , GLOBAL_ATTRIBUTE17
4957 , GLOBAL_ATTRIBUTE18
4958 , GLOBAL_ATTRIBUTE19
4959 , GLOBAL_ATTRIBUTE2
4960 , GLOBAL_ATTRIBUTE20
4961 , GLOBAL_ATTRIBUTE3
4962 , GLOBAL_ATTRIBUTE4
4963 , GLOBAL_ATTRIBUTE5
4964 , GLOBAL_ATTRIBUTE6
4965 , GLOBAL_ATTRIBUTE7
4966 , GLOBAL_ATTRIBUTE8
4967 , GLOBAL_ATTRIBUTE9
4968 , GLOBAL_ATTRIBUTE_CATEGORY
4969 , HEADER_ID
4970 , INDUSTRY_ATTRIBUTE1
4971 , INDUSTRY_ATTRIBUTE10
4972 , INDUSTRY_ATTRIBUTE11
4973 , INDUSTRY_ATTRIBUTE12
4974 , INDUSTRY_ATTRIBUTE13
4975 , INDUSTRY_ATTRIBUTE14
4976 , INDUSTRY_ATTRIBUTE15
4977 , INDUSTRY_ATTRIBUTE16
4978 , INDUSTRY_ATTRIBUTE17
4979 , INDUSTRY_ATTRIBUTE18
4980 , INDUSTRY_ATTRIBUTE19
4981 , INDUSTRY_ATTRIBUTE20
4982 , INDUSTRY_ATTRIBUTE21
4983 , INDUSTRY_ATTRIBUTE22
4984 , INDUSTRY_ATTRIBUTE23
4985 , INDUSTRY_ATTRIBUTE24
4986 , INDUSTRY_ATTRIBUTE25
4987 , INDUSTRY_ATTRIBUTE26
4988 , INDUSTRY_ATTRIBUTE27
4989 , INDUSTRY_ATTRIBUTE28
4990 , INDUSTRY_ATTRIBUTE29
4991 , INDUSTRY_ATTRIBUTE30
4992 , INDUSTRY_ATTRIBUTE2
4993 , INDUSTRY_ATTRIBUTE3
4994 , INDUSTRY_ATTRIBUTE4
4995 , INDUSTRY_ATTRIBUTE5
4996 , INDUSTRY_ATTRIBUTE6
4997 , INDUSTRY_ATTRIBUTE7
4998 , INDUSTRY_ATTRIBUTE8
4999 , INDUSTRY_ATTRIBUTE9
5003 , INVENTORY_ITEM_ID
5000 , INDUSTRY_CONTEXT
5001 , INTMED_SHIP_TO_CONTACT_ID
5002 , INTMED_SHIP_TO_ORG_ID
5004 , INVOICE_INTERFACE_STATUS_CODE
5005 , INVOICE_TO_CONTACT_ID
5006 , INVOICE_TO_ORG_ID
5007 , INVOICED_QUANTITY
5008 , INVOICING_RULE_ID
5009 , ORDERED_ITEM_ID
5010 , ITEM_IDENTIFIER_TYPE
5011 , ORDERED_ITEM
5012 , ITEM_REVISION
5013 , ITEM_TYPE_CODE
5014 , LAST_ACK_CODE
5015 , LAST_ACK_DATE
5016 , LAST_UPDATED_BY
5017 , LAST_UPDATE_DATE
5018 , LAST_UPDATE_LOGIN
5019 , LATEST_ACCEPTABLE_DATE
5020 , LINE_CATEGORY_CODE
5021 , LINE_ID
5022 , LINE_NUMBER
5023 , LINE_TYPE_ID
5024 , LINK_TO_LINE_ID
5025 , MODEL_GROUP_NUMBER
5026 -- , MFG_COMPONENT_SEQUENCE_ID
5027 , OPEN_FLAG
5028 , OPTION_FLAG
5029 , OPTION_NUMBER
5030 , ORDERED_QUANTITY
5031 , ORDER_QUANTITY_UOM
5032 --, ORG_ID
5033 , ORDER_SOURCE_ID
5034 , ORIG_SYS_DOCUMENT_REF
5035 , ORIG_SYS_LINE_REF
5036 , ORIG_SYS_SHIPMENT_REF
5037 , CHANGE_SEQUENCE
5038 , OVER_SHIP_REASON_CODE
5039 , OVER_SHIP_RESOLVED_FLAG
5040 , PAYMENT_TERM_ID
5041 , PLANNING_PRIORITY
5042 , PRICE_LIST_ID
5043 , PRICING_ATTRIBUTE1
5044 , PRICING_ATTRIBUTE10
5045 , PRICING_ATTRIBUTE2
5046 , PRICING_ATTRIBUTE3
5047 , PRICING_ATTRIBUTE4
5048 , PRICING_ATTRIBUTE5
5049 , PRICING_ATTRIBUTE6
5050 , PRICING_ATTRIBUTE7
5051 , PRICING_ATTRIBUTE8
5052 , PRICING_ATTRIBUTE9
5053 , PRICING_CONTEXT
5054 , PRICING_DATE
5055 , PRICING_QUANTITY
5056 , PRICING_QUANTITY_UOM
5057 , PROGRAM_APPLICATION_ID
5058 , PROGRAM_ID
5059 , PROGRAM_UPDATE_DATE
5060 , PROJECT_ID
5061 , PROMISE_DATE
5062 , RE_SOURCE_FLAG
5063 , REFERENCE_CUSTOMER_TRX_LINE_ID
5064 , REFERENCE_HEADER_ID
5065 , REFERENCE_LINE_ID
5066 , REFERENCE_TYPE
5067 , REQUEST_DATE
5068 , REQUEST_ID
5069 , RETURN_ATTRIBUTE1
5070 , RETURN_ATTRIBUTE10
5071 , RETURN_ATTRIBUTE11
5072 , RETURN_ATTRIBUTE12
5073 , RETURN_ATTRIBUTE13
5074 , RETURN_ATTRIBUTE14
5075 , RETURN_ATTRIBUTE15
5076 , RETURN_ATTRIBUTE2
5077 , RETURN_ATTRIBUTE3
5078 , RETURN_ATTRIBUTE4
5079 , RETURN_ATTRIBUTE5
5080 , RETURN_ATTRIBUTE6
5081 , RETURN_ATTRIBUTE7
5082 , RETURN_ATTRIBUTE8
5083 , RETURN_ATTRIBUTE9
5084 , RETURN_CONTEXT
5085 , RETURN_REASON_CODE
5086 , RLA_SCHEDULE_TYPE_CODE
5087 , SALESREP_ID
5088 , SCHEDULE_ARRIVAL_DATE
5089 , SCHEDULE_SHIP_DATE
5090 , SCHEDULE_STATUS_CODE
5091 , SHIPMENT_NUMBER
5092 , SHIPMENT_PRIORITY_CODE
5093 , SHIPPED_QUANTITY
5094 , SHIPPING_METHOD_CODE
5095 , SHIPPING_QUANTITY
5096 , SHIPPING_QUANTITY_UOM
5097 , SHIP_FROM_ORG_ID
5098 , SHIP_SET_ID
5099 , SHIP_TOLERANCE_ABOVE
5100 , SHIP_TOLERANCE_BELOW
5101 , SHIPPABLE_FLAG
5102 , SHIPPING_INTERFACED_FLAG
5103 , SHIP_TO_CONTACT_ID
5104 , SHIP_TO_ORG_ID
5105 , SHIP_MODEL_COMPLETE_FLAG
5106 , SOLD_TO_ORG_ID
5107 , SOLD_FROM_ORG_ID
5108 , SORT_ORDER
5109 , SOURCE_DOCUMENT_ID
5110 , SOURCE_DOCUMENT_LINE_ID
5111 , SOURCE_DOCUMENT_TYPE_ID
5112 , SOURCE_TYPE_CODE
5113 , SPLIT_FROM_LINE_ID
5114 , LINE_SET_ID
5115 , SPLIT_BY
5116 , model_remnant_flag
5117 , TASK_ID
5118 , TAX_CODE
5119 , TAX_DATE
5120 , TAX_EXEMPT_FLAG
5121 , TAX_EXEMPT_NUMBER
5122 , TAX_EXEMPT_REASON_CODE
5123 , TAX_POINT_CODE
5124 , TAX_RATE
5125 , TAX_VALUE
5126 , TOP_MODEL_LINE_ID
5127 , UNIT_LIST_PRICE
5128 , UNIT_SELLING_PRICE
5129 , VISIBLE_DEMAND_FLAG
5130 , VEH_CUS_ITEM_CUM_KEY_ID
5131 , SHIPPING_INSTRUCTIONS
5132 , PACKING_INSTRUCTIONS
5133 , SERVICE_TXN_REASON_CODE
5134 , SERVICE_TXN_COMMENTS
5135 , SERVICE_DURATION
5136 , SERVICE_PERIOD
5137 , SERVICE_START_DATE
5138 , SERVICE_END_DATE
5139 , SERVICE_COTERMINATE_FLAG
5140 , UNIT_LIST_PERCENT
5141 , UNIT_SELLING_PERCENT
5142 , UNIT_PERCENT_BASE_PRICE
5143 , SERVICE_NUMBER
5144 , SERVICE_REFERENCE_TYPE_CODE
5145 , SERVICE_REFERENCE_LINE_ID
5146 , SERVICE_REFERENCE_SYSTEM_ID
5147 , TP_CONTEXT
5148 , TP_ATTRIBUTE1
5149 , TP_ATTRIBUTE2
5150 , TP_ATTRIBUTE3
5151 , TP_ATTRIBUTE4
5152 , TP_ATTRIBUTE5
5153 , TP_ATTRIBUTE6
5154 , TP_ATTRIBUTE7
5155 , TP_ATTRIBUTE8
5156 , TP_ATTRIBUTE9
5157 , TP_ATTRIBUTE10
5158 , TP_ATTRIBUTE11
5159 , TP_ATTRIBUTE12
5160 , TP_ATTRIBUTE13
5161 , TP_ATTRIBUTE14
5162 , TP_ATTRIBUTE15
5166 , COMMITMENT_ID
5163 , FLOW_STATUS_CODE
5164 , MARKETING_SOURCE_CODE_ID
5165 , CALCULATE_PRICE_FLAG
5167 , UPGRADED_FLAG
5168 )
5169 VALUES
5170 ( p_line_rec.org_id
5171 , p_line_rec.accounting_rule_id
5172 , p_line_rec.actual_arrival_date
5173 , p_line_rec.actual_shipment_date
5174 , p_line_rec.agreement_id
5175 , p_line_rec.arrival_set_id
5176 , p_line_rec.ato_line_id
5177 , p_line_rec.attribute1
5178 , p_line_rec.attribute10
5179 , p_line_rec.attribute11
5180 , p_line_rec.attribute12
5181 , p_line_rec.attribute13
5182 , p_line_rec.attribute14
5183 , p_line_rec.attribute15
5184 , p_line_rec.attribute2
5185 , p_line_rec.attribute3
5186 , p_line_rec.attribute4
5187 , p_line_rec.attribute5
5188 , p_line_rec.attribute6
5189 , p_line_rec.attribute7
5190 , p_line_rec.attribute8
5191 , p_line_rec.attribute9
5192 , p_line_rec.auto_selected_quantity
5193 , p_line_rec.authorized_to_ship_flag
5194 , p_line_rec.booked_flag
5195 , p_line_rec.cancelled_flag
5196 , p_line_rec.cancelled_quantity
5197 , p_line_rec.component_code
5198 , p_line_rec.component_number
5199 , p_line_rec.component_sequence_id
5200 , p_line_rec.config_header_id
5201 , p_line_rec.config_rev_nbr
5202 , p_line_rec.config_display_sequence
5203 , p_line_rec.configuration_id
5204 , p_line_rec.context
5205 , p_line_rec.created_by
5206 , p_line_rec.creation_date
5207 , p_line_rec.credit_invoice_line_id
5208 , p_line_rec.customer_line_number
5209 , p_line_rec.customer_shipment_number
5210 , p_line_rec.customer_item_net_price
5211 , p_line_rec.customer_payment_term_id
5212 , p_line_rec.customer_dock_code
5213 , p_line_rec.customer_job
5214 , p_line_rec.customer_production_line
5215 , p_line_rec.cust_production_seq_num
5216 , p_line_rec.customer_trx_line_id
5217 , p_line_rec.cust_model_serial_number
5218 , p_line_rec.cust_po_number
5219 , p_line_rec.delivery_lead_time
5220 , p_line_rec.deliver_to_contact_id
5221 , p_line_rec.deliver_to_org_id
5222 , p_line_rec.demand_bucket_type_code
5223 , p_line_rec.demand_class_code
5224 , p_line_rec.dep_plan_required_flag
5225 --, p_line_rec.drop_ship_flag
5226 , p_line_rec.earliest_acceptable_date
5227 , p_line_rec.end_item_unit_number
5228 , p_line_rec.explosion_date
5229 , p_line_rec.first_ack_code
5230 , p_line_rec.first_ack_date
5231 , p_line_rec.fob_point_code
5232 , p_line_rec.freight_carrier_code
5233 , p_line_rec.freight_terms_code
5234 , p_line_rec.fulfilled_quantity
5235 , p_line_rec.fulfilled_flag
5236 , p_line_rec.fulfillment_method_code
5237 , p_line_rec.fulfillment_date
5238 , p_line_rec.global_attribute1
5239 , p_line_rec.global_attribute10
5240 , p_line_rec.global_attribute11
5241 , p_line_rec.global_attribute12
5242 , p_line_rec.global_attribute13
5243 , p_line_rec.global_attribute14
5244 , p_line_rec.global_attribute15
5245 , p_line_rec.global_attribute16
5246 , p_line_rec.global_attribute17
5247 , p_line_rec.global_attribute18
5248 , p_line_rec.global_attribute19
5249 , p_line_rec.global_attribute2
5250 , p_line_rec.global_attribute20
5251 , p_line_rec.global_attribute3
5252 , p_line_rec.global_attribute4
5253 , p_line_rec.global_attribute5
5254 , p_line_rec.global_attribute6
5255 , p_line_rec.global_attribute7
5256 , p_line_rec.global_attribute8
5257 , p_line_rec.global_attribute9
5258 , p_line_rec.global_attribute_category
5259 , p_line_rec.header_id
5260 , p_line_rec.industry_attribute1
5261 , p_line_rec.industry_attribute10
5262 , p_line_rec.industry_attribute11
5263 , p_line_rec.industry_attribute12
5264 , p_line_rec.industry_attribute13
5265 , p_line_rec.industry_attribute14
5266 , p_line_rec.industry_attribute15
5267 , p_line_rec.industry_attribute16
5268 , p_line_rec.industry_attribute17
5269 , p_line_rec.industry_attribute18
5270 , p_line_rec.industry_attribute19
5271 , p_line_rec.industry_attribute20
5272 , p_line_rec.industry_attribute21
5273 , p_line_rec.industry_attribute22
5274 , p_line_rec.industry_attribute23
5275 , p_line_rec.industry_attribute24
5276 , p_line_rec.industry_attribute25
5277 , p_line_rec.industry_attribute26
5278 , p_line_rec.industry_attribute27
5279 , p_line_rec.industry_attribute28
5280 , p_line_rec.industry_attribute29
5281 , p_line_rec.industry_attribute30
5282 , p_line_rec.industry_attribute2
5283 , p_line_rec.industry_attribute3
5284 , p_line_rec.industry_attribute4
5285 , p_line_rec.industry_attribute5
5286 , p_line_rec.industry_attribute6
5287 , p_line_rec.industry_attribute7
5288 , p_line_rec.industry_attribute8
5289 , p_line_rec.industry_attribute9
5290 , p_line_rec.industry_context
5291 , p_line_rec.intermed_ship_to_contact_id
5292 , p_line_rec.intermed_ship_to_org_id
5296 , p_line_rec.invoice_to_org_id
5293 , p_line_rec.inventory_item_id
5294 , p_line_rec.invoice_interface_status_code
5295 , p_line_rec.invoice_to_contact_id
5297 , p_line_rec.invoiced_quantity
5298 , p_line_rec.invoicing_rule_id
5299 , p_line_rec.ordered_item_id
5300 , p_line_rec.item_identifier_type
5301 , p_line_rec.ordered_item
5302 , p_line_rec.item_revision
5303 , p_line_rec.item_type_code
5304 , p_line_rec.last_ack_code
5305 , p_line_rec.last_ack_date
5306 , p_line_rec.last_updated_by
5307 , p_line_rec.last_update_date
5308 , p_line_rec.last_update_login
5309 , p_line_rec.latest_acceptable_date
5310 , p_line_rec.line_category_code
5311 , p_line_rec.line_id
5312 , p_line_rec.line_number
5313 , p_line_rec.line_type_id
5314 , p_line_rec.link_to_line_id
5315 , p_line_rec.model_group_number
5316 --, p_line_rec.mfg_component_sequence_id
5317 , p_line_rec.open_flag
5318 , p_line_rec.option_flag
5319 , p_line_rec.option_number
5320 , p_line_rec.ordered_quantity
5321 , p_line_rec.order_quantity_uom
5322 --, l_org_id
5323 , p_line_rec.order_source_id
5324 , p_line_rec.orig_sys_document_ref
5325 , p_line_rec.orig_sys_line_ref
5326 , p_line_rec.orig_sys_shipment_ref
5327 , p_line_rec.change_sequence
5328 , p_line_rec.over_ship_reason_code
5329 , p_line_rec.over_ship_resolved_flag
5330 , p_line_rec.payment_term_id
5331 , p_line_rec.planning_priority
5332 , p_line_rec.price_list_id
5333 , p_line_rec.pricing_attribute1
5334 , p_line_rec.pricing_attribute10
5335 , p_line_rec.pricing_attribute2
5336 , p_line_rec.pricing_attribute3
5337 , p_line_rec.pricing_attribute4
5338 , p_line_rec.pricing_attribute5
5339 , p_line_rec.pricing_attribute6
5340 , p_line_rec.pricing_attribute7
5341 , p_line_rec.pricing_attribute8
5342 , p_line_rec.pricing_attribute9
5343 , p_line_rec.pricing_context
5344 , p_line_rec.pricing_date
5345 , p_line_rec.pricing_quantity
5346 , p_line_rec.pricing_quantity_uom
5347 , p_line_rec.program_application_id
5348 , p_line_rec.program_id
5349 , p_line_rec.program_update_date
5350 , p_line_rec.project_id
5351 , p_line_rec.promise_date
5352 , p_line_rec.re_source_flag
5353 , p_line_rec.reference_customer_trx_line_id
5354 , p_line_rec.reference_header_id
5355 , p_line_rec.reference_line_id
5356 , p_line_rec.reference_type
5357 , p_line_rec.request_date
5358 , p_line_rec.request_id
5359 , p_line_rec.return_attribute1
5360 , p_line_rec.return_attribute10
5361 , p_line_rec.return_attribute11
5362 , p_line_rec.return_attribute12
5363 , p_line_rec.return_attribute13
5364 , p_line_rec.return_attribute14
5365 , p_line_rec.return_attribute15
5366 , p_line_rec.return_attribute2
5367 , p_line_rec.return_attribute3
5368 , p_line_rec.return_attribute4
5369 , p_line_rec.return_attribute5
5370 , p_line_rec.return_attribute6
5371 , p_line_rec.return_attribute7
5372 , p_line_rec.return_attribute8
5373 , p_line_rec.return_attribute9
5374 , p_line_rec.return_context
5375 , p_line_rec.return_reason_code
5376 , p_line_rec.rla_schedule_type_code
5377 , p_line_rec.salesrep_id
5378 , p_line_rec.schedule_arrival_date
5379 , p_line_rec.schedule_ship_date
5380 , p_line_rec.schedule_status_code
5381 , p_line_rec.shipment_number
5382 , p_line_rec.shipment_priority_code
5383 , p_line_rec.shipped_quantity
5384 , p_line_rec.shipping_method_code
5385 , p_line_rec.shipping_quantity
5386 , p_line_rec.shipping_quantity_uom
5387 , p_line_rec.ship_from_org_id
5388 , p_line_rec.ship_set_id
5389 , p_line_rec.ship_tolerance_above
5390 , p_line_rec.ship_tolerance_below
5391 , p_line_rec.shippable_flag
5392 , p_line_rec.shipping_interfaced_flag
5393 , p_line_rec.ship_to_contact_id
5394 , p_line_rec.ship_to_org_id
5395 , p_line_rec.ship_model_complete_flag
5396
5397 , p_line_rec.sold_to_org_id
5398 , p_line_rec.sold_from_org_id
5399 , p_line_rec.sort_order
5400 , p_line_rec.source_document_id
5401 , p_line_rec.source_document_line_id
5402 , p_line_rec.source_document_type_id
5403 , p_line_rec.source_type_code
5404 , p_line_rec.split_from_line_id
5405 , p_line_rec.line_set_id
5406 , p_line_rec.split_by
5407 , p_line_rec.model_remnant_flag
5408 , p_line_rec.task_id
5409 , p_line_rec.tax_code
5410 , p_line_rec.tax_date
5411 , p_line_rec.tax_exempt_flag
5412 , p_line_rec.tax_exempt_number
5413 , p_line_rec.tax_exempt_reason_code
5414 , p_line_rec.tax_point_code
5415 , p_line_rec.tax_rate
5416 , p_line_rec.tax_value
5417 , p_line_rec.top_model_line_id
5418 , p_line_rec.unit_list_price
5419 , p_line_rec.unit_selling_price
5420 , p_line_rec.visible_demand_flag
5421 , p_line_rec.veh_cus_item_cum_key_id
5422 , p_line_rec.shipping_instructions
5426 , p_line_rec.service_duration
5423 , p_line_rec.packing_instructions
5424 , p_line_rec.service_txn_reason_code
5425 , p_line_rec.service_txn_comments
5427 , p_line_rec.service_period
5428 , p_line_rec.service_start_date
5429 , p_line_rec.service_end_date
5430 , p_line_rec.service_coterminate_flag
5431 , p_line_rec.unit_list_percent
5432 , p_line_rec.unit_selling_percent
5433 , p_line_rec.unit_percent_base_price
5434 , p_line_rec.service_number
5435 , p_line_rec.service_reference_type_code
5436 , p_line_rec.service_reference_line_id
5437 , p_line_rec.service_reference_system_id
5438 , p_line_rec.tp_context
5439 , p_line_rec.tp_attribute1
5440 , p_line_rec.tp_attribute2
5441 , p_line_rec.tp_attribute3
5442 , p_line_rec.tp_attribute4
5443 , p_line_rec.tp_attribute5
5444 , p_line_rec.tp_attribute6
5445 , p_line_rec.tp_attribute7
5446 , p_line_rec.tp_attribute8
5447 , p_line_rec.tp_attribute9
5448 , p_line_rec.tp_attribute10
5449 , p_line_rec.tp_attribute11
5450 , p_line_rec.tp_attribute12
5451 , p_line_rec.tp_attribute13
5452 , p_line_rec.tp_attribute14
5453 , p_line_rec.tp_attribute15
5454 , p_line_rec.flow_status_code
5455 , p_line_rec.marketing_source_code_id
5456 , p_line_rec.calculate_price_flag
5457 , p_line_rec.commitment_id
5458 , p_upgraded_flag
5459 );
5460
5461 insert into oe_order_lines_history
5462 (
5463 line_id,
5464 org_id,
5465 header_id,
5466 line_type_id,
5467 line_number,
5468 ordered_item,
5469 request_date,
5470 promise_date,
5471 schedule_ship_date,
5472 order_quantity_uom,
5473 pricing_quantity,
5474 pricing_quantity_uom,
5475 cancelled_quantity,
5476 shipped_quantity,
5477 ordered_quantity,
5478 fulfilled_quantity,
5479 shipping_quantity,
5480 shipping_quantity_uom,
5481 delivery_lead_time,
5482 tax_exempt_flag,
5483 tax_exempt_number,
5484 tax_exempt_reason_code,
5485 ship_from_org_id,
5486 ship_to_org_id,
5487 invoice_to_org_id,
5488 deliver_to_org_id,
5489 ship_to_contact_id,
5490 deliver_to_contact_id,
5491 invoice_to_contact_id,
5492 sold_to_org_id,
5493 cust_po_number,
5494 ship_tolerance_above,
5495 ship_tolerance_below,
5496 demand_bucket_type_code,
5497 veh_cus_item_cum_key_id,
5498 rla_schedule_type_code,
5499 customer_dock_code,
5500 customer_job,
5501 customer_production_line,
5502 cust_model_serial_number,
5503 project_id,
5504 task_id,
5505 inventory_item_id,
5506 tax_date,
5507 tax_code,
5508 tax_rate,
5509 demand_class_code,
5510 price_list_id,
5511 pricing_date,
5512 shipment_number,
5513 agreement_id,
5514 shipment_priority_code,
5515 shipping_method_code,
5516 freight_carrier_code,
5517 freight_terms_code,
5518 fob_point_code,
5519 tax_point_code,
5520 payment_term_id,
5521 invoicing_rule_id,
5522 accounting_rule_id,
5523 source_document_type_id,
5524 orig_sys_document_ref,
5525 source_document_id,
5526 orig_sys_line_ref,
5527 source_document_line_id,
5528 reference_line_id,
5529 reference_type,
5530 reference_header_id,
5531 item_revision,
5532 unit_selling_price,
5533 unit_list_price,
5534 tax_value,
5535 context,
5536 attribute1,
5537 attribute2,
5538 attribute3,
5539 attribute4,
5540 attribute5,
5541 attribute6,
5542 attribute7,
5543 attribute8,
5544 attribute9,
5545 attribute10,
5546 attribute11,
5547 attribute12,
5548 attribute13,
5549 attribute14,
5550 attribute15,
5551 global_attribute_category,
5552 global_attribute1,
5553 global_attribute2,
5554 global_attribute3,
5555 global_attribute4,
5556 global_attribute5,
5557 global_attribute6,
5558 global_attribute7,
5559 global_attribute8,
5560 global_attribute9,
5561 global_attribute10,
5562 global_attribute11,
5563 global_attribute12,
5564 global_attribute13,
5565 global_attribute14,
5566 global_attribute15,
5567 global_attribute16,
5568 global_attribute17,
5569 global_attribute18,
5570 global_attribute19,
5571 global_attribute20,
5572 pricing_context,
5573 pricing_attribute1,
5577 pricing_attribute5,
5574 pricing_attribute2,
5575 pricing_attribute3,
5576 pricing_attribute4,
5578 pricing_attribute6,
5579 pricing_attribute7,
5580 pricing_attribute8,
5581 pricing_attribute9,
5582 pricing_attribute10,
5583 industry_context,
5584 industry_attribute1,
5585 industry_attribute2,
5586 industry_attribute3,
5587 industry_attribute4,
5588 industry_attribute5,
5589 industry_attribute6,
5590 industry_attribute7,
5591 industry_attribute8,
5592 industry_attribute9,
5593 industry_attribute10,
5594 industry_attribute11,
5595 industry_attribute12,
5596 industry_attribute13,
5597 industry_attribute14,
5598 industry_attribute15,
5599 industry_attribute16,
5600 industry_attribute17,
5601 industry_attribute18,
5602 industry_attribute19,
5603 industry_attribute20,
5604 industry_attribute21,
5605 industry_attribute22,
5606 industry_attribute23,
5607 industry_attribute24,
5608 industry_attribute25,
5609 industry_attribute26,
5610 industry_attribute27,
5611 industry_attribute28,
5612 industry_attribute29,
5613 industry_attribute30,
5614 creation_date,
5615 created_by,
5616 last_update_date,
5617 last_updated_by,
5618 last_update_login,
5619 program_application_id,
5620 program_id,
5621 program_update_date,
5622 request_id,
5623 configuration_id,
5624 link_to_line_id,
5625 component_sequence_id,
5626 component_code,
5627 config_display_sequence,
5628 sort_order,
5629 item_type_code,
5630 option_number,
5631 option_flag,
5632 dep_plan_required_flag,
5633 visible_demand_flag,
5634 line_category_code,
5635 actual_shipment_date,
5636 customer_trx_line_id,
5637 return_context,
5638 return_attribute1,
5639 return_attribute2,
5640 return_attribute3,
5641 return_attribute4,
5642 return_attribute5,
5643 return_attribute6,
5644 return_attribute7,
5645 return_attribute8,
5646 return_attribute9,
5647 return_attribute10,
5648 return_attribute11,
5649 return_attribute12,
5650 return_attribute13,
5651 return_attribute14,
5652 return_attribute15,
5653 intmed_ship_to_org_id,
5654 intmed_ship_to_contact_id,
5655 actual_arrival_date,
5656 ato_line_id,
5657 auto_selected_quantity,
5658 component_number,
5659 earliest_acceptable_date,
5660 explosion_date,
5661 latest_acceptable_date,
5662 model_group_number,
5663 schedule_arrival_date,
5664 ship_model_complete_flag,
5665 schedule_status_code,
5666 return_reason_code,
5667 salesrep_id,
5668 split_from_line_id,
5669 cust_production_seq_num,
5670 authorized_to_ship_flag,
5671 invoice_interface_status_code,
5672 ship_set_id,
5673 arrival_set_id,
5674 hist_comments,
5675 hist_type_code,
5676 reason_code,
5677 hist_created_by,
5678 hist_creation_date,
5679 source_type_code,
5680 booked_flag,
5681 fulfilled_flag,
5682 sold_from_org_id,
5683 top_model_line_id,
5684 cancelled_flag,
5685 open_flag,
5686 over_ship_reason_code,
5687 over_ship_resolved_flag,
5688 item_identifier_type,
5689 commitment_id,
5690 shipping_interfaced_flag,
5691 credit_invoice_line_id,
5692 end_item_unit_number,
5693 mfg_component_sequence_id,
5694 config_header_id,
5695 config_rev_nbr,
5696 shipping_instructions,
5697 packing_instructions,
5698 invoiced_quantity,
5699 reference_customer_trx_line_id,
5700 split_by,
5701 line_set_id,
5702 tp_context,
5703 tp_attribute1,
5704 tp_attribute2,
5705 tp_attribute3,
5706 tp_attribute4,
5707 tp_attribute5,
5708 tp_attribute6,
5709 tp_attribute7,
5710 tp_attribute8,
5711 tp_attribute9,
5712 tp_attribute10,
5713 tp_attribute11,
5714 tp_attribute12,
5715 tp_attribute13,
5716 tp_attribute14,
5717 tp_attribute15,
5718 fulfillment_method_code,
5719 service_reference_type_code,
5720 service_reference_line_id,
5721 service_reference_system_id,
5722 ordered_item_id,
5723 service_number,
5724 service_duration,
5725 service_start_date,
5726 re_source_flag,
5727 flow_status_code,
5728 service_end_date,
5729 service_coterminate_flag,
5730 shippable_flag,
5731 order_source_id,
5735 customer_line_number,
5732 orig_sys_shipment_ref,
5733 change_sequence,
5734 drop_ship_flag,
5736 customer_shipment_number,
5737 customer_item_net_price,
5738 customer_payment_term_id,
5739 first_ack_date,
5740 first_ack_code,
5741 last_ack_code,
5742 last_ack_date,
5743 planning_priority,
5744 service_txn_comments,
5745 service_period,
5746 unit_selling_percent,
5747 unit_list_percent,
5748 unit_percent_base_price,
5749 model_remnant_flag,
5750 service_txn_reason_code,
5751 calculate_price_flag,
5752 revenue_amount
5753 )
5754 select
5755 p_line_rec.line_id,
5756 org_id,
5757 header_id,
5758 line_type_id,
5759 line_number,
5760 ordered_item,
5761 request_date,
5762 promise_date,
5763 schedule_ship_date,
5764 order_quantity_uom,
5765 pricing_quantity,
5766 pricing_quantity_uom,
5767 cancelled_quantity,
5768 shipped_quantity,
5769 ordered_quantity,
5770 fulfilled_quantity,
5771 shipping_quantity,
5772 shipping_quantity_uom,
5773 delivery_lead_time,
5774 tax_exempt_flag,
5775 tax_exempt_number,
5776 tax_exempt_reason_code,
5777 ship_from_org_id,
5778 ship_to_org_id,
5779 invoice_to_org_id,
5780 deliver_to_org_id,
5781 ship_to_contact_id,
5782 deliver_to_contact_id,
5783 invoice_to_contact_id,
5784 sold_to_org_id,
5785 cust_po_number,
5786 ship_tolerance_above,
5787 ship_tolerance_below,
5788 demand_bucket_type_code,
5789 veh_cus_item_cum_key_id,
5790 rla_schedule_type_code,
5791 customer_dock_code,
5792 customer_job,
5793 customer_production_line,
5794 cust_model_serial_number,
5795 project_id,
5796 task_id,
5797 inventory_item_id,
5798 tax_date,
5799 tax_code,
5800 tax_rate,
5801 demand_class_code,
5802 price_list_id,
5803 pricing_date,
5804 shipment_number,
5805 agreement_id,
5806 shipment_priority_code,
5807 shipping_method_code,
5808 freight_carrier_code,
5809 freight_terms_code,
5810 fob_point_code,
5811 tax_point_code,
5812 payment_term_id,
5813 invoicing_rule_id,
5814 accounting_rule_id,
5815 source_document_type_id,
5816 orig_sys_document_ref,
5817 source_document_id,
5818 orig_sys_line_ref,
5819 source_document_line_id,
5820 reference_line_id,
5821 reference_type,
5822 reference_header_id,
5823 item_revision,
5824 unit_selling_price,
5825 unit_list_price,
5826 tax_value,
5827 context,
5828 attribute1,
5829 attribute2,
5830 attribute3,
5831 attribute4,
5832 attribute5,
5833 attribute6,
5834 attribute7,
5835 attribute8,
5836 attribute9,
5837 attribute10,
5838 attribute11,
5839 attribute12,
5840 attribute13,
5841 attribute14,
5842 attribute15,
5843 global_attribute_category,
5844 global_attribute1,
5845 global_attribute2,
5846 global_attribute3,
5847 global_attribute4,
5848 global_attribute5,
5849 global_attribute6,
5850 global_attribute7,
5851 global_attribute8,
5852 global_attribute9,
5853 global_attribute10,
5854 global_attribute11,
5855 global_attribute12,
5856 global_attribute13,
5857 global_attribute14,
5858 global_attribute15,
5859 global_attribute16,
5860 global_attribute17,
5861 global_attribute18,
5862 global_attribute19,
5863 global_attribute20,
5864 pricing_context,
5865 pricing_attribute1,
5866 pricing_attribute2,
5867 pricing_attribute3,
5868 pricing_attribute4,
5869 pricing_attribute5,
5870 pricing_attribute6,
5871 pricing_attribute7,
5872 pricing_attribute8,
5873 pricing_attribute9,
5874 pricing_attribute10,
5875 industry_context,
5876 industry_attribute1,
5877 industry_attribute2,
5878 industry_attribute3,
5879 industry_attribute4,
5880 industry_attribute5,
5881 industry_attribute6,
5882 industry_attribute7,
5883 industry_attribute8,
5884 industry_attribute9,
5885 industry_attribute10,
5886 industry_attribute11,
5887 industry_attribute12,
5888 industry_attribute13,
5889 industry_attribute14,
5890 industry_attribute15,
5891 industry_attribute16,
5892 industry_attribute17,
5893 industry_attribute18,
5894 industry_attribute19,
5898 industry_attribute23,
5895 industry_attribute20,
5896 industry_attribute21,
5897 industry_attribute22,
5899 industry_attribute24,
5900 industry_attribute25,
5901 industry_attribute26,
5902 industry_attribute27,
5903 industry_attribute28,
5904 industry_attribute29,
5905 industry_attribute30,
5906 creation_date,
5907 created_by,
5908 last_update_date,
5909 last_updated_by,
5910 last_update_login,
5911 program_application_id,
5912 program_id,
5913 program_update_date,
5914 request_id,
5915 configuration_id,
5916 link_to_line_id,
5917 component_sequence_id,
5918 component_code,
5919 config_display_sequence,
5920 sort_order,
5921 item_type_code,
5922 option_number,
5923 option_flag,
5924 dep_plan_required_flag,
5925 visible_demand_flag,
5926 line_category_code,
5927 actual_shipment_date,
5928 customer_trx_line_id,
5929 return_context,
5930 return_attribute1,
5931 return_attribute2,
5932 return_attribute3,
5933 return_attribute4,
5934 return_attribute5,
5935 return_attribute6,
5936 return_attribute7,
5937 return_attribute8,
5938 return_attribute9,
5939 return_attribute10,
5940 return_attribute11,
5941 return_attribute12,
5942 return_attribute13,
5943 return_attribute14,
5944 return_attribute15,
5945 intmed_ship_to_org_id,
5946 intmed_ship_to_contact_id,
5947 actual_arrival_date,
5948 ato_line_id,
5949 auto_selected_quantity,
5950 component_number,
5951 earliest_acceptable_date,
5952 explosion_date,
5953 latest_acceptable_date,
5954 model_group_number,
5955 schedule_arrival_date,
5956 ship_model_complete_flag,
5957 schedule_status_code,
5958 return_reason_code,
5959 salesrep_id,
5960 split_from_line_id,
5961 cust_production_seq_num,
5962 authorized_to_ship_flag,
5963 invoice_interface_status_code,
5964 ship_set_id,
5965 arrival_set_id,
5966 hist_comments,
5967 hist_type_code,
5968 reason_code,
5969 hist_created_by,
5970 hist_creation_date,
5971 source_type_code,
5972 booked_flag,
5973 fulfilled_flag,
5974 sold_from_org_id,
5975 top_model_line_id,
5976 cancelled_flag,
5977 open_flag,
5978 over_ship_reason_code,
5979 over_ship_resolved_flag,
5980 item_identifier_type,
5981 commitment_id,
5982 shipping_interfaced_flag,
5983 credit_invoice_line_id,
5984 end_item_unit_number,
5985 mfg_component_sequence_id,
5986 config_header_id,
5987 config_rev_nbr,
5988 shipping_instructions,
5989 packing_instructions,
5990 invoiced_quantity,
5991 reference_customer_trx_line_id,
5992 split_by,
5993 line_set_id,
5994 tp_context,
5995 tp_attribute1,
5996 tp_attribute2,
5997 tp_attribute3,
5998 tp_attribute4,
5999 tp_attribute5,
6000 tp_attribute6,
6001 tp_attribute7,
6002 tp_attribute8,
6003 tp_attribute9,
6004 tp_attribute10,
6005 tp_attribute11,
6006 tp_attribute12,
6007 tp_attribute13,
6008 tp_attribute14,
6009 tp_attribute15,
6010 fulfillment_method_code,
6011 service_reference_type_code,
6012 service_reference_line_id,
6013 service_reference_system_id,
6014 ordered_item_id,
6015 service_number,
6016 service_duration,
6017 service_start_date,
6018 re_source_flag,
6019 flow_status_code,
6020 service_end_date,
6021 service_coterminate_flag,
6022 shippable_flag,
6023 order_source_id,
6024 orig_sys_shipment_ref,
6025 change_sequence,
6026 drop_ship_flag,
6027 customer_line_number,
6028 customer_shipment_number,
6029 customer_item_net_price,
6030 customer_payment_term_id,
6031 first_ack_date,
6032 first_ack_code,
6033 last_ack_code,
6034 last_ack_date,
6035 planning_priority,
6036 service_txn_comments,
6037 service_period,
6038 unit_selling_percent,
6039 unit_list_percent,
6040 unit_percent_base_price,
6041 model_remnant_flag,
6042 service_txn_reason_code,
6043 calculate_price_flag,
6044 revenue_amount
6045 from oe_order_lines_history
6046 where line_id = p_orig_line_id;
6047
6048 /* Insert log record here */
6049 G_Log_Rec.Header_Id := p_line_rec.header_id;
6050 G_Log_Rec.Old_Line_Id := g_Old_line_id;
6051 G_Log_Rec.picking_Line_Id := null;
6052 G_Log_Rec.Old_Line_Detail_id := null;
6053 G_Log_Rec.Delivery := null;
6054 G_Log_Rec.New_Line_ID := p_line_rec.line_id;
6055 g_log_rec.return_qty_available := null;
6056 G_Log_Rec.New_Line_Number := null;
6060
6057 G_Log_Rec.mtl_sales_order_id := null; /* to check with Rupal if this is okay */
6058 g_log_rec.comments := 'Created through sub-program for Upgrade';
6059 OE_UPG_SO.Upgrade_Insert_Upgrade_Log;
6061 g_line_id := p_line_rec.line_id;
6062 g_header_id := p_line_rec.header_id;
6063
6064 /* ========== Price Adjustments =========== */
6065 IF p_apply_price_adj = 'Y' THEN
6066 OE_Upg_SO.Upgrade_Price_Adjustments
6067 ( L_level_flag => 'L');
6068 END IF;
6069
6070 /* ========== Sales Credits =========== */
6071 OE_Upg_SO.Upgrade_Sales_Credits
6072 ( L_level_flag => 'L');
6073
6074 IF l_debug_level > 0 THEN
6075 oe_debug_pub.add( 'EXITING INSERT_ROW' , 1 ) ;
6076 END IF;
6077
6078 EXCEPTION
6079 WHEN OTHERS THEN
6080
6081 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6082
6083 END Insert_Row;
6084
6085 Procedure Insert_Return_Included_Items(p_line_id NUMBER,module varchar2 default null)
6086 IS
6087 l_inventory_item_id NUMBER;
6088 l_line_set_id NUMBER;
6089 l_line_number NUMBER;
6090 l_shipment_line_id NUMBER;
6091 l_line_id NUMBER;
6092 l_line_id1 NUMBER;
6093 l_received_quantity NUMBER;
6094 l_line_rec OE_Order_PUB.Line_Rec_Type;
6095 l_line_rec1 OE_Order_PUB.Line_Rec_Type;
6096 Cursor C1 is
6097 Select rma_interface_id,rma_id,rma_line_id,
6098 inventory_item_id,component_sequence_id,
6099 quantity, unit_code,received_quantity,delivered_quantity
6100 from mtl_so_rma_interface
6101 where rma_line_id = p_line_id
6102 and inventory_item_id <> l_inventory_item_id;
6103 --
6104 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
6105 --
6106 BEGIN
6107
6108 l_line_rec := Query_Row(p_line_id);
6109 l_inventory_item_id := l_line_rec.inventory_item_id;
6110
6111 For l_rec in C1 LOOP
6112
6113 select oe_order_lines_s.nextval into l_line_id from dual;
6114
6115 IF module is null then
6116 g_Last_Line_Number := g_Last_Line_Number +1;
6117 l_line_number := g_Last_Line_Number;
6118 else
6119 begin
6120 Select max(line_number) into l_line_number
6121 from oe_order_lines_all
6122 where header_id = l_line_rec.header_id;
6123
6124 l_line_number := l_line_number + 1;
6125 exception
6126 when others then
6127 l_line_number := 1;
6128 end;
6129 end if;
6130
6131 l_received_quantity := l_rec.received_quantity;
6132
6133 IF l_received_quantity = 0 THEN
6134 l_received_quantity := NULL;
6135 END IF;
6136
6137
6138 l_line_rec1 := l_line_rec;
6139 l_line_rec1.line_number := l_line_number;
6140 l_line_rec1.inventory_item_id := l_rec.inventory_item_id;
6141 l_line_rec1.ordered_item_id := l_rec.inventory_item_id;
6142 l_line_rec1.ordered_item := NULL;
6143 l_line_rec1.order_quantity_uom := l_rec.unit_code;
6144 l_line_rec1.shipping_quantity_uom := l_rec.unit_code;
6145 l_line_rec1.pricing_quantity_uom := l_rec.unit_code;
6146 l_line_rec1.unit_list_price := 0;
6147 l_line_rec1.unit_selling_price := 0;
6148
6149
6150 IF l_received_quantity is NULL or
6151 (l_rec.quantity <= l_received_quantity) THEN
6152 l_line_rec1.line_id := l_line_id;
6153 l_line_rec1.shipment_number := 1;
6154 l_line_rec1.ordered_quantity := l_rec.quantity;
6155 l_line_rec1.pricing_quantity := l_rec.quantity;
6156 l_line_rec1.shipped_quantity := l_received_quantity;
6157 l_line_rec1.fulfilled_quantity := l_received_quantity;
6158 l_line_rec1.invoiced_quantity := NULL;
6159
6160 Insert_Row(l_line_rec1,p_line_id,'Y','N');
6161
6162 ELSE
6163
6164 select oe_sets_s.nextval into l_line_set_id from dual;
6165
6166 insert into oe_sets
6167 ( SET_ID, SET_NAME, SET_TYPE, HEADER_ID, SHIP_FROM_ORG_ID,
6168 SHIP_TO_ORG_ID,SCHEDULE_SHIP_DATE, SCHEDULE_ARRIVAL_DATE,
6169 FREIGHT_CARRIER_CODE, SHIPPING_METHOD_CODE,
6170 SHIPMENT_PRIORITY_CODE, SET_STATUS,
6171 CREATED_BY, CREATION_DATE, UPDATED_BY, UPDATE_DATE,
6172 UPDATE_LOGIN, INVENTORY_ITEM_ID,ORDERED_QUANTITY_UOM,
6173 LINE_TYPE_ID,SHIP_TOLERANCE_ABOVE, SHIP_TOLERANCE_BELOW)
6174 values
6175 (l_line_set_id, to_char(l_line_set_id),
6176 'LINE_SET',l_line_rec.header_id,null,null, null,null,null,
6177 null,null,null, 0,sysdate,0, sysdate,
6178 0,null,null,null,null,null
6179 );
6180
6181
6182 l_line_rec1.line_id := l_line_id;
6183 l_line_rec1.shipment_number := 1;
6184 l_line_rec1.ordered_quantity := l_rec.received_quantity;
6185 l_line_rec1.pricing_quantity := l_line_rec1.ordered_quantity;
6186 l_line_rec1.shipped_quantity := l_rec.received_quantity;
6187 l_line_rec1.fulfilled_quantity := l_rec.received_quantity;
6188 l_line_rec1.invoiced_quantity := NULL;
6189
6190 l_line_rec1.line_set_id := l_line_set_id;
6191
6192 Insert_Row(l_line_rec1,p_line_id,'Y','N');
6193
6194 select oe_order_lines_s.nextval into l_line_id1 from dual;
6195
6196 l_line_rec1.line_id := l_line_id1;
6197 l_line_rec1.shipment_number := 2;
6198 l_line_rec1.ordered_quantity := l_rec.quantity-l_rec.received_quantity;
6199 l_line_rec1.pricing_quantity := l_line_rec1.ordered_quantity;
6200 l_line_rec1.shipped_quantity := NULL;
6201 l_line_rec1.fulfilled_quantity := NULL;
6202 l_line_rec1.invoiced_quantity := NULL;
6203
6204 Insert_Row(l_line_rec1,p_line_id,'Y','N');
6205
6206 END IF;
6207
6208 begin
6209 -- Update PO Tables
6210 -- rcv_shipment_lines, rcv_transactions,rcv_supply
6211
6215 where oe_order_line_id = p_line_id
6212 select shipment_line_id
6213 into l_shipment_line_id
6214 from rcv_shipment_lines
6216 and item_id = l_rec.inventory_item_id;
6217
6218 update rcv_shipment_lines
6219 set oe_order_line_id = l_line_id
6220 where shipment_line_id = l_shipment_line_id;
6221
6222 update rcv_transactions
6223 set oe_order_line_id = l_line_id
6224 where shipment_line_id = l_shipment_line_id;
6225
6226 update rcv_supply
6227 set oe_order_line_id = l_line_id
6228 where shipment_line_id = l_shipment_line_id;
6229
6230 exception
6231 when others then
6232 null;
6233 end;
6234
6235 END LOOP; -- End loop for C1
6236
6237 END Insert_Return_Included_Items;
6238
6239 Procedure Process_Upgraded_Returns is
6240 l_commit_counter number := 0;
6241 l_line_id number;
6242 l_header_id number;
6243 v_error_code number;
6244 Cursor C1 is
6245 Select l1.line_id,l1.header_id
6246 from oe_order_lines_all l1, oe_order_lines_all l2
6247 where l1.reference_line_id = l2.line_id
6248 and l2.item_type_code in ('MODEL','CLASS','KIT')
6249 and l2.ato_line_id is null
6250 and l1.line_category_code = 'RETURN'
6251 and l1.reference_type is not null
6252 and l1.upgraded_flag in ('Y','P')
6253 and nvl(l1.open_flag,'-') = 'Y'
6254 and l1.line_id not in (
6255 Select u.new_line_id
6256 from oe_upgrade_log u
6257 where u.module = 'RI') ;
6258 begin
6259
6260 For l_rec in C1 LOOP
6261 l_commit_counter := l_commit_counter +1;
6262 l_header_id := l_rec.header_id;
6263 l_line_id := l_rec.line_id;
6264
6265 BEGIN
6266
6267 SAVEPOINT R_SAVE_POINT;
6268
6269 Insert_Return_Included_Items(l_line_id,'Process_Returns');
6270
6271 -- Insert the header_id,line_id entry into the upgrade Log
6272 insert into oe_upgrade_log(header_id,new_line_id,creation_date, module)
6273 values (l_header_id,l_line_id,sysdate, 'RI');
6274
6275 exception
6276 when others then
6277 ROLLBACK TO R_SAVE_POINT;
6278 v_error_code := sqlcode;
6279 oe_upg_so.upgrade_insert_errors
6280 ( L_header_id => l_header_id,
6281 L_comments => 'Exception in Process_Upgraded_Returns: '
6282 ||'Error code -'
6283 ||to_char(v_error_code)
6284 ||' - Line id '||to_char(l_line_id)
6285 );
6286 COMMIT;
6287 l_commit_counter := 0;
6288 raise;
6289
6290 END;
6291
6292 IF l_commit_counter = 500 THEN
6293 Commit;
6294 l_commit_counter := 0;
6295 END IF;
6296
6297 End LOOP;
6298
6299 commit;
6300
6301 -- Code to split ATO Items
6302 Fix_Returns_Splits;
6303
6304 end Process_Upgraded_Returns;
6305
6306 --
6307 -- Procedure to split ATO Items that are
6308 -- partially received. Called in Process_Upgraded_Returns.
6309 --
6310 Procedure Fix_Returns_Splits is
6311 l_commit_counter number := 0;
6312 v_error_code number;
6313 l_header_id number;
6314 l_line_id number;
6315 l_line_id1 number;
6316 l_line_set_id number;
6317 v_received_quantity number;
6318 -- Cursor to select ATO Items
6319 Cursor C1 is
6320 Select l1.line_id,l1.header_id,'RA' item_type,m.received_quantity
6321 from oe_order_lines_all l1, oe_order_lines_all l2,mtl_so_rma_interface m
6322 where l1.reference_line_id = l2.line_id
6323 and l1.line_category_code = 'RETURN'
6324 and decode(m.received_quantity,0,NULL,m.received_quantity) <
6325 l1.ordered_quantity
6326 and l1.line_id = m.rma_line_id
6327 and l2.item_type_code = 'OPTION'
6328 and l2.ato_line_id = l2.line_id
6329 and l1.upgraded_flag in ('Y','P')
6330 and l1.line_id not in(
6331 Select new_line_id
6332 from oe_upgrade_log u
6333 where u.module = 'RA');
6334 l_line_rec OE_Order_PUB.Line_Rec_Type;
6335 l_line_rec2 OE_Order_PUB.Line_Rec_Type;
6336 BEGIN
6337
6338 FOR l_rec in C1 LOOP
6339 l_commit_counter := l_commit_counter + 1;
6340
6341 begin
6342
6343 SAVEPOINT SAVE_POINT1;
6344
6345 l_line_id := l_rec.line_id;
6346 l_header_id := l_rec.header_id;
6347 v_received_quantity := l_rec.received_quantity;
6348
6349 l_line_rec := Query_Row(l_line_id);
6350
6351 select oe_sets_s.nextval into l_line_set_id from dual;
6352
6353 insert into oe_sets
6354 ( SET_ID, SET_NAME, SET_TYPE, HEADER_ID, SHIP_FROM_ORG_ID,
6355 SHIP_TO_ORG_ID,SCHEDULE_SHIP_DATE, SCHEDULE_ARRIVAL_DATE,
6356 FREIGHT_CARRIER_CODE, SHIPPING_METHOD_CODE,
6357 SHIPMENT_PRIORITY_CODE, SET_STATUS,
6358 CREATED_BY, CREATION_DATE, UPDATED_BY, UPDATE_DATE,
6359 UPDATE_LOGIN, INVENTORY_ITEM_ID,ORDERED_QUANTITY_UOM,
6360 LINE_TYPE_ID,SHIP_TOLERANCE_ABOVE, SHIP_TOLERANCE_BELOW)
6361 values
6362 (l_line_set_id, to_char(l_line_set_id),
6363 'LINE_SET',l_line_rec.header_id,null,null, null,null,null,
6364 null,null,null, 0,sysdate,0, sysdate,
6365 0,null,null,null,null,null
6366 );
6367
6368
6369 Update oe_order_lines_all
6370 set shipped_quantity = v_received_quantity,
6371 fulfilled_quantity = v_received_quantity,
6372 ordered_quantity = v_received_quantity ,
6373 line_set_id = l_line_set_id
6374 where line_id = l_line_id;
6375
6376
6377 select oe_order_lines_s.nextval
6378 into l_line_id1
6379 from dual;
6380
6381 l_line_rec2 := l_line_rec;
6382 l_line_rec2.line_id := l_line_id1;
6383 l_line_rec2.shipment_number := 2;
6384 l_line_rec2.ordered_quantity := l_line_rec.ordered_quantity - v_received_quantity;
6385 l_line_rec2.shipped_quantity := null;
6386 l_line_rec2.fulfilled_quantity := null;
6387 l_line_rec2.invoiced_quantity := null;
6388 l_line_rec2.line_set_id := l_line_set_id;
6389
6390 Insert_Row(l_line_rec2,l_line_id,'Y','N');
6391
6392
6393 -- Insert the header_id,line_id entry into the upgrade Log
6394 insert into oe_upgrade_log(header_id,new_line_id,creation_date, module)
6395 values (l_header_id,l_line_id,sysdate, l_rec.item_type);
6396
6397 exception
6398 when others then
6399 ROLLBACK TO SAVE_POINT1;
6400 v_error_code := sqlcode;
6401 oe_upg_so.upgrade_insert_errors
6402 ( L_header_id => l_header_id,
6403 L_comments => 'Exception in Fix_Returns_Splits: '
6404 ||'Error code -'
6405 ||to_char(v_error_code)
6406 ||' - Line id '||to_char(l_line_id)
6407 );
6408 COMMIT;
6409 l_commit_counter := 0;
6410 raise;
6411 end;
6412
6413
6414 if l_commit_counter = 500 then
6415 commit;
6416 l_commit_counter := 0;
6417 end if;
6418
6419 END LOOP;
6420
6421 Commit;
6422
6423 END Fix_Returns_Splits;
6424
6425 End OE_Upg_SO;