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