1 Package Body pay_pel_bus as
2 /* $Header: pypelrhi.pkb 120.7.12010000.3 2008/10/03 08:41:56 ankagarw ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pay_pel_bus.'; -- Global package name
9 --
10 -- The following two global variables are only to be
11 -- used by the return_legislation_code function.
12 --
13 g_legislation_code varchar2(150) default null;
14 g_element_link_id number default null;
15 g_eot date := hr_api.g_eot;
16 --
17 -- ---------------------------------------------------------------------------
18 -- |----------------------< set_security_group_id >--------------------------|
19 -- ---------------------------------------------------------------------------
20 --
21 Procedure set_security_group_id
22 (p_element_link_id in number
23 ,p_associated_column1 in varchar2 default null
24 ) is
25 --
26 -- Declare cursor
27 --
28 cursor csr_sec_grp is
29 select pbg.security_group_id
30 from per_business_groups pbg
31 , pay_element_links_f pel
32 where pel.element_link_id = p_element_link_id
33 and pbg.business_group_id = pel.business_group_id;
34 --
35 -- Declare local variables
36 --
37 l_security_group_id per_business_groups.SECURITY_GROUP_ID%TYPE;
38 l_proc varchar2(72) := g_package||'set_security_group_id';
39 --
40 begin
41 --
42 hr_utility.set_location('Entering:'|| l_proc, 10);
43 --
44 -- Ensure that all the mandatory parameter are not null
45 --
46 hr_api.mandatory_arg_error
47 (p_api_name => l_proc
48 ,p_argument => 'element_link_id'
49 ,p_argument_value => p_element_link_id
50 );
51 --
52 open csr_sec_grp;
53 fetch csr_sec_grp into l_security_group_id;
54 --
55 if csr_sec_grp%notfound then
56 --
57 close csr_sec_grp;
58 --
59 -- The primary key is invalid therefore we must error
60 --
61 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
62 hr_multi_message.add
63 (p_associated_column1
64 => nvl(p_associated_column1,'ELEMENT_LINK_ID')
65 );
66 --
67 else
68 close csr_sec_grp;
69 --
70 -- Set the security_group_id in CLIENT_INFO
71 --
72 hr_api.set_security_group_id
73 (p_security_group_id => l_security_group_id
74 );
75 end if;
76 --
77 hr_utility.set_location(' Leaving:'|| l_proc, 20);
78 --
79 end set_security_group_id;
80 --
81 -- ---------------------------------------------------------------------------
82 -- |---------------------< return_legislation_code >-------------------------|
83 -- ---------------------------------------------------------------------------
84 --
85 Function return_legislation_code
86 (p_element_link_id in number
87 )
88 Return Varchar2 Is
89 --
90 -- Declare cursor
91 --
92 cursor csr_leg_code is
93 select pbg.legislation_code
94 from per_business_groups pbg
95 , pay_element_links_f pel
96 where pel.element_link_id = p_element_link_id
97 and pbg.business_group_id = pel.business_group_id;
98 --
99 -- Declare local variables
100 --
101 l_legislation_code per_business_groups.LEGISLATION_CODE%TYPE;
102 l_proc varchar2(72) := g_package||'return_legislation_code';
103 --
104 Begin
105 --
106 hr_utility.set_location('Entering:'|| l_proc, 10);
107 --
108 -- Ensure that all the mandatory parameter are not null
109 --
110 hr_api.mandatory_arg_error
111 (p_api_name => l_proc
112 ,p_argument => 'element_link_id'
113 ,p_argument_value => p_element_link_id
114 );
115 --
116 if ( nvl(pay_pel_bus.g_element_link_id, hr_api.g_number)
117 = p_element_link_id) then
118 --
119 -- The legislation code has already been found with a previous
120 -- call to this function. Just return the value in the global
121 -- variable.
122 --
123 l_legislation_code := pay_pel_bus.g_legislation_code;
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
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_pel_bus.g_element_link_id := p_element_link_id;
147 pay_pel_bus.g_legislation_code := l_legislation_code;
148 end if;
149 hr_utility.set_location(' Leaving:'|| l_proc, 40);
150 return l_legislation_code;
151 end return_legislation_code;
152 --
153 -- ----------------------------------------------------------------------------
154 -- |------------------------------< chk_df >----------------------------------|
155 -- ----------------------------------------------------------------------------
156 --
157 -- Description:
158 -- Validates all the 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 Descriptive Flexfield structure column and data values are
169 -- all valid this procedure will end normally and processing will
170 -- continue.
171 --
172 -- Post Failure:
173 -- If the 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_df
182 (p_rec in pay_pel_shd.g_rec_type
183 ) is
184 --
185 l_proc varchar2(72) := g_package || 'chk_df';
186 --
187 begin
188 hr_utility.set_location('Entering:'||l_proc,10);
189 --
190 if ((p_rec.element_link_id is not null) and (
191 nvl(pay_pel_shd.g_old_rec.attribute_category, hr_api.g_varchar2) <>
192 nvl(p_rec.attribute_category, hr_api.g_varchar2) or
193 nvl(pay_pel_shd.g_old_rec.attribute1, hr_api.g_varchar2) <>
194 nvl(p_rec.attribute1, hr_api.g_varchar2) or
195 nvl(pay_pel_shd.g_old_rec.attribute2, hr_api.g_varchar2) <>
196 nvl(p_rec.attribute2, hr_api.g_varchar2) or
197 nvl(pay_pel_shd.g_old_rec.attribute3, hr_api.g_varchar2) <>
198 nvl(p_rec.attribute3, hr_api.g_varchar2) or
199 nvl(pay_pel_shd.g_old_rec.attribute4, hr_api.g_varchar2) <>
200 nvl(p_rec.attribute4, hr_api.g_varchar2) or
201 nvl(pay_pel_shd.g_old_rec.attribute5, hr_api.g_varchar2) <>
202 nvl(p_rec.attribute5, hr_api.g_varchar2) or
203 nvl(pay_pel_shd.g_old_rec.attribute6, hr_api.g_varchar2) <>
204 nvl(p_rec.attribute6, hr_api.g_varchar2) or
205 nvl(pay_pel_shd.g_old_rec.attribute7, hr_api.g_varchar2) <>
206 nvl(p_rec.attribute7, hr_api.g_varchar2) or
207 nvl(pay_pel_shd.g_old_rec.attribute8, hr_api.g_varchar2) <>
208 nvl(p_rec.attribute8, hr_api.g_varchar2) or
209 nvl(pay_pel_shd.g_old_rec.attribute9, hr_api.g_varchar2) <>
210 nvl(p_rec.attribute9, hr_api.g_varchar2) or
211 nvl(pay_pel_shd.g_old_rec.attribute10, hr_api.g_varchar2) <>
212 nvl(p_rec.attribute10, hr_api.g_varchar2) or
213 nvl(pay_pel_shd.g_old_rec.attribute11, hr_api.g_varchar2) <>
214 nvl(p_rec.attribute11, hr_api.g_varchar2) or
215 nvl(pay_pel_shd.g_old_rec.attribute12, hr_api.g_varchar2) <>
216 nvl(p_rec.attribute12, hr_api.g_varchar2) or
217 nvl(pay_pel_shd.g_old_rec.attribute13, hr_api.g_varchar2) <>
218 nvl(p_rec.attribute13, hr_api.g_varchar2) or
219 nvl(pay_pel_shd.g_old_rec.attribute14, hr_api.g_varchar2) <>
220 nvl(p_rec.attribute14, hr_api.g_varchar2) or
221 nvl(pay_pel_shd.g_old_rec.attribute15, hr_api.g_varchar2) <>
222 nvl(p_rec.attribute15, hr_api.g_varchar2) or
223 nvl(pay_pel_shd.g_old_rec.attribute16, hr_api.g_varchar2) <>
224 nvl(p_rec.attribute16, hr_api.g_varchar2) or
225 nvl(pay_pel_shd.g_old_rec.attribute17, hr_api.g_varchar2) <>
226 nvl(p_rec.attribute17, hr_api.g_varchar2) or
227 nvl(pay_pel_shd.g_old_rec.attribute18, hr_api.g_varchar2) <>
228 nvl(p_rec.attribute18, hr_api.g_varchar2) or
229 nvl(pay_pel_shd.g_old_rec.attribute19, hr_api.g_varchar2) <>
230 nvl(p_rec.attribute19, hr_api.g_varchar2) or
231 nvl(pay_pel_shd.g_old_rec.attribute20, hr_api.g_varchar2) <>
232 nvl(p_rec.attribute20, hr_api.g_varchar2) ))
233 or (p_rec.element_link_id is null) then
234 --
235 -- Only execute the validation if absolutely necessary:
236 -- a) During update, the structure column value or any
237 -- of the attribute values have actually changed.
238 -- b) During insert.
239 --
240 hr_dflex_utility.ins_or_upd_descflex_attribs
241 (p_appl_short_name => 'PAY'
242 ,p_descflex_name => 'EDIT_HERE: Enter descflex name'
243 ,p_attribute_category => 'ATTRIBUTE_CATEGORY'
244 ,p_attribute1_name => 'ATTRIBUTE1'
245 ,p_attribute1_value => p_rec.attribute1
246 ,p_attribute2_name => 'ATTRIBUTE2'
247 ,p_attribute2_value => p_rec.attribute2
248 ,p_attribute3_name => 'ATTRIBUTE3'
249 ,p_attribute3_value => p_rec.attribute3
250 ,p_attribute4_name => 'ATTRIBUTE4'
251 ,p_attribute4_value => p_rec.attribute4
252 ,p_attribute5_name => 'ATTRIBUTE5'
253 ,p_attribute5_value => p_rec.attribute5
254 ,p_attribute6_name => 'ATTRIBUTE6'
255 ,p_attribute6_value => p_rec.attribute6
256 ,p_attribute7_name => 'ATTRIBUTE7'
257 ,p_attribute7_value => p_rec.attribute7
258 ,p_attribute8_name => 'ATTRIBUTE8'
259 ,p_attribute8_value => p_rec.attribute8
260 ,p_attribute9_name => 'ATTRIBUTE9'
261 ,p_attribute9_value => p_rec.attribute9
262 ,p_attribute10_name => 'ATTRIBUTE10'
263 ,p_attribute10_value => p_rec.attribute10
264 ,p_attribute11_name => 'ATTRIBUTE11'
265 ,p_attribute11_value => p_rec.attribute11
266 ,p_attribute12_name => 'ATTRIBUTE12'
267 ,p_attribute12_value => p_rec.attribute12
268 ,p_attribute13_name => 'ATTRIBUTE13'
269 ,p_attribute13_value => p_rec.attribute13
270 ,p_attribute14_name => 'ATTRIBUTE14'
271 ,p_attribute14_value => p_rec.attribute14
272 ,p_attribute15_name => 'ATTRIBUTE15'
273 ,p_attribute15_value => p_rec.attribute15
274 ,p_attribute16_name => 'ATTRIBUTE16'
275 ,p_attribute16_value => p_rec.attribute16
276 ,p_attribute17_name => 'ATTRIBUTE17'
277 ,p_attribute17_value => p_rec.attribute17
278 ,p_attribute18_name => 'ATTRIBUTE18'
279 ,p_attribute18_value => p_rec.attribute18
280 ,p_attribute19_name => 'ATTRIBUTE19'
281 ,p_attribute19_value => p_rec.attribute19
282 ,p_attribute20_name => 'ATTRIBUTE20'
283 ,p_attribute20_value => p_rec.attribute20
284 );
285 end if;
286 --
287 hr_utility.set_location(' Leaving:'||l_proc,20);
288 end chk_df;
289 --
290 -- ----------------------------------------------------------------------------
291 -- |-----------------------< chk_non_updateable_args >------------------------|
292 -- ----------------------------------------------------------------------------
293 -- {Start Of Comments}
294 --
295 -- Description:
296 -- This procedure is used to ensure that non updateable attributes have
297 -- not been updated. If an attribute has been updated an error is generated.
298 --
299 -- Pre Conditions:
300 -- g_old_rec has been populated with details of the values currently in
301 -- the database.
302 --
303 -- In Arguments:
304 -- p_rec has been populated with the updated values the user would like the
305 -- record set to.
306 --
307 -- Post Success:
308 -- Processing continues if all the non updateable attributes have not
309 -- changed.
310 --
311 -- Post Failure:
312 -- An application error is raised if any of the non updatable attributes
313 -- have been altered.
314 --
315 -- {End Of Comments}
316 -- ----------------------------------------------------------------------------
317 Procedure chk_non_updateable_args
318 (p_effective_date in date
319 ,p_rec in pay_pel_shd.g_rec_type
320 ) IS
321 --
322 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
323 --
324 Begin
325 --
326 -- Only proceed with the validation if a row exists for the current
327 -- record in the HR Schema.
328 --
329 IF NOT pay_pel_shd.api_updating
330 (p_element_link_id => p_rec.element_link_id
331 ,p_effective_date => p_effective_date
332 ,p_object_version_number => p_rec.object_version_number
333 ) THEN
334 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
335 fnd_message.set_token('PROCEDURE ', l_proc);
336 fnd_message.set_token('STEP ', '5');
337 fnd_message.raise_error;
338 END IF;
339 End chk_non_updateable_args;
340 --
341 -- ----------------------------------------------------------------------------
342 -- |--------------------------< dt_update_validate >--------------------------|
343 -- ----------------------------------------------------------------------------
344 -- {Start Of Comments}
345 --
346 -- Description:
347 -- This procedure is used for referential integrity of datetracked
348 -- parent entities when a datetrack update operation is taking place
349 -- and where there is no cascading of update defined for this entity.
350 --
351 -- Prerequisites:
352 -- This procedure is called from the update_validate.
353 --
354 -- In Parameters:
355 --
356 -- Post Success:
357 -- Processing continues.
358 --
359 -- Post Failure:
360 --
361 -- Developer Implementation Notes:
362 -- This procedure should not need maintenance unless the HR Schema model
363 -- changes.
364 --
365 -- Access Status:
366 -- Internal Row Handler Use Only.
367 --
368 -- {End Of Comments}
369 -- ----------------------------------------------------------------------------
370 Procedure dt_update_validate
371 (p_payroll_id in number default hr_api.g_number
372 ,p_element_type_id in number default hr_api.g_number
373 ,p_datetrack_mode in varchar2
374 ,p_validation_start_date in date
375 ,p_validation_end_date in date
376 ) Is
377 --
378 l_proc varchar2(72) := g_package||'dt_update_validate';
379 --
380 Begin
381 --
382 -- Ensure that the p_datetrack_mode argument is not null
383 --
384 hr_api.mandatory_arg_error
385 (p_api_name => l_proc
386 ,p_argument => 'datetrack_mode'
387 ,p_argument_value => p_datetrack_mode
388 );
389 --
390 -- Mode will be valid, as this is checked at the start of the upd.
391 --
392 -- Ensure the arguments are not null
393 --
394 hr_api.mandatory_arg_error
395 (p_api_name => l_proc
396 ,p_argument => 'validation_start_date'
397 ,p_argument_value => p_validation_start_date
398 );
399 --
400 hr_api.mandatory_arg_error
401 (p_api_name => l_proc
402 ,p_argument => 'validation_end_date'
403 ,p_argument_value => p_validation_end_date
404 );
405 --
406 If ((nvl(p_payroll_id, hr_api.g_number) <> hr_api.g_number) and
407 NOT (dt_api.check_min_max_dates
408 (p_base_table_name => 'pay_all_payrolls_f'
409 ,p_base_key_column => 'PAYROLL_ID'
410 ,p_base_key_value => p_payroll_id
411 ,p_from_date => p_validation_start_date
412 ,p_to_date => p_validation_end_date))) Then
413 fnd_message.set_name('PAY', 'HR_7216_DT_UPD_INTEGRITY_ERR');
414 fnd_message.set_token('TABLE_NAME','all payrolls');
415 hr_multi_message.add
416 (p_associated_column1 => pay_pel_shd.g_tab_nam || '.PAYROLL_ID');
417 End If;
418 If ((nvl(p_element_type_id, hr_api.g_number) <> hr_api.g_number) and
419 NOT (dt_api.check_min_max_dates
420 (p_base_table_name => 'pay_element_types_f'
421 ,p_base_key_column => 'ELEMENT_TYPE_ID'
422 ,p_base_key_value => p_element_type_id
423 ,p_from_date => p_validation_start_date
424 ,p_to_date => p_validation_end_date))) Then
425 fnd_message.set_name('PAY', 'HR_7216_DT_UPD_INTEGRITY_ERR');
426 fnd_message.set_token('TABLE_NAME','element types');
427 hr_multi_message.add
428 (p_associated_column1 => pay_pel_shd.g_tab_nam || '.ELEMENT_TYPE_ID');
429 End If;
430 --
431 Exception
432 When Others Then
433 --
434 -- An unhandled or unexpected error has occurred which
435 -- we must report
436 --
437 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
438 fnd_message.set_token('PROCEDURE', l_proc);
439 fnd_message.set_token('STEP','15');
440 fnd_message.raise_error;
441 End dt_update_validate;
442 --
443 -- ----------------------------------------------------------------------------
444 -- |--------------------------< dt_delete_validate >--------------------------|
445 -- ----------------------------------------------------------------------------
446 -- {Start Of Comments}
447 --
448 -- Description:
449 -- This procedure is used for referential integrity of datetracked
450 -- child entities when either a datetrack DELETE or ZAP is in operation
451 -- and where there is no cascading of delete defined for this entity.
452 -- For the datetrack mode of DELETE or ZAP we must ensure that no
453 -- datetracked child rows exist between the validation start and end
454 -- dates.
455 --
456 -- Prerequisites:
457 -- This procedure is called from the delete_validate.
458 --
459 -- In Parameters:
460 --
461 -- Post Success:
462 -- Processing continues.
463 --
464 -- Post Failure:
465 -- If a row exists by determining the returning Boolean value from the
466 -- generic dt_api.rows_exist function then we must supply an error via
467 -- the use of the local exception handler l_rows_exist.
468 --
469 -- Developer Implementation Notes:
470 -- This procedure should not need maintenance unless the HR Schema model
471 -- changes.
472 --
473 -- Access Status:
474 -- Internal Row Handler Use Only.
475 --
476 -- {End Of Comments}
477 -- ----------------------------------------------------------------------------
478 Procedure dt_delete_validate
479 (p_element_link_id in number
480 ,p_datetrack_mode in varchar2
481 ,p_validation_start_date in date
482 ,p_validation_end_date in date
483 ) Is
484 --
485 l_proc varchar2(72) := g_package||'dt_delete_validate';
486 --
487 Begin
488 --
489 -- Ensure that the p_datetrack_mode argument is not null
490 --
491 hr_api.mandatory_arg_error
492 (p_api_name => l_proc
493 ,p_argument => 'datetrack_mode'
494 ,p_argument_value => p_datetrack_mode
495 );
496 --
497 -- Only perform the validation if the datetrack mode is either
498 -- DELETE or ZAP
499 --
500 If (p_datetrack_mode = hr_api.g_delete or
501 p_datetrack_mode = hr_api.g_zap) then
502 --
503 --
504 -- Ensure the arguments are not null
505 --
506 hr_api.mandatory_arg_error
507 (p_api_name => l_proc
508 ,p_argument => 'validation_start_date'
509 ,p_argument_value => p_validation_start_date
510 );
511 --
512 hr_api.mandatory_arg_error
513 (p_api_name => l_proc
514 ,p_argument => 'validation_end_date'
515 ,p_argument_value => p_validation_end_date
516 );
517 --
518 hr_api.mandatory_arg_error
519 (p_api_name => l_proc
520 ,p_argument => 'element_link_id'
521 ,p_argument_value => p_element_link_id
522 );
523 --
524 If (dt_api.rows_exist
525 (p_base_table_name => 'pay_link_input_values_f'
526 ,p_base_key_column => 'element_link_id'
527 ,p_base_key_value => p_element_link_id
528 ,p_from_date => p_validation_start_date
529 ,p_to_date => p_validation_end_date
530 )) Then
531 fnd_message.set_name('PAY','HR_7215_DT_CHILD_EXISTS');
532 fnd_message.set_token('TABLE_NAME','link input values');
533 hr_multi_message.add;
534 End If;
535 If (dt_api.rows_exist
536 (p_base_table_name => 'pay_element_entries_f'
537 ,p_base_key_column => 'element_link_id'
538 ,p_base_key_value => p_element_link_id
539 ,p_from_date => p_validation_start_date
540 ,p_to_date => p_validation_end_date
541 )) Then
542 fnd_message.set_name('PAY','HR_7215_DT_CHILD_EXISTS');
543 fnd_message.set_token('TABLE_NAME','element entries');
544 hr_multi_message.add;
545 End If;
546 --
547 End If;
548 --
549 Exception
550 When Others Then
551 --
552 -- An unhandled or unexpected error has occurred which
553 -- we must report
554 --
555 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
556 fnd_message.set_token('PROCEDURE', l_proc);
557 fnd_message.set_token('STEP','15');
558 fnd_message.raise_error;
559 --
560 End dt_delete_validate;
561
562 --
563 -- ---------------------------------------------------------------------------
564 -- |---------------------< chk_asg_link_usages >-------------------------|
565 -- ---------------------------------------------------------------------------
566
567 Procedure chk_asg_link_usages
568 (p_business_group_id in number,
569 p_people_group_id in number,
570 p_element_link_id in number,
571 p_effective_start_date in date,
572 p_effective_end_date in date)
573 is
574 --
575 l_proc varchar2(72) := g_package || 'chk_asg_link_usages';
576 --
577 begin
578 hr_utility.set_location('Entering:'||l_proc,10);
579 --
580 pay_asg_link_usages_pkg.insert_alu
581 (p_business_group_id,
582 p_people_group_id,
583 p_element_link_id,
584 p_effective_start_date,
585 p_effective_end_date);
586 --
587 hr_utility.set_location(' Leaving:'||l_proc,20);
588 end chk_asg_link_usages;
589
590 --
591 -- ---------------------------------------------------------------------------
592 -- |---------------------------< chk_defaults >------------------------------|
593 -- ---------------------------------------------------------------------------
594
595 Procedure chk_defaults
596 ( p_element_type_id in number
597 ,p_qualifying_age in out nocopy varchar2
598 ,p_qualifying_length_of_service in out nocopy varchar2
599 ,p_qualifying_units in out nocopy varchar2
600 ,p_multiply_value_flag in out nocopy varchar2
601 ,p_standard_link_flag in out nocopy varchar2
602 ,p_effective_date in date
603 ) is
604 --
605 l_proc varchar2(72) := g_package || 'chk_defaults';
606
607 cursor csr_Defaults is
608 select qualifying_age,qualifying_length_of_service,qualifying_units,
609 standard_link_flag,multiply_value_flag
610 from pay_element_types_f
611 where element_type_id = p_element_type_id;
612 --
613 begin
614 hr_utility.set_location('Entering:'||l_proc,10);
615 --
616 if p_qualifying_age is not null and
617 (p_qualifying_age < 0 or length(p_qualifying_age) > 2) then
618 fnd_message.set_name('PAY', 'PAY_33096_QUALI_AGE_CHECK');
619 fnd_message.raise_error;
620 end if;
621
622 begin
623 if p_qualifying_length_of_service is not null
624 and (p_qualifying_length_of_service < 0
625 or to_number(p_qualifying_length_of_service,'9999.99')
626 <> to_number(p_qualifying_length_of_service)) then
627 fnd_message.set_name('PAY', 'PAY_33097_QUALI_LOS_CHECK');
628 fnd_message.raise_error;
629 end if;
630 exception
631 when others then
632 fnd_message.set_name('PAY', 'PAY_33097_QUALI_LOS_CHECK');
633 fnd_message.raise_error;
634 end;
635
636 if p_qualifying_units is not null then
637 if hr_api.not_exists_in_hr_lookups
638 (p_effective_date
639 ,'QUALIFYING_UNITS'
640 ,p_qualifying_units) then
641 --
642 fnd_message.set_name('PAY', 'PAY_33098_QUALI_UNIT_CHECK');
643 fnd_message.raise_error;
644 --
645 end if;
646 end if;
647
648
649 for rec in csr_Defaults
650 loop
651 p_qualifying_age := nvl(p_qualifying_age,rec.qualifying_age);
652 p_qualifying_length_of_service :=
653 nvl(p_qualifying_length_of_service,rec.qualifying_length_of_service);
654 p_qualifying_units := nvl(p_qualifying_units,rec.qualifying_units);
655 p_multiply_value_flag :=
656 nvl(p_multiply_value_flag,rec.multiply_value_flag);
657 p_standard_link_flag := nvl(p_standard_link_flag,rec.standard_link_flag);
658 end loop;
659
660 --
661 hr_utility.set_location(' Leaving:'||l_proc,20);
662 end chk_defaults;
663
664 --
665 -- ---------------------------------------------------------------------------
666 -- |-------------------------<chk_link_input_values>-------------------------|
667 -- ---------------------------------------------------------------------------
668
669 Procedure chk_link_input_values
670 (p_element_type_id in number,
671 p_element_link_id in number,
672 p_effective_date in date
673 ) is
674 --
675 l_proc varchar2(72) := g_package || 'chk_link_input_values';
676 cursor csr_InputValues is
677 select effective_start_date,
678 effective_end_date,
679 input_value_id,
680 default_value,max_value,
681 min_value,warning_or_error
682 from pay_input_values_f
683 where element_type_id = p_element_type_id
684 and p_effective_date between effective_start_date and effective_end_date;
685
686 l_link_input_value_id number;
687 l_effective_start_date date;
688 l_effective_end_date date;
689 l_object_version_number number;
690 l_pay_basis_warning boolean;
691 --
692 begin
693 hr_utility.set_location('Entering:'||l_proc,10);
694 --
695 for rec in csr_InputValues
696 loop
697 pay_link_input_values_api.create_liv_internal
698 (p_effective_date => p_effective_date
699 ,p_element_link_id => p_element_link_id
700 ,p_input_value_id => rec.input_value_id
701 ,p_costed_flag => 'N'
702 ,p_default_value => rec.default_value
703 ,p_max_value => rec.max_value
704 ,p_min_value => rec.min_value
705 ,p_warning_or_error => rec.warning_or_error
706 ,p_link_input_value_id => l_link_input_value_id
707 ,p_effective_start_date => l_effective_start_date
708 ,p_effective_end_date => l_effective_end_date
709 ,p_object_version_number => l_object_version_number
710 ,p_pay_basis_warning => l_pay_basis_warning
711 );
712 end loop;
713 --
714 hr_utility.set_location(' Leaving:'||l_proc,20);
715 end chk_link_input_values;
716
717 --
718 -- ---------------------------------------------------------------------------
719 -- |---------------------------<chk_end_date>--------------------------------|
720 -- ---------------------------------------------------------------------------
721 Procedure chk_end_date
722 (p_element_type_id in number,
723 p_element_link_id in number,
724 p_effective_start_date in date,
725 p_effective_end_date in out nocopy date,
726 p_organization_id in number,
727 p_people_group_id in number,
728 p_job_id in number,
729 p_position_id in number,
730 p_grade_id in number,
731 p_location_id in number,
732 p_link_to_all_payrolls_flag in varchar2,
733 p_payroll_id in number,
734 p_employment_category in varchar2,
735 p_pay_basis_id in number,
736 p_business_group_id in number
737 ) is
738 --
739 l_proc varchar2(72) := g_package || 'chk_end_date';
740 --
741 begin
742 hr_utility.set_location('Entering:'||l_proc,10);
743 --
744 p_effective_end_date := pay_element_links_pkg.max_end_date
745 (p_element_type_id,
746 p_element_link_id,
747 p_effective_start_date,
748 p_effective_end_date,
749 p_organization_id,
750 p_people_group_id,
751 p_job_id,
752 p_position_id,
753 p_grade_id,
754 p_location_id,
755 p_link_to_all_payrolls_flag,
756 p_payroll_id,
757 p_employment_category,
758 p_pay_basis_id,
759 p_business_group_id);
760 --
761 hr_utility.set_location(' Leaving:'||l_proc,20);
762 end chk_end_date;
763
764 --
765 -- ---------------------------------------------------------------------------
766 -- |---------------------<chk_standard_entries>-------------------------|
767 -- ---------------------------------------------------------------------------
768 --
769 procedure chk_standard_entries
770 ( p_business_group_id in number
771 ,p_element_link_id in number
772 ,p_element_type_id in number
773 ,p_effective_start_date in date
774 ,p_effective_end_date in date
775 ,p_payroll_id in number
776 ,p_link_to_all_payrolls_flag in varchar2
777 ,p_job_id in number
778 ,p_grade_id in number
779 ,p_position_id in number
780 ,p_organization_id in number
781 ,p_location_id in number
782 ,p_pay_basis_id in number
783 ,p_employment_category in varchar2
784 ,p_people_group_id in number
785 ) is
786 --
787 l_proc varchar2(72) := g_package || 'chk_standard_entries';
788 --
789 begin
790 hr_utility.set_location('Entering:'||l_proc,10);
791 --
792 hrentmnt.maintain_entries_el
793 (p_business_group_id,
794 p_element_link_id,
795 p_element_type_id,
796 p_effective_start_date,
797 p_effective_end_date,
798 p_payroll_id,
799 p_link_to_all_payrolls_flag,
800 p_job_id,
801 p_grade_id,
802 p_position_id,
803 p_organization_id,
804 p_location_id,
805 p_pay_basis_id,
806 p_employment_category,
807 p_people_group_id);
808 --
809 hr_utility.set_location(' Leaving:'||l_proc,20);
810 end chk_standard_entries;
811
812 --
813 -- ---------------------------------------------------------------------------
814 -- |--------------------------<chk_ben_contri>-------------------------------|
815 -- ---------------------------------------------------------------------------
816 -- {Start Of Comments}
817 --
818 -- Description:
819 -- This procedure is used to ensure that a link cannot be created
820 -- if there are benefit contributions set up for the element as
821 -- of effective date
822 --
823 -- In Arguments:
824 -- p_element_type_id
825 -- p_effective_start_date
826 --
827 -- Post Success:
828 -- No Error is raised
829 --
830 -- Post Failure:
831 -- Errors are raised
832 --
833 -- {End Of Comments}
834 -- ----------------------------------------------------------------------------
835
836 procedure chk_ben_contri
837 (p_element_type_id in number,
838 p_effective_start_date in date
839 ) is
840 --
841 cursor csr_ben_contri_used is
842 select '1'
843 from ben_benefit_classifications bbc, pay_element_types_f pet
844 where bbc.benefit_classification_id(+) = pet.benefit_classification_id
845 and element_type_id = p_element_type_id
846 and p_effective_start_date between pet.effective_start_date
847 and pet.effective_end_date
848 and nvl(contributions_used,'N') = 'Y';
849
850 cursor csr_BenContri is
851 select '1'
852 from ben_benefit_contributions_f
853 where element_type_id = p_element_type_id
854 and p_effective_start_date between effective_start_date
855 and effective_end_date;
856
857 l_proc varchar2(72) := g_package || 'chk_ben_contri';
858 l_exists varchar2(1);
859 --
860 begin
861 hr_utility.set_location('Entering:'||l_proc,10);
862 --
863 Open csr_ben_contri_used;
864 Fetch csr_ben_contri_used into l_exists;
865 If csr_ben_contri_used%found then
866 --
867 Open csr_BenContri;
868 Fetch csr_BenContri into l_exists;
869 If csr_BenContri%notfound then
870 Close csr_ben_contri_used;
871 Close csr_BenContri;
872 fnd_message.set_name('PAY', 'PAY_33086_LINK_NO_EFF_CONTRI');
873 fnd_message.raise_error;
874 End If;
875 Close csr_BenContri;
876 --
877 End If;
878 Close csr_ben_contri_used;
879 --
880 hr_utility.set_location(' Leaving:'||l_proc,20);
881 end chk_ben_contri;
882
883 -- ---------------------------------------------------------------------------
884 -- |--------------------------<chk_emp_cat>----------------------------------|
885 -- ---------------------------------------------------------------------------
886 -- {Start Of Comments}
887 --
888 -- Description:
889 -- This procedure is used to ensure that the entered employment category is
890 -- the valid one
891 --
892 -- In Arguments:
893 -- p_employment_category
894 -- p_effective_date
895 --
896 -- Post Success:
897 -- No Error is raised
898 --
899 -- Post Failure:
900 -- Errors are raised
901 --
902 -- {End Of Comments}
903 -- ----------------------------------------------------------------------------
904 procedure chk_emp_cat
905 (p_employment_category in varchar2,
906 p_effective_date in date
907 ) is
908 --
909 l_proc varchar2(72) := g_package || 'chk_emp_cat';
910 l_dummy varchar(1);
911 --
912 begin
913 hr_utility.set_location('Entering:'||l_proc,10);
914 --
915 If p_employment_category is not null Then
916 If hr_api.not_exists_in_hr_lookups
917 (p_effective_date
918 ,'EMP_CAT'
919 ,p_employment_category) Then
920 --
921 fnd_message.set_name('PAY','HR_52966_INVALID_LOOKUP');
922 fnd_message.set_token('COLUMN','EMPLOYMENT_CATEGORY');
923 fnd_message.set_token('LOOKUP_TYPE','EMP_CAT');
924 fnd_message.raise_error;
925 --
926 End If;
927 End If;
928 --
929 hr_utility.set_location(' Leaving:'||l_proc,20);
930 end chk_emp_cat;
931 --
932 -- ---------------------------------------------------------------------------
933 -- |--------------------------<chk_org_unit>---------------------------------|
934 -- ---------------------------------------------------------------------------
935 --
936 procedure chk_org_unit
937 (p_business_group_id in number,
938 p_organization_id in number,
939 p_effective_date in date
940 -- Bug 6010954. removed the p_location_id parameter.
941 -- p_location_id in out nocopy number
942 ) is
943 --
944 l_proc varchar2(72) := g_package || 'chk_org_unit';
945 l_dummy hr_all_organization_units.location_id%TYPE;
946
947 cursor csr_OrgUnit is
948 select location_id
949 from hr_all_organization_units
950 where business_group_id = p_business_group_id
951 and organization_id = p_organization_id
952 and p_effective_date
953 between date_from and nvl(date_to, g_eot) ;
954 --
955 begin
956 hr_utility.set_location('Entering:'||l_proc,10);
957 --
958 open csr_OrgUnit;
959 fetch csr_OrgUnit into l_dummy;
960 if csr_OrgUnit%notfound then
961 close csr_OrgUnit;
962 fnd_message.set_name('PAY', 'PAY_33087_LINK_ORG_INVALID');
963 fnd_message.raise_error;
964 end if;
965 close csr_OrgUnit;
966
967 --p_location_id := nvl(p_location_id,l_dummy);
968 --
969 hr_utility.set_location(' Leaving:'||l_proc,20);
970
971 end chk_org_unit;
972
973 -- ---------------------------------------------------------------------------
974 -- |-----------------------------<chk_position>------------------------------|
975 -- ---------------------------------------------------------------------------
976 -- {Start Of Comments}
977 --
978 -- Description:
979 -- This procedure is used to ensure that a position is the one valid
980 -- for the business group
981 --
982 -- In Arguments:
983 -- p_business_group_id
984 -- p_position_id
985 -- p_organization_id
986 -- p_job_id
987 -- p_effective_date
988 --
989 -- Post Success:
990 -- No Error is raised
991 --
992 -- Post Failure:
993 -- Errors are raised
994 --
995 -- {End Of Comments}
996 -- ----------------------------------------------------------------------------
997
998 procedure chk_position
999 (p_business_group_id in number,
1000 p_position_id in number,
1001 p_organization_id in number,
1002 p_job_id in number,
1003 p_effective_date in date
1004 ) is
1005 --
1006 l_proc varchar2(72) := g_package || 'chk_position';
1007 l_dummy varchar2(1);
1008
1009 cursor csr_position is
1010 select 'X'
1011 from per_positions pos, per_jobs job, hr_organization_units org
1012 where pos.position_id = p_position_id
1013 and pos.business_group_id +0 = p_business_group_id
1014 and job.job_id = pos.job_id and org.organization_id = pos.organization_id
1015 and p_effective_date between pos.date_effective
1016 and nvl(pos.date_end, g_eot)
1017 and (p_organization_id is null or p_organization_id =pos.organization_id)
1018 and (p_job_id is null or p_job_id = pos.job_id);
1019 --
1020 begin
1021 hr_utility.set_location('Entering:'||l_proc,10);
1022 --
1023 open csr_position;
1024 fetch csr_position into l_dummy;
1025 if csr_position%notfound then
1026 close csr_position;
1027 fnd_message.set_name('PAY', 'PAY_33094_LINK_POS_INVALID');
1028 fnd_message.raise_error;
1029 end if;
1030 close csr_position;
1031 --
1032 hr_utility.set_location(' Leaving:'||l_proc,20);
1033
1034 end chk_position;
1035
1036 -- ---------------------------------------------------------------------------
1037 -- |-----------------------------<chk_job_id>--------------------------------|
1038 -- ---------------------------------------------------------------------------
1039 -- {Start Of Comments}
1040 --
1041 -- Description:
1042 -- This procedure is used to ensure that a job is the one valid
1043 --
1044 --
1045 -- In Arguments:
1046 -- p_business_group_id
1047 -- p_job_id
1048 -- p_grade_id
1049 -- p_effective_date
1050 --
1051 -- Post Success:
1052 -- No Error is raised
1053 --
1054 -- Post Failure:
1055 -- Errors are raised
1056 --
1057 -- {End Of Comments}
1058 -- ----------------------------------------------------------------------------
1059
1060 procedure chk_job_id
1061 (p_business_group_id in number,
1062 p_job_id in number,
1063 p_effective_date in date
1064 ) is
1065 --
1066 l_proc varchar2(72) := g_package || 'chk_job_id';
1067 l_dummy varchar2(1);
1068
1069 cursor csr_jobs
1070 is
1071 select 'X'
1072 from per_jobs_v job
1073 where job.business_group_id +0 = p_business_group_id
1074 and p_effective_date between job.date_from and nvl(job.date_to, g_eot)
1075 and job.job_id = p_job_id;
1076 --
1077 begin
1078 hr_utility.set_location('Entering:'||l_proc,10);
1079 --
1080 open csr_jobs;
1081 fetch csr_jobs into l_dummy;
1082 if csr_jobs%notfound then
1083 close csr_jobs;
1084 fnd_message.set_name('PAY', 'PAY_33452_FK_DATA_INACTIVE');
1085 fnd_message.set_token('COLUMN','Job Id');
1086 fnd_message.set_token('TABLE','per_jobs');
1087 fnd_message.set_token('PROCEDURE',l_proc);
1088 fnd_message.raise_error;
1089 end if;
1090 close csr_jobs;
1091 --
1092 hr_utility.set_location(' Leaving:'||l_proc,20);
1093
1094 end chk_job_id;
1095 --
1096 --
1097 -- ---------------------------------------------------------------------------
1098 -- |-----------------------------<chk_grade_id>------------------------------|
1099 -- ---------------------------------------------------------------------------
1100 -- {Start Of Comments}
1101 --
1102 -- Description:
1103 -- This procedure is used to ensure that a job is the one valid
1104 --
1105 --
1106 -- In Arguments:
1107 -- p_business_group_id
1108 -- p_job_id
1109 -- p_grade_id
1110 -- p_effective_date
1111 --
1112 -- Post Success:
1113 -- No Error is raised
1114 --
1115 -- Post Failure:
1116 -- Errors are raised
1117 --
1118 -- {End Of Comments}
1119 -- ----------------------------------------------------------------------------
1120
1121 procedure chk_grade_id
1122 (p_business_group_id in number,
1123 p_grade_id in number,
1124 p_effective_date in date
1125 ) is
1126 --
1127 l_proc varchar2(72) := g_package || 'chk_grade_id';
1128 l_dummy varchar2(1);
1129
1130 cursor csr_grades
1131 is
1132 select 'X'
1133 from per_grades grade
1134 where grade.grade_id = p_grade_id
1135 and p_effective_date
1136 between grade.date_from and nvl (grade.date_to,g_eot);
1137 --
1138 begin
1139 hr_utility.set_location('Entering:'||l_proc,10);
1140 --
1141 open csr_grades;
1142 fetch csr_grades into l_dummy;
1143 if csr_grades%notfound then
1144 close csr_grades;
1145 fnd_message.set_name('PAY', 'PAY_33452_FK_DATA_INACTIVE');
1146 fnd_message.set_token('COLUMN','Grade Id');
1147 fnd_message.set_token('TABLE','per_grades');
1148 fnd_message.set_token('PROCEDURE',l_proc);
1149 fnd_message.raise_error;
1150 end if;
1151 close csr_grades;
1152 --
1153 hr_utility.set_location(' Leaving:'||l_proc,20);
1154
1155 end chk_grade_id;
1156
1157 -- ---------------------------------------------------------------------------
1158 -- |------------------------<chk_people_group_id>----------------------------|
1159 -- ---------------------------------------------------------------------------
1160 -- {Start Of Comments}
1161 --
1162 -- Description:
1163 -- This procedure is used to ensure that a position is the one valid
1164 -- for the business group
1165 --
1166 -- In Arguments:
1167 -- p_people_group_id
1168 -- p_effective_date
1169 --
1170 -- Post Success:
1171 -- No Error is raised
1172 --
1173 -- Post Failure:
1174 -- Errors are raised
1175 --
1176 -- {End Of Comments}
1177 -- ----------------------------------------------------------------------------
1178
1179 procedure chk_people_group_id
1180 (p_people_group_id in number,
1181 p_effective_date in date
1182 ) is
1183 --
1184 l_proc varchar2(72) := g_package || 'chk_people_group_id';
1185 l_dummy varchar2(1);
1186
1187 cursor csr_people_group_id is
1188 select 'X'
1189 from pay_people_groups ppg
1190 where people_group_id = p_people_group_id
1191 and p_effective_date between nvl(ppg.start_date_active,p_effective_date)
1192 and nvl(ppg.end_date_active, g_eot);
1193 --
1194 begin
1195 hr_utility.set_location('Entering:'||l_proc,10);
1196 --
1197 open csr_people_group_id;
1198 fetch csr_people_group_id into l_dummy;
1199 if csr_people_group_id%notfound then
1200 close csr_people_group_id;
1201 fnd_message.set_name('PAY', 'PAY_33452_FK_DATA_INACTIVE');
1202 fnd_message.set_token('COLUMN','People Group Id');
1203 fnd_message.set_token('TABLE','pay_people_groups');
1204 fnd_message.set_token('PROCEDURE',l_proc);
1205 fnd_message.raise_error;
1206 end if;
1207 close csr_people_group_id;
1208 --
1209 hr_utility.set_location(' Leaving:'||l_proc,20);
1210
1211 end chk_people_group_id;
1212
1213 --
1214 -- ---------------------------------------------------------------------------
1215 -- |---------------------<chk_linktoallpr>-----------------------------------|
1216 -- ---------------------------------------------------------------------------
1217 -- {Start Of Comments}
1218 --
1219 -- Description:
1220 -- This procedure is used to ensure that a the link to all payrolls if Yes
1221 -- then a payroll id is not specified
1222 --
1223 -- In Arguments:
1224 -- p_link_to_all_payrolls_flag
1225 -- p_payroll_id
1226 --
1227 -- Post Success:
1228 -- No Error is raised
1229 --
1230 -- Post Failure:
1231 -- Errors are raised
1232 --
1233 -- {End Of Comments}
1234 -- ----------------------------------------------------------------------------
1235
1236 procedure chk_linktoallpr
1237 (p_link_to_all_payrolls_flag in varchar2,
1238 p_payroll_id in number
1239 ) is
1240 --
1241 l_proc varchar2(72) := g_package || 'chk_linktoallpr';
1242 --
1243 begin
1244 hr_utility.set_location('Entering:'||l_proc,10);
1245 --
1246 if p_link_to_all_payrolls_flag = 'Y' and p_payroll_id is not null then
1247 fnd_message.set_name('PAY', 'PAY_33088_LINK_ALL_PAYROLLS');
1248 fnd_message.raise_error;
1249 end if;
1250 --
1251 hr_utility.set_location(' Leaving:'||l_proc,20);
1252
1253 end chk_linktoallpr;
1254
1255 --
1256 -- ---------------------------------------------------------------------------
1257 -- |---------------------------<chk_costable_type>---------------------------|
1258 -- ---------------------------------------------------------------------------
1259 -- {Start Of Comments}
1260 --
1261 -- Description:
1262 -- This procedure is used to ensure the following
1263 -- If the link is for an element whose primary classification has costable_flag
1264 -- set to 'N', then the costable_type must be "Not Costed".
1265 -- The costable type of the link can be 'Distributed' only if the element type
1266 -- has a PAY_VALUE of UOM 'Money', and it is not already a member of a
1267 -- distribution set. (there cannot be a complete reverse test on defintion
1268 -- of the distribution set, since these can be defined in terms of
1269 -- classifications only).
1270 -- If the costable type is 'Not Costed' then costing,balancing and distribution
1271 -- set must be null. Transfer_to_GL must be 'N'.
1272 -- If the costable type is 'Costed' or 'Fixed Costed', then distribution set
1273 -- must be null. Costing is optional, balancing is mandatory and
1274 -- transfer_to_GL defaults to 'Y', but can be changed.
1275 -- If the costable type is 'Distributed', then:
1276 -- 1. A distribution set must be specified. The credit/debit type of
1277 -- the classifications of elements in that set must match that
1278 -- of the type for the link. Consequently, an empty distribution
1279 -- set cannot be entered (the set being date-effectively empty, due
1280 -- to end dates on all member elements should be considered).
1281 -- 2. Costing is optional.
1282 -- If it is null, then all costing comes from the components of the
1283 -- distribution set. If it's not null, then that is used to override
1284 -- the appropriate segments of the cost codes of the components of
1285 -- the distribution set (see costing LLD for details).
1286 -- 3. Balancing is mandatory.
1287 -- 4. Transfers_to_GL defaults to 'Y'.
1288 --
1289 -- In Arguments:
1290 -- p_element_link_id
1291 -- p_business_group_id
1292 -- p_element_type_id
1293 -- p_costable_type
1294 -- p_element_set_id
1295 -- p_transfer_to_gl
1296 -- p_balancing_keyflex_id
1297 -- p_cost_allocation_keyflex_id
1298 --
1299 -- Post Success:
1300 -- No Error is raised
1301 --
1302 -- Post Failure:
1303 -- Errors are raised
1304 --
1305 -- {End Of Comments}
1306 -- ----------------------------------------------------------------------------
1307
1308 procedure chk_costable_type
1309 (p_effective_date in date,
1310 p_element_link_id in number,
1311 p_business_group_id in number,
1312 p_element_type_id in number,
1313 p_costable_type in varchar2,
1314 p_element_set_id in number,
1315 p_transfer_to_gl in varchar2,
1316 p_balancing_keyflex_id in number,
1317 p_cost_allocation_keyflex_id in number
1318 ) is
1319 --
1320 l_proc varchar2(72) := g_package || 'chk_costable_type';
1321 l_costable_flag pay_element_classifications.costable_flag%TYPE;
1322 l_uom pay_input_values_f.UOM%TYPE;
1323
1324 l_leg_code per_business_groups.legislation_code%TYPE;
1325 l_costing_dc pay_element_classifications.costing_debit_or_credit%TYPE;
1326
1327 l_dummy pay_element_sets.element_set_id%TYPE;
1328
1329 cursor csr_CostableFlag is
1330 select costable_flag from pay_element_classifications
1331 where classification_id in (select distinct classification_id
1332 from pay_element_types_f
1333 where element_type_id = p_element_type_id
1334 and p_effective_date between effective_start_date
1335 and effective_end_date);
1336
1337 cursor csr_PayValue is
1338 select UOM
1339 from pay_input_values_f
1340 where element_type_id = p_element_type_id
1341 and upper(name) like 'PAY VALUE'
1342 and p_effective_date between effective_start_date and effective_end_date;
1343
1344
1345 cursor csr_legcode_cr_db_type is
1346 select distinct pec.costing_debit_or_credit, pbg.legislation_code
1347 from pay_element_classifications pec, pay_element_types_f pet,
1348 per_business_groups pbg
1349 where pec.classification_id = pet.classification_id
1350 and pet.business_group_id = pbg.business_group_id (+)
1351 and pet.element_type_id = p_element_type_id;
1352
1353
1354 cursor csr_cr_db_class(p_costing_dc varchar2,p_leg_code varchar2) is
1355 select element_set.element_set_id
1356 from pay_element_sets element_set
1357 where element_set_id = p_element_set_id
1358 and element_set.element_set_type = 'D' and
1359 /* the element set is within the users responsibility area */
1360 ( element_set.business_group_id +0 = p_business_group_id
1361 or (element_set.business_group_id is null
1362 and element_set.legislation_code = p_leg_code))
1363 and /* either the link is not distributed costing or all of the
1364 classifications in the set have the same costing debit or credit flag
1365 as the link elements classification */
1366 exists (
1367 /* check the classifications of elements in the set that are specified
1368 directly by name */
1369 select 1
1370 from pay_element_type_rules rule, pay_element_types_f type,
1371 pay_element_classifications class
1372 where element_set.element_set_id = rule.element_set_id
1373 and class.classification_id = type.classification_id
1374 and type.element_type_id = rule.element_type_id
1375 and class.costing_debit_or_credit = p_costing_dc
1376 and p_effective_date between type.effective_start_date
1377 and type.effective_end_date
1378 union all
1379 /* check the element classifications specified in the set */
1380 select 1
1381 from pay_ele_classification_rules class_rule,
1382 pay_element_classifications class2
1383 where class_rule.element_set_id = element_set.element_set_id
1384 and class2.classification_id = class_rule.classification_id
1385 and class2.costing_debit_or_credit = p_costing_dc)
1386 and
1387 /* the element set is not empty
1388 (as far as this user is concerned) */
1389 exists (
1390 /* check that there are elements directly included in
1391 the set or (see second half of union statement) */
1392 select 1
1393 from pay_element_type_rules rule2, pay_element_types_f type2
1394 where element_set.element_set_id=rule2.element_set_id
1395 and rule2.element_type_id=type2.element_type_id
1396 and rule2.include_or_exclude = 'I'
1397 and (type2.business_group_id = p_business_group_id
1398 or (type2.business_group_id is null
1399 and type2.legislation_code = p_leg_code))
1400 and p_effective_date between type2.effective_start_date
1401 and type2.effective_end_date
1402 union all
1403 /* check that there are elements with the classification that is included
1404 in the set */
1405 select 1
1406 from pay_element_types_f type3, pay_ele_classification_rules class_rule2
1407 where class_rule2.element_set_id = element_set.element_set_id
1408 and class_rule2.classification_id = type3.classification_id
1409 and (type3.business_group_id = p_business_group_id
1410 or (type3.business_group_id is null
1411 and type3.legislation_code = p_leg_code))
1412 and p_effective_date between type3.effective_start_date
1413 and type3.effective_end_date );
1414 --
1415 begin
1416 hr_utility.set_location('Entering:'||l_proc,10);
1417 --
1418 open csr_CostableFlag;
1419 Fetch csr_CostableFlag into l_costable_flag;
1420 close csr_CostableFlag;
1421
1422 open csr_PayValue;
1423 fetch csr_PayValue into l_uom;
1424 close csr_PayValue;
1425
1426 open csr_legcode_cr_db_type;
1427 fetch csr_legcode_cr_db_type into l_costing_dc,l_leg_code;
1428 close csr_legcode_cr_db_type;
1429
1430 if l_costable_flag = 'N' and p_costable_type <> 'N' then
1431 fnd_message.set_name('PAY','PAY_33089_COST_FLAG_TYPE_MISMATCH');
1432 fnd_message.set_token('TABLE_NAME','pay_element_links_f');
1433 fnd_message.raise_error;
1434 end if;
1435
1436 if p_costable_type = 'N'
1437 and
1438 (p_transfer_to_gl <> 'N'
1439 or p_cost_allocation_keyflex_id is not null
1440 or p_balancing_keyflex_id is not null
1441 or p_element_set_id is not null) then
1442 fnd_message.set_name('PAY','PAY_33091_COST_BAL_ELESET_NC');
1443 fnd_message.raise_error;
1444 elsif (p_costable_type = 'C' or p_costable_type = 'F')
1445 and
1446 ( p_element_set_id is not null or p_balancing_keyflex_id is null) then
1447 fnd_message.set_name('PAY','PAY_33092_BAL_MAND_ELE_SET_NM');
1448 fnd_message.raise_error;
1449 elsif p_costable_type = 'D' then
1450 if p_element_set_id is null or p_balancing_keyflex_id is null
1451 then
1452 fnd_message.set_name('PAY','PAY_33095_LINK_MAN_BAL_ELE_SET');
1453 fnd_message.raise_error;
1454 elsif l_uom <> 'M' then
1455 fnd_message.set_name('PAY','PAY_33090_UOM_COST_TYPE_DISTRI');
1456 fnd_message.raise_error;
1457 elsif pay_element_links_pkg.element_in_distribution_set
1458 ( p_element_type_id,
1459 p_business_group_id,
1460 l_leg_code) then
1461 fnd_message.set_name('PAY','PAY_6462_LINK_DIST_IN_DIST');
1462 fnd_message.raise_error;
1463 else
1464 open csr_cr_db_class(l_costing_dc,l_leg_code);
1465 fetch csr_cr_db_class into l_dummy;
1466 if csr_cr_db_class%notfound then
1467 fnd_message.set_name('PAY','PAY_33451_CR_DB_CLASS_MISMA');
1468 fnd_message.raise_error;
1469 end if;
1470 close csr_cr_db_class;
1471 end if;
1472 end if;
1473 --
1474 hr_utility.set_location(' Leaving:'||l_proc,20);
1475
1476 end chk_costable_type;
1477
1478 --
1479 -- ---------------------------------------------------------------------------
1480 -- |---------------------<chk_costable_type_for_upd>-------------------------|
1481 -- ---------------------------------------------------------------------------
1482 -- {Start Of Comments}
1483 --
1484 -- Description:
1485 -- This procedure is used to ensure that the costable type if updated is
1486 -- for lifetime of the element link
1487 --
1488 -- In Arguments:
1489 -- p_costable_type
1490 -- p_datetrack_mode
1491 --
1492 -- Post Success:
1493 -- No Error is raised
1494 --
1495 -- Post Failure:
1496 -- Errors are raised
1497 --
1498 -- {End Of Comments}
1499 -- ----------------------------------------------------------------------------
1500
1501
1502 procedure chk_costable_type_for_upd
1503 (p_costable_type in varchar2,
1504 p_datetrack_mode in varchar2)
1505 is
1506 l_proc varchar2(72) := g_package || 'chk_costable_type_for_upd';
1507 begin
1508 hr_utility.set_location('Entering:'||l_proc,10);
1509 if pay_pel_shd.g_old_rec.costable_type <> p_costable_type
1510 and p_datetrack_mode = hr_api.g_update then
1511 fnd_message.set_name('PAY', 'PAY_6466_LINK_NO_COST_UPD2');
1512 fnd_message.raise_error;
1513 end if;
1514 hr_utility.set_location(' Leaving:'||l_proc,20);
1515 end;
1516
1517 --
1518 -- ---------------------------------------------------------------------------
1519 -- |------------------------<chk_standard_link_flag>-------------------------|
1520 -- ---------------------------------------------------------------------------
1521 -- {Start Of Comments}
1522 --
1523 -- Description:
1524 -- This procedure is used to ensure the following business rules are
1525 -- satisfied when the standard link flag is updated from No to Yes:
1526 -- 1. the change will not take place over the lifetime of the link.
1527 -- (ie. there exist date-effective updates to the link).
1528 -- 2. no entries exist for the link.
1529 -- 3. no salary basis is associated with the standard link element.
1530 -- We also check in this procedure that the standard link flag is never set
1531 -- to 'Y' for elements whose processing type is non-recurring.
1532 --
1533 -- In Arguments:
1534 -- p_element_type_id
1535 -- p_business_group_id
1536 -- p_element_link_id
1537 -- p_standard_link_flag
1538 -- p_effective_date
1539 -- p_datetrack_mode
1540 --
1541 -- Post Success:
1542 -- No Error is raised
1543 --
1544 -- Post Failure:
1545 -- Errors are raised
1546 --
1547 -- {End Of Comments}
1548 -- ----------------------------------------------------------------------------
1549
1550 procedure chk_standard_link_flag
1551 (p_element_type_id in number,
1552 p_business_group_id in number,
1553 p_element_link_id in number,
1554 p_standard_link_flag in varchar2,
1555 p_effective_date in date,
1556 p_datetrack_mode in varchar2
1557 ) is
1558 --
1559 l_proc varchar2(72) := g_package || 'chk_standard_link_flag';
1560 l_dummy varchar2(1);
1561 l_processing_type varchar2(30);
1562 l_pay_basis_exists number;
1563
1564 cursor csr_DateEffRecs is
1565 select null
1566 from pay_element_entries_f
1567 where element_link_id = p_element_link_id;
1568
1569 cursor csr_ProcessingType is
1570 select processing_type
1571 from pay_element_types_f
1572 where element_type_id = p_element_type_id
1573 and p_effective_date between effective_start_date and effective_end_date;
1574
1575 /* commented the below block for bug no : 6764215
1576
1577 cursor csr_pay_basis_exists is
1578 select 1
1579 from
1580 pay_input_values_f piv
1581 ,per_pay_bases ppb
1582 where
1583 piv.element_type_id = p_element_type_id
1584 and p_effective_date between piv.effective_start_date
1585 and piv.effective_end_date
1586 and ppb.input_value_id = piv.input_value_id
1587 and ppb.business_group_id = p_business_group_id;
1588
1589 */
1590 --
1591 begin
1592 hr_utility.set_location('Entering:'||l_proc,10);
1593 --
1594 -- Only check for change of standard link flag if not inserting
1595 if (p_datetrack_mode <> hr_api.g_insert)
1596 then
1597 if pay_pel_shd.g_old_rec.standard_link_flag = 'N'
1598 and p_standard_link_flag = 'Y' then
1599 hr_utility.set_location(l_proc,20);
1600 open csr_DateEffRecs;
1601 fetch csr_DateEffRecs into l_dummy;
1602 if (csr_DateEffRecs%found or p_datetrack_mode = hr_api.g_update) then
1603 close csr_DateEffRecs;
1604 fnd_message.set_name('PAY', 'PAY_6733_LINK_NO_UPD_STAN_FLAG');
1605 fnd_message.raise_error;
1606 end if;
1607 close csr_DateEffRecs;
1608 end if;
1609 end if;
1610 --
1611 -- Bugfix 5012412
1612 -- Match standard link flag against processing type
1613 if (p_standard_link_flag = 'Y') then
1614 hr_utility.set_location(l_proc,30);
1615 --
1616 open csr_ProcessingType;
1617 fetch csr_ProcessingType into l_processing_type;
1618 close csr_ProcessingType;
1619 --
1620 if (l_processing_type = 'N')
1621 then
1622 hr_utility.set_location(l_proc,40);
1623 -- Standard link flag can only be 'Y' if processing type is 'R', i.e. recurring
1624 fnd_message.set_name('PAY', 'PAY_33296_INVLD_STD_LINK_FLAG');
1625 fnd_message.raise_error;
1626 end if;
1627 --
1628 hr_utility.set_location(l_proc,40);
1629 --
1630 -- #5512101.
1631 -- Check to see if salary basis exists for this element type.
1632
1633 /* commented the below block for bug no : 6764215
1634
1635 open csr_pay_basis_exists;
1636 fetch csr_pay_basis_exists into l_pay_basis_exists;
1637 if csr_pay_basis_exists%found then
1638 close csr_pay_basis_exists;
1639 fnd_message.set_name('PAY', 'PAY_33093_LINK_NO_PAY_BASIS');
1640 fnd_message.raise_error;
1641 end if;
1642 close csr_pay_basis_exists;
1643
1644 */
1645 end if;
1646 --
1647 hr_utility.set_location(' Leaving:'||l_proc,50);
1648
1649 end chk_standard_link_flag;
1650
1651 --
1652 -- ---------------------------------------------------------------------------
1653 -- |------------------------<chk_date_eff_delete>----------------------------|
1654 -- ---------------------------------------------------------------------------
1655 --
1656 procedure chk_date_eff_delete
1657 (p_element_link_id in number,
1658 p_delete_mode in varchar2,
1659 p_validation_start_date in date
1660 ) is
1661 --
1662 l_proc varchar2(72) := g_package || 'chk_date_eff_delete';
1663 --
1664 begin
1665 hr_utility.set_location('Entering:'||l_proc,10);
1666 --
1667 pay_element_links_pkg.check_deletion_allowed(p_element_link_id,p_delete_mode,
1668 p_validation_start_date);
1669
1670 hr_utility.set_location(' Leaving:'||l_proc,20);
1671 end chk_date_eff_delete;
1672
1673 -- ----------------------------------------------------------------------------
1674 -- |---------------------------< insert_validate >----------------------------|
1675 -- ----------------------------------------------------------------------------
1676 Procedure insert_validate
1677 (p_rec in pay_pel_shd.g_rec_type
1678 ,p_effective_date in date
1679 ,p_datetrack_mode in varchar2
1680 ,p_validation_start_date in date
1681 ,p_validation_end_date in date
1682 ) is
1683 --
1684 l_proc varchar2(72) := g_package||'insert_validate';
1685 --
1686 Begin
1687 hr_utility.set_location('Entering:'||l_proc, 5);
1688 --
1689 -- Call all supporting business operations
1690 --
1691 hr_api.validate_bus_grp_id
1692 (p_business_group_id => p_rec.business_group_id
1693 ,p_associated_column1 => pay_pel_shd.g_tab_nam
1694 || '.BUSINESS_GROUP_ID');
1695 --
1696 -- After validating the set of important attributes,
1697 -- if Multiple Message detection is enabled and at least
1698 -- one error has been found then abort further validation.
1699 --
1700
1701 hr_multi_message.end_validation_set;
1702 --
1703 -- Validate Dependent Attributes
1704 --
1705 --
1706
1707 if p_rec.standard_link_flag = 'Y'
1708 then
1709 pay_pel_bus.chk_standard_link_flag
1710 (p_element_type_id => p_rec.element_type_id
1711 ,p_business_group_id => p_rec.business_group_id
1712 ,p_element_link_id => p_rec.element_link_id
1713 ,p_standard_link_flag => p_rec.standard_link_flag
1714 ,p_effective_date => p_effective_date
1715 ,p_datetrack_mode => p_datetrack_mode
1716 );
1717 end if;
1718
1719 pay_pel_bus.chk_ben_contri
1720 (p_element_type_id => p_rec.element_type_id
1721 ,p_effective_start_date => p_effective_date
1722 );
1723
1724 pay_pel_bus.chk_emp_cat
1725 (p_employment_category => p_rec.employment_category,
1726 p_effective_date => p_effective_date
1727 );
1728
1729
1730 if p_rec.job_id is not null then
1731 pay_pel_bus.chk_job_id
1732 (p_business_group_id => p_rec.business_group_id,
1733 p_job_id => p_rec.job_id,
1734 p_effective_date => p_effective_date
1735 );
1736 end if;
1737
1738 if p_rec.organization_id is not null then
1739 chk_org_unit
1740 (p_business_group_id => p_rec.business_group_id
1741 ,p_organization_id => p_rec.organization_id
1742 ,p_effective_date => p_effective_date
1743 );
1744 end if;
1745
1746 if p_rec.grade_id is not null then
1747 pay_pel_bus.chk_grade_id
1748 (p_business_group_id => p_rec.business_group_id,
1749 p_grade_id => p_rec.grade_id,
1750 p_effective_date => p_effective_date
1751 );
1752 end if;
1753
1754 if p_rec.people_group_id is not null then
1755 pay_pel_bus.chk_people_group_id
1756 (p_people_group_id => p_rec.people_group_id,
1757 p_effective_date => p_effective_date
1758 );
1759 end if;
1760
1761
1762 if p_rec.position_id is not null then
1763 pay_pel_bus.chk_position
1764 (p_business_group_id => p_rec.business_group_id
1765 ,p_position_id => p_rec.position_id
1766 ,p_organization_id => p_rec.organization_id
1767 ,p_job_id => p_rec.job_id
1768 ,p_effective_date => p_effective_date
1769 );
1770 end if;
1771
1772 pay_pel_bus.chk_linktoallpr
1773 (p_link_to_all_payrolls_flag => p_rec.link_to_all_payrolls_flag
1774 ,p_payroll_id => p_rec.payroll_id
1775 );
1776
1777 pay_pel_bus.chk_costable_type
1778 (p_effective_date => p_effective_date
1779 ,p_element_link_id => p_rec.element_link_id
1780 ,p_business_group_id => p_rec.business_group_id
1781 ,p_element_type_id => p_rec.element_type_id
1782 ,p_costable_type => p_rec.costable_type
1783 ,p_element_set_id => p_rec.element_set_id
1784 ,p_transfer_to_gl => p_rec.transfer_to_gl_flag
1785 ,p_balancing_keyflex_id => p_rec.balancing_keyflex_id
1786 ,p_cost_allocation_keyflex_id => p_rec.cost_allocation_keyflex_id
1787 );
1788
1789
1790 end insert_validate;
1791 --
1792 -- ----------------------------------------------------------------------------
1793 -- |---------------------------< update_validate >----------------------------|
1794 -- ----------------------------------------------------------------------------
1795 Procedure update_validate
1796 (p_rec in pay_pel_shd.g_rec_type
1797 ,p_effective_date in date
1798 ,p_datetrack_mode in varchar2
1799 ,p_validation_start_date in date
1800 ,p_validation_end_date in date
1801 ) is
1802 --
1803 l_proc varchar2(72) := g_package||'update_validate';
1804
1805 l_qualifying_age pay_element_links_f.qualifying_age%TYPE;
1806 l_qualifying_length_of_service
1807 pay_element_links_f.qualifying_length_of_service%TYPE;
1808 l_qualifying_units pay_element_links_f.qualifying_units%TYPE;
1809 l_multiply_value_flag pay_element_links_f.multiply_value_flag%TYPE;
1810 l_standard_link_flag pay_element_links_f.standard_link_flag%TYPE;
1811
1812
1813 --
1814 Begin
1815 hr_utility.set_location('Entering:'||l_proc, 5);
1816 --
1817 -- Call all supporting business operations
1818 --
1819 hr_api.validate_bus_grp_id
1820 (p_business_group_id => p_rec.business_group_id
1821 ,p_associated_column1 => pay_pel_shd.g_tab_nam
1822 || '.BUSINESS_GROUP_ID');
1823 --
1824 -- After validating the set of important attributes,
1825 -- if Multiple Message detection is enabled and at least
1826 -- one error has been found then abort further validation.
1827 --
1828 hr_multi_message.end_validation_set;
1829 --
1830 -- Validate Dependent Attributes
1831 --
1832 -- Call the datetrack update integrity operation
1833 --
1834 dt_update_validate
1835 (p_payroll_id => p_rec.payroll_id
1836 ,p_element_type_id => p_rec.element_type_id
1837 ,p_datetrack_mode => p_datetrack_mode
1838 ,p_validation_start_date => p_validation_start_date
1839 ,p_validation_end_date => p_validation_end_date
1840 );
1841 --
1842 chk_non_updateable_args
1843 (p_effective_date => p_effective_date
1844 ,p_rec => p_rec
1845 );
1846 --
1847 -- Bug 5512101. Batch Element Link support.
1848 -- Ensure that the element link is complete.
1849 --
1850 pay_batch_object_status_pkg.chk_complete_status
1851 (p_object_type => 'EL'
1852 ,p_object_id => p_rec.element_link_id
1853 );
1854
1855 -- Check for Standard link flag update
1856 pay_pel_bus.chk_standard_link_flag
1857 (p_element_type_id => p_rec.element_type_id
1858 ,p_business_group_id => p_rec.business_group_id
1859 ,p_element_link_id => p_rec.element_link_id
1860 ,p_standard_link_flag => p_rec.standard_link_flag
1861 ,p_effective_date => p_effective_date
1862 ,p_datetrack_mode => p_datetrack_mode
1863 );
1864
1865 -- Check for Costable type updation
1866 pay_pel_bus.chk_costable_type_for_upd
1867 (p_costable_type => p_rec.costable_type ,
1868 p_datetrack_mode => p_datetrack_mode);
1869
1870 -- Check qualifying conditions
1871
1872 l_qualifying_age := p_rec.qualifying_age;
1873 l_qualifying_length_of_service := p_rec.qualifying_length_of_service;
1874 l_qualifying_units := p_rec.qualifying_units;
1875 l_multiply_value_flag := p_rec.multiply_value_flag;
1876 l_standard_link_flag := p_rec.standard_link_flag;
1877
1878
1879 pay_pel_bus.chk_defaults
1880 (p_element_type_id => null
1881 ,p_qualifying_age => l_qualifying_age
1882 ,p_qualifying_length_of_service => l_qualifying_length_of_service
1883 ,p_qualifying_units => l_qualifying_units
1884 ,p_multiply_value_flag => l_multiply_value_flag
1885 ,p_standard_link_flag => l_standard_link_flag
1886 ,p_effective_date => p_effective_date
1887 );
1888
1889
1890
1891 -- Check for general checks for costable type
1892 pay_pel_bus.chk_costable_type
1893 (p_effective_date => p_effective_date,
1894 p_element_link_id => p_rec.element_link_id,
1895 p_business_group_id => p_rec.business_group_id,
1896 p_element_type_id => p_rec.element_type_id,
1897 p_costable_type => p_rec.costable_type ,
1898 p_element_set_id => p_rec.element_set_id,
1899 p_transfer_to_gl => p_rec.transfer_to_gl_flag ,
1900 p_balancing_keyflex_id => p_rec.balancing_keyflex_id ,
1901 p_cost_allocation_keyflex_id => p_rec.cost_allocation_keyflex_id
1902 );
1903 hr_utility.set_location(' Leaving:'||l_proc, 10);
1904 End update_validate;
1905 --
1906 -- ----------------------------------------------------------------------------
1907 -- |---------------------------< delete_validate >----------------------------|
1908 -- ----------------------------------------------------------------------------
1909 Procedure delete_validate
1910 (p_rec in pay_pel_shd.g_rec_type
1911 ,p_effective_date in date
1912 ,p_datetrack_mode in varchar2
1913 ,p_validation_start_date in date
1914 ,p_validation_end_date in date
1915 ) is
1916 --
1917 l_proc varchar2(72) := g_package||'delete_validate';
1918 --
1919 Begin
1920 hr_utility.set_location('Entering:'||l_proc, 5);
1921 --
1922 -- Call all supporting business operations
1923 --
1924 dt_delete_validate
1925 (p_datetrack_mode => p_datetrack_mode
1926 ,p_validation_start_date => p_validation_start_date
1927 ,p_validation_end_date => p_validation_end_date
1928 ,p_element_link_id => p_rec.element_link_id
1929 );
1930
1931 --
1932 hr_utility.set_location(' Leaving:'||l_proc, 10);
1933 End delete_validate;
1934 --
1935 end pay_pel_bus;
[Home] [Help]