[Home] [Help]
182: CURSOR get_ue_details(c_mc_id in NUMBER,
183: c_start_date in DATE,
184: c_end_date in DATE) IS
185: SELECT une.unit_effectivity_id, une.due_date, une.latest_due_date, une.csi_item_instance_id, nvl(mrh.service_category_rank,0)
186: FROM ahl_unit_effectivities_b une, ahl_mr_headers_b mrh, ahl_unit_config_headers uch,ahl_mc_headers_b mc
187: WHERE mc.mc_id = c_mc_id
188: AND uch.master_config_id = mc.mc_header_id --tchimira :: 21 May 2012 :: compare correct columns
189: AND une.csi_item_instance_id = uch.csi_item_instance_id
190: AND (une.status_code ='INIT-DUE' OR une.status_code IS NULL)
606:
607: -- Get the Visits corresponding to the Start and End dates and Master configuration ids provided.
608: CURSOR get_visit_ids(c_snapshot_id IN NUMBER) IS
609: SELECT visit.visit_id, visit.object_version_number
610: FROM ahl_visits_b visit, ahl_unit_config_headers uch, ahl_autovisit_hierarchy avh, ahl_autovst_snpsht_hdr snap,ahl_mc_headers_b mc
611: WHERE visit.start_date_time IS NOT null AND visit.close_date_time IS NOT NULL
612: AND start_date_time >= snap.from_date
613: AND close_date_time <= snap.to_date
614: AND visit.status_code = L_STATUS_CODE
1141: c_end_date IN DATE) is
1142: SELECT ue.unit_effectivity_id, ue.due_date, ue.csi_item_instance_id, mr.mr_header_id,
1143: mr_vtyp.mr_visit_type_code, vtyp.estimated_duration
1144: FROM ahl_unit_effectivities_b ue, ahl_mr_headers_b mr, ahl_mr_visit_types_app_v mr_vtyp, ahl_visit_types_b vtyp,
1145: AHL_UNIT_CONFIG_HEADERS unit, ahl_mc_headers_b mc
1146: WHERE ue.csi_item_instance_id = unit.csi_item_instance_id
1147: AND unit.master_config_id = mc.mc_header_id
1148: AND unit.unit_config_status_code NOT IN ('QUARANTINE', 'EXPIRED')
1149: AND mc.mc_id = c_mc_id