[Home] [Help]
920: -- Contrast this with the earlier cursor valid_deferral_csr, which handles SRs with no MRs and plain MRs
921: CURSOR valid_sr_deferral_csr (p_ue_id in number)
922: IS
923: SELECT 'x'
924: FROM ahl_workorders WO, ahl_visits_b VS, ahl_visit_tasks_b VST, ahl_unit_effectivities_b UE
925: WHERE WO.master_workorder_flag = 'Y'
926: /* to filter out draft / deleted WOs */
927: AND WO.STATUS_CODE NOT IN ( '17' , '22' )
928: /* to check whether visit available in client's organization */
947: FROM AHL_Unit_Effectivities_B UE
948: WHERE (status_code IS NULL OR status_code = 'INIT-DUE')
949: AND unit_effectivity_id = p_unit_effectivity_id
950: AND NOT EXISTS (SELECT 'x'
951: FROM ahl_visit_tasks_b vts
952: WHERE vts.unit_effectivity_id = UE.unit_effectivity_id
953: AND NVL(vts.status_code,'x') IN ('PLANNING')
954: AND vts.task_type_code = 'SUMMARY'
955: AND EXISTS (select 'x'
1004: -- to detemine whether deferral is done from UMP
1005: CURSOR wo_exists_csr(p_unit_effectivity_id IN NUMBER) IS
1006: SELECT 'x'
1007: FROM ahl_workorders wo,
1008: ahl_visit_tasks_b vts
1009: WHERE WO.VISIT_TASK_ID=VTS.VISIT_TASK_ID
1010: AND wo.STATUS_CODE NOT IN ( '17' , '22' )
1011: AND vts.unit_effectivity_id = p_unit_effectivity_id
1012: AND rownum < 2;
3863: WHERE WO.job_status_code NOT IN ( G_JOB_DRAFT,G_JOB_DELETED)
3864: AND WO.unit_effectivity_id = p_unit_effectivity_id; */
3865:
3866: SELECT WO.workorder_id
3867: FROM ahl_workorders wo, ahl_visit_tasks_b vts,
3868: ahl_visits_b vst,
3869: (SELECT ORGANIZATION_ID FROM INV_ORGANIZATION_INFO_V
3870: WHERE NVL (operating_unit, mo_global.get_current_org_id()) = mo_global.get_current_org_id()) ORG
3871: WHERE wo.visit_task_id = vts.visit_task_id
3886: WO.status_code,
3887: WO.actual_start_date,
3888: WO.actual_end_date,
3889: WO.workorder_name
3890: FROM ahl_workorders WO , ahl_unit_effectivities_b UE, ahl_visit_tasks_b VST
3891: WHERE WO.status_code NOT IN ( G_JOB_DRAFT,G_JOB_DELETED)
3892: AND WO.master_workorder_flag = 'N'
3893: AND WO.visit_task_id = VST.visit_task_id
3894: AND VST.unit_effectivity_id = UE.unit_effectivity_id
3899: WO.status_code,
3900: WO.actual_start_date,
3901: WO.actual_end_date,
3902: WO.workorder_name
3903: FROM ahl_workorders WO , ahl_unit_effectivities_b UE, ahl_visit_tasks_b VST
3904: WHERE WO.status_code NOT IN ( G_JOB_DRAFT,G_JOB_DELETED)
3905: AND WO.master_workorder_flag = 'N'
3906: AND WO.visit_task_id = VST.visit_task_id
3907: AND VST.unit_effectivity_id = UE.unit_effectivity_id
5173:
5174: CURSOR prior_child_defer_to_ue_csr(p_unit_effectivity_id IN NUMBER) IS
5175: SELECT UE.unit_effectivity_id FROM ahl_unit_effectivities_b UE
5176: WHERE defer_from_ue_id IS NOT NULL
5177: --AND NOT EXISTS (Select 'x' from ahl_visit_tasks_b VST where VST.unit_effectivity_id = UE.unit_effectivity_id)
5178: AND defer_from_ue_id IN (
5179: SELECT related_ue_id
5180: FROM AHL_UE_RELATIONSHIPS
5181: WHERE relationship_code = 'PARENT'
5183: CONNECT BY ue_id = PRIOR related_ue_id
5184: );
5185:
5186: CURSOR assigned_to_visit_csr(p_unit_effectivity_id IN NUMBER) IS
5187: SELECT 'x' FROM ahl_visit_tasks_b VST
5188: WHERE VST.unit_effectivity_id = p_unit_effectivity_id;
5189:
5190: l_exists VARCHAR2(1);
5191:
5398: AND lookup_type = 'AHL_UNIT_EFFECTIVITY_STATUS';
5399:
5400: -- to fetch visit info
5401: CURSOR visit_info_csr(p_unit_effectivity_id IN NUMBER) IS
5402: SELECT VS.visit_id, VS.visit_number FROM ahl_visits_b VS,ahl_visit_tasks_b VST
5403: WHERE VST.visit_id = VS.visit_id
5404: AND VST.unit_effectivity_id = p_unit_effectivity_id;
5405:
5406: -- to check whether MR or any of its children has resettable counters
5919: */
5920:
5921: SELECT 'x'
5922: FROM ahl_workorders wo, ahl_unit_deferrals_b udf,
5923: ahl_visit_tasks_b vts, ahl_visits_b vst,
5924: (SELECT ORGANIZATION_ID
5925: FROM INV_ORGANIZATION_INFO_V
5926: WHERE NVL (operating_unit, mo_global.get_current_org_id()) = mo_global.get_current_org_id()) ORG
5927: WHERE WO.VISIT_TASK_ID=VTS.VISIT_TASK_ID