[Home] [Help]
PACKAGE BODY: APPS.BOM_RTG_ISETUP_IMP
Source
1 PACKAGE BODY BOM_RTG_ISETUP_IMP AS
2 /* $Header: BOMRTSTB.pls 120.3 2006/02/28 03:47:23 grastogi noship $ */
3 /***************************************************************************
4 --
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,
43 X_msg_count OUT NOCOPY NUMBER,
44 X_G_msg_data OUT NOCOPY LONG) IS
45
46 insCtx DBMS_XMLSave.ctxType;
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
155 , tmp.OriginalSystemReference
156 , 'UPDATE' Transaction_Type
157 , NULL Return_Status
158 , NULL Delete_Group_Name
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
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
213 AND rev.process_revision = tmp.revision)
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
313 AND tmp.OrganizationCode = P_organization_code
314 AND NVL(tmp.AlternateRoutingCode,'$$##$$') = NVL(P_alternate_routing_code,'$$##$$')
315 AND EXISTS (SELECT 1
316 FROM BOM_OPERATION_SEQUENCES oper,
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
323 AND item.concatenated_segments = tmp.AssemblyItemName
324 AND org.organization_id = rtg.organization_id
325 AND org.organization_code = tmp.OrganizationCode
326 AND NVL(rtg.alternate_routing_designator, '##$$##') = NVL(tmp.AlternateRoutingCode, '##$$##')
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
375 , tmp.Attribute12
372 , tmp.Attribute9
373 , tmp.Attribute10
374 , tmp.Attribute11
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 , tmp.ResourceSequenceNumber
416 , tmp.ResourceCode
417 , tmp.Activity
418 , tmp.StandardRateFlag
419 , TO_NUMBER(tmp.AssignedUnits)
420 , TO_NUMBER(tmp.UsageRateOrAmount)
421 , TO_NUMBER(tmp.UsageRateOrAmountInverse)
422 , tmp.BasisType
423 , tmp.ScheduleFlag
424 , TO_NUMBER(tmp.ResourceOffsetPercent)
425 , tmp.AutochargeType
426 , TO_NUMBER(tmp.SubstituteGroupNumber)
427 , TO_NUMBER(tmp.ScheduleSequenceNumber)
428 , tmp.PrincipleFlag
429 , tmp.Attributecategory
430 , tmp.Attribute1
431 , tmp.Attribute2
432 , tmp.Attribute3
433 , tmp.Attribute4
434 , tmp.Attribute5
435 , tmp.Attribute6
436 , tmp.Attribute7
437 , tmp.Attribute8
438 , tmp.Attribute9
439 , tmp.Attribute10
440 , tmp.Attribute11
441 , tmp.Attribute12
442 , tmp.Attribute13
443 , tmp.Attribute14
444 , tmp.Attribute15
445 , tmp.OriginalSystemReference
446 , 'CREATE'
447 , NULL
448 , tmp.SetupType
449 , NULL row_identifier
450 FROM bom_rtg_oper_res_temp tmp
451 WHERE tmp.AssemblyItemName = P_assembly_item_name
452 AND tmp.OrganizationCode = P_organization_code
453 AND NVL(tmp.AlternateRoutingCode,'$$##$$') = NVL(P_alternate_routing_code,'$$##$$')
454 AND NOT EXISTS (SELECT 1
455 FROM BOM_OPERATION_RESOURCES oper_res,
456 bom_operation_sequences oper,
457 bom_operational_routings rtg,
458 mtl_parameters org,
459 mtl_system_items_kfv item
460 WHERE oper.operation_sequence_id = oper_res.operation_sequence_id
461 AND rtg.routing_sequence_id = oper.routing_sequence_id
462 AND item.inventory_item_id = rtg.assembly_item_id
463 AND item.organization_id = rtg.organization_id
464 AND item.concatenated_segments = tmp.AssemblyItemName
465 AND org.organization_id = rtg.organization_id
466 AND org.organization_code = tmp.OrganizationCode
467 AND NVL(rtg.alternate_routing_designator, '##$$##') = NVL(tmp.AlternateRoutingCode, '##$$##')
468 AND oper.operation_seq_num = tmp.OperationSequenceNumber
469 AND oper.operation_type = tmp.OperationType
470 AND oper.effectivity_date = TO_DATE(tmp.StartEffectiveDate,'YYYY-MM-DD HH24:MI:SS')
471 AND oper_res.resource_seq_num = tmp.ResourceSequenceNumber)
472 UNION ALL
473 SELECT tmp.AssemblyItemName
474 , tmp.OrganizationCode
475 , tmp.AlternateRoutingCode
476 , tmp.OperationSequenceNumber
477 , tmp.OperationType
478 , TO_DATE(tmp.StartEffectiveDate,'YYYY-MM-DD HH24:MI:SS')
479 , tmp.ResourceSequenceNumber
483 , TO_NUMBER(tmp.AssignedUnits)
480 , tmp.ResourceCode
481 , tmp.Activity
482 , tmp.StandardRateFlag
484 , TO_NUMBER(tmp.UsageRateOrAmount)
485 , TO_NUMBER(tmp.UsageRateOrAmountInverse)
486 , tmp.BasisType
487 , tmp.ScheduleFlag
488 , TO_NUMBER(tmp.ResourceOffsetPercent)
489 , tmp.AutochargeType
490 , TO_NUMBER(tmp.SubstituteGroupNumber)
491 , TO_NUMBER(tmp.ScheduleSequenceNumber)
492 , tmp.PrincipleFlag
493 , tmp.Attributecategory
494 , tmp.Attribute1
495 , tmp.Attribute2
496 , tmp.Attribute3
497 , tmp.Attribute4
498 , tmp.Attribute5
499 , tmp.Attribute6
500 , tmp.Attribute7
501 , tmp.Attribute8
502 , tmp.Attribute9
503 , tmp.Attribute10
504 , tmp.Attribute11
505 , tmp.Attribute12
506 , tmp.Attribute13
507 , tmp.Attribute14
508 , tmp.Attribute15
509 , tmp.OriginalSystemReference
510 , 'UPDATE'
511 , NULL
512 , tmp.SetupType
513 , NULL row_identifier
514 FROM bom_rtg_oper_res_temp tmp
515 WHERE tmp.AssemblyItemName = P_assembly_item_name
516 AND tmp.OrganizationCode = P_organization_code
517 AND NVL(tmp.AlternateRoutingCode,'$$##$$') = NVL(P_alternate_routing_code,'$$##$$')
518 AND EXISTS (SELECT 1
519 FROM BOM_OPERATION_RESOURCES oper_res,
520 bom_operation_sequences oper,
521 bom_operational_routings rtg,
522 mtl_parameters org,
523 mtl_system_items_kfv item
524 WHERE oper.operation_sequence_id = oper_res.operation_sequence_id
525 AND rtg.routing_sequence_id = oper.routing_sequence_id
526 AND item.inventory_item_id = rtg.assembly_item_id
527 AND item.organization_id = rtg.organization_id
528 AND item.concatenated_segments = tmp.AssemblyItemName
529 AND org.organization_id = rtg.organization_id
530 AND org.organization_code = tmp.OrganizationCode
531 AND NVL(rtg.alternate_routing_designator, '##$$##') = NVL(tmp.AlternateRoutingCode, '##$$##')
532 AND oper.operation_seq_num = tmp.OperationSequenceNumber
533 AND oper.operation_type = tmp.OperationType
534 AND oper.effectivity_date = TO_DATE(tmp.StartEffectiveDate,'YYYY-MM-DD HH24:MI:SS')
535 AND oper_res.resource_seq_num = tmp.ResourceSequenceNumber
536 AND rtg.common_assembly_item_id is null);
537
538 CURSOR rtg_sub_op_res_CUR(P_assembly_item_name VARCHAR2,
539 P_organization_code VARCHAR2,
540 P_alternate_routing_code VARCHAR2) IS
541 SELECT tmp.AssemblyItemName
542 , tmp.OrganizationCode
543 , tmp.AlternateRoutingCode
544 , tmp.OperationSequenceNumber
545 , tmp.OperationType
546 , TO_DATE(tmp.StartEffectiveDate,'YYYY-MM-DD HH24:MI:SS')
547 , tmp.SubResourceCode
548 , tmp.NewSubResourceCode
549 , TO_NUMBER(tmp.SubstituteGroupNumber)
550 , TO_NUMBER(tmp.ScheduleSequenceNumber)
551 , tmp.ReplacementGroupNumber
552 --Added the following line for bug3776173 START
553 , TO_NUMBER(NULL) NewReplacementGroupNumber
554 --Bug3776173 END
555 , tmp.Activity
556 , tmp.StandardRateFlag
557 , TO_NUMBER(tmp.AssignedUnits)
558 , TO_NUMBER(tmp.UsageRateOrAmount)
559 , TO_NUMBER(tmp.UsageRateOrAmountInverse)
560 , tmp.BasisType
561 , TO_NUMBER(NULL) NewBasisType /* Added for 4689856 */
562 , tmp.ScheduleFlag
563 , TO_NUMBER(tmp.ResourceOffsetPercent)
564 , tmp.AutochargeType
565 , tmp.PrincipleFlag
566 , tmp.Attributecategory
567 , tmp.Attribute1
568 , tmp.Attribute2
569 , tmp.Attribute3
570 , tmp.Attribute4
571 , tmp.Attribute5
572 , tmp.Attribute6
573 , tmp.Attribute7
574 , tmp.Attribute8
575 , tmp.Attribute9
576 , tmp.Attribute10
577 , tmp.Attribute11
578 , tmp.Attribute12
579 , tmp.Attribute13
580 , tmp.Attribute14
581 , tmp.Attribute15
582 , tmp.OriginalSystemReference
583 , 'UPDATE'
584 , NULL
585 , tmp.SetupType
586 , NULL row_identifier
587 FROM bom_sub_oper_resources_temp tmp
588 WHERE tmp.AssemblyItemName = P_assembly_item_name
589 AND tmp.OrganizationCode = P_organization_code
590 AND NVL(tmp.AlternateRoutingCode,'$$##$$') = NVL(P_alternate_routing_code,'$$##$$')
591 AND EXISTS (SELECT 1
592 FROM BOM_SUB_OPERATION_RESOURCES sub_oper,
593 bom_operational_routings rtg,
594 BOM_OPERATION_SEQUENCES oper,
595 mtl_parameters org,
599 AND item.inventory_item_id = rtg.assembly_item_id
596 mtl_system_items_kfv item
597 WHERE oper.operation_sequence_id = sub_oper.operation_sequence_id
598 AND rtg.routing_sequence_id = oper.routing_sequence_id
600 AND item.organization_id = rtg.organization_id
601 AND item.concatenated_segments = tmp.AssemblyItemName
602 AND org.organization_id = rtg.organization_id
603 AND org.organization_code = tmp.OrganizationCode
604 AND NVL(rtg.alternate_routing_designator, '##$$##') = NVL(tmp.AlternateRoutingCode, '##$$##')
605 AND oper.operation_seq_num = tmp.OperationSequenceNumber
606 AND oper.operation_type = tmp.OperationType
607 AND oper.effectivity_date = TO_DATE(tmp.StartEffectiveDate,'YYYY-MM-DD HH24:MI:SS')
608 AND NVL(sub_oper.schedule_seq_num,-99999) = NVL(tmp.ScheduleSequenceNumber,-99999)
609 AND sub_oper.substitute_group_num = tmp.SubstituteGroupNumber
610 AND rtg.common_assembly_item_id is null)
611 UNION ALL
612 SELECT tmp.AssemblyItemName
613 , tmp.OrganizationCode
614 , tmp.AlternateRoutingCode
615 , tmp.OperationSequenceNumber
616 , tmp.OperationType
617 , TO_DATE(tmp.StartEffectiveDate,'YYYY-MM-DD HH24:MI:SS')
618 , tmp.SubResourceCode
619 , tmp.NewSubResourceCode
620 , TO_NUMBER(tmp.SubstituteGroupNumber)
621 , TO_NUMBER(tmp.ScheduleSequenceNumber)
622 , TO_NUMBER(tmp.ReplacementGroupNumber)
623 --Added the following line for Bug3776173 START
624 , TO_NUMBER(NULL) NewReplacementGroupNumber
625 --Bug3776173 END
626 , tmp.Activity
627 , tmp.StandardRateFlag
628 , TO_NUMBER(tmp.AssignedUnits)
629 , TO_NUMBER(tmp.UsageRateOrAmount)
630 , TO_NUMBER(tmp.UsageRateOrAmountInverse)
631 , tmp.BasisType
632 , TO_NUMBER(NULL) NewBasisType /* Added for 4689856 */
633 , tmp.ScheduleFlag
634 , TO_NUMBER(tmp.ResourceOffsetPercent)
635 , tmp.AutochargeType
636 , tmp.PrincipleFlag
637 , tmp.Attributecategory
638 , tmp.Attribute1
639 , tmp.Attribute2
640 , tmp.Attribute3
641 , tmp.Attribute4
642 , tmp.Attribute5
643 , tmp.Attribute6
644 , tmp.Attribute7
645 , tmp.Attribute8
646 , tmp.Attribute9
647 , tmp.Attribute10
648 , tmp.Attribute11
649 , tmp.Attribute12
650 , tmp.Attribute13
651 , tmp.Attribute14
652 , tmp.Attribute15
653 , tmp.OriginalSystemReference
654 , 'CREATE'
655 , NULL
656 , tmp.SetupType
657 , NULL row_identifier
658 FROM bom_sub_oper_resources_temp tmp
659 WHERE tmp.AssemblyItemName = P_assembly_item_name
660 AND tmp.OrganizationCode = P_organization_code
661 AND NVL(tmp.AlternateRoutingCode,'$$##$$') = NVL(P_alternate_routing_code,'$$##$$')
662 AND NOT EXISTS (SELECT 1
663 FROM BOM_SUB_OPERATION_RESOURCES sub_oper,
664 bom_operational_routings rtg,
665 BOM_OPERATION_SEQUENCES oper,
666 mtl_parameters org,
667 mtl_system_items_kfv item
668 WHERE oper.operation_sequence_id = sub_oper.operation_sequence_id
669 AND rtg.routing_sequence_id = oper.routing_sequence_id
670 AND item.inventory_item_id = rtg.assembly_item_id
671 AND item.organization_id = rtg.organization_id
672 AND item.concatenated_segments = tmp.AssemblyItemName
673 AND org.organization_id = rtg.organization_id
674 AND org.organization_code = tmp.OrganizationCode
675 AND NVL(rtg.alternate_routing_designator, '##$$##') = NVL(tmp.AlternateRoutingCode, '##$$##')
676 AND oper.operation_seq_num = tmp.OperationSequenceNumber
677 AND oper.operation_type = tmp.OperationType
678 AND oper.effectivity_date = TO_DATE(tmp.StartEffectiveDate,'YYYY-MM-DD HH24:MI:SS')
679 AND NVL(sub_oper.schedule_seq_num,-99999) = NVL(tmp.ScheduleSequenceNumber,-99999)
680 AND sub_oper.substitute_group_num = tmp.SubstituteGroupNumber);
681
682 -- BOM_OPERATION_NETWORKS
683 -- From_Op_Seq_Id
684 -- To_Op_Seq_Id
685
686 CURSOR rtg_op_networks_CUR(P_assembly_item_name VARCHAR2,
687 P_organization_code VARCHAR2,
688 P_alternate_routing_code VARCHAR2) IS
689 SELECT tmp.AssemblyItemName
690 , tmp.OrganizationCode
691 , tmp.AlternateRoutingCode
692 , tmp.OperationType
693 , tmp.FromOpSeqNumber
694 , tmp.FromXCoordinate
695 , tmp.FromYCoordinate
696 , TO_DATE(tmp.FromStartEffectiveDate,'YYYY-MM-DD HH24:MI:SS')
697 , tmp.ToOpSeqNumber
698 , tmp.ToXCoordinate
699 , tmp.ToYCoordinate
703 , tmp.NewToOpSeqNumber
700 , TO_DATE(tmp.ToStartEffectiveDate,'YYYY-MM-DD HH24:MI:SS')
701 , tmp.NewFromOpSeqNumber
702 , TO_DATE(tmp.NewFromStartEffectiveDate,'YYYY-MM-DD HH24:MI:SS')
704 , TO_DATE(tmp.NewToStartEffectiveDate,'YYYY-MM-DD HH24:MI:SS')
705 , tmp.ConnectionType
706 , TO_NUMBER(tmp.PlanningPercent)
707 , tmp.Attributecategory
708 , tmp.Attribute1
709 , tmp.Attribute2
710 , tmp.Attribute3
711 , tmp.Attribute4
712 , tmp.Attribute5
713 , tmp.Attribute6
714 , tmp.Attribute7
715 , tmp.Attribute8
716 , tmp.Attribute9
717 , tmp.Attribute10
718 , tmp.Attribute11
719 , tmp.Attribute12
720 , tmp.Attribute13
721 , tmp.Attribute14
722 , tmp.Attribute15
723 , tmp.OriginalSystemReference
724 , 'CREATE' transaction_type
725 , NULL return_status
726 , NULL row_identifier
727 FROM bom_oper_networks_temp tmp
728 WHERE tmp.AssemblyItemName = P_assembly_item_name
729 AND tmp.OrganizationCode = P_organization_code
730 AND NVL(tmp.AlternateRoutingCode,'$$##$$') = NVL(P_alternate_routing_code,'$$##$$')
731 AND NOT EXISTS (SELECT 1
732 FROM bom_operation_networks op_network,
733 bom_operational_routings rtg,
734 bom_operation_sequences oper1,
735 bom_operation_sequences oper2,
736 mtl_parameters org,
737 mtl_system_items_kfv item
738 WHERE op_network.from_op_seq_id = oper1.operation_sequence_id
739 AND op_network.to_op_seq_id = oper2.operation_sequence_id
740 AND item.inventory_item_id = rtg.assembly_item_id
741 AND item.organization_id = rtg.organization_id
742 AND item.concatenated_segments = tmp.AssemblyItemName
743 AND org.organization_id = rtg.organization_id
744 AND org.organization_code = tmp.OrganizationCode
745 AND NVL(rtg.alternate_routing_designator, '##$$##') = NVL(tmp.AlternateRoutingCode,'##$$##')
746 AND oper1.routing_sequence_id = rtg.routing_sequence_id
747 AND oper1.operation_seq_num = tmp.FromOpSeqNumber
748 AND oper1.operation_type = tmp.OperationType
749 AND oper1.effectivity_date = TO_DATE(tmp.FromStartEffectiveDate,'YYYY-MM-DD HH24:MI:SS')
750 AND oper2.routing_sequence_id = rtg.routing_sequence_id
751 AND oper2.operation_seq_num = tmp.ToOpSeqNumber
752 AND oper2.operation_type = tmp.OperationType
753 AND oper2.effectivity_date = TO_DATE(tmp.ToStartEffectiveDate,'YYYY-MM-DD HH24:MI:SS'))
754 UNION ALL
755 SELECT tmp.AssemblyItemName
756 , tmp.OrganizationCode
757 , tmp.AlternateRoutingCode
758 , tmp.OperationType
759 , tmp.FromOpSeqNumber
760 , tmp.FromXCoordinate
761 , tmp.FromYCoordinate
762 , TO_DATE(tmp.FromStartEffectiveDate,'YYYY-MM-DD HH24:MI:SS')
763 , tmp.ToOpSeqNumber
764 , tmp.ToXCoordinate
765 , tmp.ToYCoordinate
766 , TO_DATE(tmp.ToStartEffectiveDate,'YYYY-MM-DD HH24:MI:SS')
767 , tmp.NewFromOpSeqNumber
768 , TO_DATE(tmp.NewFromStartEffectiveDate,'YYYY-MM-DD HH24:MI:SS')
769 , tmp.NewToOpSeqNumber
770 , TO_DATE(tmp.NewToStartEffectiveDate,'YYYY-MM-DD HH24:MI:SS')
771 , tmp.ConnectionType
772 , TO_NUMBER(tmp.PlanningPercent)
773 , tmp.Attributecategory
774 , tmp.Attribute1
775 , tmp.Attribute2
776 , tmp.Attribute3
777 , tmp.Attribute4
778 , tmp.Attribute5
779 , tmp.Attribute6
780 , tmp.Attribute7
781 , tmp.Attribute8
782 , tmp.Attribute9
783 , tmp.Attribute10
784 , tmp.Attribute11
785 , tmp.Attribute12
786 , tmp.Attribute13
787 , tmp.Attribute14
788 , tmp.Attribute15
789 , tmp.OriginalSystemReference
790 , 'UPDATE' transaction_type
791 , NULL return_status
792 , NULL row_identifier
793 FROM bom_oper_networks_temp tmp
794 WHERE tmp.AssemblyItemName = P_assembly_item_name
795 AND tmp.OrganizationCode = P_organization_code
796 AND NVL(tmp.AlternateRoutingCode,'$$##$$') = NVL(P_alternate_routing_code,'$$##$$')
797 AND EXISTS (SELECT 1
798 FROM bom_operation_networks op_network,
799 bom_operational_routings rtg,
800 bom_operation_sequences oper1,
801 bom_operation_sequences oper2,
802 mtl_parameters org,
803 mtl_system_items_kfv item
804 WHERE op_network.from_op_seq_id = oper1.operation_sequence_id
805 AND op_network.to_op_seq_id = oper2.operation_sequence_id
809 AND org.organization_id = rtg.organization_id
806 AND item.inventory_item_id = rtg.assembly_item_id
807 AND item.organization_id = rtg.organization_id
808 AND item.concatenated_segments = tmp.AssemblyItemName
810 AND org.organization_code = tmp.OrganizationCode
811 AND NVL(rtg.alternate_routing_designator, '##$$##') = NVL(tmp.AlternateRoutingCode,'##$$##')
812 AND oper1.routing_sequence_id = rtg.routing_sequence_id
813 AND oper1.operation_seq_num = tmp.FromOpSeqNumber
814 AND oper1.operation_type = tmp.OperationType
815 AND oper1.effectivity_date = TO_DATE(tmp.FromStartEffectiveDate,'YYYY-MM-DD HH24:MI:SS')
816 AND oper2.routing_sequence_id = rtg.routing_sequence_id
817 AND oper2.operation_seq_num = tmp.ToOpSeqNumber
818 AND oper2.operation_type = tmp.OperationType
819 AND oper2.effectivity_date = TO_DATE(tmp.ToStartEffectiveDate,'YYYY-MM-DD HH24:MI:SS')
820 AND rtg.common_assembly_item_id is null);
821 i NUMBER;
822 -- Following variables are used for logging messages
823 l_error_tbl ERROR_HANDLER.ERROR_TBL_TYPE;
824 l_message VARCHAR2(2000) := NULL;
825 l_entity VARCHAR2(3) := NULL;
826 l_msg_index NUMBER;
827 l_message_type VARCHAR2(1);
828 hdr_cnt NUMBER := 0;
829
830 BEGIN
831 -- Routing Header Record
832
833 IF (p_rtg_hdr_XML IS NULL) THEN
834 FND_MESSAGE.SET_NAME('BOM', 'BOM_SETUP_NO_ROWS');
835 FND_MESSAGE.RETRIEVE(X_G_msg_data);
836 ELSE
837 -- get the context handle
838 insCtx := DBMS_XMLSave.newContext('BOM_ROUTING_HEADER_TEMP');
839 DBMS_XMLSave.setIgnoreCase(insCtx, 1);
840 DBMS_XMLSave.setDateFormat(insCtx, 'yyyy-MM-dd HH:mm:ss');
841 DBMS_XMLSave.setRowTag(insCtx , 'OperationalRoutingsVO');
842 -- this inserts the document
843 rows := DBMS_XMLSave.insertXML(insCtx,P_rtg_hdr_XML);
844 -- this closes the handle
845 DBMS_XMLSave.closeContext(insCtx);
846 -- Routing Revisions Table
847 IF (P_rtg_rev_XML IS NOT NULL) THEN
848 -- get the context handle
849 insCtx := DBMS_XMLSave.newContext('BOM_RTG_REVISIONS_TEMP');
850 DBMS_XMLSave.setIgnoreCase(insCtx, 1);
851 DBMS_XMLSave.setDateFormat(insCtx, 'yyyy-MM-dd HH:mm:ss');
852 DBMS_XMLSave.setRowTag(insCtx , 'RoutingRevisionsVO');
853 -- this inserts the document
854 rows := DBMS_XMLSave.insertXML(insCtx,P_rtg_rev_XML);
855 -- this closes the handle
856 DBMS_XMLSave.closeContext(insCtx);
857 END IF;
858
859 -- Routing Operations Table
860 IF (P_rtg_op_XML IS NOT NULL) THEN
861 -- get the context handle
862 insCtx := DBMS_XMLSave.newContext('BOM_ROUTING_OPERATIONS_TEMP');
863 DBMS_XMLSave.setIgnoreCase(insCtx, 1);
864 DBMS_XMLSave.setDateFormat(insCtx, 'yyyy-MM-dd HH:mm:ss');
865 DBMS_XMLSave.setRowTag(insCtx , 'OperationSequencesVO');
866 -- this inserts the document
867 rows := DBMS_XMLSave.insertXML(insCtx,P_rtg_op_XML);
868 -- this closes the handle
869 DBMS_XMLSave.closeContext(insCtx);
870 END IF;
871
872 -- Routing Operation Resources Table
873 IF (P_rtg_op_res_XML IS NOT NULL) THEN
874 -- get the context handle
875 insCtx := DBMS_XMLSave.newContext('BOM_RTG_OPER_RES_TEMP');
876 DBMS_XMLSave.setIgnoreCase(insCtx, 1);
877 DBMS_XMLSave.setDateFormat(insCtx, 'yyyy-MM-dd HH:mm:ss');
878 DBMS_XMLSave.setRowTag(insCtx , 'OperationResourcesVO');
879 -- this inserts the document
880 rows := DBMS_XMLSave.insertXML(insCtx,P_rtg_op_res_XML);
881 -- this closes the handle
882 DBMS_XMLSave.closeContext(insCtx);
883 END IF;
884
885 -- Routing Substitute Operation Resources Table
886 IF (p_rtg_sub_op_res_XML IS NOT NULL) THEN
887 -- get the context handle
888 insCtx := DBMS_XMLSave.newContext('BOM_SUB_OPER_RESOURCES_TEMP');
889 DBMS_XMLSave.setIgnoreCase(insCtx, 1);
890 DBMS_XMLSave.setDateFormat(insCtx, 'yyyy-MM-dd HH:mm:ss');
891 DBMS_XMLSave.setRowTag(insCtx , 'SubstituteOperationResourcesVO');
892 -- this inserts the document
893 rows := DBMS_XMLSave.insertXML(insCtx,P_rtg_sub_op_res_XML);
894 -- this closes the handle
895 DBMS_XMLSave.closeContext(insCtx);
896 END IF;
897
898 -- Routing Operation Networks Table
899 IF (P_rtg_op_network_XML IS NOT NULL) THEN
900 -- get the context handle
901 insCtx := DBMS_XMLSave.newContext('BOM_OPER_NETWORKS_TEMP');
902 DBMS_XMLSave.setIgnoreCase(insCtx, 1);
903 DBMS_XMLSave.setDateFormat(insCtx, 'yyyy-MM-dd HH:mm:ss');
904 DBMS_XMLSave.setRowTag(insCtx , 'OperationNetworksVO');
905 -- this inserts the document
906 rows := DBMS_XMLSave.insertXML(insCtx,P_rtg_op_network_XML);
907 -- this closes the handle
908 DBMS_XMLSave.closeContext(insCtx);
909 END IF;
910
911 OPEN rtg_hdr_CUR;
912 LOOP
913 FETCH rtg_hdr_CUR INTO l_rtg_header_rec;
914 IF (rtg_hdr_CUR%NOTFOUND) THEN
915 EXIT;
916 END IF;
920 l_operation_tbl.DELETE;
917 hdr_cnt := hdr_cnt + 1;
918
919 l_rtg_revision_tbl.DELETE;
921 l_op_resource_tbl.DELETE;
922 l_sub_resource_tbl.DELETE;
923 l_op_network_tbl.DELETE;
924
925 OPEN rtg_rev_CUR(l_rtg_header_rec.Assembly_Item_Name,
926 l_rtg_header_rec.Organization_Code);
927 i := 1;
928 LOOP
929 FETCH rtg_rev_CUR INTO l_rtg_revision_tbl(i);
930 IF (rtg_rev_CUR%NOTFOUND) THEN
931 EXIT;
932 END IF;
933 i := i + 1;
934 END LOOP;
935
936 CLOSE rtg_rev_CUR;
937
938 OPEN rtg_op_CUR(l_rtg_header_rec.Assembly_Item_Name,
939 l_rtg_header_rec.Organization_Code,
940 l_rtg_header_rec.Alternate_Routing_Code);
941 i := 1;
942 LOOP
943 FETCH rtg_op_CUR INTO l_operation_tbl(i);
944 IF (rtg_op_CUR%NOTFOUND) THEN
945 EXIT;
946 END IF;
947 i := i + 1;
948 END LOOP;
949 CLOSE rtg_op_CUR;
950
951 OPEN rtg_op_res_CUR(l_rtg_header_rec.Assembly_Item_Name,
952 l_rtg_header_rec.Organization_Code,
953 l_rtg_header_rec.Alternate_Routing_Code);
954 i := 1;
955 LOOP
956 FETCH rtg_op_res_CUR INTO l_op_resource_tbl(i);
957 IF (rtg_op_res_CUR%NOTFOUND) THEN
958 EXIT;
959 END IF;
960 i := i + 1;
961 END LOOP;
962 CLOSE rtg_op_res_CUR;
963 OPEN rtg_sub_op_res_CUR(l_rtg_header_rec.Assembly_Item_Name,
964 l_rtg_header_rec.Organization_Code,
965 l_rtg_header_rec.Alternate_Routing_Code);
966 i := 1;
967 LOOP
968 FETCH rtg_sub_op_res_CUR INTO l_sub_resource_tbl(i);
969 IF (rtg_sub_op_res_CUR%NOTFOUND) THEN
970 EXIT;
971 END IF;
972 i := i + 1;
973 END LOOP;
974 CLOSE rtg_sub_op_res_CUR;
975
976 OPEN rtg_op_networks_CUR(l_rtg_header_rec.Assembly_Item_Name,
977 l_rtg_header_rec.Organization_Code,
978 l_rtg_header_rec.Alternate_Routing_Code);
979 i := 1;
980 LOOP
981 FETCH rtg_op_networks_CUR INTO l_op_network_tbl(i);
982 IF (rtg_op_networks_CUR%NOTFOUND) THEN
983 EXIT;
984 END IF;
985 i := i + 1;
986 END LOOP;
987 CLOSE rtg_op_networks_CUR;
988 BOM_GLOBALS.Set_Caller_Type('MIGRATION');
989 ERROR_HANDLER.Initialize;
990 BOM_RTG_PUB.Process_RTG(p_rtg_header_rec => l_rtg_header_rec
991 , p_rtg_revision_tbl => l_rtg_revision_tbl
992 , p_operation_tbl => l_operation_tbl
993 , p_op_resource_tbl => l_op_resource_tbl
994 , p_sub_resource_tbl => l_sub_resource_tbl
995 , p_op_network_tbl => l_op_network_tbl
996 , p_debug => P_debug
997 , p_output_dir => P_output_dir
998 , p_debug_filename => P_debug_filename
999 , x_rtg_header_rec => X_rtg_header_rec
1000 , x_rtg_revision_tbl => X_rtg_revision_tbl
1001 , x_operation_tbl => X_operation_tbl
1002 , x_op_resource_tbl => X_op_resource_tbl
1003 , x_sub_resource_tbl => X_sub_resource_tbl
1004 , x_op_network_tbl => X_op_network_tbl
1005 , x_return_status => X_return_status
1006 , x_msg_count => X_msg_count);
1007
1008 FOR i IN 1..X_msg_count LOOP
1009 BEGIN
1010 ERROR_HANDLER.Get_Message(x_entity_index => l_msg_index,
1011 x_entity_id => l_entity,
1012 x_message_text => l_message,
1013 x_message_type => l_message_type);
1014 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;
1015 EXCEPTION
1016 WHEN OTHERS THEN
1017 EXIT;
1018 END;
1019 END LOOP;
1020 END LOOP;
1021 IF (hdr_cnt = 0) THEN -- There are no records to be processed. RETURN with Error Message
1022 FND_MESSAGE.SET_NAME('BOM', 'BOM_SETUP_NO_ROWS');
1023 FND_MESSAGE.RETRIEVE(X_G_msg_data);
1024 END IF;
1025 END IF;
1026 END Import_Routing;
1027
1028 END BOM_RTG_ISETUP_IMP;