[Home] [Help]
PACKAGE BODY: APPS.FLM_COPY_ROUTING
Source
1 PACKAGE BODY flm_copy_routing AS
2 /* $Header: FLMCPYRB.pls 120.1 2006/02/14 13:54:17 ksuleman noship $ */
3
4 G_LOG_ON boolean := ('Y' = FND_PROFILE.VALUE('MRP_DEBUG'));
5
6
7 TYPE two_id_rec IS RECORD (old_id number,new_id number);
8
9 TYPE two_id_list is TABLE OF two_id_rec INDEX BY BINARY_INTEGER;
10
11 Function get_org_code(p_org_id number) Return Varchar2 Is
12 l_org_code varchar2(3) := NULL;
13 Begin
14
15 select organization_code into l_org_code
16 from mtl_parameters
17 where organization_id = p_org_id;
18 return l_org_code;
19 Exception
20 when others then
21 return l_org_code;
22
23 End get_org_code;
24
25 Function get_line_code(p_org_id number,p_line_id number) Return Varchar2 Is
26 l_line_code varchar2(10) := NULL;
27 Begin
28
29 select line_code into l_line_code
30 from wip_lines
31 where organization_id = p_org_id
32 and line_id = p_line_id;
33 return l_line_code;
34 Exception
35 when others then
36 return l_line_code;
37
38 End get_line_code;
39
40 Function std_op_exists( p_org_id number,
41 p_line_id number,
42 p_std_op_code varchar2,
43 p_op_type number) Return Boolean Is
44 l_total number := 0;
45 Begin
46 select count(*) into l_total
47 from bom_standard_operations
48 where organization_id = p_org_id
49 and line_id = p_line_id
50 and operation_code = p_std_op_code
51 and operation_type = p_op_type;
52
53 if l_total > 0 then
54 return TRUE;
55 end if;
56
57 Return FALSE;
58
59 Exception
60 when OTHERS then
61 return FALSE;
62
63 End std_op_exists;
64
65 Function bill_exists( p_item_id number,
66 p_org_id number,
67 p_alternate varchar2) Return Boolean Is
68 l_total number := 0;
69 Begin
70 select count(*) into l_total
71 from bom_bill_of_materials
72 where assembly_item_id = p_item_id
73 and organization_id = p_org_id
74 and nvl(alternate_bom_designator,'NONE') = nvl(p_alternate,'NONE');
75
76 if l_total > 0 then
77 return TRUE;
78 end if;
79
80 Return FALSE;
81
82 Exception
83 when OTHERS then
84 return FALSE;
85
86 End bill_exists;
87
88 Procedure copy_attach(obj_from varchar2,obj_to varchar2,id_list two_id_list) Is
89 i number;
90 last number;
91 Begin
92 if (id_list.COUNT > 0) then
93 i := id_list.FIRST;
94 last := id_list.LAST;
95 LOOP
96 fnd_attached_documents2_pkg.copy_attachments(
97 X_from_entity_name => obj_from,
98 X_from_pk1_value => id_list(i).old_id,
99 X_from_pk2_value => '',
100 X_from_pk3_value => '',
101 X_from_pk4_value => '',
102 X_from_pk5_value => '',
103 X_to_entity_name => obj_to,
104 X_to_pk1_value => id_list(i).new_id,
105 X_to_pk2_value => '',
106 X_to_pk3_value => '',
107 X_to_pk4_value => '',
108 X_to_pk5_value => '',
109 X_created_by => fnd_global.user_id,
110 X_last_update_login => '',
111 X_program_application_id=> '',
112 X_program_id => '',
113 X_request_id => ''
114 );
115
116 exit when i = last;
117 i := id_list.NEXT(i);
118 END LOOP;
119 end if;
120 End;
121
122
123 Procedure copy_routings(
124 errbuf OUT NOCOPY varchar2
125 ,retcode OUT NOCOPY number
126 ,p_mode number
127 ,p_organization_id number
128 ,p_line_id_to number
129 ,p_alternate_code_to varchar2
130 ,p_copy_bom varchar2
131 ,p_line_id_from number
132 ,p_alternate_code_from varchar2
133 ,p_product_family_id number
134 ,p_assembly_name_from varchar2
135 ,p_assembly_name_to varchar2
136 ,p_tpct_from number
137 ,p_tpct_to number
138 ,p_lineop_code varchar2
139 ,p_process_code varchar2
140 ) IS
141
142
143 i number;
144 j number;
145 ii number;
146 jj number;
147 in_list boolean;
148 lineop_in_list boolean;
149 process_in_list boolean;
150 dup number;
151 last number;
152 success number;
153 new_row_id varchar2(18);
154 new_std_op_id number;
155 l_rtg_total number;
156
157 -- routing header key
158 l_org_code varchar2(3) := get_org_code(p_organization_id);
159 l_line_code varchar2(10) := get_line_code(p_organization_id,p_line_id_to);
160 l_assembly_item_name varchar2(81);
161 l_today date := sysdate;
162
163 l_rtg_header_rec Bom_Rtg_Pub.Rtg_Header_Rec_Type;
164 l_rtg_revision_tbl Bom_Rtg_Pub.Rtg_Revision_Tbl_Type;
165 l_operation_tbl Bom_Rtg_Pub.Operation_Tbl_Type;
166 l_op_resource_tbl Bom_Rtg_Pub.Op_Resource_Tbl_Type;
167 l_sub_resource_tbl Bom_Rtg_Pub.Sub_Resource_Tbl_Type;
168 l_op_network_tbl Bom_Rtg_Pub.Op_Network_Tbl_Type;
169
170 o_rtg_header_rec Bom_Rtg_Pub.Rtg_Header_Rec_Type;
171 o_rtg_revision_tbl Bom_Rtg_Pub.Rtg_Revision_Tbl_Type;
172 o_operation_tbl Bom_Rtg_Pub.Operation_Tbl_Type;
173 o_op_resource_tbl Bom_Rtg_Pub.Op_Resource_Tbl_Type;
174 o_sub_resource_tbl Bom_Rtg_Pub.Sub_Resource_Tbl_Type;
175 o_op_network_tbl Bom_Rtg_Pub.Op_Network_Tbl_Type;
176
177 t_rtg_revision_tbl Bom_Rtg_Pub.Rtg_Revision_Tbl_Type;
178 t_operation_tbl Bom_Rtg_Pub.Operation_Tbl_Type;
179 t_op_resource_tbl Bom_Rtg_Pub.Op_Resource_Tbl_Type;
180 t_sub_resource_tbl Bom_Rtg_Pub.Sub_Resource_Tbl_Type;
181 t_op_network_tbl Bom_Rtg_Pub.Op_Network_Tbl_Type;
182
183 a_operation_tbl Bom_Rtg_Pub.Operation_Tbl_Type;
184
185 o_return_status Varchar2(8);
186 o_msg_count Number;
187 l_msg varchar2(1000);
188 l_index number;
189 l_id varchar2(30);
190 l_type varchar2(10);
191
192 l_std_op_exist boolean;
193
194 l_new_rtg_seq_id number;
195 l_old_op_seq_id number;
196 l_new_op_seq_id number;
197
198 l_2_ids two_id_list;
199 l_2_seq_ids two_id_list;
200 t_2_ids two_id_list;
201
202 l_from_sequence_id number;
203 l_to_sequence_id number;
204 l_to_common_seq_id number;
205
206 -- CURSORS
207 Cursor c_routings IS
208 /*
209 Select
210 bor.routing_sequence_id,
211 bor.common_routing_sequence_id,
212 bor.assembly_item_id,
213 bor.common_assembly_item_id,
214 bor.alternate_routing_designator
215 From
216 bom_operational_routings bor, mtl_system_items_kfv msi_kfv
217 Where
218 bor.organization_id = p_organization_id
219 and bor.organization_id = msi_kfv.organization_id
220 and bor.assembly_item_id = msi_kfv.inventory_item_id
221 and bor.line_id = p_line_id_from
222 and bor.cfm_routing_flag = 1
223 and bor.routing_type = 1
224 and ((p_mode = 1 and msi_kfv.bom_item_type <> 5) or (p_mode = 2 and msi_kfv.bom_item_type = 5))
225 and ((p_alternate_code_from is NULL and bor.alternate_routing_designator is NULL)
226 or p_alternate_code_from = bor.alternate_routing_designator)
227 and (p_product_family_id is NULL or p_product_family_id = msi_kfv.product_family_item_id)
228 and (p_assembly_name_from is NULL or p_assembly_name_from <= msi_kfv.concatenated_segments)
229 and (p_assembly_name_to is NULL or p_assembly_name_to >= msi_kfv.concatenated_segments)
230 and (p_tpct_from is NULL or bor.total_product_cycle_time >= p_tpct_from)
231 and (p_tpct_to is NULL or bor.total_product_cycle_time <= p_tpct_to)
232 and (p_lineop_code is NULL or exists (
233 select 1 from bom_operation_sequences_v bosv
234 where bosv.routing_sequence_id = bor.common_routing_sequence_id
235 and p_lineop_code = bosv.standard_operation_code
236 and bosv.operation_type = 3)
237 )
238 and (p_process_code is NULL or exists (
239 select 1 from bom_operation_sequences_v bosv
240 where bosv.routing_sequence_id = bor.common_routing_sequence_id
241 and p_process_code = bosv.standard_operation_code
242 and bosv.operation_type = 2)
243 )
244 Order by
245 bor.routing_sequence_id
246 ;
247 */
248 Select
249 bor.routing_sequence_id,
250 bor.common_routing_sequence_id,
251 bor.assembly_item_id,
252 bor.common_assembly_item_id,
253 bor.alternate_routing_designator
254 From
255 bom_operational_routings bor, mtl_system_items_kfv msi_kfv
256 Where
257 bor.organization_id = p_organization_id
258 and bor.organization_id = msi_kfv.organization_id
259 and bor.assembly_item_id = msi_kfv.inventory_item_id
260 and bor.line_id = p_line_id_from
261 and bor.cfm_routing_flag = 1
262 and bor.routing_type = 1
263 and ((p_mode = 1 and msi_kfv.bom_item_type <> 5) or (p_mode = 2 and msi_kfv.bom_item_type = 5))
264 and ((p_alternate_code_from is NULL and bor.alternate_routing_designator is NULL)
265 or p_alternate_code_from = bor.alternate_routing_designator)
266 and (p_product_family_id is NULL or p_product_family_id = msi_kfv.product_family_item_id)
267 and (p_assembly_name_from is NULL or p_assembly_name_from <= msi_kfv.concatenated_segments)
268 and (p_assembly_name_to is NULL or p_assembly_name_to >= msi_kfv.concatenated_segments)
269 and (p_tpct_from is NULL or bor.total_product_cycle_time >= p_tpct_from)
270 and (p_tpct_to is NULL or bor.total_product_cycle_time <= p_tpct_to)
271 and (p_lineop_code is NULL or exists (
272 select 1
273 from bom_operation_sequences bos1, bom_standard_operations bso1
274 where bos1.routing_sequence_id = bor.common_routing_sequence_id
275 and p_lineop_code = bso1.operation_code
276 and bos1.standard_operation_id = bso1.standard_operation_id
277 and bso1.organization_id = p_organization_id
278 and bso1.line_id = p_line_id_from
279 and bos1.operation_type = 3)
280 )
281 and (p_process_code is NULL or exists (
282 select 1
283 from bom_operation_sequences bos1, bom_standard_operations bso1
284 where bos1.routing_sequence_id = bor.common_routing_sequence_id
285 and p_process_code = bso1.operation_code
286 and bos1.standard_operation_id = bso1.standard_operation_id
287 and bso1.organization_id = p_organization_id
288 and bso1.line_id = p_line_id_from
289 and bos1.operation_type = 2)
290 )
291 Order by
292 bor.routing_sequence_id
293 ;
294
295 Cursor c_bill_sequence(p_item_id number,p_org_id number,p_alternate varchar2) IS
296 Select
297 *
298 From
299 bom_bill_of_materials
300 Where
301 organization_id = p_org_id
302 and assembly_item_id = p_item_id
303 and (nvl(alternate_bom_designator,'NONE') = nvl(p_alternate,'NONE'))
304 ;
305
306 Cursor c_routing_header(p_routing_sequence_id number) Is
307 Select
308 flm_util.get_key_flex_item(assembly_item_id,p_organization_id) Assembly_Item_Name
309 ,l_org_code Organization_Code
310 ,p_alternate_code_to Alternate_Routing_Code
311 ,2 Eng_Routing_Flag --eng_routing_flag is oAlternate_Routing_Codepposite of routing_type
312 ,flm_util.get_key_flex_item(common_assembly_item_id,p_organization_id) Common_Assembly_Item_Name
313 ,routing_comment Routing_Comment
314 ,completion_subinventory Completion_Subinventory
315 ,flm_util.get_key_flex_location(completion_locator_id,p_organization_id) Completion_Location_Name
316 ,l_line_code Line_Code
317 ,cfm_routing_flag CFM_Routing_Flag
318 ,2 Mixed_Model_Map_Flag -- mixed_model_map_flag has only one 'Y'=1 for an item
319 ,priority Priority
320 ,total_product_cycle_time Total_Cycle_Time
321 ,2 CTP_Flag --ctp_flag: only one YES=1 for an item
322 ,attribute_category Attribute_category
323 ,attribute1 Attribute1
324 ,attribute2 Attribute2
325 ,attribute3 Attribute3
326 ,attribute4 Attribute4
327 ,attribute5 Attribute5
328 ,attribute6 Attribute6
329 ,attribute7 Attribute7
330 ,attribute8 Attribute8
331 ,attribute9 Attribute9
332 ,attribute10 Attribute10
333 ,attribute11 Attribute11
334 ,attribute12 Attribute12
335 ,attribute13 Attribute13
336 ,attribute14 Attribute14
337 ,attribute15 Attribute15
338 ,original_system_reference Original_System_Reference
339 ,'CREATE' Transaction_Type
340 ,NULL Return_Status
341 ,NULL Delete_Group_Name
342 ,NULL DG_Description
343 From
344 bom_operational_routings_v
345 Where
346 routing_sequence_id = p_routing_sequence_id
347 ;
348 l_routing_header_rec c_routing_header%ROWTYPE;
349
350
351 Cursor c_routing_revision(p_assembly_item_id number) Is
352 Select
353 l_assembly_item_name
354 ,l_org_code -- organization_code
355 ,p_alternate_code_to
356 ,process_revision revision
357 ,effectivity_date start_effective_date
358 ,attribute_category
359 ,attribute1
360 ,attribute2
361 ,attribute3
362 ,attribute4
363 ,attribute5
364 ,attribute6
365 ,attribute7
366 ,attribute8
367 ,attribute9
368 ,attribute10
369 ,attribute11
370 ,attribute12
371 ,attribute13
372 ,attribute14
373 ,attribute15
374 ,NULL
375 ,'CREATE' -- transaction_type
376 ,NULL -- return status
377 From
378 mtl_rtg_item_revisions mrir
379 Where
380 inventory_item_id = p_assembly_item_id
381 and organization_id = p_organization_id
382 ;
383
384
385 Cursor c_operations(p_routing_sequence_id number) Is
386 Select
387 l_assembly_item_name Assembly_Item_Name
388 ,l_org_code Organization_Code
389 ,p_alternate_code_to Alternate_Routing_Code
390 ,bosv.operation_seq_num Operation_Sequence_Number
391 ,bosv.operation_type Operation_Type
392 ,bosv.effectivity_date Start_Effective_Date
393 ,bosv.operation_seq_num New_Operation_Sequence_Number
394 ,bosv.effectivity_date New_Start_Effective_Date
395 ,bosv.standard_operation_code Standard_Operation_Code
396 ,bosv.department_code Department_Code
397 ,bosv.operation_lead_time_percent Op_Lead_Time_Percent
398 ,bosv.minimum_transfer_quantity Minimum_Transfer_Quantity
399 ,bosv.count_point_type Count_Point_Type
400 ,bosv.operation_description Operation_Description
401 ,bosv.disable_date Disable_Date
402 ,bosv.backflush_flag Backflush_Flag
403 ,NULL Option_Dependent_Flag
404 ,1 Reference_Flag -- copied always referenced
405 ,bosv.process_seq_num Process_Seq_Number
406 ,bosv.process_code Process_Code
407 ,bosv.line_op_seq_num Line_Op_Seq_Number
408 ,bosv.line_op_code Line_Op_Code
409 ,bosv.yield Yield
410 ,bosv.cumulative_yield Cumulative_Yield
411 ,bosv.reverse_cumulative_yield Reverse_CUM_Yield
412 ,bosv.labor_time_user User_Labor_Time
413 ,bosv.machine_time_user User_Machine_Time
414 ,100 Net_Planning_Percent --??????
415 ,bosv.include_in_rollup Include_In_Rollup
416 ,bosv.operation_yield_enabled Op_Yield_Enabled_Flag
417 ,bosv.shutdown_type Shutdown_Type
418 ,bosv.attribute_category Attribute_category
419 ,bosv.attribute1 Attribute1
420 ,bosv.attribute2 Attribute2
421 ,bosv.attribute3 Attribute3
422 ,bosv.attribute4 Attribute4
423 ,bosv.attribute5 Attribute5
424 ,bosv.attribute6 Attribute6
425 ,bosv.attribute7 Attribute7
426 ,bosv.attribute8 Attribute8
427 ,bosv.attribute9 Attribute9
428 ,bosv.attribute10 Attribute10
429 ,bosv.attribute11 Attribute11
430 ,bosv.attribute12 Attribute12
431 ,bosv.attribute13 Attribute13
432 ,bosv.attribute14 Attribute14
433 ,bosv.attribute15 Attribute15
434 ,bosv.original_system_reference Original_System_Reference
435 ,'CREATE' Transaction_Type
436 ,NULL Return_Status
437 ,NULL Delete_Group_Name
438 ,NULL DG_Description
439 From
440 bom_operation_sequences_v bosv
441 Where
442 bosv.routing_sequence_id = p_routing_sequence_id
443 and ((bosv.effectivity_date <= l_today and nvl(bosv.disable_date,l_today+1) > l_today)
444 or (bosv.effectivity_date > l_today and nvl(bosv.disable_date, bosv.effectivity_date+1) > bosv.effectivity_date))
445 Order by
446 bosv.operation_type desc
447 ;
448 l_operations_rec c_operations%ROWTYPE;
449
450
451 Cursor c_networks(p_routing_sequence_id number) Is
452 Select
453 l_assembly_item_name Assembly_Item_Name
454 ,l_org_code Organization_Code
455 ,p_alternate_code_to Alternate_Routing_Code
456 ,bonv.operation_type Operation_Type
457 ,bonv.from_seq_num From_Op_Seq_Number
458 ,bos1.x_coordinate From_X_Coordinate
459 ,bos1.y_coordinate From_Y_Coordinate
460 ,bonv.from_effectivity_date From_Start_Effective_Date
461 ,bonv.to_seq_num To_Op_Seq_Number
462 ,bos2.x_coordinate To_X_Coordinate
463 ,bos2.y_coordinate To_Y_Coordinate
464 ,bonv.to_effectivity_date To_Start_Effective_Date
465 ,bonv.from_seq_num New_From_Op_Seq_Number
466 ,bonv.from_effectivity_date New_From_Start_Effective_Date
467 ,bonv.to_seq_num New_To_Op_Seq_Number
468 ,bonv.to_effectivity_date New_To_Start_Effective_Date
469 ,bonv.transition_type Connection_Type
470 ,bonv.planning_pct Planning_Percent
471 ,bonv.attribute_category Attribute_category
472 ,bonv.attribute1 Attribute1
473 ,bonv.attribute2 Attribute2
474 ,bonv.attribute3 Attribute3
475 ,bonv.attribute4 Attribute4
476 ,bonv.attribute5 Attribute5
477 ,bonv.attribute6 Attribute6
478 ,bonv.attribute7 Attribute7
479 ,bonv.attribute8 Attribute8
480 ,bonv.attribute9 Attribute9
481 ,bonv.attribute10 Attribute10
482 ,bonv.attribute11 Attribute11
483 ,bonv.attribute12 Attribute12
484 ,bonv.attribute13 Attribute13
485 ,bonv.attribute14 Attribute14
486 ,bonv.attribute15 Attribute15
487 ,bonv.original_system_reference Original_System_Reference
488 ,'CREATE' Transaction_Type
489 ,NULL Return_Status
490 From
491 bom_operation_networks_v bonv,
492 bom_operation_sequences bos1,
493 bom_operation_sequences bos2
494 Where
495 bonv.routing_sequence_id = p_routing_sequence_id
496 and bonv.from_op_seq_id = bos1.operation_sequence_id
497 and ((bos1.effectivity_date <= l_today and nvl(bos1.disable_date,l_today+1) > l_today)
498 or (bos1.effectivity_date > l_today and nvl(bos1.disable_date, bos1.effectivity_date+1) > bos1.effectivity_date))
499 and bonv.to_op_seq_id = bos2.operation_sequence_id
500 and ((bos2.effectivity_date <= l_today and nvl(bos2.disable_date,l_today+1) > l_today)
501 or (bos2.effectivity_date > l_today and nvl(bos2.disable_date, bos2.effectivity_date+1) > bos2.effectivity_date))
502 ;
503 l_networks_rec c_networks%ROWTYPE;
504
505
506 Cursor c_std_ops(p_routing_sequence_id number) Is
507 Select distinct
508 standard_operation_id
509 ,standard_operation_code
510 ,operation_type
511 From
512 bom_operation_sequences_v bosv
513 Where
514 routing_sequence_id = p_routing_sequence_id
515 and ((effectivity_date <= l_today and nvl(disable_date,l_today+1) > l_today)
516 or (effectivity_date > l_today and nvl(disable_date, effectivity_date+1) > effectivity_date))
517 Order by
518 standard_operation_code
519 ;
520
521
522 Cursor c_std_op(p_standard_operation_id number) Is
523 Select
524 *
525 From
526 bom_standard_operations
527 Where
528 standard_operation_id = p_standard_operation_id
529 ;
530
531
532 Cursor c_std_op_res(p_standard_operation_id number) Is
533 Select
534 *
535 From
536 bom_std_op_resources
537 Where
538 standard_operation_id = p_standard_operation_id
539 ;
540
541 Cursor c_new_rtg_seq_id(p_item_id number, p_org_id number, p_alternate varchar2) Is
542 Select
543 routing_sequence_id
544 From
545 bom_operational_routings
546 Where
547 assembly_item_id = p_item_id
548 and organization_id = p_org_id
549 and nvl(alternate_routing_designator,'NONE') = nvl(p_alternate,'NONE')
550 ;
551
552 Cursor c_op_seq_id(p_rtg_seq_id number, p_op_type number, p_op_seq_num number) Is
553 Select
554 operation_sequence_id
555 From
556 bom_operation_sequences
557 Where
558 routing_sequence_id = p_rtg_seq_id
559 and nvl(operation_type,0) = nvl(p_op_type,0)
560 and operation_seq_num = p_op_seq_num
561 ;
562
563
564 l_bom_rec c_bill_sequence%ROWTYPE;
565
566 TYPE flm_rtg_tbl IS TABLE of c_routings%ROWTYPE INDEX BY BINARY_INTEGER;
567
568 l_rtg_tbl flm_rtg_tbl;
569 t_rtg_tbl flm_rtg_tbl;
570
571 std_op_rec c_std_op%ROWTYPE;
572
573
574 Begin
575 retcode := 0;
576
577
578 -- retrieve list of routings
579 i := 1;
580 l_rtg_tbl := t_rtg_tbl;
581
582 FOR l_rtg_rec IN c_routings LOOP
583 l_rtg_tbl(i) := l_rtg_rec;
584 i := i + 1;
585 END LOOP;
586
587 if (l_rtg_tbl.COUNT <= 0) then
588 if (G_LOG_ON) then
589 FND_FILE.PUT_LINE(FND_FILE.LOG, 'No routings to copy.');
590 end if;
591 return;
592 end if;
593
594
595 -- remove duplicates on destination line
596 i := l_rtg_tbl.FIRST;
597 last := l_rtg_tbl.LAST;
598 LOOP
599 select count(*) into dup
600 from bom_operational_routings bor
601 where bor.organization_id = p_organization_id
602 -- and bor.line_id = p_line_id_to
603 and bor.assembly_item_id = l_rtg_tbl(i).assembly_item_id
604 and bor.alternate_routing_designator = p_alternate_code_to;
605 if (dup > 0) then
606 l_rtg_tbl.DELETE(i);
607 end if;
608 EXIT WHEN i = last;
609 i := l_rtg_tbl.NEXT(i);
610 END LOOP;
611
612 if (l_rtg_tbl.COUNT <= 0) then
613 if (G_LOG_ON) then
614 FND_FILE.PUT_LINE(FND_FILE.LOG, 'No routings to copy.');
615 end if;
616 return;
617 end if;
618
619 -- add common routings
620 i := l_rtg_tbl.FIRST;
621 last := l_rtg_tbl.LAST;
622 j := -1;
623 LOOP
624 if (l_rtg_tbl(i).routing_sequence_id <> l_rtg_tbl(i).common_routing_sequence_id) then
625 select count(*) into dup
626 from bom_operational_routings bor
627 where bor.organization_id = p_organization_id
628 -- and bor.line_id = p_line_id_to
629 and bor.assembly_item_id = l_rtg_tbl(i).common_assembly_item_id
630 and bor.alternate_routing_designator = p_alternate_code_to;
631
632 in_list := false;
633 ii := l_rtg_tbl.FIRST;
634 jj := l_rtg_tbl.LAST;
635 LOOP
636 if l_rtg_tbl(ii).routing_sequence_id = l_rtg_tbl(i).common_routing_sequence_id then
637 in_list := true;
638 end if;
639 EXIT WHEN in_list OR (ii = jj);
640 ii := l_rtg_tbl.NEXT(ii);
641 END LOOP;
642
643 if (dup = 0) AND (NOT in_list) then
644 select routing_sequence_id,common_routing_sequence_id,assembly_item_id,
645 common_assembly_item_id,alternate_routing_designator
646 into l_rtg_tbl(j)
647 from bom_operational_routings
648 where routing_sequence_id = l_rtg_tbl(i).common_routing_sequence_id;
649 j := j - 1;
650 end if;
651 end if;
652 EXIT WHEN i = last;
653 i := l_rtg_tbl.NEXT(i);
654 END LOOP;
655
656 l_rtg_total := l_rtg_tbl.COUNT;
657 if (G_LOG_ON) then
658 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Total routings to copy: '||l_rtg_total);
659 end if;
660
661 success := 0;
662
663 -- copy each routing to destination line
664 -- and, copy associated bill if p_copy_bom = 'Y'
665 i := l_rtg_tbl.FIRST;
666 last := l_rtg_tbl.LAST;
667
668 LOOP
669 -- routing header
670 OPEN c_routing_header(l_rtg_tbl(i).routing_sequence_id);
671 FETCH c_routing_header into l_routing_header_rec;
672 l_rtg_header_rec.Assembly_Item_Name := l_routing_header_rec.Assembly_Item_Name;
673 l_rtg_header_rec.Organization_Code := l_routing_header_rec.Organization_Code;
674 l_rtg_header_rec.Alternate_Routing_Code := l_routing_header_rec.Alternate_Routing_Code ;
675 l_rtg_header_rec.Eng_Routing_Flag := l_routing_header_rec.Eng_Routing_Flag;
676 l_rtg_header_rec.Common_Assembly_Item_Name := l_routing_header_rec.Common_Assembly_Item_Name;
677 l_rtg_header_rec.Routing_Comment := l_routing_header_rec.Routing_Comment;
678 l_rtg_header_rec.Completion_Subinventory := l_routing_header_rec.Completion_Subinventory;
679 l_rtg_header_rec.Completion_Location_Name := l_routing_header_rec.Completion_Location_Name;
680 l_rtg_header_rec.Line_Code := l_routing_header_rec.Line_Code;
681 l_rtg_header_rec.CFM_Routing_Flag := l_routing_header_rec.CFM_Routing_Flag;
682 l_rtg_header_rec.Mixed_Model_Map_Flag := l_routing_header_rec.Mixed_Model_Map_Flag;
683 l_rtg_header_rec.Priority := l_routing_header_rec.Priority;
684 l_rtg_header_rec.Total_Cycle_Time := l_routing_header_rec.Total_Cycle_Time;
685 l_rtg_header_rec.CTP_Flag := l_routing_header_rec.CTP_Flag;
686 l_rtg_header_rec.Attribute_category := l_routing_header_rec.Attribute_category;
687 l_rtg_header_rec.Attribute1 := l_routing_header_rec.Attribute1;
688 l_rtg_header_rec.Attribute2 := l_routing_header_rec.Attribute2;
689 l_rtg_header_rec.Attribute3 := l_routing_header_rec.Attribute3;
690 l_rtg_header_rec.Attribute4 := l_routing_header_rec.Attribute4;
691 l_rtg_header_rec.Attribute5 := l_routing_header_rec.Attribute5;
692 l_rtg_header_rec.Attribute6 := l_routing_header_rec.Attribute6;
693 l_rtg_header_rec.Attribute7 := l_routing_header_rec.Attribute7;
694 l_rtg_header_rec.Attribute8 := l_routing_header_rec.Attribute8;
695 l_rtg_header_rec.Attribute9 := l_routing_header_rec.Attribute9;
696 l_rtg_header_rec.Attribute10 := l_routing_header_rec.Attribute10;
697 l_rtg_header_rec.Attribute11 := l_routing_header_rec.Attribute11;
698 l_rtg_header_rec.Attribute12 := l_routing_header_rec.Attribute12;
699 l_rtg_header_rec.Attribute13 := l_routing_header_rec.Attribute13;
700 l_rtg_header_rec.Attribute14 := l_routing_header_rec.Attribute14;
701 l_rtg_header_rec.Attribute15 := l_routing_header_rec.Attribute15;
702 l_rtg_header_rec.Original_System_Reference := l_routing_header_rec.Original_System_Reference;
703 l_rtg_header_rec.Transaction_Type := l_routing_header_rec.Transaction_Type;
704 l_rtg_header_rec.Return_Status := l_routing_header_rec.Return_Status;
705 l_rtg_header_rec.Delete_Group_Name := l_routing_header_rec.Delete_Group_Name;
706 l_rtg_header_rec.DG_Description := l_routing_header_rec.DG_Description;
707 CLOSE c_routing_header;
708
709 l_assembly_item_name := l_rtg_header_rec.assembly_item_name;
710
711 if (G_LOG_ON) then
712 FND_FILE.PUT_LINE(FND_FILE.LOG,'Copy routing: '||
713 l_assembly_item_name||' ('||p_alternate_code_from||' -> '||p_alternate_code_to||')');
714 end if;
715
716 -- routing revisions
717 l_rtg_revision_tbl := t_rtg_revision_tbl;
718 o_rtg_revision_tbl := l_rtg_revision_tbl;
719
720
721 -- copy standard operations and their resources
722 SAVEPOINT copy_std;
723 l_2_ids := t_2_ids;
724 l_2_seq_ids := t_2_ids;
725 FOR stdop in c_std_ops(l_rtg_tbl(i).routing_sequence_id) LOOP
726 -- insert std_op for p_line_id_to
727 -- cp std_op_res from old_op_id to new_op_id
728
729 if (not std_op_exists( p_organization_id, p_line_id_to,
730 stdop.standard_operation_code,
731 stdop.operation_type)) then
732 l_std_op_exist := true;
733 OPEN c_std_op(stdop.standard_operation_id);
734 FETCH c_std_op into std_op_rec;
735 IF c_std_op%NOTFOUND THEN
736 l_std_op_exist := false;
737 END IF;
738 CLOSE c_std_op;
739
740 if (l_std_op_exist) then
741 new_row_id := NULL;
742 new_std_op_id := NULL;
743 b_std_op_pkg.Insert_Row( x_rowid =>new_row_id
744 ,x_standard_operation_id =>new_std_op_id
745 ,x_operation_code =>std_op_rec.operation_code
746 ,x_operation_type =>std_op_rec.operation_type
747 ,x_line_id =>p_line_id_to
748 ,x_sequence_num =>std_op_rec.sequence_num
749 ,x_organization_id =>std_op_rec.organization_id
750 ,x_department_id =>std_op_rec.department_id
751 ,x_last_update_date =>sysdate
752 ,x_last_updated_by =>fnd_global.user_id
753 ,x_creation_date =>sysdate
754 ,x_created_by =>fnd_global.user_id
755 ,x_last_update_login =>fnd_global.login_id
756 ,x_minimum_transfer_quantity =>std_op_rec.minimum_transfer_quantity
757 ,x_count_point_type =>std_op_rec.count_point_type
758 ,x_operation_description =>std_op_rec.operation_description
759 ,x_option_dependent_flag =>std_op_rec.option_dependent_flag
760 ,x_attribute_category =>std_op_rec.attribute_category
761 ,x_attribute1 =>std_op_rec.attribute1
762 ,x_attribute2 =>std_op_rec.attribute2
763 ,x_attribute3 =>std_op_rec.attribute3
764 ,x_attribute4 =>std_op_rec.attribute4
765 ,x_attribute5 =>std_op_rec.attribute5
766 ,x_attribute6 =>std_op_rec.attribute6
767 ,x_attribute7 =>std_op_rec.attribute7
768 ,x_attribute8 =>std_op_rec.attribute8
769 ,x_attribute9 =>std_op_rec.attribute9
770 ,x_attribute10 =>std_op_rec.attribute10
771 ,x_attribute11 =>std_op_rec.attribute11
772 ,x_attribute12 =>std_op_rec.attribute12
773 ,x_attribute13 =>std_op_rec.attribute13
774 ,x_attribute14 =>std_op_rec.attribute14
775 ,x_attribute15 =>std_op_rec.attribute15
776 ,x_backflush_flag =>std_op_rec.backflush_flag
777 ,x_wms_task_type =>std_op_rec.wms_task_type
778 ,x_yield =>std_op_rec.yield
779 ,x_operation_yield_enabled =>std_op_rec.operation_yield_enabled);
780
781 l_2_ids(stdop.standard_operation_id).old_id := stdop.standard_operation_id;
782 l_2_ids(stdop.standard_operation_id).new_id := new_std_op_id;
783
784 FOR std_op_res_rec in c_std_op_res(stdop.standard_operation_id) LOOP
785 new_row_id := null;
786 b_std_op_res_pkg.Insert_Row( x_rowid =>new_row_id
787 ,x_standard_operation_id =>new_std_op_id
788 ,x_resource_id =>std_op_res_rec.resource_id
789 ,x_activity_id =>std_op_res_rec.activity_id
790 ,x_last_update_date =>sysdate
791 ,x_last_updated_by =>fnd_global.user_id
792 ,x_creation_date =>sysdate
793 ,x_created_by =>fnd_global.user_id
794 ,x_last_update_login =>fnd_global.login_id
795 ,x_resource_seq_num =>std_op_res_rec.resource_seq_num
796 ,x_usage_rate_or_amount =>std_op_res_rec.usage_rate_or_amount
797 ,x_usage_rate_or_amount_inverse =>std_op_res_rec.usage_rate_or_amount_inverse
798 ,x_basis_type =>std_op_res_rec.basis_type
799 ,x_autocharge_type =>std_op_res_rec.autocharge_type
800 ,x_standard_rate_flag =>std_op_res_rec.standard_rate_flag
801 ,x_assigned_units =>std_op_res_rec.assigned_units
802 ,x_schedule_flag =>std_op_res_rec.schedule_flag
803 ,x_attribute_category =>std_op_res_rec.attribute_category
804 ,x_attribute1 =>std_op_res_rec.attribute1
805 ,x_attribute2 =>std_op_res_rec.attribute2
806 ,x_attribute3 =>std_op_res_rec.attribute3
807 ,x_attribute4 =>std_op_res_rec.attribute4
808 ,x_attribute5 =>std_op_res_rec.attribute5
809 ,x_attribute6 =>std_op_res_rec.attribute6
810 ,x_attribute7 =>std_op_res_rec.attribute7
811 ,x_attribute8 =>std_op_res_rec.attribute8
812 ,x_attribute9 =>std_op_res_rec.attribute9
813 ,x_attribute10 =>std_op_res_rec.attribute10
814 ,x_attribute11 =>std_op_res_rec.attribute11
815 ,x_attribute12 =>std_op_res_rec.attribute12
816 ,x_attribute13 =>std_op_res_rec.attribute13
817 ,x_attribute14 =>std_op_res_rec.attribute14
818 ,x_attribute15 =>std_op_res_rec.attribute15);
819 END LOOP;
820
821 end if; -- l_std_op_exist
822
823 end if; -- to_line std_op not exist
824
825 END LOOP;
826
827 -- operation sequences
828 l_operation_tbl := t_operation_tbl;
829 o_operation_tbl := t_operation_tbl;
830 j := 1;
831 OPEN c_operations(l_rtg_tbl(i).routing_sequence_id);
832 LOOP
833 FETCH c_operations into l_operations_rec;
834 EXIT WHEN c_operations%NOTFOUND;
835 --first initialize the record
836 l_operation_tbl(j).Assembly_Item_Name := null;
837 l_operation_tbl(j).Organization_Code := null;
838 l_operation_tbl(j).Alternate_Routing_Code := null;
839 l_operation_tbl(j).Operation_Sequence_Number := null;
840 l_operation_tbl(j).Operation_Type := null;
841 l_operation_tbl(j).Start_Effective_Date := null;
842 l_operation_tbl(j).New_Operation_Sequence_Number := null;
843 l_operation_tbl(j).New_Start_Effective_Date := null;
844 l_operation_tbl(j).Standard_Operation_Code := null;
845 l_operation_tbl(j).Department_Code := null;
846 l_operation_tbl(j).Op_Lead_Time_Percent := null;
847 l_operation_tbl(j).Minimum_Transfer_Quantity := null;
848 l_operation_tbl(j).Count_Point_Type := null;
849 l_operation_tbl(j).Operation_Description := null;
850 l_operation_tbl(j).Disable_Date := null;
851 l_operation_tbl(j).Backflush_Flag := null;
852 l_operation_tbl(j).Option_Dependent_Flag := null;
853 l_operation_tbl(j).Reference_Flag := null;
854 l_operation_tbl(j).Process_Seq_Number := null;
855 l_operation_tbl(j).Process_Code := null;
856 l_operation_tbl(j).Line_Op_Seq_Number := null;
857 l_operation_tbl(j).Line_Op_Code := null;
858 l_operation_tbl(j).Yield := null;
859 l_operation_tbl(j).Cumulative_Yield := null;
860 l_operation_tbl(j).Reverse_CUM_Yield := null;
861 l_operation_tbl(j).User_Labor_Time := null;
862 l_operation_tbl(j).User_Machine_Time := null;
863 l_operation_tbl(j).Net_Planning_Percent := null;
864 l_operation_tbl(j).Include_In_Rollup := null;
865 l_operation_tbl(j).Op_Yield_Enabled_Flag := null;
866 l_operation_tbl(j).Shutdown_Type := null;
867 l_operation_tbl(j).Attribute_category := null;
868 l_operation_tbl(j).Attribute1 := null;
869 l_operation_tbl(j).Attribute2 := null;
870 l_operation_tbl(j).Attribute3 := null;
871 l_operation_tbl(j).Attribute4 := null;
872 l_operation_tbl(j).Attribute5 := null;
873 l_operation_tbl(j).Attribute6 := null;
874 l_operation_tbl(j).Attribute7 := null;
875 l_operation_tbl(j).Attribute8 := null;
876 l_operation_tbl(j).Attribute9 := null;
877 l_operation_tbl(j).Attribute10 := null;
878 l_operation_tbl(j).Attribute11 := null;
879 l_operation_tbl(j).Attribute12 := null;
880 l_operation_tbl(j).Attribute13 := null;
881 l_operation_tbl(j).Attribute14 := null;
882 l_operation_tbl(j).Attribute15 := null;
883 l_operation_tbl(j).Original_System_Reference := null;
884 l_operation_tbl(j).Transaction_Type := null;
885 l_operation_tbl(j).Return_Status := null;
886 l_operation_tbl(j).Delete_Group_Name := null;
887 l_operation_tbl(j).DG_Description := null;
888
889 --now populate the record from cursor record
890 l_operation_tbl(j).Assembly_Item_Name := l_operations_rec.Assembly_Item_Name;
891 l_operation_tbl(j).Organization_Code := l_operations_rec.Organization_Code;
892 l_operation_tbl(j).Alternate_Routing_Code := l_operations_rec.Alternate_Routing_Code;
893 l_operation_tbl(j).Operation_Sequence_Number := l_operations_rec.Operation_Sequence_Number;
894 l_operation_tbl(j).Operation_Type := l_operations_rec.Operation_Type;
895 l_operation_tbl(j).Start_Effective_Date := l_operations_rec.Start_Effective_Date;
896 l_operation_tbl(j).New_Operation_Sequence_Number := l_operations_rec.New_Operation_Sequence_Number;
897 l_operation_tbl(j).New_Start_Effective_Date := l_operations_rec.New_Start_Effective_Date;
898 l_operation_tbl(j).Standard_Operation_Code := l_operations_rec.Standard_Operation_Code;
899 l_operation_tbl(j).Department_Code := l_operations_rec.Department_Code;
900 l_operation_tbl(j).Op_Lead_Time_Percent := l_operations_rec.Op_Lead_Time_Percent;
901 l_operation_tbl(j).Minimum_Transfer_Quantity := l_operations_rec.Minimum_Transfer_Quantity;
902 l_operation_tbl(j).Count_Point_Type := l_operations_rec.Count_Point_Type;
903 l_operation_tbl(j).Operation_Description := l_operations_rec.Operation_Description;
904 l_operation_tbl(j).Disable_Date := l_operations_rec.Disable_Date;
905 l_operation_tbl(j).Backflush_Flag := l_operations_rec.Backflush_Flag;
906 l_operation_tbl(j).Option_Dependent_Flag := l_operations_rec.Option_Dependent_Flag;
907 l_operation_tbl(j).Reference_Flag := l_operations_rec.Reference_Flag;
908 l_operation_tbl(j).Process_Seq_Number := l_operations_rec.Process_Seq_Number;
909 l_operation_tbl(j).Process_Code := l_operations_rec.Process_Code;
910 l_operation_tbl(j).Line_Op_Seq_Number := l_operations_rec.Line_Op_Seq_Number;
911 l_operation_tbl(j).Line_Op_Code := l_operations_rec.Line_Op_Code;
912 l_operation_tbl(j).Yield := l_operations_rec.Yield;
913 l_operation_tbl(j).Cumulative_Yield := l_operations_rec.Cumulative_Yield;
914 l_operation_tbl(j).Reverse_CUM_Yield := l_operations_rec.Reverse_CUM_Yield;
915 l_operation_tbl(j).User_Labor_Time := l_operations_rec.User_Labor_Time;
916 l_operation_tbl(j).User_Machine_Time := l_operations_rec.User_Machine_Time;
917 l_operation_tbl(j).Net_Planning_Percent := l_operations_rec.Net_Planning_Percent;
918 l_operation_tbl(j).Include_In_Rollup := l_operations_rec.Include_In_Rollup;
919 l_operation_tbl(j).Op_Yield_Enabled_Flag := l_operations_rec.Op_Yield_Enabled_Flag;
920 l_operation_tbl(j).Shutdown_Type := l_operations_rec.Shutdown_Type;
921 l_operation_tbl(j).Attribute_category := l_operations_rec.Attribute_category;
922 l_operation_tbl(j).Attribute1 := l_operations_rec.Attribute1;
923 l_operation_tbl(j).Attribute2 := l_operations_rec.Attribute2;
924 l_operation_tbl(j).Attribute3 := l_operations_rec.Attribute3;
925 l_operation_tbl(j).Attribute4 := l_operations_rec.Attribute4;
926 l_operation_tbl(j).Attribute5 := l_operations_rec.Attribute5;
927 l_operation_tbl(j).Attribute6 := l_operations_rec.Attribute6;
928 l_operation_tbl(j).Attribute7 := l_operations_rec.Attribute7;
929 l_operation_tbl(j).Attribute8 := l_operations_rec.Attribute8;
930 l_operation_tbl(j).Attribute9 := l_operations_rec.Attribute9;
931 l_operation_tbl(j).Attribute10 := l_operations_rec.Attribute10;
932 l_operation_tbl(j).Attribute11 := l_operations_rec.Attribute11;
933 l_operation_tbl(j).Attribute12 := l_operations_rec.Attribute12;
934 l_operation_tbl(j).Attribute13 := l_operations_rec.Attribute13;
935 l_operation_tbl(j).Attribute14 := l_operations_rec.Attribute14;
936 l_operation_tbl(j).Attribute15 := l_operations_rec.Attribute15;
937 l_operation_tbl(j).Original_System_Reference := l_operations_rec.Original_System_Reference;
938 l_operation_tbl(j).Transaction_Type := l_operations_rec.Transaction_Type;
939 l_operation_tbl(j).Return_Status := l_operations_rec.Return_Status;
940 l_operation_tbl(j).Delete_Group_Name := l_operations_rec.Delete_Group_Name;
941 l_operation_tbl(j).DG_Description := l_operations_rec.DG_Description;
942
943 if l_operation_tbl(j).start_effective_date < l_today then
944 l_operation_tbl(j).start_effective_date := l_today;
945 l_operation_tbl(j).new_start_effective_date := l_today;
946 end if;
947 if l_operation_tbl(j).operation_type = 1 then
948 -- check event's process /lineop
949 lineop_in_list := false;
950 process_in_list := false;
951 ii := l_operation_tbl.FIRST;
952 jj := l_operation_tbl.LAST;
953 LOOP
954 if l_operation_tbl(ii).operation_type = 2 and
955 l_operation_tbl(ii).operation_sequence_number = l_operation_tbl(j).process_seq_number and
956 l_operation_tbl(ii).standard_operation_code = l_operation_tbl(j).process_code then
957 process_in_list := true;
958 end if;
959
960 if l_operation_tbl(ii).operation_type = 3 and
961 l_operation_tbl(ii).operation_sequence_number = l_operation_tbl(j).line_op_seq_number and
962 l_operation_tbl(ii).standard_operation_code = l_operation_tbl(j).line_op_code then
963 lineop_in_list := true;
964 end if;
965
966 exit when ii = jj;
967 ii := l_operation_tbl.NEXT(ii);
968
969 END LOOP;
970
971 if (not process_in_list) then
972 l_operation_tbl(j).process_seq_number := null;
973 l_operation_tbl(j).process_code := null;
974 end if;
975
976 if (not lineop_in_list) then
977 l_operation_tbl(j).line_op_seq_number := null;
978 l_operation_tbl(j).line_op_code := null;
979 end if;
980
981 end if;
982 j := j + 1;
983 END LOOP;
984 CLOSE c_operations;
985
986 a_operation_tbl := l_operation_tbl;
987
988 -- operation networks
989 l_op_network_tbl := t_op_network_tbl;
990 o_op_network_tbl := t_op_network_tbl;
991 j := 1;
992 OPEN c_networks(l_rtg_tbl(i).routing_sequence_id);
993 LOOP
994 FETCH c_networks into l_networks_rec;
995 EXIT WHEN c_networks%NOTFOUND;
996 --first initialize the record
997 l_op_network_tbl(j).Assembly_Item_Name := null;
998 l_op_network_tbl(j).Organization_Code := null;
999 l_op_network_tbl(j).Alternate_Routing_Code := null;
1000 l_op_network_tbl(j).Operation_Type := null;
1001 l_op_network_tbl(j).From_Op_Seq_Number := null;
1002 l_op_network_tbl(j).From_X_Coordinate := null;
1003 l_op_network_tbl(j).From_Y_Coordinate := null;
1004 l_op_network_tbl(j).From_Start_Effective_Date := null;
1005 l_op_network_tbl(j).To_Op_Seq_Number := null;
1006 l_op_network_tbl(j).To_X_Coordinate := null;
1007 l_op_network_tbl(j).To_Y_Coordinate := null;
1008 l_op_network_tbl(j).To_Start_Effective_Date := null;
1009 l_op_network_tbl(j).New_From_Op_Seq_Number := null;
1010 l_op_network_tbl(j).New_From_Start_Effective_Date := null;
1011 l_op_network_tbl(j).New_To_Op_Seq_Number := null;
1012 l_op_network_tbl(j).New_To_Start_Effective_Date := null;
1013 l_op_network_tbl(j).Connection_Type := null;
1014 l_op_network_tbl(j).Planning_Percent := null;
1015 l_op_network_tbl(j).Attribute_category := null;
1016 l_op_network_tbl(j).Attribute1 := null;
1017 l_op_network_tbl(j).Attribute2 := null;
1018 l_op_network_tbl(j).Attribute3 := null;
1019 l_op_network_tbl(j).Attribute4 := null;
1020 l_op_network_tbl(j).Attribute5 := null;
1021 l_op_network_tbl(j).Attribute6 := null;
1022 l_op_network_tbl(j).Attribute7 := null;
1023 l_op_network_tbl(j).Attribute8 := null;
1024 l_op_network_tbl(j).Attribute9 := null;
1025 l_op_network_tbl(j).Attribute10 := null;
1026 l_op_network_tbl(j).Attribute11 := null;
1027 l_op_network_tbl(j).Attribute12 := null;
1028 l_op_network_tbl(j).Attribute13 := null;
1029 l_op_network_tbl(j).Attribute14 := null;
1030 l_op_network_tbl(j).Attribute15 := null;
1031 l_op_network_tbl(j).Original_System_Reference := null;
1032 l_op_network_tbl(j).Transaction_Type := null;
1033 l_op_network_tbl(j).Return_Status := null;
1034
1035 --now populate the record from cursor record
1036 l_op_network_tbl(j).Assembly_Item_Name := l_networks_rec.Assembly_Item_Name;
1037 l_op_network_tbl(j).Organization_Code := l_networks_rec.Organization_Code;
1038 l_op_network_tbl(j).Alternate_Routing_Code := l_networks_rec.Alternate_Routing_Code;
1039 l_op_network_tbl(j).Operation_Type := l_networks_rec.Operation_Type;
1040 l_op_network_tbl(j).From_Op_Seq_Number := l_networks_rec.From_Op_Seq_Number;
1041 l_op_network_tbl(j).From_X_Coordinate := l_networks_rec.From_X_Coordinate;
1042 l_op_network_tbl(j).From_Y_Coordinate := l_networks_rec.From_Y_Coordinate;
1043 l_op_network_tbl(j).From_Start_Effective_Date := l_networks_rec.From_Start_Effective_Date;
1044 l_op_network_tbl(j).To_Op_Seq_Number := l_networks_rec.To_Op_Seq_Number;
1045 l_op_network_tbl(j).To_X_Coordinate := l_networks_rec.To_X_Coordinate;
1046 l_op_network_tbl(j).To_Y_Coordinate := l_networks_rec.To_Y_Coordinate;
1047 l_op_network_tbl(j).To_Start_Effective_Date := l_networks_rec.To_Start_Effective_Date;
1048 l_op_network_tbl(j).New_From_Op_Seq_Number := l_networks_rec.New_From_Op_Seq_Number;
1049 l_op_network_tbl(j).New_From_Start_Effective_Date := l_networks_rec.New_From_Start_Effective_Date;
1050 l_op_network_tbl(j).New_To_Op_Seq_Number := l_networks_rec.New_To_Op_Seq_Number;
1051 l_op_network_tbl(j).New_To_Start_Effective_Date := l_networks_rec.New_To_Start_Effective_Date;
1052 l_op_network_tbl(j).Connection_Type := l_networks_rec.Connection_Type;
1053 l_op_network_tbl(j).Planning_Percent := l_networks_rec.Planning_Percent;
1054 l_op_network_tbl(j).Attribute_category := l_networks_rec.Attribute_category;
1055 l_op_network_tbl(j).Attribute1 := l_networks_rec.Attribute1;
1056 l_op_network_tbl(j).Attribute2 := l_networks_rec.Attribute2;
1057 l_op_network_tbl(j).Attribute3 := l_networks_rec.Attribute3;
1058 l_op_network_tbl(j).Attribute4 := l_networks_rec.Attribute4;
1059 l_op_network_tbl(j).Attribute5 := l_networks_rec.Attribute5;
1060 l_op_network_tbl(j).Attribute6 := l_networks_rec.Attribute6;
1061 l_op_network_tbl(j).Attribute7 := l_networks_rec.Attribute7;
1062 l_op_network_tbl(j).Attribute8 := l_networks_rec.Attribute8;
1063 l_op_network_tbl(j).Attribute9 := l_networks_rec.Attribute9;
1064 l_op_network_tbl(j).Attribute10 := l_networks_rec.Attribute10;
1065 l_op_network_tbl(j).Attribute11 := l_networks_rec.Attribute11;
1066 l_op_network_tbl(j).Attribute12 := l_networks_rec.Attribute12;
1067 l_op_network_tbl(j).Attribute13 := l_networks_rec.Attribute13;
1068 l_op_network_tbl(j).Attribute14 := l_networks_rec.Attribute14;
1069 l_op_network_tbl(j).Attribute15 := l_networks_rec.Attribute15;
1070 l_op_network_tbl(j).Original_System_Reference := l_networks_rec.Original_System_Reference;
1071 l_op_network_tbl(j).Transaction_Type := l_networks_rec.Transaction_Type;
1072 l_op_network_tbl(j).Return_Status := l_networks_rec.Return_Status;
1073
1074 if l_op_network_tbl(j).from_start_effective_date < l_today then
1075 l_op_network_tbl(j).from_start_effective_date := l_today;
1076 l_op_network_tbl(j).new_from_start_effective_date := l_today;
1077 end if;
1078 if l_op_network_tbl(j).to_start_effective_date < l_today then
1079 l_op_network_tbl(j).to_start_effective_date := l_today;
1080 l_op_network_tbl(j).new_to_start_effective_date := l_today;
1081 end if;
1082 j := j + 1;
1083 END LOOP;
1084 CLOSE c_networks;
1085
1086 error_handler.initialize;
1087
1088 Bom_Rtg_Pub.Process_Rtg( p_rtg_header_rec =>l_rtg_header_rec
1089 ,p_rtg_revision_tbl =>l_rtg_revision_tbl
1090 ,p_operation_tbl =>l_operation_tbl
1091 ,p_op_resource_tbl =>l_op_resource_tbl
1092 ,p_sub_resource_tbl =>l_sub_resource_tbl
1093 ,p_op_network_tbl =>l_op_network_tbl
1094 ,x_rtg_header_rec =>o_rtg_header_rec
1095 ,x_rtg_revision_tbl =>o_rtg_revision_tbl
1096 ,x_operation_tbl =>o_operation_tbl
1097 ,x_op_resource_tbl =>o_op_resource_tbl
1098 ,x_sub_resource_tbl =>o_sub_resource_tbl
1099 ,x_op_network_tbl =>o_op_network_tbl
1100 ,x_return_status =>o_return_status
1101 ,x_msg_count =>o_msg_count
1102 ,p_debug =>'N'
1103 ,p_output_dir =>NULL
1104 ,p_debug_filename =>'1.log');
1105
1106 if (o_return_status <> 'S') then
1107 retcode := 1;
1108 fnd_message.set_name('FLM','FLM_RTG_BOM_API_FAILED');
1109 fnd_message.set_token('MSG_COUNT',error_handler.get_message_count);
1110 errbuf := fnd_message.get;
1111 if (G_LOG_ON) then
1112 FND_FILE.PUT_LINE(FND_FILE.LOG, errbuf || ' - ' || l_assembly_item_name);
1113 while o_msg_count > 0 loop
1114 error_handler.get_message(l_msg,l_index,l_id,l_type);
1115 FND_FILE.PUT_LINE(FND_FILE.LOG,l_type||' - '||l_msg||' - '||l_id);
1116 o_msg_count := o_msg_count - 1;
1117 end loop;
1118 end if;
1119 ROLLBACK TO SAVEPOINT copy_std;
1120 elsif (o_msg_count > 0) then
1121 if (G_LOG_ON) then
1122 while o_msg_count > 0 loop
1123 error_handler.get_message(l_msg,l_index,l_id,l_type);
1124 FND_FILE.PUT_LINE(FND_FILE.LOG,l_type||' - '||l_msg||' - '||l_id);
1125 o_msg_count := o_msg_count - 1;
1126 end loop;
1127 end if;
1128 end if;
1129
1130 -- copy BOM if source exists and destination doesn't and
1131 -- the assembly is not a product family (p_mode <> 2)
1132 if (o_return_status = 'S' and nvl(p_copy_bom,'N') = 'Y' and p_mode = 1) then
1133 if (not bill_exists(l_rtg_tbl(i).assembly_item_id,p_organization_id,p_alternate_code_to)) then
1134
1135 Open c_bill_sequence(l_rtg_tbl(i).assembly_item_id,p_organization_id,p_alternate_code_from);
1136 Fetch c_bill_sequence into l_bom_rec;
1137 if (c_bill_sequence%NOTFOUND) then
1138 l_from_sequence_id := NULL;
1139 else
1140 l_from_sequence_id := l_bom_rec.bill_sequence_id;
1141 l_to_common_seq_id := l_bom_rec.common_bill_sequence_id;
1142 end if;
1143 Close c_bill_sequence;
1144
1145 if (l_from_sequence_id is not NULL) then
1146
1147 if (G_LOG_ON) then
1148 FND_FILE.PUT_LINE(FND_FILE.LOG,'Copy bill: '||
1149 l_assembly_item_name||' ('||p_alternate_code_from||' -> '||p_alternate_code_to||')');
1150 end if;
1151
1152 l_to_sequence_id := null;
1153
1154 bom_bill_of_matls_pkg.Insert_Row(
1155 X_Rowid =>new_row_id
1156 ,X_Assembly_Item_Id =>l_bom_rec.assembly_item_id
1157 ,X_Organization_Id =>l_bom_rec.organization_id
1158 ,X_Alternate_Bom_Designator =>p_alternate_code_to
1159 ,X_Last_Update_Date =>sysdate
1160 ,X_Last_Updated_By =>fnd_global.user_id
1161 ,X_Creation_Date =>sysdate
1162 ,X_Created_By =>fnd_global.user_id
1163 ,X_Last_Update_Login =>fnd_global.login_id
1164 ,X_Common_Assembly_Item_Id =>l_bom_rec.common_assembly_item_id
1165 ,X_Specific_Assembly_Comment =>l_bom_rec.Specific_Assembly_Comment
1166 ,X_Pending_From_Ecn =>l_bom_rec.Pending_From_Ecn
1167 ,X_Attribute_Category =>l_bom_rec.attribute_category
1168 ,X_Attribute1 =>l_bom_rec.attribute1
1169 ,X_Attribute2 =>l_bom_rec.attribute2
1170 ,X_Attribute3 =>l_bom_rec.attribute3
1171 ,X_Attribute4 =>l_bom_rec.attribute4
1172 ,X_Attribute5 =>l_bom_rec.attribute5
1173 ,X_Attribute6 =>l_bom_rec.attribute6
1174 ,X_Attribute7 =>l_bom_rec.attribute7
1175 ,X_Attribute8 =>l_bom_rec.attribute8
1176 ,X_Attribute9 =>l_bom_rec.attribute9
1177 ,X_Attribute10 =>l_bom_rec.attribute10
1178 ,X_Attribute11 =>l_bom_rec.attribute11
1179 ,X_Attribute12 =>l_bom_rec.attribute12
1180 ,X_Attribute13 =>l_bom_rec.attribute13
1181 ,X_Attribute14 =>l_bom_rec.attribute14
1182 ,X_Attribute15 =>l_bom_rec.attribute15
1183 ,X_Assembly_Type =>l_bom_rec.assembly_type
1184 ,X_Common_Bill_Sequence_Id =>l_to_common_seq_id
1185 ,X_Bill_Sequence_Id =>l_to_sequence_id
1186 ,X_Common_Organization_Id =>l_bom_rec.Common_Organization_Id
1187 ,X_Next_Explode_Date =>l_bom_rec.Next_Explode_Date
1188 );
1189 bom_copy_bill.copy_bill(
1190 to_sequence_id =>l_to_sequence_id
1191 ,from_sequence_id =>l_from_sequence_id
1192 ,from_org_id =>p_organization_id
1193 ,to_org_id =>p_organization_id
1194 ,display_option =>3 -- current+future
1195 ,user_id =>fnd_global.user_id
1196 ,to_item_id =>l_rtg_tbl(i).assembly_item_id
1197 ,direction =>1
1198 ,to_alternate =>p_alternate_code_to
1199 ,rev_date =>sysdate
1200 ,e_change_notice =>NULL
1201 ,rev_item_seq_id =>NULL
1202 ,bill_or_eco =>1
1203 ,eco_eff_date =>NULL
1204 ,eco_unit_number =>NULL
1205 ,unit_number =>NULL
1206 ,from_item_id =>l_rtg_tbl(i).assembly_item_id
1207 );
1208 end if;
1209 end if;
1210 end if;
1211
1212 -- both RTG and/or BOM copied, then copy attachments
1213 if (o_return_status = 'S') then
1214 if l_2_ids.COUNT > 0 then
1215 copy_attach('BOM_STANDARD_OPERATIONS','BOM_STANDARD_OPERATIONS',l_2_ids);
1216 end if;
1217 if a_operation_tbl.COUNT > 0 then
1218
1219 l_new_rtg_seq_id := null;
1220 OPEN c_new_rtg_seq_id(l_rtg_tbl(i).assembly_item_id,p_organization_id,p_alternate_code_to);
1221 FETCH c_new_rtg_seq_id INTO l_new_rtg_seq_id;
1222 if c_new_rtg_seq_id%NOTFOUND then
1223 l_new_rtg_seq_id := null;
1224 end if;
1225 CLOSE c_new_rtg_seq_id;
1226
1227 if (l_new_rtg_seq_id is NOT NULL) then
1228 ii := a_operation_tbl.FIRST;
1229 jj := a_operation_tbl.LAST;
1230 LOOP
1231 l_old_op_seq_id := null;
1232 l_new_op_seq_id := null;
1233 OPEN c_op_seq_id( l_rtg_tbl(i).routing_sequence_id
1234 ,a_operation_tbl(ii).operation_type
1235 ,a_operation_tbl(ii).operation_sequence_number);
1236
1237 FETCH c_op_seq_id into l_old_op_seq_id;
1238 IF c_op_seq_id%NOTFOUND THEN
1239 l_old_op_seq_id := null;
1240 END IF;
1241 CLOSE c_op_seq_id;
1242
1243 OPEN c_op_seq_id( l_new_rtg_seq_id
1244 ,a_operation_tbl(ii).operation_type
1245 ,a_operation_tbl(ii).operation_sequence_number);
1246
1247 FETCH c_op_seq_id into l_new_op_seq_id;
1248 IF c_op_seq_id%NOTFOUND THEN
1249 l_new_op_seq_id := null;
1250 END IF;
1251 CLOSE c_op_seq_id;
1252
1253 if (l_old_op_seq_id is not null and l_new_op_seq_id is not null) then
1254 l_2_seq_ids(l_old_op_seq_id).old_id := l_old_op_seq_id;
1255 l_2_seq_ids(l_old_op_seq_id).new_id := l_new_op_seq_id;
1256 end if;
1257
1258 EXIT WHEN ii = jj;
1259 ii := a_operation_tbl.NEXT(ii);
1260 END LOOP;
1261 end if;
1262 end if;
1263 if l_2_seq_ids.COUNT > 0 then
1264 copy_attach('BOM_OPERATION_SEQUENCES','BOM_OPERATION_SEQUENCES',l_2_seq_ids);
1265 end if;
1266 success := success + 1;
1267 end if;
1268
1269 EXIT WHEN i = last;
1270 i := l_rtg_tbl.NEXT(i);
1271 END LOOP;
1272
1273 if (success = 0) then
1274 retcode := 2;
1275 fnd_message.set_name('FLM', 'FLM_RTG_COPY_NONE');
1276 fnd_message.set_token('RTG_TOTAL',l_rtg_total);
1277 errbuf := fnd_message.get;
1278 if (G_LOG_ON) then
1279 FND_FILE.PUT_LINE(FND_FILE.LOG, errbuf);
1280 end if;
1281 rollback;
1282 else
1283 fnd_message.set_name('FLM', 'FLM_RTG_COPY_DONE');
1284 fnd_message.set_token('RTG_COUNT', success);
1285 fnd_message.set_token('RTG_TOTAL',l_rtg_total);
1286 errbuf := fnd_message.get;
1287 if (G_LOG_ON) then
1288 FND_FILE.PUT_LINE(FND_FILE.LOG, errbuf);
1289 end if;
1290 commit;
1291 end if;
1292
1293 EXCEPTION
1294 WHEN OTHERS THEN
1295 retcode := 2;
1296 errbuf := 'Exception - ' || substr(SQLERRM,1,200) || ' (' || SQLCODE || ')';
1297 if (G_LOG_ON) then
1298 FND_FILE.PUT_LINE(FND_FILE.LOG, errbuf);
1299 end if;
1300 rollback;
1301 return;
1302
1303 End copy_routings;
1304
1305 END flm_copy_routing;