[Home] [Help]
252: BEGIN
253:
254: SAVEPOINT Create_SR_Unit_Effectivity_Pvt;
255:
256: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
257: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Create_SR_Unit_Effectivity Procedure');
258: END IF;
259:
260: -- Initialize message list
253:
254: SAVEPOINT Create_SR_Unit_Effectivity_Pvt;
255:
256: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
257: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Create_SR_Unit_Effectivity Procedure');
258: END IF;
259:
260: -- Initialize message list
261: --AMSRINIV. Bug 5470730. Removing message initialization.
274: l_customer_product_id := cs_servicerequest_pvt.user_hooks_rec.customer_product_id;
275: l_status_id := cs_servicerequest_pvt.user_hooks_rec.status_id;
276: l_exp_resolution_date := cs_servicerequest_pvt.user_hooks_rec.exp_resolution_date;
277:
278: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
279: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' input values:' );
280: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' Request id::' || cs_servicerequest_pvt.user_hooks_rec.request_id);
281: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'Status flag:' || cs_servicerequest_pvt.user_hooks_rec.status_flag);
282: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'old_type_cmro_flag:' || cs_servicerequest_pvt.user_hooks_rec.old_type_cmro_flag);
275: l_status_id := cs_servicerequest_pvt.user_hooks_rec.status_id;
276: l_exp_resolution_date := cs_servicerequest_pvt.user_hooks_rec.exp_resolution_date;
277:
278: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
279: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' input values:' );
280: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' Request id::' || cs_servicerequest_pvt.user_hooks_rec.request_id);
281: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'Status flag:' || cs_servicerequest_pvt.user_hooks_rec.status_flag);
282: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'old_type_cmro_flag:' || cs_servicerequest_pvt.user_hooks_rec.old_type_cmro_flag);
283: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'new_type_cmro_flag:' || cs_servicerequest_pvt.user_hooks_rec.new_type_cmro_flag);
276: l_exp_resolution_date := cs_servicerequest_pvt.user_hooks_rec.exp_resolution_date;
277:
278: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
279: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' input values:' );
280: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' Request id::' || cs_servicerequest_pvt.user_hooks_rec.request_id);
281: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'Status flag:' || cs_servicerequest_pvt.user_hooks_rec.status_flag);
282: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'old_type_cmro_flag:' || cs_servicerequest_pvt.user_hooks_rec.old_type_cmro_flag);
283: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'new_type_cmro_flag:' || cs_servicerequest_pvt.user_hooks_rec.new_type_cmro_flag);
284: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'customer_product_id:' || cs_servicerequest_pvt.user_hooks_rec.customer_product_id);
277:
278: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
279: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' input values:' );
280: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' Request id::' || cs_servicerequest_pvt.user_hooks_rec.request_id);
281: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'Status flag:' || cs_servicerequest_pvt.user_hooks_rec.status_flag);
282: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'old_type_cmro_flag:' || cs_servicerequest_pvt.user_hooks_rec.old_type_cmro_flag);
283: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'new_type_cmro_flag:' || cs_servicerequest_pvt.user_hooks_rec.new_type_cmro_flag);
284: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'customer_product_id:' || cs_servicerequest_pvt.user_hooks_rec.customer_product_id);
285: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'status id:' || cs_servicerequest_pvt.user_hooks_rec.status_id);
278: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
279: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' input values:' );
280: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' Request id::' || cs_servicerequest_pvt.user_hooks_rec.request_id);
281: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'Status flag:' || cs_servicerequest_pvt.user_hooks_rec.status_flag);
282: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'old_type_cmro_flag:' || cs_servicerequest_pvt.user_hooks_rec.old_type_cmro_flag);
283: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'new_type_cmro_flag:' || cs_servicerequest_pvt.user_hooks_rec.new_type_cmro_flag);
284: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'customer_product_id:' || cs_servicerequest_pvt.user_hooks_rec.customer_product_id);
285: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'status id:' || cs_servicerequest_pvt.user_hooks_rec.status_id);
286: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'exp_resolution_date:' || cs_servicerequest_pvt.user_hooks_rec.exp_resolution_date);
279: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' input values:' );
280: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' Request id::' || cs_servicerequest_pvt.user_hooks_rec.request_id);
281: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'Status flag:' || cs_servicerequest_pvt.user_hooks_rec.status_flag);
282: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'old_type_cmro_flag:' || cs_servicerequest_pvt.user_hooks_rec.old_type_cmro_flag);
283: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'new_type_cmro_flag:' || cs_servicerequest_pvt.user_hooks_rec.new_type_cmro_flag);
284: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'customer_product_id:' || cs_servicerequest_pvt.user_hooks_rec.customer_product_id);
285: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'status id:' || cs_servicerequest_pvt.user_hooks_rec.status_id);
286: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'exp_resolution_date:' || cs_servicerequest_pvt.user_hooks_rec.exp_resolution_date);
287: END IF;
280: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' Request id::' || cs_servicerequest_pvt.user_hooks_rec.request_id);
281: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'Status flag:' || cs_servicerequest_pvt.user_hooks_rec.status_flag);
282: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'old_type_cmro_flag:' || cs_servicerequest_pvt.user_hooks_rec.old_type_cmro_flag);
283: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'new_type_cmro_flag:' || cs_servicerequest_pvt.user_hooks_rec.new_type_cmro_flag);
284: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'customer_product_id:' || cs_servicerequest_pvt.user_hooks_rec.customer_product_id);
285: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'status id:' || cs_servicerequest_pvt.user_hooks_rec.status_id);
286: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'exp_resolution_date:' || cs_servicerequest_pvt.user_hooks_rec.exp_resolution_date);
287: END IF;
288:
281: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'Status flag:' || cs_servicerequest_pvt.user_hooks_rec.status_flag);
282: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'old_type_cmro_flag:' || cs_servicerequest_pvt.user_hooks_rec.old_type_cmro_flag);
283: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'new_type_cmro_flag:' || cs_servicerequest_pvt.user_hooks_rec.new_type_cmro_flag);
284: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'customer_product_id:' || cs_servicerequest_pvt.user_hooks_rec.customer_product_id);
285: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'status id:' || cs_servicerequest_pvt.user_hooks_rec.status_id);
286: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'exp_resolution_date:' || cs_servicerequest_pvt.user_hooks_rec.exp_resolution_date);
287: END IF;
288:
289:
282: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'old_type_cmro_flag:' || cs_servicerequest_pvt.user_hooks_rec.old_type_cmro_flag);
283: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'new_type_cmro_flag:' || cs_servicerequest_pvt.user_hooks_rec.new_type_cmro_flag);
284: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'customer_product_id:' || cs_servicerequest_pvt.user_hooks_rec.customer_product_id);
285: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'status id:' || cs_servicerequest_pvt.user_hooks_rec.status_id);
286: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'exp_resolution_date:' || cs_servicerequest_pvt.user_hooks_rec.exp_resolution_date);
287: END IF;
288:
289:
290: --******************************
319: x_return_status := FND_API.G_RET_STS_SUCCESS;
320: RETURN;
321: END IF;
322:
323: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
324: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' Processing for CMRO type of SR');
325: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' BEFORE Calling AHL_UTIL_PKG.Get_Appln_Usage' );
326: END IF;
327:
320: RETURN;
321: END IF;
322:
323: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
324: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' Processing for CMRO type of SR');
325: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' BEFORE Calling AHL_UTIL_PKG.Get_Appln_Usage' );
326: END IF;
327:
328: --Call the procedure AHL_UTIL_PKG.Get_Appln_Usage
321: END IF;
322:
323: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
324: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' Processing for CMRO type of SR');
325: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' BEFORE Calling AHL_UTIL_PKG.Get_Appln_Usage' );
326: END IF;
327:
328: --Call the procedure AHL_UTIL_PKG.Get_Appln_Usage
329: AHL_UTIL_PKG.Get_Appln_Usage
331: x_appln_code => l_appln_code,
332: x_return_status => l_return_status
333: );
334:
335: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
336: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' After Calling AHL_UTIL_PKG.Get_Appln_Usage successfully' );
337: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' l_appln_code: ' || l_appln_code);
338: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' l_return_status: ' || l_return_status);
339: END IF;
332: x_return_status => l_return_status
333: );
334:
335: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
336: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' After Calling AHL_UTIL_PKG.Get_Appln_Usage successfully' );
337: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' l_appln_code: ' || l_appln_code);
338: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' l_return_status: ' || l_return_status);
339: END IF;
340:
333: );
334:
335: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
336: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' After Calling AHL_UTIL_PKG.Get_Appln_Usage successfully' );
337: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' l_appln_code: ' || l_appln_code);
338: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' l_return_status: ' || l_return_status);
339: END IF;
340:
341:
334:
335: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
336: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' After Calling AHL_UTIL_PKG.Get_Appln_Usage successfully' );
337: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' l_appln_code: ' || l_appln_code);
338: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' l_return_status: ' || l_return_status);
339: END IF;
340:
341:
342: --Set the return status to an error and raise an error message if the return status is an error.
427: l_uc_hdr_id := null;
428: END IF;
429: END IF;
430:
431: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
432: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' BEFORE Calling TABLE HANDLER AHL_UNIT_EFFECTIVITIES_PKG.Insert_Row' );
433: END IF;
434:
435:
428: END IF;
429: END IF;
430:
431: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
432: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' BEFORE Calling TABLE HANDLER AHL_UNIT_EFFECTIVITIES_PKG.Insert_Row' );
433: END IF;
434:
435:
436: --******************************
499: X_LAST_UPDATE_DATE => sysdate,
500: X_LAST_UPDATED_BY => fnd_global.user_id,
501: X_LAST_UPDATE_LOGIN => fnd_global.login_id );
502:
503: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
504: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' AFTER Calling TABLE HANDLER AHL_UNIT_EFFECTIVITIES_PKG.Insert_Row' );
505: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' Unit Effectivity ID:' || l_unit_effectivity_id);
506: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' Return Status:' || x_return_status);
507: END IF;
500: X_LAST_UPDATED_BY => fnd_global.user_id,
501: X_LAST_UPDATE_LOGIN => fnd_global.login_id );
502:
503: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
504: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' AFTER Calling TABLE HANDLER AHL_UNIT_EFFECTIVITIES_PKG.Insert_Row' );
505: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' Unit Effectivity ID:' || l_unit_effectivity_id);
506: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' Return Status:' || x_return_status);
507: END IF;
508:
501: X_LAST_UPDATE_LOGIN => fnd_global.login_id );
502:
503: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
504: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' AFTER Calling TABLE HANDLER AHL_UNIT_EFFECTIVITIES_PKG.Insert_Row' );
505: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' Unit Effectivity ID:' || l_unit_effectivity_id);
506: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' Return Status:' || x_return_status);
507: END IF;
508:
509: -- End Processing
502:
503: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
504: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' AFTER Calling TABLE HANDLER AHL_UNIT_EFFECTIVITIES_PKG.Insert_Row' );
505: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' Unit Effectivity ID:' || l_unit_effectivity_id);
506: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' Return Status:' || x_return_status);
507: END IF;
508:
509: -- End Processing
510:
507: END IF;
508:
509: -- End Processing
510:
511: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
512: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Create_SR_Unit_Effectivity Procedure');
513: END IF;
514:
515: EXCEPTION
508:
509: -- End Processing
510:
511: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
512: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Create_SR_Unit_Effectivity Procedure');
513: END IF;
514:
515: EXCEPTION
516: WHEN FND_API.G_EXC_ERROR THEN
515: EXCEPTION
516: WHEN FND_API.G_EXC_ERROR THEN
517: ROLLBACK TO Create_SR_Unit_Effectivity_Pvt;
518: x_return_status := FND_API.G_RET_STS_ERROR;
519: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
520: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' Error: Return Status:' || x_return_status);
521: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' Error: Msg Count:' || fnd_msg_pub.count_msg);
522: END IF;
523:
516: WHEN FND_API.G_EXC_ERROR THEN
517: ROLLBACK TO Create_SR_Unit_Effectivity_Pvt;
518: x_return_status := FND_API.G_RET_STS_ERROR;
519: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
520: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' Error: Return Status:' || x_return_status);
521: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' Error: Msg Count:' || fnd_msg_pub.count_msg);
522: END IF;
523:
524: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
517: ROLLBACK TO Create_SR_Unit_Effectivity_Pvt;
518: x_return_status := FND_API.G_RET_STS_ERROR;
519: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
520: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' Error: Return Status:' || x_return_status);
521: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' Error: Msg Count:' || fnd_msg_pub.count_msg);
522: END IF;
523:
524: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
525: ROLLBACK TO Create_SR_Unit_Effectivity_Pvt;
523:
524: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
525: ROLLBACK TO Create_SR_Unit_Effectivity_Pvt;
526: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
527: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
528: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' UnExpError: Return Status:' || x_return_status);
529: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' UnExpError: Msg Count:' || fnd_msg_pub.count_msg);
530: END IF;
531:
524: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
525: ROLLBACK TO Create_SR_Unit_Effectivity_Pvt;
526: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
527: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
528: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' UnExpError: Return Status:' || x_return_status);
529: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' UnExpError: Msg Count:' || fnd_msg_pub.count_msg);
530: END IF;
531:
532: WHEN OTHERS THEN
525: ROLLBACK TO Create_SR_Unit_Effectivity_Pvt;
526: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
527: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
528: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' UnExpError: Return Status:' || x_return_status);
529: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' UnExpError: Msg Count:' || fnd_msg_pub.count_msg);
530: END IF;
531:
532: WHEN OTHERS THEN
533: ROLLBACK TO Create_SR_Unit_Effectivity_Pvt;
572: BEGIN
573:
574: SAVEPOINT Process_SR_Updates_Pvt;
575:
576: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
577: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
578: END IF;
579:
580: -- JR: Added the following log on 10/21/2003 to help in debugging
573:
574: SAVEPOINT Process_SR_Updates_Pvt;
575:
576: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
577: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
578: END IF;
579:
580: -- JR: Added the following log on 10/21/2003 to help in debugging
581: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
577: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
578: END IF;
579:
580: -- JR: Added the following log on 10/21/2003 to help in debugging
581: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
582: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'Relevant User Hook Record (input) Values: ' );
583: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'old_type_cmro_flag = ' || CS_SERVICEREQUEST_PVT.USER_HOOKS_REC.old_type_cmro_flag ||
584: ', new_type_cmro_flag = ' || CS_SERVICEREQUEST_PVT.USER_HOOKS_REC.new_type_cmro_flag ||
585: ', request_id = ' || CS_SERVICEREQUEST_PVT.user_hooks_rec.request_id ||
578: END IF;
579:
580: -- JR: Added the following log on 10/21/2003 to help in debugging
581: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
582: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'Relevant User Hook Record (input) Values: ' );
583: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'old_type_cmro_flag = ' || CS_SERVICEREQUEST_PVT.USER_HOOKS_REC.old_type_cmro_flag ||
584: ', new_type_cmro_flag = ' || CS_SERVICEREQUEST_PVT.USER_HOOKS_REC.new_type_cmro_flag ||
585: ', request_id = ' || CS_SERVICEREQUEST_PVT.user_hooks_rec.request_id ||
586: ', status_id = ' || CS_SERVICEREQUEST_PVT.user_hooks_rec.status_id ||
579:
580: -- JR: Added the following log on 10/21/2003 to help in debugging
581: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
582: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'Relevant User Hook Record (input) Values: ' );
583: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'old_type_cmro_flag = ' || CS_SERVICEREQUEST_PVT.USER_HOOKS_REC.old_type_cmro_flag ||
584: ', new_type_cmro_flag = ' || CS_SERVICEREQUEST_PVT.USER_HOOKS_REC.new_type_cmro_flag ||
585: ', request_id = ' || CS_SERVICEREQUEST_PVT.user_hooks_rec.request_id ||
586: ', status_id = ' || CS_SERVICEREQUEST_PVT.user_hooks_rec.status_id ||
587: ', status_flag = ' || CS_SERVICEREQUEST_PVT.user_hooks_rec.status_flag ||
598:
599: -- Begin Processing
600: IF NOT (NVL(CS_SERVICEREQUEST_PVT.USER_HOOKS_REC.old_type_cmro_flag, G_NO_FLAG) = G_YES_FLAG OR
601: NVL(CS_SERVICEREQUEST_PVT.USER_HOOKS_REC.new_type_cmro_flag, G_NO_FLAG)= G_YES_FLAG) THEN
602: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
603: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Not a CMRO Type Service Request.');
604: END IF;
605: -- Not a CMRO Type Service Request
606: -- Just return 'SUCCESS' without doing any processing.
599: -- Begin Processing
600: IF NOT (NVL(CS_SERVICEREQUEST_PVT.USER_HOOKS_REC.old_type_cmro_flag, G_NO_FLAG) = G_YES_FLAG OR
601: NVL(CS_SERVICEREQUEST_PVT.USER_HOOKS_REC.new_type_cmro_flag, G_NO_FLAG)= G_YES_FLAG) THEN
602: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
603: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Not a CMRO Type Service Request.');
604: END IF;
605: -- Not a CMRO Type Service Request
606: -- Just return 'SUCCESS' without doing any processing.
607: l_skip_processing := true;
605: -- Not a CMRO Type Service Request
606: -- Just return 'SUCCESS' without doing any processing.
607: l_skip_processing := true;
608: ELSE
609: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
610: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'From USER_HOOKS_REC: old_type_cmro_flag = ' || CS_SERVICEREQUEST_PVT.USER_HOOKS_REC.old_type_cmro_flag ||
611: ', new_type_cmro_flag = ' || CS_SERVICEREQUEST_PVT.USER_HOOKS_REC.new_type_cmro_flag);
612: END IF;
613: END IF;
606: -- Just return 'SUCCESS' without doing any processing.
607: l_skip_processing := true;
608: ELSE
609: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
610: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'From USER_HOOKS_REC: old_type_cmro_flag = ' || CS_SERVICEREQUEST_PVT.USER_HOOKS_REC.old_type_cmro_flag ||
611: ', new_type_cmro_flag = ' || CS_SERVICEREQUEST_PVT.USER_HOOKS_REC.new_type_cmro_flag);
612: END IF;
613: END IF;
614:
620:
621: IF(x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
622: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_APP_USAGE_NOT_SET');
623: FND_MSG_PUB.ADD;
624: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
625: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
626: END IF;
627: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
628: ELSIF(l_appln_code IS NULL OR (l_appln_code <> G_APP_MODULE)) THEN
621: IF(x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
622: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_APP_USAGE_NOT_SET');
623: FND_MSG_PUB.ADD;
624: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
625: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
626: END IF;
627: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
628: ELSIF(l_appln_code IS NULL OR (l_appln_code <> G_APP_MODULE)) THEN
629: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_APP_USAGE_INVALID');
627: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
628: ELSIF(l_appln_code IS NULL OR (l_appln_code <> G_APP_MODULE)) THEN
629: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_APP_USAGE_INVALID');
630: FND_MSG_PUB.ADD;
631: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
632: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
633: END IF;
634: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
635: ELSE
628: ELSIF(l_appln_code IS NULL OR (l_appln_code <> G_APP_MODULE)) THEN
629: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_APP_USAGE_INVALID');
630: FND_MSG_PUB.ADD;
631: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
632: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
633: END IF;
634: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
635: ELSE
636: IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
632: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
633: END IF;
634: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
635: ELSE
636: IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
637: FND_LOG.STRING(FND_LOG.LEVEL_EVENT, L_DEBUG_KEY, 'Application Usage Profile Option Validated');
638: END IF;
639: END IF; -- Appln_Usage Valid
640: END IF; -- new_type_cmro_flag = 'Y'
633: END IF;
634: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
635: ELSE
636: IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
637: FND_LOG.STRING(FND_LOG.LEVEL_EVENT, L_DEBUG_KEY, 'Application Usage Profile Option Validated');
638: END IF;
639: END IF; -- Appln_Usage Valid
640: END IF; -- new_type_cmro_flag = 'Y'
641:
652: Handle_Attribute_Changes(p_sr_ue_id => l_sr_ue_id);
653: END IF; -- Type change or other change
654: END IF; -- If l_skip_processing = false
655:
656: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
657: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
658: END IF;
659:
660: EXCEPTION
653: END IF; -- Type change or other change
654: END IF; -- If l_skip_processing = false
655:
656: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
657: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
658: END IF;
659:
660: EXCEPTION
661:
748: BEGIN
749: -- Standard start of API savepoint
750: SAVEPOINT Process_SR_MR_Associations_pvt;
751:
752: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
753: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
754: END IF;
755:
756: -- Standard call to check for call compatibility
749: -- Standard start of API savepoint
750: SAVEPOINT Process_SR_MR_Associations_pvt;
751:
752: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
753: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
754: END IF;
755:
756: -- Standard call to check for call compatibility
757: IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
766: -- Initialize API return status to success
767: x_return_status := FND_API.G_RET_STS_SUCCESS;
768:
769: -- Begin Processing
770: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
771: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Beginning Processing with p_request_id = ' || p_request_id ||
772: ', p_object_version_number = ' || p_object_version_number ||
773: ' and p_x_sr_mr_association_tbl.COUNT = ' || p_x_sr_mr_association_tbl.COUNT);
774: END IF;
767: x_return_status := FND_API.G_RET_STS_SUCCESS;
768:
769: -- Begin Processing
770: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
771: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Beginning Processing with p_request_id = ' || p_request_id ||
772: ', p_object_version_number = ' || p_object_version_number ||
773: ' and p_x_sr_mr_association_tbl.COUNT = ' || p_x_sr_mr_association_tbl.COUNT);
774: END IF;
775: -- Check if the Application Usage profile option has been set
778:
779: IF(x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
780: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_APP_USAGE_NOT_SET');
781: FND_MSG_PUB.ADD;
782: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
783: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
784: END IF;
785: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
786: ELSIF(l_appln_code IS NULL OR (l_appln_code <> G_APP_MODULE)) THEN
779: IF(x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
780: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_APP_USAGE_NOT_SET');
781: FND_MSG_PUB.ADD;
782: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
783: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
784: END IF;
785: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
786: ELSIF(l_appln_code IS NULL OR (l_appln_code <> G_APP_MODULE)) THEN
787: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_APP_USAGE_INVALID');
785: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
786: ELSIF(l_appln_code IS NULL OR (l_appln_code <> G_APP_MODULE)) THEN
787: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_APP_USAGE_INVALID');
788: FND_MSG_PUB.ADD;
789: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
790: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
791: END IF;
792: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
793: END IF;
786: ELSIF(l_appln_code IS NULL OR (l_appln_code <> G_APP_MODULE)) THEN
787: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_APP_USAGE_INVALID');
788: FND_MSG_PUB.ADD;
789: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
790: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
791: END IF;
792: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
793: END IF;
794: IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
790: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
791: END IF;
792: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
793: END IF;
794: IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
795: FND_LOG.STRING(FND_LOG.LEVEL_EVENT, L_DEBUG_KEY, 'Application Usage Profile Option Validated');
796: END IF;
797:
798: -- Validate the Request
791: END IF;
792: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
793: END IF;
794: IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
795: FND_LOG.STRING(FND_LOG.LEVEL_EVENT, L_DEBUG_KEY, 'Application Usage Profile Option Validated');
796: END IF;
797:
798: -- Validate the Request
799: Validate_Associated_Request(p_x_request_id => l_request_id,
802: x_sr_ue_id => l_sr_ue_id,
803: x_sr_instance_id => l_sr_instance_id,
804: x_sr_exp_resol_date => l_sr_exp_resol_date);
805:
806: IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
807: FND_LOG.STRING(FND_LOG.LEVEL_EVENT, L_DEBUG_KEY, 'Completed Validating the Request');
808: END IF;
809:
810: IF (p_x_sr_mr_association_tbl.COUNT = 0) THEN
803: x_sr_instance_id => l_sr_instance_id,
804: x_sr_exp_resol_date => l_sr_exp_resol_date);
805:
806: IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
807: FND_LOG.STRING(FND_LOG.LEVEL_EVENT, L_DEBUG_KEY, 'Completed Validating the Request');
808: END IF;
809:
810: IF (p_x_sr_mr_association_tbl.COUNT = 0) THEN
811: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_MR_SR_TBL_EMPTY');
809:
810: IF (p_x_sr_mr_association_tbl.COUNT = 0) THEN
811: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_MR_SR_TBL_EMPTY');
812: FND_MSG_PUB.ADD;
813: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
814: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
815: END IF;
816: RAISE FND_API.G_EXC_ERROR;
817: END IF;
810: IF (p_x_sr_mr_association_tbl.COUNT = 0) THEN
811: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_MR_SR_TBL_EMPTY');
812: FND_MSG_PUB.ADD;
813: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
814: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
815: END IF;
816: RAISE FND_API.G_EXC_ERROR;
817: END IF;
818:
846: END IF;
847:
848: -- Standard check of p_commit
849: IF FND_API.TO_BOOLEAN(p_commit) THEN
850: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
851: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'About to Commit.');
852: END IF;
853: COMMIT WORK;
854: END IF;
847:
848: -- Standard check of p_commit
849: IF FND_API.TO_BOOLEAN(p_commit) THEN
850: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
851: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'About to Commit.');
852: END IF;
853: COMMIT WORK;
854: END IF;
855:
859: p_data => x_msg_data,
860: p_encoded => fnd_api.g_false
861: );
862:
863: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
864: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
865: END IF;
866:
867: EXCEPTION
860: p_encoded => fnd_api.g_false
861: );
862:
863: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
864: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
865: END IF;
866:
867: EXCEPTION
868: WHEN FND_API.G_EXC_ERROR THEN
948:
949: L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Validate_Associated_Request';
950:
951: BEGIN
952: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
953: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
954: END IF;
955:
956: IF(p_x_request_id IS NULL AND p_request_number IS NULL) THEN
949: L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Validate_Associated_Request';
950:
951: BEGIN
952: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
953: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
954: END IF;
955:
956: IF(p_x_request_id IS NULL AND p_request_number IS NULL) THEN
957: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_REQ_ID_NUM_NULL');
955:
956: IF(p_x_request_id IS NULL AND p_request_number IS NULL) THEN
957: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_REQ_ID_NUM_NULL');
958: FND_MSG_PUB.ADD;
959: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
960: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
961: END IF;
962: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
963: ELSIF (p_object_version_number IS NULL) THEN
956: IF(p_x_request_id IS NULL AND p_request_number IS NULL) THEN
957: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_REQ_ID_NUM_NULL');
958: FND_MSG_PUB.ADD;
959: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
960: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
961: END IF;
962: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
963: ELSIF (p_object_version_number IS NULL) THEN
964: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_REQ_OVN_NULL');
962: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
963: ELSIF (p_object_version_number IS NULL) THEN
964: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_REQ_OVN_NULL');
965: FND_MSG_PUB.ADD;
966: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
967: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
968: END IF;
969: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
970: ELSE
963: ELSIF (p_object_version_number IS NULL) THEN
964: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_REQ_OVN_NULL');
965: FND_MSG_PUB.ADD;
966: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
967: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
968: END IF;
969: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
970: ELSE
971: OPEN get_request_dtls_csr(p_request_id => p_x_request_id,
980: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_REQ_OVN_INVALID');
981: FND_MESSAGE.Set_Token('ID', p_x_request_id);
982: FND_MESSAGE.Set_Token('OVN', p_object_version_number);
983: FND_MSG_PUB.ADD;
984: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
985: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
986: END IF;
987: CLOSE get_request_dtls_csr;
988: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
981: FND_MESSAGE.Set_Token('ID', p_x_request_id);
982: FND_MESSAGE.Set_Token('OVN', p_object_version_number);
983: FND_MSG_PUB.ADD;
984: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
985: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
986: END IF;
987: CLOSE get_request_dtls_csr;
988: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
989: END IF;
992:
993: IF(l_closed_flag = G_YES_FLAG) THEN
994: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_REQ_CLOSED');
995: FND_MSG_PUB.ADD;
996: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
997: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
998: END IF;
999: RAISE FND_API.G_EXC_ERROR;
1000: END IF;
993: IF(l_closed_flag = G_YES_FLAG) THEN
994: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_REQ_CLOSED');
995: FND_MSG_PUB.ADD;
996: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
997: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
998: END IF;
999: RAISE FND_API.G_EXC_ERROR;
1000: END IF;
1001: /*
1007: -- JR: Modified on 10/29/2003 (Using Status Id instead of Status Code)
1008: IF (l_sr_status_id = G_SR_PLANNED_STATUS_ID) THEN
1009: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_REQ_PLANNED');
1010: FND_MSG_PUB.ADD;
1011: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1012: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1013: END IF;
1014: RAISE FND_API.G_EXC_ERROR;
1015: END IF;
1008: IF (l_sr_status_id = G_SR_PLANNED_STATUS_ID) THEN
1009: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_REQ_PLANNED');
1010: FND_MSG_PUB.ADD;
1011: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1012: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1013: END IF;
1014: RAISE FND_API.G_EXC_ERROR;
1015: END IF;
1016: */
1019: IF (get_ue_dtls_csr%NOTFOUND) THEN
1020: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_NO_UE_FOR_REQ');
1021: FND_MESSAGE.Set_Token('ID', p_x_request_id);
1022: FND_MSG_PUB.ADD;
1023: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1024: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1025: END IF;
1026: CLOSE get_ue_dtls_csr;
1027: RAISE FND_API.G_EXC_ERROR;
1020: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_NO_UE_FOR_REQ');
1021: FND_MESSAGE.Set_Token('ID', p_x_request_id);
1022: FND_MSG_PUB.ADD;
1023: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1024: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1025: END IF;
1026: CLOSE get_ue_dtls_csr;
1027: RAISE FND_API.G_EXC_ERROR;
1028: END IF;
1037: FETCH get_tasks_for_ue_csr INTO l_dummy;
1038: IF (get_tasks_for_ue_csr%FOUND) THEN
1039: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_ASGND_TO_VISIT');
1040: FND_MSG_PUB.ADD;
1041: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1042: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1043: END IF;
1044: CLOSE get_tasks_for_ue_csr;
1045: RAISE FND_API.G_EXC_ERROR;
1038: IF (get_tasks_for_ue_csr%FOUND) THEN
1039: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_ASGND_TO_VISIT');
1040: FND_MSG_PUB.ADD;
1041: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1042: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1043: END IF;
1044: CLOSE get_tasks_for_ue_csr;
1045: RAISE FND_API.G_EXC_ERROR;
1046: END IF;
1045: RAISE FND_API.G_EXC_ERROR;
1046: END IF;
1047: CLOSE get_tasks_for_ue_csr;
1048: */
1049: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1050: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
1051: END IF;
1052: END Validate_Associated_Request;
1053:
1046: END IF;
1047: CLOSE get_tasks_for_ue_csr;
1048: */
1049: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1050: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
1051: END IF;
1052: END Validate_Associated_Request;
1053:
1054: ----------------------------------------
1117:
1118: L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Validate_Association_Records';
1119:
1120: BEGIN
1121: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1122: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
1123: END IF;
1124: FOR i in p_x_sr_mr_association_tbl.FIRST .. p_x_sr_mr_association_tbl.LAST LOOP
1125: l_valid := true;
1118: L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Validate_Association_Records';
1119:
1120: BEGIN
1121: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1122: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
1123: END IF;
1124: FOR i in p_x_sr_mr_association_tbl.FIRST .. p_x_sr_mr_association_tbl.LAST LOOP
1125: l_valid := true;
1126: IF (p_x_sr_mr_association_tbl(i).OPERATION_FLAG NOT IN (G_OPR_CREATE, G_OPR_DELETE)) THEN
1127: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_OPR_INVALID');
1128: FND_MESSAGE.Set_Token('OPR_FLAG', p_x_sr_mr_association_tbl(i).OPERATION_FLAG);
1129: FND_MESSAGE.Set_Token('INDEX', i);
1130: FND_MSG_PUB.ADD;
1131: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1132: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1133: END IF;
1134: l_valid := false;
1135: ELSIF (p_x_sr_mr_association_tbl(i).OPERATION_FLAG = G_OPR_DELETE) THEN
1128: FND_MESSAGE.Set_Token('OPR_FLAG', p_x_sr_mr_association_tbl(i).OPERATION_FLAG);
1129: FND_MESSAGE.Set_Token('INDEX', i);
1130: FND_MSG_PUB.ADD;
1131: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1132: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1133: END IF;
1134: l_valid := false;
1135: ELSIF (p_x_sr_mr_association_tbl(i).OPERATION_FLAG = G_OPR_DELETE) THEN
1136: -- Delete Operation
1139: IF (p_x_sr_mr_association_tbl(i).UNIT_EFFECTIVITY_ID IS NULL) THEN
1140: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_UE_ID_NULL');
1141: FND_MESSAGE.Set_Token('INDEX', i);
1142: FND_MSG_PUB.ADD;
1143: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1144: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1145: END IF;
1146: l_valid := false;
1147: ELSIF (p_x_sr_mr_association_tbl(i).OBJECT_VERSION_NUMBER IS NULL) THEN
1140: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_UE_ID_NULL');
1141: FND_MESSAGE.Set_Token('INDEX', i);
1142: FND_MSG_PUB.ADD;
1143: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1144: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1145: END IF;
1146: l_valid := false;
1147: ELSIF (p_x_sr_mr_association_tbl(i).OBJECT_VERSION_NUMBER IS NULL) THEN
1148: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_UE_OVN_NULL');
1147: ELSIF (p_x_sr_mr_association_tbl(i).OBJECT_VERSION_NUMBER IS NULL) THEN
1148: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_UE_OVN_NULL');
1149: FND_MESSAGE.Set_Token('INDEX', i);
1150: FND_MSG_PUB.ADD;
1151: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1152: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1153: END IF;
1154: l_valid := false;
1155: ELSE
1148: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_UE_OVN_NULL');
1149: FND_MESSAGE.Set_Token('INDEX', i);
1150: FND_MSG_PUB.ADD;
1151: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1152: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1153: END IF;
1154: l_valid := false;
1155: ELSE
1156: OPEN validate_mr_ue_csr(p_mr_ue_id => p_x_sr_mr_association_tbl(i).UNIT_EFFECTIVITY_ID,
1162: FND_MESSAGE.Set_Token('UE_ID', p_x_sr_mr_association_tbl(i).UNIT_EFFECTIVITY_ID);
1163: FND_MESSAGE.Set_Token('OVN', p_x_sr_mr_association_tbl(i).OBJECT_VERSION_NUMBER);
1164: FND_MESSAGE.Set_Token('INDEX', i);
1165: FND_MSG_PUB.ADD;
1166: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1167: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1168: END IF;
1169: l_valid := false;
1170: END IF; -- UE Id, OVN Not valid
1163: FND_MESSAGE.Set_Token('OVN', p_x_sr_mr_association_tbl(i).OBJECT_VERSION_NUMBER);
1164: FND_MESSAGE.Set_Token('INDEX', i);
1165: FND_MSG_PUB.ADD;
1166: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1167: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1168: END IF;
1169: l_valid := false;
1170: END IF; -- UE Id, OVN Not valid
1171: CLOSE validate_mr_ue_csr;
1169: l_valid := false;
1170: END IF; -- UE Id, OVN Not valid
1171: CLOSE validate_mr_ue_csr;
1172: END IF; -- UE Id, OVN Not null
1173: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1174: IF (l_valid) THEN
1175: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Record ' || i || ' for Delete operation is Valid');
1176: ELSE
1177: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Record ' || i || ' for Delete operation is Not valid');
1171: CLOSE validate_mr_ue_csr;
1172: END IF; -- UE Id, OVN Not null
1173: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1174: IF (l_valid) THEN
1175: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Record ' || i || ' for Delete operation is Valid');
1176: ELSE
1177: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Record ' || i || ' for Delete operation is Not valid');
1178: END IF;
1179: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Record Details: UE ID: ' || p_x_sr_mr_association_tbl(i).UNIT_EFFECTIVITY_ID ||
1173: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1174: IF (l_valid) THEN
1175: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Record ' || i || ' for Delete operation is Valid');
1176: ELSE
1177: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Record ' || i || ' for Delete operation is Not valid');
1178: END IF;
1179: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Record Details: UE ID: ' || p_x_sr_mr_association_tbl(i).UNIT_EFFECTIVITY_ID ||
1180: ', UE OVN: ' || p_x_sr_mr_association_tbl(i).OBJECT_VERSION_NUMBER);
1181: END IF; -- Log Statement
1175: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Record ' || i || ' for Delete operation is Valid');
1176: ELSE
1177: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Record ' || i || ' for Delete operation is Not valid');
1178: END IF;
1179: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Record Details: UE ID: ' || p_x_sr_mr_association_tbl(i).UNIT_EFFECTIVITY_ID ||
1180: ', UE OVN: ' || p_x_sr_mr_association_tbl(i).OBJECT_VERSION_NUMBER);
1181: END IF; -- Log Statement
1182: ELSE
1183: -- Create Operation
1187: FND_MESSAGE.Set_Token('TITLE', p_x_sr_mr_association_tbl(i).MR_TITLE);
1188: FND_MESSAGE.Set_Token('VERSION', p_x_sr_mr_association_tbl(i).MR_VERSION);
1189: FND_MESSAGE.Set_Token('INDEX', i);
1190: FND_MSG_PUB.ADD;
1191: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1192: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1193: END IF;
1194: l_valid := false;
1195: ELSIF (p_x_sr_mr_association_tbl(i).CSI_INSTANCE_ID IS NULL) THEN
1188: FND_MESSAGE.Set_Token('VERSION', p_x_sr_mr_association_tbl(i).MR_VERSION);
1189: FND_MESSAGE.Set_Token('INDEX', i);
1190: FND_MSG_PUB.ADD;
1191: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1192: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1193: END IF;
1194: l_valid := false;
1195: ELSIF (p_x_sr_mr_association_tbl(i).CSI_INSTANCE_ID IS NULL) THEN
1196: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_INST_NUM_INVALID');
1196: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_INST_NUM_INVALID');
1197: FND_MESSAGE.Set_Token('INST_NUM', p_x_sr_mr_association_tbl(i).CSI_INSTANCE_NUMBER);
1198: FND_MESSAGE.Set_Token('INDEX', i);
1199: FND_MSG_PUB.ADD;
1200: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1201: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1202: END IF;
1203: l_valid := false;
1204: ELSE
1197: FND_MESSAGE.Set_Token('INST_NUM', p_x_sr_mr_association_tbl(i).CSI_INSTANCE_NUMBER);
1198: FND_MESSAGE.Set_Token('INDEX', i);
1199: FND_MSG_PUB.ADD;
1200: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1201: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1202: END IF;
1203: l_valid := false;
1204: ELSE
1205: -- Validate the MR Header Id
1210: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_MR_ID_INVALID');
1211: FND_MESSAGE.Set_Token('MR_ID', p_x_sr_mr_association_tbl(i).MR_HEADER_ID);
1212: FND_MESSAGE.Set_Token('INDEX', i);
1213: FND_MSG_PUB.ADD;
1214: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1215: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1216: END IF;
1217: l_valid := false;
1218: ELSE
1211: FND_MESSAGE.Set_Token('MR_ID', p_x_sr_mr_association_tbl(i).MR_HEADER_ID);
1212: FND_MESSAGE.Set_Token('INDEX', i);
1213: FND_MSG_PUB.ADD;
1214: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1215: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1216: END IF;
1217: l_valid := false;
1218: ELSE
1219: CLOSE validate_mr_id_csr;
1225: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_INST_ID_INVALID');
1226: FND_MESSAGE.Set_Token('INST_ID', p_x_sr_mr_association_tbl(i).CSI_INSTANCE_ID);
1227: FND_MESSAGE.Set_Token('INDEX', i);
1228: FND_MSG_PUB.ADD;
1229: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1230: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1231: END IF;
1232: l_valid := false;
1233: ELSE
1226: FND_MESSAGE.Set_Token('INST_ID', p_x_sr_mr_association_tbl(i).CSI_INSTANCE_ID);
1227: FND_MESSAGE.Set_Token('INDEX', i);
1228: FND_MSG_PUB.ADD;
1229: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1230: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1231: END IF;
1232: l_valid := false;
1233: ELSE
1234: CLOSE validate_instance_id_csr;
1242: FND_MESSAGE.Set_Token('TITLE', p_x_sr_mr_association_tbl(i).MR_TITLE);
1243: FND_MESSAGE.Set_Token('VERSION', p_x_sr_mr_association_tbl(i).MR_VERSION);
1244: FND_MESSAGE.Set_Token('INDEX', i);
1245: FND_MSG_PUB.ADD;
1246: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1247: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1248: END IF;
1249: l_valid := false;
1250: ELSE
1243: FND_MESSAGE.Set_Token('VERSION', p_x_sr_mr_association_tbl(i).MR_VERSION);
1244: FND_MESSAGE.Set_Token('INDEX', i);
1245: FND_MSG_PUB.ADD;
1246: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1247: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1248: END IF;
1249: l_valid := false;
1250: ELSE
1251: CLOSE validate_sr_mr_intance_rel;
1262: IF (l_temp_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1263: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_MR_DTLS_FAILED');
1264: FND_MESSAGE.Set_Token('INDEX', i);
1265: FND_MSG_PUB.ADD;
1266: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1267: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1268: END IF;
1269: l_valid := false;
1270: ELSE
1263: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_MR_DTLS_FAILED');
1264: FND_MESSAGE.Set_Token('INDEX', i);
1265: FND_MSG_PUB.ADD;
1266: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1267: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1268: END IF;
1269: l_valid := false;
1270: ELSE
1271: l_mr_applicable := false;
1281: IF (l_mr_applicable = false) THEN
1282: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_MR_NOT_APPLCBL');
1283: FND_MESSAGE.Set_Token('MR_TITLE', Get_MR_Title_From_MR_Id(p_x_sr_mr_association_tbl(i).MR_HEADER_ID));
1284: FND_MSG_PUB.ADD;
1285: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1286: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1287: END IF;
1288: l_valid := false;
1289: END IF; -- MR applicable
1282: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_MR_NOT_APPLCBL');
1283: FND_MESSAGE.Set_Token('MR_TITLE', Get_MR_Title_From_MR_Id(p_x_sr_mr_association_tbl(i).MR_HEADER_ID));
1284: FND_MSG_PUB.ADD;
1285: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1286: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1287: END IF;
1288: l_valid := false;
1289: END IF; -- MR applicable
1290: END IF; -- GET_APPLICABLE_MRS successful
1292: end if; -- program_type_code
1293: END IF; -- Instance Id Valid
1294: END IF; -- MR Header Id Valid
1295: END IF; -- MR Id and Instance Id Not null
1296: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1297: IF (l_valid) THEN
1298: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Record ' || i || ' for Create operation is Valid');
1299: ELSE
1300: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Record ' || i || ' for Create operation is Not valid');
1294: END IF; -- MR Header Id Valid
1295: END IF; -- MR Id and Instance Id Not null
1296: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1297: IF (l_valid) THEN
1298: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Record ' || i || ' for Create operation is Valid');
1299: ELSE
1300: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Record ' || i || ' for Create operation is Not valid');
1301: END IF;
1302: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Record Details: MR Header ID: ' || p_x_sr_mr_association_tbl(i).MR_HEADER_ID ||
1296: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1297: IF (l_valid) THEN
1298: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Record ' || i || ' for Create operation is Valid');
1299: ELSE
1300: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Record ' || i || ' for Create operation is Not valid');
1301: END IF;
1302: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Record Details: MR Header ID: ' || p_x_sr_mr_association_tbl(i).MR_HEADER_ID ||
1303: ', CSI Instance ID: ' || p_x_sr_mr_association_tbl(i).CSI_INSTANCE_ID);
1304: END IF; -- Log Statement
1298: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Record ' || i || ' for Create operation is Valid');
1299: ELSE
1300: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Record ' || i || ' for Create operation is Not valid');
1301: END IF;
1302: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Record Details: MR Header ID: ' || p_x_sr_mr_association_tbl(i).MR_HEADER_ID ||
1303: ', CSI Instance ID: ' || p_x_sr_mr_association_tbl(i).CSI_INSTANCE_ID);
1304: END IF; -- Log Statement
1305: END IF; -- Valid Operation flag
1306: END LOOP; -- All Association/Disassociation Records
1304: END IF; -- Log Statement
1305: END IF; -- Valid Operation flag
1306: END LOOP; -- All Association/Disassociation Records
1307:
1308: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1309: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
1310: END IF;
1311: END Validate_Association_Records;
1312:
1305: END IF; -- Valid Operation flag
1306: END LOOP; -- All Association/Disassociation Records
1307:
1308: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1309: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
1310: END IF;
1311: END Validate_Association_Records;
1312:
1313: ----------------------------------------
1339:
1340: L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Process_Disassociations';
1341:
1342: BEGIN
1343: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1344: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
1345: END IF;
1346:
1347: FOR i in p_sr_mr_association_tbl.FIRST .. p_sr_mr_association_tbl.LAST LOOP
1340: L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Process_Disassociations';
1341:
1342: BEGIN
1343: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1344: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
1345: END IF;
1346:
1347: FOR i in p_sr_mr_association_tbl.FIRST .. p_sr_mr_association_tbl.LAST LOOP
1348: IF (p_sr_mr_association_tbl(i).OPERATION_FLAG = G_OPR_DELETE) THEN
1345: END IF;
1346:
1347: FOR i in p_sr_mr_association_tbl.FIRST .. p_sr_mr_association_tbl.LAST LOOP
1348: IF (p_sr_mr_association_tbl(i).OPERATION_FLAG = G_OPR_DELETE) THEN
1349: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1350: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'About to Disassociate Record with index ' || i);
1351: END IF;
1352: OPEN get_relationship_id_csr(p_sr_ue_id => p_sr_ue_id,
1353: p_mr_ue_id => p_sr_mr_association_tbl(i).UNIT_EFFECTIVITY_ID);
1346:
1347: FOR i in p_sr_mr_association_tbl.FIRST .. p_sr_mr_association_tbl.LAST LOOP
1348: IF (p_sr_mr_association_tbl(i).OPERATION_FLAG = G_OPR_DELETE) THEN
1349: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1350: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'About to Disassociate Record with index ' || i);
1351: END IF;
1352: OPEN get_relationship_id_csr(p_sr_ue_id => p_sr_ue_id,
1353: p_mr_ue_id => p_sr_mr_association_tbl(i).UNIT_EFFECTIVITY_ID);
1354: FETCH get_relationship_id_csr INTO l_relationship_id;
1355: CLOSE get_relationship_id_csr;
1356:
1357: -- Delete the Dependents (If Group MR)
1358: FOR l_rel_dtls_rec IN get_rel_dtls_csr(p_sr_mr_association_tbl(i).UNIT_EFFECTIVITY_ID) LOOP
1359: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1360: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'About to Delete Relationship with RELATIONSHIP_ID = ' || l_rel_dtls_rec.UE_RELATIONSHIP_ID || ' for a Group MR');
1361: END IF;
1362: AHL_UE_RELATIONSHIPS_PKG.DELETE_ROW(X_UE_RELATIONSHIP_ID => l_rel_dtls_rec.UE_RELATIONSHIP_ID);
1363:
1356:
1357: -- Delete the Dependents (If Group MR)
1358: FOR l_rel_dtls_rec IN get_rel_dtls_csr(p_sr_mr_association_tbl(i).UNIT_EFFECTIVITY_ID) LOOP
1359: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1360: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'About to Delete Relationship with RELATIONSHIP_ID = ' || l_rel_dtls_rec.UE_RELATIONSHIP_ID || ' for a Group MR');
1361: END IF;
1362: AHL_UE_RELATIONSHIPS_PKG.DELETE_ROW(X_UE_RELATIONSHIP_ID => l_rel_dtls_rec.UE_RELATIONSHIP_ID);
1363:
1364: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1360: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'About to Delete Relationship with RELATIONSHIP_ID = ' || l_rel_dtls_rec.UE_RELATIONSHIP_ID || ' for a Group MR');
1361: END IF;
1362: AHL_UE_RELATIONSHIPS_PKG.DELETE_ROW(X_UE_RELATIONSHIP_ID => l_rel_dtls_rec.UE_RELATIONSHIP_ID);
1363:
1364: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1365: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'About to Delete Unit Effectivity with UNIT_EFFECTIVITY_ID = ' || l_rel_dtls_rec.RELATED_UE_ID || ' for a Group MR');
1366: END IF;
1367: AHL_UNIT_EFFECTIVITIES_PKG.DELETE_ROW(X_UNIT_EFFECTIVITY_ID => l_rel_dtls_rec.RELATED_UE_ID);
1368: END LOOP;
1361: END IF;
1362: AHL_UE_RELATIONSHIPS_PKG.DELETE_ROW(X_UE_RELATIONSHIP_ID => l_rel_dtls_rec.UE_RELATIONSHIP_ID);
1363:
1364: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1365: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'About to Delete Unit Effectivity with UNIT_EFFECTIVITY_ID = ' || l_rel_dtls_rec.RELATED_UE_ID || ' for a Group MR');
1366: END IF;
1367: AHL_UNIT_EFFECTIVITIES_PKG.DELETE_ROW(X_UNIT_EFFECTIVITY_ID => l_rel_dtls_rec.RELATED_UE_ID);
1368: END LOOP;
1369: -- Delete the MR Relationship and UE
1366: END IF;
1367: AHL_UNIT_EFFECTIVITIES_PKG.DELETE_ROW(X_UNIT_EFFECTIVITY_ID => l_rel_dtls_rec.RELATED_UE_ID);
1368: END LOOP;
1369: -- Delete the MR Relationship and UE
1370: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1371: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'About to Delete Relationship with RELATIONSHIP_ID = ' || l_relationship_id);
1372: END IF;
1373: AHL_UE_RELATIONSHIPS_PKG.DELETE_ROW(X_UE_RELATIONSHIP_ID => l_relationship_id);
1374:
1367: AHL_UNIT_EFFECTIVITIES_PKG.DELETE_ROW(X_UNIT_EFFECTIVITY_ID => l_rel_dtls_rec.RELATED_UE_ID);
1368: END LOOP;
1369: -- Delete the MR Relationship and UE
1370: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1371: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'About to Delete Relationship with RELATIONSHIP_ID = ' || l_relationship_id);
1372: END IF;
1373: AHL_UE_RELATIONSHIPS_PKG.DELETE_ROW(X_UE_RELATIONSHIP_ID => l_relationship_id);
1374:
1375: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1371: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'About to Delete Relationship with RELATIONSHIP_ID = ' || l_relationship_id);
1372: END IF;
1373: AHL_UE_RELATIONSHIPS_PKG.DELETE_ROW(X_UE_RELATIONSHIP_ID => l_relationship_id);
1374:
1375: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1376: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'About to Delete Unit Effectivity with UNIT_EFFECTIVITY_ID = ' || p_sr_mr_association_tbl(i).UNIT_EFFECTIVITY_ID);
1377: END IF;
1378: AHL_UNIT_EFFECTIVITIES_PKG.DELETE_ROW(X_UNIT_EFFECTIVITY_ID => p_sr_mr_association_tbl(i).UNIT_EFFECTIVITY_ID);
1379:
1372: END IF;
1373: AHL_UE_RELATIONSHIPS_PKG.DELETE_ROW(X_UE_RELATIONSHIP_ID => l_relationship_id);
1374:
1375: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1376: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'About to Delete Unit Effectivity with UNIT_EFFECTIVITY_ID = ' || p_sr_mr_association_tbl(i).UNIT_EFFECTIVITY_ID);
1377: END IF;
1378: AHL_UNIT_EFFECTIVITIES_PKG.DELETE_ROW(X_UNIT_EFFECTIVITY_ID => p_sr_mr_association_tbl(i).UNIT_EFFECTIVITY_ID);
1379:
1380: END IF; -- Disassociation Record
1379:
1380: END IF; -- Disassociation Record
1381: END LOOP; -- All Association/Disassociation Records
1382:
1383: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1384: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
1385: END IF;
1386: END Process_Disassociations;
1387:
1380: END IF; -- Disassociation Record
1381: END LOOP; -- All Association/Disassociation Records
1382:
1383: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1384: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
1385: END IF;
1386: END Process_Disassociations;
1387:
1388: ----------------------------------------
1435:
1436: L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Process_New_Associations';
1437:
1438: BEGIN
1439: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1440: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
1441: END IF;
1442:
1443: FOR i in p_x_sr_mr_association_tbl.FIRST .. p_x_sr_mr_association_tbl.LAST LOOP
1436: L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Process_New_Associations';
1437:
1438: BEGIN
1439: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1440: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
1441: END IF;
1442:
1443: FOR i in p_x_sr_mr_association_tbl.FIRST .. p_x_sr_mr_association_tbl.LAST LOOP
1444: IF (p_x_sr_mr_association_tbl(i).OPERATION_FLAG = G_OPR_CREATE) THEN
1441: END IF;
1442:
1443: FOR i in p_x_sr_mr_association_tbl.FIRST .. p_x_sr_mr_association_tbl.LAST LOOP
1444: IF (p_x_sr_mr_association_tbl(i).OPERATION_FLAG = G_OPR_CREATE) THEN
1445: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1446: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Record ' || i || ' is a Create Record. Checking for Duplicates first.');
1447: END IF;
1448: -- Check for duplicates
1449: OPEN get_dup_mrs_csr(p_mr_id => p_x_sr_mr_association_tbl(i).MR_HEADER_ID,
1442:
1443: FOR i in p_x_sr_mr_association_tbl.FIRST .. p_x_sr_mr_association_tbl.LAST LOOP
1444: IF (p_x_sr_mr_association_tbl(i).OPERATION_FLAG = G_OPR_CREATE) THEN
1445: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1446: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Record ' || i || ' is a Create Record. Checking for Duplicates first.');
1447: END IF;
1448: -- Check for duplicates
1449: OPEN get_dup_mrs_csr(p_mr_id => p_x_sr_mr_association_tbl(i).MR_HEADER_ID,
1450: p_instance_id => p_x_sr_mr_association_tbl(i).CSI_INSTANCE_ID,
1452: FETCH get_dup_mrs_csr INTO l_dummy;
1453: IF (get_dup_mrs_csr%FOUND) THEN
1454: l_valid_flag := false;
1455: CLOSE get_dup_mrs_csr;
1456: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1457: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Record ' || i || ' with MR Id ' ||
1458: p_x_sr_mr_association_tbl(i).MR_HEADER_ID || ' and Instance Id ' ||
1459: p_x_sr_mr_association_tbl(i).CSI_INSTANCE_ID || ' is a Duplicate.');
1460: END IF;
1453: IF (get_dup_mrs_csr%FOUND) THEN
1454: l_valid_flag := false;
1455: CLOSE get_dup_mrs_csr;
1456: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1457: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Record ' || i || ' with MR Id ' ||
1458: p_x_sr_mr_association_tbl(i).MR_HEADER_ID || ' and Instance Id ' ||
1459: p_x_sr_mr_association_tbl(i).CSI_INSTANCE_ID || ' is a Duplicate.');
1460: END IF;
1461: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_DUPLICATE_MR');
1460: END IF;
1461: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_DUPLICATE_MR');
1462: FND_MESSAGE.Set_Token('MR_TITLE', Get_MR_Title_From_MR_Id(p_x_sr_mr_association_tbl(i).MR_HEADER_ID));
1463: FND_MSG_PUB.ADD;
1464: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1465: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1466: END IF;
1467: ELSE
1468: CLOSE get_dup_mrs_csr;
1461: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_DUPLICATE_MR');
1462: FND_MESSAGE.Set_Token('MR_TITLE', Get_MR_Title_From_MR_Id(p_x_sr_mr_association_tbl(i).MR_HEADER_ID));
1463: FND_MSG_PUB.ADD;
1464: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1465: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1466: END IF;
1467: ELSE
1468: CLOSE get_dup_mrs_csr;
1469: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1465: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1466: END IF;
1467: ELSE
1468: CLOSE get_dup_mrs_csr;
1469: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1470: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Record ' || i || ' is not a Duplicate. About to create MR UE and UE Relationship.');
1471: END IF;
1472:
1473: Create_MR_Unit_Effectivity(p_instance_id => p_x_sr_mr_association_tbl(i).CSI_INSTANCE_ID,
1466: END IF;
1467: ELSE
1468: CLOSE get_dup_mrs_csr;
1469: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1470: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Record ' || i || ' is not a Duplicate. About to create MR UE and UE Relationship.');
1471: END IF;
1472:
1473: Create_MR_Unit_Effectivity(p_instance_id => p_x_sr_mr_association_tbl(i).CSI_INSTANCE_ID,
1474: p_due_date => p_sr_exp_resol_date,
1506:
1507: END IF;
1508: END IF; -- Duplicate MR Check
1509: ELSE
1510: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1511: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Record ' || i || ' is not a Create Record. Skipping.');
1512: END IF;
1513: END IF; -- New Association Record
1514: END LOOP; -- All Association/Disassociation Records
1507: END IF;
1508: END IF; -- Duplicate MR Check
1509: ELSE
1510: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1511: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Record ' || i || ' is not a Create Record. Skipping.');
1512: END IF;
1513: END IF; -- New Association Record
1514: END LOOP; -- All Association/Disassociation Records
1515:
1521: OPEN c_get_origin_wo_id(p_sr_ue_id);
1522: FETCH c_get_origin_wo_id INTO l_origin_wo_id;
1523: CLOSE c_get_origin_wo_id;
1524:
1525: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1526: FND_LOG.STRING(
1527: FND_LOG.LEVEL_STATEMENT,
1528: L_DEBUG_KEY,
1529: 'Before updating Originating Work Order Id');
1522: FETCH c_get_origin_wo_id INTO l_origin_wo_id;
1523: CLOSE c_get_origin_wo_id;
1524:
1525: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1526: FND_LOG.STRING(
1527: FND_LOG.LEVEL_STATEMENT,
1528: L_DEBUG_KEY,
1529: 'Before updating Originating Work Order Id');
1530: FND_LOG.STRING(
1523: CLOSE c_get_origin_wo_id;
1524:
1525: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1526: FND_LOG.STRING(
1527: FND_LOG.LEVEL_STATEMENT,
1528: L_DEBUG_KEY,
1529: 'Before updating Originating Work Order Id');
1530: FND_LOG.STRING(
1531: FND_LOG.LEVEL_STATEMENT,
1526: FND_LOG.STRING(
1527: FND_LOG.LEVEL_STATEMENT,
1528: L_DEBUG_KEY,
1529: 'Before updating Originating Work Order Id');
1530: FND_LOG.STRING(
1531: FND_LOG.LEVEL_STATEMENT,
1532: L_DEBUG_KEY,
1533: 'p_sr_ue_id->'||p_sr_ue_id||' , '||'SR origin_wo_id->'||l_origin_wo_id);
1534: END IF;
1527: FND_LOG.LEVEL_STATEMENT,
1528: L_DEBUG_KEY,
1529: 'Before updating Originating Work Order Id');
1530: FND_LOG.STRING(
1531: FND_LOG.LEVEL_STATEMENT,
1532: L_DEBUG_KEY,
1533: 'p_sr_ue_id->'||p_sr_ue_id||' , '||'SR origin_wo_id->'||l_origin_wo_id);
1534: END IF;
1535:
1559: END;
1560: END IF;
1561: -- End Changes ER # 6123671
1562:
1563: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1564: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
1565: END IF;
1566: END Process_New_Associations;
1567:
1560: END IF;
1561: -- End Changes ER # 6123671
1562:
1563: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1564: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
1565: END IF;
1566: END Process_New_Associations;
1567:
1568: ----------------------------------------
1622: L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Get_MR_UnitEffectivity';
1623: l_get_ue_flag BOOLEAN := true;
1624:
1625: BEGIN
1626: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1627: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
1628: END IF;
1629:
1630: IF(p_x_sr_mr_association_rec.UNIT_EFFECTIVITY_ID IS NOT NULL) THEN
1623: l_get_ue_flag BOOLEAN := true;
1624:
1625: BEGIN
1626: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1627: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
1628: END IF;
1629:
1630: IF(p_x_sr_mr_association_rec.UNIT_EFFECTIVITY_ID IS NOT NULL) THEN
1631: l_get_ue_flag := false; -- No need to get the UE Id: It is already available
1647: FETCH get_ue_id_csr INTO p_x_sr_mr_association_rec.UNIT_EFFECTIVITY_ID;
1648: CLOSE get_ue_id_csr;
1649: END IF;
1650:
1651: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1652: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
1653: END IF;
1654: END Get_MR_UnitEffectivity;
1655:
1648: CLOSE get_ue_id_csr;
1649: END IF;
1650:
1651: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1652: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
1653: END IF;
1654: END Get_MR_UnitEffectivity;
1655:
1656: ----------------------------------------
1676: l_get_mr_flag BOOLEAN := true;
1677: l_get_inst_flag BOOLEAN := true;
1678:
1679: BEGIN
1680: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1681: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
1682: END IF;
1683:
1684: IF (p_x_sr_mr_association_rec.MR_HEADER_ID IS NOT NULL) THEN
1677: l_get_inst_flag BOOLEAN := true;
1678:
1679: BEGIN
1680: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1681: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
1682: END IF;
1683:
1684: IF (p_x_sr_mr_association_rec.MR_HEADER_ID IS NOT NULL) THEN
1685: l_get_mr_flag := false; -- No need to get the MR Id: It is already available
1710: FETCH get_csi_instance_id_csr INTO p_x_sr_mr_association_rec.CSI_INSTANCE_ID;
1711: CLOSE get_csi_instance_id_csr;
1712: END IF;
1713:
1714: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1715: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
1716: END IF;
1717: END Get_New_Asso_Val_To_Id;
1718:
1711: CLOSE get_csi_instance_id_csr;
1712: END IF;
1713:
1714: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1715: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
1716: END IF;
1717: END Get_New_Asso_Val_To_Id;
1718:
1719: ----------------------------------------
1731: l_temp_row_id VARCHAR2(30);
1732: L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Create_MR_Unit_Effectivity';
1733:
1734: BEGIN
1735: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1736: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
1737: END IF;
1738:
1739: AHL_UNIT_EFFECTIVITIES_PKG.Insert_Row (
1732: L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Create_MR_Unit_Effectivity';
1733:
1734: BEGIN
1735: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1736: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
1737: END IF;
1738:
1739: AHL_UNIT_EFFECTIVITIES_PKG.Insert_Row (
1740: X_ROWID => l_temp_row_id,
1799: X_LAST_UPDATE_DATE => sysdate,
1800: X_LAST_UPDATED_BY => fnd_global.user_id,
1801: X_LAST_UPDATE_LOGIN => fnd_global.login_id );
1802:
1803: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1804: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Created New UE with Id ' || x_ue_id);
1805: END IF;
1806:
1807: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1800: X_LAST_UPDATED_BY => fnd_global.user_id,
1801: X_LAST_UPDATE_LOGIN => fnd_global.login_id );
1802:
1803: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1804: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Created New UE with Id ' || x_ue_id);
1805: END IF;
1806:
1807: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1808: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
1803: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1804: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Created New UE with Id ' || x_ue_id);
1805: END IF;
1806:
1807: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1808: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
1809: END IF;
1810: END Create_MR_Unit_Effectivity;
1811:
1804: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Created New UE with Id ' || x_ue_id);
1805: END IF;
1806:
1807: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1808: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
1809: END IF;
1810: END Create_MR_Unit_Effectivity;
1811:
1812: ----------------------------------------
1824:
1825: L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Create_UE_Relationship';
1826:
1827: BEGIN
1828: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1829: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
1830: END IF;
1831:
1832: AHL_UE_RELATIONSHIPS_PKG.INSERT_ROW(
1825: L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Create_UE_Relationship';
1826:
1827: BEGIN
1828: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1829: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
1830: END IF;
1831:
1832: AHL_UE_RELATIONSHIPS_PKG.INSERT_ROW(
1833: X_UE_RELATIONSHIP_ID => x_ue_rel_id,
1857: X_CREATION_DATE => SYSDATE,
1858: X_CREATED_BY => p_user_id,
1859: X_LAST_UPDATE_LOGIN => p_login_id);
1860:
1861: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1862: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Created New UE Relationship with Id ' || x_ue_rel_id);
1863: END IF;
1864:
1865: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1858: X_CREATED_BY => p_user_id,
1859: X_LAST_UPDATE_LOGIN => p_login_id);
1860:
1861: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1862: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Created New UE Relationship with Id ' || x_ue_rel_id);
1863: END IF;
1864:
1865: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1866: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
1861: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1862: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Created New UE Relationship with Id ' || x_ue_rel_id);
1863: END IF;
1864:
1865: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1866: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
1867: END IF;
1868: END Create_UE_Relationship;
1869:
1862: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Created New UE Relationship with Id ' || x_ue_rel_id);
1863: END IF;
1864:
1865: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1866: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
1867: END IF;
1868: END Create_UE_Relationship;
1869:
1870: ----------------------------------------
1927:
1928: L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Process_Group_MR';
1929:
1930: BEGIN
1931: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1932: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
1933: END IF;
1934: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1935: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Processing Group MR with Id: ' || p_mr_header_id);
1928: L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Process_Group_MR';
1929:
1930: BEGIN
1931: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1932: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
1933: END IF;
1934: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1935: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Processing Group MR with Id: ' || p_mr_header_id);
1936: END IF;
1930: BEGIN
1931: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1932: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
1933: END IF;
1934: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1935: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Processing Group MR with Id: ' || p_mr_header_id);
1936: END IF;
1937:
1938: -- Evaluate Group MRs
1931: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1932: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
1933: END IF;
1934: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1935: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Processing Group MR with Id: ' || p_mr_header_id);
1936: END IF;
1937:
1938: -- Evaluate Group MRs
1939: -- Populate AHL_APPLICABLE_MRS if required
1941: FETCH get_app_mr_table_count_csr INTO l_temp_count;
1942: CLOSE get_app_mr_table_count_csr;
1943: IF (l_temp_count > 0) THEN
1944: -- AHL_APPLICABLE_MRS is already populated. No need to Call Populate_Appl_MRs
1945: IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1946: FND_LOG.STRING(FND_LOG.LEVEL_EVENT, L_DEBUG_KEY, 'Number of records in AHL_APPLICABLE_MRS: ' || l_temp_count || '. Not calling AHL_UMP_UTIL_PKG.Populate_Appl_MRs.');
1947: END IF;
1948: ELSE
1949: IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1942: CLOSE get_app_mr_table_count_csr;
1943: IF (l_temp_count > 0) THEN
1944: -- AHL_APPLICABLE_MRS is already populated. No need to Call Populate_Appl_MRs
1945: IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1946: FND_LOG.STRING(FND_LOG.LEVEL_EVENT, L_DEBUG_KEY, 'Number of records in AHL_APPLICABLE_MRS: ' || l_temp_count || '. Not calling AHL_UMP_UTIL_PKG.Populate_Appl_MRs.');
1947: END IF;
1948: ELSE
1949: IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1950: FND_LOG.STRING(FND_LOG.LEVEL_EVENT, L_DEBUG_KEY, 'Number of records in AHL_APPLICABLE_MRS is zero. Calling AHL_UMP_UTIL_PKG.Populate_Appl_MRs with p_csi_ii_id = ' || p_csi_instance_id);
1945: IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1946: FND_LOG.STRING(FND_LOG.LEVEL_EVENT, L_DEBUG_KEY, 'Number of records in AHL_APPLICABLE_MRS: ' || l_temp_count || '. Not calling AHL_UMP_UTIL_PKG.Populate_Appl_MRs.');
1947: END IF;
1948: ELSE
1949: IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1950: FND_LOG.STRING(FND_LOG.LEVEL_EVENT, L_DEBUG_KEY, 'Number of records in AHL_APPLICABLE_MRS is zero. Calling AHL_UMP_UTIL_PKG.Populate_Appl_MRs with p_csi_ii_id = ' || p_csi_instance_id);
1951: END IF;
1952: AHL_UMP_UTIL_PKG.Populate_Appl_MRs(p_csi_ii_id => p_csi_instance_id,
1953: x_return_status => l_temp_return_status,
1946: FND_LOG.STRING(FND_LOG.LEVEL_EVENT, L_DEBUG_KEY, 'Number of records in AHL_APPLICABLE_MRS: ' || l_temp_count || '. Not calling AHL_UMP_UTIL_PKG.Populate_Appl_MRs.');
1947: END IF;
1948: ELSE
1949: IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1950: FND_LOG.STRING(FND_LOG.LEVEL_EVENT, L_DEBUG_KEY, 'Number of records in AHL_APPLICABLE_MRS is zero. Calling AHL_UMP_UTIL_PKG.Populate_Appl_MRs with p_csi_ii_id = ' || p_csi_instance_id);
1951: END IF;
1952: AHL_UMP_UTIL_PKG.Populate_Appl_MRs(p_csi_ii_id => p_csi_instance_id,
1953: x_return_status => l_temp_return_status,
1954: x_msg_count => l_temp_msg_count,
1958: l_curr_mr_title := Get_MR_Title_From_MR_Id(p_mr_header_id);
1959: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_GRP_MR_FAILED');
1960: FND_MESSAGE.Set_Token('MR_TITLE', l_curr_mr_title);
1961: FND_MSG_PUB.ADD;
1962: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1963: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1964: END IF;
1965: RETURN; -- No need to process this Group MR further
1966: END IF; -- Populate_Appl_MRs Failed
1959: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_GRP_MR_FAILED');
1960: FND_MESSAGE.Set_Token('MR_TITLE', l_curr_mr_title);
1961: FND_MSG_PUB.ADD;
1962: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1963: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1964: END IF;
1965: RETURN; -- No need to process this Group MR further
1966: END IF; -- Populate_Appl_MRs Failed
1967: OPEN get_app_mr_table_count_csr;
1966: END IF; -- Populate_Appl_MRs Failed
1967: OPEN get_app_mr_table_count_csr;
1968: FETCH get_app_mr_table_count_csr INTO l_temp_count;
1969: CLOSE get_app_mr_table_count_csr;
1970: IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1971: FND_LOG.STRING(FND_LOG.LEVEL_EVENT, L_DEBUG_KEY, 'After calling AHL_UMP_UTIL_PKG.Populate_Appl_MRs, Number of records in AHL_APPLICABLE_MRS: ' || l_temp_count);
1972: END IF;
1973: END IF; -- AHL_APPLICABLE_MRS temp table is empty
1974:
1967: OPEN get_app_mr_table_count_csr;
1968: FETCH get_app_mr_table_count_csr INTO l_temp_count;
1969: CLOSE get_app_mr_table_count_csr;
1970: IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1971: FND_LOG.STRING(FND_LOG.LEVEL_EVENT, L_DEBUG_KEY, 'After calling AHL_UMP_UTIL_PKG.Populate_Appl_MRs, Number of records in AHL_APPLICABLE_MRS: ' || l_temp_count);
1972: END IF;
1973: END IF; -- AHL_APPLICABLE_MRS temp table is empty
1974:
1975: -- Call process_Group_MR_Instance
1975: -- Call process_Group_MR_Instance
1976: /*OPEN get_relns_table_count_csr;
1977: FETCH get_relns_table_count_csr INTO l_temp_count;
1978: CLOSE get_relns_table_count_csr;
1979: IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1980: FND_LOG.STRING(FND_LOG.LEVEL_EVENT, L_DEBUG_KEY, 'Number of records in AHL_APPLICABLE_MR_RELNS before processing: ' || l_temp_count);
1981: FND_LOG.STRING(FND_LOG.LEVEL_EVENT, L_DEBUG_KEY, 'About to call AHL_UMP_UTIL_PKG.process_Group_MR_Instance with p_top_mr_id = ' ||
1982: p_mr_header_id || ' and p_top_item_instance_id = ' || p_csi_instance_id);
1983: END IF;*/
1976: /*OPEN get_relns_table_count_csr;
1977: FETCH get_relns_table_count_csr INTO l_temp_count;
1978: CLOSE get_relns_table_count_csr;
1979: IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1980: FND_LOG.STRING(FND_LOG.LEVEL_EVENT, L_DEBUG_KEY, 'Number of records in AHL_APPLICABLE_MR_RELNS before processing: ' || l_temp_count);
1981: FND_LOG.STRING(FND_LOG.LEVEL_EVENT, L_DEBUG_KEY, 'About to call AHL_UMP_UTIL_PKG.process_Group_MR_Instance with p_top_mr_id = ' ||
1982: p_mr_header_id || ' and p_top_item_instance_id = ' || p_csi_instance_id);
1983: END IF;*/
1984: AHL_UMP_UTIL_PKG.process_Group_MR_Instance(p_top_mr_id => p_mr_header_id,
1977: FETCH get_relns_table_count_csr INTO l_temp_count;
1978: CLOSE get_relns_table_count_csr;
1979: IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1980: FND_LOG.STRING(FND_LOG.LEVEL_EVENT, L_DEBUG_KEY, 'Number of records in AHL_APPLICABLE_MR_RELNS before processing: ' || l_temp_count);
1981: FND_LOG.STRING(FND_LOG.LEVEL_EVENT, L_DEBUG_KEY, 'About to call AHL_UMP_UTIL_PKG.process_Group_MR_Instance with p_top_mr_id = ' ||
1982: p_mr_header_id || ' and p_top_item_instance_id = ' || p_csi_instance_id);
1983: END IF;*/
1984: AHL_UMP_UTIL_PKG.process_Group_MR_Instance(p_top_mr_id => p_mr_header_id,
1985: p_top_item_instance_id => p_csi_instance_id,
1986: p_init_temp_table => G_YES_FLAG); -- To clean up temp table first
1987: /*OPEN get_relns_table_count_csr;
1988: FETCH get_relns_table_count_csr INTO l_temp_count;
1989: CLOSE get_relns_table_count_csr;
1990: IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1991: FND_LOG.STRING(FND_LOG.LEVEL_EVENT, L_DEBUG_KEY, 'Number of records in AHL_APPLICABLE_MR_RELNS after calling AHL_UMP_UTIL_PKG.process_Group_MR_Instance: ' || l_temp_count);
1992: END IF;*/
1993:
1994: -- Check for duplicates
1987: /*OPEN get_relns_table_count_csr;
1988: FETCH get_relns_table_count_csr INTO l_temp_count;
1989: CLOSE get_relns_table_count_csr;
1990: IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1991: FND_LOG.STRING(FND_LOG.LEVEL_EVENT, L_DEBUG_KEY, 'Number of records in AHL_APPLICABLE_MR_RELNS after calling AHL_UMP_UTIL_PKG.process_Group_MR_Instance: ' || l_temp_count);
1992: END IF;*/
1993:
1994: -- Check for duplicates
1995: OPEN get_duplicate_mrs_csr(p_sr_ue_id => p_sr_ue_id);
2002: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_GROUP_DUP_MR');
2003: FND_MESSAGE.Set_Token('NEW_MR', l_curr_mr_title);
2004: FND_MESSAGE.Set_Token('DUP_MR', l_dup_mr_title);
2005: FND_MSG_PUB.ADD;
2006: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2007: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
2008: END IF;
2009: p_x_valid_flag := false;
2010: RETURN; -- No need to process this Group MR further
2003: FND_MESSAGE.Set_Token('NEW_MR', l_curr_mr_title);
2004: FND_MESSAGE.Set_Token('DUP_MR', l_dup_mr_title);
2005: FND_MSG_PUB.ADD;
2006: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2007: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
2008: END IF;
2009: p_x_valid_flag := false;
2010: RETURN; -- No need to process this Group MR further
2011: END IF;
2010: RETURN; -- No need to process this Group MR further
2011: END IF;
2012: CLOSE get_duplicate_mrs_csr;
2013:
2014: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2015: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Adding this Group MR will not cause any duplicates.');
2016: END IF;
2017:
2018: -- First create all the Unit Effectivities
2011: END IF;
2012: CLOSE get_duplicate_mrs_csr;
2013:
2014: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2015: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Adding this Group MR will not cause any duplicates.');
2016: END IF;
2017:
2018: -- First create all the Unit Effectivities
2019: l_temp_count := 0;
2031: UPDATE AHL_APPLICABLE_MR_RELNS
2032: SET UE_ID = l_new_ue_id
2033: WHERE CURRENT OF mr_relns_upd_csr;
2034: END LOOP;
2035: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2036: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Created ' || l_temp_count || ' Unit Effectivities for the Group MR.');
2037: END IF;
2038:
2039: -- Next, Create the UE Relationships
2032: SET UE_ID = l_new_ue_id
2033: WHERE CURRENT OF mr_relns_upd_csr;
2034: END LOOP;
2035: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2036: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Created ' || l_temp_count || ' Unit Effectivities for the Group MR.');
2037: END IF;
2038:
2039: -- Next, Create the UE Relationships
2040: l_temp_count := 0;
2052:
2053: l_temp_count := l_temp_count + 1;
2054:
2055: END LOOP;
2056: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2057: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Created ' || l_temp_count || ' UE Relationships for the Group MR.');
2058: END IF;
2059:
2060: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2053: l_temp_count := l_temp_count + 1;
2054:
2055: END LOOP;
2056: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2057: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Created ' || l_temp_count || ' UE Relationships for the Group MR.');
2058: END IF;
2059:
2060: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2061: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
2056: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2057: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Created ' || l_temp_count || ' UE Relationships for the Group MR.');
2058: END IF;
2059:
2060: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2061: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
2062: END IF;
2063: END Process_Group_MR;
2064:
2057: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Created ' || l_temp_count || ' UE Relationships for the Group MR.');
2058: END IF;
2059:
2060: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2061: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
2062: END IF;
2063: END Process_Group_MR;
2064:
2065: ----------------------------------------
2125: l_temp_count NUMBER := 0;
2126:
2127:
2128: BEGIN
2129: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2130: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
2131: END IF;
2132:
2133: FOR l_curr_ue_rec in get_MR_UEs_csr LOOP
2126:
2127:
2128: BEGIN
2129: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2130: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
2131: END IF;
2132:
2133: FOR l_curr_ue_rec in get_MR_UEs_csr LOOP
2134: IF (p_assigned_to_visit_flag = TRUE) THEN
2199: X_LAST_UPDATE_LOGIN => fnd_global.login_id -- Updated
2200: );
2201: l_temp_count := l_temp_count + 1;
2202: END LOOP;
2203: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2204: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Updated Due Date and Tolerance Flag in ' || l_temp_count|| ' dependent unit effectivities.');
2205: END IF;
2206:
2207: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2200: );
2201: l_temp_count := l_temp_count + 1;
2202: END LOOP;
2203: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2204: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Updated Due Date and Tolerance Flag in ' || l_temp_count|| ' dependent unit effectivities.');
2205: END IF;
2206:
2207: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2208: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
2203: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2204: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Updated Due Date and Tolerance Flag in ' || l_temp_count|| ' dependent unit effectivities.');
2205: END IF;
2206:
2207: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2208: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
2209: END IF;
2210: END Handle_MR_UE_Date_Change;
2211:
2204: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Updated Due Date and Tolerance Flag in ' || l_temp_count|| ' dependent unit effectivities.');
2205: END IF;
2206:
2207: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2208: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
2209: END IF;
2210: END Handle_MR_UE_Date_Change;
2211:
2212: ----------------------------------------
2248: l_visit_assign_code VARCHAR2(30);
2249: l_accomplished_date DATE;
2250:
2251: BEGIN
2252: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2253: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
2254: END IF;
2255:
2256: OPEN get_UE_dtls_csr;
2249: l_accomplished_date DATE;
2250:
2251: BEGIN
2252: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2253: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
2254: END IF;
2255:
2256: OPEN get_UE_dtls_csr;
2257: FETCH get_UE_dtls_csr INTO l_curr_ue_rec;
2281: l_temp_due_date := p_new_due_date;
2282: OPEN is_ue_assigned_to_visit_csr;
2283: FETCH is_ue_assigned_to_visit_csr INTO l_dummy;
2284: IF (is_ue_assigned_to_visit_csr%FOUND) THEN
2285: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2286: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Unit Effectivity ' || p_sr_ue_id || ' has been assigned to a visit.');
2287: END IF;
2288: x_assigned_to_visit_flag := TRUE;
2289: -- Get the Visit Start Date
2282: OPEN is_ue_assigned_to_visit_csr;
2283: FETCH is_ue_assigned_to_visit_csr INTO l_dummy;
2284: IF (is_ue_assigned_to_visit_csr%FOUND) THEN
2285: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2286: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Unit Effectivity ' || p_sr_ue_id || ' has been assigned to a visit.');
2287: END IF;
2288: x_assigned_to_visit_flag := TRUE;
2289: -- Get the Visit Start Date
2290: AHL_UMP_UTIL_PKG.Get_Visit_Details(p_unit_effectivity_id => p_sr_ue_id,
2296: x_new_tolerance_flag := G_YES_FLAG;
2297: ELSE
2298: l_temp_tolerance_flag := null;
2299: END IF;
2300: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2301: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Visit Start Date: ' || l_visit_start_date ||
2302: ', New Expected Resolution date of SR: ' || p_new_due_date ||
2303: ', Tolerance Flag Set to ' || l_temp_tolerance_flag);
2304: END IF;
2297: ELSE
2298: l_temp_tolerance_flag := null;
2299: END IF;
2300: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2301: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Visit Start Date: ' || l_visit_start_date ||
2302: ', New Expected Resolution date of SR: ' || p_new_due_date ||
2303: ', Tolerance Flag Set to ' || l_temp_tolerance_flag);
2304: END IF;
2305: ELSE
2302: ', New Expected Resolution date of SR: ' || p_new_due_date ||
2303: ', Tolerance Flag Set to ' || l_temp_tolerance_flag);
2304: END IF;
2305: ELSE
2306: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2307: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Unit Effectivity ' || p_sr_ue_id || ' has not been assigned to a visit.');
2308: END IF;
2309: END IF; -- Assigned to a visit or not
2310: CLOSE is_ue_assigned_to_visit_csr;
2303: ', Tolerance Flag Set to ' || l_temp_tolerance_flag);
2304: END IF;
2305: ELSE
2306: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2307: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Unit Effectivity ' || p_sr_ue_id || ' has not been assigned to a visit.');
2308: END IF;
2309: END IF; -- Assigned to a visit or not
2310: CLOSE is_ue_assigned_to_visit_csr;
2311: ELSE
2308: END IF;
2309: END IF; -- Assigned to a visit or not
2310: CLOSE is_ue_assigned_to_visit_csr;
2311: ELSE
2312: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2313: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'No Due Date change for Unit Effectivity ' || p_sr_ue_id);
2314: END IF;
2315: l_temp_due_date := l_curr_ue_rec.DUE_DATE;
2316: l_temp_tolerance_flag := l_curr_ue_rec.TOLERANCE_FLAG;
2309: END IF; -- Assigned to a visit or not
2310: CLOSE is_ue_assigned_to_visit_csr;
2311: ELSE
2312: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2313: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'No Due Date change for Unit Effectivity ' || p_sr_ue_id);
2314: END IF;
2315: l_temp_due_date := l_curr_ue_rec.DUE_DATE;
2316: l_temp_tolerance_flag := l_curr_ue_rec.TOLERANCE_FLAG;
2317: END IF;
2377: X_LAST_UPDATE_DATE => SYSDATE, -- Updated
2378: X_LAST_UPDATED_BY => fnd_global.user_id, -- Updated
2379: X_LAST_UPDATE_LOGIN => fnd_global.login_id -- Updated
2380: );
2381: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2382: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Unit Effectivity ' || p_sr_ue_id || ' has been updated.');
2383: END IF;
2384:
2385: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2378: X_LAST_UPDATED_BY => fnd_global.user_id, -- Updated
2379: X_LAST_UPDATE_LOGIN => fnd_global.login_id -- Updated
2380: );
2381: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2382: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Unit Effectivity ' || p_sr_ue_id || ' has been updated.');
2383: END IF;
2384:
2385: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2386: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
2381: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2382: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Unit Effectivity ' || p_sr_ue_id || ' has been updated.');
2383: END IF;
2384:
2385: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2386: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
2387: END IF;
2388: END Update_SR_Unit_Effectivity;
2389:
2382: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Unit Effectivity ' || p_sr_ue_id || ' has been updated.');
2383: END IF;
2384:
2385: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2386: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
2387: END IF;
2388: END Update_SR_Unit_Effectivity;
2389:
2390: ----------------------------------------
2409:
2410: l_dummy VARCHAR2(1);
2411:
2412: BEGIN
2413: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2414: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
2415: END IF;
2416:
2417: -- Ensure that request id is not null
2410: l_dummy VARCHAR2(1);
2411:
2412: BEGIN
2413: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2414: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
2415: END IF;
2416:
2417: -- Ensure that request id is not null
2418: IF(CS_SERVICEREQUEST_PVT.user_hooks_rec.request_id IS NULL) THEN
2417: -- Ensure that request id is not null
2418: IF(CS_SERVICEREQUEST_PVT.user_hooks_rec.request_id IS NULL) THEN
2419: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_REQ_ID_NULL');
2420: FND_MSG_PUB.ADD;
2421: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2422: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
2423: END IF;
2424: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2425: END IF;
2418: IF(CS_SERVICEREQUEST_PVT.user_hooks_rec.request_id IS NULL) THEN
2419: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_REQ_ID_NULL');
2420: FND_MSG_PUB.ADD;
2421: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2422: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
2423: END IF;
2424: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2425: END IF;
2426:
2431: CLOSE check_request_exists_csr;
2432: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_REQ_ID_INVALID');
2433: FND_MESSAGE.Set_Token('REQ_ID', CS_SERVICEREQUEST_PVT.user_hooks_rec.request_id);
2434: FND_MSG_PUB.ADD;
2435: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2436: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
2437: END IF;
2438: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2439: END IF;
2432: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_REQ_ID_INVALID');
2433: FND_MESSAGE.Set_Token('REQ_ID', CS_SERVICEREQUEST_PVT.user_hooks_rec.request_id);
2434: FND_MSG_PUB.ADD;
2435: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2436: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
2437: END IF;
2438: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2439: END IF;
2440: CLOSE check_request_exists_csr;
2447: CLOSE get_ue_dtls_csr;
2448: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_REQ_ID_INVALID');
2449: FND_MESSAGE.Set_Token('REQ_ID', CS_SERVICEREQUEST_PVT.user_hooks_rec.request_id);
2450: FND_MSG_PUB.ADD;
2451: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2452: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
2453: END IF;
2454: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2455: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Could not find any valid Unit Effectivity for this CS_INCIDENT_ID: ' || CS_SERVICEREQUEST_PVT.user_hooks_rec.request_id);
2448: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_REQ_ID_INVALID');
2449: FND_MESSAGE.Set_Token('REQ_ID', CS_SERVICEREQUEST_PVT.user_hooks_rec.request_id);
2450: FND_MSG_PUB.ADD;
2451: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2452: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
2453: END IF;
2454: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2455: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Could not find any valid Unit Effectivity for this CS_INCIDENT_ID: ' || CS_SERVICEREQUEST_PVT.user_hooks_rec.request_id);
2456: END IF;
2450: FND_MSG_PUB.ADD;
2451: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2452: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
2453: END IF;
2454: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2455: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Could not find any valid Unit Effectivity for this CS_INCIDENT_ID: ' || CS_SERVICEREQUEST_PVT.user_hooks_rec.request_id);
2456: END IF;
2457: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2458: END IF; -- UE Not Found
2451: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2452: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
2453: END IF;
2454: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2455: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Could not find any valid Unit Effectivity for this CS_INCIDENT_ID: ' || CS_SERVICEREQUEST_PVT.user_hooks_rec.request_id);
2456: END IF;
2457: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2458: END IF; -- UE Not Found
2459: CLOSE get_ue_dtls_csr;
2458: END IF; -- UE Not Found
2459: CLOSE get_ue_dtls_csr;
2460: END IF; -- CMRO Type SR
2461:
2462: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2463: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
2464: END IF;
2465:
2466: END Validate_Request_For_Update;
2459: CLOSE get_ue_dtls_csr;
2460: END IF; -- CMRO Type SR
2461:
2462: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2463: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
2464: END IF;
2465:
2466: END Validate_Request_For_Update;
2467:
2488: l_dummy VARCHAR2(1);
2489: l_temp_return_status VARCHAR2(1);
2490:
2491: BEGIN
2492: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2493: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
2494: END IF;
2495:
2496: -- Ensure that the SR is not closed
2489: l_temp_return_status VARCHAR2(1);
2490:
2491: BEGIN
2492: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2493: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
2494: END IF;
2495:
2496: -- Ensure that the SR is not closed
2497: IF (CS_SERVICEREQUEST_PVT.user_hooks_rec.status_flag = G_SR_CLOSED_FLAG) THEN
2496: -- Ensure that the SR is not closed
2497: IF (CS_SERVICEREQUEST_PVT.user_hooks_rec.status_flag = G_SR_CLOSED_FLAG) THEN
2498: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_REQ_CLOSED');
2499: FND_MSG_PUB.ADD;
2500: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2501: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
2502: END IF;
2503: RAISE FND_API.G_EXC_ERROR;
2504: END IF;
2497: IF (CS_SERVICEREQUEST_PVT.user_hooks_rec.status_flag = G_SR_CLOSED_FLAG) THEN
2498: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_REQ_CLOSED');
2499: FND_MSG_PUB.ADD;
2500: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2501: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
2502: END IF;
2503: RAISE FND_API.G_EXC_ERROR;
2504: END IF;
2505:
2503: RAISE FND_API.G_EXC_ERROR;
2504: END IF;
2505:
2506: IF (NVL(CS_SERVICEREQUEST_PVT.user_hooks_rec.new_type_cmro_flag, G_NO_FLAG) = G_NO_FLAG) THEN
2507: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2508: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'CMRO to Non-CMRO type conversion');
2509: END IF;
2510: -- CMRO to Non-CMRO type
2511: -- JR: Modified on 10/29/2003 (Using Status Id instead of Status Code)
2504: END IF;
2505:
2506: IF (NVL(CS_SERVICEREQUEST_PVT.user_hooks_rec.new_type_cmro_flag, G_NO_FLAG) = G_NO_FLAG) THEN
2507: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2508: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'CMRO to Non-CMRO type conversion');
2509: END IF;
2510: -- CMRO to Non-CMRO type
2511: -- JR: Modified on 10/29/2003 (Using Status Id instead of Status Code)
2512: IF (CS_SERVICEREQUEST_PVT.user_hooks_rec.status_id = G_SR_PLANNED_STATUS_ID) THEN
2514: FETCH get_tasks_for_ue_csr INTO l_dummy;
2515: IF (get_tasks_for_ue_csr%FOUND) THEN
2516: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_ASGND_TO_VISIT');
2517: FND_MSG_PUB.ADD;
2518: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2519: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
2520: END IF;
2521: ELSE
2522: -- This condition of a (originally) CMRO SR in Planned Status but not associated
2515: IF (get_tasks_for_ue_csr%FOUND) THEN
2516: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_ASGND_TO_VISIT');
2517: FND_MSG_PUB.ADD;
2518: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2519: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
2520: END IF;
2521: ELSE
2522: -- This condition of a (originally) CMRO SR in Planned Status but not associated
2523: -- to a visit is not possible
2520: END IF;
2521: ELSE
2522: -- This condition of a (originally) CMRO SR in Planned Status but not associated
2523: -- to a visit is not possible
2524: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2525: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Odd case of originally CMRO type SR in Planned status but not assigned to a visit');
2526: END IF;
2527: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_REQ_PLANNED');
2528: FND_MSG_PUB.ADD;
2521: ELSE
2522: -- This condition of a (originally) CMRO SR in Planned Status but not associated
2523: -- to a visit is not possible
2524: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2525: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Odd case of originally CMRO type SR in Planned status but not assigned to a visit');
2526: END IF;
2527: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_REQ_PLANNED');
2528: FND_MSG_PUB.ADD;
2529: END IF; -- tasks found
2534: -- Delete all the dependent UEs and UE relationships
2535: FOR rel_and_ue_id_rec in get_all_ue_and_rel_ids_csr LOOP
2536: -- First delete the relationship
2537: AHL_UE_RELATIONSHIPS_PKG.DELETE_ROW(X_UE_RELATIONSHIP_ID => rel_and_ue_id_rec.UE_RELATIONSHIP_ID);
2538: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2539: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Deleted Relationship with UE_RELATIONSHIP_ID = ' || rel_and_ue_id_rec.UE_RELATIONSHIP_ID);
2540: END IF;
2541: -- Next delete the unit effectivity
2542: AHL_UNIT_EFFECTIVITIES_PKG.DELETE_ROW(X_UNIT_EFFECTIVITY_ID => rel_and_ue_id_rec.RELATED_UE_ID);
2535: FOR rel_and_ue_id_rec in get_all_ue_and_rel_ids_csr LOOP
2536: -- First delete the relationship
2537: AHL_UE_RELATIONSHIPS_PKG.DELETE_ROW(X_UE_RELATIONSHIP_ID => rel_and_ue_id_rec.UE_RELATIONSHIP_ID);
2538: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2539: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Deleted Relationship with UE_RELATIONSHIP_ID = ' || rel_and_ue_id_rec.UE_RELATIONSHIP_ID);
2540: END IF;
2541: -- Next delete the unit effectivity
2542: AHL_UNIT_EFFECTIVITIES_PKG.DELETE_ROW(X_UNIT_EFFECTIVITY_ID => rel_and_ue_id_rec.RELATED_UE_ID);
2543: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2539: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Deleted Relationship with UE_RELATIONSHIP_ID = ' || rel_and_ue_id_rec.UE_RELATIONSHIP_ID);
2540: END IF;
2541: -- Next delete the unit effectivity
2542: AHL_UNIT_EFFECTIVITIES_PKG.DELETE_ROW(X_UNIT_EFFECTIVITY_ID => rel_and_ue_id_rec.RELATED_UE_ID);
2543: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2544: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Deleted Unit Effectivity with UNIT_EFFECTIVITY_ID = ' || rel_and_ue_id_rec.RELATED_UE_ID);
2545: END IF;
2546: END LOOP;
2547:
2540: END IF;
2541: -- Next delete the unit effectivity
2542: AHL_UNIT_EFFECTIVITIES_PKG.DELETE_ROW(X_UNIT_EFFECTIVITY_ID => rel_and_ue_id_rec.RELATED_UE_ID);
2543: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2544: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Deleted Unit Effectivity with UNIT_EFFECTIVITY_ID = ' || rel_and_ue_id_rec.RELATED_UE_ID);
2545: END IF;
2546: END LOOP;
2547:
2548: -- Delete the SR's Unit Effectivity
2546: END LOOP;
2547:
2548: -- Delete the SR's Unit Effectivity
2549: AHL_UNIT_EFFECTIVITIES_PKG.DELETE_ROW(X_UNIT_EFFECTIVITY_ID => p_sr_ue_id);
2550: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2551: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Deleted Unit Effectivity with UNIT_EFFECTIVITY_ID = ' || p_sr_ue_id);
2552: END IF;
2553: END IF; -- Planned or Not
2554: ELSE
2547:
2548: -- Delete the SR's Unit Effectivity
2549: AHL_UNIT_EFFECTIVITIES_PKG.DELETE_ROW(X_UNIT_EFFECTIVITY_ID => p_sr_ue_id);
2550: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2551: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Deleted Unit Effectivity with UNIT_EFFECTIVITY_ID = ' || p_sr_ue_id);
2552: END IF;
2553: END IF; -- Planned or Not
2554: ELSE
2555: -- Non-CMRO to CMRO type
2552: END IF;
2553: END IF; -- Planned or Not
2554: ELSE
2555: -- Non-CMRO to CMRO type
2556: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2557: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Non-CMRO to CMRO type conversion');
2558: END IF;
2559: IF (CS_SERVICEREQUEST_PVT.user_hooks_rec.status_id = G_SR_PLANNED_STATUS_ID) THEN
2560: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_REQ_PLANNED');
2553: END IF; -- Planned or Not
2554: ELSE
2555: -- Non-CMRO to CMRO type
2556: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2557: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Non-CMRO to CMRO type conversion');
2558: END IF;
2559: IF (CS_SERVICEREQUEST_PVT.user_hooks_rec.status_id = G_SR_PLANNED_STATUS_ID) THEN
2560: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_REQ_PLANNED');
2561: FND_MSG_PUB.ADD;
2558: END IF;
2559: IF (CS_SERVICEREQUEST_PVT.user_hooks_rec.status_id = G_SR_PLANNED_STATUS_ID) THEN
2560: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_REQ_PLANNED');
2561: FND_MSG_PUB.ADD;
2562: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2563: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
2564: END IF;
2565: RAISE FND_API.G_EXC_ERROR;
2566: ELSE
2559: IF (CS_SERVICEREQUEST_PVT.user_hooks_rec.status_id = G_SR_PLANNED_STATUS_ID) THEN
2560: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_REQ_PLANNED');
2561: FND_MSG_PUB.ADD;
2562: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2563: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
2564: END IF;
2565: RAISE FND_API.G_EXC_ERROR;
2566: ELSE
2567: -- Not yet Planned, but open: Call Create_SR_Init_Effectivity
2564: END IF;
2565: RAISE FND_API.G_EXC_ERROR;
2566: ELSE
2567: -- Not yet Planned, but open: Call Create_SR_Init_Effectivity
2568: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2569: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'About to call Create_SR_Unit_Effectivity.');
2570: END IF;
2571: Create_SR_Unit_Effectivity(x_return_status => l_temp_return_status);
2572: /* For Testing
2565: RAISE FND_API.G_EXC_ERROR;
2566: ELSE
2567: -- Not yet Planned, but open: Call Create_SR_Init_Effectivity
2568: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2569: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'About to call Create_SR_Unit_Effectivity.');
2570: END IF;
2571: Create_SR_Unit_Effectivity(x_return_status => l_temp_return_status);
2572: /* For Testing
2573: UMP_SR_TEST.Create_SR_Unit_Effectivity(x_return_status => l_temp_return_status);
2572: /* For Testing
2573: UMP_SR_TEST.Create_SR_Unit_Effectivity(x_return_status => l_temp_return_status);
2574: */
2575: IF (l_temp_return_status = FND_API.G_RET_STS_SUCCESS) THEN
2576: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2577: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Create_SR_Unit_Effectivity Returned Success.');
2578: END IF;
2579: ELSIF (l_temp_return_status = FND_API.G_RET_STS_ERROR) THEN
2580: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2573: UMP_SR_TEST.Create_SR_Unit_Effectivity(x_return_status => l_temp_return_status);
2574: */
2575: IF (l_temp_return_status = FND_API.G_RET_STS_SUCCESS) THEN
2576: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2577: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Create_SR_Unit_Effectivity Returned Success.');
2578: END IF;
2579: ELSIF (l_temp_return_status = FND_API.G_RET_STS_ERROR) THEN
2580: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2581: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Create_SR_Unit_Effectivity Returned User Error.');
2576: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2577: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Create_SR_Unit_Effectivity Returned Success.');
2578: END IF;
2579: ELSIF (l_temp_return_status = FND_API.G_RET_STS_ERROR) THEN
2580: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2581: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Create_SR_Unit_Effectivity Returned User Error.');
2582: END IF;
2583: RAISE FND_API.G_EXC_ERROR;
2584: ELSE
2577: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Create_SR_Unit_Effectivity Returned Success.');
2578: END IF;
2579: ELSIF (l_temp_return_status = FND_API.G_RET_STS_ERROR) THEN
2580: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2581: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Create_SR_Unit_Effectivity Returned User Error.');
2582: END IF;
2583: RAISE FND_API.G_EXC_ERROR;
2584: ELSE
2585: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2581: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Create_SR_Unit_Effectivity Returned User Error.');
2582: END IF;
2583: RAISE FND_API.G_EXC_ERROR;
2584: ELSE
2585: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2586: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Create_SR_Unit_Effectivity Returned Unexpected Error.');
2587: END IF;
2588: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2589: END IF;
2582: END IF;
2583: RAISE FND_API.G_EXC_ERROR;
2584: ELSE
2585: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2586: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Create_SR_Unit_Effectivity Returned Unexpected Error.');
2587: END IF;
2588: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2589: END IF;
2590: END IF; -- Planned or not
2589: END IF;
2590: END IF; -- Planned or not
2591: END IF; -- CMRO to Non-CMRO or vice-versa
2592:
2593: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2594: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
2595: END IF;
2596:
2597: END Handle_Type_Change;
2590: END IF; -- Planned or not
2591: END IF; -- CMRO to Non-CMRO or vice-versa
2592:
2593: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2594: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
2595: END IF;
2596:
2597: END Handle_Type_Change;
2598:
2622: l_assigned BOOLEAN;
2623: l_new_tolerance_flag VARCHAR2(1);
2624:
2625: BEGIN
2626: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2627: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
2628: END IF;
2629:
2630: -- Get the original (old) values and lock the row
2623: l_new_tolerance_flag VARCHAR2(1);
2624:
2625: BEGIN
2626: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2627: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
2628: END IF;
2629:
2630: -- Get the original (old) values and lock the row
2631: OPEN get_and_lock_ue_dtls_csr; -- Will throw exception (ORA-00054) if cannot acquire lock
2645: x_status_changed => l_status_changed,
2646: x_new_ue_status => l_new_ue_status);
2647:
2648: -- Handle Expected Resolution Date (Due Date) Change
2649: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2650: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'l_old_due_date = ' || l_old_due_date || ', l_new_due_date = ' || l_new_due_date);
2651: END IF;
2652: IF ((l_old_due_date IS NOT NULL AND l_new_due_date IS NULL) OR
2653: (l_old_due_date IS NULL AND l_new_due_date IS NOT NULL) OR
2646: x_new_ue_status => l_new_ue_status);
2647:
2648: -- Handle Expected Resolution Date (Due Date) Change
2649: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2650: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'l_old_due_date = ' || l_old_due_date || ', l_new_due_date = ' || l_new_due_date);
2651: END IF;
2652: IF ((l_old_due_date IS NOT NULL AND l_new_due_date IS NULL) OR
2653: (l_old_due_date IS NULL AND l_new_due_date IS NOT NULL) OR
2654: (l_old_due_date IS NOT NULL AND l_new_due_date IS NOT NULL AND l_old_due_date <> l_new_due_date)) THEN
2665: END IF;
2666:
2667: IF (l_instance_changed = true OR l_status_changed = true OR l_due_date_changed = true) THEN
2668: -- Apply changes to the SR Unit Effectivity
2669: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2670: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Changes have happened.');
2671: IF (l_instance_changed = true) THEN
2672: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'l_instance_changed = Y');
2673: ELSE
2666:
2667: IF (l_instance_changed = true OR l_status_changed = true OR l_due_date_changed = true) THEN
2668: -- Apply changes to the SR Unit Effectivity
2669: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2670: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Changes have happened.');
2671: IF (l_instance_changed = true) THEN
2672: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'l_instance_changed = Y');
2673: ELSE
2674: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'l_instance_changed = N');
2668: -- Apply changes to the SR Unit Effectivity
2669: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2670: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Changes have happened.');
2671: IF (l_instance_changed = true) THEN
2672: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'l_instance_changed = Y');
2673: ELSE
2674: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'l_instance_changed = N');
2675: END IF;
2676: IF (l_status_changed = true) THEN
2670: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Changes have happened.');
2671: IF (l_instance_changed = true) THEN
2672: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'l_instance_changed = Y');
2673: ELSE
2674: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'l_instance_changed = N');
2675: END IF;
2676: IF (l_status_changed = true) THEN
2677: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'l_status_changed = Y');
2678: ELSE
2673: ELSE
2674: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'l_instance_changed = N');
2675: END IF;
2676: IF (l_status_changed = true) THEN
2677: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'l_status_changed = Y');
2678: ELSE
2679: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'l_status_changed = N');
2680: END IF;
2681: IF (l_due_date_changed = true) THEN
2675: END IF;
2676: IF (l_status_changed = true) THEN
2677: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'l_status_changed = Y');
2678: ELSE
2679: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'l_status_changed = N');
2680: END IF;
2681: IF (l_due_date_changed = true) THEN
2682: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'l_due_date_changed = Y');
2683: ELSE
2678: ELSE
2679: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'l_status_changed = N');
2680: END IF;
2681: IF (l_due_date_changed = true) THEN
2682: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'l_due_date_changed = Y');
2683: ELSE
2684: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'l_due_date_changed = N');
2685: END IF;
2686: END IF;
2680: END IF;
2681: IF (l_due_date_changed = true) THEN
2682: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'l_due_date_changed = Y');
2683: ELSE
2684: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'l_due_date_changed = N');
2685: END IF;
2686: END IF;
2687: Update_SR_Unit_Effectivity(p_sr_ue_id => p_sr_ue_id,
2688: p_due_date_flag => l_due_date_changed,
2702: p_new_due_date => l_new_due_date);
2703: END IF;
2704: ELSE
2705: -- No Changes to Update
2706: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2707: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'No change to instance, due date or status');
2708: END IF;
2709: END IF;
2710:
2703: END IF;
2704: ELSE
2705: -- No Changes to Update
2706: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2707: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'No change to instance, due date or status');
2708: END IF;
2709: END IF;
2710:
2711: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2707: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'No change to instance, due date or status');
2708: END IF;
2709: END IF;
2710:
2711: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2712: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
2713: END IF;
2714:
2715: -- Call this API to Sync up SR summary updates with Workorder Description
2708: END IF;
2709: END IF;
2710:
2711: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2712: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
2713: END IF;
2714:
2715: -- Call this API to Sync up SR summary updates with Workorder Description
2716: Handle_Summary_Update(
2737: l_temp_count NUMBER := 0;
2738: l_new_instance_id NUMBER := CS_SERVICEREQUEST_PVT.USER_HOOKS_REC.customer_product_id;
2739:
2740: BEGIN
2741: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2742: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
2743: END IF;
2744: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2745: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'p_old_instance_id = ' || p_old_instance_id || ', l_new_instance_id = ' || l_new_instance_id);
2738: l_new_instance_id NUMBER := CS_SERVICEREQUEST_PVT.USER_HOOKS_REC.customer_product_id;
2739:
2740: BEGIN
2741: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2742: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
2743: END IF;
2744: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2745: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'p_old_instance_id = ' || p_old_instance_id || ', l_new_instance_id = ' || l_new_instance_id);
2746: END IF;
2740: BEGIN
2741: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2742: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
2743: END IF;
2744: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2745: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'p_old_instance_id = ' || p_old_instance_id || ', l_new_instance_id = ' || l_new_instance_id);
2746: END IF;
2747: -- Initialize the Instance Changed Flag to false
2748: x_instance_changed := false;
2741: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2742: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
2743: END IF;
2744: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2745: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'p_old_instance_id = ' || p_old_instance_id || ', l_new_instance_id = ' || l_new_instance_id);
2746: END IF;
2747: -- Initialize the Instance Changed Flag to false
2748: x_instance_changed := false;
2749:
2750: IF (l_new_instance_id IS NULL) THEN
2751: p_x_valid_flag := false;
2752: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_INST_NULL');
2753: FND_MSG_PUB.ADD;
2754: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2755: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
2756: END IF;
2757: ELSIF (l_new_instance_id <> p_old_instance_id) THEN
2758: -- Instance has changed: Validate
2751: p_x_valid_flag := false;
2752: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_INST_NULL');
2753: FND_MSG_PUB.ADD;
2754: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2755: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
2756: END IF;
2757: ELSIF (l_new_instance_id <> p_old_instance_id) THEN
2758: -- Instance has changed: Validate
2759: IF (CS_SERVICEREQUEST_PVT.USER_HOOKS_REC.status_flag = G_SR_CLOSED_FLAG) THEN
2759: IF (CS_SERVICEREQUEST_PVT.USER_HOOKS_REC.status_flag = G_SR_CLOSED_FLAG) THEN
2760: p_x_valid_flag := false;
2761: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_INST_CHG_CLOSED');
2762: FND_MSG_PUB.ADD;
2763: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2764: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
2765: END IF;
2766: ELSIF (CS_SERVICEREQUEST_PVT.user_hooks_rec.status_id = G_SR_PLANNED_STATUS_ID) THEN
2767: p_x_valid_flag := false;
2760: p_x_valid_flag := false;
2761: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_INST_CHG_CLOSED');
2762: FND_MSG_PUB.ADD;
2763: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2764: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
2765: END IF;
2766: ELSIF (CS_SERVICEREQUEST_PVT.user_hooks_rec.status_id = G_SR_PLANNED_STATUS_ID) THEN
2767: p_x_valid_flag := false;
2768: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_INST_CHG_PLANNED');
2766: ELSIF (CS_SERVICEREQUEST_PVT.user_hooks_rec.status_id = G_SR_PLANNED_STATUS_ID) THEN
2767: p_x_valid_flag := false;
2768: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_INST_CHG_PLANNED');
2769: FND_MSG_PUB.ADD;
2770: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2771: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
2772: END IF;
2773: ELSE
2774: OPEN get_rel_count_csr;
2767: p_x_valid_flag := false;
2768: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_INST_CHG_PLANNED');
2769: FND_MSG_PUB.ADD;
2770: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2771: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
2772: END IF;
2773: ELSE
2774: OPEN get_rel_count_csr;
2775: FETCH get_rel_count_csr INTO l_temp_count;
2777: IF (l_temp_count > 0) THEN
2778: p_x_valid_flag := false;
2779: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_INST_CHG_HAS_MRS');
2780: FND_MSG_PUB.ADD;
2781: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2782: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
2783: END IF;
2784: ELSE
2785: -- All validations passed
2778: p_x_valid_flag := false;
2779: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_INST_CHG_HAS_MRS');
2780: FND_MSG_PUB.ADD;
2781: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2782: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
2783: END IF;
2784: ELSE
2785: -- All validations passed
2786: x_instance_changed := true;
2783: END IF;
2784: ELSE
2785: -- All validations passed
2786: x_instance_changed := true;
2787: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2788: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY, 'Instance has changed from ' || p_old_instance_id || ' to ' || l_new_instance_id || '. All instance validations have succeeded.');
2789: END IF;
2790: END IF; -- Status Check
2791: END IF; -- Old <> New Check
2784: ELSE
2785: -- All validations passed
2786: x_instance_changed := true;
2787: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2788: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY, 'Instance has changed from ' || p_old_instance_id || ' to ' || l_new_instance_id || '. All instance validations have succeeded.');
2789: END IF;
2790: END IF; -- Status Check
2791: END IF; -- Old <> New Check
2792: END IF; -- New Instance Null Check
2790: END IF; -- Status Check
2791: END IF; -- Old <> New Check
2792: END IF; -- New Instance Null Check
2793:
2794: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2795: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
2796: END IF;
2797:
2798: END Handle_Instance_Change;
2791: END IF; -- Old <> New Check
2792: END IF; -- New Instance Null Check
2793:
2794: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2795: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
2796: END IF;
2797:
2798: END Handle_Instance_Change;
2799:
2821: l_dummy VARCHAR2(1);
2822: l_assigned BOOLEAN;
2823:
2824: BEGIN
2825: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2826: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
2827: END IF;
2828: -- Initialize the Status Changed Flag to false
2829: x_status_changed := false;
2822: l_assigned BOOLEAN;
2823:
2824: BEGIN
2825: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2826: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
2827: END IF;
2828: -- Initialize the Status Changed Flag to false
2829: x_status_changed := false;
2830:
2838: -- UE Neither accomplished nor terminated
2839: p_x_valid_flag := false;
2840: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_TASK_NOT_ACC');
2841: FND_MSG_PUB.ADD;
2842: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2843: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
2844: END IF;
2845: ELSE
2846: -- Unit Effectivities already accomplished: No impact on UMP, Allow the SR to be closed.
2839: p_x_valid_flag := false;
2840: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_TASK_NOT_ACC');
2841: FND_MSG_PUB.ADD;
2842: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2843: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
2844: END IF;
2845: ELSE
2846: -- Unit Effectivities already accomplished: No impact on UMP, Allow the SR to be closed.
2847: null;
2855: -- SR has associated MRs, but the SR UE has not been assigned to any visit.
2856: p_x_valid_flag := false;
2857: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_MRS_NOT_ACC');
2858: FND_MSG_PUB.ADD;
2859: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2860: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
2861: END IF;
2862: ELSE
2863: -- No associated MRs
2856: p_x_valid_flag := false;
2857: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_MRS_NOT_ACC');
2858: FND_MSG_PUB.ADD;
2859: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2860: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
2861: END IF;
2862: ELSE
2863: -- No associated MRs
2864: IF ((p_old_ue_status IS NULL) OR (p_old_ue_status <> G_UMP_SR_CLOSED_STATUS)) THEN
2879: -- Attempting to open an SR whose UE is already SR_CLOSED.
2880: p_x_valid_flag := false;
2881: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_UE_SR_CLSD');
2882: FND_MSG_PUB.ADD;
2883: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2884: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
2885: END IF;
2886: ELSE
2887: OPEN get_tasks_for_ue_csr;
2880: p_x_valid_flag := false;
2881: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_UE_SR_CLSD');
2882: FND_MSG_PUB.ADD;
2883: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2884: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
2885: END IF;
2886: ELSE
2887: OPEN get_tasks_for_ue_csr;
2888: FETCH get_tasks_for_ue_csr INTO l_dummy;
2897: -- SR is being moved to Planned status without assigning SR UE to any visit
2898: p_x_valid_flag := false;
2899: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_NOT_ASSND');
2900: FND_MSG_PUB.ADD;
2901: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2902: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
2903: END IF;
2904: END IF; -- Not assigned
2905: ELSE
2898: p_x_valid_flag := false;
2899: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_NOT_ASSND');
2900: FND_MSG_PUB.ADD;
2901: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2902: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
2903: END IF;
2904: END IF; -- Not assigned
2905: ELSE
2906: IF (l_assigned = true) THEN
2907: -- SR is being moved to a not-planned status while SR UE has already been assigned to a visit
2908: p_x_valid_flag := false;
2909: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_ALREADY_ASSND');
2910: FND_MSG_PUB.ADD;
2911: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2912: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
2913: END IF;
2914: END IF; -- Already Assigned
2915: END IF; -- SR is in Planned Status or Not
2908: p_x_valid_flag := false;
2909: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_ALREADY_ASSND');
2910: FND_MSG_PUB.ADD;
2911: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2912: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
2913: END IF;
2914: END IF; -- Already Assigned
2915: END IF; -- SR is in Planned Status or Not
2916: END IF; -- Old UE Status SR-CLOSED or not
2915: END IF; -- SR is in Planned Status or Not
2916: END IF; -- Old UE Status SR-CLOSED or not
2917: END IF; -- If SR is Closed or not
2918:
2919: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2920: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
2921: END IF;
2922:
2923: END Handle_Status_Change;
2916: END IF; -- Old UE Status SR-CLOSED or not
2917: END IF; -- If SR is Closed or not
2918:
2919: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2920: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
2921: END IF;
2922:
2923: END Handle_Status_Change;
2924:
3001: BEGIN
3002:
3003: l_count := 1;
3004:
3005: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3006: FND_LOG.STRING(
3007: FND_LOG.LEVEL_STATEMENT,
3008: L_DEBUG_KEY,
3009: 'Entering the procedure Handle_Summary_Update ..'
3002:
3003: l_count := 1;
3004:
3005: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3006: FND_LOG.STRING(
3007: FND_LOG.LEVEL_STATEMENT,
3008: L_DEBUG_KEY,
3009: 'Entering the procedure Handle_Summary_Update ..'
3010: );
3003: l_count := 1;
3004:
3005: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3006: FND_LOG.STRING(
3007: FND_LOG.LEVEL_STATEMENT,
3008: L_DEBUG_KEY,
3009: 'Entering the procedure Handle_Summary_Update ..'
3010: );
3011: END IF;
3021: OPEN c_get_SR_details(p_sr_ue_id);
3022: FETCH c_get_SR_details INTO l_sr_summary, l_sr_type;
3023: CLOSE c_get_SR_details;
3024: --l_sr_summary := CS_SERVICEREQUEST_PVT.USER_HOOKS_REC.summary;
3025: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3026:
3027: FND_LOG.STRING(
3028: FND_LOG.LEVEL_STATEMENT,
3029: L_DEBUG_KEY,
3023: CLOSE c_get_SR_details;
3024: --l_sr_summary := CS_SERVICEREQUEST_PVT.USER_HOOKS_REC.summary;
3025: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3026:
3027: FND_LOG.STRING(
3028: FND_LOG.LEVEL_STATEMENT,
3029: L_DEBUG_KEY,
3030: 'l_sr_summary -->'||l_sr_summary
3031: );
3024: --l_sr_summary := CS_SERVICEREQUEST_PVT.USER_HOOKS_REC.summary;
3025: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3026:
3027: FND_LOG.STRING(
3028: FND_LOG.LEVEL_STATEMENT,
3029: L_DEBUG_KEY,
3030: 'l_sr_summary -->'||l_sr_summary
3031: );
3032: FND_LOG.STRING(
3028: FND_LOG.LEVEL_STATEMENT,
3029: L_DEBUG_KEY,
3030: 'l_sr_summary -->'||l_sr_summary
3031: );
3032: FND_LOG.STRING(
3033: FND_LOG.LEVEL_STATEMENT,
3034: L_DEBUG_KEY,
3035: 'l_sr_type -->'||l_sr_type
3036: );
3029: L_DEBUG_KEY,
3030: 'l_sr_summary -->'||l_sr_summary
3031: );
3032: FND_LOG.STRING(
3033: FND_LOG.LEVEL_STATEMENT,
3034: L_DEBUG_KEY,
3035: 'l_sr_type -->'||l_sr_type
3036: );
3037:
3044: -- Get the workorders for SR if it exists.
3045: FOR ue_wo_rec IN c_get_ue_wo(p_sr_ue_id)
3046: LOOP
3047: IF ue_wo_rec.description <> l_wo_description THEN
3048: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3049: FND_LOG.STRING(
3050: FND_LOG.LEVEL_STATEMENT,
3051: L_DEBUG_KEY,
3052: 'ue_wo_rec.workorder_id -->'||ue_wo_rec.workorder_id
3045: FOR ue_wo_rec IN c_get_ue_wo(p_sr_ue_id)
3046: LOOP
3047: IF ue_wo_rec.description <> l_wo_description THEN
3048: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3049: FND_LOG.STRING(
3050: FND_LOG.LEVEL_STATEMENT,
3051: L_DEBUG_KEY,
3052: 'ue_wo_rec.workorder_id -->'||ue_wo_rec.workorder_id
3053: );
3046: LOOP
3047: IF ue_wo_rec.description <> l_wo_description THEN
3048: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3049: FND_LOG.STRING(
3050: FND_LOG.LEVEL_STATEMENT,
3051: L_DEBUG_KEY,
3052: 'ue_wo_rec.workorder_id -->'||ue_wo_rec.workorder_id
3053: );
3054: END IF;
3081:
3082: IF l_return_status <> FND_API.G_RET_STS_SUCCESS
3083: THEN
3084: RAISE FND_API.G_EXC_ERROR;
3085: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3086: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
3087: END IF;
3088: END IF;
3089: END IF;
3082: IF l_return_status <> FND_API.G_RET_STS_SUCCESS
3083: THEN
3084: RAISE FND_API.G_EXC_ERROR;
3085: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3086: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
3087: END IF;
3088: END IF;
3089: END IF;
3090:
3087: END IF;
3088: END IF;
3089: END IF;
3090:
3091: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3092: FND_LOG.STRING(
3093: FND_LOG.LEVEL_STATEMENT,
3094: L_DEBUG_KEY,
3095: 'Exiting the procedure Handle_Summary_Update ..'
3088: END IF;
3089: END IF;
3090:
3091: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3092: FND_LOG.STRING(
3093: FND_LOG.LEVEL_STATEMENT,
3094: L_DEBUG_KEY,
3095: 'Exiting the procedure Handle_Summary_Update ..'
3096: );
3089: END IF;
3090:
3091: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3092: FND_LOG.STRING(
3093: FND_LOG.LEVEL_STATEMENT,
3094: L_DEBUG_KEY,
3095: 'Exiting the procedure Handle_Summary_Update ..'
3096: );
3097: END IF;