[Home] [Help]
PACKAGE BODY: APPS.GHR_NRE_BUS
Source
1 Package Body ghr_nre_bus as
2 /* $Header: ghnrerhi.pkb 120.1.12010000.1 2009/03/26 10:13:57 utokachi noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ghr_nre_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------< chk_noac_remark_id >------|
12 -- ----------------------------------------------------------------------------
13 --
14 -- Description
15 -- This procedure is used to check that the primary key for the table
16 -- is created properly. It should be null on insert and
17 -- should not be able to be updated.
18 --
19 -- Pre Conditions
20 -- None.
21 --
22 -- In Parameters
23 -- noac_remark_id PK of record being inserted or updated.
24 -- object_version_number Object version number of record being
25 -- inserted or updated.
26 --
27 -- Post Success
28 -- Processing continues
29 --
30 -- Post Failure
31 -- Errors handled by the procedure
32 --
33 -- Access Status
34 -- Internal table handler use only.
35 --
36 Procedure chk_noac_remark_id(p_noac_remark_id in number,
37 p_object_version_number in number) is
38 --
39 l_proc varchar2(72);
40 l_api_updating boolean;
41 --
42 Begin
43 --
44 l_proc := g_package||'chk_noac_remark_id';
45 hr_utility.set_location('Entering:'||l_proc, 5);
46 --
47 l_api_updating := ghr_nre_shd.api_updating
48 (p_noac_remark_id => p_noac_remark_id,
49 p_object_version_number => p_object_version_number);
50 --
51 if (l_api_updating
52 and nvl(p_noac_remark_id,hr_api.g_number)
53 <> ghr_nre_shd.g_old_rec.noac_remark_id) then
54 --
55 -- raise error as PK has changed
56 --
57 ghr_nre_shd.constraint_error('GHR_NOAC_REMARKS_PK');
58 --
59 elsif not l_api_updating then
60 --
61 -- check if PK is null
62 --
63 if p_noac_remark_id is not null then
64 --
65 -- raise error as PK is not null
66 --
67 ghr_nre_shd.constraint_error('GHR_NOAC_REMARKS_PK');
68 --
69 end if;
70 --
71 end if;
72 --
73 hr_utility.set_location('Leaving:'||l_proc, 10);
74 --
75 End chk_noac_remark_id;
76 --
77 -- ----------------------------------------------------------------------------
78 -- |------< chk_remark_id >------|
79 -- ----------------------------------------------------------------------------
80 --
81 -- Description
82 -- This procedure checks that a referenced foreign key actually exists
83 -- in the referenced table.
84 --
85 -- Pre-Conditions
86 -- None.
87 --
88 -- In Parameters
89 -- p_noac_remark_id PK
90 -- p_remark_id ID of FK column
91 -- p_object_version_number object version number
92 --
93 -- Post Success
94 -- Processing continues
95 --
96 -- Post Failure
97 -- Error raised.
98 --
99 -- Access Status
100 -- Internal table handler use only.
101 --
102 Procedure chk_remark_id (p_noac_remark_id in number,
103 p_remark_id in number,
104 p_object_version_number in number) is
105 --
106 l_proc varchar2(72) := g_package||'chk_remark_id';
107 l_api_updating boolean;
111 select null
108 l_dummy varchar2(1);
109 --
110 cursor c1 is
112 from ghr_remarks a
113 where a.remark_id = p_remark_id;
114 --
115 Begin
116 --
117 hr_utility.set_location('Entering:'||l_proc,5);
118 --
119 l_api_updating := ghr_nre_shd.api_updating
120 (p_noac_remark_id => p_noac_remark_id,
121 p_object_version_number => p_object_version_number);
122 --
123 if (l_api_updating
124 and nvl(p_remark_id,hr_api.g_number)
125 <> nvl(ghr_nre_shd.g_old_rec.remark_id,hr_api.g_number)
126 or not l_api_updating) then
127 --
128 -- check if remark_id value exists in ghr_remarks table
129 --
130 open c1;
131 --
132 fetch c1 into l_dummy;
133 if c1%notfound then
134 --
135 close c1;
136 --
137 -- raise error as FK does not relate to PK in ghr_remarks
138 -- table.
139 --
140 ghr_nre_shd.constraint_error('GHR_NOAC_REMARKS_FK2');
141 --
142 end if;
143 --
144 close c1;
145 --
146 end if;
147 --
148 hr_utility.set_location('Leaving:'||l_proc,10);
149 --
150 End chk_remark_id;
151 --
152 -- ----------------------------------------------------------------------------
153 -- |------< chk_nature_of_action_id >------|
154 -- ----------------------------------------------------------------------------
155 --
156 -- Description
157 -- This procedure checks that a referenced foreign key actually exists
158 -- in the referenced table.
159 --
160 -- Pre-Conditions
161 -- None.
162 --
163 -- In Parameters
164 -- p_noac_remark_id PK
165 -- p_nature_of_action_id ID of FK column
166 -- p_object_version_number object version number
167 --
168 -- Post Success
169 -- Processing continues
170 --
171 -- Post Failure
172 -- Error raised.
173 --
174 -- Access Status
175 -- Internal table handler use only.
176 --
177 Procedure chk_nature_of_action_id (p_noac_remark_id in number,
178 p_nature_of_action_id in number,
179 p_object_version_number in number) is
180 --
181 l_proc varchar2(72) := g_package||'chk_nature_of_action_id';
182 l_api_updating boolean;
183 l_dummy varchar2(1);
184 --
185 cursor c1 is
186 select null
187 from ghr_nature_of_actions a
188 where a.nature_of_action_id = p_nature_of_action_id;
189 --
190 Begin
191 --
192 hr_utility.set_location('Entering:'||l_proc,5);
193 --
194 l_api_updating := ghr_nre_shd.api_updating
195 (p_noac_remark_id => p_noac_remark_id,
196 p_object_version_number => p_object_version_number);
197 --
198 if (l_api_updating
199 and nvl(p_nature_of_action_id,hr_api.g_number)
200 <> nvl(ghr_nre_shd.g_old_rec.nature_of_action_id,hr_api.g_number)
201 or not l_api_updating) then
202 --
203 -- check if nature_of_action_id value exists in ghr_nature_of_actions table
204 --
205 open c1;
206 --
207 fetch c1 into l_dummy;
208 if c1%notfound then
209 --
210 close c1;
211 --
212 -- raise error as FK does not relate to PK in ghr_nature_of_actions
213 -- table.
214 --
215 ghr_nre_shd.constraint_error('GHR_NOAC_REMARKS_FK1');
216 --
217 end if;
218 --
219 close c1;
220 --
221 end if;
222 --
223 hr_utility.set_location('Leaving:'||l_proc,10);
224 --
225 End chk_nature_of_action_id;
226 --
227 -- ----------------------------------------------------------------------------
228 -- |------< chk_enabled_flag >------|
229 -- ----------------------------------------------------------------------------
230 --
231 -- Description
232 -- This procedure is used to check that the lookup value is valid.
233 --
234 -- Pre Conditions
235 -- None.
236 --
237 -- In Parameters
238 -- noac_remark_id PK of record being inserted or updated.
239 -- enabled_flag Value of lookup code.
240 -- effective_date effective date
241 -- object_version_number Object version number of record being
242 -- inserted or updated.
243 --
244 -- Post Success
245 -- Processing continues
246 --
247 -- Post Failure
248 -- Error handled by procedure
249 --
250 -- Access Status
251 -- Internal table handler use only.
252 --
253 Procedure chk_enabled_flag(p_noac_remark_id in number,
257 --
254 p_enabled_flag in varchar2,
255 p_effective_date in date,
256 p_object_version_number in number) is
258 l_proc varchar2(72) ;
259 l_api_updating boolean;
260 --
261 Begin
262 --
263 l_proc := g_package||'chk_enabled_flag';
264 hr_utility.set_location('Entering:'||l_proc, 5);
265 --
266 l_api_updating := ghr_nre_shd.api_updating
267 (p_noac_remark_id => p_noac_remark_id,
268 p_object_version_number => p_object_version_number);
269 --
270 if (l_api_updating
271 and p_enabled_flag
272 <> nvl(ghr_nre_shd.g_old_rec.enabled_flag,hr_api.g_varchar2)
273 or not l_api_updating) then
274 --
275 -- check if value of lookup falls within lookup type.
276 --
277 --
278 if hr_api.not_exists_in_hr_lookups
279 (p_lookup_type => 'YES_NO',
280 p_lookup_code => p_enabled_flag,
281 p_effective_date => p_effective_date) then
282 --
283 -- raise error as does not exist as lookup
284 --
285 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
286 hr_utility.raise_error;
287 --
288 end if;
289 --
290 end if;
291 --
292 hr_utility.set_location('Leaving:'||l_proc,10);
293 --
294 end chk_enabled_flag;
295 --
296 -- ----------------------------------------------------------------------------
297 -- |------< chk_required_flag >------|
298 -- ----------------------------------------------------------------------------
299 --
300 -- Description
301 -- This procedure is used to check that the lookup value is valid.
302 --
303 -- Pre Conditions
304 -- None.
305 --
306 -- In Parameters
307 -- noac_remark_id PK of record being inserted or updated.
308 -- required_flag Value of lookup code.
309 -- effective_date effective date
310 -- object_version_number Object version number of record being
311 -- inserted or updated.
312 --
313 -- Post Success
314 -- Processing continues
315 --
316 -- Post Failure
317 -- Error handled by procedure
318 --
319 -- Access Status
320 -- Internal table handler use only.
321 --
322 Procedure chk_required_flag(p_noac_remark_id in number,
323 p_required_flag in varchar2,
324 p_effective_date in date,
325 p_object_version_number in number) is
326 --
327 l_proc varchar2(72) ;
328 l_api_updating boolean;
329 --
330 Begin
331 --
332 l_proc := g_package||'chk_required_flag';
333 hr_utility.set_location('Entering:'||l_proc, 5);
334 --
335 l_api_updating := ghr_nre_shd.api_updating
336 (p_noac_remark_id => p_noac_remark_id,
337 p_object_version_number => p_object_version_number);
338 --
339 if (l_api_updating
340 and p_required_flag
341 <> nvl(ghr_nre_shd.g_old_rec.required_flag,hr_api.g_varchar2)
342 or not l_api_updating) then
343 --
344 -- check if value of lookup falls within lookup type.
345 --
346 --
347 if hr_api.not_exists_in_hr_lookups
348 (p_lookup_type => 'YES_NO',
349 p_lookup_code => p_required_flag,
350 p_effective_date => p_effective_date) then
351 --
352 -- raise error as does not exist as lookup
353 --
354 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
355 hr_utility.raise_error;
356 --
357 end if;
358 --
359 end if;
360 --
361 hr_utility.set_location('Leaving:'||l_proc,10);
362 --
363 end chk_required_flag;
364 --
365 -- ----------------------------------------------------------------------------
366 -- |------< chk_date_from >------|
367 -- ----------------------------------------------------------------------------
368 --
369 -- Description
370 -- This procedure is used to check that the mandatory date_from is entered
371 -- If the date_to parameter is not null then this procedure checks that it
372 -- is greater than or equal to the p_date_from
373 --
374 -- Pre Conditions
375 -- None.
376 --
377 -- In Parameters
378 -- noac_remark_id PK of record being inserted or updated.
379 -- date_from date from
380 -- date_to date to
381 -- object_version_number Object version number of record being
382 -- inserted or updated.
383 --
384 -- Post Success
385 -- Processing continues
386 --
387 -- Post Failure
388 -- Error handled by procedure
389 --
390 -- Access Status
391 -- Internal table handler use only.
392 --
393
394 Procedure chk_date_from ( p_noac_remark_id in number,
395 p_date_from in date,
396 p_date_to in date,
397 p_object_version_number in number) is
398 --
399 l_proc varchar2(72) ;
400 l_api_updating boolean;
401 --
402 Begin
403 --
404 l_proc := g_package||'chk_date_from';
405 hr_utility.set_location('Entering:'||l_proc, 5);
406 --
407 l_api_updating := ghr_nre_shd.api_updating
408 (p_noac_remark_id => p_noac_remark_id,
412 and p_date_from
409 p_object_version_number => p_object_version_number);
410 --
411 if (l_api_updating
413 <> nvl(ghr_nre_shd.g_old_rec.date_from,hr_api.g_date)
414 or not l_api_updating) then
415 --
416 -- check if value of date_from is not null
417 --
418 hr_api.mandatory_arg_error
419 (p_api_name => l_proc
420 ,p_argument => 'date_from'
421 ,p_argument_value => p_date_from
422 );
423 end if;
424 --
425 -- if date_to is not null then we check that date_to is
426 -- greater than or equal to date_from
427 --
428 if p_date_to is not null then
429 if trunc(p_date_from) > trunc(p_date_to) then
430 hr_utility.set_message(8301,'GHR_38196_TO_DATE_LESSER');
431 hr_utility.raise_error;
432 end if;
433 end if;
434
435
436 hr_utility.set_location('Leaving:'||l_proc,10);
437 --
438 end chk_date_from;
439 --
440 --
441 -- ----------------------------------------------------------------------------
442 -- |------< chk_unique_act_rem_id >------|
443 -- ----------------------------------------------------------------------------
444 --
445 -- Description
446 -- This procedure is used to check that the combination of nature_of_action_id
447 -- and remark_id is unique
448 --
449 -- Pre Conditions
450 -- None.
451 --
452 -- In Parameters
453 -- noac_remark_id PK of record being inserted or updated.
454 -- nature_of_action_id
455 -- remark_id
456 -- object_version_number Object version number of record being
457 -- inserted or updated.
458 --
459 -- Post Success
460 -- Processing continues
461 --
462 -- Post Failure
463 -- Error handled by procedure
464 --
465 -- Access Status
466 -- Internal table handler use only.
467 --
468
469 Procedure chk_unique_act_rem_id ( p_noac_remark_id in number,
470 p_nature_of_action_id in number,
471 p_remark_id in number,
472 p_object_version_number in number) is
473 --
474 l_proc varchar2(72);
475 l_api_updating boolean;
476 l_dummy varchar2(1);
477 --
478 cursor c1 is
479 select null
480 from ghr_noac_remarks a
481 where a.nature_of_action_id = p_nature_of_action_id
482 and a.remark_id = p_remark_id;
483 --
484 Begin
485 --
486 l_proc := g_package||'chk_unique_act_rem_id';
487 hr_utility.set_location('Entering:'||l_proc, 5);
488 --
489 l_api_updating := ghr_nre_shd.api_updating
490 (p_noac_remark_id => p_noac_remark_id,
491 p_object_version_number => p_object_version_number);
492 --
493 hr_utility.set_location('Entering:'||l_proc, 10);
494 --
495 if (l_api_updating
496 and ( p_nature_of_action_id
497 <> nvl(ghr_nre_shd.g_old_rec.nature_of_action_id,hr_api.g_number)
498 or
499 p_remark_id
500 <> nvl(ghr_nre_shd.g_old_rec.remark_id,hr_api.g_number)
501 )
502 or not l_api_updating) then
503 --
504 -- check if the combination of action_id and remark_id already exits
505 --
506 --
507 hr_utility.set_location('Entering:'||l_proc, 15);
508 --
509 open c1;
510 --
511 --
512 hr_utility.set_location('Entering:'||l_proc, 16);
513 --
514 fetch c1 into l_dummy;
515 --
516 hr_utility.set_location('Entering:'||l_proc, 17);
517 --
518 if c1%found then
519 --
520 close c1;
521 --
522 --
523 hr_utility.set_location('Entering:'||l_proc, 18);
524 --
525 -- raise error as the combination already exists
526 --
527 hr_utility.set_message(8301,'GHR_NOAC_REM_DUPLICATE');
528 hr_utility.raise_error;
529 end if;
530 --
531 close c1;
532
533 end if;
534 --
535 hr_utility.set_location('Leaving:'||l_proc,25);
536 --
537 end chk_unique_act_rem_id;
538 --
539 -- ----------------------------------------------------------------------------
540 -- |---------------------------< insert_validate >----------------------------|
541 -- ----------------------------------------------------------------------------
542 Procedure insert_validate(p_rec in ghr_nre_shd.g_rec_type
543 ,p_effective_date in date) is
544 --
545 l_proc varchar2(72);
546 --
547 Begin
548 l_proc := g_package||'insert_validate';
549 hr_utility.set_location('Entering:'||l_proc, 5);
550 --
551 -- Set up the CLIENT_INFO
552 --
553 ghr_utility.set_client_info;
554 --
555 -- Call all supporting business operations
556 --
557 chk_noac_remark_id
558 (p_noac_remark_id => p_rec.noac_remark_id,
559 p_object_version_number => p_rec.object_version_number);
560 --
561 chk_remark_id
562 (p_noac_remark_id => p_rec.noac_remark_id,
563 p_remark_id => p_rec.remark_id,
564 p_object_version_number => p_rec.object_version_number);
565 --
566 chk_nature_of_action_id
567 (p_noac_remark_id => p_rec.noac_remark_id,
568 p_nature_of_action_id => p_rec.nature_of_action_id,
569 p_object_version_number => p_rec.object_version_number);
570 --
574 p_effective_date => p_effective_date,
571 chk_enabled_flag
572 (p_noac_remark_id => p_rec.noac_remark_id,
573 p_enabled_flag => p_rec.enabled_flag,
575 p_object_version_number => p_rec.object_version_number);
576 --
577 chk_required_flag
578 (p_noac_remark_id => p_rec.noac_remark_id,
579 p_required_flag => p_rec.required_flag,
580 p_effective_date => p_effective_date,
581 p_object_version_number => p_rec.object_version_number);
582 --
583 chk_date_from
584 (p_noac_remark_id => p_rec.noac_remark_id,
585 p_date_from => p_rec.date_from,
586 p_date_to => p_rec.date_to,
587 p_object_version_number => p_rec.object_version_number);
588 --
589 chk_unique_act_rem_id
590 (p_noac_remark_id => p_rec.noac_remark_id,
591 p_nature_of_action_id => p_rec.nature_of_action_id,
592 p_remark_id => p_rec.remark_id,
593 p_object_version_number => p_rec.object_version_number);
594 --
595 hr_utility.set_location(' Leaving:'||l_proc, 10);
596 End insert_validate;
597 --
598 -- ----------------------------------------------------------------------------
599 -- |---------------------------< update_validate >----------------------------|
600 -- ----------------------------------------------------------------------------
601 Procedure update_validate(p_rec in ghr_nre_shd.g_rec_type
602 ,p_effective_date in date) is
603 --
604 l_proc varchar2(72);
605 --
606 Begin
607 l_proc := g_package||'update_validate';
608 hr_utility.set_location('Entering:'||l_proc, 5);
609 --
610 -- Set up the CLIENT_INFO
611 --
612 ghr_utility.set_client_info;
613 --
614 -- Call all supporting business operations
615 --
616 chk_noac_remark_id
617 (p_noac_remark_id => p_rec.noac_remark_id,
618 p_object_version_number => p_rec.object_version_number);
619 --
620 chk_remark_id
621 (p_noac_remark_id => p_rec.noac_remark_id,
622 p_remark_id => p_rec.remark_id,
623 p_object_version_number => p_rec.object_version_number);
624 --
625 chk_nature_of_action_id
626 (p_noac_remark_id => p_rec.noac_remark_id,
627 p_nature_of_action_id => p_rec.nature_of_action_id,
628 p_object_version_number => p_rec.object_version_number);
629 --
630 chk_enabled_flag
631 (p_noac_remark_id => p_rec.noac_remark_id,
632 p_enabled_flag => p_rec.enabled_flag,
633 p_effective_date => p_effective_date,
634 p_object_version_number => p_rec.object_version_number);
635 --
636 chk_required_flag
637 (p_noac_remark_id => p_rec.noac_remark_id,
638 p_required_flag => p_rec.required_flag,
639 p_effective_date => p_effective_date,
640 p_object_version_number => p_rec.object_version_number);
641 --
642 chk_date_from
643 (p_noac_remark_id => p_rec.noac_remark_id,
644 p_date_from => p_rec.date_from,
645 p_date_to => p_rec.date_to,
646 p_object_version_number => p_rec.object_version_number);
647 --
648 chk_unique_act_rem_id
649 (p_noac_remark_id => p_rec.noac_remark_id,
650 p_nature_of_action_id => p_rec.nature_of_action_id,
651 p_remark_id => p_rec.remark_id,
652 p_object_version_number => p_rec.object_version_number);
653 --
654 --
655 --
656 hr_utility.set_location(' Leaving:'||l_proc, 10);
657 End update_validate;
658 --
659 -- ----------------------------------------------------------------------------
660 -- |---------------------------< delete_validate >----------------------------|
661 -- ----------------------------------------------------------------------------
662 Procedure delete_validate(p_rec in ghr_nre_shd.g_rec_type
663 ,p_effective_date in date) is
664 --
665 l_proc varchar2(72);
666 --
667 Begin
668 l_proc := g_package||'delete_validate';
669 hr_utility.set_location('Entering:'||l_proc, 5);
670 --
671 -- Call all supporting business operations
672 --
673 hr_utility.set_location(' Leaving:'||l_proc, 10);
674 End delete_validate;
675 --
676 end ghr_nre_bus;