[Home] [Help]
26: ,c_effective_date IN Date) IS
27: SELECT pdf.segment3 NFC_Agency_Code,
28: pdf.segment4 Personnel_Office_ID,
29: pdf.segment7 Grade
30: FROM hr_all_positions_f pos, per_position_definitions pdf
31: WHERE pos.position_definition_id = pdf.position_definition_id
32: AND pos.position_id = c_position_id
33: AND c_effective_date between pos.effective_start_date and pos.effective_end_date;
34:
39: CURSOR csr_master_position(cp_position_id NUMBER
40: ,cp_effective_date DATE
41: ,cp_business_group_id NUMBER ) IS
42: SELECT 'X'
43: FROM hr_all_positions_f hap
44: WHERE hap.position_id =cp_position_id
45: AND cp_effective_date BETWEEN hap.effective_start_date
46: AND hap.effective_end_date
47: AND hap.business_group_id =cp_business_group_id
53: CURSOR csr_detail_position(cp_position_id NUMBER
54: ,cp_effective_date DATE
55: ,cp_business_group_id NUMBER ) IS
56: SELECT 'X'
57: FROM hr_all_positions_f hap
58: WHERE hap.position_id =cp_position_id
59: AND cp_effective_date BETWEEN hap.effective_start_date
60: AND hap.effective_end_date
61: AND hap.business_group_id =cp_business_group_id
1748: ,hap.effective_end_date
1749: ,permanent_temporary_flag
1750: ,HR_GENERAL.DECODE_AVAILABILITY_STATUS(hap.availability_status_id) status
1751: ,hap.location_id
1752: FROM hr_all_positions_f hap
1753: WHERE cp_eff_date BETWEEN hap.effective_start_date
1754: AND hap.effective_end_date
1755: AND hap.position_id=p_position_id
1756: AND hap.business_group_id = p_business_group_id;
1773: SELECT hap.effective_start_date st_date
1774: ,hap.effective_end_date e_date
1775: ,hap.creation_date
1776: ,HR_GENERAL.DECODE_AVAILABILITY_STATUS(hap.availability_status_id) status
1777: FROM hr_all_positions_f hap
1778: WHERE hap.position_id=p_position_id
1779: AND hap.business_group_id = p_business_group_id
1780: AND hap.effective_start_date <= p_eff_dt
1781: ORDER BY hap.effective_start_date asc;
1779: AND hap.business_group_id = p_business_group_id
1780: AND hap.effective_start_date <= p_eff_dt
1781: ORDER BY hap.effective_start_date asc;
1782:
1783: CURSOR c_avbl_status_history(c_position_id hr_all_positions_f.position_id%type,
1784: c_eff_start_date ghr_pa_history.effective_date%type,
1785: c_eff_end_date ghr_pa_history.effective_date%type)
1786: IS
1787: SELECT 1
1786: IS
1787: SELECT 1
1788: FROM ghr_pa_history hist
1789: WHERE hist.information1 = to_char(c_position_id)
1790: AND hist.table_name = 'HR_ALL_POSITIONS_F'
1791: AND (hist.effective_date BETWEEN c_eff_start_date AND c_eff_end_date
1792: OR TRUNC(hist.process_date) BETWEEN TRUNC(c_eff_start_date) AND TRUNC(c_eff_end_date))
1793: AND EXISTS(
1794: SELECT 1 FROM ghr_pa_history hist1
1792: OR TRUNC(hist.process_date) BETWEEN TRUNC(c_eff_start_date) AND TRUNC(c_eff_end_date))
1793: AND EXISTS(
1794: SELECT 1 FROM ghr_pa_history hist1
1795: WHERE hist1.information1 = to_char(c_position_id)
1796: AND hist1.table_name = 'HR_ALL_POSITIONS_F'
1797: AND hist1.effective_date > hist.effective_date
1798: and hist.pa_history_id > hist1.pa_history_id
1799: AND HR_GENERAL.DECODE_AVAILABILITY_STATUS(hist1.information24) = 'Eliminated') ;
1800:
2051: CURSOR c_mast_pos(cp_position_id NUMBER
2052: ,cp_effective_date DATE
2053: ,cp_business_group_id NUMBER) IS
2054: SELECT hap.information6 mrn
2055: FROM hr_all_positions_f hap
2056: WHERE hap.position_id =cp_position_id
2057: AND cp_effective_date BETWEEN hap.effective_start_date
2058: AND hap.effective_end_date
2059: AND hap.business_group_id =cp_business_group_id;
2061: l_pos_ag_code VARCHAR2(30);
2062: l_count NUMBER;
2063: l_pos_title VARCHAR2(40);
2064: l_mrn VARCHAR2(15);
2065: l_position_id hr_all_positions_f.position_id%TYPE;
2066: l_mast_pos c_mast_pos%ROWTYPE;
2067: l_proc_name Varchar2(150) := g_proc_name ||'Get_Pos_KFF';
2068: l_position_number Varchar2(20);
2069:
2314: ,p_date_effective IN DATE
2315: ,p_record_indicator IN VARCHAR2) IS
2316: CURSOR c_job IS
2317: SELECT job.name
2318: FROM hr_all_positions_f pos,
2319: per_jobs job
2320: WHERE pos.position_id = p_position_id
2321: AND p_date_effective BETWEEN pos.effective_start_date
2322: AND pos.effective_end_date
2537: ,cp_effective_start_date DATE
2538: ,cp_effective_end_date DATE) IS
2539: SELECT hap.effective_start_date
2540: ,hap.effective_end_date
2541: FROM hr_all_positions_f hap
2542: WHERE hap.position_id = cp_position_id
2543: AND (cp_effective_end_date BETWEEN hap.effective_start_date
2544: AND hap.effective_end_date
2545: OR hap.effective_start_date BETWEEN cp_effective_start_date
2931: -- for the position extract.
2932: -- =============================================================================
2933: FUNCTION Position_Sub_Header_Criteria
2934: (p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
2935: ,p_position_id IN hr_all_positions_f.position_id%TYPE
2936: ,p_warning_message OUT NOCOPY Varchar2
2937: ,p_error_message OUT NOCOPY Varchar2
2938: ) RETURN Varchar2 IS
2939:
3138: l_proc_name Varchar2(150) := g_proc_name ||'Person_Main_Criteria';
3139: i per_all_assignments_f.business_group_id%TYPE;
3140: l_return_value Varchar2(2) :='Y';
3141: l_conc_reqest_id ben_ext_rslt.request_id%TYPE;
3142: l_position_id hr_all_positions_f.position_id%TYPE;
3143: l_value Varchar2(150) ;
3144:
3145: BEGIN
3146: Hr_Utility.set_location('Entering: '||l_proc_name, 5);