1 package body hr_scl_flex as
2 /* $Header: hrsclfli.pkb 115.7 1999/12/07 02:45:12 pkm ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' hr_scl_flex.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |---------------------------< chk_us_segment1 >----------------------------|
12 -- ----------------------------------------------------------------------------
13 -- {Start of Comments}
14 --
15 -- Description:
16 -- Validates the segment1 attribute for US legislation as follows :
17 -- A) Segment1 must exist as a tax_unit_id in the view HR_TAX_UNITS_V for the
18 -- business gruop id.
19 --
20 -- Pre-Conditions
21 -- None
22 --
23 -- In Arguments:
24 -- p_segment1 -->
25 --
26 -- Post Success:
27 -- If the p_segment1 is valid then processing continues.
28 --
29 -- Post Failure:
30 -- If any of the above rules are violated then an application error will be
31 -- raised.
32 --
33 -- Access Status:
34 -- Internal Development Use Only.
35 --
36 -- {End of Comments}
37 -- ----------------------------------------------------------------------------
38 procedure chk_us_segment1
39 (p_segment1 in varchar2
40 ,p_business_group_id in number) is
41 --
42 l_exists varchar2(1);
43 l_proc varchar2(72) := g_package||'chk_us_segment1';
44 l_business_group_id number;
45 --
46 cursor csr_chk_tu is
47 select htu.business_group_id
48 from HR_TAX_UNITS_V htu
49 where htu.tax_unit_id = p_segment1;
50 --
51 begin
52 hr_utility.set_location('Entering: '||l_proc, 1);
53 --
54 -- Check that segment1, if it is not null, is linked to a valid tax unit
55 --
56 if p_segment1 is not null then
57 --
58 -- Check mandatory parameters have been set
59 --
60 hr_api.mandatory_arg_error
61 (p_api_name => l_proc
62 ,p_argument => 'business_group_id'
63 ,p_argument_value => p_business_group_id
64 );
65 --
66 open csr_chk_tu;
67 fetch csr_chk_tu into l_business_group_id;
68 if csr_chk_tu%notfound then
69 close csr_chk_tu;
70 hr_utility.set_message(800,'HR_50002_SCL_US_GOV_REPORTING');
71 hr_utility.raise_error;
72 end if;
73 close csr_chk_tu;
74 hr_utility.set_location(l_proc, 10);
75 --
76 if l_business_group_id <> p_business_group_id then
77 hr_utility.set_message(800,'HR_50003_SCL_US_GOV_BUS_GRP');
78 hr_utility.raise_error;
79 end if;
80 end if;
81 --
82 hr_utility.set_location(' Leaving:'||l_proc, 15);
83 --
84 end chk_us_segment1;
85 --
86 -- ----------------------------------------------------------------------------
87 -- |---------------------------< chk_us_segment2 >----------------------------|
88 -- ----------------------------------------------------------------------------
89 -- {Start of Comments}
90 --
91 -- Description:
92 -- Validates the segment2 attribute for US legislation as follows :
93 -- a) Segment2 must exist as a valid person_id for a current employee in the
94 -- business group
95 --
96 -- Pre-Conditions
97 -- None
98 --
99 -- In Arguments:
100 -- p_segment2 -->
101 --
102 -- Post Success:
103 -- If the p_segment2 is valid then processing continues.
104 --
105 -- Post Failure:
106 -- If any of the above rules are violated then an application error will be
107 -- raised.
108 --
109 -- Access Status:
110 -- Internal Development Use Only.
111 --
112 -- {End of Comments}
113 -- ----------------------------------------------------------------------------
114 procedure chk_us_segment2
115 --
116 (p_segment2 in varchar2
117 ,p_business_group_id in number
118 ,p_validation_start_date in date
119 ,p_validation_end_date in date) IS
120 --
121 l_exists varchar2(1);
122 l_proc varchar2(72) := g_package||'chk_us_segment2';
123 l_business_group_id number;
124 --
125 -- Need to change this to standard datetrack validation or to use
126 -- p_effective_date when decision has been made re. correct validation
127 --
128 cursor csr_chk_emp is
129 select peo.business_group_id
130 from per_people_f peo
131 , per_assignments_f asg
132 where peo.person_id = asg.person_id
133 and asg.assignment_type = 'E'
134 and peo.person_id = p_segment2
135 and p_validation_start_date between peo.effective_start_date
136 and peo.effective_end_date
137 and p_validation_start_date between asg.effective_start_date
138 and asg.effective_end_date;
139 --
140 begin
141 hr_utility.set_location('Entering: '||l_proc, 1);
142 --
143 If p_segment2 is not null then
144 --
145 -- Check mandatory parameters have been set
146 --
147 hr_api.mandatory_arg_error
148 (p_api_name => l_proc
149 ,p_argument => 'business_group_id'
150 ,p_argument_value => p_business_group_id
151 );
152 --
153 hr_api.mandatory_arg_error
154 (p_api_name => l_proc
155 ,p_argument => 'validation_start_date'
156 ,p_argument_value => p_validation_start_date
157 );
158 --
159 hr_api.mandatory_arg_error
160 (p_api_name => l_proc
161 ,p_argument => 'validation_end_date'
162 ,p_argument_value => p_validation_end_date
163 );
164 --
165 open csr_chk_emp;
166 fetch csr_chk_emp into l_business_group_id;
167 if csr_chk_emp%notfound then
168 close csr_chk_emp;
169 hr_utility.set_message(800,'HR_50004_SCL_US_TIME_EMP');
170 hr_utility.raise_error;
171 end if;
172 close csr_chk_emp;
173 hr_utility.set_location(l_proc, 10);
174 --
175 if l_business_group_id <> p_business_group_id then
176 hr_utility.set_message(800,'HR_50005_SCL_US_TIME_BUS_GRP');
177 hr_utility.raise_error;
178 end if;
179 end if;
180 --
181 hr_utility.set_location(' Leaving:'||l_proc, 15);
182 --
183 end chk_us_segment2;
184 --
185 -- ----------------------------------------------------------------------------
186 -- |---------------------------< chk_us_segment3 >----------------------------|
187 -- ----------------------------------------------------------------------------
188 -- {Start of Comments}
189 --
190 -- Description:
191 -- Validates the segment3 attribute for US legislation as follows :
192 -- a) Segment3 must exist as a lookup_code in the table FND_COMMON_LOOKUPS
193 -- for the lookup_type = 'YES_NO'
194 --
195 -- Pre-Conditions
196 -- None
197 --
198 -- In Arguments:
199 -- p_segment3 -->
200 --
201 -- Post Success:
202 -- If the p_segment3 is valid then processing continues.
203 --
204 -- Post Failure:
205 -- If any of the above rules are violated then an application error will be
206 -- raised.
207 --
208 -- Access Status:
209 -- Internal Development Use Only.
210 --
211 -- {End of Comments}
212 -- ----------------------------------------------------------------------------
213 procedure chk_us_segment3
214 (p_segment3 in varchar2 ) is
215 --
216 l_exists varchar2(1);
217 l_proc varchar2(72) := g_package||'chk_us_segment3';
218 l_business_group_id number;
219 --
220 cursor csr_chk_yes_no is
221 select null
222 from FND_COMMON_LOOKUPS lu
223 where lu.lookup_type = 'YES_NO'
224 and lu.lookup_code = p_segment3;
225 --
226 begin
227 hr_utility.set_location('Entering: '||l_proc, 1);
228 --
229 If p_segment3 is not null then
230 --
231 open csr_chk_yes_no;
232 fetch csr_chk_yes_no into l_exists;
233 if csr_chk_yes_no%notfound then
234 close csr_chk_yes_no;
235 hr_utility.set_message(800,'HR_50006_SCL_US_TIME_REQD');
236 hr_utility.raise_error;
237 end if;
238 close csr_chk_yes_no;
239 --
240 end if;
241 hr_utility.set_location(' Leaving:'||l_proc, 15);
242 --
243 end chk_us_segment3;
244 --
245 -- ----------------------------------------------------------------------------
246 -- |---------------------------< chk_us_segment4 >----------------------------|
247 -- ----------------------------------------------------------------------------
248 -- {Start of Comments}
249 --
250 -- Description:
251 -- Validates the segment4 attribute for US legislation as follows :
252 -- a) Segment4 must exist as a user_column_id for the work schedule table set
253 -- up for the assignment's organization.
254 --
255 -- Pre-Conditions
256 -- None
257 --
258 -- In Arguments:
259 -- p_segment4 -->
260 --
261 -- Post Success:
262 -- If the p_segment4 is valid then processing continues.
263 --
264 -- Post Failure:
265 -- If any of the above rules are violated then an application error will be
266 -- raised.
267 --
268 -- Access Status:
269 -- Internal Development Use Only.
270 --
271 -- {End of Comments}
272 -- ----------------------------------------------------------------------------
273 procedure chk_us_segment4
274 (p_segment4 in varchar2
275 ,p_organization_id number) is
276 --
277 l_exists varchar2(1);
278 l_proc varchar2(72) := g_package||'chk_us_segment4';
279 l_organization_id number;
280 --
281 -- Must convert user_table_id to character, rather than org_information1 to number
282 -- as under CBO the execution plan is not guaranteed and org_information1 may contain
283 -- non-numeric data. May convert p_segment4 to number as it should be numeric, and
284 -- then the index on user_column_id is available.
285 --
286 cursor csr_chk_work_schedule is
287 select null
288 from pay_user_columns puc
289 ,hr_organization_information hoi
290 where ( to_char(puc.user_table_id) = hoi.org_information1
291 or hoi.org_information1 is null)
292 and puc.user_column_id = to_number(p_segment4)
293 and hoi.org_information_context = 'Work Schedule'
294 and hoi.organization_id = p_organization_id;
295 --
296 begin
297 hr_utility.set_location('Entering: '||l_proc, 1);
298 --
299 hr_utility.set_location(l_proc, 5);
300 --
301 If p_segment4 is not null then
302 --
303 -- Check mandatory parameters have been set
304 --
305 hr_api.mandatory_arg_error
306 (p_api_name => l_proc
307 ,p_argument => 'organization_id'
308 ,p_argument_value => p_organization_id
309 );
310 --
311 open csr_chk_work_schedule;
312 fetch csr_chk_work_schedule into l_exists;
313 if csr_chk_work_schedule%notfound then
314 close csr_chk_work_schedule;
315 hr_utility.set_message(800,'HR_50007_SCL_US_INV_SCHEDULE');
316 hr_utility.raise_error;
317 end if;
318 close csr_chk_work_schedule;
319 --
320 end if;
321 hr_utility.set_location(' Leaving:'||l_proc, 15);
322 --
323 end chk_us_segment4;
324 --
325 -- ----------------------------------------------------------------------------
326 -- |---------------------------< chk_us_segment5 >----------------------------|
327 -- ----------------------------------------------------------------------------
328 -- {Start of Comments}
329 --
330 -- Description:
331 -- Validates the segment5 attribute for US legislation as follows :
332 -- a) Segment5 must exist as a lookup_code in the table FND_COMMON_LOOKUPS
333 -- for the lookup_type = 'US_SHIFTS'
334 --
335 -- Pre-Conditions
336 -- None
337 --
338 -- In Arguments:
339 -- p_segment5 -->
340 --
341 -- Post Success:
342 -- If the p_segment5 is valid then processing continues.
343 --
344 -- Post Failure:
345 -- If any of the above rules are violated then an application error will be
346 -- raised.
347 --
348 -- Access Status:
349 -- Internal Development Use Only.
350 --
351 -- {End of Comments}
352 -- ----------------------------------------------------------------------------
353 procedure chk_us_segment5
354 (p_segment5 in varchar2 ) is
355 --
356 l_exists varchar2(1);
357 l_proc varchar2(72) := g_package||'chk_us_segment5';
358 --
359 cursor csr_chk_shift is
360 select null
361 from FND_COMMON_LOOKUPS lu
362 where lu.lookup_type = 'US_SHIFTS'
363 and lu.lookup_code = p_segment5
364 and lu.application_id = 800;
365 --
366 begin
367 hr_utility.set_location('Entering: '||l_proc, 1);
368 --
369 If p_segment5 is not null then
370 --
371 open csr_chk_shift;
372 fetch csr_chk_shift into l_exists;
373 if csr_chk_shift%notfound then
374 close csr_chk_shift;
375 hr_utility.set_message(800,'PAY_7706_PDT_SHIFT_NOT_FOUND');
376 hr_utility.raise_error;
377 end if;
378 close csr_chk_shift;
379 --
380 end if;
381 hr_utility.set_location(' Leaving:'||l_proc, 15);
382 --
383 end chk_us_segment5;
384 --
385 -- ----------------------------------------------------------------------------
386 -- |---------------------------< chk_us_segment6 >----------------------------|
387 -- ----------------------------------------------------------------------------
388 -- {Start of Comments}
389 --
390 -- Description:
391 -- Validates the segment6 attribute for US legislation as follows :
392 -- a)
393 --
394 -- Pre-Conditions
395 -- None
396 --
397 -- In Arguments:
398 -- p_segment6 -->
399 --
400 -- Post Success:
401 -- If the p_segment6 is valid then processing continues.
402 --
403 -- Post Failure:
404 -- If any of the above rules are violated then an application error will be
405 -- raised.
406 --
407 -- Access Status:
408 -- Internal Development Use Only.
409 --
410 -- {End of Comments}
411 -- ----------------------------------------------------------------------------
412 procedure chk_us_segment6
413 (p_segment6 in varchar2) is
414 --
415 l_exists varchar2(1);
416 l_proc varchar2(72) := g_package||'chk_us_segment6';
417 l_business_group_id number(15);
418 l_segment6 varchar2(60);
419 --
420 begin
421 hr_utility.set_location('Entering: '||l_proc, 1);
422 --
423 If p_segment6 is not null then
424 --
425 -- Ensure that the length is 11
426 --
427 if (length(p_segment6) > 11) then
428 hr_utility.set_message(800,'HR_50008_SCL_US_SPOUSE_SALARY');
429 hr_utility.raise_error;
430 end if;
431 --
432 hr_utility.set_location(l_proc, 5);
433 --
434 -- Ensure that p_segment6 is in numberic format with a precision of 2
435 --
436 l_segment6 := p_segment6;
437 hr_dbchkfmt.is_db_format
438 (p_value => l_segment6,
439 p_arg_name => 'segment6',
440 p_format => 'H_DECIMAL2');
441 --
442 hr_utility.set_location(l_proc, 10);
443 --
444 -- Ensure that p_segment6 is betwween 0 and 10000000.00
445 --
446 if (to_number(p_segment6) < 0) or (to_number(p_segment6) > 10000000.00) then
447 hr_utility.set_message(800,'HR_50008_SCL_US_SPOUSE_SALARY');
448 hr_utility.raise_error;
449 end if;
450 --
451 end if;
452 hr_utility.set_location(' Leaving:'||l_proc, 15);
453 --
454 end chk_us_segment6;
455 --
456 -- ----------------------------------------------------------------------------
457 -- |---------------------------< chk_us_segment7 >----------------------------|
458 -- ----------------------------------------------------------------------------
459 -- {Start of Comments}
460 --
461 -- Description:
462 -- Validates the segment7 attribute for US legislation as follows :
463 -- a) Segment7 must exist as a lookup_code in the table FND_COMMON_LOOKUPS
464 -- for the lookup_type = 'YES_NO'
465 --
466 -- Pre-Conditions
467 -- None
468 --
469 -- In Arguments:
470 -- p_segment7 -->
471 --
472 -- Post Success:
473 -- If the p_segment7 is valid then processing continues.
474 --
475 -- Post Failure:
476 -- If any of the above rules are violated then an application error will be
477 -- raised.
478 --
479 -- Access Status:
480 -- Internal Development Use Only.
481 --
482 -- {End of Comments}
483 -- ----------------------------------------------------------------------------
484 procedure chk_us_segment7
485 (p_segment7 in varchar2) is
486 --
487 l_exists varchar2(1);
488 l_proc varchar2(72) := g_package||'chk_us_segment7';
489 l_business_group_id number;
490 --
491 cursor csr_chk_yes_no is
492 select null
493 from FND_COMMON_LOOKUPS lu
494 where lu.lookup_type = 'YES_NO'
495 and lu.lookup_code = p_segment7;
496 --
497 begin
498 hr_utility.set_location('Entering: '||l_proc, 1);
499 --
500 If p_segment7 is not null then
501 --
502 open csr_chk_yes_no;
503 fetch csr_chk_yes_no into l_exists;
504 if csr_chk_yes_no%notfound then
505 close csr_chk_yes_no;
506 hr_utility.set_message(800,'HR_50009_SCL_US_LEGAL_REP');
507 hr_utility.raise_error;
508 end if;
509 close csr_chk_yes_no;
510 --
511 end if;
512 hr_utility.set_location(' Leaving:'||l_proc, 15);
513 --
514 end chk_us_segment7;
515 --
516 -- ----------------------------------------------------------------------------
517 -- |---------------------------< chk_us_segment8 >----------------------------|
518 -- ----------------------------------------------------------------------------
519 -- {Start of Comments}
520 --
521 -- Description:
522 -- Validates the segment8 attribute for US legislation as follows :
523 -- a) Segment8 must exist as a wc code
524 --
525 -- Pre-Conditions
526 -- None
527 --
528 -- In Arguments:
529 -- p_segment4 -->
530 --
531 -- Post Success:
532 -- If the p_segment8 is valid then processing continues.
533 --
534 -- Post Failure:
535 -- If any of the above rules are violated then an application error will be
536 -- raised.
537 --
538 -- Access Status:
539 -- Internal Development Use Only.
540 --
541 -- {End of Comments}
542 -- ----------------------------------------------------------------------------
543 procedure chk_us_segment8
544 (p_segment8 in varchar2
545 ,p_segment1 in varchar2
546 ,p_location_id number
547 ,p_assignment_id number) is
548 --
549 l_exists varchar2(1);
550 l_proc varchar2(72) := g_package||'chk_us_segment8';
551 l_assignment_id number;
552 l_location_id number;
553 --
554 cursor csr_chk_wc_override is
555 Select null
556 From PAY_WC_RATES wcr,
557 PAY_WC_FUNDS wcf,
558 HR_ORGANIZATION_INFORMATION org,
559 PAY_EMP_FED_TAX_V1 ftr
560 where wcr.fund_id = wcf.fund_id
561 and
562 (
563 ( wcf.location_id is null
564 and not exists
565 (
566 select 1
567 from pay_wc_rates wcr1,
568 pay_wc_funds wcf1
569 where wcr1.wc_code = wcr.wc_code
570 and wcr1.fund_id = wcf1.fund_id
571 and wcf1.location_id = p_location_id
572 and wcf1.state_code = ftr.sui_state_code
573 and wcf1.carrier_id = org.org_information8 )
574 )
575 OR
576 ( wcf.location_id is not null
577 AND wcf.location_id = p_location_id
578 )
579 )
580 AND wcf.carrier_id = org.org_information8
581 AND org.org_information1 = ftr.sui_state_code
582 AND org.org_information_context = 'State Tax Rules'
583 AND org.organization_id = p_segment1
584 AND wcf.state_code = ftr.sui_state_code
585 AND ftr.assignment_id = p_assignment_id
586 AND wcr.wc_code = p_segment8;
587 --
588 begin
589 hr_utility.set_location('Entering: '||l_proc, 1);
590 --
591 If p_segment8 is not null then
592 --
593 -- Check mandatory parameters have been set
594 --
595 hr_api.mandatory_arg_error
596 (p_api_name => l_proc
597 ,p_argument => 'assignment_id'
598 ,p_argument_value => p_assignment_id
599 );
600 --
601 hr_api.mandatory_arg_error
602 (p_api_name => l_proc
603 ,p_argument => 'segment1'
604 ,p_argument_value => p_segment1
605 );
606 --
607 open csr_chk_wc_override;
608 fetch csr_chk_wc_override into l_exists;
609 if csr_chk_wc_override%notfound then
610 close csr_chk_wc_override;
611 hr_utility.set_message(800,'HR_50010_SCL_US_WORKERS_COMP');
612 hr_utility.raise_error;
613 end if;
614 close csr_chk_wc_override;
615 --
616 end if;
617 hr_utility.set_location(' Leaving:'||l_proc, 15);
618 --
619 end chk_us_segment8;
620 --
621 -- ----------------------------------------------------------------------------
622 -- |---------------------------< chk_us_segment9 >----------------------------|
623 -- ----------------------------------------------------------------------------
624 -- {Start of Comments}
625 --
626 -- Description:
627 -- Validates the segment9 attribute for US legislation as follows :
628 -- a) Segment9 must exist as a establishment_id in the view HR_ESTABLISHMENTs
629 -- for the business gruop id.
630 --
631 -- Pre-Conditions
632 -- None
633 --
634 -- In Arguments:
635 -- p_segment9 -->
636 --
637 -- Post Success:
638 -- If the p_segment9 is valid then processing continues.
639 --
640 -- Post Failure:
641 -- If any of the above rules are violated then an application error will be
642 -- raised.
643 --
644 -- Access Status:
645 -- Internal Development Use Only.
646 --
647 -- {End of Comments}
648 -- ----------------------------------------------------------------------------
649 procedure chk_us_segment9
650 (p_segment9 in varchar2
651 ,p_business_group_id in number) is
652 --
653 l_exists varchar2(1);
654 l_proc varchar2(72) := g_package||'chk_us_segment9';
655 l_business_group_id number;
656 --
657 cursor csr_chk_est is
658 select est.business_group_id
659 from HR_ESTABLISHMENTS_V est
660 where est.establishment_id = p_segment9;
661 --
662 begin
663 hr_utility.set_location('Entering: '||l_proc, 1);
664 --
665 -- Check that segment9, if it is not null, is linked to a valid establishment
666 --
667 if p_segment9 is not null then
668 --
669 -- Check mandatory parameters have been set
670 --
671 hr_api.mandatory_arg_error
672 (p_api_name => l_proc
673 ,p_argument => 'business_group_id'
674 ,p_argument_value => p_business_group_id
675 );
676 --
677 open csr_chk_est;
678 fetch csr_chk_est into l_business_group_id;
679 if csr_chk_est%notfound then
680 close csr_chk_est;
681 hr_utility.set_message(800,'HR_50011_SCL_US_INV_REP_EST');
682 hr_utility.raise_error;
683 end if;
684 close csr_chk_est;
685 hr_utility.set_location(l_proc, 10);
686 --
687 if l_business_group_id <> p_business_group_id then
688 hr_utility.set_message(800,'HR_50012_SCL_US_INV_REP_BUS');
689 hr_utility.raise_error;
690 end if;
691 end if;
692 --
693 hr_utility.set_location(' Leaving:'||l_proc, 15);
694 --
695 end chk_us_segment9;
696 --
697 -- ----------------------------------------------------------------------------
698 -- |---------------------------< chk_us_segment10_30 >-------------------------|
699 -- ----------------------------------------------------------------------------
700 -- {Start of Comments}
701 --
702 -- Description:
703 -- Validates the segments 10..30 for US legislation
704 --
705 -- Pre-Conditions
706 -- None
707 --
708 -- In Arguments:
709 -- p_segment10..p_segment30
710 --
711 -- Post Success:
712 -- If p_segment10 to p_segment30 are NULL then processing continues.
713 --
714 -- Post Failure:
715 -- If any of p_segment10 to p_segment30 are NOT NULL then an application
716 -- error will be raised.
717 --
718 -- Access Status:
719 -- Internal Development Use Only.
720 --
721 -- {End of Comments}
722 -- ----------------------------------------------------------------------------
723 procedure chk_us_segment10_30
724 (p_segment10 in varchar2
725 ,p_segment11 in varchar2
726 ,p_segment12 in varchar2
727 ,p_segment13 in varchar2
728 ,p_segment14 in varchar2
729 ,p_segment15 in varchar2
730 ,p_segment16 in varchar2
731 ,p_segment17 in varchar2
732 ,p_segment18 in varchar2
733 ,p_segment19 in varchar2
734 ,p_segment20 in varchar2
735 ,p_segment21 in varchar2
736 ,p_segment22 in varchar2
737 ,p_segment23 in varchar2
738 ,p_segment24 in varchar2
739 ,p_segment25 in varchar2
740 ,p_segment26 in varchar2
741 ,p_segment27 in varchar2
742 ,p_segment28 in varchar2
743 ,p_segment29 in varchar2
744 ,p_segment30 in varchar2
745 ) is
746 --
747 l_proc varchar2(72) := g_package||'chk_us_segment10_30';
748 l_err exception; -- local error exception
749 l_n number; -- arg in error number (10..30)
750 l_v varchar2(60); -- arg in error value (10..30)
751 --
752 begin
753 hr_utility.set_location('Entering: '||l_proc, 1);
754 --
755 if p_segment10 is not null then
756 l_n := 10;
757 l_v := p_segment10;
758 raise l_err;
759 end if;
760 if p_segment11 is not null then
761 l_n := 11;
762 l_v := p_segment11;
763 raise l_err;
764 end if;
765 if p_segment12 is not null then
766 l_n := 12;
767 l_v := p_segment12;
768 raise l_err;
769 end if;
770 if p_segment13 is not null then
771 l_n := 13;
772 l_v := p_segment13;
773 raise l_err;
774 end if;
775 if p_segment14 is not null then
776 l_n := 14;
777 l_v := p_segment14;
778 raise l_err;
779 end if;
780 if p_segment15 is not null then
781 l_n := 15;
782 l_v := p_segment15;
783 raise l_err;
784 end if;
785 if p_segment16 is not null then
786 l_n := 16;
787 l_v := p_segment16;
788 raise l_err;
789 end if;
790 if p_segment17 is not null then
791 l_n := 17;
792 l_v := p_segment17;
793 raise l_err;
794 end if;
795 if p_segment18 is not null then
796 l_n := 18;
797 l_v := p_segment18;
798 raise l_err;
799 end if;
800 if p_segment19 is not null then
801 l_n := 19;
802 l_v := p_segment19;
803 raise l_err;
804 end if;
805 if p_segment20 is not null then
806 l_n := 20;
807 l_v := p_segment20;
808 raise l_err;
809 end if;
810 if p_segment21 is not null then
811 l_n := 21;
812 l_v := p_segment21;
813 raise l_err;
814 end if;
815 if p_segment22 is not null then
816 l_n := 22;
817 l_v := p_segment22;
818 raise l_err;
819 end if;
820 if p_segment23 is not null then
821 l_n := 23;
822 l_v := p_segment23;
823 raise l_err;
824 end if;
825 if p_segment24 is not null then
826 l_n := 24;
827 l_v := p_segment24;
828 raise l_err;
829 end if;
830 if p_segment25 is not null then
831 l_n := 25;
832 l_v := p_segment25;
833 raise l_err;
834 end if;
835 if p_segment26 is not null then
836 l_n := 26;
837 l_v := p_segment26;
838 raise l_err;
839 end if;
840 if p_segment27 is not null then
841 l_n := 27;
842 l_v := p_segment27;
843 raise l_err;
844 end if;
845 if p_segment28 is not null then
846 l_n := 28;
847 l_v := p_segment28;
848 raise l_err;
849 end if;
850 if p_segment29 is not null then
851 l_n := 29;
852 l_v := p_segment29;
853 raise l_err;
854 end if;
855 if p_segment30 is not null then
856 l_n := 30;
857 l_v := p_segment30;
858 raise l_err;
859 end if;
860 --
861 hr_utility.set_location(' Leaving:'||l_proc, 15);
862 --
863 exception
864 when l_err then
865 hr_utility.set_message(801, 'HR_7296_API_ARG_NOT_SUP');
866 hr_utility.set_message_token('ARG_NAME', 'p_segment'||to_char(l_n));
867 hr_utility.set_message_token('ARG_VALUE', l_v);
868 hr_utility.raise_error;
869 end chk_us_segment10_30;
870 --
871 -- ----------------------------------------------------------------------------
872 -- |-------------------------------< kf >-------------------------------------|
873 -- ----------------------------------------------------------------------------
874 procedure kf
875 (p_rec in per_asg_shd.g_rec_type
876 ,p_validation_start_date in date
877 ,p_validation_end_date in date
878 ) is
879 --
880 l_proc varchar2(72) := g_package||'kf';
881 l_legislation_code per_business_groups.legislation_code%type;
882 l_cagr_grade_def_id number;
883 l_concat_segments_out varchar2(2000);
884 --
885 --
886 -- this cursor selects the legislation_code for the business group which
887 -- will be used to switch the segment validation.
888 --
889 cursor lcsel is
890 select pbg.legislation_code
891 from per_business_groups pbg
892 where pbg.business_group_id = p_rec.business_group_id;
893 --
894 begin
895 hr_utility.set_location('Entering:'||l_proc, 5);
896 --
897 -- ensure that p_rec.business_group_id is mandatory
898 --
899 hr_api.mandatory_arg_error
900 (p_api_name => l_proc,
901 p_argument => 'business_group_id',
902 p_argument_value => p_rec.business_group_id);
903 --
904 open lcsel;
905 fetch lcsel into l_legislation_code;
906 if lcsel%notfound then
907 close lcsel;
908 -- the business group id must be invalid
909 hr_utility.set_message(801, 'HR_7208_API_BUS_GRP_INVALID');
910 hr_utility.raise_error;
911 end if;
912 close lcsel;
913 --
914 if (l_legislation_code = 'US') then
915 --
916 -- we need to populate the hr_scl_shd.g_rec_type for use by the validation
917 -- processes.
918 -- note: the hr_scl_shd.api_updating function will ensure the
919 -- soft_coding_keyflex_id is valid and exists.
920 -- if the function returns a false the soft_coding_keyflex_id must
921 -- be null. therefore, no validation processing is required.
922 --
923 if hr_scl_shd.api_updating
924 (p_soft_coding_keyflex_id => p_rec.soft_coding_keyflex_id) then
925 --
926 -- SEGMENT1
927 --
928 chk_us_segment1(p_segment1 => hr_scl_shd.g_old_rec.segment1
929 ,p_business_group_id => p_rec.business_group_id);
930 --
931 hr_utility.set_location(l_proc, 15);
932 --
933 -- SEGMENT2
934 --
935 chk_us_segment2(p_segment2 => hr_scl_shd.g_old_rec.segment2
936 ,p_business_group_id => p_rec.business_group_id
937 ,p_validation_start_date => p_validation_start_date
938 ,p_validation_end_date => p_validation_end_date );
939 --
940 hr_utility.set_location(l_proc, 20);
941 --
942 -- SEGMENT3
943 --
944 chk_us_segment3(p_segment3 => hr_scl_shd.g_old_rec.segment3);
945 --
946 hr_utility.set_location(l_proc, 25);
947 --
948 -- SEGMENT4
949 --
950 chk_us_segment4(p_segment4 => hr_scl_shd.g_old_rec.segment4
951 ,p_organization_id => p_rec.organization_id);
952 --
953 hr_utility.set_location(l_proc, 30);
954 --
955 -- SEGMENT5
956 --
957 chk_us_segment5(p_segment5 => hr_scl_shd.g_old_rec.segment5);
958 --
959 hr_utility.set_location(l_proc, 35);
960 --
961 -- SEGMENT6
962 --
963 chk_us_segment6(p_segment6 => hr_scl_shd.g_old_rec.segment6);
964 --
965 hr_utility.set_location(l_proc, 40);
966 --
967 -- SEGMENT7
968 --
969 chk_us_segment7(p_segment7 => hr_scl_shd.g_old_rec.segment7);
970 --
971 hr_utility.set_location(l_proc, 45);
972 --
973 -- SEGMENT8
974 --
975 -- Check if assignment is being upated, as segment8 is enterable
976 -- only when an assignment is being updated, not when an assignment is
977 -- being inserted.
978 --
979 If per_asg_shd.api_updating
980 (p_assignment_id => p_rec.assignment_id
981 ,p_effective_date => p_validation_start_date
982 ,p_object_version_number => p_rec.object_version_number) then
983 --
984 chk_us_segment8(p_segment8 => hr_scl_shd.g_old_rec.segment8
985 ,p_segment1 => hr_scl_shd.g_old_rec.segment1
986 ,p_location_id => p_rec.location_id
987 ,p_assignment_id => p_rec.assignment_id );
988 else
989 if hr_scl_shd.g_old_rec.segment8 is not null then
990 hr_utility.set_message(801,'HR_50013_SCL_WORKERS_OVERRIDE');
991 hr_utility.raise_error;
992 end if;
993 end if;
994 --
995 hr_utility.set_location(l_proc, 50);
996 --
997 -- SEGMENT9
998 --
999 chk_us_segment9(p_segment9 => hr_scl_shd.g_old_rec.segment9
1000 ,p_business_group_id => p_rec.business_group_id);
1001 --
1002 hr_utility.set_location(l_proc, 55);
1003 --
1004 -- SEGMENT10 to 30
1005 --
1006 chk_us_segment10_30(p_segment10 => hr_scl_shd.g_old_rec.segment10
1007 ,p_segment11 => hr_scl_shd.g_old_rec.segment11
1008 ,p_segment12 => hr_scl_shd.g_old_rec.segment12
1009 ,p_segment13 => hr_scl_shd.g_old_rec.segment13
1010 ,p_segment14 => hr_scl_shd.g_old_rec.segment14
1011 ,p_segment15 => hr_scl_shd.g_old_rec.segment15
1012 ,p_segment16 => hr_scl_shd.g_old_rec.segment16
1013 ,p_segment17 => hr_scl_shd.g_old_rec.segment17
1014 ,p_segment18 => hr_scl_shd.g_old_rec.segment18
1015 ,p_segment19 => hr_scl_shd.g_old_rec.segment19
1016 ,p_segment20 => hr_scl_shd.g_old_rec.segment20
1017 ,p_segment21 => hr_scl_shd.g_old_rec.segment21
1018 ,p_segment22 => hr_scl_shd.g_old_rec.segment22
1019 ,p_segment23 => hr_scl_shd.g_old_rec.segment23
1020 ,p_segment24 => hr_scl_shd.g_old_rec.segment24
1021 ,p_segment25 => hr_scl_shd.g_old_rec.segment25
1022 ,p_segment26 => hr_scl_shd.g_old_rec.segment26
1023 ,p_segment27 => hr_scl_shd.g_old_rec.segment27
1024 ,p_segment28 => hr_scl_shd.g_old_rec.segment28
1025 ,p_segment29 => hr_scl_shd.g_old_rec.segment29
1026 ,p_segment30 => hr_scl_shd.g_old_rec.segment30 );
1027 --
1028 hr_utility.set_location(l_proc, 60);
1029
1030 else
1031 --
1032 -- Call segment validation processes from here to check for any
1033 -- mandatory segments. Need to explicitly pass across null values
1034 -- because an all null segment combination is not inserted into
1035 -- hr_soft_coding_keyflex.
1036 -- e.g. chk_us_segment1(p_segment1 => null);
1037 --
1038 null;
1039 end if;
1040 elsif (l_legislation_code = 'BF') then
1041 null;
1042 elsif (l_legislation_code = 'FR') then
1043 --
1044 -- Call the server side AOL routines to validate the SCL segment entres
1045 -- Need to populate g_old_rec to access the data.
1046 -- (The record has previously been inserted by
1047 -- hr_scl_ins.insert_dml)
1048 --
1049 if hr_scl_shd.api_updating
1050 (p_soft_coding_keyflex_id => p_rec.soft_coding_keyflex_id) then
1051 --
1052 hr_kflex_utility.ins_or_sel_keyflex_comb
1053 (p_appl_short_name => 'PER',
1054 p_flex_code => 'SCL',
1055 p_flex_num => hr_scl_shd.g_old_rec.id_flex_num,
1056 p_segment1 => hr_scl_shd.g_old_rec.segment1,
1057 p_segment2 => hr_scl_shd.g_old_rec.segment2,
1058 p_segment3 => hr_scl_shd.g_old_rec.segment3,
1059 p_segment4 => hr_scl_shd.g_old_rec.segment4,
1060 p_segment5 => hr_scl_shd.g_old_rec.segment5,
1061 p_segment6 => hr_scl_shd.g_old_rec.segment6,
1062 p_segment7 => hr_scl_shd.g_old_rec.segment7,
1063 p_segment8 => hr_scl_shd.g_old_rec.segment8,
1064 p_segment9 => hr_scl_shd.g_old_rec.segment9,
1065 p_segment10 => hr_scl_shd.g_old_rec.segment10,
1066 p_segment11 => hr_scl_shd.g_old_rec.segment11,
1067 p_segment12 => hr_scl_shd.g_old_rec.segment12,
1068 p_segment13 => hr_scl_shd.g_old_rec.segment13,
1069 p_segment14 => hr_scl_shd.g_old_rec.segment14,
1070 p_segment15 => hr_scl_shd.g_old_rec.segment15,
1071 p_segment16 => hr_scl_shd.g_old_rec.segment16,
1072 p_segment17 => hr_scl_shd.g_old_rec.segment17,
1073 p_segment18 => hr_scl_shd.g_old_rec.segment18,
1074 p_segment19 => hr_scl_shd.g_old_rec.segment19,
1075 p_segment20 => hr_scl_shd.g_old_rec.segment20,
1076 p_segment21 => hr_scl_shd.g_old_rec.segment21,
1077 p_segment22 => hr_scl_shd.g_old_rec.segment22,
1078 p_segment23 => hr_scl_shd.g_old_rec.segment23,
1079 p_segment24 => hr_scl_shd.g_old_rec.segment24,
1080 p_segment25 => hr_scl_shd.g_old_rec.segment25,
1081 p_segment26 => hr_scl_shd.g_old_rec.segment26,
1082 p_segment27 => hr_scl_shd.g_old_rec.segment27,
1083 p_segment28 => hr_scl_shd.g_old_rec.segment28,
1084 p_segment29 => hr_scl_shd.g_old_rec.segment29,
1085 p_segment30 => hr_scl_shd.g_old_rec.segment30,
1086 p_concat_segments_in => null,
1087 p_ccid => l_cagr_grade_def_id,
1088 p_concat_segments_out => l_concat_segments_out );
1089 --
1090 -- Ignore the out parameters as they have been set previously.
1091 --
1092 End if;
1093 elsif (l_legislation_code = 'GB') then
1094 --
1095 -- if a soft_coding_keyflex_id exists we must error because GB does not
1096 -- support SCL (i.e. segments have been specified)
1097 --
1098 if p_rec.soft_coding_keyflex_id is not null then
1099 hr_utility.set_message(801, 'HR_7296_API_ARG_NOT_SUP');
1100 hr_utility.set_message_token('ARG_NAME', 'soft_coding_keyflex_id');
1101 hr_utility.set_message_token('ARG_VALUE', p_rec.soft_coding_keyflex_id);
1102 hr_utility.raise_error;
1103 end if;
1104 end if;
1105 --
1106 hr_utility.set_location(' Leaving:'||l_proc, 10);
1107 --
1108 end kf;
1109 --
1110 end hr_scl_flex;
[Home] [Help]