1 Package Body pay_etu_bus as
2 /* $Header: pyeturhi.pkb 120.1 2005/11/08 04:51:49 pgongada noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pay_etu_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_element_type_usage_id number default null;
15 --
16 -- ---------------------------------------------------------------------------
17 -- |----------------------< set_security_group_id >--------------------------|
18 -- ---------------------------------------------------------------------------
19 --
20 Procedure set_security_group_id
21 (p_element_type_usage_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 , pay_element_type_usages_f etu
30 where etu.element_type_usage_id = p_element_type_usage_id
31 and pbg.business_group_id = etu.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 => 'element_type_usage_id'
47 ,p_argument_value => p_element_type_usage_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_element_type_usage_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 , pay_element_type_usages_f etu
90 where etu.element_type_usage_id = p_element_type_usage_id
91 and pbg.business_group_id (+) = etu.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 => 'element_type_usage_id'
107 ,p_argument_value => p_element_type_usage_id
108 );
109 --
110 if ( nvl(pay_etu_bus.g_element_type_usage_id, hr_api.g_number)
111 = p_element_type_usage_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 := pay_etu_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 pay_etu_bus.g_element_type_usage_id:= p_element_type_usage_id;
142 pay_etu_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 pay_etu_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 pay_etu_shd.api_updating
190 (p_element_type_usage_id => p_rec.element_type_usage_id
191 ,p_effective_date => p_effective_date
192 ,p_object_version_number => p_rec.object_version_number
193 ) THEN
194 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
195 fnd_message.set_token('PROCEDURE ', l_proc);
196 fnd_message.set_token('STEP ', '5');
197 fnd_message.raise_error;
198 END IF;
199 --
200 hr_utility.set_location(l_proc, 10);
201 --
202 if nvl(p_rec.business_group_id, hr_api.g_number) <>
203 nvl(pay_etu_shd.g_old_rec.business_group_id, hr_api.g_number) then
204 l_argument := 'business_group_id';
205 raise l_error;
206 end if;
207 hr_utility.set_location(l_proc, 15);
208 --
209 if nvl(p_rec.element_type_usage_id,hr_api.g_number) <>
210 nvl(pay_etu_shd.g_old_rec.element_type_usage_id,hr_api.g_number) then
211 l_argument := 'element_type_usage_id';
212 raise l_error;
213 end if;
214 hr_utility.set_location(l_proc, 20);
215 --
216 if nvl(p_rec.legislation_code,hr_api.g_varchar2) <>
217 nvl(pay_etu_shd.g_old_rec.legislation_code,hr_api.g_varchar2) then
218 l_argument := 'legislation_code';
219 raise l_error;
220 end if;
221 hr_utility.set_location(l_proc, 25);
222 --
223 if nvl(p_rec.element_type_id, hr_api.g_number) <>
224 nvl(pay_etu_shd.g_old_rec.element_type_id, hr_api.g_number) then
225 l_argument := 'element_type_id';
226 raise l_error;
227 end if;
228 hr_utility.set_location(l_proc, 30);
229 --
230 if nvl(p_rec.run_type_id,hr_api.g_number) <>
231 nvl(pay_etu_shd.g_old_rec.run_type_id,hr_api.g_number) then
232 l_argument := 'run_type_id';
233 raise l_error;
234 end if;
235 hr_utility.set_location(l_proc, 35);
236 --
237 --commented out as not required
238 /* if nvl(p_rec.inclusion_flag,hr_api.g_varchar2) <>
239 nvl(pay_etu_shd.g_old_rec.inclusion_flag,hr_api.g_varchar2) then
240 l_argument := 'inclusion_flag';
241 raise l_error;
242 end if;
243 hr_utility.set_location(l_proc, 40);
244 --
245 if nvl(p_rec.usage_type,hr_api.g_varchar2) <>
246 nvl(pay_etu_shd.g_old_rec.usage_type,hr_api.g_varchar2) then
247 l_argument := 'usage_type';
248 raise l_error;
249 end if;*/
250 hr_utility.set_location(l_proc, 50);
251 --
252 EXCEPTION
253 WHEN l_error THEN
254 hr_api.argument_changed_error
255 (p_api_name => l_proc
256 ,p_argument => l_argument);
257 WHEN OTHERS THEN
258 RAISE;
259 End chk_non_updateable_args;
260 --
261 -- ---------------------------------------------------------------------------
262 -- |--------------------------< chk_run_type_id >----------------------------|
263 -- ---------------------------------------------------------------------------
264 --
265 -- Description:
266 -- Checks the validity of the run_type_id entered by carrying out
267 -- the following:
268 -- - check that the run_type_id exists
269 -- - check that the following rules apply:
270 --
271 -- Mode Run Type Available Components Resulting usage
272 -- ------ ----------- ------------------------------- ---------------
273 -- USER USER USER, STARTUP, GENERIC USER
274 -- USER STARTUP USER, STARTUP, GENERIC USER
275 -- USER GENERIC USER, STARTUP, GENERIC USER
276 -- STARTUP USER This mode cannot access USER Error
277 -- run types
278 -- STARTUP STARTUP STARTUP, GENERIC STARTUP
279 -- STARTUP GENERIC STARTUP, GENERIC STARTUP
280 -- GENERIC USER This mode cannot access USER Error
281 -- run types
282 -- GENERIC STARTUP This mode cannot access STARTUP Error
283 -- run types
284 -- GENERIC GENERIC GENERIC GENERIC
285 --
286 -- Pre-conditions:
287 -- None
288 --
289 -- In Arguments:
290 -- p_element_type_usage_id
291 -- p_run_type_id
292 -- p_effective_date
293 -- p_business_group_id
294 -- p_legislation_code
295 --
296 -- Post Success:
297 -- If the run_type_id is valid then processing continues
298 --
299 -- Post Failure:
300 -- If any of the following cases are true then an application error will be
301 -- raised and processing is terminated:
302 --
303 -- a) run_type_id does not exist
304 --
305 -- Access Status:
306 -- Internal Row Handler Use Only.
307 --
308 -- ---------------------------------------------------------------------------
309 PROCEDURE chk_run_type_id
310 (p_element_type_usage_id in number
311 ,p_run_type_id in number
312 ,p_effective_date in date
313 ,p_business_group_id in number
314 ,p_legislation_code in varchar2) is
315 --
316 l_proc varchar2(72) := g_package||'chk_run_type_id';
317 l_legislation_code pay_element_type_usages_f.legislation_code%TYPE := Null;
318 l_exists varchar2(1);
319 --
320 CURSOR csr_chk_user_run_type(p_leg_code varchar2) is
321 select 'Y'
322 from pay_run_types_f prt
323 where prt.run_type_id = p_run_type_id
324 and p_effective_date between prt.effective_start_date
325 and prt.effective_end_date
326 and ((prt.business_group_id is not null
327 and prt.business_group_id = p_business_group_id)
328 or (prt.legislation_code is not null
329 and prt.legislation_code = p_leg_code)
330 or (prt.business_group_id is null
331 and prt.legislation_code is null));
332 --
333 CURSOR csr_chk_startup_run_type is
334 select 'Y'
335 from pay_run_types_f prt
336 where prt.run_type_id = p_run_type_id
337 and p_effective_date between prt.effective_start_date
338 and prt.effective_end_date
339 and prt.business_group_id is null
340 and ((p_legislation_code is not null
341 and prt.legislation_code = p_legislation_code)
342 or (prt.legislation_code is null));
343 --
344 CURSOR csr_chk_generic_run_type is
345 select 'Y'
346 from pay_run_types_f prt
347 where prt.run_type_id = p_run_type_id
348 and p_effective_date between prt.effective_start_date
349 and prt.effective_end_date
350 and prt.business_group_id is null
351 and prt.legislation_code is null;
352 --
353 BEGIN
357 -- Only execute the cursor if absolutely necessary.
354 --
355 hr_utility.set_location('Entering: '|| l_proc, 5);
356 --
358 -- a) During update, the run_type_id has actually changed to another not
359 -- null value, i,e, the value passed to this procedure is different to the
360 -- g_old_rec value.
361 -- b) During insert, the run_type_id is null.
362 -- Can tell the difference between insert and update by looking at the
363 -- primary key value. For update it will be not null. For insert it will be
364 -- null because pre_insert has not been called yet.
365 --
366 IF (((p_element_type_usage_id is not null) and
367 nvl(pay_etu_shd.g_old_rec.run_type_id, hr_api.g_number) <>
368 nvl(p_run_type_id, hr_api.g_number)) or
369 (p_element_type_usage_id is null)) THEN
370 --
371 hr_utility.set_location(l_proc, 10);
372 --
373 -- Only need to open the cursor if run_type_id is not null
374 --
375 IF p_run_type_id is not null THEN
376 --
377 IF hr_startup_data_api_support.g_startup_mode = 'GENERIC' THEN
378 hr_utility.set_location(l_proc, 15);
379 --
380 OPEN csr_chk_generic_run_type;
381 FETCH csr_chk_generic_run_type INTO l_exists;
382 IF csr_chk_generic_run_type%NOTFOUND THEN
383 --
384 CLOSE csr_chk_generic_run_type;
385 hr_utility.set_message(801, 'HR_33587_INVALID_RT_FOR_MODE');
386 hr_utility.raise_error;
387 --
388 END IF;
389 CLOSE csr_chk_generic_run_type;
390 --
391 ELSIF hr_startup_data_api_support.g_startup_mode = 'STARTUP' THEN
392 hr_utility.set_location(l_proc, 20);
393 --
394 OPEN csr_chk_startup_run_type;
395 FETCH csr_chk_startup_run_type INTO l_exists;
396 IF csr_chk_startup_run_type%NOTFOUND THEN
397 --
398 CLOSE csr_chk_startup_run_type;
399 hr_utility.set_message(801, 'HR_33587_INVALID_RT_FOR_MODE');
400 hr_utility.raise_error;
401 END IF;
402 CLOSE csr_chk_startup_run_type;
403 --
404 ELSIF hr_startup_data_api_support.g_startup_mode = 'USER' THEN
405 hr_utility.set_location(l_proc, 25);
406 --
407 IF p_element_type_usage_id is not null THEN
408 l_legislation_code := pay_etu_bus.return_legislation_code(p_element_type_usage_id);
409 ELSE
410 l_legislation_code := hr_api.return_legislation_code(p_business_group_id);
411 END IF;
412 --
413 OPEN csr_chk_user_run_type(l_legislation_code);
414 FETCH csr_chk_user_run_type INTO l_exists;
415 IF csr_chk_user_run_type%NOTFOUND THEN
416 --
417 CLOSE csr_chk_user_run_type;
418 hr_utility.set_message(801, 'HR_33589_INVALID_RUN_TYPE');
419 hr_utility.raise_error;
420 END IF;
421 CLOSE csr_chk_user_run_type;
422 --
423 END IF;
424 --
425 END IF;
426 --
427 END IF;
428 --
429 hr_utility.set_location('Leaving: '||l_proc, 30);
430 --
431 end chk_run_type_id;
432 --
433 -- ---------------------------------------------------------------------------
434 -- |------------------------< chk_element_type_id >--------------------------|
435 -- ---------------------------------------------------------------------------
436 --
437 -- Description:
438 -- Checks the validity of the element_type_id entered by carrying out
439 -- the following:
440 -- - check that the element_type_id exists
441 -- - check that the following rules apply:
442 --
443 -- Mode Element Type Available Components Resulting usage
444 -- ------ ----------- ------------------------------- ---------------
445 -- USER USER USER, STARTUP, GENERIC USER
446 -- USER STARTUP USER, STARTUP, GENERIC USER
447 -- USER GENERIC USER, STARTUP, GENERIC USER
448 -- STARTUP USER This mode cannot access USER Error
449 -- element types.
450 -- STARTUP STARTUP STARTUP, GENERIC STARTUP
451 -- STARTUP GENERIC STARTUP, GENERIC STARTUP
452 -- GENERIC USER This mode cannot access USER Error
453 -- element types.
454 -- GENERIC STARTUP This mode cannot access STARTUP Error
455 -- element types.
456 -- GENERIC GENERIC GENERIC GENERIC
457 --
458 -- Pre-conditions:
459 -- None
460 --
461 -- In Arguments:
462 -- p_element_type_usage_id
463 -- p_element_type_id
464 -- p_effective_date
465 -- p_business_group_id
466 -- p_legislation_code
467 --
468 -- Post Success:
469 -- If the element_type_id is valid then processing continues
470 --
471 -- Post Failure:
472 -- If any of the following cases are true then an application error will be
473 -- raised and processing is terminated:
474 --
475 -- a) element_type_id does not exist
476 --
477 -- Access Status:
478 -- Internal Row Handler Use Only.
479 --
483 ,p_element_type_id in number
480 -- ---------------------------------------------------------------------------
481 PROCEDURE chk_element_type_id
482 (p_element_type_usage_id in number
484 ,p_effective_date in date
485 ,p_business_group_id in number
486 ,p_legislation_code in varchar2) is
487 --
488 l_proc varchar2(72) := g_package||'chk_element_type_id';
489 l_legislation_code pay_element_type_usages_f.legislation_code%TYPE := Null;
490 l_exists varchar2(1);
491 --
492 CURSOR csr_chk_user_element_type(p_leg_code varchar2) is
493 select 'Y'
494 from pay_element_types_f pet,
495 pay_run_types_f rty
496 where pet.element_type_id = p_element_type_id
497 and p_effective_date between pet.effective_start_date
498 and pet.effective_end_date
499 and ((pet.business_group_id is not null
500 and pet.business_group_id = p_business_group_id)
501 or (pet.legislation_code is not null
502 and pet.legislation_code = p_leg_code)
503 or (pet.business_group_id is null
504 and pet.legislation_code is null))
505 -- This condition is to check the element if any classification usage(s) is/are defined for the given
506 -- runtype then we need to allow only the elements which belongs to the classification(s).
507 and ((exists (select 'Y' from pay_element_class_usages_f ecu
508 where rty.run_type_id = ecu.run_type_id
509 and ecu.classification_id = pet.classification_id
510 and p_effective_date between ecu.effective_start_date and ecu.effective_end_date
511 and nvl(ecu.legislation_code,nvl(p_leg_code, ' ')) = nvl(p_leg_code, ' ')
512 and nvl(ecu.business_group_id, nvl(p_business_group_id, -1)) = nvl(p_business_group_id,-1)))
513 or ( not exists (select 'Y' from pay_element_class_usages_f ecu
514 where rty.run_type_id = ecu.run_type_id
515 and p_effective_date between ecu.effective_start_date and ecu.effective_end_date
516 and nvl(ecu.legislation_code,nvl(p_leg_code, ' ')) = nvl(p_leg_code, ' ')
517 and nvl(ecu.business_group_id, nvl(p_business_group_id, -1)) = nvl(p_business_group_id,-1)))
518 );
519 --
520 CURSOR csr_chk_startup_element_type is
521 select 'Y'
522 from pay_element_types_f pet,
523 pay_run_types_f rty
524 where pet.element_type_id = p_element_type_id
525 and p_effective_date between pet.effective_start_date
526 and pet.effective_end_date
527 and pet.business_group_id is null
528 and ((p_legislation_code is not null
529 and pet.legislation_code = p_legislation_code)
530 or (pet.legislation_code is null))
531 -- This condition is to check the element, if any classification usage(s) is/are defined for the given
532 -- runtype then we need to allow only the elements which belongs to the classification(s).
533 and ((exists (select 'Y' from pay_element_class_usages_f ecu
534 where rty.run_type_id = ecu.run_type_id
535 and ecu.classification_id = pet.classification_id
536 and p_effective_date between ecu.effective_start_date and ecu.effective_end_date
537 and nvl(ecu.legislation_code,nvl(p_legislation_code, ' ')) = nvl(p_legislation_code, ' ')
538 and nvl(ecu.business_group_id, nvl(p_business_group_id, -1)) = nvl(p_business_group_id,-1)))
539 or ( not exists (select 'Y' from pay_element_class_usages_f ecu
540 where rty.run_type_id = ecu.run_type_id
541 and p_effective_date between ecu.effective_start_date and ecu.effective_end_date
542 and nvl(ecu.legislation_code,nvl(p_legislation_code, ' ')) = nvl(p_legislation_code, ' ')
543 and nvl(ecu.business_group_id, nvl(p_business_group_id, -1)) = nvl(p_business_group_id,-1)))
544 );
545 --
546 CURSOR csr_chk_generic_element_type is
547 select 'Y'
548 from pay_element_types_f pet,
549 pay_run_types_f rty
550 where pet.element_type_id = p_element_type_id
551 and p_effective_date between pet.effective_start_date
552 and pet.effective_end_date
553 and pet.business_group_id is null
554 and pet.legislation_code is null
555 -- This condition is to check the element, if any classification usage(s) is/are defined for the given
556 -- runtype then we need to allow only the elements which belongs to the classification(s).
557 and ((exists (select 'Y' from pay_element_class_usages_f ecu
558 where rty.run_type_id = ecu.run_type_id
559 and ecu.classification_id = pet.classification_id
560 and p_effective_date between ecu.effective_start_date and ecu.effective_end_date
561 and nvl(ecu.legislation_code,nvl(p_legislation_code, ' ')) = nvl(p_legislation_code, ' ')
562 and nvl(ecu.business_group_id, nvl(p_business_group_id, -1)) = nvl(p_business_group_id,-1)))
563 or ( not exists (select 'Y' from pay_element_class_usages_f ecu
564 where rty.run_type_id = ecu.run_type_id
565 and p_effective_date between ecu.effective_start_date and ecu.effective_end_date
566 and nvl(ecu.legislation_code,nvl(p_legislation_code, ' ')) = nvl(p_legislation_code, ' ')
567 and nvl(ecu.business_group_id, nvl(p_business_group_id, -1)) = nvl(p_business_group_id,-1)))
568 );
569 --
570 BEGIN
571 --
572 hr_utility.set_location('Entering: '|| l_proc, 5);
573 --
574 -- Only execute the cursor if absolutely necessary.
575 -- a) During update, the element_type_id has actually changed to another not
576 -- null value, i,e, the value passed to this procedure is different to the
577 -- g_old_rec value.
581 -- null because pre_insert has not been called yet.
578 -- b) During insert, the element_type_id is null.
579 -- Can tell the difference between insert and update by looking at the
580 -- primary key value. For update it will be not null. For insert it will be
582 --
583 IF (((p_element_type_usage_id is not null) and
584 nvl(pay_etu_shd.g_old_rec.element_type_id, hr_api.g_number) <>
585 nvl(p_element_type_id, hr_api.g_number)) or
586 (p_element_type_usage_id is null)) THEN
587 --
588 hr_utility.set_location(l_proc, 10);
589 --
590 -- Only need to open the cursor if element_type_id is not null
591 --
592 IF p_element_type_id is not null THEN
593 --
594 IF hr_startup_data_api_support.g_startup_mode = 'GENERIC' THEN
595 hr_utility.set_location(l_proc, 15);
596 --
597 OPEN csr_chk_generic_element_type;
598 FETCH csr_chk_generic_element_type INTO l_exists;
599 IF csr_chk_generic_element_type%NOTFOUND THEN
600 --
601 CLOSE csr_chk_generic_element_type;
602 hr_utility.set_message(801, 'HR_33588_ETU_INVAL_ET_FOR_MODE');
603 hr_utility.raise_error;
604 --
605 END IF;
606 CLOSE csr_chk_generic_element_type;
607 --
608 ELSIF hr_startup_data_api_support.g_startup_mode = 'STARTUP' THEN
609 hr_utility.set_location(l_proc, 20);
610 --
611 OPEN csr_chk_startup_element_type;
612 FETCH csr_chk_startup_element_type INTO l_exists;
613 IF csr_chk_startup_element_type%NOTFOUND THEN
614 --
615 CLOSE csr_chk_startup_element_type;
616 hr_utility.set_message(801, 'HR_33588_ETU_INVAL_ET_FOR_MODE');
617 hr_utility.raise_error;
618 END IF;
619 CLOSE csr_chk_startup_element_type;
620 --
621 ELSIF hr_startup_data_api_support.g_startup_mode = 'USER' THEN
622 hr_utility.set_location(l_proc, 25);
623 --
624 IF p_element_type_usage_id is not null THEN
625 l_legislation_code := pay_etu_bus.return_legislation_code(p_element_type_usage_id);
626 ELSE
627 l_legislation_code := hr_api.return_legislation_code(p_business_group_id);
628 END IF;
629 --
630 OPEN csr_chk_user_element_type(l_legislation_code);
631 FETCH csr_chk_user_element_type INTO l_exists;
632 IF csr_chk_user_element_type%NOTFOUND THEN
633 --
634 CLOSE csr_chk_user_element_type;
635 hr_utility.set_message(801, 'HR_33590_ETU_INVALID_ELE_TYPE');
636 hr_utility.raise_error;
637 END IF;
638 CLOSE csr_chk_user_element_type;
639 --
640 END IF;
641 --
642 END IF;
643 --
644 END IF;
645 --
646 hr_utility.set_location('Leaving: '||l_proc, 30);
647 --
648 end chk_element_type_id;
649 --
650 -- ----------------------------------------------------------------------------
651 -- |-------------------------< chk_inclusion_flag >---------------------------|
652 -- ----------------------------------------------------------------------------
653 -- {Start Of Comments}
654 --
655 -- Description:
656 -- This procedure is used to ensure that the inclusion_flag is one of the
657 -- following:
658 -- N - No
659 --
660 -- Pre Conditions:
661 -- g_old_rec has been populated with details of the values currently in
662 -- the database.
663 --
664 -- In Arguments:
665 -- p_rec has been populated with the updated values the user would like the
666 -- record set to.
667 --
668 -- Post Success:
669 -- Processing continues if a valid run_method has been entered.
670 --
671 -- Post Failure:
672 -- An application error is raised if a invalid run_method has been entered.
673 --
674 -- {End Of Comments}
675 -- ----------------------------------------------------------------------------
676 Procedure chk_inclusion_flag
677 (p_effective_date in date
678 ,p_validation_start_date in date
679 ,p_validation_end_date in date
680 ,p_inclusion_flag in varchar2) IS
681 --
682 l_proc varchar2(72) := g_package || 'chk_inclusion_flag';
683 l_error EXCEPTION;
684 l_argument varchar2(30);
685 --
686 Begin
687 --
688 -- As inclusion_flag is validated against the standard lookup 'YES_NO' users
689 -- will not be able to add new lookup_codes. Thus only need to validate against
690 -- hr_standard_lookups, even though the table does have a business_group_id
691 -- and would expect to need to validate against hr_lookups.
692 --
693 hr_utility.set_location('Entering: '||l_proc,5);
694 --
695 IF (hr_api.not_exists_in_dt_hrstanlookups
696 (p_effective_date => p_effective_date
697 ,p_validation_start_date => p_validation_start_date
698 ,p_validation_end_date => p_validation_end_date
699 ,p_lookup_type => 'YES_NO'
700 ,p_lookup_code => p_inclusion_flag
701 )
702 OR p_inclusion_flag = 'Y')
703 THEN
704 --
705 -- The inclusion_flag for this record is invalid
706 --
710 hr_utility.set_location(l_proc,10);
707 fnd_message.set_name('PAY','HR_33585_ETU_INCLUSION_FLAG_N');
708 fnd_message.raise_error;
709 --
711 END IF;
712 --
713 hr_utility.set_location('Leaving: '||l_proc,15);
714 --
715 End chk_inclusion_flag;
716 --
717 -- ---------------------------------------------------------------------------
718 -- |--------------------------< chk_bg_leg_code >----------------------------|
719 -- ---------------------------------------------------------------------------
720 --
721 -- Description:
722 -- Checks the validity of the business_group_id and legislation code entered
723 -- by enforcing the following:
724 --
725 -- Mode Business Group ID Legislation Code
726 -- ------------- -------------------- ------------------------------
727 -- USER NOT NULL NULL
728 -- STARTUP NULL NOT NULL
729 -- GENERIC NULL NULL
730 --
731 -- Pre-conditions:
732 -- None
733 --
734 -- In Arguments:
735 -- p_business_group_id
736 -- p_legislation_code
737 --
738 -- Post Success:
739 -- If the combination is valid then processing continues
740 --
741 -- Post Failure:
742 -- If any of the following cases are true then an application error will be
743 -- raised and processing is terminated:
744 --
745 -- a) Combination of business_group_id and legislation_code is anything other
746 -- than detailed above.
747 --
748 -- Access Status:
749 -- Internal Row Handler Use Only.
750 --
751 -- ---------------------------------------------------------------------------
752 PROCEDURE chk_bg_leg_code
753 (p_business_group_id in number
754 ,p_legislation_code in varchar2) is
755 --
756 l_proc varchar2(72) := g_package||'chk_bg_leg_code';
757 --
758 BEGIN
759 --
760 hr_utility.set_location('Entering: '|| l_proc, 5);
761 --
762 IF hr_startup_data_api_support.g_startup_mode = 'GENERIC' THEN
763 --
764 hr_utility.set_location(l_proc, 15);
765 --
766 IF ((p_business_group_id is not null)
767 or (p_legislation_code is not null)) THEN
768 --
769 hr_utility.set_message(801, 'HR_33586_INVALID_BG_LEG_COMBI');
770 hr_utility.raise_error;
771 --
772 END IF;
773 --
774 ELSIF hr_startup_data_api_support.g_startup_mode = 'STARTUP' THEN
775 --
776 hr_utility.set_location(l_proc, 20);
777 --
778 IF ((p_business_group_id is not null)
779 or (p_legislation_code is null)) THEN
780 --
781 hr_utility.set_message(801, 'HR_33586_INVALID_BG_LEG_COMBI');
782 hr_utility.raise_error;
783 --
784 END IF;
785 --
786 ELSIF hr_startup_data_api_support.g_startup_mode = 'USER' THEN
787 --
788 hr_utility.set_location(l_proc, 25);
789 --
790 IF ((p_business_group_id is null)
791 or (p_legislation_code is not null)) THEN
792 --
793 hr_utility.set_message(801, 'HR_33586_INVALID_BG_LEG_COMBI');
794 hr_utility.raise_error;
795 --
796 END IF;
797 --
798 END IF;
799 --
800 hr_utility.set_location('Leaving: '||l_proc, 30);
801 --
802 end chk_bg_leg_code;
803 --
804 -- ----------------------------------------------------------------------------
805 -- |--------------------------< dt_update_validate >--------------------------|
806 -- ----------------------------------------------------------------------------
807 -- {Start Of Comments}
808 --
809 -- Description:
810 -- This procedure is used for referential integrity of datetracked
811 -- parent entities when a datetrack update operation is taking place
812 -- and where there is no cascading of update defined for this entity.
813 --
814 -- Prerequisites:
815 -- This procedure is called from the update_validate.
816 --
817 -- In Parameters:
818 --
819 -- Post Success:
820 -- Processing continues.
821 --
822 -- Post Failure:
823 --
824 -- Developer Implementation Notes:
825 -- This procedure should not need maintenance unless the HR Schema model
826 -- changes.
827 --
828 -- Access Status:
829 -- Internal Row Handler Use Only.
830 --
831 -- {End Of Comments}
832 -- ----------------------------------------------------------------------------
833 Procedure dt_update_validate
834 (p_element_type_id in number default hr_api.g_number
835 ,p_run_type_id in number default hr_api.g_number
836 ,p_datetrack_mode in varchar2
837 ,p_validation_start_date in date
838 ,p_validation_end_date in date
839 ) Is
840 --
841 l_proc varchar2(72) := g_package||'dt_update_validate';
842 l_integrity_error Exception;
843 l_table_name all_tables.table_name%TYPE;
844 --
845 Begin
846 --
847 -- Ensure that the p_datetrack_mode argument is not null
848 --
849 hr_api.mandatory_arg_error
850 (p_api_name => l_proc
851 ,p_argument => 'datetrack_mode'
852 ,p_argument_value => p_datetrack_mode
853 );
854 --
858 --
855 -- Mode will be valid, as this is checked at the start of the upd.
856 --
857 -- Ensure the arguments are not null
859 hr_api.mandatory_arg_error
860 (p_api_name => l_proc
861 ,p_argument => 'validation_start_date'
862 ,p_argument_value => p_validation_start_date
863 );
864 --
865 hr_api.mandatory_arg_error
866 (p_api_name => l_proc
867 ,p_argument => 'validation_end_date'
868 ,p_argument_value => p_validation_end_date
869 );
870 --
871 If ((nvl(p_element_type_id, hr_api.g_number) <> hr_api.g_number) and
872 NOT (dt_api.check_min_max_dates
873 (p_base_table_name => 'pay_element_types_f'
874 ,p_base_key_column => 'ELEMENT_TYPE_ID'
875 ,p_base_key_value => p_element_type_id
876 ,p_from_date => p_validation_start_date
877 ,p_to_date => p_validation_end_date))) Then
878 l_table_name := 'element types';
879 raise l_integrity_error;
880 End If;
881 If ((nvl(p_run_type_id, hr_api.g_number) <> hr_api.g_number) and
882 NOT (dt_api.check_min_max_dates
883 (p_base_table_name => 'pay_run_types_f'
884 ,p_base_key_column => 'RUN_TYPE_ID'
885 ,p_base_key_value => p_run_type_id
886 ,p_from_date => p_validation_start_date
887 ,p_to_date => p_validation_end_date))) Then
888 l_table_name := 'run types';
889 raise l_integrity_error;
890 End If;
891 --
892 Exception
893 When l_integrity_error Then
894 --
895 -- A referential integrity check was violated therefore
896 -- we must error
897 --
898 fnd_message.set_name('PAY', 'HR_7216_DT_UPD_INTEGRITY_ERR');
899 fnd_message.set_token('TABLE_NAME', l_table_name);
900 fnd_message.raise_error;
901 When Others Then
902 --
903 -- An unhandled or unexpected error has occurred which
904 -- we must report
905 --
906 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
907 fnd_message.set_token('PROCEDURE', l_proc);
908 fnd_message.set_token('STEP','15');
909 fnd_message.raise_error;
910 End dt_update_validate;
911 --
912 -- ----------------------------------------------------------------------------
913 -- |--------------------------< dt_delete_validate >--------------------------|
914 -- ----------------------------------------------------------------------------
915 -- {Start Of Comments}
916 --
917 -- Description:
918 -- This procedure is used for referential integrity of datetracked
919 -- child entities when either a datetrack DELETE or ZAP is in operation
920 -- and where there is no cascading of delete defined for this entity.
921 -- For the datetrack mode of DELETE or ZAP we must ensure that no
922 -- datetracked child rows exist between the validation start and end
923 -- dates.
924 --
925 -- Prerequisites:
926 -- This procedure is called from the delete_validate.
927 --
928 -- In Parameters:
929 --
930 -- Post Success:
931 -- Processing continues.
932 --
933 -- Post Failure:
934 -- If a row exists by determining the returning Boolean value from the
935 -- generic dt_api.rows_exist function then we must supply an error via
936 -- the use of the local exception handler l_rows_exist.
937 --
938 -- Developer Implementation Notes:
939 -- This procedure should not need maintenance unless the HR Schema model
940 -- changes.
941 --
942 -- Access Status:
943 -- Internal Row Handler Use Only.
944 --
945 -- {End Of Comments}
946 -- ----------------------------------------------------------------------------
947 Procedure dt_delete_validate
948 (p_element_type_usage_id in number
949 ,p_datetrack_mode in varchar2
950 ,p_validation_start_date in date
951 ,p_validation_end_date in date
952 ) Is
953 --
954 l_proc varchar2(72) := g_package||'dt_delete_validate';
955 l_rows_exist Exception;
956 l_table_name all_tables.table_name%TYPE;
957 --
958 Begin
959 --
960 -- Ensure that the p_datetrack_mode argument is not null
961 --
962 hr_api.mandatory_arg_error
963 (p_api_name => l_proc
964 ,p_argument => 'datetrack_mode'
965 ,p_argument_value => p_datetrack_mode
966 );
967 --
968 -- Only perform the validation if the datetrack mode is either
969 -- DELETE or ZAP
970 --
971 If (p_datetrack_mode = hr_api.g_delete or
972 p_datetrack_mode = hr_api.g_zap) then
973 --
974 --
975 -- Ensure the arguments are not null
976 --
977 hr_api.mandatory_arg_error
978 (p_api_name => l_proc
979 ,p_argument => 'validation_start_date'
980 ,p_argument_value => p_validation_start_date
981 );
982 --
983 hr_api.mandatory_arg_error
984 (p_api_name => l_proc
985 ,p_argument => 'validation_end_date'
986 ,p_argument_value => p_validation_end_date
987 );
988 --
989 hr_api.mandatory_arg_error
990 (p_api_name => l_proc
991 ,p_argument => 'element_type_usage_id'
992 ,p_argument_value => p_element_type_usage_id
996 --
993 );
994 --
995 --
997 End If;
998 --
999 Exception
1000 When l_rows_exist Then
1001 --
1002 -- A referential integrity check was violated therefore
1003 -- we must error
1004 --
1005 fnd_message.set_name('PAY', 'HR_7215_DT_CHILD_EXISTS');
1006 fnd_message.set_token('TABLE_NAME', l_table_name);
1007 fnd_message.raise_error;
1008 When Others Then
1009 --
1010 -- An unhandled or unexpected error has occurred which
1011 -- we must report
1012 --
1013 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
1014 fnd_message.set_token('PROCEDURE', l_proc);
1015 fnd_message.set_token('STEP','15');
1016 fnd_message.raise_error;
1017 --
1018 End dt_delete_validate;
1019 --
1020 -- ----------------------------------------------------------------------------
1021 -- |----------------------< chk_startup_action >------------------------------|
1022 -- ----------------------------------------------------------------------------
1023 --
1024 -- Description:
1025 -- This procedure will check that the current action is allowed according
1026 -- to the current startup mode.
1027 --
1028 -- ----------------------------------------------------------------------------
1029 PROCEDURE chk_startup_action
1030 (p_insert IN boolean
1031 ,p_business_group_id IN number
1032 ,p_legislation_code IN varchar2
1033 ,p_legislation_subgroup IN varchar2 DEFAULT NULL) IS
1034 --
1035 BEGIN
1036 --
1037 -- Call the supporting procedure to check startup mode
1038 --
1039 IF (p_insert) THEN
1040 --
1041 -- Call procedure to check startup_action for inserts.
1042 --
1043 hr_startup_data_api_support.chk_startup_action
1044 (p_generic_allowed => TRUE
1045 ,p_startup_allowed => TRUE
1046 ,p_user_allowed => TRUE
1047 ,p_business_group_id => p_business_group_id
1048 ,p_legislation_code => p_legislation_code
1049 ,p_legislation_subgroup => p_legislation_subgroup
1050 );
1051 ELSE
1052 --
1053 -- Call procedure to check startup_action for updates and deletes.
1054 --
1055 hr_startup_data_api_support.chk_upd_del_startup_action
1056 (p_generic_allowed => TRUE
1057 ,p_startup_allowed => TRUE
1058 ,p_user_allowed => TRUE
1059 ,p_business_group_id => p_business_group_id
1060 ,p_legislation_code => p_legislation_code
1061 ,p_legislation_subgroup => p_legislation_subgroup
1062 );
1063 END IF;
1064 --
1065 END chk_startup_action;
1066 --
1067 -- ----------------------------------------------------------------------------
1068 -- |---------------------------< insert_validate >----------------------------|
1069 -- ----------------------------------------------------------------------------
1070 Procedure insert_validate
1071 (p_rec in pay_etu_shd.g_rec_type
1072 ,p_effective_date in date
1073 ,p_datetrack_mode in varchar2
1074 ,p_validation_start_date in date
1075 ,p_validation_end_date in date
1076 ) is
1077 --
1078 l_proc varchar2(72) := g_package||'insert_validate';
1079 --
1080 Begin
1081 hr_utility.set_location('Entering:'||l_proc, 5);
1082 --
1083 -- Call all supporting business operations
1084 --
1085 chk_startup_action(True
1086 ,p_rec.business_group_id
1087 ,p_rec.legislation_code);
1088 IF hr_startup_data_api_support.g_startup_mode NOT IN ('GENERIC','STARTUP') THEN
1089 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate bus_grp
1090 END IF;
1091 --
1092 hr_utility.set_location(l_proc, 10);
1093 --
1094 chk_run_type_id(p_element_type_usage_id => p_rec.element_type_usage_id
1095 ,p_run_type_id => p_rec.run_type_id
1096 ,p_effective_date => p_effective_date
1097 ,p_business_group_id => p_rec.business_group_id
1098 ,p_legislation_code => p_rec.legislation_code);
1099 --
1100 hr_utility.set_location(l_proc, 15);
1101 --
1102 chk_element_type_id(p_element_type_usage_id => p_rec.element_type_usage_id
1103 ,p_element_type_id => p_rec.element_type_id
1104 ,p_effective_date => p_effective_date
1105 ,p_business_group_id => p_rec.business_group_id
1106 ,p_legislation_code => p_rec.legislation_code);
1107 --
1108 hr_utility.set_location(l_proc, 20);
1109 --
1110 chk_inclusion_flag(p_effective_date => p_effective_date
1111 ,p_validation_start_date => p_validation_start_date
1112 ,p_validation_end_date => p_validation_end_date
1113 ,p_inclusion_flag => p_rec.inclusion_flag);
1114 --
1115 hr_utility.set_location(l_proc, 25);
1116 --
1117 chk_bg_leg_code(p_business_group_id => p_rec.business_group_id
1118 ,p_legislation_code => p_rec.legislation_code);
1119 --
1120 hr_utility.set_location(' Leaving:'||l_proc, 30);
1121 End insert_validate;
1122 --
1123 -- ----------------------------------------------------------------------------
1124 -- |---------------------------< update_validate >----------------------------|
1125 -- ----------------------------------------------------------------------------
1126 Procedure update_validate
1127 (p_rec in pay_etu_shd.g_rec_type
1128 ,p_effective_date in date
1129 ,p_datetrack_mode in varchar2
1130 ,p_validation_start_date in date
1131 ,p_validation_end_date in date
1132 ) is
1133 --
1134 l_proc varchar2(72) := g_package||'update_validate';
1135 --
1136 Begin
1137 hr_utility.set_location('Entering:'||l_proc, 5);
1138 --
1139 -- Call all supporting business operations
1140 --
1141 chk_startup_action(False
1142 ,p_rec.business_group_id
1143 ,p_rec.legislation_code);
1144 IF hr_startup_data_api_support.g_startup_mode NOT IN ('GENERIC','STARTUP') THEN
1145 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate bus_grp
1146 END IF;
1147 --
1148 hr_utility.set_location(l_proc, 10);
1149 --
1150 chk_run_type_id(p_element_type_usage_id => p_rec.element_type_usage_id
1151 ,p_run_type_id => p_rec.run_type_id
1152 ,p_effective_date => p_effective_date
1153 ,p_business_group_id => p_rec.business_group_id
1154 ,p_legislation_code => p_rec.legislation_code);
1155 --
1156 hr_utility.set_location(l_proc, 15);
1157 --
1158 chk_element_type_id(p_element_type_usage_id => p_rec.element_type_usage_id
1159 ,p_element_type_id => p_rec.element_type_id
1160 ,p_effective_date => p_effective_date
1161 ,p_business_group_id => p_rec.business_group_id
1162 ,p_legislation_code => p_rec.legislation_code);
1163 --
1167 ,p_validation_start_date => p_validation_start_date
1164 hr_utility.set_location(l_proc, 20);
1165 --
1166 chk_inclusion_flag(p_effective_date => p_effective_date
1168 ,p_validation_end_date => p_validation_end_date
1169 ,p_inclusion_flag => p_rec.inclusion_flag);
1170 --
1171 hr_utility.set_location(l_proc, 25);
1172 --
1173 chk_bg_leg_code(p_business_group_id => p_rec.business_group_id
1174 ,p_legislation_code => p_rec.legislation_code);
1175 --
1176 hr_utility.set_location(l_proc, 30);
1177 --
1178 -- Call the datetrack update integrity operation
1179 --
1180 dt_update_validate
1181 (p_element_type_id => p_rec.element_type_id
1182 ,p_run_type_id => p_rec.run_type_id
1183 ,p_datetrack_mode => p_datetrack_mode
1184 ,p_validation_start_date => p_validation_start_date
1185 ,p_validation_end_date => p_validation_end_date
1186 );
1187 --
1188 hr_utility.set_location(l_proc, 35);
1189 --
1190 chk_non_updateable_args
1191 (p_effective_date => p_effective_date
1192 ,p_rec => p_rec
1193 );
1194 --
1195 --
1196 hr_utility.set_location(' Leaving:'||l_proc, 40);
1197 End update_validate;
1198 --
1199 -- ----------------------------------------------------------------------------
1200 -- |---------------------------< delete_validate >----------------------------|
1201 -- ----------------------------------------------------------------------------
1202 Procedure delete_validate
1203 (p_rec in pay_etu_shd.g_rec_type
1204 ,p_effective_date in date
1205 ,p_datetrack_mode in varchar2
1206 ,p_validation_start_date in date
1207 ,p_validation_end_date in date
1208 ) is
1209 --
1210 l_proc varchar2(72) := g_package||'delete_validate';
1211 --
1212 Begin
1213 hr_utility.set_location('Entering:'||l_proc, 5);
1214 --
1215 -- Call all supporting business operations
1216 --
1217 chk_startup_action(False
1218 ,pay_etu_shd.g_old_rec.business_group_id
1219 ,pay_etu_shd.g_old_rec.legislation_code);
1220 --
1221 dt_delete_validate
1222 (p_datetrack_mode => p_datetrack_mode
1223 ,p_validation_start_date => p_validation_start_date
1224 ,p_validation_end_date => p_validation_end_date
1225 ,p_element_type_usage_id => p_rec.element_type_usage_id
1226 );
1227 --
1228 hr_utility.set_location(' Leaving:'||l_proc, 10);
1229 End delete_validate;
1230 --
1231 end pay_etu_bus;
[Home] [Help]