[Home] [Help]
PACKAGE BODY: APPS.PER_RES_BUS
Source
1 Package Body per_res_bus as
2 /* $Header: peresrhi.pkb 115.2 2003/04/02 13:38:24 eumenyio noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_res_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_cagr_entitlement_result_id number default null;
15 --
16 -- ---------------------------------------------------------------------------
17 -- |----------------------< set_security_group_id >--------------------------|
18 -- ---------------------------------------------------------------------------
19 --
20 Procedure set_security_group_id
21 (p_cagr_entitlement_result_id in number
22 ) is
23 --
24 -- Declare cursor
25 --
26 cursor csr_sec_grp is
27 select pbg.security_group_id
28 from per_business_groups pbg
29 , per_cagr_entitlement_results res
30 where res.cagr_entitlement_result_id = p_cagr_entitlement_result_id
31 and pbg.business_group_id = res.business_group_id;
32 --
33 -- Declare local variables
34 --
35 l_security_group_id number;
36 l_proc varchar2(72) := g_package||'set_security_group_id';
37 --
38 begin
39 --
40 hr_utility.set_location('Entering:'|| l_proc, 10);
41 --
42 -- Ensure that all the mandatory parameter are not null
43 --
44 hr_api.mandatory_arg_error
45 (p_api_name => l_proc
46 ,p_argument => 'cagr_entitlement_result_id'
47 ,p_argument_value => p_cagr_entitlement_result_id
48 );
49 --
50 open csr_sec_grp;
51 fetch csr_sec_grp into l_security_group_id;
52 --
53 if csr_sec_grp%notfound then
54 --
55 close csr_sec_grp;
56 --
57 -- The primary key is invalid therefore we must error
58 --
59 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
60 fnd_message.raise_error;
61 --
62 end if;
63 close csr_sec_grp;
64 --
65 -- Set the security_group_id in CLIENT_INFO
66 --
67 hr_api.set_security_group_id
68 (p_security_group_id => l_security_group_id
69 );
70 --
71 hr_utility.set_location(' Leaving:'|| l_proc, 20);
72 --
73 end set_security_group_id;
74 --
75 -- ---------------------------------------------------------------------------
76 -- |---------------------< return_legislation_code >-------------------------|
77 -- ---------------------------------------------------------------------------
78 --
79 Function return_legislation_code
80 (p_cagr_entitlement_result_id in number
81 )
82 Return Varchar2 Is
83 --
84 -- Declare cursor
85 --
86 cursor csr_leg_code is
87 select pbg.legislation_code
88 from per_business_groups pbg
89 , per_cagr_entitlement_results res
90 where res.cagr_entitlement_result_id = p_cagr_entitlement_result_id
91 and pbg.business_group_id (+) = res.business_group_id;
92 --
93 -- Declare local variables
94 --
95 l_legislation_code varchar2(150);
96 l_proc varchar2(72) := g_package||'return_legislation_code';
97 --
98 Begin
99 --
100 hr_utility.set_location('Entering:'|| l_proc, 10);
101 --
102 -- Ensure that all the mandatory parameter are not null
103 --
104 hr_api.mandatory_arg_error
105 (p_api_name => l_proc
106 ,p_argument => 'cagr_entitlement_result_id'
107 ,p_argument_value => p_cagr_entitlement_result_id
108 );
109 --
110 if ( nvl(per_res_bus.g_cagr_entitlement_result_id, hr_api.g_number)
111 = p_cagr_entitlement_result_id) then
112 --
113 -- The legislation code has already been found with a previous
114 -- call to this function. Just return the value in the global
115 -- variable.
116 --
117 l_legislation_code := per_res_bus.g_legislation_code;
118 hr_utility.set_location(l_proc, 20);
119 else
120 --
121 -- The ID is different to the last call to this function
122 -- or this is the first call to this function.
123 --
124 open csr_leg_code;
125 fetch csr_leg_code into l_legislation_code;
126 --
127 if csr_leg_code%notfound then
128 --
129 -- The primary key is invalid therefore we must error
130 --
131 close csr_leg_code;
132 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
133 fnd_message.raise_error;
134 end if;
135 hr_utility.set_location(l_proc,30);
136 --
137 -- Set the global variables so the values are
138 -- available for the next call to this function.
139 --
140 close csr_leg_code;
141 per_res_bus.g_cagr_entitlement_result_id := p_cagr_entitlement_result_id;
142 per_res_bus.g_legislation_code := l_legislation_code;
143 end if;
144 hr_utility.set_location(' Leaving:'|| l_proc, 40);
145 return l_legislation_code;
146 end return_legislation_code;
147 --
148 -- ----------------------------------------------------------------------------
149 -- |-----------------------< chk_non_updateable_args >------------------------|
150 -- ----------------------------------------------------------------------------
151 -- {Start Of Comments}
152 --
153 -- Description:
154 -- This procedure is used to ensure that non updateable attributes have
155 -- not been updated. If an attribute has been updated an error is generated.
156 --
157 -- Pre Conditions:
158 -- g_old_rec has been populated with details of the values currently in
159 -- the database.
160 --
161 -- In Arguments:
162 -- p_rec has been populated with the updated values the user would like the
163 -- record set to.
164 --
165 -- Post Success:
166 -- Processing continues if all the non updateable attributes have not
167 -- changed.
168 --
169 -- Post Failure:
170 -- An application error is raised if any of the non updatable attributes
171 -- have been altered.
172 --
173 -- {End Of Comments}
174 -- ----------------------------------------------------------------------------
175 Procedure chk_non_updateable_args
176 (p_effective_date in date
177 ,p_rec in per_res_shd.g_rec_type
178 ) IS
179 --
180 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
181 l_error EXCEPTION;
182 l_argument varchar2(30);
183 --
184 Begin
185 --
186 -- Only proceed with the validation if a row exists for the current
187 -- record in the HR Schema.
188 --
189 IF NOT per_res_shd.api_updating
190 (p_cagr_entitlement_result_id => p_rec.cagr_entitlement_result_id
191 ,p_object_version_number => p_rec.object_version_number
192 ) THEN
193 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
194 fnd_message.set_token('PROCEDURE ', l_proc);
195 fnd_message.set_token('STEP ', '5');
196 fnd_message.raise_error;
197 END IF;
198 --
199 -- Checks to ensure non-updateable args have
200 -- not been updated.
201 --
202 hr_utility.set_location(l_proc, 30);
203 --
204 if nvl(p_rec.assignment_id, hr_api.g_number) <>
205 nvl(per_res_shd.g_old_rec.assignment_id
206 ,hr_api.g_number) then
207 l_argument := 'assignment_id';
208 raise l_error;
209 end if;
210 /*
211 if nvl(p_rec.cagr_request_id, hr_api.g_number) <>
212 nvl(per_res_shd.g_old_rec.cagr_request_id
213 ,hr_api.g_number) then
214 l_argument := 'cagr_request_id';
215 raise l_error;
216 end if;
217 */
218
219 if nvl(p_rec.start_date, hr_api.g_date) <>
220 nvl(per_res_shd.g_old_rec.start_date
221 ,hr_api.g_date) then
222 l_argument := 'start_date';
223 raise l_error;
224 end if;
225
226 if nvl(p_rec.end_date, hr_api.g_date) <>
227 nvl(per_res_shd.g_old_rec.end_date
228 ,hr_api.g_date) then
229 l_argument := 'end_date';
230 raise l_error;
231 end if;
232
233 if nvl(p_rec.collective_agreement_id, hr_api.g_number) <>
234 nvl(per_res_shd.g_old_rec.collective_agreement_id
235 ,hr_api.g_number) then
236 l_argument := 'collective_agreement_id';
237 raise l_error;
238 end if;
239
240 if nvl(p_rec.cagr_entitlement_item_id, hr_api.g_number) <>
241 nvl(per_res_shd.g_old_rec.cagr_entitlement_item_id
242 ,hr_api.g_number) then
243 l_argument := 'cagr_entitlement_item_id';
244 raise l_error;
245 end if;
246
247 if nvl(p_rec.element_type_id, hr_api.g_number) <>
248 nvl(per_res_shd.g_old_rec.element_type_id
249 ,hr_api.g_number) then
250 l_argument := 'element_type_id';
251 raise l_error;
252 end if;
253
254 if nvl(p_rec.input_value_id, hr_api.g_number) <>
255 nvl(per_res_shd.g_old_rec.input_value_id
256 ,hr_api.g_number) then
257 l_argument := 'input_value_id';
258 raise l_error;
259 end if;
260
261 if nvl(p_rec.cagr_api_id, hr_api.g_number) <>
262 nvl(per_res_shd.g_old_rec.cagr_api_id
263 ,hr_api.g_number) then
264 l_argument := 'cagr_api_id';
265 raise l_error;
266 end if;
267
268 if nvl(p_rec.cagr_api_param_id, hr_api.g_number) <>
269 nvl(per_res_shd.g_old_rec.cagr_api_param_id
270 ,hr_api.g_number) then
271 l_argument := 'cagr_api_param_id';
272 raise l_error;
273 end if;
274
275 if nvl(p_rec.category_name, hr_api.g_varchar2) <>
276 nvl(per_res_shd.g_old_rec.category_name
277 ,hr_api.g_varchar2) then
278 l_argument := 'category_name';
279 raise l_error;
280 end if;
281
282 if nvl(p_rec.cagr_entitlement_id, hr_api.g_number) <>
283 nvl(per_res_shd.g_old_rec.cagr_entitlement_id
284 ,hr_api.g_number) then
285 l_argument := 'cagr_entitlement_id';
286 raise l_error;
287 end if;
288
289 if nvl(p_rec.cagr_entitlement_line_id, hr_api.g_number) <>
290 nvl(per_res_shd.g_old_rec.cagr_entitlement_line_id
291 ,hr_api.g_number) then
292 l_argument := 'cagr_entitlement_line_id';
293 raise l_error;
294 end if;
295 -- works
296
297 if nvl(p_rec.value, hr_api.g_varchar2) <>
298 nvl(per_res_shd.g_old_rec.value
299 ,hr_api.g_varchar2) then
300 l_argument := 'value';
301 raise l_error;
302 end if;
303
304 if nvl(p_rec.units_of_measure, hr_api.g_varchar2) <>
305 nvl(per_res_shd.g_old_rec.units_of_measure
306 ,hr_api.g_varchar2) then
307 l_argument := 'units_of_measure';
308 raise l_error;
309 end if;
310
311 if nvl(p_rec.range_from, hr_api.g_varchar2) <>
312 nvl(per_res_shd.g_old_rec.range_from
313 ,hr_api.g_varchar2) then
314 l_argument := 'range_from';
315 raise l_error;
316 end if;
317
318 if nvl(p_rec.range_to, hr_api.g_varchar2) <>
319 nvl(per_res_shd.g_old_rec.range_to
320 ,hr_api.g_varchar2) then
321 l_argument := 'range_to';
322 raise l_error;
323 end if;
324
325 if nvl(p_rec.grade_spine_id, hr_api.g_number) <>
326 nvl(per_res_shd.g_old_rec.grade_spine_id
327 ,hr_api.g_number) then
328 l_argument := 'grade_spine_id';
329 raise l_error;
330 end if;
331
332 if nvl(p_rec.parent_spine_id, hr_api.g_number) <>
333 nvl(per_res_shd.g_old_rec.parent_spine_id
334 ,hr_api.g_number) then
335 l_argument := 'parent_spine_id';
336 raise l_error;
337 end if;
338
339 if nvl(p_rec.step_id , hr_api.g_number) <>
340 nvl(per_res_shd.g_old_rec.step_id
341 ,hr_api.g_number) then
342 l_argument := 'step_id ';
343 raise l_error;
344 end if;
345
346 if nvl(p_rec.from_step_id, hr_api.g_number) <>
347 nvl(per_res_shd.g_old_rec.from_step_id
348 ,hr_api.g_number) then
349 l_argument := 'from_step_id';
350 raise l_error;
351 end if;
352
353 if nvl(p_rec.to_step_id, hr_api.g_number) <>
354 nvl(per_res_shd.g_old_rec.to_step_id
355 ,hr_api.g_number) then
356 l_argument := 'to_step_id';
357 raise l_error;
358 end if;
359
360 if nvl(p_rec.oipl_id, hr_api.g_number) <>
361 nvl(per_res_shd.g_old_rec.oipl_id
362 ,hr_api.g_number) then
363 l_argument := 'oipl_id';
364 raise l_error;
365 end if;
366
367 if nvl(p_rec.column_type, hr_api.g_varchar2) <>
368 nvl(per_res_shd.g_old_rec.column_type
369 ,hr_api.g_varchar2) then
370 l_argument := 'column_type';
371 raise l_error;
372 end if;
373
374 if nvl(p_rec.column_size, hr_api.g_number) <>
375 nvl(per_res_shd.g_old_rec.column_size
376 ,hr_api.g_number) then
377 l_argument := 'column_size';
378 raise l_error;
379 end if;
380
381 if nvl(p_rec.beneficial_flag, hr_api.g_varchar2) <>
382 nvl(per_res_shd.g_old_rec.beneficial_flag ,hr_api.g_varchar2) then
383 l_argument := 'beneficial_flag';
384 raise l_error;
385 end if;
386
387 if nvl(p_rec.business_group_id, hr_api.g_number) <>
388 nvl(per_res_shd.g_old_rec.business_group_id
389 ,hr_api.g_number) then
390 l_argument := 'business_group_id';
391 raise l_error;
392 end if;
393
394 if nvl(p_rec.legislation_code, hr_api.g_varchar2) <>
395 nvl(per_res_shd.g_old_rec.legislation_code
396 ,hr_api.g_varchar2) then
397 l_argument := 'legislation_code';
398 raise l_error;
399 end if;
400
401 if nvl(p_rec.eligy_prfl_id, hr_api.g_number) <>
402 nvl(per_res_shd.g_old_rec.eligy_prfl_id
403 ,hr_api.g_number) then
404 l_argument := 'eligy_prfl_id';
405 raise l_error;
406 end if;
407
408 if nvl(p_rec.formula_id, hr_api.g_number) <>
409 nvl(per_res_shd.g_old_rec.formula_id
410 ,hr_api.g_number) then
411 l_argument := 'formula_id';
412 raise l_error;
413 end if;
414 --
415 EXCEPTION
416 WHEN l_error THEN
417 hr_api.argument_changed_error
418 (p_api_name => l_proc
419 ,p_argument => l_argument);
420 WHEN OTHERS THEN
421 RAISE;
422 End chk_non_updateable_args;
426 -- ----------------------------------------------------------------------------
423 --
424 -- ----------------------------------------------------------------------------
425 -- |--------------------------< chk_chosen_flag >------------------------------|
427 --
428 -- Description:
429 -- Validates that the value for chosen_flag exists in hr_lookups (lookup_code)
430 -- for the lookup_type 'YES_NO' on the effective date.
431 -- Only called by update_validate since the row_handler deliberately only
432 -- supports update of per_cagr_entitlement_results.
433 --
434 -- Pre-conditions:
435 -- Effective_date must be valid.
436 --
437 -- In Arguments:
438 -- p_cagr_entitlement_result_id
439 -- p_chosen_flag
440 -- p_effective_date
441 --
442 -- Post Success:
443 -- If a row does exist in hr_lookups for the given chosen_flag value then
444 -- processing continues.
445 --
446 -- Post Failure:
447 -- If a row does not exist in hr_lookups for the given chosen_flag value then
448 -- an application error will be raised and processing is terminated.
449 --
450 -- Access Status:
451 -- Internal Table Handler Use Only.
452 --
453 -- {End Of Comments}
454 -- ----------------------------------------------------------------------------
455 procedure chk_chosen_flag (p_cagr_entitlement_result_id in number
456 ,p_chosen_flag in varchar2
457 ,p_effective_date in date) is
458 --
459 l_proc varchar2(72) := g_package||'chk_chosen_flag';
460 --
461
462 Begin
463 --
464 hr_utility.set_location('Entering:'|| l_proc, 1);
465 --
466 hr_api.mandatory_arg_error
467 (p_api_name => l_proc
468 ,p_argument => 'effective_date'
469 ,p_argument_value => p_effective_date
470 );
471 --
472 if p_chosen_flag is not null then
473 --
474 hr_utility.set_location(l_proc, 5);
475 -- Only proceed with validation if :
476 -- Updating and the value for chosen_flag has changed
477 --
478 if ((p_cagr_entitlement_result_id is not null) and
479 (per_res_shd.g_old_rec.chosen_flag <> p_chosen_flag)) then
480 --
481 if hr_api.not_exists_in_hr_lookups
482 (p_effective_date => p_effective_date
483 ,p_lookup_type => 'YES_NO'
484 ,p_lookup_code => p_chosen_flag
485 )
486 then
487 --
488 hr_utility.set_message(800, 'HR_XXXXXX_CAGR_INV_CHOSEN');
489 hr_utility.raise_error;
490 --
491 end if;
492 end if;
493 end if;
494
495 --
496 hr_utility.set_location(' Leaving:'|| l_proc, 20);
497 --
498 End chk_chosen_flag;
499
500 --
501 -- ----------------------------------------------------------------------------
502 -- |----------------------< chk_startup_action >------------------------------|
503 -- ----------------------------------------------------------------------------
504 --
505 -- Description:
506 -- This procedure will check that the current action is allowed according
507 -- to the current startup mode.
508 --
509 -- ----------------------------------------------------------------------------
510 PROCEDURE chk_startup_action
511 (p_insert IN boolean
512 ,p_business_group_id IN number
513 ,p_legislation_code IN varchar2
514 ,p_legislation_subgroup IN varchar2 DEFAULT NULL) IS
515 --
516 BEGIN
517 --
518 -- Call the supporting procedure to check startup mode
519 -- EDIT_HERE: The following call should be edited if certain types of rows
520 -- are not permitted.
521 IF (p_insert) THEN
522 hr_startup_data_api_support.chk_startup_action
523 (p_generic_allowed => TRUE
524 ,p_startup_allowed => TRUE
525 ,p_user_allowed => TRUE
526 ,p_business_group_id => p_business_group_id
527 ,p_legislation_code => p_legislation_code
528 ,p_legislation_subgroup => p_legislation_subgroup
529 );
530 ELSE
531 hr_startup_data_api_support.chk_upd_del_startup_action
532 (p_generic_allowed => TRUE
533 ,p_startup_allowed => TRUE
534 ,p_user_allowed => TRUE
535 ,p_business_group_id => p_business_group_id
536 ,p_legislation_code => p_legislation_code
537 ,p_legislation_subgroup => p_legislation_subgroup
538 );
539 END IF;
540 --
541 END chk_startup_action;
542 --
543 -- ----------------------------------------------------------------------------
544 -- |---------------------------< insert_validate >----------------------------|
545 -- ----------------------------------------------------------------------------
546 Procedure insert_validate
547 (p_effective_date in date
548 ,p_rec in per_res_shd.g_rec_type
549 ) is
550 --
551 l_proc varchar2(72) := g_package||'insert_validate';
552 --
553 Begin
554 hr_utility.set_location('Entering:'||l_proc, 5);
555 --
556 -- Error out - this rowhandler should not be used to insert data as only
557 -- per_cagr_engine_pkg may write records to the per_cagr_entitlement_results table.
558 --
559 hr_utility.set_message(800, 'HR_XXXXX_CAGR_INV_RES_INS');
560 hr_utility.raise_error;
561 --
562 --
563 --
564 --
565 hr_utility.set_location(' Leaving:'||l_proc, 10);
566 End insert_validate;
567 --
568 -- ----------------------------------------------------------------------------
569 -- |---------------------------< update_validate >----------------------------|
570 -- ----------------------------------------------------------------------------
571 Procedure update_validate
572 (p_effective_date in date
573 ,p_rec in per_res_shd.g_rec_type
574 ) is
575 --
576 l_proc varchar2(72) := g_package||'update_validate';
577 --
578 Begin
579 hr_utility.set_location('Entering:'||l_proc, 5);
580 --
581 -- Call all supporting business operations
582 --
583 --
584 --
585 chk_non_updateable_args
586 (p_effective_date => p_effective_date
587 ,p_rec => p_rec
588 );
589 --
590 -- validate chosen flag, when updating only.
591 --
592 chk_chosen_flag (p_cagr_entitlement_result_id => p_rec.cagr_entitlement_result_id
593 ,p_chosen_flag => p_rec.chosen_flag
594 ,p_effective_date => p_effective_date);
595 --
596 --
597 hr_utility.set_location(' Leaving:'||l_proc, 10);
598 End update_validate;
599 --
600 -- ----------------------------------------------------------------------------
601 -- |---------------------------< delete_validate >----------------------------|
602 -- ----------------------------------------------------------------------------
603 Procedure delete_validate
604 (p_rec in per_res_shd.g_rec_type
605 ) is
606 --
607 l_proc varchar2(72) := g_package||'delete_validate';
608 --
609 Begin
610 hr_utility.set_location('Entering:'||l_proc, 5);
611 --
612 --
613 chk_startup_action(false
614 ,per_res_shd.g_old_rec.business_group_id
615 ,per_res_shd.g_old_rec.legislation_code
616 );
617 IF hr_startup_data_api_support.g_startup_mode
618 NOT IN ('GENERIC','STARTUP') THEN
619 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate bus_grp
620 END IF;
621 --
622 -- Call all supporting business operations
623 --
624 hr_utility.set_location(' Leaving:'||l_proc, 10);
625 End delete_validate;
626 --
627 end per_res_bus;