[Home] [Help]
PACKAGE BODY: APPS.BOM_RTG_COPYORG_IMP
Source
1 PACKAGE BODY BOM_RTG_COPYORG_IMP AS
2 /* $Header: BOMRTCPB.pls 120.0.12000000.1 2007/02/26 12:31:54 appldev 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_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
130
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,
227 NULL Row_Identifier
224 NULL Delete_Group_Name,
225 NULL DG_Description,
226 NULL Long_Description,
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 bor.Resource_Seq_Num Resource_Sequence_Number,
252 br.Resource_Code Resource_Code,
253 ca.Activity Activity,
254 bor.Standard_Rate_Flag Standard_Rate_Flag,
255 bor.Assigned_Units Assigned_Units,
256 bor.Usage_Rate_Or_amount Usage_Rate_Or_Amount,
257 bor.Usage_Rate_Or_Amount_Inverse Usage_Rate_Or_Amount_Inverse,
258 bor.Basis_Type Basis_Type,
259 bor.Schedule_Flag Schedule_Flag,
260 bor.Resource_Offset_Percent Resource_Offset_Percent,
261 bor.Autocharge_Type Autocharge_Type,
262 bor.Substitute_Group_Num Substitute_Group_Number,
263 bor.Schedule_Seq_Num Schedule_Sequence_Number,
264 bor.Principle_Flag Principle_Flag,
265 bor.Attribute_category Attribute_category,
266 bor.Attribute1 Attribute1,
267 bor.Attribute2 Attribute2,
268 bor.Attribute3 Attribute3,
269 bor.Attribute4 Attribute4,
270 bor.Attribute5 Attribute5,
271 bor.Attribute6 Attribute6,
272 bor.Attribute7 Attribute7,
273 bor.Attribute8 Attribute8,
274 bor.Attribute9 Attribute9,
275 bor.Attribute10 Attribute10,
276 bor.Attribute11 Attribute11,
277 bor.Attribute12 Attribute12,
278 bor.Attribute13 Attribute13,
279 bor.Attribute14 Attribute14,
280 bor.Attribute15 Attribute15,
281 NULL Original_System_Reference,
282 'CREATE' Transaction_Type,
283 NULL Return_Status,
284 bst.Setup_Code Setup_Type,
285 NULL Row_Identifier
286 FROM bom_operation_resources bor,
287 bom_resources br,
288 cst_activities ca,
289 bom_setup_types bst,
290 bom_operation_sequences op_seq,
291 bom_operational_routings rtg,
292 mtl_system_items_kfv item,
293 mtl_parameters org
294 WHERE op_seq.operation_sequence_id = bor.operation_sequence_id
295 AND rtg.routing_sequence_id = op_seq.routing_sequence_id
296 AND org.organization_id = rtg.organization_id
297 AND item.organization_id = rtg.organization_id
298 AND item.inventory_item_id = rtg.assembly_item_id
299 AND br.resource_id = bor.resource_id
300 AND ca.activity_id(+) = bor.activity_id
301 AND bst.setup_id(+) = bor.setup_id
302 AND item.concatenated_segments = P_assembly_item_name
303 AND NVL(rtg.alternate_routing_designator,'$$##$$') = NVL(P_alternate_routing_code,'$$##$$')
304 AND org.Organization_id = p_model_org_id; --myerrams, Filter Records based on Model Org Id
305
306
307 ---------------------------------Substitute Operation Resources Cursor-----------------------------------------------
308
309 CURSOR rtg_sub_op_res_CUR(P_assembly_item_name VARCHAR2,
310 P_alternate_routing_code VARCHAR2) IS
311 SELECT item.concatenated_segments Assembly_Item_Name,
312 p_target_orgcode Organization_Code,
313 rtg.alternate_routing_designator Alternate_Routing_Code,
314 op_seq.operation_seq_num Operation_Sequence_Number,
315 op_seq.operation_type Operation_Type,
316 op_seq.effectivity_date Op_Start_Effective_Date,
317 br.Resource_Code Sub_Resource_Code,
318 null New_Sub_Resource_Code,
319 bsor.substitute_group_num Substitute_Group_Number,
320 bsor.schedule_seq_num Schedule_Sequence_Number,
321 bsor.replacement_group_num Replacement_Group_Number,
325 bsor.Assigned_Units Assigned_Units,
322 TO_NUMBER(NULL) New_Replacement_Group_Number,
323 ca.Activity Activity,
324 bsor.Standard_Rate_Flag Standard_Rate_Flag,
326 bsor.Usage_Rate_Or_Amount Usage_Rate_Or_Amount,
327 bsor.Usage_Rate_Or_Amount_Inverse Usage_Rate_Or_Amount_Inverse,
328 bsor.Basis_Type Basis_Type,
329 TO_NUMBER(NULL) New_Basis_Type,
330 bsor.Schedule_Flag Schedule_Flag,
331 bsor.Resource_Offset_Percent Resource_Offset_Percent,
332 bsor.Autocharge_Type Autocharge_Type,
333 bsor.Principle_Flag Principle_Flag,
334 bsor.Attribute_category Attribute_category,
335 bsor.Attribute1 Attribute1,
336 bsor.Attribute2 Attribute2,
337 bsor.Attribute3 Attribute3,
338 bsor.Attribute4 Attribute4,
339 bsor.Attribute5 Attribute5,
340 bsor.Attribute6 Attribute6,
341 bsor.Attribute7 Attribute7,
342 bsor.Attribute8 Attribute8,
343 bsor.Attribute9 Attribute9,
344 bsor.Attribute10 Attribute10,
345 bsor.Attribute11 Attribute11,
346 bsor.Attribute12 Attribute12,
347 bsor.Attribute13 Attribute13,
348 bsor.Attribute14 Attribute14,
349 bsor.Attribute15 Attribute15,
350 bsor.original_system_reference Original_System_Reference,
351 'CREATE' Transaction_Type,
352 NULL Return_Status,
353 bst.setup_Code Setup_Type,
354 NULL Row_Identifier
355 FROM bom_sub_operation_resources bsor,
356 mtl_system_items_kfv item,
357 mtl_parameters org,
358 bom_operational_routings rtg,
359 bom_operation_sequences op_seq,
360 bom_resources br,
361 cst_activities ca,
362 bom_setup_types bst
363 WHERE op_seq.operation_sequence_id = bsor.operation_sequence_id
364 AND rtg.routing_sequence_id = op_seq.routing_sequence_id
365 AND org.organization_id = rtg.organization_id
366 AND item.organization_id = rtg.organization_id
367 AND item.inventory_item_id = rtg.assembly_item_id
368 AND br.resource_id = bsor.resource_id
369 AND ca.activity_id(+) = bsor.activity_id
370 AND bst.setup_id(+) = bsor.setup_id
371 AND item.concatenated_segments = P_assembly_item_name
372 AND NVL(rtg.alternate_routing_designator,'$$##$$') = NVL(P_alternate_routing_code,'$$##$$')
373 AND org.Organization_id = p_model_org_id; --myerrams, Filter Records based on Model Org Id
374
375
376 ---------------------------------Operation Networks Cursor-----------------------------------------------
377
378 -- BOM_OPERATION_NETWORKS
379 -- From_Op_Seq_Id
380 -- To_Op_Seq_Id
381
382 CURSOR rtg_op_networks_CUR(P_assembly_item_name VARCHAR2,
383 P_alternate_routing_code VARCHAR2) IS
384 SELECT item.concatenated_segments Assembly_Item_Name,
385 p_target_orgcode Organization_Code,
386 rtg.alternate_routing_designator Alternate_Routing_Code,
387 bonv.operation_type Operation_Type,
388 bonv.From_Seq_Num From_Op_Seq_Number,
389 bos1.X_Coordinate From_X_Coordinate,
390 bos1.Y_Coordinate From_Y_Coordinate,
391 bonv.From_Effectivity_Date From_Start_Effective_Date,
392 bonv.To_Seq_Num To_Op_Seq_Number,
393 bos2.X_Coordinate To_X_Coordinate,
394 bos2.Y_Coordinate To_Y_Coordinate,
395 bonv.To_Effectivity_Date To_Start_Effective_Date,
396 null New_From_Op_Seq_Number,
397 null New_From_Start_Effective_Date,
398 null New_To_Op_Seq_Number,
399 null New_To_Start_Effective_Date,
400 bonv.Transition_Type Connection_Type,
401 bonv.Planning_Pct Planning_Percent,
402 bonv.Attribute_category Attribute_category,
403 bonv.Attribute1 Attribute1,
404 bonv.Attribute2 Attribute2,
405 bonv.Attribute3 Attribute3,
406 bonv.Attribute4 Attribute4,
407 bonv.Attribute5 Attribute5,
408 bonv.Attribute6 Attribute6,
409 bonv.Attribute7 Attribute7,
410 bonv.Attribute8 Attribute8,
411 bonv.Attribute9 Attribute9,
412 bonv.Attribute10 Attribute10,
413 bonv.Attribute11 Attribute11,
414 bonv.Attribute12 Attribute12,
415 bonv.Attribute13 Attribute13,
416 bonv.Attribute14 Attribute14,
417 bonv.Attribute15 Attribute15,
418 bonv.Original_System_Reference Original_System_Reference,
419 'CREATE' Transaction_Type,
420 NULL Return_Status,
424 bom_operation_sequences bos2,
421 NULL Row_Identifier
422 FROM bom_operation_networks_v bonv,
423 bom_operation_sequences bos1,
425 bom_operational_routings rtg,
426 mtl_system_items_kfv item,
427 mtl_parameters org
428 WHERE rtg.routing_sequence_id = bonv.routing_sequence_id
429 AND bos1.Operation_Sequence_Id = bonv.From_Op_Seq_Id
430 AND bos2.Operation_Sequence_Id = bonv.To_Op_Seq_Id
431 AND org.organization_id = rtg.organization_id
432 AND item.organization_id = rtg.organization_id
433 AND item.inventory_item_id = rtg.assembly_item_id
434 AND item.concatenated_segments = P_assembly_item_name
435 AND NVL(rtg.alternate_routing_designator,'$$##$$') = NVL(P_alternate_routing_code,'$$##$$')
436 AND org.Organization_id = p_model_org_id; --myerrams, Filter Records based on Model Org Id
437
438 ---------------------------------End of Cursors Definitions-----------------------------------------------
439
440 i NUMBER;
441 -- Following variables are used for logging messages
442 l_message VARCHAR2(2000) := NULL;
443 l_entity VARCHAR2(3) := NULL;
444 l_msg_index NUMBER;
445 l_message_type VARCHAR2(1);
446
447 --myerrams, Following Variables are used for passing the Return Status to CopyLoader and to Log Assembly Item Name
448 l_X_return_status VARCHAR2(10);
449 l_status_set BOOLEAN := FALSE;
450 l_Assembly_Item_Name VARCHAR2(100);
451
452 --myerrams, Following Variables are used for Looping Logic
453 l_hdr_cnt NUMBER := 0;
454 l_counter NUMBER := 1;
455 l_max_batch_size NUMBER := 200;
456 l_min_index NUMBER;
457 l_max_index NUMBER;
458
459 BEGIN
460
461 IF g_fnd_debug = 'Y' AND
462 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
463 THEN
464 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
465 , G_MODULE_PREFIX || g_api_name || '.begin'
466 , NULL);
467 END IF;
468
469 /*myerrams, to make sure that Return Status is not null when the model org doesn't have any Routings to copy*/
470 x_return_status := 'S';
471
472 --myerrams, To get the number of routings in Model org.
473 SELECT COUNT(*)
474 INTO l_hdr_cnt
475 FROM bom_operational_routings bor,
476 wip_lines wl,
477 mtl_parameters org,
478 mtl_system_items_kfv item1,
479 mtl_system_items_kfv item2,
480 mtl_item_locations_kfv locators
481 WHERE wl.line_id(+) = bor.line_id
482 AND org.organization_id = bor.organization_id
483 AND item1.organization_id = bor.organization_id
484 AND item1.inventory_item_id = bor.assembly_item_id
485 AND item2.inventory_item_id(+) = bor.common_assembly_item_id
486 AND item2.organization_id(+) = bor.organization_id
487 AND locators.inventory_location_id(+) = bor.completion_locator_id
488 AND locators.organization_id (+) = bor.organization_id
489 AND bor.organization_id = p_model_org_id;
490
491 l_min_index := 1;
492
493 IF l_hdr_cnt > l_max_batch_size
494 THEN
495 l_max_index := l_max_batch_size;
496 ELSE
497 l_max_index := l_hdr_cnt;
498 END IF;
499
500 -- Routing Header Record
501 l_rtg_header_tbl.DELETE;
502
503 OPEN rtg_hdr_CUR;
504 LOOP
505 FETCH rtg_hdr_CUR BULK COLLECT INTO l_rtg_header_tbl LIMIT l_max_batch_size;
506 l_counter := 1;
507
508
509 FOR l_Idx IN l_min_index..l_max_index
510 LOOP
511 l_rtg_header_rec := l_rtg_header_tbl(l_counter);
512 l_Assembly_Item_Name := l_rtg_header_rec.Assembly_Item_Name;
513
514 l_rtg_revision_tbl.DELETE;
515 l_operation_tbl.DELETE;
516 l_op_resource_tbl.DELETE;
517 l_sub_resource_tbl.DELETE;
518 l_op_network_tbl.DELETE;
519
520 OPEN rtg_rev_CUR(l_rtg_header_rec.Assembly_Item_Name);
521 i := 1;
522 LOOP
526 END IF;
523 FETCH rtg_rev_CUR INTO l_rtg_revision_tbl(i);
524 IF (rtg_rev_CUR%NOTFOUND) THEN
525 EXIT;
527 i := i + 1;
528 END LOOP;
529 CLOSE rtg_rev_CUR;
530
531 OPEN rtg_op_CUR(l_rtg_header_rec.Assembly_Item_Name,
532 l_rtg_header_rec.Alternate_Routing_Code);
533 i := 1;
534 LOOP
535 FETCH rtg_op_CUR INTO l_operation_tbl(i);
536 IF (rtg_op_CUR%NOTFOUND) THEN
537 EXIT;
538 END IF;
539 i := i + 1;
540 END LOOP;
541 CLOSE rtg_op_CUR;
542
543 OPEN rtg_op_res_CUR(l_rtg_header_rec.Assembly_Item_Name,
544 l_rtg_header_rec.Alternate_Routing_Code);
545 i := 1;
546 LOOP
547 FETCH rtg_op_res_CUR INTO l_op_resource_tbl(i);
548 IF (rtg_op_res_CUR%NOTFOUND) THEN
549 EXIT;
550 END IF;
551 i := i + 1;
552 END LOOP;
553 CLOSE rtg_op_res_CUR;
554 OPEN rtg_sub_op_res_CUR(l_rtg_header_rec.Assembly_Item_Name,
555 l_rtg_header_rec.Alternate_Routing_Code);
556 i := 1;
557 LOOP
558 FETCH rtg_sub_op_res_CUR INTO l_sub_resource_tbl(i);
559 IF (rtg_sub_op_res_CUR%NOTFOUND) THEN
560 EXIT;
561 END IF;
562 i := i + 1;
563 END LOOP;
564 CLOSE rtg_sub_op_res_CUR;
565
566 OPEN rtg_op_networks_CUR(l_rtg_header_rec.Assembly_Item_Name,
567 l_rtg_header_rec.Alternate_Routing_Code);
568 i := 1;
569 LOOP
570 FETCH rtg_op_networks_CUR INTO l_op_network_tbl(i);
571 IF (rtg_op_networks_CUR%NOTFOUND) THEN
572 EXIT;
573 END IF;
574 i := i + 1;
575 END LOOP;
576 CLOSE rtg_op_networks_CUR;
577 BOM_GLOBALS.Set_Caller_Type('MIGRATION');
578 ERROR_HANDLER.Initialize;
579 BOM_RTG_PUB.Process_RTG(p_rtg_header_rec => l_rtg_header_rec
580 , p_rtg_revision_tbl => l_rtg_revision_tbl
581 , p_operation_tbl => l_operation_tbl
582 , p_op_resource_tbl => l_op_resource_tbl
583 , p_sub_resource_tbl => l_sub_resource_tbl
584 , p_op_network_tbl => l_op_network_tbl
585 , p_debug => P_debug
586 , p_output_dir => P_output_dir
587 , p_debug_filename => P_debug_filename
588 , x_rtg_header_rec => X_rtg_header_rec
589 , x_rtg_revision_tbl => X_rtg_revision_tbl
590 , x_operation_tbl => X_operation_tbl
591 , x_op_resource_tbl => X_op_resource_tbl
592 , x_sub_resource_tbl => X_sub_resource_tbl
593 , x_op_network_tbl => X_op_network_tbl
594 , x_return_status => l_X_return_status
595 , x_msg_count => X_msg_count);
596
597 --myerrams, Bug: 5493353; Delete the successfully processed records after the
598 --call to BOM_RTG_PUB.Process_RTG to reduce the memory consumed by the process
599 X_rtg_revision_tbl.DELETE;
600 X_operation_tbl.DELETE;
601 X_op_resource_tbl.DELETE;
602 X_sub_resource_tbl.DELETE;
603 X_op_network_tbl.DELETE;
604
605 commit;
606
607 IF (l_X_return_status <> 'S' and l_status_set = FALSE) THEN
608 X_return_status := l_X_return_status;
609 l_status_set := TRUE;
610 END IF;
611
612 IF (l_X_return_status = 'U' and X_return_status <> 'U') THEN
613 X_return_status := l_X_return_status;
614 END IF;
615
616 FOR i IN 1..X_msg_count LOOP
617 BEGIN
618 ERROR_HANDLER.Get_Message(x_entity_index => l_msg_index,
619 x_entity_id => l_entity,
620 x_message_text => l_message,
621 x_message_type => l_message_type);
622
623 IF g_fnd_debug = 'Y' AND
624 FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
625 THEN
626 FND_LOG.string( FND_LOG.LEVEL_STATEMENT
627 , G_MODULE_PREFIX || g_api_name
628 , TO_CHAR(l_msg_index) || ': '||l_entity ||': '|| l_message_type ||': '||l_message);
629 END IF;
630
631 EXCEPTION
632 WHEN OTHERS THEN
633
634 IF FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
635 THEN
636 FND_LOG.string( FND_LOG.LEVEL_UNEXPECTED
637 , G_MODULE_PREFIX || g_api_name
638 , SQLCODE||' : '||SQLERRM || ' Item: ' || l_Assembly_Item_Name);
639 END IF;
640
641 X_G_msg_data := X_G_msg_data || FND_GLOBAL.NewLine || SQLCODE ||' : ' || SQLERRM || FND_GLOBAL.NewLine || ' Item: ' || l_Assembly_Item_Name ;
642 X_return_status := 'U';
643
644 EXIT;
645 END;
646 END LOOP; --Close X_msg_count LOOP
647
648 IF(X_msg_count > 0 ) THEN
649
650 IF g_fnd_debug = 'Y' AND
651 FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
652 THEN
653 FND_LOG.string( FND_LOG.LEVEL_STATEMENT
654 , G_MODULE_PREFIX || g_api_name
655 , 'X_return_status for Item:' || l_Assembly_Item_Name || ' is: ' || l_X_return_status);
656 END IF;
657
658 END IF;
659
660 l_counter := l_counter + 1;
661 END LOOP; --Close l_min_index..l_max_index LOOP
662
663 l_min_index := l_max_index + 1;
664 IF l_hdr_cnt > (l_max_index + l_max_batch_size)
665 THEN
666 l_max_index := l_max_index + l_max_batch_size;
667 ELSE
668 l_max_index := l_hdr_cnt;
669 END IF;
670
671 EXIT WHEN rtg_hdr_CUR%NOTFOUND;
672 END LOOP;
673 CLOSE rtg_hdr_CUR;
674
675 IF (l_hdr_cnt = 0) THEN -- There are no records to be processed. RETURN with Error Message
676 FND_MESSAGE.SET_NAME('BOM', 'BOM_SETUP_NO_ROWS');
677 FND_MESSAGE.RETRIEVE(X_G_msg_data);
678 END IF;
679
680 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.';
681
682 IF g_fnd_debug = 'Y' AND
683 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
684 THEN
685 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
686 , G_MODULE_PREFIX || g_api_name || '.end'
687 , NULL);
688 END IF;
689
690 END Import_Routing;
691
692 END BOM_RTG_COPYORG_IMP;