1 PACKAGE BODY hr_cal_event_mapping_pkg AS
2 -- $Header: pecalmap.pkb 120.5 2005/09/12 11:06:18 lsilveir noship $
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 --
386 -- Local variables for process_org_coverage
383 PROCEDURE process_org_coverage(p_org_hier_table t_org_hier_table
384 ,p_cal_event_table t_cal_event_table
385 ) IS
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.
512 END LOOP; -- c_org_events
509 l_cal_event_table(c_org_events%ROWCOUNT).entry_flag := 'S';
510 END IF;
511
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
608 TYPE t_geo_hier_table IS TABLE OF c_geo_hier_nodes%ROWTYPE INDEX BY BINARY_INTEGER;
609 TYPE t_cal_event_table IS TABLE OF c_geo_events%ROWTYPE INDEX BY BINARY_INTEGER;
610 TYPE t_node_id_table IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
611
612 -- Local variables for process_geo_list
613 l_geo_hier_table t_geo_hier_table;
614 l_cal_event_table t_cal_event_table;
615 l_top_node_id per_gen_hierarchy_nodes.hierarchy_node_id%TYPE;
616 l_ghv_ver_id per_gen_hierarchy_versions.hierarchy_version_id%TYPE;
617 l_proc VARCHAR2(100);
618
619 --
620 -------------------------------------------------------------------------
621 -------------------------< write_geo_cache >-----------------------------
622 -------------------------------------------------------------------------
623 --
624 -- Private procedure to write calendar event GEO mapping records to the
625 -- GEO cache table
626 --
627 PROCEDURE write_geo_cache(p_coverage_list t_node_id_table
628 ,p_calendar_entry_id NUMBER
629 ) IS
630
631 -- Cursor to fetch override identifier (if any)
632 CURSOR c_geo_ovr ( cp_calendar_entry_id NUMBER
633 , cp_hierarchy_node_id NUMBER
634 ) IS
635 SELECT cal_entry_value_id
636 FROM per_cal_entry_values
637 WHERE usage_flag = 'O'
638 AND calendar_entry_id = cp_calendar_entry_id
639 AND hierarchy_node_id = cp_hierarchy_node_id;
640
641 l_ovr_id per_cal_entry_values.cal_entry_value_id%TYPE;
642 l_proc VARCHAR2(100);
643
644 BEGIN
648 IF p_coverage_list.COUNT > 0 THEN
645 l_proc := 'HR_CAL_EVENT_MAPPING_PKG.write_geo_cache';
646 hr_utility.set_location('Entering '||l_proc,10);
647
649 hr_utility.set_location(l_proc,12);
650 FOR idx IN p_coverage_list.FIRST .. p_coverage_list.LAST LOOP
651 hr_utility.set_location(l_proc,14);
652 l_ovr_id := NULL;
653
654 -- Get override (if any)
655 OPEN c_geo_ovr (p_calendar_entry_id
656 ,p_coverage_list(idx)
657 );
658 FETCH c_geo_ovr INTO l_ovr_id;
659 CLOSE c_geo_ovr;
660
661 hr_utility.set_location(l_proc,18);
662
663 INSERT INTO per_cal_entry_geo_list
664 (calendar_entry_id
665 ,hierarchy_node_id
666 ,ovr_cal_entry_value_id
667 ,last_update_date
668 ,last_updated_by
669 ,last_update_login
670 ,created_by
671 ,creation_date
672 )
673 VALUES
674 (p_calendar_entry_id
675 ,p_coverage_list(idx)
676 ,l_ovr_id
677 ,TRUNC(SYSDATE)
678 ,0
679 ,0
680 ,0
681 ,TRUNC(SYSDATE)
682 );
683 END LOOP;
684 END IF;
685
686 COMMIT;
687
688 hr_utility.set_location('Leaving '||l_proc,20);
689 END write_geo_cache;
690
691 --
692 -------------------------------------------------------------------------
693 -------------------------< is_node_in_list >-----------------------------
694 -------------------------------------------------------------------------
695 --
696 -- Private procedure to check is a given node is in the given list
697 --
698 FUNCTION is_node_in_list(p_node_id NUMBER
699 ,p_node_list t_node_id_table
700 ) RETURN BOOLEAN IS
701 l_return BOOLEAN;
702 l_proc VARCHAR2(100);
703 BEGIN
704 l_proc := 'HR_CAL_EVENT_MAPPING_PKG.is_node_in_list';
705 hr_utility.set_location('Entering '||l_proc,10);
706 l_return := FALSE;
707
708 IF p_node_list.COUNT > 0 THEN
709 hr_utility.set_location(l_proc,13);
710 FOR idx IN p_node_list.FIRST .. p_node_list.LAST LOOP
711 hr_utility.set_location(l_proc,15);
712 IF p_node_list(idx) = p_node_id THEN
713 l_return := TRUE;
714 EXIT;
715 END IF;
716 END LOOP;
717 END IF;
718
719 hr_utility.set_location('Leaving '||l_proc,20);
720
721 RETURN l_return;
722 END is_node_in_list;
723
724 --
725 -------------------------------------------------------------------------
726 -------------------------< get_child_nodes >-----------------------------
727 -------------------------------------------------------------------------
728 --
729 -- Private procedure to get the child coverage records for a given event
730 --
731 FUNCTION get_child_nodes(p_coverage_node_id NUMBER
732 ,p_exclusion_list t_node_id_table
733 ,p_geo_hier_table t_geo_hier_table
734 ) RETURN t_node_id_table IS
735 l_result_list t_node_id_table;
736 l_result_count NUMBER;
737 l_coverage_top_level NUMBER;
738 l_exclusion_level NUMBER;
739 l_parent_found BOOLEAN;
740 l_exclude_flag BOOLEAN;
741 l_proc VARCHAR2(100);
742
743 BEGIN
744 l_proc := 'HR_CAL_EVENT_MAPPING_PKG.get_child_nodes';
745 hr_utility.set_location('Entering '||l_proc,10);
746
747 l_result_count := 0;
748 l_parent_found := FALSE;
749 l_exclude_flag := FALSE;
750 l_coverage_top_level := 1;
751 l_exclusion_level := 0;
752
753 IF p_geo_hier_table.COUNT > 0 THEN
754 hr_utility.set_location(l_proc,15);
755
756 -- Loop through the geo hier tree nodes
757 FOR idx IN p_geo_hier_table.FIRST .. p_geo_hier_table.LAST LOOP
758 hr_utility.set_location(l_proc,20);
759
760 IF p_geo_hier_table(idx).hierarchy_node_id = p_coverage_node_id THEN
761 hr_utility.set_location(l_proc,30);
762 -- This is the first HN for the COV
763 l_parent_found := TRUE;
764 l_result_count := l_result_count + 1;
765 l_result_list(l_result_count) := p_geo_hier_table(idx).hierarchy_node_id;
766 l_coverage_top_level := p_geo_hier_table(idx).level;
767 ELSE -- not first HN
768 hr_utility.set_location(l_proc,40);
769
770 IF l_parent_found THEN
771 hr_utility.set_location(l_proc,50);
772
773 -- Check if we have moved back up the tree beyond coverage level
774 IF p_geo_hier_table(idx).level <= l_coverage_top_level THEN
775 hr_utility.set_location(l_proc,60);
776 -- Stop as we have moved up the tree again
777 EXIT; -- break the loop
778 END IF; -- level check
779
780 -- Check if we need to stop exclusions due to moving up the tree
781 -- beyond exclusion level
782 IF l_exclude_flag AND l_exclusion_level >= p_geo_hier_table(idx).level THEN
783 hr_utility.set_location(l_proc,70);
784 l_exclude_flag := FALSE;
785 END IF;
786
787 -- Check for node exclusion
791 ,p_exclusion_list
788 IF NOT l_exclude_flag AND p_exclusion_list.COUNT > 0 THEN
789 hr_utility.set_location(l_proc,80);
790 l_exclude_flag := is_node_in_list(p_geo_hier_table(idx).hierarchy_node_id
792 );
793 IF l_exclude_flag THEN
794 hr_utility.set_location(l_proc,90);
795 -- Note the exclusion level
796 l_exclusion_level := p_geo_hier_table(idx).level;
797 END IF; -- exclusion
798 END IF; -- check node exclusion
799
800 -- Check if node is in coverage and save
801 IF NOT l_exclude_flag THEN
802 hr_utility.set_location(l_proc,100);
803 l_result_count := l_result_count + 1;
804 l_result_list(l_result_count) := p_geo_hier_table(idx).hierarchy_node_id;
805 END IF; -- Node is in coverage
806
807 END IF; -- first HN found
808 END IF; -- first HN
809
810 END LOOP; -- geo hier elements
811 END IF; -- count
812
813 hr_utility.set_location('Leaving '||l_proc,110);
814
815 RETURN l_result_list;
816 END get_child_nodes;
817
818 --
819 -------------------------------------------------------------------------
820 -----------------------< process_geo_coverage >--------------------------
821 -------------------------------------------------------------------------
822 --
823 -- Private procedure to build mapping data
824 --
825 PROCEDURE process_geo_coverage(p_geo_hier_table t_geo_hier_table
826 ,p_cal_event_table t_cal_event_table
827 ) IS
828 -- Local variables for process_geo_coverage
829 l_exc_count NUMBER;
830 l_coverage_node_id NUMBER;
831 l_coverage_list t_node_id_table;
832 l_exclusion_list t_node_id_table;
833 l_proc VARCHAR2(100);
834 BEGIN
835 l_proc := 'HR_CAL_EVENT_MAPPING_PKG.process_geo_coverage';
836 hr_utility.set_location('Entering '||l_proc,10);
837 l_exc_count := 0;
838 IF p_cal_event_table.COUNT > 0 THEN
839 hr_utility.set_location(l_proc,20);
840 -- Loop for each entry in the calendar entry table passed in
841 FOR idx IN p_cal_event_table.FIRST .. p_cal_event_table.LAST LOOP
842 hr_utility.set_location(l_proc,30);
843 IF p_cal_event_table(idx).usage = 'COV' THEN
844 hr_utility.set_location(l_proc,40);
845 -- This is the start of a coverage
846 l_coverage_node_id := p_cal_event_table(idx).hierarchy_node_id;
847 ELSIF p_cal_event_table(idx).usage = 'EXC' THEN
848 hr_utility.set_location(l_proc,50);
849 -- Note the exclusion node
850 l_exc_count := l_exc_count + 1;
851 l_exclusion_list(l_exc_count) := p_cal_event_table(idx).hierarchy_node_id;
852 END IF;
853 -- If end of coverage, process the coverage
854 IF p_cal_event_table(idx).entry_flag = 'E' THEN
855 hr_utility.set_location(l_proc,60);
856 l_coverage_list := get_child_nodes(l_coverage_node_id
857 ,l_exclusion_list
858 ,p_geo_hier_table
859 );
860 -- If coverage rows returned, write to cache
861 IF l_coverage_list.COUNT > 0 THEN
862 hr_utility.set_location(l_proc,70);
863 write_geo_cache(l_coverage_list
864 ,p_cal_event_table(idx).calendar_entry_id
865 );
866 END IF; -- coverage rows returned
867
868 -- Reset local variables for next calendar event
869 l_exc_count := 0;
870 l_coverage_node_id := NULL;
871 l_coverage_list.DELETE;
872 l_exclusion_list.DELETE;
873 hr_utility.set_location(l_proc,80);
874
875 END IF; -- end of coverage
876 END LOOP; -- event loop
877 END IF; -- count of events to process
878 hr_utility.set_location('Leaving '||l_proc,90);
879 END process_geo_coverage;
880
881 BEGIN -- populate_geo_list
882 l_proc := 'HR_CAL_EVENT_MAPPING_PKG.populate_geo_list';
883 hr_utility.set_location('Entering '||l_proc,10);
884
885 -- Get the identifiers for all the hierarchies that need to be processed.
886 FOR l_ghv_rec IN c_ghv_ids LOOP
887 hr_utility.set_location('GHVId:'||l_ghv_rec.ghv_id,20);
888
889 -- Get the version of the associated GEO hierarchy
890 OPEN c_ghv_ver_id(l_ghv_rec.ghv_id);
891 FETCH c_ghv_ver_id INTO l_ghv_ver_id;
892 CLOSE c_ghv_ver_id;
893
894 hr_utility.set_location('GHVVerId:'||l_ghv_ver_id,25);
895
896 -- Get the top of the associated GEO hierarchy
897 OPEN c_geo_hier_top(l_ghv_ver_id);
898 FETCH c_geo_hier_top INTO l_top_node_id;
899 CLOSE c_geo_hier_top;
900
901 hr_utility.set_location('TopNodeId:'||l_top_node_id,30);
902
903 -- Get the nodes of the associated GEO hierarchy
904 FOR l_geo_hier_node_rec IN c_geo_hier_nodes(l_ghv_ver_id
905 ,l_top_node_id
906 ) LOOP
907 hr_utility.set_location(l_proc,40);
908 -- Store the GEO hierarchy node
909 l_geo_hier_table(c_geo_hier_nodes%ROWCOUNT) := l_geo_hier_node_rec;
910 END LOOP; -- c_geo_hier_nodes
911
912 hr_utility.set_location(l_proc,50);
913
917 -- Store the event
914 -- Get the calendar events for the GEO Hierarchy
915 FOR l_geo_event_rec IN c_geo_events(l_ghv_rec.ghv_id) LOOP
916 hr_utility.set_location(l_proc,60);
918 l_cal_event_table(c_geo_events%ROWCOUNT) := l_geo_event_rec;
919
920 IF c_geo_events%ROWCOUNT > 1 THEN
921 hr_utility.set_location(l_proc,70);
922
923 -- This is not the first record. Check if different from the
924 -- previous event.
925 IF l_geo_event_rec.calendar_entry_id <>
926 l_cal_event_table(c_geo_events%ROWCOUNT-1).calendar_entry_id THEN
927 hr_utility.set_location(l_proc,80);
928
929 -- Mark end of old event coverage and start of new event coverage
930 l_cal_event_table(c_geo_events%ROWCOUNT-1).entry_flag := 'E';
931 l_cal_event_table(c_geo_events%ROWCOUNT).entry_flag := 'S';
932 END IF;
933
934 ELSE
935 hr_utility.set_location(l_proc,90);
936 -- This is the first record. Mark it as coverage start.
937 l_cal_event_table(c_geo_events%ROWCOUNT).entry_flag := 'S';
938 END IF;
939
940 END LOOP; -- c_geo_events
941
942 -- Mark end of coverage for the last row
943 IF l_cal_event_table.COUNT > 0 THEN
944 hr_utility.set_location(l_proc,100);
945 l_cal_event_table(l_cal_event_table.LAST).entry_flag := 'E';
946 END IF;
947
948 END LOOP; -- c_ghv_ids
949
950 hr_utility.set_location(l_proc,110);
951
952 -- Process coverage data
953 process_geo_coverage(l_geo_hier_table
954 ,l_cal_event_table
955 );
956
957 hr_utility.set_location('Leaving '||l_proc,120);
958 END populate_geo_list;
959
960 --
961 -----------------------------------------------------------------------------
962 ---------------------------< build_event_cache >-----------------------------
963 -----------------------------------------------------------------------------
964 --
965 -- Public procedure which populates the calendar event mapping cache tables
966 -- and generates stats for these.
967 --
968 PROCEDURE build_event_cache(errbuf IN OUT NOCOPY VARCHAR2
969 ,retcode IN OUT NOCOPY NUMBER
970 ) IS
971
972 l_process_date DATE;
973 l_table_owner VARCHAR2(30);
974 l_status VARCHAR2(255);
975 l_industry VARCHAR2(255);
976 l_dummy BOOLEAN;
977 l_proc VARCHAR2(100);
978
979 BEGIN
980 l_proc := 'HR_CAL_EVENT_MAPPING_PKG.build_event_cache';
981 hr_utility.set_location('Entering '||l_proc,10);
982
983 -- Delete previous event org list records as this execution will
984 -- refresh the list
985 DELETE FROM per_cal_entry_org_list;
986 DELETE FROM per_cal_entry_geo_list;
987
988 hr_utility.set_location(l_proc,20);
989
990 -- Generate event list for organization hierarchy coverage.
991 populate_org_list;
992
993 hr_utility.set_location(l_proc,30);
994
995 -- Generate event list for geographic hierarchy coverage.
996 populate_geo_list;
997
998 hr_utility.set_location(l_proc,40);
999
1000 -- Gather stats on the cache tables for current schema (clone)
1001 l_dummy := fnd_installation.get_app_info
1002 (application_short_name => 'PER'
1003 ,status => l_status
1004 ,industry => l_industry
1005 ,oracle_schema => l_table_owner
1006 );
1007
1008 hr_utility.set_location(l_proc,50);
1009
1010 fnd_stats.gather_table_stats(ownname => l_table_owner
1011 ,tabname => 'PER_CAL_ENTRY_ORG_LIST'
1012 ,percent => 50
1013 );
1014
1015 hr_utility.set_location(l_proc,60);
1016
1017 fnd_stats.gather_table_stats(ownname => l_table_owner
1018 ,tabname => 'PER_CAL_ENTRY_GEO_LIST'
1019 ,percent => 50
1020 );
1021
1022 hr_utility.set_location('Leaving '||l_proc,70);
1023 END build_event_cache;
1024
1025 --
1026 -----------------------------------------------------------------------------
1027 ------------------------< get_per_asg_cal_events >---------------------------
1028 -----------------------------------------------------------------------------
1029 --
1030 -- Public function returning a list of calendar events applicable to a person
1031 --
1032 PROCEDURE get_per_asg_cal_events (p_person_id IN NUMBER
1033 ,p_assignment_id IN NUMBER DEFAULT NULL
1034 ,p_event_type IN VARCHAR2 DEFAULT NULL
1035 ,p_start_date IN DATE DEFAULT NULL
1036 ,p_end_date IN DATE DEFAULT NULL
1037 ,p_event_type_flag IN VARCHAR2 DEFAULT NULL
1038 ,x_cal_event_varray IN OUT NOCOPY per_cal_event_varray
1039 ) IS
1040
1041 -- Cursor to fetch person assignment
1042 CURSOR c_per_asg ( cp_person_id NUMBER
1043 , cp_assignment_id NUMBER
1044 , cp_start_date DATE
1045 , cp_end_date DATE
1046 ) IS
1047 SELECT assignment_id,
1048 organization_id, -- ORG Node Id
1049 location_id, -- for GEO mapping
1053 AND (
1050 business_group_id -- for GEO mapping
1051 FROM per_all_assignments_f
1052 WHERE person_id = cp_person_id
1054 (cp_assignment_id IS NOT NULL AND assignment_id = cp_assignment_id)
1055 OR
1056 (cp_assignment_id IS NULL AND primary_flag = 'Y')
1057 )
1058 AND effective_start_date <= NVL(cp_end_date, SYSDATE)
1059 AND effective_end_date >= NVL(cp_start_date, SYSDATE);
1060
1061 -- Cursor to fetch GEO node at assignment EIT level
1062 CURSOR c_asg_geo_node (cp_assignment_id NUMBER) IS
1063 SELECT aei_information1
1064 FROM per_assignment_extra_info
1065 WHERE assignment_id = cp_assignment_id
1066 AND information_type = 'PER_GEO_HIER_NODE_MAP'
1067 AND aei_information_category = 'PER_GEO_HIER_NODE_MAP';
1068
1069 -- Cursor to fetch GEO node at location EIT level
1070 CURSOR c_loc_geo_node (cp_location_id NUMBER) IS
1071 SELECT lei_information1
1072 FROM hr_location_extra_info
1073 WHERE location_id = cp_location_id
1074 AND information_type = 'PER_GEO_HIER_NODE_MAP'
1075 AND lei_information_category = 'PER_GEO_HIER_NODE_MAP';
1076
1077 -- Cursor to fetch GEO node at business group legislation level
1078 CURSOR c_bg_geo_node (cp_business_group_id NUMBER) IS
1079 SELECT org_information9
1080 FROM hr_organization_information
1081 WHERE organization_id = cp_business_group_id
1082 AND org_information_context = 'Business Group Information'
1083 AND attribute_category = 'Business Group Information';
1084
1085 -- Cursor to fetch GEO Node Id
1086 CURSOR c_geo_node_id (cp_geo_node VARCHAR2) IS
1087 SELECT GHN.hierarchy_node_id
1088 FROM per_gen_hierarchy GH,
1089 per_gen_hierarchy_versions GHV,
1090 per_gen_hierarchy_nodes GHN
1091 WHERE GH.type = 'PER_CAL_GEO'
1092 AND GH.hierarchy_id = GHV.hierarchy_id
1093 AND GHV.version_number = 1
1094 AND GHV.hierarchy_version_id = GHN.hierarchy_version_id
1095 AND GHN.entity_id = cp_geo_node;
1096
1097 -- Cursor to fetch ORG events
1098 CURSOR c_org_events ( cp_organization_id NUMBER
1099 , cp_event_type VARCHAR2
1100 , cp_start_date DATE
1101 , cp_end_date DATE
1102 ) IS
1103 SELECT ENT.calendar_entry_id,
1104 ENT.business_group_id,
1105 ENT.name,
1106 ENT.type,
1107 ENT.start_date,
1108 ENT.end_date,
1109 ENT.start_hour,
1110 ENT.end_hour,
1111 ENT.start_min,
1112 ENT.end_min,
1113 ORGENT.ovr_cal_entry_value_id
1114 FROM per_cal_entry_org_list ORGENT,
1115 per_calendar_entries ENT
1116 WHERE ORGENT.organization_id = cp_organization_id
1117 AND ORGENT.calendar_entry_id = ENT.calendar_entry_id
1118 AND ENT.type = NVL(cp_event_type, ENT.type)
1119 AND ENT.start_date <= NVL(cp_end_date, ENT.start_date)
1120 AND ENT.end_date >= NVL(cp_start_date, ENT.end_date);
1121
1122 -- Cursor to fetch GEO events
1123 CURSOR c_geo_events ( cp_geo_node_id NUMBER
1124 , cp_event_type VARCHAR2
1125 , cp_start_date DATE
1126 , cp_end_date DATE
1127 ) IS
1128 SELECT ENT.calendar_entry_id,
1129 ENT.business_group_id,
1130 ENT.name,
1131 ENT.type,
1132 ENT.start_date,
1133 ENT.end_date,
1134 ENT.start_hour,
1135 ENT.end_hour,
1136 ENT.start_min,
1137 ENT.end_min,
1138 GEOENT.ovr_cal_entry_value_id
1139 FROM per_cal_entry_geo_list GEOENT,
1140 per_calendar_entries ENT
1141 WHERE GEOENT.hierarchy_node_id = cp_geo_node_id
1142 AND GEOENT.calendar_entry_id = ENT.calendar_entry_id
1143 AND ENT.type = NVL(cp_event_type, ENT.type)
1144 AND ENT.start_date <= NVL(cp_end_date, ENT.start_date)
1145 AND ENT.end_date >= NVL(cp_start_date, ENT.end_date);
1146
1147 -- Cursor to fetch event override name and type
1148 CURSOR c_event_ovr (cp_ovr_id NUMBER) IS
1149 SELECT override_name
1150 ,override_type
1151 FROM per_cal_entry_values
1152 WHERE cal_entry_value_id = cp_ovr_id;
1153
1154 l_proc VARCHAR2(50);
1155 l_cal_event_obj per_cal_event_obj;
1156 l_event_type_flag VARCHAR2(1);
1157 l_null_times BOOLEAN;
1158 l_not_null_times BOOLEAN;
1159 l_start_date DATE;
1160 l_end_date DATE;
1161
1162 -- Person Assigment attributes
1163 l_assignment_id per_all_assignments_f.assignment_id%TYPE;
1164 l_organization_id per_all_assignments_f.organization_id%TYPE;
1165 l_location_id per_all_assignments_f.location_id%TYPE;
1166 l_business_group_id per_all_assignments_f.business_group_id%TYPE;
1167
1168 -- GEO Node attributes
1169 l_geo_node VARCHAR2(150);
1170 l_geo_node_id NUMBER;
1171
1172 -- Calendar event override attributes
1173 l_ovr_id NUMBER;
1174 l_ovr_name per_cal_entry_values.override_name%TYPE;
1175 l_ovr_type per_cal_entry_values.override_type%TYPE;
1176
1177 -- Local exceptions
1178 e_param_valdn_fail EXCEPTION;
1179 e_bg_leg_not_found EXCEPTION;
1180
1181 BEGIN
1182 l_proc := 'hr_cal_event_mapping_pkg.get_per_asg_cal_events';
1183 hr_utility.set_location('Entering: '|| l_proc, 10);
1184
1185 l_cal_event_obj := per_cal_event_obj(NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
1186 x_cal_event_varray := per_cal_event_varray(); -- initialize empty
1187 l_event_type_flag := NVL(p_event_type_flag, 'B');
1188 l_geo_node := NULL;
1189
1190 -- Range validate supplied start and end dates
1191 IF p_start_date IS NOT NULL AND p_end_date IS NOT NULL THEN
1192 IF p_end_date < p_start_date THEN
1193 hr_utility.set_location(l_proc, 20);
1194 RAISE e_param_valdn_fail;
1195 END IF;
1196 l_start_date := TRUNC(p_start_date);
1197 l_end_date := TRUNC(p_end_date);
1198 END IF;
1199
1200 -- Validate event type flag
1201 IF l_event_type_flag NOT IN ('B', -- Both ORG and GEO events
1202 'O', -- Only ORG events
1203 'G' -- Only GEO events
1204 ) THEN
1205 hr_utility.set_location(l_proc, 30);
1206 RAISE e_param_valdn_fail;
1207 END IF;
1208
1209 -- Get the person assignment identifier
1210 OPEN c_per_asg ( p_person_id
1211 , p_assignment_id
1212 , p_start_date
1213 , p_end_date
1214 );
1215 FETCH c_per_asg INTO l_assignment_id
1216 , l_organization_id
1217 , l_location_id
1218 , l_business_group_id;
1219 CLOSE c_per_asg;
1220
1221 hr_utility.set_location('PerId: '||p_person_id||' AsgId: '||l_assignment_id, 30);
1222
1223 -- Get GEO node from assignment EIT if present
1224 IF l_event_type_flag IN ('B','G') THEN
1225 BEGIN
1226 OPEN c_asg_geo_node (l_assignment_id);
1227 FETCH c_asg_geo_node INTO l_geo_node;
1228 CLOSE c_asg_geo_node;
1229 hr_utility.set_location('GEONode: '||l_geo_node, 40);
1230 EXCEPTION
1231 WHEN OTHERS THEN
1232 -- GEO node not found at assignment EIT level
1233 hr_utility.set_location(l_proc, 45);
1234 END;
1235 END IF; -- event flag is 'B' or 'G'
1236
1237 -- Get GEO node from location EIT if present
1238 IF l_event_type_flag IN ('B','G') AND l_geo_node IS NULL THEN
1239 BEGIN
1240 OPEN c_loc_geo_node (l_location_id);
1241 FETCH c_loc_geo_node INTO l_geo_node;
1242 CLOSE c_loc_geo_node;
1243 hr_utility.set_location('GEONode: '||l_geo_node, 50);
1244 EXCEPTION
1245 WHEN OTHERS THEN
1246 -- GEO node not found at location EIT level
1247 hr_utility.set_location(l_proc, 55);
1248 END;
1249 END IF; -- event flag is ('B' or 'G') and GEO Node not yet found
1250
1251 -- Get GEO node from business group legislation
1252 IF l_event_type_flag IN ('B','G') AND l_geo_node IS NULL THEN
1253 BEGIN
1254 OPEN c_bg_geo_node (l_business_group_id);
1255 FETCH c_bg_geo_node INTO l_geo_node;
1256 CLOSE c_bg_geo_node;
1257 hr_utility.set_location('GEONode: '||l_geo_node, 60);
1258 EXCEPTION
1259 WHEN OTHERS THEN
1260 -- GEO node not found at business group legislation level
1261 hr_utility.set_location(l_proc, 65);
1262 RAISE e_bg_leg_not_found;
1263 END;
1264 END IF; -- event flag is ('B' or 'G') and GEO Node not yet found
1265
1266 -- Get GEO Node Id
1267 IF l_event_type_flag IN ('B','G') AND l_geo_node IS NOT NULL THEN
1268 OPEN c_geo_node_id (l_geo_node);
1269 FETCH c_geo_node_id INTO l_geo_node_id;
1270 CLOSE c_geo_node_id;
1271 hr_utility.set_location('GEONodeId: '||l_geo_node_id, 70);
1272 END IF; -- event flag is ('B' or 'G') and GEO Node found
1273
1274 -- Get ORG Events if required
1275 IF l_event_type_flag IN ('B','O') AND l_organization_id IS NOT NULL THEN
1276 OPEN c_org_events (l_organization_id
1277 ,p_event_type
1278 ,l_start_date
1279 ,p_end_date
1280 );
1281 LOOP -- ORG Events
1282 l_ovr_id := NULL;
1283 l_ovr_name := NULL;
1284 l_ovr_type := NULL;
1285
1286 FETCH c_org_events INTO l_cal_event_obj.cal_event_id
1287 ,l_cal_event_obj.business_group_id
1288 ,l_cal_event_obj.event_name
1289 ,l_cal_event_obj.event_type
1290 ,l_cal_event_obj.start_date
1291 ,l_cal_event_obj.end_date
1292 ,l_cal_event_obj.start_hour
1293 ,l_cal_event_obj.end_hour
1294 ,l_cal_event_obj.start_minute
1295 ,l_cal_event_obj.end_minute
1296 ,l_ovr_id;
1297 EXIT WHEN c_org_events%NOTFOUND;
1298
1299 -- Handle incomplete times
1300 l_null_times := FALSE;
1301 l_not_null_times := FALSE;
1302 IF l_cal_event_obj.start_hour IS NULL THEN
1303 l_null_times := TRUE;
1304 ELSE
1305 l_not_null_times := TRUE;
1306 END IF;
1307 IF l_cal_event_obj.end_hour IS NULL THEN
1308 l_null_times := TRUE;
1309 ELSE
1310 l_not_null_times := TRUE;
1311 END IF;
1312 IF l_cal_event_obj.start_minute IS NULL THEN
1313 l_null_times := TRUE;
1314 ELSE
1315 l_not_null_times := TRUE;
1316 END IF;
1317 IF l_cal_event_obj.end_minute IS NULL THEN
1318 l_null_times := TRUE;
1319 ELSE
1320 l_not_null_times := TRUE;
1321 END IF;
1322 IF l_null_times AND l_not_null_times THEN
1323 -- Mixed nulls have been entered i.e. incomplete times
1324 IF l_cal_event_obj.start_hour IS NULL THEN
1328 l_cal_event_obj.end_hour := '0';
1325 l_cal_event_obj.start_hour := '0';
1326 END IF;
1327 IF l_cal_event_obj.end_hour IS NULL THEN
1329 END IF;
1330 IF l_cal_event_obj.start_minute IS NULL THEN
1331 l_cal_event_obj.start_minute := '0';
1332 END IF;
1333 IF l_cal_event_obj.end_minute IS NULL THEN
1334 l_cal_event_obj.end_minute := '0';
1335 END IF;
1336 END IF;
1337
1338 -- Adjust date for same day events for CAC integration
1339 IF (
1340 (l_cal_event_obj.start_hour IS NULL AND
1341 l_cal_event_obj.end_hour IS NULL AND
1342 l_cal_event_obj.start_minute IS NULL AND
1343 l_cal_event_obj.end_minute IS NULL
1344 )
1345 OR
1346 (l_cal_event_obj.start_hour IS NOT NULL AND
1347 l_cal_event_obj.end_hour IS NOT NULL AND
1348 l_cal_event_obj.start_minute IS NOT NULL AND
1349 l_cal_event_obj.end_minute IS NOT NULL AND
1350 l_cal_event_obj.start_hour = l_cal_event_obj.end_hour AND
1351 l_cal_event_obj.start_minute = l_cal_event_obj.end_minute AND
1352 l_cal_event_obj.start_hour = '0' AND
1353 l_cal_event_obj.start_minute = '0' AND
1354 l_cal_event_obj.start_date = l_cal_event_obj.end_date
1355 )
1356 ) THEN
1357 l_cal_event_obj.end_date := l_cal_event_obj.end_date + 1;
1358 IF (l_cal_event_obj.start_hour IS NULL AND
1359 l_cal_event_obj.end_hour IS NULL AND
1360 l_cal_event_obj.start_minute IS NULL AND
1361 l_cal_event_obj.end_minute IS NULL) THEN
1362 l_cal_event_obj.start_hour:= '0';
1363 l_cal_event_obj.end_hour := '0';
1364 l_cal_event_obj.start_minute := '0';
1365 l_cal_event_obj.end_minute := '0';
1366 END IF;
1367 END IF;
1368
1369 -- Fetch override if it exists
1370 IF l_ovr_id IS NOT NULL THEN
1371 OPEN c_event_ovr (l_ovr_id);
1372 FETCH c_event_ovr INTO l_ovr_name,
1373 l_ovr_type;
1374 CLOSE c_event_ovr;
1375 l_cal_event_obj.event_name := NVL(l_ovr_name, l_cal_event_obj.event_name);
1376 l_cal_event_obj.event_type := NVL(l_ovr_type, l_cal_event_obj.event_type);
1377 END IF;
1378
1379 x_cal_event_varray.EXTEND(1);
1380 x_cal_event_varray(x_cal_event_varray.COUNT) := l_cal_event_obj;
1381 END LOOP; -- ORG Events
1382 CLOSE c_org_events;
1383 hr_utility.set_location(l_proc, 80);
1384 END IF; -- event flag is ('B' or ')') and ORG Node Id found
1385
1386 -- Get GEO Events if required
1387 IF l_event_type_flag IN ('B','G') AND l_geo_node_id IS NOT NULL THEN
1388 OPEN c_geo_events (l_geo_node_id
1389 ,p_event_type
1390 ,l_start_date
1391 ,p_end_date
1392 );
1393 LOOP -- GEO Events
1394 l_ovr_id := NULL;
1395 l_ovr_name := NULL;
1396 l_ovr_type := NULL;
1397
1398 FETCH c_geo_events INTO l_cal_event_obj.cal_event_id
1399 ,l_cal_event_obj.business_group_id
1400 ,l_cal_event_obj.event_name
1401 ,l_cal_event_obj.event_type
1402 ,l_cal_event_obj.start_date
1403 ,l_cal_event_obj.end_date
1404 ,l_cal_event_obj.start_hour
1405 ,l_cal_event_obj.end_hour
1406 ,l_cal_event_obj.start_minute
1407 ,l_cal_event_obj.end_minute
1408 ,l_ovr_id;
1409 EXIT WHEN c_geo_events%NOTFOUND;
1410
1411 -- Handle incomplete times
1412 l_null_times := FALSE;
1413 l_not_null_times := FALSE;
1414 IF l_cal_event_obj.start_hour IS NULL THEN
1415 l_null_times := TRUE;
1416 ELSE
1417 l_not_null_times := TRUE;
1418 END IF;
1419 IF l_cal_event_obj.end_hour IS NULL THEN
1420 l_null_times := TRUE;
1421 ELSE
1422 l_not_null_times := TRUE;
1423 END IF;
1424 IF l_cal_event_obj.start_minute IS NULL THEN
1425 l_null_times := TRUE;
1426 ELSE
1427 l_not_null_times := TRUE;
1428 END IF;
1429 IF l_cal_event_obj.end_minute IS NULL THEN
1430 l_null_times := TRUE;
1431 ELSE
1432 l_not_null_times := TRUE;
1433 END IF;
1434 IF l_null_times AND l_not_null_times THEN
1435 -- Mixed nulls have been entered i.e. incomplete times
1436 IF l_cal_event_obj.start_hour IS NULL THEN
1437 l_cal_event_obj.start_hour := '0';
1438 END IF;
1439 IF l_cal_event_obj.end_hour IS NULL THEN
1440 l_cal_event_obj.end_hour := '0';
1441 END IF;
1442 IF l_cal_event_obj.start_minute IS NULL THEN
1443 l_cal_event_obj.start_minute := '0';
1444 END IF;
1445 IF l_cal_event_obj.end_minute IS NULL THEN
1446 l_cal_event_obj.end_minute := '0';
1447 END IF;
1448 END IF;
1449
1450 -- Adjust date for same day events for CAC integration
1451 IF (
1452 (l_cal_event_obj.start_hour IS NULL AND
1453 l_cal_event_obj.end_hour IS NULL AND
1454 l_cal_event_obj.start_minute IS NULL AND
1455 l_cal_event_obj.end_minute IS NULL
1456 )
1457 OR
1458 (l_cal_event_obj.start_hour IS NOT NULL AND
1462 l_cal_event_obj.start_hour = l_cal_event_obj.end_hour AND
1459 l_cal_event_obj.end_hour IS NOT NULL AND
1460 l_cal_event_obj.start_minute IS NOT NULL AND
1461 l_cal_event_obj.end_minute IS NOT NULL AND
1463 l_cal_event_obj.start_minute = l_cal_event_obj.end_minute AND
1464 l_cal_event_obj.start_hour = '0' AND
1465 l_cal_event_obj.start_minute = '0' AND
1466 l_cal_event_obj.start_date = l_cal_event_obj.end_date
1467 )
1468 ) THEN
1469 l_cal_event_obj.end_date := l_cal_event_obj.end_date + 1;
1470 IF (l_cal_event_obj.start_hour IS NULL AND
1471 l_cal_event_obj.end_hour IS NULL AND
1472 l_cal_event_obj.start_minute IS NULL AND
1473 l_cal_event_obj.end_minute IS NULL) THEN
1474 l_cal_event_obj.start_hour:= '0';
1475 l_cal_event_obj.end_hour := '0';
1476 l_cal_event_obj.start_minute := '0';
1477 l_cal_event_obj.end_minute := '0';
1478 END IF;
1479 END IF;
1480
1481 -- Fetch override if it exists
1482 IF l_ovr_id IS NOT NULL THEN
1483 OPEN c_event_ovr (l_ovr_id);
1484 FETCH c_event_ovr INTO l_ovr_name,
1485 l_ovr_type;
1486 CLOSE c_event_ovr;
1487 l_cal_event_obj.event_name := NVL(l_ovr_name, l_cal_event_obj.event_name);
1488 l_cal_event_obj.event_type := NVL(l_ovr_type, l_cal_event_obj.event_type);
1489 END IF;
1490
1491 x_cal_event_varray.EXTEND(1);
1492 x_cal_event_varray(x_cal_event_varray.COUNT) := l_cal_event_obj;
1493 END LOOP; -- GEO Events
1494 CLOSE c_geo_events;
1495 hr_utility.set_location(l_proc, 90);
1496 END IF; -- event flag is ('B' or 'G') and GEO Node Id found
1497
1498 hr_utility.set_location('Leaving: '|| l_proc, 100);
1499
1500 EXCEPTION
1501
1502 WHEN e_param_valdn_fail THEN
1503 hr_utility.set_location('Leaving: '|| l_proc, 110);
1504
1505 WHEN e_bg_leg_not_found THEN
1506 hr_utility.set_location('Leaving: '|| l_proc, 120);
1507
1508 WHEN OTHERS THEN
1509 hr_utility.set_location('Leaving: '|| l_proc, 130);
1510 hr_utility.set_location(SQLERRM, 135);
1511
1512 END get_per_asg_cal_events;
1513
1514 --
1515 -----------------------------------------------------------------------------
1516 -------------------< get_cal_events (Person Version) >-----------------------
1517 -----------------------------------------------------------------------------
1518 --
1519 -- Public function returning a list of calendar events applicable to a person
1520 --
1521 FUNCTION get_cal_events (p_person_id IN NUMBER
1522 ,p_event_type IN VARCHAR2 DEFAULT NULL
1523 ,p_start_date IN DATE DEFAULT NULL
1524 ,p_end_date IN DATE DEFAULT NULL
1525 ,p_event_type_flag IN VARCHAR2 DEFAULT NULL
1526 ) RETURN per_cal_event_varray IS
1527
1528 l_proc VARCHAR2(60);
1529 l_cal_event_varray per_cal_event_varray;
1530
1531 BEGIN
1532
1533 l_proc := 'hr_cal_event_mapping_pkg.get_cal_events (PerVer)';
1534 hr_utility.set_location('Entering: '|| l_proc, 10);
1535
1536 -- Invoke private procedure to get calendar events
1537 get_per_asg_cal_events (p_person_id => p_person_id
1538 ,p_assignment_id => ''
1539 ,p_event_type => p_event_type
1540 ,p_start_date => p_start_date
1541 ,p_end_date => p_end_date
1542 ,p_event_type_flag => p_event_type_flag
1543 ,x_cal_event_varray => l_cal_event_varray
1544 );
1545
1546 hr_utility.set_location('Leaving: '|| l_proc, 20);
1547 RETURN l_cal_event_varray;
1548
1549 EXCEPTION
1550
1551 WHEN OTHERS THEN
1552 hr_utility.set_location('Leaving: '|| l_proc, 30);
1553 hr_utility.set_location(SQLERRM, 35);
1554 RETURN l_cal_event_varray;
1555
1556 END get_cal_events; -- Person Version
1557
1558 --
1559 -----------------------------------------------------------------------------
1560 ----------------< get_cal_events (Assignment Version) >----------------------
1561 -----------------------------------------------------------------------------
1562 --
1563 -- Public function returning a list of calendar events applicable to an
1564 -- assignment.
1565 --
1566 FUNCTION get_cal_events (p_assignment_id IN NUMBER
1567 ,p_event_type IN VARCHAR2 DEFAULT NULL
1568 ,p_start_date IN DATE DEFAULT NULL
1569 ,p_end_date IN DATE DEFAULT NULL
1570 ,p_event_type_flag IN VARCHAR2 DEFAULT NULL
1571 ) RETURN per_cal_event_varray IS
1572
1573 l_proc VARCHAR2(60);
1574 l_cal_event_varray per_cal_event_varray;
1575 l_person_id NUMBER;
1576
1577 -- Cursor to get the person id for an assignment id.
1578 CURSOR c_per_id (cp_assignment_id NUMBER) IS
1579 SELECT person_id
1580 FROM per_all_assignments_f
1581 WHERE assignment_id = cp_assignment_id;
1582
1583 BEGIN
1584
1585 l_proc := 'hr_cal_event_mapping_pkg.get_cal_events (AsgVer)';
1586 hr_utility.set_location('Entering: '|| l_proc, 10);
1587
1588 -- Get the person id for an assignment id. Though multiple records could
1589 -- exist, the person id will be the same. So sufficient to fetch first.
1590 OPEN c_per_id (p_assignment_id);
1594 hr_utility.set_location(l_proc, 20);
1591 FETCH c_per_id INTO l_person_id;
1592 CLOSE c_per_id;
1593
1595
1596 -- Invoke private procedure to get calendar events
1597 get_per_asg_cal_events (p_person_id => l_person_id
1598 ,p_assignment_id => p_assignment_id
1599 ,p_event_type => p_event_type
1600 ,p_start_date => p_start_date
1601 ,p_end_date => p_end_date
1602 ,p_event_type_flag => p_event_type_flag
1603 ,x_cal_event_varray => l_cal_event_varray
1604 );
1605
1606 hr_utility.set_location('Leaving: '|| l_proc, 30);
1607 RETURN l_cal_event_varray;
1608
1609 EXCEPTION
1610
1611 WHEN OTHERS THEN
1612 hr_utility.set_location('Leaving: '|| l_proc, 40);
1613 hr_utility.set_location(SQLERRM, 45);
1614 RETURN l_cal_event_varray;
1615
1616 END get_cal_events; -- Assignment Version
1617
1618 --
1619 -----------------------------------------------------------------------------
1620 ------------------< get_cal_events (HZ Party Version) >----------------------
1621 -----------------------------------------------------------------------------
1622 --
1623 -- Public function returning a list of calendar events applicable to an HZ
1624 -- party.
1625 --
1626 FUNCTION get_cal_events (p_hz_party_id IN NUMBER
1627 ,p_event_type IN VARCHAR2 DEFAULT NULL
1628 ,p_start_date IN DATE DEFAULT NULL
1629 ,p_end_date IN DATE DEFAULT NULL
1630 ,p_event_type_flag IN VARCHAR2 DEFAULT NULL
1631 ) RETURN per_cal_event_varray IS
1632
1633 l_proc VARCHAR2(60);
1634 l_cal_event_varray per_cal_event_varray;
1635 l_person_id NUMBER;
1636
1637 -- Cursor to get the party id for a person id.
1638 CURSOR c_per_id (cp_party_id NUMBER) IS
1639 SELECT person_identifier
1640 FROM hz_parties
1641 WHERE party_id = cp_party_id
1642 AND created_by_module = 'HR API'
1643 AND orig_system_reference = 'PER:'||person_identifier;
1644
1645 BEGIN
1646
1647 l_proc := 'hr_cal_event_mapping_pkg.get_cal_events (HZVer)';
1648 hr_utility.set_location('Entering: '|| l_proc, 10);
1649
1650 -- Get the person id for a party id. Though multiple records could
1651 -- exist, the person id will be the same. So sufficient to fetch first.
1652 OPEN c_per_id (p_hz_party_id);
1653 FETCH c_per_id INTO l_person_id;
1654 CLOSE c_per_id;
1655
1656 hr_utility.set_location(l_proc, 20);
1657
1658 -- Invoke private procedure to get calendar events
1659 get_per_asg_cal_events (p_person_id => l_person_id
1660 ,p_assignment_id => ''
1661 ,p_event_type => p_event_type
1662 ,p_start_date => p_start_date
1663 ,p_end_date => p_end_date
1664 ,p_event_type_flag => p_event_type_flag
1665 ,x_cal_event_varray => l_cal_event_varray
1666 );
1667
1668 hr_utility.set_location('Leaving: '|| l_proc, 30);
1669 RETURN l_cal_event_varray;
1670
1671 EXCEPTION
1672
1673 WHEN OTHERS THEN
1674 hr_utility.set_location('Leaving: '|| l_proc, 40);
1675 hr_utility.set_location(SQLERRM, 45);
1676 RETURN l_cal_event_varray;
1677
1678 END get_cal_events; -- HZ Party Version
1679
1680 --
1681 -----------------------------------------------------------------------------
1682 --------------------------< get_all_cal_events >-----------------------------
1683 -----------------------------------------------------------------------------
1684 --
1685 -- This function returns all the calendar events in the system or filtered
1686 -- as per given criteria.
1687 --
1688 FUNCTION get_all_cal_events (p_event_type IN VARCHAR2 DEFAULT NULL
1689 ,p_start_date IN DATE DEFAULT NULL
1690 ,p_end_date IN DATE DEFAULT NULL
1691 ) RETURN per_cal_event_varray IS
1692
1693 l_proc VARCHAR2(60);
1694 l_cal_event_varray per_cal_event_varray;
1695 l_cal_event_obj per_cal_event_obj;
1696 l_null_times BOOLEAN;
1697 l_not_null_times BOOLEAN;
1698 l_bg_id NUMBER;
1699 l_start_date DATE;
1700 l_end_date DATE;
1701
1702 -- Cursor to fetch calendar events
1703 CURSOR c_cal_events ( cp_event_type VARCHAR2
1704 , cp_start_date DATE
1705 , cp_end_date DATE
1706 , cp_bg_id NUMBER
1707 ) IS
1708 SELECT calendar_entry_id,
1709 business_group_id,
1710 name,
1711 type,
1712 start_date,
1713 end_date,
1714 start_hour,
1715 end_hour,
1716 start_min,
1717 end_min
1718 FROM per_calendar_entries
1719 WHERE type = NVL(cp_event_type, type)
1720 AND start_date <= NVL(cp_end_date, start_date)
1721 AND end_date >= NVL(cp_start_date, end_date)
1722 AND (business_group_id IS NULL
1723 OR
1724 (business_group_id IS NOT NULL AND
1725 business_group_id = NVL(cp_bg_id, business_group_id)
1726 )
1727 );
1728
1729 BEGIN
1730
1731 l_proc := 'hr_cal_event_mapping_pkg.get_all_cal_events';
1735 l_bg_id := FND_PROFILE.Value('PER_BUSINESS_GROUP_ID');
1732 hr_utility.set_location('Entering: '|| l_proc, 10);
1733 l_cal_event_obj := per_cal_event_obj(NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
1734 l_cal_event_varray := per_cal_event_varray(); -- initialize empty
1736 l_start_date := TRUNC(p_start_date);
1737 l_end_date := TRUNC(p_end_date);
1738
1739 OPEN c_cal_events (p_event_type
1740 ,l_start_date
1741 ,p_end_date
1742 ,l_bg_id
1743 );
1744 LOOP -- Cal Events
1745 FETCH c_cal_events INTO l_cal_event_obj.cal_event_id
1746 ,l_cal_event_obj.business_group_id
1747 ,l_cal_event_obj.event_name
1748 ,l_cal_event_obj.event_type
1749 ,l_cal_event_obj.start_date
1750 ,l_cal_event_obj.end_date
1751 ,l_cal_event_obj.start_hour
1752 ,l_cal_event_obj.end_hour
1753 ,l_cal_event_obj.start_minute
1754 ,l_cal_event_obj.end_minute;
1755 EXIT WHEN c_cal_events%NOTFOUND;
1756
1757 -- Handle incomplete times
1758 l_null_times := FALSE;
1759 l_not_null_times := FALSE;
1760 IF l_cal_event_obj.start_hour IS NULL THEN
1761 l_null_times := TRUE;
1762 ELSE
1763 l_not_null_times := TRUE;
1764 END IF;
1765 IF l_cal_event_obj.end_hour IS NULL THEN
1766 l_null_times := TRUE;
1767 ELSE
1768 l_not_null_times := TRUE;
1769 END IF;
1770 IF l_cal_event_obj.start_minute IS NULL THEN
1771 l_null_times := TRUE;
1772 ELSE
1773 l_not_null_times := TRUE;
1774 END IF;
1775 IF l_cal_event_obj.end_minute IS NULL THEN
1776 l_null_times := TRUE;
1777 ELSE
1778 l_not_null_times := TRUE;
1779 END IF;
1780 IF l_null_times AND l_not_null_times THEN
1781 -- Mixed nulls have been entered i.e. incomplete times
1782 IF l_cal_event_obj.start_hour IS NULL THEN
1783 l_cal_event_obj.start_hour := '0';
1784 END IF;
1785 IF l_cal_event_obj.end_hour IS NULL THEN
1786 l_cal_event_obj.end_hour := '0';
1787 END IF;
1788 IF l_cal_event_obj.start_minute IS NULL THEN
1789 l_cal_event_obj.start_minute := '0';
1790 END IF;
1791 IF l_cal_event_obj.end_minute IS NULL THEN
1792 l_cal_event_obj.end_minute := '0';
1793 END IF;
1794 END IF;
1795
1796 -- Adjust date for same day events for CAC integration
1797 IF (
1798 (l_cal_event_obj.start_hour IS NULL AND
1799 l_cal_event_obj.end_hour IS NULL AND
1800 l_cal_event_obj.start_minute IS NULL AND
1801 l_cal_event_obj.end_minute IS NULL
1802 )
1803 OR
1804 (l_cal_event_obj.start_hour IS NOT NULL AND
1805 l_cal_event_obj.end_hour IS NOT NULL AND
1806 l_cal_event_obj.start_minute IS NOT NULL AND
1807 l_cal_event_obj.end_minute IS NOT NULL AND
1808 l_cal_event_obj.start_hour = l_cal_event_obj.end_hour AND
1809 l_cal_event_obj.start_minute = l_cal_event_obj.end_minute AND
1810 l_cal_event_obj.start_hour = '0' AND
1811 l_cal_event_obj.start_minute = '0' AND
1812 l_cal_event_obj.start_date = l_cal_event_obj.end_date
1813 )
1814 ) THEN
1815 l_cal_event_obj.end_date := l_cal_event_obj.end_date + 1;
1816 IF (l_cal_event_obj.start_hour IS NULL AND
1817 l_cal_event_obj.end_hour IS NULL AND
1818 l_cal_event_obj.start_minute IS NULL AND
1819 l_cal_event_obj.end_minute IS NULL) THEN
1820 l_cal_event_obj.start_hour:= '0';
1821 l_cal_event_obj.end_hour := '0';
1822 l_cal_event_obj.start_minute := '0';
1823 l_cal_event_obj.end_minute := '0';
1824 END IF;
1825 END IF;
1826
1827 l_cal_event_varray.EXTEND(1);
1828 l_cal_event_varray(l_cal_event_varray.COUNT) := l_cal_event_obj;
1829 END LOOP; -- ORG Events
1830 CLOSE c_cal_events;
1831
1832 hr_utility.set_location('Leaving: '|| l_proc, 20);
1833 RETURN l_cal_event_varray;
1834
1835 EXCEPTION
1836
1837 WHEN OTHERS THEN
1838 hr_utility.set_location('Leaving: '|| l_proc, 30);
1839 hr_utility.set_location(SQLERRM, 35);
1840 RETURN l_cal_event_varray;
1841
1842 END get_all_cal_events;
1843
1844 --
1845 -----------------------------------------------------------------------------
1846 -------------------------< build_cal_map_cache >-----------------------------
1847 -----------------------------------------------------------------------------
1848 --
1849 -- This procedure builds transient data into table PER_CAL_MAP_CACHE for
1850 -- use by the calendar mapping user interface.
1851 --
1852 PROCEDURE build_cal_map_cache (p_person_id IN NUMBER
1853 ,p_assignment_id IN NUMBER
1854 ,p_event_type IN VARCHAR2 DEFAULT NULL
1855 ,p_start_date IN DATE DEFAULT NULL
1856 ,p_end_date IN DATE DEFAULT NULL
1857 ) IS
1858
1859 l_proc VARCHAR2(60);
1860 l_cal_event_varray per_cal_event_varray;
1861 l_cal_event_obj per_cal_event_obj;
1862
1863 BEGIN
1864
1865 l_proc := 'hr_cal_event_mapping_pkg.build_cal_map_cache';
1866 hr_utility.set_location('Entering: '|| l_proc, 10);
1867 l_cal_event_obj := per_cal_event_obj(NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
1868 l_cal_event_varray := per_cal_event_varray(); -- initialize empty
1869
1870 l_cal_event_varray := get_cal_events ( p_assignment_id => p_assignment_id
1871 , p_event_type => p_event_type
1872 , p_start_date => p_start_date
1873 , p_end_date => p_end_date
1874 );
1875
1876 hr_utility.set_location(l_proc, 20);
1877
1878 DELETE FROM per_cal_map_cache;
1879
1880 hr_utility.set_location(l_proc, 30);
1881
1882 IF l_cal_event_varray.COUNT > 0 THEN
1883 hr_utility.set_location(l_proc, 35);
1884
1885 FOR idx IN l_cal_event_varray.FIRST..l_cal_event_varray.LAST LOOP
1886 l_cal_event_obj := l_cal_event_varray(idx);
1887 INSERT INTO per_cal_map_cache
1888 (person_id
1889 ,assignment_id
1890 ,event_name
1891 ,event_type
1892 ,start_date
1893 ,end_date
1894 ,start_hour
1895 ,end_hour
1896 ,start_minute
1897 ,end_minute
1898 )
1899 VALUES
1900 (p_person_id
1901 ,p_assignment_id
1902 ,l_cal_event_obj.event_name
1903 ,l_cal_event_obj.event_type
1904 ,l_cal_event_obj.start_date
1905 ,l_cal_event_obj.end_date
1906 ,l_cal_event_obj.start_hour
1907 ,l_cal_event_obj.end_hour
1908 ,l_cal_event_obj.start_minute
1909 ,l_cal_event_obj.end_minute
1910 );
1911 END LOOP;
1912 END IF;
1913
1914 hr_utility.set_location(l_proc, 40);
1915
1916 COMMIT;
1917
1918 hr_utility.set_location('Leaving: '|| l_proc, 50);
1919
1920 EXCEPTION
1921
1922 WHEN OTHERS THEN
1923 hr_utility.set_location('Leaving: '|| l_proc, 60);
1924 hr_utility.set_location(SQLERRM, 65);
1925
1926 END build_cal_map_cache;
1927
1928 END hr_cal_event_mapping_pkg;
[Home] [Help]