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