[Home] [Help]
PACKAGE BODY: APPS.HXC_RETRIEVE_ABSENCES
Source
1 PACKAGE BODY HXC_RETRIEVE_ABSENCES AS
2 /* $Header: hxcretabs.pkb 120.43.12020000.2 2012/10/09 14:59:12 asrajago ship $ */
3
4 g_debug BOOLEAN := hr_utility.debug_enabled;
5
6
7 TYPE ALIAS_TAB IS TABLE OF VARCHAR2(400) INDEX BY BINARY_INTEGER;
8 TYPE NUMTABLE IS TABLE OF NUMBER;
9 TYPE VARCHARTABLE IS TABLE OF VARCHAR2(500);
10 g_alias_tab ALIAS_TAB;
11 g_pref_table hxc_preference_evaluation.t_pref_table;
12 g_dummy_bbit NUMBER;
13 g_alias_bbit NUMBER;
14 g_layout_bbit NUMBER;
15 g_pending_appr BOOLEAN := FALSE;
16 g_pending_delete BOOLEAN := FALSE;
17 -- Bug 8995913
18 -- Added the following flag.
19 g_pending_conf BOOLEAN := FALSE;
20
21
22
23
24 PROCEDURE retrieve_absences( p_person_id IN NUMBER,
25 p_start_date IN DATE,
26 p_end_date IN DATE,
27 p_abs_tab IN OUT NOCOPY hxc_retrieve_absences.abs_tab)
28 AS
29
30
31 l_abs_org_tab hr_person_absence_api.abs_data;
32 l_abs_inv hr_person_absence_api.abs_data_inv;
33
34
35
36 l_abs_att_id NUMBER;
37 l_abs_type_id NUMBER;
38 l_days NUMBER;
39 l_hours NUMBER;
40 l_start DATE;
41 l_end DATE;
42 l_element_type_id NUMBER;
43 l_start_time DATE;
44 l_stop_time DATE;
45
46 l_abs_tab ABS_TAB;
47 l_ind BINARY_INTEGER;
48
49 BEGIN
50
51 l_ind := 1;
52 -- Bug 8864418
53 g_pending_appr := FALSE;
54 g_pending_delete := FALSE;
55 -- Bug 8995913
56 g_pending_conf := FALSE;
57
58 hr_person_absence_api.get_absence_data(p_person_id=>p_person_id,
59 p_start_date => p_start_date,
60 p_end_date => p_end_date,
61 absence_records => l_abs_org_tab,
62 absence_records_inv => l_abs_inv );
63
64
65 IF l_abs_org_tab.COUNT > 0
66 THEN
67 FOR i in l_abs_org_tab.FIRST..l_abs_org_tab.LAST
68 LOOP
69 << CONTINUE_TO_NEXT >>
70 LOOP
71
72 IF g_debug
73 THEN
74 hr_utility.trace('ABS : l_abs_org_tab(i).absence_type_id '||l_abs_org_tab(i).absence_type_id);
75 hr_utility.trace('ABS : l_abs_org_tab(i).abs_startdate '||l_abs_org_tab(i).abs_startdate);
76 hr_utility.trace('ABS : l_abs_org_tab(i).abs_enddate '||l_abs_org_tab(i).abs_enddate);
77 hr_utility.trace('ABS : l_abs_org_tab(i).rec_start_date '||l_abs_org_tab(i).rec_start_date);
78 hr_utility.trace('ABS : l_abs_org_tab(i).rec_end_date '||l_abs_org_tab(i).rec_end_date);
79 hr_utility.trace('ABS : l_abs_org_tab(i).confirmed_flag '||l_abs_org_tab(i).confirmed_flag);
80 END IF;
81 IF TRUNC(fnd_date.canonical_to_date(l_abs_org_tab(i).abs_startdate)) > TRUNC(p_end_date)
82 OR TRUNC(fnd_date.canonical_to_date(l_abs_org_tab(i).abs_startdate)) < TRUNC(p_start_date)
83 THEN
84 IF g_debug
85 THEN
86 hr_utility.trace('ABS : Spans outside the timecard ');
87 END IF;
88 EXIT CONTINUE_TO_NEXT;
89 END IF;
90
91
92 p_abs_tab(l_ind).abs_type_id :=l_abs_org_tab(i).absence_type_id ;
93 p_abs_tab(l_ind).abs_date :=TRUNC(fnd_date.canonical_to_date(l_abs_org_tab(i).abs_startdate)) ;
94 p_abs_tab(l_ind).element_type_id := l_abs_org_tab(i).element_type_ID ;
95 p_abs_tab(l_ind).abs_attendance_id := l_abs_org_tab(i).absence_attendance_id ;
96 p_abs_tab(l_ind).prg_appl_id := l_abs_org_tab(i).program_application_id ;
97 p_abs_tab(l_ind).rec_start_date := l_abs_org_tab(i).rec_start_date ;
98 p_abs_tab(l_ind).rec_end_date := l_abs_org_tab(i).rec_end_date ;
99
100 -- Bug 8995913
101 -- Added the following to pick up confirmed or not flag.
102 p_abs_tab(l_ind).confirmed_flag := l_abs_org_tab(i).confirmed_flag ;
103
104 IF p_abs_tab(l_ind).confirmed_flag = 'N'
105 THEN
106 g_pending_conf := TRUE;
107 END IF;
108
109
110 IF l_abs_org_tab(i).days_or_hours = 'D'
111 THEN
112 p_abs_tab(l_ind).uom := 'D' ;
113 p_abs_tab(l_ind).duration := 1 ;
114 ELSE
115 p_abs_tab(l_ind).uom := 'H';
116 p_abs_tab(l_ind).duration := NVL(l_abs_org_tab(i).rec_duration,
117 (fnd_date.canonical_to_date(l_abs_org_tab(i).abs_enddate) -
118 fnd_date.canonical_to_date(l_abs_org_tab(i).abs_startdate)
119 )*24);
120 END IF;
121
122 IF l_abs_org_tab(i).days_or_hours = 'H'
123 THEN
124 p_abs_tab(l_ind).abs_start := fnd_date.canonical_to_date(l_abs_org_tab(i).abs_startdate) ;
125 p_abs_tab(l_ind).abs_end := fnd_date.canonical_to_date(l_abs_org_tab(i).abs_enddate) ;
126 END IF;
127
128
129
130 IF l_abs_org_tab(i).transactionid IS NOT NULL
131 THEN
132 IF g_debug
133 THEN
134 hr_utility.trace('ABS : This is an SSHR transaction ');
135 END IF;
136 p_abs_tab(l_ind).transaction_id := l_abs_org_tab(i).transactionid;
137 p_abs_tab(l_ind).modetype := l_abs_org_tab(i).modetype;
138 IF p_abs_tab(l_ind).modetype = 'DeleteMode'
139 THEN
140 g_pending_delete := TRUE;
141 END IF;
142 g_pending_appr := TRUE;
143 END IF;
144 l_ind := l_ind + 1;
145 EXIT CONTINUE_TO_NEXT;
146 END LOOP CONTINUE_TO_NEXT;
147 END LOOP; -- FOR i in l_abs_org_tab.FIRST..l_abs_org_tab.LAST
148 END IF;
149
150 IF g_pref_table.COUNT > 0
151 THEN
152 FOR i IN g_pref_table.FIRST..g_pref_table.LAST
153 LOOP
154 IF g_pref_table(i).preference_code = 'TS_ABS_PREFERENCES'
155 THEN
156 IF g_pending_appr = TRUE
157 THEN
158 IF g_pending_delete = TRUE
159 THEN
160 hxc_timecard_message_helper.addErrorToCollection
161 ( g_messages
162 ,'HXC_ABS_PEND_APPR_DELETE'
163 ,hxc_timecard.c_error
164 ,NULL
165 ,NULL
166 ,hxc_timecard.c_hxc
167 ,NULL
168 ,NULL
169 ,NULL
170 ,NULL
171 );
172 hr_utility.trace('ABS : This is a DELETE pending in SSHR ');
173 g_message_string := 'HXC_ABS_PEND_APPR_DELETE';
174 EXIT;
175 END IF; -- Pending Delete = TRUE
176
177 IF g_pref_table(i).attribute3 = 'ERROR'
178 THEN
179 hr_utility.trace('ABS : This is pending APPROVAL in SSHR -- ERROR');
180 hxc_timecard_message_helper.addErrorToCollection
181 (g_messages
182 ,'HXC_ABS_PEND_APPR_ERROR'
183 ,hxc_timecard.c_error
184 ,NULL
185 ,NULL
186 ,hxc_timecard.c_hxc
187 ,NULL
188 ,NULL
189 ,NULL
190 ,NULL
191 );
192 hr_utility.trace('ABS : A pending approval error added ');
193 g_message_string := 'HXC_ABS_PEND_APPR_ERROR';
194 END IF; -- Attribute3 = ERROR
195 EXIT;
196
197 END IF; -- PENDING APPR = TRUE
198
199 -- Bug 8995913
200 -- Added the below construct to block the timecard if
201 -- absences pending confirmation are not allowed.
202
203 IF g_pending_conf = TRUE
204 THEN
205 IF g_pref_table(i).attribute7 = 'ERROR'
206 THEN
207 hr_utility.trace('ABS : This is pending Confirmation in SSHR -- ERROR');
208 hxc_timecard_message_helper.addErrorToCollection
209 (g_messages
210 ,'HXC_ABS_PEND_CONF_ERROR'
211 ,hxc_timecard.c_error
212 ,NULL
213 ,NULL
214 ,hxc_timecard.c_hxc
215 ,NULL
216 ,NULL
217 ,NULL
218 ,NULL
219 );
220 hr_utility.trace('ABS : A pending Confirmation error added ');
221 g_message_string := 'HXC_ABS_PEND_CONF_ERROR';
222 END IF;
223 END IF; -- PENDING CONF = TRUE
224 EXIT;
225 END IF; -- PREF CODE = TS_ABS_PREFERENCES
226 END LOOP;
227 END IF;
228
229 -- Bug 8855103
230 -- Added the below construct to clear off invalid transactions.
231 IF l_abs_inv.COUNT > 0
232 THEN
233 l_ind := l_abs_inv.FIRST;
234 LOOP
235 BEGIN
236 hr_person_absence_swi.delete_absences_in_tt(l_abs_inv(l_ind).transactionid);
237 EXCEPTION
238 WHEN NO_DATA_FOUND THEN
239 NULL;
240 END;
241 l_ind := l_abs_inv.NEXT(l_ind);
242 EXIT WHEN NOT l_abs_inv.EXISTS(l_ind);
243 END LOOP;
244 END IF;
245
246
247 END retrieve_absences;
248
249
250 FUNCTION get_alias_for_detail ( p_bb_id IN NUMBER,
251 p_bb_ovn IN NUMBER,
252 p_element_type_id IN NUMBER,
253 p_attribute_id IN NUMBER)
254 RETURN HXC_ATTRIBUTE_TYPE
255 IS
256
257 l_attribute_type HXC_ATTRIBUTE_TYPE;
258
259 BEGIN
260
261 l_attribute_type := HXC_ATTRIBUTE_TYPE(NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
262 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
263 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
264 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
265
266
267 l_attribute_type.time_attribute_id := p_attribute_id;
268 l_attribute_type.attribute_category := 'ALTERNATE NAME IDENTIFIERS';
269 l_attribute_type.attribute1 := g_alias_tab(p_element_type_id);
270 l_attribute_type.bld_blk_info_type_id := g_alias_bbit;
271 l_attribute_type.bld_blk_info_type := 'ALTERNATE NAME IDENTIFIERS';
272 l_attribute_type.BUILDING_BLOCK_ID := p_bb_id;
273 l_attribute_type.BUILDING_BLOCK_OVN := p_bb_ovn;
274 l_attribute_type.OBJECT_VERSION_NUMBER := 1;
275
276 RETURN l_attribute_type;
277
278
279 END get_alias_for_detail;
280
281
282
283
284 PROCEDURE gen_alt_ids ( p_person_id IN NUMBER,
285 p_start_time IN DATE,
286 p_stop_time IN DATE,
287 p_mode IN VARCHAR2)
288 IS
289
290 l_ind BINARY_INTEGER;
291 l_alias_list VARCHAR2(500);
292
293 l_alias_sql VARCHAR2(5000) :=
294 'SELECT hav.attribute1,hav.attribute2
295 FROM hxc_alias_definitions had,
296 hxc_alias_values hav
297 WHERE had.alias_definition_id IN ALIASLIST
298 AND hav.alias_definition_id = had.alias_definition_id
299 AND hav.attribute_category = ''PAYROLL_ELEMENTS''
300 AND hav.attribute2 IS NOT NULL
301 ORDER BY hav.alias_definition_id,hav.alias_value_id DESC' ;
302
303 l_ref_cursor SYS_REFCURSOR;
304 l_element_tab NUMTABLE;
305 l_id_tab NUMTABLE;
306
307 BEGIN
308 IF g_debug
309 THEN
310 hr_utility.trace('Getting alt name identifier s');
311 END IF;
312
313 hxc_preference_evaluation.resource_preferences(p_person_id,
314 p_start_time,
315 p_stop_time,
316 g_pref_table);
317
318 IF g_pref_table.COUNT > 0
319 THEN
320 l_ind := g_pref_table.FIRST;
321 LOOP
322 IF p_mode = 'SS'
323 THEN
324 IF g_pref_table(l_ind).preference_code = 'TC_W_TCRD_ALIASES'
325 THEN
326 l_alias_list := '('||g_pref_table(l_ind).attribute1||
327 ','||g_pref_table(l_ind).attribute2||
328 ','||g_pref_table(l_ind).attribute3||
329 ','||g_pref_table(l_ind).attribute4||
330 ','||g_pref_table(l_ind).attribute5||
331 ','||g_pref_table(l_ind).attribute6||
332 ','||g_pref_table(l_ind).attribute7||
333 ','||g_pref_table(l_ind).attribute8||
334 ','||g_pref_table(l_ind).attribute8||
335 ','||g_pref_table(l_ind).attribute10;
336 l_alias_list := RTRIM(l_alias_list,',');
337 -- Bug 8880941
338 -- Added the following statement to replace multiple commas
339 -- with a single one.
340 l_alias_list := REGEXP_REPLACE(l_alias_list,'(,){2,}',',');
341 l_alias_list := l_alias_list||')';
342 EXIT;
343 END IF;
344 ELSIF p_mode = 'TK'
345 THEN
346 IF g_pref_table(l_ind).preference_code = 'TK_TCARD_ATTRIBUTES_DEFINITION'
347 THEN
348 l_alias_list := '('||g_pref_table(l_ind).attribute1||
349 ','||g_pref_table(l_ind).attribute2||
350 ','||g_pref_table(l_ind).attribute3||
351 ','||g_pref_table(l_ind).attribute4||
352 ','||g_pref_table(l_ind).attribute5||
353 ','||g_pref_table(l_ind).attribute6||
354 ','||g_pref_table(l_ind).attribute7||
355 ','||g_pref_table(l_ind).attribute8||
356 ','||g_pref_table(l_ind).attribute8||
357 ','||g_pref_table(l_ind).attribute10;
358 l_alias_list := RTRIM(l_alias_list,',');
359 -- Bug 8880941
360 -- Added the following statement to replace multiple commas
361 -- with a single one.
362 l_alias_list := REGEXP_REPLACE(l_alias_list,'(,){2,}',',');
363 l_alias_list := l_alias_list||')';
364 EXIT;
365 END IF;
366 END IF;
367 l_ind := g_pref_table.NEXT(l_ind);
368 EXIT WHEN NOT g_pref_table.EXISTS(l_ind);
369 END LOOP;
370
371 IF l_alias_list <> '()'
372 THEN
373 l_alias_sql := REPLACE(l_alias_sql,'ALIASLIST',l_alias_list);
374 IF g_debug
375 THEN
376 hr_utility.trace(l_alias_sql);
377 END IF;
378 OPEN l_ref_cursor FOR l_alias_sql;
379 FETCH l_ref_cursor BULK COLLECT INTO l_element_tab,
380 l_id_tab;
381 CLOSE l_ref_cursor;
382
383 IF l_element_tab.COUNT > 0
384 THEN
385 FOR i IN l_element_tab.FIRST..l_element_tab.LAST
386 LOOP
387 g_alias_tab(l_element_tab(i)) := l_id_tab(i);
388 IF g_debug
389 THEN
390 hr_utility.trace('Element Id is'||g_alias_tab(l_element_tab(i)));
391 hr_utility.trace('Alt Id is '||l_id_tab(i));
392 END IF;
393 END LOOP;
394 END IF;
395
396 END IF;
397 END IF;
398
399 SELECT bld_blk_info_type_id
400 INTO g_dummy_bbit
401 FROM hxc_bld_blk_info_types
402 WHERE bld_blk_info_type = 'Dummy Element Context';
403
404 SELECT bld_blk_info_type_id
405 INTO g_alias_bbit
406 FROM hxc_bld_blk_info_types
407 WHERE bld_blk_info_type = 'ALTERNATE NAME IDENTIFIERS';
408
409 -- Bug 8911152
410 -- Added this query to pull up layout attribute's bbit.
411 SELECT bld_blk_info_type_id
412 INTO g_layout_bbit
413 FROM hxc_bld_blk_info_types
414 WHERE bld_blk_info_type = 'LAYOUT';
415
416
417 END gen_alt_ids;
418
419
420 -- overloaded proc for TK
421
422 PROCEDURE create_tc_with_abs ( p_person_id IN NUMBER,
423 p_start_date IN DATE,
424 p_end_date IN DATE,
425 p_approval_style_id IN NUMBER,
426 p_lock_rowid IN VARCHAR2,
427 p_source IN VARCHAR2,
428 p_timekeeper_id IN NUMBER,
429 p_iteration_count IN NUMBER,
430 p_block_array IN OUT NOCOPY HXC_BLOCK_TABLE_TYPE,
431 p_attribute_array IN OUT NOCOPY HXC_ATTRIBUTE_TABLE_TYPE )
432 IS
433
434 l_abs_tab abs_tab;
435 l_block_ind NUMBER;
436
437 l_current_date DATE;
438 l_day_id NUMBER;
439 l_det_id NUMBER;
440 l_att_id NUMBER;
441 l_att_ind NUMBER;
442 l_messages HXC_MESSAGE_TABLE_TYPE;
443 l_UOM VARCHAR2(5);
444
445
446 l_tbb_id_reference_table hxc_alias_utility.t_tbb_id_reference;
447
448 BEGIN
449
450 g_messages := HXC_MESSAGE_TABLE_TYPE();
451
452 --p_end_date:=trunc(p_end_date); --
453
454
455 retrieve_absences( p_person_id,
456 p_start_date,
457 p_end_date,
458 l_abs_tab );
459
460
461
462 IF l_abs_tab.COUNT > 0
463 THEN
464
465 p_block_array := HXC_BLOCK_TABLE_TYPE();
466 p_block_array.EXTEND(1);
467 l_block_ind := p_block_array.first;
468 p_block_array(l_block_ind):= HXC_BLOCK_TYPE(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
469 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
470 NULL,NULL,NULL,NULL,NULL,NULL,NULL);
471 --p_block_array(l_block_ind).TIME_BUILDING_BLOCK_ID := -2; original code
472 p_block_array(l_block_ind).TIME_BUILDING_BLOCK_ID := (-3 - (p_iteration_count*30000)); -- SVG added
473 p_block_array(l_block_ind).TYPE := 'RANGE';
474 p_block_array(l_block_ind).MEASURE := NULL;
475 p_block_array(l_block_ind).UNIT_OF_MEASURE := 'HOURS';
476 p_block_array(l_block_ind).START_TIME := FND_DATE.DATE_TO_CANONICAL(p_start_date);
477 p_block_array(l_block_ind).STOP_TIME := TO_CHAR(p_end_date,'YYYY/MM/DD ')||'23:59:59';
478 p_block_array(l_block_ind).PARENT_BUILDING_BLOCK_ID := NULL;
479 p_block_array(l_block_ind).PARENT_IS_NEW := NULL;
480 p_block_array(l_block_ind).SCOPE := 'TIMECARD';
481 p_block_array(l_block_ind).OBJECT_VERSION_NUMBER := 1;
482 p_block_array(l_block_ind).APPROVAL_STATUS := NULL;
483 p_block_array(l_block_ind).RESOURCE_ID := p_person_id;
484 p_block_array(l_block_ind).RESOURCE_TYPE := 'PERSON';
485 p_block_array(l_block_ind).APPROVAL_STYLE_ID := NULL;
486 p_block_array(l_block_ind).DATE_FROM := FND_DATE.DATE_TO_CANONICAL(SYSDATE);
487 p_block_array(l_block_ind).DATE_TO := FND_DATE.DATE_TO_CANONICAL(hr_general.end_of_time);
488 p_block_array(l_block_ind).COMMENT_TEXT := NULL;
489 p_block_array(l_block_ind).PARENT_BUILDING_BLOCK_OVN := NULL;
490 p_block_array(l_block_ind).NEW := 'Y';
491 p_block_array(l_block_ind).CHANGED := 'Y';
492 p_block_array(l_block_ind).PROCESS := NULL;
493 p_block_array(l_block_ind).APPLICATION_SET_ID := NULL;
494 p_block_array(l_block_ind).TRANSLATION_DISPLAY_KEY := NULL;
495
496 l_block_ind := l_block_ind + 1;
497 -- l_day_id := -2; original
498 l_day_id := (-4 - (p_iteration_count*30000)); -- SVG changed
499 l_current_date := TRUNC(p_start_date);
500 WHILE l_current_date <= trunc(p_end_date)
501 LOOP
502 p_block_array.EXTEND(1);
503 l_day_id := l_day_id -1;
504 g_day_tab(TO_CHAR(l_current_date,'YYYYMMDD')) := l_day_id;
505 p_block_array(l_block_ind) := get_day_block(l_current_date,
506 p_person_id,
507 p_approval_style_id,
508 -- -2, -- Original
509 (-3 - (p_iteration_count*30000)), -- svg changed
510 l_day_id );
511 l_current_date := l_current_date + 1 ;
512 l_block_ind := l_block_ind + 1;
513 END LOOP;
514 l_det_id := l_day_id;
515 l_att_id := -1;
516 l_att_ind := 1;
517 p_attribute_array := HXC_ATTRIBUTE_TABLE_TYPE();
518 l_att_ind := -1;
519 FOR i IN l_abs_tab.FIRST..l_abs_tab.LAST
520 LOOP
521 <<CONTINUE_TO_NEXT>>
522 LOOP
523 IF NOT l_abs_tab.EXISTS(i)
524 THEN
525 EXIT CONTINUE_TO_NEXT;
526 END IF;
527 l_det_id := l_det_id -1;
528 l_att_id := l_att_id -1;
529 p_attribute_array.EXTEND(1);
530 IF l_att_ind = -1
531 THEN
532 l_att_ind := p_attribute_array.FIRST;
533 END IF;
534 p_block_array.EXTEND(1);
535 p_block_array(l_block_ind) := get_detail_block(l_abs_tab(i).abs_start,
536 l_abs_tab(i).abs_end,
537 l_abs_tab(i).duration,
538 p_person_id,
539 p_approval_style_id,
540 g_day_tab(TO_CHAR(l_abs_tab(i).abs_date,'YYYYMMDD')),
541 l_det_id,
542 1,
543 trunc(hr_general.end_of_time));
544 p_attribute_array(l_att_ind) := get_attribute_for_detail(l_det_id,
545 1,
546 l_abs_tab(i).element_type_id,
547 l_abs_tab(i).abs_attendance_id,
548 l_att_id );
549
550 IF g_alias_tab.EXISTS(l_abs_tab(i).element_type_id)
551 THEN
552 l_att_ind := l_att_ind + 1;
553 p_attribute_array.EXTEND(1);
554 l_att_id := l_att_id -1 ;
555 p_attribute_array(l_att_ind) := get_alias_for_detail(l_det_id,
556 1,
557 l_abs_tab(i).element_type_id,
558 l_att_id );
559 END IF;
560
561
562 IF l_abs_tab(i).abs_start IS NULL
563 THEN
564 l_UOM := 'D';
565 ELSE
566 l_uom := 'H';
567 END IF;
568 /*
569 record_carried_over_absences(l_det_id,
570 1,
571 l_abs_tab(i).abs_type_id,
572 l_abs_tab(i).abs_attendance_id,
573 l_abs_tab(i).element_type_id,
574 NVL(l_abs_tab(i).abs_start,l_abs_tab(i).abs_date),
575 NVL(l_abs_tab(i).abs_end,l_abs_tab(i).abs_date),
576 l_uom,
577 l_abs_tab(i).duration,
578 'PREP',
579 p_person_id,
580 p_start_date,
581 p_end_date,
582 p_lock_rowid);
583 */
584
585 IF l_abs_tab(i).transaction_id IS NULL
586 THEN
587 -- Bug 8995913
588 -- Added confirmed flag in parameters.
589 record_carried_over_absences(l_det_id,
590 1,
591 l_abs_tab(i).abs_type_id,
592 l_abs_tab(i).abs_attendance_id,
593 l_abs_tab(i).element_type_id,
594 NVL(l_abs_tab(i).abs_start,l_abs_tab(i).abs_date),
595 NVL(l_abs_tab(i).abs_end,l_abs_tab(i).abs_date),
596 l_uom,
597 l_abs_tab(i).duration,
598 'PREP',
599 p_person_id,
600 p_start_date,
601 trunc(p_end_date),
602 p_lock_rowid,
603 NULL,
604 NULL,
605 l_abs_tab(i).confirmed_flag);
606 ELSE
607 -- Bug 8941273
608 -- Added abs_attendance Id also.
609 -- Bug 8995913
610 -- Added confirmed flag in parameters.
611 record_carried_over_absences(l_det_id,
612 1,
613 l_abs_tab(i).abs_type_id,
614 l_abs_tab(i).abs_attendance_id,
615 l_abs_tab(i).element_type_id,
616 NVL(l_abs_tab(i).abs_start,l_abs_tab(i).abs_date),
617 NVL(l_abs_tab(i).abs_end,l_abs_tab(i).abs_date),
618 l_uom,
619 l_abs_tab(i).duration,
620 'PREP-SS',
621 p_person_id,
622 p_start_date,
623 trunc(p_end_date),
624 p_lock_rowid,
625 l_abs_tab(i).transaction_id,
626 l_abs_tab(i).modetype,
627 l_abs_tab(i).confirmed_flag );
628 END IF;
629
630
631
632 l_block_ind := l_block_ind + 1;
633 l_att_ind := l_att_ind + 1;
634 EXIT CONTINUE_TO_NEXT;
635 END LOOP CONTINUE_TO_NEXT;
636 END LOOP;
637
638
639 if p_source = 'TK' then -- added SVG
640
641
642 HXC_ALIAS_TRANSLATOR.do_retrieval_translation( p_attributes => p_attribute_array
643 ,p_blocks => p_block_array
644 ,p_start_time => p_start_date
645 ,p_stop_time => p_end_date
646 ,p_resource_id => p_timekeeper_id
647 ,p_processing_mode => hxc_alias_utility.c_tk_processing
648 ,p_messages => l_messages );
649
650
651 else
652
653 HXC_ALIAS_TRANSLATOR.do_retrieval_translation( p_attributes => p_attribute_array
654 ,p_blocks => p_block_array
655 ,p_start_time => p_start_date
656 ,p_stop_time => p_end_date
657 ,p_resource_id => p_person_id
658 ,p_messages => l_messages );
659 end if;
660
661 END IF;
662
663
664 END create_tc_with_abs; -- Overloaded proc create_tc_with_abs
665
666
667
668 PROCEDURE create_tc_with_abs ( p_person_id IN NUMBER,
669 p_start_date IN DATE,
670 p_end_date IN DATE,
671 p_approval_style_id IN NUMBER,
672 p_lock_rowid IN VARCHAR2,
673 p_block_array IN OUT NOCOPY HXC_BLOCK_TABLE_TYPE,
674 p_attribute_array IN OUT NOCOPY HXC_ATTRIBUTE_TABLE_TYPE )
675 IS
676
677 l_abs_tab ABS_TAB;
678 l_block_ind NUMBER;
679
680 l_current_date DATE;
681 l_day_id NUMBER;
682 l_det_id NUMBER;
683 l_att_id NUMBER;
684 l_att_ind NUMBER;
685 l_messages HXC_MESSAGE_TABLE_TYPE;
686 l_UOM VARCHAR2(5);
687
688 l_tbb_id_reference_table hxc_alias_utility.t_tbb_id_reference;
689
690 i BINARY_INTEGER;
691
692 BEGIN
693
694 g_messages := HXC_MESSAGE_TABLE_TYPE();
695
696 retrieve_absences( p_person_id,
697 p_start_date,
698 p_end_date,
699 l_abs_tab );
700 IF g_pending_appr
701 AND g_messages.COUNT >0
702 THEN
703 RETURN;
704 END IF;
705
706
707
708 IF l_abs_tab.COUNT > 0
709 THEN
710
711 p_block_array := HXC_BLOCK_TABLE_TYPE();
712 p_block_array.EXTEND(1);
713 l_block_ind := p_block_array.first;
714 p_block_array(l_block_ind):= HXC_BLOCK_TYPE(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
715 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
716 NULL,NULL,NULL,NULL,NULL,NULL,NULL);
717 p_block_array(l_block_ind).TIME_BUILDING_BLOCK_ID := -2;
718 p_block_array(l_block_ind).TYPE := 'RANGE';
719 p_block_array(l_block_ind).MEASURE := NULL;
720 p_block_array(l_block_ind).UNIT_OF_MEASURE := 'HOURS';
721 p_block_array(l_block_ind).START_TIME := FND_DATE.DATE_TO_CANONICAL(p_start_date);
722 p_block_array(l_block_ind).STOP_TIME := TO_CHAR(p_end_date,'YYYY/MM/DD ')||'23:59:59';
723 p_block_array(l_block_ind).PARENT_BUILDING_BLOCK_ID := NULL;
724 p_block_array(l_block_ind).PARENT_IS_NEW := NULL;
725 p_block_array(l_block_ind).SCOPE := 'TIMECARD';
726 p_block_array(l_block_ind).OBJECT_VERSION_NUMBER := 1;
727 p_block_array(l_block_ind).APPROVAL_STATUS := NULL;
728 p_block_array(l_block_ind).RESOURCE_ID := p_person_id;
729 p_block_array(l_block_ind).RESOURCE_TYPE := 'PERSON';
730 p_block_array(l_block_ind).APPROVAL_STYLE_ID := NULL;
731 p_block_array(l_block_ind).DATE_FROM := FND_DATE.DATE_TO_CANONICAL(SYSDATE);
732 p_block_array(l_block_ind).DATE_TO := FND_DATE.DATE_TO_CANONICAL(hr_general.end_of_time);
733 p_block_array(l_block_ind).COMMENT_TEXT := NULL;
734 p_block_array(l_block_ind).PARENT_BUILDING_BLOCK_OVN := NULL;
735 p_block_array(l_block_ind).NEW := 'Y';
736 p_block_array(l_block_ind).CHANGED := 'Y';
737 p_block_array(l_block_ind).PROCESS := NULL;
738 p_block_array(l_block_ind).APPLICATION_SET_ID := NULL;
739 p_block_array(l_block_ind).TRANSLATION_DISPLAY_KEY := NULL;
740
741
742 -- Bug 8911152
743 -- Adding call to create layout attribute.
744 p_attribute_array := HXC_ATTRIBUTE_TABLE_TYPE();
745 p_attribute_array.EXTEND(1);
746 l_att_ind := p_attribute_array.FIRST;
747 l_att_id := -1;
748
749 p_attribute_array(l_att_ind) := get_layout_attribute(-2,
750 1,
751 l_att_id);
752
753 IF g_debug
754 THEN
755 hr_utility.trace('ABS: Created timecard and layout attribute ');
756 END IF;
757
758 l_block_ind := l_block_ind + 1;
759 l_day_id := -2;
760 l_current_date := TRUNC(p_start_date);
761
762 WHILE l_current_date <= trunc(p_end_date)
763 LOOP
764 p_block_array.EXTEND(1);
765 l_day_id := l_day_id -1;
766 g_day_tab(TO_CHAR(l_current_date,'YYYYMMDD')) := l_day_id;
767 p_block_array(l_block_ind) := get_day_block(l_current_date,
768 p_person_id,
769 p_approval_style_id,
770 -2,
771 l_day_id );
772 l_current_date := l_current_date + 1 ;
773 l_block_ind := l_block_ind + 1;
774 END LOOP;
775 l_det_id := l_day_id;
776 -- Bug 8911152
777 -- No longer need this initialization cos we are creating
778 -- the layout attribute above.
779 --l_att_id := -1;
780 --l_att_ind := 1;
781 --p_attribute_array := HXC_ATTRIBUTE_TABLE_TYPE();
782 l_att_ind := l_att_ind +1;
783 FOR i IN l_abs_tab.FIRST..l_abs_tab.LAST
784 LOOP
785 << CONTINUE_TO_NEXT>>
786 LOOP
787 hr_utility.trace('ABS : i'||i);
788 IF NOT l_abs_tab.EXISTS(i)
789 THEN
790 EXIT CONTINUE_TO_NEXT;
791 END IF;
792 l_det_id := l_det_id -1;
793 l_att_id := l_att_id -1;
794 p_attribute_array.EXTEND(1);
795 IF l_att_ind = -1
796 THEN
797 l_att_ind := p_attribute_array.FIRST;
798 END IF;
799 p_block_array.EXTEND(1);
800 IF g_debug
801 THEN
802 hr_utility.trace('ABS: l_abs_tab(i).abs_start'||l_abs_tab(i).abs_start);
803 hr_utility.trace('ABS: l_abs_tab(i).abs_end'||l_abs_tab(i).abs_end);
804 hr_utility.trace('ABS: l_abs_tab(i).duration'||l_abs_tab(i).duration);
805 hr_utility.trace('ABS: g_day_tab(TO_CHAR(l_abs_tab(i).abs_date,YYYYMMDD))'||
806 g_day_tab(TO_CHAR(l_abs_tab(i).abs_date,'YYYYMMDD')));
807 hr_utility.trace('ABS: l_abs_tab(i).abs_start'||l_abs_tab(i).abs_start);
808 hr_utility.trace('ABS: l_abs_tab(i).abs_start'||l_abs_tab(i).abs_start);
809 END IF;
810 p_block_array(l_block_ind) := get_detail_block(l_abs_tab(i).abs_start,
811 l_abs_tab(i).abs_end,
812 l_abs_tab(i).duration,
813 p_person_id,
814 p_approval_style_id,
815 g_day_tab(TO_CHAR(l_abs_tab(i).abs_date,'YYYYMMDD')),
816 l_det_id,
817 1,
818 TRUNC(hr_general.end_of_time));
819 p_attribute_array(l_att_ind) := get_attribute_for_detail(l_det_id,
820 1,
821 l_abs_tab(i).element_type_id,
822 l_abs_tab(i).abs_attendance_id,
823 l_att_id );
824
825 IF g_alias_tab.EXISTS(l_abs_tab(i).element_type_id)
826 THEN
827 l_att_ind := l_att_ind + 1;
828 p_attribute_array.EXTEND(1);
829 l_att_id := l_att_id -1 ;
830 p_attribute_array(l_att_ind) := get_alias_for_detail(l_det_id,
831 1,
832 l_abs_tab(i).element_type_id,
833 l_att_id );
834 END IF;
835
836 IF l_abs_tab(i).abs_start IS NULL
837 THEN
838 l_UOM := 'D';
839 ELSE
840 l_uom := 'H';
841 END IF;
842 IF l_abs_tab(i).transaction_id IS NULL
843 THEN
844 -- Bug 8995913
845 -- Added confirmed flag in parameters.
846 record_carried_over_absences(l_det_id,
847 1,
848 l_abs_tab(i).abs_type_id,
849 l_abs_tab(i).abs_attendance_id,
850 l_abs_tab(i).element_type_id,
851 NVL(l_abs_tab(i).abs_start,l_abs_tab(i).abs_date),
852 NVL(l_abs_tab(i).abs_end,l_abs_tab(i).abs_date),
853 l_uom,
854 l_abs_tab(i).duration,
855 'PREP',
856 p_person_id,
857 p_start_date,
858 p_end_date,
859 p_lock_rowid,
860 NULL,
861 NULL,
862 l_abs_tab(i).confirmed_flag);
863 ELSE
864 -- Bug 8941273
865 -- Added abs_attendance Id also.
866 -- Bug 8995913
867 -- Added confirmed flag in parameters.
868 record_carried_over_absences(l_det_id,
869 1,
870 l_abs_tab(i).abs_type_id,
871 l_abs_tab(i).abs_attendance_id,
872 l_abs_tab(i).element_type_id,
873 NVL(l_abs_tab(i).abs_start,l_abs_tab(i).abs_date),
874 NVL(l_abs_tab(i).abs_end,l_abs_tab(i).abs_date),
875 l_uom,
876 l_abs_tab(i).duration,
877 'PREP-SS',
878 p_person_id,
879 p_start_date,
880 p_end_date,
881 p_lock_rowid,
882 l_abs_tab(i).transaction_id,
883 l_abs_tab(i).modetype ,
884 l_abs_tab(i).confirmed_flag);
885 END IF;
886 l_block_ind := l_block_ind + 1;
887 l_att_ind := l_att_ind + 1;
888 EXIT CONTINUE_TO_NEXT;
889 END LOOP CONTINUE_TO_NEXT;
890 END LOOP;
891
892 HXC_ALIAS_TRANSLATOR.do_retrieval_translation( p_attributes => p_attribute_array
893 ,p_blocks => p_block_array
894 ,p_start_time => p_start_date
895 ,p_stop_time => p_end_date
896 ,p_resource_id => p_person_id
897 ,p_messages => l_messages );
898
899 IF g_debug
900 THEN
901 i := p_block_array.FIRST;
902 LOOP
903 hr_utility.trace('p_block_array(i).time_building_block_id '||p_block_array(i).time_building_block_id);
904 hr_utility.trace('p_block_array(i).object_version_number '||p_block_array(i).object_version_number);
905 hr_utility.trace('p_block_array(i).scope '||p_block_array(i).scope);
906 hr_utility.trace('p_block_array(i).start_time '||p_block_array(i).start_time);
907 hr_utility.trace('p_block_array(i).stop_time '||p_block_array(i).stop_time);
908 hr_utility.trace('p_block_array(i).measure '||p_block_array(i).measure);
909 hr_utility.trace('p_block_array(i).date_to '||p_block_array(i).date_to);
910 i:= p_block_array.NEXT(i);
911 EXIT WHEN NOT p_block_array.EXISTS(i);
912 END LOOP;
913
914 i:= p_attribute_array.FIRST;
915 LOOP
916 hr_utility.trace('p_attribute_array(i).building_block_id '||p_attribute_array(i).building_block_id);
917 hr_utility.trace('p_attribute_array(i).building_block_ovn '||p_attribute_array(i).building_block_ovn);
918 hr_utility.trace('p_attribute_array(i).attribute_category '||p_attribute_array(i).attribute_category);
919 hr_utility.trace('p_attribute_array(i).bld_blk_info_type '||p_attribute_array(i).bld_blk_info_type);
920 hr_utility.trace('p_attribute_array(i).attribute1 '||p_attribute_array(i).attribute1);
921 hr_utility.trace('p_attribute_array(i).attribute1 '||p_attribute_array(i).attribute2);
922 hr_utility.trace('p_attribute_array(i).attribute1 '||p_attribute_array(i).attribute3);
923 i:= p_attribute_array.NEXT(i);
924 EXIT WHEN NOT p_block_array.EXISTS(i);
925 END LOOP;
926
927 END IF;
928
929 END IF;
930
931
932 END create_tc_with_abs ;
933
934
935 FUNCTION get_day_block ( p_date IN DATE,
936 p_person_id IN NUMBER,
937 p_approval_style_id IN NUMBER,
938 p_timecard_id IN NUMBER,
939 p_bb_id IN NUMBER)
940 RETURN hxc_block_type
941 IS
942
943 l_block_type HXC_BLOCK_TYPE;
944
945 BEGIN
946
947 l_block_type := HXC_BLOCK_TYPE(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
948 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
949 NULL,NULL,NULL,NULL,NULL,NULL,NULL);
950
951 l_block_type.TIME_BUILDING_BLOCK_ID := p_bb_id;
952 l_block_type.TYPE := 'RANGE';
953 l_block_type.UNIT_OF_MEASURE := 'HOURS';
954 l_block_type.START_TIME := FND_DATE.DATE_TO_CANONICAL(p_date);
955 l_block_type.STOP_TIME := TO_CHAR(p_date,'YYYY/MM/DD ')||'23:59:59';
956 l_block_type.PARENT_BUILDING_BLOCK_ID := p_timecard_id;
957 l_block_type.SCOPE := 'DAY';
958 l_block_type.OBJECT_VERSION_NUMBER := 1;
959 l_block_type.RESOURCE_ID := p_person_id;
960 l_block_type.RESOURCE_TYPE := 'PERSON';
961 l_block_type.APPROVAL_STYLE_ID := p_approval_style_id;
962 l_block_type.DATE_FROM := FND_DATE.DATE_TO_CANONICAL(SYSDATE);
963 l_block_type.DATE_TO := FND_DATE.DATE_TO_CANONICAL(hr_general.end_of_time);
964 l_block_type.PARENT_BUILDING_BLOCK_OVN := 1;
965 l_block_type.NEW := 'Y';
966 l_block_type.CHANGED := 'Y';
967 l_block_type.PROCESS := NULL;
968 l_block_type.APPLICATION_SET_ID := NULL;
969 l_block_type.TRANSLATION_DISPLAY_KEY := NULL;
970 l_block_type.MEASURE := NULL;
971 l_block_type.PARENT_IS_NEW := NULL;
972 l_block_type.APPROVAL_STATUS := NULL;
973 l_block_type.COMMENT_TEXT := NULL;
974
975 RETURN l_block_type;
976
977 END get_day_block;
978
979 FUNCTION get_detail_block ( p_start_time IN DATE,
980 p_stop_time IN DATE,
981 p_measure IN NUMBER,
982 p_person_id IN NUMBER,
983 p_approval_style_id IN NUMBER,
984 p_day_id IN NUMBER,
985 p_bb_id IN NUMBER,
986 p_bb_ovn IN NUMBER,
987 p_date_to IN DATE)
988 RETURN hxc_block_type
989 IS
990 l_block_type HXC_BLOCK_TYPE;
991
992 BEGIN
993
994
995
996 l_block_type :=HXC_BLOCK_TYPE(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
997 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
998 NULL,NULL,NULL,NULL,NULL,NULL,NULL);
999
1000
1001 l_block_type.TIME_BUILDING_BLOCK_ID := p_bb_id;
1002 l_block_type.UNIT_OF_MEASURE := 'HOURS';
1003 l_block_type.PARENT_BUILDING_BLOCK_ID := p_day_id;
1004 l_block_type.SCOPE := 'DETAIL';
1005 l_block_type.OBJECT_VERSION_NUMBER := p_bb_ovn;
1006 l_block_type.RESOURCE_ID := p_person_id;
1007 l_block_type.RESOURCE_TYPE := 'PERSON';
1008 l_block_type.APPROVAL_STYLE_ID := p_approval_style_id;
1009 l_block_type.DATE_FROM := FND_DATE.DATE_TO_CANONICAL(SYSDATE);
1010 l_block_type.DATE_TO := FND_DATE.DATE_TO_CANONICAL(p_date_to);
1011 l_block_type.PARENT_BUILDING_BLOCK_OVN := 1;
1012 l_block_type.NEW := 'Y';
1013 l_block_type.CHANGED := 'Y';
1014 l_block_type.PROCESS := NULL;
1015 l_block_type.APPLICATION_SET_ID := NULL;
1016 l_block_type.TRANSLATION_DISPLAY_KEY := NULL;
1017 l_block_type.PARENT_IS_NEW := NULL;
1018 l_block_type.APPROVAL_STATUS := NULL;
1019 l_block_type.COMMENT_TEXT := NULL;
1020
1021 IF p_start_time IS NOT NULL
1022 THEN
1023 l_block_type.TYPE := 'RANGE';
1024 l_block_type.MEASURE := NULL;
1025 l_block_type.START_TIME := FND_DATE.DATE_TO_CANONICAL(p_start_time);
1026 l_block_type.STOP_TIME := FND_DATE.DATE_TO_CANONICAL(p_stop_time);
1027 ELSE
1028 l_block_type.TYPE := 'MEASURE';
1029 l_block_type.MEASURE := p_measure;
1030 l_block_type.START_TIME := NULL;
1031 l_block_type.STOP_TIME := NULL;
1032 END IF;
1033
1034 RETURN l_block_type;
1035
1036 END get_detail_block;
1037
1038
1039 FUNCTION get_attribute_for_detail ( p_bb_id IN NUMBER,
1040 p_bb_ovn IN NUMBER,
1041 p_element_type_id IN NUMBER,
1042 p_abs_att_id IN NUMBER,
1043 p_attribute_id IN NUMBER
1044 )
1045 RETURN hxc_attribute_type
1046 IS
1047 l_attribute_type HXC_ATTRIBUTE_TYPE;
1048
1049 BEGIN
1050
1051 l_attribute_type := HXC_ATTRIBUTE_TYPE(NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1052 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1053 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1054 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
1055
1056
1057 l_attribute_type.time_attribute_id := p_attribute_id;
1058 l_attribute_type.attribute_category := 'ELEMENT - '||p_element_type_id;
1059 l_attribute_type.attribute25 := p_abs_att_id ;
1060 l_attribute_type.bld_blk_info_type_id := 1;
1061 l_attribute_type.bld_blk_info_type := 'Dummy Element Context';
1062 l_attribute_type.BUILDING_BLOCK_ID := p_bb_id;
1063 l_attribute_type.BUILDING_BLOCK_OVN := p_bb_ovn;
1064 l_attribute_type.OBJECT_VERSION_NUMBER := 1;
1065
1066 RETURN l_attribute_type;
1067
1068 END get_attribute_for_detail;
1069
1070
1071
1072
1073 PROCEDURE get_abs_statuses ( p_person_id IN NUMBER,
1074 p_start_date IN DATE,
1075 p_end_date IN DATE,
1076 p_abs_status_rec OUT NOCOPY ABS_STATUS_TAB)
1077 IS
1078
1079 CURSOR get_alias_value ( p_alias_id IN NUMBER,
1080 p_element_id IN NUMBER )
1081 IS SELECT havtl.name
1082 FROM per_absence_attendance_types hav,
1083 per_abs_attendance_types_tl havtl
1084 WHERE hav.absence_attendance_type_id = havtl.absence_attendance_type_id
1085 AND hav.absence_attendance_type_id = p_element_id
1086 AND language = userenv('LANG');
1087
1088 l_measure NUMBER;
1089 l_element NUMBER;
1090 l_start DATE;
1091 l_end DATE;
1092
1093 l_alias_value VARCHAR2(400);
1094 l_alias_definition_id NUMBER;
1095
1096 l_index BINARY_INTEGER := 0;
1097
1098 l_abs_status_org_tab ABS_STATUS_TAB;
1099
1100 p_abs_tab abs_tab;
1101 i_ind BINARY_INTEGER:= 0;
1102
1103
1104 l_abs_status_tab abs_status_tab;
1105
1106 TYPE NUMBERTABLE IS TABLE OF VARCHAR2(5) INDEX BY BINARY_INTEGER;
1107
1108 TYPE hrrec IS RECORD
1109 ( id NUMBER,
1110 start_date DATE,
1111 stop_date DATE
1112 );
1113
1114 TYPE HRTABLE IS TABLE OF HRREC INDEX BY BINARY_INTEGER;
1115 l_sshr_tab HRTABLE;
1116 l_core_tab HRTABLE;
1117
1118 l_trans_tab NUMBERTABLE;
1119 l_atten_tab NUMBERTABLE;
1120 i BINARY_INTEGER;
1121 l_temp_id NUMBER;
1122
1123
1124 BEGIN
1125
1126 retrieve_absences( p_person_id ,
1127 p_start_date,
1128 p_end_date,
1129 p_abs_tab );
1130
1131 IF p_abs_tab.COUNT >0
1132 THEN
1133 i_ind := p_abs_tab.FIRST;
1134 LOOP
1135 << CONTINUE_TO_NEXT >>
1136 LOOP
1137 OPEN get_alias_value( l_alias_definition_id,
1138 p_abs_tab(i_ind).abs_type_id );
1139
1140 FETCH get_alias_value INTO l_alias_value;
1141
1142
1143 CLOSE get_alias_value;
1144
1145 IF p_abs_tab(i_ind).uom = 'H'
1146 THEN
1147
1148 -- Bug 8853984
1149 -- Calcuation of earliest start and latest end for
1150 -- Hour based absences.
1151 IF l_atten_tab.EXISTS(p_abs_tab(i_ind).abs_attendance_id)
1152 THEN
1153 -- Get the absence attendance id
1154 l_temp_id := p_abs_tab(i_ind).abs_attendance_id;
1155 -- Check if the incoming value is lesser than the
1156 -- stored value. If yes store it.
1157 IF p_abs_tab(i_ind).abs_start < l_core_tab(l_temp_id).start_date
1158 THEN
1159 l_abs_status_tab(l_core_tab(l_temp_id).id).abs_start
1160 := TO_CHAR(p_abs_tab(i_ind).abs_start,
1161 FND_PROFILE.VALUE('ICX_DATE_FORMAT_MASK')||' HH24:MI');
1162 l_core_tab(l_temp_id).start_date := p_abs_tab(i_ind).abs_start;
1163 END IF;
1164 --Check if the incoming value is greater than the
1165 -- stored value. If yes, store it.
1166 IF p_abs_tab(i_ind).abs_end > l_core_tab(l_temp_id).stop_date
1167 THEN
1168 l_abs_status_tab(l_core_tab(l_temp_id).id).abs_end
1169 := TO_CHAR(p_abs_tab(i_ind).abs_end,
1170 FND_PROFILE.VALUE('ICX_DATE_FORMAT_MASK')||' HH24:MI');
1171 l_core_tab(l_temp_id).stop_date := p_abs_tab(i_ind).abs_end;
1172 END IF;
1173 END IF;
1174
1175 -- Bug 8853984
1176 -- Calcuation of earliest start and latest end for
1177 -- Hour based absences.
1178 IF l_trans_tab.EXISTS( p_abs_tab(i_ind).transaction_id)
1179 THEN
1180 l_temp_id := p_abs_tab(i_ind).transaction_id;
1181 IF p_abs_tab(i_ind).abs_start < l_sshr_tab(l_temp_id).start_date
1182 THEN
1183 l_abs_status_tab(l_sshr_tab(l_temp_id).id).abs_start
1184 := TO_CHAR(p_abs_tab(i_ind).abs_start,
1185 FND_PROFILE.VALUE('ICX_DATE_FORMAT_MASK')||' HH24:MI');
1186 l_sshr_tab(l_temp_id).start_date := p_abs_tab(i_ind).abs_start;
1187 END IF;
1188 IF p_abs_tab(i_ind).abs_end > l_sshr_tab(l_temp_id).stop_date
1189 THEN
1190 l_abs_status_tab(l_sshr_tab(l_temp_id).id).abs_end
1191 := TO_CHAR(p_abs_tab(i_ind).abs_end,
1192 FND_PROFILE.VALUE('ICX_DATE_FORMAT_MASK')||' HH24:MI');
1193 l_sshr_tab(l_temp_id).stop_date := p_abs_tab(i_ind).abs_end;
1194 END IF;
1195 END IF;
1196
1197
1198 END IF;
1199
1200 IF l_trans_tab.EXISTS( p_abs_tab(i_ind).transaction_id)
1201 OR l_atten_tab.EXISTS(p_abs_tab(i_ind).abs_attendance_id)
1202 THEN
1203 EXIT CONTINUE_TO_NEXT;
1204 END IF;
1205
1206 l_index := l_index + 1;
1207 l_abs_status_tab(l_index).abs_type := l_alias_value;
1208
1209 IF p_abs_tab(i_ind).uom = 'D'
1210 THEN
1211 l_abs_status_tab(l_index).UOM := get_lookup_value('NAME_TRANSLATIONS','DAYS');
1212 l_abs_status_tab(l_index).abs_start := TO_CHAR(NVL(p_abs_tab(i_ind).rec_start_date,
1213 p_abs_tab(i_ind).abs_date
1214 ),
1215 FND_PROFILE.VALUE('ICX_DATE_FORMAT_MASK'));
1216 l_abs_status_tab(l_index).abs_end := TO_CHAR(NVL(p_abs_tab(i_ind).rec_end_date,
1217 p_abs_tab(i_ind).abs_date
1218 ),
1219 FND_PROFILE.VALUE('ICX_DATE_FORMAT_MASK'));
1220 -- Bug 8859597
1221 -- Removing Duration.
1222 --l_abs_status_tab(l_index).measure := TO_CHAR(p_abs_tab(i_ind).rec_end_date-p_abs_tab(i_ind).rec_start_date +1);
1223 ELSIF p_abs_tab(i_ind).uom = 'H'
1224 THEN
1225 l_abs_status_tab(l_index).UOM := get_lookup_value('NAME_TRANSLATIONS','HOURS');
1226
1227 -- Bug 8853984
1228 -- Calculation of start and end changed.
1229 l_abs_status_tab(l_index).abs_start := TO_CHAR(p_abs_tab(i_ind).abs_start,
1230 FND_PROFILE.VALUE('ICX_DATE_FORMAT_MASK')||' HH24:MI');
1231 l_abs_status_tab(l_index).abs_end := TO_CHAR(p_abs_tab(i_ind).abs_end,
1232 FND_PROFILE.VALUE('ICX_DATE_FORMAT_MASK')||' HH24:MI');
1233
1234 -- Bug 8859597
1235 -- Removing Duration.
1236 /*
1237 IF TRUNC(p_abs_tab(i_ind).rec_start_date) <> TRUNC(p_abs_tab(i_ind).rec_end_date)
1238 THEN
1239 l_abs_status_tab(l_index).measure := p_abs_tab(i_ind).duration*
1240 (( TRUNC(p_abs_tab(i_ind).rec_end_date) - TRUNC(p_abs_tab(i_ind).rec_start_date))+1) ;
1241 ELSE
1242 l_abs_status_tab(l_index).measure := p_abs_tab(i_ind).duration;
1243 END IF;
1244 */
1245 END IF;
1246
1247 IF p_abs_tab(i_ind).prg_appl_id = 800
1248 THEN
1249 l_abs_status_tab(l_index).source := get_lookup_value('HXC_ABS_PROG_APPLICATIONS','CHR');
1250 ELSE
1251 l_abs_status_tab(l_index).source := get_lookup_value('HXC_ABS_PROG_APPLICATIONS','OTL');
1252 END IF;
1253
1254 IF p_abs_tab(i_ind).abs_attendance_id IS NOT NULL
1255 THEN
1256 l_atten_tab(p_abs_tab(i_ind).abs_attendance_id) := 'Y';
1257 -- Bug 8853984
1258 -- Store the values in the cache table.
1259 l_core_tab(p_abs_tab(i_ind).abs_attendance_id).id := l_index;
1260 l_core_tab(p_abs_tab(i_ind).abs_attendance_id).start_date := p_abs_tab(i_ind).abs_start;
1261 l_core_tab(p_abs_tab(i_ind).abs_attendance_id).stop_date := p_abs_tab(i_ind).abs_end;
1262 END IF;
1263
1264 IF p_abs_tab(i_ind).transaction_id IS NOT NULL
1265 THEN
1266 l_trans_tab(p_abs_tab(i_ind).transaction_id) := 'Y';
1267 l_abs_status_tab(l_index).status := get_lookup_value('LEAVE_STATUS','PA');
1268 -- Bug 8853984
1269 -- Store the values in the cache table.
1270 l_sshr_tab(p_abs_tab(i_ind).transaction_id).id := l_index;
1271 l_sshr_tab(p_abs_tab(i_ind).transaction_id).start_date := p_abs_tab(i_ind).abs_start;
1272 l_sshr_tab(p_abs_tab(i_ind).transaction_id).stop_date := p_abs_tab(i_ind).abs_end;
1273 ELSIF p_abs_tab(i_ind).transaction_id IS NULL
1274 THEN
1275 l_abs_status_tab(l_index).status := get_lookup_value('LEAVE_STATUS','A');
1276 END IF;
1277
1278 EXIT CONTINUE_TO_NEXT;
1279 END LOOP CONTINUE_TO_NEXT ;
1280 i_ind := p_abs_tab.NEXT(i_ind);
1281 EXIT WHEN NOT p_abs_tab.EXISTS(i_ind);
1282
1283 END LOOP;
1284
1285 END IF;
1286
1287 IF g_debug
1288 THEN
1289 IF l_abs_status_tab.COUNT >0
1290 THEN
1291 FOR i IN l_abs_status_tab.FIRST..l_abs_status_tab.LAST
1292 LOOP
1293 hr_utility.trace('ABS: l_abs_status_tab(i).abs_type '||l_abs_status_tab(i).abs_type);
1294 hr_utility.trace('ABS: l_abs_status_tab(i).status '||l_abs_status_tab(i).status);
1295 hr_utility.trace('ABS: l_abs_status_tab(i).uom '||l_abs_status_tab(i).uom);
1296 hr_utility.trace('ABS: l_abs_status_tab(i).source '||l_abs_status_tab(i).source);
1297 hr_utility.trace('ABS: l_abs_status_tab(i).abs_start '||l_abs_status_tab(i).abs_start);
1298 hr_utility.trace('ABS: l_abs_status_tab(i).abs_end '||l_abs_status_tab(i).abs_end);
1299 -- Bug 8859597
1300 -- Removing Duration.
1301 --hr_utility.trace('ABS: l_abs_status_tab(i).measure '||l_abs_status_tab(i).measure);
1302 END LOOP;
1303 END IF;
1304 END IF;
1305 p_abs_status_rec := l_abs_status_tab;
1306
1307 END get_abs_statuses;
1308
1309
1310
1311
1312 PROCEDURE get_abs_statuses ( p_person_id IN NUMBER,
1313 p_start_date IN VARCHAR2,
1314 p_end_date IN VARCHAR2,
1315 p_abs_status_tab OUT NOCOPY HXC_ABS_STATUS_TABLE)
1316 IS
1317
1318 l_abs_status_rec HXC_ABS_STATUS_TYPE;
1319 l_abs_status_tab ABS_STATUS_TAB;
1320 l_ind BINARY_INTEGER := 1;
1321
1322 BEGIN
1323
1324 get_abs_statuses( p_person_id,
1325 fnd_date.canonical_to_date(p_start_date), --TO_DATE(p_start_date,'yyyy/mm/dd'),
1326 fnd_date.canonical_to_date(p_end_date), --TO_DATE(p_end_date,'yyyy/mm/dd'),
1327 l_abs_status_tab);
1328
1329 p_abs_status_tab := HXC_ABS_STATUS_TABLE();
1330
1331 IF l_abs_status_tab.COUNT > 0 THEN
1332
1333 FOR i IN l_abs_status_tab.FIRST..l_abs_status_tab.LAST
1334 LOOP
1335 p_abs_status_tab.EXTEND(1);
1336 -- Bug 8859597
1337 -- Removing Duration.
1338 p_abs_status_tab(l_ind) := HXC_ABS_STATUS_TYPE(NULL,NULL,NULL,NULL,NULL,NULL);
1339 p_abs_status_tab(l_ind).abs_type := l_abs_status_tab(i).abs_type;
1340 p_abs_status_tab(l_ind).abs_start := l_abs_status_tab(i).abs_start;
1341 p_abs_status_tab(l_ind).abs_end := l_abs_status_tab(i).abs_end;
1342 -- Bug 8859597
1343 -- Removing Duration.
1344 --p_abs_status_tab(l_ind).measure := l_abs_status_tab(i).measure;
1345 p_abs_status_tab(l_ind).status := l_abs_status_tab(i).status;
1346 p_abs_status_tab(l_ind).UOM := l_abs_status_tab(i).uom;
1347 p_abs_status_tab(l_ind).source := l_abs_status_tab(i).source;
1348 l_ind := l_ind + 1;
1349 END LOOP;
1350
1351 END IF;
1352
1353 RETURN;
1354
1355 END get_abs_statuses;
1356
1357
1358 -- SVG added overloaded procedure add_absence_types
1359
1360
1361 PROCEDURE add_absence_types ( p_person_id IN NUMBER,
1362 p_start_date IN DATE,
1363 p_end_date IN DATE,
1364 p_approval_style_id IN NUMBER,
1365 p_lock_rowid IN VARCHAR2,
1366 p_source IN VARCHAR2 ,
1367 p_timekeeper_id IN NUMBER,
1368 p_iteration_count IN NUMBER,
1369 p_block_array IN OUT NOCOPY HXC_BLOCK_TABLE_TYPE,
1370 p_attribute_array IN OUT NOCOPY HXC_ATTRIBUTE_TABLE_TYPE )
1371 IS
1372
1373 l_index BINARY_INTEGER;
1374
1375 BEGIN
1376
1377 -- Bug 8829088
1378 -- Added this init so that we avoid the ORA-6531 in case of
1379 -- absence of setup.
1380
1381 g_messages := HXC_MESSAGE_TABLE_TYPE();
1382
1383
1384
1385 IF NVL(FND_PROFILE.VALUE('HR_ABS_OTL_INTEGRATION'),'N') = 'N'
1386 THEN
1387 RETURN;
1388 END IF;
1389
1390 if g_debug then
1391
1392 hr_utility.trace('Entered add_absence_types');
1393 hr_utility.trace('Before clear_prev_sessions');
1394
1395 end if;
1396
1397
1398 /*
1399 delete_other_sessions(p_person_id,
1400 p_start_date,
1401 p_end_date,
1402 p_lock_rowid);
1403 */
1404 clear_prev_sessions(p_person_id,
1405 p_start_date,
1406 p_end_date,
1407 p_lock_rowid);
1408
1409 if g_debug then
1410
1411 hr_utility.trace('After clear_prev_sessions');
1412
1413 end if;
1414
1415
1416 gen_alt_ids(p_person_id,
1417 p_start_date,
1418 p_end_date,
1419 'TK');
1420
1421 if g_debug then
1422
1423 hr_utility.trace('After gen_alt_ids');
1424
1425 end if;
1426
1427
1428 IF g_pref_table.COUNT >0
1429 THEN
1430 l_index := g_pref_table.FIRST;
1431 LOOP
1432 IF g_pref_table(l_index).preference_code = 'TS_ABS_PREFERENCES'
1433 THEN
1434 IF g_pref_table(l_index).attribute1 <> 'Y'
1435 THEN
1436 IF g_debug
1437 THEN
1438 hr_utility.trace('ABS : Integration not enabled for this employee ');
1439 END IF;
1440 RETURN;
1441 ELSE
1442 EXIT;
1443 END IF;
1444 END IF;
1445 l_index := g_pref_table.NEXT(l_index);
1446 EXIT WHEN NOT g_pref_table.EXISTS(l_index);
1447 END LOOP;
1448 END IF;
1449
1450
1451
1452 IF p_block_array.COUNT = 0
1453 THEN
1454
1455 if p_source = 'TK' then
1456
1457 create_tc_with_abs ( p_person_id => p_person_id,
1458 p_start_date => p_start_date,
1459 p_end_date => p_end_date,
1460 p_block_array => p_block_array,
1461 p_approval_style_id => p_approval_style_id,
1462 p_attribute_array => p_attribute_array,
1463 p_lock_rowid => p_lock_rowid,
1464 p_source => p_source,
1465 p_timekeeper_id => p_timekeeper_id,
1466 p_iteration_count => p_iteration_count);
1467
1468 else
1469
1470 create_tc_with_abs ( p_person_id => p_person_id,
1471 p_start_date => p_start_date,
1472 p_end_date => p_end_date,
1473 p_block_array => p_block_array,
1474 p_approval_style_id => p_approval_style_id,
1475 p_attribute_array => p_attribute_array,
1476 p_lock_rowid => p_lock_rowid );
1477
1478 end if;
1479
1480 ELSE
1481 add_abs_to_tc ( p_person_id => p_person_id,
1482 p_start_date => p_start_date,
1483 p_end_date => p_end_date,
1484 p_block_array => p_block_array,
1485 p_approval_style_id => p_approval_style_id,
1486 p_attribute_array => p_attribute_array,
1487 p_lock_rowid => p_lock_rowid );
1488 END IF;
1489
1490 RETURN;
1491
1492 END ; -- overloaded proc add_absence_types
1493
1494
1495 -- Change end
1496
1497
1498 PROCEDURE add_absence_types ( p_person_id IN NUMBER,
1499 p_start_date IN DATE,
1500 p_end_date IN DATE,
1501 p_approval_style_id IN NUMBER,
1502 p_lock_rowid IN VARCHAR2,
1503 p_block_array IN OUT NOCOPY HXC_BLOCK_TABLE_TYPE,
1504 p_attribute_array IN OUT NOCOPY HXC_ATTRIBUTE_TABLE_TYPE )
1505 IS
1506
1507 l_index BINARY_INTEGER;
1508
1509 BEGIN
1510
1511 -- Bug 8829088
1512 -- Added this init so that we avoid the ORA-6531 in case of
1513 -- absence of setup.
1514
1515 g_messages := HXC_MESSAGE_TABLE_TYPE();
1516
1517 IF NVL(FND_PROFILE.VALUE('HR_ABS_OTL_INTEGRATION'),'N') = 'N'
1518 THEN
1519 RETURN;
1520 END IF;
1521
1522
1523 delete_other_sessions(p_person_id,
1524 p_start_date,
1525 p_end_date,
1526 p_lock_rowid);
1527
1528 gen_alt_ids(p_person_id,
1529 p_start_date,
1530 p_end_date,
1531 'SS');
1532
1533
1534 IF g_pref_table.COUNT >0
1535 THEN
1536 l_index := g_pref_table.FIRST;
1537 LOOP
1538 IF g_pref_table(l_index).preference_code = 'TS_ABS_PREFERENCES'
1539 THEN
1540 IF g_pref_table(l_index).attribute1 <> 'Y'
1541 THEN
1542 IF g_debug
1543 THEN
1544 hr_utility.trace('ABS : Integration not enabled for this employee ');
1545 END IF;
1546 RETURN;
1547 ELSE
1548 EXIT;
1549 END IF;
1550 END IF;
1551 l_index := g_pref_table.NEXT(l_index);
1552 EXIT WHEN NOT g_pref_table.EXISTS(l_index);
1553 END LOOP;
1554 END IF;
1555
1556
1557
1558 IF p_block_array.COUNT = 0
1559 THEN
1560 create_tc_with_abs ( p_person_id => p_person_id,
1561 p_start_date => p_start_date,
1562 p_end_date => p_end_date,
1563 p_block_array => p_block_array,
1564 p_approval_style_id => p_approval_style_id,
1565 p_attribute_array => p_attribute_array,
1566 p_lock_rowid => p_lock_rowid );
1567 ELSE
1568 add_abs_to_tc ( p_person_id => p_person_id,
1569 p_start_date => p_start_date,
1570 p_end_date => p_end_date,
1571 p_block_array => p_block_array,
1572 p_approval_style_id => p_approval_style_id,
1573 p_attribute_array => p_attribute_array,
1574 p_lock_rowid => p_lock_rowid );
1575 END IF;
1576
1577 RETURN;
1578
1579 END add_absence_types;
1580
1581
1582
1583
1584 PROCEDURE tc_api_add_absence_types ( p_person_id IN NUMBER,
1585 p_start_date IN DATE,
1586 p_end_date IN DATE,
1587 p_blocks IN OUT NOCOPY hxc_self_service_time_deposit.timecard_info,
1588 p_app_attributes IN OUT NOCOPY hxc_self_service_time_deposit.app_attributes_info
1589 )
1590 IS
1591
1592 l_index BINARY_INTEGER;
1593 l_app_attribute_index BINARY_INTEGER;
1594
1595 l_attribute_array hxc_attribute_table_type;
1596 l_block_array hxc_block_table_type;
1597
1598 l_approval_style_id hxc_time_building_blocks.approval_style_id%type;
1599 l_lock_rowid VARCHAR2(100) := '0';
1600 l_timecard_exists varchar2(1) := 'N';
1601
1602 BEGIN
1603
1604 l_attribute_array := hxc_attribute_table_type ();
1605 l_block_array := hxc_block_table_type();
1606
1607 BEGIN
1608
1609 SELECT 'Y'
1610 INTO l_timecard_exists
1611 FROM hxc_timecard_summary
1612 WHERE resource_id = p_person_id
1613 AND trunc(start_time) = trunc(p_start_date)
1614 AND trunc(stop_time) = trunc(p_end_date);
1615
1616 EXCEPTION
1617 WHEN NO_DATA_FOUND THEN
1618 l_timecard_exists := 'N';
1619 END;
1620
1621
1622 IF l_timecard_exists = 'Y' THEN
1623 RETURN; -- DO NOTHING
1624 ELSE
1625 delete_other_sessions(p_person_id,
1626 p_start_date,
1627 p_end_date,
1628 l_lock_rowid);
1629
1630 -- convert the app_attributes_info into hxc_attribute_table_type
1631 hxc_timestore_deposit_util.convert_app_attributes_to_type (p_attributes => l_attribute_array,
1632 p_app_attributes => p_app_attributes
1633 );
1634 -- conver the timecard_block_info into hxc_block_table_type
1635 l_block_array := hxc_timestore_deposit_util.convert_tbb_to_type(p_blocks);
1636
1637 l_index := l_block_array.first;
1638 LOOP
1639 EXIT WHEN NOT l_block_array.exists(l_index);
1640 IF l_block_array(l_index).scope = 'TIMECARD' THEN
1641 l_approval_style_id := l_block_array(l_index).approval_style_id;
1642 EXIT;
1643 END IF;
1644 l_index := l_block_array.next(l_index);
1645 END LOOP;
1646
1647 IF l_block_array.COUNT = 0
1648 THEN
1649 create_tc_with_abs ( p_person_id => p_person_id,
1650 p_start_date => p_start_date,
1651 p_end_date => p_end_date,
1652 p_block_array => l_block_array,
1653 p_approval_style_id => l_approval_style_id,
1654 p_attribute_array => l_attribute_array,
1655 p_lock_rowid => l_lock_rowid
1656 );
1657 ELSE
1658 add_abs_to_tc ( p_person_id => p_person_id,
1659 p_start_date => p_start_date,
1660 p_end_date => p_end_date,
1661 p_block_array => l_block_array,
1662 p_approval_style_id => l_approval_style_id,
1663 p_attribute_array => l_attribute_array,
1664 p_lock_rowid => l_lock_rowid
1665 );
1666 END IF;
1667
1668 l_index := l_attribute_array.first;
1669 LOOP
1670 EXIT WHEN NOT l_attribute_array.exists(l_index);
1671
1672 IF l_attribute_array(l_index).ATTRIBUTE_CATEGORY like 'ELEMENT%' THEN
1673
1674 l_app_attribute_index := NVL (p_app_attributes.LAST, 0) + 1;
1675
1676 p_app_attributes(l_app_attribute_index).time_attribute_id := l_attribute_array(l_index).TIME_ATTRIBUTE_ID;
1677 p_app_attributes(l_app_attribute_index).building_block_id := l_attribute_array(l_index).BUILDING_BLOCK_ID;
1678 p_app_attributes(l_app_attribute_index).attribute_name := 'Dummy Element Context';
1679 p_app_attributes(l_app_attribute_index).attribute_value := l_attribute_array(l_index).ATTRIBUTE_CATEGORY;
1680 p_app_attributes(l_app_attribute_index).segment := 'ATTRIBUTE_CATEGORY';
1681 p_app_attributes(l_app_attribute_index).bld_blk_info_type := 'Dummy Element Context';
1682 p_app_attributes(l_app_attribute_index).CATEGORY := 'Dummy Element Context';
1683 p_app_attributes(l_app_attribute_index).updated := l_attribute_array(l_index).CHANGED;
1684 p_app_attributes(l_app_attribute_index).changed := l_attribute_array(l_index).CHANGED;
1685 p_app_attributes(l_app_attribute_index).process := l_attribute_array(l_index).PROCESS;
1686
1687 END IF;
1688
1689 l_index := l_attribute_array.next(l_index);
1690 END LOOP;
1691
1692 p_blocks.delete;
1693
1694 l_index := l_block_array.first;
1695 LOOP
1696 EXIT WHEN NOT l_block_array.exists(l_index);
1697 p_blocks(l_index).TIME_BUILDING_BLOCK_ID := l_block_array(l_index).TIME_BUILDING_BLOCK_ID;
1698 p_blocks(l_index).TYPE := l_block_array(l_index).TYPE;
1699 p_blocks(l_index).measure := l_block_array(l_index).measure;
1700 p_blocks(l_index).unit_of_measure := l_block_array(l_index).unit_of_measure;
1701 p_blocks(l_index).start_time := fnd_date.canonical_to_date(l_block_array(l_index).start_time);
1702 p_blocks(l_index).stop_time := fnd_date.canonical_to_date(l_block_array(l_index).stop_time);
1703 p_blocks(l_index).parent_building_block_id := l_block_array(l_index).parent_building_block_id;
1704 p_blocks(l_index).parent_is_new := l_block_array(l_index).parent_is_new;
1705 p_blocks(l_index).SCOPE := l_block_array(l_index).SCOPE;
1706 p_blocks(l_index).object_version_number := l_block_array(l_index).object_version_number;
1707 p_blocks(l_index).approval_status := l_block_array(l_index).approval_status;
1708 p_blocks(l_index).resource_id := l_block_array(l_index).resource_id;
1709 p_blocks(l_index).resource_type := l_block_array(l_index).resource_type;
1710 p_blocks(l_index).approval_style_id := l_block_array(l_index).approval_style_id;
1711 p_blocks(l_index).date_from := fnd_date.canonical_to_date(l_block_array(l_index).date_from);
1712 p_blocks(l_index).date_to := fnd_date.canonical_to_date(l_block_array(l_index).date_to);
1713 p_blocks(l_index).comment_text := l_block_array(l_index).comment_text;
1714 p_blocks(l_index).parent_building_block_ovn := l_block_array(l_index).parent_building_block_ovn;
1715 p_blocks(l_index).NEW := l_block_array(l_index).NEW;
1716 p_blocks(l_index).changed := l_block_array(l_index).changed;
1717 p_blocks(l_index).process := l_block_array(l_index).process;
1718 p_blocks(l_index).application_set_id := l_block_array(l_index).application_set_id;
1719 p_blocks(l_index).translation_display_key := l_block_array(l_index).translation_display_key;
1720
1721 l_index := l_block_array.next(l_index);
1722 END LOOP;
1723
1724 END IF; --IF l_timecard_exists = 'Y' THEN
1725
1726 END tc_api_add_absence_types;
1727
1728 PROCEDURE tc_api_add_absence_types ( p_blocks IN OUT NOCOPY hxc_self_service_time_deposit.timecard_info,
1729 p_app_attributes IN OUT NOCOPY hxc_self_service_time_deposit.app_attributes_info
1730 )
1731
1732 IS
1733
1734 l_index BINARY_INTEGER;
1735 l_app_attribute_index BINARY_INTEGER;
1736 l_attribute_array hxc_attribute_table_type;
1737 l_block_array hxc_block_table_type;
1738
1739 l_person_id hxc_time_building_blocks.resource_id%type;
1740 l_approval_style_id hxc_time_building_blocks.approval_style_id%type;
1741
1742 l_start_date DATE;
1743 l_end_date DATE;
1744
1745 l_lock_rowid VARCHAR2(100) := '0';
1746 l_timecard_exists varchar2(1) := 'N';
1747
1748 BEGIN
1749
1750 l_attribute_array := hxc_attribute_table_type ();
1751 l_block_array := hxc_block_table_type();
1752
1753 -- get person id, timecard start time and timecard stop time
1754 l_index := p_blocks.first;
1755 LOOP
1756 EXIT WHEN NOT p_blocks.exists(l_index);
1757 IF p_blocks(l_index).scope = 'TIMECARD' THEN
1758 l_approval_style_id := p_blocks(l_index).approval_style_id;
1759 l_person_id := p_blocks(l_index).resource_id;
1760 l_start_date := rtrim(p_blocks(l_index).start_time);
1761 l_end_date := rtrim(p_blocks(l_index).stop_time);
1762 EXIT;
1763 END IF;
1764 l_index := p_blocks.next(l_index);
1765 END LOOP;
1766
1767 BEGIN
1768
1769 SELECT 'Y'
1770 INTO l_timecard_exists
1771 FROM hxc_timecard_summary
1772 WHERE resource_id = l_person_id
1773 AND trunc(start_time) = trunc(l_start_date)
1774 AND trunc(stop_time) = trunc(l_end_date);
1775
1776 EXCEPTION
1777 WHEN NO_DATA_FOUND THEN
1778 l_timecard_exists := 'N';
1779 END;
1780
1781 IF l_timecard_exists = 'Y' THEN
1782 RETURN; -- DO NOTHING
1783 ELSE
1784
1785 delete_other_sessions(l_person_id,
1786 l_start_date,
1787 l_end_date,
1788 l_lock_rowid);
1789
1790 -- convert the app_attributes_info into hxc_attribute_table_type
1791 hxc_timestore_deposit_util.convert_app_attributes_to_type
1792 (p_attributes => l_attribute_array,
1793 p_app_attributes => p_app_attributes
1794 );
1795 -- conver the timecard_block_info into hxc_block_table_type
1796 l_block_array := hxc_timestore_deposit_util.convert_tbb_to_type(p_blocks);
1797
1798 IF l_block_array.COUNT = 0
1799 THEN
1800 create_tc_with_abs ( p_person_id => l_person_id,
1801 p_start_date => l_start_date,
1802 p_end_date => l_end_date,
1803 p_block_array => l_block_array,
1804 p_approval_style_id => l_approval_style_id,
1805 p_attribute_array => l_attribute_array,
1806 p_lock_rowid => l_lock_rowid
1807 );
1808 ELSE
1809 add_abs_to_tc ( p_person_id => l_person_id,
1810 p_start_date => l_start_date,
1811 p_end_date => l_end_date,
1812 p_block_array => l_block_array,
1813 p_approval_style_id => l_approval_style_id,
1814 p_attribute_array => l_attribute_array,
1815 p_lock_rowid => l_lock_rowid
1816 );
1817 END IF;
1818
1819 l_index := l_attribute_array.first;
1820 LOOP
1821 EXIT WHEN NOT l_attribute_array.exists(l_index);
1822
1823 IF l_attribute_array(l_index).ATTRIBUTE_CATEGORY like 'ELEMENT%' THEN
1824
1825 l_app_attribute_index := NVL (p_app_attributes.LAST, 0) + 1;
1826
1827 p_app_attributes(l_app_attribute_index).time_attribute_id := l_attribute_array(l_index).TIME_ATTRIBUTE_ID;
1828 p_app_attributes(l_app_attribute_index).building_block_id := l_attribute_array(l_index).BUILDING_BLOCK_ID;
1829 p_app_attributes(l_app_attribute_index).attribute_name := 'Dummy Element Context';
1830 p_app_attributes(l_app_attribute_index).attribute_value := l_attribute_array(l_index).ATTRIBUTE_CATEGORY;
1831 p_app_attributes(l_app_attribute_index).segment := 'ATTRIBUTE_CATEGORY';
1832 p_app_attributes(l_app_attribute_index).bld_blk_info_type := 'Dummy Element Context';
1833 p_app_attributes(l_app_attribute_index).CATEGORY := 'Dummy Element Context';
1834 p_app_attributes(l_app_attribute_index).updated := l_attribute_array(l_index).CHANGED;
1835 p_app_attributes(l_app_attribute_index).changed := l_attribute_array(l_index).CHANGED;
1836 p_app_attributes(l_app_attribute_index).process := l_attribute_array(l_index).PROCESS;
1837
1838 END IF;
1839
1840 l_index := l_attribute_array.next(l_index);
1841 END LOOP;
1842
1843 p_blocks.delete;
1844
1845 l_index := l_block_array.first;
1846 LOOP
1847 EXIT WHEN NOT l_block_array.exists(l_index);
1848 p_blocks(l_index).TIME_BUILDING_BLOCK_ID := l_block_array(l_index).TIME_BUILDING_BLOCK_ID;
1849 p_blocks(l_index).TYPE := l_block_array(l_index).TYPE;
1850 p_blocks(l_index).measure := l_block_array(l_index).measure;
1851 p_blocks(l_index).unit_of_measure := l_block_array(l_index).unit_of_measure;
1852 p_blocks(l_index).start_time := fnd_date.canonical_to_date(l_block_array(l_index).start_time);
1853 p_blocks(l_index).stop_time := fnd_date.canonical_to_date(l_block_array(l_index).stop_time);
1854 p_blocks(l_index).parent_building_block_id := l_block_array(l_index).parent_building_block_id;
1855 p_blocks(l_index).parent_is_new := l_block_array(l_index).parent_is_new;
1856 p_blocks(l_index).SCOPE := l_block_array(l_index).SCOPE;
1857 p_blocks(l_index).object_version_number := l_block_array(l_index).object_version_number;
1858 p_blocks(l_index).approval_status := l_block_array(l_index).approval_status;
1859 p_blocks(l_index).resource_id := l_block_array(l_index).resource_id;
1860 p_blocks(l_index).resource_type := l_block_array(l_index).resource_type;
1861 p_blocks(l_index).approval_style_id := l_block_array(l_index).approval_style_id;
1862 p_blocks(l_index).date_from := fnd_date.canonical_to_date(l_block_array(l_index).date_from);
1863 p_blocks(l_index).date_to := fnd_date.canonical_to_date(l_block_array(l_index).date_to);
1864 p_blocks(l_index).comment_text := l_block_array(l_index).comment_text;
1865 p_blocks(l_index).parent_building_block_ovn := l_block_array(l_index).parent_building_block_ovn;
1866 p_blocks(l_index).NEW := l_block_array(l_index).NEW;
1867 p_blocks(l_index).changed := l_block_array(l_index).changed;
1868 p_blocks(l_index).process := l_block_array(l_index).process;
1869 p_blocks(l_index).application_set_id := l_block_array(l_index).application_set_id;
1870 p_blocks(l_index).translation_display_key := l_block_array(l_index).translation_display_key;
1871
1872 l_index := l_block_array.next(l_index);
1873 END LOOP;
1874
1875 END IF; --IF l_timecard_exists = 'Y' THEN
1876
1877 END tc_api_add_absence_types;
1878
1879
1880 PROCEDURE add_abs_to_tc ( p_person_id IN NUMBER,
1881 p_start_date IN DATE,
1882 p_end_date IN DATE,
1883 p_approval_style_id IN NUMBER,
1884 p_block_array IN OUT NOCOPY HXC_BLOCK_TABLE_TYPE,
1885 p_attribute_array IN OUT NOCOPY HXC_ATTRIBUTE_TABLE_TYPE ,
1886 p_lock_rowid IN VARCHAR2 )
1887 IS
1888
1889 l_index BINARY_INTEGER;
1890 l_block_ind BINARY_INTEGER;
1891 l_att_ind BINARY_INTEGER;
1892
1893 l_messages HXC_MESSAGE_TABLE_TYPE;
1894
1895 l_timecard_id NUMBER := 0;
1896 l_timecard_ovn NUMBER := 0;
1897
1898 l_day_id_tab NUMTAB;
1899 l_day_ovn_tab NUMTAB;
1900
1901 offset NUMBER;
1902 IS_NEG BOOLEAN := NULL;
1903 TC_FOUND BOOLEAN ;
1904
1905 INCONSISTENT_IDS EXCEPTION;
1906 NO_DAY_FOUND EXCEPTION;
1907 NO_TIMECARD_FOUND EXCEPTION;
1908
1909 l_abs_tab ABS_TAB;
1910
1911 l_det_id NUMBER := 0;
1912 l_att_id NUMBER := 0;
1913
1914 l_UOM VARCHAR2(5);
1915
1916 BEGIN
1917
1918 l_index := p_block_array.FIRST;
1919 LOOP
1920 <<TO_CONTINUE_TO_NEXT_BLOCK>>
1921 LOOP
1922 IF ABS(p_block_array(l_index).time_building_block_id) > l_det_id
1923 THEN
1924 l_det_id := ABS(p_block_array(l_index).time_building_block_id);
1925 END IF;
1926 IF p_block_array(l_index).time_building_block_id < 0
1927 THEN
1928 IF IS_NEG IS NULL
1929 THEN
1930 IS_NEG := TRUE;
1931 offset := -1;
1932 ELSIF NOT IS_NEG
1933 THEN
1934 RAISE INCONSISTENT_IDS;
1935 END IF;
1936 ELSE
1937 IF IS_NEG IS NULL
1938 THEN
1939 IS_NEG := FALSE;
1940 offset := 1;
1941 ELSIF IS_NEG
1942 THEN
1943 RAISE INCONSISTENT_IDS;
1944 END IF;
1945 END IF;
1946
1947 IF p_block_array(l_index).SCOPE = 'TIMECARD'
1948 THEN
1949 TC_FOUND := TRUE;
1950 EXIT TO_CONTINUE_TO_NEXT_BLOCK;
1951 END IF;
1952
1953 IF p_block_array(l_index).SCOPE = 'DAY'
1954 THEN
1955 l_day_id_tab(p_block_array(l_index).start_time) := p_block_array(l_index).time_building_block_id;
1956 l_day_ovn_tab(p_block_array(l_index).start_time) := p_block_array(l_index).object_version_number;
1957 EXIT TO_CONTINUE_TO_NEXT_BLOCK;
1958 END IF;
1959 EXIT TO_CONTINUE_TO_NEXT_BLOCK;
1960 END LOOP TO_CONTINUE_TO_NEXT_BLOCK;
1961 l_index := p_block_array.NEXT(l_index);
1962 EXIT WHEN NOT p_block_array.EXISTS(l_index);
1963 END LOOP;
1964
1965 IF l_day_id_tab.COUNT <> (TRUNC(p_end_date) - TRUNC(p_start_date))+1
1966 OR l_day_ovn_tab.COUNT <> (TRUNC(p_end_date) - TRUNC(p_start_date))+1
1967 THEN
1968 RAISE NO_DAY_FOUND;
1969 END IF;
1970
1971 IF NOT TC_FOUND
1972 THEN
1973 RAISE NO_TIMECARD_FOUND;
1974 END IF;
1975
1976 IF p_attribute_array.COUNT > 0
1977 THEN
1978 l_index := p_attribute_array.FIRST;
1979 LOOP
1980 IF ABS(p_attribute_array(l_index).time_attribute_id) > l_att_id
1981 THEN
1982 l_att_id := p_attribute_array(l_index).time_attribute_id;
1983 END IF;
1984 l_index := p_attribute_array.NEXT(l_index);
1985 EXIT WHEN NOT p_attribute_array.EXISTS(l_index);
1986 END LOOP;
1987 ELSE
1988 l_att_id := 0;
1989 END IF;
1990
1991
1992 g_messages := HXC_MESSAGE_TABLE_TYPE();
1993
1994 retrieve_absences( p_person_id,
1995 p_start_date,
1996 p_end_date,
1997 l_abs_tab );
1998
1999 -- Bug 8854684
2000 -- Added this construct to NULL out blocks and attribute table
2001 -- and return if there was an error in processing abs.
2002 IF g_pending_appr
2003 AND g_messages.COUNT >0
2004 THEN
2005 p_block_array := HXC_BLOCK_TABLE_TYPE();
2006 p_attribute_array := HXC_ATTRIBUTE_TABLE_TYPE();
2007 RETURN;
2008 END IF;
2009
2010
2011 l_att_id := l_att_id * offset + offset;
2012 l_det_id := l_det_id * offset + offset;
2013
2014
2015 IF l_abs_tab.COUNT > 0
2016 THEN
2017 FOR l_index IN l_abs_tab.FIRST..l_abs_tab.LAST
2018 LOOP
2019 << CONTINUE_TO_NEXT>>
2020 LOOP
2021 IF NOT l_abs_tab.EXISTS(l_index)
2022 THEN
2023 EXIT CONTINUE_TO_NEXT;
2024 END IF;
2025 p_attribute_array.EXTEND(1);
2026 p_block_array.EXTEND(1);
2027 l_block_ind := p_block_array.LAST;
2028 l_att_ind := p_attribute_array.LAST;
2029 p_block_array(l_block_ind) := get_detail_block(l_abs_tab(l_index).abs_start,
2030 l_abs_tab(l_index).abs_end,
2031 l_abs_tab(l_index).duration,
2032 p_person_id,
2033 p_approval_style_id,
2034 l_day_id_tab(FND_DATE.DATE_TO_CANONICAL(l_abs_tab(l_index).abs_date)),
2035 l_det_id,
2036 1,
2037 TRUNC(hr_general.end_of_time));
2038 p_attribute_array(l_att_ind) := get_attribute_for_detail(l_det_id,
2039 1,
2040 l_abs_tab(l_index).element_type_id,
2041 l_abs_tab(l_index).abs_attendance_id,
2042 l_att_id );
2043
2044 IF l_abs_tab(l_index).abs_start IS NULL
2045 THEN
2046 l_UOM := 'D';
2047 ELSE
2048 l_uom := 'H';
2049 END IF;
2050
2051 -- Bug 8854684
2052 -- Corrected this call to record_co_absences.
2053 IF l_abs_tab(l_index).transaction_id IS NULL
2054 THEN
2055 -- Bug 8995913
2056 -- Added confirmed flag in parameters.
2057 record_carried_over_absences(l_det_id,
2058 1,
2059 l_abs_tab(l_index).abs_type_id,
2060 l_abs_tab(l_index).abs_attendance_id,
2061 l_abs_tab(l_index).element_type_id,
2062 NVL(l_abs_tab(l_index).abs_start,l_abs_tab(l_index).abs_date),
2063 NVL(l_abs_tab(l_index).abs_end,l_abs_tab(l_index).abs_date),
2064 l_uom,
2065 l_abs_tab(l_index).duration,
2066 'PREP',
2067 p_person_id,
2068 p_start_date,
2069 p_end_date,
2070 p_lock_rowid,
2071 NULL,
2072 NULL,
2073 l_abs_tab(l_index).confirmed_flag );
2074 ELSE
2075 -- Bug 8941273
2076 -- Added abs_attendance Id also.
2077 -- Bug 8995913
2078 -- Added confirmed flag in parameters.
2079 record_carried_over_absences(l_det_id,
2080 1,
2081 l_abs_tab(l_index).abs_type_id,
2082 l_abs_tab(l_index).abs_attendance_id,
2083 l_abs_tab(l_index).element_type_id,
2084 NVL(l_abs_tab(l_index).abs_start,l_abs_tab(l_index).abs_date),
2085 NVL(l_abs_tab(l_index).abs_end,l_abs_tab(l_index).abs_date),
2086 l_uom,
2087 l_abs_tab(l_index).duration,
2088 'PREP-SS',
2089 p_person_id,
2090 p_start_date,
2091 p_end_date,
2092 p_lock_rowid,
2093 l_abs_tab(l_index).transaction_id,
2094 l_abs_tab(l_index).modetype,
2095 l_abs_tab(l_index).confirmed_flag );
2096 END IF;
2097
2098 l_block_ind := l_block_ind + 1;
2099 l_att_ind := l_att_ind + 1;
2100 l_att_id := l_att_id + offset;
2101 l_det_id := l_det_id + offset;
2102
2103 EXIT CONTINUE_TO_NEXT;
2104 END LOOP CONTINUE_TO_NEXT;
2105 END LOOP;
2106
2107 HXC_ALIAS_TRANSLATOR.do_retrieval_translation( p_attributes => p_attribute_array
2108 ,p_blocks => p_block_array
2109 ,p_start_time => p_start_date
2110 ,p_stop_time => p_end_date
2111 ,p_resource_id => p_person_id
2112 ,p_messages => l_messages );
2113 END IF;
2114
2115 END add_abs_to_tc;
2116
2117
2118 -- Bug 8995913
2119 -- Added confirmed flag as p_conf
2120
2121 PROCEDURE record_carried_over_absences( p_bb_id IN NUMBER,
2122 p_bb_ovn IN NUMBER,
2123 p_abs_id IN NUMBER,
2124 p_abs_att_id IN NUMBER,
2125 p_element IN NUMBER,
2126 p_start_date IN DATE,
2127 p_end_date IN DATE,
2128 p_uom IN VARCHAR2,
2129 p_measure IN NUMBER,
2130 p_stage IN VARCHAR2,
2131 p_resource_id IN NUMBER,
2132 p_tc_start IN DATE,
2133 p_tc_stop IN DATE,
2134 p_lock_rowid IN VARCHAR2,
2135 p_transaction_id IN NUMBER DEFAULT NULL,
2136 p_action IN VARCHAR2 DEFAULT NULL,
2137 p_conf IN VARCHAR2 DEFAULT 'Y' )
2138 IS
2139
2140 PRAGMA AUTONOMOUS_TRANSACTION;
2141
2142 BEGIN
2143
2144
2145 INSERT INTO hxc_abs_co_details
2146 ( time_building_block_id ,
2147 object_version_number ,
2148 absence_type_id ,
2149 absence_attendance_id ,
2150 element_type_id ,
2151 uom ,
2152 measure ,
2153 start_date ,
2154 end_date ,
2155 stage ,
2156 sessionid ,
2157 co_date ,
2158 lock_rowid ,
2159 resource_id,
2160 start_time,
2161 stop_time,
2162 transaction_id,
2163 action,
2164 confirmed_flag )
2165 VALUES ( p_bb_id ,
2166 p_bb_ovn ,
2167 p_abs_id ,
2168 p_abs_att_id ,
2169 p_element ,
2170 p_uom ,
2171 p_measure ,
2172 p_start_date ,
2173 p_end_date ,
2174 p_stage ,
2175 USERENV('SESSIONID'),
2176 SYSDATE,
2177 p_lock_rowid,
2178 p_resource_id,
2179 p_tc_start,
2180 p_tc_stop,
2181 p_transaction_id,
2182 p_action,
2183 p_conf );
2184
2185 COMMIT;
2186
2187 END record_carried_over_absences;
2188
2189
2190
2191 PROCEDURE update_co_absences(p_old_bb_id IN NUMBER,
2192 p_new_bb_id IN NUMBER,
2193 p_start_time IN DATE,
2194 p_stop_time IN DATE,
2195 p_element_id IN NUMBER)
2196 IS
2197
2198 l_rowid VARCHAR2(50);
2199 l_start NUMBER;
2200 l_stop NUMBER;
2201 l_element NUMBER;
2202 l_transaction_id NUMBER;
2203
2204 BEGIN
2205
2206 IF g_debug
2207 THEN
2208 hr_utility.trace('g_lock_rowid '||g_lock_row_id);
2209 hr_utility.trace('g_person_id '||g_person_id);
2210 hr_utility.trace('g_start_time '||g_start_time);
2211 hr_utility.trace('g_stop_time '||g_stop_time);
2212 hr_utility.trace('p_old_bb_id '||p_old_bb_id);
2213 END IF;
2214
2215 -- Bug 8859290
2216 -- Removed lock_rowid condition.
2217
2218 SELECT ROWIDTOCHAR(ROWID),
2219 TO_CHAR(start_date,'hh24miss'),
2220 TO_CHAR(end_date,'hh24miss'),
2221 element_type_id,
2222 transaction_id
2223 INTO l_rowid,
2224 l_start,
2225 l_stop,
2226 l_element,
2227 l_transaction_id
2228 FROM hxc_abs_co_details
2229 WHERE time_building_block_id = p_old_bb_id
2230 AND resource_id = g_person_id
2231 AND start_time = g_start_time
2232 AND stage IN ('PREP','DEP','PREP-SS')
2233 AND TRUNC(stop_time) = TRUNC(g_stop_time);
2234
2235 UPDATE hxc_abs_co_details
2236 SET time_building_block_id = p_new_bb_id,
2237 stage = 'DEP'
2238 WHERE ROWID = CHARTOROWID(l_rowid);
2239
2240 -- Bug 8859290
2241 -- Put correct date format parameter below.
2242 IF l_start <> 0
2243 OR l_stop <> 0
2244 THEN
2245 IF l_start <> TO_CHAR(p_start_time,'hh24miss')
2246 OR l_stop <> TO_CHAR(p_stop_time,'hh24miss')
2247 THEN
2248 IF g_debug
2249 THEN
2250 hr_utility.trace('ABS : l_start '||l_start);
2251 hr_utility.trace('ABS : l_stop '||l_stop);
2252 END IF;
2253 RETURN;
2254 END IF;
2255 END IF;
2256
2257 IF l_element <> p_element_id
2258 THEN
2259 RETURN;
2260 END IF;
2261
2262 IF l_transaction_id IS NOT NULL
2263 THEN
2264 RETURN;
2265 END IF;
2266
2267 g_detail_trans_tab(p_new_bb_id) := 1;
2268
2269 EXCEPTION
2270 WHEN NO_DATA_FOUND
2271 THEN NULL;
2272
2273 END update_co_absences;
2274
2275
2276 PROCEDURE update_co_absences_ovn(p_old_bb_id IN hxc_time_building_blocks.time_building_block_id%type,
2277 p_new_ovn IN NUMBER,
2278 p_start_time IN DATE,
2279 p_stop_time IN DATE,
2280 p_element_id IN NUMBER )
2281 IS
2282
2283 l_rowid VARCHAR2(50);
2284 l_start NUMBER;
2285 l_stop NUMBER;
2286 l_element NUMBER;
2287 l_action VARCHAR2(50);
2288
2289
2290 BEGIN
2291
2292 IF g_debug
2293 THEN
2294 hr_utility.trace('g_lock_rowid '||g_lock_row_id);
2295 hr_utility.trace('g_person_id '||g_person_id);
2296 hr_utility.trace('g_start_time '||g_start_time);
2297 hr_utility.trace('g_stop_time '||g_stop_time);
2298 hr_utility.trace('p_old_bb_id '||p_old_bb_id);
2299 END IF;
2300
2301 SELECT ROWIDTOCHAR(ROWID),
2302 TO_CHAR(start_date,'hh24miss'),
2303 TO_CHAR(end_date,'hh24miss'),
2304 element_type_id,
2305 action
2306 INTO l_rowid,
2307 l_start,
2308 l_stop,
2309 l_element,
2310 l_action
2311 FROM hxc_abs_co_details
2312 WHERE time_building_block_id = p_old_bb_id
2313 AND stage IN ('PREP','DEP');
2314
2315
2316 UPDATE hxc_abs_co_details
2317 SET object_version_number = p_new_ovn,
2318 stage = 'DEP'
2319 WHERE ROWID = chartorowid(l_rowid);
2320
2321 IF l_start <> 0
2322 OR l_stop <> 0
2323 THEN
2324 IF l_start <> TO_CHAR(p_start_time,'hh24miss')
2325 OR l_stop <> TO_CHAR(p_stop_time,'hh24miss')
2326 THEN
2327 RETURN;
2328 END IF;
2329 END IF;
2330
2331 IF l_element <> p_element_id
2332 THEN
2333 RETURN;
2334 END IF;
2335
2336 IF l_action IS NOT NULL
2337 THEN
2338 RETURN;
2339 END IF;
2340
2341 g_detail_trans_tab(p_old_bb_id) := p_new_ovn;
2342
2343 EXCEPTION
2344 WHEN NO_DATA_FOUND
2345 THEN NULL;
2346
2347 END update_co_absences_ovn;
2348
2349
2350 PROCEDURE delete_other_sessions ( p_resource_id IN NUMBER,
2351 p_start_time IN DATE,
2352 p_stop_time IN DATE,
2353 p_lock_rowid IN VARCHAR2)
2354 IS
2355
2356 PRAGMA AUTONOMOUS_TRANSACTION;
2357
2358 BEGIN
2359
2360 /*
2361
2362 DELETE FROM hxc_abs_co_details
2363 WHERE resource_id = p_resource_id
2364 AND stage IN ( 'PREP','PREP-SS');
2365 */
2366
2367 DELETE FROM hxc_abs_co_details
2368 WHERE resource_id = p_resource_id
2369 AND start_time = p_start_time
2370 AND TRUNC(stop_time) = TRUNC(p_stop_time);
2371
2372
2373
2374 COMMIT;
2375
2376 END delete_other_sessions ;
2377
2378
2379 PROCEDURE insert_audit_header ( p_resource_id IN NUMBER,
2380 p_start_time IN DATE,
2381 p_stop_time IN DATE,
2382 p_transaction_id IN OUT NOCOPY NUMBER)
2383 IS
2384
2385 l_transaction_id NUMBER;
2386 l_retrieval_process_id NUMBER;
2387
2388 CURSOR c_transaction_sequence
2389 IS SELECT hxc_transactions_s.NEXTVAL
2390 FROM DUAL;
2391
2392 BEGIN
2393
2394 SELECT RETRIEVAL_PROCESS_ID
2395 INTO l_RETRIEVAL_PROCESS_ID
2396 FROM hxc_retrieval_processes
2397 WHERE name = 'BEE Retrieval Process';
2398
2399 OPEN c_transaction_sequence;
2400 FETCH c_transaction_sequence INTO l_transaction_id;
2401 CLOSE c_transaction_sequence;
2402
2403
2404 INSERT INTO hxc_transactions
2405 (transaction_id
2406 ,transaction_date
2407 ,type
2408 ,transaction_process_id
2409 ,created_by
2410 ,creation_date
2411 ,last_updated_by
2412 ,last_update_date
2413 ,last_update_login
2414 ,status
2415 ,exception_description
2416 ) VALUES
2417 (l_transaction_id
2418 ,SYSDATE
2419 ,'RETRIEVAL'
2420 ,l_RETRIEVAL_PROCESS_ID
2421 ,NULL
2422 ,SYSDATE
2423 ,NULL
2424 ,SYSDATE
2425 ,NULL
2426 ,'SUCCESS'
2427 ,'This prepopulated transaction is already present in HR'
2428 );
2429
2430 p_transaction_id := l_transaction_id;
2431
2432
2433 END insert_audit_header;
2434
2435
2436
2437
2438 PROCEDURE insert_audit_details ( p_resource_id IN NUMBER,
2439 p_detail_bb_id IN NUMBER,
2440 p_detail_ovn IN NUMBER,
2441 p_header_id IN NUMBER)
2442 IS
2443
2444 l_index NUMBER;
2445
2446 CURSOR c_transaction_detail_sequence
2447 IS SELECT hxc_transaction_details_s.NEXTVAL
2448 FROM DUAL;
2449
2450 l_transaction_detail_id hxc_transaction_details.transaction_detail_id%TYPE;
2451
2452 Begin
2453
2454 l_index := g_detail_trans_tab.first;
2455
2456 LOOP
2457 EXIT WHEN NOT g_detail_trans_tab.exists(l_index);
2458
2459
2460 OPEN c_transaction_detail_sequence;
2461 FETCH c_transaction_detail_sequence into l_transaction_detail_id;
2462 CLOSE c_transaction_detail_sequence;
2463
2464 INSERT INTO hxc_transaction_details
2465 (transaction_detail_id
2466 ,time_building_block_id
2467 ,transaction_id
2468 ,created_by
2469 ,creation_date
2470 ,last_updated_by
2471 ,last_update_date
2472 ,last_update_login
2473 ,time_building_block_ovn
2474 ,status
2475 ,exception_description
2476 ) VALUES
2477 (l_transaction_detail_id
2478 ,l_index
2479 ,p_header_id
2480 ,NULL
2481 ,SYSDATE
2482 ,NULL
2483 ,SYSDATE
2484 ,NULL
2485 ,g_detail_trans_tab(l_index)
2486 ,'SUCCESS'
2487 ,'This is a prepopulated record and is already present in HR'
2488 );
2489
2490 l_index := g_detail_trans_tab.next(l_index);
2491
2492 END LOOP;
2493
2494 END insert_audit_details;
2495
2496
2497
2498 PROCEDURE manage_retrieval_audit (p_resource_id IN NUMBER,
2499 p_start_time IN DATE,
2500 p_stop_time IN DATE)
2501
2502 IS
2503
2504 l_transaction_id NUMBER;
2505
2506 PROCEDURE clear_pending_notifications(p_resource_id IN NUMBER,
2507 p_start_time IN DATE,
2508 p_stop_time IN DATE)
2509 IS
2510
2511 CURSOR get_notifications
2512 IS SELECT transaction_id,
2513 ROWIDTOCHAR(abs.ROWID)
2514 FROM hxc_abs_co_details abs
2515 WHERE resource_id = p_resource_id
2516 AND start_time = p_start_time
2517 AND stop_time = p_stop_time
2518 AND transaction_id IS NOT NULL;
2519
2520 l_trans_tab NUMTABLE;
2521 l_rowid_tab VARCHARTABLE;
2522
2523 BEGIN
2524 OPEN get_notifications;
2525 FETCH get_notifications BULK COLLECT INTO l_trans_tab,
2526 l_rowid_tab;
2527 CLOSE get_notifications;
2528
2529 IF l_trans_tab.COUNT > 0
2530 THEN
2531 l_trans_tab := SET(l_trans_tab);
2532 FOR i IN l_trans_tab.FIRST..l_trans_tab.LAST
2533 LOOP
2534 BEGIN
2535 IF g_debug
2536 THEN
2537 hr_utility.trace('ABS : Calling delete_absences_in_tt ');
2538 hr_utility.trace('ABS: Deleting trans '||l_trans_tab(i));
2539 END IF;
2540
2541 hr_person_absence_swi.delete_absences_in_tt(l_trans_tab(i));
2542
2543 EXCEPTION
2544 WHEN NO_DATA_FOUND THEN
2545 NULL;
2546 END;
2547 END LOOP;
2548
2549 FORALL i IN l_rowid_tab.FIRST..l_rowid_tab.LAST
2550 UPDATE hxc_abs_co_details
2551 SET transaction_id = NULL
2552 WHERE ROWID = CHARTOROWID(l_rowid_tab(i));
2553
2554 END IF;
2555
2556 END clear_pending_notifications;
2557
2558 BEGIN
2559 IF g_detail_trans_tab.COUNT > 0
2560 THEN
2561 insert_audit_header(p_resource_id,
2562 p_start_time,
2563 p_stop_time,
2564 l_transaction_id);
2565
2566 insert_audit_details ( p_resource_id => p_resource_id,
2567 p_detail_bb_id => 1,
2568 p_detail_ovn => 1,
2569 p_header_id => l_transaction_id);
2570
2571 END IF;
2572 clear_pending_notifications(p_resource_id => p_resource_id,
2573 p_start_time => p_start_time,
2574 p_stop_time => p_stop_time);
2575 END manage_retrieval_audit;
2576
2577
2578 PROCEDURE verify_view_only_absences( p_blocks IN HXC_BLOCK_TABLE_TYPE,
2579 p_attributes IN HXC_ATTRIBUTE_TABLE_TYPE,
2580 p_lock_rowid IN VARCHAR2,
2581 p_messages IN OUT NOCOPY HXC_MESSAGE_TABLE_TYPE)
2582 IS
2583
2584 CURSOR get_edit_status(p_element_type IN NUMBER)
2585 IS SELECT edit_flag,uom
2586 FROM hxc_absence_type_elements
2587 WHERE element_type_id = p_element_type;
2588
2589
2590 -- Bug 8829122
2591 -- Added condition for Stage in the below
2592 -- cursor to avoid RET records.
2593 CURSOR get_abs_data(p_lock_rowid IN VARCHAR2,
2594 p_start_time IN DATE,
2595 p_stop_time IN DATE,
2596 p_resource_id IN NUMBER )
2597 IS
2598 SELECT time_building_block_id,
2599 element_type_id,
2600 DECODE(uom,'D',measure,NULL) measure,
2601 DECODE(uom,'H',fnd_date.date_to_canonical(start_date),NULL) start_time,
2602 DECODE(uom,'H',fnd_date.date_to_canonical(end_date),NULL) stop_time,
2603 TRUNC(start_date) abs_date,
2604 'N' validated
2605 FROM hxc_abs_co_details
2606 WHERE start_time = p_start_time
2607 AND resource_id = p_resource_id
2608 AND TRUNC(stop_time) = TRUNC(p_stop_time)
2609 AND stage IN ( 'PREP-SS', 'PREP','DEP');
2610
2611
2612 -- Bug 8886949
2613 -- Replaced lock rowid with resource id to avoid lock rowid issues.
2614 CURSOR get_pending_absences(p_resource_id IN NUMBER,
2615 p_start_time IN DATE,
2616 p_stop_time IN DATE)
2617 IS SELECT time_building_block_id,
2618 action
2619 FROM hxc_abs_co_details
2620 WHERE resource_id = p_resource_id
2621 AND start_time = p_start_time
2622 AND TRUNC(stop_time) = TRUNC(p_stop_time)
2623 AND stage = 'PREP-SS';
2624
2625
2626 -- Bug 8995913
2627 -- Added the below cursor for picking up absences pending
2628 -- confirmation.
2629 -- Bug 9018288
2630 -- Added conditions to check in PER table and remove
2631 -- SSHR related transactions.
2632
2633 CURSOR get_pending_conf(p_resource_id IN NUMBER,
2634 p_start_time IN DATE,
2635 p_stop_time IN DATE)
2636 IS SELECT /*+ LEADING(hxc) */
2637 time_building_block_id
2638 FROM hxc_abs_co_details hxc,
2639 per_absence_attendances per
2640 WHERE resource_id = p_resource_id
2641 AND start_time = p_start_time
2642 AND TRUNC(stop_time) = TRUNC(p_stop_time)
2643 AND stage IN ( 'PREP-SS','PREP', 'DEP')
2644 AND confirmed_flag = 'N'
2645 AND transaction_id IS NULL
2646 AND action IS NULL
2647 AND per.absence_attendance_id = NVL(hxc.absence_attendance_id,0)
2648 AND per.date_start IS NULL
2649 AND per.date_end IS NULL ;
2650
2651
2652 CURSOR get_pending_absence_validation(p_resource_id IN NUMBER,
2653 p_start_time IN DATE,
2654 p_stop_time IN DATE)
2655 IS SELECT time_building_block_id,
2656 element_type_id,
2657 DECODE(uom,'D',measure,NULL) measure,
2658 DECODE(uom,'H',fnd_date.date_to_canonical(start_date),NULL) start_time,
2659 DECODE(uom,'H',fnd_date.date_to_canonical(end_date),NULL) stop_time,
2660 TRUNC(start_date) abs_date,
2661 'N' validated
2662 FROM hxc_abs_co_details
2663 WHERE resource_id = p_resource_id
2664 AND start_time = p_start_time
2665 AND TRUNC(stop_time) = TRUNC(p_stop_time)
2666 AND stage in ('DEP','PREP-SS')
2667 AND absence_attendance_id IS NULL;
2668
2669
2670 TYPE ABSTAB IS TABLE OF get_abs_data%ROWTYPE INDEX BY BINARY_INTEGER;
2671 l_usertab ABSTAB;
2672 l_cotab ABSTAB;
2673 l_valtab ABSTAB;
2674 l_pend_tab ABSTAB;
2675 l_abs_tot_tab ABSTAB; -- Added for Bug 8888601
2676
2677 TYPE DAYTAB IS TABLE OF DATE INDEX BY BINARY_INTEGER;
2678 l_daytab DAYTAB;
2679
2680
2681 TYPE YESNOTAB IS TABLE OF VARCHAR2(5) INDEX BY BINARY_INTEGER;
2682 l_edit_tab YESNOTAB;
2683 l_uom_tab YESNOTAB;
2684 l_element VARCHAR2(5);
2685 l_uom VARCHAR2(5);
2686 l_bb_id NUMBER;
2687 l_bb_ovn NUMBER;
2688 l_start_time DATE;
2689 l_stop_time DATE;
2690 l_resource_id NUMBER;
2691 l_measure NUMBER;
2692 l_exists NUMBER;
2693 l_date_to DATE;
2694
2695
2696 i NUMBER;
2697 j NUMBER;
2698 bb_index BINARY_INTEGER;
2699 l_edit_prep VARCHAR2(5);
2700
2701 l_pref_table hxc_preference_evaluation.t_pref_table;
2702 l_message VARCHAR2(50);
2703
2704
2705 l_validation_error BOOLEAN := FALSE;
2706 l_pa_abs NUMBER := 0;
2707 l_edit_pa VARCHAR2(50);
2708 l_bb_tab NUMTABLE;
2709 l_act_tab VARCHARTABLE;
2710
2711
2712 l_hr_val_action VARCHAR2(500);
2713 l_val_level VARCHAR2(500);
2714
2715 l_pend_conf_action VARCHAR2(50);
2716
2717 l_abs_name_tab ALIAS_TAB;
2718
2719 --- ADDED FOR OTL Absence Integration 8888902
2720 -- OTL-ABS START
2721 l_precision varchar2(4);
2722 l_rounding_rule varchar2(80);
2723 l_pref_eval_date date;
2724 l_emp_hire_date date;
2725
2726 l_abs_days NUMBER;
2727 l_abs_hours NUMBER;
2728
2729 cursor emp_hire_info(p_resource_id hxc_time_building_blocks.resource_id%TYPE) IS
2730 select date_start from per_periods_of_service where person_id=p_resource_id order by date_start desc;
2731
2732 -- OTL-ABS START
2733
2734 -- Bug 9019114
2735 -- Added the below type and variables to
2736 -- hold the OVN and Element type id for each building block
2737 -- in p_blocks array.
2738 TYPE NUMBERTAB IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
2739
2740 l_ovn_tab NUMBERTAB;
2741 l_ele_tab NUMBERTAB;
2742
2743
2744 -- Bug 8855103
2745 -- Added the below function to return name of absence for the
2746 -- error token.
2747
2748 FUNCTION get_abs_name(p_abs_id IN NUMBER)
2749 RETURN VARCHAR2
2750 IS
2751
2752 l_name VARCHAR2(500) := 'ERROR';
2753 CURSOR get_name (p_abs_id IN NUMBER)
2754 IS SELECT name
2755 FROM per_abs_attendance_types_tl
2756 WHERE absence_attendance_type_id = p_abs_id
2757 AND language = USERENV('LANG');
2758
2759
2760 BEGIN
2761 IF l_abs_name_tab.EXISTS(p_abs_id)
2762 THEN
2763 RETURN l_abs_name_tab(p_abs_id);
2764 ELSE
2765 OPEN get_name(p_abs_id);
2766 FETCH get_name INTO l_name;
2767 CLOSE get_name;
2768 l_abs_name_tab(p_abs_id) := l_name;
2769 RETURN l_name;
2770 END IF;
2771
2772 END get_abs_name;
2773
2774
2775 PROCEDURE add_error_to_tc( p_messages IN OUT NOCOPY HXC_MESSAGE_TABLE_TYPE,
2776 p_error_code IN VARCHAR2,
2777 p_bb_id IN NUMBER,
2778 p_error_level IN VARCHAR2 DEFAULT hxc_timecard.c_error,
2779 p_token IN VARCHAR2 DEFAULT NULL)
2780 IS
2781
2782 BEGIN
2783 hxc_timecard_message_helper.addErrorToCollection
2784 (p_messages
2785 ,p_error_code
2786 ,p_error_level
2787 ,null
2788 ,p_token
2789 ,hxc_timecard.c_hxc
2790 ,p_bb_id
2791 ,1
2792 ,null
2793 ,null
2794 );
2795 -- Bug 8945924
2796 -- Corrected the below condition to '='
2797 -- so that the flag is set only if there was an error.
2798 IF p_error_level = hxc_timecard.c_error
2799 THEN
2800 l_validation_error := TRUE;
2801 END IF;
2802 END add_error_to_tc;
2803
2804
2805 -- Bug 9019114
2806 -- Added this new procedure to pick restrict switching of
2807 -- Hours type for an already retrieved detail.
2808 PROCEDURE restrict_attribute_switch
2809 IS
2810
2811 l_ind BINARY_INTEGER;
2812 l_retrieved NUMBER;
2813 l_attribute NUMBER;
2814
2815
2816 CURSOR get_transactions( p_bb_id IN NUMBER,
2817 p_bb_ovn IN NUMBER)
2818 IS SELECT htd.time_building_block_ovn
2819 FROM hxc_transaction_details htd,
2820 hxc_transactions ht
2821 WHERE htd.time_building_block_id = p_bb_id
2822 AND htd.time_building_block_ovn <= p_bb_ovn
2823 AND htd.transaction_id = ht.transaction_id
2824 AND ht.type = 'RETRIEVAL'
2825 AND ht.transaction_process_id = g_bee_retrieval
2826 AND htd.status = 'SUCCESS'
2827 ORDER BY time_building_block_id DESC;
2828
2829 CURSOR pick_old_attribute(p_bb_id IN NUMBER,
2830 p_bb_ovn IN NUMBER)
2831 IS SELECT REPLACE(hta.attribute_category,'ELEMENT - ')
2832 FROM hxc_time_attribute_usages hau,
2833 hxc_time_attributes hta
2834 WHERE hau.time_building_block_id = p_bb_id
2835 AND hau.time_building_block_ovn = p_bb_ovn
2836 AND hta.time_attribute_id = hau.time_attribute_id
2837 AND hta.bld_blk_info_type_id = g_bld_blk_info;
2838
2839 l_old_exists BOOLEAN;
2840 l_new_exists BOOLEAN;
2841 l_count NUMBER;
2842
2843 CURSOR pick_abs_type(p_element IN NUMBER)
2844 IS SELECT 1
2845 FROM hxc_absence_type_elements
2846 WHERE element_type_id = p_element;
2847
2848
2849 BEGIN
2850 l_ind := l_ovn_tab.FIRST;
2851 LOOP
2852 EXIT WHEN NOT l_ovn_tab.EXISTS(l_ind);
2853 OPEN get_transactions(l_ind,l_ovn_tab(l_ind));
2854 FETCH get_transactions
2855 INTO l_retrieved;
2856 IF get_transactions%FOUND
2857 THEN
2858 OPEN pick_old_attribute(l_ind,l_retrieved);
2859 FETCH pick_old_attribute
2860 INTO l_attribute;
2861 CLOSE pick_old_attribute;
2862 IF l_attribute <> l_ele_tab(l_ind)
2863 THEN
2864 OPEN pick_abs_type(l_attribute);
2865 FETCH pick_abs_type
2866 INTO l_count;
2867 IF pick_abs_type%FOUND
2868 THEN
2869 l_old_exists := TRUE;
2870 ELSE
2871 l_old_exists := FALSE;
2872 END IF;
2873 CLOSE pick_abs_type;
2874
2875 OPEN pick_abs_type(l_ele_tab(l_ind));
2876 FETCH pick_abs_type
2877 INTO l_count;
2878 IF pick_abs_type%FOUND
2879 THEN
2880 l_new_exists := TRUE;
2881 ELSE
2882 l_new_exists := FALSE;
2883 END IF;
2884 CLOSE pick_abs_type;
2885 IF ( l_new_exists = TRUE AND l_old_exists = FALSE)
2886 OR (l_new_exists = FALSE AND l_old_exists = TRUE )
2887 THEN
2888 add_error_to_tc(p_messages,
2889 'HXC_ABS_NO_ATTRIBUTE_CHANGE',
2890 l_ind);
2891 END IF;
2892 END IF;
2893 END IF;
2894 CLOSE get_transactions;
2895 l_ind := l_ovn_tab.NEXT(l_ind);
2896 END LOOP;
2897
2898 END restrict_attribute_switch;
2899
2900
2901
2902
2903
2904 -- Bug 8855103
2905 -- Added the below functions to do recipient update validations.
2906
2907 PROCEDURE validate_overlap_absences( p_messages IN OUT NOCOPY HXC_MESSAGE_TABLE_TYPE,
2908 p_usertab IN ABSTAB,
2909 p_resource_id IN NUMBER,
2910 p_start_time IN DATE,
2911 p_stop_time IN DATE,
2912 p_val_level IN VARCHAR2)
2913 IS
2914
2915 TYPE DATETAB IS TABLE OF DATE ;
2916 abs_datetab DATETAB;
2917 sshr_datetab DATETAB;
2918 l_starttab DATETAB;
2919 l_endtab DATETAB;
2920 l_total NUMBER;
2921 i BINARY_INTEGER;
2922 l_valid BOOLEAN := FALSE;
2923
2924 -- Bug 14593332
2925 -- Modified the cursor to not pick up Abs details
2926 -- which are posted from OTL.
2927 -- While checking overlap, we dont want to check absences
2928 -- twice from OTL and from HR.
2929 CURSOR get_absences
2930 IS SELECT NVL(date_start,date_projected_start),
2931 NVL(date_end,date_projected_end)
2932 FROM per_absence_attendances abs
2933 WHERE person_id = p_resource_id
2934 AND NVL(date_start,date_projected_start) <= p_stop_time
2935 AND NVL(date_end,date_projected_end) >= p_start_time
2936 AND NOT EXISTS ( SELECT /* INDEX(tc HXC_ABS_CO_DETAILS_FK4) */
2937 1
2938 FROM hxc_abs_co_details tc
2939 WHERE tc.absence_attendance_id = abs.absence_attendance_id
2940 AND tc.stage = 'RET'
2941 AND tc.resource_id = p_resource_id
2942 AND tc.start_time = p_start_time);
2943
2944 CURSOR get_pending_abs
2945 IS SELECT distinct TRUNC(start_date)
2946 FROM hxc_abs_co_details
2947 WHERE resource_id = p_resource_id
2948 AND start_time = p_start_time
2949 AND TRUNC(stop_time) = TRUNC(p_stop_time)
2950 AND stage in ('DEP','PREP-SS')
2951 AND absence_attendance_id IS NULL;
2952
2953
2954 BEGIN
2955
2956 IF g_debug
2957 THEN
2958 i := p_usertab.FIRST;
2959 LOOP
2960 hr_utility.trace('User bbid is '||i);
2961 hr_utility.trace('User Element '||p_usertab(i).element_type_id);
2962 hr_utility.trace('User measure '||p_usertab(i).measure);
2963 hr_utility.trace('User Start_time '||p_usertab(i).start_time);
2964 i := p_usertab.NEXT(i);
2965 EXIT WHEN NOT p_usertab.EXISTS(i);
2966 END LOOP;
2967 END IF;
2968
2969 abs_datetab := DATETAB();
2970 i := p_usertab.FIRST;
2971 LOOP
2972 abs_datetab.EXTEND(1);
2973 j := abs_datetab.LAST;
2974 abs_datetab(j) := p_usertab(i).abs_date;
2975 i := p_usertab.NEXT(i);
2976 EXIT WHEN NOT p_usertab.EXISTS(i);
2977 END LOOP;
2978
2979 OPEN get_pending_abs;
2980 FETCH get_pending_abs BULK COLLECT INTO sshr_datetab;
2981 CLOSE get_pending_abs;
2982 IF sshr_datetab IS NOT EMPTY
2983 THEN
2984 abs_datetab := abs_datetab MULTISET UNION sshr_datetab;
2985 END IF;
2986
2987 IF abs_datetab IS NOT A SET
2988 THEN
2989 hr_utility.trace('ABS:Its not equal -- there is a detail overlap ');
2990 add_error_to_tc(p_messages,
2991 'HXC_ABS_DET_OVERLAP',
2992 NULL,
2993 p_val_level);
2994 END IF;
2995
2996 l_total := abs_datetab.COUNT;
2997
2998 IF g_debug
2999 THEN
3000 FOR j IN abs_datetab.FIRST..abs_datetab.LAST
3001 LOOP
3002 hr_utility.trace('ABS:orig -'||abs_datetab(j));
3003 END LOOP;
3004 END IF;
3005 abs_datetab := SET(abs_datetab);
3006
3007 IF g_debug
3008 THEN
3009 FOR j IN abs_datetab.FIRST..abs_datetab.LAST
3010 LOOP
3011 hr_utility.trace('ABS:Changed -'||abs_datetab(j));
3012 END LOOP;
3013 END IF;
3014
3015
3016 OPEN get_absences;
3017 FETCH get_absences BULK COLLECT INTO l_starttab,
3018 l_endtab;
3019 CLOSE get_absences;
3020
3021 IF l_starttab IS NOT EMPTY
3022 THEN
3023 FOR i in l_starttab.FIRST..l_starttab.LAST
3024 LOOP
3025 FOR j in abs_datetab.FIRST..abs_datetab.LAST
3026 LOOP
3027 IF abs_datetab(j) BETWEEN l_starttab(i)
3028 AND l_endtab(i)
3029 THEN
3030 hr_utility.trace('ABS:There is a problem -- Overlap with HR');
3031 add_error_to_tc(p_messages,
3032 'HXC_ABS_HR_OVERLAP',
3033 NULL,
3034 p_val_level);
3035 l_valid := TRUE;
3036 EXIT;
3037 END IF;
3038 END LOOP;
3039 IF l_valid
3040 THEN
3041 EXIT;
3042 END IF;
3043 END LOOP ;
3044 END IF;
3045
3046
3047 END validate_overlap_absences;
3048
3049
3050
3051 PROCEDURE validate_run_totals_and_pto ( p_messages IN OUT NOCOPY HXC_MESSAGE_TABLE_TYPE,
3052 p_usertab IN ABSTAB,
3053 p_pendtab IN ABSTAB,
3054 p_resource_id IN NUMBER,
3055 p_start_time IN DATE,
3056 p_stop_time IN DATE,
3057 p_val_level IN VARCHAR2)
3058 IS
3059
3060 l_abs_type_id NUMBER;
3061 TYPE TOTTABLE IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
3062 l_tottab TOTTABLE;
3063 l_asg NUMBER;
3064 l_bal NUMBER;
3065
3066 -- Bug 9359368
3067 -- New array to hold Abs Inc or Dec balance type
3068 TYPE RUNTABLE IS TABLE OF VARCHAR2(5) INDEX BY BINARY_INTEGER;
3069 l_runtab RUNTABLE;
3070
3071 BEGIN
3072
3073 l_asg := get_assignment_id(p_resource_id,
3074 p_start_time);
3075
3076 hr_utility.trace('ABS:l_asg '||l_asg);
3077
3078 IF TO_CHAR(p_start_time,'yyyy') = TO_CHAR(p_stop_time,'yyyy')
3079 THEN
3080
3081
3082 i := p_usertab.FIRST;
3083 LOOP
3084 EXIT WHEN NOT p_usertab.EXISTS(i);
3085 IF NOT l_tottab.EXISTS(get_absence_id(p_usertab(i).element_type_id))
3086 THEN
3087 l_tottab(get_absence_id(p_usertab(i).element_type_id))
3088 := NVL(p_usertab(i).measure,(FND_DATE.canonical_to_date(p_usertab(i).stop_time)
3089 -FND_DATE.canonical_to_date(p_usertab(i).start_time))*24);
3090 ELSE
3091 l_tottab(get_absence_id(p_usertab(i).element_type_id))
3092 := l_tottab(get_absence_id(p_usertab(i).element_type_id)) +
3093 NVL(p_usertab(i).measure,(FND_DATE.canonical_to_date(p_usertab(i).stop_time
3094 )
3095 - FND_DATE.canonical_to_date(p_usertab(i).start_time
3096 )
3097 )*24
3098 );
3099
3100 END IF;
3101 -- Bug 9359368
3102 -- Find out Inc or Dec balance type
3103 IF NOT l_runtab.EXISTS(get_absence_id(p_usertab(i).element_type_id))
3104 THEN
3105 l_runtab(get_absence_id(p_usertab(i).element_type_id)) :=
3106 get_abs_running(p_usertab(i).element_type_id);
3107 END IF;
3108 i := p_usertab.NEXT(i);
3109 END LOOP;
3110
3111
3112
3113 i := p_pendtab.FIRST;
3114 LOOP
3115 EXIT WHEN NOT p_pendtab.EXISTS(i);
3116 IF NOT l_tottab.EXISTS(get_absence_id(p_pendtab(i).element_type_id))
3117 THEN
3118 l_tottab(get_absence_id(p_pendtab(i).element_type_id))
3119 := NVL(p_pendtab(i).measure,(FND_DATE.canonical_to_date(p_pendtab(i).stop_time)
3120 -FND_DATE.canonical_to_date(p_pendtab(i).start_time))*24);
3121 ELSE
3122 l_tottab(get_absence_id(p_pendtab(i).element_type_id))
3123 := l_tottab(get_absence_id(p_pendtab(i).element_type_id)) +
3124 NVL(p_pendtab(i).measure,(FND_DATE.canonical_to_date(p_pendtab(i).stop_time
3125 )
3126 - FND_DATE.canonical_to_date(p_pendtab(i).start_time
3127 )
3128 )*24
3129 );
3130
3131 END IF;
3132 -- Bug 9359368
3133 -- Find out Inc or Dec balance type
3134 IF NOT l_runtab.EXISTS(get_absence_id(p_pendtab(i).element_type_id))
3135 THEN
3136 l_runtab(get_absence_id(p_pendtab(i).element_type_id)) :=
3137 get_abs_running(p_pendtab(i).element_type_id);
3138 END IF;
3139
3140 i := p_pendtab.NEXT(i);
3141 END LOOP;
3142
3143
3144
3145
3146 i := l_tottab.FIRST;
3147 LOOP
3148 EXIT WHEN NOT l_tottab.EXISTS(i);
3149 IF g_debug
3150 THEN
3151 hr_utility.trace(' l_total for '||i);
3152 END IF;
3153 l_bal := per_absence_attendances_pkg.get_annual_balance(p_start_time,
3154 i,
3155 l_asg);
3156 IF g_debug
3157 THEN
3158 hr_utility.trace(' l_balance '||l_bal);
3159 hr_utility.trace(' l_tottab(i) '||l_tottab(i));
3160 END IF;
3161 IF l_bal < l_tottab(i)
3162 AND l_runtab(i) = 'D' -- Bug 9359368
3163 THEN
3164 hr_utility.trace('ABS:There is a problem with running total ');
3165 add_error_to_tc(p_messages,
3166 'HXC_ABS_RUN_TOTAL',
3167 NULL,
3168 p_val_level,
3169 'ABS&'||get_abs_name(i));
3170 END IF;
3171
3172
3173 IF NOT per_absence_attendances_pkg.is_emp_entitled (i,
3174 l_asg,
3175 p_start_time,
3176 l_tottab(i),
3177 l_tottab(i))
3178 THEN
3179 hr_utility.trace('There is a problem with pto ');
3180 add_error_to_tc(p_messages,
3181 'HXC_ABS_PTO_NOT_ENTITLED',
3182 NULL,
3183 p_val_level,
3184 'ABS&'||get_abs_name(i));
3185 END IF;
3186
3187
3188 i := l_tottab.NEXT(i);
3189 END LOOP;
3190
3191
3192
3193 ELSE
3194 hr_utility.trace('ABS : Year crossing over in the timecard period ');
3195
3196 i := p_usertab.FIRST;
3197 LOOP
3198 EXIT WHEN NOT p_usertab.EXISTS(i);
3199 IF TO_CHAR(p_usertab(i).abs_date,'YYYY') = TO_CHAR(p_start_time,'YYYY')
3200 THEN
3201 IF NOT l_tottab.EXISTS(get_absence_id(p_usertab(i).element_type_id))
3202 THEN
3203 l_tottab(get_absence_id(p_usertab(i).element_type_id))
3204 := NVL(p_usertab(i).measure,(FND_DATE.canonical_to_date(p_usertab(i).stop_time)
3205 -FND_DATE.canonical_to_date(p_usertab(i).start_time))*24);
3206 ELSE
3207 l_tottab(get_absence_id(p_usertab(i).element_type_id))
3208 := l_tottab(get_absence_id(p_usertab(i).element_type_id)) +
3209 NVL(p_usertab(i).measure,(FND_DATE.canonical_to_date(p_usertab(i).stop_time
3210 )
3211 - FND_DATE.canonical_to_date(p_usertab(i).start_time
3212 )
3213 )*24
3214 );
3215
3216 END IF;
3217 END IF;
3218 i := p_usertab.NEXT(i);
3219 END LOOP;
3220
3221 i := p_pendtab.FIRST;
3222 LOOP
3223 EXIT WHEN NOT p_pendtab.EXISTS(i);
3224 IF TO_CHAR(p_pendtab(i).abs_date,'YYYY') = TO_CHAR(p_start_time,'YYYY')
3225 THEN
3226
3227 IF NOT l_tottab.EXISTS(get_absence_id(p_pendtab(i).element_type_id))
3228 THEN
3229 l_tottab(get_absence_id(p_pendtab(i).element_type_id))
3230 := NVL(p_pendtab(i).measure,(FND_DATE.canonical_to_date(p_pendtab(i).stop_time)
3231 -FND_DATE.canonical_to_date(p_pendtab(i).start_time))*24);
3232 ELSE
3233 l_tottab(get_absence_id(p_pendtab(i).element_type_id))
3234 := l_tottab(get_absence_id(p_pendtab(i).element_type_id)) +
3235 NVL(p_pendtab(i).measure,(FND_DATE.canonical_to_date(p_pendtab(i).stop_time
3236 )
3237 - FND_DATE.canonical_to_date(p_pendtab(i).start_time
3238 )
3239 )*24
3240 );
3241
3242 END IF;
3243 END IF;
3244 i := p_pendtab.NEXT(i);
3245 END LOOP;
3246
3247 i := l_tottab.FIRST;
3248 LOOP
3249 EXIT WHEN NOT l_tottab.EXISTS(i);
3250 IF g_debug
3251 THEN
3252 hr_utility.trace(' l_total for '||i);
3253 END IF;
3254 l_bal := per_absence_attendances_pkg.get_annual_balance(p_start_time,
3255 i,
3256 l_asg);
3257 IF g_debug
3258 THEN
3259 hr_utility.trace(' l_balance '||l_bal);
3260 hr_utility.trace(' l_tottab(i) '||l_tottab(i));
3261 END IF;
3262 IF l_bal < l_tottab(i)
3263 AND l_runtab(i) = 'D' -- Bug 9359368
3264 THEN
3265 hr_utility.trace('ABS:There is a problem with running total ');
3266 add_error_to_tc(p_messages,
3267 'HXC_ABS_RUN_TOTAL',
3268 NULL,
3269 p_val_level,
3270 'ABS&'||get_abs_name(i));
3271 END IF;
3272
3273
3274 IF NOT per_absence_attendances_pkg.is_emp_entitled (i,
3275 l_asg,
3276 p_start_time,
3277 l_tottab(i),
3278 l_tottab(i))
3279 THEN
3280 hr_utility.trace('There is a problem with pto ');
3281 add_error_to_tc(p_messages,
3282 'HXC_ABS_PTO_NOT_ENTITLED',
3283 NULL,
3284 p_val_level,
3285 'ABS&'||get_abs_name(i));
3286 END IF;
3287
3288
3289 i := l_tottab.NEXT(i);
3290 END LOOP;
3291
3292 l_tottab.DELETE;
3293
3294 i := p_usertab.FIRST;
3295 LOOP
3296 EXIT WHEN NOT p_usertab.EXISTS(i);
3297 IF TO_CHAR(p_usertab(i).abs_date,'YYYY') = TO_CHAR(p_stop_time,'YYYY')
3298 THEN
3299 IF NOT l_tottab.EXISTS(get_absence_id(p_usertab(i).element_type_id))
3300 THEN
3301 l_tottab(get_absence_id(p_usertab(i).element_type_id))
3302 := NVL(p_usertab(i).measure,(FND_DATE.canonical_to_date(p_usertab(i).stop_time)
3303 -FND_DATE.canonical_to_date(p_usertab(i).start_time))*24);
3304 ELSE
3305 l_tottab(get_absence_id(p_usertab(i).element_type_id))
3306 := l_tottab(get_absence_id(p_usertab(i).element_type_id)) +
3307 NVL(p_usertab(i).measure,(FND_DATE.canonical_to_date(p_usertab(i).stop_time
3308 )
3309 - FND_DATE.canonical_to_date(p_usertab(i).start_time
3310 )
3311 )*24
3312 );
3313
3314 END IF;
3315 END IF;
3316 i := p_usertab.NEXT(i);
3317 END LOOP;
3318
3319 i := p_pendtab.FIRST;
3320 LOOP
3321 EXIT WHEN NOT p_pendtab.EXISTS(i);
3322 IF TO_CHAR(p_pendtab(i).abs_date,'YYYY') = TO_CHAR(p_stop_time,'YYYY')
3323 THEN
3324
3325 IF NOT l_tottab.EXISTS(get_absence_id(p_pendtab(i).element_type_id))
3326 THEN
3327 l_tottab(get_absence_id(p_pendtab(i).element_type_id))
3328 := NVL(p_pendtab(i).measure,(FND_DATE.canonical_to_date(p_pendtab(i).stop_time)
3329 -FND_DATE.canonical_to_date(p_pendtab(i).start_time))*24);
3330 ELSE
3331 l_tottab(get_absence_id(p_pendtab(i).element_type_id))
3332 := l_tottab(get_absence_id(p_pendtab(i).element_type_id)) +
3333 NVL(p_pendtab(i).measure,(FND_DATE.canonical_to_date(p_pendtab(i).stop_time
3334 )
3335 - FND_DATE.canonical_to_date(p_pendtab(i).start_time
3336 )
3337 )*24
3338 );
3339
3340 END IF;
3341 END IF;
3342 i := p_pendtab.NEXT(i);
3343 END LOOP;
3344
3345 i := l_tottab.FIRST;
3346 LOOP
3347 EXIT WHEN NOT l_tottab.EXISTS(i);
3348 IF g_debug
3349 THEN
3350 hr_utility.trace(' l_total for '||i);
3351 END IF;
3352 l_bal := per_absence_attendances_pkg.get_annual_balance(p_stop_time,
3353 i,
3354 l_asg);
3355 IF g_debug
3356 THEN
3357 hr_utility.trace(' l_balance '||l_bal);
3358 hr_utility.trace(' l_tottab(i) '||l_tottab(i));
3359 END IF;
3360 IF l_bal < l_tottab(i)
3361 AND l_runtab(i) = 'D' -- Bug 9359368
3362 THEN
3363 hr_utility.trace('ABS:There is a problem with running total ');
3364 add_error_to_tc(p_messages,
3365 'HXC_ABS_RUN_TOTAL',
3366 NULL,
3367 p_val_level,
3368 'ABS&'||get_abs_name(i));
3369 END IF;
3370
3371
3372 IF NOT per_absence_attendances_pkg.is_emp_entitled (i,
3373 l_asg,
3374 p_stop_time,
3375 l_tottab(i),
3376 l_tottab(i))
3377 THEN
3378 hr_utility.trace('There is a problem with pto ');
3379 add_error_to_tc(p_messages,
3380 'HXC_ABS_PTO_NOT_ENTITLED',
3381 NULL,
3382 p_val_level,
3383 'ABS&'||get_abs_name(i));
3384 END IF;
3385
3386
3387 i := l_tottab.NEXT(i);
3388 END LOOP;
3389
3390
3391
3392
3393 END IF;
3394
3395
3396
3397
3398 END validate_run_totals_and_pto;
3399
3400
3401
3402
3403
3404
3405
3406 BEGIN
3407
3408 IF p_attributes.COUNT = 0
3409 THEN
3410 RETURN ;
3411 END IF;
3412
3413
3414
3415 IF g_debug
3416 THEN
3417 hr_utility.trace('p_lock_rowid '||p_lock_rowid);
3418 i := p_blocks.FIRST;
3419 LOOP
3420 hr_utility.trace('p_blocks(i).time_building_block_id '||p_blocks(i).time_building_block_id);
3421 hr_utility.trace('p_blocks(i).object_version_number '||p_blocks(i).object_version_number);
3422 hr_utility.trace('p_blocks(i).scope '||p_blocks(i).scope);
3423 hr_utility.trace('p_blocks(i).start_time '||p_blocks(i).start_time);
3424 hr_utility.trace('p_blocks(i).stop_time '||p_blocks(i).stop_time);
3425 hr_utility.trace('p_blocks(i).measure '||p_blocks(i).measure);
3426 hr_utility.trace('p_blocks(i).date_to '||p_blocks(i).date_to);
3427 i:= p_blocks.NEXT(i);
3428 EXIT WHEN NOT p_blocks.EXISTS(i);
3429 END LOOP;
3430
3431 i:= p_attributes.FIRST;
3432 LOOP
3433 hr_utility.trace('p_attributes(i).building_block_id '||p_attributes(i).building_block_id);
3434 hr_utility.trace('p_attributes(i).building_block_ovn '||p_attributes(i).building_block_ovn);
3435 hr_utility.trace('p_attributes(i).attribute_category '||p_attributes(i).attribute_category);
3436 hr_utility.trace('p_attributes(i).bld_blk_info_type '||p_attributes(i).bld_blk_info_type);
3437 i:= p_attributes.NEXT(i);
3438 EXIT WHEN NOT p_blocks.EXISTS(i);
3439 END LOOP;
3440
3441 END IF;
3442
3443 IF l_usertab.COUNT >0
3444 THEN
3445 l_usertab.DELETE;
3446 END IF;
3447
3448
3449 -- gather absence types from p_blocks
3450
3451 i := p_blocks.FIRST;
3452 LOOP
3453 << TO_CONTINUE_TO_NEXT_BLOCK >>
3454 LOOP
3455 IF p_blocks(i).scope = 'TIMECARD'
3456 -- Bug 8858587
3457 -- Dont do any of these in case the timecard is going to be deleted.
3458 AND FND_DATE.canonical_to_date(p_blocks(i).DATE_TO) = hr_general.end_of_time
3459 THEN
3460 hxc_preference_evaluation.resource_preferences(p_blocks(i).resource_id,
3461 FND_DATE.canonical_to_date(p_blocks(i).start_time),
3462 FND_DATE.canonical_to_date(p_blocks(i).start_time),
3463 l_pref_table);
3464 l_start_time := FND_DATE.canonical_to_date(p_blocks(i).start_time);
3465 l_stop_time := FND_DATE.canonical_to_date(p_blocks(i).stop_time);
3466 l_resource_id := p_blocks(i).resource_id;
3467 EXIT TO_CONTINUE_TO_NEXT_BLOCK;
3468 END IF;
3469 IF g_debug
3470 THEN
3471 hr_utility.trace('p_blocks(i).time_building_block_id '||p_blocks(i).time_building_block_id);
3472 hr_utility.trace('p_blocks(i).date_to '||p_blocks(i).date_to);
3473 hr_utility.trace('p_blocks(i).scope '||p_blocks(i).scope);
3474 END IF;
3475
3476 IF p_blocks(i).scope = 'DAY'
3477 THEN
3478 l_daytab(p_blocks(i).time_building_block_id) := TRUNC(fnd_date.canonical_to_date(p_blocks(i).start_time));
3479 EXIT TO_CONTINUE_TO_NEXT_BLOCK;
3480 END IF;
3481
3482 IF p_blocks(i).SCOPE <> 'DETAIL'
3483 OR FND_DATE.canonical_to_date(p_blocks(i).DATE_TO) <> hr_general.end_of_time
3484 THEN
3485 EXIT TO_CONTINUE_TO_NEXT_BLOCK;
3486 END IF;
3487 bb_index := p_blocks(i).time_building_block_id;
3488 l_usertab(bb_index).time_building_block_id := bb_index;
3489 l_usertab(bb_index).measure := p_blocks(i).measure;
3490 l_usertab(bb_index).start_time := p_blocks(i).start_time;
3491 l_usertab(bb_index).stop_time := p_blocks(i).stop_time;
3492 l_usertab(bb_index).abs_date := l_daytab(p_blocks(i).parent_building_block_id);
3493 l_usertab(bb_index).validated := 'N';
3494 -- Bug 9019114
3495 l_ovn_tab(bb_index) := p_blocks(i).object_version_number;
3496 EXIT TO_CONTINUE_TO_NEXT_BLOCK;
3497 END LOOP TO_CONTINUE_TO_NEXT_BLOCK;
3498 i := p_blocks.NEXT(i);
3499 EXIT WHEN NOT p_blocks.EXISTS(i);
3500 END LOOP;
3501
3502
3503 IF l_pref_table.COUNT = 0
3504 THEN
3505 hr_utility.trace('ABS Either no preferences or this is a template. Return');
3506 RETURN;
3507 END IF;
3508
3509 i := l_pref_table.FIRST;
3510 LOOP
3511 IF l_pref_table(i).preference_code = 'TS_ABS_PREFERENCES'
3512 THEN
3513 l_edit_prep := l_pref_table(i).attribute2;
3514 l_edit_pa := l_pref_table(i).attribute3;
3515 l_hr_val_action := l_pref_table(i).attribute6;
3516 l_pend_conf_action := l_pref_table(i).attribute7;
3517 IF l_pref_table(i).attribute1 <> 'Y'
3518 THEN
3519 IF g_debug
3520 THEN
3521 hr_utility.trace('Abs Integration disabled for this employee ');
3522 END IF;
3523 -- Bug 8886949
3524 -- Need to return when integration preference is set to NO.
3525 RETURN;
3526 END IF;
3527 EXIT;
3528 END IF;
3529 i := l_pref_table.NEXT(i);
3530 EXIT WHEN NOT l_pref_table.EXISTS(i);
3531 END LOOP;
3532
3533
3534 -- Bug 8886949
3535 -- Replaced lock rowid with resource id so that querying happens fine.
3536 OPEN get_pending_absences(l_resource_id,
3537 l_start_time,
3538 l_stop_time);
3539
3540 FETCH get_pending_absences BULK COLLECT INTO l_bb_tab,l_act_tab;
3541 CLOSE get_pending_absences;
3542
3543 IF l_bb_tab.COUNT > 0
3544 THEN
3545 FOR i IN l_bb_tab.FIRST..l_bb_tab.LAST
3546 LOOP
3547 IF l_edit_pa = 'ERROR'
3548 THEN
3549 add_error_to_tc(p_messages,
3550 'HXC_ABS_PEND_APPR_ERROR',
3551 l_bb_tab(i));
3552 ELSE
3553 add_error_to_tc(p_messages,
3554 'HXC_ABS_PEND_APPR_WARNING',
3555 NULL,
3556 hxc_timecard.c_warning);
3557 EXIT;
3558 END IF;
3559 END LOOP;
3560
3561 END IF;
3562
3563 -- Bug 8995913
3564 -- Added validation for absences pending Confirmation..
3565
3566 OPEN get_pending_conf(l_resource_id,
3567 l_start_time,
3568 l_stop_time);
3569
3570 FETCH get_pending_conf BULK COLLECT INTO l_bb_tab;
3571 CLOSE get_pending_conf;
3572
3573 IF l_bb_tab.COUNT > 0
3574 THEN
3575 FOR i IN l_bb_tab.FIRST..l_bb_tab.LAST
3576 LOOP
3577 IF l_pend_conf_action = 'ERROR'
3578 THEN
3579 add_error_to_tc(p_messages,
3580 'HXC_ABS_PEND_CONF_ERROR',
3581 l_bb_tab(i));
3582 ELSE
3583 add_error_to_tc(p_messages,
3584 'HXC_ABS_PEND_CONF_WARNING',
3585 NULL,
3586 hxc_timecard.c_warning);
3587 EXIT;
3588 END IF;
3589 END LOOP;
3590
3591 END IF;
3592
3593
3594
3595 i := p_attributes.FIRST;
3596 LOOP
3597 << TO_CONTINUE_TO_NEXT_ATTRIBUTE >>
3598 LOOP
3599 IF p_attributes(i).bld_blk_info_type <> 'Dummy Element Context'
3600 THEN
3601 EXIT TO_CONTINUE_TO_NEXT_ATTRIBUTE;
3602 END IF;
3603
3604 -- Bug 9019114
3605 -- Added the below construct to copy the element type id
3606 -- of a bb-id, OVN combo, which is of OVN > 1.
3607 IF l_ovn_tab.EXISTS(p_attributes(i).building_block_id)
3608 THEN
3609 l_ele_tab(p_attributes(i).building_block_id) := REPLACE(p_attributes(i).attribute_category,'ELEMENT - ');
3610 END IF;
3611
3612 IF NOT l_edit_tab.EXISTS(REPLACE(p_attributes(i).attribute_category,'ELEMENT - '))
3613 THEN
3614 OPEN get_edit_status(REPLACE(p_attributes(i).attribute_category,'ELEMENT - '));
3615 FETCH get_edit_status INTO l_element,l_uom;
3616 IF get_edit_status%NOTFOUND
3617 THEN
3618 CLOSE get_edit_status;
3619 l_usertab.DELETE(p_attributes(i).building_block_id);
3620 EXIT TO_CONTINUE_TO_NEXT_ATTRIBUTE;
3621 END IF;
3622 CLOSE get_edit_status;
3623 l_edit_tab(REPLACE(p_attributes(i).attribute_category,'ELEMENT - ')) := l_element;
3624 l_uom_tab(REPLACE(p_attributes(i).attribute_category,'ELEMENT - ')) := l_uom;
3625 END IF;
3626 IF l_usertab.EXISTS(p_attributes(i).building_block_id)
3627 THEN
3628 l_usertab(p_attributes(i).building_block_id).element_type_id :=
3629 REPLACE(p_attributes(i).attribute_category,'ELEMENT - ');
3630 END IF;
3631 EXIT TO_CONTINUE_TO_NEXT_ATTRIBUTE;
3632 END LOOP TO_CONTINUE_TO_NEXT_ATTRIBUTE;
3633 i := p_attributes.NEXT(i);
3634 EXIT WHEN NOT p_attributes.EXISTS(i);
3635 END LOOP;
3636
3637 -- Bug 9019114
3638 -- Calling the below procedures to populate global variables
3639 -- and limit switching of hours type in case the details are
3640 -- already retrieved.
3641 populate_globals;
3642 restrict_attribute_switch;
3643
3644
3645 IF g_debug
3646 THEN
3647 hr_utility.trace('l_start_time '||l_start_time);
3648 hr_utility.trace('p_lock_rowid '||p_lock_rowid);
3649 hr_utility.trace('l_stop_time '||TO_CHAR(l_stop_time,'dd-mon-yyyy hh:mi:ss'));
3650 END IF;
3651
3652 IF l_cotab.COUNT >0
3653 THEN
3654 l_cotab.DELETE;
3655 END IF;
3656
3657 OPEN get_abs_data(p_lock_rowid,
3658 l_start_time,
3659 l_stop_time,
3660 l_resource_id);
3661 FETCH get_abs_data BULK COLLECT INTO l_cotab;
3662 CLOSE get_abs_data;
3663
3664
3665 -- Bug 8910881
3666 -- Added the below construct to work out
3667 -- situations where the only one row is deleted.
3668 IF l_cotab.COUNT >0
3669 AND l_usertab.COUNT = 0
3670 AND l_edit_prep = 'Y'
3671 THEN
3672 IF g_debug
3673 THEN
3674 hr_utility.trace('ABS: There is a validation prob');
3675 END IF;
3676 j := l_cotab.FIRST;
3677 LOOP
3678 IF NOT l_edit_tab.EXISTS(l_cotab(j).element_type_id)
3679 THEN
3680 OPEN get_edit_status(l_cotab(j).element_type_id);
3681 FETCH get_edit_status INTO l_element,l_uom;
3682 CLOSE get_edit_status;
3683 l_edit_tab(l_cotab(j).element_type_id) := l_element;
3684 l_uom_tab(l_cotab(j).element_type_id) := l_uom;
3685 END IF;
3686 IF l_element = 'N'
3687 THEN
3688 IF g_debug
3689 THEN
3690 hr_utility.trace('ABS: There is an error added');
3691 END IF;
3692 add_error_to_tc(p_messages,
3693 'HXC_ABS_VIEW_ONLY_NO_DELETE',
3694 l_cotab(j).time_building_block_id);
3695 END IF;
3696 j := l_cotab.NEXT(j);
3697 EXIT WHEN NOT l_cotab.EXISTS(j);
3698 END LOOP;
3699 END IF;
3700
3701
3702 -- Bug 8858587
3703 -- Added the condition to check if there are any
3704 -- prepop rows and pref to edit these is set to NO.
3705
3706 IF l_usertab.COUNT = 0
3707 THEN
3708 IF l_cotab.COUNT > 0
3709 AND l_edit_prep = 'N'
3710 THEN
3711 add_error_to_tc(p_messages,
3712 'HXC_ABS_NO_EDIT_PREP',
3713 NULL);
3714 END IF;
3715
3716 IF g_debug
3717 THEN
3718 hr_utility.trace('No active details in this timecard, Return ');
3719 END IF;
3720 RETURN;
3721 END IF;
3722
3723 IF l_edit_tab.COUNT = 0
3724 THEN
3725 IF g_debug
3726 THEN
3727 hr_utility.trace('No abs elements in this timecard, Return ');
3728 END IF;
3729 RETURN ;
3730 END IF;
3731
3732
3733 IF g_debug
3734 THEN
3735 i := l_edit_tab.FIRST;
3736 LOOP
3737 IF g_debug
3738 THEN
3739 hr_utility.trace('Element is '||i);
3740 hr_utility.trace('Edit is '||l_edit_tab(i));
3741 END IF;
3742 i := l_edit_tab.NEXT(i);
3743 EXIT WHEN NOT l_edit_tab.EXISTS(i);
3744 END LOOP;
3745
3746 i := l_usertab.FIRST;
3747 LOOP
3748 IF g_debug
3749 THEN
3750 hr_utility.trace('User bbid is '||i);
3751 hr_utility.trace('User Element '||l_usertab(i).element_type_id);
3752 hr_utility.trace('User measure '||l_usertab(i).measure);
3753 hr_utility.trace('User Start_time '||l_usertab(i).start_time);
3754 END IF;
3755
3756 i := l_usertab.NEXT(i);
3757 EXIT WHEN NOT l_usertab.EXISTS(i);
3758 END LOOP;
3759 IF g_debug
3760 THEN
3761 IF l_cotab.COUNT > 0
3762 THEN
3763 i := l_cotab.FIRST;
3764 LOOP
3765 hr_utility.trace('Co bbid is '||i);
3766 hr_utility.trace('CoElement '||l_cotab(i).element_type_id);
3767 hr_utility.trace('Comeasure '||l_cotab(i).measure);
3768 hr_utility.trace('CoStart_time '||l_cotab(i).start_time);
3769 hr_utility.trace('Co time bb id '||l_cotab(i).time_building_block_id);
3770 hr_utility.trace('Co Start_time '||l_cotab(i).start_time);
3771 i := l_cotab.NEXT(i);
3772 EXIT WHEN NOT l_cotab.EXISTS(i);
3773 END LOOP;
3774 END IF;
3775 END IF;
3776
3777 END IF;
3778
3779 -- Bug 8945994
3780 -- Added the below construct to avoid a later exception.
3781 -- If there are no attributes, raise an error and return.
3782 IF l_usertab.COUNT > 0
3783 THEN
3784 i := l_usertab.FIRST;
3785 LOOP
3786 IF l_usertab(i).element_type_id IS NULL
3787 THEN
3788 add_error_to_tc (p_messages
3789 ,'HXC_DEP_VAL_NO_ATTR'
3790 ,l_usertab(i).time_building_block_id);
3791 END IF;
3792 i:= l_usertab.NEXT(i);
3793 EXIT WHEN NOT l_usertab.EXISTS(i);
3794 END LOOP;
3795 END IF;
3796
3797 IF l_validation_error
3798 THEN
3799 RETURN;
3800 END IF;
3801
3802
3803 l_valtab := l_usertab;
3804 l_abs_tot_tab := l_usertab; -- Added for Bug 8888601
3805
3806
3807 IF g_debug
3808 THEN
3809 hr_utility.trace('l_edit_prep is set to '||l_edit_prep);
3810 END IF;
3811
3812 IF l_cotab.COUNT > 0
3813 THEN
3814 j := l_cotab.FIRST;
3815 LOOP
3816 << CONTINUE_TO_NEXT >>
3817 LOOP
3818 IF g_debug
3819 THEN
3820 hr_utility.trace('CoElement '||l_cotab(j).element_type_id);
3821 hr_utility.trace('Comeasure '||l_cotab(j).measure);
3822 hr_utility.trace('CoStart_time '||l_cotab(j).start_time);
3823 hr_utility.trace('Co time bb id '||l_cotab(j).time_building_block_id);
3824 END IF;
3825
3826 IF NOT l_edit_tab.EXISTS(l_cotab(j).element_type_id)
3827 THEN
3828
3829 OPEN get_edit_status(l_cotab(j).element_type_id);
3830 FETCH get_edit_status INTO l_element,l_uom;
3831 CLOSE get_edit_status;
3832 l_edit_tab(l_cotab(j).element_type_id) := l_element;
3833 l_uom_tab(l_cotab(j).element_type_id) := l_uom;
3834 END IF;
3835
3836 IF l_valtab.EXISTS(l_cotab(j).time_building_block_id)
3837 THEN
3838 l_valtab.DELETE(l_cotab(j).time_building_block_id);
3839 END IF;
3840
3841 IF ( l_edit_prep = 'N'
3842 OR l_edit_tab(l_cotab(j).element_type_id) = 'N')
3843 THEN
3844 IF NOT l_usertab.EXISTS(l_cotab(j).time_building_block_id)
3845 THEN
3846 IF l_edit_prep = 'N'
3847 THEN
3848 l_message := 'HXC_ABS_NO_EDIT_PREP';
3849 ELSE
3850 l_message := 'HXC_ABS_VIEW_ONLY_NO_DELETE';
3851 END IF;
3852 -- Bug 8888488
3853 -- Passing NULL for building block id so that
3854 -- timekeeper has no issues in showing up the error.
3855 add_error_to_tc (p_messages
3856 ,l_message
3857 ,NULL);
3858 EXIT CONTINUE_TO_NEXT;
3859 END IF;
3860 IF NVL(l_cotab(j).measure,0) <> NVL(l_usertab(l_cotab(j).time_building_block_id).measure,0)
3861 OR NVL(l_cotab(j).start_time,TRUNC(sysdate)) <> NVL(l_usertab(l_cotab(j).time_building_block_id).start_time,TRUNC(SYSDATE))
3862 OR NVL(l_cotab(j).stop_time,TRUNC(SYSDATE)) <> NVL(l_usertab(l_cotab(j).time_building_block_id).stop_time,TRUNC(SYSDATE))
3863 -- Bug 8864161
3864 -- Added this condition to restrict hours type change also.
3865 OR NVL(l_cotab(j).element_type_id,0) <> NVL(l_usertab(l_cotab(j).time_building_block_id).element_type_id,0)
3866 THEN
3867 IF g_debug
3868 THEN
3869 hr_utility.trace( 'There is a mismatch in prepopulated ');
3870 END IF;
3871 IF l_edit_prep = 'N'
3872 THEN
3873 l_message := 'HXC_ABS_NO_EDIT_PREP';
3874 ELSE
3875 l_message := 'HXC_ABS_VIEW_ONLY';
3876 END IF;
3877 add_error_to_tc (p_messages
3878 ,l_message
3879 ,l_cotab(j).time_building_block_id);
3880 l_usertab.DELETE(l_cotab(j).time_building_block_id);
3881 EXIT CONTINUE_TO_NEXT;
3882 ELSE
3883 IF g_debug
3884 THEN
3885 hr_utility.trace( 'There is no mismatch, this is just like prepopulated ');
3886 END IF;
3887 l_usertab.DELETE(l_cotab(j).time_building_block_id);
3888 EXIT CONTINUE_TO_NEXT;
3889 END IF;
3890 END IF;
3891 EXIT CONTINUE_TO_NEXT;
3892 END LOOP CONTINUE_TO_NEXT;
3893 j := l_cotab.NEXT(j);
3894 EXIT WHEN NOT l_cotab.EXISTS(j);
3895 END LOOP;
3896
3897 END IF;
3898
3899 IF l_usertab.COUNT > 0
3900 THEN
3901 j := l_usertab.FIRST;
3902 LOOP
3903 IF l_edit_tab(l_usertab(j).element_type_id) = 'N'
3904 THEN
3905 add_error_to_tc ( p_messages
3906 ,'HXC_ABS_VIEW_ONLY'
3907 ,l_usertab(j).time_building_block_id );
3908 ELSIF l_uom_tab(l_usertab(j).element_type_id) = 'HOURS'
3909 AND ( l_usertab(j).start_time IS NULL
3910 OR l_usertab(j).stop_time IS NULL
3911 OR l_usertab(j).measure IS NOT NULL )
3912 THEN
3913 add_error_to_tc (p_messages
3914 ,'HXC_ABS_HOUR_FORMAT'
3915 ,l_usertab(j).time_building_block_id );
3916 ELSIF l_uom_tab(l_usertab(j).element_type_id) = 'DAYS'
3917 AND ( l_usertab(j).start_time IS NOT NULL
3918 OR l_usertab(j).stop_time IS NOT NULL
3919 OR l_usertab(j).measure <> 1 )
3920 THEN
3921 add_error_to_tc (p_messages
3922 ,'HXC_ABS_DAY_FORMAT'
3923 ,l_usertab(j).time_building_block_id );
3924 END IF;
3925 j := l_usertab.NEXT(j);
3926 EXIT WHEN NOT l_usertab.EXISTS(j);
3927 END LOOP;
3928 END IF;
3929
3930 IF l_validation_error
3931 THEN
3932 RETURN;
3933 END IF;
3934
3935 -- Bug 8855103
3936 -- Below code added for recipient update validation.
3937 OPEN get_pending_absence_validation(l_resource_id,
3938 l_start_time,
3939 l_stop_time);
3940
3941 FETCH get_pending_absence_validation BULK COLLECT INTO l_pend_tab;
3942 CLOSE get_pending_absence_validation;
3943
3944
3945 IF l_hr_val_action = 'ERROR'
3946 THEN
3947 l_val_level := hxc_timecard.c_error;
3948 ELSIF l_hr_val_action = 'WARNING'
3949 THEN
3950 l_val_level := hxc_timecard.c_warning;
3951 END IF;
3952
3953
3954 IF l_usertab.COUNT > 0
3955 -- Bug 8945994
3956 -- Added below check.
3957 AND l_valtab.COUNT > 0
3958 AND l_hr_val_action <> 'IGNORE'
3959 THEN
3960
3961
3962 validate_overlap_absences(p_messages,
3963 l_valtab,
3964 l_resource_id,
3965 l_start_time,
3966 l_stop_time,
3967 l_val_level);
3968
3969 validate_run_totals_and_pto(p_messages,
3970 l_valtab,
3971 l_pend_tab,
3972 l_resource_id,
3973 l_start_time,
3974 l_stop_time,
3975 l_val_level);
3976 END IF;
3977
3978 -- Added for OTL ABS Integration 8888902
3979 -- OTL-ABS START
3980
3981 OPEN emp_hire_info (l_resource_id);
3982 FETCH emp_hire_info INTO l_emp_hire_date;
3983 CLOSE emp_hire_info;
3984
3985 IF trunc(l_emp_hire_date) >= trunc(l_start_time)
3986 AND trunc(l_emp_hire_date) <= trunc(l_stop_time)
3987 THEN
3988 l_pref_eval_date := trunc(l_emp_hire_date);
3989 ELSE
3990 l_pref_eval_date := trunc(l_start_time);
3991 END IF;
3992
3993 l_precision := hxc_preference_evaluation.resource_preferences
3994 (l_resource_id,
3995 'TC_W_TCRD_UOM',
3996 3,
3997 l_pref_eval_date);
3998
3999
4000 l_rounding_rule := hxc_preference_evaluation.resource_preferences
4001 (l_resource_id,
4002 'TC_W_TCRD_UOM',
4003 4,
4004 l_pref_eval_date);
4005 IF l_precision IS NULL
4006 THEN
4007 l_precision := '2';
4008 END IF;
4009
4010 IF l_rounding_rule IS NULL
4011 THEN
4012 l_rounding_rule := 'ROUND_TO_NEAREST';
4013 END IF;
4014
4015 IF g_debug THEN
4016 hr_utility.trace('ABS> In hxc_retrieve_absences.verify_view_only_absences');
4017 hr_utility.trace('ABS> invoke logic to calculate absence hours and absence days');
4018 hr_utility.trace('ABS> l_precision ::'||l_precision);
4019 hr_utility.trace('ABS> l_rounding_rule ::'||l_rounding_rule);
4020 hr_utility.trace('ABS> l_abs_days ::'||l_abs_days);
4021 hr_utility.trace('ABS> l_abs_hours ::'||l_abs_hours);
4022 hr_utility.trace('ABS> l_abs_tot_tab.COUNT ::'||l_abs_tot_tab.COUNT);
4023 hr_utility.trace('ABS> p_messages.COUNT ::'||p_messages.COUNT);
4024 END IF;
4025
4026 l_abs_days := 0;
4027 l_abs_hours := 0;
4028
4029 IF l_abs_tot_tab.COUNT > 0
4030 THEN
4031 j := l_abs_tot_tab.FIRST;
4032 LOOP
4033
4034 IF g_debug THEN
4035 hr_utility.trace('ABS> In LOOP OF l_abs_tot_tab INDEX ::'||j);
4036 hr_utility.trace('ABS> l_uom_tab(l_abs_tot_tab(j).element_type_id) ::'
4037 ||l_uom_tab(l_abs_tot_tab(j).element_type_id));
4038 END IF;
4039
4040 IF l_uom_tab(l_abs_tot_tab(j).element_type_id) = 'DAYS'
4041 THEN
4042
4043 IF g_debug THEN
4044 hr_utility.trace('ABS> l_uom_tab(l_abs_tot_tab(j).element_type_id) ::'
4045 ||l_uom_tab(l_abs_tot_tab(j).element_type_id));
4046 hr_utility.trace('ABS> l_abs_tot_tab(j).measure ::'||l_abs_tot_tab(j).measure);
4047 END IF;
4048
4049 l_abs_days := l_abs_days +
4050 hxc_find_notify_aprs_pkg.apply_round_rule(
4051 l_rounding_rule,
4052 l_precision,
4053 nvl(l_abs_tot_tab(j).measure,0)
4054 );
4055 ELSIF l_uom_tab(l_abs_tot_tab(j).element_type_id) = 'HOURS'
4056 THEN
4057 IF g_debug THEN
4058 hr_utility.trace('ABS> l_uom_tab(l_abs_tot_tab(j).element_type_id) ::'
4059 ||l_uom_tab(l_abs_tot_tab(j).element_type_id));
4060 hr_utility.trace('ABS> l_abs_tot_tab(j).stop_time ::'||l_abs_tot_tab(j).stop_time);
4061 hr_utility.trace('ABS> l_abs_tot_tab(j).start_time ::'||l_abs_tot_tab(j).start_time);
4062 hr_utility.trace('ABS> FND_DATE.CANONICAL_TO_DATE(l_abs_tot_tab(j).stop_time) ::'
4063 ||FND_DATE.CANONICAL_TO_DATE(l_abs_tot_tab(j).stop_time));
4064 hr_utility.trace('ABS> FND_DATE.CANONICAL_TO_DATE(l_abs_tot_tab(j).start_time ::'
4065 ||FND_DATE.CANONICAL_TO_DATE(l_abs_tot_tab(j).start_time));
4066 END IF;
4067
4068 l_abs_hours := l_abs_hours +
4069 hxc_find_notify_aprs_pkg.apply_round_rule(
4070 l_rounding_rule,
4071 l_precision,
4072 nvl((FND_DATE.CANONICAL_TO_DATE(l_abs_tot_tab(j).stop_time) -
4073 FND_DATE.CANONICAL_TO_DATE(l_abs_tot_tab(j).start_time))*24,0)
4074 );
4075 END IF;
4076
4077 j := l_abs_tot_tab.NEXT(j);
4078 EXIT WHEN NOT l_abs_tot_tab.EXISTS(j);
4079
4080 END LOOP;
4081 END IF;
4082
4083 IF g_debug THEN
4084 hr_utility.trace('ABS> In hxc_retrieve_absences.verify_view_only_absences');
4085 hr_utility.trace('ABS> final values of absence hours and absence days');
4086 hr_utility.trace('ABS> l_resource_id ::'||l_resource_id);
4087 hr_utility.trace('ABS> l_start_time ::'||l_start_time);
4088 hr_utility.trace('ABS> l_stop_time ::'||l_stop_time);
4089 hr_utility.trace('ABS> l_abs_days ::'||l_abs_days);
4090 hr_utility.trace('ABS> l_abs_hours ::'||l_abs_hours);
4091 hr_utility.trace('ABS> now invoke update_absence_summary_row');
4092 END IF;
4093
4094 update_absence_summary_row(l_resource_id,
4095 l_start_time,
4096 l_stop_time,
4097 l_abs_days,
4098 l_abs_hours
4099 );
4100
4101 -- End of Code for Bug 8888902
4102
4103 RETURN;
4104
4105 END verify_view_only_absences;
4106
4107
4108 PROCEDURE clear_prev_sessions(p_resource_id IN NUMBER,
4109 p_tc_start IN DATE,
4110 p_tc_stop IN DATE,
4111 p_lock_rowid IN VARCHAR2)
4112 IS
4113
4114 PRAGMA AUTONOMOUS_TRANSACTION;
4115
4116 BEGIN
4117
4118 DELETE FROM hxc_abs_co_details
4119 WHERE resource_id = p_resource_id
4120 AND start_time = p_tc_start
4121 AND TRUNC(stop_time) = TRUNC(p_tc_stop);
4122
4123
4124 COMMIT;
4125
4126 END clear_prev_sessions;
4127
4128 -- Added for OTL ABS Integration 8888902
4129 -- OTL-ABS START
4130
4131 PROCEDURE insert_absence_summary_row
4132 IS
4133
4134 PRAGMA AUTONOMOUS_TRANSACTION;
4135
4136 l_abs_record_exist VARCHAR2(1) := 'Y';
4137 BEGIN
4138
4139 SELECT 'Y'
4140 INTO l_abs_record_exist
4141 FROM hxc_absence_summary_temp
4142 WHERE resource_id = hxc_retrieve_absences.g_person_id
4143 AND start_time = hxc_retrieve_absences.g_start_time
4144 AND stop_time = hxc_retrieve_absences.g_stop_time;
4145
4146 IF l_abs_record_exist = 'Y' THEN
4147 -- DO NOTHING
4148 return;
4149
4150 END IF;
4151
4152 EXCEPTION
4153 WHEN NO_DATA_FOUND THEN
4154
4155 INSERT INTO hxc_absence_summary_temp
4156 (resource_id
4157 ,start_time
4158 ,stop_time
4159 )
4160 VALUES
4161 (hxc_retrieve_absences.g_person_id
4162 ,hxc_retrieve_absences.g_start_time
4163 ,hxc_retrieve_absences.g_stop_time
4164 );
4165
4166 COMMIT;
4167 END insert_absence_summary_row;
4168
4169
4170 PROCEDURE update_absence_summary_row(p_resource_id IN NUMBER,
4171 p_tc_start IN DATE,
4172 p_tc_stop IN DATE,
4173 p_abs_days IN NUMBER,
4174 p_abs_hours IN NUMBER
4175 )
4176 IS
4177
4178 PRAGMA AUTONOMOUS_TRANSACTION;
4179
4180 BEGIN
4181
4182 UPDATE hxc_absence_summary_temp
4183 SET absence_days = p_abs_days,
4184 absence_hours = p_abs_hours
4185 WHERE resource_id = p_resource_id
4186 AND start_time = p_tc_start
4187 AND stop_time = p_tc_stop;
4188
4189 COMMIT;
4190 END update_absence_summary_row;
4191
4192
4193 PROCEDURE clear_absence_summary_rows
4194 IS
4195
4196 PRAGMA AUTONOMOUS_TRANSACTION;
4197
4198 BEGIN
4199
4200 DELETE FROM hxc_absence_summary_temp;
4201 COMMIT;
4202
4203 END clear_absence_summary_rows;
4204
4205 -- OTL-ABS END
4206
4207 -- Returns Lookup Meaning for a given lookup type and code
4208 FUNCTION get_lookup_value( p_lookup_type IN VARCHAR2,
4209 p_lookup_code IN VARCHAR2)
4210 RETURN VARCHAR2
4211 IS
4212
4213 CURSOR get_meaning(p_lookup_type IN VARCHAR2,
4214 p_lookup_code IN VARCHAR2)
4215 IS SELECT meaning
4216 FROM FND_LOOKUP_VALUES
4217 WHERE lookup_type = p_lookup_type
4218 AND language = USERENV('LANG')
4219 AND enabled_flag = 'Y'
4220 AND lookup_code = p_lookup_code;
4221
4222 l_meaning VARCHAR2(200);
4223
4224 BEGIN
4225
4226 OPEN get_meaning(p_lookup_type,
4227 p_lookup_code);
4228
4229 FETCH get_meaning INTO l_meaning;
4230
4231 CLOSE get_meaning;
4232
4233 IF l_meaning IS NULL
4234 THEN
4235 l_meaning := 'LOOKUP ERROR';
4236 END IF;
4237
4238 RETURN l_meaning;
4239
4240 END get_lookup_value;
4241
4242
4243 -- Bug 8911152
4244 -- Added this new function to create layout attributes.
4245
4246 FUNCTION get_layout_attribute ( p_bb_id IN NUMBER,
4247 p_bb_ovn IN NUMBER,
4248 p_attribute_id IN NUMBER)
4249 RETURN HXC_ATTRIBUTE_TYPE
4250 IS
4251
4252
4253 l_attribute_type HXC_ATTRIBUTE_TYPE;
4254 l_attribute1 NUMBER;
4255 l_attribute2 NUMBER;
4256 l_attribute3 NUMBER;
4257
4258 BEGIN
4259
4260 l_attribute_type := HXC_ATTRIBUTE_TYPE(NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
4261 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
4262 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
4263 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
4264
4265 IF g_pref_table.COUNT >0
4266 THEN
4267 FOR i IN g_pref_table.FIRST..g_pref_table.LAST
4268 LOOP
4269 IF g_pref_table(i).preference_code = 'TC_W_TCRD_LAYOUT'
4270 THEN
4271 l_attribute1 := g_pref_table(i).attribute1;
4272 l_attribute2 := g_pref_table(i).attribute2;
4273 l_attribute3 := g_pref_table(i).attribute3;
4274 IF g_debug
4275 THEN
4276 hr_utility.trace('ABS : Time entry layout '||l_attribute1);
4277 hr_utility.trace('ABS : Review layout '||l_attribute2);
4278 hr_utility.trace('ABS : Confirmation layout '||l_attribute3);
4279 END IF;
4280 EXIT;
4281 END IF;
4282 END LOOP;
4283 END IF;
4284
4285 l_attribute_type.time_attribute_id := p_attribute_id;
4286 l_attribute_type.attribute_category := 'LAYOUT';
4287 l_attribute_type.attribute1 := l_attribute1;
4288 l_attribute_type.attribute2 := l_attribute2;
4289 l_attribute_type.attribute3 := l_attribute3;
4290 l_attribute_type.bld_blk_info_type_id := g_layout_bbit;
4291 l_attribute_type.bld_blk_info_type := 'LAYOUT';
4292 l_attribute_type.BUILDING_BLOCK_ID := p_bb_id;
4293 l_attribute_type.BUILDING_BLOCK_OVN := p_bb_ovn;
4294 l_attribute_type.OBJECT_VERSION_NUMBER := 1;
4295
4296 RETURN l_attribute_type;
4297 END get_layout_attribute;
4298
4299
4300
4301
4302
4303 -- Bug 8855103
4304 -- Added the below code to handle multiple requests
4305 -- with respect to validations.
4306
4307 FUNCTION get_absence_details (p_element_type_id IN NUMBER)
4308 RETURN NUMBER
4309 IS
4310
4311 l_abs_type_id NUMBER;
4312 l_inc_dec VARCHAR2(2);
4313 l_name VARCHAR2(500);
4314 l_uom VARCHAR2(5);
4315
4316 BEGIN
4317 IF g_abs_id_tab.EXISTS(p_element_type_id)
4318 THEN
4319 RETURN g_abs_id_tab(p_element_type_id).abs_id;
4320 END IF;
4321
4322 SELECT hxc.absence_attendance_type_id,
4323 per.increasing_or_decreasing_flag,
4324 pertl.name,
4325 per.hours_or_days
4326 INTO l_abs_type_id,
4327 l_inc_dec,
4328 l_name,
4329 l_uom
4330 FROM hxc_absence_type_elements hxc,
4331 per_absence_attendance_types per,
4332 per_abs_attendance_types_tl pertl
4333 WHERE hxc.element_type_id = p_element_type_id
4334 AND hxc.absence_attendance_type_id = per.absence_attendance_type_id
4335 AND per.absence_attendance_type_id = pertl.absence_attendance_type_id
4336 AND pertl.language = USERENV('LANG');
4337
4338 g_abs_id_tab(p_element_type_id).abs_id := l_abs_type_id;
4339 g_abs_id_tab(p_element_type_id).abs_name := l_name;
4340 g_abs_id_tab(p_element_type_id).run_total := l_inc_dec;
4341 g_abs_id_tab(p_element_type_id).uom := l_uom;
4342 RETURN l_abs_type_id;
4343
4344 END get_absence_details;
4345
4346
4347
4348 FUNCTION get_absence_id(p_element_type_id IN NUMBER)
4349 RETURN NUMBER
4350 IS
4351
4352 BEGIN
4353 IF g_abs_id_tab.EXISTS(p_element_type_id)
4354 THEN
4355 RETURN g_abs_id_tab(p_element_type_id).abs_id;
4356 ELSE
4357 RETURN get_absence_details(p_element_type_id);
4358 END IF;
4359
4360 END get_absence_id;
4361
4362
4363
4364 FUNCTION get_abs_running(p_element_type_id IN NUMBER)
4365 RETURN VARCHAR2
4366 IS
4367
4368 l_id NUMBER;
4369
4370 BEGIN
4371 IF g_abs_id_tab.EXISTS(p_element_type_id)
4372 THEN
4373 RETURN g_abs_id_tab(p_element_type_id).run_total;
4374 ELSE
4375 l_id := get_absence_details(p_element_type_id);
4376 RETURN g_abs_id_tab(p_element_type_id).run_total;
4377 END IF;
4378
4379 END get_abs_running;
4380
4381
4382
4383
4384 FUNCTION get_absence_name(p_element_type_id IN NUMBER)
4385 RETURN VARCHAR2
4386 IS
4387
4388 l_id NUMBER;
4389
4390 BEGIN
4391 IF g_abs_id_tab.EXISTS(p_element_type_id)
4392 THEN
4393 RETURN g_abs_id_tab(p_element_type_id).abs_name;
4394 ELSE
4395 l_id := get_absence_details(p_element_type_id);
4396 RETURN g_abs_id_tab(p_element_type_id).abs_name;
4397 END IF;
4398 END get_absence_name;
4399
4400
4401
4402 FUNCTION get_absence_uom(p_element_type_id IN NUMBER)
4403 RETURN VARCHAR2
4404 IS
4405
4406 l_id NUMBER;
4407
4408 BEGIN
4409 IF g_abs_id_tab.EXISTS(p_element_type_id)
4410 THEN
4411 RETURN g_abs_id_tab(p_element_type_id).uom;
4412 ELSE
4413 l_id := get_absence_details(p_element_type_id);
4414 RETURN g_abs_id_tab(p_element_type_id).uom;
4415 END IF;
4416 END get_absence_uom;
4417
4418
4419
4420
4421 FUNCTION get_assignment_id (p_person_id IN NUMBER,
4422 p_start_time IN DATE)
4423 RETURN NUMBER
4424 IS
4425
4426 l_asg_id NUMBER;
4427
4428 CURSOR get_asg
4429 IS SELECT assignment_id
4430 FROM per_all_assignments_f
4431 WHERE person_id = p_person_id
4432 AND p_start_time BETWEEN effective_start_date
4433 AND effective_end_date;
4434
4435 BEGIN
4436 IF g_asgtab.EXISTS(p_person_id)
4437 THEN
4438 RETURN g_asgtab(p_person_id);
4439 ELSE
4440 OPEN get_asg;
4441 FETCH get_asg INTO l_asg_id;
4442 CLOSE get_asg;
4443 g_asgtab(p_person_id) := l_asg_id;
4444 RETURN l_asg_id;
4445 END IF;
4446 END get_assignment_id;
4447
4448
4449 -- Bug 9019114
4450 -- New function added to populate these global
4451 -- variables for validation.
4452 PROCEDURE populate_globals
4453 IS
4454
4455 BEGIN
4456 SELECT bld_blk_info_type_id
4457 INTO g_bld_blk_info
4458 FROM hxc_bld_blk_info_types
4459 WHERE bld_blk_info_type = 'Dummy Element Context';
4460
4461 SELECT retrieval_process_id
4462 INTO g_bee_retrieval
4463 FROM hxc_retrieval_processes
4464 WHERE name = 'BEE Retrieval Process';
4465
4466 END populate_globals;
4467
4468 PROCEDURE is_absence_element(p_alias_value_id IN NUMBER,
4469 p_absence_element_flag OUT NOCOPY VARCHAR2)
4470 IS
4471
4472 BEGIN
4473 IF g_debug THEN
4474 hr_utility.trace('ABS> In hxc_retrieve_absences.is_absence_element');
4475 hr_utility.trace('ABS> p_alias_value_id ::'||p_alias_value_id);
4476 END IF;
4477
4478 SELECT 'Y'
4479 INTO p_absence_element_flag
4480 FROM hxc_absence_type_elements
4481 WHERE element_type_id in ( SELECT attribute1
4482 FROM hxc_alias_values
4483 WHERE alias_value_id = p_alias_value_id);
4484 IF g_debug THEN
4485 hr_utility.trace('ABS> p_absence_element_flag ::'||p_absence_element_flag);
4486 END IF;
4487
4488 EXCEPTION
4489 WHEN NO_DATA_FOUND THEN
4490 p_absence_element_flag := 'N';
4491 IF g_debug THEN
4492 hr_utility.trace('ABS> In hxc_retrieve_absences.is_absence_element -- exception NO_DATA_FOUND');
4493 hr_utility.trace('ABS> p_absence_element_flag ::'||p_absence_element_flag);
4494 END IF;
4495 WHEN TOO_MANY_ROWS THEN
4496 p_absence_element_flag := 'Y';
4497 IF g_debug THEN
4498 hr_utility.trace('ABS> In hxc_retrieve_absences.is_absence_element -- exception TOO_MANY_ROWS');
4499 hr_utility.trace('ABS> p_absence_element_flag ::'||p_absence_element_flag);
4500 END IF;
4501
4502 END is_absence_element;
4503
4504
4505 END HXC_RETRIEVE_ABSENCES;
4506
4507