[Home] [Help]
PACKAGE BODY: APPS.PAY_ETP_BUS_ND
Source
1 Package Body pay_etp_bus_nd as
2 /* $Header: pyetpmhi.pkb 120.3.12010000.3 2008/08/06 07:12:57 ubhat ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pay_etp_bus_nd.'; -- 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_nd.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_nd.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_nd.g_element_type_id := p_element_type_id;
147 pay_etp_bus_nd.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_nd.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_nd.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_nd.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_nd.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_nd.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_nd.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_nd.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_nd.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_nd.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_nd.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_nd.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_nd.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_nd.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_nd.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_nd.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_nd.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_nd.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_nd.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_nd.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_nd.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_nd.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_nd.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 null;
287 end if;
288 --
289 hr_utility.set_location(' Leaving:'||l_proc,20);
290 end chk_ddf;
291 --
292 -- ----------------------------------------------------------------------------
293 -- |------------------------------< chk_df >----------------------------------|
294 -- ----------------------------------------------------------------------------
295 --
296 -- Description:
297 -- Validates all the Descriptive Flexfield values.
298 --
299 -- Prerequisites:
300 -- All other columns have been validated. Must be called as the
301 -- last step from insert_validate and update_validate.
302 --
303 -- In Arguments:
304 -- p_rec
305 --
306 -- Post Success:
307 -- If the Descriptive Flexfield structure column and data values are
308 -- all valid this procedure will end normally and processing will
309 -- continue.
310 --
311 -- Post Failure:
312 -- If the Descriptive Flexfield structure column value or any of
313 -- the data values are invalid then an application error is raised as
314 -- a PL/SQL exception.
315 --
316 -- Access Status:
317 -- Internal Row Handler Use Only.
318 --
319 -- ----------------------------------------------------------------------------
320 procedure chk_df
321 (p_rec in pay_etp_shd_nd.g_rec_type
322 ) is
323 --
324 l_proc varchar2(72) := g_package || 'chk_df';
325 --
326 begin
327 hr_utility.set_location('Entering:'||l_proc,10);
328 --
329 if ((p_rec.element_type_id is not null) and (
330 nvl(pay_etp_shd_nd.g_old_rec.attribute_category, hr_api.g_varchar2) <>
331 nvl(p_rec.attribute_category, hr_api.g_varchar2) or
332 nvl(pay_etp_shd_nd.g_old_rec.attribute1, hr_api.g_varchar2) <>
333 nvl(p_rec.attribute1, hr_api.g_varchar2) or
334 nvl(pay_etp_shd_nd.g_old_rec.attribute2, hr_api.g_varchar2) <>
335 nvl(p_rec.attribute2, hr_api.g_varchar2) or
336 nvl(pay_etp_shd_nd.g_old_rec.attribute3, hr_api.g_varchar2) <>
337 nvl(p_rec.attribute3, hr_api.g_varchar2) or
338 nvl(pay_etp_shd_nd.g_old_rec.attribute4, hr_api.g_varchar2) <>
339 nvl(p_rec.attribute4, hr_api.g_varchar2) or
340 nvl(pay_etp_shd_nd.g_old_rec.attribute5, hr_api.g_varchar2) <>
341 nvl(p_rec.attribute5, hr_api.g_varchar2) or
342 nvl(pay_etp_shd_nd.g_old_rec.attribute6, hr_api.g_varchar2) <>
343 nvl(p_rec.attribute6, hr_api.g_varchar2) or
344 nvl(pay_etp_shd_nd.g_old_rec.attribute7, hr_api.g_varchar2) <>
345 nvl(p_rec.attribute7, hr_api.g_varchar2) or
346 nvl(pay_etp_shd_nd.g_old_rec.attribute8, hr_api.g_varchar2) <>
347 nvl(p_rec.attribute8, hr_api.g_varchar2) or
348 nvl(pay_etp_shd_nd.g_old_rec.attribute9, hr_api.g_varchar2) <>
349 nvl(p_rec.attribute9, hr_api.g_varchar2) or
350 nvl(pay_etp_shd_nd.g_old_rec.attribute10, hr_api.g_varchar2) <>
351 nvl(p_rec.attribute10, hr_api.g_varchar2) or
352 nvl(pay_etp_shd_nd.g_old_rec.attribute11, hr_api.g_varchar2) <>
353 nvl(p_rec.attribute11, hr_api.g_varchar2) or
354 nvl(pay_etp_shd_nd.g_old_rec.attribute12, hr_api.g_varchar2) <>
355 nvl(p_rec.attribute12, hr_api.g_varchar2) or
356 nvl(pay_etp_shd_nd.g_old_rec.attribute13, hr_api.g_varchar2) <>
357 nvl(p_rec.attribute13, hr_api.g_varchar2) or
358 nvl(pay_etp_shd_nd.g_old_rec.attribute14, hr_api.g_varchar2) <>
359 nvl(p_rec.attribute14, hr_api.g_varchar2) or
360 nvl(pay_etp_shd_nd.g_old_rec.attribute15, hr_api.g_varchar2) <>
361 nvl(p_rec.attribute15, hr_api.g_varchar2) or
362 nvl(pay_etp_shd_nd.g_old_rec.attribute16, hr_api.g_varchar2) <>
363 nvl(p_rec.attribute16, hr_api.g_varchar2) or
364 nvl(pay_etp_shd_nd.g_old_rec.attribute17, hr_api.g_varchar2) <>
365 nvl(p_rec.attribute17, hr_api.g_varchar2) or
366 nvl(pay_etp_shd_nd.g_old_rec.attribute18, hr_api.g_varchar2) <>
367 nvl(p_rec.attribute18, hr_api.g_varchar2) or
368 nvl(pay_etp_shd_nd.g_old_rec.attribute19, hr_api.g_varchar2) <>
369 nvl(p_rec.attribute19, hr_api.g_varchar2) or
370 nvl(pay_etp_shd_nd.g_old_rec.attribute20, hr_api.g_varchar2) <>
371 nvl(p_rec.attribute20, hr_api.g_varchar2) ))
372 or (p_rec.element_type_id is null) then
373 --
374 -- Only execute the validation if absolutely necessary:
375 -- a) During update, the structure column value or any
376 -- of the attribute values have actually changed.
377 -- b) During insert.
378 --
379 /* hr_dflex_utility.ins_or_upd_descflex_attribs
380 (p_appl_short_name => 'PAY'
381 ,p_descflex_name => 'PAY_ELEMENT_TYPES'
382 ,p_attribute_category => p_rec.attribute_category
383 ,p_attribute1_name => 'ATTRIBUTE1'
384 ,p_attribute1_value => p_rec.attribute1
385 ,p_attribute2_name => 'ATTRIBUTE2'
386 ,p_attribute2_value => p_rec.attribute2
387 ,p_attribute3_name => 'ATTRIBUTE3'
388 ,p_attribute3_value => p_rec.attribute3
389 ,p_attribute4_name => 'ATTRIBUTE4'
390 ,p_attribute4_value => p_rec.attribute4
391 ,p_attribute5_name => 'ATTRIBUTE5'
392 ,p_attribute5_value => p_rec.attribute5
393 ,p_attribute6_name => 'ATTRIBUTE6'
394 ,p_attribute6_value => p_rec.attribute6
395 ,p_attribute7_name => 'ATTRIBUTE7'
396 ,p_attribute7_value => p_rec.attribute7
397 ,p_attribute8_name => 'ATTRIBUTE8'
398 ,p_attribute8_value => p_rec.attribute8
399 ,p_attribute9_name => 'ATTRIBUTE9'
400 ,p_attribute9_value => p_rec.attribute9
401 ,p_attribute10_name => 'ATTRIBUTE10'
402 ,p_attribute10_value => p_rec.attribute10
403 ,p_attribute11_name => 'ATTRIBUTE11'
404 ,p_attribute11_value => p_rec.attribute11
405 ,p_attribute12_name => 'ATTRIBUTE12'
406 ,p_attribute12_value => p_rec.attribute12
407 ,p_attribute13_name => 'ATTRIBUTE13'
408 ,p_attribute13_value => p_rec.attribute13
409 ,p_attribute14_name => 'ATTRIBUTE14'
410 ,p_attribute14_value => p_rec.attribute14
411 ,p_attribute15_name => 'ATTRIBUTE15'
412 ,p_attribute15_value => p_rec.attribute15
413 ,p_attribute16_name => 'ATTRIBUTE16'
414 ,p_attribute16_value => p_rec.attribute16
415 ,p_attribute17_name => 'ATTRIBUTE17'
416 ,p_attribute17_value => p_rec.attribute17
417 ,p_attribute18_name => 'ATTRIBUTE18'
418 ,p_attribute18_value => p_rec.attribute18
419 ,p_attribute19_name => 'ATTRIBUTE19'
420 ,p_attribute19_value => p_rec.attribute19
421 ,p_attribute20_name => 'ATTRIBUTE20'
422 ,p_attribute20_value => p_rec.attribute20
423 ); */
424 null;
425 end if;
426 --
427 hr_utility.set_location(' Leaving:'||l_proc,20);
428 end chk_df;
429 --
430 -- ----------------------------------------------------------------------------
431 -- |-----------------------< chk_non_updateable_args >------------------------|
432 -- ----------------------------------------------------------------------------
433 -- {Start Of Comments}
434 --
435 -- Description:
436 -- This procedure is used to ensure that non updateable attributes have
437 -- not been updated. If an attribute has been updated an error is generated.
438 --
439 -- Pre Conditions:
440 -- g_old_rec has been populated with details of the values currently in
441 -- the database.
442 --
443 -- In Arguments:
444 -- p_rec has been populated with the updated values the user would like the
445 -- record set to.
446 --
447 -- Post Success:
448 -- Processing continues if all the non updateable attributes have not
449 -- changed.
450 --
451 -- Post Failure:
452 -- An application error is raised if any of the non updatable attributes
453 -- have been altered.
454 --
455 -- {End Of Comments}
456 -- ----------------------------------------------------------------------------
457 Procedure chk_non_updateable_args
458 (p_effective_date in date
459 ,p_rec in pay_etp_shd_nd.g_rec_type
460 ) IS
461 --
462 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
463 --
464 Begin
465 --
466 -- Only proceed with the validation if a row exists for the current
467 -- record in the HR Schema.
468 --
469 IF NOT pay_etp_shd_nd.api_updating
470 (p_element_type_id => p_rec.element_type_id
471 ,p_effective_date => p_effective_date
472 ,p_object_version_number => p_rec.object_version_number
473 ) THEN
474 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
475 fnd_message.set_token('PROCEDURE ', l_proc);
476 fnd_message.set_token('STEP ', '5');
477 fnd_message.raise_error;
478 END IF;
479 --
480 End chk_non_updateable_args;
481 --
482 -- ----------------------------------------------------------------------------
483 -- |--------------------------< dt_update_validate >--------------------------|
484 -- ----------------------------------------------------------------------------
485 -- {Start Of Comments}
486 --
487 -- Description:
488 -- This procedure is used for referential integrity of datetracked
489 -- parent entities when a datetrack update operation is taking place
490 -- and where there is no cascading of update defined for this entity.
491 --
492 -- Prerequisites:
493 -- This procedure is called from the update_validate.
494 --
495 -- In Parameters:
496 --
497 -- Post Success:
498 -- Processing continues.
499 --
500 -- Post Failure:
501 --
502 -- Developer Implementation Notes:
503 -- This procedure should not need maintenance unless the HR Schema model
504 -- changes.
505 --
506 -- Access Status:
507 -- Internal Row Handler Use Only.
508 --
509 -- {End Of Comments}
510 -- ----------------------------------------------------------------------------
511 Procedure dt_update_validate
512 (p_datetrack_mode in varchar2
513 ,p_validation_start_date in date
514 ,p_validation_end_date in date
515 ) Is
516 --
517 l_proc varchar2(72) := g_package||'dt_update_validate';
518 --
519 Begin
520 --
521 -- Ensure that the p_datetrack_mode argument is not null
522 --
523 hr_api.mandatory_arg_error
524 (p_api_name => l_proc
525 ,p_argument => 'datetrack_mode'
526 ,p_argument_value => p_datetrack_mode
527 );
528 --
529 -- Mode will be valid, as this is checked at the start of the upd.
530 --
531 -- Ensure the arguments are not null
532 --
533 hr_api.mandatory_arg_error
534 (p_api_name => l_proc
535 ,p_argument => 'validation_start_date'
536 ,p_argument_value => p_validation_start_date
537 );
538 --
539 hr_api.mandatory_arg_error
540 (p_api_name => l_proc
541 ,p_argument => 'validation_end_date'
542 ,p_argument_value => p_validation_end_date
543 );
544 --
545 --
546 --
547 Exception
548 When Others Then
549 --
550 -- An unhandled or unexpected error has occurred which
551 -- we must report
552 --
553 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
554 fnd_message.set_token('PROCEDURE', l_proc);
555 fnd_message.set_token('STEP','15');
556 fnd_message.raise_error;
557 End dt_update_validate;
558 --
559 -- ----------------------------------------------------------------------------
560 -- |--------------------------< dt_delete_validate >--------------------------|
561 -- ----------------------------------------------------------------------------
562 -- {Start Of Comments}
563 --
564 -- Description:
565 -- This procedure is used for referential integrity of datetracked
566 -- child entities when either a datetrack DELETE or ZAP is in operation
567 -- and where there is no cascading of delete defined for this entity.
568 -- For the datetrack mode of DELETE or ZAP we must ensure that no
569 -- datetracked child rows exist between the validation start and end
570 -- dates.
571 --
572 -- Prerequisites:
573 -- This procedure is called from the delete_validate.
574 --
575 -- In Parameters:
576 --
577 -- Post Success:
578 -- Processing continues.
579 --
580 -- Post Failure:
581 -- If a row exists by determining the returning Boolean value from the
582 -- generic dt_api.rows_exist function then we must supply an error via
583 -- the use of the local exception handler l_rows_exist.
584 --
585 -- Developer Implementation Notes:
586 -- This procedure should not need maintenance unless the HR Schema model
587 -- changes.
588 --
589 -- Access Status:
590 -- Internal Row Handler Use Only.
591 --
592 -- {End Of Comments}
593 -- ----------------------------------------------------------------------------
594 Procedure dt_delete_validate
595 (p_element_type_id in number
596 ,p_datetrack_mode in varchar2
597 ,p_validation_start_date in date
598 ,p_validation_end_date in date
599 ) Is
600 --
601 l_proc varchar2(72) := g_package||'dt_delete_validate';
602 --
603 Begin
604 --
605 -- Ensure that the p_datetrack_mode argument is not null
606 --
607 hr_api.mandatory_arg_error
608 (p_api_name => l_proc
609 ,p_argument => 'datetrack_mode'
610 ,p_argument_value => p_datetrack_mode
611 );
612 --
613 -- Only perform the validation if the datetrack mode is either
614 -- DELETE or ZAP
615 --
616 If (p_datetrack_mode = hr_api.g_delete or
617 p_datetrack_mode = hr_api.g_zap) then
618 --
619 --
620 -- Ensure the arguments are not null
621 --
622 hr_api.mandatory_arg_error
623 (p_api_name => l_proc
624 ,p_argument => 'validation_start_date'
625 ,p_argument_value => p_validation_start_date
626 );
627 --
628 hr_api.mandatory_arg_error
629 (p_api_name => l_proc
630 ,p_argument => 'validation_end_date'
631 ,p_argument_value => p_validation_end_date
632 );
633 --
634 hr_api.mandatory_arg_error
635 (p_api_name => l_proc
636 ,p_argument => 'element_type_id'
637 ,p_argument_value => p_element_type_id
638 );
639 --
640 If (dt_api.rows_exist
641 (p_base_table_name => 'pay_input_values_f'
642 ,p_base_key_column => 'element_type_id'
643 ,p_base_key_value => p_element_type_id
644 ,p_from_date => p_validation_start_date
645 ,p_to_date => p_validation_end_date
646 )) Then
647 fnd_message.set_name('PAY','HR_7215_DT_CHILD_EXISTS');
648 fnd_message.set_token('TABLE_NAME','input values');
649 hr_multi_message.add;
650 End If;
651 If (dt_api.rows_exist
652 (p_base_table_name => 'ben_acty_base_rt_f'
653 ,p_base_key_column => 'element_type_id'
654 ,p_base_key_value => p_element_type_id
655 ,p_from_date => p_validation_start_date
656 ,p_to_date => p_validation_end_date
657 )) Then
658 fnd_message.set_name('PAY','HR_7215_DT_CHILD_EXISTS');
659 fnd_message.set_token('TABLE_NAME','acty base rt');
660 hr_multi_message.add;
661 End If;
662 If (dt_api.rows_exist
663 (p_base_table_name => 'pay_element_links_f'
664 ,p_base_key_column => 'element_type_id'
665 ,p_base_key_value => p_element_type_id
666 ,p_from_date => p_validation_start_date
667 ,p_to_date => p_validation_end_date
668 )) Then
669 fnd_message.set_name('PAY','HR_7215_DT_CHILD_EXISTS');
670 fnd_message.set_token('TABLE_NAME','element links');
671 hr_multi_message.add;
672 End If;
673 If (dt_api.rows_exist
674 (p_base_table_name => 'pay_element_type_usages_f'
675 ,p_base_key_column => 'element_type_id'
676 ,p_base_key_value => p_element_type_id
677 ,p_from_date => p_validation_start_date
678 ,p_to_date => p_validation_end_date
679 )) Then
680 fnd_message.set_name('PAY','HR_7215_DT_CHILD_EXISTS');
681 fnd_message.set_token('TABLE_NAME','element type usages');
682 hr_multi_message.add;
683 End If;
684 --
685 End If;
686 --
687 Exception
688 When Others Then
689 --
690 -- An unhandled or unexpected error has occurred which
691 -- we must report
692 --
693 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
694 fnd_message.set_token('PROCEDURE', l_proc);
695 fnd_message.set_token('STEP','15');
696 fnd_message.raise_error;
697 --
698 End dt_delete_validate;
699 --
700 -- ----------------------------------------------------------------------------
701 -- |----------------------< chk_startup_action >------------------------------|
702 -- ----------------------------------------------------------------------------
703 --
704 -- Description:
705 -- This procedure will check that the current action is allowed according
706 -- to the current startup mode.
707 --
708 -- ----------------------------------------------------------------------------
709 PROCEDURE chk_startup_action
710 (p_insert IN boolean
711 ,p_business_group_id IN number
712 ,p_legislation_code IN varchar2
713 ,p_legislation_subgroup IN varchar2 DEFAULT NULL) IS
714 --
715 BEGIN
716 --
717 -- Call the supporting procedure to check startup mode
718 --
719 IF (p_insert) THEN
720 hr_startup_data_api_support.chk_startup_action
721 (p_generic_allowed => TRUE
722 ,p_startup_allowed => TRUE
723 ,p_user_allowed => TRUE
724 ,p_business_group_id => p_business_group_id
725 ,p_legislation_code => p_legislation_code
726 ,p_legislation_subgroup => p_legislation_subgroup
727 );
728 ELSE
729 hr_startup_data_api_support.chk_upd_del_startup_action
730 (p_generic_allowed => TRUE
731 ,p_startup_allowed => TRUE
732 ,p_user_allowed => TRUE
733 ,p_business_group_id => p_business_group_id
734 ,p_legislation_code => p_legislation_code
735 ,p_legislation_subgroup => p_legislation_subgroup
736 );
737 END IF;
738 --
739 END chk_startup_action;
740 --
741 -- ----------------------------------------------------------------------------
742 -- |------------------------< chk_business_group_id >-------------------------|
743 -- ----------------------------------------------------------------------------
744 --
745 -- Description:
746 -- This procedure is used to validate the business group id against the
747 -- parent table
748 --
749 -- ----------------------------------------------------------------------------
750 Procedure chk_business_group_id
751 (p_business_group_id in number
752 ,p_effective_date in date
753 ) is
754 --
755 l_proc varchar2(72) := g_package||'chk_business_group_id';
756 l_exists varchar2(1);
757 Cursor c_chk_bg_id
758 is
759 select '1'
760 from hr_all_organization_units
761 where business_group_id = p_business_group_id;
762 -- and p_effective_date between date_from
763 -- and nvl(date_to, hr_api.g_eot);
764 --
765 Begin
766 hr_utility.set_location('Entering:'||l_proc, 5);
767 --
768 Open c_chk_bg_id;
769 Fetch c_chk_bg_id into l_exists;
770 If c_chk_bg_id%notfound Then
771 --
772 Close c_chk_bg_id;
773 fnd_message.set_name('PAY','PAY_33085_INVALID_FK');
774 fnd_message.set_token('COLUMN','BUSINESS_GROUP_ID');
775 fnd_message.set_token('TABLE','HR_ORGANIZATION_UNITS');
776 fnd_message.raise_error;
777 --
778 End If;
779 Close c_chk_bg_id;
780 --
781 hr_utility.set_location('Leaving:'||l_proc, 10);
782 End;
783 --
784 -- ----------------------------------------------------------------------------
785 -- |------------------------< chk_legislation_code >--------------------------|
786 -- ----------------------------------------------------------------------------
787 --
788 -- Description:
789 -- This procedure is used to validate the legislation code against the
790 -- parent table
791 --
792 -- ----------------------------------------------------------------------------
793 Procedure chk_legislation_code
794 (p_legislation_code in varchar2)
795 is
796 --
797 l_proc varchar2(72) := g_package||'chk_legislation_code';
798 l_exists varchar2(1);
799 Cursor c_chk_leg_code
800 is
801 select '1'
802 from fnd_territories
803 where territory_code = p_legislation_code;
804 --
805 Begin
806 hr_utility.set_location('Entering:'||l_proc, 5);
807 --
808 If p_legislation_code is not null then
809
810 Open c_chk_leg_code;
811 Fetch c_chk_leg_code into l_exists;
812 If c_chk_leg_code%notfound Then
813 --
814 Close c_chk_leg_code;
815 fnd_message.set_name('PAY','PAY_33085_INVALID_FK');
816 fnd_message.set_token('COLUMN','LEGISLATION_CODE');
817 fnd_message.set_token('TABLE','FND_TERRITORIES');
818 fnd_message.raise_error;
819 --
820 End If;
821 Close c_chk_leg_code;
822
823 End If;
824 --
825 hr_utility.set_location('Leaving:'||l_proc, 10);
826 End;
827 --
828 -- ----------------------------------------------------------------------------
829 -- |--------------------------< chk_formula_id >------------------------------|
830 -- ----------------------------------------------------------------------------
831 -- {Start Of Comments}
832 --
833 -- Description:
834 -- This procedure is used to validate the following business rules:
835 --
836 -- a) The Formula Type of the formula_id (ie. Skip Rule) must be
837 -- 'Element Skip'.
838 -- b) The formula_id must be null if process_in_run_flag is 'N' or
839 -- indirect_only_flag is 'Y' or adjustment_only_flag is 'Y'.
840 --
841 -- Prerequisites:
842 -- This procedure is called from the insert_validate and update_validate.
843 --
844 -- In Parameters:
845 -- p_effective_date
846 -- p_validation_start_date
847 -- p_validation_end_date
848 -- p_formula_id
849 -- p_process_in_run_flag
850 -- p_indirect_only_flag
851 -- p_adjustment_only_flag
852 --
853 -- Post Success:
854 -- Processing continues.
855 --
856 -- Post Failure:
857 -- If any one of the business rule validation fails then an error is raised.
858 --
859 -- Access Status:
860 -- Internal Row Handler Use Only.
861 --
862 -- {End Of Comments}
863 -- ----------------------------------------------------------------------------
864 Procedure chk_formula_id
865 (p_effective_date in date
866 ,p_validation_start_date in date
867 ,p_validation_end_date in date
868 ,p_formula_id in number
869 ,p_process_in_run_flag in varchar2
870 ,p_indirect_only_flag in varchar2
871 ,p_adjustment_only_flag in varchar2
872 ) is
873 --
874 l_proc varchar2(72) := g_package||'chk_formula_id';
875 l_exists varchar2(1);
876 Cursor c_chk_formula_type
877 is
878 select '1'
879 from ff_formulas_f fml, ff_formula_types ftp
880 where fml.formula_id = p_formula_id
881 and p_effective_date between fml.effective_start_date
882 and fml.effective_end_date
883 and ftp.formula_type_id = fml.formula_type_id
884 and upper(ftp.formula_type_name) = 'ELEMENT SKIP';
885 --
886 Begin
887 hr_utility.set_location('Entering:'||l_proc, 5);
888 --
889 If p_formula_id is not null Then
890 If not (dt_api.rows_exist
891 (p_base_table_name => 'FF_FORMULAS_F'
892 ,p_base_key_column => 'FORMULA_ID'
893 ,p_base_key_value => p_formula_id
894 ,p_from_date => p_validation_start_date
895 ,p_to_date => p_validation_end_date
896 )) Then
897 --
898 fnd_message.set_name('PAY','PAY_33085_INVALID_FK');
899 fnd_message.set_token('COLUMN','FORMULA_ID');
900 fnd_message.set_token('TABLE','FF_FORMULAS_F');
901 fnd_message.raise_error;
902 --
903 End If;
904 --
905 If (p_process_in_run_flag = 'N' or
906 p_indirect_only_flag = 'Y' or
907 p_adjustment_only_flag = 'Y') Then
908 --
909 fnd_message.set_name('PAY','HR_6951_PAY_ELE_NO_SKIP_RULE');
910 fnd_message.raise_error;
911 --
912 End If;
913 --
914 Open c_chk_formula_type;
915 Fetch c_chk_formula_type into l_exists;
916 If c_chk_formula_type%notfound Then
917 --
918 Close c_chk_formula_type;
919 fnd_message.set_name('PAY','PAY_34130_ELE_FTYPE_INVALID');
920 fnd_message.raise_error;
921 --
922 End If;
923 Close c_chk_formula_type;
924 End If;
925 --
926 hr_utility.set_location('Leaving:'||l_proc, 10);
927 End;
928 --
929 -- ----------------------------------------------------------------------------
930 -- |--------------------------< chk_currency_codes >--------------------------|
931 -- ----------------------------------------------------------------------------
932 -- {Start Of Comments}
933 --
934 -- Description:
935 -- This procedure is used to validate the following business rules:
936 --
937 -- a) If an input_currency_code is specified, then an output_currency_code
938 -- must also be specified and vice versa
939 -- b) Both input_currency_code and output_currency_code must be
940 -- present in FND_CURRENCIES (currency_code) for the current
941 -- session date.
942 -- c) For element classification 'Payments', the output currency should be
943 -- same as that defined in pay_legislation_rules.
944 --
945 -- Prerequisites:
946 -- This procedure is called from the insert_validate.
947 --
948 -- In Parameters:
949 -- p_effective_date
950 -- p_input_currency_code
951 -- p_output_currency_code
952 --
953 -- Post Success:
954 -- Processing continues.
955 --
956 -- Post Failure:
957 -- If any one of the business rule validation fails then an error is raised.
958 --
959 -- Access Status:
960 -- Internal Row Handler Use Only.
961 --
962 -- {End Of Comments}
963 -- ----------------------------------------------------------------------------
964 Procedure chk_currency_codes
965 (p_effective_date in date
966 ,p_classification_id in number
967 ,p_business_group_id in number
968 ,p_input_currency_code in varchar2
969 ,p_output_currency_code in varchar2
970 ) is
971 --
972 l_proc varchar2(72) := g_package||'chk_currency_codes';
973 l_exists varchar2(1);
974 l_non_payments_flag varchar2(30);
975 l_rule_mode pay_legislation_rules.rule_mode%type;
976
977 Cursor c_chk_currency(p_currency_code varchar2)
978 is
979 select '1'
980 from fnd_currencies
981 where currency_code = p_currency_code
982 and enabled_flag = 'Y'
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_nd.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_nd.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_nd.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 -- In parameters:
2263 -- P_TIME_DEFINITION_ID : ID of the time defintion specified.
2264 -- Post success:
2265 -- Processing continues.
2266 -- Post failure:
2267 -- Raise an error and stops the process.
2268 -- Access Status:
2269 -- Internal Row Handler Use Only.
2270 --
2271 -- {End Of Comments}
2272 -- ----------------------------------------------------------------------------
2273 Procedure chk_time_definition(p_time_definition_id number) is
2274 --
2275 l_proc varchar2(72) := g_package||'chk_time_definition';
2276 l_flag varchar2(1);
2277
2278 Cursor csr_time_definition is
2279 select 'Y'
2280 from pay_time_definitions
2281 where time_definition_id = p_time_definition_id;
2282
2283 Begin
2284 --
2285 hr_utility.set_location('Entering:'||l_proc, 5);
2286 open csr_time_definition;
2287 fetch csr_time_definition into l_flag;
2288 If (csr_time_definition%notfound) then
2289 close csr_time_definition;
2290 fnd_message.set_name('PAY','PAY_33085_INVALID_FK');
2291 fnd_message.set_token('COLUMN','TIME_DEFINTION_ID');
2292 fnd_message.set_token('TABLE','PAY_TIME_DEFINITIONS');
2293 fnd_message.raise_error;
2294 end if;
2295 close csr_time_definition;
2296 hr_utility.set_location('Leaving:'||l_proc, 10);
2297 --
2298 End chk_time_definition;
2299 --
2300 -- ----------------------------------------------------------------------------
2301 -- |----------------------< chk_time_definition_type>-------------------------|
2302 -- ----------------------------------------------------------------------------
2303 -- {Start Of Comments}
2304 --
2305 -- Description:
2306 -- This procedure is used to check whether the specified time definition
2307 -- type is valid or not.
2308 -- In parameters:
2309 -- P_TIME_DEFINITION_TYPE : time defintion type specified.
2310 -- Post success:
2311 -- Processing continues.
2312 -- Post failure:
2313 -- Raise an error and stops the process.
2314 -- Access Status:
2315 -- Internal Row Handler Use Only.
2316 --
2317 -- {End Of Comments}
2318 -- ----------------------------------------------------------------------------
2319 Procedure chk_time_definition_type(p_effective_date date
2320 ,p_time_definition_type varchar2) is
2321 --
2322 l_proc varchar2(72) := g_package||'chk_time_definition_type';
2323 --
2324 Begin
2325 --
2326 hr_utility.set_location('Entering:'||l_proc, 5);
2327 /* If hr_api.not_exists_in_hr_lookups
2328 (p_effective_date
2329 ,'PAY_TIME_DEFINITION_TYPE'
2330 ,p_time_definition_type) Then
2331 --
2332 fnd_message.set_name('PAY','HR_52966_INVALID_LOOKUP');
2333 fnd_message.set_token('COLUMN','TIME_DEFINITION_TYPE');
2334 fnd_message.set_token('LOOKUP_TYPE','PAY_TIME_DEFINITION_TYPE');
2335 fnd_message.raise_error;
2336 --
2337 End If; */
2338 hr_utility.set_location('Leaving:'||l_proc, 10);
2339 --
2340 End chk_time_definition_type;
2341 --
2342 -- ----------------------------------------------------------------------------
2343 -- |------------------------< chk_update_allowed >----------------------------|
2344 -- ----------------------------------------------------------------------------
2345 -- {Start Of Comments}
2346 --
2347 -- Description:
2348 -- This procedure is used to validate the following business rules:
2349 --
2350 -- a) Indirect_Only_Flag, Additional_Entry_Allowed_Flag, Standard_Link_Flag,
2351 -- Adjustment_Only_Flag, Post_Termination_Rule, Process_In_Run_Flag and
2352 -- Once_Each_Period_Flag can only be updated if:
2353 --
2354 -- i. There are no element links for the element.
2355 -- ii. The change will take effect for the complete lifetime of element
2356 -- type (ie. cannot perform the update if the element has been
2357 -- date-effectively updated).
2358 -- b) Adjustment_Only_Flag can only be updated if:
2359 --
2360 -- - There exists no formula result rules where the element type
2361 -- is the subject of indirect results.
2362 --
2363 -- c) Post_Termination_Rule, Process_In_Run_Flag and Once_Each_Period_Flag
2364 -- can only be updated if:
2365 --
2366 -- - There exists no run results for the element.
2367 --
2368 -- Prerequisites:
2369 -- This procedure is called from update_validate.
2370 --
2371 -- In Parameters:
2372 -- p_element_type_id
2373 -- p_Indirect_Only_Flag
2374 -- p_Additional_Entry_Allowed_Flg
2375 -- p_Standard_Link_Flag
2376 -- p_Adjustment_Only_Flag
2377 -- p_Post_Termination_Rule
2378 -- p_Process_In_Run_Flag
2379 -- p_validation_start_date
2380 -- p_validation_end_date
2381 --
2382 -- Post Success:
2383 -- Processing continues. The OUT parameter p_datetrack_update_mode will be
2384 -- set to 'CORRECTION' if rule a-ii is satisified.
2385 --
2386 -- Post Failure:
2387 -- If the business rule validation fails then an error is raised.
2388 --
2389 -- Access Status:
2390 -- Internal Row Handler Use Only.
2391 --
2392 -- {End Of Comments}
2393 -- ----------------------------------------------------------------------------
2394 Procedure chk_update_allowed
2395 (p_element_type_id in number
2396 ,p_indirect_only_flag in varchar2
2397 ,p_additional_entry_allowed_flg in varchar2
2398 ,p_standard_link_flag in varchar2
2399 ,p_adjustment_only_flag in varchar2
2400 ,p_post_termination_rule in varchar2
2401 ,p_process_in_run_flag in varchar2
2402 ,p_validation_start_date in date
2403 ,p_validation_end_date in date
2404 ,p_once_each_period_flag in varchar2
2405 ) is
2406 --
2407 l_proc varchar2(72) := g_package||'chk_update_allowed';
2408 l_element_links boolean;
2409 l_indirect_results boolean;
2410 l_run_results boolean;
2411 --
2412 Begin
2413 hr_utility.set_location('Entering:'||l_proc, 5);
2414 --
2415 If (
2416 (p_indirect_only_flag <> pay_etp_shd_nd.g_old_rec.indirect_only_flag)
2417 or
2418 (p_additional_entry_allowed_flg <>
2419 pay_etp_shd_nd.g_old_rec.additional_entry_allowed_flag)
2420 or
2421 (p_standard_link_flag <> pay_etp_shd_nd.g_old_rec.standard_link_flag)
2422 or
2423 (p_adjustment_only_flag <> pay_etp_shd_nd.g_old_rec.adjustment_only_flag)
2424 or
2425 (p_post_termination_rule <> pay_etp_shd_nd.g_old_rec.post_termination_rule)
2426 or
2427 (p_process_in_run_flag <> pay_etp_shd_nd.g_old_rec.process_in_run_flag)
2428 or
2429 (p_once_each_period_flag <> pay_etp_shd_nd.g_old_rec.once_each_period_flag)
2430 ) Then
2431 --
2432 l_element_links := pay_element_types_pkg.links_exist
2433 (p_element_type_id,
2434 p_validation_start_date,
2435 p_validation_end_date);
2436 --
2437 If l_element_links Then
2438 --
2439 fnd_message.set_name('PAY','PAY_6147_ELEMENT_LINK_UPDATE');
2440 fnd_message.raise_error;
2441 --
2442 End If;
2443 If (p_adjustment_only_flag <> pay_etp_shd_nd.g_old_rec.adjustment_only_flag
2444 )Then
2445 --
2446 l_indirect_results := pay_element_types_pkg.fed_by_indirect_results
2447 (p_element_type_id,
2448 p_validation_start_date,
2449 p_validation_end_date);
2450 --
2451 If l_indirect_results Then
2452 --
2453 fnd_message.set_name('PAY','PAY_34138_ELE_NO_ADJ_FRR_UPD');
2454 fnd_message.raise_error;
2455 --
2456 End If;
2457 --
2458 End If;
2459 If ((p_post_termination_rule <> pay_etp_shd_nd.g_old_rec.post_termination_rule)
2460 or
2461 (p_process_in_run_flag <> pay_etp_shd_nd.g_old_rec.process_in_run_flag)
2462 or
2463 (p_once_each_period_flag <> pay_etp_shd_nd.g_old_rec.once_each_period_flag)
2464 ) Then
2465 --
2466 l_run_results:= pay_element_types_pkg.run_results_exist
2467 (p_element_type_id, p_validation_start_date, p_validation_end_date);
2468 --
2469 If l_run_results Then
2470 --
2471 fnd_message.set_name('PAY','PAY_6909_ELEMENT_NO_UPD_RR');
2472 fnd_message.raise_error;
2473 --
2474 End If;
2475 --
2476 End If;
2477 --
2478 End If;
2479 --
2480 hr_utility.set_location('Leaving:'||l_proc, 10);
2481 End;
2482 --
2483 -- ----------------------------------------------------------------------------
2484 -- |------------------------< chk_delete_allowed >----------------------------|
2485 -- ----------------------------------------------------------------------------
2486 -- {Start Of Comments}
2487 --
2488 -- Description:
2489 -- This procedure is used to validate the delete business rules.
2490 --
2491 -- Prerequisites:
2492 -- This procedure is called from delete_validate.
2493 --
2494 -- In Parameters:
2495 -- p_element_type_id
2496 -- p_processing_priority
2497 -- p_validation_start_date
2498 -- p_validation_end_date
2499 -- p_datetrack_delete_mode
2500 --
2501 -- Post Success:
2502 -- Processing continues.
2503 --
2504 -- Post Failure:
2505 -- If the business rule validation fails then an error is raised.
2506 --
2507 -- Access Status:
2508 -- Internal Row Handler Use Only.
2509 --
2510 -- {End Of Comments}
2511 -- ----------------------------------------------------------------------------
2512 Procedure chk_delete_allowed
2513 (p_element_type_id in number
2514 ,p_processing_priority in number
2515 ,p_validation_start_date in date
2516 ,p_validation_end_date in date
2517 ,p_datetrack_delete_mode in varchar2
2518 ) is
2519 --
2520 l_proc varchar2(72) := g_package||'chk_delete_allowed';
2521 --
2522 -- Returns TRUE if there are accrual plans for the parameter element type
2523 --
2524 Function accrual_plan_exists
2525 (p_element_type_id number) return boolean is
2526 --
2527 l_accrual_exists boolean := FALSE;
2528 l_dummy_number number(1);
2529 --
2530 cursor csr_accrual
2531 is
2532 select null
2533 from pay_accrual_plans
2534 where accrual_plan_element_type_id = p_element_type_id;
2535 --
2536 begin
2537 --
2538 hr_utility.set_location (' ACCRUAL_PLAN_EXISTS',1);
2539 --
2540 open csr_accrual;
2541 fetch csr_accrual into l_dummy_number;
2542 l_accrual_exists := csr_accrual%found;
2543 close csr_accrual;
2544 --
2545 return l_accrual_exists;
2546 --
2547 end;
2548 --
2549 -- Returns TRUE if there are benefit contributions which refer to the
2550 -- specified element
2551 --
2552 Function benefit_contributions_exist
2553 (p_element_type_id number,
2554 p_validation_start_date date,
2555 p_validation_end_date date
2556 ) return boolean is
2557 --
2558 l_contribution_exists boolean := FALSE;
2559 l_dummy_number number(1);
2560 --
2561 Cursor csr_contribution
2562 is
2563 select 1
2564 from ben_benefit_contributions_f
2565 where element_type_id = p_element_type_id
2566 and effective_start_date <= p_validation_end_date
2567 and effective_end_date >= p_validation_start_date;
2568 --
2569 begin
2570 --
2571 hr_utility.set_location(' benefit_contributions_exist',1);
2572 --
2573 open csr_contribution;
2574 fetch csr_contribution into l_dummy_number;
2575 l_contribution_exists := csr_contribution%found;
2576 close csr_contribution;
2577 --
2578 return l_contribution_exists;
2579 --
2580 end;
2581 --
2582 -- Returns TRUE if the element has an input value which is used as a pay
2583 -- basis.
2584 --
2585 Function element_used_as_pay_basis
2586 (p_element_type_id number)
2587 return boolean is
2588 --
2589 l_pay_basis_element boolean := FALSE;
2590 l_dummy_number number(1);
2591 --
2592 cursor csr_pay_basis
2593 is
2594 select 1
2595 from per_pay_bases BASIS,
2596 pay_input_values_f IV
2597 where iv.input_value_id = basis.input_value_id
2598 and iv.element_type_id = p_element_type_id;
2599 --
2600 begin
2601 --
2602 open csr_pay_basis;
2603 fetch csr_pay_basis into l_dummy_number;
2604 l_pay_basis_element := csr_pay_basis%found;
2605 close csr_pay_basis;
2606 --
2607 return l_pay_basis_element;
2608 --
2609 end;
2610 --
2611 Begin
2612 hr_utility.set_location('Entering:'||l_proc, 5);
2613 --
2614 If p_datetrack_delete_mode = 'DELETE_NEXT_CHANGE' Then
2615 If pay_element_types_pkg.priority_result_rule_violated
2616 (p_element_type_id,
2617 p_processing_priority,
2618 p_validation_start_date,
2619 p_validation_end_date) Then
2620 --
2621 fnd_message.set_name('PAY','PAY_6149_ELEMENT_PRIORITY_UPD');
2622 fnd_message.raise_error;
2623 --
2624 Elsif pay_element_types_pkg.cobra_benefits_exist
2625 (p_element_type_id,
2626 p_validation_start_date,
2627 p_validation_end_date) Then
2628 --
2629 fnd_message.set_name('PAY','PAY_COBRA_BENS_NO_DEL');
2630 fnd_message.raise_error;
2631 --
2632 End If;
2633 End If;
2634 --
2635 If p_datetrack_delete_mode = 'DELETE' Then
2636 --
2637 If pay_element_types_pkg.links_exist
2638 (p_element_type_id,
2639 p_validation_start_date,
2640 p_validation_end_date) Then
2641 --
2642 fnd_message.set_name('PAY','PAY_6155_ELEMENT_NO_DEL_LINK');
2643 fnd_message.raise_error;
2644 --
2645 Elsif pay_element_types_pkg.run_results_exist
2646 (p_element_type_id,
2647 p_validation_start_date,
2648 p_validation_end_date) Then
2649 --
2650 fnd_message.set_name('PAY','PAY_6242_ELEMENTS_NO_DEL_RR');
2651 fnd_message.raise_error;
2652 --
2653 Elsif pay_element_types_pkg.stop_entry_rules_exist(p_element_type_id) Then
2654 --
2655 fnd_message.set_name('PAY','PAY_6157_ELEMENT_NO_DEL_FRR');
2656 fnd_message.raise_error;
2657 --
2658 End If;
2659 --
2660 End If;
2661 --
2662 If p_datetrack_delete_mode = 'ZAP' Then
2663 --
2664 If pay_element_types_pkg.element_is_in_an_element_set
2665 (p_element_type_id) Then
2666 --
2667 fnd_message.set_name('PAY','PAY_6713_ELEMENT_NO_DEL_RULE');
2668 fnd_message.raise_error;
2669 --
2670 Elsif pay_element_types_pkg.links_exist
2671 (p_element_type_id,
2672 p_validation_start_date,
2673 p_validation_end_date) Then
2674 --
2675 fnd_message.set_name('PAY','PAY_6155_ELEMENT_NO_DEL_LINK');
2676 fnd_message.raise_error;
2677 --
2678 Elsif pay_element_types_pkg.stop_entry_rules_exist(p_element_type_id) Then
2679 --
2680 fnd_message.set_name('PAY','PAY_6157_ELEMENT_NO_DEL_FRR');
2681 fnd_message.raise_error;
2682 --
2683 Elsif accrual_plan_exists(p_element_type_id) Then
2684 --
2685 fnd_message.set_name('PAY','PAY_34142_ELE_NO_DEL_ACCRUAL');
2686 fnd_message.raise_error;
2687 --
2688 Elsif pay_element_types_pkg.run_results_exist
2689 (p_element_type_id,
2690 p_validation_start_date,
2691 p_validation_end_date) Then
2692 --
2693 fnd_message.set_name('PAY','PAY_6242_ELEMENTS_NO_DEL_RR');
2694 fnd_message.raise_error;
2695 --
2696 Elsif benefit_contributions_exist
2697 (p_element_type_id,
2698 p_validation_start_date,
2699 p_validation_end_date) Then
2700 --
2701 fnd_message.set_name('PAY','PAY_34140_ELE_BEN_CONT_NO_DEL');
2702 fnd_message.raise_error;
2703 --
2704 Elsif pay_element_types_pkg.cobra_benefits_exist
2705 (p_element_type_id,
2706 p_validation_start_date,
2707 p_validation_end_date) Then
2708 --
2709 fnd_message.set_name('PAY','PAY_COBRA_BENS_NO_DEL');
2710 fnd_message.raise_error;
2711 --
2712 Elsif element_used_as_pay_basis(p_element_type_id) Then
2713 --
2714 fnd_message.set_name('PAY','PAY_6965_INPVAL_NO_DEL_SB');
2715 fnd_message.raise_error;
2716 --
2717 End If;
2718 End If;
2719 --
2720 hr_utility.set_location('Leaving:'||l_proc, 10);
2721 End;
2722 --
2723 -- ----------------------------------------------------------------------------
2724 -- |---------------------------< insert_validate >----------------------------|
2725 -- ----------------------------------------------------------------------------
2726 Procedure insert_validate
2727 (p_rec in pay_etp_shd_nd.g_rec_type
2728 ,p_effective_date in date
2729 ,p_datetrack_mode in varchar2
2730 ,p_validation_start_date in date
2731 ,p_validation_end_date in date
2732 ,p_processing_priority_warning out nocopy boolean
2733 ) is
2734 --
2735 l_proc varchar2(72) := g_package||'insert_validate';
2736 l_processing_priority_warning boolean;
2737 --
2738 Begin
2739 hr_utility.set_location('Entering:'||l_proc, 5);
2740 --
2741 -- Call all supporting business operations
2742 --
2743 --
2744 chk_startup_action(true
2745 ,p_rec.business_group_id
2746 ,p_rec.legislation_code
2747 );
2748 IF hr_startup_data_api_support.g_startup_mode
2749 NOT IN ('GENERIC','STARTUP') THEN
2750 --
2751 -- Validate Important Attributes
2752 --
2753 hr_api.validate_bus_grp_id
2754 (p_business_group_id => p_rec.business_group_id
2755 ,p_associated_column1 => pay_etp_shd_nd.g_tab_nam
2756 || '.BUSINESS_GROUP_ID');
2757 --
2758 -- after validating the set of important attributes,
2759 -- if Multiple Message Detection is enabled and at least
2760 -- one error has been found then abort further validation.
2761 --
2762 hr_multi_message.end_validation_set;
2763 END IF;
2764 --
2765 -- Validate Dependent Attributes
2766 --
2767 if (p_rec.business_group_id is not null) then
2768 chk_business_group_id
2769 (p_rec.business_group_id
2770 ,p_effective_date);
2771 end if;
2772 --
2773 If (p_rec.legislation_code is not null) then
2774 chk_legislation_code
2775 (p_rec.legislation_code);
2776 end if;
2777 --
2778 chk_formula_id
2779 (p_effective_date
2780 ,p_validation_start_date
2781 ,p_validation_end_date
2782 ,p_rec.formula_id
2783 ,p_rec.process_in_run_flag
2784 ,p_rec.indirect_only_flag
2785 ,p_rec.adjustment_only_flag);
2786 --
2787 chk_currency_codes
2788 (p_effective_date
2789 ,p_rec.classification_id
2790 ,p_rec.business_group_id
2791 ,p_rec.input_currency_code
2792 ,p_rec.output_currency_code);
2793 --
2794 chk_classification_id
2795 (p_rec.classification_id
2796 ,p_rec.adjustment_only_flag
2797 ,p_rec.process_in_run_flag
2798 ,p_rec.processing_type
2799 ,p_rec.indirect_only_flag);
2800 --
2801 chk_addl_entry_allowed
2802 (p_effective_date
2803 ,p_rec.additional_entry_allowed_flag
2804 ,p_rec.processing_type);
2805 --
2806 chk_adjustment_only_flag
2807 (p_effective_date
2808 ,p_rec.adjustment_only_flag
2809 ,p_rec.closed_for_entry_flag
2810 ,p_rec.processing_type
2811 ,p_rec.indirect_only_flag);
2812 --
2813 chk_element_name
2814 (p_rec.element_name
2815 ,null
2816 ,p_rec.business_group_id
2817 ,p_rec.legislation_code
2818 );
2819 --
2820 chk_multiple_entries_allowed
2821 (p_effective_date => p_effective_date
2822 ,p_multiple_entries_allowed_flg => p_rec.multiple_entries_allowed_flag
2823 ,p_benefit_classification_id => p_rec.benefit_classification_id
2824 ,p_processing_type => p_rec.processing_type);
2825 --
2826 chk_process_in_run_flag
2827 (p_effective_date
2828 ,p_rec.process_in_run_flag
2829 ,p_rec.adjustment_only_flag
2830 ,p_rec.indirect_only_flag
2831 ,p_rec.multiply_value_flag
2832 ,p_rec.post_termination_rule
2833 ,p_rec.once_each_period_flag);
2834 --
2835 chk_processing_priority
2836 (p_processing_priority => p_rec.processing_priority
2837 ,p_classification_id => p_rec.classification_id
2838 ,p_processing_priority_warning => l_processing_priority_warning);
2839 --
2840 p_processing_priority_warning := l_processing_priority_warning;
2841 --
2842 chk_processing_type
2843 (p_effective_date
2844 ,p_rec.processing_type
2845 ,p_rec.benefit_classification_id);
2846 --
2847 chk_standard_link_flag
2848 (p_effective_date
2849 ,p_rec.standard_link_flag
2850 ,p_rec.processing_type
2851 ,p_rec.multiple_entries_allowed_flag);
2852 --
2853 chk_qualifying_factors
2854 (p_rec.qualifying_age
2855 ,p_rec.qualifying_length_of_service
2856 ,p_rec.qualifying_units
2857 ,p_effective_date);
2858 --
2859 chk_third_party_pay_only_flag
2860 (p_effective_date => p_effective_date
2861 ,p_third_party_pay_only_flag => p_rec.third_party_pay_only_flag
2862 ,p_indirect_only_flag => p_rec.indirect_only_flag);
2863 --
2864 chk_iterative_rules
2865 (p_rec.iterative_flag
2866 ,p_rec.iterative_formula_id
2867 ,p_rec.iterative_priority
2868 ,p_rec.grossup_flag
2869 ,p_rec.process_mode
2870 ,p_effective_date
2871 ,p_validation_start_date
2872 ,p_validation_end_date);
2873 --
2874 chk_proration_values
2875 (p_rec.retro_summ_ele_id
2876 ,p_rec.proration_group_id
2877 ,p_rec.proration_formula_id
2878 ,p_rec.recalc_event_group_id
2879 ,p_validation_start_date
2880 ,p_validation_end_date);
2881 --
2882 If (p_rec.time_definition_id is not null) then
2883 chk_time_definition(p_rec.time_definition_id);
2884 end if;
2885 --
2886 If (p_rec.time_definition_type is not null) then
2887 chk_time_definition_type(p_effective_date
2888 ,p_rec.time_definition_type);
2889 end if;
2890 --
2891 pay_etp_bus_nd.chk_ddf(p_rec);
2892 --
2893 pay_etp_bus_nd.chk_df(p_rec);
2894 --
2895 hr_utility.set_location(' Leaving:'||l_proc, 10);
2896 End insert_validate;
2897 --
2898 -- ----------------------------------------------------------------------------
2899 -- |---------------------------< update_validate >----------------------------|
2900 -- ----------------------------------------------------------------------------
2901 Procedure update_validate
2902 (p_rec in pay_etp_shd_nd.g_rec_type
2903 ,p_effective_date in date
2904 ,p_datetrack_mode in varchar2
2905 ,p_validation_start_date in date
2906 ,p_validation_end_date in date
2907 ,p_processing_priority_warning out nocopy boolean
2908 ) is
2909 --
2910 l_proc varchar2(72) := g_package||'update_validate';
2911 l_processing_priority_warning boolean;
2912 --
2913 Begin
2914 hr_utility.set_location('Entering:'||l_proc, 5);
2915 --
2916 -- Call all supporting business operations
2917 --
2918 --
2919 chk_startup_action(false
2920 ,p_rec.business_group_id
2921 ,p_rec.legislation_code
2922 );
2923 IF hr_startup_data_api_support.g_startup_mode
2924 NOT IN ('GENERIC','STARTUP') THEN
2925 --
2926 -- Validate Important Attributes
2927 --
2928 hr_api.validate_bus_grp_id
2929 (p_business_group_id => p_rec.business_group_id
2930 ,p_associated_column1 => pay_etp_shd_nd.g_tab_nam
2931 || '.BUSINESS_GROUP_ID');
2932 --
2933 -- After validating the set of important attributes,
2934 -- if Multiple Message Detection is enabled and at least
2935 -- one error has been found then abort further validation.
2936 --
2937 hr_multi_message.end_validation_set;
2938 END IF;
2939 --
2940 --
2941 -- Validate Dependent Attributes
2942 --
2943 chk_formula_id
2944 (p_effective_date
2945 ,p_validation_start_date
2946 ,p_validation_end_date
2947 ,p_rec.formula_id
2948 ,p_rec.process_in_run_flag
2949 ,p_rec.indirect_only_flag
2950 ,p_rec.adjustment_only_flag);
2951 --
2952 chk_classification_id
2953 (p_rec.classification_id
2954 ,p_rec.adjustment_only_flag
2955 ,p_rec.process_in_run_flag
2956 ,p_rec.processing_type
2957 ,p_rec.indirect_only_flag);
2958 --
2959 chk_addl_entry_allowed
2960 (p_effective_date
2961 ,p_rec.additional_entry_allowed_flag
2962 ,p_rec.processing_type);
2963 --
2964 chk_adjustment_only_flag
2965 (p_effective_date
2966 ,p_rec.adjustment_only_flag
2967 ,p_rec.closed_for_entry_flag
2968 ,p_rec.processing_type
2969 ,p_rec.indirect_only_flag);
2970 --
2971 chk_element_name
2972 (p_rec.element_name
2973 ,p_rec.element_type_id
2974 ,p_rec.business_group_id
2975 ,p_rec.legislation_code
2976 );
2977 --
2978 chk_multiple_entries_allowed
2979 (p_effective_date
2980 ,p_rec.element_type_id
2981 ,p_validation_start_date
2982 ,p_validation_end_date
2983 ,p_rec.multiple_entries_allowed_flag
2984 ,p_rec.benefit_classification_id
2985 ,p_rec.processing_type);
2986 --
2987 chk_process_in_run_flag
2988 (p_effective_date
2989 ,p_rec.process_in_run_flag
2990 ,p_rec.adjustment_only_flag
2991 ,p_rec.indirect_only_flag
2992 ,p_rec.multiply_value_flag
2993 ,p_rec.post_termination_rule
2994 ,p_rec.once_each_period_flag);
2995 --
2996 chk_processing_priority
2997 (p_rec.element_type_id
2998 ,p_validation_start_date
2999 ,p_validation_end_date
3000 ,p_rec.processing_priority
3001 ,p_rec.classification_id
3002 ,l_processing_priority_warning);
3003 --
3004 p_processing_priority_warning := l_processing_priority_warning;
3005 --
3006 chk_processing_type
3007 (p_effective_date
3008 ,p_rec.processing_type
3009 ,p_rec.benefit_classification_id);
3010 --
3011 chk_standard_link_flag
3012 (p_effective_date
3013 ,p_rec.standard_link_flag
3014 ,p_rec.processing_type
3015 ,p_rec.multiple_entries_allowed_flag);
3016 --
3017 chk_qualifying_factors
3018 (p_rec.qualifying_age
3019 ,p_rec.qualifying_length_of_service
3020 ,p_rec.qualifying_units
3021 ,p_effective_date);
3022 --
3023 chk_third_party_pay_only_flag
3024 (p_effective_date
3025 ,p_rec.element_type_id
3026 ,p_rec.third_party_pay_only_flag
3027 ,p_rec.indirect_only_flag);
3028 --
3029 chk_iterative_rules
3030 (p_rec.iterative_flag
3031 ,p_rec.iterative_formula_id
3032 ,p_rec.iterative_priority
3033 ,p_rec.grossup_flag
3034 ,p_rec.process_mode
3035 ,p_effective_date
3036 ,p_validation_start_date
3037 ,p_validation_end_date);
3038 --
3039 chk_proration_values
3040 (p_rec.retro_summ_ele_id
3041 ,p_rec.proration_group_id
3042 ,p_rec.proration_formula_id
3043 ,p_rec.recalc_event_group_id
3044 ,p_validation_start_date
3045 ,p_validation_end_date);
3046 --
3047 If (p_rec.time_definition_id is not null) then
3048 chk_time_definition(p_rec.time_definition_id);
3049 end if;
3050 --
3051 If (p_rec.time_definition_type is not null) then
3052 chk_time_definition_type(p_effective_date
3053 ,p_rec.time_definition_type);
3054 end if;
3055 --
3056 chk_update_allowed
3057 (p_rec.element_type_id
3058 ,p_rec.indirect_only_flag
3059 ,p_rec.additional_entry_allowed_flag
3060 ,p_rec.standard_link_flag
3061 ,p_rec.adjustment_only_flag
3062 ,p_rec.post_termination_rule
3063 ,p_rec.process_in_run_flag
3064 ,p_validation_start_date
3065 ,p_validation_end_date
3066 ,p_rec.once_each_period_flag
3067 );
3068 --
3069 -- Call the datetrack update integrity operation
3070 --
3071 dt_update_validate
3072 (p_datetrack_mode => p_datetrack_mode
3073 ,p_validation_start_date => p_validation_start_date
3074 ,p_validation_end_date => p_validation_end_date
3075 );
3076 --
3077 chk_non_updateable_args
3078 (p_effective_date => p_effective_date
3079 ,p_rec => p_rec
3080 );
3081 --
3082 pay_etp_bus_nd.chk_ddf(p_rec);
3083 --
3084 pay_etp_bus_nd.chk_df(p_rec);
3085 --
3086 hr_utility.set_location(' Leaving:'||l_proc, 10);
3087 End update_validate;
3088 --
3089 -- ----------------------------------------------------------------------------
3090 -- |---------------------------< delete_validate >----------------------------|
3091 -- ----------------------------------------------------------------------------
3092 Procedure delete_validate
3093 (p_rec in pay_etp_shd_nd.g_rec_type
3094 ,p_effective_date in date
3095 ,p_datetrack_mode in varchar2
3096 ,p_validation_start_date in date
3097 ,p_validation_end_date in date
3098 ) is
3099 --
3100 l_proc varchar2(72) := g_package||'delete_validate';
3101 --
3102 Begin
3103 hr_utility.set_location('Entering:'||l_proc, 5);
3104 --
3105 --
3106 chk_startup_action(false
3107 ,p_rec.business_group_id
3108 ,p_rec.legislation_code
3109 );
3110 IF hr_startup_data_api_support.g_startup_mode
3111 NOT IN ('GENERIC','STARTUP') THEN
3112 --
3113 -- Validate Important Attributes
3114 --
3115 --
3116 -- After validating the set of important attributes,
3117 -- if Multiple Message Detection is enabled and at least
3118 -- one error has been found then abort further validation.
3119 --
3120 hr_multi_message.end_validation_set;
3121 END IF;
3122 --
3123 chk_delete_allowed
3124 (p_rec.element_type_id
3125 ,p_rec.processing_priority
3126 ,p_validation_start_date
3127 ,p_validation_end_date
3128 ,p_datetrack_mode);
3129 --
3130 -- Call all supporting business operations
3131 --
3132 dt_delete_validate
3133 (p_datetrack_mode => p_datetrack_mode
3134 ,p_validation_start_date => p_validation_start_date
3135 ,p_validation_end_date => p_validation_end_date
3136 ,p_element_type_id => p_rec.element_type_id
3137 );
3138 --
3139 hr_utility.set_location(' Leaving:'||l_proc, 10);
3140 End delete_validate;
3141 --
3142 end pay_etp_bus_nd;