[Home] [Help]
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);
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,
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;
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:' ||
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)
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
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
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;
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
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
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;
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;
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;
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;
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
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;
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;
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
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,
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:' ||
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);
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
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
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
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
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;
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
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
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
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;
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:
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;
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:
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)
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:
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||
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
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
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);
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;
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;
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)
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:
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
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
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
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
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;
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;
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;
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
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:
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
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,
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
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
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;
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
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:
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;
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;
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
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;
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:
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;
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 -------------------------------------------------
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 ||
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');
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:
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;
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
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
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;
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;
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);
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;
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);
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
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;
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
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;
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;
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
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
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;
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
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;
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
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
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;
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;
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:
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:
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,
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,
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
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)||
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,
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),
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;
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),
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;
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
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;
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
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
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
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:
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
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');
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
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
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
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
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:
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:
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
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);
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,
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
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,
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:
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)
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)
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:
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
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
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);
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||
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;
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');
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;
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;
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
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
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
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');
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
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;
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;
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;
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:
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)
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 */
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
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;
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:
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))
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
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:
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),
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
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:
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' );
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;
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;
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;
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
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
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),
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;
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' );
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;
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
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
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
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 */
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
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;
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);
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),
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
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;
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
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);
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
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
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 */
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),
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),
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;
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;
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
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.********************************* */
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
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;
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
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;
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
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));
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)
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
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;
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);
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),
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;
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;
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;
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;
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;
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),
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),
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;
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
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);
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
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
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;
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);
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),
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
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);
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
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;
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),
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;
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)
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;
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
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;
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;
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;