1 PACKAGE BODY HR_UTIL_MISC_SS AS
2 /* $Header: hrutlmss.pkb 120.17.12010000.1 2008/07/28 03:57:24 appldev ship $ */
3
4 g_package VARCHAR2(30) := 'HR_UTIL_MISC_SS.';
5 g_debug boolean := hr_utility.debug_enabled;
6
7 PROCEDURE initLoginPrsnCtx(p_eff_date IN DATE) IS
8 l_proc constant varchar2(100) := g_package || ' initLoginPrsnCtx';
9 CURSOR c_bg IS
10 SELECT business_group_id, nvl(org_information10,'USD') currency_code
11 FROM per_people_f ppf, hr_organization_information oi
12 WHERE ppf.person_id = fnd_global.employee_id
13 AND ppf.business_group_id = oi.organization_id
14 AND oi.org_information_context = 'Business Group Information'
15 AND g_eff_date between ppf.effective_start_date and ppf.effective_end_date;
16 BEGIN
17 hr_utility.set_location('Entering: '|| l_proc,5);
18 OPEN c_bg;
19 hr_utility.trace('Going into Fetch after ( OPEN c_bg ): '|| l_proc);
20 FETCH c_bg INTO g_loginPrsnBGId, g_loginPrsnCurrencyCode;
21 CLOSE c_bg;
22 g_rate_type := hr_currency_pkg.get_rate_type (
23 p_business_group_id => g_loginPrsnBGId
24 ,p_conversion_date => p_eff_date
25 ,p_processing_type => 'R');
26
27 hr_utility.set_location('Leaving: '|| l_proc,15);
28 END initLoginPrsnCtx;
29
30 /*
31 Currency conversion function to return converted amount as a number.
32 Input params - from amount, from currency code, conversion date,
33 override(to) currecny (if override currency is not passed, preferred currency is used).
34 */
35 FUNCTION get_in_preferred_currency_num(
36 p_amount IN NUMBER
37 ,p_from_currency IN VARCHAR2
38 ,p_eff_Date IN DATE DEFAULT trunc(sysdate)
39 ,p_override_currency IN VARCHAR2 default fnd_profile.value('ICX_PREFERRED_CURRENCY')
40 ) RETURN NUMBER IS
41 l_to_currency VARCHAR2(10);
42 l_return NUMBER;
43 BEGIN
44 if(p_amount is null) then
45 return null;
46 end if;
47 if(p_from_currency is null) then
48 return p_amount;
49 end if;
50 /* Populate local variables, all these functions internally cache values*/
51 if(p_override_currency is null or p_override_currency = 'ANY') then
52 l_to_currency := p_from_currency;
53 else
54 l_to_currency := p_override_currency;
55 end if;
56 l_return := hr_currency_pkg.convert_amount(
57 p_from_currency
58 ,l_to_currency
59 ,p_eff_date
60 ,p_amount
61 ,g_rate_type);
62 /* hr_currency_pkg.convert_amount returns negative value in case of invalid currency/date combination.
63 Return the original value in such a case*/
64 if(l_return>=0) then
65 return l_return;
66 else
67 return p_amount;
68 end if;
69 Exception When Others then
70 return p_amount;
71 END get_in_preferred_currency_num;
72
73 /*
74 Currency conversion function to return the currency code into which
75 get_in_preferred_currency would convert to.
76 Input params - from currency code, conversion date,
77 override (to) currecny (if override currency is not passed, preferred currency is used).
78 */
79 FUNCTION get_preferred_currency(
80 p_from_currency IN VARCHAR2
81 ,p_eff_Date IN DATE DEFAULT trunc(sysdate)
82 ,p_override_currency IN VARCHAR2 default fnd_profile.value('ICX_PREFERRED_CURRENCY')
83 ) RETURN VARCHAR2 IS
84 l_to_currency VARCHAR2(10);
85 l_return NUMBER;
86 BEGIN
87 /* Populate local variables, all these functions internally cache values*/
88 if(p_from_currency is null ) then
89 return null;
90 end if;
91 if(p_override_currency is null or p_override_currency = 'ANY') then
92 l_to_currency := p_from_currency;
93 else
94 l_to_currency := p_override_currency;
95 end if;
96 l_return := hr_currency_pkg.convert_amount(
97 p_from_currency
98 ,l_to_currency
99 ,p_eff_date
100 ,10
101 ,g_rate_type);
102 /* hr_currency_pkg.convert_amount returns negative value in case of invalid currency/date combination.
103 Return the original value in such a case*/
104 if(l_return>=0) then
105 return l_to_currency;
106 else
107 return p_from_currency;
108 end if;
109 Exception When Others then
110 return p_from_currency;
111 END get_preferred_currency;
112
113 /*
114 Currency conversion function to return the converted amount concatenated with the currency as a string.
115 Input params - from amount, from currency code, conversion date,
116 override (to) currecny (if override currency is not passed, preferred currency is used).
117 */
118
119 FUNCTION get_in_preferred_currency_str(
120 p_amount IN NUMBER
121 ,p_from_currency IN VARCHAR2
122 ,p_eff_Date IN DATE DEFAULT trunc(sysdate)
123 ,p_override_currency IN VARCHAR2 default fnd_profile.value('ICX_PREFERRED_CURRENCY')
124 ) RETURN VARCHAR2 IS
125 l_to_currency VARCHAR2(10);
126 l_converted_amount NUMBER;
127 BEGIN
128 if(p_amount is null) then
129 return null;
130 end if;
131 if(p_from_currency is null) then
132 return p_amount;
133 end if;
134 /* Populate local variables, all these functions internally cache values*/
135 if(p_override_currency is null or p_override_currency = 'ANY') then
136 l_to_currency := p_from_currency;
137 else
138 l_to_currency := p_override_currency;
139 end if;
140 if(l_to_currency=p_from_currency) then
141 return to_char(p_amount,
142 fnd_currency.get_format_mask(p_from_currency,25))
143 || ' ' || p_from_currency;
144 end if;
145 l_converted_amount := hr_currency_pkg.convert_amount(
146 p_from_currency
147 ,l_to_currency
148 ,p_eff_Date
149 ,p_amount
150 ,g_rate_type);
151 /* hr_currency_pkg.convert_amount returns negative value in case of invalid currency/date combination.
152 Return the original value in such a case*/
153 if(l_converted_amount>=0) then
154 fnd_message.set_name('PER','HR_MULTI_CURR_FMT');
155 fnd_message.set_token('FROM_AMT',to_char(p_amount,
156 fnd_currency.get_format_mask(p_from_currency,25)),false);
157 fnd_message.set_token('FROM_CURR',p_from_currency,false);
158 fnd_message.set_token('TO_AMT',to_char(l_converted_amount,
159 fnd_currency.get_format_mask(l_to_currency,25)),false);
160 fnd_message.set_token('TO_CURR',l_to_currency,false);
161 return (fnd_message.get);
162 else
163 fnd_message.set_name('PER','HR_MULTI_CURR_FROM_FMT');
164 fnd_message.set_token('FROM_AMT',to_char(p_amount,
165 fnd_currency.get_format_mask(p_from_currency,25)),false);
166 fnd_message.set_token('FROM_CURR',p_from_currency,false);
167 return (fnd_message.get);
168 end if;
169 Exception When Others then
170 fnd_message.set_name('PER','HR_MULTI_CURR_FROM_FMT');
171 fnd_message.set_token('FROM_AMT',to_char(p_amount,
172 fnd_currency.get_format_mask(p_from_currency,25)),false);
173 fnd_message.set_token('FROM_CURR',p_from_currency,false);
174 return (fnd_message.get);
175 END get_in_preferred_currency_str;
176
177 FUNCTION getCompSourceInfo (
178 p_competence_id IN NUMBER
179 ,p_person_id IN NUMBER
180 ) RETURN VARCHAR2 IS
181
182 CURSOR c_srcInfo (p_cid IN NUMBER, p_pid IN NUMBER) IS
183 SELECT 4 rank, hr_general.decode_lookup('STRUCTURE_TYPE','POS')||'#'||proficiency_level_id||'#'||high_proficiency_level_id src
184 FROM per_competence_elements ce, per_all_assignments_f paf
185 WHERE ce.type = 'REQUIREMENT'
186 AND trunc(sysdate) between nvl(ce.effective_date_from, sysdate) and nvl(ce.effective_date_to, sysdate)
187 AND ce.position_id = paf.position_id
188 AND paf.primary_flag = 'Y'
189 AND paf.assignment_type in ('E', 'C')
190 AND trunc(sysdate) between paf.effective_start_date and paf.effective_end_date
191 AND (ce.proficiency_level_id is not null or ce.high_proficiency_level_id is not null)
192 AND ce.competence_id = p_cid
193 AND paf.person_id = p_pid
194 UNION ALL
195 SELECT 3 rank, hr_general.decode_lookup('STRUCTURE_TYPE','JOB')||'#'||proficiency_level_id||'#'||high_proficiency_level_id src
196 FROM per_competence_elements ce, per_all_assignments_f paf
197 WHERE ce.type = 'REQUIREMENT'
198 AND trunc(sysdate) between nvl(ce.effective_date_from, sysdate) and nvl(ce.effective_date_to, sysdate)
199 AND ce.job_id = paf.job_id
200 AND paf.primary_flag = 'Y'
201 AND paf.assignment_type in ('E', 'C')
202 AND trunc(sysdate) between paf.effective_start_date and paf.effective_end_date
203 AND (ce.proficiency_level_id is not null or ce.high_proficiency_level_id is not null)
204 AND ce.competence_id = p_cid
205 AND paf.person_id = p_pid
206 UNION ALL
207 SELECT 2 rank, hr_general.decode_lookup('STRUCTURE_TYPE','ORG')||'#'||proficiency_level_id||'#'||high_proficiency_level_id src
208 FROM per_competence_elements ce, per_all_assignments_f paf
209 WHERE ce.type = 'REQUIREMENT'
210 AND trunc(sysdate) between ce.effective_date_from and nvl(ce.effective_date_to, sysdate)
211 AND ce.organization_id = paf.organization_id
212 AND paf.primary_flag = 'Y'
213 AND paf.assignment_type in ('E', 'C')
214 AND trunc(sysdate) between paf.effective_start_date and paf.effective_end_date
215 AND (ce.proficiency_level_id is not null or ce.high_proficiency_level_id is not null)
216 AND ce.competence_id = p_cid
217 AND paf.person_id = p_pid
218 UNION ALL
219 SELECT 1 rank, hr_general.decode_lookup('STRUCTURE_TYPE','BUS')||'#'||proficiency_level_id||'#'||high_proficiency_level_id src
220 FROM per_competence_elements ce, per_all_assignments_f paf
221 WHERE ce.type = 'REQUIREMENT'
222 AND trunc(sysdate) between ce.effective_date_from and nvl(ce.effective_date_to, sysdate)
223 AND ce.enterprise_id = paf.business_group_id
224 AND paf.primary_flag = 'Y'
225 AND paf.assignment_type in ('E', 'C')
226 AND trunc(sysdate) between paf.effective_start_date and paf.effective_end_date
227 AND (ce.proficiency_level_id is not null or ce.high_proficiency_level_id is not null)
228 AND ce.competence_id = p_cid
229 AND paf.person_id = p_pid
230 UNION ALL
231 SELECT 0 rank, hr_general.decode_lookup('STRUCTURE_TYPE','ADD')||'##' src
232 FROM dual
233 ORDER BY RANK DESC;
234
235 BEGIN
236 For I in c_srcInfo(p_competence_id, p_person_id) Loop
237 return I.src;
238 End Loop;
239 END getCompSourceInfo;
240
241 /**
242 * Wrapper function that calls fnd_data_security.check_function and
243 * check_cwk_access
244 */
245 FUNCTION validate_selected_function (
246 p_api_version IN NUMBER
247 ,p_function IN VARCHAR2
248 ,p_object_name IN VARCHAR2
249 ,p_person_id IN VARCHAR2 -- p_instance_pk1_value
250 ,p_instance_pk2_value IN VARCHAR2
251 ,p_user_name IN VARCHAR2
252 ,p_eff_date IN DATE
253 )
254 RETURN VARCHAR2
255 IS
256 l_proc varchar2(72) := g_package||' validate_selected_function';
257 l_status VARCHAR2(10):='F';
258 l_asg_security VARCHAR2(10) := hr_general2.supervisor_assignments_in_use;
259 BEGIN
260
261 IF g_debug then
262 hr_utility.set_location('Entering: '|| l_proc, 5);
263 END IF;
264
265 IF (l_asg_security = 'TRUE' AND p_person_id <> '-1' ) THEN
266 l_status := check_term_access(
267 p_function => p_function,
268 p_person_id => TO_NUMBER(p_person_id),
269 p_eff_date => p_eff_date);
270 ELSE
271 l_status := 'T';
272 END IF;
273
274
275 IF (l_status = 'T' AND p_person_id <> '-1' ) THEN
276 hr_utility.trace('In ( IF (l_status = T AND p_person_id <> -1 ) ): '|| l_proc);
277 IF g_debug then
278 hr_utility.set_location('Entering: '|| 'fnd_data_security.check_function', 10);
279 END IF;
280
281 l_status := fnd_data_security.check_function(
282 p_api_version => p_api_version
283 ,p_function => p_function
284 ,p_object_name => p_object_name
285 ,p_instance_pk1_value => p_person_id
286 ,p_instance_pk2_value => p_instance_pk2_value);
287 IF l_status = 'E' OR l_status = 'U' THEN
288 hr_utility.trace('Error in fnd_data_security.check_function l_status is: '||l_status||'. Error is: '|| replace(fnd_message.get_encoded(), chr(0), ' '));
289 l_status := 'F';
290 END IF;
291
292 IF g_debug then
293 hr_utility.set_location('Leaving: '|| 'fnd_data_security.check_function', 15);
294 END IF;
295 END IF;
296
297 IF (l_status = 'T' AND p_person_id <> '-1' ) THEN
298 l_status := check_cwk_access(
299 p_function => p_function,
300 p_person_id => TO_NUMBER(p_person_id),
301 p_eff_date => p_eff_date);
302 END IF;
303
304 IF g_debug then
305 hr_utility.set_location('Leaving: '||l_proc, 20);
306 END IF;
307
308 RETURN l_status;
309 END validate_selected_function;
310
311 /**
312 * This function is used to return the named user id
313 */
314 function get_person_id return number is
315 --
316 cursor get_sec_person_id(p_security_profile_id number) is
317 select named_person_id
318 from per_security_profiles
319 where security_profile_id=p_security_profile_id;
320 --
321 cursor get_user_person_id(p_user_id number) is
322 select employee_id
323 from fnd_user
324 where user_id=p_user_id;
325 --
326 l_person_id number;
327 --
328 begin
329 --
330 open get_sec_person_id(hr_security.get_security_profile);
331 fetch get_sec_person_id into l_person_id;
332 close get_sec_person_id;
333 if l_person_id is null then
334 open get_user_person_id(fnd_global.user_id);
335 fetch get_user_person_id into l_person_id;
336 close get_user_person_id;
337 end if;
338 --
339 return l_person_id;
340 --
341 end get_person_id;
342
343 /**
344 * This function is used to extract the parameter value from the parameter
345 * list. The return values and their meanings are :
346 * 1) ERROR - This parameter is not in this list or Exception raised
347 * 2) NULL - Value of this parameter is null
348 * 3) The value of the parameter itself
349 */
350 FUNCTION get_parameter_value (
351 p_parameter_list IN VARCHAR2
352 ,p_parameter IN VARCHAR2
353 )
354 RETURN VARCHAR2
355 IS
356 l_smarker INTEGER;
357 l_emarker INTEGER;
358 l_proc constant varchar2(100) := g_package || ' get_parameter_value';
359 BEGIN
360 hr_utility.set_location('Entering: '|| l_proc,5);
361 l_smarker := instr(p_parameter_list, p_parameter);
362
363 if (l_smarker = 0) THEN
364 hr_utility.set_location('Leaving: '|| l_proc,10);
365 RETURN NULL;
366
367 END IF;
368
369 l_smarker := l_smarker + LENGTH(p_parameter)+1;
370 l_emarker := INSTR(p_parameter_list, '&', l_smarker);
371
372 IF (l_emarker <= 0) THEN
373 l_emarker := LENGTH(p_parameter_list)+1;
374 END IF;
375 hr_utility.set_location('Leaving: '|| l_proc,15);
376
377 RETURN SUBSTR(p_parameter_list, l_smarker, l_emarker - l_smarker);
378 EXCEPTION
379 WHEN OTHERS THEN
380 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
381 RETURN NULL;
382 END get_parameter_value;
383
384
385
386 /**
387 * Checks for the Term access of the function returns a N or T
388 */
389 FUNCTION check_term_access (
390 p_function IN VARCHAR2
391 ,p_person_id IN NUMBER
392 ,p_eff_date IN DATE
393 )
394 RETURN VARCHAR2
395 IS
396 l_proc varchar2(72) := g_package||' check_term_access';
397 l_status VARCHAR2(10):= 'N';
398 l_item_type hr_api_transactions.item_type%type;
399 l_temp VARCHAR2(100);
400 l_function_name fnd_form_functions.function_name%Type;
401
402 -- Local Cursors
403 CURSOR csr_fnd_func_details (p_func_name VARCHAR2)IS
404 SELECT function_id, parameters, web_html_call
405 FROM fnd_form_functions fff
406 WHERE fff.function_name = p_func_name;
407
408
409 CURSOR csr_wf_process (p_wfpname VARCHAR2, p_item_type VARCHAR2) IS
410 SELECT 'N' status
411 FROM WF_PROCESS_ACTIVITIES pa1, WF_PROCESS_ACTIVITIES pa2,
412 WF_ACTIVITIES a1, WF_ACTIVITIES a2, WF_ACTIVITY_ATTR_VALUES aav
413 WHERE pa1.process_item_type = p_item_type
414 and pa1.process_name = p_wfpname
415 and pa1.activity_name = pa2.process_name
416 and a1.name = pa1.process_name
417 and pa1.process_version = a1.version
418 and a1.item_type = p_item_type
419 and sysdate between a1.begin_date and nvl(a1.end_date,sysdate)
420 and pa2.process_item_type = p_item_type
421 and pa2.process_name = a2.name
422 and pa2.process_version = a2.version
423 and a2.item_type = p_item_type
424 and sysdate between a2.begin_date and nvl(a2.end_date,sysdate)
425 and pa2.instance_id = aav.process_activity_id
426 and aav.name = 'HR_ACTIVITY_TYPE_VALUE'
427 and aav.text_value IN ('HR_TERMINATION_TOP_SS','HR_CWK_TERMINATION_PAGE_SS')
428 UNION
429 SELECT 'N' status
430 FROM WF_ACTIVITIES a, WF_PROCESS_ACTIVITIES pa,
431 WF_ACTIVITY_ATTR_VALUES aav
432 where a.item_type = p_item_type
433 and a.name = p_wfpname
434 and sysdate between a.begin_date and nvl(a.end_date,sysdate)
435 and pa.process_item_type = a.item_type
436 and pa.process_name = a.name
437 and pa.process_version = a.version
438 and pa.instance_id = aav.process_activity_id
439 and aav.name = 'HR_ACTIVITY_TYPE_VALUE'
440 and aav.text_value IN ('HR_TERMINATION_TOP_SS','HR_CWK_TERMINATION_PAGE_SS');
441
442 l_func_details csr_fnd_func_details%ROWTYPE;
443 BEGIN
444 IF g_debug then
445 hr_utility.set_location('Entering: '|| l_proc, 5);
446 END IF;
447
448 OPEN csr_fnd_func_details (p_function);
449 hr_utility.trace('Going into Fetch after (OPEN csr_fnd_func_details (p_function)): '|| l_proc);
450 FETCH csr_fnd_func_details INTO l_func_details;
451 CLOSE csr_fnd_func_details;
452 -- Overriding p_function value depending on pCalledFrom value
453 l_function_name := nvl(get_parameter_value(l_func_details.parameters,
454 'pCalledFrom'),p_function);
455
456 IF l_function_name <> p_function THEN
457 OPEN csr_fnd_func_details (l_function_name);
458 hr_utility.trace('Going into Fetch after (OPEN csr_fnd_func_details (l_function_name)): '|| l_proc);
459 FETCH csr_fnd_func_details INTO l_func_details;
460 CLOSE csr_fnd_func_details;
461 END IF;
462
463 -- Checks whether this function is workflow based
464 IF (INSTR(l_func_details.parameters, 'pProcessName') <> 0) THEN
465 hr_utility.trace('In( IF (INSTR(l_func_details.parameters, pProcessName) <> 0)): '|| l_proc);
466 l_temp := get_parameter_value(l_func_details.parameters,
467 'pProcessName');
468
469 IF (l_temp is not null) THEN
470 hr_utility.trace('In( IF (l_temp is not null)): '|| l_proc);
471 l_item_type := get_parameter_value(l_func_details.parameters,
472 'pItemType');
473 OPEN csr_wf_process (l_temp, l_item_type);
474 hr_utility.trace('Going into Fetch after( OPEN csr_wf_process (l_temp, l_item_type))): '|| l_proc);
475 FETCH csr_wf_process INTO l_status;
476 IF csr_wf_process%NOTFOUND THEN
477 l_status := 'T';
478 ELSE
479 l_status := check_primary_access(p_person_id, p_eff_date);
480 END IF;
481 CLOSE csr_wf_process;
482
483 END IF;
484 ELSE
485 hr_utility.trace('In else of (IF (INSTR(l_func_details.parameters, pProcessName) <> 0)): '|| l_proc);
486 l_status := 'T';
487 END IF;
488
489 IF g_debug then
490 hr_utility.set_location('Leaving: '||l_proc, 30);
491 END IF;
492
493 RETURN l_status; -- if status is N then it is no term access
494
495 EXCEPTION
496 WHEN OTHERS THEN
497 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
498 RETURN 'N';
499 END check_term_access;
500
501 /**
502 * Checks for the Primary access of the function returns a N or T
503 */
504
505 FUNCTION check_primary_access (
506 p_selected_person_id NUMBER,
507 p_effective_date DATE)
508 RETURN VARCHAR2 IS
509
510
511 cursor fetch_asg (l_person_id NUMBER, l_effective_date DATE) is
512 SELECT 'T'
513 FROM per_assignments_f2 paf, per_assignment_status_types past
514 WHERE paf.person_id = l_person_id
515 AND l_effective_date between paf.effective_start_date and paf.effective_end_date
516 AND paf.primary_flag = 'Y'
517 AND paf.assignment_type IN ('E', 'C')
518 AND paf.assignment_status_type_id = past.assignment_status_type_id
519 AND past.per_system_status NOT IN ('TERM_ASSIGN','END');
520
521 l_status VARCHAR2(1) := 'N';
522
523 l_proc varchar2(72) := g_package||'check_primary_access';
524 BEGIN
525 IF g_debug then
526 hr_utility.set_location('Entering: '|| l_proc, 5);
527 END IF;
528
529 OPEN fetch_asg (p_selected_person_id, p_effective_date);
530 hr_utility.trace('Going into Fetch after (OPEN fetch_asg (p_selected_person_id, p_effective_date) ): '|| l_proc);
531 FETCH fetch_asg INTO l_status;
532
533 IF fetch_asg%NOTFOUND THEN
534 l_status := 'N';
535 END IF;
536
537 CLOSE fetch_asg;
538
539 IF g_debug then
540 hr_utility.set_location('Leaving: '||l_proc, 10);
541 END IF;
542
543 RETURN l_status;
544
545 END check_primary_access;
546
547
548 /**
549 * This function checks whether the akregion code is in the
550 * CWK exclusion list. If Yes the return 'C' else 'T'
551 */
552 FUNCTION check_akregion_code (
553 p_ak_region IN VARCHAR2
554 )
555 RETURN VARCHAR2
556 IS
557 l_status VARCHAR2(10):='C';
558 --local variables
559 l_proc constant varchar2(100) := g_package || ' check_akregion_code';
560 -- CWK Phase III Changes,
561 -- Following functions are being made available for CWK
562 -- 'HR_ASSIGNMENT_TOP_SS',
563 -- 'HR_WORK_SCHED_TOP_SS',
564 -- 'HR_MANAGER_TOP_SS',
565 -- 'HR_P_RATE_TOP_SS',
566 -- 'HR_TERMINATION_TOP_SS',
567 -- 'HR_ASSIGNMENT_TOP_SS',
568 -- 'HR_NEWHIRE_PERSON_TOP_SS',
569 -- 'HR_CAED_TOP_SS'
570 CURSOR csr_akregion_list IS
571 SELECT 'C' FROM dual
572 WHERE UPPER(p_ak_region) IN (
573 'HR_CCMGR_OVERVIEW_TOP_SS',
574 'PQH_ACADEMIC_RANK_TOP',
575 'PQH_ACADEMIC_RANK_OVRVW_TOP',
576 'PQH_TENURE_STATUS_OVRVW_TOP',
577 'PQH_TENURE_STATUS_TOP',
578 'PQH_REVIEW_FIND_TOP',
579 --'OTA_TRAINING_TOP_SS',
580 --'OTA_ADDTRNG_OVERVIEW_TOP_SS',
581 --'PQH_REVIEWS_TOP',
582 --'PQH_EVENTS_MGR_SEARCH_TOP',
583 'HR_LOA_SUMMARY_TOP_SS'
584 );
585
586 BEGIN
587 hr_utility.set_location('Entering: '|| l_proc,5);
588 OPEN csr_akregion_list;
589 hr_utility.trace('Going into Fetch after ( OPEN csr_akregion_list ): '|| l_proc);
590 FETCH csr_akregion_list INTO l_status;
591 IF csr_akregion_list%NOTFOUND
592 THEN
593 l_status := 'T';
594 END IF;
595 CLOSE csr_akregion_list;
596
597 RETURN l_status;
598 hr_utility.set_location('Leaving: '|| l_proc,15);
599 END check_akregion_code;
600
601 /**
602 * Checks for the CWK access of the function returns a C or T
603 */
604
605 FUNCTION check_cwk_access (
606 p_function IN VARCHAR2
607 ,p_person_id IN NUMBER
608 ,p_eff_date IN DATE
609 )
610 RETURN VARCHAR2
611 IS
612 l_proc varchar2(72) := g_package||'check_cwk_access';
613 l_status VARCHAR2(10):= 'C';
614 l_npw_status VARCHAR2(30);
615 l_item_type hr_api_transactions.item_type%type;
616 l_temp VARCHAR2(100);
617 l_function_name fnd_form_functions.function_name%Type;
618
619 -- Local Cursors
620 CURSOR csr_per_npw_flag IS
621 SELECT nvl(current_npw_flag,'N')
622 FROM per_all_people_f per
623 WHERE per.person_id = p_person_id
624 AND p_eff_date BETWEEN per.effective_start_date AND per.effective_end_date;
625
626 CURSOR csr_fnd_func_details (p_func_name VARCHAR2)IS
627 SELECT function_id, parameters, web_html_call
628 FROM fnd_form_functions fff
629 WHERE fff.function_name = p_func_name;
630
631 CURSOR csr_menu_entries (p_menu_name VARCHAR2, p_func_id NUMBER) IS
632 select 'T' from fnd_menus m, fnd_menu_entries me
633 where menu_name = p_menu_name
634 and m.menu_id = me.menu_id
635 and me.function_id = p_func_id;
636
637 CURSOR csr_wf_process (p_wfpname VARCHAR2, p_item_type VARCHAR2) IS
638 SELECT 'C' status
639 FROM WF_ACTIVITIES a, WF_PROCESS_ACTIVITIES pa,
640 WF_ACTIVITY_ATTR_VALUES aav
641 where a.item_type = p_item_type
642 and a.name = p_wfpname
643 and sysdate between a.begin_date and nvl(a.end_date,sysdate)
644 and pa.process_item_type = a.item_type
645 and pa.process_name = a.name
646 and pa.process_version = a.version
647 and pa.instance_id = aav.process_activity_id
648 and aav.name = 'HR_ACTIVITY_TYPE_VALUE'
649 and 'C' = hr_util_misc_ss.check_akregion_code(text_value);
650
651 l_func_details csr_fnd_func_details%ROWTYPE;
652 BEGIN
653 IF g_debug then
654 hr_utility.set_location('Entering: '|| l_proc, 5);
655 END IF;
656
657 OPEN csr_per_npw_flag;
658 hr_utility.trace('Going into Fetch after (OPEN csr_per_npw_flag ): '|| l_proc);
659 FETCH csr_per_npw_flag INTO l_npw_status;
660 IF csr_per_npw_flag%NOTFOUND THEN
661 hr_utility.set_message(800,'PER_52097_APL_INV_PERSON_ID');
662 hr_utility.raise_error;
663 END IF;
664 CLOSE csr_per_npw_flag;
665 IF (l_npw_status <> 'Y') THEN
666 hr_utility.set_location('Leaving: '|| l_proc,15);
667 RETURN 'T';
668 END IF;
669
670 OPEN csr_fnd_func_details (p_function);
671 hr_utility.trace('Going into Fetch after ( OPEN csr_fnd_func_details (p_function)): '|| l_proc);
672 FETCH csr_fnd_func_details INTO l_func_details;
673 CLOSE csr_fnd_func_details;
674 -- Overriding p_function value depending on pCalledFrom value
675 l_function_name := nvl(get_parameter_value(l_func_details.parameters,
676 'pCalledFrom'),p_function);
677 IF l_function_name <> p_function THEN
678 OPEN csr_fnd_func_details (l_function_name);
679 hr_utility.trace('Going into Fetch after (OPEN csr_fnd_func_details (l_function_name)): '|| l_proc);
680 FETCH csr_fnd_func_details INTO l_func_details;
681 CLOSE csr_fnd_func_details;
682 END IF;
683
684 -- Checks whether this function is workflow based
685 IF (INSTR(l_func_details.parameters, 'pProcessName') <> 0) THEN
686 hr_utility.trace('In( IF (INSTR(l_func_details.parameters, pProcessName) <> 0) '|| l_proc);
687 l_temp := get_parameter_value(l_func_details.parameters,
688 'pProcessName');
689 IF (l_temp is not null) THEN
690 l_item_type := get_parameter_value(l_func_details.parameters,
691 'pItemType');
692 OPEN csr_wf_process (l_temp, l_item_type);
693 hr_utility.trace('Going into Fetch after (OPEN csr_wf_process (l_temp, l_item_type)): '|| l_proc);
694 FETCH csr_wf_process INTO l_status;
695 IF csr_wf_process%NOTFOUND THEN
696 l_status := 'T';
697 END IF;
698 CLOSE csr_wf_process;
699
700 END IF;
701
702 ELSE
703 hr_utility.trace('In else of ( IF (INSTR(l_func_details.parameters, pProcessName) <> 0) '|| l_proc);
704
705 l_temp := nvl(get_parameter_value(l_func_details.web_html_call,
706 'akRegionCode'),
707 get_parameter_value(l_func_details.web_html_call,
708 'OAFunc')
709 );
710 l_status := check_akregion_code(l_temp);
711 END IF;
712
713 IF g_debug then
714 hr_utility.set_location('Leaving: '||l_proc, 40);
715 END IF;
716
717 RETURN l_status;
718 EXCEPTION
719 WHEN OTHERS THEN
720
721 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
722 RETURN 'C';
723 END check_cwk_access;
724
725 PROCEDURE clear_cache
726 IS
727 --local variable
728 l_proc constant varchar2(100) := g_package || ' clear_cache';
729 BEGIN
730 hr_utility.set_location('Entering: '|| l_proc,5);
731 g_entity_list.delete;
732 -- g_entitydetail_list.delete;
733 hr_utility.set_location('Leaving: '|| l_proc,10);
734 END clear_cache;
735
736 FUNCTION entity_exists (
737 p_entity_id IN NUMBER
738 )
739 RETURN VARCHAR2
740 IS
741 --local variable
742 l_proc constant varchar2(100) := g_package || ' entity_exists';
743 BEGIN
744 hr_utility.set_location('Entering: '|| l_proc,5);
745 IF g_entity_list.exists(p_entity_id) THEN
746 hr_utility.set_location('Leaving: '|| l_proc,10);
747 RETURN 'T';
748 END IF;
749 hr_utility.set_location('Leaving: '|| l_proc,15);
750 RETURN 'F';
751 EXCEPTION
752 WHEN others THEN
753 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
754 RETURN 'F';
755 END entity_exists;
756
757 PROCEDURE populate_entity_list (
758 p_elist IN HR_MISC_SS_NUMBER_TABLE,
759 p_retain_cache IN VARCHAR2
760 )
761 IS
762 --local variable
763 l_proc constant varchar2(100) := g_package || ' populate_entity_list';
764 BEGIN
765 hr_utility.set_location('Entering: '|| l_proc,5);
766 if p_retain_cache = 'N'
767 then
768 clear_cache();
769 end if;
770 FOR I IN 1 ..p_elist.count LOOP
771 g_entity_list(p_elist(i)) := p_elist(i);
772 END LOOP;
773 hr_utility.set_location('Leaving: '|| l_proc,10);
774 END populate_entity_list;
775
776 PROCEDURE check_ota_installed (appl_id number, status out nocopy varchar2) is
777 l_status VARCHAR2(1);
778 l_industry VARCHAR2(10);
779 l_flag boolean;
780 --local variable
781 l_proc constant varchar2(100) := g_package || ' check_ota_installed';
782 begin
783 hr_utility.set_location('Entering: '|| l_proc,5);
784 l_flag := fnd_installation.get(appl_id => appl_id,
785 dep_appl_id => appl_id,
786 status => l_status,
787 industry => l_industry );
788
789 if l_status = 'I' then
790 status := 'Y';
791 else
792 status := 'N';
793 end if;
794 hr_utility.set_location('Leaving: '|| l_proc,10);
795 end check_ota_installed;
796
797 FUNCTION get_employee_salary(
798 p_assignment_id IN NUMBER
799 ,P_Effective_Date IN date
800 ) RETURN NUMBER IS
801 ln_proposed_salary NUMBER;
802 lv_frequency VARCHAR2(100);
803 ln_annual_salary NUMBER;
804 lv_pay_basis_name VARCHAR2(100);
805 lv_reason_cd VARCHAR2(100);
806 ln_currency VARCHAR2(100);
807 ln_status NUMBER;
808 lv_pay_basis_frequency per_pay_bases.pay_basis%TYPE;
809 begin
810 pqh_employee_salary.get_employee_salary(
811 P_Assignment_id => p_assignment_id,
812 P_Effective_Date => p_effective_date,
813 p_salary => ln_proposed_salary,
814 p_frequency => lv_frequency,
815 p_annual_salary => ln_annual_salary,
816 p_pay_basis => lv_pay_basis_name,
817 p_reason_cd => lv_reason_cd,
818 p_currency => ln_currency,
819 p_status => ln_status,
820 p_pay_basis_frequency => lv_pay_basis_frequency);
821
822 return ln_annual_salary;
823 end get_employee_salary;
824
825 PROCEDURE is_employee_check (
826 itemtype in varchar2,
827 itemkey in varchar2,
828 actid in number,
829 funcmode in varchar2,
830 resultout out nocopy varchar2
831 )
832 IS
833 l_text_value wf_activity_attr_values.text_value%type;
834 --local variable
835 l_proc constant varchar2(100) := g_package || ' is_employee_check';
836 BEGIN
837 hr_utility.set_location('Entering: '|| l_proc,5);
838 --
839 l_text_value := wf_engine.GetItemAttrText (
840 itemtype => itemtype,
841 itemkey => itemkey,
842 aname => 'HR_SELECTED_PERSON_TYPE_ATTR');
843 if l_text_value = 'C'
844 then
845 resultout := 'COMPLETE:'|| 'N';
846 else
847 resultout := 'COMPLETE:'|| 'Y';
848 end if;
849 --
850 hr_utility.set_location('Leaving: '|| l_proc,10);
851 EXCEPTION
852 WHEN OTHERS THEN
853
854 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
855 WF_CORE.CONTEXT (
856 g_package,
857 'branch_on_approval_flag',
858 itemtype,
859 itemkey,
860 to_char(actid),
861 funcmode);
862 RAISE;
863 END is_employee_check;
864
865 PROCEDURE populateInterimPersonList (
866 person_data PER_INTERIM_PERSON_LIST_STRUCT
867 )
868 IS
869 --local variable
870 l_proc constant varchar2(100) := g_package || ' populateInterimPersonList';
871 BEGIN
872 hr_utility.set_location('Entering: '|| l_proc,5);
873 -- This commit is issued here to remove the Data from the Temp table.
874 -- This can be replaced by truncate.
875 --COMMIT;
876 DELETE PER_INTERIM_PERSON_LIST;
877 FOR i in 1.. person_data.count LOOP
878 INSERT INTO PER_INTERIM_PERSON_LIST (person_id, assignment_id, in_my_list)
879 values (person_data(i).person_id, person_data(i).assignment_id, person_data(i).in_my_list);
880 END LOOP;
881 hr_utility.set_location('Leaving: '|| l_proc,10);
882 EXCEPTION
883 WHEN others THEN
884 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
885 RAISE;
886 END populateInterimPersonList;
887
888 PROCEDURE populateInterimListFromMyList (
889 person_id number
890 )
891 IS
892 --local variable
893 l_proc constant varchar2(100) := g_package || ' populateInterimListFromMyList';
894 BEGIN
895 hr_utility.set_location('Entering: '|| l_proc,5);
896 --COMMIT;
897 DELETE PER_INTERIM_PERSON_LIST;
898 INSERT INTO PER_INTERIM_PERSON_LIST (person_id, assignment_id, in_my_list)
899 SELECT selected_person_id, selected_assignment_id, 'Y'
900 FROM hr_working_person_lists
901 WHERE owning_person_id = person_id;
902 hr_utility.set_location('Leaving: '|| l_proc,10);
903 EXCEPTION
904 WHEN others THEN
905 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
906 RAISE;
907 END populateInterimListFromMyList;
908
909 PROCEDURE addToMyListFromInterimList (
910 prsn_id number
911 )
912 IS
913 --local variable
914 l_proc constant varchar2(100) := g_package || ' addToMyListFromInterimList';
915 BEGIN
916 hr_utility.set_location('Entering: '|| l_proc,5);
917 INSERT INTO HR_WORKING_PERSON_LISTS(working_person_list_id, owning_person_id,
918 selected_person_id, current_selection, multiple_selection, selected_assignment_id)
919 SELECT HR_WORKING_PERSON_LISTS_s.NEXTVAL,
920 prsn_id,
921 list.person_id,
922 NULL,
923 NULL,
924 list.assignment_id
925 FROM PER_INTERIM_PERSON_LIST list;
926 hr_utility.set_location('Leaving: '|| l_proc,10);
927 EXCEPTION
928 WHEN others THEN
929 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
930 RAISE;
931 END addToMyListFromInterimList;
932
933 PROCEDURE setEffectiveDate
934 (p_effective_date in date)
935 IS
936 --
937 PRAGMA AUTONOMOUS_TRANSACTION;
938 --
939 --local variable
940 l_proc constant varchar2(100) := g_package || ' setEffectiveDate';
941 BEGIN
942 hr_utility.set_location('Entering: '|| l_proc,5);
943 --
944 g_eff_date := trunc(p_effective_date);
945 --bug 5765957 start
946 begin
947 dt_fndate.set_effective_date(g_eff_date);
948 exception
949 when DUP_VAL_ON_INDEX then
950 hr_utility.set_location('change for DUP_VAL_ON_INDEX : ' || l_proc ,999);
951 when others then
952 hr_utility.set_location('change for others : ' || l_proc ,998);
953 end;
954 --bug 5765957 end
955 initLoginPrsnCtx(g_eff_date);
956 g_year_start := to_date('01/01/'||to_char(g_eff_date,'RRRR'),'DD/MM/RRRR');
957 --
958 commit;
959 --
960 hr_utility.set_location('Leaving: '|| l_proc,10);
961 EXCEPTION
962 WHEN others THEN
963 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
964 rollback;
965 raise;
966 END setEffectiveDate;
967
968 FUNCTION getObjectName(
969 p_object IN varchar2,
970 p_object_id IN number,
971 p_bg_id IN number,
972 p_value IN varchar2
973 )
974 return varchar2
975 IS
976 --local variable
977 l_proc constant varchar2(100) := g_package || ' getObjectName';
978 BEGIN
979 hr_utility.set_location('Entering: '|| l_proc,5);
980 IF PER_WORK_STRUCTURE_OVERRIDE_SS.isOverrideEnabled(p_object) THEN
981 return nvl(PER_WORK_STRUCTURE_OVERRIDE_SS.getObjectName(p_object
982 , p_object_id, p_bg_id, p_value) ,p_value);
983 END IF;
984 hr_utility.set_location('Leaving: '|| l_proc,10);
985 return p_value;
986 END getObjectName;
987
988 PROCEDURE initialize_am IS
989 l_proc varchar2(72) := g_package||'initialize_am';
990 BEGIN
991
992 -- If g_debug is only set at package level
993 -- logging will not work consistently
994 --
995 g_debug := hr_utility.debug_enabled;
996
997 IF g_debug then
998 hr_utility.set_location('Entering: '|| l_proc, 5);
999 END IF;
1000
1001
1002 -- 3952978
1003 --
1004 -- Mark HR Security cache as invalid. The next time a secure view
1005 -- is accessed - cache will be rebuilt. This is the equivalent of
1006 -- code calling fnd_global.apps_initialize but where the HR signon
1007 -- callback is not called - ie user/resp/sc context has not changed.
1008 --
1009
1010 if ( nvl(fnd_profile.value('HR_SEC_INIT_AM'),'N') = 'Y')
1011 then
1012 hr_signon.session_context := hr_signon.session_context + 1;
1013 end if;
1014
1015 END initialize_am ;
1016
1017 FUNCTION getEnableSecurityGroups RETURN VARCHAR2 IS
1018
1019 l_enableSecGroups varchar2(10);
1020 defined_z BOOLEAN;
1021
1022 BEGIN
1023
1024 l_enableSecGroups := nvl(fnd_profile.value('ENABLE_SECURITY_GROUPS'),'N');
1025
1026 IF (nvl(fnd_global.application_short_name,'#') <> 'PER' AND l_enableSecGroups <> 'Y') THEN
1027 fnd_profile.get_specific('ENABLE_SECURITY_GROUPS',NULL,NULL,'800',l_enableSecGroups,defined_z,NULL,NULL);
1028 IF (nvl(l_enableSecGroups,'N') <> nvl(fnd_profile.value('ENABLE_SECURITY_GROUPS'),'N')) THEN
1029 fnd_profile.put('ENABLE_SECURITY_GROUPS', l_enableSecGroups);
1030 END IF;
1031 END IF;
1032
1033 RETURN l_enableSecGroups;
1034
1035 EXCEPTION When Others then
1036 RETURN 'N';
1037 END getEnableSecurityGroups;
1038
1039 PROCEDURE SET_SYS_CTX (
1040 p_legCode in varchar2
1041 ,p_bgId in varchar2
1042 ) IS
1043
1044 l_secGrpId number;
1045 l_enableSecGrp varchar2(10);
1046 BEGIN
1047
1048 l_secGrpId := 0;
1049 l_enableSecGrp := getEnableSecurityGroups;
1050
1051 IF p_legCode IS NOT NULL THEN
1052 HR_API.SET_LEGISLATION_CONTEXT(p_legCode);
1053 END IF;
1054
1055
1056 IF (p_bgId IS NOT NULL AND l_enableSecGrp = 'Y' ) THEN
1057 BEGIN
1058
1059 select security_group_id into l_secGrpId
1060 from fnd_security_groups
1061 where security_group_key = p_bgId;
1062
1063 fnd_client_info.set_security_group_context(to_char(l_secGrpId));
1064 -- Fix for bug 5531282 , this reverts the earlier fix for bug 5084537
1065 --FND_GLOBAL.set_security_group_id_context(l_secGrpId);
1066
1067 EXCEPTION When Others then
1068 fnd_client_info.set_security_group_context(to_char(l_secGrpId));
1069 -- Fix for bug 5531282 , this reverts the earlier fix for bug 5084537
1070 -- FND_GLOBAL.set_security_group_id_context(l_secGrpId);
1071 END;
1072 END IF;
1073
1074 EXCEPTION
1075 WHEN others THEN
1076 raise;
1077 END SET_SYS_CTX;
1078
1079 PROCEDURE populateInterimEntityList (
1080 entity_data PER_INTERIM_ENTITY_LIST_STRUCT
1081 ,p_retain_cache IN VARCHAR2
1082 )
1083 IS
1084 --local variable
1085 l_proc constant varchar2(100) := g_package || ' populateInterimEntityList';
1086 BEGIN
1087 hr_utility.set_location('Entering: '|| l_proc,5);
1088 if p_retain_cache = 'N'
1089 then
1090 DELETE PER_INTERIM_ENTITY_LIST;
1091 end if;
1092 FOR i in 1.. entity_data.count LOOP
1093 INSERT INTO PER_INTERIM_ENTITY_LIST (entity_name,state,pk1,pk2,pk3,pk4,pk5)
1094 values (entity_data(i).entity_name, entity_data(i).state,entity_data(i).pk1,entity_data(i).pk2,entity_data(i).pk3,entity_data(i).pk4,entity_data(i).pk5);
1095 END LOOP;
1096 hr_utility.set_location('Leaving: '|| l_proc,10);
1097 EXCEPTION
1098 WHEN others THEN
1099 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
1100 RAISE;
1101 END populateInterimEntityList;
1102
1103 PROCEDURE clearInterimEntityList
1104 IS
1105 l_proc constant varchar2(100) := g_package || 'clearInterimEntityList';
1106 BEGIN
1107 hr_utility.set_location('Entering: '|| l_proc,5);
1108 DELETE PER_INTERIM_ENTITY_LIST;
1109 END clearInterimEntityList;
1110
1111 procedure isPersonTerminated (
1112 result out nocopy varchar2,
1113 p_person_id varchar2,
1114 p_assignment_id varchar2
1115 )
1116 is
1117 l_proc constant varchar2(100) := g_package || ' isPersonTerminated';
1118 assi_id varchar2(200) := null ;
1119
1120 begin
1121 hr_utility.set_location('Entering: '|| l_proc,5);
1122
1123 select assignment_id
1124 into assi_id
1125 from per_people_f ppf, per_assignments_f paf
1126 where paf.person_id = ppf.person_id
1127 and trunc(sysdate) between paf.effective_start_date(+) and paf.effective_end_date(+)
1128 and trunc(sysdate) between ppf.effective_start_date(+) and ppf.effective_end_date(+)
1129 and ppf.person_id = p_person_id
1130 and paf.assignment_id = p_assignment_id
1131 and nvl(ppf.CURRENT_EMP_OR_APL_FLAG,'N') = 'N' and nvl(ppf.CURRENT_EMPLOYEE_FLAG,'N') = 'N'
1132 and nvl(ppf.CURRENT_NPW_FLAG,'N') = 'N';
1133
1134 if assi_id is not null then
1135 result := 'TRUE';
1136 else
1137 result := 'FASE';
1138 --break;
1139 end if;
1140 exception
1141 when no_data_found then
1142 result := 'FALSE';
1143 null;
1144 WHEN others THEN
1145 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
1146 result := 'TRUE';
1147 END isPersonTerminated;
1148
1149 procedure getDeploymentPersonID (person_id in number, result out nocopy number )
1150 is
1151 cursor c_per is
1152 select from_person_id
1153 from hr_person_deployments dep
1154 where dep.to_person_id = person_id and permanent='Y';
1155
1156 l_person_id number;
1157 begin
1158 for c in c_per loop
1159 l_person_id := c.from_person_id;
1160 result := l_person_id;
1161 end loop;
1162 result := person_id;
1163 exception
1164 when others then
1165 result := person_id;
1166 end;
1167
1168 END HR_UTIL_MISC_SS;
[Home] [Help]