1 PACKAGE BODY hr_cal_event_mapping_pkg AS
2 -- $Header: pecalmap.pkb 120.8 2009/11/04 14:02:31 tkghosh ship $
3
4 --
5 -----------------------------------------------------------------------------
6 ---------------------------< populate_org_list >-----------------------------
7 -----------------------------------------------------------------------------
8 --
9 -- Private procedure to build mapping data for ORG Hierarchy based calendar
10 -- events. This procedure refreshes data in table PER_CAL_ENTRY_ORG_LIST.
11 --
12 PROCEDURE populate_org_list IS
13 --
14 -- Cursor to get unique list of OSVs that have calendar enrty coverages
15 -- data defined.
16 --
17 CURSOR c_osv_ids IS
18 SELECT DISTINCT ENT.org_structure_version_id "OSV_ID"
19 FROM per_calendar_entries ENT
20 WHERE ENT.org_structure_version_id IS NOT NULL
21 AND EXISTS (SELECT 'x'
22 FROM per_cal_entry_values ENV
23 WHERE ENV.calendar_entry_id = ENT.calendar_entry_id
24 );
25 --
26 -- Cursor to fetch the top of the hierarchy
27 --
28 CURSOR c_org_hier_top (cp_osv_id NUMBER
29 ) IS
30 SELECT DISTINCT organization_id_parent
31 FROM per_org_structure_elements
32 WHERE org_structure_version_id = cp_osv_id
33 AND organization_id_parent NOT IN
34 (SELECT organization_id_child
35 FROM per_org_structure_elements
36 WHERE org_structure_version_id = cp_osv_id
37 );
38 --
39 -- Cursor to walk the ORG hierarchy to return all nodes and levels
40 --
41 CURSOR c_org_hier_elements (cp_osv_id NUMBER
42 ,cp_organization_id_parent NUMBER
43 ) IS
44 SELECT LEVEL
45 ,org_structure_version_id
46 ,org_structure_element_id
47 ,organization_id_parent
48 ,organization_id_child
49 FROM per_org_structure_elements
50 WHERE org_structure_version_id = cp_osv_id
51 START WITH organization_id_parent = cp_organization_id_parent
52 CONNECT BY PRIOR organization_id_child = organization_id_parent;
53 --
54 -- Cursor to fetch calendar events on ORG Hierarchy
55 --
56 CURSOR c_org_events (cp_osv_id NUMBER
57 ) IS
58 SELECT ENT.calendar_entry_id
59 ,ENT.org_structure_version_id
60 ,ENV.org_structure_element_id
61 ,ENV.organization_id
62 ,ENV.usage_flag
63 ,DECODE(ENV.usage_flag,
64 'N','COV',
65 'Y','EXC',
66 'O','OVR') "USAGE"
67 ,ENV.cal_entry_value_id
68 ,ENV.parent_entry_value_id
69 ,'' "ENTRY_FLAG"
70 FROM per_calendar_entries ENT
71 ,per_cal_entry_values ENV
72 WHERE ENT.org_structure_version_id = cp_osv_id
73 AND ENT.calendar_entry_id = ENV.calendar_entry_id
74 AND ENV.org_structure_element_id IS NOT NULL
75 AND ENV.organization_id IS NOT NULL
76 ORDER BY ENT.calendar_entry_id
77 -- ,DECODE(ENV.usage_flag,
78 -- 'N',1,
79 -- 'O',2,
80 -- 'Y',3)
81 ;
82
83 TYPE t_org_hier_table IS TABLE OF c_org_hier_elements%ROWTYPE INDEX BY BINARY_INTEGER;
84 TYPE t_cal_event_table IS TABLE OF c_org_events%ROWTYPE INDEX BY BINARY_INTEGER;
85 TYPE t_org_id_table IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
86
87 -- Local variables for process_org_list
88 l_org_hier_table t_org_hier_table;
89 l_cal_event_table t_cal_event_table;
90 l_top_org_id per_org_structure_elements.organization_id_parent%TYPE;
91 l_proc VARCHAR2(100);
92
93 --
94 -------------------------------------------------------------------------
95 ---------------------< get_org_hier_elements >---------------------------
96 -------------------------------------------------------------------------
97 --
98 -- Private procedure to manually walk the ORG hierarchy as issues have
99 -- been detected with the inherent tree walk feature in 9i
100 --
101 PROCEDURE get_org_hier_elements(p_osv_id IN NUMBER
102 ,p_top_org_id IN NUMBER
103 ,p_org_hier_table IN OUT NOCOPY t_org_hier_table
104 ) IS
105 l_stack_table t_org_hier_table;
106 l_level NUMBER;
107 l_stack_index NUMBER;
108 l_main_index NUMBER;
109 l_child_id NUMBER;
110 l_proc VARCHAR2(100);
111
112 -- Tree walk cursor
113 CURSOR c_org_tree_walk (cp_osv_id IN NUMBER
114 ,cp_par_org_id IN NUMBER
115 ) IS
116 SELECT l_level "LEVEL"
117 ,org_structure_version_id
118 ,org_structure_element_id
119 ,organization_id_parent
120 ,organization_id_child
121 FROM per_org_structure_elements
122 WHERE org_structure_version_id = cp_osv_id
123 AND organization_id_parent = cp_par_org_id;
124 BEGIN
125 l_proc := 'HR_CAL_EVENT_MAPPING_PKG.get_org_hier_elements';
126 hr_utility.set_location('Entering '||l_proc,10);
127
128 -- Populate hierarchy Level 1 to stack
129 l_level := 1;
130 l_stack_index := 0;
131 l_main_index := 0;
132 FOR l_org_hier_rec IN c_org_tree_walk(p_osv_id,p_top_org_id) LOOP
133 hr_utility.set_location(l_proc,15);
134 l_stack_index := l_stack_index + 1;
135 l_stack_table(l_stack_index) := l_org_hier_rec;
136 END LOOP;
137
138 hr_utility.set_location(l_proc,20);
139
140 -- Work from stack table
141 WHILE l_stack_index > 0 LOOP
142 hr_utility.set_location(l_proc,24);
143 -- Get node from stack
144 l_main_index := l_main_index + 1;
145 p_org_hier_table(l_main_index) := l_stack_table(l_stack_index);
146 l_child_id := l_stack_table(l_stack_index).organization_id_child;
147 l_level := l_stack_table(l_stack_index).level;
148 l_stack_index := l_stack_index - 1;
149 -- Fetch children from stack
150 l_level := l_level + 1;
151 FOR l_org_hier_rec IN c_org_tree_walk(p_osv_id,l_child_id) LOOP
152 hr_utility.set_location(l_proc,28);
153 l_stack_index := l_stack_index + 1;
154 l_stack_table(l_stack_index) := l_org_hier_rec;
155 END LOOP;
156 END LOOP;
157 --
158 hr_utility.set_location('Count: '||p_org_hier_table.COUNT,30);
159 hr_utility.set_location('Leaving '||l_proc,40);
160 EXCEPTION
161 WHEN OTHERS THEN
162 hr_utility.set_location('Leaving '||l_proc,50);
163 END get_org_hier_elements;
164
165 --
166 -------------------------------------------------------------------------
167 -------------------------< write_org_cache >-----------------------------
168 -------------------------------------------------------------------------
169 --
170 -- Private procedure to write calendar event ORG mapping records to the
171 -- ORG cache table
172 --
173 PROCEDURE write_org_cache(p_coverage_list t_org_id_table
174 ,p_calendar_entry_id NUMBER
175 ) IS
176
177 -- Cursor to fetch override identifier (if any)
178 CURSOR c_org_ovr ( cp_calendar_entry_id NUMBER
179 , cp_organization_id NUMBER
180 ) IS
181 SELECT cal_entry_value_id
182 FROM per_cal_entry_values
183 WHERE usage_flag = 'O'
184 AND calendar_entry_id = cp_calendar_entry_id
185 AND organization_id = cp_organization_id;
186
187 l_ovr_id per_cal_entry_values.cal_entry_value_id%TYPE;
188 l_proc VARCHAR2(100);
189
190 BEGIN
191 l_proc := 'HR_CAL_EVENT_MAPPING_PKG.write_org_cache';
192 hr_utility.set_location('Entering '||l_proc,10);
193
194 IF p_coverage_list.COUNT > 0 THEN
195 hr_utility.set_location(l_proc,12);
196 FOR idx IN p_coverage_list.FIRST .. p_coverage_list.LAST LOOP
197 hr_utility.set_location(l_proc,14);
198 l_ovr_id := NULL;
199
200 -- Get override (if any)
201 OPEN c_org_ovr (p_calendar_entry_id
202 ,p_coverage_list(idx)
203 );
204 FETCH c_org_ovr INTO l_ovr_id;
205 CLOSE c_org_ovr;
206
207 hr_utility.set_location(l_proc,18);
208
209 INSERT INTO per_cal_entry_org_list
210 (calendar_entry_id
211 ,organization_id
212 ,ovr_cal_entry_value_id
213 ,last_update_date
214 ,last_updated_by
215 ,last_update_login
216 ,created_by
217 ,creation_date
218 )
219 VALUES
220 (p_calendar_entry_id
221 ,p_coverage_list(idx)
222 ,l_ovr_id
223 ,TRUNC(SYSDATE)
224 ,0
225 ,0
226 ,0
227 ,TRUNC(SYSDATE)
228 );
229 END LOOP;
230 END IF;
231
232 COMMIT;
233
234 hr_utility.set_location('Leaving '||l_proc,20);
235 END write_org_cache;
236
237 --
238 -------------------------------------------------------------------------
239 --------------------------< is_org_in_list >-----------------------------
240 -------------------------------------------------------------------------
241 --
242 -- Private procedure to check is a given org is in the given list
243 --
244 FUNCTION is_org_in_list(p_org_id NUMBER
245 ,p_org_list t_org_id_table
246 ) RETURN BOOLEAN IS
247 l_return BOOLEAN;
248 l_proc VARCHAR2(100);
249 BEGIN
250 l_proc := 'HR_CAL_EVENT_MAPPING_PKG.is_org_in_list';
251 hr_utility.set_location('Entering '||l_proc,10);
252 l_return := FALSE;
253
254 IF p_org_list.COUNT > 0 THEN
255 hr_utility.set_location(l_proc,13);
256 FOR idx IN p_org_list.FIRST .. p_org_list.LAST LOOP
257 hr_utility.set_location(l_proc,15);
258 IF p_org_list(idx) = p_org_id THEN
259 l_return := TRUE;
260 EXIT;
261 END IF;
262 END LOOP;
263 END IF;
264
265 hr_utility.set_location('Leaving '||l_proc,20);
266
267 RETURN l_return;
268 END is_org_in_list;
269
270 --
271 -------------------------------------------------------------------------
272 -----------------------< get_child_org_nodes >---------------------------
273 -------------------------------------------------------------------------
274 --
275 -- Private procedure to get the child coverage records for a given event
276 --
277 FUNCTION get_child_org_nodes(p_coverage_org_id NUMBER
278 ,p_exclusion_list t_org_id_table
279 ,p_org_hier_table t_org_hier_table
280 ) RETURN t_org_id_table IS
281 l_result_list t_org_id_table;
282 l_result_count NUMBER;
283 l_coverage_top_level NUMBER;
284 l_exclusion_level NUMBER;
285 l_parent_found BOOLEAN;
286 l_exclude_flag BOOLEAN;
287 l_hier_top_case BOOLEAN;
288 l_proc VARCHAR2(100);
289
290 BEGIN
291 l_proc := 'HR_CAL_EVENT_MAPPING_PKG.get_child_org_nodes';
292 hr_utility.set_location('Entering '||l_proc,5);
293 l_result_count := 0;
294 l_parent_found := FALSE;
295 l_exclude_flag := FALSE;
296 l_coverage_top_level := 1;
297 l_exclusion_level := 0;
298 l_hier_top_case := FALSE;
299
300 IF p_org_hier_table.COUNT > 0 THEN
301 hr_utility.set_location(l_proc,10);
302 -- Loop through the org hier tree elements
303 FOR idx IN p_org_hier_table.FIRST .. p_org_hier_table.LAST LOOP
304 hr_utility.set_location(l_proc,11);
305
306 -- Due to the nature of results returned by a tree walk, the first
307 -- record will always be a top node in the hierarchy. Check is the
308 -- ORG at the hierarchy top is in coverage.
309 IF idx = 1 AND p_org_hier_table(idx).organization_id_parent = p_coverage_org_id THEN
310 hr_utility.set_location(l_proc,12);
311 -- This is the first OSE for the COV as a special case where parent
312 -- ORG is in coverage.
313 l_parent_found := TRUE;
314 l_result_count := l_result_count + 1;
315 l_result_list(l_result_count) := p_org_hier_table(idx).organization_id_parent;
316 l_coverage_top_level := p_org_hier_table(idx).level;
317 l_hier_top_case := TRUE;
318 END IF; -- top node
319
320 IF p_org_hier_table(idx).organization_id_child = p_coverage_org_id THEN
321 hr_utility.set_location(l_proc,13);
322 -- This is the first OSE for the COV
323 l_parent_found := TRUE;
324 l_result_count := l_result_count + 1;
325 l_result_list(l_result_count) := p_org_hier_table(idx).organization_id_child;
326 l_coverage_top_level := p_org_hier_table(idx).level;
327 ELSE -- not first OSE
328 hr_utility.set_location(l_proc,14);
329 IF l_parent_found THEN
330
331 -- Check if we have moved back up the tree beyond coverage level
332 IF p_org_hier_table(idx).level <= l_coverage_top_level AND NOT l_hier_top_case THEN
333 -- Stop as we have moved up the tree again
334 hr_utility.set_location(l_proc,15);
335 EXIT; -- break the loop
336 END IF; -- level check
337
338 -- Check if we need to stop exclusions due to moving up the tree
339 -- beyond exclusion level
340 IF l_exclude_flag AND l_exclusion_level >= p_org_hier_table(idx).level THEN
341 hr_utility.set_location(l_proc,16);
342 l_exclude_flag := FALSE;
343 END IF;
344
345 -- Check for ORG exclusion
346 IF NOT l_exclude_flag AND p_exclusion_list.COUNT > 0 THEN
347 hr_utility.set_location(l_proc,17);
348 l_exclude_flag := is_org_in_list(p_org_hier_table(idx).organization_id_child
349 ,p_exclusion_list
350 );
351 IF l_exclude_flag THEN
352 hr_utility.set_location(l_proc,18);
353 -- Note the exclusion level
354 l_exclusion_level := p_org_hier_table(idx).level;
355 END IF; -- exclusion
356 END IF; -- check ORG exclusion
357
358 -- Check if ORG is in coverage and save
359 IF NOT l_exclude_flag THEN
360 hr_utility.set_location(l_proc,19);
361 l_result_count := l_result_count + 1;
362 l_result_list(l_result_count) := p_org_hier_table(idx).organization_id_child;
363 END IF; -- ORG is in coverage
364
365 END IF; -- first OSE found
366 END IF; -- first OSE
367
368 END LOOP; -- org hier elements
369 END IF; -- count
370
371 hr_utility.set_location('Leaving '||l_proc,20);
372
373 RETURN l_result_list;
374 END get_child_org_nodes;
375
376 --
377 -------------------------------------------------------------------------
378 -----------------------< process_org_coverage >--------------------------
379 -------------------------------------------------------------------------
380 --
381 -- Private procedure to build mapping data
382 --
383 PROCEDURE process_org_coverage(p_org_hier_table t_org_hier_table
384 ,p_cal_event_table t_cal_event_table
385 ) IS
386 -- Local variables for process_org_coverage
387 l_exc_count NUMBER;
388 l_coverage_org_id NUMBER;
389 l_coverage_list t_org_id_table;
390 l_exclusion_list t_org_id_table;
391 l_proc VARCHAR2(100);
392 BEGIN
393 l_proc := 'HR_CAL_EVENT_MAPPING_PKG.process_org_coverage';
394 hr_utility.set_location('Entering '||l_proc,10);
395 l_exc_count := 0;
396 IF p_cal_event_table.COUNT > 0 THEN
397 hr_utility.set_location(l_proc,20);
398 -- Loop for each entry in the calendar entry table passed in
399 FOR idx IN p_cal_event_table.FIRST .. p_cal_event_table.LAST LOOP
400 hr_utility.set_location(l_proc,21);
401 IF p_cal_event_table(idx).usage = 'COV' THEN
402 hr_utility.set_location(l_proc,22);
403 -- This is the start of a coverage
404 l_coverage_org_id := p_cal_event_table(idx).organization_id;
405 ELSIF p_cal_event_table(idx).usage = 'EXC' THEN
406 hr_utility.set_location(l_proc,23);
407 -- Note the exclusion org
408 l_exc_count := l_exc_count + 1;
409 l_exclusion_list(l_exc_count) := p_cal_event_table(idx).organization_id;
410 END IF;
411 -- If end of coverage, process the coverage
412 IF p_cal_event_table(idx).entry_flag = 'E' THEN
413 hr_utility.set_location(l_proc,24);
414 l_coverage_list := get_child_org_nodes(l_coverage_org_id
415 ,l_exclusion_list
416 ,p_org_hier_table
417 );
418 -- If coverage rows returned, write to cache
419 IF l_coverage_list.COUNT > 0 THEN
420 hr_utility.set_location(l_proc,25);
421 write_org_cache(l_coverage_list
422 ,p_cal_event_table(idx).calendar_entry_id
423 );
424 END IF; -- coverage rows returned
425
426 hr_utility.set_location(l_proc,26);
427
428 -- Reset local variables for next calendar event
429 l_exc_count := 0;
430 l_coverage_org_id := NULL;
431 l_coverage_list.DELETE;
432 l_exclusion_list.DELETE;
433
434 END IF; -- end of coverage
435 END LOOP; -- event loop
436 END IF; -- count of events to process
437 hr_utility.set_location('Leaving '||l_proc,30);
438 END process_org_coverage;
439
440 BEGIN -- populate_org_list
441 l_proc := 'HR_CAL_EVENT_MAPPING_PKG.populate_org_list';
442 hr_utility.set_location('Entering '||l_proc,10);
443
444 -- Get identifiers for all the hierarchies that need to be processed
445 FOR l_osv_rec IN c_osv_ids LOOP
446 hr_utility.set_location('OSVId:'||l_osv_rec.osv_id,20);
447
448 -- Get the top of the associated ORG hierarchy
449 OPEN c_org_hier_top(l_osv_rec.osv_id);
450 FETCH c_org_hier_top INTO l_top_org_id;
451 CLOSE c_org_hier_top;
452
453 hr_utility.set_location('TopOrgId:'||l_top_org_id,30);
454
455 -- Clear down PLSQL tables
456 IF l_org_hier_table.COUNT > 0 THEN
457 l_org_hier_table.DELETE;
458 END IF;
459 IF l_cal_event_table.COUNT > 0 THEN
460 l_cal_event_table.DELETE;
461 END IF;
462
463 hr_utility.set_location(l_proc,35);
464
465 -- Get the elements of the associated ORG hierarchy
466 -- FOR l_org_hier_element_rec IN c_org_hier_elements(l_osv_rec.osv_id
467 -- ,l_top_org_id
468 -- ) LOOP
469 -- -- Store the ORG Hierarchy element
470 -- l_org_hier_table(c_org_hier_elements%ROWCOUNT) := l_org_hier_element_rec;
471 -- END LOOP; -- c_org_hier_elements
472 -- Using manual tree walk as inconsistent behaviour has been found with the
473 -- hierarchical tree walk feature in 9i.
474 get_org_hier_elements(l_osv_rec.osv_id
475 ,l_top_org_id
476 ,l_org_hier_table
477 );
478
479 hr_utility.set_location(l_proc,40);
480
481 -- Get the calendar events for the ORG Hierarchy
482 FOR l_org_event_rec IN c_org_events(l_osv_rec.osv_id) LOOP
483 hr_utility.set_location(l_proc,50);
484
485 -- Store the event
486 l_cal_event_table(c_org_events%ROWCOUNT) := l_org_event_rec;
487
488 IF c_org_events%ROWCOUNT > 1 THEN
489 hr_utility.set_location(l_proc,60);
490
491 -- This is not the first record. Check if different from the
492 -- previous event or if same event and end of coverage
493 IF (l_org_event_rec.calendar_entry_id <> l_cal_event_table(c_org_events%ROWCOUNT-1).calendar_entry_id)
494 OR
495 (l_org_event_rec.calendar_entry_id = l_cal_event_table(c_org_events%ROWCOUNT-1).calendar_entry_id
496 AND l_org_event_rec.usage_flag = 'N')
497 THEN
498 hr_utility.set_location(l_proc,70);
499
500 -- Mark end of old event coverage and start of new event coverage
501 l_cal_event_table(c_org_events%ROWCOUNT-1).entry_flag := 'E';
502 l_cal_event_table(c_org_events%ROWCOUNT).entry_flag := 'S';
503 END IF;
504
505 ELSE
506 hr_utility.set_location(l_proc,80);
507
508 -- This is the first record. Mark it as coverage start.
509 l_cal_event_table(c_org_events%ROWCOUNT).entry_flag := 'S';
510 END IF;
511
512 END LOOP; -- c_org_events
513
514 -- Mark end of coverage for the last row
515 IF l_cal_event_table.COUNT > 0 THEN
516 hr_utility.set_location(l_proc,90);
517 l_cal_event_table(l_cal_event_table.LAST).entry_flag := 'E';
518 END IF;
519
520 hr_utility.set_location(l_proc,100);
521
522 -- Process coverage data
523 process_org_coverage(l_org_hier_table
524 ,l_cal_event_table
525 );
526 END LOOP; -- c_osv_ids
527
528 hr_utility.set_location('Leaving '||l_proc,110);
529 END populate_org_list;
530
531 --
532 -----------------------------------------------------------------------------
533 ---------------------------< populate_geo_list >-----------------------------
534 -----------------------------------------------------------------------------
535 --
536 -- Private procedure to build mapping data for GEO Hierarchy based calendar
537 -- events. This procedure refreshes data in table PER_CAL_ENTRY_GEO_LIST.
538 --
539 PROCEDURE populate_geo_list IS
540
541 -- Cursor to get unique list of GHVs that have calendar entry coverages
542 -- data defined.
543 CURSOR c_ghv_ids IS
544 SELECT DISTINCT ENT.hierarchy_id "GHV_ID"
545 FROM per_calendar_entries ENT
546 WHERE ENT.hierarchy_id IS NOT NULL
547 AND EXISTS (SELECT 'x'
548 FROM per_cal_entry_values ENV
549 WHERE ENV.calendar_entry_id = ENT.calendar_entry_id
550 );
551
552 -- Cursor to fetch the version id of GHV
553 CURSOR c_ghv_ver_id (cp_ghv_id NUMBER
554 ) IS
555 SELECT hierarchy_version_id
556 FROM per_gen_hierarchy_versions
557 WHERE hierarchy_id = cp_ghv_id
558 AND version_number = (SELECT MAX(version_number)
559 FROM per_gen_hierarchy_versions
560 WHERE hierarchy_id = cp_ghv_id);
561
562 -- Cursor to fetch the top of the hierarchy
563 CURSOR c_geo_hier_top (cp_ghv_id NUMBER
564 ) IS
565 SELECT hierarchy_node_id
566 FROM per_gen_hierarchy_nodes
567 WHERE hierarchy_version_id = cp_ghv_id
568 AND parent_hierarchy_node_id IS NULL;
569
570 -- Cursor to walk the GEO hierarchy and return all nodes and levels
571 CURSOR c_geo_hier_nodes (cp_ghv_id NUMBER
572 ,cp_top_node_id NUMBER
573 ) IS
574 SELECT LEVEL
575 ,hierarchy_version_id
576 ,hierarchy_node_id
577 ,entity_id
578 FROM per_gen_hierarchy_nodes
579 WHERE hierarchy_version_id = cp_ghv_id
580 START WITH hierarchy_node_id = cp_top_node_id
581 CONNECT BY PRIOR hierarchy_node_id = parent_hierarchy_node_id;
582
583 -- Cursor to fetch calendar events on GEO Hierarchy
584 CURSOR c_geo_events (cp_ghv_id NUMBER
585 ) IS
586 SELECT ENT.calendar_entry_id
587 ,ENT.hierarchy_id
588 ,ENV.hierarchy_node_id
589 ,ENV.usage_flag
590 ,DECODE(ENV.usage_flag,
591 'N','COV',
592 'Y','EXC',
593 'O','OVR') "USAGE"
594 ,ENV.cal_entry_value_id
595 ,ENV.parent_entry_value_id
596 ,'' "ENTRY_FLAG"
597 FROM per_calendar_entries ENT
598 ,per_cal_entry_values ENV
599 WHERE ENT.hierarchy_id = cp_ghv_id
600 AND ENT.calendar_entry_id = ENV.calendar_entry_id
601 AND ENV.hierarchy_node_id IS NOT NULL
602 ORDER BY ENT.calendar_entry_id
603 ,DECODE(ENV.usage_flag,
604 'N',1,
605 'O',2,
606 'Y',3)
607 ,hierarchy_node_id;
608
609 TYPE t_geo_hier_table IS TABLE OF c_geo_hier_nodes%ROWTYPE INDEX BY BINARY_INTEGER;
610 TYPE t_cal_event_table IS TABLE OF c_geo_events%ROWTYPE INDEX BY BINARY_INTEGER;
611 TYPE t_node_id_table IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
612
613 -- Local variables for process_geo_list
614 l_geo_hier_table t_geo_hier_table;
615 l_cal_event_table t_cal_event_table;
616 l_top_node_id per_gen_hierarchy_nodes.hierarchy_node_id%TYPE;
617 l_ghv_ver_id per_gen_hierarchy_versions.hierarchy_version_id%TYPE;
618 l_proc VARCHAR2(100);
619
620 --
621 -------------------------------------------------------------------------
622 -------------------------< write_geo_cache >-----------------------------
623 -------------------------------------------------------------------------
624 --
625 -- Private procedure to write calendar event GEO mapping records to the
626 -- GEO cache table
627 --
628 PROCEDURE write_geo_cache(p_coverage_list t_node_id_table
629 ,p_calendar_entry_id NUMBER
630 ) IS
631
632 -- Cursor to fetch override identifier (if any)
633 CURSOR c_geo_ovr ( cp_calendar_entry_id NUMBER
634 , cp_hierarchy_node_id NUMBER
635 ) IS
636 SELECT cal_entry_value_id
637 FROM per_cal_entry_values
638 WHERE usage_flag = 'O'
639 AND calendar_entry_id = cp_calendar_entry_id
640 AND hierarchy_node_id = cp_hierarchy_node_id;
641
642 l_ovr_id per_cal_entry_values.cal_entry_value_id%TYPE;
643 l_proc VARCHAR2(100);
644
645 BEGIN
646 l_proc := 'HR_CAL_EVENT_MAPPING_PKG.write_geo_cache';
647 hr_utility.set_location('Entering '||l_proc,10);
648
649 IF p_coverage_list.COUNT > 0 THEN
650 hr_utility.set_location(l_proc,12);
651 FOR idx IN p_coverage_list.FIRST .. p_coverage_list.LAST LOOP
652 hr_utility.set_location(l_proc,14);
653 hr_utility.set_location('p_calendar_entry_id:'||p_calendar_entry_id,15);
654 hr_utility.set_location('p_coverage_list:'||p_coverage_list(idx),16);
655 l_ovr_id := NULL;
656
657 -- Get override (if any)
658 OPEN c_geo_ovr (p_calendar_entry_id
659 ,p_coverage_list(idx)
660 );
661 FETCH c_geo_ovr INTO l_ovr_id;
662 CLOSE c_geo_ovr;
663
664 hr_utility.set_location(l_proc,18);
665
666 INSERT INTO per_cal_entry_geo_list
667 (calendar_entry_id
668 ,hierarchy_node_id
669 ,ovr_cal_entry_value_id
670 ,last_update_date
671 ,last_updated_by
672 ,last_update_login
673 ,created_by
674 ,creation_date
675 )
676 VALUES
677 (p_calendar_entry_id
678 ,p_coverage_list(idx)
679 ,l_ovr_id
680 ,TRUNC(SYSDATE)
681 ,0
682 ,0
683 ,0
684 ,TRUNC(SYSDATE)
685 );
686 END LOOP;
687 END IF;
688
689 COMMIT;
690
691 hr_utility.set_location('Leaving '||l_proc,20);
692 END write_geo_cache;
693
694 --
695 -------------------------------------------------------------------------
696 -------------------------< is_node_in_list >-----------------------------
697 -------------------------------------------------------------------------
698 --
699 -- Private procedure to check is a given node is in the given list
700 --
701 FUNCTION is_node_in_list(p_node_id NUMBER
702 ,p_node_list t_node_id_table
703 ) RETURN BOOLEAN IS
704 l_return BOOLEAN;
705 l_proc VARCHAR2(100);
706 BEGIN
707 l_proc := 'HR_CAL_EVENT_MAPPING_PKG.is_node_in_list';
708 hr_utility.set_location('Entering '||l_proc,10);
709 hr_utility.set_location('p_node_id:'||p_node_id,11);
710 l_return := FALSE;
711
712 IF p_node_list.COUNT > 0 THEN
713 hr_utility.set_location(l_proc,13);
714 FOR idx IN p_node_list.FIRST .. p_node_list.LAST LOOP
715 hr_utility.set_location(l_proc,15);
716 IF p_node_list(idx) = p_node_id THEN
717 l_return := TRUE;
718 EXIT;
719 END IF;
720 END LOOP;
721 END IF;
722
723 hr_utility.set_location('Leaving '||l_proc,20);
724
725 RETURN l_return;
726 END is_node_in_list;
727
728 --
729 -------------------------------------------------------------------------
730 -------------------------< get_child_nodes >-----------------------------
731 -------------------------------------------------------------------------
732 --
733 -- Private procedure to get the child coverage records for a given event
734 --
735 FUNCTION get_child_nodes(p_coverage_node_id NUMBER
736 ,p_exclusion_list t_node_id_table
737 ,p_geo_hier_table t_geo_hier_table
738 ,p_coverage t_node_id_table
739 ) RETURN t_node_id_table IS
740 l_result_list t_node_id_table;
741 l_result_count NUMBER;
742 l_coverage_top_level NUMBER;
743 l_exclusion_level NUMBER;
744 l_parent_found BOOLEAN;
745 l_exclude_flag BOOLEAN;
746 l_proc VARCHAR2(100);
747
748 BEGIN
749 l_proc := 'HR_CAL_EVENT_MAPPING_PKG.get_child_nodes';
750 hr_utility.set_location('Entering '||l_proc,10);
751
752 l_result_count := 0;
753 l_parent_found := FALSE;
754 l_exclude_flag := FALSE;
755 l_coverage_top_level := 1;
756 l_exclusion_level := 0;
757
758 IF p_geo_hier_table.COUNT > 0 THEN
759 hr_utility.set_location(l_proc,15);
760
761 -- Loop through the geo hier tree nodes
762 FOR idx IN p_geo_hier_table.FIRST .. p_geo_hier_table.LAST LOOP
763 hr_utility.set_location(l_proc,20);
764
765 IF p_geo_hier_table(idx).hierarchy_node_id = p_coverage_node_id THEN
766 hr_utility.set_location(l_proc,30);
767 -- This is the first HN for the COV
768 l_parent_found := TRUE;
769 l_result_count := l_result_count + 1;
770 l_result_list(l_result_count) := p_geo_hier_table(idx).hierarchy_node_id;
771 l_coverage_top_level := p_geo_hier_table(idx).level;
772 ELSE -- not first HN
773 hr_utility.set_location(l_proc,40);
774
775 IF p_coverage.COUNT > 0 THEN -- if node is in coverage then save it
776 hr_utility.set_location(l_proc,100);
777 hr_utility.set_location('p_geo_hier_table.hierarchy_node_id:'||p_geo_hier_table(idx).hierarchy_node_id,100);
778 FOR l_count IN p_coverage.FIRST .. p_coverage.LAST LOOP
779 hr_utility.set_location('p_coverage:'||p_coverage(l_count),110);
780 IF(p_coverage(l_count) = p_geo_hier_table(idx).hierarchy_node_id) THEN
781 hr_utility.set_location('p_coverage:'||p_coverage(l_count),120);
782 l_result_count := l_result_count + 1;
783 l_result_list(l_result_count) := p_coverage(l_count);
784 END IF;
785 END LOOP;
786 END IF; -- Node is in coverage
787
788
789
790 IF l_parent_found THEN
791 hr_utility.set_location(l_proc,50);
792
793 -- Check if we have moved back up the tree beyond coverage level
794 IF p_geo_hier_table(idx).level <= l_coverage_top_level THEN
795 hr_utility.set_location(l_proc,60);
796 -- Stop as we have moved up the tree again
797 EXIT; -- break the loop
798 END IF; -- level check
799
800 -- Check if we need to stop exclusions due to moving up the tree
801 -- beyond exclusion level
802 IF l_exclude_flag AND l_exclusion_level >= p_geo_hier_table(idx).level THEN
803 hr_utility.set_location(l_proc,70);
804 l_exclude_flag := FALSE;
805 END IF;
806
807 -- Check for node exclusion
808 IF NOT l_exclude_flag AND p_exclusion_list.COUNT > 0 THEN
809 hr_utility.set_location(l_proc,80);
810 l_exclude_flag := is_node_in_list(p_geo_hier_table(idx).hierarchy_node_id
811 ,p_exclusion_list
812 );
813 IF l_exclude_flag THEN
814 hr_utility.set_location(l_proc,90);
815 -- Note the exclusion level
816 l_exclusion_level := p_geo_hier_table(idx).level;
817 END IF; -- exclusion
818 END IF; -- check node exclusion
819
820 -- Check if node is in coverage and save
821 IF NOT l_exclude_flag THEN
822 hr_utility.set_location(l_proc,100);
823 l_result_count := l_result_count + 1;
824 l_result_list(l_result_count) := p_geo_hier_table(idx).hierarchy_node_id;
825 END IF; -- Node is in coverage
826
827 END IF; -- first HN found
828 END IF; -- first HN
829
830 END LOOP; -- geo hier elements
831 END IF; -- count
832
833 hr_utility.set_location('Leaving '||l_proc,110);
834
835 RETURN l_result_list;
836 END get_child_nodes;
837
838 --
839 -------------------------------------------------------------------------
840 -----------------------< process_geo_coverage >--------------------------
841 -------------------------------------------------------------------------
842 --
843 -- Private procedure to build mapping data
844 --
845 PROCEDURE process_geo_coverage(p_geo_hier_table t_geo_hier_table
846 ,p_cal_event_table t_cal_event_table
847 ) IS
848 -- Local variables for process_geo_coverage
849 l_exc_count NUMBER;
850 l_coverage_node_id NUMBER;
851 l_coverage_list t_node_id_table;
852 l_coverage t_node_id_table;
853 l_exclusion_list t_node_id_table;
854 l_proc VARCHAR2(100);
855 l_cov_count NUMBER;
856
857 BEGIN
858 l_proc := 'HR_CAL_EVENT_MAPPING_PKG.process_geo_coverage';
859 hr_utility.set_location('Entering '||l_proc,10);
860 l_exc_count := 0;
861 l_cov_count := 0;
862 IF p_cal_event_table.COUNT > 0 THEN
863 hr_utility.set_location(l_proc,20);
864 -- Loop for each entry in the calendar entry table passed in
865 FOR idx IN p_cal_event_table.FIRST .. p_cal_event_table.LAST LOOP
866 hr_utility.set_location(l_proc,30);
867 IF p_cal_event_table(idx).usage = 'COV' THEN
868 hr_utility.set_location(l_proc,40);
869 -- This is the start of a coverage
870 l_coverage_node_id := p_cal_event_table(idx).hierarchy_node_id;
871 ELSIF p_cal_event_table(idx).usage = 'EXC' THEN
872 hr_utility.set_location(l_proc,50);
873 -- Note the exclusion node
874 l_exc_count := l_exc_count + 1;
875 l_exclusion_list(l_exc_count) := p_cal_event_table(idx).hierarchy_node_id;
876 END IF;
877 -- If end of coverage, process the coverage
878 IF p_cal_event_table(idx).entry_flag = 'E' THEN
879 hr_utility.set_location(l_proc,60);
880 l_coverage_list := get_child_nodes(l_coverage_node_id
881 ,l_exclusion_list
882 ,p_geo_hier_table
883 ,l_coverage
884 );
885 -- If coverage rows returned, write to cache
886 IF l_coverage_list.COUNT > 0 THEN
887 hr_utility.set_location(l_proc,70);
888 write_geo_cache(l_coverage_list
889 ,p_cal_event_table(idx).calendar_entry_id
890 );
891 END IF; -- coverage rows returned
892
893 -- Reset local variables for next calendar event
894 l_exc_count := 0;
895 l_cov_count := 0;
896 l_coverage_node_id := NULL;
897 l_coverage_list.DELETE;
898 l_coverage.DELETE;
899 l_exclusion_list.DELETE;
900 hr_utility.set_location(l_proc,80);
901
902 ELSE
903 IF l_coverage_node_id = p_cal_event_table(idx).hierarchy_node_id THEN
904 l_cov_count := l_cov_count + 1;
905 l_coverage(l_cov_count) := p_cal_event_table(idx).hierarchy_node_id;
906 END IF;
907
908 END IF; -- end of coverage
909 END LOOP; -- event loop
910 END IF; -- count of events to process
911 hr_utility.set_location('Leaving '||l_proc,90);
912 END process_geo_coverage;
913
914 BEGIN -- populate_geo_list
915 l_proc := 'HR_CAL_EVENT_MAPPING_PKG.populate_geo_list';
916 hr_utility.set_location('Entering '||l_proc,10);
917
918 -- Get the identifiers for all the hierarchies that need to be processed.
919 FOR l_ghv_rec IN c_ghv_ids LOOP
920 hr_utility.set_location('GHVId:'||l_ghv_rec.ghv_id,20);
921
922 -- Get the version of the associated GEO hierarchy
923 OPEN c_ghv_ver_id(l_ghv_rec.ghv_id);
924 FETCH c_ghv_ver_id INTO l_ghv_ver_id;
925 CLOSE c_ghv_ver_id;
926
927 hr_utility.set_location('GHVVerId:'||l_ghv_ver_id,25);
928
929 hr_utility.set_location(l_proc,50);
930
931 -- Get the calendar events for the GEO Hierarchy
932 FOR l_geo_event_rec IN c_geo_events(l_ghv_rec.ghv_id) LOOP
933 hr_utility.set_location(l_proc,60);
934 -- Store the event
935 l_cal_event_table(c_geo_events%ROWCOUNT) := l_geo_event_rec;
936
937 IF c_geo_events%ROWCOUNT > 1 THEN
938 hr_utility.set_location(l_proc,70);
939
940 -- This is not the first record. Check if different from the
941 -- previous event.
942 IF l_geo_event_rec.calendar_entry_id <>
943 l_cal_event_table(c_geo_events%ROWCOUNT-1).calendar_entry_id THEN
944 hr_utility.set_location(l_proc,80);
945
946 -- Mark end of old event coverage and start of new event coverage
947 l_cal_event_table(c_geo_events%ROWCOUNT-1).entry_flag := 'E';
948 l_cal_event_table(c_geo_events%ROWCOUNT).entry_flag := 'S';
949 END IF;
950
951 ELSE
952 hr_utility.set_location(l_proc,90);
953 -- This is the first record. Mark it as coverage start.
954 l_cal_event_table(c_geo_events%ROWCOUNT).entry_flag := 'S';
955 END IF;
956
957 END LOOP; -- c_geo_events
958
959 -- Mark end of coverage for the last row
960 IF l_cal_event_table.COUNT > 0 THEN
961 hr_utility.set_location(l_proc,100);
962 l_cal_event_table(l_cal_event_table.LAST).entry_flag := 'E';
963 END IF;
964
965 END LOOP; -- c_ghv_ids
966
967 hr_utility.set_location(l_proc,110);
968
969 -- Get the top of the associated GEO hierarchy
970 /*OPEN c_geo_hier_top(l_ghv_ver_id);
971 FETCH c_geo_hier_top INTO l_top_node_id;
972 CLOSE c_geo_hier_top;*/
973
974 FOR l_geo_hier_top_rec IN c_geo_hier_top(l_ghv_ver_id) LOOP
975
976 l_top_node_id := l_geo_hier_top_rec.hierarchy_node_id;
977 hr_utility.set_location('TopNodeId:'||l_top_node_id,30);
978
979 -- Get the nodes of the associated GEO hierarchy
980 FOR l_geo_hier_node_rec IN c_geo_hier_nodes(l_ghv_ver_id
981 ,l_top_node_id
982 ) LOOP
983 hr_utility.set_location(l_proc,40);
984 -- Store the GEO hierarchy node
985 l_geo_hier_table(c_geo_hier_nodes%ROWCOUNT) := l_geo_hier_node_rec;
986 END LOOP; -- c_geo_hier_nodes
987
988 -- Process coverage data
989 process_geo_coverage(l_geo_hier_table
990 ,l_cal_event_table
991 );
992
993 END LOOP ; -- hier_top
994
995 hr_utility.set_location('Leaving '||l_proc,120);
996 END populate_geo_list;
997
998 --
999 -----------------------------------------------------------------------------
1000 ---------------------------< build_event_cache >-----------------------------
1001 -----------------------------------------------------------------------------
1002 --
1003 -- Public procedure which populates the calendar event mapping cache tables
1004 -- and generates stats for these.
1005 --
1006 PROCEDURE build_event_cache(errbuf IN OUT NOCOPY VARCHAR2
1007 ,retcode IN OUT NOCOPY NUMBER
1008 ) IS
1009
1010 l_process_date DATE;
1011 l_table_owner VARCHAR2(30);
1012 l_status VARCHAR2(255);
1013 l_industry VARCHAR2(255);
1014 l_dummy BOOLEAN;
1015 l_proc VARCHAR2(100);
1016
1017 BEGIN
1018 l_proc := 'HR_CAL_EVENT_MAPPING_PKG.build_event_cache';
1019 hr_utility.set_location('Entering '||l_proc,10);
1020
1021 -- Delete previous event org list records as this execution will
1022 -- refresh the list
1023 DELETE FROM per_cal_entry_org_list;
1024 DELETE FROM per_cal_entry_geo_list;
1025
1026 hr_utility.set_location(l_proc,20);
1027
1028 -- Generate event list for organization hierarchy coverage.
1029 populate_org_list;
1030
1031 hr_utility.set_location(l_proc,30);
1032
1033 -- Generate event list for geographic hierarchy coverage.
1034 populate_geo_list;
1035
1036 hr_utility.set_location(l_proc,40);
1037
1038 -- Gather stats on the cache tables for current schema (clone)
1039 l_dummy := fnd_installation.get_app_info
1040 (application_short_name => 'PER'
1041 ,status => l_status
1042 ,industry => l_industry
1043 ,oracle_schema => l_table_owner
1044 );
1045
1046 hr_utility.set_location(l_proc,50);
1047
1048 fnd_stats.gather_table_stats(ownname => l_table_owner
1049 ,tabname => 'PER_CAL_ENTRY_ORG_LIST'
1050 ,percent => 50
1051 );
1052
1053 hr_utility.set_location(l_proc,60);
1054
1055 fnd_stats.gather_table_stats(ownname => l_table_owner
1056 ,tabname => 'PER_CAL_ENTRY_GEO_LIST'
1057 ,percent => 50
1058 );
1059
1060 hr_utility.set_location('Leaving '||l_proc,70);
1061 END build_event_cache;
1062
1063 --
1064 -----------------------------------------------------------------------------
1065 ------------------------< get_per_asg_cal_events >---------------------------
1066 -----------------------------------------------------------------------------
1067 --
1068 -- Public function returning a list of calendar events applicable to a person
1069 --
1070 PROCEDURE get_per_asg_cal_events (p_person_id IN NUMBER
1071 ,p_assignment_id IN NUMBER DEFAULT NULL
1072 ,p_event_type IN VARCHAR2 DEFAULT NULL
1073 ,p_start_date IN DATE DEFAULT NULL
1074 ,p_end_date IN DATE DEFAULT NULL
1075 ,p_event_type_flag IN VARCHAR2 DEFAULT NULL
1076 ,x_cal_event_varray IN OUT NOCOPY per_cal_event_varray
1077 ) IS
1078
1079 -- Cursor to fetch person assignment
1080 CURSOR c_per_asg ( cp_person_id NUMBER
1081 , cp_assignment_id NUMBER
1082 , cp_start_date DATE
1083 , cp_end_date DATE
1084 ) IS
1085 SELECT assignment_id,
1086 organization_id, -- ORG Node Id
1087 location_id, -- for GEO mapping
1088 business_group_id -- for GEO mapping
1089 FROM per_all_assignments_f
1090 WHERE person_id = cp_person_id
1091 AND (
1092 (cp_assignment_id IS NOT NULL AND assignment_id = cp_assignment_id)
1093 OR
1094 (cp_assignment_id IS NULL AND primary_flag = 'Y')
1095 )
1096 AND effective_start_date <= NVL(cp_end_date, SYSDATE)
1097 AND effective_end_date >= NVL(cp_start_date, SYSDATE);
1098
1099 -- Cursor to fetch GEO node at assignment EIT level
1100 CURSOR c_asg_geo_node (cp_assignment_id NUMBER) IS
1101 SELECT aei_information1
1102 FROM per_assignment_extra_info
1103 WHERE assignment_id = cp_assignment_id
1104 AND information_type = FND_PROFILE.value('HR_GEO_HIER_NODE_MAP')
1105 AND aei_information_category = FND_PROFILE.value('HR_GEO_HIER_NODE_MAP');
1106
1107 -- Cursor to fetch GEO node at location EIT level
1108 CURSOR c_loc_geo_node (cp_location_id NUMBER) IS
1109 SELECT lei_information1
1110 FROM hr_location_extra_info
1111 WHERE location_id = cp_location_id
1112 AND information_type = FND_PROFILE.value('HR_GEO_HIER_NODE_MAP')
1113 AND lei_information_category = FND_PROFILE.value('HR_GEO_HIER_NODE_MAP');
1114
1115 -- Cursor to fetch GEO node at business group legislation level
1116 CURSOR c_bg_geo_node (cp_business_group_id NUMBER) IS
1117 SELECT org_information9
1118 FROM hr_organization_information
1119 WHERE organization_id = cp_business_group_id
1120 AND org_information_context = 'Business Group Information'
1121 AND attribute_category = 'Business Group Information';
1122
1123 -- Cursor to fetch GEO Node Id
1124 CURSOR c_geo_node_id (cp_geo_node VARCHAR2) IS
1125 SELECT GHN.hierarchy_node_id
1126 FROM per_gen_hierarchy GH,
1127 per_gen_hierarchy_versions GHV,
1128 per_gen_hierarchy_nodes GHN
1129 WHERE GH.type = 'PER_CAL_GEO'
1130 AND GH.hierarchy_id = GHV.hierarchy_id
1131 AND GHV.version_number = 1
1132 AND GHV.hierarchy_version_id = GHN.hierarchy_version_id
1133 AND GHN.entity_id = cp_geo_node;
1134
1135 -- Cursor to fetch ORG events
1136 CURSOR c_org_events ( cp_organization_id NUMBER
1137 , cp_event_type VARCHAR2
1138 , cp_start_date DATE
1139 , cp_end_date DATE
1140 ) IS
1141 SELECT ENT.calendar_entry_id,
1142 ENT.business_group_id,
1143 ENT.name,
1144 ENT.type,
1145 ENT.start_date,
1146 ENT.end_date,
1147 ENT.start_hour,
1148 ENT.end_hour,
1149 ENT.start_min,
1150 ENT.end_min,
1151 ORGENT.ovr_cal_entry_value_id
1152 FROM per_cal_entry_org_list ORGENT,
1153 per_calendar_entries ENT
1154 WHERE ORGENT.organization_id = cp_organization_id
1155 AND ORGENT.calendar_entry_id = ENT.calendar_entry_id
1156 AND ENT.type = NVL(cp_event_type, ENT.type)
1157 AND ENT.start_date <= NVL(cp_end_date, ENT.start_date)
1158 AND ENT.end_date >= NVL(cp_start_date, ENT.end_date);
1159
1160 -- Cursor to fetch GEO events
1161 CURSOR c_geo_events ( cp_geo_node_id NUMBER
1162 , cp_event_type VARCHAR2
1163 , cp_start_date DATE
1164 , cp_end_date DATE
1165 ) IS
1166 SELECT ENT.calendar_entry_id,
1167 ENT.business_group_id,
1168 ENT.name,
1169 ENT.type,
1170 ENT.start_date,
1171 ENT.end_date,
1172 ENT.start_hour,
1173 ENT.end_hour,
1174 ENT.start_min,
1175 ENT.end_min,
1176 GEOENT.ovr_cal_entry_value_id
1177 FROM per_cal_entry_geo_list GEOENT,
1178 per_calendar_entries ENT
1179 WHERE GEOENT.hierarchy_node_id = cp_geo_node_id
1180 AND GEOENT.calendar_entry_id = ENT.calendar_entry_id
1181 AND ENT.type = NVL(cp_event_type, ENT.type)
1182 AND ENT.start_date <= NVL(cp_end_date, ENT.start_date)
1183 AND ENT.end_date >= NVL(cp_start_date, ENT.end_date);
1184
1185 -- Cursor to fetch event override name and type
1186 CURSOR c_event_ovr (cp_ovr_id NUMBER) IS
1187 SELECT override_name
1188 ,override_type
1189 FROM per_cal_entry_values
1190 WHERE cal_entry_value_id = cp_ovr_id;
1191
1192 l_proc VARCHAR2(50);
1193 l_cal_event_obj per_cal_event_obj;
1194 l_event_type_flag VARCHAR2(1);
1195 l_null_times BOOLEAN;
1196 l_not_null_times BOOLEAN;
1197 l_start_date DATE;
1198 l_end_date DATE;
1199
1200 -- Person Assigment attributes
1201 l_assignment_id per_all_assignments_f.assignment_id%TYPE;
1202 l_organization_id per_all_assignments_f.organization_id%TYPE;
1203 l_location_id per_all_assignments_f.location_id%TYPE;
1204 l_business_group_id per_all_assignments_f.business_group_id%TYPE;
1205
1206 -- GEO Node attributes
1207 l_geo_node VARCHAR2(150);
1208 l_geo_node_id NUMBER;
1209
1210 -- Calendar event override attributes
1211 l_ovr_id NUMBER;
1212 l_ovr_name per_cal_entry_values.override_name%TYPE;
1213 l_ovr_type per_cal_entry_values.override_type%TYPE;
1214
1215 -- Local exceptions
1216 e_param_valdn_fail EXCEPTION;
1217 e_bg_leg_not_found EXCEPTION;
1218
1219 BEGIN
1220 l_proc := 'hr_cal_event_mapping_pkg.get_per_asg_cal_events';
1221 hr_utility.set_location('Entering: '|| l_proc, 10);
1222
1223 l_cal_event_obj := per_cal_event_obj(NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
1224 x_cal_event_varray := per_cal_event_varray(); -- initialize empty
1225 l_event_type_flag := NVL(p_event_type_flag, 'B');
1226 l_geo_node := NULL;
1227
1228 -- Range validate supplied start and end dates
1229 IF p_start_date IS NOT NULL AND p_end_date IS NOT NULL THEN
1230 IF p_end_date < p_start_date THEN
1231 hr_utility.set_location(l_proc, 20);
1232 RAISE e_param_valdn_fail;
1233 END IF;
1234 l_start_date := TRUNC(p_start_date);
1235 l_end_date := TRUNC(p_end_date);
1236 END IF;
1237
1238 -- Validate event type flag
1239 IF l_event_type_flag NOT IN ('B', -- Both ORG and GEO events
1240 'O', -- Only ORG events
1241 'G' -- Only GEO events
1242 ) THEN
1243 hr_utility.set_location(l_proc, 30);
1244 RAISE e_param_valdn_fail;
1245 END IF;
1246
1247 -- Get the person assignment identifier
1248 OPEN c_per_asg ( p_person_id
1249 , p_assignment_id
1250 , p_start_date
1251 , p_end_date
1252 );
1253 FETCH c_per_asg INTO l_assignment_id
1254 , l_organization_id
1255 , l_location_id
1256 , l_business_group_id;
1257 CLOSE c_per_asg;
1258
1259 hr_utility.set_location('PerId: '||p_person_id||' AsgId: '||l_assignment_id, 30);
1260
1261 -- Get GEO node from assignment EIT if present
1262 IF l_event_type_flag IN ('B','G') THEN
1263 BEGIN
1264 OPEN c_asg_geo_node (l_assignment_id);
1265 FETCH c_asg_geo_node INTO l_geo_node;
1266 CLOSE c_asg_geo_node;
1267 hr_utility.set_location('GEONode: '||l_geo_node, 40);
1268 EXCEPTION
1269 WHEN OTHERS THEN
1270 -- GEO node not found at assignment EIT level
1271 hr_utility.set_location(l_proc, 45);
1272 END;
1273 END IF; -- event flag is 'B' or 'G'
1274
1275 -- Get GEO node from location EIT if present
1276 IF l_event_type_flag IN ('B','G') AND l_geo_node IS NULL THEN
1277 BEGIN
1278 OPEN c_loc_geo_node (l_location_id);
1279 FETCH c_loc_geo_node INTO l_geo_node;
1280 CLOSE c_loc_geo_node;
1281 hr_utility.set_location('GEONode: '||l_geo_node, 50);
1282 EXCEPTION
1283 WHEN OTHERS THEN
1284 -- GEO node not found at location EIT level
1285 hr_utility.set_location(l_proc, 55);
1286 END;
1287 END IF; -- event flag is ('B' or 'G') and GEO Node not yet found
1288
1289 -- Get GEO node from business group legislation
1290 IF l_event_type_flag IN ('B','G') AND l_geo_node IS NULL THEN
1291 BEGIN
1292 OPEN c_bg_geo_node (l_business_group_id);
1293 FETCH c_bg_geo_node INTO l_geo_node;
1294 CLOSE c_bg_geo_node;
1295 hr_utility.set_location('GEONode: '||l_geo_node, 60);
1296 EXCEPTION
1297 WHEN OTHERS THEN
1298 -- GEO node not found at business group legislation level
1299 hr_utility.set_location(l_proc, 65);
1300 RAISE e_bg_leg_not_found;
1301 END;
1302 END IF; -- event flag is ('B' or 'G') and GEO Node not yet found
1303
1304 -- Get GEO Node Id
1305 IF l_event_type_flag IN ('B','G') AND l_geo_node IS NOT NULL THEN
1306 OPEN c_geo_node_id (l_geo_node);
1307 FETCH c_geo_node_id INTO l_geo_node_id;
1308 CLOSE c_geo_node_id;
1309 hr_utility.set_location('GEONodeId: '||l_geo_node_id, 70);
1310 END IF; -- event flag is ('B' or 'G') and GEO Node found
1311
1312 -- Get ORG Events if required
1313 IF l_event_type_flag IN ('B','O') AND l_organization_id IS NOT NULL THEN
1314 OPEN c_org_events (l_organization_id
1315 ,p_event_type
1316 ,l_start_date
1317 ,p_end_date
1318 );
1319 LOOP -- ORG Events
1320 l_ovr_id := NULL;
1321 l_ovr_name := NULL;
1322 l_ovr_type := NULL;
1323
1324 FETCH c_org_events INTO l_cal_event_obj.cal_event_id
1325 ,l_cal_event_obj.business_group_id
1326 ,l_cal_event_obj.event_name
1327 ,l_cal_event_obj.event_type
1328 ,l_cal_event_obj.start_date
1329 ,l_cal_event_obj.end_date
1330 ,l_cal_event_obj.start_hour
1331 ,l_cal_event_obj.end_hour
1332 ,l_cal_event_obj.start_minute
1333 ,l_cal_event_obj.end_minute
1334 ,l_ovr_id;
1335 EXIT WHEN c_org_events%NOTFOUND;
1336
1337 -- Handle incomplete times
1338 l_null_times := FALSE;
1339 l_not_null_times := FALSE;
1340 IF l_cal_event_obj.start_hour IS NULL THEN
1341 l_null_times := TRUE;
1342 ELSE
1343 l_not_null_times := TRUE;
1344 END IF;
1345 IF l_cal_event_obj.end_hour IS NULL THEN
1346 l_null_times := TRUE;
1347 ELSE
1348 l_not_null_times := TRUE;
1349 END IF;
1350 IF l_cal_event_obj.start_minute IS NULL THEN
1351 l_null_times := TRUE;
1352 ELSE
1353 l_not_null_times := TRUE;
1354 END IF;
1355 IF l_cal_event_obj.end_minute IS NULL THEN
1356 l_null_times := TRUE;
1357 ELSE
1358 l_not_null_times := TRUE;
1359 END IF;
1360 IF l_null_times AND l_not_null_times THEN
1361 -- Mixed nulls have been entered i.e. incomplete times
1362 IF l_cal_event_obj.start_hour IS NULL THEN
1363 l_cal_event_obj.start_hour := '0';
1364 END IF;
1365 IF l_cal_event_obj.end_hour IS NULL THEN
1366 l_cal_event_obj.end_hour := '0';
1367 END IF;
1368 IF l_cal_event_obj.start_minute IS NULL THEN
1369 l_cal_event_obj.start_minute := '0';
1370 END IF;
1371 IF l_cal_event_obj.end_minute IS NULL THEN
1372 l_cal_event_obj.end_minute := '0';
1373 END IF;
1374 END IF;
1375
1376 -- Adjust date for same day events for CAC integration
1377 IF (
1378 (l_cal_event_obj.start_hour IS NULL AND
1379 l_cal_event_obj.end_hour IS NULL AND
1380 l_cal_event_obj.start_minute IS NULL AND
1381 l_cal_event_obj.end_minute IS NULL
1382 )
1383 OR
1384 (l_cal_event_obj.start_hour IS NOT NULL AND
1385 l_cal_event_obj.end_hour IS NOT NULL AND
1386 l_cal_event_obj.start_minute IS NOT NULL AND
1387 l_cal_event_obj.end_minute IS NOT NULL AND
1388 l_cal_event_obj.start_hour = l_cal_event_obj.end_hour AND
1389 l_cal_event_obj.start_minute = l_cal_event_obj.end_minute AND
1390 l_cal_event_obj.start_hour = '0' AND
1391 l_cal_event_obj.start_minute = '0' AND
1392 l_cal_event_obj.start_date = l_cal_event_obj.end_date
1393 )
1394 ) THEN
1395 l_cal_event_obj.end_date := l_cal_event_obj.end_date + 1;
1396 IF (l_cal_event_obj.start_hour IS NULL AND
1397 l_cal_event_obj.end_hour IS NULL AND
1398 l_cal_event_obj.start_minute IS NULL AND
1399 l_cal_event_obj.end_minute IS NULL) THEN
1400 l_cal_event_obj.start_hour:= '0';
1401 l_cal_event_obj.end_hour := '0';
1402 l_cal_event_obj.start_minute := '0';
1403 l_cal_event_obj.end_minute := '0';
1404 END IF;
1405 END IF;
1406
1407 -- Fetch override if it exists
1408 IF l_ovr_id IS NOT NULL THEN
1409 OPEN c_event_ovr (l_ovr_id);
1410 FETCH c_event_ovr INTO l_ovr_name,
1411 l_ovr_type;
1412 CLOSE c_event_ovr;
1413 l_cal_event_obj.event_name := NVL(l_ovr_name, l_cal_event_obj.event_name);
1414 l_cal_event_obj.event_type := NVL(l_ovr_type, l_cal_event_obj.event_type);
1415 END IF;
1416
1417 x_cal_event_varray.EXTEND(1);
1418 x_cal_event_varray(x_cal_event_varray.COUNT) := l_cal_event_obj;
1419 END LOOP; -- ORG Events
1420 CLOSE c_org_events;
1421 hr_utility.set_location(l_proc, 80);
1422 END IF; -- event flag is ('B' or ')') and ORG Node Id found
1423
1424 -- Get GEO Events if required
1425 IF l_event_type_flag IN ('B','G') AND l_geo_node_id IS NOT NULL THEN
1426 OPEN c_geo_events (l_geo_node_id
1427 ,p_event_type
1428 ,l_start_date
1429 ,p_end_date
1430 );
1431 LOOP -- GEO Events
1432 l_ovr_id := NULL;
1433 l_ovr_name := NULL;
1434 l_ovr_type := NULL;
1435
1436 FETCH c_geo_events INTO l_cal_event_obj.cal_event_id
1437 ,l_cal_event_obj.business_group_id
1438 ,l_cal_event_obj.event_name
1439 ,l_cal_event_obj.event_type
1440 ,l_cal_event_obj.start_date
1441 ,l_cal_event_obj.end_date
1442 ,l_cal_event_obj.start_hour
1443 ,l_cal_event_obj.end_hour
1444 ,l_cal_event_obj.start_minute
1445 ,l_cal_event_obj.end_minute
1446 ,l_ovr_id;
1447 EXIT WHEN c_geo_events%NOTFOUND;
1448
1449 -- Handle incomplete times
1450 l_null_times := FALSE;
1451 l_not_null_times := FALSE;
1452 IF l_cal_event_obj.start_hour IS NULL THEN
1453 l_null_times := TRUE;
1454 ELSE
1455 l_not_null_times := TRUE;
1456 END IF;
1457 IF l_cal_event_obj.end_hour IS NULL THEN
1458 l_null_times := TRUE;
1459 ELSE
1460 l_not_null_times := TRUE;
1461 END IF;
1462 IF l_cal_event_obj.start_minute IS NULL THEN
1463 l_null_times := TRUE;
1464 ELSE
1465 l_not_null_times := TRUE;
1466 END IF;
1467 IF l_cal_event_obj.end_minute IS NULL THEN
1468 l_null_times := TRUE;
1469 ELSE
1470 l_not_null_times := TRUE;
1471 END IF;
1472 IF l_null_times AND l_not_null_times THEN
1473 -- Mixed nulls have been entered i.e. incomplete times
1474 IF l_cal_event_obj.start_hour IS NULL THEN
1475 l_cal_event_obj.start_hour := '0';
1476 END IF;
1477 IF l_cal_event_obj.end_hour IS NULL THEN
1478 l_cal_event_obj.end_hour := '0';
1479 END IF;
1480 IF l_cal_event_obj.start_minute IS NULL THEN
1481 l_cal_event_obj.start_minute := '0';
1482 END IF;
1483 IF l_cal_event_obj.end_minute IS NULL THEN
1484 l_cal_event_obj.end_minute := '0';
1485 END IF;
1486 END IF;
1487
1488 -- Adjust date for same day events for CAC integration
1489 IF (
1490 (l_cal_event_obj.start_hour IS NULL AND
1491 l_cal_event_obj.end_hour IS NULL AND
1492 l_cal_event_obj.start_minute IS NULL AND
1493 l_cal_event_obj.end_minute IS NULL
1494 )
1495 OR
1496 (l_cal_event_obj.start_hour IS NOT NULL AND
1497 l_cal_event_obj.end_hour IS NOT NULL AND
1498 l_cal_event_obj.start_minute IS NOT NULL AND
1499 l_cal_event_obj.end_minute IS NOT NULL AND
1500 l_cal_event_obj.start_hour = l_cal_event_obj.end_hour AND
1501 l_cal_event_obj.start_minute = l_cal_event_obj.end_minute AND
1502 l_cal_event_obj.start_hour = '0' AND
1503 l_cal_event_obj.start_minute = '0' AND
1504 l_cal_event_obj.start_date = l_cal_event_obj.end_date
1505 )
1506 ) THEN
1507 l_cal_event_obj.end_date := l_cal_event_obj.end_date + 1;
1508 IF (l_cal_event_obj.start_hour IS NULL AND
1509 l_cal_event_obj.end_hour IS NULL AND
1510 l_cal_event_obj.start_minute IS NULL AND
1511 l_cal_event_obj.end_minute IS NULL) THEN
1512 l_cal_event_obj.start_hour:= '0';
1513 l_cal_event_obj.end_hour := '0';
1514 l_cal_event_obj.start_minute := '0';
1515 l_cal_event_obj.end_minute := '0';
1516 END IF;
1517 END IF;
1518
1519 -- Fetch override if it exists
1520 IF l_ovr_id IS NOT NULL THEN
1521 OPEN c_event_ovr (l_ovr_id);
1522 FETCH c_event_ovr INTO l_ovr_name,
1523 l_ovr_type;
1524 CLOSE c_event_ovr;
1525 l_cal_event_obj.event_name := NVL(l_ovr_name, l_cal_event_obj.event_name);
1526 l_cal_event_obj.event_type := NVL(l_ovr_type, l_cal_event_obj.event_type);
1527 END IF;
1528
1529 x_cal_event_varray.EXTEND(1);
1530 x_cal_event_varray(x_cal_event_varray.COUNT) := l_cal_event_obj;
1531 END LOOP; -- GEO Events
1532 CLOSE c_geo_events;
1533 hr_utility.set_location(l_proc, 90);
1534 END IF; -- event flag is ('B' or 'G') and GEO Node Id found
1535
1536 hr_utility.set_location('Leaving: '|| l_proc, 100);
1537
1538 EXCEPTION
1539
1540 WHEN e_param_valdn_fail THEN
1541 hr_utility.set_location('Leaving: '|| l_proc, 110);
1542
1543 WHEN e_bg_leg_not_found THEN
1544 hr_utility.set_location('Leaving: '|| l_proc, 120);
1545
1546 WHEN OTHERS THEN
1547 hr_utility.set_location('Leaving: '|| l_proc, 130);
1548 hr_utility.set_location(SQLERRM, 135);
1549
1550 END get_per_asg_cal_events;
1551
1552 --
1553 -----------------------------------------------------------------------------
1554 -------------------< get_cal_events (Person Version) >-----------------------
1555 -----------------------------------------------------------------------------
1556 --
1557 -- Public function returning a list of calendar events applicable to a person
1558 --
1559 FUNCTION get_cal_events (p_person_id IN NUMBER
1560 ,p_event_type IN VARCHAR2 DEFAULT NULL
1561 ,p_start_date IN DATE DEFAULT NULL
1562 ,p_end_date IN DATE DEFAULT NULL
1563 ,p_event_type_flag IN VARCHAR2 DEFAULT NULL
1564 ) RETURN per_cal_event_varray IS
1565
1566 l_proc VARCHAR2(60);
1567 l_cal_event_varray per_cal_event_varray;
1568
1569 BEGIN
1570
1571 l_proc := 'hr_cal_event_mapping_pkg.get_cal_events (PerVer)';
1572 hr_utility.set_location('Entering: '|| l_proc, 10);
1573
1574 -- Invoke private procedure to get calendar events
1575 get_per_asg_cal_events (p_person_id => p_person_id
1576 ,p_assignment_id => ''
1577 ,p_event_type => p_event_type
1578 ,p_start_date => p_start_date
1579 ,p_end_date => p_end_date
1580 ,p_event_type_flag => p_event_type_flag
1581 ,x_cal_event_varray => l_cal_event_varray
1582 );
1583
1584 hr_utility.set_location('Leaving: '|| l_proc, 20);
1585 RETURN l_cal_event_varray;
1586
1587 EXCEPTION
1588
1589 WHEN OTHERS THEN
1590 hr_utility.set_location('Leaving: '|| l_proc, 30);
1591 hr_utility.set_location(SQLERRM, 35);
1592 RETURN l_cal_event_varray;
1593
1594 END get_cal_events; -- Person Version
1595
1596 --
1597 -----------------------------------------------------------------------------
1598 ----------------< get_cal_events (Assignment Version) >----------------------
1599 -----------------------------------------------------------------------------
1600 --
1601 -- Public function returning a list of calendar events applicable to an
1602 -- assignment.
1603 --
1604 FUNCTION get_cal_events (p_assignment_id IN NUMBER
1605 ,p_event_type IN VARCHAR2 DEFAULT NULL
1606 ,p_start_date IN DATE DEFAULT NULL
1607 ,p_end_date IN DATE DEFAULT NULL
1608 ,p_event_type_flag IN VARCHAR2 DEFAULT NULL
1609 ) RETURN per_cal_event_varray IS
1610
1611 l_proc VARCHAR2(60);
1612 l_cal_event_varray per_cal_event_varray;
1613 l_person_id NUMBER;
1614
1615 -- Cursor to get the person id for an assignment id.
1616 CURSOR c_per_id (cp_assignment_id NUMBER) IS
1617 SELECT person_id
1618 FROM per_all_assignments_f
1619 WHERE assignment_id = cp_assignment_id;
1620
1621 BEGIN
1622
1623 l_proc := 'hr_cal_event_mapping_pkg.get_cal_events (AsgVer)';
1624 hr_utility.set_location('Entering: '|| l_proc, 10);
1625
1626 -- Get the person id for an assignment id. Though multiple records could
1627 -- exist, the person id will be the same. So sufficient to fetch first.
1628 OPEN c_per_id (p_assignment_id);
1629 FETCH c_per_id INTO l_person_id;
1630 CLOSE c_per_id;
1631
1632 hr_utility.set_location(l_proc, 20);
1633
1634 -- Invoke private procedure to get calendar events
1635 get_per_asg_cal_events (p_person_id => l_person_id
1636 ,p_assignment_id => p_assignment_id
1637 ,p_event_type => p_event_type
1638 ,p_start_date => p_start_date
1639 ,p_end_date => p_end_date
1640 ,p_event_type_flag => p_event_type_flag
1641 ,x_cal_event_varray => l_cal_event_varray
1642 );
1643
1644 hr_utility.set_location('Leaving: '|| l_proc, 30);
1645 RETURN l_cal_event_varray;
1646
1647 EXCEPTION
1648
1649 WHEN OTHERS THEN
1650 hr_utility.set_location('Leaving: '|| l_proc, 40);
1651 hr_utility.set_location(SQLERRM, 45);
1652 RETURN l_cal_event_varray;
1653
1654 END get_cal_events; -- Assignment Version
1655
1656 --
1657 -----------------------------------------------------------------------------
1658 ------------------< get_cal_events (HZ Party Version) >----------------------
1659 -----------------------------------------------------------------------------
1660 --
1661 -- Public function returning a list of calendar events applicable to an HZ
1662 -- party.
1663 --
1664 FUNCTION get_cal_events (p_hz_party_id IN NUMBER
1665 ,p_event_type IN VARCHAR2 DEFAULT NULL
1666 ,p_start_date IN DATE DEFAULT NULL
1667 ,p_end_date IN DATE DEFAULT NULL
1668 ,p_event_type_flag IN VARCHAR2 DEFAULT NULL
1669 ) RETURN per_cal_event_varray IS
1670
1671 l_proc VARCHAR2(60);
1672 l_cal_event_varray per_cal_event_varray;
1673 l_person_id NUMBER;
1674
1675 -- Cursor to get the party id for a person id.
1676 CURSOR c_per_id (cp_party_id NUMBER) IS
1677 SELECT person_identifier
1678 FROM hz_parties
1679 WHERE party_id = cp_party_id
1680 AND created_by_module = 'HR API'
1681 AND orig_system_reference = 'PER:'||person_identifier;
1682
1683 BEGIN
1684
1685 l_proc := 'hr_cal_event_mapping_pkg.get_cal_events (HZVer)';
1686 hr_utility.set_location('Entering: '|| l_proc, 10);
1687
1688 -- Get the person id for a party id. Though multiple records could
1689 -- exist, the person id will be the same. So sufficient to fetch first.
1690 OPEN c_per_id (p_hz_party_id);
1691 FETCH c_per_id INTO l_person_id;
1692 CLOSE c_per_id;
1693
1694 hr_utility.set_location(l_proc, 20);
1695
1696 -- Invoke private procedure to get calendar events
1697 get_per_asg_cal_events (p_person_id => l_person_id
1698 ,p_assignment_id => ''
1699 ,p_event_type => p_event_type
1700 ,p_start_date => p_start_date
1701 ,p_end_date => p_end_date
1702 ,p_event_type_flag => p_event_type_flag
1703 ,x_cal_event_varray => l_cal_event_varray
1704 );
1705
1706 hr_utility.set_location('Leaving: '|| l_proc, 30);
1707 RETURN l_cal_event_varray;
1708
1709 EXCEPTION
1710
1711 WHEN OTHERS THEN
1712 hr_utility.set_location('Leaving: '|| l_proc, 40);
1713 hr_utility.set_location(SQLERRM, 45);
1714 RETURN l_cal_event_varray;
1715
1716 END get_cal_events; -- HZ Party Version
1717
1718 --
1719 -----------------------------------------------------------------------------
1720 --------------------------< get_all_cal_events >-----------------------------
1721 -----------------------------------------------------------------------------
1722 --
1723 -- This function returns all the calendar events in the system or filtered
1724 -- as per given criteria.
1725 --
1726 FUNCTION get_all_cal_events (p_event_type IN VARCHAR2 DEFAULT NULL
1727 ,p_start_date IN DATE DEFAULT NULL
1728 ,p_end_date IN DATE DEFAULT NULL
1729 ) RETURN per_cal_event_varray IS
1730
1731 l_proc VARCHAR2(60);
1732 l_cal_event_varray per_cal_event_varray;
1733 l_cal_event_obj per_cal_event_obj;
1734 l_null_times BOOLEAN;
1735 l_not_null_times BOOLEAN;
1736 l_bg_id NUMBER;
1737 l_start_date DATE;
1738 l_end_date DATE;
1739
1740 -- Cursor to fetch calendar events
1741 CURSOR c_cal_events ( cp_event_type VARCHAR2
1742 , cp_start_date DATE
1743 , cp_end_date DATE
1744 , cp_bg_id NUMBER
1745 ) IS
1746 SELECT calendar_entry_id,
1747 business_group_id,
1748 name,
1749 type,
1750 start_date,
1751 end_date,
1752 start_hour,
1753 end_hour,
1754 start_min,
1755 end_min
1756 FROM per_calendar_entries
1757 WHERE type = NVL(cp_event_type, type)
1758 AND start_date <= NVL(cp_end_date, start_date)
1759 AND end_date >= NVL(cp_start_date, end_date)
1760 AND (business_group_id IS NULL
1761 OR
1762 (business_group_id IS NOT NULL AND
1763 business_group_id = NVL(cp_bg_id, business_group_id)
1764 )
1765 );
1766
1767 BEGIN
1768
1769 l_proc := 'hr_cal_event_mapping_pkg.get_all_cal_events';
1770 hr_utility.set_location('Entering: '|| l_proc, 10);
1771 l_cal_event_obj := per_cal_event_obj(NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
1772 l_cal_event_varray := per_cal_event_varray(); -- initialize empty
1773 l_bg_id := FND_PROFILE.Value('PER_BUSINESS_GROUP_ID');
1774 l_start_date := TRUNC(p_start_date);
1775 l_end_date := TRUNC(p_end_date);
1776
1777 OPEN c_cal_events (p_event_type
1778 ,l_start_date
1779 ,p_end_date
1780 ,l_bg_id
1781 );
1782 LOOP -- Cal Events
1783 FETCH c_cal_events INTO l_cal_event_obj.cal_event_id
1784 ,l_cal_event_obj.business_group_id
1785 ,l_cal_event_obj.event_name
1786 ,l_cal_event_obj.event_type
1787 ,l_cal_event_obj.start_date
1788 ,l_cal_event_obj.end_date
1789 ,l_cal_event_obj.start_hour
1790 ,l_cal_event_obj.end_hour
1791 ,l_cal_event_obj.start_minute
1792 ,l_cal_event_obj.end_minute;
1793 EXIT WHEN c_cal_events%NOTFOUND;
1794
1795 -- Handle incomplete times
1796 l_null_times := FALSE;
1797 l_not_null_times := FALSE;
1798 IF l_cal_event_obj.start_hour IS NULL THEN
1799 l_null_times := TRUE;
1800 ELSE
1801 l_not_null_times := TRUE;
1802 END IF;
1803 IF l_cal_event_obj.end_hour IS NULL THEN
1804 l_null_times := TRUE;
1805 ELSE
1806 l_not_null_times := TRUE;
1807 END IF;
1808 IF l_cal_event_obj.start_minute IS NULL THEN
1809 l_null_times := TRUE;
1810 ELSE
1811 l_not_null_times := TRUE;
1812 END IF;
1813 IF l_cal_event_obj.end_minute IS NULL THEN
1814 l_null_times := TRUE;
1815 ELSE
1816 l_not_null_times := TRUE;
1817 END IF;
1818 IF l_null_times AND l_not_null_times THEN
1819 -- Mixed nulls have been entered i.e. incomplete times
1820 IF l_cal_event_obj.start_hour IS NULL THEN
1821 l_cal_event_obj.start_hour := '0';
1822 END IF;
1823 IF l_cal_event_obj.end_hour IS NULL THEN
1824 l_cal_event_obj.end_hour := '0';
1825 END IF;
1826 IF l_cal_event_obj.start_minute IS NULL THEN
1827 l_cal_event_obj.start_minute := '0';
1828 END IF;
1829 IF l_cal_event_obj.end_minute IS NULL THEN
1830 l_cal_event_obj.end_minute := '0';
1831 END IF;
1832 END IF;
1833
1834 -- Adjust date for same day events for CAC integration
1835 IF (
1836 (l_cal_event_obj.start_hour IS NULL AND
1837 l_cal_event_obj.end_hour IS NULL AND
1838 l_cal_event_obj.start_minute IS NULL AND
1839 l_cal_event_obj.end_minute IS NULL
1840 )
1841 OR
1842 (l_cal_event_obj.start_hour IS NOT NULL AND
1843 l_cal_event_obj.end_hour IS NOT NULL AND
1844 l_cal_event_obj.start_minute IS NOT NULL AND
1845 l_cal_event_obj.end_minute IS NOT NULL AND
1846 l_cal_event_obj.start_hour = l_cal_event_obj.end_hour AND
1847 l_cal_event_obj.start_minute = l_cal_event_obj.end_minute AND
1848 l_cal_event_obj.start_hour = '0' AND
1849 l_cal_event_obj.start_minute = '0' AND
1850 l_cal_event_obj.start_date = l_cal_event_obj.end_date
1851 )
1852 ) THEN
1853 l_cal_event_obj.end_date := l_cal_event_obj.end_date + 1;
1854 IF (l_cal_event_obj.start_hour IS NULL AND
1855 l_cal_event_obj.end_hour IS NULL AND
1856 l_cal_event_obj.start_minute IS NULL AND
1857 l_cal_event_obj.end_minute IS NULL) THEN
1858 l_cal_event_obj.start_hour:= '0';
1859 l_cal_event_obj.end_hour := '0';
1860 l_cal_event_obj.start_minute := '0';
1861 l_cal_event_obj.end_minute := '0';
1862 END IF;
1863 END IF;
1864
1865 l_cal_event_varray.EXTEND(1);
1866 l_cal_event_varray(l_cal_event_varray.COUNT) := l_cal_event_obj;
1867 END LOOP; -- ORG Events
1868 CLOSE c_cal_events;
1869
1870 hr_utility.set_location('Leaving: '|| l_proc, 20);
1871 RETURN l_cal_event_varray;
1872
1873 EXCEPTION
1874
1875 WHEN OTHERS THEN
1876 hr_utility.set_location('Leaving: '|| l_proc, 30);
1877 hr_utility.set_location(SQLERRM, 35);
1878 RETURN l_cal_event_varray;
1879
1880 END get_all_cal_events;
1881
1882 --
1883 -----------------------------------------------------------------------------
1884 -------------------------< build_cal_map_cache >-----------------------------
1885 -----------------------------------------------------------------------------
1886 --
1887 -- This procedure builds transient data into table PER_CAL_MAP_CACHE for
1888 -- use by the calendar mapping user interface.
1889 --
1890 PROCEDURE build_cal_map_cache (p_person_id IN NUMBER
1891 ,p_assignment_id IN NUMBER
1892 ,p_event_type IN VARCHAR2 DEFAULT NULL
1893 ,p_start_date IN DATE DEFAULT NULL
1894 ,p_end_date IN DATE DEFAULT NULL
1895 ) IS
1896
1897 l_proc VARCHAR2(60);
1898 l_cal_event_varray per_cal_event_varray;
1899 l_cal_event_obj per_cal_event_obj;
1900
1901 BEGIN
1902
1903 l_proc := 'hr_cal_event_mapping_pkg.build_cal_map_cache';
1904 hr_utility.set_location('Entering: '|| l_proc, 10);
1905 l_cal_event_obj := per_cal_event_obj(NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
1906 l_cal_event_varray := per_cal_event_varray(); -- initialize empty
1907
1908 l_cal_event_varray := get_cal_events ( p_assignment_id => p_assignment_id
1909 , p_event_type => p_event_type
1910 , p_start_date => p_start_date
1911 , p_end_date => p_end_date
1912 );
1913
1914 hr_utility.set_location(l_proc, 20);
1915
1916 DELETE FROM per_cal_map_cache;
1917
1918 hr_utility.set_location(l_proc, 30);
1919
1920 IF l_cal_event_varray.COUNT > 0 THEN
1921 hr_utility.set_location(l_proc, 35);
1922
1923 FOR idx IN l_cal_event_varray.FIRST..l_cal_event_varray.LAST LOOP
1924 l_cal_event_obj := l_cal_event_varray(idx);
1925 INSERT INTO per_cal_map_cache
1926 (person_id
1927 ,assignment_id
1928 ,event_name
1929 ,event_type
1930 ,start_date
1931 ,end_date
1932 ,start_hour
1933 ,end_hour
1934 ,start_minute
1935 ,end_minute
1936 )
1937 VALUES
1938 (p_person_id
1939 ,p_assignment_id
1940 ,l_cal_event_obj.event_name
1941 ,l_cal_event_obj.event_type
1942 ,l_cal_event_obj.start_date
1943 ,l_cal_event_obj.end_date
1944 ,l_cal_event_obj.start_hour
1945 ,l_cal_event_obj.end_hour
1946 ,l_cal_event_obj.start_minute
1947 ,l_cal_event_obj.end_minute
1948 );
1949 END LOOP;
1950 END IF;
1951
1952 hr_utility.set_location(l_proc, 40);
1953
1954 COMMIT;
1955
1956 hr_utility.set_location('Leaving: '|| l_proc, 50);
1957
1958 EXCEPTION
1959
1960 WHEN OTHERS THEN
1961 hr_utility.set_location('Leaving: '|| l_proc, 60);
1962 hr_utility.set_location(SQLERRM, 65);
1963
1964 END build_cal_map_cache;
1965
1966 END hr_cal_event_mapping_pkg;
[Home] [Help]