[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT inventory_item_id,
inventory_item_id,
inv_master_organization_id
INTO p_x_nonroutine_rec.inventory_item_id,
l_service_request_rec.inventory_item_id,
l_service_request_rec.inventory_org_id
FROM csi_item_instances
WHERE instance_id = p_x_nonroutine_rec.instance_id;
SELECT unit_effectivity_id, unit_effectivity_id, object_version_number
INTO l_unit_effectivity_id, p_x_nonroutine_rec.unit_effectivity_id, p_x_nonroutine_rec.ue_object_version_number
FROM ahl_unit_effectivities_b
WHERE object_type = 'SR' and
cs_incident_id = p_x_nonroutine_rec.incident_id;
UPDATE ahl_unit_effectivities_b
SET log_series_code = p_x_nonroutine_rec.log_series_code,
log_series_number = p_x_nonroutine_rec.log_series_number,
flight_number = p_x_nonroutine_rec.flight_number,
-- clear_station_org_id = p_x_nonroutine_rec.clear_station_org_id,
-- clear_station_dept_id = p_x_nonroutine_rec.clear_station_dept_id,
mel_cdl_type_code = p_x_nonroutine_rec.mel_cdl_type_code,
position_path_id = p_x_nonroutine_rec.position_path_id,
ata_code = p_x_nonroutine_rec.ata_code,
unit_config_header_id = p_x_nonroutine_rec.unit_config_header_id,
--Added by debadey for NR Enhancement for Quality Integration
qa_inspection_type = p_x_nonroutine_rec.qa_inspection_type
WHERE unit_effectivity_id = l_unit_effectivity_id;
'UE details updated for MEL/CDL qualification'
);
SELECT repcat.repair_time
INTO l_ata_rep_time
FROM ahl_mel_cdl_ata_sequences ata, ahl_repair_categories repcat
WHERE ata.repair_category_id = repcat.repair_category_id and ata.mel_cdl_ata_sequence_id = l_ata_sequence_id;
AHL_UNIT_DEFERRALS_PKG.INSERT_ROW
(
X_ROWID => l_row_id,
X_UNIT_DEFERRAL_ID => l_deferral_id,
X_ATTRIBUTE14 => null,
X_ATTRIBUTE15 => 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_AFFECT_DUE_CALC_FLAG => 'N',
X_DEFER_REASON_CODE => null,
X_USER_DEFERRAL_TYPE => null,
X_DEFERRAL_EFFECTIVE_ON => l_service_request_rec.incident_occurred_date,
X_UNIT_EFFECTIVITY_ID => l_unit_effectivity_id,
X_UNIT_DEFERRAL_TYPE => p_x_nonroutine_rec.mel_cdl_type_code,
X_SET_DUE_DATE => null,
X_APPROVAL_STATUS_CODE => 'DRAFT',
X_SKIP_MR_FLAG => null,
X_CANCEL_FLAG => null,
X_OBJECT_VERSION_NUMBER => 1,
X_ATA_SEQUENCE_ID => l_ata_sequence_id,
X_REMARKS => null,
X_APPROVER_NOTES => 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
);
'Insert unit_deferral ['||l_deferral_id||'] with relevant MEL/CDL qualification information'
);
PROCEDURE Update_SR
(
-- Standard IN params
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_FALSE,
p_module_type IN VARCHAR2 := NULL,
-- Standard OUT params
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
-- Procedure IN, OUT, IN/OUT params
p_x_nonroutine_rec IN OUT NOCOPY NonRoutine_Rec_Type
)
IS
-- Declare local variables
l_api_name CONSTANT VARCHAR2(30) := 'Update_SR';
SELECT unit_effectivity_id, object_version_number
FROM ahl_unit_effectivities_b
WHERE object_type = 'SR' and
cs_incident_id = c_incident_id and
nvl(status_code, 'X') <> 'DEFERRED';
SELECT *
FROM ahl_unit_deferrals_vl
WHERE unit_effectivity_id = c_unit_effectivity_id AND
unit_deferral_type IN ('MEL','CDL');
SELECT contact_type, party_id
FROM CS_HZ_SR_CONTACT_POINTS
WHERE incident_id = c_incident_id
AND primary_flag = 'Y';
SELECT object_version_number
FROM CS_INCIDENTS
WHERE incident_id = c_incident_id;
SAVEPOINT Update_SR_SP;
SELECT incident_date, incident_occurred_date, incident_occurred_date
INTO l_service_request_rec.request_date, l_service_request_rec.incident_occurred_date, p_x_nonroutine_rec.incident_date
FROM cs_incidents_all_b
WHERE incident_id = p_x_nonroutine_rec.incident_id;
SELECT p_x_nonroutine_rec.inventory_item_id,
inv_master_organization_id
INTO l_service_request_rec.inventory_item_id,
l_service_request_rec.inventory_org_id
FROM csi_item_instances
WHERE instance_id = p_x_nonroutine_rec.instance_id;
CS_SERVICEREQUEST_PUB.Update_ServiceRequest
(
p_api_version => 3.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_request_id => p_x_nonroutine_rec.incident_id,
p_request_number => NULL,
p_audit_comments => NULL,
p_object_version_number => p_x_nonroutine_rec.incident_object_version_number,
p_resp_appl_id => fnd_global.resp_appl_id,
p_resp_id => fnd_global.resp_id,
p_last_updated_by => fnd_global.user_id,
p_last_update_login => fnd_global.login_id,
p_last_update_date => sysdate,
p_service_request_rec => l_service_request_rec,
p_notes => l_notes_table,
p_contacts => l_contacts_table,
p_called_by_workflow => NULL,
p_workflow_process_id => NULL,
x_workflow_process_id => l_workflow_process_id,
x_interaction_id => l_interaction_id
);
'Call to CS_SERVICEREQUEST_PUB.Update_ServiceRequest failed...'
);
SELECT object_version_number
INTO p_x_nonroutine_rec.ue_object_version_number
FROM ahl_unit_effectivities_b
WHERE unit_effectivity_id = p_x_nonroutine_rec.unit_effectivity_id;
'UE ['||l_unit_effectivity_id||'] is updated for non-routine ['||p_x_nonroutine_rec.incident_id||']'
);
UPDATE ahl_unit_effectivities_b
SET log_series_code = p_x_nonroutine_rec.log_series_code,
log_series_number = p_x_nonroutine_rec.log_series_number,
flight_number = p_x_nonroutine_rec.flight_number,
-- clear_station_org_id = p_x_nonroutine_rec.clear_station_org_id,
-- clear_station_dept_id = p_x_nonroutine_rec.clear_station_dept_id,
unit_config_header_id = p_x_nonroutine_rec.unit_config_header_id,
--debadey: changes for NR Enhancement for Quality Integration
qa_inspection_type = p_x_nonroutine_rec.qa_inspection_type
WHERE unit_effectivity_id = l_unit_effectivity_id;
UPDATE ahl_unit_effectivities_b
SET mel_cdl_type_code = p_x_nonroutine_rec.mel_cdl_type_code,
position_path_id = p_x_nonroutine_rec.position_path_id,
ata_code = p_x_nonroutine_rec.ata_code
WHERE unit_effectivity_id = l_unit_effectivity_id;
'UE details updated for MEL/CDL qualification'
);
SELECT repcat.repair_time
INTO l_ata_rep_time
FROM ahl_mel_cdl_ata_sequences ata, ahl_repair_categories repcat
WHERE ata.repair_category_id = repcat.repair_category_id and ata.mel_cdl_ata_sequence_id = l_ata_sequence_id;
AHL_UNIT_DEFERRALS_PKG.UPDATE_ROW
(
X_UNIT_DEFERRAL_ID => l_deferral_rec.unit_deferral_id,
X_ATTRIBUTE14 => l_deferral_rec.attribute14,
X_ATTRIBUTE15 => l_deferral_rec.attribute15,
X_ATTRIBUTE_CATEGORY => l_deferral_rec.attribute_category,
X_ATTRIBUTE1 => l_deferral_rec.attribute1,
X_ATTRIBUTE2 => l_deferral_rec.attribute2,
X_ATTRIBUTE3 => l_deferral_rec.attribute3,
X_ATTRIBUTE4 => l_deferral_rec.attribute4,
X_ATTRIBUTE5 => l_deferral_rec.attribute5,
X_ATTRIBUTE6 => l_deferral_rec.attribute6,
X_ATTRIBUTE7 => l_deferral_rec.attribute7,
X_ATTRIBUTE8 => l_deferral_rec.attribute8,
X_ATTRIBUTE9 => l_deferral_rec.attribute9,
X_ATTRIBUTE10 => l_deferral_rec.attribute10,
X_ATTRIBUTE11 => l_deferral_rec.attribute11,
X_ATTRIBUTE12 => l_deferral_rec.attribute12,
X_ATTRIBUTE13 => l_deferral_rec.attribute13,
X_AFFECT_DUE_CALC_FLAG => 'N',
X_DEFER_REASON_CODE => l_deferral_rec.defer_reason_code,
X_DEFERRAL_EFFECTIVE_ON => l_deferral_rec.deferral_effective_on,
X_UNIT_EFFECTIVITY_ID => l_unit_effectivity_id,
X_UNIT_DEFERRAL_TYPE => p_x_nonroutine_rec.mel_cdl_type_code,
X_SET_DUE_DATE => l_deferral_rec.set_due_date,
X_APPROVAL_STATUS_CODE => 'DRAFT',
X_SKIP_MR_FLAG => l_deferral_rec.skip_mr_flag,
X_CANCEL_FLAG => l_deferral_rec.cancel_flag,
X_OBJECT_VERSION_NUMBER => l_deferral_rec.object_version_number + 1,
X_ATA_SEQUENCE_ID => l_ata_sequence_id,
X_REMARKS => l_deferral_rec.remarks,
X_APPROVER_NOTES => l_deferral_rec.approver_notes,
X_USER_DEFERRAL_TYPE => null,
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => fnd_global.user_id,
X_LAST_UPDATE_LOGIN => fnd_global.login_id
);
'Updated unit_deferral ['||l_deferral_rec.unit_deferral_id||'] with relevant MEL/CDL qualification information'
);
AHL_UNIT_DEFERRALS_PKG.INSERT_ROW
(
X_ROWID => l_row_id,
X_UNIT_DEFERRAL_ID => l_deferral_id,
X_ATTRIBUTE14 => null,
X_ATTRIBUTE15 => 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_AFFECT_DUE_CALC_FLAG => 'N',
X_DEFER_REASON_CODE => null,
X_DEFERRAL_EFFECTIVE_ON => l_service_request_rec.incident_occurred_date,
X_UNIT_EFFECTIVITY_ID => l_unit_effectivity_id,
X_UNIT_DEFERRAL_TYPE => p_x_nonroutine_rec.mel_cdl_type_code,
X_SET_DUE_DATE => null,
X_APPROVAL_STATUS_CODE => 'DRAFT',
X_SKIP_MR_FLAG => null,
X_CANCEL_FLAG => null,
X_OBJECT_VERSION_NUMBER => 1,
X_ATA_SEQUENCE_ID => l_ata_sequence_id,
X_REMARKS => null,
X_APPROVER_NOTES => null,
X_USER_DEFERRAL_TYPE => 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
);
'Insert unit_deferral ['||l_deferral_id||'] with relevant MEL/CDL qualification information'
);
DELETE FROM ahl_unit_deferrals_tl
WHERE unit_deferral_id IN
(
SELECT unit_deferral_id
FROM ahl_unit_deferrals_b
WHERE unit_effectivity_id = l_unit_effectivity_id
);
DELETE FROM ahl_unit_deferrals_b
WHERE unit_effectivity_id = l_unit_effectivity_id;
'Deleted unit_deferral ['||l_deferral_rec.unit_deferral_id||']'
);
Rollback to Update_SR_SP;
Rollback to Update_SR_SP;
Rollback to Update_SR_SP;
p_procedure_name => 'Update_SR',
p_error_text => SUBSTR(SQLERRM,1,240)
);
END Update_SR;
select unit_deferral_id, object_version_number, unit_deferral_type,
approval_status_code, ata_sequence_id, deferral_effective_on
from ahl_unit_deferrals_b
where unit_effectivity_id = p_ue_id and
unit_deferral_type in ('MEL', 'CDL')
for update of object_version_number;
select unit_effectivity_id, object_version_number, status_code,
cs_incident_id, mel_cdl_type_code, csi_item_instance_id,
unit_config_header_id, log_series_code, log_series_number
from ahl_unit_effectivities_b
where unit_effectivity_id = p_ue_id
and object_type = 'SR'
and (status_code IS NULL or status_code = 'INIT_DUE')
for update of object_version_number;
select 'x'
from ahl_visit_tasks_b vts, ahl_visits_b vst
where vst.visit_id = vts.visit_id
and NVL(vst.status_code,'x') IN ('PARTIALLY RELEASED','RELEASED')
and NVL(vts.status_code,'x') = 'RELEASED'
and vts.unit_effectivity_id = p_ue_id ;
select 'x'
from ahl_unit_deferrals_b
where unit_effectivity_id = p_ue_id
and unit_deferral_type = 'DEFERRAL'
and approval_status_code = 'DEFERRAL_PENDING';
select 'x'
from ahl_unit_deferrals_b
where unit_effectivity_id IN (select related_ue_id
from ahl_ue_relationships
start with ue_id = p_ue_id
connect by PRIOR related_ue_id = ue_id)
and unit_deferral_type = 'DEFERRAL'
and approval_status_code = 'DEFERRAL_PENDING';
select seq.INSTALLED_NUMBER, seq.DISPATCH_NUMBER, nvl(rc.repair_time,0)
from ahl_mel_cdl_ata_sequences seq, ahl_mel_cdl_headers hdr,
ahl_repair_categories rc
where seq.mel_cdl_header_id = hdr.mel_cdl_header_id
and seq.mel_cdl_ata_sequence_id = p_ata_sequence_id
and nvl(hdr.expired_date, sysdate+1) > sysdate
and seq.repair_category_id = rc.repair_category_id;
select count(ue.cs_incident_id)
from AHL_UNIT_EFFECTIVITIES_B UE, CS_INCIDENTS_ALL_B CS,
CS_INCIDENT_STATUSES_B STATUS, AHL_UNIT_DEFERRALS_B UDF,
AHL_MEL_CDL_ATA_SEQUENCES SEQ
where SEQ.MEL_CDL_ATA_SEQUENCE_ID = UDF.ATA_SEQUENCE_ID
AND UDF.UNIT_EFFECTIVITY_ID = UE.UNIT_EFFECTIVITY_ID
AND UE.CS_INCIDENT_ID = CS.INCIDENT_ID
AND CS.INCIDENT_STATUS_ID = STATUS.INCIDENT_STATUS_ID
AND NVL(STATUS.CLOSE_FLAG, 'N') = 'N'
AND SEQ.mel_cdl_ata_sequence_id = p_ata_sequence_id
AND UE.unit_config_header_id = p_unit_config_header_id
AND (UE.status_code IS NULL OR UE.status_code = 'INIT_DUE')
AND UDF.approval_status_code IN ('DEFERRED','DEFERRAL_PENDING');
select related_ata_sequence_id ata_sequence_id
from ahl_mel_cdl_relationships
where ata_sequence_id = p_ata_sequence_id
UNION ALL
select ata_sequence_id
from ahl_mel_cdl_relationships
where related_ata_sequence_id = p_ata_sequence_id;
select EXPECTED_RESOLUTION_DATE
from cs_incidents_all_b cs
where cs.incident_id = p_cs_incident_id;
SELECT UE.unit_effectivity_id
FROM
AHL_UNIT_EFFECTIVITIES_APP_V UE, CS_INCIDENTS_ALL_B CS,
CS_INCIDENT_STATUSES_B STATUS, AHL_UNIT_DEFERRALS_B UDF--,
--AHL_MEL_CDL_ATA_SEQUENCES SEQ
WHERE
--SEQ.MEL_CDL_ATA_SEQUENCE_ID = UDF.ATA_SEQUENCE_ID
--AND
UDF.UNIT_EFFECTIVITY_ID = UE.UNIT_EFFECTIVITY_ID
AND UDF.ATA_SEQUENCE_ID = p_mel_cdl_header_id
AND UE.CS_INCIDENT_ID = CS.INCIDENT_ID
AND CS.INCIDENT_STATUS_ID = STATUS.INCIDENT_STATUS_ID
AND NVL(STATUS.CLOSE_FLAG, 'N') = 'N'
--AND SEQ.MEL_CDL_HEADER_ID = p_mel_cdl_header_id
AND (UE.STATUS_CODE IS NULL OR UE.STATUS_CODE = 'INIT_DUE')
AND UDF.APPROVAL_STATUS_CODE IN ('DRAFT','DEFERRAL_PENDING','DEFERRAL_REJECTED')
AND ROWNUM = 1;
SELECT UE.unit_effectivity_id
FROM
AHL_UNIT_EFFECTIVITIES_APP_V UE, CS_INCIDENTS_ALL_B CS,
CS_INCIDENT_STATUSES_B STATUS, AHL_UNIT_DEFERRALS_B UDF,
AHL_MEL_CDL_ATA_SEQUENCES SEQ, AHL_MEL_CDL_HEADERS HDR
WHERE
SEQ.MEL_CDL_ATA_SEQUENCE_ID = UDF.ATA_SEQUENCE_ID
AND UDF.UNIT_EFFECTIVITY_ID = UE.UNIT_EFFECTIVITY_ID
AND UE.CS_INCIDENT_ID = CS.INCIDENT_ID
AND CS.INCIDENT_STATUS_ID = STATUS.INCIDENT_STATUS_ID
AND NVL(STATUS.CLOSE_FLAG, 'N') = 'N'
AND (UE.STATUS_CODE IS NULL OR UE.STATUS_CODE = 'INIT_DUE')
AND UDF.APPROVAL_STATUS_CODE IN ('DRAFT','DEFERRAL_PENDING','DEFERRAL_REJECTED')
AND SEQ.MEL_CDL_HEADER_ID = HDR.MEL_CDL_HEADER_ID
AND HDR.PC_NODE_ID IN
-- priyan : bug #5302804
(
-- traverse up the branch, for MEL/CDL, from the PC node being deleted
SELECT PC_NODE_ID
FROM AHL_PC_NODES_B
CONNECT BY PRIOR PARENT_NODE_ID= PC_NODE_ID
START WITH PC_NODE_ID = p_pc_node_id
UNION
-- traverse down the tree, for MEL/CDL, from the PC node being deleted
SELECT PC_NODE_ID
FROM AHL_PC_NODES_B
CONNECT BY PRIOR PC_NODE_ID = PARENT_NODE_ID
START WITH PC_NODE_ID = p_pc_node_id
)
AND ROWNUM = 1;
SELECT ahl_util_uc_pkg.get_uc_header_id(csi_item_instance_id),
mel_cdl_type_code
FROM ahl_unit_effectivities_b
WHERE unit_effectivity_id = p_unit_effectivity_id;
SELECT * FROM (
select mel.mel_cdl_header_id, mel.version_number, pcn.pc_node_id
from ahl_pc_nodes_b pcn,
ahl_mel_cdl_headers mel
where pcn.pc_node_id = mel.pc_node_id (+) and
--where pcn.pc_node_id = mel.pc_node_id and -- fix for bug# 9775781. Need outer join else nodes with no MEL definition will not be skipped.
mel.mel_cdl_type_code = l_mel_cdl_type_code and
mel.status_code = 'COMPLETE' and
trunc(sysdate) between trunc(revision_date) and trunc(nvl(expired_date, sysdate + 1))
connect by pcn.pc_node_id = prior pcn.parent_node_id
start with pcn.pc_node_id in
(
select node.pc_node_id
from ahl_pc_associations assos,
ahl_pc_nodes_b node,
ahl_pc_headers_b hdr
where assos.unit_item_id = l_unit_config_id and
assos.pc_node_id = node.pc_node_id and
node.pc_header_id = hdr.pc_header_id and
hdr.status = 'COMPLETE' and
hdr.primary_flag = 'Y' and
hdr.association_type_flag = 'U'
)
--order by pcn.pc_node_id desc, mel.version_number desc;
SELECT unit_deferral_type, approval_status_code
FROM ahl_unit_deferrals_b
WHERE unit_effectivity_id = p_unit_effectivity_id AND
unit_deferral_type IN ('MEL', 'CDL');
SELECT approval_status_code, unit_deferral_type
FROM ahl_unit_deferrals_b
WHERE unit_effectivity_id = p_unit_effectivity_id;
SELECT 'x'
FROM ahl_applicable_instances appl,
csi_ii_relationships cii,
ahl_mc_relationships mch,
ahl_unit_config_headers uch
WHERE appl.position_id = p_x_nonroutine_rec.position_path_id and
(appl.csi_item_instance_id = cii.subject_id or appl.csi_item_instance_id = cii.object_id) and
to_number(cii.position_reference) = mch.relationship_id and
mch.mc_header_id = uch.master_config_id and
uch.unit_config_header_id = p_x_nonroutine_rec.unit_config_header_id
UNION ALL
SELECT 'x'
FROM ahl_applicable_instances appl,
ahl_unit_config_headers uch
WHERE appl.position_id = p_x_nonroutine_rec.position_path_id and
appl.csi_item_instance_id = uch.csi_item_instance_id and
uch.unit_config_header_id = p_x_nonroutine_rec.unit_config_header_id;
SELECT log_series_code, log_series_number, position_path_id, mel_cdl_type_code, ata_code
FROM ahl_unit_effectivities_b
WHERE unit_effectivity_id = p_unit_effectivity_id;
SELECT 'x'
FROM ahl_unit_effectivities_b
WHERE log_series_number = p_x_nonroutine_rec.log_series_number and
log_series_code = p_x_nonroutine_rec.log_series_code and
unit_effectivity_id <> nvl(p_unit_effectivity_id, unit_effectivity_id) and
unit_config_header_id = nvl(p_x_nonroutine_rec.unit_config_header_id, unit_config_header_id ) and --pdoki added for ER 9204858
nvl(status_code, 'X') <> 'DEFERRED';
select ORG.organization_id
from inv_organization_name_v ORG,
mtl_parameters MP
where MP.organization_id = ORG.organization_id AND
MP.EAM_enabled_flag = 'Y' AND
ORG.organization_code = p_org_code;
select 'x'
from inv_organization_name_v ORG,
mtl_parameters MP
where MP.organization_id = ORG.organization_id AND
MP.EAM_enabled_flag = 'Y' AND
ORG.organization_id = p_org_id;
select department_id
from bom_departments
where organization_id = p_org_id and
department_code = p_dept_code;
select 'x'
from bom_departments
where organization_id = p_org_id and
department_id = p_dept_id;
SELECT 'x'
FROM ahl_unit_deferrals_b
WHERE unit_effectivity_id = p_unit_effectivity_id AND
unit_deferral_type IN ('MEL', 'CDL') AND
approval_status_code IN ('DEFERRAL_PENDING', 'DEFERRED');
SELECT 'x'
FROM ahl_unit_deferrals_b
WHERE unit_effectivity_id = p_unit_effectivity_id AND
unit_deferral_type IN ('MEL', 'CDL');
SELECT ahl_log_series_s.NEXTVAL INTO p_x_nonroutine_rec.log_series_number FROM DUAL;
SELECT ahl_util_uc_pkg.get_uc_header_id(csi_item_instance_id) unit_config_id,
ahl_util_uc_pkg.get_unit_name(csi_item_instance_id) unit_config_name,
mel_cdl_type_code,
position_path_id,
ata_code
FROM ahl_unit_effectivities_b
WHERE unit_effectivity_id = p_unit_effectivity_id;
SELECT mel_cdl_ata_sequence_id
FROM ahl_mel_cdl_ata_sequences
WHERE mel_cdl_header_id = p_mel_cdl_header_id and
ata_code = p_ata_code;
SELECT mch.ata_code
FROM ahl_applicable_instances appl,
csi_ii_relationships cii,
ahl_mc_relationships mch,
ahl_unit_config_headers uch
WHERE appl.position_id = p_position_path_id and
appl.csi_item_instance_id = cii.subject_id and
cii.position_reference = mch.relationship_id and
mch.mc_header_id = uch.master_config_id and
uch.unit_config_header_id = p_unit_config_id
UNION ALL
SELECT mch.ata_code
FROM ahl_applicable_instances appl,
ahl_mc_relationships mch,
ahl_unit_config_headers uch
WHERE appl.position_id = p_position_path_id and
appl.csi_item_instance_id = uch.csi_item_instance_id and
mch.mc_header_id = uch.master_config_id and
uch.unit_config_header_id = p_unit_config_id;
UPDATE ahl_unit_effectivities_b
SET ata_code = l_ata_for_position
WHERE unit_effectivity_id = p_unit_effectivity_id;
SELECT incident_number, incident_date
FROM cs_incidents_all_b
WHERE incident_id = p_x_nonroutine_rec.incident_id AND
object_version_number = p_x_nonroutine_rec.incident_object_version_number;
SELECT 'x'
FROM cs_incident_severities_vl csv,
mfg_lookups mfl
WHERE mfl.lookup_code = csv.incident_severity_id AND
csv.incident_severity_id = p_x_nonroutine_rec.severity_id AND
csv.incident_subtype = 'INC' AND
mfl.lookup_type = 'WIP_EAM_ACTIVITY_PRIORITY' AND
trunc(sysdate) between trunc(nvl(csv.start_date_active,sysdate)) AND trunc(nvl(csv.end_date_active,sysdate));
SELECT sr_incident_number
FROM AHL_SR_NON_ROUTINES_V
WHERE UE_CSI_INSTANCE_ID = c_instance_id
AND nvl2(fnd_profile.VALUE('AHL_REMOVAL_NON_ROUTINE'), SR_INCIDENT_TYPE_ID, 1) = nvl(fnd_profile.VALUE('AHL_REMOVAL_NON_ROUTINE'),2)
AND SR_INCIDENT_STATUS NOT IN ('Canceled', 'Cancelled by User', 'Closed');
SELECT sr_incident_number
FROM AHL_SR_NON_ROUTINES_V
WHERE UE_CSI_INSTANCE_ID = c_instance_id
AND nvl2(fnd_profile.VALUE('AHL_REMOVAL_NON_ROUTINE'), SR_INCIDENT_TYPE_ID, 1) = nvl(fnd_profile.VALUE('AHL_REMOVAL_NON_ROUTINE'),2)
AND SR_INCIDENT_STATUS NOT IN ('Canceled', 'Cancelled by User', 'Closed')
AND SR_INCIDENT_ID NOT IN (c_sr_incident_id);
SELECT csi.instance_id
INTO p_x_nonroutine_rec.instance_id
FROM csi_item_instances csi
WHERE trunc(nvl(csi.active_start_date, sysdate)) <= trunc(sysdate) and
trunc(nvl(csi.active_end_date, sysdate+1)) > trunc(sysdate) and
csi.instance_number = p_x_nonroutine_rec.instance_number;
SELECT incident_type_id
INTO p_x_nonroutine_rec.type_id
FROM cs_incident_types_vl
WHERE name = p_x_nonroutine_rec.type_name and
cmro_flag = 'Y' and
incident_subtype = 'INC' and
trunc(sysdate) between trunc(nvl(start_date_active, sysdate)) and trunc(nvl(end_date_active, sysdate));
SELECT incident_status_id
INTO p_x_nonroutine_rec.status_id
FROM cs_incident_statuses_vl
WHERE name = p_x_nonroutine_rec.status_name and
trunc(sysdate) between trunc(nvl(start_date_active, sysdate)) and trunc(nvl(end_date_active, sysdate));
SELECT incident_severity_id
INTO p_x_nonroutine_rec.severity_id
FROM cs_incident_severities_vl
WHERE name = p_x_nonroutine_rec.severity_name and
trunc(sysdate) between trunc(nvl(start_date_active, sysdate)) and trunc(nvl(end_date_active, sysdate));
SELECT NVL(OWNER_PARTY_ID, -1)
INTO l_instance_owner_id
FROM csi_item_instances
WHERE instance_id = p_x_nonroutine_rec.instance_id;
SELECT party_id, party_type
INTO p_x_nonroutine_rec.customer_id, p_x_nonroutine_rec.customer_type
FROM hz_parties
WHERE status = 'A' AND
party_type IN ('PERSON', 'ORGANIZATION') AND
party_name = p_x_nonroutine_rec.customer_name AND
party_id = NVL(p_x_nonroutine_rec.customer_id, party_id);
SELECT party_name, party_type
INTO p_x_nonroutine_rec.customer_name, p_x_nonroutine_rec.customer_type
FROM hz_parties
WHERE status = 'A' AND
party_type IN ('PERSON', 'ORGANIZATION') AND
party_id = p_x_nonroutine_rec.customer_id;
SELECT party_id
INTO p_x_nonroutine_rec.contact_id
FROM hz_parties
WHERE status = 'A' AND
party_name = p_x_nonroutine_rec.contact_name;
SELECT person_id
INTO p_x_nonroutine_rec.contact_id
FROM per_people_x
WHERE full_name = p_x_nonroutine_rec.contact_name and
person_id = nvl(p_x_nonroutine_rec.contact_id,person_id);
select incident_severity_id, customer_id, caller_type,
nvl(incident_occurred_date,incident_date) incident_occurred_date,
expected_resolution_date , object_version_number, incident_number
from cs_incidents_all_b
where incident_id = p_cs_incident_id;
Select wo.workorder_name, tsk.visit_id,
wdj.scheduled_start_date, vst.close_date_time
from ahl_workorders wo, ahl_visit_tasks_b tsk, wip_discrete_jobs wdj,
ahl_visits_b vst
where wo.visit_task_id = tsk.visit_task_id
and tsk.visit_id = vst.visit_id
and wdj.wip_entity_id = wo.wip_entity_id
and tsk.unit_effectivity_id = p_ue_id
and tsk.task_type_code IN ('SUMMARY','UNASSOCIATED');
select party_id, contact_type
from cs_sr_contact_points_v
where incident_id = p_cs_incident_id
and primary_flag = 'Y';
select cs.object_version_number, ue.unit_effectivity_id
from ahl_unit_effectivities_b UE, cs_incidents_all_b cs
where ue.cs_incident_id = cs.incident_id
and cs.incident_id = p_cs_incident_id;
SELECT INCIDENT_TYPE_ID
FROM cs_incident_types_vl
where INCIDENT_SUBTYPE = 'INC'
AND CMRO_FLAG = 'Y'
AND incident_type_id=fnd_profile.value('AHL_MCL_M_AND_O_SR_TYPE')
AND trunc(sysdate) between trunc(nvl(start_date_active,sysdate))
AND trunc(nvl(end_date_active,sysdate));
SELECT mop.mr_header_id
FROM ahl_mel_cdl_mo_procedures mop,
ahl_mr_headers_b mrh
WHERE mop.ata_sequence_id = p_ata_sequence_id
AND mrh.mr_header_id = mop.mr_header_id
AND mrh.mr_status_code = 'COMPLETE'
AND trunc(sysdate) between trunc(mrh.effective_from) and trunc(nvl(mrh.effective_to,sysdate+1))
AND mrh.version_number IN
( SELECT MAX(version_number)
FROM ahl_mr_headers_b
WHERE title = mrh.title
AND TRUNC(SYSDATE)
BETWEEN TRUNC(effective_from)
AND TRUNC(NVL(effective_to,sysdate+1))
AND mr_status_code = 'COMPLETE'
AND program_type_code = 'MO_PROC'
);
SELECT A.inventory_item_id,
vst.organization_id
FROM AHL_VISIT_TASKS_B A, ahl_visits_b vst
WHERE a.visit_id = vst.visit_id
and A.unit_effectivity_id = p_ue_id
AND A.task_type_code IN ('SUMMARY','UNASSOCIATED')
AND rownum = 1;
select repair_time, sr_urgency_id
from ahl_mel_cdl_ata_sequences seq, ahl_repair_categories rep
where mel_cdl_ata_sequence_id = p_ata_sequence_id
and seq.repair_category_id = rep.repair_category_id;
SELECT unit_deferral_id,
ata_sequence_id,
unit_deferral_type,
defer_reason_code,
skip_mr_flag,
cancel_flag,
affect_due_calc_flag,
set_due_date,
deferral_effective_on,
remarks,approver_notes, user_deferral_type,
attribute_category, attribute1,
attribute2, attribute3, attribute4, attribute5, attribute6, attribute7,
attribute8, attribute9, attribute10, attribute11, attribute12,
attribute13, attribute14, attribute15
FROM ahl_unit_deferrals_vl
WHERE unit_deferral_id = p_deferral_id;
SELECT unit_effectivity_id
FROM ahl_unit_effectivities_b
WHERE cs_incident_id = p_cs_incident_id
AND status_code IS NULL;
'Starting SR Update');
CS_SERVICEREQUEST_PUB.Update_ServiceRequest
(
p_api_version => 3.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_request_id => p_cs_incident_id,
p_request_number => NULL,
p_audit_comments => NULL,
p_object_version_number => l_inc_rec.object_version_number,
p_resp_appl_id => fnd_global.resp_appl_id,
p_resp_id => fnd_global.resp_id,
p_last_updated_by => fnd_global.user_id,
p_last_update_login => fnd_global.login_id,
p_last_update_date => sysdate,
p_service_request_rec => l_service_request_rec,
p_notes => l_notes_table,
p_contacts => l_contacts_table,
p_called_by_workflow => NULL,
p_workflow_process_id => NULL,
x_workflow_process_id => l_workflow_process_id,
x_interaction_id => l_interaction_id
);
'After call to Update Service Request :return_status:' || l_return_status);
AHL_UNIT_DEFERRALS_PKG.insert_row(
x_rowid => l_rowid,
x_unit_deferral_id => l_unit_deferral_id,
x_ata_sequence_id => l_deferral_rec.ata_sequence_id,
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 => l_new_cs_ue_id,
x_unit_deferral_type => l_deferral_rec.unit_deferral_type,
x_set_due_date => l_deferral_rec.set_due_date,
x_deferral_effective_on => l_deferral_rec.deferral_effective_on,
x_approval_status_code => 'DEFERRED',
x_defer_reason_code => l_deferral_rec.defer_reason_code,
x_affect_due_calc_flag => l_deferral_rec.affect_due_calc_flag,
x_skip_mr_flag => l_deferral_rec.skip_mr_flag,
x_cancel_flag => l_deferral_rec.cancel_flag,
x_remarks => l_deferral_rec.remarks,
x_approver_notes => l_deferral_rec.approver_notes,
x_user_deferral_type => l_deferral_rec.user_deferral_type,
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
);
'After insert into ahl_unit_deferrals table: deferral ID:' || l_unit_deferral_id);