[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
G_JOB_DELETED CONSTANT VARCHAR2(30) := '22';
PROCEDURE validate_deferral_updates(
p_df_header_rec IN AHL_PRD_DF_PVT.df_header_rec_type,
x_warning_msg_data OUT NOCOPY VARCHAR2);
FUNCTION isValidStatusUpdate(
operation_code VARCHAR2,
status_code VARCHAR2)RETURN BOOLEAN;
p_x_df_header_rec.operation_flag IN (G_OP_CREATE,G_OP_UPDATE))) THEN
process_df_schedules(
p_df_header_rec => p_x_df_header_rec,
p_x_df_schedules_tbl => p_x_df_schedules_tbl
);
p_x_df_header_rec.operation_flag IN (G_OP_CREATE,G_OP_UPDATE))) THEN
validate_deferral_updates(
p_df_header_rec => p_x_df_header_rec,
x_warning_msg_data => l_warning_msg_data
);
SELECT unit_deferral_id, object_version_number
from ahl_unit_deferrals_b
WHERE unit_deferral_type = 'DEFERRAL'
AND unit_effectivity_id = p_unit_effectivity_id;
* in the cursor select of "unit_effectivity_info_csr".
* Here onwards in this program we dont have to worry about any app usage code related issues.
*/
CURSOR unit_effectivity_info_csr(p_unit_effectivity_id IN NUMBER) IS
SELECT mr_header_id,cs_incident_id,repetitive_mr_flag,orig_deferral_ue_id,ue_status_code,
def_status_code,manually_planned_flag
FROM ahl_ue_deferral_details_v
WHERE unit_effectivity_id = p_unit_effectivity_id
AND APPLICATION_USG_CODE = RTRIM(LTRIM(FND_PROFILE.VALUE('AHL_APPLN_USAGE'))) ;
SELECT 'x' from csi_cp_counters_v CP, AHL_MR_INTERVALS_V MRI,AHL_MR_EFFECTIVITIES MRE, AHL_UNIT_EFFECTIVITIES_B UE
WHERE CP.customer_product_id = UE.csi_item_instance_id
AND CP.counter_name = MRI.counter_name
AND MRI.reset_value IS NOT NULL
AND MRI.mr_effectivity_id = MRE.mr_effectivity_id
AND MRE.mr_header_id = UE.mr_header_id
AND UE.unit_effectivity_id = p_unit_effectivity_id
UNION
--SELECT 'x' from csi_cp_counters_v CP, AHL_MR_INTERVALS_V MRI, AHL_MR_EFFECTIVITIES_APP_V MRE, AHL_UNIT_EFFECTIVITIES_APP_V UE
SELECT 'x' from csi_cp_counters_v CP, AHL_MR_INTERVALS_V MRI,AHL_MR_EFFECTIVITIES MRE, AHL_UNIT_EFFECTIVITIES_B UE
WHERE CP.customer_product_id = UE.csi_item_instance_id
AND CP.counter_name = MRI.counter_name
AND MRI.reset_value IS NOT NULL
AND MRI.mr_effectivity_id = MRE.mr_effectivity_id
AND MRE.mr_header_id = UE.mr_header_id
AND UE.unit_effectivity_id IN
(
SELECT related_ue_id
FROM AHL_UE_RELATIONSHIPS
WHERE relationship_code = 'PARENT'
START WITH ue_id = p_unit_effectivity_id
CONNECT BY ue_id = PRIOR related_ue_id
);
SELECT 'x'
from csi_counter_associations ca, csi_counters_vl CP, AHL_MR_INTERVALS_V MRI,
AHL_MR_EFFECTIVITIES MRE, AHL_UNIT_EFFECTIVITIES_B UE
WHERE CA.source_object_id = UE.csi_item_instance_id
AND ca.source_object_code = 'CP'
AND CP.counter_template_name = MRI.counter_name
AND MRI.reset_value IS NOT NULL
AND MRI.mr_effectivity_id = MRE.mr_effectivity_id
AND MRE.mr_header_id = UE.mr_header_id
AND UE.unit_effectivity_id = p_unit_effectivity_id
UNION
SELECT 'x'
from csi_counter_associations ca, csi_counters_vl CP, AHL_MR_INTERVALS_V MRI,
AHL_MR_EFFECTIVITIES MRE, AHL_UNIT_EFFECTIVITIES_B UE
WHERE CA.source_object_id = UE.csi_item_instance_id
AND ca.source_object_code = 'CP'
AND CP.counter_template_name = MRI.counter_name
AND MRI.reset_value IS NOT NULL
AND MRI.mr_effectivity_id = MRE.mr_effectivity_id
AND MRE.mr_header_id = UE.mr_header_id
AND UE.unit_effectivity_id IN
(
SELECT related_ue_id
FROM AHL_UE_RELATIONSHIPS
WHERE relationship_code = 'PARENT'
START WITH ue_id = p_unit_effectivity_id
CONNECT BY ue_id = PRIOR related_ue_id
);
p_x_df_header_rec.operation_flag := G_OP_UPDATE;
IF(p_x_df_header_rec.operation_flag = G_OP_DELETE) THEN
-- delete schedules records
DELETE AHL_UNIT_THRESHOLDS WHERE UNIT_DEFERRAL_ID = p_x_df_header_rec.unit_deferral_id;
AHL_UNIT_DEFERRALS_PKG.delete_row(x_unit_deferral_id => p_x_df_header_rec.unit_deferral_id);
p_x_df_header_rec.last_updated_by := fnd_global.user_id;
p_x_df_header_rec.last_update_date := SYSDATE;
p_x_df_header_rec.last_update_login := fnd_global.user_id;
AHL_UNIT_DEFERRALS_PKG.insert_row(
x_rowid => l_rowid,
x_unit_deferral_id => p_x_df_header_rec.unit_deferral_id,
x_object_version_number => p_x_df_header_rec.object_version_number,
x_created_by => p_x_df_header_rec.created_by,
x_creation_date => p_x_df_header_rec.creation_date,
x_last_updated_by => p_x_df_header_rec.last_updated_by,
x_last_update_date => p_x_df_header_rec.last_update_date,
x_last_update_login => p_x_df_header_rec.last_update_login,
x_unit_effectivity_id => p_x_df_header_rec.unit_effectivity_id,
x_unit_deferral_type => p_x_df_header_rec.unit_deferral_type,
x_set_due_date => p_x_df_header_rec.set_due_date,
x_deferral_effective_on => p_x_df_header_rec.deferral_effective_on,
x_approval_status_code => p_x_df_header_rec.approval_status_code,
x_defer_reason_code => p_x_df_header_rec.defer_reason_code,
x_affect_due_calc_flag => p_x_df_header_rec.affect_due_calc_flag,
x_skip_mr_flag => p_x_df_header_rec.skip_mr_flag,
x_remarks => p_x_df_header_rec.remarks,
x_approver_notes => p_x_df_header_rec.approver_notes,
x_ata_sequence_id => NULL,
x_user_deferral_type => p_x_df_header_rec.user_deferral_type_code,
x_attribute_category => p_x_df_header_rec.attribute_category,
x_attribute1 => p_x_df_header_rec.attribute1,
x_attribute2 => p_x_df_header_rec.attribute2,
x_attribute3 => p_x_df_header_rec.attribute3,
x_attribute4 => p_x_df_header_rec.attribute4,
x_attribute5 => p_x_df_header_rec.attribute5,
x_attribute6 => p_x_df_header_rec.attribute6,
x_attribute7 => p_x_df_header_rec.attribute7,
x_attribute8 => p_x_df_header_rec.attribute8,
x_attribute9 => p_x_df_header_rec.attribute9,
x_attribute10 => p_x_df_header_rec.attribute10,
x_attribute11 => p_x_df_header_rec.attribute11,
x_attribute12 => p_x_df_header_rec.attribute12,
x_attribute13 => p_x_df_header_rec.attribute13,
x_attribute14 => p_x_df_header_rec.attribute14,
x_attribute15 => p_x_df_header_rec.attribute15
);
ELSIF (p_x_df_header_rec.operation_flag = G_OP_UPDATE) THEN
-- setting up object version number
p_x_df_header_rec.object_version_number := p_x_df_header_rec.object_version_number + 1;
p_x_df_header_rec.last_updated_by := fnd_global.user_id;
p_x_df_header_rec.last_update_date := SYSDATE;
p_x_df_header_rec.last_update_login := fnd_global.user_id;
DELETE AHL_UNIT_THRESHOLDS WHERE UNIT_DEFERRAL_ID = p_x_df_header_rec.unit_deferral_id;
AHL_UNIT_DEFERRALS_PKG.update_row(
x_unit_deferral_id => p_x_df_header_rec.unit_deferral_id,
x_object_version_number => p_x_df_header_rec.object_version_number,
x_last_updated_by => p_x_df_header_rec.last_updated_by,
x_last_update_date => p_x_df_header_rec.last_update_date,
x_last_update_login => p_x_df_header_rec.last_update_login,
x_unit_effectivity_id => p_x_df_header_rec.unit_effectivity_id,
x_unit_deferral_type => p_x_df_header_rec.unit_deferral_type,
x_set_due_date => p_x_df_header_rec.set_due_date,
x_deferral_effective_on => p_x_df_header_rec.deferral_effective_on,
x_approval_status_code => p_x_df_header_rec.approval_status_code,
x_defer_reason_code => p_x_df_header_rec.defer_reason_code,
x_affect_due_calc_flag => p_x_df_header_rec.affect_due_calc_flag,
x_skip_mr_flag => p_x_df_header_rec.skip_mr_flag,
x_remarks => p_x_df_header_rec.remarks,
x_approver_notes => p_x_df_header_rec.approver_notes,
x_ata_sequence_id => null,
x_user_deferral_type => p_x_df_header_rec.user_deferral_type_code,
x_attribute_category => p_x_df_header_rec.attribute_category,
x_attribute1 => p_x_df_header_rec.attribute1,
x_attribute2 => p_x_df_header_rec.attribute2,
x_attribute3 => p_x_df_header_rec.attribute3,
x_attribute4 => p_x_df_header_rec.attribute4,
x_attribute5 => p_x_df_header_rec.attribute5,
x_attribute6 => p_x_df_header_rec.attribute6,
x_attribute7 => p_x_df_header_rec.attribute7,
x_attribute8 => p_x_df_header_rec.attribute8,
x_attribute9 => p_x_df_header_rec.attribute9,
x_attribute10 => p_x_df_header_rec.attribute10,
x_attribute11 => p_x_df_header_rec.attribute11,
x_attribute12 => p_x_df_header_rec.attribute12,
x_attribute13 => p_x_df_header_rec.attribute13,
x_attribute14 => p_x_df_header_rec.attribute14,
x_attribute15 => p_x_df_header_rec.attribute15
);
SELECT 'x' from ahl_unit_deferrals_vl
WHERE unit_deferral_type = 'DEFERRAL'
AND unit_effectivity_id = p_unit_effectivity_id;
SELECT 'x' from ahl_workorder_tasks_v WO,AHL_UNIT_EFFECTIVITIES_B UE --Undid App usage related BLIND changes
WHERE WO.unit_effectivity_id = UE.unit_effectivity_id
AND NVL(UE.status_code,'x') NOT IN('ACCOMPLISHED','DEFERRED','EXCEPTION','TERMINATED','CANCELLED','MR-TERMINATE')
AND UE.unit_effectivity_id = p_unit_effectivity_id;
select 'x' from AHL_MR_HEADERS_APP_V mr, AHL_MR_HEADERS_APP_V def, ahl_unit_effectivities_b UE
where UE.unit_effectivity_id = p_unit_effectivity_id
AND def.mr_header_id = UE.mr_header_id
and def.title = mr.title
and trunc(sysdate) between trunc(mr.effective_from)
and trunc(nvl(mr.effective_to, sysdate))
and mr.version_number >= def.version_number;*/
SELECT 'x' from ahl_unit_deferrals_b
WHERE approval_status_code IN ('DRAFT','DEFERRAL_REJECTED')
AND unit_deferral_type = 'DEFERRAL'
AND unit_deferral_id = p_unit_deferral_id;
SELECT 'x'
FROM ahl_workorders WO, ahl_visits_b VS, ahl_visit_tasks_b VST, ahl_unit_effectivities_b UE
WHERE WO.master_workorder_flag = 'Y'
/* to filter out draft / deleted WOs */
AND WO.STATUS_CODE NOT IN ( '17' , '22' )
/* to check whether visit available in client's organization */
AND WO.visit_id = VS.visit_id
AND VS.ORGANIZATION_ID IN
(SELECT ORGANIZATION_ID FROM org_organization_definitions
WHERE NVL (operating_unit, mo_global.get_current_org_id())
= mo_global.get_current_org_id())
AND VST.visit_id = VS.visit_id
/* */
AND WO.visit_task_id = VST.visit_task_id
AND VST.unit_effectivity_id = UE.unit_effectivity_id
AND VST.mr_id IS NULL
AND NVL(UE.status_code,'x') NOT IN('ACCOMPLISHED','DEFERRED','EXCEPTION','TERMINATED','CANCELLED','MR-TERMINATE')
AND UE.cs_incident_id IS NOT NULL
AND UE.unit_effectivity_id = p_ue_id;
SELECT 'x'
FROM AHL_Unit_Effectivities_B UE
WHERE status_code IS NULL OR status_code = 'INIT-DUE'
AND unit_effectivity_id = p_unit_effectivity_id
AND NOT EXISTS (SELECT 'x'
FROM ahl_visit_tasks_b vts
WHERE vts.unit_effectivity_id = UE.unit_effectivity_id
AND NVL(vts.status_code,'x') IN ('PLANNED')
AND EXISTS (select 'x'
from ahl_visits_b vst, ahl_simulation_plans_b sim
where vst.simulation_plan_id = sim.simulation_plan_id(+)
and vst.visit_id = vts.visit_id
and sim.primary_plan_flag(+) = 'Y')
);
SELECT 'x'
FROM fnd_lookup_values_vl
WHERE lookup_type = 'AHL_PRD_DEFERRAL_TYPE'
AND lookup_code = p_user_defer_type
AND enabled_flag = 'Y'
AND trunc(sysdate) BETWEEN start_date_active AND nvl(end_date_active, sysdate+1);
SELECT cii.serial_number
FROM ahl_unit_effectivities_b ue, csi_item_instances cii
WHERE unit_effectivity_id in (select originator_ue_id
from ahl_ue_relationships
where related_ue_id = p_ue_id)
AND ue.csi_item_instance_id = cii.instance_id
AND cii.quantity > 1
AND ue.object_type = 'SR';
IF(p_df_header_rec.operation_flag IS NOT NULL AND p_df_header_rec.operation_flag NOT IN(G_OP_CREATE,G_OP_UPDATE,G_OP_DELETE))THEN
FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DF_INV_HD_OP_FLAG');
'invalid mr or sr status invalid for update or delete 1'
);
'invalid mr or sr status invalid for update or delete 1'
);
ELSIF (p_df_header_rec.operation_flag IN (G_OP_UPDATE,G_OP_DELETE)) THEN
-- check whether deferral record can be updated or deleted
OPEN valid_deferral_up_csr(p_df_header_rec.unit_deferral_id);
'approval status invalid for update or delete : ue_id : ' || p_df_header_rec.unit_deferral_id
);
IF (p_df_header_rec.operation_flag IN (G_OP_CREATE, G_OP_UPDATE)) THEN
/*-- check MR status now
IF(p_df_header_rec.deferral_type = G_DEFERRAL_TYPE_MR) THEN
OPEN valid_mr_csr(p_df_header_rec.unit_effectivity_id);
SELECT 'x' FROM fnd_lookup_values_vl fnd
WHERE fnd.lookup_code = p_reason_code
AND fnd.lookup_type = 'AHL_PRD_DF_REASON_TYPES';
SELECT unit_effectivity_id, 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 object_version_number= p_object_version_number
AND unit_deferral_id = p_unit_deferral_id;
IF(p_x_df_header_rec.operation_flag IS NULL OR p_x_df_header_rec.operation_flag = G_OP_UPDATE) THEN
OPEN df_header_csr(p_x_df_header_rec.unit_deferral_id, p_x_df_header_rec.object_version_number);
'approver notes can not be updated by this API'
);
SELECT CO.counter_id FROM CSI_CP_COUNTERS_V CO, AHL_UNIT_EFFECTIVITIES_B UE -- Undid App usage BLIND changes
WHERE UPPER(co.counter_name) like UPPER(p_counter_name)
AND co.customer_product_id = ue.csi_item_instance_id
AND UE.unit_effectivity_id = p_unit_effectivity_id;
IF(p_x_df_schedules_tbl(i).operation_flag NOT IN (G_OP_CREATE,G_OP_UPDATE,G_OP_DELETE)) THEN
FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DF_SCH_INV_OP');
ELSIF(p_x_df_schedules_tbl(i).operation_flag IN (G_OP_UPDATE,G_OP_DELETE)) THEN
IF(p_x_df_schedules_tbl(i).unit_threshold_id IS NULL OR
p_x_df_schedules_tbl(i).object_version_number IS NULL) THEN
FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DF_SCH_REC_KEY_MISS');
IF(G_MODULE_TYPE = 'JSP' AND p_x_df_schedules_tbl(i).operation_flag IN (G_OP_CREATE,G_OP_UPDATE))THEN
OPEN counter_id_csr(p_x_df_schedules_tbl(i).counter_name, p_df_header_rec.unit_effectivity_id);
IF(p_x_df_schedules_tbl(i).operation_flag = G_OP_DELETE) THEN
AHL_UNIT_THRESHOLDS_PKG.delete_row(p_x_df_schedules_tbl(i).unit_threshold_id);
IF (p_x_df_schedules_tbl(i).operation_flag = G_OP_UPDATE) THEN
p_x_df_schedules_tbl(i).object_version_number := p_x_df_schedules_tbl(i).object_version_number + 1;
p_x_df_schedules_tbl(i).last_updated_by := fnd_global.user_id;
p_x_df_schedules_tbl(i).last_update_date := SYSDATE;
p_x_df_schedules_tbl(i).last_update_login := fnd_global.user_id;
AHL_UNIT_THRESHOLDS_PKG.update_row(
p_unit_threshold_id => p_x_df_schedules_tbl(i).unit_threshold_id,
p_object_version_number => p_x_df_schedules_tbl(i).object_version_number,
p_last_updated_by => p_x_df_schedules_tbl(i).last_updated_by,
p_last_update_date => p_x_df_schedules_tbl(i).last_update_date,
p_last_update_login => p_x_df_schedules_tbl(i).last_update_login,
p_unit_deferral_id => p_x_df_schedules_tbl(i).unit_deferral_id,
p_counter_id => p_x_df_schedules_tbl(i).counter_id,
p_counter_value => p_x_df_schedules_tbl(i).counter_value,
p_ctr_value_type_code => p_x_df_schedules_tbl(i).ctr_value_type_code,
p_attribute_category => p_x_df_schedules_tbl(i).attribute_category,
p_attribute1 => p_x_df_schedules_tbl(i).attribute1,
p_attribute2 => p_x_df_schedules_tbl(i).attribute2,
p_attribute3 => p_x_df_schedules_tbl(i).attribute3,
p_attribute4 => p_x_df_schedules_tbl(i).attribute4,
p_attribute5 => p_x_df_schedules_tbl(i).attribute5,
p_attribute6 => p_x_df_schedules_tbl(i).attribute6,
p_attribute7 => p_x_df_schedules_tbl(i).attribute7,
p_attribute8 => p_x_df_schedules_tbl(i).attribute8,
p_attribute9 => p_x_df_schedules_tbl(i).attribute9,
p_attribute10 => p_x_df_schedules_tbl(i).attribute10,
p_attribute11 => p_x_df_schedules_tbl(i).attribute11,
p_attribute12 => p_x_df_schedules_tbl(i).attribute12,
p_attribute13 => p_x_df_schedules_tbl(i).attribute13,
p_attribute14 => p_x_df_schedules_tbl(i).attribute14,
p_attribute15 => p_x_df_schedules_tbl(i).attribute15
);
p_x_df_schedules_tbl(i).last_updated_by := fnd_global.user_id;
p_x_df_schedules_tbl(i).last_update_date := SYSDATE;
p_x_df_schedules_tbl(i).last_update_login := fnd_global.user_id;
AHL_UNIT_THRESHOLDS_PKG.insert_row(
p_x_unit_threshold_id => p_x_df_schedules_tbl(i).unit_threshold_id,
p_object_version_number => p_x_df_schedules_tbl(i).object_version_number,
p_created_by => p_x_df_schedules_tbl(i).created_by,
p_creation_date => p_x_df_schedules_tbl(i).creation_date,
p_last_updated_by => p_x_df_schedules_tbl(i).last_updated_by,
p_last_update_date => p_x_df_schedules_tbl(i).last_update_date,
p_last_update_login => p_x_df_schedules_tbl(i).last_update_login,
p_unit_deferral_id => p_x_df_schedules_tbl(i).unit_deferral_id,
p_counter_id => p_x_df_schedules_tbl(i).counter_id,
p_counter_value => p_x_df_schedules_tbl(i).counter_value,
p_ctr_value_type_code => p_x_df_schedules_tbl(i).ctr_value_type_code,
p_attribute_category => p_x_df_schedules_tbl(i).attribute_category,
p_attribute1 => p_x_df_schedules_tbl(i).attribute1,
p_attribute2 => p_x_df_schedules_tbl(i).attribute2,
p_attribute3 => p_x_df_schedules_tbl(i).attribute3,
p_attribute4 => p_x_df_schedules_tbl(i).attribute4,
p_attribute5 => p_x_df_schedules_tbl(i).attribute5,
p_attribute6 => p_x_df_schedules_tbl(i).attribute6,
p_attribute7 => p_x_df_schedules_tbl(i).attribute7,
p_attribute8 => p_x_df_schedules_tbl(i).attribute8,
p_attribute9 => p_x_df_schedules_tbl(i).attribute9,
p_attribute10 => p_x_df_schedules_tbl(i).attribute10,
p_attribute11 => p_x_df_schedules_tbl(i).attribute11,
p_attribute12 => p_x_df_schedules_tbl(i).attribute12,
p_attribute13 => p_x_df_schedules_tbl(i).attribute13,
p_attribute14 => p_x_df_schedules_tbl(i).attribute14,
p_attribute15 => p_x_df_schedules_tbl(i).attribute15
);
SELECT 'x' FROM CSI_CP_COUNTERS_V CO, AHL_UNIT_EFFECTIVITIES_B UE,AHL_UNIT_DEFERRALS_B UD -- Undid app usage related blind changes
WHERE co.customer_product_id = ue.csi_item_instance_id
AND co.counter_id = p_counter_id
AND UE.unit_effectivity_id = UD.unit_effectivity_id
AND UD.unit_deferral_id = p_unit_deferral_id;
SELECT 'x' from ahl_unit_effectivities_b UE, ahl_unit_deferrals_b UD,AHL_MR_INTERVALS_V MR,CSI_CP_COUNTERS_V CO
WHERE UD.unit_deferral_id = p_unit_deferral_id
AND UE.unit_effectivity_id = UD.unit_effectivity_id
AND co.customer_product_id = ue.csi_item_instance_id
AND UE.mr_effectivity_id = MR.mr_effectivity_id
AND CO.counter_id = p_counter_id
AND CO.counter_name = MR.counter_name; */
SELECT 'x' FROM ahl_unit_thresholds
WHERE object_version_number = p_object_version_number
AND unit_threshold_id = p_unit_threshold_id;
IF(p_df_schedules_tbl(i).operation_flag IN (G_OP_CREATE,G_OP_UPDATE) AND NVL(G_MODULE_TYPE,'x') <> 'JSP') THEN
-- validate whether valid items' counter
OPEN valid_counter_csr(p_df_schedules_tbl(i).unit_deferral_id,p_df_schedules_tbl(i).counter_id);
ELSIF(p_df_schedules_tbl(i).operation_flag = G_OP_DELETE) THEN
-- validate whether record exists for delete
OPEN valid_df_rec_del_csr(p_df_schedules_tbl(i).unit_threshold_id,p_df_schedules_tbl(i).object_version_number);
'Record for delete operation not found with keys in schedule record : ' || i
);
SELECT unit_deferral_id,counter_id,counter_value,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 object_version_number= p_object_version_number
AND unit_threshold_id = p_unit_threshold_id;
IF(p_x_df_schedules_tbl(i).operation_flag = G_OP_UPDATE) THEN
OPEN df_schedules_csr(p_x_df_schedules_tbl(i).unit_threshold_id, p_x_df_schedules_tbl(i).object_version_number);
PROCEDURE validate_deferral_updates(
p_df_header_rec IN AHL_PRD_DF_PVT.df_header_rec_type,
x_warning_msg_data OUT NOCOPY VARCHAR2)IS
l_count1 NUMBER;
SELECT UT.counter_id, CO.name, UT.counter_value, UT.ctr_value_type_code,CO.uom_code
FROM CS_COUNTERS CO,ahl_unit_thresholds UT
WHERE CO.counter_id = UT.counter_id
AND UT.unit_deferral_id = p_unit_deferral_id;
SELECT NVL(net_reading, 0) FROM cs_ctr_counter_values_v ctrread, cs_counter_groups_v ctrgrp,
-- AHL_UNIT_EFFECTIVITIES_APP_V UE,AHL_UNIT_DEFERRALS_B UD
AHL_UNIT_EFFECTIVITIES_B UE,AHL_UNIT_DEFERRALS_B UD -- Undid app usage related blind changes
WHERE ctrread.VALUE_TIMESTAMP <= p_deferral_effective_on
AND ctrread.counter_group_id = ctrgrp.counter_group_id
AND SOURCE_OBJECT_CODE = 'CP'
AND SOURCE_OBJECT_ID = UE.csi_item_instance_id
AND ctrread.counter_id = p_counter_id
AND UE.unit_effectivity_id = UD.unit_effectivity_id
AND UD.unit_deferral_id = p_unit_deferral_id
ORDER BY ctrread.counter_id asc, ctrread.VALUE_TIMESTAMP desc;
SELECT whichever_first_code
--FROM AHL_MR_HEADERS_APP_V MR, AHL_UNIT_EFFECTIVITIES_APP_V UE
FROM AHL_MR_HEADERS_B MR, AHL_UNIT_EFFECTIVITIES_B UE -- Undid blind changes for app_usage code
WHERE MR.mr_header_id = UE.mr_header_id
AND UE.unit_effectivity_id = p_unit_effectivity_id;
SELECT UE.due_date
FROM AHL_UNIT_EFFECTIVITIES_B UE
WHERE UE.mr_header_id = (
SELECT mr_header_id FROM AHL_UNIT_EFFECTIVITIES_B where unit_effectivity_id = p_unit_effectivity_id)
AND UE.csi_item_instance_id = (
SELECT csi_item_instance_id FROM AHL_UNIT_EFFECTIVITIES_B where unit_effectivity_id = p_unit_effectivity_id)
AND UE.unit_effectivity_id <> p_unit_effectivity_id
AND ( UE.status_code IS NULL OR UE.status_code = 'INIT-DUE')
ORDER BY DUE_DATE ASC;
'ahl.plsql.AHL_PRD_DF_PVT.validate_deferral_updates.begin',
'At the start of PLSQL procedure'
);
SELECT count(*) INTO l_count1 from (SELECT counter_id FROM ahl_unit_thresholds
WHERE unit_deferral_id = p_df_header_rec.unit_deferral_id);
SELECT count(*) INTO l_count2 from (SELECT DISTINCT counter_id FROM ahl_unit_thresholds
WHERE unit_deferral_id = p_df_header_rec.unit_deferral_id);
'ahl.plsql.AHL_PRD_DF_PVT.validate_deferral_updates',
'Counters are repeating in schedules'
);
'ahl.plsql.AHL_PRD_DF_PVT.validate_deferral_updates',
'Set due date or counter values are mandatory '
);
'ahl.plsql.AHL_PRD_DF_PVT.validate_deferral_updates',
'Deferral Effective On Date can not be null or greater than system date '
);
'ahl.plsql.AHL_PRD_DF_PVT.validate_deferral_updates',
'Defer to counter value is less than current counter value for counter name : ' || l_counter_name
);
'ahl.plsql.AHL_PRD_DF_PVT.validate_deferral_updates',
'Calculate Due Date API threw Error'
);
'ahl.plsql.AHL_PRD_DF_PVT.validate_deferral_updates.end',
'At the end of PLSQL procedure'
);
END validate_deferral_updates;
SELECT approval_status_code FROM ahl_unit_deferrals_b
WHERE unit_effectivity_id = p_unit_effectivity_id
UNION
SELECT approval_status_code FROM ahl_unit_deferrals_b
WHERE unit_effectivity_id IN
(
/*SELECT ue_id
FROM AHL_UE_RELATIONSHIPS
WHERE relationship_code = 'PARENT'
START WITH related_ue_id = p_unit_effectivity_id
CONNECT BY related_ue_id = PRIOR ue_id
UNION*/--parents are taken care of by now
SELECT distinct related_ue_id
FROM AHL_UE_RELATIONSHIPS
WHERE relationship_code = 'PARENT'
START WITH ue_id = p_unit_effectivity_id
CONNECT BY ue_id = PRIOR related_ue_id
);
UPDATE ahl_unit_deferrals_b
SET approval_status_code = p_new_status
WHERE unit_deferral_id = p_unit_deferral_id
AND object_version_number = p_object_version_number;--same transaction of caller API and update already happened
SELECT csi_item_instance_id FROM AHL_UNIT_EFFECTIVITIES_B UE, ahl_unit_deferrals_b UD
WHERE UE.unit_effectivity_id = UD.unit_effectivity_id
--AND UD.object_version_number = p_object_version_number
AND UD.unit_deferral_id = p_unit_deferral_id;
SELECT 'x' from AHL_MR_HEADERS_APP_V mr, AHL_MR_HEADERS_APP_V def,
ahl_unit_effectivities_b UE,ahl_unit_deferrals_b UD
WHERE UD.unit_deferral_id = p_unit_deferral_id
AND UD.object_version_number = p_object_version_number
AND UE.unit_effectivity_id = UD.unit_effectivity_id
AND def.mr_header_id = NVL(UE.mr_header_id,def.mr_header_id)
AND def.title = mr.title
AND trunc(sysdate) between trunc(mr.effective_from)
AND trunc(nvl(mr.effective_to, sysdate))
AND mr.version_number >= def.version_number;
UPDATE ahl_unit_deferrals_b
SET approval_status_code = 'DEFERRED',
object_version_number = p_object_version_number + 1
WHERE unit_deferral_id = p_unit_deferral_id
AND object_version_number = p_object_version_number;
UPDATE ahl_unit_deferrals_b
SET approval_status_code = p_new_status,
object_version_number = p_object_version_number + 1
WHERE unit_deferral_id = p_unit_deferral_id
AND object_version_number = p_object_version_number;
SELECT UD.unit_effectivity_id, ue.object_type
from ahl_unit_deferrals_b UD, AHL_UNIT_EFFECTIVITIES_B UE
WHERE NVL(UE.status_code,'x') NOT IN('ACCOMPLISHED','DEFERRED','EXCEPTION','TERMINATED','CANCELLED','MR-TERMINATE')
AND UE.unit_effectivity_id = UD.unit_effectivity_id
AND UD.object_version_number = p_object_version_number
AND UD.unit_deferral_id = p_unit_deferral_id;
SELECT WO.workorder_id
FROM ahl_workorder_tasks_v WO
WHERE WO.job_status_code NOT IN ( G_JOB_DRAFT,G_JOB_DELETED)
AND WO.unit_effectivity_id = p_unit_effectivity_id; */
SELECT WO.workorder_id
FROM ahl_workorders wo, ahl_visit_tasks_b vts,
ahl_visits_b vst,
(SELECT ORGANIZATION_ID FROM INV_ORGANIZATION_INFO_V
WHERE NVL (operating_unit, mo_global.get_current_org_id()) = mo_global.get_current_org_id()) ORG
WHERE wo.visit_task_id = vts.visit_task_id
AND vts.visit_id = vst.visit_id
AND vst.organization_id = org.organization_id
AND WO.status_code NOT IN ( G_JOB_DRAFT,G_JOB_DELETED)
AND vts.unit_effectivity_id = p_unit_effectivity_id
AND vts.task_type_code IN ('SUMMARY','UNASSOCIATED');
SELECT WO.workorder_id,
WO.object_version_number,
WO.status_code,
WO.actual_start_date,
WO.actual_end_date,
WO.workorder_name
FROM ahl_workorders WO , ahl_unit_effectivities_b UE, ahl_visit_tasks_b VST
WHERE WO.status_code NOT IN ( G_JOB_DRAFT,G_JOB_DELETED)
AND WO.master_workorder_flag = 'N'
AND WO.visit_task_id = VST.visit_task_id
AND VST.unit_effectivity_id = UE.unit_effectivity_id
AND UE.unit_effectivity_id = p_unit_effectivity_id
UNION
SELECT WO.workorder_id,
WO.object_version_number,
WO.status_code,
WO.actual_start_date,
WO.actual_end_date,
WO.workorder_name
FROM ahl_workorders WO , ahl_unit_effectivities_b UE, ahl_visit_tasks_b VST
WHERE WO.status_code NOT IN ( G_JOB_DRAFT,G_JOB_DELETED)
AND WO.master_workorder_flag = 'N'
AND WO.visit_task_id = VST.visit_task_id
AND VST.unit_effectivity_id = UE.unit_effectivity_id
AND UE.unit_effectivity_id IN
(
SELECT distinct related_ue_id
FROM AHL_UE_RELATIONSHIPS
WHERE relationship_code = 'PARENT'
START WITH ue_id = p_unit_effectivity_id
CONNECT BY ue_id = PRIOR related_ue_id
);
SELECT object_version_number from ahl_workorders
WHERE workorder_id = p_workorder_id;
SELECT title from ahl_unit_effectivities_v
WHERE UNIT_EFFECTIVITY_ID = p_unit_effectivity_id;
l_update_flag BOOLEAN := false;
SELECT 'x'
FROM CSI_ITEM_INSTANCES CII, AHL_WORKORDERS AWO
WHERE CII.WIP_JOB_ID = AWO.WIP_ENTITY_ID
AND AWO.workorder_id = p_workorder_id
AND ACTIVE_START_DATE <= SYSDATE
AND ((ACTIVE_END_DATE IS NULL) OR (ACTIVE_END_DATE >= SYSDATE))
AND LOCATION_TYPE_CODE = 'WIP'
AND NOT EXISTS (SELECT 'X' FROM CSI_II_RELATIONSHIPS CIR
WHERE CIR.SUBJECT_ID = CII.INSTANCE_ID
AND CIR.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
AND SYSDATE BETWEEN NVL(ACTIVE_START_DATE,SYSDATE) AND NVL(ACTIVE_END_DATE,SYSDATE));
l_update_flag := FALSE;
IF(isValidStatusUpdate(G_DEFERRAL_INITIATED, l_prd_workorder_rec.status_code))THEN
l_update_flag := TRUE;
IF(isValidStatusUpdate(G_DEFERRAL_REJECTED, l_prd_workorder_rec.status_code))THEN
l_update_flag := TRUE;
IF(l_update_flag)THEN
IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
fnd_log.string
(
fnd_log.level_statement,
'ahl.plsql.AHL_PRD_DF_PVT.process_workorders',
'Workorder Update Flag :TRUE '
);
'new Workorder status/Update : ' || l_prd_workorder_rec.status_code
);
SELECT meaning INTO l_status_meaning
FROM fnd_lookup_values_vl
WHERE lookup_type = 'AHL_JOB_STATUS'
AND LOOKUP_CODE = l_prd_workorder_rec.status_code;
AHL_PRD_WORKORDER_PVT.update_job
(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
p_default => FND_API.G_TRUE,
p_module_type => 'API',
x_return_status => x_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_x_prd_workorder_rec => l_prd_workorder_rec,
p_x_prd_workoper_tbl => l_prd_workoper_tbl
);
'Status after AHL_PRD_WORKORDER_PVT.update_job API call : ' || x_return_status
);
'AHL_PRD_WORKORDER_PVT.update_job API returned error '
);
AHL_PRD_WORKORDER_PVT.update_job
(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
p_default => FND_API.G_TRUE,
p_module_type => 'API',
x_return_status => x_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_x_prd_workorder_rec => l_prd_workorder_rec,
p_x_prd_workoper_tbl => l_prd_workoper_tbl
);
'Status after AHL_PRD_WORKORDER_PVT.update_job API call : ' || x_return_status
);
'AHL_PRD_WORKORDER_PVT.update_job API returned error '
);
FUNCTION isValidStatusUpdate(
operation_code VARCHAR2,
status_code VARCHAR2)RETURN BOOLEAN IS
l_yes_flag boolean := FALSE;
END isValidStatusUpdate;
SELECT status_code,last_update_date FROM ahl_workorder_txns
WHERE workorder_id = p_workorder_id ORDER BY last_update_date DESC;
SELECT UD.unit_effectivity_id from ahl_unit_deferrals_b UD
WHERE UD.object_version_number = p_object_version_number
AND UD.unit_deferral_id = p_unit_deferral_id;
SELECT
UNIT_EFFECTIVITY_ID, OBJECT_VERSION_NUMBER, CSI_ITEM_INSTANCE_ID, MR_INTERVAL_ID,
MR_EFFECTIVITY_ID, MR_HEADER_ID, STATUS_CODE, SET_DUE_DATE, ACCOMPLISHED_DATE,
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, COUNTER_ID,OBJECT_TYPE,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_VL UE
WHERE unit_effectivity_id = p_unit_effectivity_id
UNION
SELECT
UNIT_EFFECTIVITY_ID, OBJECT_VERSION_NUMBER, CSI_ITEM_INSTANCE_ID, MR_INTERVAL_ID,
MR_EFFECTIVITY_ID, MR_HEADER_ID, STATUS_CODE, SET_DUE_DATE, ACCOMPLISHED_DATE,
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, COUNTER_ID,OBJECT_TYPE,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_VL UE
WHERE unit_effectivity_id IN
(
SELECT distinct related_ue_id
FROM AHL_UE_RELATIONSHIPS
WHERE relationship_code = 'PARENT'
START WITH ue_id = p_unit_effectivity_id
CONNECT BY ue_id = PRIOR related_ue_id
);
SELECT distinct ue_id, related_ue_id, originator_ue_id
FROM AHL_UE_RELATIONSHIPS
WHERE relationship_code = 'PARENT'
START WITH ue_id = p_unit_effectivity_id
CONNECT BY ue_id = PRIOR related_ue_id;
l_update_status VARCHAR2(30);
SELECT ue.object_type,
sr.incident_id,
sr.incident_number,
sr.object_version_number
FROM
ahl_unit_effectivities_b ue,
cs_incidents_all_b sr
WHERE
ue.cs_incident_id = sr.incident_id (+) and
ue.unit_effectivity_id = p_ue_id;
AHL_UNIT_EFFECTIVITIES_PKG.Insert_Row (
X_ROWID => l_rowid,
X_UNIT_EFFECTIVITY_ID => l_new_unit_effectivity_id,
X_CSI_ITEM_INSTANCE_ID => ue_rec.csi_item_instance_id,
X_MR_HEADER_ID => ue_rec.mr_header_id,
X_REPETITIVE_MR_FLAG => ue_rec.repetitive_mr_flag,
X_REMARKS => ue_rec.remarks,
X_SERVICE_LINE_ID => ue_rec.service_line_id,
X_PROGRAM_MR_HEADER_ID => ue_rec.program_mr_header_id,
X_CS_INCIDENT_ID => ue_rec.cs_incident_id,
X_DEFER_FROM_UE_ID => l_unit_effectivity_id,
X_ORIG_DEFERRAL_UE_ID => NULL,
X_QA_COLLECTION_ID => NULL, --ue_rec.qa_collection_id,
X_MR_INTERVAL_ID => null,
X_MR_EFFECTIVITY_ID => null,
X_STATUS_CODE => null,
X_DUE_DATE => null,
X_DUE_COUNTER_VALUE => null,
X_FORECAST_SEQUENCE => null,
X_TOLERANCE_FLAG => null,
X_MESSAGE_CODE => null,
X_PRECEDING_UE_ID => null,
X_DATE_RUN => null,
X_SET_DUE_DATE => null,
X_ACCOMPLISHED_DATE => null,
X_CANCEL_REASON_CODE => null,
X_EARLIEST_DUE_DATE => null,
X_LATEST_DUE_DATE => 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_APPLICATION_USG_CODE => RTRIM(LTRIM(FND_PROFILE.VALUE('AHL_APPLN_USAGE'))),
X_OBJECT_TYPE => ue_rec.object_type,
X_MANUALLY_PLANNED_FLAG => ue_rec.manually_planned_flag,
X_COUNTER_ID => ue_rec.counter_id,
X_LOG_SERIES_CODE => ue_rec.log_series_code,
X_LOG_SERIES_NUMBER => ue_rec.log_series_number,
X_FLIGHT_NUMBER => ue_rec.flight_number,
X_MEL_CDL_TYPE_CODE => ue_rec.mel_cdl_type_code,
X_POSITION_PATH_ID => ue_rec.position_path_id,
X_ATA_CODE => ue_rec.ATA_CODE,
X_UNIT_CONFIG_HEADER_ID => ue_rec.unit_config_header_id,
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 );
l_update_status := p_new_status;
l_update_status := ue_rec.status_code;
AHL_UNIT_EFFECTIVITIES_PKG.update_row(
x_unit_effectivity_id => ue_rec.UNIT_EFFECTIVITY_ID,
x_csi_item_instance_id => ue_rec.CSI_ITEM_INSTANCE_ID,
x_mr_interval_id => ue_rec.MR_INTERVAL_ID,
x_mr_effectivity_id => ue_rec.MR_EFFECTIVITY_ID,
x_mr_header_id => ue_rec.MR_HEADER_ID,
x_status_code => l_update_status,
x_due_date => ue_rec.DUE_DATE,
x_due_counter_value => ue_rec.DUE_COUNTER_VALUE,
x_forecast_sequence => ue_rec.FORECAST_SEQUENCE,
x_repetitive_mr_flag => ue_rec.REPETITIVE_MR_FLAG,
x_tolerance_flag => ue_rec.TOLERANCE_FLAG,
x_remarks => ue_rec.REMARKS,
x_message_code => ue_rec.MESSAGE_CODE,
x_preceding_ue_id => ue_rec.PRECEDING_UE_ID,
x_date_run => ue_rec.DATE_RUN,
x_set_due_date => ue_rec.set_due_date,
x_accomplished_date => ue_rec.accomplished_date,
x_service_line_id => ue_rec.service_line_id,
x_program_mr_header_id => ue_rec.program_mr_header_id,
x_cancel_reason_code => ue_rec.cancel_reason_code,
x_earliest_due_date => ue_rec.earliest_due_date,
x_latest_due_date => ue_rec.latest_due_date,
x_defer_from_ue_id => ue_rec.defer_from_ue_id,
x_qa_collection_id => ue_rec.qa_collection_id,
x_cs_incident_id => ue_rec.cs_incident_id,
x_orig_deferral_ue_id => ue_rec.orig_deferral_ue_id,
X_APPLICATION_USG_CODE => RTRIM(LTRIM(FND_PROFILE.VALUE('AHL_APPLN_USAGE'))),
X_COUNTER_ID => ue_rec.counter_id,
X_OBJECT_TYPE => ue_rec.object_type,
X_MANUALLY_PLANNED_FLAG => ue_rec.manually_planned_flag,
X_LOG_SERIES_CODE => ue_rec.log_series_code,
X_LOG_SERIES_NUMBER => ue_rec.log_series_number,
X_FLIGHT_NUMBER => ue_rec.flight_number,
X_MEL_CDL_TYPE_CODE => ue_rec.mel_cdl_type_code,
X_POSITION_PATH_ID => ue_rec.position_path_id,
X_ATA_CODE => ue_rec.ATA_CODE,
X_UNIT_CONFIG_HEADER_ID => ue_rec.unit_config_header_id,
x_attribute_category => ue_rec.ATTRIBUTE_CATEGORY,
x_attribute1 => ue_rec.ATTRIBUTE1,
x_attribute2 => ue_rec.ATTRIBUTE2,
x_attribute3 => ue_rec.ATTRIBUTE3,
x_attribute4 => ue_rec.ATTRIBUTE4,
x_attribute5 => ue_rec.ATTRIBUTE5,
x_attribute6 => ue_rec.ATTRIBUTE6,
x_attribute7 => ue_rec.ATTRIBUTE7,
x_attribute8 => ue_rec.ATTRIBUTE8,
x_attribute9 => ue_rec.ATTRIBUTE9,
x_attribute10 => ue_rec.ATTRIBUTE10,
x_attribute11 => ue_rec.ATTRIBUTE11,
x_attribute12 => ue_rec.ATTRIBUTE12,
x_attribute13 => ue_rec.ATTRIBUTE13,
x_attribute14 => ue_rec.ATTRIBUTE14,
x_attribute15 => ue_rec.ATTRIBUTE15,
x_object_version_number => 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
);
/* Insert SR update status call here after making sure that object type is 'SR' for p_unit_effectivity_id
* This call should be made only when p_new_status = 'DEFERRED' as SR can not be terminated
* IF SR need to be updated even when deferral has been rejected then make the same call in ELSIF
* statement below
*/
-- TAMAL -- Begin changes for ER #3356804
-- TAMAL -- Get UE and SR details
OPEN get_ue_sr_details (ue_rec.UNIT_EFFECTIVITY_ID);
-- Call SR Update_Status API
CS_ServiceRequest_PUB.Update_Status
(
p_api_version => 2.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_resp_appl_id => NULL,
p_resp_id => NULL,
p_user_id => NULL,
p_login_id => NULL,
--p_status_id => 1, --OPEN
p_status_id => l_status_id,
p_closed_date => NULL,
p_audit_comments => NULL,
p_called_by_workflow => FND_API.G_FALSE,
p_workflow_process_id => NULL,
p_comments => NULL,
p_public_comment_flag => FND_API.G_FALSE,
p_validate_sr_closure => 'N',
p_auto_close_child_entities => 'N',
p_request_id => NULL,
p_request_number => l_cs_incident_number,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_object_version_number => l_cs_incident_ovn,
-- p_status => 'OPEN',
x_interaction_id => l_interaction_id
);
-- Abort if any error in calling the SR Update_Status API...
IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
THEN
x_return_status := l_return_status;
AHL_UE_RELATIONSHIPS_PKG.Insert_Row(
X_UE_RELATIONSHIP_ID => l_ue_relationship_id,
X_UE_ID => l_ue_relns_tbl(k).ue_id,
X_RELATED_UE_ID => l_ue_relns_tbl(k).related_ue_id,
X_RELATIONSHIP_CODE => 'PARENT',
X_ORIGINATOR_UE_ID => l_new_parent_ue_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 => fnd_global.user_id,
X_CREATION_DATE => sysdate,
X_CREATED_BY => fnd_global.user_id,
X_LAST_UPDATE_LOGIN => fnd_global.login_id);
/* Insert SR update status call here after making sure that object type is 'SR' for p_unit_effectivity_id
* This call should be made only when p_new_status = 'DEFERRED' as SR can not be terminated
* IF SR need to be updated even when deferral has been rejected then make the same call in ELSIF
* statement below
*/
ELSIF(p_approval_result_code IN (G_DEFERRAL_INITIATED,G_DEFERRAL_REJECTED))THEN
IF(p_approval_result_code = G_DEFERRAL_INITIATED)THEN
l_orig_deferral_ue_id := l_unit_effectivity_id;
AHL_UNIT_EFFECTIVITIES_PKG.update_row(
x_unit_effectivity_id => ue_rec.UNIT_EFFECTIVITY_ID,
x_csi_item_instance_id => ue_rec.CSI_ITEM_INSTANCE_ID,
x_mr_interval_id => ue_rec.MR_INTERVAL_ID,
x_mr_effectivity_id => ue_rec.MR_EFFECTIVITY_ID,
x_mr_header_id => ue_rec.MR_HEADER_ID,
x_status_code => ue_rec.status_code,
x_due_date => ue_rec.DUE_DATE,
x_due_counter_value => ue_rec.DUE_COUNTER_VALUE,
x_forecast_sequence => ue_rec.FORECAST_SEQUENCE,
x_repetitive_mr_flag => ue_rec.REPETITIVE_MR_FLAG,
x_tolerance_flag => ue_rec.TOLERANCE_FLAG,
x_remarks => ue_rec.REMARKS,
x_message_code => ue_rec.MESSAGE_CODE,
x_preceding_ue_id => ue_rec.PRECEDING_UE_ID,
x_date_run => ue_rec.DATE_RUN,
x_set_due_date => ue_rec.set_due_date,
x_accomplished_date => ue_rec.accomplished_date,
x_service_line_id => ue_rec.service_line_id,
x_program_mr_header_id => ue_rec.program_mr_header_id,
x_cancel_reason_code => ue_rec.cancel_reason_code,
x_earliest_due_date => ue_rec.earliest_due_date,
x_latest_due_date => ue_rec.latest_due_date,
x_defer_from_ue_id => ue_rec.defer_from_ue_id,
x_qa_collection_id => ue_rec.qa_collection_id,
x_cs_incident_id => ue_rec.cs_incident_id,
x_orig_deferral_ue_id => l_orig_deferral_ue_id,
X_APPLICATION_USG_CODE => RTRIM(LTRIM(FND_PROFILE.VALUE('AHL_APPLN_USAGE'))),
X_OBJECT_TYPE => ue_rec.object_type,
X_MANUALLY_PLANNED_FLAG => ue_rec.manually_planned_flag,
X_COUNTER_ID => ue_rec.counter_id,
X_LOG_SERIES_CODE => ue_rec.log_series_code,
X_LOG_SERIES_NUMBER => ue_rec.log_series_number,
X_FLIGHT_NUMBER => ue_rec.flight_number,
X_MEL_CDL_TYPE_CODE => ue_rec.mel_cdl_type_code,
X_POSITION_PATH_ID => ue_rec.position_path_id,
X_ATA_CODE => ue_rec.ATA_CODE,
X_UNIT_CONFIG_HEADER_ID => ue_rec.unit_config_header_id,
x_attribute_category => ue_rec.ATTRIBUTE_CATEGORY,
x_attribute1 => ue_rec.ATTRIBUTE1,
x_attribute2 => ue_rec.ATTRIBUTE2,
x_attribute3 => ue_rec.ATTRIBUTE3,
x_attribute4 => ue_rec.ATTRIBUTE4,
x_attribute5 => ue_rec.ATTRIBUTE5,
x_attribute6 => ue_rec.ATTRIBUTE6,
x_attribute7 => ue_rec.ATTRIBUTE7,
x_attribute8 => ue_rec.ATTRIBUTE8,
x_attribute9 => ue_rec.ATTRIBUTE9,
x_attribute10 => ue_rec.ATTRIBUTE10,
x_attribute11 => ue_rec.ATTRIBUTE11,
x_attribute12 => ue_rec.ATTRIBUTE12,
x_attribute13 => ue_rec.ATTRIBUTE13,
x_attribute14 => ue_rec.ATTRIBUTE14,
x_attribute15 => ue_rec.ATTRIBUTE15,
x_object_version_number => 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);
SELECT UE.unit_effectivity_id FROM ahl_unit_effectivities_b UE
WHERE defer_from_ue_id IS NOT NULL
--AND NOT EXISTS (Select 'x' from ahl_visit_tasks_b VST where VST.unit_effectivity_id = UE.unit_effectivity_id)
AND defer_from_ue_id IN (
SELECT related_ue_id
FROM AHL_UE_RELATIONSHIPS
WHERE relationship_code = 'PARENT'
START WITH ue_id = p_unit_effectivity_id
CONNECT BY ue_id = PRIOR related_ue_id
);
SELECT 'x' FROM ahl_visit_tasks_b VST
WHERE VST.unit_effectivity_id = p_unit_effectivity_id;
SELECT ue_relationship_id FROM AHL_UE_RELATIONSHIPS
WHERE relationship_code = 'PARENT'
AND related_ue_id = p_unit_effectivity_id;
SELECT
UNIT_EFFECTIVITY_ID, OBJECT_VERSION_NUMBER, CSI_ITEM_INSTANCE_ID, MR_INTERVAL_ID,
MR_EFFECTIVITY_ID, MR_HEADER_ID, STATUS_CODE, SET_DUE_DATE, ACCOMPLISHED_DATE,
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, COUNTER_ID,OBJECT_TYPE,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_VL UE
WHERE unit_effectivity_id = p_unit_effectivity_id;
SELECT UD.unit_deferral_id FROM ahl_unit_deferrals_b UD
WHERE UD.unit_deferral_type = 'DEFERRAL'
AND UD.unit_effectivity_id IN (
SELECT related_ue_id
FROM AHL_UE_RELATIONSHIPS
WHERE relationship_code = 'PARENT'
START WITH ue_id = p_unit_effectivity_id
CONNECT BY ue_id = PRIOR related_ue_id
)
AND NOT EXISTS (
SELECT 'x' FROM ahl_unit_effectivities_b
WHERE defer_from_ue_id = UD.unit_effectivity_id
);
SELECT unit_threshold_id FROM ahl_unit_thresholds
WHERE unit_deferral_id = p_unit_deferral_id;
AHL_UNIT_EFFECTIVITIES_PKG.delete_row(ue_id_rec.unit_effectivity_id);
AHL_UE_RELATIONSHIPS_PKG.delete_row(l_ue_relationship_id);
AHL_UNIT_EFFECTIVITIES_PKG.update_row(
x_unit_effectivity_id => ue_rec.UNIT_EFFECTIVITY_ID,
x_csi_item_instance_id => ue_rec.CSI_ITEM_INSTANCE_ID,
x_mr_interval_id => ue_rec.MR_INTERVAL_ID,
x_mr_effectivity_id => ue_rec.MR_EFFECTIVITY_ID,
x_mr_header_id => ue_rec.MR_HEADER_ID,
x_status_code => 'EXCEPTION',
x_due_date => ue_rec.DUE_DATE,
x_due_counter_value => ue_rec.DUE_COUNTER_VALUE,
x_forecast_sequence => ue_rec.FORECAST_SEQUENCE,
x_repetitive_mr_flag => ue_rec.REPETITIVE_MR_FLAG,
x_tolerance_flag => ue_rec.TOLERANCE_FLAG,
x_remarks => ue_rec.REMARKS,
x_message_code => ue_rec.MESSAGE_CODE,
x_preceding_ue_id => ue_rec.PRECEDING_UE_ID,
x_date_run => ue_rec.DATE_RUN,
x_set_due_date => ue_rec.set_due_date,
x_accomplished_date => ue_rec.accomplished_date,
x_service_line_id => ue_rec.service_line_id,
x_program_mr_header_id => ue_rec.program_mr_header_id,
x_cancel_reason_code => ue_rec.cancel_reason_code,
x_earliest_due_date => ue_rec.earliest_due_date,
x_latest_due_date => ue_rec.latest_due_date,
x_defer_from_ue_id => ue_rec.defer_from_ue_id,
x_qa_collection_id => ue_rec.qa_collection_id,
x_cs_incident_id => ue_rec.cs_incident_id,
x_orig_deferral_ue_id => ue_rec.orig_deferral_ue_id,
X_APPLICATION_USG_CODE => RTRIM(LTRIM(FND_PROFILE.VALUE('AHL_APPLN_USAGE'))),
X_OBJECT_TYPE => ue_rec.object_type,
X_MANUALLY_PLANNED_FLAG => ue_rec.manually_planned_flag,
X_COUNTER_ID => ue_rec.counter_id,
X_LOG_SERIES_CODE => ue_rec.log_series_code,
X_LOG_SERIES_NUMBER => ue_rec.log_series_number,
X_FLIGHT_NUMBER => ue_rec.flight_number,
X_MEL_CDL_TYPE_CODE => ue_rec.mel_cdl_type_code,
X_POSITION_PATH_ID => ue_rec.position_path_id,
X_ATA_CODE => ue_rec.ATA_CODE,
X_UNIT_CONFIG_HEADER_ID => ue_rec.unit_config_header_id,
x_attribute_category => ue_rec.ATTRIBUTE_CATEGORY,
x_attribute1 => ue_rec.ATTRIBUTE1,
x_attribute2 => ue_rec.ATTRIBUTE2,
x_attribute3 => ue_rec.ATTRIBUTE3,
x_attribute4 => ue_rec.ATTRIBUTE4,
x_attribute5 => ue_rec.ATTRIBUTE5,
x_attribute6 => ue_rec.ATTRIBUTE6,
x_attribute7 => ue_rec.ATTRIBUTE7,
x_attribute8 => ue_rec.ATTRIBUTE8,
x_attribute9 => ue_rec.ATTRIBUTE9,
x_attribute10 => ue_rec.ATTRIBUTE10,
x_attribute11 => ue_rec.ATTRIBUTE11,
x_attribute12 => ue_rec.ATTRIBUTE12,
x_attribute13 => ue_rec.ATTRIBUTE13,
x_attribute14 => ue_rec.ATTRIBUTE14,
x_attribute15 => ue_rec.ATTRIBUTE15,
x_object_version_number => 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_UNIT_THRESHOLDS_PKG.delete_row(redundant_threshold_rec.unit_threshold_id);
AHL_UNIT_DEFERRALS_PKG.delete_row(redundant_deferral_rec.unit_deferral_id);
SELECT due_date, mr_header_id,title,description,repetitive_mr_flag,cs_incident_id,cs_incident_number,
cs_incident_summary,manually_planned_flag
FROM ahl_ue_deferral_details_v
WHERE unit_effectivity_id = p_unit_effectivity_id
AND APPLICATION_USG_CODE = RTRIM(LTRIM(FND_PROFILE.VALUE('AHL_APPLN_USAGE'))) ;--this takes care of app_usage changes
SELECT meaning FROM FND_LOOKUP_VALUES_VL
WHERE lookup_code = p_status_code
AND lookup_type = 'AHL_PRD_MR_STATUS';
SELECT VS.visit_id, VS.visit_number FROM ahl_visits_b VS,ahl_visit_tasks_b VST
WHERE VST.visit_id = VS.visit_id
AND VST.unit_effectivity_id = p_unit_effectivity_id;
SELECT 'x' from csi_cp_counters_v CP, AHL_MR_INTERVALS_V MRI, AHL_MR_EFFECTIVITIES MRE, AHL_UNIT_EFFECTIVITIES_B UE
WHERE CP.customer_product_id = UE.csi_item_instance_id
AND CP.counter_name = MRI.counter_name
AND MRI.reset_value IS NOT NULL
AND MRI.mr_effectivity_id = MRE.mr_effectivity_id
AND MRE.mr_header_id = UE.mr_header_id
AND UE.unit_effectivity_id = p_unit_effectivity_id
UNION
SELECT 'x' from csi_cp_counters_v CP, AHL_MR_INTERVALS_V MRI, AHL_MR_EFFECTIVITIES MRE, AHL_UNIT_EFFECTIVITIES_B UE
WHERE CP.customer_product_id = UE.csi_item_instance_id
AND CP.counter_name = MRI.counter_name
AND MRI.reset_value IS NOT NULL
AND MRI.mr_effectivity_id = MRE.mr_effectivity_id
AND MRE.mr_header_id = UE.mr_header_id
AND UE.unit_effectivity_id IN
(
SELECT related_ue_id
FROM AHL_UE_RELATIONSHIPS
WHERE relationship_code = 'PARENT'
START WITH ue_id = p_unit_effectivity_id
CONNECT BY ue_id = PRIOR related_ue_id
);
SELECT 'x'
from csi_counter_associations ca, csi_counters_vl CP, AHL_MR_INTERVALS_V MRI,
AHL_MR_EFFECTIVITIES MRE, AHL_UNIT_EFFECTIVITIES_B UE
WHERE CA.source_object_id = UE.csi_item_instance_id
AND ca.source_object_code = 'CP'
AND CP.counter_template_name = MRI.counter_name
AND MRI.reset_value IS NOT NULL
AND MRI.mr_effectivity_id = MRE.mr_effectivity_id
AND MRE.mr_header_id = UE.mr_header_id
AND UE.unit_effectivity_id = p_unit_effectivity_id
UNION
SELECT 'x'
from csi_counter_associations ca, csi_counters_vl CP, AHL_MR_INTERVALS_V MRI,
AHL_MR_EFFECTIVITIES MRE, AHL_UNIT_EFFECTIVITIES_B UE
WHERE CA.source_object_id = UE.csi_item_instance_id
AND ca.source_object_code = 'CP'
AND CP.counter_template_name = MRI.counter_name
AND MRI.reset_value IS NOT NULL
AND MRI.mr_effectivity_id = MRE.mr_effectivity_id
AND MRE.mr_header_id = UE.mr_header_id
AND UE.unit_effectivity_id IN
(
SELECT related_ue_id
FROM AHL_UE_RELATIONSHIPS
WHERE relationship_code = 'PARENT'
START WITH ue_id = p_unit_effectivity_id
CONNECT BY ue_id = PRIOR related_ue_id
);
SELECT unit_deferral_id, object_version_number, approval_status_code,FLV.meaning approval_status_meaning,defer_reason_code,skip_mr_flag,
affect_due_calc_flag, set_due_date, deferral_effective_on,remarks,approver_notes, user_deferral_type, DTYP.meaning user_deferral_mean
FROM ahl_unit_deferrals_vl,fnd_lookup_values_vl FLV, fnd_lookup_values_vl DTYP
WHERE unit_deferral_type = 'DEFERRAL'
AND unit_effectivity_id = p_unit_effectivity_id
AND FLV.lookup_code = approval_status_code
AND FLV.lookup_type = 'AHL_PRD_DF_APPR_STATUS_TYPES'
AND DTYP.lookup_type(+) = 'AHL_PRD_DEFERRAL_TYPE'
AND DTYP.lookup_code(+) = user_deferral_type ;
SELECT UT.unit_threshold_id,UT.object_version_number,UT.unit_deferral_id, UT.counter_id,
CO.name, UT.counter_value, UT.ctr_value_type_code,MU.unit_of_measure
FROM MTL_UNITS_OF_MEASURE_VL MU, CS_COUNTERS CO,ahl_unit_thresholds UT
WHERE MU.uom_code = CO.uom_code
AND CO.counter_id = UT.counter_id
AND UT.unit_deferral_id = p_unit_deferral_id
ORDER BY CO.name;
SELECT orig_deferral_ue_id,ue_status_code,def_status_code
FROM ahl_ue_deferral_details_v
WHERE unit_effectivity_id = p_unit_effectivity_id;
SELECT UD.unit_deferral_id FROM ahl_unit_deferrals_b UD
WHERE UD.unit_deferral_type = 'DEFERRAL'
AND UD.unit_effectivity_id = p_unit_effectivity_id;
SELECT unit_threshold_id FROM ahl_unit_thresholds
WHERE unit_deferral_id = p_unit_deferral_id;
AHL_UNIT_THRESHOLDS_PKG.delete_row(redundant_threshold_rec.unit_threshold_id);
AHL_UNIT_DEFERRALS_PKG.delete_row(l_unit_deferral_id);
SELECT 'x'
FROM ahl_workorder_tasks_v wo, ahl_unit_deferrals_b udf
WHERE wo.unit_effectivity_id = udf.unit_effectivity_id
AND udf.unit_deferral_id = p_unit_deferral_id;
SELECT 'x'
FROM ahl_workorders wo, ahl_unit_deferrals_b udf,
ahl_visit_tasks_b vts, ahl_visits_b vst,
(SELECT ORGANIZATION_ID
FROM INV_ORGANIZATION_INFO_V
WHERE NVL (operating_unit, mo_global.get_current_org_id()) = mo_global.get_current_org_id()) ORG
WHERE WO.VISIT_TASK_ID=VTS.VISIT_TASK_ID
AND VST.VISIT_ID=VTS.VISIT_ID
AND VST.ORGANIZATION_ID=ORG.ORGANIZATION_ID
AND vts.unit_effectivity_id = udf.unit_effectivity_id
AND udf.unit_deferral_id = p_unit_deferral_id
AND rownum < 2;