[Home] [Help]
PACKAGE BODY: APPS.BEN_DPNT_EDC_BUS
Source
1 Package body ben_dpnt_edc_bus as
2 /* $Header: beedvrhi.pkb 120.2 2010/05/18 07:23:55 sgnanama noship $ */
3 --
4 --
5 -- ----------------------------------------------------------------------------
6 -- | Private Global Definitions |
7 -- ----------------------------------------------------------------------------
8 --
9 g_package varchar2(33) := ' ben_dpnt_edc_bus.'; -- Global package name
10 --
11 -- ----------------------------------------------------------------------------
12 -- |------< chk_dpnt_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
21 -- None.
18 -- should not be able to be updated.
19 --
20 -- Pre Conditions
22 --
23 -- In Parameters
24 -- dpnt_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_dpnt_eligy_crit_values_id (
39 p_dpnt_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_dpnt_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_dpnt_edc_shd.api_updating
52 (p_effective_date => p_effective_date,
53 p_dpnt_eligy_crit_values_id => p_dpnt_eligy_crit_values_id,
54 p_object_version_number => p_object_version_number
55 );
56 --
57 if (l_api_updating
58 and nvl(p_dpnt_eligy_crit_values_id,hr_api.g_number)
59 <> ben_dpnt_edc_shd.g_old_rec.dpnt_eligy_crit_values_id) then
60 --
61 -- raise error as PK has changed
62 --
63 ben_dpnt_edc_shd.constraint_error('ben_dpnt_eligy_crit_values_f_PK');
64 --
65 elsif not l_api_updating then
66 --
67 --check if PK is NULL
68 --
69 if p_dpnt_eligy_crit_values_id is not null then
70 --
71 -- raise error as PK is not null
72 --
73 ben_dpnt_edc_shd.constraint_error('ben_dpnt_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_dpnt_eligy_crit_values_id;
82 --
83 -- ----------------------------------------------------------------------------
84 -- |----------------------------< chk_eligy_criteria_dpnt_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_dpnt_eligy_crit_values_id PK
96 -- p_eligy_criteria_dpnt_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_dpnt_id(
109 p_dpnt_eligy_crit_values_id In Number,
110 p_eligy_criteria_dpnt_id In Number,
111 p_effective_date In Date,
112 p_object_version_number In Number
113 ) is
114 --
115 l_proc varchar2(72) := g_package||'chk_eligy_criteria_dpnt_id';
116 l_api_updating boolean;
117 l_dummy varchar2(1);
118 --
119 Cursor csr_eligy_criteria_dpnt_id is
120 select NULL
121 from ben_eligy_criteria_dpnt
122 where eligy_criteria_dpnt_id = p_eligy_criteria_dpnt_id;
123 begin
124 --
125 hr_utility.set_location('Entering:'||l_proc, 5);
126 --
127 l_api_updating := ben_dpnt_edc_shd.api_updating(
128 p_dpnt_eligy_crit_values_id => p_dpnt_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_dpnt_id,hr_api.g_number)
135 <> ben_dpnt_edc_shd.g_old_rec.eligy_criteria_dpnt_id
136 or not l_api_updating) then
137 --
138 open csr_eligy_criteria_dpnt_id;
139 --
140 fetch csr_eligy_criteria_dpnt_id into l_dummy;
141 --
142 if csr_eligy_criteria_dpnt_id%notfound then
143 --
144 close csr_eligy_criteria_dpnt_id;
145 --
146 --Raise an error
147 --
148 ben_dpnt_edc_shd.constraint_error('ben_dpnt_eligy_crit_values_fK1');
149 --
150 end if;
151 --
152 close csr_eligy_criteria_dpnt_id;
153 --
154 end if;
155 --
156 hr_utility.set_location('Leaving:'||l_proc, 10);
157 --
158 end chk_eligy_criteria_dpnt_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_dpnt_eligy_crit_values_id PK
173 -- p_eligy_prfl_id ID of FK column
174 -- p_object_version_number object version number
175 --
179 -- Post Failure
176 -- Post Success
177 -- Processing continues
178 --
180 -- Error raised.
181 --
182 -- Access Status
183 -- Internal table handler use only.
184 --
185 procedure chk_eligy_prfl_id(
186 p_dpnt_eligy_crit_values_id In Number,
187 p_dpnt_cvg_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_dpnt_cvg_eligy_prfl_f
199 where dpnt_cvg_eligy_prfl_id = p_dpnt_cvg_eligy_prfl_id;
200 begin
201 --
202 hr_utility.set_location('Entering:'||l_proc, 5);
203 --
204 l_api_updating := ben_dpnt_edc_shd.api_updating(
205 p_dpnt_eligy_crit_values_id => p_dpnt_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_dpnt_cvg_eligy_prfl_id,hr_api.g_number)
212 <> ben_dpnt_edc_shd.g_old_rec.dpnt_cvg_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_dpnt_edc_shd.constraint_error('ben_dpnt_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_dpnt_eligy_crit_values_id in number,
245 p_dpnt_cvg_eligy_prfl_id in number,
246 p_eligy_criteria_dpnt_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,
260 p_validation_end_date in date,
261 p_business_group_id in number
262 ) is
263 --
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_dpnt_eligy_crit_values_f edc
270 where edc.dpnt_cvg_eligy_prfl_id = p_dpnt_cvg_eligy_prfl_id
271 and edc.eligy_criteria_dpnt_id = p_eligy_criteria_dpnt_id
272 and edc.dpnt_eligy_crit_values_id <> nvl ( p_dpnt_eligy_crit_values_id, -1)
273 and nvl(edc.number_value1,hr_api.g_number) = nvl(p_number_value1,hr_api.g_number)
274 and nvl(edc.number_value2,hr_api.g_number) = nvl(p_number_value2,hr_api.g_number)
275 and nvl(edc.char_value1,hr_api.g_varchar2) = nvl(p_char_value1,hr_api.g_varchar2)
276 and nvl(edc.char_value2,hr_api.g_varchar2) = nvl(p_char_value2,hr_api.g_varchar2)
277 and nvl(edc.date_value1,hr_api.g_date) = nvl (p_date_value1,hr_api.g_date)
278 and nvl(edc.date_value2,hr_api.g_date) = nvl (p_date_value2,hr_api.g_date)
279 and nvl(edc.number_value3,hr_api.g_number) = nvl(p_number_value3,hr_api.g_number)
280 and nvl(edc.number_value4,hr_api.g_number) = nvl(p_number_value4,hr_api.g_number)
281 and nvl(edc.char_value3,hr_api.g_varchar2) = nvl(p_char_value3,hr_api.g_varchar2)
282 and nvl(edc.char_value4,hr_api.g_varchar2) = nvl(p_char_value4,hr_api.g_varchar2)
283 and nvl(edc.date_value3,hr_api.g_date) = nvl (p_date_value3,hr_api.g_date)
284 and nvl(edc.date_value4,hr_api.g_date) = nvl (p_date_value4,hr_api.g_date)
285 and edc.effective_start_date <= p_validation_end_date
286 and edc.effective_end_date >= p_validation_start_date
287 and edc.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 --
316 --
313 -- ---------------------------------------------------------------------------
314 -- |-----------------------< chk_duplicate_ordr_num >---------------------------|
315 -- ---------------------------------------------------------------------------
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_dpnt_eligy_crit_values_id dpnt_eligy_crit_values_id
326 -- p_dpnt_cvg_eligy_prfl_id dpnt_cvg_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_dpnt_eligy_crit_values_id in number,
344 p_dpnt_cvg_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_dpnt_eligy_crit_values_f
357 where dpnt_eligy_crit_values_id <> nvl( p_dpnt_eligy_crit_values_id, -1 )
358 and dpnt_cvg_eligy_prfl_id = p_dpnt_cvg_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_dpnt_eligy_crit_values_id in number,
402 p_eligy_criteria_dpnt_id in number,
403 p_number_value1 in number,
404 p_char_value1 in varchar2,
405 p_date_Value1 in date,
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_dpnt egl
436 where egl.eligy_criteria_dpnt_id = p_eligy_criteria_dpnt_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 --
450 where ose.org_structure_version_id = p_number_value1
447 cursor c_start_org is
448 select null
449 from per_org_structure_elements ose
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
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
534 --
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 )
583 fnd_message.raise_error;
580 then
581 --
582 fnd_message.set_name('BEN', 'BEN_94141_VSET_VALUE_NULL');
584 --
585 end if;
586 --
587 --
588 end if;
589 --
590 -- Set 2 validations added rbingi
591 if l_crit_col2_val_type_cd = 'LOOKUP'
592 then
593 --
594 if ( l_crit_col2_datatype = 'N' and p_number_value3 is null ) OR
595 ( l_crit_col2_datatype = 'N' and l_allow_range_validation_flag2 = 'Y' and p_number_value4 is null ) OR
596 ( l_crit_col2_datatype = 'D' and p_date_value3 is null ) OR
597 ( l_crit_col2_datatype = 'D' and l_allow_range_validation_flag2 = 'Y' and p_date_value4 is null ) OR
598 ( l_crit_col2_datatype = 'C' and p_char_value3 is null ) OR
599 ( l_crit_col2_datatype = 'C' and l_allow_range_validation_flag2 = 'Y' and p_char_value4 is null )
600 then
601 --
602 fnd_message.set_name('BEN', 'BEN_94140_LOOKUP_VALUE_NULL');
603 fnd_message.raise_error;
604 --
605 else
606 --
607 if l_crit_col2_datatype = 'N'
608 then
609 l_lookup_code := p_number_value3;
610 elsif l_crit_col2_datatype = 'C'
611 then
612 l_lookup_code := p_char_value3;
613 elsif l_crit_col2_datatype = 'D'
614 then
615 l_lookup_code := p_date_value3;
616 end if;
617 --
618 if hr_api.not_exists_in_hr_lookups
619 (p_lookup_type => l_col2_lookup_type,
620 p_lookup_code => l_lookup_code,
621 p_effective_date => p_effective_date) then
622 --
623 -- raise error as code does not exist as lookup
624 --
625 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
626 fnd_message.set_token('FIELD', 'Lookup Value');
627 fnd_message.set_token('TYPE',l_col2_lookup_type);
628 fnd_message.set_token('VALUE',l_lookup_code);
629 fnd_message.raise_error;
630 --
631 end if;
632 --
633 end if;
634 --
635 --
636 -- Value Set
637 --
638 elsif l_crit_col2_val_type_cd = 'VAL_SET'
639 then
640 --
641 if ( l_crit_col2_datatype = 'N' and p_number_value3 is null ) OR
642 ( l_crit_col2_datatype = 'N' and l_allow_range_validation_flag2 = 'Y' and p_number_value4 is null ) OR
643 ( l_crit_col2_datatype = 'D' and p_date_value3 is null ) OR
644 ( l_crit_col2_datatype = 'D' and l_allow_range_validation_flag2 = 'Y' and p_date_value4 is null ) OR
645 ( l_crit_col2_datatype = 'C' and p_char_value3 is null ) OR
646 ( l_crit_col2_datatype = 'C' and l_allow_range_validation_flag2 = 'Y' and p_char_value4 is null )
647 then
648 --
649 fnd_message.set_name('BEN', 'BEN_94141_VSET_VALUE_NULL');
650 fnd_message.raise_error;
651 --
652 end if;
653 --
654 end if;
655 --
656 end if;
657 --
658 close c_eligy_criteria;
659 --
660 hr_utility.set_location('Leaving:'||l_proc, 10);
661 --
662 end chk_required_fields;
663 --
664 -- ----------------------------------------------------------------------------
665 -- |--------------------------< dt_update_validate >--------------------------|
666 -- ----------------------------------------------------------------------------
667 -- {Start Of Comments}
668 --
669 -- Description:
670 -- This procedure is used for referential integrity of datetracked
671 -- parent entities when a datetrack update operation is taking place
672 -- and where there is no cascading of update defined for this entity.
673 --
674 -- Prerequisites:
675 -- This procedure is called from the update_validate.
676 --
677 -- In Parameters:
678 --
679 -- Post Success:
680 -- Processing continues.
681 --
682 -- Post Failure:
683 --
684 -- Developer Implementation Notes:
685 -- This procedure should not need maintenance unless the HR Schema model
686 -- changes.
687 --
688 -- Access Status:
689 -- Internal Row Handler Use Only.
690 --
691 -- {End Of Comments}
692 -- ----------------------------------------------------------------------------
693 procedure dt_update_validate
694 (p_eligy_criteria_dpnt_id In number default hr_api.g_number,
695 p_dpnt_cvg_eligy_prfl_id In number default hr_api.g_number,
696 p_datetrack_mode In Varchar2,
697 p_validation_start_date In Date,
698 p_validation_end_date In Date
699 ) Is
700 --
701 l_proc varchar2(72) := g_package||'dt_update_validate';
702 l_integrity_error Exception;
703 l_table_name all_tables.table_name%TYPE;
704 --
705 Begin
706 hr_utility.set_location('Entering:'||l_proc, 5);
707 --
708 -- Ensure that the p_datetrack_mode argument is not null
709 --
710 hr_api.mandatory_arg_error
711 (p_api_name => l_proc,
712 p_argument => 'datetrack_mode',
713 p_argument_value => p_datetrack_mode);
714 --
715 -- Only perform the validation if the datetrack update mode is valid
716 --
717 If (dt_api.validate_dt_upd_mode(p_datetrack_mode => p_datetrack_mode)) then
718 --
719 --
720 -- Ensure the arguments are not null
721 --
722 hr_api.mandatory_arg_error
723 (p_api_name => l_proc,
727 hr_api.mandatory_arg_error
724 p_argument => 'validation_start_date',
725 p_argument_value => p_validation_start_date);
726 --
728 (p_api_name => l_proc,
729 p_argument => 'validation_end_date',
730 p_argument_value => p_validation_end_date);
731 --
732 /* If ((nvl(p_eligy_criteria_dpnt_id, hr_api.g_number) <> hr_api.g_number) and
733 NOT (dt_api.check_min_max_dates
734 (p_base_table_name => 'ben_eligy_criteria',
735 p_base_key_column => 'eligy_criteria_dpnt_id',
736 p_base_key_value => p_eligy_criteria_dpnt_id,
737 p_from_date => p_validation_start_date,
738 p_to_date => p_validation_end_date))) Then
739 l_table_name := 'ben_eligy_criteria';
740 Raise l_integrity_error;
741 End If;*/
742 --
743 If ((nvl(p_dpnt_cvg_eligy_prfl_id, hr_api.g_number) <> hr_api.g_number) and
744 NOT (dt_api.check_min_max_dates
745 (p_base_table_name => 'ben_dpnt_cvg_eligy_prfl_f',
746 p_base_key_column => 'dpnt_cvg_eligy_prfl_id',
747 p_base_key_value => p_dpnt_cvg_eligy_prfl_id,
748 p_from_date => p_validation_start_date,
749 p_to_date => p_validation_end_date))) Then
750 l_table_name := 'ben_eligy_prfl_f';
751 Raise l_integrity_error;
752 End If;
753 --
754 end if;
755 --
756 hr_utility.set_location(' Leaving:'||l_proc, 10);
757 Exception
758 When l_integrity_error Then
759 --
760 -- A referential integrity check was violated therefore
761 -- we must error
762 --
763 ben_utility.parent_integrity_error(p_table_name => l_table_name);
764 When Others Then
765 --
766 -- An unhandled or unexpected error has occurred which
767 -- we must report
768 --
769 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
770 hr_utility.set_message_token('PROCEDURE', l_proc);
771 hr_utility.set_message_token('STEP','15');
772 hr_utility.raise_error;
773 end dt_update_validate;
774 -- ----------------------------------------------------------------------------
775 -- |--------------------------< dt_delete_validate >--------------------------|
776 -- ----------------------------------------------------------------------------
777 -- {Start Of Comments}
778 --
779 -- Description:
780 -- This procedure is used for referential integrity of datetracked
781 -- child entities when either a datetrack DELETE or ZAP is in operation
782 -- and where there is no cascading of delete defined for this entity.
783 -- For the datetrack mode of DELETE or ZAP we must ensure that no
784 -- datetracked child rows exist between the validation start and end
785 -- dates.
786 --
787 -- Prerequisites:
788 -- This procedure is called from the delete_validate.
789 --
790 -- In Parameters:
791 --
792 -- Post Success:
793 -- Processing continues.
794 --
795 -- Post Failure:
796 -- If a row exists by determining the returning Boolean value from the
797 -- generic dt_api.rows_exist function then we must supply an error via
798 -- the use of the local exception handler l_rows_exist.
799 --
800 -- Developer Implementation Notes:
801 -- This procedure should not need maintenance unless the HR Schema model
802 -- changes.
803 --
804 -- Access Status:
805 -- Internal Row Handler Use Only.
806 --
807 -- {End Of Comments}
808 -- ----------------------------------------------------------------------------
809 Procedure dt_delete_validate
810 (p_dpnt_eligy_crit_values_id in number,
811 p_datetrack_mode in varchar2,
812 p_validation_start_date in date,
813 p_validation_end_date in date) Is
814 --
815 l_proc varchar2(72) := g_package||'dt_delete_validate';
816 l_rows_exist Exception;
817 l_table_name all_tables.table_name%TYPE;
818 --
819 Begin
820 hr_utility.set_location('Entering:'||l_proc, 5);
821 --
822 -- Ensure that the p_datetrack_mode argument is not null
823 --
824 hr_api.mandatory_arg_error
825 (p_api_name => l_proc,
826 p_argument => 'datetrack_mode',
827 p_argument_value => p_datetrack_mode);
828 --
829 -- Only perform the validation if the datetrack mode is either
830 -- DELETE or ZAP
831 --
832 If (p_datetrack_mode = 'DELETE' or
833 p_datetrack_mode = 'ZAP') then
834 --
835 --
836 -- Ensure the arguments are not null
837 --
838 hr_api.mandatory_arg_error
839 (p_api_name => l_proc,
840 p_argument => 'validation_start_date',
841 p_argument_value => p_validation_start_date);
842 --
843 hr_api.mandatory_arg_error
844 (p_api_name => l_proc,
845 p_argument => 'validation_end_date',
846 p_argument_value => p_validation_end_date);
847 --
848 hr_api.mandatory_arg_error
849 (p_api_name => l_proc,
850 p_argument => 'dpnt_eligy_crit_values_id',
851 p_argument_value => p_dpnt_eligy_crit_values_id);
852 --
853 end if;
854 --
855 end dt_delete_validate;
856 --
857 -- ----------------------------------------------------------------------------
858 -- |---------------------------< insert_validate >----------------------------|
859 -- ----------------------------------------------------------------------------
860 Procedure insert_validate
861 (p_rec in ben_dpnt_edc_shd.g_rec_type,
862 p_effective_date in date,
863 p_datetrack_mode in varchar2,
867 l_proc varchar2(72) := g_package||'insert_validate';
864 p_validation_start_date in date,
865 p_validation_end_date in date) is
866 --
868 --
869 begin
870 hr_utility.set_location('Entering:'||l_proc, 5);
871 --
872 -- Call all supporting business operations
873 --
874 --
875 if p_rec.business_group_id is not null then
876 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
877 end if;
878 --
879 chk_dpnt_eligy_crit_values_id( p_dpnt_eligy_crit_values_id => p_rec.dpnt_eligy_crit_values_id,
880 p_effective_date => p_effective_date,
881 p_object_version_number => p_rec.object_version_number );
882 --
883 chk_eligy_criteria_dpnt_id( p_dpnt_eligy_crit_values_id => p_rec.dpnt_eligy_crit_values_id,
884 p_eligy_criteria_dpnt_id => p_rec.eligy_criteria_dpnt_id,
885 p_effective_date => p_effective_date,
886 p_object_version_number => p_rec.object_version_number );
887 --
888 chk_eligy_prfl_id( p_dpnt_eligy_crit_values_id => p_rec.dpnt_eligy_crit_values_id,
889 p_dpnt_cvg_eligy_prfl_id => p_rec.dpnt_cvg_eligy_prfl_id,
890 p_effective_date => p_effective_date,
891 p_object_version_number => p_rec.object_version_number );
892 --
893 chk_duplicate_eligy_criteria (p_dpnt_eligy_crit_values_id => p_rec.dpnt_eligy_crit_values_id,
894 p_dpnt_cvg_eligy_prfl_id => p_rec.dpnt_cvg_eligy_prfl_id,
895 p_eligy_criteria_dpnt_id => p_rec.eligy_criteria_dpnt_id,
896 p_number_value1 => p_rec.number_value1,
897 p_char_value1 => p_rec.char_value1,
898 p_date_value1 => p_rec.date_value1,
899 p_number_value2 => p_rec.number_value2,
900 p_char_value2 => p_rec.char_value2,
901 p_date_value2 => p_rec.date_value2,
902 p_number_value3 => p_rec.number_value3,
903 p_char_value3 => p_rec.char_value3,
904 p_date_value3 => p_rec.date_value3,
905 p_number_value4 => p_rec.number_value4,
906 p_char_value4 => p_rec.char_value4,
907 p_date_value4 => p_rec.date_value4,
908 p_validation_start_date => p_validation_start_date,
909 p_validation_end_date => p_validation_end_date,
910 p_business_group_id => p_rec.business_group_id );
911
912 --
913 chk_duplicate_ordr_num ( p_dpnt_eligy_crit_values_id => p_rec.dpnt_eligy_crit_values_id,
914 p_dpnt_cvg_eligy_prfl_id => p_rec.dpnt_cvg_eligy_prfl_id,
915 p_ordr_num => p_rec.ordr_num,
916 p_validation_start_date => p_validation_start_date,
917 p_validation_end_date => p_validation_end_date,
918 p_business_group_id => p_rec.business_group_id );
919 --
920 chk_required_fields ( p_dpnt_eligy_crit_values_id => p_rec.dpnt_eligy_crit_values_id,
921 p_eligy_criteria_dpnt_id => p_rec.eligy_criteria_dpnt_id,
922 p_number_value1 => p_rec.number_value1,
923 p_char_value1 => p_rec.char_value1,
924 p_date_Value1 => p_rec.date_Value1,
925 p_number_value2 => p_rec.number_value2,
926 p_char_value2 => p_rec.char_value2,
927 p_date_Value2 => p_rec.date_Value2,
928 p_number_value3 => p_rec.number_value3,
929 p_char_value3 => p_rec.char_value3,
930 p_date_Value3 => p_rec.date_Value3,
931 p_number_value4 => p_rec.number_value4,
932 p_char_value4 => p_rec.char_value4,
933 p_date_Value4 => p_rec.date_Value4,
934 p_business_group_id => p_rec.business_group_id,
935 p_effective_date => p_effective_date) ;
936 --
937 hr_utility.set_location(' Leaving:'||l_proc, 10);
938 --
939 end insert_validate;
940 --
941 -- ----------------------------------------------------------------------------
942 -- |---------------------------< update_validate >----------------------------|
943 -- ----------------------------------------------------------------------------
944 Procedure update_validate
945 (p_rec in ben_dpnt_edc_shd.g_rec_type,
946 p_effective_date in date,
947 p_datetrack_mode in varchar2,
948 p_validation_start_date in date,
949 p_validation_end_date in date) is
950 --
951 l_proc varchar2(72) := g_package||'update_validate';
952 begin
953 --
954 hr_utility.set_location('Entering:'||l_proc, 5);
955 --
956 -- Call all supporting business operations
957 --
958 --
959 if p_rec.business_group_id is not null
960 then
961 --
962 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
963 --
964 end if;
965 --
969 --
966 chk_dpnt_eligy_crit_values_id( p_dpnt_eligy_crit_values_id => p_rec.dpnt_eligy_crit_values_id,
967 p_effective_date => p_effective_date,
968 p_object_version_number => p_rec.object_version_number );
970 chk_eligy_criteria_dpnt_id( p_dpnt_eligy_crit_values_id => p_rec.dpnt_eligy_crit_values_id,
971 p_eligy_criteria_dpnt_id => p_rec.eligy_criteria_dpnt_id,
972 p_effective_date => p_effective_date,
973 p_object_version_number => p_rec.object_version_number );
974 --
975 chk_eligy_prfl_id( p_dpnt_eligy_crit_values_id => p_rec.dpnt_eligy_crit_values_id,
976 p_dpnt_cvg_eligy_prfl_id => p_rec.dpnt_cvg_eligy_prfl_id,
977 p_effective_date => p_effective_date,
978 p_object_version_number => p_rec.object_version_number );
979 --
980 chk_duplicate_eligy_criteria (p_dpnt_eligy_crit_values_id => p_rec.dpnt_eligy_crit_values_id,
981 p_dpnt_cvg_eligy_prfl_id => p_rec.dpnt_cvg_eligy_prfl_id,
982 p_eligy_criteria_dpnt_id => p_rec.eligy_criteria_dpnt_id,
983 p_number_value1 => p_rec.number_value1,
984 p_char_value1 => p_rec.char_value1,
985 p_date_value1 => p_rec.date_value1,
986 p_number_value2 => p_rec.number_value2,
987 p_char_value2 => p_rec.char_value2,
988 p_date_value2 => p_rec.date_value2,
989 p_number_value3 => p_rec.number_value3,
990 p_char_value3 => p_rec.char_value3,
991 p_date_value3 => p_rec.date_value3,
992 p_number_value4 => p_rec.number_value4,
993 p_char_value4 => p_rec.char_value4,
994 p_date_value4 => p_rec.date_value4,
995 p_validation_start_date => p_validation_start_date,
996 p_validation_end_date => p_validation_end_date,
997 p_business_group_id => p_rec.business_group_id );
998 --
999 chk_duplicate_ordr_num ( p_dpnt_eligy_crit_values_id => p_rec.dpnt_eligy_crit_values_id,
1000 p_dpnt_cvg_eligy_prfl_id => p_rec.dpnt_cvg_eligy_prfl_id,
1001 p_ordr_num => p_rec.ordr_num,
1002 p_validation_start_date => p_validation_start_date,
1003 p_validation_end_date => p_validation_end_date,
1004 p_business_group_id => p_rec.business_group_id );
1005 --
1006 chk_required_fields ( p_dpnt_eligy_crit_values_id => p_rec.dpnt_eligy_crit_values_id,
1007 p_eligy_criteria_dpnt_id => p_rec.eligy_criteria_dpnt_id,
1008 p_number_value1 => p_rec.number_value1,
1009 p_char_value1 => p_rec.char_value1,
1010 p_date_Value1 => p_rec.date_Value1,
1011 p_number_value2 => p_rec.number_value2,
1012 p_char_value2 => p_rec.char_value2,
1013 p_date_Value2 => p_rec.date_Value2,
1014 p_number_value3 => p_rec.number_value3,
1015 p_char_value3 => p_rec.char_value3,
1016 p_date_Value3 => p_rec.date_Value3,
1017 p_number_value4 => p_rec.number_value4,
1018 p_char_value4 => p_rec.char_value4,
1019 p_date_Value4 => p_rec.date_Value4,
1020 p_business_group_id => p_rec.business_group_id,
1021 p_effective_date => p_effective_date) ;
1022 --
1023 -- Call the datetrack update integrity operation
1024 --
1025 dt_update_validate( p_eligy_criteria_dpnt_id => p_rec.eligy_criteria_dpnt_id,
1026 p_dpnt_cvg_eligy_prfl_id => p_rec.dpnt_cvg_eligy_prfl_id,
1027 p_datetrack_mode => p_datetrack_mode,
1028 p_validation_start_date => p_validation_start_date,
1029 p_validation_end_date => p_validation_end_date );
1030 --
1031 --
1032 hr_utility.set_location(' Leaving:'||l_proc, 10);
1033 --
1034 end update_validate;
1035 --
1036 -- ----------------------------------------------------------------------------
1037 -- |---------------------------< delete_validate >-----------------------------|
1038 -- ----------------------------------------------------------------------------
1039 Procedure delete_validate
1040 (p_rec in ben_dpnt_edc_shd.g_rec_type,
1041 p_effective_date in date,
1042 p_datetrack_mode in varchar2,
1043 p_validation_start_date in date,
1044 p_validation_end_date in date) is
1045 --
1046 l_proc varchar2(72) := g_package||'delete_validate';
1047 Begin
1048 hr_utility.set_location('Entering:'||l_proc, 5);
1049 --
1050 -- Call all supporting business operations
1051 --
1052 chk_dpnt_eligy_crit_values_id
1053 (
1054 p_dpnt_eligy_crit_values_id => p_rec.dpnt_eligy_crit_values_id,
1058 --
1055 p_effective_date => p_effective_date,
1056 p_object_version_number => p_rec.object_version_number
1057 );
1059 dt_delete_validate
1060 (
1061 p_dpnt_eligy_crit_values_id => p_rec.dpnt_eligy_crit_values_id,
1062 p_datetrack_mode => p_datetrack_mode,
1063 p_validation_start_date => p_validation_start_date,
1064 p_validation_end_date => p_validation_end_date
1065 );
1066 end delete_validate;
1067 --
1068 /*
1069 -- ---------------------------------------------------------------------------
1070 -- |---------------------< return_legislation_code >-------------------------|
1071 -- ---------------------------------------------------------------------------
1072 function return_legislation_code
1073 (p_dpnt_eligy_crit_values_id in number) return varchar2 is
1074 --
1075 -- Declare cursor
1076 --
1077 cursor csr_leg_code is
1078 select a.legislation_code
1079 from per_business_groups a,
1080 ben_dpnt_eligy_crit_values_f b
1081 where b.dpnt_eligy_crit_values_id = p_dpnt_eligy_crit_values_id
1082 and a.business_group_id = b.business_group_id;
1083 --
1084 -- Declare local variables
1085 --
1086 l_legislation_code varchar2(150);
1087 l_proc varchar2(72) := g_package||'return_legislation_code';
1088 --
1089 begin
1090 --
1091 hr_utility.set_location('Entering:'|| l_proc, 10);
1092 --
1093 -- Ensure that all the mandatory parameter are not null
1094 --
1095 hr_api.mandatory_arg_error(p_api_name => l_proc,
1096 p_argument => 'dpnt_eligy_crit_values_id',
1097 p_argument_value => p_dpnt_eligy_crit_values_id);
1098 --
1099 open csr_leg_code;
1100 --
1101 fetch csr_leg_code into l_legislation_code;
1102
1103 --
1104 if csr_leg_code%notfound then
1105 --
1106 close csr_leg_code;
1107 --
1108 -- The primary key is invalid therefore we must error
1109 --
1110 hr_utility.set_message(801,'HR_7220_INVALID_PRIMARY_KEY');
1111 hr_utility.raise_error;
1112 --
1113 end if;
1114 --
1115 close csr_leg_code;
1116 --
1117 hr_utility.set_location(' Leaving:'|| l_proc, 20);
1118 --
1119 return l_legislation_code;
1120
1121 --
1122 end return_legislation_code;*/
1123 --
1124 end ben_dpnt_edc_bus;
1125 --