[Home] [Help]
193: CURSOR ahl_visit_csr(p_ue_id IN NUMBER) IS
194: SELECT vst.start_date_time, vst.visit_number
195: FROM ahl_visit_tasks_b tsk,
196: (select vst1.* from
197: ahl_visits_b vst1, ahl_simulation_plans_b sim
198: where vst1.simulation_plan_id = sim.simulation_plan_id
199: and sim.primary_plan_flag = 'Y'
200: UNION ALL
201: select vst1.* from
210: 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
211: SELECT vst.start_date_time, vst.visit_number, vst.visit_id
212: FROM ahl_visit_tasks_b tsk,
213: (select vst1.* from
214: ahl_visits_b vst1, ahl_simulation_plans_b sim
215: where vst1.simulation_plan_id = sim.simulation_plan_id
216: and sim.primary_plan_flag = 'Y'
217: UNION ALL
218: select vst1.* from
920: --amsriniv Begin
921: --Visit Number
922: --Set when calling for UMP Search when not in PM Mode.
923: IF p_search_mr_instance_rec.visit_number is NOT NULL THEN
924: 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(+) ';
925: 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 ';
926: 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) ' ;
927: l_bindvar_tbl(l_bind_index) := p_search_mr_instance_rec.visit_number;
928: l_bind_index := l_bind_index + 1;