1 Package Body pay_pur_bus as
2 /* $Header: pypurrhi.pkb 120.1.12010000.2 2009/12/23 09:46:10 asnell ship $ */
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 -- bug 9234524 start
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 if ( l_range_or_match = 'M' or p_disable_range_overlap_check = FALSE ) then
666
667 open csr_unique_name;
668 fetch csr_unique_name into l_exists;
669
670 if csr_unique_name%found then
671 close csr_unique_name;
672 fnd_message.set_name( 'PAY' , 'PAY_7884_USER_TABLE_UNIQUE' );
673 fnd_message.raise_error ;
674 end if ;
675
676 close csr_unique_name;
677 end if;
678
679 end if;
680
681 hr_utility.set_location(' Leaving:'|| l_proc, 20);
682
683 exception
684
685 when app_exception.application_exception then
686 if hr_multi_message.exception_add
687 (p_associated_column1 => 'PAY_USER_ROWS_F.ROW_LOW_RANGE_OR_NAME') then
688 raise;
689 end if;
690
691 when others then
692 if csr_unique_name%isopen then
693 close csr_unique_name;
694 end if;
695 raise;
696
697 end chk_row_low_range_or_name ;
698 --
699 -- ----------------------------------------------------------------------------
700 -- |------------------------< chk_row_high_range >----------------------------|
701 -- ----------------------------------------------------------------------------
702 --
703 -- Description:
704 -- Validates that the row_high_range
705 -- 1. Is Mandatory for range tables.
706 -- 2. Is Ignored for match tables.
707 -- 3. Is numeric.
708 --
709 -- Pre-Requisites:
710 -- user_table_id must be validated
711 --
712 -- In Parameters:
713 -- p_user_row_id
714 -- p_user_table_id
715 -- p_row_high_range
716 -- p_object_version_number
717 -- p_effective_date
718 --
719 -- Post Success:
720 -- Processing continues if the row_high_range is valid.
721 --
722 -- Post Failure:
723 -- An application error is raised and processing is terminated if
724 -- the row_high_range is invalid.
725 --
726 -- Developer/Implementation Notes:
727 -- None
728 --
729 -- Access Status:
730 -- Internal Row Handler Use Only
731 --
732 procedure chk_row_high_range
733 ( p_row_high_range in out nocopy varchar2
734 ,p_user_table_id in number
735 ,p_user_row_id in number
736 ,p_object_version_number in number
737 ,p_effective_date in date
738 ) is
739 --
740 l_proc varchar2(100) := g_package || 'chk_row_high_range';
741 l_range_or_match PAY_USER_TABLES.RANGE_OR_MATCH%TYPE;
742 l_user_key_units PAY_USER_TABLES.USER_KEY_UNITS%TYPE;
743 l_exists varchar2(1);
744 l_result boolean;
745 --
746 begin
747
748 hr_utility.set_location('Entering:'|| l_proc, 10);
749
750 if hr_multi_message.no_exclusive_error
751 (p_check_column1 => 'PAY_USER_ROWS_F.USER_TABLE_ID'
752 ,p_associated_column1 => 'PAY_USER_ROWS_F.ROW_HIGH_RANGE'
753 ) and (
754 not pay_pur_shd.api_updating
755 (p_user_row_id => p_user_row_id
756 ,p_effective_date => p_effective_date
757 ,p_object_version_number => p_object_version_number
758 ) or
759 nvl(p_row_high_range, hr_api.g_varchar2) <>
760 nvl(pay_pur_shd.g_old_rec.row_high_range, hr_api.g_varchar2)
761 ) then
762 --
763 -- Ignore all validations for ROW_HIGH_RANGE for Exact Match User Tables.
764 --
765
766 l_user_key_units := return_user_key_units(p_user_table_id);
767 l_range_or_match := return_range_or_match(p_user_table_id);
768
769 if l_range_or_match = 'R' then
770
771 -- row_high_range is mandatory
772
773 hr_api.mandatory_arg_error
774 (p_api_name => l_proc
775 ,p_argument => 'ROW_HIGH_RANGE'
776 ,p_argument_value => p_row_high_range
777 );
778
779
780 l_result := chk_format( p_row_high_range,
781 l_user_key_units ) ;
782
783 if ( l_result = FALSE ) then
784 fnd_message.set_name ( 'PAY', 'PAY_34025_UT_RANGE_NOT_NUMERIC' );
785 fnd_message.raise_error;
786 end if;
787 end if;
788 end if;
789
790 hr_utility.set_location(' Leaving:'|| l_proc, 20);
791
792 exception
793
794 when app_exception.application_exception then
795 if hr_multi_message.exception_add
796 (p_associated_column1 => 'PAY_USER_ROWS_F.ROW_HIGH_RANGE') then
797 raise;
798 end if;
799
800 when others then
801 raise;
802
803 end chk_row_high_range ;
804 --
805 -- ----------------------------------------------------------------------------
806 -- |-----------------------------< chk_range >--------------------------------|
807 -- ----------------------------------------------------------------------------
808 --
809 -- Description:
810 -- Validates that the
811 -- 1. row_high_range >= row_low_range_or_name for range tables
812 -- 2. The range row_low_range_or_name -> row_high_range does not overlap
813 -- with other rows. (Conditional)
814 --
815 -- Pre-Requisites:
816 -- row_low_range_or_name and row_high_range must be validated.
817 --
818 -- In Parameters:
819 -- p_user_row_id
820 -- p_user_table_id
821 -- p_row_low_range_or_name
822 -- p_row_high_range
823 -- p_object_version_number
824 -- p_disable_range_overlap_check - User supplied flag which indicates
825 -- whether range overlap check has to be
826 -- carried out or not (Conditional)
827 -- p_legislation_code
828 -- p_business_group_id
829 -- p_effective_date
830 -- p_validation_start_date
831 -- p_validation_end_date
832 --
833 -- Post Success:
834 -- Processing continues if the range is valid.
835 --
836 -- Post Failure:
837 -- An application error is raised and processing is terminated if
838 -- the range is invalid.
839 --
840 -- Developer/Implementation Notes:
841 -- None
842 --
843 -- Access Status:
844 -- Internal Row Handler Use Only
845 --
846 procedure chk_range
847 ( p_user_row_id in number
848 ,p_user_table_id in number
849 ,p_row_low_range_or_name in varchar2
850 ,p_row_high_range in varchar2
851 ,p_disable_range_overlap_check in boolean
852 ,p_object_version_number in number
853 ,p_business_group_id in number
854 ,p_legislation_code in varchar2
855 ,p_effective_date in date
856 ,p_validation_start_date in date
857 ,p_validation_end_date in date
858 ) is
859 --
860
861 cursor csr_row_overlap is
862 select null
863 from pay_user_rows_f usr
864 where usr.user_table_id = p_user_table_id
865 and ( p_user_row_id is null
866 or ( p_user_row_id is not null and usr.user_row_id <> p_user_row_id ) )
867 and ( p_business_group_id is null
868 or ( p_business_group_id is not null and p_business_group_id = usr.business_group_id )
869 or ( p_business_group_id is not null and
870 usr.legislation_code is null and usr.business_group_id is null )
871 or ( p_business_group_id is not null and
872 usr.legislation_code = hr_api.return_legislation_code(p_business_group_id ) ))
873 and ( p_legislation_code is null
874 or ( p_legislation_code is not null and p_legislation_code = usr.legislation_code )
875 or ( p_legislation_code is not null and
876 usr.legislation_code is null and usr.business_group_id is null)
877 or ( p_legislation_code is not null and
878 p_legislation_code = hr_api.return_legislation_code(usr.business_group_id) ))
879 and (fnd_number.canonical_to_number(p_row_low_range_or_name) between
880 fnd_number.canonical_to_number(usr.row_low_range_or_name) and fnd_number.canonical_to_number(usr.row_high_range)
881 or (fnd_number.canonical_to_number(p_row_high_range) between
882 fnd_number.canonical_to_number(usr.row_low_range_or_name) and fnd_number.canonical_to_number(usr.row_high_range))
883 or (fnd_number.canonical_to_number(usr.row_low_range_or_name) between
884 fnd_number.canonical_to_number(p_row_low_range_or_name) and fnd_number.canonical_to_number(p_row_high_range))
885 or (fnd_number.canonical_to_number(usr.row_high_range) between
886 fnd_number.canonical_to_number(p_row_low_range_or_name) and fnd_number.canonical_to_number(p_row_high_range)))
887 and ( usr.effective_start_date <= p_validation_end_date and
888 usr.effective_end_date >= p_validation_start_date );
889
890 l_proc varchar2(100) := g_package || 'chk_range';
891 l_range_or_match PAY_USER_TABLES.RANGE_OR_MATCH%TYPE;
892 l_user_key_units PAY_USER_TABLES.USER_KEY_UNITS%TYPE;
893 l_exists varchar2(1);
894 l_result boolean;
895 l_prod_status varchar2(1);
896 l_ghr_installed varchar2(1);
897 l_industry varchar2(1);
898 l_oracle_scheema varchar2(30);
899
900 begin
901
902 hr_utility.set_location('Entering:'|| l_proc, 10);
903
904 if hr_multi_message.no_exclusive_error
905 ( p_check_column1 => 'PAY_USER_ROWS_F.ROW_LOW_RANGE_OR_NAME'
906 ,p_check_column2 => 'PAY_USER_ROWS_F.ROW_HIGH_RANGE'
907 ,p_associated_column1 => 'PAY_USER_ROWS_F.ROW_LOW_RANGE_OR_NAME'
908 ,p_associated_column2 => 'PAY_USER_ROWS_F.ROW_HIGH_RANGE'
909 ) and (
910 not pay_pur_shd.api_updating
911 (p_user_row_id => p_user_row_id
912 ,p_effective_date => p_effective_date
913 ,p_object_version_number => p_object_version_number
914 ) or (
915 nvl(p_row_low_range_or_name, hr_api.g_varchar2) <>
916 nvl(pay_pur_shd.g_old_rec.row_low_range_or_name, hr_api.g_varchar2) or
917 nvl(p_row_high_range, hr_api.g_varchar2) <>
918 nvl(pay_pur_shd.g_old_rec.row_high_range, hr_api.g_varchar2))
919 ) then
920
921 if p_disable_range_overlap_check is null then
922 fnd_message.set_name('PAY', 'HR_7207_API_MANDATORY_ARG');
923 fnd_message.set_token('API_NAME', l_proc);
924 fnd_message.set_token('ARGUMENT','DISABLE_RANGE_OVERLAP_CHECK');
925 fnd_message.raise_error;
926 end if;
927
928 -- Validation required only for Range Match
929
930 l_range_or_match := return_range_or_match(p_user_table_id);
931
932 if l_range_or_match = 'R' then
933
934 -- Bug 3832215. Convert row_low_range_or_name and row_high_range
935 -- from canonical to number before compare.
936
937 if fnd_number.canonical_to_number(p_row_high_range) <
938 fnd_number.canonical_to_number(p_row_low_range_or_name) then
939 fnd_message.set_name('PAY','PAY_33178_RANGE_INVALID');
940 fnd_message.raise_error ;
941 end if;
942
943
944 l_result := fnd_installation.get_app_info ( 'GHR',
945 l_prod_status,
946 l_industry,
947 l_oracle_scheema );
948
949 if ( l_prod_status = 'I' ) then
950 l_ghr_installed := 'Y';
951 else
952 l_ghr_installed := 'N';
953 end if;
954
955 -- bug 9234524 start
956 -- if ( ( l_ghr_installed = 'N' )
957 -- or ( l_ghr_installed = 'Y' and p_disable_range_overlap_check = FALSE) ) then
958 if ( p_disable_range_overlap_check = FALSE ) then
959
960 open csr_row_overlap;
961 fetch csr_row_overlap into l_exists;
962
963 if csr_row_overlap%found then
964 close csr_row_overlap;
965 fnd_message.set_name('PER','PER_34003_USER_ROW_OVERLAP');
966 fnd_message.raise_error ;
967 end if ;
968
969 close csr_row_overlap;
970 end if;
971 end if;
972 end if;
973
974 hr_utility.set_location(' Leaving:'|| l_proc, 20);
975
976 exception
977
978 when app_exception.application_exception then
979 if hr_multi_message.exception_add
980 (p_associated_column1 => 'PAY_USER_ROWS_F.ROW_LOW_RANGE_OR_NAME' ,
981 p_associated_column2 => 'PAY_USER_ROWS_F.ROW_HIGH_RANGE' ) then
982 raise;
983 end if;
984
985 when others then
986 if csr_row_overlap%isopen then
987 close csr_row_overlap;
988 end if;
989 raise;
990 end chk_range;
991 --
992 --
993 -- ----------------------------------------------------------------------------
994 -- |-----------------------------< chk_display_sequence >---------------------|
995 -- ----------------------------------------------------------------------------
996 --
997 -- Description:
998 -- Validates that the display_sequence is numeric
999 --
1000 -- Pre-Requisites:
1001 -- None
1002 --
1003 -- In Parameters:
1004 -- p_display_sequence
1005 --
1006 -- Post Success:
1007 -- Processing continues if the display_sequence is valid.
1008 --
1009 -- Post Failure:
1010 -- An application error is raised and processing is terminated if
1011 -- the display_sequence is invalid.
1012 --
1013 -- Developer/Implementation Notes:
1014 -- None
1015 --
1016 -- Access Status:
1017 -- Internal Row Handler Use Only
1018 --
1019 procedure chk_display_sequence
1020 ( p_display_sequence in number )
1021 is
1022 l_proc varchar2(100) := g_package || 'chk_display_sequence';
1023 --
1024 Begin
1025
1026 hr_utility.set_location('Entering:'|| l_proc, 10);
1027 /* Bug fix: 4661747 : Added the if condition to this call */
1028 if(p_display_sequence is not NULL) then
1029 hr_dbchkfmt.is_db_format( p_display_sequence , 'DISPLAY_SEQUENCE' , 'I' );
1030 end if;
1031
1032 hr_utility.set_location(' Leaving:'|| l_proc, 20);
1033
1034 Exception
1035
1036 when app_exception.application_exception then
1037 if hr_multi_message.exception_add
1038 (p_associated_column1 => 'PAY_USER_ROWS_F.DISPLAY_SEQUENCE' ) then
1039 raise;
1040 end if;
1041
1042 when others then
1043 raise;
1044
1045 end chk_display_sequence;
1046 --
1047 -- ----------------------------------------------------------------------------
1048 -- |------------------------------< chk_delete >------------------------------|
1049 -- ----------------------------------------------------------------------------
1050 --
1051 -- Description:
1052 -- Validates that the
1053 -- 1. For Delete Mode there are no child rows on or after
1054 -- validation_start_date
1055 -- 2. For Zap Mode there are no child rows at all.
1056 -- 3. For DELETE_NEXT_CHANGE and FUTURE_CHANGE modes deletion
1057 -- will not violate the uniqueness or overlap constraints.(Conditional)
1058 --
1059 -- Pre-Requisites:
1060 -- None.
1061 --
1062 -- In Parameters:
1063 -- p_user_row_id
1064 -- p_user_table_id
1065 -- p_row_low_range_or_name
1066 -- p_row_high_range
1067 -- p_datetrack_mode
1068 -- p_disable_range_overlap_check - User supplied flag which indicates
1069 -- whether range overlap check has to be
1070 -- carried out or not (Conditional)
1071 -- p_legislation_code
1072 -- p_business_group_id
1073 -- p_validation_start_date
1074 -- p_validation_end_date
1075 --
1076 -- Post Success:
1077 -- Processing continues if the deletion is valid.
1078 --
1079 -- Post Failure:
1080 -- An application error is raised and processing is terminated if
1081 -- the deletion is invalid.
1082 --
1083 -- Developer/Implementation Notes:
1084 -- None
1085 --
1086 -- Access Status:
1087 -- Internal Row Handler Use Only
1088 --
1089 procedure chk_delete
1090 (p_user_table_id in number
1091 ,p_user_row_id in number
1092 ,p_row_low_range_or_name in varchar2
1093 ,p_row_high_range in varchar2
1094 ,p_datetrack_mode in varchar2
1095 ,p_business_group_id in number
1096 ,p_legislation_code in varchar2
1097 ,p_disable_range_overlap_check in boolean
1098 ,p_validation_start_date in date
1099 ,p_validation_end_date in date
1100 )is
1101 --
1102 cursor csr_unique_name is
1103 select null
1104 from pay_user_rows_f usr
1105 where usr.user_table_id = p_user_table_id
1106 and upper(usr.row_low_range_or_name) = upper(p_row_low_range_or_name)
1107 and ( p_user_row_id is null
1108 or ( p_user_row_id is not null and usr.user_row_id <> p_user_row_id ) )
1109 and ( p_business_group_id is null
1110 or ( p_business_group_id is not null and p_business_group_id = usr.business_group_id )
1111 or ( p_business_group_id is not null and
1112 usr.legislation_code is null and usr.business_group_id is null )
1113 or ( p_business_group_id is not null and
1114 usr.legislation_code = hr_api.return_legislation_code(p_business_group_id ) ))
1115 and ( p_legislation_code is null
1116 or ( p_legislation_code is not null and p_legislation_code = usr.legislation_code )
1117 or ( p_legislation_code is not null and
1118 usr.legislation_code is null and usr.business_group_id is null)
1119 or ( p_legislation_code is not null and
1120 p_legislation_code = hr_api.return_legislation_code(usr.business_group_id ) ))
1121 and ( usr.effective_start_date <= p_validation_end_date and
1122 usr.effective_end_date >= p_validation_start_date );
1123
1124 cursor csr_row_overlap is
1125 select null
1126 from pay_user_rows_f usr
1127 where usr.user_table_id = p_user_table_id
1128 and ( p_user_row_id is null
1129 or ( p_user_row_id is not null and usr.user_row_id <> p_user_row_id ) )
1130 and ( p_business_group_id is null
1131 or ( p_business_group_id is not null and p_business_group_id = usr.business_group_id )
1132 or ( p_business_group_id is not null and
1133 usr.legislation_code is null and usr.business_group_id is null )
1134 or ( p_business_group_id is not null and
1135 usr.legislation_code = hr_api.return_legislation_code(p_business_group_id ) ))
1136 and ( p_legislation_code is null
1137 or ( p_legislation_code is not null and p_legislation_code = usr.legislation_code )
1138 or ( p_legislation_code is not null and
1139 usr.legislation_code is null and usr.business_group_id is null)
1140 or ( p_legislation_code is not null and
1141 p_legislation_code = hr_api.return_legislation_code(usr.business_group_id ) ))
1142 and (fnd_number.canonical_to_number(p_row_low_range_or_name) between
1143 fnd_number.canonical_to_number(usr.row_low_range_or_name) and fnd_number.canonical_to_number(usr.row_high_range)
1144 or (fnd_number.canonical_to_number(p_row_high_range) between
1145 fnd_number.canonical_to_number(usr.row_low_range_or_name) and fnd_number.canonical_to_number(usr.row_high_range))
1146 or (fnd_number.canonical_to_number(usr.row_low_range_or_name) between
1147 fnd_number.canonical_to_number(p_row_low_range_or_name) and fnd_number.canonical_to_number(p_row_high_range))
1148 or (fnd_number.canonical_to_number(usr.row_high_range) between
1149 fnd_number.canonical_to_number(p_row_low_range_or_name) and fnd_number.canonical_to_number(p_row_high_range)))
1150 and ( usr.effective_start_date <= p_validation_end_date and
1151 usr.effective_end_date >= p_validation_start_date );
1152
1153 cursor csr_zap_mode is
1154 select null
1155 from pay_user_column_instances_f
1156 where user_row_id = p_user_row_id ;
1157
1158 cursor csr_delete_mode is
1159 select null
1160 from pay_user_column_instances_f
1161 where user_row_id = p_user_row_id
1162 and effective_end_date >= p_validation_start_date ;
1163
1164
1165 l_exists varchar2(1);
1166 l_proc varchar2(100) := g_package || 'chk_delete';
1167 l_result boolean;
1168 l_range_or_match PAY_USER_TABLES.RANGE_OR_MATCH%TYPE;
1169 l_prod_status varchar2(1);
1170 l_ghr_installed varchar2(1);
1171 l_industry varchar2(1);
1172 l_oracle_scheema varchar2(30);
1173
1174 --
1175 Begin
1176
1177 hr_utility.set_location('Entering:'|| l_proc, 10);
1178
1179 if p_disable_range_overlap_check is null then
1180 fnd_message.set_name('PAY', 'HR_7207_API_MANDATORY_ARG');
1181 fnd_message.set_token('API_NAME', l_proc);
1182 fnd_message.set_token('ARGUMENT','DISABLE_RANGE_OVERLAP_CHECK');
1183 fnd_message.raise_error;
1184 end if;
1185
1186 if p_datetrack_mode = hr_api.g_delete then
1187
1188 open csr_delete_mode;
1189 fetch csr_delete_mode into l_exists;
1190 if csr_delete_mode%found then
1191 close csr_delete_mode;
1192 fnd_message.set_name( 'PAY', 'PAY_6982_USERTAB_END_VALUES' );
1193 fnd_message.raise_error;
1194 end if;
1195 close csr_delete_mode;
1196
1197 elsif p_datetrack_mode = hr_api.g_zap then
1198
1199 open csr_zap_mode;
1200 fetch csr_zap_mode into l_exists ;
1201 if csr_zap_mode%found then
1202 close csr_zap_mode;
1203 fnd_message.set_name( 'PAY', 'HR_6980_USERTAB_VALUES_FIRST' ) ;
1204 fnd_message.set_token( 'ROWCOL' , 'row' ) ;
1205 fnd_message.raise_error ;
1206 end if ;
1207 close csr_zap_mode;
1208
1209 elsif p_datetrack_mode in (hr_api.g_future_change,hr_api.g_delete_next_change) then
1210
1211
1212 l_result := fnd_installation.get_app_info ( 'GHR',
1213 l_prod_status,
1214 l_industry,
1215 l_oracle_scheema );
1216
1217 if ( l_prod_status = 'I' ) then
1218 l_ghr_installed := 'Y';
1219 else
1220 l_ghr_installed := 'N';
1221 end if;
1222
1223 l_range_or_match := return_range_or_match(p_user_table_id);
1224
1225 if ( l_ghr_installed = 'N'
1226 or ( l_ghr_installed = 'Y' and l_range_or_match = 'M' )
1227 or ( l_ghr_installed = 'Y' and p_disable_range_overlap_check = FALSE ) ) then
1228
1229 open csr_unique_name;
1230 fetch csr_unique_name into l_exists;
1231
1232 if csr_unique_name%found then
1233 close csr_unique_name;
1234 fnd_message.set_name ('PAY','HR_72033_CANNOT_DNC_RECORD');
1235 fnd_message.raise_error ;
1236 end if;
1237 close csr_unique_name;
1238 end if;
1239
1240 if ( l_range_or_match = 'R'
1241 and ( l_ghr_installed = 'N'
1242 or ( l_ghr_installed = 'Y' and p_disable_range_overlap_check = FALSE))) then
1243
1244 open csr_row_overlap;
1245 fetch csr_row_overlap into l_exists;
1246
1247 if csr_row_overlap%found then
1248 close csr_row_overlap;
1249 fnd_message.set_name ('PAY','HR_72033_CANNOT_DNC_RECORD');
1250 fnd_message.raise_error ;
1251 end if ;
1252
1253 close csr_row_overlap;
1254 end if;
1255 end if;
1256
1257 hr_utility.set_location(' Leaving:'|| l_proc, 20);
1258 Exception
1259
1260 when app_exception.application_exception then
1261 if hr_multi_message.exception_add
1262 (p_associated_column1 => 'PAY_USER_ROWS_F.USER_ROW_ID') then
1263 raise;
1264 end if;
1265
1266 when others then
1267 if csr_unique_name%isopen then
1268 close csr_unique_name;
1269 end if;
1270
1271 if csr_row_overlap%isopen then
1272 close csr_row_overlap;
1273 end if;
1274
1275 if csr_zap_mode%isopen then
1276 close csr_zap_mode;
1277 end if;
1278
1279 if csr_delete_mode%isopen then
1280 close csr_delete_mode;
1281 end if;
1282
1283 raise;
1284
1285 End chk_delete;
1286 --
1287 -- ----------------------------------------------------------------------------
1288 -- |-----------------------< chk_non_updateable_args >------------------------|
1289 -- ----------------------------------------------------------------------------
1290 -- {Start Of Comments}
1291 --
1292 -- Description:
1293 -- This procedure is used to ensure that non updateable attributes have
1294 -- not been updated. If an attribute has been updated an error is generated.
1295 --
1296 -- Pre Conditions:
1297 -- g_old_rec has been populated with details of the values currently in
1298 -- the database.
1299 --
1300 -- In Arguments:
1301 -- p_rec has been populated with the updated values the user would like the
1302 -- record set to.
1303 --
1304 -- Post Success:
1305 -- Processing continues if all the non updateable attributes have not
1306 -- changed.
1307 --
1308 -- Post Failure:
1309 -- An application error is raised if any of the non updatable attributes
1310 -- have been altered.
1311 --
1312 -- {End Of Comments}
1313 -- ----------------------------------------------------------------------------
1314 Procedure chk_non_updateable_args
1315 (p_effective_date in date
1316 ,p_rec in pay_pur_shd.g_rec_type
1317 ) IS
1318 --
1319 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
1320 --
1321 Begin
1322
1323 hr_utility.set_location('Entering:'|| l_proc, 10);
1324 --
1325 -- Only proceed with the validation if a row exists for the current
1326 -- record in the HR Schema.
1327 --
1328 IF NOT pay_pur_shd.api_updating
1329 (p_user_row_id => p_rec.user_row_id
1330 ,p_effective_date => p_effective_date
1331 ,p_object_version_number => p_rec.object_version_number
1332 ) THEN
1333 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
1334 fnd_message.set_token('PROCEDURE ', l_proc);
1335 fnd_message.set_token('STEP ', '5');
1336 fnd_message.raise_error;
1337 END IF;
1338 --
1339 --
1340 if nvl(p_rec.user_table_id, hr_api.g_number) <>
1341 pay_pur_shd.g_old_rec.user_table_id then
1342 hr_api.argument_changed_error
1343 (p_api_name => l_proc
1344 ,p_argument => 'USER_TABLE_ID'
1345 ,p_base_table => pay_pur_shd.g_tab_nam
1346 );
1347 end if;
1348 --
1349 if nvl(p_rec.business_group_id, hr_api.g_number) <>
1350 nvl(pay_pur_shd.g_old_rec.business_group_id, hr_api.g_number) then
1351 hr_api.argument_changed_error
1352 (p_api_name => l_proc
1353 ,p_argument => 'BUSINESS_GROUP_ID'
1354 ,p_base_table => pay_pur_shd.g_tab_nam
1355 );
1356 end if;
1357 --
1358 if nvl(p_rec.legislation_code, hr_api.g_varchar2) <>
1359 nvl(pay_pur_shd.g_old_rec.legislation_code, hr_api.g_varchar2) then
1360 hr_api.argument_changed_error
1361 (p_api_name => l_proc
1362 ,p_argument => 'LEGISLATION_CODE'
1363 ,p_base_table => pay_pur_shd.g_tab_nam
1364 );
1365 end if;
1366 --
1367 hr_utility.set_location(' Leaving:'|| l_proc, 20);
1368
1369 End chk_non_updateable_args;
1370 --
1371 -- ----------------------------------------------------------------------------
1372 -- |--------------------------< dt_update_validate >--------------------------|
1373 -- ----------------------------------------------------------------------------
1374 -- {Start Of Comments}
1375 --
1376 -- Description:
1377 -- This procedure is used for referential integrity of datetracked
1378 -- parent entities when a datetrack update operation is taking place
1379 -- and where there is no cascading of update defined for this entity.
1380 --
1381 -- Prerequisites:
1382 -- This procedure is called from the update_validate.
1383 --
1384 -- In Parameters:
1385 --
1386 -- Post Success:
1387 -- Processing continues.
1388 --
1389 -- Post Failure:
1390 --
1391 -- Developer Implementation Notes:
1392 -- This procedure should not need maintenance unless the HR Schema model
1393 -- changes.
1394 --
1395 -- Access Status:
1396 -- Internal Row Handler Use Only.
1397 --
1398 -- {End Of Comments}
1399 -- ----------------------------------------------------------------------------
1400 Procedure dt_update_validate
1401 (p_datetrack_mode in varchar2
1402 ,p_validation_start_date in date
1403 ,p_validation_end_date in date
1404 ) Is
1405 --
1406 l_proc varchar2(72) := g_package||'dt_update_validate';
1407 --
1408 Begin
1409 --
1410 -- Ensure that the p_datetrack_mode argument is not null
1411 --
1412 hr_api.mandatory_arg_error
1413 (p_api_name => l_proc
1414 ,p_argument => 'datetrack_mode'
1415 ,p_argument_value => p_datetrack_mode
1416 );
1417 --
1418 -- Mode will be valid, as this is checked at the start of the upd.
1419 --
1420 -- Ensure the arguments are not null
1421 --
1422 hr_api.mandatory_arg_error
1423 (p_api_name => l_proc
1424 ,p_argument => 'validation_start_date'
1425 ,p_argument_value => p_validation_start_date
1426 );
1427 --
1428 hr_api.mandatory_arg_error
1429 (p_api_name => l_proc
1430 ,p_argument => 'validation_end_date'
1431 ,p_argument_value => p_validation_end_date
1432 );
1433 --
1434 --
1435 --
1436 Exception
1437 When Others Then
1438 --
1439 -- An unhandled or unexpected error has occurred which
1440 -- we must report
1441 --
1442 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
1443 fnd_message.set_token('PROCEDURE', l_proc);
1444 fnd_message.set_token('STEP','15');
1445 fnd_message.raise_error;
1446 End dt_update_validate;
1447 --
1448 -- ----------------------------------------------------------------------------
1449 -- |--------------------------< dt_delete_validate >--------------------------|
1450 -- ----------------------------------------------------------------------------
1451 -- {Start Of Comments}
1452 --
1453 -- Description:
1454 -- This procedure is used for referential integrity of datetracked
1455 -- child entities when either a datetrack DELETE or ZAP is in operation
1456 -- and where there is no cascading of delete defined for this entity.
1457 -- For the datetrack mode of DELETE or ZAP we must ensure that no
1458 -- datetracked child rows exist between the validation start and end
1459 -- dates.
1460 --
1461 -- Prerequisites:
1462 -- This procedure is called from the delete_validate.
1463 --
1464 -- In Parameters:
1465 --
1466 -- Post Success:
1467 -- Processing continues.
1468 --
1469 -- Post Failure:
1470 -- If a row exists by determining the returning Boolean value from the
1471 -- generic dt_api.rows_exist function then we must supply an error via
1472 -- the use of the local exception handler l_rows_exist.
1473 --
1474 -- Developer Implementation Notes:
1475 -- This procedure should not need maintenance unless the HR Schema model
1476 -- changes.
1477 --
1478 -- Access Status:
1479 -- Internal Row Handler Use Only.
1480 --
1481 -- {End Of Comments}
1482 -- ----------------------------------------------------------------------------
1483 Procedure dt_delete_validate
1484 (p_user_row_id in number
1485 ,p_datetrack_mode in varchar2
1486 ,p_validation_start_date in date
1487 ,p_validation_end_date in date
1488 ) Is
1489 --
1490 l_proc varchar2(72) := g_package||'dt_delete_validate';
1491 --
1492 Begin
1493 --
1494 -- Ensure that the p_datetrack_mode argument is not null
1495 --
1496 hr_api.mandatory_arg_error
1497 (p_api_name => l_proc
1498 ,p_argument => 'datetrack_mode'
1499 ,p_argument_value => p_datetrack_mode
1500 );
1501 --
1502 -- Only perform the validation if the datetrack mode is either
1503 -- DELETE or ZAP
1504 --
1505 If (p_datetrack_mode = hr_api.g_delete or
1506 p_datetrack_mode = hr_api.g_zap) then
1507 --
1508 --
1509 -- Ensure the arguments are not null
1510 --
1511 hr_api.mandatory_arg_error
1512 (p_api_name => l_proc
1513 ,p_argument => 'validation_start_date'
1514 ,p_argument_value => p_validation_start_date
1515 );
1516 --
1517 hr_api.mandatory_arg_error
1518 (p_api_name => l_proc
1519 ,p_argument => 'validation_end_date'
1520 ,p_argument_value => p_validation_end_date
1521 );
1522 --
1523 hr_api.mandatory_arg_error
1524 (p_api_name => l_proc
1525 ,p_argument => 'user_row_id'
1526 ,p_argument_value => p_user_row_id
1527 );
1528 --
1529 --
1530 --
1531 End If;
1532 --
1533 Exception
1534 When Others Then
1535 --
1536 -- An unhandled or unexpected error has occurred which
1537 -- we must report
1538 --
1539 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
1540 fnd_message.set_token('PROCEDURE', l_proc);
1541 fnd_message.set_token('STEP','15');
1542 fnd_message.raise_error;
1543 --
1544 End dt_delete_validate;
1545 --
1546 -- ----------------------------------------------------------------------------
1547 -- |----------------------< chk_startup_action >------------------------------|
1548 -- ----------------------------------------------------------------------------
1549 --
1550 -- Description:
1551 -- This procedure will check that the current action is allowed according
1552 -- to the current startup mode.
1553 --
1554 -- ----------------------------------------------------------------------------
1555 PROCEDURE chk_startup_action
1556 (p_insert IN boolean
1557 ,p_business_group_id IN number
1558 ,p_legislation_code IN varchar2
1559 ,p_legislation_subgroup IN varchar2 DEFAULT NULL) IS
1560 --
1561 BEGIN
1562 --
1563 -- Call the supporting procedure to check startup mode
1564
1565 IF (p_insert) THEN
1566
1567 if p_business_group_id is not null and p_legislation_code is not null then
1568 fnd_message.set_name('PAY', 'PAY_33179_BGLEG_INVALID');
1569 fnd_message.raise_error;
1570 end if;
1571
1572 hr_startup_data_api_support.chk_startup_action
1573 (p_generic_allowed => TRUE
1574 ,p_startup_allowed => TRUE
1575 ,p_user_allowed => TRUE
1576 ,p_business_group_id => p_business_group_id
1577 ,p_legislation_code => p_legislation_code
1578 ,p_legislation_subgroup => p_legislation_subgroup
1579 );
1580 ELSE
1581 hr_startup_data_api_support.chk_upd_del_startup_action
1582 (p_generic_allowed => TRUE
1583 ,p_startup_allowed => TRUE
1584 ,p_user_allowed => TRUE
1585 ,p_business_group_id => p_business_group_id
1586 ,p_legislation_code => p_legislation_code
1587 ,p_legislation_subgroup => p_legislation_subgroup
1588 );
1589 END IF;
1590 --
1591 END chk_startup_action;
1592 --
1593 -- ----------------------------------------------------------------------------
1594 -- |---------------------------< insert_validate >----------------------------|
1595 -- ----------------------------------------------------------------------------
1596 Procedure insert_validate
1597 (p_rec in out nocopy pay_pur_shd.g_rec_type
1598 ,p_effective_date in date
1599 ,p_datetrack_mode in varchar2
1600 ,p_validation_start_date in date
1601 ,p_validation_end_date in date
1602 ,p_disable_units_check in boolean
1603 ,p_disable_range_overlap_check in boolean
1604 ) is
1605 --
1606 l_proc varchar2(72) := g_package||'insert_validate';
1607 --
1608 Begin
1609 hr_utility.set_location('Entering:'||l_proc, 5);
1610
1611 --
1612 -- Clearing the Global variables since the record may have changed.
1613 --
1614 g_user_key_units := NULL;
1615 g_range_or_match := NULL;
1616 --
1617 --
1618 -- Call all supporting business operations
1619 --
1620 --
1621 chk_startup_action(true
1622 ,p_rec.business_group_id
1623 ,p_rec.legislation_code
1624 );
1625 IF hr_startup_data_api_support.g_startup_mode
1626 NOT IN ('GENERIC','STARTUP') THEN
1627 --
1628 -- Validate Important Attributes
1629 --
1630 hr_api.validate_bus_grp_id
1631 (p_business_group_id => p_rec.business_group_id
1632 ,p_associated_column1 => pay_pur_shd.g_tab_nam
1633 || '.BUSINESS_GROUP_ID');
1634 --
1635 -- after validating the set of important attributes,
1636 -- if Multiple Message Detection is enabled and at least
1637 -- one error has been found then abort further validation.
1638 --
1639 hr_multi_message.end_validation_set;
1640
1641 END IF;
1642 --
1643
1644 if hr_startup_data_api_support.g_startup_mode not in ('GENERIC','USER') then
1645
1646 --
1647 -- Validate Important Attributes
1648 --
1649 chk_legislation_code(p_legislation_code => p_rec.legislation_code);
1650 --
1651 hr_multi_message.end_validation_set;
1652
1653 end if;
1654 --
1655 --
1656 -- Validate Dependent Attributes
1657 --
1658
1659 chk_user_table_id
1660 (p_user_table_id => p_rec.user_table_id
1661 ,p_business_group_id => p_rec.business_group_id
1662 ,p_legislation_code => p_rec.legislation_code
1663 );
1664
1665 chk_row_low_range_or_name
1666 (p_user_row_id => p_rec.user_row_id
1667 ,p_user_table_id => p_rec.user_table_id
1668 ,p_row_low_range_or_name => p_rec.row_low_range_or_name
1669 ,p_object_version_number => p_rec.object_version_number
1670 ,p_disable_units_check => p_disable_units_check
1671 ,p_disable_range_overlap_check => p_disable_range_overlap_check
1672 ,p_business_group_id => p_rec.business_group_id
1673 ,p_legislation_code => p_rec.legislation_code
1674 ,p_effective_date => p_effective_date
1675 ,p_validation_start_date => p_validation_start_date
1676 ,p_validation_end_date => p_validation_end_date
1677 );
1678 --
1679 --
1680 chk_display_sequence( p_display_sequence => p_rec.display_sequence );
1681 --
1682 --
1683 chk_row_high_range
1684 ( p_row_high_range => p_rec.row_high_range
1685 ,p_user_table_id => p_rec.user_table_id
1686 ,p_user_row_id => p_rec.user_row_id
1687 ,p_object_version_number => p_rec.object_version_number
1688 ,p_effective_date => p_effective_date
1689 );
1690 --
1691 --
1692 chk_range
1693 ( p_user_row_id => p_rec.user_row_id
1694 ,p_user_table_id => p_rec.user_table_id
1695 ,p_row_low_range_or_name => p_rec.row_low_range_or_name
1696 ,p_row_high_range => p_rec.row_high_range
1697 ,p_disable_range_overlap_check => p_disable_range_overlap_check
1698 ,p_object_version_number => p_rec.object_version_number
1699 ,p_business_group_id => p_rec.business_group_id
1700 ,p_legislation_code => p_rec.legislation_code
1701 ,p_effective_date => p_effective_date
1702 ,p_validation_start_date => p_validation_start_date
1703 ,p_validation_end_date => p_validation_end_date
1704 );
1705 --
1706 --
1707 hr_utility.set_location(' Leaving:'||l_proc, 10);
1708 End insert_validate;
1709 --
1710 -- ----------------------------------------------------------------------------
1711 -- |---------------------------< update_validate >----------------------------|
1712 -- ----------------------------------------------------------------------------
1713 Procedure update_validate
1714 (p_rec in out nocopy pay_pur_shd.g_rec_type
1715 ,p_effective_date in date
1716 ,p_datetrack_mode in varchar2
1717 ,p_validation_start_date in date
1718 ,p_validation_end_date in date
1719 ,p_disable_units_check in boolean
1720 ,p_disable_range_overlap_check in boolean
1721 ) is
1722 --
1723 l_proc varchar2(72) := g_package||'update_validate';
1724 --
1725 Begin
1726 hr_utility.set_location('Entering:'||l_proc, 5);
1727 --
1728 --
1729 -- Clearing the Global variables since the record may have changed.
1730 --
1731 g_user_key_units := NULL;
1732 g_range_or_match := NULL;
1733 --
1734 --
1735 -- Call all supporting business operations
1736 --
1737 --
1738 chk_startup_action(false
1739 ,p_rec.business_group_id
1740 ,p_rec.legislation_code
1741 );
1742 IF hr_startup_data_api_support.g_startup_mode
1743 NOT IN ('GENERIC','STARTUP') THEN
1744 --
1745 -- Validate Important Attributes
1746 --
1747 hr_api.validate_bus_grp_id
1748 (p_business_group_id => p_rec.business_group_id
1749 ,p_associated_column1 => pay_pur_shd.g_tab_nam
1750 || '.BUSINESS_GROUP_ID');
1751 --
1752 -- After validating the set of important attributes,
1753 -- if Multiple Message Detection is enabled and at least
1754 -- one error has been found then abort further validation.
1755 --
1756 hr_multi_message.end_validation_set;
1757 END IF;
1758 --
1759 --
1760 -- Validate Dependent Attributes
1761 --
1762 -- Call the datetrack update integrity operation
1763 --
1764 dt_update_validate
1765 (p_datetrack_mode => p_datetrack_mode
1766 ,p_validation_start_date => p_validation_start_date
1767 ,p_validation_end_date => p_validation_end_date
1768 );
1769 --
1770 chk_non_updateable_args
1771 (p_effective_date => p_effective_date
1772 ,p_rec => p_rec
1773 );
1774 --
1775 --
1776 chk_row_low_range_or_name
1777 (p_user_row_id => p_rec.user_row_id
1778 ,p_user_table_id => p_rec.user_table_id
1779 ,p_row_low_range_or_name => p_rec.row_low_range_or_name
1780 ,p_object_version_number => p_rec.object_version_number
1781 ,p_disable_units_check => p_disable_units_check
1782 ,p_disable_range_overlap_check => p_disable_range_overlap_check
1783 ,p_business_group_id => p_rec.business_group_id
1784 ,p_legislation_code => p_rec.legislation_code
1785 ,p_effective_date => p_effective_date
1786 ,p_validation_start_date => p_validation_start_date
1787 ,p_validation_end_date => p_validation_end_date
1788 );
1789 --
1790 --
1791 chk_display_sequence( p_display_sequence => p_rec.display_sequence );
1792 --
1793 --
1794 chk_row_high_range
1795 ( p_row_high_range => p_rec.row_high_range
1796 ,p_user_table_id => p_rec.user_table_id
1797 ,p_user_row_id => p_rec.user_row_id
1798 ,p_object_version_number => p_rec.object_version_number
1799 ,p_effective_date => p_effective_date
1800 );
1801 --
1802 --
1803 chk_range
1804 ( p_user_row_id => p_rec.user_row_id
1805 ,p_user_table_id => p_rec.user_table_id
1806 ,p_row_low_range_or_name => p_rec.row_low_range_or_name
1807 ,p_row_high_range => p_rec.row_high_range
1808 ,p_disable_range_overlap_check => p_disable_range_overlap_check
1809 ,p_object_version_number => p_rec.object_version_number
1810 ,p_business_group_id => p_rec.business_group_id
1811 ,p_legislation_code => p_rec.legislation_code
1812 ,p_effective_date => p_effective_date
1813 ,p_validation_start_date => p_validation_start_date
1814 ,p_validation_end_date => p_validation_end_date
1815 );
1816 --
1817 --
1818 hr_utility.set_location(' Leaving:'||l_proc, 10);
1819 End update_validate;
1820 --
1821 -- ----------------------------------------------------------------------------
1822 -- |---------------------------< delete_validate >----------------------------|
1823 -- ----------------------------------------------------------------------------
1824 Procedure delete_validate
1825 (p_rec in pay_pur_shd.g_rec_type
1826 ,p_effective_date in date
1827 ,p_datetrack_mode in varchar2
1828 ,p_disable_range_overlap_check in boolean
1829 ,p_validation_start_date in date
1830 ,p_validation_end_date in date
1831 ) is
1832 --
1833 l_proc varchar2(72) := g_package||'delete_validate';
1834 --
1835 Begin
1836 hr_utility.set_location('Entering:'||l_proc, 5);
1837 --
1838 -- Clearing the Global variables since the record may have changed.
1839 --
1840 g_user_key_units := NULL;
1841 g_range_or_match := NULL;
1842 --
1843 --
1844 chk_startup_action(false
1845 ,pay_pur_shd.g_old_rec.business_group_id
1846 ,pay_pur_shd.g_old_rec.legislation_code
1847 );
1848 IF hr_startup_data_api_support.g_startup_mode
1849 NOT IN ('GENERIC','STARTUP') THEN
1850 --
1851 -- Validate Important Attributes
1852 --
1853 --
1854 -- After validating the set of important attributes,
1855 -- if Multiple Message Detection is enabled and at least
1856 -- one error has been found then abort further validation.
1857 --
1858 hr_multi_message.end_validation_set;
1859 END IF;
1860 --
1861 -- Call all supporting business operations
1862 --
1863 dt_delete_validate
1864 (p_datetrack_mode => p_datetrack_mode
1865 ,p_validation_start_date => p_validation_start_date
1866 ,p_validation_end_date => p_validation_end_date
1867 ,p_user_row_id => p_rec.user_row_id
1868 );
1869 --
1870 --
1871 chk_delete
1872 (p_user_table_id => pay_pur_shd.g_old_rec.user_table_id
1873 ,p_user_row_id => p_rec.user_row_id
1874 ,p_row_low_range_or_name => pay_pur_shd.g_old_rec.row_low_range_or_name
1875 ,p_row_high_range => pay_pur_shd.g_old_rec.row_high_range
1876 ,p_datetrack_mode => p_datetrack_mode
1877 ,p_business_group_id => pay_pur_shd.g_old_rec.business_group_id
1878 ,p_legislation_code => pay_pur_shd.g_old_rec.legislation_code
1879 ,p_disable_range_overlap_check => p_disable_range_overlap_check
1880 ,p_validation_start_date => p_validation_start_date
1881 ,p_validation_end_date => p_validation_end_date
1882 );
1883
1884 hr_utility.set_location(' Leaving:'||l_proc, 10);
1885 End delete_validate;
1886 --
1887 end pay_pur_bus;
[Home] [Help]