[Home] [Help]
259: CURSOR get_visit_cur (c_dept_id IN NUMBER)
260: IS
261: SELECT visit_id,TRUNC(start_date_time),
262: TRUNC(close_date_time)
263: from ahl_visits_vl
264: WHERE department_id = c_dept_id
265: AND start_date_time IS NOT NULL
266: -- anraj:Do not consider visits which are deleted or cancelled
267: AND status_code NOT IN('DELETED','CANCELLED');
367: --
368: CURSOR get_visit_cur (c_dept_id IN NUMBER)
369: IS
370: SELECT visit_id,TRUNC(start_date_time),TRUNC(close_date_time)
371: FROM ahl_visits_vl
372: WHERE department_id = c_dept_id
373: AND start_date_time IS NOT NULL
374: -- anraj:Do not consider visits which are deleted or cancelled
375: AND status_code NOT IN('DELETED','CANCELLED');
763: --
764: --SELECT Clause
765: l_sql_string := 'select distinct(a.department_id),b.description, b.department_code,c.name';
766: -- From Clause
767: l_sql_string := l_sql_string || ' from ahl_visits_vl a , bom_departments b, hr_all_organization_units c';
768: -- Where Clause
769: l_sql_string := l_sql_string || ' where visit_id not in (select visit_id from ahl_space_assignments)';
770: l_sql_string := l_sql_string || ' and a.department_id = b.department_id';
771: l_sql_string := l_sql_string || ' and a.organization_id = c.organization_id';
1816:
1817: --SELECT Clause
1818: l_sql_string := 'select distinct(a.department_id),b.description, b.department_code,c.name';
1819: -- From Clause
1820: l_sql_string := l_sql_string || ' from ahl_visits_vl a , bom_departments b, hr_all_organization_units c';
1821: -- Where Clause
1822: l_sql_string := l_sql_string || ' where visit_id not in (select visit_id from ahl_space_assignments)';
1823: l_sql_string := l_sql_string || ' and a.department_id = b.department_id';
1824: l_sql_string := l_sql_string || ' and a.organization_id = c.organization_id';
2883:
2884: --SELECT Clause
2885: l_sql_string := 'select distinct(a.department_id),b.description, b.department_code,c.name';
2886: -- From Clause
2887: l_sql_string := l_sql_string || ' from ahl_visits_vl a , bom_departments b, hr_all_organization_units c';
2888: -- Where Clause
2889: l_sql_string := l_sql_string || ' where visit_id not in (select visit_id from ahl_space_assignments)';
2890: l_sql_string := l_sql_string || ' and a.department_id = b.department_id';
2891: l_sql_string := l_sql_string || ' and a.organization_id = c.organization_id';
5269: WHERE A.UNIT_EFFECTIVITY_ID = B.UNIT_EFFECTIVITY_ID
5270: AND B.VISIT_ID = VST.VISIT_ID
5271: GROUP BY VISIT_ID
5272: ) due_by
5273: FROM AHL_VISITS_VL VST,
5274: CSI_ITEM_INSTANCES CSI ,
5275: MTL_SYSTEM_ITEMS_B_KFV mtl,
5276: FND_LOOKUP_VALUES_VL FLVT,
5277: ahl_space_assignments SPS
5306: WHERE A.UNIT_EFFECTIVITY_ID = B.UNIT_EFFECTIVITY_ID
5307: AND B.VISIT_ID = VST.VISIT_ID
5308: GROUP BY VISIT_ID
5309: ) due_by
5310: FROM AHL_VISITS_VL VST,
5311: CSI_ITEM_INSTANCES CSI ,
5312: MTL_SYSTEM_ITEMS_B_KFV mtl,
5313: FND_LOOKUP_VALUES_VL FLVT
5314: where VST.ITEM_INSTANCE_ID = CSI.INSTANCE_ID(+)
5335: b.simulation_plan_id,
5336: visit_type_mean,
5337: a.start_date_time, due_by,
5338: trunc(b.close_date_time) close_date_time
5339: FROM ahl_visit_details_v a , ahl_visits_vl b
5340: WHERE a.visit_id = c_visit_id
5341: AND a.visit_id = b.visit_id
5342: AND a.simulation_plan_id = c_plan_id
5343: AND (((trunc(b.start_date_time) between trunc(c_start_period) and trunc(c_end_period))
5369: visit_type_mean,
5370: b.start_date_time, due_by,
5371: trunc(b.CLOSE_DATE_TIME) close_date_time
5372: FROM ahl_visit_details_v a ,
5373: ahl_visits_vl b
5374: WHERE a.visit_id = b.visit_id
5375: AND a.simulation_plan_id in (select simulation_plan_id
5376: from ahl_simulation_plans_vl where primary_plan_flag = 'Y')
5377: AND a.visit_id = c_visit_id
5431: IS
5432: SELECT visit_id, visit_name, visit_type_code,
5433: trunc(start_date_time) start_date_time,
5434: trunc(close_date_time) close_date_time
5435: FROM ahl_visits_vl
5436: WHERE department_id = c_dept_id
5437: AND start_date_time IS NOT NULL;
5438: --
5439: -- AnRaj: Split the cursor, perf bug 5208300, index was not being hit because of logical OR