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