[Home] [Help]
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;
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:
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);
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;
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;
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);
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;
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: -------------------------------------------------------------------------
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
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:
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
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
230: END IF;
231:
232: COMMIT;
233:
234: hr_utility.set_location('Leaving '||l_proc,20);
235: END write_org_cache;
236:
237: --
238: -------------------------------------------------------------------------
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);
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;
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;
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:
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:
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.
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;
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;
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
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
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
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
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
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:
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:
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
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
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;
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);
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;
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: );
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
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;
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';
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);
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;
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;
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
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);
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:
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';
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;
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);
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
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: -----------------------------------------------------------------------------
642: l_proc VARCHAR2(100);
643:
644: BEGIN
645: l_proc := 'HR_CAL_EVENT_MAPPING_PKG.write_geo_cache';
646: hr_utility.set_location('Entering '||l_proc,10);
647:
648: IF p_coverage_list.COUNT > 0 THEN
649: hr_utility.set_location(l_proc,12);
650: FOR idx IN p_coverage_list.FIRST .. p_coverage_list.LAST LOOP
645: l_proc := 'HR_CAL_EVENT_MAPPING_PKG.write_geo_cache';
646: hr_utility.set_location('Entering '||l_proc,10);
647:
648: IF p_coverage_list.COUNT > 0 THEN
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:
647:
648: IF p_coverage_list.COUNT > 0 THEN
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
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
684: END IF;
685:
686: COMMIT;
687:
688: hr_utility.set_location('Leaving '||l_proc,20);
689: END write_geo_cache;
690:
691: --
692: -------------------------------------------------------------------------
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);
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;
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;
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:
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);
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
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;
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:
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);
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:
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
785: END IF;
786:
787: -- Check for node exclusion
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
791: ,p_exclusion_list
792: );
793: IF l_exclude_flag THEN
790: l_exclude_flag := is_node_in_list(p_geo_hier_table(idx).hierarchy_node_id
791: ,p_exclusion_list
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
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:
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:
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
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
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;
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);
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;
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: );
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
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
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';
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);
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;
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;
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
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:
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:
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);
912: hr_utility.set_location(l_proc,50);
913:
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);
917: -- Store the event
918: l_cal_event_table(c_geo_events%ROWCOUNT) := l_geo_event_rec;
919:
920: IF c_geo_events%ROWCOUNT > 1 THEN
917: -- Store the event
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 <>
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';
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
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
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: -----------------------------------------------------------------------------
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;
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:
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:
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'
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
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
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: -----------------------------------------------------------------------------
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');
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);
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
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
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);
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
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);
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
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);
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:
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
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
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:
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
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:
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);
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:
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: --
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 => ''
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:
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
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:
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);
1590: OPEN c_per_id (p_assignment_id);
1591: FETCH c_per_id INTO l_person_id;
1592: CLOSE c_per_id;
1593:
1594: hr_utility.set_location(l_proc, 20);
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
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:
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
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:
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);
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 => ''
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:
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
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:
1728:
1729: BEGIN
1730:
1731: l_proc := 'hr_cal_event_mapping_pkg.get_all_cal_events';
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
1735: l_bg_id := FND_PROFILE.Value('PER_BUSINESS_GROUP_ID');
1736: l_start_date := TRUNC(p_start_date);
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:
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;
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:
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
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);
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:
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
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);
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
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:
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;