[Home] [Help]
PACKAGE BODY: APPS.HR_CAU_BUS
Source
1 Package Body hr_cau_bus as
2 /* $Header: hrcaurhi.pkb 120.0 2005/05/29 02:29:58 appldev noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' hr_cau_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |---------------------------< chk_start_date >-----------------------------|
12 -- ----------------------------------------------------------------------------
13 procedure chk_start_date (
14 --
15 p_start_date in date,
16 p_calendar_id in number,
17 p_calendar_usage_id in number default null,
18 p_object_version_number in number default null
19 ) is
20 --
21 cursor csr_calendar is
22 --
23 -- Check the usage start/end dates
24 --
25 select calendar_start_time
26 from hr_calendars
27 where calendar_id = p_calendar_id;
28 --
29 l_proc varchar2(72) := g_package||'chk_start_date';
30 l_calendar_start_time date := null;
31 l_api_updating boolean := FALSE;
32 --
33 procedure check_parameters is
34 --
35 begin
36 --
37 hr_api.mandatory_arg_error
38 (p_api_name => l_proc,
39 p_argument => 'calendar_id',
40 p_argument_value => p_calendar_id);
41 --
42 hr_api.mandatory_arg_error
43 (p_api_name => l_proc,
44 p_argument => 'start_date',
45 p_argument_value => p_start_date);
46 --
47 end check_parameters;
48 --
49 begin
50 --
51 hr_utility.set_location ('Entering '||l_proc,1);
52 --
53 check_parameters;
54 --
55 l_api_updating := hr_cau_shd.api_updating (
56 --
57 p_calendar_usage_id => p_calendar_usage_id,
58 p_object_version_number => p_object_version_number);
59 --
60 if ((l_api_updating and p_start_date <> hr_cau_shd.g_old_rec.end_date)
61 or (not l_api_updating))
62 then
63 --
64 open csr_calendar;
65 fetch csr_calendar into l_calendar_start_time;
66 --
67 if csr_calendar%notfound then
68 --
69 -- There was no calendar identified by the ID
70 --
71 hr_utility.set_message(801, 'HR_51068_CAU_CAL_FK');
72 hr_utility.raise_error;
73 --
74 elsif l_calendar_start_time > p_start_date then
75 --
76 -- The usage starts before its calendar
77 --
78 hr_utility.set_message (801,'HR_51038_CAU_INVALID_START');
79 hr_utility.set_message_token ('CALENDAR_START_DATE',
80 to_char (l_calendar_start_time,
81 'DD-MON-YYYY HH24:MI'));
82 hr_utility.raise_error;
83 --
84 end if;
85 --
86 end if;
87 --
88 hr_utility.set_location ('Leaving '||l_proc, 10);
89 --
90 end chk_start_date;
91 -- ----------------------------------------------------------------------------
92 -- |---------------------------< chk_entity_purpose >-------------------------|
93 -- ----------------------------------------------------------------------------
94 procedure chk_entity_purpose (
95 --
96 p_purpose_usage_id in number,
97 p_primary_key_value in number,
98 p_start_date in date,
99 p_end_date in date,
100 p_calendar_usage_id in number default null) is
101 --
102 l_proc varchar2(72) := g_package||'chk_entity_purpose';
103 l_dummy integer (1) := null;
104 --
105 cursor csr_overlap is
106 --
107 -- Check for overlapping calendar use
108 --
109 select 1
110 from hr_calendar_usages
111 where purpose_usage_id = p_purpose_usage_id
112 and (p_calendar_usage_id is null
113 or p_calendar_usage_id <> calendar_usage_id)
114 and primary_key_value = p_primary_key_value
115 and p_start_date <= end_date
116 and p_end_date >= start_date;
117 --
118 procedure check_parameters is
119 --
120 begin
121 --
122 hr_api.mandatory_arg_error
123 (p_api_name => l_proc,
124 p_argument => 'purpose_usage_id',
125 p_argument_value => p_purpose_usage_id);
126 --
127 hr_api.mandatory_arg_error
128 (p_api_name => l_proc,
129 p_argument => 'primary_key_value',
130 p_argument_value => p_primary_key_value);
131 --
132 hr_api.mandatory_arg_error
133 (p_api_name => l_proc,
134 p_argument => 'start_date',
135 p_argument_value => p_start_date);
136 --
137 hr_api.mandatory_arg_error
138 (p_api_name => l_proc,
139 p_argument => 'end_date',
140 p_argument_value => p_end_date);
141 --
142 end check_parameters;
143 --
144 begin
145 --
146 hr_utility.set_location ('Entering '||l_proc,1);
147 --
148 check_parameters;
149 --
150 open csr_overlap;
151 fetch csr_overlap into l_dummy;
152 --
153 if csr_overlap%found then
154 --
155 close csr_overlap;
156 hr_utility.set_message (801, 'HR_51072_CAU_USAGE_OVERLAP');
157 hr_utility.raise_error;
158 --
159 else
160 close csr_overlap;
161 end if;
162 --
163 hr_utility.set_location ('Leaving '||l_proc,1);
164 --
165 end chk_entity_purpose;
166 -- ----------------------------------------------------------------------------
167 -- |---------------------------< chk_purpose_usage_id >-----------------------|
168 -- ----------------------------------------------------------------------------
169 procedure chk_purpose_usage_id (
170 --
171 p_purpose_usage_id in number,
172 p_calendar_id in number) is
173 --
174 l_proc varchar2(72) := g_package||'chk_purpose_usage_id';
175 l_purpose varchar2 (30) := null;
176 l_dummy integer (1) := null;
177 --
178 cursor csr_pattern_construction is
179 --
180 -- Check the pattern construction for any bit with an availability not
181 -- equal to 'QUALIFYING' or 'NON QUALIFYING'. This is an SSP-specific
182 -- test.
183 --
184 select 1
185 from hr_pattern_constructions CON,
186 hr_pattern_constructions CON2,
187 hr_calendars CAL
188 where cal.calendar_id = p_calendar_id
189 and cal.pattern_id = con.pattern_id
190 and con.component_pattern_id = con2.pattern_id (+)
191 and (nvl (con.availability,con2.availability)
192 not in ('QUALIFYING', 'NON QUALIFYING'));
193 --
194 cursor csr_purpose is
195 --
196 -- Get the purpose of the calendar pattern
197 --
198 select pattern_purpose
199 from hr_pattern_purpose_usages
200 where purpose_usage_id = p_purpose_usage_id;
201 --
202 procedure check_parameters is
203 --
204 begin
205 --
206 hr_api.mandatory_arg_error
207 (p_api_name => l_proc,
208 p_argument => 'purpose_usage_id',
209 p_argument_value => p_purpose_usage_id);
210 --
211 hr_api.mandatory_arg_error
212 (p_api_name => l_proc,
213 p_argument => 'calendar_id',
214 p_argument_value => p_calendar_id);
215 --
216 end check_parameters;
217 --
218 begin
219 --
220 hr_utility.set_location ('Entering '||l_proc,1);
221 --
222 check_parameters;
223 --
224 open csr_purpose;
225 fetch csr_purpose into l_purpose;
226 if csr_purpose%notfound then
227 --
228 -- The purpose usage id was invalid.
229 hr_cau_shd.constraint_error ('HR_CAU_PPU_FK');
230 --
231 end if;
232 --
233 if l_purpose = 'QUALIFYING PATTERN' then
234 --
235 open csr_pattern_construction;
236 fetch csr_pattern_construction into l_dummy;
237 if csr_pattern_construction%found then
238 --
239 -- A pattern construction row was found which is not appropriate for SSP
240 --
241 hr_utility.set_message (801,'HR_51071_CAU_SSP_AVAILABILITY');
242 hr_utility.raise_error;
243 --
244 end if;
245 --
246 end if;
247 --
248 hr_utility.set_location ('Leaving '||l_proc,10);
249 --
250 end chk_purpose_usage_id;
251 -- ----------------------------------------------------------------------------
252 -- |---------------------------< chk_calendar_id >----------------------------|
253 -- ----------------------------------------------------------------------------
254 procedure chk_calendar_id (
255 --
256 p_calendar_id in out nocopy number,
257 p_start_date in date,
258 p_purpose_usage_id in number) is
259 --
260 l_proc varchar2(72) := g_package||'chk_calendar_id';
261 --
262 cursor csr_hierarchy is
263 --
264 -- Get the next level up the hierarchy of defaulting
265 --
266 select cau.purpose_usage_id,
267 cau.primary_key_value
268 from hr_pattern_purpose_usages PPU1,
269 hr_calendar_usages CAU
270 where ppu1.pattern_purpose = (
271 select ppu2.pattern_purpose
272 from hr_pattern_purpose_usages PPU2
273 where ppu2.purpose_usage_id = p_purpose_usage_id
274 and ppu1.hierarchy_level < ppu2.hierarchy_level)
275 and cau.purpose_usage_id = ppu1.purpose_usage_id
276 and p_start_date between cau.start_date and cau.end_date
277 order by ppu1.hierarchy_level desc;
278 --
279 cursor csr_default (
280 --
281 -- Get the default calendar
282 --
283 p_primary_key_value in number,
284 p_purpose_usage_id in number,
285 p_start_date in date) is
286 --
287 select calendar_id
288 from hr_calendar_usages
289 where primary_key_value = p_primary_key_value
290 and purpose_usage_id = p_purpose_usage_id
291 and p_start_date between start_date and end_date;
292 --
293 procedure check_parameters is
294 --
295 begin
296 --
297 hr_api.mandatory_arg_error
298 (p_api_name => l_proc,
299 p_argument => 'purpose_usage_id',
300 p_argument_value => p_purpose_usage_id);
301 --
302 hr_api.mandatory_arg_error
303 (p_api_name => l_proc,
304 p_argument => 'start_date',
305 p_argument_value => p_start_date);
306 --
307 end check_parameters;
308 --
309 begin
310 --
311 hr_utility.set_location ('Entering '||l_proc,1);
312 --
313 check_parameters;
314 --
315 if p_calendar_id is null then
316 --
317 -- We must default the calendar_id from the hierarchy
318 --
319 for higher_level in csr_hierarchy LOOP
320 --
321 -- Climb up the hierarchy, looking for a valid calendar_id to use as a
322 -- default.
323 --
324 open csr_default (higher_level.primary_key_value,
325 higher_level.purpose_usage_id,
326 p_start_date);
327 --
328 fetch csr_default into p_calendar_id;
329 --
330 if csr_default%found then
331 --
332 -- We have found a valid calendar from higher in the hierarchy
333 -- so stop looking.
334 --
335 close csr_default;
336 exit;
337 --
338 end if;
339 --
340 end loop;
341 --
342 end if;
343 --
344 -- If the calendar_id is still null at this point then no valid default
345 -- existed for the date specified, but we can leave the error to the constraint.
346 --
347 hr_utility.set_location ('Leaving '||l_proc,10);
348 --
349 end chk_calendar_id;
350 -- ----------------------------------------------------------------------------
351 -- |---------------------------< chk_primary_key_value >----------------------|
352 -- ----------------------------------------------------------------------------
353 procedure chk_primary_key_value (
354 --
355 p_purpose_usage_id in number,
356 p_primary_key_value in number,
357 p_start_date in date,
358 p_end_date in date) is
359 --
360 cursor csr_person is
361 --
362 -- Check for a valid person id
363 --
364 select 1
365 from per_all_people_f
366 where person_id = p_primary_key_value
367 and p_start_date between effective_start_date
368 and effective_end_date;
369 --
370 cursor csr_assignment is
371 --
372 -- Check for a valid assignment id
373 --
374 select 1
375 from per_assignments_f
376 where assignment_id = p_primary_key_value
377 and p_start_date between effective_start_date
378 and effective_end_date;
379 --
380 cursor csr_location is
381 --
382 -- Check for a valid location id
383 --
384 select 1
385 from hr_locations
386 where location_id = p_primary_key_value
387 and p_start_date < nvl (inactive_date, hr_general.end_of_time);
388 --
389 cursor csr_job is
390 --
391 -- Check for a valid job id
392 --
393 select 1
394 from per_jobs
395 where job_id = p_primary_key_value
396 and p_start_date between date_from
397 and nvl (date_to, hr_general.end_of_time);
398 --
399 cursor csr_organization is
400 --
401 -- Check for a valid organization id
402 --
403 select 1
404 from per_business_groups_perf
405 where business_group_id = p_primary_key_value
406 and p_start_date between date_from
407 and nvl (date_to, hr_general.end_of_time);
408 --
409 cursor csr_position is
410 --
411 -- Check for a valid position id
412 -- Changed 12-Oct-99 SCNair (per_positions to hr_positions) dt track position req.
413 --
414 select 1
415 from hr_positions_f
416 where position_id = p_primary_key_value
417 and p_start_date between date_effective
418 and nvl (hr_general.get_position_date_end(position_id), hr_general.end_of_time);
419 --
420 cursor csr_entity_name is
421 --
422 -- Get the entity name for the pattern purpose usage
423 --
424 select entity_name
425 from hr_pattern_purpose_usages
426 where purpose_usage_id = p_purpose_usage_id;
427 --
428 l_dummy integer (1) := null;
429 l_entity_name varchar2 (30);
430 l_table_name varchar2 (30) := null;
431 l_primary_key_name varchar2 (30) := null;
432 l_proc varchar2(72) := g_package||'chk_primary_key_value';
433 --
434 procedure check_parameters is
435 --
436 begin
437 --
438 hr_api.mandatory_arg_error
439 (p_api_name => l_proc,
440 p_argument => 'purpose_usage_id',
441 p_argument_value => p_purpose_usage_id);
442 --
443 hr_api.mandatory_arg_error
444 (p_api_name => l_proc,
445 p_argument => 'primary_key_value',
446 p_argument_value => p_primary_key_value);
447 --
448 hr_api.mandatory_arg_error
449 (p_api_name => l_proc,
450 p_argument => 'start_date',
451 p_argument_value => p_start_date);
452 --
453 end check_parameters;
454 --
455 begin
456 --
457 hr_utility.set_location ('Entering '||l_proc, 1);
458 --
459 check_parameters;
460 --
461 open csr_entity_name;
462 fetch csr_entity_name into l_entity_name;
463 if csr_entity_name%notfound then
464 --
465 -- The p_purpose_usage_id must be invalid
466 --
467 close csr_entity_name;
468 hr_cau_shd.constraint_error ('HR_CAU_PPU_FK');
469 --
470 else
471 --
472 close csr_entity_name;
473 --
474 end if;
475 --
476 if l_entity_name = 'PERSON' then
477 --
478 open csr_person;
479 fetch csr_person into l_dummy;
480 close csr_person;
481 --
482 elsif l_entity_name = 'ASSIGNMENT' then
483 --
484 open csr_assignment;
485 fetch csr_assignment into l_dummy;
486 close csr_assignment;
487 --
488 elsif l_entity_name = 'ORGANIZATION' then
489 --
490 open csr_organization;
491 fetch csr_organization into l_dummy;
492 close csr_organization;
493 --
494 elsif l_entity_name = 'JOB' then
495 --
496 open csr_job;
497 fetch csr_job into l_dummy;
498 close csr_job;
499 --
500 elsif l_entity_name = 'POSITION' then
501 --
502 open csr_position;
503 fetch csr_position into l_dummy;
504 close csr_position;
505 --
506 elsif l_entity_name = 'LOCATION' then
507 --
508 open csr_location;
509 fetch csr_location into l_dummy;
510 close csr_location;
511 --
512 else
513 --
514 -- An unexpected error occurred; the entity name is invalid.
515 --
516 hr_utility.set_message (801,' HR_6153_ALL_PROCEDURE_FAIL');
517 hr_utility.set_message_token ('PROCEDURE',l_proc);
518 hr_utility.set_message_token ('STEP','1');
519 hr_utility.raise_error;
520 --
521 end if;
522 --
523 if l_dummy is null then
524 --
525 -- The primary key did not exist on its table.
526 --
527 hr_utility.set_message (801,'HR_51067_CAU_PRIMARY_KEY_VALUE');
528 hr_utility.set_message_token ('ENTITY_NAME',l_entity_name);
529 hr_utility.set_message_token ('START_DATE',to_char (p_start_date));
530 hr_utility.raise_error;
531 --
532 end if;
533 --
534 end chk_primary_key_value;
535 -- ----------------------------------------------------------------------------
536 -- |---------------------------< insert_validate >----------------------------|
537 -- ----------------------------------------------------------------------------
538 Procedure insert_validate(p_rec in out nocopy hr_cau_shd.g_rec_type) is
539 --
540 l_proc varchar2(72) := g_package||'insert_validate';
541 --
542 Begin
543 hr_utility.set_location('Entering:'||l_proc, 5);
544 --
545 -- Call all supporting business operations
546 --
547 chk_start_date ( p_start_date => p_rec.start_date,
548 p_calendar_id => p_rec.calendar_id);
549 --
550 chk_calendar_id ( p_calendar_id => p_rec.calendar_id,
551 p_start_date => p_rec.start_date,
552 p_purpose_usage_id => p_rec.purpose_usage_id);
553 --
554 chk_primary_key_value (p_purpose_usage_id=> p_rec.purpose_usage_id,
555 p_primary_key_value=> p_rec.primary_key_value,
556 p_start_date=> p_rec.start_date,
557 p_end_date=> p_rec.end_date);
558 --
559 chk_purpose_usage_id (p_purpose_usage_id=> p_rec.purpose_usage_id,
560 p_calendar_id=> p_rec.calendar_id);
561 --
562 chk_entity_purpose (p_purpose_usage_id=> p_rec.purpose_usage_id,
563 p_primary_key_value=> p_rec.primary_key_value,
564 p_start_date=> p_rec.start_date,
565 p_end_date=> p_rec.end_date);
566 --
567 hr_utility.set_location(' Leaving:'||l_proc, 10);
568 End insert_validate;
569 --
570 -- ----------------------------------------------------------------------------
571 -- |---------------------------< update_validate >----------------------------|
572 -- ----------------------------------------------------------------------------
573 Procedure update_validate(p_rec in hr_cau_shd.g_rec_type) is
574 --
575 l_proc varchar2(72) := g_package||'update_validate';
576 --
577 Begin
578 hr_utility.set_location('Entering:'||l_proc, 5);
579 --
580 -- Call all supporting business operations
581 --
582 chk_start_date ( p_start_date => p_rec.start_date,
583 p_calendar_id => p_rec.calendar_id,
584 p_calendar_usage_id => p_rec.calendar_usage_id,
585 p_object_version_number => p_rec.object_version_number);
586 --
587 chk_entity_purpose (p_purpose_usage_id=> p_rec.purpose_usage_id,
588 p_calendar_usage_id=> p_rec.calendar_usage_id,
589 p_primary_key_value=> p_rec.primary_key_value,
590 p_start_date=> p_rec.start_date,
591 p_end_date=> p_rec.end_date);
592 --
593 hr_utility.set_location(' Leaving:'||l_proc, 10);
594 End update_validate;
595 --
596 -- ----------------------------------------------------------------------------
597 -- |---------------------------< delete_validate >----------------------------|
598 -- ----------------------------------------------------------------------------
599 Procedure delete_validate(p_rec in hr_cau_shd.g_rec_type) is
600 --
601 l_proc varchar2(72) := g_package||'delete_validate';
602 --
603 Begin
604 hr_utility.set_location('Entering:'||l_proc, 5);
605 --
606 -- Call all supporting business operations
607 --
608 hr_utility.set_location(' Leaving:'||l_proc, 10);
609 End delete_validate;
610 --
611 end hr_cau_bus;