[Home] [Help]
PACKAGE BODY: APPS.HR_WRK_SCH_PKG
Source
1 PACKAGE BODY hr_wrk_sch_pkg AS
2 -- $Header: pewrksch.pkb 120.6.12010000.2 2008/08/06 09:39:30 ubhat ship $
3 -- this procedure will check if the entered absence is overlapping with any of
4 -- the schedules attached at BG , Pos, Job, HROrg, Loc . Level
5 -- NOTE : this procedure will only be called when there exists an Schedule at Person Asg Level.
6 -- fix for the bug 6711896
7 --
8 -----------------------------------------------------------------------------
9 --------------------------< check_overlap_schedules >---------------------------
10 -----------------------------------------------------------------------------
11
12 overlapped EXCEPTION;
13
14 Procedure check_overlap_schedules (p_person_assignment_id IN NUMBER
15 ,p_period_start_date IN DATE
16 ,p_period_end_date IN DATE
17 ,p_schedule_category IN VARCHAR2
18 ,p_include_exceptions IN VARCHAR2
19 ,p_busy_tentative_as IN VARCHAR2
20 ,x_schedule_source IN OUT NOCOPY VARCHAR2
21 ,x_schedule IN OUT NOCOPY cac_avlblty_time_varray) is
22
23
24 l_proc VARCHAR2(50);
25 l_return_status VARCHAR2(1);
26 l_msg_count NUMBER;
27 l_msg_data VARCHAR2(2000);
28 l_sch_inh_seq NUMBER;
29 l_wrk_sch_found BOOLEAN;
30 l_wrk_sch_count NUMBER;
31 l_bus_grp_id NUMBER;
32 l_hr_org_id NUMBER;
33 l_job_id NUMBER;
34 l_pos_id NUMBER;
35 l_loc_id NUMBER;
36 l_include_exceptions VARCHAR2(1);
37 l_busy_tentative_as VARCHAR2(30);
38
39
40 -- Get max schedule inheritance level
41 CURSOR c_max_inh_seq IS
42 SELECT MAX(hier_seq)
43 FROM per_sch_inherit_hier
44 WHERE in_hier = 'Y'
45 AND hier_seq IS NOT NULL;
46
47 -- Get schedule inheritance level from hierarchy
48 CURSOR c_sch_inh_lvl (cp_inh_seq IN NUMBER) IS
49 SELECT inherit_level
50 FROM per_sch_inherit_hier
51 WHERE hier_seq = cp_inh_seq;
52
53 -- Cursor to get person assignment attributes
54 CURSOR c_per_asg (cp_per_asg_id IN NUMBER
55 ,cp_eff_date IN DATE) IS
56 SELECT business_group_id
57 ,organization_id
58 ,job_id
59 ,position_id
60 ,location_id
61 FROM per_all_assignments_f
62 WHERE assignment_id = cp_per_asg_id
63 AND cp_eff_date BETWEEN effective_start_date
64 ANd effective_end_date;
65
66 -- Cursor to test if schedule exists
67 CURSOR c_sch_found (cp_object_type IN VARCHAR2
68 ,cp_object_id IN NUMBER
69 ,cp_start_date IN DATE
70 ,cp_end_date IN DATE
71 ,cp_sch_cat IN VARCHAR2
72 ) IS
73 SELECT COUNT(*)
74 FROM cac_sr_schdl_objects CSSO
75 ,cac_sr_schedules_b CSSB
76 WHERE CSSO.object_type = cp_object_type
77 AND CSSO.object_id = cp_object_id
78 AND CSSO.start_date_active <= cp_end_date
79 AND CSSO.end_date_active >= cp_start_date
80 AND CSSO.schedule_id = CSSB.schedule_id
81 AND CSSB.deleted_date IS NULL
82 AND (CSSB.schedule_category = cp_sch_cat
83 OR
84 CSSB.schedule_id IN (SELECT schedule_id
85 FROM cac_sr_publish_schedules
86 WHERE object_type = cp_object_type
87 AND object_id = cp_object_id
88 AND cp_sch_cat IS NULL
89 )
90 );
91
92 CURSOR c_sch_dates (cp_object_type IN VARCHAR2
93 ,cp_object_id IN NUMBER
94 ,cp_start_date IN DATE
95 ,cp_end_date IN DATE
96 ,cp_sch_cat IN VARCHAR2
97 ) IS
98 SELECT CSSO.start_date_active,CSSO.end_date_active
99 FROM cac_sr_schdl_objects CSSO
100 ,cac_sr_schedules_b CSSB
101 WHERE CSSO.object_type = cp_object_type
102 AND CSSO.object_id = cp_object_id
103 AND CSSO.start_date_active <= cp_end_date
104 AND CSSO.end_date_active >= cp_start_date
105 AND CSSO.schedule_id = CSSB.schedule_id
106 AND CSSB.deleted_date IS NULL
107 AND (CSSB.schedule_category = cp_sch_cat
108 OR
109 CSSB.schedule_id IN (SELECT schedule_id
110 FROM cac_sr_publish_schedules
111 WHERE object_type = cp_object_type
112 AND object_id = cp_object_id
113 AND cp_sch_cat IS NULL
114 )
115 );
116
117 l_sch_start_date date:=null;
118 l_sch_end_date date :=null;
119
120
121
122 begin
123
124 hr_utility.set_location('entering overlap check: ', 10);
125
126 OPEN c_max_inh_seq;
127 FETCH c_max_inh_seq INTO l_sch_inh_seq;
128 CLOSE c_max_inh_seq;
129
130 hr_utility.set_location('MaxSeq: '||l_sch_inh_seq, 10);
131
132 -- Get person assignment attributes
133 OPEN c_per_asg (p_person_assignment_id
134 ,p_period_start_date
135 );
136 FETCH c_per_asg INTO l_bus_grp_id
137 ,l_hr_org_id
138 ,l_job_id
139 ,l_pos_id
140 ,l_loc_id;
141 CLOSE c_per_asg;
142
143 hr_utility.set_location('BGId:'||l_bus_grp_id||
144 ' HROrgId:'||l_hr_org_id||
145 ' JobId:'||l_job_id||
146 ' PosId:'||l_pos_id||
147 ' LocId:'||l_loc_id, 15);
148
149 WHILE l_sch_inh_seq > 0
150 LOOP
151 -- Get inheritance level
152 OPEN c_sch_inh_lvl (l_sch_inh_seq);
153 FETCH c_sch_inh_lvl INTO x_schedule_source;
154 CLOSE c_sch_inh_lvl;
155
156 hr_utility.set_location('SchInhLvl: '||x_schedule_source, 20);
157
158 CASE x_schedule_source
159 WHEN 'BUS_GRP' THEN
160 -- Get schedule from business group
161 hr_utility.set_location('overlap check', 30);
162 cac_avlblty_pub.get_schedule(p_api_version => 1.0
163 ,p_init_msg_list => 'F'
164 ,p_object_type => 'BUSINESS_GROUP'
165 ,p_object_id => l_bus_grp_id
166 ,p_start_date_time => p_period_start_date
167 ,p_end_date_time => p_period_end_date
168 ,p_schedule_category => p_schedule_category
169 ,p_include_exception => l_include_exceptions
170 ,p_busy_tentative => l_busy_tentative_as
171 ,x_schedule => x_schedule
172 ,x_return_status => l_return_status
173 ,x_msg_count => l_msg_count
174 ,x_msg_data => l_msg_data
175 );
176 hr_utility.set_location('SchCnt:'||x_schedule.COUNT, 45);
177
178 IF x_schedule.COUNT >= 1 then
179 hr_utility.set_location(l_proc||' Before opening cursor ', 1);
180 OPEN c_sch_dates ('BUSINESS_GROUP'
181 ,l_bus_grp_id
182 ,p_period_start_date
183 ,p_period_end_date
184 ,p_schedule_category
185 );
186
187 hr_utility.set_location(l_proc||' After opening cursor ', 2);
188 fetch c_sch_dates into l_sch_start_date,l_sch_end_date;
189 hr_utility.set_location(l_proc||' After fetching', 3);
190 if(c_sch_dates%found) then
191 hr_utility.set_location(l_proc||' Record found', 40);
192 close c_sch_dates;
193 hr_utility.set_location(l_proc||': start date '||l_sch_start_date||' end date '||to_char(l_sch_end_date), 20);
194 -- if(p_period_start_date < l_sch_start_date or p_period_end_date > l_sch_end_date) then
195 if ( p_period_start_date between l_sch_start_date and l_sch_end_date ) then
196 hr_utility.set_location(l_proc||' Raising error', 20);
197 raise overlapped;
198 hr_utility.set_location(l_proc||' Raising error', 21);
199
200 end if;
201 hr_utility.set_location(l_proc||' Exiting with success', 5);
202 ELSE
203 hr_utility.set_location(l_proc||' No Record found ', 6);
204 close c_sch_dates;
205 l_sch_start_date:=null;
206 l_sch_end_date:=null;
207
208 end if;
209 end if;
210
211 WHEN 'HR_ORG' THEN
212 -- Get schedule from hr organization
213 hr_utility.set_location(l_proc, 80);
214 cac_avlblty_pub.get_schedule(p_api_version => 1.0
215 ,p_init_msg_list => 'F'
216 ,p_object_type => 'HR_ORGANIZATION'
217 ,p_object_id => l_hr_org_id
218 ,p_start_date_time => p_period_start_date
219 ,p_end_date_time => p_period_end_date
220 ,p_schedule_category => p_schedule_category
221 ,p_include_exception => l_include_exceptions
222 ,p_busy_tentative => l_busy_tentative_as
223 ,x_schedule => x_schedule
224 ,x_return_status => l_return_status
225 ,x_msg_count => l_msg_count
226 ,x_msg_data => l_msg_data
227 );
228 hr_utility.set_location('SchCnt:'||x_schedule.COUNT, 85);
229 IF x_schedule.COUNT >= 1 then
230 hr_utility.set_location(l_proc||' Before opening cursor ', 21);
231 OPEN c_sch_dates ('HR_ORGANIZATION'
232 ,l_hr_org_id
233 ,p_period_start_date
234 ,p_period_end_date
235 ,p_schedule_category
236 );
237
238 hr_utility.set_location(l_proc||' After opening cursor ', 22);
239 fetch c_sch_dates into l_sch_start_date,l_sch_end_date;
240 hr_utility.set_location(l_proc||' After fetching', 3);
241 if(c_sch_dates%found) then
242 hr_utility.set_location(l_proc||' Record found', 23);
243 close c_sch_dates;
244 hr_utility.set_location(l_proc||': start date '||l_sch_start_date||' end date '||to_char(l_sch_end_date), 20);
245 -- if(p_period_start_date < l_sch_start_date or p_period_end_date > l_sch_end_date) then
246 if ( p_period_start_date between l_sch_start_date and l_sch_end_date ) then
247 hr_utility.set_location(l_proc||' Raising error', 24);
248 raise overlapped;
249 hr_utility.set_location(l_proc||' Raising error', 25);
250
251 end if;
252 hr_utility.set_location(l_proc||' Exiting with success', 26);
253 ELSE
254 hr_utility.set_location(l_proc||' No Record found ', 27);
255 close c_sch_dates;
256 l_sch_start_date:=null;
257 l_sch_end_date:=null;
258
259 end if;
260 end if;
261
262 WHEN 'JOB' THEN
263 -- Get schedule from job
264 hr_utility.set_location(l_proc, 90);
265 cac_avlblty_pub.get_schedule(p_api_version => 1.0
266 ,p_init_msg_list => 'F'
267 ,p_object_type => 'HR_JOB'
268 ,p_object_id => l_job_id
269 ,p_start_date_time => p_period_start_date
270 ,p_end_date_time => p_period_end_date
271 ,p_schedule_category => p_schedule_category
272 ,p_include_exception => l_include_exceptions
273 ,p_busy_tentative => l_busy_tentative_as
274 ,x_schedule => x_schedule
275 ,x_return_status => l_return_status
276 ,x_msg_count => l_msg_count
277 ,x_msg_data => l_msg_data
278 );
279 hr_utility.set_location('SchCnt:'||x_schedule.COUNT, 95);
280 IF x_schedule.COUNT >= 1 then
281 hr_utility.set_location(l_proc||' Before opening cursor ', 41);
282 OPEN c_sch_dates ('HR_JOB'
283 ,l_job_id
284 ,p_period_start_date
285 ,p_period_end_date
286 ,p_schedule_category
287 );
288
289 hr_utility.set_location(l_proc||' After opening cursor ', 42);
290 fetch c_sch_dates into l_sch_start_date,l_sch_end_date;
291 hr_utility.set_location(l_proc||' After fetching', 3);
292 if(c_sch_dates%found) then
293 hr_utility.set_location(l_proc||' Record found', 43);
294 close c_sch_dates;
295 hr_utility.set_location(l_proc||': start date '||l_sch_start_date||' end date '||to_char(l_sch_end_date), 20);
296 -- if(p_period_start_date < l_sch_start_date or p_period_end_date > l_sch_end_date) then
297 if ( p_period_start_date between l_sch_start_date and l_sch_end_date ) then
298 hr_utility.set_location(l_proc||' Raising error', 44);
299 raise overlapped;
300 hr_utility.set_location(l_proc||' Raising error', 45);
301
302 end if;
303 hr_utility.set_location(l_proc||' Exiting with success', 46);
304 ELSE
305 hr_utility.set_location(l_proc||' No Record found ', 47);
306 close c_sch_dates;
307 l_sch_start_date:=null;
308 l_sch_end_date:=null;
309
310 end if;
311 end if;
312
313 WHEN 'POS' THEN
314 -- Get schedule from position
315 hr_utility.set_location(l_proc, 100);
316 cac_avlblty_pub.get_schedule(p_api_version => 1.0
317 ,p_init_msg_list => 'F'
318 ,p_object_type => 'HR_POSITION'
319 ,p_object_id => l_pos_id
320 ,p_start_date_time => p_period_start_date
321 ,p_end_date_time => p_period_end_date
322 ,p_schedule_category => p_schedule_category
323 ,p_include_exception => l_include_exceptions
324 ,p_busy_tentative => l_busy_tentative_as
325 ,x_schedule => x_schedule
326 ,x_return_status => l_return_status
327 ,x_msg_count => l_msg_count
328 ,x_msg_data => l_msg_data
329 );
330 hr_utility.set_location('SchCnt:'||x_schedule.COUNT, 105);
331 IF x_schedule.COUNT >= 1 then
332 hr_utility.set_location(l_proc||' Before opening cursor ', 41);
333 OPEN c_sch_dates ('HR_POSITION'
334 ,l_pos_id
335 ,p_period_start_date
336 ,p_period_end_date
337 ,p_schedule_category
338 );
339
340 hr_utility.set_location(l_proc||' After opening cursor ', 42);
341 fetch c_sch_dates into l_sch_start_date,l_sch_end_date;
342 hr_utility.set_location(l_proc||' After fetching', 3);
343 if(c_sch_dates%found) then
344 hr_utility.set_location(l_proc||' Record found', 43);
345 close c_sch_dates;
346 hr_utility.set_location(l_proc||': start date '||l_sch_start_date||' end date '||to_char(l_sch_end_date), 20);
347 -- if(p_period_start_date < l_sch_start_date or p_period_end_date > l_sch_end_date) then
348 if ( p_period_start_date between l_sch_start_date and l_sch_end_date ) then
349 hr_utility.set_location(l_proc||' Raising error', 44);
350 raise overlapped;
351 hr_utility.set_location(l_proc||' Raising error', 45);
352
353 end if;
354 hr_utility.set_location(l_proc||' Exiting with success', 46);
355 ELSE
356 hr_utility.set_location(l_proc||' No Record found ', 47);
357 close c_sch_dates;
358 l_sch_start_date:=null;
359 l_sch_end_date:=null;
360
361 end if;
362 end if;
363
364 WHEN 'LOC' THEN
365 -- Get schedule from location
366 hr_utility.set_location(l_proc, 110);
367 cac_avlblty_pub.get_schedule(p_api_version => 1.0
368 ,p_init_msg_list => 'F'
369 ,p_object_type => 'HR_LOCATION'
370 ,p_object_id => l_loc_id
371 ,p_start_date_time => p_period_start_date
372 ,p_end_date_time => p_period_end_date
373 ,p_schedule_category => p_schedule_category
374 ,p_include_exception => l_include_exceptions
375 ,p_busy_tentative => l_busy_tentative_as
376 ,x_schedule => x_schedule
377 ,x_return_status => l_return_status
378 ,x_msg_count => l_msg_count
379 ,x_msg_data => l_msg_data
380 );
381 hr_utility.set_location('SchCnt:'||x_schedule.COUNT, 115);
382
383 IF x_schedule.COUNT >= 1 then
384 hr_utility.set_location(l_proc||' Before opening cursor ', 51);
385 OPEN c_sch_dates ('HR_LOCATION'
386 ,l_loc_id
387 ,p_period_start_date
388 ,p_period_end_date
389 ,p_schedule_category
390 );
391
392 hr_utility.set_location(l_proc||' After opening cursor ', 52);
393 fetch c_sch_dates into l_sch_start_date,l_sch_end_date;
394 hr_utility.set_location(l_proc||' After fetching', 3);
395 if(c_sch_dates%found) then
396 hr_utility.set_location(l_proc||' Record found', 53);
397 close c_sch_dates;
398 hr_utility.set_location(l_proc||': start date '||l_sch_start_date||' end date '||to_char(l_sch_end_date), 20);
399 -- if(p_period_start_date < l_sch_start_date or p_period_end_date > l_sch_end_date) then
400 if ( p_period_start_date between l_sch_start_date and l_sch_end_date ) then
401 hr_utility.set_location(l_proc||' Raising error', 54);
402 raise overlapped;
403 hr_utility.set_location(l_proc||' Raising error', 55);
404
405 end if;
406 hr_utility.set_location(l_proc||' Exiting with success', 56);
407 ELSE
408 hr_utility.set_location(l_proc||' No Record found ', 57);
409 close c_sch_dates;
410 l_sch_start_date:=null;
411 l_sch_end_date:=null;
412
413 end if;
414 end if;
415
416 END CASE;
417
418
419 l_sch_inh_seq := l_sch_inh_seq - 1;
420 END LOOP;
421
422
423 end check_overlap_schedules;
424 --
425 --fix for the bug 6711896
426 --
427 -----------------------------------------------------------------------------
428 --------------------------< get_per_asg_schedule >---------------------------
429 -----------------------------------------------------------------------------
430 --
431 -- Schedule Source Return Values:-
432 -- PER_ASG - HR Person Assignment
433 -- BUS_GRP - Business Group
434 -- HR_ORG - HR Organization
435 -- JOB - HR Job
436 -- POS - HR Position
437 -- LOC - HR Location
438 --
439 -- Return Status Values:-
440 -- 0 - Success
441 -- 1 - Warning
442 -- 2 - Failure
443 --
444 PROCEDURE get_per_asg_schedule(p_person_assignment_id IN NUMBER
445 ,p_period_start_date IN DATE
446 ,p_period_end_date IN DATE
447 ,p_schedule_category IN VARCHAR2
448 ,p_include_exceptions IN VARCHAR2
449 ,p_busy_tentative_as IN VARCHAR2
450 ,x_schedule_source IN OUT NOCOPY VARCHAR2
451 ,x_schedule IN OUT NOCOPY cac_avlblty_time_varray
452 ,x_return_status OUT NOCOPY NUMBER
453 ,x_return_message OUT NOCOPY VARCHAR2
454 ) IS
455
456 l_proc VARCHAR2(50);
457 l_return_status VARCHAR2(1);
458 l_msg_count NUMBER;
459 l_msg_data VARCHAR2(2000);
460 l_sch_inh_seq NUMBER;
461 l_wrk_sch_found BOOLEAN;
462 l_wrk_sch_count NUMBER;
463 l_bus_grp_id NUMBER;
464 l_hr_org_id NUMBER;
465 l_job_id NUMBER;
466 l_pos_id NUMBER;
467 l_loc_id NUMBER;
468 l_include_exceptions VARCHAR2(1);
469 l_busy_tentative_as VARCHAR2(30);
470 e_invalid_params EXCEPTION;
471
472 -- Get max schedule inheritance level
473 CURSOR c_max_inh_seq IS
474 SELECT MAX(hier_seq)
475 FROM per_sch_inherit_hier
476 WHERE in_hier = 'Y'
477 AND hier_seq IS NOT NULL;
478
479 -- Get schedule inheritance level from hierarchy
480 CURSOR c_sch_inh_lvl (cp_inh_seq IN NUMBER) IS
481 SELECT inherit_level
482 FROM per_sch_inherit_hier
483 WHERE hier_seq = cp_inh_seq;
484
485 -- Cursor to get person assignment attributes
486 CURSOR c_per_asg (cp_per_asg_id IN NUMBER
487 ,cp_eff_date IN DATE) IS
488 SELECT business_group_id
489 ,organization_id
490 ,job_id
491 ,position_id
492 ,location_id
493 FROM per_all_assignments_f
494 WHERE assignment_id = cp_per_asg_id
495 AND cp_eff_date BETWEEN effective_start_date
496 ANd effective_end_date;
497
498 -- Cursor to test if schedule exists
499 CURSOR c_sch_found (cp_object_type IN VARCHAR2
500 ,cp_object_id IN NUMBER
501 ,cp_start_date IN DATE
502 ,cp_end_date IN DATE
503 ,cp_sch_cat IN VARCHAR2
504 ) IS
505 SELECT COUNT(*)
506 FROM cac_sr_schdl_objects CSSO
507 ,cac_sr_schedules_b CSSB
508 WHERE CSSO.object_type = cp_object_type
509 AND CSSO.object_id = cp_object_id
510 AND CSSO.start_date_active <= cp_end_date
511 AND CSSO.end_date_active >= cp_start_date
512 AND CSSO.schedule_id = CSSB.schedule_id
513 AND CSSB.deleted_date IS NULL
514 AND (CSSB.schedule_category = cp_sch_cat
515 OR
516 CSSB.schedule_id IN (SELECT schedule_id
517 FROM cac_sr_publish_schedules
518 WHERE object_type = cp_object_type
519 AND object_id = cp_object_id
520 AND cp_sch_cat IS NULL
521 )
522 );
523 --
524 --- fix for the bug 6711896
525
526 CURSOR c_sch_dates (cp_object_type IN VARCHAR2
527 ,cp_object_id IN NUMBER
528 ,cp_start_date IN DATE
529 ,cp_end_date IN DATE
530 ,cp_sch_cat IN VARCHAR2
531 ) IS
532 SELECT CSSO.start_date_active,CSSO.end_date_active
533 FROM cac_sr_schdl_objects CSSO
534 ,cac_sr_schedules_b CSSB
535 WHERE CSSO.object_type = cp_object_type
536 AND CSSO.object_id = cp_object_id
537 AND CSSO.start_date_active <= cp_end_date
538 AND CSSO.end_date_active >= cp_start_date
539 AND CSSO.schedule_id = CSSB.schedule_id
540 AND CSSB.deleted_date IS NULL
541 AND (CSSB.schedule_category = cp_sch_cat
542 OR
543 CSSB.schedule_id IN (SELECT schedule_id
544 FROM cac_sr_publish_schedules
545 WHERE object_type = cp_object_type
546 AND object_id = cp_object_id
547 AND cp_sch_cat IS NULL
548 )
549 );
550
551 l_sch_start_date date:=null;
552 l_sch_end_date date :=null;
553 -- fix for the bug 6711896
554
555 BEGIN
556 l_proc := 'hr_wrk_sch_pkg.get_per_asg_schedule';
557 hr_utility.set_location('Entering: '|| l_proc, 10);
558 --
559 -- Initialize
560 x_return_status := 0;
561 x_schedule := cac_avlblty_time_varray();
562 x_schedule_source := 'PER_ASG';
563 l_busy_tentative_as := NVL(p_busy_tentative_as, 'BUSY');
564 l_wrk_sch_found := FALSE;
565 l_wrk_sch_count := 0;
566 SELECT DECODE(p_include_exceptions, 'Y','T', 'N','F', 'T','T', 'F','F', 'T')
567 INTO l_include_exceptions FROM DUAL;
568
569 -- Validate parameters
570 IF p_person_assignment_id IS NULL THEN
571 x_return_message := 'NULL P_PERSON_ASSIGNMENT_ID';
572 RAISE e_invalid_params;
573 ELSIF p_period_start_date IS NULL THEN
574 x_return_message := 'NULL P_PERIOD_START_DATE';
575 RAISE e_invalid_params;
576 ELSIF p_period_end_date IS NULL THEN
577 x_return_message := 'NULL P_PERIOD_END_DATE';
578 RAISE e_invalid_params;
579 END IF;
580
581 hr_utility.set_location(l_proc, 20);
582
583 -- Get schedule from person assignment
584 cac_avlblty_pub.get_schedule(p_api_version => 1.0
585 ,p_init_msg_list => 'F'
586 ,p_object_type => 'PERSON_ASSIGNMENT'
587 ,p_object_id => p_person_assignment_id
588 ,p_start_date_time => p_period_start_date
589 ,p_end_date_time => p_period_end_date
590 ,p_schedule_category => p_schedule_category
591 ,p_include_exception => l_include_exceptions
592 ,p_busy_tentative => l_busy_tentative_as
593 ,x_schedule => x_schedule
594 ,x_return_status => l_return_status
595 ,x_msg_count => l_msg_count
596 ,x_msg_data => l_msg_data
597 );
598 --
599 hr_utility.set_location('SchCnt:'||x_schedule.COUNT, 30);
600 --
601 IF x_schedule.COUNT > 1 OR (x_schedule.COUNT = 1 AND x_schedule(1).period_name IS NOT NULL) THEN
602 l_wrk_sch_found := TRUE;
603 END IF;
604 --
605 IF x_schedule.COUNT = 1 AND x_schedule(1).period_name IS NULL THEN
606 -- Either schedule is found with no working time or schedule is not found.
607 -- Test if work schedule exists at PER ASG level
608 OPEN c_sch_found ('PERSON_ASSIGNMENT'
609 ,p_person_assignment_id
610 ,p_period_start_date
611 ,p_period_end_date
612 ,p_schedule_category
613 );
614 FETCH c_sch_found INTO l_wrk_sch_count;
615 CLOSE c_sch_found;
616 IF l_wrk_sch_count > 0 THEN
617 l_wrk_sch_found := TRUE;
618 END IF;
619 --
620 hr_utility.set_location('PerAsg SchCnt:'||l_wrk_sch_count, 35);
621 END IF;
622 -- fix for the bug 6711896
623 --
624 OPEN c_sch_dates ('PERSON_ASSIGNMENT'
625 ,p_person_assignment_id
626 ,p_period_start_date
627 ,p_period_end_date
628 ,p_schedule_category
629 );
630 fetch c_sch_dates into l_sch_start_date,l_sch_end_date;
631
632 if(c_sch_dates%found) then
633 close c_sch_dates;
634 hr_utility.set_location(l_proc||' start date :'||to_char(l_sch_start_date),20);
635 hr_utility.set_location(l_proc||' end date :'||to_char(l_sch_end_date),20);
636
637 if ( p_period_start_date < l_sch_start_date and p_period_end_date > l_sch_start_date )
638 then
639 hr_utility.set_location('call the check overlap function :' ,20);
640 check_overlap_schedules(p_person_assignment_id,
641 p_period_start_date,
642 p_period_end_date,
643 p_schedule_category,
644 p_include_exceptions,
645 p_busy_tentative_as,
646 x_schedule_source,
647 x_schedule );
648 end if;
649 end if;
650 hr_utility.set_location('after checking overlaps :' ,20);
651 --
652 -- fix for the bug 6711896
653 -- fix for the bug 6711896
654 --
655
656 -- If explicit schedule not found, use default schedule inheritance
657 IF NOT l_wrk_sch_found THEN
658 -- Get max schedule inheritance hierarchy level
659 OPEN c_max_inh_seq;
660 FETCH c_max_inh_seq INTO l_sch_inh_seq;
661 CLOSE c_max_inh_seq;
662
663 hr_utility.set_location('MaxSeq: '||l_sch_inh_seq, 40);
664
665 -- Get person assignment attributes
666 OPEN c_per_asg (p_person_assignment_id
667 ,p_period_start_date
668 );
669 FETCH c_per_asg INTO l_bus_grp_id
670 ,l_hr_org_id
671 ,l_job_id
672 ,l_pos_id
673 ,l_loc_id;
674 CLOSE c_per_asg;
675
676 hr_utility.set_location('BGId:'||l_bus_grp_id||
677 ' HROrgId:'||l_hr_org_id||
678 ' JobId:'||l_job_id||
679 ' PosId:'||l_pos_id||
680 ' LocId:'||l_loc_id, 50);
681
682 WHILE l_sch_inh_seq > 0 AND NOT l_wrk_sch_found LOOP
683 -- Get inheritance level
684 OPEN c_sch_inh_lvl (l_sch_inh_seq);
685 FETCH c_sch_inh_lvl INTO x_schedule_source;
686 CLOSE c_sch_inh_lvl;
687
688 hr_utility.set_location('SchInhLvl: '||x_schedule_source, 60);
689
690 CASE x_schedule_source
691 WHEN 'BUS_GRP' THEN
692 -- Get schedule from business group
693 hr_utility.set_location(l_proc, 70);
694 cac_avlblty_pub.get_schedule(p_api_version => 1.0
695 ,p_init_msg_list => 'F'
696 ,p_object_type => 'BUSINESS_GROUP'
697 ,p_object_id => l_bus_grp_id
698 ,p_start_date_time => p_period_start_date
699 ,p_end_date_time => p_period_end_date
700 ,p_schedule_category => p_schedule_category
701 ,p_include_exception => l_include_exceptions
702 ,p_busy_tentative => l_busy_tentative_as
703 ,x_schedule => x_schedule
704 ,x_return_status => l_return_status
705 ,x_msg_count => l_msg_count
706 ,x_msg_data => l_msg_data
707 );
708 hr_utility.set_location('SchCnt:'||x_schedule.COUNT, 75);
709
710 WHEN 'HR_ORG' THEN
711 -- Get schedule from hr organization
712 hr_utility.set_location(l_proc, 80);
713 cac_avlblty_pub.get_schedule(p_api_version => 1.0
714 ,p_init_msg_list => 'F'
715 ,p_object_type => 'HR_ORGANIZATION'
716 ,p_object_id => l_hr_org_id
717 ,p_start_date_time => p_period_start_date
718 ,p_end_date_time => p_period_end_date
719 ,p_schedule_category => p_schedule_category
720 ,p_include_exception => l_include_exceptions
721 ,p_busy_tentative => l_busy_tentative_as
722 ,x_schedule => x_schedule
723 ,x_return_status => l_return_status
724 ,x_msg_count => l_msg_count
725 ,x_msg_data => l_msg_data
726 );
727 hr_utility.set_location('SchCnt:'||x_schedule.COUNT, 85);
728
729 WHEN 'JOB' THEN
730 -- Get schedule from job
731 hr_utility.set_location(l_proc, 90);
732 cac_avlblty_pub.get_schedule(p_api_version => 1.0
733 ,p_init_msg_list => 'F'
734 ,p_object_type => 'HR_JOB'
735 ,p_object_id => l_job_id
736 ,p_start_date_time => p_period_start_date
737 ,p_end_date_time => p_period_end_date
738 ,p_schedule_category => p_schedule_category
739 ,p_include_exception => l_include_exceptions
740 ,p_busy_tentative => l_busy_tentative_as
741 ,x_schedule => x_schedule
742 ,x_return_status => l_return_status
743 ,x_msg_count => l_msg_count
744 ,x_msg_data => l_msg_data
745 );
746 hr_utility.set_location('SchCnt:'||x_schedule.COUNT, 95);
747
748 WHEN 'POS' THEN
749 -- Get schedule from position
750 hr_utility.set_location(l_proc, 100);
751 cac_avlblty_pub.get_schedule(p_api_version => 1.0
752 ,p_init_msg_list => 'F'
753 ,p_object_type => 'HR_POSITION'
754 ,p_object_id => l_pos_id
755 ,p_start_date_time => p_period_start_date
756 ,p_end_date_time => p_period_end_date
757 ,p_schedule_category => p_schedule_category
758 ,p_include_exception => l_include_exceptions
759 ,p_busy_tentative => l_busy_tentative_as
760 ,x_schedule => x_schedule
761 ,x_return_status => l_return_status
762 ,x_msg_count => l_msg_count
763 ,x_msg_data => l_msg_data
764 );
765 hr_utility.set_location('SchCnt:'||x_schedule.COUNT, 105);
766
767 WHEN 'LOC' THEN
768 -- Get schedule from location
769 hr_utility.set_location(l_proc, 110);
770 cac_avlblty_pub.get_schedule(p_api_version => 1.0
771 ,p_init_msg_list => 'F'
772 ,p_object_type => 'HR_LOCATION'
773 ,p_object_id => l_loc_id
774 ,p_start_date_time => p_period_start_date
775 ,p_end_date_time => p_period_end_date
776 ,p_schedule_category => p_schedule_category
777 ,p_include_exception => l_include_exceptions
778 ,p_busy_tentative => l_busy_tentative_as
779 ,x_schedule => x_schedule
780 ,x_return_status => l_return_status
781 ,x_msg_count => l_msg_count
782 ,x_msg_data => l_msg_data
783 );
784 hr_utility.set_location('SchCnt:'||x_schedule.COUNT, 115);
785 END CASE;
786
787 -- Loop exit conditions
788 IF x_schedule.COUNT > 1 OR (x_schedule.COUNT = 1 AND x_schedule(1).period_name IS NOT NULL) THEN
789 l_wrk_sch_found := TRUE;
790 END IF;
791 -- Check if schedule found with no working time.
792 IF x_schedule.COUNT = 1 AND x_schedule(1).period_name IS NULL THEN
793 -- Either schedule is found with no working time or schedule is not found.
794 -- Test if work schedule exists at PER ASG level
795 CASE x_schedule_source
796 WHEN 'BUS_GRP' THEN
797 OPEN c_sch_found ('BUSINESS_GROUP'
798 ,l_bus_grp_id
799 ,p_period_start_date
800 ,p_period_end_date
801 ,p_schedule_category
802 );
803 FETCH c_sch_found INTO l_wrk_sch_count;
804 CLOSE c_sch_found;
805 IF l_wrk_sch_count > 0 THEN
806 l_wrk_sch_found := TRUE;
807 END IF;
808 --
809 hr_utility.set_location('BusGrp SchCnt:'||l_wrk_sch_count, 117);
810 --
811 WHEN 'HR_ORG' THEN
812 OPEN c_sch_found ('HR_ORGANIZATION'
813 ,l_hr_org_id
814 ,p_period_start_date
815 ,p_period_end_date
816 ,p_schedule_category
817 );
818 FETCH c_sch_found INTO l_wrk_sch_count;
819 CLOSE c_sch_found;
820 IF l_wrk_sch_count > 0 THEN
821 l_wrk_sch_found := TRUE;
822 END IF;
823 --
824 hr_utility.set_location('HROrg SchCnt:'||l_wrk_sch_count, 117);
825 --
826 WHEN 'JOB' THEN
827 OPEN c_sch_found ('HR_JOB'
828 ,l_job_id
829 ,p_period_start_date
830 ,p_period_end_date
831 ,p_schedule_category
832 );
833 FETCH c_sch_found INTO l_wrk_sch_count;
834 CLOSE c_sch_found;
835 IF l_wrk_sch_count > 0 THEN
836 l_wrk_sch_found := TRUE;
837 END IF;
838 --
839 hr_utility.set_location('HRJob SchCnt:'||l_wrk_sch_count, 117);
840 --
841 WHEN 'POS' THEN
842 OPEN c_sch_found ('HR_POSITION'
843 ,l_pos_id
844 ,p_period_start_date
845 ,p_period_end_date
846 ,p_schedule_category
847 );
848 FETCH c_sch_found INTO l_wrk_sch_count;
849 CLOSE c_sch_found;
850 IF l_wrk_sch_count > 0 THEN
851 l_wrk_sch_found := TRUE;
852 END IF;
853 --
854 hr_utility.set_location('HRPos SchCnt:'||l_wrk_sch_count, 117);
855 --
856 WHEN 'LOC' THEN
857 OPEN c_sch_found ('HR_LOCATION'
858 ,l_loc_id
859 ,p_period_start_date
860 ,p_period_end_date
861 ,p_schedule_category
862 );
863 FETCH c_sch_found INTO l_wrk_sch_count;
864 CLOSE c_sch_found;
865 IF l_wrk_sch_count > 0 THEN
866 l_wrk_sch_found := TRUE;
867 END IF;
868 --
869 hr_utility.set_location('HRLoc SchCnt:'||l_wrk_sch_count, 117);
870 --
871 END CASE;
872 END IF;
873 --
874 l_sch_inh_seq := l_sch_inh_seq - 1;
875 END LOOP;
876 END IF; -- Explicit schedule not found
877
878 IF NOT l_wrk_sch_found THEN
879 x_schedule_source := '';
880 END IF;
881
882 IF l_return_status = 'S' THEN
883 x_return_status := 0;
884 END IF;
885
886 hr_utility.set_location('Leaving: '|| l_proc, 120);
887
888 EXCEPTION
889 WHEN e_invalid_params THEN
890 hr_utility.set_location('Leaving: '|| l_proc, 130);
891 hr_utility.set_location(SQLERRM, 135);
892 x_return_status := 1;
893
894 WHEN overlapped THEN
895 hr_utility.set_location('raising: '|| l_proc, 125);
896 fnd_message.set_name('PER', 'HR_449835_ABS_SCHEDULE_OVERLAP');
897 fnd_message.set_token('STARTDATE', fnd_date.date_to_chardate(l_sch_start_date));
898 fnd_message.set_token('ENDDATE', fnd_date.date_to_chardate(l_sch_end_date));
899 fnd_message.raise_error;
900
901 WHEN OTHERS THEN
902 hr_utility.set_location('Leaving: '|| l_proc, 140);
903 hr_utility.set_location(SQLERRM, 145);
904 x_return_status := 2;
905 x_return_message := SQLERRM;
906
907 END get_per_asg_schedule;
908
909 --
910 -----------------------------------------------------------------------------
911 --------------------------< get_working_day >---------------------------
912 -----------------------------------------------------------------------------
913 --
914 -- input parameters : Valid value for prev_next flag is N and P
915 -- returns the next/previous working day for a leave request.
916
917 function get_working_day
918 (
919 p_person_assignment_id IN NUMBER
920 ,loa_start_date in DATE
921 ,loa_end_date in DATE
922 ,prev_next_flag in VARCHAR2 default 'N'
923
924 ) return DATE IS
925
926 l_working_date DATE;
927 l_flag Boolean;
928 l_schedule_source varchar2(1000);
929 l_schedule cac_avlblty_time_varray;
930 l_return_status number;
931 l_return_message varchar2(1000);
932 l_busy_tentative_as varchar2(30);
933 l_wrk_sch_found boolean;
934 l_free_busy varchar2(30);
935 l_count NUMBER;
936
937
938
939 BEGIN
940
941 l_flag := true;
942 l_return_status := 0;
943 l_schedule := cac_avlblty_time_varray();
944 l_schedule_source := 'PER_ASG';
945 l_busy_tentative_as := NVL(l_busy_tentative_as, 'BUSY');
946 l_wrk_sch_found := false;
947 l_count := 0;
948
949 if prev_next_flag = 'N' then
950 l_working_date := loa_end_date + 1;
951 else
952 l_working_date := loa_start_date -1 ;
953 end if;
954
955 while l_flag LOOP
956 --get the schedule
957 l_count := l_count +1;
958 get_per_asg_schedule(p_person_assignment_id
959 ,l_working_date
960 ,l_working_date+1
961 ,null
962 ,null
963 ,l_busy_tentative_as
964 ,l_schedule_source
965 ,l_schedule
966 ,l_return_status
967 ,l_return_message
968 );
969
970 IF l_schedule.COUNT > 1 or (l_schedule.COUNT =1 and l_schedule(1).period_name is not null) THEN
971 l_wrk_sch_found := TRUE;
972 END IF;
973
974 if l_wrk_sch_found then
975 for i in 1..l_schedule.COUNT loop
976 l_free_busy := l_schedule(i).FREE_BUSY_TYPE ;
977 if l_free_busy is not null and l_free_busy = 'FREE' then
978 -- found the working day
979 l_flag := false;
980 exit ;
981 end if;
982 end loop;
983 end if;
984
985 if l_flag then
986 if prev_next_flag = 'N' then
987 l_working_date := l_working_date + 1;
988 else
989 l_working_date := l_working_date -1;
990 end if;
991 end if ;
992
993 if l_count > 50 then
994 -- this condition has been added as an exception condition
995 -- to prevent an infinite loop.
996 return null;
997 end if;
998
999
1000 END LOOP;
1001 return l_working_date;
1002
1003 END get_working_day; --end of function
1004
1005 END hr_wrk_sch_pkg;