[Home] [Help]
PACKAGE BODY: APPS.BEN_ECV_BUS
Source
1 Package body ben_ecv_bus as
2 /* $Header: beecvrhi.pkb 120.1 2005/07/29 09:50:17 rbingi noship $ */
3 --
4 --
5 -- ----------------------------------------------------------------------------
6 -- | Private Global Definitions |
7 -- ----------------------------------------------------------------------------
8 --
9 g_package varchar2(33) := ' ben_ecv_bus.'; -- Global package name
10 --
11 -- ----------------------------------------------------------------------------
12 -- |------< chk_eligy_crit_values_id >------|
13 -- ----------------------------------------------------------------------------
14 --
15 -- Description
16 -- This procedure is used to check that the primary key for the table
17 -- is created properly. It should be null on insert and
18 -- should not be able to be updated.
19 --
20 -- Pre Conditions
21 -- None.
22 --
23 -- In Parameters
24 -- eligy_crit_values_id PK of record being inserted or updated.
25 -- effective_date Effective Date of session
26 -- object_version_number Object version number of record being
27 -- inserted or updated.
28 --
29 -- Post Success
30 -- Processing continues
31 --
32 -- Post Failure
33 -- Errors handled by the procedure
34 --
35 -- Access Status
36 -- Internal table handler use only.
37 --
38 procedure chk_eligy_crit_values_id (
39 p_eligy_crit_values_id In Number,
40 p_effective_date In Date,
41 p_object_version_number In Number
42 ) is
43 --
44 l_proc varchar2(72) := g_package||'chk_eligy_crit_values_id';
45 l_api_updating boolean;
46 --
47 Begin
48 --
49 hr_utility.set_location('Entering:'||l_proc, 5);
50 --
51 l_api_updating := ben_ecv_shd.api_updating
52 (p_effective_date => p_effective_date,
53 p_eligy_crit_values_id => p_eligy_crit_values_id,
54 p_object_version_number => p_object_version_number
55 );
56 --
57 if (l_api_updating
58 and nvl(p_eligy_crit_values_id,hr_api.g_number)
59 <> ben_ecv_shd.g_old_rec.eligy_crit_values_id) then
60 --
61 -- raise error as PK has changed
62 --
63 ben_ecv_shd.constraint_error('BEN_ELIGY_CRIT_VALUES_F_PK');
64 --
65 elsif not l_api_updating then
66 --
67 --check if PK is NULL
68 --
69 if p_eligy_crit_values_id is not null then
70 --
71 -- raise error as PK is not null
72 --
73 ben_ecv_shd.constraint_error('BEN_ELIGY_CRIT_VALUES_F_PK');
74 --
75 end if;
76 --
77 end if;
78 --
79 hr_utility.set_location('Leaving:'||l_proc, 10);
80 --
81 End chk_eligy_crit_values_id;
82 --
83 -- ----------------------------------------------------------------------------
84 -- |----------------------------< chk_eligy_criteria_id >----------------------------|
85 -- ----------------------------------------------------------------------------
86 --
87 -- Description
88 -- This procedure checks that a referenced foreign key actually exists
89 -- in the referenced table.
90 --
91 -- Pre-Conditions
92 -- None.
93 --
94 -- In Parameters
95 -- p_eligy_crit_values_id PK
96 -- p_eligy_criteria_id ID of FK column
97 -- p_object_version_number object version number
98 --
99 -- Post Success
100 -- Processing continues
101 --
102 -- Post Failure
103 -- Error raised.
104 --
105 -- Access Status
106 -- Internal table handler use only.
107 --
108 procedure chk_eligy_criteria_id(
112 p_object_version_number In Number
109 p_eligy_crit_values_id In Number,
110 p_eligy_criteria_id In Number,
111 p_effective_date In Date,
113 ) is
114 --
115 l_proc varchar2(72) := g_package||'chk_eligy_criteria_id';
116 l_api_updating boolean;
117 l_dummy varchar2(1);
118 --
119 Cursor csr_eligy_criteria_id is
120 select NULL
121 from ben_eligy_criteria
122 where eligy_criteria_id = p_eligy_criteria_id;
123 begin
124 --
125 hr_utility.set_location('Entering:'||l_proc, 5);
126 --
127 l_api_updating := ben_ecv_shd.api_updating(
128 p_eligy_crit_values_id => p_eligy_crit_values_id,
129 p_effective_date => p_effective_date,
130 p_object_version_number => p_object_version_number
131 );
132 --
133 if (l_api_updating
134 and nvl(p_eligy_criteria_id,hr_api.g_number)
135 <> ben_ecv_shd.g_old_rec.eligy_criteria_id
136 or not l_api_updating) then
137 --
138 open csr_eligy_criteria_id;
139 --
140 fetch csr_eligy_criteria_id into l_dummy;
141 --
142 if csr_eligy_criteria_id%notfound then
143 --
144 close csr_eligy_criteria_id;
145 --
146 --Raise an error
147 --
148 ben_ecv_shd.constraint_error('BEN_ELIGY_CRIT_VALUES_FK1');
149 --
150 end if;
151 --
152 close csr_eligy_criteria_id;
153 --
154 end if;
155 --
156 hr_utility.set_location('Leaving:'||l_proc, 10);
157 --
158 end chk_eligy_criteria_id;
159 --
160 -- ----------------------------------------------------------------------------
161 -- |----------------------------< chk_eligy_prfl_id >----------------------------|
162 -- ----------------------------------------------------------------------------
163 --
164 -- Description
165 -- This procedure checks that a referenced foreign key actually exists
166 -- in the referenced table.
167 --
168 -- Pre-Conditions
169 -- None.
170 --
171 -- In Parameters
172 -- p_eligy_crit_values_id PK
173 -- p_eligy_prfl_id ID of FK column
174 -- p_object_version_number object version number
175 --
176 -- Post Success
177 -- Processing continues
178 --
179 -- Post Failure
180 -- Error raised.
181 --
182 -- Access Status
183 -- Internal table handler use only.
184 --
185 procedure chk_eligy_prfl_id(
186 p_eligy_crit_values_id In Number,
187 p_eligy_prfl_id In Number,
188 p_effective_date In Date,
189 p_object_version_number In Number
190 ) is
191 --
192 l_proc varchar2(72) := g_package||'chk_eligy_prfl_id';
193 l_api_updating boolean;
194 l_dummy varchar2(1);
195 --
196 Cursor csr_eligy_prfl_id is
197 select NULL
198 from ben_eligy_prfl_f
199 where eligy_prfl_id = p_eligy_prfl_id;
200 begin
201 --
202 hr_utility.set_location('Entering:'||l_proc, 5);
203 --
204 l_api_updating := ben_ecv_shd.api_updating(
205 p_eligy_crit_values_id => p_eligy_crit_values_id,
206 p_effective_date => p_effective_date,
207 p_object_version_number => p_object_version_number
208 );
209 --
210 if (l_api_updating
211 and nvl(p_eligy_prfl_id,hr_api.g_number)
212 <> ben_ecv_shd.g_old_rec.eligy_prfl_id
213 or not l_api_updating) then
214 --
215 open csr_eligy_prfl_id;
216 --
217 fetch csr_eligy_prfl_id into l_dummy;
218 --
219 if csr_eligy_prfl_id%notfound then
220 --
221 close csr_eligy_prfl_id;
222 --Raise an error
223 --
224 ben_ecv_shd.constraint_error('BEN_ELIGY_CRIT_VALUES_FK1');
225 --
226 end if;
227 --
228 close csr_eligy_prfl_id;
229 end if;
230 --
231 hr_utility.set_location('Leaving:'||l_proc, 10);
232 --
233 end chk_eligy_prfl_id;
234 --
235 -- ----------------------------------------------------------------------------
236 -- |-------------------< chk_duplicate_eligy_criteria >-----------------------|
237 -- ----------------------------------------------------------------------------
238 --
239 -- Description :
240 -- This procedure checks that an eligibility criteria is not attached more
241 -- than once to the eligiblity profile
242 -- ---------------------------------------------------------------------------
243 procedure chk_duplicate_eligy_criteria
244 (p_eligy_crit_values_id in number,
245 p_eligy_prfl_id in number,
246 p_eligy_criteria_id in number,
247 p_number_value1 in number,
248 p_char_value1 in varchar2,
249 p_date_value1 in date,
250 p_number_value2 in number,
251 p_char_value2 in varchar2,
252 p_date_value2 in date,
253 p_number_value3 in number,
254 p_char_value3 in varchar2,
255 p_date_value3 in date,
256 p_number_value4 in number,
257 p_char_value4 in varchar2,
258 p_date_value4 in date,
259 p_validation_start_date in date,
263 --
260 p_validation_end_date in date,
261 p_business_group_id in number
262 ) is
264 l_proc varchar2(72) := g_package||'.chk_duplicate_eligy_criteria';
265 l_dummy varchar2(1);
266 --
267 cursor c_eligy_prfl is
268 select null
269 from ben_eligy_crit_values_f ecv
270 where ecv.eligy_prfl_id = p_eligy_prfl_id
271 and ecv.eligy_criteria_id = p_eligy_criteria_id
272 and ecv.eligy_crit_values_id <> nvl ( p_eligy_crit_values_id, -1)
273 and nvl(ecv.number_value1,hr_api.g_number) = nvl(p_number_value1,hr_api.g_number)
274 and nvl(ecv.number_value2,hr_api.g_number) = nvl(p_number_value2,hr_api.g_number)
275 and nvl(ecv.char_value1,hr_api.g_varchar2) = nvl(p_char_value1,hr_api.g_varchar2)
276 and nvl(ecv.char_value2,hr_api.g_varchar2) = nvl(p_char_value2,hr_api.g_varchar2)
277 and nvl(ecv.date_value1,hr_api.g_date) = nvl (p_date_value1,hr_api.g_date)
278 and nvl(ecv.date_value2,hr_api.g_date) = nvl (p_date_value2,hr_api.g_date)
279 and nvl(ecv.number_value3,hr_api.g_number) = nvl(p_number_value3,hr_api.g_number)
280 and nvl(ecv.number_value4,hr_api.g_number) = nvl(p_number_value4,hr_api.g_number)
281 and nvl(ecv.char_value3,hr_api.g_varchar2) = nvl(p_char_value3,hr_api.g_varchar2)
282 and nvl(ecv.char_value4,hr_api.g_varchar2) = nvl(p_char_value4,hr_api.g_varchar2)
283 and nvl(ecv.date_value3,hr_api.g_date) = nvl (p_date_value3,hr_api.g_date)
284 and nvl(ecv.date_value4,hr_api.g_date) = nvl (p_date_value4,hr_api.g_date)
285 and ecv.effective_start_date <= p_validation_end_date
286 and ecv.effective_end_date >= p_validation_start_date
287 and ecv.business_group_id = p_business_group_id;
288 --
289 begin
290 --
291 hr_utility.set_location('Entering:'||l_proc, 5);
292
293 --
294 open c_eligy_prfl;
295 --
296 fetch c_eligy_prfl into l_dummy;
297 --
298 if c_eligy_prfl%found
299 then
300 --
301 close c_eligy_prfl;
302 fnd_message.set_name('BEN', 'BEN_94139_DUP_ELIGY_CRIT_EXIST');
303 fnd_message.raise_error;
304 --
305 end if;
306 --
307 close c_eligy_prfl;
308 --
309 hr_utility.set_location('Leaving:'||l_proc, 10);
310 --
311 end chk_duplicate_eligy_criteria;
312 --
313 -- ---------------------------------------------------------------------------
314 -- |-----------------------< chk_duplicate_ordr_num >---------------------------|
315 -- ---------------------------------------------------------------------------
316 --
317 -- Description
318 -- Ensure that the Sequence Number is unique
319 -- within business_group
320 --
321 -- Pre Conditions
322 -- None.
323 --
324 -- In Parameters
325 -- p_eligy_crit_values_id eligy_crit_values_id
326 -- p_eligy_prfl_id eligy_prfl_id
327 -- p_ordr_num Sequence Number
328 -- p_business_group_id Business Group ID
329 -- p_validation_start_date Start date of the record
330 -- p_validation_end_date End date of the record
331 --
332 -- Post Success
333 -- Processing continues
334 --
335 -- Post Failure
336 -- Errors handled by the procedure
337 --
338 -- Access Status
339 -- Internal table handler use only
340 --
341 -- ----------------------------------------------------------------------------
342 Procedure chk_duplicate_ordr_num
343 ( p_eligy_crit_values_id in number,
344 p_eligy_prfl_id in number,
345 p_ordr_num in number,
346 p_validation_start_date in date,
347 p_validation_end_date in date,
348 p_business_group_id in number
349 ) is
350 --
351 l_proc varchar2(72) := g_package||'.chk_duplicate_ordr_num';
352 l_dummy char(1);
353 --
354 cursor c1 is
355 select null
356 from ben_eligy_crit_values_f
357 where eligy_crit_values_id <> nvl( p_eligy_crit_values_id, -1 )
358 and eligy_prfl_id = p_eligy_prfl_id
359 and ordr_num = p_ordr_num
360 and business_group_id = p_business_group_id
361 and effective_start_date <= p_validation_end_date
362 and effective_end_date >= p_validation_start_date;
363 --
364 Begin
365 --
366 hr_utility.set_location('Entering:'||l_proc, 5);
367 --
368 open c1;
369 --
370 fetch c1 into l_dummy;
371 --
372 if c1%found
373 then
374 --
375 close c1;
376 fnd_message.set_name('BEN','BEN_91001_SEQ_NOT_UNIQUE');
377 fnd_message.raise_error;
378 --
379 end if;
380 --
381 close c1;
382 --
383 hr_utility.set_location('Leaving:'||l_proc, 15);
384 --
385 End chk_duplicate_ordr_num;
386 --
387 -- ----------------------------------------------------------------------------
388 -- |------------------------< chk_required_fields >---------------------------|
389 -- ----------------------------------------------------------------------------
390 --
391 -- Description :
392 -- This procedure checks
393 -- (1) Based on selected eligibility criteria, all relevant fields are not null
394 -- (2) Validates the Lookup Type (if required)
395 -- (3) Validates if Organization Hierarchy exists and is active on p_effective_date
396 -- (4) Validates if Start Organization falls under Organization Hierarchy
397 -- (5) Validates if Position Hierarchy exists and is active on p_effective_date
398 -- (6) Validates if Start Position falls under Position Hierarchy
399 -- ---------------------------------------------------------------------------
400 procedure chk_required_fields
401 (p_eligy_crit_values_id in number,
405 p_date_Value1 in date,
402 p_eligy_criteria_id in number,
403 p_number_value1 in number,
404 p_char_value1 in varchar2,
406 p_number_value2 in number,
407 p_char_value2 in varchar2,
408 p_date_Value2 in date,
409 p_number_value3 in number,
410 p_char_value3 in varchar2,
411 p_date_Value3 in date,
412 p_number_value4 in number,
413 p_char_value4 in varchar2,
414 p_date_Value4 in date,
415 p_business_group_id in number,
416 p_effective_date in date
417 ) is
418 --
419 l_proc varchar2(72) := g_package||'.chk_required_fields';
420 l_dummy varchar2(1);
421 l_crit_col1_val_type_cd varchar2(30);
422 l_crit_col1_datatype varchar2(30);
423 l_col1_lookup_type varchar2(30);
424 l_lookup_code varchar2(30);
425 l_allow_range_validation_flag varchar2(30);
426 --
427 l_crit_col2_val_type_cd varchar2(30);
428 l_crit_col2_datatype varchar2(30);
429 l_col2_lookup_type varchar2(30);
430 l_allow_range_validation_flag2 varchar2(30);
431 --
432 cursor c_eligy_criteria is
433 select crit_col1_val_type_cd, crit_col1_datatype, col1_lookup_type, allow_range_validation_flag,
434 crit_col2_val_type_cd, crit_col2_datatype, col2_lookup_type, allow_range_validation_flag2
435 from ben_eligy_criteria egl
436 where egl.eligy_criteria_id = p_eligy_criteria_id
437 and egl.business_group_id = p_business_group_id;
438 -- Cursors for Set 1
439 cursor c_org_stru_ver is
440 select null
441 from per_org_structure_versions osv
442 where osv.business_group_id = p_business_group_id
443 and osv.org_structure_version_id = p_number_value1
444 and p_effective_date between osv.date_from
445 and nvl (osv.date_to, p_effective_date );
446 --
447 cursor c_start_org is
448 select null
449 from per_org_structure_elements ose
450 where ose.org_structure_version_id = p_number_value1
451 and ( ose.organization_id_parent = p_number_value2 or
452 ose.organization_id_child = p_number_value2
453 );
454 --
455 cursor c_pos_stru_ver is
456 select null
457 from per_pos_structure_versions psv
458 where psv.business_group_id = p_business_group_id
459 and psv.pos_structure_version_id = p_number_value1
460 and p_effective_date between psv.date_from
461 and nvl (psv.date_to, p_effective_date );
462 --
463 cursor c_start_pos is
464 select null
465 from per_pos_structure_elements pse
466 where pse.pos_structure_version_id = p_number_value1
467 and ( pse.parent_position_id = p_number_value2 or
468 pse.subordinate_position_id = p_number_value2
469 );
470 --
471 -- Cursors for Set 2
472 cursor c_org_stru_ver2 is
473 select null
474 from per_org_structure_versions osv
475 where osv.business_group_id = p_business_group_id
476 and osv.org_structure_version_id = p_number_value3
477 and p_effective_date between osv.date_from
478 and nvl (osv.date_to, p_effective_date );
479 --
480 cursor c_start_org2 is
481 select null
482 from per_org_structure_elements ose
483 where ose.org_structure_version_id = p_number_value3
484 and ( ose.organization_id_parent = p_number_value4 or
485 ose.organization_id_child = p_number_value4
486 );
487 --
488 cursor c_pos_stru_ver2 is
489 select null
490 from per_pos_structure_versions psv
491 where psv.business_group_id = p_business_group_id
492 and psv.pos_structure_version_id = p_number_value3
493 and p_effective_date between psv.date_from
494 and nvl (psv.date_to, p_effective_date );
495 --
496 cursor c_start_pos2 is
497 select null
498 from per_pos_structure_elements pse
499 where pse.pos_structure_version_id = p_number_value3
500 and ( pse.parent_position_id = p_number_value4 or
501 pse.subordinate_position_id = p_number_value4
502 );
503 --
504 begin
505 --
506 hr_utility.set_location('Entering:'||l_proc, 5);
507 --
508 open c_eligy_criteria;
509 --
510 fetch c_eligy_criteria into l_crit_col1_val_type_cd,
511 l_crit_col1_datatype,
512 l_col1_lookup_type,
513 l_allow_range_validation_flag,
514 l_crit_col2_val_type_cd,
515 l_crit_col2_datatype,
516 l_col2_lookup_type,
517 l_allow_range_validation_flag2;
518 --
519 if c_eligy_criteria%found
520 then
521 --
522 -- LOOKUP VALUES
523 --
524 if l_crit_col1_val_type_cd = 'LOOKUP'
525 then
526 --
527 if ( l_crit_col1_datatype = 'N' and p_number_value1 is null ) OR
528 ( l_crit_col1_datatype = 'N' and l_allow_range_validation_flag = 'Y' and p_number_value2 is null ) OR
529 ( l_crit_col1_datatype = 'D' and p_date_value1 is null ) OR
530 ( l_crit_col1_datatype = 'D' and l_allow_range_validation_flag = 'Y' and p_date_value2 is null ) OR
534 --
531 ( l_crit_col1_datatype = 'C' and p_char_value1 is null ) OR
532 ( l_crit_col1_datatype = 'C' and l_allow_range_validation_flag = 'Y' and p_char_value2 is null )
533 then
535 fnd_message.set_name('BEN', 'BEN_94140_LOOKUP_VALUE_NULL');
536 fnd_message.raise_error;
537 --
538 else
539 --
540 if l_crit_col1_datatype = 'N'
541 then
542 l_lookup_code := p_number_value1;
543 elsif l_crit_col1_datatype = 'C'
544 then
545 l_lookup_code := p_char_value1;
546 elsif l_crit_col1_datatype = 'D'
547 then
548 l_lookup_code := p_date_value1;
549 end if;
550 --
551 if hr_api.not_exists_in_hr_lookups
552 (p_lookup_type => l_col1_lookup_type,
553 p_lookup_code => l_lookup_code,
554 p_effective_date => p_effective_date) then
555 --
556 -- raise error as code does not exist as lookup
557 --
558 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
559 fnd_message.set_token('FIELD', 'Lookup Value');
560 fnd_message.set_token('TYPE',l_col1_lookup_type);
561 fnd_message.set_token('VALUE',l_lookup_code);
562 fnd_message.raise_error;
563 --
564 end if;
565 --
566 end if;
567 --
568 --
569 -- Value Set
570 --
571 elsif l_crit_col1_val_type_cd = 'VAL_SET'
572 then
573 --
574 if ( l_crit_col1_datatype = 'N' and p_number_value1 is null ) OR
575 ( l_crit_col1_datatype = 'N' and l_allow_range_validation_flag = 'Y' and p_number_value2 is null ) OR
576 ( l_crit_col1_datatype = 'D' and p_date_value1 is null ) OR
577 ( l_crit_col1_datatype = 'D' and l_allow_range_validation_flag = 'Y' and p_date_value2 is null ) OR
578 ( l_crit_col1_datatype = 'C' and p_char_value1 is null ) OR
579 ( l_crit_col1_datatype = 'C' and l_allow_range_validation_flag = 'Y' and p_char_value2 is null )
580 then
581 --
582 fnd_message.set_name('BEN', 'BEN_94141_VSET_VALUE_NULL');
583 fnd_message.raise_error;
584 --
585 end if;
586 --
587 --
588 -- Organization Hierarchy
589 --
590 elsif l_crit_col1_val_type_cd = 'ORG_HIER'
591 then
592 --
593 if (p_number_value1 is null or p_number_value2 is null)
594 then
595 --
596 fnd_message.set_name('BEN', 'BEN_94142_ORG_HIERARCHY_NULL');
597 fnd_message.raise_error;
598 --
599 else
600 --
601 -- Validate Organization Hierarchy / Structure
602 --
603 open c_org_stru_ver ;
604 --
605 fetch c_org_stru_ver into l_dummy;
606 --
607 if c_org_stru_ver%notfound
608 then
609 --
610 -- Organization Structure Version ID non-existent or inactive
611 --
612 fnd_message.set_name('BEN', 'BEN_94143_INVALID_ORG_STRU_VER');
613 fnd_message.raise_error;
614 --
615 end if;
616 --
617 close c_org_stru_ver;
618 --
619 -- Validate Start Organization
620 --
621 open c_start_org ;
622 --
623 fetch c_start_org into l_dummy;
624 --
625 if c_start_org%notfound
626 then
627 --
628 -- Start Organization does not fall under selected Organization Structure / Hierarchy
629 --
630 fnd_message.set_name('BEN', 'BEN_94144_INVALID_START_ORG');
631 fnd_message.raise_error;
632 --
633 end if;
634 --
635 close c_start_org;
636 --
637
638 end if;
639 --
640 --
641 -- Position Hierarchy
642 --
643 elsif l_crit_col1_val_type_cd = 'POS_HIER'
644 then
645 --
646 if (p_number_value1 is null or p_number_value2 is null)
647 then
648 --
649 fnd_message.set_name('BEN', 'BEN_94145_POS_HIERARCHY_NULL');
650 fnd_message.raise_error;
651 --
652 else
653 --
654 -- Validate Position Hierarchy / Structure
655 --
656 open c_pos_stru_ver ;
657 --
658 fetch c_pos_stru_ver into l_dummy;
659 --
660 if c_pos_stru_ver%notfound
661 then
662 --
663 -- Position Structure Version ID non-existent or inactive
664 --
665 fnd_message.set_name('BEN', 'BEN_94146_INVALID_POS_STRU_VER');
666 fnd_message.raise_error;
667 --
668 end if;
669 --
670 close c_pos_stru_ver;
671 --
672 -- Validate Start Position
673 --
674 open c_start_pos ;
675 --
676 fetch c_start_pos into l_dummy;
677 --
678 if c_start_pos%notfound
679 then
680 --
684 fnd_message.raise_error;
681 -- Start Position does not fall under selected Position Structure / Hierarchy
682 --
683 fnd_message.set_name('BEN', 'BEN_94147_INVALID_START_POS');
685 --
686 end if;
687 --
688 close c_start_pos;
689 --
690 end if;
691 --
692 end if;
693 --
694 -- Set 2 validations added rbingi
695 if l_crit_col2_val_type_cd = 'LOOKUP'
696 then
697 --
698 if ( l_crit_col2_datatype = 'N' and p_number_value3 is null ) OR
699 ( l_crit_col2_datatype = 'N' and l_allow_range_validation_flag2 = 'Y' and p_number_value4 is null ) OR
700 ( l_crit_col2_datatype = 'D' and p_date_value3 is null ) OR
701 ( l_crit_col2_datatype = 'D' and l_allow_range_validation_flag2 = 'Y' and p_date_value4 is null ) OR
702 ( l_crit_col2_datatype = 'C' and p_char_value3 is null ) OR
703 ( l_crit_col2_datatype = 'C' and l_allow_range_validation_flag2 = 'Y' and p_char_value4 is null )
704 then
705 --
706 fnd_message.set_name('BEN', 'BEN_94140_LOOKUP_VALUE_NULL');
707 fnd_message.raise_error;
708 --
709 else
710 --
711 if l_crit_col2_datatype = 'N'
712 then
713 l_lookup_code := p_number_value3;
714 elsif l_crit_col2_datatype = 'C'
715 then
716 l_lookup_code := p_char_value3;
717 elsif l_crit_col2_datatype = 'D'
718 then
719 l_lookup_code := p_date_value3;
720 end if;
721 --
722 if hr_api.not_exists_in_hr_lookups
723 (p_lookup_type => l_col2_lookup_type,
724 p_lookup_code => l_lookup_code,
725 p_effective_date => p_effective_date) then
726 --
727 -- raise error as code does not exist as lookup
728 --
729 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
730 fnd_message.set_token('FIELD', 'Lookup Value');
731 fnd_message.set_token('TYPE',l_col2_lookup_type);
732 fnd_message.set_token('VALUE',l_lookup_code);
733 fnd_message.raise_error;
734 --
735 end if;
736 --
737 end if;
738 --
739 --
740 -- Value Set
741 --
742 elsif l_crit_col2_val_type_cd = 'VAL_SET'
743 then
744 --
745 if ( l_crit_col2_datatype = 'N' and p_number_value3 is null ) OR
746 ( l_crit_col2_datatype = 'N' and l_allow_range_validation_flag2 = 'Y' and p_number_value4 is null ) OR
747 ( l_crit_col2_datatype = 'D' and p_date_value3 is null ) OR
748 ( l_crit_col2_datatype = 'D' and l_allow_range_validation_flag2 = 'Y' and p_date_value4 is null ) OR
749 ( l_crit_col2_datatype = 'C' and p_char_value3 is null ) OR
750 ( l_crit_col2_datatype = 'C' and l_allow_range_validation_flag2 = 'Y' and p_char_value4 is null )
751 then
752 --
753 fnd_message.set_name('BEN', 'BEN_94141_VSET_VALUE_NULL');
754 fnd_message.raise_error;
755 --
756 end if;
757 --
758 --
759 -- Organization Hierarchy
760 --
761 elsif l_crit_col2_val_type_cd = 'ORG_HIER'
762 then
763 --
764 if (p_number_value3 is null or p_number_value4 is null)
765 then
766 --
767 fnd_message.set_name('BEN', 'BEN_94142_ORG_HIERARCHY_NULL');
768 fnd_message.raise_error;
769 --
770 else
771 --
772 -- Validate Organization Hierarchy / Structure
773 --
774 open c_org_stru_ver2 ;
775 --
776 fetch c_org_stru_ver2 into l_dummy;
777 --
778 if c_org_stru_ver2%notfound
779 then
780 --
781 -- Organization Structure Version ID non-existent or inactive
782 --
783 fnd_message.set_name('BEN', 'BEN_94143_INVALID_ORG_STRU_VER');
784 fnd_message.raise_error;
785 --
786 end if;
787 --
788 close c_org_stru_ver2;
789 --
790 -- Validate Start Organization
791 --
792 open c_start_org2 ;
793 --
794 fetch c_start_org2 into l_dummy;
795 --
796 if c_start_org2%notfound
797 then
798 --
799 -- Start Organization does not fall under selected Organization Structure / Hierarchy
800 --
801 fnd_message.set_name('BEN', 'BEN_94144_INVALID_START_ORG');
802 fnd_message.raise_error;
803 --
804 end if;
805 --
806 close c_start_org2;
807 --
808
809 end if;
810 --
811 --
812 -- Position Hierarchy
813 --
814 elsif l_crit_col2_val_type_cd = 'POS_HIER'
815 then
816 --
817 if (p_number_value3 is null or p_number_value4 is null)
818 then
819 --
820 fnd_message.set_name('BEN', 'BEN_94145_POS_HIERARCHY_NULL');
821 fnd_message.raise_error;
822 --
823 else
824 --
825 -- Validate Position Hierarchy / Structure
826 --
827 open c_pos_stru_ver2 ;
828 --
829 fetch c_pos_stru_ver2 into l_dummy;
833 --
830 --
831 if c_pos_stru_ver2%notfound
832 then
834 -- Position Structure Version ID non-existent or inactive
835 --
836 fnd_message.set_name('BEN', 'BEN_94146_INVALID_POS_STRU_VER');
837 fnd_message.raise_error;
838 --
839 end if;
840 --
841 close c_pos_stru_ver2;
842 --
843 -- Validate Start Position
844 --
845 open c_start_pos2 ;
846 --
847 fetch c_start_pos2 into l_dummy;
848 --
849 if c_start_pos2%notfound
850 then
851 --
852 -- Start Position does not fall under selected Position Structure / Hierarchy
853 --
854 fnd_message.set_name('BEN', 'BEN_94147_INVALID_START_POS');
855 fnd_message.raise_error;
856 --
857 end if;
858 --
859 close c_start_pos2;
860 --
861 end if;
862 --
863 end if;
864 --
865 end if;
866 --
867 close c_eligy_criteria;
868 --
869 hr_utility.set_location('Leaving:'||l_proc, 10);
870 --
871 end chk_required_fields;
872 --
873 -- ----------------------------------------------------------------------------
874 -- |--------------------------< dt_update_validate >--------------------------|
875 -- ----------------------------------------------------------------------------
876 -- {Start Of Comments}
877 --
878 -- Description:
879 -- This procedure is used for referential integrity of datetracked
880 -- parent entities when a datetrack update operation is taking place
881 -- and where there is no cascading of update defined for this entity.
882 --
883 -- Prerequisites:
884 -- This procedure is called from the update_validate.
885 --
886 -- In Parameters:
887 --
888 -- Post Success:
889 -- Processing continues.
890 --
891 -- Post Failure:
892 --
893 -- Developer Implementation Notes:
894 -- This procedure should not need maintenance unless the HR Schema model
895 -- changes.
896 --
897 -- Access Status:
898 -- Internal Row Handler Use Only.
899 --
900 -- {End Of Comments}
901 -- ----------------------------------------------------------------------------
902 procedure dt_update_validate
903 (p_eligy_criteria_id In number default hr_api.g_number,
904 p_eligy_prfl_id In number default hr_api.g_number,
905 p_datetrack_mode In Varchar2,
906 p_validation_start_date In Date,
907 p_validation_end_date In Date
908 ) Is
909 --
910 l_proc varchar2(72) := g_package||'dt_update_validate';
911 l_integrity_error Exception;
912 l_table_name all_tables.table_name%TYPE;
913 --
914 Begin
915 hr_utility.set_location('Entering:'||l_proc, 5);
916 --
917 -- Ensure that the p_datetrack_mode argument is not null
918 --
919 hr_api.mandatory_arg_error
920 (p_api_name => l_proc,
921 p_argument => 'datetrack_mode',
922 p_argument_value => p_datetrack_mode);
923 --
924 -- Only perform the validation if the datetrack update mode is valid
925 --
926 If (dt_api.validate_dt_upd_mode(p_datetrack_mode => p_datetrack_mode)) then
927 --
928 --
929 -- Ensure the arguments are not null
930 --
931 hr_api.mandatory_arg_error
932 (p_api_name => l_proc,
933 p_argument => 'validation_start_date',
934 p_argument_value => p_validation_start_date);
935 --
936 hr_api.mandatory_arg_error
937 (p_api_name => l_proc,
938 p_argument => 'validation_end_date',
939 p_argument_value => p_validation_end_date);
940 --
941 /* If ((nvl(p_eligy_criteria_id, hr_api.g_number) <> hr_api.g_number) and
942 NOT (dt_api.check_min_max_dates
943 (p_base_table_name => 'ben_eligy_criteria',
944 p_base_key_column => 'eligy_criteria_id',
945 p_base_key_value => p_eligy_criteria_id,
946 p_from_date => p_validation_start_date,
947 p_to_date => p_validation_end_date))) Then
948 l_table_name := 'ben_eligy_criteria';
949 Raise l_integrity_error;
950 End If;*/
951 --
952 If ((nvl(p_eligy_prfl_id, hr_api.g_number) <> hr_api.g_number) and
953 NOT (dt_api.check_min_max_dates
954 (p_base_table_name => 'ben_eligy_prfl_f',
955 p_base_key_column => 'eligy_prfl_id',
956 p_base_key_value => p_eligy_prfl_id,
957 p_from_date => p_validation_start_date,
958 p_to_date => p_validation_end_date))) Then
959 l_table_name := 'ben_eligy_prfl_f';
960 Raise l_integrity_error;
961 End If;
962 --
963 end if;
964 --
965 hr_utility.set_location(' Leaving:'||l_proc, 10);
966 Exception
967 When l_integrity_error Then
968 --
969 -- A referential integrity check was violated therefore
970 -- we must error
971 --
972 ben_utility.parent_integrity_error(p_table_name => l_table_name);
973 When Others Then
974 --
975 -- An unhandled or unexpected error has occurred which
976 -- we must report
977 --
978 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
979 hr_utility.set_message_token('PROCEDURE', l_proc);
980 hr_utility.set_message_token('STEP','15');
981 hr_utility.raise_error;
982 end dt_update_validate;
986 -- {Start Of Comments}
983 -- ----------------------------------------------------------------------------
984 -- |--------------------------< dt_delete_validate >--------------------------|
985 -- ----------------------------------------------------------------------------
987 --
988 -- Description:
989 -- This procedure is used for referential integrity of datetracked
990 -- child entities when either a datetrack DELETE or ZAP is in operation
991 -- and where there is no cascading of delete defined for this entity.
992 -- For the datetrack mode of DELETE or ZAP we must ensure that no
993 -- datetracked child rows exist between the validation start and end
994 -- dates.
995 --
996 -- Prerequisites:
997 -- This procedure is called from the delete_validate.
998 --
999 -- In Parameters:
1000 --
1001 -- Post Success:
1002 -- Processing continues.
1003 --
1004 -- Post Failure:
1005 -- If a row exists by determining the returning Boolean value from the
1006 -- generic dt_api.rows_exist function then we must supply an error via
1007 -- the use of the local exception handler l_rows_exist.
1008 --
1009 -- Developer Implementation Notes:
1010 -- This procedure should not need maintenance unless the HR Schema model
1011 -- changes.
1012 --
1013 -- Access Status:
1014 -- Internal Row Handler Use Only.
1015 --
1016 -- {End Of Comments}
1017 -- ----------------------------------------------------------------------------
1018 Procedure dt_delete_validate
1019 (p_eligy_crit_values_id in number,
1020 p_datetrack_mode in varchar2,
1021 p_validation_start_date in date,
1022 p_validation_end_date in date) Is
1023 --
1024 l_proc varchar2(72) := g_package||'dt_delete_validate';
1025 l_rows_exist Exception;
1026 l_table_name all_tables.table_name%TYPE;
1027 --
1028 Begin
1029 hr_utility.set_location('Entering:'||l_proc, 5);
1030 --
1031 -- Ensure that the p_datetrack_mode argument is not null
1032 --
1033 hr_api.mandatory_arg_error
1034 (p_api_name => l_proc,
1035 p_argument => 'datetrack_mode',
1036 p_argument_value => p_datetrack_mode);
1037 --
1038 -- Only perform the validation if the datetrack mode is either
1039 -- DELETE or ZAP
1040 --
1041 If (p_datetrack_mode = 'DELETE' or
1042 p_datetrack_mode = 'ZAP') then
1043 --
1044 --
1045 -- Ensure the arguments are not null
1046 --
1047 hr_api.mandatory_arg_error
1048 (p_api_name => l_proc,
1049 p_argument => 'validation_start_date',
1050 p_argument_value => p_validation_start_date);
1051 --
1052 hr_api.mandatory_arg_error
1053 (p_api_name => l_proc,
1054 p_argument => 'validation_end_date',
1055 p_argument_value => p_validation_end_date);
1056 --
1057 hr_api.mandatory_arg_error
1058 (p_api_name => l_proc,
1059 p_argument => 'eligy_crit_values_id',
1060 p_argument_value => p_eligy_crit_values_id);
1061 --
1062 end if;
1063 --
1064 end dt_delete_validate;
1065 --
1066 -- ----------------------------------------------------------------------------
1067 -- |---------------------------< insert_validate >----------------------------|
1068 -- ----------------------------------------------------------------------------
1069 Procedure insert_validate
1070 (p_rec in ben_ecv_shd.g_rec_type,
1071 p_effective_date in date,
1072 p_datetrack_mode in varchar2,
1073 p_validation_start_date in date,
1074 p_validation_end_date in date) is
1075 --
1076 l_proc varchar2(72) := g_package||'insert_validate';
1077 --
1078 begin
1079 hr_utility.set_location('Entering:'||l_proc, 5);
1080 --
1081 -- Call all supporting business operations
1082 --
1083 --
1084 if p_rec.business_group_id is not null and p_rec.legislation_code is null then
1085 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
1086 end if;
1087 --
1088 chk_eligy_crit_values_id( p_eligy_crit_values_id => p_rec.eligy_crit_values_id,
1089 p_effective_date => p_effective_date,
1090 p_object_version_number => p_rec.object_version_number );
1091 --
1092 chk_eligy_criteria_id( p_eligy_crit_values_id => p_rec.eligy_crit_values_id,
1093 p_eligy_criteria_id => p_rec.eligy_criteria_id,
1094 p_effective_date => p_effective_date,
1095 p_object_version_number => p_rec.object_version_number );
1096 --
1097 chk_eligy_prfl_id( p_eligy_crit_values_id => p_rec.eligy_crit_values_id,
1098 p_eligy_prfl_id => p_rec.eligy_prfl_id,
1099 p_effective_date => p_effective_date,
1100 p_object_version_number => p_rec.object_version_number );
1101 --
1102 chk_duplicate_eligy_criteria (p_eligy_crit_values_id => p_rec.eligy_crit_values_id,
1103 p_eligy_prfl_id => p_rec.eligy_prfl_id,
1104 p_eligy_criteria_id => p_rec.eligy_criteria_id,
1105 p_number_value1 => p_rec.number_value1,
1106 p_char_value1 => p_rec.char_value1,
1107 p_date_value1 => p_rec.date_value1,
1108 p_number_value2 => p_rec.number_value2,
1109 p_char_value2 => p_rec.char_value2,
1110 p_date_value2 => p_rec.date_value2,
1111 p_number_value3 => p_rec.number_value3,
1115 p_char_value4 => p_rec.char_value4,
1112 p_char_value3 => p_rec.char_value3,
1113 p_date_value3 => p_rec.date_value3,
1114 p_number_value4 => p_rec.number_value4,
1116 p_date_value4 => p_rec.date_value4,
1117 p_validation_start_date => p_validation_start_date,
1118 p_validation_end_date => p_validation_end_date,
1119 p_business_group_id => p_rec.business_group_id );
1120
1121 --
1122 chk_duplicate_ordr_num ( p_eligy_crit_values_id => p_rec.eligy_crit_values_id,
1123 p_eligy_prfl_id => p_rec.eligy_prfl_id,
1124 p_ordr_num => p_rec.ordr_num,
1125 p_validation_start_date => p_validation_start_date,
1126 p_validation_end_date => p_validation_end_date,
1127 p_business_group_id => p_rec.business_group_id );
1128 --
1129 chk_required_fields ( p_eligy_crit_values_id => p_rec.eligy_crit_values_id,
1130 p_eligy_criteria_id => p_rec.eligy_criteria_id,
1131 p_number_value1 => p_rec.number_value1,
1132 p_char_value1 => p_rec.char_value1,
1133 p_date_Value1 => p_rec.date_Value1,
1134 p_number_value2 => p_rec.number_value2,
1135 p_char_value2 => p_rec.char_value2,
1136 p_date_Value2 => p_rec.date_Value2,
1137 p_number_value3 => p_rec.number_value3,
1138 p_char_value3 => p_rec.char_value3,
1139 p_date_Value3 => p_rec.date_Value3,
1140 p_number_value4 => p_rec.number_value4,
1141 p_char_value4 => p_rec.char_value4,
1142 p_date_Value4 => p_rec.date_Value4,
1143 p_business_group_id => p_rec.business_group_id,
1144 p_effective_date => p_effective_date) ;
1145 --
1146 hr_utility.set_location(' Leaving:'||l_proc, 10);
1147 --
1148 end insert_validate;
1149 --
1150 -- ----------------------------------------------------------------------------
1151 -- |---------------------------< update_validate >----------------------------|
1152 -- ----------------------------------------------------------------------------
1153 Procedure update_validate
1154 (p_rec in ben_ecv_shd.g_rec_type,
1155 p_effective_date in date,
1156 p_datetrack_mode in varchar2,
1157 p_validation_start_date in date,
1158 p_validation_end_date in date) is
1159 --
1160 l_proc varchar2(72) := g_package||'update_validate';
1161 begin
1162 --
1163 hr_utility.set_location('Entering:'||l_proc, 5);
1164 --
1165 -- Call all supporting business operations
1166 --
1167 --
1168 if p_rec.business_group_id is not null and p_rec.legislation_code is null
1169 then
1170 --
1171 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
1172 --
1173 end if;
1174 --
1175 chk_eligy_crit_values_id( p_eligy_crit_values_id => p_rec.eligy_crit_values_id,
1176 p_effective_date => p_effective_date,
1177 p_object_version_number => p_rec.object_version_number );
1178 --
1179 chk_eligy_criteria_id( p_eligy_crit_values_id => p_rec.eligy_crit_values_id,
1180 p_eligy_criteria_id => p_rec.eligy_criteria_id,
1181 p_effective_date => p_effective_date,
1182 p_object_version_number => p_rec.object_version_number );
1183 --
1184 chk_eligy_prfl_id( p_eligy_crit_values_id => p_rec.eligy_crit_values_id,
1185 p_eligy_prfl_id => p_rec.eligy_prfl_id,
1186 p_effective_date => p_effective_date,
1187 p_object_version_number => p_rec.object_version_number );
1188 --
1189 chk_duplicate_eligy_criteria (p_eligy_crit_values_id => p_rec.eligy_crit_values_id,
1190 p_eligy_prfl_id => p_rec.eligy_prfl_id,
1191 p_eligy_criteria_id => p_rec.eligy_criteria_id,
1192 p_number_value1 => p_rec.number_value1,
1193 p_char_value1 => p_rec.char_value1,
1194 p_date_value1 => p_rec.date_value1,
1195 p_number_value2 => p_rec.number_value2,
1196 p_char_value2 => p_rec.char_value2,
1197 p_date_value2 => p_rec.date_value2,
1198 p_number_value3 => p_rec.number_value3,
1199 p_char_value3 => p_rec.char_value3,
1200 p_date_value3 => p_rec.date_value3,
1201 p_number_value4 => p_rec.number_value4,
1202 p_char_value4 => p_rec.char_value4,
1203 p_date_value4 => p_rec.date_value4,
1204 p_validation_start_date => p_validation_start_date,
1205 p_validation_end_date => p_validation_end_date,
1206 p_business_group_id => p_rec.business_group_id );
1207 --
1208 chk_duplicate_ordr_num ( p_eligy_crit_values_id => p_rec.eligy_crit_values_id,
1212 p_validation_end_date => p_validation_end_date,
1209 p_eligy_prfl_id => p_rec.eligy_prfl_id,
1210 p_ordr_num => p_rec.ordr_num,
1211 p_validation_start_date => p_validation_start_date,
1213 p_business_group_id => p_rec.business_group_id );
1214 --
1215 chk_required_fields ( p_eligy_crit_values_id => p_rec.eligy_crit_values_id,
1216 p_eligy_criteria_id => p_rec.eligy_criteria_id,
1217 p_number_value1 => p_rec.number_value1,
1218 p_char_value1 => p_rec.char_value1,
1219 p_date_Value1 => p_rec.date_Value1,
1220 p_number_value2 => p_rec.number_value2,
1221 p_char_value2 => p_rec.char_value2,
1222 p_date_Value2 => p_rec.date_Value2,
1223 p_number_value3 => p_rec.number_value3,
1224 p_char_value3 => p_rec.char_value3,
1225 p_date_Value3 => p_rec.date_Value3,
1226 p_number_value4 => p_rec.number_value4,
1227 p_char_value4 => p_rec.char_value4,
1228 p_date_Value4 => p_rec.date_Value4,
1229 p_business_group_id => p_rec.business_group_id,
1230 p_effective_date => p_effective_date) ;
1231 --
1232 -- Call the datetrack update integrity operation
1233 --
1234 dt_update_validate( p_eligy_criteria_id => p_rec.eligy_criteria_id,
1235 p_eligy_prfl_id => p_rec.eligy_prfl_id,
1236 p_datetrack_mode => p_datetrack_mode,
1237 p_validation_start_date => p_validation_start_date,
1238 p_validation_end_date => p_validation_end_date );
1239 --
1240 --
1241 hr_utility.set_location(' Leaving:'||l_proc, 10);
1242 --
1243 end update_validate;
1244 --
1245 -- ----------------------------------------------------------------------------
1246 -- |---------------------------< delete_validate >-----------------------------|
1247 -- ----------------------------------------------------------------------------
1248 Procedure delete_validate
1249 (p_rec in ben_ecv_shd.g_rec_type,
1250 p_effective_date in date,
1251 p_datetrack_mode in varchar2,
1252 p_validation_start_date in date,
1253 p_validation_end_date in date) is
1254 --
1255 l_proc varchar2(72) := g_package||'delete_validate';
1256 Begin
1257 hr_utility.set_location('Entering:'||l_proc, 5);
1258 --
1259 -- Call all supporting business operations
1260 --
1261 chk_eligy_crit_values_id
1262 (
1263 p_eligy_crit_values_id => p_rec.eligy_crit_values_id,
1264 p_effective_date => p_effective_date,
1265 p_object_version_number => p_rec.object_version_number
1266 );
1267 --
1268 dt_delete_validate
1269 (
1270 p_eligy_crit_values_id => p_rec.eligy_crit_values_id,
1271 p_datetrack_mode => p_datetrack_mode,
1272 p_validation_start_date => p_validation_start_date,
1273 p_validation_end_date => p_validation_end_date
1274 );
1275 end delete_validate;
1276 --
1277 -- ---------------------------------------------------------------------------
1278 -- |---------------------< return_legislation_code >-------------------------|
1279 -- ---------------------------------------------------------------------------
1280 function return_legislation_code
1281 (p_eligy_crit_values_id in number) return varchar2 is
1282 --
1283 -- Declare cursor
1284 --
1285 cursor csr_leg_code is
1286 select a.legislation_code
1287 from per_business_groups a,
1288 ben_eligy_crit_values_f b
1289 where b.eligy_crit_values_id = p_eligy_crit_values_id
1290 and a.business_group_id = b.business_group_id;
1291 --
1292 -- Declare local variables
1293 --
1294 l_legislation_code varchar2(150);
1295 l_proc varchar2(72) := g_package||'return_legislation_code';
1296 --
1297 begin
1298 --
1299 hr_utility.set_location('Entering:'|| l_proc, 10);
1300 --
1301 -- Ensure that all the mandatory parameter are not null
1302 --
1303 hr_api.mandatory_arg_error(p_api_name => l_proc,
1304 p_argument => 'eligy_crit_values_id',
1305 p_argument_value => p_eligy_crit_values_id);
1306 --
1307 open csr_leg_code;
1308 --
1309 fetch csr_leg_code into l_legislation_code;
1310
1311 --
1312 if csr_leg_code%notfound then
1313 --
1314 close csr_leg_code;
1315 --
1316 -- The primary key is invalid therefore we must error
1317 --
1318 hr_utility.set_message(801,'HR_7220_INVALID_PRIMARY_KEY');
1319 hr_utility.raise_error;
1320 --
1321 end if;
1322 --
1323 close csr_leg_code;
1324 --
1325 hr_utility.set_location(' Leaving:'|| l_proc, 20);
1326 --
1327 return l_legislation_code;
1328
1329 --
1330 end return_legislation_code;
1331 --
1332 end ben_ecv_bus;
1333 --