1 Package Body pay_pur_bus as
2 /* $Header: pypurrhi.pkb 120.1 2005/10/26 23:17 shisriva noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pay_pur_bus.'; -- Global package name
9 --
10 -- The following two global variables are only to be
11 -- used by the return_legislation_code function.
12 --
13 g_legislation_code varchar2(150) default null;
14 g_user_row_id number default null;
15 --
16 -- ---------------------------------------------------------------------------
17 -- |----------------------< set_security_group_id >--------------------------|
18 -- ---------------------------------------------------------------------------
19 --
20 Procedure set_security_group_id
21 (p_user_row_id in number
22 ,p_associated_column1 in varchar2 default null
23 ) is
24 --
25 -- Declare cursor
26 --
27 cursor csr_sec_grp is
28 select pbg.security_group_id
29 from per_business_groups pbg
30 , pay_user_rows_f pur
31 where pur.user_row_id = p_user_row_id
32 and pbg.business_group_id = pur.business_group_id;
33 --
34 -- Declare local variables
35 --
36 l_security_group_id number;
37 l_proc varchar2(72) := g_package||'set_security_group_id';
38 --
39 begin
40 --
41 hr_utility.set_location('Entering:'|| l_proc, 10);
42 --
43 -- Ensure that all the mandatory parameter are not null
44 --
45 hr_api.mandatory_arg_error
46 (p_api_name => l_proc
47 ,p_argument => 'user_row_id'
48 ,p_argument_value => p_user_row_id
49 );
50 --
51 open csr_sec_grp;
52 fetch csr_sec_grp into l_security_group_id;
53 --
54 if csr_sec_grp%notfound then
55 --
56 close csr_sec_grp;
57 --
58 -- The primary key is invalid therefore we must error
59 --
60 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
61 hr_multi_message.add
62 (p_associated_column1
63 => nvl(p_associated_column1,'USER_ROW_ID')
64 );
65 --
66 else
67 close csr_sec_grp;
68 --
69 -- Set the security_group_id in CLIENT_INFO
70 --
71 hr_api.set_security_group_id
72 (p_security_group_id => l_security_group_id
73 );
74 end if;
75 --
76 hr_utility.set_location(' Leaving:'|| l_proc, 20);
77 --
78 end set_security_group_id;
79 --
80 -- ---------------------------------------------------------------------------
81 -- |---------------------< return_legislation_code >-------------------------|
82 -- ---------------------------------------------------------------------------
83 --
84 Function return_legislation_code
85 (p_user_row_id in number
86 )
87 Return Varchar2 Is
88 --
89 -- Declare cursor
90 --
91 cursor csr_leg_code is
92 select pbg.legislation_code
93 from per_business_groups pbg
94 , pay_user_rows_f pur
95 where pur.user_row_id = p_user_row_id
96 and pbg.business_group_id (+) = pur.business_group_id;
97 --
98 -- Declare local variables
99 --
100 l_legislation_code varchar2(150);
101 l_proc varchar2(72) := g_package||'return_legislation_code';
102 --
103 Begin
104 --
105 hr_utility.set_location('Entering:'|| l_proc, 10);
106 --
107 -- Ensure that all the mandatory parameter are not null
108 --
109 hr_api.mandatory_arg_error
110 (p_api_name => l_proc
111 ,p_argument => 'user_row_id'
112 ,p_argument_value => p_user_row_id
113 );
114 --
115 if ( nvl(pay_pur_bus.g_user_row_id, hr_api.g_number)
116 = p_user_row_id) then
117 --
118 -- The legislation code has already been found with a previous
119 -- call to this function. Just return the value in the global
120 -- variable.
121 --
122 l_legislation_code := pay_pur_bus.g_legislation_code;
123 hr_utility.set_location(l_proc, 20);
124 else
125 --
126 -- The ID is different to the last call to this function
127 -- or this is the first call to this function.
128 --
129 open csr_leg_code;
130 fetch csr_leg_code into l_legislation_code;
131 --
132 if csr_leg_code%notfound then
133 --
134 -- The primary key is invalid therefore we must error
135 --
136 close csr_leg_code;
137 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
138 fnd_message.raise_error;
139 end if;
140 hr_utility.set_location(l_proc,30);
141 --
142 -- Set the global variables so the values are
143 -- available for the next call to this function.
144 --
145 close csr_leg_code;
146 pay_pur_bus.g_user_row_id := p_user_row_id;
147 pay_pur_bus.g_legislation_code := l_legislation_code;
148 end if;
149 hr_utility.set_location(' Leaving:'|| l_proc, 40);
150 return l_legislation_code;
151 end return_legislation_code;
152 --
153 --
154 -- ----------------------------------------------------------------------------
155 -- |--------------------------< chk_legislation_code>-------------------------|
156 -- ----------------------------------------------------------------------------
157 --
158 -- Description:
159 -- Validates that the legislation code exists in fnd_territories
160 --
161 -- Pre-Requisites:
162 -- None
163 --
164 -- In Parameters:
165 -- p_legislation_code
166 --
167 -- Post Success:
168 -- Processing continues if the legislation_code is valid.
169 --
170 -- Post Failure:
171 -- An application error is raised and processing is terminated if
172 -- the legislation_code is invalid.
173 --
174 -- Developer/Implementation Notes:
175 -- None
176 --
177 -- Access Status:
178 -- Internal Row Handler Use Only
179 --
180 procedure chk_legislation_code
181 ( p_legislation_code in varchar2 )
182 is
183 --
184 cursor csr_legislation_code is
185 select null
186 from fnd_territories
187 where territory_code = p_legislation_code ;
188 --
189 l_exists varchar2(1);
190 l_proc varchar2(100) := g_package || 'chk_legislation_code';
191 begin
192 --
193 hr_utility.set_location('Entering:'|| l_proc, 10);
194
195 open csr_legislation_code;
196 fetch csr_legislation_code into l_exists ;
197
198 if csr_legislation_code%notfound then
199 close csr_legislation_code;
200 fnd_message.set_name('PAY', 'PAY_33177_LEG_CODE_INVALID');
201 fnd_message.raise_error;
202 end if;
203 close csr_legislation_code;
204
205 hr_utility.set_location(' Leaving:'|| l_proc, 20);
206 --
207 exception
208 when app_exception.application_exception then
209 if hr_multi_message.exception_add
210 (p_associated_column1 => 'PAY_USER_ROWS_F.LEGISLATION_CODE'
211 ) then
212 raise;
213 end if;
214 when others then
215 if csr_legislation_code%isopen then
216 close csr_legislation_code;
217 end if;
218 raise;
219 end chk_legislation_code;
220 --
221 -- ----------------------------------------------------------------------------
222 -- |----------------------< return_user_key_units >-------------------------|
223 -- ----------------------------------------------------------------------------
224 --
225 function return_user_key_units
226 ( p_user_table_id in PAY_USER_TABLES.USER_TABLE_ID%TYPE )
227 return varchar2 is
228 --
229 cursor csr_user_key_units
230 is
231 select user_key_units
232 from pay_user_tables put
233 where put.user_table_id = p_user_table_id ;
234
235 l_proc varchar2(100) := g_package || 'return_user_key_units';
236 --
237 begin
238
239 hr_utility.set_location('Entering:'||l_proc, 10);
240
241 if g_user_key_units is null then
242
243 --
244 -- USER_TABLE_ID is mandatory.
245 --
246 hr_api.mandatory_arg_error
247 (p_api_name => l_proc
248 ,p_argument => 'USER_TABLE_ID'
249 ,p_argument_value => p_user_table_id
250 );
251 --
252
253 open csr_user_key_units;
254 fetch csr_user_key_units into g_user_key_units;
255
256 If csr_user_key_units%notfound then
257 close csr_user_key_units;
258 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
259 fnd_message.set_token('PROCEDURE', l_proc);
260 fnd_message.set_token('STEP','5');
261 fnd_message.raise_error;
262 end if;
263
264 close csr_user_key_units;
265
266 end if;
267
268 hr_utility.set_location(' Leaving:'|| l_proc, 20);
269
270 return g_user_key_units;
271
272 end return_user_key_units;
273 --
274 -- ----------------------------------------------------------------------------
275 -- |----------------------< return_range_or_match >--------------------------|
276 -- ----------------------------------------------------------------------------
277 --
278 function return_range_or_match
279 ( p_user_table_id in PAY_USER_TABLES.USER_TABLE_ID%TYPE )
280 return varchar2 is
281 --
282 cursor csr_range_or_match
283 is
284 select range_or_match
285 from pay_user_tables put
286 where put.user_table_id = p_user_table_id ;
287
288 l_proc varchar2(100) := g_package || 'return_range_or_match';
289 --
290 begin
291
292 hr_utility.set_location('Entering:'||l_proc, 10);
293
294 if g_range_or_match is null then
295
296 --
297 -- USER_TABLE_ID is mandatory.
298 --
299 hr_api.mandatory_arg_error
300 (p_api_name => l_proc
301 ,p_argument => 'USER_TABLE_ID'
302 ,p_argument_value => p_user_table_id
303 );
304 --
305
306 open csr_range_or_match;
307 fetch csr_range_or_match into g_range_or_match;
308
309 If csr_range_or_match%notfound then
310 close csr_range_or_match;
311 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
312 fnd_message.set_token('PROCEDURE', l_proc);
313 fnd_message.set_token('STEP','5');
314 fnd_message.raise_error;
315 end if;
316
317 close csr_range_or_match;
318
319 end if;
320
321 hr_utility.set_location(' Leaving:'|| l_proc, 20);
322
323 return g_range_or_match;
324
325 end return_range_or_match;
326 -- ----------------------------------------------------------------------------
327 -- |--------------------------< chk_user_table_id >---------------------------|
328 -- ----------------------------------------------------------------------------
329 --
330 -- Description:
331 -- Validates that the user_table_id exists in pay_user_tables
332 --
333 -- Pre-Requisites:
334 -- None
335 --
336 -- In Parameters:
337 -- p_user_table_id
338 -- p_legislation_code
339 -- p_business_group_id
340 --
341 -- Post Success:
342 -- Processing continues if the user_table_id is valid.
343 --
344 -- Post Failure:
345 -- An application error is raised and processing is terminated if
346 -- the user_table_id is invalid.
347 --
348 -- Developer/Implementation Notes:
349 -- None
350 --
351 -- Access Status:
352 -- Internal Row Handler Use Only
353 --
354 procedure chk_user_table_id
355 (p_user_table_id in number
356 ,p_legislation_code in varchar2
357 ,p_business_group_id in number
358 ) is
359 --
360 cursor csr_user_table_id is
361 select put.legislation_code , put.business_group_id
362 from pay_user_tables put
363 where put.user_table_id = p_user_table_id ;
364 --
365 l_busgrpid PAY_USER_ROWS_F.BUSINESS_GROUP_ID%TYPE;
366 l_legcode PAY_USER_ROWS_F.LEGISLATION_CODE%TYPE;
367
368 l_proc varchar2(100) := g_package || 'chk_user_table_id';
369 begin
370
371 hr_utility.set_location('Entering:'|| l_proc, 10);
372 --
373 -- USER_TABLE_ID is mandatory.
374 --
375 hr_api.mandatory_arg_error
376 (p_api_name => l_proc
377 ,p_argument => 'USER_TABLE_ID'
378 ,p_argument_value => p_user_table_id
379 );
380 --
381 open csr_user_table_id;
382 fetch csr_user_table_id into l_legcode, l_busgrpid ;
383
384 if csr_user_table_id%notfound then
385 close csr_user_table_id;
386 fnd_message.set_name('PAY', 'PAY_33174_PARENT_ID_INVALID');
387 fnd_message.set_token('PARENT' , 'User Table Id' );
388 fnd_message.raise_error;
389 end if;
390 close csr_user_table_id;
391 --
392 -- Confirm that the parent USER_TABLE's startup mode is compatible
393 -- with this PAY_USER_ROWS row.
394 --
395 if not pay_put_shd.chk_startup_mode_compatible
396 (p_parent_bgid => l_busgrpid
397 ,p_parent_legcode => l_legcode
398 ,p_child_bgid => p_business_group_id
399 ,p_child_legcode => p_legislation_code
400 ) then
401 fnd_message.set_name('PAY', 'PAY_33175_BGLEG_MISMATCH');
402 fnd_message.set_token('CHILD', 'User Row');
403 fnd_message.set_token('PARENT' , 'User Table');
404 fnd_message.raise_error;
405 end if;
406
407 hr_utility.set_location(' Leaving:'|| l_proc, 20);
408
409 exception
410 when app_exception.application_exception then
411 if hr_multi_message.exception_add
412 (p_associated_column1 => 'PAY_USER_ROWS_F.USER_TABLE_ID'
413 ) then
414 raise;
415 end if;
416 when others then
417 if csr_user_table_id%isopen then
418 close csr_user_table_id;
419 end if;
420 raise;
421
422 end chk_user_table_id;
423 --
424 -- ----------------------------------------------------------------------------
425 -- |------------------------< chk_format >------------------------------------|
426 -- ----------------------------------------------------------------------------
427 --
428 -- Description:
429 -- Validates that the p_value is in the format specified by p_format_code
430 --
431 -- Pre-Requisites:
432 -- None
433 --
434 -- In Parameters:
435 -- p_value - This relates to the row_low_range_or_name or row_high_range
436 -- columns in pay_user_rows_f table
437 -- p_format_code - This relates to user_key_units column in pay_user_tables
438 --
439 -- Post Success:
440 -- Processing continues if p_value is valid.
441 --
442 -- Post Failure:
443 -- An application error is raised and processing is terminated if
444 -- the p_value is invalid.
445 --
446 -- Developer/Implementation Notes:
447 -- None
448 --
449 -- Access Status:
450 -- Internal Row Handler Use Only
451 --
452 function chk_format
453 (p_value in out nocopy varchar2
454 ,p_format_code in varchar2
455 )
456 return boolean is
457 --
458 l_return boolean;
459 l_dummy varchar2(255);
460 l_format varchar2(255);
461 l_unformatted_value varchar2(255);
462
463 l_proc varchar2(100) := g_package || 'chk_format';
464 --
465 begin
466 --
467 hr_utility.set_location('Entering:'|| l_proc, 10);
468
469 if p_format_code = 'D' then
470 l_format := 'DATE' ;
471 elsif p_format_code = 'T' then
472 l_format := 'C' ;
473 else
474 l_format := p_format_code ;
475 end if ;
476
477 l_unformatted_value := p_value;
478
479 hr_chkfmt.checkformat ( l_unformatted_value ,
480 l_format ,
481 p_value ,
482 null ,
483 null ,
484 'N' ,
485 l_dummy,
486 null
487 );
488 l_return := TRUE;
489
490 hr_utility.set_location(' Leaving:'|| l_proc, 20);
491
492 return l_return;
493
494 exception
495 when app_exception.application_exception then
496 l_return := FALSE;
497 return l_return;
498
499 end chk_format;
500 -- ----------------------------------------------------------------------------
501 -- |------------------------< chk_row_low_range_or_name >---------------------|
502 -- ----------------------------------------------------------------------------
503 --
504 -- Description:
505 -- Validates that the row_low_range_or_name
506 -- 1. Is Mandatory.
507 -- 2. Is Numeric if range match is used.
508 -- 3. Is in the format as specified by user_key_units in pay_user_tables
509 -- (Conditional)
510 -- 4. Is Unique. (Conditional)
511 --
512 -- Pre-Requisites:
513 -- user_table_id must be validated.
514 --
515 -- In Parameters:
516 -- p_user_row_id
517 -- p_user_table_id
518 -- p_row_low_range_or_name
519 -- p_object_version_number
520 -- p_disable_units_check - User supplied flag which indicates whether
521 -- data type validation has to be carried out or not
522 -- p_disable_range_overlap_check - User supplied flag which indicates
523 -- whether range overlap check has to be
524 -- carried out or not (Conditional)
525 -- p_legislation_code
526 -- p_business_group_id
527 -- p_effective_date
528 -- p_validation_start_date
529 -- p_validation_end_date
530 --
531 -- Post Success:
532 -- Processing continues if the row_low_range_or_name is valid.
533 --
534 -- Post Failure:
535 -- An application error is raised and processing is terminated if
536 -- the row_low_range_or_name is invalid.
537 --
538 -- Developer/Implementation Notes:
539 -- None
540 --
541 -- Access Status:
542 -- Internal Row Handler Use Only
543 --
544 procedure chk_row_low_range_or_name
545 ( p_user_row_id in number
546 ,p_user_table_id in number
547 ,p_row_low_range_or_name in out nocopy varchar2
548 ,p_object_version_number in number
549 ,p_disable_units_check in boolean
550 ,p_disable_range_overlap_check in boolean
551 ,p_business_group_id in number
552 ,p_legislation_code in varchar2
553 ,p_effective_date in date
554 ,p_validation_start_date in date
555 ,p_validation_end_date in date
556 ) is
557 --
558 cursor csr_unique_name is
559 select null
560 from pay_user_rows_f usr
561 where usr.user_table_id = p_user_table_id
562 and upper(usr.row_low_range_or_name) = upper(p_row_low_range_or_name)
563 and ( p_user_row_id is null
564 or ( p_user_row_id is not null and usr.user_row_id <> p_user_row_id ) )
565 and ( p_business_group_id is null
566 or ( p_business_group_id is not null and p_business_group_id = usr.business_group_id )
567 or ( p_business_group_id is not null and
568 usr.legislation_code is null and usr.business_group_id is null )
569 or ( p_business_group_id is not null and
570 usr.legislation_code = hr_api.return_legislation_code(p_business_group_id )))
571 and ( p_legislation_code is null
572 or ( p_legislation_code is not null and p_legislation_code = usr.legislation_code )
573 or ( p_legislation_code is not null and
574 usr.legislation_code is null and usr.business_group_id is null)
575 or ( p_legislation_code is not null and
576 p_legislation_code = hr_api.return_legislation_code(usr.business_group_id )))
577 and ( usr.effective_start_date <= p_validation_end_date and
578 usr.effective_end_date >= p_validation_start_date );
579
580
581 l_proc varchar2(100) := g_package || 'chk_row_low_range_or_name';
582 l_range_or_match PAY_USER_TABLES.RANGE_OR_MATCH%TYPE;
583 l_user_key_units PAY_USER_TABLES.USER_KEY_UNITS%TYPE;
584 l_exists varchar2(1);
585 l_result boolean;
586 l_prod_status varchar2(1);
587 l_ghr_installed varchar2(1);
588 l_industry varchar2(1);
589 l_oracle_scheema varchar2(30);
590
591 begin
592
593 hr_utility.set_location('Entering:'|| l_proc, 10);
594
595 if hr_multi_message.no_exclusive_error
596 (p_check_column1 => 'PAY_USER_ROWS_F.USER_TABLE_ID'
597 ,p_associated_column1 => 'PAY_USER_ROWS_F.ROW_LOW_RANGE_OR_NAME'
598 ) and (
599 not pay_pur_shd.api_updating
600 (p_user_row_id => p_user_row_id
601 ,p_effective_date => p_effective_date
602 ,p_object_version_number => p_object_version_number
603 ) or
604 nvl(p_row_low_range_or_name, hr_api.g_varchar2) <>
605 pay_pur_shd.g_old_rec.row_low_range_or_name
606 ) then
607 --
608 -- The name is mandatory.
609 --
610 hr_api.mandatory_arg_error
611 (p_api_name => l_proc
612 ,p_argument => 'ROW_LOW_RANGE_OR_NAME'
613 ,p_argument_value => p_row_low_range_or_name
614 );
615
616 if p_disable_units_check is null then
617 fnd_message.set_name('PAY', 'HR_7207_API_MANDATORY_ARG');
618 fnd_message.set_token('API_NAME', l_proc);
619 fnd_message.set_token('ARGUMENT','DISABLE_UNITS_CHECK');
620 fnd_message.raise_error;
621 end if;
622
623 if p_disable_range_overlap_check is null then
624 fnd_message.set_name('PAY', 'HR_7207_API_MANDATORY_ARG');
625 fnd_message.set_token('API_NAME', l_proc);
626 fnd_message.set_token('ARGUMENT','DISABLE_RANGE_OVERLAP_CHECK');
627 fnd_message.raise_error;
628 end if;
629
630 l_user_key_units := return_user_key_units(p_user_table_id);
631 l_range_or_match := return_range_or_match(p_user_table_id);
632
633
634 if( l_range_or_match = 'R' or ( l_range_or_match = 'M' and p_disable_units_check = FALSE)) then
635
636 l_result := chk_format( p_row_low_range_or_name,
637 l_user_key_units ) ;
638
639 if ( l_result = FALSE ) then
640 if ( l_range_or_match = 'M' ) then
641 fnd_message.set_name ( 'PAY', 'PAY_33131_UT_INVALID_ROW' );
642 fnd_message.raise_error;
643 elsif (l_range_or_match = 'R' ) then
644 fnd_message.set_name ( 'PAY', 'PAY_34025_UT_RANGE_NOT_NUMERIC' );
645 fnd_message.raise_error;
646 end if;
647 end if;
648 end if;
649
650
651 l_result := fnd_installation.get_app_info ( 'GHR',
652 l_prod_status,
653 l_industry,
654 l_oracle_scheema );
655
656 if ( l_prod_status = 'I' ) then
657 l_ghr_installed := 'Y';
658 else
659 l_ghr_installed := 'N';
660 end if;
661
662 if ( l_ghr_installed = 'N'
663 or ( l_ghr_installed = 'Y' and l_range_or_match = 'M' )
664 or ( l_ghr_installed = 'Y' and p_disable_range_overlap_check = FALSE ) ) then
665
666 open csr_unique_name;
667 fetch csr_unique_name into l_exists;
668
669 if csr_unique_name%found then
670 close csr_unique_name;
671 fnd_message.set_name( 'PAY' , 'PAY_7884_USER_TABLE_UNIQUE' );
672 fnd_message.raise_error ;
673 end if ;
674
675 close csr_unique_name;
676 end if;
677
678 end if;
679
680 hr_utility.set_location(' Leaving:'|| l_proc, 20);
681
682 exception
683
684 when app_exception.application_exception then
685 if hr_multi_message.exception_add
686 (p_associated_column1 => 'PAY_USER_ROWS_F.ROW_LOW_RANGE_OR_NAME') then
687 raise;
688 end if;
689
690 when others then
691 if csr_unique_name%isopen then
692 close csr_unique_name;
693 end if;
694 raise;
695
696 end chk_row_low_range_or_name ;
697 --
698 -- ----------------------------------------------------------------------------
699 -- |------------------------< chk_row_high_range >----------------------------|
700 -- ----------------------------------------------------------------------------
701 --
702 -- Description:
703 -- Validates that the row_high_range
704 -- 1. Is Mandatory for range tables.
705 -- 2. Is Ignored for match tables.
706 -- 3. Is numeric.
707 --
708 -- Pre-Requisites:
709 -- user_table_id must be validated
710 --
711 -- In Parameters:
712 -- p_user_row_id
713 -- p_user_table_id
714 -- p_row_high_range
715 -- p_object_version_number
716 -- p_effective_date
717 --
718 -- Post Success:
719 -- Processing continues if the row_high_range is valid.
720 --
721 -- Post Failure:
722 -- An application error is raised and processing is terminated if
723 -- the row_high_range is invalid.
724 --
725 -- Developer/Implementation Notes:
726 -- None
727 --
728 -- Access Status:
729 -- Internal Row Handler Use Only
730 --
731 procedure chk_row_high_range
732 ( p_row_high_range in out nocopy varchar2
733 ,p_user_table_id in number
734 ,p_user_row_id in number
735 ,p_object_version_number in number
736 ,p_effective_date in date
737 ) is
738 --
739 l_proc varchar2(100) := g_package || 'chk_row_high_range';
740 l_range_or_match PAY_USER_TABLES.RANGE_OR_MATCH%TYPE;
741 l_user_key_units PAY_USER_TABLES.USER_KEY_UNITS%TYPE;
742 l_exists varchar2(1);
743 l_result boolean;
744 --
745 begin
746
747 hr_utility.set_location('Entering:'|| l_proc, 10);
748
749 if hr_multi_message.no_exclusive_error
750 (p_check_column1 => 'PAY_USER_ROWS_F.USER_TABLE_ID'
751 ,p_associated_column1 => 'PAY_USER_ROWS_F.ROW_HIGH_RANGE'
752 ) and (
753 not pay_pur_shd.api_updating
754 (p_user_row_id => p_user_row_id
755 ,p_effective_date => p_effective_date
756 ,p_object_version_number => p_object_version_number
757 ) or
758 nvl(p_row_high_range, hr_api.g_varchar2) <>
759 nvl(pay_pur_shd.g_old_rec.row_high_range, hr_api.g_varchar2)
760 ) then
761 --
762 -- Ignore all validations for ROW_HIGH_RANGE for Exact Match User Tables.
763 --
764
765 l_user_key_units := return_user_key_units(p_user_table_id);
766 l_range_or_match := return_range_or_match(p_user_table_id);
767
768 if l_range_or_match = 'R' then
769
770 -- row_high_range is mandatory
771
772 hr_api.mandatory_arg_error
773 (p_api_name => l_proc
774 ,p_argument => 'ROW_HIGH_RANGE'
775 ,p_argument_value => p_row_high_range
776 );
777
778
779 l_result := chk_format( p_row_high_range,
780 l_user_key_units ) ;
781
782 if ( l_result = FALSE ) then
783 fnd_message.set_name ( 'PAY', 'PAY_34025_UT_RANGE_NOT_NUMERIC' );
784 fnd_message.raise_error;
785 end if;
786 end if;
787 end if;
788
789 hr_utility.set_location(' Leaving:'|| l_proc, 20);
790
791 exception
792
793 when app_exception.application_exception then
794 if hr_multi_message.exception_add
795 (p_associated_column1 => 'PAY_USER_ROWS_F.ROW_HIGH_RANGE') then
796 raise;
797 end if;
798
799 when others then
800 raise;
801
802 end chk_row_high_range ;
803 --
804 -- ----------------------------------------------------------------------------
805 -- |-----------------------------< chk_range >--------------------------------|
806 -- ----------------------------------------------------------------------------
807 --
808 -- Description:
809 -- Validates that the
810 -- 1. row_high_range >= row_low_range_or_name for range tables
811 -- 2. The range row_low_range_or_name -> row_high_range does not overlap
812 -- with other rows. (Conditional)
813 --
814 -- Pre-Requisites:
815 -- row_low_range_or_name and row_high_range must be validated.
816 --
817 -- In Parameters:
818 -- p_user_row_id
819 -- p_user_table_id
820 -- p_row_low_range_or_name
821 -- p_row_high_range
822 -- p_object_version_number
823 -- p_disable_range_overlap_check - User supplied flag which indicates
824 -- whether range overlap check has to be
825 -- carried out or not (Conditional)
826 -- p_legislation_code
827 -- p_business_group_id
828 -- p_effective_date
829 -- p_validation_start_date
830 -- p_validation_end_date
831 --
832 -- Post Success:
833 -- Processing continues if the range is valid.
834 --
835 -- Post Failure:
836 -- An application error is raised and processing is terminated if
837 -- the range is invalid.
838 --
839 -- Developer/Implementation Notes:
840 -- None
841 --
842 -- Access Status:
843 -- Internal Row Handler Use Only
844 --
845 procedure chk_range
846 ( p_user_row_id in number
847 ,p_user_table_id in number
848 ,p_row_low_range_or_name in varchar2
849 ,p_row_high_range in varchar2
850 ,p_disable_range_overlap_check in boolean
851 ,p_object_version_number in number
852 ,p_business_group_id in number
853 ,p_legislation_code in varchar2
854 ,p_effective_date in date
855 ,p_validation_start_date in date
856 ,p_validation_end_date in date
857 ) is
858 --
859
860 cursor csr_row_overlap is
861 select null
862 from pay_user_rows_f usr
863 where usr.user_table_id = p_user_table_id
864 and ( p_user_row_id is null
865 or ( p_user_row_id is not null and usr.user_row_id <> p_user_row_id ) )
866 and ( p_business_group_id is null
867 or ( p_business_group_id is not null and p_business_group_id = usr.business_group_id )
868 or ( p_business_group_id is not null and
869 usr.legislation_code is null and usr.business_group_id is null )
870 or ( p_business_group_id is not null and
871 usr.legislation_code = hr_api.return_legislation_code(p_business_group_id ) ))
872 and ( p_legislation_code is null
873 or ( p_legislation_code is not null and p_legislation_code = usr.legislation_code )
874 or ( p_legislation_code is not null and
875 usr.legislation_code is null and usr.business_group_id is null)
876 or ( p_legislation_code is not null and
877 p_legislation_code = hr_api.return_legislation_code(usr.business_group_id) ))
878 and (fnd_number.canonical_to_number(p_row_low_range_or_name) between
879 fnd_number.canonical_to_number(usr.row_low_range_or_name) and fnd_number.canonical_to_number(usr.row_high_range)
880 or (fnd_number.canonical_to_number(p_row_high_range) between
881 fnd_number.canonical_to_number(usr.row_low_range_or_name) and fnd_number.canonical_to_number(usr.row_high_range))
882 or (fnd_number.canonical_to_number(usr.row_low_range_or_name) between
883 fnd_number.canonical_to_number(p_row_low_range_or_name) and fnd_number.canonical_to_number(p_row_high_range))
884 or (fnd_number.canonical_to_number(usr.row_high_range) between
885 fnd_number.canonical_to_number(p_row_low_range_or_name) and fnd_number.canonical_to_number(p_row_high_range)))
886 and ( usr.effective_start_date <= p_validation_end_date and
887 usr.effective_end_date >= p_validation_start_date );
888
889 l_proc varchar2(100) := g_package || 'chk_range';
890 l_range_or_match PAY_USER_TABLES.RANGE_OR_MATCH%TYPE;
891 l_user_key_units PAY_USER_TABLES.USER_KEY_UNITS%TYPE;
892 l_exists varchar2(1);
893 l_result boolean;
894 l_prod_status varchar2(1);
895 l_ghr_installed varchar2(1);
896 l_industry varchar2(1);
897 l_oracle_scheema varchar2(30);
898
899 begin
900
901 hr_utility.set_location('Entering:'|| l_proc, 10);
902
903 if hr_multi_message.no_exclusive_error
904 ( p_check_column1 => 'PAY_USER_ROWS_F.ROW_LOW_RANGE_OR_NAME'
905 ,p_check_column2 => 'PAY_USER_ROWS_F.ROW_HIGH_RANGE'
906 ,p_associated_column1 => 'PAY_USER_ROWS_F.ROW_LOW_RANGE_OR_NAME'
907 ,p_associated_column2 => 'PAY_USER_ROWS_F.ROW_HIGH_RANGE'
908 ) and (
909 not pay_pur_shd.api_updating
910 (p_user_row_id => p_user_row_id
911 ,p_effective_date => p_effective_date
912 ,p_object_version_number => p_object_version_number
913 ) or (
914 nvl(p_row_low_range_or_name, hr_api.g_varchar2) <>
915 nvl(pay_pur_shd.g_old_rec.row_low_range_or_name, hr_api.g_varchar2) or
916 nvl(p_row_high_range, hr_api.g_varchar2) <>
917 nvl(pay_pur_shd.g_old_rec.row_high_range, hr_api.g_varchar2))
918 ) then
919
920 if p_disable_range_overlap_check is null then
921 fnd_message.set_name('PAY', 'HR_7207_API_MANDATORY_ARG');
922 fnd_message.set_token('API_NAME', l_proc);
923 fnd_message.set_token('ARGUMENT','DISABLE_RANGE_OVERLAP_CHECK');
924 fnd_message.raise_error;
925 end if;
926
927 -- Validation required only for Range Match
928
929 l_range_or_match := return_range_or_match(p_user_table_id);
930
931 if l_range_or_match = 'R' then
932
933 -- Bug 3832215. Convert row_low_range_or_name and row_high_range
934 -- from canonical to number before compare.
935
936 if fnd_number.canonical_to_number(p_row_high_range) <
937 fnd_number.canonical_to_number(p_row_low_range_or_name) then
938 fnd_message.set_name('PAY','PAY_33178_RANGE_INVALID');
939 fnd_message.raise_error ;
940 end if;
941
942
943 l_result := fnd_installation.get_app_info ( 'GHR',
944 l_prod_status,
945 l_industry,
946 l_oracle_scheema );
947
948 if ( l_prod_status = 'I' ) then
949 l_ghr_installed := 'Y';
950 else
951 l_ghr_installed := 'N';
952 end if;
953
954 if ( ( l_ghr_installed = 'N' )
955 or ( l_ghr_installed = 'Y' and p_disable_range_overlap_check = FALSE) ) then
956
957 open csr_row_overlap;
958 fetch csr_row_overlap into l_exists;
959
960 if csr_row_overlap%found then
961 close csr_row_overlap;
962 fnd_message.set_name('PER','PER_34003_USER_ROW_OVERLAP');
963 fnd_message.raise_error ;
964 end if ;
965
966 close csr_row_overlap;
967 end if;
968 end if;
969 end if;
970
971 hr_utility.set_location(' Leaving:'|| l_proc, 20);
972
973 exception
974
975 when app_exception.application_exception then
976 if hr_multi_message.exception_add
977 (p_associated_column1 => 'PAY_USER_ROWS_F.ROW_LOW_RANGE_OR_NAME' ,
978 p_associated_column2 => 'PAY_USER_ROWS_F.ROW_HIGH_RANGE' ) then
979 raise;
980 end if;
981
982 when others then
983 if csr_row_overlap%isopen then
984 close csr_row_overlap;
985 end if;
986 raise;
987 end chk_range;
988 --
989 --
990 -- ----------------------------------------------------------------------------
991 -- |-----------------------------< chk_display_sequence >---------------------|
992 -- ----------------------------------------------------------------------------
993 --
994 -- Description:
995 -- Validates that the display_sequence is numeric
996 --
997 -- Pre-Requisites:
998 -- None
999 --
1000 -- In Parameters:
1001 -- p_display_sequence
1002 --
1003 -- Post Success:
1004 -- Processing continues if the display_sequence is valid.
1005 --
1006 -- Post Failure:
1007 -- An application error is raised and processing is terminated if
1008 -- the display_sequence is invalid.
1009 --
1010 -- Developer/Implementation Notes:
1011 -- None
1012 --
1013 -- Access Status:
1014 -- Internal Row Handler Use Only
1015 --
1016 procedure chk_display_sequence
1017 ( p_display_sequence in number )
1018 is
1019 l_proc varchar2(100) := g_package || 'chk_display_sequence';
1020 --
1021 Begin
1022
1023 hr_utility.set_location('Entering:'|| l_proc, 10);
1024 /* Bug fix: 4661747 : Added the if condition to this call */
1025 if(p_display_sequence is not NULL) then
1026 hr_dbchkfmt.is_db_format( p_display_sequence , 'DISPLAY_SEQUENCE' , 'I' );
1027 end if;
1028
1029 hr_utility.set_location(' Leaving:'|| l_proc, 20);
1030
1031 Exception
1032
1033 when app_exception.application_exception then
1034 if hr_multi_message.exception_add
1035 (p_associated_column1 => 'PAY_USER_ROWS_F.DISPLAY_SEQUENCE' ) then
1036 raise;
1037 end if;
1038
1039 when others then
1040 raise;
1041
1042 end chk_display_sequence;
1043 --
1044 -- ----------------------------------------------------------------------------
1045 -- |------------------------------< chk_delete >------------------------------|
1046 -- ----------------------------------------------------------------------------
1047 --
1048 -- Description:
1049 -- Validates that the
1050 -- 1. For Delete Mode there are no child rows on or after
1051 -- validation_start_date
1052 -- 2. For Zap Mode there are no child rows at all.
1053 -- 3. For DELETE_NEXT_CHANGE and FUTURE_CHANGE modes deletion
1054 -- will not violate the uniqueness or overlap constraints.(Conditional)
1055 --
1056 -- Pre-Requisites:
1057 -- None.
1058 --
1059 -- In Parameters:
1060 -- p_user_row_id
1061 -- p_user_table_id
1062 -- p_row_low_range_or_name
1063 -- p_row_high_range
1064 -- p_datetrack_mode
1065 -- p_disable_range_overlap_check - User supplied flag which indicates
1066 -- whether range overlap check has to be
1067 -- carried out or not (Conditional)
1068 -- p_legislation_code
1069 -- p_business_group_id
1070 -- p_validation_start_date
1071 -- p_validation_end_date
1072 --
1073 -- Post Success:
1074 -- Processing continues if the deletion is valid.
1075 --
1076 -- Post Failure:
1077 -- An application error is raised and processing is terminated if
1078 -- the deletion is invalid.
1079 --
1080 -- Developer/Implementation Notes:
1081 -- None
1082 --
1083 -- Access Status:
1084 -- Internal Row Handler Use Only
1085 --
1086 procedure chk_delete
1087 (p_user_table_id in number
1088 ,p_user_row_id in number
1089 ,p_row_low_range_or_name in varchar2
1090 ,p_row_high_range in varchar2
1091 ,p_datetrack_mode in varchar2
1092 ,p_business_group_id in number
1093 ,p_legislation_code in varchar2
1094 ,p_disable_range_overlap_check in boolean
1095 ,p_validation_start_date in date
1096 ,p_validation_end_date in date
1097 )is
1098 --
1099 cursor csr_unique_name is
1100 select null
1101 from pay_user_rows_f usr
1102 where usr.user_table_id = p_user_table_id
1103 and upper(usr.row_low_range_or_name) = upper(p_row_low_range_or_name)
1104 and ( p_user_row_id is null
1105 or ( p_user_row_id is not null and usr.user_row_id <> p_user_row_id ) )
1106 and ( p_business_group_id is null
1107 or ( p_business_group_id is not null and p_business_group_id = usr.business_group_id )
1108 or ( p_business_group_id is not null and
1109 usr.legislation_code is null and usr.business_group_id is null )
1110 or ( p_business_group_id is not null and
1111 usr.legislation_code = hr_api.return_legislation_code(p_business_group_id ) ))
1112 and ( p_legislation_code is null
1113 or ( p_legislation_code is not null and p_legislation_code = usr.legislation_code )
1114 or ( p_legislation_code is not null and
1115 usr.legislation_code is null and usr.business_group_id is null)
1116 or ( p_legislation_code is not null and
1117 p_legislation_code = hr_api.return_legislation_code(usr.business_group_id ) ))
1118 and ( usr.effective_start_date <= p_validation_end_date and
1119 usr.effective_end_date >= p_validation_start_date );
1120
1121 cursor csr_row_overlap is
1122 select null
1123 from pay_user_rows_f usr
1124 where usr.user_table_id = p_user_table_id
1125 and ( p_user_row_id is null
1126 or ( p_user_row_id is not null and usr.user_row_id <> p_user_row_id ) )
1127 and ( p_business_group_id is null
1128 or ( p_business_group_id is not null and p_business_group_id = usr.business_group_id )
1129 or ( p_business_group_id is not null and
1130 usr.legislation_code is null and usr.business_group_id is null )
1131 or ( p_business_group_id is not null and
1132 usr.legislation_code = hr_api.return_legislation_code(p_business_group_id ) ))
1133 and ( p_legislation_code is null
1134 or ( p_legislation_code is not null and p_legislation_code = usr.legislation_code )
1135 or ( p_legislation_code is not null and
1136 usr.legislation_code is null and usr.business_group_id is null)
1137 or ( p_legislation_code is not null and
1138 p_legislation_code = hr_api.return_legislation_code(usr.business_group_id ) ))
1139 and (fnd_number.canonical_to_number(p_row_low_range_or_name) between
1140 fnd_number.canonical_to_number(usr.row_low_range_or_name) and fnd_number.canonical_to_number(usr.row_high_range)
1141 or (fnd_number.canonical_to_number(p_row_high_range) between
1142 fnd_number.canonical_to_number(usr.row_low_range_or_name) and fnd_number.canonical_to_number(usr.row_high_range))
1143 or (fnd_number.canonical_to_number(usr.row_low_range_or_name) between
1144 fnd_number.canonical_to_number(p_row_low_range_or_name) and fnd_number.canonical_to_number(p_row_high_range))
1145 or (fnd_number.canonical_to_number(usr.row_high_range) between
1146 fnd_number.canonical_to_number(p_row_low_range_or_name) and fnd_number.canonical_to_number(p_row_high_range)))
1147 and ( usr.effective_start_date <= p_validation_end_date and
1148 usr.effective_end_date >= p_validation_start_date );
1149
1150 cursor csr_zap_mode is
1151 select null
1152 from pay_user_column_instances_f
1153 where user_row_id = p_user_row_id ;
1154
1155 cursor csr_delete_mode is
1156 select null
1157 from pay_user_column_instances_f
1158 where user_row_id = p_user_row_id
1159 and effective_end_date >= p_validation_start_date ;
1160
1161
1162 l_exists varchar2(1);
1163 l_proc varchar2(100) := g_package || 'chk_delete';
1164 l_result boolean;
1165 l_range_or_match PAY_USER_TABLES.RANGE_OR_MATCH%TYPE;
1166 l_prod_status varchar2(1);
1167 l_ghr_installed varchar2(1);
1168 l_industry varchar2(1);
1169 l_oracle_scheema varchar2(30);
1170
1171 --
1172 Begin
1173
1174 hr_utility.set_location('Entering:'|| l_proc, 10);
1175
1176 if p_disable_range_overlap_check is null then
1177 fnd_message.set_name('PAY', 'HR_7207_API_MANDATORY_ARG');
1178 fnd_message.set_token('API_NAME', l_proc);
1179 fnd_message.set_token('ARGUMENT','DISABLE_RANGE_OVERLAP_CHECK');
1180 fnd_message.raise_error;
1181 end if;
1182
1183 if p_datetrack_mode = hr_api.g_delete then
1184
1185 open csr_delete_mode;
1186 fetch csr_delete_mode into l_exists;
1187 if csr_delete_mode%found then
1188 close csr_delete_mode;
1189 fnd_message.set_name( 'PAY', 'PAY_6982_USERTAB_END_VALUES' );
1190 fnd_message.raise_error;
1191 end if;
1192 close csr_delete_mode;
1193
1194 elsif p_datetrack_mode = hr_api.g_zap then
1195
1196 open csr_zap_mode;
1197 fetch csr_zap_mode into l_exists ;
1198 if csr_zap_mode%found then
1199 close csr_zap_mode;
1200 fnd_message.set_name( 'PAY', 'HR_6980_USERTAB_VALUES_FIRST' ) ;
1201 fnd_message.set_token( 'ROWCOL' , 'row' ) ;
1202 fnd_message.raise_error ;
1203 end if ;
1204 close csr_zap_mode;
1205
1206 elsif p_datetrack_mode in (hr_api.g_future_change,hr_api.g_delete_next_change) then
1207
1208
1209 l_result := fnd_installation.get_app_info ( 'GHR',
1210 l_prod_status,
1211 l_industry,
1212 l_oracle_scheema );
1213
1214 if ( l_prod_status = 'I' ) then
1215 l_ghr_installed := 'Y';
1216 else
1217 l_ghr_installed := 'N';
1218 end if;
1219
1220 l_range_or_match := return_range_or_match(p_user_table_id);
1221
1222 if ( l_ghr_installed = 'N'
1223 or ( l_ghr_installed = 'Y' and l_range_or_match = 'M' )
1224 or ( l_ghr_installed = 'Y' and p_disable_range_overlap_check = FALSE ) ) then
1225
1226 open csr_unique_name;
1227 fetch csr_unique_name into l_exists;
1228
1229 if csr_unique_name%found then
1230 close csr_unique_name;
1231 fnd_message.set_name ('PAY','HR_72033_CANNOT_DNC_RECORD');
1232 fnd_message.raise_error ;
1233 end if;
1234 close csr_unique_name;
1235 end if;
1236
1237 if ( l_range_or_match = 'R'
1238 and ( l_ghr_installed = 'N'
1239 or ( l_ghr_installed = 'Y' and p_disable_range_overlap_check = FALSE))) then
1240
1241 open csr_row_overlap;
1242 fetch csr_row_overlap into l_exists;
1243
1244 if csr_row_overlap%found then
1245 close csr_row_overlap;
1246 fnd_message.set_name ('PAY','HR_72033_CANNOT_DNC_RECORD');
1247 fnd_message.raise_error ;
1248 end if ;
1249
1250 close csr_row_overlap;
1251 end if;
1252 end if;
1253
1254 hr_utility.set_location(' Leaving:'|| l_proc, 20);
1255 Exception
1256
1257 when app_exception.application_exception then
1258 if hr_multi_message.exception_add
1259 (p_associated_column1 => 'PAY_USER_ROWS_F.USER_ROW_ID') then
1260 raise;
1261 end if;
1262
1263 when others then
1264 if csr_unique_name%isopen then
1265 close csr_unique_name;
1266 end if;
1267
1268 if csr_row_overlap%isopen then
1269 close csr_row_overlap;
1270 end if;
1271
1272 if csr_zap_mode%isopen then
1273 close csr_zap_mode;
1274 end if;
1275
1276 if csr_delete_mode%isopen then
1277 close csr_delete_mode;
1278 end if;
1279
1280 raise;
1281
1282 End chk_delete;
1283 --
1284 -- ----------------------------------------------------------------------------
1285 -- |-----------------------< chk_non_updateable_args >------------------------|
1286 -- ----------------------------------------------------------------------------
1287 -- {Start Of Comments}
1288 --
1289 -- Description:
1290 -- This procedure is used to ensure that non updateable attributes have
1291 -- not been updated. If an attribute has been updated an error is generated.
1292 --
1293 -- Pre Conditions:
1294 -- g_old_rec has been populated with details of the values currently in
1295 -- the database.
1296 --
1297 -- In Arguments:
1298 -- p_rec has been populated with the updated values the user would like the
1299 -- record set to.
1300 --
1301 -- Post Success:
1302 -- Processing continues if all the non updateable attributes have not
1303 -- changed.
1304 --
1305 -- Post Failure:
1306 -- An application error is raised if any of the non updatable attributes
1307 -- have been altered.
1308 --
1309 -- {End Of Comments}
1310 -- ----------------------------------------------------------------------------
1311 Procedure chk_non_updateable_args
1312 (p_effective_date in date
1313 ,p_rec in pay_pur_shd.g_rec_type
1314 ) IS
1315 --
1316 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
1317 --
1318 Begin
1319
1320 hr_utility.set_location('Entering:'|| l_proc, 10);
1321 --
1322 -- Only proceed with the validation if a row exists for the current
1323 -- record in the HR Schema.
1324 --
1325 IF NOT pay_pur_shd.api_updating
1326 (p_user_row_id => p_rec.user_row_id
1327 ,p_effective_date => p_effective_date
1328 ,p_object_version_number => p_rec.object_version_number
1329 ) THEN
1330 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
1331 fnd_message.set_token('PROCEDURE ', l_proc);
1332 fnd_message.set_token('STEP ', '5');
1333 fnd_message.raise_error;
1334 END IF;
1335 --
1336 --
1337 if nvl(p_rec.user_table_id, hr_api.g_number) <>
1338 pay_pur_shd.g_old_rec.user_table_id then
1339 hr_api.argument_changed_error
1340 (p_api_name => l_proc
1341 ,p_argument => 'USER_TABLE_ID'
1342 ,p_base_table => pay_pur_shd.g_tab_nam
1343 );
1344 end if;
1345 --
1346 if nvl(p_rec.business_group_id, hr_api.g_number) <>
1347 nvl(pay_pur_shd.g_old_rec.business_group_id, hr_api.g_number) then
1348 hr_api.argument_changed_error
1349 (p_api_name => l_proc
1350 ,p_argument => 'BUSINESS_GROUP_ID'
1351 ,p_base_table => pay_pur_shd.g_tab_nam
1352 );
1353 end if;
1354 --
1355 if nvl(p_rec.legislation_code, hr_api.g_varchar2) <>
1356 nvl(pay_pur_shd.g_old_rec.legislation_code, hr_api.g_varchar2) then
1357 hr_api.argument_changed_error
1358 (p_api_name => l_proc
1359 ,p_argument => 'LEGISLATION_CODE'
1360 ,p_base_table => pay_pur_shd.g_tab_nam
1361 );
1362 end if;
1363 --
1364 hr_utility.set_location(' Leaving:'|| l_proc, 20);
1365
1366 End chk_non_updateable_args;
1367 --
1368 -- ----------------------------------------------------------------------------
1369 -- |--------------------------< dt_update_validate >--------------------------|
1370 -- ----------------------------------------------------------------------------
1371 -- {Start Of Comments}
1372 --
1373 -- Description:
1374 -- This procedure is used for referential integrity of datetracked
1375 -- parent entities when a datetrack update operation is taking place
1376 -- and where there is no cascading of update defined for this entity.
1377 --
1378 -- Prerequisites:
1379 -- This procedure is called from the update_validate.
1380 --
1381 -- In Parameters:
1382 --
1383 -- Post Success:
1384 -- Processing continues.
1385 --
1386 -- Post Failure:
1387 --
1388 -- Developer Implementation Notes:
1389 -- This procedure should not need maintenance unless the HR Schema model
1390 -- changes.
1391 --
1392 -- Access Status:
1393 -- Internal Row Handler Use Only.
1394 --
1395 -- {End Of Comments}
1396 -- ----------------------------------------------------------------------------
1397 Procedure dt_update_validate
1398 (p_datetrack_mode in varchar2
1399 ,p_validation_start_date in date
1400 ,p_validation_end_date in date
1401 ) Is
1402 --
1403 l_proc varchar2(72) := g_package||'dt_update_validate';
1404 --
1405 Begin
1406 --
1407 -- Ensure that the p_datetrack_mode argument is not null
1408 --
1409 hr_api.mandatory_arg_error
1410 (p_api_name => l_proc
1411 ,p_argument => 'datetrack_mode'
1412 ,p_argument_value => p_datetrack_mode
1413 );
1414 --
1415 -- Mode will be valid, as this is checked at the start of the upd.
1416 --
1417 -- Ensure the arguments are not null
1418 --
1419 hr_api.mandatory_arg_error
1420 (p_api_name => l_proc
1421 ,p_argument => 'validation_start_date'
1422 ,p_argument_value => p_validation_start_date
1423 );
1424 --
1425 hr_api.mandatory_arg_error
1426 (p_api_name => l_proc
1427 ,p_argument => 'validation_end_date'
1428 ,p_argument_value => p_validation_end_date
1429 );
1430 --
1431 --
1432 --
1433 Exception
1434 When Others Then
1435 --
1436 -- An unhandled or unexpected error has occurred which
1437 -- we must report
1438 --
1439 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
1440 fnd_message.set_token('PROCEDURE', l_proc);
1441 fnd_message.set_token('STEP','15');
1442 fnd_message.raise_error;
1443 End dt_update_validate;
1444 --
1445 -- ----------------------------------------------------------------------------
1446 -- |--------------------------< dt_delete_validate >--------------------------|
1447 -- ----------------------------------------------------------------------------
1448 -- {Start Of Comments}
1449 --
1450 -- Description:
1451 -- This procedure is used for referential integrity of datetracked
1452 -- child entities when either a datetrack DELETE or ZAP is in operation
1453 -- and where there is no cascading of delete defined for this entity.
1454 -- For the datetrack mode of DELETE or ZAP we must ensure that no
1455 -- datetracked child rows exist between the validation start and end
1456 -- dates.
1457 --
1458 -- Prerequisites:
1459 -- This procedure is called from the delete_validate.
1460 --
1461 -- In Parameters:
1462 --
1463 -- Post Success:
1464 -- Processing continues.
1465 --
1466 -- Post Failure:
1467 -- If a row exists by determining the returning Boolean value from the
1468 -- generic dt_api.rows_exist function then we must supply an error via
1469 -- the use of the local exception handler l_rows_exist.
1470 --
1471 -- Developer Implementation Notes:
1472 -- This procedure should not need maintenance unless the HR Schema model
1473 -- changes.
1474 --
1475 -- Access Status:
1476 -- Internal Row Handler Use Only.
1477 --
1478 -- {End Of Comments}
1479 -- ----------------------------------------------------------------------------
1480 Procedure dt_delete_validate
1481 (p_user_row_id in number
1482 ,p_datetrack_mode in varchar2
1483 ,p_validation_start_date in date
1484 ,p_validation_end_date in date
1485 ) Is
1486 --
1487 l_proc varchar2(72) := g_package||'dt_delete_validate';
1488 --
1489 Begin
1490 --
1491 -- Ensure that the p_datetrack_mode argument is not null
1492 --
1493 hr_api.mandatory_arg_error
1494 (p_api_name => l_proc
1495 ,p_argument => 'datetrack_mode'
1496 ,p_argument_value => p_datetrack_mode
1497 );
1498 --
1499 -- Only perform the validation if the datetrack mode is either
1500 -- DELETE or ZAP
1501 --
1502 If (p_datetrack_mode = hr_api.g_delete or
1503 p_datetrack_mode = hr_api.g_zap) then
1504 --
1505 --
1506 -- Ensure the arguments are not null
1507 --
1508 hr_api.mandatory_arg_error
1509 (p_api_name => l_proc
1510 ,p_argument => 'validation_start_date'
1511 ,p_argument_value => p_validation_start_date
1512 );
1513 --
1514 hr_api.mandatory_arg_error
1515 (p_api_name => l_proc
1516 ,p_argument => 'validation_end_date'
1517 ,p_argument_value => p_validation_end_date
1518 );
1519 --
1520 hr_api.mandatory_arg_error
1521 (p_api_name => l_proc
1522 ,p_argument => 'user_row_id'
1523 ,p_argument_value => p_user_row_id
1524 );
1525 --
1526 --
1527 --
1528 End If;
1529 --
1530 Exception
1531 When Others Then
1532 --
1533 -- An unhandled or unexpected error has occurred which
1534 -- we must report
1535 --
1536 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
1537 fnd_message.set_token('PROCEDURE', l_proc);
1538 fnd_message.set_token('STEP','15');
1539 fnd_message.raise_error;
1540 --
1541 End dt_delete_validate;
1542 --
1543 -- ----------------------------------------------------------------------------
1544 -- |----------------------< chk_startup_action >------------------------------|
1545 -- ----------------------------------------------------------------------------
1546 --
1547 -- Description:
1548 -- This procedure will check that the current action is allowed according
1549 -- to the current startup mode.
1550 --
1551 -- ----------------------------------------------------------------------------
1552 PROCEDURE chk_startup_action
1553 (p_insert IN boolean
1554 ,p_business_group_id IN number
1555 ,p_legislation_code IN varchar2
1556 ,p_legislation_subgroup IN varchar2 DEFAULT NULL) IS
1557 --
1558 BEGIN
1559 --
1560 -- Call the supporting procedure to check startup mode
1561
1562 IF (p_insert) THEN
1563
1564 if p_business_group_id is not null and p_legislation_code is not null then
1565 fnd_message.set_name('PAY', 'PAY_33179_BGLEG_INVALID');
1566 fnd_message.raise_error;
1567 end if;
1568
1569 hr_startup_data_api_support.chk_startup_action
1570 (p_generic_allowed => TRUE
1571 ,p_startup_allowed => TRUE
1572 ,p_user_allowed => TRUE
1573 ,p_business_group_id => p_business_group_id
1574 ,p_legislation_code => p_legislation_code
1575 ,p_legislation_subgroup => p_legislation_subgroup
1576 );
1577 ELSE
1578 hr_startup_data_api_support.chk_upd_del_startup_action
1579 (p_generic_allowed => TRUE
1580 ,p_startup_allowed => TRUE
1581 ,p_user_allowed => TRUE
1582 ,p_business_group_id => p_business_group_id
1583 ,p_legislation_code => p_legislation_code
1584 ,p_legislation_subgroup => p_legislation_subgroup
1585 );
1586 END IF;
1587 --
1588 END chk_startup_action;
1589 --
1590 -- ----------------------------------------------------------------------------
1591 -- |---------------------------< insert_validate >----------------------------|
1592 -- ----------------------------------------------------------------------------
1593 Procedure insert_validate
1594 (p_rec in out nocopy pay_pur_shd.g_rec_type
1595 ,p_effective_date in date
1596 ,p_datetrack_mode in varchar2
1597 ,p_validation_start_date in date
1598 ,p_validation_end_date in date
1599 ,p_disable_units_check in boolean
1600 ,p_disable_range_overlap_check in boolean
1601 ) is
1602 --
1603 l_proc varchar2(72) := g_package||'insert_validate';
1604 --
1605 Begin
1606 hr_utility.set_location('Entering:'||l_proc, 5);
1607
1608 --
1609 -- Clearing the Global variables since the record may have changed.
1610 --
1611 g_user_key_units := NULL;
1612 g_range_or_match := NULL;
1613 --
1614 --
1615 -- Call all supporting business operations
1616 --
1617 --
1618 chk_startup_action(true
1619 ,p_rec.business_group_id
1620 ,p_rec.legislation_code
1621 );
1622 IF hr_startup_data_api_support.g_startup_mode
1623 NOT IN ('GENERIC','STARTUP') THEN
1624 --
1625 -- Validate Important Attributes
1626 --
1627 hr_api.validate_bus_grp_id
1628 (p_business_group_id => p_rec.business_group_id
1629 ,p_associated_column1 => pay_pur_shd.g_tab_nam
1630 || '.BUSINESS_GROUP_ID');
1631 --
1632 -- after validating the set of important attributes,
1633 -- if Multiple Message Detection is enabled and at least
1634 -- one error has been found then abort further validation.
1635 --
1636 hr_multi_message.end_validation_set;
1637
1638 END IF;
1639 --
1640
1641 if hr_startup_data_api_support.g_startup_mode not in ('GENERIC','USER') then
1642
1643 --
1644 -- Validate Important Attributes
1645 --
1646 chk_legislation_code(p_legislation_code => p_rec.legislation_code);
1647 --
1648 hr_multi_message.end_validation_set;
1649
1650 end if;
1651 --
1652 --
1653 -- Validate Dependent Attributes
1654 --
1655
1656 chk_user_table_id
1657 (p_user_table_id => p_rec.user_table_id
1658 ,p_business_group_id => p_rec.business_group_id
1659 ,p_legislation_code => p_rec.legislation_code
1660 );
1661
1662 chk_row_low_range_or_name
1663 (p_user_row_id => p_rec.user_row_id
1664 ,p_user_table_id => p_rec.user_table_id
1665 ,p_row_low_range_or_name => p_rec.row_low_range_or_name
1666 ,p_object_version_number => p_rec.object_version_number
1667 ,p_disable_units_check => p_disable_units_check
1668 ,p_disable_range_overlap_check => p_disable_range_overlap_check
1669 ,p_business_group_id => p_rec.business_group_id
1670 ,p_legislation_code => p_rec.legislation_code
1671 ,p_effective_date => p_effective_date
1672 ,p_validation_start_date => p_validation_start_date
1673 ,p_validation_end_date => p_validation_end_date
1674 );
1675 --
1676 --
1677 chk_display_sequence( p_display_sequence => p_rec.display_sequence );
1678 --
1679 --
1680 chk_row_high_range
1681 ( p_row_high_range => p_rec.row_high_range
1685 ,p_effective_date => p_effective_date
1682 ,p_user_table_id => p_rec.user_table_id
1683 ,p_user_row_id => p_rec.user_row_id
1684 ,p_object_version_number => p_rec.object_version_number
1686 );
1687 --
1688 --
1689 chk_range
1690 ( p_user_row_id => p_rec.user_row_id
1691 ,p_user_table_id => p_rec.user_table_id
1692 ,p_row_low_range_or_name => p_rec.row_low_range_or_name
1693 ,p_row_high_range => p_rec.row_high_range
1694 ,p_disable_range_overlap_check => p_disable_range_overlap_check
1695 ,p_object_version_number => p_rec.object_version_number
1696 ,p_business_group_id => p_rec.business_group_id
1697 ,p_legislation_code => p_rec.legislation_code
1698 ,p_effective_date => p_effective_date
1699 ,p_validation_start_date => p_validation_start_date
1700 ,p_validation_end_date => p_validation_end_date
1701 );
1702 --
1703 --
1704 hr_utility.set_location(' Leaving:'||l_proc, 10);
1705 End insert_validate;
1706 --
1707 -- ----------------------------------------------------------------------------
1708 -- |---------------------------< update_validate >----------------------------|
1709 -- ----------------------------------------------------------------------------
1710 Procedure update_validate
1711 (p_rec in out nocopy pay_pur_shd.g_rec_type
1712 ,p_effective_date in date
1713 ,p_datetrack_mode in varchar2
1714 ,p_validation_start_date in date
1715 ,p_validation_end_date in date
1716 ,p_disable_units_check in boolean
1717 ,p_disable_range_overlap_check in boolean
1718 ) is
1719 --
1720 l_proc varchar2(72) := g_package||'update_validate';
1721 --
1722 Begin
1723 hr_utility.set_location('Entering:'||l_proc, 5);
1724 --
1725 --
1726 -- Clearing the Global variables since the record may have changed.
1727 --
1728 g_user_key_units := NULL;
1729 g_range_or_match := NULL;
1730 --
1731 --
1732 -- Call all supporting business operations
1733 --
1734 --
1735 chk_startup_action(false
1736 ,p_rec.business_group_id
1737 ,p_rec.legislation_code
1738 );
1739 IF hr_startup_data_api_support.g_startup_mode
1740 NOT IN ('GENERIC','STARTUP') THEN
1741 --
1742 -- Validate Important Attributes
1743 --
1744 hr_api.validate_bus_grp_id
1745 (p_business_group_id => p_rec.business_group_id
1746 ,p_associated_column1 => pay_pur_shd.g_tab_nam
1747 || '.BUSINESS_GROUP_ID');
1748 --
1749 -- After validating the set of important attributes,
1750 -- if Multiple Message Detection is enabled and at least
1751 -- one error has been found then abort further validation.
1752 --
1753 hr_multi_message.end_validation_set;
1754 END IF;
1755 --
1756 --
1757 -- Validate Dependent Attributes
1758 --
1759 -- Call the datetrack update integrity operation
1760 --
1761 dt_update_validate
1762 (p_datetrack_mode => p_datetrack_mode
1763 ,p_validation_start_date => p_validation_start_date
1764 ,p_validation_end_date => p_validation_end_date
1765 );
1766 --
1767 chk_non_updateable_args
1768 (p_effective_date => p_effective_date
1769 ,p_rec => p_rec
1770 );
1771 --
1772 --
1773 chk_row_low_range_or_name
1774 (p_user_row_id => p_rec.user_row_id
1775 ,p_user_table_id => p_rec.user_table_id
1776 ,p_row_low_range_or_name => p_rec.row_low_range_or_name
1777 ,p_object_version_number => p_rec.object_version_number
1778 ,p_disable_units_check => p_disable_units_check
1779 ,p_disable_range_overlap_check => p_disable_range_overlap_check
1780 ,p_business_group_id => p_rec.business_group_id
1781 ,p_legislation_code => p_rec.legislation_code
1782 ,p_effective_date => p_effective_date
1783 ,p_validation_start_date => p_validation_start_date
1784 ,p_validation_end_date => p_validation_end_date
1785 );
1786 --
1787 --
1788 chk_display_sequence( p_display_sequence => p_rec.display_sequence );
1789 --
1790 --
1791 chk_row_high_range
1792 ( p_row_high_range => p_rec.row_high_range
1793 ,p_user_table_id => p_rec.user_table_id
1794 ,p_user_row_id => p_rec.user_row_id
1795 ,p_object_version_number => p_rec.object_version_number
1796 ,p_effective_date => p_effective_date
1797 );
1798 --
1799 --
1800 chk_range
1801 ( p_user_row_id => p_rec.user_row_id
1802 ,p_user_table_id => p_rec.user_table_id
1803 ,p_row_low_range_or_name => p_rec.row_low_range_or_name
1804 ,p_row_high_range => p_rec.row_high_range
1805 ,p_disable_range_overlap_check => p_disable_range_overlap_check
1806 ,p_object_version_number => p_rec.object_version_number
1807 ,p_business_group_id => p_rec.business_group_id
1808 ,p_legislation_code => p_rec.legislation_code
1809 ,p_effective_date => p_effective_date
1810 ,p_validation_start_date => p_validation_start_date
1811 ,p_validation_end_date => p_validation_end_date
1812 );
1813 --
1814 --
1815 hr_utility.set_location(' Leaving:'||l_proc, 10);
1816 End update_validate;
1817 --
1818 -- ----------------------------------------------------------------------------
1819 -- |---------------------------< delete_validate >----------------------------|
1820 -- ----------------------------------------------------------------------------
1821 Procedure delete_validate
1822 (p_rec in pay_pur_shd.g_rec_type
1823 ,p_effective_date in date
1824 ,p_datetrack_mode in varchar2
1825 ,p_disable_range_overlap_check in boolean
1826 ,p_validation_start_date in date
1827 ,p_validation_end_date in date
1828 ) is
1829 --
1830 l_proc varchar2(72) := g_package||'delete_validate';
1831 --
1832 Begin
1833 hr_utility.set_location('Entering:'||l_proc, 5);
1834 --
1835 -- Clearing the Global variables since the record may have changed.
1836 --
1837 g_user_key_units := NULL;
1838 g_range_or_match := NULL;
1839 --
1840 --
1841 chk_startup_action(false
1842 ,pay_pur_shd.g_old_rec.business_group_id
1843 ,pay_pur_shd.g_old_rec.legislation_code
1844 );
1845 IF hr_startup_data_api_support.g_startup_mode
1846 NOT IN ('GENERIC','STARTUP') THEN
1847 --
1848 -- Validate Important Attributes
1849 --
1850 --
1851 -- After validating the set of important attributes,
1852 -- if Multiple Message Detection is enabled and at least
1853 -- one error has been found then abort further validation.
1854 --
1855 hr_multi_message.end_validation_set;
1856 END IF;
1857 --
1858 -- Call all supporting business operations
1859 --
1860 dt_delete_validate
1861 (p_datetrack_mode => p_datetrack_mode
1862 ,p_validation_start_date => p_validation_start_date
1863 ,p_validation_end_date => p_validation_end_date
1864 ,p_user_row_id => p_rec.user_row_id
1865 );
1866 --
1867 --
1868 chk_delete
1869 (p_user_table_id => pay_pur_shd.g_old_rec.user_table_id
1870 ,p_user_row_id => p_rec.user_row_id
1871 ,p_row_low_range_or_name => pay_pur_shd.g_old_rec.row_low_range_or_name
1872 ,p_row_high_range => pay_pur_shd.g_old_rec.row_high_range
1873 ,p_datetrack_mode => p_datetrack_mode
1874 ,p_business_group_id => pay_pur_shd.g_old_rec.business_group_id
1875 ,p_legislation_code => pay_pur_shd.g_old_rec.legislation_code
1876 ,p_disable_range_overlap_check => p_disable_range_overlap_check
1877 ,p_validation_start_date => p_validation_start_date
1878 ,p_validation_end_date => p_validation_end_date
1879 );
1880
1881 hr_utility.set_location(' Leaving:'||l_proc, 10);
1882 End delete_validate;
1883 --
1884 end pay_pur_bus;
[Home] [Help]