[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Update_Unit_Effectivity(
p_unit_Effectivity_rec IN AHL_UMP_UNITMAINT_PVT.Unit_Effectivity_rec_type);
PROCEDURE Update_Thresholds(
p_unit_Effectivity_rec IN AHL_UMP_UNITMAINT_PVT.Unit_Effectivity_rec_type,
p_x_unit_threshold_tbl IN OUT NOCOPY AHL_UMP_UNITMAINT_PVT.Unit_Threshold_tbl_type);
PROCEDURE Update_Accomplishments(
p_x_unit_accomplish_tbl IN OUT NOCOPY AHL_UMP_UNITMAINT_PVT.Unit_Accomplish_tbl_type,
p_unit_Effectivity_rec IN AHL_UMP_UNITMAINT_PVT.Unit_Effectivity_rec_type);
PROCEDURE Capture_MR_Updates
(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
p_default IN VARCHAR2 := FND_API.G_TRUE,
p_module_type IN VARCHAR2 := NULL,
p_unit_Effectivity_tbl IN AHL_UMP_UNITMAINT_PVT.Unit_Effectivity_tbl_type,
p_x_unit_threshold_tbl IN OUT NOCOPY AHL_UMP_UNITMAINT_PVT.Unit_Threshold_tbl_type,
p_x_unit_accomplish_tbl IN OUT NOCOPY AHL_UMP_UNITMAINT_PVT.Unit_Accomplish_tbl_type,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2) IS
l_api_version CONSTANT NUMBER := 1.5;
l_api_name CONSTANT VARCHAR2(30) := 'Capture_MR_Updates';
SAVEPOINT Capture_MR_Updates_pvt;
AHL_DEBUG_PUB.debug('About to update thresholds', 'UMP');
Update_Thresholds(l_unit_Effectivity_rec,
l_unit_threshold_tbl);
AHL_DEBUG_PUB.debug('About to update accomplishments', 'UMP');
Update_Accomplishments(l_unit_accomplish_tbl, l_unit_Effectivity_rec);
AHL_DEBUG_PUB.debug('About to update unit effectivity', 'UMP');
Update_Unit_Effectivity(l_unit_Effectivity_rec);
AHL_DEBUG_PUB.debug('Updated unit effectivity', 'UMP');
l_unit_threshold_tbl.DELETE;
l_unit_accomplish_tbl.DELETE;
Rollback to Capture_MR_Updates_pvt;
Rollback to Capture_MR_Updates_pvt;
Rollback to Capture_MR_Updates_pvt;
p_procedure_name => 'Capture_MR_Updates',
p_error_text => SUBSTR(SQLERRM,1,240));
END Capture_MR_Updates;
SELECT 'x'
FROM AHL_UE_RELATIONSHIPS
WHERE related_ue_id = p_ue_id;
SELECT 'x'
FROM AHL_UNIT_EFFECTIVITIES_APP_V
WHERE MR_HEADER_ID = p_mr_id AND
CSI_ITEM_INSTANCE_ID = p_item_instance_id AND
status_code IN ('INIT-DUE', 'INIT-ACCOMPLISHED') AND
UNIT_EFFECTIVITY_ID <> p_ue_id;
SELECT MR_HEADER_ID, CSI_ITEM_INSTANCE_ID, REPETITIVE_MR_FLAG, STATUS_CODE
FROM AHL_UNIT_EFFECTIVITIES_APP_V
WHERE UNIT_EFFECTIVITY_ID = p_ue_id;
SELECT MR_HEADER_ID
FROM AHL_MR_HEADERS_B
WHERE TITLE = p_mr_title AND
VERSION_NUMBER = p_mr_version_number;
SELECT INSTANCE_ID
FROM CSI_ITEM_INSTANCES
WHERE INSTANCE_NUMBER = p_instance_number;
SELECT LOOKUP_CODE
FROM fnd_lookup_values_vl
WHERE lookup_type = 'AHL_UNIT_EFFECTIVITY_STATUS' AND
MEANING = p_status_meaning;
/*SELECT co.counter_id
FROM csi_cp_counters_v co, ahl_unit_effectivities_app_v ue
WHERE co.COUNTER_NAME = p_counter_name AND
ue.UNIT_EFFECTIVITY_ID = p_ue_id AND
ue.csi_item_instance_id = CUSTOMER_PRODUCT_ID;*/
/*SELECT c.counter_id
FROM CS_COUNTERS C, CS_COUNTER_GROUPS CTRGRP, CSI_ITEM_INSTANCES CII, MTL_SYSTEM_ITEMS_KFV MSITEM, ahl_unit_effectivities_app_v ue
WHERE C.COUNTER_GROUP_ID(+) = CTRGRP.COUNTER_GROUP_ID
AND CTRGRP.SOURCE_OBJECT_CODE = 'CP'
AND CTRGRP.SOURCE_OBJECT_ID = CII.INSTANCE_ID
AND MSITEM.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
AND MSITEM.ORGANIZATION_ID = CII.INV_MASTER_ORGANIZATION_ID
AND ue.csi_item_instance_id = CII.INSTANCE_ID
AND c.NAME = p_counter_name
AND ue.UNIT_EFFECTIVITY_ID = p_ue_id;*/
select
cc.counter_id
from
csi_counters_vl cc,
csi_counter_associations cca,
ahl_unit_effectivities_b ue
where
cc.counter_id (+) = cca.counter_id
and cca.source_object_code = 'CP'
and cca.source_object_id = ue.csi_item_instance_id
and cc.name = p_counter_name
--and cc.counter_template_name = p_counter_name
and ue.unit_effectivity_id = p_ue_id;
/*SELECT co.counter_id
FROM csi_cp_counters_v co, ahl_unit_effectivities_app_v ue
WHERE co.COUNTER_NAME = p_counter_name AND
ue.UNIT_EFFECTIVITY_ID = p_ue_id AND
ue.csi_item_instance_id = CUSTOMER_PRODUCT_ID;*/
SELECT c.counter_id
FROM CS_COUNTERS C, CS_COUNTER_GROUPS CTRGRP, CSI_ITEM_INSTANCES CII, MTL_SYSTEM_ITEMS_KFV MSITEM, ahl_unit_effectivities_app_v ue
WHERE C.COUNTER_GROUP_ID(+) = CTRGRP.COUNTER_GROUP_ID
AND CTRGRP.SOURCE_OBJECT_CODE = 'CP'
AND CTRGRP.SOURCE_OBJECT_ID = CII.INSTANCE_ID
AND MSITEM.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
AND MSITEM.ORGANIZATION_ID = CII.INV_MASTER_ORGANIZATION_ID
AND ue.csi_item_instance_id = CII.INSTANCE_ID
AND c.NAME = p_counter_name
AND ue.UNIT_EFFECTIVITY_ID = p_ue_id; */
select
cc.counter_id
from
csi_counters_vl cc,
csi_counter_associations cca,
ahl_unit_effectivities_b ue
where
cc.counter_id (+) = cca.counter_id
and cca.source_object_code = 'CP'
and cca.source_object_id = ue.csi_item_instance_id
and cc.name = p_counter_name
--and cc.counter_template_name = p_counter_name
and ue.unit_effectivity_id = p_ue_id;
SELECT 'x'
FROM ahl_unit_effectivities_app_v
WHERE (status_code not in ('INIT-ACCOMPLISHED', 'TERMINATED', 'MR-TERMINATE', 'ACCOMPLISHED','SR-CLOSED','DEFERRED','CANCELLED') OR status_code IS NULL)
AND unit_effectivity_id in (
SELECT related_ue_id
FROM ahl_ue_relationships
START WITH ue_id = p_ue_id
AND relationship_code = 'PARENT'
CONNECT BY ue_id = PRIOR related_ue_id
AND relationship_code = 'PARENT');
SELECT status_code
FROM AHL_UNIT_EFFECTIVITIES_APP_V
WHERE MR_HEADER_ID = p_mr_id AND
CSI_ITEM_INSTANCE_ID = p_item_instance_id AND
status_code IN ('INIT-DUE', 'INIT-ACCOMPLISHED','DEFERRED') AND
UNIT_EFFECTIVITY_ID <> p_ue_id;
SELECT STATUS_CODE, OBJECT_VERSION_NUMBER, MR_HEADER_ID,
CSI_ITEM_INSTANCE_ID, PRECEDING_UE_ID,SERVICE_LINE_ID
FROM AHL_UNIT_EFFECTIVITIES_APP_V
WHERE UNIT_EFFECTIVITY_ID = p_ue_id;
SELECT UNIT_EFFECTIVITY_ID
FROM AHL_UNIT_EFFECTIVITIES_APP_V
WHERE MR_HEADER_ID = p_mr_id AND
CSI_ITEM_INSTANCE_ID = p_item_instance_id AND
FORECAST_SEQUENCE = (SELECT MIN(FORECAST_SEQUENCE) FROM AHL_UNIT_EFFECTIVITIES_APP_V
WHERE MR_HEADER_ID = p_mr_id AND
CSI_ITEM_INSTANCE_ID = p_item_instance_id AND
(STATUS_CODE IS NULL OR
STATUS_CODE = 'INIT-DUE'))
AND (STATUS_CODE IS NULL OR STATUS_CODE = 'INIT-DUE');
SELECT MIN(unit_effectivity_id)
FROM AHL_UNIT_EFFECTIVITIES_VL
WHERE MR_HEADER_ID = p_mr_id AND
CSI_ITEM_INSTANCE_ID = p_item_instance_id AND
service_line_id = p_service_line_id AND
(STATUS_CODE IS NULL OR
STATUS_CODE IN ('INIT-DUE'));
SELECT MR_HEADER_ID, CSI_ITEM_INSTANCE_ID
FROM AHL_UNIT_EFFECTIVITIES_APP_V
WHERE UNIT_EFFECTIVITY_ID = p_pred_ue_id;
SELECT 'x'
FROM FND_LOOKUP_VALUES_VL
WHERE LOOKUP_TYPE = 'AHL_UNIT_EFFECTIVITY_STATUS' AND
LOOKUP_CODE IN ('ACCOMPLISHED','INIT-ACCOMPLISHED','INIT-DUE','CANCELLED') AND
LOOKUP_CODE = p_status_code;
SELECT collection_id
FROM QA_RESULTS
WHERE collection_id = p_qa_collection_id
AND rownum < 2;
SELECT 'x'
FROM ahl_unit_deferrals_b
WHERE unit_deferral_id = p_unit_deferral_id
AND unit_deferral_type = 'INIT-DUE';
SELECT CSI.instance_number, UE.due_date, MR.title
FROM ahl_unit_effectivities_b UE, ahl_mr_headers_b MR,
csi_item_instances CSI
WHERE UE.unit_effectivity_id = p_ue_id
AND UE.mr_header_id = MR.mr_header_id
AND UE.csi_item_instance_id = CSI.instance_id;
SELECT CS.incident_number
FROM cs_incident_links CLK, CS_INCIDENTS_ALL_B CS
WHERE CS.incident_id = CLK.subject_id
AND CLK.object_id = p_ue_id
AND CLK.object_type = p_object_type
AND CLK.subject_type = p_subject_type
AND CLK.link_type_id = p_link_type_id;
SELECT 'x'
FROM AHL_UE_RELATIONSHIPS
WHERE related_ue_id = p_ue_id;
SELECT NVL(net_reading, 0)
FROM CSI_CP_COUNTERS_V CO, AHL_UNIT_EFFECTIVITIES_APP_V UE
WHERE co.customer_product_id = ue.csi_item_instance_id and
ue.unit_effectivity_id = p_ue_id and
co.counter_id = p_counter_id;
SELECT NVL(net_reading, 0)
FROM csi_counter_values_v cv, csi_counter_associations cca, AHL_UNIT_EFFECTIVITIES_APP_V UE
WHERE cca.source_object_code = 'CP'
AND cca.source_object_id = ue.csi_item_instance_id
AND cca.counter_id = cv.counter_id
AND ue.unit_effectivity_id = p_ue_id
AND cv.counter_id = p_counter_id
ORDER BY cv.value_timestamp desc;
SELECT 'x'
FROM CSI_COUNTERS_VL CC, csi_counter_associations cca, AHL_UNIT_EFFECTIVITIES_APP_V UE
WHERE cca.source_object_code = 'CP'
AND cca.source_object_id = ue.csi_item_instance_id
AND cca.counter_id = cc.counter_id
AND ue.unit_effectivity_id = p_ue_id
AND cc.counter_id = p_counter_id;
SELECT NVL(CCR.net_reading, 0)
FROM
CSI_COUNTERS_VL CC,
CSI_COUNTER_READINGS CCR
WHERE
CCR.COUNTER_ID = CC.COUNTER_ID
AND CC.COUNTER_ID = p_counter_id
ORDER BY
CCR.VALUE_TIMESTAMP DESC;
SELECT OBJECT_VERSION_NUMBER, COUNTER_ID
FROM AHL_UNIT_THRESHOLDS
WHERE UNIT_THRESHOLD_ID = p_threshold_id;
SELECT 'x'
FROM AHL_UNIT_THRESHOLDS UTH, AHL_UNIT_DEFERRALS_B UDF
WHERE UTH.unit_deferral_id = UDF.unit_deferral_ID AND
UTH.UNIT_THRESHOLD_ID = p_threshold_id AND
UDF.UNIT_EFFECTIVITY_ID = p_ue_id AND
UDF.Unit_deferral_type = 'INIT-DUE';
SELECT NVL(net_reading, 0)
FROM CSI_CP_COUNTERS_V CO, AHL_UNIT_EFFECTIVITIES_APP_V UE
WHERE co.customer_product_id = ue.csi_item_instance_id and
ue.unit_effectivity_id = p_ue_id and
co.counter_id = p_counter_id;
SELECT 'x'
FROM CSI_COUNTERS_VL CC, csi_counter_associations cca, AHL_UNIT_EFFECTIVITIES_APP_V UE
WHERE cca.source_object_code = 'CP'
AND cca.source_object_id = ue.csi_item_instance_id
AND cca.counter_id = cc.counter_id
AND ue.unit_effectivity_id = p_ue_id
AND cc.counter_id = p_counter_id;
SELECT NVL(CCR.net_reading, 0)
FROM
CSI_COUNTERS_VL CC,
CSI_COUNTER_READINGS CCR
WHERE
CCR.COUNTER_ID = CC.COUNTER_ID
AND CC.COUNTER_ID = p_counter_id
AND CCR.VALUE_TIMESTAMP <= p_accomplishment_date
ORDER BY
CCR.VALUE_TIMESTAMP DESC;
SELECT NVL(CCR.net_reading, 0)
FROM
CSI_COUNTERS_VL CC,
CSI_COUNTER_READINGS CCR
WHERE
CCR.COUNTER_ID = CC.COUNTER_ID
AND CC.COUNTER_ID = p_counter_id
AND trunc(CCR.VALUE_TIMESTAMP) <= trunc(p_accomplishment_date)
ORDER BY
CCR.VALUE_TIMESTAMP DESC;
SELECT NVL(CCR.net_reading, 0)
FROM
CSI_COUNTER_READINGS CCR
WHERE
CCR.COUNTER_ID = p_counter_id
-- fix for bug# 7016783. pick next highest counter value.
AND trunc(CCR.VALUE_TIMESTAMP) > trunc(p_accomplishment_date)
ORDER BY
-- fix for bug# 7016783
CCR.VALUE_TIMESTAMP ASC;
SELECT OBJECT_VERSION_NUMBER, COUNTER_ID
FROM AHL_UNIT_ACCOMPLISHMNTS
WHERE UNIT_ACCOMPLISHMNT_ID = p_accomplishment_id;
SELECT 'x'
FROM AHL_UNIT_ACCOMPLISHMNTS
WHERE UNIT_ACCOMPLISHMNT_ID = p_accomplishment_id AND
UNIT_EFFECTIVITY_ID = p_ue_id;
PROCEDURE Update_Unit_Effectivity
(
p_unit_Effectivity_rec IN AHL_UMP_UNITMAINT_PVT.Unit_Effectivity_rec_type) IS
CURSOR l_threshold_exists_csr(p_ue_id IN NUMBER) IS
SELECT 'x'
FROM AHL_UNIT_THRESHOLDS UTH, AHL_UNIT_DEFERRALS_B UDF
WHERE UTH.unit_deferral_id = UDF.unit_deferral_id
AND UDF.unit_deferral_type = 'INIT-DUE'
AND UDF.unit_effectivity_id = p_ue_id;
SELECT 'x'
FROM AHL_UNIT_ACCOMPLISHMNTS
WHERE unit_effectivity_id = p_ue_id;
SELECT
OBJECT_VERSION_NUMBER,
CSI_ITEM_INSTANCE_ID,
MR_INTERVAL_ID,
MR_EFFECTIVITY_ID,
MR_HEADER_ID,
STATUS_CODE,
DUE_DATE,
ACCOMPLISHED_DATE,
SET_DUE_DATE,
DUE_COUNTER_VALUE,
FORECAST_SEQUENCE,
REPETITIVE_MR_FLAG,
TOLERANCE_FLAG,
DATE_RUN,
PRECEDING_UE_ID,
MESSAGE_CODE,
REMARKS,
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
FROM AHL_UNIT_EFFECTIVITIES_APP_V
WHERE unit_effectivity_id = p_ue_id
FOR UPDATE OF OBJECT_VERSION_NUMBER;
SELECT related_ue_id
FROM ahl_ue_relationships
START WITH ue_id = p_ue_id
AND relationship_code = 'PARENT'
CONNECT BY ue_id = PRIOR related_ue_id
AND relationship_code = 'PARENT';
SELECT UNIT_ACCOMPLISHMNT_ID
FROM ahl_unit_accomplishmnts
WHERE UNIT_EFFECTIVITY_ID = p_ue_id;
SELECT UNIT_THRESHOLD_ID
FROM ahl_unit_thresholds
WHERE UNIT_DEFERRAL_ID = p_ue_id;
SELECT unit_deferral_id,
ata_sequence_id,
object_version_number,
unit_deferral_type,
approval_status_code,
defer_reason_code,
skip_mr_flag,
affect_due_calc_flag,
set_due_date,
deferral_effective_on,
remarks,approver_notes,attribute_category, attribute1,
attribute2, attribute3, attribute4, attribute5, attribute6, attribute7,
attribute8, attribute9, attribute10, attribute11, attribute12,
attribute13, attribute14, attribute15
FROM ahl_unit_deferrals_vl
WHERE unit_effectivity_id = p_ue_id
AND UNIT_DEFERRAL_TYPE = 'INIT-DUE'
FOR UPDATE OF OBJECT_VERSION_NUMBER;
AHL_DEBUG_PUB.debug('In Update_Unit_Effectivity', 'UMP');
* init-accomplishment deletes as well.
-- If this an accomplishment, init-accomplishment or termination, ensure that
-- values for all counters are given by calling FMP API
IF l_ue_rec.MR_HEADER_ID IS NOT NULL THEN
IF (l_status_code = 'TERMINATED' OR l_status_code = 'MR-TERMINATE' OR
l_status_code = 'ACCOMPLISHED') OR (l_status_code = 'INIT-ACCOMPLISHED' AND l_ue_rec.status_code IS NULL) THEN
Match_Counters_with_FMP(
p_unit_effectivity_id => p_unit_Effectivity_rec.UNIT_EFFECTIVITY_ID,
p_item_instance_id => l_ue_rec.CSI_ITEM_INSTANCE_ID,
p_mr_header_id => l_ue_rec.MR_HEADER_ID,
x_counters => l_counters_msg,
x_return_status => l_return_status);
AHL_UNIT_EFFECTIVITIES_PKG.update_row(
x_unit_effectivity_id => p_unit_Effectivity_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_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 => l_ue_rec.DATE_RUN,
x_set_due_date => null,
x_accomplished_date => l_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_qa_collection_id => l_ue_rec.qa_collection_id,
x_cs_incident_id => l_ue_rec.cs_incident_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 => TRUNC(sysdate),
x_last_updated_by => fnd_global.user_id,
x_last_update_login => fnd_global.login_id);
AHL_UNIT_DEFERRALS_PKG.delete_row(x_unit_deferral_id => l_unit_def_rec.unit_deferral_id);
AHL_UNIT_DEFERRALS_PKG.update_row(
x_unit_deferral_id => l_unit_def_rec.unit_deferral_id,
x_ata_sequence_id => l_unit_def_rec.ata_sequence_id,
x_object_version_number => l_unit_def_rec.object_version_number + 1,
x_last_updated_by => fnd_global.user_id,
x_last_update_date => sysdate,
x_last_update_login => fnd_global.login_id,
x_unit_effectivity_id => p_unit_Effectivity_rec.UNIT_EFFECTIVITY_ID,
x_unit_deferral_type => l_unit_def_rec.unit_deferral_type,
x_set_due_date => l_set_due_date,
x_deferral_effective_on => l_unit_def_rec.deferral_effective_on,
x_approval_status_code => l_unit_def_rec.approval_status_code,
x_defer_reason_code => l_unit_def_rec.defer_reason_code,
x_affect_due_calc_flag => l_unit_def_rec.affect_due_calc_flag,
x_skip_mr_flag => l_unit_def_rec.skip_mr_flag,
x_remarks => l_unit_def_rec.remarks,
x_approver_notes => l_unit_def_rec.approver_notes,
x_user_deferral_type => null,
x_attribute_category => l_unit_def_rec.attribute_category,
x_attribute1 => l_unit_def_rec.attribute1,
x_attribute2 => l_unit_def_rec.attribute2,
x_attribute3 => l_unit_def_rec.attribute3,
x_attribute4 => l_unit_def_rec.attribute4,
x_attribute5 => l_unit_def_rec.attribute5,
x_attribute6 => l_unit_def_rec.attribute6,
x_attribute7 => l_unit_def_rec.attribute7,
x_attribute8 => l_unit_def_rec.attribute8,
x_attribute9 => l_unit_def_rec.attribute9,
x_attribute10 => l_unit_def_rec.attribute10,
x_attribute11 => l_unit_def_rec.attribute11,
x_attribute12 => l_unit_def_rec.attribute12,
x_attribute13 => l_unit_def_rec.attribute13,
x_attribute14 => l_unit_def_rec.attribute14,
x_attribute15 => l_unit_def_rec.attribute15
);
AHL_UNIT_DEFERRALS_PKG.insert_row(
x_rowid => l_rowid,
x_unit_deferral_id => l_unit_deferral_id,
x_ata_sequence_id => null,
x_object_version_number => 1,
x_created_by => fnd_global.user_id,
x_creation_date => sysdate,
x_last_updated_by => fnd_global.user_id,
x_last_update_date => sysdate,
x_last_update_login => fnd_global.login_id,
x_unit_effectivity_id => p_unit_Effectivity_rec.UNIT_EFFECTIVITY_ID,
x_unit_deferral_type => 'INIT-DUE',
x_set_due_date => p_unit_Effectivity_rec.set_due_date,
x_deferral_effective_on => null,
x_approval_status_code => null,
x_defer_reason_code => null,
x_affect_due_calc_flag => 'Y',
x_skip_mr_flag => null,
x_remarks => null,
x_approver_notes => null,
x_user_deferral_type => null,
x_attribute_category => null,
x_attribute1 => null,
x_attribute2 => null,
x_attribute3 => null,
x_attribute4 => null,
x_attribute5 => null,
x_attribute6 => null,
x_attribute7 => null,
x_attribute8 => null,
x_attribute9 => null,
x_attribute10 => null,
x_attribute11 => null,
x_attribute12 => null,
x_attribute13 => null,
x_attribute14 => null,
x_attribute15 => null
);
AHL_UNIT_ACCOMPLISH_PKG.delete_row(l_accomplishment_id);
AHL_UNIT_THRESHOLDS_PKG.delete_row(l_threshold_id);
AHL_UNIT_DEFERRALS_PKG.delete_row(x_unit_deferral_id => l_unit_def_rec.unit_deferral_id);
END Update_Unit_Effectivity;
SELECT
OBJECT_VERSION_NUMBER,
CSI_ITEM_INSTANCE_ID,
MR_INTERVAL_ID,
MR_EFFECTIVITY_ID,
MR_HEADER_ID,
DUE_DATE,
DUE_COUNTER_VALUE,
FORECAST_SEQUENCE,
REPETITIVE_MR_FLAG,
TOLERANCE_FLAG,
REMARKS,
MESSAGE_CODE,
PRECEDING_UE_ID,
DATE_RUN,
SET_DUE_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
FROM AHL_UNIT_EFFECTIVITIES_APP_V
WHERE unit_effectivity_id = p_ue_id
FOR UPDATE OF OBJECT_VERSION_NUMBER;
AHL_UNIT_EFFECTIVITIES_PKG.update_row(
x_unit_effectivity_id => p_descendent_ue_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 => 'TERMINATED',
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 => l_ue_rec.DATE_RUN,
x_set_due_date => l_ue_rec.SET_DUE_DATE,
x_accomplished_date => TRUNC(sysdate),
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_qa_collection_id => l_ue_rec.qa_collection_id,
x_orig_deferral_ue_id => l_ue_rec.orig_deferral_ue_id,
x_cs_incident_id => l_ue_rec.cs_incident_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 => TRUNC(sysdate),
x_last_updated_by => fnd_global.user_id,
x_last_update_login => fnd_global.login_id);
PROCEDURE Update_Thresholds(
p_unit_Effectivity_rec IN AHL_UMP_UNITMAINT_PVT.Unit_Effectivity_rec_type,
p_x_unit_threshold_tbl IN OUT NOCOPY AHL_UMP_UNITMAINT_PVT.Unit_Threshold_tbl_type) IS
CURSOR l_get_unit_threshold_csr(p_threshold_id IN NUMBER) IS
SELECT OBJECT_VERSION_NUMBER,
--UNIT_EFFECTIVITY_ID,
UNIT_DEFERRAL_ID,
COUNTER_ID,
CTR_VALUE_TYPE_CODE,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15
FROM AHL_UNIT_THRESHOLDS
WHERE UNIT_THRESHOLD_ID = p_threshold_id
FOR UPDATE OF OBJECT_VERSION_NUMBER;
SELECT 'x'
FROM AHL_UNIT_THRESHOLDS UTH
WHERE UTH.unit_deferral_id = p_unit_def_id AND
UTH.COUNTER_ID = p_counter_id;
SELECT unit_deferral_id
FROM AHL_UNIT_DEFERRALS_B UDF
WHERE UDF.UNIT_EFFECTIVITY_ID = p_ue_id AND
UDF.UNIT_DEFERRAL_TYPE = 'INIT-DUE';
AHL_UNIT_THRESHOLDS_PKG.delete_row(p_x_unit_threshold_tbl(i).UNIT_THRESHOLD_ID);
AHL_UNIT_THRESHOLDS_PKG.update_row (
P_UNIT_THRESHOLD_ID => p_x_unit_threshold_tbl(i).UNIT_THRESHOLD_ID,
--P_UNIT_EFFECTIVITY_ID => l_threshold_details.UNIT_EFFECTIVITY_ID,
P_UNIT_DEFERRAL_ID => l_threshold_details.UNIT_DEFERRAL_ID,
P_CTR_VALUE_TYPE_CODE => l_threshold_details.CTR_VALUE_TYPE_CODE,
P_COUNTER_ID => l_threshold_details.COUNTER_ID,
P_COUNTER_VALUE => p_x_unit_threshold_tbl(i).COUNTER_VALUE,
P_ATTRIBUTE_CATEGORY => p_x_unit_threshold_tbl(i).ATTRIBUTE_CATEGORY,
P_ATTRIBUTE1 => p_x_unit_threshold_tbl(i).ATTRIBUTE1,
P_ATTRIBUTE2 => p_x_unit_threshold_tbl(i).ATTRIBUTE2,
P_ATTRIBUTE3 => p_x_unit_threshold_tbl(i).ATTRIBUTE3,
P_ATTRIBUTE4 => p_x_unit_threshold_tbl(i).ATTRIBUTE4,
P_ATTRIBUTE5 => p_x_unit_threshold_tbl(i).ATTRIBUTE5,
P_ATTRIBUTE6 => p_x_unit_threshold_tbl(i).ATTRIBUTE6,
P_ATTRIBUTE7 => p_x_unit_threshold_tbl(i).ATTRIBUTE7,
P_ATTRIBUTE8 => p_x_unit_threshold_tbl(i).ATTRIBUTE8,
P_ATTRIBUTE9 => p_x_unit_threshold_tbl(i).ATTRIBUTE9,
P_ATTRIBUTE10 => p_x_unit_threshold_tbl(i).ATTRIBUTE10,
P_ATTRIBUTE11 => p_x_unit_threshold_tbl(i).ATTRIBUTE11,
P_ATTRIBUTE12 => p_x_unit_threshold_tbl(i).ATTRIBUTE12,
P_ATTRIBUTE13 => p_x_unit_threshold_tbl(i).ATTRIBUTE13,
P_ATTRIBUTE14 => p_x_unit_threshold_tbl(i).ATTRIBUTE14,
P_ATTRIBUTE15 => p_x_unit_threshold_tbl(i).ATTRIBUTE15,
P_OBJECT_VERSION_NUMBER => p_x_unit_threshold_tbl(i).OBJECT_VERSION_NUMBER,
P_LAST_UPDATE_DATE => TRUNC(sysdate),
P_LAST_UPDATED_BY => fnd_global.user_id,
P_LAST_UPDATE_LOGIN => fnd_global.login_id);
AHL_UNIT_DEFERRALS_PKG.insert_row(
x_rowid => l_rowid,
x_unit_deferral_id => l_unit_deferral_id,
x_ata_sequence_id => null,
x_object_version_number => 1,
x_created_by => fnd_global.user_id,
x_creation_date => sysdate,
x_last_updated_by => fnd_global.user_id,
x_last_update_date => sysdate,
x_last_update_login => fnd_global.login_id,
x_unit_effectivity_id => p_x_unit_threshold_tbl(i).unit_effectivity_id,
x_unit_deferral_type => 'INIT-DUE',
x_set_due_date => p_unit_Effectivity_rec.set_due_date,
x_deferral_effective_on => null,
x_approval_status_code => null,
x_defer_reason_code => null,
x_affect_due_calc_flag => 'Y',
x_skip_mr_flag => null,
x_remarks => null,
x_approver_notes => null,
x_user_deferral_type => null,
x_attribute_category => null,
x_attribute1 => null,
x_attribute2 => null,
x_attribute3 => null,
x_attribute4 => null,
x_attribute5 => null,
x_attribute6 => null,
x_attribute7 => null,
x_attribute8 => null,
x_attribute9 => null,
x_attribute10 => null,
x_attribute11 => null,
x_attribute12 => null,
x_attribute13 => null,
x_attribute14 => null,
x_attribute15 => null
);
AHL_UNIT_THRESHOLDS_PKG.insert_row (
P_X_UNIT_THRESHOLD_ID => p_x_unit_threshold_tbl(i).UNIT_THRESHOLD_ID,
--P_UNIT_EFFECTIVITY_ID => p_x_unit_threshold_tbl(i).UNIT_EFFECTIVITY_ID,
P_UNIT_DEFERRAL_ID => l_UNIT_DEFERRAL_ID,
P_COUNTER_ID => p_x_unit_threshold_tbl(i).COUNTER_ID,
P_COUNTER_VALUE => p_x_unit_threshold_tbl(i).COUNTER_VALUE,
P_CTR_VALUE_TYPE_CODE => 'DEFER_TO',
P_ATTRIBUTE_CATEGORY => p_x_unit_threshold_tbl(i).ATTRIBUTE_CATEGORY,
P_ATTRIBUTE1 => p_x_unit_threshold_tbl(i).ATTRIBUTE1,
P_ATTRIBUTE2 => p_x_unit_threshold_tbl(i).ATTRIBUTE2,
P_ATTRIBUTE3 => p_x_unit_threshold_tbl(i).ATTRIBUTE3,
P_ATTRIBUTE4 => p_x_unit_threshold_tbl(i).ATTRIBUTE4,
P_ATTRIBUTE5 => p_x_unit_threshold_tbl(i).ATTRIBUTE5,
P_ATTRIBUTE6 => p_x_unit_threshold_tbl(i).ATTRIBUTE6,
P_ATTRIBUTE7 => p_x_unit_threshold_tbl(i).ATTRIBUTE7,
P_ATTRIBUTE8 => p_x_unit_threshold_tbl(i).ATTRIBUTE8,
P_ATTRIBUTE9 => p_x_unit_threshold_tbl(i).ATTRIBUTE9,
P_ATTRIBUTE10 => p_x_unit_threshold_tbl(i).ATTRIBUTE10,
P_ATTRIBUTE11 => p_x_unit_threshold_tbl(i).ATTRIBUTE11,
P_ATTRIBUTE12 => p_x_unit_threshold_tbl(i).ATTRIBUTE12,
P_ATTRIBUTE13 => p_x_unit_threshold_tbl(i).ATTRIBUTE13,
P_ATTRIBUTE14 => p_x_unit_threshold_tbl(i).ATTRIBUTE14,
P_ATTRIBUTE15 => p_x_unit_threshold_tbl(i).ATTRIBUTE15,
P_OBJECT_VERSION_NUMBER => p_x_unit_threshold_tbl(i).OBJECT_VERSION_NUMBER,
P_LAST_UPDATE_DATE => TRUNC(sysdate),
P_LAST_UPDATED_BY => fnd_global.user_id,
P_CREATION_DATE => TRUNC(sysdate),
P_CREATED_BY => fnd_global.user_id,
P_LAST_UPDATE_LOGIN => fnd_global.login_id);
END Update_Thresholds;
PROCEDURE Update_Accomplishments(
p_x_unit_accomplish_tbl IN OUT NOCOPY AHL_UMP_UNITMAINT_PVT.Unit_Accomplish_tbl_type,
p_unit_Effectivity_rec IN AHL_UMP_UNITMAINT_PVT.Unit_Effectivity_rec_type) IS
CURSOR l_get_unit_accomplish_csr(p_accomplish_id IN NUMBER) IS
SELECT OBJECT_VERSION_NUMBER,
UNIT_EFFECTIVITY_ID,
COUNTER_ID,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15
FROM AHL_UNIT_ACCOMPLISHMNTS
WHERE UNIT_ACCOMPLISHMNT_ID = p_accomplish_id
FOR UPDATE OF OBJECT_VERSION_NUMBER;
SELECT 'x'
FROM AHL_UNIT_ACCOMPLISHMNTS
WHERE UNIT_EFFECTIVITY_ID = p_ue_id AND
COUNTER_ID = p_counter_id;
AHL_UNIT_ACCOMPLISH_PKG.delete_row(p_x_unit_accomplish_tbl(i).UNIT_ACCOMPLISH_ID);
AHL_UNIT_ACCOMPLISH_PKG.update_row (
P_UNIT_ACCOMPLISHMNT_ID => p_x_unit_accomplish_tbl(i).UNIT_ACCOMPLISH_ID,
P_UNIT_EFFECTIVITY_ID => p_x_unit_accomplish_tbl(i).UNIT_EFFECTIVITY_ID,
P_COUNTER_ID => p_x_unit_accomplish_tbl(i).COUNTER_ID,
P_COUNTER_VALUE => p_x_unit_accomplish_tbl(i).COUNTER_VALUE,
P_ATTRIBUTE_CATEGORY => p_x_unit_accomplish_tbl(i).ATTRIBUTE_CATEGORY,
P_ATTRIBUTE1 => p_x_unit_accomplish_tbl(i).ATTRIBUTE1,
P_ATTRIBUTE2 => p_x_unit_accomplish_tbl(i).ATTRIBUTE2,
P_ATTRIBUTE3 => p_x_unit_accomplish_tbl(i).ATTRIBUTE3,
P_ATTRIBUTE4 => p_x_unit_accomplish_tbl(i).ATTRIBUTE4,
P_ATTRIBUTE5 => p_x_unit_accomplish_tbl(i).ATTRIBUTE5,
P_ATTRIBUTE6 => p_x_unit_accomplish_tbl(i).ATTRIBUTE6,
P_ATTRIBUTE7 => p_x_unit_accomplish_tbl(i).ATTRIBUTE7,
P_ATTRIBUTE8 => p_x_unit_accomplish_tbl(i).ATTRIBUTE8,
P_ATTRIBUTE9 => p_x_unit_accomplish_tbl(i).ATTRIBUTE9,
P_ATTRIBUTE10 => p_x_unit_accomplish_tbl(i).ATTRIBUTE10,
P_ATTRIBUTE11 => p_x_unit_accomplish_tbl(i).ATTRIBUTE11,
P_ATTRIBUTE12 => p_x_unit_accomplish_tbl(i).ATTRIBUTE12,
P_ATTRIBUTE13 => p_x_unit_accomplish_tbl(i).ATTRIBUTE13,
P_ATTRIBUTE14 => p_x_unit_accomplish_tbl(i).ATTRIBUTE14,
P_ATTRIBUTE15 => p_x_unit_accomplish_tbl(i).ATTRIBUTE15,
P_OBJECT_VERSION_NUMBER => p_x_unit_accomplish_tbl(i).OBJECT_VERSION_NUMBER,
P_LAST_UPDATE_DATE => TRUNC(sysdate),
P_LAST_UPDATED_BY => fnd_global.user_id,
P_LAST_UPDATE_LOGIN => fnd_global.login_id);
AHL_UNIT_ACCOMPLISH_PKG.insert_row (
P_X_UNIT_ACCOMPLISHMNT_ID => p_x_unit_accomplish_tbl(i).UNIT_ACCOMPLISH_ID,
P_UNIT_EFFECTIVITY_ID => p_x_unit_accomplish_tbl(i).UNIT_EFFECTIVITY_ID,
P_COUNTER_ID => p_x_unit_accomplish_tbl(i).COUNTER_ID,
P_COUNTER_VALUE => p_x_unit_accomplish_tbl(i).COUNTER_VALUE,
P_ATTRIBUTE_CATEGORY => p_x_unit_accomplish_tbl(i).ATTRIBUTE_CATEGORY,
P_ATTRIBUTE1 => p_x_unit_accomplish_tbl(i).ATTRIBUTE1,
P_ATTRIBUTE2 => p_x_unit_accomplish_tbl(i).ATTRIBUTE2,
P_ATTRIBUTE3 => p_x_unit_accomplish_tbl(i).ATTRIBUTE3,
P_ATTRIBUTE4 => p_x_unit_accomplish_tbl(i).ATTRIBUTE4,
P_ATTRIBUTE5 => p_x_unit_accomplish_tbl(i).ATTRIBUTE5,
P_ATTRIBUTE6 => p_x_unit_accomplish_tbl(i).ATTRIBUTE6,
P_ATTRIBUTE7 => p_x_unit_accomplish_tbl(i).ATTRIBUTE7,
P_ATTRIBUTE8 => p_x_unit_accomplish_tbl(i).ATTRIBUTE8,
P_ATTRIBUTE9 => p_x_unit_accomplish_tbl(i).ATTRIBUTE9,
P_ATTRIBUTE10 => p_x_unit_accomplish_tbl(i).ATTRIBUTE10,
P_ATTRIBUTE11 => p_x_unit_accomplish_tbl(i).ATTRIBUTE11,
P_ATTRIBUTE12 => p_x_unit_accomplish_tbl(i).ATTRIBUTE12,
P_ATTRIBUTE13 => p_x_unit_accomplish_tbl(i).ATTRIBUTE13,
P_ATTRIBUTE14 => p_x_unit_accomplish_tbl(i).ATTRIBUTE14,
P_ATTRIBUTE15 => p_x_unit_accomplish_tbl(i).ATTRIBUTE15,
P_OBJECT_VERSION_NUMBER => p_x_unit_accomplish_tbl(i).OBJECT_VERSION_NUMBER,
P_LAST_UPDATE_DATE => TRUNC(sysdate),
P_LAST_UPDATED_BY => fnd_global.user_id,
P_CREATION_DATE => TRUNC(sysdate),
P_CREATED_BY => fnd_global.user_id,
P_LAST_UPDATE_LOGIN => fnd_global.login_id);
END Update_Accomplishments;
select distinct name counter_name from csi_counter_template_vl co, ahl_mr_intervals mr
where co.counter_id = mr.counter_id
and mr.mr_effectivity_id = p_mr_effectivity_id
intersect
/* Fix for bug# 6956784. Replace CS_COUNTERS with CSI_COUNTERS_VL.
* Use COUNTER_TEMPLATE_NAME for counter name
* Uptake R12 Counters Changes.
SELECT c.name
FROM CS_COUNTERS C, CS_COUNTER_GROUPS CTRGRP, CSI_ITEM_INSTANCES CII--, MTL_SYSTEM_ITEMS_KFV MSITEM
WHERE C.COUNTER_GROUP_ID = CTRGRP.COUNTER_GROUP_ID
AND CTRGRP.SOURCE_OBJECT_CODE = 'CP'
AND CTRGRP.SOURCE_OBJECT_ID = CII.INSTANCE_ID
--AND MSITEM.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
--AND MSITEM.ORGANIZATION_ID = CII.INV_MASTER_ORGANIZATION_ID
AND CII.INSTANCE_ID = p_instance_id
*/
SELECT cc.COUNTER_TEMPLATE_NAME counter_name
FROM csi_counters_vl cc,
csi_counter_associations cca --, CSI_ITEM_INSTANCES CII
WHERE cc.counter_id = cca.counter_id
and cca.source_object_code = 'CP'
and cca.source_object_id = p_instance_id
MINUS
--SELECT c.name counter_name
SELECt c.COUNTER_TEMPLATE_NAME counter_name
--FROM CS_COUNTERS C, ahl_unit_accomplishmnts ua
FROM csi_counters_vl C, ahl_unit_accomplishmnts ua
WHERE c.COUNTER_ID = UA.COUNTER_ID
AND UA.unit_effectivity_id = p_unit_effectivity_id;
l_fmp_sql := ' select distinct name counter_name from csi_counter_template_vl co, ahl_mr_intervals mr';
l_inst_sql := ' select distinct counter_template_name counter_name from csi_counters_vl cc, csi_counter_associations cca ';
l_acc_sql := ' select distinct counter_template_name counter_name from csi_counters_vl cc, ahl_unit_accomplishmnts ua';
SELECT MR_HEADER_ID FROM AHL_MR_HEADERS_B
WHERE TITLE = p_mr_title AND VERSION_NUMBER = p_version_number;
SELECT EFFECTIVE_TO, application_usg_code FROM AHL_MR_HEADERS_VL WHERE MR_HEADER_ID = p_mr_header_id;
SELECT EFFECTIVE_FROM FROM AHL_MR_HEADERS_VL WHERE MR_HEADER_ID = p_mr_header_id;
CURSOR l_unit_effectivity_csr(p_header_id IN NUMBER) IS SELECT
EFF.UNIT_EFFECTIVITY_ID,
EFF.CSI_ITEM_INSTANCE_ID,
EFF.MR_INTERVAL_ID ,
EFF.MR_EFFECTIVITY_ID ,
EFF.MR_HEADER_ID ,
EFF.STATUS_CODE ,
EFF.DUE_DATE ,
EFF.DUE_COUNTER_VALUE ,
EFF.FORECAST_SEQUENCE ,
EFF.REPETITIVE_MR_FLAG,
EFF.TOLERANCE_FLAG ,
EFF.REMARKS ,
EFF.MESSAGE_CODE ,
EFF.PRECEDING_UE_ID ,
EFF.DATE_RUN,
EFF.SET_DUE_DATE,
EFF.ACCOMPLISHED_DATE,
EFF.SERVICE_LINE_ID,
EFF.PROGRAM_MR_HEADER_ID,
EFF.CANCEL_REASON_CODE,
EFF.EARLIEST_DUE_DATE,
EFF.LATEST_DUE_DATE,
EFF.DEFER_FROM_UE_ID,
EFF.CS_INCIDENT_ID,
EFF.QA_COLLECTION_ID,
EFF.ORIG_DEFERRAL_UE_ID,
EFF.APPLICATION_USG_CODE,
EFF.OBJECT_TYPE,
EFF.COUNTER_ID,
EFF.MANUALLY_PLANNED_FLAG,
EFF.LOG_SERIES_CODE,
EFF.LOG_SERIES_NUMBER,
EFF.FLIGHT_NUMBER,
EFF.MEL_CDL_TYPE_CODE,
EFF.POSITION_PATH_ID,
EFF.ATA_CODE,
EFF.UNIT_CONFIG_HEADER_ID,
EFF.ATTRIBUTE_CATEGORY,
EFF.ATTRIBUTE1,
EFF.ATTRIBUTE2,
EFF.ATTRIBUTE3,
EFF.ATTRIBUTE4,
EFF.ATTRIBUTE5,
EFF.ATTRIBUTE6,
EFF.ATTRIBUTE7,
EFF.ATTRIBUTE8,
EFF.ATTRIBUTE9,
EFF.ATTRIBUTE10,
EFF.ATTRIBUTE11,
EFF.ATTRIBUTE12,
EFF.ATTRIBUTE13,
EFF.ATTRIBUTE14,
EFF.ATTRIBUTE15,
EFF.OBJECT_VERSION_NUMBER,
EFF.LAST_UPDATE_DATE,
EFF.LAST_UPDATED_BY ,
EFF.LAST_UPDATE_LOGIN
FROM AHL_UNIT_EFFECTIVITIES_APP_V EFF
WHERE EFF.MR_HEADER_ID = p_header_id
AND object_type = 'MR'
-- AND (EFF.status_code is null or EFF.status_code in ('INIT-DUE','DEFERRED'))
AND (EFF.status_code is null or EFF.status_code = 'INIT-DUE')
AND not exists (select 'x' from ahl_ue_relationships where
related_ue_id = EFF.unit_effectivity_id)
FOR UPDATE;
CURSOR l_ue_descendent_csr(p_effectivity_id IN NUMBER) IS SELECT
EFF.UNIT_EFFECTIVITY_ID,
EFF.CSI_ITEM_INSTANCE_ID,
EFF.MR_INTERVAL_ID ,
EFF.MR_EFFECTIVITY_ID ,
EFF.MR_HEADER_ID ,
EFF.STATUS_CODE ,
EFF.DUE_DATE ,
EFF.DUE_COUNTER_VALUE ,
EFF.FORECAST_SEQUENCE ,
EFF.REPETITIVE_MR_FLAG,
EFF.TOLERANCE_FLAG ,
EFF.REMARKS ,
EFF.MESSAGE_CODE ,
EFF.PRECEDING_UE_ID ,
EFF.DATE_RUN,
EFF.SET_DUE_DATE,
EFF.ACCOMPLISHED_DATE,
EFF.SERVICE_LINE_ID,
EFF.PROGRAM_MR_HEADER_ID,
EFF.CANCEL_REASON_CODE,
EFF.EARLIEST_DUE_DATE,
EFF.LATEST_DUE_DATE,
EFF.DEFER_FROM_UE_ID,
EFF.CS_INCIDENT_ID,
EFF.QA_COLLECTION_ID,
EFF.ORIG_DEFERRAL_UE_ID,
EFF.APPLICATION_USG_CODE,
EFF.OBJECT_TYPE,
EFF.COUNTER_ID,
EFF.MANUALLY_PLANNED_FLAG,
EFF.LOG_SERIES_CODE,
EFF.LOG_SERIES_NUMBER,
EFF.FLIGHT_NUMBER,
EFF.MEL_CDL_TYPE_CODE,
EFF.POSITION_PATH_ID,
EFF.ATA_CODE,
EFF.UNIT_CONFIG_HEADER_ID,
EFF.ATTRIBUTE_CATEGORY,
EFF.ATTRIBUTE1,
EFF.ATTRIBUTE2,
EFF.ATTRIBUTE3,
EFF.ATTRIBUTE4,
EFF.ATTRIBUTE5,
EFF.ATTRIBUTE6,
EFF.ATTRIBUTE7,
EFF.ATTRIBUTE8,
EFF.ATTRIBUTE9,
EFF.ATTRIBUTE10,
EFF.ATTRIBUTE11,
EFF.ATTRIBUTE12,
EFF.ATTRIBUTE13,
EFF.ATTRIBUTE14,
EFF.ATTRIBUTE15,
EFF.OBJECT_VERSION_NUMBER,
EFF.LAST_UPDATE_DATE,
EFF.LAST_UPDATED_BY,
EFF.LAST_UPDATE_LOGIN
FROM AHL_UNIT_EFFECTIVITIES_APP_V EFF
WHERE EFF.UNIT_EFFECTIVITY_ID IN
(SELECT REL.RELATED_UE_ID FROM AHL_UE_RELATIONSHIPS REL
WHERE REL.ORIGINATOR_UE_ID = p_effectivity_id)
FOR UPDATE;
SELECT UE1.unit_effectivity_id
FROM ahl_unit_effectivities_app_v UE,
ahl_ue_relationships UER, ahl_unit_effectivities_b UE1
WHERE UER.ue_id = UE.unit_effectivity_id
AND UER.related_ue_id = UE1.unit_effectivity_id
AND UE.object_type = 'SR'
AND (UE.status_code is null or UE.status_code = 'INIT-DUE')
AND UE1.mr_header_id = p_mr_header_id;*/-- commented for performance tuning
SELECT 'x'
FROM ahl_unit_effectivities_app_v UE1
WHERE UE1.unit_effectivity_id = p_unit_effectivity_id
AND UE1.mr_header_id = p_mr_header_id;
SELECT UER.related_ue_id
FROM ahl_unit_effectivities_app_v UE, ahl_ue_relationships UER
WHERE UER.ue_id = UE.unit_effectivity_id
AND UER.relationship_code = 'PARENT'
AND UE.object_type = 'SR'
AND (UE.status_code is null or UE.status_code = 'INIT-DUE');
SELECT related_ue_id
FROM ahl_ue_relationships
START WITH ue_id = p_ue_id
AND relationship_code = 'PARENT'
CONNECT BY ue_id = PRIOR related_ue_id
AND relationship_code = 'PARENT';
SELECT COPY_INIT_ACCOMPL_FLAG, COPY_DEFERRALS_FLAG FROM AHL_MR_HEADERS_APP_V
WHERE MR_HEADER_ID = p_mr_header_id;
CURSOR l_cp_ue_csr(p_header_id IN NUMBER,p_csi_item_instance_id IN NUMBER) IS SELECT
EFF.UNIT_EFFECTIVITY_ID,
EFF.CSI_ITEM_INSTANCE_ID,
EFF.MR_INTERVAL_ID ,
EFF.MR_EFFECTIVITY_ID ,
EFF.MR_HEADER_ID ,
EFF.STATUS_CODE ,
EFF.DUE_DATE ,
EFF.DUE_COUNTER_VALUE ,
EFF.FORECAST_SEQUENCE ,
EFF.REPETITIVE_MR_FLAG,
EFF.TOLERANCE_FLAG ,
EFF.REMARKS ,
EFF.MESSAGE_CODE ,
EFF.PRECEDING_UE_ID ,
EFF.DATE_RUN,
EFF.SET_DUE_DATE,
EFF.ACCOMPLISHED_DATE,
EFF.SERVICE_LINE_ID,
EFF.PROGRAM_MR_HEADER_ID,
EFF.CANCEL_REASON_CODE,
EFF.EARLIEST_DUE_DATE,
EFF.LATEST_DUE_DATE,
EFF.DEFER_FROM_UE_ID,
EFF.CS_INCIDENT_ID,
EFF.QA_COLLECTION_ID,
EFF.ORIG_DEFERRAL_UE_ID,
EFF.APPLICATION_USG_CODE,
EFF.OBJECT_TYPE,
EFF.COUNTER_ID,
EFF.MANUALLY_PLANNED_FLAG,
EFF.LOG_SERIES_CODE,
EFF.LOG_SERIES_NUMBER,
EFF.FLIGHT_NUMBER,
EFF.MEL_CDL_TYPE_CODE,
EFF.POSITION_PATH_ID,
EFF.ATA_CODE,
EFF.UNIT_CONFIG_HEADER_ID,
EFF.ATTRIBUTE_CATEGORY,
EFF.ATTRIBUTE1,
EFF.ATTRIBUTE2,
EFF.ATTRIBUTE3,
EFF.ATTRIBUTE4,
EFF.ATTRIBUTE5,
EFF.ATTRIBUTE6,
EFF.ATTRIBUTE7,
EFF.ATTRIBUTE8,
EFF.ATTRIBUTE9,
EFF.ATTRIBUTE10,
EFF.ATTRIBUTE11,
EFF.ATTRIBUTE12,
EFF.ATTRIBUTE13,
EFF.ATTRIBUTE14,
EFF.ATTRIBUTE15,
EFF.OBJECT_VERSION_NUMBER,
EFF.LAST_UPDATE_DATE,
EFF.LAST_UPDATED_BY ,
EFF.LAST_UPDATE_LOGIN
FROM AHL_UNIT_EFFECTIVITIES_APP_V EFF
WHERE EFF.MR_HEADER_ID = p_header_id
AND EFF.CSI_ITEM_INSTANCE_ID = p_csi_item_instance_id
AND object_type = 'MR'
-- AND (EFF.status_code is null or EFF.status_code in ('INIT-DUE','DEFERRED'))
AND (EFF.status_code = 'MR-TERMINATE')
AND not exists (select 'x' from ahl_ue_relationships where
related_ue_id = EFF.unit_effectivity_id);
SELECT unit_deferral_id from ahl_unit_deferrals_b
where UNIT_EFFECTIVITY_ID = p_unit_effectivity_id
AND UNIT_DEFERRAL_TYPE = 'INIT-DUE';
AHL_DEBUG_PUB.debug('before update record status to MR-TERMINATE');
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_ue_rec.PRECEDING_UE_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_qa_collection_id => l_ue_rec.qa_collection_id,
x_orig_deferral_ue_id => l_ue_rec.orig_deferral_ue_id,
x_cs_incident_id => l_ue_rec.cs_incident_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);
AHL_DEBUG_PUB.debug('After update record');
AHL_UNIT_EFFECTIVITIES_PKG.UPDATE_ROW (
x_unit_effectivity_id => l_ue_descendent_rec.UNIT_EFFECTIVITY_ID,
x_csi_item_instance_id => l_ue_descendent_rec.CSI_ITEM_INSTANCE_ID,
x_mr_interval_id => l_ue_descendent_rec.MR_INTERVAL_ID ,
x_MR_EFFECTIVITY_ID => l_ue_descendent_rec.MR_EFFECTIVITY_ID ,
x_MR_HEADER_ID => l_ue_descendent_rec.MR_HEADER_ID ,
x_STATUS_CODE => l_ue_descendent_rec.STATUS_CODE ,
x_DUE_DATE => l_ue_descendent_rec.DUE_DATE ,
x_DUE_COUNTER_VALUE => l_ue_descendent_rec.DUE_COUNTER_VALUE ,
x_FORECAST_SEQUENCE => l_ue_descendent_rec.FORECAST_SEQUENCE ,
x_REPETITIVE_MR_FLAG => l_ue_descendent_rec.REPETITIVE_MR_FLAG,
x_TOLERANCE_FLAG => l_ue_descendent_rec.TOLERANCE_FLAG ,
x_REMARKS => l_ue_descendent_rec.REMARKS ,
x_MESSAGE_CODE => l_ue_descendent_rec.MESSAGE_CODE ,
x_PRECEDING_UE_ID => l_ue_descendent_rec.PRECEDING_UE_ID ,
x_DATE_RUN => l_ue_descendent_rec.DATE_RUN,
x_SET_DUE_DATE => l_ue_descendent_rec.SET_DUE_DATE,
x_ACCOMPLISHED_DATE => l_ue_descendent_rec.ACCOMPLISHED_DATE,
x_SERVICE_LINE_ID => l_ue_descendent_rec.SERVICE_LINE_ID,
x_PROGRAM_MR_HEADER_ID => l_ue_descendent_rec.PROGRAM_MR_HEADER_ID,
x_CANCEL_REASON_CODE => l_ue_descendent_rec.CANCEL_REASON_CODE,
x_EARLIEST_DUE_DATE => l_ue_descendent_rec.EARLIEST_DUE_DATE,
x_LATEST_DUE_DATE => l_ue_descendent_rec.LATEST_DUE_DATE,
x_defer_from_ue_id => l_ue_descendent_rec.defer_from_ue_id,
x_qa_collection_id => l_ue_descendent_rec.qa_collection_id,
x_orig_deferral_ue_id => l_ue_descendent_rec.orig_deferral_ue_id,
x_cs_incident_id => l_ue_descendent_rec.cs_incident_id,
x_application_usg_code => l_ue_descendent_rec.application_usg_code,
x_object_type => l_ue_descendent_rec.object_type,
x_counter_id => l_ue_descendent_rec.counter_id,
x_manually_planned_flag => l_ue_descendent_rec.manually_planned_flag,
X_LOG_SERIES_CODE => l_ue_descendent_rec.log_series_code,
X_LOG_SERIES_NUMBER => l_ue_descendent_rec.log_series_number,
X_FLIGHT_NUMBER => l_ue_descendent_rec.flight_number,
X_MEL_CDL_TYPE_CODE => l_ue_descendent_rec.mel_cdl_type_code,
X_POSITION_PATH_ID => l_ue_descendent_rec.position_path_id,
X_ATA_CODE => l_ue_descendent_rec.ATA_CODE,
X_UNIT_CONFIG_HEADER_ID => l_ue_descendent_rec.unit_config_header_id,
x_ATTRIBUTE_CATEGORY => l_ue_descendent_rec.ATTRIBUTE_CATEGORY,
x_ATTRIBUTE1 => l_ue_descendent_rec.ATTRIBUTE1,
x_ATTRIBUTE2 => l_ue_descendent_rec.ATTRIBUTE2,
x_ATTRIBUTE3 => l_ue_descendent_rec.ATTRIBUTE3,
x_ATTRIBUTE4 => l_ue_descendent_rec.ATTRIBUTE4,
x_ATTRIBUTE5 => l_ue_descendent_rec.ATTRIBUTE5,
x_ATTRIBUTE6 => l_ue_descendent_rec.ATTRIBUTE6,
x_ATTRIBUTE7 => l_ue_descendent_rec.ATTRIBUTE7,
x_ATTRIBUTE8 => l_ue_descendent_rec.ATTRIBUTE8,
x_ATTRIBUTE9 => l_ue_descendent_rec.ATTRIBUTE9,
x_ATTRIBUTE10 => l_ue_descendent_rec.ATTRIBUTE10,
x_ATTRIBUTE11 => l_ue_descendent_rec.ATTRIBUTE11,
x_ATTRIBUTE12 => l_ue_descendent_rec.ATTRIBUTE12,
x_ATTRIBUTE13 => l_ue_descendent_rec.ATTRIBUTE13,
x_ATTRIBUTE14 => l_ue_descendent_rec.ATTRIBUTE14,
x_ATTRIBUTE15 => l_ue_descendent_rec.ATTRIBUTE15,
x_OBJECT_VERSION_NUMBER => l_ue_descendent_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);
AHL_DEBUG_PUB.debug('Before update record status to EXCEPTION');
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_ue_rec.PRECEDING_UE_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_qa_collection_id => l_ue_rec.qa_collection_id,
x_orig_deferral_ue_id => l_ue_rec.orig_deferral_ue_id,
x_cs_incident_id => l_ue_rec.cs_incident_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, --l_ue_rec.LAST_UPDATE_DATE,
x_LAST_UPDATED_BY => fnd_global.user_id, --l_ue_rec.LAST_UPDATED_BY,
x_LAST_UPDATE_LOGIN => fnd_global.login_id); -- l_ue_rec.LAST_UPDATE_LOGIN );
AHL_DEBUG_PUB.debug('After update record');
AHL_DEBUG_PUB.debug('start update status to Exception descedant ue_id' || l_ue_descendent_rec.UNIT_EFFECTIVITY_ID);
AHL_UNIT_EFFECTIVITIES_PKG.UPDATE_ROW (
x_unit_effectivity_id => l_ue_descendent_rec.UNIT_EFFECTIVITY_ID,
x_csi_item_instance_id => l_ue_descendent_rec.CSI_ITEM_INSTANCE_ID,
x_mr_interval_id => l_ue_descendent_rec.MR_INTERVAL_ID ,
x_MR_EFFECTIVITY_ID => l_ue_descendent_rec.MR_EFFECTIVITY_ID ,
x_MR_HEADER_ID => l_ue_descendent_rec.MR_HEADER_ID ,
x_STATUS_CODE => l_ue_descendent_rec.STATUS_CODE ,
x_DUE_DATE => l_ue_descendent_rec.DUE_DATE ,
x_DUE_COUNTER_VALUE => l_ue_descendent_rec.DUE_COUNTER_VALUE ,
x_FORECAST_SEQUENCE => l_ue_descendent_rec.FORECAST_SEQUENCE ,
x_REPETITIVE_MR_FLAG => l_ue_descendent_rec.REPETITIVE_MR_FLAG,
x_TOLERANCE_FLAG => l_ue_descendent_rec.TOLERANCE_FLAG ,
x_REMARKS => l_ue_descendent_rec.REMARKS ,
x_MESSAGE_CODE => l_ue_descendent_rec.MESSAGE_CODE ,
x_PRECEDING_UE_ID => l_ue_descendent_rec.PRECEDING_UE_ID ,
x_DATE_RUN => l_ue_descendent_rec.DATE_RUN,
x_SET_DUE_DATE => l_ue_descendent_rec.SET_DUE_DATE,
x_ACCOMPLISHED_DATE => l_ue_descendent_rec.ACCOMPLISHED_DATE,
x_SERVICE_LINE_ID => l_ue_descendent_rec.SERVICE_LINE_ID,
x_PROGRAM_MR_HEADER_ID => l_ue_descendent_rec.PROGRAM_MR_HEADER_ID,
x_CANCEL_REASON_CODE => l_ue_descendent_rec.CANCEL_REASON_CODE,
x_EARLIEST_DUE_DATE => l_ue_descendent_rec.EARLIEST_DUE_DATE,
x_LATEST_DUE_DATE => l_ue_descendent_rec.LATEST_DUE_DATE,
x_defer_from_ue_id => l_ue_descendent_rec.defer_from_ue_id,
x_qa_collection_id => l_ue_descendent_rec.qa_collection_id,
x_orig_deferral_ue_id => l_ue_descendent_rec.orig_deferral_ue_id,
x_cs_incident_id => l_ue_descendent_rec.cs_incident_id,
x_application_usg_code => l_ue_descendent_rec.application_usg_code,
x_object_type => l_ue_descendent_rec.object_type,
x_counter_id => l_ue_descendent_rec.counter_id,
x_manually_planned_flag => l_ue_descendent_rec.manually_planned_flag,
X_LOG_SERIES_CODE => l_ue_descendent_rec.log_series_code,
X_LOG_SERIES_NUMBER => l_ue_descendent_rec.log_series_number,
X_FLIGHT_NUMBER => l_ue_descendent_rec.flight_number,
X_MEL_CDL_TYPE_CODE => l_ue_descendent_rec.mel_cdl_type_code,
X_POSITION_PATH_ID => l_ue_descendent_rec.position_path_id,
X_ATA_CODE => l_ue_descendent_rec.ATA_CODE,
X_UNIT_CONFIG_HEADER_ID => l_ue_descendent_rec.unit_config_header_id,
x_ATTRIBUTE_CATEGORY => l_ue_descendent_rec.ATTRIBUTE_CATEGORY,
x_ATTRIBUTE1 => l_ue_descendent_rec.ATTRIBUTE1,
x_ATTRIBUTE2 => l_ue_descendent_rec.ATTRIBUTE2,
x_ATTRIBUTE3 => l_ue_descendent_rec.ATTRIBUTE3,
x_ATTRIBUTE4 => l_ue_descendent_rec.ATTRIBUTE4,
x_ATTRIBUTE5 => l_ue_descendent_rec.ATTRIBUTE5,
x_ATTRIBUTE6 => l_ue_descendent_rec.ATTRIBUTE6,
x_ATTRIBUTE7 => l_ue_descendent_rec.ATTRIBUTE7,
x_ATTRIBUTE8 => l_ue_descendent_rec.ATTRIBUTE8,
x_ATTRIBUTE9 => l_ue_descendent_rec.ATTRIBUTE9,
x_ATTRIBUTE10 => l_ue_descendent_rec.ATTRIBUTE10,
x_ATTRIBUTE11 => l_ue_descendent_rec.ATTRIBUTE11,
x_ATTRIBUTE12 => l_ue_descendent_rec.ATTRIBUTE12,
x_ATTRIBUTE13 => l_ue_descendent_rec.ATTRIBUTE13,
x_ATTRIBUTE14 => l_ue_descendent_rec.ATTRIBUTE14,
x_ATTRIBUTE15 => l_ue_descendent_rec.ATTRIBUTE15,
x_OBJECT_VERSION_NUMBER => l_ue_descendent_rec.OBJECT_VERSION_NUMBER + 1,
x_LAST_UPDATE_DATE => sysdate, --l_ue_descendent_rec.LAST_UPDATE_DATE,
x_LAST_UPDATED_BY => fnd_global.user_id, --l_ue_descendent_rec.LAST_UPDATED_BY ,
x_LAST_UPDATE_LOGIN => fnd_global.login_id); --l_ue_descendent_rec.LAST_UPDATE_LOGIN );
AHL_UNIT_EFFECTIVITIES_PKG.Delete_Row(l_ue_rec.UNIT_EFFECTIVITY_ID);
AHL_DEBUG_PUB.debug('start delete descedant ue_id' ||l_ue_descendent_rec.UNIT_EFFECTIVITY_ID);
AHL_UNIT_EFFECTIVITIES_PKG.Delete_Row(l_ue_descendent_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 => cp_ue_rec.csi_item_instance_id,
X_MR_INTERVAL_ID => cp_ue_rec.mr_interval_id,
X_MR_EFFECTIVITY_ID => cp_ue_rec.mr_effectivity_id,
X_MR_HEADER_ID => l_new_mr_header_id,
X_STATUS_CODE => cp_ue_rec.status_code, /* status_code */
X_DUE_DATE => cp_ue_rec.due_date,
X_DUE_COUNTER_VALUE => cp_ue_rec.due_counter_value,
X_FORECAST_SEQUENCE => cp_ue_rec.forecast_sequence,
X_REPETITIVE_MR_FLAG => cp_ue_rec.repetitive_mr_flag,
X_TOLERANCE_FLAG => cp_ue_rec.tolerance_flag,
X_REMARKS => null, /* remarks */
X_MESSAGE_CODE => null,
X_PRECEDING_UE_ID => null, /* p_x_temp_mr_rec.preceding_ue_id */
X_DATE_RUN => sysdate, /* date_run */
X_SET_DUE_DATE => cp_ue_rec.set_due_date,
X_ACCOMPLISHED_DATE => null, /* accomplished date */
X_SERVICE_LINE_ID => cp_ue_rec.service_line_id,
X_PROGRAM_MR_HEADER_ID => cp_ue_rec.program_mr_header_id,
X_CANCEL_REASON_CODE => null, /* cancel_reason_code */
X_EARLIEST_DUE_DATE => cp_ue_rec.earliest_due_date,
X_LATEST_DUE_DATE => cp_ue_rec.latest_due_date,
X_defer_from_ue_id => cp_ue_rec.DEFER_FROM_UE_ID,
X_cs_incident_id => null,
X_qa_collection_id => null,
X_orig_deferral_ue_id => cp_ue_rec.ORIG_DEFERRAL_UE_ID,
X_application_usg_code => cp_ue_rec.APPLICATION_USG_CODE,
X_object_type => 'MR',
X_counter_id => cp_ue_rec.counter_id,
X_MANUALLY_PLANNED_FLAG => cp_ue_rec.MANUALLY_PLANNED_FLAG,
X_LOG_SERIES_CODE => cp_ue_rec.LOG_SERIES_CODE,
X_LOG_SERIES_NUMBER => cp_ue_rec.LOG_SERIES_NUMBER,
X_FLIGHT_NUMBER => cp_ue_rec.FLIGHT_NUMBER,
X_MEL_CDL_TYPE_CODE => cp_ue_rec.MEL_CDL_TYPE_CODE,
X_POSITION_PATH_ID => cp_ue_rec.POSITION_PATH_ID,
X_ATA_CODE => cp_ue_rec.ATA_CODE,
X_UNIT_CONFIG_HEADER_ID => cp_ue_rec.UNIT_CONFIG_HEADER_ID,
X_ATTRIBUTE_CATEGORY => cp_ue_rec.ATTRIBUTE_CATEGORY,
X_ATTRIBUTE1 => cp_ue_rec.ATTRIBUTE1,
X_ATTRIBUTE2 => cp_ue_rec.ATTRIBUTE2,
X_ATTRIBUTE3 => cp_ue_rec.ATTRIBUTE3,
X_ATTRIBUTE4 => cp_ue_rec.ATTRIBUTE4,
X_ATTRIBUTE5 => cp_ue_rec.ATTRIBUTE5,
X_ATTRIBUTE6 => cp_ue_rec.ATTRIBUTE6,
X_ATTRIBUTE7 => cp_ue_rec.ATTRIBUTE7,
X_ATTRIBUTE8 => cp_ue_rec.ATTRIBUTE8,
X_ATTRIBUTE9 => cp_ue_rec.ATTRIBUTE9,
X_ATTRIBUTE10 => cp_ue_rec.ATTRIBUTE10,
X_ATTRIBUTE11 => cp_ue_rec.ATTRIBUTE11,
X_ATTRIBUTE12 => cp_ue_rec.ATTRIBUTE12,
X_ATTRIBUTE13 => cp_ue_rec.ATTRIBUTE13,
X_ATTRIBUTE14 => cp_ue_rec.ATTRIBUTE14,
X_ATTRIBUTE15 => cp_ue_rec.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_UNIT_DEFERRALS_B SET UNIT_EFFECTIVITY_ID = l_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;
/*SELECT mr_header_id, effective_to
FROM ahl_mr_headers_v
WHERE title = p_title
AND version_number = p_version_number;*/
SELECT mr_header_id, effective_to
FROM ahl_mr_headers_app_v
WHERE title = p_title
AND version_number = p_version_number;
SELECT unit_config_header_id
FROM ahl_unit_config_headers
WHERE name = p_unit_name;
SELECT instance_id
FROM csi_item_instances
WHERE instance_number = p_csi_instance_number
AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1));
SELECT title, version_number, effective_to, effective_from
FROM ahl_mr_headers_app_v
WHERE mr_header_id = p_mr_header_id;
SELECT instance_number, active_end_date
FROM csi_item_instances
WHERE instance_id = p_csi_item_instance_id;
SELECT name, active_start_date, active_end_date, csi_item_instance_id
FROM ahl_unit_config_headers
WHERE unit_config_header_id = p_uc_header_id;
SELECT title, version_number, effective_to, effective_from
FROM ahl_mr_headers_app_v
WHERE mr_header_id = p_mr_header_id;
SELECT instance_number, active_end_date
FROM csi_item_instances
WHERE instance_id = p_csi_item_instance_id;
select 'x'
from okc_k_headers_all_b
where contract_number = p_contract_number
and nvl(contract_number_modifier, 'X') = nvl(decode(p_contract_modifier, FND_API.G_MISS_CHAR, null, p_contract_modifier), 'X');
SELECT
OBJECT_VERSION_NUMBER,
CSI_ITEM_INSTANCE_ID,
MR_INTERVAL_ID,
MR_EFFECTIVITY_ID,
MR_HEADER_ID,
STATUS_CODE,
ACCOMPLISHED_DATE,
DUE_DATE,
DUE_COUNTER_VALUE,
FORECAST_SEQUENCE,
REPETITIVE_MR_FLAG,
TOLERANCE_FLAG,
REMARKS,
MESSAGE_CODE,
PRECEDING_UE_ID,
DATE_RUN,
SET_DUE_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
FROM AHL_UNIT_EFFECTIVITIES_APP_V
WHERE unit_effectivity_id = p_ue_id
FOR UPDATE OF OBJECT_VERSION_NUMBER;
AHL_UNIT_EFFECTIVITIES_PKG.update_row(
x_unit_effectivity_id => p_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 => null,
x_due_counter_value => null,
x_forecast_sequence => null,
x_repetitive_mr_flag => null,
x_tolerance_flag => null,
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 => l_ue_rec.DATE_RUN,
x_set_due_date => l_ue_rec.SET_DUE_DATE,
x_accomplished_date => TRUNC(sysdate),
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_qa_collection_id => l_ue_rec.qa_collection_id,
x_orig_deferral_ue_id => l_ue_rec.orig_deferral_ue_id,
x_cs_incident_id => l_ue_rec.cs_incident_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 => TRUNC(sysdate),
x_last_updated_by => fnd_global.user_id,
x_last_update_login => fnd_global.login_id);
SELECT 'X'
FROM ahl_pc_headers_b
WHERE pc_header_id = c_pc_header_id
AND status = 'COMPLETE';
SELECT mrh.mr_header_id, mre.mr_effectivity_id
FROM ahl_mr_headers_b mrh, ahl_mr_effectivities mre,
ahl_pc_nodes_b pcn
WHERE mrh.mr_header_id = mre.mr_header_id
AND mre.pc_node_id = pcn.pc_node_id
AND pcn.pc_header_id = c_pc_header_id
AND TRUNC(NVL(mrh.effective_to, SYSDATE+1)) > TRUNC(SYSDATE);