[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
G_OPR_DELETE CONSTANT VARCHAR2(1) := 'D';
PROCEDURE Update_SR_Unit_Effectivity(
p_sr_ue_id IN NUMBER,
p_due_date_flag IN BOOLEAN,
p_new_due_date IN DATE,
p_instance_flag IN BOOLEAN,
p_new_instance_id IN NUMBER,
p_status_flag IN BOOLEAN,
p_new_status_code IN VARCHAR2,
x_assigned_to_visit_flag OUT NOCOPY BOOLEAN,
x_new_tolerance_flag OUT NOCOPY VARCHAR2);
PROCEDURE Validate_Request_For_Update(
x_sr_ue_id OUT NOCOPY NUMBER,
x_sr_ue_ovn OUT NOCOPY NUMBER);
PROCEDURE Handle_Summary_Update(
p_sr_ue_id IN NUMBER
);
SELECT incident_id
FROM cs_incidents_all_b
WHERE incident_id = c_request_id;
SELECT instance_id, instance_number, active_end_date
FROM csi_item_instances
WHERE instance_id = c_instance_id;
SELECT ahl_util_uc_pkg.get_uc_header_id(l_customer_product_id) into l_uc_hdr_id from dual;
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' BEFORE Calling TABLE HANDLER AHL_UNIT_EFFECTIVITIES_PKG.Insert_Row' );
AHL_UNIT_EFFECTIVITIES_PKG.Insert_Row (
X_ROWID => l_rowid,
X_UNIT_EFFECTIVITY_ID => l_unit_effectivity_id,
X_MANUALLY_PLANNED_FLAG => 'Y',
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_CLEAR_STATION_ORG_ID => null,
--X_CLEAR_STATION_DEPT_ID => null,
X_UNIT_CONFIG_HEADER_ID => l_uc_hdr_id,
X_QA_COLLECTION_ID => null,
X_CS_INCIDENT_ID => l_request_id,
X_OBJECT_TYPE => 'SR',
X_APPLICATION_USG_CODE => l_appln_code,
X_COUNTER_ID => null,
X_EARLIEST_DUE_DATE => null,
X_LATEST_DUE_DATE => null,
X_FORECAST_SEQUENCE => null,
X_REPETITIVE_MR_FLAG => null,
X_TOLERANCE_FLAG => null,
X_MESSAGE_CODE => null,
X_DATE_RUN => null,
X_PRECEDING_UE_ID => null,
X_SET_DUE_DATE => null,
X_ACCOMPLISHED_DATE => l_accomplished_date,
X_SERVICE_LINE_ID => null,
X_PROGRAM_MR_HEADER_ID => null,
X_CANCEL_REASON_CODE => 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,
X_OBJECT_VERSION_NUMBER => 1,
X_CSI_ITEM_INSTANCE_ID => l_customer_product_id,
X_MR_HEADER_ID => null,
X_MR_EFFECTIVITY_ID => null,
X_MR_INTERVAL_ID => null,
X_STATUS_CODE => l_ump_status,
X_DUE_DATE => l_exp_resolution_date,
X_DUE_COUNTER_VALUE => null,
X_DEFER_FROM_UE_ID => null,
X_ORIG_DEFERRAL_UE_ID => null,
X_REMARKS => null,
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 );
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY , ' AFTER Calling TABLE HANDLER AHL_UNIT_EFFECTIVITIES_PKG.Insert_Row' );
PROCEDURE Process_SR_Updates
(
x_return_status OUT NOCOPY VARCHAR2) IS
l_api_name CONSTANT VARCHAR2(30) := 'Process_SR_Updates';
L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Process_SR_Updates';
SAVEPOINT Process_SR_Updates_Pvt;
Validate_Request_For_Update(x_sr_ue_id => l_sr_ue_id,
x_sr_ue_ovn => l_sr_ue_ovn);
ROLLBACK TO Process_SR_Updates_Pvt;
ROLLBACK TO Process_SR_Updates_Pvt;
ROLLBACK TO Process_SR_Updates_Pvt;
p_procedure_name => 'Process_SR_Updates',
p_error_text => SUBSTR(SQLERRM,1,240));
END Process_SR_Updates;
/*SELECT incident_id, incident_status_id, closed_flag, customer_product_id, expected_resolution_date
FROM CS_INCIDENTS_V
WHERE INCIDENT_ID like DECODE(p_request_id, null, '%', p_request_id)
AND INCIDENT_NUMBER like NVL(p_request_number, '%')
AND OBJECT_VERSION_NUMBER = p_object_version_number;*/
SELECT INC.incident_id, INC.incident_status_id, NVL(STATUS.CLOSE_FLAG, 'N') closed_flag, INC.customer_product_id, INC.expected_resolution_date
FROM CS_INCIDENT_STATUSES_B STATUS,CS_INCIDENTS_ALL_B INC
WHERE INC.INCIDENT_STATUS_ID = STATUS.INCIDENT_STATUS_ID
AND INC.INCIDENT_ID like DECODE(p_request_id, null, '%', p_request_id)
AND INC.INCIDENT_NUMBER like NVL(p_request_number, '%')
AND INC.OBJECT_VERSION_NUMBER = p_object_version_number;
SELECT UNIT_EFFECTIVITY_ID
FROM AHL_UNIT_EFFECTIVITIES_APP_V
WHERE CS_INCIDENT_ID = p_request_id
AND (STATUS_CODE IS NULL OR STATUS_CODE NOT IN (G_UMP_DEFERRED_STATUS, G_UMP_EXCEPTION_STATUS));
SELECT 'x' from AHL_VISIT_TASKS_B
where UNIT_EFFECTIVITY_ID = p_ue_id;
SELECT 'x'
FROM AHL_UNIT_EFFECTIVITIES_APP_V UE
WHERE UE.UNIT_EFFECTIVITY_ID = p_mr_ue_id AND
UE.OBJECT_VERSION_NUMBER = p_mr_ue_ovn AND
EXISTS (SELECT 'x' from AHL_UE_RELATIONSHIPS UR
WHERE UR.UE_ID = p_sr_ue_id AND
UR.RELATED_UE_ID = p_mr_ue_id AND
UR.relationship_code = G_UE_PARENT_REL_CODE);
SELECT TITLE, PROGRAM_TYPE_CODE
FROM AHL_MR_HEADERS_APP_V
WHERE MR_HEADER_ID = p_mr_header_id;
SELECT INSTANCE_NUMBER
FROM CSI_ITEM_INSTANCES
WHERE INSTANCE_ID = p_csi_instance_id;
select 'X'
from csi_ii_relationships
where subject_id = c_mr_instance_id
start with object_id = c_sr_instance_id
and RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
and trunc(nvl(ACTIVE_START_DATE, sysdate)) <= trunc(sysdate)
and trunc(nvl(ACTIVE_END_DATE, sysdate + 1)) > trunc(sysdate)
connect by prior subject_id = object_id
and RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
and trunc(nvl(ACTIVE_START_DATE, sysdate)) <= trunc(sysdate)
and trunc(nvl(ACTIVE_END_DATE, sysdate + 1)) > trunc(sysdate)
UNION ALL
select 'X'
from csi_item_instances
where instance_id = c_sr_instance_id
and instance_id = c_mr_instance_id;
IF (p_x_sr_mr_association_tbl(i).OPERATION_FLAG NOT IN (G_OPR_CREATE, G_OPR_DELETE)) THEN
FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_SR_OPR_INVALID');
ELSIF (p_x_sr_mr_association_tbl(i).OPERATION_FLAG = G_OPR_DELETE) THEN
-- Delete Operation
Get_MR_UnitEffectivity(p_sr_ue_id => p_sr_ue_id,
p_x_sr_mr_association_rec => p_x_sr_mr_association_tbl(i));
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Record ' || i || ' for Delete operation is Valid');
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Record ' || i || ' for Delete operation is Not valid');
SELECT UE_RELATIONSHIP_ID
FROM AHL_UE_RELATIONSHIPS
WHERE UE_ID = p_sr_ue_id AND
RELATED_UE_ID = p_mr_ue_id AND
RELATIONSHIP_CODE = G_UE_PARENT_REL_CODE;
SELECT UE_RELATIONSHIP_ID, RELATED_UE_ID, level
FROM AHL_UE_RELATIONSHIPS
START WITH UE_ID = p_mr_ue_id
CONNECT BY PRIOR RELATED_UE_ID = UE_ID
ORDER BY LEVEL DESC; /* Bottom Up */
IF (p_sr_mr_association_tbl(i).OPERATION_FLAG = G_OPR_DELETE) THEN
IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'About to Disassociate Record with index ' || i);
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'About to Delete Relationship with RELATIONSHIP_ID = ' || l_rel_dtls_rec.UE_RELATIONSHIP_ID || ' for a Group MR');
AHL_UE_RELATIONSHIPS_PKG.DELETE_ROW(X_UE_RELATIONSHIP_ID => l_rel_dtls_rec.UE_RELATIONSHIP_ID);
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'About to Delete Unit Effectivity with UNIT_EFFECTIVITY_ID = ' || l_rel_dtls_rec.RELATED_UE_ID || ' for a Group MR');
AHL_UNIT_EFFECTIVITIES_PKG.DELETE_ROW(X_UNIT_EFFECTIVITY_ID => l_rel_dtls_rec.RELATED_UE_ID);
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'About to Delete Relationship with RELATIONSHIP_ID = ' || l_relationship_id);
AHL_UE_RELATIONSHIPS_PKG.DELETE_ROW(X_UE_RELATIONSHIP_ID => l_relationship_id);
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'About to Delete Unit Effectivity with UNIT_EFFECTIVITY_ID = ' || p_sr_mr_association_tbl(i).UNIT_EFFECTIVITY_ID);
AHL_UNIT_EFFECTIVITIES_PKG.DELETE_ROW(X_UNIT_EFFECTIVITY_ID => p_sr_mr_association_tbl(i).UNIT_EFFECTIVITY_ID);
SELECT 'x' from AHL_UNIT_EFFECTIVITIES_B ue
WHERE ue.mr_header_id = p_mr_id AND
ue.csi_item_instance_id = p_instance_id AND
EXISTS (SELECT 'x' FROM ahl_ue_relationships ur
WHERE ur.related_ue_id = ue.unit_effectivity_id
START WITH ur.ue_id = p_sr_ue_id
CONNECT BY PRIOR ur.related_ue_id = ur.ue_id);
SELECT
ORIGINATING_WO_ID
FROM
AHL_UNIT_EFFECTIVITIES_B
WHERE
UNIT_EFFECTIVITY_ID = p_sr_ue_id
AND OBJECT_TYPE = 'SR';
SELECT COUNT(*) from AHL_MR_RELATIONSHIPS
WHERE MR_HEADER_ID = p_mr_id;
UPDATE
AHL_UNIT_EFFECTIVITIES_B
SET
ORIGINATING_WO_ID = l_origin_wo_id
WHERE
UNIT_EFFECTIVITY_ID IN (
SELECT
RELATED_UE_ID
FROM
AHL_UE_RELATIONSHIPS
WHERE
ORIGINATOR_UE_ID = p_sr_ue_id
)
AND ORIGINATING_WO_ID IS NULL;
/* SELECT UE.UNIT_EFFECTIVITY_ID
FROM AHL_UNIT_EFFECTIVITIES_APP_V UE
WHERE UE.MR_HEADER_ID = (SELECT MR_HEADER_ID FROM AHL_MR_HEADERS_APP_V
WHERE MR_HEADER_ID like DECODE(p_mr_header_id, null, '%', p_mr_header_id) AND
TITLE like DECODE(p_mr_header_id, null, p_mr_title, '%') AND
VERSION_NUMBER like DECODE(p_mr_header_id, null, p_mr_version, '%')) AND
VERSION_NUMBER like DECODE(p_mr_header_id, null, '' || p_mr_version || '', '%')) AND
UE.CSI_ITEM_INSTANCE_ID = (SELECT INSTANCE_ID FROM CSI_ITEM_INSTANCES
WHERE INSTANCE_ID like DECODE(p_csi_instance_id, null, '%', p_csi_instance_id) AND
INSTANCE_NUMBER like DECODE(p_csi_instance_id, null, p_csi_instance_number, '%')) AND
EXISTS (SELECT 'x' FROM AHL_UE_RELATIONSHIPS
WHERE UE_ID = p_sr_ue_id AND
RELATED_UE_ID = UE.UNIT_EFFECTIVITY_ID AND
RELATIONSHIP_CODE = G_UE_PARENT_REL_CODE);*/
SELECT UE.UNIT_EFFECTIVITY_ID
FROM AHL_UNIT_EFFECTIVITIES_APP_V UE
WHERE UE.MR_HEADER_ID =
(SELECT MR_HEADER_ID
FROM AHL_MR_HEADERS_APP_V
WHERE MR_HEADER_ID = NVL(p_mr_header_id,MR_HEADER_ID)
AND TITLE like DECODE(p_mr_header_id, null, p_mr_title, '%')
AND VERSION_NUMBER like DECODE(p_mr_header_id, null, '' || p_mr_version || '', '%')
)
AND UE.CSI_ITEM_INSTANCE_ID =
(SELECT INSTANCE_ID
FROM CSI_ITEM_INSTANCES
WHERE INSTANCE_ID = NVL(p_csi_instance_id,INSTANCE_ID)
AND INSTANCE_NUMBER like DECODE(p_csi_instance_id, null, p_csi_instance_number, '%')
)
AND EXISTS
(SELECT 'x'
FROM AHL_UE_RELATIONSHIPS
WHERE UE_ID = p_sr_ue_id
AND RELATED_UE_ID = UE.UNIT_EFFECTIVITY_ID
AND RELATIONSHIP_CODE = G_UE_PARENT_REL_CODE
);
SELECT MR_HEADER_ID
FROM AHL_MR_HEADERS_APP_V
WHERE UPPER(TITLE) = UPPER(p_mr_title) AND
VERSION_NUMBER = p_mr_version;
SELECT INSTANCE_ID
FROM CSI_ITEM_INSTANCES
WHERE INSTANCE_NUMBER = p_csi_instance_number;
AHL_UNIT_EFFECTIVITIES_PKG.Insert_Row (
X_ROWID => l_temp_row_id,
X_UNIT_EFFECTIVITY_ID => x_ue_id,
X_MANUALLY_PLANNED_FLAG => 'Y',
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_CLEAR_STATION_ORG_ID => null,
--X_CLEAR_STATION_DEPT_ID => null,
X_UNIT_CONFIG_HEADER_ID => null,
X_QA_COLLECTION_ID => null,
X_CS_INCIDENT_ID => null,
X_OBJECT_TYPE => G_UE_MR_OBJECT_TYPE,
X_APPLICATION_USG_CODE => G_APP_MODULE,
X_COUNTER_ID => null,
X_EARLIEST_DUE_DATE => null,
X_LATEST_DUE_DATE => null,
X_FORECAST_SEQUENCE => null,
X_REPETITIVE_MR_FLAG => null,
X_TOLERANCE_FLAG => null,
X_MESSAGE_CODE => null,
X_DATE_RUN => null,
X_PRECEDING_UE_ID => null,
X_SET_DUE_DATE => null,
X_ACCOMPLISHED_DATE => null,
X_SERVICE_LINE_ID => null,
X_PROGRAM_MR_HEADER_ID => null,
X_CANCEL_REASON_CODE => 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,
X_OBJECT_VERSION_NUMBER => 1,
X_CSI_ITEM_INSTANCE_ID => p_instance_id,
X_MR_HEADER_ID => p_mr_header_id,
X_MR_EFFECTIVITY_ID => null,
X_MR_INTERVAL_ID => null,
X_STATUS_CODE => null,
X_DUE_DATE => p_due_date,
X_DUE_COUNTER_VALUE => null,
X_DEFER_FROM_UE_ID => null,
X_ORIG_DEFERRAL_UE_ID => null,
X_REMARKS => null,
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 => x_ue_rel_id,
X_UE_ID => p_ue_id,
X_RELATED_UE_ID => p_related_ue_id,
X_RELATIONSHIP_CODE => p_relationship_code,
X_ORIGINATOR_UE_ID => p_originator_id,
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,
X_OBJECT_VERSION_NUMBER => 1,
X_LAST_UPDATE_DATE => SYSDATE,
X_LAST_UPDATED_BY => p_user_id,
X_CREATION_DATE => SYSDATE,
X_CREATED_BY => p_user_id,
X_LAST_UPDATE_LOGIN => p_login_id);
SELECT UE.MR_HEADER_ID, UE.CSI_ITEM_INSTANCE_ID
FROM AHL_UNIT_EFFECTIVITIES_B UE, AHL_UE_RELATIONSHIPS UR
WHERE UR.ORIGINATOR_UE_ID = p_sr_ue_id AND
UR.RELATIONSHIP_CODE = G_UE_PARENT_REL_CODE AND
UE.UNIT_EFFECTIVITY_ID = UR.RELATED_UE_ID
INTERSECT
SELECT RELATED_MR_HEADER_ID, RELATED_CSI_ITEM_INSTANCE_ID
FROM AHL_APPLICABLE_MR_RELNS;
SELECT * FROM AHL_APPLICABLE_MR_RELNS
FOR UPDATE OF UE_ID;
SELECT child.ue_id child_ue_id, NVL(parent.ue_id, p_mr_ue_id) parent_ue_id, child.relationship_code
FROM AHL_APPLICABLE_MR_RELNS child, AHL_APPLICABLE_MR_RELNS parent
WHERE child.ORIG_MR_HEADER_ID = p_mr_header_id AND -- Filter condition
child.ORIG_CSI_ITEM_INSTANCE_ID = p_csi_instance_id AND -- Filter condition
parent.RELATED_MR_HEADER_ID (+) = child.MR_HEADER_ID AND -- Join condition
parent.RELATED_CSI_ITEM_INSTANCE_ID (+) = child.CSI_ITEM_INSTANCE_ID; -- Join condition
SELECT count(*) from AHL_APPLICABLE_MRS;
SELECT count(*) from AHL_APPLICABLE_MR_RELNS;*/
UPDATE AHL_APPLICABLE_MR_RELNS
SET UE_ID = l_new_ue_id
WHERE CURRENT OF mr_relns_upd_csr;
SELECT TITLE
FROM AHL_MR_HEADERS_B
WHERE MR_HEADER_ID = p_mr_header_id;
SELECT MR.TITLE
FROM AHL_MR_HEADERS_B MR, AHL_UNIT_EFFECTIVITIES_APP_V UE
WHERE UE.UNIT_EFFECTIVITY_ID = p_unit_effectivity_id AND
UE.MR_HEADER_ID = MR.MR_HEADER_ID;
SELECT * FROM AHL_UNIT_EFFECTIVITIES_APP_V
WHERE UNIT_EFFECTIVITY_ID IN (SELECT RELATED_UE_ID from AHL_UE_RELATIONSHIPS
WHERE ORIGINATOR_UE_ID = p_sr_ue_id
AND RELATIONSHIP_CODE = G_UE_PARENT_REL_CODE);
AHL_UNIT_EFFECTIVITIES_PKG.UPDATE_ROW(
X_UNIT_EFFECTIVITY_ID => l_curr_ue_rec.UNIT_EFFECTIVITY_ID,
X_MANUALLY_PLANNED_FLAG => l_curr_ue_rec.MANUALLY_PLANNED_FLAG,
X_LOG_SERIES_CODE => l_curr_ue_rec.LOG_SERIES_CODE,
X_LOG_SERIES_NUMBER => l_curr_ue_rec.LOG_SERIES_NUMBER,
X_FLIGHT_NUMBER => l_curr_ue_rec.FLIGHT_NUMBER,
X_MEL_CDL_TYPE_CODE => l_curr_ue_rec.MEL_CDL_TYPE_CODE,
X_POSITION_PATH_ID => l_curr_ue_rec.POSITION_PATH_ID,
X_ATA_CODE => l_curr_ue_rec.ATA_CODE,
--X_CLEAR_STATION_ORG_ID => l_curr_ue_rec.CLEAR_STATION_ORG_ID,
--X_CLEAR_STATION_DEPT_ID => l_curr_ue_rec.CLEAR_STATION_DEPT_ID,
X_UNIT_CONFIG_HEADER_ID => l_curr_ue_rec.UNIT_CONFIG_HEADER_ID,
X_QA_COLLECTION_ID => l_curr_ue_rec.QA_COLLECTION_ID,
X_CS_INCIDENT_ID => l_curr_ue_rec.CS_INCIDENT_ID,
X_OBJECT_TYPE => l_curr_ue_rec.OBJECT_TYPE,
X_APPLICATION_USG_CODE => l_curr_ue_rec.APPLICATION_USG_CODE,
X_COUNTER_ID => l_curr_ue_rec.COUNTER_ID,
X_EARLIEST_DUE_DATE => l_curr_ue_rec.EARLIEST_DUE_DATE,
X_LATEST_DUE_DATE => l_curr_ue_rec.LATEST_DUE_DATE,
X_FORECAST_SEQUENCE => l_curr_ue_rec.FORECAST_SEQUENCE,
X_REPETITIVE_MR_FLAG => l_curr_ue_rec.REPETITIVE_MR_FLAG,
X_TOLERANCE_FLAG => l_temp_tolerance_flag, -- Updated
X_MESSAGE_CODE => l_curr_ue_rec.MESSAGE_CODE,
X_DATE_RUN => l_curr_ue_rec.DATE_RUN,
X_PRECEDING_UE_ID => l_curr_ue_rec.PRECEDING_UE_ID,
X_SET_DUE_DATE => l_curr_ue_rec.SET_DUE_DATE,
X_ACCOMPLISHED_DATE => l_curr_ue_rec.ACCOMPLISHED_DATE,
X_SERVICE_LINE_ID => l_curr_ue_rec.SERVICE_LINE_ID,
X_PROGRAM_MR_HEADER_ID => l_curr_ue_rec.PROGRAM_MR_HEADER_ID,
X_CANCEL_REASON_CODE => l_curr_ue_rec.CANCEL_REASON_CODE,
X_ATTRIBUTE_CATEGORY => l_curr_ue_rec.ATTRIBUTE_CATEGORY,
X_ATTRIBUTE1 => l_curr_ue_rec.ATTRIBUTE1,
X_ATTRIBUTE2 => l_curr_ue_rec.ATTRIBUTE2,
X_ATTRIBUTE3 => l_curr_ue_rec.ATTRIBUTE3,
X_ATTRIBUTE4 => l_curr_ue_rec.ATTRIBUTE4,
X_ATTRIBUTE5 => l_curr_ue_rec.ATTRIBUTE5,
X_ATTRIBUTE6 => l_curr_ue_rec.ATTRIBUTE6,
X_ATTRIBUTE7 => l_curr_ue_rec.ATTRIBUTE7,
X_ATTRIBUTE8 => l_curr_ue_rec.ATTRIBUTE8,
X_ATTRIBUTE9 => l_curr_ue_rec.ATTRIBUTE9,
X_ATTRIBUTE10 => l_curr_ue_rec.ATTRIBUTE10,
X_ATTRIBUTE11 => l_curr_ue_rec.ATTRIBUTE11,
X_ATTRIBUTE12 => l_curr_ue_rec.ATTRIBUTE12,
X_ATTRIBUTE13 => l_curr_ue_rec.ATTRIBUTE13,
X_ATTRIBUTE14 => l_curr_ue_rec.ATTRIBUTE14,
X_ATTRIBUTE15 => l_curr_ue_rec.ATTRIBUTE15,
X_OBJECT_VERSION_NUMBER => l_curr_ue_rec.OBJECT_VERSION_NUMBER + 1, -- Updated
X_CSI_ITEM_INSTANCE_ID => l_curr_ue_rec.CSI_ITEM_INSTANCE_ID,
X_MR_HEADER_ID => l_curr_ue_rec.MR_HEADER_ID,
X_MR_EFFECTIVITY_ID => l_curr_ue_rec.MR_EFFECTIVITY_ID,
X_MR_INTERVAL_ID => l_curr_ue_rec.MR_INTERVAL_ID,
X_STATUS_CODE => l_curr_ue_rec.STATUS_CODE,
X_DUE_DATE => p_new_due_date, -- Updated
X_DUE_COUNTER_VALUE => l_curr_ue_rec.DUE_COUNTER_VALUE,
X_DEFER_FROM_UE_ID => l_curr_ue_rec.DEFER_FROM_UE_ID,
X_ORIG_DEFERRAL_UE_ID => l_curr_ue_rec.ORIG_DEFERRAL_UE_ID,
X_REMARKS => l_curr_ue_rec.REMARKS,
X_LAST_UPDATE_DATE => SYSDATE, -- Updated
X_LAST_UPDATED_BY => fnd_global.user_id, -- Updated
X_LAST_UPDATE_LOGIN => fnd_global.login_id -- Updated
);
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Updated Due Date and Tolerance Flag in ' || l_temp_count|| ' dependent unit effectivities.');
PROCEDURE Update_SR_Unit_Effectivity(
p_sr_ue_id IN NUMBER,
p_due_date_flag IN BOOLEAN,
p_new_due_date IN DATE,
p_instance_flag IN BOOLEAN,
p_new_instance_id IN NUMBER,
p_status_flag IN BOOLEAN,
p_new_status_code IN VARCHAR2,
x_assigned_to_visit_flag OUT NOCOPY BOOLEAN,
x_new_tolerance_flag OUT NOCOPY VARCHAR2) IS
CURSOR get_UE_dtls_csr IS
SELECT * FROM AHL_UNIT_EFFECTIVITIES_APP_V
WHERE UNIT_EFFECTIVITY_ID = p_sr_ue_id;
SELECT 'x' FROM AHL_VISIT_TASKS_B
WHERE UNIT_EFFECTIVITY_ID = p_sr_ue_id;
L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Update_SR_Unit_Effectivity';
AHL_UNIT_EFFECTIVITIES_PKG.UPDATE_ROW(
X_UNIT_EFFECTIVITY_ID => l_curr_ue_rec.UNIT_EFFECTIVITY_ID,
X_MANUALLY_PLANNED_FLAG => l_curr_ue_rec.MANUALLY_PLANNED_FLAG,
X_LOG_SERIES_CODE => l_curr_ue_rec.LOG_SERIES_CODE,
X_LOG_SERIES_NUMBER => l_curr_ue_rec.LOG_SERIES_NUMBER,
X_FLIGHT_NUMBER => l_curr_ue_rec.FLIGHT_NUMBER,
X_MEL_CDL_TYPE_CODE => l_curr_ue_rec.MEL_CDL_TYPE_CODE,
X_POSITION_PATH_ID => l_curr_ue_rec.POSITION_PATH_ID,
X_ATA_CODE => l_curr_ue_rec.ATA_CODE,
--X_CLEAR_STATION_ORG_ID => l_curr_ue_rec.CLEAR_STATION_ORG_ID,
--X_CLEAR_STATION_DEPT_ID => l_curr_ue_rec.CLEAR_STATION_DEPT_ID,
X_UNIT_CONFIG_HEADER_ID => l_curr_ue_rec.UNIT_CONFIG_HEADER_ID,
X_QA_COLLECTION_ID => l_curr_ue_rec.QA_COLLECTION_ID,
X_CS_INCIDENT_ID => l_curr_ue_rec.CS_INCIDENT_ID,
X_OBJECT_TYPE => l_curr_ue_rec.OBJECT_TYPE,
X_APPLICATION_USG_CODE => l_curr_ue_rec.APPLICATION_USG_CODE,
X_COUNTER_ID => l_curr_ue_rec.COUNTER_ID,
X_EARLIEST_DUE_DATE => l_curr_ue_rec.EARLIEST_DUE_DATE,
X_LATEST_DUE_DATE => l_curr_ue_rec.LATEST_DUE_DATE,
X_FORECAST_SEQUENCE => l_curr_ue_rec.FORECAST_SEQUENCE,
X_REPETITIVE_MR_FLAG => l_curr_ue_rec.REPETITIVE_MR_FLAG,
X_TOLERANCE_FLAG => l_temp_tolerance_flag, -- Updated
X_MESSAGE_CODE => l_curr_ue_rec.MESSAGE_CODE,
X_DATE_RUN => l_curr_ue_rec.DATE_RUN,
X_PRECEDING_UE_ID => l_curr_ue_rec.PRECEDING_UE_ID,
X_SET_DUE_DATE => l_curr_ue_rec.SET_DUE_DATE,
X_ACCOMPLISHED_DATE => l_accomplished_date, -- Updated
X_SERVICE_LINE_ID => l_curr_ue_rec.SERVICE_LINE_ID,
X_PROGRAM_MR_HEADER_ID => l_curr_ue_rec.PROGRAM_MR_HEADER_ID,
X_CANCEL_REASON_CODE => l_curr_ue_rec.CANCEL_REASON_CODE,
X_ATTRIBUTE_CATEGORY => l_curr_ue_rec.ATTRIBUTE_CATEGORY,
X_ATTRIBUTE1 => l_curr_ue_rec.ATTRIBUTE1,
X_ATTRIBUTE2 => l_curr_ue_rec.ATTRIBUTE2,
X_ATTRIBUTE3 => l_curr_ue_rec.ATTRIBUTE3,
X_ATTRIBUTE4 => l_curr_ue_rec.ATTRIBUTE4,
X_ATTRIBUTE5 => l_curr_ue_rec.ATTRIBUTE5,
X_ATTRIBUTE6 => l_curr_ue_rec.ATTRIBUTE6,
X_ATTRIBUTE7 => l_curr_ue_rec.ATTRIBUTE7,
X_ATTRIBUTE8 => l_curr_ue_rec.ATTRIBUTE8,
X_ATTRIBUTE9 => l_curr_ue_rec.ATTRIBUTE9,
X_ATTRIBUTE10 => l_curr_ue_rec.ATTRIBUTE10,
X_ATTRIBUTE11 => l_curr_ue_rec.ATTRIBUTE11,
X_ATTRIBUTE12 => l_curr_ue_rec.ATTRIBUTE12,
X_ATTRIBUTE13 => l_curr_ue_rec.ATTRIBUTE13,
X_ATTRIBUTE14 => l_curr_ue_rec.ATTRIBUTE14,
X_ATTRIBUTE15 => l_curr_ue_rec.ATTRIBUTE15,
X_OBJECT_VERSION_NUMBER => l_curr_ue_rec.OBJECT_VERSION_NUMBER + 1, -- Updated
X_CSI_ITEM_INSTANCE_ID => l_temp_instance_id,
X_MR_HEADER_ID => l_curr_ue_rec.MR_HEADER_ID,
X_MR_EFFECTIVITY_ID => l_curr_ue_rec.MR_EFFECTIVITY_ID,
X_MR_INTERVAL_ID => l_curr_ue_rec.MR_INTERVAL_ID,
X_STATUS_CODE => l_temp_status_code, -- Updated
X_DUE_DATE => l_temp_due_date, -- Updated
X_DUE_COUNTER_VALUE => l_curr_ue_rec.DUE_COUNTER_VALUE,
X_DEFER_FROM_UE_ID => l_curr_ue_rec.DEFER_FROM_UE_ID,
X_ORIG_DEFERRAL_UE_ID => l_curr_ue_rec.ORIG_DEFERRAL_UE_ID,
X_REMARKS => l_curr_ue_rec.REMARKS,
X_LAST_UPDATE_DATE => SYSDATE, -- Updated
X_LAST_UPDATED_BY => fnd_global.user_id, -- Updated
X_LAST_UPDATE_LOGIN => fnd_global.login_id -- Updated
);
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Unit Effectivity ' || p_sr_ue_id || ' has been updated.');
END Update_SR_Unit_Effectivity;
PROCEDURE Validate_Request_For_Update(
x_sr_ue_id OUT NOCOPY NUMBER,
x_sr_ue_ovn OUT NOCOPY NUMBER) IS
CURSOR check_request_exists_csr IS
SELECT 'x' FROM CS_INCIDENTS
WHERE INCIDENT_ID = CS_SERVICEREQUEST_PVT.user_hooks_rec.request_id;
SELECT UNIT_EFFECTIVITY_ID, OBJECT_VERSION_NUMBER
FROM AHL_UNIT_EFFECTIVITIES_APP_V
WHERE CS_INCIDENT_ID = CS_SERVICEREQUEST_PVT.user_hooks_rec.request_id
AND (STATUS_CODE IS NULL OR STATUS_CODE NOT IN (G_UMP_DEFERRED_STATUS, G_UMP_EXCEPTION_STATUS));
L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Validate_Request_For_Update';
END Validate_Request_For_Update;
SELECT 'x' from AHL_VISIT_TASKS_B
WHERE UNIT_EFFECTIVITY_ID = p_sr_ue_id;
SELECT UE_RELATIONSHIP_ID, RELATED_UE_ID, LEVEL
FROM AHL_UE_RELATIONSHIPS
WHERE originator_ue_id = p_sr_ue_id
START WITH UE_ID = p_sr_ue_id
CONNECT BY PRIOR RELATED_UE_ID = UE_ID
ORDER BY LEVEL DESC; /* Bottom Up ordering*/
AHL_UE_RELATIONSHIPS_PKG.DELETE_ROW(X_UE_RELATIONSHIP_ID => rel_and_ue_id_rec.UE_RELATIONSHIP_ID);
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Deleted Relationship with UE_RELATIONSHIP_ID = ' || rel_and_ue_id_rec.UE_RELATIONSHIP_ID);
AHL_UNIT_EFFECTIVITIES_PKG.DELETE_ROW(X_UNIT_EFFECTIVITY_ID => rel_and_ue_id_rec.RELATED_UE_ID);
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Deleted Unit Effectivity with UNIT_EFFECTIVITY_ID = ' || rel_and_ue_id_rec.RELATED_UE_ID);
AHL_UNIT_EFFECTIVITIES_PKG.DELETE_ROW(X_UNIT_EFFECTIVITY_ID => p_sr_ue_id);
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Deleted Unit Effectivity with UNIT_EFFECTIVITY_ID = ' || p_sr_ue_id);
SELECT CSI_ITEM_INSTANCE_ID, DUE_DATE, STATUS_CODE from AHL_UNIT_EFFECTIVITIES_B
WHERE UNIT_EFFECTIVITY_ID = p_sr_ue_id
FOR UPDATE NOWAIT;
Update_SR_Unit_Effectivity(p_sr_ue_id => p_sr_ue_id,
p_due_date_flag => l_due_date_changed,
p_new_due_date => l_new_due_date,
p_instance_flag => l_instance_changed,
p_new_instance_id => l_new_instance,
p_status_flag => l_status_changed,
p_new_status_code => l_new_ue_status,
x_assigned_to_visit_flag => l_assigned,
x_new_tolerance_flag => l_new_tolerance_flag);
Handle_Summary_Update(
p_sr_ue_id => p_sr_ue_id
);
SELECT COUNT(*) FROM AHL_UE_RELATIONSHIPS
WHERE UE_ID = p_sr_ue_id;
SELECT 'x' from AHL_VISIT_TASKS_B
where UNIT_EFFECTIVITY_ID = p_sr_ue_id;
SELECT COUNT(*) FROM AHL_UE_RELATIONSHIPS
WHERE UE_ID = p_sr_ue_id;
PROCEDURE Handle_Summary_Update(
p_sr_ue_id IN NUMBER
)IS
--Check if the SR fits in any of below conditions
-- 1. Non-Routines(NR) created on the shop floor.
-- 2. Non-Routines with no MRs associated and planned from UMP into a Visit.
--Get Corresponding NR Workorders for the given SR Ue id.
CURSOR c_get_ue_wo(p_sr_ue_id IN NUMBER)
IS
SELECT
WO.workorder_id,
WIPJ.description,
VTSK.visit_task_number,
VST.visit_number
FROM
AHL_WORKORDERS WO,
WIP_DISCRETE_JOBS WIPJ,
AHL_VISIT_TASKS_B VTSK,
AHL_VISITS_B VST,
AHL_UNIT_EFFECTIVITIES_B UE
WHERE
WO.status_code not in (4,12,7,5) AND -- Completed/Closed/Cancelled/Completed_No_Charge
WO.wip_entity_id = WIPJ.wip_entity_id AND
WO.visit_task_id = VTSK.visit_task_id AND
WO.visit_id = VST.visit_id AND
VTSK.unit_effectivity_id = UE.Unit_effectivity_id AND
UE.Unit_effectivity_id = p_sr_ue_id AND
UE.manually_planned_flag = 'Y' AND
UE.cs_incident_id IS NOT NULL AND
NOT EXISTS (SELECT
'X'
FROM
AHL_UE_RELATIONSHIPS UER
WHERE
UER.related_ue_id = UE.Unit_effectivity_id OR
UER.ue_id = UE.Unit_effectivity_id);
SELECT
CSIA.summary,
CSIT.name -- incident type name.
FROM
CS_INCIDENTS_ALL CSIA,
CS_INCIDENT_TYPES_VL CSIT,
AHL_UNIT_EFFECTIVITIES_B UE
WHERE
UE.unit_effectivity_id = p_sr_ue_id AND
CSIA.incident_id = ue.cs_incident_id AND
CSIT.incident_type_id = CSIA.incident_type_id;
L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Handle_Summary_Update';
'Entering the procedure Handle_Summary_Update ..'
);
'Exiting the procedure Handle_Summary_Update ..'
);
END Handle_Summary_Update;