[Home] [Help]
182: CURSOR ahl_visit_csr(p_ue_id IN NUMBER) IS
183: SELECT vst.start_date_time, vst.visit_number
184: FROM ahl_visit_tasks_b tsk,
185: (select vst1.* from
186: ahl_visits_b vst1, ahl_simulation_plans_b sim
187: where vst1.simulation_plan_id = sim.simulation_plan_id
188: and sim.primary_plan_flag = 'Y'
189: UNION ALL
190: select vst1.* from
199: CURSOR ahl_visit_csr(p_ue_id IN NUMBER, p_visit_num IN VARCHAR2, p_visit_org_name IN VARCHAR2, p_visit_dept_name IN VARCHAR2) IS
200: SELECT vst.start_date_time, vst.visit_number, vst.visit_id
201: FROM ahl_visit_tasks_b tsk,
202: (select vst1.* from
203: ahl_visits_b vst1, ahl_simulation_plans_b sim
204: where vst1.simulation_plan_id = sim.simulation_plan_id
205: and sim.primary_plan_flag = 'Y'
206: UNION ALL
207: select vst1.* from
838: --amsriniv Begin
839: --Visit Number
840: --Set when calling for UMP Search when not in PM Mode.
841: IF p_search_mr_instance_rec.visit_number is NOT NULL THEN
842: l_sql_string := l_sql_string || ' and exists (select ''x'' from ahl_visits_b vst1,ahl_simulation_plans_b sim, ahl_visit_tasks_b tsk where vst1.simulation_plan_id = sim.simulation_plan_id(+) ';
843: l_sql_string := l_sql_string || 'and sim.primary_plan_flag(+) = ''Y'' and vst1.visit_number like :VISIT_NUMBER and vst1.visit_id = tsk.visit_id AND NVL(vst1.status_code,''x'') NOT ';
844: l_sql_string := l_sql_string || 'IN (''DELETED'',''CANCELLED'') AND NVL(tsk.status_code,''x'') NOT IN (''DELETED'',''CANCELLED'') AND tsk.unit_effectivity_id = UMP.unit_effectivity_id) ' ;
845: l_bindvar_tbl(l_bind_index) := p_search_mr_instance_rec.visit_number;
846: l_bind_index := l_bind_index + 1;