[Home] [Help]
PACKAGE BODY: APPS.PQH_WPR_BUS
Source
1 Package Body pqh_wpr_bus as
2 /* $Header: pqwprrhi.pkb 115.4 2002/12/13 00:06:28 rpasapul noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pqh_wpr_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------< chk_worksheet_period_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 -- worksheet_period_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_worksheet_period_id(p_worksheet_period_id in number,
37 p_object_version_number in number) is
38 --
39 l_proc varchar2(72) := g_package||'chk_worksheet_period_id';
40 l_api_updating boolean;
41 --
42 Begin
43 --
44 hr_utility.set_location('Entering:'||l_proc, 5);
45 --
46 l_api_updating := pqh_wpr_shd.api_updating
47 (p_worksheet_period_id => p_worksheet_period_id,
48 p_object_version_number => p_object_version_number);
49 --
50 if (l_api_updating
51 and nvl(p_worksheet_period_id,hr_api.g_number)
52 <> pqh_wpr_shd.g_old_rec.worksheet_period_id) then
53 --
54 -- raise error as PK has changed
55 --
56 pqh_wpr_shd.constraint_error('PQH_WORKSHEET_PERIODS_PK');
57 --
58 elsif not l_api_updating then
59 --
60 -- check if PK is null
61 --
62 if p_worksheet_period_id is not null then
63 --
64 -- raise error as PK is not null
65 --
66 pqh_wpr_shd.constraint_error('PQH_WORKSHEET_PERIODS_PK');
67 --
68 end if;
69 --
70 end if;
71 --
72 hr_utility.set_location('Leaving:'||l_proc, 10);
73 --
74 End chk_worksheet_period_id;
75 --
76 -- ----------------------------------------------------------------------------
77 -- |------< chk_end_time_period_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_worksheet_period_id PK
89 -- p_end_time_period_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_end_time_period_id (p_worksheet_period_id in number,
102 p_end_time_period_id in number,
103 p_object_version_number in number) is
104 --
105 l_proc varchar2(72) := g_package||'chk_end_time_period_id';
106 l_api_updating boolean;
107 l_dummy varchar2(1);
108 --
109 cursor c1 is
110 select null
111 from per_time_periods a
112 where a.time_period_id = p_end_time_period_id;
113 --
114 Begin
115 --
116 hr_utility.set_location('Entering:'||l_proc,5);
117 --
118 l_api_updating := pqh_wpr_shd.api_updating
119 (p_worksheet_period_id => p_worksheet_period_id,
120 p_object_version_number => p_object_version_number);
121 --
122 if (l_api_updating
123 and nvl(p_end_time_period_id,hr_api.g_number)
124 <> nvl(pqh_wpr_shd.g_old_rec.end_time_period_id,hr_api.g_number)
125 or not l_api_updating) then
126 --
127 -- check if end_time_period_id value exists in per_time_periods 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 per_time_periods
137 -- table.
138 --
139 pqh_wpr_shd.constraint_error('PQH_WORKSHEET_PERIODS_FK3');
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_end_time_period_id;
150 --
151 -- ----------------------------------------------------------------------------
152 -- |------< chk_start_time_period_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_worksheet_period_id PK
164 -- p_start_time_period_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_start_time_period_id (p_worksheet_period_id in number,
177 p_start_time_period_id in number,
178 p_object_version_number in number) is
179 --
180 l_proc varchar2(72) := g_package||'chk_start_time_period_id';
181 l_api_updating boolean;
182 l_dummy varchar2(1);
183 --
184 cursor c1 is
185 select null
186 from per_time_periods a
187 where a.time_period_id = p_start_time_period_id;
188 --
189 Begin
190 --
191 hr_utility.set_location('Entering:'||l_proc,5);
192 --
193 l_api_updating := pqh_wpr_shd.api_updating
194 (p_worksheet_period_id => p_worksheet_period_id,
195 p_object_version_number => p_object_version_number);
196 --
197 if (l_api_updating
198 and nvl(p_start_time_period_id,hr_api.g_number)
199 <> nvl(pqh_wpr_shd.g_old_rec.start_time_period_id,hr_api.g_number)
200 or not l_api_updating) then
201 --
202 -- check if start_time_period_id value exists in per_time_periods 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 per_time_periods
212 -- table.
213 --
214 pqh_wpr_shd.constraint_error('PQH_WORKSHEET_PERIODS_FK2');
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_start_time_period_id;
225 --
226 -- ----------------------------------------------------------------------------
227 -- |------< chk_worksheet_detail_id >------|
228 -- ----------------------------------------------------------------------------
229 --
230 -- Description
231 -- This procedure checks that a referenced foreign key actually exists
232 -- in the referenced table.
233 --
234 -- Pre-Conditions
235 -- None.
236 --
237 -- In Parameters
238 -- p_worksheet_period_id PK
239 -- p_worksheet_detail_id ID of FK column
240 -- p_object_version_number object version number
241 --
242 -- Post Success
243 -- Processing continues
244 --
245 -- Post Failure
246 -- Error raised.
247 --
248 -- Access Status
249 -- Internal table handler use only.
250 --
251 Procedure chk_worksheet_detail_id (p_worksheet_period_id in number,
252 p_worksheet_detail_id in number,
253 p_object_version_number in number) is
254 --
255 l_proc varchar2(72) := g_package||'chk_worksheet_detail_id';
256 l_api_updating boolean;
257 l_dummy varchar2(1);
258 --
259 cursor c1 is
260 select null
261 from pqh_worksheet_details a
262 where a.worksheet_detail_id = p_worksheet_detail_id;
263 --
264 Begin
265 --
266 hr_utility.set_location('Entering:'||l_proc,5);
267 --
268 l_api_updating := pqh_wpr_shd.api_updating
269 (p_worksheet_period_id => p_worksheet_period_id,
270 p_object_version_number => p_object_version_number);
271 --
272 if (l_api_updating
273 and nvl(p_worksheet_detail_id,hr_api.g_number)
274 <> nvl(pqh_wpr_shd.g_old_rec.worksheet_detail_id,hr_api.g_number)
275 or not l_api_updating) then
276 --
277 -- check if worksheet_detail_id value exists in pqh_worksheet_details table
278 --
279 open c1;
280 --
281 fetch c1 into l_dummy;
282 if c1%notfound then
283 --
284 close c1;
285 --
286 -- raise error as FK does not relate to PK in pqh_worksheet_details
287 -- table.
288 --
289 pqh_wpr_shd.constraint_error('PQH_WORKSHEET_PERIODS_FK1');
290 --
291 end if;
292 --
293 close c1;
294 --
295 end if;
296 --
297 hr_utility.set_location('Leaving:'||l_proc,10);
298 --
299 End chk_worksheet_detail_id;
300 --
301 --
302 -- ----------------------------------------------------------------------------
303 -- |------< chk_budget_unit3_value_type_cd >------|
304 -- ----------------------------------------------------------------------------
305 --
306 -- Description
307 -- This procedure is used to check that the lookup value is valid.
308 --
309 -- Pre Conditions
310 -- None.
311 --
312 -- In Parameters
313 -- worksheet_period_id PK of record being inserted or updated.
314 -- budget_unit3_value_type_cd Value of lookup code.
315 -- effective_date effective date
316 -- object_version_number Object version number of record being
317 -- inserted or updated.
318 --
319 -- Post Success
320 -- Processing continues
321 --
322 -- Post Failure
323 -- Error handled by procedure
324 --
325 -- Access Status
326 -- Internal table handler use only.
327 --
328 Procedure chk_budget_unit3_value_type_cd(p_worksheet_period_id in number,
329 p_budget_unit3_value_type_cd in varchar2,
330 p_effective_date in date,
331 p_object_version_number in number) is
332 --
333 l_proc varchar2(72) := g_package||'chk_budget_unit3_value_type_cd';
334 l_api_updating boolean;
335 --
336 Begin
337 --
338 hr_utility.set_location('Entering:'||l_proc, 5);
339 --
340 l_api_updating := pqh_wpr_shd.api_updating
341 (p_worksheet_period_id => p_worksheet_period_id,
342 p_object_version_number => p_object_version_number);
343 --
344 if (l_api_updating
345 and p_budget_unit3_value_type_cd
346 <> nvl(pqh_wpr_shd.g_old_rec.budget_unit3_value_type_cd,hr_api.g_varchar2)
347 or not l_api_updating)
348 and p_budget_unit3_value_type_cd is not null then
349 --
350 -- check if value of lookup falls within lookup type.
351 --
352 if hr_api.not_exists_in_hr_lookups
353 (p_lookup_type => 'PQH_BUDGET_UNIT_VALUE_TYPE',
354 p_lookup_code => p_budget_unit3_value_type_cd,
355 p_effective_date => p_effective_date) then
356 --
357 -- raise error as does not exist as lookup
358 --
359 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
360 hr_utility.raise_error;
361 --
362 end if;
363 --
364 end if;
365 --
366 hr_utility.set_location('Leaving:'||l_proc,10);
367 --
368 end chk_budget_unit3_value_type_cd;
369 --
370 -- ----------------------------------------------------------------------------
371 -- |------< chk_budget_unit2_value_type_cd >------|
372 -- ----------------------------------------------------------------------------
373 --
374 -- Description
375 -- This procedure is used to check that the lookup value is valid.
376 --
377 -- Pre Conditions
378 -- None.
379 --
380 -- In Parameters
381 -- worksheet_period_id PK of record being inserted or updated.
382 -- budget_unit2_value_type_cd Value of lookup code.
383 -- effective_date effective date
384 -- object_version_number Object version number of record being
385 -- inserted or updated.
386 --
387 -- Post Success
388 -- Processing continues
389 --
390 -- Post Failure
391 -- Error handled by procedure
392 --
393 -- Access Status
394 -- Internal table handler use only.
395 --
396 Procedure chk_budget_unit2_value_type_cd(p_worksheet_period_id in number,
397 p_budget_unit2_value_type_cd in varchar2,
398 p_effective_date in date,
399 p_object_version_number in number) is
400 --
401 l_proc varchar2(72) := g_package||'chk_budget_unit2_value_type_cd';
402 l_api_updating boolean;
403 --
404 Begin
405 --
406 hr_utility.set_location('Entering:'||l_proc, 5);
407 --
408 l_api_updating := pqh_wpr_shd.api_updating
409 (p_worksheet_period_id => p_worksheet_period_id,
410 p_object_version_number => p_object_version_number);
411 --
412 if (l_api_updating
413 and p_budget_unit2_value_type_cd
414 <> nvl(pqh_wpr_shd.g_old_rec.budget_unit2_value_type_cd,hr_api.g_varchar2)
415 or not l_api_updating)
416 and p_budget_unit2_value_type_cd is not null then
417 --
418 -- check if value of lookup falls within lookup type.
419 --
420 if hr_api.not_exists_in_hr_lookups
421 (p_lookup_type => 'PQH_BUDGET_UNIT_VALUE_TYPE',
422 p_lookup_code => p_budget_unit2_value_type_cd,
423 p_effective_date => p_effective_date) then
424 --
425 -- raise error as does not exist as lookup
426 --
427 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
428 hr_utility.raise_error;
429 --
430 end if;
431 --
432 end if;
433 --
434 hr_utility.set_location('Leaving:'||l_proc,10);
435 --
436 end chk_budget_unit2_value_type_cd;
437 --
438 -- ----------------------------------------------------------------------------
439 -- |------< chk_budget_unit1_value_type_cd >------|
440 -- ----------------------------------------------------------------------------
441 --
442 -- Description
443 -- This procedure is used to check that the lookup value is valid.
444 --
445 -- Pre Conditions
446 -- None.
447 --
448 -- In Parameters
449 -- worksheet_period_id PK of record being inserted or updated.
453 -- inserted or updated.
450 -- budget_unit1_value_type_cd Value of lookup code.
451 -- effective_date effective date
452 -- object_version_number Object version number of record being
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 Procedure chk_budget_unit1_value_type_cd(p_worksheet_period_id in number,
465 p_budget_unit1_value_type_cd in varchar2,
466 p_effective_date in date,
467 p_object_version_number in number) is
468 --
469 l_proc varchar2(72) := g_package||'chk_budget_unit1_value_type_cd';
470 l_api_updating boolean;
471 --
472 Begin
473 --
474 hr_utility.set_location('Entering:'||l_proc, 5);
475 --
476 l_api_updating := pqh_wpr_shd.api_updating
477 (p_worksheet_period_id => p_worksheet_period_id,
478 p_object_version_number => p_object_version_number);
479 --
480 if (l_api_updating
481 and p_budget_unit1_value_type_cd
482 <> nvl(pqh_wpr_shd.g_old_rec.budget_unit1_value_type_cd,hr_api.g_varchar2)
483 or not l_api_updating)
484 and p_budget_unit1_value_type_cd is not null then
485 --
486 -- check if value of lookup falls within lookup type.
487 --
488 if hr_api.not_exists_in_hr_lookups
489 (p_lookup_type => 'PQH_BUDGET_UNIT_VALUE_TYPE',
490 p_lookup_code => p_budget_unit1_value_type_cd,
491 p_effective_date => p_effective_date) then
492 --
493 -- raise error as does not exist as lookup
494 --
495 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
496 hr_utility.raise_error;
497 --
498 end if;
499 --
500 end if;
501 --
502 hr_utility.set_location('Leaving:'||l_proc,10);
503 --
504 end chk_budget_unit1_value_type_cd;
505 --
506 --
507 -- Additional checks
508 --
509 Procedure chk_numeric_values
510 (p_worksheet_period_id in number,
511 p_budget_unit1_percent in number,
512 p_budget_unit1_value in number,
513 p_budget_unit2_percent in number,
514 p_budget_unit2_value in number,
515 p_budget_unit3_percent in number,
516 p_budget_unit3_value in number,
517 p_object_version_number in number) is
518 --
519 l_proc varchar2(72) := g_package||'chk_numeric_values';
520 l_api_updating boolean;
521 --
522 --
523 Begin
524 --
525 hr_utility.set_location('Entering:'||l_proc,5);
526 --
527 l_api_updating := pqh_wpr_shd.api_updating
528 (p_worksheet_period_id => p_worksheet_period_id,
529 p_object_version_number => p_object_version_number);
530 --
531 if (l_api_updating
532 and nvl(p_budget_unit1_percent,hr_api.g_number)
533 <> nvl(pqh_wpr_shd.g_old_rec.budget_unit1_percent,hr_api.g_number)
534 or not l_api_updating) and
535 p_budget_unit1_percent is not null then
536 --
537 -- Raise error if the value is negative
538 --
539 If p_budget_unit1_percent < 0 then
540 hr_utility.set_message(8302,'PQH_INVALID_BDGT_UNIT_PERCENT');
541 hr_utility.raise_error;
542 End if;
543 end if;
544 --
545 --
546 if (l_api_updating
547 and nvl(p_budget_unit1_value,hr_api.g_number)
548 <> nvl(pqh_wpr_shd.g_old_rec.budget_unit1_value,hr_api.g_number)
549 or not l_api_updating) and
550 p_budget_unit1_value is not null then
551 --
552 -- Raise error if the value is negative
553 --
554 If p_budget_unit1_value < 0 then
555 hr_utility.set_message(8302,'PQH_INVALID_BDGT_UNIT_VALUE');
556 hr_utility.raise_error;
557 End if;
558 end if;
559 --
560 /**
561 --
562 if (l_api_updating
563 and nvl(p_budget_unit1_available,hr_api.g_number)
564 <> nvl(pqh_wpr_shd.g_old_rec.budget_unit1_available,hr_api.g_number)
565 or not l_api_updating) and
566 p_budget_unit1_available is not null then
567 --
568 -- Raise error if the value is negative
569 --
570 If p_budget_unit1_available < 0 then
571 hr_utility.set_message(8302,'PQH_INVALID_UNIT1_CONSUMED');
572 hr_utility.raise_error;
573 End if;
574 end if;
575 --
576 **/
577 --
578 --
579 if (l_api_updating
580 and nvl(p_budget_unit2_percent,hr_api.g_number)
581 <> nvl(pqh_wpr_shd.g_old_rec.budget_unit2_percent,hr_api.g_number)
582 or not l_api_updating) and
583 p_budget_unit2_percent is not null then
584 --
585 -- Raise error if the value is negative
586 --
587 If p_budget_unit2_percent < 0 then
588 hr_utility.set_message(8302,'PQH_INVALID_BDGT_UNIT_PERCENT');
592 --
589 hr_utility.raise_error;
590 End if;
591 end if;
593 --
594 if (l_api_updating
595 and nvl(p_budget_unit2_value,hr_api.g_number)
596 <> nvl(pqh_wpr_shd.g_old_rec.budget_unit2_value,hr_api.g_number)
597 or not l_api_updating) and
598 p_budget_unit2_value is not null then
599 --
600 -- Raise error if the value is negative
601 --
602 If p_budget_unit2_value < 0 then
603 hr_utility.set_message(8302,'PQH_INVALID_BDGT_UNIT_VALUE');
604 hr_utility.raise_error;
605 End if;
606 end if;
607 --
608 --
609 /**
610 if (l_api_updating
611 and nvl(p_budget_unit2_available,hr_api.g_number)
612 <> nvl(pqh_wpr_shd.g_old_rec.budget_unit2_available,hr_api.g_number)
613 or not l_api_updating) and
614 p_budget_unit2_available is not null then
615 --
616 -- Raise error if the value is negative
617 --
618 If p_budget_unit2_available < 0 then
619 hr_utility.set_message(8302,'PQH_INVALID_UNIT2_CONSUMED');
620 hr_utility.raise_error;
621 End if;
622 end if;
623 **/
624 --
625 --
626 if (l_api_updating
627 and nvl(p_budget_unit3_percent,hr_api.g_number)
628 <> nvl(pqh_wpr_shd.g_old_rec.budget_unit3_percent,hr_api.g_number)
629 or not l_api_updating) and
630 p_budget_unit3_percent is not null then
631 --
632 -- Raise error if the value is negative
633 --
634 If p_budget_unit3_percent < 0 then
635 hr_utility.set_message(8302,'PQH_INVALID_BDGT_UNIT_PERCENT');
636 hr_utility.raise_error;
637 End if;
638 end if;
639 --
640 --
641 if (l_api_updating
642 and nvl(p_budget_unit3_value,hr_api.g_number)
643 <> nvl(pqh_wpr_shd.g_old_rec.budget_unit3_value,hr_api.g_number)
644 or not l_api_updating) and
645 p_budget_unit3_value is not null then
646 --
647 -- Raise error if the value is negative
648 --
649 If p_budget_unit3_value < 0 then
650 hr_utility.set_message(8302,'PQH_INVALID_BDGT_UNIT_VALUE');
651 hr_utility.raise_error;
652 End if;
653 end if;
654 --
655 --
656 /**
657 if (l_api_updating
658 and nvl(p_budget_unit3_available,hr_api.g_number)
659 <> nvl(pqh_wpr_shd.g_old_rec.budget_unit3_available,hr_api.g_number)
660 or not l_api_updating) and
661 p_budget_unit3_available is not null then
662 --
663 -- Raise error if the value is negative
664 --
665 If p_budget_unit3_available < 0 then
666 hr_utility.set_message(8302,'PQH_INVALID_UNIT3_CONSUMED');
667 hr_utility.raise_error;
668 End if;
669 end if;
670 **/
671 --
672 --
673 hr_utility.set_location('Leaving:'||l_proc,10);
674 --
675 End chk_numeric_values;
676 --
677 --
678 Procedure chk_invalid_time_periods
679 (p_worksheet_period_id in number,
680 p_worksheet_detail_id in number,
681 p_start_time_period_id in number,
682 p_end_time_period_id in number,
683 p_object_version_number in number) is
684 --
685 l_proc varchar2(72) := g_package||'chk_invalid_time_periods';
686 l_api_updating boolean;
687 l_dummy varchar2(1);
688 --
689 l_start_date per_time_periods.start_date%type;
690 l_end_date per_time_periods.end_date%type;
691 l_budget_start_date pqh_budgets.budget_start_date%type;
692 l_budget_end_date pqh_budgets.budget_end_date%type;
693 --
694 cursor c1 is
695 select start_date
696 from per_time_periods a
697 where a.time_period_id = p_start_time_period_id;
698 --
699 --
700 cursor c2 is
701 select end_date
702 from per_time_periods a
703 where a.time_period_id = p_end_time_period_id;
704 --
705 Cursor c3 is
706 Select bdt.BUDGET_START_DATE,bdt.BUDGET_END_DATE
707 From pqh_budgets bdt, pqh_worksheets wks , pqh_worksheet_details wdt
708 Where wdt.worksheet_detail_id = p_worksheet_detail_id
709 ANd wdt.worksheet_id = wks.worksheet_id
710 AND wks.budget_id = bdt.budget_id;
711 Begin
712 --
713 --
714 hr_utility.set_location('Entering:'||l_proc,5);
715 --
716 -- CHECK IF Start date < End date
717 --
718 Open c1;
719 Fetch c1 into l_start_date;
720 if c1%notfound then
721 --
722 close c1;
723 --
724 -- raise error as FK does not relate to PK in per_time_periods
725 -- table.
726 --
727 pqh_wpr_shd.constraint_error('PQH_WORKSHEET_PERIODS_FK2');
728 --
729 end if;
730 Close c1;
731 --
732 Open c2;
733 Fetch c2 into l_end_date;
734 if c2%notfound then
735 --
736 close c1;
737 --
738 -- raise error as FK does not relate to PK in per_time_periods
739 -- table.
743 end if;
740 --
741 pqh_wpr_shd.constraint_error('PQH_WORKSHEET_PERIODS_FK3');
742 --
744 Close c2;
745 --
746 l_api_updating := pqh_wpr_shd.api_updating
747 (p_worksheet_period_id => p_worksheet_period_id,
748 p_object_version_number => p_object_version_number);
749 --
750 if (l_api_updating
751 and ( nvl(p_start_time_period_id,hr_api.g_number) <>
752 nvl(pqh_wpr_shd.g_old_rec.start_time_period_id,hr_api.g_number)
753 OR
754 nvl(p_end_time_period_id,hr_api.g_number) <>
755 nvl(pqh_wpr_shd.g_old_rec.end_time_period_id,hr_api.g_number)
756 )
757 or not l_api_updating) then
758 --
759 If l_start_date > l_end_date then
760 hr_utility.set_message(8302,'PQH_WPR_INVALID_TIME_PERIODS');
761 hr_utility.raise_error;
762 End if;
763 --
764 -- CHECK IF THE TIME-PERIOD OF THE WORKSHEET LIES within time-period
765 -- of Budget
766 --
767 Open c3;
768 Fetch c3 into l_budget_start_date,l_budget_end_date;
769 /** A Budget must have a start and end date
770 if c3%notfound then
771 --
772 close c3;
773 --
774 -- raise error as FK does not relate to PK in per_time_periods
775 -- table.
776 --
777 hr_utility.set_message(8302,'PQH_BUDGET_DATE_NOT_FOUND');
778 hr_utility.raise_error;
779 --
780 end if;
781 **/
782 Close c3;
783 --
784 If l_start_date < l_budget_start_date OR
785 l_end_date > l_budget_end_date then
786 hr_utility.set_message(8302,'PQH_WKS_PERIOD_OUTSIDE_BDGT');
787 hr_utility.raise_error;
788 End if;
789 --
790 end if;
791 --
792 hr_utility.set_location('Leaving:'||l_proc,10);
793 --
794 End chk_invalid_time_periods;
795 --
796 --
797 -- ----------------------------------------------------------------------------
798 -- |---------------------------< insert_validate >----------------------------|
799 -- ----------------------------------------------------------------------------
800 Procedure insert_validate(p_rec in pqh_wpr_shd.g_rec_type
801 ,p_effective_date in date) is
802 --
803 l_proc varchar2(72) := g_package||'insert_validate';
804 --
805 Begin
806 hr_utility.set_location('Entering:'||l_proc, 5);
807 --
808 -- Call all supporting business operations
809 --
810 chk_worksheet_period_id
811 (p_worksheet_period_id => p_rec.worksheet_period_id,
812 p_object_version_number => p_rec.object_version_number);
813 --
814 chk_end_time_period_id
815 (p_worksheet_period_id => p_rec.worksheet_period_id,
816 p_end_time_period_id => p_rec.end_time_period_id,
817 p_object_version_number => p_rec.object_version_number);
818 --
819 chk_start_time_period_id
820 (p_worksheet_period_id => p_rec.worksheet_period_id,
821 p_start_time_period_id => p_rec.start_time_period_id,
822 p_object_version_number => p_rec.object_version_number);
823 --
824 chk_worksheet_detail_id
825 (p_worksheet_period_id => p_rec.worksheet_period_id,
826 p_worksheet_detail_id => p_rec.worksheet_detail_id,
827 p_object_version_number => p_rec.object_version_number);
828 --
829 chk_budget_unit3_value_type_cd
830 (p_worksheet_period_id => p_rec.worksheet_period_id,
831 p_budget_unit3_value_type_cd => p_rec.budget_unit3_value_type_cd,
832 p_effective_date => p_effective_date,
833 p_object_version_number => p_rec.object_version_number);
834 --
835 chk_budget_unit2_value_type_cd
836 (p_worksheet_period_id => p_rec.worksheet_period_id,
837 p_budget_unit2_value_type_cd => p_rec.budget_unit2_value_type_cd,
838 p_effective_date => p_effective_date,
839 p_object_version_number => p_rec.object_version_number);
840 --
841 chk_budget_unit1_value_type_cd
842 (p_worksheet_period_id => p_rec.worksheet_period_id,
843 p_budget_unit1_value_type_cd => p_rec.budget_unit1_value_type_cd,
844 p_effective_date => p_effective_date,
845 p_object_version_number => p_rec.object_version_number);
846 --
847 chk_invalid_time_periods
848 (p_worksheet_period_id => p_rec.worksheet_period_id,
849 p_worksheet_detail_id => p_rec.worksheet_detail_id,
850 p_start_time_period_id => p_rec.start_time_period_id,
851 p_end_time_period_id => p_rec.end_time_period_id,
852 p_object_version_number => p_rec.object_version_number);
853 --
854 chk_numeric_values
855 (p_worksheet_period_id => p_rec.worksheet_period_id,
856 p_budget_unit1_percent => p_rec.budget_unit1_percent,
857 p_budget_unit1_value => p_rec.budget_unit1_value,
858 p_budget_unit2_percent => p_rec.budget_unit2_percent,
859 p_budget_unit2_value => p_rec.budget_unit2_value,
860 p_budget_unit3_percent => p_rec.budget_unit3_percent,
861 p_budget_unit3_value => p_rec.budget_unit3_value,
862 p_object_version_number => p_rec.object_version_number);
863 --
864 --
865 --
866 hr_utility.set_location(' Leaving:'||l_proc, 10);
867 End insert_validate;
868 --
869 -- ----------------------------------------------------------------------------
870 -- |---------------------------< update_validate >----------------------------|
871 -- ----------------------------------------------------------------------------
872 Procedure update_validate(p_rec in pqh_wpr_shd.g_rec_type
873 ,p_effective_date in date) is
874 --
875 l_proc varchar2(72) := g_package||'update_validate';
876 --
877 Begin
878 hr_utility.set_location('Entering:'||l_proc, 5);
879 --
880 -- Call all supporting business operations
881 --
882 chk_worksheet_period_id
883 (p_worksheet_period_id => p_rec.worksheet_period_id,
884 p_object_version_number => p_rec.object_version_number);
885 --
886 chk_end_time_period_id
887 (p_worksheet_period_id => p_rec.worksheet_period_id,
888 p_end_time_period_id => p_rec.end_time_period_id,
889 p_object_version_number => p_rec.object_version_number);
890 --
891 chk_start_time_period_id
892 (p_worksheet_period_id => p_rec.worksheet_period_id,
893 p_start_time_period_id => p_rec.start_time_period_id,
894 p_object_version_number => p_rec.object_version_number);
895 --
896 chk_worksheet_detail_id
897 (p_worksheet_period_id => p_rec.worksheet_period_id,
898 p_worksheet_detail_id => p_rec.worksheet_detail_id,
899 p_object_version_number => p_rec.object_version_number);
900 --
901 chk_budget_unit3_value_type_cd
902 (p_worksheet_period_id => p_rec.worksheet_period_id,
903 p_budget_unit3_value_type_cd => p_rec.budget_unit3_value_type_cd,
904 p_effective_date => p_effective_date,
905 p_object_version_number => p_rec.object_version_number);
906 --
907 chk_budget_unit2_value_type_cd
908 (p_worksheet_period_id => p_rec.worksheet_period_id,
909 p_budget_unit2_value_type_cd => p_rec.budget_unit2_value_type_cd,
910 p_effective_date => p_effective_date,
911 p_object_version_number => p_rec.object_version_number);
912 --
913 chk_budget_unit1_value_type_cd
914 (p_worksheet_period_id => p_rec.worksheet_period_id,
915 p_budget_unit1_value_type_cd => p_rec.budget_unit1_value_type_cd,
916 p_effective_date => p_effective_date,
917 p_object_version_number => p_rec.object_version_number);
918 --
919 chk_invalid_time_periods
920 (p_worksheet_period_id => p_rec.worksheet_period_id,
921 p_worksheet_detail_id => p_rec.worksheet_detail_id,
922 p_start_time_period_id => p_rec.start_time_period_id,
923 p_end_time_period_id => p_rec.end_time_period_id,
924 p_object_version_number => p_rec.object_version_number);
925 --
926 chk_numeric_values
927 (p_worksheet_period_id => p_rec.worksheet_period_id,
928 p_budget_unit1_percent => p_rec.budget_unit1_percent,
929 p_budget_unit1_value => p_rec.budget_unit1_value,
930 p_budget_unit2_percent => p_rec.budget_unit2_percent,
931 p_budget_unit2_value => p_rec.budget_unit2_value,
932 p_budget_unit3_percent => p_rec.budget_unit3_percent,
933 p_budget_unit3_value => p_rec.budget_unit3_value,
934 p_object_version_number => p_rec.object_version_number);
935 --
936 --
937 --
938 hr_utility.set_location(' Leaving:'||l_proc, 10);
939 End update_validate;
940 --
941 -- ----------------------------------------------------------------------------
942 -- |---------------------------< delete_validate >----------------------------|
943 -- ----------------------------------------------------------------------------
944 Procedure delete_validate(p_rec in pqh_wpr_shd.g_rec_type
945 ,p_effective_date in date) is
946 --
947 l_proc varchar2(72) := g_package||'delete_validate';
948 --
949 Begin
950 hr_utility.set_location('Entering:'||l_proc, 5);
951 --
952 -- Call all supporting business operations
953 --
954 hr_utility.set_location(' Leaving:'||l_proc, 10);
955 End delete_validate;
956 --
957 end pqh_wpr_bus;