1 Package Body pay_etp_bus as
2 /* $Header: pyetprhi.pkb 120.6.12010000.6 2009/07/30 11:56:55 npannamp ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pay_etp_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_element_type_id number default null;
15 --
16 -- ---------------------------------------------------------------------------
17 -- |----------------------< set_security_group_id >--------------------------|
18 -- ---------------------------------------------------------------------------
19 --
20 Procedure set_security_group_id
21 (p_element_type_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_element_types_f etp
31 where etp.element_type_id = p_element_type_id
32 and pbg.business_group_id = etp.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 => 'element_type_id'
48 ,p_argument_value => p_element_type_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,'ELEMENT_TYPE_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_element_type_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_element_types_f etp
95 where etp.element_type_id = p_element_type_id
96 and pbg.business_group_id (+) = etp.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 => 'element_type_id'
112 ,p_argument_value => p_element_type_id
113 );
114 --
115 if ( nvl(pay_etp_bus.g_element_type_id, hr_api.g_number)
116 = p_element_type_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_etp_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_etp_bus.g_element_type_id := p_element_type_id;
147 pay_etp_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 -- |-----------------------------< chk_ddf >----------------------------------|
155 -- ----------------------------------------------------------------------------
156 --
157 -- Description:
158 -- Validates all the Developer Descriptive Flexfield values.
159 --
160 -- Prerequisites:
161 -- All other columns have been validated. Must be called as the
162 -- last step from insert_validate and update_validate.
163 --
164 -- In Arguments:
165 -- p_rec
166 --
167 -- Post Success:
168 -- If the Developer Descriptive Flexfield structure column and data values
169 -- are all valid this procedure will end normally and processing will
170 -- continue.
171 --
172 -- Post Failure:
173 -- If the Developer Descriptive Flexfield structure column value or any of
174 -- the data values are invalid then an application error is raised as
175 -- a PL/SQL exception.
176 --
177 -- Access Status:
178 -- Internal Row Handler Use Only.
179 --
180 -- ----------------------------------------------------------------------------
181 procedure chk_ddf
182 (p_rec in pay_etp_shd.g_rec_type
183 ) is
184 --
185 l_proc varchar2(72) := g_package || 'chk_ddf';
186 --
187 begin
188 hr_utility.set_location('Entering:'||l_proc,10);
189 --
190 if ((p_rec.element_type_id is not null) and
191 (nvl(pay_etp_shd.g_old_rec.element_information_category,
192 hr_api.g_varchar2) <>
193 nvl(p_rec.element_information_category, hr_api.g_varchar2) or
194 nvl(pay_etp_shd.g_old_rec.element_information1, hr_api.g_varchar2) <>
195 nvl(p_rec.element_information1, hr_api.g_varchar2) or
196 nvl(pay_etp_shd.g_old_rec.element_information2, hr_api.g_varchar2) <>
197 nvl(p_rec.element_information2, hr_api.g_varchar2) or
198 nvl(pay_etp_shd.g_old_rec.element_information3, hr_api.g_varchar2) <>
199 nvl(p_rec.element_information3, hr_api.g_varchar2) or
200 nvl(pay_etp_shd.g_old_rec.element_information4, hr_api.g_varchar2) <>
201 nvl(p_rec.element_information4, hr_api.g_varchar2) or
202 nvl(pay_etp_shd.g_old_rec.element_information5, hr_api.g_varchar2) <>
203 nvl(p_rec.element_information5, hr_api.g_varchar2) or
204 nvl(pay_etp_shd.g_old_rec.element_information6, hr_api.g_varchar2) <>
205 nvl(p_rec.element_information6, hr_api.g_varchar2) or
206 nvl(pay_etp_shd.g_old_rec.element_information7, hr_api.g_varchar2) <>
207 nvl(p_rec.element_information7, hr_api.g_varchar2) or
208 nvl(pay_etp_shd.g_old_rec.element_information8, hr_api.g_varchar2) <>
209 nvl(p_rec.element_information8, hr_api.g_varchar2) or
210 nvl(pay_etp_shd.g_old_rec.element_information9, hr_api.g_varchar2) <>
211 nvl(p_rec.element_information9, hr_api.g_varchar2) or
212 nvl(pay_etp_shd.g_old_rec.element_information10, hr_api.g_varchar2) <>
213 nvl(p_rec.element_information10, hr_api.g_varchar2) or
214 nvl(pay_etp_shd.g_old_rec.element_information11, hr_api.g_varchar2) <>
215 nvl(p_rec.element_information11, hr_api.g_varchar2) or
216 nvl(pay_etp_shd.g_old_rec.element_information12, hr_api.g_varchar2) <>
217 nvl(p_rec.element_information12, hr_api.g_varchar2) or
218 nvl(pay_etp_shd.g_old_rec.element_information13, hr_api.g_varchar2) <>
219 nvl(p_rec.element_information13, hr_api.g_varchar2) or
220 nvl(pay_etp_shd.g_old_rec.element_information14, hr_api.g_varchar2) <>
221 nvl(p_rec.element_information14, hr_api.g_varchar2) or
222 nvl(pay_etp_shd.g_old_rec.element_information15, hr_api.g_varchar2) <>
223 nvl(p_rec.element_information15, hr_api.g_varchar2) or
224 nvl(pay_etp_shd.g_old_rec.element_information16, hr_api.g_varchar2) <>
225 nvl(p_rec.element_information16, hr_api.g_varchar2) or
226 nvl(pay_etp_shd.g_old_rec.element_information17, hr_api.g_varchar2) <>
227 nvl(p_rec.element_information17, hr_api.g_varchar2) or
228 nvl(pay_etp_shd.g_old_rec.element_information18, hr_api.g_varchar2) <>
229 nvl(p_rec.element_information18, hr_api.g_varchar2) or
230 nvl(pay_etp_shd.g_old_rec.element_information19, hr_api.g_varchar2) <>
231 nvl(p_rec.element_information19, hr_api.g_varchar2) or
232 nvl(pay_etp_shd.g_old_rec.element_information20, hr_api.g_varchar2) <>
233 nvl(p_rec.element_information20, hr_api.g_varchar2) ))
234 or (p_rec.element_type_id is null) then
235 --
236 -- Only execute the validation if absolutely necessary:
237 -- a) During update, the structure column value or any
238 -- of the attribute values have actually changed.
239 -- b) During insert.
240 --
241 hr_dflex_utility.ins_or_upd_descflex_attribs
242 (p_appl_short_name => 'PAY'
243 ,p_descflex_name => 'Element Developer DF'
244 ,p_attribute_category => p_rec.element_information_category
245 ,p_attribute1_name => 'ELEMENT_INFORMATION1'
246 ,p_attribute1_value => p_rec.element_information1
247 ,p_attribute2_name => 'ELEMENT_INFORMATION2'
248 ,p_attribute2_value => p_rec.element_information2
249 ,p_attribute3_name => 'ELEMENT_INFORMATION3'
250 ,p_attribute3_value => p_rec.element_information3
251 ,p_attribute4_name => 'ELEMENT_INFORMATION4'
252 ,p_attribute4_value => p_rec.element_information4
253 ,p_attribute5_name => 'ELEMENT_INFORMATION5'
254 ,p_attribute5_value => p_rec.element_information5
255 ,p_attribute6_name => 'ELEMENT_INFORMATION6'
256 ,p_attribute6_value => p_rec.element_information6
257 ,p_attribute7_name => 'ELEMENT_INFORMATION7'
258 ,p_attribute7_value => p_rec.element_information7
259 ,p_attribute8_name => 'ELEMENT_INFORMATION8'
260 ,p_attribute8_value => p_rec.element_information8
261 ,p_attribute9_name => 'ELEMENT_INFORMATION9'
262 ,p_attribute9_value => p_rec.element_information9
263 ,p_attribute10_name => 'ELEMENT_INFORMATION10'
264 ,p_attribute10_value => p_rec.element_information10
265 ,p_attribute11_name => 'ELEMENT_INFORMATION11'
266 ,p_attribute11_value => p_rec.element_information11
267 ,p_attribute12_name => 'ELEMENT_INFORMATION12'
268 ,p_attribute12_value => p_rec.element_information12
269 ,p_attribute13_name => 'ELEMENT_INFORMATION13'
270 ,p_attribute13_value => p_rec.element_information13
271 ,p_attribute14_name => 'ELEMENT_INFORMATION14'
272 ,p_attribute14_value => p_rec.element_information14
273 ,p_attribute15_name => 'ELEMENT_INFORMATION15'
274 ,p_attribute15_value => p_rec.element_information15
275 ,p_attribute16_name => 'ELEMENT_INFORMATION16'
276 ,p_attribute16_value => p_rec.element_information16
277 ,p_attribute17_name => 'ELEMENT_INFORMATION17'
278 ,p_attribute17_value => p_rec.element_information17
279 ,p_attribute18_name => 'ELEMENT_INFORMATION18'
280 ,p_attribute18_value => p_rec.element_information18
281 ,p_attribute19_name => 'ELEMENT_INFORMATION19'
282 ,p_attribute19_value => p_rec.element_information19
283 ,p_attribute20_name => 'ELEMENT_INFORMATION20'
284 ,p_attribute20_value => p_rec.element_information20
285 );
286 end if;
287 --
288 hr_utility.set_location(' Leaving:'||l_proc,20);
289 end chk_ddf;
290 --
291 -- ----------------------------------------------------------------------------
292 -- |------------------------------< chk_df >----------------------------------|
293 -- ----------------------------------------------------------------------------
294 --
295 -- Description:
296 -- Validates all the Descriptive Flexfield values.
297 --
298 -- Prerequisites:
299 -- All other columns have been validated. Must be called as the
300 -- last step from insert_validate and update_validate.
301 --
302 -- In Arguments:
303 -- p_rec
304 --
305 -- Post Success:
306 -- If the Descriptive Flexfield structure column and data values are
307 -- all valid this procedure will end normally and processing will
308 -- continue.
309 --
310 -- Post Failure:
311 -- If the Descriptive Flexfield structure column value or any of
312 -- the data values are invalid then an application error is raised as
313 -- a PL/SQL exception.
314 --
315 -- Access Status:
316 -- Internal Row Handler Use Only.
317 --
318 -- ----------------------------------------------------------------------------
319 procedure chk_df
320 (p_rec in pay_etp_shd.g_rec_type
321 ) is
322 --
323 l_proc varchar2(72) := g_package || 'chk_df';
324 --
325 begin
326 hr_utility.set_location('Entering:'||l_proc,10);
327 --
328 if ((p_rec.element_type_id is not null) and (
329 nvl(pay_etp_shd.g_old_rec.attribute_category, hr_api.g_varchar2) <>
330 nvl(p_rec.attribute_category, hr_api.g_varchar2) or
331 nvl(pay_etp_shd.g_old_rec.attribute1, hr_api.g_varchar2) <>
332 nvl(p_rec.attribute1, hr_api.g_varchar2) or
333 nvl(pay_etp_shd.g_old_rec.attribute2, hr_api.g_varchar2) <>
334 nvl(p_rec.attribute2, hr_api.g_varchar2) or
335 nvl(pay_etp_shd.g_old_rec.attribute3, hr_api.g_varchar2) <>
336 nvl(p_rec.attribute3, hr_api.g_varchar2) or
337 nvl(pay_etp_shd.g_old_rec.attribute4, hr_api.g_varchar2) <>
338 nvl(p_rec.attribute4, hr_api.g_varchar2) or
339 nvl(pay_etp_shd.g_old_rec.attribute5, hr_api.g_varchar2) <>
340 nvl(p_rec.attribute5, hr_api.g_varchar2) or
341 nvl(pay_etp_shd.g_old_rec.attribute6, hr_api.g_varchar2) <>
342 nvl(p_rec.attribute6, hr_api.g_varchar2) or
343 nvl(pay_etp_shd.g_old_rec.attribute7, hr_api.g_varchar2) <>
344 nvl(p_rec.attribute7, hr_api.g_varchar2) or
345 nvl(pay_etp_shd.g_old_rec.attribute8, hr_api.g_varchar2) <>
346 nvl(p_rec.attribute8, hr_api.g_varchar2) or
347 nvl(pay_etp_shd.g_old_rec.attribute9, hr_api.g_varchar2) <>
348 nvl(p_rec.attribute9, hr_api.g_varchar2) or
349 nvl(pay_etp_shd.g_old_rec.attribute10, hr_api.g_varchar2) <>
350 nvl(p_rec.attribute10, hr_api.g_varchar2) or
351 nvl(pay_etp_shd.g_old_rec.attribute11, hr_api.g_varchar2) <>
352 nvl(p_rec.attribute11, hr_api.g_varchar2) or
353 nvl(pay_etp_shd.g_old_rec.attribute12, hr_api.g_varchar2) <>
354 nvl(p_rec.attribute12, hr_api.g_varchar2) or
355 nvl(pay_etp_shd.g_old_rec.attribute13, hr_api.g_varchar2) <>
356 nvl(p_rec.attribute13, hr_api.g_varchar2) or
357 nvl(pay_etp_shd.g_old_rec.attribute14, hr_api.g_varchar2) <>
358 nvl(p_rec.attribute14, hr_api.g_varchar2) or
359 nvl(pay_etp_shd.g_old_rec.attribute15, hr_api.g_varchar2) <>
360 nvl(p_rec.attribute15, hr_api.g_varchar2) or
361 nvl(pay_etp_shd.g_old_rec.attribute16, hr_api.g_varchar2) <>
362 nvl(p_rec.attribute16, hr_api.g_varchar2) or
363 nvl(pay_etp_shd.g_old_rec.attribute17, hr_api.g_varchar2) <>
364 nvl(p_rec.attribute17, hr_api.g_varchar2) or
365 nvl(pay_etp_shd.g_old_rec.attribute18, hr_api.g_varchar2) <>
366 nvl(p_rec.attribute18, hr_api.g_varchar2) or
367 nvl(pay_etp_shd.g_old_rec.attribute19, hr_api.g_varchar2) <>
368 nvl(p_rec.attribute19, hr_api.g_varchar2) or
369 nvl(pay_etp_shd.g_old_rec.attribute20, hr_api.g_varchar2) <>
370 nvl(p_rec.attribute20, hr_api.g_varchar2) ))
371 or (p_rec.element_type_id is null) then
372 --
373 -- Only execute the validation if absolutely necessary:
374 -- a) During update, the structure column value or any
375 -- of the attribute values have actually changed.
376 -- b) During insert.
377 --
378 hr_dflex_utility.ins_or_upd_descflex_attribs
379 (p_appl_short_name => 'PAY'
380 ,p_descflex_name => 'PAY_ELEMENT_TYPES'
381 ,p_attribute_category => p_rec.attribute_category
382 ,p_attribute1_name => 'ATTRIBUTE1'
383 ,p_attribute1_value => p_rec.attribute1
384 ,p_attribute2_name => 'ATTRIBUTE2'
385 ,p_attribute2_value => p_rec.attribute2
386 ,p_attribute3_name => 'ATTRIBUTE3'
387 ,p_attribute3_value => p_rec.attribute3
388 ,p_attribute4_name => 'ATTRIBUTE4'
389 ,p_attribute4_value => p_rec.attribute4
390 ,p_attribute5_name => 'ATTRIBUTE5'
391 ,p_attribute5_value => p_rec.attribute5
392 ,p_attribute6_name => 'ATTRIBUTE6'
393 ,p_attribute6_value => p_rec.attribute6
394 ,p_attribute7_name => 'ATTRIBUTE7'
395 ,p_attribute7_value => p_rec.attribute7
396 ,p_attribute8_name => 'ATTRIBUTE8'
397 ,p_attribute8_value => p_rec.attribute8
398 ,p_attribute9_name => 'ATTRIBUTE9'
399 ,p_attribute9_value => p_rec.attribute9
400 ,p_attribute10_name => 'ATTRIBUTE10'
401 ,p_attribute10_value => p_rec.attribute10
402 ,p_attribute11_name => 'ATTRIBUTE11'
403 ,p_attribute11_value => p_rec.attribute11
404 ,p_attribute12_name => 'ATTRIBUTE12'
405 ,p_attribute12_value => p_rec.attribute12
406 ,p_attribute13_name => 'ATTRIBUTE13'
407 ,p_attribute13_value => p_rec.attribute13
408 ,p_attribute14_name => 'ATTRIBUTE14'
409 ,p_attribute14_value => p_rec.attribute14
410 ,p_attribute15_name => 'ATTRIBUTE15'
411 ,p_attribute15_value => p_rec.attribute15
412 ,p_attribute16_name => 'ATTRIBUTE16'
413 ,p_attribute16_value => p_rec.attribute16
414 ,p_attribute17_name => 'ATTRIBUTE17'
415 ,p_attribute17_value => p_rec.attribute17
416 ,p_attribute18_name => 'ATTRIBUTE18'
417 ,p_attribute18_value => p_rec.attribute18
418 ,p_attribute19_name => 'ATTRIBUTE19'
419 ,p_attribute19_value => p_rec.attribute19
420 ,p_attribute20_name => 'ATTRIBUTE20'
421 ,p_attribute20_value => p_rec.attribute20
422 );
423 end if;
424 --
425 hr_utility.set_location(' Leaving:'||l_proc,20);
426 end chk_df;
427 --
428 -- ----------------------------------------------------------------------------
429 -- |-----------------------< chk_non_updateable_args >------------------------|
430 -- ----------------------------------------------------------------------------
431 -- {Start Of Comments}
432 --
433 -- Description:
434 -- This procedure is used to ensure that non updateable attributes have
435 -- not been updated. If an attribute has been updated an error is generated.
436 --
437 -- Pre Conditions:
438 -- g_old_rec has been populated with details of the values currently in
439 -- the database.
440 --
441 -- In Arguments:
442 -- p_rec has been populated with the updated values the user would like the
443 -- record set to.
444 --
445 -- Post Success:
446 -- Processing continues if all the non updateable attributes have not
447 -- changed.
448 --
449 -- Post Failure:
450 -- An application error is raised if any of the non updatable attributes
451 -- have been altered.
452 --
453 -- {End Of Comments}
454 -- ----------------------------------------------------------------------------
455 Procedure chk_non_updateable_args
456 (p_effective_date in date
457 ,p_rec in pay_etp_shd.g_rec_type
458 ) IS
459 --
460 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
461 --
462 Begin
463 --
464 -- Only proceed with the validation if a row exists for the current
465 -- record in the HR Schema.
466 --
467 IF NOT pay_etp_shd.api_updating
468 (p_element_type_id => p_rec.element_type_id
469 ,p_effective_date => p_effective_date
470 ,p_object_version_number => p_rec.object_version_number
471 ) THEN
472 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
473 fnd_message.set_token('PROCEDURE ', l_proc);
474 fnd_message.set_token('STEP ', '5');
475 fnd_message.raise_error;
476 END IF;
477 --
478 End chk_non_updateable_args;
479 --
480 -- ----------------------------------------------------------------------------
481 -- |--------------------------< dt_update_validate >--------------------------|
482 -- ----------------------------------------------------------------------------
483 -- {Start Of Comments}
484 --
485 -- Description:
486 -- This procedure is used for referential integrity of datetracked
487 -- parent entities when a datetrack update operation is taking place
488 -- and where there is no cascading of update defined for this entity.
489 --
490 -- Prerequisites:
491 -- This procedure is called from the update_validate.
492 --
493 -- In Parameters:
494 --
495 -- Post Success:
496 -- Processing continues.
497 --
498 -- Post Failure:
499 --
500 -- Developer Implementation Notes:
501 -- This procedure should not need maintenance unless the HR Schema model
502 -- changes.
503 --
504 -- Access Status:
505 -- Internal Row Handler Use Only.
506 --
507 -- {End Of Comments}
508 -- ----------------------------------------------------------------------------
509 Procedure dt_update_validate
510 (p_datetrack_mode in varchar2
511 ,p_validation_start_date in date
512 ,p_validation_end_date in date
513 ) Is
514 --
515 l_proc varchar2(72) := g_package||'dt_update_validate';
516 --
517 Begin
518 --
519 -- Ensure that the p_datetrack_mode argument is not null
520 --
521 hr_api.mandatory_arg_error
522 (p_api_name => l_proc
523 ,p_argument => 'datetrack_mode'
524 ,p_argument_value => p_datetrack_mode
525 );
526 --
527 -- Mode will be valid, as this is checked at the start of the upd.
528 --
529 -- Ensure the arguments are not null
530 --
531 hr_api.mandatory_arg_error
532 (p_api_name => l_proc
533 ,p_argument => 'validation_start_date'
534 ,p_argument_value => p_validation_start_date
535 );
536 --
537 hr_api.mandatory_arg_error
538 (p_api_name => l_proc
539 ,p_argument => 'validation_end_date'
540 ,p_argument_value => p_validation_end_date
541 );
542 --
543 --
544 --
545 Exception
546 When Others Then
547 --
548 -- An unhandled or unexpected error has occurred which
549 -- we must report
550 --
551 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
552 fnd_message.set_token('PROCEDURE', l_proc);
553 fnd_message.set_token('STEP','15');
554 fnd_message.raise_error;
555 End dt_update_validate;
556 --
557 -- ----------------------------------------------------------------------------
558 -- |--------------------------< dt_delete_validate >--------------------------|
559 -- ----------------------------------------------------------------------------
560 -- {Start Of Comments}
561 --
562 -- Description:
563 -- This procedure is used for referential integrity of datetracked
564 -- child entities when either a datetrack DELETE or ZAP is in operation
565 -- and where there is no cascading of delete defined for this entity.
566 -- For the datetrack mode of DELETE or ZAP we must ensure that no
567 -- datetracked child rows exist between the validation start and end
568 -- dates.
569 --
570 -- Prerequisites:
571 -- This procedure is called from the delete_validate.
572 --
573 -- In Parameters:
574 --
575 -- Post Success:
576 -- Processing continues.
577 --
578 -- Post Failure:
579 -- If a row exists by determining the returning Boolean value from the
580 -- generic dt_api.rows_exist function then we must supply an error via
581 -- the use of the local exception handler l_rows_exist.
582 --
583 -- Developer Implementation Notes:
584 -- This procedure should not need maintenance unless the HR Schema model
585 -- changes.
586 --
587 -- Access Status:
588 -- Internal Row Handler Use Only.
589 --
590 -- {End Of Comments}
591 -- ----------------------------------------------------------------------------
592 Procedure dt_delete_validate
593 (p_element_type_id in number
594 ,p_datetrack_mode in varchar2
595 ,p_validation_start_date in date
596 ,p_validation_end_date in date
597 ) Is
598 --
599 l_proc varchar2(72) := g_package||'dt_delete_validate';
600 --
601 Begin
602 --
603 -- Ensure that the p_datetrack_mode argument is not null
604 --
605 hr_api.mandatory_arg_error
606 (p_api_name => l_proc
607 ,p_argument => 'datetrack_mode'
608 ,p_argument_value => p_datetrack_mode
609 );
610 --
611 -- Only perform the validation if the datetrack mode is either
612 -- DELETE or ZAP
613 --
614 If (p_datetrack_mode = hr_api.g_delete or
615 p_datetrack_mode = hr_api.g_zap) then
616 --
617 --
618 -- Ensure the arguments are not null
619 --
620 hr_api.mandatory_arg_error
621 (p_api_name => l_proc
622 ,p_argument => 'validation_start_date'
623 ,p_argument_value => p_validation_start_date
624 );
625 --
626 hr_api.mandatory_arg_error
627 (p_api_name => l_proc
628 ,p_argument => 'validation_end_date'
629 ,p_argument_value => p_validation_end_date
630 );
631 --
632 hr_api.mandatory_arg_error
633 (p_api_name => l_proc
634 ,p_argument => 'element_type_id'
635 ,p_argument_value => p_element_type_id
636 );
637 --
638 If (dt_api.rows_exist
639 (p_base_table_name => 'pay_input_values_f'
640 ,p_base_key_column => 'element_type_id'
641 ,p_base_key_value => p_element_type_id
642 ,p_from_date => p_validation_start_date
643 ,p_to_date => p_validation_end_date
644 )) Then
645 fnd_message.set_name('PAY','HR_7215_DT_CHILD_EXISTS');
646 fnd_message.set_token('TABLE_NAME','input values');
647 hr_multi_message.add;
648 End If;
649 If (dt_api.rows_exist
650 (p_base_table_name => 'ben_acty_base_rt_f'
651 ,p_base_key_column => 'element_type_id'
652 ,p_base_key_value => p_element_type_id
653 ,p_from_date => p_validation_start_date
654 ,p_to_date => p_validation_end_date
655 )) Then
656 fnd_message.set_name('PAY','HR_7215_DT_CHILD_EXISTS');
657 fnd_message.set_token('TABLE_NAME','acty base rt');
658 hr_multi_message.add;
659 End If;
660 If (dt_api.rows_exist
661 (p_base_table_name => 'pay_element_links_f'
662 ,p_base_key_column => 'element_type_id'
663 ,p_base_key_value => p_element_type_id
664 ,p_from_date => p_validation_start_date
665 ,p_to_date => p_validation_end_date
666 )) Then
667 fnd_message.set_name('PAY','HR_7215_DT_CHILD_EXISTS');
668 fnd_message.set_token('TABLE_NAME','element links');
669 hr_multi_message.add;
670 End If;
671 If (dt_api.rows_exist
672 (p_base_table_name => 'pay_element_type_usages_f'
673 ,p_base_key_column => 'element_type_id'
674 ,p_base_key_value => p_element_type_id
675 ,p_from_date => p_validation_start_date
676 ,p_to_date => p_validation_end_date
677 )) Then
678 fnd_message.set_name('PAY','HR_7215_DT_CHILD_EXISTS');
679 fnd_message.set_token('TABLE_NAME','element type usages');
680 hr_multi_message.add;
681 End If;
682 --
683 End If;
684 --
685 Exception
686 When Others Then
687 --
688 -- An unhandled or unexpected error has occurred which
689 -- we must report
690 --
691 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
692 fnd_message.set_token('PROCEDURE', l_proc);
693 fnd_message.set_token('STEP','15');
694 fnd_message.raise_error;
695 --
696 End dt_delete_validate;
697 --
698 -- ----------------------------------------------------------------------------
699 -- |----------------------< chk_startup_action >------------------------------|
700 -- ----------------------------------------------------------------------------
701 --
702 -- Description:
703 -- This procedure will check that the current action is allowed according
704 -- to the current startup mode.
705 --
706 -- ----------------------------------------------------------------------------
707 PROCEDURE chk_startup_action
708 (p_insert IN boolean
709 ,p_business_group_id IN number
710 ,p_legislation_code IN varchar2
711 ,p_legislation_subgroup IN varchar2 DEFAULT NULL) IS
712 --
713 BEGIN
714 --
715 -- Call the supporting procedure to check startup mode
716 --
717 IF (p_insert) THEN
718 hr_startup_data_api_support.chk_startup_action
719 (p_generic_allowed => TRUE
720 ,p_startup_allowed => TRUE
721 ,p_user_allowed => TRUE
722 ,p_business_group_id => p_business_group_id
723 ,p_legislation_code => p_legislation_code
724 ,p_legislation_subgroup => p_legislation_subgroup
725 );
726 ELSE
727 hr_startup_data_api_support.chk_upd_del_startup_action
728 (p_generic_allowed => TRUE
729 ,p_startup_allowed => TRUE
730 ,p_user_allowed => TRUE
731 ,p_business_group_id => p_business_group_id
732 ,p_legislation_code => p_legislation_code
733 ,p_legislation_subgroup => p_legislation_subgroup
734 );
735 END IF;
736 --
737 END chk_startup_action;
738 --
739 -- ----------------------------------------------------------------------------
740 -- |------------------------< chk_business_group_id >-------------------------|
741 -- ----------------------------------------------------------------------------
742 --
743 -- Description:
744 -- This procedure is used to validate the business group id against the
745 -- parent table
746 --
747 -- ----------------------------------------------------------------------------
748 Procedure chk_business_group_id
749 (p_business_group_id in number
750 ,p_effective_date in date
751 ) is
752 --
753 l_proc varchar2(72) := g_package||'chk_business_group_id';
754 l_exists varchar2(1);
755 Cursor c_chk_bg_id
756 is
757 select '1'
758 from hr_organization_units
759 where business_group_id = p_business_group_id;
760 -- and p_effective_date between date_from
761 -- and nvl(date_to, hr_api.g_eot);
762 --
763 Begin
764 hr_utility.set_location('Entering:'||l_proc, 5);
765 --
766 Open c_chk_bg_id;
767 Fetch c_chk_bg_id into l_exists;
768 If c_chk_bg_id%notfound Then
769 --
770 Close c_chk_bg_id;
771 fnd_message.set_name('PAY','PAY_33085_INVALID_FK');
772 fnd_message.set_token('COLUMN','BUSINESS_GROUP_ID');
773 fnd_message.set_token('TABLE','HR_ORGANIZATION_UNITS');
774 fnd_message.raise_error;
775 --
776 End If;
777 Close c_chk_bg_id;
778 --
779 hr_utility.set_location('Leaving:'||l_proc, 10);
780 End;
781 --
782 -- ----------------------------------------------------------------------------
783 -- |------------------------< chk_legislation_code >--------------------------|
784 -- ----------------------------------------------------------------------------
785 --
786 -- Description:
787 -- This procedure is used to validate the legislation code against the
788 -- parent table
789 --
790 -- ----------------------------------------------------------------------------
791 Procedure chk_legislation_code
792 (p_legislation_code in varchar2)
793 is
794 --
795 l_proc varchar2(72) := g_package||'chk_legislation_code';
796 l_exists varchar2(1);
797 Cursor c_chk_leg_code
798 is
799 select '1'
800 from fnd_territories
801 where territory_code = p_legislation_code;
802 --
803 Begin
804 hr_utility.set_location('Entering:'||l_proc, 5);
805 --
806 If p_legislation_code is not null then
807
808 Open c_chk_leg_code;
809 Fetch c_chk_leg_code into l_exists;
810 If c_chk_leg_code%notfound Then
811 --
812 Close c_chk_leg_code;
813 fnd_message.set_name('PAY','PAY_33085_INVALID_FK');
814 fnd_message.set_token('COLUMN','LEGISLATION_CODE');
815 fnd_message.set_token('TABLE','FND_TERRITORIES');
816 fnd_message.raise_error;
817 --
818 End If;
819 Close c_chk_leg_code;
820
821 End If;
822 --
823 hr_utility.set_location('Leaving:'||l_proc, 10);
824 End;
825 --
826 -- ----------------------------------------------------------------------------
827 -- |--------------------------< chk_formula_id >------------------------------|
828 -- ----------------------------------------------------------------------------
829 -- {Start Of Comments}
830 --
831 -- Description:
832 -- This procedure is used to validate the following business rules:
833 --
834 -- a) The Formula Type of the formula_id (ie. Skip Rule) must be
835 -- 'Element Skip'.
836 -- b) The formula_id must be null if process_in_run_flag is 'N' or
837 -- indirect_only_flag is 'Y' or adjustment_only_flag is 'Y'.
838 --
839 -- Prerequisites:
840 -- This procedure is called from the insert_validate and update_validate.
841 --
842 -- In Parameters:
843 -- p_effective_date
844 -- p_validation_start_date
845 -- p_validation_end_date
846 -- p_formula_id
847 -- p_process_in_run_flag
848 -- p_indirect_only_flag
849 -- p_adjustment_only_flag
850 --
851 -- Post Success:
852 -- Processing continues.
853 --
854 -- Post Failure:
855 -- If any one of the business rule validation fails then an error is raised.
856 --
857 -- Access Status:
858 -- Internal Row Handler Use Only.
859 --
860 -- {End Of Comments}
861 -- ----------------------------------------------------------------------------
862 Procedure chk_formula_id
863 (p_effective_date in date
864 ,p_validation_start_date in date
865 ,p_validation_end_date in date
866 ,p_formula_id in number
867 ,p_process_in_run_flag in varchar2
868 ,p_indirect_only_flag in varchar2
869 ,p_adjustment_only_flag in varchar2
870 ) is
871 --
872 l_proc varchar2(72) := g_package||'chk_formula_id';
873 l_exists varchar2(1);
874 Cursor c_chk_formula_type
875 is
876 select '1'
877 from ff_formulas_f fml, ff_formula_types ftp
878 where fml.formula_id = p_formula_id
879 and p_effective_date between fml.effective_start_date
880 and fml.effective_end_date
881 and ftp.formula_type_id = fml.formula_type_id
882 and upper(ftp.formula_type_name) = 'ELEMENT SKIP';
883 --
884 Begin
885 hr_utility.set_location('Entering:'||l_proc, 5);
886 --
887 If p_formula_id is not null Then
888 If not (dt_api.rows_exist
889 (p_base_table_name => 'FF_FORMULAS_F'
890 ,p_base_key_column => 'FORMULA_ID'
891 ,p_base_key_value => p_formula_id
892 ,p_from_date => p_validation_start_date
893 ,p_to_date => p_validation_end_date
894 )) Then
895 --
896 fnd_message.set_name('PAY','PAY_33085_INVALID_FK');
897 fnd_message.set_token('COLUMN','FORMULA_ID');
898 fnd_message.set_token('TABLE','FF_FORMULAS_F');
899 fnd_message.raise_error;
900 --
901 End If;
902 --
903 If (p_process_in_run_flag = 'N' or
904 p_indirect_only_flag = 'Y' or
905 p_adjustment_only_flag = 'Y') Then
906 --
907 fnd_message.set_name('PAY','HR_6951_PAY_ELE_NO_SKIP_RULE');
908 fnd_message.raise_error;
909 --
910 End If;
911 --
912 Open c_chk_formula_type;
913 Fetch c_chk_formula_type into l_exists;
914 If c_chk_formula_type%notfound Then
915 --
916 Close c_chk_formula_type;
917 fnd_message.set_name('PAY','PAY_34130_ELE_FTYPE_INVALID');
918 fnd_message.raise_error;
919 --
920 End If;
921 Close c_chk_formula_type;
922 End If;
923 --
924 hr_utility.set_location('Leaving:'||l_proc, 10);
925 End;
926 --
927 -- ----------------------------------------------------------------------------
928 -- |--------------------------< chk_currency_codes >--------------------------|
929 -- ----------------------------------------------------------------------------
930 -- {Start Of Comments}
931 --
932 -- Description:
933 -- This procedure is used to validate the following business rules:
934 --
935 -- a) If an input_currency_code is specified, then an output_currency_code
936 -- must also be specified and vice versa
937 -- b) Both input_currency_code and output_currency_code must be
938 -- present in FND_CURRENCIES (currency_code) for the current
939 -- session date.
940 -- c) For element classification 'Payments', the output currency should be
941 -- same as that defined in pay_legislation_rules.
942 --
943 -- Prerequisites:
944 -- This procedure is called from the insert_validate.
945 --
946 -- In Parameters:
947 -- p_effective_date
948 -- p_input_currency_code
949 -- p_output_currency_code
950 --
951 -- Post Success:
952 -- Processing continues.
953 --
954 -- Post Failure:
955 -- If any one of the business rule validation fails then an error is raised.
956 --
957 -- Access Status:
958 -- Internal Row Handler Use Only.
959 --
960 -- {End Of Comments}
961 -- ----------------------------------------------------------------------------
962 Procedure chk_currency_codes
963 (p_effective_date in date
964 ,p_classification_id in number
965 ,p_business_group_id in number
966 ,p_input_currency_code in varchar2
967 ,p_output_currency_code in varchar2
968 ) is
969 --
970 l_proc varchar2(72) := g_package||'chk_currency_codes';
971 l_exists varchar2(1);
972 l_non_payments_flag varchar2(30);
973 l_rule_mode pay_legislation_rules.rule_mode%type;
974
975 Cursor c_chk_currency(p_currency_code varchar2)
976 is
977 -- bug 7462502 in startup mode remove restriction on enabled currencies
978 select '1'
979 from fnd_currencies
980 where currency_code = p_currency_code
981 and ( ( enabled_flag = 'Y') or
982 ( hr_startup_data_api_support.g_startup_mode IN ('STARTUP') ))
983 and currency_flag = 'Y'
984 and p_effective_date between nvl(start_date_active,p_effective_date)
985 and nvl(end_date_active,p_effective_date);
986
987 Cursor c_non_payments
988 is
989 select non_payments_flag
990 from pay_element_classifications
991 where classification_id = p_classification_id;
992
993 Cursor c_leg_rule_currency
994 is
995 select rule_mode
996 from pay_legislation_rules
997 where legislation_code = hr_api.return_legislation_code
998 (p_business_group_id)
999 and rule_type = 'DC';
1000 --
1001 Begin
1002 hr_utility.set_location('Entering:'||l_proc, 5);
1003 --
1004 Open c_non_payments;
1005 Fetch c_non_payments into l_non_payments_flag;
1006 Close c_non_payments;
1007 --
1008 If (l_non_payments_flag = 'N' and
1009 (p_input_currency_code is null or p_output_currency_code is null)) Then
1010 --
1011 fnd_message.set_name('PAY','PAY_34131_ELE_CURR_REQD1');
1012 fnd_message.raise_error;
1013 --
1014 End If;
1015 --
1016 If (p_input_currency_code is not null and p_output_currency_code is null
1017 or
1018 p_input_currency_code is null and p_output_currency_code is not null
1019 ) Then
1020 --
1021 fnd_message.set_name('PAY','PAY_34150_ELE_CURR_REQD2');
1022 fnd_message.raise_error;
1023 --
1024 End If;
1025 --
1026 If p_input_currency_code is not null Then
1027 --
1028 Open c_chk_currency(p_input_currency_code);
1029 Fetch c_chk_currency into l_exists;
1030 If c_chk_currency%notfound Then
1031 --
1032 Close c_chk_currency;
1033 fnd_message.set_name('PAY','HR_51855_QUA_CCY_INV');
1034 fnd_message.raise_error;
1035 --
1036 End If;
1037 Close c_chk_currency;
1038 --
1039 End If;
1040 --
1041 If p_output_currency_code is not null Then
1042 --
1043 Open c_chk_currency(p_output_currency_code);
1044 Fetch c_chk_currency into l_exists;
1045 If c_chk_currency%notfound Then
1046 --
1047 Close c_chk_currency;
1048 fnd_message.set_name('PAY','HR_51855_QUA_CCY_INV');
1049 fnd_message.raise_error;
1050 --
1051 End If;
1052 Close c_chk_currency;
1053 --
1054 End If;
1055 --
1056 If l_non_payments_flag = 'N' Then
1057 --
1058 Open c_leg_rule_currency;
1059 Fetch c_leg_rule_currency into l_rule_mode;
1060 If c_leg_rule_currency%found Then
1061 If p_output_currency_code <> l_rule_mode then
1062 Close c_leg_rule_currency;
1063 fnd_message.set_name('PAY','PAY_34152_ELE_INVALID_CURR');
1064 fnd_message.raise_error;
1065 End If;
1066 End if;
1067 Close c_leg_rule_currency;
1068 --
1069 End If;
1070 --
1071 hr_utility.set_location('Leaving:'||l_proc, 10);
1072 End;
1073 --
1074 -- ----------------------------------------------------------------------------
1075 -- |------------------------< chk_classification_id >-------------------------|
1076 -- ----------------------------------------------------------------------------
1077 -- {Start Of Comments}
1078 --
1079 -- Description:
1080 -- This procedure is used to validate the following business rule:
1081 --
1082 -- a) An element with an 'Initial Balance' classification must have
1083 -- adjustment_only_flag as 'Y', process_in_run_flag as 'Y', processing_type
1084 -- as 'N' (Non recurring) and indirect_only_flag as 'N'.
1085 --
1086 -- Prerequisites:
1087 -- This procedure is called from the insert_validate and update_validate.
1088 --
1089 -- In Parameters:
1090 -- p_classification_id
1091 -- p_adjustment_only_flag
1092 -- p_process_in_run_flag
1093 -- p_processing_type
1094 -- p_indirect_only_flag
1095 --
1096 -- Post Success:
1097 -- Processing continues.
1098 --
1099 -- Post Failure:
1100 -- If the business rule validation fails then an error is raised.
1101 --
1102 -- Access Status:
1103 -- Internal Row Handler Use Only.
1104 --
1105 -- {End Of Comments}
1106 -- ----------------------------------------------------------------------------
1107 Procedure chk_classification_id
1108 (p_classification_id in number
1109 ,p_adjustment_only_flag in varchar2
1110 ,p_process_in_run_flag in varchar2
1111 ,p_processing_type in varchar2
1112 ,p_indirect_only_flag in varchar2
1113 ) is
1114 --
1115 l_proc varchar2(72) := g_package||'chk_class_id';
1116 l_exists varchar2(1);
1117 Cursor c_get_class_bal_init
1118 is
1119 select '1'
1120 from pay_element_classifications
1121 where classification_id = p_classification_id
1122 and nvl(balance_initialization_flag,'N') = 'Y';
1123 --
1124 Begin
1125 hr_utility.set_location('Entering:'||l_proc, 5);
1126 --
1127 Open c_get_class_bal_init;
1128 Fetch c_get_class_bal_init into l_exists;
1129 If c_get_class_bal_init%found then
1130 --
1131 If (p_adjustment_only_flag <> 'Y' or
1132 p_process_in_run_flag <> 'Y' or
1133 p_processing_type <> 'N' or
1134 p_indirect_only_flag <> 'N') Then
1135 --
1136 Close c_get_class_bal_init;
1137 fnd_message.set_name('PAY','PAY_34132_ELE_CLASS_VLDTN');
1138 fnd_message.raise_error;
1139 --
1140 End If;
1141 End If;
1142 Close c_get_class_bal_init;
1143 --
1144 hr_utility.set_location('Leaving:'||l_proc, 10);
1145 End;
1146 --
1147 -- ----------------------------------------------------------------------------
1148 -- |------------------------< chk_addl_entry_allowed >------------------------|
1149 -- ----------------------------------------------------------------------------
1150 -- {Start Of Comments}
1151 --
1152 -- Description:
1153 -- This procedure is used to validate the following business rule:
1154 --
1155 -- a) Additional_entry_allowed_flag must be 'N' when the processing_type
1156 -- is 'N'.
1157 --
1158 -- Prerequisites:
1159 -- This procedure is called from insert_validate and update_validate.
1160 --
1161 -- In Parameters:
1162 -- p_addl_entry_allowed_flag
1163 -- p_processing_type
1164 --
1165 -- Post Success:
1166 -- Processing continues.
1167 --
1168 -- Post Failure:
1169 -- If the business rule validation fails then an error is raised.
1170 --
1171 -- Access Status:
1172 -- Internal Row Handler Use Only.
1173 --
1174 -- {End Of Comments}
1175 -- ----------------------------------------------------------------------------
1176 Procedure chk_addl_entry_allowed
1177 (p_effective_date in date
1178 ,p_addl_entry_allowed_flag in varchar2
1179 ,p_processing_type in varchar2
1180 ) is
1181 --
1182 l_proc varchar2(72) := g_package||'chk_addl_entry_alld';
1183 --
1184 Begin
1185 hr_utility.set_location('Entering:'||l_proc, 5);
1186 --
1187 If hr_api.not_exists_in_hr_lookups
1188 (p_effective_date
1189 ,'YES_NO'
1190 ,p_addl_entry_allowed_flag) Then
1191 --
1192 fnd_message.set_name('PAY','HR_52966_INVALID_LOOKUP');
1193 fnd_message.set_token('COLUMN','ADDITIONAL_ENTRY_ALLOWED_FLAG');
1194 fnd_message.set_token('LOOKUP_TYPE','YES_NO');
1195 fnd_message.raise_error;
1196 --
1197 End If;
1198 --
1199 If (p_addl_entry_allowed_flag = 'Y' and p_processing_type = 'N') Then
1200 --
1201 fnd_message.set_name('PAY','PAY_6142_ELEMENT_NO_ADD_ENTRY');
1202 fnd_message.raise_error;
1203 --
1204 End If;
1205 --
1206 hr_utility.set_location('Leaving:'||l_proc, 10);
1207 End;
1208 --
1209 -- ----------------------------------------------------------------------------
1210 -- |------------------------< chk_adjustment_only_flag >----------------------|
1211 -- ----------------------------------------------------------------------------
1212 -- {Start Of Comments}
1213 --
1214 -- Description:
1215 -- This procedure is used to validate the following business rules:
1216 --
1217 -- a) Adjustment_only_flag must be 'N' when the Processing_type is 'R'.
1218 -- b) If adjustment_only_flag is 'Y' then indirect_only_flag must be 'N'
1219 -- and vice versa.
1220 -- c) Indirect_only_flag must be 'N' when the Processing_type is 'R'.
1221 --
1222 -- Prerequisites:
1223 -- This procedure is called from insert_validate and update_validate.
1224 --
1225 -- In Parameters:
1226 -- p_adjustment_only_flag
1227 -- p_processing_type
1228 -- p_indirect_only_flag
1229 --
1230 -- Post Success:
1231 -- Processing continues.
1232 --
1233 -- Post Failure:
1234 -- If the business rule validation fails then an error is raised.
1235 --
1236 -- Access Status:
1237 -- Internal Row Handler Use Only.
1238 --
1239 -- {End Of Comments}
1240 -- ----------------------------------------------------------------------------
1241 Procedure chk_adjustment_only_flag
1242 (p_effective_date in date
1243 ,p_adjustment_only_flag in varchar2
1244 ,p_closed_for_entry_flag in varchar2
1245 ,p_processing_type in varchar2
1246 ,p_indirect_only_flag in varchar2
1247 ) is
1248 --
1249 l_proc varchar2(72) := g_package||'chk_adj_only_flag';
1250 --
1251 Begin
1252 hr_utility.set_location('Entering:'||l_proc, 5);
1253 --
1254 If hr_api.not_exists_in_hr_lookups
1255 (p_effective_date
1256 ,'YES_NO'
1257 ,p_adjustment_only_flag) Then
1258 --
1259 fnd_message.set_name('PAY','HR_52966_INVALID_LOOKUP');
1260 fnd_message.set_token('COLUMN','ADJUSTMENT_ONLY_FLAG');
1261 fnd_message.set_token('LOOKUP_TYPE','YES_NO');
1262 fnd_message.raise_error;
1263 --
1264 End If;
1265 --
1266 If hr_api.not_exists_in_hr_lookups
1267 (p_effective_date
1268 ,'YES_NO'
1269 ,p_closed_for_entry_flag) Then
1270 --
1271 fnd_message.set_name('PAY','HR_52966_INVALID_LOOKUP');
1272 fnd_message.set_token('COLUMN','CLOSED_FOR_ENTRY_FLAG');
1273 fnd_message.set_token('LOOKUP_TYPE','YES_NO');
1274 fnd_message.raise_error;
1275 --
1276 End If;
1277 --
1278 If (p_adjustment_only_flag = 'Y' and p_processing_type = 'R') Then
1279 --
1280 fnd_message.set_name('PAY','PAY_6712_ELEMENT_NO_REC_ADJ');
1281 fnd_message.raise_error;
1282 --
1283 End If;
1284 --
1285 If (p_adjustment_only_flag = 'Y' and p_indirect_only_flag = 'Y') Then
1286 --
1287 fnd_message.set_name('PAY','PAY_34133_ELE_ADJ_INDIRECT');
1288 fnd_message.raise_error;
1289 --
1290 End If;
1291 --
1292 If (p_processing_type = 'R' and p_indirect_only_flag = 'Y') Then
1293 --
1294 fnd_message.set_name('PAY','PAY_6707_ELEMENT_NO_REC_IND');
1295 fnd_message.raise_error;
1296 --
1297 End If;
1298 --
1299 hr_utility.set_location('Leaving:'||l_proc, 10);
1300 End;
1301 --
1302 -- ----------------------------------------------------------------------------
1303 -- |------------------------< chk_element_name >------------------------------|
1304 -- ----------------------------------------------------------------------------
1305 -- {Start Of Comments}
1306 --
1307 -- Description:
1308 -- This procedure is used to validate the following business rules:
1309 --
1310 -- a) Element name must be unique within a Legislation or Business group for
1311 -- entire lifetime of the Element
1312 -- b) Element name must be a valid db item name.
1313 --
1314 -- Prerequisites:
1315 -- This procedure is called from insert_validate and update_validate.
1316 --
1317 -- In Parameters:
1318 -- p_element_name
1319 -- p_business_group_id
1320 -- p_legislation_code
1321 --
1322 -- Post Success:
1323 -- Processing continues.
1324 --
1325 -- Post Failure:
1326 -- If the business rule validation fails then an error is raised.
1327 --
1328 -- Access Status:
1329 -- Internal Row Handler Use Only.
1330 --
1331 -- {End Of Comments}
1332 -- ----------------------------------------------------------------------------
1333 Procedure chk_element_name
1334 (p_element_name in varchar2
1335 ,p_element_type_id in number default null
1336 ,p_business_group_id in number
1337 ,p_legislation_code in varchar2
1338 ) is
1339 --
1340 l_proc varchar2(72) := g_package||'chk_element_name';
1341 l_dummy varchar2(100);
1342 l_checkformat_error boolean;
1343 l_unformatted_value varchar2(255) := p_element_name;
1344 --
1345 Begin
1346 hr_utility.set_location('Entering:'||l_proc, 5);
1347 --
1348 If p_element_name is not null and
1349 pay_element_types_pkg.name_is_not_unique
1350 (p_element_name => p_element_name
1351 ,p_element_type_id => p_element_type_id
1352 ,p_business_group_id => p_business_group_id
1353 ,p_legislation_code => p_legislation_code
1354 ,p_error_if_true => FALSE
1355 ) Then
1356 --
1357 fnd_message.set_name('PAY','PAY_6137_ELEMENT_DUP_NAME');
1358 fnd_message.raise_error;
1359 --
1360 End If;
1361 --
1362 Begin
1363 hr_chkfmt.checkformat(l_unformatted_value,
1364 'PAY_NAME',
1365 l_dummy,
1366 null,
1367 null,
1368 'N',
1369 l_dummy,
1370 null);
1371 Exception
1372 when hr_utility.hr_error then
1373 l_checkformat_error := True;
1374 End;
1375 If (l_checkformat_error) then
1376 --
1377 fnd_message.set_name('PAY','PAY_6365_ELEMENT_NO_DB_NAME');
1378 fnd_message.raise_error;
1379 --
1380 End If;
1381 --
1382 hr_utility.set_location('Leaving:'||l_proc, 10);
1383 End;
1384 --
1385 -- ----------------------------------------------------------------------------
1386 -- |---------------------< chk_multiple_entries_allowed >---------------------|
1387 -- ----------------------------------------------------------------------------
1388 -- {Start Of Comments}
1389 --
1390 -- Description:
1391 -- This procedure is used to validate the following business rules:
1392 --
1393 -- a) Multiple_entries_allowed_flag must be set to 'N' if the Cobra_flag on
1394 -- the benefit classification of the Recurring Element is set to 'Y'.
1395 -- b) Multiple_entries_allowed_flag can only be updated if:
1396 -- There exist no formula result rules where this element type
1397 -- is the subject of "Stop entry" or "Update recurring" result
1398 -- rules, unless the target of the rule is the same element as the
1399 -- source
1400 --
1401 -- Prerequisites:
1402 -- This procedure is called from insert_validate and update_validate.
1403 --
1404 -- In Parameters:
1405 -- p_element_type_id
1406 -- p_validation_start_date
1407 -- p_validation_end_date
1408 -- p_multiple_entries_allowed_flg
1409 -- p_benefit_classification_id
1410 -- p_processing_type
1411 --
1412 -- Post Success:
1413 -- Processing continues.
1414 --
1415 -- Post Failure:
1416 -- If the business rule validation fails then an error is raised.
1417 --
1418 -- Access Status:
1419 -- Internal Row Handler Use Only.
1420 --
1421 -- {End Of Comments}
1422 -- ----------------------------------------------------------------------------
1423 Procedure chk_multiple_entries_allowed
1424 (p_effective_date in date
1425 ,p_element_type_id in number default null
1426 ,p_validation_start_date in date default null
1427 ,p_validation_end_date in date default null
1428 ,p_multiple_entries_allowed_flg in varchar2
1429 ,p_benefit_classification_id in number
1430 ,p_processing_type in varchar2
1431 ) is
1432 --
1433 l_proc varchar2(72) := g_package||'chk_multiple_entries';
1434 l_exists varchar2(1);
1435 Cursor c_chk_ben_cobra_flag
1436 is
1437 select '1'
1438 from ben_benefit_classifications
1439 where benefit_classification_id = p_benefit_classification_id
1440 and cobra_flag = 'Y';
1441 --
1442 Begin
1443 hr_utility.set_location('Entering:'||l_proc, 5);
1444 --
1445 If hr_api.not_exists_in_hr_lookups
1446 (p_effective_date
1447 ,'YES_NO'
1448 ,p_multiple_entries_allowed_flg) Then
1449 --
1450 fnd_message.set_name('PAY','HR_52966_INVALID_LOOKUP');
1451 fnd_message.set_token('COLUMN','MULTIPLE_ENTRIES_ALLOWED_FLAG');
1452 fnd_message.set_token('LOOKUP_TYPE','YES_NO');
1453 fnd_message.raise_error;
1454 --
1455 End If;
1456 --
1457 Open c_chk_ben_cobra_flag;
1458 Fetch c_chk_ben_cobra_flag into l_exists;
1459 Close c_chk_ben_cobra_flag;
1460 If (p_multiple_entries_allowed_flg = 'Y' and p_processing_type = 'R'
1461 and l_exists is not null) Then
1462 --
1463 fnd_message.set_name('PAY','PAY_6964_ELEMENT_COBRA_MULTI');
1464 fnd_message.raise_error;
1465 --
1466 End If;
1467 If p_element_type_id is not null and
1468 (p_multiple_entries_allowed_flg <>
1469 pay_etp_shd.g_old_rec.multiple_entries_allowed_flag) Then
1470 --
1471 If pay_element_types_pkg.stop_entry_rules_exist
1472 (p_element_type_id
1473 ,p_validation_start_date
1474 ,p_validation_end_date) Then
1475 --
1476 fnd_message.set_name('PAY','HR_6953_PAY_ELE_NO_STOP_ENTRY');
1477 fnd_message.raise_error;
1478 --
1479 End If;
1480 If pay_element_types_pkg.update_recurring_rules_exist
1481 (p_element_type_id
1482 ,p_validation_start_date
1483 ,p_validation_end_date) Then
1484 --
1485 fnd_message.set_name('PAY','HR_6953_PAY_ELE_NO_STOP_ENTRY');
1486 fnd_message.raise_error;
1487 --
1488 End If;
1489 End If;
1490 --
1491 hr_utility.set_location('Leaving:'||l_proc, 10);
1492 End;
1493 --
1494 -- ----------------------------------------------------------------------------
1495 -- |---------------------< chk_process_in_run_flag >--------------------------|
1496 -- ----------------------------------------------------------------------------
1497 -- {Start Of Comments}
1498 --
1499 -- Description:
1500 -- This procedure is used to validate the following business rules:
1501 --
1502 -- a) If Process_in_run_flag is set to 'N', then Indirect_only_flag,
1503 -- Adjustment_only_flag, Multiply_value_flag and Once_each_period_flag
1504 -- must all be set to 'N'.
1505 --
1506 -- Prerequisites:
1507 -- This procedure is called from insert_validate and update_validate.
1508 --
1509 -- In Parameters:
1510 -- p_process_in_run_flag
1511 -- p_adjustment_only_flag
1512 -- p_indirect_only_flag
1513 -- p_multiply_value_flag
1514 -- p_once_each_period_flag
1515 --
1516 -- Post Success:
1517 -- Processing continues.
1518 --
1519 -- Post Failure:
1520 -- If the business rule validation fails then an error is raised.
1521 --
1522 -- Access Status:
1523 -- Internal Row Handler Use Only.
1524 --
1525 -- {End Of Comments}
1526 -- ----------------------------------------------------------------------------
1527 Procedure chk_process_in_run_flag
1528 (p_effective_date in date
1529 ,p_process_in_run_flag in varchar2
1530 ,p_adjustment_only_flag in varchar2
1531 ,p_indirect_only_flag in varchar2
1532 ,p_multiply_value_flag in varchar2
1533 ,p_post_termination_rule in varchar2
1534 ,p_once_each_period_flag in varchar2
1535 ) is
1536 --
1537 l_proc varchar2(72) := g_package||'chk_proc_in_run';
1538 --
1539 Begin
1540 hr_utility.set_location('Entering:'||l_proc, 5);
1541 --
1542 If hr_api.not_exists_in_hr_lookups
1543 (p_effective_date
1544 ,'YES_NO'
1545 ,p_process_in_run_flag) Then
1546 --
1547 fnd_message.set_name('PAY','HR_52966_INVALID_LOOKUP');
1548 fnd_message.set_token('COLUMN','PROCESS_IN_RUN_FLAG');
1549 fnd_message.set_token('LOOKUP_TYPE','YES_NO');
1550 fnd_message.raise_error;
1551 --
1552 End If;
1553 --
1554 If hr_api.not_exists_in_hr_lookups
1555 (p_effective_date
1556 ,'YES_NO'
1557 ,p_indirect_only_flag) Then
1558 --
1559 fnd_message.set_name('PAY','HR_52966_INVALID_LOOKUP');
1560 fnd_message.set_token('COLUMN','INDIRECT_ONLY_FLAG');
1561 fnd_message.set_token('LOOKUP_TYPE','YES_NO');
1562 fnd_message.raise_error;
1563 --
1564 End If;
1565 --
1566 If hr_api.not_exists_in_hr_lookups
1567 (p_effective_date
1568 ,'YES_NO'
1569 ,p_multiply_value_flag) Then
1570 --
1571 fnd_message.set_name('PAY','HR_52966_INVALID_LOOKUP');
1572 fnd_message.set_token('COLUMN','MULTIPLY_VALUE_FLAG');
1573 fnd_message.set_token('LOOKUP_TYPE','YES_NO');
1574 fnd_message.raise_error;
1575 --
1576 End If;
1577 --
1578 If hr_api.not_exists_in_hr_lookups
1579 (p_effective_date
1580 ,'TERMINATION_RULE'
1581 ,p_post_termination_rule) Then
1582 --
1583 fnd_message.set_name('PAY','HR_52966_INVALID_LOOKUP');
1584 fnd_message.set_token('COLUMN','POST_TERMINATION_RULE');
1585 fnd_message.set_token('LOOKUP_TYPE','TERMINATION_RULE');
1586 fnd_message.raise_error;
1587 --
1588 End If;
1589 --
1590 If hr_api.not_exists_in_hr_lookups
1591 (p_effective_date
1592 ,'YES_NO'
1593 ,p_once_each_period_flag) Then
1594 --
1595 fnd_message.set_name('PAY','HR_52966_INVALID_LOOKUP');
1596 fnd_message.set_token('COLUMN','ONCE_EACH_PERIOD_FLAG');
1597 fnd_message.set_token('LOOKUP_TYPE','YES_NO');
1598 fnd_message.raise_error;
1599 --
1600 End If;
1601 If (p_process_in_run_flag = 'N' and
1602 (p_adjustment_only_flag = 'Y' or
1603 p_indirect_only_flag = 'Y' or
1604 p_multiply_value_flag = 'Y' or
1605 p_once_each_period_flag = 'Y')) Then
1606 --
1607 fnd_message.set_name('PAY','PAY_34134_ELE_PROC_RUN_VLDTN');
1608 fnd_message.raise_error;
1609 --
1610 End If;
1611 --
1612 hr_utility.set_location('Leaving:'||l_proc, 10);
1613 End;
1614 --
1615 -- ----------------------------------------------------------------------------
1616 -- |---------------------< chk_processing_priority >--------------------------|
1617 -- ----------------------------------------------------------------------------
1618 -- {Start Of Comments}
1619 --
1620 -- Description:
1621 -- This procedure is used to validate the following business rules:
1622 --
1623 -- a) The Processing_priority can lie outside of the range for the
1624 -- classification; the user is warned if this will be the case.
1625 -- b) Processing_priority should not violate any formula result rules.
1626 --
1627 -- Prerequisites:
1628 -- This procedure is called from insert_validate and update_validate.
1629 --
1630 -- In Parameters:
1631 -- p_element_type_id
1632 -- p_validation_start_date
1633 -- p_validation_end_date
1634 -- p_processing_priority
1635 -- p_classification_id
1636 --
1637 -- Post Success:
1638 -- Processing continues. Also p_processing_priority_warning parameter is set
1639 -- to true if business rule a) is violated.
1640 --
1641 -- Post Failure:
1642 -- If the business rule validation fails then an error is raised.
1643 --
1644 -- Access Status:
1645 -- Internal Row Handler Use Only.
1646 --
1647 -- {End Of Comments}
1648 -- ----------------------------------------------------------------------------
1649 Procedure chk_processing_priority
1650 (p_element_type_id in number default null
1651 ,p_validation_start_date in date default null
1652 ,p_validation_end_date in date default null
1653 ,p_processing_priority in number
1654 ,p_classification_id in number
1655 ,p_processing_priority_warning out nocopy boolean
1656 ) is
1657 --
1658 l_proc varchar2(72) := g_package||'chk_processing_priority';
1659 l_high_priority pay_element_classifications.default_high_priority%type;
1660 l_low_priority pay_element_classifications.default_low_priority%type;
1661 Cursor c_get_ele_class_priority
1662 is
1663 select default_high_priority,
1664 default_low_priority
1665 from pay_element_classifications
1666 where classification_id = p_classification_id;
1667 --
1668 Begin
1669 hr_utility.set_location('Entering:'||l_proc, 5);
1670 --
1671 Open c_get_ele_class_priority;
1672 Fetch c_get_ele_class_priority into l_high_priority, l_low_priority;
1673 Close c_get_ele_class_priority;
1674 --
1675 If p_processing_priority not between l_high_priority and l_low_priority Then
1676 p_processing_priority_warning := True;
1677 End If;
1678 If p_element_type_id is not null and
1679 (p_processing_priority <> pay_etp_shd.g_old_rec.processing_priority) Then
1680 --
1681 If pay_element_types_pkg.priority_result_rule_violated
1682 (p_element_type_id,
1683 p_processing_priority,
1684 p_validation_start_date,
1685 p_validation_end_date,
1686 p_error_if_true => FALSE) Then
1687 --
1688 fnd_message.set_name('PAY','PAY_6149_ELEMENT_PRIORITY_UPD');
1689 fnd_message.raise_error;
1690 --
1691 End If;
1692 End If;
1693 --
1694 hr_utility.set_location('Leaving:'||l_proc, 10);
1695 End;
1696 --
1697 -- ----------------------------------------------------------------------------
1698 -- |---------------------< chk_processing_type >------------------------------|
1699 -- ----------------------------------------------------------------------------
1700 -- {Start Of Comments}
1701 --
1702 -- Description:
1703 -- This procedure is used to validate the following business rules:
1704 --
1705 -- a) If Processing_type is Non-Recurring the Benefit_classification_id
1706 -- cannot be set.
1707 --
1708 -- Prerequisites:
1709 -- This procedure is called from insert_validate.
1710 --
1711 -- In Parameters:
1712 -- p_processing_type
1713 -- p_benefit_classification_id
1714 --
1715 -- Post Success:
1716 -- Processing continues.
1717 --
1718 -- Post Failure:
1719 -- If the business rule validation fails then an error is raised.
1720 --
1721 -- Access Status:
1722 -- Internal Row Handler Use Only.
1723 --
1724 -- {End Of Comments}
1725 -- ----------------------------------------------------------------------------
1726 Procedure chk_processing_type
1727 (p_effective_date in date
1728 ,p_processing_type in varchar2
1729 ,p_benefit_classification_id in number
1730 ) is
1731 --
1732 l_proc varchar2(72) := g_package||'chk_processing_type';
1733 --
1734 Begin
1735 hr_utility.set_location('Entering:'||l_proc, 5);
1736 --
1737 If hr_api.not_exists_in_hr_lookups
1738 (p_effective_date
1739 ,'PROCESSING_TYPE'
1740 ,p_processing_type) Then
1741 --
1742 fnd_message.set_name('PAY','HR_52966_INVALID_LOOKUP');
1743 fnd_message.set_token('COLUMN','PROCESSING_TYPE');
1744 fnd_message.set_token('LOOKUP_TYPE','PROCESSING_TYPE');
1745 fnd_message.raise_error;
1746 --
1747 End If;
1748 --
1749 If (p_processing_type = 'N' and p_benefit_classification_id is not null) Then
1750 --
1751 fnd_message.set_name('PAY','PAY_34135_ELE_BEN_CLASS_VLDTN');
1752 fnd_message.raise_error;
1753 --
1754 End If;
1755 --
1756 hr_utility.set_location('Leaving:'||l_proc, 10);
1757 End;
1758 --
1759 -- ----------------------------------------------------------------------------
1760 -- |-----------------------< chk_standard_link_flag >-------------------------|
1761 -- ----------------------------------------------------------------------------
1762 -- {Start Of Comments}
1763 --
1764 -- Description:
1765 -- This procedure is used to validate the following business rules:
1766 --
1767 -- a) Standard_link_flag cannot be 'Y' when the Processing_type is 'N'
1768 -- or Multiple_entries_allowed_flag is 'Y'.
1769 --
1770 -- Prerequisites:
1771 -- This procedure is called from insert_validate and update_validate.
1772 --
1773 -- In Parameters:
1774 -- p_standard_link_flag
1775 -- p_processing_type
1776 -- p_multiple_entries_allowed_flg
1777 --
1778 -- Post Success:
1779 -- Processing continues.
1780 --
1781 -- Post Failure:
1782 -- If the business rule validation fails then an error is raised.
1783 --
1784 -- Access Status:
1785 -- Internal Row Handler Use Only.
1786 --
1787 -- {End Of Comments}
1788 -- ----------------------------------------------------------------------------
1789 Procedure chk_standard_link_flag
1790 (p_effective_date in date
1791 ,p_standard_link_flag in varchar2
1792 ,p_processing_type in varchar2
1793 ,p_multiple_entries_allowed_flg in varchar2
1794 ) is
1795 --
1796 l_proc varchar2(72) := g_package||'chk_standard_link';
1797 --
1798 Begin
1799 hr_utility.set_location('Entering:'||l_proc, 5);
1800 --
1801 If hr_api.not_exists_in_hr_lookups
1802 (p_effective_date
1803 ,'YES_NO'
1804 ,p_standard_link_flag) Then
1805 --
1806 fnd_message.set_name('PAY','HR_52966_INVALID_LOOKUP');
1807 fnd_message.set_token('COLUMN','STANDARD_LINK_FLAG');
1808 fnd_message.set_token('LOOKUP_TYPE','YES_NO');
1809 fnd_message.raise_error;
1810 --
1811 End If;
1812 --
1813 If (p_standard_link_flag = 'Y' and p_processing_type = 'N') Then
1814 --
1815 fnd_message.set_name('PAY','PAY_6140_ELEMENT_NO_STANDARD');
1816 fnd_message.raise_error;
1817 --
1818 End If;
1819 If (p_standard_link_flag = 'Y' and p_multiple_entries_allowed_flg = 'Y') Then
1820 --
1821 fnd_message.set_name('PAY','HR_6952_PAY_ELE_NO_STD_MULTI');
1822 fnd_message.raise_error;
1823 --
1824 End If;
1825 --
1826 hr_utility.set_location('Leaving:'||l_proc, 10);
1827 End;
1828 --
1829 -- ----------------------------------------------------------------------------
1830 -- |-----------------------< chk_qualifying_factors >-------------------------|
1831 -- ----------------------------------------------------------------------------
1832 -- {Start Of Comments}
1833 --
1834 -- Description:
1835 -- This procedure is used to validate the following business rules:
1836 --
1837 -- a) Qualifying_age should not be negative and should be between range
1838 -- 0 to 99.
1839 -- b) Qualifying_length_of_service should not be negative.
1840 -- c) Qualifying_units must be specified if qualifying_length_of_service is
1841 -- specified.
1842 --
1843 -- Prerequisites:
1844 -- This procedure is called from insert_validate and update_validate.
1845 --
1846 -- In Parameters:
1847 -- p_qualifying_age
1848 -- p_qualifying_length_of_service
1849 -- p_qualifying_units
1850 -- p_effective_date
1851 --
1852 -- Post Success:
1853 -- Processing continues.
1854 --
1855 -- Post Failure:
1856 -- If the business rule validation fails then an error is raised.
1857 --
1858 -- Access Status:
1859 -- Internal Row Handler Use Only.
1860 --
1861 -- {End Of Comments}
1862 -- ----------------------------------------------------------------------------
1863 Procedure chk_qualifying_factors
1864 (p_qualifying_age in number
1865 ,p_qualifying_length_of_service in number
1866 ,p_qualifying_units in varchar2
1867 ,p_effective_date in date
1868 ) is
1869 --
1870 l_proc varchar2(72) := g_package||'chk_qual_factors';
1871 --
1872 Begin
1873 hr_utility.set_location('Entering:'||l_proc, 5);
1874 --
1875 If nvl(p_qualifying_age,hr_api.g_number) <> hr_api.g_number Then
1876 If (sign(p_qualifying_age) = -1) or (p_qualifying_age not between 0 and 99)
1877 then
1878 --
1879 fnd_message.set_name('PAY','PAY_33096_QUALI_AGE_CHECK');
1880 fnd_message.raise_error;
1881 --
1882 End If;
1883 Elsif nvl(p_qualifying_length_of_service,hr_api.g_number) <> hr_api.g_number
1884 then
1885 If (sign(p_qualifying_length_of_service) = -1) Then
1886 --
1887 fnd_message.set_name('PAY','PAY_34143_QUALI_LOS_VLDTN');
1888 fnd_message.raise_error;
1889 --
1890 End If;
1891 --
1892 If nvl(p_qualifying_units,hr_api.g_varchar2) = hr_api.g_varchar2 Then
1893 --
1894 fnd_message.set_name('PAY','PAY_34137_QUALI_FACTORS_VLDTN');
1895 fnd_message.raise_error;
1896 --
1897 Else
1898 --
1899 If hr_api.not_exists_in_hr_lookups
1900 (p_effective_date
1901 ,'QUALIFYING_UNITS'
1902 ,p_qualifying_units) Then
1903 --
1904 fnd_message.set_name('PAY','HR_52966_INVALID_LOOKUP');
1905 fnd_message.set_token('COLUMN','QUALIFYING_UNITS');
1906 fnd_message.set_token('LOOKUP_TYPE','QUALIFYING_UNITS');
1907 fnd_message.raise_error;
1908 --
1909 End If;
1910 --
1911 End If;
1912 End If;
1913 --
1914 hr_utility.set_location('Leaving:'||l_proc, 5);
1915 End;
1916 --
1917 -- ----------------------------------------------------------------------------
1918 -- |----------------------< chk_third_party_pay_only_flag >-------------------|
1919 -- ----------------------------------------------------------------------------
1920 -- {Start Of Comments}
1921 --
1922 -- Description:
1923 -- This procedure is used to validate the following business rules:
1924 --
1925 -- a) Third_party_pay_only_flag cannot be updated when element entries
1926 -- exist.
1927 --
1928 -- Prerequisites:
1929 -- This procedure is called from insert_validate and update_validate.
1930 --
1931 -- In Parameters:
1932 -- p_effective_date
1933 -- p_element_type_id
1934 -- p_third_party_pay_only_flag
1935 -- p_indirect_only_flag
1936 --
1937 -- Post Success:
1938 -- Processing continues.
1939 --
1940 -- Post Failure:
1941 -- If the business rule validation fails then an error is raised.
1942 --
1943 -- Access Status:
1944 -- Internal Row Handler Use Only.
1945 --
1946 -- {End Of Comments}
1947 -- ----------------------------------------------------------------------------
1948 Procedure chk_third_party_pay_only_flag
1949 (p_effective_date in date
1950 ,p_element_type_id in number default null
1951 ,p_third_party_pay_only_flag in varchar2
1952 ,p_indirect_only_flag in varchar2
1953 ) is
1954 --
1955 l_proc varchar2(72) := g_package||'chk_third_party_pay';
1956 l_exists varchar2(1);
1957 --
1958 Begin
1959 hr_utility.set_location('Entering:'||l_proc, 5);
1960 --
1961 If hr_api.not_exists_in_hr_lookups
1962 (p_effective_date
1963 ,'YES_NO'
1964 ,nvl(p_third_party_pay_only_flag,'N')) Then
1965 --
1966 fnd_message.set_name('PAY','HR_52966_INVALID_LOOKUP');
1967 fnd_message.set_token('COLUMN','THIRD_PARTY_PAY_ONLY_FLAG');
1968 fnd_message.set_token('LOOKUP_TYPE','YES_NO');
1969 fnd_message.raise_error;
1970 --
1971 End If;
1972 If p_element_type_id is not null and
1973 (p_third_party_pay_only_flag <>
1974 nvl(pay_etp_shd.g_old_rec.third_party_pay_only_flag,hr_api.g_varchar2))
1975 Then
1976 --
1977 If pay_element_types_pkg.element_entries_exist
1978 (p_element_type_id
1979 ,FALSE) Then
1980 --
1981 fnd_message.set_name('PAY','PAY_34139_ELE_3RD_NO_UPD');
1982 fnd_message.raise_error;
1983 --
1984 End If;
1985 End If;
1986 --
1987 hr_utility.set_location('Leaving:'||l_proc, 10);
1988 End;
1989 --
1990 -- ----------------------------------------------------------------------------
1991 -- |------------------------< chk_iterative_rules >---------------------------|
1992 -- ----------------------------------------------------------------------------
1993 -- {Start Of Comments}
1994 --
1995 -- Description:
1996 -- This procedure is used to validate the following business rules:
1997 --
1998 -- a) If Iterative_flag is 'N' then Iterative_formula_id and
1999 -- Iterative_priority cannot be set.
2000 -- b) The Formula Type of the Iterative_formula_id must be 'Net to Gross'.
2001 -- c) If Iterative_flag is 'Y' then Iterative_formula_id must be specified.
2002 -- d) If Grossup_flag is 'Y', then Iterative_flag must be 'Y'.
2003 -- e) Process_mode value must be 'P', 'N' or 'S'
2004 --
2005 -- Prerequisites:
2006 -- This procedure is called from insert_validate and update_validate.
2007 --
2008 -- In Parameters:
2009 -- p_iterative_flag
2010 -- p_iterative_formula_id
2011 -- p_iterative_priority
2012 --
2013 -- Post Success:
2014 -- Processing continues.
2015 --
2016 -- Post Failure:
2017 -- If the business rule validation fails then an error is raised.
2018 --
2019 -- Access Status:
2020 -- Internal Row Handler Use Only.
2021 --
2022 -- {End Of Comments}
2023 -- ----------------------------------------------------------------------------
2024 Procedure chk_iterative_rules
2025 (p_iterative_flag in varchar2
2026 ,p_iterative_formula_id in number
2027 ,p_iterative_priority in number
2028 ,p_grossup_flag in varchar2
2029 ,p_process_mode in varchar2
2030 ,p_effective_date in date
2031 ,p_validation_start_date in date
2032 ,p_validation_end_date in date
2033 ) is
2034 --
2035 l_proc varchar2(72) := g_package||'chk_iterative_rules';
2036 l_formula_type ff_formula_types.formula_type_name%type;
2037
2038 Cursor c_formula_type
2039 is
2040 select formula_type_name
2041 from ff_formula_types ftp, ff_formulas_f fml
2042 where fml.formula_id = p_iterative_formula_id
2043 and p_effective_date between fml.effective_start_date
2044 and fml.effective_end_date
2045 and ftp.formula_type_id = fml.formula_type_id;
2046 --
2047 Begin
2048 hr_utility.set_location('Entering:'||l_proc, 5);
2049 --
2050 If nvl(p_iterative_formula_id,hr_api.g_number) <> hr_api.g_number Then
2051 If not (dt_api.rows_exist
2052 (p_base_table_name => 'FF_FORMULAS_F'
2053 ,p_base_key_column => 'FORMULA_ID'
2054 ,p_base_key_value => p_iterative_formula_id
2055 ,p_from_date => p_validation_start_date
2056 ,p_to_date => p_validation_end_date
2057 )) Then
2058 --
2059 fnd_message.set_name('PAY','PAY_33085_INVALID_FK');
2060 fnd_message.set_token('COLUMN','FORMULA_ID');
2061 fnd_message.set_token('TABLE','FF_FORMULAS_F');
2062 fnd_message.raise_error;
2063 --
2064 End If;
2065 End If;
2066 --
2067 If hr_api.not_exists_in_hr_lookups
2068 (p_effective_date
2069 ,'YES_NO'
2070 ,nvl(p_iterative_flag,'N')) Then
2071 --
2072 fnd_message.set_name('PAY','HR_52966_INVALID_LOOKUP');
2073 fnd_message.set_token('COLUMN','ITERATIVE_FLAG');
2074 fnd_message.set_token('LOOKUP_TYPE','YES_NO');
2075 fnd_message.raise_error;
2076 --
2077 End If;
2078 --
2079 If hr_api.not_exists_in_hr_lookups
2080 (p_effective_date
2081 ,'YES_NO'
2082 ,nvl(p_grossup_flag,'N')) Then
2083 --
2084 fnd_message.set_name('PAY','HR_52966_INVALID_LOOKUP');
2085 fnd_message.set_token('COLUMN','GROSSUP_FLAG');
2086 fnd_message.set_token('LOOKUP_TYPE','YES_NO');
2087 fnd_message.raise_error;
2088 --
2089 End If;
2090 --
2091 If p_iterative_flag = 'N' and
2092 (p_iterative_formula_id is not null or
2093 p_iterative_priority is not null) Then
2094 --
2095 fnd_message.set_name('PAY','PAY_34144_ELE_ITR_NO_FORML_PRI');
2096 fnd_message.raise_error;
2097 --
2098 End If;
2099 If p_iterative_flag = 'Y' and p_iterative_formula_id is null Then
2100 --
2101 fnd_message.set_name('PAY','PAY_34146_ELE_ITR_FORML_REQD');
2102 fnd_message.raise_error;
2103 --
2104 End If;
2105 If p_iterative_formula_id is not null Then
2106 --
2107 Open c_formula_type;
2108 Fetch c_formula_type into l_formula_type;
2109 Close c_formula_type;
2110 --
2111 If upper(l_formula_type) <> 'NET TO GROSS' Then
2112 --
2113 fnd_message.set_name('PAY','PAY_34145_ELE_ITR_FTYPE');
2114 fnd_message.raise_error;
2115 --
2116 End If;
2117 --
2118 End If;
2119 If (p_grossup_flag = 'Y' and p_iterative_flag <> 'Y') Then
2120 --
2121 fnd_message.set_name('PAY','PAY_34147_ELE_ITR_GROSSUP');
2122 fnd_message.raise_error;
2123 --
2124 End If;
2125 If p_process_mode not in ('P','N','S') Then
2126 --
2127 fnd_message.set_name('PAY','PAY_34148_ELE_PROC_MODE');
2128 fnd_message.raise_error;
2129 --
2130 End If;
2131 --
2132 hr_utility.set_location('Leaving:'||l_proc, 10);
2133 End;
2134 --
2135 -- ----------------------------------------------------------------------------
2136 -- |------------------------< chk_proration_values >--------------------------|
2137 -- ----------------------------------------------------------------------------
2138 --
2139 -- Description:
2140 -- This procedure is used to validate the following attributes against the
2141 -- respective parent table.
2142 --
2143 -- a) retro_summ_ele_id
2144 -- b) proration_group_id
2145 -- c) proration_formula_id
2146 -- d) recalc_event_group_id
2147 --
2148 -- ----------------------------------------------------------------------------
2149 Procedure chk_proration_values
2150 (p_retro_summ_ele_id in number
2151 ,p_proration_group_id in number
2152 ,p_proration_formula_id in number
2153 ,p_recalc_event_group_id in number
2154 ,p_validation_start_date in date
2155 ,p_validation_end_date in date
2156 ) is
2157 --
2158 l_proc varchar2(72) := g_package||'chk_proration_values';
2159 l_exists varchar2(1);
2160 p_event_group_type pay_event_groups.event_group_type%type;
2161
2162 Cursor c_chk_event_group_id(p_event_group_id number)
2163 is
2164 select '1', event_group_type
2165 from pay_event_groups
2166 where event_group_id = p_event_group_id;
2167 --
2168 Begin
2169 hr_utility.set_location('Entering:'||l_proc, 5);
2170 --
2171 If nvl(p_retro_summ_ele_id,hr_api.g_number) <> hr_api.g_number Then
2172 If not (dt_api.rows_exist
2173 (p_base_table_name => 'PAY_ELEMENT_TYPES_F'
2174 ,p_base_key_column => 'ELEMENT_TYPE_ID'
2175 ,p_base_key_value => p_retro_summ_ele_id
2176 ,p_from_date => p_validation_start_date
2177 ,p_to_date => p_validation_end_date
2178 )) Then
2179 --
2180 fnd_message.set_name('PAY','PAY_33085_INVALID_FK');
2181 fnd_message.set_token('COLUMN','RETRO_SUMM_ELE_ID');
2182 fnd_message.set_token('TABLE','PAY_ELEMENT_TYPES_F');
2183 fnd_message.raise_error;
2184 --
2185 End If;
2186 End If;
2187 --
2188 If nvl(p_proration_formula_id,hr_api.g_number) <> hr_api.g_number Then
2189 If not (dt_api.rows_exist
2190 (p_base_table_name => 'FF_FORMULAS_F'
2191 ,p_base_key_column => 'FORMULA_ID'
2192 ,p_base_key_value => p_proration_formula_id
2193 ,p_from_date => p_validation_start_date
2194 ,p_to_date => p_validation_end_date
2195 )) Then
2196 --
2197 fnd_message.set_name('PAY','PAY_33085_INVALID_FK');
2198 fnd_message.set_token('COLUMN','PRORATION_FORMULA_ID');
2199 fnd_message.set_token('TABLE','FF_FORMULAS_F');
2200 fnd_message.raise_error;
2201 --
2202 End If;
2203 End If;
2204 --
2205 If nvl(p_proration_group_id,hr_api.g_number) <> hr_api.g_number Then
2206 --
2207 Open c_chk_event_group_id(p_proration_group_id);
2208 Fetch c_chk_event_group_id into l_exists, p_event_group_type;
2209 If c_chk_event_group_id%notfound Then
2210 --
2211 Close c_chk_event_group_id;
2212 fnd_message.set_name('PAY','PAY_33085_INVALID_FK');
2213 fnd_message.set_token('COLUMN','PRORATION_GROUP_ID');
2214 fnd_message.set_token('TABLE','PAY_EVENT_GROUPS');
2215 fnd_message.raise_error;
2216 --
2217 Elsif p_event_group_type <> 'P' Then
2218 --
2219 Close c_chk_event_group_id;
2220 fnd_message.set_name('PAY','PAY_34141_ELE_PRORATION');
2221 fnd_message.raise_error;
2222 --
2223 End If;
2224 Close c_chk_event_group_id;
2225 --
2226 End If;
2227 --
2228 If nvl(p_recalc_event_group_id,hr_api.g_number) <> hr_api.g_number Then
2229 --
2230 Open c_chk_event_group_id(p_recalc_event_group_id);
2231 Fetch c_chk_event_group_id into l_exists, p_event_group_type;
2232 If c_chk_event_group_id%notfound Then
2233 --
2234 Close c_chk_event_group_id;
2235 fnd_message.set_name('PAY','PAY_33085_INVALID_FK');
2236 fnd_message.set_token('COLUMN','RECALC_EVENT_GROUP_ID');
2237 fnd_message.set_token('TABLE','PAY_EVENT_GROUPS');
2238 fnd_message.raise_error;
2239 --
2240 Elsif p_event_group_type <> 'R' Then
2241 --
2242 Close c_chk_event_group_id;
2243 fnd_message.set_name('PAY','PAY_34149_ELE_RECALC_EVENT');
2244 fnd_message.raise_error;
2245 --
2246 End If;
2247 Close c_chk_event_group_id;
2248 --
2249 End If;
2250 --
2251 hr_utility.set_location('Leaving:'||l_proc, 10);
2252 End;
2253 --
2254 -- ----------------------------------------------------------------------------
2255 -- |------------------------< chk_time_definition>----------------------------|
2256 -- ----------------------------------------------------------------------------
2257 -- {Start Of Comments}
2258 --
2259 -- Description:
2260 -- This procedure is used to check whether the specified time definition
2261 -- is valid or not.
2262 -- The typical business logic is as below:
2263 -- 1) If the user has specified the time definition type as STATIC (S)
2264 -- and the time definition id has not specified then the message
2265 -- PAY_34080_FLSA_STATIC_TIME will be raised.
2266 -- 2) If the user has specified time definition type other than STATIC(S)
2267 -- and time definition id also then the message
2268 -- PAY_34052_FLSA_ARG_NOT_NULL will be raised.
2269 -- In parameters:
2270 -- P_TIME_DEFINITION_ID : ID of the time defintion specified.
2271 -- P_TIME_DEFINITION_TYPE : Time Definition Type
2272 -- P_BUSINESS_GROUP_ID : Business Group ID
2273 -- P_LEGISLATION_CODE : Legislation Code
2274 -- P_EFFECTIVE_DATE : Effective Date
2275 -- Post success:
2276 -- Processing continues.
2277 -- Post failure:
2278 -- Raise an error and stops the process.
2279 -- Access Status:
2280 -- Internal Row Handler Use Only.
2281 --
2282 -- {End Of Comments}
2283 -- ----------------------------------------------------------------------------
2284 Procedure chk_time_definition(p_time_definition_id NUMBER
2285 ,p_time_definition_type VARCHAR2
2286 ,p_business_group_id number
2287 ,p_legislation_code varchar2
2288 ,p_effective_date date) is
2289 --
2290 l_proc varchar2(72) := g_package||'chk_time_definition';
2291 l_flag varchar2(1);
2292
2293 Cursor csr_time_definition is
2294 select 'Y'
2295 from pay_time_definitions
2296 where time_definition_id = p_time_definition_id
2297 AND DEFINITION_TYPE = 'S'
2298 and nvl (business_group_id, nvl(p_business_group_id, -1)) = nvl(p_business_group_id,-1)
2299 and nvl (legislation_code, nvl(p_legislation_code, '-1')) = nvl(p_legislation_code, '-1');
2300
2301 Begin
2302 --
2303 hr_utility.set_location('Entering:'||l_proc, 5);
2304 If (p_time_definition_type <> hr_api.g_varchar2) AND
2305 -- The below check has been added because, we treat NULL and N as same for
2306 -- the table PAY_ELEMENT_TYPES_F for the column TIME_DEFINITION_TYPE.
2307 -- So no need to check if the value is NULL or N.
2308 (p_time_definition_type IS NOT NULL AND p_time_definition_type <> 'N') AND
2309 hr_api.not_exists_in_hr_lookups( p_effective_date
2310 ,'PAY_TIME_DEFINITION_TYPE'
2311 ,p_time_definition_type) Then
2312
2313 hr_utility.set_location('The user has passed some value to time definition type ', 20);
2314 fnd_message.set_name('PAY','HR_52966_INVALID_LOOKUP');
2315 fnd_message.set_token('COLUMN','TIME_DEFINITION_TYPE');
2316 fnd_message.set_token('LOOKUP_TYPE','PAY_TIME_DEFINITION_TYPE');
2317 fnd_message.raise_error;
2318 ELSIF ( p_time_definition_type = 'S' ) THEN
2319 IF (p_time_definition_id IS NOT NULL) THEN
2320 open csr_time_definition;
2321 fetch csr_time_definition into l_flag;
2322 If (csr_time_definition%notfound) then
2323 close csr_time_definition;
2324 fnd_message.set_name('PAY','PAY_34056_FLSA_INV_TIME_DEF_ID');
2325 fnd_message.raise_error;
2326 end if;
2327 close csr_time_definition;
2328 ELSE
2329 fnd_message.set_name('PAY','PAY_34080_FLSA_STATIC_TIME');
2330 fnd_message.raise_error;
2331 END IF;
2332 ELSIF (nvl(p_time_definition_type, '-1') <> 'S' AND p_time_definition_id IS NOT NULL) THEN
2333 fnd_message.set_name('PAY','PAY_34052_FLSA_ARG_NOT_NULL');
2334 fnd_message.set_token('ARGUMENT','TIME_DEFINITION_ID');
2335 fnd_message.raise_error;
2336 END IF;
2337 hr_utility.set_location('Leaving:'||l_proc, 10);
2338 --
2339 End chk_time_definition;
2340 --
2341 -- ----------------------------------------------------------------------------
2342 -- |------------------------< chk_advance_element>----------------------------|
2343 -- ----------------------------------------------------------------------------
2344 -- {Start Of Comments}
2345 --
2346 -- Description:
2347 -- This procedure is used to check whether the specified advance element or
2348 -- dediction element is valid or not.
2349 -- The typical business logic is as below:
2350 -- 1) If the user has specified the advance_element_type_id as Invalid or
2351 -- deduction_element_type_id as invalid then the message
2352 -- PAY_34080_FLSA_STATIC_TIME will be raised.
2353 -- 2) If the user has specified advance_payable as 'N' and advance_element_type_id
2354 -- is passed as a parameter to API then then the message
2355 -- PAY_34052_FLSA_ARG_NOT_NULL will be raised.
2356 -- Same is checked for advance_deduction.
2357 -- In parameters:
2358 -- P_TIME_DEFINITION_ID : ID of the time defintion specified.
2359 -- P_TIME_DEFINITION_TYPE : Time Definition Type
2360 -- P_BUSINESS_GROUP_ID : Business Group ID
2361 -- P_LEGISLATION_CODE : Legislation Code
2362 -- P_EFFECTIVE_DATE : Effective Date
2363 -- Post success:
2364 -- Processing continues.
2365 -- Post failure:
2366 -- Raise an error and stops the process.
2367 -- Access Status:
2368 -- Internal Row Handler Use Only.
2369 --
2370 -- {End Of Comments}
2371 -- ----------------------------------------------------------------------------
2372 Procedure chk_advance_element(
2373 p_element_type_id IN Number
2374 ,p_advance_payable IN Varchar2
2375 ,p_advance_deduction IN Varchar2
2376 ,p_advance_element_type_id IN Number
2377 ,p_deduction_element_type_id IN Number
2378 ,p_business_group_id IN Number
2379 ,p_legislation_code IN Varchar2
2380 ,p_effective_date IN Date
2381 ,p_validation_start_date IN Date
2382 ,p_validation_end_date IN Date
2383 ,p_datetrack_mode in varchar2
2384 ) is
2385 --
2386 l_proc varchar2(72) := g_package||'get_advance_element';
2387 l_flag varchar2(1);
2388 l_count number;
2389 l_a_count number;
2390 l_d_count number;
2391 --
2392 Begin
2393 --
2394 hr_utility.set_location('Entering:'||l_proc, 5);
2395 -- Parameter Vaidation
2396 -- check if the flag is not set but the id is passed
2397 If ( p_advance_payable = 'N' OR p_advance_payable is Null) and p_advance_element_type_id is not null then
2398 fnd_message.set_name('PAY','PAY_33299_INVALID_ADVANCE_PAY');
2399 fnd_message.set_token('ADVANCE_PAYABLE', 'Advance Payable' );
2400 fnd_message.set_token('ADVANCE', 'Advance' );
2401 fnd_message.raise_error;
2402 end if;
2403 --
2404 If ( p_advance_deduction = 'N' OR p_advance_deduction is Null) and p_deduction_element_type_id is not null then
2405 fnd_message.set_name('PAY','PAY_33299_INVALID_ADVANCE_PAY');
2406 fnd_message.set_token('ADVANCE_PAYABLE', 'Advance Deduction' );
2407 fnd_message.set_token('ADVANCE', 'Deduction' );
2408 fnd_message.raise_error;
2409 end if;
2410 --
2411 --
2412 if p_datetrack_mode = 'INSERT' then
2413 -- Normal element with Advance Element
2414 if p_advance_payable = 'Y' and p_advance_element_type_id is not null then
2415 select count(*) into l_count from pay_element_types_f
2416 where element_type_id = p_advance_element_type_id and
2417 effective_start_date <= p_validation_start_date and
2418 effective_end_date >= p_validation_end_date and
2419 p_effective_date between effective_start_date and effective_end_date;
2420
2421 if l_count = 0 then
2422 fnd_message.set_name('PAY','PAY_33739_INVALID_ADVANCE_ARG');
2423 fnd_message.set_token('ADVANCE', 'Advance' );
2424 fnd_message.raise_error;
2425 end if;
2426 end if;
2427 --
2428 --Normal element with Deduction Element
2429 if p_advance_deduction = 'Y' and p_deduction_element_type_id is not null then
2430 select count(*)
2431 into l_count
2432 from pay_element_types_f
2433 where element_type_id = p_deduction_element_type_id and
2434 effective_start_date <= p_validation_start_date and
2435 effective_end_date >= p_validation_end_date and
2436 p_effective_date between effective_start_date and effective_end_date;
2437
2438 if l_count = 0 then
2439 fnd_message.set_name('PAY','PAY_33739_INVALID_ADVANCE_ARG');
2440 fnd_message.set_token('ADVANCE', 'Deduction' );
2441 fnd_message.raise_error;
2442 end if;
2443 end if;
2444 -- No INSERT Validation For Advance Or Deduction Element
2445 end if; -- end of Insert
2446
2447 if p_datetrack_mode = 'UPDATE' or p_datetrack_mode = 'CORRECTION' then
2448 -- UPDATE Validation For Normal Advance Element
2449 if p_advance_payable = 'Y' and p_advance_element_type_id is not null then
2450 select count(*)
2451 into l_count
2452 from pay_element_types_f
2453 where element_type_id = p_advance_element_type_id and
2454 effective_start_date <= p_validation_start_date and
2455 effective_end_date >= p_validation_end_date and
2456 p_effective_date between effective_start_date and effective_end_date;
2457
2458 if l_count = 0 then
2459 fnd_message.set_name('PAY','PAY_33739_INVALID_ADVANCE_ARG');
2460 fnd_message.set_token('ADVANCE', 'Advance' );
2461 fnd_message.raise_error;
2462 end if;
2463 end if;
2464 --
2465 -- UPDATE Validation For Normal Deduction Element
2466 if p_advance_deduction = 'Y' and p_deduction_element_type_id is not null then
2467 select count(*)
2468 into l_count
2469 from pay_element_types_f
2470 where element_type_id = p_deduction_element_type_id and
2471 effective_start_date <= p_validation_start_date and
2472 effective_end_date >= p_validation_end_date and
2473 p_effective_date between effective_start_date and effective_end_date;
2474
2475 if l_count = 0 then
2476 fnd_message.set_name('PAY','PAY_33739_INVALID_ADVANCE_ARG');
2477 fnd_message.set_token('ADVANCE', 'Deduction' );
2478 fnd_message.raise_error;
2479 end if;
2480 end if;
2481
2482 -- No UPDATE Validation For Advance/Deduction Element
2483 end if; -- end of Update
2484 --
2485 if p_datetrack_mode = 'DELETE_NEXT_CHANGE' or p_datetrack_mode = 'DELETE_FUTURE_CHANGE' then
2486 if p_advance_payable = 'Y' and p_advance_element_type_id is not null then
2487 --
2488 select count(*) into l_count from pay_element_types_f
2489 where element_type_id = p_advance_element_type_id and
2490 effective_start_date <= p_validation_start_date and
2491 effective_end_date >= p_validation_end_date;
2492
2493 if l_count = 0 then
2494 fnd_message.set_name('PAY','PAY_33425_ADV_ELM_NCD');
2495 fnd_message.set_token('ADVANCE', 'Advance' );
2496 fnd_message.set_token('DELETE_MODE', p_datetrack_mode);
2497 fnd_message.raise_error;
2498 end if;
2499 end if;
2500 --
2501 -- Delete Next Change Validation for Normal Deduction Element
2502 if p_advance_deduction = 'Y' and p_deduction_element_type_id is not null then
2503 --
2504 select count(*) into l_count from pay_element_types_f
2505 where element_type_id = p_deduction_element_type_id and
2506 effective_start_date <= p_validation_start_date and
2507 effective_end_date >= p_validation_end_date;
2508
2509 if l_count = 0 then
2510 fnd_message.set_name('PAY','PAY_33425_ADV_ELM_NCD');
2511 fnd_message.set_token('ADVANCE', 'Deduction' );
2512 fnd_message.set_token('DELETE_MODE', p_datetrack_mode);
2513 fnd_message.raise_error;
2514 end if;
2515 end if;
2516 end if; -- end of delete_next_change
2517
2518 -- PURGE Mode For Advance Element.
2519 if p_datetrack_mode = 'ZAP' then
2520 select count(*) into l_count from pay_element_types_f where advance_element_type_id = p_element_type_id ;
2521 if l_count > 0 then
2522 fnd_message.set_name('PAY','PAY_33450_ADVANCE_ELE_DELETE');
2523 fnd_message.set_token('ADVANCE', 'Advance' );
2524 fnd_message.set_token('DELETE_MODE', p_datetrack_mode);
2525 fnd_message.raise_error;
2526 end if;
2527 --
2528 select count(*) into l_count from pay_element_types_f where deduction_element_type_id = p_element_type_id ;
2529 if l_count > 0 then
2530 fnd_message.set_name('PAY','PAY_33450_ADVANCE_ELE_DELETE');
2531 fnd_message.set_token('ADVANCE', 'Deduction' );
2532 fnd_message.set_token('DELETE_MODE', p_datetrack_mode);
2533 fnd_message.raise_error;
2534 end if;
2535 end if;
2536 -- end of ZAP
2537
2538 --DELETE Mode For Advance Element.
2539 if p_datetrack_mode = 'DELETE' and ((p_advance_payable = 'N' OR p_advance_payable is null) and (p_advance_deduction = 'N' OR p_advance_deduction is null)) then
2540 select count(*) into l_count
2541 from pay_element_types_f
2542 where (advance_element_type_id = p_element_type_id or
2543 deduction_element_type_id = p_element_type_id );
2544 if l_count <> 0 then
2545 -- advance
2546 select count(*) into l_a_count from pay_element_types_f
2547 where advance_element_type_id = p_element_type_id
2548 and not (effective_start_date >= p_validation_start_date
2549 and effective_end_date <= p_validation_end_date)
2550 and p_effective_date between effective_start_date and effective_end_date;
2551 -- deduction
2552 select count(*) into l_d_count from pay_element_types_f
2553 where deduction_element_type_id = p_element_type_id
2554 and not (effective_start_date >= p_validation_start_date
2555 and effective_end_date <= p_validation_end_date)
2556 and p_effective_date between effective_start_date and effective_end_date;
2557 --
2558 if l_a_count > 0 OR l_d_count > 0 then
2559 fnd_message.set_name('PAY','PAY_33450_ADVANCE_ELE_DELETE');
2560 fnd_message.set_token('ADVANCE', 'Advance or Deduction' );
2561 fnd_message.set_token('DELETE_MODE', p_datetrack_mode);
2562 fnd_message.raise_error;
2563 end if;
2564 end if;
2565 --
2566 end if;
2567 -- end of DELETE
2568 hr_utility.set_location('Leaving:'||l_proc, 10);
2569 --
2570 End chk_advance_element;
2571 --
2572 -- ----------------------------------------------------------------------------
2573 -- |------------------------< chk_update_allowed >----------------------------|
2574 -- ----------------------------------------------------------------------------
2575 -- {Start Of Comments}
2576 --
2577 -- Description:
2578 -- This procedure is used to validate the following business rules:
2579 --
2580 -- a) Indirect_Only_Flag, Additional_Entry_Allowed_Flag, Standard_Link_Flag,
2581 -- Adjustment_Only_Flag, Post_Termination_Rule, Process_In_Run_Flag and
2582 -- Once_Each_Period_Flag can only be updated if:
2583 --
2584 -- i. There are no element links for the element.
2585 -- ii. The change will take effect for the complete lifetime of element
2586 -- type (ie. cannot perform the update if the element has been
2587 -- date-effectively updated).
2588 -- b) Adjustment_Only_Flag can only be updated if:
2589 --
2590 -- - There exists no formula result rules where the element type
2591 -- is the subject of indirect results.
2592 --
2593 -- c) Post_Termination_Rule, Process_In_Run_Flag and Once_Each_Period_Flag
2594 -- can only be updated if:
2595 --
2596 -- - There exists no run results for the element.
2597 --
2598 -- Prerequisites:
2599 -- This procedure is called from update_validate.
2600 --
2601 -- In Parameters:
2602 -- p_element_type_id
2603 -- p_Indirect_Only_Flag
2604 -- p_Additional_Entry_Allowed_Flg
2605 -- p_Standard_Link_Flag
2606 -- p_Adjustment_Only_Flag
2607 -- p_Post_Termination_Rule
2608 -- p_Process_In_Run_Flag
2609 -- p_validation_start_date
2610 -- p_validation_end_date
2611 --
2612 -- Post Success:
2613 -- Processing continues. The OUT parameter p_datetrack_update_mode will be
2614 -- set to 'CORRECTION' if rule a-ii is satisified.
2615 --
2616 -- Post Failure:
2617 -- If the business rule validation fails then an error is raised.
2618 --
2619 -- Access Status:
2620 -- Internal Row Handler Use Only.
2621 --
2622 -- {End Of Comments}
2623 -- ----------------------------------------------------------------------------
2624 Procedure chk_update_allowed
2625 (p_element_type_id in number
2626 ,p_indirect_only_flag in varchar2
2627 ,p_additional_entry_allowed_flg in varchar2
2628 ,p_standard_link_flag in varchar2
2629 ,p_adjustment_only_flag in varchar2
2630 ,p_post_termination_rule in varchar2
2631 ,p_process_in_run_flag in varchar2
2632 ,p_validation_start_date in date
2633 ,p_validation_end_date in date
2634 ,p_once_each_period_flag in varchar2
2635 ) is
2636 --
2637 l_proc varchar2(72) := g_package||'chk_update_allowed';
2638 l_element_links boolean;
2639 l_indirect_results boolean;
2640 l_run_results boolean;
2641 --
2642 Begin
2643 hr_utility.set_location('Entering:'||l_proc, 5);
2644 --
2645 If (
2646 (p_indirect_only_flag <> pay_etp_shd.g_old_rec.indirect_only_flag)
2647 or
2648 (p_additional_entry_allowed_flg <>
2649 pay_etp_shd.g_old_rec.additional_entry_allowed_flag)
2650 or
2651 (p_standard_link_flag <> pay_etp_shd.g_old_rec.standard_link_flag)
2652 or
2653 (p_adjustment_only_flag <> pay_etp_shd.g_old_rec.adjustment_only_flag)
2654 or
2655 (p_post_termination_rule <> pay_etp_shd.g_old_rec.post_termination_rule)
2656 or
2657 (p_process_in_run_flag <> pay_etp_shd.g_old_rec.process_in_run_flag)
2658 or
2659 (p_once_each_period_flag <> pay_etp_shd.g_old_rec.once_each_period_flag)
2660 ) Then
2661 --
2662 l_element_links := pay_element_types_pkg.links_exist
2663 (p_element_type_id,
2664 p_validation_start_date,
2665 p_validation_end_date);
2666 --
2667 If l_element_links Then
2668 --
2669 fnd_message.set_name('PAY','PAY_6147_ELEMENT_LINK_UPDATE');
2670 fnd_message.raise_error;
2671 --
2672 End If;
2673 If (p_adjustment_only_flag <> pay_etp_shd.g_old_rec.adjustment_only_flag
2674 )Then
2675 --
2676 l_indirect_results := pay_element_types_pkg.fed_by_indirect_results
2677 (p_element_type_id,
2678 p_validation_start_date,
2679 p_validation_end_date);
2680 --
2681 If l_indirect_results Then
2682 --
2683 fnd_message.set_name('PAY','PAY_34138_ELE_NO_ADJ_FRR_UPD');
2684 fnd_message.raise_error;
2685 --
2686 End If;
2687 --
2688 End If;
2689 If ((p_post_termination_rule <> pay_etp_shd.g_old_rec.post_termination_rule)
2690 or
2691 (p_process_in_run_flag <> pay_etp_shd.g_old_rec.process_in_run_flag)
2692 or
2693 (p_once_each_period_flag <> pay_etp_shd.g_old_rec.once_each_period_flag)
2694 ) Then
2695 --
2696 l_run_results:= pay_element_types_pkg.run_results_exist
2697 (p_element_type_id, p_validation_start_date, p_validation_end_date);
2698 --
2699 If l_run_results Then
2700 --
2701 fnd_message.set_name('PAY','PAY_6909_ELEMENT_NO_UPD_RR');
2702 fnd_message.raise_error;
2703 --
2704 End If;
2705 --
2706 End If;
2707 --
2708 End If;
2709 --
2710 hr_utility.set_location('Leaving:'||l_proc, 10);
2711 End;
2712 --
2713 -- ----------------------------------------------------------------------------
2714 -- |------------------------< chk_delete_allowed >----------------------------|
2715 -- ----------------------------------------------------------------------------
2716 -- {Start Of Comments}
2717 --
2718 -- Description:
2719 -- This procedure is used to validate the delete business rules.
2720 --
2721 -- Prerequisites:
2722 -- This procedure is called from delete_validate.
2723 --
2724 -- In Parameters:
2725 -- p_element_type_id
2726 -- p_processing_priority
2727 -- p_validation_start_date
2728 -- p_validation_end_date
2729 -- p_datetrack_delete_mode
2730 --
2731 -- Post Success:
2732 -- Processing continues.
2733 --
2734 -- Post Failure:
2735 -- If the business rule validation fails then an error is raised.
2736 --
2737 -- Access Status:
2738 -- Internal Row Handler Use Only.
2739 --
2740 -- {End Of Comments}
2741 -- ----------------------------------------------------------------------------
2742 Procedure chk_delete_allowed
2743 (p_element_type_id in number
2744 ,p_processing_priority in number
2745 ,p_validation_start_date in date
2746 ,p_validation_end_date in date
2747 ,p_datetrack_delete_mode in varchar2
2748 ) is
2749 --
2750 l_proc varchar2(72) := g_package||'chk_delete_allowed';
2751 --
2752 -- Returns TRUE if there are accrual plans for the parameter element type
2753 --
2754 Function accrual_plan_exists
2755 (p_element_type_id number) return boolean is
2756 --
2757 l_accrual_exists boolean := FALSE;
2758 l_dummy_number number(1);
2759 --
2760 cursor csr_accrual
2761 is
2762 select null
2763 from pay_accrual_plans
2764 where accrual_plan_element_type_id = p_element_type_id;
2765 --
2766 begin
2767 --
2768 hr_utility.set_location (' ACCRUAL_PLAN_EXISTS',1);
2769 --
2770 open csr_accrual;
2771 fetch csr_accrual into l_dummy_number;
2772 l_accrual_exists := csr_accrual%found;
2773 close csr_accrual;
2774 --
2775 return l_accrual_exists;
2776 --
2777 end;
2778 --
2779 -- Returns TRUE if there are benefit contributions which refer to the
2780 -- specified element
2781 --
2782 Function benefit_contributions_exist
2783 (p_element_type_id number,
2784 p_validation_start_date date,
2785 p_validation_end_date date
2786 ) return boolean is
2787 --
2788 l_contribution_exists boolean := FALSE;
2789 l_dummy_number number(1);
2790 --
2791 Cursor csr_contribution
2792 is
2793 select 1
2794 from ben_benefit_contributions_f
2795 where element_type_id = p_element_type_id
2796 and effective_start_date <= p_validation_end_date
2797 and effective_end_date >= p_validation_start_date;
2798 --
2799 begin
2800 --
2801 hr_utility.set_location(' benefit_contributions_exist',1);
2802 --
2803 open csr_contribution;
2804 fetch csr_contribution into l_dummy_number;
2805 l_contribution_exists := csr_contribution%found;
2806 close csr_contribution;
2807 --
2808 return l_contribution_exists;
2809 --
2810 end;
2811 --
2812 -- Returns TRUE if the element has an input value which is used as a pay
2813 -- basis.
2814 --
2815 Function element_used_as_pay_basis
2816 (p_element_type_id number)
2817 return boolean is
2818 --
2819 l_pay_basis_element boolean := FALSE;
2820 l_dummy_number number(1);
2821 --
2822 cursor csr_pay_basis
2823 is
2824 select 1
2825 from per_pay_bases BASIS,
2826 pay_input_values_f IV
2827 where iv.input_value_id = basis.input_value_id
2828 and iv.element_type_id = p_element_type_id;
2829 --
2830 begin
2831 --
2832 open csr_pay_basis;
2833 fetch csr_pay_basis into l_dummy_number;
2834 l_pay_basis_element := csr_pay_basis%found;
2835 close csr_pay_basis;
2836 --
2837 return l_pay_basis_element;
2838 --
2839 end;
2840 --
2841 Begin
2842 hr_utility.set_location('Entering:'||l_proc, 5);
2843 --
2844 If p_datetrack_delete_mode = 'DELETE_NEXT_CHANGE' Then
2845 If pay_element_types_pkg.priority_result_rule_violated
2846 (p_element_type_id,
2847 p_processing_priority,
2848 p_validation_start_date,
2849 p_validation_end_date) Then
2850 --
2851 fnd_message.set_name('PAY','PAY_6149_ELEMENT_PRIORITY_UPD');
2852 fnd_message.raise_error;
2853 --
2854 Elsif pay_element_types_pkg.cobra_benefits_exist
2855 (p_element_type_id,
2856 p_validation_start_date,
2857 p_validation_end_date) Then
2858 --
2859 fnd_message.set_name('PAY','PAY_COBRA_BENS_NO_DEL');
2860 fnd_message.raise_error;
2861 --
2862 End If;
2863 End If;
2864 --
2865 If p_datetrack_delete_mode = 'DELETE' Then
2866 --
2867 If pay_element_types_pkg.links_exist
2868 (p_element_type_id,
2869 p_validation_start_date,
2870 p_validation_end_date) Then
2871 --
2872 fnd_message.set_name('PAY','PAY_6155_ELEMENT_NO_DEL_LINK');
2873 fnd_message.raise_error;
2874 --
2875 Elsif pay_element_types_pkg.run_results_exist
2876 (p_element_type_id,
2877 p_validation_start_date,
2878 p_validation_end_date) Then
2879 --
2880 fnd_message.set_name('PAY','PAY_6242_ELEMENTS_NO_DEL_RR');
2881 fnd_message.raise_error;
2882 --
2883 Elsif pay_element_types_pkg.stop_entry_rules_exist(p_element_type_id) Then
2884 --
2885 fnd_message.set_name('PAY','PAY_6157_ELEMENT_NO_DEL_FRR');
2886 fnd_message.raise_error;
2887 --
2888 End If;
2889 --
2890 End If;
2891 --
2892 If p_datetrack_delete_mode = 'ZAP' Then
2893 --
2894 If pay_element_types_pkg.element_is_in_an_element_set
2895 (p_element_type_id) Then
2896 --
2897 fnd_message.set_name('PAY','PAY_6713_ELEMENT_NO_DEL_RULE');
2898 fnd_message.raise_error;
2899 --
2900 Elsif pay_element_types_pkg.links_exist
2901 (p_element_type_id,
2902 p_validation_start_date,
2903 p_validation_end_date) Then
2904 --
2905 fnd_message.set_name('PAY','PAY_6155_ELEMENT_NO_DEL_LINK');
2906 fnd_message.raise_error;
2907 --
2908 Elsif pay_element_types_pkg.stop_entry_rules_exist(p_element_type_id) Then
2909 --
2910 fnd_message.set_name('PAY','PAY_6157_ELEMENT_NO_DEL_FRR');
2911 fnd_message.raise_error;
2912 --
2913 Elsif accrual_plan_exists(p_element_type_id) Then
2914 --
2915 fnd_message.set_name('PAY','PAY_34142_ELE_NO_DEL_ACCRUAL');
2916 fnd_message.raise_error;
2917 --
2918 Elsif pay_element_types_pkg.run_results_exist
2919 (p_element_type_id,
2920 p_validation_start_date,
2921 p_validation_end_date) Then
2922 --
2923 fnd_message.set_name('PAY','PAY_6242_ELEMENTS_NO_DEL_RR');
2924 fnd_message.raise_error;
2925 --
2926 Elsif benefit_contributions_exist
2927 (p_element_type_id,
2928 p_validation_start_date,
2929 p_validation_end_date) Then
2930 --
2931 fnd_message.set_name('PAY','PAY_34140_ELE_BEN_CONT_NO_DEL');
2932 fnd_message.raise_error;
2933 --
2934 Elsif pay_element_types_pkg.cobra_benefits_exist
2935 (p_element_type_id,
2936 p_validation_start_date,
2937 p_validation_end_date) Then
2938 --
2939 fnd_message.set_name('PAY','PAY_COBRA_BENS_NO_DEL');
2940 fnd_message.raise_error;
2941 --
2942 Elsif element_used_as_pay_basis(p_element_type_id) Then
2943 --
2944 fnd_message.set_name('PAY','PAY_6965_INPVAL_NO_DEL_SB');
2945 fnd_message.raise_error;
2946 --
2947 End If;
2948 End If;
2949 --
2950 hr_utility.set_location('Leaving:'||l_proc, 10);
2951 End;
2952 --
2953 -- ----------------------------------------------------------------------------
2954 -- |---------------------------< insert_validate >----------------------------|
2955 -- ----------------------------------------------------------------------------
2956 Procedure insert_validate
2957 (p_rec in pay_etp_shd.g_rec_type
2958 ,p_effective_date in date
2959 ,p_datetrack_mode in varchar2
2960 ,p_validation_start_date in date
2961 ,p_validation_end_date in date
2962 ,p_processing_priority_warning out nocopy boolean
2963 ) is
2964 --
2965 l_proc varchar2(72) := g_package||'insert_validate';
2966 l_processing_priority_warning boolean;
2967 --
2968 Begin
2969 hr_utility.set_location('Entering:'||l_proc, 5);
2970 --
2971 -- Call all supporting business operations
2972 --
2973 --
2974 chk_startup_action(true
2975 ,p_rec.business_group_id
2976 ,p_rec.legislation_code
2977 );
2978 IF hr_startup_data_api_support.g_startup_mode
2979 NOT IN ('GENERIC','STARTUP') THEN
2980 --
2981 -- Validate Important Attributes
2982 --
2983 hr_api.validate_bus_grp_id
2984 (p_business_group_id => p_rec.business_group_id
2985 ,p_associated_column1 => pay_etp_shd.g_tab_nam
2986 || '.BUSINESS_GROUP_ID');
2987 --
2988 -- after validating the set of important attributes,
2989 -- if Multiple Message Detection is enabled and at least
2990 -- one error has been found then abort further validation.
2991 --
2992 hr_multi_message.end_validation_set;
2993 END IF;
2994 IF hr_startup_data_api_support.g_startup_mode NOT IN ('STARTUP') THEN
2995 --
2996 -- Validate Dependent Attributes
2997 --
2998 if (p_rec.business_group_id is not null) then
2999 chk_business_group_id
3000 (p_rec.business_group_id
3001 ,p_effective_date);
3002 end if;
3003 --
3004 If (p_rec.legislation_code is not null) then
3005 chk_legislation_code
3006 (p_rec.legislation_code);
3007 end if;
3008 --
3009 chk_formula_id
3010 (p_effective_date
3011 ,p_validation_start_date
3012 ,p_validation_end_date
3013 ,p_rec.formula_id
3014 ,p_rec.process_in_run_flag
3015 ,p_rec.indirect_only_flag
3016 ,p_rec.adjustment_only_flag);
3017 --
3018 chk_currency_codes
3019 (p_effective_date
3020 ,p_rec.classification_id
3021 ,p_rec.business_group_id
3022 ,p_rec.input_currency_code
3023 ,p_rec.output_currency_code);
3024 --
3025 chk_classification_id
3026 (p_rec.classification_id
3027 ,p_rec.adjustment_only_flag
3028 ,p_rec.process_in_run_flag
3029 ,p_rec.processing_type
3030 ,p_rec.indirect_only_flag);
3031 --
3032 chk_addl_entry_allowed
3033 (p_effective_date
3034 ,p_rec.additional_entry_allowed_flag
3035 ,p_rec.processing_type);
3036 --
3037 chk_adjustment_only_flag
3038 (p_effective_date
3039 ,p_rec.adjustment_only_flag
3040 ,p_rec.closed_for_entry_flag
3041 ,p_rec.processing_type
3042 ,p_rec.indirect_only_flag);
3043 --
3044 chk_element_name
3045 (p_rec.element_name
3046 ,null
3047 ,p_rec.business_group_id
3048 ,p_rec.legislation_code
3049 );
3050 --
3051 chk_multiple_entries_allowed
3052 (p_effective_date => p_effective_date
3053 ,p_multiple_entries_allowed_flg => p_rec.multiple_entries_allowed_flag
3054 ,p_benefit_classification_id => p_rec.benefit_classification_id
3055 ,p_processing_type => p_rec.processing_type);
3056 --
3057 chk_process_in_run_flag
3058 (p_effective_date
3059 ,p_rec.process_in_run_flag
3060 ,p_rec.adjustment_only_flag
3061 ,p_rec.indirect_only_flag
3062 ,p_rec.multiply_value_flag
3063 ,p_rec.post_termination_rule
3064 ,p_rec.once_each_period_flag);
3065 --
3066 chk_processing_priority
3067 (p_processing_priority => p_rec.processing_priority
3068 ,p_classification_id => p_rec.classification_id
3069 ,p_processing_priority_warning => l_processing_priority_warning);
3070 --
3071 p_processing_priority_warning := l_processing_priority_warning;
3072 --
3073 chk_processing_type
3074 (p_effective_date
3075 ,p_rec.processing_type
3076 ,p_rec.benefit_classification_id);
3077 --
3078 chk_standard_link_flag
3079 (p_effective_date
3080 ,p_rec.standard_link_flag
3081 ,p_rec.processing_type
3082 ,p_rec.multiple_entries_allowed_flag);
3083 --
3084 chk_qualifying_factors
3085 (p_rec.qualifying_age
3086 ,p_rec.qualifying_length_of_service
3087 ,p_rec.qualifying_units
3088 ,p_effective_date);
3089 --
3090 chk_third_party_pay_only_flag
3091 (p_effective_date => p_effective_date
3092 ,p_third_party_pay_only_flag => p_rec.third_party_pay_only_flag
3093 ,p_indirect_only_flag => p_rec.indirect_only_flag);
3094 --
3095 chk_iterative_rules
3096 (p_rec.iterative_flag
3097 ,p_rec.iterative_formula_id
3098 ,p_rec.iterative_priority
3099 ,p_rec.grossup_flag
3100 ,p_rec.process_mode
3101 ,p_effective_date
3102 ,p_validation_start_date
3103 ,p_validation_end_date);
3104 --
3105 chk_proration_values
3106 (p_rec.retro_summ_ele_id
3107 ,p_rec.proration_group_id
3108 ,p_rec.proration_formula_id
3109 ,p_rec.recalc_event_group_id
3110 ,p_validation_start_date
3111 ,p_validation_end_date);
3112 --
3113 chk_time_definition(p_rec.time_definition_id
3114 ,p_rec.time_definition_type
3115 ,p_rec.business_group_id
3116 ,p_rec.legislation_code
3117 ,p_effective_date);
3118 --
3119 chk_advance_element(
3120 p_rec.element_type_id
3121 ,p_rec.advance_payable
3122 ,p_rec.advance_deduction
3123 ,p_rec.advance_element_type_id
3124 ,p_rec.deduction_element_type_id
3125 ,p_rec.business_group_id
3126 ,p_rec.legislation_code
3127 ,p_effective_date
3128 ,p_validation_start_date
3129 ,p_validation_end_date
3130 ,p_datetrack_mode
3131 );
3132 --
3133 pay_etp_bus.chk_ddf(p_rec);
3134 --
3135 pay_etp_bus.chk_df(p_rec);
3136 --
3137 END IF;
3138 hr_utility.set_location(' Leaving:'||l_proc, 10);
3139 End insert_validate;
3140 --
3141 -- ----------------------------------------------------------------------------
3142 -- |---------------------------< update_validate >----------------------------|
3143 -- ----------------------------------------------------------------------------
3144 Procedure update_validate
3145 (p_rec in pay_etp_shd.g_rec_type
3146 ,p_effective_date in date
3147 ,p_datetrack_mode in varchar2
3148 ,p_validation_start_date in date
3149 ,p_validation_end_date in date
3150 ,p_processing_priority_warning out nocopy boolean
3151 ) is
3152 --
3153 l_proc varchar2(72) := g_package||'update_validate';
3154 l_processing_priority_warning boolean;
3155 --
3156 Begin
3157 hr_utility.set_location('Entering:'||l_proc, 5);
3158 --
3159 -- Call all supporting business operations
3160 --
3161 --
3162 chk_startup_action(false
3163 ,p_rec.business_group_id
3164 ,p_rec.legislation_code
3165 );
3166 IF hr_startup_data_api_support.g_startup_mode
3167 NOT IN ('GENERIC','STARTUP') THEN
3168 --
3169 -- Validate Important Attributes
3170 --
3171 hr_api.validate_bus_grp_id
3172 (p_business_group_id => p_rec.business_group_id
3173 ,p_associated_column1 => pay_etp_shd.g_tab_nam
3174 || '.BUSINESS_GROUP_ID');
3175 --
3176 -- After validating the set of important attributes,
3177 -- if Multiple Message Detection is enabled and at least
3178 -- one error has been found then abort further validation.
3179 --
3180 hr_multi_message.end_validation_set;
3181 END IF;
3182 IF hr_startup_data_api_support.g_startup_mode NOT IN ('STARTUP') THEN
3183 --
3184 --
3185 -- Validate Dependent Attributes
3186 --
3187 chk_formula_id
3188 (p_effective_date
3189 ,p_validation_start_date
3190 ,p_validation_end_date
3191 ,p_rec.formula_id
3192 ,p_rec.process_in_run_flag
3193 ,p_rec.indirect_only_flag
3194 ,p_rec.adjustment_only_flag);
3195 --
3196 chk_classification_id
3197 (p_rec.classification_id
3198 ,p_rec.adjustment_only_flag
3199 ,p_rec.process_in_run_flag
3200 ,p_rec.processing_type
3201 ,p_rec.indirect_only_flag);
3202 --
3203 chk_addl_entry_allowed
3204 (p_effective_date
3205 ,p_rec.additional_entry_allowed_flag
3206 ,p_rec.processing_type);
3207 --
3208 chk_adjustment_only_flag
3209 (p_effective_date
3210 ,p_rec.adjustment_only_flag
3211 ,p_rec.closed_for_entry_flag
3212 ,p_rec.processing_type
3213 ,p_rec.indirect_only_flag);
3214 --
3215 chk_element_name
3216 (p_rec.element_name
3217 ,p_rec.element_type_id
3218 ,p_rec.business_group_id
3219 ,p_rec.legislation_code
3220 );
3221 --
3222 chk_multiple_entries_allowed
3223 (p_effective_date
3224 ,p_rec.element_type_id
3225 ,p_validation_start_date
3226 ,p_validation_end_date
3227 ,p_rec.multiple_entries_allowed_flag
3228 ,p_rec.benefit_classification_id
3229 ,p_rec.processing_type);
3230 --
3231 chk_process_in_run_flag
3232 (p_effective_date
3233 ,p_rec.process_in_run_flag
3234 ,p_rec.adjustment_only_flag
3235 ,p_rec.indirect_only_flag
3236 ,p_rec.multiply_value_flag
3237 ,p_rec.post_termination_rule
3238 ,p_rec.once_each_period_flag);
3239 --
3240 chk_processing_priority
3241 (p_rec.element_type_id
3242 ,p_validation_start_date
3243 ,p_validation_end_date
3244 ,p_rec.processing_priority
3245 ,p_rec.classification_id
3246 ,l_processing_priority_warning);
3247 --
3248 p_processing_priority_warning := l_processing_priority_warning;
3249 --
3250 chk_processing_type
3251 (p_effective_date
3252 ,p_rec.processing_type
3253 ,p_rec.benefit_classification_id);
3254 --
3255 chk_standard_link_flag
3256 (p_effective_date
3257 ,p_rec.standard_link_flag
3258 ,p_rec.processing_type
3259 ,p_rec.multiple_entries_allowed_flag);
3260 --
3261 chk_qualifying_factors
3262 (p_rec.qualifying_age
3263 ,p_rec.qualifying_length_of_service
3264 ,p_rec.qualifying_units
3265 ,p_effective_date);
3266 --
3267 chk_third_party_pay_only_flag
3268 (p_effective_date
3269 ,p_rec.element_type_id
3270 ,p_rec.third_party_pay_only_flag
3271 ,p_rec.indirect_only_flag);
3272 --
3273 chk_iterative_rules
3274 (p_rec.iterative_flag
3275 ,p_rec.iterative_formula_id
3276 ,p_rec.iterative_priority
3277 ,p_rec.grossup_flag
3278 ,p_rec.process_mode
3279 ,p_effective_date
3280 ,p_validation_start_date
3281 ,p_validation_end_date);
3282 --
3283 chk_proration_values
3284 (p_rec.retro_summ_ele_id
3285 ,p_rec.proration_group_id
3286 ,p_rec.proration_formula_id
3287 ,p_rec.recalc_event_group_id
3288 ,p_validation_start_date
3289 ,p_validation_end_date);
3290 --
3291 chk_time_definition(p_rec.time_definition_id
3292 ,p_rec.time_definition_type
3293 ,p_rec.business_group_id
3294 ,p_rec.legislation_code
3295 ,p_effective_date);
3296 --
3297 chk_advance_element(
3298 p_rec.element_type_id
3299 ,p_rec.advance_payable
3300 ,p_rec.advance_deduction
3301 ,p_rec.advance_element_type_id
3302 ,p_rec.deduction_element_type_id
3303 ,p_rec.business_group_id
3304 ,p_rec.legislation_code
3305 ,p_effective_date
3306 ,p_validation_start_date
3307 ,p_validation_end_date
3308 ,p_datetrack_mode
3309 );
3310 --
3311 chk_update_allowed
3312 (p_rec.element_type_id
3313 ,p_rec.indirect_only_flag
3314 ,p_rec.additional_entry_allowed_flag
3315 ,p_rec.standard_link_flag
3316 ,p_rec.adjustment_only_flag
3317 ,p_rec.post_termination_rule
3318 ,p_rec.process_in_run_flag
3319 ,p_validation_start_date
3320 ,p_validation_end_date
3321 ,p_rec.once_each_period_flag
3322 );
3323 --
3324 -- Call the datetrack update integrity operation
3325 --
3326 dt_update_validate
3327 (p_datetrack_mode => p_datetrack_mode
3328 ,p_validation_start_date => p_validation_start_date
3329 ,p_validation_end_date => p_validation_end_date
3330 );
3331 --
3332 chk_non_updateable_args
3333 (p_effective_date => p_effective_date
3334 ,p_rec => p_rec
3335 );
3336 --
3337 pay_etp_bus.chk_ddf(p_rec);
3338 --
3339 pay_etp_bus.chk_df(p_rec);
3340 --
3341 END IF;
3342 hr_utility.set_location(' Leaving:'||l_proc, 10);
3343 End update_validate;
3344 --
3345 -- ----------------------------------------------------------------------------
3346 -- |---------------------------< delete_validate >----------------------------|
3347 -- ----------------------------------------------------------------------------
3348 Procedure delete_validate
3349 (p_rec in pay_etp_shd.g_rec_type
3350 ,p_effective_date in date
3351 ,p_datetrack_mode in varchar2
3352 ,p_validation_start_date in date
3353 ,p_validation_end_date in date
3354 ) is
3355 --
3356 l_proc varchar2(72) := g_package||'delete_validate';
3357 --
3358 Begin
3359 hr_utility.set_location('Entering:'||l_proc, 5);
3360 --
3361 --
3362 chk_startup_action(false
3363 ,p_rec.business_group_id
3364 ,p_rec.legislation_code
3365 );
3366 IF hr_startup_data_api_support.g_startup_mode
3367 NOT IN ('GENERIC','STARTUP') THEN
3368 --
3369 -- Validate Important Attributes
3370 --
3371 --
3372 -- After validating the set of important attributes,
3373 -- if Multiple Message Detection is enabled and at least
3374 -- one error has been found then abort further validation.
3375 --
3376 hr_multi_message.end_validation_set;
3377 END IF;
3378 --
3379 chk_delete_allowed
3380 (p_rec.element_type_id
3381 ,p_rec.processing_priority
3382 ,p_validation_start_date
3383 ,p_validation_end_date
3384 ,p_datetrack_mode);
3385 --
3386 chk_advance_element(
3387 p_rec.element_type_id
3388 ,p_rec.advance_payable
3389 ,p_rec.advance_deduction
3390 ,p_rec.advance_element_type_id
3391 ,p_rec.deduction_element_type_id
3392 ,p_rec.business_group_id
3393 ,p_rec.legislation_code
3394 ,p_effective_date
3395 ,p_validation_start_date
3396 ,p_validation_end_date
3397 ,p_datetrack_mode
3398 );
3399
3400 -- Call all supporting business operations
3401 --
3402 dt_delete_validate
3403 (p_datetrack_mode => p_datetrack_mode
3404 ,p_validation_start_date => p_validation_start_date
3405 ,p_validation_end_date => p_validation_end_date
3406 ,p_element_type_id => p_rec.element_type_id
3407 );
3408 --
3409 hr_utility.set_location(' Leaving:'||l_proc, 10);
3410 End delete_validate;
3411 --
3412 end pay_etp_bus;