[Home] [Help]
PACKAGE BODY: APPS.BOM_RTG_ISETUP_IMP
Source
4 --
1 PACKAGE BODY BOM_RTG_ISETUP_IMP AS
2 /* $Header: BOMRTSTB.pls 120.4.12020000.2 2012/12/27 14:10:43 rambkond ship $ */
3 /***************************************************************************
5 -- Copyright (c) 1996 Oracle Corporation, Redwood Shores, CA, USA
6 -- All rights reserved.
7 --
8 -- FILENAME
9 --
10 -- BOMRTSTB.pls
11 --
12 -- DESCRIPTION
13 --
14 -- Body of package BOM_RTG_ISETUP_IMP
15 --
16 -- NOTES
17 --
18 -- HISTORY
19 --
20 -- 18-NOV-02 M V M P Tilak Initial Creation
21 -- 05-DEC-03 M V M P Tilak Modified for substitute_group_num changes
22 dones to the Routing Business Object API
23 as well as the Routing form modifications.
24 schedule_sequence_number is no more a
25 mandatory field in the table.
26 Added this new column substitutegroupnumber
27 in the temp tables and using that in the
28 corr. cursors.
29 -- 20-JUL-04 M V M P Tilak Modified the cursor rtg_sub_op_res_CUR
30 for bug#3776173.
31 ***************************************************************************/
32
33 PROCEDURE IMPORT_ROUTING(P_debug IN VARCHAR2 := 'N',
34 P_output_dir IN VARCHAR2 := NULL,
35 P_debug_filename IN VARCHAR2 := 'BOM_BO_debug.log',
36 P_rtg_hdr_XML IN CLOB,
37 P_rtg_rev_XML IN CLOB,
38 P_rtg_op_XML IN CLOB,
39 P_rtg_op_res_XML IN CLOB,
40 P_rtg_sub_op_res_XML IN CLOB,
41 P_rtg_op_network_XML IN CLOB,
42 X_return_status OUT NOCOPY VARCHAR2,
46 insCtx DBMS_XMLSave.ctxType;
43 X_msg_count OUT NOCOPY NUMBER,
44 X_G_msg_data OUT NOCOPY LONG) IS
45
47 rows NUMBER;
48 ename_v VARCHAR2(20);
49
50 l_rtg_header_rec Bom_Rtg_Pub.Rtg_Header_Rec_Type
51 := Bom_Rtg_Pub.G_MISS_RTG_HEADER_REC;
52 l_rtg_revision_tbl Bom_Rtg_Pub.Rtg_Revision_Tbl_Type
53 := Bom_Rtg_Pub.G_MISS_RTG_REVISION_TBL;
54 l_operation_tbl Bom_Rtg_Pub.Operation_Tbl_Type
55 := Bom_Rtg_Pub.G_MISS_OPERATION_TBL;
56 l_op_resource_tbl Bom_Rtg_Pub.Op_Resource_Tbl_Type
57 := Bom_Rtg_Pub.G_MISS_OP_RESOURCE_TBL;
58 l_sub_resource_tbl Bom_Rtg_Pub.Sub_Resource_Tbl_Type
59 := Bom_Rtg_Pub.G_MISS_SUB_RESOURCE_TBL;
60 l_op_network_tbl Bom_Rtg_Pub.Op_Network_Tbl_Type
61 := Bom_Rtg_Pub.G_MISS_OP_NETWORK_TBL;
62 x_rtg_header_rec Bom_Rtg_Pub.Rtg_Header_Rec_Type
63 := Bom_Rtg_Pub.G_MISS_RTG_HEADER_REC;
64 x_rtg_revision_tbl Bom_Rtg_Pub.Rtg_Revision_Tbl_Type
65 :=Bom_Rtg_Pub.G_MISS_RTG_REVISION_TBL;
66 x_operation_tbl Bom_Rtg_Pub.Operation_Tbl_Type
67 := Bom_Rtg_Pub.G_MISS_OPERATION_TBL;
68 x_op_resource_tbl Bom_Rtg_Pub.Op_Resource_Tbl_Type
69 := Bom_Rtg_Pub.G_MISS_OP_RESOURCE_TBL;
70 x_sub_resource_tbl Bom_Rtg_Pub.Sub_Resource_Tbl_Type
71 := Bom_Rtg_Pub.G_MISS_SUB_RESOURCE_TBL;
72 x_op_network_tbl Bom_Rtg_Pub.Op_Network_Tbl_Type
73 := Bom_Rtg_Pub.G_MISS_OP_NETWORK_TBL;
74
75 CURSOR rtg_hdr_CUR IS
76 SELECT tmp.AssemblyItemName
77 , tmp.OrganizationCode
78 , tmp.AlternateRoutingCode
79 , tmp.EngRoutingFlag
80 , tmp.CommonAssemblyItemName
81 , tmp.RoutingComment
82 , tmp.CompletionSubinventory
83 , tmp.CompletionLocationName
84 , tmp.LineCode
85 , tmp.CFMRoutingFlag
86 , tmp.MixedModelMapFlag
87 , tmp.Priority
88 , TO_NUMBER(tmp.TotalCycleTime)
89 , tmp.CTPFlag
90 , tmp.Attributecategory
91 , tmp.Attribute1
92 , tmp.Attribute2
93 , tmp.Attribute3
94 , tmp.Attribute4
95 , tmp.Attribute5
96 , tmp.Attribute6
97 , tmp.Attribute7
98 , tmp.Attribute8
99 , tmp.Attribute9
100 , tmp.Attribute10
101 , tmp.Attribute11
102 , tmp.Attribute12
103 , tmp.Attribute13
104 , tmp.Attribute14
105 , tmp.Attribute15
106 , tmp.OriginalSystemReference
107 , 'CREATE' Transaction_Type
108 , NULL Return_Status
109 , NULL Delete_Group_Name
110 , NULL DG_Description
111 , NULL ser_start_op_seq
112 , NULL row_identifier
113 FROM bom_routing_header_temp tmp,
114 mtl_parameters org,
115 mtl_system_items_kfv item
116 WHERE org.organization_code = tmp.OrganizationCode
117 AND item.organization_id = org.organization_id
118 AND item.concatenated_segments = tmp.AssemblyItemName
119 AND NOT EXISTS (SELECT 1
120 FROM bom_operational_routings rtg
121 WHERE rtg.assembly_item_id = item.inventory_item_id
122 AND rtg.organization_id = item.organization_id
123 AND NVL(rtg.alternate_routing_designator,'$$##$$') = NVL(tmp.AlternateRoutingCode,'$$##$$'))
124 UNION ALL
125 SELECT tmp.AssemblyItemName
126 , tmp.OrganizationCode
127 , tmp.AlternateRoutingCode
128 , tmp.EngRoutingFlag
129 , tmp.CommonAssemblyItemName
130 , tmp.RoutingComment
131 , tmp.CompletionSubinventory
132 , tmp.CompletionLocationName
133 , tmp.LineCode
134 , tmp.CFMRoutingFlag
135 , tmp.MixedModelMapFlag
136 , tmp.Priority
137 , TO_NUMBER(tmp.TotalCycleTime)
138 , tmp.CTPFlag
139 , tmp.Attributecategory
140 , tmp.Attribute1
141 , tmp.Attribute2
142 , tmp.Attribute3
143 , tmp.Attribute4
144 , tmp.Attribute5
145 , tmp.Attribute6
146 , tmp.Attribute7
147 , tmp.Attribute8
148 , tmp.Attribute9
149 , tmp.Attribute10
150 , tmp.Attribute11
151 , tmp.Attribute12
152 , tmp.Attribute13
153 , tmp.Attribute14
154 , tmp.Attribute15
158 , NULL Delete_Group_Name
155 , tmp.OriginalSystemReference
156 , 'UPDATE' Transaction_Type
157 , NULL Return_Status
159 , NULL DG_Description
160 , NULL ser_start_op_seq
161 , NULL row_identifier
162 FROM bom_routing_header_temp tmp,
163 mtl_parameters org,
164 mtl_system_items_kfv item
165 WHERE org.organization_code = tmp.OrganizationCode
166 AND item.organization_id = org.organization_id
167 AND item.concatenated_segments = tmp.AssemblyItemName
168 AND EXISTS (SELECT 1
169 FROM bom_operational_routings rtg
170 WHERE rtg.assembly_item_id = item.inventory_item_id
171 AND rtg.organization_id = item.organization_id
172 AND NVL(rtg.alternate_routing_designator,'$$##$$') = NVL(tmp.AlternateRoutingCode,'$$##$$'));
173
174 CURSOR rtg_rev_CUR(P_assembly_item_name VARCHAR2,
175 P_organization_code VARCHAR2) IS
176 SELECT tmp.AssemblyItemName
177 , tmp.OrganizationCode
178 , NULL Alternate_Routing_Code
179 , tmp.Revision
180 , TO_DATE(tmp.StartEffectiveDate,'YYYY-MM-DD HH24:MI:SS')
181 , tmp.AttributeCategory
182 , tmp.Attribute1
183 , tmp.Attribute2
184 , tmp.Attribute3
185 , tmp.Attribute4
186 , tmp.Attribute5
187 , tmp.Attribute6
188 , tmp.Attribute7
189 , tmp.Attribute8
190 , tmp.Attribute9
191 , tmp.Attribute10
192 , tmp.Attribute11
193 , tmp.Attribute12
194 , tmp.Attribute13
195 , tmp.Attribute14
196 , tmp.Attribute15
197 , tmp.OriginalSystemReference
198 , 'CREATE' transaction_type
199 , NULL return_status
200 , NULL row_identifier
201 FROM bom_rtg_revisions_temp tmp,
202 mtl_parameters org,
203 mtl_system_items_kfv item
204 WHERE org.organization_code = tmp.OrganizationCode
205 AND item.organization_id = org.organization_id
206 AND item.concatenated_segments = tmp.AssemblyItemName
207 AND tmp.AssemblyItemName = P_assembly_item_name
208 AND tmp.OrganizationCode = P_organization_code
209 AND NOT EXISTS (SELECT 1
213 AND rev.process_revision = tmp.revision)
210 FROM mtl_rtg_item_revisions rev
211 WHERE rev.organization_id = item.organization_id
212 AND rev.inventory_item_id = item.inventory_item_id
214 UNION ALL
215 SELECT tmp.AssemblyItemName
216 , tmp.OrganizationCode
217 , NULL Alternate_Routing_Code
218 , tmp.Revision
219 , TO_DATE(tmp.StartEffectiveDate,'YYYY-MM-DD HH24:MI:SS')
220 , tmp.AttributeCategory
221 , tmp.Attribute1
222 , tmp.Attribute2
223 , tmp.Attribute3
224 , tmp.Attribute4
225 , tmp.Attribute5
226 , tmp.Attribute6
227 , tmp.Attribute7
228 , tmp.Attribute8
229 , tmp.Attribute9
230 , tmp.Attribute10
231 , tmp.Attribute11
232 , tmp.Attribute12
233 , tmp.Attribute13
234 , tmp.Attribute14
235 , tmp.Attribute15
236 , tmp.OriginalSystemReference
237 , 'UPDATE' transaction_type
238 , NULL return_status
239 , NULL row_identifier
240 FROM bom_rtg_revisions_temp tmp,
241 mtl_parameters org,
242 mtl_system_items_kfv item
243 WHERE org.organization_code = tmp.OrganizationCode
244 AND item.organization_id = org.organization_id
245 AND item.concatenated_segments = tmp.AssemblyItemName
246 AND tmp.AssemblyItemName = P_assembly_item_name
247 AND tmp.OrganizationCode = P_organization_code
248 AND EXISTS (SELECT 1
249 FROM mtl_rtg_item_revisions rev
250 WHERE rev.organization_id = item.organization_id
251 AND rev.inventory_item_id = item.inventory_item_id
252 AND rev.process_revision = tmp.revision);
253
254 CURSOR rtg_op_CUR(P_assembly_item_name VARCHAR2,
255 P_organization_code VARCHAR2,
256 P_alternate_routing_code VARCHAR2) IS
257 SELECT tmp.AssemblyItemName
258 , tmp.OrganizationCode
259 , tmp.AlternateRoutingCode
260 , tmp.OperationSequenceNumber
261 , tmp.OperationType
262 , TO_DATE(tmp.StartEffectiveDate,'YYYY-MM-DD HH24:MI:SS')
263 , tmp.NewOperationSequenceNumber
264 , TO_DATE(tmp.NewStartEffectiveDate,'YYYY-MM-DD HH24:MI:SS')
265 , tmp.StandardOperationCode
266 , tmp.DepartmentCode
267 , TO_NUMBER(tmp.OpLeadTimePercent)
268 , TO_NUMBER(tmp.MinimumTransferQuantity)
269 , tmp.CountPointType
270 , tmp.OperationDescription
271 , TO_DATE(tmp.DisableDate,'YYYY-MM-DD HH24:MI:SS')
272 , tmp.BackflushFlag
273 , tmp.OptionDependentFlag
274 , tmp.ReferenceFlag
275 , tmp.ProcessSeqNumber
276 , tmp.ProcessCode
277 , tmp.LineOpSeqNumber
278 , tmp.LineOpCode
279 , tmp.Yield
280 , tmp.CumulativeYield
281 , TO_NUMBER(tmp.ReverseCUMYield)
282 , tmp.UserLaborTime
283 , tmp.UserMachineTime
284 , tmp.NetPlanningPercent
285 , tmp.IncludeInRollup
286 , tmp.OpYieldEnabledFlag
287 , tmp.ShutdownType
288 , tmp.Attributecategory
289 , tmp.Attribute1
290 , tmp.Attribute2
291 , tmp.Attribute3
292 , tmp.Attribute4
293 , tmp.Attribute5
294 , tmp.Attribute6
295 , tmp.Attribute7
296 , tmp.Attribute8
297 , tmp.Attribute9
298 , tmp.Attribute10
299 , tmp.Attribute11
300 , tmp.Attribute12
301 , tmp.Attribute13
302 , tmp.Attribute14
303 , tmp.Attribute15
304 , tmp.OriginalSystemReference
305 , 'UPDATE' Transaction_Type
306 , NULL Return_Status
307 , NULL Delete_Group_Name
308 , NULL DG_Description
309 , NULL long_description
310 , NULL row_identifier
311 FROM bom_routing_operations_temp tmp
312 WHERE tmp.AssemblyItemName = P_assembly_item_name
316 FROM BOM_OPERATION_SEQUENCES oper,
313 AND tmp.OrganizationCode = P_organization_code
314 AND NVL(tmp.AlternateRoutingCode,'$$##$$') = NVL(P_alternate_routing_code,'$$##$$')
315 AND EXISTS (SELECT 1
317 bom_operational_routings rtg,
318 mtl_parameters org,
319 mtl_system_items_kfv item
320 WHERE rtg.routing_sequence_id = oper.routing_sequence_id
321 AND item.inventory_item_id = rtg.assembly_item_id
322 AND item.organization_id = rtg.organization_id
326 AND NVL(rtg.alternate_routing_designator, '##$$##') = NVL(tmp.AlternateRoutingCode, '##$$##')
323 AND item.concatenated_segments = tmp.AssemblyItemName
324 AND org.organization_id = rtg.organization_id
325 AND org.organization_code = tmp.OrganizationCode
327 AND oper.operation_seq_num = tmp.OperationSequenceNumber
328 AND oper.operation_type = tmp.OperationType
329 AND oper.effectivity_date = TO_DATE(tmp.StartEffectiveDate,'YYYY-MM-DD HH24:MI:SS')
330 AND rtg.common_assembly_item_id is null)
331 UNION ALL
332 SELECT tmp.AssemblyItemName
333 , tmp.OrganizationCode
334 , tmp.AlternateRoutingCode
335 , tmp.OperationSequenceNumber
336 , tmp.OperationType
337 , TO_DATE(tmp.StartEffectiveDate,'YYYY-MM-DD HH24:MI:SS')
338 , tmp.NewOperationSequenceNumber
339 , TO_DATE(tmp.NewStartEffectiveDate,'YYYY-MM-DD HH24:MI:SS')
340 , tmp.StandardOperationCode
341 , tmp.DepartmentCode
342 , TO_NUMBER(tmp.OpLeadTimePercent)
343 , TO_NUMBER(tmp.MinimumTransferQuantity)
344 , tmp.CountPointType
345 , tmp.OperationDescription
346 , TO_DATE(tmp.DisableDate,'YYYY-MM-DD HH24:MI:SS')
347 , tmp.BackflushFlag
348 , tmp.OptionDependentFlag
349 , tmp.ReferenceFlag
350 , tmp.ProcessSeqNumber
351 , tmp.ProcessCode
352 , tmp.LineOpSeqNumber
353 , tmp.LineOpCode
354 , tmp.Yield
355 , tmp.CumulativeYield
356 , TO_NUMBER(tmp.ReverseCUMYield)
357 , tmp.UserLaborTime
358 , tmp.UserMachineTime
359 , tmp.NetPlanningPercent
360 , tmp.IncludeInRollup
361 , tmp.OpYieldEnabledFlag
362 , tmp.ShutdownType
363 , tmp.Attributecategory
364 , tmp.Attribute1
365 , tmp.Attribute2
366 , tmp.Attribute3
367 , tmp.Attribute4
368 , tmp.Attribute5
369 , tmp.Attribute6
370 , tmp.Attribute7
371 , tmp.Attribute8
372 , tmp.Attribute9
373 , tmp.Attribute10
374 , tmp.Attribute11
375 , tmp.Attribute12
376 , tmp.Attribute13
377 , tmp.Attribute14
378 , tmp.Attribute15
379 , tmp.OriginalSystemReference
380 , 'CREATE' Transaction_Type
381 , NULL Return_Status
382 , NULL Delete_Group_Name
383 , NULL DG_Description
384 , NULL long_description
385 , NULL row_identifier
386 FROM bom_routing_operations_temp tmp
387 WHERE tmp.AssemblyItemName = P_assembly_item_name
388 AND tmp.OrganizationCode = P_organization_code
389 AND NVL(tmp.AlternateRoutingCode,'$$##$$') = NVL(P_alternate_routing_code,'$$##$$')
390 AND NOT EXISTS (SELECT 1
391 FROM BOM_OPERATION_SEQUENCES oper,
392 bom_operational_routings rtg,
393 mtl_parameters org,
394 mtl_system_items_kfv item
395 WHERE rtg.routing_sequence_id = oper.routing_sequence_id
396 AND item.inventory_item_id = rtg.assembly_item_id
397 AND item.organization_id = rtg.organization_id
398 AND item.concatenated_segments = tmp.AssemblyItemName
399 AND org.organization_id = rtg.organization_id
400 AND org.organization_code = tmp.OrganizationCode
401 AND NVL(rtg.alternate_routing_designator, '##$$##') = NVL(tmp.AlternateRoutingCode, '##$$##')
402 AND oper.operation_seq_num = tmp.OperationSequenceNumber
403 AND oper.operation_type = tmp.OperationType
404 AND oper.effectivity_date = TO_DATE(tmp.StartEffectiveDate,'YYYY-MM-DD HH24:MI:SS'));
405
406 CURSOR rtg_op_res_CUR (P_assembly_item_name VARCHAR2,
407 P_organization_code VARCHAR2,
408 P_alternate_routing_code VARCHAR2) IS
409 SELECT tmp.AssemblyItemName
410 , tmp.OrganizationCode
411 , tmp.AlternateRoutingCode
412 , tmp.OperationSequenceNumber
413 , tmp.OperationType
414 , TO_DATE(tmp.StartEffectiveDate,'YYYY-MM-DD HH24:MI:SS')
415 , null acd_type --Bug 16042107
416 , null eco_name --Bug 16042107
417 , tmp.ResourceSequenceNumber
418 , tmp.ResourceCode
419 , tmp.Activity
420 , tmp.StandardRateFlag
421 , TO_NUMBER(tmp.AssignedUnits)
422 , TO_NUMBER(tmp.UsageRateOrAmount)
423 , TO_NUMBER(tmp.UsageRateOrAmountInverse)
424 , tmp.BasisType
425 , tmp.ScheduleFlag
426 , TO_NUMBER(tmp.ResourceOffsetPercent)
427 , tmp.AutochargeType
428 , TO_NUMBER(tmp.SubstituteGroupNumber)
429 , TO_NUMBER(tmp.ScheduleSequenceNumber)
430 , tmp.PrincipleFlag
431 , tmp.Attributecategory
432 , tmp.Attribute1
433 , tmp.Attribute2
434 , tmp.Attribute3
435 , tmp.Attribute4
436 , tmp.Attribute5
437 , tmp.Attribute6
438 , tmp.Attribute7
439 , tmp.Attribute8
440 , tmp.Attribute9
441 , tmp.Attribute10
442 , tmp.Attribute11
443 , tmp.Attribute12
447 , tmp.OriginalSystemReference
444 , tmp.Attribute13
445 , tmp.Attribute14
446 , tmp.Attribute15
448 , 'CREATE'
449 , NULL
450 , tmp.SetupType
451 , NULL row_identifier
452 FROM bom_rtg_oper_res_temp tmp
453 WHERE tmp.AssemblyItemName = P_assembly_item_name
454 AND tmp.OrganizationCode = P_organization_code
455 AND NVL(tmp.AlternateRoutingCode,'$$##$$') = NVL(P_alternate_routing_code,'$$##$$')
456 AND NOT EXISTS (SELECT 1
460 mtl_parameters org,
457 FROM BOM_OPERATION_RESOURCES oper_res,
458 bom_operation_sequences oper,
459 bom_operational_routings rtg,
461 mtl_system_items_kfv item
462 WHERE oper.operation_sequence_id = oper_res.operation_sequence_id
463 AND rtg.routing_sequence_id = oper.routing_sequence_id
464 AND item.inventory_item_id = rtg.assembly_item_id
465 AND item.organization_id = rtg.organization_id
466 AND item.concatenated_segments = tmp.AssemblyItemName
467 AND org.organization_id = rtg.organization_id
468 AND org.organization_code = tmp.OrganizationCode
469 AND NVL(rtg.alternate_routing_designator, '##$$##') = NVL(tmp.AlternateRoutingCode, '##$$##')
470 AND oper.operation_seq_num = tmp.OperationSequenceNumber
471 AND oper.operation_type = tmp.OperationType
472 AND oper.effectivity_date = TO_DATE(tmp.StartEffectiveDate,'YYYY-MM-DD HH24:MI:SS')
473 AND oper_res.resource_seq_num = tmp.ResourceSequenceNumber)
474 UNION ALL
475 SELECT tmp.AssemblyItemName
476 , tmp.OrganizationCode
477 , tmp.AlternateRoutingCode
478 , tmp.OperationSequenceNumber
479 , tmp.OperationType
480 , TO_DATE(tmp.StartEffectiveDate,'YYYY-MM-DD HH24:MI:SS')
481 , null acd_type --Bug 16042107
482 , null eco_name --Bug 16042107
483 , tmp.ResourceSequenceNumber
484 , tmp.ResourceCode
485 , tmp.Activity
486 , tmp.StandardRateFlag
487 , TO_NUMBER(tmp.AssignedUnits)
488 , TO_NUMBER(tmp.UsageRateOrAmount)
489 , TO_NUMBER(tmp.UsageRateOrAmountInverse)
490 , tmp.BasisType
491 , tmp.ScheduleFlag
492 , TO_NUMBER(tmp.ResourceOffsetPercent)
493 , tmp.AutochargeType
494 , TO_NUMBER(tmp.SubstituteGroupNumber)
495 , TO_NUMBER(tmp.ScheduleSequenceNumber)
496 , tmp.PrincipleFlag
497 , tmp.Attributecategory
498 , tmp.Attribute1
499 , tmp.Attribute2
500 , tmp.Attribute3
501 , tmp.Attribute4
502 , tmp.Attribute5
503 , tmp.Attribute6
504 , tmp.Attribute7
505 , tmp.Attribute8
506 , tmp.Attribute9
507 , tmp.Attribute10
508 , tmp.Attribute11
509 , tmp.Attribute12
510 , tmp.Attribute13
511 , tmp.Attribute14
512 , tmp.Attribute15
513 , tmp.OriginalSystemReference
514 , 'UPDATE'
515 , NULL
516 , tmp.SetupType
517 , NULL row_identifier
518 FROM bom_rtg_oper_res_temp tmp
519 WHERE tmp.AssemblyItemName = P_assembly_item_name
520 AND tmp.OrganizationCode = P_organization_code
521 AND NVL(tmp.AlternateRoutingCode,'$$##$$') = NVL(P_alternate_routing_code,'$$##$$')
522 AND EXISTS (SELECT 1
523 FROM BOM_OPERATION_RESOURCES oper_res,
524 bom_operation_sequences oper,
525 bom_operational_routings rtg,
526 mtl_parameters org,
527 mtl_system_items_kfv item
528 WHERE oper.operation_sequence_id = oper_res.operation_sequence_id
529 AND rtg.routing_sequence_id = oper.routing_sequence_id
530 AND item.inventory_item_id = rtg.assembly_item_id
531 AND item.organization_id = rtg.organization_id
532 AND item.concatenated_segments = tmp.AssemblyItemName
533 AND org.organization_id = rtg.organization_id
534 AND org.organization_code = tmp.OrganizationCode
535 AND NVL(rtg.alternate_routing_designator, '##$$##') = NVL(tmp.AlternateRoutingCode, '##$$##')
539 AND oper_res.resource_seq_num = tmp.ResourceSequenceNumber
536 AND oper.operation_seq_num = tmp.OperationSequenceNumber
537 AND oper.operation_type = tmp.OperationType
538 AND oper.effectivity_date = TO_DATE(tmp.StartEffectiveDate,'YYYY-MM-DD HH24:MI:SS')
540 AND rtg.common_assembly_item_id is null);
541
542 CURSOR rtg_sub_op_res_CUR(P_assembly_item_name VARCHAR2,
543 P_organization_code VARCHAR2,
544 P_alternate_routing_code VARCHAR2) IS
545 SELECT tmp.AssemblyItemName
546 , tmp.OrganizationCode
547 , tmp.AlternateRoutingCode
548 , tmp.OperationSequenceNumber
549 , tmp.OperationType
550 , TO_DATE(tmp.StartEffectiveDate,'YYYY-MM-DD HH24:MI:SS')
551 , tmp.SubResourceCode
552 , tmp.NewSubResourceCode
553 , TO_NUMBER(tmp.SubstituteGroupNumber)
554 , TO_NUMBER(tmp.ScheduleSequenceNumber)
555 , tmp.ReplacementGroupNumber
556 --Added the following line for bug3776173 START
557 , TO_NUMBER(NULL) NewReplacementGroupNumber
558 --Bug3776173 END
559 , tmp.Activity
560 , tmp.StandardRateFlag
561 , TO_NUMBER(tmp.AssignedUnits)
562 , TO_NUMBER(tmp.UsageRateOrAmount)
563 , TO_NUMBER(tmp.UsageRateOrAmountInverse)
564 , tmp.BasisType
565 , TO_NUMBER(NULL) NewBasisType /* Added for 4689856 */
566 , tmp.ScheduleFlag
567 , TO_NUMBER(NULL) NewScheduleFlag /* Added for bug 13005178 */
568 , TO_NUMBER(tmp.ResourceOffsetPercent)
569 , tmp.AutochargeType
570 , tmp.PrincipleFlag
571 , tmp.Attributecategory
572 , tmp.Attribute1
573 , tmp.Attribute2
574 , tmp.Attribute3
575 , tmp.Attribute4
576 , tmp.Attribute5
577 , tmp.Attribute6
578 , tmp.Attribute7
579 , tmp.Attribute8
580 , tmp.Attribute9
581 , tmp.Attribute10
582 , tmp.Attribute11
583 , tmp.Attribute12
584 , tmp.Attribute13
585 , tmp.Attribute14
586 , tmp.Attribute15
587 , tmp.OriginalSystemReference
588 , 'UPDATE'
589 , NULL
590 , tmp.SetupType
591 , NULL row_identifier
592 FROM bom_sub_oper_resources_temp tmp
593 WHERE tmp.AssemblyItemName = P_assembly_item_name
594 AND tmp.OrganizationCode = P_organization_code
595 AND NVL(tmp.AlternateRoutingCode,'$$##$$') = NVL(P_alternate_routing_code,'$$##$$')
596 AND EXISTS (SELECT 1
597 FROM BOM_SUB_OPERATION_RESOURCES sub_oper,
598 bom_operational_routings rtg,
599 BOM_OPERATION_SEQUENCES oper,
600 mtl_parameters org,
601 mtl_system_items_kfv item
602 WHERE oper.operation_sequence_id = sub_oper.operation_sequence_id
603 AND rtg.routing_sequence_id = oper.routing_sequence_id
604 AND item.inventory_item_id = rtg.assembly_item_id
605 AND item.organization_id = rtg.organization_id
606 AND item.concatenated_segments = tmp.AssemblyItemName
607 AND org.organization_id = rtg.organization_id
608 AND org.organization_code = tmp.OrganizationCode
609 AND NVL(rtg.alternate_routing_designator, '##$$##') = NVL(tmp.AlternateRoutingCode, '##$$##')
610 AND oper.operation_seq_num = tmp.OperationSequenceNumber
611 AND oper.operation_type = tmp.OperationType
612 AND oper.effectivity_date = TO_DATE(tmp.StartEffectiveDate,'YYYY-MM-DD HH24:MI:SS')
613 AND NVL(sub_oper.schedule_seq_num,-99999) = NVL(tmp.ScheduleSequenceNumber,-99999)
614 AND sub_oper.substitute_group_num = tmp.SubstituteGroupNumber
615 AND rtg.common_assembly_item_id is null)
616 UNION ALL
617 SELECT tmp.AssemblyItemName
618 , tmp.OrganizationCode
619 , tmp.AlternateRoutingCode
620 , tmp.OperationSequenceNumber
621 , tmp.OperationType
622 , TO_DATE(tmp.StartEffectiveDate,'YYYY-MM-DD HH24:MI:SS')
623 , tmp.SubResourceCode
624 , tmp.NewSubResourceCode
625 , TO_NUMBER(tmp.SubstituteGroupNumber)
626 , TO_NUMBER(tmp.ScheduleSequenceNumber)
627 , TO_NUMBER(tmp.ReplacementGroupNumber)
628 --Added the following line for Bug3776173 START
629 , TO_NUMBER(NULL) NewReplacementGroupNumber
630 --Bug3776173 END
631 , tmp.Activity
632 , tmp.StandardRateFlag
633 , TO_NUMBER(tmp.AssignedUnits)
634 , TO_NUMBER(tmp.UsageRateOrAmount)
635 , TO_NUMBER(tmp.UsageRateOrAmountInverse)
636 , tmp.BasisType
637 , TO_NUMBER(NULL) NewBasisType /* Added for 4689856 */
638 , tmp.ScheduleFlag
639 , TO_NUMBER(NULL) NewScheduleFlag /* Added for bug 13005178 */
640 , TO_NUMBER(tmp.ResourceOffsetPercent)
641 , tmp.AutochargeType
642 , tmp.PrincipleFlag
643 , tmp.Attributecategory
644 , tmp.Attribute1
645 , tmp.Attribute2
646 , tmp.Attribute3
647 , tmp.Attribute4
651 , tmp.Attribute8
648 , tmp.Attribute5
649 , tmp.Attribute6
650 , tmp.Attribute7
652 , tmp.Attribute9
653 , tmp.Attribute10
654 , tmp.Attribute11
655 , tmp.Attribute12
656 , tmp.Attribute13
657 , tmp.Attribute14
658 , tmp.Attribute15
659 , tmp.OriginalSystemReference
660 , 'CREATE'
661 , NULL
662 , tmp.SetupType
663 , NULL row_identifier
664 FROM bom_sub_oper_resources_temp tmp
665 WHERE tmp.AssemblyItemName = P_assembly_item_name
666 AND tmp.OrganizationCode = P_organization_code
667 AND NVL(tmp.AlternateRoutingCode,'$$##$$') = NVL(P_alternate_routing_code,'$$##$$')
668 AND NOT EXISTS (SELECT 1
669 FROM BOM_SUB_OPERATION_RESOURCES sub_oper,
670 bom_operational_routings rtg,
671 BOM_OPERATION_SEQUENCES oper,
672 mtl_parameters org,
673 mtl_system_items_kfv item
674 WHERE oper.operation_sequence_id = sub_oper.operation_sequence_id
675 AND rtg.routing_sequence_id = oper.routing_sequence_id
676 AND item.inventory_item_id = rtg.assembly_item_id
677 AND item.organization_id = rtg.organization_id
678 AND item.concatenated_segments = tmp.AssemblyItemName
679 AND org.organization_id = rtg.organization_id
680 AND org.organization_code = tmp.OrganizationCode
681 AND NVL(rtg.alternate_routing_designator, '##$$##') = NVL(tmp.AlternateRoutingCode, '##$$##')
682 AND oper.operation_seq_num = tmp.OperationSequenceNumber
683 AND oper.operation_type = tmp.OperationType
684 AND oper.effectivity_date = TO_DATE(tmp.StartEffectiveDate,'YYYY-MM-DD HH24:MI:SS')
685 AND NVL(sub_oper.schedule_seq_num,-99999) = NVL(tmp.ScheduleSequenceNumber,-99999)
686 AND sub_oper.substitute_group_num = tmp.SubstituteGroupNumber);
687
688 -- BOM_OPERATION_NETWORKS
689 -- From_Op_Seq_Id
690 -- To_Op_Seq_Id
691
692 CURSOR rtg_op_networks_CUR(P_assembly_item_name VARCHAR2,
693 P_organization_code VARCHAR2,
694 P_alternate_routing_code VARCHAR2) IS
695 SELECT tmp.AssemblyItemName
696 , tmp.OrganizationCode
697 , tmp.AlternateRoutingCode
698 , tmp.OperationType
699 , tmp.FromOpSeqNumber
700 , tmp.FromXCoordinate
701 , tmp.FromYCoordinate
702 , TO_DATE(tmp.FromStartEffectiveDate,'YYYY-MM-DD HH24:MI:SS')
703 , tmp.ToOpSeqNumber
704 , tmp.ToXCoordinate
705 , tmp.ToYCoordinate
706 , TO_DATE(tmp.ToStartEffectiveDate,'YYYY-MM-DD HH24:MI:SS')
707 , tmp.NewFromOpSeqNumber
708 , TO_DATE(tmp.NewFromStartEffectiveDate,'YYYY-MM-DD HH24:MI:SS')
709 , tmp.NewToOpSeqNumber
710 , TO_DATE(tmp.NewToStartEffectiveDate,'YYYY-MM-DD HH24:MI:SS')
711 , tmp.ConnectionType
712 , TO_NUMBER(tmp.PlanningPercent)
713 , tmp.Attributecategory
714 , tmp.Attribute1
715 , tmp.Attribute2
716 , tmp.Attribute3
717 , tmp.Attribute4
718 , tmp.Attribute5
719 , tmp.Attribute6
720 , tmp.Attribute7
721 , tmp.Attribute8
722 , tmp.Attribute9
726 , tmp.Attribute13
723 , tmp.Attribute10
724 , tmp.Attribute11
725 , tmp.Attribute12
727 , tmp.Attribute14
728 , tmp.Attribute15
729 , tmp.OriginalSystemReference
730 , 'CREATE' transaction_type
731 , NULL return_status
732 , NULL row_identifier
733 FROM bom_oper_networks_temp tmp
734 WHERE tmp.AssemblyItemName = P_assembly_item_name
735 AND tmp.OrganizationCode = P_organization_code
736 AND NVL(tmp.AlternateRoutingCode,'$$##$$') = NVL(P_alternate_routing_code,'$$##$$')
737 AND NOT EXISTS (SELECT 1
738 FROM bom_operation_networks op_network,
739 bom_operational_routings rtg,
740 bom_operation_sequences oper1,
741 bom_operation_sequences oper2,
742 mtl_parameters org,
746 AND item.inventory_item_id = rtg.assembly_item_id
743 mtl_system_items_kfv item
744 WHERE op_network.from_op_seq_id = oper1.operation_sequence_id
745 AND op_network.to_op_seq_id = oper2.operation_sequence_id
747 AND item.organization_id = rtg.organization_id
748 AND item.concatenated_segments = tmp.AssemblyItemName
749 AND org.organization_id = rtg.organization_id
750 AND org.organization_code = tmp.OrganizationCode
751 AND NVL(rtg.alternate_routing_designator, '##$$##') = NVL(tmp.AlternateRoutingCode,'##$$##')
752 AND oper1.routing_sequence_id = rtg.routing_sequence_id
753 AND oper1.operation_seq_num = tmp.FromOpSeqNumber
754 AND oper1.operation_type = tmp.OperationType
755 AND oper1.effectivity_date = TO_DATE(tmp.FromStartEffectiveDate,'YYYY-MM-DD HH24:MI:SS')
756 AND oper2.routing_sequence_id = rtg.routing_sequence_id
757 AND oper2.operation_seq_num = tmp.ToOpSeqNumber
758 AND oper2.operation_type = tmp.OperationType
759 AND oper2.effectivity_date = TO_DATE(tmp.ToStartEffectiveDate,'YYYY-MM-DD HH24:MI:SS'))
760 UNION ALL
761 SELECT tmp.AssemblyItemName
762 , tmp.OrganizationCode
763 , tmp.AlternateRoutingCode
764 , tmp.OperationType
765 , tmp.FromOpSeqNumber
766 , tmp.FromXCoordinate
767 , tmp.FromYCoordinate
768 , TO_DATE(tmp.FromStartEffectiveDate,'YYYY-MM-DD HH24:MI:SS')
769 , tmp.ToOpSeqNumber
770 , tmp.ToXCoordinate
771 , tmp.ToYCoordinate
772 , TO_DATE(tmp.ToStartEffectiveDate,'YYYY-MM-DD HH24:MI:SS')
773 , tmp.NewFromOpSeqNumber
774 , TO_DATE(tmp.NewFromStartEffectiveDate,'YYYY-MM-DD HH24:MI:SS')
775 , tmp.NewToOpSeqNumber
776 , TO_DATE(tmp.NewToStartEffectiveDate,'YYYY-MM-DD HH24:MI:SS')
777 , tmp.ConnectionType
778 , TO_NUMBER(tmp.PlanningPercent)
779 , tmp.Attributecategory
780 , tmp.Attribute1
781 , tmp.Attribute2
782 , tmp.Attribute3
783 , tmp.Attribute4
784 , tmp.Attribute5
785 , tmp.Attribute6
786 , tmp.Attribute7
787 , tmp.Attribute8
788 , tmp.Attribute9
789 , tmp.Attribute10
790 , tmp.Attribute11
791 , tmp.Attribute12
792 , tmp.Attribute13
793 , tmp.Attribute14
794 , tmp.Attribute15
795 , tmp.OriginalSystemReference
796 , 'UPDATE' transaction_type
797 , NULL return_status
798 , NULL row_identifier
799 FROM bom_oper_networks_temp tmp
800 WHERE tmp.AssemblyItemName = P_assembly_item_name
801 AND tmp.OrganizationCode = P_organization_code
802 AND NVL(tmp.AlternateRoutingCode,'$$##$$') = NVL(P_alternate_routing_code,'$$##$$')
803 AND EXISTS (SELECT 1
804 FROM bom_operation_networks op_network,
805 bom_operational_routings rtg,
806 bom_operation_sequences oper1,
807 bom_operation_sequences oper2,
808 mtl_parameters org,
809 mtl_system_items_kfv item
810 WHERE op_network.from_op_seq_id = oper1.operation_sequence_id
811 AND op_network.to_op_seq_id = oper2.operation_sequence_id
812 AND item.inventory_item_id = rtg.assembly_item_id
813 AND item.organization_id = rtg.organization_id
814 AND item.concatenated_segments = tmp.AssemblyItemName
815 AND org.organization_id = rtg.organization_id
816 AND org.organization_code = tmp.OrganizationCode
817 AND NVL(rtg.alternate_routing_designator, '##$$##') = NVL(tmp.AlternateRoutingCode,'##$$##')
818 AND oper1.routing_sequence_id = rtg.routing_sequence_id
819 AND oper1.operation_seq_num = tmp.FromOpSeqNumber
820 AND oper1.operation_type = tmp.OperationType
821 AND oper1.effectivity_date = TO_DATE(tmp.FromStartEffectiveDate,'YYYY-MM-DD HH24:MI:SS')
822 AND oper2.routing_sequence_id = rtg.routing_sequence_id
823 AND oper2.operation_seq_num = tmp.ToOpSeqNumber
824 AND oper2.operation_type = tmp.OperationType
825 AND oper2.effectivity_date = TO_DATE(tmp.ToStartEffectiveDate,'YYYY-MM-DD HH24:MI:SS')
826 AND rtg.common_assembly_item_id is null);
827 i NUMBER;
828 -- Following variables are used for logging messages
829 l_error_tbl ERROR_HANDLER.ERROR_TBL_TYPE;
830 l_message VARCHAR2(2000) := NULL;
831 l_entity VARCHAR2(3) := NULL;
832 l_msg_index NUMBER;
833 l_message_type VARCHAR2(1);
834 hdr_cnt NUMBER := 0;
835
836 BEGIN
837 -- Routing Header Record
838
839 IF (p_rtg_hdr_XML IS NULL) THEN
840 FND_MESSAGE.SET_NAME('BOM', 'BOM_SETUP_NO_ROWS');
841 FND_MESSAGE.RETRIEVE(X_G_msg_data);
842 ELSE
843 -- get the context handle
844 insCtx := DBMS_XMLSave.newContext('BOM_ROUTING_HEADER_TEMP');
850 -- this closes the handle
845 DBMS_XMLSave.setIgnoreCase(insCtx, 1);
846 DBMS_XMLSave.setDateFormat(insCtx, 'yyyy-MM-dd HH:mm:ss');
847 DBMS_XMLSave.setRowTag(insCtx , 'OperationalRoutingsVO');
848 -- this inserts the document
849 rows := DBMS_XMLSave.insertXML(insCtx,P_rtg_hdr_XML);
851 DBMS_XMLSave.closeContext(insCtx);
852 -- Routing Revisions Table
853 IF (P_rtg_rev_XML IS NOT NULL) THEN
854 -- get the context handle
855 insCtx := DBMS_XMLSave.newContext('BOM_RTG_REVISIONS_TEMP');
856 DBMS_XMLSave.setIgnoreCase(insCtx, 1);
857 DBMS_XMLSave.setDateFormat(insCtx, 'yyyy-MM-dd HH:mm:ss');
858 DBMS_XMLSave.setRowTag(insCtx , 'RoutingRevisionsVO');
859 -- this inserts the document
860 rows := DBMS_XMLSave.insertXML(insCtx,P_rtg_rev_XML);
861 -- this closes the handle
862 DBMS_XMLSave.closeContext(insCtx);
863 END IF;
864
865 -- Routing Operations Table
866 IF (P_rtg_op_XML IS NOT NULL) THEN
867 -- get the context handle
868 insCtx := DBMS_XMLSave.newContext('BOM_ROUTING_OPERATIONS_TEMP');
869 DBMS_XMLSave.setIgnoreCase(insCtx, 1);
870 DBMS_XMLSave.setDateFormat(insCtx, 'yyyy-MM-dd HH:mm:ss');
871 DBMS_XMLSave.setRowTag(insCtx , 'OperationSequencesVO');
872 -- this inserts the document
873 rows := DBMS_XMLSave.insertXML(insCtx,P_rtg_op_XML);
874 -- this closes the handle
875 DBMS_XMLSave.closeContext(insCtx);
876 END IF;
877
878 -- Routing Operation Resources Table
879 IF (P_rtg_op_res_XML IS NOT NULL) THEN
880 -- get the context handle
881 insCtx := DBMS_XMLSave.newContext('BOM_RTG_OPER_RES_TEMP');
882 DBMS_XMLSave.setIgnoreCase(insCtx, 1);
883 DBMS_XMLSave.setDateFormat(insCtx, 'yyyy-MM-dd HH:mm:ss');
884 DBMS_XMLSave.setRowTag(insCtx , 'OperationResourcesVO');
885 -- this inserts the document
886 rows := DBMS_XMLSave.insertXML(insCtx,P_rtg_op_res_XML);
887 -- this closes the handle
888 DBMS_XMLSave.closeContext(insCtx);
889 END IF;
890
891 -- Routing Substitute Operation Resources Table
892 IF (p_rtg_sub_op_res_XML IS NOT NULL) THEN
893 -- get the context handle
894 insCtx := DBMS_XMLSave.newContext('BOM_SUB_OPER_RESOURCES_TEMP');
895 DBMS_XMLSave.setIgnoreCase(insCtx, 1);
896 DBMS_XMLSave.setDateFormat(insCtx, 'yyyy-MM-dd HH:mm:ss');
897 DBMS_XMLSave.setRowTag(insCtx , 'SubstituteOperationResourcesVO');
898 -- this inserts the document
899 rows := DBMS_XMLSave.insertXML(insCtx,P_rtg_sub_op_res_XML);
900 -- this closes the handle
901 DBMS_XMLSave.closeContext(insCtx);
902 END IF;
903
904 -- Routing Operation Networks Table
905 IF (P_rtg_op_network_XML IS NOT NULL) THEN
906 -- get the context handle
907 insCtx := DBMS_XMLSave.newContext('BOM_OPER_NETWORKS_TEMP');
908 DBMS_XMLSave.setIgnoreCase(insCtx, 1);
909 DBMS_XMLSave.setDateFormat(insCtx, 'yyyy-MM-dd HH:mm:ss');
910 DBMS_XMLSave.setRowTag(insCtx , 'OperationNetworksVO');
911 -- this inserts the document
912 rows := DBMS_XMLSave.insertXML(insCtx,P_rtg_op_network_XML);
913 -- this closes the handle
914 DBMS_XMLSave.closeContext(insCtx);
915 END IF;
916
917 OPEN rtg_hdr_CUR;
918 LOOP
919 FETCH rtg_hdr_CUR INTO l_rtg_header_rec;
920 IF (rtg_hdr_CUR%NOTFOUND) THEN
921 EXIT;
922 END IF;
923 hdr_cnt := hdr_cnt + 1;
924
925 l_rtg_revision_tbl.DELETE;
926 l_operation_tbl.DELETE;
927 l_op_resource_tbl.DELETE;
928 l_sub_resource_tbl.DELETE;
929 l_op_network_tbl.DELETE;
930
931 OPEN rtg_rev_CUR(l_rtg_header_rec.Assembly_Item_Name,
932 l_rtg_header_rec.Organization_Code);
933 i := 1;
934 LOOP
935 FETCH rtg_rev_CUR INTO l_rtg_revision_tbl(i);
936 IF (rtg_rev_CUR%NOTFOUND) THEN
937 EXIT;
938 END IF;
939 i := i + 1;
940 END LOOP;
941
942 CLOSE rtg_rev_CUR;
943
944 OPEN rtg_op_CUR(l_rtg_header_rec.Assembly_Item_Name,
945 l_rtg_header_rec.Organization_Code,
946 l_rtg_header_rec.Alternate_Routing_Code);
947 i := 1;
948 LOOP
949 FETCH rtg_op_CUR INTO l_operation_tbl(i);
950 IF (rtg_op_CUR%NOTFOUND) THEN
951 EXIT;
952 END IF;
953 i := i + 1;
954 END LOOP;
955 CLOSE rtg_op_CUR;
956
957 OPEN rtg_op_res_CUR(l_rtg_header_rec.Assembly_Item_Name,
958 l_rtg_header_rec.Organization_Code,
959 l_rtg_header_rec.Alternate_Routing_Code);
960 i := 1;
961 LOOP
965 END IF;
962 FETCH rtg_op_res_CUR INTO l_op_resource_tbl(i);
963 IF (rtg_op_res_CUR%NOTFOUND) THEN
964 EXIT;
966 i := i + 1;
967 END LOOP;
968 CLOSE rtg_op_res_CUR;
969 OPEN rtg_sub_op_res_CUR(l_rtg_header_rec.Assembly_Item_Name,
970 l_rtg_header_rec.Organization_Code,
971 l_rtg_header_rec.Alternate_Routing_Code);
972 i := 1;
973 LOOP
974 FETCH rtg_sub_op_res_CUR INTO l_sub_resource_tbl(i);
975 IF (rtg_sub_op_res_CUR%NOTFOUND) THEN
976 EXIT;
977 END IF;
978 i := i + 1;
979 END LOOP;
980 CLOSE rtg_sub_op_res_CUR;
981
982 OPEN rtg_op_networks_CUR(l_rtg_header_rec.Assembly_Item_Name,
983 l_rtg_header_rec.Organization_Code,
984 l_rtg_header_rec.Alternate_Routing_Code);
985 i := 1;
986 LOOP
987 FETCH rtg_op_networks_CUR INTO l_op_network_tbl(i);
988 IF (rtg_op_networks_CUR%NOTFOUND) THEN
989 EXIT;
990 END IF;
991 i := i + 1;
992 END LOOP;
993 CLOSE rtg_op_networks_CUR;
994 BOM_GLOBALS.Set_Caller_Type('MIGRATION');
995 ERROR_HANDLER.Initialize;
996 BOM_RTG_PUB.Process_RTG(p_rtg_header_rec => l_rtg_header_rec
997 , p_rtg_revision_tbl => l_rtg_revision_tbl
998 , p_operation_tbl => l_operation_tbl
999 , p_op_resource_tbl => l_op_resource_tbl
1000 , p_sub_resource_tbl => l_sub_resource_tbl
1001 , p_op_network_tbl => l_op_network_tbl
1002 , p_debug => P_debug
1003 , p_output_dir => P_output_dir
1004 , p_debug_filename => P_debug_filename
1005 , x_rtg_header_rec => X_rtg_header_rec
1006 , x_rtg_revision_tbl => X_rtg_revision_tbl
1007 , x_operation_tbl => X_operation_tbl
1011 , x_return_status => X_return_status
1008 , x_op_resource_tbl => X_op_resource_tbl
1009 , x_sub_resource_tbl => X_sub_resource_tbl
1010 , x_op_network_tbl => X_op_network_tbl
1012 , x_msg_count => X_msg_count);
1013
1014 FOR i IN 1..X_msg_count LOOP
1015 BEGIN
1016 ERROR_HANDLER.Get_Message(x_entity_index => l_msg_index,
1017 x_entity_id => l_entity,
1018 x_message_text => l_message,
1019 x_message_type => l_message_type);
1020 X_G_msg_data := X_G_msg_data || FND_GLOBAL.NewLine || FND_GLOBAL.NewLine || TO_CHAR(l_msg_index) || ': '||l_entity ||': '|| l_message_type ||': '||l_message;
1021 EXCEPTION
1022 WHEN OTHERS THEN
1023 EXIT;
1024 END;
1025 END LOOP;
1026 END LOOP;
1027 IF (hdr_cnt = 0) THEN -- There are no records to be processed. RETURN with Error Message
1028 FND_MESSAGE.SET_NAME('BOM', 'BOM_SETUP_NO_ROWS');
1029 FND_MESSAGE.RETRIEVE(X_G_msg_data);
1030 END IF;
1031 END IF;
1032 END Import_Routing;
1033
1034 END BOM_RTG_ISETUP_IMP;