[Home] [Help]
PACKAGE BODY: APPS.HXC_TER_BUS
Source
1 Package Body hxc_ter_bus as
2 /* $Header: hxcterrhi.pkb 120.2 2005/09/23 09:19:47 nissharm noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' hxc_ter_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_time_entry_rule_id number default null;
15
16 g_debug boolean := hr_utility.debug_enabled;
17 --
18 --
19 -- ----------------------------------------------------------------------------
20 -- |------------------------------< chk_df >----------------------------------|
21 -- ----------------------------------------------------------------------------
22 --
23 -- Description:
24 -- Validates all the Descriptive Flexfield values.
25 --
26 -- Prerequisites:
27 -- All other columns have been validated. Must be called as the
28 -- last step from insert_validate and update_validate.
29 --
30 -- In Arguments:
31 -- p_rec
32 --
33 -- Post Success:
34 -- If the Descriptive Flexfield structure column and data values are
35 -- all valid this procedure will end normally and processing will
36 -- continue.
37 --
38 -- Post Failure:
39 -- If the Descriptive Flexfield structure column value or any of
40 -- the data values are invalid then an application error is raised as
41 -- a PL/SQL exception.
42 --
43 -- Access Status:
44 -- Internal Row Handler Use Only.
45 --
46 -- ----------------------------------------------------------------------------
47 procedure chk_df
48 (p_rec in hxc_ter_shd.g_rec_type
49 ) is
50 --
51 l_proc varchar2(72);
52 --
53 begin
54 g_debug := hr_utility.debug_enabled;
55
56 if g_debug then
57 l_proc := g_package || 'chk_df';
58 hr_utility.set_location('Entering:'||l_proc,10);
59 end if;
60 --
61 if ((p_rec.time_entry_rule_id is not null) and (
62 nvl(hxc_ter_shd.g_old_rec.attribute_category, hr_api.g_varchar2) <>
63 nvl(p_rec.attribute_category, hr_api.g_varchar2) or
64 nvl(hxc_ter_shd.g_old_rec.attribute1, hr_api.g_varchar2) <>
65 nvl(p_rec.attribute1, hr_api.g_varchar2) or
66 nvl(hxc_ter_shd.g_old_rec.attribute2, hr_api.g_varchar2) <>
67 nvl(p_rec.attribute2, hr_api.g_varchar2) or
68 nvl(hxc_ter_shd.g_old_rec.attribute3, hr_api.g_varchar2) <>
69 nvl(p_rec.attribute3, hr_api.g_varchar2) or
70 nvl(hxc_ter_shd.g_old_rec.attribute4, hr_api.g_varchar2) <>
71 nvl(p_rec.attribute4, hr_api.g_varchar2) or
72 nvl(hxc_ter_shd.g_old_rec.attribute5, hr_api.g_varchar2) <>
73 nvl(p_rec.attribute5, hr_api.g_varchar2) or
74 nvl(hxc_ter_shd.g_old_rec.attribute6, hr_api.g_varchar2) <>
75 nvl(p_rec.attribute6, hr_api.g_varchar2) or
76 nvl(hxc_ter_shd.g_old_rec.attribute7, hr_api.g_varchar2) <>
77 nvl(p_rec.attribute7, hr_api.g_varchar2) or
78 nvl(hxc_ter_shd.g_old_rec.attribute8, hr_api.g_varchar2) <>
79 nvl(p_rec.attribute8, hr_api.g_varchar2) or
80 nvl(hxc_ter_shd.g_old_rec.attribute9, hr_api.g_varchar2) <>
81 nvl(p_rec.attribute9, hr_api.g_varchar2) or
82 nvl(hxc_ter_shd.g_old_rec.attribute10, hr_api.g_varchar2) <>
83 nvl(p_rec.attribute10, hr_api.g_varchar2) or
84 nvl(hxc_ter_shd.g_old_rec.attribute11, hr_api.g_varchar2) <>
85 nvl(p_rec.attribute11, hr_api.g_varchar2) or
86 nvl(hxc_ter_shd.g_old_rec.attribute12, hr_api.g_varchar2) <>
87 nvl(p_rec.attribute12, hr_api.g_varchar2) or
88 nvl(hxc_ter_shd.g_old_rec.attribute13, hr_api.g_varchar2) <>
89 nvl(p_rec.attribute13, hr_api.g_varchar2) or
90 nvl(hxc_ter_shd.g_old_rec.attribute14, hr_api.g_varchar2) <>
91 nvl(p_rec.attribute14, hr_api.g_varchar2) or
92 nvl(hxc_ter_shd.g_old_rec.attribute15, hr_api.g_varchar2) <>
93 nvl(p_rec.attribute15, hr_api.g_varchar2) or
94 nvl(hxc_ter_shd.g_old_rec.attribute16, hr_api.g_varchar2) <>
95 nvl(p_rec.attribute16, hr_api.g_varchar2) or
96 nvl(hxc_ter_shd.g_old_rec.attribute17, hr_api.g_varchar2) <>
97 nvl(p_rec.attribute17, hr_api.g_varchar2) or
98 nvl(hxc_ter_shd.g_old_rec.attribute18, hr_api.g_varchar2) <>
99 nvl(p_rec.attribute18, hr_api.g_varchar2) or
100 nvl(hxc_ter_shd.g_old_rec.attribute19, hr_api.g_varchar2) <>
101 nvl(p_rec.attribute19, hr_api.g_varchar2) or
102 nvl(hxc_ter_shd.g_old_rec.attribute20, hr_api.g_varchar2) <>
103 nvl(p_rec.attribute20, hr_api.g_varchar2) or
104 nvl(hxc_ter_shd.g_old_rec.attribute21, hr_api.g_varchar2) <>
105 nvl(p_rec.attribute21, hr_api.g_varchar2) or
106 nvl(hxc_ter_shd.g_old_rec.attribute22, hr_api.g_varchar2) <>
107 nvl(p_rec.attribute22, hr_api.g_varchar2) or
108 nvl(hxc_ter_shd.g_old_rec.attribute23, hr_api.g_varchar2) <>
109 nvl(p_rec.attribute23, hr_api.g_varchar2) or
110 nvl(hxc_ter_shd.g_old_rec.attribute24, hr_api.g_varchar2) <>
111 nvl(p_rec.attribute24, hr_api.g_varchar2) or
112 nvl(hxc_ter_shd.g_old_rec.attribute25, hr_api.g_varchar2) <>
113 nvl(p_rec.attribute25, hr_api.g_varchar2) or
114 nvl(hxc_ter_shd.g_old_rec.attribute26, hr_api.g_varchar2) <>
115 nvl(p_rec.attribute26, hr_api.g_varchar2) or
116 nvl(hxc_ter_shd.g_old_rec.attribute27, hr_api.g_varchar2) <>
117 nvl(p_rec.attribute27, hr_api.g_varchar2) or
118 nvl(hxc_ter_shd.g_old_rec.attribute28, hr_api.g_varchar2) <>
119 nvl(p_rec.attribute28, hr_api.g_varchar2) or
120 nvl(hxc_ter_shd.g_old_rec.attribute29, hr_api.g_varchar2) <>
121 nvl(p_rec.attribute29, hr_api.g_varchar2) or
122 nvl(hxc_ter_shd.g_old_rec.attribute30, hr_api.g_varchar2) <>
123 nvl(p_rec.attribute30, hr_api.g_varchar2) ))
124 or (p_rec.time_entry_rule_id is null) then
125 --
126 -- Only execute the validation if absolutely necessary:
127 -- a) During update, the structure column value or any
128 -- of the attribute values have actually changed.
129 -- b) During insert.
130 --
131 hr_dflex_utility.ins_or_upd_descflex_attribs
132 (p_appl_short_name => 'HXC'
133 ,p_descflex_name => 'OTL Formulas'
134 ,p_attribute_category => p_rec.attribute_category
135 ,p_attribute1_name => 'ATTRIBUTE1'
136 ,p_attribute1_value => p_rec.attribute1
137 ,p_attribute2_name => 'ATTRIBUTE2'
138 ,p_attribute2_value => p_rec.attribute2
139 ,p_attribute3_name => 'ATTRIBUTE3'
140 ,p_attribute3_value => p_rec.attribute3
141 ,p_attribute4_name => 'ATTRIBUTE4'
142 ,p_attribute4_value => p_rec.attribute4
143 ,p_attribute5_name => 'ATTRIBUTE5'
144 ,p_attribute5_value => p_rec.attribute5
145 ,p_attribute6_name => 'ATTRIBUTE6'
146 ,p_attribute6_value => p_rec.attribute6
147 ,p_attribute7_name => 'ATTRIBUTE7'
148 ,p_attribute7_value => p_rec.attribute7
149 ,p_attribute8_name => 'ATTRIBUTE8'
150 ,p_attribute8_value => p_rec.attribute8
151 ,p_attribute9_name => 'ATTRIBUTE9'
152 ,p_attribute9_value => p_rec.attribute9
153 ,p_attribute10_name => 'ATTRIBUTE10'
154 ,p_attribute10_value => p_rec.attribute10
155 ,p_attribute11_name => 'ATTRIBUTE11'
156 ,p_attribute11_value => p_rec.attribute11
157 ,p_attribute12_name => 'ATTRIBUTE12'
158 ,p_attribute12_value => p_rec.attribute12
159 ,p_attribute13_name => 'ATTRIBUTE13'
160 ,p_attribute13_value => p_rec.attribute13
161 ,p_attribute14_name => 'ATTRIBUTE14'
162 ,p_attribute14_value => p_rec.attribute14
163 ,p_attribute15_name => 'ATTRIBUTE15'
164 ,p_attribute15_value => p_rec.attribute15
165 ,p_attribute16_name => 'ATTRIBUTE16'
166 ,p_attribute16_value => p_rec.attribute16
167 ,p_attribute17_name => 'ATTRIBUTE17'
168 ,p_attribute17_value => p_rec.attribute17
169 ,p_attribute18_name => 'ATTRIBUTE18'
170 ,p_attribute18_value => p_rec.attribute18
171 ,p_attribute19_name => 'ATTRIBUTE19'
172 ,p_attribute19_value => p_rec.attribute19
173 ,p_attribute20_name => 'ATTRIBUTE20'
174 ,p_attribute20_value => p_rec.attribute20
175 ,p_attribute21_name => 'ATTRIBUTE21'
176 ,p_attribute21_value => p_rec.attribute21
177 ,p_attribute22_name => 'ATTRIBUTE22'
178 ,p_attribute22_value => p_rec.attribute22
179 ,p_attribute23_name => 'ATTRIBUTE23'
180 ,p_attribute23_value => p_rec.attribute23
181 ,p_attribute24_name => 'ATTRIBUTE24'
182 ,p_attribute24_value => p_rec.attribute24
183 ,p_attribute25_name => 'ATTRIBUTE25'
184 ,p_attribute25_value => p_rec.attribute25
185 ,p_attribute26_name => 'ATTRIBUTE26'
186 ,p_attribute26_value => p_rec.attribute26
187 ,p_attribute27_name => 'ATTRIBUTE27'
188 ,p_attribute27_value => p_rec.attribute27
189 ,p_attribute28_name => 'ATTRIBUTE28'
190 ,p_attribute28_value => p_rec.attribute28
191 ,p_attribute29_name => 'ATTRIBUTE29'
192 ,p_attribute29_value => p_rec.attribute29
193 ,p_attribute30_name => 'ATTRIBUTE30'
194 ,p_attribute30_value => p_rec.attribute30
195 );
196 end if;
197 --
198 if g_debug then
199 hr_utility.set_location(' Leaving:'||l_proc,20);
200 end if;
201 end chk_df;
202 --
203 -- ----------------------------------------------------------------------------
204 -- |-----------------------< chk_non_updateable_args >------------------------|
205 -- ----------------------------------------------------------------------------
206 --
207 -- Description:
208 -- This procedure is used to ensure that non updateable attributes have
209 -- not been updated. If an attribute has been updated an error is generated.
210 --
211 -- Pre Conditions:
212 -- g_old_rec has been populated with details of the values currently in
213 -- the database.
214 --
215 -- In Arguments:
216 -- p_rec has been populated with the updated values the user would like the
217 -- record set to.
218 --
219 -- Post Success:
220 -- Processing continues if all the non updateable attributes have not
221 -- changed.
222 --
223 -- Post Failure:
224 -- An application error is raised if any of the non updatable attributes
225 -- have been altered.
226 --
227 -- {End Of Comments}
228 -- ----------------------------------------------------------------------------
229 Procedure chk_non_updateable_args
230 (p_effective_date in date
231 ,p_rec in hxc_ter_shd.g_rec_type
232 ) IS
233 --
234 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
235 l_error EXCEPTION;
236 l_argument varchar2(30);
237 --
238 Begin
239 --
240 -- Only proceed with the validation if a row exists for the current
241 -- record in the HR Schema.
242 --
243 IF NOT hxc_ter_shd.api_updating
244 (p_time_entry_rule_id => p_rec.time_entry_rule_id
245 ,p_object_version_number => p_rec.object_version_number
246 ) THEN
247 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
248 fnd_message.set_token('PROCEDURE ', l_proc);
249 fnd_message.set_token('STEP ', '5');
250 fnd_message.raise_error;
251 END IF;
252 --
253 -- EDIT_HERE: Add checks to ensure non-updateable args have
254 -- not been updated.
255 --
256 EXCEPTION
257 WHEN l_error THEN
258 hr_api.argument_changed_error
259 (p_api_name => l_proc
260 ,p_argument => l_argument);
261 WHEN OTHERS THEN
262 RAISE;
263 End chk_non_updateable_args;
264 --
265 -- ----------------------------------------------------------------------------
266 -- |-----------------------< chk_name >---------------------------------------|
267 -- ----------------------------------------------------------------------------
268 -- {Start Of Comments}
269 --
270 -- Description:
271 -- This procedure insures a valid time entry rule name
272 --
273 -- Pre Conditions:
274 -- None
275 --
276 -- In Arguments:
277 -- name
278 -- object_version_number
279 -- start_date
280 -- end_date
281 --
282 -- Post Success:
283 -- Processing continues if the name business rules have not been violated
284 --
285 -- Post Failure:
286 -- An application error is raised if the name is not valid
287 --
288 -- {End Of Comments}
289 -- ----------------------------------------------------------------------------
290 Procedure chk_name
291 (
292 p_name in hxc_time_entry_rules.name%TYPE
293 ,p_time_entry_rule_id in hxc_time_entry_rules.time_entry_rule_id%TYPE
294 ,p_end_date in hxc_time_entry_rules.end_date%TYPE
295 ,p_start_date in hxc_time_entry_rules.start_date%TYPE
296 ,p_legislation_code VARCHAR2
297 ,p_bg_id NUMBER
298 ) IS
299 --
300 l_proc varchar2(72);
301 --
302 -- cursor to check name does not overlap
303 --
304 CURSOR csr_chk_name IS
305 SELECT 'error'
306 FROM hxc_time_entry_rules ter
307 WHERE
308 ter.name = p_name AND
309 ( ter.time_entry_rule_id <> p_time_entry_rule_id OR
310 p_time_entry_rule_id IS NULL )
311 AND (
312 ( ter.business_group_id IS NULL AND
313 ter.legislation_code IS NULL )
314 OR
315 ( ter.legislation_code IS NOT NULL AND
316 ter.legislation_code = p_legislation_code )
317 OR
318 ( ter.business_group_id IS NOT NULL AND
319 ter.business_group_id = NVL( p_bg_id, -1 ) )
320 OR
321 ( p_bg_id IS NULL ) )
322 AND (
323 (
324 ( p_start_date BETWEEN
325 ter.start_date AND NVL(ter.end_date, HR_GENERAL.END_OF_TIME )
326 )
327 OR
328 ( NVL(p_end_date, HR_GENERAL.END_OF_TIME ) BETWEEN
329 ter.start_date AND NVL(ter.end_date, HR_GENERAL.END_OF_TIME )
330 )
331 )
332 OR
333 ( ter.start_date BETWEEN
334 p_start_date AND NVL(p_end_date, HR_GENERAL.END_OF_TIME )
335 )
336 OR
337 ( NVL(ter.end_date, HR_GENERAL.END_OF_TIME ) BETWEEN
338 p_start_date AND NVL(p_end_date, HR_GENERAL.END_OF_TIME )
339 )
340
341 );
342 --
343 l_dup_name varchar2(5) := NULL;
344 --
345 BEGIN
346 g_debug := hr_utility.debug_enabled;
347
348 if g_debug then
349 l_proc := g_package||'chk_name';
350 hr_utility.set_location('Entering:'||l_proc, 5);
351 end if;
352 --
353 -- check that the name has been entered
354 --
355 IF p_name IS NULL
356 THEN
357 --
358 hr_utility.set_message(809, 'HXC_0037_DAR_NAME_MAND');
359 hr_utility.raise_error;
360 --
361 END IF;
362 if g_debug then
363 hr_utility.set_location('Processing:'||l_proc, 10);
364 end if;
365 --
366 -- check that the name is unique within the date range
367 --
368 OPEN csr_chk_name;
369 FETCH csr_chk_name INTO l_dup_name;
370 CLOSE csr_chk_name;
371 --
372 IF l_dup_name IS NOT NULL
373 THEN
374 --
375 hr_utility.set_message(809, 'HXC_0038_DAR_DUP_NAME');
376 hr_utility.raise_error;
377 --
378 END IF;
379 --
380 if g_debug then
381 hr_utility.set_location('Leaving:'||l_proc, 20);
382 end if;
383 END chk_name;
384 --
385 --
386 -- ----------------------------------------------------------------------------
387 -- |-----------------------< chk_rule_usage >---------------------------------|
388 -- ----------------------------------------------------------------------------
389 -- {Start Of Comments}
390 --
391 -- Description:
392 -- This procedure insures a valid rule usage
393 --
394 -- Pre Conditions:
395 -- None
396 --
397 -- In Arguments:
398 -- rule_usage
399 --
400 -- Post Success:
401 -- Processing continues if the rule usage is entered and is valid
402 --
403 -- Post Failure:
404 -- An application error is raised if the rule usage is not valid
405 --
406 -- {End Of Comments}
407 -- ----------------------------------------------------------------------------
408 Procedure chk_rule_usage
409 (
410 p_rule_usage in hxc_time_entry_rules.rule_usage%TYPE
411 ) IS
412 --
413 l_proc varchar2(72);
414 --
415 CURSOR csr_chk_rule_usage IS
416 SELECT 'ok'
417 FROM hr_lookups
418 WHERE lookup_type = 'HXC_APPROVAL_RULE_USAGE'
419 AND lookup_code = p_rule_usage;
420 --
421 l_valid_usage VARCHAR2(5) := NULL;
422 --
423 BEGIN
424 g_debug := hr_utility.debug_enabled;
425
426 if g_debug then
427 l_proc := g_package||'chk_rule_usage';
428 hr_utility.set_location('Entering:'||l_proc, 5);
429 end if;
430 --
431 -- check that usage_name has been entered
432 --
433 IF p_rule_usage IS NULL
434 THEN
435 --
436 hr_utility.set_message(809, 'HXC_0039_DAR_USAGE_MAND');
437 hr_utility.raise_error;
438 --
439 END IF;
440 if g_debug then
441 hr_utility.set_location('In:'||l_proc, 10);
442 end if;
443 --
444 -- check if the rule_usage name exists in HR_LOOKUPS
445 --
446 OPEN csr_chk_rule_usage;
447 FETCH csr_chk_rule_usage INTO l_valid_usage;
448 CLOSE csr_chk_rule_usage;
449 --
450 if g_debug then
451 hr_utility.set_location('Processing:'||l_proc, 20);
452 end if;
453 --
454 IF l_valid_usage IS NULL
455 THEN
456 --
457 hr_utility.set_message(809, 'HXC_0040_DAR_USAGE_INVLD');
458 hr_utility.raise_error;
459 --
460 END IF;
461 --
462 if g_debug then
463 hr_utility.set_location('Leaving:'||l_proc, 30);
464 end if;
465 END chk_rule_usage;
466 --
467 -- ----------------------------------------------------------------------------
468 -- |-----------------------< chk_mapping_id >---------------------------------|
469 -- ----------------------------------------------------------------------------
470 -- {Start Of Comments}
471 --
472 -- Description:
473 -- This procedure insures a valid mapping_id
474 --
475 -- Pre Conditions:
476 -- None
477 --
478 -- In Arguments:
479 -- mapping_id
480 --
481 -- Post Success:
482 -- Processing continues if the mapping_id is valid
483 --
484 -- Post Failure:
485 -- An application error is raised if the mapping_id is not valid
486 --
487 -- {End Of Comments}
488 -- ----------------------------------------------------------------------------
489 Procedure chk_mapping_id
490 (
491 p_mapping_id in hxc_time_entry_rules.mapping_id%TYPE
492 ) IS
493 --
494 l_proc varchar2(72) := g_package||'chk_mapping_id';
495 --
496 CURSOR csr_chk_mapping_id IS
497 SELECT 'ok'
498 FROM dual
499 WHERE EXISTS (
500 SELECT 'x'
501 FROM hxc_mappings
502 WHERE mapping_id = p_mapping_id );
503 --
504 l_mapping_ok varchar2(2) := NULL;
505 --
506 BEGIN
507 --
508 IF p_mapping_id IS NOT NULL
509 THEN
510 --
511 -- check mapping id exists
512 --
513 OPEN csr_chk_mapping_id;
514 FETCH csr_chk_mapping_id INTO l_mapping_ok;
515 CLOSE csr_chk_mapping_id;
516 --
517 IF l_mapping_ok IS NULL
518 THEN
519 --
520 hr_utility.set_message(809, 'HXC_0041_DAR_MAPPING_INVLD');
521 hr_utility.raise_error;
522 --
523 END IF;
524 --
525 END IF;
526 --
527 END chk_mapping_id;
528 --
529 -- ----------------------------------------------------------------------------
530 -- |-----------------------< chk_formula_id >---------------------------------|
531 -- ----------------------------------------------------------------------------
532 -- {Start Of Comments}
533 --
534 -- Description:
535 -- This procedure insures a valid formula_id
536 --
537 -- Pre Conditions:
538 -- None
539 --
540 -- In Arguments:
541 -- formula_id
542 -- start_date
543 -- end_date
544 --
545 -- Post Success:
546 -- Processing continues if the formual_id is valid
547 --
548 -- Post Failure:
549 -- An application error is raised if the formula_id is not valid
550 --
551 -- {End Of Comments}
552 -- ----------------------------------------------------------------------------
553 Procedure chk_formula_id
554 (
555 p_formula_id in hxc_time_entry_rules.formula_id%TYPE
556 ,p_start_date in hxc_time_entry_rules.start_date%TYPE
557 ,p_end_date in hxc_time_entry_rules.end_date%TYPE
558 ) IS
559 --
560 l_proc varchar2(72) := g_package||'chk_formula_id';
561 --
562 CURSOR csr_chk_formula_id IS
563 SELECT 'ok'
564 FROM dual
565 WHERE EXISTS (
566 SELECT 'x'
567 FROM ff_formulas_f
568 WHERE formula_id = p_formula_id
569 AND effective_start_date <= p_start_date
570 AND effective_end_date >= p_end_date );
571 --
572 l_formula_ok varchar2(2) := NULL;
573 --
574 BEGIN
575 --
576 IF p_formula_id IS NOT NULL
577 THEN
578 --
579 -- check formula valid over range of approval rule
580 --
581 OPEN csr_chk_formula_id;
582 FETCH csr_chk_formula_id INTO l_formula_ok;
583 CLOSE csr_chk_formula_id;
584 --
585 IF l_formula_ok IS NULL
586 THEN
587 --
588 hr_utility.set_message(809, 'HXC_0042_DAR_FF_DATE_INVLD');
589 hr_utility.raise_error;
590 --
591 END IF;
592 --
593 END IF;
594 --
595 END chk_formula_id;
596 --
597 -- ----------------------------------------------------------------------------
598 -- |-----------------------< chk_mapping_formula>-----------------------------|
599 -- ----------------------------------------------------------------------------
600 -- {Start Of Comments}
601 --
602 -- Description:
603 -- This procedure insures that at least one of mapping id or formula id are
604 -- entered
605 --
606 -- Pre Conditions:
607 -- None
608 --
609 -- In Arguments:
610 -- formula_id
611 -- mapping_id
612 --
613 -- Post Success:
614 -- Processing continues if the formula_id or mapping id is entered
615 --
616 -- Post Failure:
617 -- An application error is raised if the formula_id or mapping is are not entered
618 --
619 -- {End Of Comments}
620 -- ----------------------------------------------------------------------------
621 Procedure chk_formula_mapping
622 (
623 p_formula_id in hxc_time_entry_rules.formula_id%TYPE
624 , p_mapping_id in hxc_time_entry_rules.mapping_id%TYPE
625 ) IS
626
627 l_proc varchar2(72) := g_package||'chk_formula_mapping';
628
629 BEGIN
630
631 IF ( p_formula_id IS NULL AND p_mapping_id IS NULL )
632 THEN
633
634 hr_utility.set_message(809, 'HXC_0043_DAR_MAP_OR_FF_MAND');
635 hr_utility.raise_error;
636
637 END IF;
638
639 END chk_formula_mapping;
640 --
641 -- ----------------------------------------------------------------------------
642 -- |-----------------------< chk_start_date >----------------------------------|
643 -- ----------------------------------------------------------------------------
644 -- doc for this procedure is located in the header
645 -- ----------------------------------------------------------------------------
646 Procedure chk_start_date
647 (
648 p_name in hxc_time_entry_rules.name%TYPE
649 ,p_start_date in hxc_time_entry_rules.start_date%TYPE
650 ,p_end_date in hxc_time_entry_rules.end_date%TYPE
651 ,p_ovn in hxc_time_entry_rules.object_version_number%TYPE
652 ,p_bg_id NUMBER
653 ,p_legislation_code VARCHAR2
654 ) IS
655 --
656 l_proc varchar2(72);
657 --
658 -- cursor to check start date does not overlap
659 --
660 CURSOR csr_chk_start_date IS
661 SELECT 'error'
662 FROM hxc_time_entry_rules ter
663 WHERE
664 ter.name = p_name AND
665 ter.object_version_number <> NVL(p_ovn, -1)
666 AND (
667 ( ter.business_group_id IS NULL AND
668 ter.legislation_code IS NULL )
669 OR
670 ( ter.legislation_code IS NOT NULL AND
671 ter.legislation_code = p_legislation_code )
672 OR
673 ( ter.business_group_id IS NOT NULL AND
674 ter.business_group_id = NVL( p_bg_id, -1 ) )
675 OR
676 ( p_bg_id IS NULL ) )
677 AND
678 p_start_date BETWEEN
679 ter.start_date AND NVL(ter.end_date, HR_GENERAL.END_OF_TIME );
680 --
681 l_bad_start_date varchar2(5) := NULL;
682 --
683 BEGIN
684 g_debug := hr_utility.debug_enabled;
685
686 if g_debug then
687 l_proc := g_package||'chk_start_date';
688 hr_utility.set_location('Entering:'||l_proc, 5);
689 end if;
690 --
691 -- check that date from has been entered
692 --
693 IF p_start_date IS NULL
694 THEN
695 --
696 hr_utility.set_message(809, 'HXC_0056_DAR_START_DATE_MAND');
697 hr_utility.raise_error;
698 --
699 END IF;
700 --
701 if g_debug then
702 hr_utility.set_location('Processing: '||l_proc, 10);
703 end if;
704 --
705 -- check that date from is not greater than end_date
706 --
707 IF p_start_date > NVL(p_end_date, HR_GENERAL.END_OF_TIME )
708 THEN
709 --
710 hr_utility.set_message(809, 'HXC_0057_DAR_FROM_MORE_THAN_TO');
711 hr_utility.raise_error;
712 --
713 END IF;
714 --
715 if g_debug then
716 hr_utility.set_location('Processing: '||l_proc, 20);
717 end if;
718 --
719 -- check that this start date does not overlap with rule of the same name
720 --
721 OPEN csr_chk_start_date;
722 FETCH csr_chk_start_date INTO l_bad_start_date;
723 CLOSE csr_chk_start_date;
724 --
725 if g_debug then
726 hr_utility.set_location('Processing: '||l_proc, 30);
727 end if;
728 --
729 IF l_bad_start_date IS NOT NULL
730 THEN
731 --
732 hr_utility.set_message(809, 'HXC_0058_DAR_FROM_OVERLAPS');
733 hr_utility.raise_error;
734 --
735 END IF;
736 --
737 if g_debug then
738 hr_utility.set_location('Leaving: '||l_proc, 40);
739 end if;
740 --
741 END chk_start_date;
742 --
743 -- ----------------------------------------------------------------------------
744 -- |-----------------------< chk_end_date >------------------------------------|
745 -- ----------------------------------------------------------------------------
746 -- doc for this procedure is located in the header
747 -- ----------------------------------------------------------------------------
748 Procedure chk_end_date
749 (
750 p_name in hxc_time_entry_rules.name%TYPE
751 ,p_start_date in hxc_time_entry_rules.start_date%TYPE
752 ,p_end_date in hxc_time_entry_rules.end_date%TYPE
753 ,p_ovn in hxc_time_entry_rules.object_version_number%TYPE
754 ,p_bg_id NUMBER
755 ,p_legislation_code VARCHAR2
756 ) IS
757 --
758 l_proc varchar2(72);
759 --
760 -- cursor to check start date does not overlap
761 --
762 CURSOR csr_chk_end_date IS
763 SELECT 'error'
764 FROM hxc_time_entry_rules ter
765 WHERE
766 ter.name = p_name AND
767 ter.object_version_number <> NVL(p_ovn, -1)
768 AND (
769 ( ter.business_group_id IS NULL AND
770 ter.legislation_code IS NULL )
771 OR
772 ( ter.legislation_code IS NOT NULL AND
773 ter.legislation_code = p_legislation_code )
774 OR
775 ( ter.business_group_id IS NOT NULL AND
776 ter.business_group_id = NVL( p_bg_id, -1 ) )
777 OR
778 ( p_bg_id IS NULL ) )
779 AND
780 p_end_date BETWEEN
781 ter.start_date AND NVL(ter.end_date, HR_GENERAL.END_OF_TIME );
782 --
783 l_bad_end_date varchar2(5) := NULL;
784 --
785 BEGIN
786 g_debug := hr_utility.debug_enabled;
787
788 if g_debug then
789 l_proc := g_package||'chk_end_date';
790 hr_utility.set_location('Entering:'||l_proc, 5);
791 end if;
792 --
793 -- check that date to is not less than date from
794 --
795 IF NVL(p_end_date, HR_GENERAL.END_OF_TIME ) < p_start_date
796 THEN
797 --
798 hr_utility.set_message(809, 'HXC_0059_DAR_TO_LESS_THAN_FROM');
799 hr_utility.raise_error;
800 --
801 END IF;
802 --
803 if g_debug then
804 hr_utility.set_location('Processing: '||l_proc, 10);
805 end if;
806 --
807 -- check that this end date does not overlap with rule of the same name
808 --
809 OPEN csr_chk_end_date;
810 FETCH csr_chk_end_date INTO l_bad_end_date;
811 CLOSE csr_chk_end_date;
812 --
813 if g_debug then
814 hr_utility.set_location('Processing: '||l_proc, 20);
815 end if;
816 --
817 IF l_bad_end_date IS NOT NULL
818 THEN
819 --
820 hr_utility.set_message(809, 'HXC_0060_DAR_TO_OVERLAPS');
821 hr_utility.raise_error;
822 --
823 END IF;
824 --
825 if g_debug then
826 hr_utility.set_location('Leaving: '||l_proc, 30);
827 end if;
828 --
829 END chk_end_date;
830 --
831 -- ----------------------------------------------------------------------------
832 -- |-----------------------< chk_delete >-------------------------------------|
833 -- ----------------------------------------------------------------------------
834 -- {Start Of Comments}
835 --
836 -- Description:
837 -- This procedure carries out delete time refential integrity checks
838 --
839 -- Pre Conditions:
840 -- None
841 --
842 -- In Arguments:
843 -- time_entry_rule__id
844 --
845 -- Post Success:
846 -- Processing continues if the name is not being referenced
847 --
848 -- Post Failure:
849 -- An application error is raised if the rule is being used.
850 --
851 -- {End Of Comments}
852 -- ----------------------------------------------------------------------------
853 Procedure chk_delete
854 (
855 p_time_entry_rule_id in hxc_time_entry_rules.time_entry_rule_id%TYPE
856 ) IS
857
858 l_proc varchar2(72);
859
860 CURSOR csr_chk_teru IS
861 SELECT 'exists'
862 FROM hxc_entity_group_comps egc
863 WHERE egc.entity_type = 'TIME_ENTRY_RULES'
864 AND egc.entity_id = p_time_entry_rule_id;
865
866 CURSOR csr_chk_daru IS
867 SELECT 'exists'
868 FROM hxc_data_app_rule_usages daru
869 WHERE daru.time_entry_rule_id = p_time_entry_rule_id;
870
871
872 l_exists VARCHAR2(6) := NULL;
873
874 BEGIN
875 g_debug := hr_utility.debug_enabled;
876
877 if g_debug then
878 l_proc := g_package||'chk_delete';
879 hr_utility.set_location('Entering:'||l_proc, 5);
880 end if;
881
882 -- check that approval rule is not being used in any
883 -- Time Entry Rule or ELP Entry Rule Group
884
885 OPEN csr_chk_teru;
886 FETCH csr_chk_teru INTO l_exists;
887 CLOSE csr_chk_teru;
888
889 if g_debug then
890 hr_utility.set_location('Processing: '||l_proc, 10);
891 end if;
892
893 IF l_exists IS NOT NULL
894 THEN
895
896 hr_utility.set_message(809, 'HXC_0061_DAR_RULE_IN_USE');
897 hr_utility.raise_error;
898
899 END IF;
900
901 -- check that the time entry rule is not being used in
902 -- an approval style Date Interdepency Rule
903
904 OPEN csr_chk_daru;
905 FETCH csr_chk_daru INTO l_exists;
906 CLOSE csr_chk_daru;
907
908 if g_debug then
909 hr_utility.set_location('Processing: '||l_proc, 20);
910 end if;
911
912 IF l_exists IS NOT NULL
913 THEN
914
915 hr_utility.set_message(809, 'HXC_TER_REF_APPROVAL');
916 hr_utility.raise_error;
917
918 END IF;
919
920
921
922 if g_debug then
923 hr_utility.set_location('Leaving: '||l_proc, 30);
924 end if;
925
926 END chk_delete;
927 --
928 --
929 -- -----------------------------------------------------------------
930 -- |-----------------------< chk_legislation_code >----------------|
931 -- -----------------------------------------------------------------
932 --
933 -- Description:
934 -- Validate the legislation_code against the FND_TERRITORIES table.
935 --
936 -- Pre-Requisites:
937 -- None
938 --
939 -- In Parameters:
940 -- p_legislation_code
941 --
942 -- Post Success:
943 -- Processing continues if the legislation_code is valid.
944 --
945 -- Post Failure:
946 -- An application error is raised and processing is terminated if
947 -- the legislation_code is invalid.
948 --
949 -- Access Status:
950 -- Internal Row Handler Use Only.
951 --
952 Procedure chk_legislation_code
953 (p_business_group_id in number,
954 p_legislation_code in varchar2
955 ) is
956 --
957 -- Local declarations
958 l_proc varchar2(72);
959 l_territory_code fnd_territories.territory_code%TYPE;
960 l_lc per_business_groups.legislation_code%TYPE;
961 --
962 -- Setup cursor for valid legislation code check
963 cursor csr_valid_legislation_code is
964 select territory_code
965 from fnd_territories ft
966 where ft.territory_code = p_legislation_code;
967
968 -- Setup cursor for valid legislation code for a particular business_group
969 cursor csr_valid_bg_lc is
970 select legislation_code
971 from per_business_groups pbg
972 where pbg.business_group_id = p_business_group_id
973 and pbg.legislation_code = p_legislation_code;
974
975 --
976 --
977 begin
978 g_debug := hr_utility.debug_enabled;
979
980 if g_debug then
981 l_proc := g_package||'chk_legislation_code';
982 hr_utility.set_location('Entering: '||l_proc,5);
983 end if;
984 --------------------------------
985 -- Check legislation code is valid --
986 --------------------------------
987 if p_legislation_code is not null then
988
989 open csr_valid_legislation_code;
990 fetch csr_valid_legislation_code into l_territory_code;
991 if csr_valid_legislation_code%notfound then
992 close csr_valid_legislation_code;
993 hr_utility.set_message(800,'PER_52123_AMD_LEG_CODE_INV');
994 hr_utility.raise_error;
995 end if; -- End cursor if
996 close csr_valid_legislation_code;
997
998 if p_business_group_id is not null then
999 open csr_valid_bg_lc;
1000 fetch csr_valid_bg_lc into l_lc;
1001 if csr_valid_bg_lc%notfound then
1002 close csr_valid_bg_lc;
1003 hr_utility.set_message(800,'PER_52123_AMD_LEG_CODE_INV');
1004 hr_utility.raise_error;
1005 end if; -- End cursor if
1006 close csr_valid_bg_lc;
1007 end if;
1008
1009 end if; -- end check
1010
1011 if g_debug then
1012 hr_utility.set_location('Leaving: '||l_proc,10);
1013 end if;
1014 end chk_legislation_code;
1015 --
1016 -- ----------------------------------------------------------------------------
1017 -- |---------------------------< insert_validate >----------------------------|
1018 -- ----------------------------------------------------------------------------
1019 Procedure insert_validate
1020 (p_effective_date in date
1021 ,p_rec in hxc_ter_shd.g_rec_type
1022 ) is
1023 --
1024 l_proc varchar2(72);
1025 --
1026 Begin
1027 g_debug := hr_utility.debug_enabled;
1028
1029 if g_debug then
1030 l_proc := g_package||' insert_validate';
1031 hr_utility.set_location('Entering:'||l_proc, 5);
1032 end if;
1033 --
1034 -- Call all supporting business operations
1035 --
1036 chk_name (
1037 p_name => p_rec.name
1038 ,p_time_entry_rule_id => p_rec.time_entry_rule_id
1039 ,p_end_date => p_rec.end_date
1040 ,p_start_date => p_rec.start_date
1041 ,p_legislation_code=> p_rec.legislation_code
1042 ,p_bg_id => p_rec.business_group_id
1043 );
1044 --
1045 if g_debug then
1046 hr_utility.set_location(' Processing: '||l_proc, 10);
1047 end if;
1048 --
1049 chk_rule_usage
1050 (
1051 p_rule_usage => p_rec.rule_usage
1052 );
1053 --
1054 if g_debug then
1055 hr_utility.set_location(' Processing: '||l_proc, 30);
1056 end if;
1057 --
1058 chk_mapping_id
1059 (
1060 p_mapping_id => p_rec.mapping_id
1061 );
1062 --
1063 if g_debug then
1064 hr_utility.set_location(' Processing: '||l_proc, 40);
1065 end if;
1066 --
1067 chk_formula_id
1068 (
1069 p_formula_id => p_rec.formula_id
1070 ,p_start_date => p_rec.start_date
1071 ,p_end_date => p_rec.end_date );
1072 --
1073 if g_debug then
1074 hr_utility.set_location(' Processing: '||l_proc, 50);
1075 end if;
1076 --
1077 chk_formula_mapping
1078 (
1079 p_formula_id => p_rec.formula_id
1080 , p_mapping_id => p_rec.mapping_id );
1081 --
1082 if g_debug then
1083 hr_utility.set_location(' Processing: '||l_proc, 50);
1084 end if;
1085 chk_start_date
1086 (
1087 p_name => p_rec.name
1088 ,p_start_date => p_rec.start_date
1089 ,p_end_date => p_rec.end_date
1090 ,p_ovn => p_rec.object_version_number
1091 ,p_legislation_code=> p_rec.legislation_code
1092 ,p_bg_id => p_rec.business_group_id
1093 );
1094 --
1095 if g_debug then
1096 hr_utility.set_location(' Processing: '||l_proc, 50);
1097 end if;
1098 chk_end_date
1099 (
1100 p_name => p_rec.name
1101 ,p_start_date => p_rec.start_date
1102 ,p_end_date => p_rec.end_date
1103 ,p_ovn => p_rec.object_version_number
1104 ,p_legislation_code=> p_rec.legislation_code
1105 ,p_bg_id => p_rec.business_group_id
1106 );
1107 --
1108 if p_rec.business_group_id is not null then
1109 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
1110 end if;
1111 -- Validate the legislation_code
1112 chk_legislation_code
1113 (p_business_group_id => p_rec.business_group_id,
1114 p_legislation_code => p_rec.legislation_code);
1115 --
1116 hxc_ter_bus.chk_df(p_rec);
1117
1118 if g_debug then
1119 hr_utility.set_location(' Leaving: '||l_proc, 60);
1120 end if;
1121 --
1122 End insert_validate;
1123 --
1124 -- ----------------------------------------------------------------------------
1125 -- |---------------------------< update_validate >----------------------------|
1126 -- ----------------------------------------------------------------------------
1127 Procedure update_validate
1128 (p_effective_date in date
1129 ,p_rec in hxc_ter_shd.g_rec_type
1130 ) is
1131 --
1132 l_proc varchar2(72);
1133 --
1134 Begin
1135 g_debug := hr_utility.debug_enabled;
1136
1137 if g_debug then
1138 l_proc := g_package||'update_validate';
1139 hr_utility.set_location('Entering:'||l_proc, 5);
1140 end if;
1141 --
1142 -- Call all supporting business operations
1143 --
1144 --
1145 chk_non_updateable_args
1146 (p_effective_date => p_effective_date
1147 ,p_rec => p_rec
1148 );
1149 --
1150 --
1151 if g_debug then
1152 hr_utility.set_location(' Processing: '||l_proc, 10);
1153 end if;
1154 --
1155 --
1156
1157 --
1158 chk_name (
1159 p_name => p_rec.name
1160 ,p_time_entry_rule_id => p_rec.time_entry_rule_id
1161 ,p_end_date => p_rec.end_date
1162 ,p_start_date => p_rec.start_date
1163 ,p_legislation_code=> p_rec.legislation_code
1164 ,p_bg_id => p_rec.business_group_id
1165 );
1166 --
1167 if g_debug then
1168 hr_utility.set_location(' Processing: '||l_proc, 20);
1169 end if;
1170 --
1171 chk_rule_usage
1172 (
1173 p_rule_usage => p_rec.rule_usage
1174 );
1175 --
1176 if g_debug then
1177 hr_utility.set_location(' Processing: '||l_proc, 30);
1178 end if;
1179 --
1180 chk_mapping_id
1181 (
1182 p_mapping_id => p_rec.mapping_id
1183 );
1184 --
1185 if g_debug then
1186 hr_utility.set_location(' Processing: '||l_proc, 40);
1187 end if;
1188 chk_formula_id
1189 (
1190 p_formula_id => p_rec.formula_id
1191 ,p_start_date => p_rec.start_date
1192 ,p_end_date => p_rec.end_date );
1193 --
1194 chk_formula_mapping
1195 (
1196 p_formula_id => p_rec.formula_id
1197 , p_mapping_id => p_rec.mapping_id );
1198 --
1199 if g_debug then
1200 hr_utility.set_location(' Processing: '||l_proc, 50);
1201 end if;
1202 chk_start_date
1203 (
1204 p_name => p_rec.name
1205 ,p_start_date => p_rec.start_date
1206 ,p_end_date => p_rec.end_date
1207 ,p_ovn => p_rec.object_version_number
1208 ,p_legislation_code=> p_rec.legislation_code
1209 ,p_bg_id => p_rec.business_group_id
1210 );
1211 --
1212 chk_end_date
1213 (
1214 p_name => p_rec.name
1215 ,p_start_date => p_rec.start_date
1216 ,p_end_date => p_rec.end_date
1217 ,p_ovn => p_rec.object_version_number
1218 ,p_legislation_code=> p_rec.legislation_code
1219 ,p_bg_id => p_rec.business_group_id
1220 );
1221 --
1222 --
1223 if p_rec.business_group_id is not null then
1224 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
1225 end if;
1226 -- Validate the legislation_code
1227 chk_legislation_code
1228 (p_business_group_id => p_rec.business_group_id,
1229 p_legislation_code => p_rec.legislation_code);
1230 --
1231 hxc_ter_bus.chk_df(p_rec);
1232 --
1233 if g_debug then
1234 hr_utility.set_location(' Leaving:'||l_proc, 60);
1235 end if;
1236 End update_validate;
1237 --
1238 -- ----------------------------------------------------------------------------
1239 -- |---------------------------< delete_validate >----------------------------|
1240 -- ----------------------------------------------------------------------------
1241 Procedure delete_validate
1242 (p_rec in hxc_ter_shd.g_rec_type
1243 ) is
1244 --
1245 l_proc varchar2(72);
1246 --
1247 Begin
1248 g_debug := hr_utility.debug_enabled;
1249
1250 if g_debug then
1251 l_proc := g_package||'delete_validate';
1252 hr_utility.set_location('Entering:'||l_proc, 5);
1253 end if;
1254 --
1255 -- Call all supporting business operations
1256 --
1257 chk_delete
1258 (
1259 p_time_entry_rule_id => p_rec.time_entry_rule_id
1260 );
1261 --
1262 if g_debug then
1263 hr_utility.set_location(' Leaving:'||l_proc, 10);
1264 end if;
1265 End delete_validate;
1266 --
1267 end hxc_ter_bus;