1 PACKAGE BODY PER_VALID_GRADES_PKG as
2 /* $Header: pevgr01t.pkb 120.0.12020000.2 2012/07/05 05:06:31 amnaraya ship $ */
3 --
4 procedure get_grade(p_grade_id in number,
5 p_grade in out nocopy varchar2) is
6 --
7 cursor c1 is select name
8 from per_grades_vl -- Bug 13538388
9 where p_grade_id = grade_id;
10 --
11 begin
12 --
13 -- Retrieve the grade name
14 --
15 open c1;
16 fetch c1 into p_grade;
17 if (C1%NOTFOUND) then
18 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
19 hr_utility.set_message_token('PROCEDURE','get_grade');
20 hr_utility.set_message_token('STEP','1');
21 end if;
22 close c1;
23 --
24 hr_utility.set_location('PER_VALID_GRADES_PKG.get_grade', 1);
25 --
26 end get_grade;
27 --
28 procedure get_next_sequence(p_valid_grade_id in out nocopy number) is
29 --
30 cursor c1 is select per_valid_grades_s.nextval
31 from sys.dual;
32 --
33 begin
34 --
35 -- Retrieve the next sequence number for valid_grade_id
36 --
37 if (p_valid_grade_id is null) then
38 open c1;
39 fetch c1 into p_valid_grade_id;
40 if (C1%NOTFOUND) then
41 CLOSE C1;
42 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
43 hr_utility.set_message_token('PROCEDURE','get_next_sequence');
44 hr_utility.set_message_token('STEP','1');
45 end if;
46 close c1;
47 end if;
48 --
49 hr_utility.set_location('PER_VALID_GRADES_PKG.get_next_sequence', 1);
50 --
51 end get_next_sequence;
52 --
53 PROCEDURE check_unique_grade(
54 p_business_group_id in number,
55 p_job_id in number,
56 p_grade_id in number,
57 p_rowid in varchar2,
58 p_date_from in date, -- Bug 3338072
59 p_date_to in date default null) is -- Bug 3338072
60 --
61
62 cursor csr_grade is
63 select null
64 from per_valid_grades vg
65 where ((p_rowid is not null and
66 rowidtochar(rowid) <> p_rowid)
67 or p_rowid is null)
68 and vg.business_group_id + 0 = p_business_group_id
69 and vg.job_id = p_job_id
70 and vg.grade_id = p_grade_id
71 -- Bug 3338072 Start
72 and (p_date_from between
73 vg.date_from and nvl(vg.date_to,hr_api.g_eot) or
74 p_date_to between
75 vg.date_from and nvl(vg.date_to,hr_api.g_eot));
76 -- Bug 3338072 End
77
78 --
79 g_dummy_number number;
80 v_not_unique boolean := FALSE;
81 --
82 -- Check the grade name is unique
83 --
84 begin
85 --
86 open csr_grade;
87 fetch csr_grade into g_dummy_number;
88 v_not_unique := csr_grade%FOUND;
89 close csr_grade;
90 --
91 if v_not_unique then
92 hr_utility.set_message(801,'PER_7818_DEF_JOB_GRD_EXISTS');
93 hr_utility.raise_error;
94 end if;
95 --
96 hr_utility.set_location('PER_VALID_GRADES_PKG.check_unique_grade', 1);
97 --
98 end check_unique_grade;
99 --
100 PROCEDURE check_date_from(p_grade_id number,
101 p_date_from date) is
102 --
103 cursor csr_grade_date is select g.date_from
104 from per_grades g
105 where g.grade_id = p_grade_id
106 and p_date_from < g.date_from;
107 --
108 g_dummy_date date;
109 v_date_greater boolean := FALSE;
110 --
111 -- Check that date_from of valid grade must be after the date_from of
112 -- the job and grade
113 --
114 begin
115 --
116 open csr_grade_date;
117 fetch csr_grade_date into g_dummy_date;
118 v_date_greater := csr_grade_date%FOUND;
119 close csr_grade_date;
120 --
121 if v_date_greater then
122 hr_utility.set_message(801,'PER_7821_DEF_JOB_GRD_START_GRD');
123 hr_utility.set_message_token('DATE', g_dummy_date);
124 hr_utility.raise_error;
125 end if;
126 --
127 hr_utility.set_location('PER_VALID_GRADES_PKG.check_date_from', 1);
128 --
129 end check_date_from;
130 --
131 PROCEDURE check_date_to(p_grade_id number,
132 p_date_to date,
133 p_end_of_time date) is
134 --
135 cursor csr_grade_date is select nvl(g.date_to, p_end_of_time)
136 from per_grades g
137 where g.grade_id = p_grade_id
138 and nvl(p_date_to, p_end_of_time) >
139 nvl(g.date_to, p_end_of_time);
140 --
141 g_dummy_date date;
142 v_date_greater boolean := FALSE;
143 --
144 -- Check that date_to of valid grade is after date_to in per_grades
145 --
146 begin
147 --
148 open csr_grade_date;
149 fetch csr_grade_date into g_dummy_date;
150 v_date_greater := csr_grade_date%FOUND;
151 close csr_grade_date;
152 --
153 if v_date_greater then
154 hr_utility.set_message(801,'PER_7872_DEF_GRD_POS_END_POS');
155 hr_utility.set_message_token('DATE', g_dummy_date);
156 hr_utility.raise_error;
157 end if;
158 --
159 hr_utility.set_location('PER_VALID_GRADES_PKG.check_date_to', 1);
160 --
161 end check_date_to;
162 --
163 END PER_VALID_GRADES_PKG;
[Home] [Help]