1 PACKAGE BODY hxc_preference_evaluation AS
2 /* $Header: hxcpfevl.pkb 120.20 2011/08/10 10:23:15 asrajago ship $ */
3
4 g_migration_mode BOOLEAN := FALSE;
5 g_package VARCHAR2 (72) := 'hxc_preference_evaluation';
6 g_debug boolean := hr_utility.debug_enabled;
7
8 -- will use PL/SQL tables in various places to manipulate preference information
9
10 TYPE t_str_version_row IS RECORD
11 ( org_Structure_id per_organization_structures.organization_structure_id%TYPE,
12 org_version_id per_org_structure_versions.org_structure_version_id%type,
13 time_info date);
14
15 TYPE t_requested_pref IS RECORD
16 ( code hxc_pref_definitions.code%TYPE,
17 attr_list VARCHAR2(90) );
18
19 TYPE t_dated_pref_row IS RECORD
20 ( start_date DATE,
21 end_date DATE,
22 code hxc_pref_definitions.code%TYPE,
23 rule_evaluation_order NUMBER,
24 pref_ref NUMBER,
25 edit_allowed hxc_pref_hierarchies.edit_allowed%TYPE);
26
27 TYPE t_hierarchy_list_row IS RECORD
28 (
29 pref_hierarchy_id NUMBER,
30 start_date DATE,
31 end_date DATE,
32 elig_start_date DATE,
33 elig_end_date DATE,
34 rule_evaluation_order NUMBER);
35
36 TYPE t_requested_pref_list IS TABLE OF
37 t_requested_pref
38 INDEX BY BINARY_INTEGER;
39
40 TYPE t_pref_trans IS TABLE OF
41 hxc_pref_definitions.code%TYPE
42 INDEX BY BINARY_INTEGER;
43
44 TYPE t_hierarchy_list IS TABLE OF
45 t_hierarchy_list_row
46 INDEX BY BINARY_INTEGER;
47
48 -- when evaluating preferences with a range of dates, its possible to have
49 -- more than one value for a single preference. The following structure is used
50 -- to keep track of which rows in a preference table are for which preference.
51
52 TYPE t_dated_prefs IS TABLE OF
53 t_dated_pref_row
54 INDEX BY BINARY_INTEGER;
55
56 TYPE t_str_version is table of t_str_version_row
57 index by binary_integer;
58
59 g_str_version t_str_version;
60
61 g_input_separator VARCHAR2(1) := '|';
62 g_raise_fatal_errors BOOLEAN := TRUE;
63 g_fatal_error_occurred BOOLEAN := FALSE;
64 g_fatal_error VARCHAR2(30) :='';
65
66 -- variables and type for sorted date range preference evaluation
67
68 g_sort_pref_table t_pref_table;
69
70 TYPE r_sort_cache IS RECORD ( resource_id NUMBER(15), start_date DATE, end_date DATE);
71 TYPE t_sort_cache IS TABLE OF r_sort_cache INDEX BY BINARY_INTEGER;
72
73 g_sort_cache t_sort_cache;
74
75 --------
76 -- Procedure RETURNs all the preferences for a given resource_id
77 --------
78 --------Function added for the bug 3868611
79
80 Function check_number(p_string in varchar2) return number is
81 l_string number;
82 BEGIN
83 l_string:= to_number(p_string);
84 return(l_string);
85 EXCEPTION
86 WHEN OTHERS THEN
87 return(null);
88 END;
89
90
91
92 PROCEDURE resource_preferences(p_resource_id IN NUMBER,
93 p_pref_table IN OUT NOCOPY t_pref_table,
94 p_evaluation_date IN DATE default sysdate,
95 p_user_id IN number default fnd_global.user_id,
96 p_resp_id IN number default -99,
97 p_ignore_user_id in boolean default false,
98 p_ignore_resp_id in boolean default false)
99
100 IS
101
102 l_req NUMBER;
103 l_personal_hierarchy NUMBER;
104 l_hier NUMBER;
105 l_requested_list t_requested_pref_list;
106 l_pref_trans t_pref_trans;
107 l_hierarchy_list t_hierarchy_list;
108 l_evaluation_date DATE;
109 l_hier_count number;
110 l_employee_id NUMBER;
111
112 --Added By Mithun for CWK Terminate Bug
113 cwk_final_process_date DATE;
114 l_num_of_days_to_add NUMBER;
115
116 -- Bug 3297639
117 l_pref_index number;
118 l_preference_id number;
119 l_last_updated_date date;
120 l_use_cache boolean := FALSE;
121
122 --Added By Mithun
123 l_resp_id NUMBER;
124 CURSOR c_get_last_updated_date(p_pref_hierarchy_id IN number) IS
125 Select last_update_date
126 From hxc_pref_hierarchies
127 Where pref_hierarchy_id = p_pref_hierarchy_id;
128
129 -- CURSORs to find the hierarchies a resource is eligible for ...
130 -- To support new criteria add new CURSORs or add to existing CURSOR.
131
132 CURSOR get_employee_id(p_user_id IN Number) Is
133
134 Select employee_id from fnd_user
135 Where user_id = p_user_id;
136
137
138 /*Cursor Modified By Mithun for CWK Terminate Bug*/
139 -- Modified the cursor to support CWK.
140 CURSOR c_eligible_hierarchies_basic(p_resource_id IN NUMBER,
141 p_evaluation_date IN DATE) IS
142
143 SELECT hrr.pref_hierarchy_id, hrr.rule_evaluation_order
144 FROM hxc_resource_rules hrr,
145 per_all_assignments_f pa
146 WHERE pa.person_id = p_resource_id
147 AND nvl(hrr.business_group_id,pa.business_group_id) = pa.business_group_id
148 AND pa.primary_flag = 'Y'
149 and pa.assignment_type in ('E','C')
150 AND p_evaluation_date
151 BETWEEN pa.effective_start_date
152 AND Decode(pa.assignment_type , 'C',
153 Decode(cwk_final_process_date,pa.effective_END_date,pa.effective_END_date + l_num_of_days_to_add ,pa.effective_END_date),pa.effective_END_date)
154 AND p_evaluation_date between hrr.start_date and hrr.end_date
155 AND (( to_char(pa.assignment_id) = hrr.eligibility_criteria_id
156 AND hrr.eligibility_criteria_type = 'ASSIGNMENT')
157 OR ( to_char(pa.payroll_id) = hrr.eligibility_criteria_id
158 AND hrr.eligibility_criteria_type = 'PAYROLL')
159 OR ( to_char(pa.person_id) = hrr.eligibility_criteria_id
160 AND hrr.eligibility_criteria_type = 'PERSON')
161 OR ( to_char(pa.location_id) = hrr.eligibility_criteria_id
162 AND hrr.eligibility_criteria_type = 'LOCATION')
163 OR ( pa.EMPLOYEE_CATEGORY = hrr.eligibility_criteria_id
164 AND hrr.eligibility_criteria_type = 'EMP_CATEGORY')
165 OR ( pa.EMPLOYMENT_CATEGORY = hrr.eligibility_criteria_id
166 AND hrr.eligibility_criteria_type = 'ASGN_CATEGORY')
167 OR ( to_char(pa.organization_id) = hrr.eligibility_criteria_id
168 AND hrr.eligibility_criteria_type = 'ORGANIZATION')
169 OR (
170 hrr.eligibility_criteria_type = 'ALL_PEOPLE'));
171
172
173 -- Bug 7484448
174 -- Added USE_NL in the below query for optimum perf.
175 /*Cursor Modified By Mithun for CWK Terminate Bug*/
176 CURSOR c_eligible_hierarchies_rollup(p_resource_id IN NUMBER,
177 p_evaluation_date IN DATE) IS
178 SELECT /*+ USE_NL(PA HRR) */
179 hrr.pref_hierarchy_id, hrr.rule_evaluation_order
180 FROM hxc_resource_rules hrr,
181 per_all_assignments_f pa
182 WHERE pa.person_id = p_resource_id
183 AND nvl(hrr.business_group_id,pa.business_group_id) = pa.business_group_id
184 AND pa.primary_flag = 'Y'
185 and pa.assignment_type in ('E','C')
186 AND p_evaluation_date
187 BETWEEN pa.effective_start_date
188 AND Decode(pa.assignment_type , 'C',
189 Decode(cwk_final_process_date,pa.effective_END_date,pa.effective_END_date + l_num_of_days_to_add ,pa.effective_END_date),pa.effective_END_date)
190 AND p_evaluation_date between hrr.start_date and hrr.end_date
191 AND (
192 ((HXC_PREFERENCE_EVALUATION.check_number(substr(hrr.eligibility_criteria_id,instr(hrr.eligibility_criteria_id,'-',1,1)+1)) in
193 (SELECT pose.organization_id_parent
194 FROM
195 per_org_structure_elements pose
196 start with organization_id_child = pa.organization_id
197 and pose.org_structure_version_id=
198 HXC_PREFERENCE_EVALUATION.return_version_id(HXC_PREFERENCE_EVALUATION.check_number(substr(hrr.eligibility_criteria_id,1,instr(hrr.eligibility_criteria_id,'-',1,1)-1)),
199 hrr.eligibility_criteria_type)
200 connect by prior organization_id_parent=organization_id_child
201 and pose.org_structure_version_id=
202 HXC_PREFERENCE_EVALUATION.return_version_id(HXC_PREFERENCE_EVALUATION.check_number(substr(hrr.eligibility_criteria_id,1,instr(hrr.eligibility_criteria_id,'-',1,1)-1)),
203 hrr.eligibility_criteria_type)
204 union
205 select organization_id
206 from hr_all_organization_units
207 where organization_id = pa.organization_id))
208 AND hrr.eligibility_criteria_type = 'ROLLUP' )
209 );
210
211 /*Cursor Modified By Mithun for CWK Terminate Bug*/
212 -- Modified cursor to support CWK.
213 CURSOR c_eligible_hierarchies_flex(p_resource_id IN NUMBER,
214 p_evaluation_date IN DATE) IS
215 SELECT hrr.pref_hierarchy_id, hrr.rule_evaluation_order
216 FROM hxc_resource_rules hrr,
217 per_all_assignments_f pa
218 WHERE pa.person_id = p_resource_id
219 AND nvl(hrr.business_group_id,pa.business_group_id) = pa.business_group_id
220 AND pa.primary_flag = 'Y'
221 and pa.assignment_type in ('E','C')
222 AND p_evaluation_date
223 BETWEEN pa.effective_start_date
224 AND Decode(pa.assignment_type , 'C',
225 Decode(cwk_final_process_date,pa.effective_END_date,pa.effective_END_date + l_num_of_days_to_add ,pa.effective_END_date),pa.effective_END_date)
226 AND p_evaluation_date between hrr.start_date and hrr.end_date
227 AND ( (DECODE ( SUBSTR( hrr.eligibility_criteria_type, 1, 3 ),
228 'SCL', DECODE ( pa.soft_coding_keyflex_id, NULL, -1,
229 hxc_resource_rules_utils.chk_flex_valid ('SCL', pa.soft_coding_keyflex_id,
230 SUBSTR( hrr.eligibility_criteria_type, 5 ),
231 hrr.eligibility_criteria_id )), -1 ) = 1 )
232 OR
233 (DECODE ( SUBSTR( hrr.eligibility_criteria_type, 1, 6 ),
234 'PEOPLE', DECODE ( pa.people_group_id, NULL, -1,
235 hxc_resource_rules_utils.chk_flex_valid ( 'PEOPLE', pa.people_group_id,
236 SUBSTR( hrr.eligibility_criteria_type, 8 ),
237 hrr.eligibility_criteria_id )), -1 ) = 1 )
238 OR
239 (DECODE ( SUBSTR( hrr.eligibility_criteria_type, 1, 5 ),
240 'GRADE', DECODE ( pa.grade_id, NULL, -1,
241 hxc_resource_rules_utils.chk_flex_valid ( 'GRADE', pa.grade_id,
242 SUBSTR( hrr.eligibility_criteria_type, 7 ),
243 hrr.eligibility_criteria_id )), -1 ) = 1 )
244 OR
245
246 (DECODE ( SUBSTR( hrr.eligibility_criteria_type, 1, 3 ),
247 'JOB', hrr.eligibility_criteria_id, -1 ) = to_char(pa.job_id))-- Issue 4
248
249 OR
250
251 (DECODE ( SUBSTR( hrr.eligibility_criteria_type, 1, 8 ),
252 'POSITION', hrr.eligibility_criteria_id, -1 ) = to_char(pa.position_id)) -- Issue 4
253 );
254
255 -- Cursor to check whether the resource has personalized preferences.
256 -- Note that this CURSOR could be removed in order to possibly boost performance
257 -- But, keep it simple for now.
258
259 CURSOR c_personal_hierarchy(p_user_id IN NUMBER) IS
260 SELECT hrr.pref_hierarchy_id , hrr.rule_evaluation_order
261 FROM hxc_resource_rules hrr
262 WHERE hrr.resource_type='PERSON'
263 AND hrr.eligibility_criteria_type = 'LOGIN'
264 AND hrr.eligibility_criteria_id = to_char(p_user_id) ; -- Issue 4
265
266
267
268 -- Cursor to pick up any responsibility based prefs(both responsibility and perst responsibility)
269
270 CURSOR c_resp_hierarchies(p_responsibility_id IN NUMBER,
271 p_evaluation_date IN DATE) IS
272 SELECT hrr.pref_hierarchy_id ,
273 hrr.rule_evaluation_order
274 FROM hxc_resource_rules hrr
275 WHERE hrr.resource_type='PERSON'
276 AND p_evaluation_date BETWEEN hrr.start_date
277 AND hrr.end_date
278 AND hrr.eligibility_criteria_type IN ('RESPONSIBILITY','PERST_RESPONSIBILITY')
279 AND hrr.eligibility_criteria_id = to_char(p_responsibility_id); -- Issue 4
280
281
282
283 --Added By Mithun
284 --Cursor to pick up only persistent responsibility based preference
285 CURSOR c_perst_resp_hierarchies(p_responsibility_id IN NUMBER,
286 p_evaluation_date IN DATE) IS
287 SELECT hrr.pref_hierarchy_id ,
288 hrr.rule_evaluation_order
289 FROM hxc_resource_rules hrr
290 WHERE hrr.resource_type='PERSON'
291 AND p_evaluation_date BETWEEN hrr.start_date
292 AND hrr.end_date
293 AND hrr.eligibility_criteria_type = 'PERST_RESPONSIBILITY'
294 AND hrr.eligibility_criteria_id = to_char(p_responsibility_id);
295
296 -- Issue 4
297
298
299 /*Cursor Modified By Mithun for CWK Terminate Bug*/
300 CURSOR c_person_type_hierarchies(p_resource_id IN NUMBER,p_evaluation_date IN DATE) IS
301 SELECT hrr.pref_hierarchy_id , hrr.rule_evaluation_order
302 FROM hxc_resource_rules hrr,
303 per_person_types typ,
304 per_person_type_usages_f ptu
305 WHERE hrr.resource_type='PERSON'
306 AND p_evaluation_date between hrr.start_date and hrr.end_date
307 AND hrr.eligibility_criteria_type = 'PERSON_TYPE'
308 AND hrr.eligibility_criteria_id = to_char(ptu.person_type_id) -- Issue 4
309 AND ptu.person_id = p_resource_id
310 AND typ.system_person_type IN ('EMP','EX_EMP','EMP_APL','EX_EMP_APL','CWK','EX_CWK')
311 AND typ.person_type_id = ptu.person_type_id
312 AND p_evaluation_date between Ptu.effective_start_date
313 and Decode(typ.system_person_type , 'CWK',
314 Decode(cwk_final_process_date,Ptu.effective_end_date, Ptu.effective_end_date + l_num_of_days_to_add , Ptu.effective_end_date) ,Ptu.effective_end_date);
315 ------------------------------------------------------------------------------------
316 -- Cursor to RETURN the preference nodes in a hierarchy. Note that this is based
317 -- on a hierchical query. This can be removed if necessary by denormalization
318 -- on the hxc_pref_hierarchy base table
319
320 CURSOR c_pref_nodes(p_hierarchy_id IN NUMBER)
321 IS
322 SELECT pref_hierarchy_id
323 ,pref_definition_id preference_id
324 ,attribute1
325 ,attribute2
326 ,attribute3
327 ,attribute4
328 ,attribute5
329 ,attribute6
330 ,attribute7
331 ,attribute8
332 ,attribute9
333 ,attribute10
334 ,attribute11
335 ,attribute12
336 ,attribute13
337 ,attribute14
338 ,attribute15
339 ,attribute16
340 ,attribute17
341 ,attribute18
342 ,attribute19
343 ,attribute20
344 ,attribute21
345 ,attribute22
346 ,attribute23
347 ,attribute24
348 ,attribute25
349 ,attribute26
350 ,attribute27
351 ,attribute28
352 ,attribute29
353 ,attribute30
354 ,edit_allowed
355 ,displayed
356 ,name
357 ,top_level_parent_id --Performance Fix
358 ,code
359 FROM hxc_pref_hierarchies
360 WHERE top_level_parent_id = p_hierarchy_id;
361 -- pref_definition_id is not null
362 -- START WITH pref_hierarchy_id = p_hierarchy_id
363 -- CONNECT BY prior pref_hierarchy_id = parent_pref_hierarchy_id;
364 -- Performance Fix.
365 /*CURSOR c_pref_codes
366 IS
367 SELECT
368 pref_definition_id, code
369 FROM hxc_pref_definitions;*/
370
371 l_user_id NUMBER;
372
373 /* Mikarthi Terminated CWK Enhancement */
374 Cursor c_cwk_terminate_date( p_person_id IN NUMBER, p_evaluation_date IN DATE) is
375 Select NVL(final_process_date, hr_general.end_of_time)
376 from per_periods_of_placement
377 where person_id = p_person_id
378 and date_start <= p_evaluation_date
379 order by date_start desc;
380
381 /*End of Terminated CWK Enhancement Addtion */
382
383 l_find_resp_required BOOLEAN DEFAULT FALSE;
384
385 BEGIN
386
387 --Here we are checking whether it is really required to do preference evaluation based on
388 --Responsibility. There can be three conditions
389 --1) p_resp_id = -99 This is the default value for the parameter. So this could come
390 -- either because no value was passed while calling resource_preference,
391 -- or explicitly -99 was passed while calling resource preference. In either case
392 -- persistent responsibiilty preference evalution would be done. Which means, we
393 -- would obtain the responsibility stored in the timecard and then obtain preferences
394 -- attached to that responsibility, if any. If no valid responsibility is obtained
395 -- from the tc and if employee_id and the resource_id is the same, then we will do
396 -- persistent resp evalution based on the FND_global.resp_id
397 --2) p_resp_id = -101 we dont have to do preference evalution on persistent responsibility
398 --
399
400 g_debug := hr_utility.debug_enabled;
401
402 IF g_debug
403 THEN
404 hr_utility.trace('Evaluation pref for p_evaluation_date ');
405 hr_utility.trace('p_resource_id '||p_resource_id);
406 hr_utility.trace('p_evaluation_date '||p_evaluation_date);
407 END IF;
408
409 l_resp_id := p_resp_id;
410 l_user_id := p_user_id;
411
412 -- Find out if the resource himself is logged in.
413 -- In that case, there is no need of looking into any
414 -- timecard, consider persistent responsibility also as
415 -- session responsibility.
416
417 OPEN get_employee_id(fnd_global.user_id);
418
419 FETCH get_employee_id
420 INTO l_employee_id ;
421
422 CLOSE get_employee_id;
423
424 IF ( p_resp_id = -99 ) AND (l_employee_id <> p_resource_id)
425 THEN
426
427 l_find_resp_required := TRUE;
428
429 ELSE
430
431 l_find_resp_required := FALSE;
432
433 END IF;
434
435 -- Either ways, you need this responsibility. This is the key
436 -- in case the user is the employee himself.
437
438 l_resp_id := FND_GLOBAL.RESP_ID;
439
440
441 IF g_debug
442 THEN
443 hr_utility.trace('Current user''s user_id is '||fnd_global.user_id);
444 hr_utility.trace('Current user''s person_id is '||l_employee_id);
445 END IF;
446
447 -- make sure pref table is empty - otherwise this will interfere with the evaluation
448 p_pref_table.delete;
449
450 -- l_evaluation_date:=trunc(p_evaluation_date); -- replaced for bug 3097015 with
451 -- the following function call ...
452 l_evaluation_date :=
453 evaluation_date (
454 p_resource_id=> p_resource_id,
455 p_evaluation_date=> p_evaluation_date
456 );
457
458 -- populate table that will allow us to find a pref_code given an
459 -- pref_definition_id
460 -- note:
461 -- a) could do this join in one of the CURSORs - choose not to do this to avoid
462 -- complexity in the SQL which might lead to overly complex execution plan.
463 -- b) could denormalize the code onto the pref_hierarchies table thus making
464 -- this step redundent.
465 -- c) could turn this into a bulk collect - pick up the values AND THEN
466 -- populate the array later.
467 -- Probably do b) in conjunction with other denormalization
468
469 -- Performance Fix
470 --FOR pref_rec IN c_pref_codes LOOP
471 -- l_pref_trans(pref_rec.pref_definition_id) := pref_rec.code;
472 --END LOOP;
473
474 --Added By Mithun for CWK Terminate Bug
475 OPEN c_cwk_terminate_date (p_resource_id, p_evaluation_date);
476 FETCH c_cwk_terminate_date INTO cwk_final_process_date;
477 CLOSE c_cwk_terminate_date;
478
479 l_num_of_days_to_add := NVL(fnd_profile.value('HXC_CWK_TK_FPD'),0);
480
481 if g_debug then
482
483 HR_UTILITY.trace(g_package || ' cwk_final_process_date ' || cwk_final_process_date);
484 HR_UTILITY.trace(g_package || 'l_num_of_days_to_add ' || l_num_of_days_to_add);
485
486 end if;
487 --End of Addition By Mithun for CWK Terminate Bug
488
489 l_hier_count:=0;
490 -- Now find the hierarchies which the person is eligible for ...
491 -- note:
492 -- a) IF we cycle through the hierarchies in order of increasing precidence
493 -- - dont even need to do evaluation as we cycle. Sort is probably more
494 -- expensive though ...
495
496 -- basic eligibility, must be at least one
497
498 FOR hier_rec IN c_eligible_hierarchies_basic(p_resource_id, l_evaluation_date) LOOP
499 l_hierarchy_list(l_hier_count).rule_evaluation_order := hier_rec.rule_evaluation_order;
500 l_hierarchy_list(l_hier_count).pref_hierarchy_id := hier_rec.pref_hierarchy_id;
501 l_hier_count:=l_hier_count+1;
502 END LOOP;
503
504 BEGIN
505 FOR hier_rec IN c_eligible_hierarchies_rollup(p_resource_id, l_evaluation_date) LOOP
506 l_hierarchy_list(l_hier_count).rule_evaluation_order := hier_rec.rule_evaluation_order;
507 l_hierarchy_list(l_hier_count).pref_hierarchy_id := hier_rec.pref_hierarchy_id;
508 l_hier_count:=l_hier_count+1;
509 END LOOP;
510 EXCEPTION
511 WHEN NO_DATA_FOUND THEN null;
512 END;
513
514 -- Issue 6
515 IF(l_hierarchy_list.count = 0)
516 then
517 IF( g_raise_fatal_errors = TRUE) THEN
518 hr_utility.set_message(809, 'HXC_NO_HIER_FOR_DATE');
519 hr_utility.raise_error;
520 ELSE
521 g_fatal_error_occurred := TRUE;
522 g_fatal_error := 'HXC_NO_HIER_FOR_DATE';
523 RETURN;
524 END IF;
525 END IF;
526
527 -- more complex eligibility, zero or more
528
529 BEGIN
530 FOR hier_rec in c_eligible_hierarchies_flex(p_resource_id, l_evaluation_date) LOOP
531 l_hierarchy_list(l_hier_count).rule_evaluation_order := hier_rec.rule_evaluation_order;
532 l_hierarchy_list(l_hier_count).pref_hierarchy_id := hier_rec.pref_hierarchy_id;
533 l_hier_count := l_hier_count+1;
534
535 END LOOP;
536 EXCEPTION
537 WHEN NO_DATA_FOUND THEN null;
538 END;
539
540 --IF(fnd_global.resp_id <> -1) THEN
541 --IF(p_resp_id <> -1) then
542
543 -- Issue 3
544 /*If (p_user_id = -1) Then
545 -- we are defaulting the value
546 -- from the fnd_global value
547 l_employee_id := fnd_global.employee_id;
548 l_user_id := fnd_global.user_id;
549 Else*/
550
551 --End If;
552
553 -- Responsibility based hierarchies
554 -- ================================
555 -- If the resource himself is in session, you only look for session responsibility
556 -- Otherwise, you have to look into which timecard is in question to find out
557 -- which responsibility last touched the timecard.
558
559
560 If (l_resp_id <> -1 AND ( not p_ignore_resp_id)) Then
561
562 -- Do a session based resp evaluation only when the user himself is logged
563 -- in. But at this point consider even persistent responsibility. Note the
564 -- defn of the cursor that is opened here.
565
566 IF l_employee_id = p_resource_id
567 THEN
568 BEGIN
569 FOR hier_rec in c_resp_hierarchies(l_resp_id, l_evaluation_date) LOOP
570 l_hierarchy_list(l_hier_count).rule_evaluation_order := hier_rec.rule_evaluation_order;
571 l_hierarchy_list(l_hier_count).pref_hierarchy_id := hier_rec.pref_hierarchy_id;
572
573 l_hier_count := l_hier_count+1;
574 END LOOP;
575 EXCEPTION
576 WHEN NO_DATA_FOUND THEN null;
577 END;
578 END IF;
579
580 --Checking If we have to obtain the resp_id value stored in the timecard
581 IF l_find_resp_required
582 THEN
583 l_resp_id := get_tc_resp(p_resource_id, l_evaluation_date);
584
585 --l_resp_id = -101 here indicates that some other user (for eg TK) has modified this timecard
586 --and the current resp_id stored is the resp_id used by that user. So we should not use
587 --that resp_id for preference evalution. However we still have to do preference
588 --evaluation on persistent responsibility using resp id as fnd_global.resp_id
589 --if l_employee_id = p_resource_id
590 IF l_resp_id <> -101
591 THEN
592 BEGIN
593
594 FOR hier_rec IN c_perst_resp_hierarchies(l_resp_id, l_evaluation_date)
595 LOOP
596 l_hierarchy_list(l_hier_count).rule_evaluation_order := hier_rec.rule_evaluation_order;
597 l_hierarchy_list(l_hier_count).pref_hierarchy_id := hier_rec.pref_hierarchy_id;
598
599 l_hier_count := l_hier_count+1;
600 END LOOP;
601 EXCEPTION
602 WHEN NO_DATA_FOUND THEN null;
603 END;
604 END IF;
605 END IF;
606 End If;
607 --End of changes Done By Myth
608
609 -- personalisation, only one hierarchy possible
610 -- Issue 3 Added p_ignore_user_id
611
612 BEGIN
613 FOR hier_rec in c_person_type_hierarchies(p_resource_id, l_evaluation_date) LOOP
614 l_hierarchy_list(l_hier_count).rule_evaluation_order := hier_rec.rule_evaluation_order;
615 l_hierarchy_list(l_hier_count).pref_hierarchy_id := hier_rec.pref_hierarchy_id;
616 l_hier_count := l_hier_count+1;
617
618 END LOOP;
619 EXCEPTION
620 WHEN NO_DATA_FOUND THEN null;
621 END;
622
623 -----------------------------------------------------------------------------------------
624
625
626 if (( NOT p_ignore_user_id) AND l_employee_id = p_resource_id AND l_user_id <> -1) then
627 BEGIN
628 FOR hier_rec IN c_personal_hierarchy(l_user_id) LOOP
629 l_hierarchy_list(l_hier_count).rule_evaluation_order := hier_rec.rule_evaluation_order;
630 l_hierarchy_list(l_hier_count).pref_hierarchy_id := hier_rec.pref_hierarchy_id;
631 l_hier_count := l_hier_count+1;
632 END LOOP;
633 EXCEPTION
634 WHEN NO_DATA_FOUND THEN null;
635 END;
636 END if;
637
638 -- Issue 6
639 /*IF(l_hierarchy_list.count = 0)
640 then
641 IF( g_raise_fatal_errors = TRUE) THEN
642 hr_utility.set_message(809, 'HXC_NO_HIER_FOR_DATE');
643 hr_utility.raise_error;
644 ELSE
645 g_fatal_error_occurred := TRUE;
646 g_fatal_error := 'HXC_NO_HIER_FOR_DATE';
647 RETURN;
648 END IF;
649 END IF;*/
650
651 -- loop over the hierarchies we have found
652
653 l_hier := l_hierarchy_list.first;
654
655 l_hier := l_hierarchy_list.first;
656 g_loop_count := 0;
657
658 -- Bug 3297639.
659 -- Modified the logic for caching Preference values.
660 LOOP
661 EXIT WHEN NOT l_hierarchy_list.exists(l_hier);
662
663 --reset for each Pref Id
664 l_use_cache := FALSE;
665
666 -- Check if the required data is already cached.
667 If ( g_pref_hier_ct.exists(l_hierarchy_list(l_hier).pref_hierarchy_id) ) then
668
669 Open c_get_last_updated_date(l_hierarchy_list(l_hier).pref_hierarchy_id);
670 Fetch c_get_last_updated_date into l_last_updated_date;
671 Close c_get_last_updated_date;
672
673 -- checking if the cache data is outdated.
674 if ( g_pref_hier_ct(l_hierarchy_list(l_hier).pref_hierarchy_id).caching_time >= l_last_updated_date) then
675 l_use_cache := TRUE;
676 else
677 l_use_cache := FALSE;
678 -- Delete the Pref Values for this, since it has to be refreshed anyway.
679 g_pref_values_ct.delete(g_pref_hier_ct(l_hierarchy_list(l_hier).pref_hierarchy_id).Start_Index,g_pref_hier_ct(l_hierarchy_list(l_hier).pref_hierarchy_id).Stop_Index); -- table.delete(m,n)
680 end if;
681 end if;
682
683 -- If l_use_cache = FALSE, then populate/refresh cache data by db fetch.
684 If (not l_use_cache) then
685
686 -- initialise main table.
687 g_pref_hier_ct(l_hierarchy_list(l_hier).pref_hierarchy_id).caching_time := sysdate;
688 g_pref_hier_ct(l_hierarchy_list(l_hier).pref_hierarchy_id).Start_Index := -1;
689 g_pref_hier_ct(l_hierarchy_list(l_hier).pref_hierarchy_id).Stop_Index := -1;
690
691 -- Initialize Start_Index for the Pref Values
692 If (g_pref_values_ct.count > 0) then
693 l_pref_index := g_pref_values_ct.last + 1;
694 else
695 l_pref_index := 1;
696 End If;
697
698 g_pref_hier_ct(l_hierarchy_list(l_hier).pref_hierarchy_id).Start_Index := l_pref_index;
699
700 -- populate preference values into cache
701 FOR pref_node in c_pref_nodes(l_hierarchy_list(l_hier).pref_hierarchy_id)
702 LOOP
703
704 g_pref_values_ct(l_pref_index).pref_hierarchy_id := pref_node.pref_hierarchy_id;
705 g_pref_values_ct(l_pref_index).pref_definition_id := pref_node.preference_id;
706 g_pref_values_ct(l_pref_index).attribute1:= pref_node.attribute1;
707 g_pref_values_ct(l_pref_index).attribute2:= pref_node.attribute2;
708 g_pref_values_ct(l_pref_index).attribute3:= pref_node.attribute3;
709 g_pref_values_ct(l_pref_index).attribute4:= pref_node.attribute4;
710 g_pref_values_ct(l_pref_index).attribute5:= pref_node.attribute5;
711 g_pref_values_ct(l_pref_index).attribute6:= pref_node.attribute6;
712 g_pref_values_ct(l_pref_index).attribute7:= pref_node.attribute7;
713 g_pref_values_ct(l_pref_index).attribute8:= pref_node.attribute8;
714 g_pref_values_ct(l_pref_index).attribute9:= pref_node.attribute9;
715 g_pref_values_ct(l_pref_index).attribute10:= pref_node.attribute10;
716 g_pref_values_ct(l_pref_index).attribute11:= pref_node.attribute11;
717 g_pref_values_ct(l_pref_index).attribute12:= pref_node.attribute12;
718 g_pref_values_ct(l_pref_index).attribute13:= pref_node.attribute13;
719 g_pref_values_ct(l_pref_index).attribute14:= pref_node.attribute14;
720 g_pref_values_ct(l_pref_index).attribute15:= pref_node.attribute15;
721 g_pref_values_ct(l_pref_index).attribute16:= pref_node.attribute16;
722 g_pref_values_ct(l_pref_index).attribute17:= pref_node.attribute17;
723 g_pref_values_ct(l_pref_index).attribute18:= pref_node.attribute18;
724 g_pref_values_ct(l_pref_index).attribute19:= pref_node.attribute19;
725 g_pref_values_ct(l_pref_index).attribute20:= pref_node.attribute20;
726 g_pref_values_ct(l_pref_index).attribute21:= pref_node.attribute21;
727 g_pref_values_ct(l_pref_index).attribute22:= pref_node.attribute22;
728 g_pref_values_ct(l_pref_index).attribute23:= pref_node.attribute23;
729 g_pref_values_ct(l_pref_index).attribute24:= pref_node.attribute24;
730 g_pref_values_ct(l_pref_index).attribute25:= pref_node.attribute25;
731 g_pref_values_ct(l_pref_index).attribute26:= pref_node.attribute26;
732 g_pref_values_ct(l_pref_index).attribute27:= pref_node.attribute27;
733 g_pref_values_ct(l_pref_index).attribute28:= pref_node.attribute28;
734 g_pref_values_ct(l_pref_index).attribute29:= pref_node.attribute29;
735 g_pref_values_ct(l_pref_index).attribute30:= pref_node.attribute30;
736
737 g_pref_values_ct(l_pref_index).edit_allowed:= pref_node.edit_allowed;
738 g_pref_values_ct(l_pref_index).displayed:= pref_node.displayed;
739 g_pref_values_ct(l_pref_index).name:= pref_node.name;
740 g_pref_values_ct(l_pref_index).top_level_parent_id:= pref_node.top_level_parent_id;
741 g_pref_values_ct(l_pref_index).code:= pref_node.code;
742
743 l_pref_index := g_pref_values_ct.last + 1;
744 End Loop;
745
746 g_pref_hier_ct(l_hierarchy_list(l_hier).pref_hierarchy_id).Stop_Index := l_pref_index - 1;
747
748 -- check for valid start and stop index. Incase the only leaf node was deleted or no leaf nodes exists then reset start and stop index accordingly
749 if (g_pref_hier_ct(l_hierarchy_list(l_hier).pref_hierarchy_id).Stop_Index < g_pref_hier_ct(l_hierarchy_list(l_hier).pref_hierarchy_id).Start_Index) then
750 g_pref_hier_ct(l_hierarchy_list(l_hier).pref_hierarchy_id).Start_Index := 0;
751 g_pref_hier_ct(l_hierarchy_list(l_hier).pref_hierarchy_id).Stop_Index := 0;
752 end if;
753
754 l_use_cache := TRUE; -- now data is in cache
755 End If;
756
757 -- Now required data is in cache. Populate this required data to the main table after evaluation.
758 If (g_pref_hier_ct(l_hierarchy_list(l_hier).pref_hierarchy_id).Stop_Index <> 0 ) then --(case where parent node has no children)
759 For l_index in g_pref_hier_ct(l_hierarchy_list(l_hier).pref_hierarchy_id).Start_Index..g_pref_hier_ct(l_hierarchy_list(l_hier).pref_hierarchy_id).Stop_index Loop
760 Begin
761
762 l_preference_id := g_pref_values_ct(l_index).pref_definition_id;
763 IF( (l_hierarchy_list(l_hier).rule_evaluation_order >
764 p_pref_table(l_preference_id).rule_evaluation_order
765 AND p_pref_table(l_preference_id).rule_evaluation_order <>0)
766 OR (l_hierarchy_list(l_hier).rule_evaluation_order = 0
767 AND p_pref_table(l_preference_id).edit_allowed = 'Y') ) THEN
768
769 p_pref_table(l_preference_id).attribute1 := g_pref_values_ct(l_index).attribute1;
770 p_pref_table(l_preference_id).attribute2 := g_pref_values_ct(l_index).attribute2;
771 p_pref_table(l_preference_id).attribute3 := g_pref_values_ct(l_index).attribute3;
772 p_pref_table(l_preference_id).attribute4 := g_pref_values_ct(l_index).attribute4;
773 p_pref_table(l_preference_id).attribute5 := g_pref_values_ct(l_index).attribute5;
774 p_pref_table(l_preference_id).attribute6 := g_pref_values_ct(l_index).attribute6;
775 p_pref_table(l_preference_id).attribute7 := g_pref_values_ct(l_index).attribute7;
776 p_pref_table(l_preference_id).attribute8 := g_pref_values_ct(l_index).attribute8;
777 p_pref_table(l_preference_id).attribute9 := g_pref_values_ct(l_index).attribute9;
778 p_pref_table(l_preference_id).attribute10 := g_pref_values_ct(l_index).attribute10;
779 p_pref_table(l_preference_id).attribute11 := g_pref_values_ct(l_index).attribute11;
780 p_pref_table(l_preference_id).attribute12 := g_pref_values_ct(l_index).attribute12;
781 p_pref_table(l_preference_id).attribute13 := g_pref_values_ct(l_index).attribute13;
782 p_pref_table(l_preference_id).attribute14 := g_pref_values_ct(l_index).attribute14;
783 p_pref_table(l_preference_id).attribute15 := g_pref_values_ct(l_index).attribute15;
784 p_pref_table(l_preference_id).attribute16 := g_pref_values_ct(l_index).attribute16;
785 p_pref_table(l_preference_id).attribute17 := g_pref_values_ct(l_index).attribute17;
786 p_pref_table(l_preference_id).attribute18 := g_pref_values_ct(l_index).attribute18;
787 p_pref_table(l_preference_id).attribute19 := g_pref_values_ct(l_index).attribute19;
788 p_pref_table(l_preference_id).attribute20 := g_pref_values_ct(l_index).attribute20;
789 p_pref_table(l_preference_id).attribute21 := g_pref_values_ct(l_index).attribute21;
790 p_pref_table(l_preference_id).attribute22 := g_pref_values_ct(l_index).attribute22;
791 p_pref_table(l_preference_id).attribute23 := g_pref_values_ct(l_index).attribute23;
792 p_pref_table(l_preference_id).attribute24 := g_pref_values_ct(l_index).attribute24;
793 p_pref_table(l_preference_id).attribute25 := g_pref_values_ct(l_index).attribute25;
794 p_pref_table(l_preference_id).attribute26 := g_pref_values_ct(l_index).attribute26;
795 p_pref_table(l_preference_id).attribute27 := g_pref_values_ct(l_index).attribute27;
796 p_pref_table(l_preference_id).attribute28 := g_pref_values_ct(l_index).attribute28;
797 p_pref_table(l_preference_id).attribute29 := g_pref_values_ct(l_index).attribute29;
798 p_pref_table(l_preference_id).attribute30 := g_pref_values_ct(l_index).attribute30;
799
800 p_pref_table(l_preference_id).edit_allowed := g_pref_values_ct(l_index).edit_allowed;
801 p_pref_table(l_preference_id).displayed := g_pref_values_ct(l_index).displayed;
802 p_pref_table(l_preference_id).rule_evaluation_order
803 := l_hierarchy_list(l_hier).rule_evaluation_order;
804 p_pref_table(l_preference_id).name := g_pref_values_ct(l_index).name;
805
806 END IF;
807 EXCEPTION
808 WHEN NO_DATA_FOUND THEN
809 -- exception happens when there is no existing row with preference_code.
810 -- ie first row of this preference_code
811
812 p_pref_table(l_preference_id).preference_code := g_pref_values_ct(l_index).code;--Performace Fix l_pref_trans(l_preference_id);
813 p_pref_table(l_preference_id).attribute1 := g_pref_values_ct(l_index).attribute1;
814 p_pref_table(l_preference_id).attribute2 := g_pref_values_ct(l_index).attribute2;
815 p_pref_table(l_preference_id).attribute3 := g_pref_values_ct(l_index).attribute3;
816 p_pref_table(l_preference_id).attribute4 := g_pref_values_ct(l_index).attribute4;
817 p_pref_table(l_preference_id).attribute5 := g_pref_values_ct(l_index).attribute5;
818 p_pref_table(l_preference_id).attribute6 := g_pref_values_ct(l_index).attribute6;
819 p_pref_table(l_preference_id).attribute7 := g_pref_values_ct(l_index).attribute7;
820 p_pref_table(l_preference_id).attribute8 := g_pref_values_ct(l_index).attribute8;
821 p_pref_table(l_preference_id).attribute9 := g_pref_values_ct(l_index).attribute9;
822 p_pref_table(l_preference_id).attribute10 := g_pref_values_ct(l_index).attribute10;
823 p_pref_table(l_preference_id).attribute11 := g_pref_values_ct(l_index).attribute11;
824 p_pref_table(l_preference_id).attribute12 := g_pref_values_ct(l_index).attribute12;
825 p_pref_table(l_preference_id).attribute13 := g_pref_values_ct(l_index).attribute13;
826 p_pref_table(l_preference_id).attribute14 := g_pref_values_ct(l_index).attribute14;
827 p_pref_table(l_preference_id).attribute15 := g_pref_values_ct(l_index).attribute15;
828 p_pref_table(l_preference_id).attribute16 := g_pref_values_ct(l_index).attribute16;
829 p_pref_table(l_preference_id).attribute17 := g_pref_values_ct(l_index).attribute17;
830 p_pref_table(l_preference_id).attribute18 := g_pref_values_ct(l_index).attribute18;
831 p_pref_table(l_preference_id).attribute19 := g_pref_values_ct(l_index).attribute19;
832 p_pref_table(l_preference_id).attribute20 := g_pref_values_ct(l_index).attribute20;
833 p_pref_table(l_preference_id).attribute21 := g_pref_values_ct(l_index).attribute21;
834 p_pref_table(l_preference_id).attribute22 := g_pref_values_ct(l_index).attribute22;
835 p_pref_table(l_preference_id).attribute23 := g_pref_values_ct(l_index).attribute23;
836 p_pref_table(l_preference_id).attribute24 := g_pref_values_ct(l_index).attribute24;
837 p_pref_table(l_preference_id).attribute25 := g_pref_values_ct(l_index).attribute25;
838 p_pref_table(l_preference_id).attribute26 := g_pref_values_ct(l_index).attribute26;
839 p_pref_table(l_preference_id).attribute27 := g_pref_values_ct(l_index).attribute27;
840 p_pref_table(l_preference_id).attribute28 := g_pref_values_ct(l_index).attribute28;
841 p_pref_table(l_preference_id).attribute29 := g_pref_values_ct(l_index).attribute29;
842 p_pref_table(l_preference_id).attribute30 := g_pref_values_ct(l_index).attribute30;
843
844 p_pref_table(l_preference_id).edit_allowed := g_pref_values_ct(l_index).edit_allowed;
845 p_pref_table(l_preference_id).displayed := g_pref_values_ct(l_index).displayed;
846 p_pref_table(l_preference_id).rule_evaluation_order
847 := l_hierarchy_list(l_hier).rule_evaluation_order;
848 p_pref_table(l_preference_id).name := g_pref_values_ct(l_index).name;
849 END;
850 End loop;
851 End if;
852
853 l_hier := l_hierarchy_list.next(l_hier);
854
855 --g_loop_count := g_loop_count + 1;
856
857 IF(g_loop_count > g_maxloop) THEN
858 hr_utility.set_message(809, 'HXC_LPS_GT_MAX');
859 hr_utility.raise_error;
860 END IF;
861
862 END LOOP;
863
864 END resource_preferences;
865
866
867
868 ---------
869 -- Procedure takes a string preference mask and parses it into a table preference mask
870 ---------
871
872 PROCEDURE string_to_table_mask(p_string_mask IN VARCHAR2,
873 p_table_mask OUT NOCOPY t_requested_pref_list)
874 IS
875
876 l_pos NUMBER;
877 l_start NUMBER;
878 l_start_attr NUMBER;
879 l_end NUMBER;
880 l_req NUMBER;
881
882 BEGIN
883
884 l_start := 1;
885 l_pos := l_start;
886 l_req := 1;
887
888 g_loop_count := 0;
889
890 LOOP
891
892 l_pos := instr(p_string_mask,',',l_start,1);
893
894 IF l_pos = 0 THEN
895 l_end := length(p_string_mask)+1;
896 ELSE
897 l_end := l_pos;
898 END IF;
899
900 -- l_start and l_end now bound the description for a single preference code
901 -- such as 'TC_W_TCARD_DISPLAY_DAYS|1|2|'
902
903 l_start_attr := instr(p_string_mask,g_input_separator,l_start,1);
904
905 IF l_start_attr = 0 THEN
906 l_start_attr := l_end;
907 END IF;
908
909 p_table_mask(l_req).code := substr(p_string_mask,l_start,l_start_attr-l_start);
910 p_table_mask(l_req).attr_list := substr(p_string_mask,l_start_attr+1,l_end-l_start_attr);
911
912 l_start := l_end + 1;
913
914 l_req := l_req + 1;
915 EXIT WHEN l_pos = 0;
916
917 --g_loop_count := g_loop_count + 1;
918
919 IF(g_loop_count > g_maxloop) THEN
920 hr_utility.set_message(809, 'HXC_LPS_GT_MAX');
921 hr_utility.raise_error;
922 END IF;
923
924
925 END LOOP;
926
927 END string_to_table_mask;
928
929 ---------
930 -- Function to prepare a list of preference values from a preference table and
931 -- a table preference mask
932 ---------
933
934 FUNCTION string_from_mask_and_prefs(p_pref_table IN t_pref_table,
935 p_table_mask IN t_requested_pref_list,
936 p_output_separator IN varchar2)
937 RETURN VARCHAR2
938 IS
939 p_pref_value_list VARCHAR2(1000);
940 l_single_pref_str VARCHAR2(1000);
941 l_pref_table t_pref_table;
942 l_pref_start NUMBER;
943 l_first_attr NUMBER;
944 l_next_pipe NUMBER;
945 l_next_pref NUMBER;
946 l_attr NUMBER;
947 l_pref NUMBER;
948 l_req NUMBER;
949 l_found BOOLEAN;
950 p_val_to_append VARCHAR2(150);
951
952 BEGIN
953
954 l_req := p_table_mask.first;
955 p_pref_value_list := null;
956
957 g_loop_count := 0;
958 LOOP
959 EXIT WHEN NOT p_table_mask.exists(l_req);
960 l_pref := p_pref_table.first;
961 g_loop_count := 0;
962 l_found:=FALSE;
963
964 LOOP
965 EXIT WHEN NOT p_pref_table.exists(l_pref);
966
967 IF( p_table_mask(l_req).code=p_pref_table(l_pref).preference_code ) THEN
968
969 l_found:=TRUE;
970 -- go through requested attribute list
971
972 l_first_attr := 1;
973 g_loop_count := 0;
974 LOOP
975 EXIT WHEN instr(p_table_mask(l_req).attr_list,g_input_separator,l_first_attr,1) = 0 ;
976 l_next_pipe := instr(p_table_mask(l_req).attr_list,g_input_separator,l_first_attr,1);
977 l_attr := substr(p_table_mask(l_req).attr_list,l_first_attr,l_next_pipe-l_first_attr);
978
979 IF (l_attr=1) THEN p_val_to_append := p_pref_table(l_pref).attribute1;
980 ELSIF(l_attr=2) THEN p_val_to_append := p_pref_table(l_pref).attribute2;
981 ELSIF(l_attr=3) THEN p_val_to_append := p_pref_table(l_pref).attribute3;
982 ELSIF(l_attr=4) THEN p_val_to_append := p_pref_table(l_pref).attribute4;
983 ELSIF(l_attr=5) THEN p_val_to_append := p_pref_table(l_pref).attribute5;
984 ELSIF(l_attr=6) THEN p_val_to_append := p_pref_table(l_pref).attribute6;
985 ELSIF(l_attr=7) THEN p_val_to_append := p_pref_table(l_pref).attribute7;
986 ELSIF(l_attr=8) THEN p_val_to_append := p_pref_table(l_pref).attribute8;
987 ELSIF(l_attr=9) THEN p_val_to_append := p_pref_table(l_pref).attribute9;
988 ELSIF(l_attr=10) THEN p_val_to_append := p_pref_table(l_pref).attribute10;
989 ELSIF(l_attr=11) THEN p_val_to_append := p_pref_table(l_pref).attribute11;
990 ELSIF(l_attr=12) THEN p_val_to_append := p_pref_table(l_pref).attribute12;
991 ELSIF(l_attr=13) THEN p_val_to_append := p_pref_table(l_pref).attribute13;
992 ELSIF(l_attr=14) THEN p_val_to_append := p_pref_table(l_pref).attribute14;
993 ELSIF(l_attr=15) THEN p_val_to_append := p_pref_table(l_pref).attribute15;
994 ELSIF(l_attr=16) THEN p_val_to_append := p_pref_table(l_pref).attribute16;
995 ELSIF(l_attr=17) THEN p_val_to_append := p_pref_table(l_pref).attribute17;
996 ELSIF(l_attr=18) THEN p_val_to_append := p_pref_table(l_pref).attribute18;
997 ELSIF(l_attr=19) THEN p_val_to_append := p_pref_table(l_pref).attribute19;
998 ELSIF(l_attr=20) THEN p_val_to_append := p_pref_table(l_pref).attribute20;
999 ELSIF(l_attr=21) THEN p_val_to_append := p_pref_table(l_pref).attribute21;
1000 ELSIF(l_attr=22) THEN p_val_to_append := p_pref_table(l_pref).attribute22;
1001 ELSIF(l_attr=23) THEN p_val_to_append := p_pref_table(l_pref).attribute23;
1002 ELSIF(l_attr=24) THEN p_val_to_append := p_pref_table(l_pref).attribute24;
1003 ELSIF(l_attr=25) THEN p_val_to_append := p_pref_table(l_pref).attribute25;
1004 ELSIF(l_attr=26) THEN p_val_to_append := p_pref_table(l_pref).attribute26;
1005 ELSIF(l_attr=27) THEN p_val_to_append := p_pref_table(l_pref).attribute27;
1006 ELSIF(l_attr=28) THEN p_val_to_append := p_pref_table(l_pref).attribute28;
1007 ELSIF(l_attr=29) THEN p_val_to_append := p_pref_table(l_pref).attribute29;
1008 ELSIF(l_attr=30) THEN p_val_to_append := p_pref_table(l_pref).attribute30;
1009 END IF;
1010 -- Return 'null' when there is no value. This makes the string easier to parse
1011 -- using standard Java class
1012
1013 IF (p_val_to_append IS null) THEN
1014 p_val_to_append:='null';
1015 END IF;
1016
1017 p_pref_value_list :=p_pref_value_list||p_val_to_append||p_output_separator;
1018
1019 l_first_attr := l_next_pipe+1;
1020
1021 -- debug, to be removed
1022 -- g_loop_count := g_loop_count + 1;
1023
1024 IF(g_loop_count > g_maxloop) THEN
1025 hr_utility.set_message(809, 'HXC_LPS_GT_MAX');
1026 hr_utility.raise_error;
1027 END IF;
1028 -- debug, to be removed
1029
1030 END LOOP;
1031 END IF;
1032 l_pref := p_pref_table.next(l_pref);
1033
1034 -- g_loop_count := g_loop_count + 1;
1035
1036 IF(g_loop_count > g_maxloop) THEN
1037 hr_utility.set_message(809, 'HXC_LPS_GT_MAX');
1038 hr_utility.raise_error;
1039 END IF;
1040
1041 END LOOP;
1042
1043 IF(l_found = FALSE) then
1044 -- Raise an error as the requested preference was not found for the user.
1045 -- This should never happen as all supported preferences MUST occur in the
1046 -- default preference tree (even if the value is left blank)
1047 IF( g_raise_fatal_errors = TRUE) THEN
1048 hr_utility.set_message(809, 'HXC_NO_VALS_FOR_PREF_CODE');
1049 hr_utility.raise_error;
1050 ELSE
1051 g_fatal_error_occurred := TRUE;
1052 g_fatal_error := 'HXC_NO_VALS_FOR_PREF_CODE';
1053 RETURN null;
1054 END IF;
1055 END IF;
1056
1057 l_req := p_table_mask.next(l_req);
1058
1059 -- g_loop_count := g_loop_count + 1;
1060
1061 IF(g_loop_count > g_maxloop) THEN
1062 hr_utility.set_message(809, 'HXC_LPS_GT_MAX');
1063 hr_utility.raise_error;
1064 END IF;
1065
1066 END LOOP;
1067 RETURN substr(p_pref_value_list,1,length(p_pref_value_list)-1);
1068 END;
1069
1070
1071 ---------
1072 -- PROCEDURE to restrict and order a preference table according to a preference table
1073 -- and preference mask
1074 ---------
1075
1076 PROCEDURE trim_order_prefs(p_pref_table in out nocopy t_pref_table,
1077 p_table_mask in t_requested_pref_list)
1078 is
1079 l_pref_table t_pref_table;
1080 l_mask NUMBER;
1081 l_pref NUMBER;
1082
1083 BEGIN
1084
1085 -- loop through the table mask
1086
1087 l_mask := p_table_mask.first;
1088
1089 g_loop_count := 0;
1090
1091 LOOP
1092 EXIT WHEN NOT p_table_mask.exists(l_mask) ;
1093
1094 -- loop through pref table
1095
1096 l_pref := p_pref_table.first;
1097
1098 g_loop_count := 0;
1099 LOOP
1100 EXIT WHEN NOT p_pref_table.exists(l_pref);
1101
1102 IF( p_table_mask(l_mask).code = p_pref_table(l_pref).preference_code )THEN
1103 l_pref_table(l_pref) := p_pref_table(l_pref);
1104 END IF;
1105
1106 l_pref := p_pref_table.next(l_pref);
1107
1108 -- debug, to be removed
1109 -- g_loop_count := g_loop_count + 1;
1110
1111 IF(g_loop_count > g_maxloop) THEN
1112 hr_utility.set_message(809, 'HXC_LPS_GT_MAX');
1113 hr_utility.raise_error;
1114 END IF;
1115 -- debug, to be removed
1116
1117 END LOOP;
1118
1119 l_mask := p_table_mask.next(l_mask);
1120
1121 -- debug, to be removed
1122 -- g_loop_count := g_loop_count + 1;
1123
1124 IF(g_loop_count > g_maxloop) THEN
1125 hr_utility.set_message(809, 'HXC_LPS_GT_MAX');
1126 hr_utility.raise_error;
1127 END IF;
1128 -- debug, to be removed
1129 END LOOP;
1130 p_pref_table := l_pref_table;
1131
1132 END trim_order_prefs;
1133
1134
1135 ---------
1136 -- FUNCTION useful where results need to be in string format
1137 ---------
1138
1139 FUNCTION resource_pref_sep(p_resource_id IN NUMBER ,
1140 p_pref_spec_list IN VARCHAR2,
1141 p_output_separator IN varchar2,
1142 p_evaluation_date IN DATE DEFAULT sysdate)
1143 RETURN VARCHAR2 IS
1144
1145
1146 l_pref_value_list VARCHAR2(1000) :='';
1147 l_pref_table t_pref_table;
1148
1149 l_requested_list t_requested_pref_list;
1150
1151
1152 BEGIN
1153
1154 -- 1. Find out which preferences are being requested
1155 string_to_table_mask(p_pref_spec_list,l_requested_list);
1156
1157 -- 2. get the preference for the resource
1158 resource_preferences(p_resource_id,l_pref_table, p_evaluation_date);
1159
1160 IF(g_fatal_error_occurred = FALSE) THEN
1161 -- 3. Organise the string to RETURN
1162 l_pref_value_list := string_from_mask_and_prefs(l_pref_table,l_requested_list,p_output_separator);
1163 END IF;
1164
1165 RETURN l_pref_value_list;
1166
1167
1168 END resource_pref_sep;
1169
1170 ---------
1171 -- FUNCTION useful where caller wants any fatal error to be reported but not raised
1172 ---------
1173
1174
1175
1176 FUNCTION resource_pref_errcode(p_resource_id IN NUMBER,
1177 p_pref_spec_list IN VARCHAR2,
1178 p_message IN OUT NOCOPY VARCHAR,
1179 p_evaluation_date IN DATE DEFAULT sysdate) RETURN VARCHAR2
1180 IS
1181 l_pref_spec_list varchar2(2000);
1182 BEGIN
1183
1184 -- set global variable that controls how fatal errors are handled
1185 g_raise_fatal_errors := FALSE;
1186 g_fatal_error_occurred := FALSE;
1187 g_fatal_error :='';
1188
1189 -- call base method
1190 l_pref_spec_list := resource_preferences(p_resource_id =>p_resource_id,
1191 p_pref_spec_list => p_pref_spec_list,
1192 p_evaluation_date =>p_evaluation_date);
1193
1194 -- copy any error to p_message, and reset fatal error vars
1195 p_message := g_fatal_error;
1196 g_fatal_error_occurred := FALSE;
1197 g_fatal_error :='';
1198
1199 -- set global variable back
1200 g_raise_fatal_errors := TRUE;
1201
1202 RETURN l_pref_spec_list;
1203
1204 END resource_pref_errcode;
1205
1206 ---------
1207 -- FUNCTION useful where results need to be in string format. As above but defaults output separator to '|'
1208 ---------
1209 FUNCTION resource_preferences(p_resource_id IN NUMBER ,
1210 p_pref_spec_list IN VARCHAR2,
1211 p_evaluation_date IN DATE DEFAULT sysdate)
1212 RETURN VARCHAR2 IS
1213
1214 BEGIN
1215
1216 return resource_pref_sep(p_resource_id => p_resource_id ,
1217 p_pref_spec_list => p_pref_spec_list,
1218 p_output_separator => '|',
1219 p_evaluation_date => p_evaluation_date);
1220
1221
1222 END resource_preferences;
1223
1224
1225
1226
1227 -------
1228 -- PROCEDURE useful when a limited NUMBER of preferences are needed
1229 -------
1230
1231 PROCEDURE resource_preferences(p_resource_id IN NUMBER,
1232 p_pref_code_list IN VARCHAR2,
1233 p_pref_table IN OUT NOCOPY t_pref_table,
1234 p_evaluation_date IN DATE DEFAULT sysdate,
1235 p_resp_id IN NUMBER DEFAULT -99)
1236 IS
1237 l_table_mask t_requested_pref_list;
1238 BEGIN
1239
1240 -- Note that this currently only filters and orders the full preference table for a
1241 -- resource. There is currently no performance saving from using this procedure. Will be
1242 -- updating this procedure to improve performance further for this case
1243
1244 -- 1. Get full set of prefs
1245 --resource_preferences(p_resource_id, p_pref_table, p_evaluation_date);
1246 --Changed By Mithun for Persistent Responsibility Enhancement
1247 resource_preferences(p_resource_id, p_pref_table, p_evaluation_date,FND_GLOBAL.user_id, p_resp_id);
1248
1249 -- 2. Turn the string into a table mask.
1250 string_to_table_mask(p_pref_code_list,l_table_mask);
1251
1252 -- 3. filter the results to return only those requested
1253 trim_order_prefs(p_pref_table,l_table_mask);
1254
1255 END;
1256
1257
1258 ------
1259 -- Function useful in case where only one attribute of a given preference is required
1260 ------
1261
1262 FUNCTION resource_preferences(p_resource_id IN NUMBER,
1263 p_pref_code IN VARCHAR2,
1264 p_attribute_n IN NUMBER,
1265 p_evaluation_date IN DATE DEFAULT sysdate,
1266 p_resp_id IN number default -99)
1267 RETURN VARCHAR2 IS
1268 l_pref_table t_pref_table;
1269 l_table_mask t_requested_pref_list;
1270 l_first NUMBER;
1271 BEGIN
1272
1273 -- Note that this currently only filters and orders the full preference table for a
1274 -- resource. There is currently no performance saving from using this procedure. Will
1275 -- be updating this procedure to improve performance further for this case
1276
1277 -- 1. Turn the string into a table mask
1278 string_to_table_mask(p_pref_code,l_table_mask);
1279
1280 -- 2. Get full set of prefs
1281 --Changed By Mithun for persistent responsibility enhancement
1282 resource_preferences(p_resource_id,l_pref_table, p_evaluation_date,fnd_global.user_id,p_resp_id);
1283
1284 -- 3. Filter for the pref we want
1285 trim_order_prefs(l_pref_table,l_table_mask);
1286
1287 l_first := l_pref_table.first;
1288
1289 IF(p_attribute_n = 1) THEN RETURN l_pref_table(l_first).attribute1;
1290 ELSIF(p_attribute_n = 2) THEN RETURN l_pref_table(l_first).attribute2;
1291 ELSIF(p_attribute_n = 3) THEN RETURN l_pref_table(l_first).attribute3;
1292 ELSIF(p_attribute_n = 4) THEN RETURN l_pref_table(l_first).attribute4;
1293 ELSIF(p_attribute_n = 5) THEN RETURN l_pref_table(l_first).attribute5;
1294 ELSIF(p_attribute_n = 6) THEN RETURN l_pref_table(l_first).attribute6;
1295 ELSIF(p_attribute_n = 7) THEN RETURN l_pref_table(l_first).attribute7;
1296 ELSIF(p_attribute_n = 8) THEN RETURN l_pref_table(l_first).attribute8;
1297 ELSIF(p_attribute_n = 9) THEN RETURN l_pref_table(l_first).attribute9;
1298 ELSIF(p_attribute_n = 10) THEN RETURN l_pref_table(l_first).attribute10;
1299 ELSIF(p_attribute_n = 11) THEN RETURN l_pref_table(l_first).attribute11;
1300 ELSIF(p_attribute_n = 12) THEN RETURN l_pref_table(l_first).attribute12;
1301 ELSIF(p_attribute_n = 13) THEN RETURN l_pref_table(l_first).attribute13;
1302 ELSIF(p_attribute_n = 14) THEN RETURN l_pref_table(l_first).attribute14;
1303 ELSIF(p_attribute_n = 15) THEN RETURN l_pref_table(l_first).attribute15;
1304 ELSIF(p_attribute_n = 16) THEN RETURN l_pref_table(l_first).attribute16;
1305 ELSIF(p_attribute_n = 17) THEN RETURN l_pref_table(l_first).attribute17;
1306 ELSIF(p_attribute_n = 18) THEN RETURN l_pref_table(l_first).attribute18;
1307 ELSIF(p_attribute_n = 19) THEN RETURN l_pref_table(l_first).attribute19;
1308 ELSIF(p_attribute_n = 20) THEN RETURN l_pref_table(l_first).attribute20;
1309 ELSIF(p_attribute_n = 21) THEN RETURN l_pref_table(l_first).attribute21;
1310 ELSIF(p_attribute_n = 22) THEN RETURN l_pref_table(l_first).attribute22;
1311 ELSIF(p_attribute_n = 23) THEN RETURN l_pref_table(l_first).attribute23;
1312 ELSIF(p_attribute_n = 24) THEN RETURN l_pref_table(l_first).attribute24;
1313 ELSIF(p_attribute_n = 25) THEN RETURN l_pref_table(l_first).attribute25;
1314 ELSIF(p_attribute_n = 26) THEN RETURN l_pref_table(l_first).attribute26;
1315 ELSIF(p_attribute_n = 27) THEN RETURN l_pref_table(l_first).attribute27;
1316 ELSIF(p_attribute_n = 28) THEN RETURN l_pref_table(l_first).attribute28;
1317 ELSIF(p_attribute_n = 29) THEN RETURN l_pref_table(l_first).attribute29;
1318 ELSIF(p_attribute_n = 30) THEN RETURN l_pref_table(l_first).attribute30;
1319 END IF;
1320
1321 END resource_preferences;
1322 ------
1323 -- Function populates the global preference array which can then be read by
1324 -- get_resource_preferences
1325 ------
1326
1327 PROCEDURE set_resource_preferences(p_resource_id IN NUMBER,
1328 p_evaluation_date IN DATE DEFAULT sysdate )
1329 IS
1330 BEGIN
1331
1332 g_pref_table.delete;
1333 resource_preferences(p_resource_id => p_resource_id,
1334 p_pref_table => g_pref_table,
1335 p_evaluation_date => p_evaluation_date);
1336
1337 END set_resource_preferences;
1338
1339
1340 ------
1341 -- Function populates the global preference array which can then be read by
1342 -- get_resource_preferences
1343 ------
1344
1345 PROCEDURE set_resource_preferences(p_resource_id IN NUMBER,
1346 p_start_evaluation_date IN DATE,
1347 p_end_evaluation_date IN DATE )
1348 IS
1349 BEGIN
1350
1351 g_pref_table.delete;
1352 resource_preferences(p_resource_id => p_resource_id,
1353 p_pref_table => g_pref_table,
1354 p_start_evaluation_date => p_start_evaluation_date,
1355 p_end_evaluation_date => p_start_evaluation_date);
1356
1357 END set_resource_preferences;
1358
1359 ------
1360 -- Function gets resource preferences from global tables
1361 ------
1362
1363 FUNCTION get_resource_preferences(p_resource_id IN NUMBER,
1364 p_pref_id IN NUMBER,
1365 p_attn IN VARCHAR2) RETURN VARCHAR2
1366 IS
1367 BEGIN
1368
1369 IF(p_attn = '1') then return g_pref_table(p_pref_id).attribute1;
1370 elsif(p_attn = '2') then return g_pref_table(p_pref_id).attribute2;
1371 elsif(p_attn = '3') then return g_pref_table(p_pref_id).attribute3;
1372 elsif(p_attn = '4') then return g_pref_table(p_pref_id).attribute4;
1373 elsif(p_attn = '5') then return g_pref_table(p_pref_id).attribute5;
1374 elsif(p_attn = '6') then return g_pref_table(p_pref_id).attribute6;
1375 elsif(p_attn = '7') then return g_pref_table(p_pref_id).attribute7;
1376 elsif(p_attn = '8') then return g_pref_table(p_pref_id).attribute8;
1377 elsif(p_attn = '9') then return g_pref_table(p_pref_id).attribute9;
1378 elsif(p_attn = '10') then return g_pref_table(p_pref_id).attribute10;
1379 elsif(p_attn = '11') then return g_pref_table(p_pref_id).attribute11;
1380 elsif(p_attn = '12') then return g_pref_table(p_pref_id).attribute12;
1381 elsif(p_attn = '13') then return g_pref_table(p_pref_id).attribute13;
1382 elsif(p_attn = '14') then return g_pref_table(p_pref_id).attribute14;
1383 elsif(p_attn = '15') then return g_pref_table(p_pref_id).attribute15;
1384 elsif(p_attn = '16') then return g_pref_table(p_pref_id).attribute16;
1385 elsif(p_attn = '17') then return g_pref_table(p_pref_id).attribute17;
1386 elsif(p_attn = '18') then return g_pref_table(p_pref_id).attribute18;
1387 elsif(p_attn = '19') then return g_pref_table(p_pref_id).attribute19;
1388 elsif(p_attn = '20') then return g_pref_table(p_pref_id).attribute20;
1389 elsif(p_attn = '21') then return g_pref_table(p_pref_id).attribute21;
1390 elsif(p_attn = '22') then return g_pref_table(p_pref_id).attribute22;
1391 elsif(p_attn = '23') then return g_pref_table(p_pref_id).attribute23;
1392 elsif(p_attn = '24') then return g_pref_table(p_pref_id).attribute24;
1393 elsif(p_attn = '25') then return g_pref_table(p_pref_id).attribute25;
1394 elsif(p_attn = '26') then return g_pref_table(p_pref_id).attribute26;
1395 elsif(p_attn = '27') then return g_pref_table(p_pref_id).attribute27;
1396 elsif(p_attn = '28') then return g_pref_table(p_pref_id).attribute28;
1397 elsif(p_attn = '29') then return g_pref_table(p_pref_id).attribute29;
1398 elsif(p_attn = '30') then return g_pref_table(p_pref_id).attribute30;
1399 elsif(p_attn = 'E') then return g_pref_table(p_pref_id).edit_allowed;
1400 elsif(p_attn = 'D') then return g_pref_table(p_pref_id).displayed;
1401 END IF;
1402
1403 END get_resource_preferences;
1404
1405 ----
1406 -- Procedure to return date range prefrences - complex evaluation
1407 ----
1408
1409 PROCEDURE resource_preferences(p_resource_id in NUMBER,
1410 p_start_evaluation_date DATE,
1411 p_end_evaluation_date DATE,
1412 p_pref_table IN OUT NOCOPY t_pref_table,
1413 p_no_prefs_outside_asg IN BOOLEAN DEFAULT FALSE,
1414 p_resp_id IN number default -99,
1415 p_resp_appl_id IN NUMBER DEFAULT fnd_global.resp_appl_id,
1416 p_ignore_resp_id in boolean default false)
1417 IS
1418
1419 l_req NUMBER;
1420 l_personal_hierarchy NUMBER;
1421 l_hier NUMBER;
1422 l_requested_list t_requested_pref_list;
1423 l_pref_trans t_pref_trans;
1424 l_hierarchy_list t_hierarchy_list;
1425
1426 l_pref_table t_pref_table; --Performance Fix
1427 l_dated_prefs t_dated_prefs;
1428
1429 l_pref_count NUMBER;
1430 l_pref_ref NUMBER;
1431 l_dated_pref_count NUMBER;
1432 l_index NUMBER;
1433 l_finish NUMBER;
1434 l_start_evaluation_date DATE;
1435 l_end_evaluation_date DATE;
1436 l_hier_count NUMBER;
1437 l_tmp_date DATE;
1438
1439 -- Bug 3297639
1440 l_pref_index number;
1441 l_preference_id number;
1442 l_last_updated_date date;
1443 l_use_cache boolean := FALSE;
1444
1445 CURSOR c_get_last_updated_date(p_pref_hierarchy_id IN number) IS
1446 Select last_update_date
1447 From hxc_pref_hierarchies
1448 Where pref_hierarchy_id = p_pref_hierarchy_id;
1449
1450 --Performance Fix
1451 TYPE t_pref_encountered IS TABLE OF
1452 number
1453 INDEX BY BINARY_INTEGER;
1454 l_pref_encountered t_pref_encountered;
1455
1456 -- CURSORs to find the hierarchies a resource is eligible for ...
1457 -- To support new criteria add new CURSORs or add to existing CURSOR.
1458
1459 -- Modified cursor to support CWK.
1460
1461 -- Bug 7484448
1462 -- Added USE_NL in the below query for optimum perf.
1463 CURSOR c_eligible_hierarchies_basic(p_resource_id IN NUMBER,
1464 p_start_evaluation_date IN DATE,
1465 p_end_evaluation_date IN DATE) IS
1466
1467 SELECT /*+ USE_NL(PA HRR) */
1468 hrr.pref_hierarchy_id,
1469 hrr.rule_evaluation_order,
1470 hrr.start_date,
1471 hrr.end_date,
1472 decode(hrr.eligibility_criteria_type,
1473 'ALL_PEOPLE', hr_general.start_of_time
1474 , pa.effective_start_date) elig_start_date,
1475 decode(hrr.eligibility_criteria_type,
1476 'ALL_PEOPLE', hr_general.end_of_time
1477 , pa.effective_END_date) elig_end_date
1478 FROM hxc_resource_rules hrr,
1479 per_all_assignments_f pa
1480 WHERE pa.person_id = p_resource_id
1481 AND nvl(hrr.business_group_id,pa.business_group_id) = pa.business_group_id
1482 AND pa.primary_flag = 'Y'
1483 and pa.assignment_type in ('E','C')
1484 AND p_start_evaluation_date <= pa.effective_END_date
1485 and pa.effective_start_date <= p_end_evaluation_date
1486 and p_start_evaluation_date <= hrr.end_date
1487 and hrr.start_date <= p_end_evaluation_date
1488 and hrr.start_date <= pa.effective_end_date
1489 and hrr.end_date >=pa.effective_start_date
1490 AND (( to_char(pa.assignment_id) = hrr.eligibility_criteria_id
1491 AND hrr.eligibility_criteria_type = 'ASSIGNMENT')
1492 OR ( to_char(pa.payroll_id) = hrr.eligibility_criteria_id
1493 AND hrr.eligibility_criteria_type = 'PAYROLL')
1494 OR ( to_char(pa.person_id) = hrr.eligibility_criteria_id
1495 AND hrr.eligibility_criteria_type = 'PERSON')
1496 OR ( to_char(pa.location_id) = hrr.eligibility_criteria_id
1497 AND hrr.eligibility_criteria_type = 'LOCATION')
1498 OR ( pa.EMPLOYEE_CATEGORY = hrr.eligibility_criteria_id
1499 AND hrr.eligibility_criteria_type = 'EMP_CATEGORY')
1500 OR ( pa.EMPLOYMENT_CATEGORY = hrr.eligibility_criteria_id
1501 AND hrr.eligibility_criteria_type = 'ASGN_CATEGORY')
1502 OR ( to_char(pa.organization_id) = hrr.eligibility_criteria_id
1503 AND hrr.eligibility_criteria_type = 'ORGANIZATION')
1504 OR ((HXC_PREFERENCE_EVALUATION.check_number(substr(hrr.eligibility_criteria_id,instr(hrr.eligibility_criteria_id,'-',1,1)+1)) in
1505 (SELECT pose.organization_id_parent
1506 FROM
1507 per_org_structure_elements pose
1508 start with organization_id_child = pa.organization_id
1509 and pose.org_structure_version_id=
1510 HXC_PREFERENCE_EVALUATION.return_version_id(HXC_PREFERENCE_EVALUATION.check_number(substr(hrr.eligibility_criteria_id,1,instr(hrr.eligibility_criteria_id,'-',1,1)-1)),
1511 hrr.eligibility_criteria_type)
1512 connect by prior organization_id_parent=organization_id_child
1513 and pose.org_structure_version_id=
1514 HXC_PREFERENCE_EVALUATION.return_version_id(HXC_PREFERENCE_EVALUATION.check_number(substr(hrr.eligibility_criteria_id,1,instr(hrr.eligibility_criteria_id,'-',1,1)-1)),
1515 hrr.eligibility_criteria_type)
1516 union
1517 select organization_id
1518 from hr_all_organization_units
1519 where organization_id = pa.organization_id))
1520 AND hrr.eligibility_criteria_type = 'ROLLUP' )
1521 OR (
1522 hrr.eligibility_criteria_type = 'ALL_PEOPLE'));
1523
1524
1525 -- for rehired employees with preference breaks
1526 -- Bug 7484448
1527 -- Added USE_NL in the below query for optimum perf.
1528 CURSOR c_rehire_elig_hier_basic(p_resource_id IN NUMBER,
1529 p_start_evaluation_date IN DATE,
1530 p_end_evaluation_date IN DATE) IS
1531 SELECT /*+ USE_NL(PA HRR) */
1532 hrr.pref_hierarchy_id,
1533 hrr.rule_evaluation_order,
1534 hrr.start_date,
1535 hrr.end_date,
1536 pa.effective_start_date elig_start_date,
1537 pa.effective_END_date elig_end_date
1538 FROM hxc_resource_rules hrr,
1539 per_all_assignments_f pa
1540 WHERE pa.person_id = p_resource_id
1541 AND nvl(hrr.business_group_id,pa.business_group_id) = pa.business_group_id
1542 AND pa.primary_flag = 'Y'
1543 and pa.assignment_type in ('E','C')
1544 AND p_start_evaluation_date <= pa.effective_END_date
1545 and pa.effective_start_date <= p_end_evaluation_date
1546 and p_start_evaluation_date <= hrr.end_date
1547 and hrr.start_date <= p_end_evaluation_date
1548 and hrr.start_date <= pa.effective_end_date
1549 and hrr.end_date >=pa.effective_start_date
1550 AND (( to_char(pa.assignment_id) = hrr.eligibility_criteria_id
1551 AND hrr.eligibility_criteria_type = 'ASSIGNMENT')
1552 OR ( to_char(pa.payroll_id) = hrr.eligibility_criteria_id
1553 AND hrr.eligibility_criteria_type = 'PAYROLL')
1554 OR ( to_char(pa.person_id) = hrr.eligibility_criteria_id
1555 AND hrr.eligibility_criteria_type = 'PERSON')
1556 OR ( to_char(pa.location_id) = hrr.eligibility_criteria_id
1557 AND hrr.eligibility_criteria_type = 'LOCATION')
1558 OR ( pa.EMPLOYEE_CATEGORY = hrr.eligibility_criteria_id
1559 AND hrr.eligibility_criteria_type = 'EMP_CATEGORY')
1560 OR ( pa.EMPLOYMENT_CATEGORY = hrr.eligibility_criteria_id
1561 AND hrr.eligibility_criteria_type = 'ASGN_CATEGORY')
1562 OR ( to_char(pa.organization_id) = hrr.eligibility_criteria_id
1563 AND hrr.eligibility_criteria_type = 'ORGANIZATION')
1564 OR ((HXC_PREFERENCE_EVALUATION.check_number(substr(hrr.eligibility_criteria_id,instr(hrr.eligibility_criteria_id,'-',1,1)+1)) in
1565 (SELECT pose.organization_id_parent
1566 FROM
1567 per_org_structure_elements pose
1568 start with organization_id_child = pa.organization_id
1569 and pose.org_structure_version_id=
1570 HXC_PREFERENCE_EVALUATION.return_version_id(HXC_PREFERENCE_EVALUATION.check_number(substr(hrr.eligibility_criteria_id,1,instr(hrr.eligibility_criteria_id,'-',1,1)-1)),
1571 hrr.eligibility_criteria_type)
1572 connect by prior organization_id_parent=organization_id_child
1573 and pose.org_structure_version_id=
1574 HXC_PREFERENCE_EVALUATION.return_version_id(HXC_PREFERENCE_EVALUATION.check_number(substr(hrr.eligibility_criteria_id,1,instr(hrr.eligibility_criteria_id,'-',1,1)-1)),
1575 hrr.eligibility_criteria_type)
1576 union
1577 select organization_id
1578 from hr_all_organization_units
1579 where organization_id = pa.organization_id))
1580 AND hrr.eligibility_criteria_type = 'ROLLUP' )
1581 OR (
1582 hrr.eligibility_criteria_type = 'ALL_PEOPLE'));
1583
1584 -- Modified cursor to support CWK.
1585 CURSOR c_eligible_hierarchies_flex(p_resource_id IN NUMBER,
1586 p_start_evaluation_date IN DATE,
1587 p_end_evaluation_date IN DATE) IS
1588
1589 SELECT hrr.pref_hierarchy_id,
1590 hrr.rule_evaluation_order,
1591 hrr.start_date, hrr.end_date,
1592 pa.effective_start_date elig_start_date,
1593 pa.effective_END_date elig_end_date
1594 FROM hxc_resource_rules hrr,
1595 per_all_assignments_f pa
1596 WHERE pa.person_id = p_resource_id
1597 AND nvl(hrr.business_group_id,pa.business_group_id) = pa.business_group_id
1598 AND pa.primary_flag = 'Y'
1599 and pa.assignment_type in ('E','C')
1600 AND p_start_evaluation_date <= pa.effective_END_date
1601 and pa.effective_start_date <= p_end_evaluation_date
1602 and p_start_evaluation_date <= hrr.end_date
1603 and hrr.start_date <= p_end_evaluation_date
1604 and hrr.start_date <= pa.effective_end_date
1605 and hrr.end_date >=pa.effective_start_date
1606 AND ( (DECODE ( SUBSTR( hrr.eligibility_criteria_type, 1, 3 ),
1607 'SCL', DECODE ( pa.soft_coding_keyflex_id, NULL, -1,
1608 hxc_resource_rules_utils.chk_flex_valid ( 'SCL', pa.soft_coding_keyflex_id,
1609 SUBSTR( hrr.eligibility_criteria_type, 5 ), hrr.eligibility_criteria_id )), -1 ) = 1 )
1610 OR
1611 (DECODE ( SUBSTR( hrr.eligibility_criteria_type, 1, 6 ),
1612 'PEOPLE', DECODE ( pa.people_group_id, NULL, -1,
1613 hxc_resource_rules_utils.chk_flex_valid ( 'PEOPLE', pa.people_group_id,
1614 SUBSTR( hrr.eligibility_criteria_type, 8 ), hrr.eligibility_criteria_id )), -1 ) = 1 )
1615 OR
1616 (DECODE ( SUBSTR( hrr.eligibility_criteria_type, 1, 5 ),
1617 'GRADE', DECODE ( pa.grade_id, NULL, -1,
1618 hxc_resource_rules_utils.chk_flex_valid ( 'GRADE', pa.grade_id,
1619 SUBSTR( hrr.eligibility_criteria_type, 7 ), hrr.eligibility_criteria_id )), -1 ) = 1 )
1620 OR
1621
1622 (DECODE ( SUBSTR( hrr.eligibility_criteria_type, 1, 3 ),
1623 'JOB', hrr.eligibility_criteria_id, -1 ) = to_char(pa.job_id)) -- Issue 4
1624
1625 OR
1626
1627 (DECODE ( SUBSTR( hrr.eligibility_criteria_type, 1, 8 ),
1628 'POSITION', hrr.eligibility_criteria_id, -1 ) = to_char(pa.position_id)) -- Issue 4
1629 );
1630
1631 -- Cursor to check whether the resource has personalized preferences.
1632 -- Note that this CURSOR could be removed in order to possible boost performance.
1633 -- Need to alter c_eligible_hierarchies CURSOR to include LOGIN type. We would THEN need
1634 -- to use a DECODE in the SELECT to give LOGIN type rules higest priority.
1635 -- Decode expression would need to include the EDIT_ALLOWED flag.
1636 -- But, keep it simple for now.
1637
1638
1639
1640 CURSOR c_personal_hierarchy(p_resource_id IN NUMBER) IS
1641
1642 SELECT hrr.pref_hierarchy_id , hrr.rule_evaluation_order, hrr.start_date, hrr.end_date
1643 FROM hxc_resource_rules hrr,
1644 fnd_user fu
1645 WHERE hrr.resource_type='PERSON'
1646 AND hrr.eligibility_criteria_type = 'LOGIN'
1647 AND hrr.eligibility_criteria_id = to_char(fu.user_id) -- Issue 4
1648 AND fu.employee_id = p_resource_id;
1649
1650 -- Cursor to pick up any responsibility based prefs
1651
1652 CURSOR c_resp_hierarchies(p_responsibility_id IN NUMBER,
1653 p_resp_appl_id IN NUMBER,
1654 p_start_evaluation_date IN DATE,
1655 p_end_evaluation_date IN DATE) IS
1656 SELECT hrr.pref_hierarchy_id , hrr.rule_evaluation_order, hrr.start_date, hrr.end_date,
1657 fr.start_date elig_start_date,
1658 fr.end_date elig_end_date
1659 FROM hxc_resource_rules hrr,
1660 fnd_responsibility fr
1661 WHERE hrr.resource_type='PERSON'
1662 and p_start_evaluation_date <= hrr.end_date
1663 and hrr.start_date <= p_end_evaluation_date
1664 AND hrr.eligibility_criteria_type IN ( 'RESPONSIBILITY',
1665 'PERST_RESPONSIBILITY' )
1666 AND hrr.eligibility_criteria_id = to_char(p_responsibility_id) -- Issue 4
1667 and fr.responsibility_id = p_responsibility_id
1668 and fr.application_id= p_resp_appl_id
1669 and fr.responsibility_id = hrr.eligibility_criteria_id;
1670
1671
1672
1673 CURSOR c_perst_resp_hierarchies(p_responsibility_id NUMBER,
1674 p_start_evaluation_date DATE,
1675 p_end_evaluation_date DATE)
1676 IS
1677 SELECT hrr.pref_hierarchy_id ,
1678 hrr.rule_evaluation_order,
1679 hrr.start_date,
1680 hrr.end_date,
1681 p_start_evaluation_date elig_start_date,
1682 p_end_evaluation_date elig_end_date
1683 FROM hxc_resource_rules hrr
1684 WHERE hrr.resource_type='PERSON'
1685 AND p_start_evaluation_date BETWEEN hrr.start_date
1686 AND hrr.end_date
1687 AND p_end_evaluation_date BETWEEN hrr.start_date
1688 AND hrr.end_date
1689 AND hrr.eligibility_criteria_type = 'PERST_RESPONSIBILITY'
1690 AND hrr.eligibility_criteria_id = to_char(p_responsibility_id) ;
1691
1692
1693
1694
1695
1696 CURSOR get_employee_id(p_user_id IN Number) Is
1697 Select employee_id from fnd_user
1698 Where user_id = p_user_id;
1699
1700
1701 CURSOR c_person_type_hierarchies(p_resource_id IN NUMBER,
1702 p_start_evaluation_date IN DATE,
1703 p_end_evaluation_date IN DATE) IS
1704 SELECT hrr.pref_hierarchy_id , hrr.rule_evaluation_order, hrr.start_date, hrr.end_date,
1705 ptu.effective_start_date elig_start_date,
1706 ptu.effective_end_date elig_end_date
1707 FROM hxc_resource_rules hrr,
1708 per_person_types typ,
1709 per_person_type_usages_f ptu
1710 WHERE hrr.resource_type='PERSON'
1711 and p_start_evaluation_date <= hrr.end_date
1712 and hrr.start_date <= p_end_evaluation_date
1713 AND hrr.eligibility_criteria_type = 'PERSON_TYPE'
1714 AND hrr.eligibility_criteria_id = to_char(ptu.person_type_id) -- Issue 4
1715 AND ptu.person_id = p_resource_id
1716 AND typ.system_person_type IN ('EMP','EX_EMP','EMP_APL','EX_EMP_APL','CWK','EX_CWK')
1717 AND typ.person_type_id = ptu.person_type_id
1718 AND p_start_evaluation_date <= ptu.effective_end_date
1719 AND ptu.effective_start_date <= p_end_evaluation_date;
1720 --------------------------------------------------------------------------
1721
1722 -- Cursor to RETURN the preference nodes in a hierarchy. Note that this is based on a
1723 -- hierchical query.
1724 -- This can be removed if necessary by denormalization onto the hxc_pref_hierarchies base table
1725 -- About 30% of preference evaluatuation time is spent in this query. Denormalization seems
1726 -- to be appropriate at some point.
1727
1728 CURSOR c_pref_nodes(p_hierarchy_id IN NUMBER)
1729 IS
1730 SELECT pref_hierarchy_id
1731 ,pref_definition_id preference_id
1732 ,attribute1
1733 ,attribute2
1734 ,attribute3
1735 ,attribute4
1736 ,attribute5
1737 ,attribute6
1738 ,attribute7
1739 ,attribute8
1740 ,attribute9
1741 ,attribute10
1742 ,attribute11
1743 ,attribute12
1744 ,attribute13
1745 ,attribute14
1746 ,attribute15
1747 ,attribute16
1748 ,attribute17
1749 ,attribute18
1750 ,attribute19
1751 ,attribute20
1752 ,attribute21
1753 ,attribute22
1754 ,attribute23
1755 ,attribute24
1756 ,attribute25
1757 ,attribute26
1758 ,attribute27
1759 ,attribute28
1760 ,attribute29
1761 ,attribute30
1762 ,edit_allowed
1763 ,displayed
1764 ,name
1765 ,top_level_parent_id --Performance Fix
1766 ,code
1767 FROM hxc_pref_hierarchies
1768 WHERE top_level_parent_id = p_hierarchy_id;
1769 --pref_definition_id is not null
1770 --START WITH pref_hierarchy_id = p_hierarchy_id
1771 --CONNECT BY prior pref_hierarchy_id = parent_pref_hierarchy_id;
1772
1773 CURSOR c_pref_codes
1774 IS
1775 SELECT
1776 pref_definition_id, code
1777 FROM hxc_pref_definitions;
1778
1779 --VARIABLES ADDED by Mithun for Perst Resp enhancement
1780 l_find_resp_required BOOLEAN DEFAULT FALSE;
1781 l_resp_id NUMBER;
1782 l_resp_appl_id NUMBER;
1783 l_employee_id NUMBER;
1784 --l_tc_employee_id NUMBER;
1785
1786 l_resptab resplisttab;
1787
1788
1789 BEGIN
1790
1791 l_resp_id := p_resp_id;
1792
1793
1794 g_debug := hr_utility.debug_enabled;
1795
1796 IF g_debug
1797 THEN
1798 hr_utility.trace('Evaluating pref for start and stop dates');
1799 hr_utility.trace('p_resource_id '||p_resource_id);
1800 hr_utility.trace('p_start_evaluation_date '||p_start_evaluation_date);
1801 hr_utility.trace('p_end_evaluation_date '||p_end_evaluation_date);
1802 END IF;
1803
1804
1805
1806 Open get_employee_id(fnd_global.user_id);
1807 Fetch get_employee_id into l_employee_id;
1808 Close get_employee_id;
1809
1810 IF ( p_resp_id = -99 ) AND (l_employee_id <> p_resource_id)
1811 THEN
1812 -- By default we need to consider persistent responsibility also,
1813 -- so set the flag to TRUE.
1814 l_find_resp_required := TRUE;
1815
1816 ELSE
1817 -- The call is by passing -101 explicitly, meaning we are not considering
1818 -- persistent responsibility. Get the session responsibility and keep it.
1819 -- Set the flag to FALSE.
1820 l_find_resp_required := FALSE;
1821
1822 END IF;
1823
1824 l_resp_id := fnd_global.resp_id;
1825
1826 -- make sure pref table is empty - otherwise this will interfere with the evaluation
1827 p_pref_table.delete;
1828
1829 l_hier_count:=1;
1830
1831 l_start_evaluation_date:=trunc(p_start_evaluation_date);
1832 l_end_evaluation_date :=trunc(p_end_evaluation_date);
1833
1834 l_dated_pref_count:=0;
1835 l_pref_count:=0;
1836
1837
1838
1839
1840 -- populate table that will allow us to find a pref_code given an pref_definition_id
1841 -- Performance Fix (Loop Commented).
1842 /*FOR pref_rec IN c_pref_codes LOOP
1843 l_pref_trans(pref_rec.pref_definition_id) := pref_rec.code;
1844
1845 -- set a dummy default preference for each preference code (start and ends at evaluation dates)
1846 -- These will be overriden by default preferences
1847
1848 l_dated_prefs(l_dated_pref_count).code := pref_rec.code;
1849 l_dated_prefs(l_dated_pref_count).start_date := l_start_evaluation_date;
1850 l_dated_prefs(l_dated_pref_count).end_date := l_end_evaluation_date;
1851 l_dated_prefs(l_dated_pref_count).rule_evaluation_order := -1;
1852 l_dated_prefs(l_dated_pref_count).pref_ref := 1;
1853 l_dated_pref_count := l_dated_pref_count+1;
1854
1855 END LOOP;*/
1856
1857 -- Now find the hierarchies which the person is eligible for ...
1858
1859 -- basic eligibility, must be at least one
1860
1861 if p_no_prefs_outside_asg = TRUE then
1862
1863 FOR hier_rec IN c_rehire_elig_hier_basic(p_resource_id,
1864 l_start_evaluation_date,
1865 l_end_evaluation_date) LOOP
1866 -- Performance Fix
1867 if(hier_rec.rule_evaluation_order = 1) then
1868 l_hierarchy_list(0).pref_hierarchy_id := hier_rec.pref_hierarchy_id;
1869 l_hierarchy_list(0).rule_evaluation_order := hier_rec.rule_evaluation_order;
1870 l_hierarchy_list(0).start_date := hier_rec.start_date;
1871 l_hierarchy_list(0).end_date := hier_rec.end_date;
1872 l_hierarchy_list(0).elig_start_date := hier_rec.elig_start_date;
1873 l_hierarchy_list(0).elig_end_date := hier_rec.elig_end_date;
1874
1875 else
1876 l_hierarchy_list(l_hier_count).pref_hierarchy_id := hier_rec.pref_hierarchy_id;
1877 l_hierarchy_list(l_hier_count).rule_evaluation_order := hier_rec.rule_evaluation_order;
1878 l_hierarchy_list(l_hier_count).start_date := hier_rec.start_date;
1879 l_hierarchy_list(l_hier_count).end_date := hier_rec.end_date;
1880 l_hierarchy_list(l_hier_count).elig_start_date := hier_rec.elig_start_date;
1881 l_hierarchy_list(l_hier_count).elig_end_date := hier_rec.elig_end_date;
1882 l_hier_count := l_hier_count + 1;
1883 end if;
1884
1885 END LOOP;
1886
1887 ELSE
1888
1889 FOR hier_rec IN c_eligible_hierarchies_basic(p_resource_id,
1890 l_start_evaluation_date,
1891 l_end_evaluation_date) LOOP
1892 -- Performance Fix
1893 if(hier_rec.rule_evaluation_order = 1) then
1894 l_hierarchy_list(0).pref_hierarchy_id := hier_rec.pref_hierarchy_id;
1895 l_hierarchy_list(0).rule_evaluation_order := hier_rec.rule_evaluation_order;
1896 l_hierarchy_list(0).start_date := hier_rec.start_date;
1897 l_hierarchy_list(0).end_date := hier_rec.end_date;
1898 l_hierarchy_list(0).elig_start_date := hier_rec.elig_start_date;
1899 l_hierarchy_list(0).elig_end_date := hier_rec.elig_end_date;
1900
1901 else
1902 l_hierarchy_list(l_hier_count).pref_hierarchy_id := hier_rec.pref_hierarchy_id;
1903 l_hierarchy_list(l_hier_count).rule_evaluation_order := hier_rec.rule_evaluation_order;
1904 l_hierarchy_list(l_hier_count).start_date := hier_rec.start_date;
1905 l_hierarchy_list(l_hier_count).end_date := hier_rec.end_date;
1906 l_hierarchy_list(l_hier_count).elig_start_date := hier_rec.elig_start_date;
1907 l_hierarchy_list(l_hier_count).elig_end_date := hier_rec.elig_end_date;
1908 l_hier_count := l_hier_count + 1;
1909 end if;
1910
1911 END LOOP;
1912 END IF;
1913
1914 -- Issue 6
1915 if(l_hierarchy_list(0).pref_hierarchy_id is null)
1916 then
1917 IF( g_raise_fatal_errors = TRUE) THEN
1918 hr_utility.set_message(809, 'HXC_NO_HIER_FOR_DATE');
1919 hr_utility.raise_error;
1920 ELSE
1921 g_fatal_error := 'HXC_NO_HIER_FOR_DATE';
1922 g_fatal_error_occurred := TRUE;
1923 RETURN;
1924 END IF;
1925 end if;
1926
1927 -- more complex eligibility, zero or more
1928
1929 BEGIN
1930 FOR hier_rec in c_eligible_hierarchies_flex(p_resource_id,
1931 l_start_evaluation_date,
1932 l_end_evaluation_date) LOOP
1933 l_hierarchy_list(l_hier_count).pref_hierarchy_id := hier_rec.pref_hierarchy_id;
1934 l_hierarchy_list(l_hier_count).rule_evaluation_order := hier_rec.rule_evaluation_order;
1935 l_hierarchy_list(l_hier_count).start_date := hier_rec.start_date;
1936 l_hierarchy_list(l_hier_count).end_date := hier_rec.end_date;
1937 l_hierarchy_list(l_hier_count).elig_start_date := hier_rec.elig_start_date;
1938 l_hierarchy_list(l_hier_count).elig_end_date := hier_rec.elig_end_date;
1939 l_hier_count := l_hier_count + 1;
1940 END LOOP;
1941 EXCEPTION
1942 WHEN NO_DATA_FOUND THEN null;
1943 END;
1944
1945
1946
1947 -------------------------4215885-- ----------------------
1948 BEGIN
1949 FOR hier_rec in c_person_type_hierarchies(p_resource_id,
1950 l_start_evaluation_date,
1951 l_end_evaluation_date) LOOP
1952 l_hierarchy_list(l_hier_count).pref_hierarchy_id := hier_rec.pref_hierarchy_id;
1953 l_hierarchy_list(l_hier_count).rule_evaluation_order := hier_rec.rule_evaluation_order;
1954 l_hierarchy_list(l_hier_count).start_date := hier_rec.start_date;
1955 l_hierarchy_list(l_hier_count).end_date := hier_rec.end_date;
1956 l_hierarchy_list(l_hier_count).elig_start_date := hier_rec.elig_start_date;
1957 l_hierarchy_list(l_hier_count).elig_end_date := hier_rec.elig_end_date;
1958 l_hier_count := l_hier_count + 1;
1959 END LOOP;
1960 EXCEPTION
1961 WHEN NO_DATA_FOUND THEN null;
1962 END;
1963
1964 -------------------------------------------------------------------
1965 --IF(fnd_global.resp_id <> -1) THEN
1966
1967 --Changes done by Mithun for Persistent Responsibility Enhancement
1968
1969 If (l_resp_id <> -1 AND ( not p_ignore_resp_id)) Then
1970
1971 --This is Where we are evaluating for session responsibility.
1972 --We check for l_employee_id = p_resource_id to make sure that employee himself has logged in
1973 IF l_employee_id = p_resource_id THEN
1974
1975 BEGIN
1976 FOR hier_rec in c_resp_hierarchies(l_resp_id, p_resp_appl_id, l_start_evaluation_date, l_end_evaluation_date) LOOP
1977
1978 l_hierarchy_list(l_hier_count).pref_hierarchy_id := hier_rec.pref_hierarchy_id;
1979 l_hierarchy_list(l_hier_count).rule_evaluation_order := hier_rec.rule_evaluation_order;
1980 l_hierarchy_list(l_hier_count).start_date := hier_rec.start_date;
1981 l_hierarchy_list(l_hier_count).end_date := hier_rec.end_date;
1982 l_hierarchy_list(l_hier_count).elig_start_date := hier_rec.elig_start_date;
1983 l_hierarchy_list(l_hier_count).elig_end_date := hier_rec.elig_end_date;
1984
1985 l_hier_count := l_hier_count+1;
1986 END LOOP;
1987 EXCEPTION
1988 WHEN NO_DATA_FOUND THEN null;
1989 END;
1990 END IF;
1991
1992 --Checking If we have to obtain the resp_id value stored in the timecard
1993 -- If we have to then get the responsibilities and calculate the hierarchies
1994 -- associated.
1995 --
1996 IF l_find_resp_required
1997 THEN
1998 -- We need Persistent responsibility, so get the valid responsibilities
1999 -- within the given date range.
2000
2001 get_tc_resp(p_resource_id,
2002 l_start_evaluation_date,
2003 l_end_evaluation_date,
2004 l_resptab );
2005
2006
2007 -- If you got any timecards in the given date range, do the
2008 -- following.
2009
2010 IF l_resptab.COUNT > 0
2011 THEN
2012 -- Do the following for all timecards.
2013 FOR i IN l_resptab.FIRST..l_resptab.LAST
2014 LOOP
2015
2016 -- The following condition means that this timecard
2017 -- was updated by another person, not the employee
2018 -- himself, meaning we dont have to consider persistent
2019 -- responsibilities at all.
2020
2021 IF l_resptab(i).resp_id <> -1
2022 THEN
2023
2024 BEGIN
2025 -- Run the cursor for all the responsibilities and
2026 -- timecards and keep recording the hierarchy rules.
2027
2028 FOR hier_rec IN c_perst_resp_hierarchies(l_resptab(i).resp_id,
2029 TRUNC(l_resptab(i).start_date),
2030 TRUNC(l_resptab(i).stop_date))
2031 LOOP
2032 l_hierarchy_list(l_hier_count).pref_hierarchy_id := hier_rec.pref_hierarchy_id;
2033 l_hierarchy_list(l_hier_count).rule_evaluation_order := hier_rec.rule_evaluation_order;
2034 l_hierarchy_list(l_hier_count).start_date := hier_rec.start_date;
2035 l_hierarchy_list(l_hier_count).end_date := hier_rec.end_date;
2036 l_hierarchy_list(l_hier_count).elig_start_date := hier_rec.elig_start_date;
2037 l_hierarchy_list(l_hier_count).elig_end_date := hier_rec.elig_end_date;
2038
2039 l_hier_count := l_hier_count+1;
2040 END LOOP;
2041 EXCEPTION
2042 WHEN NO_DATA_FOUND THEN null;
2043 END ; -- BEGIN
2044 END IF; -- IF l_resptab(i).resp_id <> -1
2045 END LOOP; -- FOR i IN l_resptab.FIRST..l_resptab.LAST
2046
2047 END IF; -- IF l_resptab.COUNT > 0
2048
2049 END IF; -- IF l_find_resp_required
2050
2051 End If;
2052 -- personalisation, only one hierarchy possible. Note we give a start and end date
2053 -- spanning all time. Personalizations are only applied over ranges of time in which the
2054 -- granted preference has the edit_allowed flag. Note the the personalisation MUST
2055 -- be the last hierarchy to be considered. Only when all the other hierarchies have
2056 -- been considered can which ranges of time have the edit allowed flag
2057
2058 BEGIN
2059 FOR hier_rec IN c_personal_hierarchy(p_resource_id) LOOP
2060 l_hierarchy_list(l_hier_count).pref_hierarchy_id := hier_rec.pref_hierarchy_id;
2061 l_hierarchy_list(l_hier_count).rule_evaluation_order := hier_rec.rule_evaluation_order;
2062 l_hierarchy_list(l_hier_count).start_date := hr_general.start_of_time;
2063 l_hierarchy_list(l_hier_count).end_date := hr_general.end_of_time;
2064 l_hierarchy_list(l_hier_count).elig_start_date := hr_general.start_of_time;
2065 l_hierarchy_list(l_hier_count).elig_end_date := hr_general.end_of_time;
2066 l_hier_count := l_hier_count+1;
2067 END LOOP;
2068 EXCEPTION
2069 WHEN NO_DATA_FOUND THEN null;
2070 END;
2071
2072 -- sanity check
2073 --Issue 6
2074 /*if(l_hierarchy_list.count=0)
2075 then
2076 IF( g_raise_fatal_errors = TRUE) THEN
2077 hr_utility.set_message(809, 'HXC_NO_HIER_FOR_DATE');
2078 hr_utility.raise_error;
2079 ELSE
2080 g_fatal_error := 'HXC_NO_HIER_FOR_DATE';
2081 g_fatal_error_occurred := TRUE;
2082 RETURN;
2083 END IF;
2084 end if;*/
2085
2086 -- Performance Fix.
2087 l_dated_pref_count :=0;
2088
2089 -- Bug 3297639
2090 -- if data not in cache then fetch from db and populate the cache.
2091 If ( not g_pref_hier_ct.exists(l_hierarchy_list(0).pref_hierarchy_id) ) then
2092
2093 g_pref_hier_ct(l_hierarchy_list(0).pref_hierarchy_id).caching_time := sysdate;
2094 g_pref_hier_ct(l_hierarchy_list(0).pref_hierarchy_id).Start_Index := -1;
2095 g_pref_hier_ct(l_hierarchy_list(0).pref_hierarchy_id).Stop_Index := -1;
2096
2097
2098 l_pref_index := 1;
2099 If (g_pref_values_ct.count > 0) then
2100 l_pref_index := g_pref_values_ct.last + 1;
2101 end if;
2102
2103 g_pref_hier_ct(l_hierarchy_list(0).pref_hierarchy_id).Start_Index := l_pref_index;
2104
2105
2106 FOR pref_node in c_pref_nodes(l_hierarchy_list(0).pref_hierarchy_id)
2107 LOOP
2108 g_pref_values_ct(l_pref_index).pref_hierarchy_id := pref_node.pref_hierarchy_id;
2109 g_pref_values_ct(l_pref_index).pref_definition_id := pref_node.preference_id;
2110 g_pref_values_ct(l_pref_index).attribute1:= pref_node.attribute1;
2111 g_pref_values_ct(l_pref_index).attribute2:= pref_node.attribute2;
2112 g_pref_values_ct(l_pref_index).attribute3:= pref_node.attribute3;
2113 g_pref_values_ct(l_pref_index).attribute4:= pref_node.attribute4;
2114 g_pref_values_ct(l_pref_index).attribute5:= pref_node.attribute5;
2115 g_pref_values_ct(l_pref_index).attribute6:= pref_node.attribute6;
2116 g_pref_values_ct(l_pref_index).attribute7:= pref_node.attribute7;
2117 g_pref_values_ct(l_pref_index).attribute8:= pref_node.attribute8;
2118 g_pref_values_ct(l_pref_index).attribute9:= pref_node.attribute9;
2119 g_pref_values_ct(l_pref_index).attribute10:= pref_node.attribute10;
2120 g_pref_values_ct(l_pref_index).attribute11:= pref_node.attribute11;
2121 g_pref_values_ct(l_pref_index).attribute12:= pref_node.attribute12;
2122 g_pref_values_ct(l_pref_index).attribute13:= pref_node.attribute13;
2123 g_pref_values_ct(l_pref_index).attribute14:= pref_node.attribute14;
2124 g_pref_values_ct(l_pref_index).attribute15:= pref_node.attribute15;
2125 g_pref_values_ct(l_pref_index).attribute16:= pref_node.attribute16;
2126 g_pref_values_ct(l_pref_index).attribute17:= pref_node.attribute17;
2127 g_pref_values_ct(l_pref_index).attribute18:= pref_node.attribute18;
2128 g_pref_values_ct(l_pref_index).attribute19:= pref_node.attribute19;
2129 g_pref_values_ct(l_pref_index).attribute20:= pref_node.attribute20;
2130 g_pref_values_ct(l_pref_index).attribute21:= pref_node.attribute21;
2131 g_pref_values_ct(l_pref_index).attribute22:= pref_node.attribute22;
2132 g_pref_values_ct(l_pref_index).attribute23:= pref_node.attribute23;
2133 g_pref_values_ct(l_pref_index).attribute24:= pref_node.attribute24;
2134 g_pref_values_ct(l_pref_index).attribute25:= pref_node.attribute25;
2135 g_pref_values_ct(l_pref_index).attribute26:= pref_node.attribute26;
2136 g_pref_values_ct(l_pref_index).attribute27:= pref_node.attribute27;
2137 g_pref_values_ct(l_pref_index).attribute28:= pref_node.attribute28;
2138 g_pref_values_ct(l_pref_index).attribute29:= pref_node.attribute29;
2139 g_pref_values_ct(l_pref_index).attribute30:= pref_node.attribute30;
2140 g_pref_values_ct(l_pref_index).edit_allowed:= pref_node.edit_allowed;
2141 g_pref_values_ct(l_pref_index).displayed:= pref_node.displayed;
2142 g_pref_values_ct(l_pref_index).name:= pref_node.name;
2143 g_pref_values_ct(l_pref_index).top_level_parent_id:= pref_node.top_level_parent_id;
2144 g_pref_values_ct(l_pref_index).code:= pref_node.code;
2145 l_pref_index := g_pref_values_ct.last + 1;
2146
2147 End Loop;
2148
2149
2150 g_pref_hier_ct(l_hierarchy_list(0).pref_hierarchy_id).Stop_Index := l_pref_index - 1;
2151
2152 -- check for valid start and stop index. Incase the only leaf node was deleted or no leaf nodes exists then reset start and stop index accordingly
2153 if (g_pref_hier_ct(l_hierarchy_list(0).pref_hierarchy_id).Stop_Index < g_pref_hier_ct(l_hierarchy_list(0).pref_hierarchy_id).Start_Index) then
2154 g_pref_hier_ct(l_hierarchy_list(0).pref_hierarchy_id).Start_Index := 0;
2155 g_pref_hier_ct(l_hierarchy_list(0).pref_hierarchy_id).Stop_Index := 0;
2156 end if;
2157
2158 end if;
2159
2160 -- Not checking if cache info for this pref id is outdated since its seed data.
2161 For l_loop_index in g_pref_hier_ct(l_hierarchy_list(0).pref_hierarchy_id).Start_Index..g_pref_hier_ct(l_hierarchy_list(0).pref_hierarchy_id).Stop_index
2162 Loop
2163 l_dated_prefs(l_dated_pref_count).code := g_pref_values_ct(l_loop_index).code;
2164 l_dated_prefs(l_dated_pref_count).start_date := l_start_evaluation_date;
2165 l_dated_prefs(l_dated_pref_count).end_date := l_end_evaluation_date;
2166 l_dated_prefs(l_dated_pref_count).rule_evaluation_order := -1;
2167 l_dated_prefs(l_dated_pref_count).pref_ref := 1;
2168 l_dated_pref_count := l_dated_pref_count+1;
2169 end loop;
2170
2171 /* FOR pref_node in c_pref_nodes(l_hierarchy_list(0).pref_hierarchy_id) LOOP
2172
2173 l_dated_prefs(l_dated_pref_count).code := pref_node.code;
2174 l_dated_prefs(l_dated_pref_count).start_date := l_start_evaluation_date;
2175 l_dated_prefs(l_dated_pref_count).end_date := l_end_evaluation_date;
2176 l_dated_prefs(l_dated_pref_count).rule_evaluation_order := -1;
2177 l_dated_prefs(l_dated_pref_count).pref_ref := 1;
2178 l_dated_pref_count := l_dated_pref_count+1;
2179
2180 end loop;
2181 */
2182
2183 l_hier := l_hierarchy_list.first;
2184
2185 -- loop over the hierarchies we have found
2186
2187 l_hier := l_hierarchy_list.first;
2188
2189 g_loop_count := 0;
2190
2191 LOOP --1
2192 EXIT WHEN NOT l_hierarchy_list.exists(l_hier);
2193
2194 --reset for each Pref Id
2195 l_use_cache := FALSE;
2196
2197 -- Check if the required data is already cached.
2198 If ( g_pref_hier_ct.exists(l_hierarchy_list(l_hier).pref_hierarchy_id) ) then
2199
2200 Open c_get_last_updated_date(l_hierarchy_list(l_hier).pref_hierarchy_id);
2201 Fetch c_get_last_updated_date into l_last_updated_date;
2202 Close c_get_last_updated_date;
2203
2204 -- checking if the cache data is outdated.
2205 if ( g_pref_hier_ct(l_hierarchy_list(l_hier).pref_hierarchy_id).caching_time >= l_last_updated_date) then
2206 l_use_cache := TRUE;
2207 else
2208 l_use_cache := FALSE;
2209 -- Delete the Pref Values for this, since it has to be refreshed anyway.
2210 g_pref_values_ct.delete(g_pref_hier_ct(l_hierarchy_list(l_hier).pref_hierarchy_id).Start_Index,g_pref_hier_ct(l_hierarchy_list(l_hier).pref_hierarchy_id).Stop_Index); -- table.delete(m,n)
2211 end if;
2212 end if;
2213
2214 -- If l_use_cache = FALSE, then populate/refresh cache data by db fetch.
2215 If (not l_use_cache) then
2216
2217 -- initialise main table.
2218 g_pref_hier_ct(l_hierarchy_list(l_hier).pref_hierarchy_id).caching_time := sysdate;
2219 g_pref_hier_ct(l_hierarchy_list(l_hier).pref_hierarchy_id).Start_Index := -1;
2220 g_pref_hier_ct(l_hierarchy_list(l_hier).pref_hierarchy_id).Stop_Index := -1;
2221
2222 -- Initialize Start_Index for the Pref Values
2223 If (g_pref_values_ct.count > 0) then
2224 l_pref_index := g_pref_values_ct.last + 1;
2225 else
2226 l_pref_index := 1;
2227 End If;
2228
2229 g_pref_hier_ct(l_hierarchy_list(l_hier).pref_hierarchy_id).Start_Index := l_pref_index;
2230
2231 -- populate preference values into cache
2232 FOR pref_node in c_pref_nodes(l_hierarchy_list(l_hier).pref_hierarchy_id)
2233 LOOP
2234
2235 g_pref_values_ct(l_pref_index).pref_hierarchy_id := pref_node.pref_hierarchy_id;
2236 g_pref_values_ct(l_pref_index).pref_definition_id := pref_node.preference_id;
2237 g_pref_values_ct(l_pref_index).attribute1:= pref_node.attribute1;
2238 g_pref_values_ct(l_pref_index).attribute2:= pref_node.attribute2;
2239 g_pref_values_ct(l_pref_index).attribute3:= pref_node.attribute3;
2240 g_pref_values_ct(l_pref_index).attribute4:= pref_node.attribute4;
2241 g_pref_values_ct(l_pref_index).attribute5:= pref_node.attribute5;
2242 g_pref_values_ct(l_pref_index).attribute6:= pref_node.attribute6;
2243 g_pref_values_ct(l_pref_index).attribute7:= pref_node.attribute7;
2244 g_pref_values_ct(l_pref_index).attribute8:= pref_node.attribute8;
2245 g_pref_values_ct(l_pref_index).attribute9:= pref_node.attribute9;
2246 g_pref_values_ct(l_pref_index).attribute10:= pref_node.attribute10;
2247 g_pref_values_ct(l_pref_index).attribute11:= pref_node.attribute11;
2248 g_pref_values_ct(l_pref_index).attribute12:= pref_node.attribute12;
2249 g_pref_values_ct(l_pref_index).attribute13:= pref_node.attribute13;
2250 g_pref_values_ct(l_pref_index).attribute14:= pref_node.attribute14;
2251 g_pref_values_ct(l_pref_index).attribute15:= pref_node.attribute15;
2252 g_pref_values_ct(l_pref_index).attribute16:= pref_node.attribute16;
2253 g_pref_values_ct(l_pref_index).attribute17:= pref_node.attribute17;
2254 g_pref_values_ct(l_pref_index).attribute18:= pref_node.attribute18;
2255 g_pref_values_ct(l_pref_index).attribute19:= pref_node.attribute19;
2256 g_pref_values_ct(l_pref_index).attribute20:= pref_node.attribute20;
2257 g_pref_values_ct(l_pref_index).attribute21:= pref_node.attribute21;
2258 g_pref_values_ct(l_pref_index).attribute22:= pref_node.attribute22;
2259 g_pref_values_ct(l_pref_index).attribute23:= pref_node.attribute23;
2260 g_pref_values_ct(l_pref_index).attribute24:= pref_node.attribute24;
2261 g_pref_values_ct(l_pref_index).attribute25:= pref_node.attribute25;
2262 g_pref_values_ct(l_pref_index).attribute26:= pref_node.attribute26;
2263 g_pref_values_ct(l_pref_index).attribute27:= pref_node.attribute27;
2264 g_pref_values_ct(l_pref_index).attribute28:= pref_node.attribute28;
2265 g_pref_values_ct(l_pref_index).attribute29:= pref_node.attribute29;
2266 g_pref_values_ct(l_pref_index).attribute30:= pref_node.attribute30;
2267
2268 g_pref_values_ct(l_pref_index).edit_allowed:= pref_node.edit_allowed;
2269 g_pref_values_ct(l_pref_index).displayed:= pref_node.displayed;
2270 g_pref_values_ct(l_pref_index).name:= pref_node.name;
2271 g_pref_values_ct(l_pref_index).top_level_parent_id:= pref_node.top_level_parent_id;
2272 g_pref_values_ct(l_pref_index).code:= pref_node.code;
2273
2274 l_pref_index := g_pref_values_ct.last + 1;
2275 End Loop;
2276
2277 g_pref_hier_ct(l_hierarchy_list(l_hier).pref_hierarchy_id).Stop_Index := l_pref_index - 1;
2278
2279 -- check for valid start and stop index. Incase the only leaf node was deleted or no leaf nodes exists then reset start and stop index accordingly
2280 if (g_pref_hier_ct(l_hierarchy_list(l_hier).pref_hierarchy_id).Stop_Index < g_pref_hier_ct(l_hierarchy_list(l_hier).pref_hierarchy_id).Start_Index) then
2281 g_pref_hier_ct(l_hierarchy_list(l_hier).pref_hierarchy_id).Start_Index := 0;
2282 g_pref_hier_ct(l_hierarchy_list(l_hier).pref_hierarchy_id).Stop_Index := 0;
2283 end if;
2284
2285 l_use_cache := TRUE; -- now data is in cache
2286 End If;
2287
2288 -- Now all required data is in cache.
2289 If (g_pref_hier_ct(l_hierarchy_list(l_hier).pref_hierarchy_id).Stop_Index <> 0 ) then --(case where parent node has no children)
2290 For l_loop_index in g_pref_hier_ct(l_hierarchy_list(l_hier).pref_hierarchy_id).Start_Index..g_pref_hier_ct(l_hierarchy_list(l_hier).pref_hierarchy_id).Stop_index Loop -- 2
2291
2292 -- record node details in list
2293 -- Note. We shouldnt actually make these assignments before we know we are goiing to
2294 -- need the values. Need some reordering of code to achieve this. Also note that should
2295 -- try to make the assignments in bulk (i.e. record to record) for performance
2296 -- AND readability
2297
2298 l_pref_count := l_pref_count+1;
2299 l_pref_table(l_pref_count).preference_code := g_pref_values_ct(l_loop_index).code;--Performance Fix l_pref_trans(pref_node.preference_id);
2300 l_pref_table(l_pref_count).attribute1 := g_pref_values_ct(l_loop_index).attribute1;
2301 l_pref_table(l_pref_count).attribute2 := g_pref_values_ct(l_loop_index).attribute2;
2302 l_pref_table(l_pref_count).attribute3 := g_pref_values_ct(l_loop_index).attribute3;
2303 l_pref_table(l_pref_count).attribute4 := g_pref_values_ct(l_loop_index).attribute4;
2304 l_pref_table(l_pref_count).attribute5 := g_pref_values_ct(l_loop_index).attribute5;
2305 l_pref_table(l_pref_count).attribute6 := g_pref_values_ct(l_loop_index).attribute6;
2306 l_pref_table(l_pref_count).attribute7 := g_pref_values_ct(l_loop_index).attribute7;
2307 l_pref_table(l_pref_count).attribute8 := g_pref_values_ct(l_loop_index).attribute8;
2308 l_pref_table(l_pref_count).attribute9 := g_pref_values_ct(l_loop_index).attribute9;
2309 l_pref_table(l_pref_count).attribute10 := g_pref_values_ct(l_loop_index).attribute10;
2310 l_pref_table(l_pref_count).attribute11 := g_pref_values_ct(l_loop_index).attribute11;
2311 l_pref_table(l_pref_count).attribute12 := g_pref_values_ct(l_loop_index).attribute12;
2312 l_pref_table(l_pref_count).attribute13 := g_pref_values_ct(l_loop_index).attribute13;
2313 l_pref_table(l_pref_count).attribute14 := g_pref_values_ct(l_loop_index).attribute14;
2314 l_pref_table(l_pref_count).attribute15 := g_pref_values_ct(l_loop_index).attribute15;
2315 l_pref_table(l_pref_count).attribute16 := g_pref_values_ct(l_loop_index).attribute16;
2316 l_pref_table(l_pref_count).attribute17 := g_pref_values_ct(l_loop_index).attribute17;
2317 l_pref_table(l_pref_count).attribute18 := g_pref_values_ct(l_loop_index).attribute18;
2318 l_pref_table(l_pref_count).attribute19 := g_pref_values_ct(l_loop_index).attribute19;
2319 l_pref_table(l_pref_count).attribute20 := g_pref_values_ct(l_loop_index).attribute20;
2320 l_pref_table(l_pref_count).attribute21 := g_pref_values_ct(l_loop_index).attribute21;
2321 l_pref_table(l_pref_count).attribute22 := g_pref_values_ct(l_loop_index).attribute22;
2322 l_pref_table(l_pref_count).attribute23 := g_pref_values_ct(l_loop_index).attribute23;
2323 l_pref_table(l_pref_count).attribute24 := g_pref_values_ct(l_loop_index).attribute24;
2324 l_pref_table(l_pref_count).attribute25 := g_pref_values_ct(l_loop_index).attribute25;
2325 l_pref_table(l_pref_count).attribute26 := g_pref_values_ct(l_loop_index).attribute26;
2326 l_pref_table(l_pref_count).attribute27 := g_pref_values_ct(l_loop_index).attribute27;
2327 l_pref_table(l_pref_count).attribute28 := g_pref_values_ct(l_loop_index).attribute28;
2328 l_pref_table(l_pref_count).attribute29 := g_pref_values_ct(l_loop_index).attribute29;
2329 l_pref_table(l_pref_count).attribute30 := g_pref_values_ct(l_loop_index).attribute30;
2330 l_pref_table(l_pref_count).edit_allowed := g_pref_values_ct(l_loop_index).edit_allowed;
2331 l_pref_table(l_pref_count).displayed := g_pref_values_ct(l_loop_index).displayed;
2332 l_pref_table(l_pref_count).rule_evaluation_order
2333 := l_hierarchy_list(l_hier).rule_evaluation_order;
2334
2335 -- the start date and end dates take into account eligibilty dates. Want the overlap between
2336 -- eligbility range and rule range
2337
2338 if(l_hierarchy_list(l_hier).start_date>=l_hierarchy_list(l_hier).elig_start_date) then
2339 l_pref_table(l_pref_count).start_date := l_hierarchy_list(l_hier).start_date;
2340 else
2341 l_pref_table(l_pref_count).start_date := l_hierarchy_list(l_hier).elig_start_date;
2342 end if;
2343
2344 if(l_hierarchy_list(l_hier).end_date>=l_hierarchy_list(l_hier).elig_end_date) then
2345 l_pref_table(l_pref_count).end_date := l_hierarchy_list(l_hier).elig_end_date;
2346 else
2347 l_pref_table(l_pref_count).end_date := l_hierarchy_list(l_hier).end_date;
2348 end if;
2349
2350 -- further restrict by start / end evaluation_dates
2351 if(l_pref_table(l_pref_count).start_date<l_start_evaluation_date) then
2352 l_pref_table(l_pref_count).start_date := l_start_evaluation_date;
2353 end if;
2354
2355 if(l_pref_table(l_pref_count).end_date>l_end_evaluation_date) then
2356 l_pref_table(l_pref_count).end_date := l_end_evaluation_date;
2357 end if;
2358
2359 l_pref_table(l_pref_count).name := g_pref_values_ct(l_loop_index).name;
2360
2361 -- now work out what to do with this row by looping over the l_dated_prefs structure.
2362 -- Note we only want to compare against nodes of the same pref code. Shouldnt have to
2363 -- loop through entire l_dated_prefs table to do this. Should structure the data so that
2364 -- it can be accessed faster.
2365
2366 -- Performance Fix.
2367 /* if(not l_pref_encountered.exists(pref_node.preference_id)) THEN
2368 -- set a dummy default preference for each preference code (start and ends at evaluation dates)
2369 -- These will be overridden by default preferences
2370 l_dated_prefs(l_dated_pref_count).start_date := l_hierarchy_list(0).start_date;
2371 l_dated_prefs(l_dated_pref_count).end_date := l_hierarchy_list(0).end_date;
2372 l_dated_prefs(l_dated_pref_count).rule_evaluation_order := -1;
2373 l_dated_prefs(l_dated_pref_count).pref_ref := 1;
2374 l_dated_pref_count := l_dated_pref_count+1;
2375 -- mark this preference as having being defaulted (initialized).
2376 l_pref_encountered(pref_node.preference_id) := '1';
2377 end if;*/
2378
2379 l_index:=l_dated_prefs.first;
2380 l_finish:=l_dated_prefs.last;
2381
2382 LOOP --3
2383
2384 -- for all dated pref rows with the same code as the new pref and that the precidence
2385 -- of the new pref is higher
2386 -- (also take into account personalization - which has a rule_evaluation_order of 0
2387 -- but overrides everything where the base pref has the edit allowed flag
2388
2389 if( l_dated_prefs(l_index).code = l_pref_table(l_pref_count).preference_code
2390 AND (
2391 (
2392 (l_dated_prefs(l_index).rule_evaluation_order <=
2393 l_pref_table(l_pref_count).rule_evaluation_order)
2394 AND l_dated_prefs(l_index).rule_evaluation_order <>0
2395 )
2396 OR
2397 (
2398 l_pref_table(l_pref_count).rule_evaluation_order = 0
2399 AND l_dated_prefs(l_index).edit_allowed = 'Y'
2400 )
2401 )
2402 ) THEN
2403
2404 -- check that new / old pref overlap
2405
2406 if(l_dated_prefs(l_index).start_date<=l_pref_table(l_pref_count).end_date and
2407 l_pref_table(l_pref_count).start_date<=l_dated_prefs(l_index).end_date) then
2408
2409 -- overlap of rows: Four possible cases - one special case
2410
2411 if( l_dated_prefs(l_index).start_date = l_pref_table(l_pref_count).start_date
2412 and l_dated_prefs(l_index).end_date = l_pref_table(l_pref_count).end_date ) then
2413
2414 l_dated_prefs(l_index).pref_ref := l_pref_count;
2415 l_dated_prefs(l_index).rule_evaluation_order
2416 := l_pref_table(l_pref_count).rule_evaluation_order;
2417 l_dated_prefs(l_index).edit_allowed := l_pref_table(l_pref_count).edit_allowed;
2418
2419 elsif( l_dated_prefs(l_index).start_date >= l_pref_table(l_pref_count).start_date
2420 and l_dated_prefs(l_index).end_date <= l_pref_table(l_pref_count).end_date ) then
2421
2422 -- case 1: New row spans /equivalent to old row.
2423 -- Change row attribution but keep dates
2424 -- Number of rows does not change
2425 -- |++++++++|
2426 -- +
2427 -- |-------|
2428 -- =
2429 -- |+++++++|
2430
2431 l_dated_prefs(l_index).pref_ref := l_pref_count;
2432 l_dated_prefs(l_index).rule_evaluation_order
2433 := l_pref_table(l_pref_count).rule_evaluation_order;
2434 l_dated_prefs(l_index).edit_allowed := l_pref_table(l_pref_count).edit_allowed;
2435
2436
2437 elsif( l_dated_prefs(l_index).start_date >= l_pref_table(l_pref_count).start_date
2438 and l_dated_prefs(l_index).end_date >= l_pref_table(l_pref_count).end_date) then
2439
2440 -- case 2: New row paritally overlaps old row (spans old row start_date)
2441 -- Create row with new row attribution between old row start date and new row
2442 -- stop date
2443 -- Change start date of old row to end date of new row
2444 -- Number of rows increased by 1
2445 -- |+++++|
2446 -- +
2447 -- |-------|
2448 -- =
2449 -- |++|----|
2450
2451 l_dated_prefs(l_index).start_date := l_pref_table(l_pref_count).end_date+1;
2452 l_dated_pref_count:=l_dated_pref_count+1;
2453 l_dated_prefs(l_dated_pref_count).start_date := l_pref_table(l_pref_count).start_date;
2454 l_dated_prefs(l_dated_pref_count).end_date := l_pref_table(l_pref_count).end_date;
2455 l_dated_prefs(l_dated_pref_count).rule_evaluation_order
2456 := l_pref_table(l_pref_count).rule_evaluation_order;
2457 l_dated_prefs(l_dated_pref_count).edit_allowed := l_pref_table(l_pref_count).edit_allowed;
2458 l_dated_prefs(l_dated_pref_count).code := l_pref_table(l_pref_count).preference_code;
2459 l_dated_prefs(l_dated_pref_count).pref_ref := l_pref_count;
2460
2461 elsif( l_dated_prefs(l_index).start_date <= l_pref_table(l_pref_count).start_date
2462 and l_dated_prefs(l_index).end_date <= l_pref_table(l_pref_count).end_date) then
2463
2464 -- case 3: New row paritally overlaps old row (spans old row stop_date)
2465 -- Create row with new row attribution between old row start date and new
2466 -- row stop date
2467 -- Change end of old row to start date of new row
2468 -- Number of rows increased by 1
2469 -- |+++++|
2470 -- +
2471 -- |-------|
2472 -- =
2473 -- |----|++|
2474 l_tmp_date:=l_dated_prefs(l_index).end_date;
2475 l_dated_prefs(l_index).end_date := l_pref_table(l_pref_count).start_date-1;
2476 -- -1 because new row has precedence
2477 l_dated_pref_count:=l_dated_pref_count+1;
2478 l_dated_prefs(l_dated_pref_count).start_date := l_pref_table(l_pref_count).start_date;
2479 l_dated_prefs(l_dated_pref_count).end_date := l_tmp_date;
2480 l_dated_prefs(l_dated_pref_count).rule_evaluation_order
2481 := l_pref_table(l_pref_count).rule_evaluation_order;
2482 l_dated_prefs(l_dated_pref_count).edit_allowed := l_pref_table(l_pref_count).edit_allowed;
2483 l_dated_prefs(l_dated_pref_count).code := l_pref_table(l_pref_count).preference_code;
2484 l_dated_prefs(l_dated_pref_count).pref_ref := l_pref_count;
2485
2486 elsif( l_dated_prefs(l_index).start_date <= l_pref_table(l_pref_count).start_date
2487 and l_dated_prefs(l_index).end_date >= l_pref_table(l_pref_count).end_date) then
2488
2489 -- case 4: New row spanned / equivalent to old row
2490 -- Create 2 new rows.
2491 -- Change end date of old row to be start date of new row
2492 -- create row with start date of new row and end date of new row with new
2493 -- row attribution
2494 -- create new row with start date of end date of new row and end date of old
2495 -- row with attribution of old row
2496 -- |+++|
2497 -- +
2498 -- |-------|
2499 -- =
2500 -- |-|+++|-|
2501 l_tmp_date:=l_dated_prefs(l_index).start_date;
2502 l_dated_prefs(l_index).start_date := l_pref_table(l_pref_count).end_date+1;
2503 l_dated_pref_count:=l_dated_pref_count+1;
2504 l_dated_prefs(l_dated_pref_count).start_date := l_pref_table(l_pref_count).start_date;
2505 l_dated_prefs(l_dated_pref_count).end_date := l_pref_table(l_pref_count).end_date;
2506 l_dated_prefs(l_dated_pref_count).rule_evaluation_order
2507 := l_pref_table(l_pref_count).rule_evaluation_order;
2508 l_dated_prefs(l_dated_pref_count).edit_allowed := l_pref_table(l_pref_count).edit_allowed;
2509 l_dated_prefs(l_dated_pref_count).code := l_pref_table(l_pref_count).preference_code;
2510 l_dated_prefs(l_dated_pref_count).pref_ref := l_pref_count;
2511 l_dated_pref_count:=l_dated_pref_count+1;
2512 l_dated_prefs(l_dated_pref_count).start_date := l_tmp_date;
2513 l_dated_prefs(l_dated_pref_count).end_date := l_pref_table(l_pref_count).start_date-1;
2514 l_dated_prefs(l_dated_pref_count).rule_evaluation_order
2515 :=l_dated_prefs(l_index).rule_evaluation_order;
2516 l_dated_prefs(l_dated_pref_count).edit_allowed :=l_dated_prefs(l_index).edit_allowed;
2517 l_dated_prefs(l_dated_pref_count).code := l_pref_table(l_pref_count).preference_code;
2518 l_dated_prefs(l_dated_pref_count).pref_ref := l_dated_prefs(l_index).pref_ref;
2519 end if;
2520
2521 end if;
2522 end if;
2523
2524 exit when (l_index = l_finish);
2525
2526 l_index := l_dated_prefs.next(l_index);
2527
2528 -- IF(g_loop_count > g_maxloop) THEN
2529 -- hr_utility.set_message(809, 'HXC_LPS_GT_MAX');
2530 -- hr_utility.raise_error;
2531 -- END IF;
2532
2533 -- g_loop_count := g_loop_count + 1;
2534
2535 END LOOP; --3 update of existing dated prefrences
2536
2537 END LOOP; --2 node loop;
2538 End if;
2539
2540 l_hier := l_hierarchy_list.next(l_hier);
2541
2542 -- g_loop_count := g_loop_count + 1;
2543
2544 IF(g_loop_count > g_maxloop) THEN
2545 hr_utility.set_message(809, 'HXC_LPS_GT_MAX');
2546 hr_utility.raise_error;
2547 END IF;
2548
2549 END LOOP; --1 hierarchy loop;
2550
2551 -- now build out results table based on calculations
2552 -- create an entry for each pref - also assign dates
2553
2554
2555 l_index:=l_dated_prefs.first;
2556 l_pref_count:=0;
2557
2558
2559 LOOP
2560 EXIT when not l_dated_prefs.exists(l_index);
2561
2562 IF (p_no_prefs_outside_asg = TRUE AND l_dated_prefs(l_index).rule_evaluation_order <> -1)
2563 OR p_no_prefs_outside_asg = FALSE
2564 THEN
2565
2566 l_pref_ref := l_dated_prefs(l_index).pref_ref;
2567
2568 p_pref_table(l_pref_count).preference_code := l_dated_prefs(l_index).code;
2569 p_pref_table(l_pref_count).start_date := l_dated_prefs(l_index).start_date;
2570 p_pref_table(l_pref_count).end_date := l_dated_prefs(l_index).end_date;
2571 p_pref_table(l_pref_count).rule_evaluation_order
2572 := l_dated_prefs(l_index).rule_evaluation_order;
2573 p_pref_table(l_pref_count).attribute1
2574 := l_pref_table(l_pref_ref).attribute1;
2575 p_pref_table(l_pref_count).attribute2 := l_pref_table(l_pref_ref).attribute2;
2576 p_pref_table(l_pref_count).attribute3 := l_pref_table(l_pref_ref).attribute3;
2577 p_pref_table(l_pref_count).attribute4 := l_pref_table(l_pref_ref).attribute4;
2578 p_pref_table(l_pref_count).attribute5 := l_pref_table(l_pref_ref).attribute5;
2579 p_pref_table(l_pref_count).attribute6 := l_pref_table(l_pref_ref).attribute6;
2580 p_pref_table(l_pref_count).attribute7 := l_pref_table(l_pref_ref).attribute7;
2581 p_pref_table(l_pref_count).attribute8 := l_pref_table(l_pref_ref).attribute8;
2582 p_pref_table(l_pref_count).attribute9 := l_pref_table(l_pref_ref).attribute9;
2583 p_pref_table(l_pref_count).attribute10 := l_pref_table(l_pref_ref).attribute10;
2584 p_pref_table(l_pref_count).attribute11 := l_pref_table(l_pref_ref).attribute11;
2585 p_pref_table(l_pref_count).attribute12 := l_pref_table(l_pref_ref).attribute12;
2586 p_pref_table(l_pref_count).attribute13 := l_pref_table(l_pref_ref).attribute13;
2587 p_pref_table(l_pref_count).attribute14 := l_pref_table(l_pref_ref).attribute14;
2588 p_pref_table(l_pref_count).attribute15 := l_pref_table(l_pref_ref).attribute15;
2589 p_pref_table(l_pref_count).attribute16 := l_pref_table(l_pref_ref).attribute16;
2590 p_pref_table(l_pref_count).attribute17 := l_pref_table(l_pref_ref).attribute17;
2591 p_pref_table(l_pref_count).attribute18 := l_pref_table(l_pref_ref).attribute18;
2592 p_pref_table(l_pref_count).attribute19 := l_pref_table(l_pref_ref).attribute19;
2593 p_pref_table(l_pref_count).attribute20 := l_pref_table(l_pref_ref).attribute20;
2594 p_pref_table(l_pref_count).attribute21 := l_pref_table(l_pref_ref).attribute21;
2595 p_pref_table(l_pref_count).attribute22 := l_pref_table(l_pref_ref).attribute22;
2596 p_pref_table(l_pref_count).attribute23 := l_pref_table(l_pref_ref).attribute23;
2597 p_pref_table(l_pref_count).attribute24 := l_pref_table(l_pref_ref).attribute24;
2598 p_pref_table(l_pref_count).attribute25 := l_pref_table(l_pref_ref).attribute25;
2599 p_pref_table(l_pref_count).attribute26 := l_pref_table(l_pref_ref).attribute26;
2600 p_pref_table(l_pref_count).attribute27 := l_pref_table(l_pref_ref).attribute27;
2601 p_pref_table(l_pref_count).attribute28 := l_pref_table(l_pref_ref).attribute28;
2602 p_pref_table(l_pref_count).attribute29 := l_pref_table(l_pref_ref).attribute29;
2603 p_pref_table(l_pref_count).attribute30 := l_pref_table(l_pref_ref).attribute30;
2604 p_pref_table(l_pref_count).edit_allowed := l_pref_table(l_pref_ref).edit_allowed;
2605 p_pref_table(l_pref_count).displayed := l_pref_table(l_pref_ref).displayed;
2606
2607 END IF;
2608
2609 l_index:=l_dated_prefs.next(l_index);
2610
2611 IF(g_loop_count > g_maxloop) THEN
2612 hr_utility.set_message(809, 'HXC_LPS_GT_MAX');
2613 hr_utility.raise_error;
2614 END IF;
2615
2616 -- g_loop_count := g_loop_count + 1;
2617
2618 l_pref_count:=l_pref_count+1;
2619
2620 END LOOP;
2621
2622 END resource_preferences;
2623
2624
2625 ----
2626 -- Supporting function to allow inquiries as to whether specific values have been used
2627 -- in preference hierarchies. Useful for data integrity checking.
2628 ----
2629
2630 FUNCTION num_hierarchy_occurances(p_preference_code IN VARCHAR2,
2631 p_attributen IN NUMBER,
2632 p_value IN VARCHAR2) RETURN NUMBER
2633 IS
2634
2635 -- note this cursor contains a decode in the WHERE clause. Could cause performance issue.
2636 -- Hovever the SQL is very light and this is a function that will be called during application
2637 -- setup and is therefore not performance critical
2638
2639 l_count NUMBER;
2640
2641 CURSOR get_num_hierarchy_occurances(p_preference_code VARCHAR2,
2642 p_attributen NUMBER,
2643 p_value VARCHAR2)
2644 IS
2645 SELECT count(*)
2646 FROM hxc_pref_hierarchies hph,
2647 hxc_pref_definitions hpd
2648 WHERE hph.pref_definition_id = hpd.pref_definition_id
2649 AND hpd.code = p_preference_code
2650 AND decode (p_attributen, 1, hph.attribute1, 2, hph.attribute2, 3, hph.attribute3,
2651 4, hph.attribute4, 5, hph.attribute5, 6, hph.attribute6,
2652 7, hph.attribute7, 8, hph.attribute8, 9, hph.attribute9,
2653 10, hph.attribute10,11, hph.attribute11,12, hph.attribute12,
2654 13, hph.attribute13,14, hph.attribute14,15, hph.attribute15,
2655 16, hph.attribute16,17, hph.attribute17,18, hph.attribute18,
2656 19, hph.attribute19,20, hph.attribute20,21, hph.attribute21,
2657 22, hph.attribute22,23, hph.attribute23,24, hph.attribute24,
2658 25, hph.attribute25,26, hph.attribute26,27, hph.attribute27,
2659 28, hph.attribute28,29, hph.attribute29,30, hph.attribute30) = p_value;
2660
2661 BEGIN
2662
2663 OPEN get_num_hierarchy_occurances(p_preference_code,
2664 p_attributen,
2665 p_value);
2666
2667 FETCH get_num_hierarchy_occurances INTO l_count;
2668
2669 RETURN l_count;
2670
2671 END num_hierarchy_occurances;
2672
2673
2674 PROCEDURE resource_preferences(p_resource_id in NUMBER,
2675 p_preference_code IN VARCHAR2,
2676 p_start_evaluation_date DATE,
2677 p_end_evaluation_date DATE,
2678 p_sorted_pref_table IN OUT NOCOPY t_pref_table,
2679 p_clear_cache BOOLEAN DEFAULT FALSE,
2680 p_no_prefs_outside_asg IN BOOLEAN DEFAULT FALSE ) IS
2681
2682 l_prefs t_pref_table;
2683
2684 l_prefs_filter t_pref_table; -- filtered table
2685 l_prefs_sorted t_pref_table; -- filtered in date order table
2686
2687 l_ind BINARY_INTEGER;
2688 l_sort_ind BINARY_INTEGER := 1;
2689
2690 l_not_sorted BOOLEAN := TRUE;
2691
2692 l_temp_pref_rec t_pref_table_row;
2693 l_temp_ind BINARY_INTEGER;
2694
2695 BEGIN
2696
2697 IF ( g_sort_pref_table.COUNT <> 0 )
2698 THEN
2699
2700 IF ( p_clear_cache
2701 OR ( g_sort_cache(1).resource_id <> p_resource_id )
2702 OR ( ( g_sort_cache(1).start_date <> p_start_evaluation_date ) OR
2703 ( g_sort_cache(1).end_date <> p_end_evaluation_date ) ) )
2704 THEN
2705
2706 g_sort_pref_table.DELETE;
2707
2708 hxc_preference_evaluation.resource_preferences(p_resource_id => p_resource_id,
2709 p_start_evaluation_date => p_start_evaluation_date,
2710 p_end_evaluation_date => p_end_evaluation_date,
2711 p_pref_table => g_sort_pref_table,
2712 p_no_prefs_outside_asg => p_no_prefs_outside_asg );
2713
2714 g_sort_cache(1).resource_id := p_resource_id;
2715 g_sort_cache(1).start_date := p_start_evaluation_date;
2716 g_sort_cache(1).end_date := p_end_evaluation_date;
2717
2718 END IF;
2719
2720 ELSE
2721
2722 hxc_preference_evaluation.resource_preferences(p_resource_id => p_resource_id,
2723 p_start_evaluation_date => p_start_evaluation_date,
2724 p_end_evaluation_date => p_end_evaluation_date,
2725 p_pref_table => g_sort_pref_table,
2726 p_no_prefs_outside_asg => p_no_prefs_outside_asg );
2727
2728 g_sort_cache(1).resource_id := p_resource_id;
2729 g_sort_cache(1).start_date := p_start_evaluation_date;
2730 g_sort_cache(1).end_date := p_end_evaluation_date;
2731
2732
2733 END IF;
2734
2735
2736 l_ind := g_sort_pref_table.FIRST;
2737
2738 -- filter out the desired preference
2739
2740 WHILE l_ind IS NOT NULL
2741 LOOP
2742
2743 IF ( g_sort_pref_table(l_ind).preference_code = p_preference_code )
2744 THEN
2745 l_temp_ind := to_char(g_sort_pref_table(l_ind).start_date,'J');
2746
2747 IF l_prefs_filter.exists(l_temp_ind) AND
2748 l_prefs_filter(l_temp_ind).end_date >= g_sort_pref_table(l_ind).end_date THEN
2749
2750 NULL;
2751 ELSE
2752 l_prefs_filter(l_temp_ind) := g_sort_pref_table(l_ind);
2753 END IF;
2754
2755 END IF;
2756
2757 l_ind := g_sort_pref_table.NEXT(l_ind);
2758
2759 END LOOP;
2760
2761 -- now copy sorted table into table with binary index incremented by 1
2762
2763 l_ind := l_prefs_filter.FIRST;
2764
2765 WHILE l_ind IS NOT NULL
2766 LOOP
2767
2768 l_prefs_sorted(l_sort_ind) := l_prefs_filter(l_ind);
2769
2770 l_ind := l_prefs_filter.NEXT(l_ind);
2771
2772 l_sort_ind := l_sort_ind + 1;
2773
2774 END LOOP;
2775
2776 l_prefs_filter.DELETE;
2777
2778 p_sorted_pref_table := l_prefs_sorted;
2779
2780 end resource_preferences;
2781
2782 PROCEDURE resource_preferences(p_resource_id in NUMBER,
2783 p_preference_code IN VARCHAR2,
2784 p_start_evaluation_date DATE,
2785 p_end_evaluation_date DATE,
2786 p_sorted_pref_table IN OUT NOCOPY t_pref_table,
2787 p_clear_cache BOOLEAN DEFAULT FALSE,
2788 p_master_pref_table t_pref_table ) IS
2789
2790 l_tmp_pref_table t_pref_table;
2791
2792 l_ind BINARY_INTEGER := 1;
2793
2794 BEGIN
2795
2796 g_sort_pref_table.DELETE;
2797
2798 g_sort_pref_table := p_master_pref_table;
2799
2800 g_sort_cache(1).resource_id := p_resource_id;
2801 g_sort_cache(1).start_date := p_start_evaluation_date;
2802 g_sort_cache(1).end_date := p_end_evaluation_date;
2803
2804 resource_preferences (
2805 p_resource_id => p_resource_id,
2806 p_preference_code => p_preference_code,
2807 p_start_evaluation_date => p_start_evaluation_date,
2808 p_end_evaluation_date => p_end_evaluation_date,
2809 p_sorted_pref_table => p_sorted_pref_table,
2810 p_clear_cache => FALSE );
2811
2812 -- GPM 115.27 start
2813 -- the preference table passed here was for all dates
2814 -- filter out the unwanted dates
2815
2816 FOR x in 1 .. p_sorted_pref_table.LAST
2817 LOOP
2818 -- Bug 6123330
2819 IF ( to_date(to_char(p_sorted_pref_table(x).end_date,'DD-MM-YYYY'),'DD-MM-YYYY') >= to_date(to_char(p_start_evaluation_date,'DD-MM-YYYY'),'DD-MM-YYYY') AND
2820 to_date(to_char(p_sorted_pref_table(x).start_date,'DD-MM-YYYY'),'DD-MM-YYYY') <= to_date(to_char(p_end_evaluation_date,'DD-MM-YYYY'),'DD-MM-YYYY') )
2821 THEN
2822
2823 l_tmp_pref_table(l_ind) := p_sorted_pref_table(x);
2824
2825 l_ind := l_ind + 1;
2826
2827 END IF;
2828
2829 END LOOP;
2830
2831 p_sorted_pref_table := l_tmp_pref_table;
2832
2833 l_tmp_pref_table.DELETE;
2834
2835 -- GPM 115.27 end
2836
2837 END resource_preferences;
2838
2839 -- this clears the pref table cache when preference evaluation is finished
2840 -- to allow memory saving.
2841
2842 PROCEDURE clear_sort_pref_table_cache IS
2843
2844 BEGIN
2845
2846 g_sort_pref_table.DELETE;
2847
2848 END clear_sort_pref_table_cache;
2849
2850 FUNCTION migration_mode
2851 RETURN BOOLEAN
2852 IS
2853 l_proc VARCHAR2 (72);
2854 BEGIN
2855 g_debug := hr_utility.debug_enabled;
2856
2857 if g_debug then
2858 l_proc := g_package|| 'migration_mode';
2859 hr_utility.set_location ( 'Entering:'
2860 || l_proc, 10);
2861 end if;
2862
2863 IF (g_migration_mode)
2864 THEN
2865 if g_debug then
2866 hr_utility.set_location (' returning g_migration_mode = TRUE', 20);
2867 end if;
2868 ELSE
2869 if g_debug then
2870 hr_utility.set_location (' returning g_migration_mode = FALSE', 30);
2871 end if;
2872 END IF;
2873
2874 if g_debug then
2875 hr_utility.set_location ( 'Leaving:'
2876 || l_proc, 100);
2877 end if;
2878 RETURN g_migration_mode;
2879 END migration_mode;
2880 PROCEDURE set_migration_mode (p_migration_mode IN BOOLEAN)
2881 IS
2882 l_proc VARCHAR2 (72);
2883 BEGIN
2884 g_debug := hr_utility.debug_enabled;
2885
2886 if g_debug then
2887 l_proc := g_package||'set_migration_mode';
2888 hr_utility.set_location ( 'Entering:'|| l_proc, 10);
2889 end if;
2890
2891 IF (p_migration_mode)
2892 THEN
2893 if g_debug then
2894 hr_utility.set_location (' setting g_migration_mode to TRUE', 20);
2895 end if;
2896 ELSE
2897 if g_debug then
2898 hr_utility.set_location (' setting g_migration_mode to FALSE', 30);
2899 end if;
2900 END IF;
2901
2902 g_migration_mode := p_migration_mode;
2903 if g_debug then
2904 hr_utility.set_location ( 'Leaving:'
2905 || l_proc, 100);
2906 end if;
2907 END set_migration_mode;
2908 FUNCTION employment_ended (
2909 p_person_id per_all_people_f.person_id%TYPE,
2910 p_effective_date per_all_assignments_f.effective_start_date%TYPE
2911 DEFAULT SYSDATE
2912 )
2913 RETURN BOOLEAN
2914 IS
2915 l_proc VARCHAR2 (72);
2916 l_employment_ended BOOLEAN;
2917
2918 CURSOR csr_existing_employment (
2919 p_person_id per_all_people_f.person_id%TYPE,
2920 p_effective_date per_all_assignments_f.effective_start_date%TYPE
2921 )
2922 IS
2923 SELECT 1
2924 FROM per_all_assignments_f paaf, per_assignment_status_types past
2925 WHERE paaf.person_id = p_person_id
2926 AND p_effective_date BETWEEN paaf.effective_start_date
2927 AND paaf.effective_end_date
2928 AND paaf.assignment_type IN ( 'E','C')
2929 AND past.assignment_status_type_id = paaf.assignment_status_type_id
2930 AND past.per_system_status IN ('ACTIVE_ASSIGN','ACTIVE_CWK');
2931
2932 l_existing_employment csr_existing_employment%ROWTYPE;
2933 BEGIN
2934 g_debug := hr_utility.debug_enabled;
2935
2936 if g_debug then
2937 l_proc := g_package|| 'employment_ended';
2938 hr_utility.set_location ( 'Entering:'
2939 || l_proc, 10);
2940 end if;
2941 OPEN csr_existing_employment (p_person_id, p_effective_date);
2942 FETCH csr_existing_employment INTO l_existing_employment;
2943
2944 IF (csr_existing_employment%NOTFOUND)
2945 THEN
2946 if g_debug then
2947 hr_utility.set_location (
2948 ' The employment for '
2949 || p_person_id
2950 || 'ended.',
2951 20
2952 );
2953 end if;
2954 l_employment_ended := TRUE;
2955 ELSE
2956 l_employment_ended := FALSE;
2957 END IF;
2958
2959 CLOSE csr_existing_employment;
2960 if g_debug then
2961 hr_utility.set_location ( 'Leaving:'
2962 || l_proc, 100);
2963 end if;
2964 RETURN l_employment_ended;
2965 END employment_ended;
2966
2967 FUNCTION assignment_last_eff_dt (
2968 p_person_id per_all_people_f.person_id%TYPE,
2969 p_effective_date per_all_assignments_f.effective_start_date%TYPE
2970 DEFAULT SYSDATE
2971 )
2972 RETURN per_all_assignments_f.effective_start_date%TYPE
2973 IS
2974 l_proc VARCHAR2 (72);
2975 l_assignment_last_eff_dt per_all_assignments_f.effective_start_date%TYPE;
2976
2977 CURSOR csr_assignment_last_eff_dt (
2978 p_person_id per_all_people_f.person_id%TYPE,
2979 p_effective_date per_all_assignments_f.effective_start_date%TYPE
2980 )
2981 IS
2982 SELECT MAX (paaf.effective_end_date)
2983 FROM per_all_assignments_f paaf, per_assignment_status_types past
2984 WHERE paaf.person_id = p_person_id
2985 AND paaf.effective_end_date <= p_effective_date
2986 AND paaf.assignment_type IN ( 'E', 'C')
2987 AND past.assignment_status_type_id = paaf.assignment_status_type_id
2988 AND past.per_system_status IN ( 'ACTIVE_ASSIGN','ACTIVE_CWK');
2989 BEGIN
2990 g_debug := hr_utility.debug_enabled;
2991
2992 if g_debug then
2993 l_proc := g_package|| 'assignment_last_eff_dt';
2994 hr_utility.set_location ( 'Entering:'
2995 || l_proc, 10);
2996 end if;
2997 OPEN csr_assignment_last_eff_dt (p_person_id, p_effective_date);
2998 FETCH csr_assignment_last_eff_dt INTO l_assignment_last_eff_dt;
2999
3000 IF (csr_assignment_last_eff_dt%NOTFOUND)
3001 THEN
3002 -- this will get handled during the actuall preference evaluation, we don't
3003 -- have to act on it here.
3004 l_assignment_last_eff_dt := NULL;
3005 ELSE
3006 if g_debug then
3007 hr_utility.set_location (
3008 ' The last available effective date for person '
3009 || p_person_id
3010 || ' is '
3011 || l_assignment_last_eff_dt,
3012 20
3013 );
3014 end if;
3015 END IF;
3016
3017 CLOSE csr_assignment_last_eff_dt;
3018 if g_debug then
3019 hr_utility.set_location ( 'Leaving:'
3020 || l_proc, 100);
3021 end if;
3022 RETURN l_assignment_last_eff_dt;
3023 END assignment_last_eff_dt;
3024
3025 FUNCTION evaluation_date (
3026 p_resource_id hxc_time_building_blocks.resource_id%TYPE,
3027 p_evaluation_date DATE
3028 )
3029 RETURN DATE
3030 IS
3031 l_proc VARCHAR2 (72);
3032 l_evaluation_date DATE;
3033 BEGIN
3034 g_debug := hr_utility.debug_enabled;
3035
3036 if g_debug then
3037 l_proc := g_package|| 'evaluation_date';
3038 hr_utility.set_location ( 'Entering:'
3039 || l_proc, 10);
3040 end if;
3041
3042 IF ( (migration_mode)
3043 AND (employment_ended (p_person_id => p_resource_id))
3044 AND TRUNC (p_evaluation_date) = TRUNC (SYSDATE)
3045 )
3046 THEN
3047 l_evaluation_date :=
3048 assignment_last_eff_dt (p_person_id => p_resource_id);
3049 ELSE
3050 l_evaluation_date := TRUNC (p_evaluation_date);
3051 END IF;
3052
3053 if g_debug then
3054 hr_utility.set_location (
3055 ' Returning evaluation_date ='
3056 || l_evaluation_date,
3057 20
3058 );
3059 hr_utility.set_location ( 'Leaving:'
3060 || l_proc, 100);
3061 end if;
3062 RETURN l_evaluation_date;
3063 END evaluation_date;
3064
3065 -- Procedure for Bulk preference evaluation. Calculates the preference for a set of resource_ids.
3066 -- Its a single date evaluation and does not consider responsibility or login based preferences.
3067
3068 procedure resource_prefs_bulk (p_evaluation_date in date,
3069 p_pref_table IN OUT NOCOPY t_pref_table,
3070 p_resource_pref_table IN OUT NOCOPY t_resource_pref_table,
3071 p_resource_sql in varchar2 )
3072 is
3073
3074
3075 l_current_resource_id number;
3076 l_matches number;
3077
3078 l_pref_sets_index_table t_pref_sets_index_table;
3079 l_pref_sets_index_table_idx number;
3080
3081 l_pref_sets_table t_pref_sets_table;
3082 l_pref_sets_table_idx number;
3083
3084 l_flat_table t_pref_sets_table;
3085 l_flat_table_idx number;
3086
3087 l_resource_elig_table t_resource_elig_table;
3088 l_resource_elig_table_idx number;
3089
3090
3091 l_pref_table_idx number;
3092
3093 l_eval_pref_table hxc_preference_evaluation.t_pref_table;
3094 l_eval_pref_table_idx number;
3095
3096 l_set_start number;
3097 l_set_stop number;
3098 l_result_start number;
3099 l_result_stop number;
3100
3101 l_index number;
3102
3103 l_sql_statement varchar2(32000);
3104 TYPE PrefCurTyp IS REF CURSOR;
3105
3106 prefs_cv PrefCurTyp;
3107
3108
3109 begin
3110
3111 -- Bug 7484448
3112 -- Added USE_NL in the below query for optimum perf.
3113 l_sql_statement := ' SELECT /*+ USE_NL(PA HRR) */
3114 pa.person_id as criteria_id,
3115 hrr.pref_hierarchy_id,
3116 hrr.rule_evaluation_order
3117 FROM hxc_resource_rules hrr,
3118 per_all_assignments_f pa
3119 WHERE pa.person_id '||p_resource_sql||'
3120 AND nvl(hrr.business_group_id,pa.business_group_id) = pa.business_group_id
3121 AND pa.primary_flag =''Y''
3122 AND pa.assignment_type in (''E'',''C'')
3123 AND :evaluation_date
3124 BETWEEN pa.effective_start_date AND pa.effective_END_date
3125 AND :evaluation_date between hrr.start_date and hrr.end_date
3126 AND (( to_char(pa.assignment_id) = hrr.eligibility_criteria_id
3127 AND hrr.eligibility_criteria_type = ''ASSIGNMENT'')
3128 OR ( to_char(pa.payroll_id) = hrr.eligibility_criteria_id
3129 AND hrr.eligibility_criteria_type = ''PAYROLL'')
3130 OR ( to_char(pa.person_id) = hrr.eligibility_criteria_id
3131 AND hrr.eligibility_criteria_type = ''PERSON'')
3132 OR ( pa.EMPLOYEE_CATEGORY = hrr.eligibility_criteria_id
3133 AND hrr.eligibility_criteria_type = ''EMP_CATEGORY'')
3134 OR ( pa.EMPLOYMENT_CATEGORY = hrr.eligibility_criteria_id
3135 AND hrr.eligibility_criteria_type = ''ASGN_CATEGORY'')
3136 OR ( to_char(pa.location_id) = hrr.eligibility_criteria_id
3137 AND hrr.eligibility_criteria_type = ''LOCATION'')
3138 OR ( to_char(pa.organization_id) = hrr.eligibility_criteria_id
3139 AND hrr.eligibility_criteria_type = ''ORGANIZATION'')
3140 OR ((HXC_PREFERENCE_EVALUATION.check_number(substr(hrr.eligibility_criteria_id,instr(hrr.eligibility_criteria_id,''-'',1,1)+1)) in
3141 (SELECT pose.organization_id_parent
3142 FROM
3143 per_org_structure_elements pose
3144 start with organization_id_child = pa.organization_id
3145 and pose.org_structure_version_id=
3146 HXC_PREFERENCE_EVALUATION.return_version_id(HXC_PREFERENCE_EVALUATION.check_number(substr(hrr.eligibility_criteria_id,1,instr(hrr.eligibility_criteria_id,''-'',1,1)-1)),
3147 hrr.eligibility_criteria_type)
3148 connect by prior organization_id_parent=organization_id_child
3149 and pose.org_structure_version_id=
3150 HXC_PREFERENCE_EVALUATION.return_version_id(HXC_PREFERENCE_EVALUATION.check_number(substr(hrr.eligibility_criteria_id,1,instr(hrr.eligibility_criteria_id,''-'',1,1)-1)),
3151 hrr.eligibility_criteria_type)
3152 union
3153 select organization_id
3154 from hr_all_organization_units
3155 where organization_id = pa.organization_id))
3156 AND hrr.eligibility_criteria_type = ''ROLLUP'' )
3157 OR ( HXC_PREFERENCE_EVALUATION.check_number(hrr.eligibility_criteria_id)
3158 in ( SELECT typ.person_type_id
3159 FROM per_person_types typ
3160 ,per_person_type_usages_f ptu
3161 WHERE typ.system_person_type IN (''EMP'',''EX_EMP'',''EMP_APL'',''EX_EMP_APL'',''CWK'',''EX_CWK'')
3162 AND typ.person_type_id = ptu.person_type_id
3163 AND :evaluation_date BETWEEN ptu.effective_start_date AND ptu.effective_end_date
3164 AND pa.effective_start_date <= ptu.effective_end_date
3165 AND pa.effective_end_date >=ptu.effective_start_date
3166 AND ptu.person_id = pa.person_id)
3167 AND hrr.eligibility_criteria_type = ''PERSON_TYPE'')
3168 OR (
3169 hrr.eligibility_criteria_type = ''ALL_PEOPLE''))'|| 'union '||
3170 ' SELECT pa.person_id as criteria_id,hrr.pref_hierarchy_id, hrr.rule_evaluation_order
3171 FROM hxc_resource_rules hrr,
3172 per_all_assignments_f pa
3173 WHERE pa.person_id '||p_resource_sql||'
3174 AND nvl(hrr.business_group_id,pa.business_group_id) = pa.business_group_id
3175 AND pa.primary_flag = ''Y''
3176 AND pa.assignment_type in (''E'',''C'')
3177 AND :evaluation_date
3178 BETWEEN pa.effective_start_date AND pa.effective_END_date
3179 AND :evaluation_date between hrr.start_date and hrr.end_date
3180 AND ( (DECODE ( SUBSTR( hrr.eligibility_criteria_type, 1, 3 ),
3181 ''SCL'', DECODE ( pa.soft_coding_keyflex_id, NULL, -1,
3182 hxc_resource_rules_utils.chk_flex_valid (''SCL'', pa.soft_coding_keyflex_id,
3183 SUBSTR( hrr.eligibility_criteria_type, 5 ),
3184 hrr.eligibility_criteria_id )), -1 ) = 1 )
3185 OR (DECODE ( SUBSTR( hrr.eligibility_criteria_type, 1, 6 ),
3186 ''PEOPLE'', DECODE ( pa.people_group_id, NULL, -1,
3187 hxc_resource_rules_utils.chk_flex_valid ( ''PEOPLE'', pa.people_group_id,
3188 SUBSTR( hrr.eligibility_criteria_type, 8 ),
3189 hrr.eligibility_criteria_id )), -1 ) = 1 )
3190 OR (DECODE ( SUBSTR( hrr.eligibility_criteria_type, 1, 5 ),
3191 ''GRADE'', DECODE ( pa.grade_id, NULL, -1,
3192 hxc_resource_rules_utils.chk_flex_valid ( ''GRADE'', pa.grade_id,
3193 SUBSTR( hrr.eligibility_criteria_type, 7 ),
3194 hrr.eligibility_criteria_id )), -1 ) = 1 )
3195 OR (DECODE ( SUBSTR( hrr.eligibility_criteria_type, 1, 3 ),
3196 ''JOB'', hrr.eligibility_criteria_id, -1 ) = to_char(pa.job_id))-- Issue 4
3197 OR (DECODE ( SUBSTR( hrr.eligibility_criteria_type, 1, 8 ),
3198 ''POSITION'', hrr.eligibility_criteria_id, -1 ) = to_char(pa.position_id)) -- Issue 4
3199 )
3200 order by criteria_id';
3201
3202
3203 l_resource_elig_table_idx := 0;
3204 -- Cache the Rules of all the resource_ids
3205 OPEN prefs_cv FOR l_sql_statement using p_evaluation_date,p_evaluation_date,p_evaluation_date,p_evaluation_date,p_evaluation_date;
3206
3207 loop
3208 FETCH prefs_cv INTO l_resource_elig_table(l_resource_elig_table_idx);
3209 l_resource_elig_table_idx := l_resource_elig_table_idx + 1;
3210
3211 EXIT WHEN prefs_cv%NOTFOUND;
3212 end loop;
3213
3214
3215 l_resource_elig_table_idx := l_resource_elig_table.first;
3216 l_flat_table_idx := 0;
3217
3218 while (l_resource_elig_table_idx is not null)
3219 loop
3220
3221 l_current_resource_id := l_resource_elig_table(l_resource_elig_table_idx).criteria_id;
3222
3223 -- populate the flat table;
3224 l_flat_table(l_flat_table_idx).pref_hier_id := l_resource_elig_table(l_resource_elig_table_idx).pref_hier_id;
3225 l_flat_table(l_flat_table_idx).reo := l_resource_elig_table(l_resource_elig_table_idx).reo;
3226 l_flat_table_idx := l_flat_table_idx + 1;
3227
3228 if(l_resource_elig_table.next(l_resource_elig_table_idx) is not null and
3229 l_resource_elig_table(l_resource_elig_table.next(l_resource_elig_table_idx)).criteria_id = l_current_resource_id) then
3230
3231 -- If there are still rules associated to this resource, then fetch them into flat table before processing
3232 l_resource_elig_table_idx := l_resource_elig_table.next(l_resource_elig_table_idx);
3233
3234 -- The flat table now has all the rules associated to the current resource_id. Hence process.
3235 else
3236
3237 l_matches := 0;
3238
3239 l_pref_sets_index_table_idx := l_pref_sets_index_table.first;
3240 while (l_pref_sets_index_table_idx is not null)
3241 loop
3242
3243 l_flat_table_idx := l_flat_table.first;
3244
3245 for l_index in l_pref_sets_index_table(l_pref_sets_index_table_idx).set_start .. l_pref_sets_index_table(l_pref_sets_index_table_idx).set_stop
3246 loop
3247 -- if all entries in the flat table match sets-table but number of entries in flat and sets table is diff, then
3248
3249 if (l_flat_table_idx is null) then
3250 -- Flat table no data or is exhausted
3251 l_matches := 0;
3252 exit;
3253 end if;
3254 l_matches := 1;
3255 if (l_pref_sets_table(l_index).pref_hier_id <> l_flat_table(l_flat_table_idx).pref_hier_id or
3256 l_pref_sets_table(l_index).reo <> l_flat_table(l_flat_table_idx).reo) then
3257 -- No match with the current set. Check the next set.
3258
3259 l_matches := 0;
3260 exit;
3261
3262 end if;
3263 l_flat_table_idx := l_flat_table.next(l_flat_table_idx);
3264
3265 end loop;
3266
3267
3268 -- Case where match found for avaliable entries..but number of entries not same
3269 if ((l_index is null and l_flat_table_idx is not null) or (l_index is not null and l_flat_table_idx is null)) then
3270 l_matches := 0;
3271 end if;
3272
3273 if (l_matches = 1) then
3274 -- Found the matching set and hence the preference values
3275 -- Update the Out table p_resource_pref_table
3276
3277 p_resource_pref_table(l_current_resource_id).start_index := l_pref_sets_index_table(l_pref_sets_index_table_idx).result_start;
3278
3279 p_resource_pref_table(l_current_resource_id).stop_index := l_pref_sets_index_table(l_pref_sets_index_table_idx).result_stop;
3280
3281 exit;
3282 end if;
3283
3284 l_pref_sets_index_table_idx := l_pref_sets_index_table.next(l_pref_sets_index_table_idx);
3285 end loop;
3286
3287 -- if no match found, then need to evaluate preferences.
3288 if (l_matches = 0) then
3289 -- Call preference evaluation proc
3290 hxc_preference_evaluation.resource_preferences(
3291 p_resource_id =>l_current_resource_id,
3292 p_evaluation_date =>p_evaluation_date,
3293 p_pref_table =>l_eval_pref_table,
3294 p_ignore_user_id => true,
3295 p_ignore_resp_id => true);
3296
3297 -- 1. Add the Pref-REO set of this resource into l_pref_sets table
3298 l_flat_table_idx := l_flat_table.first;
3299 l_pref_sets_table_idx := l_pref_sets_table.last + 1;
3300 if (l_pref_sets_table.last is null) then
3301 l_pref_sets_table_idx := 0;
3302 end if;
3303
3304 l_set_start := l_pref_sets_table_idx;
3305 while(l_flat_table_idx is not null)
3306 loop
3307 l_pref_sets_table(l_pref_sets_table_idx).pref_hier_id := l_flat_table(l_flat_table_idx).pref_hier_id;
3308
3309 l_pref_sets_table(l_pref_sets_table_idx).reo := l_flat_table(l_flat_table_idx).reo;
3310
3311 l_pref_sets_table_idx := l_pref_sets_table_idx + 1;
3312 l_flat_table_idx := l_flat_table.next(l_flat_table_idx);
3313 end loop;
3314 l_set_stop := l_pref_sets_table_idx - 1;
3315
3316 -- 2. Add Preference Values into Out table p_pref_table
3317 l_eval_pref_table_idx := l_eval_pref_table.first;
3318 l_pref_table_idx := p_pref_table.last + 1;
3319 if (l_pref_table_idx is null) then
3320 l_pref_table_idx := 0;
3321 end if;
3322
3323 l_result_start := l_pref_table_idx;
3324 while (l_eval_pref_table_idx is not null)
3325 loop
3326 p_pref_table(l_pref_table_idx) := l_eval_pref_table(l_eval_pref_table_idx);
3327
3328 l_eval_pref_table_idx := l_eval_pref_table.next(l_eval_pref_table_idx);
3329 l_pref_table_idx := l_pref_table_idx + 1;
3330 end loop;
3331 l_result_stop := l_pref_table_idx - 1;
3332
3333 -- 3. Update the sets_index table
3334 l_pref_sets_index_table_idx := l_pref_sets_index_table.last + 1;
3335 if (l_pref_sets_index_table_idx is null) then
3336 l_pref_sets_index_table_idx := 0;
3337 end if;
3338 l_pref_sets_index_table(l_pref_sets_index_table_idx).set_start := l_set_start;
3339 l_pref_sets_index_table(l_pref_sets_index_table_idx).set_stop := l_set_stop;
3340 l_pref_sets_index_table(l_pref_sets_index_table_idx).result_start := l_result_start;
3341 l_pref_sets_index_table(l_pref_sets_index_table_idx).result_stop := l_result_stop;
3342
3343 -- 4. Update the Out table p_resource_pref_table
3344 p_resource_pref_table(l_current_resource_id).start_index := l_result_start;
3345 p_resource_pref_table(l_current_resource_id).stop_index := l_result_stop;
3346 end if;
3347
3348
3349 -- The current resource has been processed. So delete the l_flat_table for fresh data to be populated.
3350
3351 l_flat_table.delete;
3352 l_flat_table_idx := 0;
3353
3354 -- Fetch data for the next resource
3355 l_resource_elig_table_idx := l_resource_elig_table.next(l_resource_elig_table_idx);
3356 end if;
3357 end loop;
3358
3359 end resource_prefs_bulk;
3360
3361 function return_version_id
3362 (p_criteria hxc_resource_rules.eligibility_criteria_id%TYPE,
3363 p_eligibility_type hxc_resource_rules.eligibility_criteria_type%TYPE)
3364 return number is
3365
3366 cursor c_version_id(p_number number) is
3367 select org_structure_version_id
3368 from per_org_structure_versions
3369 where organization_structure_id = p_number
3370 and trunc(sysdate) between nvl(date_from,trunc(sysdate)) and
3371 nvl(date_to,sysdate);
3372
3373 l_version_id number;
3374 l_number number;
3375
3376 begin
3377
3378 if (p_eligibility_type = 'ROLLUP') then
3379
3380 l_number:=to_number(p_criteria);
3381 --Caching logic implemented to improve the performance. If the output for
3382 --given input value exists in global table and not older than 30 second,
3383 --then return the output from global table else hit back the query
3384 --against DB to fetch the output and store the value in global table
3385 --for next reference,,if required-.
3386
3387 if(g_str_version.exists(l_number)
3388 and (sysdate-g_str_version(l_number).time_info)*24*60*60<30) then
3389 return g_str_version(l_number).org_version_id;
3390 end if;
3391
3392
3393 open c_version_id(l_number);
3394 fetch c_version_id into l_version_id;
3395 if(c_version_id %found) then
3396 close c_version_id ;
3397 g_str_version(l_number).org_version_id:=l_version_id;
3398 g_str_version(l_number).time_info:=sysdate;
3399 return l_version_id;
3400 else
3401 close c_version_id ;
3402 g_str_version(l_number).org_version_id:=null;
3403 g_str_version(l_number).time_info:=sysdate;
3404 return null;
3405 end if;
3406
3407 else
3408 return null;
3409 end if;
3410
3411 end return_version_id;
3412 --==================================================================
3413 FUNCTION get_tc_resp ( p_resource_id NUMBER,
3414 p_evaluation_date DATE)
3415 RETURN NUMBER
3416 IS
3417 cursor get_resp_id(p_resource_id IN NUMBER, p_evaluation_date IN DATE) is
3418 SELECT ta.attribute4 , ta.attribute3
3419 from HXC_TIME_ATTRIBUTES ta, HXC_TIME_ATTRIBUTE_USAGES tau, hxc_latest_details ld, hxc_timecard_summary tbd
3420 where ta.time_attribute_id = tau.time_attribute_id
3421 and tau.time_building_block_id = ld.time_building_block_id
3422 and tau.time_building_block_ovn = ld.object_version_number
3423 and ld.resource_id = p_resource_id
3424 and p_evaluation_date between trunc(tbd.START_TIME) and trunc(tbd.STOP_TIME)
3425 and tbd.resource_id = p_resource_id
3426 and trunc(ld.start_time) <= trunc(tbd.STOP_TIME)
3427 and trunc(ld.stop_time) >= trunc(tbd.start_time)
3428 and ta.attribute_category = 'SECURITY'
3429 --and tbd.scope = 'TIMECARD'
3430 order by ld.last_update_date DESC;
3431 resp_id VARCHAR2(100);
3432 l_tc_user_id VARCHAR2(150);
3433 l_tc_employee_id NUMBER;
3434 CURSOR get_employee_id(p_user_id IN Number) Is
3435 Select employee_id from fnd_user
3436 Where user_id = p_user_id;
3437 BEGIN
3438 g_debug := hr_utility.debug_enabled;
3439 if g_debug then
3440 hr_utility.set_location ('Starting get_tc_resp' , 10 );
3441 end if;
3442 OPEN get_resp_id (p_resource_id,p_evaluation_date);
3443 FETCH get_resp_id into resp_id, l_tc_user_id;
3444 close get_resp_id;
3445 open get_employee_id(l_tc_user_id);
3446 Fetch get_employee_id into l_tc_employee_id;
3447 Close get_employee_id;
3448 IF p_resource_id = l_tc_employee_id then
3449 if g_debug then
3450 hr_utility.set_location ('Returning resp_id =' || resp_id, 20 );
3451 hr_utility.set_location ('Returning l_tc_employee_id =' || l_tc_employee_id, 20 );
3452 end if;
3453 return to_number(resp_id);
3454 ELSE
3455 hr_utility.set_location ('Returning resp_id = -101' , 20 );
3456 return(-101);
3457 END IF;
3458 if g_debug then
3459 hr_utility.set_location ('Stopping get_tc_resp' , 30 );
3460 end if;
3461 EXCEPTION
3462 WHEN NO_DATA_FOUND THEN
3463 hr_utility.set_location ('Resp_id not found in Security attribute ' || resp_id || 'for resource ' || p_resource_id , 40 );
3464 RETURN(-101);
3465 WHEN INVALID_NUMBER THEN
3466 hr_utility.set_location ('Invalid Number Value found in resp_id ' || resp_id || 'for resource ' || p_resource_id , 40 );
3467 RETURN(-101);
3468 END get_tc_resp;
3469
3470
3471
3472
3473 --================================================================================
3474
3475 PROCEDURE get_tc_resp ( p_resource_id IN NUMBER,
3476 p_start_evaluation_date IN DATE,
3477 p_end_evaluation_date IN DATE,
3478 p_resp_id OUT NOCOPY NUMBER,
3479 p_resp_appl_id OUT NOCOPY NUMBER)
3480 IS
3481
3482
3483
3484 cursor get_latest_detail_bbid (p_resource_id IN NUMBER, p_start_evaluation_date IN DATE, p_end_evaluation_date DATE) is
3485 SELECT ld.time_building_block_id, ld.object_version_number
3486 from hxc_latest_details ld
3487 WHERE ld.resource_id = p_resource_id
3488 and trunc(ld.start_time) <= trunc(p_end_evaluation_date)
3489 and trunc(ld.stop_time) >= trunc(p_start_evaluation_date)
3490 order by ld.last_update_date DESC;
3491
3492 cursor get_resp_id (detail_building_block_id IN NUMBER, detail_builiding_block_ovn IN NUMBER) IS
3493 SELECT ta.attribute4, ta.attribute5, ta.attribute3
3494 FROM HXC_TIME_ATTRIBUTES ta, HXC_TIME_ATTRIBUTE_USAGES tau
3495 WHERE ta.attribute_category = 'SECURITY'
3496 and ta.time_attribute_id = tau.time_attribute_id
3497 and tau.time_building_block_id = detail_building_block_id
3498 and tau.time_building_block_ovn = detail_builiding_block_ovn;
3499
3500
3501
3502 resp_id VARCHAR2(100);
3503 resp_appl_id VARCHAR2(100);
3504 l_tc_user_id VARCHAR2(150);
3505 l_tc_employee_id NUMBER;
3506 CURSOR get_employee_id(p_user_id IN Number) Is
3507 Select employee_id from fnd_user
3508 Where user_id = p_user_id;
3509
3510
3511
3512 Cursor get_tc_start_stop( p_resource_id IN NUMBER, l_detail_building_block_id IN NUMBER, detail_builiding_block_ovn IN NUMBER) is
3513 select START_TIME, STOP_TIME, DATE_TO from hxc_time_building_blocks t
3514 where scope = 'TIMECARD' AND
3515 resource_id =p_resource_id
3516 connect by prior parent_building_block_id = time_building_block_id
3517 and prior parent_building_block_ovn = object_version_number
3518 start with time_building_block_id = l_detail_building_block_id
3519 and object_version_number = detail_builiding_block_ovn
3520 order by time_building_block_id asc, object_version_number desc;
3521
3522 l_detail_building_block_id NUMBER;
3523 l_detail_building_block_ovn NUMBER;
3524 l_tc_start_time DATE;
3525 l_tc_stop_time DATE;
3526 l_tc_date_to DATE;
3527
3528
3529 BEGIN
3530 g_debug := hr_utility.debug_enabled;
3531 if g_debug then
3532 hr_utility.set_location ('Starting get_tc_resp' , 40 );
3533 end if;
3534
3535
3536
3537 OPEN get_latest_detail_bbid (p_resource_id,p_start_evaluation_date,p_end_evaluation_date );
3538 FETCH get_latest_detail_bbid into l_detail_building_block_id, l_detail_building_block_ovn ;
3539 close get_latest_detail_bbid;
3540
3541 /*OPEN get_resp_id (p_resource_id,p_start_evaluation_date,p_end_evaluation_date );
3542 FETCH get_resp_id into resp_id, resp_appl_id, l_tc_user_id,l_detail_building_block_id, l_detail_building_block_ovn ;
3543 close get_resp_id;*/
3544
3545
3546
3547
3548 OPEN get_tc_start_stop(p_resource_id, l_detail_building_block_id, l_detail_building_block_ovn);
3549 FETCH get_tc_start_stop INTO l_tc_start_time, l_tc_stop_time,l_tc_date_to;
3550 CLOSE get_tc_start_stop;
3551
3552 IF trunc(l_tc_start_time) = trunc(p_start_evaluation_date)
3553 AND trunc(l_tc_stop_time) = trunc(p_end_evaluation_date)
3554 AND trunc(l_tc_date_to) = trunc(hr_general.end_of_time) THEN
3555
3556
3557 OPEN get_resp_id (l_detail_building_block_id, l_detail_building_block_ovn );
3558 FETCH get_resp_id into resp_id, resp_appl_id, l_tc_user_id;
3559 close get_resp_id;
3560
3561
3562 open get_employee_id(l_tc_user_id);
3563 Fetch get_employee_id into l_tc_employee_id;
3564 Close get_employee_id;
3565
3566
3567 IF p_resource_id = l_tc_employee_id then
3568
3569 --return to_number(resp_id);
3570 p_resp_id := to_number(resp_id);
3571 p_resp_appl_id := to_number(resp_appl_id);
3572
3573
3574 if g_debug then
3575 hr_utility.set_location ('Returning resp_id =' || resp_id, 50 );
3576 hr_utility.set_location ('Returning resp_appl_id =' || resp_appl_id, 50 );
3577 end if;
3578
3579 ELSE
3580 p_resp_id := -101;
3581 p_resp_appl_id := to_number(fnd_global.resp_appl_id);
3582 if g_debug then
3583 hr_utility.set_location ('Returning resp_id =' || p_resp_id, 55 );
3584 hr_utility.set_location ('Returning resp_appl_id =' || p_resp_appl_id, 55 );
3585 end if;
3586 END IF;
3587 ELSE
3588
3589
3590 p_resp_id := to_number(fnd_global.resp_id);
3591 p_resp_appl_id := to_number(fnd_global.resp_appl_id);
3592
3593 if g_debug then
3594 hr_utility.set_location ('10 Returning resp_id =' || p_resp_id, 58 );
3595 hr_utility.set_location ('10 Returning resp_appl_id =' || p_resp_appl_id, 58 );
3596 end if;
3597
3598 END IF;
3599
3600
3601 if g_debug then
3602 hr_utility.set_location ('Stopping get_tc_resp' , 60 );
3603 end if;
3604 EXCEPTION
3605 WHEN NO_DATA_FOUND THEN
3606 p_resp_id := -101;
3607 p_resp_appl_id := to_number(fnd_global.resp_appl_id);
3608 WHEN INVALID_NUMBER THEN
3609 p_resp_id := -101;
3610 p_resp_appl_id := to_number(fnd_global.resp_appl_id);
3611 hr_utility.set_location ('Invalid Number Value found in resp_id ' || resp_id || 'for resource ' || p_resource_id , 70 );
3612 END get_tc_resp;
3613
3614
3615
3616
3617
3618 -- PROCEDURE get_tc_resp
3619 -- Returns a PL/SQL table structure holding the timecard start and
3620 -- stop dates and the last touched responsibility for that timecard.
3621 -- Overloaded from get_tc_resp used above, but this version is used
3622 -- for actual persistent responsibility evaluation.
3623
3624
3625 PROCEDURE get_tc_resp ( p_resource_id IN NUMBER,
3626 p_start_evaluation_date IN DATE,
3627 p_end_evaluation_date IN DATE,
3628 p_resplist OUT NOCOPY resplisttab )
3629 IS
3630
3631
3632
3633 -- The below cursor would return all the timecard start, stop
3634 -- dates within the given range. The WHERE clause looks at
3635 -- the passed in resource_id, and start and end evaluation
3636 -- dates. Since we anyway have to do this, why not pull out
3637 -- the last touched time building block id and ovn ?
3638 -- The RANK function will partition based on resource_id,
3639 -- start_time and stop_time, meaning for a logical timecard
3640 -- structure for a resource, it will rank the available records
3641 -- based on these -- and we identify one valid timecard per
3642 -- resource_id-start_time-stop_time combination in the system.
3643 -- The Rank is determined ordering by date_to, tbb_id, and ovn
3644 -- This means that the first record out there would be the
3645 -- latest timecard, hence the outer query is looking at only
3646 -- the first rank.
3647 -- Since the rank is partitioned on resource_id, start_time
3648 -- and stop_time, we would get only one record even if there
3649 -- are multiple deletions and resubmissions for the same
3650 -- time periods -- we always get the latest one.
3651
3652
3653
3654 CURSOR get_time_periods ( p_resource_id NUMBER,
3655 p_start_evaluation_date DATE,
3656 p_end_evaluation_date DATE )
3657 IS SELECT timecard_id,
3658 timecard_ovn,
3659 start_time,
3660 stop_time,
3661 date_to
3662 FROM ( SELECT time_building_block_id timecard_id,
3663 object_version_number timecard_ovn,
3664 start_time,
3665 stop_time,
3666 date_to,
3667 RANK() OVER ( PARTITION BY resource_id,
3668 start_time,
3669 stop_time
3670 ORDER BY date_to DESC,
3671 time_building_block_id DESC,
3672 object_version_number DESC ) rank
3673 FROM hxc_time_building_blocks
3674 WHERE resource_id = p_resource_id
3675 AND scope = 'TIMECARD'
3676 AND TRUNC(start_time) BETWEEN p_start_evaluation_date
3677 AND p_end_evaluation_date
3678 AND TRUNC(stop_time) BETWEEN p_start_evaluation_date
3679 AND p_end_evaluation_date
3680 )
3681 WHERE rank = 1;
3682
3683
3684 -- This cursor pulls out the SECURITY attribute values
3685 -- for the given time_building_block_id and OVN.
3686
3687
3688 CURSOR get_tc_resp_id ( p_timecard_id NUMBER,
3689 p_timecard_ovn NUMBER )
3690 IS SELECT ha.attribute4,
3691 ha.attribute5,
3692 ha.attribute3
3693 FROM hxc_time_attribute_usages hau,
3694 hxc_time_attributes ha
3695 WHERE hau.time_building_block_id = p_timecard_id
3696 AND hau.time_building_block_ovn = p_timecard_ovn
3697 AND ha.time_attribute_id = hau.time_attribute_id
3698 AND attribute_category = 'SECURITY' ;
3699
3700
3701 -- This cursor would pull out SECURITY attributes for
3702 -- the latest updated detail from hxc_latest_details.
3703 -- We pick only one record from the below cursor, the first
3704 -- one when its ordered Descending based on last_update_date.
3705
3706 -- Bug 12700547
3707 -- Added hints.
3708
3709 CURSOR get_tc_det_resp_id ( p_resource_id NUMBER,
3710 p_start_date DATE,
3711 p_stop_date DATE )
3712 IS SELECT /*+ LEADING(hld)
3713 INDEX(hld hxc_lastest_details_n3) */
3714 ha.attribute4,
3715 ha.attribute5,
3716 ha.attribute3
3717 FROM hxc_time_attribute_usages hau,
3718 hxc_time_attributes ha,
3719 hxc_latest_details hld
3720 WHERE hld.resource_id = p_resource_id
3721 AND TRUNC(hld.start_time) >= p_start_date
3722 AND TRUNC(hld.stop_time) <= TRUNC(p_stop_date)
3723 AND hau.time_building_block_id = hld.time_building_block_id
3724 AND hau.time_building_block_ovn = hld.object_version_number
3725 AND hau.time_attribute_id = ha.time_attribute_id
3726 AND attribute_category = 'SECURITY'
3727 ORDER BY hld.last_update_date DESC ;
3728
3729
3730 CURSOR get_user_person_id ( p_user_id NUMBER )
3731 IS SELECT employee_id
3732 FROM fnd_user
3733 WHERE user_id = p_user_id;
3734
3735 TYPE time_periodsrec IS RECORD
3736 ( timecard_id NUMBER,
3737 timecard_ovn NUMBER,
3738 start_time DATE,
3739 stop_time DATE,
3740 date_to DATE ) ;
3741
3742 TYPE time_periodstab IS TABLE OF time_periodsrec ;
3743
3744 time_periods time_periodstab;
3745
3746 l_resp_id NUMBER;
3747 l_user_id NUMBER;
3748 l_resp_appln_id NUMBER;
3749 l_resource_id NUMBER;
3750
3751 resplist resplisttab := resplisttab () ;
3752
3753 cnt NUMBER;
3754
3755
3756
3757
3758 BEGIN
3759 -- Public Procedure get_tc_resp
3760 -- Takes in the resource_id for whom the process is executed
3761 -- and the evaluation start and end dates.
3762 -- Get the valid time periods from hxc_time_building_blocks
3763 -- table falling within the given start and end dates for
3764 -- evaluation. Need to consider only those periods which
3765 -- fall within the given range, not the ones crossing over
3766 -- the boundaries, because Persistent responsibility can
3767 -- be evaluated only when the whole timecard is looked at.
3768 -- While the time periods ( timecard start dates and stop dates
3769 -- are picked up, also pick up the latest OVNs and tbb ids
3770 -- for each tc start- stop times.
3771 -- If the last touched upon timecard record is deleted, there
3772 -- is no point in looking at latest details, rather look
3773 -- into the timecard record's SECURITY attributes.
3774 -- If the last touched upon timecard is still live look at
3775 -- HXC_LATEST_DETAILS table to find out who touched the
3776 -- timecard last and get the SECURITY attribute.
3777 -- Get the resource_id attached to the user.
3778 -- If its the same resource_id as the parameter of this procedure
3779 -- it means its the employee himself, and we need his
3780 -- persistent responsibility preferences. Record the fetched
3781 -- responsibility ids and periods into the data structure.
3782 -- If its somebody else, it is a Time Keeper or a Line Manager
3783 -- or an Authorized Delegate, never mind his preferences;
3784 -- put down -1 for responsibilities.
3785 -- Repeat the above steps for all the valid time periods
3786 -- ( timecard start-stop dates in the given date range)
3787 --
3788
3789
3790
3791 IF g_debug
3792 THEN
3793 hr_utility.trace ('get_tc_resp');
3794 END IF;
3795
3796 OPEN get_time_periods ( p_resource_id => p_resource_id,
3797 p_start_evaluation_date => p_start_evaluation_date,
3798 p_end_evaluation_date => p_end_evaluation_date ) ;
3799
3800 FETCH get_time_periods
3801 BULK COLLECT INTO time_periods ;
3802
3803 CLOSE get_time_periods;
3804
3805 cnt := 0;
3806
3807 IF time_periods.COUNT > 0
3808 THEN
3809 FOR i IN time_periods.FIRST..time_periods.LAST
3810 LOOP
3811
3812 -- For all the available time periods and tbb_ids and OVNs
3813 -- last updated for those time periods, check if the last
3814 -- updation was a deletion -- meaning there would not be
3815 -- end of time in the date_to column. For these timecards,
3816 -- neednt go to details, just get the responsibility which
3817 -- deleted the timecard.
3818
3819 IF time_periods(i).date_to <> hr_general.end_of_time
3820 THEN
3821 OPEN get_tc_resp_id( time_periods(i).timecard_id,
3822 time_periods(i).timecard_ovn );
3823 FETCH get_tc_resp_id
3824 INTO l_resp_id,
3825 l_resp_appln_id,
3826 l_user_id ;
3827 CLOSE get_tc_resp_id;
3828
3829
3830 -- For others, you have to go to the details and find
3831 -- out which one was last modified. You take only one
3832 -- record -- meaning, even if there are multiple records
3833 -- out there, we take only the last one for each timecard.
3834
3835 ELSE
3836 OPEN get_tc_det_resp_id ( p_resource_id,
3837 time_periods(i).start_time,
3838 time_periods(i).stop_time) ;
3839 FETCH get_tc_det_resp_id
3840 INTO l_resp_id,
3841 l_resp_appln_id,
3842 l_user_id ;
3843
3844 CLOSE get_tc_det_resp_id;
3845 END IF;
3846
3847 OPEN get_user_person_id( l_user_id );
3848
3849 FETCH get_user_person_id
3850 INTO l_resource_id ;
3851
3852 CLOSE get_user_person_id ;
3853
3854
3855
3856 resplist.EXTEND(1);
3857 cnt := cnt + 1;
3858
3859 -- Only if the person who touched the timecard last is
3860 -- the same person to whom the timecard belongs to, need
3861 -- the responsibility be recorded. Else record -1.
3862 IF l_resource_id = p_resource_id
3863 THEN
3864 resplist(cnt).resp_id := l_resp_id;
3865 resplist(cnt).start_date := time_periods(i).start_time ;
3866 resplist(cnt).stop_date := time_periods(i).stop_time;
3867 ELSE
3868 resplist(cnt).resp_id := -1 ;
3869 resplist(cnt).start_date := time_periods(i).start_time ;
3870 resplist(cnt).stop_date := time_periods(i).stop_time;
3871 END IF;
3872
3873 l_resp_id := NULL;
3874 l_user_id := NULL;
3875 l_resp_appln_id := NULL;
3876
3877 END LOOP;
3878
3879 END IF;
3880
3881
3882 -- Return the responsibility table to resource_preferences.
3883 p_resplist := resplist;
3884
3885
3886
3887 END get_tc_resp;
3888
3889
3890
3891
3892
3893
3894
3895 FUNCTION resource_preferences(p_resource_id IN NUMBER,
3896 p_pref_code IN VARCHAR2,
3897 p_attribute_n IN NUMBER,
3898 p_resp_id IN NUMBER )
3899 RETURN VARCHAR2 IS
3900 l_pref_table t_pref_table;
3901 l_table_mask t_requested_pref_list;
3902 l_first NUMBER;
3903 BEGIN
3904 RETURN resource_preferences(p_resource_id,p_pref_code,p_attribute_n,SYSDATE,p_resp_id);
3905 END resource_preferences;
3906 --===============================================
3907
3908 END hxc_preference_evaluation;
3909
[Home] [Help]