[Home] [Help]
PACKAGE BODY: APPS.PQH_WDT_BUS
Source
1 Package Body pqh_wdt_bus as
2 /* $Header: pqwdtrhi.pkb 120.0.12000000.1 2007/01/17 00:29:46 appldev noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pqh_wdt_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------< chk_worksheet_detail_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_detail_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_detail_id(p_worksheet_detail_id in number,
37 p_object_version_number in number) is
38 --
39 l_proc varchar2(72) := g_package||'chk_worksheet_detail_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_wdt_shd.api_updating
47 (p_worksheet_detail_id => p_worksheet_detail_id,
48 p_object_version_number => p_object_version_number);
49 --
50 if (l_api_updating
51 and nvl(p_worksheet_detail_id,hr_api.g_number)
52 <> pqh_wdt_shd.g_old_rec.worksheet_detail_id) then
53 --
54 -- raise error as PK has changed
55 --
56 pqh_wdt_shd.constraint_error('PQH_WORKSHEET_DETAILS_PK');
57 --
58 elsif not l_api_updating then
59 --
60 -- check if PK is null
61 --
62 if p_worksheet_detail_id is not null then
63 --
64 -- raise error as PK is not null
65 --
66 pqh_wdt_shd.constraint_error('PQH_WORKSHEET_DETAILS_PK');
67 --
68 end if;
69 --
70 end if;
71 --
72 hr_utility.set_location('Leaving:'||l_proc, 10);
73 --
74 End chk_worksheet_detail_id;
75 --
76 -- ----------------------------------------------------------------------------
77 -- |------< chk_parent_worksheet_detail_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_detail_id PK
89 -- p_parent_worksheet_detail_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_parent_worksheet_detail_id (p_worksheet_detail_id in number,
102 p_parent_worksheet_detail_id in number,
103 p_object_version_number in number) is
104 --
105 l_proc varchar2(72) := g_package||'chk_parent_worksheet_detail_id';
106 l_api_updating boolean;
107 l_dummy varchar2(1);
108 --
109 cursor c1 is
110 select null
111 from pqh_worksheet_details a
112 where a.worksheet_detail_id = p_parent_worksheet_detail_id;
113 --
114 Begin
115 --
116 hr_utility.set_location('Entering:'||l_proc,5);
117 --
118 l_api_updating := pqh_wdt_shd.api_updating
119 (p_worksheet_detail_id => p_worksheet_detail_id,
120 p_object_version_number => p_object_version_number);
121 --
122 if (l_api_updating
123 and nvl(p_parent_worksheet_detail_id,hr_api.g_number)
124 <> nvl(pqh_wdt_shd.g_old_rec.parent_worksheet_detail_id,hr_api.g_number)
125 or not l_api_updating) and
126 p_parent_worksheet_detail_id is not null then
127 --
128 -- check if parent_worksheet_detail_id value exists in pqh_worksheet_details 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 pqh_worksheet_details
138 -- table.
139 --
140 pqh_wdt_shd.constraint_error('PQH_WORKSHEET_DETAILS_FK8');
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_parent_worksheet_detail_id;
151 --
152 -- ----------------------------------------------------------------------------
153 -- |------< chk_worksheet_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_worksheet_detail_id PK
165 -- p_worksheet_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_worksheet_id (p_worksheet_detail_id in number,
178 p_worksheet_id in number,
179 p_object_version_number in number) is
180 --
181 l_proc varchar2(72) := g_package||'chk_worksheet_id';
182 l_api_updating boolean;
183 l_dummy varchar2(1);
184 --
185 cursor c1 is
186 select null
187 from pqh_worksheets a
188 where a.worksheet_id = p_worksheet_id;
189 --
190 Begin
191 --
192 hr_utility.set_location('Entering:'||l_proc,5);
193 --
194 l_api_updating := pqh_wdt_shd.api_updating
195 (p_worksheet_detail_id => p_worksheet_detail_id,
196 p_object_version_number => p_object_version_number);
197 --
198 if (l_api_updating
199 and nvl(p_worksheet_id,hr_api.g_number)
200 <> nvl(pqh_wdt_shd.g_old_rec.worksheet_id,hr_api.g_number)
201 or not l_api_updating) then
202 --
203 -- check if worksheet_id value exists in pqh_worksheets 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 pqh_worksheets
213 -- table.
214 --
215 pqh_wdt_shd.constraint_error('PQH_WORKSHEET_DETAILS_FK7');
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_worksheet_id;
226 --
227 -- ----------------------------------------------------------------------------
228 -- |------< chk_grade_id >------|
229 -- ----------------------------------------------------------------------------
230 --
231 -- Description
232 -- This procedure checks that a referenced foreign key actually exists
233 -- in the referenced table.
234 --
235 -- Pre-Conditions
236 -- None.
237 --
238 -- In Parameters
239 -- p_worksheet_detail_id PK
240 -- p_grade_id ID of FK column
241 -- p_object_version_number object version number
242 --
243 -- Post Success
244 -- Processing continues
245 --
246 -- Post Failure
247 -- Error raised.
248 --
249 -- Access Status
250 -- Internal table handler use only.
251 --
252 Procedure chk_grade_id (p_worksheet_detail_id in number,
253 p_worksheet_id in number,
254 p_grade_id in number,
255 p_object_version_number in number) is
256 --
257 l_proc varchar2(72) := g_package||'chk_grade_id';
258 l_api_updating boolean;
259 l_dummy varchar2(1);
260 l_budgeted_entity_cd pqh_budgets.budgeted_entity_cd%type;
261 --
262 --
263 cursor c1 is
264 select null
265 from per_grades a
266 where a.grade_id = p_grade_id;
267 --
268 Cursor c2 is
269 select budgeted_entity_cd
270 From pqh_budgets bdt,pqh_worksheets wks
271 Where wks.worksheet_id = p_worksheet_id
272 ANd bdt.budget_id = wks.budget_id;
273 --
274 Begin
275 --
276 hr_utility.set_location('Entering:'||l_proc,5);
277 --
278 l_api_updating := pqh_wdt_shd.api_updating
279 (p_worksheet_detail_id => p_worksheet_detail_id,
280 p_object_version_number => p_object_version_number);
281 --
282 if (l_api_updating
283 and nvl(p_grade_id,hr_api.g_number)
284 <> nvl(pqh_wdt_shd.g_old_rec.grade_id,hr_api.g_number)
285 or not l_api_updating) and
286 p_grade_id is not null then
287 --
288 /**
289 --
290 -- Raise error if budgeted entity cd = pos/job/org
291 --
292 Open c2;
293 Fetch c2 into l_budgeted_entity_cd;
294 If c2%notfound then
295 --
296 Close c2;
297 hr_utility.set_message(8302,'PQH_NO_BUDGETED_ENTITY_CD');
298 hr_utility.raise_error;
299 --
300 End if;
301 Close c2;
302 --
303 If l_budgeted_entity_cd IS NOT NULL AND
304 (l_budgeted_entity_cd = 'POSITION' OR
305 l_budgeted_entity_cd = 'JOB' OR
306 l_budgeted_entity_cd = 'ORGANIZATION') then
307 --
308 hr_utility.set_message(8302,'PQH_GRADE_MUST_BE_NULL');
309 hr_utility.raise_error;
310 --
311 End if;
312 **/
313 --
314 -- check if grade_id value exists in per_grades table
315 --
316 open c1;
317 --
318 fetch c1 into l_dummy;
319 if c1%notfound then
320 --
321 close c1;
322 --
323 -- raise error as FK does not relate to PK in per_grades
324 -- table.
325 --
326 pqh_wdt_shd.constraint_error('PQH_WORKSHEET_DETAILS_FK6');
327 --
328 end if;
329 --
330 close c1;
331 --
332 end if;
333 --
334 hr_utility.set_location('Leaving:'||l_proc,10);
335 --
336 End chk_grade_id;
337 --
338 -- ----------------------------------------------------------------------------
339 -- |------< chk_job_id >------|
340 -- ----------------------------------------------------------------------------
341 --
342 -- Description
343 -- This procedure checks that a referenced foreign key actually exists
344 -- in the referenced table.
345 --
346 -- Pre-Conditions
347 -- None.
348 --
349 -- In Parameters
350 -- p_worksheet_detail_id PK
351 -- p_job_id ID of FK column
352 -- p_object_version_number object version number
353 --
354 -- Post Success
355 -- Processing continues
356 --
357 -- Post Failure
358 -- Error raised.
359 --
360 -- Access Status
361 -- Internal table handler use only.
362 --
363 Procedure chk_job_id (p_worksheet_detail_id in number,
364 p_job_id in number,
365 p_object_version_number in number) is
366 --
367 l_proc varchar2(72) := g_package||'chk_job_id';
368 l_api_updating boolean;
369 l_dummy varchar2(1);
370 --
371 cursor c1 is
372 select null
373 from per_jobs a
374 where a.job_id = p_job_id;
375 --
376 Begin
377 --
378 hr_utility.set_location('Entering:'||l_proc,5);
379 --
380 l_api_updating := pqh_wdt_shd.api_updating
381 (p_worksheet_detail_id => p_worksheet_detail_id,
382 p_object_version_number => p_object_version_number);
383 --
384 if (l_api_updating
385 and nvl(p_job_id,hr_api.g_number)
386 <> nvl(pqh_wdt_shd.g_old_rec.job_id,hr_api.g_number)
387 or not l_api_updating) and
388 p_job_id is not null then
389 --
390 -- check if job_id value exists in per_jobs table
391 --
392 open c1;
393 --
394 fetch c1 into l_dummy;
395 if c1%notfound then
396 --
397 close c1;
398 --
399 -- raise error as FK does not relate to PK in per_jobs
400 -- table.
401 --
402 pqh_wdt_shd.constraint_error('PQH_WORKSHEET_DETAILS_FK5');
403 --
404 end if;
405 --
406 close c1;
407 --
408 end if;
409 --
410 hr_utility.set_location('Leaving:'||l_proc,10);
411 --
412 End chk_job_id;
413 --
414 -- ----------------------------------------------------------------------------
415 -- |------< chk_position_transaction_id >------|
416 -- ----------------------------------------------------------------------------
417 --
418 -- Description
419 -- This procedure checks that a referenced foreign key actually exists
420 -- in the referenced table.
421 --
422 -- Pre-Conditions
423 -- None.
424 --
425 -- In Parameters
426 -- p_worksheet_detail_id PK
427 -- p_position_transaction_id ID of FK column
428 -- p_object_version_number object version number
429 --
430 -- Post Success
431 -- Processing continues
432 --
433 -- Post Failure
434 -- Error raised.
435 --
436 -- Access Status
437 -- Internal table handler use only.
438 --
439 Procedure chk_position_transaction_id (p_worksheet_detail_id in number,
440 p_position_transaction_id in number,
441 p_object_version_number in number) is
442 --
443 l_proc varchar2(72) := g_package||'chk_position_transaction_id';
444 l_api_updating boolean;
445 l_dummy varchar2(1);
446 --
447 cursor c1 is
448 select null
449 from pqh_position_transactions a
450 where a.position_transaction_id = p_position_transaction_id;
451 --
452 Begin
453 --
454 hr_utility.set_location('Entering:'||l_proc,5);
455 --
456 l_api_updating := pqh_wdt_shd.api_updating
457 (p_worksheet_detail_id => p_worksheet_detail_id,
458 p_object_version_number => p_object_version_number);
459 --
460 if (l_api_updating
461 and nvl(p_position_transaction_id,hr_api.g_number)
462 <> nvl(pqh_wdt_shd.g_old_rec.position_transaction_id,hr_api.g_number)
463 or not l_api_updating) and
464 p_position_transaction_id is not null then
465 --
466 -- check if position_transaction_id value exists in pqh_position_transactions table
467 --
468 open c1;
469 --
470 fetch c1 into l_dummy;
471 if c1%notfound then
472 --
473 close c1;
474 --
475 -- raise error as FK does not relate to PK in pqh_position_transactions
476 -- table.
477 --
478 pqh_wdt_shd.constraint_error('PQH_WORKSHEET_DETAILS_FK4');
479 --
480 end if;
481 --
482 close c1;
483 --
484 end if;
485 --
486 hr_utility.set_location('Leaving:'||l_proc,10);
487 --
488 End chk_position_transaction_id;
489 --
490 -- ----------------------------------------------------------------------------
491 -- |------< chk_budget_detail_id >------|
492 -- ----------------------------------------------------------------------------
493 --
494 -- Description
495 -- This procedure checks that a referenced foreign key actually exists
496 -- in the referenced table.
497 --
498 -- Pre-Conditions
499 -- None.
500 --
501 -- In Parameters
502 -- p_worksheet_detail_id PK
503 -- p_budget_detail_id ID of FK column
504 -- p_object_version_number object version number
505 --
506 -- Post Success
507 -- Processing continues
508 --
509 -- Post Failure
510 -- Error raised.
511 --
512 -- Access Status
513 -- Internal table handler use only.
514 --
515 Procedure chk_budget_detail_id (p_worksheet_detail_id in number,
516 p_budget_detail_id in number,
517 p_object_version_number in number) is
518 --
519 l_proc varchar2(72) := g_package||'chk_budget_detail_id';
520 l_api_updating boolean;
521 l_dummy varchar2(1);
522 --
523 cursor c1 is
524 select null
525 from pqh_budget_details a
526 where a.budget_detail_id = p_budget_detail_id;
527 --
528 Begin
529 --
530 hr_utility.set_location('Entering:'||l_proc,5);
531 --
532 l_api_updating := pqh_wdt_shd.api_updating
533 (p_worksheet_detail_id => p_worksheet_detail_id,
534 p_object_version_number => p_object_version_number);
535 --
536 if (l_api_updating
537 and nvl(p_budget_detail_id,hr_api.g_number)
538 <> nvl(pqh_wdt_shd.g_old_rec.budget_detail_id,hr_api.g_number)
539 or not l_api_updating) and
540 p_budget_detail_id is not null then
541 --
542 -- check if budget_detail_id value exists in pqh_budget_details table
543 --
544 open c1;
545 --
546 fetch c1 into l_dummy;
547 if c1%notfound then
548 --
549 close c1;
550 --
551 -- raise error as FK does not relate to PK in pqh_budget_details
552 -- table.
553 --
554 pqh_wdt_shd.constraint_error('PQH_WORKSHEET_DETAILS_FK3');
555 --
556 end if;
557 --
558 close c1;
559 --
560 end if;
561 --
562 hr_utility.set_location('Leaving:'||l_proc,10);
563 --
564 End chk_budget_detail_id;
565 --
566 -- ----------------------------------------------------------------------------
567 -- |------< chk_organization_id >------|
568 -- ----------------------------------------------------------------------------
569 --
570 -- Description
571 -- This procedure checks that a referenced foreign key actually exists
572 -- in the referenced table.
573 --
574 -- Pre-Conditions
575 -- None.
576 --
577 -- In Parameters
578 -- p_worksheet_detail_id PK
579 -- p_organization_id ID of FK column
580 -- p_object_version_number object version number
581 --
582 -- Post Success
583 -- Processing continues
584 --
585 -- Post Failure
586 -- Error raised.
587 --
588 -- Access Status
589 -- Internal table handler use only.
590 --
591 Procedure chk_organization_id (p_worksheet_detail_id in number,
592 p_worksheet_id in number,
593 p_organization_id in number,
594 p_object_version_number in number) is
595 --
596 l_proc varchar2(72) := g_package||'chk_organization_id';
597 l_api_updating boolean;
598 l_dummy varchar2(1);
599 --
600 l_org_structure_version_id pqh_budgets.org_structure_version_id%type;
601 l_start_organization_id pqh_budgets.start_organization_id%type;
602 --
603 cursor c1 is
604 select null
605 from hr_all_organization_units a
606 where a.organization_id = p_organization_id;
607 --
608 Cursor c2 is
609 Select start_organization_id,org_structure_version_id
610 From pqh_worksheets wfs , pqh_budgets bdt
611 Where wfs.worksheet_id = p_worksheet_id
612 AND wfs.budget_id = bdt.budget_id;
613 --
614 --
615 Cursor c3 is
616 Select null from dual
617 Where p_organization_id in
618 (Select ORGANIZATION_ID_CHILD
619 from per_org_structure_elements
620 where org_structure_version_id = l_org_structure_version_id);
621 --
622 Begin
623 --
624 hr_utility.set_location('Entering:'||l_proc,5);
625 --
626 l_api_updating := pqh_wdt_shd.api_updating
627 (p_worksheet_detail_id => p_worksheet_detail_id,
628 p_object_version_number => p_object_version_number);
629 --
630 if (l_api_updating
631 and nvl(p_organization_id,hr_api.g_number)
632 <> nvl(pqh_wdt_shd.g_old_rec.organization_id,hr_api.g_number)
633 or not l_api_updating) and
634 p_organization_id is not null then
635 --
636 -- check if organization_id value exists in hr_organization_units table
637 --
638 open c1;
639 --
640 fetch c1 into l_dummy;
641 if c1%notfound then
642 --
643 close c1;
644 --
645 -- raise error as FK does not relate to PK in hr_organization_units
646 -- table.
647 --
648 pqh_wdt_shd.constraint_error('PQH_WORKSHEET_DETAILS_FK2');
649 --
650 end if;
651 --
652 close c1;
653 --
654
655 open c2;
656 Fetch c2 into l_start_organization_id,l_org_structure_version_id ;
657 If c2%notfound then
658 Close c2;
659 hr_utility.set_message(8302,'PQH_WDT_ORG_SRUCTURE_NOT_FOUND');
660 hr_utility.raise_error;
661 End if;
662 Close c2;
663 --
664 If p_organization_id <> l_start_organization_id then
665 --
666 open c3;
667 Fetch c3 into l_dummy;
668 If c3%notfound then
669 Close c3;
670 hr_utility.set_message(8302,'PQH_ORG_NOT_IN_BDT_ORG_STRUCT');
671 hr_utility.raise_error;
672 End if;
673 Close c3;
674 --
675 End if;
676 --
677 --
678 end if;
679 --
680 hr_utility.set_location('Leaving:'||l_proc,10);
681 --
682 End chk_organization_id;
683 --
684 -- ----------------------------------------------------------------------------
685 -- |------< chk_defer_flag >------|
686 -- ----------------------------------------------------------------------------
687 --
688 -- Description
689 -- This procedure is used to check that the lookup value is valid.
690 --
691 -- Pre Conditions
692 -- None.
693 --
694 -- In Parameters
695 -- worksheet_detail_id PK of record being inserted or updated.
696 -- defer_flag Value of lookup code.
697 -- effective_date effective date
698 -- object_version_number Object version number of record being
699 -- inserted or updated.
700 --
701 -- Post Success
702 -- Processing continues
703 --
704 -- Post Failure
705 -- Error handled by procedure
706 --
707 -- Access Status
708 -- Internal table handler use only.
709 --
710 Procedure chk_defer_flag(p_worksheet_detail_id in number,
711 p_defer_flag in varchar2,
712 p_effective_date in date,
713 p_object_version_number in number) is
714 --
715 l_proc varchar2(72) := g_package||'chk_defer_flag';
716 l_api_updating boolean;
717 --
718 Begin
719 --
720 hr_utility.set_location('Entering:'||l_proc, 5);
721 --
722 l_api_updating := pqh_wdt_shd.api_updating
723 (p_worksheet_detail_id => p_worksheet_detail_id,
724 p_object_version_number => p_object_version_number);
725 --
726 if (l_api_updating
727 and p_defer_flag
728 <> nvl(pqh_wdt_shd.g_old_rec.defer_flag,hr_api.g_varchar2)
729 or not l_api_updating)
730 and p_defer_flag is not null then
731 --
732 -- check if value of lookup falls within lookup type.
733 --
734 if hr_api.not_exists_in_hr_lookups
735 (p_lookup_type => 'YES_NO',
736 p_lookup_code => p_defer_flag,
737 p_effective_date => p_effective_date) then
738 --
739 -- raise error as does not exist as lookup
740 --
741 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
742 hr_utility.raise_error;
743 --
744 end if;
745 --
746 end if;
747 --
748 hr_utility.set_location('Leaving:'||l_proc,10);
749 --
750 end chk_defer_flag;
751 --
752 -- ----------------------------------------------------------------------------
753 -- |------< chk_budget_unit3_value_type_cd >------|
754 -- ----------------------------------------------------------------------------
755 --
756 -- Description
757 -- This procedure is used to check that the lookup value is valid.
758 --
759 -- Pre Conditions
760 -- None.
761 --
762 -- In Parameters
763 -- worksheet_detail_id PK of record being inserted or updated.
764 -- budget_unit3_value_type_cd Value of lookup code.
765 -- effective_date effective date
766 -- object_version_number Object version number of record being
767 -- inserted or updated.
768 --
769 -- Post Success
770 -- Processing continues
771 --
772 -- Post Failure
773 -- Error handled by procedure
774 --
775 -- Access Status
776 -- Internal table handler use only.
777 --
778 Procedure chk_budget_unit3_value_type_cd(p_worksheet_detail_id in number,
779 p_budget_unit3_value_type_cd in varchar2,
780 p_effective_date in date,
781 p_object_version_number in number) is
782 --
783 l_proc varchar2(72) := g_package||'chk_budget_unit3_value_type_cd';
784 l_api_updating boolean;
785 --
786 Begin
787 --
788 hr_utility.set_location('Entering:'||l_proc, 5);
789 --
790 l_api_updating := pqh_wdt_shd.api_updating
791 (p_worksheet_detail_id => p_worksheet_detail_id,
792 p_object_version_number => p_object_version_number);
793 --
794 if (l_api_updating
795 and p_budget_unit3_value_type_cd
796 <> nvl(pqh_wdt_shd.g_old_rec.budget_unit3_value_type_cd,hr_api.g_varchar2)
797 or not l_api_updating)
798 and p_budget_unit3_value_type_cd is not null then
799 --
800 -- check if value of lookup falls within lookup type.
801 --
802 if hr_api.not_exists_in_hr_lookups
803 (p_lookup_type => 'PQH_BUDGET_UNIT_VALUE_TYPE',
804 p_lookup_code => p_budget_unit3_value_type_cd,
805 p_effective_date => p_effective_date) then
806 --
807 -- raise error as does not exist as lookup
808 --
809 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
810 hr_utility.raise_error;
811 --
812 end if;
813 --
814 end if;
815 --
816 hr_utility.set_location('Leaving:'||l_proc,10);
817 --
818 end chk_budget_unit3_value_type_cd;
819 --
820 -- ----------------------------------------------------------------------------
821 -- |------< chk_budget_unit2_value_type_cd >------|
822 -- ----------------------------------------------------------------------------
823 --
824 -- Description
825 -- This procedure is used to check that the lookup value is valid.
826 --
827 -- Pre Conditions
828 -- None.
829 --
830 -- In Parameters
831 -- worksheet_detail_id PK of record being inserted or updated.
832 -- budget_unit2_value_type_cd Value of lookup code.
833 -- effective_date effective date
834 -- object_version_number Object version number of record being
835 -- inserted or updated.
836 --
837 -- Post Success
838 -- Processing continues
839 --
840 -- Post Failure
841 -- Error handled by procedure
842 --
843 -- Access Status
844 -- Internal table handler use only.
845 --
846 Procedure chk_budget_unit2_value_type_cd(p_worksheet_detail_id in number,
847 p_budget_unit2_value_type_cd in varchar2,
848 p_effective_date in date,
849 p_object_version_number in number) is
850 --
851 l_proc varchar2(72) := g_package||'chk_budget_unit2_value_type_cd';
852 l_api_updating boolean;
853 --
854 Begin
855 --
856 hr_utility.set_location('Entering:'||l_proc, 5);
857 --
858 l_api_updating := pqh_wdt_shd.api_updating
859 (p_worksheet_detail_id => p_worksheet_detail_id,
860 p_object_version_number => p_object_version_number);
861 --
862 if (l_api_updating
863 and p_budget_unit2_value_type_cd
864 <> nvl(pqh_wdt_shd.g_old_rec.budget_unit2_value_type_cd,hr_api.g_varchar2)
865 or not l_api_updating)
866 and p_budget_unit2_value_type_cd is not null then
867 --
868 -- check if value of lookup falls within lookup type.
869 --
870 if hr_api.not_exists_in_hr_lookups
871 (p_lookup_type => 'PQH_BUDGET_UNIT_VALUE_TYPE',
872 p_lookup_code => p_budget_unit2_value_type_cd,
873 p_effective_date => p_effective_date) then
874 --
875 -- raise error as does not exist as lookup
876 --
877 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
878 hr_utility.raise_error;
879 --
880 end if;
881 --
882 end if;
883 --
884 hr_utility.set_location('Leaving:'||l_proc,10);
885 --
886 end chk_budget_unit2_value_type_cd;
887 --
888 -- ----------------------------------------------------------------------------
889 -- |------< chk_budget_unit1_value_type_cd >------|
890 -- ----------------------------------------------------------------------------
891 --
892 -- Description
893 -- This procedure is used to check that the lookup value is valid.
894 --
895 -- Pre Conditions
896 -- None.
897 --
898 -- In Parameters
899 -- worksheet_detail_id PK of record being inserted or updated.
900 -- budget_unit1_value_type_cd Value of lookup code.
901 -- effective_date effective date
902 -- object_version_number Object version number of record being
903 -- inserted or updated.
904 --
905 -- Post Success
906 -- Processing continues
907 --
908 -- Post Failure
909 -- Error handled by procedure
910 --
911 -- Access Status
912 -- Internal table handler use only.
913 --
914 Procedure chk_budget_unit1_value_type_cd(p_worksheet_detail_id in number,
915 p_budget_unit1_value_type_cd in varchar2,
916 p_effective_date in date,
917 p_object_version_number in number) is
918 --
919 l_proc varchar2(72) := g_package||'chk_budget_unit1_value_type_cd';
920 l_api_updating boolean;
921 --
922 Begin
923 --
924 hr_utility.set_location('Entering:'||l_proc, 5);
925 --
926 l_api_updating := pqh_wdt_shd.api_updating
927 (p_worksheet_detail_id => p_worksheet_detail_id,
928 p_object_version_number => p_object_version_number);
929 --
930 if (l_api_updating
931 and p_budget_unit1_value_type_cd
932 <> nvl(pqh_wdt_shd.g_old_rec.budget_unit1_value_type_cd,hr_api.g_varchar2)
933 or not l_api_updating)
934 and p_budget_unit1_value_type_cd is not null then
935 --
936 -- check if value of lookup falls within lookup type.
937 --
938 if hr_api.not_exists_in_hr_lookups
939 (p_lookup_type => 'PQH_BUDGET_UNIT_VALUE_TYPE',
940 p_lookup_code => p_budget_unit1_value_type_cd,
941 p_effective_date => p_effective_date) then
942 --
943 -- raise error as does not exist as lookup
944 --
945 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
946 hr_utility.raise_error;
947 --
948 end if;
949 --
950 end if;
951 --
952 hr_utility.set_location('Leaving:'||l_proc,10);
953 --
954 end chk_budget_unit1_value_type_cd;
955 --
956 -- ----------------------------------------------------------------------------
957 -- |------< chk_action_cd >------|
958 -- ----------------------------------------------------------------------------
959 --
960 -- Description
961 -- This procedure is used to check that the lookup value is valid.
962 --
963 -- Pre Conditions
964 -- None.
965 --
966 -- In Parameters
967 -- worksheet_detail_id PK of record being inserted or updated.
968 -- action_cd Value of lookup code.
969 -- effective_date effective date
970 -- object_version_number Object version number of record being
971 -- inserted or updated.
972 --
973 -- Post Success
974 -- Processing continues
975 --
976 -- Post Failure
977 -- Error handled by procedure
978 --
979 -- Access Status
980 -- Internal table handler use only.
981 --
982 Procedure chk_action_cd(p_worksheet_detail_id in number,
983 p_action_cd in varchar2,
984 p_effective_date in date,
985 p_object_version_number in number) is
986 --
987 l_proc varchar2(72) := g_package||'chk_action_cd';
988 l_api_updating boolean;
989 --
990 Begin
991 --
992 hr_utility.set_location('Entering:'||l_proc, 5);
993 --
994 l_api_updating := pqh_wdt_shd.api_updating
995 (p_worksheet_detail_id => p_worksheet_detail_id,
996 p_object_version_number => p_object_version_number);
997 --
998 if (l_api_updating
999 and p_action_cd
1000 <> nvl(pqh_wdt_shd.g_old_rec.action_cd,hr_api.g_varchar2)
1001 or not l_api_updating)
1002 and p_action_cd is not null then
1003 --
1004 -- check if value of lookup falls within lookup type.
1005 --
1006 if hr_api.not_exists_in_hr_lookups
1007 (p_lookup_type => 'PQH_WORKSHEET_DETAIL_ACTION',
1008 p_lookup_code => p_action_cd,
1009 p_effective_date => p_effective_date) then
1010 --
1011 -- raise error as does not exist as lookup
1012 --
1013 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
1014 hr_utility.raise_error;
1015 --
1016 end if;
1017 --
1018 end if;
1019 --
1020 hr_utility.set_location('Leaving:'||l_proc,10);
1021 --
1022 end chk_action_cd;
1023 --
1024 --
1025 -- ----------------------------------------------------------------------------
1026 -- |------< chk_status >------|
1027 -- ----------------------------------------------------------------------------
1028 --
1029 -- Description
1030 -- This procedure is used to check that the lookup value is valid.
1031 --
1032 -- Pre Conditions
1033 -- None.
1034 --
1035 -- In Parameters
1036 -- worksheet_detail_id PK of record being inserted or updated.
1037 -- status Value of lookup code.
1038 -- effective_date effective date
1039 -- object_version_number Object version number of record being
1040 -- inserted or updated.
1041 --
1042 -- Post Success
1043 -- Processing continues
1044 --
1045 -- Post Failure
1046 -- Error handled by procedure
1047 --
1048 -- Access Status
1049 -- Internal table handler use only.
1050 --
1051 Procedure chk_status(p_worksheet_detail_id in number,
1052 p_status in varchar2,
1053 p_effective_date in date,
1054 p_object_version_number in number) is
1055 --
1056 l_proc varchar2(72) := g_package||'chk_status';
1057 l_api_updating boolean;
1058 --
1059 Begin
1060 --
1061 hr_utility.set_location('Entering:'||l_proc, 5);
1062 --
1063 l_api_updating := pqh_wdt_shd.api_updating
1064 (p_worksheet_detail_id => p_worksheet_detail_id,
1065 p_object_version_number => p_object_version_number);
1066 --
1067 if (l_api_updating
1068 and p_status
1069 <> nvl(pqh_wdt_shd.g_old_rec.status,hr_api.g_varchar2)
1070 or not l_api_updating)
1071 and p_status is not null then
1072 --
1073 -- check if value of lookup falls within lookup type.
1074 --
1075 if hr_api.not_exists_in_hr_lookups
1076 (p_lookup_type => 'PQH_TRANSACTION_STATUS',
1077 p_lookup_code => p_status,
1078 p_effective_date => p_effective_date) then
1079 --
1080 -- raise error as does not exist as lookup
1081 --
1082 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
1083 hr_utility.raise_error;
1084 --
1085 end if;
1086 --
1087 end if;
1088 --
1089 hr_utility.set_location('Leaving:'||l_proc,10);
1090 --
1091 end chk_status;
1092 --
1093 --
1094 -- ----------------------------------------------------------------------------
1095 -- |------< chk_propagation_method >------|
1096 -- ----------------------------------------------------------------------------
1097 --
1098 -- Description
1099 -- This procedure is used to check that the lookup value is valid.
1100 --
1101 -- Pre Conditions
1102 -- None.
1103 --
1104 -- In Parameters
1105 -- worksheet_detail_id PK of record being inserted or updated.
1106 -- propagation_method Value of lookup code.
1107 -- effective_date effective date
1108 -- object_version_number Object version number of record being
1109 -- inserted or updated.
1110 --
1111 -- Post Success
1112 -- Processing continues
1113 --
1114 -- Post Failure
1115 -- Error handled by procedure
1116 --
1117 -- Access Status
1118 -- Internal table handler use only.
1119 --
1120 Procedure chk_propagation_method(p_worksheet_detail_id in number,
1121 p_propagation_method in varchar2,
1122 p_effective_date in date,
1123 p_object_version_number in number) is
1124 --
1125 l_proc varchar2(72) := g_package||'chk_propagation_method';
1126 l_api_updating boolean;
1127 --
1128 Begin
1129 --
1130 hr_utility.set_location('Entering:'||l_proc, 5);
1131 --
1132 l_api_updating := pqh_wdt_shd.api_updating
1133 (p_worksheet_detail_id => p_worksheet_detail_id,
1134 p_object_version_number => p_object_version_number);
1135 --
1136 if (l_api_updating
1137 and p_propagation_method
1138 <> nvl(pqh_wdt_shd.g_old_rec.propagation_method,hr_api.g_varchar2)
1139 or not l_api_updating)
1140 and p_propagation_method is not null then
1141 --
1142 -- check if value of lookup falls within lookup type.
1143 --
1144 if hr_api.not_exists_in_hr_lookups
1145 (p_lookup_type => 'PQH_WORKSHEET_PROPAGATE_METHOD',
1146 p_lookup_code => p_propagation_method,
1147 p_effective_date => p_effective_date) then
1148 --
1149 -- raise error as does not exist as lookup
1150 --
1151 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
1152 hr_utility.raise_error;
1153 --
1154 end if;
1155 --
1156 end if;
1157 --
1158 hr_utility.set_location('Leaving:'||l_proc,10);
1159 --
1160 end chk_propagation_method;
1161 --
1162 --
1163 -- Additional checks
1164 --
1165 -- ----------------------------------------------------------------------------
1166 -- |------< chk_position_id >------|
1167 -- ----------------------------------------------------------------------------
1168 --
1169 -- Description
1170 -- This procedure checks that a referenced foreign key actually exists
1171 -- in the referenced table.
1172 --
1173 -- Pre-Conditions
1174 -- None.
1175 --
1176 -- In Parameters
1177 -- p_worksheet_detail_id PK
1178 -- p_position_id ID of FK column
1179 -- p_object_version_number object version number
1180 --
1181 -- Post Success
1182 -- Processing continues
1183 --
1184 -- Post Failure
1185 -- Error raised.
1186 --
1187 -- Access Status
1188 -- Internal table handler use only.
1189 --
1190 Procedure chk_position_id (p_worksheet_detail_id in number,
1191 p_position_id in number,
1192 p_object_version_number in number) is
1193 --
1194 l_proc varchar2(72) := g_package||'chk_position_id';
1195 l_api_updating boolean;
1196 l_dummy varchar2(1);
1197 --
1198 cursor c1 is
1199 select null
1200 from hr_all_positions_f a
1201 where a.position_id = p_position_id;
1202 --
1203 Begin
1204 --
1205 hr_utility.set_location('Entering:'||l_proc,5);
1206 --
1207 l_api_updating := pqh_wdt_shd.api_updating
1208 (p_worksheet_detail_id => p_worksheet_detail_id,
1209 p_object_version_number => p_object_version_number);
1210 --
1211 if (l_api_updating
1212 and nvl(p_position_id,hr_api.g_number)
1213 <> nvl(pqh_wdt_shd.g_old_rec.position_id,hr_api.g_number)
1214 or not l_api_updating) and
1215 p_position_id is not null then
1216 --
1217 -- check if position_id value exists in hr_positions_f table
1218 --
1219 open c1;
1220 --
1221 fetch c1 into l_dummy;
1222 if c1%notfound then
1223 --
1224 close c1;
1225 --
1226 -- raise error as FK does not relate to PK in hr_positions_f
1227 -- table.
1228 --
1229 hr_utility.set_message(8302,'PQH_INVALID_POSITION_ID');
1230 hr_utility.raise_error;
1231 --
1232 end if;
1233 --
1234 close c1;
1235 --
1236 end if;
1237 --
1238 hr_utility.set_location('Leaving:'||l_proc,10);
1239 --
1240 End chk_position_id;
1241 --
1242 Procedure chk_user_id (p_worksheet_detail_id in number,
1243 p_user_id in number,
1244 p_object_version_number in number) is
1245 --
1246 l_proc varchar2(72) := g_package||'chk_user_id';
1247 l_api_updating boolean;
1248 l_dummy varchar2(1);
1249 --
1250 cursor c1 is
1251 select null
1252 from fnd_user a
1253 where a.user_id = p_user_id;
1254 --
1255 Begin
1256 --
1257 hr_utility.set_location('Entering:'||l_proc,5);
1258 --
1259 l_api_updating := pqh_wdt_shd.api_updating
1260 (p_worksheet_detail_id => p_worksheet_detail_id,
1261 p_object_version_number => p_object_version_number);
1262 --
1263 if (l_api_updating
1264 and nvl(p_user_id,hr_api.g_number)
1265 <> nvl(pqh_wdt_shd.g_old_rec.user_id,hr_api.g_number)
1266 or not l_api_updating) and
1267 p_user_id is not null then
1268 --
1269 -- check if user_id value exists in fnd_user table
1270 --
1271 open c1;
1272 --
1273 fetch c1 into l_dummy;
1274 if c1%notfound then
1275 --
1276 close c1;
1277 --
1278 -- raise error as FK does not relate to PK in hr_positions_f
1279 -- table.
1280 --
1281 hr_utility.set_message(8302,'PQH_INVALID_USER_ID');
1282 hr_utility.raise_error;
1283 --
1284 end if;
1285 --
1286 close c1;
1287 --
1288 end if;
1289 --
1290 hr_utility.set_location('Leaving:'||l_proc,10);
1291 --
1292 End chk_user_id;
1293 --
1294 --
1295 Procedure chk_numeric_values
1296 (p_worksheet_detail_id in number,
1297 p_budget_unit1_percent in number,
1298 p_budget_unit1_value in number,
1299 p_budget_unit2_percent in number,
1300 p_budget_unit2_value in number,
1301 p_budget_unit3_percent in number,
1302 p_budget_unit3_value in number,
1303 p_object_version_number in number) is
1304 --
1305 l_proc varchar2(72) := g_package||'chk_numeric_values';
1306 l_api_updating boolean;
1307 --
1308 --
1309 Begin
1310 --
1311 hr_utility.set_location('Entering:'||l_proc,5);
1312 --
1313 -- Check if all non-numeric values are all +ve / 0
1314 --
1315 l_api_updating := pqh_wdt_shd.api_updating
1316 (p_worksheet_detail_id => p_worksheet_detail_id,
1317 p_object_version_number => p_object_version_number);
1318 --
1319 if (l_api_updating
1320 and nvl(p_budget_unit1_percent,hr_api.g_number)
1321 <> nvl(pqh_wdt_shd.g_old_rec.budget_unit1_percent,hr_api.g_number)
1322 or not l_api_updating) and
1323 p_budget_unit1_percent is not null then
1324 --
1325 -- Raise error if the value is negative
1326 --
1327 If p_budget_unit1_percent < 0 then
1328 hr_utility.set_message(8302,'PQH_INVALID_BDGT_UNIT_PERCENT');
1329 hr_utility.raise_error;
1330 End if;
1331 end if;
1332 --
1333 --
1334 if (l_api_updating
1335 and nvl(p_budget_unit1_value,hr_api.g_number)
1336 <> nvl(pqh_wdt_shd.g_old_rec.budget_unit1_value,hr_api.g_number)
1337 or not l_api_updating) and
1338 p_budget_unit1_value is not null then
1339 --
1340 -- Raise error if the value is negative
1341 --
1342 If p_budget_unit1_value < 0 then
1343 hr_utility.set_message(8302,'PQH_INVALID_BDGT_UNIT_VALUE');
1344 hr_utility.raise_error;
1345 End if;
1346 end if;
1347 --
1348 --
1349 /**
1350 if (l_api_updating
1351 and nvl(p_budget_unit1_available,hr_api.g_number)
1352 <> nvl(pqh_wdt_shd.g_old_rec.budget_unit1_available,hr_api.g_number)
1353 or not l_api_updating) and
1354 p_budget_unit1_available is not null then
1355 --
1356 -- Raise error if the value is negative
1357 --
1358 If p_budget_unit1_available < 0 then
1359 hr_utility.set_message(8302,'PQH_INVALID_UNIT1_CONSUMED');
1360 hr_utility.raise_error;
1361 End if;
1362 end if;
1363 **/
1364 --
1365 --
1366 --
1367 if (l_api_updating
1368 and nvl(p_budget_unit2_percent,hr_api.g_number)
1369 <> nvl(pqh_wdt_shd.g_old_rec.budget_unit2_percent,hr_api.g_number)
1370 or not l_api_updating) and
1371 p_budget_unit2_percent is not null then
1372 --
1373 -- Raise error if the value is negative
1374 --
1375 If p_budget_unit2_percent < 0 then
1376 hr_utility.set_message(8302,'PQH_INVALID_BDGT_UNIT_PERCENT');
1377 hr_utility.raise_error;
1378 End if;
1379 end if;
1380 --
1381 --
1382 if (l_api_updating
1383 and nvl(p_budget_unit2_value,hr_api.g_number)
1384 <> nvl(pqh_wdt_shd.g_old_rec.budget_unit2_value,hr_api.g_number)
1385 or not l_api_updating) and
1386 p_budget_unit2_value is not null then
1387 --
1388 -- Raise error if the value is negative
1389 --
1390 If p_budget_unit2_value < 0 then
1391 hr_utility.set_message(8302,'PQH_INVALID_BDGT_UNIT_VALUE');
1392 hr_utility.raise_error;
1393 End if;
1394 end if;
1395 --
1396 --
1397 /**
1398 if (l_api_updating
1399 and nvl(p_budget_unit2_available,hr_api.g_number)
1400 <> nvl(pqh_wdt_shd.g_old_rec.budget_unit2_available,hr_api.g_number)
1401 or not l_api_updating) and
1402 p_budget_unit2_available is not null then
1403 --
1404 -- Raise error if the value is negative
1405 --
1406 If p_budget_unit2_available < 0 then
1407 hr_utility.set_message(8302,'PQH_INVALID_UNIT2_CONSUMED');
1408 hr_utility.raise_error;
1409 End if;
1410 end if;
1411 **/
1412 --
1413 --
1414 if (l_api_updating
1415 and nvl(p_budget_unit3_percent,hr_api.g_number)
1416 <> nvl(pqh_wdt_shd.g_old_rec.budget_unit3_percent,hr_api.g_number)
1417 or not l_api_updating) and
1418 p_budget_unit3_percent is not null then
1419 --
1420 -- Raise error if the value is negative
1421 --
1422 If p_budget_unit3_percent < 0 then
1423 hr_utility.set_message(8302,'PQH_INVALID_BDGT_UNIT_PERCENT');
1424 hr_utility.raise_error;
1425 End if;
1426 end if;
1427 --
1428 --
1429 if (l_api_updating
1430 and nvl(p_budget_unit3_value,hr_api.g_number)
1431 <> nvl(pqh_wdt_shd.g_old_rec.budget_unit3_value,hr_api.g_number)
1432 or not l_api_updating) and
1433 p_budget_unit3_value is not null then
1434 --
1435 -- Raise error if the value is negative
1436 --
1437 If p_budget_unit3_value < 0 then
1438 hr_utility.set_message(8302,'PQH_INVALID_BDGT_UNIT_VALUE');
1439 hr_utility.raise_error;
1440 End if;
1441 end if;
1442 --
1443 --
1444 /**
1445 if (l_api_updating
1446 and nvl(p_budget_unit3_available,hr_api.g_number)
1447 <> nvl(pqh_wdt_shd.g_old_rec.budget_unit3_available,hr_api.g_number)
1448 or not l_api_updating) and
1449 p_budget_unit3_available is not null then
1450 --
1451 -- Raise error if the value is negative
1452 --
1453 If p_budget_unit3_available < 0 then
1454 hr_utility.set_message(8302,'PQH_INVALID_UNIT3_CONSUMED');
1455 hr_utility.raise_error;
1456 End if;
1457 end if;
1458 **/
1459 --
1460 --
1461 hr_utility.set_location('Leaving:'||l_proc,10);
1462 --
1463 End chk_numeric_values;
1464 --
1465 -- ----------------------------------------------------------------------------
1466 -- |---------------------------< insert_validate >----------------------------|
1467 -- ----------------------------------------------------------------------------
1468 Procedure insert_validate(p_rec in pqh_wdt_shd.g_rec_type
1469 ,p_effective_date in date) is
1470 --
1471 l_proc varchar2(72) := g_package||'insert_validate';
1472 --
1473 Begin
1474 hr_utility.set_location('Entering:'||l_proc, 5);
1475 --
1476 -- Call all supporting business operations
1477 --
1478 chk_worksheet_detail_id
1479 (p_worksheet_detail_id => p_rec.worksheet_detail_id,
1480 p_object_version_number => p_rec.object_version_number);
1481 --
1482 chk_parent_worksheet_detail_id
1483 (p_worksheet_detail_id => p_rec.worksheet_detail_id,
1484 p_parent_worksheet_detail_id => p_rec.parent_worksheet_detail_id,
1485 p_object_version_number => p_rec.object_version_number);
1486 --
1487 chk_worksheet_id
1488 (p_worksheet_detail_id => p_rec.worksheet_detail_id,
1489 p_worksheet_id => p_rec.worksheet_id,
1490 p_object_version_number => p_rec.object_version_number);
1491 --
1492 chk_grade_id
1493 (p_worksheet_detail_id => p_rec.worksheet_detail_id,
1494 p_worksheet_id => p_rec.worksheet_id,
1495 p_grade_id => p_rec.grade_id,
1496 p_object_version_number => p_rec.object_version_number);
1497 --
1498 chk_job_id
1499 (p_worksheet_detail_id => p_rec.worksheet_detail_id,
1500 p_job_id => p_rec.job_id,
1501 p_object_version_number => p_rec.object_version_number);
1502 --
1503 chk_position_transaction_id
1504 (p_worksheet_detail_id => p_rec.worksheet_detail_id,
1505 p_position_transaction_id => p_rec.position_transaction_id,
1506 p_object_version_number => p_rec.object_version_number);
1507 --
1508 chk_budget_detail_id
1509 (p_worksheet_detail_id => p_rec.worksheet_detail_id,
1510 p_budget_detail_id => p_rec.budget_detail_id,
1511 p_object_version_number => p_rec.object_version_number);
1512 --
1513 chk_organization_id
1514 (p_worksheet_detail_id => p_rec.worksheet_detail_id,
1515 p_worksheet_id => p_rec.worksheet_id,
1516 p_organization_id => p_rec.organization_id,
1517 p_object_version_number => p_rec.object_version_number);
1518 --
1519 chk_defer_flag
1520 (p_worksheet_detail_id => p_rec.worksheet_detail_id,
1521 p_defer_flag => p_rec.defer_flag,
1522 p_effective_date => p_effective_date,
1523 p_object_version_number => p_rec.object_version_number);
1524 --
1525 chk_budget_unit3_value_type_cd
1526 (p_worksheet_detail_id => p_rec.worksheet_detail_id,
1527 p_budget_unit3_value_type_cd => p_rec.budget_unit3_value_type_cd,
1528 p_effective_date => p_effective_date,
1529 p_object_version_number => p_rec.object_version_number);
1530 --
1531 chk_budget_unit2_value_type_cd
1532 (p_worksheet_detail_id => p_rec.worksheet_detail_id,
1533 p_budget_unit2_value_type_cd => p_rec.budget_unit2_value_type_cd,
1534 p_effective_date => p_effective_date,
1535 p_object_version_number => p_rec.object_version_number);
1536 --
1537 chk_budget_unit1_value_type_cd
1538 (p_worksheet_detail_id => p_rec.worksheet_detail_id,
1539 p_budget_unit1_value_type_cd => p_rec.budget_unit1_value_type_cd,
1540 p_effective_date => p_effective_date,
1541 p_object_version_number => p_rec.object_version_number);
1542 --
1543 chk_action_cd
1544 (p_worksheet_detail_id => p_rec.worksheet_detail_id,
1545 p_action_cd => p_rec.action_cd,
1546 p_effective_date => p_effective_date,
1547 p_object_version_number => p_rec.object_version_number);
1548 --
1549 chk_status
1550 (p_worksheet_detail_id => p_rec.worksheet_detail_id,
1551 p_status => p_rec.status,
1552 p_effective_date => p_effective_date,
1553 p_object_version_number => p_rec.object_version_number);
1554 --
1555 chk_propagation_method
1556 (p_worksheet_detail_id => p_rec.worksheet_detail_id,
1557 p_propagation_method => p_rec.propagation_method,
1558 p_effective_date => p_effective_date,
1559 p_object_version_number => p_rec.object_version_number);
1560 --
1561 --
1562 chk_position_id
1563 (p_worksheet_detail_id => p_rec.worksheet_detail_id,
1564 p_position_id => p_rec.position_id,
1565 p_object_version_number => p_rec.object_version_number);
1566 --
1567 chk_user_id
1568 (p_worksheet_detail_id => p_rec.worksheet_detail_id,
1569 p_user_id => p_rec.user_id,
1570 p_object_version_number => p_rec.object_version_number);
1571 --
1572 chk_numeric_values
1573 (p_worksheet_detail_id => p_rec.worksheet_detail_id,
1574 p_budget_unit1_percent => p_rec.budget_unit1_percent,
1575 p_budget_unit1_value => p_rec.budget_unit1_value,
1576 p_budget_unit2_percent => p_rec.budget_unit2_percent,
1577 p_budget_unit2_value => p_rec.budget_unit2_value,
1578 p_budget_unit3_percent => p_rec.budget_unit3_percent,
1579 p_budget_unit3_value => p_rec.budget_unit3_value,
1580 p_object_version_number => p_rec.object_version_number);
1581 --
1582 hr_utility.set_location(' Leaving:'||l_proc, 10);
1583 End insert_validate;
1584 --
1585 -- ----------------------------------------------------------------------------
1586 -- |---------------------------< update_validate >----------------------------|
1587 -- ----------------------------------------------------------------------------
1588 Procedure update_validate(p_rec in pqh_wdt_shd.g_rec_type
1589 ,p_effective_date in date) is
1590 --
1591 l_proc varchar2(72) := g_package||'update_validate';
1592 --
1593 Begin
1594 hr_utility.set_location('Entering:'||l_proc, 5);
1595 --
1596 -- Call all supporting business operations
1597 --
1598 chk_worksheet_detail_id
1599 (p_worksheet_detail_id => p_rec.worksheet_detail_id,
1600 p_object_version_number => p_rec.object_version_number);
1601 --
1602 chk_parent_worksheet_detail_id
1603 (p_worksheet_detail_id => p_rec.worksheet_detail_id,
1604 p_parent_worksheet_detail_id => p_rec.parent_worksheet_detail_id,
1605 p_object_version_number => p_rec.object_version_number);
1606 --
1607 chk_worksheet_id
1608 (p_worksheet_detail_id => p_rec.worksheet_detail_id,
1609 p_worksheet_id => p_rec.worksheet_id,
1610 p_object_version_number => p_rec.object_version_number);
1611 --
1612 chk_grade_id
1613 (p_worksheet_detail_id => p_rec.worksheet_detail_id,
1614 p_worksheet_id => p_rec.worksheet_id,
1615 p_grade_id => p_rec.grade_id,
1616 p_object_version_number => p_rec.object_version_number);
1617 --
1618 chk_job_id
1619 (p_worksheet_detail_id => p_rec.worksheet_detail_id,
1620 p_job_id => p_rec.job_id,
1621 p_object_version_number => p_rec.object_version_number);
1622 --
1623 chk_position_transaction_id
1624 (p_worksheet_detail_id => p_rec.worksheet_detail_id,
1625 p_position_transaction_id => p_rec.position_transaction_id,
1626 p_object_version_number => p_rec.object_version_number);
1627 --
1628 chk_budget_detail_id
1629 (p_worksheet_detail_id => p_rec.worksheet_detail_id,
1630 p_budget_detail_id => p_rec.budget_detail_id,
1631 p_object_version_number => p_rec.object_version_number);
1632 --
1633 chk_organization_id
1634 (p_worksheet_detail_id => p_rec.worksheet_detail_id,
1635 p_worksheet_id => p_rec.worksheet_id,
1636 p_organization_id => p_rec.organization_id,
1637 p_object_version_number => p_rec.object_version_number);
1638 --
1639 chk_defer_flag
1640 (p_worksheet_detail_id => p_rec.worksheet_detail_id,
1641 p_defer_flag => p_rec.defer_flag,
1642 p_effective_date => p_effective_date,
1643 p_object_version_number => p_rec.object_version_number);
1644 --
1645 chk_budget_unit3_value_type_cd
1646 (p_worksheet_detail_id => p_rec.worksheet_detail_id,
1647 p_budget_unit3_value_type_cd => p_rec.budget_unit3_value_type_cd,
1648 p_effective_date => p_effective_date,
1649 p_object_version_number => p_rec.object_version_number);
1650 --
1651 chk_budget_unit2_value_type_cd
1652 (p_worksheet_detail_id => p_rec.worksheet_detail_id,
1653 p_budget_unit2_value_type_cd => p_rec.budget_unit2_value_type_cd,
1654 p_effective_date => p_effective_date,
1655 p_object_version_number => p_rec.object_version_number);
1656 --
1657 chk_budget_unit1_value_type_cd
1658 (p_worksheet_detail_id => p_rec.worksheet_detail_id,
1659 p_budget_unit1_value_type_cd => p_rec.budget_unit1_value_type_cd,
1660 p_effective_date => p_effective_date,
1661 p_object_version_number => p_rec.object_version_number);
1662 --
1663 chk_action_cd
1664 (p_worksheet_detail_id => p_rec.worksheet_detail_id,
1665 p_action_cd => p_rec.action_cd,
1666 p_effective_date => p_effective_date,
1667 p_object_version_number => p_rec.object_version_number);
1668 --
1669 chk_status
1670 (p_worksheet_detail_id => p_rec.worksheet_detail_id,
1671 p_status => p_rec.status,
1672 p_effective_date => p_effective_date,
1673 p_object_version_number => p_rec.object_version_number);
1674 --
1675 chk_propagation_method
1676 (p_worksheet_detail_id => p_rec.worksheet_detail_id,
1677 p_propagation_method => p_rec.propagation_method,
1678 p_effective_date => p_effective_date,
1679 p_object_version_number => p_rec.object_version_number);
1680 --
1681 --
1682 chk_position_id
1683 (p_worksheet_detail_id => p_rec.worksheet_detail_id,
1684 p_position_id => p_rec.position_id,
1685 p_object_version_number => p_rec.object_version_number);
1686 --
1687 chk_user_id
1688 (p_worksheet_detail_id => p_rec.worksheet_detail_id,
1689 p_user_id => p_rec.user_id,
1690 p_object_version_number => p_rec.object_version_number);
1691 --
1692 chk_numeric_values
1693 (p_worksheet_detail_id => p_rec.worksheet_detail_id,
1694 p_budget_unit1_percent => p_rec.budget_unit1_percent,
1695 p_budget_unit1_value => p_rec.budget_unit1_value,
1696 p_budget_unit2_percent => p_rec.budget_unit2_percent,
1697 p_budget_unit2_value => p_rec.budget_unit2_value,
1698 p_budget_unit3_percent => p_rec.budget_unit3_percent,
1699 p_budget_unit3_value => p_rec.budget_unit3_value,
1700 p_object_version_number => p_rec.object_version_number);
1701 --
1702 --
1703 --
1704 hr_utility.set_location(' Leaving:'||l_proc, 10);
1705 End update_validate;
1706 --
1707 -- ----------------------------------------------------------------------------
1708 -- |---------------------------< delete_validate >----------------------------|
1709 -- ----------------------------------------------------------------------------
1710 Procedure delete_validate(p_rec in pqh_wdt_shd.g_rec_type
1711 ,p_effective_date in date) is
1712 --
1713 l_proc varchar2(72) := g_package||'delete_validate';
1714 --
1715 Begin
1716 hr_utility.set_location('Entering:'||l_proc, 5);
1717 --
1718 -- Call all supporting business operations
1719 --
1720 hr_utility.set_location(' Leaving:'||l_proc, 10);
1721 End delete_validate;
1722 --
1723 end pqh_wdt_bus;