[Home] [Help]
PACKAGE BODY: APPS.FLM_COPY_ROUTING
Source
1 PACKAGE BODY flm_copy_routing AS
2 /* $Header: FLMCPYRB.pls 120.3 2010/12/15 09:21:44 sisankar ship $ */
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 Procedure copy_routings(
123 errbuf OUT NOCOPY varchar2
124 ,retcode OUT NOCOPY number
125 ,p_mode number
126 ,p_organization_id number
127 ,p_line_id_to number
128 ,p_alternate_code_to varchar2
129 ,p_copy_bom varchar2
130 ,p_line_id_from number
131 ,p_alternate_code_from varchar2
132 ,p_product_family_id number
133 ,p_assembly_name_from varchar2
134 ,p_assembly_name_to varchar2
135 ,p_tpct_from number
136 ,p_tpct_to number
137 ,p_lineop_code varchar2
138 ,p_process_code varchar2
139 ) IS
140
141
142 i number;
143 j number;
144 k number;
145 l number;
146 ii number;
147 jj number;
148 in_list boolean;
149 lineop_in_list boolean;
150 process_in_list boolean;
151 dup number;
152 last number;
153 success number;
154 new_row_id varchar2(18);
155 new_std_op_id number;
156 l_rtg_total number;
157 l_op_res_cnt number;
158 -- routing header key
159 l_org_code varchar2(3) := get_org_code(p_organization_id);
160 l_line_code varchar2(10) := get_line_code(p_organization_id,p_line_id_to);
161
162 --Added for bugfix:8416058
163 l_line_code_from varchar2(10) := get_line_code(p_organization_id,p_line_id_from);
164 l_dept_diff number :=0;
165 l_diff_count number;
166
167 l_assembly_item_name varchar2(81);
168 l_today date := sysdate;
169
170 l_rtg_header_rec Bom_Rtg_Pub.Rtg_Header_Rec_Type;
171 l_rtg_revision_tbl Bom_Rtg_Pub.Rtg_Revision_Tbl_Type;
172 l_operation_tbl Bom_Rtg_Pub.Operation_Tbl_Type;
173 l_op_resource_tbl Bom_Rtg_Pub.Op_Resource_Tbl_Type;
174 l_sub_resource_tbl Bom_Rtg_Pub.Sub_Resource_Tbl_Type;
175 l_op_network_tbl Bom_Rtg_Pub.Op_Network_Tbl_Type;
176
177 o_rtg_header_rec Bom_Rtg_Pub.Rtg_Header_Rec_Type;
178 o_rtg_revision_tbl Bom_Rtg_Pub.Rtg_Revision_Tbl_Type;
179 o_operation_tbl Bom_Rtg_Pub.Operation_Tbl_Type;
180 o_op_resource_tbl Bom_Rtg_Pub.Op_Resource_Tbl_Type;
181 o_sub_resource_tbl Bom_Rtg_Pub.Sub_Resource_Tbl_Type;
182 o_op_network_tbl Bom_Rtg_Pub.Op_Network_Tbl_Type;
183
184 t_rtg_revision_tbl Bom_Rtg_Pub.Rtg_Revision_Tbl_Type;
185 t_operation_tbl Bom_Rtg_Pub.Operation_Tbl_Type;
186 t_op_resource_tbl Bom_Rtg_Pub.Op_Resource_Tbl_Type;
187 t_sub_resource_tbl Bom_Rtg_Pub.Sub_Resource_Tbl_Type;
188 t_op_network_tbl Bom_Rtg_Pub.Op_Network_Tbl_Type;
189
190 a_operation_tbl Bom_Rtg_Pub.Operation_Tbl_Type;
191
192 o_return_status Varchar2(8);
193 o_msg_count Number;
194 l_msg varchar2(1000);
195 l_index number;
196 l_id varchar2(30);
197 l_type varchar2(10);
198
199 l_std_op_exist boolean;
200
201 l_new_rtg_seq_id number;
202 l_old_op_seq_id number;
203 l_new_op_seq_id number;
204
205 l_2_ids two_id_list;
206 l_2_seq_ids two_id_list;
207 t_2_ids two_id_list;
208
209 l_from_sequence_id number;
210 l_to_sequence_id number;
211 l_to_common_seq_id number;
212
213 -- CURSORS
214 Cursor c_routings IS
215 Select bor.routing_sequence_id,
216 bor.common_routing_sequence_id,
217 bor.assembly_item_id,
218 bor.common_assembly_item_id,
219 bor.alternate_routing_designator
220 From bom_operational_routings bor, mtl_system_items_kfv msi_kfv
221 Where bor.organization_id = p_organization_id
222 and bor.organization_id = msi_kfv.organization_id
223 and bor.assembly_item_id = msi_kfv.inventory_item_id
224 and bor.line_id = p_line_id_from
225 and bor.cfm_routing_flag = 1
226 and bor.routing_type = 1
227 and ((p_mode = 1 and msi_kfv.bom_item_type <> 5) or (p_mode = 2 and msi_kfv.bom_item_type = 5))
228 and ((p_alternate_code_from is NULL and bor.alternate_routing_designator is NULL)
229 or p_alternate_code_from = bor.alternate_routing_designator)
230 and (p_product_family_id is NULL or p_product_family_id = msi_kfv.product_family_item_id)
231 and (p_assembly_name_from is NULL or p_assembly_name_from <= msi_kfv.concatenated_segments)
232 and (p_assembly_name_to is NULL or p_assembly_name_to >= msi_kfv.concatenated_segments)
233 and (p_tpct_from is NULL or bor.total_product_cycle_time >= p_tpct_from)
234 and (p_tpct_to is NULL or bor.total_product_cycle_time <= p_tpct_to)
235 and (p_lineop_code is NULL or exists (
236 select 1
237 from bom_operation_sequences bos1, bom_standard_operations bso1
238 where bos1.routing_sequence_id = bor.common_routing_sequence_id
239 and p_lineop_code = bso1.operation_code
240 and bos1.standard_operation_id = bso1.standard_operation_id
241 and bso1.organization_id = p_organization_id
242 and bso1.line_id = p_line_id_from
243 and bos1.operation_type = 3)
244 )
245 and (p_process_code is NULL or exists (
246 select 1
247 from bom_operation_sequences bos1, bom_standard_operations bso1
248 where bos1.routing_sequence_id = bor.common_routing_sequence_id
249 and p_process_code = bso1.operation_code
250 and bos1.standard_operation_id = bso1.standard_operation_id
251 and bso1.organization_id = p_organization_id
252 and bso1.line_id = p_line_id_from
253 and bos1.operation_type = 2)
254 )
255 Order by bor.routing_sequence_id ;
256
257 Cursor c_bill_sequence(p_item_id number,p_org_id number,p_alternate varchar2) IS
258 Select * From bom_bill_of_materials
259 Where organization_id = p_org_id
260 and assembly_item_id = p_item_id
261 and (nvl(alternate_bom_designator,'NONE') = nvl(p_alternate,'NONE'));
262
263 Cursor c_routing_header(p_routing_sequence_id number) Is
264 Select flm_util.get_key_flex_item(assembly_item_id,p_organization_id) Assembly_Item_Name
265 ,l_org_code Organization_Code
266 ,p_alternate_code_to Alternate_Routing_Code
267 ,2 Eng_Routing_Flag --eng_routing_flag is oAlternate_Routing_Codepposite of routing_type
268 ,flm_util.get_key_flex_item(common_assembly_item_id,p_organization_id) Common_Assembly_Item_Name
269 ,routing_comment Routing_Comment
270 ,completion_subinventory Completion_Subinventory
271 ,flm_util.get_key_flex_location(completion_locator_id,p_organization_id) Completion_Location_Name
272 ,l_line_code Line_Code
273 ,cfm_routing_flag CFM_Routing_Flag
274 ,2 Mixed_Model_Map_Flag -- mixed_model_map_flag has only one 'Y'=1 for an item
275 ,priority Priority
276 ,total_product_cycle_time Total_Cycle_Time
277 ,2 CTP_Flag --ctp_flag: only one YES=1 for an item
278 ,attribute_category Attribute_category
279 ,attribute1 Attribute1
280 ,attribute2 Attribute2
281 ,attribute3 Attribute3
282 ,attribute4 Attribute4
283 ,attribute5 Attribute5
284 ,attribute6 Attribute6
285 ,attribute7 Attribute7
286 ,attribute8 Attribute8
287 ,attribute9 Attribute9
288 ,attribute10 Attribute10
289 ,attribute11 Attribute11
290 ,attribute12 Attribute12
291 ,attribute13 Attribute13
292 ,attribute14 Attribute14
293 ,attribute15 Attribute15
294 ,original_system_reference Original_System_Reference
295 ,'CREATE' Transaction_Type
296 ,NULL Return_Status
297 ,NULL Delete_Group_Name
298 ,NULL DG_Description
299 From bom_operational_routings_v
300 Where routing_sequence_id = p_routing_sequence_id;
301 l_routing_header_rec c_routing_header%ROWTYPE;
302
303 Cursor c_routing_revision(p_assembly_item_id number) Is
304 Select l_assembly_item_name
305 ,l_org_code -- organization_code
306 ,p_alternate_code_to
307 ,process_revision revision
308 ,effectivity_date start_effective_date
309 ,attribute_category
310 ,attribute1
311 ,attribute2
312 ,attribute3
313 ,attribute4
314 ,attribute5
315 ,attribute6
316 ,attribute7
317 ,attribute8
318 ,attribute9
319 ,attribute10
320 ,attribute11
321 ,attribute12
322 ,attribute13
323 ,attribute14
324 ,attribute15
325 ,NULL
326 ,'CREATE' -- transaction_type
327 ,NULL -- return status
328 From mtl_rtg_item_revisions mrir
329 Where inventory_item_id = p_assembly_item_id
330 and organization_id = p_organization_id;
331
332
333 Cursor c_operations(p_routing_sequence_id number) Is
334 Select l_assembly_item_name Assembly_Item_Name
335 ,l_org_code Organization_Code
336 ,p_alternate_code_to Alternate_Routing_Code
337 ,bosv.operation_seq_num Operation_Sequence_Number
338 ,bosv.operation_type Operation_Type
339 ,bosv.effectivity_date Start_Effective_Date
340 ,bosv.operation_seq_num New_Operation_Sequence_Number
341 ,bosv.effectivity_date New_Start_Effective_Date
342 ,bosv.standard_operation_code Standard_Operation_Code
343 ,bosv.department_code Department_Code
344 ,bosv.operation_lead_time_percent Op_Lead_Time_Percent
345 ,bosv.minimum_transfer_quantity Minimum_Transfer_Quantity
346 ,bosv.count_point_type Count_Point_Type
347 ,bosv.operation_description Operation_Description
348 ,bosv.disable_date Disable_Date
349 ,bosv.backflush_flag Backflush_Flag
350 ,NULL Option_Dependent_Flag
351 -- Bug 10316535.
352 -- ,1 Reference_Flag -- copied always referenced
353 ,bosv.reference_flag
354 ,bosv.process_seq_num Process_Seq_Number
355 ,bosv.process_code Process_Code
356 ,bosv.line_op_seq_num Line_Op_Seq_Number
357 ,bosv.line_op_code Line_Op_Code
358 ,bosv.yield Yield
359 ,bosv.cumulative_yield Cumulative_Yield
360 ,bosv.reverse_cumulative_yield Reverse_CUM_Yield
361 ,bosv.labor_time_user User_Labor_Time
362 ,bosv.machine_time_user User_Machine_Time
363 ,100 Net_Planning_Percent --??????
364 ,bosv.include_in_rollup Include_In_Rollup
365 ,bosv.operation_yield_enabled Op_Yield_Enabled_Flag
366 ,bosv.shutdown_type Shutdown_Type
367 ,bosv.attribute_category Attribute_category
368 ,bosv.attribute1 Attribute1
369 ,bosv.attribute2 Attribute2
370 ,bosv.attribute3 Attribute3
371 ,bosv.attribute4 Attribute4
372 ,bosv.attribute5 Attribute5
373 ,bosv.attribute6 Attribute6
374 ,bosv.attribute7 Attribute7
375 ,bosv.attribute8 Attribute8
376 ,bosv.attribute9 Attribute9
377 ,bosv.attribute10 Attribute10
378 ,bosv.attribute11 Attribute11
379 ,bosv.attribute12 Attribute12
380 ,bosv.attribute13 Attribute13
381 ,bosv.attribute14 Attribute14
382 ,bosv.attribute15 Attribute15
383 ,bosv.original_system_reference Original_System_Reference
384 ,'CREATE' Transaction_Type
385 ,NULL Return_Status
386 ,NULL Delete_Group_Name
387 ,NULL DG_Description
388 ,bosv.OPERATION_SEQUENCE_ID OPERATION_SEQUENCE_ID
389 ,bosv.STANDARD_OPERATION_ID STANDARD_OPERATION_ID
390 From bom_operation_sequences_v bosv
391 Where bosv.routing_sequence_id = p_routing_sequence_id
392 and ((bosv.effectivity_date <= l_today and nvl(bosv.disable_date,l_today+1) > l_today)
393 or (bosv.effectivity_date > l_today and nvl(bosv.disable_date, bosv.effectivity_date+1) > bosv.effectivity_date))
394 Order by bosv.operation_type desc;
395
396 l_operations_rec c_operations%ROWTYPE;
397
398 Cursor c_non_ref_op_res(p_op_sequence_id number) Is
399 select l_assembly_item_name Assembly_Item_Name
400 , l_org_code Organization_Code
401 , p_alternate_code_to Alternate_Routing_Code
402 , null Operation_Sequence_Number
403 , null Operation_Type
404 , null Op_Start_Effective_Date
405 , borv.resource_seq_num Resource_Sequence_Number
406 , borv.resource_code Resource_Code
407 , borv.activity Activity
408 , borv.standard_rate_flag Standard_Rate_Flag
409 , borv.assigned_units Assigned_Units
410 , borv.usage_rate_or_amount Usage_Rate_Or_Amount
411 , borv.usage_rate_or_amount_inverse Usage_Rate_Or_Amount_Inverse
412 , borv.basis_type Basis_Type
413 , borv.schedule_flag Schedule_Flag
414 , borv.resource_offset_percent Resource_offset_percent
415 , borv.autocharge_type Autocharge_type
416 , borv.substitute_group_num Substitute_group_number
417 , borv.schedule_seq_num Schedule_sequence_number
418 , borv.principle_flag Principle_flag
419 , borv.attribute_category Attribute_category
420 , borv.attribute1 Attribute1
421 , borv.attribute2 Attribute2
422 , borv.attribute3 Attribute3
423 , borv.attribute4 Attribute4
424 , borv.attribute5 Attribute5
425 , borv.attribute6 Attribute6
426 , borv.attribute7 Attribute7
427 , borv.attribute8 Attribute8
428 , borv.attribute9 Attribute9
429 , borv.attribute10 Attribute10
430 , borv.attribute11 Attribute11
431 , borv.attribute12 Attribute12
432 , borv.attribute13 Attribute13
433 , borv.attribute14 Attribute14
434 , borv.attribute15 Attribute15
435 , borv.original_system_reference Original_system_reference
436 ,'UPDATE' Transaction_type
437 , null Return_status
438 , borv.setup_code Setup_type
439 , null Row_identifier
440 From bom_operation_resources_v borv
441 Where borv.operation_sequence_id = p_op_sequence_id;
442
443 l_op_res_rec c_non_ref_op_res%ROWTYPE;
444
445 Cursor c_non_ref_op_sub_res(p_op_sequence_id number) Is
446 select
447 l_assembly_item_name Assembly_Item_Name
448 , l_org_code Organization_Code
449 , p_alternate_code_to Alternate_Routing_Code
450 , null Operation_Sequence_Number
451 , null Operation_Type
452 , null Op_Start_Effective_Date
453 , bsorv.Resource_Code Sub_Resource_Code
454 , bsorv.Resource_Code New_Sub_Resource_Code
455 , bsorv.Substitute_Group_Num Substitute_Group_Number
456 , bsorv.Schedule_Seq_Num Schedule_Sequence_Number
457 , bsorv.Replacement_Group_Num Replacement_Group_Number
458 , bsorv.Replacement_Group_Num New_Replacement_Group_Number
459 , bsorv.Activity Activity
460 , bsorv.Standard_Rate_Flag Standard_Rate_Flag
461 , bsorv.Assigned_Units Assigned_Units
462 , bsorv.Usage_Rate_Or_Amount Usage_Rate_Or_Amount
463 , bsorv.Usage_Rate_Or_Amount_Inverse Usage_Rate_Or_Amount_Inverse
464 , bsorv.Basis_Type Basis_Type
465 , bsorv.Basis_Type New_Basis_Type
466 , bsorv.Schedule_Flag Schedule_Flag
467 , bsorv.Resource_Offset_Percent Resource_Offset_Percent
468 , bsorv.Autocharge_Type Autocharge_Type
469 , bsorv.principle_flag Principle_Flag
470 , bsorv.Attribute_category Attribute_category
471 , bsorv.Attribute1 Attribute1
472 , bsorv.Attribute2 Attribute2
473 , bsorv.Attribute3 Attribute3
474 , bsorv.Attribute4 Attribute4
475 , bsorv.Attribute5 Attribute5
476 , bsorv.Attribute6 Attribute6
477 , bsorv.Attribute7 Attribute7
478 , bsorv.Attribute8 Attribute8
479 , bsorv.Attribute9 Attribute9
480 , bsorv.Attribute10 Attribute10
481 , bsorv.Attribute11 Attribute11
482 , bsorv.Attribute12 Attribute12
483 , bsorv.Attribute13 Attribute13
484 , bsorv.Attribute14 Attribute14
485 , bsorv.Attribute15 Attribute15
486 , bsorv.Original_System_Reference Original_System_Reference
487 , 'UPDATE' Transaction_Type
488 , null Return_Status
489 , bsorv.Setup_code Setup_Type
490 , bsorv.resource_id Resource_id
491 from BOM_SUB_OPERATION_RESOURCES_V BSORV
492 Where bsorv.operation_sequence_id = p_op_sequence_id;
493
494 l_op_sub_res_rec c_non_ref_op_sub_res%ROWTYPE;
495
496 Cursor c_networks(p_routing_sequence_id number) Is
497 Select l_assembly_item_name Assembly_Item_Name
498 ,l_org_code Organization_Code
499 ,p_alternate_code_to Alternate_Routing_Code
500 ,bonv.operation_type Operation_Type
501 ,bonv.from_seq_num From_Op_Seq_Number
502 ,bos1.x_coordinate From_X_Coordinate
503 ,bos1.y_coordinate From_Y_Coordinate
504 ,bonv.from_effectivity_date From_Start_Effective_Date
505 ,bonv.to_seq_num To_Op_Seq_Number
506 ,bos2.x_coordinate To_X_Coordinate
507 ,bos2.y_coordinate To_Y_Coordinate
508 ,bonv.to_effectivity_date To_Start_Effective_Date
509 ,bonv.from_seq_num New_From_Op_Seq_Number
510 ,bonv.from_effectivity_date New_From_Start_Effective_Date
511 ,bonv.to_seq_num New_To_Op_Seq_Number
512 ,bonv.to_effectivity_date New_To_Start_Effective_Date
513 ,bonv.transition_type Connection_Type
514 ,bonv.planning_pct Planning_Percent
515 ,bonv.attribute_category Attribute_category
516 ,bonv.attribute1 Attribute1
517 ,bonv.attribute2 Attribute2
518 ,bonv.attribute3 Attribute3
519 ,bonv.attribute4 Attribute4
520 ,bonv.attribute5 Attribute5
521 ,bonv.attribute6 Attribute6
522 ,bonv.attribute7 Attribute7
523 ,bonv.attribute8 Attribute8
524 ,bonv.attribute9 Attribute9
525 ,bonv.attribute10 Attribute10
526 ,bonv.attribute11 Attribute11
527 ,bonv.attribute12 Attribute12
528 ,bonv.attribute13 Attribute13
529 ,bonv.attribute14 Attribute14
530 ,bonv.attribute15 Attribute15
531 ,bonv.original_system_reference Original_System_Reference
532 ,'CREATE' Transaction_Type
533 ,NULL Return_Status
534 From bom_operation_networks_v bonv,
535 bom_operation_sequences bos1,
536 bom_operation_sequences bos2
537 Where bonv.routing_sequence_id = p_routing_sequence_id
538 and bonv.from_op_seq_id = bos1.operation_sequence_id
539 and ((bos1.effectivity_date <= l_today and nvl(bos1.disable_date,l_today+1) > l_today)
540 or (bos1.effectivity_date > l_today and nvl(bos1.disable_date, bos1.effectivity_date+1) > bos1.effectivity_date))
541 and bonv.to_op_seq_id = bos2.operation_sequence_id
542 and ((bos2.effectivity_date <= l_today and nvl(bos2.disable_date,l_today+1) > l_today)
543 or (bos2.effectivity_date > l_today and nvl(bos2.disable_date, bos2.effectivity_date+1) > bos2.effectivity_date));
544 l_networks_rec c_networks%ROWTYPE;
545
546 Cursor c_std_ops(p_routing_sequence_id number) Is
547 Select distinct standard_operation_id
548 ,standard_operation_code
549 ,operation_type
550 From bom_operation_sequences_v bosv
551 Where routing_sequence_id = p_routing_sequence_id
552 and ((effectivity_date <= l_today and nvl(disable_date,l_today+1) > l_today)
553 or (effectivity_date > l_today and nvl(disable_date, effectivity_date+1) > effectivity_date))
554 Order by standard_operation_code;
555
556 Cursor c_std_op(p_standard_operation_id number) Is
557 Select * From bom_standard_operations
558 Where standard_operation_id = p_standard_operation_id;
559
560 Cursor c_std_op_res(p_standard_operation_id number) Is
561 Select * From bom_std_op_resources
562 Where standard_operation_id = p_standard_operation_id ;
563
564 Cursor c_new_rtg_seq_id(p_item_id number, p_org_id number, p_alternate varchar2) Is
565 Select routing_sequence_id
566 From bom_operational_routings
567 Where assembly_item_id = p_item_id
568 and organization_id = p_org_id
569 and nvl(alternate_routing_designator,'NONE') = nvl(p_alternate,'NONE') ;
570
571 Cursor c_op_seq_id(p_rtg_seq_id number, p_op_type number, p_op_seq_num number) Is
572 Select operation_sequence_id
573 From bom_operation_sequences
574 Where routing_sequence_id = p_rtg_seq_id
575 and nvl(operation_type,0) = nvl(p_op_type,0)
576 and operation_seq_num = p_op_seq_num;
577
578 l_bom_rec c_bill_sequence%ROWTYPE;
579 TYPE flm_rtg_tbl IS TABLE of c_routings%ROWTYPE INDEX BY BINARY_INTEGER;
580 l_rtg_tbl flm_rtg_tbl;
581 t_rtg_tbl flm_rtg_tbl;
582 std_op_rec c_std_op%ROWTYPE;
583
584 Begin
585 retcode := 0;
586 -- retrieve list of routings
587 i := 1;
588 l_rtg_tbl := t_rtg_tbl;
589
590 FOR l_rtg_rec IN c_routings LOOP
591 l_rtg_tbl(i) := l_rtg_rec;
592 i := i + 1;
593 END LOOP;
594
595 if (l_rtg_tbl.COUNT <= 0) then
596 if (G_LOG_ON) then
597 FND_FILE.PUT_LINE(FND_FILE.LOG, 'No routings to copy.');
598 end if;
599 return;
600 end if;
601
602 -- remove duplicates on destination line
603 i := l_rtg_tbl.FIRST;
604 last := l_rtg_tbl.LAST;
605 LOOP
606 select count(*) into dup
607 from bom_operational_routings bor
608 where bor.organization_id = p_organization_id
609 -- and bor.line_id = p_line_id_to
610 and bor.assembly_item_id = l_rtg_tbl(i).assembly_item_id
611 and bor.alternate_routing_designator = p_alternate_code_to;
612 if (dup > 0) then
613 l_rtg_tbl.DELETE(i);
614 end if;
615 EXIT WHEN i = last;
616 i := l_rtg_tbl.NEXT(i);
617 END LOOP;
618
619 if (l_rtg_tbl.COUNT <= 0) then
620 if (G_LOG_ON) then
621 FND_FILE.PUT_LINE(FND_FILE.LOG, 'No routings to copy.');
622 end if;
623 return;
624 end if;
625
626 -- add common routings
627 i := l_rtg_tbl.FIRST;
628 last := l_rtg_tbl.LAST;
629 j := -1;
630 LOOP
631 if (l_rtg_tbl(i).routing_sequence_id <> l_rtg_tbl(i).common_routing_sequence_id) then
632 select count(*) into dup
633 from bom_operational_routings bor
634 where bor.organization_id = p_organization_id
635 -- and bor.line_id = p_line_id_to
636 and bor.assembly_item_id = l_rtg_tbl(i).common_assembly_item_id
637 and bor.alternate_routing_designator = p_alternate_code_to;
638
639 in_list := false;
640 ii := l_rtg_tbl.FIRST;
641 jj := l_rtg_tbl.LAST;
642 LOOP
643 if l_rtg_tbl(ii).routing_sequence_id = l_rtg_tbl(i).common_routing_sequence_id then
644 in_list := true;
645 end if;
646 EXIT WHEN in_list OR (ii = jj);
647 ii := l_rtg_tbl.NEXT(ii);
648 END LOOP;
649
650 if (dup = 0) AND (NOT in_list) then
651 select routing_sequence_id,common_routing_sequence_id,assembly_item_id,
652 common_assembly_item_id,alternate_routing_designator
653 into l_rtg_tbl(j)
654 from bom_operational_routings
655 where routing_sequence_id = l_rtg_tbl(i).common_routing_sequence_id;
656 j := j - 1;
657 end if;
658 end if;
659 EXIT WHEN i = last;
660 i := l_rtg_tbl.NEXT(i);
661 END LOOP;
662
663 l_rtg_total := l_rtg_tbl.COUNT;
664 if (G_LOG_ON) then
665 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Total routings to copy: '||l_rtg_total);
666 end if;
667
668 success := 0;
669
670 -- copy each routing to destination line
671 -- and, copy associated bill if p_copy_bom = 'Y'
672 i := l_rtg_tbl.FIRST;
673 last := l_rtg_tbl.LAST;
674
675 LOOP
676 -- routing header
677 OPEN c_routing_header(l_rtg_tbl(i).routing_sequence_id);
678 FETCH c_routing_header into l_routing_header_rec;
679 l_rtg_header_rec.Assembly_Item_Name := l_routing_header_rec.Assembly_Item_Name;
680 l_rtg_header_rec.Organization_Code := l_routing_header_rec.Organization_Code;
681 l_rtg_header_rec.Alternate_Routing_Code := l_routing_header_rec.Alternate_Routing_Code ;
682 l_rtg_header_rec.Eng_Routing_Flag := l_routing_header_rec.Eng_Routing_Flag;
683 l_rtg_header_rec.Common_Assembly_Item_Name := l_routing_header_rec.Common_Assembly_Item_Name;
684 l_rtg_header_rec.Routing_Comment := l_routing_header_rec.Routing_Comment;
685 l_rtg_header_rec.Completion_Subinventory := l_routing_header_rec.Completion_Subinventory;
686 l_rtg_header_rec.Completion_Location_Name := l_routing_header_rec.Completion_Location_Name;
687 l_rtg_header_rec.Line_Code := l_routing_header_rec.Line_Code;
688 l_rtg_header_rec.CFM_Routing_Flag := l_routing_header_rec.CFM_Routing_Flag;
689 l_rtg_header_rec.Mixed_Model_Map_Flag := l_routing_header_rec.Mixed_Model_Map_Flag;
690 l_rtg_header_rec.Priority := l_routing_header_rec.Priority;
691 l_rtg_header_rec.Total_Cycle_Time := l_routing_header_rec.Total_Cycle_Time;
692 l_rtg_header_rec.CTP_Flag := l_routing_header_rec.CTP_Flag;
693 l_rtg_header_rec.Attribute_category := l_routing_header_rec.Attribute_category;
694 l_rtg_header_rec.Attribute1 := l_routing_header_rec.Attribute1;
695 l_rtg_header_rec.Attribute2 := l_routing_header_rec.Attribute2;
696 l_rtg_header_rec.Attribute3 := l_routing_header_rec.Attribute3;
697 l_rtg_header_rec.Attribute4 := l_routing_header_rec.Attribute4;
698 l_rtg_header_rec.Attribute5 := l_routing_header_rec.Attribute5;
699 l_rtg_header_rec.Attribute6 := l_routing_header_rec.Attribute6;
700 l_rtg_header_rec.Attribute7 := l_routing_header_rec.Attribute7;
701 l_rtg_header_rec.Attribute8 := l_routing_header_rec.Attribute8;
702 l_rtg_header_rec.Attribute9 := l_routing_header_rec.Attribute9;
703 l_rtg_header_rec.Attribute10 := l_routing_header_rec.Attribute10;
704 l_rtg_header_rec.Attribute11 := l_routing_header_rec.Attribute11;
705 l_rtg_header_rec.Attribute12 := l_routing_header_rec.Attribute12;
706 l_rtg_header_rec.Attribute13 := l_routing_header_rec.Attribute13;
707 l_rtg_header_rec.Attribute14 := l_routing_header_rec.Attribute14;
708 l_rtg_header_rec.Attribute15 := l_routing_header_rec.Attribute15;
709 l_rtg_header_rec.Original_System_Reference := l_routing_header_rec.Original_System_Reference;
710 l_rtg_header_rec.Transaction_Type := l_routing_header_rec.Transaction_Type;
711 l_rtg_header_rec.Return_Status := l_routing_header_rec.Return_Status;
712 l_rtg_header_rec.Delete_Group_Name := l_routing_header_rec.Delete_Group_Name;
713 l_rtg_header_rec.DG_Description := l_routing_header_rec.DG_Description;
714 CLOSE c_routing_header;
715
716 l_assembly_item_name := l_rtg_header_rec.assembly_item_name;
717
718 if (G_LOG_ON) then
719 FND_FILE.PUT_LINE(FND_FILE.LOG,'Copy routing: '||
720 l_assembly_item_name||' ('||p_alternate_code_from||' -> '||p_alternate_code_to||')');
721 end if;
722
723 -- routing revisions
724 l_rtg_revision_tbl := t_rtg_revision_tbl;
725 o_rtg_revision_tbl := l_rtg_revision_tbl;
726
727 -- copy standard operations and their resources
728 SAVEPOINT copy_std;
729
730 l_2_ids := t_2_ids;
731 l_2_seq_ids := t_2_ids;
732 FOR stdop in c_std_ops(l_rtg_tbl(i).routing_sequence_id)
733 LOOP
734 -- insert std_op for p_line_id_to
735 -- cp std_op_res from old_op_id to new_op_id
736
737 if (not std_op_exists( p_organization_id, p_line_id_to,stdop.standard_operation_code,stdop.operation_type)) then
738 l_std_op_exist := true;
739 OPEN c_std_op(stdop.standard_operation_id);
740 FETCH c_std_op into std_op_rec;
741 IF c_std_op%NOTFOUND THEN
742 l_std_op_exist := false;
743 END IF;
744 CLOSE c_std_op;
745
746 if (l_std_op_exist) then
747 new_row_id := NULL;
748 new_std_op_id := NULL;
749 b_std_op_pkg.Insert_Row( x_rowid =>new_row_id
750 ,x_standard_operation_id =>new_std_op_id
751 ,x_operation_code =>std_op_rec.operation_code
752 ,x_operation_type =>std_op_rec.operation_type
753 ,x_line_id =>p_line_id_to
754 ,x_sequence_num =>std_op_rec.sequence_num
755 ,x_organization_id =>std_op_rec.organization_id
756 ,x_department_id =>std_op_rec.department_id
757 ,x_last_update_date =>sysdate
758 ,x_last_updated_by =>fnd_global.user_id
759 ,x_creation_date =>sysdate
760 ,x_created_by =>fnd_global.user_id
761 ,x_last_update_login =>fnd_global.login_id
762 ,x_minimum_transfer_quantity =>std_op_rec.minimum_transfer_quantity
763 ,x_count_point_type =>std_op_rec.count_point_type
764 ,x_operation_description =>std_op_rec.operation_description
765 ,x_option_dependent_flag =>std_op_rec.option_dependent_flag
766 ,x_attribute_category =>std_op_rec.attribute_category
767 ,x_attribute1 =>std_op_rec.attribute1
768 ,x_attribute2 =>std_op_rec.attribute2
769 ,x_attribute3 =>std_op_rec.attribute3
770 ,x_attribute4 =>std_op_rec.attribute4
771 ,x_attribute5 =>std_op_rec.attribute5
772 ,x_attribute6 =>std_op_rec.attribute6
773 ,x_attribute7 =>std_op_rec.attribute7
774 ,x_attribute8 =>std_op_rec.attribute8
775 ,x_attribute9 =>std_op_rec.attribute9
776 ,x_attribute10 =>std_op_rec.attribute10
777 ,x_attribute11 =>std_op_rec.attribute11
778 ,x_attribute12 =>std_op_rec.attribute12
779 ,x_attribute13 =>std_op_rec.attribute13
780 ,x_attribute14 =>std_op_rec.attribute14
781 ,x_attribute15 =>std_op_rec.attribute15
782 ,x_backflush_flag =>std_op_rec.backflush_flag
783 ,x_wms_task_type =>std_op_rec.wms_task_type
784 ,x_yield =>std_op_rec.yield
785 ,x_operation_yield_enabled =>std_op_rec.operation_yield_enabled);
786
787 l_2_ids(stdop.standard_operation_id).old_id := stdop.standard_operation_id;
788 l_2_ids(stdop.standard_operation_id).new_id := new_std_op_id;
789
790 FOR std_op_res_rec in c_std_op_res(stdop.standard_operation_id)
791 LOOP
792 new_row_id := null;
793 b_std_op_res_pkg.Insert_Row( x_rowid =>new_row_id
794 ,x_standard_operation_id =>new_std_op_id
795 ,x_resource_id =>std_op_res_rec.resource_id
796 ,x_activity_id =>std_op_res_rec.activity_id
797 ,x_last_update_date =>sysdate
798 ,x_last_updated_by =>fnd_global.user_id
799 ,x_creation_date =>sysdate
800 ,x_created_by =>fnd_global.user_id
801 ,x_last_update_login =>fnd_global.login_id
802 ,x_resource_seq_num =>std_op_res_rec.resource_seq_num
803 ,x_usage_rate_or_amount =>std_op_res_rec.usage_rate_or_amount
804 ,x_usage_rate_or_amount_inverse =>std_op_res_rec.usage_rate_or_amount_inverse
805 ,x_basis_type =>std_op_res_rec.basis_type
806 ,x_autocharge_type =>std_op_res_rec.autocharge_type
807 ,x_standard_rate_flag =>std_op_res_rec.standard_rate_flag
808 ,x_assigned_units =>std_op_res_rec.assigned_units
809 ,x_schedule_flag =>std_op_res_rec.schedule_flag
810 ,x_attribute_category =>std_op_res_rec.attribute_category
811 ,x_attribute1 =>std_op_res_rec.attribute1
812 ,x_attribute2 =>std_op_res_rec.attribute2
813 ,x_attribute3 =>std_op_res_rec.attribute3
814 ,x_attribute4 =>std_op_res_rec.attribute4
815 ,x_attribute5 =>std_op_res_rec.attribute5
816 ,x_attribute6 =>std_op_res_rec.attribute6
817 ,x_attribute7 =>std_op_res_rec.attribute7
818 ,x_attribute8 =>std_op_res_rec.attribute8
819 ,x_attribute9 =>std_op_res_rec.attribute9
820 ,x_attribute10 =>std_op_res_rec.attribute10
821 ,x_attribute11 =>std_op_res_rec.attribute11
822 ,x_attribute12 =>std_op_res_rec.attribute12
823 ,x_attribute13 =>std_op_res_rec.attribute13
824 ,x_attribute14 =>std_op_res_rec.attribute14
825 ,x_attribute15 =>std_op_res_rec.attribute15);
826 END LOOP;
827
828 end if; -- l_std_op_exist
829
830 /*Added below else condition for bugfix:8416058.If line operation/process/event already exists on
831 destination line,check whether department of line operation/process/event is same on both source
832 and destination lines.If not same,report it in log file and skip copy for that assembly */
833 else
834
835 BEGIN
836 l_diff_count := 0;
837 select count(*) into l_diff_count
838 from (select department_id from bom_standard_operations where standard_operation_id = stdop.standard_operation_id) a,
839 (select department_id from bom_standard_operations where organization_id = p_organization_id and line_id = p_line_id_to
840 and operation_code = stdop.standard_operation_code and operation_type = stdop.operation_type) b
841 where a.department_id <> b.department_id;
842 if (l_diff_count = 1) then
843 l_dept_diff := 1;
844 retcode := 1;
845 if (stdop.operation_type = 1) then
846 FND_FILE.PUT_LINE(FND_FILE.LOG,'Copy flow routing across Lines program failed to copy for assembly '||
847 l_assembly_item_name||' as department of Event '||stdop.standard_operation_code||' on source line '||l_line_code_from||
848 ' is different from that on the destination line '||l_line_code||'.');
849 elsif (stdop.operation_type = 2) then
850 FND_FILE.PUT_LINE(FND_FILE.LOG,'Copy flow routing across Lines program failed to copy for assembly '||
851 l_assembly_item_name||' as department of Process '||stdop.standard_operation_code||' on source line '||l_line_code_from||
852 ' is different from that on the destination line '||l_line_code||'.');
853 else
854 FND_FILE.PUT_LINE(FND_FILE.LOG,'Copy flow routing across Lines program failed to copy for assembly '||
855 l_assembly_item_name||' as department of Line Operation '||stdop.standard_operation_code||' on source line '||l_line_code_from||
856 ' is different from that on the destination line '||l_line_code||'.');
857 end if;
858 end if;
859 Exception
860 When others then
861 null;
862 END;
863
864 end if; -- to_line std_op not exist
865
866 END LOOP;
867
868 -- Added below code for bugfix:8416058
869 if (l_dept_diff=1) then
870 l_dept_diff :=0;
871 goto SKIP_TILL_HERE;
872 end if;
873
874 -- operation sequences
875 l_operation_tbl := t_operation_tbl;
876 o_operation_tbl := t_operation_tbl;
877 j := 1;
878
879 -- operation resources.
880 l_op_resource_tbl := t_op_resource_tbl;
881 o_op_resource_tbl := t_op_resource_tbl;
882 k := 1;
883
884 -- operation substitute resources.
885 l_sub_resource_tbl := t_sub_resource_tbl;
886 o_sub_resource_tbl := t_sub_resource_tbl;
887 l := 1;
888
889 OPEN c_operations(l_rtg_tbl(i).routing_sequence_id);
890 LOOP
891 FETCH c_operations into l_operations_rec;
892 EXIT WHEN c_operations%NOTFOUND;
893 --first initialize the record
894 l_operation_tbl(j).Assembly_Item_Name := null;
895 l_operation_tbl(j).Organization_Code := null;
896 l_operation_tbl(j).Alternate_Routing_Code := null;
897 l_operation_tbl(j).Operation_Sequence_Number := null;
898 l_operation_tbl(j).Operation_Type := null;
899 l_operation_tbl(j).Start_Effective_Date := null;
900 l_operation_tbl(j).New_Operation_Sequence_Number := null;
901 l_operation_tbl(j).New_Start_Effective_Date := null;
902 l_operation_tbl(j).Standard_Operation_Code := null;
903 l_operation_tbl(j).Department_Code := null;
904 l_operation_tbl(j).Op_Lead_Time_Percent := null;
905 l_operation_tbl(j).Minimum_Transfer_Quantity := null;
906 l_operation_tbl(j).Count_Point_Type := null;
907 l_operation_tbl(j).Operation_Description := null;
908 l_operation_tbl(j).Disable_Date := null;
909 l_operation_tbl(j).Backflush_Flag := null;
910 l_operation_tbl(j).Option_Dependent_Flag := null;
911 l_operation_tbl(j).Reference_Flag := null;
912 l_operation_tbl(j).Process_Seq_Number := null;
913 l_operation_tbl(j).Process_Code := null;
914 l_operation_tbl(j).Line_Op_Seq_Number := null;
915 l_operation_tbl(j).Line_Op_Code := null;
916 l_operation_tbl(j).Yield := null;
917 l_operation_tbl(j).Cumulative_Yield := null;
918 l_operation_tbl(j).Reverse_CUM_Yield := null;
919 l_operation_tbl(j).User_Labor_Time := null;
920 l_operation_tbl(j).User_Machine_Time := null;
921 l_operation_tbl(j).Net_Planning_Percent := null;
922 l_operation_tbl(j).Include_In_Rollup := null;
923 l_operation_tbl(j).Op_Yield_Enabled_Flag := null;
924 l_operation_tbl(j).Shutdown_Type := null;
925 l_operation_tbl(j).Attribute_category := null;
926 l_operation_tbl(j).Attribute1 := null;
927 l_operation_tbl(j).Attribute2 := null;
928 l_operation_tbl(j).Attribute3 := null;
929 l_operation_tbl(j).Attribute4 := null;
930 l_operation_tbl(j).Attribute5 := null;
931 l_operation_tbl(j).Attribute6 := null;
932 l_operation_tbl(j).Attribute7 := null;
933 l_operation_tbl(j).Attribute8 := null;
934 l_operation_tbl(j).Attribute9 := null;
935 l_operation_tbl(j).Attribute10 := null;
936 l_operation_tbl(j).Attribute11 := null;
937 l_operation_tbl(j).Attribute12 := null;
938 l_operation_tbl(j).Attribute13 := null;
939 l_operation_tbl(j).Attribute14 := null;
940 l_operation_tbl(j).Attribute15 := null;
941 l_operation_tbl(j).Original_System_Reference := null;
942 l_operation_tbl(j).Transaction_Type := null;
943 l_operation_tbl(j).Return_Status := null;
944 l_operation_tbl(j).Delete_Group_Name := null;
945 l_operation_tbl(j).DG_Description := null;
946
947 --now populate the record from cursor record
948 l_operation_tbl(j).Assembly_Item_Name := l_operations_rec.Assembly_Item_Name;
949 l_operation_tbl(j).Organization_Code := l_operations_rec.Organization_Code;
950 l_operation_tbl(j).Alternate_Routing_Code := l_operations_rec.Alternate_Routing_Code;
951 l_operation_tbl(j).Operation_Sequence_Number := l_operations_rec.Operation_Sequence_Number;
952 l_operation_tbl(j).Operation_Type := l_operations_rec.Operation_Type;
953 l_operation_tbl(j).Start_Effective_Date := l_operations_rec.Start_Effective_Date;
954 l_operation_tbl(j).New_Operation_Sequence_Number := l_operations_rec.New_Operation_Sequence_Number;
955 l_operation_tbl(j).New_Start_Effective_Date := l_operations_rec.New_Start_Effective_Date;
956 l_operation_tbl(j).Standard_Operation_Code := l_operations_rec.Standard_Operation_Code;
957 l_operation_tbl(j).Department_Code := l_operations_rec.Department_Code;
958 l_operation_tbl(j).Op_Lead_Time_Percent := l_operations_rec.Op_Lead_Time_Percent;
959 l_operation_tbl(j).Minimum_Transfer_Quantity := l_operations_rec.Minimum_Transfer_Quantity;
960 l_operation_tbl(j).Count_Point_Type := l_operations_rec.Count_Point_Type;
961 l_operation_tbl(j).Operation_Description := l_operations_rec.Operation_Description;
962 l_operation_tbl(j).Disable_Date := l_operations_rec.Disable_Date;
963 l_operation_tbl(j).Backflush_Flag := l_operations_rec.Backflush_Flag;
964 l_operation_tbl(j).Option_Dependent_Flag := l_operations_rec.Option_Dependent_Flag;
965 l_operation_tbl(j).Reference_Flag := l_operations_rec.Reference_Flag;
966 l_operation_tbl(j).Process_Seq_Number := l_operations_rec.Process_Seq_Number;
967 l_operation_tbl(j).Process_Code := l_operations_rec.Process_Code;
968 l_operation_tbl(j).Line_Op_Seq_Number := l_operations_rec.Line_Op_Seq_Number;
969 l_operation_tbl(j).Line_Op_Code := l_operations_rec.Line_Op_Code;
970 l_operation_tbl(j).Yield := l_operations_rec.Yield;
971 l_operation_tbl(j).Cumulative_Yield := l_operations_rec.Cumulative_Yield;
972 l_operation_tbl(j).Reverse_CUM_Yield := l_operations_rec.Reverse_CUM_Yield;
973 l_operation_tbl(j).User_Labor_Time := l_operations_rec.User_Labor_Time;
974 l_operation_tbl(j).User_Machine_Time := l_operations_rec.User_Machine_Time;
975 l_operation_tbl(j).Net_Planning_Percent := l_operations_rec.Net_Planning_Percent;
976 l_operation_tbl(j).Include_In_Rollup := l_operations_rec.Include_In_Rollup;
977 l_operation_tbl(j).Op_Yield_Enabled_Flag := l_operations_rec.Op_Yield_Enabled_Flag;
978 l_operation_tbl(j).Shutdown_Type := l_operations_rec.Shutdown_Type;
979 l_operation_tbl(j).Attribute_category := l_operations_rec.Attribute_category;
980 l_operation_tbl(j).Attribute1 := l_operations_rec.Attribute1;
981 l_operation_tbl(j).Attribute2 := l_operations_rec.Attribute2;
982 l_operation_tbl(j).Attribute3 := l_operations_rec.Attribute3;
983 l_operation_tbl(j).Attribute4 := l_operations_rec.Attribute4;
984 l_operation_tbl(j).Attribute5 := l_operations_rec.Attribute5;
985 l_operation_tbl(j).Attribute6 := l_operations_rec.Attribute6;
986 l_operation_tbl(j).Attribute7 := l_operations_rec.Attribute7;
987 l_operation_tbl(j).Attribute8 := l_operations_rec.Attribute8;
988 l_operation_tbl(j).Attribute9 := l_operations_rec.Attribute9;
989 l_operation_tbl(j).Attribute10 := l_operations_rec.Attribute10;
990 l_operation_tbl(j).Attribute11 := l_operations_rec.Attribute11;
991 l_operation_tbl(j).Attribute12 := l_operations_rec.Attribute12;
992 l_operation_tbl(j).Attribute13 := l_operations_rec.Attribute13;
993 l_operation_tbl(j).Attribute14 := l_operations_rec.Attribute14;
994 l_operation_tbl(j).Attribute15 := l_operations_rec.Attribute15;
995 l_operation_tbl(j).Original_System_Reference := l_operations_rec.Original_System_Reference;
996 l_operation_tbl(j).Transaction_Type := l_operations_rec.Transaction_Type;
997 l_operation_tbl(j).Return_Status := l_operations_rec.Return_Status;
998 l_operation_tbl(j).Delete_Group_Name := l_operations_rec.Delete_Group_Name;
999 l_operation_tbl(j).DG_Description := l_operations_rec.DG_Description;
1000
1001 if l_operation_tbl(j).start_effective_date < l_today then
1002 l_operation_tbl(j).start_effective_date := l_today;
1003 l_operation_tbl(j).new_start_effective_date := l_today;
1004 end if;
1005 if l_operation_tbl(j).operation_type = 1 then
1006 -- check event's process /lineop
1007 lineop_in_list := false;
1008 process_in_list := false;
1009 ii := l_operation_tbl.FIRST;
1010 jj := l_operation_tbl.LAST;
1011 LOOP
1012 if l_operation_tbl(ii).operation_type = 2 and
1013 l_operation_tbl(ii).operation_sequence_number = l_operation_tbl(j).process_seq_number and
1014 l_operation_tbl(ii).standard_operation_code = l_operation_tbl(j).process_code then
1015 process_in_list := true;
1016 end if;
1017 if l_operation_tbl(ii).operation_type = 3 and
1018 l_operation_tbl(ii).operation_sequence_number = l_operation_tbl(j).line_op_seq_number and
1019 l_operation_tbl(ii).standard_operation_code = l_operation_tbl(j).line_op_code then
1020 lineop_in_list := true;
1021 end if;
1022 exit when ii = jj;
1023 ii := l_operation_tbl.NEXT(ii);
1024 END LOOP;
1025 if (not process_in_list) then
1026 l_operation_tbl(j).process_seq_number := null;
1027 l_operation_tbl(j).process_code := null;
1028 end if;
1029 if (not lineop_in_list) then
1030 l_operation_tbl(j).line_op_seq_number := null;
1031 l_operation_tbl(j).line_op_code := null;
1032 end if;
1033 end if;
1034
1035 if l_operation_tbl(j).Reference_Flag <> 1 then
1036 OPEN c_non_ref_op_res(l_operations_rec.operation_sequence_id);
1037 LOOP
1038 FETCH c_non_ref_op_res into l_op_res_rec;
1039 EXIT WHEN c_non_ref_op_res%NOTFOUND;
1040 --first initialize the record
1041 l_op_resource_tbl(k).Assembly_Item_Name := null;
1042 l_op_resource_tbl(k).Organization_Code := null;
1043 l_op_resource_tbl(k).Alternate_Routing_Code := null;
1044 l_op_resource_tbl(k).Operation_Sequence_Number := null;
1045 l_op_resource_tbl(k).Operation_Type := null;
1046 l_op_resource_tbl(k).Op_Start_Effective_Date := null;
1047 l_op_resource_tbl(k).Resource_Sequence_Number := null;
1048 l_op_resource_tbl(k).Resource_Code := null;
1049 l_op_resource_tbl(k).Activity := null;
1050 l_op_resource_tbl(k).Standard_Rate_Flag := null;
1051 l_op_resource_tbl(k).Assigned_Units := null;
1052 l_op_resource_tbl(k).Usage_Rate_Or_Amount := null;
1053 l_op_resource_tbl(k).Usage_Rate_Or_Amount_Inverse := null;
1054 l_op_resource_tbl(k).Basis_Type := null;
1055 l_op_resource_tbl(k).Schedule_Flag := null;
1056 l_op_resource_tbl(k).Resource_offset_percent := null;
1057 l_op_resource_tbl(k).Autocharge_type := null;
1058 l_op_resource_tbl(k).Substitute_group_number := null;
1059 l_op_resource_tbl(k).Schedule_sequence_number := null;
1060 l_op_resource_tbl(k).Principle_flag := null;
1061 l_op_resource_tbl(k).Attribute_category := null;
1062 l_op_resource_tbl(k).Attribute1 := null;
1063 l_op_resource_tbl(k).Attribute2 := null;
1064 l_op_resource_tbl(k).Attribute3 := null;
1065 l_op_resource_tbl(k).Attribute4 := null;
1066 l_op_resource_tbl(k).Attribute5 := null;
1067 l_op_resource_tbl(k).Attribute6 := null;
1068 l_op_resource_tbl(k).Attribute7 := null;
1069 l_op_resource_tbl(k).Attribute8 := null;
1070 l_op_resource_tbl(k).Attribute9 := null;
1071 l_op_resource_tbl(k).Attribute10 := null;
1072 l_op_resource_tbl(k).Attribute11 := null;
1073 l_op_resource_tbl(k).Attribute12 := null;
1074 l_op_resource_tbl(k).Attribute13 := null;
1075 l_op_resource_tbl(k).Attribute14 := null;
1076 l_op_resource_tbl(k).Attribute15 := null;
1077 l_op_resource_tbl(k).Original_system_reference := null;
1078 l_op_resource_tbl(k).Transaction_type := null;
1079 l_op_resource_tbl(k).Return_status := null;
1080 l_op_resource_tbl(k).Setup_type := null;
1081 l_op_resource_tbl(k).Row_identifier := null;
1082
1083 --now populate the record from cursor record
1084 l_op_resource_tbl(k).Assembly_Item_Name := l_op_res_rec.Assembly_Item_Name;
1085 l_op_resource_tbl(k).Organization_Code := l_op_res_rec.Organization_Code;
1086 l_op_resource_tbl(k).Alternate_Routing_Code := l_op_res_rec.Alternate_Routing_Code;
1087 l_op_resource_tbl(k).Operation_Sequence_Number := l_operation_tbl(j).Operation_Sequence_Number;
1088 l_op_resource_tbl(k).Operation_Type := l_operation_tbl(j).Operation_Type ;
1089 l_op_resource_tbl(k).Op_Start_Effective_Date := l_operation_tbl(j).Start_Effective_Date;
1090 l_op_resource_tbl(k).Resource_Sequence_Number := l_op_res_rec.Resource_Sequence_Number;
1091 l_op_resource_tbl(k).Resource_Code := l_op_res_rec.Resource_Code ;
1092 l_op_resource_tbl(k).Activity := l_op_res_rec.Activity ;
1093 l_op_resource_tbl(k).Standard_Rate_Flag := l_op_res_rec.Standard_Rate_Flag ;
1094 l_op_resource_tbl(k).Assigned_Units := l_op_res_rec.Assigned_Units ;
1095 l_op_resource_tbl(k).Usage_Rate_Or_Amount := l_op_res_rec.Usage_Rate_Or_Amount;
1096 l_op_resource_tbl(k).Usage_Rate_Or_Amount_Inverse := l_op_res_rec.Usage_Rate_Or_Amount_Inverse;
1097 l_op_resource_tbl(k).Basis_Type := l_op_res_rec.Basis_Type;
1098 l_op_resource_tbl(k).Schedule_Flag := l_op_res_rec.Schedule_Flag ;
1099 l_op_resource_tbl(k).Resource_offset_percent := l_op_res_rec.Resource_offset_percent ;
1100 l_op_resource_tbl(k).Autocharge_type := l_op_res_rec.Autocharge_type ;
1101 l_op_resource_tbl(k).Substitute_group_number := l_op_res_rec.Substitute_group_number ;
1102 l_op_resource_tbl(k).Schedule_sequence_number := l_op_res_rec.Schedule_sequence_number;
1103 l_op_resource_tbl(k).Principle_flag := l_op_res_rec.Principle_flag ;
1104 l_op_resource_tbl(k).Attribute_category := l_op_res_rec.Attribute_category ;
1105 l_op_resource_tbl(k).Attribute1 := l_op_res_rec.Attribute1 ;
1106 l_op_resource_tbl(k).Attribute2 := l_op_res_rec.Attribute2 ;
1107 l_op_resource_tbl(k).Attribute3 := l_op_res_rec.Attribute3 ;
1108 l_op_resource_tbl(k).Attribute4 := l_op_res_rec.Attribute4 ;
1109 l_op_resource_tbl(k).Attribute5 := l_op_res_rec.Attribute5 ;
1110 l_op_resource_tbl(k).Attribute6 := l_op_res_rec.Attribute6 ;
1111 l_op_resource_tbl(k).Attribute7 := l_op_res_rec.Attribute7 ;
1112 l_op_resource_tbl(k).Attribute8 := l_op_res_rec.Attribute8 ;
1113 l_op_resource_tbl(k).Attribute9 := l_op_res_rec.Attribute9 ;
1114 l_op_resource_tbl(k).Attribute10 := l_op_res_rec.Attribute10;
1115 l_op_resource_tbl(k).Attribute11 := l_op_res_rec.Attribute11;
1116 l_op_resource_tbl(k).Attribute12 := l_op_res_rec.Attribute12;
1117 l_op_resource_tbl(k).Attribute13 := l_op_res_rec.Attribute13;
1118 l_op_resource_tbl(k).Attribute14 := l_op_res_rec.Attribute14;
1119 l_op_resource_tbl(k).Attribute15 := l_op_res_rec.Attribute15;
1120 l_op_resource_tbl(k).Original_system_reference := l_op_res_rec.Original_system_reference ;
1121 l_op_resource_tbl(k).Transaction_type := l_op_res_rec.Transaction_type;
1122 l_op_resource_tbl(k).Return_status := l_op_res_rec.Return_status ;
1123 l_op_resource_tbl(k).Setup_type := l_op_res_rec.Setup_type ;
1124 l_op_resource_tbl(k).Row_identifier := l_op_res_rec.Row_identifier;
1125 l_op_res_cnt := 0;
1126 if l_operations_rec.Standard_Operation_id is not null then
1127 select count(1) into l_op_res_cnt
1128 from bom_std_op_resources bsor
1129 where bsor.Standard_Operation_id = l_operations_rec.Standard_Operation_id
1130 and bsor.resource_seq_num = l_op_res_rec.Resource_Sequence_Number;
1131 if l_op_res_cnt > 0 then
1132 l_op_resource_tbl(k).Transaction_type := 'UPDATE';
1133 else
1134 l_op_resource_tbl(k).Transaction_type := 'CREATE';
1135 end if;
1136 else
1137 l_op_resource_tbl(k).Transaction_type := 'CREATE' ;
1138 end if;
1139 k := k + 1;
1140 END LOOP;
1141 CLOSE c_non_ref_op_res;
1142
1143 OPEN c_non_ref_op_sub_res(l_operations_rec.operation_sequence_id);
1144 LOOP
1145 FETCH c_non_ref_op_sub_res into l_op_sub_res_rec;
1146 EXIT WHEN c_non_ref_op_sub_res%NOTFOUND;
1147
1148 --first initialize the record
1149 l_sub_resource_tbl(l).Assembly_Item_Name := null;
1150 l_sub_resource_tbl(l).Organization_Code := null ;
1151 l_sub_resource_tbl(l).Alternate_Routing_Code := null ;
1152 l_sub_resource_tbl(l).Operation_Sequence_Number := null ;
1153 l_sub_resource_tbl(l).Operation_Type := null ;
1154 l_sub_resource_tbl(l).Op_Start_Effective_Date := null ;
1155 l_sub_resource_tbl(l).Sub_Resource_Code := null ;
1156 l_sub_resource_tbl(l).New_Sub_Resource_Code := null ;
1157 l_sub_resource_tbl(l).Substitute_Group_Number := null ;
1158 l_sub_resource_tbl(l).Schedule_Sequence_Number := null ;
1159 l_sub_resource_tbl(l).Replacement_Group_Number := null ;
1160 l_sub_resource_tbl(l).New_Replacement_Group_Number := null ;
1161 l_sub_resource_tbl(l).Activity := null ;
1162 l_sub_resource_tbl(l).Standard_Rate_Flag := null ;
1163 l_sub_resource_tbl(l).Assigned_Units := null ;
1164 l_sub_resource_tbl(l).Usage_Rate_Or_Amount := null ;
1165 l_sub_resource_tbl(l).Usage_Rate_Or_Amount_Inverse := null ;
1166 l_sub_resource_tbl(l).Basis_Type := null ;
1167 l_sub_resource_tbl(l).New_Basis_Type := null ;
1168 l_sub_resource_tbl(l).Schedule_Flag := null ;
1169 l_sub_resource_tbl(l).Resource_Offset_Percent := null ;
1170 l_sub_resource_tbl(l).Autocharge_Type := null ;
1171 l_sub_resource_tbl(l).Principle_Flag := null ;
1172 l_sub_resource_tbl(l).Attribute_category := null ;
1173 l_sub_resource_tbl(l).Attribute1 := null ;
1174 l_sub_resource_tbl(l).Attribute2 := null ;
1175 l_sub_resource_tbl(l).Attribute3 := null ;
1176 l_sub_resource_tbl(l).Attribute4 := null ;
1177 l_sub_resource_tbl(l).Attribute5 := null ;
1178 l_sub_resource_tbl(l).Attribute6 := null ;
1179 l_sub_resource_tbl(l).Attribute7 := null ;
1180 l_sub_resource_tbl(l).Attribute8 := null ;
1181 l_sub_resource_tbl(l).Attribute9 := null ;
1182 l_sub_resource_tbl(l).Attribute10 := null ;
1183 l_sub_resource_tbl(l).Attribute11 := null ;
1184 l_sub_resource_tbl(l).Attribute12 := null ;
1185 l_sub_resource_tbl(l).Attribute13 := null ;
1186 l_sub_resource_tbl(l).Attribute14 := null ;
1187 l_sub_resource_tbl(l).Attribute15 := null ;
1188 l_sub_resource_tbl(l).Original_System_Reference := null ;
1189 l_sub_resource_tbl(l).Transaction_Type := null ;
1190 l_sub_resource_tbl(l).Return_Status := null ;
1191 l_sub_resource_tbl(l).Setup_Type := null ;
1192
1193
1194 --now populate the record from cursor record
1195 l_sub_resource_tbl(l).Assembly_Item_Name := l_op_sub_res_rec.Assembly_Item_Name;
1196 l_sub_resource_tbl(l).Organization_Code := l_op_sub_res_rec.Organization_Code ;
1197 l_sub_resource_tbl(l).Alternate_Routing_Code := l_op_sub_res_rec.Alternate_Routing_Code;
1198 l_sub_resource_tbl(l).Operation_Sequence_Number := l_operation_tbl(j).Operation_Sequence_Number ;
1199 l_sub_resource_tbl(l).Operation_Type := l_operation_tbl(j).Operation_Type ;
1200 l_sub_resource_tbl(l).Op_Start_Effective_Date := l_operation_tbl(j).Start_Effective_Date ;
1201 l_sub_resource_tbl(l).Sub_Resource_Code := l_op_sub_res_rec.Sub_Resource_Code ;
1202 l_sub_resource_tbl(l).New_Sub_Resource_Code := l_op_sub_res_rec.New_Sub_Resource_Code;
1203 l_sub_resource_tbl(l).Substitute_Group_Number := l_op_sub_res_rec.Substitute_Group_Number ;
1204 l_sub_resource_tbl(l).Schedule_Sequence_Number := l_op_sub_res_rec.Schedule_Sequence_Number;
1205 l_sub_resource_tbl(l).Replacement_Group_Number := l_op_sub_res_rec.Replacement_Group_Number;
1206 l_sub_resource_tbl(l).New_Replacement_Group_Number := l_op_sub_res_rec.New_Replacement_Group_Number;
1207 l_sub_resource_tbl(l).Activity := l_op_sub_res_rec.Activity ;
1208 l_sub_resource_tbl(l).Standard_Rate_Flag := l_op_sub_res_rec.Standard_Rate_Flag ;
1209 l_sub_resource_tbl(l).Assigned_Units := l_op_sub_res_rec.Assigned_Units ;
1210 l_sub_resource_tbl(l).Usage_Rate_Or_Amount := l_op_sub_res_rec.Usage_Rate_Or_Amount;
1211 l_sub_resource_tbl(l).Usage_Rate_Or_Amount_Inverse := l_op_sub_res_rec.Usage_Rate_Or_Amount_Inverse;
1212 l_sub_resource_tbl(l).Basis_Type := l_op_sub_res_rec.Basis_Type ;
1213 l_sub_resource_tbl(l).New_Basis_Type := l_op_sub_res_rec.New_Basis_Type ;
1214 l_sub_resource_tbl(l).Schedule_Flag := l_op_sub_res_rec.Schedule_Flag ;
1215 l_sub_resource_tbl(l).Resource_Offset_Percent := l_op_sub_res_rec.Resource_Offset_Percent ;
1216 l_sub_resource_tbl(l).Autocharge_Type := l_op_sub_res_rec.Autocharge_Type;
1217 l_sub_resource_tbl(l).Principle_Flag := l_op_sub_res_rec.Principle_Flag ;
1218 l_sub_resource_tbl(l).Attribute_category := l_op_sub_res_rec.Attribute_category ;
1219 l_sub_resource_tbl(l).Attribute1 := l_op_sub_res_rec.Attribute1;
1220 l_sub_resource_tbl(l).Attribute2 := l_op_sub_res_rec.Attribute2;
1221 l_sub_resource_tbl(l).Attribute3 := l_op_sub_res_rec.Attribute3;
1222 l_sub_resource_tbl(l).Attribute4 := l_op_sub_res_rec.Attribute4;
1223 l_sub_resource_tbl(l).Attribute5 := l_op_sub_res_rec.Attribute5;
1224 l_sub_resource_tbl(l).Attribute6 := l_op_sub_res_rec.Attribute6;
1225 l_sub_resource_tbl(l).Attribute7 := l_op_sub_res_rec.Attribute7;
1226 l_sub_resource_tbl(l).Attribute8 := l_op_sub_res_rec.Attribute8;
1227 l_sub_resource_tbl(l).Attribute9 := l_op_sub_res_rec.Attribute9;
1228 l_sub_resource_tbl(l).Attribute10 := l_op_sub_res_rec.Attribute10 ;
1229 l_sub_resource_tbl(l).Attribute11 := l_op_sub_res_rec.Attribute11 ;
1230 l_sub_resource_tbl(l).Attribute12 := l_op_sub_res_rec.Attribute12 ;
1231 l_sub_resource_tbl(l).Attribute13 := l_op_sub_res_rec.Attribute13 ;
1232 l_sub_resource_tbl(l).Attribute14 := l_op_sub_res_rec.Attribute14 ;
1233 l_sub_resource_tbl(l).Attribute15 := l_op_sub_res_rec.Attribute15 ;
1234 l_sub_resource_tbl(l).Original_System_Reference := l_op_sub_res_rec.Original_System_Reference ;
1235 l_sub_resource_tbl(l).Transaction_Type := l_op_sub_res_rec.Transaction_Type;
1236 l_sub_resource_tbl(l).Return_Status := l_op_sub_res_rec.Return_Status ;
1237 l_sub_resource_tbl(l).Setup_Type := l_op_sub_res_rec.Setup_Type ;
1238
1239 l_op_res_cnt := 0;
1240 if l_operations_rec.Standard_Operation_id is not null then
1241 select count(1) into l_op_res_cnt
1242 from BOM_STD_SUB_OP_RESOURCES bsor
1243 where bsor.Standard_Operation_id = l_operations_rec.Standard_Operation_id
1244 and bsor.resource_id = l_op_sub_res_rec.resource_id
1245 and bsor.substitute_group_num = l_op_sub_res_rec.Substitute_Group_Number
1246 and bsor.replacement_group_num = l_op_sub_res_rec.Replacement_Group_Number
1247 and bsor.basis_type = l_op_sub_res_rec.Basis_Type;
1248 if l_op_res_cnt > 0 then
1249 l_sub_resource_tbl(l).Transaction_type := 'UPDATE';
1250 else
1251 l_sub_resource_tbl(l).Transaction_type := 'CREATE';
1252 end if;
1253 else
1254 l_sub_resource_tbl(l).Transaction_type := 'CREATE' ;
1255 end if;
1256 l := l + 1;
1257 END LOOP;
1258 CLOSE c_non_ref_op_sub_res;
1259
1260 end if;
1261 j := j + 1;
1262 END LOOP;
1263 CLOSE c_operations;
1264
1265 a_operation_tbl := l_operation_tbl;
1266
1267 -- operation networks
1268 l_op_network_tbl := t_op_network_tbl;
1269 o_op_network_tbl := t_op_network_tbl;
1270 j := 1;
1271 OPEN c_networks(l_rtg_tbl(i).routing_sequence_id);
1272 LOOP
1273 FETCH c_networks into l_networks_rec;
1274 EXIT WHEN c_networks%NOTFOUND;
1275 --first initialize the record
1276 l_op_network_tbl(j).Assembly_Item_Name := null;
1277 l_op_network_tbl(j).Organization_Code := null;
1278 l_op_network_tbl(j).Alternate_Routing_Code := null;
1279 l_op_network_tbl(j).Operation_Type := null;
1280 l_op_network_tbl(j).From_Op_Seq_Number := null;
1281 l_op_network_tbl(j).From_X_Coordinate := null;
1282 l_op_network_tbl(j).From_Y_Coordinate := null;
1283 l_op_network_tbl(j).From_Start_Effective_Date := null;
1284 l_op_network_tbl(j).To_Op_Seq_Number := null;
1285 l_op_network_tbl(j).To_X_Coordinate := null;
1286 l_op_network_tbl(j).To_Y_Coordinate := null;
1287 l_op_network_tbl(j).To_Start_Effective_Date := null;
1288 l_op_network_tbl(j).New_From_Op_Seq_Number := null;
1289 l_op_network_tbl(j).New_From_Start_Effective_Date := null;
1290 l_op_network_tbl(j).New_To_Op_Seq_Number := null;
1291 l_op_network_tbl(j).New_To_Start_Effective_Date := null;
1292 l_op_network_tbl(j).Connection_Type := null;
1293 l_op_network_tbl(j).Planning_Percent := null;
1294 l_op_network_tbl(j).Attribute_category := null;
1295 l_op_network_tbl(j).Attribute1 := null;
1296 l_op_network_tbl(j).Attribute2 := null;
1297 l_op_network_tbl(j).Attribute3 := null;
1298 l_op_network_tbl(j).Attribute4 := null;
1299 l_op_network_tbl(j).Attribute5 := null;
1300 l_op_network_tbl(j).Attribute6 := null;
1301 l_op_network_tbl(j).Attribute7 := null;
1302 l_op_network_tbl(j).Attribute8 := null;
1303 l_op_network_tbl(j).Attribute9 := null;
1304 l_op_network_tbl(j).Attribute10 := null;
1305 l_op_network_tbl(j).Attribute11 := null;
1306 l_op_network_tbl(j).Attribute12 := null;
1307 l_op_network_tbl(j).Attribute13 := null;
1308 l_op_network_tbl(j).Attribute14 := null;
1309 l_op_network_tbl(j).Attribute15 := null;
1310 l_op_network_tbl(j).Original_System_Reference := null;
1311 l_op_network_tbl(j).Transaction_Type := null;
1312 l_op_network_tbl(j).Return_Status := null;
1313
1314 --now populate the record from cursor record
1315 l_op_network_tbl(j).Assembly_Item_Name := l_networks_rec.Assembly_Item_Name;
1316 l_op_network_tbl(j).Organization_Code := l_networks_rec.Organization_Code;
1317 l_op_network_tbl(j).Alternate_Routing_Code := l_networks_rec.Alternate_Routing_Code;
1318 l_op_network_tbl(j).Operation_Type := l_networks_rec.Operation_Type;
1319 l_op_network_tbl(j).From_Op_Seq_Number := l_networks_rec.From_Op_Seq_Number;
1320 l_op_network_tbl(j).From_X_Coordinate := l_networks_rec.From_X_Coordinate;
1321 l_op_network_tbl(j).From_Y_Coordinate := l_networks_rec.From_Y_Coordinate;
1322 l_op_network_tbl(j).From_Start_Effective_Date := l_networks_rec.From_Start_Effective_Date;
1323 l_op_network_tbl(j).To_Op_Seq_Number := l_networks_rec.To_Op_Seq_Number;
1324 l_op_network_tbl(j).To_X_Coordinate := l_networks_rec.To_X_Coordinate;
1325 l_op_network_tbl(j).To_Y_Coordinate := l_networks_rec.To_Y_Coordinate;
1326 l_op_network_tbl(j).To_Start_Effective_Date := l_networks_rec.To_Start_Effective_Date;
1327 l_op_network_tbl(j).New_From_Op_Seq_Number := l_networks_rec.New_From_Op_Seq_Number;
1328 l_op_network_tbl(j).New_From_Start_Effective_Date := l_networks_rec.New_From_Start_Effective_Date;
1329 l_op_network_tbl(j).New_To_Op_Seq_Number := l_networks_rec.New_To_Op_Seq_Number;
1330 l_op_network_tbl(j).New_To_Start_Effective_Date := l_networks_rec.New_To_Start_Effective_Date;
1331 l_op_network_tbl(j).Connection_Type := l_networks_rec.Connection_Type;
1332 l_op_network_tbl(j).Planning_Percent := l_networks_rec.Planning_Percent;
1333 l_op_network_tbl(j).Attribute_category := l_networks_rec.Attribute_category;
1334 l_op_network_tbl(j).Attribute1 := l_networks_rec.Attribute1;
1335 l_op_network_tbl(j).Attribute2 := l_networks_rec.Attribute2;
1336 l_op_network_tbl(j).Attribute3 := l_networks_rec.Attribute3;
1337 l_op_network_tbl(j).Attribute4 := l_networks_rec.Attribute4;
1338 l_op_network_tbl(j).Attribute5 := l_networks_rec.Attribute5;
1339 l_op_network_tbl(j).Attribute6 := l_networks_rec.Attribute6;
1340 l_op_network_tbl(j).Attribute7 := l_networks_rec.Attribute7;
1341 l_op_network_tbl(j).Attribute8 := l_networks_rec.Attribute8;
1342 l_op_network_tbl(j).Attribute9 := l_networks_rec.Attribute9;
1343 l_op_network_tbl(j).Attribute10 := l_networks_rec.Attribute10;
1344 l_op_network_tbl(j).Attribute11 := l_networks_rec.Attribute11;
1345 l_op_network_tbl(j).Attribute12 := l_networks_rec.Attribute12;
1346 l_op_network_tbl(j).Attribute13 := l_networks_rec.Attribute13;
1347 l_op_network_tbl(j).Attribute14 := l_networks_rec.Attribute14;
1348 l_op_network_tbl(j).Attribute15 := l_networks_rec.Attribute15;
1349 l_op_network_tbl(j).Original_System_Reference := l_networks_rec.Original_System_Reference;
1350 l_op_network_tbl(j).Transaction_Type := l_networks_rec.Transaction_Type;
1351 l_op_network_tbl(j).Return_Status := l_networks_rec.Return_Status;
1352
1353 if l_op_network_tbl(j).from_start_effective_date < l_today then
1354 l_op_network_tbl(j).from_start_effective_date := l_today;
1355 l_op_network_tbl(j).new_from_start_effective_date := l_today;
1356 end if;
1357 if l_op_network_tbl(j).to_start_effective_date < l_today then
1358 l_op_network_tbl(j).to_start_effective_date := l_today;
1359 l_op_network_tbl(j).new_to_start_effective_date := l_today;
1360 end if;
1361 j := j + 1;
1362 END LOOP;
1363 CLOSE c_networks;
1364
1365 error_handler.initialize;
1366
1367 Bom_Rtg_Pub.Process_Rtg( p_rtg_header_rec =>l_rtg_header_rec
1368 ,p_rtg_revision_tbl =>l_rtg_revision_tbl
1369 ,p_operation_tbl =>l_operation_tbl
1370 ,p_op_resource_tbl =>l_op_resource_tbl
1371 ,p_sub_resource_tbl =>l_sub_resource_tbl
1372 ,p_op_network_tbl =>l_op_network_tbl
1373 ,x_rtg_header_rec =>o_rtg_header_rec
1374 ,x_rtg_revision_tbl =>o_rtg_revision_tbl
1375 ,x_operation_tbl =>o_operation_tbl
1376 ,x_op_resource_tbl =>o_op_resource_tbl
1377 ,x_sub_resource_tbl =>o_sub_resource_tbl
1378 ,x_op_network_tbl =>o_op_network_tbl
1379 ,x_return_status =>o_return_status
1380 ,x_msg_count =>o_msg_count
1381 ,p_debug =>'Y'
1382 ,p_output_dir => '/slot/ems2862/oracle/db/tech_st/10.2.0/temp'
1383 ,p_debug_filename =>'BOM_BO_Debug.log');
1384
1385 if (o_return_status <> 'S') then
1386 retcode := 1;
1387 fnd_message.set_name('FLM','FLM_RTG_BOM_API_FAILED');
1388 fnd_message.set_token('MSG_COUNT',error_handler.get_message_count);
1389 errbuf := fnd_message.get;
1390 if (G_LOG_ON) then
1391 FND_FILE.PUT_LINE(FND_FILE.LOG, errbuf || ' - ' || l_assembly_item_name);
1392 while o_msg_count > 0 loop
1393 error_handler.get_message(l_msg,l_index,l_id,l_type);
1394 FND_FILE.PUT_LINE(FND_FILE.LOG,l_type||' - '||l_msg||' - '||l_id);
1395 o_msg_count := o_msg_count - 1;
1396 end loop;
1397 end if;
1398 ROLLBACK TO SAVEPOINT copy_std;
1399 elsif (o_msg_count > 0) then
1400 if (G_LOG_ON) then
1401 while o_msg_count > 0 loop
1402 error_handler.get_message(l_msg,l_index,l_id,l_type);
1403 FND_FILE.PUT_LINE(FND_FILE.LOG,l_type||' - '||l_msg||' - '||l_id);
1404 o_msg_count := o_msg_count - 1;
1405 end loop;
1406 end if;
1407 end if;
1408
1409 -- copy BOM if source exists and destination doesn't and
1410 -- the assembly is not a product family (p_mode <> 2)
1411 if (o_return_status = 'S' and nvl(p_copy_bom,'N') = 'Y' and p_mode = 1) then
1412 if (not bill_exists(l_rtg_tbl(i).assembly_item_id,p_organization_id,p_alternate_code_to)) then
1413 Open c_bill_sequence(l_rtg_tbl(i).assembly_item_id,p_organization_id,p_alternate_code_from);
1414 Fetch c_bill_sequence into l_bom_rec;
1415 if (c_bill_sequence%NOTFOUND) then
1416 l_from_sequence_id := NULL;
1417 else
1418 l_from_sequence_id := l_bom_rec.bill_sequence_id;
1419 l_to_common_seq_id := l_bom_rec.common_bill_sequence_id;
1420 end if;
1421 Close c_bill_sequence;
1422 if (l_from_sequence_id is not NULL) then
1423 if (G_LOG_ON) then
1424 FND_FILE.PUT_LINE(FND_FILE.LOG,'Copy bill: '||
1425 l_assembly_item_name||' ('||p_alternate_code_from||' -> '||p_alternate_code_to||')');
1426 end if;
1427
1428 l_to_sequence_id := null;
1429
1430 bom_bill_of_matls_pkg.Insert_Row(
1431 X_Rowid =>new_row_id
1432 ,X_Assembly_Item_Id =>l_bom_rec.assembly_item_id
1433 ,X_Organization_Id =>l_bom_rec.organization_id
1434 ,X_Alternate_Bom_Designator =>p_alternate_code_to
1435 ,X_Last_Update_Date =>sysdate
1436 ,X_Last_Updated_By =>fnd_global.user_id
1437 ,X_Creation_Date =>sysdate
1438 ,X_Created_By =>fnd_global.user_id
1439 ,X_Last_Update_Login =>fnd_global.login_id
1440 ,X_Common_Assembly_Item_Id =>l_bom_rec.common_assembly_item_id
1441 ,X_Specific_Assembly_Comment =>l_bom_rec.Specific_Assembly_Comment
1442 ,X_Pending_From_Ecn =>l_bom_rec.Pending_From_Ecn
1443 ,X_Attribute_Category =>l_bom_rec.attribute_category
1444 ,X_Attribute1 =>l_bom_rec.attribute1
1445 ,X_Attribute2 =>l_bom_rec.attribute2
1446 ,X_Attribute3 =>l_bom_rec.attribute3
1447 ,X_Attribute4 =>l_bom_rec.attribute4
1448 ,X_Attribute5 =>l_bom_rec.attribute5
1449 ,X_Attribute6 =>l_bom_rec.attribute6
1450 ,X_Attribute7 =>l_bom_rec.attribute7
1451 ,X_Attribute8 =>l_bom_rec.attribute8
1452 ,X_Attribute9 =>l_bom_rec.attribute9
1453 ,X_Attribute10 =>l_bom_rec.attribute10
1454 ,X_Attribute11 =>l_bom_rec.attribute11
1455 ,X_Attribute12 =>l_bom_rec.attribute12
1456 ,X_Attribute13 =>l_bom_rec.attribute13
1457 ,X_Attribute14 =>l_bom_rec.attribute14
1458 ,X_Attribute15 =>l_bom_rec.attribute15
1459 ,X_Assembly_Type =>l_bom_rec.assembly_type
1460 ,X_Common_Bill_Sequence_Id =>l_to_common_seq_id
1461 ,X_Bill_Sequence_Id =>l_to_sequence_id
1462 ,X_Common_Organization_Id =>l_bom_rec.Common_Organization_Id
1463 ,X_Next_Explode_Date =>l_bom_rec.Next_Explode_Date);
1464 bom_copy_bill.copy_bill(
1465 to_sequence_id =>l_to_sequence_id
1466 ,from_sequence_id =>l_from_sequence_id
1467 ,from_org_id =>p_organization_id
1468 ,to_org_id =>p_organization_id
1469 ,display_option =>3 -- current+future
1470 ,user_id =>fnd_global.user_id
1471 ,to_item_id =>l_rtg_tbl(i).assembly_item_id
1472 ,direction =>1
1473 ,to_alternate =>p_alternate_code_to
1474 ,rev_date =>sysdate
1475 ,e_change_notice =>NULL
1476 ,rev_item_seq_id =>NULL
1477 ,bill_or_eco =>1
1478 ,eco_eff_date =>NULL
1479 ,eco_unit_number =>NULL
1480 ,unit_number =>NULL
1481 ,from_item_id =>l_rtg_tbl(i).assembly_item_id);
1482 end if;
1483 end if;
1484 end if;
1485
1486 -- both RTG and/or BOM copied, then copy attachments
1487 if (o_return_status = 'S') then
1488 if l_2_ids.COUNT > 0 then
1489 copy_attach('BOM_STANDARD_OPERATIONS','BOM_STANDARD_OPERATIONS',l_2_ids);
1490 end if;
1491 if a_operation_tbl.COUNT > 0 then
1492 l_new_rtg_seq_id := null;
1493 OPEN c_new_rtg_seq_id(l_rtg_tbl(i).assembly_item_id,p_organization_id,p_alternate_code_to);
1494 FETCH c_new_rtg_seq_id INTO l_new_rtg_seq_id;
1495 if c_new_rtg_seq_id%NOTFOUND then
1496 l_new_rtg_seq_id := null;
1497 end if;
1498 CLOSE c_new_rtg_seq_id;
1499 if (l_new_rtg_seq_id is NOT NULL) then
1500 ii := a_operation_tbl.FIRST;
1501 jj := a_operation_tbl.LAST;
1502 LOOP
1503 l_old_op_seq_id := null;
1504 l_new_op_seq_id := null;
1505 OPEN c_op_seq_id( l_rtg_tbl(i).routing_sequence_id,a_operation_tbl(ii).operation_type,
1506 a_operation_tbl(ii).operation_sequence_number);
1507
1508 FETCH c_op_seq_id into l_old_op_seq_id;
1509 IF c_op_seq_id%NOTFOUND THEN
1510 l_old_op_seq_id := null;
1511 END IF;
1512 CLOSE c_op_seq_id;
1513
1514 OPEN c_op_seq_id( l_new_rtg_seq_id,a_operation_tbl(ii).operation_type,a_operation_tbl(ii).operation_sequence_number);
1515 FETCH c_op_seq_id into l_new_op_seq_id;
1516 IF c_op_seq_id%NOTFOUND THEN
1517 l_new_op_seq_id := null;
1518 END IF;
1519 CLOSE c_op_seq_id;
1520 if (l_old_op_seq_id is not null and l_new_op_seq_id is not null) then
1521 l_2_seq_ids(l_old_op_seq_id).old_id := l_old_op_seq_id;
1522 l_2_seq_ids(l_old_op_seq_id).new_id := l_new_op_seq_id;
1523 end if;
1524 EXIT WHEN ii = jj;
1525 ii := a_operation_tbl.NEXT(ii);
1526 END LOOP;
1527 end if;
1528 end if;
1529 if l_2_seq_ids.COUNT > 0 then
1530 copy_attach('BOM_OPERATION_SEQUENCES','BOM_OPERATION_SEQUENCES',l_2_seq_ids);
1531 end if;
1532 success := success + 1;
1533 end if;
1534 --Added skip till here for bugfix:8416058
1535 <<SKIP_TILL_HERE>>
1536 EXIT WHEN i = last;
1537 i := l_rtg_tbl.NEXT(i);
1538 END LOOP;
1539 if (success = 0) then
1540 retcode := 2;
1541 fnd_message.set_name('FLM', 'FLM_RTG_COPY_NONE');
1542 fnd_message.set_token('RTG_TOTAL',l_rtg_total);
1543 errbuf := fnd_message.get;
1544 if (G_LOG_ON) then
1545 FND_FILE.PUT_LINE(FND_FILE.LOG, errbuf);
1546 end if;
1547 rollback;
1548 else
1549 fnd_message.set_name('FLM', 'FLM_RTG_COPY_DONE');
1550 fnd_message.set_token('RTG_COUNT', success);
1551 fnd_message.set_token('RTG_TOTAL',l_rtg_total);
1552 errbuf := fnd_message.get;
1553 if (G_LOG_ON) then
1554 FND_FILE.PUT_LINE(FND_FILE.LOG, errbuf);
1555 end if;
1556 commit;
1557 end if;
1558 EXCEPTION
1559 WHEN OTHERS THEN
1560 retcode := 2;
1561 errbuf := 'Exception - ' || substr(SQLERRM,1,200) || ' (' || SQLCODE || ')';
1562 if (G_LOG_ON) then
1563 FND_FILE.PUT_LINE(FND_FILE.LOG, errbuf);
1564 end if;
1565 rollback;
1566 return;
1567 End copy_routings;
1568
1569 END flm_copy_routing;