[Home] [Help]
PACKAGE BODY: APPS.HR_WRK_SCH_PKG
Source
1 PACKAGE BODY hr_wrk_sch_pkg AS
2 -- $Header: pewrksch.pkb 120.8.12020000.4 2012/10/23 07:09:35 srannama 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 -- this is a local schedule variable that will be passed to the check_overlap_schedules
555 -- procedure to avoid corruption of the variable x_schedule
556 -- Bug 14683476
557
558 x_schedule_dup cac_avlblty_time_varray;
559
560 BEGIN
561 l_proc := 'hr_wrk_sch_pkg.get_per_asg_schedule';
562 hr_utility.set_location('Entering: '|| l_proc, 10);
563 --
564 -- Initialize
565 x_return_status := 0;
566 x_schedule := cac_avlblty_time_varray();
567 x_schedule_source := 'PER_ASG';
568 l_busy_tentative_as := NVL(p_busy_tentative_as, 'BUSY');
569 l_wrk_sch_found := FALSE;
570 l_wrk_sch_count := 0;
571 SELECT DECODE(p_include_exceptions, 'Y','T', 'N','F', 'T','T', 'F','F', 'T')
572 INTO l_include_exceptions FROM DUAL;
573
574 -- Validate parameters
575 IF p_person_assignment_id IS NULL THEN
576 x_return_message := 'NULL P_PERSON_ASSIGNMENT_ID';
577 RAISE e_invalid_params;
578 ELSIF p_period_start_date IS NULL THEN
579 x_return_message := 'NULL P_PERIOD_START_DATE';
580 RAISE e_invalid_params;
581 ELSIF p_period_end_date IS NULL THEN
582 x_return_message := 'NULL P_PERIOD_END_DATE';
583 RAISE e_invalid_params;
584 END IF;
585
586 hr_utility.set_location(l_proc, 20);
587
588 -- Get schedule from person assignment
589 cac_avlblty_pub.get_schedule(p_api_version => 1.0
590 ,p_init_msg_list => 'F'
591 ,p_object_type => 'PERSON_ASSIGNMENT'
592 ,p_object_id => p_person_assignment_id
593 ,p_start_date_time => p_period_start_date
594 ,p_end_date_time => p_period_end_date
595 ,p_schedule_category => p_schedule_category
596 ,p_include_exception => l_include_exceptions
597 ,p_busy_tentative => l_busy_tentative_as
598 ,x_schedule => x_schedule
599 ,x_return_status => l_return_status
600 ,x_msg_count => l_msg_count
601 ,x_msg_data => l_msg_data
602 );
603 --
604 hr_utility.set_location('SchCnt:'||x_schedule.COUNT, 30);
605 --
606 IF x_schedule.COUNT > 1 OR (x_schedule.COUNT = 1 AND x_schedule(1).period_name IS NOT NULL) THEN
607 l_wrk_sch_found := TRUE;
608 END IF;
609 --
610 IF x_schedule.COUNT = 1 AND x_schedule(1).period_name IS NULL THEN
611 -- Either schedule is found with no working time or schedule is not found.
612 -- Test if work schedule exists at PER ASG level
613 OPEN c_sch_found ('PERSON_ASSIGNMENT'
614 ,p_person_assignment_id
615 ,p_period_start_date
616 ,p_period_end_date
617 ,p_schedule_category
618 );
619 FETCH c_sch_found INTO l_wrk_sch_count;
620 CLOSE c_sch_found;
621 IF l_wrk_sch_count > 0 THEN
622 l_wrk_sch_found := TRUE;
623 END IF;
624 --
625 hr_utility.set_location('PerAsg SchCnt:'||l_wrk_sch_count, 35);
626 END IF;
627 -- fix for the bug 6711896
628 --
629 OPEN c_sch_dates ('PERSON_ASSIGNMENT'
630 ,p_person_assignment_id
631 ,p_period_start_date
632 ,p_period_end_date
633 ,p_schedule_category
634 );
635 fetch c_sch_dates into l_sch_start_date,l_sch_end_date;
636
637 if(c_sch_dates%found) then
638 close c_sch_dates;
639 hr_utility.set_location(l_proc||' start date :'||to_char(l_sch_start_date),20);
640 hr_utility.set_location(l_proc||' end date :'||to_char(l_sch_end_date),20);
641
642 if ( p_period_start_date < l_sch_start_date and p_period_end_date > l_sch_start_date )
643 then
644
645 x_schedule_dup :=x_schedule; -- Bug 14683476
646 hr_utility.set_location('call the check overlap function :' ,20);
647 check_overlap_schedules(p_person_assignment_id,
648 p_period_start_date,
649 p_period_end_date,
650 p_schedule_category,
651 p_include_exceptions,
652 p_busy_tentative_as,
653 x_schedule_source,
654 x_schedule_dup ); -- Bug 14683476
655 end if;
656 end if;
657 hr_utility.set_location('after checking overlaps :' ,20);
658 --
659 -- fix for the bug 6711896
660 -- fix for the bug 6711896
661 --
662
663 -- If explicit schedule not found, use default schedule inheritance
664 IF NOT l_wrk_sch_found THEN
665 -- Get max schedule inheritance hierarchy level
666 OPEN c_max_inh_seq;
667 FETCH c_max_inh_seq INTO l_sch_inh_seq;
668 CLOSE c_max_inh_seq;
669
670 hr_utility.set_location('MaxSeq: '||l_sch_inh_seq, 40);
671
672 -- Get person assignment attributes
673 OPEN c_per_asg (p_person_assignment_id
674 ,p_period_start_date
675 );
676 FETCH c_per_asg INTO l_bus_grp_id
677 ,l_hr_org_id
678 ,l_job_id
679 ,l_pos_id
680 ,l_loc_id;
681 CLOSE c_per_asg;
682
683 hr_utility.set_location('BGId:'||l_bus_grp_id||
684 ' HROrgId:'||l_hr_org_id||
685 ' JobId:'||l_job_id||
686 ' PosId:'||l_pos_id||
687 ' LocId:'||l_loc_id, 50);
688
689 WHILE l_sch_inh_seq > 0 AND NOT l_wrk_sch_found LOOP
690 -- Get inheritance level
691 OPEN c_sch_inh_lvl (l_sch_inh_seq);
692 FETCH c_sch_inh_lvl INTO x_schedule_source;
693 CLOSE c_sch_inh_lvl;
694
695 hr_utility.set_location('SchInhLvl: '||x_schedule_source, 60);
696
697 CASE x_schedule_source
698 WHEN 'BUS_GRP' THEN
699 -- Get schedule from business group
700 hr_utility.set_location(l_proc, 70);
701 cac_avlblty_pub.get_schedule(p_api_version => 1.0
702 ,p_init_msg_list => 'F'
703 ,p_object_type => 'BUSINESS_GROUP'
704 ,p_object_id => l_bus_grp_id
705 ,p_start_date_time => p_period_start_date
706 ,p_end_date_time => p_period_end_date
707 ,p_schedule_category => p_schedule_category
708 ,p_include_exception => l_include_exceptions
709 ,p_busy_tentative => l_busy_tentative_as
710 ,x_schedule => x_schedule
711 ,x_return_status => l_return_status
712 ,x_msg_count => l_msg_count
713 ,x_msg_data => l_msg_data
714 );
715 hr_utility.set_location('SchCnt:'||x_schedule.COUNT, 75);
716
717 WHEN 'HR_ORG' THEN
718 -- Get schedule from hr organization
719 hr_utility.set_location(l_proc, 80);
720 cac_avlblty_pub.get_schedule(p_api_version => 1.0
721 ,p_init_msg_list => 'F'
722 ,p_object_type => 'HR_ORGANIZATION'
723 ,p_object_id => l_hr_org_id
724 ,p_start_date_time => p_period_start_date
725 ,p_end_date_time => p_period_end_date
726 ,p_schedule_category => p_schedule_category
727 ,p_include_exception => l_include_exceptions
728 ,p_busy_tentative => l_busy_tentative_as
729 ,x_schedule => x_schedule
730 ,x_return_status => l_return_status
731 ,x_msg_count => l_msg_count
732 ,x_msg_data => l_msg_data
733 );
734 hr_utility.set_location('SchCnt:'||x_schedule.COUNT, 85);
735
736 WHEN 'JOB' THEN
737 -- Get schedule from job
738 hr_utility.set_location(l_proc, 90);
739 cac_avlblty_pub.get_schedule(p_api_version => 1.0
740 ,p_init_msg_list => 'F'
741 ,p_object_type => 'HR_JOB'
742 ,p_object_id => l_job_id
743 ,p_start_date_time => p_period_start_date
744 ,p_end_date_time => p_period_end_date
745 ,p_schedule_category => p_schedule_category
746 ,p_include_exception => l_include_exceptions
747 ,p_busy_tentative => l_busy_tentative_as
748 ,x_schedule => x_schedule
749 ,x_return_status => l_return_status
750 ,x_msg_count => l_msg_count
751 ,x_msg_data => l_msg_data
752 );
753 hr_utility.set_location('SchCnt:'||x_schedule.COUNT, 95);
754
755 WHEN 'POS' THEN
756 -- Get schedule from position
757 hr_utility.set_location(l_proc, 100);
758 cac_avlblty_pub.get_schedule(p_api_version => 1.0
759 ,p_init_msg_list => 'F'
760 ,p_object_type => 'HR_POSITION'
761 ,p_object_id => l_pos_id
762 ,p_start_date_time => p_period_start_date
763 ,p_end_date_time => p_period_end_date
764 ,p_schedule_category => p_schedule_category
765 ,p_include_exception => l_include_exceptions
766 ,p_busy_tentative => l_busy_tentative_as
767 ,x_schedule => x_schedule
768 ,x_return_status => l_return_status
769 ,x_msg_count => l_msg_count
770 ,x_msg_data => l_msg_data
771 );
772 hr_utility.set_location('SchCnt:'||x_schedule.COUNT, 105);
773
774 WHEN 'LOC' THEN
775 -- Get schedule from location
776 hr_utility.set_location(l_proc, 110);
777 cac_avlblty_pub.get_schedule(p_api_version => 1.0
778 ,p_init_msg_list => 'F'
779 ,p_object_type => 'HR_LOCATION'
780 ,p_object_id => l_loc_id
781 ,p_start_date_time => p_period_start_date
782 ,p_end_date_time => p_period_end_date
783 ,p_schedule_category => p_schedule_category
784 ,p_include_exception => l_include_exceptions
785 ,p_busy_tentative => l_busy_tentative_as
786 ,x_schedule => x_schedule
787 ,x_return_status => l_return_status
788 ,x_msg_count => l_msg_count
789 ,x_msg_data => l_msg_data
790 );
791 hr_utility.set_location('SchCnt:'||x_schedule.COUNT, 115);
792 END CASE;
793
794 -- Loop exit conditions
795 IF x_schedule.COUNT > 1 OR (x_schedule.COUNT = 1 AND x_schedule(1).period_name IS NOT NULL) THEN
796 l_wrk_sch_found := TRUE;
797 END IF;
798 -- Check if schedule found with no working time.
799 IF x_schedule.COUNT = 1 AND x_schedule(1).period_name IS NULL THEN
800 -- Either schedule is found with no working time or schedule is not found.
801 -- Test if work schedule exists at PER ASG level
802 CASE x_schedule_source
803 WHEN 'BUS_GRP' THEN
804 OPEN c_sch_found ('BUSINESS_GROUP'
805 ,l_bus_grp_id
806 ,p_period_start_date
807 ,p_period_end_date
808 ,p_schedule_category
809 );
810 FETCH c_sch_found INTO l_wrk_sch_count;
811 CLOSE c_sch_found;
812 IF l_wrk_sch_count > 0 THEN
813 l_wrk_sch_found := TRUE;
814 END IF;
815 --
816 hr_utility.set_location('BusGrp SchCnt:'||l_wrk_sch_count, 117);
817 --
818 WHEN 'HR_ORG' THEN
819 OPEN c_sch_found ('HR_ORGANIZATION'
820 ,l_hr_org_id
821 ,p_period_start_date
822 ,p_period_end_date
823 ,p_schedule_category
824 );
825 FETCH c_sch_found INTO l_wrk_sch_count;
826 CLOSE c_sch_found;
827 IF l_wrk_sch_count > 0 THEN
828 l_wrk_sch_found := TRUE;
829 END IF;
830 --
831 hr_utility.set_location('HROrg SchCnt:'||l_wrk_sch_count, 117);
832 --
833 WHEN 'JOB' THEN
834 OPEN c_sch_found ('HR_JOB'
835 ,l_job_id
836 ,p_period_start_date
837 ,p_period_end_date
838 ,p_schedule_category
839 );
840 FETCH c_sch_found INTO l_wrk_sch_count;
841 CLOSE c_sch_found;
842 IF l_wrk_sch_count > 0 THEN
843 l_wrk_sch_found := TRUE;
844 END IF;
845 --
846 hr_utility.set_location('HRJob SchCnt:'||l_wrk_sch_count, 117);
847 --
848 WHEN 'POS' THEN
849 OPEN c_sch_found ('HR_POSITION'
850 ,l_pos_id
851 ,p_period_start_date
852 ,p_period_end_date
853 ,p_schedule_category
854 );
855 FETCH c_sch_found INTO l_wrk_sch_count;
856 CLOSE c_sch_found;
857 IF l_wrk_sch_count > 0 THEN
858 l_wrk_sch_found := TRUE;
859 END IF;
860 --
861 hr_utility.set_location('HRPos SchCnt:'||l_wrk_sch_count, 117);
862 --
863 WHEN 'LOC' THEN
864 OPEN c_sch_found ('HR_LOCATION'
865 ,l_loc_id
866 ,p_period_start_date
867 ,p_period_end_date
868 ,p_schedule_category
869 );
870 FETCH c_sch_found INTO l_wrk_sch_count;
871 CLOSE c_sch_found;
872 IF l_wrk_sch_count > 0 THEN
873 l_wrk_sch_found := TRUE;
874 END IF;
875 --
876 hr_utility.set_location('HRLoc SchCnt:'||l_wrk_sch_count, 117);
877 --
878 END CASE;
879 END IF;
880 --
881 l_sch_inh_seq := l_sch_inh_seq - 1;
882 END LOOP;
883 END IF; -- Explicit schedule not found
884
885 IF NOT l_wrk_sch_found THEN
886 x_schedule_source := '';
887 END IF;
888
889 IF l_return_status = 'S' THEN
890 x_return_status := 0;
891 END IF;
892
893 hr_utility.set_location('Leaving: '|| l_proc, 120);
894
895 EXCEPTION
896 WHEN e_invalid_params THEN
897 hr_utility.set_location('Leaving: '|| l_proc, 130);
898 hr_utility.set_location(SQLERRM, 135);
899 x_return_status := 1;
900
901 WHEN overlapped THEN
902 hr_utility.set_location('raising: '|| l_proc, 125);
903 fnd_message.set_name('PER', 'HR_449835_ABS_SCHEDULE_OVERLAP');
904 /* Commented and added as a part of Bug#10239011 Starts
905 fnd_message.set_token('STARTDATE', fnd_date.date_to_chardate(l_sch_start_date));
906 fnd_message.set_token('ENDDATE', fnd_date.date_to_chardate(l_sch_end_date)); */
907 fnd_message.set_token('STARTDATE', fnd_date.date_to_chardate(l_sch_start_date, calendar_aware => FND_DATE.calendar_aware_alt));
908 fnd_message.set_token('ENDDATE', fnd_date.date_to_chardate(l_sch_end_date, calendar_aware => FND_DATE.calendar_aware_alt));
909 /* Commented and added as a part of Bug#10239011 Ends */
910 fnd_message.raise_error;
911
912 WHEN OTHERS THEN
913 hr_utility.set_location('Leaving: '|| l_proc, 140);
914 hr_utility.set_location(SQLERRM, 145);
915 x_return_status := 2;
916 x_return_message := SQLERRM;
917
918 END get_per_asg_schedule;
919
920 --
921 -----------------------------------------------------------------------------
922 --------------------------< get_working_day >---------------------------
923 -----------------------------------------------------------------------------
924 --
925 -- input parameters : Valid value for prev_next flag is N and P
926 -- returns the next/previous working day for a leave request.
927
928 function get_working_day
929 (
930 p_person_assignment_id IN NUMBER
931 ,loa_start_date in DATE
932 ,loa_end_date in DATE
933 ,prev_next_flag in VARCHAR2 default 'N'
934
935 ) return DATE IS
936
937 l_working_date DATE;
938 l_flag Boolean;
939 l_schedule_source varchar2(1000);
940 l_schedule cac_avlblty_time_varray;
941 l_return_status number;
942 l_return_message varchar2(1000);
943 l_busy_tentative_as varchar2(30);
944 l_wrk_sch_found boolean;
945 l_free_busy varchar2(30);
946 l_count NUMBER;
947
948
949
950 BEGIN
951
952 l_flag := true;
953 l_return_status := 0;
954 l_schedule := cac_avlblty_time_varray();
955 l_schedule_source := 'PER_ASG';
956 l_busy_tentative_as := NVL(l_busy_tentative_as, 'BUSY');
957 l_wrk_sch_found := false;
958 l_count := 0;
959
960 if prev_next_flag = 'N' then
961 l_working_date := loa_end_date + 1;
962 else
963 l_working_date := loa_start_date -1 ;
964 end if;
965
966 while l_flag LOOP
967 --get the schedule
968 l_count := l_count +1;
969 get_per_asg_schedule(p_person_assignment_id
970 ,l_working_date
971 ,l_working_date+1
972 ,null
973 ,null
974 ,l_busy_tentative_as
975 ,l_schedule_source
976 ,l_schedule
977 ,l_return_status
978 ,l_return_message
979 );
980
981 IF l_schedule.COUNT > 1 or (l_schedule.COUNT =1 and l_schedule(1).period_name is not null) THEN
982 l_wrk_sch_found := TRUE;
983 END IF;
984
985 if l_wrk_sch_found then
986 for i in 1..l_schedule.COUNT loop
987 l_free_busy := l_schedule(i).FREE_BUSY_TYPE ;
988 if l_free_busy is not null and l_free_busy = 'FREE' then
989 -- found the working day
990 l_flag := false;
991 exit ;
992 end if;
993 end loop;
994 end if;
995
996 if l_flag then
997 if prev_next_flag = 'N' then
998 l_working_date := l_working_date + 1;
999 else
1000 l_working_date := l_working_date -1;
1001 end if;
1002 end if ;
1003
1004 if l_count > 50 then
1005 -- this condition has been added as an exception condition
1006 -- to prevent an infinite loop.
1007 return null;
1008 end if;
1009
1010
1011 END LOOP;
1012 return l_working_date;
1013
1014 END get_working_day; --end of function
1015
1016 END hr_wrk_sch_pkg;