[Home] [Help]
894: -- Contrast this with the earlier cursor valid_deferral_csr, which handles SRs with no MRs and plain MRs
895: CURSOR valid_sr_deferral_csr (p_ue_id in number)
896: IS
897: SELECT 'x'
898: FROM ahl_workorders WO, ahl_visits_b VS, ahl_visit_tasks_b VST, ahl_unit_effectivities_b UE
899: WHERE WO.master_workorder_flag = 'Y'
900: /* to filter out draft / deleted WOs */
901: AND WO.STATUS_CODE NOT IN ( '17' , '22' )
902: /* to check whether visit available in client's organization */
921: FROM AHL_Unit_Effectivities_B UE
922: WHERE status_code IS NULL OR status_code = 'INIT-DUE'
923: AND unit_effectivity_id = p_unit_effectivity_id
924: AND NOT EXISTS (SELECT 'x'
925: FROM ahl_visit_tasks_b vts
926: WHERE vts.unit_effectivity_id = UE.unit_effectivity_id
927: AND NVL(vts.status_code,'x') IN ('PLANNED')
928: AND EXISTS (select 'x'
929: from ahl_visits_b vst, ahl_simulation_plans_b sim
3707: WHERE WO.job_status_code NOT IN ( G_JOB_DRAFT,G_JOB_DELETED)
3708: AND WO.unit_effectivity_id = p_unit_effectivity_id; */
3709:
3710: SELECT WO.workorder_id
3711: FROM ahl_workorders wo, ahl_visit_tasks_b vts,
3712: ahl_visits_b vst,
3713: (SELECT ORGANIZATION_ID FROM INV_ORGANIZATION_INFO_V
3714: WHERE NVL (operating_unit, mo_global.get_current_org_id()) = mo_global.get_current_org_id()) ORG
3715: WHERE wo.visit_task_id = vts.visit_task_id
3730: WO.status_code,
3731: WO.actual_start_date,
3732: WO.actual_end_date,
3733: WO.workorder_name
3734: FROM ahl_workorders WO , ahl_unit_effectivities_b UE, ahl_visit_tasks_b VST
3735: WHERE WO.status_code NOT IN ( G_JOB_DRAFT,G_JOB_DELETED)
3736: AND WO.master_workorder_flag = 'N'
3737: AND WO.visit_task_id = VST.visit_task_id
3738: AND VST.unit_effectivity_id = UE.unit_effectivity_id
3743: WO.status_code,
3744: WO.actual_start_date,
3745: WO.actual_end_date,
3746: WO.workorder_name
3747: FROM ahl_workorders WO , ahl_unit_effectivities_b UE, ahl_visit_tasks_b VST
3748: WHERE WO.status_code NOT IN ( G_JOB_DRAFT,G_JOB_DELETED)
3749: AND WO.master_workorder_flag = 'N'
3750: AND WO.visit_task_id = VST.visit_task_id
3751: AND VST.unit_effectivity_id = UE.unit_effectivity_id
4795:
4796: CURSOR prior_child_defer_to_ue_csr(p_unit_effectivity_id IN NUMBER) IS
4797: SELECT UE.unit_effectivity_id FROM ahl_unit_effectivities_b UE
4798: WHERE defer_from_ue_id IS NOT NULL
4799: --AND NOT EXISTS (Select 'x' from ahl_visit_tasks_b VST where VST.unit_effectivity_id = UE.unit_effectivity_id)
4800: AND defer_from_ue_id IN (
4801: SELECT related_ue_id
4802: FROM AHL_UE_RELATIONSHIPS
4803: WHERE relationship_code = 'PARENT'
4805: CONNECT BY ue_id = PRIOR related_ue_id
4806: );
4807:
4808: CURSOR assigned_to_visit_csr(p_unit_effectivity_id IN NUMBER) IS
4809: SELECT 'x' FROM ahl_visit_tasks_b VST
4810: WHERE VST.unit_effectivity_id = p_unit_effectivity_id;
4811:
4812: l_exists VARCHAR2(1);
4813:
5015: AND lookup_type = 'AHL_PRD_MR_STATUS';
5016:
5017: -- to fetch visit info
5018: CURSOR visit_info_csr(p_unit_effectivity_id IN NUMBER) IS
5019: SELECT VS.visit_id, VS.visit_number FROM ahl_visits_b VS,ahl_visit_tasks_b VST
5020: WHERE VST.visit_id = VS.visit_id
5021: AND VST.unit_effectivity_id = p_unit_effectivity_id;
5022:
5023: -- to check whether MR or any of its children has resettable counters
5446: */
5447:
5448: SELECT 'x'
5449: FROM ahl_workorders wo, ahl_unit_deferrals_b udf,
5450: ahl_visit_tasks_b vts, ahl_visits_b vst,
5451: (SELECT ORGANIZATION_ID
5452: FROM INV_ORGANIZATION_INFO_V
5453: WHERE NVL (operating_unit, mo_global.get_current_org_id()) = mo_global.get_current_org_id()) ORG
5454: WHERE WO.VISIT_TASK_ID=VTS.VISIT_TASK_ID