[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,
-- JKJain, NR Analysis and Forecasting
fleet_header_id,
-- added for SB Enh
accomplish_trigger_type,
loop_chain_seq_num,
start_lc_ue_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)
AND nvl(accomplish_trigger_type,'X') NOT IN ('LOOP', 'CHAIN')
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 tmp_ue
WHERE preceding_mr_header_id IS NOT NULL AND
preceding_csi_item_instance_id IS NOT NULL AND
preceding_forecast_seq IS NOT NULL
-- added for SB Enh: last accomplishment of MR is available in applicable mrs table.
AND NOT EXISTS (select 'x' from ahl_applicable_mrs appl
where appl.mr_header_id = tmp_ue.preceding_mr_header_id
and appl.csi_item_instance_id = tmp_ue.csi_item_instance_id
and appl.ACCOMPLISHED_UE_ID is not null);
SELECT unit_effectivity_id FROM (
SELECT atu.unit_effectivity_id unit_effectivity_id, atu.due_date
FROM ahl_temp_unit_effectivities atu
--WHERE atu.mr_header_id = p_preceding_mr_header_id AND
WHERE atu.mr_header_id IN (select mr1.mr_header_id from ahl_mr_headers_b mr1
where mr1.title = (select mr2.title from ahl_mr_headers_b mr2
where mr2.mr_header_id = p_preceding_mr_header_id)
) AND
atu.csi_item_instance_id = p_preceding_item_instance
--forecast_sequence = p_preceding_forecast_seq;
SELECT atu.unit_effectivity_id unit_effectivity_id, atu.due_date
FROM ahl_temp_unit_SR_deferrals atu
--WHERE atu.mr_header_id = p_preceding_mr_header_id AND
WHERE atu.mr_header_id IN (select mr1.mr_header_id from ahl_mr_headers_b mr1
where mr1.title = (select mr2.title from ahl_mr_headers_b mr2
where mr2.mr_header_id = p_preceding_mr_header_id)
) AND
atu.csi_item_instance_id = p_preceding_item_instance
ORDER BY DUE_DATE ASC)
WHERE ROWNUM < 2;
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;
SELECT unit_effectivity_id INTO l_terminating_mr_header_id
FROM (
SELECT ue.unit_effectivity_id
BULK COLLECT INTO l_ue_id_tbl
FROM ahl_unit_effectivities_b ue, ahl_mr_headers_b mr
WHERE ue.mr_header_id = mr.mr_header_id
AND (ue.status_code IS NULL OR ue.status_code = 'INIT-DUE')
AND mr.title IN (select title from ahl_mr_headers_b where mr_header_id = l_term_mr_id_tbl(i))
ORDER BY ue.due_date asc)
where rownum < 2;
SELECT due_date, unit_effectivity_id INTO l_min_due_date, l_min_ue_id
FROM (
SELECT def.due_date, def.unit_effectivity_id
FROM ahl_temp_unit_SR_deferrals def, ahl_mr_headers_b mr1
WHERE def.csi_item_instance_id = l_temp_mr_rec.csi_item_instance_id
AND def.mr_header_id = mr1.mr_header_id
AND mr1.title = (select mr2.title from ahl_mr_headers_b mr2 where mr2.mr_header_id = l_temp_mr_rec.MR_header_id)
AND def.due_date IS NOT NULL
AND def.deferral_effective_on IS NOT NULL
AND def.orig_unit_effectivity_id IS NULL
ORDER BY due_date asc )
WHERE ROWNUM < 2;
SELECT due_date, unit_effectivity_id INTO l_min_mr_due_date, l_min_mr_ue_id
FROM (
SELECT ue.due_date, ue.unit_effectivity_id
FROM ahl_unit_effectivities_b ue, ahl_visit_tasks_b vts,
ahl_mr_headers_b mr1, ahl_mr_headers_b mr2
WHERE ue.csi_item_instance_id = l_temp_mr_rec.csi_item_instance_id
AND ue.mr_header_id = mr1.mr_header_id
AND mr1.title = mr2.title
AND mr1.version_number < mr2.version_number
AND mr2.mr_header_id = l_temp_mr_rec.MR_header_id
AND (ue.status_code IS NULL OR ue.status_code = 'INIT-DUE')
AND ue.due_date is not null
AND ue.defer_from_ue_id IS NULL -- do not pick deferrals
AND ue.unit_effectivity_id = vts.unit_effectivity_id
AND vts.status_code IN ('RELEASED', 'CLOSED')
AND vts.task_type_code = 'SUMMARY'
AND NOT EXISTS (select 'x' from ahl_ue_relationships
where related_ue_id = ue.unit_effectivity_id)
ORDER BY due_date asc )
WHERE ROWNUM < 2;
UPDATE AHL_UNIT_EFFECTIVITIES_B
SET REPETITIVE_MR_FLAG = 'N',
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_min_mr_ue_id;
UPDATE AHL_UNIT_EFFECTIVITIES_B
SET REPETITIVE_MR_FLAG = 'Y',
DATE_RUN = SYSDATE,
LAST_UPDATE_DATE = SYSDATE
WHERE unit_effectivity_id = l_min_ue_id;
UPDATE AHL_UNIT_EFFECTIVITIES_B
SET REPETITIVE_MR_FLAG = 'N',
DATE_RUN = SYSDATE,
LAST_UPDATE_DATE = SYSDATE
WHERE unit_effectivity_id = l_min_ue_id;
UPDATE AHL_UNIT_EFFECTIVITIES_B
SET REPETITIVE_MR_FLAG = 'Y',
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_min_mr_ue_id;
UPDATE AHL_UNIT_EFFECTIVITIES_B
SET REPETITIVE_MR_FLAG = 'N',
DATE_RUN = SYSDATE,
LAST_UPDATE_DATE = SYSDATE
WHERE unit_effectivity_id = l_min_ue_id;
UPDATE AHL_UNIT_EFFECTIVITIES_B
SET REPETITIVE_MR_FLAG = 'N',
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_min_mr_ue_id;
UPDATE AHL_UNIT_EFFECTIVITIES_B
SET REPETITIVE_MR_FLAG = 'Y',
DATE_RUN = SYSDATE,
LAST_UPDATE_DATE = SYSDATE
WHERE unit_effectivity_id = l_min_ue_id;
UPDATE AHL_UNIT_EFFECTIVITIES_B
SET REPETITIVE_MR_FLAG = 'Y',
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_min_mr_ue_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 => G_UC_HEADER_ID,--l_ue_rec.unit_config_header_id, -- JKJain, NR Analysis and Forecasting
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);
AHL_DEBUG_PUB.Debug('Start Update of Terminating MR..');
SELECT DISTINCT mr_header_id, csi_item_instance_id,terminating_mr_header_id
BULK COLLECT INTO l_mr_id_tbl, l_ii_id_tbl, l_term_mr_id_tbl
FROM AHL_APPLICABLE_MRS appl_mr
WHERE appl_mr.terminating_mr_header_id IS NOT NULL;
SELECT unit_effectivity_id INTO l_terminating_ue_id
FROM (
SELECT ue.unit_effectivity_id
FROM ahl_unit_effectivities_b ue, ahl_mr_headers_b mr
WHERE ue.mr_header_id = mr.mr_header_id
AND ue.csi_item_instance_id = l_ii_id_tbl(i)
AND (ue.status_code IS NULL OR ue.status_code = 'INIT-DUE')
AND mr.title IN (select title from ahl_mr_headers_b where mr_header_id = l_term_mr_id_tbl(i))
AND date_run >= l_start_time
ORDER BY ue.due_date asc)
where rownum < 2;
UPDATE ahl_unit_effectivities_b
SET terminating_ue_id = l_terminating_ue_id
WHERE csi_item_instance_id = l_ii_id_tbl(i)
AND mr_header_id IN (select mr_header_id from ahl_mr_headers_b
where title IN (select title from ahl_mr_headers_b where mr_header_id = l_mr_id_tbl(i))
)
AND (status_code IS NULL OR status_code = 'INIT-DUE');
AHL_DEBUG_PUB.Debug('End Update of Terminating MR..');
l_delete_flag := TRUE;
l_delete_flag := TRUE;
l_delete_flag := FALSE;
l_delete_flag := FALSE;
UPDATE ahl_unit_effectivities_b
SET repetitive_mr_flag = 'N',
date_run = sysdate,
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 = exception_rec.unit_effectivity_id;
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 => G_UC_HEADER_ID,--exception_rec.unit_config_header_id,-- JKJain, NR Analysis and Forecasting
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 => G_UC_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);
UPDATE ahl_simulation_plans_b
SET last_bue_run = SYSDATE, object_version_number = object_version_number + 1
WHERE primary_plan_flag = 'Y'
AND status_code = 'ACTIVE'
AND simulation_type = 'UMP';
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 => G_UC_HEADER_ID, -- JKJain, NR Analysis and Forecasting
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,
-- JKJain, NR Analysis and Forecasting
X_FLEET_HEADER_ID => p_x_temp_mr_rec.fleet_header_id);
UPDATE AHL_UNIT_EFFECTIVITIES_B
SET accomplish_trigger_type = p_x_temp_mr_rec.accomplish_trigger_type,
loop_chain_seq_num = p_x_temp_mr_rec.loop_chain_seq_num,
start_lc_ue_id = p_x_temp_mr_rec.start_lc_ue_id
WHERE rowid = l_rowid;
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 => G_UC_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 ,
-- JKJain, NR Analysis and Forecasting
X_FLEET_HEADER_ID => p_temp_mr_rec.fleet_header_id);
UPDATE ahl_unit_effectivities_b
SET accomplish_trigger_type = p_temp_mr_rec.accomplish_trigger_type,
loop_chain_seq_num = p_temp_mr_rec.loop_chain_seq_num,
start_lc_ue_id = p_temp_mr_rec.start_lc_ue_id,
terminating_ue_id = null -- fix for bug# 16567016; reset this value.
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,
rowid
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 rowid = l_temp_grp_rec.rowid;
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,
-- JKJain, NR Analysis and Forecasting
FLEET_HEADER_ID = p_temp_mr_rec.fleet_header_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
ORDER BY csi_item_instance_id, mr_header_id
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,
-- added for SB Enh
accomplish_trigger_type,
start_lc_ue_id,
loop_chain_seq_num,
-- JKJain, NR Analysis and Forecasting
fleet_header_id
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 => G_UC_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,
-- JKJain, NR Analysis and Forecasting
X_FLEET_HEADER_ID => l_unit_effectivity_rec.fleet_header_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 => G_UC_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 => 'Y', -- modified to fix bug# 9078331
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 => G_UC_HEADER_ID,--l_unit_effectivity_rec.unit_config_header_id, -- JKJain, NR Analysis and Forecasting
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;
UPDATE AHL_UNIT_EFFECTIVITIES_B
SET accomplish_trigger_type = l_unit_effectivity_rec.accomplish_trigger_type,
loop_chain_seq_num = l_unit_effectivity_rec.loop_chain_seq_num,
start_lc_ue_id = l_unit_effectivity_rec.start_lc_ue_id
WHERE unit_effectivity_id = l_new_top_ue_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 => 'Y', -- modified to fix bug# 9078331
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 => G_UC_HEADER_ID,--l_unit_effectivity_rec.unit_config_header_id, -- JKJain, NR Analysis and Forecasting
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;
SELECT tmp_ue.unit_effectivity_id,
tmp_ue.csi_item_instance_id,
tmp_ue.MR_header_id,
tmp_ue.due_date,
tmp_ue.mr_interval_id,
tmp_ue.mr_effectivity_id,
tmp_ue.due_counter_value,
tmp_ue.forecast_sequence,
tmp_ue.repetitive_mr_flag,
tmp_ue.tolerance_flag,
tmp_ue.message_code,
tmp_ue.earliest_due_date,
tmp_ue.latest_due_date,
tmp_ue.counter_id,
tmp_ue.accomplish_trigger_type,
tmp_ue.loop_chain_seq_num,
tmp_ue.start_mr_header_id,
tmp_ue.start_lc_ue_id,
tmp_ue.rowid,
(select title from ahl_mr_headers_b mr where mr_header_id = tmp_ue.MR_header_id) mr_title
FROM ahl_temp_unit_effectivities tmp_ue, ahl_mr_headers_b mr
WHERE tmp_ue.start_mr_header_id = mr.mr_header_id
AND mr.title = p_mr_title
AND tmp_ue.csi_item_instance_id = p_csi_ii_id
AND tmp_ue.accomplish_trigger_type IN ('LOOP')
ORDER BY csi_item_instance_id, start_mr_header_id, forecast_sequence, loop_chain_seq_num;
SELECT distinct mr.title, csi_item_instance_id
FROM ahl_applicable_mrs appl, ahl_mr_headers_b mr
where appl.accomplish_trigger_type = 'LOOP'
and appl.start_mr_header_id = appl.mr_header_id
and appl.mr_header_id = mr.mr_header_id;
SELECT ue.unit_effectivity_Id, ue.loop_chain_seq_num, mr.title, 'N' match_flag
FROM ahl_unit_effectivities_b ue, ahl_mr_headers_b mr
WHERE ue.mr_header_id = mr.mr_header_id
AND ue.start_lc_ue_id = p_start_lc_ue_id
AND ue.accomplish_trigger_type = 'LOOP'
AND (ue.status_code IS NULL OR ue.status_code = 'INIT-DUE')
AND loop_chain_seq_num > 1
AND ue.defer_from_ue_id IS NULL
ORDER BY loop_chain_seq_num;
SELECT ue.unit_effectivity_id
FROM ahl_temp_unit_effectivities ue, ahl_mr_headers_b mr
WHERE ue.start_mr_header_id = mr.mr_header_id
AND mr.title = p_mr_title
AND ue.csi_item_instance_id = p_csi_ii_id
AND ue.accomplish_trigger_type IN ('LOOP')
--AND (ue.status_code IS NULL OR ue.status_code = 'INIT-DUE')
ORDER BY csi_item_instance_id, due_date, forecast_sequence, loop_chain_seq_num;
l_old_start_lc_ue_tbl.delete;
SELECT due_date, unit_effectivity_id INTO l_min_due_date, l_min_ue_id
FROM (
SELECT def.due_date, def.unit_effectivity_id
FROM ahl_temp_unit_SR_deferrals def, ahl_mr_headers_b mr1
WHERE def.csi_item_instance_id = l_temp_ue_records(i).csi_item_instance_id
AND def.mr_header_id = mr1.mr_header_id
AND mr1.title = (select mr2.title from ahl_mr_headers_b mr2 where mr2.mr_header_id = l_temp_ue_records(i).MR_header_id)
AND def.due_date IS NOT NULL
AND def.deferral_effective_on IS NOT NULL
AND def.orig_unit_effectivity_id IS NULL
ORDER BY due_date asc )
WHERE ROWNUM < 2;
UPDATE AHL_UNIT_EFFECTIVITIES_B
SET repetitive_mr_flag = 'N',
preceding_ue_id = null
WHERE UNIT_EFFECTIVITY_ID = l_min_ue_id;
UPDATE ahl_temp_unit_effectivities
SET UNIT_EFFECTIVITY_ID = l_temp_ue_records(i).UNIT_EFFECTIVITY_ID
WHERE rowid = l_temp_ue_records(i).rowid;
UPDATE ahl_temp_unit_effectivities
SET unit_effectivity_id = l_temp_mr_rec.unit_effectivity_id
WHERE rowid = l_temp_ue_records(i).rowid;
UPDATE ahl_unit_effectivities_b
SET start_lc_ue_id = l_temp_ue_records(i).start_lc_ue_id
WHERE unit_effectivity_id = l_temp_mr_rec.unit_effectivity_id;
UPDATE AHL_UNIT_EFFECTIVITIES_B
SET
due_date = l_upd_UE_tbl_records(i).due_date,
mr_interval_id = l_upd_UE_tbl_records(i).mr_interval_id,
mr_effectivity_id = l_upd_UE_tbl_records(i).mr_effectivity_id,
due_counter_value = l_upd_UE_tbl_records(i).due_counter_value,
forecast_sequence = l_upd_UE_tbl_records(i).forecast_sequence,
repetitive_mr_flag = l_upd_UE_tbl_records(i).repetitive_mr_flag,
tolerance_flag = l_upd_UE_tbl_records(i).tolerance_flag,
message_code = l_upd_UE_tbl_records(i).message_code,
earliest_due_date = l_upd_UE_tbl_records(i).earliest_due_date,
latest_due_date = l_upd_UE_tbl_records(i).latest_due_date,
counter_id = l_upd_UE_tbl_records(i).counter_id,
accomplish_trigger_type = l_upd_UE_tbl_records(i).accomplish_trigger_type,
loop_chain_seq_num = l_upd_UE_tbl_records(i).loop_chain_seq_num,
start_lc_ue_id = l_upd_UE_tbl_records(i).start_lc_ue_id,
date_run = sysdate,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
object_version_number = OBJECT_VERSION_NUMBER + 1
WHERE
unit_effectivity_id = l_upd_UE_tbl_records(i).UNIT_EFFECTIVITY_ID;
l_upd_UE_tbl_records.delete;
l_temp_ue_records.delete;
UPDATE AHL_UNIT_EFFECTIVITIES_B
SET preceding_ue_id = null
where unit_effectivity_id = l_ue_id_tbl(l_index);
UPDATE AHL_UNIT_EFFECTIVITIES_B
SET preceding_ue_id = null
where unit_effectivity_id = l_ue_id_tbl(l_index);
UPDATE AHL_UNIT_EFFECTIVITIES_B
SET preceding_ue_id = l_ue_id_tbl(i-1)
WHERE unit_effectivity_id = l_ue_id_tbl(i);
SELECT tmp_ue.unit_effectivity_id,
tmp_ue.csi_item_instance_id,
tmp_ue.MR_header_id,
tmp_ue.due_date,
tmp_ue.mr_interval_id,
tmp_ue.mr_effectivity_id,
tmp_ue.due_counter_value,
tmp_ue.forecast_sequence,
tmp_ue.repetitive_mr_flag,
tmp_ue.tolerance_flag,
tmp_ue.message_code,
tmp_ue.earliest_due_date,
tmp_ue.latest_due_date,
tmp_ue.counter_id,
tmp_ue.accomplish_trigger_type,
tmp_ue.loop_chain_seq_num,
tmp_ue.start_mr_header_id,
tmp_ue.start_lc_ue_id,
tmp_ue.rowid,
(select title from ahl_mr_headers_b mr where mr_header_id = tmp_ue.MR_header_id) mr_title
FROM ahl_temp_unit_effectivities tmp_ue, ahl_mr_headers_b mr
WHERE tmp_ue.start_mr_header_id = mr.mr_header_id
AND mr.title = p_mr_title
AND tmp_ue.csi_item_instance_id = p_csi_ii_id
AND tmp_ue.accomplish_trigger_type IN ('CHAIN')
ORDER BY csi_item_instance_id, start_mr_header_id, forecast_sequence, loop_chain_seq_num;
SELECT distinct mr.title, csi_item_instance_id
FROM ahl_applicable_mrs appl, ahl_mr_headers_b mr
where appl.accomplish_trigger_type = 'CHAIN'
and appl.start_mr_header_id = appl.mr_header_id
and appl.mr_header_id = mr.mr_header_id;
SELECT ue.unit_effectivity_Id, ue.loop_chain_seq_num, mr.title, 'N' match_flag
FROM ahl_unit_effectivities_b ue, ahl_mr_headers_b mr
WHERE ue.mr_header_id = mr.mr_header_id
AND ue.start_lc_ue_id = p_start_lc_ue_id
AND ue.accomplish_trigger_type = 'CHAIN'
AND (ue.status_code IS NULL OR ue.status_code = 'INIT-DUE')
AND loop_chain_seq_num > 1
AND ue.defer_from_ue_id IS NULL
ORDER BY loop_chain_seq_num;
SELECT ue.unit_effectivity_id
FROM ahl_temp_unit_effectivities ue, ahl_mr_headers_b mr
WHERE ue.start_mr_header_id = mr.mr_header_id
AND mr.title = p_mr_title
AND ue.csi_item_instance_id = p_csi_ii_id
AND ue.start_lc_ue_id = p_start_lc_ue_id
AND ue.accomplish_trigger_type IN ('CHAIN')
--AND (ue.status_code IS NULL OR ue.status_code = 'INIT-DUE')
ORDER BY csi_item_instance_id, due_date, forecast_sequence, loop_chain_seq_num;
SELECT ue.unit_effectivity_id
FROM ahl_unit_effectivities_b ue
WHERE ue.start_lc_ue_id = p_start_lc_ue_id
AND ue.accomplish_trigger_type IN ('CHAIN')
AND (ue.status_code IS NULL OR ue.status_code = 'INIT-DUE')
ORDER BY loop_chain_seq_num;
select distinct start_lc_ue_id
from ahl_temp_unit_effectivities ue,ahl_mr_headers_b mr
WHERE ue.start_mr_header_id = mr.mr_header_id
AND mr.title = p_mr_title
AND ue.csi_item_instance_id = p_csi_ii_id
AND ue.accomplish_trigger_type IN ('CHAIN');
l_old_start_lc_ue_tbl.delete;
SELECT due_date, unit_effectivity_id INTO l_min_due_date, l_min_ue_id
FROM (
SELECT def.due_date, def.unit_effectivity_id
FROM ahl_temp_unit_SR_deferrals def, ahl_mr_headers_b mr1
WHERE def.csi_item_instance_id = l_temp_ue_records(i).csi_item_instance_id
AND def.mr_header_id = mr1.mr_header_id
AND mr1.title = (select mr2.title from ahl_mr_headers_b mr2 where mr2.mr_header_id = l_temp_ue_records(i).MR_header_id)
AND def.due_date IS NOT NULL
AND def.deferral_effective_on IS NOT NULL
AND def.orig_unit_effectivity_id IS NULL
ORDER BY due_date asc )
WHERE ROWNUM < 2;
UPDATE AHL_UNIT_EFFECTIVITIES_B
SET repetitive_mr_flag = 'N',
preceding_ue_id = null
WHERE UNIT_EFFECTIVITY_ID = l_min_ue_id;
UPDATE ahl_temp_unit_effectivities
SET UNIT_EFFECTIVITY_ID = l_temp_ue_records(i).UNIT_EFFECTIVITY_ID
WHERE rowid = l_temp_ue_records(i).rowid;
UPDATE ahl_temp_unit_effectivities
SET unit_effectivity_id = l_temp_mr_rec.unit_effectivity_id
WHERE rowid = l_temp_ue_records(i).rowid;
UPDATE ahl_unit_effectivities_b
SET start_lc_ue_id = l_temp_ue_records(i).start_lc_ue_id
WHERE unit_effectivity_id = l_temp_mr_rec.unit_effectivity_id;
UPDATE AHL_UNIT_EFFECTIVITIES_B
SET
due_date = l_upd_UE_tbl_records(i).due_date,
mr_interval_id = l_upd_UE_tbl_records(i).mr_interval_id,
mr_effectivity_id = l_upd_UE_tbl_records(i).mr_effectivity_id,
due_counter_value = l_upd_UE_tbl_records(i).due_counter_value,
forecast_sequence = l_upd_UE_tbl_records(i).forecast_sequence,
repetitive_mr_flag = l_upd_UE_tbl_records(i).repetitive_mr_flag,
tolerance_flag = l_upd_UE_tbl_records(i).tolerance_flag,
message_code = l_upd_UE_tbl_records(i).message_code,
earliest_due_date = l_upd_UE_tbl_records(i).earliest_due_date,
latest_due_date = l_upd_UE_tbl_records(i).latest_due_date,
counter_id = l_upd_UE_tbl_records(i).counter_id,
accomplish_trigger_type = l_upd_UE_tbl_records(i).accomplish_trigger_type,
loop_chain_seq_num = l_upd_UE_tbl_records(i).loop_chain_seq_num,
start_lc_ue_id = l_upd_UE_tbl_records(i).start_lc_ue_id,
date_run = sysdate,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
object_version_number = OBJECT_VERSION_NUMBER + 1
WHERE
unit_effectivity_id = l_upd_UE_tbl_records(i).UNIT_EFFECTIVITY_ID;
l_upd_UE_tbl_records.delete;
l_temp_ue_records.delete;
UPDATE AHL_UNIT_EFFECTIVITIES_B
SET preceding_ue_id = null
where unit_effectivity_id = l_ue_id_tbl(l_index);
UPDATE AHL_UNIT_EFFECTIVITIES_B
SET preceding_ue_id = null
where unit_effectivity_id = l_ue_id_tbl(l_index);
UPDATE AHL_UNIT_EFFECTIVITIES_B
SET preceding_ue_id = l_ue_id_tbl(i-1)
WHERE unit_effectivity_id = l_ue_id_tbl(i);
SELECT SIMULATION_UE_ID
FROM AHL_UE_SIMULATIONS
WHERE SIMULATION_PLAN_ID = c_simulation_plan_id
AND UNIT_CONFIG_HEADER_ID = c_uc_header_id;
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,
fleet_header_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 unit_effectivity_id,
csi_item_instance_id,
mr_header_id,
due_date,
status_code,
mr_interval_id,
mr_effectivity_id,
due_counter_value,
forecast_sequence,
repetitive_mr_flag,
tolerance_flag,
message_code,
earliest_due_date,
latest_due_date,
counter_id,
fleet_header_id,
cs_incident_id,
manually_planned_flag,
defer_from_ue_id,
counter_id
from ahl_unit_effectivities_b
WHERE csi_item_instance_id = c_csi_item_instance_id and
defer_from_ue_id is null and
(status_code is null or status_code = 'INIT-DUE') and
cs_incident_id IS NOT NULL and
object_type = 'SR'
;
SELECT unit_effectivity_id
FROM ahl_unit_effectivities_b
WHERE csi_item_instance_id = c_csi_item_instance_id and
defer_from_ue_id is null and
((status_code is null and manually_planned_flag = 'Y') OR status_code = 'INIT-DUE') and
NOT EXISTS ( SELECT 'X'
FROM ahl_ue_relationships
where related_ue_id = unit_effectivity_id )
;
SELECT ue.unit_effectivity_id,
temp.due_date,
temp.tolerance_flag,
temp.message_code,
temp.counter_id
from ahl_temp_unit_sr_deferrals temp, ahl_unit_effectivities_b ue
WHERE ue.unit_effectivity_id = temp.unit_effectivity_id and
temp.csi_item_instance_id = c_csi_item_instance_id and
temp.orig_unit_effectivity_id IS NULL and
temp.deferral_effective_on IS NOT NULL
ORDER BY DEFERRAL_EFFECTIVE_ON ASC;
SELECT related_ue_id child_ue_id, ue_id parent_ue_id
FROM ahl_ue_relationships
START WITH ue_id = c_unit_effectivity_id
AND relationship_code = 'PARENT'
CONNECT BY PRIOR related_ue_id = ue_id
AND relationship_code = 'PARENT'
ORDER BY level;
SELECT related_ue_id
FROM ahl_ue_relationships
WHERE ORIGINATOR_UE_ID = c_unit_effectivity_id
AND relationship_code = 'PARENT';
DELETE FROM AHL_UE_SIMULATIONS WHERE SIMULATION_PLAN_ID = G_SIMULATION_PLAN_ID AND UNIT_CONFIG_HEADER_ID = G_UC_HEADER_ID;
l_temp_sim_ue_rec.last_update_date := sysdate;
l_temp_sim_ue_rec.last_updated_by := l_user_id;
l_temp_sim_ue_rec.last_update_login := fnd_global.login_id;
UPDATE ahl_temp_unit_effectivities
SET unit_effectivity_id = l_temp_sim_ue_rec.SIMULATION_UE_ID
WHERE CURRENT OF temp_individual_mrs_csr;
l_ue_simue_id_table.DELETE;
UPDATE AHL_UE_SIMULATIONS
SET originator_ue_id = l_simulation_ue_id
WHERE simulation_ue_id = l_simulation_ue_id;
l_ue_simue_id_table.DELETE;
UPDATE AHL_UE_SIMULATIONS
SET originator_ue_id = l_simulation_ue_id
WHERE simulation_ue_id = l_simulation_ue_id;
G_SIM_UE_ID_TBL.DELETE;
UPDATE AHL_SIMULATION_PLANS_B
SET LAST_BUE_RUN = sysdate
WHERE simulation_plan_id = p_simulation_plan_id;
INSERT INTO AHL_UE_SIMULATIONS (
SIMULATION_UE_ID ,
SIMULATION_PLAN_ID ,
CSI_ITEM_INSTANCE_ID ,
MR_HEADER_ID ,
FLEET_HEADER_ID ,
UNIT_CONFIG_HEADER_ID ,
DUE_DATE ,
STATUS_CODE ,
OBJECT_VERSION_NUMBER ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_LOGIN ,
SECURITY_GROUP_ID ,
MR_EFFECTIVITY_ID ,
MR_INTERVAL_ID ,
DUE_COUNTER_VALUE ,
FORECAST_SEQUENCE ,
REPETITIVE_MR_FLAG ,
TOLERANCE_FLAG ,
MESSAGE_CODE ,
DATE_RUN ,
PRECEDING_UE_ID ,
EARLIEST_DUE_DATE ,
LATEST_DUE_DATE ,
DEFER_FROM_UE_ID ,
CS_INCIDENT_ID ,
COUNTER_ID ,
UNIT_EFFECTIVITY_ID ,
ORIGINATOR_UE_ID ,
PARENT_UE_ID
) VALUES (
p_x_temp_sim_ue_rec.SIMULATION_UE_ID ,
p_x_temp_sim_ue_rec.SIMULATION_PLAN_ID ,
p_x_temp_sim_ue_rec.CSI_ITEM_INSTANCE_ID ,
p_x_temp_sim_ue_rec.MR_HEADER_ID ,
p_x_temp_sim_ue_rec.FLEET_HEADER_ID ,
p_x_temp_sim_ue_rec.UNIT_CONFIG_HEADER_ID ,
p_x_temp_sim_ue_rec.DUE_DATE ,
p_x_temp_sim_ue_rec.STATUS_CODE ,
p_x_temp_sim_ue_rec.OBJECT_VERSION_NUMBER ,
p_x_temp_sim_ue_rec.LAST_UPDATE_DATE ,
p_x_temp_sim_ue_rec.LAST_UPDATED_BY ,
p_x_temp_sim_ue_rec.CREATION_DATE ,
p_x_temp_sim_ue_rec.CREATED_BY ,
p_x_temp_sim_ue_rec.LAST_UPDATE_LOGIN ,
p_x_temp_sim_ue_rec.SECURITY_GROUP_ID ,
p_x_temp_sim_ue_rec.MR_EFFECTIVITY_ID ,
p_x_temp_sim_ue_rec.MR_INTERVAL_ID ,
p_x_temp_sim_ue_rec.DUE_COUNTER_VALUE ,
p_x_temp_sim_ue_rec.FORECAST_SEQUENCE ,
p_x_temp_sim_ue_rec.REPETITIVE_MR_FLAG ,
p_x_temp_sim_ue_rec.TOLERANCE_FLAG ,
p_x_temp_sim_ue_rec.MESSAGE_CODE ,
p_x_temp_sim_ue_rec.DATE_RUN ,
p_x_temp_sim_ue_rec.PRECEDING_UE_ID ,
p_x_temp_sim_ue_rec.EARLIEST_DUE_DATE ,
p_x_temp_sim_ue_rec.LATEST_DUE_DATE ,
p_x_temp_sim_ue_rec.DEFER_FROM_UE_ID ,
p_x_temp_sim_ue_rec.CS_INCIDENT_ID ,
p_x_temp_sim_ue_rec.COUNTER_ID ,
p_x_temp_sim_ue_rec.UNIT_EFFECTIVITY_ID ,
p_x_temp_sim_ue_rec.ORIGINATOR_UE_ID ,
p_x_temp_sim_ue_rec.PARENT_UE_ID
);
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_sim_ue_rec.simulation_ue_id
WHERE csi_item_instance_id = p_x_temp_sim_ue_rec.csi_item_instance_id AND
mr_header_id = p_x_temp_sim_ue_rec.mr_header_id AND
forecast_sequence = p_x_temp_sim_ue_rec.forecast_sequence;
l_temp_child_rec.last_update_date := sysdate;
l_temp_child_rec.last_updated_by := p_x_temp_sim_ue_rec.last_updated_by;
l_temp_child_rec.last_update_login := p_x_temp_sim_ue_rec.last_update_login;
UPDATE ahl_temp_unit_effectivities
SET unit_effectivity_id = l_temp_child_rec.simulation_ue_id
WHERE CURRENT OF ahl_temp_grp_csr;
SELECT unit_effectivity_id,
csi_item_instance_id,
mr_header_id,
due_date,
status_code,
mr_interval_id,
mr_effectivity_id,
due_counter_value,
forecast_sequence,
repetitive_mr_flag,
tolerance_flag,
message_code,
earliest_due_date,
latest_due_date,
counter_id,
fleet_header_id,
cs_incident_id,
manually_planned_flag,
defer_from_ue_id
from ahl_unit_effectivities_b
where unit_effectivity_id = c_unit_effectivity_id;
l_temp_sim_ue_rec.last_update_date := sysdate;
l_temp_sim_ue_rec.last_updated_by := fnd_global.user_id;
l_temp_sim_ue_rec.last_update_login := fnd_global.login_id;