[Home] [Help]
PACKAGE BODY: APPS.PQH_FR_PROGRESSION_POINT_PKG
Source
1 Package Body pqh_fr_progression_point_pkg as
2 /* $Header: pqfrpspp.pkb 120.0.12000000.2 2007/02/27 13:31:56 spendhar noship $ */
3
4 PROCEDURE update_sal_rate_for_point(p_spinal_point_id in number,
5 p_parent_spine_id in number,
6 p_information_category in varchar2,
7 p_information1 in varchar2,
8 p_information1_o in varchar2,
9 p_information2 in varchar2,
10 p_information2_o in varchar2) IS
11
12 CURSOR csr_scale_type IS
13 SELECT information_category,information1
14 FROM per_parent_spines
15 WHERE parent_spine_id = p_parent_spine_id;
16
17 CURSOR csr_rate_ovn(p_grd_rule_id NUMBER) IS
18 SELECT object_version_number
19 FROM pay_grade_rules_f
20 WHERE grade_rule_id = p_grd_rule_id
21 AND TRUNC(SYSDATE) between effective_start_date and effective_end_date;
22
23 l_scale_type varchar2(30);
24 l_scale_info_catg varchar2(30);
25 l_hr_rate_id number(15);
26 l_acty_base_rt_id number(15);
27 l_opt_id number(15);
28 l_dt_upd_mode Varchar2(30);
29 l_new_rate number(22,5);
30 l_esd DATE;
31 l_eed DATE;
32 l_ovn NUMBER(9);
33 l_gross_index NUMBER(15);
34 l_sal_rate NUMBER(22,5);
35 l_gross_index_o NUMBER(15);
36 l_sal_rate_o NUMBER(22,5);
37
38 BEGIN
39 --
40 /* Added for GSI Bug 5472781 */
41 IF NOT hr_utility.chk_product_install('Oracle Human Resources', 'FR') THEN
42 hr_utility.set_location('Leaving : pqh_fr_progression_point_pkg.update_sal_rate_for_point' , 10);
43 return;
44 END IF;
45 --
46 OPEN csr_scale_type;
47 FETCH csr_scale_type INTO l_scale_info_catg,l_scale_type;
48 CLOSE csr_scale_type;
49 IF NVL(l_scale_info_catg,'X') = 'FR_PQH' AND NVL(p_information_category,'X') = 'FR_PQH' THEN
50 l_gross_index := p_information1;
51 l_sal_rate := p_information2;
52 l_gross_index_o := p_information1_o;
53 l_sal_rate_o := p_information2_o;
54
55 IF (NVL(l_gross_index,-1) <> NVL(l_gross_index_o, -1)) OR (NVL(l_sal_rate,-1) <> NVL(l_sal_rate_o,-1)) THEN
56 l_opt_id := pqh_gsp_hr_to_stage.get_opt_for_point(p_point_id => p_spinal_point_id,
57 p_effective_date => TRUNC(SYSDATE));
58 IF l_opt_id IS NULL THEN
59 RETURN;
60 END IF;
61 l_acty_base_rt_id := pqh_gsp_hr_to_stage.get_co_std_rate(p_opt_id => l_opt_id,
62 p_effective_date => TRUNC(SYSDATE),
63 p_pay_rule_id => l_hr_rate_id);
64 IF l_hr_rate_id IS NULL THEN
65 RETURN;
66 END IF;
67
68 OPEN csr_rate_ovn(l_hr_rate_id);
69 FETCH csr_rate_ovn INTO l_ovn;
70 IF csr_rate_ovn%NOTFOUND THEN
71 CLOSE csr_rate_ovn;
72 RETURN;
73 END IF;
74 CLOSE csr_rate_ovn;
75
76 IF l_scale_type = 'L' THEN
77 l_new_rate := pqh_corps_utility.get_salary_rate(p_gross_index => l_gross_index,
78 p_effective_date => TRUNC(SYSDATE));
79 ELSIF l_scale_type = 'E' THEN
80 l_new_rate := l_sal_rate;
81 ELSE
82 RETURN;
83 END IF;
84 pqh_fr_utility.get_datetrack_mode(p_effective_date => trunc(sysdate),
85 p_base_table_name => 'PAY_GRADE_RULES_F',
86 p_base_key_column => 'GRADE_RULE_ID',
87 p_base_key_value => l_hr_rate_id,
88 p_datetrack_mode => l_dt_upd_mode);
89
90 hr_rate_values_api.update_rate_value(p_grade_rule_id => l_hr_rate_id,
91 p_datetrack_mode => l_dt_upd_mode,
92 p_effective_date => TRUNC(SYSDATE),
93 p_value => l_new_rate,
94 p_object_version_number => l_ovn,
95 p_effective_start_date => l_esd,
96 p_effective_end_date => l_eed);
97
98 END IF;-- If gross index/sal. rate changed
99 END IF; -- if FR_PQH info. catg,
100 END update_sal_rate_for_point;
101
102 END pqh_fr_progression_point_pkg;