[Home] [Help]
269: BEGIN
270:
271: SAVEPOINT Create_SR_Unit_Effectivity_Pvt;
272:
273: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
274: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Create_SR_Unit_Effectivity Procedure');
275: END IF;
276:
277: -- Initialize message list
270:
271: SAVEPOINT Create_SR_Unit_Effectivity_Pvt;
272:
273: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
274: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Create_SR_Unit_Effectivity Procedure');
275: END IF;
276:
277: -- Initialize message list
278: --AMSRINIV. Bug 5470730. Removing message initialization.
291: l_customer_product_id := cs_servicerequest_pvt.user_hooks_rec.customer_product_id;
292: l_status_id := cs_servicerequest_pvt.user_hooks_rec.status_id;
293: l_exp_resolution_date := cs_servicerequest_pvt.user_hooks_rec.exp_resolution_date;
294:
295: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
296: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' input values:' );
297: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' Request id::' || cs_servicerequest_pvt.user_hooks_rec.request_id);
298: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'Status flag:' || cs_servicerequest_pvt.user_hooks_rec.status_flag);
299: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'old_type_cmro_flag:' || cs_servicerequest_pvt.user_hooks_rec.old_type_cmro_flag);
292: l_status_id := cs_servicerequest_pvt.user_hooks_rec.status_id;
293: l_exp_resolution_date := cs_servicerequest_pvt.user_hooks_rec.exp_resolution_date;
294:
295: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
296: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' input values:' );
297: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' Request id::' || cs_servicerequest_pvt.user_hooks_rec.request_id);
298: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'Status flag:' || cs_servicerequest_pvt.user_hooks_rec.status_flag);
299: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'old_type_cmro_flag:' || cs_servicerequest_pvt.user_hooks_rec.old_type_cmro_flag);
300: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'new_type_cmro_flag:' || cs_servicerequest_pvt.user_hooks_rec.new_type_cmro_flag);
293: l_exp_resolution_date := cs_servicerequest_pvt.user_hooks_rec.exp_resolution_date;
294:
295: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
296: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' input values:' );
297: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' Request id::' || cs_servicerequest_pvt.user_hooks_rec.request_id);
298: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'Status flag:' || cs_servicerequest_pvt.user_hooks_rec.status_flag);
299: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'old_type_cmro_flag:' || cs_servicerequest_pvt.user_hooks_rec.old_type_cmro_flag);
300: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'new_type_cmro_flag:' || cs_servicerequest_pvt.user_hooks_rec.new_type_cmro_flag);
301: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'customer_product_id:' || cs_servicerequest_pvt.user_hooks_rec.customer_product_id);
294:
295: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
296: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' input values:' );
297: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' Request id::' || cs_servicerequest_pvt.user_hooks_rec.request_id);
298: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'Status flag:' || cs_servicerequest_pvt.user_hooks_rec.status_flag);
299: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'old_type_cmro_flag:' || cs_servicerequest_pvt.user_hooks_rec.old_type_cmro_flag);
300: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'new_type_cmro_flag:' || cs_servicerequest_pvt.user_hooks_rec.new_type_cmro_flag);
301: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'customer_product_id:' || cs_servicerequest_pvt.user_hooks_rec.customer_product_id);
302: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'status id:' || cs_servicerequest_pvt.user_hooks_rec.status_id);
295: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
296: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' input values:' );
297: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' Request id::' || cs_servicerequest_pvt.user_hooks_rec.request_id);
298: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'Status flag:' || cs_servicerequest_pvt.user_hooks_rec.status_flag);
299: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'old_type_cmro_flag:' || cs_servicerequest_pvt.user_hooks_rec.old_type_cmro_flag);
300: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'new_type_cmro_flag:' || cs_servicerequest_pvt.user_hooks_rec.new_type_cmro_flag);
301: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'customer_product_id:' || cs_servicerequest_pvt.user_hooks_rec.customer_product_id);
302: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'status id:' || cs_servicerequest_pvt.user_hooks_rec.status_id);
303: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'exp_resolution_date:' || cs_servicerequest_pvt.user_hooks_rec.exp_resolution_date);
296: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' input values:' );
297: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' Request id::' || cs_servicerequest_pvt.user_hooks_rec.request_id);
298: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'Status flag:' || cs_servicerequest_pvt.user_hooks_rec.status_flag);
299: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'old_type_cmro_flag:' || cs_servicerequest_pvt.user_hooks_rec.old_type_cmro_flag);
300: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'new_type_cmro_flag:' || cs_servicerequest_pvt.user_hooks_rec.new_type_cmro_flag);
301: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'customer_product_id:' || cs_servicerequest_pvt.user_hooks_rec.customer_product_id);
302: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'status id:' || cs_servicerequest_pvt.user_hooks_rec.status_id);
303: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'exp_resolution_date:' || cs_servicerequest_pvt.user_hooks_rec.exp_resolution_date);
304: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'incident_occurred_date:' || cs_servicerequest_pvt.user_hooks_rec.incident_occurred_date);
297: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' Request id::' || cs_servicerequest_pvt.user_hooks_rec.request_id);
298: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'Status flag:' || cs_servicerequest_pvt.user_hooks_rec.status_flag);
299: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'old_type_cmro_flag:' || cs_servicerequest_pvt.user_hooks_rec.old_type_cmro_flag);
300: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'new_type_cmro_flag:' || cs_servicerequest_pvt.user_hooks_rec.new_type_cmro_flag);
301: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'customer_product_id:' || cs_servicerequest_pvt.user_hooks_rec.customer_product_id);
302: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'status id:' || cs_servicerequest_pvt.user_hooks_rec.status_id);
303: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'exp_resolution_date:' || cs_servicerequest_pvt.user_hooks_rec.exp_resolution_date);
304: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'incident_occurred_date:' || cs_servicerequest_pvt.user_hooks_rec.incident_occurred_date);
305: END IF;
298: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'Status flag:' || cs_servicerequest_pvt.user_hooks_rec.status_flag);
299: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'old_type_cmro_flag:' || cs_servicerequest_pvt.user_hooks_rec.old_type_cmro_flag);
300: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'new_type_cmro_flag:' || cs_servicerequest_pvt.user_hooks_rec.new_type_cmro_flag);
301: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'customer_product_id:' || cs_servicerequest_pvt.user_hooks_rec.customer_product_id);
302: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'status id:' || cs_servicerequest_pvt.user_hooks_rec.status_id);
303: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'exp_resolution_date:' || cs_servicerequest_pvt.user_hooks_rec.exp_resolution_date);
304: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'incident_occurred_date:' || cs_servicerequest_pvt.user_hooks_rec.incident_occurred_date);
305: END IF;
306:
299: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'old_type_cmro_flag:' || cs_servicerequest_pvt.user_hooks_rec.old_type_cmro_flag);
300: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'new_type_cmro_flag:' || cs_servicerequest_pvt.user_hooks_rec.new_type_cmro_flag);
301: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'customer_product_id:' || cs_servicerequest_pvt.user_hooks_rec.customer_product_id);
302: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'status id:' || cs_servicerequest_pvt.user_hooks_rec.status_id);
303: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'exp_resolution_date:' || cs_servicerequest_pvt.user_hooks_rec.exp_resolution_date);
304: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'incident_occurred_date:' || cs_servicerequest_pvt.user_hooks_rec.incident_occurred_date);
305: END IF;
306:
307:
300: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'new_type_cmro_flag:' || cs_servicerequest_pvt.user_hooks_rec.new_type_cmro_flag);
301: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'customer_product_id:' || cs_servicerequest_pvt.user_hooks_rec.customer_product_id);
302: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'status id:' || cs_servicerequest_pvt.user_hooks_rec.status_id);
303: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'exp_resolution_date:' || cs_servicerequest_pvt.user_hooks_rec.exp_resolution_date);
304: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'incident_occurred_date:' || cs_servicerequest_pvt.user_hooks_rec.incident_occurred_date);
305: END IF;
306:
307:
308: --******************************
337: x_return_status := FND_API.G_RET_STS_SUCCESS;
338: RETURN;
339: END IF;
340:
341: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
342: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' Processing for CMRO type of SR');
343: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' BEFORE Calling AHL_UTIL_PKG.Get_Appln_Usage' );
344: END IF;
345:
338: RETURN;
339: END IF;
340:
341: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
342: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' Processing for CMRO type of SR');
343: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' BEFORE Calling AHL_UTIL_PKG.Get_Appln_Usage' );
344: END IF;
345:
346: --Call the procedure AHL_UTIL_PKG.Get_Appln_Usage
339: END IF;
340:
341: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
342: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' Processing for CMRO type of SR');
343: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' BEFORE Calling AHL_UTIL_PKG.Get_Appln_Usage' );
344: END IF;
345:
346: --Call the procedure AHL_UTIL_PKG.Get_Appln_Usage
347: AHL_UTIL_PKG.Get_Appln_Usage
349: x_appln_code => l_appln_code,
350: x_return_status => l_return_status
351: );
352:
353: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
354: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' After Calling AHL_UTIL_PKG.Get_Appln_Usage successfully' );
355: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' l_appln_code: ' || l_appln_code);
356: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' l_return_status: ' || l_return_status);
357: END IF;
350: x_return_status => l_return_status
351: );
352:
353: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
354: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' After Calling AHL_UTIL_PKG.Get_Appln_Usage successfully' );
355: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' l_appln_code: ' || l_appln_code);
356: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' l_return_status: ' || l_return_status);
357: END IF;
358:
351: );
352:
353: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
354: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' After Calling AHL_UTIL_PKG.Get_Appln_Usage successfully' );
355: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' l_appln_code: ' || l_appln_code);
356: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' l_return_status: ' || l_return_status);
357: END IF;
358:
359:
352:
353: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
354: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' After Calling AHL_UTIL_PKG.Get_Appln_Usage successfully' );
355: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' l_appln_code: ' || l_appln_code);
356: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' l_return_status: ' || l_return_status);
357: END IF;
358:
359:
360: --Set the return status to an error and raise an error message if the return status is an error.
445: l_uc_hdr_id := null;
446: END IF;
447: END IF;
448:
449: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
450: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' BEFORE Calling TABLE HANDLER AHL_UNIT_EFFECTIVITIES_PKG.Insert_Row' );
451: END IF;
452:
453:
446: END IF;
447: END IF;
448:
449: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
450: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' BEFORE Calling TABLE HANDLER AHL_UNIT_EFFECTIVITIES_PKG.Insert_Row' );
451: END IF;
452:
453:
454: -- bachandr Bug # 13916897
530: X_FLEET_HEADER_ID => AHL_UMP_UTIL_PKG.Get_Associated_Fleet(l_uc_hdr_id,nvl(l_exp_resolution_date,l_occurred_date),null)
531: -- BACHANDR - NR project end
532: );
533:
534: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
535: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' AFTER Calling TABLE HANDLER AHL_UNIT_EFFECTIVITIES_PKG.Insert_Row' );
536: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' Unit Effectivity ID:' || l_unit_effectivity_id);
537: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' Return Status:' || x_return_status);
538: END IF;
531: -- BACHANDR - NR project end
532: );
533:
534: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
535: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' AFTER Calling TABLE HANDLER AHL_UNIT_EFFECTIVITIES_PKG.Insert_Row' );
536: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' Unit Effectivity ID:' || l_unit_effectivity_id);
537: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' Return Status:' || x_return_status);
538: END IF;
539:
532: );
533:
534: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
535: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' AFTER Calling TABLE HANDLER AHL_UNIT_EFFECTIVITIES_PKG.Insert_Row' );
536: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' Unit Effectivity ID:' || l_unit_effectivity_id);
537: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' Return Status:' || x_return_status);
538: END IF;
539:
540: -- End Processing
533:
534: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
535: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' AFTER Calling TABLE HANDLER AHL_UNIT_EFFECTIVITIES_PKG.Insert_Row' );
536: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' Unit Effectivity ID:' || l_unit_effectivity_id);
537: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' Return Status:' || x_return_status);
538: END IF;
539:
540: -- End Processing
541:
538: END IF;
539:
540: -- End Processing
541:
542: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
543: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Create_SR_Unit_Effectivity Procedure');
544: END IF;
545:
546: EXCEPTION
539:
540: -- End Processing
541:
542: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
543: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Create_SR_Unit_Effectivity Procedure');
544: END IF;
545:
546: EXCEPTION
547: WHEN FND_API.G_EXC_ERROR THEN
546: EXCEPTION
547: WHEN FND_API.G_EXC_ERROR THEN
548: ROLLBACK TO Create_SR_Unit_Effectivity_Pvt;
549: x_return_status := FND_API.G_RET_STS_ERROR;
550: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
551: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' Error: Return Status:' || x_return_status);
552: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' Error: Msg Count:' || fnd_msg_pub.count_msg);
553: END IF;
554:
547: WHEN FND_API.G_EXC_ERROR THEN
548: ROLLBACK TO Create_SR_Unit_Effectivity_Pvt;
549: x_return_status := FND_API.G_RET_STS_ERROR;
550: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
551: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' Error: Return Status:' || x_return_status);
552: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' Error: Msg Count:' || fnd_msg_pub.count_msg);
553: END IF;
554:
555: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
548: ROLLBACK TO Create_SR_Unit_Effectivity_Pvt;
549: x_return_status := FND_API.G_RET_STS_ERROR;
550: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
551: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' Error: Return Status:' || x_return_status);
552: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' Error: Msg Count:' || fnd_msg_pub.count_msg);
553: END IF;
554:
555: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
556: ROLLBACK TO Create_SR_Unit_Effectivity_Pvt;
554:
555: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
556: ROLLBACK TO Create_SR_Unit_Effectivity_Pvt;
557: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
558: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
559: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' UnExpError: Return Status:' || x_return_status);
560: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' UnExpError: Msg Count:' || fnd_msg_pub.count_msg);
561: END IF;
562:
555: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
556: ROLLBACK TO Create_SR_Unit_Effectivity_Pvt;
557: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
558: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
559: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' UnExpError: Return Status:' || x_return_status);
560: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' UnExpError: Msg Count:' || fnd_msg_pub.count_msg);
561: END IF;
562:
563: WHEN OTHERS THEN
556: ROLLBACK TO Create_SR_Unit_Effectivity_Pvt;
557: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
558: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
559: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' UnExpError: Return Status:' || x_return_status);
560: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' UnExpError: Msg Count:' || fnd_msg_pub.count_msg);
561: END IF;
562:
563: WHEN OTHERS THEN
564: ROLLBACK TO Create_SR_Unit_Effectivity_Pvt;
604: BEGIN
605:
606: SAVEPOINT Process_SR_Updates_Pvt;
607:
608: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
609: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
610: END IF;
611:
612: -- JR: Added the following log on 10/21/2003 to help in debugging
605:
606: SAVEPOINT Process_SR_Updates_Pvt;
607:
608: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
609: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
610: END IF;
611:
612: -- JR: Added the following log on 10/21/2003 to help in debugging
613: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
609: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
610: END IF;
611:
612: -- JR: Added the following log on 10/21/2003 to help in debugging
613: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
614: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'Relevant User Hook Record (input) Values: ' );
615: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'old_type_cmro_flag = ' || CS_SERVICEREQUEST_PVT.USER_HOOKS_REC.old_type_cmro_flag ||
616: ', new_type_cmro_flag = ' || CS_SERVICEREQUEST_PVT.USER_HOOKS_REC.new_type_cmro_flag ||
617: ', request_id = ' || CS_SERVICEREQUEST_PVT.user_hooks_rec.request_id ||
610: END IF;
611:
612: -- JR: Added the following log on 10/21/2003 to help in debugging
613: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
614: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'Relevant User Hook Record (input) Values: ' );
615: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'old_type_cmro_flag = ' || CS_SERVICEREQUEST_PVT.USER_HOOKS_REC.old_type_cmro_flag ||
616: ', new_type_cmro_flag = ' || CS_SERVICEREQUEST_PVT.USER_HOOKS_REC.new_type_cmro_flag ||
617: ', request_id = ' || CS_SERVICEREQUEST_PVT.user_hooks_rec.request_id ||
618: ', status_id = ' || CS_SERVICEREQUEST_PVT.user_hooks_rec.status_id ||
611:
612: -- JR: Added the following log on 10/21/2003 to help in debugging
613: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
614: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'Relevant User Hook Record (input) Values: ' );
615: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , 'old_type_cmro_flag = ' || CS_SERVICEREQUEST_PVT.USER_HOOKS_REC.old_type_cmro_flag ||
616: ', new_type_cmro_flag = ' || CS_SERVICEREQUEST_PVT.USER_HOOKS_REC.new_type_cmro_flag ||
617: ', request_id = ' || CS_SERVICEREQUEST_PVT.user_hooks_rec.request_id ||
618: ', status_id = ' || CS_SERVICEREQUEST_PVT.user_hooks_rec.status_id ||
619: ', status_flag = ' || CS_SERVICEREQUEST_PVT.user_hooks_rec.status_flag ||
630:
631: -- Begin Processing
632: IF NOT (NVL(CS_SERVICEREQUEST_PVT.USER_HOOKS_REC.old_type_cmro_flag, G_NO_FLAG) = G_YES_FLAG OR
633: NVL(CS_SERVICEREQUEST_PVT.USER_HOOKS_REC.new_type_cmro_flag, G_NO_FLAG)= G_YES_FLAG) THEN
634: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
635: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Not a CMRO Type Service Request.');
636: END IF;
637: -- Not a CMRO Type Service Request
638: -- Just return 'SUCCESS' without doing any processing.
631: -- Begin Processing
632: IF NOT (NVL(CS_SERVICEREQUEST_PVT.USER_HOOKS_REC.old_type_cmro_flag, G_NO_FLAG) = G_YES_FLAG OR
633: NVL(CS_SERVICEREQUEST_PVT.USER_HOOKS_REC.new_type_cmro_flag, G_NO_FLAG)= G_YES_FLAG) THEN
634: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
635: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Not a CMRO Type Service Request.');
636: END IF;
637: -- Not a CMRO Type Service Request
638: -- Just return 'SUCCESS' without doing any processing.
639: l_skip_processing := true;
637: -- Not a CMRO Type Service Request
638: -- Just return 'SUCCESS' without doing any processing.
639: l_skip_processing := true;
640: ELSE
641: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
642: 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 ||
643: ', new_type_cmro_flag = ' || CS_SERVICEREQUEST_PVT.USER_HOOKS_REC.new_type_cmro_flag);
644: END IF;
645: END IF;
638: -- Just return 'SUCCESS' without doing any processing.
639: l_skip_processing := true;
640: ELSE
641: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
642: 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 ||
643: ', new_type_cmro_flag = ' || CS_SERVICEREQUEST_PVT.USER_HOOKS_REC.new_type_cmro_flag);
644: END IF;
645: END IF;
646:
652:
653: IF(x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
654: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_APP_USAGE_NOT_SET');
655: FND_MSG_PUB.ADD;
656: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
657: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
658: END IF;
659: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
660: ELSIF(l_appln_code IS NULL OR (l_appln_code <> G_APP_MODULE)) THEN
653: IF(x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
654: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_APP_USAGE_NOT_SET');
655: FND_MSG_PUB.ADD;
656: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
657: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
658: END IF;
659: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
660: ELSIF(l_appln_code IS NULL OR (l_appln_code <> G_APP_MODULE)) THEN
661: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_APP_USAGE_INVALID');
659: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
660: ELSIF(l_appln_code IS NULL OR (l_appln_code <> G_APP_MODULE)) THEN
661: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_APP_USAGE_INVALID');
662: FND_MSG_PUB.ADD;
663: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
664: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
665: END IF;
666: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
667: ELSE
660: ELSIF(l_appln_code IS NULL OR (l_appln_code <> G_APP_MODULE)) THEN
661: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_APP_USAGE_INVALID');
662: FND_MSG_PUB.ADD;
663: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
664: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
665: END IF;
666: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
667: ELSE
668: IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
664: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
665: END IF;
666: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
667: ELSE
668: IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
669: FND_LOG.STRING(FND_LOG.LEVEL_EVENT, L_DEBUG_KEY, 'Application Usage Profile Option Validated');
670: END IF;
671: END IF; -- Appln_Usage Valid
672: END IF; -- new_type_cmro_flag = 'Y'
665: END IF;
666: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
667: ELSE
668: IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
669: FND_LOG.STRING(FND_LOG.LEVEL_EVENT, L_DEBUG_KEY, 'Application Usage Profile Option Validated');
670: END IF;
671: END IF; -- Appln_Usage Valid
672: END IF; -- new_type_cmro_flag = 'Y'
673:
686: Handle_Attribute_Changes(p_sr_ue_id => l_sr_ue_id);
687: END IF; -- Type change or other change
688: END IF; -- If l_skip_processing = false
689:
690: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
691: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
692: END IF;
693:
694: EXCEPTION
687: END IF; -- Type change or other change
688: END IF; -- If l_skip_processing = false
689:
690: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
691: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
692: END IF;
693:
694: EXCEPTION
695:
782: BEGIN
783: -- Standard start of API savepoint
784: SAVEPOINT Process_SR_MR_Associations_pvt;
785:
786: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
787: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
788: END IF;
789:
790: -- Standard call to check for call compatibility
783: -- Standard start of API savepoint
784: SAVEPOINT Process_SR_MR_Associations_pvt;
785:
786: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
787: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
788: END IF;
789:
790: -- Standard call to check for call compatibility
791: IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
800: -- Initialize API return status to success
801: x_return_status := FND_API.G_RET_STS_SUCCESS;
802:
803: -- Begin Processing
804: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
805: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Beginning Processing with p_request_id = ' || p_request_id ||
806: ', p_object_version_number = ' || p_object_version_number ||
807: ' and p_x_sr_mr_association_tbl.COUNT = ' || p_x_sr_mr_association_tbl.COUNT);
808: END IF;
801: x_return_status := FND_API.G_RET_STS_SUCCESS;
802:
803: -- Begin Processing
804: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
805: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Beginning Processing with p_request_id = ' || p_request_id ||
806: ', p_object_version_number = ' || p_object_version_number ||
807: ' and p_x_sr_mr_association_tbl.COUNT = ' || p_x_sr_mr_association_tbl.COUNT);
808: END IF;
809: -- Check if the Application Usage profile option has been set
812:
813: IF(x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
814: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_APP_USAGE_NOT_SET');
815: FND_MSG_PUB.ADD;
816: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
817: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
818: END IF;
819: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
820: ELSIF(l_appln_code IS NULL OR (l_appln_code <> G_APP_MODULE)) THEN
813: IF(x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
814: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_APP_USAGE_NOT_SET');
815: FND_MSG_PUB.ADD;
816: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
817: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
818: END IF;
819: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
820: ELSIF(l_appln_code IS NULL OR (l_appln_code <> G_APP_MODULE)) THEN
821: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_APP_USAGE_INVALID');
819: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
820: ELSIF(l_appln_code IS NULL OR (l_appln_code <> G_APP_MODULE)) THEN
821: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_APP_USAGE_INVALID');
822: FND_MSG_PUB.ADD;
823: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
824: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
825: END IF;
826: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
827: END IF;
820: ELSIF(l_appln_code IS NULL OR (l_appln_code <> G_APP_MODULE)) THEN
821: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_APP_USAGE_INVALID');
822: FND_MSG_PUB.ADD;
823: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
824: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
825: END IF;
826: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
827: END IF;
828: IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
824: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
825: END IF;
826: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
827: END IF;
828: IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
829: FND_LOG.STRING(FND_LOG.LEVEL_EVENT, L_DEBUG_KEY, 'Application Usage Profile Option Validated');
830: END IF;
831:
832: -- Validate the Request
825: END IF;
826: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
827: END IF;
828: IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
829: FND_LOG.STRING(FND_LOG.LEVEL_EVENT, L_DEBUG_KEY, 'Application Usage Profile Option Validated');
830: END IF;
831:
832: -- Validate the Request
833: Validate_Associated_Request(p_x_request_id => l_request_id,
836: x_sr_ue_id => l_sr_ue_id,
837: x_sr_instance_id => l_sr_instance_id,
838: x_sr_exp_resol_date => l_sr_exp_resol_date);
839:
840: IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
841: FND_LOG.STRING(FND_LOG.LEVEL_EVENT, L_DEBUG_KEY, 'Completed Validating the Request');
842: END IF;
843:
844: IF (p_x_sr_mr_association_tbl.COUNT = 0) THEN
837: x_sr_instance_id => l_sr_instance_id,
838: x_sr_exp_resol_date => l_sr_exp_resol_date);
839:
840: IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
841: FND_LOG.STRING(FND_LOG.LEVEL_EVENT, L_DEBUG_KEY, 'Completed Validating the Request');
842: END IF;
843:
844: IF (p_x_sr_mr_association_tbl.COUNT = 0) THEN
845: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_MR_SR_TBL_EMPTY');
843:
844: IF (p_x_sr_mr_association_tbl.COUNT = 0) THEN
845: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_MR_SR_TBL_EMPTY');
846: FND_MSG_PUB.ADD;
847: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
848: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
849: END IF;
850: RAISE FND_API.G_EXC_ERROR;
851: END IF;
844: IF (p_x_sr_mr_association_tbl.COUNT = 0) THEN
845: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_MR_SR_TBL_EMPTY');
846: FND_MSG_PUB.ADD;
847: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
848: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
849: END IF;
850: RAISE FND_API.G_EXC_ERROR;
851: END IF;
852:
880: END IF;
881:
882: -- Standard check of p_commit
883: IF FND_API.TO_BOOLEAN(p_commit) THEN
884: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
885: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'About to Commit.');
886: END IF;
887: COMMIT WORK;
888: END IF;
881:
882: -- Standard check of p_commit
883: IF FND_API.TO_BOOLEAN(p_commit) THEN
884: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
885: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'About to Commit.');
886: END IF;
887: COMMIT WORK;
888: END IF;
889:
893: p_data => x_msg_data,
894: p_encoded => fnd_api.g_false
895: );
896:
897: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
898: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
899: END IF;
900:
901: EXCEPTION
894: p_encoded => fnd_api.g_false
895: );
896:
897: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
898: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
899: END IF;
900:
901: EXCEPTION
902: WHEN FND_API.G_EXC_ERROR THEN
982:
983: L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Validate_Associated_Request';
984:
985: BEGIN
986: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
987: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
988: END IF;
989:
990: IF(p_x_request_id IS NULL AND p_request_number IS NULL) THEN
983: L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Validate_Associated_Request';
984:
985: BEGIN
986: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
987: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
988: END IF;
989:
990: IF(p_x_request_id IS NULL AND p_request_number IS NULL) THEN
991: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_REQ_ID_NUM_NULL');
989:
990: IF(p_x_request_id IS NULL AND p_request_number IS NULL) THEN
991: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_REQ_ID_NUM_NULL');
992: FND_MSG_PUB.ADD;
993: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
994: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
995: END IF;
996: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
997: ELSIF (p_object_version_number IS NULL) THEN
990: IF(p_x_request_id IS NULL AND p_request_number IS NULL) THEN
991: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_REQ_ID_NUM_NULL');
992: FND_MSG_PUB.ADD;
993: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
994: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
995: END IF;
996: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
997: ELSIF (p_object_version_number IS NULL) THEN
998: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_REQ_OVN_NULL');
996: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
997: ELSIF (p_object_version_number IS NULL) THEN
998: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_REQ_OVN_NULL');
999: FND_MSG_PUB.ADD;
1000: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1001: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1002: END IF;
1003: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1004: ELSE
997: ELSIF (p_object_version_number IS NULL) THEN
998: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_REQ_OVN_NULL');
999: FND_MSG_PUB.ADD;
1000: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1001: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1002: END IF;
1003: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1004: ELSE
1005: OPEN get_request_dtls_csr(p_request_id => p_x_request_id,
1014: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_REQ_OVN_INVALID');
1015: FND_MESSAGE.Set_Token('ID', p_x_request_id);
1016: FND_MESSAGE.Set_Token('OVN', p_object_version_number);
1017: FND_MSG_PUB.ADD;
1018: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1019: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1020: END IF;
1021: CLOSE get_request_dtls_csr;
1022: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1015: FND_MESSAGE.Set_Token('ID', p_x_request_id);
1016: FND_MESSAGE.Set_Token('OVN', p_object_version_number);
1017: FND_MSG_PUB.ADD;
1018: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1019: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1020: END IF;
1021: CLOSE get_request_dtls_csr;
1022: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1023: END IF;
1026:
1027: IF(l_closed_flag = G_YES_FLAG) THEN
1028: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_REQ_CLOSED');
1029: FND_MSG_PUB.ADD;
1030: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1031: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1032: END IF;
1033: RAISE FND_API.G_EXC_ERROR;
1034: END IF;
1027: IF(l_closed_flag = G_YES_FLAG) THEN
1028: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_REQ_CLOSED');
1029: FND_MSG_PUB.ADD;
1030: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1031: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1032: END IF;
1033: RAISE FND_API.G_EXC_ERROR;
1034: END IF;
1035: /*
1041: -- JR: Modified on 10/29/2003 (Using Status Id instead of Status Code)
1042: IF (l_sr_status_id = G_SR_PLANNED_STATUS_ID) THEN
1043: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_REQ_PLANNED');
1044: FND_MSG_PUB.ADD;
1045: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1046: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1047: END IF;
1048: RAISE FND_API.G_EXC_ERROR;
1049: END IF;
1042: IF (l_sr_status_id = G_SR_PLANNED_STATUS_ID) THEN
1043: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_REQ_PLANNED');
1044: FND_MSG_PUB.ADD;
1045: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1046: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1047: END IF;
1048: RAISE FND_API.G_EXC_ERROR;
1049: END IF;
1050: */
1053: IF (get_ue_dtls_csr%NOTFOUND) THEN
1054: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_NO_UE_FOR_REQ');
1055: FND_MESSAGE.Set_Token('ID', p_x_request_id);
1056: FND_MSG_PUB.ADD;
1057: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1058: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1059: END IF;
1060: CLOSE get_ue_dtls_csr;
1061: RAISE FND_API.G_EXC_ERROR;
1054: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_NO_UE_FOR_REQ');
1055: FND_MESSAGE.Set_Token('ID', p_x_request_id);
1056: FND_MSG_PUB.ADD;
1057: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1058: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1059: END IF;
1060: CLOSE get_ue_dtls_csr;
1061: RAISE FND_API.G_EXC_ERROR;
1062: END IF;
1071: FETCH get_tasks_for_ue_csr INTO l_dummy;
1072: IF (get_tasks_for_ue_csr%FOUND) THEN
1073: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_ASGND_TO_VISIT');
1074: FND_MSG_PUB.ADD;
1075: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1076: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1077: END IF;
1078: CLOSE get_tasks_for_ue_csr;
1079: RAISE FND_API.G_EXC_ERROR;
1072: IF (get_tasks_for_ue_csr%FOUND) THEN
1073: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_ASGND_TO_VISIT');
1074: FND_MSG_PUB.ADD;
1075: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1076: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1077: END IF;
1078: CLOSE get_tasks_for_ue_csr;
1079: RAISE FND_API.G_EXC_ERROR;
1080: END IF;
1079: RAISE FND_API.G_EXC_ERROR;
1080: END IF;
1081: CLOSE get_tasks_for_ue_csr;
1082: */
1083: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1084: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
1085: END IF;
1086: END Validate_Associated_Request;
1087:
1080: END IF;
1081: CLOSE get_tasks_for_ue_csr;
1082: */
1083: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1084: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
1085: END IF;
1086: END Validate_Associated_Request;
1087:
1088: ----------------------------------------
1151:
1152: L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Validate_Association_Records';
1153:
1154: BEGIN
1155: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1156: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
1157: END IF;
1158: FOR i in p_x_sr_mr_association_tbl.FIRST .. p_x_sr_mr_association_tbl.LAST LOOP
1159: l_valid := true;
1152: L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Validate_Association_Records';
1153:
1154: BEGIN
1155: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1156: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
1157: END IF;
1158: FOR i in p_x_sr_mr_association_tbl.FIRST .. p_x_sr_mr_association_tbl.LAST LOOP
1159: l_valid := true;
1160: IF (p_x_sr_mr_association_tbl(i).OPERATION_FLAG NOT IN (G_OPR_CREATE, G_OPR_DELETE)) THEN
1161: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_OPR_INVALID');
1162: FND_MESSAGE.Set_Token('OPR_FLAG', p_x_sr_mr_association_tbl(i).OPERATION_FLAG);
1163: FND_MESSAGE.Set_Token('INDEX', i);
1164: FND_MSG_PUB.ADD;
1165: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1166: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1167: END IF;
1168: l_valid := false;
1169: ELSIF (p_x_sr_mr_association_tbl(i).OPERATION_FLAG = G_OPR_DELETE) THEN
1162: FND_MESSAGE.Set_Token('OPR_FLAG', p_x_sr_mr_association_tbl(i).OPERATION_FLAG);
1163: FND_MESSAGE.Set_Token('INDEX', i);
1164: FND_MSG_PUB.ADD;
1165: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1166: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1167: END IF;
1168: l_valid := false;
1169: ELSIF (p_x_sr_mr_association_tbl(i).OPERATION_FLAG = G_OPR_DELETE) THEN
1170: -- Delete Operation
1173: IF (p_x_sr_mr_association_tbl(i).UNIT_EFFECTIVITY_ID IS NULL) THEN
1174: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_UE_ID_NULL');
1175: FND_MESSAGE.Set_Token('INDEX', i);
1176: FND_MSG_PUB.ADD;
1177: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1178: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1179: END IF;
1180: l_valid := false;
1181: ELSIF (p_x_sr_mr_association_tbl(i).OBJECT_VERSION_NUMBER IS NULL) THEN
1174: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_UE_ID_NULL');
1175: FND_MESSAGE.Set_Token('INDEX', i);
1176: FND_MSG_PUB.ADD;
1177: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1178: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1179: END IF;
1180: l_valid := false;
1181: ELSIF (p_x_sr_mr_association_tbl(i).OBJECT_VERSION_NUMBER IS NULL) THEN
1182: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_UE_OVN_NULL');
1181: ELSIF (p_x_sr_mr_association_tbl(i).OBJECT_VERSION_NUMBER IS NULL) THEN
1182: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_UE_OVN_NULL');
1183: FND_MESSAGE.Set_Token('INDEX', i);
1184: FND_MSG_PUB.ADD;
1185: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1186: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1187: END IF;
1188: l_valid := false;
1189: ELSE
1182: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_UE_OVN_NULL');
1183: FND_MESSAGE.Set_Token('INDEX', i);
1184: FND_MSG_PUB.ADD;
1185: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1186: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1187: END IF;
1188: l_valid := false;
1189: ELSE
1190: OPEN validate_mr_ue_csr(p_mr_ue_id => p_x_sr_mr_association_tbl(i).UNIT_EFFECTIVITY_ID,
1196: FND_MESSAGE.Set_Token('UE_ID', p_x_sr_mr_association_tbl(i).UNIT_EFFECTIVITY_ID);
1197: FND_MESSAGE.Set_Token('OVN', p_x_sr_mr_association_tbl(i).OBJECT_VERSION_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: END IF; -- UE Id, OVN Not valid
1197: FND_MESSAGE.Set_Token('OVN', p_x_sr_mr_association_tbl(i).OBJECT_VERSION_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: END IF; -- UE Id, OVN Not valid
1205: CLOSE validate_mr_ue_csr;
1203: l_valid := false;
1204: END IF; -- UE Id, OVN Not valid
1205: CLOSE validate_mr_ue_csr;
1206: END IF; -- UE Id, OVN Not null
1207: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1208: IF (l_valid) THEN
1209: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Record ' || i || ' for Delete operation is Valid');
1210: ELSE
1211: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Record ' || i || ' for Delete operation is Not valid');
1205: CLOSE validate_mr_ue_csr;
1206: END IF; -- UE Id, OVN Not null
1207: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1208: IF (l_valid) THEN
1209: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Record ' || i || ' for Delete operation is Valid');
1210: ELSE
1211: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Record ' || i || ' for Delete operation is Not valid');
1212: END IF;
1213: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Record Details: UE ID: ' || p_x_sr_mr_association_tbl(i).UNIT_EFFECTIVITY_ID ||
1207: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1208: IF (l_valid) THEN
1209: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Record ' || i || ' for Delete operation is Valid');
1210: ELSE
1211: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Record ' || i || ' for Delete operation is Not valid');
1212: END IF;
1213: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Record Details: UE ID: ' || p_x_sr_mr_association_tbl(i).UNIT_EFFECTIVITY_ID ||
1214: ', UE OVN: ' || p_x_sr_mr_association_tbl(i).OBJECT_VERSION_NUMBER);
1215: END IF; -- Log Statement
1209: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Record ' || i || ' for Delete operation is Valid');
1210: ELSE
1211: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Record ' || i || ' for Delete operation is Not valid');
1212: END IF;
1213: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Record Details: UE ID: ' || p_x_sr_mr_association_tbl(i).UNIT_EFFECTIVITY_ID ||
1214: ', UE OVN: ' || p_x_sr_mr_association_tbl(i).OBJECT_VERSION_NUMBER);
1215: END IF; -- Log Statement
1216: ELSE
1217: -- Create Operation
1221: FND_MESSAGE.Set_Token('TITLE', p_x_sr_mr_association_tbl(i).MR_TITLE);
1222: FND_MESSAGE.Set_Token('VERSION', p_x_sr_mr_association_tbl(i).MR_VERSION);
1223: FND_MESSAGE.Set_Token('INDEX', i);
1224: FND_MSG_PUB.ADD;
1225: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1226: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1227: END IF;
1228: l_valid := false;
1229: ELSIF (p_x_sr_mr_association_tbl(i).CSI_INSTANCE_ID IS NULL) THEN
1222: FND_MESSAGE.Set_Token('VERSION', p_x_sr_mr_association_tbl(i).MR_VERSION);
1223: FND_MESSAGE.Set_Token('INDEX', i);
1224: FND_MSG_PUB.ADD;
1225: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1226: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1227: END IF;
1228: l_valid := false;
1229: ELSIF (p_x_sr_mr_association_tbl(i).CSI_INSTANCE_ID IS NULL) THEN
1230: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_INST_NUM_INVALID');
1230: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_INST_NUM_INVALID');
1231: FND_MESSAGE.Set_Token('INST_NUM', p_x_sr_mr_association_tbl(i).CSI_INSTANCE_NUMBER);
1232: FND_MESSAGE.Set_Token('INDEX', i);
1233: FND_MSG_PUB.ADD;
1234: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1235: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1236: END IF;
1237: l_valid := false;
1238: ELSE
1231: FND_MESSAGE.Set_Token('INST_NUM', p_x_sr_mr_association_tbl(i).CSI_INSTANCE_NUMBER);
1232: FND_MESSAGE.Set_Token('INDEX', i);
1233: FND_MSG_PUB.ADD;
1234: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1235: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1236: END IF;
1237: l_valid := false;
1238: ELSE
1239: -- Validate the MR Header Id
1244: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_MR_ID_INVALID');
1245: FND_MESSAGE.Set_Token('MR_ID', p_x_sr_mr_association_tbl(i).MR_HEADER_ID);
1246: FND_MESSAGE.Set_Token('INDEX', i);
1247: FND_MSG_PUB.ADD;
1248: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1249: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1250: END IF;
1251: l_valid := false;
1252: ELSE
1245: FND_MESSAGE.Set_Token('MR_ID', p_x_sr_mr_association_tbl(i).MR_HEADER_ID);
1246: FND_MESSAGE.Set_Token('INDEX', i);
1247: FND_MSG_PUB.ADD;
1248: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1249: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1250: END IF;
1251: l_valid := false;
1252: ELSE
1253: CLOSE validate_mr_id_csr;
1259: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_INST_ID_INVALID');
1260: FND_MESSAGE.Set_Token('INST_ID', p_x_sr_mr_association_tbl(i).CSI_INSTANCE_ID);
1261: FND_MESSAGE.Set_Token('INDEX', i);
1262: FND_MSG_PUB.ADD;
1263: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1264: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1265: END IF;
1266: l_valid := false;
1267: ELSE
1260: FND_MESSAGE.Set_Token('INST_ID', p_x_sr_mr_association_tbl(i).CSI_INSTANCE_ID);
1261: FND_MESSAGE.Set_Token('INDEX', i);
1262: FND_MSG_PUB.ADD;
1263: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1264: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1265: END IF;
1266: l_valid := false;
1267: ELSE
1268: CLOSE validate_instance_id_csr;
1276: FND_MESSAGE.Set_Token('TITLE', p_x_sr_mr_association_tbl(i).MR_TITLE);
1277: FND_MESSAGE.Set_Token('VERSION', p_x_sr_mr_association_tbl(i).MR_VERSION);
1278: FND_MESSAGE.Set_Token('INDEX', i);
1279: FND_MSG_PUB.ADD;
1280: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1281: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1282: END IF;
1283: l_valid := false;
1284: ELSE
1277: FND_MESSAGE.Set_Token('VERSION', p_x_sr_mr_association_tbl(i).MR_VERSION);
1278: FND_MESSAGE.Set_Token('INDEX', i);
1279: FND_MSG_PUB.ADD;
1280: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1281: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1282: END IF;
1283: l_valid := false;
1284: ELSE
1285: CLOSE validate_sr_mr_intance_rel;
1296: IF (l_temp_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1297: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_MR_DTLS_FAILED');
1298: FND_MESSAGE.Set_Token('INDEX', i);
1299: FND_MSG_PUB.ADD;
1300: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1301: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1302: END IF;
1303: l_valid := false;
1304: ELSE
1297: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_MR_DTLS_FAILED');
1298: FND_MESSAGE.Set_Token('INDEX', i);
1299: FND_MSG_PUB.ADD;
1300: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1301: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1302: END IF;
1303: l_valid := false;
1304: ELSE
1305: l_mr_applicable := false;
1315: IF (l_mr_applicable = false) THEN
1316: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_MR_NOT_APPLCBL');
1317: FND_MESSAGE.Set_Token('MR_TITLE', Get_MR_Title_From_MR_Id(p_x_sr_mr_association_tbl(i).MR_HEADER_ID));
1318: FND_MSG_PUB.ADD;
1319: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1320: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1321: END IF;
1322: l_valid := false;
1323: END IF; -- MR applicable
1316: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_MR_NOT_APPLCBL');
1317: FND_MESSAGE.Set_Token('MR_TITLE', Get_MR_Title_From_MR_Id(p_x_sr_mr_association_tbl(i).MR_HEADER_ID));
1318: FND_MSG_PUB.ADD;
1319: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1320: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1321: END IF;
1322: l_valid := false;
1323: END IF; -- MR applicable
1324: END IF; -- GET_APPLICABLE_MRS successful
1326: end if; -- program_type_code
1327: END IF; -- Instance Id Valid
1328: END IF; -- MR Header Id Valid
1329: END IF; -- MR Id and Instance Id Not null
1330: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1331: IF (l_valid) THEN
1332: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Record ' || i || ' for Create operation is Valid');
1333: ELSE
1334: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Record ' || i || ' for Create operation is Not valid');
1328: END IF; -- MR Header Id Valid
1329: END IF; -- MR Id and Instance Id Not null
1330: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1331: IF (l_valid) THEN
1332: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Record ' || i || ' for Create operation is Valid');
1333: ELSE
1334: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Record ' || i || ' for Create operation is Not valid');
1335: END IF;
1336: 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 ||
1330: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1331: IF (l_valid) THEN
1332: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Record ' || i || ' for Create operation is Valid');
1333: ELSE
1334: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Record ' || i || ' for Create operation is Not valid');
1335: END IF;
1336: 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 ||
1337: ', CSI Instance ID: ' || p_x_sr_mr_association_tbl(i).CSI_INSTANCE_ID);
1338: END IF; -- Log Statement
1332: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Record ' || i || ' for Create operation is Valid');
1333: ELSE
1334: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Record ' || i || ' for Create operation is Not valid');
1335: END IF;
1336: 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 ||
1337: ', CSI Instance ID: ' || p_x_sr_mr_association_tbl(i).CSI_INSTANCE_ID);
1338: END IF; -- Log Statement
1339: END IF; -- Valid Operation flag
1340: END LOOP; -- All Association/Disassociation Records
1338: END IF; -- Log Statement
1339: END IF; -- Valid Operation flag
1340: END LOOP; -- All Association/Disassociation Records
1341:
1342: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1343: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
1344: END IF;
1345: END Validate_Association_Records;
1346:
1339: END IF; -- Valid Operation flag
1340: END LOOP; -- All Association/Disassociation Records
1341:
1342: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1343: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
1344: END IF;
1345: END Validate_Association_Records;
1346:
1347: ----------------------------------------
1373:
1374: L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Process_Disassociations';
1375:
1376: BEGIN
1377: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1378: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
1379: END IF;
1380:
1381: FOR i in p_sr_mr_association_tbl.FIRST .. p_sr_mr_association_tbl.LAST LOOP
1374: L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Process_Disassociations';
1375:
1376: BEGIN
1377: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1378: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
1379: END IF;
1380:
1381: FOR i in p_sr_mr_association_tbl.FIRST .. p_sr_mr_association_tbl.LAST LOOP
1382: IF (p_sr_mr_association_tbl(i).OPERATION_FLAG = G_OPR_DELETE) THEN
1379: END IF;
1380:
1381: FOR i in p_sr_mr_association_tbl.FIRST .. p_sr_mr_association_tbl.LAST LOOP
1382: IF (p_sr_mr_association_tbl(i).OPERATION_FLAG = G_OPR_DELETE) THEN
1383: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1384: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'About to Disassociate Record with index ' || i);
1385: END IF;
1386: OPEN get_relationship_id_csr(p_sr_ue_id => p_sr_ue_id,
1387: p_mr_ue_id => p_sr_mr_association_tbl(i).UNIT_EFFECTIVITY_ID);
1380:
1381: FOR i in p_sr_mr_association_tbl.FIRST .. p_sr_mr_association_tbl.LAST LOOP
1382: IF (p_sr_mr_association_tbl(i).OPERATION_FLAG = G_OPR_DELETE) THEN
1383: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1384: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'About to Disassociate Record with index ' || i);
1385: END IF;
1386: OPEN get_relationship_id_csr(p_sr_ue_id => p_sr_ue_id,
1387: p_mr_ue_id => p_sr_mr_association_tbl(i).UNIT_EFFECTIVITY_ID);
1388: FETCH get_relationship_id_csr INTO l_relationship_id;
1389: CLOSE get_relationship_id_csr;
1390:
1391: -- Delete the Dependents (If Group MR)
1392: FOR l_rel_dtls_rec IN get_rel_dtls_csr(p_sr_mr_association_tbl(i).UNIT_EFFECTIVITY_ID) LOOP
1393: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1394: 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');
1395: END IF;
1396: AHL_UE_RELATIONSHIPS_PKG.DELETE_ROW(X_UE_RELATIONSHIP_ID => l_rel_dtls_rec.UE_RELATIONSHIP_ID);
1397:
1390:
1391: -- Delete the Dependents (If Group MR)
1392: FOR l_rel_dtls_rec IN get_rel_dtls_csr(p_sr_mr_association_tbl(i).UNIT_EFFECTIVITY_ID) LOOP
1393: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1394: 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');
1395: END IF;
1396: AHL_UE_RELATIONSHIPS_PKG.DELETE_ROW(X_UE_RELATIONSHIP_ID => l_rel_dtls_rec.UE_RELATIONSHIP_ID);
1397:
1398: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1394: 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');
1395: END IF;
1396: AHL_UE_RELATIONSHIPS_PKG.DELETE_ROW(X_UE_RELATIONSHIP_ID => l_rel_dtls_rec.UE_RELATIONSHIP_ID);
1397:
1398: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1399: 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');
1400: END IF;
1401: AHL_UNIT_EFFECTIVITIES_PKG.DELETE_ROW(X_UNIT_EFFECTIVITY_ID => l_rel_dtls_rec.RELATED_UE_ID);
1402: END LOOP;
1395: END IF;
1396: AHL_UE_RELATIONSHIPS_PKG.DELETE_ROW(X_UE_RELATIONSHIP_ID => l_rel_dtls_rec.UE_RELATIONSHIP_ID);
1397:
1398: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1399: 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');
1400: END IF;
1401: AHL_UNIT_EFFECTIVITIES_PKG.DELETE_ROW(X_UNIT_EFFECTIVITY_ID => l_rel_dtls_rec.RELATED_UE_ID);
1402: END LOOP;
1403: -- Delete the MR Relationship and UE
1400: END IF;
1401: AHL_UNIT_EFFECTIVITIES_PKG.DELETE_ROW(X_UNIT_EFFECTIVITY_ID => l_rel_dtls_rec.RELATED_UE_ID);
1402: END LOOP;
1403: -- Delete the MR Relationship and UE
1404: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1405: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'About to Delete Relationship with RELATIONSHIP_ID = ' || l_relationship_id);
1406: END IF;
1407: AHL_UE_RELATIONSHIPS_PKG.DELETE_ROW(X_UE_RELATIONSHIP_ID => l_relationship_id);
1408:
1401: AHL_UNIT_EFFECTIVITIES_PKG.DELETE_ROW(X_UNIT_EFFECTIVITY_ID => l_rel_dtls_rec.RELATED_UE_ID);
1402: END LOOP;
1403: -- Delete the MR Relationship and UE
1404: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1405: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'About to Delete Relationship with RELATIONSHIP_ID = ' || l_relationship_id);
1406: END IF;
1407: AHL_UE_RELATIONSHIPS_PKG.DELETE_ROW(X_UE_RELATIONSHIP_ID => l_relationship_id);
1408:
1409: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1405: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'About to Delete Relationship with RELATIONSHIP_ID = ' || l_relationship_id);
1406: END IF;
1407: AHL_UE_RELATIONSHIPS_PKG.DELETE_ROW(X_UE_RELATIONSHIP_ID => l_relationship_id);
1408:
1409: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1410: 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);
1411: END IF;
1412: AHL_UNIT_EFFECTIVITIES_PKG.DELETE_ROW(X_UNIT_EFFECTIVITY_ID => p_sr_mr_association_tbl(i).UNIT_EFFECTIVITY_ID);
1413:
1406: END IF;
1407: AHL_UE_RELATIONSHIPS_PKG.DELETE_ROW(X_UE_RELATIONSHIP_ID => l_relationship_id);
1408:
1409: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1410: 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);
1411: END IF;
1412: AHL_UNIT_EFFECTIVITIES_PKG.DELETE_ROW(X_UNIT_EFFECTIVITY_ID => p_sr_mr_association_tbl(i).UNIT_EFFECTIVITY_ID);
1413:
1414: END IF; -- Disassociation Record
1413:
1414: END IF; -- Disassociation Record
1415: END LOOP; -- All Association/Disassociation Records
1416:
1417: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1418: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
1419: END IF;
1420: END Process_Disassociations;
1421:
1414: END IF; -- Disassociation Record
1415: END LOOP; -- All Association/Disassociation Records
1416:
1417: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1418: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
1419: END IF;
1420: END Process_Disassociations;
1421:
1422: ----------------------------------------
1491:
1492: L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Process_New_Associations';
1493:
1494: BEGIN
1495: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1496: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
1497: END IF;
1498:
1499: FOR i in p_x_sr_mr_association_tbl.FIRST .. p_x_sr_mr_association_tbl.LAST LOOP
1492: L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Process_New_Associations';
1493:
1494: BEGIN
1495: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1496: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
1497: END IF;
1498:
1499: FOR i in p_x_sr_mr_association_tbl.FIRST .. p_x_sr_mr_association_tbl.LAST LOOP
1500: IF (p_x_sr_mr_association_tbl(i).OPERATION_FLAG = G_OPR_CREATE) THEN
1497: END IF;
1498:
1499: FOR i in p_x_sr_mr_association_tbl.FIRST .. p_x_sr_mr_association_tbl.LAST LOOP
1500: IF (p_x_sr_mr_association_tbl(i).OPERATION_FLAG = G_OPR_CREATE) THEN
1501: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1502: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Record ' || i || ' is a Create Record. Checking for Duplicates first.');
1503: END IF;
1504: -- Check for duplicates
1505: OPEN get_dup_mrs_csr(p_mr_id => p_x_sr_mr_association_tbl(i).MR_HEADER_ID,
1498:
1499: FOR i in p_x_sr_mr_association_tbl.FIRST .. p_x_sr_mr_association_tbl.LAST LOOP
1500: IF (p_x_sr_mr_association_tbl(i).OPERATION_FLAG = G_OPR_CREATE) THEN
1501: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1502: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Record ' || i || ' is a Create Record. Checking for Duplicates first.');
1503: END IF;
1504: -- Check for duplicates
1505: OPEN get_dup_mrs_csr(p_mr_id => p_x_sr_mr_association_tbl(i).MR_HEADER_ID,
1506: p_instance_id => p_x_sr_mr_association_tbl(i).CSI_INSTANCE_ID,
1508: FETCH get_dup_mrs_csr INTO l_dummy;
1509: IF (get_dup_mrs_csr%FOUND) THEN
1510: l_valid_flag := false;
1511: CLOSE get_dup_mrs_csr;
1512: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1513: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Record ' || i || ' with MR Id ' ||
1514: p_x_sr_mr_association_tbl(i).MR_HEADER_ID || ' and Instance Id ' ||
1515: p_x_sr_mr_association_tbl(i).CSI_INSTANCE_ID || ' is a Duplicate.');
1516: END IF;
1509: IF (get_dup_mrs_csr%FOUND) THEN
1510: l_valid_flag := false;
1511: CLOSE get_dup_mrs_csr;
1512: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1513: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Record ' || i || ' with MR Id ' ||
1514: p_x_sr_mr_association_tbl(i).MR_HEADER_ID || ' and Instance Id ' ||
1515: p_x_sr_mr_association_tbl(i).CSI_INSTANCE_ID || ' is a Duplicate.');
1516: END IF;
1517: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_DUPLICATE_MR');
1516: END IF;
1517: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_DUPLICATE_MR');
1518: FND_MESSAGE.Set_Token('MR_TITLE', Get_MR_Title_From_MR_Id(p_x_sr_mr_association_tbl(i).MR_HEADER_ID));
1519: FND_MSG_PUB.ADD;
1520: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1521: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1522: END IF;
1523: ELSE
1524: CLOSE get_dup_mrs_csr;
1517: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_DUPLICATE_MR');
1518: FND_MESSAGE.Set_Token('MR_TITLE', Get_MR_Title_From_MR_Id(p_x_sr_mr_association_tbl(i).MR_HEADER_ID));
1519: FND_MSG_PUB.ADD;
1520: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1521: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1522: END IF;
1523: ELSE
1524: CLOSE get_dup_mrs_csr;
1525: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1521: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1522: END IF;
1523: ELSE
1524: CLOSE get_dup_mrs_csr;
1525: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1526: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Record ' || i || ' is not a Duplicate. About to create MR UE and UE Relationship.');
1527: END IF;
1528:
1529: --pdoki added for ER 9583373
1522: END IF;
1523: ELSE
1524: CLOSE get_dup_mrs_csr;
1525: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1526: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Record ' || i || ' is not a Duplicate. About to create MR UE and UE Relationship.');
1527: END IF;
1528:
1529: --pdoki added for ER 9583373
1530: OPEN Chk_UE_Init_Due (p_x_sr_mr_association_tbl(i).MR_HEADER_ID, p_x_sr_mr_association_tbl(i).CSI_INSTANCE_ID);
1580:
1581: END IF;
1582: END IF; -- Duplicate MR Check
1583: ELSE
1584: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1585: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Record ' || i || ' is not a Create Record. Skipping.');
1586: END IF;
1587: END IF; -- New Association Record
1588: END LOOP; -- All Association/Disassociation Records
1581: END IF;
1582: END IF; -- Duplicate MR Check
1583: ELSE
1584: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1585: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Record ' || i || ' is not a Create Record. Skipping.');
1586: END IF;
1587: END IF; -- New Association Record
1588: END LOOP; -- All Association/Disassociation Records
1589:
1595: OPEN c_get_origin_wo_id(p_sr_ue_id);
1596: FETCH c_get_origin_wo_id INTO l_origin_wo_id;
1597: CLOSE c_get_origin_wo_id;
1598:
1599: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1600: FND_LOG.STRING(
1601: FND_LOG.LEVEL_STATEMENT,
1602: L_DEBUG_KEY,
1603: 'Before updating Originating Work Order Id');
1596: FETCH c_get_origin_wo_id INTO l_origin_wo_id;
1597: CLOSE c_get_origin_wo_id;
1598:
1599: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1600: FND_LOG.STRING(
1601: FND_LOG.LEVEL_STATEMENT,
1602: L_DEBUG_KEY,
1603: 'Before updating Originating Work Order Id');
1604: FND_LOG.STRING(
1597: CLOSE c_get_origin_wo_id;
1598:
1599: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1600: FND_LOG.STRING(
1601: FND_LOG.LEVEL_STATEMENT,
1602: L_DEBUG_KEY,
1603: 'Before updating Originating Work Order Id');
1604: FND_LOG.STRING(
1605: FND_LOG.LEVEL_STATEMENT,
1600: FND_LOG.STRING(
1601: FND_LOG.LEVEL_STATEMENT,
1602: L_DEBUG_KEY,
1603: 'Before updating Originating Work Order Id');
1604: FND_LOG.STRING(
1605: FND_LOG.LEVEL_STATEMENT,
1606: L_DEBUG_KEY,
1607: 'p_sr_ue_id->'||p_sr_ue_id||' , '||'SR origin_wo_id->'||l_origin_wo_id);
1608: END IF;
1601: FND_LOG.LEVEL_STATEMENT,
1602: L_DEBUG_KEY,
1603: 'Before updating Originating Work Order Id');
1604: FND_LOG.STRING(
1605: FND_LOG.LEVEL_STATEMENT,
1606: L_DEBUG_KEY,
1607: 'p_sr_ue_id->'||p_sr_ue_id||' , '||'SR origin_wo_id->'||l_origin_wo_id);
1608: END IF;
1609:
1633: END;
1634: END IF;
1635: -- End Changes ER # 6123671
1636:
1637: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1638: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
1639: END IF;
1640: END Process_New_Associations;
1641:
1634: END IF;
1635: -- End Changes ER # 6123671
1636:
1637: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1638: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
1639: END IF;
1640: END Process_New_Associations;
1641:
1642: ----------------------------------------
1696: L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Get_MR_UnitEffectivity';
1697: l_get_ue_flag BOOLEAN := true;
1698:
1699: BEGIN
1700: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1701: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
1702: END IF;
1703:
1704: IF(p_x_sr_mr_association_rec.UNIT_EFFECTIVITY_ID IS NOT NULL) THEN
1697: l_get_ue_flag BOOLEAN := true;
1698:
1699: BEGIN
1700: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1701: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
1702: END IF;
1703:
1704: IF(p_x_sr_mr_association_rec.UNIT_EFFECTIVITY_ID IS NOT NULL) THEN
1705: l_get_ue_flag := false; -- No need to get the UE Id: It is already available
1721: FETCH get_ue_id_csr INTO p_x_sr_mr_association_rec.UNIT_EFFECTIVITY_ID;
1722: CLOSE get_ue_id_csr;
1723: END IF;
1724:
1725: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1726: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
1727: END IF;
1728: END Get_MR_UnitEffectivity;
1729:
1722: CLOSE get_ue_id_csr;
1723: END IF;
1724:
1725: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1726: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
1727: END IF;
1728: END Get_MR_UnitEffectivity;
1729:
1730: ----------------------------------------
1750: l_get_mr_flag BOOLEAN := true;
1751: l_get_inst_flag BOOLEAN := true;
1752:
1753: BEGIN
1754: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1755: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
1756: END IF;
1757:
1758: IF (p_x_sr_mr_association_rec.MR_HEADER_ID IS NOT NULL) THEN
1751: l_get_inst_flag BOOLEAN := true;
1752:
1753: BEGIN
1754: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1755: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
1756: END IF;
1757:
1758: IF (p_x_sr_mr_association_rec.MR_HEADER_ID IS NOT NULL) THEN
1759: l_get_mr_flag := false; -- No need to get the MR Id: It is already available
1784: FETCH get_csi_instance_id_csr INTO p_x_sr_mr_association_rec.CSI_INSTANCE_ID;
1785: CLOSE get_csi_instance_id_csr;
1786: END IF;
1787:
1788: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1789: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
1790: END IF;
1791: END Get_New_Asso_Val_To_Id;
1792:
1785: CLOSE get_csi_instance_id_csr;
1786: END IF;
1787:
1788: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1789: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
1790: END IF;
1791: END Get_New_Asso_Val_To_Id;
1792:
1793: ----------------------------------------
1806: l_temp_row_id VARCHAR2(30);
1807: L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Create_MR_Unit_Effectivity';
1808:
1809: BEGIN
1810: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1811: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
1812: END IF;
1813:
1814:
1807: L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Create_MR_Unit_Effectivity';
1808:
1809: BEGIN
1810: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1811: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
1812: END IF;
1813:
1814:
1815: AHL_UNIT_EFFECTIVITIES_PKG.Insert_Row (
1879: X_FLEET_HEADER_ID => p_fleet_header_id
1880: -- BACHANDR - NR project end
1881: );
1882:
1883: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1884: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Created New UE with Id ' || x_ue_id);
1885: END IF;
1886:
1887: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1880: -- BACHANDR - NR project end
1881: );
1882:
1883: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1884: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Created New UE with Id ' || x_ue_id);
1885: END IF;
1886:
1887: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1888: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
1883: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1884: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Created New UE with Id ' || x_ue_id);
1885: END IF;
1886:
1887: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1888: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
1889: END IF;
1890: END Create_MR_Unit_Effectivity;
1891:
1884: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Created New UE with Id ' || x_ue_id);
1885: END IF;
1886:
1887: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1888: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
1889: END IF;
1890: END Create_MR_Unit_Effectivity;
1891:
1892: ----------------------------------------
1904:
1905: L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Create_UE_Relationship';
1906:
1907: BEGIN
1908: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1909: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
1910: END IF;
1911:
1912: AHL_UE_RELATIONSHIPS_PKG.INSERT_ROW(
1905: L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Create_UE_Relationship';
1906:
1907: BEGIN
1908: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1909: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
1910: END IF;
1911:
1912: AHL_UE_RELATIONSHIPS_PKG.INSERT_ROW(
1913: X_UE_RELATIONSHIP_ID => x_ue_rel_id,
1937: X_CREATION_DATE => SYSDATE,
1938: X_CREATED_BY => p_user_id,
1939: X_LAST_UPDATE_LOGIN => p_login_id);
1940:
1941: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1942: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Created New UE Relationship with Id ' || x_ue_rel_id);
1943: END IF;
1944:
1945: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1938: X_CREATED_BY => p_user_id,
1939: X_LAST_UPDATE_LOGIN => p_login_id);
1940:
1941: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1942: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Created New UE Relationship with Id ' || x_ue_rel_id);
1943: END IF;
1944:
1945: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1946: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
1941: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1942: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Created New UE Relationship with Id ' || x_ue_rel_id);
1943: END IF;
1944:
1945: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1946: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
1947: END IF;
1948: END Create_UE_Relationship;
1949:
1942: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Created New UE Relationship with Id ' || x_ue_rel_id);
1943: END IF;
1944:
1945: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1946: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
1947: END IF;
1948: END Create_UE_Relationship;
1949:
1950: ----------------------------------------
2023: l_dummy VARCHAR2(1);
2024: l_mr_title AHL_MR_HEADERS_B.TITLE%TYPE := NULL;
2025:
2026: BEGIN
2027: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2028: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
2029: END IF;
2030: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2031: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Processing Group MR with Id: ' || p_mr_header_id);
2024: l_mr_title AHL_MR_HEADERS_B.TITLE%TYPE := NULL;
2025:
2026: BEGIN
2027: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2028: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
2029: END IF;
2030: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2031: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Processing Group MR with Id: ' || p_mr_header_id);
2032: END IF;
2026: BEGIN
2027: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2028: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
2029: END IF;
2030: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2031: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Processing Group MR with Id: ' || p_mr_header_id);
2032: END IF;
2033:
2034: -- Evaluate Group MRs
2027: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2028: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
2029: END IF;
2030: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2031: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Processing Group MR with Id: ' || p_mr_header_id);
2032: END IF;
2033:
2034: -- Evaluate Group MRs
2035: -- Populate AHL_APPLICABLE_MRS if required
2037: FETCH get_app_mr_table_count_csr INTO l_temp_count;
2038: CLOSE get_app_mr_table_count_csr;
2039: IF (l_temp_count > 0) THEN
2040: -- AHL_APPLICABLE_MRS is already populated. No need to Call Populate_Appl_MRs
2041: IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2042: 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.');
2043: END IF;
2044: ELSE
2045: IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2038: CLOSE get_app_mr_table_count_csr;
2039: IF (l_temp_count > 0) THEN
2040: -- AHL_APPLICABLE_MRS is already populated. No need to Call Populate_Appl_MRs
2041: IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2042: 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.');
2043: END IF;
2044: ELSE
2045: IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2046: 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);
2041: IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2042: 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.');
2043: END IF;
2044: ELSE
2045: IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2046: 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);
2047: END IF;
2048: AHL_UMP_UTIL_PKG.Populate_Appl_MRs(p_csi_ii_id => p_csi_instance_id,
2049: x_return_status => l_temp_return_status,
2042: 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.');
2043: END IF;
2044: ELSE
2045: IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2046: 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);
2047: END IF;
2048: AHL_UMP_UTIL_PKG.Populate_Appl_MRs(p_csi_ii_id => p_csi_instance_id,
2049: x_return_status => l_temp_return_status,
2050: x_msg_count => l_temp_msg_count,
2054: l_curr_mr_title := Get_MR_Title_From_MR_Id(p_mr_header_id);
2055: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_GRP_MR_FAILED');
2056: FND_MESSAGE.Set_Token('MR_TITLE', l_curr_mr_title);
2057: FND_MSG_PUB.ADD;
2058: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2059: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
2060: END IF;
2061: RETURN; -- No need to process this Group MR further
2062: END IF; -- Populate_Appl_MRs Failed
2055: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_GRP_MR_FAILED');
2056: FND_MESSAGE.Set_Token('MR_TITLE', l_curr_mr_title);
2057: FND_MSG_PUB.ADD;
2058: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2059: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
2060: END IF;
2061: RETURN; -- No need to process this Group MR further
2062: END IF; -- Populate_Appl_MRs Failed
2063: OPEN get_app_mr_table_count_csr;
2062: END IF; -- Populate_Appl_MRs Failed
2063: OPEN get_app_mr_table_count_csr;
2064: FETCH get_app_mr_table_count_csr INTO l_temp_count;
2065: CLOSE get_app_mr_table_count_csr;
2066: IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2067: 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);
2068: END IF;
2069: END IF; -- AHL_APPLICABLE_MRS temp table is empty
2070:
2063: OPEN get_app_mr_table_count_csr;
2064: FETCH get_app_mr_table_count_csr INTO l_temp_count;
2065: CLOSE get_app_mr_table_count_csr;
2066: IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2067: 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);
2068: END IF;
2069: END IF; -- AHL_APPLICABLE_MRS temp table is empty
2070:
2071: -- Call process_Group_MR_Instance
2071: -- Call process_Group_MR_Instance
2072: /*OPEN get_relns_table_count_csr;
2073: FETCH get_relns_table_count_csr INTO l_temp_count;
2074: CLOSE get_relns_table_count_csr;
2075: IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2076: FND_LOG.STRING(FND_LOG.LEVEL_EVENT, L_DEBUG_KEY, 'Number of records in AHL_APPLICABLE_MR_RELNS before processing: ' || l_temp_count);
2077: 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 = ' ||
2078: p_mr_header_id || ' and p_top_item_instance_id = ' || p_csi_instance_id);
2079: END IF;*/
2072: /*OPEN get_relns_table_count_csr;
2073: FETCH get_relns_table_count_csr INTO l_temp_count;
2074: CLOSE get_relns_table_count_csr;
2075: IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2076: FND_LOG.STRING(FND_LOG.LEVEL_EVENT, L_DEBUG_KEY, 'Number of records in AHL_APPLICABLE_MR_RELNS before processing: ' || l_temp_count);
2077: 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 = ' ||
2078: p_mr_header_id || ' and p_top_item_instance_id = ' || p_csi_instance_id);
2079: END IF;*/
2080: AHL_UMP_UTIL_PKG.process_Group_MR_Instance(p_top_mr_id => p_mr_header_id,
2073: FETCH get_relns_table_count_csr INTO l_temp_count;
2074: CLOSE get_relns_table_count_csr;
2075: IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2076: FND_LOG.STRING(FND_LOG.LEVEL_EVENT, L_DEBUG_KEY, 'Number of records in AHL_APPLICABLE_MR_RELNS before processing: ' || l_temp_count);
2077: 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 = ' ||
2078: p_mr_header_id || ' and p_top_item_instance_id = ' || p_csi_instance_id);
2079: END IF;*/
2080: AHL_UMP_UTIL_PKG.process_Group_MR_Instance(p_top_mr_id => p_mr_header_id,
2081: p_top_item_instance_id => p_csi_instance_id,
2082: p_init_temp_table => G_YES_FLAG); -- To clean up temp table first
2083: /*OPEN get_relns_table_count_csr;
2084: FETCH get_relns_table_count_csr INTO l_temp_count;
2085: CLOSE get_relns_table_count_csr;
2086: IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2087: 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);
2088: END IF;*/
2089:
2090: -- Check for duplicates
2083: /*OPEN get_relns_table_count_csr;
2084: FETCH get_relns_table_count_csr INTO l_temp_count;
2085: CLOSE get_relns_table_count_csr;
2086: IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2087: 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);
2088: END IF;*/
2089:
2090: -- Check for duplicates
2091: OPEN get_duplicate_mrs_csr(p_sr_ue_id => p_sr_ue_id);
2098: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_GROUP_DUP_MR');
2099: FND_MESSAGE.Set_Token('NEW_MR', l_curr_mr_title);
2100: FND_MESSAGE.Set_Token('DUP_MR', l_dup_mr_title);
2101: FND_MSG_PUB.ADD;
2102: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2103: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
2104: END IF;
2105: p_x_valid_flag := false;
2106: RETURN; -- No need to process this Group MR further
2099: FND_MESSAGE.Set_Token('NEW_MR', l_curr_mr_title);
2100: FND_MESSAGE.Set_Token('DUP_MR', l_dup_mr_title);
2101: FND_MSG_PUB.ADD;
2102: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2103: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
2104: END IF;
2105: p_x_valid_flag := false;
2106: RETURN; -- No need to process this Group MR further
2107: END IF;
2106: RETURN; -- No need to process this Group MR further
2107: END IF;
2108: CLOSE get_duplicate_mrs_csr;
2109:
2110: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2111: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Adding this Group MR will not cause any duplicates.');
2112: END IF;
2113:
2114: -- First create all the Unit Effectivities
2107: END IF;
2108: CLOSE get_duplicate_mrs_csr;
2109:
2110: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2111: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Adding this Group MR will not cause any duplicates.');
2112: END IF;
2113:
2114: -- First create all the Unit Effectivities
2115: l_temp_count := 0;
2148: UPDATE AHL_APPLICABLE_MR_RELNS
2149: SET UE_ID = l_new_ue_id
2150: WHERE CURRENT OF mr_relns_upd_csr;
2151: END LOOP;
2152: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2153: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Created ' || l_temp_count || ' Unit Effectivities for the Group MR.');
2154: END IF;
2155:
2156: -- Next, Create the UE Relationships
2149: SET UE_ID = l_new_ue_id
2150: WHERE CURRENT OF mr_relns_upd_csr;
2151: END LOOP;
2152: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2153: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Created ' || l_temp_count || ' Unit Effectivities for the Group MR.');
2154: END IF;
2155:
2156: -- Next, Create the UE Relationships
2157: l_temp_count := 0;
2169:
2170: l_temp_count := l_temp_count + 1;
2171:
2172: END LOOP;
2173: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2174: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Created ' || l_temp_count || ' UE Relationships for the Group MR.');
2175: END IF;
2176:
2177: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2170: l_temp_count := l_temp_count + 1;
2171:
2172: END LOOP;
2173: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2174: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Created ' || l_temp_count || ' UE Relationships for the Group MR.');
2175: END IF;
2176:
2177: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2178: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
2173: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2174: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Created ' || l_temp_count || ' UE Relationships for the Group MR.');
2175: END IF;
2176:
2177: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2178: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
2179: END IF;
2180: END Process_Group_MR;
2181:
2174: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Created ' || l_temp_count || ' UE Relationships for the Group MR.');
2175: END IF;
2176:
2177: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2178: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
2179: END IF;
2180: END Process_Group_MR;
2181:
2182: ----------------------------------------
2242: l_temp_count NUMBER := 0;
2243:
2244:
2245: BEGIN
2246: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2247: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
2248: END IF;
2249:
2250: FOR l_curr_ue_rec in get_MR_UEs_csr LOOP
2243:
2244:
2245: BEGIN
2246: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2247: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
2248: END IF;
2249:
2250: FOR l_curr_ue_rec in get_MR_UEs_csr LOOP
2251: IF (p_assigned_to_visit_flag = TRUE) THEN
2319: -- BACHANDR - NR project end
2320: );
2321: l_temp_count := l_temp_count + 1;
2322: END LOOP;
2323: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2324: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Updated Due Date and Tolerance Flag in ' || l_temp_count|| ' dependent unit effectivities.');
2325: END IF;
2326:
2327: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2320: );
2321: l_temp_count := l_temp_count + 1;
2322: END LOOP;
2323: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2324: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Updated Due Date and Tolerance Flag in ' || l_temp_count|| ' dependent unit effectivities.');
2325: END IF;
2326:
2327: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2328: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
2323: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2324: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Updated Due Date and Tolerance Flag in ' || l_temp_count|| ' dependent unit effectivities.');
2325: END IF;
2326:
2327: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2328: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
2329: END IF;
2330: END Handle_MR_UE_Date_Change;
2331:
2324: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Updated Due Date and Tolerance Flag in ' || l_temp_count|| ' dependent unit effectivities.');
2325: END IF;
2326:
2327: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2328: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
2329: END IF;
2330: END Handle_MR_UE_Date_Change;
2331:
2332: ----------------------------------------
2376: l_visit_assign_code VARCHAR2(30);
2377: l_accomplished_date DATE;
2378:
2379: BEGIN
2380: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2381: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
2382: END IF;
2383:
2384: OPEN get_UE_dtls_csr;
2377: l_accomplished_date DATE;
2378:
2379: BEGIN
2380: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2381: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
2382: END IF;
2383:
2384: OPEN get_UE_dtls_csr;
2385: FETCH get_UE_dtls_csr INTO l_curr_ue_rec;
2409: l_temp_due_date := p_new_due_date;
2410: OPEN is_ue_assigned_to_visit_csr;
2411: FETCH is_ue_assigned_to_visit_csr INTO l_dummy;
2412: IF (is_ue_assigned_to_visit_csr%FOUND) THEN
2413: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2414: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Unit Effectivity ' || p_sr_ue_id || ' has been assigned to a visit.');
2415: END IF;
2416: x_assigned_to_visit_flag := TRUE;
2417: -- Get the Visit Start Date
2410: OPEN is_ue_assigned_to_visit_csr;
2411: FETCH is_ue_assigned_to_visit_csr INTO l_dummy;
2412: IF (is_ue_assigned_to_visit_csr%FOUND) THEN
2413: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2414: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Unit Effectivity ' || p_sr_ue_id || ' has been assigned to a visit.');
2415: END IF;
2416: x_assigned_to_visit_flag := TRUE;
2417: -- Get the Visit Start Date
2418: AHL_UMP_UTIL_PKG.Get_Visit_Details(p_unit_effectivity_id => p_sr_ue_id,
2424: x_new_tolerance_flag := G_YES_FLAG;
2425: ELSE
2426: l_temp_tolerance_flag := null;
2427: END IF;
2428: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2429: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Visit Start Date: ' || l_visit_start_date ||
2430: ', New Expected Resolution date of SR: ' || p_new_due_date ||
2431: ', Tolerance Flag Set to ' || l_temp_tolerance_flag);
2432: END IF;
2425: ELSE
2426: l_temp_tolerance_flag := null;
2427: END IF;
2428: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2429: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Visit Start Date: ' || l_visit_start_date ||
2430: ', New Expected Resolution date of SR: ' || p_new_due_date ||
2431: ', Tolerance Flag Set to ' || l_temp_tolerance_flag);
2432: END IF;
2433: ELSE
2430: ', New Expected Resolution date of SR: ' || p_new_due_date ||
2431: ', Tolerance Flag Set to ' || l_temp_tolerance_flag);
2432: END IF;
2433: ELSE
2434: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2435: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Unit Effectivity ' || p_sr_ue_id || ' has not been assigned to a visit.');
2436: END IF;
2437: END IF; -- Assigned to a visit or not
2438: CLOSE is_ue_assigned_to_visit_csr;
2431: ', Tolerance Flag Set to ' || l_temp_tolerance_flag);
2432: END IF;
2433: ELSE
2434: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2435: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Unit Effectivity ' || p_sr_ue_id || ' has not been assigned to a visit.');
2436: END IF;
2437: END IF; -- Assigned to a visit or not
2438: CLOSE is_ue_assigned_to_visit_csr;
2439: ELSE
2436: END IF;
2437: END IF; -- Assigned to a visit or not
2438: CLOSE is_ue_assigned_to_visit_csr;
2439: ELSE
2440: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2441: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'No Due Date change for Unit Effectivity ' || p_sr_ue_id);
2442: END IF;
2443: l_temp_due_date := l_curr_ue_rec.DUE_DATE;
2444: l_temp_tolerance_flag := l_curr_ue_rec.TOLERANCE_FLAG;
2437: END IF; -- Assigned to a visit or not
2438: CLOSE is_ue_assigned_to_visit_csr;
2439: ELSE
2440: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2441: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'No Due Date change for Unit Effectivity ' || p_sr_ue_id);
2442: END IF;
2443: l_temp_due_date := l_curr_ue_rec.DUE_DATE;
2444: l_temp_tolerance_flag := l_curr_ue_rec.TOLERANCE_FLAG;
2445: END IF;
2517: -- BACHANDR - NR project start
2518: X_FLEET_HEADER_ID => AHL_UMP_UTIL_PKG.Get_Associated_Fleet(l_curr_ue_rec.UNIT_CONFIG_HEADER_ID,nvl(l_temp_due_date,l_occurred_date),null)
2519: -- BACHANDR - NR project end
2520: );
2521: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2522: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Unit Effectivity ' || p_sr_ue_id || ' has been updated.');
2523: END IF;
2524:
2525: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2518: X_FLEET_HEADER_ID => AHL_UMP_UTIL_PKG.Get_Associated_Fleet(l_curr_ue_rec.UNIT_CONFIG_HEADER_ID,nvl(l_temp_due_date,l_occurred_date),null)
2519: -- BACHANDR - NR project end
2520: );
2521: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2522: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Unit Effectivity ' || p_sr_ue_id || ' has been updated.');
2523: END IF;
2524:
2525: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2526: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
2521: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2522: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Unit Effectivity ' || p_sr_ue_id || ' has been updated.');
2523: END IF;
2524:
2525: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2526: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
2527: END IF;
2528: END Update_SR_Unit_Effectivity;
2529:
2522: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Unit Effectivity ' || p_sr_ue_id || ' has been updated.');
2523: END IF;
2524:
2525: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2526: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
2527: END IF;
2528: END Update_SR_Unit_Effectivity;
2529:
2530: ----------------------------------------
2550:
2551: l_dummy VARCHAR2(1);
2552:
2553: BEGIN
2554: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2555: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
2556: END IF;
2557:
2558: -- Ensure that request id is not null
2551: l_dummy VARCHAR2(1);
2552:
2553: BEGIN
2554: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2555: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
2556: END IF;
2557:
2558: -- Ensure that request id is not null
2559: IF(CS_SERVICEREQUEST_PVT.user_hooks_rec.request_id IS NULL) THEN
2558: -- Ensure that request id is not null
2559: IF(CS_SERVICEREQUEST_PVT.user_hooks_rec.request_id IS NULL) THEN
2560: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_REQ_ID_NULL');
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_UNEXPECTED_ERROR;
2566: END IF;
2559: IF(CS_SERVICEREQUEST_PVT.user_hooks_rec.request_id IS NULL) THEN
2560: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_REQ_ID_NULL');
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_UNEXPECTED_ERROR;
2566: END IF;
2567:
2572: CLOSE check_request_exists_csr;
2573: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_REQ_ID_INVALID');
2574: FND_MESSAGE.Set_Token('REQ_ID', CS_SERVICEREQUEST_PVT.user_hooks_rec.request_id);
2575: FND_MSG_PUB.ADD;
2576: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2577: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
2578: END IF;
2579: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2580: END IF;
2573: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_REQ_ID_INVALID');
2574: FND_MESSAGE.Set_Token('REQ_ID', CS_SERVICEREQUEST_PVT.user_hooks_rec.request_id);
2575: FND_MSG_PUB.ADD;
2576: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2577: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
2578: END IF;
2579: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2580: END IF;
2581: CLOSE check_request_exists_csr;
2589: CLOSE get_ue_dtls_csr;
2590: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_REQ_ID_INVALID');
2591: FND_MESSAGE.Set_Token('REQ_ID', CS_SERVICEREQUEST_PVT.user_hooks_rec.request_id);
2592: FND_MSG_PUB.ADD;
2593: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2594: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
2595: END IF;
2596: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2597: 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);
2590: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_REQ_ID_INVALID');
2591: FND_MESSAGE.Set_Token('REQ_ID', CS_SERVICEREQUEST_PVT.user_hooks_rec.request_id);
2592: FND_MSG_PUB.ADD;
2593: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2594: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
2595: END IF;
2596: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2597: 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);
2598: END IF;
2592: FND_MSG_PUB.ADD;
2593: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2594: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
2595: END IF;
2596: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2597: 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);
2598: END IF;
2599: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2600: END IF; -- UE Not Found
2593: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2594: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
2595: END IF;
2596: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2597: 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);
2598: END IF;
2599: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2600: END IF; -- UE Not Found
2601: CLOSE get_ue_dtls_csr;
2600: END IF; -- UE Not Found
2601: CLOSE get_ue_dtls_csr;
2602: END IF; -- CMRO Type SR
2603:
2604: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2605: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
2606: END IF;
2607:
2608: END Validate_Request_For_Update;
2601: CLOSE get_ue_dtls_csr;
2602: END IF; -- CMRO Type SR
2603:
2604: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2605: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
2606: END IF;
2607:
2608: END Validate_Request_For_Update;
2609:
2631: l_dummy VARCHAR2(1);
2632: l_temp_return_status VARCHAR2(1);
2633:
2634: BEGIN
2635: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2636: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
2637: END IF;
2638:
2639: -- Ensure that the SR is not closed
2632: l_temp_return_status VARCHAR2(1);
2633:
2634: BEGIN
2635: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2636: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
2637: END IF;
2638:
2639: -- Ensure that the SR is not closed
2640: IF (CS_SERVICEREQUEST_PVT.user_hooks_rec.status_flag = G_SR_CLOSED_FLAG) THEN
2639: -- Ensure that the SR is not closed
2640: IF (CS_SERVICEREQUEST_PVT.user_hooks_rec.status_flag = G_SR_CLOSED_FLAG) THEN
2641: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_REQ_CLOSED');
2642: FND_MSG_PUB.ADD;
2643: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2644: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
2645: END IF;
2646: RAISE FND_API.G_EXC_ERROR;
2647: END IF;
2640: IF (CS_SERVICEREQUEST_PVT.user_hooks_rec.status_flag = G_SR_CLOSED_FLAG) THEN
2641: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_REQ_CLOSED');
2642: FND_MSG_PUB.ADD;
2643: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2644: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
2645: END IF;
2646: RAISE FND_API.G_EXC_ERROR;
2647: END IF;
2648:
2646: RAISE FND_API.G_EXC_ERROR;
2647: END IF;
2648:
2649: IF (NVL(CS_SERVICEREQUEST_PVT.user_hooks_rec.new_type_cmro_flag, G_NO_FLAG) = G_NO_FLAG) THEN
2650: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2651: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'CMRO to Non-CMRO type conversion');
2652: END IF;
2653: -- CMRO to Non-CMRO type
2654: -- apattark start changes for bug #9166304
2647: END IF;
2648:
2649: IF (NVL(CS_SERVICEREQUEST_PVT.user_hooks_rec.new_type_cmro_flag, G_NO_FLAG) = G_NO_FLAG) THEN
2650: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2651: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'CMRO to Non-CMRO type conversion');
2652: END IF;
2653: -- CMRO to Non-CMRO type
2654: -- apattark start changes for bug #9166304
2655: IF (p_defer_from_ue_id IS NOT NULL) THEN
2652: END IF;
2653: -- CMRO to Non-CMRO type
2654: -- apattark start changes for bug #9166304
2655: IF (p_defer_from_ue_id IS NOT NULL) THEN
2656: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2657: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'UE was deferred.');
2658: END IF;
2659: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_REQ_DEFERRED');
2660: FND_MSG_PUB.ADD;
2653: -- CMRO to Non-CMRO type
2654: -- apattark start changes for bug #9166304
2655: IF (p_defer_from_ue_id IS NOT NULL) THEN
2656: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2657: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'UE was deferred.');
2658: END IF;
2659: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_REQ_DEFERRED');
2660: FND_MSG_PUB.ADD;
2661: RAISE FND_API.G_EXC_ERROR;
2667: FETCH get_tasks_for_ue_csr INTO l_dummy;
2668: IF (get_tasks_for_ue_csr%FOUND) THEN
2669: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_ASGND_TO_VISIT');
2670: FND_MSG_PUB.ADD;
2671: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2672: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
2673: END IF;
2674: ELSE
2675: -- This condition of a (originally) CMRO SR in Planned Status but not associated
2668: IF (get_tasks_for_ue_csr%FOUND) THEN
2669: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_ASGND_TO_VISIT');
2670: FND_MSG_PUB.ADD;
2671: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2672: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
2673: END IF;
2674: ELSE
2675: -- This condition of a (originally) CMRO SR in Planned Status but not associated
2676: -- to a visit is not possible
2673: END IF;
2674: ELSE
2675: -- This condition of a (originally) CMRO SR in Planned Status but not associated
2676: -- to a visit is not possible
2677: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2678: 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');
2679: END IF;
2680: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_REQ_PLANNED');
2681: FND_MSG_PUB.ADD;
2674: ELSE
2675: -- This condition of a (originally) CMRO SR in Planned Status but not associated
2676: -- to a visit is not possible
2677: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2678: 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');
2679: END IF;
2680: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_REQ_PLANNED');
2681: FND_MSG_PUB.ADD;
2682: END IF; -- tasks found
2687: -- Delete all the dependent UEs and UE relationships
2688: FOR rel_and_ue_id_rec in get_all_ue_and_rel_ids_csr LOOP
2689: -- First delete the relationship
2690: AHL_UE_RELATIONSHIPS_PKG.DELETE_ROW(X_UE_RELATIONSHIP_ID => rel_and_ue_id_rec.UE_RELATIONSHIP_ID);
2691: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2692: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Deleted Relationship with UE_RELATIONSHIP_ID = ' || rel_and_ue_id_rec.UE_RELATIONSHIP_ID);
2693: END IF;
2694: -- Next delete the unit effectivity
2695: AHL_UNIT_EFFECTIVITIES_PKG.DELETE_ROW(X_UNIT_EFFECTIVITY_ID => rel_and_ue_id_rec.RELATED_UE_ID);
2688: FOR rel_and_ue_id_rec in get_all_ue_and_rel_ids_csr LOOP
2689: -- First delete the relationship
2690: AHL_UE_RELATIONSHIPS_PKG.DELETE_ROW(X_UE_RELATIONSHIP_ID => rel_and_ue_id_rec.UE_RELATIONSHIP_ID);
2691: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2692: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Deleted Relationship with UE_RELATIONSHIP_ID = ' || rel_and_ue_id_rec.UE_RELATIONSHIP_ID);
2693: END IF;
2694: -- Next delete the unit effectivity
2695: AHL_UNIT_EFFECTIVITIES_PKG.DELETE_ROW(X_UNIT_EFFECTIVITY_ID => rel_and_ue_id_rec.RELATED_UE_ID);
2696: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2692: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Deleted Relationship with UE_RELATIONSHIP_ID = ' || rel_and_ue_id_rec.UE_RELATIONSHIP_ID);
2693: END IF;
2694: -- Next delete the unit effectivity
2695: AHL_UNIT_EFFECTIVITIES_PKG.DELETE_ROW(X_UNIT_EFFECTIVITY_ID => rel_and_ue_id_rec.RELATED_UE_ID);
2696: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2697: 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);
2698: END IF;
2699: END LOOP;
2700:
2693: END IF;
2694: -- Next delete the unit effectivity
2695: AHL_UNIT_EFFECTIVITIES_PKG.DELETE_ROW(X_UNIT_EFFECTIVITY_ID => rel_and_ue_id_rec.RELATED_UE_ID);
2696: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2697: 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);
2698: END IF;
2699: END LOOP;
2700:
2701: -- Delete the SR's Unit Effectivity
2699: END LOOP;
2700:
2701: -- Delete the SR's Unit Effectivity
2702: AHL_UNIT_EFFECTIVITIES_PKG.DELETE_ROW(X_UNIT_EFFECTIVITY_ID => p_sr_ue_id);
2703: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2704: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Deleted Unit Effectivity with UNIT_EFFECTIVITY_ID = ' || p_sr_ue_id);
2705: END IF;
2706: END IF; -- Planned or Not
2707: ELSE
2700:
2701: -- Delete the SR's Unit Effectivity
2702: AHL_UNIT_EFFECTIVITIES_PKG.DELETE_ROW(X_UNIT_EFFECTIVITY_ID => p_sr_ue_id);
2703: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2704: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Deleted Unit Effectivity with UNIT_EFFECTIVITY_ID = ' || p_sr_ue_id);
2705: END IF;
2706: END IF; -- Planned or Not
2707: ELSE
2708: -- Non-CMRO to CMRO type
2705: END IF;
2706: END IF; -- Planned or Not
2707: ELSE
2708: -- Non-CMRO to CMRO type
2709: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2710: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Non-CMRO to CMRO type conversion');
2711: END IF;
2712: IF (CS_SERVICEREQUEST_PVT.user_hooks_rec.status_id = G_SR_PLANNED_STATUS_ID) THEN
2713: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_REQ_PLANNED');
2706: END IF; -- Planned or Not
2707: ELSE
2708: -- Non-CMRO to CMRO type
2709: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2710: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Non-CMRO to CMRO type conversion');
2711: END IF;
2712: IF (CS_SERVICEREQUEST_PVT.user_hooks_rec.status_id = G_SR_PLANNED_STATUS_ID) THEN
2713: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_REQ_PLANNED');
2714: FND_MSG_PUB.ADD;
2711: END IF;
2712: IF (CS_SERVICEREQUEST_PVT.user_hooks_rec.status_id = G_SR_PLANNED_STATUS_ID) THEN
2713: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_REQ_PLANNED');
2714: FND_MSG_PUB.ADD;
2715: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2716: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
2717: END IF;
2718: RAISE FND_API.G_EXC_ERROR;
2719: ELSE
2712: IF (CS_SERVICEREQUEST_PVT.user_hooks_rec.status_id = G_SR_PLANNED_STATUS_ID) THEN
2713: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_REQ_PLANNED');
2714: FND_MSG_PUB.ADD;
2715: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2716: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
2717: END IF;
2718: RAISE FND_API.G_EXC_ERROR;
2719: ELSE
2720: -- Not yet Planned, but open: Call Create_SR_Init_Effectivity
2717: END IF;
2718: RAISE FND_API.G_EXC_ERROR;
2719: ELSE
2720: -- Not yet Planned, but open: Call Create_SR_Init_Effectivity
2721: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2722: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'About to call Create_SR_Unit_Effectivity.');
2723: END IF;
2724: Create_SR_Unit_Effectivity(x_return_status => l_temp_return_status);
2725: /* For Testing
2718: RAISE FND_API.G_EXC_ERROR;
2719: ELSE
2720: -- Not yet Planned, but open: Call Create_SR_Init_Effectivity
2721: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2722: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'About to call Create_SR_Unit_Effectivity.');
2723: END IF;
2724: Create_SR_Unit_Effectivity(x_return_status => l_temp_return_status);
2725: /* For Testing
2726: UMP_SR_TEST.Create_SR_Unit_Effectivity(x_return_status => l_temp_return_status);
2725: /* For Testing
2726: UMP_SR_TEST.Create_SR_Unit_Effectivity(x_return_status => l_temp_return_status);
2727: */
2728: IF (l_temp_return_status = FND_API.G_RET_STS_SUCCESS) THEN
2729: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2730: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Create_SR_Unit_Effectivity Returned Success.');
2731: END IF;
2732: ELSIF (l_temp_return_status = FND_API.G_RET_STS_ERROR) THEN
2733: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2726: UMP_SR_TEST.Create_SR_Unit_Effectivity(x_return_status => l_temp_return_status);
2727: */
2728: IF (l_temp_return_status = FND_API.G_RET_STS_SUCCESS) THEN
2729: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2730: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Create_SR_Unit_Effectivity Returned Success.');
2731: END IF;
2732: ELSIF (l_temp_return_status = FND_API.G_RET_STS_ERROR) THEN
2733: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2734: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Create_SR_Unit_Effectivity Returned User Error.');
2729: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2730: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Create_SR_Unit_Effectivity Returned Success.');
2731: END IF;
2732: ELSIF (l_temp_return_status = FND_API.G_RET_STS_ERROR) THEN
2733: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2734: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Create_SR_Unit_Effectivity Returned User Error.');
2735: END IF;
2736: RAISE FND_API.G_EXC_ERROR;
2737: ELSE
2730: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Create_SR_Unit_Effectivity Returned Success.');
2731: END IF;
2732: ELSIF (l_temp_return_status = FND_API.G_RET_STS_ERROR) THEN
2733: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2734: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Create_SR_Unit_Effectivity Returned User Error.');
2735: END IF;
2736: RAISE FND_API.G_EXC_ERROR;
2737: ELSE
2738: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2734: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Create_SR_Unit_Effectivity Returned User Error.');
2735: END IF;
2736: RAISE FND_API.G_EXC_ERROR;
2737: ELSE
2738: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2739: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Create_SR_Unit_Effectivity Returned Unexpected Error.');
2740: END IF;
2741: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2742: END IF;
2735: END IF;
2736: RAISE FND_API.G_EXC_ERROR;
2737: ELSE
2738: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2739: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Create_SR_Unit_Effectivity Returned Unexpected Error.');
2740: END IF;
2741: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2742: END IF;
2743: END IF; -- Planned or not
2742: END IF;
2743: END IF; -- Planned or not
2744: END IF; -- CMRO to Non-CMRO or vice-versa
2745:
2746: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2747: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
2748: END IF;
2749:
2750: END Handle_Type_Change;
2743: END IF; -- Planned or not
2744: END IF; -- CMRO to Non-CMRO or vice-versa
2745:
2746: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2747: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
2748: END IF;
2749:
2750: END Handle_Type_Change;
2751:
2781: l_msg_count NUMBER;
2782: l_calc_due_date DATE;
2783:
2784: BEGIN
2785: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2786: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
2787: END IF;
2788:
2789: -- Get the original (old) values and lock the row
2782: l_calc_due_date DATE;
2783:
2784: BEGIN
2785: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2786: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
2787: END IF;
2788:
2789: -- Get the original (old) values and lock the row
2790: OPEN get_and_lock_ue_dtls_csr; -- Will throw exception (ORA-00054) if cannot acquire lock
2805: x_status_changed => l_status_changed,
2806: x_new_ue_status => l_new_ue_status);
2807:
2808: -- Handle Expected Resolution Date (Due Date) Change
2809: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2810: 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);
2811: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'l_old_status = ' || l_old_status || ', l_new_ue_status = ' || l_new_ue_status);
2812: END IF;
2813:
2806: x_new_ue_status => l_new_ue_status);
2807:
2808: -- Handle Expected Resolution Date (Due Date) Change
2809: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2810: 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);
2811: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'l_old_status = ' || l_old_status || ', l_new_ue_status = ' || l_new_ue_status);
2812: END IF;
2813:
2814: -- added for Complex Assembly Enh: Initialize a SR.
2807:
2808: -- Handle Expected Resolution Date (Due Date) Change
2809: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2810: 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);
2811: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'l_old_status = ' || l_old_status || ', l_new_ue_status = ' || l_new_ue_status);
2812: END IF;
2813:
2814: -- added for Complex Assembly Enh: Initialize a SR.
2815: IF (l_old_status = 'INIT-DUE' AND (l_new_ue_status IS NULL OR l_new_ue_status <> G_UMP_SR_CLOSED_STATUS)) THEN
2845: END IF;
2846:
2847: IF (l_instance_changed = true OR l_status_changed = true OR l_due_date_changed = true) THEN
2848: -- Apply changes to the SR Unit Effectivity
2849: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2850: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Changes have happened.');
2851: IF (l_instance_changed = true) THEN
2852: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'l_instance_changed = Y');
2853: ELSE
2846:
2847: IF (l_instance_changed = true OR l_status_changed = true OR l_due_date_changed = true) THEN
2848: -- Apply changes to the SR Unit Effectivity
2849: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2850: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Changes have happened.');
2851: IF (l_instance_changed = true) THEN
2852: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'l_instance_changed = Y');
2853: ELSE
2854: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'l_instance_changed = N');
2848: -- Apply changes to the SR Unit Effectivity
2849: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2850: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Changes have happened.');
2851: IF (l_instance_changed = true) THEN
2852: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'l_instance_changed = Y');
2853: ELSE
2854: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'l_instance_changed = N');
2855: END IF;
2856: IF (l_status_changed = true) THEN
2850: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Changes have happened.');
2851: IF (l_instance_changed = true) THEN
2852: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'l_instance_changed = Y');
2853: ELSE
2854: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'l_instance_changed = N');
2855: END IF;
2856: IF (l_status_changed = true) THEN
2857: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'l_status_changed = Y');
2858: ELSE
2853: ELSE
2854: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'l_instance_changed = N');
2855: END IF;
2856: IF (l_status_changed = true) THEN
2857: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'l_status_changed = Y');
2858: ELSE
2859: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'l_status_changed = N');
2860: END IF;
2861: IF (l_due_date_changed = true) THEN
2855: END IF;
2856: IF (l_status_changed = true) THEN
2857: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'l_status_changed = Y');
2858: ELSE
2859: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'l_status_changed = N');
2860: END IF;
2861: IF (l_due_date_changed = true) THEN
2862: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'l_due_date_changed = Y');
2863: ELSE
2858: ELSE
2859: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'l_status_changed = N');
2860: END IF;
2861: IF (l_due_date_changed = true) THEN
2862: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'l_due_date_changed = Y');
2863: ELSE
2864: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'l_due_date_changed = N');
2865: END IF;
2866: END IF;
2860: END IF;
2861: IF (l_due_date_changed = true) THEN
2862: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'l_due_date_changed = Y');
2863: ELSE
2864: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'l_due_date_changed = N');
2865: END IF;
2866: END IF;
2867: Update_SR_Unit_Effectivity(p_sr_ue_id => p_sr_ue_id,
2868: p_due_date_flag => l_due_date_changed,
2882: p_new_due_date => l_new_due_date);
2883: END IF;
2884: ELSE
2885: -- No Changes to Update
2886: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2887: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'No change to instance, due date or status');
2888: END IF;
2889: END IF;
2890:
2883: END IF;
2884: ELSE
2885: -- No Changes to Update
2886: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2887: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'No change to instance, due date or status');
2888: END IF;
2889: END IF;
2890:
2891: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2887: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'No change to instance, due date or status');
2888: END IF;
2889: END IF;
2890:
2891: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2892: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
2893: END IF;
2894:
2895: -- Call this API to Sync up SR summary updates with Workorder Description
2888: END IF;
2889: END IF;
2890:
2891: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2892: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
2893: END IF;
2894:
2895: -- Call this API to Sync up SR summary updates with Workorder Description
2896: Handle_Summary_Update(
2918: l_temp_count NUMBER := 0;
2919: l_new_instance_id NUMBER := CS_SERVICEREQUEST_PVT.USER_HOOKS_REC.customer_product_id;
2920:
2921: BEGIN
2922: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2923: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
2924: END IF;
2925: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2926: 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);
2919: l_new_instance_id NUMBER := CS_SERVICEREQUEST_PVT.USER_HOOKS_REC.customer_product_id;
2920:
2921: BEGIN
2922: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2923: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
2924: END IF;
2925: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2926: 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);
2927: END IF;
2921: BEGIN
2922: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2923: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
2924: END IF;
2925: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2926: 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);
2927: END IF;
2928: -- Initialize the Instance Changed Flag to false
2929: x_instance_changed := false;
2922: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2923: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
2924: END IF;
2925: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2926: 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);
2927: END IF;
2928: -- Initialize the Instance Changed Flag to false
2929: x_instance_changed := false;
2930:
2931: IF (l_new_instance_id IS NULL) THEN
2932: p_x_valid_flag := false;
2933: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_INST_NULL');
2934: FND_MSG_PUB.ADD;
2935: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2936: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
2937: END IF;
2938: ELSIF (l_new_instance_id <> p_old_instance_id) THEN
2939: -- Instance has changed: Validate
2932: p_x_valid_flag := false;
2933: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_INST_NULL');
2934: FND_MSG_PUB.ADD;
2935: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2936: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
2937: END IF;
2938: ELSIF (l_new_instance_id <> p_old_instance_id) THEN
2939: -- Instance has changed: Validate
2940: --apattark begin changes for bug #9166304
2941: IF (p_defer_from_ue_id IS NOT NULL) THEN
2942: p_x_valid_flag := false;
2943: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_INST_CHG_DEFERRED');
2944: FND_MSG_PUB.ADD;
2945: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2946: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
2947: END IF;
2948: END IF;
2949: --apattark end changes for bug #9166304
2942: p_x_valid_flag := false;
2943: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_INST_CHG_DEFERRED');
2944: FND_MSG_PUB.ADD;
2945: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2946: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
2947: END IF;
2948: END IF;
2949: --apattark end changes for bug #9166304
2950: IF (CS_SERVICEREQUEST_PVT.USER_HOOKS_REC.status_flag = G_SR_CLOSED_FLAG) THEN
2950: IF (CS_SERVICEREQUEST_PVT.USER_HOOKS_REC.status_flag = G_SR_CLOSED_FLAG) THEN
2951: p_x_valid_flag := false;
2952: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_INST_CHG_CLOSED');
2953: FND_MSG_PUB.ADD;
2954: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2955: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
2956: END IF;
2957:
2958: ELSIF (CS_SERVICEREQUEST_PVT.user_hooks_rec.status_id = G_SR_PLANNED_STATUS_ID) THEN
2951: p_x_valid_flag := false;
2952: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_INST_CHG_CLOSED');
2953: FND_MSG_PUB.ADD;
2954: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2955: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
2956: END IF;
2957:
2958: ELSIF (CS_SERVICEREQUEST_PVT.user_hooks_rec.status_id = G_SR_PLANNED_STATUS_ID) THEN
2959: p_x_valid_flag := false;
2958: ELSIF (CS_SERVICEREQUEST_PVT.user_hooks_rec.status_id = G_SR_PLANNED_STATUS_ID) THEN
2959: p_x_valid_flag := false;
2960: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_INST_CHG_PLANNED');
2961: FND_MSG_PUB.ADD;
2962: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2963: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
2964: END IF;
2965: ELSE
2966: OPEN get_rel_count_csr;
2959: p_x_valid_flag := false;
2960: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_INST_CHG_PLANNED');
2961: FND_MSG_PUB.ADD;
2962: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2963: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
2964: END IF;
2965: ELSE
2966: OPEN get_rel_count_csr;
2967: FETCH get_rel_count_csr INTO l_temp_count;
2969: IF (l_temp_count > 0) THEN
2970: p_x_valid_flag := false;
2971: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_INST_CHG_HAS_MRS');
2972: FND_MSG_PUB.ADD;
2973: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2974: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
2975: END IF;
2976: ELSE
2977: -- All validations passed
2970: p_x_valid_flag := false;
2971: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_INST_CHG_HAS_MRS');
2972: FND_MSG_PUB.ADD;
2973: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2974: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
2975: END IF;
2976: ELSE
2977: -- All validations passed
2978: x_instance_changed := true;
2975: END IF;
2976: ELSE
2977: -- All validations passed
2978: x_instance_changed := true;
2979: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2980: 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.');
2981: END IF;
2982: END IF; -- Status Check
2983: END IF; -- Old <> New Check
2976: ELSE
2977: -- All validations passed
2978: x_instance_changed := true;
2979: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2980: 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.');
2981: END IF;
2982: END IF; -- Status Check
2983: END IF; -- Old <> New Check
2984: END IF; -- New Instance Null Check
2982: END IF; -- Status Check
2983: END IF; -- Old <> New Check
2984: END IF; -- New Instance Null Check
2985:
2986: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2987: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
2988: END IF;
2989:
2990: END Handle_Instance_Change;
2983: END IF; -- Old <> New Check
2984: END IF; -- New Instance Null Check
2985:
2986: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2987: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
2988: END IF;
2989:
2990: END Handle_Instance_Change;
2991:
3013: l_dummy VARCHAR2(1);
3014: l_assigned BOOLEAN;
3015:
3016: BEGIN
3017: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3018: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
3019: END IF;
3020: -- Initialize the Status Changed Flag to false
3021: x_status_changed := false;
3014: l_assigned BOOLEAN;
3015:
3016: BEGIN
3017: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3018: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
3019: END IF;
3020: -- Initialize the Status Changed Flag to false
3021: x_status_changed := false;
3022:
3030: -- UE Neither accomplished nor terminated
3031: p_x_valid_flag := false;
3032: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_TASK_NOT_ACC');
3033: FND_MSG_PUB.ADD;
3034: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3035: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
3036: END IF;
3037: ELSE
3038: -- Unit Effectivities already accomplished: No impact on UMP, Allow the SR to be closed.
3031: p_x_valid_flag := false;
3032: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_TASK_NOT_ACC');
3033: FND_MSG_PUB.ADD;
3034: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3035: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
3036: END IF;
3037: ELSE
3038: -- Unit Effectivities already accomplished: No impact on UMP, Allow the SR to be closed.
3039: null;
3047: -- SR has associated MRs, but the SR UE has not been assigned to any visit.
3048: p_x_valid_flag := false;
3049: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_MRS_NOT_ACC');
3050: FND_MSG_PUB.ADD;
3051: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3052: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
3053: END IF;
3054: ELSE
3055: -- No associated MRs
3048: p_x_valid_flag := false;
3049: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_MRS_NOT_ACC');
3050: FND_MSG_PUB.ADD;
3051: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3052: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
3053: END IF;
3054: ELSE
3055: -- No associated MRs
3056: IF ((p_old_ue_status IS NULL) OR (p_old_ue_status <> G_UMP_SR_CLOSED_STATUS)) THEN
3071: -- Attempting to open an SR whose UE is already SR_CLOSED.
3072: p_x_valid_flag := false;
3073: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_UE_SR_CLSD');
3074: FND_MSG_PUB.ADD;
3075: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3076: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
3077: END IF;
3078: ELSE
3079: OPEN get_tasks_for_ue_csr;
3072: p_x_valid_flag := false;
3073: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_UE_SR_CLSD');
3074: FND_MSG_PUB.ADD;
3075: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3076: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
3077: END IF;
3078: ELSE
3079: OPEN get_tasks_for_ue_csr;
3080: FETCH get_tasks_for_ue_csr INTO l_dummy;
3089: -- SR is being moved to Planned status without assigning SR UE to any visit
3090: p_x_valid_flag := false;
3091: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_NOT_ASSND');
3092: FND_MSG_PUB.ADD;
3093: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3094: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
3095: END IF;
3096: END IF; -- Not assigned
3097: ELSE
3090: p_x_valid_flag := false;
3091: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_NOT_ASSND');
3092: FND_MSG_PUB.ADD;
3093: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3094: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
3095: END IF;
3096: END IF; -- Not assigned
3097: ELSE
3098: IF (l_assigned = true) THEN
3099: -- SR is being moved to a not-planned status while SR UE has already been assigned to a visit
3100: p_x_valid_flag := false;
3101: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_ALREADY_ASSND');
3102: FND_MSG_PUB.ADD;
3103: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3104: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
3105: END IF;
3106: END IF; -- Already Assigned
3107: END IF; -- SR is in Planned Status or Not
3100: p_x_valid_flag := false;
3101: FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_ALREADY_ASSND');
3102: FND_MSG_PUB.ADD;
3103: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3104: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
3105: END IF;
3106: END IF; -- Already Assigned
3107: END IF; -- SR is in Planned Status or Not
3108: END IF; -- Old UE Status SR-CLOSED or not
3107: END IF; -- SR is in Planned Status or Not
3108: END IF; -- Old UE Status SR-CLOSED or not
3109: END IF; -- If SR is Closed or not
3110:
3111: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3112: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
3113: END IF;
3114:
3115: END Handle_Status_Change;
3108: END IF; -- Old UE Status SR-CLOSED or not
3109: END IF; -- If SR is Closed or not
3110:
3111: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3112: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
3113: END IF;
3114:
3115: END Handle_Status_Change;
3116:
3193: BEGIN
3194:
3195: l_count := 1;
3196:
3197: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3198: FND_LOG.STRING(
3199: FND_LOG.LEVEL_STATEMENT,
3200: L_DEBUG_KEY,
3201: 'Entering the procedure Handle_Summary_Update ..'
3194:
3195: l_count := 1;
3196:
3197: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3198: FND_LOG.STRING(
3199: FND_LOG.LEVEL_STATEMENT,
3200: L_DEBUG_KEY,
3201: 'Entering the procedure Handle_Summary_Update ..'
3202: );
3195: l_count := 1;
3196:
3197: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3198: FND_LOG.STRING(
3199: FND_LOG.LEVEL_STATEMENT,
3200: L_DEBUG_KEY,
3201: 'Entering the procedure Handle_Summary_Update ..'
3202: );
3203: END IF;
3213: OPEN c_get_SR_details(p_sr_ue_id);
3214: FETCH c_get_SR_details INTO l_sr_summary, l_sr_type;
3215: CLOSE c_get_SR_details;
3216: --l_sr_summary := CS_SERVICEREQUEST_PVT.USER_HOOKS_REC.summary;
3217: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3218:
3219: FND_LOG.STRING(
3220: FND_LOG.LEVEL_STATEMENT,
3221: L_DEBUG_KEY,
3215: CLOSE c_get_SR_details;
3216: --l_sr_summary := CS_SERVICEREQUEST_PVT.USER_HOOKS_REC.summary;
3217: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3218:
3219: FND_LOG.STRING(
3220: FND_LOG.LEVEL_STATEMENT,
3221: L_DEBUG_KEY,
3222: 'l_sr_summary -->'||l_sr_summary
3223: );
3216: --l_sr_summary := CS_SERVICEREQUEST_PVT.USER_HOOKS_REC.summary;
3217: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3218:
3219: FND_LOG.STRING(
3220: FND_LOG.LEVEL_STATEMENT,
3221: L_DEBUG_KEY,
3222: 'l_sr_summary -->'||l_sr_summary
3223: );
3224: FND_LOG.STRING(
3220: FND_LOG.LEVEL_STATEMENT,
3221: L_DEBUG_KEY,
3222: 'l_sr_summary -->'||l_sr_summary
3223: );
3224: FND_LOG.STRING(
3225: FND_LOG.LEVEL_STATEMENT,
3226: L_DEBUG_KEY,
3227: 'l_sr_type -->'||l_sr_type
3228: );
3221: L_DEBUG_KEY,
3222: 'l_sr_summary -->'||l_sr_summary
3223: );
3224: FND_LOG.STRING(
3225: FND_LOG.LEVEL_STATEMENT,
3226: L_DEBUG_KEY,
3227: 'l_sr_type -->'||l_sr_type
3228: );
3229:
3236: -- Get the workorders for SR if it exists.
3237: FOR ue_wo_rec IN c_get_ue_wo(p_sr_ue_id)
3238: LOOP
3239: IF ue_wo_rec.description <> l_wo_description THEN
3240: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3241: FND_LOG.STRING(
3242: FND_LOG.LEVEL_STATEMENT,
3243: L_DEBUG_KEY,
3244: 'ue_wo_rec.workorder_id -->'||ue_wo_rec.workorder_id
3237: FOR ue_wo_rec IN c_get_ue_wo(p_sr_ue_id)
3238: LOOP
3239: IF ue_wo_rec.description <> l_wo_description THEN
3240: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3241: FND_LOG.STRING(
3242: FND_LOG.LEVEL_STATEMENT,
3243: L_DEBUG_KEY,
3244: 'ue_wo_rec.workorder_id -->'||ue_wo_rec.workorder_id
3245: );
3238: LOOP
3239: IF ue_wo_rec.description <> l_wo_description THEN
3240: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3241: FND_LOG.STRING(
3242: FND_LOG.LEVEL_STATEMENT,
3243: L_DEBUG_KEY,
3244: 'ue_wo_rec.workorder_id -->'||ue_wo_rec.workorder_id
3245: );
3246: END IF;
3273:
3274: IF l_return_status <> FND_API.G_RET_STS_SUCCESS
3275: THEN
3276: RAISE FND_API.G_EXC_ERROR;
3277: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3278: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
3279: END IF;
3280: END IF;
3281: END IF;
3274: IF l_return_status <> FND_API.G_RET_STS_SUCCESS
3275: THEN
3276: RAISE FND_API.G_EXC_ERROR;
3277: IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3278: FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
3279: END IF;
3280: END IF;
3281: END IF;
3282:
3279: END IF;
3280: END IF;
3281: END IF;
3282:
3283: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3284: FND_LOG.STRING(
3285: FND_LOG.LEVEL_STATEMENT,
3286: L_DEBUG_KEY,
3287: 'Exiting the procedure Handle_Summary_Update ..'
3280: END IF;
3281: END IF;
3282:
3283: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3284: FND_LOG.STRING(
3285: FND_LOG.LEVEL_STATEMENT,
3286: L_DEBUG_KEY,
3287: 'Exiting the procedure Handle_Summary_Update ..'
3288: );
3281: END IF;
3282:
3283: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3284: FND_LOG.STRING(
3285: FND_LOG.LEVEL_STATEMENT,
3286: L_DEBUG_KEY,
3287: 'Exiting the procedure Handle_Summary_Update ..'
3288: );
3289: END IF;