[Home] [Help]
584: ,p_job_id out nocopy number) IS
585:
586: CURSOR csr_position IS
587: SELECT organization_id, job_id
588: FROM hr_positions_f
589: WHERE position_id = p_pos_id
590: AND TRUNC(SYSDATE) BETWEEN effective_start_date
591: AND effective_end_date;
592:
769: ,p_where_clause => l_flex_where_clause);
770:
771: l_dynamic_sql := 'SELECT pp.position_id,'
772: ||l_flex_select_clause
773: ||' FROM hr_positions_f pp, per_position_definitions ppd'
774: ||' WHERE TRUNC(SYSDATE) BETWEEN pp.effective_start_date'
775: ||' AND pp.effective_end_date'
776: ||' AND pp.position_definition_id = ppd.position_definition_id'
777: ||' AND pp.position_id IN ('
1062: ||','|| l_pos_flex_select_clause
1063: ||' FROM per_assignments_f paf'
1064: ||' ,per_people_f ppf'
1065: ||' ,per_person_type_usages_f ptu'
1066: ||' ,hr_positions_f pp'
1067: ||' ,per_position_definitions ppd'
1068: ||' WHERE paf.business_group_id = '
1069: ||l_business_group
1070: ||' AND paf.assignment_type = '||l_assignment_type
1234: ||' AND UPPER(name) '
1235: || l_filter_clause;
1236: ELSIF p_search_type = g_position_type THEN
1237: l_dynamic_sql := 'SELECT position_id, name'
1238: ||' FROM hr_positions_f'
1239: ||' WHERE business_group_id = '
1240: ||l_business_group
1241: ||' AND TRUNC(sysdate) BETWEEN effective_start_date'
1242: ||' AND effective_end_date'
1359: AND hou.location_id = hl.location_id(+);
1360:
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
2709: CURSOR csr_succession_by_person IS
2710: SELECT * FROM (
2711: SELECT psp.position_id, pp.name
2712: FROM per_succession_planning psp,
2713: hr_positions_f pp
2714: WHERE psp.person_id = p_person_id
2715: AND p_effective_date <= NVL(psp.end_date, p_effective_date)
2716: AND psp.position_id = pp.position_id
2717: AND TRUNC(p_effective_date) BETWEEN pp.effective_start_date
2717: AND TRUNC(p_effective_date) BETWEEN pp.effective_start_date
2718: AND pp.effective_end_date
2719: UNION
2720: SELECT pp.position_id, pp.name
2721: FROM hr_positions_f pp
2722: WHERE pp.position_id = (SELECT pp2.successor_position_id
2723: FROM hr_positions_f pp2,
2724: per_assignments_f paf
2725: WHERE paf.person_id = p_person_id
2719: UNION
2720: SELECT pp.position_id, pp.name
2721: FROM hr_positions_f pp
2722: WHERE pp.position_id = (SELECT pp2.successor_position_id
2723: FROM hr_positions_f pp2,
2724: per_assignments_f paf
2725: WHERE paf.person_id = p_person_id
2726: AND p_effective_date BETWEEN paf.effective_start_date
2727: AND paf.effective_end_date
2759: CURSOR csr_deployment_by_person IS
2760: SELECT pasf.assignment_id, pasf.position_id, pasf.grade_id, pp.name
2761: FROM per_assignments_f pasf,
2762: per_assignment_status_types past,
2763: hr_positions_f pp
2764: WHERE pasf.person_id = p_person_id
2765: AND p_effective_date BETWEEN pasf.effective_start_date
2766: AND pasf.effective_end_date
2767: AND pasf.position_id = pp.position_id
2946: ,ppf.full_name name
2947: ,hr_person_type_usage_info.get_user_person_type
2948: (p_effective_date, ppf.person_id) type
2949: ,ppf.order_name
2950: from hr_positions_f ps,
2951: per_assignments_f paf
2952: ,per_people_f ppf
2953: where ps.successor_position_id = p_pos_id
2954: AND p_effective_date BETWEEN ps.effective_start_date
2983: ,hr_person_type_usage_info.get_user_person_type
2984: (p_effective_date, ppf.person_id) type
2985: ,ppf.last_name
2986: ,ppf.first_name
2987: from hr_positions_f ps,
2988: per_assignments_f paf
2989: ,per_people_f ppf
2990: where ps.successor_position_id = p_pos_id
2991: AND p_effective_date BETWEEN ps.effective_start_date