1 Package Body irc_vce_bus as
2 /* $Header: irvcerhi.pkb 120.1 2005/12/13 06:42:58 cnholmes noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' irc_vce_bus.'; -- Global package name
9 --
10 -- The following two global variables are only to be
11 -- used by the return_legislation_code function.
12 --
13 g_legislation_code varchar2(150) default null;
14 g_vacancy_id number default null;
15 g_variable_comp_lookup varchar2(30) default null;
16 --
17 -- ---------------------------------------------------------------------------
18 -- |----------------------< set_security_group_id >--------------------------|
19 -- ---------------------------------------------------------------------------
20 --
21 Procedure set_security_group_id
22 (p_vacancy_id in number
23 ,p_variable_comp_lookup in varchar2
24 ,p_associated_column1 in varchar2 default null
25 ,p_associated_column2 in varchar2 default null
26 ) is
27 --
28 -- Declare cursor
29 --
30 cursor csr_sec_grp is
31 select pbg.security_group_id
32 from per_business_groups pbg
33 , per_all_vacancies pav
34 where pav.vacancy_id = p_vacancy_id
35 and pbg.business_group_id = pav.business_group_id;
36 --
37 --
38 -- Declare local variables
39 --
40 l_security_group_id number;
41 l_proc varchar2(72) := g_package||'set_security_group_id';
42 --
43 begin
44 --
45 hr_utility.set_location('Entering:'|| l_proc, 10);
46 --
47 -- Ensure that all the mandatory parameter are not null
48 --
49 hr_api.mandatory_arg_error
50 (p_api_name => l_proc
51 ,p_argument => 'vacancy_id'
52 ,p_argument_value => p_vacancy_id
53 );
54 hr_api.mandatory_arg_error
55 (p_api_name => l_proc
56 ,p_argument => 'variable_comp_lookup'
57 ,p_argument_value => p_variable_comp_lookup
58 );
59 --
60 open csr_sec_grp;
61 fetch csr_sec_grp into l_security_group_id;
62 --
63 if csr_sec_grp%notfound then
64 --
65 close csr_sec_grp;
66 --
67 -- The primary key is invalid therefore we must error
68 --
69 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
70 hr_multi_message.add
71 (p_associated_column1
72 => nvl(p_associated_column1,'VACANCY_ID')
73 ,p_associated_column2
74 => nvl(p_associated_column2,'VARIABLE_COMP_LOOKUP')
75 );
76 --
77 else
78 close csr_sec_grp;
79 --
80 -- Set the security_group_id in CLIENT_INFO
81 --
82 hr_api.set_security_group_id
83 (p_security_group_id => l_security_group_id
84 );
85 end if;
86 --
87 hr_utility.set_location(' Leaving:'|| l_proc, 20);
88 --
89 end set_security_group_id;
90 --
91 -- ---------------------------------------------------------------------------
92 -- |---------------------< return_legislation_code >-------------------------|
93 -- ---------------------------------------------------------------------------
94 --
95 Function return_legislation_code
96 (p_vacancy_id in number
97 ,p_variable_comp_lookup in varchar2
98 )
99 Return Varchar2 Is
100 --
101 -- Declare cursor
102 --
103 -- Join between irc_variable_comp_elements and PER_BUSINESS_GROUPS
104 -- so that the legislation_code for
105 -- the current business group context can be derived.
106
107 cursor csr_leg_code is
108 select pbg.legislation_code
109 from per_business_groups pbg
110 ,per_all_vacancies pav
111 where pav.vacancy_id = p_vacancy_id
112 and pbg.business_group_id = pav.business_group_id;
113 --
114 -- Declare local variables
115 --
116 l_legislation_code varchar2(150);
117 l_proc varchar2(72) := g_package||'return_legislation_code';
118 --
119 Begin
120 --
121 hr_utility.set_location('Entering:'|| l_proc, 10);
122 --
123 -- Ensure that all the mandatory parameter are not null
124 --
125 hr_api.mandatory_arg_error
126 (p_api_name => l_proc
127 ,p_argument => 'vacancy_id'
128 ,p_argument_value => p_vacancy_id
129 );
130 hr_api.mandatory_arg_error
131 (p_api_name => l_proc
132 ,p_argument => 'variable_comp_lookup'
133 ,p_argument_value => p_variable_comp_lookup
134 );
135 --
136 if (( nvl(irc_vce_bus.g_vacancy_id, hr_api.g_number)
137 = p_vacancy_id)
138 and ( nvl(irc_vce_bus.g_variable_comp_lookup, hr_api.g_varchar2)
139 = p_variable_comp_lookup)) then
140 --
141 -- The legislation code has already been found with a previous
142 -- call to this function. Just return the value in the global
143 -- variable.
144 --
145 l_legislation_code := irc_vce_bus.g_legislation_code;
146 hr_utility.set_location(l_proc, 20);
147 else
148 --
149 -- The ID is different to the last call to this function
150 -- or this is the first call to this function.
151 --
152 open csr_leg_code;
153 fetch csr_leg_code into l_legislation_code;
154 --
155 if csr_leg_code%notfound then
156 --
157 -- The primary key is invalid therefore we must error
158 --
159 close csr_leg_code;
160 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
161 fnd_message.raise_error;
162 end if;
163 hr_utility.set_location(l_proc,30);
164 --
165 -- Set the global variables so the values are
166 -- available for the next call to this function.
167 --
168 close csr_leg_code;
169 irc_vce_bus.g_vacancy_id := p_vacancy_id;
170 irc_vce_bus.g_variable_comp_lookup := p_variable_comp_lookup;
171 irc_vce_bus.g_legislation_code := l_legislation_code;
172 end if;
173 hr_utility.set_location(' Leaving:'|| l_proc, 40);
174 return l_legislation_code;
175 end return_legislation_code;
176 --
177 -- ----------------------------------------------------------------------------
178 -- |-----------------------< chk_non_updateable_args >------------------------|
179 -- ----------------------------------------------------------------------------
180 -- {Start Of Comments}
181 --
182 -- Description:
183 -- This procedure is used to ensure that non updateable attributes have
184 -- not been updated. If an attribute has been updated an error is generated.
185 --
186 -- Pre Conditions:
187 -- g_old_rec has been populated with details of the values currently in
188 -- the database.
189 --
190 -- In Arguments:
191 -- p_rec has been populated with the updated values the user would like the
192 -- record set to.
193 --
194 -- Post Success:
195 -- Processing continues if all the non updateable attributes have not
196 -- changed.
197 --
198 -- Post Failure:
199 -- An application error is raised if any of the non updatable attributes
200 -- have been altered.
201 --
202 -- {End Of Comments}
203 -- ----------------------------------------------------------------------------
204 Procedure chk_non_updateable_args
205 (p_effective_date in date
206 ,p_rec in irc_vce_shd.g_rec_type
207 ) IS
208 --
209 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
210 --
211 Begin
212 --
213 -- Only proceed with the validation if a row exists for the current
214 -- record in the HR Schema.
215 --
216 IF NOT irc_vce_shd.api_updating
217 (p_vacancy_id => p_rec.vacancy_id
218 ,p_variable_comp_lookup => p_rec.variable_comp_lookup
219 ,p_object_version_number => p_rec.object_version_number
220 ) THEN
221 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
222 fnd_message.set_token('PROCEDURE ', l_proc);
223 fnd_message.set_token('STEP ', '5');
224 fnd_message.raise_error;
225 END IF;
226 --
227 -- Since no update is there for IRC_VARIABLE_COMP_ELEMENTS, no additional
228 -- checks need to be added here.
229 --
230 End chk_non_updateable_args;
231 --
232 -- ----------------------------------------------------------------------------
233 -- |-----------------------< chk_variable_comp_lookup >-----------------------|
234 -- ----------------------------------------------------------------------------
235 -- {Start Of Comments}
236 --
237 -- Description:
238 -- This procedure is used to ensure that variable compensation lookup is
239 -- correct.
240 --
241 -- Pre Conditions:
242 --
243 --
244 -- In Arguments:
245 -- p_variable_comp_lookup
246 -- p_effective_date
247 --
248 -- Post Success:
249 -- Processing continues if all the variable compensation lookup is correct
250 --
251 -- Post Failure:
252 -- An application error is raised if variable compensation lookup is
253 -- incorrect
254 --
255 -- {End Of Comments}
256 -- ----------------------------------------------------------------------------
257 Procedure chk_variable_comp_lookup
258 (p_effective_date in date
259 ,p_variable_comp_lookup in
260 irc_variable_comp_elements.variable_comp_lookup%TYPE
261 ) IS
262 --
263 l_proc varchar2(72) := g_package || 'chk_variable_comp_lookup';
264 l_ret boolean;
265 --
266 begin
267 hr_utility.set_location('Entering:'||l_proc,10);
268 -- Checks that variable compensation lookup is passed as mandatory argument.
269 hr_api.mandatory_arg_error
270 (p_api_name => l_proc
271 ,p_argument => 'variable_comp_lookup'
272 ,p_argument_value => p_variable_comp_lookup
273 );
274 -- Checks that variable compensation lookup is validated against hr_lookups.
275 hr_utility.set_location(l_proc,15);
276 l_ret := hr_api.not_exists_in_hr_lookups(
277 p_effective_date => p_effective_date
278 ,p_lookup_type => 'IRC_VARIABLE_COMP_ELEMENT'
279 ,p_lookup_code => p_variable_comp_lookup);
280 hr_utility.set_location(l_proc,20);
281 if l_ret = true then
282 fnd_message.set_name('PER','IRC_412030_VCE_INV_VAR_COMP_LO');
283 fnd_message.raise_error;
284 end if;
285 --
286 hr_utility.set_location(' Leaving:'||l_proc,25);
287 exception
288 when app_exception.application_exception then
289 if hr_multi_message.exception_add
290 (p_associated_column1 =>
291 'IRC_VARIABLE_COMP_ELEMENTS.VARIABLE_COMP_LOOKUP'
292 ) then
293 hr_utility.set_location(' Leaving:'||l_proc,30);
294 raise;
295 end if;
296 hr_utility.set_location(' Leaving:'||l_proc,35);
297 end chk_variable_comp_lookup;
298 --
299 -- ----------------------------------------------------------------------------
300 -- |-----------------------------< chk_vacancy_id >---------------------------|
301 -- ----------------------------------------------------------------------------
302 -- {Start Of Comments}
303 --
304 -- Description:
305 -- This procedure is used to ensure that vacancy id exists in
306 -- PER_ALL_VACANCIES
307 --
308 -- Pre Conditions:
309 --
310 -- In Arguments:
311 -- p_effective_date
312 -- p_vacancy_id
313 --
314 -- Post Success:
315 -- Processing continues if all the variable compensation lookup is correct
316 --
317 -- Post Failure:
318 -- An application error is raised if variable compensation lookup is
319 -- incorrect
320 --
321 -- {End Of Comments}
322 -- ----------------------------------------------------------------------------
323 Procedure chk_vacancy_id
324 (p_effective_date in date
325 ,p_vacancy_id in irc_variable_comp_elements.vacancy_id%TYPE
326 ) IS
327 --
328 l_proc varchar2(72) := g_package || 'chk_vacancy_id';
329 l_vacancy_id varchar2(1);
330 --
331 cursor csr_vacancy_id is
332 select null from per_all_vacancies pav where pav.vacancy_id = p_vacancy_id
333 and p_effective_date < NVL(pav.date_to,hr_api.g_eot);
334 --
335 begin
336 hr_utility.set_location('Entering:'||l_proc,10);
337 -- Checks that vacancy_id is passed as mandatory attribute
338 hr_api.mandatory_arg_error
339 (p_api_name => l_proc
343 -- Checks that vacancy_id exists in PER_ALL_VACANCIES.
340 ,p_argument => 'vacancy_id'
341 ,p_argument_value => p_vacancy_id
342 );
344 hr_utility.set_location(l_proc,15);
345 open csr_vacancy_id;
346 fetch csr_vacancy_id into l_vacancy_id;
347 if csr_vacancy_id%NOTFOUND then
348 close csr_vacancy_id;
349 fnd_message.set_name('PER','IRC_412031_VCE_INV_VACANCY_ID');
350 fnd_message.raise_error;
351 end if;
352 close csr_vacancy_id;
353 --
354 hr_utility.set_location(' Leaving:'||l_proc,20);
355 exception
356 when app_exception.application_exception then
357 if hr_multi_message.exception_add
358 (p_associated_column1 =>
359 'IRC_VARIABLE_COMP_ELEMENTS.VACANCY_ID'
360 ) then
361 hr_utility.set_location(' Leaving:'||l_proc,25);
362 raise;
363 end if;
364 hr_utility.set_location(' Leaving:'||l_proc,30);
365 end chk_vacancy_id;
366 --
367 -- ----------------------------------------------------------------------------
368 -- |---------------------------< insert_validate >----------------------------|
369 -- ----------------------------------------------------------------------------
370 Procedure insert_validate
371 (p_effective_date in date
372 ,p_rec in irc_vce_shd.g_rec_type
373 ) is
374 --
375 l_proc varchar2(72) := g_package||'insert_validate';
376 --
377 Begin
378 hr_utility.set_location('Entering:'||l_proc, 5);
379 --
380 -- Validate Important Attributes
381 --
382 -- Call all supporting business operations
383 --
384 -- After validating the set of important attributes,
385 -- if Mulitple message detection is enabled and at least
386 -- one error has been found then abort further validation.
387 --
388 -- Validate Dependent Attributes
389 --
390 hr_utility.set_location(l_proc,10);
391 chk_variable_comp_lookup
392 (p_effective_date => p_effective_date
393 ,p_variable_comp_lookup => p_rec.variable_comp_lookup
394 );
395 --
396 --
397 hr_utility.set_location(l_proc,15);
398 chk_vacancy_id
399 (p_effective_date => p_effective_date
400 ,p_vacancy_id => p_rec.vacancy_id
401 );
402 --
403 -- As IRC_VARIABLE_COMP_ELEMENTS does not have a mandatory business_group_id
404 -- column, client_info is populated by calling
405 -- irc_vce_bus.set_security_group_id procedure.
406 hr_utility.set_location(l_proc,20);
407 irc_vce_bus.set_security_group_id(
408 p_vacancy_id => p_rec.vacancy_id
409 ,p_variable_comp_lookup => p_rec.variable_comp_lookup
410 );
411 --
412 hr_utility.set_location(' Leaving:'||l_proc, 25);
413 End insert_validate;
414 --
415 -- ----------------------------------------------------------------------------
416 -- |---------------------------< update_validate >----------------------------|
417 -- ----------------------------------------------------------------------------
418 Procedure update_validate
419 (p_effective_date in date
420 ,p_rec in irc_vce_shd.g_rec_type
421 ) is
422 --
423 l_proc varchar2(72) := g_package||'update_validate';
424 --
425 Begin
426 hr_utility.set_location('Entering:'||l_proc, 5);
427 --
428 -- Validate Important Attributes
429 --
430 -- Call all supporting business operations
431 --
432 -- After validating the set of important attributes,
433 -- if Mulitple message detection is enabled and at least
434 -- one error has been found then abort further validation.
435 --
436 -- Validate Dependent Attributes
437 --
438 hr_utility.set_location(l_proc,10);
439 chk_non_updateable_args
440 (p_effective_date => p_effective_date
441 ,p_rec => p_rec
442 );
443 --
444 -- As IRC_VARIABLE_COMP_ELEMENTS does not have a mandatory business_group_id
445 -- column, client_info is populated by calling
446 -- irc_vce_bus.set_security_group_id procedure.
447 hr_utility.set_location(l_proc,15);
448 irc_vce_bus.set_security_group_id(
449 p_vacancy_id => p_rec.vacancy_id
450 ,p_variable_comp_lookup => p_rec.variable_comp_lookup
451 );
452 --
453 hr_utility.set_location(' Leaving:'||l_proc, 20);
454 End update_validate;
455 --
456 -- ----------------------------------------------------------------------------
457 -- |---------------------------< delete_validate >----------------------------|
458 -- ----------------------------------------------------------------------------
459 Procedure delete_validate
460 (p_rec in irc_vce_shd.g_rec_type
461 ) is
462 --
463 l_proc varchar2(72) := g_package||'delete_validate';
464 --
465 Begin
466 hr_utility.set_location('Entering:'||l_proc, 5);
467 --
468 -- Call all supporting business operations
469 --
470 hr_utility.set_location(' Leaving:'||l_proc, 10);
471 End delete_validate;
472 --
473 end irc_vce_bus;
[Home] [Help]