[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE update_record (p_temp_mr_rec IN ahl_temp_unit_effectivities%ROWTYPE,
p_mr_rec IN ahl_unit_effectivities_app_v%ROWTYPE);
PROCEDURE update_group (p_temp_mr_rec IN ahl_temp_unit_effectivities%ROWTYPE,
p_mr_rec IN ahl_unit_effectivities_app_v%ROWTYPE);
PROCEDURE Delete_Sch_Materials(p_unit_effectivity_id IN NUMBER);
SELECT unit_effectivity_id,
csi_item_instance_id,
MR_header_id,
due_date,
mr_interval_id,
mr_effectivity_id,
due_counter_value,
parent_csi_item_instance_id,
parent_mr_header_id,
orig_csi_item_instance_id,
orig_mr_header_id,
forecast_sequence,
repetitive_mr_flag,
tolerance_flag,
message_code,
service_line_id,
program_mr_header_id,
earliest_due_date,
latest_due_date,
counter_id
FROM ahl_temp_unit_effectivities
WHERE MR_header_id = nvl(orig_mr_header_id, mr_header_id) AND
csi_item_instance_id = nvl(orig_csi_item_instance_id,csi_item_instance_id)
ORDER by forecast_sequence ASC
FOR UPDATE OF unit_effectivity_id;
SELECT preceding_mr_header_id, preceding_csi_item_instance_id,
preceding_forecast_seq, unit_effectivity_id
FROM ahl_temp_unit_effectivities
WHERE preceding_mr_header_id IS NOT NULL AND
preceding_csi_item_instance_id IS NOT NULL AND
preceding_forecast_seq IS NOT NULL;
SELECT unit_effectivity_id
FROM ahl_temp_unit_effectivities
WHERE mr_header_id = p_preceding_mr_header_id AND
csi_item_instance_id = p_preceding_item_instance AND
forecast_sequence = p_preceding_forecast_seq;
SELECT
UNIT_EFFECTIVITY_ID ,
CSI_ITEM_INSTANCE_ID,
MR_INTERVAL_ID,
MR_EFFECTIVITY_ID ,
MR_HEADER_ID,
STATUS_CODE ,
DUE_DATE ,
DUE_COUNTER_VALUE ,
FORECAST_SEQUENCE ,
REPETITIVE_MR_FLAG ,
TOLERANCE_FLAG ,
REMARKS ,
MESSAGE_CODE ,
PRECEDING_UE_ID ,
DATE_RUN ,
SET_DUE_DATE ,
ACCOMPLISHED_DATE ,
SERVICE_LINE_ID,
PROGRAM_MR_HEADER_ID,
CANCEL_REASON_CODE,
EARLIEST_DUE_DATE,
LATEST_DUE_DATE,
defer_from_ue_id,
cs_incident_id,
qa_collection_id,
orig_deferral_ue_id,
application_usg_code,
object_type,
counter_id,
manually_planned_flag,
LOG_SERIES_CODE,
LOG_SERIES_NUMBER,
FLIGHT_NUMBER,
MEL_CDL_TYPE_CODE,
POSITION_PATH_ID,
ATA_CODE,
UNIT_CONFIG_HEADER_ID,
ATTRIBUTE_CATEGORY ,
ATTRIBUTE1,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
ATTRIBUTE9 ,
ATTRIBUTE10,
ATTRIBUTE11 ,
ATTRIBUTE12 ,
ATTRIBUTE13 ,
ATTRIBUTE14 ,
ATTRIBUTE15 ,
OBJECT_VERSION_NUMBER ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN
--FROM ahl_unit_effectivities_app_v
FROM ahl_unit_effectivities_vl
WHERE unit_effectivity_id = p_unit_effectivity_id;
SELECT
UNIT_EFFECTIVITY_ID,
CSI_ITEM_INSTANCE_ID,
MR_HEADER_ID,
STATUS_CODE,
MR_INTERVAL_ID,
MR_EFFECTIVITY_ID ,
DUE_DATE ,
DUE_COUNTER_VALUE ,
FORECAST_SEQUENCE ,
REPETITIVE_MR_FLAG ,
TOLERANCE_FLAG ,
REMARKS ,
MESSAGE_CODE ,
PRECEDING_UE_ID ,
DATE_RUN ,
SET_DUE_DATE ,
ACCOMPLISHED_DATE ,
SERVICE_LINE_ID,
PROGRAM_MR_HEADER_ID,
CANCEL_REASON_CODE,
EARLIEST_DUE_DATE,
LATEST_DUE_DATE,
defer_from_ue_id,
cs_incident_id,
qa_collection_id,
orig_deferral_ue_id,
application_usg_code,
object_type,
counter_id,
manually_planned_flag,
LOG_SERIES_CODE,
LOG_SERIES_NUMBER,
FLIGHT_NUMBER,
MEL_CDL_TYPE_CODE,
POSITION_PATH_ID,
ATA_CODE,
UNIT_CONFIG_HEADER_ID,
ATTRIBUTE_CATEGORY ,
ATTRIBUTE1,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
ATTRIBUTE9 ,
ATTRIBUTE10,
ATTRIBUTE11 ,
ATTRIBUTE12 ,
ATTRIBUTE13 ,
ATTRIBUTE14 ,
ATTRIBUTE15 ,
OBJECT_VERSION_NUMBER
FROM ahl_unit_effectivities_app_v ue
WHERE csi_item_instance_id = p_csi_item_instance_id AND
( status_code IS NULL OR
status_code NOT IN ('ACCOMPLISHED','TERMINATED',
'MR-TERMINATE', 'INIT-ACCOMPLISHED',
'DEFERRED', 'SR-CLOSED','CANCELLED'))
AND date_run < p_date_run
--AND defer_from_ue_id IS NULL
AND nvl(manually_planned_flag,'N') = 'N'
AND NOT EXISTS (SELECT 'x'
FROM ahl_ue_relationships
WHERE related_ue_id = ue.unit_effectivity_id);
SELECT ue_relationship_id, related_ue_id
FROM ahl_ue_relationships
WHERE relationship_code = 'PARENT' AND
originator_ue_id = p_unit_effectivity_id;
SELECT unit_deferral_id
--FROM ahl_unit_thresholds
FROM ahl_unit_deferrals_b
WHERE unit_effectivity_id = p_unit_effectivity_id
AND unit_deferral_type = 'INIT-DUE';
SELECT 'x'
FROM ahl_ue_relationships
WHERE ue_id = p_unit_effectivity_id;
l_delete_flag BOOLEAN;
SELECT 'x' from okc_k_lines_b OKCL, ahl_unit_effectivities_b UE
--WHERE NVL(NVL(DATE_TERMINATED,END_DATE),SYSDATE) >= SYSDATE
-- Fix for bug# 5517930
WHERE NVL(NVL(DATE_TERMINATED,END_DATE),SYSDATE+1) >= trunc(SYSDATE)
AND OKCL.id = UE.service_line_id
AND UE.unit_effectivity_id = p_unit_effectivity_id;
SELECT 'x'
FROM okc_k_lines_b OKCL, ahl_unit_effectivities_b UE
WHERE trunc(p_due_date) <= NVL(DATE_TERMINATED,END_DATE)
AND OKCL.id = UE.service_line_id
AND UE.unit_effectivity_id = p_unit_effectivity_id;
Update_group(l_temp_mr_rec,
l_mr_rec);
update_record(l_temp_mr_rec,
l_mr_rec);
UPDATE ahl_temp_unit_effectivities
SET unit_effectivity_id = l_temp_mr_rec.unit_effectivity_id
WHERE CURRENT OF temp_individual_mrs_csr;
AHL_UNIT_EFFECTIVITIES_PKG.Update_Row(
X_UNIT_EFFECTIVITY_ID => l_ue_rec.unit_effectivity_id,
X_CSI_ITEM_INSTANCE_ID => l_ue_rec.csi_item_instance_id,
X_MR_INTERVAL_ID => l_ue_rec.mr_interval_id,
X_MR_EFFECTIVITY_ID => l_ue_rec.mr_effectivity_id,
X_MR_HEADER_ID => l_ue_rec.mr_header_id,
X_STATUS_CODE => l_ue_rec.status_code,
X_DUE_DATE => l_ue_rec.due_date,
X_DUE_COUNTER_VALUE => l_ue_rec.due_counter_value,
X_FORECAST_SEQUENCE => l_ue_rec.forecast_sequence,
X_REPETITIVE_MR_FLAG => l_ue_rec.repetitive_mr_flag,
X_TOLERANCE_FLAG => l_ue_rec.tolerance_flag,
X_REMARKS => l_ue_rec.remarks,
X_MESSAGE_CODE => l_ue_rec.message_code,
X_PRECEDING_UE_ID => l_unit_effectivity_id,
X_DATE_RUN => l_ue_rec.date_run,
X_SET_DUE_DATE => l_ue_rec.set_due_date,
X_ACCOMPLISHED_DATE => l_ue_rec.accomplished_date,
X_SERVICE_LINE_ID => l_ue_rec.service_line_id,
X_PROGRAM_MR_HEADER_ID => l_ue_rec.program_mr_header_id,
X_CANCEL_REASON_CODE => l_ue_rec.cancel_reason_code,
X_EARLIEST_DUE_DATE => l_ue_rec.earliest_due_date,
X_LATEST_DUE_DATE => l_ue_rec.latest_due_date,
X_defer_from_ue_id => l_ue_rec.defer_from_ue_id,
X_cs_incident_id => l_ue_rec.cs_incident_id,
X_qa_collection_id => l_ue_rec.qa_collection_id,
X_orig_deferral_ue_id => l_ue_rec.orig_deferral_ue_id,
X_application_usg_code => l_ue_rec.application_usg_code,
X_object_type => l_ue_rec.object_type,
X_counter_id => l_ue_rec.counter_id,
X_MANUALLY_PLANNED_FLAG => l_ue_rec.MANUALLY_PLANNED_FLAG,
X_LOG_SERIES_CODE => l_ue_rec.log_series_code,
X_LOG_SERIES_NUMBER => l_ue_rec.log_series_number,
X_FLIGHT_NUMBER => l_ue_rec.flight_number,
X_MEL_CDL_TYPE_CODE => l_ue_rec.mel_cdl_type_code,
X_POSITION_PATH_ID => l_ue_rec.position_path_id,
X_ATA_CODE => l_ue_rec.ATA_CODE,
X_UNIT_CONFIG_HEADER_ID => l_ue_rec.unit_config_header_id,
X_ATTRIBUTE_CATEGORY => l_ue_rec.attribute_category,
X_ATTRIBUTE1 => l_ue_rec.attribute1,
X_ATTRIBUTE2 => l_ue_rec.attribute2,
X_ATTRIBUTE3 => l_ue_rec.attribute3,
X_ATTRIBUTE4 => l_ue_rec.attribute4,
X_ATTRIBUTE5 => l_ue_rec.attribute5,
X_ATTRIBUTE6 => l_ue_rec.attribute6,
X_ATTRIBUTE7 => l_ue_rec.attribute7,
X_ATTRIBUTE8 => l_ue_rec.attribute8,
X_ATTRIBUTE9 => l_ue_rec.attribute9,
X_ATTRIBUTE10 => l_ue_rec.attribute10,
X_ATTRIBUTE11 => l_ue_rec.attribute11,
X_ATTRIBUTE12 => l_ue_rec.attribute12,
X_ATTRIBUTE13 => l_ue_rec.attribute13,
X_ATTRIBUTE14 => l_ue_rec.attribute14,
X_ATTRIBUTE15 => l_ue_rec.attribute15,
X_OBJECT_VERSION_NUMBER => l_ue_rec.object_version_number, -- no change to this needed.
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => fnd_global.user_id,
X_LAST_UPDATE_LOGIN => fnd_global.login_id);
l_delete_flag := TRUE;
l_delete_flag := TRUE;
l_delete_flag := FALSE;
l_delete_flag := FALSE;
l_delete_flag := TRUE;
l_delete_flag := FALSE;
l_delete_flag := FALSE;
l_delete_flag := FALSE;
AHL_DEBUG_PUB.Debug('Service Line Expired for UE with due date < termination/end date. Will not delete');
AHL_UNIT_EFFECTIVITIES_PKG.Update_Row(
X_UNIT_EFFECTIVITY_ID => exception_rec.unit_effectivity_id,
X_CSI_ITEM_INSTANCE_ID => exception_rec.csi_item_instance_id,
X_MR_INTERVAL_ID => exception_rec.mr_interval_id,
X_MR_EFFECTIVITY_ID => exception_rec.mr_effectivity_id,
X_MR_HEADER_ID => exception_rec.mr_header_id,
X_STATUS_CODE => exception_rec.status_code,
X_DUE_DATE => exception_rec.due_date,
X_DUE_COUNTER_VALUE => exception_rec.due_counter_value,
X_FORECAST_SEQUENCE => exception_rec.forecast_sequence,
X_REPETITIVE_MR_FLAG => exception_rec.repetitive_mr_flag,
X_TOLERANCE_FLAG => exception_rec.tolerance_flag,
X_REMARKS => exception_rec.remarks,
X_MESSAGE_CODE => exception_rec.message_code,
X_PRECEDING_UE_ID => exception_rec.preceding_ue_id,
X_DATE_RUN => sysdate,
X_SET_DUE_DATE => exception_rec.set_due_date,
X_ACCOMPLISHED_DATE => exception_rec.accomplished_date,
X_SERVICE_LINE_ID => exception_rec.service_line_id,
X_PROGRAM_MR_HEADER_ID => exception_rec.program_mr_header_id,
X_CANCEL_REASON_CODE => exception_rec.cancel_reason_code,
X_EARLIEST_DUE_DATE => exception_rec.earliest_due_date,
X_LATEST_DUE_DATE => exception_rec.latest_due_date,
X_defer_from_ue_id => exception_rec.defer_from_ue_id,
X_cs_incident_id => exception_rec.cs_incident_id,
X_qa_collection_id => exception_rec.qa_collection_id,
X_orig_deferral_ue_id => exception_rec.orig_deferral_ue_id,
X_application_usg_code => exception_rec.application_usg_code,
X_object_type => exception_rec.object_type,
X_counter_id => exception_rec.counter_id,
X_MANUALLY_PLANNED_FLAG => exception_rec.MANUALLY_PLANNED_FLAG,
X_LOG_SERIES_CODE => exception_rec.log_series_code,
X_LOG_SERIES_NUMBER => exception_rec.log_series_number,
X_FLIGHT_NUMBER => exception_rec.flight_number,
X_MEL_CDL_TYPE_CODE => exception_rec.mel_cdl_type_code,
X_POSITION_PATH_ID => exception_rec.position_path_id,
X_ATA_CODE => exception_rec.ATA_CODE,
X_UNIT_CONFIG_HEADER_ID => exception_rec.unit_config_header_id,
X_ATTRIBUTE_CATEGORY => exception_rec.attribute_category,
X_ATTRIBUTE1 => exception_rec.attribute1,
X_ATTRIBUTE2 => exception_rec.attribute2,
X_ATTRIBUTE3 => exception_rec.attribute3,
X_ATTRIBUTE4 => exception_rec.attribute4,
X_ATTRIBUTE5 => exception_rec.attribute5,
X_ATTRIBUTE6 => exception_rec.attribute6,
X_ATTRIBUTE7 => exception_rec.attribute7,
X_ATTRIBUTE8 => exception_rec.attribute8,
X_ATTRIBUTE9 => exception_rec.attribute9,
X_ATTRIBUTE10 => exception_rec.attribute10,
X_ATTRIBUTE11 => exception_rec.attribute11,
X_ATTRIBUTE12 => exception_rec.attribute12,
X_ATTRIBUTE13 => exception_rec.attribute13,
X_ATTRIBUTE14 => exception_rec.attribute14,
X_ATTRIBUTE15 => exception_rec.attribute15,
X_OBJECT_VERSION_NUMBER => exception_rec.object_version_number + 1,
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => fnd_global.user_id,
X_LAST_UPDATE_LOGIN => fnd_global.login_id);
Delete_Sch_Materials(exception_rec.unit_effectivity_id);
AHL_UNIT_EFFECTIVITIES_PKG.Update_Row(
X_UNIT_EFFECTIVITY_ID => l_ue_rec.unit_effectivity_id,
X_CSI_ITEM_INSTANCE_ID => l_ue_rec.csi_item_instance_id,
X_MR_INTERVAL_ID => l_ue_rec.mr_interval_id,
X_MR_EFFECTIVITY_ID => l_ue_rec.mr_effectivity_id,
X_MR_HEADER_ID => l_ue_rec.mr_header_id,
X_STATUS_CODE => exception_rec.status_code,
X_DUE_DATE => l_ue_rec.due_date,
X_DUE_COUNTER_VALUE => l_ue_rec.due_counter_value,
X_FORECAST_SEQUENCE => l_ue_rec.forecast_sequence,
X_REPETITIVE_MR_FLAG => l_ue_rec.repetitive_mr_flag,
X_TOLERANCE_FLAG => l_ue_rec.tolerance_flag,
X_REMARKS => l_ue_rec.remarks,
X_MESSAGE_CODE => l_ue_rec.message_code,
X_PRECEDING_UE_ID => l_ue_rec.preceding_ue_id,
X_DATE_RUN => sysdate,
X_SET_DUE_DATE => l_ue_rec.set_due_date,
X_ACCOMPLISHED_DATE => l_ue_rec.accomplished_date,
X_SERVICE_LINE_ID => l_ue_rec.service_line_id,
X_PROGRAM_MR_HEADER_ID => l_ue_rec.program_mr_header_id,
X_CANCEL_REASON_CODE => l_ue_rec.cancel_reason_code,
X_EARLIEST_DUE_DATE => l_ue_rec.earliest_due_date,
X_LATEST_DUE_DATE => l_ue_rec.latest_due_date,
X_defer_from_ue_id => l_ue_rec.defer_from_ue_id,
X_cs_incident_id => l_ue_rec.cs_incident_id,
X_qa_collection_id => l_ue_rec.qa_collection_id,
X_orig_deferral_ue_id => l_ue_rec.orig_deferral_ue_id,
X_application_usg_code => l_ue_rec.application_usg_code,
X_object_type => l_ue_rec.object_type,
X_counter_id => l_ue_rec.counter_id,
X_MANUALLY_PLANNED_FLAG => l_ue_rec.MANUALLY_PLANNED_FLAG,
X_LOG_SERIES_CODE => l_ue_rec.log_series_code,
X_LOG_SERIES_NUMBER => l_ue_rec.log_series_number,
X_FLIGHT_NUMBER => l_ue_rec.flight_number,
X_MEL_CDL_TYPE_CODE => l_ue_rec.mel_cdl_type_code,
X_POSITION_PATH_ID => l_ue_rec.position_path_id,
X_ATA_CODE => l_ue_rec.ATA_CODE,
X_UNIT_CONFIG_HEADER_ID => l_ue_rec.unit_config_header_id,
X_ATTRIBUTE_CATEGORY => l_ue_rec.attribute_category,
X_ATTRIBUTE1 => l_ue_rec.attribute1,
X_ATTRIBUTE2 => l_ue_rec.attribute2,
X_ATTRIBUTE3 => l_ue_rec.attribute3,
X_ATTRIBUTE4 => l_ue_rec.attribute4,
X_ATTRIBUTE5 => l_ue_rec.attribute5,
X_ATTRIBUTE6 => l_ue_rec.attribute6,
X_ATTRIBUTE7 => l_ue_rec.attribute7,
X_ATTRIBUTE8 => l_ue_rec.attribute8,
X_ATTRIBUTE9 => l_ue_rec.attribute9,
X_ATTRIBUTE10 => l_ue_rec.attribute10,
X_ATTRIBUTE11 => l_ue_rec.attribute11,
X_ATTRIBUTE12 => l_ue_rec.attribute12,
X_ATTRIBUTE13 => l_ue_rec.attribute13,
X_ATTRIBUTE14 => l_ue_rec.attribute14,
X_ATTRIBUTE15 => l_ue_rec.attribute15,
X_OBJECT_VERSION_NUMBER => l_ue_rec.object_version_number + 1,
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => fnd_global.user_id,
X_LAST_UPDATE_LOGIN => fnd_global.login_id);
Delete_Sch_Materials(l_ue_rec.unit_effectivity_id);
ELSIF (l_delete_flag) THEN
IF G_DEBUG = 'Y' THEN
AHL_DEBUG_PUB.Debug('Deleting exception code..');
AHL_UE_RELATIONSHIPS_PKG.Delete_Row (ue_reln_rec.ue_relationship_id);
AHL_UNIT_EFFECTIVITIES_PKG.Delete_Row(ue_reln_rec.related_ue_id);
Delete_Sch_Materials(ue_reln_rec.related_ue_id);
Delete_Sch_Materials(exception_rec.unit_effectivity_id);
DELETE from ahl_unit_thresholds
WHERE unit_deferral_id = l_unit_deferral_id;
AHL_UNIT_DEFERRALS_PKG.Delete_Row(l_unit_deferral_id);
AHL_UNIT_EFFECTIVITIES_PKG.Delete_Row(exception_rec.unit_effectivity_id);
AHL_UNIT_EFFECTIVITIES_PKG.Insert_Row (
X_ROWID => l_rowid,
X_UNIT_EFFECTIVITY_ID => l_unit_effectivity_id,
X_CSI_ITEM_INSTANCE_ID => p_x_temp_mr_rec.csi_item_instance_id,
X_MR_INTERVAL_ID => p_x_temp_mr_rec.mr_interval_id,
X_MR_EFFECTIVITY_ID => p_x_temp_mr_rec.mr_effectivity_id,
X_MR_HEADER_ID => p_x_temp_mr_rec.MR_header_id,
X_STATUS_CODE => null, /* status_code */
X_DUE_DATE => p_x_temp_mr_rec.due_date,
X_DUE_COUNTER_VALUE => p_x_temp_mr_rec.due_counter_value,
X_FORECAST_SEQUENCE => p_x_temp_mr_rec.forecast_sequence,
X_REPETITIVE_MR_FLAG => p_x_temp_mr_rec.repetitive_mr_flag,
X_TOLERANCE_FLAG => p_x_temp_mr_rec.tolerance_flag,
X_REMARKS => null, /* remarks */
X_MESSAGE_CODE => p_x_temp_mr_rec.message_code,
X_PRECEDING_UE_ID => null, /* p_x_temp_mr_rec.preceding_ue_id */
X_DATE_RUN => sysdate, /* date_run */
X_SET_DUE_DATE => null, /* set due date */
X_ACCOMPLISHED_DATE => null, /* accomplished date */
X_SERVICE_LINE_ID => p_x_temp_mr_rec.service_line_id,
X_PROGRAM_MR_HEADER_ID => p_x_temp_mr_rec.program_mr_header_id,
X_CANCEL_REASON_CODE => null, /* cancel_reason_code */
X_EARLIEST_DUE_DATE => p_x_temp_mr_rec.earliest_due_date,
X_LATEST_DUE_DATE => p_x_temp_mr_rec.latest_due_date,
X_defer_from_ue_id => null,
X_cs_incident_id => null,
X_qa_collection_id => null,
X_orig_deferral_ue_id => null,
X_application_usg_code => G_APPLN_USAGE_CODE,
X_object_type => 'MR',
X_counter_id => p_x_temp_mr_rec.counter_id,
X_MANUALLY_PLANNED_FLAG => 'N',
X_LOG_SERIES_CODE => NULL,
X_LOG_SERIES_NUMBER => NULL,
X_FLIGHT_NUMBER => NULL,
X_MEL_CDL_TYPE_CODE => NULL,
X_POSITION_PATH_ID => NULL,
X_ATA_CODE => NULL,
X_UNIT_CONFIG_HEADER_ID => NULL,
X_ATTRIBUTE_CATEGORY => null, /* ATTRIBUTE_CATEGORY */
X_ATTRIBUTE1 => null, /* ATTRIBUTE1 */
X_ATTRIBUTE2 => null, /* ATTRIBUTE2 */
X_ATTRIBUTE3 => null, /* ATTRIBUTE3 */
X_ATTRIBUTE4 => null, /* ATTRIBUTE4 */
X_ATTRIBUTE5 => null, /* ATTRIBUTE5 */
X_ATTRIBUTE6 => null, /* ATTRIBUTE6 */
X_ATTRIBUTE7 => null, /* ATTRIBUTE7 */
X_ATTRIBUTE8 => null, /* ATTRIBUTE8 */
X_ATTRIBUTE9 => null, /* ATTRIBUTE9 */
X_ATTRIBUTE10 => null, /* ATTRIBUTE10 */
X_ATTRIBUTE11 => null, /* ATTRIBUTE11 */
X_ATTRIBUTE12 => null, /* ATTRIBUTE12 */
X_ATTRIBUTE13 => null, /* ATTRIBUTE13 */
X_ATTRIBUTE14 => null, /* ATTRIBUTE14 */
X_ATTRIBUTE15 => null, /* ATTRIBUTE15 */
X_OBJECT_VERSION_NUMBER => 1, /* object version */
X_CREATION_DATE => sysdate,
X_CREATED_BY => fnd_global.user_id,
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => fnd_global.user_id,
X_LAST_UPDATE_LOGIN => fnd_global.login_id );
PROCEDURE update_record (p_temp_mr_rec IN ahl_temp_unit_effectivities%ROWTYPE,
p_mr_rec IN ahl_unit_effectivities_app_v%ROWTYPE)
IS
BEGIN
IF G_DEBUG = 'Y' THEN
AHL_DEBUG_PUB.Debug('Start Update Record-' || p_mr_rec.unit_effectivity_id);
AHL_UNIT_EFFECTIVITIES_PKG.Update_Row (
X_UNIT_EFFECTIVITY_ID => p_mr_rec.unit_effectivity_id,
X_CSI_ITEM_INSTANCE_ID => p_mr_rec.csi_item_instance_id,
X_MR_INTERVAL_ID => p_temp_mr_rec.mr_interval_id,
X_MR_EFFECTIVITY_ID => p_temp_mr_rec.mr_effectivity_id,
X_MR_HEADER_ID => p_mr_rec.MR_header_id,
X_STATUS_CODE => p_mr_rec.status_code,
X_DUE_DATE => p_temp_mr_rec.due_date,
X_DUE_COUNTER_VALUE => p_temp_mr_rec.due_counter_value,
X_FORECAST_SEQUENCE => p_temp_mr_rec.forecast_sequence,
X_REPETITIVE_MR_FLAG => p_temp_mr_rec.repetitive_mr_flag,
X_TOLERANCE_FLAG => p_temp_mr_rec.tolerance_flag,
X_REMARKS => p_mr_rec.remarks,
X_MESSAGE_CODE => p_temp_mr_rec.message_code,
X_PRECEDING_UE_ID => null, /* preceding_ue_id */
X_DATE_RUN => sysdate, /* date run */
X_SET_DUE_DATE => p_mr_rec.set_due_date,
X_ACCOMPLISHED_DATE => p_mr_rec.accomplished_date,
X_SERVICE_LINE_ID => p_temp_mr_rec.service_line_id,
X_PROGRAM_MR_HEADER_ID => p_temp_mr_rec.program_mr_header_id,
X_CANCEL_REASON_CODE => p_mr_rec.cancel_reason_code,
X_EARLIEST_DUE_DATE => p_temp_mr_rec.earliest_due_date,
X_LATEST_DUE_DATE => p_temp_mr_rec.latest_due_date,
X_defer_from_ue_id => p_mr_rec.defer_from_ue_id,
X_cs_incident_id => p_mr_rec.cs_incident_id,
X_qa_collection_id => p_mr_rec.qa_collection_id,
X_orig_deferral_ue_id => p_mr_rec.orig_deferral_ue_id,
X_application_usg_code => p_mr_rec.application_usg_code,
X_object_type => p_mr_rec.object_type,
X_counter_id => p_temp_mr_rec.counter_id,
X_MANUALLY_PLANNED_FLAG => p_mr_rec.MANUALLY_PLANNED_FLAG,
X_LOG_SERIES_CODE => p_mr_rec.log_series_code,
X_LOG_SERIES_NUMBER => p_mr_rec.log_series_number,
X_FLIGHT_NUMBER => p_mr_rec.flight_number,
X_MEL_CDL_TYPE_CODE => p_mr_rec.mel_cdl_type_code,
X_POSITION_PATH_ID => p_mr_rec.position_path_id,
X_ATA_CODE => p_mr_rec.ATA_CODE,
X_UNIT_CONFIG_HEADER_ID => p_mr_rec.unit_config_header_id,
X_ATTRIBUTE_CATEGORY => p_mr_rec.ATTRIBUTE_CATEGORY,
X_ATTRIBUTE1 => p_mr_rec.ATTRIBUTE1,
X_ATTRIBUTE2 => p_mr_rec.ATTRIBUTE2,
X_ATTRIBUTE3 => p_mr_rec.ATTRIBUTE3,
X_ATTRIBUTE4 => p_mr_rec.ATTRIBUTE4,
X_ATTRIBUTE5 => p_mr_rec.ATTRIBUTE5,
X_ATTRIBUTE6 => p_mr_rec.ATTRIBUTE6,
X_ATTRIBUTE7 => p_mr_rec.ATTRIBUTE7,
X_ATTRIBUTE8 => p_mr_rec.ATTRIBUTE8,
X_ATTRIBUTE9 => p_mr_rec.ATTRIBUTE9,
X_ATTRIBUTE10 => p_mr_rec.ATTRIBUTE10,
X_ATTRIBUTE11 => p_mr_rec.ATTRIBUTE11,
X_ATTRIBUTE12 => p_mr_rec.ATTRIBUTE12,
X_ATTRIBUTE13 => p_mr_rec.ATTRIBUTE13,
X_ATTRIBUTE14 => p_mr_rec.ATTRIBUTE14,
X_ATTRIBUTE15 => p_mr_rec.ATTRIBUTE15,
X_OBJECT_VERSION_NUMBER => p_mr_rec.object_version_number+1,
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => fnd_global.user_id,
X_LAST_UPDATE_LOGIN => fnd_global.login_id );
Delete_Sch_Materials(p_mr_rec.unit_effectivity_id);
AHL_DEBUG_PUB.Debug('End Update Record');
END update_record;
SELECT unit_effectivity_id,
csi_item_instance_id,
MR_header_id,
due_date,
mr_interval_id,
mr_effectivity_id,
due_counter_value,
parent_csi_item_instance_id,
parent_mr_header_id,
orig_csi_item_instance_id,
orig_mr_header_id,
forecast_sequence,
repetitive_mr_flag,
tolerance_flag,
message_code,
earliest_due_date,
latest_due_date,
counter_id
FROM ahl_temp_unit_effectivities
START WITH parent_csi_item_instance_id = p_csi_item_instance_id
AND parent_mr_header_id = p_mr_header_id
AND orig_csi_item_instance_id = p_csi_item_instance_id
AND orig_mr_header_id = p_mr_header_id
AND orig_forecast_sequence = p_forecast_sequence
AND nvl(preceding_check_flag,'N') = 'N'
CONNECT BY PRIOR MR_header_id = parent_mr_header_id
AND PRIOR csi_item_instance_id = parent_csi_item_instance_id
AND orig_csi_item_instance_id = p_csi_item_instance_id
AND orig_mr_header_id = p_mr_header_id
AND orig_forecast_sequence = p_forecast_sequence
AND nvl(preceding_check_flag,'N') = 'N'
FOR UPDATE OF due_date;
SELECT unit_effectivity_id
FROM ahl_temp_unit_effectivities
WHERE csi_item_instance_id = p_parent_csi_item_instance_id
AND MR_header_id = p_parent_mr_header_id
AND orig_csi_item_instance_id = p_orig_csi_item_instance_id
AND orig_mr_header_id = p_orig_mr_header_id
AND orig_forecast_sequence = p_forecast_sequence;
UPDATE ahl_temp_unit_effectivities
SET unit_effectivity_id = p_x_temp_grp_rec.unit_effectivity_id
WHERE csi_item_instance_id = p_x_temp_grp_rec.csi_item_instance_id AND
mr_header_id = p_x_temp_grp_rec.mr_header_id AND
forecast_sequence = p_x_temp_grp_rec.forecast_sequence;
UPDATE ahl_temp_unit_effectivities
SET unit_effectivity_id = l_temp_child_rec.unit_effectivity_id
WHERE CURRENT OF ahl_temp_grp_csr;
AHL_UE_RELATIONSHIPS_PKG.Insert_Row(
X_UE_RELATIONSHIP_ID => l_ue_relationship_id,
X_UE_ID => l_parent_ue_id,
X_RELATED_UE_ID => l_temp_grp_rec.unit_effectivity_id,
X_RELATIONSHIP_CODE => 'PARENT',
X_ORIGINATOR_UE_ID => l_originator_ue_id,
X_ATTRIBUTE_CATEGORY => null, /* ATTRIBUTE_CATEGORY */
X_ATTRIBUTE1 => null, /* ATTRIBUTE1 */
X_ATTRIBUTE2 => null, /* ATTRIBUTE2 */
X_ATTRIBUTE3 => null, /* ATTRIBUTE3 */
X_ATTRIBUTE4 => null, /* ATTRIBUTE4 */
X_ATTRIBUTE5 => null, /* ATTRIBUTE5 */
X_ATTRIBUTE6 => null, /* ATTRIBUTE6 */
X_ATTRIBUTE7 => null, /* ATTRIBUTE7 */
X_ATTRIBUTE8 => null, /* ATTRIBUTE8 */
X_ATTRIBUTE9 => null, /* ATTRIBUTE9 */
X_ATTRIBUTE10 => null, /* ATTRIBUTE10 */
X_ATTRIBUTE11 => null, /* ATTRIBUTE11 */
X_ATTRIBUTE12 => null, /* ATTRIBUTE12 */
X_ATTRIBUTE13 => null, /* ATTRIBUTE13 */
X_ATTRIBUTE14 => null, /* ATTRIBUTE14 */
X_ATTRIBUTE15 => null, /* ATTRIBUTE15 */
X_OBJECT_VERSION_NUMBER => 1,
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => fnd_global.user_id,
X_CREATION_DATE => sysdate,
X_CREATED_BY => fnd_global.user_id,
X_LAST_UPDATE_LOGIN => fnd_global.login_id);
PROCEDURE update_group (p_temp_mr_rec IN ahl_temp_unit_effectivities%ROWTYPE,
p_mr_rec IN ahl_unit_effectivities_app_v%ROWTYPE)
IS
-- Read group elements.
CURSOR ahl_temp_grp_csr(p_csi_item_instance_id IN NUMBER,
p_mr_header_id IN NUMBER,
p_forecast_sequence IN NUMBER,
p_level IN NUMBER) IS
SELECT csi_item_instance_id,
MR_header_id,
parent_csi_item_instance_id,
parent_mr_header_id,
orig_csi_item_instance_id,
orig_mr_header_id,
forecast_sequence
FROM ahl_temp_unit_effectivities
WHERE level = p_level
START WITH parent_csi_item_instance_id = p_csi_item_instance_id
AND parent_mr_header_id = p_mr_header_id
AND orig_csi_item_instance_id = p_csi_item_instance_id
AND orig_mr_header_id = p_mr_header_id
AND orig_forecast_sequence = p_forecast_sequence
AND nvl(preceding_check_flag,'N') = 'N'
CONNECT BY PRIOR MR_header_id = parent_mr_header_id
AND PRIOR csi_item_instance_id = parent_csi_item_instance_id
AND orig_csi_item_instance_id = p_csi_item_instance_id
AND orig_mr_header_id = p_mr_header_id
AND orig_forecast_sequence = p_forecast_sequence
AND nvl(preceding_check_flag,'N') = 'N';
SELECT UE_ID parent_ue_id,
RELATED_UE_ID ue_id
FROM ahl_ue_relationships
WHERE level = p_level
START WITH ue_id = p_unit_effectivity_id AND
relationship_code = 'PARENT'
CONNECT BY PRIOR related_ue_id = ue_id AND
relationship_code = 'PARENT';
SELECT ue1.mr_header_id, ue1.csi_item_instance_id, ue1.unit_effectivity_id,
ue2.mr_header_id parent_mr_header_id,
ue2.csi_item_instance_id parent_csi_item_instance_id
--FROM ahl_unit_effectivities_app_v ue1, ahl_unit_effectivities_app_v ue2
FROM ahl_unit_effectivities_b ue1, ahl_unit_effectivities_b ue2
WHERE ue1.unit_effectivity_id = p_ue_id AND
ue2.unit_effectivity_id = p_parent_ue_id;
SELECT
UNIT_EFFECTIVITY_ID ,
CSI_ITEM_INSTANCE_ID,
MR_INTERVAL_ID,
MR_EFFECTIVITY_ID ,
MR_HEADER_ID,
STATUS_CODE ,
DUE_DATE ,
DUE_COUNTER_VALUE ,
FORECAST_SEQUENCE ,
--REPETITIVE_MR_FLAG ,
TOLERANCE_FLAG ,
REMARKS ,
MESSAGE_CODE ,
PRECEDING_UE_ID ,
DATE_RUN ,
SET_DUE_DATE ,
ACCOMPLISHED_DATE ,
CANCEL_REASON_CODE,
--EARLIEST_DUE_DATE,
--LATEST_DUE_DATE,
defer_from_ue_id,
cs_incident_id,
qa_collection_id,
orig_deferral_ue_id,
application_usg_code,
object_type,
--counter_id,
ATTRIBUTE_CATEGORY ,
ATTRIBUTE1,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
ATTRIBUTE9 ,
ATTRIBUTE10,
ATTRIBUTE11 ,
ATTRIBUTE12 ,
ATTRIBUTE13 ,
ATTRIBUTE14 ,
ATTRIBUTE15 ,
OBJECT_VERSION_NUMBER
--FROM ahl_unit_effectivities_app_v
FROM ahl_unit_effectivities_vl
WHERE unit_effectivity_id = p_unit_effectivity_id;
SELECT unit_deferral_id
FROM ahl_unit_deferrals_b
WHERE UNIT_EFFECTIVITY_ID = p_ue_id
AND UNIT_DEFERRAL_TYPE = 'INIT-DUE';
AHL_DEBUG_PUB.Debug('Start Update Group');
SAVEPOINT update_group;
UPDATE AHL_UNIT_EFFECTIVITIES_B
SET mr_interval_id = p_temp_mr_rec.mr_interval_id,
mr_effectivity_id = p_temp_mr_rec.mr_effectivity_id,
due_date = p_temp_mr_rec.due_date,
due_counter_value = p_temp_mr_rec.due_counter_value,
forecast_sequence = p_temp_mr_rec.forecast_sequence,
repetitive_mr_flag = p_temp_mr_rec.repetitive_mr_flag,
tolerance_flag = p_temp_mr_rec.tolerance_flag,
message_code = p_temp_mr_rec.message_code,
date_run = sysdate,
earliest_due_date = p_temp_mr_rec.earliest_due_date,
latest_due_date = p_temp_mr_rec.latest_due_date,
counter_id = p_temp_mr_rec.counter_id,
object_version_number = object_version_number+1,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = fnd_global.user_id,
LAST_UPDATE_LOGIN = fnd_global.login_id
WHERE unit_effectivity_id IN (SELECT related_ue_id
FROM ahl_ue_relationships
WHERE originator_ue_id = p_temp_mr_rec.unit_effectivity_id
AND relationship_code = 'PARENT');
l_temp_grp_tbl.DELETE;
l_ue_grp_tbl.DELETE;
l_grp_match_flag := FALSE; -- this will be set to true when record gets updated.
update_record (p_temp_mr_rec, l_unit_effectivity_rec);
ROLLBACK to update_group;
UPDATE ahl_unit_deferrals_b
SET UNIT_EFFECTIVITY_ID = l_temp_mr_rec.unit_effectivity_id,
last_update_date = sysdate,
object_version_number = object_version_number + 1,
LAST_UPDATED_BY = fnd_global.user_id,
LAST_UPDATE_LOGIN = fnd_global.login_id
WHERE unit_deferral_id = l_unit_deferral_id;
UPDATE ahl_unit_effectivities_b
SET status_code = 'INIT-DUE'
WHERE unit_effectivity_id = l_temp_mr_rec.unit_effectivity_id;
update_record (p_temp_mr_rec,
p_mr_rec);
AHL_DEBUG_PUB.Debug('End Update Group');
END update_group;
SELECT
unit_effectivity_id,
object_type,
csi_item_instance_id,
mr_header_id,
due_date,
due_counter_value,
tolerance_flag,
message_code,
parent_csi_item_instance_id,
parent_mr_header_id,
orig_csi_item_instance_id,
orig_mr_header_id,
orig_unit_effectivity_id,
group_match_flag,
counter_id
FROM ahl_temp_unit_SR_deferrals
WHERE orig_unit_effectivity_id IS NULL
FOR UPDATE OF unit_effectivity_id;
SELECT related_ue_id
FROM ahl_ue_relationships
WHERE originator_ue_id = p_orig_ue_id
AND relationship_code = 'PARENT';
SELECT unit_effectivity_id,
csi_item_instance_id,
MR_header_id,
due_date,
due_counter_value,
parent_csi_item_instance_id,
parent_mr_header_id,
orig_csi_item_instance_id,
orig_mr_header_id,
tolerance_flag,
message_code
FROM ahl_temp_unit_SR_deferrals
START WITH parent_csi_item_instance_id = p_csi_item_instance_id
AND parent_mr_header_id = p_mr_header_id
AND orig_csi_item_instance_id = p_csi_item_instance_id
AND orig_mr_header_id = p_mr_header_id
AND orig_unit_effectivity_id = p_unit_effectivity_id
CONNECT BY PRIOR MR_header_id = parent_mr_header_id
AND PRIOR csi_item_instance_id = parent_csi_item_instance_id
AND orig_csi_item_instance_id = p_csi_item_instance_id
AND orig_mr_header_id = p_mr_header_id
AND orig_unit_effectivity_id = p_unit_effectivity_id
FOR UPDATE OF due_date
ORDER BY level;
SELECT unit_effectivity_id
FROM ahl_temp_unit_SR_deferrals
WHERE csi_item_instance_id = p_parent_csi_item_instance_id
AND MR_header_id = p_parent_mr_header_id
AND orig_csi_item_instance_id = p_orig_csi_item_instance_id
AND orig_mr_header_id = p_orig_mr_header_id
AND orig_unit_effectivity_id = p_unit_effectivity_id;
SELECT
UNIT_EFFECTIVITY_ID ,
CSI_ITEM_INSTANCE_ID,
MR_INTERVAL_ID,
MR_EFFECTIVITY_ID ,
MR_HEADER_ID,
STATUS_CODE ,
DUE_DATE ,
DUE_COUNTER_VALUE ,
FORECAST_SEQUENCE ,
REPETITIVE_MR_FLAG ,
TOLERANCE_FLAG ,
REMARKS ,
MESSAGE_CODE ,
PRECEDING_UE_ID ,
DATE_RUN ,
SET_DUE_DATE ,
ACCOMPLISHED_DATE ,
CANCEL_REASON_CODE,
EARLIEST_DUE_DATE,
LATEST_DUE_DATE,
SERVICE_LINE_ID,
PROGRAM_MR_HEADER_ID,
defer_from_ue_id,
cs_incident_id,
qa_collection_id,
orig_deferral_ue_id,
application_usg_code,
object_type,
counter_id,
manually_planned_flag,
LOG_SERIES_CODE,
LOG_SERIES_NUMBER,
FLIGHT_NUMBER,
MEL_CDL_TYPE_CODE,
POSITION_PATH_ID,
ATA_CODE,
UNIT_CONFIG_HEADER_ID,
ATTRIBUTE_CATEGORY ,
ATTRIBUTE1,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
ATTRIBUTE9 ,
ATTRIBUTE10,
ATTRIBUTE11 ,
ATTRIBUTE12 ,
ATTRIBUTE13 ,
ATTRIBUTE14 ,
ATTRIBUTE15 ,
OBJECT_VERSION_NUMBER
FROM ahl_unit_effectivities_vl
WHERE unit_effectivity_id = p_unit_effectivity_id;
SELECT unit_effectivity_id
FROM ahl_unit_effectivities_b UE, ahl_ue_relationships UER
WHERE UE.unit_effectivity_id = UER.related_ue_id
AND UER.ue_id = p_sr_ue_id
AND UE.status_code = 'MR-TERMINATE';
l_delete_ue_id_tbl nbr_tbl_type;
AHL_DEBUG_PUB.Debug('Update Record-' || l_unit_effectivity_rec.unit_effectivity_id);
AHL_UNIT_EFFECTIVITIES_PKG.Update_Row (
X_UNIT_EFFECTIVITY_ID => l_unit_effectivity_rec.unit_effectivity_id,
X_CSI_ITEM_INSTANCE_ID => l_unit_effectivity_rec.csi_item_instance_id,
X_MR_INTERVAL_ID => l_unit_effectivity_rec.mr_interval_id,
X_MR_EFFECTIVITY_ID => l_unit_effectivity_rec.mr_effectivity_id,
X_MR_HEADER_ID => l_unit_effectivity_rec.MR_header_id,
X_STATUS_CODE => l_unit_effectivity_rec.status_code,
X_DUE_DATE => l_unit_effectivity_rec.due_date,
X_DUE_COUNTER_VALUE => l_unit_effectivity_rec.due_counter_value,
X_FORECAST_SEQUENCE => l_unit_effectivity_rec.forecast_sequence,
X_REPETITIVE_MR_FLAG => l_unit_effectivity_rec.repetitive_mr_flag,
X_TOLERANCE_FLAG => l_unit_effectivity_rec.tolerance_flag,
X_REMARKS => l_unit_effectivity_rec.remarks,
X_MESSAGE_CODE => l_unit_effectivity_rec.message_code,
X_PRECEDING_UE_ID => l_unit_effectivity_rec.preceding_ue_id,
X_DATE_RUN => sysdate, /* date run */
X_SET_DUE_DATE => l_unit_effectivity_rec.set_due_date,
X_ACCOMPLISHED_DATE => l_unit_effectivity_rec.accomplished_date,
X_SERVICE_LINE_ID => l_unit_effectivity_rec.service_line_id,
X_PROGRAM_MR_HEADER_ID => l_unit_effectivity_rec.program_mr_header_id,
X_CANCEL_REASON_CODE => l_unit_effectivity_rec.cancel_reason_code,
X_EARLIEST_DUE_DATE => l_unit_effectivity_rec.earliest_due_date,
X_LATEST_DUE_DATE => l_unit_effectivity_rec.latest_due_date,
X_defer_from_ue_id => l_unit_effectivity_rec.defer_from_ue_id,
X_cs_incident_id => l_unit_effectivity_rec.cs_incident_id,
X_qa_collection_id => l_unit_effectivity_rec.qa_collection_id,
X_orig_deferral_ue_id => l_unit_effectivity_rec.orig_deferral_ue_id,
X_application_usg_code => l_unit_effectivity_rec.application_usg_code,
X_object_type => l_unit_effectivity_rec.object_type,
X_counter_id => l_unit_effectivity_rec.counter_id,
X_MANUALLY_PLANNED_FLAG => l_unit_effectivity_rec.MANUALLY_PLANNED_FLAG,
X_LOG_SERIES_CODE => l_unit_effectivity_rec.log_series_code,
X_LOG_SERIES_NUMBER => l_unit_effectivity_rec.log_series_number,
X_FLIGHT_NUMBER => l_unit_effectivity_rec.flight_number,
X_MEL_CDL_TYPE_CODE => l_unit_effectivity_rec.mel_cdl_type_code,
X_POSITION_PATH_ID => l_unit_effectivity_rec.position_path_id,
X_ATA_CODE => l_unit_effectivity_rec.ATA_CODE,
X_UNIT_CONFIG_HEADER_ID => l_unit_effectivity_rec.unit_config_header_id,
X_ATTRIBUTE_CATEGORY => l_unit_effectivity_rec.ATTRIBUTE_CATEGORY,
X_ATTRIBUTE1 => l_unit_effectivity_rec.ATTRIBUTE1,
X_ATTRIBUTE2 => l_unit_effectivity_rec.ATTRIBUTE2,
X_ATTRIBUTE3 => l_unit_effectivity_rec.ATTRIBUTE3,
X_ATTRIBUTE4 => l_unit_effectivity_rec.ATTRIBUTE4,
X_ATTRIBUTE5 => l_unit_effectivity_rec.ATTRIBUTE5,
X_ATTRIBUTE6 => l_unit_effectivity_rec.ATTRIBUTE6,
X_ATTRIBUTE7 => l_unit_effectivity_rec.ATTRIBUTE7,
X_ATTRIBUTE8 => l_unit_effectivity_rec.ATTRIBUTE8,
X_ATTRIBUTE9 => l_unit_effectivity_rec.ATTRIBUTE9,
X_ATTRIBUTE10 => l_unit_effectivity_rec.ATTRIBUTE10,
X_ATTRIBUTE11 => l_unit_effectivity_rec.ATTRIBUTE11,
X_ATTRIBUTE12 => l_unit_effectivity_rec.ATTRIBUTE12,
X_ATTRIBUTE13 => l_unit_effectivity_rec.ATTRIBUTE13,
X_ATTRIBUTE14 => l_unit_effectivity_rec.ATTRIBUTE14,
X_ATTRIBUTE15 => l_unit_effectivity_rec.ATTRIBUTE15,
X_OBJECT_VERSION_NUMBER => l_unit_effectivity_rec.object_version_number+1,
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => fnd_global.user_id,
X_LAST_UPDATE_LOGIN => fnd_global.login_id );
Delete_Sch_Materials(l_unit_effectivity_rec.unit_effectivity_id);
AHL_DEBUG_PUB.Debug('Update Record-' || l_unit_effectivity_rec.unit_effectivity_id);
AHL_UNIT_EFFECTIVITIES_PKG.Update_Row (
X_UNIT_EFFECTIVITY_ID => l_unit_effectivity_rec.unit_effectivity_id,
X_CSI_ITEM_INSTANCE_ID => l_unit_effectivity_rec.csi_item_instance_id,
X_MR_INTERVAL_ID => l_unit_effectivity_rec.mr_interval_id,
X_MR_EFFECTIVITY_ID => l_unit_effectivity_rec.mr_effectivity_id,
X_MR_HEADER_ID => l_unit_effectivity_rec.MR_header_id,
X_STATUS_CODE => l_unit_effectivity_rec.status_code,
X_DUE_DATE => l_unit_effectivity_rec.due_date,
X_DUE_COUNTER_VALUE => l_unit_effectivity_rec.due_counter_value,
X_FORECAST_SEQUENCE => l_unit_effectivity_rec.forecast_sequence,
X_REPETITIVE_MR_FLAG => l_unit_effectivity_rec.repetitive_mr_flag,
X_TOLERANCE_FLAG => l_unit_effectivity_rec.tolerance_flag,
X_REMARKS => l_unit_effectivity_rec.remarks,
X_MESSAGE_CODE => l_unit_effectivity_rec.message_code,
X_PRECEDING_UE_ID => l_unit_effectivity_rec.preceding_ue_id,
X_DATE_RUN => sysdate, /* date run */
X_SET_DUE_DATE => l_unit_effectivity_rec.set_due_date,
X_ACCOMPLISHED_DATE => l_unit_effectivity_rec.accomplished_date,
X_SERVICE_LINE_ID => l_unit_effectivity_rec.service_line_id,
X_PROGRAM_MR_HEADER_ID => l_unit_effectivity_rec.program_mr_header_id,
X_CANCEL_REASON_CODE => l_unit_effectivity_rec.cancel_reason_code,
X_EARLIEST_DUE_DATE => l_unit_effectivity_rec.earliest_due_date,
X_LATEST_DUE_DATE => l_unit_effectivity_rec.latest_due_date,
X_defer_from_ue_id => l_unit_effectivity_rec.defer_from_ue_id,
X_cs_incident_id => l_unit_effectivity_rec.cs_incident_id,
X_qa_collection_id => l_unit_effectivity_rec.qa_collection_id,
X_orig_deferral_ue_id => l_unit_effectivity_rec.orig_deferral_ue_id,
X_application_usg_code => l_unit_effectivity_rec.application_usg_code,
X_object_type => l_unit_effectivity_rec.object_type,
X_counter_id => l_unit_effectivity_rec.counter_id,
X_MANUALLY_PLANNED_FLAG => l_unit_effectivity_rec.MANUALLY_PLANNED_FLAG,
X_LOG_SERIES_CODE => l_unit_effectivity_rec.log_series_code,
X_LOG_SERIES_NUMBER => l_unit_effectivity_rec.log_series_number,
X_FLIGHT_NUMBER => l_unit_effectivity_rec.flight_number,
X_MEL_CDL_TYPE_CODE => l_unit_effectivity_rec.mel_cdl_type_code,
X_POSITION_PATH_ID => l_unit_effectivity_rec.position_path_id,
X_ATA_CODE => l_unit_effectivity_rec.ATA_CODE,
X_UNIT_CONFIG_HEADER_ID => l_unit_effectivity_rec.unit_config_header_id,
X_ATTRIBUTE_CATEGORY => l_unit_effectivity_rec.ATTRIBUTE_CATEGORY,
X_ATTRIBUTE1 => l_unit_effectivity_rec.ATTRIBUTE1,
X_ATTRIBUTE2 => l_unit_effectivity_rec.ATTRIBUTE2,
X_ATTRIBUTE3 => l_unit_effectivity_rec.ATTRIBUTE3,
X_ATTRIBUTE4 => l_unit_effectivity_rec.ATTRIBUTE4,
X_ATTRIBUTE5 => l_unit_effectivity_rec.ATTRIBUTE5,
X_ATTRIBUTE6 => l_unit_effectivity_rec.ATTRIBUTE6,
X_ATTRIBUTE7 => l_unit_effectivity_rec.ATTRIBUTE7,
X_ATTRIBUTE8 => l_unit_effectivity_rec.ATTRIBUTE8,
X_ATTRIBUTE9 => l_unit_effectivity_rec.ATTRIBUTE9,
X_ATTRIBUTE10 => l_unit_effectivity_rec.ATTRIBUTE10,
X_ATTRIBUTE11 => l_unit_effectivity_rec.ATTRIBUTE11,
X_ATTRIBUTE12 => l_unit_effectivity_rec.ATTRIBUTE12,
X_ATTRIBUTE13 => l_unit_effectivity_rec.ATTRIBUTE13,
X_ATTRIBUTE14 => l_unit_effectivity_rec.ATTRIBUTE14,
X_ATTRIBUTE15 => l_unit_effectivity_rec.ATTRIBUTE15,
X_OBJECT_VERSION_NUMBER => l_unit_effectivity_rec.object_version_number+1,
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => fnd_global.user_id,
X_LAST_UPDATE_LOGIN => fnd_global.login_id );
Delete_Sch_Materials(l_unit_effectivity_rec.unit_effectivity_id);
l_delete_ue_id_tbl(l_index) := unit_sr_def_rec.unit_effectivity_id;
AHL_DEBUG_PUB.Debug('Insert Record-');
AHL_UNIT_EFFECTIVITIES_PKG.Insert_Row (
X_ROWID => l_rowid,
X_UNIT_EFFECTIVITY_ID => l_unit_effectivity_id,
X_CSI_ITEM_INSTANCE_ID => l_unit_effectivity_rec.csi_item_instance_id,
X_MR_INTERVAL_ID => null,
X_MR_EFFECTIVITY_ID => null,
X_MR_HEADER_ID => l_unit_effectivity_rec.MR_header_id,
X_STATUS_CODE => null, /* status_code */
X_DUE_DATE => l_unit_effectivity_rec.due_date,
X_DUE_COUNTER_VALUE => l_unit_effectivity_rec.due_counter_value,
X_FORECAST_SEQUENCE => null,
X_REPETITIVE_MR_FLAG => 'N',
X_TOLERANCE_FLAG => l_unit_effectivity_rec.tolerance_flag,
X_REMARKS => l_unit_effectivity_rec.remarks,
X_MESSAGE_CODE => l_unit_effectivity_rec.message_code,
X_PRECEDING_UE_ID => null, /* preceding_ue_id */
X_DATE_RUN => sysdate, /* date_run */
X_SET_DUE_DATE => null, /* set due date */
X_ACCOMPLISHED_DATE => null, /* accomplished date */
X_SERVICE_LINE_ID => l_unit_effectivity_rec.service_line_id,
X_PROGRAM_MR_HEADER_ID => l_unit_effectivity_rec.program_mr_header_id,
X_CANCEL_REASON_CODE => null, /* cancel_reason_code */
X_EARLIEST_DUE_DATE => l_unit_effectivity_rec.earliest_due_date,
X_LATEST_DUE_DATE => l_unit_effectivity_rec.latest_due_date,
X_defer_from_ue_id => l_unit_effectivity_rec.defer_from_ue_id,
X_cs_incident_id => l_unit_effectivity_rec.cs_incident_id,
X_qa_collection_id => l_unit_effectivity_rec.qa_collection_id,
X_orig_deferral_ue_id => l_unit_effectivity_rec.orig_deferral_ue_id,
X_application_usg_code => l_unit_effectivity_rec.application_usg_code,
X_object_type => l_unit_effectivity_rec.object_type,
X_counter_id => l_unit_effectivity_rec.counter_id,
X_MANUALLY_PLANNED_FLAG => l_unit_effectivity_rec.manually_planned_flag,
X_LOG_SERIES_CODE => l_unit_effectivity_rec.log_series_code,
X_LOG_SERIES_NUMBER => l_unit_effectivity_rec.log_series_number,
X_FLIGHT_NUMBER => l_unit_effectivity_rec.flight_number,
X_MEL_CDL_TYPE_CODE => l_unit_effectivity_rec.mel_cdl_type_code,
X_POSITION_PATH_ID => l_unit_effectivity_rec.position_path_id,
X_ATA_CODE => l_unit_effectivity_rec.ATA_CODE,
X_UNIT_CONFIG_HEADER_ID => l_unit_effectivity_rec.unit_config_header_id,
X_ATTRIBUTE_CATEGORY => null, /* ATTRIBUTE_CATEGORY */
X_ATTRIBUTE1 => null, /* ATTRIBUTE1 */
X_ATTRIBUTE2 => null, /* ATTRIBUTE2 */
X_ATTRIBUTE3 => null, /* ATTRIBUTE3 */
X_ATTRIBUTE4 => null, /* ATTRIBUTE4 */
X_ATTRIBUTE5 => null, /* ATTRIBUTE5 */
X_ATTRIBUTE6 => null, /* ATTRIBUTE6 */
X_ATTRIBUTE7 => null, /* ATTRIBUTE7 */
X_ATTRIBUTE8 => null, /* ATTRIBUTE8 */
X_ATTRIBUTE9 => null, /* ATTRIBUTE9 */
X_ATTRIBUTE10 => null, /* ATTRIBUTE10 */
X_ATTRIBUTE11 => null, /* ATTRIBUTE11 */
X_ATTRIBUTE12 => null, /* ATTRIBUTE12 */
X_ATTRIBUTE13 => null, /* ATTRIBUTE13 */
X_ATTRIBUTE14 => null, /* ATTRIBUTE14 */
X_ATTRIBUTE15 => null, /* ATTRIBUTE15 */
X_OBJECT_VERSION_NUMBER => 1, /* object version */
X_CREATION_DATE => sysdate,
X_CREATED_BY => fnd_global.user_id,
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => fnd_global.user_id,
X_LAST_UPDATE_LOGIN => fnd_global.login_id );
UPDATE ahl_temp_unit_SR_deferrals
SET unit_effectivity_id = l_new_top_ue_id,
object_type = l_unit_effectivity_rec.object_type
WHERE CURRENT OF ahl_unit_sr_def_csr ;
UPDATE AHL_UNIT_DEFERRALS_B
SET unit_effectivity_id = l_new_top_ue_id,
last_update_date = sysdate,
object_version_number = object_version_number + 1,
LAST_UPDATED_BY = fnd_global.user_id,
LAST_UPDATE_LOGIN = fnd_global.login_id
WHERE unit_effectivity_id = unit_sr_def_rec.unit_effectivity_id;
AHL_UNIT_EFFECTIVITIES_PKG.Insert_Row (
X_ROWID => l_rowid,
X_UNIT_EFFECTIVITY_ID => l_unit_effectivity_id,
X_CSI_ITEM_INSTANCE_ID => ahl_temp_rec.csi_item_instance_id,
X_MR_INTERVAL_ID => null,
X_MR_EFFECTIVITY_ID => null,
X_MR_HEADER_ID => ahl_temp_rec.mr_header_id,
X_STATUS_CODE => null, /* status_code */
X_DUE_DATE => l_unit_effectivity_rec.due_date,
X_DUE_COUNTER_VALUE => l_unit_effectivity_rec.due_counter_value,
X_FORECAST_SEQUENCE => null,
X_REPETITIVE_MR_FLAG => 'N',
X_TOLERANCE_FLAG => l_unit_effectivity_rec.tolerance_flag,
X_REMARKS => l_unit_effectivity_rec.remarks,
X_MESSAGE_CODE => l_unit_effectivity_rec.message_code,
X_PRECEDING_UE_ID => null, /* preceding_ue_id */
X_DATE_RUN => sysdate, /* date_run */
X_SET_DUE_DATE => null, /* set due date */
X_ACCOMPLISHED_DATE => null, /* accomplished date */
X_SERVICE_LINE_ID => l_unit_effectivity_rec.service_line_id,
X_PROGRAM_MR_HEADER_ID => l_unit_effectivity_rec.program_mr_header_id,
X_CANCEL_REASON_CODE => null, /* cancel_reason_code */
X_EARLIEST_DUE_DATE => l_unit_effectivity_rec.earliest_due_date,
X_LATEST_DUE_DATE => l_unit_effectivity_rec.latest_due_date,
X_defer_from_ue_id => l_unit_effectivity_rec.defer_from_ue_id,
X_cs_incident_id => l_unit_effectivity_rec.cs_incident_id,
X_qa_collection_id => l_unit_effectivity_rec.qa_collection_id,
X_orig_deferral_ue_id => l_unit_effectivity_rec.orig_deferral_ue_id,
X_application_usg_code => l_unit_effectivity_rec.application_usg_code,
X_object_type => 'MR',
X_counter_id => l_unit_effectivity_rec.counter_id,
X_manually_planned_flag => l_unit_effectivity_rec.manually_planned_flag,
X_LOG_SERIES_CODE => l_unit_effectivity_rec.log_series_code,
X_LOG_SERIES_NUMBER => l_unit_effectivity_rec.log_series_number,
X_FLIGHT_NUMBER => l_unit_effectivity_rec.flight_number,
X_MEL_CDL_TYPE_CODE => l_unit_effectivity_rec.mel_cdl_type_code,
X_POSITION_PATH_ID => l_unit_effectivity_rec.position_path_id,
X_ATA_CODE => l_unit_effectivity_rec.ATA_CODE,
X_UNIT_CONFIG_HEADER_ID => l_unit_effectivity_rec.unit_config_header_id,
X_ATTRIBUTE_CATEGORY => null, /* ATTRIBUTE_CATEGORY */
X_ATTRIBUTE1 => null, /* ATTRIBUTE1 */
X_ATTRIBUTE2 => null, /* ATTRIBUTE2 */
X_ATTRIBUTE3 => null, /* ATTRIBUTE3 */
X_ATTRIBUTE4 => null, /* ATTRIBUTE4 */
X_ATTRIBUTE5 => null, /* ATTRIBUTE5 */
X_ATTRIBUTE6 => null, /* ATTRIBUTE6 */
X_ATTRIBUTE7 => null, /* ATTRIBUTE7 */
X_ATTRIBUTE8 => null, /* ATTRIBUTE8 */
X_ATTRIBUTE9 => null, /* ATTRIBUTE9 */
X_ATTRIBUTE10 => null, /* ATTRIBUTE10 */
X_ATTRIBUTE11 => null, /* ATTRIBUTE11 */
X_ATTRIBUTE12 => null, /* ATTRIBUTE12 */
X_ATTRIBUTE13 => null, /* ATTRIBUTE13 */
X_ATTRIBUTE14 => null, /* ATTRIBUTE14 */
X_ATTRIBUTE15 => null, /* ATTRIBUTE15 */
X_OBJECT_VERSION_NUMBER => 1, /* object version */
X_CREATION_DATE => sysdate,
X_CREATED_BY => fnd_global.user_id,
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => fnd_global.user_id,
X_LAST_UPDATE_LOGIN => fnd_global.login_id );
UPDATE ahl_temp_unit_SR_deferrals
SET unit_effectivity_id = l_unit_effectivity_id,
object_type = l_unit_effectivity_rec.object_type
WHERE CURRENT OF ahl_temp_csr ;
AHL_UE_RELATIONSHIPS_PKG.Insert_Row(
X_UE_RELATIONSHIP_ID => l_ue_relationship_id,
X_UE_ID => l_parent_ue_id,
X_RELATED_UE_ID => ahl_temp_rec.unit_effectivity_id,
X_RELATIONSHIP_CODE => 'PARENT',
X_ORIGINATOR_UE_ID => l_new_top_ue_id,
X_ATTRIBUTE_CATEGORY => null, /* ATTRIBUTE_CATEGORY */
X_ATTRIBUTE1 => null, /* ATTRIBUTE1 */
X_ATTRIBUTE2 => null, /* ATTRIBUTE2 */
X_ATTRIBUTE3 => null, /* ATTRIBUTE3 */
X_ATTRIBUTE4 => null, /* ATTRIBUTE4 */
X_ATTRIBUTE5 => null, /* ATTRIBUTE5 */
X_ATTRIBUTE6 => null, /* ATTRIBUTE6 */
X_ATTRIBUTE7 => null, /* ATTRIBUTE7 */
X_ATTRIBUTE8 => null, /* ATTRIBUTE8 */
X_ATTRIBUTE9 => null, /* ATTRIBUTE9 */
X_ATTRIBUTE10 => null, /* ATTRIBUTE10 */
X_ATTRIBUTE11 => null, /* ATTRIBUTE11 */
X_ATTRIBUTE12 => null, /* ATTRIBUTE12 */
X_ATTRIBUTE13 => null, /* ATTRIBUTE13 */
X_ATTRIBUTE14 => null, /* ATTRIBUTE14 */
X_ATTRIBUTE15 => null, /* ATTRIBUTE15 */
X_OBJECT_VERSION_NUMBER => 1,
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => fnd_global.user_id,
X_CREATION_DATE => sysdate,
X_CREATED_BY => fnd_global.user_id,
X_LAST_UPDATE_LOGIN => fnd_global.login_id);
AHL_UNIT_EFFECTIVITIES_PKG.Insert_Row (
X_ROWID => l_rowid,
X_UNIT_EFFECTIVITY_ID => l_unit_effectivity_id,
X_CSI_ITEM_INSTANCE_ID => l_unit_effectivity_rec.csi_item_instance_id,
X_MR_INTERVAL_ID => l_unit_effectivity_rec.MR_INTERVAL_ID,
X_MR_EFFECTIVITY_ID => l_unit_effectivity_rec.MR_EFFECTIVITY_ID,
X_MR_HEADER_ID => l_unit_effectivity_rec.MR_header_id,
X_STATUS_CODE => l_unit_effectivity_rec.STATUS_CODE, /* status_code */
X_DUE_DATE => l_unit_effectivity_rec.due_date,
X_DUE_COUNTER_VALUE => l_unit_effectivity_rec.due_counter_value,
X_FORECAST_SEQUENCE => l_unit_effectivity_rec.FORECAST_SEQUENCE,
X_REPETITIVE_MR_FLAG => l_unit_effectivity_rec.REPETITIVE_MR_FLAG,
X_TOLERANCE_FLAG => l_unit_effectivity_rec.tolerance_flag,
X_REMARKS => l_unit_effectivity_rec.remarks,
X_MESSAGE_CODE => l_unit_effectivity_rec.message_code,
X_PRECEDING_UE_ID => l_unit_effectivity_rec.PRECEDING_UE_ID, /* preceding_ue_id */
X_DATE_RUN => sysdate, /* date_run */
X_SET_DUE_DATE => null, /* set due date */
X_ACCOMPLISHED_DATE => l_unit_effectivity_rec.ACCOMPLISHED_DATE, /* accomplished date */
X_SERVICE_LINE_ID => l_unit_effectivity_rec.service_line_id,
X_PROGRAM_MR_HEADER_ID => l_unit_effectivity_rec.program_mr_header_id,
X_CANCEL_REASON_CODE => l_unit_effectivity_rec.CANCEL_REASON_CODE, /* cancel_reason_code */
X_EARLIEST_DUE_DATE => l_unit_effectivity_rec.earliest_due_date,
X_LATEST_DUE_DATE => l_unit_effectivity_rec.latest_due_date,
X_defer_from_ue_id => l_unit_effectivity_rec.defer_from_ue_id,
X_cs_incident_id => l_unit_effectivity_rec.cs_incident_id,
X_qa_collection_id => l_unit_effectivity_rec.qa_collection_id,
X_orig_deferral_ue_id => l_unit_effectivity_rec.orig_deferral_ue_id,
X_application_usg_code => l_unit_effectivity_rec.application_usg_code,
X_object_type => l_unit_effectivity_rec.object_type,
X_counter_id => l_unit_effectivity_rec.counter_id,
x_manually_planned_flag => l_unit_effectivity_rec.manually_planned_flag,
X_LOG_SERIES_CODE => l_unit_effectivity_rec.log_series_code,
X_LOG_SERIES_NUMBER => l_unit_effectivity_rec.log_series_number,
X_FLIGHT_NUMBER => l_unit_effectivity_rec.flight_number,
X_MEL_CDL_TYPE_CODE => l_unit_effectivity_rec.mel_cdl_type_code,
X_POSITION_PATH_ID => l_unit_effectivity_rec.position_path_id,
X_ATA_CODE => l_unit_effectivity_rec.ATA_CODE,
X_UNIT_CONFIG_HEADER_ID => l_unit_effectivity_rec.unit_config_header_id,
X_ATTRIBUTE_CATEGORY => null, /* ATTRIBUTE_CATEGORY */
X_ATTRIBUTE1 => null, /* ATTRIBUTE1 */
X_ATTRIBUTE2 => null, /* ATTRIBUTE2 */
X_ATTRIBUTE3 => null, /* ATTRIBUTE3 */
X_ATTRIBUTE4 => null, /* ATTRIBUTE4 */
X_ATTRIBUTE5 => null, /* ATTRIBUTE5 */
X_ATTRIBUTE6 => null, /* ATTRIBUTE6 */
X_ATTRIBUTE7 => null, /* ATTRIBUTE7 */
X_ATTRIBUTE8 => null, /* ATTRIBUTE8 */
X_ATTRIBUTE9 => null, /* ATTRIBUTE9 */
X_ATTRIBUTE10 => null, /* ATTRIBUTE10 */
X_ATTRIBUTE11 => null, /* ATTRIBUTE11 */
X_ATTRIBUTE12 => null, /* ATTRIBUTE12 */
X_ATTRIBUTE13 => null, /* ATTRIBUTE13 */
X_ATTRIBUTE14 => null, /* ATTRIBUTE14 */
X_ATTRIBUTE15 => null, /* ATTRIBUTE15 */
X_OBJECT_VERSION_NUMBER => 1, /* object version */
X_CREATION_DATE => sysdate,
X_CREATED_BY => fnd_global.user_id,
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => fnd_global.user_id,
X_LAST_UPDATE_LOGIN => fnd_global.login_id );
AHL_UE_RELATIONSHIPS_PKG.Insert_Row(
X_UE_RELATIONSHIP_ID => l_ue_relationship_id,
X_UE_ID => l_parent_ue_id,
X_RELATED_UE_ID => l_unit_effectivity_id,
X_RELATIONSHIP_CODE => 'PARENT',
X_ORIGINATOR_UE_ID => l_new_top_ue_id,
X_ATTRIBUTE_CATEGORY => null, /* ATTRIBUTE_CATEGORY */
X_ATTRIBUTE1 => null, /* ATTRIBUTE1 */
X_ATTRIBUTE2 => null, /* ATTRIBUTE2 */
X_ATTRIBUTE3 => null, /* ATTRIBUTE3 */
X_ATTRIBUTE4 => null, /* ATTRIBUTE4 */
X_ATTRIBUTE5 => null, /* ATTRIBUTE5 */
X_ATTRIBUTE6 => null, /* ATTRIBUTE6 */
X_ATTRIBUTE7 => null, /* ATTRIBUTE7 */
X_ATTRIBUTE8 => null, /* ATTRIBUTE8 */
X_ATTRIBUTE9 => null, /* ATTRIBUTE9 */
X_ATTRIBUTE10 => null, /* ATTRIBUTE10 */
X_ATTRIBUTE11 => null, /* ATTRIBUTE11 */
X_ATTRIBUTE12 => null, /* ATTRIBUTE12 */
X_ATTRIBUTE13 => null, /* ATTRIBUTE13 */
X_ATTRIBUTE14 => null, /* ATTRIBUTE14 */
X_ATTRIBUTE15 => null, /* ATTRIBUTE15 */
X_OBJECT_VERSION_NUMBER => 1,
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => fnd_global.user_id,
X_CREATION_DATE => sysdate,
X_CREATED_BY => fnd_global.user_id,
X_LAST_UPDATE_LOGIN => fnd_global.login_id);
IF (l_delete_ue_id_tbl.count > 0) THEN
FOR i IN l_delete_ue_id_tbl.FIRST..l_delete_ue_id_tbl.LAST LOOP
-- check if UE assigned to a visit.
l_visit_status := AHL_UMP_UTIL_PKG.get_Visit_Status (l_delete_ue_id_tbl(i));
FOR l_ue_rec IN ahl_ue_csr(l_delete_ue_id_tbl(i)) LOOP
-- Delete the corresponding rows in ahl_schedule materials for this ue.
Delete_Sch_Materials(l_ue_rec.related_ue_id);
UPDATE AHL_UNIT_EFFECTIVITIES_B
SET status_code = 'EXCEPTION',
message_code = 'VISIT-ASSIGN',
object_version_number = object_version_number + 1,
DATE_RUN = sysdate,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = fnd_global.user_id,
LAST_UPDATE_LOGIN = fnd_global.login_id
WHERE unit_effectivity_id IN (SELECT related_ue_id
FROM ahl_ue_relationships
WHERE originator_ue_id = l_delete_ue_id_tbl(i)
AND relationship_code = 'PARENT');
UPDATE AHL_UNIT_EFFECTIVITIES_B
SET status_code = 'EXCEPTION',
message_code = 'VISIT-ASSIGN',
object_version_number = object_version_number + 1,
DATE_RUN = sysdate,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = fnd_global.user_id,
LAST_UPDATE_LOGIN = fnd_global.login_id
WHERE unit_effectivity_id = l_delete_ue_id_tbl(i);
FOR l_ue_rec IN ahl_ue_csr(l_delete_ue_id_tbl(i)) LOOP
-- delete unit effectivity record.
AHL_UNIT_EFFECTIVITIES_PKG.Delete_Row(l_ue_rec.related_ue_id);
Delete_Sch_Materials(l_ue_rec.related_ue_id);
DELETE FROM ahl_ue_relationships
WHERE originator_ue_id = l_delete_ue_id_tbl(i);
AHL_UNIT_EFFECTIVITIES_PKG.Delete_Row(l_delete_ue_id_tbl(i));
Delete_Sch_Materials(l_delete_ue_id_tbl(i));
PROCEDURE Delete_Sch_Materials(p_unit_effectivity_id IN NUMBER) IS
CURSOR ahl_sch_material_csr (p_unit_effectivity_id IN NUMBER) IS
SELECT scheduled_material_id
FROM ahl_schedule_materials
WHERE material_request_type = 'FORECAST'
AND unit_effectivity_id = p_unit_effectivity_id
FOR UPDATE NOWAIT;
AHL_DEBUG_PUB.Debug('Start Delete_Sch_Materials for UE:' || p_unit_effectivity_id,'UMP-ProcessUnit');
AHL_SCHEDULE_MATERIALS_PKG.delete_row(x_scheduled_material_id => sch_material_rec.scheduled_material_id);
AHL_DEBUG_PUB.Debug('End Delete_Sch_Materials');
END Delete_Sch_Materials;