[Home] [Help]
PACKAGE BODY: APPS.HXC_ABS_RETRIEVAL_PKG
Source
1 PACKAGE BODY hxc_abs_retrieval_pkg AS
2 /* $Header: hxcabsret.pkb 120.24.12020000.2 2012/07/04 09:11:13 amnaraya ship $ */
3
4 g_package varchar2(30) := 'hxc_abs_retrieval_pkg.';
5 g_debug boolean := hr_utility.debug_enabled;
6
7 g_resource_id NUMBER := NULL;
8 g_tc_start DATE := NULL;
9 g_tc_stop DATE := NULL;
10 g_tc_status VARCHAR2 (10) := NULL;
11
12 g_abs_integ_enabled VARCHAR2 (1) := 'N';
13 g_abs_prepop_edit VARCHAR2 (1) := 'N';
14 g_all_id_tab hxc_abs_retrieval_pkg.NUMTAB;
15 g_all_ovn_tab hxc_abs_retrieval_pkg.NUMTAB;
16 g_success_create_msg VARCHAR2 (100):= 'This absence detail was successfully created in Absences module';
17 g_success_delete_msg VARCHAR2 (100):= 'This absence detail was successfully deleted in Absences module';
18 g_fail_create_msg VARCHAR2 (50) := ' (Online Retrieval Failed)';
19 g_business_group_id NUMBER := NULL;
20 g_error_message VARCHAR2(50) := NULL;
21 g_transaction_id hxc_transactions.transaction_id%TYPE := NULL;
22
23 PROCEDURE post_absences (p_resource_id IN NUMBER,
24 p_tc_start IN DATE,
25 p_tc_stop IN DATE,
26 p_tc_status IN VARCHAR2,
27 p_messages IN OUT NOCOPY HXC_MESSAGE_TABLE_TYPE )
28 AS
29
30 CURSOR c_abs_from_temp (c_resource_id NUMBER,
31 c_tc_start DATE,
32 c_tc_stop DATE )
33 IS
34 SELECT time_building_block_id,
35 object_version_number,
36 resource_id,
37 in_time,
38 out_time,
39 absence_attendance_type_id,
40 uom,
41 cost_allocation_keyflex_id,
42 absence_attendance_id,
43 day_start,
44 day_stop,
45 retrieval_status,
46 absences_action,
47 element_type_id,
48 link_time_building_block_id
49 FROM hxc_abs_ret_temp
50 WHERE resource_id = c_resource_id
51 AND TRUNC (day_start) >= TRUNC (c_tc_start)
52 AND TRUNC (day_stop) <= TRUNC (c_tc_stop)
53 ORDER BY absences_action ASC,
54 uom ASC,
55 absence_attendance_type_id ASC,
56 cost_allocation_keyflex_id ASC,
57 day_start ASC;
58
59 -- get cost center information from ATTRIBUTE table
60 CURSOR get_cost_attributes
61 IS
62 SELECT /*+ INDEX(htau HXC_TIME_ATTRIBUTE_USAGES_FK2)
63 INDEX(hta HXC_TIME_ATTRIBUTES_PK) */
64 temp.time_building_block_id time_building_block_id,
65 temp.object_version_number object_version_number,
66 hta.attribute1 attribute1, hta.attribute2 attribute2,
67 hta.attribute3 attribute3, hta.attribute4 attribute4,
68 hta.attribute5 attribute5, hta.attribute6 attribute6,
69 hta.attribute7 attribute7, hta.attribute8 attribute8,
70 hta.attribute9 attribute9, hta.attribute10 attribute10,
71 hta.attribute11 attribute11, hta.attribute12 attribute12,
72 hta.attribute13 attribute13, hta.attribute14 attribute14,
73 hta.attribute15 attribute15, hta.attribute16 attribute16,
74 hta.attribute17 attribute17, hta.attribute18 attribute18,
75 hta.attribute19 attribute19, hta.attribute20 attribute20,
76 hta.attribute21 attribute21, hta.attribute22 attribute22,
77 hta.attribute23 attribute23, hta.attribute24 attribute24,
78 hta.attribute25 attribute25, hta.attribute26 attribute26,
79 hta.attribute27 attribute27, hta.attribute28 attribute28,
80 hta.attribute29 attribute29, hta.attribute30 attribute30,
81 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
82 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
83 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
84 0 cost_allocation_keyflex_id
85 FROM hxc_time_attributes hta,
86 hxc_time_attribute_usages htau,
87 hxc_abs_ret_temp temp
88 WHERE temp.time_building_block_id = htau.time_building_block_id
89 AND temp.object_version_number = htau.time_building_block_ovn
90 AND htau.time_attribute_id = hta.time_attribute_id
91 AND hta.attribute_category = 'Dummy Cost Context';
92
93 -- ABSENCES DELETE/UPDATE IMMEDIATELY AFTER PREPOPULATION
94
95 -- get prepopulated absence_attendance_ids which has to be deleted because of updation/deletion in TC
96 -- immediately after prepopulation
97 -- returns the days information as well
98 CURSOR c_delete_prepop_abs
99 IS
100 SELECT absco.absence_attendance_id,
101 absco.uom,
102 trunc(absco.start_date) start_date,
103 trunc(absco.end_date) end_date,
104 absco.time_building_block_id,
105 absco.object_version_number
106 FROM hxc_abs_co_details absco
107 WHERE absco.resource_id = g_resource_id
108 AND TRUNC (absco.start_time) >= TRUNC (g_tc_start)
109 AND TRUNC (absco.stop_time) <= TRUNC (g_tc_stop)
110 AND absco.stage <> 'RET'
111 AND
112 NOT EXISTS (SELECT /*+ LEADING(htd HXC_TRANSACTION_DETAILS_FK1)
113 INDEX(ht HXC_TRANSACTIONS_PK)
114 INDEX(ht HXC_TRANSACTIONS_N3) */
115 1
116 FROM hxc_transaction_details htd, hxc_transactions ht
117 WHERE absco.time_building_block_id = htd.time_building_block_id
118 AND htd.transaction_id = ht.transaction_id
119 AND ht.transaction_process_id = g_retrieval_process_id
120 AND ht.TYPE = 'RETRIEVAL'
121 AND ht.status = 'SUCCESS'
122 AND htd.status = 'SUCCESS')
123 ORDER BY uom ASC, absence_attendance_id ASC, start_date ASC;
124
125
126 -- RETRIEVED ABSENCES DELETE/UPDATE
127
128 -- get retrieved absence_attendance_ids which has to be deleted because of updation/deletion in TC
129 CURSOR c_delete_ret_abs
130 IS
131 SELECT absence_attendance_id,
132 uom,
133 trunc(day_start) start_date,
134 trunc(day_stop) end_date,
135 time_building_block_id,
136 object_version_number
137 FROM hxc_abs_ret_temp
138 WHERE absence_attendance_id IS NOT NULL
139 AND absences_action = 'DELETE'
140 ORDER BY uom ASC, absence_attendance_id ASC, start_date ASC;
141
142
143 -- this info is used to create TXNS
144 CURSOR c_success_from_temp (c_resource_id NUMBER,
145 c_tc_start DATE,
146 c_tc_stop DATE)
147 IS
148 SELECT temp.time_building_block_id,
149 temp.object_version_number,
150 latest.object_version_number,
151 temp.absences_action,
152 detail.date_to
153 FROM hxc_abs_ret_temp temp,
154 hxc_latest_details latest,
155 hxc_time_building_blocks detail
156 WHERE temp.resource_id = c_resource_id
157 AND TRUNC (temp.day_start) >= TRUNC (c_tc_start)
158 AND TRUNC (temp.day_stop) <= TRUNC (c_tc_stop)
159 AND temp.retrieval_status = 'SUCCESS'
160 AND temp.time_building_block_id = latest.time_building_block_id
161 AND latest.time_building_block_id = detail.time_building_block_id
162 AND latest.object_version_number = detail.object_version_number
163 ORDER BY absences_action ASC;
164
165
166 TYPE t_delete_absences IS TABLE OF c_delete_prepop_abs%ROWTYPE
167 INDEX BY BINARY_INTEGER;
168
169 new_absences_tab t_absences;
170 edited_abs_tab t_absences;
171 all_abs_tab t_absences;
172 l_tbb_ix NUMBER := 0;
173 abs_ix BINARY_INTEGER;
174 l_cost_segment t_cost_attributes;
175 l_id_tab hxc_abs_retrieval_pkg.NUMTAB;
176 l_ovn_tab hxc_abs_retrieval_pkg.NUMTAB;
177
178 l_prepop_delete_absences t_delete_absences;
179 l_ret_delete_absences t_delete_absences;
180 l_edited_days t_edited_days;
181 days_tab t_absences;
182 hours_tab t_absences;
183 l_abs_retrieval_rule VARCHAR2 (50);
184 l_retrieval_rule_grp_id hxc_retrieval_rule_groups_v.retrieval_rule_group_id%TYPE;
185 l_emp_name per_all_people_f.full_name%TYPE;
186 l_temp_row hxc_abs_ret_temp%ROWTYPE;
187 l_count NUMBER;
188 l_days_ix NUMBER;
189 l_d_ix BINARY_INTEGER := 0;
190 l_h_ix BINARY_INTEGER := 0;
191 l_abs_ix BINARY_INTEGER := 0;
192 l_uom VARCHAR2(1);
193
194 l_temp_id_tab NUMTAB;
195 l_temp_ovn_tab NUMTAB;
196 l_latest_ovn_tab NUMTAB;
197 l_action_tab VARCHARTAB;
198 l_create_id_tab NUMTAB;
199 l_create_ovn_tab NUMTAB;
200 l_delete_id_tab NUMTAB;
201 l_delete_ovn_tab NUMTAB;
202 l_latest_date_to_tab DATETAB;
203
204 l_txn_index NUMBER := 0;
205 l_pref_table hxc_preference_evaluation.t_pref_table;
206 l_absence_attendance_id hxc_abs_co_details.absence_attendance_id%TYPE;
207 l_proc VARCHAR2(100);
208 l_ed_days_ix BINARY_INTEGER := 0;
209 l_call_delete BOOLEAN;
210 ret_rules_not_set EXCEPTION;
211
212 -- Bug 9747820
213 l_timecard_id NUMBER;
214 l_timecard_ovn NUMBER;
215
216 BEGIN
217 g_debug := hr_utility.debug_enabled;
218
219 IF g_debug THEN
220 l_proc := g_package||'post_absences';
221 hr_utility.set_location('ABS:Processing '||l_proc, 10);
222 END IF;
223
224 g_abs_integ_enabled := 'N';
225 g_abs_prepop_edit := 'N';
226 g_business_group_id := NULL;
227 g_transaction_id := NULL;
228 g_resource_id := p_resource_id;
229 g_tc_start := p_tc_start;
230 g_tc_stop := p_tc_stop;
231 g_tc_status := p_tc_status;
232
233 IF g_debug THEN
234 hr_utility.TRACE ('ABS:g_resource_id = ' || g_resource_id);
235 hr_utility.TRACE ('ABS:g_tc_start = ' || g_tc_start);
236 hr_utility.TRACE ('ABS:g_tc_stop = ' || g_tc_stop);
237 hr_utility.TRACE ('ABS:g_tc_status = ' || g_tc_status);
238 END IF;
239
240 SELECT full_name, business_group_id
241 INTO l_emp_name, g_business_group_id
242 FROM per_all_people_f
243 WHERE person_id = g_resource_id
244 AND g_tc_start between effective_start_date and effective_end_date;
245
246 IF g_debug THEN
247 hr_utility.TRACE ('ABS:g_business_group_id = ' || g_business_group_id);
248 hr_utility.TRACE ('ABS:l_emp_name = ' || l_emp_name);
249 END IF;
250
251 IF g_retrieval_process_id IS NULL THEN
252 SELECT retrieval_process_id
253 INTO g_retrieval_process_id
254 FROM hxc_retrieval_processes
255 WHERE NAME = 'BEE Retrieval Process';
256 END IF;
257
258 IF g_debug THEN
259 hr_utility.set_location('ABS:Processing '||l_proc, 20);
260 END IF;
261
262 -- Evaluate preferences to get the retrieval rule group preference value and
263 -- other absence preferences
264 hxc_preference_evaluation.resource_preferences(g_resource_id,
265 g_tc_start,
266 g_tc_start,
267 l_pref_table);
268
269
270 IF g_debug THEN
271 hr_utility.set_location('ABS:Processing '||l_proc, 30);
272 END IF;
273
274 IF l_pref_table.COUNT > 0
275 THEN
276 FOR i IN l_pref_table.FIRST..l_pref_table.LAST
277 LOOP
278 IF l_pref_table(i).preference_code = 'TS_ABS_PREFERENCES'
279 THEN
280
281 g_abs_integ_enabled := l_pref_table (i).attribute1;
282 g_abs_prepop_edit := NVL (l_pref_table (i).attribute2, 'N');
283 l_abs_retrieval_rule := NVL(l_pref_table (i).attribute4, 'RRG');
284
285 EXIT;
286
287 END IF;
288 END LOOP;
289
290 -- Bug 9394444
291 -- Added the following code to delete Absences prepopulated
292 -- earlier from the retrieval table.
293
294 -- Bug 9688040
295 -- Added hxc_transactions and details in the below query to
296 -- avoid issues with prepop and edited(or from SS) absences.
297
298 IF g_abs_integ_enabled = 'Y'
299 THEN
300 DELETE FROM hxc_pay_latest_details
301 WHERE (time_building_block_id,object_version_number)
302 IN ( SELECT co.time_building_block_id,
303 co.object_version_number
304 FROM hxc_abs_co_details co,
305 hxc_transaction_details htd,
306 hxc_transactions ht
307 WHERE co.resource_id = g_resource_id
308 AND co.start_time = g_tc_start
309 AND TRUNC(co.stop_time) = TRUNC(g_tc_stop)
310 AND htd.time_building_block_id = co.time_building_block_id
311 AND htd.time_building_block_ovn = co.object_version_number
312 AND htd.transaction_id = ht.transaction_id
313 AND ht.transaction_process_id = g_retrieval_process_id
314 AND action IS NULL );
315 END IF;
316
317
318
319 -- if absences retrieval rule is set to follow the retrieval rule group preference then
320 IF (l_abs_retrieval_rule = 'RRG' and g_abs_integ_enabled = 'Y') THEN
321
322 FOR i IN l_pref_table.FIRST..l_pref_table.LAST
323 LOOP
324
325 IF (l_pref_table(i).preference_code = 'TS_PER_RETRIEVAL_RULES') THEN
326
327 l_retrieval_rule_grp_id := l_pref_table (i).attribute1;
328
329 l_abs_retrieval_rule := get_retrieval_rule ( l_retrieval_rule_grp_id );
330
331 IF l_abs_retrieval_rule = 'NODATA'
332 THEN
333 RAISE ret_rules_not_set;
334 END IF;
335 END IF;
336
337 END LOOP;
338
339 END IF;
340 END IF;
341
342 IF g_debug THEN
343 hr_utility.set_location('ABS:Processing '||l_proc, 40);
344 END IF;
345
346 IF g_debug THEN
347 hr_utility.TRACE ('ABS:g_abs_integ_enabled = ' || g_abs_integ_enabled);
348 hr_utility.TRACE ('ABS:g_abs_prepop_edit = ' || g_abs_prepop_edit);
349 hr_utility.TRACE ('ABS:l_abs_retrieval_rule = ' || l_abs_retrieval_rule);
350 END IF;
351
352
353 IF ( g_abs_integ_enabled = 'Y'
354 AND g_tc_status <> 'ERROR'
355 )
356 THEN -- if1
357 IF (l_abs_retrieval_rule = g_tc_status)
358 OR (l_abs_retrieval_rule = 'WORKING')
359 OR (g_tc_status = 'APPROVED'
360 AND l_abs_retrieval_rule = 'SUBMITTED'
361 )
362 OR (g_tc_status = 'DELETED')
363 THEN -- if2
364
365 IF g_debug THEN
366 hr_utility.set_location('ABS:Processing '||l_proc, 50);
367 END IF;
368
369 -- Populate TEMP table with absences data that has to be created
370 -- Absences which are new and not yet retrieved
371
372 INSERT INTO hxc_abs_ret_temp (
373 SELECT /*+ LEADING (latest HXC_LATEST_DETAILS_N1)
374 INDEX(detail HXC_TIME_BUILDING_BLOCKS_PK)
375 INDEX(htau HXC_TIME_ATTRIBUTE_USAGES_FK2)
376 INDEX(hta HXC_TIME_ATTRIBUTES_PK) */
377 detail.time_building_block_id, --time_building_block_id,
378 detail.object_version_number , --object_version_number,
379 detail.resource_id , --resource_id,
380 detail.start_time , --in_time,
381 detail.stop_time , --out_time,
382 hate.absence_attendance_type_id, --absence_attendance_type_id,
383 SUBSTR (hate.uom, 1, 1) , --uom,
384 0 , --cost_allocation_keyflex_id,
385 NULL , --absence_attendance_id,
386 latest.start_time , --day_start,
387 latest.stop_time , --day_stop,
388 NULL , --retrieval_status,
389 'CREATE' , --absences_action,
390 to_number(
391 SUBSTR(hta.attribute_category, 11)
392 ) , --element_type_id,
393 NULL --link_time_building_block_id
394 FROM hxc_latest_details latest,
395 hxc_time_building_blocks detail,
396 hxc_time_attribute_usages htau,
397 hxc_time_attributes hta,
398 hxc_absence_type_elements hate
399 WHERE latest.resource_id = g_resource_id
400 AND TRUNC (latest.start_time) BETWEEN TRUNC (g_tc_start) AND TRUNC (g_tc_stop)
401 AND latest.time_building_block_id = detail.time_building_block_id
402 AND latest.object_version_number = detail.object_version_number
403 AND detail.time_building_block_id = htau.time_building_block_id
404 AND detail.object_version_number = htau.time_building_block_ovn
405 AND detail.date_to = hr_general.end_of_time
406 AND htau.time_attribute_id = hta.time_attribute_id
407 AND hta.attribute_category like 'ELEMENT - %'
408 AND to_number(SUBSTR(hta.attribute_category, 11)) = hate.element_type_id
409 AND NOT EXISTS (
410 SELECT /*+ LEADING(htd HXC_TRANSACTION_DETAILS_FK1)
411 INDEX(ht HXC_TRANSACTIONS_PK)
412 INDEX(ht HXC_TRANSACTIONS_N3) */
413 1
414 FROM hxc_transaction_details htd, hxc_transactions ht
415 WHERE latest.time_building_block_id = htd.time_building_block_id
416 AND latest.object_version_number = htd.time_building_block_ovn
417 AND htd.transaction_id = ht.transaction_id
418 AND ht.transaction_process_id = g_retrieval_process_id
419 AND ht.TYPE = 'RETRIEVAL'
420 AND ht.status = 'SUCCESS'
421 AND htd.status = 'SUCCESS') );
422
423
424 IF g_debug THEN
425 hr_utility.TRACE ( 'ABS:new absences COUNT = ' || SQL%ROWCOUNT);
426 END IF;
427
428 IF g_debug THEN
429 hr_utility.set_location('ABS:Processing '||l_proc, 60);
430 END IF;
431
432 -- Populate TEMP table with updated/deleted absences data
433 -- This cursor returns Absences which were retrieved earlier and have undergone a change during
434 -- next retrieval
435 INSERT INTO hxc_abs_ret_temp (
436 SELECT detail.time_building_block_id , --time_building_block_id,
437 detail.object_version_number , --object_version_number,
438 detail.resource_id , --resource_id,
439 detail.start_time , --in_time,
440 detail.stop_time , --out_time,
441 absatt.absence_attendance_type_id, --absence_attendance_type_id,
442 absco.uom , --uom,
443 0 , --cost_allocation_keyflex_id,
444 absco.absence_attendance_id , --absence_attendance_id,
445 TRUNC (DAY.start_time) , --day_start,
446 TRUNC (DAY.stop_time) , --day_stop,
447 NULL , --retrieval_status,
448 'DELETE' , --absences_action,
449 absco.element_type_id , --element_type_id,
450 NULL --link_time_building_block_id
451 FROM hxc_abs_co_details absco,
452 hxc_time_building_blocks detail,
453 hxc_time_building_blocks DAY,
454 per_absence_attendances absatt
455 WHERE absco.resource_id = g_resource_id
456 AND TRUNC (absco.start_time) = TRUNC (g_tc_start)
457 AND TRUNC (absco.stop_time) = TRUNC (g_tc_stop)
458 AND absco.absence_attendance_id IS NOT NULL
459 AND detail.time_building_block_id = absco.time_building_block_id
460 AND detail.object_version_number =
461 (SELECT /*+ LEADING(htd HXC_TRANSACTION_DETAILS_FK1)
462 INDEX(ht HXC_TRANSACTIONS_PK)
463 INDEX(ht HXC_TRANSACTIONS_N3) */
464 MAX (htd.time_building_block_ovn)
465 FROM hxc_transactions ht, hxc_transaction_details htd
466 WHERE ht.transaction_process_id = g_retrieval_process_id
467 AND htd.time_building_block_id = absco.time_building_block_id
468 AND htd.transaction_id = ht.transaction_id
469 AND htd.status = 'SUCCESS'
470 AND ht.TYPE = 'RETRIEVAL'
471 AND ht.status = 'SUCCESS')
472 AND DAY.time_building_block_id = detail.parent_building_block_id
473 AND DAY.object_version_number = detail.parent_building_block_ovn
474 AND detail.date_to <> hr_general.end_of_time
475 AND absco.absence_attendance_id = absatt.absence_attendance_id ) ;
476
477
478 IF g_debug THEN
479 hr_utility.TRACE ('ABS:edited abs COUNT = ' || SQL%ROWCOUNT );
480 END IF;
481
482 IF g_debug THEN
483 hr_utility.set_location('ABS:Processing '||l_proc, 70);
484 END IF;
485
486
487 -- Bug 9394444
488 -- Added the DELETE to avoid Abs details getting into
489 -- retrieval tables.
490
491 DELETE FROM hxc_pay_latest_details
492 WHERE (time_building_block_id,object_version_number)
493 IN ( SELECT time_building_block_id,
494 object_version_number
495 FROM hxc_abs_ret_temp );
496
497
498
499
500 OPEN c_abs_from_temp(g_resource_id,
501 g_tc_start,
502 g_tc_stop);
503 FETCH c_abs_from_temp BULK COLLECT INTO all_abs_tab;
504 CLOSE c_abs_from_temp;
505
506 IF all_abs_tab.COUNT > 0 THEN
507 abs_ix := all_abs_tab.FIRST;
508
509 LOOP
510 l_tbb_ix := all_abs_tab(abs_ix).time_building_block_id;
511
512 IF (all_abs_tab(abs_ix).absences_action = 'CREATE') THEN
513 new_absences_tab(l_tbb_ix).time_building_block_id := all_abs_tab(abs_ix).time_building_block_id;
514 new_absences_tab(l_tbb_ix).object_version_number := all_abs_tab(abs_ix).object_version_number;
515 new_absences_tab(l_tbb_ix).resource_id := all_abs_tab(abs_ix).resource_id;
516 new_absences_tab(l_tbb_ix).in_time := all_abs_tab(abs_ix).in_time;
517 new_absences_tab(l_tbb_ix).out_time := all_abs_tab(abs_ix).out_time;
518 new_absences_tab(l_tbb_ix).absence_attendance_type_id := all_abs_tab(abs_ix).absence_attendance_type_id;
519 new_absences_tab(l_tbb_ix).uom := all_abs_tab(abs_ix).uom;
520 new_absences_tab(l_tbb_ix).cost_allocation_keyflex_id := all_abs_tab(abs_ix).cost_allocation_keyflex_id;
521 new_absences_tab(l_tbb_ix).absence_attendance_id := all_abs_tab(abs_ix).absence_attendance_id;
522 new_absences_tab(l_tbb_ix).day_start := all_abs_tab(abs_ix).day_start;
523 new_absences_tab(l_tbb_ix).day_stop := all_abs_tab(abs_ix).day_stop;
524 new_absences_tab(l_tbb_ix).retrieval_status := all_abs_tab(abs_ix).retrieval_status;
525 new_absences_tab(l_tbb_ix).absences_action := all_abs_tab(abs_ix).absences_action;
526 new_absences_tab(l_tbb_ix).element_type_id := all_abs_tab(abs_ix).element_type_id;
527 new_absences_tab(l_tbb_ix).link_time_building_block_id := all_abs_tab(abs_ix).link_time_building_block_id;
528 ELSE
529 edited_abs_tab(l_tbb_ix).time_building_block_id := all_abs_tab(abs_ix).time_building_block_id;
530 edited_abs_tab(l_tbb_ix).object_version_number := all_abs_tab(abs_ix).object_version_number;
531 edited_abs_tab(l_tbb_ix).resource_id := all_abs_tab(abs_ix).resource_id;
532 edited_abs_tab(l_tbb_ix).in_time := all_abs_tab(abs_ix).in_time;
533 edited_abs_tab(l_tbb_ix).out_time := all_abs_tab(abs_ix).out_time;
534 edited_abs_tab(l_tbb_ix).absence_attendance_type_id := all_abs_tab(abs_ix).absence_attendance_type_id;
535 edited_abs_tab(l_tbb_ix).uom := all_abs_tab(abs_ix).uom;
536 edited_abs_tab(l_tbb_ix).cost_allocation_keyflex_id := all_abs_tab(abs_ix).cost_allocation_keyflex_id;
537 edited_abs_tab(l_tbb_ix).absence_attendance_id := all_abs_tab(abs_ix).absence_attendance_id;
538 edited_abs_tab(l_tbb_ix).day_start := all_abs_tab(abs_ix).day_start;
539 edited_abs_tab(l_tbb_ix).day_stop := all_abs_tab(abs_ix).day_stop;
540 edited_abs_tab(l_tbb_ix).retrieval_status := all_abs_tab(abs_ix).retrieval_status;
541 edited_abs_tab(l_tbb_ix).absences_action := all_abs_tab(abs_ix).absences_action;
542 edited_abs_tab(l_tbb_ix).element_type_id := all_abs_tab(abs_ix).element_type_id;
543 edited_abs_tab(l_tbb_ix).link_time_building_block_id := all_abs_tab(abs_ix).link_time_building_block_id;
544 END IF;
545
546 abs_ix := all_abs_tab.NEXT (abs_ix);
547 EXIT WHEN NOT all_abs_tab.EXISTS (abs_ix);
548 END LOOP;
549
550 END IF; -- all_abs_tab.COUNT > 0
551
552 IF g_debug THEN
553 hr_utility.TRACE ('ABS:new_absences_tab.COUNT = ' || new_absences_tab.COUNT );
554 hr_utility.TRACE ('ABS:edited_abs_tab.COUNT = ' || edited_abs_tab.COUNT );
555 END IF;
556
557 -- update the TEMP table with the Dummy Cost Context segment value
558 IF (new_absences_tab.COUNT > 0 OR edited_abs_tab.COUNT > 0)
559 THEN
560
561 IF g_debug THEN
562 hr_utility.TRACE ('ABS:Update TEMP table with cost information');
563 END IF;
564
565 OPEN get_cost_attributes;
566
567 FETCH get_cost_attributes
568 BULK COLLECT INTO l_cost_segment;
569
570 CLOSE get_cost_attributes;
571
572 IF g_debug THEN
573 hr_utility.TRACE ( 'ABS:l_cost_segment.COUNT = ' || l_cost_segment.COUNT );
574 END IF;
575
576 IF (l_cost_segment.COUNT > 0)
577 THEN
578 populate_cost_keyflex (l_cost_segment);
579 FOR l_ix IN l_cost_segment.FIRST .. l_cost_segment.LAST
580 LOOP
581 UPDATE hxc_abs_ret_temp
582 SET cost_allocation_keyflex_id = l_cost_segment (l_ix).cost_allocation_keyflex_id
583 WHERE time_building_block_id = l_cost_segment (l_ix).time_building_block_id
584 AND object_version_number = l_cost_segment (l_ix).object_version_number;
585 END LOOP;
586 END IF;
587
588 l_cost_segment.DELETE;
589
590 IF g_debug THEN
591 hr_utility.set_location('ABS:Processing '||l_proc, 80);
592 END IF;
593
594
595 -- The following processing is for records which only has a OVN change and no "Hours Type" or "detail" change
596 -- When multiple OVN changes happen before retrieval with no attribute or detail change,
597 -- these changes should not be considered for retrieval, instead the TXNS and CO tables have to updated with the
598 -- latest ovn, by doing this we avoid a unnecessary delete and recreate of exactly same absences in HR
599 -- This processing is only for unplanned absence records,
600 -- for prepopulated ones, this ovn update happens during validation phase itself.
601
602 IF g_debug THEN
603 hr_utility.TRACE ('ABS:Process records with only ovn updates');
604 END IF;
605
606 IF (new_absences_tab.COUNT > 0) THEN
607 abs_ix := new_absences_tab.FIRST;
608 LOOP
609
610 l_tbb_ix := new_absences_tab(abs_ix).time_building_block_id;
611
612 IF edited_abs_tab.EXISTS(l_tbb_ix) THEN
613
614 IF new_absences_tab(l_tbb_ix).uom = 'H' and edited_abs_tab(l_tbb_ix).uom = 'H' THEN
615
616 IF new_absences_tab(l_tbb_ix).time_building_block_id = edited_abs_tab(l_tbb_ix).time_building_block_id
617 AND
618 new_absences_tab(l_tbb_ix).in_time = edited_abs_tab(l_tbb_ix).in_time
619 AND
620 new_absences_tab(l_tbb_ix).out_time = edited_abs_tab(l_tbb_ix).out_time
621 AND
622 new_absences_tab(l_tbb_ix).absence_attendance_type_id = edited_abs_tab(l_tbb_ix).absence_attendance_type_id
623 AND
624 new_absences_tab(l_tbb_ix).cost_allocation_keyflex_id = edited_abs_tab(l_tbb_ix).cost_allocation_keyflex_id
625 THEN
626
627 l_txn_index := l_txn_index + 1;
628 l_id_tab(l_txn_index) := new_absences_tab(l_tbb_ix).time_building_block_id;
629 l_ovn_tab(l_txn_index) := new_absences_tab(l_tbb_ix).object_version_number;
630
631 END IF;
632 END IF;
633
634 IF new_absences_tab(l_tbb_ix).uom = 'D' and edited_abs_tab(l_tbb_ix).uom = 'D' THEN
635
636 IF new_absences_tab(l_tbb_ix).time_building_block_id = edited_abs_tab(l_tbb_ix).time_building_block_id
637 AND
638 new_absences_tab(l_tbb_ix).absence_attendance_type_id = edited_abs_tab(l_tbb_ix).absence_attendance_type_id
639 AND
640 new_absences_tab(l_tbb_ix).cost_allocation_keyflex_id = edited_abs_tab(l_tbb_ix).cost_allocation_keyflex_id
641 THEN
642
643 l_txn_index := l_txn_index + 1;
644 l_id_tab(l_txn_index) := new_absences_tab(l_tbb_ix).time_building_block_id;
645 l_ovn_tab(l_txn_index) := new_absences_tab(l_tbb_ix).object_version_number;
646
647 END IF;
648 END IF;
649
650 END IF;
651
652 abs_ix := new_absences_tab.NEXT (abs_ix);
653 EXIT WHEN NOT new_absences_tab.EXISTS (abs_ix);
654 END LOOP;
655 END IF; -- new_absences_tab.COUNT > 0
656
657 IF g_debug THEN
658 hr_utility.TRACE ('ABS:Delete records from temp having only OVN change'||l_id_tab.COUNT);
659 END IF;
660
661 IF (l_id_tab.COUNT > 0)
662 THEN
663
664 FORALL l_ix IN l_id_tab.FIRST .. l_id_tab.LAST
665 DELETE FROM hxc_abs_ret_temp
666 WHERE time_building_block_id = l_id_tab(l_ix);
667
668 FORALL l_ix IN l_ovn_tab.FIRST .. l_ovn_tab.LAST
669 UPDATE hxc_abs_co_details
670 SET object_version_number = l_ovn_tab (l_ix)
671 WHERE time_building_block_id = l_id_tab(l_ix)
672 AND stage = 'RET';
673
674 create_transactions (l_id_tab,
675 l_ovn_tab,
676 'SUCCESS',
677 g_success_create_msg
678 );
679 END IF;
680
681 IF g_debug THEN
682 hr_utility.set_location('ABS:Processing '||l_proc, 90);
683 END IF;
684
685 l_id_tab.DELETE;
686 l_ovn_tab.DELETE;
687
688 END IF; -- new_absences_tab.COUNT + edited_abs_tab.COUNT
689
690 new_absences_tab.DELETE;
691 edited_abs_tab.DELETE;
692
693 all_abs_tab.DELETE;
694 g_all_id_tab.DELETE;
695 g_all_ovn_tab.DELETE;
696
697 OPEN c_abs_from_temp(g_resource_id,
698 g_tc_start,
699 g_tc_stop);
700 FETCH c_abs_from_temp BULK COLLECT INTO all_abs_tab;
701 CLOSE c_abs_from_temp;
702
703 IF g_debug THEN
704 hr_utility.TRACE ( 'ABS:Records to be processed in TEMP table = '|| all_abs_tab.COUNT );
705 END IF;
706
707 IF (all_abs_tab.COUNT > 0) THEN
708 FOR abs_ix IN all_abs_tab.FIRST .. all_abs_tab.LAST
709 LOOP
710 -- g_all_id_tab stored all rows in TEMP table, this is used for audit purposes in case of exception
711 g_all_id_tab(abs_ix) := all_abs_tab(abs_ix).time_building_block_id;
712 g_all_ovn_tab(abs_ix) := all_abs_tab(abs_ix).object_version_number;
713
714 -- build pl/sql tables
715
716 -- build pl/sql tables for days and hours CREATE records
717 IF (all_abs_tab(abs_ix).absences_action = 'CREATE'
718 AND
719 all_abs_tab(abs_ix).absence_attendance_id IS NULL) THEN
720
721 -- build days_tab for 'D' Create records
722 IF (all_abs_tab(abs_ix).uom = 'D') THEN
723 l_d_ix := l_d_ix + 1;
724 days_tab(l_d_ix).time_building_block_id := all_abs_tab(abs_ix).time_building_block_id;
725 days_tab(l_d_ix).object_version_number := all_abs_tab(abs_ix).object_version_number;
726 days_tab(l_d_ix).resource_id := all_abs_tab(abs_ix).resource_id;
727 days_tab(l_d_ix).in_time := all_abs_tab(abs_ix).in_time;
728 days_tab(l_d_ix).out_time := all_abs_tab(abs_ix).out_time;
729 days_tab(l_d_ix).absence_attendance_type_id := all_abs_tab(abs_ix).absence_attendance_type_id;
730 days_tab(l_d_ix).uom := all_abs_tab(abs_ix).uom;
731 days_tab(l_d_ix).cost_allocation_keyflex_id := all_abs_tab(abs_ix).cost_allocation_keyflex_id;
732 days_tab(l_d_ix).absence_attendance_id := all_abs_tab(abs_ix).absence_attendance_id;
733 days_tab(l_d_ix).day_start := all_abs_tab(abs_ix).day_start;
734 days_tab(l_d_ix).day_stop := all_abs_tab(abs_ix).day_stop;
735 days_tab(l_d_ix).retrieval_status := all_abs_tab(abs_ix).retrieval_status;
736 days_tab(l_d_ix).absences_action := all_abs_tab(abs_ix).absences_action;
737 days_tab(l_d_ix).element_type_id := all_abs_tab(abs_ix).element_type_id;
738 days_tab(l_d_ix).link_time_building_block_id := all_abs_tab(abs_ix).link_time_building_block_id;
739 ELSE
740 -- build hours_tab for 'H' Create records
741 l_h_ix := l_h_ix + 1;
742 hours_tab(l_h_ix).time_building_block_id := all_abs_tab(abs_ix).time_building_block_id;
743 hours_tab(l_h_ix).object_version_number := all_abs_tab(abs_ix).object_version_number;
744 hours_tab(l_h_ix).resource_id := all_abs_tab(abs_ix).resource_id;
745 hours_tab(l_h_ix).in_time := all_abs_tab(abs_ix).in_time;
746 hours_tab(l_h_ix).out_time := all_abs_tab(abs_ix).out_time;
747 hours_tab(l_h_ix).absence_attendance_type_id := all_abs_tab(abs_ix).absence_attendance_type_id;
748 hours_tab(l_h_ix).uom := all_abs_tab(abs_ix).uom;
749 hours_tab(l_h_ix).cost_allocation_keyflex_id := all_abs_tab(abs_ix).cost_allocation_keyflex_id;
750 hours_tab(l_h_ix).absence_attendance_id := all_abs_tab(abs_ix).absence_attendance_id;
751 hours_tab(l_h_ix).day_start := all_abs_tab(abs_ix).day_start;
752 hours_tab(l_h_ix).day_stop := all_abs_tab(abs_ix).day_stop;
753 hours_tab(l_h_ix).retrieval_status := all_abs_tab(abs_ix).retrieval_status;
754 hours_tab(l_h_ix).absences_action := all_abs_tab(abs_ix).absences_action;
755 hours_tab(l_h_ix).element_type_id := all_abs_tab(abs_ix).element_type_id;
756 hours_tab(l_h_ix).link_time_building_block_id := all_abs_tab(abs_ix).link_time_building_block_id;
757 END IF;
758 END IF;
759
760 END LOOP;
761 END IF; -- all_abs_tab.COUNT > 0
762
763 ---------------PHASE 1------------------------------
764
765 -- PREPOPULATED ABSENCES which are deleted/updated from OTL timecard immediately after prepopulation
766 -- has to get updated in HR Absences as well.
767 -- Process these first, because if there are Absences which spread across time periods for a
768 -- single absence_attendance_id, then we need to delete the whole Absence record and recreate
769 -- the needed ones which are not deleted via OTL timecard.
770
771 l_call_delete := FALSE;
772
773 OPEN c_delete_prepop_abs;
774
775 FETCH c_delete_prepop_abs
776 BULK COLLECT INTO l_prepop_delete_absences;
777
778 CLOSE c_delete_prepop_abs;
779
780 IF g_debug THEN
781 hr_utility.TRACE( 'ABS:** Prepopulation ** Count of prepop rows updated = ' || l_prepop_delete_absences.COUNT);
782 END IF;
783
784 -- call for delete and recreation, if any
785 IF (l_prepop_delete_absences.COUNT > 0)
786 THEN
787 l_abs_ix := l_prepop_delete_absences.FIRST;
788
789 LOOP
790 l_ed_days_ix := l_ed_days_ix + 1;
791 l_edited_days(l_ed_days_ix).day_start := l_prepop_delete_absences (l_abs_ix).start_date;
792 l_edited_days(l_ed_days_ix).day_stop := l_prepop_delete_absences (l_abs_ix).end_date;
793 l_edited_days(l_ed_days_ix).time_building_block_id := l_prepop_delete_absences (l_abs_ix).time_building_block_id;
794 l_edited_days(l_ed_days_ix).object_version_number := l_prepop_delete_absences (l_abs_ix).object_version_number;
795
796 l_absence_attendance_id := l_prepop_delete_absences (l_abs_ix).absence_attendance_id;
797 l_uom := l_prepop_delete_absences (l_abs_ix).uom;
798
799 l_abs_ix := l_abs_ix + 1;
800
801 IF (l_prepop_delete_absences.EXISTS (l_abs_ix)) THEN
802 IF (l_prepop_delete_absences (l_abs_ix).absence_attendance_id <> l_absence_attendance_id) THEN
803 l_call_delete := TRUE;
804 ELSE
805 l_call_delete := FALSE;
806 END IF;
807 ELSE
808 l_call_delete := TRUE;
809 END IF;
810
811 IF l_call_delete THEN
812
813 IF g_debug THEN
814 hr_utility.TRACE( 'ABS:Process delete for Absence attendance id = '
815 || l_absence_attendance_id );
816 END IF;
817
818 -- call delete absences for each absence_attendance_id
819 delete_absences
820 (l_absence_attendance_id,
821 l_edited_days,
822 l_uom
823 );
824
825 IF g_debug THEN
826 hr_utility.TRACE( 'ABS:Completed delete for Absence attendance id = '
827 || l_absence_attendance_id );
828 END IF;
829
830 l_ed_days_ix := 0;
831 l_edited_days.DELETE;
832
833 END IF;
834 EXIT WHEN NOT l_prepop_delete_absences.EXISTS (l_abs_ix);
835 END LOOP;
836
837 END IF;
838
839 IF g_debug THEN
840 hr_utility.set_location('ABS:Processing '||l_proc, 100);
841 END IF;
842
843 l_prepop_delete_absences.DELETE;
844 l_edited_days.DELETE;
845
846 -- Proceed furthur ONLY if there is data in the TEMP table
847 IF (g_all_id_tab.COUNT <> 0)
848 THEN
849 -- ABSENCES which are deleted from OTL timecard and have been retrieved earlier
850 -- has to get deleted in HR Absences as well.
851 -- Process these next, because if there are Absences which spread across time periods for a
852 -- single absence_attendance_id, then we need to delete the whole Absence record and recreate
853 -- the needed ones which are not deleted via OTL timecard.
854
855 l_call_delete := FALSE;
856
857 IF g_debug THEN
858 hr_utility.set_location('ABS:Processing '||l_proc, 110);
859 END IF;
860
861 OPEN c_delete_ret_abs;
862
863 FETCH c_delete_ret_abs
864 BULK COLLECT INTO l_ret_delete_absences;
865
866 CLOSE c_delete_ret_abs;
867
868 IF g_debug THEN
869 hr_utility.TRACE ( 'ABS:** Retrieved ** Count of retrieved rows updated = ' || l_ret_delete_absences.COUNT );
870 END IF;
871
872 l_ed_days_ix := 0;
873
874 -- call for delete and recreation, if any
875 IF (l_ret_delete_absences.COUNT > 0)
876 THEN
877 l_abs_ix := l_ret_delete_absences.FIRST;
878
879 LOOP
880 l_ed_days_ix := l_ed_days_ix + 1;
881 l_edited_days(l_ed_days_ix).day_start := l_ret_delete_absences (l_abs_ix).start_date;
882 l_edited_days(l_ed_days_ix).day_stop := l_ret_delete_absences (l_abs_ix).end_date;
883 l_edited_days(l_ed_days_ix).time_building_block_id := l_ret_delete_absences (l_abs_ix).time_building_block_id;
884 l_edited_days(l_ed_days_ix).object_version_number := l_ret_delete_absences (l_abs_ix).object_version_number;
885
886 l_absence_attendance_id := l_ret_delete_absences (l_abs_ix).absence_attendance_id;
887 l_uom := l_ret_delete_absences (l_abs_ix).uom;
888
889 l_abs_ix := l_abs_ix + 1;
890
891 IF (l_ret_delete_absences.EXISTS (l_abs_ix)) THEN
892 IF (l_ret_delete_absences (l_abs_ix).absence_attendance_id <> l_absence_attendance_id) THEN
893 l_call_delete := TRUE;
894 ELSE
895 l_call_delete := FALSE;
896 END IF;
897 ELSE
898 l_call_delete := TRUE;
899 END IF;
900
901 IF l_call_delete THEN
902
903 IF g_debug THEN
904 hr_utility.TRACE( 'ABS:Process delete for Absence attendance id = '
905 || l_absence_attendance_id );
906 END IF;
907
908 -- call delete absences for each absence_attendance_id
909 delete_absences
910 (l_absence_attendance_id,
911 l_edited_days,
912 l_uom
913 );
914
915 IF g_debug THEN
916 hr_utility.TRACE( 'ABS:Completed delete for Absence attendance id = '
917 || l_absence_attendance_id );
918 END IF;
919
920 l_ed_days_ix := 0;
921 l_edited_days.DELETE;
922
923 END IF;
924 EXIT WHEN NOT l_ret_delete_absences.EXISTS (l_abs_ix);
925 END LOOP;
926
927 END IF;
928
929
930 IF g_debug THEN
931 hr_utility.set_location('ABS:Processing '||l_proc, 120);
932 END IF;
933
934 l_ret_delete_absences.DELETE;
935 l_edited_days.DELETE;
936
937 IF g_debug THEN
938 hr_utility.trace('ABS: Start processing CREATE Absence records in TEMP table');
939 END IF;
940
941 -- Process CREATE records with UOM = DAYS, this is for fresh unplanned absences
942 -- logic for continuous posting for DAYS
943 l_days_ix := days_tab.FIRST;
944 l_count := days_tab.COUNT;
945
946 IF g_debug THEN
947 hr_utility.TRACE ('ABS:current count - days = ' || days_tab.COUNT);
948 END IF;
949
950 IF (l_count > 0)
951 THEN
952 LOOP
953 EXIT WHEN l_days_ix > l_count;
954
955 IF (days_tab.EXISTS (l_days_ix + 1))
956 THEN
957
958 IF ( days_tab (l_days_ix + 1).absence_attendance_type_id =
959 days_tab (l_days_ix).absence_attendance_type_id
960 AND days_tab (l_days_ix + 1).cost_allocation_keyflex_id =
961 days_tab (l_days_ix).cost_allocation_keyflex_id
962 AND TRUNC (days_tab (l_days_ix + 1).day_stop) =
963 TRUNC (days_tab (l_days_ix).day_stop) + 1
964 )
965 THEN
966 IF g_debug THEN
967 hr_utility.TRACE ('ABS:difference of 1 in dates');
968 END IF;
969
970 days_tab (l_days_ix + 1).day_start := days_tab (l_days_ix).day_start;
971
972 UPDATE hxc_abs_ret_temp
973 SET link_time_building_block_id = days_tab (l_days_ix + 1).time_building_block_id
974 WHERE time_building_block_id = days_tab (l_days_ix).time_building_block_id
975 AND absences_action = 'CREATE';
976
977 days_tab.DELETE (l_days_ix);
978 ELSIF ( days_tab (l_days_ix + 1).absence_attendance_type_id =
979 days_tab (l_days_ix).absence_attendance_type_id
980 AND days_tab (l_days_ix + 1).cost_allocation_keyflex_id =
981 days_tab (l_days_ix).cost_allocation_keyflex_id
982 AND TRUNC (days_tab (l_days_ix + 1).day_stop) =
983 TRUNC (days_tab (l_days_ix).day_stop)
984 )
985 THEN
986
987 IF g_debug THEN
988 hr_utility.TRACE ('ABS:no difference in dates');
989 END IF;
990
991 l_temp_row := days_tab (l_days_ix);
992 days_tab (l_days_ix) := days_tab (l_days_ix + 1);
993 days_tab (l_days_ix + 1) := l_temp_row;
994 END IF;
995 END IF;
996
997 l_days_ix := l_days_ix + 1;
998 END LOOP;
999
1000 IF g_debug THEN
1001 hr_utility.set_location('ABS:Processing '||l_proc, 130);
1002 END IF;
1003
1004 -- send to absences module
1005 IF g_debug THEN
1006 hr_utility.TRACE ('ABS:CALLING create_absences for DAYS');
1007 END IF;
1008
1009 create_absences (days_tab,
1010 'D');
1011
1012 IF g_debug THEN
1013 hr_utility.TRACE ('ABS:COMPLETED create_absences for DAYS');
1014 END IF;
1015
1016 END IF;
1017
1018 IF g_debug THEN
1019 hr_utility.set_location('ABS:Processing '||l_proc, 140);
1020 END IF;
1021
1022 -- Process CREATE records with UOM = HOURS (fresh unplanned absences)
1023 IF g_debug THEN
1024 hr_utility.TRACE ('ABS:current count - hours = ' || hours_tab.COUNT);
1025 END IF;
1026
1027 -- send to absences module, there is no continuous posting for HOURS
1028 IF (hours_tab.COUNT > 0)
1029 THEN
1030
1031 IF g_debug THEN
1032 hr_utility.TRACE ('ABS:CALLING create_absences for HOURS');
1033 END IF;
1034
1035 create_absences (hours_tab,
1036 'H');
1037
1038 IF g_debug THEN
1039 hr_utility.TRACE ('ABS:COMPLETED create_absences for HOURS');
1040 END IF;
1041
1042 END IF;
1043
1044 IF g_debug THEN
1045 hr_utility.set_location('ABS:Processing '||l_proc, 150);
1046 END IF;
1047
1048
1049 -- Processing for TXN DETAILS creation starts
1050 OPEN c_success_from_temp (p_resource_id, p_tc_start, p_tc_stop);
1051
1052 FETCH c_success_from_temp BULK COLLECT INTO l_temp_id_tab,
1053 l_temp_ovn_tab,
1054 l_latest_ovn_tab,
1055 l_action_tab,
1056 l_latest_date_to_tab;
1057
1058 CLOSE c_success_from_temp;
1059
1060 l_d_ix := 0;
1061 l_h_ix := 0;
1062
1063 IF (l_temp_id_tab.COUNT > 0) THEN
1064 FOR l_ix IN l_temp_id_tab.FIRST .. l_temp_id_tab.LAST
1065 LOOP
1066
1067 IF (l_action_tab(l_ix) = 'CREATE') THEN
1068 l_d_ix := l_d_ix + 1;
1069 l_create_id_tab(l_d_ix) := l_temp_id_tab(l_ix);
1070 l_create_ovn_tab(l_d_ix) := l_temp_ovn_tab(l_ix);
1071 ELSE
1072 l_h_ix := l_h_ix + 1;
1073 l_delete_id_tab(l_h_ix) := l_temp_id_tab(l_ix);
1074
1075 IF l_latest_date_to_tab(l_ix) <> hr_general.end_of_time THEN
1076 l_delete_ovn_tab(l_h_ix) := l_latest_ovn_tab(l_ix);
1077 ELSE
1078 l_delete_ovn_tab(l_h_ix) := l_temp_ovn_tab(l_ix);
1079 END IF;
1080 END IF;
1081
1082 END LOOP;
1083 END IF;
1084
1085 IF g_debug THEN
1086 hr_utility.set_location('ABS:Processing '||l_proc, 160);
1087 hr_utility.trace('create txn records - '||l_create_id_tab.COUNT);
1088 hr_utility.trace('delete txn records - '||l_delete_id_tab.COUNT);
1089 END IF;
1090
1091 -- Process CREATE absence records from TEMP table which are successfully sent to Absences
1092 -- Make an entry for these retrieved Absence details in TXN tables.
1093 IF (l_create_id_tab.COUNT > 0)
1094 THEN
1095
1096 IF g_debug THEN
1097 hr_utility.TRACE ('ABS:CALLING create_transactions for CREATE records - '||l_create_id_tab.COUNT);
1098 END IF;
1099
1100 create_transactions (l_create_id_tab,
1101 l_create_ovn_tab,
1102 'SUCCESS',
1103 g_success_create_msg
1104 );
1105
1106 IF g_debug THEN
1107 hr_utility.TRACE ('ABS:COMPLETED create_transactions for CREATE records');
1108 END IF;
1109
1110 FORALL l_tx_index IN l_create_id_tab.FIRST .. l_create_id_tab.LAST
1111 DELETE FROM hxc_abs_ret_temp
1112 WHERE time_building_block_id = l_create_id_tab(l_tx_index);
1113
1114 IF g_debug THEN
1115 hr_utility.set_location('ABS:Processing '||l_proc, 170);
1116 END IF;
1117
1118 END IF;
1119
1120 l_create_id_tab.DELETE;
1121 l_create_ovn_tab.DELETE;
1122
1123
1124
1125 -- Fetch all DELETE absence records from TEMP table which are successfully sent to Absences
1126 -- Make an entry for these retrieved Absence details in TXN tables.
1127 IF (l_delete_id_tab.COUNT > 0)
1128 THEN
1129
1130 IF g_debug THEN
1131 hr_utility.TRACE ('ABS:CALLING create_transactions for DELETE records - '||l_delete_id_tab.COUNT);
1132 END IF;
1133
1134 create_transactions (l_delete_id_tab,
1135 l_delete_ovn_tab,
1136 'SUCCESS',
1137 g_success_delete_msg
1138 );
1139
1140 IF g_debug THEN
1141 hr_utility.TRACE ('ABS:COMPLETED create_transactions for DELETE records');
1142 END IF;
1143
1144 FORALL l_tx_index IN l_delete_id_tab.FIRST .. l_delete_id_tab.LAST
1145 DELETE FROM hxc_abs_ret_temp
1146 WHERE time_building_block_id = l_delete_id_tab(l_tx_index);
1147
1148 IF g_debug THEN
1149 hr_utility.set_location('ABS:Processing '||l_proc, 180);
1150 END IF;
1151
1152 END IF;
1153
1154 l_delete_id_tab.DELETE;
1155 l_delete_ovn_tab.DELETE;
1156
1157 IF g_debug THEN
1158 hr_utility.set_location('ABS:Processing '||l_proc, 190);
1159 END IF;
1160
1161
1162 IF g_debug THEN
1163 hr_utility.set_location('ABS:Leaving '||l_proc, 200);
1164 END IF;
1165
1166
1167
1168 END IF; -- end if for g_all_id_tab.COUNT <> 0
1169 END IF; -- endif for if2
1170
1171 IF g_debug THEN
1172 hr_utility.trace('ABS: p_tc_status ::'||p_tc_status);
1173 hr_utility.trace('ABS: p_resource_id ::'||p_resource_id);
1174 hr_utility.trace('ABS: p_tc_start ::'||p_tc_start);
1175 hr_utility.trace('ABS: p_tc_stop ::'||p_tc_stop);
1176 END IF;
1177
1178 IF p_tc_status <> 'DELETED' THEN -- Added for Bug 12533914
1179
1180 IF g_debug THEN
1181 hr_utility.trace('ABS: Pick up timecard id to process transferred_to column');
1182 hr_utility.trace('ABS: l_timecard_id ::'||l_timecard_id);
1183 hr_utility.trace('ABS: l_timecard_ovn ::'||l_timecard_ovn);
1184 END IF;
1185
1186 -- Bug 9747820
1187 -- Pick up the timecard id and process
1188 -- transferred_to column
1189 SELECT timecard_id,
1190 timecard_ovn
1191 INTO l_timecard_id,
1192 l_timecard_ovn
1193 FROM hxc_timecard_summary
1194 WHERE resource_id = p_resource_id
1195 AND start_time = p_tc_start
1196 AND stop_time = TRUNC(p_tc_stop) + 1 - (1/86400) ;
1197
1198
1199 IF g_debug THEN
1200 hr_utility.trace('ABS: l_timecard_id ::'||l_timecard_id);
1201 hr_utility.trace('ABS: l_timecard_ovn ::'||l_timecard_ovn);
1202 hr_utility.trace('ABS: call to hxc_timecard_summary_pkg.update_transferred_to');
1203 END IF;
1204
1205 hxc_timecard_summary_pkg.update_transferred_to(l_timecard_id,
1206 l_timecard_ovn,
1207 g_retrieval_process_id);
1208 END IF; -- IF p_tc_status <> 'DELETED' THEN
1209
1210 END IF; -- endif for if1 --- g_abs_integ_enabled = 'Y'
1211
1212 IF g_debug THEN
1213 hr_utility.trace('ABS: End of post_absences..');
1214 END IF;
1215
1216 EXCEPTION
1217
1218 WHEN ret_rules_not_set
1219 THEN
1220 IF g_debug THEN
1221 hr_utility.TRACE ('ABS:EXCEPTION IN POST_ABSENCES - Retrieval Rules not set');
1222 END IF;
1223
1224 hxc_timecard_message_helper.addErrorToCollection
1225 (p_messages
1226 ,'HXC_GNUTL_NO_RET_RULE_FOR_RET'
1227 ,hxc_timecard.c_error
1228 ,null
1229 ,'EMP&'||nvl(l_emp_name, 'unknown')
1230 ,'HXC'
1231 ,null
1232 ,null
1233 ,null
1234 ,null );
1235
1236 addTkError(l_emp_name);
1237
1238 WHEN OTHERS
1239 THEN
1240 IF g_debug THEN
1241 hr_utility.TRACE ('ABS:EXCEPTION RAISED FROM POST_ABSENCES');
1242 END IF;
1243 -- Bug 9394444
1244 -- Added this backtrace to error stack because
1245 -- we are calling an OTHERS exception.
1246 -- Removed all other OTHERS exception blocks because that was
1247 -- either way unnecessary.
1248 hr_utility.trace(dbms_utility.format_error_backtrace);
1249
1250 IF (g_all_id_tab.COUNT > 0) THEN
1251 create_transactions (p_tbb_id => g_all_id_tab,
1252 p_tbb_ovn => g_all_ovn_tab,
1253 p_status => 'ERRORS',
1254 p_description => SUBSTR (SQLERRM, 1, 2000 ) || g_fail_create_msg
1255 );
1256
1257
1258 DELETE FROM hxc_abs_ret_temp
1259 WHERE resource_id = g_resource_id
1260 AND trunc(day_start) >= trunc(g_tc_start)
1261 AND trunc(day_stop) <= trunc(g_tc_stop);
1262
1263 END IF;
1264
1265 hxc_timecard_message_helper.addErrorToCollection
1266 (p_messages
1267 ,nvl(g_error_message, 'HXC_ABS_RET_FAILED')
1268 ,hxc_timecard.c_error
1269 ,null
1270 ,'EMP_NAME&'||nvl(l_emp_name, 'unknown')
1271 ,'HXC'
1272 ,null
1273 ,null
1274 ,null
1275 ,null );
1276
1277 addTkError(l_emp_name);
1278
1279
1280 END post_absences;
1281
1282 /*********CALLED PROCEDURES**********/
1283
1284
1285 -- Procedure to Create Absences in HR module and update HXC_ABS_CO_DETAILS with the latest absence info.
1286
1287 PROCEDURE create_absences (
1288 p_absences IN hxc_abs_retrieval_pkg.t_absences,
1289 p_uom IN VARCHAR2
1290 )
1291 AS
1292 CURSOR get_create_abs_details (c_tbb_id NUMBER)
1293 IS
1294 SELECT time_building_block_id,
1295 object_version_number,
1296 trunc(day_start) day_start,
1297 trunc(day_stop) day_stop
1298 FROM hxc_abs_ret_temp temp
1299 START WITH time_building_block_id = c_tbb_id
1300 CONNECT BY PRIOR time_building_block_id = link_time_building_block_id;
1301
1302 l_id_tab NUMTAB;
1303 l_ovn_tab NUMTAB;
1304
1305 l_day_start_tab DATETAB;
1306 l_day_stop_tab DATETAB;
1307
1308 l_element_type_id NUMBER;
1309 l_in_time DATE;
1310 l_out_time DATE;
1311 l_absence_attendance_type_id NUMBER;
1312 l_abs_ix NUMBER;
1313 l_absence_hours NUMBER := NULL;
1314 l_absence_days NUMBER;
1315 l_occurrence NUMBER;
1316 l_object_version_number NUMBER;
1317 l_absence_attendance_id NUMBER;
1318 l_dur_dys_less_warning BOOLEAN;
1319 l_dur_hrs_less_warning BOOLEAN;
1320 l_exceeds_pto_entit_warning BOOLEAN;
1321 l_exceeds_run_total_warning BOOLEAN;
1322 l_dur_overwritten_warning BOOLEAN;
1323 l_abs_day_after_warning BOOLEAN;
1324 l_abs_overlap_warning BOOLEAN;
1325 l_time_start VARCHAR2 (10) := NULL;
1326 l_time_end VARCHAR2 (10) := NULL;
1327 l_day_start DATE;
1328 l_day_stop DATE;
1329
1330 l_proc VARCHAR2(100);
1331
1332 --set_to_view_only_c EXCEPTION;
1333
1334 BEGIN
1335
1336 g_debug := hr_utility.debug_enabled;
1337
1338 IF g_debug THEN
1339 l_proc := g_package||'create_absences';
1340 hr_utility.set_location('ABS:Processing '||l_proc, 200);
1341 END IF;
1342
1343 IF g_debug THEN
1344 hr_utility.TRACE ('ABS:p_uom = ' || p_uom);
1345 END IF;
1346
1347 IF (p_uom = 'D')
1348 THEN
1349 l_absence_hours := NULL;
1350 ELSE
1351 l_absence_days := NULL;
1352 END IF;
1353
1354 l_abs_ix := p_absences.FIRST;
1355
1356 LOOP
1357
1358 -- Removed this check for bug 8932359
1359 /* IF (is_view_only(p_absences (l_abs_ix).absence_attendance_type_id)) THEN
1360 IF g_debug THEN
1361 hr_utility.trace('ABS:This Absence Type is changed to VIEW ONLY before retrieval - Wrong Setup');
1362 END IF;
1363
1364 RAISE set_to_view_only_c;
1365
1366 END IF;*/
1367
1368 IF (p_uom = 'D')
1369 THEN
1370 l_day_start := TRUNC (p_absences (l_abs_ix).day_start);
1371 l_day_stop := TRUNC (p_absences (l_abs_ix).day_stop);
1372 l_absence_days := (l_day_stop - l_day_start) + 1;
1373
1374 ELSE
1375 l_absence_hours := ( p_absences (l_abs_ix).out_time - p_absences (l_abs_ix).in_time ) * 24;
1376 l_day_start := TRUNC (p_absences (l_abs_ix).in_time);
1377 l_day_stop := TRUNC (p_absences (l_abs_ix).out_time);
1378 l_time_start := TO_CHAR (p_absences (l_abs_ix).in_time, 'HH24:MI');
1379 l_time_end := TO_CHAR (p_absences (l_abs_ix).out_time, 'HH24:MI');
1380 END IF;
1381
1382 l_absence_attendance_type_id := p_absences (l_abs_ix).absence_attendance_type_id;
1383 l_element_type_id := p_absences (l_abs_ix).element_type_id;
1384 l_in_time := p_absences (l_abs_ix).in_time;
1385 l_out_time := p_absences (l_abs_ix).out_time;
1386
1387 IF g_debug THEN
1388 hr_utility.TRACE ('ABS: Parameters passed to HR CREATE API');
1389 hr_utility.TRACE ('ABS:person_id = ' || p_absences (l_abs_ix).resource_id);
1390 hr_utility.TRACE ('ABS:g_business_group_id = ' || g_business_group_id);
1391 hr_utility.TRACE ('ABS:absence_attendance_type_id = ' || l_absence_attendance_type_id);
1392 hr_utility.TRACE ('ABS:l_day_start = ' || l_day_start);
1393 hr_utility.TRACE ('ABS:l_time_start = ' || l_time_start);
1394 hr_utility.TRACE ('ABS:l_day_stop = ' || l_day_stop);
1395 hr_utility.TRACE ('ABS:l_time_end = ' || l_time_end);
1396 hr_utility.TRACE ('ABS:l_absence_days = ' || l_absence_days);
1397 hr_utility.TRACE ('ABS:l_absence_hours = ' || l_absence_hours);
1398 hr_utility.TRACE ('ABS:Calling HR CREATE API');
1399 END IF;
1400
1401
1402 -- send data to absences
1403 hr_person_absence_api.create_person_absence
1404 (p_validate => FALSE,
1405 p_effective_date => SYSDATE,
1406 p_person_id => p_absences (l_abs_ix).resource_id,
1407 p_business_group_id => g_business_group_id,
1408 p_absence_attendance_type_id => l_absence_attendance_type_id,
1409 p_date_notification => SYSDATE,
1410 p_date_start => l_day_start,
1411 p_time_start => l_time_start,
1412 p_date_end => l_day_stop,
1413 p_time_end => l_time_end,
1414 p_absence_days => l_absence_days,
1415 p_absence_hours => l_absence_hours,
1416 p_program_application_id => 809,
1417 p_called_from => 809,
1418 p_absence_attendance_id => l_absence_attendance_id,
1419 p_object_version_number => l_object_version_number,
1420 p_occurrence => l_occurrence,
1421 p_dur_dys_less_warning => l_dur_dys_less_warning,
1422 p_dur_hrs_less_warning => l_dur_hrs_less_warning,
1423 p_exceeds_pto_entit_warning => l_exceeds_pto_entit_warning,
1424 p_exceeds_run_total_warning => l_exceeds_run_total_warning,
1425 p_dur_overwritten_warning => l_dur_overwritten_warning,
1426 p_abs_day_after_warning => l_abs_day_after_warning,
1427 p_abs_overlap_warning => l_abs_overlap_warning
1428 );
1429
1430 IF g_debug THEN
1431 hr_utility.TRACE ('ABS:completed HR CREATE API');
1432 hr_utility.TRACE ('ABS:Created Absence Attendance Id - '||l_absence_attendance_id);
1433 END IF;
1434
1435 OPEN get_create_abs_details (p_absences (l_abs_ix).time_building_block_id);
1436
1437 FETCH get_create_abs_details BULK COLLECT INTO l_id_tab ,
1438 l_ovn_tab ,
1439 l_day_start_tab ,
1440 l_day_stop_tab ;
1441
1442
1443 CLOSE get_create_abs_details;
1444
1445 IF l_id_tab.COUNT > 0
1446 THEN
1447
1448 -- update the absence_attendance_id column for the details sent as SINGLE continuous absence entry
1449 FORALL l_index IN l_id_tab.FIRST .. l_id_tab.LAST
1450 UPDATE hxc_abs_ret_temp
1451 SET retrieval_status = 'SUCCESS',
1452 absence_attendance_id = l_absence_attendance_id
1453 WHERE time_building_block_id = l_id_tab(l_index)
1454 AND absence_attendance_id IS NULL;
1455
1456 IF g_debug THEN
1457 hr_utility.TRACE ('ABS:updated temp table = ' || SQL%ROWCOUNT);
1458 END IF;
1459
1460 -- update absence details if it is already present in CO table
1461 FORALL l_index IN l_id_tab.FIRST .. l_id_tab.LAST
1462 UPDATE hxc_abs_co_details
1463 SET object_version_number = l_ovn_tab(l_index),
1464 absence_type_id = l_absence_attendance_type_id,
1465 absence_attendance_id = l_absence_attendance_id,
1466 element_type_id = l_element_type_id,
1467 uom = SUBSTR(p_uom,1,1),
1468 measure = nvl(l_absence_hours, 1),
1469 start_date = nvl(l_in_time, l_day_start_tab(l_index)),
1470 end_date = nvl(l_out_time, l_day_stop_tab(l_index))
1471 WHERE time_building_block_id = l_id_tab(l_index)
1472 AND stage = 'RET';
1473
1474
1475 FORALL l_index IN l_id_tab.FIRST .. l_id_tab.LAST
1476 UPDATE hxc_abs_co_details
1477 SET absence_attendance_id = l_absence_attendance_id
1478 WHERE time_building_block_id = l_id_tab(l_index)
1479 AND stage <> 'RET';
1480
1481 IF g_debug THEN
1482 hr_utility.TRACE ('ABS:Updated HXC_ABS_CO_DETAILS');
1483 END IF;
1484
1485 END IF;
1486
1487 -- HXC_ABS_CO_DETAILS should contain only unique records for each detail id belonging to the timecard
1488 -- inserting fresh unplanned absence records
1489 INSERT INTO hxc_abs_co_details
1490 (TIME_BUILDING_BLOCK_ID,
1491 OBJECT_VERSION_NUMBER ,
1492 ABSENCE_TYPE_ID ,
1493 ABSENCE_ATTENDANCE_ID ,
1494 ELEMENT_TYPE_ID ,
1495 UOM ,
1496 MEASURE ,
1497 START_DATE ,
1498 END_DATE ,
1499 STAGE ,
1500 RESOURCE_ID ,
1501 START_TIME ,
1502 STOP_TIME)
1503 (SELECT time_building_block_id,
1504 object_version_number,
1505 absence_attendance_type_id,
1506 absence_attendance_id,
1507 element_type_id,
1508 uom,
1509 nvl(l_absence_hours, 1),
1510 NVL (in_time, TRUNC (day_start)),
1511 NVL (out_time, TRUNC (day_stop)),
1512 'RET',
1513 g_resource_id,
1514 g_tc_start,
1515 g_tc_stop
1516 FROM hxc_abs_ret_temp temp
1517 WHERE temp.absence_attendance_id = l_absence_attendance_id
1518 AND temp.retrieval_status = 'SUCCESS'
1519 AND NOT EXISTS (
1520 SELECT 1
1521 FROM hxc_abs_co_details absco
1522 WHERE absco.time_building_block_id = temp.time_building_block_id));
1523
1524 IF g_debug THEN
1525 hr_utility.TRACE ('ABS:Created new records in CO table = ' || SQL%ROWCOUNT);
1526 END IF;
1527
1528 -- update the ELEMENT ENTRY with the COST CENTER information if the element is costed in HR
1529 IF (p_absences (l_abs_ix).cost_allocation_keyflex_id <> 0)
1530 THEN
1531 update_cost_center
1532 (l_absence_attendance_id,
1533 p_absences (l_abs_ix).cost_allocation_keyflex_id
1534 );
1535 END IF;
1536
1537 IF g_debug THEN
1538 hr_utility.trace('ABS:Process next');
1539 END IF;
1540
1541 l_abs_ix := p_absences.NEXT (l_abs_ix);
1542 EXIT WHEN NOT p_absences.EXISTS (l_abs_ix);
1543 END LOOP;
1544
1545 IF g_debug THEN
1546 hr_utility.set_location('ABS:Leaving '||l_proc, 210);
1547 END IF;
1548
1549
1550 -- Commented code for bug 8932359
1551 /*WHEN set_to_view_only_c
1552 THEN
1553 IF g_debug THEN
1554 hr_utility.TRACE ('ABS:EXCEPTION IN CREATE_ABSENCES - set_to_view_only_c');
1555 END IF;
1556
1557 g_error_message := NULL;
1558 g_error_message := 'HXC_ABS_VIEW_ONLY';
1559
1560 RAISE; */
1561
1562
1563 END create_absences;
1564
1565
1566
1567
1568 -- This procedure is used to recreate absences which are not edited but part of a deleted absence attendance id
1569 -- Scenario : When a absence record holds absences info from 01-jan-2009 to 10-jan-2009
1570 -- After prepopulation, if 3-jan-2009 and 7-jan-2009 only are deleted/updated, then delete the single absence entry in HR and
1571 -- recreate absences for the remaining untouched days
1572
1573 PROCEDURE recreate_absences (
1574 p_absences IN hxc_abs_retrieval_pkg.t_absences_details,
1575 p_uom IN VARCHAR2,
1576 p_old_absence_attendance_id IN NUMBER
1577 )
1578 AS
1579 l_abs_ix NUMBER;
1580 l_absence_hours NUMBER := NULL;
1581 l_absence_days NUMBER;
1582 l_occurrence NUMBER;
1583 l_object_version_number NUMBER;
1584 l_new_absence_attendance_id NUMBER;
1585 l_dur_dys_less_warning BOOLEAN;
1586 l_dur_hrs_less_warning BOOLEAN;
1587 l_exceeds_pto_entit_warning BOOLEAN;
1588 l_exceeds_run_total_warning BOOLEAN;
1589 l_dur_overwritten_warning BOOLEAN;
1590 l_abs_day_after_warning BOOLEAN;
1591 l_abs_overlap_warning BOOLEAN;
1592 l_time_start VARCHAR2 (10) := NULL;
1593 l_time_end VARCHAR2 (10) := NULL;
1594 l_date_start DATE;
1595 l_date_end DATE;
1596
1597 l_proc VARCHAR2(100);
1598
1599 BEGIN
1600
1601 g_debug := hr_utility.debug_enabled;
1602
1603 IF g_debug THEN
1604 l_proc := g_package||'recreate_absences';
1605 hr_utility.set_location('ABS:Processing '||l_proc, 300);
1606 END IF;
1607
1608 IF (p_uom = 'D')
1609 THEN
1610 l_absence_hours := NULL;
1611 ELSE
1612 l_absence_days := NULL;
1613 END IF;
1614
1615 l_abs_ix := p_absences.FIRST;
1616
1617 LOOP
1618 l_date_start := TRUNC (p_absences (l_abs_ix).date_start);
1619 l_date_end := TRUNC (p_absences (l_abs_ix).date_end);
1620
1621 IF (p_uom = 'D')
1622 THEN
1623 l_absence_days := (l_date_end - l_date_start) + 1;
1624 ELSE
1625 l_absence_hours :=
1626 ( TO_DATE ( TO_CHAR (l_date_end, 'DD-MON-YYYY')
1627 || p_absences (l_abs_ix).time_end,
1628 'DD-MON-YYYY HH24:MI:SS'
1629 )
1630 - TO_DATE ( TO_CHAR (l_date_start, 'DD-MON-YYYY')
1631 || p_absences (l_abs_ix).time_start,
1632 'DD-MON-YYYY HH24:MI:SS'
1633 )
1634 ) * 24;
1635 l_time_start := p_absences (l_abs_ix).time_start;
1636 l_time_end := p_absences (l_abs_ix).time_end;
1637 END IF;
1638
1639 IF g_debug THEN
1640 hr_utility.TRACE ('ABS: Recreation');
1641 hr_utility.TRACE ('ABS: Parameters passed to HR CREATE API');
1642 hr_utility.TRACE ('ABS:person_id = ' || p_absences (l_abs_ix).person_id);
1643 hr_utility.TRACE ('ABS:g_business_group_id = ' || g_business_group_id);
1644 hr_utility.TRACE ('ABS:absence_attendance_type_id = ' || p_absences (l_abs_ix).absence_attendance_type_id);
1645 hr_utility.TRACE ('ABS:l_day_start = ' || l_date_start);
1646 hr_utility.TRACE ('ABS:l_time_start = ' || l_time_start);
1647 hr_utility.TRACE ('ABS:l_day_stop = ' || l_date_end);
1648 hr_utility.TRACE ('ABS:l_time_end = ' || l_time_end);
1649 hr_utility.TRACE ('ABS:l_absence_days = ' || l_absence_days);
1650 hr_utility.TRACE ('ABS:l_absence_hours = ' || l_absence_hours);
1651 hr_utility.TRACE ('ABS:Calling HR CREATE API');
1652 END IF;
1653
1654 -- send data to absences
1655 hr_person_absence_api.create_person_absence
1656 (p_validate => FALSE,
1657 p_effective_date => SYSDATE,
1658 p_person_id => p_absences (l_abs_ix).person_id,
1659 p_business_group_id => g_business_group_id,
1660 p_absence_attendance_type_id => p_absences (l_abs_ix).absence_attendance_type_id,
1661 p_date_notification => SYSDATE,
1662 p_date_start => l_date_start,
1663 p_time_start => l_time_start,
1664 p_date_end => l_date_end,
1665 p_time_end => l_time_end,
1666 p_absence_days => l_absence_days,
1667 p_absence_hours => l_absence_hours,
1668 p_program_application_id => 809,
1669 p_called_from => 809,
1670 p_absence_attendance_id => l_new_absence_attendance_id,
1671 p_object_version_number => l_object_version_number,
1672 p_occurrence => l_occurrence,
1673 p_dur_dys_less_warning => l_dur_dys_less_warning,
1674 p_dur_hrs_less_warning => l_dur_hrs_less_warning,
1675 p_exceeds_pto_entit_warning => l_exceeds_pto_entit_warning,
1676 p_exceeds_run_total_warning => l_exceeds_run_total_warning,
1677 p_dur_overwritten_warning => l_dur_overwritten_warning,
1678 p_abs_day_after_warning => l_abs_day_after_warning,
1679 p_abs_overlap_warning => l_abs_overlap_warning
1680 );
1681
1682
1683 IF g_debug THEN
1684 hr_utility.TRACE ('ABS:completed HR CREATE API');
1685 hr_utility.TRACE ('ABS:Created Absence Attendance Id - '||l_new_absence_attendance_id);
1686 END IF;
1687
1688 -- update the CO table with the new absence attendance id for those untouched absence details
1689 -- which are not edited but recreated
1690
1691 UPDATE hxc_abs_co_details
1692 SET absence_attendance_id = l_new_absence_attendance_id
1693 WHERE TRUNC (start_date) BETWEEN TRUNC (l_date_start)
1694 AND TRUNC (l_date_end)
1695 AND absence_attendance_id = p_old_absence_attendance_id;
1696
1697 l_abs_ix := p_absences.NEXT (l_abs_ix);
1698 EXIT WHEN NOT p_absences.EXISTS (l_abs_ix);
1699 END LOOP;
1700
1701 IF g_debug THEN
1702 hr_utility.set_location('ABS:Leaving '||l_proc, 310);
1703 END IF;
1704
1705
1706 END recreate_absences;
1707
1708
1709
1710
1711 -- delete absences from HR for updated/modified TC absences details
1712 PROCEDURE delete_absences (
1713 p_absence_attendance_id IN NUMBER,
1714 p_edited_days IN hxc_abs_retrieval_pkg.t_edited_days,
1715 p_uom IN VARCHAR2
1716 )
1717 AS
1718 CURSOR get_absences_details_cur
1719 IS
1720 SELECT absatt.absence_attendance_type_id,
1721 trunc(absatt.date_start),
1722 trunc(absatt.date_end),
1723 absatt.time_start,
1724 absatt.time_end, absatt.person_id,
1725 absatt.program_application_id,
1726 hate.edit_flag
1727 FROM per_absence_attendances absatt,
1728 hxc_absence_type_elements hate
1729 WHERE absatt.absence_attendance_id = p_absence_attendance_id
1730 AND absatt.absence_attendance_type_id = hate.absence_attendance_type_id;
1731
1732 CURSOR co_details_cur
1733 IS
1734 SELECT trunc(start_date) start_date,
1735 trunc(end_date) end_date,
1736 to_char(start_date, 'HH24:MI') time_start,
1737 to_char(end_date, 'HH24:MI') time_end,
1738 start_time,
1739 stop_time
1740 FROM hxc_abs_co_details absco
1741 WHERE absco.absence_attendance_id = p_absence_attendance_id
1742 AND absco.time_building_block_id > 0
1743 AND NOT EXISTS(select 1 from hxc_abs_ret_temp temp
1744 where temp.time_building_block_id = absco.time_building_block_id)
1745 ORDER BY 1 asc ;
1746
1747 TYPE t_co_details IS TABLE OF co_details_cur%ROWTYPE;
1748 l_co_details t_co_details;
1749
1750 l_absence_attendance_type_id per_absence_attendances.absence_attendance_type_id%TYPE;
1751 l_date_start per_absence_attendances.date_start%TYPE;
1752 l_date_end per_absence_attendances.date_end%TYPE;
1753 l_time_start per_absence_attendances.time_start%TYPE;
1754 l_time_end per_absence_attendances.time_end%TYPE;
1755 l_person_id per_absence_attendances.person_id%TYPE;
1756 l_program_application_id per_absence_attendances.program_application_id%TYPE;
1757 l_edit_flag hxc_absence_type_elements.edit_flag%TYPE;
1758 l_absences_details t_absences_details;
1759 l_abs_index BINARY_INTEGER := 0;
1760 l_left NUMBER := 0;
1761 l_right NUMBER := 0;
1762 l_orig_date_start DATE;
1763 l_orig_date_end DATE;
1764 l_temp_date DATE;
1765 l_old_tc_start DATE;
1766 l_old_tc_stop DATE;
1767
1768 l_proc VARCHAR2(100);
1769 pref_changed_before_ret EXCEPTION;
1770 --set_to_view_only_d EXCEPTION;
1771
1772 BEGIN
1773
1774 g_debug := hr_utility.debug_enabled;
1775
1776 IF g_debug THEN
1777 l_proc := g_package||'delete_absences';
1778 hr_utility.set_location('ABS:Processing '||l_proc, 400);
1779 END IF;
1780
1781 IF g_debug THEN
1782 hr_utility.TRACE ( 'ABS:Process DELETE for Absence attendance id = ' || p_absence_attendance_id );
1783 END IF;
1784
1785 -- get absences information from per_absence_attendances table
1786 OPEN get_absences_details_cur;
1787
1788 FETCH get_absences_details_cur
1789 INTO l_absence_attendance_type_id,
1790 l_date_start,
1791 l_date_end,
1792 l_time_start,
1793 l_time_end,
1794 l_person_id,
1795 l_program_application_id,
1796 l_edit_flag;
1797
1798 IF get_absences_details_cur%NOTFOUND
1799 THEN
1800 IF g_debug THEN
1801 hr_utility.TRACE ('ABS:Absence record already deleted from HR');
1802 END IF;
1803
1804 RETURN;
1805 END IF;
1806
1807 CLOSE get_absences_details_cur;
1808
1809 IF g_debug THEN
1810 hr_utility.TRACE ('ABS:l_absence_attendance_type_id = '||l_absence_attendance_type_id);
1811 hr_utility.TRACE ('ABS:l_date_start = '||l_date_start);
1812 hr_utility.TRACE ('ABS:l_date_end = '||l_date_end);
1813 hr_utility.TRACE ('ABS:l_time_start = '||l_time_start);
1814 hr_utility.TRACE ('ABS:l_time_end = '||l_time_end);
1815 hr_utility.TRACE ('ABS:l_person_id = '||l_person_id);
1816 hr_utility.TRACE ('ABS:l_program_application_id = '||l_program_application_id);
1817 hr_utility.TRACE ('ABS:l_edit_flag = '||l_edit_flag);
1818 END IF;
1819
1820 IF g_debug THEN
1821 hr_utility.set_location('ABS:Processing '||l_proc, 410);
1822 END IF;
1823
1824 -- delete absences attached to HOURS based absences created by OTL from absences module
1825 IF (p_uom IN ('H', 'HOURS') AND l_program_application_id = '809')
1826 THEN
1827
1828 IF g_debug THEN
1829 hr_utility.TRACE ('ABS:Call HR DELETE API for absence (hours) = ' || p_absence_attendance_id );
1830 END IF;
1831
1832 hr_person_absence_api.delete_person_absence
1833 (p_validate => FALSE,
1834 p_absence_attendance_id => p_absence_attendance_id,
1835 p_object_version_number => NULL,
1836 p_called_from => 809
1837 );
1838
1839 UPDATE hxc_abs_co_details
1840 SET absence_attendance_id = NULL
1841 WHERE absence_attendance_id = p_absence_attendance_id;
1842
1843 IF g_debug THEN
1844 hr_utility.TRACE ('ABS:Rows update (1) - ' || SQL%ROWCOUNT);
1845 END IF;
1846
1847 IF g_debug THEN
1848 hr_utility.set_location('ABS:Processing '||l_proc, 420);
1849 END IF;
1850
1851 UPDATE hxc_abs_ret_temp
1852 SET retrieval_status = 'SUCCESS'
1853 WHERE absence_attendance_id = p_absence_attendance_id;
1854
1855
1856 RETURN;
1857
1858 -- If there is a HOURS update,
1859 -- the new value would go through the regular Create_Absences picked up from TEMP
1860
1861 END IF;
1862
1863
1864 -- delete absences and recreate untouched absences
1865 IF (l_program_application_id = '809' AND p_uom IN('D', 'DAYS'))
1866 OR (l_program_application_id = '800' AND g_abs_prepop_edit = 'Y')
1867 THEN
1868
1869
1870 IF (l_edit_flag = 'N' and l_program_application_id = '800' and g_tc_status = 'DELETED')
1871 THEN
1872 IF g_debug THEN
1873 hr_utility.set_location('ABS:Processing '||l_proc, 425);
1874 END IF;
1875
1876 IF g_debug THEN
1877 hr_utility.trace('ABS:These prepop absences are set to View Only and not deleted from HR on timecard delete');
1878 END IF;
1879
1880 UPDATE hxc_abs_co_details
1881 SET absence_attendance_id = NULL
1882 WHERE absence_attendance_id = p_absence_attendance_id;
1883
1884 UPDATE hxc_abs_ret_temp
1885 SET retrieval_status = 'SUCCESS'
1886 WHERE absence_attendance_id = p_absence_attendance_id;
1887
1888 IF g_debug THEN
1889 hr_utility.set_location('ABS:Processing '||l_proc, 428);
1890 END IF;
1891
1892 RETURN;
1893
1894 END IF;
1895
1896
1897 IF g_debug THEN
1898 hr_utility.TRACE ('ABS:Call HR DELETE API for absence = ' || p_absence_attendance_id );
1899 END IF;
1900
1901 hr_person_absence_api.delete_person_absence
1902 (p_validate => FALSE,
1903 p_absence_attendance_id => p_absence_attendance_id,
1904 p_object_version_number => NULL,
1905 p_called_from => 809
1906 );
1907
1908 IF g_debug THEN
1909 hr_utility.TRACE ('ABS:Start Splitting the periods');
1910 END IF;
1911
1912 -- when recreating DAYS based absences with program application id = 809, it has to still be
1913 -- recreated as continuous entries whereas for HOURS it has to be recreated as entry for each day
1914 IF (p_uom in ('D', 'DAYS')) THEN
1915
1916 IF g_debug THEN
1917 hr_utility.TRACE ('ABS:Splitting days' );
1918 END IF;
1919 -- cut the right and left details from the current TC period, if it exists
1920 -- cutting the left
1921 IF (l_date_start < g_tc_start)
1922 THEN
1923 l_abs_index := l_abs_index + 1;
1924 l_absences_details (l_abs_index).absence_attendance_type_id := l_absence_attendance_type_id;
1925 l_absences_details (l_abs_index).date_start := l_date_start;
1926 l_absences_details (l_abs_index).date_end := TRUNC (g_tc_start - 1);
1927 l_absences_details (l_abs_index).time_start := l_time_start;
1928 l_absences_details (l_abs_index).time_end := l_time_end;
1929 l_absences_details (l_abs_index).person_id := l_person_id;
1930 l_absences_details (l_abs_index).program_application_id := l_program_application_id;
1931 l_left := 1;
1932 END IF;
1933
1934 IF g_debug THEN
1935 hr_utility.set_location('ABS:Processing '||l_proc, 430);
1936 END IF;
1937
1938 -- cutting the right
1939 IF (l_date_end > g_tc_stop)
1940 THEN
1941 l_abs_index := l_abs_index + 1;
1942 l_absences_details (l_abs_index).absence_attendance_type_id := l_absence_attendance_type_id;
1943 l_absences_details (l_abs_index).date_start := TRUNC (g_tc_stop + 1);
1944 l_absences_details (l_abs_index).date_end := l_date_end;
1945 l_absences_details (l_abs_index).time_start := l_time_start;
1946 l_absences_details (l_abs_index).time_end := l_time_end;
1947 l_absences_details (l_abs_index).person_id := l_person_id;
1948 l_absences_details (l_abs_index).program_application_id := l_program_application_id;
1949 l_right := 1;
1950 END IF;
1951
1952 IF g_debug THEN
1953 hr_utility.set_location('ABS:Processing '||l_proc, 440);
1954 END IF;
1955
1956 /*
1957 4 scenarios
1958 left right
1959 0 0, absence_id spreads within the TC period
1960 0 1, absence_id spreads across current and next TC period
1961 1 0, absence_id spreads across previous and current TC period
1962 1 1, absence_id spreads across previous, current and next TC periods
1963 */
1964 IF (l_left = 1 AND l_right = 1)
1965 THEN
1966 l_date_start := g_tc_start;
1967 l_date_end := g_tc_stop;
1968 END IF;
1969
1970 IF (l_left = 1 AND l_right = 0)
1971 THEN
1972 l_date_start := TRUNC (g_tc_start);
1973 l_date_end := l_date_end; -- no change
1974 END IF;
1975
1976 IF (l_left = 0 AND l_right = 1)
1977 THEN
1978 l_date_start := l_date_start; -- no change
1979 l_date_end := TRUNC (g_tc_stop);
1980 END IF;
1981
1982 IF (l_left = 0 AND l_right = 0)
1983 THEN
1984 l_date_start := l_date_start; -- no change
1985 l_date_end := l_date_end; -- no change
1986 END IF;
1987
1988 IF g_debug THEN
1989 hr_utility.set_location('ABS:Processing '||l_proc, 440);
1990 END IF;
1991
1992
1993 IF g_debug THEN
1994 hr_utility.TRACE ('ABS:Process within timecard period');
1995 hr_utility.TRACE ('ABS:l_date_start = ' || l_date_start);
1996 hr_utility.TRACE ('ABS:l_date_end = ' || l_date_end);
1997 hr_utility.TRACE ('ABS:p_edited_days.count = ' || p_edited_days.COUNT);
1998 END IF;
1999
2000 -- split based on the days which are updated/deleted within the timecard period
2001 IF (p_edited_days.COUNT > 0)
2002 THEN
2003 FOR l_days IN p_edited_days.FIRST .. p_edited_days.LAST
2004 LOOP
2005 IF (l_date_start < p_edited_days (l_days).day_start)
2006 THEN
2007 hr_utility.TRACE ('IF - 1');
2008 l_abs_index := l_abs_index + 1;
2009 l_absences_details (l_abs_index).absence_attendance_type_id := l_absence_attendance_type_id;
2010 l_absences_details (l_abs_index).date_start := l_date_start;
2011 l_absences_details (l_abs_index).date_end := p_edited_days (l_days).day_stop - 1;
2012 l_absences_details (l_abs_index).time_start := l_time_start;
2013 l_absences_details (l_abs_index).time_end := l_time_end;
2014 l_absences_details (l_abs_index).person_id := l_person_id;
2015 l_absences_details (l_abs_index).program_application_id := l_program_application_id;
2016 END IF;
2017
2018 l_date_start := p_edited_days (l_days).day_start + 1;
2019
2020 END LOOP;
2021
2022 IF (l_date_start <= l_date_end)
2023 THEN
2024 hr_utility.TRACE ('IF - 2');
2025 l_abs_index := l_abs_index + 1;
2026 l_absences_details (l_abs_index).absence_attendance_type_id := l_absence_attendance_type_id;
2027 l_absences_details (l_abs_index).date_start := l_date_start;
2028 l_absences_details (l_abs_index).date_end := l_date_end;
2029 l_absences_details (l_abs_index).time_start := l_time_start;
2030 l_absences_details (l_abs_index).time_end := l_time_end;
2031 l_absences_details (l_abs_index).person_id := l_person_id;
2032 l_absences_details (l_abs_index).program_application_id := l_program_application_id;
2033 END IF;
2034 END IF; -- p_edited_days.COUNT > 0
2035
2036 IF g_debug THEN
2037 hr_utility.set_location('ABS:Processing '||l_proc, 450);
2038 END IF;
2039
2040 END IF; -- p_uom in ('D', 'DAYS')
2041
2042 -- when recreating HOURS based absences with program application id = 809, it has to be
2043 -- recreated for each day and hence the logic differs for HOURS and DAYS
2044 IF (p_uom in ('H', 'HOURS')) THEN
2045
2046 IF g_debug THEN
2047 hr_utility.TRACE ('ABS:Splitting hours' );
2048 END IF;
2049
2050 l_orig_date_start := l_date_start;
2051 l_orig_date_end := l_date_end;
2052
2053 OPEN co_details_cur;
2054 FETCH co_details_cur BULK COLLECT INTO l_co_details;
2055 CLOSE co_details_cur;
2056
2057
2058 IF g_debug THEN
2059 hr_utility.TRACE ('ABS:l_co_details.COUNT = '||l_co_details.COUNT);
2060 END IF;
2061
2062 IF (l_co_details.COUNT = 0) THEN
2063 RETURN;
2064 END IF;
2065
2066 -- recreate absences for periods which fall to the left of the first start_time in l_co_details
2067 IF l_date_start < l_co_details (l_co_details.FIRST).start_time THEN
2068
2069 IF g_debug THEN
2070 hr_utility.TRACE ('ABS: Processing left for HOURS');
2071 END IF;
2072
2073 LOOP
2074 EXIT WHEN l_date_start = l_co_details (l_co_details.FIRST).start_time;
2075
2076 l_abs_index := l_abs_index + 1;
2077
2078 l_absences_details (l_abs_index).absence_attendance_type_id := l_absence_attendance_type_id;
2079 l_absences_details (l_abs_index).date_start := l_date_start;
2080 l_absences_details (l_abs_index).date_end := l_date_start;
2081 l_absences_details (l_abs_index).time_start := '00:00';
2082 l_absences_details (l_abs_index).time_end := '23:59';
2083 l_absences_details (l_abs_index).person_id := l_person_id;
2084 l_absences_details (l_abs_index).program_application_id := l_program_application_id;
2085
2086 l_date_start := l_date_start + 1;
2087 END LOOP;
2088 END IF;
2089
2090 IF g_debug THEN
2091 hr_utility.set_location('ABS:Processing '||l_proc, 451);
2092 END IF;
2093
2094 -- recreate absences for periods which fall to the right of the last stop_time in l_co_details
2095 IF l_date_end > l_co_details (l_co_details.LAST).stop_time THEN
2096
2097 IF g_debug THEN
2098 hr_utility.TRACE ('ABS: Processing right for HOURS');
2099 END IF;
2100
2101 LOOP
2102 EXIT WHEN l_date_end = l_co_details (l_co_details.LAST).stop_time;
2103
2104 l_abs_index := l_abs_index + 1;
2105
2106 l_absences_details (l_abs_index).absence_attendance_type_id := l_absence_attendance_type_id;
2107 l_absences_details (l_abs_index).date_start := l_date_end;
2108 l_absences_details (l_abs_index).date_end := l_date_end;
2109 l_absences_details (l_abs_index).time_start := '00:00';
2110 l_absences_details (l_abs_index).time_end := '23:59';
2111 l_absences_details (l_abs_index).person_id := l_person_id;
2112 l_absences_details (l_abs_index).program_application_id := l_program_application_id;
2113
2114 l_date_end := l_date_end - 1;
2115 END LOOP;
2116 END IF;
2117
2118 IF g_debug THEN
2119 hr_utility.set_location('ABS:Processing '||l_proc, 452);
2120 END IF;
2121
2122 -- recreate absences with for the time periods in l_co_details
2123 IF g_debug THEN
2124 hr_utility.TRACE ('ABS: Processing within CO for HOURS');
2125 END IF;
2126
2127 FOR l_days IN l_co_details.FIRST .. l_co_details.LAST
2128 LOOP
2129 l_abs_index := l_abs_index + 1;
2130
2131 l_absences_details (l_abs_index).absence_attendance_type_id := l_absence_attendance_type_id;
2132 l_absences_details (l_abs_index).date_start := l_co_details (l_days).start_date;
2133 l_absences_details (l_abs_index).date_end := l_co_details (l_days).end_date;
2134 l_absences_details (l_abs_index).time_start := l_co_details (l_days).time_start;
2135 l_absences_details (l_abs_index).time_end := l_co_details (l_days).time_end;
2136 l_absences_details (l_abs_index).person_id := l_person_id;
2137 l_absences_details (l_abs_index).program_application_id := l_program_application_id;
2138
2139 END LOOP;
2140
2141 IF g_debug THEN
2142 hr_utility.set_location('ABS:Processing '||l_proc, 453);
2143 END IF;
2144
2145 l_old_tc_start := l_co_details(l_co_details.FIRST).start_time;
2146 l_old_tc_stop := l_co_details(l_co_details.FIRST).stop_time;
2147
2148 -- recreate absences for left out time periods which fall within the first and last timecard
2149 -- period in l_co_details
2150 FOR l_days IN l_co_details.FIRST .. l_co_details.LAST
2151 LOOP
2152 IF (l_co_details(l_days).start_time - l_old_tc_stop > 1) THEN
2153
2154 IF g_debug THEN
2155 hr_utility.TRACE ('ABS: Processing missing time periods within CO for HOURS');
2156 END IF;
2157
2158 l_temp_date := l_old_tc_stop + 1;
2159
2160 LOOP
2161 EXIT WHEN l_co_details(l_days).start_time = l_temp_date;
2162
2163 l_abs_index := l_abs_index + 1;
2164
2165 l_absences_details (l_abs_index).absence_attendance_type_id := l_absence_attendance_type_id;
2166 l_absences_details (l_abs_index).date_start := l_temp_date;
2167 l_absences_details (l_abs_index).date_end := l_temp_date;
2168 l_absences_details (l_abs_index).time_start := '00:00';
2169 l_absences_details (l_abs_index).time_end := '23:59';
2170 l_absences_details (l_abs_index).person_id := l_person_id;
2171 l_absences_details (l_abs_index).program_application_id := l_program_application_id;
2172
2173 l_temp_date := l_temp_date + 1;
2174 END LOOP;
2175
2176
2177 END IF;
2178
2179 l_old_tc_start := l_co_details(l_days).start_time;
2180 l_old_tc_stop := l_co_details(l_days).stop_time;
2181
2182
2183
2184 END LOOP;
2185
2186 IF g_debug THEN
2187 hr_utility.set_location('ABS:Processing '||l_proc, 454);
2188 END IF;
2189
2190
2191 IF (l_absences_details.COUNT > 0) THEN
2192 IF l_absences_details(l_absences_details.FIRST).date_start = l_orig_date_start THEN
2193 l_absences_details (l_absences_details.FIRST).time_start := l_time_start;
2194 END IF;
2195
2196 IF l_absences_details(l_absences_details.LAST).date_end = l_orig_date_end THEN
2197 l_absences_details (l_absences_details.LAST).time_end := l_time_end;
2198 END IF;
2199 END IF;
2200
2201 IF g_debug THEN
2202 hr_utility.set_location('ABS:Processing '||l_proc, 455);
2203 END IF;
2204
2205 END IF; -- p_uom in ('H', 'HOURS')
2206
2207 IF g_debug THEN
2208 hr_utility.set_location('ABS:Processing '||l_proc, 458);
2209 END IF;
2210
2211
2212 -- delete absences details from absco table which are updated/deleted from Timecard
2213 UPDATE hxc_abs_co_details
2214 SET absence_attendance_id = NULL
2215 WHERE time_building_block_id IN (
2216 SELECT time_building_block_id
2217 FROM hxc_abs_ret_temp
2218 WHERE absence_attendance_id = p_absence_attendance_id
2219 AND absences_action = 'DELETE')
2220 OR time_building_block_id < 0
2221 AND absence_attendance_id = p_absence_attendance_id;
2222
2223 IF g_debug THEN
2224 hr_utility.set_location('ABS:Processing '||l_proc, 460);
2225 END IF;
2226
2227 -- periods which has to be recreated due to absence_attendance_id deletion
2228 IF l_absences_details.COUNT > 0
2229 THEN
2230 IF g_debug THEN
2231 hr_utility.TRACE ('ABS:split periods');
2232 END IF;
2233
2234 FOR l_abs_ix IN l_absences_details.FIRST .. l_absences_details.LAST
2235 LOOP
2236 IF g_debug THEN
2237 hr_utility.TRACE ('ABS:abs type id'||l_absences_details (l_abs_ix).absence_attendance_type_id );
2238 hr_utility.TRACE ('ABS:start date'||l_absences_details (l_abs_ix).date_start);
2239 hr_utility.TRACE ('ABS:end date'||l_absences_details (l_abs_ix).date_end);
2240 END IF;
2241 END LOOP;
2242
2243 -- call recreate_absences for the split periods
2244 IF g_debug THEN
2245 hr_utility.TRACE ('ABS:Calling recreate_absences proc');
2246 END IF;
2247
2248 recreate_absences (l_absences_details,
2249 p_uom,
2250 p_absence_attendance_id
2251 );
2252
2253 IF g_debug THEN
2254 hr_utility.TRACE ('ABS:Completed recreate_absences proc');
2255 END IF;
2256
2257 END IF;
2258
2259 UPDATE hxc_abs_ret_temp
2260 SET retrieval_status = 'SUCCESS'
2261 WHERE absence_attendance_id = p_absence_attendance_id;
2262
2263 IF g_debug THEN
2264 hr_utility.set_location('ABS:Processing '||l_proc, 470);
2265 END IF;
2266
2267 RETURN;
2268 END IF;
2269
2270 IF (l_program_application_id = '800' AND g_abs_prepop_edit = 'N')
2271 THEN
2272 IF (g_tc_status = 'DELETED') THEN
2273
2274 IF g_debug THEN
2275 hr_utility.trace('ABS:Absences with source HR are not allowed to be deleted on timecard delete');
2276 END IF;
2277
2278 UPDATE hxc_abs_co_details
2279 SET absence_attendance_id = NULL
2280 WHERE absence_attendance_id = p_absence_attendance_id;
2281
2282 UPDATE hxc_abs_ret_temp
2283 SET retrieval_status = 'SUCCESS'
2284 WHERE absence_attendance_id = p_absence_attendance_id;
2285
2286 IF g_debug THEN
2287 hr_utility.set_location('ABS:Processing '||l_proc, 480);
2288 END IF;
2289 ELSE
2290 IF g_debug THEN
2291 hr_utility.trace('ABS:This prepopulated absence was allowed to be edited in Timecard and later set to EDIT NOT ALLOWED before retrieval');
2292 hr_utility.trace('ABS:Change in preference setup before Absence Retrieval - WRONG SETUP');
2293 END IF;
2294
2295 RAISE pref_changed_before_ret;
2296 END IF;
2297 END IF;
2298
2299 EXCEPTION
2300
2301 -- Commented code for bug 8932359
2302 /*WHEN set_to_view_only_d
2303 THEN
2304 IF g_debug THEN
2305 hr_utility.TRACE ('ABS:EXCEPTION IN DELETE_ABSENCES - set_to_view_only_d');
2306 END IF;
2307
2308 g_error_message := NULL;
2309 g_error_message := 'HXC_ABS_VIEW_ONLY';
2310
2311 RAISE;*/
2312
2313 WHEN pref_changed_before_ret
2314 THEN
2315 IF g_debug THEN
2316 hr_utility.TRACE ('ABS:EXCEPTION IN DELETE_ABSENCES - pref_changed_before_ret');
2317 END IF;
2318
2319 g_error_message := NULL;
2320 g_error_message := 'HXC_ABS_NO_EDIT_PREP';
2321
2322 RAISE;
2323
2324
2325 END delete_absences;
2326
2327
2328
2329
2330 -- Create Transaction detail records for successfully retrieved absences
2331 PROCEDURE create_transactions (
2332 p_tbb_id IN hxc_abs_retrieval_pkg.NUMTAB,
2333 p_tbb_ovn IN hxc_abs_retrieval_pkg.NUMTAB,
2334 p_status IN VARCHAR2 DEFAULT NULL,
2335 p_description IN VARCHAR2 DEFAULT NULL
2336 )
2337 IS
2338 PRAGMA AUTONOMOUS_TRANSACTION;
2339
2340 BEGIN
2341
2342 IF g_transaction_id IS NULL THEN
2343 insert_audit_header (p_status,
2344 p_description,
2345 g_transaction_id);
2346 END IF;
2347
2348 insert_audit_details (p_tbb_id,
2349 p_tbb_ovn,
2350 p_status,
2351 p_description,
2352 g_transaction_id
2353 );
2354
2355 COMMIT;
2356
2357 END create_transactions;
2358
2359
2360 PROCEDURE insert_audit_header (
2361 p_status IN VARCHAR2,
2362 p_description IN VARCHAR2,
2363 p_transaction_id OUT NOCOPY hxc_transactions.transaction_id%TYPE
2364 )
2365 IS
2366 CURSOR c_transaction_sequence
2367 IS
2368 SELECT hxc_transactions_s.NEXTVAL
2369 FROM DUAL;
2370
2371 BEGIN
2372 OPEN c_transaction_sequence;
2373
2374 FETCH c_transaction_sequence
2375 INTO g_transaction_id;
2376
2377 CLOSE c_transaction_sequence;
2378
2379 INSERT INTO hxc_transactions
2380 (transaction_id,
2381 transaction_date,
2382 TYPE,
2383 transaction_process_id,
2384 created_by,
2385 creation_date,
2386 last_updated_by,
2387 last_update_date,
2388 last_update_login,
2389 status,
2390 exception_description,
2391 data_set_id
2392 )
2393 VALUES (g_transaction_id,
2394 SYSDATE,
2395 'RETRIEVAL',
2396 g_retrieval_process_id,
2397 NULL,
2398 SYSDATE,
2399 NULL,
2400 SYSDATE,
2401 NULL,
2402 p_status,
2403 p_description,
2404 NULL
2405 );
2406
2407 p_transaction_id := g_transaction_id;
2408
2409 END insert_audit_header;
2410
2411
2412
2413 PROCEDURE insert_audit_details (
2414 p_tbb_id IN hxc_abs_retrieval_pkg.NUMTAB,
2415 p_tbb_ovn IN hxc_abs_retrieval_pkg.NUMTAB,
2416 p_status IN VARCHAR2 DEFAULT NULL,
2417 p_description IN VARCHAR2 DEFAULT NULL,
2418 p_transaction_id IN hxc_transactions.transaction_id%TYPE
2419 )
2420 IS
2421 CURSOR c_transaction_detail_sequence
2422 IS
2423 SELECT hxc_transaction_details_s.NEXTVAL
2424 FROM DUAL;
2425
2426 l_transaction_detail_id hxc_transaction_details.transaction_detail_id%TYPE;
2427 BEGIN
2428 -- insert into hxc_transaction_details
2429 FOR l_tx_index IN p_tbb_id.FIRST .. p_tbb_id.LAST
2430 LOOP
2431 OPEN c_transaction_detail_sequence;
2432
2433 FETCH c_transaction_detail_sequence
2434 INTO l_transaction_detail_id;
2435
2436 CLOSE c_transaction_detail_sequence;
2437
2438
2439 INSERT INTO hxc_transaction_details
2440 (transaction_detail_id,
2441 time_building_block_id,
2442 transaction_id,
2443 created_by,
2444 creation_date,
2445 last_updated_by,
2446 last_update_date,
2447 last_update_login,
2448 time_building_block_ovn,
2449 status,
2450 exception_description,
2451 data_set_id
2452 )
2453 VALUES (l_transaction_detail_id,
2454 p_tbb_id (l_tx_index),
2455 g_transaction_id,
2456 NULL,
2457 SYSDATE,
2458 NULL,
2459 SYSDATE,
2460 NULL,
2461 p_tbb_ovn (l_tx_index),
2462 p_status,
2463 p_description,
2464 NULL
2465 );
2466 END LOOP;
2467 END insert_audit_details;
2468
2469
2470
2471 PROCEDURE update_cost_center (
2472 p_absence_attendance_id NUMBER,
2473 p_cost_allocation_keyflex_id NUMBER
2474 )
2475 AS
2476 CURSOR get_element_entry_info
2477 IS
2478 SELECT peef.element_entry_id, peef.effective_start_date,
2479 peef.effective_end_date,
2480 peef.object_version_number,
2481 pelf.costable_type
2482 FROM pay_element_entries_f peef, pay_element_links_f pelf
2483 WHERE peef.creator_id = p_absence_attendance_id
2484 AND peef.element_link_id = pelf.element_link_id
2485 AND peef.object_version_number =
2486 (SELECT /*+NO_UNNEST*/
2487 MAX (object_version_number)
2488 FROM pay_element_entries_f
2489 WHERE creator_id = p_absence_attendance_id);
2490
2491 l_element_entry_id NUMBER;
2492 l_effective_start_date DATE;
2493 l_effective_end_date DATE;
2494 l_ee_ovn NUMBER;
2495 l_costable_type VARCHAR2 (30);
2496 l_update_warning BOOLEAN;
2497
2498 l_proc VARCHAR2(100);
2499
2500 BEGIN
2501
2502 g_debug := hr_utility.debug_enabled;
2503
2504 IF g_debug THEN
2505 l_proc := g_package||'update_cost_center';
2506 hr_utility.set_location('ABS:Processing '||l_proc, 500);
2507 END IF;
2508
2509 IF g_debug THEN
2510 hr_utility.TRACE
2511 ( 'ABS:Entered update_cost_center for absence_attendance_id = ' || p_absence_attendance_id );
2512
2513 END IF;
2514
2515 OPEN get_element_entry_info;
2516
2517 FETCH get_element_entry_info
2518 INTO l_element_entry_id,
2519 l_effective_start_date,
2520 l_effective_end_date,
2521 l_ee_ovn,
2522 l_costable_type;
2523
2524 CLOSE get_element_entry_info;
2525
2526 IF g_debug THEN
2527 hr_utility.TRACE ('ABS:l_element_entry_id = ' || l_element_entry_id);
2528 hr_utility.TRACE ('ABS:l_costable_type = ' || l_costable_type);
2529 END IF;
2530
2531 IF l_costable_type = 'C'
2532 THEN
2533 IF g_debug THEN
2534 hr_utility.TRACE ( 'ABS:p_cost_allocation_keyflex_id = ' || p_cost_allocation_keyflex_id );
2535 END IF;
2536
2537 -- update element entries with the cost allocation keyflex id
2538 py_element_entry_api.update_element_entry
2539 (p_validate => FALSE,
2540 p_datetrack_update_mode => hr_api.g_correction,
2541 p_effective_date => TRUNC(l_effective_start_date),
2542 p_business_group_id => g_business_group_id,
2543 p_element_entry_id => l_element_entry_id,
2544 p_object_version_number => l_ee_ovn,
2545 p_cost_allocation_keyflex_id => p_cost_allocation_keyflex_id,
2546 p_effective_start_date => l_effective_start_date,
2547 p_effective_end_date => l_effective_end_date,
2548 p_update_warning => l_update_warning
2549 );
2550 END IF;
2551
2552 IF g_debug THEN
2553 hr_utility.set_location('ABS:Processing '||l_proc, 520);
2554 END IF;
2555
2556
2557 END update_cost_center;
2558
2559
2560
2561 PROCEDURE populate_cost_keyflex (
2562 p_cost_attributes IN OUT NOCOPY hxc_abs_retrieval_pkg.t_cost_attributes
2563 )
2564 IS
2565 sql_stmt VARCHAR2 (300);
2566 l_proc VARCHAR2(100);
2567 l_cost_allocation_structure VARCHAR2(150);
2568 BEGIN
2569
2570 g_debug := hr_utility.debug_enabled;
2571
2572 IF g_debug THEN
2573 l_proc := g_package||'populate_cost_keyflex';
2574 hr_utility.set_location('ABS:Processing '||l_proc, 600);
2575 END IF;
2576
2577 l_cost_allocation_structure := get_cost_alloc_struct(g_business_group_id);
2578
2579 IF g_debug THEN
2580 hr_utility.set_location('ABS:Processing '||l_proc, 610);
2581 END IF;
2582
2583 -- Modified for Bug 14143774
2584 -- Derive the cost_allocation_keyflex_id
2585 FOR att_ix IN p_cost_attributes.FIRST .. p_cost_attributes.LAST
2586 LOOP
2587
2588
2589 p_cost_attributes (att_ix).cost_allocation_keyflex_id :=
2590 hr_entry.maintain_cost_keyflex
2591 (p_cost_keyflex_structure => l_cost_allocation_structure,
2592 p_cost_allocation_keyflex_id => -1,
2593 p_concatenated_segments => NULL,
2594 p_summary_flag => 'N',
2595 p_start_date_active => NULL,
2596 p_end_date_active => NULL,
2597 p_segment1 => p_cost_attributes
2598 (att_ix).attribute1,
2599 p_segment2 => p_cost_attributes
2600 (att_ix).attribute2,
2601 p_segment3 => p_cost_attributes
2602 (att_ix).attribute3,
2603 p_segment4 => p_cost_attributes
2604 (att_ix).attribute4,
2605 p_segment5 => p_cost_attributes
2606 (att_ix).attribute5,
2607 p_segment6 => p_cost_attributes
2608 (att_ix).attribute6,
2609 p_segment7 => p_cost_attributes
2610 (att_ix).attribute7,
2611 p_segment8 => p_cost_attributes
2612 (att_ix).attribute8,
2613 p_segment9 => p_cost_attributes
2614 (att_ix).attribute9,
2615 p_segment10 => p_cost_attributes
2616 (att_ix).attribute10,
2617 p_segment11 => p_cost_attributes
2618 (att_ix).attribute11,
2619 p_segment12 => p_cost_attributes
2620 (att_ix).attribute12,
2621 p_segment13 => p_cost_attributes
2622 (att_ix).attribute13,
2623 p_segment14 => p_cost_attributes
2624 (att_ix).attribute14,
2625 p_segment15 => p_cost_attributes
2626 (att_ix).attribute15,
2627 p_segment16 => p_cost_attributes
2628 (att_ix).attribute16,
2629 p_segment17 => p_cost_attributes
2630 (att_ix).attribute17,
2631 p_segment18 => p_cost_attributes
2632 (att_ix).attribute18,
2633 p_segment19 => p_cost_attributes
2634 (att_ix).attribute19,
2635 p_segment20 => p_cost_attributes
2636 (att_ix).attribute20,
2637 p_segment21 => p_cost_attributes
2638 (att_ix).attribute21,
2639 p_segment22 => p_cost_attributes
2640 (att_ix).attribute22,
2641 p_segment23 => p_cost_attributes
2642 (att_ix).attribute23,
2643 p_segment24 => p_cost_attributes
2644 (att_ix).attribute24,
2645 p_segment25 => p_cost_attributes
2646 (att_ix).attribute25,
2647 p_segment26 => p_cost_attributes
2648 (att_ix).attribute26,
2649 p_segment27 => p_cost_attributes
2650 (att_ix).attribute27,
2651 p_segment28 => p_cost_attributes
2652 (att_ix).attribute28,
2653 p_segment29 => p_cost_attributes
2654 (att_ix).attribute29,
2655 p_segment30 => p_cost_attributes
2656 (att_ix).attribute30
2657 );
2658 END LOOP;
2659
2660 IF g_debug THEN
2661 hr_utility.set_location('ABS:Leaving '||l_proc, 620);
2662 END IF;
2663
2664 EXCEPTION
2665 WHEN OTHERS THEN
2666
2667 hr_utility.trace('ABS:In populate_cost_keyflex exception');
2668 hr_utility.trace('ABS:In populate_cost_keyflex dbms_utility.format_call_stack ::'||dbms_utility.format_call_stack);
2669 hr_utility.trace('ABS:In populate_cost_keyflex dbms_utility.format_error_backtrace ::'||dbms_utility.format_error_backtrace);
2670
2671 RAISE;
2672
2673 END populate_cost_keyflex;
2674
2675
2676 FUNCTION is_view_only
2677 (p_absence_attendance_type_id NUMBER)
2678 RETURN BOOLEAN
2679 IS
2680
2681 l_edit_flag VARCHAR2(1);
2682
2683 BEGIN
2684
2685 IF g_debug THEN
2686 hr_utility.trace('ABS:Inside function IS_VIEW_ONLY');
2687 END IF;
2688
2689 SELECT edit_flag INTO l_edit_flag
2690 FROM hxc_absence_type_elements
2691 WHERE absence_attendance_type_id = p_absence_attendance_type_id;
2692
2693 IF g_debug THEN
2694 hr_utility.trace('ABS:l_edit_flag = '||l_edit_flag);
2695 END IF;
2696
2697 IF (l_edit_flag = 'Y') THEN
2698 IF g_debug THEN
2699 hr_utility.trace('ABS:View and Edit');
2700 END IF;
2701
2702 RETURN FALSE;
2703 ELSE
2704 IF g_debug THEN
2705 hr_utility.trace('ABS:View Only');
2706 END IF;
2707
2708 RETURN TRUE;
2709 END IF;
2710
2711
2712 END is_view_only;
2713
2714
2715 PROCEDURE addTkError( p_token VARCHAR2 )
2716 IS
2717
2718 l_index BINARY_INTEGER;
2719 l_msg_name VARCHAR2(50);
2720
2721 BEGIN
2722
2723 l_msg_name := 'HXC_ABS_RET_FAILED';
2724
2725 if g_tk_ret_messages.COUNT = 0 then
2726 l_index := 0;
2727 else
2728 l_index := g_tk_ret_messages.LAST;
2729 end if;
2730
2731 g_tk_ret_messages(l_index + 1).message_name := l_msg_name ;
2732 g_tk_ret_messages(l_index + 1).employee_name := p_token;
2733
2734 END;
2735
2736 FUNCTION get_cost_alloc_struct ( p_business_group_id IN NUMBER)
2737 RETURN VARCHAR2
2738 IS
2739
2740 l_cost_alloc_struct VARCHAR2(150);
2741
2742 BEGIN
2743 IF g_cost_struct.EXISTS(p_business_group_id)
2744 THEN
2745 RETURN g_cost_struct(p_business_group_id).cost_allocation_structure;
2746 ELSE
2747 SELECT cost_allocation_structure
2748 INTO l_cost_alloc_struct
2749 FROM per_business_groups
2750 WHERE business_group_id = p_business_group_id;
2751
2752 g_cost_struct(p_business_group_id).business_group_id := p_business_group_id;
2753 g_cost_struct(p_business_group_id).cost_allocation_structure := l_cost_alloc_struct;
2754
2755 RETURN g_cost_struct(p_business_group_id).cost_allocation_structure;
2756 END IF;
2757 END get_cost_alloc_struct;
2758
2759
2760 FUNCTION get_retrieval_rule ( p_retrieval_rule_grp_id IN NUMBER)
2761 RETURN VARCHAR2
2762 IS
2763
2764 l_ret_status VARCHAR2(20);
2765
2766 BEGIN
2767 IF g_ret_rules.EXISTS(p_retrieval_rule_grp_id)
2768 THEN
2769 RETURN g_ret_rules(p_retrieval_rule_grp_id).status;
2770 ELSE
2771 SELECT rrc.status
2772 INTO l_ret_status
2773 FROM hxc_retrieval_rule_comps rrc,
2774 hxc_retrieval_rules rr,
2775 hxc_retrieval_rule_grp_comps_v rrgc,
2776 hxc_retrieval_rule_groups_v rrg
2777 WHERE rrg.retrieval_rule_group_id = p_retrieval_rule_grp_id
2778 AND rrgc.retrieval_rule_group_id = rrg.retrieval_rule_group_id
2779 AND rrgc.retrieval_process_id = g_retrieval_process_id
2780 AND rr.retrieval_rule_id = rrgc.retrieval_rule_id
2781 AND rrc.retrieval_rule_id = rr.retrieval_rule_id;
2782
2783 g_ret_rules(p_retrieval_rule_grp_id).retrieval_rule_group_id := p_retrieval_rule_grp_id;
2784 g_ret_rules(p_retrieval_rule_grp_id).status := l_ret_status;
2785
2786 RETURN g_ret_rules(p_retrieval_rule_grp_id).status;
2787 END IF;
2788
2789 EXCEPTION
2790 WHEN NO_DATA_FOUND THEN
2791 RETURN 'NODATA';
2792
2793
2794 END get_retrieval_rule;
2795
2796
2797
2798 END hxc_abs_retrieval_pkg;
2799