1 PACKAGE BODY Ahl_Ump_Bom_Pvt AS
2 /* $Header: AHLVUMBB.pls 120.0.12020000.2 2012/12/07 16:08:12 sareepar noship $*/
3 ---------------------------- FND Logging Constants -----------------------------
4 G_DEBUG_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
5 G_DEBUG_PROC CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
6 G_DEBUG_STMT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
7 G_REQ_TYPE_FORECAST CONSTANT VARCHAR2(30) := 'FORECAST';
8
9 ---------------------------- Commit counter variable ---------------------------
10 g_commit_counter NUMBER := 0;
11
12 ---------------------------- Exception looging variables -----------------------
13 g_err_msg VARCHAR2(4000);
14 g_err_code NUMBER;
15 G_DEBUG_LINE_NUM VARCHAR2(420);
16 ---------------------------- Table Types ---------------------------------------
17 -- number table.
18 TYPE nbr_tbl_type
19 IS
20 TABLE OF NUMBER INDEX BY BINARY_INTEGER;
21 -- varchar2 table.
22 TYPE vchar_tbl_type
23 IS
24 TABLE OF VARCHAR2(400) INDEX BY BINARY_INTEGER;
25 -- date table
26 TYPE date_tbl_type
27 IS
28 TABLE OF DATE INDEX BY BINARY_INTEGER;
29
30
31 ---------------------------- Record type for storing material -----------------
32
33 -- Record type for storing material for each UE
34 TYPE ump_mtrl_rec_type
35 IS
36 RECORD
37 (
38 OPERATING_ORG_ID NUMBER,
39 INVENTORY_ITEM_ID NUMBER,
40 PRIMARY_UOM VARCHAR2(3),
41 QUANTITY NUMBER );
42
43 -- Table type for storing material for each UE
44 TYPE ump_mtrl_tbl_type
45 IS
46 TABLE OF ump_mtrl_rec_type INDEX BY BINARY_INTEGER;
47
48
49
50 -------------------------- Record Type for storing UMP BOM header details ----------------
51 TYPE ump_maint_rec_type
52 IS
53 RECORD
54 (
55 UNIT_EFFECTIVITY_ID NUMBER,
56 SIMULATION_PLAN_ID NUMBER,
57 OBJECT_TYPE VARCHAR2(3),
58 OPERATING_ORG_ID NUMBER,
59 DEPARTMENT_ID NUMBER,
60 DUE_DATE DATE,
61 TOLERANCE_BEF_DATE DATE,
62 MAINTENANCE_TYPE_CODE VARCHAR2(30),
63 TITLE VARCHAR2(500),
64 ITEM_INSTANCE_ID NUMBER,
65 -- Included by debadey for VCP requirement to populate fleet
66 FLEET_HEADER_ID NUMBER
67 );
68
69
70
71 --------------------------- Record type for storing resource -------------------
72 -- Record type that will store one resource unit
73
74 TYPE ump_resource_rec_type
75 IS
76 RECORD
77 ( UNIT_EFFECTIVITY_ID NUMBER,
78 OBJECT_TYPE VARCHAR2(3),
79 SIM_PLAN_ID NUMBER,
80 OPERATING_ORG_ID NUMBER,
81 BOM_RESOURCE_ID NUMBER,
82 DEPARTMENT_ID NUMBER,
83 TOTAL_QUANTITY NUMBER,
84 ASSIGNED_UNITS NUMBER,
85 CMRO_RESOURCE_ID NUMBER );
86
87 -- Table type to store all resource for an UE
88 TYPE ump_resource_tbl_type
89 IS
90 TABLE OF ump_resource_rec_type INDEX BY BINARY_INTEGER;
91
92 -- Variable to store status across different procedures
93 g_ret_status NUMBER;
94
95 --------------------------- End of Type and Global variable declaration------------------------------------------------
96
97 --------------------------- Start of procedure declaration ------------------------------------------------------------
98
99
100
101 /*----------------------------------------------------------------------------------------------------------------------
102 Procedure Name: Process_Mr_Nr_Profile
103 Type: Private
104 Function: This procedure populates the material and resource values for a UE
105 from the MR's NR profile. This procedure is called for every UE.
106
107 Parameters:
108 1. p_mr_header_id - MR header id for the UE
109 2. p_item_instance_id - Item instance id for the UE
110 3. p_unit_effectivity_id - Unit effectivity id
111 p_maint_org_id - maintenance org. Added for bug 13860147
112 4. p_x_consolidated_mat_tbl - Table variable that stores the material requirement for a UE
113 5. p_x_consolidated_res_tbl - Table variable that stores the resource requirement for a UE
114 ------------------------------------------------------------------------------------------------------------------------*/
115 -----------------------------------Start of Procedure Process_Mr_Nr_Profile----------------------------------------------
116
117 PROCEDURE Process_Mr_Nr_Profile
118 ( p_mr_header_id IN NUMBER,
119 p_item_instance_id IN NUMBER,
120 p_unit_effectivity_id IN NUMBER,
121 p_plan_id IN NUMBER,
122 p_maint_org_id IN NUMBER,
123 p_x_consolidated_mat_tbl IN OUT NOCOPY ump_mtrl_tbl_type,
124 p_x_consolidated_res_tbl IN OUT NOCOPY ump_resource_tbl_type )
125 IS
126 l_debug_module VARCHAR2(1000) := 'ahl.plsql.AHL_UMP_BOM_PVT.Process_Mr_Nr_Profile';
127 l_item_present_flag VARCHAR2(1);
128
129 -- Variables for calling NR profile API.
130 nrp_mat_tbl AHL_RA_NR_PROFILE_PVT.nrp_mat_req_tbl_type;
131 nrp_res_tbl AHL_RA_NR_PROFILE_PVT.nrp_res_req_tbl_type;
132
133
134 -- Variables for calling NR profile API
135 l_return_status VARCHAR2(1);
136 l_msg_count NUMBER;
137 l_msg_data VARCHAR2(1000);
138
139 -- Temp variables
140 l_count NUMBER;
141 l_temp NUMBER;
142 l_err_msg VARCHAR2(2000);
143 l_msg_index_out NUMBER;
144
145 BEGIN
146
147 -- Reset table type variables
148 G_DEBUG_LINE_NUM := l_debug_module||': '||3000;
149 nrp_res_tbl.DELETE;
150 nrp_mat_tbl.DELETE;
151
152 -- Log
153 IF (G_DEBUG_PROC >= G_DEBUG_LEVEL)
154 THEN
155 FND_LOG.STRING(G_DEBUG_PROC, l_debug_module, G_DEBUG_LINE_NUM||' Input Parameters to Process_Mr_Nr_Profile - ' ||
156 ' p_mr_header_id = ' || p_mr_header_id ||
157 ' p_item_instance_id = '||p_item_instance_id||
158 ' p_unit_effectivity_id = '||p_unit_effectivity_id||
159 ' p_plan_id = '||p_plan_id);
160 END IF;
161 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Input Parameters to Process_Mr_Nr_Profile - ' ||
162 ' p_mr_header_id = ' || p_mr_header_id ||
163 ' p_item_instance_id = '||p_item_instance_id||
164 ' p_unit_effectivity_id = '||p_unit_effectivity_id||
165 ' p_plan_id = '||p_plan_id);
166
167 G_DEBUG_LINE_NUM := l_debug_module||': '||3010;
168
169 -- Call NR profile material API
170 BEGIN
171 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Calling API AHL_RA_NR_PROFILE_PVT.populate_prof_mat_req');
172 AHL_RA_NR_PROFILE_PVT.populate_prof_mat_req(
173 p_mr_header_id => p_mr_header_id,
174 p_instance_id => p_item_instance_id,
175 p_ue_id => p_unit_effectivity_id,
176 p_plan_id => p_plan_id,
177 x_return_status => l_return_status,
178 x_msg_count => l_msg_count,
179 x_msg_data => l_msg_data,
180 x_prof_mat_req_tbl => nrp_mat_tbl
181 );
182 EXCEPTION
183 WHEN OTHERS
184 THEN
185 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' ****************************** Exception ******************************');
186 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Exception in AHL_RA_NR_PROFILE_PVT.populate_prof_mat_req -'||SQLERRM);
187 g_ret_status := 2;
188 RAISE;
189 END;
190 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Returned from call to AHL_NR_PROFILE_PVT.populate_prof_mat_req:' ||
191 ' x_return_status = ' || l_return_status || ' nrp_mat_tbl.COUNT = '||nrp_mat_tbl.COUNT);
192
193
194 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR)
195 THEN
196 G_DEBUG_LINE_NUM := l_debug_module||': '||3020;
197 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' ****************************** Unexpected Error ******************************');
198 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' AHL_NR_PROFILE_PVT.populate_prof_mat_req Threw Unexpected Error');
199 l_msg_count := Fnd_Msg_Pub.count_msg;
200 IF (l_msg_count > 0) THEN
201 FOR i IN 1..l_msg_count
202 LOOP
203 fnd_msg_pub.get( p_msg_index => i,
204 p_encoded => FND_API.G_FALSE,
205 p_data => l_err_msg,
206 p_msg_index_out => l_msg_index_out);
207
208 fnd_file.put_line(FND_FILE.LOG, 'Error message-'||l_msg_index_out||':' || l_err_msg);
209 END LOOP;
210 END IF;
211 g_ret_status := 1;
212 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
213 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR)
214 THEN
215 G_DEBUG_LINE_NUM := l_debug_module||': '||3030;
216 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' ******************************* Error ******************************');
217 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' AHL_NR_PROFILE_PVT.populate_prof_mat_req Threw Error');
218 l_msg_count := Fnd_Msg_Pub.count_msg;
219 IF (l_msg_count > 0) THEN
220 FOR i IN 1..l_msg_count
221 LOOP
222 fnd_msg_pub.get( p_msg_index => i,
223 p_encoded => FND_API.G_FALSE,
224 p_data => l_err_msg,
225 p_msg_index_out => l_msg_index_out);
226
227 fnd_file.put_line(FND_FILE.LOG, 'Error message-'||l_msg_index_out||':' || l_err_msg);
228 END LOOP;
229 END IF;
230 g_ret_status := 1;
231 RAISE FND_API.G_EXC_ERROR;
232 ELSIF (l_return_status = FND_API.G_RET_STS_SUCCESS)
233 THEN
234 -- Populate p_x_consolidated_mat_tbl variable to be returned
235 G_DEBUG_LINE_NUM := l_debug_module||': '||3040;
236
237 IF (nrp_mat_tbl IS NOT NULL AND nrp_mat_tbl.COUNT > 0)
238 THEN
239 FOR t IN nrp_mat_tbl.FIRST..nrp_mat_tbl.LAST
240 LOOP
241 G_DEBUG_LINE_NUM := l_debug_module||': '||3050;
242
243 -- Log
244 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' nrp_mat_tbl('||t||').INVENTORY_ITEM_ID = '||nrp_mat_tbl(t).INVENTORY_ITEM_ID);
245 -- Added for bug 13860147
246 -- Check whether NR material item is matching the organization to which the UE belongs.
247 BEGIN
248 G_DEBUG_LINE_NUM := l_debug_module||': '||3052;
249 SELECT 1
250 INTO l_temp
251 FROM mtl_system_items_b
252 WHERE inventory_item_id = nrp_mat_tbl(t).INVENTORY_ITEM_ID
253 AND organization_id = p_maint_org_id;
254 EXCEPTION
255 WHEN NO_DATA_FOUND -- No match for item and UE org. So skip this item
256 THEN
257 -- Log
258 G_DEBUG_LINE_NUM := l_debug_module||': '||3054;
259 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' ****************************** WARNING ******************************');
260 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' The inventory item id '||nrp_mat_tbl(t).INVENTORY_ITEM_ID||
261 ' is invalid in the org with org id '||p_maint_org_id);
262 g_ret_status := 1;
263 CONTINUE;
264 END;
265
266 l_item_present_flag := 'N';
267 IF (p_x_consolidated_mat_tbl.COUNT > 0)
268 THEN
269 G_DEBUG_LINE_NUM := l_debug_module||': '||3060;
270
271 FOR l IN p_x_consolidated_mat_tbl.FIRST.. p_x_consolidated_mat_tbl.LAST
272 LOOP
273 G_DEBUG_LINE_NUM := l_debug_module||': '||3070;
274 IF (p_x_consolidated_mat_tbl(l).INVENTORY_ITEM_ID = nrp_mat_tbl(t).INVENTORY_ITEM_ID)
275 THEN
276
277 -- The inventory item is already present in the variable. So simply add up the quantity
278 G_DEBUG_LINE_NUM := l_debug_module||': '||3080;
279 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Existing quantity is '||p_x_consolidated_mat_tbl(l).QUANTITY);
280 p_x_consolidated_mat_tbl(l).QUANTITY := p_x_consolidated_mat_tbl(l).QUANTITY + nrp_mat_tbl(t).QUANTITY;
281 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Bumped up quantity to '||p_x_consolidated_mat_tbl(l).QUANTITY);
282 l_item_present_flag := 'Y';
283 EXIT; -- Stop processing material table
284 END IF;
285 G_DEBUG_LINE_NUM := l_debug_module||': '||3090;
286 END LOOP;
287 G_DEBUG_LINE_NUM := l_debug_module||': '||3100;
288 END IF;
289 G_DEBUG_LINE_NUM := l_debug_module||': '||3110;
290 -- The item is not there in the variable. Hence add the new item
291 IF (l_item_present_flag <>'Y')
292 THEN
293 G_DEBUG_LINE_NUM := l_debug_module||': '||3120;
294 -- Log
295 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' nrp_mat_tbl(t).INVENTORY_ITEM_ID is not there in the variable');
296 IF (p_x_consolidated_mat_tbl.COUNT = 0)
297 THEN
298 G_DEBUG_LINE_NUM := l_debug_module||': '||3110;
299 l_count :=0;
300 ELSE
301 G_DEBUG_LINE_NUM := l_debug_module||': '||3120;
302 l_count := p_x_consolidated_mat_tbl.LAST;
303 END IF;
304 p_x_consolidated_mat_tbl(l_count + 1).INVENTORY_ITEM_ID := nrp_mat_tbl(t).INVENTORY_ITEM_ID;
305 p_x_consolidated_mat_tbl(l_count + 1).QUANTITY := nrp_mat_tbl(t).QUANTITY;
306 p_x_consolidated_mat_tbl(l_count + 1).PRIMARY_UOM := nrp_mat_tbl(t).UOM_CODE;
307 G_DEBUG_LINE_NUM := l_debug_module||': '||3130;
308 END IF;
309
310 G_DEBUG_LINE_NUM := l_debug_module||': '||3140;
311 END LOOP;
312 END IF; -- End of population of the p_x_consolidated_mat_tbl
313 G_DEBUG_LINE_NUM := l_debug_module||': '||3150;
314 -- Log
315 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' End of material processing in Process_Mr_Nr_Profile');
316 END IF;
317
318 G_DEBUG_LINE_NUM := l_debug_module||': '||3160;
319 -- Reset the variables again for calling resource API
320 l_return_status := NULL;
321 l_msg_count:= NULL;
322 l_msg_data:= NULL;
323 G_DEBUG_LINE_NUM := l_debug_module||': '||3170;
324
325 -- Call NR profile resource API
326 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Calling API AHL_RA_NR_PROFILE_PVT.populate_prof_res_req');
327 BEGIN
328 AHL_RA_NR_PROFILE_PVT.populate_prof_res_req(
329 p_mr_header_id => p_mr_header_id,
330 p_instance_id => p_item_instance_id,
331 p_ue_id => p_unit_effectivity_id,
332 p_plan_id => p_plan_id,
333 x_return_status => l_return_status,
334 x_msg_count => l_msg_count,
335 x_msg_data => l_msg_data,
336 x_prof_res_req_tbl => nrp_res_tbl
337 );
338 EXCEPTION
339 WHEN OTHERS
340 THEN
341 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' ****************************** Exception ******************************');
342 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Exception in AHL_RA_NR_PROFILE_PVT.populate_prof_res_req -'||SQLERRM);
343 RAISE;
344 END;
345 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Returned from call to AHL_NR_PROFILE_PVT.populate_prof_res_req:' ||
346 ' x_return_status = ' || l_return_status || ' nrp_res_tbl.COUNT = '||nrp_res_tbl.COUNT);
347 G_DEBUG_LINE_NUM := l_debug_module||': '||3180;
348 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR)
349 THEN
350 G_DEBUG_LINE_NUM := l_debug_module||': '||3185;
351 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' ******************************* Unexpected Error ******************************');
352 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||'AHL_NR_PROFILE_PVT.populate_prof_res_req Threw Unexpected Error');
353 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' message data = '||l_msg_data);
354 l_msg_count := Fnd_Msg_Pub.count_msg;
355 IF (l_msg_count > 0) THEN
356 FOR i IN 1..l_msg_count
357 LOOP
358 fnd_msg_pub.get( p_msg_index => i,
359 p_encoded => FND_API.G_FALSE,
360 p_data => l_err_msg,
361 p_msg_index_out => l_msg_index_out);
362
363 fnd_file.put_line(FND_FILE.LOG, 'Error message-'||l_msg_index_out||':' || l_err_msg);
364 END LOOP;
365 END IF;
366 g_ret_status := 1;
367 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
368 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR)
369 THEN
370 G_DEBUG_LINE_NUM := l_debug_module||': '||3187;
371 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' ******************************* Error ******************************');
372 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||'AHL_NR_PROFILE_PVT.populate_prof_res_req Threw Error');
373 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' message data = '||l_msg_data);
374 l_msg_count := Fnd_Msg_Pub.count_msg;
375 IF (l_msg_count > 0) THEN
376 FOR i IN 1..l_msg_count
377 LOOP
378 fnd_msg_pub.get( p_msg_index => i,
379 p_encoded => FND_API.G_FALSE,
380 p_data => l_err_msg,
381 p_msg_index_out => l_msg_index_out);
382
383 fnd_file.put_line(FND_FILE.LOG, 'Error message-'||l_msg_index_out||':' || l_err_msg);
384 END LOOP;
385 END IF;
386 g_ret_status := 1;
387 RAISE FND_API.G_EXC_ERROR;
388 ELSIF (l_return_status = FND_API.G_RET_STS_SUCCESS)
389 THEN
390 G_DEBUG_LINE_NUM := l_debug_module||': '||3190;
391
392 -- Populate p_x_consolidated_res_tbl variable to be returned
393 -- Check if the resource is already there in the resource variable
394 -- If yes then increase the count else insert a new entry
395 IF(nrp_res_tbl IS NOT NULL AND nrp_res_tbl.COUNT > 0)
396 THEN
397 G_DEBUG_LINE_NUM := l_debug_module||': '||3200;
398 FOR q IN nrp_res_tbl.FIRST..nrp_res_tbl.LAST
399 LOOP
400
401 G_DEBUG_LINE_NUM := l_debug_module||': '||3210;
402 l_item_present_flag := 'N';
403 IF (p_x_consolidated_res_tbl.COUNT > 0)
404 THEN
405 G_DEBUG_LINE_NUM := l_debug_module||': '||3210;
406 FOR m IN p_x_consolidated_res_tbl.FIRST.. p_x_consolidated_res_tbl.LAST
407 LOOP
408 G_DEBUG_LINE_NUM := l_debug_module||': '||3220;
409 -- Log
410 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' nrp_res_tbl('||q||').CMRO_RESOURCE_ID = '||nrp_res_tbl(q).CMRO_RESOURCE_ID);
411
412 IF (p_x_consolidated_res_tbl(m).CMRO_RESOURCE_ID = nrp_res_tbl(q).CMRO_RESOURCE_ID)
413 THEN
414
415 p_x_consolidated_res_tbl(m).TOTAL_QUANTITY :=
416 p_x_consolidated_res_tbl(m).TOTAL_QUANTITY + nrp_res_tbl(q).DURATION;
417 l_item_present_flag := 'Y';
418 G_DEBUG_LINE_NUM := l_debug_module||': '||3240;
419 EXIT;-- Exit to check the next resource in the variable
420 END IF;
421 G_DEBUG_LINE_NUM := l_debug_module||': '||3250;
422 END LOOP;
423 G_DEBUG_LINE_NUM := l_debug_module||': '||3260;
424 END IF;
425 G_DEBUG_LINE_NUM := l_debug_module||': '||3270;
426 IF (l_item_present_flag <>'Y')
427 THEN
428 -- Log
429 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' nrp_res_tbl(q).CMRO_RESOURCE_ID is not there in the variable');
430 IF (p_x_consolidated_res_tbl.COUNT = 0)
431 THEN
432 G_DEBUG_LINE_NUM := l_debug_module||': '||3280;
433 l_count := 0;
434 ELSE
435 G_DEBUG_LINE_NUM := l_debug_module||': '||3290;
436 l_count := p_x_consolidated_res_tbl.LAST;
437 END IF;
438 G_DEBUG_LINE_NUM := l_debug_module||': '||3300;
439 -- Log
440 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Updating in variable '||nrp_res_tbl(q).DURATION);
441 p_x_consolidated_res_tbl(l_count+1).CMRO_RESOURCE_ID := nrp_res_tbl(q).CMRO_RESOURCE_ID;
442 p_x_consolidated_res_tbl(l_count+1).TOTAL_QUANTITY := nrp_res_tbl(q).DURATION;
443 l_item_present_flag := 'N';
444
445 END IF;
446 G_DEBUG_LINE_NUM := l_debug_module||': '||3310;
447 END LOOP;
448 G_DEBUG_LINE_NUM := l_debug_module||': '||3320;
449 END IF;
450 G_DEBUG_LINE_NUM := l_debug_module||': '||3330;
451 -- Log
452 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' End of resource processing in Process_Mr_Nr_Profile');
453 END IF;
454 G_DEBUG_LINE_NUM := l_debug_module||': '||3340;
455 -- Log
456 IF (G_DEBUG_PROC >= G_DEBUG_LEVEL)
457 THEN
458 FND_LOG.STRING(G_DEBUG_PROC, l_debug_module, ' Leaving Process_Mr_Nr_Profile');
459 END IF;
460 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Leaving Process_Mr_Nr_Profile');
461
462 END Process_Mr_Nr_Profile;
463 -----------------------------------End of Procedure Process_Mr_Nr_Profile-------------------------------------------------
464
465
466
467 /*----------------------------------------------------------------------------------------------------------------------
468 Procedure Name: Process_Material_Req
469 Type: Private
470 Function: This procedure populates the material values for a UE from associated routes. This procedure is called for each UE.
471 Parameters:
472
473 2. p_csi_item_instance_id - Item Id
474 3. p_due_date - Due date for the generated UE. This comes from the
475 4. p_org_id - Maintenance org for the UE
476 5. p_route_id_tbl - All routes associated to the MR of the UE
477 6. p_mr_route_tbl - MR route combination for the MR of the UE
478 7. p_r_start_date_active_tbl - Start date of the routes
479 8. p_r_end_date_active_tbl - End date of the routes
480 9. p_x_consolidated_mat_tbl - Table variable that stores the material requirement for the UE
481 ------------------------------------------------------------------------------------------------------------------------*/
482 -----------------------------------Start of Procedure Process_Material_Req-------------------------------------------------
483
484 PROCEDURE Process_Material_Req
485 (
486 p_csi_item_instance_id IN NUMBER,
487 p_due_date IN DATE,
488 p_org_id IN NUMBER,
489 p_route_id_tbl IN nbr_tbl_type,
490 p_mr_route_tbl IN nbr_tbl_type,
491 p_r_start_date_active_tbl IN date_tbl_type,
492 p_r_end_date_active_tbl IN date_tbl_type,
493 p_x_consolidated_mat_tbl IN OUT NOCOPY ump_mtrl_tbl_type)
494 IS
495
496 ----------------------------------------------------- Local Variables -----------------------------------------------------
497 l_debug_module VARCHAR2(1000) := 'ahl.plsql.AHL_UMP_BOM_PVT.Process_Material_Req';
498 l_requirement_date DATE;
499 l_msg_data VARCHAR2(2000);
500 l_msg_count NUMBER;
501 l_return_status VARCHAR2(1);
502 l_item_present_flag VARCHAR2(1);
503 l_temp NUMBER;
504 l_count NUMBER;
505 l_err_msg VARCHAR2(2000);
506 l_msg_index_out NUMBER;
507
508 l_Route_Mtl_Req_Tbl AHL_LTP_MTL_REQ_PVT.Route_Mtl_Req_Tbl_Type;
509
510 BEGIN
511
512 G_DEBUG_LINE_NUM := l_debug_module||': '||4000;
513 -- Log
514 IF (G_DEBUG_PROC >= G_DEBUG_LEVEL)
515 THEN
516 FND_LOG.STRING(G_DEBUG_PROC, l_debug_module, 'In Process_Material_Req. Parameters passed p_csi_item_instance_id ='||p_csi_item_instance_id||
517 ' p_due_date ='||p_due_date||
518 ' p_org_id ='||p_org_id||
519 ' p_route_id_tbl.COUNT = '||p_route_id_tbl.COUNT||
520 ' p_mr_route_tbl.COUNT = '||p_mr_route_tbl.COUNT||
521 ' p_r_start_date_active_tbl.COUNT = '||p_r_start_date_active_tbl.COUNT||
522 ' p_r_end_date_active_tbl.COUNT = '||p_r_end_date_active_tbl.COUNT||
523 ' p_x_consolidated_mat_tbl.COUNT = '||p_x_consolidated_mat_tbl.COUNT);
524 END IF;
525 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' In Process_Material_Req. Parameters passed p_csi_item_instance_id ='||p_csi_item_instance_id||
526 ' p_due_date ='||p_due_date||
527 ' p_org_id ='||p_org_id||
528 ' p_route_id_tbl.COUNT = '||p_route_id_tbl.COUNT||
529 ' p_mr_route_tbl.COUNT = '||p_mr_route_tbl.COUNT||
530 ' p_r_start_date_active_tbl.COUNT = '||p_r_start_date_active_tbl.COUNT||
531 ' p_r_end_date_active_tbl.COUNT = '||p_r_end_date_active_tbl.COUNT||
532 ' p_x_consolidated_mat_tbl.COUNT = '||p_x_consolidated_mat_tbl.COUNT);
533 G_DEBUG_LINE_NUM := l_debug_module||': '||4010;
534 --loop through routes passed
535 FOR k IN p_route_id_tbl.FIRST..p_route_id_tbl.LAST
536 LOOP
537 G_DEBUG_LINE_NUM := l_debug_module||': '||4020;
538 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' k = '||k);
539 IF NOT (TRUNC(NVL(p_r_start_date_active_tbl(k), SYSDATE)) <= TRUNC(SYSDATE)
540 AND TRUNC(NVL(p_r_end_date_active_tbl(k), SYSDATE + 1))>TRUNC(SYSDATE))
541 THEN
542 -- route is expired so skip loop
543 G_DEBUG_LINE_NUM := l_debug_module||': '||4030;
544 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' k = '||k||'Invalid Route - p_route_id :p_mr_route_id : ' ||
545 p_route_id_tbl(k) || ':' || p_mr_route_tbl(k));
546 CONTINUE; -- This route is invalid. Move to the next route in loop
547 ELSE
548 -- route is valid and proceed to get the materials details
549 -- Deb: is the requirement date calculated below correct?
550 G_DEBUG_LINE_NUM := l_debug_module||': '||4040;
551 IF(TRUNC(p_due_date) < TRUNC(SYSDATE))
552 THEN
553 l_requirement_date := SYSDATE;
554 ELSE
555 l_requirement_date := p_due_date;
556 END IF;
557
558 -- Call API
559 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Calling AHL_LTP_MTL_REQ_PVT.Get_Route_Mtl_Req');
560 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Params passed - p_route_id:'||p_route_id_tbl(k)||
561 ' p_mr_route_id:'||p_mr_route_tbl(k)||
562 ' p_item_instance_id:'||p_csi_item_instance_id||
563 ' p_requirement_date:'||l_requirement_date);
564 G_DEBUG_LINE_NUM := l_debug_module||': '||4050;
565 BEGIN
566 AHL_LTP_MTL_REQ_PVT.Get_Route_Mtl_Req (p_api_version => 1.0,
567 p_init_msg_list => FND_API.G_FALSE,
568 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
569 x_return_status => l_return_status,
570 x_msg_count => l_msg_count,
571 x_msg_data => l_msg_data,
572 p_route_id => p_route_id_tbl(k),
573 p_mr_route_id => p_mr_route_tbl(k),
574 p_item_instance_id => p_csi_item_instance_id,
575 p_requirement_date => l_requirement_date,
576 p_request_type => G_REQ_TYPE_FORECAST,
577 x_route_mtl_req_tbl => l_Route_Mtl_Req_Tbl );
578
579 EXCEPTION
580 WHEN OTHERS
581 THEN
582 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' ****************************** Exception ******************************');
583 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Exception in AHL_LTP_MTL_REQ_PVT.Get_Route_Mtl_Req -'||SQLERRM);
584 g_ret_status := 2;
585 RAISE;
586 END;
587 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
588 THEN
589
590 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' ****************************** AHL_LTP_MTL_REQ_PVT.Get_Route_Mtl_Req Threw error ****************************** ');
591 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' message data = '||l_msg_data);
592 l_msg_count := Fnd_Msg_Pub.count_msg;
593 IF (l_msg_count > 0) THEN
594
595 FOR i IN 1..l_msg_count
596 LOOP
597 fnd_msg_pub.get( p_msg_index => i,
598 p_encoded => FND_API.G_FALSE,
599 p_data => l_err_msg,
600 p_msg_index_out => l_msg_index_out);
601
602 fnd_file.put_line(FND_FILE.LOG, 'Error message-'||l_msg_index_out||':' || l_err_msg);
603 END LOOP;
604 END IF;
605 g_ret_status := 1;
606 RAISE FND_API.G_EXC_ERROR;-- Raise Error
607 END IF;
608
609 --Log
610 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' After AHL_LTP_MTL_REQ_PVT.Get_Route_Mtl_Req, l_Route_Mtl_Req_Tbl.COUNT = '||l_Route_Mtl_Req_Tbl.COUNT);
611 G_DEBUG_LINE_NUM := l_debug_module||': '||4060;
612 -- Process the result if no error is raised earlier
613 IF (l_Route_Mtl_Req_Tbl IS NOT NULL AND l_Route_Mtl_Req_Tbl.COUNT > 0)
614 THEN
615 G_DEBUG_LINE_NUM := l_debug_module||': '||4070;
616 FOR j IN l_Route_Mtl_Req_Tbl.FIRST..l_Route_Mtl_Req_Tbl.LAST
617 LOOP
618 G_DEBUG_LINE_NUM := l_debug_module||': '||4080;
619 -- Log
620 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Looping through the route material l_Route_Mtl_Req_Tbl('||j||').INVENTORY_ITEM_ID = '||
621 l_Route_Mtl_Req_Tbl(j).INVENTORY_ITEM_ID||' UOM -'||l_Route_Mtl_Req_Tbl(j).UOM_CODE
622 ||' Quantity -'||l_Route_Mtl_Req_Tbl(j).QUANTITY);
623 -- Check whether route material item is matching the organization to which the UE belongs.
624 BEGIN
625 G_DEBUG_LINE_NUM := l_debug_module||': '||4090;
626 SELECT 1
627 INTO l_temp
628 FROM mtl_system_items_b
629 WHERE INVENTORY_ITEM_ID = l_Route_Mtl_Req_Tbl(j).INVENTORY_ITEM_ID
630 AND organization_id = p_org_id;
631 EXCEPTION
632 WHEN NO_DATA_FOUND-- No match for item and UE org. So skip this item
633 THEN
634 -- Log
635 G_DEBUG_LINE_NUM := l_debug_module||': '||4100;
636 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' ****************************** WARNING ******************************');
637 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' No Entries for the inventory item id and the org passed in'||
638 ' mtl_system_items_b table where p_org_id = '||p_org_id );
639 g_ret_status:=1;
640 CONTINUE;
641 END;
642
643 --Match is there for the item. Go ahead and update the material consolidation variable
644
645 -- Check if the item is already present in our variable
646 G_DEBUG_LINE_NUM := l_debug_module||': '||4110;
647 l_item_present_flag := 'N';
648 IF (p_x_consolidated_mat_tbl IS NOT NULL AND p_x_consolidated_mat_tbl.COUNT > 0)
649 THEN
650 G_DEBUG_LINE_NUM := l_debug_module||': '||4120;
651 FOR l IN p_x_consolidated_mat_tbl.FIRST.. p_x_consolidated_mat_tbl.LAST
652 LOOP
653 G_DEBUG_LINE_NUM := l_debug_module||': '||4130;
654 IF (p_x_consolidated_mat_tbl(l).INVENTORY_ITEM_ID = l_Route_Mtl_Req_Tbl(j).INVENTORY_ITEM_ID)
655 THEN
656 G_DEBUG_LINE_NUM := l_debug_module||': '||4140;
657 -- The inventory item is already present in the variable. So simply add up the quantity
658 p_x_consolidated_mat_tbl(l).QUANTITY := p_x_consolidated_mat_tbl(l).QUANTITY + l_Route_Mtl_Req_Tbl(j).QUANTITY;
659 l_item_present_flag := 'Y';
660 EXIT; -- Stop processing material table
661 END IF;
662 G_DEBUG_LINE_NUM := l_debug_module||': '||4150;
663 END LOOP;
664 G_DEBUG_LINE_NUM := l_debug_module||': '||4160;
665 END IF;
666 -- The item is not there in the variable. Hence add the new item
667 IF (l_item_present_flag <>'Y')
668 THEN
669 G_DEBUG_LINE_NUM := l_debug_module||': '||4170;
670 -- Log
671 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' The Inventory item is not present in the p_x_consolidated_mat_tbl');
672 IF (p_x_consolidated_mat_tbl.COUNT = 0)
673 THEN
674 G_DEBUG_LINE_NUM := l_debug_module||': '||4180;
675 l_count := 0;
676 ELSE
677 G_DEBUG_LINE_NUM := l_debug_module||': '||4190;
678 l_count := p_x_consolidated_mat_tbl.LAST;
679 END IF;
680 G_DEBUG_LINE_NUM := l_debug_module||': '||4200;
681 p_x_consolidated_mat_tbl(l_count + 1).INVENTORY_ITEM_ID := l_Route_Mtl_Req_Tbl(j).INVENTORY_ITEM_ID;
682 p_x_consolidated_mat_tbl(l_count + 1).QUANTITY := l_Route_Mtl_Req_Tbl(j).QUANTITY;
683 p_x_consolidated_mat_tbl(l_count + 1).PRIMARY_UOM := l_Route_Mtl_Req_Tbl(j).UOM_CODE;
684 END IF;
685 G_DEBUG_LINE_NUM := l_debug_module||': '||4210;
686 END LOOP;
687 G_DEBUG_LINE_NUM := l_debug_module||': '||4220;
688
689 END IF;
690 G_DEBUG_LINE_NUM := l_debug_module||': '||4230;
691 END IF;
692 G_DEBUG_LINE_NUM := l_debug_module||': '||4240;
693 END LOOP;
694 G_DEBUG_LINE_NUM := l_debug_module||': '||4250;
695 -- Log
696 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Looping through all p_route_id_tbl is complete.Index k'||
697 ' should be equal to p_route_id_tbl.COUNT = '||p_route_id_tbl.COUNT);
698
699 IF (G_DEBUG_PROC >= G_DEBUG_LEVEL)
700 THEN
701 fnd_log.string (G_DEBUG_PROC, l_debug_module, 'End of procedure Process_Material_Req');
702 END IF;
703 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' End of procedure Process_Material_Req');
704 END Process_Material_Req;
705 -----------------------------------End of Procedure Process_Material_Req-------------------------------------------------
706
707
708
709 /*----------------------------------------------------------------------------------------------------------------------
710 Procedure Name: Perform_Updates
711 Type: Private
712 Function: This procedure populates the tables
713 Parameters:
714
715 2. p_ump_maint_rec - Header table data
716 3. p_x_consolidated_res_tbl - Resource Table data
717 4. p_x_consolidated_mat_tbl - Material table data
718
719 ------------------------------------------------------------------------------------------------------------------------*/
720
721 PROCEDURE Perform_Updates(p_ump_maint_rec IN ump_maint_rec_type,
722 p_x_consolidated_res_tbl IN ump_resource_tbl_type,
723 p_x_consolidated_mat_tbl IN ump_mtrl_tbl_type)
724 AS
725 ----------------------------------------- Start of Procedure Perform_Updates ----------------------------------------------------
726
727 ----------------------------------------- local variables ------------------------
728 l_debug_module VARCHAR2(1000) := 'ahl.plsql.AHL_UMP_BOM_PVT.Perform_Updates';
729 -- Added by debadey for bug 13080499
730 l_error_count NUMBER;
731 ex_dml_errors EXCEPTION;
732 PRAGMA EXCEPTION_INIT(ex_dml_errors, -24381);
733 BEGIN
734 G_DEBUG_LINE_NUM := l_debug_module||': '||7000;
735 IF (G_DEBUG_PROC >= G_DEBUG_LEVEL)
736 THEN
737 fnd_log.string (G_DEBUG_PROC, l_debug_module, G_DEBUG_LINE_NUM||' In procedure Perform_Updates');
738 END IF;
739 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' In procedure Perform_Updates');
740
741 -- Insert/Update/Delete
742 -- Delete record for the root UE from the table
743 IF ((p_x_consolidated_res_tbl IS NOT NULL AND p_x_consolidated_res_tbl.COUNT <>0) OR
744 (p_x_consolidated_mat_tbl IS NOT NULL AND p_x_consolidated_mat_tbl.COUNT <>0))
745 THEN
746
747 -- Insert records for the root UE now
748 -- Log
749 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Inserting record for UE '||p_ump_maint_rec.UNIT_EFFECTIVITY_ID);
750 INSERT INTO AHL_UMP_MAINT_REQMNTS(MAINTENANCE_REQMNT_ID,
751 OBJECT_TYPE,
752 SIMULATION_PLAN_ID,
753 OPERATING_ORG_ID,
754 DUE_DATE,
755 TOLERANCE_BEF_DATE,
756 MAINTENANCE_TYPE_CODE,
757 TITLE,
758 ITEM_INSTANCE_ID,
759 LAST_UPDATE_DATE,
760 LAST_UPDATED_BY,
761 CREATION_DATE,
762 CREATED_BY,
763 LAST_UPDATE_LOGIN,
764 -- Added by debadey for VCP integration
765 FLEET_HEADER_ID
766 )
767 VALUES(p_ump_maint_rec.UNIT_EFFECTIVITY_ID,
768 p_ump_maint_rec.OBJECT_TYPE ,
769 p_ump_maint_rec.SIMULATION_PLAN_ID,
770 p_ump_maint_rec.OPERATING_ORG_ID,
771 p_ump_maint_rec.DUE_DATE,
772 NULL,-- Deb; Need to replace this
773 p_ump_maint_rec.MAINTENANCE_TYPE_CODE,
774 p_ump_maint_rec.TITLE,
775 p_ump_maint_rec.ITEM_INSTANCE_ID,
776 SYSDATE,
777 fnd_global.user_id,
778 SYSDATE,
779 fnd_global.user_id,
780 fnd_global.login_id,
781 p_ump_maint_rec.FLEET_HEADER_ID
782 );
783 G_DEBUG_LINE_NUM := l_debug_module||': '||7040;
784 -- Insert Resource records for the root UE now
785 -- Log
786 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Inserting resources. No of resources - p_x_consolidated_res_tbl.COUNT = '||p_x_consolidated_res_tbl.COUNT);
787 IF (p_x_consolidated_res_tbl.COUNT > 0)
788 THEN
789 G_DEBUG_LINE_NUM := l_debug_module||': '||7050;
790 --Bulk Exception handling added by debadey for bug 13080499
791 BEGIN
792 FORALL x IN p_x_consolidated_res_tbl.FIRST..p_x_consolidated_res_tbl.LAST SAVE EXCEPTIONS
793 INSERT INTO AHL_UMP_RESOURCE_REQMNTS(MAINTENANCE_REQMNT_ID,
794 OBJECT_TYPE,
795 SIMULATION_PLAN_ID,
796 OPERATING_ORG_ID,
797 BOM_RESOURCE_ID,
798 DEPARTMENT_ID,
799 ASSIGNED_UNITS,
800 TOTAL_QUANTITY,
801 CMRO_RESOURCE_ID,
802 LAST_UPDATE_DATE,
803 LAST_UPDATED_BY,
804 CREATION_DATE,
805 CREATED_BY,
806 LAST_UPDATE_LOGIN)
807 VALUES (p_ump_maint_rec.UNIT_EFFECTIVITY_ID,
808 p_ump_maint_rec.OBJECT_TYPE ,
809 p_ump_maint_rec.SIMULATION_PLAN_ID,
810 p_ump_maint_rec.OPERATING_ORG_ID,
811 p_x_consolidated_res_tbl(x).BOM_RESOURCE_ID,
812 p_ump_maint_rec.DEPARTMENT_ID,
813 p_x_consolidated_res_tbl(x).ASSIGNED_UNITS,
814 p_x_consolidated_res_tbl(x).TOTAL_QUANTITY,
815 p_x_consolidated_res_tbl(x).CMRO_RESOURCE_ID,
816 SYSDATE,
817 fnd_global.user_id,
818 SYSDATE,
819 fnd_global.user_id,
820 fnd_global.login_id
821 );
822 EXCEPTION
823 WHEN ex_dml_errors
824 THEN
825 -- Log
826 g_ret_status :=1;
827 l_error_count := 0;
828 l_error_count := SQL%BULK_EXCEPTIONS.count;
829 FOR i IN 1 .. l_error_count
830 LOOP
831 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' ****************************** ERROR ******************************');
832 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Error Inserting for the element number '||SQL%BULK_EXCEPTIONS(i).error_index ||' Message: ' || SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
833 END LOOP;
834 END;
835 -- Reset the resource variable
836 -- Do not reset the variable now. DO it before calling process_ue p_x_consolidated_res_tbl.DELETE;
837 END IF;
838 -- Insert Material records for the root UE now
839 -- Log
840 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Inserting material p_x_consolidated_mat_tbl.COUNT = '||p_x_consolidated_mat_tbl.COUNT);
841 IF (p_x_consolidated_mat_tbl.COUNT > 0)
842 THEN
843 G_DEBUG_LINE_NUM := l_debug_module||': '||7060;
844 BEGIN
845 FORALL x IN p_x_consolidated_mat_tbl.FIRST..p_x_consolidated_mat_tbl.LAST SAVE EXCEPTIONS
846 INSERT INTO AHL_UMP_MATERIAL_REQMNTS(MAINTENANCE_REQMNT_ID,
847 OBJECT_TYPE,
848 SIMULATION_PLAN_ID,
849 OPERATING_ORG_ID,
850 INVENTORY_ITEM_ID,
851 QUANTITY,
852 UOM_CODE,
853 LAST_UPDATE_DATE,
854 LAST_UPDATED_BY,
855 CREATION_DATE,
856 CREATED_BY,
857 LAST_UPDATE_LOGIN
858 )
859 VALUES(p_ump_maint_rec.UNIT_EFFECTIVITY_ID,
860 p_ump_maint_rec.OBJECT_TYPE ,
861 p_ump_maint_rec.SIMULATION_PLAN_ID,
862 p_ump_maint_rec.OPERATING_ORG_ID,
863 p_x_consolidated_mat_tbl(x).INVENTORY_ITEM_ID,
864 p_x_consolidated_mat_tbl(x).QUANTITY,
865 p_x_consolidated_mat_tbl(x).PRIMARY_UOM,
866 SYSDATE,
867 fnd_global.user_id,
868 SYSDATE,
869 fnd_global.user_id,
870 fnd_global.login_id
871 );
872 EXCEPTION
873 WHEN ex_dml_errors
874 THEN
875 -- Log
876 g_ret_status :=1;
877 l_error_count := 0;
878 l_error_count := SQL%BULK_EXCEPTIONS.count;
879 FOR i IN 1 .. l_error_count
880 LOOP
881 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' ********************************** Error **********************************');
882 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Error Inserting for the element number '||SQL%BULK_EXCEPTIONS(i).error_index ||' Message: ' || SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
883 END LOOP;
884 END;
885
886 END IF;
887 -- Commit after every 2001 delete/insert or simply increment the counter.
888 IF (g_commit_counter = 2000)
889 THEN
890 G_DEBUG_LINE_NUM := l_debug_module||': '||7070;
891 COMMIT;
892 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' COMMIT done after 2001 records');
893 g_commit_counter := 0;
894 ELSE
895 G_DEBUG_LINE_NUM := l_debug_module||': '||7080;
896 g_commit_counter := g_commit_counter + 1;
897 END IF;
898 --- End of inserting/updating/deleting
899 ELSE -- material and resource variables are empty
900 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Material and resource variables are empty. No insertion to be made');
901
902 END IF;
903 IF (G_DEBUG_PROC >= G_DEBUG_LEVEL)
904 THEN
905 fnd_log.string (G_DEBUG_PROC, l_debug_module, G_DEBUG_LINE_NUM||' End of procedure Perform_Updates');
906 END IF;
907 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' End of procedure Perform_Updates');
908 EXCEPTION
909 WHEN OTHERS
910 THEN
911 g_ret_status :=2;
912 -- Log
913 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL)
914 THEN
915 g_err_msg := substr(SQLERRM, 1, 3900);
916 g_err_code := SQLCODE;
917 fnd_log.string (G_DEBUG_STMT, l_debug_module, 'Error in procedure Perform_Updates');
918 fnd_log.string (G_DEBUG_STMT, l_debug_module, 'error code is '||g_err_code);
919 fnd_log.string (G_DEBUG_STMT, l_debug_module, 'error message is '||g_err_msg);
920 END IF;
921 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Error in procedure Perform_Updates');
922 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' error code is '||g_err_code);
923 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' error message is '||g_err_msg);
924 RAISE;
925
926 END;
927 ------------------------------------- End of Procedure Perform_Updates -------------------------------------------------
928
929
930
931 /*----------------------------------------------------------------------------------------------------------------------
932 Procedure Name: Process_Ue
933 Type: Private
934 Function: This procedure processes each UE and does all calculations for that and finally makes an insert
935 Parameters:
936
937 p_unit_effectivity_id :Unit Effectivity id
938 p_group_mr_flag :Whether the UE is a stand alone UE or group UE
939 p_mr_header_id :MR header id for the UE. In case of SR UE it will be null.
940 p_item_instance_id :Item intsnace id against the UE
941 p_item_quantity :Item quantity
942 p_maint_org_id :Maintenance org id for the UE
943 p_maint_dept_id :Maintenance department id for the UE
944 p_object_type :Object type should be MR or SR
945 p_due_date :Due date for the UE stored in effectivities table
946 p_ue_mr_downtime :If MR based UE then the MR downtime. This may be null
947 p_mr_title :MR title
948 p_defer_from_ue_id :Flag to denote whether it is a defered UE
949 p_plan_id :Simulation/Primary plan id
950 p_manually_planned_flag :Flag to denote whether manually planned
951 p_sim_plan-flag : Flag to denote primary/simulation plan
952 p_fleet_header_id : Fleet header id against the ue
953 p_x_consolidated_res_tbl:Consolidated resource table which will be updated
954 p_x_consolidated_mat_tbl:Consolidated material table which will be updated
955
956 ------------------------------------------------------------------------------------------------------------------------*/
957 -----------------------------------------Start of Procedure Process_Ue ----------------------------------------------------
958
959 PROCEDURE Process_Ue( p_unit_effectivity_id IN NUMBER,
960 p_group_mr_flag IN VARCHAR2,
961 p_mr_header_id IN NUMBER,
962 p_item_instance_id IN NUMBER,
963 p_item_quantity IN NUMBER,
964 p_maint_org_id IN NUMBER,
965 p_maint_dept_id IN NUMBER,
966 p_object_type IN VARCHAR2,
967 p_due_date IN DATE,
968 p_ue_mr_downtime IN NUMBER,
969 p_mr_title IN VARCHAR2,
970 p_defer_from_ue_id IN NUMBER,
971 p_plan_id IN NUMBER,
972 p_manually_planned_flag IN VARCHAR2,
973 p_sim_plan_flag IN VARCHAR2,
974 p_fleet_header_id IN NUMBER,
975 p_x_consolidated_res_tbl IN OUT NOCOPY ump_resource_tbl_type,
976 p_x_consolidated_mat_tbl IN OUT NOCOPY ump_mtrl_tbl_type
977 )
978 IS
979 ---------------------------------------------------- Cursor Variables ------------------------------------------------------
980
981
982
983 -- Get Route Resources for all child UEs
984 CURSOR aggregate_route_res (p_grp_ue_id IN NUMBER, p_ii_qty IN NUMBER)
985 IS
986 SELECT SUM(resc_hrs) total_resource_hours, MAX(duration) max_resource_duration, ASO_RESOURCE_ID
987 FROM
988 (SELECT DECODE(cost_basis_id, '1', duration*quantity*p_ii_qty, duration*quantity) resc_hrs,
989 ASO_RESOURCE_ID,
990 duration
991 FROM ahl_rt_oper_resources res,
992 ahl_mr_routes r,
993 ahl_routes_v route
994 WHERE r.route_id = res.object_id
995 AND r.mr_header_id IN
996 (SELECT mr.mr_header_id
997 FROM ahl_mr_headers_b mr,
998 ahl_unit_effectivities_b ue
999 WHERE mr.mr_header_id = ue.mr_header_id
1000 AND ue.unit_effectivity_id IN
1001 (SELECT related_ue_id
1002 FROM ahl_ue_relationships
1003 WHERE originator_ue_id = p_grp_ue_id
1004 )
1005 )
1006 AND res.association_type_code = 'ROUTE'
1007 -- start of changes by debadey for bug 13934412
1008 AND route.revision_status_code='COMPLETE'
1009 AND r.route_id = route.route_id
1010 AND TRUNC(SYSDATE) BETWEEN TRUNC(route.start_date_active) AND
1011 TRUNC(NVL(route.end_date_active,SYSDATE+1))
1012 AND route.revision_number IN
1013 (SELECT MAX(revision_number)
1014 FROM ahl_routes_v rv
1015 WHERE route.route_no = rv.route_no
1016 AND revision_status_code = 'COMPLETE'
1017 AND TRUNC(SYSDATE) BETWEEN TRUNC(start_date_active) AND
1018 TRUNC(NVL(end_date_active,SYSDATE+1) ))
1019 -- end of changes by debadey for bug 13934412
1020 )
1021 GROUP BY ASO_RESOURCE_ID;
1022
1023
1024
1025
1026 -- Get Route Resources for all child UEs for simulation plans
1027 CURSOR aggregate_route_res_sim (p_grp_ue_id IN NUMBER, p_ii_qty IN NUMBER)
1028 IS
1029 SELECT SUM(resc_hrs) total_resource_hours, MAX(duration) max_resource_duration, ASO_RESOURCE_ID
1030 FROM
1031 (SELECT DECODE(cost_basis_id, '1', duration*quantity*p_ii_qty, duration*quantity) resc_hrs,
1032 ASO_RESOURCE_ID,
1033 duration
1034 FROM ahl_rt_oper_resources res,
1035 ahl_mr_routes r,
1036 ahl_routes_v route
1037 WHERE r.route_id = res.object_id
1038 AND r.mr_header_id IN
1039 (SELECT mr.mr_header_id
1040 FROM ahl_mr_headers_b mr,
1041 ahl_ue_simulations ue
1042 WHERE mr.mr_header_id = ue.mr_header_id
1043 AND ue.originator_ue_id = p_grp_ue_id
1044 AND ue.parent_ue_id IS NOT NULL -- Deb: get this logic for chld UEs checked
1045 )
1046 AND res.association_type_code = 'ROUTE'
1047 -- start of changes by debadey for bug 13934412
1048 AND route.revision_status_code='COMPLETE'
1049 AND r.route_id = route.route_id
1050 AND TRUNC(SYSDATE) BETWEEN TRUNC(route.start_date_active) AND
1051 TRUNC(NVL(route.end_date_active,SYSDATE+1))
1052 AND route.revision_number IN
1053 (SELECT MAX(revision_number)
1054 FROM ahl_routes_v rv
1055 WHERE route.route_no = rv.route_no
1056 AND revision_status_code = 'COMPLETE'
1057 AND TRUNC(SYSDATE) BETWEEN TRUNC(start_date_active) AND
1058 TRUNC(NVL(end_date_active,SYSDATE+1) ))
1059 -- end of changes by debadey for bug 13934412
1060 )
1061 GROUP BY ASO_RESOURCE_ID;
1062
1063
1064 -- Get Operation Resources for all child UEs
1065 CURSOR aggregate_operation_res (p_grp_ue_id IN NUMBER, p_ii_qty IN NUMBER)
1066 IS
1067 SELECT SUM(resc_hrs), MAX(duration), ASO_RESOURCE_ID
1068 FROM
1069 (SELECT DECODE(cost_basis_id, '1', duration*quantity*p_ii_qty, duration*quantity) resc_hrs,
1070 ASO_RESOURCE_ID,
1071 duration
1072 FROM ahl_rt_oper_resources res,
1073 ahl_route_operations rop,
1074 ahl_mr_routes r,
1075 ahl_routes_v route,
1076 ahl_operations_vl op
1077 WHERE route.route_id = r.route_id
1078 AND r.route_id = rop.route_id
1079 AND res.object_id = rop.operation_id
1080 AND r.mr_header_id IN
1081 ( -- all mr headers belonging to child UEs
1082 SELECT mr.mr_header_id
1083 FROM ahl_mr_headers_b mr,
1084 ahl_unit_effectivities_b ue
1085 WHERE mr.mr_header_id = ue.mr_header_id
1086 AND ue.unit_effectivity_id IN
1087 (SELECT related_ue_id
1088 FROM ahl_ue_relationships
1089 WHERE originator_ue_id = p_grp_ue_id
1090 )
1091 )
1092 AND res.association_type_code = 'OPERATION'
1093 -- do an aggregrate if route has resources.
1094 AND NOT EXISTS
1095 (SELECT 'x'
1096 FROM ahl_rt_oper_resources
1097 WHERE association_type_code = 'ROUTE'
1098 AND object_id = r.route_id
1099 )
1100 -- Added by debadey for bug 13934412
1101 AND op.operation_id = rop.operation_id
1102 AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(op.start_date_active,SYSDATE))
1103 AND TRUNC(NVL(op.end_date_active,SYSDATE+1))
1104 AND op.revision_status_code='COMPLETE'
1105 AND op.revision_number IN
1106 ( SELECT MAX(revision_number)
1107 FROM AHL_OPERATIONS_B_KFV
1108 WHERE concatenated_segments=op.concatenated_segments
1109 AND revision_status_code='COMPLETE'
1110 AND TRUNC(SYSDATE) BETWEEN TRUNC(start_date_active) AND
1111 TRUNC(NVL(end_date_active,SYSDATE+1))
1112 )
1113 -- End of changes b debadey for bug 13934412
1114 AND route.revision_number IN
1115 (SELECT MAX(revision_number)
1116 FROM ahl_routes_v rv
1117 WHERE route.route_no = rv.route_no
1118 AND revision_status_code = 'COMPLETE'
1119 AND TRUNC(SYSDATE) BETWEEN TRUNC(start_date_active) AND
1120 TRUNC(NVL(end_date_active,SYSDATE+1) ))
1121 )
1122 GROUP BY ASO_RESOURCE_ID;
1123
1124
1125 -- Get Operation Resources for all child UEs for simulation plans
1126 CURSOR aggregate_operation_res_sim (p_grp_ue_id IN NUMBER, p_ii_qty IN NUMBER)
1127 IS
1128 SELECT SUM(resc_hrs), MAX(duration), ASO_RESOURCE_ID
1129 FROM
1130 (SELECT DECODE(cost_basis_id, '1', duration*quantity*p_ii_qty, duration*quantity) resc_hrs,
1131 ASO_RESOURCE_ID,
1132 duration
1133 FROM ahl_rt_oper_resources res,
1134 ahl_route_operations rop,
1135 ahl_mr_routes r,
1136 ahl_routes_v route,
1137 ahl_operations_vl op
1138 WHERE route.route_id = r.route_id
1139 AND r.route_id = rop.route_id
1140 AND res.object_id = rop.operation_id
1141 AND r.mr_header_id IN
1142 ( -- all mr headers belonging to child UEs
1143 SELECT mr.mr_header_id
1144 FROM ahl_mr_headers_b mr,
1145 ahl_ue_simulations ue
1146 WHERE mr.mr_header_id = ue.mr_header_id
1147 AND ue.originator_ue_id = p_grp_ue_id
1148 AND ue.parent_ue_id IS NOT NULL
1149 )
1150 AND res.association_type_code = 'OPERATION'
1151 -- do an aggregrate if route has resources.
1152 AND NOT EXISTS
1153 (SELECT 'x'
1154 FROM ahl_rt_oper_resources
1155 WHERE association_type_code = 'ROUTE'
1156 AND object_id = r.route_id
1157 )
1158 -- Added by debadey for bug 13934412
1159 AND op.operation_id = rop.operation_id
1160 AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(op.start_date_active,SYSDATE))
1161 AND TRUNC(NVL(op.end_date_active,SYSDATE+1))
1162 AND op.revision_status_code='COMPLETE'
1163 AND op.revision_number IN
1164 ( SELECT MAX(revision_number)
1165 FROM AHL_OPERATIONS_B_KFV
1166 WHERE concatenated_segments=op.concatenated_segments
1167 AND revision_status_code='COMPLETE'
1168 AND TRUNC(SYSDATE) BETWEEN TRUNC(start_date_active) AND
1169 TRUNC(NVL(end_date_active,SYSDATE+1))
1170 )
1171 -- End of changes by debadey for bug 13934412
1172 AND route.revision_number IN
1173 (SELECT MAX(revision_number)
1174 FROM ahl_routes_v rv
1175 WHERE route.route_no = rv.route_no
1176 AND revision_status_code = 'COMPLETE'
1177 AND TRUNC(SYSDATE) BETWEEN TRUNC(start_date_active)
1178 AND TRUNC(NVL(end_date_active,SYSDATE+1) ))
1179 )
1180 GROUP BY ASO_RESOURCE_ID;
1181
1182
1183 -- aggregate route resources for a MR. Here we get the max duration for each resource as also the sum of the duration.
1184
1185 CURSOR aggregate_mr_route_res (p_mr_header_id IN NUMBER, p_ii_qty IN NUMBER)
1186 IS
1187 SELECT SUM(resc_hrs),
1188 MAX(duration),
1189 ASO_RESOURCE_ID
1190 FROM
1191 (SELECT DECODE(cost_basis_id, '1', duration*quantity*p_ii_qty, duration*quantity) resc_hrs,
1192 ASO_RESOURCE_ID,
1193 duration
1194 FROM ahl_rt_oper_resources res,
1195 ahl_mr_routes r,
1196 ahl_routes_v route
1197 WHERE r.route_id = res.object_id
1198 AND r.mr_header_id = p_mr_header_id
1199 AND res.association_type_code = 'ROUTE'
1200 -- start of changes by debadey for bug 13934412
1201 AND route.revision_status_code='COMPLETE'
1202 AND r.route_id = route.route_id
1203 AND TRUNC(SYSDATE) BETWEEN TRUNC(route.start_date_active) AND
1204 TRUNC(NVL(route.end_date_active,SYSDATE+1))
1205 AND route.revision_number IN
1206 (SELECT MAX(revision_number)
1207 FROM ahl_routes_v rv
1208 WHERE route.route_no = rv.route_no
1209 AND revision_status_code = 'COMPLETE'
1210 AND TRUNC(SYSDATE) BETWEEN TRUNC(start_date_active) AND
1211 TRUNC(NVL(end_date_active,SYSDATE+1) ))
1212 -- end of changes by debadey for bug 13934412
1213 )
1214 GROUP BY ASO_RESOURCE_ID;
1215
1216
1217 -- aggregate operation resources for a MR when route resources do not exist.
1218 CURSOR aggregate_mr_op_res (p_mr_header_id IN NUMBER, p_ii_qty IN NUMBER)
1219 IS
1220 SELECT SUM(resc_hrs),
1221 MAX(duration),
1222 ASO_RESOURCE_ID
1223 FROM
1224 (SELECT DECODE(cost_basis_id, '1', duration*quantity*p_ii_qty, duration*quantity) resc_hrs,
1225 ASO_RESOURCE_ID,
1226 duration
1227 FROM ahl_rt_oper_resources res,
1228 ahl_route_operations rop,
1229 ahl_routes_v route,
1230 ahl_mr_routes r,
1231 ahl_operations_vl op
1232 WHERE route.route_id = r.route_id
1233 AND r.route_id = rop.route_id
1234 AND res.object_id = rop.operation_id
1235 AND r.mr_header_id = p_mr_header_id
1236 AND res.association_type_code = 'OPERATION'
1237 -- do an aggregrate if route has resources.
1238 AND NOT EXISTS
1239 (SELECT 'x'
1240 FROM ahl_rt_oper_resources
1241 WHERE association_type_code = 'ROUTE'
1242 AND object_id = r.route_id
1243 )
1244 -- Added by debadey for bug 13934412
1245 AND op.operation_id = rop.operation_id
1246 AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(op.start_date_active,SYSDATE))
1247 AND TRUNC(NVL(op.end_date_active,SYSDATE+1))
1248 AND op.revision_status_code='COMPLETE'
1249 AND op.revision_number IN
1250 ( SELECT MAX(revision_number)
1251 FROM AHL_OPERATIONS_B_KFV
1252 WHERE concatenated_segments=op.concatenated_segments
1253 AND revision_status_code='COMPLETE'
1254 AND TRUNC(SYSDATE) BETWEEN TRUNC(start_date_active) AND
1255 TRUNC(NVL(end_date_active,SYSDATE+1))
1256 )
1257 -- End of changes b debadey for bug 13934412
1258 AND route.revision_number IN
1259 (SELECT MAX(revision_number)
1260 FROM ahl_routes_v rv
1261 WHERE route.route_no = rv.route_no
1262 AND revision_status_code = 'COMPLETE'
1263 AND TRUNC(SYSDATE) BETWEEN TRUNC(start_date_active)
1264 AND TRUNC(NVL(end_date_active,SYSDATE+1) ))
1265 )
1266
1267 GROUP BY ASO_RESOURCE_ID;
1268
1269
1270
1271 -- Get all child Ue records for PRIMARY plans
1272 CURSOR child_ue_details(p_parent_ue_id NUMBER)
1273 IS
1274 SELECT ue.unit_effectivity_id, ue.mr_header_id, ue.csi_item_instance_id,
1275 ue.due_date, od.mr_maintenance_org_id maintenance_org_id, od.mr_maintenance_dept_id dept_id, b.down_time
1276 FROM ahl_unit_effectivities_b ue, ahl_mr_headers_b b, AHL_UE_ORGS_DEPTS_V od
1277 WHERE ue.unit_effectivity_id IN
1278 (SELECT related_ue_id
1279 FROM ahl_ue_relationships
1280 WHERE originator_ue_id = p_parent_ue_id
1281 )
1282 AND ue.mr_header_id IS NOT NULL
1283 AND ue.mr_header_id = b.mr_header_id
1284 AND od.unit_effectivity_id = p_parent_ue_id
1285 --AND ue.unit_effectivity_id = od.unit_effectivity_id
1286 AND od.SIM_UE_FLAG = 'N';
1287
1288
1289
1290 -- Get all child UE records for SIMULATION plans
1291 CURSOR child_ue_details_sim(p_parent_ue_id NUMBER)
1292 IS
1293 SELECT ue.simulation_ue_id, ue.mr_header_id, ue.csi_item_instance_id,
1294 ue.due_date, od.mr_maintenance_org_id maintenance_org_id, od.mr_maintenance_dept_id dept_id, b.down_time
1295 FROM ahl_ue_simulations ue, ahl_mr_headers_b b, AHL_UE_ORGS_DEPTS_V od
1296 WHERE ue.originator_ue_id = p_parent_ue_id
1297 AND ue.parent_ue_id IS NOT NULL
1298 AND ue.mr_header_id IS NOT NULL
1299 AND ue.mr_header_id = b.mr_header_id
1300 AND od.unit_effectivity_id = p_parent_ue_id -- Get the org and dept of the root ue and not that of child ues
1301 AND od.SIM_UE_FLAG = 'Y';
1302
1303
1304
1305 -- Get mr_route and route_id for a mr_header.
1306 CURSOR ahl_mr_route_csr(p_mr_header_id IN NUMBER)
1307 IS
1308 SELECT rt.mr_route_id,
1309 rt.route_id,
1310 R.start_date_active,
1311 R.end_date_active
1312 FROM ahl_mr_routes rt,
1313 ahl_routes_b R
1314 WHERE rt.route_id = r.route_id
1315 AND rt.mr_header_id = p_mr_header_id;
1316
1317
1318
1319
1320 ---------------------------------------------------- Variables for processing child UEs ------------------------------------------------------
1321 l_child_ue_id_tbl nbr_tbl_type;
1322 l_child_mr_route_id_tbl nbr_tbl_type;
1323 l_child_route_id_tbl nbr_tbl_type;
1324 l_child_r_start_date_tbl date_tbl_type;
1325 l_child_r_end_date_tbl date_tbl_type;
1326 l_child_mr_down_time_tbl nbr_tbl_type;
1327 l_child_mr_header_id_tbl nbr_tbl_type;
1328 l_child_csi_item_instance_tbl nbr_tbl_type;
1329 l_child_due_date_tbl date_tbl_type;
1330 l_child_maint_org_tbl nbr_tbl_type;
1331 l_child_dept_id_tbl nbr_tbl_type;
1332
1333 --l_mr_header_id NUMBER;
1334
1335
1336
1337 ---------------------------------------------------- Variables for processing Root UEs ------------------------------------------------------
1338 l_maintenance_type VARCHAR2(30);
1339 l_resource_hrs_tbl nbr_tbl_type;
1340 l_max_resrc_duration_tbl nbr_tbl_type;
1341 l_aso_resource_id_tbl nbr_tbl_type;
1342
1343
1344 l_ump_maint_rec ump_maint_rec_type;
1345
1346 ----------------------------------------------------- Variables for routes processing -------------------------------------------------------
1347 l_mr_route_id_tbl nbr_tbl_type;
1348 l_route_id_tbl nbr_tbl_type;
1349 l_r_start_date_tbl date_tbl_type;
1350 l_r_end_date_tbl date_tbl_type;
1351
1352
1353 ------------------------------------------------------------- Local Variables ----------------------------------------------------------------
1354 l_buffer_limit NUMBER := 1000;
1355 l_resource_found_flag NUMBER := 0;
1356 l_max_resource_duration NUMBER := 0;
1357 l_max_downtime NUMBER := 0;
1358 l_count NUMBER;
1359 l_conversion_factor NUMBER := 1;
1360 l_temp NUMBER;
1361 l_msg_count NUMBER;
1362 l_err_msg VARCHAR2(2000);
1363 l_msg_index_out NUMBER;
1364
1365 l_debug_module VARCHAR2(1000) := 'ahl.plsql.AHL_UMP_BOM_PVT.Process_Ue';
1366
1367 ----------------------------------------------------- End of variable declaration -----------------------------------------------------------
1368
1369 BEGIN
1370
1371 -- Log
1372 G_DEBUG_LINE_NUM := l_debug_module||': '||2000;
1373 IF (G_DEBUG_PROC >= G_DEBUG_LEVEL)
1374 THEN
1375 FND_LOG.STRING(G_DEBUG_PROC, l_debug_module, 'In Procedure Process_Ue. Input Paramters - '||
1376 ' p_unit_effectivity_id = '||p_unit_effectivity_id ||
1377 ' p_group_mr_flag = '||p_group_mr_flag ||
1378 ' p_mr_header_id = '||p_mr_header_id ||
1379 ' p_item_instance_id = '||p_item_instance_id ||
1380 ' p_item_quantity = '||p_item_quantity ||
1381 ' p_maint_org_id = '||p_maint_org_id||
1382 ' p_maint_dept_id = '||p_maint_dept_id||
1383 ' p_object_type= '||p_object_type||
1384 ' p_due_date = '||p_due_date||
1385 ' p_ue_mr_downtime = '||p_ue_mr_downtime||
1386 ' p_mr_title = '||p_mr_title||
1387 ' p_defer_from_ue_id = '||p_defer_from_ue_id||
1388 ' p_plan_id = '||p_plan_id||
1389 ' p_manually_planned_flag = '||p_manually_planned_flag||
1390 ' p_sim_plan_flag = '||p_sim_plan_flag||
1391 ' p_fleet_header_id = '||p_fleet_header_id||
1392 ' p_x_consolidated_res_tbl.COUNT = '||p_x_consolidated_res_tbl.COUNT||
1393 ' p_x_consolidated_mat_tbl.COUNT = '||p_x_consolidated_mat_tbl.COUNT);
1394 END IF;
1395 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' In Procedure Process_Ue. Input Paramters - '||
1396 ' p_unit_effectivity_id = '||p_unit_effectivity_id ||
1397 ' p_group_mr_flag = '||p_group_mr_flag ||
1398 ' p_mr_header_id = '||p_mr_header_id ||
1399 ' p_item_instance_id = '||p_item_instance_id ||
1400 ' p_item_quantity = '||p_item_quantity ||
1401 ' p_maint_org_id = '||p_maint_org_id||
1402 ' p_maint_dept_id = '||p_maint_dept_id||
1403 ' p_object_type= '||p_object_type||
1404 ' p_due_date = '||p_due_date||
1405 ' p_ue_mr_downtime = '||p_ue_mr_downtime||
1406 ' p_mr_title = '||p_mr_title||
1407 ' p_defer_from_ue_id = '||p_defer_from_ue_id||
1408 ' p_plan_id = '||p_plan_id||
1409 ' p_manually_planned_flag = '||p_manually_planned_flag||
1410 ' p_sim_plan_flag = '||p_sim_plan_flag||
1411 ' p_fleet_header_id = '||p_fleet_header_id||
1412 ' p_x_consolidated_res_tbl.COUNT = '||p_x_consolidated_res_tbl.COUNT||
1413 ' p_x_consolidated_mat_tbl.COUNT = '||p_x_consolidated_mat_tbl.COUNT);
1414
1415 -- If the UE is a group UE, then child UEs need to be processed
1416
1417 -- Establish savepoint
1418 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Establish Savepoint');
1419 SAVEPOINT Process_Ue;
1420 IF (p_group_mr_flag = 'Y')
1421 THEN
1422 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Processing group UE');
1423 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Processing child UE Route resource');
1424 -- Calculate resources first for all the child UEs
1425 -- First process Route resource
1426
1427 G_DEBUG_LINE_NUM := l_debug_module||': '||2010;
1428 IF (p_sim_plan_flag = 'Y')
1429 THEN
1430 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Its a sim UE');
1431 -- Open simulation cursor
1432 G_DEBUG_LINE_NUM := l_debug_module||': '||2020;
1433 OPEN aggregate_route_res_sim(p_unit_effectivity_id, p_item_quantity);
1434 LOOP
1435 G_DEBUG_LINE_NUM := l_debug_module||': '||2030;
1436 FETCH aggregate_route_res_sim
1437 BULK COLLECT
1438 INTO l_resource_hrs_tbl, l_max_resrc_duration_tbl, l_aso_resource_id_tbl
1439 LIMIT l_buffer_limit; -- For each UE, process 2000 routes at a time
1440
1441 EXIT WHEN (l_aso_resource_id_tbl IS NULL OR l_aso_resource_id_tbl.COUNT = 0);
1442
1443 -- Log
1444 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Processing Simlation Plan. ROUTE Resource data fetched.'||
1445 ' l_aso_resource_id_tbl.COUNT = '||l_aso_resource_id_tbl.COUNT);
1446
1447 FOR k IN l_aso_resource_id_tbl.FIRST..l_aso_resource_id_tbl.LAST
1448 LOOP
1449 G_DEBUG_LINE_NUM := l_debug_module||': '||2040;
1450 -- Log
1451 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Looping for resource l_aso_resource_id_tbl('||k||
1452 ') = '|| l_aso_resource_id_tbl(k));
1453 -- Check if the resource is already there in the resource variable
1454 -- If yes then increase the count else insert a new entry
1455 l_resource_found_flag :=0;
1456 IF (p_x_consolidated_res_tbl IS NOT NULL AND p_x_consolidated_res_tbl.COUNT > 0)
1457 THEN
1458 G_DEBUG_LINE_NUM := l_debug_module||': '||2050;
1459 FOR m IN p_x_consolidated_res_tbl.FIRST.. p_x_consolidated_res_tbl.LAST
1460 LOOP
1461 G_DEBUG_LINE_NUM := l_debug_module||': '||2060;
1462 IF (p_x_consolidated_res_tbl(m).CMRO_RESOURCE_ID = l_aso_resource_id_tbl(k))
1463 THEN
1464 G_DEBUG_LINE_NUM := l_debug_module||': '||2070;
1465 p_x_consolidated_res_tbl(m).TOTAL_QUANTITY :=
1466 p_x_consolidated_res_tbl(m).TOTAL_QUANTITY + l_resource_hrs_tbl(k);
1467 l_resource_found_flag := 1;
1468 EXIT;-- Exit to check the next resource in the variable
1469 END IF;
1470 END LOOP;
1471 G_DEBUG_LINE_NUM := l_debug_module||': '||2080;
1472 END IF;
1473 IF (l_resource_found_flag<>1)
1474 THEN
1475 G_DEBUG_LINE_NUM := l_debug_module||': '||2090;
1476 -- Log
1477 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Resource not present in p_x_consolidated_res_tbl variable');
1478 IF (p_x_consolidated_res_tbl.COUNT = 0)
1479 THEN
1480 l_count := 0;
1481 G_DEBUG_LINE_NUM := l_debug_module||': '||2100;
1482 ELSE
1483 l_count := p_x_consolidated_res_tbl.LAST;
1484 G_DEBUG_LINE_NUM := l_debug_module||': '||2110;
1485 END IF;
1486 p_x_consolidated_res_tbl(l_count+1).CMRO_RESOURCE_ID := l_aso_resource_id_tbl(k);
1487 p_x_consolidated_res_tbl(l_count+1).TOTAL_QUANTITY := l_resource_hrs_tbl(k);
1488 l_resource_found_flag :=0;
1489 G_DEBUG_LINE_NUM := l_debug_module||': '||2120;
1490 END IF;
1491
1492
1493 -- Update max resource duration
1494 IF (l_max_resrc_duration_tbl(k)>l_max_resource_duration)
1495 THEN
1496 -- Log
1497 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||'l_max_resource_duration calculated for loop k = '||k||
1498 ' and the value is '||l_max_resrc_duration_tbl(k)||
1499 ' and the current value of l_max_resource_duration='||l_max_resource_duration);
1500
1501 l_max_resource_duration := l_max_resrc_duration_tbl(k);
1502 G_DEBUG_LINE_NUM := l_debug_module||': '||2130;
1503 END IF;
1504
1505 END LOOP; -- End of looping through the resource for child UE
1506 G_DEBUG_LINE_NUM := l_debug_module||': '||2140;
1507 -- Log
1508 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Finished looping through all resources for this route for simulation plan');
1509 END LOOP; -- End of looping through all child UEs for routes for simulation plan
1510 G_DEBUG_LINE_NUM := l_debug_module||': '||2150;
1511 CLOSE aggregate_route_res_sim;
1512 G_DEBUG_LINE_NUM := l_debug_module||': '||2160;
1513 -- Log
1514 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Finished route resource calc for all child UEs');
1515 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Processing Sim Plan. Process OPERATIONS resources for child UEs');
1516 -- Second process Operation Resource
1517 G_DEBUG_LINE_NUM := l_debug_module||': '||2170;
1518 OPEN aggregate_operation_res_sim(p_unit_effectivity_id, p_item_quantity);
1519 LOOP
1520 FETCH aggregate_operation_res_sim
1521 BULK COLLECT
1522 INTO l_resource_hrs_tbl, l_max_resrc_duration_tbl, l_aso_resource_id_tbl
1523 LIMIT l_buffer_limit; -- For each UE, process 2000 routes at a time
1524
1525 -- Log
1526 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' In aggregate_operation_res_sim. Processing l_aso_resource_id_tbl.COUNT = '||l_aso_resource_id_tbl.COUNT );
1527 EXIT WHEN (l_aso_resource_id_tbl IS NULL OR l_aso_resource_id_tbl.COUNT = 0);
1528
1529 FOR k IN l_aso_resource_id_tbl.FIRST..l_aso_resource_id_tbl.LAST
1530 LOOP
1531 G_DEBUG_LINE_NUM := l_debug_module||': '||2180;
1532 -- Check if the resource is already there in the resource variable
1533 -- If yes then increase the count else insert a new entry
1534 -- Log
1535 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Processing resource l_aso_resource_id_tbl('||k||') = '||l_aso_resource_id_tbl(k));
1536 l_resource_found_flag :=0;
1537 IF (p_x_consolidated_res_tbl IS NOT NULL AND p_x_consolidated_res_tbl.COUNT > 0)
1538 THEN
1539 G_DEBUG_LINE_NUM := l_debug_module||': '||2190;
1540 FOR m IN p_x_consolidated_res_tbl.FIRST.. p_x_consolidated_res_tbl.LAST
1541 LOOP
1542 G_DEBUG_LINE_NUM := l_debug_module||': '||2200;
1543 IF (p_x_consolidated_res_tbl(m).CMRO_RESOURCE_ID = l_aso_resource_id_tbl(k))
1544 THEN
1545
1546 p_x_consolidated_res_tbl(m).TOTAL_QUANTITY :=
1547 p_x_consolidated_res_tbl(m).TOTAL_QUANTITY + l_resource_hrs_tbl(k);
1548 l_resource_found_flag := 1;
1549 G_DEBUG_LINE_NUM := l_debug_module||': '||2210;
1550 EXIT;-- Exit to check the next resource in the variable
1551 END IF;
1552 G_DEBUG_LINE_NUM := l_debug_module||': '||2220;
1553 END LOOP;
1554 G_DEBUG_LINE_NUM := l_debug_module||': '||2230;
1555 END IF;
1556 G_DEBUG_LINE_NUM := l_debug_module||': '||2240;
1557 IF (l_resource_found_flag<>1)
1558 THEN
1559 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Resource not present in the p_x_consolidated_res_tbl variable');
1560 IF (p_x_consolidated_res_tbl.COUNT = 0)
1561 THEN
1562 l_count := 0;
1563 ELSE
1564 l_count := p_x_consolidated_res_tbl.LAST;
1565 END IF;
1566 G_DEBUG_LINE_NUM := l_debug_module||': '||2250;
1567 p_x_consolidated_res_tbl(l_count+1).CMRO_RESOURCE_ID := l_aso_resource_id_tbl(k);
1568 p_x_consolidated_res_tbl(l_count+1).TOTAL_QUANTITY := l_resource_hrs_tbl(k);
1569 l_resource_found_flag :=0;
1570 END IF;
1571
1572 -- Update max resource duration
1573 IF (l_max_resrc_duration_tbl(k) IS NOT NULL)
1574 THEN
1575 G_DEBUG_LINE_NUM := l_debug_module||': '||2260;
1576 IF (l_max_resrc_duration_tbl(k)>l_max_resource_duration)
1577 THEN
1578 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' l_max_resource_duration is for index k='||k||
1579 ' and the value is '||l_max_resrc_duration_tbl(k)||
1580 ' Current value of l_max_resource_duration ='||l_max_resource_duration);
1581 l_max_resource_duration := l_max_resrc_duration_tbl(k);
1582 G_DEBUG_LINE_NUM := l_debug_module||': '||2270;
1583 END IF;
1584 END IF;
1585 G_DEBUG_LINE_NUM := l_debug_module||': '||2280;
1586
1587 END LOOP; -- End of looping through the resource for child UE
1588 G_DEBUG_LINE_NUM := l_debug_module||': '||2290;
1589 -- Log
1590 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Sim plan processing -Finished looping through all resources for this route-operation');
1591 END LOOP; -- End of looping through all Child UEs for operations resources
1592 G_DEBUG_LINE_NUM := l_debug_module||': '||2300;
1593 CLOSE aggregate_operation_res_sim;
1594 G_DEBUG_LINE_NUM := l_debug_module||': '||2310;
1595 -- Log
1596 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Finished looping through all OPRATIONS for child UEs. Closed cursor aggregate_operation_res_sim');
1597 G_DEBUG_LINE_NUM := l_debug_module||': '||2320;
1598 ELSE
1599 -- Primary Plan UE processing
1600 -- Log
1601 G_DEBUG_LINE_NUM := l_debug_module||': '||2330;
1602 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Its a primary plan UE');
1603 G_DEBUG_LINE_NUM := l_debug_module||': '||2330;
1604 OPEN aggregate_route_res(p_unit_effectivity_id, p_item_quantity);-- Open primary plan cursor
1605 -- Check p_item_quantity is that of the parent UE. Is that correct?
1606 LOOP
1607 G_DEBUG_LINE_NUM := l_debug_module||': '||2340;
1608 FETCH aggregate_route_res
1609 BULK COLLECT
1610 INTO l_resource_hrs_tbl, l_max_resrc_duration_tbl, l_aso_resource_id_tbl
1611 LIMIT l_buffer_limit; -- For each UE, process 2000 routes at a time
1612 G_DEBUG_LINE_NUM := l_debug_module||': '||2350;
1613
1614 EXIT WHEN (l_aso_resource_id_tbl.COUNT = 0);
1615
1616 FOR k IN l_aso_resource_id_tbl.FIRST..l_aso_resource_id_tbl.LAST
1617 LOOP
1618 G_DEBUG_LINE_NUM := l_debug_module||': '||2360;
1619 -- Log
1620 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' RLooping for resource l_aso_resource_id_tbl('||k||
1621 ') = '|| l_aso_resource_id_tbl(k));
1622 -- Check if the resource is already there in the resource variable
1623 -- If yes then increase the count else insert a new entry
1624 l_resource_found_flag :=0;
1625 IF (p_x_consolidated_res_tbl.COUNT > 0)
1626 THEN
1627 G_DEBUG_LINE_NUM := l_debug_module||': '||2370;
1628 FOR m IN p_x_consolidated_res_tbl.FIRST.. p_x_consolidated_res_tbl.LAST
1629 LOOP
1630
1631 IF (p_x_consolidated_res_tbl(m).CMRO_RESOURCE_ID = l_aso_resource_id_tbl(k))
1632 THEN
1633
1634 G_DEBUG_LINE_NUM := l_debug_module||': '||2380;
1635 p_x_consolidated_res_tbl(m).TOTAL_QUANTITY :=
1636 p_x_consolidated_res_tbl(m).TOTAL_QUANTITY + l_resource_hrs_tbl(k);
1637 l_resource_found_flag := 1;
1638 EXIT;-- Exit to check the next resource in the variable
1639 END IF;
1640 G_DEBUG_LINE_NUM := l_debug_module||': '||2390;
1641 END LOOP;
1642 G_DEBUG_LINE_NUM := l_debug_module||': '||2400;
1643 END IF;
1644 IF (l_resource_found_flag<>1)
1645 THEN
1646 G_DEBUG_LINE_NUM := l_debug_module||': '||2410;
1647 -- Log
1648 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Resource not present in p_x_consolidated_res_tbl variable');
1649 IF (p_x_consolidated_res_tbl.COUNT = 0)
1650 THEN
1651 l_count := 0;
1652 ELSE
1653 l_count := p_x_consolidated_res_tbl.LAST;
1654 G_DEBUG_LINE_NUM := l_debug_module||': '||2420;
1655 END IF;
1656 p_x_consolidated_res_tbl(l_count+1).CMRO_RESOURCE_ID := l_aso_resource_id_tbl(k);
1657 p_x_consolidated_res_tbl(l_count+1).TOTAL_QUANTITY := l_resource_hrs_tbl(k);
1658 l_resource_found_flag :=0;
1659 G_DEBUG_LINE_NUM := l_debug_module||': '||2430;
1660 END IF;
1661
1662
1663 -- Update max resource duration
1664 IF (l_max_resrc_duration_tbl(k)>l_max_resource_duration)
1665 THEN
1666 l_max_resource_duration := l_max_resrc_duration_tbl(k);
1667 G_DEBUG_LINE_NUM := l_debug_module||': '||2450;
1668 END IF;
1669
1670 END LOOP; -- End of looping through the resource for child UE
1671 G_DEBUG_LINE_NUM := l_debug_module||': '||2460;
1672 -- Log
1673 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Finished looping through all resources for this route for primary plan');
1674 END LOOP; -- End of looping through all child UEs for routes
1675 G_DEBUG_LINE_NUM := l_debug_module||': '||2470;
1676 CLOSE aggregate_route_res;
1677 G_DEBUG_LINE_NUM := l_debug_module||': '||2480;
1678 -- Log
1679 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Primary Plan UE. Process OPERATIONS now for child UEs resources');
1680 -- Second process Operation Resource
1681 OPEN aggregate_operation_res(p_unit_effectivity_id, p_item_quantity);
1682 G_DEBUG_LINE_NUM := l_debug_module||': '||2490;
1683 LOOP
1684 G_DEBUG_LINE_NUM := l_debug_module||': '||2500;
1685 FETCH aggregate_operation_res
1686 BULK COLLECT
1687 INTO l_resource_hrs_tbl, l_max_resrc_duration_tbl, l_aso_resource_id_tbl
1688 LIMIT l_buffer_limit; -- For each UE, process 2000 routes at a time
1689 G_DEBUG_LINE_NUM := l_debug_module||': '||2510;
1690
1691 EXIT WHEN (l_aso_resource_id_tbl.COUNT = 0);
1692
1693 FOR k IN l_aso_resource_id_tbl.FIRST..l_aso_resource_id_tbl.LAST
1694 LOOP
1695 G_DEBUG_LINE_NUM := l_debug_module||': '||2520;
1696 -- Check if the resource is already there in the resource variable
1697 -- If yes then increase the count else insert a new entry
1698 -- Log
1699 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Processing Resource l_aso_resource_id_tbl('||k||') = '||l_aso_resource_id_tbl(k));
1700 l_resource_found_flag :=0;
1701 IF (p_x_consolidated_res_tbl IS NOT NULL AND p_x_consolidated_res_tbl.COUNT > 0)
1702 THEN
1703 FOR m IN p_x_consolidated_res_tbl.FIRST.. p_x_consolidated_res_tbl.LAST
1704 LOOP
1705 G_DEBUG_LINE_NUM := l_debug_module||': '||2525;
1706 IF (p_x_consolidated_res_tbl(m).CMRO_RESOURCE_ID = l_aso_resource_id_tbl(k))
1707 THEN
1708 G_DEBUG_LINE_NUM := l_debug_module||': '||2530;
1709
1710 p_x_consolidated_res_tbl(m).TOTAL_QUANTITY :=
1711 p_x_consolidated_res_tbl(m).TOTAL_QUANTITY + l_resource_hrs_tbl(k);
1712 l_resource_found_flag := 1;
1713 EXIT;-- Exit to check the next resource in the variable
1714 END IF;
1715 G_DEBUG_LINE_NUM := l_debug_module||': '||2540;
1716 END LOOP;
1717 G_DEBUG_LINE_NUM := l_debug_module||': '||2550;
1718 END IF;
1719 IF (l_resource_found_flag<>1)
1720 THEN
1721
1722 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Resource not present in the p_x_consolidated_res_tbl variable');
1723 IF (p_x_consolidated_res_tbl.COUNT = 0)
1724 THEN
1725 l_count := 0;
1726 G_DEBUG_LINE_NUM := l_debug_module||': '||2560;
1727 ELSE
1728 l_count := p_x_consolidated_res_tbl.LAST;
1729 END IF;
1730 p_x_consolidated_res_tbl(l_count+1).CMRO_RESOURCE_ID := l_aso_resource_id_tbl(k);
1731 p_x_consolidated_res_tbl(l_count+1).TOTAL_QUANTITY := l_resource_hrs_tbl(k);
1732 l_resource_found_flag :=0;
1733 G_DEBUG_LINE_NUM := l_debug_module||': '||2570;
1734 END IF;
1735
1736
1737 -- Update max resource duration
1738 IF (l_max_resrc_duration_tbl(k)>l_max_resource_duration)
1739 THEN
1740
1741 G_DEBUG_LINE_NUM := l_debug_module||': '||2580;
1742 l_max_resource_duration := l_max_resrc_duration_tbl(k);
1743 END IF;
1744
1745
1746 END LOOP; -- End of looping through the resource for child UE
1747 G_DEBUG_LINE_NUM := l_debug_module||': '||2590;
1748 -- Log
1749 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Finished looping through all resources for this route-operation');
1750 END LOOP; -- End of looping through all Child UEs for operations resources
1751
1752 CLOSE aggregate_operation_res;--new
1753 G_DEBUG_LINE_NUM := l_debug_module||': '||2600;
1754 -- Log
1755 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Primary Plan - Finished Resource cal for Child UEs');
1756
1757 END IF;
1758 G_DEBUG_LINE_NUM := l_debug_module||': '||2610;
1759
1760 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Start Material calculation for Child UEs:');
1761 -- End of iteration for resource processing for child UEs
1762 -- Iterate through the child UEs for material processing
1763 -- Deb: Check if this can be avoided. We are again iterating through all child UEs
1764
1765 IF (p_sim_plan_flag = 'Y')
1766 THEN
1767 G_DEBUG_LINE_NUM := l_debug_module||': '||2620;
1768 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Sim Plan Processing');
1769 OPEN child_ue_details_sim(p_unit_effectivity_id);
1770 FETCH child_ue_details_sim
1771 BULK COLLECT
1772 INTO l_child_ue_id_tbl,
1773 l_child_mr_header_id_tbl,
1774 l_child_csi_item_instance_tbl,
1775 l_child_due_date_tbl,
1776 l_child_maint_org_tbl,
1777 l_child_dept_id_tbl,
1778 l_child_mr_down_time_tbl;
1779 CLOSE child_ue_details_sim;
1780 ELSE
1781 G_DEBUG_LINE_NUM := l_debug_module||': '||2630;
1782 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Primary Plan Processing');
1783 OPEN child_ue_details(p_unit_effectivity_id);
1784 FETCH child_ue_details
1785 BULK COLLECT
1786 INTO l_child_ue_id_tbl,
1787 l_child_mr_header_id_tbl,
1788 l_child_csi_item_instance_tbl,
1789 l_child_due_date_tbl,
1790 l_child_maint_org_tbl,
1791 l_child_dept_id_tbl,
1792 l_child_mr_down_time_tbl;
1793 CLOSE child_ue_details;
1794 END IF;
1795 -- Log
1796 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Fetched Child UE cursor');
1797 G_DEBUG_LINE_NUM := l_debug_module||': '||2635;
1798 -- Loop through all child UE
1799 IF (l_child_mr_header_id_tbl IS NOT NULL AND l_child_mr_header_id_tbl.COUNT > 0)
1800 THEN
1801 FOR p IN l_child_mr_header_id_tbl.FIRST..l_child_mr_header_id_tbl.LAST
1802 LOOP
1803 G_DEBUG_LINE_NUM := l_debug_module||': '||2640;
1804
1805 -- Log
1806 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Child UE cursor Loop.'||
1807 ' l_child_mr_header_id_tbl('||p||') = '||l_child_mr_header_id_tbl(p)||
1808 ' l_child_ue_id_tbl('||p||') = '||l_child_ue_id_tbl(p)||
1809 ' l_child_csi_item_instance_tbl('||p||') = '||l_child_csi_item_instance_tbl(p)||
1810 ' l_child_due_date_tbl('||p||') = '||l_child_due_date_tbl(p)||
1811 ' l_child_maint_org_tbl('||p||') = '||l_child_maint_org_tbl(p)||
1812 ' l_child_dept_id_tbl('||p||') = '||l_child_dept_id_tbl(p)||
1813 ' l_child_mr_down_time_tbl('||p||') = '||l_child_mr_down_time_tbl(p)
1814 );
1815 -- Calculate Material for each child UE
1816
1817 IF (l_child_mr_header_id_tbl(p) IS NOT NULL AND
1818 l_child_maint_org_tbl(p) IS NOT NULL AND
1819 l_child_due_date_tbl(p) IS NOT NULL)
1820 THEN
1821 G_DEBUG_LINE_NUM := l_debug_module||': '||2650;
1822
1823 -- Reset tbl variables
1824 l_child_mr_route_id_tbl.DELETE;
1825 l_child_route_id_tbl.DELETE;
1826 l_child_r_start_date_tbl.DELETE;
1827 l_child_r_end_date_tbl.DELETE;
1828
1829 G_DEBUG_LINE_NUM := l_debug_module||': '||2660;
1830 -- Get route data for each child UE
1831 -- Log
1832 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Get Route data for each child ues mr');
1833 OPEN ahl_mr_route_csr(l_child_mr_header_id_tbl(p));
1834 FETCH ahl_mr_route_csr
1835 BULK COLLECT
1836 INTO l_mr_route_id_tbl,
1837 l_route_id_tbl,
1838 l_r_start_date_tbl,
1839 l_r_end_date_tbl;
1840
1841 CLOSE ahl_mr_route_csr;
1842 IF (l_mr_route_id_tbl.COUNT > 0) -- Routes are there for the UE
1843 THEN
1844 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Calling Process_Material_Req');
1845 G_DEBUG_LINE_NUM := l_debug_module||': '||2670;
1846 --Process Material requirement for the child UE for ALL routes
1847
1848 Process_Material_Req( p_csi_item_instance_id => l_child_csi_item_instance_tbl(p),
1849 p_due_date => l_child_due_date_tbl(p),
1850 p_org_id => l_child_maint_org_tbl(p),
1851 p_route_id_tbl => l_route_id_tbl,
1852 p_mr_route_tbl => l_mr_route_id_tbl,
1853 p_r_start_date_active_tbl => l_r_start_date_tbl,
1854 p_r_end_date_active_tbl => l_r_end_date_tbl,
1855 p_x_consolidated_mat_tbl => p_x_consolidated_mat_tbl );
1856
1857 G_DEBUG_LINE_NUM := l_debug_module||': '||2680;
1858 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Back from Process_Material_Req. p_x_consolidated_mat_tbl.COUNT = '||p_x_consolidated_mat_tbl.COUNT);
1859
1860 END IF;
1861 END IF;
1862 G_DEBUG_LINE_NUM := l_debug_module||': '||2690;
1863
1864 -- Call NR profile data for the child UE
1865 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Calling Process_Mr_Nr_Profile for each child ues mr');
1866
1867 Process_Mr_Nr_Profile(p_mr_header_id => l_child_mr_header_id_tbl(p),
1868 p_item_instance_id => l_child_csi_item_instance_tbl(p),
1869 p_unit_effectivity_id => l_child_ue_id_tbl(p),
1870 p_plan_id => p_plan_id,
1871 p_maint_org_id => l_child_maint_org_tbl(p),-- Added by debadey to check the validity of the material
1872 p_x_consolidated_mat_tbl => p_x_consolidated_mat_tbl,
1873 p_x_consolidated_res_tbl => p_x_consolidated_res_tbl );
1874
1875 -- Log
1876 G_DEBUG_LINE_NUM := l_debug_module||': '||2710;
1877 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Back from Process_Mr_Nr_Profile');
1878
1879 IF (l_max_downtime < l_child_mr_down_time_tbl(p))
1880 THEN
1881 G_DEBUG_LINE_NUM := l_debug_module||': '||2720;
1882 l_max_downtime := l_child_mr_down_time_tbl(p);
1883
1884 END IF;
1885 G_DEBUG_LINE_NUM := l_debug_module||': '||2730;
1886
1887 END LOOP;-- End of looping through child UEs
1888 END IF;
1889
1890 G_DEBUG_LINE_NUM := l_debug_module||': '||2740;
1891
1892 -- Log
1893 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' End of looping through child UEs');
1894 END IF; -- End of calculation for child UEs
1895 G_DEBUG_LINE_NUM := l_debug_module||': '||2750;
1896 -- Calculate Route Resources for the root UE
1897 -- For SR based UEs the following processing is not required
1898 -- Log
1899 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Processing Root UE');
1900 IF(p_object_type = 'MR')
1901 THEN
1902
1903 G_DEBUG_LINE_NUM := l_debug_module||': '||2760;
1904 OPEN aggregate_mr_route_res(p_mr_header_id, p_item_quantity);
1905 LOOP
1906 G_DEBUG_LINE_NUM := l_debug_module||': '||2770;
1907 FETCH aggregate_mr_route_res
1908 BULK COLLECT
1909 INTO l_resource_hrs_tbl, l_max_resrc_duration_tbl, l_aso_resource_id_tbl
1910 LIMIT l_buffer_limit; -- For each root UE, process 2000 routes at a time
1911 --Log
1912 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' aggregate_mr_route_res is fetched. l_aso_resource_id_tbl.COUNT ='||l_aso_resource_id_tbl.COUNT);
1913 EXIT WHEN (l_aso_resource_id_tbl.COUNT = 0);
1914 G_DEBUG_LINE_NUM := l_debug_module||': '||2780;
1915 FOR k IN l_aso_resource_id_tbl.FIRST..l_aso_resource_id_tbl.LAST
1916 LOOP
1917 -- Check if the resource is already there in the resource variable
1918 -- If yes then increase the count else insert a new entry
1919 -- Log
1920 G_DEBUG_LINE_NUM := l_debug_module||': '||2790;
1921
1922 l_resource_found_flag :=0;
1923 IF (p_x_consolidated_res_tbl.COUNT > 0)
1924 THEN
1925 G_DEBUG_LINE_NUM := l_debug_module||': '||2800;
1926 FOR m IN p_x_consolidated_res_tbl.FIRST.. p_x_consolidated_res_tbl.LAST
1927 LOOP
1928 G_DEBUG_LINE_NUM := l_debug_module||': '||2810;
1929 IF (p_x_consolidated_res_tbl(m).CMRO_RESOURCE_ID = l_aso_resource_id_tbl(k))
1930 THEN
1931 -- Log
1932 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Resource is there in the variable p_x_consolidated_res_tbl');
1933 p_x_consolidated_res_tbl(m).TOTAL_QUANTITY :=
1934 p_x_consolidated_res_tbl(m).TOTAL_QUANTITY + l_resource_hrs_tbl(k);
1935 l_resource_found_flag := 1;
1936 EXIT;-- Exit to check the next resource in the variable
1937 END IF;
1938 G_DEBUG_LINE_NUM := l_debug_module||': '||2820;
1939 END LOOP;
1940 END IF;
1941 G_DEBUG_LINE_NUM := l_debug_module||': '||2830;
1942 IF (l_resource_found_flag<>1)
1943 THEN
1944 -- Log
1945 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Resource is not there in the variable p_x_consolidated_res_tbl');
1946 IF (p_x_consolidated_res_tbl.COUNT = 0)
1947 THEN
1948 l_count := 0;
1949 ELSE
1950 l_count := p_x_consolidated_res_tbl.LAST;
1951 END IF;
1952 G_DEBUG_LINE_NUM := l_debug_module||': '||2840;
1953
1954 p_x_consolidated_res_tbl(l_count+1).CMRO_RESOURCE_ID := l_aso_resource_id_tbl(k);
1955 p_x_consolidated_res_tbl(l_count+1).TOTAL_QUANTITY := l_resource_hrs_tbl(k);
1956 l_resource_found_flag :=0;
1957 END IF;
1958
1959 -- Update max resource duration
1960 IF (l_max_resrc_duration_tbl(k)>l_max_resource_duration)
1961 THEN
1962 -- Log
1963 G_DEBUG_LINE_NUM := l_debug_module||': '||2850;
1964 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Updating l_max_resource_duration to '||l_max_resrc_duration_tbl(k));
1965 l_max_resource_duration := l_max_resrc_duration_tbl(k);
1966 END IF;
1967
1968
1969 END LOOP; -- End of looping through the resource for child UE
1970 -- Log
1971 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' End of Loop on resources');
1972 END LOOP;
1973 CLOSE aggregate_mr_route_res;
1974 G_DEBUG_LINE_NUM := l_debug_module||': '||2850;
1975 -- Log
1976 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' End of Loop on MR route resources');
1977 -- Calculate Operations Resources for the root UE
1978
1979 -- Log
1980 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Looping for MR operation resources');
1981 OPEN aggregate_mr_op_res(p_mr_header_id, p_item_quantity);
1982 LOOP
1983 FETCH aggregate_mr_op_res
1984 BULK COLLECT
1985 INTO l_resource_hrs_tbl, l_max_resrc_duration_tbl, l_aso_resource_id_tbl
1986 LIMIT l_buffer_limit; -- For each UE, process 2000 routes at a time
1987
1988 -- Log
1989 G_DEBUG_LINE_NUM := l_debug_module||': '||2852;
1990 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Looping with root UE.l_aso_resource_id_tbl.COUNT = '||l_aso_resource_id_tbl.COUNT);
1991 EXIT WHEN (l_aso_resource_id_tbl.COUNT = 0);
1992
1993 FOR k IN l_aso_resource_id_tbl.FIRST..l_aso_resource_id_tbl.LAST
1994 LOOP
1995 -- Check if the resource is already there in the resource variable
1996 -- If yes then increase the count else insert a new entry
1997 l_resource_found_flag :=0;
1998 IF (p_x_consolidated_res_tbl.COUNT > 0)
1999 THEN
2000 FOR m IN p_x_consolidated_res_tbl.FIRST.. p_x_consolidated_res_tbl.LAST
2001 LOOP
2002 G_DEBUG_LINE_NUM := l_debug_module||': '||2854;
2003 IF (p_x_consolidated_res_tbl(m).CMRO_RESOURCE_ID = l_aso_resource_id_tbl(k))
2004 THEN
2005 -- Log
2006 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Resource exists in p_x_consolidated_res_tbl');
2007 p_x_consolidated_res_tbl(m).TOTAL_QUANTITY :=
2008 p_x_consolidated_res_tbl(m).TOTAL_QUANTITY + l_resource_hrs_tbl(k);
2009 l_resource_found_flag := 1;
2010 EXIT;-- Exit to check the next resource in the variable
2011 END IF;
2012 END LOOP;
2013 END IF;
2014 IF (l_resource_found_flag<>1)
2015 THEN
2016 -- Log
2017 G_DEBUG_LINE_NUM := l_debug_module||': '||2856;
2018 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Resource does not exist in p_x_consolidated_res_tbl');
2019 IF (p_x_consolidated_res_tbl.COUNT = 0)
2020 THEN
2021 l_count := 0;
2022 ELSE
2023 l_count := p_x_consolidated_res_tbl.LAST;
2024 END IF;
2025 p_x_consolidated_res_tbl(l_count+1).CMRO_RESOURCE_ID := l_aso_resource_id_tbl(k);
2026 p_x_consolidated_res_tbl(l_count+1).TOTAL_QUANTITY := l_resource_hrs_tbl(k);
2027 l_resource_found_flag :=0;
2028 END IF;
2029
2030 G_DEBUG_LINE_NUM := l_debug_module||': '||2858;
2031 -- Update max resource duration
2032 IF (l_max_resrc_duration_tbl(k)>l_max_resource_duration)
2033 THEN
2034 -- Log
2035 G_DEBUG_LINE_NUM := l_debug_module||': '||2860;
2036 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' l_max_resource_duration is '||l_max_resource_duration);
2037 l_max_resource_duration := l_max_resrc_duration_tbl(k);
2038 END IF;
2039
2040
2041 END LOOP; -- End of looping through the resource for child UE
2042 -- Log
2043 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' End of loop through operation resources ');
2044 END LOOP; -- End of looping through all root UEs for operations resources
2045 CLOSE aggregate_mr_op_res;
2046 G_DEBUG_LINE_NUM := l_debug_module||': '||2862;
2047
2048 -- Log
2049 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Calculate Material for the root UE ');
2050
2051 -- Calculate Material for the root UE
2052
2053 IF (p_mr_header_id IS NOT NULL AND
2054 p_maint_org_id IS NOT NULL AND
2055 p_due_date IS NOT NULL)
2056 THEN
2057
2058 -- Reset tbl variables
2059 G_DEBUG_LINE_NUM := l_debug_module||': '||2864;
2060 l_mr_route_id_tbl.DELETE;
2061 l_route_id_tbl.DELETE;
2062 l_r_start_date_tbl.DELETE;
2063 l_r_end_date_tbl.DELETE;
2064
2065
2066 -- Get route data for each root UE
2067 OPEN ahl_mr_route_csr(p_mr_header_id);
2068 FETCH ahl_mr_route_csr
2069 BULK COLLECT
2070 INTO l_mr_route_id_tbl,
2071 l_route_id_tbl,
2072 l_r_start_date_tbl,
2073 l_r_end_date_tbl;
2074 G_DEBUG_LINE_NUM := l_debug_module||': '||2866;
2075 CLOSE ahl_mr_route_csr;
2076
2077 -- Log
2078 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' In ahl_mr_route_csr l_mr_route_id_tbl.COUNT = '||l_mr_route_id_tbl.COUNT);
2079 IF (l_mr_route_id_tbl.COUNT > 0) -- Routes are there for the UE
2080 THEN
2081 -- Log
2082 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Calling Process_Material_Req p_x_consolidated_mat_tbl.COUNT '||p_x_consolidated_mat_tbl.COUNT);
2083 --Process Material requirement for the child UE for ALL routes
2084 Process_Material_Req( p_csi_item_instance_id => p_item_instance_id,
2085 p_due_date => p_due_date,
2086 p_org_id => p_maint_org_id,
2087 p_route_id_tbl => l_route_id_tbl,
2088 p_mr_route_tbl => l_mr_route_id_tbl,
2089 p_r_start_date_active_tbl => l_r_start_date_tbl,
2090 p_r_end_date_active_tbl => l_r_end_date_tbl,
2091 p_x_consolidated_mat_tbl => p_x_consolidated_mat_tbl );
2092
2093 -- Log
2094 G_DEBUG_LINE_NUM := l_debug_module||': '||2867;
2095 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Back from Process_Material_Req p_x_consolidated_mat_tbl.COUNT = '||p_x_consolidated_mat_tbl.COUNT);
2096 END IF;
2097 END IF;
2098
2099 -- Log
2100 G_DEBUG_LINE_NUM := l_debug_module||': '||2868;
2101 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Calling Process_Mr_Nr_Profile');
2102
2103 -- Call NR profile for the root UE
2104 Process_Mr_Nr_Profile( p_mr_header_id => p_mr_header_id,
2105 p_item_instance_id => p_item_instance_id,
2106 p_unit_effectivity_id => p_unit_effectivity_id,
2107 p_plan_id => p_plan_id,
2108 p_maint_org_id => p_maint_org_id,-- Added by debadey to check the validity of the material
2109 p_x_consolidated_mat_tbl => p_x_consolidated_mat_tbl,
2110 p_x_consolidated_res_tbl => p_x_consolidated_res_tbl );
2111
2112 -- Log
2113 G_DEBUG_LINE_NUM := l_debug_module||': '||2869;
2114 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Back from Process_Mr_Nr_Profile');
2115
2116 END IF;
2117 -- Update Max downtime
2118 IF (l_max_downtime < p_ue_mr_downtime)
2119 THEN
2120
2121 -- Log
2122 G_DEBUG_LINE_NUM := l_debug_module||': '||2870;
2123 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' l_max_downtime = '||l_max_downtime);
2124 l_max_downtime := p_ue_mr_downtime;
2125
2126 END IF;
2127
2128 -- Derive maintenance type
2129 IF (p_object_type = 'SR')
2130 THEN
2131
2132 -- l_title := p_mr_title;
2133 IF (p_defer_from_ue_id is not null) then
2134 l_maintenance_type := 'DEFER_NONROUTINE';
2135 ELSE
2136 l_maintenance_type := 'NONROUTINE';
2137 END IF;
2138 ELSE
2139 --l_title := p_mr_title;
2140 IF (p_defer_from_ue_id is not null) then
2141 l_maintenance_type := 'DEFER_ROUTINE';
2142 ELSIF (p_manually_planned_flag = 'Y') then
2143 l_maintenance_type := 'ROUTINE_UNKNOWN';
2144 ELSE
2145 l_maintenance_type := 'ROUTINE';
2146 END IF;
2147 END IF;
2148 -- Log
2149 G_DEBUG_LINE_NUM := l_debug_module||': '||2872;
2150 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' l_maintenance_type = '||l_maintenance_type);
2151
2152 -- If max downtime is not there then use max resource duration
2153 l_conversion_factor := l_max_downtime;
2154 IF (l_max_downtime IS NULL OR l_max_downtime = 0)
2155 THEN
2156
2157 -- For bad data if no duration or 0 duartion convert to 1
2158 IF (l_max_resource_duration IS NULL OR l_max_resource_duration=0)
2159 THEN
2160 l_conversion_factor := 1;
2161 ELSE
2162 l_conversion_factor := l_max_resource_duration;
2163 END IF;
2164 END IF;
2165
2166 -- Log
2167 G_DEBUG_LINE_NUM := l_debug_module||': '||2874;
2168 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' l_conversion_factor = '||l_conversion_factor);
2169
2170 -- Convert CMRO resource to BOM resource
2171
2172 IF (p_x_consolidated_res_tbl.COUNT > 0)
2173 THEN
2174 FOR u IN p_x_consolidated_res_tbl.FIRST..p_x_consolidated_res_tbl.LAST
2175 LOOP
2176
2177 -- Log
2178 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' p_x_consolidated_res_tbl('||u||
2179 ').CMRO_RESOURCE_ID ='||p_x_consolidated_res_tbl(u).CMRO_RESOURCE_ID||
2180 ' p_maint_org_id = '||p_maint_org_id||
2181 ' p_maint_dept_id = '||p_maint_dept_id);
2182
2183
2184 /* Commented out as per code review comments
2185 SELECT bom_resource_id
2186 INTO p_x_consolidated_res_tbl(u).BOM_RESOURCE_ID
2187 FROM ahl_resource_mappings
2188 WHERE aso_resource_id = p_x_consolidated_res_tbl(u).CMRO_RESOURCE_ID
2189 AND bom_org_id = p_maint_org_id
2190 AND NVL(department_id, p_maint_dept_id) = p_maint_dept_id; -- Deb: The NVL is used because department is optional for a bom resource...
2191 -- and in such a case where department is not there the bom resource needs to be picked.
2192
2193 */
2194 /* SELECT bom_resource_id
2195 INTO p_x_consolidated_res_tbl(u).BOM_RESOURCE_ID
2196 FROM ahl_resource_mappings map, bom_department_resources bdr
2197 WHERE map.aso_resource_id = p_x_consolidated_res_tbl(u).CMRO_RESOURCE_ID
2198 AND bdr.department_id = p_maint_dept_id
2199 AND map.bom_resource_id = bdr.resource_id
2200 AND NVL(p_maint_dept_id, bdr.department_id) = bdr.department_id
2201 AND ROWNUM < 2;
2202 -- Populate assigned units for the resource
2203 -- Calculate Assigned Units from total_quantity
2204 p_x_consolidated_res_tbl(u).ASSIGNED_UNITS
2205 := p_x_consolidated_res_tbl(u).TOTAL_QUANTITY/l_conversion_factor; */
2206 BEGIN
2207 -- Added to avoid division by zero
2208 IF (l_conversion_factor = 0 OR l_conversion_factor IS NULL)
2209 THEN
2210 l_conversion_factor := 1;
2211 END IF;
2212 G_DEBUG_LINE_NUM := l_debug_module||': '||2876;
2213 IF (p_maint_dept_id IS NOT NULL)
2214 THEN
2215
2216 SELECT bom_resource_id, p_maint_dept_id
2217 INTO p_x_consolidated_res_tbl(u).BOM_RESOURCE_ID, p_x_consolidated_res_tbl(u).DEPARTMENT_ID
2218 FROM ahl_resource_mappings map
2219 WHERE map.aso_resource_id = p_x_consolidated_res_tbl(u).CMRO_RESOURCE_ID
2220 AND map.bom_org_id = p_maint_org_id
2221 AND NVL(map.department_id, p_maint_dept_id) = p_maint_dept_id;
2222 G_DEBUG_LINE_NUM := l_debug_module||': '||2877;
2223 SELECT 1
2224 INTO l_temp
2225 FROM bom_department_resources
2226 WHERE resource_id = p_x_consolidated_res_tbl(u).BOM_RESOURCE_ID
2227 AND department_id = p_maint_dept_id
2228 AND ROWNUM < 2;
2229 G_DEBUG_LINE_NUM := l_debug_module||': '||2878;
2230 p_x_consolidated_res_tbl(u).ASSIGNED_UNITS
2231 := p_x_consolidated_res_tbl(u).TOTAL_QUANTITY/l_conversion_factor;
2232
2233 ELSE
2234 -- Log
2235 G_DEBUG_LINE_NUM := l_debug_module||': '||2880;
2236 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' p_maint_dept_id IS NULL');
2237
2238 SELECT map.bom_resource_id, bdr.department_id
2239 INTO p_x_consolidated_res_tbl(u).BOM_RESOURCE_ID, p_x_consolidated_res_tbl(u).DEPARTMENT_ID
2240 FROM ahl_resource_mappings map, bom_department_resources bdr
2241 WHERE map.aso_resource_id = p_x_consolidated_res_tbl(u).CMRO_RESOURCE_ID
2242 AND map.bom_org_id = p_maint_org_id
2243 AND map.bom_resource_id = bdr.resource_id
2244 AND NVL(map.department_id,bdr.department_id) = bdr.department_id
2245 AND ROWNUM < 2;
2246
2247 p_x_consolidated_res_tbl(u).ASSIGNED_UNITS
2248 := p_x_consolidated_res_tbl(u).TOTAL_QUANTITY/l_conversion_factor;
2249 END IF;
2250
2251 EXCEPTION
2252 WHEN NO_DATA_FOUND
2253 THEN
2254 -- Log
2255 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' ****************************** WARNING ******************************');
2256 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' The CMRO resource has no corresponding BOM resource');
2257 g_ret_status := 1;
2258 -- The CMRO resource has no corresponding BOM resource.
2259 -- Hence delete the entry from the resource variable
2260 p_x_consolidated_res_tbl.DELETE(u);
2261 END;
2262
2263 END LOOP;
2264 END IF;
2265 G_DEBUG_LINE_NUM := l_debug_module||': '||2882;
2266 -- Populate header data for UMP_MAINTENANCE_REQMNTS table
2267 l_ump_maint_rec.UNIT_EFFECTIVITY_ID := p_unit_effectivity_id ;
2268 l_ump_maint_rec.SIMULATION_PLAN_ID := p_plan_id;--l_primary_plan_id;--l_plan_id_tbl(i); -- plan_id
2269 IF (p_sim_plan_flag = 'Y')
2270 THEN
2271 l_ump_maint_rec.OBJECT_TYPE := 'SIM';-- Simulation UE
2272 ELSE
2273 l_ump_maint_rec.OBJECT_TYPE := 'UE'; -- Primary UE
2274 END IF;
2275
2276 l_ump_maint_rec.OPERATING_ORG_ID := p_maint_org_id;
2277 l_ump_maint_rec.DUE_DATE := p_due_date; -- Root UE due date
2278 l_ump_maint_rec.MAINTENANCE_TYPE_CODE:= l_maintenance_type;
2279 l_ump_maint_rec.TITLE := p_mr_title;
2280 l_ump_maint_rec.ITEM_INSTANCE_ID := p_item_instance_id ; -- Root UE instance
2281 l_ump_maint_rec.DEPARTMENT_ID := p_maint_dept_id;
2282 -- Added by debadey for VCP integration
2283 l_ump_maint_rec.FLEET_HEADER_ID := p_fleet_header_id;
2284 G_DEBUG_LINE_NUM := l_debug_module||': '||2884;
2285 --
2286 -- Log
2287 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' l_ump_maint_rec.UNIT_EFFECTIVITY_ID = '||l_ump_maint_rec.UNIT_EFFECTIVITY_ID||
2288 'l_ump_maint_rec.SIMULATION_PLAN_ID = '||l_ump_maint_rec.SIMULATION_PLAN_ID||
2289 'l_ump_maint_rec.OBJECT_TYPE = '||l_ump_maint_rec.OBJECT_TYPE||
2290 'l_ump_maint_rec.DUE_DATE = '|| l_ump_maint_rec.DUE_DATE||
2291 'l_ump_maint_rec.MAINTENANCE_TYPE_CODE = '|| l_ump_maint_rec.MAINTENANCE_TYPE_CODE||
2292 'l_ump_maint_rec.TITLE = '||l_ump_maint_rec.TITLE||
2293 'l_ump_maint_rec.ITEM_INSTANCE_ID = '||l_ump_maint_rec.ITEM_INSTANCE_ID||
2294 'l_ump_maint_rec.DEPARTMENT_ID = '||l_ump_maint_rec.DEPARTMENT_ID);
2295
2296 -- Call perform updates procedure
2297 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Calling Perform_Updates');
2298 Perform_Updates(l_ump_maint_rec,
2299 p_x_consolidated_res_tbl,
2300 p_x_consolidated_mat_tbl);
2301 G_DEBUG_LINE_NUM := l_debug_module||': '||2885;
2302 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Back from Perform_Updates');
2303 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' UE processing is complete');
2304 IF (G_DEBUG_PROC >= G_DEBUG_LEVEL)
2305 THEN
2306 fnd_log.string (G_DEBUG_PROC, l_debug_module, G_DEBUG_LINE_NUM||' End of procedure Process_Ue');
2307 END IF;
2308
2309 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' End of procedure Process_Ue');
2310 EXCEPTION
2311 WHEN FND_API.G_EXC_ERROR THEN
2312 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM|| ' Caught FND_API.G_EXC_ERROR');
2313 g_ret_status := 1;
2314 l_msg_count := Fnd_Msg_Pub.count_msg;
2315 IF (l_msg_count > 0) THEN
2316 FOR i IN 1..l_msg_count
2317 LOOP
2318 fnd_msg_pub.get( p_msg_index => i,
2319 p_encoded => FND_API.G_FALSE,
2320 p_data => l_err_msg,
2321 p_msg_index_out => l_msg_index_out);
2322
2323 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM|| 'Error message-'||l_msg_index_out||':' || l_err_msg);
2324 END LOOP;
2325 END IF;
2326
2327 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2328 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM|| ' Caught G_EXC_UNEXPECTED_ERROR');
2329 g_ret_status := 1;
2330 l_msg_count := Fnd_Msg_Pub.count_msg;
2331 IF (l_msg_count > 0) THEN
2332 FOR i IN 1..l_msg_count
2333 LOOP
2334 fnd_msg_pub.get( p_msg_index => i,
2335 p_encoded => FND_API.G_FALSE,
2336 p_data => l_err_msg,
2337 p_msg_index_out => l_msg_index_out);
2338
2339 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM|| 'Error message-'||l_msg_index_out||':' || l_err_msg);
2340 END LOOP;
2341 END IF;
2342 WHEN OTHERS THEN
2343 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM|| ' Caught OTHERS');
2344 ROLLBACK TO Process_Ue;
2345 g_ret_status := 2;
2346 l_msg_count := Fnd_Msg_Pub.count_msg;
2347 IF (l_msg_count > 0) THEN
2348 FOR i IN 1..l_msg_count
2349 LOOP
2350 fnd_msg_pub.get( p_msg_index => i,
2351 p_encoded => FND_API.G_FALSE,
2352 p_data => l_err_msg,
2353 p_msg_index_out => l_msg_index_out);
2354
2355 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM|| 'Error message-'||l_msg_index_out||':' || l_err_msg);
2356 END LOOP;
2357 END IF;
2358 RAISE;
2359 END Process_Ue;
2360 ----------------------------------------- End of Procedure Process_Ue ----------------------------------------------------
2361
2362
2363
2364 /*----------------------------------------------------------------------------------------------------------------------
2365 Procedure Name: Create_Update_Ump_Bom
2366 Type: Public
2367 Function: This procedure is called by concurrent program to generate UMP BOM
2368 Parameters:
2369 1. errbuf - Error message if any
2370 2. retcode - retcode equal to 0 implies succesfull processing
2371 3. p_sim_plan_name - Simulation Plan name for which the BOM has to be generated
2372 4. p_all_flag - A values of 'Y' indicates that the proc needs to be run for all
2373 active simulation and primary plans
2374 ------------------------------------------------------------------------------------------------------------------------*/
2375 -----------------------------------------Start of Procedure Create_Update_Ump_Bom ----------------------------------------
2376 PROCEDURE Create_Update_Ump_Bom
2377 (
2378 errbuf OUT NOCOPY VARCHAR2,
2379 retcode OUT NOCOPY NUMBER,
2380 p_sim_plan_name IN VARCHAR2,
2381 p_all_flag IN VARCHAR2 )
2382 IS
2383
2384
2385 ------------------------------------------------------Cursor declaration -------------------------------------------------
2386
2387 -- Get all mr header ids for which an UE is there in the effectivites table for the PRIMARY plan
2388 CURSOR ahl_mr_headers_csr
2389 IS
2390 SELECT mr.mr_header_id,
2391 mr.title
2392 FROM ahl_mr_headers_b mr
2393 WHERE mr_status_code = 'COMPLETE'
2394 AND EXISTS
2395 (SELECT 1
2396 FROM ahl_unit_effectivities_b ueb
2397 WHERE ueb.mr_header_id = mr.mr_header_id
2398 AND (ueb.status_code IS NULL
2399 OR ueb.status_code = 'INIT-DUE')
2400 );
2401
2402
2403
2404
2405 -- Get all mr header ids for which an UE is there in the effectivites table for the SIMULATION plan(s)
2406 -- If p_plan_id is NULL then the cursor will pick MRs for all simulation plans
2407
2408 CURSOR ahl_sim_mr_headers_csr(p_plan_id NUMBER)
2409 IS
2410 SELECT mr.mr_header_id,
2411 mr.title
2412 FROM ahl_mr_headers_b mr
2413 WHERE mr_status_code = 'COMPLETE'
2414 AND EXISTS
2415 (SELECT 1
2416 FROM ahl_ue_simulations ues
2417 WHERE ues.mr_header_id = mr.mr_header_id
2418 AND (ues.status_code IS NULL
2419 OR ues.status_code = 'INIT-DUE')
2420 AND NVL(p_plan_id, simulation_plan_id) = simulation_plan_id);
2421
2422
2423
2424
2425 -- Get all applicable root UEs for PRIMARY plan for an MR
2426
2427 CURSOR ue_mr_csr(p_mr_header_id NUMBER)
2428 IS
2429 SELECT * FROM (
2430 SELECT ue.unit_effectivity_id, ue.due_date, ue.mr_header_id,
2431 ue.csi_item_instance_id, ue.object_type, ue.defer_from_ue_id,
2432 mr.title title, ue.manually_planned_flag, ii.quantity,
2433 (SELECT 'Y' from ahl_ue_relationships WHERE originator_ue_id = ue.unit_effectivity_id AND ROWNUM < 2) grp_flag,
2434 mr.down_time, mr.uom_code, od.mr_maintenance_org_id maintenance_org_id, od.mr_maintenance_dept_id dept_id, ue.fleet_header_id
2435 FROM AHL_UNIT_EFFECTIVITIES_B UE, CSI_ITEM_INSTANCES II, AHL_MR_HEADERS_B mr, AHL_UE_ORGS_DEPTS_V od
2436 WHERE UE.csi_item_instance_id = II.instance_id
2437 AND UE.mr_header_id = mr.mr_header_id
2438 AND UE.application_usg_code = 'AHL'
2439 AND UE.object_type = 'MR'
2440 AND UE.mr_header_id = p_mr_header_id
2441 AND (UE.status_code IS NULL OR UE.status_code = 'INIT-DUE')
2442 AND UE.due_date IS NOT NULL
2443 AND NOT EXISTS (SELECT 'x' FROM ahl_workorders wo, ahl_visit_tasks_b tsk
2444 WHERE wo.visit_task_id = tsk.visit_task_id
2445 AND tsk.task_type_code = 'SUMMARY'
2446 AND tsk.status_code NOT IN ('DELETED','CANCELLED')
2447 AND tsk.unit_effectivity_id = UE.unit_effectivity_id)
2448 AND NOT EXISTS (SELECT 'x' FROM ahl_ue_relationships WHERE related_ue_id = UE.unit_effectivity_id)
2449 AND od.unit_effectivity_id = ue.unit_effectivity_id
2450 AND od.sim_ue_flag = 'N'
2451 AND od.mr_maintenance_org_id IS NOT NULL -- Maintenance org and dept will always be there. Otherwise its an invalid UE
2452 --AND od.mr_maintenance_dept_id IS NOT NULL -- removed dept check as dept can be null for ues not associated to fleet
2453 ORDER BY ue.csi_item_instance_id, ue.due_date);
2454
2455
2456
2457
2458 -- Get all applicable root UEs for SIMULATION plan(s) for an MR
2459 -- If p_plan_id is NULL then the cursor will pick UEs for all simulation plans
2460
2461 CURSOR ue_sim_mr_csr(p_mr_header_id NUMBER, p_sim_plan_id NUMBER)
2462 IS
2463 SELECT * FROM (
2464 SELECT ues.simulation_ue_id,
2465 ues.simulation_plan_id,
2466 ues.due_date,
2467 ues.mr_header_id,
2468 ues.csi_item_instance_id,
2469 'MR' object_type,
2470 ues.defer_from_ue_id,
2471 mr.title title,
2472 DECODE(ues.unit_effectivity_id, NULL, 'N',
2473 (SELECT manually_planned_flag
2474 FROM ahl_unit_effectivities_b
2475 WHERE unit_effectivity_id = ues.unit_effectivity_id)) manually_planned_flag,
2476 ii.quantity,
2477 (SELECT 'Y' from ahl_ue_simulations WHERE originator_ue_id = ues.simulation_ue_id AND ROWNUM < 2) grp_flag,
2478 mr.down_time,
2479 mr.uom_code,
2480 od.MR_MAINTENANCE_ORG_ID maintenance_org_id,
2481 od.MR_MAINTENANCE_DEPT_ID dept_id,
2482 ues.fleet_header_id
2483 FROM AHL_UE_SIMULATIONS UES, CSI_ITEM_INSTANCES II, AHL_MR_HEADERS_B mr, AHL_UE_ORGS_DEPTS_V od
2484 WHERE UES.csi_item_instance_id = II.instance_id
2485 AND UES.mr_header_id = mr.mr_header_id
2486 AND UES.cs_incident_id IS NULL
2487 AND UES.mr_header_id = p_mr_header_id
2488 AND (UES.status_code IS NULL OR UES.status_code = 'INIT-DUE')
2489 AND UES.due_date IS NOT NULL
2490 AND NOT EXISTS (SELECT 'x' FROM ahl_workorders wo, ahl_visit_tasks_b tsk
2491 WHERE wo.visit_task_id = tsk.visit_task_id
2492 AND tsk.task_type_code = 'SUMMARY'
2493 AND tsk.status_code NOT IN ('DELETED','CANCELLED')
2494 AND tsk.unit_effectivity_id = ues.simulation_ue_id)
2495 AND UES.parent_ue_id IS NULL
2496 AND od.unit_effectivity_id = ues.simulation_ue_id
2497 AND od.sim_ue_flag = 'Y'
2498 AND od.MR_MAINTENANCE_ORG_ID IS NOT NULL -- Maintenance org and dept will always be there. Otherwise its an invalid UE
2499 --AND od.MR_MAINTENANCE_DEPT_ID IS NOT NULL
2500 AND NVL(p_sim_plan_id, UES.simulation_plan_id) = UES.simulation_plan_id
2501 ORDER BY ues.csi_item_instance_id, ues.due_date);
2502
2503
2504
2505
2506 -- Get all applicable root UEs for PRIMARY plan for an SR
2507 CURSOR ue_sr_csr IS
2508 SELECT * FROM (
2509 SELECT ue.unit_effectivity_id, ue.due_date, ue.mr_header_id,
2510 ue.csi_item_instance_id, ue.object_type, ue.defer_from_ue_id,
2511 ue.manually_planned_flag,
2512 (SELECT CIT.NAME || '-' || CS.INCIDENT_NUMBER
2513 FROM CS_INCIDENTS_ALL_B CS, CS_INCIDENT_TYPES_TL CIT
2514 WHERE CS.incident_id = ue.cs_incident_id
2515 AND CS.INCIDENT_TYPE_ID = CIT.INCIDENT_TYPE_ID
2516 AND CIT.LANGUAGE = USERENV('LANG')) sr_title,
2517 ii.quantity,
2518 (SELECT 'Y' from ahl_ue_relationships
2519 where originator_ue_id = ue.unit_effectivity_id and rownum < 2) grp_flag,
2520 flt.operating_org_id maintenance_org_id, flt.department_id dept_id, ue.fleet_header_id
2521 FROM AHL_UNIT_EFFECTIVITIES_B UE, CSI_ITEM_INSTANCES II, AHL_FLEET_HEADERS_B flt
2522 WHERE UE.csi_item_instance_id = II.instance_id
2523 AND UE.APPLICATION_USG_CODE = 'AHL'
2524 AND UE.Object_type = 'SR'-- Deb: When object type is SR then mr_header_id is null. In that case all mr dependent cursor is going to fail
2525 AND (UE.status_code IS NULL OR UE.status_code = 'INIT-DUE')
2526 AND UE.due_date IS NOT NULL
2527 AND NOT EXISTS (select 'x' from ahl_workorders wo, ahl_visit_tasks_b tsk
2528 where wo.visit_task_id = tsk.visit_task_id
2529 and tsk.task_type_code = 'SUMMARY'
2530 and tsk.status_code NOT IN ('DELETED','CANCELLED')
2531 and tsk.unit_effectivity_id = UE.unit_effectivity_id)
2532 AND NOT EXISTS (SELECT 'x' from ahl_ue_relationships where related_ue_id = UE.unit_effectivity_id)
2533 --AND od.unit_effectivity_id = ue.unit_effectivity_id
2534 --AND od.sim_ue_flag = 'N'
2535 --AND od.mr_maintenance_org_id IS NOT NULL
2536 -- Added by debadey because od does not reflect SR UEs org and dept
2537 AND UE.fleet_header_id IS NOT NULL
2538 AND flt.fleet_header_id = UE.fleet_header_id
2539 ORDER BY ue.csi_item_instance_id, ue.due_date); -- Maintenance org and dept will always be there. Otherwise its an invalid UE
2540 --AND od.mr_maintenance_dept_id IS NOT NULL); -- Deb; remove this condition
2541
2542
2543
2544
2545 -- Get all applicable root Ues for SIMULATION plan(s) for an SR
2546 -- Deb: We may need to get the plan id also when the plan id is not passed
2547 CURSOR ue_sim_sr_csr(p_sim_plan_id NUMBER) IS
2548 SELECT * FROM (
2549 SELECT UES.simulation_ue_id,ues.simulation_plan_id, ues.due_date, ues.mr_header_id,
2550 ues.csi_item_instance_id, 'SR' object_type, ues.defer_from_ue_id,
2551 (SELECT CIT.NAME || '-' || CS.INCIDENT_NUMBER
2552 FROM CS_INCIDENTS_ALL_B CS, CS_INCIDENT_TYPES_TL CIT
2553 WHERE CS.incident_id = ues.cs_incident_id
2554 AND CS.INCIDENT_TYPE_ID = CIT.INCIDENT_TYPE_ID
2555 AND CIT.LANGUAGE = USERENV('LANG')) sr_title,
2556 DECODE(ues.unit_effectivity_id, NULL, 'N',
2557 (SELECT manually_planned_flag
2558 FROM ahl_unit_effectivities_b
2559 WHERE unit_effectivity_id = ues.unit_effectivity_id)) manually_planned_flag,
2560 ii.quantity,
2561 (SELECT 'Y' from ahl_ue_simulations WHERE originator_ue_id = ues.simulation_ue_id AND ROWNUM < 2) grp_flag,
2562 flt.operating_org_id maintenance_org_id, flt.department_id dept_id, ues.fleet_header_id
2563 FROM AHL_UE_SIMULATIONS UES, CSI_ITEM_INSTANCES II, AHL_FLEET_HEADERS_B flt
2564 WHERE UES.csi_item_instance_id = II.instance_id
2565 --AND UE.APPLICATION_USG_CODE = 'AHL'
2566 AND UES.cs_incident_id IS NOT NULL-- Deb: When object type is SR them mr_header_id is null. In that case all mr dependent cursor is going to fail
2567 AND (UES.status_code IS NULL OR UES.status_code = 'INIT-DUE')
2568 AND UES.due_date IS NOT NULL
2569 AND NOT EXISTS (select 'x' from ahl_workorders wo, ahl_visit_tasks_b tsk
2570 where wo.visit_task_id = tsk.visit_task_id
2571 and tsk.task_type_code = 'SUMMARY'
2572 and tsk.status_code NOT IN ('DELETED','CANCELLED')
2573 and tsk.unit_effectivity_id = ues.simulation_ue_id)
2574 AND UES.parent_ue_id IS NULL
2575 --AND od.unit_effectivity_id = UES.simulation_ue_id
2576 --AND od.sim_ue_flag = 'Y'
2577 --AND od.mr_maintenance_org_id IS NOT NULL -- Maintenance org and dept will always be there. Otherwise its an invalid UE
2578 --AND od.mr_maintenance_dept_id IS NOT NULL
2579 AND NVL(p_sim_plan_id, UES.simulation_plan_id) = UES.simulation_plan_id
2580 AND UES.fleet_header_id IS NOT NULL
2581 AND flt.fleet_header_id = UES.fleet_header_id
2582 ORDER BY ues.csi_item_instance_id, ues.due_date);
2583
2584
2585
2586
2587
2588 ----------------------------------------------- Local variable declaration ------------------------------------------------------------
2589 l_debug_module VARCHAR2(400) := 'ahl.plsql.AHL_UMP_BOM.Create_Update_Ump_Bom';
2590 l_mr_header_id NUMBER;
2591 l_buffer_limit NUMBER := 2000;
2592 l_primary_plan_flag VARCHAR2(1);
2593 l_plan_id NUMBER;
2594 l_maintenance_type VARCHAR2(30);
2595
2596 l_previous_instance_id NUMBER;
2597 l_previous_org_id NUMBER;
2598 l_previous_fleet_id NUMBER;
2599
2600
2601 ----------------------------------------------- User Defined Type variables -----------------------------------------------------------
2602
2603
2604 l_ump_mr_title_tbl vchar_tbl_type;
2605 l_uom_code_tbl vchar_tbl_type;
2606 l_ue_object_type_tbl vchar_tbl_type;
2607 l_ue_grp_flag_tbl vchar_tbl_type;
2608 l_ue_man_planned_flag_tbl vchar_tbl_type;
2609
2610 l_mr_header_id_tbl nbr_tbl_type;
2611 l_ue_mr_header_id_tbl nbr_tbl_type;
2612 l_ue_id_tbl nbr_tbl_type;
2613 l_ue_ii_id_tbl nbr_tbl_type;
2614 l_ue_mr_downtime_tbl nbr_tbl_type;
2615 l_ue_csi_item_quantity_tbl nbr_tbl_type;
2616 l_ue_defer_from_ue_tbl nbr_tbl_type;
2617 l_ue_maint_org_id_tbl nbr_tbl_type;
2618 l_ue_maint_dept_id_tbl nbr_tbl_type;
2619 l_ue_fleet_header_id_tbl nbr_tbl_type;
2620 l_plan_id_tbl nbr_tbl_type;
2621
2622 l_ue_due_date_tbl date_tbl_type;
2623
2624 ----------------------------------------------- Variables to store material and resource ----------------------------------------------
2625 l_ump_maint_rec ump_maint_rec_type;
2626 p_x_consolidated_res_tbl ump_resource_tbl_type;
2627 p_x_consolidated_mat_tbl ump_mtrl_tbl_type;
2628
2629
2630 ------------------------------------------------------- Start of Procedure ------------------------------------------------------------
2631
2632 BEGIN
2633
2634 G_DEBUG_LINE_NUM := l_debug_module||': '||1;
2635 IF (G_DEBUG_PROC >= G_DEBUG_LEVEL) THEN
2636 fnd_log.string (G_DEBUG_PROC, l_debug_module, 'Start Create_Update_Ump_Bom');
2637 fnd_log.string (G_DEBUG_PROC, l_debug_module, 'Input Parameter: p_sim_plan_name:' || p_sim_plan_name);
2638 fnd_log.string (G_DEBUG_PROC, l_debug_module, 'Input Parameter: p_all_flag:' || p_all_flag);
2639 END IF;
2640 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Start Create_Update_Ump_Bom');
2641 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Input Parameter: p_sim_plan_name:' || p_sim_plan_name);
2642 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Input Parameter: p_all_flag:' || p_all_flag);
2643 -- Initialize return status.
2644 retcode := 0;
2645 g_ret_status := 0;
2646
2647 -- Initialize message list
2648 FND_MSG_PUB.Initialize;
2649
2650 -- Validation
2651 IF (UPPER(p_all_flag) = 'Y' AND p_sim_plan_name IS NOT NULL)
2652 THEN
2653 retcode := 2;
2654 errbuf := 'All plan flag can not be Y when Plan Name is passed. Please pass only one criteria.';
2655 FND_MESSAGE.Set_Name('AHL','AHL_SIM_MULTIPLE_PARAMS');
2656 FND_MSG_PUB.ADD;
2657 return;
2658 END IF;
2659
2660 IF (UPPER(p_all_flag) = 'Y')
2661 THEN
2662
2663
2664 /*---------------------------------------------- Process PRIMARY plan UEs first --------------------------------------------------*/
2665 -- Get primary plan id
2666 BEGIN
2667 SELECT simulation_plan_id
2668 INTO l_plan_id
2669 FROM ahl_simulation_plans_b
2670 WHERE primary_plan_flag = 'Y'
2671 AND simulation_type = 'UMP';
2672 EXCEPTION
2673 WHEN NO_DATA_FOUND
2674 THEN
2675 retcode := 2;
2676 errbuf := 'Primary Plan not present';
2677 FND_MESSAGE.Set_Name('AHL','AHL_SIM_PRIMARY_PLAN_ABSENT');
2678 FND_MSG_PUB.ADD;
2679 return;
2680 END;
2681
2682 G_DEBUG_LINE_NUM := l_debug_module||': '||10;
2683 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Need to process ALL plans. Now processing the primary plan with l_plan_id = '||l_plan_id);
2684
2685 /* Bug 13096151 - Remove all UE, resource and material for the sim plan from the bom tables */
2686
2687 IF (l_plan_id IS NOT NULL)
2688 THEN
2689 G_DEBUG_LINE_NUM := l_debug_module||': '||12;
2690 DELETE FROM AHL_UMP_RESOURCE_REQMNTS
2691 WHERE maintenance_reqmnt_id IN
2692 (SELECT maintenance_reqmnt_id
2693 FROM AHL_UMP_MAINT_REQMNTS
2694 WHERE object_type = 'UE'
2695 AND simulation_plan_id = l_plan_id);
2696
2697
2698
2699 G_DEBUG_LINE_NUM := l_debug_module||': '||14;
2700 DELETE FROM AHL_UMP_MATERIAL_REQMNTS
2701 WHERE maintenance_reqmnt_id IN
2702 (SELECT maintenance_reqmnt_id
2703 FROM AHL_UMP_MAINT_REQMNTS
2704 WHERE object_type = 'UE'
2705 AND simulation_plan_id = l_plan_id);
2706
2707
2708 G_DEBUG_LINE_NUM := l_debug_module||': '||16;
2709 DELETE FROM AHL_UMP_MAINT_REQMNTS
2710 WHERE object_type = 'UE'
2711 AND simulation_plan_id = l_plan_id;
2712
2713 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' deleted bom ue with l_plan_id = '||l_plan_id);
2714 G_DEBUG_LINE_NUM := l_debug_module||': '||18;
2715 END IF;
2716
2717 /* End of changes for bug 13096151 */
2718
2719 /*----- Process all MR based UE for PRIMARY plan------*/
2720 -- Process all applicable MRs beloging to primary plan UE
2721 OPEN ahl_mr_headers_csr;
2722 LOOP
2723
2724 G_DEBUG_LINE_NUM := l_debug_module||': '||20;
2725 -- Process max 2000 MRs at a time
2726 FETCH ahl_mr_headers_csr
2727 BULK COLLECT
2728 INTO l_mr_header_id_tbl,l_ump_mr_title_tbl
2729 LIMIT l_buffer_limit;
2730
2731 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Number of MRs to be processed for this fetch - l_mr_header_id_tbl.COUNT = '||l_mr_header_id_tbl.COUNT );
2732
2733 EXIT WHEN (l_mr_header_id_tbl IS NULL OR l_mr_header_id_tbl.count = 0);
2734
2735 -- Iterate over MR header id
2736 FOR j IN l_mr_header_id_tbl.FIRST..l_mr_header_id_tbl.LAST
2737 LOOP
2738 G_DEBUG_LINE_NUM := l_debug_module||': '||30;
2739 -- Get all applicable root UEs and loop through them
2740 -- Get all information reg the UE in tbl type variables
2741 -- Log
2742 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Processing MR - l_mr_header_id_tbl('||j||') = '||l_mr_header_id_tbl(j) );
2743
2744 -- reset the variables to check previous UE as this is a different MR
2745 l_previous_instance_id := NULL;
2746 l_previous_org_id := NULL;
2747 l_previous_fleet_id := NULL;
2748
2749 OPEN ue_mr_csr(l_mr_header_id_tbl(j));
2750 LOOP
2751
2752 G_DEBUG_LINE_NUM := l_debug_module||': '||40;
2753 FETCH ue_mr_csr BULK COLLECT
2754 INTO l_ue_id_tbl,
2755 l_ue_due_date_tbl,
2756 l_ue_mr_header_id_tbl,
2757 l_ue_ii_id_tbl,
2758 l_ue_object_type_tbl,
2759 l_ue_defer_from_ue_tbl,
2760 l_ump_mr_title_tbl,
2761 l_ue_man_planned_flag_tbl,
2762 l_ue_csi_item_quantity_tbl,
2763 l_ue_grp_flag_tbl,
2764 l_ue_mr_downtime_tbl,
2765 l_uom_code_tbl,
2766 l_ue_maint_org_id_tbl,
2767 l_ue_maint_dept_id_tbl,
2768 l_ue_fleet_header_id_tbl
2769 LIMIT l_buffer_limit; -- For each MR, process 2000 UEs at a time
2770
2771 -- Log
2772 IF (l_ue_id_tbl IS NOT NULL)
2773 THEN
2774 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Number of UEs to be processed for this MR for this fetch- l_ue_id_tbl.COUNT = '||l_ue_id_tbl.COUNT);
2775 END IF;
2776
2777 EXIT WHEN (l_ue_id_tbl IS NULL OR l_ue_id_tbl.COUNT = 0);
2778
2779
2780 -- Iterate over Root UEs for primary plan
2781 FOR i IN l_ue_id_tbl.FIRST..l_ue_id_tbl.LAST
2782 LOOP
2783
2784 G_DEBUG_LINE_NUM := l_debug_module||': '||50;
2785 -- Log
2786 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Processing UE - l_ue_id_tbl('||i||') = '||l_ue_id_tbl(i) );
2787 -- Check whether the ue has similar properties as the previous ue
2788 IF (l_previous_instance_id = l_ue_ii_id_tbl(i)
2789 AND l_previous_org_id = l_ue_maint_org_id_tbl(i)
2790 AND l_previous_fleet_id = l_ue_fleet_header_id_tbl(i))
2791 THEN
2792 -- No need to process UE. Simply insert the previously calculated values
2793 -- But we still need the ue headers data
2794 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' No need to process UE. Simply insert the previously calculated values' );
2795 G_DEBUG_LINE_NUM := l_debug_module||': '||60;
2796
2797 IF (l_ue_object_type_tbl(i) = 'SR')
2798 THEN
2799
2800 -- l_title := p_mr_title;
2801 IF (l_ue_defer_from_ue_tbl(i) is not null) then
2802 l_maintenance_type := 'DEFER_NONROUTINE';
2803 ELSE
2804 l_maintenance_type := 'NONROUTINE';
2805 END IF;
2806 ELSE
2807 --l_title := p_mr_title;
2808 IF (l_ue_defer_from_ue_tbl(i) is not null) then
2809 l_maintenance_type := 'DEFER_ROUTINE';
2810 ELSIF (l_ue_man_planned_flag_tbl(i) = 'Y') then
2811 l_maintenance_type := 'ROUTINE_UNKNOWN';
2812 ELSE
2813 l_maintenance_type := 'ROUTINE';
2814 END IF;
2815 END IF;
2816
2817
2818 -- Populate header data for UMP_MAINTENANCE_REQMNTS table
2819 l_ump_maint_rec.UNIT_EFFECTIVITY_ID := l_ue_id_tbl(i) ;
2820 l_ump_maint_rec.SIMULATION_PLAN_ID := l_plan_id;
2821 l_ump_maint_rec.OBJECT_TYPE := 'UE'; -- Primary UE
2822 l_ump_maint_rec.OPERATING_ORG_ID := l_ue_maint_org_id_tbl(i);
2823 l_ump_maint_rec.DUE_DATE := l_ue_due_date_tbl(i); -- Root UE due date
2824 l_ump_maint_rec.MAINTENANCE_TYPE_CODE:= l_maintenance_type;
2825 l_ump_maint_rec.TITLE := l_ump_mr_title_tbl(i);
2826 l_ump_maint_rec.ITEM_INSTANCE_ID := l_ue_ii_id_tbl(i) ; -- Root UE instance
2827 l_ump_maint_rec.DEPARTMENT_ID := l_ue_maint_dept_id_tbl(i);
2828 -- Added by debadey for VCP integration
2829 l_ump_maint_rec.FLEET_HEADER_ID := l_ue_fleet_header_id_tbl(i);
2830 -- Invoke update procedure
2831 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Invoking procedure Perform_Updates' );
2832 Perform_Updates(l_ump_maint_rec,
2833 p_x_consolidated_res_tbl,
2834 p_x_consolidated_mat_tbl);
2835
2836
2837 ELSE
2838 -- This becomes the previous instance in the next loop
2839 l_previous_instance_id := l_ue_ii_id_tbl(i);
2840 l_previous_org_id := l_ue_maint_org_id_tbl(i);
2841 l_previous_fleet_id := l_ue_fleet_header_id_tbl(i);
2842
2843
2844 G_DEBUG_LINE_NUM := l_debug_module||': '||70;
2845 -- Reset all storage variables that are at UE level before calling Process_Ue
2846 IF (p_x_consolidated_res_tbl IS NOT NULL)
2847 THEN
2848 p_x_consolidated_res_tbl.DELETE; --To store consolidated Resource data for each parent UE
2849 END IF;
2850
2851 IF(p_x_consolidated_mat_tbl IS NOT NULL)
2852 THEN
2853 p_x_consolidated_mat_tbl.DELETE; --To store consolidated Material data for each parent UE
2854 END IF;
2855
2856 G_DEBUG_LINE_NUM := l_debug_module||': '||80;
2857
2858 --Invoke Process Ues
2859 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Invoking procedure Process_Ue');
2860 Process_Ue( p_unit_effectivity_id => l_ue_id_tbl(i),
2861 p_group_mr_flag => l_ue_grp_flag_tbl(i),
2862 p_mr_header_id => l_ue_mr_header_id_tbl(i),
2863 p_item_instance_id => l_ue_ii_id_tbl(i),
2864 p_item_quantity => l_ue_csi_item_quantity_tbl(i),
2865 p_maint_org_id => l_ue_maint_org_id_tbl(i),
2866 p_maint_dept_id => l_ue_maint_dept_id_tbl(i),
2867 p_object_type => l_ue_object_type_tbl(i),
2868 p_due_date => l_ue_due_date_tbl(i),
2869 p_ue_mr_downtime => l_ue_mr_downtime_tbl(i),
2870 p_mr_title => l_ump_mr_title_tbl(i),
2871 p_defer_from_ue_id => l_ue_defer_from_ue_tbl(i),
2872 p_plan_id => l_plan_id,
2873 p_manually_planned_flag => l_ue_man_planned_flag_tbl(i),
2874 p_sim_plan_flag => 'N',
2875 -- Added by debadey for VCP integration
2876 p_fleet_header_id => l_ue_fleet_header_id_tbl(i),
2877 p_x_consolidated_res_tbl => p_x_consolidated_res_tbl,
2878 p_x_consolidated_mat_tbl => p_x_consolidated_mat_tbl);
2879
2880 -- Log
2881 G_DEBUG_LINE_NUM := l_debug_module||': '||89;
2882 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Back from procedure Process_Ue. Now p_x_consolidated_res_tbl.COUNT = '||p_x_consolidated_res_tbl.COUNT||
2883 ' And p_x_consolidated_mat_tbl.COUNT = '||p_x_consolidated_mat_tbl.COUNT);
2884 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' End of looping for the UE = '||l_ue_id_tbl(i) );
2885
2886 END IF; -- This is for the end of checking whether the UE data is same as previous
2887 END LOOP; -- End of looping over root UEs table populated during FETCH
2888
2889 G_DEBUG_LINE_NUM := l_debug_module||': '||90;
2890
2891 END LOOP; -- End of looping over all root UEs for an MR
2892 G_DEBUG_LINE_NUM := l_debug_module||': '||100;
2893 CLOSE ue_mr_csr;
2894 G_DEBUG_LINE_NUM := l_debug_module||': '||110;
2895 -- Log
2896 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' End of looping over the MR.'||l_mr_header_id_tbl(j));
2897 END LOOP; -- End of looping over applicable MRs got through FETCH
2898 G_DEBUG_LINE_NUM := l_debug_module||': '||120;
2899 -- Log
2900 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' End of Loop over this set of MR' );
2901 END LOOP; -- End of processing of all MRs for the primary plan
2902 G_DEBUG_LINE_NUM := l_debug_module||': '||130;
2903 CLOSE ahl_mr_headers_csr;
2904 G_DEBUG_LINE_NUM := l_debug_module||': '||140;
2905 -- Log
2906 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' End of MR proessing in Primary Plan' );
2907 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Process all SR based UEs for primary plan' );
2908
2909 /*----- Process all SR based UE for PRIMARY plan------*/
2910 G_DEBUG_LINE_NUM := l_debug_module||': '||150;
2911 OPEN ue_sr_csr;
2912 LOOP
2913 FETCH ue_sr_csr BULK COLLECT
2914 INTO l_ue_id_tbl,
2915 l_ue_due_date_tbl,
2916 l_ue_mr_header_id_tbl,
2917 l_ue_ii_id_tbl,
2918 l_ue_object_type_tbl,
2919 l_ue_defer_from_ue_tbl,
2920 l_ue_man_planned_flag_tbl,
2921 l_ump_mr_title_tbl, -- This will capture SR title
2922 l_ue_csi_item_quantity_tbl,
2923 l_ue_grp_flag_tbl,
2924 l_ue_maint_org_id_tbl,
2925 l_ue_maint_dept_id_tbl,
2926 l_ue_fleet_header_id_tbl
2927 LIMIT l_buffer_limit; -- For each MR, process 2000 UEs at a time
2928
2929 G_DEBUG_LINE_NUM := l_debug_module||': '||160;
2930
2931 -- Log
2932 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' In ue_sr_csr l_ue_id_tbl.COUNT = '||l_ue_id_tbl.COUNT );
2933
2934 EXIT WHEN (l_ue_id_tbl IS NULL OR l_ue_id_tbl.COUNT = 0);
2935
2936 -- Iterate over Root UEs for primary plan
2937 FOR i IN l_ue_id_tbl.FIRST..l_ue_id_tbl.LAST
2938 LOOP
2939 G_DEBUG_LINE_NUM := l_debug_module||': '||170;
2940 -- Reset all storage variables that are at UE level
2941 p_x_consolidated_res_tbl.DELETE; --To store consolidated Rsource data for each parent UE
2942 p_x_consolidated_mat_tbl.DELETE; --To store consolidated Material data for each parent UE
2943
2944 -- Log
2945 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' For Primary plan SR processing.In ue_cr_csr l_ue_id_tbl('||i||') = '||l_ue_id_tbl(i));
2946 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Calling Process_Ue. Now p_x_consolidated_res_tbl.COUNT = '||p_x_consolidated_res_tbl.COUNT||
2947 ' and p_x_consolidated_mat_tbl.COUNT = '||p_x_consolidated_mat_tbl.COUNT );
2948 G_DEBUG_LINE_NUM := l_debug_module||': '||180;
2949 --Invoke Process Ues
2950 Process_Ue( p_unit_effectivity_id => l_ue_id_tbl(i),
2951 p_group_mr_flag => l_ue_grp_flag_tbl(i),
2952 p_mr_header_id => NULL,
2953 p_item_instance_id => l_ue_ii_id_tbl(i),
2954 p_item_quantity => l_ue_csi_item_quantity_tbl(i),
2955 p_maint_org_id => l_ue_maint_org_id_tbl(i),
2956 p_maint_dept_id => l_ue_maint_dept_id_tbl(i),
2957 p_object_type => l_ue_object_type_tbl(i),
2958 p_due_date => l_ue_due_date_tbl(i),
2959 p_ue_mr_downtime => NULL,
2960 p_mr_title => l_ump_mr_title_tbl(i),
2961 p_defer_from_ue_id => l_ue_defer_from_ue_tbl(i),
2962 p_plan_id => l_plan_id,
2963 p_manually_planned_flag => l_ue_man_planned_flag_tbl(i),
2964 p_sim_plan_flag => 'N',
2965 -- Added by debadey for VCP integration
2966 p_fleet_header_id => l_ue_fleet_header_id_tbl(i),
2967 p_x_consolidated_res_tbl => p_x_consolidated_res_tbl,
2968 p_x_consolidated_mat_tbl => p_x_consolidated_mat_tbl);
2969
2970 -- Log
2971 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' After call to Process_Ue p_x_consolidated_res_tbl.COUNT = '||p_x_consolidated_res_tbl.COUNT||
2972 ' and p_x_consolidated_mat_tbl.COUNT = '||p_x_consolidated_mat_tbl.COUNT );
2973 G_DEBUG_LINE_NUM := l_debug_module||': '||190;
2974 END LOOP;
2975 G_DEBUG_LINE_NUM := l_debug_module||': '||200;
2976
2977 END LOOP;
2978 G_DEBUG_LINE_NUM := l_debug_module||': '||210;
2979 CLOSE ue_sr_csr;
2980 G_DEBUG_LINE_NUM := l_debug_module||': '||220;
2981 -- Log
2982 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' End of UEs belonging to primary plan' );
2983
2984 -- Process for simulation plans next
2985 -- Log
2986 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Start processing Simulation Plan UEs' );
2987 G_DEBUG_LINE_NUM := l_debug_module||': '||230;
2988 OPEN ahl_sim_mr_headers_csr(NULL);
2989 LOOP
2990 G_DEBUG_LINE_NUM := l_debug_module||': '||240;
2991 FETCH ahl_sim_mr_headers_csr
2992 BULK COLLECT
2993 INTO l_mr_header_id_tbl,l_ump_mr_title_tbl
2994 LIMIT l_buffer_limit;
2995
2996 -- Log
2997 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' In ahl_sim_mr_headers_csr l_mr_header_id_tbl.COUNT = '||l_mr_header_id_tbl.COUNT );
2998 EXIT WHEN (l_mr_header_id_tbl IS NULL OR l_mr_header_id_tbl.count = 0);
2999
3000 G_DEBUG_LINE_NUM := l_debug_module||': '||250;
3001 -- Iterate over MR header id
3002 FOR j IN l_mr_header_id_tbl.FIRST..l_mr_header_id_tbl.LAST
3003 LOOP
3004 G_DEBUG_LINE_NUM := l_debug_module||': '||260;
3005 -- Log
3006 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' l_mr_header_id_tbl('||j||') ='||l_mr_header_id_tbl(j));
3007 l_previous_instance_id := NULL;
3008 l_previous_org_id:= NULL;
3009 l_previous_fleet_id := NULL;
3010 -- Get all applicable root UEs and loop through them
3011 -- Get all information reg the UE in tbl type variables
3012 OPEN ue_sim_mr_csr(l_mr_header_id_tbl(j), NULL);
3013 LOOP
3014 G_DEBUG_LINE_NUM := l_debug_module||': '||270;
3015 FETCH ue_sim_mr_csr BULK COLLECT
3016 INTO l_ue_id_tbl,
3017 l_plan_id_tbl,
3018 l_ue_due_date_tbl,
3019 l_ue_mr_header_id_tbl,
3020 l_ue_ii_id_tbl,
3021 l_ue_object_type_tbl,
3022 l_ue_defer_from_ue_tbl,
3023 l_ump_mr_title_tbl,
3024 l_ue_man_planned_flag_tbl,
3025 l_ue_csi_item_quantity_tbl,
3026 l_ue_grp_flag_tbl,
3027 l_ue_mr_downtime_tbl,
3028 l_uom_code_tbl,
3029 l_ue_maint_org_id_tbl,
3030 l_ue_maint_dept_id_tbl,
3031 l_ue_fleet_header_id_tbl
3032 LIMIT l_buffer_limit; -- For each MR, process 2000 UEs at a time
3033
3034 EXIT WHEN (l_ue_id_tbl IS NULL OR l_ue_id_tbl.COUNT = 0);
3035 -- Log
3036 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' In ue_sim_mr_csr l_ue_id_tbl.COUNT = '||l_ue_id_tbl.COUNT);
3037
3038 G_DEBUG_LINE_NUM := l_debug_module||': '||280;
3039 -- Iterate over Root UEs for Simulation plan
3040 FOR i IN l_ue_id_tbl.FIRST..l_ue_id_tbl.LAST
3041 LOOP
3042
3043 -- Log
3044 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Simulation plan ue processing. l_ue_id_tbl('||i||') ='||l_ue_id_tbl(i));
3045 /* Bug 13096151 - Remove all UE, resource and material for the sim plan from the bom tables */
3046
3047 IF (l_plan_id_tbl(i) IS NOT NULL)
3048 THEN
3049 G_DEBUG_LINE_NUM := l_debug_module||': '||282;
3050 DELETE FROM AHL_UMP_RESOURCE_REQMNTS
3051 WHERE maintenance_reqmnt_id IN
3052 (SELECT maintenance_reqmnt_id
3053 FROM AHL_UMP_MAINT_REQMNTS
3054 WHERE object_type = 'SIM'
3055 AND simulation_plan_id = l_plan_id_tbl(i));
3056
3057 G_DEBUG_LINE_NUM := l_debug_module||': '||284;
3058 DELETE FROM AHL_UMP_MATERIAL_REQMNTS
3059 WHERE maintenance_reqmnt_id IN
3060 (SELECT maintenance_reqmnt_id
3061 FROM AHL_UMP_MAINT_REQMNTS
3062 WHERE object_type = 'SIM'
3063 AND simulation_plan_id = l_plan_id_tbl(i));
3064
3065 G_DEBUG_LINE_NUM := l_debug_module||': '||286;
3066 DELETE FROM AHL_UMP_MAINT_REQMNTS
3067 WHERE object_type = 'SIM'
3068 AND simulation_plan_id = l_plan_id_tbl(i);
3069
3070 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' deleted ump bom for l_plan_id = '||l_plan_id_tbl(i));
3071 G_DEBUG_LINE_NUM := l_debug_module||': '||288;
3072 END IF;
3073
3074 /* End of changes for bug 13096151 */
3075
3076 -- Check whether the ue has similar properties as the previous ue
3077 IF (l_previous_instance_id = l_ue_ii_id_tbl(i)
3078 AND l_previous_org_id = l_ue_maint_org_id_tbl(i)
3079 AND l_previous_fleet_id = l_ue_fleet_header_id_tbl(i))
3080 THEN
3081
3082 -- No need to process UE. Simply insert the previously calculated values
3083 -- But we still need the ue headers data
3084 -- Log
3085 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' No need to process UE. Simply insert the previously calculated values' );
3086 G_DEBUG_LINE_NUM := l_debug_module||': '||289;
3087
3088 IF (l_ue_object_type_tbl(i) = 'SR')
3089 THEN
3090
3091 -- l_title := p_mr_title;
3092 IF (l_ue_defer_from_ue_tbl(i) is not null) then
3093 l_maintenance_type := 'DEFER_NONROUTINE';
3094 ELSE
3095 l_maintenance_type := 'NONROUTINE';
3096 END IF;
3097 ELSE
3098 --l_title := p_mr_title;
3099 IF (l_ue_defer_from_ue_tbl(i) is not null) then
3100 l_maintenance_type := 'DEFER_ROUTINE';
3101 ELSIF (l_ue_man_planned_flag_tbl(i) = 'Y') then
3102 l_maintenance_type := 'ROUTINE_UNKNOWN';
3103 ELSE
3104 l_maintenance_type := 'ROUTINE';
3105 END IF;
3106 END IF;
3107
3108
3109 -- Populate header data for UMP_MAINTENANCE_REQMNTS table
3110 l_ump_maint_rec.UNIT_EFFECTIVITY_ID := l_ue_id_tbl(i) ;
3111 l_ump_maint_rec.SIMULATION_PLAN_ID := l_plan_id_tbl(i);--l_primary_plan_id;--l_plan_id_tbl(i); -- plan_id
3112 /*IF (p_sim_plan_flag = 'Y')
3113 THEN
3114 l_ump_maint_rec.OBJECT_TYPE := 'SIM';-- Simulation UE
3115 ELSE
3116 l_ump_maint_rec.OBJECT_TYPE := 'UE'; -- Primary UE
3117 END IF;*/
3118 l_ump_maint_rec.OBJECT_TYPE := 'SIM'; -- Simulation UE
3119 l_ump_maint_rec.OPERATING_ORG_ID := l_ue_maint_org_id_tbl(i);
3120 l_ump_maint_rec.DUE_DATE := l_ue_due_date_tbl(i); -- Root UE due date
3121 l_ump_maint_rec.MAINTENANCE_TYPE_CODE:= l_maintenance_type;
3122 l_ump_maint_rec.TITLE := l_ump_mr_title_tbl(i);
3123 l_ump_maint_rec.ITEM_INSTANCE_ID := l_ue_ii_id_tbl(i) ; -- Root UE instance
3124 l_ump_maint_rec.DEPARTMENT_ID := l_ue_maint_dept_id_tbl(i);
3125 -- Added by debadey for VCP integration
3126 l_ump_maint_rec.FLEET_HEADER_ID := l_ue_fleet_header_id_tbl(i);
3127 -- Invoke update procedure
3128 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Calling Perform_Updates');
3129 Perform_Updates(l_ump_maint_rec,
3130 p_x_consolidated_res_tbl,
3131 p_x_consolidated_mat_tbl);
3132 G_DEBUG_LINE_NUM := l_debug_module||': '||290;
3133 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Back from Perform_Updates');
3134 ELSE
3135 -- This becomes the previous instance in the next loop
3136 l_previous_instance_id := l_ue_ii_id_tbl(i);
3137 l_previous_org_id :=l_ue_maint_org_id_tbl(i);
3138 l_previous_fleet_id := l_ue_fleet_header_id_tbl(i);
3139 -- Reset all storage variables that are at UE level
3140 p_x_consolidated_res_tbl.DELETE; --To store consolidated Rsource data for each parent UE
3141 p_x_consolidated_mat_tbl.DELETE; --To store consolidated Material data for each parent UE
3142 G_DEBUG_LINE_NUM := l_debug_module||': '||292;
3143 -- Log
3144 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Calling Process_Ue for sim plan UE. p_x_consolidated_res_tbl.COUNT = '||p_x_consolidated_res_tbl.COUNT||
3145 ' p_x_consolidated_mat_tbl.COUNT = '||p_x_consolidated_mat_tbl.COUNT);
3146 G_DEBUG_LINE_NUM := l_debug_module||': '||300;
3147 --Invoke Process Ues
3148 Process_Ue( p_unit_effectivity_id => l_ue_id_tbl(i),
3149 p_group_mr_flag => l_ue_grp_flag_tbl(i),
3150 p_mr_header_id => l_ue_mr_header_id_tbl(i),
3151 p_item_instance_id => l_ue_ii_id_tbl(i),
3152 p_item_quantity => l_ue_csi_item_quantity_tbl(i),
3153 p_maint_org_id => l_ue_maint_org_id_tbl(i),
3154 p_maint_dept_id => l_ue_maint_dept_id_tbl(i),
3155 p_object_type => l_ue_object_type_tbl(i),
3156 p_due_date => l_ue_due_date_tbl(i),
3157 p_ue_mr_downtime => l_ue_mr_downtime_tbl(i),
3158 p_mr_title => l_ump_mr_title_tbl(i),
3159 p_defer_from_ue_id => l_ue_defer_from_ue_tbl(i),
3160 p_plan_id => l_plan_id_tbl(i),
3161 p_manually_planned_flag => l_ue_man_planned_flag_tbl(i),
3162 p_sim_plan_flag => 'Y',
3163 -- Added by debadey for VCP integration
3164 p_fleet_header_id => l_ue_fleet_header_id_tbl(i),
3165 p_x_consolidated_res_tbl => p_x_consolidated_res_tbl,
3166 p_x_consolidated_mat_tbl => p_x_consolidated_mat_tbl);
3167
3168
3169 -- Log
3170 G_DEBUG_LINE_NUM := l_debug_module||': '||310;
3171 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Back from Process_Ue for sim plan UE.Now p_x_consolidated_res_tbl.COUNT = '||p_x_consolidated_res_tbl.COUNT||
3172 ' p_x_consolidated_mat_tbl.COUNT = '||p_x_consolidated_mat_tbl.COUNT);
3173
3174 END IF; -- This is for the end of checking whether the UE data is same as previous
3175 END LOOP; -- End of looping over root UEs table populated during FETCH
3176
3177 -- Log
3178 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' End of looping over sim plan root UEs for this round of fetch');
3179 G_DEBUG_LINE_NUM := l_debug_module||': '||320;
3180
3181 END LOOP; -- End of looping over all root UEs for an MR
3182 CLOSE ue_sim_mr_csr;
3183 G_DEBUG_LINE_NUM := l_debug_module||': '||330;
3184 -- Log
3185 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' End of looping over all sim plan root UEs for the MR');
3186 END LOOP; -- End of looping over applicable MRs got through FETCH
3187 G_DEBUG_LINE_NUM := l_debug_module||': '||340;
3188
3189 END LOOP; -- End of processing of all MRs for the simulation plan
3190 CLOSE ahl_sim_mr_headers_csr;
3191 G_DEBUG_LINE_NUM := l_debug_module||': '||350;
3192 -- Log
3193 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' End of looping over all MRs for Simulation Plan');
3194 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Process SR based UE for Simulation Plan');
3195 -- Process all SR based UE for Simulation Plan
3196
3197 OPEN ue_sim_sr_csr(NULL);
3198 LOOP
3199 G_DEBUG_LINE_NUM := l_debug_module||': '||360;
3200 FETCH ue_sim_sr_csr BULK COLLECT
3201 INTO l_ue_id_tbl,
3202 l_plan_id_tbl,
3203 l_ue_due_date_tbl,
3204 l_ue_mr_header_id_tbl,
3205 l_ue_ii_id_tbl,
3206 l_ue_object_type_tbl,
3207 l_ue_defer_from_ue_tbl,
3208 l_ump_mr_title_tbl, -- This will capture SR title
3209 l_ue_man_planned_flag_tbl,
3210 l_ue_csi_item_quantity_tbl,
3211 l_ue_grp_flag_tbl,
3212 l_ue_maint_org_id_tbl,
3213 l_ue_maint_dept_id_tbl,
3214 l_ue_fleet_header_id_tbl
3215 LIMIT l_buffer_limit; -- For each MR, process 2000 UEs at a time
3216
3217 -- Log
3218 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Processing sim plan SR based UEs. In ue_sim_sr_csr l_ue_id_tbl.COUNT = '||l_ue_id_tbl.COUNT);
3219 G_DEBUG_LINE_NUM := l_debug_module||': '||370;
3220 EXIT WHEN (l_ue_id_tbl IS NULL OR l_ue_id_tbl.COUNT = 0);
3221
3222 -- Iterate over Root UEs for primary plan
3223 FOR i IN l_ue_id_tbl.FIRST..l_ue_id_tbl.LAST
3224 LOOP
3225 G_DEBUG_LINE_NUM := l_debug_module||': '||380;
3226 -- Reset all storage variables that are at UE level
3227 p_x_consolidated_res_tbl.DELETE; --To store consolidated Rsource data for each parent UE
3228 p_x_consolidated_mat_tbl.DELETE; --To store consolidated Material data for each parent UE
3229
3230 /* Bug 13096151 - Remove all UE, resource and material for the sim plan from the bom tables */
3231
3232 IF (l_plan_id_tbl(i) IS NOT NULL)
3233 THEN
3234 G_DEBUG_LINE_NUM := l_debug_module||': '||382;
3235 DELETE FROM AHL_UMP_RESOURCE_REQMNTS
3236 WHERE maintenance_reqmnt_id IN
3237 (SELECT maintenance_reqmnt_id
3238 FROM AHL_UMP_MAINT_REQMNTS
3239 WHERE object_type = 'SIM'
3240 AND simulation_plan_id = l_plan_id_tbl(i));
3241
3242
3243
3244 G_DEBUG_LINE_NUM := l_debug_module||': '||384;
3245 DELETE FROM AHL_UMP_MATERIAL_REQMNTS
3246 WHERE maintenance_reqmnt_id IN
3247 (SELECT maintenance_reqmnt_id
3248 FROM AHL_UMP_MAINT_REQMNTS
3249 WHERE object_type = 'SIM'
3250 AND simulation_plan_id = l_plan_id_tbl(i));
3251
3252
3253 G_DEBUG_LINE_NUM := l_debug_module||': '||386;
3254 DELETE FROM AHL_UMP_MAINT_REQMNTS
3255 WHERE object_type = 'SIM'
3256 AND simulation_plan_id = l_plan_id_tbl(i);
3257
3258 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' BOM UE deleted for plan id = '||l_plan_id_tbl(i));
3259 G_DEBUG_LINE_NUM := l_debug_module||': '||388;
3260 END IF;
3261
3262 /* End of changes for bug 13096151 */
3263
3264 -- Log
3265 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' l_ue_id_tbl('||i||') = '||l_ue_id_tbl(i));
3266 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Calling Process_Ue for sim plan UE. p_x_consolidated_res_tbl.COUNT = '||p_x_consolidated_res_tbl.COUNT||' p_x_consolidated_mat_tbl.COUNT = '||p_x_consolidated_mat_tbl.COUNT);
3267 G_DEBUG_LINE_NUM := l_debug_module||': '||390;
3268 --Invoke Process Ues
3269 Process_Ue( p_unit_effectivity_id => l_ue_id_tbl(i),
3270 p_group_mr_flag => l_ue_grp_flag_tbl(i),
3271 p_mr_header_id => NULL,
3272 p_item_instance_id => l_ue_ii_id_tbl(i),
3273 p_item_quantity => l_ue_csi_item_quantity_tbl(i),
3274 p_maint_org_id => l_ue_maint_org_id_tbl(i),
3275 p_maint_dept_id => l_ue_maint_dept_id_tbl(i),
3276 p_object_type => l_ue_object_type_tbl(i),
3277 p_due_date => l_ue_due_date_tbl(i),
3278 p_ue_mr_downtime => NULL,
3279 p_mr_title => l_ump_mr_title_tbl(i),
3280 p_defer_from_ue_id => l_ue_defer_from_ue_tbl(i),
3281 p_plan_id => l_plan_id_tbl(i),
3282 p_manually_planned_flag => l_ue_man_planned_flag_tbl(i),
3283 p_sim_plan_flag => 'Y',
3284 -- Added by debadey for VCP integration
3285 p_fleet_header_id => l_ue_fleet_header_id_tbl(i),
3286 p_x_consolidated_res_tbl => p_x_consolidated_res_tbl,
3287 p_x_consolidated_mat_tbl => p_x_consolidated_mat_tbl);
3288
3289 G_DEBUG_LINE_NUM := l_debug_module||': '||400;
3290 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Back from Process_Ue for sim plan UE.'||
3291 ' Now p_x_consolidated_res_tbl.COUNT = '||p_x_consolidated_res_tbl.COUNT||
3292 ' p_x_consolidated_mat_tbl.COUNT = '||p_x_consolidated_mat_tbl.COUNT);
3293
3294 END LOOP;
3295 G_DEBUG_LINE_NUM := l_debug_module||': '||410;
3296 -- Log
3297 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' End of Iteration over root UEs for this fetch');
3298
3299
3300 END LOOP;
3301 G_DEBUG_LINE_NUM := l_debug_module||': '||420;
3302 CLOSE ue_sim_sr_csr;
3303 -- Log
3304 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' End of Iteration over SR based root UEs for simulation plan');
3305 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' End of Processing when all plans flag is set to Y');
3306 -- End of processing for all simulation and primary flag
3307
3308 ELSE
3309 /* ************************* Processing is not for all plans.********************************* */
3310
3311 -- Process only specific plan passed as parameter
3312 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Processing only specific plan');
3313 -- Get the plan id for the plan name passed
3314 G_DEBUG_LINE_NUM := l_debug_module||': '||430;
3315 IF (p_sim_plan_name) IS NULL
3316 THEN
3317 G_DEBUG_LINE_NUM := l_debug_module||': '||440;
3318
3319 -- Log
3320 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Plan Name passed is null. Returning...');
3321 retcode := 2;
3322 errbuf := 'Plan Name passed is null.';-- Deb: use fnd_msg_pub
3323 FND_MESSAGE.Set_Name('AHL','AHL_SIM_NO_PLAN_NAME');
3324 FND_MSG_PUB.ADD;
3325 RETURN;
3326
3327 END IF;
3328 G_DEBUG_LINE_NUM := l_debug_module||': '||450;
3329 BEGIN
3330 SELECT b.simulation_plan_id, b.primary_plan_flag
3331 INTO l_plan_id, l_primary_plan_flag
3332 FROM ahl_simulation_plans_tl tl, ahl_simulation_plans_b b
3333 WHERE UPPER(tl.simulation_plan_name) = UPPER(p_sim_plan_name)
3334 AND b.simulation_plan_id = tl.simulation_plan_id
3335 AND b.status_code = 'ACTIVE'
3336 AND b.simulation_type = 'UMP'
3337 AND ROWNUM<2;
3338 EXCEPTION
3339 WHEN NO_DATA_FOUND
3340 THEN
3341 -- Log
3342 G_DEBUG_LINE_NUM := l_debug_module||': '||460;
3343 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Plan Name passed is invalid. Returning...');
3344 FND_MESSAGE.Set_Name('AHL','AHL_SIM_INVALID_PLAN_NAME');
3345 FND_MSG_PUB.ADD;
3346 retcode := 2;
3347 errbuf := 'Plan Name passed is not found.';
3348 RETURN;
3349 END;
3350 G_DEBUG_LINE_NUM := l_debug_module||': '||470;
3351
3352 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Processing l_plan_id ='||l_plan_id||', l_primary_plan_flag = '||l_primary_plan_flag);
3353 -- Start Processing
3354
3355 IF l_primary_plan_flag = 'Y'
3356 THEN
3357 -- Process Primary plan
3358 G_DEBUG_LINE_NUM := l_debug_module||': '||480;
3359
3360 /*----- Process all MR based UE for PRIMARY plan------*/
3361 -- Process all applicable MRs beloging to primary plan UE
3362 /* Bug 13096151 - Remove all UE, resource and material for the plan from the bom tables */
3363 G_DEBUG_LINE_NUM := l_debug_module||': '||471;
3364 DELETE FROM AHL_UMP_RESOURCE_REQMNTS
3365 WHERE maintenance_reqmnt_id IN
3366 (SELECT maintenance_reqmnt_id
3367 FROM AHL_UMP_MAINT_REQMNTS
3368 WHERE object_type = 'UE'
3369 AND simulation_plan_id = l_plan_id);
3370
3371 G_DEBUG_LINE_NUM := l_debug_module||': '||472;
3372 DELETE FROM AHL_UMP_MATERIAL_REQMNTS
3373 WHERE maintenance_reqmnt_id IN
3374 (SELECT maintenance_reqmnt_id
3375 FROM AHL_UMP_MAINT_REQMNTS
3376 WHERE object_type = 'UE'
3377 AND simulation_plan_id = l_plan_id);
3378
3379 G_DEBUG_LINE_NUM := l_debug_module||': '||476;
3380 DELETE FROM AHL_UMP_MAINT_REQMNTS
3381 WHERE object_type = 'UE'
3382 AND simulation_plan_id = l_plan_id;
3383
3384 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Deleted BOM for the primary plan with plan id '||l_plan_id);
3385
3386 G_DEBUG_LINE_NUM := l_debug_module||': '||478;
3387 /* End of changes for bug 13096151 */
3388 OPEN ahl_mr_headers_csr;
3389 LOOP
3390 G_DEBUG_LINE_NUM := l_debug_module||': '||490;
3391 -- Process max 2000 MRs at a time
3392 FETCH ahl_mr_headers_csr
3393 BULK COLLECT
3394 INTO l_mr_header_id_tbl,l_ump_mr_title_tbl
3395 LIMIT l_buffer_limit;
3396
3397 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Looping through primary plan MRs. l_mr_header_id_tbl.COUNT = '||l_mr_header_id_tbl.COUNT );
3398 EXIT WHEN (l_mr_header_id_tbl IS NULL OR l_mr_header_id_tbl.count = 0);
3399
3400 G_DEBUG_LINE_NUM := l_debug_module||': '||500;
3401 -- Iterate over MR header id
3402 FOR j IN l_mr_header_id_tbl.FIRST..l_mr_header_id_tbl.LAST
3403 LOOP
3404
3405 G_DEBUG_LINE_NUM := l_debug_module||': '||510;
3406 -- Get all applicable root UEs and loop through them
3407 -- Get all information reg the UE in tbl type variables
3408 -- Log
3409 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' l_mr_header_id_tbl('||j||') = '||l_mr_header_id_tbl(j) );
3410 l_previous_instance_id := NULL;
3411 l_previous_org_id := NULL;
3412 l_previous_fleet_id := NULL;
3413 OPEN ue_mr_csr(l_mr_header_id_tbl(j));
3414 LOOP
3415 G_DEBUG_LINE_NUM := l_debug_module||': '||520;
3416 FETCH ue_mr_csr BULK COLLECT
3417 INTO l_ue_id_tbl,
3418 l_ue_due_date_tbl,
3419 l_ue_mr_header_id_tbl,
3420 l_ue_ii_id_tbl,
3421 l_ue_object_type_tbl,
3422 l_ue_defer_from_ue_tbl,
3423 l_ump_mr_title_tbl,
3424 l_ue_man_planned_flag_tbl,
3425 l_ue_csi_item_quantity_tbl,
3426 l_ue_grp_flag_tbl,
3427 l_ue_mr_downtime_tbl,
3428 l_uom_code_tbl,
3429 l_ue_maint_org_id_tbl,
3430 l_ue_maint_dept_id_tbl,
3431 l_ue_fleet_header_id_tbl
3432 LIMIT l_buffer_limit; -- For each MR, process 2000 UEs at a time
3433
3434 EXIT WHEN (l_ue_id_tbl IS NULL OR l_ue_id_tbl.COUNT = 0);
3435
3436 -- Iterate over Root UEs for primary plan
3437 FOR i IN l_ue_id_tbl.FIRST..l_ue_id_tbl.LAST
3438 LOOP
3439 G_DEBUG_LINE_NUM := l_debug_module||': '||530;
3440 -- Log
3441 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Processing Primary plan ue l_ue_id_tbl('||i||') = '||l_ue_id_tbl(i) );
3442
3443 -- Check whether the ue has similar properties as the previous ue
3444 IF (l_previous_instance_id = l_ue_ii_id_tbl(i)
3445 AND l_previous_org_id = l_ue_maint_org_id_tbl(i)
3446 AND l_previous_fleet_id = l_ue_fleet_header_id_tbl(i))
3447 THEN
3448
3449 -- No need to process UE. Simply insert the previously calculated values
3450 -- But we still need the ue headers data
3451 -- Log
3452 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' No need to process UE. Simply insert the previously calculated values' );
3453 G_DEBUG_LINE_NUM := l_debug_module||': '||535;
3454
3455 IF (l_ue_object_type_tbl(i) = 'SR')
3456 THEN
3457
3458 -- l_title := p_mr_title;
3459 IF (l_ue_defer_from_ue_tbl(i) is not null) then
3460 l_maintenance_type := 'DEFER_NONROUTINE';
3461 ELSE
3462 l_maintenance_type := 'NONROUTINE';
3463 END IF;
3464 ELSE
3465 --l_title := p_mr_title;
3466 IF (l_ue_defer_from_ue_tbl(i) is not null) then
3467 l_maintenance_type := 'DEFER_ROUTINE';
3468 ELSIF (l_ue_man_planned_flag_tbl(i) = 'Y') then
3469 l_maintenance_type := 'ROUTINE_UNKNOWN';
3470 ELSE
3471 l_maintenance_type := 'ROUTINE';
3472 END IF;
3473 END IF;
3474
3475
3476 -- Populate header data for UMP_MAINTENANCE_REQMNTS table
3477 l_ump_maint_rec.UNIT_EFFECTIVITY_ID := l_ue_id_tbl(i) ;
3478 l_ump_maint_rec.SIMULATION_PLAN_ID := l_plan_id;--l_primary_plan_id;--l_plan_id_tbl(i); -- plan_id
3479 /*IF (p_sim_plan_flag = 'Y')
3480 THEN
3481 l_ump_maint_rec.OBJECT_TYPE := 'SIM';-- Simulation UE
3482 ELSE
3483 l_ump_maint_rec.OBJECT_TYPE := 'UE'; -- Primary UE
3484 END IF;*/
3485 l_ump_maint_rec.OBJECT_TYPE := 'UE'; -- Primary UE
3486 l_ump_maint_rec.OPERATING_ORG_ID := l_ue_maint_org_id_tbl(i);
3487 l_ump_maint_rec.DUE_DATE := l_ue_due_date_tbl(i); -- Root UE due date
3488 l_ump_maint_rec.MAINTENANCE_TYPE_CODE:= l_maintenance_type;
3489 l_ump_maint_rec.TITLE := l_ump_mr_title_tbl(i);
3490 l_ump_maint_rec.ITEM_INSTANCE_ID := l_ue_ii_id_tbl(i) ; -- Root UE instance
3491 l_ump_maint_rec.DEPARTMENT_ID := l_ue_maint_dept_id_tbl(i);
3492 -- Added by debadey for VCP integration
3493 l_ump_maint_rec.FLEET_HEADER_ID := l_ue_fleet_header_id_tbl(i);
3494
3495
3496 -- Invoke update procedure
3497 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Calling Perform_Updates');
3498 Perform_Updates(l_ump_maint_rec,
3499 p_x_consolidated_res_tbl,
3500 p_x_consolidated_mat_tbl);
3501 G_DEBUG_LINE_NUM := l_debug_module||': '||538;
3502 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Back from Perform_Updates');
3503
3504 ELSE
3505 -- This becomes the previous instance in the next loop
3506 l_previous_instance_id := l_ue_ii_id_tbl(i);
3507 l_previous_org_id :=l_ue_maint_org_id_tbl(i);
3508 l_previous_fleet_id := l_ue_fleet_header_id_tbl(i);
3509
3510 -- Reset all storage variables that are at UE level
3511 p_x_consolidated_res_tbl.DELETE; --To store consolidated Rsource data for each parent UE
3512 p_x_consolidated_mat_tbl.DELETE; --To store consolidated Material data for each parent UE
3513
3514
3515 --Invoke Process Ues
3516 G_DEBUG_LINE_NUM := l_debug_module||': '||540;
3517 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Calling procedure Process_Ue');
3518 Process_Ue( p_unit_effectivity_id => l_ue_id_tbl(i),
3519 p_group_mr_flag => l_ue_grp_flag_tbl(i),
3520 p_mr_header_id => l_ue_mr_header_id_tbl(i),
3521 p_item_instance_id => l_ue_ii_id_tbl(i),
3522 p_item_quantity => l_ue_csi_item_quantity_tbl(i),
3523 p_maint_org_id => l_ue_maint_org_id_tbl(i),
3524 p_maint_dept_id => l_ue_maint_dept_id_tbl(i),
3525 p_object_type => l_ue_object_type_tbl(i),
3526 p_due_date => l_ue_due_date_tbl(i),
3527 p_ue_mr_downtime => l_ue_mr_downtime_tbl(i),
3528 p_mr_title => l_ump_mr_title_tbl(i),
3529 p_defer_from_ue_id => l_ue_defer_from_ue_tbl(i),
3530 p_plan_id => l_plan_id,
3531 p_manually_planned_flag => l_ue_man_planned_flag_tbl(i),
3532 p_sim_plan_flag => 'N',
3533 -- Added by debadey for VCP integration
3534 p_fleet_header_id => l_ue_fleet_header_id_tbl(i),
3535 p_x_consolidated_res_tbl => p_x_consolidated_res_tbl,
3536 p_x_consolidated_mat_tbl => p_x_consolidated_mat_tbl);
3537
3538 -- Log
3539 G_DEBUG_LINE_NUM := l_debug_module||': '||550;
3540 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Back from procedure Process_Ue. Now p_x_consolidated_res_tbl.COUNT = '||p_x_consolidated_res_tbl.COUNT||' And p_x_consolidated_mat_tbl.COUNT = '||p_x_consolidated_mat_tbl.COUNT);
3541
3542 END IF;
3543 END LOOP; -- End of looping over root UEs table populated during FETCH
3544 G_DEBUG_LINE_NUM := l_debug_module||': '||560;
3545 -- Log
3546 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' End of Loop for this round of UEs' );
3547
3548 END LOOP; -- End of looping over all root UEs for an MR
3549 G_DEBUG_LINE_NUM := l_debug_module||': '||570;
3550 CLOSE ue_mr_csr;
3551 G_DEBUG_LINE_NUM := l_debug_module||': '||580;
3552 -- Log
3553 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' End of Loop for the MR '||l_mr_header_id_tbl(j) );
3554 END LOOP; -- End of looping over applicable MRs got through FETCH
3555 G_DEBUG_LINE_NUM := l_debug_module||': '||590;
3556 END LOOP; -- End of processing of all MRs for the primary plan
3557 G_DEBUG_LINE_NUM := l_debug_module||': '||600;
3558 CLOSE ahl_mr_headers_csr;
3559 G_DEBUG_LINE_NUM := l_debug_module||': '||610;
3560 -- Log
3561 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' End of MR processing in Primary Plan' );
3562 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Process all SR based UEs for primary plan' );
3563
3564 /*----- Process all SR based UE for PRIMARY plan------*/
3565 G_DEBUG_LINE_NUM := l_debug_module||': '||620;
3566 OPEN ue_sr_csr;
3567 LOOP
3568 G_DEBUG_LINE_NUM := l_debug_module||': '||630;
3569 FETCH ue_sr_csr BULK COLLECT
3570 INTO l_ue_id_tbl,
3571 l_ue_due_date_tbl,
3572 l_ue_mr_header_id_tbl,
3573 l_ue_ii_id_tbl,
3574 l_ue_object_type_tbl,
3575 l_ue_defer_from_ue_tbl,
3576 l_ue_man_planned_flag_tbl,
3577 l_ump_mr_title_tbl, -- This will capture SR title
3578 l_ue_csi_item_quantity_tbl,
3579 l_ue_grp_flag_tbl,
3580 l_ue_maint_org_id_tbl,
3581 l_ue_maint_dept_id_tbl,
3582 l_ue_fleet_header_id_tbl
3583 LIMIT l_buffer_limit; -- For each MR, process 2000 UEs at a time
3584
3585
3586 EXIT WHEN (l_ue_id_tbl IS NULL OR l_ue_id_tbl.COUNT = 0);
3587
3588 -- Iterate over Root UEs for primary plan
3589 FOR i IN l_ue_id_tbl.FIRST..l_ue_id_tbl.LAST
3590 LOOP
3591 G_DEBUG_LINE_NUM := l_debug_module||': '||640;
3592 -- Reset all storage variables that are at UE level
3593 p_x_consolidated_res_tbl.DELETE; --To store consolidated Rsource data for each parent UE
3594 p_x_consolidated_mat_tbl.DELETE; --To store consolidated Material data for each parent UE
3595
3596 -- Log
3597 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' In ue_cr_csr l_ue_id_tbl('||i||') = '||l_ue_id_tbl(i));
3598 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Calling Process_Ue. Now p_x_consolidated_res_tbl.COUNT = '||p_x_consolidated_res_tbl.COUNT||' and p_x_consolidated_mat_tbl.COUNT = '||p_x_consolidated_mat_tbl.COUNT );
3599 G_DEBUG_LINE_NUM := l_debug_module||': '||650;
3600 --Invoke Process Ues
3601 Process_Ue( p_unit_effectivity_id => l_ue_id_tbl(i),
3602 p_group_mr_flag => l_ue_grp_flag_tbl(i),
3603 p_mr_header_id => NULL,
3604 p_item_instance_id => l_ue_ii_id_tbl(i),
3605 p_item_quantity => l_ue_csi_item_quantity_tbl(i),
3606 p_maint_org_id => l_ue_maint_org_id_tbl(i),
3607 p_maint_dept_id => l_ue_maint_dept_id_tbl(i),
3608 p_object_type => l_ue_object_type_tbl(i),
3609 p_due_date => l_ue_due_date_tbl(i),
3610 p_ue_mr_downtime => NULL,
3611 p_mr_title => l_ump_mr_title_tbl(i),
3612 p_defer_from_ue_id => l_ue_defer_from_ue_tbl(i),
3613 p_plan_id => l_plan_id,
3614 p_manually_planned_flag => l_ue_man_planned_flag_tbl(i),
3615 p_sim_plan_flag => 'N',
3616 -- Added by debadey for VCP integration
3617 p_fleet_header_id => l_ue_fleet_header_id_tbl(i),
3618 p_x_consolidated_res_tbl => p_x_consolidated_res_tbl,
3619 p_x_consolidated_mat_tbl => p_x_consolidated_mat_tbl);
3620
3621 -- Log
3622 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' After call to Process_Ue p_x_consolidated_res_tbl.COUNT = '||p_x_consolidated_res_tbl.COUNT||' and p_x_consolidated_mat_tbl.COUNT = '||p_x_consolidated_mat_tbl.COUNT );
3623 END LOOP;
3624 G_DEBUG_LINE_NUM := l_debug_module||': '||660;
3625
3626 END LOOP;
3627 G_DEBUG_LINE_NUM := l_debug_module||': '||670;
3628 CLOSE ue_sr_csr;
3629 G_DEBUG_LINE_NUM := l_debug_module||': '||680;
3630 -- Log
3631 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' End of looping over SR based UEs for primary plan' );
3632 ELSE
3633 -- Process Simulation plan
3634 -- Log
3635 G_DEBUG_LINE_NUM := l_debug_module||': '||690;
3636 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Start processing specific Simulation Plans UE for the plan '||l_plan_id );
3637 /* Bug 13096151 - Remove all UE, resource and material for the plan from the bom tables */
3638 G_DEBUG_LINE_NUM := l_debug_module||': '||692;
3639 DELETE FROM AHL_UMP_RESOURCE_REQMNTS
3640 WHERE maintenance_reqmnt_id IN
3641 (SELECT maintenance_reqmnt_id
3642 FROM AHL_UMP_MAINT_REQMNTS
3643 WHERE object_type = 'SIM'
3644 AND simulation_plan_id = l_plan_id);
3645
3646
3647 G_DEBUG_LINE_NUM := l_debug_module||': '||694;
3648 DELETE FROM AHL_UMP_MATERIAL_REQMNTS
3649 WHERE maintenance_reqmnt_id IN
3650 (SELECT maintenance_reqmnt_id
3651 FROM AHL_UMP_MAINT_REQMNTS
3652 WHERE object_type = 'SIM'
3653 AND simulation_plan_id = l_plan_id);
3654
3655
3656 G_DEBUG_LINE_NUM := l_debug_module||': '||696;
3657 DELETE FROM AHL_UMP_MAINT_REQMNTS
3658 WHERE object_type = 'SIM'
3659 AND simulation_plan_id = l_plan_id;
3660 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' BOM UE deleted for plan id = '||l_plan_id);
3661 G_DEBUG_LINE_NUM := l_debug_module||': '||698;
3662 /* End of changes for bug 13096151 */
3663
3664 OPEN ahl_sim_mr_headers_csr(l_plan_id);
3665 LOOP
3666 G_DEBUG_LINE_NUM := l_debug_module||': '||700;
3667 FETCH ahl_sim_mr_headers_csr
3668 BULK COLLECT
3669 INTO l_mr_header_id_tbl,l_ump_mr_title_tbl
3670 LIMIT l_buffer_limit;
3671 G_DEBUG_LINE_NUM := l_debug_module||': '||710;
3672 -- Log
3673
3674 EXIT WHEN (l_mr_header_id_tbl IS NULL OR l_mr_header_id_tbl.COUNT = 0);
3675
3676 -- Iterate over MR header id
3677 FOR j IN l_mr_header_id_tbl.FIRST..l_mr_header_id_tbl.LAST
3678 LOOP
3679 G_DEBUG_LINE_NUM := l_debug_module||': '||720;
3680 -- Log
3681 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Processing sim plan l_mr_header_id_tbl('||j||') ='||l_mr_header_id_tbl(j));
3682 l_previous_instance_id := NULL;
3683 l_previous_org_id := NULL;
3684 l_previous_fleet_id := NULL;
3685 -- Get all applicable root UEs and loop through them
3686 -- Get all information reg the UE in tbl type variables
3687 OPEN ue_sim_mr_csr(l_mr_header_id_tbl(j), l_plan_id);
3688 LOOP
3689 G_DEBUG_LINE_NUM := l_debug_module||': '||730;
3690 FETCH ue_sim_mr_csr BULK COLLECT
3691 INTO l_ue_id_tbl,
3692 l_plan_id_tbl,
3693 l_ue_due_date_tbl,
3694 l_ue_mr_header_id_tbl,
3695 l_ue_ii_id_tbl,
3696 l_ue_object_type_tbl,
3697 l_ue_defer_from_ue_tbl,
3698 l_ump_mr_title_tbl,
3699 l_ue_man_planned_flag_tbl,
3700 l_ue_csi_item_quantity_tbl,
3701 l_ue_grp_flag_tbl,
3702 l_ue_mr_downtime_tbl,
3703 l_uom_code_tbl,
3704 l_ue_maint_org_id_tbl,
3705 l_ue_maint_dept_id_tbl,
3706 l_ue_fleet_header_id_tbl
3707 LIMIT l_buffer_limit; -- For each MR, process 2000 UEs at a time
3708
3709
3710
3711 EXIT WHEN (l_ue_id_tbl IS NULL OR l_ue_id_tbl.COUNT = 0);
3712
3713
3714 -- Iterate over Root UEs for simulation plan
3715 FOR i IN l_ue_id_tbl.FIRST..l_ue_id_tbl.LAST
3716 LOOP
3717 G_DEBUG_LINE_NUM := l_debug_module||': '||740;
3718 -- Log
3719 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Processing UE - l_ue_id_tbl('||i||') ='||l_ue_id_tbl(i));
3720 -- Check whether the ue has similar properties as the previous ue
3721 IF (l_previous_instance_id = l_ue_ii_id_tbl(i)
3722 AND l_previous_org_id = l_ue_maint_org_id_tbl(i)
3723 AND l_previous_fleet_id = l_ue_fleet_header_id_tbl(i))
3724 THEN
3725
3726 -- No need to process UE. Simply insert the previously calculated values
3727 -- But we still need the ue headers data
3728 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' No need to process UE. Simply insert the previously calculated values' );
3729 G_DEBUG_LINE_NUM := l_debug_module||': '||745;
3730
3731 IF (l_ue_object_type_tbl(i) = 'SR')
3732 THEN
3733
3734 -- l_title := p_mr_title;
3735 IF (l_ue_defer_from_ue_tbl(i) is not null) then
3736 l_maintenance_type := 'DEFER_NONROUTINE';
3737 ELSE
3738 l_maintenance_type := 'NONROUTINE';
3739 END IF;
3740 ELSE
3741 --l_title := p_mr_title;
3742 IF (l_ue_defer_from_ue_tbl(i) is not null) then
3743 l_maintenance_type := 'DEFER_ROUTINE';
3744 ELSIF (l_ue_man_planned_flag_tbl(i) = 'Y') then
3745 l_maintenance_type := 'ROUTINE_UNKNOWN';
3746 ELSE
3747 l_maintenance_type := 'ROUTINE';
3748 END IF;
3749 END IF;
3750
3751
3752 -- Populate header data for UMP_MAINTENANCE_REQMNTS table
3753 l_ump_maint_rec.UNIT_EFFECTIVITY_ID := l_ue_id_tbl(i) ;
3754 l_ump_maint_rec.SIMULATION_PLAN_ID := l_plan_id_tbl(i);--l_primary_plan_id;--l_plan_id_tbl(i); -- plan_id
3755 /*IF (p_sim_plan_flag = 'Y')
3756 THEN
3757 l_ump_maint_rec.OBJECT_TYPE := 'SIM';-- Simulation UE
3758 ELSE
3759 l_ump_maint_rec.OBJECT_TYPE := 'UE'; -- Primary UE
3760 END IF;*/
3761 l_ump_maint_rec.OBJECT_TYPE := 'SIM'; -- Simulation UE
3762 l_ump_maint_rec.OPERATING_ORG_ID := l_ue_maint_org_id_tbl(i);
3763 l_ump_maint_rec.DUE_DATE := l_ue_due_date_tbl(i); -- Root UE due date
3764 l_ump_maint_rec.MAINTENANCE_TYPE_CODE:= l_maintenance_type;
3765 l_ump_maint_rec.TITLE := l_ump_mr_title_tbl(i);
3766 l_ump_maint_rec.ITEM_INSTANCE_ID := l_ue_ii_id_tbl(i) ; -- Root UE instance
3767 l_ump_maint_rec.DEPARTMENT_ID := l_ue_maint_dept_id_tbl(i);
3768 -- Added by debadey for VCP integration
3769 l_ump_maint_rec.FLEET_HEADER_ID := l_ue_fleet_header_id_tbl(i);
3770 -- Invoke update procedure
3771 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Calling procedure Perform_Updates');
3772 Perform_Updates(l_ump_maint_rec,
3773 p_x_consolidated_res_tbl,
3774 p_x_consolidated_mat_tbl);
3775 G_DEBUG_LINE_NUM := l_debug_module||': '||748;
3776 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Back from procedure Perform_Updates');
3777
3778 ELSE
3779 -- This becomes the previous instance in the next loop
3780 l_previous_instance_id := l_ue_ii_id_tbl(i);
3781 l_previous_org_id :=l_ue_maint_org_id_tbl(i);
3782 l_previous_fleet_id := l_ue_fleet_header_id_tbl(i);
3783 -- Reset all storage variables that are at UE level
3784 p_x_consolidated_res_tbl.DELETE; --To store consolidated Rsource data for each parent UE
3785 p_x_consolidated_mat_tbl.DELETE; --To store consolidated Material data for each parent UE
3786
3787 -- Log
3788 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Calling Process_Ue for sim plan UE. p_x_consolidated_res_tbl.COUNT = '||p_x_consolidated_res_tbl.COUNT||' p_x_consolidated_mat_tbl.COUNT = '||p_x_consolidated_mat_tbl.COUNT);
3789 --Invoke Process_Ue
3790 G_DEBUG_LINE_NUM := l_debug_module||': '||750;
3791 Process_Ue( p_unit_effectivity_id => l_ue_id_tbl(i),
3792 p_group_mr_flag => l_ue_grp_flag_tbl(i),
3793 p_mr_header_id => l_ue_mr_header_id_tbl(i),
3794 p_item_instance_id => l_ue_ii_id_tbl(i),
3795 p_item_quantity => l_ue_csi_item_quantity_tbl(i),
3796 p_maint_org_id => l_ue_maint_org_id_tbl(i),
3797 p_maint_dept_id => l_ue_maint_dept_id_tbl(i),
3798 p_object_type => l_ue_object_type_tbl(i),
3799 p_due_date => l_ue_due_date_tbl(i),
3800 p_ue_mr_downtime => l_ue_mr_downtime_tbl(i),
3801 p_mr_title => l_ump_mr_title_tbl(i),
3802 p_defer_from_ue_id => l_ue_defer_from_ue_tbl(i),
3803 p_plan_id => l_plan_id_tbl(i),
3804 p_manually_planned_flag => l_ue_man_planned_flag_tbl(i),
3805 p_sim_plan_flag => 'Y',
3806 -- Added by debadey for VCP integration
3807 p_fleet_header_id => l_ue_fleet_header_id_tbl(i),
3808 p_x_consolidated_res_tbl => p_x_consolidated_res_tbl,
3809 p_x_consolidated_mat_tbl => p_x_consolidated_mat_tbl);
3810
3811 -- Log
3812 G_DEBUG_LINE_NUM := l_debug_module||': '||758;
3813 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Back from Process_Ue for sim plan UE.Now p_x_consolidated_res_tbl.COUNT = '||p_x_consolidated_res_tbl.COUNT||
3814 ' p_x_consolidated_mat_tbl.COUNT = '||p_x_consolidated_mat_tbl.COUNT);
3815 END IF; -- End of checking whether the UE properties are same as the previous UE
3816
3817 END LOOP; -- End of looping over root UEs table populated during FETCH
3818 G_DEBUG_LINE_NUM := l_debug_module||': '||760;
3819
3820 END LOOP; -- End of looping over all root UEs for an MR
3821 G_DEBUG_LINE_NUM := l_debug_module||': '||770;
3822 CLOSE ue_sim_mr_csr;
3823 G_DEBUG_LINE_NUM := l_debug_module||': '||780;
3824 -- Log
3825 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' End of looping over all sim plan root UEs for the MR');
3826 END LOOP; -- End of looping over applicable MRs got through FETCH
3827 G_DEBUG_LINE_NUM := l_debug_module||': '||790;
3828
3829 END LOOP; -- End of processing of all MRs for the simulation plan
3830 G_DEBUG_LINE_NUM := l_debug_module||': '||800;
3831 CLOSE ahl_sim_mr_headers_csr;
3832 G_DEBUG_LINE_NUM := l_debug_module||': '||810;
3833 -- Log
3834 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' End of looping over all MRs for Simulation Plan');
3835 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Process SR based UE for Simualtion Plan');
3836 -- Process all SR based UE for Simulation Plan
3837 G_DEBUG_LINE_NUM := l_debug_module||': '||820;
3838 OPEN ue_sim_sr_csr(l_plan_id);
3839 LOOP
3840 FETCH ue_sim_sr_csr BULK COLLECT
3841 INTO l_ue_id_tbl,
3842 l_plan_id_tbl,
3843 l_ue_due_date_tbl,
3844 l_ue_mr_header_id_tbl,
3845 l_ue_ii_id_tbl,
3846 l_ue_object_type_tbl,
3847 l_ue_defer_from_ue_tbl,
3848 l_ump_mr_title_tbl, -- This will capture SR title
3849 l_ue_man_planned_flag_tbl,
3850 l_ue_csi_item_quantity_tbl,
3851 l_ue_grp_flag_tbl,
3852 l_ue_maint_org_id_tbl,
3853 l_ue_maint_dept_id_tbl,
3854 l_ue_fleet_header_id_tbl
3855 LIMIT l_buffer_limit; -- For each MR, process 2000 UEs at a time
3856
3857
3858 EXIT WHEN (l_ue_id_tbl IS NULL OR l_ue_id_tbl.COUNT = 0);
3859
3860 -- Iterate over Root UEs for primary plan
3861 FOR i IN l_ue_id_tbl.FIRST..l_ue_id_tbl.LAST
3862 LOOP
3863 G_DEBUG_LINE_NUM := l_debug_module||': '||830;
3864 -- Reset all storage variables that are at UE level
3865 p_x_consolidated_res_tbl.DELETE; --To store consolidated Rsource data for each parent UE
3866 p_x_consolidated_mat_tbl.DELETE; --To store consolidated Material data for each parent UE
3867
3868 -- Log
3869 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Processing UE - l_ue_id_tbl('||i||') = '||l_ue_id_tbl(i));
3870 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Calling Process_Ue for sim plan UE. p_x_consolidated_res_tbl.COUNT = '||p_x_consolidated_res_tbl.COUNT||
3871 ' p_x_consolidated_mat_tbl.COUNT = '||p_x_consolidated_mat_tbl.COUNT);
3872 --Invoke Process Ues
3873 G_DEBUG_LINE_NUM := l_debug_module||': '||840;
3874 Process_Ue( p_unit_effectivity_id => l_ue_id_tbl(i),
3875 p_group_mr_flag => l_ue_grp_flag_tbl(i),
3876 p_mr_header_id => NULL,
3877 p_item_instance_id => l_ue_ii_id_tbl(i),
3878 p_item_quantity => l_ue_csi_item_quantity_tbl(i),
3879 p_maint_org_id => l_ue_maint_org_id_tbl(i),
3880 p_maint_dept_id => l_ue_maint_dept_id_tbl(i),
3881 p_object_type => l_ue_object_type_tbl(i),
3882 p_due_date => l_ue_due_date_tbl(i),
3883 p_ue_mr_downtime => NULL,
3884 p_mr_title => l_ump_mr_title_tbl(i),
3885 p_defer_from_ue_id => l_ue_defer_from_ue_tbl(i),
3886 p_plan_id => l_plan_id_tbl(i),
3887 p_manually_planned_flag => l_ue_man_planned_flag_tbl(i),
3888 p_sim_plan_flag => 'Y',
3889 -- Added by debadey for VCP integration
3890 p_fleet_header_id => l_ue_fleet_header_id_tbl(i),
3891 p_x_consolidated_res_tbl => p_x_consolidated_res_tbl,
3892 p_x_consolidated_mat_tbl => p_x_consolidated_mat_tbl);
3893
3894
3895 G_DEBUG_LINE_NUM := l_debug_module||': '||850;
3896 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Back from Process_Ue for sim plan UE.'||
3897 ' Now p_x_consolidated_res_tbl.COUNT = '||p_x_consolidated_res_tbl.COUNT||
3898 ' p_x_consolidated_mat_tbl.COUNT = '||p_x_consolidated_mat_tbl.COUNT);
3899
3900 END LOOP;
3901 G_DEBUG_LINE_NUM := l_debug_module||': '||860;
3902
3903
3904 END LOOP;
3905 G_DEBUG_LINE_NUM := l_debug_module||': '||870;
3906 CLOSE ue_sim_sr_csr;
3907 G_DEBUG_LINE_NUM := l_debug_module||': '||880;
3908 -- Log
3909 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' End of Processing when all plans flag is not set.');
3910 END IF; -- End of check for primary/simulation plan
3911 G_DEBUG_LINE_NUM := l_debug_module||': '||890;
3912 END IF; -- End of check for all_plan_flag
3913 G_DEBUG_LINE_NUM := l_debug_module||': '||900;
3914
3915 -- Set the status
3916 IF (g_ret_status <>0 AND (retcode = 0 OR retcode = 1))
3917 THEN
3918 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Setting proper return status. retcode = '||retcode);
3919 retcode := g_ret_status;
3920 END IF;
3921 -- In case of less than 2000 updates commit has not happened till now.
3922 IF (retcode = 2)
3923 THEN
3924 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Since return code is 2 no further commit to be done');
3925 ELSE
3926 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Commiting in Create_Update_Ump_Bom');
3927 COMMIT;
3928 g_commit_counter := 0;
3929 END IF;
3930 EXCEPTION
3931 WHEN OTHERS
3932 THEN
3933 -- Log
3934 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL)
3935 THEN
3936 g_err_msg := substr(SQLERRM, 1, 3900);
3937 g_err_code := SQLCODE;
3938 fnd_log.string (G_DEBUG_STMT, l_debug_module, G_DEBUG_LINE_NUM||' Error in procedure Create_Update_Ump_Bom');
3939 fnd_log.string (G_DEBUG_STMT, l_debug_module, G_DEBUG_LINE_NUM||' error code is '||g_err_code);
3940 fnd_log.string (G_DEBUG_STMT, l_debug_module, G_DEBUG_LINE_NUM||' error message is '||g_err_msg);
3941 END IF;
3942 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' Error in procedure Create_Update_Ump_Bom');
3943 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' error code is '||g_err_code);
3944 FND_FILE.put_line(FND_FILE.log, G_DEBUG_LINE_NUM||' error message is '||g_err_msg);
3945 -- Rollback changes since last commit
3946 ROLLBACK;
3947 retcode := 2;
3948 errbuf := g_err_msg;
3949
3950 END Create_Update_Ump_Bom;
3951 END Ahl_Ump_Bom_Pvt;
[Home] [Help]