1 Package Body hxc_generic_retrieval_utils as
2 /* $Header: hxcretutl.pkb 120.16.12020000.2 2013/02/11 11:21:29 jnerella ship $ */
3
4 -- global package data type and variables
5
6 g_debug boolean := hr_utility.debug_enabled;
7
8 l_message_table HXC_MESSAGE_TABLE_TYPE;
9 l_rowid ROWID;
10 l_boolean boolean;
11
12 -- public function
13 -- time_bld_blk_changed
14 --
15 -- description
16 -- This function returns TRUE if the latest version of the
17 -- time building block specified by its ID has a greater
18 -- Object Version Number in the time store than that specified
19 -- in the call to the function
20 --
21 -- parameters
22 -- p_bb_id - time building block id
23 -- p_bb_ovn - time building block object version number
24
25 FUNCTION time_bld_blk_changed ( p_bb_id NUMBER
26 , p_bb_ovn NUMBER )RETURN BOOLEAN IS
27
28 CURSOR csr_get_bb_ovn IS
29 SELECT MAX(tbb.object_version_number)
30 FROM hxc_time_building_blocks tbb
31 WHERE tbb.time_building_block_id = p_bb_id;
32
33 l_ovn hxc_time_building_blocks.object_version_number%TYPE;
34
35 BEGIN
36
37 OPEN csr_get_bb_ovn;
38 FETCH csr_get_bb_ovn INTO l_ovn;
39 CLOSE csr_get_bb_ovn;
40
41 IF ( l_ovn > p_bb_ovn )
42 THEN
43 RETURN TRUE;
44 ELSE
45 RETURN FALSE;
46 END IF;
47
48 END time_bld_blk_changed;
49
50 -- Bug 6121705
51 -- Private function chk_need_adj
52 -- Checks to see if the detail record referred to had a history of transfer previously
53 -- thru a different process ( OTM or BEE). If it is transferred, need to capture those
54 -- ovns, so insert those into the temp table created for reversal batches.
55
56 -- Bug 8366309
57 -- Added new parameters p_bb_ovn and p_action.
58
59 FUNCTION chk_need_adj ( p_tc_id NUMBER,
60 p_tc_ovn NUMBER,
61 p_resource_id NUMBER,
62 p_date_earned DATE,
63 p_bb_id NUMBER,
64 p_bb_ovn NUMBER,
65 p_action VARCHAR2,
66 p_retr_process_id NUMBER )
67 RETURN BOOLEAN
68 IS
69
70 -- This cursor would pull out the last transferred ovn (if any) for the given
71 -- detail block, where transaction process is either transfer_to_bee or transfer
72 -- to_otm.
73 CURSOR cur_chk_retr_history (p_curr_bb_id NUMBER ) IS
74 SELECT htd.time_building_block_id,
75 htd.time_building_block_ovn,
76 ht.transaction_process_id
77 FROM hxc_transaction_details htd,
78 hxc_transactions ht,
79 hxc_retrieval_processes hrp
80 WHERE htd.time_building_block_id = p_curr_bb_id
81 AND htd.transaction_id = ht.transaction_id
82 AND ht.type = 'RETRIEVAL'
83 AND htd.status = 'SUCCESS'
84 AND ht.transaction_process_id IN (-1,hrp.retrieval_process_id)
85 AND hrp.name = 'BEE Retrieval Process'
86 ORDER BY 2 DESC ;
87
88 l_tbb_id NUMBER;
89 l_tbb_ovn NUMBER;
90 l_rt_id NUMBER;
91 l_return BOOLEAN := FALSE;
92 l_batch_source VARCHAR2(15);
93
94 BEGIN
95
96 OPEN cur_chk_retr_history ( p_bb_id);
97
98 -- Fetch the first value, which is the last transferred ovn.
99 -- We dont need anything else, just close of the cursor.
100
101 FETCH cur_chk_retr_history
102 INTO l_tbb_id,
103 l_tbb_ovn,
104 l_rt_id ;
105
106 CLOSE cur_chk_retr_history;
107
108 -- If the last transferred ovn is for the same process id, we are safe, do nothing.
109
110 -- Bug 8366309
111 -- If p_action = 'Y', then it is a DELETE action, DELETED flag = 'Y'
112 -- We need to reverse this item only if the DELETED item was not
113 -- retrieved earlier. If the Deleted item was retrieved earlier
114 -- the item is already adjusted; why bother now ??
115 -- So check if the OVN we are having is higher than the last
116 -- one retrieved. If yes, this is a DELETE and the DELETE was
117 -- retrieved earlier, so dont go in at all.
118 -- If p_action = N , then this is a new entry or an update. We need to
119 -- reverse out what went in. Go in.
120
121 IF ( l_rt_id <> p_retr_process_id)
122 AND( ( p_action = 'N' )
123 OR (p_action = 'Y' AND p_bb_ovn > l_tbb_ovn)
124 )
125 THEN
126 -- If it was different, find out what source it was. Write down corresponding
127 -- values into the table.
128 IF(l_rt_id = -1)
129 THEN
130 l_batch_source := 'OTM';
131 ELSE
132 l_batch_source := 'Time Store';
133 END IF;
134 INSERT INTO hxc_bee_pref_adj_lines
135 ( timecard_id,
136 timecard_ovn,
137 resource_id,
138 detail_bb_id,
139 detail_bb_ovn,
140 date_earned,
141 batch_source
142 )
143 VALUES ( p_tc_id,
144 p_tc_ovn,
145 p_resource_id,
146 l_tbb_id,
147 l_tbb_ovn,
148 p_date_earned,
149 l_batch_source
150 );
151 -- And adjustment needs to be done, return true.
152 l_return := TRUE;
153 END IF;
154 -- If it came here, it means you dont have to adjust, so return the default value - FALSE.
155 RETURN l_return;
156 EXCEPTION
157 WHEN NO_DATA_FOUND THEN
158 CLOSE cur_chk_retr_history;
159 RETURN l_return;
160 END chk_need_adj;
161
162
163 -- Bug 8366309
164 -- Added the new function to chk if the deleted entries
165 -- have the required preference to be adjusted.
166 -- Returns TRUE or FALSE accordingly.
167
168 FUNCTION chk_otm_pref ( p_resource_id IN NUMBER,
169 p_date IN DATE,
170 p_process_id IN NUMBER)
171 RETURN BOOLEAN
172 IS
173
174 l_ind NUMBER;
175 l_flag VARCHAR2(2) := 'X';
176
177 BEGIN
178 -- This table would have been populated by parse_resources
179 -- If the entry doesnt exist, return FALSE. something wrong.
180 IF g_res_pref_list.exists(p_resource_id)
181 THEN
182 -- Start from this guy's first record in the rules list.
183 l_ind := g_res_pref_list(p_resource_id).otm_rules.FIRST;
184 LOOP
185 -- If the date we are looking for falls here
186 IF p_date BETWEEN TRUNC(g_res_pref_list(p_resource_id).otm_rules(l_ind).start_date)
187 AND TRUNC(g_res_pref_list(p_resource_id).otm_rules(l_ind).end_date)
188 THEN
189 -- Copy the pref flag, and come out of the loop.
190 l_flag := g_res_pref_list(p_resource_id).otm_rules(l_ind).flag;
191 EXIT;
192 ELSE
193 -- Look for the next record and exit when appropriate.
194 l_ind := g_res_pref_list(p_resource_id).otm_rules.NEXT(l_ind);
195 EXIT WHEN NOT g_res_pref_list(p_resource_id).otm_rules.EXISTS(l_ind);
196 END IF;
197 END LOOP;
198 -- This is the default value -- meaning we dint find a good record
199 -- Send FALSE anyway.
200 IF l_flag = 'X'
201 THEN
202 RETURN FALSE;
203 -- If rules evaluation is Y and process is Apply Scheduled Rules
204 ELSIF l_flag = 'Y' AND p_process_id = -1
205 THEN
206 RETURN TRUE;
207 -- If rules evaluation is N and process is not Apply Scheduled Rules
208 ELSIF l_flag = 'N' AND p_process_id <> -1
209 THEN
210 RETURN TRUE;
211 -- None of the above, so send FALSE anyway.
212 ELSE
213 RETURN FALSE;
214 END IF;
215
216 ELSE
217 -- No pref, send FALSE
218 RETURN FALSE;
219 END IF;
220
221 END chk_otm_pref;
222
223
224
225 --
226
227
228 PROCEDURE populate_rtr_outcomes (
229 p_resource_id NUMBER
230 , p_ret_ranges IN t_ret_ranges
231 , p_ret_rules_tab IN OUT NOCOPY t_ret_rule
232 , p_ret_rules_start PLS_INTEGER
233 , p_rtr_outcomes_tab IN OUT NOCOPY t_rtr_outcome
234 ) IS
235
236 l_proc varchar2(72);
237
238 l_start_time hxc_time_building_blocks.start_time%TYPE;
239 l_stop_time hxc_time_building_blocks.stop_time%TYPE;
240
241 l_period_tab hxc_app_period_summary_api.valid_period_tab;
242 l_ind PLS_INTEGER;
243 l_cnt PLS_INTEGER;
244 l_iter PLS_INTEGER;
245
246 l_outcome_index PLS_INTEGER := 1;
247
248 l_overall_outcome_exists varchar2(1) ;
249
250 BEGIN
251 g_debug := hr_utility.debug_enabled;
252
253 IF ( g_debug ) THEN
254 l_proc := g_package||'populate_rtr_outcomes';
255 hr_utility.set_location('Processing '||l_proc, 10);
256 END IF;
257
258 l_cnt := p_ret_rules_start;
259
260 WHILE ( l_cnt IS NOT NULL )
261 LOOP
262 l_overall_outcome_exists := 'N';
263
264 IF ( g_debug ) THEN
265 hr_utility.set_location('Processing '||l_proc, 20);
266 END IF;
267
268 l_iter := p_ret_ranges.first;
269
270 WHILE l_iter is not null
271 LOOP
272
273 if p_ret_ranges(l_iter).rtr_grp_id = p_ret_rules_tab(l_cnt).rtr_grp_id then
274
275 -- convert to character to pass to the dyn SQL
276
277 IF ( g_debug ) THEN
278 hr_utility.trace('Params for Get Valid Periods are ...');
279 hr_utility.trace('Start Time is '||to_char(p_ret_ranges(l_iter).start_date,'dd-mon-yy'));
280 hr_utility.trace('Stop Time is '||to_char(p_ret_ranges(l_iter).stop_date,'dd-mon-yy'));
281 hr_utility.trace('App Status is '||p_ret_rules_tab(l_cnt).status);
282 hr_utility.trace('Time Recip is '||p_ret_rules_tab(l_cnt).time_recipient_id);
283 END IF;
284
285
286 -- get valid periods
287
288 hxc_app_period_summary_api.get_valid_periods (
289 P_RESOURCE_ID => p_resource_id
290 ,P_TIME_RECIPIENT_ID => p_ret_rules_tab(l_cnt).time_recipient_id
291 ,P_START_DATE => TRUNC(p_ret_ranges(l_iter).start_date)
292 ,P_STOP_DATE => TRUNC(p_ret_ranges(l_iter).stop_date)
293 ,P_VALID_STATUS => p_ret_rules_tab(l_cnt).status
294 ,P_VALID_PERIODS => l_period_tab );
295
296 l_outcome_index := NVL(p_rtr_outcomes_tab.LAST,0) + 1;
297
298 -- set outcomes exists flag in rtr_rules table so later on we know
299 -- to interogate the outcome table
300
301 IF ( l_period_tab.COUNT <> 0 )
302 THEN
303
304 IF ( l_overall_outcome_exists = 'N' )
305 THEN
306 IF ( g_debug ) THEN
307 hr_utility.set_location('Processing '||l_proc, 30);
308 END IF;
309
310 p_ret_rules_tab(l_cnt).outcome_exists := 'Y';
311 p_ret_rules_tab(l_cnt).outcome_start := l_outcome_index;
312
313 l_overall_outcome_exists := 'Y';
314
315 END IF;
316
317 l_ind := l_period_tab.FIRST;
318
319 WHILE l_ind IS NOT NULL
320 LOOP
321
322 IF ( g_debug ) THEN
323 hr_utility.set_location('Processing '||l_proc, 50);
324 END IF;
325
326 p_rtr_outcomes_tab(l_outcome_index).rtr_grp_id := p_ret_rules_tab(l_cnt).rtr_grp_id;
327 p_rtr_outcomes_tab(l_outcome_index).time_recipient_id := p_ret_rules_tab(l_cnt).time_recipient_id;
328 p_rtr_outcomes_tab(l_outcome_index).start_time := l_period_tab(l_ind).start_time;
329 p_rtr_outcomes_tab(l_outcome_index).stop_time := l_period_tab(l_ind).stop_time;
330
331 l_outcome_index := l_outcome_index + 1;
332
333 l_ind := l_period_tab.NEXT(l_ind);
334
335 END LOOP;
336
337 IF ( g_debug ) THEN
338 hr_utility.set_location('Processing '||l_proc, 60);
339 END IF;
340
341 l_period_tab.DELETE;
342
343 END IF; -- l_period_tab.COUNT <> 0
344
345 End if; --if p_ret_ranges(iter).rtr_grp_id = p_ret_rules_tab(l_cnt).rtr_grp_id
346
347 l_iter := p_ret_ranges.next(l_iter);
348
349
350 END LOOP;
351
352 if (l_overall_outcome_exists = 'Y') then
353 p_ret_rules_tab(l_cnt).outcome_stop := l_outcome_index - 1;
354 else
355 p_ret_rules_tab(l_cnt).outcome_exists := 'N';
356 end if;
357
358 l_cnt := p_ret_rules_tab.NEXT(l_cnt);
359
360 END LOOP;
361
362 IF ( g_debug ) THEN
363 hr_utility.set_location('Processing '||l_proc, 70);
364 END IF;
365
366 END populate_rtr_outcomes;
367
368 PROCEDURE parse_resources (
369 p_process_id NUMBER
370 , p_ret_tr_id NUMBER
371 , p_prefs IN OUT NOCOPY t_pref
372 , p_ret_rules IN OUT NOCOPY t_ret_rule
373 , p_rtr_outcomes IN OUT NOCOPY t_rtr_outcome
374 , p_errors IN OUT NOCOPY t_errors ) IS
375
376
377
378 TYPE r_resource_rtr is RECORD (
379 dummy VARCHAR2(1)
380 );
381
382 TYPE t_resource_rtr IS TABLE OF r_resource_rtr INDEX BY BINARY_INTEGER;
383
384 l_resource_rtr t_resource_rtr;
385
386 l_resource_rrg_id_tab t_resource_rtr;
387
388 l_ret_ranges_tmp_tab t_resource_rtr;
389
390
391 l_tmp_otm_tab hxc_preference_evaluation.t_pref_table;
392 l_tmp_rtr_tab hxc_preference_evaluation.t_pref_table;
393
394
395 l_ret_ranges t_ret_ranges;
396 l_ret_ranges1 t_ret_ranges;
397
398 l_tmp_otm_iter PLS_INTEGER;
399 l_tmp_rtr_iter PLS_INTEGER;
400
401
402 l_iter PLS_INTEGER;
403 l_ret_range_iter PLS_INTEGER;
404
405 l_proc varchar2(72);
406
407 l_dummy NUMBER(1);
408
409 l_rr_index PLS_INTEGER := 1;
410 l_index PLS_INTEGER;
411 l_resource_index PLS_INTEGER;
412
413
414
415 l_time_recipient_id hxc_time_recipients.time_recipient_id%TYPE;
416 l_status VARCHAR2(40);
417 l_emp per_people_f.last_name%TYPE;
418 l_rtr_grp_id NUMBER(15);
419 l_app_set_id NUMBER(15);
420
421 l_otm_explosion VARCHAR2(1);
422
423 l_rtr_exists t_rtr_exists;
424
425
426 l_bee_ret_id hxc_retrieval_processes.retrieval_process_id%TYPE;
427
428
429 l_process_id hxc_retrieval_processes.retrieval_process_id%TYPE;
430 l_rtr_process_id hxc_retrieval_processes.retrieval_process_id%TYPE;
431
432
433 l_resource_start_time DATE;
434 l_resource_stop_time DATE;
435 l_pref_date_not_ok BOOLEAN := FALSE;
436 l_pref_date DATE;
437
438 l_set_rtr BOOLEAN;
439
440 -- GPM v115.40
441
442 CURSOR csr_get_retrieval_rules ( p_rtr_grp_id NUMBER, p_process_id NUMBER ) IS
443 SELECT
444 rrc.time_recipient_id
445 , rrc.status
446 FROM
447 hxc_retrieval_rule_comps rrc
448 , hxc_retrieval_rules rr
449 , hxc_retrieval_rule_grp_comps_v rrgc
450 , hxc_retrieval_rule_groups_v rrg
451 WHERE
452 rrg.retrieval_rule_group_id = p_rtr_grp_id
453 AND
454 rrgc.retrieval_rule_group_id = rrg.retrieval_rule_group_id AND
455 rrgc.retrieval_process_id = p_process_id
456 AND
457 rr.retrieval_rule_id = rrgc.retrieval_rule_id
458 AND
459 rrc.retrieval_rule_id = rr.retrieval_rule_id;
460
461 CURSOR csr_get_emp ( p_resource_id NUMBER ) IS
462 SELECT last_name
463 FROM per_people_f
464 WHERE person_id = p_resource_id;
465
466 CURSOR csr_get_app_sets ( p_app_set_id NUMBER ) IS
467 SELECT 1
468 FROM hxc_application_set_comps_v apsc
469 , hxc_application_sets_v aps
470 WHERE aps.application_set_id = p_app_set_id
471 AND
472 apsc.application_set_id = aps.application_set_id AND
473 apsc.time_recipient_id = p_ret_tr_id;
474
475 CURSOR csr_get_bee_ret_id IS
476 SELECT ret.retrieval_process_id
477 FROM hxc_retrieval_processes ret
478 WHERE ret.name = 'BEE Retrieval Process';
479
480
481 BEGIN -- parse_resources
482
483 g_debug := hr_utility.debug_enabled;
484
485 IF ( g_debug ) THEN
486 l_proc := g_package||'parse_resources';
487 hr_utility.set_location('Processing '||l_proc, 10);
488
489 hr_utility.trace('');
490 hr_utility.trace('************** Params are: *****************');
491 hr_utility.trace('is p_process_id '||to_char(p_process_id));
492 hr_utility.trace('is p_ret_tr_id '||to_char(p_ret_tr_id));
493 hr_utility.trace('');
494
495 END IF;
496
497 l_process_id := p_process_id;
498
499
500 -- Get the BEE retrieval process id
501
502 OPEN csr_get_bee_ret_id;
503 FETCH csr_get_bee_ret_id INTO l_bee_ret_id;
504
505 IF ( csr_get_bee_ret_id%NOTFOUND )
506 THEN
507
508 CLOSE csr_get_bee_ret_id;
509
510 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
511 fnd_message.set_token('PROCEDURE', l_proc);
512 fnd_message.set_token('STEP','no BEE Retrieval Process id');
513 fnd_message.raise_error;
514
515 END IF;
516
517 CLOSE csr_get_bee_ret_id;
518
519 -- GPM v115.40
520
521 -- loop through the resources table and get the preferences
522
523 l_resource_index := hxc_generic_retrieval_utils.g_resources.FIRST;
524
525 WHILE l_resource_index IS NOT NULL
526 LOOP
527
528 BEGIN
529
530 --
531 -- REMEMBER the index on g_resources is the resource id!!!
532 --
533
534 WHILE l_resource_index IS NOT NULL
535 LOOP
536 IF ( g_debug ) THEN
537 hr_utility.set_location('Processing '||l_proc, 15);
538 END IF;
539
540 --delete the ret ranges table. This is because we maintain ret ranges table for each resource
541 l_ret_ranges.delete;
542
543 -- need to hit the database to retrieve this person's prefs
544
545 l_resource_start_time := TRUNC(hxc_generic_retrieval_utils.g_resources(l_resource_index).start_time);
546
547 l_resource_stop_time := TRUNC(hxc_generic_retrieval_utils.g_resources(l_resource_index).stop_time);
548
549 -- GPM v115.27
550
551 IF ( g_debug ) THEN
552
553 hr_utility.trace('************* NEW RESOURCE ********************');
554 hr_utility.trace('resource id is '||to_char(l_resource_index));
555 hr_utility.trace('resource start date is '||to_char(l_resource_start_time, 'dd-mon-yyyy hh24:mi:ss'));
556 hr_utility.trace('resource stop date is '||to_char(l_resource_stop_time, 'dd-mon-yyyy hh24:mi:ss'));
557 hr_utility.trace('************* NEW RESOURCE ********************');
558
559 END IF;
560
561
562 --Changing the call to preference evaluation here. We are passing the entire range of time
563 --so that the pref table contains date tracked changes also. The following procedures
564 --would return tables sorted on start date.
565
566 BEGIN
567
568 hxc_preference_evaluation.resource_preferences(
569 p_resource_id => l_resource_index,
570 p_preference_code => 'TS_PER_RETRIEVAL_RULES',
571 p_start_evaluation_date =>l_resource_start_time,
572 p_end_evaluation_date =>l_resource_stop_time,
573 p_sorted_pref_table => l_tmp_rtr_tab,
574 p_no_prefs_outside_asg => TRUE );
575
576 IF l_process_id IN ( -1, l_bee_ret_id)
577 THEN
578 hxc_preference_evaluation.resource_preferences(
579 p_resource_id => l_resource_index,
580 p_preference_code => 'TC_W_RULES_EVALUATION',
581 p_start_evaluation_date => l_resource_start_time,
582 p_end_evaluation_date => l_resource_stop_time,
583 p_sorted_pref_table => l_tmp_otm_tab,
584 p_clear_cache => TRUE,
585 p_no_prefs_outside_asg => FALSE );
586 END IF;
587
588 --clear the cache, since we have obtained the values
589 hxc_preference_evaluation.clear_sort_pref_table_cache;
590
591 EXCEPTION
592 WHEN OTHERS THEN
593 IF ( g_debug ) THEN
594 hr_utility.trace('Error 1 : '||substr(sqlerrm,1,100));
595 END IF;
596 hr_utility.set_message(809, SUBSTR('HXC_NO_RESRC_DATES-'||to_char(l_resource_index),1,28));
597 p_errors(l_resource_index).exception_description := SUBSTR('HXC_NO_RESRC_DATES-'||to_char(l_resource_index),1,2000);
598 p_prefs(l_resource_index).prefs_ok := 'X';
599 END;
600
601 --Check for any errors - if not proceed
602
603 IF ( NOT p_errors.EXISTS(l_resource_index) )
604 THEN
605
606 -- Now we need to make sure that we have the preferences we are interested in
607
608 IF (l_process_id = -1 or l_process_id = l_bee_ret_id)
609 THEN
610 IF ( l_tmp_otm_tab.COUNT = 0 OR l_tmp_rtr_tab.COUNT = 0 )
611 THEN
612 IF ( g_debug ) THEN
613 hr_utility.trace('Error 2 : '||substr(sqlerrm,1,100));
614 END IF;
615 p_errors(l_resource_index).exception_description := 'HXC_NO_HIER_FOR_DATE';
616 p_prefs(l_resource_index).prefs_ok := 'X';
617 END IF;
618 ELSE
619 IF ( l_tmp_rtr_tab.COUNT = 0 )
620 THEN
621 IF ( g_debug ) THEN
622 hr_utility.trace('Error 3 : '||substr(sqlerrm,1,100));
623 END IF;
624 p_errors(l_resource_index).exception_description := 'HXC_NO_HIER_FOR_DATE';
625 p_prefs(l_resource_index).prefs_ok := 'X';
626 END IF;
627 END IF; -- (l_process_id = -1 or l_process_id = l_bee_ret_id)
628
629
630 --Check for any errors - if not proceed
631 IF ( NOT p_errors.EXISTS(l_resource_index) )
632 THEN
633
634 -- Bug 8366309
635 -- Copy down the preferences for this employee into
636 -- the global table of tables. Do this only for
637 -- BEE retrievals.
638 IF l_process_id IN (-1, l_bee_ret_id)
639 THEN
640 FOR l_filter_ind IN 1 .. l_tmp_otm_tab.LAST
641 LOOP
642 --Copy attribute1 as the flag.
643 g_res_pref_list(l_resource_index).otm_rules(l_filter_ind).flag := l_tmp_otm_tab(l_filter_ind).attribute1;
644 -- Copy the date ranges.
645 g_res_pref_list(l_resource_index).otm_rules(l_filter_ind).start_date
646 := l_tmp_otm_tab(l_filter_ind).start_date;
647 g_res_pref_list(l_resource_index).otm_rules(l_filter_ind).end_date
648 := l_tmp_otm_tab(l_filter_ind).end_date;
649
650 END LOOP;
651 END IF;
652
653
654 IF ( g_debug ) AND l_process_id IN (-1, l_bee_ret_id)
655 THEN
656
657 hr_utility.trace('OTM prefs tab is');
658 hr_utility.trace('Preference Code ATT1 ATT2 Start Date End Date');
659 hr_utility.trace('--------------------- ------ ----- -------------------- ---------------------');
660
661 FOR l_filter_ind IN 1 .. l_tmp_otm_tab.LAST
662 LOOP
663
664 hr_utility.trace(l_tmp_otm_tab(l_filter_ind).preference_code||' '||
665 l_tmp_otm_tab(l_filter_ind).attribute1||' '||
666 l_tmp_otm_tab(l_filter_ind).attribute2||' '||
667 to_char(l_tmp_otm_tab(l_filter_ind).start_date,'DD-MON-YYYY HH24:MI:SS')||' '||
668 to_char(l_tmp_otm_tab(l_filter_ind).end_date,'DD-MON-YYYY HH24:MI:SS'));
669
670 END LOOP;
671
672 hr_utility.trace('--------------------- ------ ----- -------------------- ---------------------');
673
674 hr_utility.trace('RTR prefs tab is');
675 hr_utility.trace('Preference Code ATT1 ATT2 Start Date End Date');
676 hr_utility.trace('--------------------- ------ ----- -------------------- ---------------------');
677
678 FOR l_filter_ind IN 1 .. l_tmp_rtr_tab.LAST
679 LOOP
680
681 hr_utility.trace(l_tmp_rtr_tab(l_filter_ind).preference_code||' '||
682 l_tmp_rtr_tab(l_filter_ind).attribute1||' '||
683 l_tmp_rtr_tab(l_filter_ind).attribute2||' '||
684 to_char(l_tmp_rtr_tab(l_filter_ind).start_date,'DD-MON-YYYY HH24:MI:SS')||' '||
685 to_char(l_tmp_rtr_tab(l_filter_ind).end_date,'DD-MON-YYYY HH24:MI:SS'));
686
687 END LOOP;
688
689 hr_utility.trace('--------------------- ------ ----- -------------------- ---------------------');
690
691 END IF; -- l debug
692
693 --We have filtered the main pref table and populated the temporary tables we created.
694 --we need to get a merged table based on Application process..
695 --The merge logic assumes that the temporary tables are sorted on start_date.
696 --Since the above call to pref evaluation returns rows in a sorted order we dont have any problem.
697
698 --we need to get a merged table based on Application process..
699 --however this will be done only if the process is Apply Schedule Rules or BEE process
700 --otherwise the l_tmp_rtr_tab table will be copied to ret_ranges table.
701 --Note : We dont need the merge logic in case the Application Process is not BEE or Apply Schedule Rules
702
703 IF ( g_debug ) THEN
704 hr_utility.trace('Merge logic starts');
705 END IF;
706
707 if (l_process_id = -1 or l_process_id = l_bee_ret_id) then
708 --merge logic
709 IF ( g_debug ) THEN
710 hr_utility.trace('merge logic');
711 END IF;
712
713 l_ret_range_iter :=1;
714 l_tmp_rtr_iter := l_tmp_rtr_tab.first;
715
716 WHILE l_tmp_rtr_iter is not null
717 LOOP
718 l_tmp_otm_iter := l_tmp_otm_tab.first;
719
720 WHILE l_tmp_otm_iter is not null
721 LOOP
722 --check for overlap
723
724 if (l_tmp_rtr_tab(l_tmp_rtr_iter).end_date >= l_tmp_otm_tab(l_tmp_otm_iter).start_date ) and
725 (l_tmp_rtr_tab(l_tmp_rtr_iter).start_date <= l_tmp_otm_tab(l_tmp_otm_iter).end_date ) then
726
727 --there is a overlap
728
729 IF ( (l_process_id = l_bee_ret_id AND l_tmp_otm_tab (l_tmp_otm_iter).attribute1 = 'N') OR
730 (l_process_id = -1 AND l_tmp_otm_tab (l_tmp_otm_iter).attribute1 = 'Y')
731 )
732 THEN
733 --we need this record
734 l_ret_ranges (l_ret_range_iter).rtr_grp_id := l_tmp_rtr_tab (l_tmp_rtr_iter).attribute1;
735 l_ret_ranges (l_ret_range_iter).start_date := greatest (l_tmp_rtr_tab (l_tmp_rtr_iter).start_date,l_tmp_otm_tab (l_tmp_otm_iter).start_date);
736 l_ret_ranges (l_ret_range_iter).stop_date := least (l_tmp_rtr_tab (l_tmp_rtr_iter).end_date,l_tmp_otm_tab (l_tmp_otm_iter).end_date);
737 l_ret_range_iter := l_ret_range_iter+1;
738
739 END IF;
740
741 end if;
742
743 l_tmp_otm_iter := l_tmp_otm_tab.next(l_tmp_otm_iter);
744 END LOOP;
745
746 l_tmp_rtr_iter := l_tmp_rtr_tab.next(l_tmp_rtr_iter);
747 END LOOP;
748
749 IF ( g_debug ) THEN
750 hr_utility.trace('merge logic over');
751 END IF;
752
753 --merge logic over
754 else
755
756 --process is neither Apply Schedule rules nor BEE. Hence we just copy
757 --l_tmp_rtr_tab to l_ret_ranges
758
759 IF ( g_debug ) THEN
760 hr_utility.trace('not BEE or Apply thus copy RTR');
761 END IF;
762
763 l_iter := 1;
764 l_tmp_rtr_iter := l_tmp_rtr_tab.first;
765
766 while l_tmp_rtr_iter is not null
767 loop
768 l_ret_ranges(l_iter).rtr_grp_id := l_tmp_rtr_tab(l_tmp_rtr_iter).attribute1;
769 l_ret_ranges(l_iter).start_date := l_tmp_rtr_tab(l_tmp_rtr_iter).start_date;
770 l_ret_ranges(l_iter).stop_date := l_tmp_rtr_tab(l_tmp_rtr_iter).end_date;
771
772 l_iter := l_iter + 1;
773 l_tmp_rtr_iter := l_tmp_rtr_tab.next(l_tmp_rtr_iter);
774 end loop;
775
776
777 end if;
778 IF ( g_debug ) THEN
779 hr_utility.trace('Merge logic ends');
780 END IF;
781
782 --we can delete the pref table and temporary tables to save space
783 l_tmp_otm_tab.DELETE;
784 l_tmp_rtr_tab.DELETE;
785
786
787 --the resulting l_ret_ranges table is not sorted on rtr_grp_id. This needs to be done because
788 --ret_rules needs to be in sorted order and so this table must be in a sorted manner.
789 --Please note that we are just sorting the table only on rtr_grp_id.The table is already sorted based
790 --on start date.
791
792 --the unsorted table is l_ret_ranges.
793 --we shall have a temporary table that we can use to store the distinct
794 --rtr grp ids. It will be indexed on rtr grp id.
795
796 IF ( g_debug ) THEN
797 hr_utility.trace('Sorting the unsorted ret ranges table');
798 END IF;
799 l_iter:= l_ret_ranges.first;
800
801 WHILE l_iter is not null
802 LOOP
803 l_tmp_rtr_iter := l_ret_ranges(l_iter).rtr_grp_id;
804 l_ret_ranges_tmp_tab(l_tmp_rtr_iter).dummy := 'Y';
805
806 l_iter:= l_ret_ranges.next(l_iter);
807 END LOOP;
808
809 --we have got the tmp table.
810 l_tmp_rtr_iter := l_ret_ranges_tmp_tab.first;
811 l_index := 1;
812
813 WHILE l_tmp_rtr_iter is not null
814 LOOP --through the tmp table
815
816 l_iter:= l_ret_ranges.first;
817
818 WHILE l_iter is not null
819 LOOP -- through the ret ranges and retrieve columns and put them into another temporary table.
820 -- we can use the l_ret_ranges1 here.That will contain the sorted columns.
821 if l_ret_ranges(l_iter).rtr_grp_id = l_tmp_rtr_iter then
822 l_ret_ranges1(l_index).rtr_grp_id := l_ret_ranges(l_iter).rtr_grp_id;
823 l_ret_ranges1(l_index).start_date := l_ret_ranges(l_iter).start_date;
824 l_ret_ranges1(l_index).stop_date := l_ret_ranges(l_iter).stop_date;
825 l_index := l_index +1;
826 end if;
827
828 l_iter:= l_ret_ranges.next(l_iter);
829 END LOOP;
830
831 l_tmp_rtr_iter:= l_ret_ranges_tmp_tab.next(l_tmp_rtr_iter);
832 END LOOP;
833 --
834
835 l_ret_ranges := l_ret_ranges1;
836 l_ret_ranges1.delete;
837 l_ret_ranges_tmp_tab.delete;
838
839 IF ( g_debug ) THEN
840 hr_utility.trace('Sorting the unsorted ret ranges table over');
841 END IF;
842
843
844 --Now we have the final ret ranges table in l_ret_ranges. We will use the ret ranges table to populate
845 --ret_rules and outcomes table.
846
847 --We need to ensure that the l_ret_ranges contains atleast 1 record for
848 --us to proceed.
849 if (l_ret_ranges.COUNT >0) then
850
851
852 IF ( g_debug ) THEN
853
854 hr_utility.trace('Ret Ranges Table');
855 hr_utility.trace('Index RTR GRP ID Start Date End Date');
856 hr_utility.trace('------ ----------- -------------------- ---------------------');
857
858 FOR l_filter_ind IN 1 .. l_ret_ranges.LAST
859 LOOP
860
861 hr_utility.trace(to_char(l_filter_ind)||' '||
862 to_char(l_ret_Ranges(l_filter_ind).rtr_grp_id)||' '||
863 to_char(l_ret_ranges(l_filter_ind).start_date,'DD-MON-YYYY HH24:MI:SS')||' '||
864 to_char(l_ret_ranges(l_filter_ind).stop_date,'DD-MON-YYYY HH24:MI:SS'));
865
866 END LOOP;
867
868 hr_utility.trace('----------- -------------------- ---------------------');
869
870 END IF;
871
872 l_resource_rtr.DELETE;
873 l_resource_rrg_id_tab.DELETE;
874 l_set_rtr := FALSE;
875
876 -- build a distinct table of resource RRGs
877 -- this is so we can detect whether or not to error if the
878 -- resource does not have a RRG for this process when there are more
879 -- than one RRG for the resource
880
881 FOR x IN l_ret_ranges.FIRST .. l_ret_ranges.LAST
882 LOOP
883
884 l_resource_rrg_id_tab(l_ret_ranges(x).rtr_grp_id).dummy := 'Y';
885
886 END LOOP;
887
888
889
890 FOR ret_range_iter IN l_ret_ranges.FIRST .. l_ret_ranges.LAST
891 LOOP
892
893 if not (l_resource_rtr.exists(l_ret_ranges(ret_range_iter).rtr_grp_id)) then
894
895 l_rtr_grp_id := l_ret_ranges(ret_range_iter).rtr_grp_id;
896
897 l_resource_rrg_id_tab.DELETE(l_rtr_grp_id);
898
899 l_resource_rtr(l_ret_ranges(ret_range_iter).rtr_grp_id).dummy := 'Y';
900
901 IF ( g_debug ) THEN
902 hr_utility.set_location('Processing '||l_proc, 55);
903 END IF;
904
905 -- check to see that we haven't got these rules before
906
907 IF ( l_rtr_exists.EXISTS(l_rtr_grp_id) )
908 THEN
909 IF ( g_debug ) THEN
910 hr_utility.trace('hitting PL/SQL table for rtr');
911 END IF;
912
913 l_rr_index := NVL(p_ret_rules.LAST, 0) + 1;
914
915 IF NOT l_set_rtr
916 THEN
917
918 p_prefs(l_resource_index).rtr_start := l_rr_index;
919 p_prefs(l_resource_index).prefs_ok := 'Y';
920
921 l_set_rtr := TRUE;
922
923 END IF;
924
925 FOR x IN l_rtr_exists(l_rtr_grp_id).rtr_start ..
926 l_rtr_exists(l_rtr_grp_id).rtr_stop
927 LOOP
928 p_ret_rules(l_rr_index) := p_ret_rules(x);
929
930 -- GPM v115.12 30-JUL-01
931
932 l_rr_index := l_rr_index + 1;
933
934 END LOOP;
935
936 p_prefs(l_resource_index).rtr_end := l_rr_index -1;
937
938 ELSE
939
940 -- get the retrieval rules for the given resource ids retrieval group
941
942 IF ( g_debug ) THEN
943 hr_utility.trace('hitting db for rtr');
944 END IF;
945
946 -- l_process_id is passed to the cursor csr_get_retrieval_rules. However
947 --for the 'Apply Schedule Rules' Process the process id is 1 and the
948 --cursor will not retrieve rows in case 1 is passed. Hence we need to
949 --pass the BEE Retrieval ID in that case.
950 if (l_process_id = -1) then
951 l_rtr_process_id := l_bee_ret_id;
952 else
953 l_rtr_process_id := l_process_id;
954 end if;
955
956 OPEN csr_get_retrieval_rules ( l_ret_ranges(ret_range_iter).rtr_grp_id, l_rtr_process_id );
957 FETCH csr_get_retrieval_rules INTO l_time_recipient_id, l_status;
958
959 -- if there are no retrieval rules for the person for this retrieval
960 -- then error.
961
962 IF csr_get_retrieval_rules%NOTFOUND
963 THEN
964
965 -- only error if this is the last RRG for this person
966
967 IF ( l_resource_rrg_id_tab.COUNT = 0 )
968 THEN
969
970 OPEN csr_get_emp ( l_resource_index);
971 FETCH csr_get_emp INTO l_emp;
972 CLOSE csr_get_emp;
973
974 hr_utility.set_message(809, 'HXC_NO_RET_RULE_FOR_RET');
975 hr_utility.set_message_token('EMP', l_emp);
976
977 p_errors(l_resource_index).exception_description := SUBSTR('HXC_NO_RET_RULE_FOR_RET: '||l_emp,1,2000);
978 p_prefs(l_resource_index).prefs_ok := 'X';
979
980 END IF;
981
982 CLOSE csr_get_retrieval_rules;
983
984 ELSE
985 -- get next index value for the retrieval rules
986
987 l_rr_index := NVL(p_ret_rules.LAST,0) + 1;
988
989 IF NOT l_set_rtr
990 THEN
991
992 -- set this value in the prefs table for use later
993
994 p_prefs(l_resource_index).rtr_start := l_rr_index;
995 p_prefs(l_resource_index).prefs_ok := 'Y';
996
997 l_set_rtr := TRUE;
998
999 END IF;
1000
1001 IF ( g_debug ) THEN
1002 hr_utility.set_location('Processing '||l_proc, 60);
1003 END IF;
1004
1005 WHILE csr_get_retrieval_rules%FOUND
1006 LOOP
1007 IF ( g_debug ) THEN
1008 hr_utility.set_location('Processing '||l_proc, 70);
1009 END IF;
1010
1011 -- maintain table of retrieval rules
1012
1013 p_ret_rules(l_rr_index).rtr_grp_id := l_ret_ranges(ret_range_iter).rtr_grp_id;
1014 p_ret_rules(l_rr_index).time_recipient_id := l_time_recipient_id;
1015 p_ret_rules(l_rr_index).status := l_status;
1016
1017 FETCH csr_get_retrieval_rules INTO l_time_recipient_id, l_status;
1018
1019 l_rr_index := l_rr_index + 1;
1020
1021 END LOOP;
1022
1023 Close csr_get_retrieval_rules;
1024
1025 -- maintain l_rtr_exists
1026
1027 l_rtr_exists(l_rtr_grp_id).rtr_start := p_prefs(l_resource_index).rtr_start;
1028 l_rtr_exists(l_rtr_grp_id).rtr_stop := l_rr_index - 1;
1029 p_prefs(l_resource_index).rtr_end := l_rr_index - 1;
1030
1031 IF ( g_debug ) THEN
1032 hr_utility.set_location('Processing '||l_proc, 80);
1033 END IF;
1034
1035 END IF; -- csr_get_retrieval_rules%NOTFOUND
1036
1037 END IF; -- l_rtr_exists.EXISTS(l_resource_index)
1038
1039 -- now get the application period dates for these
1040
1041 IF ( g_debug ) THEN
1042 hr_utility.set_location('Processing '||l_proc, 90);
1043 END IF;
1044
1045 END IF; -- if not
1046 --(l_resource_rtr.exists(p_ret_range(ret_range).rtr_grp_id) then
1047
1048 END LOOP; -- FOR ret_range_iter l_ret_ranges.FIRST .. l_ret_ranges.LAST
1049
1050 -- make sure the retrieval rule did not error
1051
1052 IF NOT p_errors.EXISTS(l_resource_index)
1053 THEN
1054 IF ( g_debug ) THEN
1055 hr_utility.set_location('Processing '||l_proc, 95);
1056 END IF;
1057
1058 populate_rtr_outcomes (
1059 p_resource_id => l_resource_index
1060 , p_ret_ranges => l_ret_ranges
1061 , p_ret_rules_tab => p_ret_rules
1062 , p_ret_rules_start => p_prefs(l_resource_index).rtr_start
1063 , p_rtr_outcomes_tab => p_rtr_outcomes
1064 );
1065
1066 END IF; --IF NOT p_errors.EXISTS(l_resource_index)
1067
1068 IF ( g_debug ) THEN
1069 hr_utility.set_location('Processing '||l_proc, 97);
1070 END IF;
1071
1072 ELSE
1073
1074 -- not an error - there were just no valid RTRs for this person for this
1075 -- date range
1076
1077 p_prefs(l_resource_index).prefs_ok := 'N';
1078
1079 END IF; --IF (l_ret_ranges.COUNT > 0)
1080
1081 IF ( g_debug ) THEN
1082 hr_utility.set_location('Processing '||l_proc, 98);
1083 END IF;
1084
1085 END IF; -- NOT p_errors.EXISTS(l_resource_index) - hxc_no_hier_for_date check
1086
1087 END IF; -- NOT p_errors.EXISTS(l_resource_index) - preference call
1088
1089 l_resource_index := hxc_generic_retrieval_utils.g_resources.NEXT(l_resource_index);
1090
1091 END LOOP; -- g_resources loop
1092
1093 IF ( g_debug ) THEN
1094 hr_utility.set_location('Processing '||l_proc, 110);
1095 END IF;
1096
1097 EXCEPTION WHEN OTHERS
1098 THEN
1099 -- unhandled excpetion whilst processing resource table
1100
1101 IF ( g_debug ) THEN
1102 hr_utility.trace('in parse resources unhandled exception');
1103 hr_utility.trace('resource is '||to_char(l_resource_index));
1104 hr_utility.trace('error is '||SUBSTR(SQLERRM,1,150));
1105 END IF;
1106
1107 p_errors(l_resource_index).exception_description := SUBSTR(l_proc||':'||SQLERRM,1,2000);
1108 p_prefs(l_resource_index).prefs_ok := 'X';
1109 l_resource_index := hxc_generic_retrieval_utils.g_resources.NEXT(l_resource_index);
1110
1111 IF ( g_debug ) THEN
1112 hr_utility.set_location('Processing '||l_proc, 150);
1113 END IF;
1114
1115 END;
1116
1117 END LOOP; -- master g_resources loop
1118
1119 IF ( g_debug ) THEN
1120
1121 hr_utility.trace('****** Resources ********');
1122
1123 l_resource_index := hxc_generic_retrieval_utils.g_resources.FIRST;
1124
1125 WHILE l_resource_index IS NOT NULL
1126 LOOP
1127 hr_utility.trace('');
1128 hr_utility.trace('index is '||to_char(l_resource_index));
1129 hr_utility.trace('start time is '||to_char(hxc_generic_retrieval_utils.g_resources(l_resource_index).start_time,'dd-mon-yyyy'));
1130 hr_utility.trace('stop time is '||to_char(hxc_generic_retrieval_utils.g_resources(l_resource_index).stop_time,'dd-mon-yyyy'));
1131 hr_utility.trace('');
1132
1133 l_resource_index := hxc_generic_retrieval_utils.g_resources.NEXT(l_resource_index);
1134
1135 END LOOP;
1136
1137 hr_utility.trace('****** Prefs ********');
1138
1139 l_resource_index := p_prefs.FIRST;
1140
1141 WHILE l_resource_index IS NOT NULL
1142 LOOP
1143 hr_utility.trace('');
1144 hr_utility.trace('index is '||to_char(l_resource_index));
1145 hr_utility.trace('prefs_ok flag is '||p_prefs(l_resource_index).prefs_ok);
1146 hr_utility.trace('rtr grp start is '||to_char(p_prefs(l_resource_index).rtr_start));
1147 hr_utility.trace('rtr grp end is '||to_char(p_prefs(l_resource_index).rtr_end));
1148 hr_utility.trace('');
1149
1150 l_resource_index := p_prefs.NEXT(l_resource_index);
1151
1152 END LOOP;
1153
1154 hr_utility.trace('****** RTR Rules ********');
1155
1156 l_resource_index := p_ret_rules.FIRST;
1157
1158 WHILE l_resource_index IS NOT NULL
1159 LOOP
1160 hr_utility.trace('');
1161 hr_utility.trace('index is '||to_char(l_resource_index));
1162 hr_utility.trace('rtr grp id is '||to_char(p_ret_rules(l_resource_index).rtr_grp_id));
1163 hr_utility.trace('time recipient id is '||to_char(p_ret_rules(l_resource_index).time_recipient_id));
1164 hr_utility.trace('status is :'||p_ret_rules(l_resource_index).status||':');
1165 hr_utility.trace('outcome exists is '||p_ret_rules(l_resource_index).outcome_exists);
1166 hr_utility.trace('outcome start is '||to_char(p_ret_rules(l_resource_index).outcome_start));
1167 hr_utility.trace('outcome stop is '||to_char(p_ret_rules(l_resource_index).outcome_stop));
1168 hr_utility.trace('');
1169
1170 l_resource_index := p_ret_rules.NEXT(l_resource_index);
1171
1172 END LOOP;
1173
1174 hr_utility.trace('****** RTR Outcome ********');
1175
1176 l_resource_index := p_rtr_outcomes.FIRST;
1177
1178 WHILE l_resource_index IS NOT NULL
1179 LOOP
1180 hr_utility.trace('');
1181 hr_utility.trace('index is '||to_char(l_resource_index));
1182 hr_utility.trace('rtr grp id is '||to_char(p_rtr_outcomes(l_resource_index).rtr_grp_id));
1183 hr_utility.trace('time recipient id is '||to_char(p_rtr_outcomes(l_resource_index).time_recipient_id));
1184 hr_utility.trace('start time is '||to_char(p_rtr_outcomes(l_resource_index).start_time,'dd-mon-yyyy'));
1185 hr_utility.trace('stop time is '||to_char(p_rtr_outcomes(l_resource_index).stop_time,'dd-mon-yyyy'));
1186 hr_utility.trace('');
1187
1188 l_resource_index := p_rtr_outcomes.NEXT(l_resource_index);
1189
1190 END LOOP;
1191
1192 hr_utility.trace('****** Errors ********');
1193
1194 l_resource_index := p_errors.FIRST;
1195
1196 WHILE l_resource_index IS NOT NULL
1197 LOOP
1198 hr_utility.trace('');
1199 hr_utility.trace('index is '||to_char(l_resource_index));
1200 hr_utility.trace('exception is '||SUBSTR(p_errors(l_resource_index).exception_description,1,60));
1201 hr_utility.trace('');
1202
1203 l_resource_index := p_errors.NEXT(l_resource_index);
1204
1205 END LOOP;
1206
1207 END IF; -- g_debug
1208
1209 END parse_resources;
1210
1211 --
1212 -- private function
1213 -- chk_retrieve
1214 --
1215 -- description
1216 -- returns TRUE or FALSE depending on whether the bld blks is allowed
1217 -- to be retrieved based on the resources application set and retrieval
1218 -- time recipient and then if this is OK, based on the retrieval rules
1219 -- for the bld blks resource.
1220
1221 PROCEDURE chk_retrieve (
1222 p_resource_id NUMBER
1223 , p_bb_status VARCHAR2
1224 , p_bb_deleted VARCHAR2
1225 , p_bb_start_time DATE
1226 , p_bb_stop_time DATE
1227 , p_bb_id NUMBER -- 6121705
1228 , p_bb_ovn NUMBER -- 6121705
1229 , p_attribute_category VARCHAR2 -- Absences Integration -- 8779478
1230 , p_process VARCHAR2 -- 6121705
1231 , p_prefs t_pref
1232 , p_ret_rules t_ret_rule
1233 , p_rtr_outcomes t_rtr_outcome
1234 , p_tc_bb_id NUMBER
1235 , p_tc_bb_ovn NUMBER
1236 , p_timecard_retrieve IN OUT NOCOPY BOOLEAN
1237 , p_day_retrieve IN OUT NOCOPY BOOLEAN
1238 , p_tc_locked IN OUT NOCOPY BOOLEAN
1239 , p_tc_first_lock IN OUT NOCOPY BOOLEAN
1240 , p_bb_skipped_reason OUT NOCOPY VARCHAR2) IS
1241
1242 l_outcome_start PLS_INTEGER;
1243 l_outcome_stop PLS_INTEGER;
1244
1245 l_rtr_start PLS_INTEGER;
1246 l_rtr_end PLS_INTEGER;
1247
1248 l_working varchar2(15) := 'WORKING';
1249
1250 l_overall_outcome BOOLEAN := FALSE;
1251
1252 l_proc varchar2(72);
1253
1254 TYPE r_rtr_grp IS RECORD (dummy VARCHAR2(1));
1255 TYPE t_rtr_grp IS TABLE OF r_rtr_grp INDEX BY BINARY_INTEGER;
1256 l_rtr_grp t_rtr_grp;
1257
1258 l_old_rtr_grp_id NUMBER(15) := -1;
1259
1260 l_rsn_blk_wrking varchar2(80) := 'Block Status does not meet the Retrieval Rule Group preference';
1261 l_rsn_blk_not_approved varchar2(50) := 'Block is not yet Approved';
1262 -- Bug 8888911
1263 -- Reason corrected.
1264 l_rsn_blk_transferred varchar2(150):= 'Block does not meet Retrieval Preference for this process';
1265 l_rsn_locked varchar2(50) := 'Timecard is already locked';
1266 l_rsn_failed_lock varchar2(50) := 'Failed to obtain a lock';
1267
1268 -- Added for OTL-Absences Integration (Bug 8779478)
1269 -- Bug 9657355
1270 -- Reason corrected
1271 l_rsn_absence_detail varchar2(100) := 'Absence detail does not get processed by Transfer time from OTL to BEE process';
1272
1273
1274 BEGIN
1275
1276 g_debug := hr_utility.debug_enabled;
1277
1278 -- Absences code starts
1279 -- OTL-Absences Integration (Bug 8779478)
1280
1281
1282 IF g_debug THEN
1283 hr_utility.trace('ABS:Entered chk_retrieve');
1284 hr_utility.trace('ABS:p_attribute_category = '|| p_attribute_category);
1285 END IF;
1286
1287 IF (p_attribute_category like 'ELEMENT%'
1288 AND p_process IN ( 'BEE Retrieval Process',
1289 'Apply Schedule Rules') ) THEN -- Bug 16203299
1290
1291 IF g_debug THEN
1292 hr_utility.trace('ABS:p_bb_id = '||p_bb_id);
1293 hr_utility.trace('ABS:p_bb_ovn = '||p_bb_ovn);
1294 END IF;
1295
1296 IF (absence_link_exists(to_number(substr(p_attribute_category, 10)))) THEN
1297
1298 IF g_debug THEN
1299 hr_utility.trace('ABS:***** Skip this Absence record *****'|| p_bb_id);
1300 END IF;
1301
1302 p_timecard_retrieve := FALSE;
1303 p_day_retrieve := FALSE;
1304 p_bb_skipped_reason := l_rsn_absence_detail;
1305
1306 RETURN;
1307
1308 END IF;
1309 END IF;
1310
1311 -- Absences code ends
1312
1313
1314
1315 -- check to see if we have tried to lock this timecard before
1316 -- or if the timecard is already locked
1317
1318 IF ( ( p_tc_first_lock ) OR ( NOT p_tc_first_lock AND p_tc_locked ) )
1319 THEN
1320
1321 -- first check preferences were OK
1322 IF ( p_prefs(p_resource_id).prefs_ok = 'Y' AND p_bb_deleted = 'N' )
1323 THEN
1324
1325 l_rtr_start := p_prefs(p_resource_id).rtr_start;
1326 l_rtr_end := p_prefs(p_resource_id).rtr_end;
1327
1328 -- create a table of distinct RRG IDs for this resource
1329
1330 FOR x in l_rtr_start .. l_rtr_end
1331 LOOP
1332 l_rtr_grp(p_ret_rules(x).rtr_grp_id).dummy := 'Y';
1333 END LOOP;
1334
1335 WHILE l_rtr_grp.count >0 LOOP
1336
1337 FOR rtr_cnt IN l_rtr_start .. l_rtr_end
1338 LOOP
1339
1340 IF (l_rtr_grp.EXISTS(p_ret_rules(rtr_cnt).rtr_grp_id))
1341 THEN
1342 l_rtr_grp.DELETE(p_ret_rules(rtr_cnt).rtr_grp_id);
1343 END IF;
1344
1345 IF l_overall_outcome AND ( l_old_rtr_grp_id <> p_ret_rules(rtr_cnt).rtr_grp_id )
1346 THEN
1347 p_day_retrieve := TRUE;
1348 l_rtr_grp.DELETE;
1349 EXIT;
1350 END IF;
1351
1352 IF ( p_ret_rules(rtr_cnt).status <> l_working )
1353 THEN
1354
1355 -- need to test bld blk status
1356
1357 IF ( p_bb_status = 'WORKING' )
1358 THEN
1359 p_day_retrieve := FALSE;
1360 l_rtr_grp.DELETE;
1361 p_bb_skipped_reason := l_rsn_blk_wrking;
1362 EXIT;
1363 ELSE
1364
1365 -- test approval outcomes
1366
1367 IF ( p_ret_rules(rtr_cnt).outcome_exists = 'Y' )
1368 THEN
1369
1370 -- if they exist then loop for the time recipient id
1371 -- identified in the p_ret_rules for the resource
1372
1373 l_outcome_start := p_ret_rules(rtr_cnt).outcome_start;
1374 l_outcome_stop := p_ret_rules(rtr_cnt).outcome_stop;
1375
1376 l_overall_outcome := FALSE;
1377
1378 FOR outcome_cnt IN l_outcome_start .. l_outcome_stop
1379 LOOP
1380
1381 IF ( ( TRUNC(p_bb_start_time) >= p_rtr_outcomes(outcome_cnt).start_time )
1382 AND
1383 ( TRUNC(p_bb_start_time) <= p_rtr_outcomes(outcome_cnt).stop_time )
1384 AND
1385 ( TRUNC(p_bb_stop_time) >= p_rtr_outcomes(outcome_cnt).start_time )
1386 AND
1387 ( TRUNC(p_bb_stop_time) <= p_rtr_outcomes(outcome_cnt).stop_time ) )
1388 THEN
1389 -- approval OK
1390 l_overall_outcome := TRUE;
1391
1392 END IF;
1393
1394 END LOOP;
1395
1396 -- check to see that approvals for this time recipient
1397 -- were existing, if only one missing then do not retrieve
1398
1399 IF NOT ( l_overall_outcome )
1400 THEN
1401 IF l_rtr_grp.count =0 then
1402 p_day_retrieve := FALSE;
1403 p_bb_skipped_reason := l_rsn_blk_not_approved;
1404 EXIT;
1405 END IF;
1406 END IF;
1407
1408
1409 ELSE -- p_ret_rules(rtr_cnt).outcome_exists = 'Y'
1410
1411 IF l_rtr_grp.count =0 then
1412 p_day_retrieve := FALSE;
1413 p_bb_skipped_reason := l_rsn_blk_not_approved;
1414 EXIT;
1415 END IF;
1416
1417 END IF; -- p_ret_rules(rtr_cnt).outcome_exists = 'Y'
1418
1419 END IF; -- p_bb_status = 'WORKING'
1420
1421 END IF; -- p_ret_rules(rtr_cnt).status <> l_working
1422
1423 IF ( l_old_rtr_grp_id <> p_ret_rules(rtr_cnt).rtr_grp_id )
1424 THEN
1425 l_old_rtr_grp_id := p_ret_rules(rtr_cnt).rtr_grp_id;
1426 END IF;
1427
1428 END LOOP; -- rtr_cnt IN l_rtr_start .. l_rtr_end
1429
1430 END LOOP; -- WHILE l_rtr_grp.count >0 LOOP
1431
1432 ELSIF ( p_prefs(p_resource_id).prefs_ok = 'Y' AND p_bb_deleted = 'Y' )
1433 THEN
1434
1435 p_day_retrieve := TRUE;
1436
1437 ELSE
1438
1439 -- prefs_ok either 'X' or 'N' - either way no retrieve
1440
1441 p_timecard_retrieve := FALSE;
1442 p_day_retrieve := FALSE;
1443 p_bb_skipped_reason := l_rsn_blk_transferred;
1444
1445 END IF; -- p_prefs(p_resource_id).prefs_ok <> 'X'
1446
1447 -- now lock the TC if p_day_retrieve is TRUE
1448
1449 IF ( p_day_retrieve AND p_tc_first_lock )
1450 THEN
1451
1452 hxc_lock_api.request_lock
1453 (p_process_locker_type => hxc_generic_retrieval_pkg.g_lock_type
1454 ,p_time_building_block_id => p_tc_bb_id
1455 ,p_time_building_block_ovn => p_tc_bb_ovn
1456 ,p_expiration_time => 60
1457 ,p_messages => l_message_table
1458 ,p_row_lock_id => l_rowid
1459 ,p_locked_success => p_tc_locked
1460 ,p_transaction_lock_id => hxc_generic_retrieval_pkg.g_transaction_id
1461 );
1462
1463 IF ( NOT p_tc_locked )
1464 THEN
1465 IF ( g_debug ) THEN
1466 l_proc := g_package||'chk_retrieve';
1467 hr_utility.trace('not locked');
1468 hr_utility.trace('message is '||l_message_table(1).message_name);
1469 END IF;
1470
1471 p_day_retrieve := FALSE;
1472 p_timecard_retrieve := FALSE;
1473 p_bb_skipped_reason := l_rsn_failed_lock;
1474 END IF;
1475
1476 p_tc_first_lock := FALSE;
1477
1478 END IF;
1479
1480 ELSE
1481 -- we have not been able to lock this timecard
1482
1483 p_day_retrieve := FALSE;
1484 p_timecard_retrieve := FALSE;
1485 p_bb_skipped_reason := l_rsn_locked;
1486
1487 END IF; -- ( p_tc_first_lock OR p_tc_locked )
1488
1489 /*
1490 IF ( p_day_retrieve )
1491 THEN
1492 IF ( g_debug ) THEN
1493 hr_utility.trace('');
1494 hr_utility.trace('day retrieve is TRUE for resource '||to_char(p_resource_id));
1495 hr_utility.trace('day is '||to_char(p_bb_start_time, 'dd-mon-yyyy hh:mi:ss'));
1496 hr_utility.trace('');
1497 END IF;
1498 ELSE
1499 IF ( g_debug ) THEN
1500 hr_utility.trace('');
1501 hr_utility.trace('day retrieve is FALSE for resource '||to_char(p_resource_id));
1502 hr_utility.trace('');
1503 END IF;
1504 END IF;
1505
1506 IF ( p_timecard_retrieve )
1507 THEN
1508 IF ( g_debug ) THEN
1509 hr_utility.trace('');
1510 hr_utility.trace('time retrieve is TRUE for resource '||to_char(p_resource_id));
1511 hr_utility.trace('');
1512 END IF;
1513 ELSE
1514 IF ( g_debug ) THEN
1515 hr_utility.trace('');
1516 hr_utility.trace('time retrieve is FALSE for resource '||to_char(p_resource_id));
1517 hr_utility.trace('');
1518 END IF;
1519 END IF;
1520 */
1521
1522 -- Bug 6121705
1523 -- If this is BEE retrieval process, you have to do an additional check to find
1524 -- out if this record was transferred to BEE earlier while you are running
1525 -- transfer to OTM now and vice versa. After you decide to retrieve it,
1526 -- check if this is BEE retreival process.
1527 IF ((p_day_retrieve = TRUE) AND
1528 (p_process IN ( 'BEE Retrieval Process',
1529 'Apply Schedule Rules' ) ) )
1530 THEN
1531 -- Check if you need to make adjustment batches.
1532 IF ( chk_need_adj ( p_tc_bb_id,
1533 p_tc_bb_ovn,
1534 p_resource_id,
1535 p_bb_start_time,
1536 p_bb_id,
1537 p_bb_ovn, -- Bug 8366309
1538 'N', -- Bug 8366309 'N' is an UPDATE or NEW entry; DELETED = 'N'
1539 hxc_generic_retrieval_pkg.g_retrieval_process_id ) )
1540 THEN
1541 -- If you need adjustments, log it down.
1542 IF(g_debug)
1543 THEN
1544 hr_utility.trace('Resource '||p_resource_id||
1545 ' had a different Rules evaluation preference earlier and needs adjustment this time');
1546 END IF;
1547 END IF;
1548 END IF;
1549
1550
1551 END chk_retrieve;
1552
1553 PROCEDURE set_parent_statuses IS
1554
1555 l_proc varchar2(72);
1556
1557 TYPE r_day_record IS RECORD ( ind BINARY_INTEGER );
1558 TYPE t_day_table IS TABLE OF r_day_record INDEX BY BINARY_INTEGER;
1559
1560 l_day_table t_day_table;
1561
1562 l_day_parent hxc_time_building_blocks.time_building_block_id%TYPE;
1563
1564 l_day_changed BOOLEAN;
1565 l_same_timecard BOOLEAN;
1566
1567 l_day_index PLS_INTEGER;
1568 l_detail_index PLS_INTEGER;
1569
1570 l_last_day_index PLS_INTEGER;
1571 l_last_detail_index PLS_INTEGER;
1572
1573 l_overall_status hxc_transaction_details.status%TYPE;
1574
1575 BEGIN
1576 g_debug := hr_utility.debug_enabled;
1577
1578 IF ( g_debug ) THEN
1579 l_proc := g_package||'set_parent_statuses';
1580 hr_utility.set_location('Processing '||l_proc, 10);
1581 END IF;
1582
1583 IF ( ( hxc_generic_retrieval_pkg.t_tx_detail_bb_id.COUNT <> 0 )
1584 AND ( hxc_generic_retrieval_pkg.t_tx_time_bb_id.COUNT <> 0 )
1585 AND ( hxc_generic_retrieval_pkg.t_tx_day_bb_id.COUNT <> 0 ) )
1586 THEN
1587
1588 IF ( g_debug ) THEN
1589 hr_utility.set_location('Processing '||l_proc, 20);
1590 END IF;
1591
1592 -- loop through timecard statuses
1593
1594 FOR time IN hxc_generic_retrieval_pkg.t_tx_time_bb_id.FIRST ..
1595 hxc_generic_retrieval_pkg.t_tx_time_bb_id.LAST
1596 LOOP
1597 IF ( g_debug ) THEN
1598 hr_utility.set_location('Processing '||l_proc, 30);
1599 END IF;
1600
1601 l_day_parent := hxc_generic_retrieval_pkg.t_tx_time_bb_id(time);
1602 l_day_changed := TRUE;
1603
1604 -- loop through day statues
1605
1606 l_day_index := NVL( l_last_day_index, hxc_generic_retrieval_pkg.t_tx_day_bb_id.FIRST );
1607
1608 WHILE ( l_day_changed )
1609 LOOP
1610 IF ( g_debug ) THEN
1611 hr_utility.set_location('Processing '||l_proc, 40);
1612 END IF;
1613
1614 -- store day index in day table indexed by day bb id
1615
1616 l_day_table(hxc_generic_retrieval_pkg.t_tx_day_bb_id(l_day_index)).ind := l_day_index;
1617
1618 l_day_index := hxc_generic_retrieval_pkg.t_tx_day_bb_id.NEXT(l_day_index);
1619
1620 IF ( l_day_index IS NOT NULL )
1621 THEN
1622 IF ( g_debug ) THEN
1623 hr_utility.set_location('Processing '||l_proc, 50);
1624 END IF;
1625
1626 IF ( hxc_generic_retrieval_pkg.t_tx_day_parent_id(l_day_index) <> l_day_parent )
1627 THEN
1628 IF ( g_debug ) THEN
1629 hr_utility.set_location('Processing '||l_proc, 60);
1630 END IF;
1631
1632 l_day_changed := FALSE;
1633 l_last_day_index := l_day_index;
1634 END IF;
1635 ELSE
1636 IF ( g_debug ) THEN
1637 hr_utility.set_location('Processing '||l_proc, 70);
1638 END IF;
1639
1640 l_day_changed := FALSE;
1641 END IF;
1642
1643 IF ( g_debug ) THEN
1644 hr_utility.set_location('Processing '||l_proc, 80);
1645 END IF;
1646
1647 IF NOT ( l_day_changed )
1648 THEN
1649 IF ( g_debug ) THEN
1650 hr_utility.set_location('Processing '||l_proc, 90);
1651 END IF;
1652
1653 IF ( l_day_table.COUNT <> 0 )
1654 THEN
1655 IF ( g_debug ) THEN
1656 hr_utility.set_location('Processing '||l_proc, 100);
1657 END IF;
1658
1659 -- get detail statuses
1660
1661 l_same_timecard := TRUE;
1662 l_detail_index := NVL( l_last_detail_index,
1663 hxc_generic_retrieval_pkg.t_tx_detail_bb_id.FIRST );
1664 l_overall_status:= 'IN PROGRESS';
1665
1666 WHILE ( l_same_timecard )
1667 LOOP
1668 IF ( g_debug ) THEN
1669 hr_utility.set_location('Processing '||l_proc, 110);
1670 END IF;
1671
1672 IF ( hxc_generic_retrieval_pkg.t_tx_detail_status(l_detail_index) = 'ERRORS' )
1673 THEN
1674 l_overall_status := 'ERRORS';
1675
1676 ELSIF (hxc_generic_retrieval_pkg.t_tx_detail_status(l_detail_index) = 'SUCCESS'
1677 AND l_overall_status <> 'ERRORS' )
1678 THEN
1679 l_overall_status := 'SUCCESS';
1680 END IF;
1681
1682 IF ( g_debug ) THEN
1683 hr_utility.set_location('Processing '||l_proc, 120);
1684 END IF;
1685
1686 IF NOT ( l_day_table.EXISTS( hxc_generic_retrieval_pkg.t_tx_detail_parent_id(l_detail_index) ) )
1687 THEN
1688 IF ( g_debug ) THEN
1689 hr_utility.set_location('Processing '||l_proc, 130);
1690 END IF;
1691
1692 l_same_timecard := FALSE;
1693 l_last_detail_index := l_detail_index;
1694 hxc_generic_retrieval_pkg.t_tx_time_status(time) := l_overall_status;
1695 l_day_table.DELETE;
1696 ELSE
1697 IF ( g_debug ) THEN
1698 hr_utility.set_location('Processing '||l_proc, 140);
1699 END IF;
1700
1701
1702 hxc_generic_retrieval_pkg.t_tx_day_status(l_day_table(hxc_generic_retrieval_pkg.t_tx_detail_parent_id(l_detail_index)).ind)
1703 := hxc_generic_retrieval_pkg.t_tx_detail_status(l_detail_index);
1704 END IF;
1705
1706 l_detail_index :=
1707 hxc_generic_retrieval_pkg.t_tx_detail_bb_id.NEXT(l_detail_index);
1708
1709 IF ( l_detail_index IS NULL )
1710 THEN
1711 IF ( g_debug ) THEN
1712 hr_utility.set_location('Processing '||l_proc, 150);
1713 END IF;
1714
1715 hxc_generic_retrieval_pkg.t_tx_time_status(time) := l_overall_status;
1716 l_same_timecard := FALSE;
1717 END IF;
1718
1719 END LOOP; -- detail loop
1720
1721 END IF; -- l_day_table.COUNT <> 0
1722
1723 END IF; -- IF NOT ( l_day_changed )
1724
1725 END LOOP; -- day loop
1726
1727 END LOOP; -- timecard loop
1728
1729
1730 END IF; -- hxc_generic_retrieval_pkg.t_tx_detail_bb_id.COUNT <> 0 )
1731
1732 IF ( g_debug ) THEN
1733 hr_utility.set_location('Processing '||l_proc, 170);
1734 END IF;
1735
1736 END set_parent_statuses;
1737
1738
1739 PROCEDURE recovery ( p_process_id NUMBER
1740 , p_process VARCHAR2 ) IS
1741
1742 PRAGMA AUTONOMOUS_TRANSACTION;
1743
1744 CURSOR csr_chk_for_in_progress ( p_conc_date DATE ) IS
1745 SELECT transaction_id
1746 , TRUNC(transaction_date) transaction_date
1747 FROM hxc_transactions tx
1748 WHERE tx.transaction_process_id = p_process_id
1749 AND tx.type = 'RETRIEVAL'
1750 AND tx.status = 'IN PROGRESS'
1751 AND tx.transaction_date < p_conc_date
1752 FOR UPDATE OF status NOWAIT;
1753
1754 CURSOR csr_chk_conc_request ( p_conc_program_name VARCHAR2
1755 , p_app_id NUMBER ) IS
1756 SELECT MIN(cr.actual_start_date)
1757 FROM
1758 fnd_concurrent_programs cp
1759 , fnd_concurrent_requests cr
1760 WHERE
1761 cp.concurrent_program_name = p_conc_program_name AND
1762 cp.application_id = p_app_id
1763 AND
1764 cr.concurrent_program_id = cp.concurrent_program_id AND
1765 cr.status_code = 'R';
1766
1767 CURSOR csr_get_appl_id ( p_appl_short_name VARCHAR2 ) IS
1768 SELECT a.application_id
1769 FROM fnd_application a
1770 WHERE a.application_short_name = p_appl_short_name;
1771
1772 TYPE r_date_record IS RECORD ( run_date DATE );
1773 TYPE t_date_table IS TABLE OF r_date_record INDEX BY BINARY_INTEGER;
1774
1775 l_tx_tab t_date_table;
1776
1777 l_tx_rec csr_chk_for_in_progress%ROWTYPE;
1778
1779 l_req_date DATE;
1780
1781 l_index PLS_INTEGER;
1782
1783 l_cleaned_up BOOLEAN := FALSE;
1784 l_locking_retrieval_ranges BOOLEAN := FALSE;
1785 l_cnt NUMBER := 0;
1786
1787 l_proc varchar2(72);
1788
1789 l_appl_id fnd_application.application_id%TYPE;
1790
1791 BEGIN
1792
1793 g_debug := hr_utility.debug_enabled;
1794
1795 IF ( g_debug ) THEN
1796 l_proc := g_package||'recovery';
1797 hr_utility.set_location('Processing '||l_proc, 10);
1798
1799 hr_utility.trace('process id is '||to_char(p_process_id));
1800 hr_utility.trace('process is '||p_process);
1801 END IF;
1802
1803 WHILE NOT l_cleaned_up
1804 LOOP
1805
1806 BEGIN
1807
1808 -- get the minimum process date for this process
1809
1810 IF ( p_process = 'Projects Retrieval Process' )
1811 THEN
1812
1813 OPEN csr_get_appl_id ( 'PA' );
1814 FETCH csr_get_appl_id INTO l_appl_id;
1815 CLOSE csr_get_appl_id;
1816
1817 OPEN csr_chk_conc_request ( 'PAXTRTRX', l_appl_id );
1818 FETCH csr_chk_conc_request INTO l_req_date;
1819 CLOSE csr_chk_conc_request;
1820
1821 IF ( g_debug ) THEN
1822 hr_utility.trace('Minimum Conc Date for PA is '||to_char(l_req_date,'DD-MON-YYYY HH24:MI:SS'));
1823 END IF;
1824
1825 ELSIF ( p_process in ( 'BEE Retrieval Process', 'Apply Schedule Rules') )
1826 THEN
1827
1828 OPEN csr_get_appl_id ( 'PER' );
1829 FETCH csr_get_appl_id INTO l_appl_id;
1830 CLOSE csr_get_appl_id;
1831
1832 OPEN csr_chk_conc_request ( 'PYTSHPRI', l_appl_id );
1833 FETCH csr_chk_conc_request INTO l_req_date;
1834 CLOSE csr_chk_conc_request;
1835
1836 IF ( g_debug ) THEN
1837 hr_utility.trace('Minimum Conc Date for BEE is '||to_char(l_req_date,'DD-MON-YYYY HH24:MI:SS'));
1838 END IF;
1839
1840 ELSIF ( p_process = 'Purchasing Retrieval Process' )
1841 THEN
1842
1843 OPEN csr_get_appl_id ( 'PO' );
1844 FETCH csr_get_appl_id INTO l_appl_id;
1845 CLOSE csr_get_appl_id;
1846
1847 OPEN csr_chk_conc_request ( 'RCVGHXT', l_appl_id );
1848 FETCH csr_chk_conc_request INTO l_req_date;
1849 CLOSE csr_chk_conc_request;
1850
1851 IF ( g_debug ) THEN
1852 hr_utility.trace('Minimum Conc Date for PO is '||to_char(l_req_date,'DD-MON-YYYY HH24:MI:SS'));
1853 END IF;
1854
1855 ELSIF ( p_process = 'Maintenance Retrieval Process' )
1856 THEN
1857
1858 OPEN csr_get_appl_id ( 'EAM' );
1859 FETCH csr_get_appl_id INTO l_appl_id;
1860 CLOSE csr_get_appl_id;
1861
1862 OPEN csr_chk_conc_request ( 'EAMTROTL', l_appl_id );
1863 FETCH csr_chk_conc_request INTO l_req_date;
1864 CLOSE csr_chk_conc_request;
1865
1866 IF ( g_debug ) THEN
1867 hr_utility.trace('Minimum Conc Date for EAM is '||to_char(l_req_date,'DD-MON-YYYY HH24:MI:SS'));
1868 END IF;
1869
1870 END IF;
1871
1872 IF ( g_debug ) THEN
1873 hr_utility.set_location('Processing '||l_proc, 20);
1874 END IF;
1875
1876 -- chk to see if there are any IN PROGRESS transactions for this retrieval process
1877 -- which were created before the minimum process date - these MUST be rogue
1878 -- transactions
1879
1880 OPEN csr_chk_for_in_progress ( l_req_date );
1881 FETCH csr_chk_for_in_progress INTO l_tx_rec;
1882
1883 IF ( g_debug ) THEN
1884 hr_utility.set_location('Processing '||l_proc, 25);
1885 END IF;
1886
1887 IF ( csr_chk_for_in_progress%FOUND )
1888 THEN
1889 -- if we know there are outstanding transactions then lock the retrieval
1890 -- range table now to ensure no other processes are accessing it
1891
1892 IF ( g_debug ) THEN
1893 hr_utility.trace('Locking table');
1894 END IF;
1895
1896 l_locking_retrieval_ranges := TRUE;
1897
1898 LOCK TABLE hxc_retrieval_ranges IN EXCLUSIVE MODE NOWAIT;
1899
1900 END IF;
1901
1902 IF ( g_debug ) THEN
1903 hr_utility.set_location('Processing '||l_proc, 30);
1904 END IF;
1905
1906 WHILE csr_chk_for_in_progress%FOUND
1907 LOOP
1908
1909 IF ( g_debug ) THEN
1910 hr_utility.trace('tx id is '||to_char(l_tx_rec.transaction_id));
1911 hr_utility.trace('tx date is '||to_char(l_tx_rec.transaction_date,'DD-MON-YYYY HH24:MI:SS'));
1912 END IF;
1913
1914 l_tx_tab(l_tx_rec.transaction_id).run_date := l_tx_rec.transaction_date;
1915
1916 FETCH csr_chk_for_in_progress INTO l_tx_rec;
1917
1918 END LOOP;
1919
1920 IF ( g_debug ) THEN
1921 hr_utility.set_location('Processing '||l_proc, 40);
1922 END IF;
1923
1924 IF l_tx_tab.COUNT > 0
1925 THEN
1926
1927 IF ( g_debug ) THEN
1928 hr_utility.set_location('Processing '||l_proc, 50);
1929 END IF;
1930
1931 -- we have some rogue transactions - let's update them
1932
1933 l_index := l_tx_tab.FIRST;
1934
1935 WHILE l_index IS NOT NULL
1936 LOOP
1937
1938 IF ( g_debug ) THEN
1939 hr_utility.trace('Updating tx id '||to_char(l_index));
1940 END IF;
1941
1942 UPDATE hxc_transactions tx
1943 SET tx.status = 'RECOVERED'
1944 WHERE tx.transaction_id = l_index;
1945
1946 l_index := l_tx_tab.NEXT(l_index);
1947
1948 -- unlock any outstanding TC locks
1949
1950 hxc_lock_api.release_lock ( p_transaction_lock_id => l_index
1951 , p_process_locker_type => hxc_generic_retrieval_pkg.g_lock_type
1952 , p_row_lock_id => NULL
1953 , p_messages => l_message_table
1954 , p_released_success => l_boolean );
1955
1956 END LOOP;
1957
1958 -- COMMIT;
1959
1960 IF ( g_debug ) THEN
1961 hr_utility.set_location('Processing '||l_proc, 60);
1962 END IF;
1963
1964
1965 END IF; -- l_tx_tab.COUNT > 0
1966
1967 CLOSE csr_chk_for_in_progress;
1968
1969 -- now check to see if there are any retrieval ranges which need to be
1970 -- cleared up (even if there were no IN PROGRESS transactions
1971
1972 -- NOTE: removed lock table here since if there are orphaned ranges here
1973 -- without in progress transactions then the transactions must have
1974 -- been updated in SQL. 99.9% of the time the transaction will
1975 -- exist thus the table will be locked
1976
1977 UPDATE hxc_retrieval_ranges rr
1978 SET rr.transaction_id = -999
1979 WHERE rr.creation_date < l_req_date
1980 AND rr.retrieval_process_id = p_process_id
1981 AND rr.transaction_id = 0;
1982
1983 COMMIT;
1984
1985 l_cleaned_up := TRUE;
1986
1987 EXCEPTION WHEN OTHERS
1988 THEN
1989
1990 IF ( SQLCODE = '-54' )
1991 THEN
1992 IF ( g_debug ) THEN
1993 hr_utility.set_location('Processing '||l_proc, 70);
1994 END IF;
1995
1996 IF ( l_locking_retrieval_ranges )
1997 THEN
1998
1999 CLOSE csr_chk_for_in_progress;
2000
2001 l_locking_retrieval_ranges := FALSE;
2002
2003 END IF;
2004
2005 l_cnt := l_cnt + 1;
2006 ELSE
2007
2008 IF ( g_debug ) THEN
2009 hr_utility.trace('SQLERRM is '||SQLERRM);
2010 END IF;
2011
2012 raise;
2013 END IF;
2014
2015 END;
2016
2017 IF ( l_cnt > 10000 )
2018 THEN
2019
2020 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
2021 fnd_message.set_token('PROCEDURE', l_proc);
2022 fnd_message.set_token('STEP','recovery cannot clean up');
2023 fnd_message.raise_error;
2024
2025 END IF;
2026
2027 END LOOP;
2028
2029 END recovery;
2030
2031 FUNCTION chk_terminated ( p_conc_request_id NUMBER ) RETURN BOOLEAN IS
2032
2033 CURSOR csr_chk_terminated IS
2034 SELECT cr.status_code
2035 FROM fnd_concurrent_requests cr
2036 WHERE cr.request_id = p_conc_request_id;
2037
2038 l_status_code fnd_concurrent_requests.status_code%TYPE;
2039
2040 BEGIN
2041
2042 OPEN csr_chk_terminated;
2043 FETCH csr_chk_terminated INTO l_status_code;
2044 CLOSE csr_chk_terminated;
2045
2046 IF ( l_status_code <> 'R' )
2047 THEN
2048 RETURN TRUE;
2049 ELSE
2050 RETURN FALSE;
2051 END IF;
2052
2053 END chk_terminated;
2054
2055 FUNCTION get_ret_criteria
2056 RETURN VARCHAR2
2057 IS
2058 l_ret_criteria_clause VARCHAR2(1000) := null;
2059
2060
2061 l_payroll_criteria VARCHAR2(200) := ' and paa.payroll_id = :p_payroll_id ';
2062 l_location_criteria VARCHAR2(200) := ' and paa.location_id = :p_location_id ';
2063 l_org_criteria VARCHAR2(200) := ' and paa.organization_id = :p_org_id ';
2064
2065 l_eff_date_criteria VARCHAR2(200) := '
2066 and tbb_latest.start_time
2067 between paa.effective_start_date and paa.effective_end_date )';
2068
2069 l_ret_criteria hxc_generic_retrieval_pkg.r_ret_criteria;
2070 BEGIN
2071
2072 l_ret_criteria := hxc_generic_retrieval_pkg.g_ret_criteria;
2073
2074 IF l_ret_criteria.gre_id is not null then
2075 l_ret_criteria_clause := ' AND exists (
2076 select 1
2077 from per_all_assignments_f paa, hr_soft_coding_keyflex hsk
2078 where paa.person_id = rrr.resource_id
2079 and paa.soft_coding_keyflex_id = hsk.soft_coding_keyflex_id
2080 and hsk.segment1 = :p_gre_id
2081 ';
2082 else
2083 l_ret_criteria_clause := ' AND exists (
2084 select 1
2085 from per_all_assignments_f paa
2086 where paa.person_id = rrr.resource_id
2087 ';
2088 END IF;
2089
2090 IF l_ret_criteria.payroll_id is not null then
2091 l_ret_criteria_clause := l_ret_criteria_clause ||l_payroll_criteria;
2092 END IF;
2093
2094 IF l_ret_criteria.location_id is not null then
2095 l_ret_criteria_clause := l_ret_criteria_clause ||l_location_criteria;
2096 END IF;
2097
2098 IF l_ret_criteria.organization_id is not null then
2099 l_ret_criteria_clause := l_ret_criteria_clause ||l_org_criteria;
2100 END IF;
2101
2102 IF l_ret_criteria.payroll_id is null and
2103 l_ret_criteria.location_id is null and
2104 l_ret_criteria.organization_id is null and
2105 l_ret_criteria.gre_id is null
2106 then
2107 l_ret_criteria_clause := null;
2108 else
2109 l_ret_criteria_clause := l_ret_criteria_clause ||l_eff_date_criteria;
2110 END IF;
2111
2112 RETURN l_ret_criteria_clause;
2113
2114 END get_ret_criteria;
2115
2116 -- Added this function for Absences Integration
2117 -- OTL-Absences Integration (Bug 8779478)
2118 FUNCTION absence_link_exists
2119 (p_element_type_id NUMBER)
2120 RETURN BOOLEAN
2121 IS
2122
2123 l_exists NUMBER;
2124
2125 BEGIN
2126
2127 SELECT count(*) INTO l_exists
2128 FROM hxc_absence_type_elements
2129 WHERE element_type_id = p_element_type_id
2130 AND rownum < 2;
2131
2132 IF (l_exists = 0) THEN
2133 IF g_debug THEN
2134 hr_utility.trace('Absence Element - False');
2135 END IF;
2136
2137 RETURN FALSE;
2138 ELSE
2139 IF g_debug THEN
2140 hr_utility.trace('Absence Element - True');
2141 END IF;
2142
2143 RETURN TRUE;
2144 END IF;
2145
2146
2147 END absence_link_exists;
2148
2149
2150 end hxc_generic_retrieval_utils;
[Home] [Help]