734: l_business_group := hr_util_misc_web.get_business_group_id;
735:
736: IF p_search_type = g_location_type THEN --location
737: l_dynamic_sql := 'SELECT location_id, location_code'
738: ||' FROM hr_locations_all'
739: ||' WHERE location_id IN ('
740: ||p_ids
741: ||')';
742: ELSIF p_search_type = g_organization_type THEN --org
948: ||' ,hl.location_code name'
949: ||' FROM per_assignments_f paf'
950: ||' ,per_people_f ppf'
951: ||' ,per_person_type_usages_f ptu'
952: ||' ,hr_locations_all hl'
953: ||' WHERE paf.business_group_id = '
954: ||l_business_group
955: ||' AND paf.assignment_type = '||l_assignment_type
956: ||' AND ppf.person_id = ptu.person_id '
1200: END IF;
1201: ELSE
1202: IF p_search_type = g_location_type THEN
1203: l_dynamic_sql := 'SELECT location_id, location_code'
1204: ||' FROM hr_locations_all'
1205: ||' WHERE UPPER(location_code) '
1206: || l_filter_clause;
1207: ELSIF p_search_type = g_organization_type THEN
1208: l_dynamic_sql := 'SELECT distinct(hou.organization_id), hou.name'
1353:
1354: CURSOR csr_org_info IS
1355: SELECT hou.name, hl.location_code
1356: FROM hr_organization_units hou,
1357: hr_locations_all hl
1358: WHERE hou.organization_id = p_id
1359: AND hou.location_id = hl.location_id(+);
1360:
1361: CURSOR csr_pos_info IS
1361: CURSOR csr_pos_info IS
1362: SELECT hou.name, hl.location_code
1363: FROM hr_positions_f pp,
1364: hr_organization_units hou,
1365: hr_locations_all hl
1366: WHERE pp.position_id = p_id
1367: AND TRUNC(SYSDATE) BETWEEN pp.effective_start_date
1368: AND pp.effective_end_date
1369: AND pp.organization_id = hou.organization_id
1375: ,hl.location_code
1376: FROM per_vacancies pv,
1377: per_requisitions pr,
1378: hr_organization_units hou,
1379: hr_locations_all hl
1380: WHERE pv.vacancy_id = p_id
1381: AND pv.requisition_id = pr.requisition_id
1382: AND pv.organization_id = hou.organization_id(+)
1383: AND hou.location_id = hl.location_id(+);