[Home] [Help]
Skip to content
PACKAGE BODY: APPS.HR_VALID_GRADE_API
Source
1 Package Body hr_valid_grade_api as
2 /* $Header: pevgrapi.pkb 120.1 2011/09/08 09:58:46 srannama ship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := ' hr_valid_grade_api.';
7 --
8 -- ----------------------------------------------------------------------------
9 -- |-------------------------< create_valid_grade >---------------------------|
10 -- ----------------------------------------------------------------------------
11 --
12 procedure create_valid_grade
13 (p_validate in boolean default false
14 ,p_grade_id in number
15 ,p_date_from in date
16 ,p_effective_date in date --Added for bug# 1760707
17 ,p_comments in varchar2 default null
18 ,p_date_to in date default null
19 ,p_job_id in number default null
20 ,p_position_id in number default null
21 ,p_attribute_category in varchar2 default null
22 ,p_attribute1 in varchar2 default null
23 ,p_attribute2 in varchar2 default null
24 ,p_attribute3 in varchar2 default null
25 ,p_attribute4 in varchar2 default null
26 ,p_attribute5 in varchar2 default null
27 ,p_attribute6 in varchar2 default null
28 ,p_attribute7 in varchar2 default null
29 ,p_attribute8 in varchar2 default null
30 ,p_attribute9 in varchar2 default null
31 ,p_attribute10 in varchar2 default null
32 ,p_attribute11 in varchar2 default null
33 ,p_attribute12 in varchar2 default null
34 ,p_attribute13 in varchar2 default null
35 ,p_attribute14 in varchar2 default null
36 ,p_attribute15 in varchar2 default null
37 ,p_attribute16 in varchar2 default null
38 ,p_attribute17 in varchar2 default null
39 ,p_attribute18 in varchar2 default null
40 ,p_attribute19 in varchar2 default null
41 ,p_attribute20 in varchar2 default null
42 ,p_valid_grade_id out nocopy number
43 ,p_object_version_number out nocopy number
44 ) is
45 --
46 -- Declare cursors and local variables
47 --
48 l_business_group_id per_valid_grades.business_group_id%TYPE;
49 l_proc varchar2(72) := g_package||'create_valid_grade';
50
51 --
52 --
53 -- Variables added for before and after busines process
54 --
55 l_valid_grade_id number;
56 l_object_version_number number;
57 l_date_from date;
58 l_date_to date;
59
60
61 cursor csr_bus_grp is
62 select gra.business_group_id
63 from per_grades gra
64 where gra.grade_id = p_grade_id;
65
66 -- Bug 10282503
67 l_pos_date_effective date;
68 cursor csr_pos_start_date is
69 select pos.date_effective
70 from hr_positions_f pos
71 where pos.position_id = p_position_id;
72 --
73
74 begin
75 hr_utility.set_location('Entering:'|| l_proc, 5);
76 --
77 -- Issue a savepoint.
78 --
79 savepoint create_valid_grade;
80 l_date_from :=trunc(p_date_from);
81 l_date_to :=trunc(p_date_to);
82 --
83 -- Check that p_grade_id is not null as it is used in the cursor.
84 --
85 hr_api.mandatory_arg_error
86 (p_api_name => l_proc,
87 p_argument => 'grade_id',
88 p_argument_value => p_grade_id);
89 --
90
91 -- Bug 10282503
92 open csr_pos_start_date;
93 fetch csr_pos_start_date into l_pos_date_effective;
94 l_pos_date_effective := trunc(l_pos_date_effective);
95 if l_date_from < l_pos_date_effective then
96 l_date_from := l_pos_date_effective;
97 end if;
98 close csr_pos_start_date;
99
100 --
101 hr_utility.set_location(l_proc, 6);
102 --
103 -- Get business_group_id using grade_id.
104 --
105 open csr_bus_grp;
106 fetch csr_bus_grp
107 into l_business_group_id;
108 --
109 if csr_bus_grp%notfound then
110 close csr_bus_grp;
111 hr_utility.set_message(801, 'HR_51082_GRADE_NOT_EXIST');
112 hr_utility.raise_error;
113 end if;
114 --
115 close csr_bus_grp;
116 --
117 hr_utility.set_location(l_proc, 7);
118 --
119 -- Call before Process User Hook point create_valid_grade
120 --
121 begin
122 hr_valid_grade_bk1.create_valid_grade_b
123 (p_business_group_id => l_business_group_id
124 ,p_grade_id => p_grade_id
125 ,p_date_from => l_date_from
126 ,p_effective_date => p_effective_date --Added for bug# 1760707
127 ,p_comments => p_comments
128 ,p_date_to => l_date_to
129 ,p_job_id => p_job_id
130 ,p_position_id => p_position_id
131 ,p_attribute_category => p_attribute_category
132 ,p_attribute1 => p_attribute1
133 ,p_attribute2 => p_attribute2
134 ,p_attribute3 => p_attribute3
135 ,p_attribute4 => p_attribute4
136 ,p_attribute5 => p_attribute5
137 ,p_attribute6 => p_attribute6
138 ,p_attribute7 => p_attribute7
139 ,p_attribute8 => p_attribute8
140 ,p_attribute9 => p_attribute9
141 ,p_attribute10 => p_attribute10
142 ,p_attribute11 => p_attribute11
143 ,p_attribute12 => p_attribute12
144 ,p_attribute13 => p_attribute13
145 ,p_attribute14 => p_attribute14
146 ,p_attribute15 => p_attribute15
147 ,p_attribute16 => p_attribute16
148 ,p_attribute17 => p_attribute17
149 ,p_attribute18 => p_attribute18
150 ,p_attribute19 => p_attribute19
151 ,p_attribute20 => p_attribute20
152 );
153 exception
154 when hr_api.Cannot_Find_Prog_Unit then
155 hr_api.cannot_find_prog_unit_error
156 (p_module_name => 'CREATE_VALID_GRADE'
157 ,p_hook_type => 'BP'
158 );
159 --
160 --
161 -- End of API User Hook for the before process hook point create_valid_grade
162 end;
163 --
164 --
165 -- Insert Valid Grade details.
166 --
167 per_vgr_ins.ins
168 (p_valid_grade_id => l_valid_grade_id
169 ,p_business_group_id => l_business_group_id
170 ,p_grade_id => p_grade_id
171 ,p_date_from => l_date_from -- Bug 10282503
172 ,p_effective_date => p_effective_date --Added for bug# 1760707
173 ,p_comments => p_comments
174 ,p_date_to => l_date_to
175 ,p_job_id => p_job_id
176 ,p_position_id => p_position_id
177 ,p_attribute_category => p_attribute_category
178 ,p_attribute1 => p_attribute1
179 ,p_attribute2 => p_attribute2
180 ,p_attribute3 => p_attribute3
181 ,p_attribute4 => p_attribute4
182 ,p_attribute5 => p_attribute5
183 ,p_attribute6 => p_attribute6
184 ,p_attribute7 => p_attribute7
185 ,p_attribute8 => p_attribute8
186 ,p_attribute9 => p_attribute9
187 ,p_attribute10 => p_attribute10
188 ,p_attribute11 => p_attribute11
189 ,p_attribute12 => p_attribute12
190 ,p_attribute13 => p_attribute13
191 ,p_attribute14 => p_attribute14
192 ,p_attribute15 => p_attribute15
193 ,p_attribute16 => p_attribute16
194 ,p_attribute17 => p_attribute17
195 ,p_attribute18 => p_attribute18
196 ,p_attribute19 => p_attribute19
197 ,p_attribute20 => p_attribute20
198 ,p_object_version_number => l_object_version_number
199 ,p_validate => FALSE
200 );
201 --
202 hr_utility.set_location(l_proc, 8);
203 --
204 -- Call after Process User Hook point create_valid_grade
205 --
206 begin
207 hr_valid_grade_bk1.create_valid_grade_a
208 (p_business_group_id => l_business_group_id
209 ,p_grade_id => p_grade_id
210 ,p_date_from => l_date_from
211 ,p_effective_date => p_effective_date --Added for bug# 1760707
212 ,p_comments => p_comments
213 ,p_date_to => l_date_to
214 ,p_job_id => p_job_id
215 ,p_position_id => p_position_id
216 ,p_attribute_category => p_attribute_category
217 ,p_attribute1 => p_attribute1
218 ,p_attribute2 => p_attribute2
219 ,p_attribute3 => p_attribute3
220 ,p_attribute4 => p_attribute4
221 ,p_attribute5 => p_attribute5
222 ,p_attribute6 => p_attribute6
223 ,p_attribute7 => p_attribute7
224 ,p_attribute8 => p_attribute8
225 ,p_attribute9 => p_attribute9
226 ,p_attribute10 => p_attribute10
227 ,p_attribute11 => p_attribute11
228 ,p_attribute12 => p_attribute12
229 ,p_attribute13 => p_attribute13
230 ,p_attribute14 => p_attribute14
231 ,p_attribute15 => p_attribute15
232 ,p_attribute16 => p_attribute16
233 ,p_attribute17 => p_attribute17
234 ,p_attribute18 => p_attribute18
235 ,p_attribute19 => p_attribute19
236 ,p_attribute20 => p_attribute20
237 ,p_valid_grade_id => l_valid_grade_id
238 ,p_object_version_number => l_object_version_number
239 );
240 exception
241 when hr_api.Cannot_Find_Prog_Unit then
242 hr_api.cannot_find_prog_unit_error
243 (p_module_name => 'CREATE_VALID_GRADE'
244 ,p_hook_type => 'AP'
245 );
246 --
247 -- End of API User Hook for the after process hook point create_valid_grade
248 --
249 end;
250 --
251 if p_validate then
252 raise hr_api.validate_enabled;
253 end if;
254 --
255 -- Set all output arguments
256 --
257 p_valid_grade_id := l_valid_grade_id;
258 p_object_version_number := l_object_version_number;
259 --
260 -- When in validation only mode raise the Validate_Enabled exception
261 --
262
263 --
264 hr_utility.set_location(' Leaving:'||l_proc, 11);
265 exception
266 when hr_api.validate_enabled then
267 --
268 -- As the Validate_Enabled exception has been raised
269 -- we must rollback to the savepoint
270 --
271 ROLLBACK TO create_valid_grade;
272 --
273 -- Only set output warning arguments
274 -- (Any key or derived arguments must be set to null
275 -- when validation only mode is being used.)
276 --
277 p_valid_grade_id := null;
278 p_object_version_number := null;
279 --
280 when others then
281 --
282 -- A validation or unexpected error has occurred
283 --
284 -- Added as part of fix to bug 632479
285 --
286 ROLLBACK TO create_valid_grade;
287 -- Reset IN OUT parameters and set OUT parameters
288 p_valid_grade_id := null;
289 p_object_version_number := null;
290 raise;
291 --
292 -- End of fix.
293 --
294 --
295 end create_valid_grade;
296 --
297 --
298 -- ----------------------------------------------------------------------------
299 -- |----------------------------< update_valid_grades >-----------------------|
300 -- ----------------------------------------------------------------------------
301 procedure update_valid_grades_for_job
302 (p_business_group_id number,
303 p_job_id number,
304 p_date_to date)
305 is
306 --
307 begin
308 --
309 -- Update valid grade end dates to match the end date of the
310 -- job where the end date of the job is earlier than the end
311 -- date of the valid grade.or the previous end dates matched.
312 --
313 --
314 update per_valid_grades vg
315 set vg.date_to =
316 (select least(nvl(p_date_to, to_date('12/31/4712','mm/dd/yyyy')),
317 nvl(g.date_to, to_date('12/31/4712','mm/dd/yyyy')))
318 from per_grades g
319 where g.grade_id = vg.grade_id
320 and g.business_group_id + 0 = p_business_group_id)
321 where vg.business_group_id + 0 = p_business_group_id
322 and vg.job_id = p_job_id
323 and nvl(vg.date_to, to_date('12/31/4712','mm/dd/yyyy')) > p_date_to;
324 --
325 if (SQL%NOTFOUND) then
326 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
327 hr_utility.set_message_token('PROCEDURE','update_valid_grades');
328 hr_utility.set_message_token('STEP','1');
329 hr_utility.raise_error;
330 end if;
331 --
332 --
333 end update_valid_grades_for_job;
334 --
335 -- ----------------------------------------------------------------------------
336 -- |----------------------------< delete_valid_grades >-----------------------|
337 -- ----------------------------------------------------------------------------
338 procedure delete_valid_grades_for_job
339 (p_business_group_id number,
340 p_job_id number,
341 p_date_to date) is
342 --
343 begin
344 --
345 -- Valid grades are deleted if the end date of the job
346 -- has been made earlier than the start date of the
347 -- valid grade.
348 --
349 --
350 delete from per_valid_grades vg
351 where vg.business_group_id + 0 = p_business_group_id
352 and vg.job_id = p_job_id
353 and vg.date_from > p_date_to;
354 --
355 --
356 if (SQL%NOTFOUND) then
357 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
358 hr_utility.set_message_token('PROCEDURE','delete_valid_grades');
359 hr_utility.set_message_token('STEP','1');
360 hr_utility.raise_error;
361 end if;
362 --
363 end delete_valid_grades_for_job;
364 end hr_valid_grade_api;
365 --