[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT uom_code,
counter_value
FROM AHL_BUILD_GOALS
WHERE visit_id = c_visit_id;
SELECT start_date_time FROM AHL_VISITS_B WHERE visit_id = c_visit_id;
SELECT 'Y'
FROM csi_item_instances
WHERE instance_id = c_instance_id
AND INV_LOCATOR_ID IS NULL
AND WIP_JOB_ID IS NULL;
SELECT unit_effectivity_id,
due_date,
NVL(AMI_II_COUNTER_ID, UMPCounter_ID) counter_id,
(SELECT uom_code
FROM csi_counters_b
WHERE counter_id = NVL(AMI_II_COUNTER_ID, UMPCounter_ID)
) uom_code,
NVL(due_counter_value,0) due_counter_value
FROM
(SELECT UMP.unit_effectivity_id,
UMP.due_date,
(SELECT ii.counter_id
FROM CSI_COUNTER_ASSOCIATIONS CCA,
CSI_COUNTERS_VL ii,
CSI_COUNTER_TEMPLATE_VL tmp
WHERE CCA.SOURCE_OBJECT_CODE = 'CP'
AND CCA.SOURCE_OBJECT_ID = UMP.CSI_ITEM_INSTANCE_ID
AND ii.counter_id = cca.counter_id
AND tmp.counter_id = ami.counter_id
AND tmp.name = ii.counter_template_name
) AMI_II_COUNTER_ID,
UMP.counter_id UMPCounter_ID,
UMP.due_counter_value
FROM AHL_UNIT_EFFECTIVITIES_B UMP,
AHL_MR_HEADERS_B MRH,
AHL_MR_INTERVALS AMI
WHERE
--Table joins
UMP.MR_HEADER_ID = MRH.MR_HEADER_ID
AND ami.mr_interval_id(+) = ump.mr_interval_id
-- Bindings
AND UMP.CSI_ITEM_INSTANCE_ID = c_instance_id
--UMP Related conditions
AND UMP.APPLICATION_USG_CODE= 'AHL'
AND (UMP.STATUS_CODE IS NULL
OR UMP.STATUS_CODE ='INIT-DUE')
--MR Related conditions
AND MRH.SERVICE_TYPE_CODE = 'ASR'
AND MRH.IMPLEMENT_STATUS_CODE IN ('MANDATORY','SOFT_LIMIT','OPTIONAL_IMPLEMENT')
AND UMP.due_date IS NOT NULL
ORDER BY due_date
)
WHERE rownum <2;
SELECT unit_effectivity_id,
due_date,
NVL(AMI_II_COUNTER_ID, UMPCounter_ID) counter_id,
(SELECT uom_code
FROM csi_counters_b
WHERE counter_id = NVL(AMI_II_COUNTER_ID, UMPCounter_ID)
) uom_code,
NVL(due_counter_value,0) due_counter_value
FROM
(SELECT UMP.unit_effectivity_id,
UMP.due_date,
(SELECT ii.counter_id
FROM CSI_COUNTER_ASSOCIATIONS CCA,
CSI_COUNTERS_VL ii,
CSI_COUNTER_TEMPLATE_VL tmp
WHERE CCA.SOURCE_OBJECT_CODE = 'CP'
AND CCA.SOURCE_OBJECT_ID = UMP.CSI_ITEM_INSTANCE_ID
AND ii.counter_id = cca.counter_id
AND tmp.counter_id = ami.counter_id
AND tmp.name = ii.counter_template_name
) AMI_II_COUNTER_ID,
UMP.counter_id UMPCounter_ID,
UMP.due_counter_value
FROM AHL_UNIT_EFFECTIVITIES_B UMP,
AHL_MR_HEADERS_B MRH,
AHL_MR_INTERVALS AMI
WHERE
--Table joins
UMP.MR_HEADER_ID = MRH.MR_HEADER_ID
AND ami.mr_interval_id(+) = ump.mr_interval_id
-- Bindings
AND UMP.unit_effectivity_id = c_ue_id
--UMP Related conditions
AND UMP.APPLICATION_USG_CODE= 'AHL'
AND (UMP.STATUS_CODE IS NULL
OR UMP.STATUS_CODE ='INIT-DUE')
--MR Related conditions
AND MRH.SERVICE_TYPE_CODE = 'ASR'
AND MRH.IMPLEMENT_STATUS_CODE IN ('MANDATORY','SOFT_LIMIT','OPTIONAL_IMPLEMENT')
AND UMP.due_date IS NOT NULL
ORDER BY due_date
);
SELECT
nvl((SELECT ccr.net_reading
FROM csi_counter_readings ccr
WHERE ccr.counter_value_id = cc.CTR_VAL_MAX_SEQ_NO
AND NVL(ccr.disabled_flag,'N') = 'N'
),0) net_reading
FROM CSI_COUNTERS_B CC
WHERE CC.COUNTER_ID = c_counter_id;
' The selected UE values are > ' || ' Due Date = ' || TO_CHAR(l_due_date) ||
' Counter UOM = ' || l_counter_uom || ' UE Line id = ' || l_ue_id||
' Counter Id'||l_counter_id );
SELECT NVL(ue.earliest_due_date,ue.due_date)
FROM ahl_unit_effectivities_b ue,
CS_INCIDENTS_ALL_B nr
WHERE ue.csi_item_instance_id =c_instance_id
AND ue.cs_incident_id =nr.incident_id
AND nr.incident_type_id =fnd_profile.value('AHL_REMOVAL_NON_ROUTINE') -- Removal NR type Id
AND ue.object_type ='SR'
AND ue.application_usg_code = fnd_profile.value('AHL_APPLN_USAGE')
AND NVL(ue.status_code,'INIT-DUE') = 'INIT-DUE';
SELECT MIN(NVL(earliest_due_date,due_date))
FROM ahl_unit_effectivities_b B,
ahl_mr_headers_b mr
WHERE B.mr_header_id =mr.mr_header_id
AND NVL(mr.service_type_code,'ASR') = 'ASR'
AND B.object_type ='MR'
AND B.application_usg_code = fnd_profile.value('AHL_APPLN_USAGE')
AND NVL(B.status_code,'INIT-DUE') = 'INIT-DUE'
--AND B.preceding_ue_id IS NULL
AND B.csi_item_instance_id IN
(SELECT subject_id
FROM csi_ii_relationships
WHERE 1 =1
START WITH object_id =c_instance_id
AND relationship_type_code = 'COMPONENT-OF'
AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
CONNECT BY prior subject_id=object_id
AND relationship_type_code = 'COMPONENT-OF'
AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
UNION
SELECT c_instance_id AS subject_id
FROM dual -- incliude the root instance id also
);
SELECT COUNT(visit_id)
INTO l_have_visit
FROM ahl_visits_b visit
WHERE visit.status_code <> 'CLOSED'
AND TRUNC(Get_Visit_End_Date(visit.visit_id)) BETWEEN p_from_dt AND p_to_dt
AND visit.item_instance_id IN
(SELECT object_id
FROM csi_ii_relationships
WHERE 1 =1
START WITH subject_id = p_instance_id
AND AHL_COMPLEX_MX_PVT.Is_Instance_On_Visit(visit.visit_id,subject_id)='Y'
CONNECT BY PRIOR object_id = subject_id
AND AHL_COMPLEX_MX_PVT.Is_Instance_On_Visit(visit.visit_id,subject_id)='Y'
UNION
SELECT p_instance_id object_id FROM dual
)
AND IS_RTS_VISIT(p_instance_id,visit.visit_id) = 1;
SELECT 1
INTO l_RTS_visit
FROM ahl_visits_b vst,
ahl_visit_tasks_b task
WHERE vst.visit_id = task.visit_id
and vst.visit_id=p_visit_id
AND (vst.ITEM_INSTANCE_ID = p_instance_id
OR task.instance_id =p_instance_id)
AND task.return_to_supply_flag = 'Y'
AND task.status_code <> 'DELETED'
ANd rownum <2;
SELECT cc.UOM_CODE,
(SELECT ccr.net_reading
FROM csi_counter_readings ccr
WHERE ccr.counter_value_id = cc.CTR_VAL_MAX_SEQ_NO
AND NVL(ccr.disabled_flag,'N') = 'N'
)
INTO l_ctr_uom,
l_ctr_reading
FROM CSI_COUNTER_ASSOCIATIONS CCA,
CSI_COUNTERS_VL CC
WHERE CCA.COUNTER_ID = CC.COUNTER_ID
AND CCA.SOURCE_OBJECT_ID = p_instance_id
AND CCA.SOURCE_OBJECT_CODE = 'CP'
AND CC.COUNTER_TEMPLATE_NAME = p_ctr_template_name;
select max(nvl(wdj.scheduled_completion_date,vts.end_date_time))
from ahl_visit_tasks_b vts, ahl_workorders wo, wip_discrete_jobs wdj
where
vts.visit_id = p_visit_id
and vts.return_to_supply_flag = 'Y'
and vts.status_code <> 'DELETED'
and not exists (select '1' from ahl_workorders where visit_task_id = vts.visit_task_id and status_code in (12,22,4,5,7))
/*If workorder exists then its status should not be Closed, Deleted, Complete, Complete No-charge, Cancelled */
and vts.visit_task_id = wo.visit_task_id (+)
and wo.wip_entity_id = wdj.wip_entity_id (+);
SELECT CLOSE_DATE_TIME
FROM AHL_VISITS_B
WHERE VISIT_ID = p_visit_id;
SELECT COUNT(*) FROM (
SELECT distinct vst.visit_id, vts.instance_id
FROM
AHL_VISITS_B VST, AHL_VISIT_TASKS_B VTS
WHERE
vst.visit_id = vts.visit_id
AND ( (c_visit_state = 'PLANNED' and VST.STATUS_CODE IN ('DRAFT','PLANNING')) OR
(c_visit_state = 'SCHEDULED' and VST.STATUS_CODE IN ('RELEASED','PARTIALLY RELEASED')))
AND VTS.INVENTORY_ITEM_ID = ITEM_ID
AND VST.ORGANIZATION_ID = ORG_ID
and vts.return_to_supply_flag = 'Y'
and vts.status_code <> 'DELETED'
and not exists (select '1' from ahl_workorders where visit_task_id = vts.visit_task_id and status_code in (12,22,4,5,7))
AND TRUNC(AHL_COMPLEX_MX_PVT.Get_Visit_End_Date(VST.VISIT_ID)) BETWEEN TRUNC(START_DATE) AND TRUNC(END_DATE)
union
SELECT distinct vst.visit_id, vst.item_instance_id
FROM
AHL_VISITS_B VST
WHERE
( (c_visit_state = 'PLANNED' and VST.STATUS_CODE IN ('DRAFT','PLANNING')) OR
(c_visit_state = 'SCHEDULED' and VST.STATUS_CODE IN ('RELEASED','PARTIALLY RELEASED')))
AND VST.INVENTORY_ITEM_ID = ITEM_ID
AND VST.ORGANIZATION_ID = ORG_ID
and exists(select 1 from ahl_visit_tasks_b vts where vts.visit_id = vst.visit_id and vts.return_to_supply_flag = 'Y' and vts.inventory_item_id <> vst.inventory_item_id and
vts.status_code <> 'DELETED' and not exists (select '1' from ahl_workorders where visit_task_id = vts.visit_task_id and status_code in (12,22,4,5,7)) and rownum < 2)
AND TRUNC(AHL_COMPLEX_MX_PVT.Get_Visit_End_Date(VST.VISIT_ID)) BETWEEN TRUNC(START_DATE) AND TRUNC(END_DATE)
);
SELECT COUNT(*) FROM (
SELECT distinct vst.visit_id, vts.instance_id
FROM
AHL_VISITS_B VST, AHL_VISIT_TASKS_B VTS
WHERE
vst.visit_id = vts.visit_id
AND vst.status_code IN ('DRAFT' , 'PLANNING', 'PARTIALLY RELEASED', 'RELEASED')
AND VTS.INVENTORY_ITEM_ID = ITEM_ID
AND VST.ORGANIZATION_ID = ORG_ID
and vts.return_to_supply_flag = 'Y'
and vts.status_code <> 'DELETED'
and not exists (select '1' from ahl_workorders where visit_task_id = vts.visit_task_id and status_code in (12,22,4,5,7))
and LAST_DAY(AHL_COMPLEX_MX_PVT.Get_Visit_End_Date(VST.VISIT_ID)) = REQ_DATE
union
SELECT distinct vst.visit_id, vst.item_instance_id
FROM
AHL_VISITS_B VST
WHERE
vst.status_code IN ('DRAFT' , 'PLANNING', 'PARTIALLY RELEASED', 'RELEASED')
AND VST.INVENTORY_ITEM_ID = ITEM_ID
AND VST.ORGANIZATION_ID = ORG_ID
and exists(select 1 from ahl_visit_tasks_b vts where vts.visit_id = vst.visit_id and vts.return_to_supply_flag = 'Y' and vts.inventory_item_id <> vst.inventory_item_id and
vts.status_code <> 'DELETED' and not exists (select '1' from ahl_workorders where visit_task_id = vts.visit_task_id and status_code in (12,22,4,5,7)) and rownum < 2)
and LAST_DAY(AHL_COMPLEX_MX_PVT.Get_Visit_End_Date(VST.VISIT_ID)) = REQ_DATE
);
SELECT cii.instance_id,cii.location_type_code, cii.instance_usage_code,cii.wip_job_id,cii.inv_locator_id,
mil.segment19,mil.segment20
-- fix for bug 16304850
--FROM csi_item_instances cii ,mtl_item_locations_kfv mil
FROM csi_item_instances cii ,mtl_item_locations mil
-- end fix for bug 16304850
WHERE cii.instance_id = c_instance_id
and cii.inv_locator_id = mil.inventory_location_id (+);
SELECT item_instance_id
FROM AHL_VISITS_B
WHERE visit_id = c_visit_id;
SELECT object_id, subject_id, position_reference, level, relationship_id csi_ii_relationship_id,
object_version_number csi_ii_object_version_number
FROM csi_ii_relationships
START WITH object_id = c_instance_id
AND relationship_type_code = 'COMPONENT-OF'
AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
CONNECT BY PRIOR subject_id = object_id
AND relationship_type_code = 'COMPONENT-OF'
AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
ORDER BY level;
SELECT vst.visit_Id
FROM ahl_visits_b vst, ahl_visit_tasks_b vts
WHERE
vst.project_id = c_project_id
and vst.visit_id = vts.visit_id
and vts.project_task_id = c_project_task_id
and rownum < 2;
SELECT VST.Visit_Id
FROM AHL_WORKORDERS WO, AHL_VISIT_TASKS_B VTS, AHL_VISITS_B VST
WHERE
WO.wip_entity_id = c_wip_entity_id
AND VTS.visit_task_id = WO.visit_task_id
AND VST.visit_id = VTS.visit_id;