[Home] [Help]
PACKAGE BODY: APPS.BOM_RTG_COPYORG_IMP
Source
1 PACKAGE BODY BOM_RTG_COPYORG_IMP AS
2 /* $Header: BOMRTCPB.pls 120.2.12020000.2 2012/12/27 14:18:48 rambkond ship $ */
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_COPYORG_IMP
15 --
16 -- NOTES
17 --
18 -- HISTORY
19 --
20 -- 06-OCT-06 Mohan Yerramsetty Bug# 5493353, Initial Creation.
21 -- This package has PL/SQL logic of Copying
22 -- Routings. It doesn't use Exporting to XML,
23 -- Importing from XML Logic. This will fetch
24 -- all Routings from source organization and
25 -- pass all the records to Routing Interface API.
26 -- Routing Interface API will do the copying.
27 -- 13-DEC-06 Mohan Yerramsetty Bug# 5493353, Modified the code to delete
28 -- the successfully processed records after
29 -- the call to BOM_RTG_PUB.Process_RTG to
30 -- reduce the memory consumed by the process.
31 ***************************************************************************/
32
33 --=============================================
34 -- CONSTANTS
35 --=============================================
36 G_MODULE_PREFIX CONSTANT VARCHAR2(50) := 'bom.plsql.BOM_RTG_COPYORG_IMP.';
37 g_api_name CONSTANT VARCHAR2(30) := 'IMPORT_ROUTING';
38
39 --=============================================
40 -- GLOBAL VARIABLES
41 --=============================================
42 g_fnd_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'), 'N');
43
44
45 PROCEDURE IMPORT_ROUTING(P_debug IN VARCHAR2 := 'N',
46 P_output_dir IN VARCHAR2 := NULL,
47 P_debug_filename IN VARCHAR2 := 'BOM_BO_debug.log',
48 p_model_org_id IN NUMBER,
49 p_target_orgcode IN VARCHAR2,
50 X_return_status OUT NOCOPY VARCHAR2,
51 X_msg_count OUT NOCOPY NUMBER,
52 X_G_msg_data OUT NOCOPY LONG) IS
53
54 TYPE Rtg_Header_Tbl_Type IS TABLE OF Bom_Rtg_Pub.Rtg_Header_Rec_Type
55 INDEX BY BINARY_INTEGER ;
56 l_rtg_header_tbl Rtg_Header_Tbl_Type;
57
58 l_rtg_header_rec Bom_Rtg_Pub.Rtg_Header_Rec_Type;
59 l_rtg_revision_tbl Bom_Rtg_Pub.Rtg_Revision_Tbl_Type;
60 l_operation_tbl Bom_Rtg_Pub.Operation_Tbl_Type;
61 l_op_resource_tbl Bom_Rtg_Pub.Op_Resource_Tbl_Type;
62 l_sub_resource_tbl Bom_Rtg_Pub.Sub_Resource_Tbl_Type;
63 l_op_network_tbl Bom_Rtg_Pub.Op_Network_Tbl_Type;
64
65 x_rtg_header_rec Bom_Rtg_Pub.Rtg_Header_Rec_Type;
66 x_rtg_revision_tbl Bom_Rtg_Pub.Rtg_Revision_Tbl_Type;
67 x_operation_tbl Bom_Rtg_Pub.Operation_Tbl_Type;
68 x_op_resource_tbl Bom_Rtg_Pub.Op_Resource_Tbl_Type;
69 x_sub_resource_tbl Bom_Rtg_Pub.Sub_Resource_Tbl_Type;
70 x_op_network_tbl Bom_Rtg_Pub.Op_Network_Tbl_Type;
71
72
73 ---------------------------------Operational Routings Cursor-----------------------------------------------
74
75 CURSOR rtg_hdr_CUR IS
76 SELECT item1.concatenated_segments Assembly_Item_Name,
77 p_target_orgcode Organization_Code, --myerrams, Select the Target Org code into the cursor
78 bor.alternate_routing_designator Alternate_Routing_Code,
79 DECODE(bor.routing_type, 1, 2, 2, 1, NULL) Eng_Routing_Flag,
80 item2.concatenated_segments Common_Assembly_Item_Name,
81 bor.routing_comment Routing_Comment,
82 bor.completion_subinventory Completion_Subinventory,
83 locators.concatenated_segments Completion_Location_Name,
84 wl.line_code Line_Code,
85 bor.cfm_Routing_Flag CFM_Routing_Flag,
86 bor.mixed_model_map_flag Mixed_Model_Map_Flag,
87 bor.priority Priority,
88 bor.total_product_cycle_time Total_Cycle_Time,
89 bor.ctp_flag CTP_Flag,
90 bor.attribute_category Attribute_category,
91 bor.attribute1 Attribute1,
92 bor.attribute2 Attribute2,
93 bor.attribute3 Attribute3,
94 bor.attribute4 Attribute4,
95 bor.attribute5 Attribute5,
96 bor.attribute6 Attribute6,
97 bor.attribute7 Attribute7,
98 bor.attribute8 Attribute8,
99 bor.attribute9 Attribute9,
100 bor.attribute10 Attribute10,
101 bor.attribute11 Attribute11,
102 bor.attribute12 Attribute12,
103 bor.attribute13 Attribute13,
104 bor.attribute14 Attribute14,
105 bor.attribute15 Attribute15,
106 bor.original_system_reference Original_System_Reference,
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_operational_routings bor,
114 wip_lines wl,
115 mtl_parameters org,
116 mtl_system_items_kfv item1,
117 mtl_system_items_kfv item2,
118 mtl_item_locations_kfv locators
119 WHERE wl.line_id(+) = bor.line_id
120 AND org.organization_id = bor.organization_id
121 AND item1.organization_id = bor.organization_id
122 AND item1.inventory_item_id = bor.assembly_item_id
123 AND item2.inventory_item_id(+) = bor.common_assembly_item_id
124 AND item2.organization_id(+) = bor.organization_id
125 AND locators.inventory_location_id(+) = bor.completion_locator_id
126 AND locators.organization_id (+) = bor.organization_id
127 AND bor.organization_id = p_model_org_id --myerrams, Filter Records based on Model Org Id
128 --Order by bor.alternate_routing_designator desc, bor.assembly_item_id; --myerrams, To copy Alternate Routings at the end.
129 Order by decode(trim(alternate_routing_designator),null,'0','1')||decode(routing_sequence_id,common_routing_sequence_id,'1','2');
130 /* Modifying the order by clause Bug 6923784*/
131 ---------------------------------Routing Revisions Cursor-----------------------------------------------
132
133 CURSOR rtg_rev_CUR(P_assembly_item_name VARCHAR2
134 ) IS
135 SELECT item.concatenated_segments Assembly_Item_Name,
136 p_target_orgcode Organization_Code,
137 NULL Alternate_Routing_Code,
138 rev.Process_Revision Revision,
139 rev.Effectivity_Date Start_Effective_Date,
140 rev.Attribute_category Attribute_category,
141 rev.Attribute1 Attribute1,
142 rev.Attribute2 Attribute2,
143 rev.Attribute3 Attribute3,
144 rev.Attribute4 Attribute4,
145 rev.Attribute5 Attribute5,
146 rev.Attribute6 Attribute6,
147 rev.Attribute7 Attribute7,
148 rev.Attribute8 Attribute8,
149 rev.Attribute9 Attribute9,
150 rev.Attribute10 Attribute10,
151 rev.Attribute11 Attribute11,
152 rev.Attribute12 Attribute12,
153 rev.Attribute13 Attribute13,
154 rev.Attribute14 Attribute14,
155 rev.Attribute15 Attribute15,
156 NULL Original_System_Reference,
157 'CREATE' Transaction_Type,
158 NULL Return_Status,
159 NULL Row_Identifier
160 FROM mtl_rtg_item_revisions rev,
161 mtl_parameters org,
162 mtl_system_items_kfv item
163 WHERE org.organization_id = rev.organization_id
164 AND item.organization_id = rev.organization_id
165 AND item.inventory_item_id = rev.inventory_item_id
166 AND item.concatenated_segments = P_assembly_item_name
167 AND org.organization_id = p_model_org_id; --myerrams, Filter Records based on Model Org Id
168
169
170 ---------------------------------Operation Sequences Cursor-----------------------------------------------
171
172 CURSOR rtg_op_CUR(P_assembly_item_name VARCHAR2,
173 P_alternate_routing_code VARCHAR2) IS
174 SELECT item.concatenated_segments Assembly_Item_Name,
175 p_target_orgcode Organization_Code,
176 rtg.alternate_routing_designator Alternate_Routing_Code,
177 op_seq.Operation_Seq_Num Operation_Sequence_Number,
178 op_seq.Operation_Type Operation_Type,
179 op_seq.Effectivity_Date Start_Effective_Date,
180 NULL New_Operation_Sequence_Number,
181 NULL New_Start_Effective_Date,
182 op_seq.Standard_Operation_Code Standard_Operation_Code,
183 op_seq.Department_Code Department_Code,
184 op_seq.Operation_Lead_Time_Percent Op_Lead_Time_Percent,
185 op_seq.Minimum_Transfer_Quantity Minimum_Transfer_Quantity,
186 op_seq.Count_Point_Type Count_Point_Type,
187 op_seq.Operation_Description Operation_Description,
188 op_seq.Disable_Date Disable_Date,
189 op_seq.Backflush_Flag Backflush_Flag,
190 op_seq.Option_Dependent_Flag Option_Dependent_Flag,
191 op_seq.Reference_Flag Reference_Flag,
192 op_seq.Process_Seq_Num Process_Seq_Number,
193 op_seq.Process_Code Process_Code,
194 op_seq.Line_Op_Seq_Num Line_Op_Seq_Number,
195 op_seq.Line_Op_Code Line_Op_Code,
196 op_seq.Yield Yield,
197 op_seq.Cumulative_Yield Cumulative_Yield,
198 op_seq.Reverse_Cumulative_Yield Reverse_CUM_Yield,
199 op_seq.Labor_Time_User User_Labor_Time,
200 op_seq.Machine_Time_User User_Machine_Time,
201 op_seq.Net_Planning_Percent Net_Planning_Percent,
202 op_seq.Include_In_Rollup Include_In_Rollup,
203 op_seq.Operation_Yield_Enabled Op_Yield_Enabled_Flag,
204 op_seq.SHUTDOWN_TYPE Shutdown_Type,
205 op_seq.Attribute_category Attribute_category,
206 op_seq.Attribute1 Attribute1,
207 op_seq.Attribute2 Attribute2,
208 op_seq.Attribute3 Attribute3,
209 op_seq.Attribute4 Attribute4,
210 op_seq.Attribute5 Attribute5,
211 op_seq.Attribute6 Attribute6,
212 op_seq.Attribute7 Attribute7,
213 op_seq.Attribute8 Attribute8,
214 op_seq.Attribute9 Attribute9,
215 op_seq.Attribute10 Attribute10,
216 op_seq.Attribute11 Attribute11,
217 op_seq.Attribute12 Attribute12,
218 op_seq.Attribute13 Attribute13,
219 op_seq.Attribute14 Attribute14,
220 op_seq.Attribute15 Attribute15,
221 op_seq.Original_System_Reference Original_System_Reference,
222 'CREATE' Transaction_Type,
223 NULL Return_Status,
224 NULL Delete_Group_Name,
225 NULL DG_Description,
226 NULL Long_Description,
227 NULL Row_Identifier
228 FROM bom_operation_sequences_v op_seq,
229 bom_operational_routings rtg,
230 mtl_parameters org,
231 mtl_system_items_kfv item
232 WHERE rtg.routing_sequence_id = op_seq.routing_sequence_id
233 AND org.organization_id = rtg.organization_id
234 AND item.organization_id = rtg.organization_id
235 AND item.inventory_item_id = rtg.assembly_item_id
236 AND item.concatenated_segments = P_assembly_item_name
237 AND NVL(rtg.alternate_routing_designator,'$$##$$') = NVL(P_alternate_routing_code,'$$##$$')
238 AND org.Organization_id = p_model_org_id; --myerrams, Filter Records based on Model Org Id
239
240
241 ---------------------------------Operation Resources Cursor-----------------------------------------------
242
243 CURSOR rtg_op_res_CUR (P_assembly_item_name VARCHAR2,
244 P_alternate_routing_code VARCHAR2) IS
245 SELECT item.concatenated_segments Assembly_Item_Name,
246 p_target_orgcode Organization_Code,
247 rtg.alternate_routing_designator Alternate_Routing_Code,
248 op_seq.operation_seq_num Operation_Sequence_Number,
249 op_seq.operation_type Operation_Type,
250 op_seq.effectivity_date Op_Start_Effective_Date,
251 null acd_type, --Bug 16042107
252 null eco_name, --Bug 16042107
253 bor.Resource_Seq_Num Resource_Sequence_Number,
254 br.Resource_Code Resource_Code,
255 ca.Activity Activity,
256 bor.Standard_Rate_Flag Standard_Rate_Flag,
257 bor.Assigned_Units Assigned_Units,
258 bor.Usage_Rate_Or_amount Usage_Rate_Or_Amount,
259 bor.Usage_Rate_Or_Amount_Inverse Usage_Rate_Or_Amount_Inverse,
260 bor.Basis_Type Basis_Type,
261 bor.Schedule_Flag Schedule_Flag,
262 bor.Resource_Offset_Percent Resource_Offset_Percent,
263 bor.Autocharge_Type Autocharge_Type,
264 bor.Substitute_Group_Num Substitute_Group_Number,
265 bor.Schedule_Seq_Num Schedule_Sequence_Number,
266 bor.Principle_Flag Principle_Flag,
267 bor.Attribute_category Attribute_category,
268 bor.Attribute1 Attribute1,
269 bor.Attribute2 Attribute2,
270 bor.Attribute3 Attribute3,
271 bor.Attribute4 Attribute4,
272 bor.Attribute5 Attribute5,
273 bor.Attribute6 Attribute6,
274 bor.Attribute7 Attribute7,
275 bor.Attribute8 Attribute8,
276 bor.Attribute9 Attribute9,
277 bor.Attribute10 Attribute10,
278 bor.Attribute11 Attribute11,
279 bor.Attribute12 Attribute12,
280 bor.Attribute13 Attribute13,
281 bor.Attribute14 Attribute14,
282 bor.Attribute15 Attribute15,
283 NULL Original_System_Reference,
284 'CREATE' Transaction_Type,
285 NULL Return_Status,
286 bst.Setup_Code Setup_Type,
287 NULL Row_Identifier
288 FROM bom_operation_resources bor,
289 bom_resources br,
290 cst_activities ca,
291 bom_setup_types bst,
292 bom_operation_sequences op_seq,
293 bom_operational_routings rtg,
294 mtl_system_items_kfv item,
295 mtl_parameters org
296 WHERE op_seq.operation_sequence_id = bor.operation_sequence_id
297 AND rtg.routing_sequence_id = op_seq.routing_sequence_id
298 AND org.organization_id = rtg.organization_id
299 AND item.organization_id = rtg.organization_id
300 AND item.inventory_item_id = rtg.assembly_item_id
301 AND br.resource_id = bor.resource_id
302 AND ca.activity_id(+) = bor.activity_id
303 AND bst.setup_id(+) = bor.setup_id
304 AND item.concatenated_segments = P_assembly_item_name
305 AND NVL(rtg.alternate_routing_designator,'$$##$$') = NVL(P_alternate_routing_code,'$$##$$')
306 AND org.Organization_id = p_model_org_id; --myerrams, Filter Records based on Model Org Id
307
308
309 ---------------------------------Substitute Operation Resources Cursor-----------------------------------------------
310
311 CURSOR rtg_sub_op_res_CUR(P_assembly_item_name VARCHAR2,
312 P_alternate_routing_code VARCHAR2) IS
313 SELECT item.concatenated_segments Assembly_Item_Name,
314 p_target_orgcode Organization_Code,
315 rtg.alternate_routing_designator Alternate_Routing_Code,
316 op_seq.operation_seq_num Operation_Sequence_Number,
317 op_seq.operation_type Operation_Type,
318 op_seq.effectivity_date Op_Start_Effective_Date,
319 br.Resource_Code Sub_Resource_Code,
320 null New_Sub_Resource_Code,
321 bsor.substitute_group_num Substitute_Group_Number,
322 bsor.schedule_seq_num Schedule_Sequence_Number,
323 bsor.replacement_group_num Replacement_Group_Number,
324 TO_NUMBER(NULL) New_Replacement_Group_Number,
325 ca.Activity Activity,
326 bsor.Standard_Rate_Flag Standard_Rate_Flag,
327 bsor.Assigned_Units Assigned_Units,
328 bsor.Usage_Rate_Or_Amount Usage_Rate_Or_Amount,
329 bsor.Usage_Rate_Or_Amount_Inverse Usage_Rate_Or_Amount_Inverse,
330 bsor.Basis_Type Basis_Type,
331 TO_NUMBER(NULL) New_Basis_Type,
332 bsor.Schedule_Flag Schedule_Flag,
333 TO_NUMBER(NULL) New_Schedule_Flag, --bug 13563553
334 bsor.Resource_Offset_Percent Resource_Offset_Percent,
335 bsor.Autocharge_Type Autocharge_Type,
336 bsor.Principle_Flag Principle_Flag,
337 bsor.Attribute_category Attribute_category,
338 bsor.Attribute1 Attribute1,
339 bsor.Attribute2 Attribute2,
340 bsor.Attribute3 Attribute3,
341 bsor.Attribute4 Attribute4,
342 bsor.Attribute5 Attribute5,
343 bsor.Attribute6 Attribute6,
344 bsor.Attribute7 Attribute7,
345 bsor.Attribute8 Attribute8,
346 bsor.Attribute9 Attribute9,
347 bsor.Attribute10 Attribute10,
348 bsor.Attribute11 Attribute11,
349 bsor.Attribute12 Attribute12,
350 bsor.Attribute13 Attribute13,
351 bsor.Attribute14 Attribute14,
352 bsor.Attribute15 Attribute15,
353 bsor.original_system_reference Original_System_Reference,
354 'CREATE' Transaction_Type,
355 NULL Return_Status,
356 bst.setup_Code Setup_Type,
357 NULL Row_Identifier
358 FROM bom_sub_operation_resources bsor,
359 mtl_system_items_kfv item,
360 mtl_parameters org,
361 bom_operational_routings rtg,
362 bom_operation_sequences op_seq,
363 bom_resources br,
364 cst_activities ca,
365 bom_setup_types bst
366 WHERE op_seq.operation_sequence_id = bsor.operation_sequence_id
367 AND rtg.routing_sequence_id = op_seq.routing_sequence_id
368 AND org.organization_id = rtg.organization_id
369 AND item.organization_id = rtg.organization_id
370 AND item.inventory_item_id = rtg.assembly_item_id
371 AND br.resource_id = bsor.resource_id
372 AND ca.activity_id(+) = bsor.activity_id
373 AND bst.setup_id(+) = bsor.setup_id
374 AND item.concatenated_segments = P_assembly_item_name
375 AND NVL(rtg.alternate_routing_designator,'$$##$$') = NVL(P_alternate_routing_code,'$$##$$')
376 AND org.Organization_id = p_model_org_id; --myerrams, Filter Records based on Model Org Id
377
378
379 ---------------------------------Operation Networks Cursor-----------------------------------------------
380
381 -- BOM_OPERATION_NETWORKS
382 -- From_Op_Seq_Id
383 -- To_Op_Seq_Id
384
385 CURSOR rtg_op_networks_CUR(P_assembly_item_name VARCHAR2,
386 P_alternate_routing_code VARCHAR2) IS
387 SELECT item.concatenated_segments Assembly_Item_Name,
388 p_target_orgcode Organization_Code,
389 rtg.alternate_routing_designator Alternate_Routing_Code,
390 bonv.operation_type Operation_Type,
391 bonv.From_Seq_Num From_Op_Seq_Number,
392 bos1.X_Coordinate From_X_Coordinate,
393 bos1.Y_Coordinate From_Y_Coordinate,
394 bonv.From_Effectivity_Date From_Start_Effective_Date,
395 bonv.To_Seq_Num To_Op_Seq_Number,
396 bos2.X_Coordinate To_X_Coordinate,
397 bos2.Y_Coordinate To_Y_Coordinate,
398 bonv.To_Effectivity_Date To_Start_Effective_Date,
399 null New_From_Op_Seq_Number,
400 null New_From_Start_Effective_Date,
401 null New_To_Op_Seq_Number,
402 null New_To_Start_Effective_Date,
403 bonv.Transition_Type Connection_Type,
404 bonv.Planning_Pct Planning_Percent,
405 bonv.Attribute_category Attribute_category,
406 bonv.Attribute1 Attribute1,
407 bonv.Attribute2 Attribute2,
408 bonv.Attribute3 Attribute3,
409 bonv.Attribute4 Attribute4,
410 bonv.Attribute5 Attribute5,
411 bonv.Attribute6 Attribute6,
412 bonv.Attribute7 Attribute7,
413 bonv.Attribute8 Attribute8,
414 bonv.Attribute9 Attribute9,
415 bonv.Attribute10 Attribute10,
416 bonv.Attribute11 Attribute11,
417 bonv.Attribute12 Attribute12,
418 bonv.Attribute13 Attribute13,
419 bonv.Attribute14 Attribute14,
420 bonv.Attribute15 Attribute15,
421 bonv.Original_System_Reference Original_System_Reference,
422 'CREATE' Transaction_Type,
423 NULL Return_Status,
424 NULL Row_Identifier
425 FROM bom_operation_networks_v bonv,
426 bom_operation_sequences bos1,
427 bom_operation_sequences bos2,
428 bom_operational_routings rtg,
429 mtl_system_items_kfv item,
430 mtl_parameters org
431 WHERE rtg.routing_sequence_id = bonv.routing_sequence_id
432 AND bos1.Operation_Sequence_Id = bonv.From_Op_Seq_Id
433 AND bos2.Operation_Sequence_Id = bonv.To_Op_Seq_Id
434 AND org.organization_id = rtg.organization_id
435 AND item.organization_id = rtg.organization_id
436 AND item.inventory_item_id = rtg.assembly_item_id
437 AND item.concatenated_segments = P_assembly_item_name
438 AND NVL(rtg.alternate_routing_designator,'$$##$$') = NVL(P_alternate_routing_code,'$$##$$')
439 AND org.Organization_id = p_model_org_id; --myerrams, Filter Records based on Model Org Id
440
441 ---------------------------------End of Cursors Definitions-----------------------------------------------
442
443 i NUMBER;
444 -- Following variables are used for logging messages
445 l_message VARCHAR2(2000) := NULL;
446 l_entity VARCHAR2(3) := NULL;
447 l_msg_index NUMBER;
448 l_message_type VARCHAR2(1);
449
450 --myerrams, Following Variables are used for passing the Return Status to CopyLoader and to Log Assembly Item Name
451 l_X_return_status VARCHAR2(10);
452 l_status_set BOOLEAN := FALSE;
453 l_Assembly_Item_Name VARCHAR2(100);
454
455 --myerrams, Following Variables are used for Looping Logic
456 l_hdr_cnt NUMBER := 0;
457 l_counter NUMBER := 1;
458 l_max_batch_size NUMBER := 200;
459 l_min_index NUMBER;
460 l_max_index NUMBER;
461
462 BEGIN
463
464 IF g_fnd_debug = 'Y' AND
465 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
466 THEN
467 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
468 , G_MODULE_PREFIX || g_api_name || '.begin'
469 , NULL);
470 END IF;
471
472 /*myerrams, to make sure that Return Status is not null when the model org doesn't have any Routings to copy*/
473 x_return_status := 'S';
474
475 --myerrams, To get the number of routings in Model org.
476 SELECT COUNT(*)
477 INTO l_hdr_cnt
478 FROM bom_operational_routings bor,
479 wip_lines wl,
480 mtl_parameters org,
481 mtl_system_items_kfv item1,
482 mtl_system_items_kfv item2,
483 mtl_item_locations_kfv locators
484 WHERE wl.line_id(+) = bor.line_id
485 AND org.organization_id = bor.organization_id
486 AND item1.organization_id = bor.organization_id
487 AND item1.inventory_item_id = bor.assembly_item_id
488 AND item2.inventory_item_id(+) = bor.common_assembly_item_id
489 AND item2.organization_id(+) = bor.organization_id
490 AND locators.inventory_location_id(+) = bor.completion_locator_id
491 AND locators.organization_id (+) = bor.organization_id
492 AND bor.organization_id = p_model_org_id;
493
494 l_min_index := 1;
495
496 IF l_hdr_cnt > l_max_batch_size
497 THEN
498 l_max_index := l_max_batch_size;
499 ELSE
500 l_max_index := l_hdr_cnt;
501 END IF;
502
503 -- Routing Header Record
504 l_rtg_header_tbl.DELETE;
505
506 OPEN rtg_hdr_CUR;
507 LOOP
508 FETCH rtg_hdr_CUR BULK COLLECT INTO l_rtg_header_tbl LIMIT l_max_batch_size;
509 l_counter := 1;
510
511
512 FOR l_Idx IN l_min_index..l_max_index
513 LOOP
514 l_rtg_header_rec := l_rtg_header_tbl(l_counter);
515 l_Assembly_Item_Name := l_rtg_header_rec.Assembly_Item_Name;
516
517 l_rtg_revision_tbl.DELETE;
518 l_operation_tbl.DELETE;
519 l_op_resource_tbl.DELETE;
520 l_sub_resource_tbl.DELETE;
521 l_op_network_tbl.DELETE;
522
523 OPEN rtg_rev_CUR(l_rtg_header_rec.Assembly_Item_Name);
524 i := 1;
525 LOOP
526 FETCH rtg_rev_CUR INTO l_rtg_revision_tbl(i);
527 IF (rtg_rev_CUR%NOTFOUND) THEN
528 EXIT;
529 END IF;
530 i := i + 1;
531 END LOOP;
532 CLOSE rtg_rev_CUR;
533
534 OPEN rtg_op_CUR(l_rtg_header_rec.Assembly_Item_Name,
535 l_rtg_header_rec.Alternate_Routing_Code);
536 i := 1;
537 LOOP
538 FETCH rtg_op_CUR INTO l_operation_tbl(i);
539 IF (rtg_op_CUR%NOTFOUND) THEN
540 EXIT;
541 END IF;
542 i := i + 1;
543 END LOOP;
544 CLOSE rtg_op_CUR;
545
546 OPEN rtg_op_res_CUR(l_rtg_header_rec.Assembly_Item_Name,
547 l_rtg_header_rec.Alternate_Routing_Code);
548 i := 1;
549 LOOP
550 FETCH rtg_op_res_CUR INTO l_op_resource_tbl(i);
551 IF (rtg_op_res_CUR%NOTFOUND) THEN
552 EXIT;
553 END IF;
554 i := i + 1;
555 END LOOP;
556 CLOSE rtg_op_res_CUR;
557 OPEN rtg_sub_op_res_CUR(l_rtg_header_rec.Assembly_Item_Name,
558 l_rtg_header_rec.Alternate_Routing_Code);
559 i := 1;
560 LOOP
561 FETCH rtg_sub_op_res_CUR INTO l_sub_resource_tbl(i);
562 IF (rtg_sub_op_res_CUR%NOTFOUND) THEN
563 EXIT;
564 END IF;
565 i := i + 1;
566 END LOOP;
567 CLOSE rtg_sub_op_res_CUR;
568
569 OPEN rtg_op_networks_CUR(l_rtg_header_rec.Assembly_Item_Name,
570 l_rtg_header_rec.Alternate_Routing_Code);
571 i := 1;
572 LOOP
573 FETCH rtg_op_networks_CUR INTO l_op_network_tbl(i);
574 IF (rtg_op_networks_CUR%NOTFOUND) THEN
575 EXIT;
576 END IF;
577 i := i + 1;
578 END LOOP;
579 CLOSE rtg_op_networks_CUR;
580 BOM_GLOBALS.Set_Caller_Type('MIGRATION');
581 ERROR_HANDLER.Initialize;
582 BOM_RTG_PUB.Process_RTG(p_rtg_header_rec => l_rtg_header_rec
583 , p_rtg_revision_tbl => l_rtg_revision_tbl
584 , p_operation_tbl => l_operation_tbl
585 , p_op_resource_tbl => l_op_resource_tbl
586 , p_sub_resource_tbl => l_sub_resource_tbl
587 , p_op_network_tbl => l_op_network_tbl
588 , p_debug => P_debug
589 , p_output_dir => P_output_dir
590 , p_debug_filename => P_debug_filename
591 , x_rtg_header_rec => X_rtg_header_rec
592 , x_rtg_revision_tbl => X_rtg_revision_tbl
593 , x_operation_tbl => X_operation_tbl
594 , x_op_resource_tbl => X_op_resource_tbl
595 , x_sub_resource_tbl => X_sub_resource_tbl
596 , x_op_network_tbl => X_op_network_tbl
597 , x_return_status => l_X_return_status
598 , x_msg_count => X_msg_count);
599
600 --myerrams, Bug: 5493353; Delete the successfully processed records after the
601 --call to BOM_RTG_PUB.Process_RTG to reduce the memory consumed by the process
602 X_rtg_revision_tbl.DELETE;
603 X_operation_tbl.DELETE;
604 X_op_resource_tbl.DELETE;
605 X_sub_resource_tbl.DELETE;
606 X_op_network_tbl.DELETE;
607
608 commit;
609
610 IF (l_X_return_status <> 'S' and l_status_set = FALSE) THEN
611 X_return_status := l_X_return_status;
612 l_status_set := TRUE;
613 END IF;
614
615 IF (l_X_return_status = 'U' and X_return_status <> 'U') THEN
616 X_return_status := l_X_return_status;
617 END IF;
618
619 FOR i IN 1..X_msg_count LOOP
620 BEGIN
621 ERROR_HANDLER.Get_Message(x_entity_index => l_msg_index,
622 x_entity_id => l_entity,
623 x_message_text => l_message,
624 x_message_type => l_message_type);
625
626 IF g_fnd_debug = 'Y' AND
627 FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
628 THEN
629 FND_LOG.string( FND_LOG.LEVEL_STATEMENT
630 , G_MODULE_PREFIX || g_api_name
631 , TO_CHAR(l_msg_index) || ': '||l_entity ||': '|| l_message_type ||': '||l_message);
632 END IF;
633
634 EXCEPTION
635 WHEN OTHERS THEN
636
637 IF FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
638 THEN
639 FND_LOG.string( FND_LOG.LEVEL_UNEXPECTED
640 , G_MODULE_PREFIX || g_api_name
641 , SQLCODE||' : '||SQLERRM || ' Item: ' || l_Assembly_Item_Name);
642 END IF;
643
644 X_G_msg_data := X_G_msg_data || FND_GLOBAL.NewLine || SQLCODE ||' : ' || SQLERRM || FND_GLOBAL.NewLine || ' Item: ' || l_Assembly_Item_Name ;
645 X_return_status := 'U';
646
647 EXIT;
648 END;
649 END LOOP; --Close X_msg_count LOOP
650
651 IF(X_msg_count > 0 ) THEN
652
653 IF g_fnd_debug = 'Y' AND
654 FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
655 THEN
656 FND_LOG.string( FND_LOG.LEVEL_STATEMENT
657 , G_MODULE_PREFIX || g_api_name
658 , 'X_return_status for Item:' || l_Assembly_Item_Name || ' is: ' || l_X_return_status);
659 END IF;
660
661 END IF;
662
663 l_counter := l_counter + 1;
664 END LOOP; --Close l_min_index..l_max_index LOOP
665
666 l_min_index := l_max_index + 1;
667 IF l_hdr_cnt > (l_max_index + l_max_batch_size)
668 THEN
669 l_max_index := l_max_index + l_max_batch_size;
670 ELSE
671 l_max_index := l_hdr_cnt;
672 END IF;
673
674 EXIT WHEN rtg_hdr_CUR%NOTFOUND;
675 END LOOP;
676 CLOSE rtg_hdr_CUR;
677
678 IF (l_hdr_cnt = 0) THEN -- There are no records to be processed. RETURN with Error Message
679 FND_MESSAGE.SET_NAME('BOM', 'BOM_SETUP_NO_ROWS');
680 FND_MESSAGE.RETRIEVE(X_G_msg_data);
681 END IF;
682
683 X_G_msg_data := X_G_msg_data || FND_GLOBAL.NewLine || 'Log messages are logged in FND LOG with Module name: bom.plsql.BOM_RTG_COPYORG_IMP.';
684
685 IF g_fnd_debug = 'Y' AND
686 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
687 THEN
688 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
689 , G_MODULE_PREFIX || g_api_name || '.end'
690 , NULL);
691 END IF;
692
693 END Import_Routing;
694
695 END BOM_RTG_COPYORG_IMP;