[Home] [Help]
PACKAGE BODY: APPS.HR_UTIL_MISC_SS
Source
1 PACKAGE BODY HR_UTIL_MISC_SS AS
2 /* $Header: hrutlmss.pkb 120.41.12020000.11 2013/03/07 09:19:52 pathota 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 --
266 -- Commented and added as a part of bug#13116989 Starts.
267 --
268 /*IF (l_asg_security = 'TRUE' AND p_person_id <> '-1' ) THEN
269 l_status := check_term_access(
270 p_function => p_function,
271 p_person_id => TO_NUMBER(p_person_id),
272 p_eff_date => p_eff_date);
273 ELSE
274 l_status := 'T';
275 END IF;*/
276
277 l_status := 'T';
278
279 --
280 -- Commented and added as a part of bug#13116989 Ends.
281 --
282
283 IF (l_status = 'T' AND p_person_id <> '-1' ) THEN
284 hr_utility.trace('In ( IF (l_status = T AND p_person_id <> -1 ) ): '|| l_proc);
285 IF g_debug then
286 hr_utility.set_location('Entering: '|| 'fnd_data_security.check_function', 10);
287 END IF;
288
289 l_status := fnd_data_security.check_function(
290 p_api_version => p_api_version
291 ,p_function => p_function
292 ,p_object_name => p_object_name
293 ,p_instance_pk1_value => p_person_id
294 ,p_instance_pk2_value => p_instance_pk2_value);
295 IF l_status = 'E' OR l_status = 'U' THEN
296 hr_utility.trace('Error in fnd_data_security.check_function l_status is: '||l_status||'. Error is: '|| replace(fnd_message.get_encoded(), chr(0), ' '));
297 l_status := 'F';
298 END IF;
299
300 IF g_debug then
301 hr_utility.set_location('Leaving: '|| 'fnd_data_security.check_function', 15);
302 END IF;
303 END IF;
304
305 IF (l_status = 'T' AND p_person_id <> '-1' ) THEN
306 l_status := check_cwk_access(
307 p_function => p_function,
308 p_person_id => TO_NUMBER(p_person_id),
309 p_eff_date => p_eff_date);
310 END IF;
311
312 IF g_debug then
313 hr_utility.set_location('Leaving: '||l_proc, 20);
314 END IF;
315
316 RETURN l_status;
317 END validate_selected_function;
318
319 /**
320 * This function is used to return the named user id
321 */
322 function get_person_id return number is
323 --
324 cursor get_sec_person_id(p_security_profile_id number) is
325 select named_person_id
326 from per_security_profiles
327 where security_profile_id=p_security_profile_id;
328 --
329 cursor get_user_person_id(p_user_id number) is
330 select employee_id
331 from fnd_user
332 where user_id=p_user_id;
333 --
334 l_person_id number;
335 --
336 begin
337 --
338 open get_sec_person_id(hr_security.get_security_profile);
339 fetch get_sec_person_id into l_person_id;
340 close get_sec_person_id;
341 if l_person_id is null then
342 open get_user_person_id(fnd_global.user_id);
343 fetch get_user_person_id into l_person_id;
344 close get_user_person_id;
345 end if;
346 --
347 return l_person_id;
348 --
349 end get_person_id;
350
351 /**
352 * This function is used to extract the parameter value from the parameter
353 * list. The return values and their meanings are :
354 * 1) ERROR - This parameter is not in this list or Exception raised
355 * 2) NULL - Value of this parameter is null
356 * 3) The value of the parameter itself
357 */
358 FUNCTION get_parameter_value (
359 p_parameter_list IN VARCHAR2
360 ,p_parameter IN VARCHAR2
361 )
362 RETURN VARCHAR2
363 IS
364 l_smarker INTEGER;
365 l_emarker INTEGER;
366 l_proc constant varchar2(100) := g_package || ' get_parameter_value';
367 BEGIN
368 hr_utility.set_location('Entering: '|| l_proc,5);
369 l_smarker := instr(p_parameter_list, p_parameter);
370
371 if (l_smarker = 0) THEN
372 hr_utility.set_location('Leaving: '|| l_proc,10);
373 RETURN NULL;
374
375 END IF;
376
377 l_smarker := l_smarker + LENGTH(p_parameter)+1;
378 l_emarker := INSTR(p_parameter_list, '&', l_smarker);
379
380 IF (l_emarker <= 0) THEN
381 l_emarker := LENGTH(p_parameter_list)+1;
382 END IF;
383 hr_utility.set_location('Leaving: '|| l_proc,15);
384
385 RETURN SUBSTR(p_parameter_list, l_smarker, l_emarker - l_smarker);
386 EXCEPTION
387 WHEN OTHERS THEN
388 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
389 RETURN NULL;
390 END get_parameter_value;
391
392
393
394 /**
395 * Checks for the Term access of the function returns a N or T
396 */
397 FUNCTION check_term_access (
398 p_function IN VARCHAR2
399 ,p_person_id IN NUMBER
400 ,p_eff_date IN DATE
401 )
402 RETURN VARCHAR2
403 IS
404 l_proc varchar2(72) := g_package||' check_term_access';
405 l_status VARCHAR2(10):= 'N';
406 l_item_type hr_api_transactions.item_type%type;
407 l_temp VARCHAR2(100);
408 l_function_name fnd_form_functions.function_name%Type;
409
410 -- Local Cursors
411 CURSOR csr_fnd_func_details (p_func_name VARCHAR2)IS
412 SELECT function_id, parameters, web_html_call
413 FROM fnd_form_functions fff
414 WHERE fff.function_name = p_func_name;
415
416
417 CURSOR csr_wf_process (p_wfpname VARCHAR2, p_item_type VARCHAR2) IS
418 SELECT 'N' status
419 FROM WF_PROCESS_ACTIVITIES pa1, WF_PROCESS_ACTIVITIES pa2,
420 WF_ACTIVITIES a1, WF_ACTIVITIES a2, WF_ACTIVITY_ATTR_VALUES aav
421 WHERE pa1.process_item_type = p_item_type
422 and pa1.process_name = p_wfpname
423 and pa1.activity_name = pa2.process_name
424 and a1.name = pa1.process_name
425 and pa1.process_version = a1.version
426 and a1.item_type = p_item_type
427 and sysdate between a1.begin_date and nvl(a1.end_date,sysdate)
428 and pa2.process_item_type = p_item_type
429 and pa2.process_name = a2.name
430 and pa2.process_version = a2.version
431 and a2.item_type = p_item_type
432 and sysdate between a2.begin_date and nvl(a2.end_date,sysdate)
433 and pa2.instance_id = aav.process_activity_id
434 and aav.name = 'HR_ACTIVITY_TYPE_VALUE'
435 and aav.text_value IN ('HR_TERMINATION_TOP_SS','HR_CWK_TERMINATION_PAGE_SS')
436 UNION
437 SELECT 'N' status
438 FROM WF_ACTIVITIES a, WF_PROCESS_ACTIVITIES pa,
439 WF_ACTIVITY_ATTR_VALUES aav
440 where a.item_type = p_item_type
441 and a.name = p_wfpname
442 and sysdate between a.begin_date and nvl(a.end_date,sysdate)
443 and pa.process_item_type = a.item_type
444 and pa.process_name = a.name
445 and pa.process_version = a.version
446 and pa.instance_id = aav.process_activity_id
447 and aav.name = 'HR_ACTIVITY_TYPE_VALUE'
448 and aav.text_value IN ('HR_TERMINATION_TOP_SS','HR_CWK_TERMINATION_PAGE_SS');
449
450 l_func_details csr_fnd_func_details%ROWTYPE;
451 BEGIN
452 IF g_debug then
453 hr_utility.set_location('Entering: '|| l_proc, 5);
454 END IF;
455
456 OPEN csr_fnd_func_details (p_function);
457 hr_utility.trace('Going into Fetch after (OPEN csr_fnd_func_details (p_function)): '|| l_proc);
458 FETCH csr_fnd_func_details INTO l_func_details;
459 CLOSE csr_fnd_func_details;
460 -- Overriding p_function value depending on pCalledFrom value
461 l_function_name := nvl(get_parameter_value(l_func_details.parameters,
462 'pCalledFrom'),p_function);
463
464 IF l_function_name <> p_function THEN
465 OPEN csr_fnd_func_details (l_function_name);
466 hr_utility.trace('Going into Fetch after (OPEN csr_fnd_func_details (l_function_name)): '|| l_proc);
467 FETCH csr_fnd_func_details INTO l_func_details;
468 CLOSE csr_fnd_func_details;
469 END IF;
470
471 -- Checks whether this function is workflow based
472 IF (INSTR(l_func_details.parameters, 'pProcessName') <> 0) THEN
473 hr_utility.trace('In( IF (INSTR(l_func_details.parameters, pProcessName) <> 0)): '|| l_proc);
474 l_temp := get_parameter_value(l_func_details.parameters,
475 'pProcessName');
476
477 IF (l_temp is not null) THEN
478 hr_utility.trace('In( IF (l_temp is not null)): '|| l_proc);
479 l_item_type := get_parameter_value(l_func_details.parameters,
480 'pItemType');
481 OPEN csr_wf_process (l_temp, l_item_type);
482 hr_utility.trace('Going into Fetch after( OPEN csr_wf_process (l_temp, l_item_type))): '|| l_proc);
483 FETCH csr_wf_process INTO l_status;
484 IF csr_wf_process%NOTFOUND THEN
485 l_status := 'T';
486 ELSE
487 l_status := check_primary_access(p_person_id, p_eff_date);
488 END IF;
489 CLOSE csr_wf_process;
490
491 END IF;
492 ELSE
493 hr_utility.trace('In else of (IF (INSTR(l_func_details.parameters, pProcessName) <> 0)): '|| l_proc);
494 l_status := 'T';
495 END IF;
496
497 IF g_debug then
498 hr_utility.set_location('Leaving: '||l_proc, 30);
499 END IF;
500
501 RETURN l_status; -- if status is N then it is no term access
502
503 EXCEPTION
504 WHEN OTHERS THEN
505 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
506 RETURN 'N';
507 END check_term_access;
508
509 /**
510 * Checks for the Primary access of the function returns a N or T
511 */
512
513 FUNCTION check_primary_access (
514 p_selected_person_id NUMBER,
515 p_effective_date DATE)
516 RETURN VARCHAR2 IS
517
518
519 cursor fetch_asg (l_person_id NUMBER, l_effective_date DATE) is
520 SELECT 'T'
521 FROM per_assignments_f2 paf, per_assignment_status_types past
522 WHERE paf.person_id = l_person_id
523 AND l_effective_date between paf.effective_start_date and paf.effective_end_date
524 AND paf.primary_flag = 'Y'
525 AND paf.assignment_type IN ('E', 'C')
526 AND paf.assignment_status_type_id = past.assignment_status_type_id
527 AND past.per_system_status NOT IN ('TERM_ASSIGN','END');
528
529 l_status VARCHAR2(1) := 'N';
530
531 l_proc varchar2(72) := g_package||'check_primary_access';
532 BEGIN
533 IF g_debug then
534 hr_utility.set_location('Entering: '|| l_proc, 5);
535 END IF;
536
537 OPEN fetch_asg (p_selected_person_id, p_effective_date);
538 hr_utility.trace('Going into Fetch after (OPEN fetch_asg (p_selected_person_id, p_effective_date) ): '|| l_proc);
539 FETCH fetch_asg INTO l_status;
540
541 IF fetch_asg%NOTFOUND THEN
542 l_status := 'N';
543 END IF;
544
545 CLOSE fetch_asg;
546
547 IF g_debug then
548 hr_utility.set_location('Leaving: '||l_proc, 10);
549 END IF;
550
551 RETURN l_status;
552
553 END check_primary_access;
554
555
556 /**
557 * This function checks whether the akregion code is in the
558 * CWK exclusion list. If Yes the return 'C' else 'T'
559 */
560 FUNCTION check_akregion_code (
561 p_ak_region IN VARCHAR2
562 )
563 RETURN VARCHAR2
564 IS
565 l_status VARCHAR2(10):='C';
566 --local variables
567 l_proc constant varchar2(100) := g_package || ' check_akregion_code';
568 -- CWK Phase III Changes,
569 -- Following functions are being made available for CWK
570 -- 'HR_ASSIGNMENT_TOP_SS',
571 -- 'HR_WORK_SCHED_TOP_SS',
572 -- 'HR_MANAGER_TOP_SS',
573 -- 'HR_P_RATE_TOP_SS',
574 -- 'HR_TERMINATION_TOP_SS',
575 -- 'HR_ASSIGNMENT_TOP_SS',
576 -- 'HR_NEWHIRE_PERSON_TOP_SS',
577 -- 'HR_CAED_TOP_SS'
578 CURSOR csr_akregion_list IS
579 SELECT 'C' FROM dual
580 WHERE UPPER(p_ak_region) IN (
581 'HR_CCMGR_OVERVIEW_TOP_SS',
582 'PQH_ACADEMIC_RANK_TOP',
583 'PQH_ACADEMIC_RANK_OVRVW_TOP',
584 'PQH_TENURE_STATUS_OVRVW_TOP',
585 'PQH_TENURE_STATUS_TOP',
586 'PQH_REVIEW_FIND_TOP',
587 --'OTA_TRAINING_TOP_SS',
588 --'OTA_ADDTRNG_OVERVIEW_TOP_SS',
589 --'PQH_REVIEWS_TOP',
590 --'PQH_EVENTS_MGR_SEARCH_TOP',
591 'HR_LOA_SUMMARY_TOP_SS'
592 );
593
594 BEGIN
595 hr_utility.set_location('Entering: '|| l_proc,5);
596 OPEN csr_akregion_list;
597 hr_utility.trace('Going into Fetch after ( OPEN csr_akregion_list ): '|| l_proc);
598 FETCH csr_akregion_list INTO l_status;
599 IF csr_akregion_list%NOTFOUND
600 THEN
601 l_status := 'T';
602 END IF;
603 CLOSE csr_akregion_list;
604
605 RETURN l_status;
606 hr_utility.set_location('Leaving: '|| l_proc,15);
607 END check_akregion_code;
608
609 /**
610 * Checks for the CWK access of the function returns a C or T
611 */
612
613 FUNCTION check_cwk_access (
614 p_function IN VARCHAR2
615 ,p_person_id IN NUMBER
616 ,p_eff_date IN DATE
617 )
618 RETURN VARCHAR2
619 IS
620 l_proc varchar2(72) := g_package||'check_cwk_access';
621 l_status VARCHAR2(10):= 'C';
622 l_npw_status VARCHAR2(30);
623 l_item_type hr_api_transactions.item_type%type;
624 l_temp VARCHAR2(100);
625 l_function_name fnd_form_functions.function_name%Type;
626
627 -- Local Cursors
628 CURSOR csr_per_npw_flag IS
629 SELECT nvl(current_npw_flag,'N')
630 FROM per_all_people_f per
631 WHERE per.person_id = p_person_id
632 AND p_eff_date BETWEEN per.effective_start_date AND per.effective_end_date;
633
634 CURSOR csr_fnd_func_details (p_func_name VARCHAR2)IS
635 SELECT function_id, parameters, web_html_call
636 FROM fnd_form_functions fff
637 WHERE fff.function_name = p_func_name;
638
639 CURSOR csr_menu_entries (p_menu_name VARCHAR2, p_func_id NUMBER) IS
640 select 'T' from fnd_menus m, fnd_menu_entries me
641 where menu_name = p_menu_name
642 and m.menu_id = me.menu_id
643 and me.function_id = p_func_id;
644
645 CURSOR csr_wf_process (p_wfpname VARCHAR2, p_item_type VARCHAR2) IS
646 SELECT 'C' status
647 FROM WF_ACTIVITIES a, WF_PROCESS_ACTIVITIES pa,
648 WF_ACTIVITY_ATTR_VALUES aav
649 where a.item_type = p_item_type
650 and a.name = p_wfpname
651 and sysdate between a.begin_date and nvl(a.end_date,sysdate)
652 and pa.process_item_type = a.item_type
653 and pa.process_name = a.name
654 and pa.process_version = a.version
655 and pa.instance_id = aav.process_activity_id
656 and aav.name = 'HR_ACTIVITY_TYPE_VALUE'
657 and 'C' = hr_util_misc_ss.check_akregion_code(text_value);
658
659 l_func_details csr_fnd_func_details%ROWTYPE;
660 BEGIN
661 IF g_debug then
662 hr_utility.set_location('Entering: '|| l_proc, 5);
663 END IF;
664
665 OPEN csr_per_npw_flag;
666 hr_utility.trace('Going into Fetch after (OPEN csr_per_npw_flag ): '|| l_proc);
667 FETCH csr_per_npw_flag INTO l_npw_status;
668 IF csr_per_npw_flag%NOTFOUND THEN
669 hr_utility.set_message(800,'PER_52097_APL_INV_PERSON_ID');
670 hr_utility.raise_error;
671 END IF;
672 CLOSE csr_per_npw_flag;
673 IF (l_npw_status <> 'Y') THEN
674 hr_utility.set_location('Leaving: '|| l_proc,15);
675 RETURN 'T';
676 END IF;
677
678 OPEN csr_fnd_func_details (p_function);
679 hr_utility.trace('Going into Fetch after ( OPEN csr_fnd_func_details (p_function)): '|| l_proc);
680 FETCH csr_fnd_func_details INTO l_func_details;
681 CLOSE csr_fnd_func_details;
682 -- Overriding p_function value depending on pCalledFrom value
683 l_function_name := nvl(get_parameter_value(l_func_details.parameters,
684 'pCalledFrom'),p_function);
685 IF l_function_name <> p_function THEN
686 OPEN csr_fnd_func_details (l_function_name);
687 hr_utility.trace('Going into Fetch after (OPEN csr_fnd_func_details (l_function_name)): '|| l_proc);
688 FETCH csr_fnd_func_details INTO l_func_details;
689 CLOSE csr_fnd_func_details;
690 END IF;
691
692 -- Checks whether this function is workflow based
693 IF (INSTR(l_func_details.parameters, 'pProcessName') <> 0) THEN
694 hr_utility.trace('In( IF (INSTR(l_func_details.parameters, pProcessName) <> 0) '|| l_proc);
695 l_temp := get_parameter_value(l_func_details.parameters,
696 'pProcessName');
697 IF (l_temp is not null) THEN
698 l_item_type := get_parameter_value(l_func_details.parameters,
699 'pItemType');
700 OPEN csr_wf_process (l_temp, l_item_type);
701 hr_utility.trace('Going into Fetch after (OPEN csr_wf_process (l_temp, l_item_type)): '|| l_proc);
702 FETCH csr_wf_process INTO l_status;
703 IF csr_wf_process%NOTFOUND THEN
704 l_status := 'T';
705 END IF;
706 CLOSE csr_wf_process;
707
708 END IF;
709
710 ELSE
711 hr_utility.trace('In else of ( IF (INSTR(l_func_details.parameters, pProcessName) <> 0) '|| l_proc);
712
713 l_temp := nvl(get_parameter_value(l_func_details.web_html_call,
714 'akRegionCode'),
715 get_parameter_value(l_func_details.web_html_call,
716 'OAFunc')
717 );
718 l_status := check_akregion_code(l_temp);
719 END IF;
720
721 IF g_debug then
722 hr_utility.set_location('Leaving: '||l_proc, 40);
723 END IF;
724
725 RETURN l_status;
726 EXCEPTION
727 WHEN OTHERS THEN
728
729 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
730 RETURN 'C';
731 END check_cwk_access;
732
733 PROCEDURE clear_cache
734 IS
735 --local variable
736 l_proc constant varchar2(100) := g_package || ' clear_cache';
737 BEGIN
738 hr_utility.set_location('Entering: '|| l_proc,5);
739 g_entity_list.delete;
740 -- g_entitydetail_list.delete;
741 hr_utility.set_location('Leaving: '|| l_proc,10);
742 END clear_cache;
743
744 FUNCTION entity_exists (
745 p_entity_id IN NUMBER
746 )
747 RETURN VARCHAR2
748 IS
749 --local variable
750 l_proc constant varchar2(100) := g_package || ' entity_exists';
751 BEGIN
752 hr_utility.set_location('Entering: '|| l_proc,5);
753 IF g_entity_list.exists(p_entity_id) THEN
754 hr_utility.set_location('Leaving: '|| l_proc,10);
755 RETURN 'T';
756 END IF;
757 hr_utility.set_location('Leaving: '|| l_proc,15);
758 RETURN 'F';
759 EXCEPTION
760 WHEN others THEN
761 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
762 RETURN 'F';
763 END entity_exists;
764
765 PROCEDURE populate_entity_list (
766 p_elist IN HR_MISC_SS_NUMBER_TABLE,
767 p_retain_cache IN VARCHAR2
768 )
769 IS
770 --local variable
771 l_proc constant varchar2(100) := g_package || ' populate_entity_list';
772 BEGIN
773 hr_utility.set_location('Entering: '|| l_proc,5);
774 if p_retain_cache = 'N'
775 then
776 clear_cache();
777 end if;
778 FOR I IN 1 ..p_elist.count LOOP
779 g_entity_list(p_elist(i)) := p_elist(i);
780 END LOOP;
781 hr_utility.set_location('Leaving: '|| l_proc,10);
782 END populate_entity_list;
783
784 PROCEDURE check_ota_installed (appl_id number, status out nocopy varchar2) is
785 l_status VARCHAR2(1);
786 l_industry VARCHAR2(10);
787 l_flag boolean;
788 --local variable
789 l_proc constant varchar2(100) := g_package || ' check_ota_installed';
790 begin
791 hr_utility.set_location('Entering: '|| l_proc,5);
792 l_flag := fnd_installation.get(appl_id => appl_id,
793 dep_appl_id => appl_id,
794 status => l_status,
795 industry => l_industry );
796
797 if l_status = 'I' then
798 status := 'Y';
799 else
800 status := 'N';
801 end if;
802 hr_utility.set_location('Leaving: '|| l_proc,10);
803 end check_ota_installed;
804
805 FUNCTION get_employee_salary(
806 p_assignment_id IN NUMBER
807 ,P_Effective_Date IN date
808 ) RETURN NUMBER IS
809 ln_proposed_salary NUMBER;
810 lv_frequency VARCHAR2(100);
811 ln_annual_salary NUMBER;
812 lv_pay_basis_name VARCHAR2(100);
813 lv_reason_cd VARCHAR2(100);
814 ln_currency VARCHAR2(100);
815 ln_status NUMBER;
816 lv_pay_basis_frequency per_pay_bases.pay_basis%TYPE;
817 begin
818 pqh_employee_salary.get_employee_salary(
819 P_Assignment_id => p_assignment_id,
820 P_Effective_Date => p_effective_date,
821 p_salary => ln_proposed_salary,
822 p_frequency => lv_frequency,
823 p_annual_salary => ln_annual_salary,
824 p_pay_basis => lv_pay_basis_name,
825 p_reason_cd => lv_reason_cd,
826 p_currency => ln_currency,
827 p_status => ln_status,
828 p_pay_basis_frequency => lv_pay_basis_frequency);
829
830 return ln_annual_salary;
831 end get_employee_salary;
832
833 FUNCTION get_employee_salary(
834 p_assignment_id in number
835 ,p_Effective_Date in date
836 ,p_proposed_salary IN NUMBER
837 ,p_pay_annual_factor IN number
838 ,p_pay_basis in varchar2
839 ) RETURN NUMBER IS
840 l_fte_profile_value VARCHAR2(30) := fnd_profile.VALUE('PER_ANNUAL_SALARY_ON_FTE');
841 l_pay_factor number;
842 l_fte_factor NUMBER;
843 ln_annual_salary NUMBER;
844 begin
845 l_pay_factor := p_pay_annual_factor;
846 if (p_pay_annual_factor is null OR p_pay_annual_factor = 0) then
847 l_pay_factor := 1;
848 end if;
849 ln_annual_salary := p_proposed_salary * l_pay_factor;
850 if ((l_fte_profile_value is null OR l_fte_profile_value = 'Y') AND p_pay_basis = 'HOURLY') then
851 l_fte_factor := per_saladmin_utility.get_fte_factor(p_assignment_id,p_Effective_Date);
852 ln_annual_salary := ln_annual_salary * l_fte_factor;
853 end if;
854 return ln_annual_salary;
855 end get_employee_salary;
856
857 FUNCTION get_apl_asgs_count(
858 p_person_id IN number,
859 p_effective_date IN date)
860 return number
861 is
862 CURSOR csr_apl_asgs_count is
863 select assignment_id from per_all_assignments_f
864 where person_id = p_person_id and assignment_type = 'A' and
865 p_effective_date between effective_start_date and effective_end_date;
866 l_apl_asgs_count INTEGER := 0;
867 begin
868 FOR l_apl_asgs_rec IN csr_apl_asgs_count
869 LOOP
870 l_apl_asgs_count := l_apl_asgs_count + 1;
871 END LOOP;
872 RETURN(l_apl_asgs_count);
873 END get_apl_asgs_count;
874
875
876 PROCEDURE is_voluntary_termination (
877 itemtype in varchar2,
878 itemkey in varchar2,
879 actid in number,
880 funcmode in varchar2,
881 resultout out nocopy varchar2
882 )
883 IS
884 l_number_value wf_activity_attr_values.number_value%type;
885 l_proc constant varchar2(100) := g_package || ' is_voluntary_termination';
886 dummy varchar2(2) := 'N';
887 l_vol_term varchar2(2) := 'N';
888
889 cursor csr_attr_value(actid in number, name in varchar2) is
890 SELECT WAAV.TEXT_VALUE Value
891 FROM WF_ACTIVITY_ATTR_VALUES WAAV
892 WHERE WAAV.PROCESS_ACTIVITY_ID = actid
893 AND WAAV.NAME = name;
894
895 BEGIN
896
897 hr_utility.set_location('Entering: '|| l_proc,5);
898
899 l_number_value := wf_engine.GetItemAttrNumber (
900 itemtype => itemtype,
901 itemkey => itemkey,
902 aname => 'CURRENT_PERSON_ID');
903
904 if (l_number_value = fnd_global.employee_id) then
905 dummy := 'Y';
906 end if;
907
908 if (dummy = 'Y') then
909 l_vol_term := wf_engine.getitemattrtext(itemtype, itemkey,
910 'HR_VOL_TERM_SS',true);
911 if (l_vol_term is null OR l_vol_term <> 'Y') then
912 dummy := 'N';
913 end if;
914 end if;
915
916 if (dummy = 'Y') then
917 open csr_attr_value(actid,'BYPASS_CHG_MGR');
918 fetch csr_attr_value into dummy;
919 close csr_attr_value;
920 end if;
921
922 if (dummy = 'Y')
923 then
924 resultout := 'COMPLETE:'|| 'Y';
925 else
926 resultout := 'COMPLETE:'|| 'N';
927 end if;
928 --
929 hr_utility.set_location('Leaving: '|| l_proc,10);
930
931 EXCEPTION
932 WHEN OTHERS THEN
933
934 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
935 WF_CORE.CONTEXT (
936 g_package,
937 'is_voluntary_termination',
938 itemtype,
939 itemkey,
940 to_char(actid),
941 funcmode);
942 RAISE;
943 END is_voluntary_termination;
944
945 PROCEDURE is_primary_assign (
946 itemtype in varchar2,
947 itemkey in varchar2,
948 actid in number,
949 funcmode in varchar2,
950 resultout out nocopy varchar2
951 )
952 IS
953 l_number_value wf_activity_attr_values.number_value%type;
954 l_date_value wf_activity_attr_values.date_value%type;
955 l_proc constant varchar2(100) := g_package || ' is_primary_assign';
956 dummy varchar2(2);
957
958 BEGIN
959
960 hr_utility.set_location('Entering: '|| l_proc,5);
961
962 hr_approval_ss.create_item_attrib_if_notexist(itemtype => itemtype
963 ,itemkey => itemkey
964 ,aname => 'HR_TERM_SEC_ASG'
965 ,text_value=>'Y'
966 ,number_value=>null,
967 date_value=>null
968 );
969
970 l_number_value := wf_engine.GetItemAttrNumber (
971 itemtype => itemtype,
972 itemkey => itemkey,
973 aname => 'CURRENT_ASSIGNMENT_ID');
974
975 l_date_value := wf_engine.GetItemAttrDate (
976 itemtype => itemtype,
977 itemkey => itemkey,
978 aname => 'CURRENT_EFFECTIVE_DATE');
979
980 select primary_flag into dummy from per_all_assignments_f
981 where assignment_id=l_number_value and l_date_value between effective_start_date
982 and effective_end_date;
983
984 if dummy = 'Y'
985 then
986 resultout := 'COMPLETE:'|| 'Y';
987 else
988 resultout := 'COMPLETE:'|| 'N';
989 end if;
990 --
991 hr_utility.set_location('Leaving: '|| l_proc,10);
992
993 EXCEPTION
994 WHEN OTHERS THEN
995
996 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
997 WF_CORE.CONTEXT (
998 g_package,
999 'branch_on_approval_flag',
1000 itemtype,
1001 itemkey,
1002 to_char(actid),
1003 funcmode);
1004 RAISE;
1005 END is_primary_assign;
1006
1007 FUNCTION get_assign_termination_date(
1008 p_assignment_id IN number)
1009 return date is
1010 l_asg_term_date date;
1011 begin
1012 select min(asg.EFFECTIVE_start_DATE) into l_asg_term_date
1013 from per_all_assignments_f asg ,per_assignment_status_types ast
1014 where asg.assignment_id = p_assignment_id
1015 and ast.assignment_status_type_id = asg.assignment_status_type_id
1016 and ast.per_system_status = 'TERM_ASSIGN';
1017
1018 if l_asg_term_date is null then
1019 select max(EFFECTIVE_end_DATE) into l_asg_term_date
1020 from per_all_assignments_f where assignment_id = p_assignment_id;
1021 if l_asg_term_date = hr_api.g_eot then
1022 l_asg_term_date := null;
1023 end if;
1024 end if;
1025
1026 return l_asg_term_date;
1027 end get_assign_termination_date;
1028
1029 PROCEDURE is_employee_check (
1030 itemtype in varchar2,
1031 itemkey in varchar2,
1032 actid in number,
1033 funcmode in varchar2,
1034 resultout out nocopy varchar2
1035 )
1036 IS
1037 l_text_value wf_activity_attr_values.text_value%type;
1038 --local variable
1039 l_proc constant varchar2(100) := g_package || ' is_employee_check';
1040 BEGIN
1041 hr_utility.set_location('Entering: '|| l_proc,5);
1042 --
1043 l_text_value := wf_engine.GetItemAttrText (
1044 itemtype => itemtype,
1045 itemkey => itemkey,
1046 aname => 'HR_SELECTED_PERSON_TYPE_ATTR');
1047 if l_text_value = 'C'
1048 then
1049 resultout := 'COMPLETE:'|| 'N';
1050 else
1051 resultout := 'COMPLETE:'|| 'Y';
1052 end if;
1053 --
1054 hr_utility.set_location('Leaving: '|| l_proc,10);
1055 EXCEPTION
1056 WHEN OTHERS THEN
1057
1058 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
1059 WF_CORE.CONTEXT (
1060 g_package,
1061 'branch_on_approval_flag',
1062 itemtype,
1063 itemkey,
1064 to_char(actid),
1065 funcmode);
1066 RAISE;
1067 END is_employee_check;
1068
1069 PROCEDURE populateInterimPersonList (
1070 person_data PER_INTERIM_PERSON_LIST_STRUCT
1071 )
1072 IS
1073 --local variable
1074 l_proc constant varchar2(100) := g_package || ' populateInterimPersonList';
1075 BEGIN
1076 hr_utility.set_location('Entering: '|| l_proc,5);
1077 -- This commit is issued here to remove the Data from the Temp table.
1078 -- This can be replaced by truncate.
1079 --COMMIT;
1080 DELETE PER_INTERIM_PERSON_LIST;
1081 FOR i in 1.. person_data.count LOOP
1082 INSERT INTO PER_INTERIM_PERSON_LIST (person_id, assignment_id, in_my_list)
1083 values (person_data(i).person_id, person_data(i).assignment_id, person_data(i).in_my_list);
1084 END LOOP;
1085 hr_utility.set_location('Leaving: '|| l_proc,10);
1086 EXCEPTION
1087 WHEN others THEN
1088 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
1089 RAISE;
1090 END populateInterimPersonList;
1091
1092 PROCEDURE populateInterimListFromMyList (
1093 person_id number
1094 )
1095 IS
1096 --local variable
1097 l_proc constant varchar2(100) := g_package || ' populateInterimListFromMyList';
1098 BEGIN
1099 hr_utility.set_location('Entering: '|| l_proc,5);
1100 --COMMIT;
1101 DELETE PER_INTERIM_PERSON_LIST;
1102 INSERT INTO PER_INTERIM_PERSON_LIST (person_id, assignment_id, in_my_list)
1103 SELECT selected_person_id, selected_assignment_id, 'Y'
1104 FROM hr_working_person_lists
1105 WHERE owning_person_id = person_id;
1106 hr_utility.set_location('Leaving: '|| l_proc,10);
1107 EXCEPTION
1108 WHEN others THEN
1109 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
1110 RAISE;
1111 END populateInterimListFromMyList;
1112
1113 PROCEDURE addToMyListFromInterimList (
1114 prsn_id number
1115 )
1116 IS
1117 --local variable
1118 l_proc constant varchar2(100) := g_package || ' addToMyListFromInterimList';
1119 BEGIN
1120 hr_utility.set_location('Entering: '|| l_proc,5);
1121 INSERT INTO HR_WORKING_PERSON_LISTS(working_person_list_id, owning_person_id,
1122 selected_person_id, current_selection, multiple_selection, selected_assignment_id)
1123 SELECT HR_WORKING_PERSON_LISTS_s.NEXTVAL,
1124 prsn_id,
1125 list.person_id,
1126 NULL,
1127 NULL,
1128 list.assignment_id
1129 FROM PER_INTERIM_PERSON_LIST list;
1130 hr_utility.set_location('Leaving: '|| l_proc,10);
1131 EXCEPTION
1132 WHEN others THEN
1133 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
1134 RAISE;
1135 END addToMyListFromInterimList;
1136
1137 PROCEDURE setEffectiveDate
1138 (p_effective_date in date)
1139 IS
1140 --
1141 PRAGMA AUTONOMOUS_TRANSACTION;
1142 --
1143 --local variable
1144 l_proc constant varchar2(100) := g_package || ' setEffectiveDate';
1145 BEGIN
1146 hr_utility.set_location('Entering: '|| l_proc,5);
1147 --
1148 g_eff_date := trunc(p_effective_date);
1149 --bug 5765957 start
1150 begin
1151 dt_fndate.set_effective_date(g_eff_date);
1152 exception
1153 when DUP_VAL_ON_INDEX then
1154 hr_utility.set_location('change for DUP_VAL_ON_INDEX : ' || l_proc ,999);
1155 when others then
1156 hr_utility.set_location('change for others : ' || l_proc ,998);
1157 end;
1158 --bug 5765957 end
1159 initLoginPrsnCtx(g_eff_date);
1160 g_year_start := to_date('01/01/'||to_char(g_eff_date,'RRRR'),'DD/MM/RRRR');
1161 --
1162 commit;
1163 --
1164 hr_utility.set_location('Leaving: '|| l_proc,10);
1165 EXCEPTION
1166 WHEN others THEN
1167 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
1168 rollback;
1169 raise;
1170 END setEffectiveDate;
1171
1172 FUNCTION getObjectName(
1173 p_object IN varchar2,
1174 p_object_id IN number,
1175 p_bg_id IN number,
1176 p_value IN varchar2
1177 )
1178 return varchar2
1179 IS
1180 --local variable
1181 l_proc constant varchar2(100) := g_package || ' getObjectName';
1182 BEGIN
1183 hr_utility.set_location('Entering: '|| l_proc,5);
1184 IF PER_WORK_STRUCTURE_OVERRIDE_SS.isOverrideEnabled(p_object) THEN
1185 return nvl(PER_WORK_STRUCTURE_OVERRIDE_SS.getObjectName(p_object
1186 , p_object_id, p_bg_id, p_value) ,p_value);
1187 END IF;
1188 hr_utility.set_location('Leaving: '|| l_proc,10);
1189 return p_value;
1190 END getObjectName;
1191
1192 PROCEDURE initialize_am IS
1193 l_proc varchar2(72) := g_package||'initialize_am';
1194 BEGIN
1195
1196 -- If g_debug is only set at package level
1197 -- logging will not work consistently
1198 --
1199 g_debug := hr_utility.debug_enabled;
1200
1201 IF g_debug then
1202 hr_utility.set_location('Entering: '|| l_proc, 5);
1203 END IF;
1204
1205
1206 -- 3952978
1207 --
1208 -- Mark HR Security cache as invalid. The next time a secure view
1209 -- is accessed - cache will be rebuilt. This is the equivalent of
1210 -- code calling fnd_global.apps_initialize but where the HR signon
1211 -- callback is not called - ie user/resp/sc context has not changed.
1212 --
1213
1214 if ( nvl(fnd_profile.value('HR_SEC_INIT_AM'),'N') = 'Y')
1215 then
1216 hr_signon.session_context := hr_signon.session_context + 1;
1217 end if;
1218
1219 END initialize_am ;
1220
1221 FUNCTION getEnableSecurityGroups RETURN VARCHAR2 IS
1222
1223 l_enableSecGroups varchar2(10);
1224 defined_z BOOLEAN;
1225
1226 BEGIN
1227
1228 l_enableSecGroups := nvl(fnd_profile.value('ENABLE_SECURITY_GROUPS'),'N');
1229
1230 IF (nvl(fnd_global.application_short_name,'#') <> 'PER' AND l_enableSecGroups <> 'Y') THEN
1231 fnd_profile.get_specific('ENABLE_SECURITY_GROUPS',NULL,NULL,'800',l_enableSecGroups,defined_z,NULL,NULL);
1232 IF (nvl(l_enableSecGroups,'N') <> nvl(fnd_profile.value('ENABLE_SECURITY_GROUPS'),'N')) THEN
1233 fnd_profile.put('ENABLE_SECURITY_GROUPS', l_enableSecGroups);
1234 END IF;
1235 END IF;
1236
1237 RETURN l_enableSecGroups;
1238
1239 EXCEPTION When Others then
1240 RETURN 'N';
1241 END getEnableSecurityGroups;
1242
1243 PROCEDURE SET_SYS_CTX (
1244 p_legCode in varchar2
1245 ,p_bgId in varchar2
1246 ) IS
1247
1248 l_secGrpId number;
1249 l_enableSecGrp varchar2(10);
1250 BEGIN
1251
1252 l_secGrpId := 0;
1253 l_enableSecGrp := getEnableSecurityGroups;
1254
1255 IF p_legCode IS NOT NULL THEN
1256 HR_API.SET_LEGISLATION_CONTEXT(p_legCode);
1257 END IF;
1258
1259
1260 IF (p_bgId IS NOT NULL AND l_enableSecGrp = 'Y' ) THEN
1261 BEGIN
1262
1263 select security_group_id into l_secGrpId
1264 from fnd_security_groups
1265 where security_group_key = p_bgId;
1266
1267 fnd_client_info.set_security_group_context(to_char(l_secGrpId));
1268 -- Fix for bug 5531282 , this reverts the earlier fix for bug 5084537
1269 --FND_GLOBAL.set_security_group_id_context(l_secGrpId);
1270
1271 EXCEPTION When Others then
1272 fnd_client_info.set_security_group_context(to_char(l_secGrpId));
1273 -- Fix for bug 5531282 , this reverts the earlier fix for bug 5084537
1274 -- FND_GLOBAL.set_security_group_id_context(l_secGrpId);
1275 END;
1276 END IF;
1277
1278 EXCEPTION
1279 WHEN others THEN
1280 raise;
1281 END SET_SYS_CTX;
1282
1283 PROCEDURE populateInterimEntityList (
1284 entity_data PER_INTERIM_ENTITY_LIST_STRUCT
1285 ,p_retain_cache IN VARCHAR2
1286 )
1287 IS
1288 --local variable
1289 l_proc constant varchar2(100) := g_package || ' populateInterimEntityList';
1290 BEGIN
1291 hr_utility.set_location('Entering: '|| l_proc,5);
1292 if p_retain_cache = 'N'
1293 then
1294 DELETE PER_INTERIM_ENTITY_LIST;
1295 end if;
1296 FOR i in 1.. entity_data.count LOOP
1297 INSERT INTO PER_INTERIM_ENTITY_LIST (entity_name,state,pk1,pk2,pk3,pk4,pk5)
1298 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);
1299 END LOOP;
1300 hr_utility.set_location('Leaving: '|| l_proc,10);
1301 EXCEPTION
1302 WHEN others THEN
1303 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
1304 RAISE;
1305 END populateInterimEntityList;
1306
1307 PROCEDURE clearInterimEntityList
1308 IS
1309 l_proc constant varchar2(100) := g_package || 'clearInterimEntityList';
1310 BEGIN
1311 hr_utility.set_location('Entering: '|| l_proc,5);
1312 DELETE PER_INTERIM_ENTITY_LIST;
1313 END clearInterimEntityList;
1314
1315 procedure isPersonTerminated (
1316 result out nocopy varchar2,
1317 p_person_id varchar2,
1318 p_assignment_id varchar2
1319 )
1320 is
1321 l_proc constant varchar2(100) := g_package || ' isPersonTerminated';
1322 assi_id varchar2(200) := null ;
1323
1324 begin
1325 hr_utility.set_location('Entering: '|| l_proc,5);
1326
1327 select assignment_id
1328 into assi_id
1329 from per_people_f ppf, per_assignments_f paf
1330 where paf.person_id = ppf.person_id
1331 and trunc(sysdate) between paf.effective_start_date(+) and paf.effective_end_date(+)
1332 and trunc(sysdate) between ppf.effective_start_date(+) and ppf.effective_end_date(+)
1333 and ppf.person_id = p_person_id
1334 and paf.assignment_id = p_assignment_id
1335 and nvl(ppf.CURRENT_EMP_OR_APL_FLAG,'N') = 'N' and nvl(ppf.CURRENT_EMPLOYEE_FLAG,'N') = 'N'
1336 and nvl(ppf.CURRENT_NPW_FLAG,'N') = 'N';
1337
1338 if assi_id is not null then
1339 result := 'TRUE';
1340 else
1341 result := 'FASE';
1342 --break;
1343 end if;
1344 exception
1345 when no_data_found then
1346 result := 'FALSE';
1347 null;
1348 WHEN others THEN
1349 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
1350 result := 'TRUE';
1351 END isPersonTerminated;
1352
1353 procedure getDeploymentPersonID (person_id in number, result out nocopy number )
1354 is
1355 cursor c_per is
1356 select from_person_id
1357 from hr_person_deployments dep
1358 where dep.to_person_id = person_id and permanent='Y';
1359
1360 l_person_id number;
1361 begin
1362 for c in c_per loop
1363 l_person_id := c.from_person_id;
1364 result := l_person_id;
1365 end loop;
1366 result := person_id;
1367 exception
1368 when others then
1369 result := person_id;
1370 end;
1371
1372 FUNCTION getBusinessGroup(
1373 p_function_id IN number,
1374 p_bg_id IN number,
1375 p_person_id IN number
1376 )
1377 return per_all_people_f.business_group_id%Type
1378 IS
1379 --local variable
1380 l_proc constant varchar2(100) := g_package || ' getBusinessGroup';
1381 l_func_name varchar2(100) default null;
1382 l_param_name varchar2(100) default null;
1383 l_bg_id number(20);
1384 l_web_html_call varchar2(250) default null;
1385 l_appr_index number default 0;
1386 l_ghr_index number default 0;
1387
1388 CURSOR csr_fnd_func_details IS
1389 SELECT function_name ,parameters, web_html_call
1390 FROM fnd_form_functions fff
1391 WHERE fff.function_id = p_function_id;
1392
1393 l_func_details csr_fnd_func_details%ROWTYPE;
1394
1395 BEGIN
1396 hr_utility.set_location('Entering: '|| l_proc,5);
1397 l_bg_id := p_bg_id;
1398 OPEN csr_fnd_func_details;
1399 FETCH csr_fnd_func_details INTO l_func_details;
1400
1401 if csr_fnd_func_details%found then
1402 l_func_name := l_func_details.function_name;
1403 l_param_name := nvl(get_parameter_value(l_func_details.parameters,'pCalledFrom'),l_func_name);
1404 l_web_html_call := l_func_details.WEB_HTML_CALL;
1405 begin
1406 select INSTR(l_web_html_call,'/oracle/apps/per/selfservice/talentmanagement/webui/MgrTalentManagementPG') as str_index into l_appr_index from dual;
1407 if(l_appr_index=0)then
1408 select INSTR(l_web_html_call,'/oracle/apps/per/selfservice/appraisals/webui/MgrMainAppraiserPG&') as str_index into l_appr_index from dual;
1409 end if;
1410 end;
1411
1412 select INSTR(l_web_html_call,'/ghr/') as str_index into l_ghr_index from dual;
1413
1414 -- This logic for SSHR Manager Self-Service fucntion and for Appraisal Manager Fucntion
1415 IF (l_func_name <> l_param_name) OR (l_appr_index > 0 ) OR (l_ghr_index > 0)THEN
1416 begin
1417 select decode(fnd_profile.value('ENABLE_SECURITY_GROUPS'), 'Y' , p_bg_id,
1418 decode(fnd_global.employee_id,p_person_id,
1419 nvl(
1420 decode( FND_PROFILE.VALUE_SPECIFIC('PER_BUSINESS_GROUP_ID',null,fnd_global.resp_id)
1421 ,null,fnd_profile.value('PER_BUSINESS_GROUP_ID'),
1422 FND_PROFILE.VALUE_SPECIFIC('PER_BUSINESS_GROUP_ID',null,fnd_global.resp_id)
1423 )
1424 ,p_bg_id
1425 ),p_bg_id)) into l_bg_id from dual;
1426 end;
1427 END IF; -- end of IF (l_func_name <> l_param_name) OR (l_appr_index > 0 )THEN
1428 end if; -- end of if csr_fnd_func_details%found then
1429 CLOSE csr_fnd_func_details;
1430 hr_utility.set_location('Leaving getBusinessGroup with result (BusinesGroupID): ' || l_bg_id,5);
1431 return l_bg_id;
1432 END getBusinessGroup;
1433
1434
1435 procedure update_attachment
1436 (p_entity_name in varchar2 default null
1437 ,p_pk1_value in varchar2 default null
1438 ,p_rowid in varchar2 ) is
1439
1440 l_proc varchar2(72) := g_package ||'update_attachment';
1441 l_rowid varchar2(50);
1442 l_language varchar2(30) ;
1443 data_error exception;
1444
1445 cursor csr_get_attached_doc is
1446 select *
1447 from fnd_attached_documents
1448 where rowid = p_rowid;
1449 cursor csr_get_doc(csr_p_document_id in number) is
1450 select *
1451 from fnd_documents
1452 where document_id = csr_p_document_id;
1453 cursor csr_get_doc_tl (csr_p_lang in varchar2
1454 ,csr_p_document_id in number) is
1455 select *
1456 from fnd_documents_tl
1457 where document_id = csr_p_document_id
1458 and language = csr_p_lang;
1459 l_attached_doc_pre_upd csr_get_attached_doc%rowtype;
1460 l_doc_pre_upd csr_get_doc%rowtype;
1461 l_doc_tl_pre_upd csr_get_doc_tl%rowtype;
1462 Begin
1463 hr_utility.set_location(' Entering:' || l_proc,10);
1464 select userenv('LANG') into l_language from dual;
1465 Open csr_get_attached_doc;
1466 fetch csr_get_attached_doc into l_attached_doc_pre_upd;
1467 IF csr_get_attached_doc%NOTFOUND THEN
1468 close csr_get_attached_doc;
1469 raise data_error;
1470 END IF;
1471
1472 Open csr_get_doc(l_attached_doc_pre_upd.document_id);
1473 fetch csr_get_doc into l_doc_pre_upd;
1474 IF csr_get_doc%NOTFOUND then
1475 close csr_get_doc;
1476 raise data_error;
1477 END IF;
1478
1479 Open csr_get_doc_tl (csr_p_lang => l_language
1480 ,csr_p_document_id => l_attached_doc_pre_upd.document_id);
1481 fetch csr_get_doc_tl into l_doc_tl_pre_upd;
1482 IF csr_get_doc_tl%NOTFOUND then
1483 close csr_get_doc_tl;
1484 raise data_error;
1485 END IF;
1486
1487 hr_utility.set_location(' before fnd_attached_documents_pkg.lock_row :' || l_proc,20);
1488 fnd_attached_documents_pkg.lock_row
1489 (x_rowid => p_rowid
1490 ,x_attached_document_id =>
1491 l_attached_doc_pre_upd.attached_document_id
1492 ,x_document_id => l_doc_pre_upd.document_id
1493 ,x_seq_num => l_attached_doc_pre_upd.seq_num
1494 ,x_entity_name => l_attached_doc_pre_upd.entity_name
1495 ,x_column1 => l_attached_doc_pre_upd.column1
1496 ,x_pk1_value => l_attached_doc_pre_upd.pk1_value
1497 ,x_pk2_value => l_attached_doc_pre_upd.pk2_value
1498 ,x_pk3_value => l_attached_doc_pre_upd.pk3_value
1499 ,x_pk4_value => l_attached_doc_pre_upd.pk4_value
1500 ,x_pk5_value => l_attached_doc_pre_upd.pk5_value
1501 ,x_automatically_added_flag =>
1502 l_attached_doc_pre_upd.automatically_added_flag
1503 ,x_attribute_category =>
1504 l_attached_doc_pre_upd.attribute_category
1505 ,x_attribute1 => l_attached_doc_pre_upd.attribute1
1506 ,x_attribute2 => l_attached_doc_pre_upd.attribute2
1507 ,x_attribute3 => l_attached_doc_pre_upd.attribute3
1508 ,x_attribute4 => l_attached_doc_pre_upd.attribute4
1509 ,x_attribute5 => l_attached_doc_pre_upd.attribute5
1510 ,x_attribute6 => l_attached_doc_pre_upd.attribute6
1511 ,x_attribute7 => l_attached_doc_pre_upd.attribute7
1512 ,x_attribute8 => l_attached_doc_pre_upd.attribute8
1513 ,x_attribute9 => l_attached_doc_pre_upd.attribute9
1514 ,x_attribute10 => l_attached_doc_pre_upd.attribute10
1515 ,x_attribute11 => l_attached_doc_pre_upd.attribute11
1516 ,x_attribute12 => l_attached_doc_pre_upd.attribute12
1517 ,x_attribute13 => l_attached_doc_pre_upd.attribute13
1518 ,x_attribute14 => l_attached_doc_pre_upd.attribute14
1519 ,x_attribute15 => l_attached_doc_pre_upd.attribute15
1520 ,x_datatype_id => l_doc_pre_upd.datatype_id
1521 ,x_category_id => l_doc_pre_upd.category_id
1522 ,x_security_type => l_doc_pre_upd.security_type
1523 ,x_security_id => l_doc_pre_upd.security_id
1524 ,x_publish_flag => l_doc_pre_upd.publish_flag
1525 ,x_image_type => l_doc_pre_upd.image_type
1526 ,x_storage_type => l_doc_pre_upd.storage_type
1527 ,x_usage_type => l_doc_pre_upd.usage_type
1528 ,x_start_date_active => l_doc_pre_upd.start_date_active
1529 ,x_end_date_active => l_doc_pre_upd.end_date_active
1530 ,x_language => l_doc_tl_pre_upd.language
1531 ,x_description => l_doc_tl_pre_upd.description
1532 ,x_file_name => l_doc_pre_upd.file_name
1533 ,x_media_id => l_doc_pre_upd.media_id
1534 ,x_doc_attribute_category =>
1535 l_doc_tl_pre_upd.doc_attribute_category
1536 ,x_doc_attribute1 => l_doc_tl_pre_upd.doc_attribute1
1537 ,x_doc_attribute2 => l_doc_tl_pre_upd.doc_attribute2
1538 ,x_doc_attribute3 => l_doc_tl_pre_upd.doc_attribute3
1539 ,x_doc_attribute4 => l_doc_tl_pre_upd.doc_attribute4
1540 ,x_doc_attribute5 => l_doc_tl_pre_upd.doc_attribute5
1541 ,x_doc_attribute6 => l_doc_tl_pre_upd.doc_attribute6
1542 ,x_doc_attribute7 => l_doc_tl_pre_upd.doc_attribute7
1543 ,x_doc_attribute8 => l_doc_tl_pre_upd.doc_attribute8
1544 ,x_doc_attribute9 => l_doc_tl_pre_upd.doc_attribute9
1545 ,x_doc_attribute10 => l_doc_tl_pre_upd.doc_attribute10
1546 ,x_doc_attribute11 => l_doc_tl_pre_upd.doc_attribute11
1547 ,x_doc_attribute12 => l_doc_tl_pre_upd.doc_attribute12
1548 ,x_doc_attribute13 => l_doc_tl_pre_upd.doc_attribute13
1549 ,x_doc_attribute14 => l_doc_tl_pre_upd.doc_attribute14
1550 ,x_doc_attribute15 => l_doc_tl_pre_upd.doc_attribute15
1551 ,x_url => l_doc_pre_upd.url
1552 ,x_title => l_doc_tl_pre_upd.title
1553 );
1554
1555
1556 hr_utility.set_location(' before fnd_attached_documents_pkg.update_row :' || l_proc,30);
1557 fnd_attached_documents_pkg.update_row
1558 (x_rowid => p_rowid
1559 ,x_attached_document_id =>
1560 l_attached_doc_pre_upd.attached_document_id
1561 ,x_document_id => l_doc_pre_upd.document_id
1562 ,x_last_update_date => trunc(sysdate)
1563 ,x_last_updated_by => l_attached_doc_pre_upd.last_updated_by
1564 ,x_seq_num => l_attached_doc_pre_upd.seq_num
1565 ,x_entity_name => p_entity_name
1566 ,x_column1 => l_attached_doc_pre_upd.column1
1567 ,x_pk1_value => p_pk1_value
1568 ,x_pk2_value => l_attached_doc_pre_upd.pk2_value
1569 ,x_pk3_value => l_attached_doc_pre_upd.pk3_value
1570 ,x_pk4_value => l_attached_doc_pre_upd.pk4_value
1571 ,x_pk5_value => l_attached_doc_pre_upd.pk5_value
1572 ,x_automatically_added_flag =>
1573 l_attached_doc_pre_upd.automatically_added_flag
1574 ,x_attribute_category =>
1575 l_attached_doc_pre_upd.attribute_category
1576 ,x_attribute1 => l_attached_doc_pre_upd.attribute1
1577 ,x_attribute2 => l_attached_doc_pre_upd.attribute2
1578 ,x_attribute3 => l_attached_doc_pre_upd.attribute3
1579 ,x_attribute4 => l_attached_doc_pre_upd.attribute4
1580 ,x_attribute5 => l_attached_doc_pre_upd.attribute5
1581 ,x_attribute6 => l_attached_doc_pre_upd.attribute6
1582 ,x_attribute7 => l_attached_doc_pre_upd.attribute7
1583 ,x_attribute8 => l_attached_doc_pre_upd.attribute8
1584 ,x_attribute9 => l_attached_doc_pre_upd.attribute9
1585 ,x_attribute10 => l_attached_doc_pre_upd.attribute10
1586 ,x_attribute11 => l_attached_doc_pre_upd.attribute11
1587 ,x_attribute12 => l_attached_doc_pre_upd.attribute12
1588 ,x_attribute13 => l_attached_doc_pre_upd.attribute13
1589 ,x_attribute14 => l_attached_doc_pre_upd.attribute14
1590 ,x_attribute15 => l_attached_doc_pre_upd.attribute15
1591
1592 ,x_datatype_id => l_doc_pre_upd.datatype_id
1593 ,x_category_id => l_doc_pre_upd.category_id
1594 ,x_security_type => l_doc_pre_upd.security_type
1595 ,x_security_id => l_doc_pre_upd.security_id
1596 ,x_publish_flag => l_doc_pre_upd.publish_flag
1597 ,x_image_type => l_doc_pre_upd.image_type
1598 ,x_storage_type => l_doc_pre_upd.storage_type
1599 ,x_usage_type => l_doc_pre_upd.usage_type
1600 ,x_start_date_active => trunc(sysdate)
1601 ,x_end_date_active => l_doc_pre_upd.end_date_active
1602 ,x_language => l_language
1603 ,x_description => l_doc_tl_pre_upd.description
1604 ,x_file_name => l_doc_pre_upd.file_name
1605 ,x_media_id => l_doc_pre_upd.media_id
1606 ,x_doc_attribute_category =>
1607 l_doc_tl_pre_upd.doc_attribute_category
1608 ,x_doc_attribute1 => l_doc_tl_pre_upd.doc_attribute1
1609 ,x_doc_attribute2 => l_doc_tl_pre_upd.doc_attribute2
1610 ,x_doc_attribute3 => l_doc_tl_pre_upd.doc_attribute3
1611 ,x_doc_attribute4 => l_doc_tl_pre_upd.doc_attribute4
1612 ,x_doc_attribute5 => l_doc_tl_pre_upd.doc_attribute5
1613 ,x_doc_attribute6 => l_doc_tl_pre_upd.doc_attribute6
1614 ,x_doc_attribute7 => l_doc_tl_pre_upd.doc_attribute7
1615 ,x_doc_attribute8 => l_doc_tl_pre_upd.doc_attribute8
1616 ,x_doc_attribute9 => l_doc_tl_pre_upd.doc_attribute9
1617 ,x_doc_attribute10 => l_doc_tl_pre_upd.doc_attribute10
1618 ,x_doc_attribute11 => l_doc_tl_pre_upd.doc_attribute11
1619 ,x_doc_attribute12 => l_doc_tl_pre_upd.doc_attribute12
1620 ,x_doc_attribute13 => l_doc_tl_pre_upd.doc_attribute13
1621 ,x_doc_attribute14 => l_doc_tl_pre_upd.doc_attribute14
1622 ,x_doc_attribute15 => l_doc_tl_pre_upd.doc_attribute15
1623 ,x_url => l_doc_pre_upd.url
1624 ,x_title => l_doc_tl_pre_upd.title
1625 );
1626
1627 hr_utility.set_location(' after fnd_attached_documents_pkg.update_row :' || l_proc,40);
1628 hr_utility.set_location(' Leaving:' || l_proc,50);
1629
1630 EXCEPTION
1631 when others then
1632 hr_utility.set_location(' Error in :' || l_proc,60);
1633 raise;
1634 End update_attachment;
1635
1636 procedure merge_attachments (
1637 p_source_entity_name in varchar2 default 'PQH_SS_ATTACHMENT'
1638 ,p_dest_entity_name in varchar2
1639 ,p_source_pk1_value in varchar2 default null
1640 ,p_dest_pk1_value in varchar2
1641 ,p_return_status in out nocopy varchar2 )
1642 is
1643
1644 l_rowid varchar2(50);
1645 l_proc varchar2(72) := g_package ||'merge_attachments';
1646 l_source_pk1_value varchar2(100) := null;
1647 l_item_type hr_api_transactions.item_type%type := null;
1648 l_item_key hr_api_transactions.item_key%type := null;
1649 /*Added following variables as a part of ER#9879782 Starts*/
1650 l_person_id NUMBER := null;
1651 l_irc_doc_id NUMBER := null;
1652 /*Added following variables as a part of ER#9879782 Ends*/
1653
1654
1655 cursor csr_get_attached_doc(source_pk1_value in varchar2) is
1656 select *
1657 from fnd_attached_documents
1658 where entity_name=p_source_entity_name
1659 and pk1_value=source_pk1_value;
1660
1661 CURSOR C (X_attached_document_id in number) IS
1662 SELECT rowid
1663 FROM fnd_attached_documents
1664 WHERE attached_document_id = X_attached_document_id;
1665
1666 cursor csr_get_itemkey(source_pk1_value in varchar2) is
1667 select item_type, item_key
1668 from hr_api_transactions
1669 where transaction_id = source_pk1_value;
1670
1671 begin
1672 savepoint merge_attachments;
1673 hr_multi_message.enable_message_list;
1674
1675 if(p_source_pk1_value is null) then
1676 l_source_pk1_value := hr_transaction_swi.g_txn_ctx.TRANSACTION_ID;
1677 else
1678 l_source_pk1_value := p_source_pk1_value;
1679 end if;
1680
1681 if (getAttachToEntity(l_source_pk1_value)) then
1682
1683 open csr_get_itemkey(l_source_pk1_value);
1684 fetch csr_get_itemkey into l_item_type, l_item_key;
1685 CLOSE csr_get_itemkey;
1686
1687 for attached_documents_rec in csr_get_attached_doc(l_source_pk1_value) loop
1688 OPEN C (attached_documents_rec.attached_document_id);
1689 FETCH C INTO l_rowid;
1690 if (C%NOTFOUND) then
1691 CLOSE C;
1692 RAISE NO_DATA_FOUND;
1693 end if;
1694 CLOSE C;
1695 /*Added following code as a part of ER#9879782 Starts*/
1696 BEGIN
1697 SELECT hat.SELECTED_PERSON_ID, hatv2.number_value irc_doc_id
1698 INTO l_person_id,l_irc_doc_id
1699 FROM hr_api_transactions hat
1700 , fnd_attached_documents fads
1701 , hr_api_transaction_steps hats
1702 , hr_api_transaction_values hatv1
1703 , hr_api_transaction_values hatv2
1704 WHERE hat.transaction_id = l_source_pk1_value
1705 AND hats.transaction_id = hat.transaction_id
1706 AND hats.api_name = 'HR_PROCESS_PERSON_SS.PROCESS_API'
1707 AND hatv1.transaction_step_id = hats.transaction_step_id
1708 AND fads.attached_document_id = attached_documents_rec.attached_document_id
1709 AND hatv1.name like 'P_FND_DOC_ID%'
1710 AND hatv1.NUMBER_VALUE = fads.document_id
1711 AND hatv2.transaction_step_id = hatv1.transaction_step_id
1712 AND hatv2.name = 'P_IRC_DOC_ID'||substr(hatv1.name,-1,length(hatv1.name));
1713 EXCEPTION
1714 WHEN OTHERS THEN
1715 hr_utility.set_location('Exception when fetching person_id and irc_doc_id ', 10);
1716 l_person_id := null;
1717 l_irc_doc_id := null;
1718 END;
1719 hr_utility.set_location('l_person_id: '||l_person_id, 10);
1720 hr_utility.set_location('l_irc_doc_id: '||l_irc_doc_id, 10);
1721
1722 IF l_person_id = p_dest_pk1_value
1723 and fnd_profile.value('PER_UPLOAD_IREC_DOC') = 'Y'
1724 THEN
1725 hr_utility.set_location('Entering: IF l_person_id <> null ', 10);
1726 IF(per_fnd_attachment_pkg.is_iRec_doc_exist (l_person_id , l_irc_doc_id ))
1727 THEN
1728 hr_utility.set_location('Entering: IF(per_fnd_attachment_pkg.is_iRec_doc_exist (l_person_id , l_irc_doc_id )) ', 20);
1729
1730 /*start - added for bug 14277455*/
1731 per_fnd_attachment_pkg.update_on_link_table(l_irc_doc_id,l_person_id, attached_documents_rec);
1732 /*end - added for bug 14277455*/
1733
1734
1735 ELSE
1736 hr_utility.set_location('Entering: ELSE OF IF(per_fnd_attachment_pkg.is_iRec_doc_exist (l_person_id , l_irc_doc_id )) ', 20);
1737 update_attachment
1738 (p_entity_name=>p_dest_entity_name
1739 ,p_pk1_value=> p_dest_pk1_value
1740 ,p_rowid=>l_rowid);
1741 END IF;
1742 ELSE
1743 hr_utility.set_location('Entering: ELSE OF IF l_person_id <> null ', 20);
1744 update_attachment
1745 (p_entity_name=>p_dest_entity_name
1746 ,p_pk1_value=> p_dest_pk1_value
1747 ,p_rowid=>l_rowid);
1748 END IF;
1749
1750 /*Added following Code as a part of ER#9879782 Ends*/
1751
1752 if(l_item_key is not null) then
1753 wf_engine.setitemattrtext
1754 (itemtype => l_item_type
1755 ,itemkey => l_item_key
1756 ,aname => 'HR_NTF_ATTACHMENTS_ATTR'
1757 ,avalue =>'FND:entity=' || p_dest_entity_name ||'&pk1name=TransactionId&pk1value=' || p_dest_pk1_value);
1758 end if;
1759
1760
1761 end loop;
1762
1763 end if;
1764
1765 p_return_status := hr_multi_message.get_return_status_disable;
1766 exception
1767 when others then
1768 rollback to merge_attachments;
1769 if hr_multi_message.unexpected_error_add(l_proc) then
1770 raise;
1771 end if;
1772 p_return_status := hr_multi_message.get_return_status_disable;
1773
1774 end merge_attachments;
1775
1776 function getAttachToEntity(p_transaction_id in number)
1777 return boolean
1778 is
1779
1780 c_proc constant varchar2(30) := 'getAttachToEntity';
1781 lr_hr_api_transaction_rec hr_api_transactions%rowtype;
1782 l_save_attachment_old varchar2(30) := null;
1783 l_save_attachment_new varchar2(30) := null;
1784 l_save_attach boolean;
1785 rootNode xmldom.DOMNode;
1786 l_Attach_Node xmldom.DOMNode;
1787 l_TransCtx_Node xmldom.DOMNode;
1788 l_TransCtx_NodeList xmldom.DOMNodeList;
1789 l_Attach_NodeList xmldom.DOMNodeList;
1790
1791 cursor csr_trans_rec is
1792 select *
1793 from hr_api_transactions
1794 where transaction_id = p_transaction_id;
1795
1796
1797 begin
1798
1799 if g_debug then
1800 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
1801 end if;
1802
1803 if(p_transaction_id is not null) then
1804
1805 --Added this cusror to support Delete Absence flow.
1806 --In Delete Absence after process_api is called the transaction will
1807 --be deleted if OTL Integration is on and we cannot find any transaction
1808 --so in such case return false.
1809 --Other products can also have such kind of scenarios.
1810
1811 open csr_trans_rec;
1812 fetch csr_trans_rec into lr_hr_api_transaction_rec;
1813 if (csr_trans_rec%NOTFOUND) then
1814 CLOSE csr_trans_rec;
1815 hr_utility.set_location('Transaction not found',5);
1816 return false;
1817 end if;
1818 close csr_trans_rec;
1819
1820 l_save_attach := false;
1821
1822 if( lr_hr_api_transaction_rec.transaction_document is not null) then
1823
1824 rootNode := xmldom.makeNode(hr_transaction_swi.convertCLOBtoXMLElement(lr_hr_api_transaction_rec.transaction_document));
1825 l_TransCtx_NodeList :=xmldom.getChildrenByTagName(xmldom.makeElement(rootNode),'TransCtx');
1826
1827 IF (xmldom.getLength(l_TransCtx_NodeList) > 0) THEN
1828 l_TransCtx_Node :=xmldom.item(l_TransCtx_NodeList,0);
1829 l_Attach_NodeList :=xmldom.getChildrenByTagName(xmldom.makeElement(l_TransCtx_Node),'AttachCheck');
1830 END IF;
1831
1832 l_Attach_Node := xmldom.item(l_Attach_NodeList,0);
1833 l_Attach_Node := xmldom.getFirstChild(l_Attach_Node);
1834 l_save_attachment_new :=xmldom.getNodeValue(l_Attach_Node);
1835 else
1836
1837 l_save_attachment_old := wf_engine.getitemattrtext(lr_hr_api_transaction_rec.item_type,
1838 lr_hr_api_transaction_rec.item_key,
1839 'SAVE_ATTACHMENT',true);
1840 end if;
1841
1842 if( (l_save_attachment_old is not null) AND (l_save_attachment_old = 'Y') )then
1843 l_save_attach := true;
1844 end if;
1845
1846 if( (l_save_attachment_new is not null) AND (l_save_attachment_new = 'Y') ) then
1847 l_save_attach := true;
1848 end if;
1849
1850 return l_save_attach;
1851
1852 if (g_debug ) then
1853 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 10);
1854 end if;
1855 end if;
1856
1857 exception
1858 when others then
1859 raise;
1860
1861 end getAttachToEntity;
1862
1863 procedure saveAttachment(p_transaction_id in number
1864 ,p_return_status out nocopy varchar2)
1865 is
1866
1867 c_proc constant varchar2(30) := 'saveAttachment';
1868 lr_hr_api_transaction_rec hr_api_transactions%rowtype;
1869 l_api_name varchar2(100) := null;
1870 l_entity_name varchar2(30) := null;
1871 l_dest_pk1_val varchar2(30) := null;
1872 l_return_status varchar2(30) := null;
1873 l_pk_value varchar2(30) := null;
1874 l_period_of_serv_id Number := null;
1875 /*Added following variables as a part of ER#9879782 Starts*/
1876 l_apl_flag Number := 0;
1877 l_person_id Number:= 0;
1878 l_doc_upload_date Date:= null;
1879 l_assignment_id Number:= 0;
1880 l_application_id Number:= 0;
1881 l_irec_doc_id Number := 0;
1882 l_fnd_doc_id Number :=0;
1883 l_fnd_doc_link_id Number := 0;
1884 l_fnd_doc_link_ovn Number:=0;
1885 /*Added following variables as a part of ER#9879782 Ends*/
1886
1887 cursor csr_hat_steps is
1888 select *
1889 from hr_api_transaction_steps
1890 where transaction_id=p_transaction_id;
1891
1892 step_row csr_hat_steps%ROWTYPE;
1893
1894 cursor csr_hat is
1895 select hat.transaction_id,
1896 hat.assignment_id,
1897 hat.selected_person_id,
1898 hat.transaction_ref_id
1899 from hr_api_transactions hat
1900 where hat.transaction_id =p_transaction_id;
1901
1902 trans_row csr_hat%rowtype;
1903 /*Added following cursor as a part of ER#9879782 Starts*/
1904 cursor csr_apl_hire_txn_vals(x_transaction_step_id NUMBER)
1905 is
1906 SELECT hatv1.NUMBER_VALUE IRC_DOC_ID, hatv2.NUMBER_VALUE FND_DOC_ID
1907 FROM hr_api_transaction_values hatv1, hr_api_transaction_values hatv2
1908 WHERE hatv1.transaction_step_id = x_transaction_step_id
1909 and hatv1.NAME like 'P_IRC_DOC_ID%'
1910 and hatv1.NUMBER_VALUE is not null
1911 and hatv2.transaction_step_id = hatv1.transaction_step_id
1912 and hatv2.NAME = 'P_FND_DOC_ID'||substr(hatv1.name,-1,length(hatv1.name))
1913 and hatv2.NUMBER_VALUE is not null;
1914 /*Added following cursor as a part of ER#9879782 Ends*/
1915
1916
1917 begin
1918 hr_utility.set_location('In saveAttachment', 1);
1919 if(p_transaction_id is not null) then
1920
1921 hr_utility.set_location('p_transaction_id :' || p_transaction_id, 1);
1922
1923 if (getAttachToEntity(p_transaction_id)) then
1924 hr_utility.set_location('In getAttachToEntity true', 1);
1925
1926 OPEN csr_hat;
1927 FETCH csr_hat INTO trans_row ;
1928 CLOSE csr_hat;
1929
1930 OPEN csr_hat_steps;
1931 FETCH csr_hat_steps INTO step_row ;
1932 CLOSE csr_hat_steps;
1933
1934 l_api_name := step_row.api_name;
1935
1936 hr_utility.set_location('l_api_name :' || l_api_name, 1);
1937
1938 if(l_api_name = 'HR_LOA_SS.PROCESS_API' OR
1939 l_api_name = 'HR_PERSON_ABSENCE_SWI.PROCESS_API') then
1940
1941 l_entity_name := 'PER_ABSENCE_ATTENDANCES';
1942 l_pk_value := trans_row.transaction_ref_id;
1943
1944 elsif(l_api_name = 'HR_PROCESS_ASSIGNMENT_SS.PROCESS_API' OR
1945 l_api_name = 'HR_SUPERVISOR_SS.PROCESS_API' OR
1946 l_api_name = 'HR_PAY_RATE_SS.PROCESS_API' OR
1947 l_api_name = 'PER_SSHR_CHANGE_PAY.PROCESS_API' OR --fix for bug 11065050
1948 l_api_name = 'BEN_PROCESS_COMPENSATION_W.PROCESS_API') then --fix for bug 14277455
1949
1950 l_entity_name := 'PER_ASSIGNMENTS_F';
1951 l_pk_value := trans_row.assignment_id;
1952
1953 elsif(l_api_name = 'HR_PROCESS_PERSON_SS.PROCESS_API' OR
1954 l_api_name = 'HR_PROCESS_SIT_SS.PROCESS_API' OR
1955 l_api_name = 'HR_PROCESS_EIT_SS.PROCESS_API' OR
1956 l_api_name = 'HR_CAED_SS.PROCESS_API' OR
1957 l_api_name = 'HR_PROCESS_PHONE_NUMBERS_SS.PROCESS_API' OR
1958 l_api_name = 'HR_PROCESS_ADDRESS_SS.PROCESS_API' OR
1959 l_api_name = 'HR_PROCESS_CONTACT_SS.PROCESS_CREATE_CONTACT_API'OR
1960 --Code Fix for Bug 11677614
1961 l_api_name = 'HR_PROCESS_CEI_SS.PROCESS_API' OR
1962 --End of code Fix for Bug 11677614
1963
1964 --Code Fix for Bug 14085358
1965 l_api_name = 'HR_PROCESS_CONTACT_SS.PROCESS_API') then
1966 --End of Code Fix for Bug 14085358
1967
1968 l_entity_name := 'PER_PEOPLE_F';
1969 l_pk_value := trans_row.selected_person_id;
1970
1971
1972 if( l_pk_value is null) then
1973 hr_utility.set_location('l_pk_value is null' , 1);
1974
1975 l_pk_value := hr_process_person_ss.g_person_id;
1976 end if;
1977
1978 elsif(l_api_name = 'HR_TERMINATION_SS.PROCESS_API') then
1979
1980 select number_value into l_period_of_serv_id from
1981 hr_api_transaction_values
1982 where TRANSACTION_STEP_ID = step_row.TRANSACTION_STEP_ID
1983 and NAME = 'P_PERIOD_OF_SERVICE_ID';
1984
1985 l_entity_name := 'PER_PERIODS_OF_SERVICE';
1986 l_pk_value := l_period_of_serv_id;
1987
1988 elsif(l_api_name = 'HR_REVTERMINATION_SS.PROCESS_API') then
1989
1990 select number_value into l_period_of_serv_id from
1991 hr_api_transaction_values
1992 where TRANSACTION_STEP_ID = step_row.TRANSACTION_STEP_ID
1993 and NAME = 'P_PERIODS_OF_SERVICE_ID';
1994
1995 l_entity_name := 'PER_PERIODS_OF_SERVICE';
1996 l_pk_value := l_period_of_serv_id;
1997
1998 end if;
1999
2000 hr_utility.set_location('l_pk_value :' || l_pk_value, 1);
2001
2002
2003 merge_attachments (p_dest_entity_name => l_entity_name
2004 ,p_source_pk1_value => trans_row.transaction_id
2005 ,p_dest_pk1_value => l_pk_value
2006 ,p_return_status => l_return_status);
2007
2008 /* Added the following code as a part of ER#9879782 Starts*/
2009 IF l_api_name = 'HR_PROCESS_PERSON_SS.PROCESS_API'
2010 and fnd_profile.value('PER_UPLOAD_IREC_DOC') = 'Y'
2011 THEN
2012 BEGIN
2013 SELECT COUNT(1)
2014 INTO l_apl_flag
2015 FROM hr_api_transaction_values
2016 WHERE transaction_step_id = step_row.TRANSACTION_STEP_ID
2017 AND NAME = 'P_APPLICANT_NUMBER'
2018 AND VARCHAR2_VALUE IS NOT NULL;
2019 EXCEPTION WHEN OTHERS THEN
2020 l_apl_flag := 0;
2021 END;
2022 IF l_apl_flag <> 0 THEN
2023 BEGIN
2024 select number_value into l_person_id from
2025 hr_api_transaction_values
2026 where TRANSACTION_STEP_ID = step_row.TRANSACTION_STEP_ID
2027 and NAME = 'P_PERSON_ID';
2028 EXCEPTION WHEN OTHERS
2029 THEN
2030 l_person_id := null;
2031 END;
2032 BEGIN
2033 select date_value into l_doc_upload_date from
2034 hr_api_transaction_values
2035 where TRANSACTION_STEP_ID = step_row.TRANSACTION_STEP_ID
2036 and NAME = 'P_EFFECTIVE_DATE';
2037 EXCEPTION WHEN OTHERS
2038 THEN
2039 l_doc_upload_date := null;
2040 END;
2041 BEGIN
2042 select number_value into l_assignment_id from
2043 hr_api_transaction_values
2044 where TRANSACTION_STEP_ID = step_row.TRANSACTION_STEP_ID
2045 and NAME = 'P_APPL_ASSIGNMENT_ID';
2046 EXCEPTION WHEN OTHERS
2047 THEN
2048 l_assignment_id := null;
2049 END;
2050 BEGIN
2051 SELECT application_id
2052 INTO l_application_id
2053 FROM per_all_assignments_f
2054 WHERE assignment_id = l_assignment_id;
2055 /*AND sysdate
2056 BETWEEN effective_start_date
2057 AND effective_end_date;*/
2058 EXCEPTION WHEN OTHERS
2059 THEN
2060 l_application_id := null;
2061 END;
2062
2063 OPEN csr_apl_hire_txn_vals(step_row.TRANSACTION_STEP_ID);
2064 LOOP
2065 FETCH csr_apl_hire_txn_vals into l_irec_doc_id,l_fnd_doc_id;
2066 EXIT WHEN csr_apl_hire_txn_vals%NOTFOUND;
2067 IF NOT per_fnd_attachment_pkg.is_iRec_doc_exist (l_person_id , l_irec_doc_id ) THEN
2068 per_fnd_attachment_pkg.dml_on_link_table (
2069 p_entity_name=>'SSHR',
2070 p_sub_entity_name=>'IRC_ATTCH',
2071 p_person_id=>l_person_id,
2072 p_doc_upload_date=> l_doc_upload_date,
2073 p_transaction_type=> 'SSHR',
2074 p_transaction_status=> 'COMPLETE',
2075 p_entity_doc_id=>l_irec_doc_id,
2076 p_fnd_doc_id=>l_fnd_doc_id,
2077 p_assignment_id=>l_assignment_id,
2078 p_application_id=>l_application_id,
2079 p_fnd_doc_link_id=>l_fnd_doc_link_id,
2080 p_object_version_number=>l_fnd_doc_link_ovn,
2081 p_mode=> 'INSERT');
2082 END IF;
2083 END LOOP;
2084 CLOSE csr_apl_hire_txn_vals;
2085 END IF;
2086 END IF;
2087 /* Added the following code as a part of ER#9879782 Ends*/
2088 p_return_status := l_return_status;
2089
2090 end if;
2091 end if;
2092 exception
2093 when others then
2094 raise;
2095
2096 end saveAttachment;
2097
2098 function getUpgradeCheck(p_transaction_id in number) return varchar2
2099
2100 IS
2101 c_proc constant varchar2(30) := 'getUpgradeCheck';
2102 lv_is_upgrade hr_api_transaction_steps.Information30%type;
2103 begin
2104 g_debug := hr_utility.debug_enabled;
2105 if g_debug then
2106 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
2107 end if;
2108
2109 if(p_transaction_id is not null) then
2110 begin
2111 select Information30
2112 into lv_is_upgrade
2113 from hr_api_transaction_steps
2114 where transaction_id=p_transaction_id;
2115
2116 exception
2117 when others then
2118 null;
2119 lv_is_upgrade:=null;
2120 end;
2121 end if;
2122
2123 if g_debug then
2124 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 30);
2125 end if;
2126
2127 return lv_is_upgrade;
2128
2129 exception
2130 when others then
2131 hr_utility.set_location(g_package||c_proc|| 'errored : '||SQLERRM ||' '||to_char(SQLCODE), 30);
2132 Wf_Core.Context(g_package, c_proc, p_transaction_id);
2133 -- raise;
2134 return null;
2135 end getUpgradeCheck;
2136
2137 function getJobName (p_job_id in number, p_bg_id in number)
2138 return varchar2
2139 is
2140 --jname varchar2(200) := '';
2141 jname per_jobs_tl.name%type := ''; --14592612
2142 begin
2143
2144 select name
2145 into jname
2146 from per_jobs_tl
2147 where p_job_id = job_id
2148 and language(+) = userenv('LANG');
2149
2150 return hr_util_misc_ss.getObjectName('JOB', p_job_id, p_bg_id, jname);
2151
2152 end getJobName;
2153
2154
2155 function getPositionName (p_position_id in number, p_bg_id in number)
2156 return varchar2
2157 is
2158 --pname varchar2(200) := '';
2159 pname hr_all_positions_f_tl.name%type := ''; --14592612
2160 begin
2161
2162 select name
2163 into pname
2164 from hr_all_positions_f_tl
2165 where p_position_id = position_id
2166 and language(+) = userenv('LANG');
2167
2168 return hr_util_misc_ss.getObjectName('POSITION', p_position_id, p_bg_id, pname);
2169
2170 end getPositionName;
2171
2172
2173 function getGradeName (p_grade_id in number, p_bg_id in number)
2174 return varchar2
2175 is
2176 --gname varchar2(200) := '';
2177 gname per_grades_tl.name%type := ''; --14592612
2178 begin
2179
2180 select name
2181 into gname
2182 from per_grades_tl
2183 where p_grade_id = grade_id
2184 and language(+) = userenv('LANG');
2185
2186 return hr_util_misc_ss.getObjectName('GRADE', p_grade_id, p_bg_id, gname);
2187
2188 end getGradeName;
2189
2190
2191 function getOrgName (p_org_id in number, p_bg_id in number)
2192 return varchar2
2193 is
2194 --oname varchar2(200) := '';
2195 oname hr_all_organization_units_tl.name%type := ''; --14592612
2196 begin
2197
2198 select name
2199 into oname
2200 from hr_all_organization_units_tl
2201 where p_org_id = organization_id
2202 and language(+) = userenv('LANG');
2203
2204 return oname;
2205
2206 end getOrgName;
2207
2208 function getLocName (p_loc_id in number, p_bg_id in number)
2209 return varchar2
2210 is
2211 --lname varchar2(200) := '';
2212 lname hr_locations_all_tl.location_code%type := ''; --14592612
2213 begin
2214
2215 select location_code
2216 into lname
2217 from hr_locations_all_tl
2218 where p_loc_id = location_id
2219 and language(+) = userenv('LANG');
2220
2221 return lname;
2222
2223 end getLocName;
2224
2225 PROCEDURE is_us_emp_check (
2226 itemtype in varchar2,
2227 itemkey in varchar2,
2228 actid in number,
2229 funcmode in varchar2,
2230 resultout out nocopy varchar2
2231 )
2232 IS
2233 l_person_id wf_activity_attr_values.number_value%type;
2234 l_business_grp_id wf_activity_attr_values.number_value%type;
2235 l_leg_code varchar2(100) := null;
2236 l_transaction_step_id number;
2237
2238 l_proc constant varchar2(100) := g_package || ' is_us_emp_check';
2239 l_eth_dff_exists varchar2(30):= null;
2240
2241 cursor csr_txn_step_id is
2242 select trs.transaction_step_id
2243 from hr_api_transaction_steps trs
2244 where trs.transaction_id = hr_transaction_ss.get_transaction_id
2245 (itemtype ,itemkey )
2246 and trs.api_name ='HR_PROCESS_PERSON_SS.PROCESS_API';
2247
2248 cursor ethnic_dff is
2249 select DESCRIPTIVE_FLEXFIELD_NAME from FND_DESCR_FLEX_CONTEXTS
2250 where DESCRIPTIVE_FLEXFIELD_NAME = 'Extra Person Info DDF'
2251 and DESCRIPTIVE_FLEX_CONTEXT_CODE = 'US_ETHNIC_ORIGIN';
2252
2253 BEGIN
2254 hr_utility.set_location('Entering: '|| l_proc,5);
2255
2256 OPEN ethnic_dff;
2257 FETCH ethnic_dff INTO l_eth_dff_exists;
2258
2259 IF ( ethnic_dff%FOUND ) THEN
2260
2261 l_person_id := wf_engine.GetItemAttrNumber (
2262 itemtype => itemtype,
2263 itemkey => itemkey,
2264 aname => 'CURRENT_PERSON_ID');
2265
2266 hr_utility.set_location('l_person_id: '|| l_person_id,5);
2267
2268 if l_person_id is null then
2269
2270 hr_utility.set_location('l_person_id: is not null',5);
2271 open csr_txn_step_id;
2272 fetch csr_txn_step_id into l_transaction_step_id;
2273 close csr_txn_step_id;
2274
2275 l_business_grp_Id :=hr_transaction_api.get_number_value
2276 (p_transaction_step_id => l_transaction_step_id
2277 ,p_name => 'P_BUSINESS_GROUP_ID');
2278
2279 hr_utility.set_location('l_business_grp_Id: ' || l_business_grp_Id,5);
2280
2281 else
2282
2283 select BUSINESS_GROUP_ID into l_business_grp_id from per_all_people_f where
2284 person_id = l_person_id and trunc(sysdate) between effective_start_date and effective_end_date;
2285
2286 hr_utility.set_location('l_business_grp_Id: ' || l_business_grp_Id,6);
2287 end if;
2288
2289
2290 l_leg_code := hr_api.return_legislation_code(l_business_grp_id);
2291
2292 hr_utility.set_location('l_leg_code: ' || l_leg_code,5);
2293
2294
2295 if l_leg_code = 'US'
2296 then
2297 resultout := 'COMPLETE:'|| 'Y';
2298 else
2299 resultout := 'COMPLETE:'|| 'N';
2300 end if;
2301
2302 ELSE
2303 resultout := 'COMPLETE:'|| 'N';
2304 END IF;
2305 --
2306 hr_utility.set_location('Leaving: '|| l_proc,10);
2307
2308 EXCEPTION
2309 WHEN OTHERS THEN
2310
2311 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
2312 WF_CORE.CONTEXT (
2313 g_package,
2314 'is_us_emp_check',
2315 itemtype,
2316 itemkey,
2317 to_char(actid),
2318 funcmode);
2319 RAISE;
2320 END is_us_emp_check;
2321
2322 function is_basic_det_chg (
2323 p_person_id in number
2324 ) return varchar2
2325 IS
2326 l_person_id number;
2327 l_business_group_id number ;
2328 l_leg_code varchar2(100) := null;
2329
2330 l_proc constant varchar2(100) := g_package || ' is_basic_det_chg';
2331
2332 BEGIN
2333 hr_utility.set_location('Entering: '|| l_proc,5);
2334
2335
2336
2337 select BUSINESS_GROUP_ID into l_business_group_id from per_all_people_f where
2338 person_id = p_person_id and trunc(sysdate) between effective_start_date and effective_end_date;
2339
2340 hr_utility.set_location('l_business_group_id: ' || l_business_group_id,6);
2341
2342 l_leg_code := hr_api.return_legislation_code(l_business_group_id);
2343
2344 hr_utility.set_location('l_leg_code: ' || l_leg_code,5);
2345
2346
2347 if l_leg_code = 'US'
2348 then
2349 return 'Y';
2350 else
2351 return 'N';
2352 end if;
2353 --
2354 hr_utility.set_location('Leaving: '|| l_proc,10);
2355
2356 EXCEPTION
2357 WHEN OTHERS THEN
2358
2359 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
2360 RAISE;
2361 END is_basic_det_chg;
2362
2363
2364 function getJobNameNew (p_rptg_grp_id in number,p_plan_id in number,p_job_id in
2365 number, p_bg_id in number)
2366 return varchar2
2367 is
2368 jname varchar2(200) := '';
2369 l_number_value number(10):= -1;
2370 l_job_id number(10):=-1;
2371 l_transaction_step_id number(10):=-1;
2372 l_asg_id number(10);
2373 --l_job_name varchar2(100) := null;
2374 l_job_name per_jobs_tl.name%type := ''; --14592612
2375 begin
2376 -- hr_utility.trace_on(null,'ORCL');
2377 hr_utility.set_location('Entering: getJobNameNew', 30);
2378 hr_utility.set_location(' p_rptg_grp_id' ||p_rptg_grp_id, 30);
2379 -- if rptg_grp_id is null
2380
2381 if(p_rptg_grp_id is not null and p_plan_id is null) then
2382 begin
2383 hr_utility.set_location('Before selecting job_id from paf', 30);
2384 select job_id into l_job_id from per_all_assignments_f paf
2385 where assignment_id = p_rptg_grp_id
2386 and sysdate between paf.effective_start_date and paf.effective_end_date;
2387 exception
2388 when others then
2389 hr_utility.set_location('E2 :'|| 'An error was encountered -'||SQLCODE||' -ERROR- '||SQLERRM, 30);
2390
2391 end;
2392 if(l_job_id is not null) then
2393 hr_utility.set_location('before calling jobname the second time', 30);
2394 l_job_name := getJobName(l_job_id, p_bg_id);
2395 hr_utility.set_location('After getJob name 2 l_job_name:'|| l_job_name, 30);
2396 end if;
2397
2398
2399 else
2400
2401
2402 l_job_name:= getJobName(p_job_id, p_bg_id);
2403
2404
2405
2406 end if;
2407
2408 return l_job_name;
2409
2410
2411
2412 end getJobNameNew;
2413
2414
2415
2416
2417 function getPositionNameNew (p_rptg_grp_id in number,p_plan_id in
2418 number,p_position_id in number, p_bg_id in number)
2419 return varchar2
2420 is
2421
2422 --l_position_id number(10):=-1;
2423 l_position_id per_all_assignments_f.position_id%type := -1; --14592612
2424 --l_position_name varchar2(100) := null;
2425 l_position_name hr_all_positions_f_tl.name%type := ''; --14592612
2426 begin
2427
2428 if(p_rptg_grp_id is not null and p_plan_id is null) then
2429 begin
2430
2431 select position_id into l_position_id from per_all_assignments_f paf
2432 where assignment_id = p_rptg_grp_id
2433 and sysdate between paf.effective_start_date and paf.effective_end_date;
2434
2435 exception
2436 when others then
2437 hr_utility.set_location('E2 :'|| 'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM, 30);
2438
2439 end;
2440 if(l_position_id is not null) then
2441 l_position_name := getPositionName(l_position_id, p_bg_id);
2442 end if;
2443 else
2444 l_position_name:= getPositionName(p_position_id, p_bg_id);
2445
2446 end if;
2447
2448 return l_position_name;
2449
2450 end getPositionNameNew;
2451
2452
2453 function getGradeNameNew (p_rptg_grp_id in number,p_plan_id in number,p_grade_id
2454 in number, p_bg_id in number)
2455 return varchar2
2456 is
2457
2458 --l_grade_id number(10):=-1;
2459 l_grade_id per_all_assignments_f.grade_id%type := -1; --14592612
2460 --l_grade_name varchar2(100) := null;
2461 l_grade_name per_grades_tl.name%type := ''; --14592612
2462 begin
2463
2464 if(p_rptg_grp_id is not null and p_plan_id is null) then
2465 begin
2466
2467 select grade_id into l_grade_id from per_all_assignments_f paf
2468 where assignment_id = p_rptg_grp_id
2469 and sysdate between paf.effective_start_date and paf.effective_end_date;
2470
2471 exception
2472 when others then
2473 hr_utility.set_location('E2 :'|| 'An error was encountered -'||SQLCODE||' -ERROR- '||SQLERRM, 30);
2474
2475 end;
2476 if(l_grade_id is not null) then
2477 l_grade_name := getGradeName(l_grade_id, p_bg_id);
2478 end if;
2479 else
2480 l_grade_name:= getGradeName(p_grade_id, p_bg_id);
2481
2482 end if;
2483
2484 return l_grade_name;
2485
2486 end getGradeNameNew;
2487
2488
2489
2490 function getOrgNameNew (p_rptg_grp_id in number,p_plan_id in number,p_org_id in
2491 number, p_bg_id in number)
2492 return varchar2
2493 is
2494
2495 --l_org_id number(10):=-1;
2496 l_org_id per_all_assignments_f.ORGANIZATION_ID%type := -1; --14592612
2497 --l_org_name varchar2(100) := null;
2498 l_org_name hr_all_organization_units_tl.name%type := ''; --14592612
2499 begin
2500
2501 if(p_rptg_grp_id is not null and p_plan_id is null) then
2502 begin
2503
2504 select ORGANIZATION_ID into l_org_id from per_all_assignments_f paf
2505 where assignment_id = p_rptg_grp_id
2506 and sysdate between paf.effective_start_date and paf.effective_end_date;
2507
2508 exception
2509 when others then
2510 hr_utility.set_location('E2 :'|| 'An error was encountered -'||SQLCODE||' -ERROR- '||SQLERRM, 30);
2511
2512 end;
2513 if(l_org_id is not null) then
2514 l_org_name := getOrgName(l_org_id, p_bg_id);
2515 end if;
2516 else
2517 l_org_name:= getOrgName(p_org_id, p_bg_id);
2518
2519 end if;
2520
2521 return l_org_name;
2522
2523 end getOrgNameNew;
2524
2525
2526 function getLocNameNew (p_rptg_grp_id in number,p_plan_id in number,p_loc_id in
2527 number, p_bg_id in number)
2528 return varchar2
2529 is
2530
2531 --l_loc_id number(10):=-1;
2532 l_loc_id per_all_assignments_f.Location_ID%type := -1; --14592612
2533 --l_loc_name varchar2(100) := null;
2534 l_loc_name hr_locations_all_tl.location_code%type := ''; --14592612
2535 begin
2536 -- hr_utility.trace_on(null,'ORCL');
2537 if(p_rptg_grp_id is not null and p_plan_id is null) then
2538 begin
2539
2540 select Location_ID into l_loc_id from per_all_assignments_f paf
2541 where assignment_id = p_rptg_grp_id
2542 and sysdate between paf.effective_start_date and paf.effective_end_date;
2543
2544 exception
2545 when others then
2546 hr_utility.set_location('E2 :'|| 'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM, 30);
2547
2548 end;
2549 if(l_loc_id is not null) then
2550 l_loc_name := getLocName(l_loc_id, p_bg_id);
2551 end if;
2552 else
2553 l_loc_name:= getLocName(p_loc_id, p_bg_id);
2554
2555 end if;
2556
2557 return l_loc_name;
2558
2559 end getLocNameNew;
2560
2561
2562 function getManagerName (p_rptg_grp_id in number,p_plan_id in
2563 number,p_assignment_id in number, p_bg_id in number)
2564 return varchar2
2565 is
2566 l_profile_value varchar2(10) := fnd_profile.VALUE('HR_LOCAL_OR_GLOBAL_NAME_FORMAT');
2567 l_manager_id number(10);
2568 l_manager_global_name varchar2(100) := null;
2569 l_manager_local_name varchar2(100) := null;
2570 begin
2571
2572 if(p_rptg_grp_id is not null and p_plan_id is null) then
2573 begin
2574
2575 select distinct(supervisor_id) into l_manager_id from per_all_assignments_f paf where paf.assignment_id = p_rptg_grp_id
2576 and sysdate between paf.effective_start_date and paf.effective_end_date;
2577
2578 exception
2579 when others then
2580 hr_utility.set_location('E2 :'|| 'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM, 30);
2581
2582 end;
2583
2584 else
2585 begin
2586
2587 select distinct(supervisor_id) into l_manager_id from per_all_assignments_f paf where paf.assignment_id = p_assignment_id
2588 and sysdate between paf.effective_start_date and paf.effective_end_date;
2589
2590 exception
2591 when others then
2592 hr_utility.set_location('E3 :'|| 'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM, 30);
2593
2594 end;
2595 end if;
2596
2597
2598
2599 if ( l_manager_id is not null) then
2600 select distinct global_name,local_name into l_manager_global_name,l_manager_local_name from per_all_people_f where person_id = l_manager_id
2601 and trunc(sysdate) between effective_start_date and effective_end_date;
2602 else
2603 return null;
2604 end if;
2605
2606 if(l_profile_value = 'G') then
2607 return l_manager_global_name;
2608 else
2609 return l_manager_local_name;
2610 end if;
2611 end getManagerName;
2612
2613 PROCEDURE get_item_type_and_key (
2614 p_transaction_id IN VARCHAR2
2615 ,p_itemType OUT NOCOPY VARCHAR2
2616 ,p_itemKey OUT NOCOPY VARCHAR2 ) IS
2617 c_proc varchar2(30) default 'get_item_type_and_key';
2618 lv_item_type wf_item_activity_statuses.item_type%type;
2619 lv_item_key wf_item_activity_statuses.item_key%type;
2620 lv_activity_id wf_item_activity_statuses.process_activity%type;
2621 dummy varchar2(10) := null;
2622 begin
2623 g_debug := hr_utility.debug_enabled;
2624 if g_debug then
2625 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
2626 end if;
2627
2628 -- get the itemtype and item key for the notification id
2629 begin
2630 select transaction_item_type, transaction_item_key
2631 into lv_item_type, lv_item_key
2632 from pqh_ss_approval_history
2633 where transaction_history_id = p_transaction_id
2634 and rownum =1;
2635 exception
2636 when no_data_found then
2637 lv_item_type := 'HRSSA';
2638 lv_item_key := '0000000000';
2639 when others then
2640 raise;
2641 end;
2642
2643 begin
2644 select 'x' into dummy from hr_api_transactions
2645 where item_key = lv_item_key
2646 and item_type = lv_item_type
2647 and status <> 'AC';
2648
2649 exception
2650 when no_data_found then
2651 lv_item_type := 'HRSSA';
2652 lv_item_key := '0000000000';
2653 when others then
2654 raise;
2655 end;
2656
2657
2658 p_itemType := lv_item_type;
2659 p_itemKey := lv_item_key;
2660
2661 exception
2662 when others then
2663 hr_utility.set_location('hr_util_misc_ss.get_item_type_and_key errored : '||SQLERRM ||' '||to_char(SQLCODE), 30);
2664 Wf_Core.Context('hr_util_misc_ss', 'get_item_type_and_key', p_transaction_id);
2665 raise;
2666 end get_item_type_and_key;
2667
2668
2669 procedure setApprSFLResponseContext( p_item_type in varchar2,
2670 p_item_key in varchar2,
2671 p_act_id in number,
2672 funmode in varchar2,
2673 result out nocopy varchar2 ) IS
2674 -- local variables
2675 c_proc constant varchar2(30) := 'setApprSFLResponseContext';
2676 c_transaction_id varchar2(25);
2677 CURSOR csr_txn (p_txn_id IN NUMBER)IS
2678 SELECT transaction_ref_table, transaction_ref_id
2679 FROM hr_api_transactions
2680 WHERE transaction_id = p_txn_id;
2681 l_transaction_ref_table VARCHAR2(50);
2682 l_transaction_ref_id NUMBER;
2683 lv_result_code WF_ITEM_ACTIVITY_STATUSES.activity_result_code%type;
2684 begin
2685 g_debug := hr_utility.debug_enabled;
2686
2687 if g_debug then
2688 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
2689 end if;
2690 begin
2691 select text_value
2692 into lv_result_code
2693 from wf_notification_attributes
2694 where notification_id=wf_engine.context_nid
2695 and name='RESULT';
2696
2697 exception
2698 when others then
2699 null;
2700 end;
2701 c_transaction_id := wf_engine.getitemattrnumber(p_item_type,p_item_key,'TRANSACTION_ID');
2702 OPEN csr_txn(c_transaction_id);
2703 FETCH csr_txn INTO l_transaction_ref_table,l_transaction_ref_id;
2704 CLOSE csr_txn;
2705 hr_utility.trace('txn_ref_table: '||l_transaction_ref_table);
2706 IF NVL(l_transaction_ref_table,'XXX') = 'PER_APPRAISALS' and lv_result_code = 'DEL' THEN
2707 hr_utility.trace('returning with resubmit as we donot want SFL deletion for appraisal');
2708 result := 'COMPLETE:XYZ'; --so that this takes the default option in the WF
2709 RETURN;
2710 END IF;
2711 if g_debug then
2712 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 10);
2713 end if;
2714 ---END Changes by KMG
2715 end setApprSFLResponseContext;
2716
2717 function isQkFn
2718 (
2719 fn_name in varchar2
2720 ) return varchar2 is
2721
2722 dummy VARCHAR2(10);
2723 begin
2724
2725
2726 select 'X' into dummy from fnd_form_functions where function_name = fn_name
2727 and parameters like '%pQkFn=Y%';
2728
2729 dummy := null;
2730
2731 return 'Y';
2732 exception
2733 when NO_DATA_FOUND then
2734 return 'N';
2735 end isQkFn;
2736
2737 function isBpRv
2738 (
2739 fn_name in varchar2
2740 ) return varchar2 is
2741
2742 dummy VARCHAR2(10);
2743 begin
2744
2745 select 'X' into dummy from fnd_form_functions where function_name = fn_name
2746 and parameters like '%pBpRv=Y%';
2747
2748 dummy := null;
2749
2750 return 'Y';
2751
2752 exception
2753 when NO_DATA_FOUND then
2754 return 'N';
2755 end isBpRv;
2756
2757 function isEffDateReq
2758 (
2759 fn_name in varchar2
2760 ) return varchar2 is
2761
2762 dummy VARCHAR2(10);
2763
2764 begin
2765
2766
2767 select 'X' into dummy from fnd_form_functions where function_name = fn_name
2768 and parameters like '%pEffectiveDate=%';
2769
2770 dummy := null;
2771
2772 return 'Y';
2773 exception
2774 when NO_DATA_FOUND then
2775
2776 return 'N';
2777 end isEffDateReq;
2778
2779 -- ---------------------------------------------------------------------------
2780 -- |----------------------< get_wf_bprv >--------------------------|
2781 -- ---------------------------------------------------------------------------
2782 --
2783 PROCEDURE get_wf_bprv (
2784 itemtype IN WF_ITEMS.ITEM_TYPE%TYPE,
2785 itemkey IN WF_ITEMS.ITEM_KEY%TYPE,
2786 actid IN NUMBER,
2787 funcmode IN VARCHAR2,
2788 resultout OUT nocopy VARCHAR2 )
2789 IS
2790
2791 l_item_value varchar2(200);
2792
2793 BEGIN
2794
2795 hr_utility.set_location('ENTERING get_bprv', 10);
2796 IF (funcmode='RUN') THEN
2797 l_item_value := wf_engine.getItemAttrText(itemtype => itemtype
2798 ,itemkey => itemkey
2799 , aname => 'BYPASS_REVIEW');
2800
2801 if (l_item_value = 'NO' or l_item_value is null) then
2802 resultout:='COMPLETE:N';
2803 else
2804 resultout:='COMPLETE:Y';
2805 end if;
2806 hr_utility.trace('l_resultout' || resultout);
2807
2808
2809 RETURN;
2810 END IF; --RUN
2811
2812 IF (funcmode='CANCEL') THEN
2813 resultout:='COMPLETE';
2814 RETURN;
2815 END IF;
2816 Exception
2817 when others then
2818 hr_utility.set_location('ENTERING exception get_bprv', 10);
2819
2820
2821 end get_wf_bprv;
2822
2823 function check_trans_step_exists
2824 (
2825 itemtype IN WF_ITEMS.ITEM_TYPE%TYPE,
2826 itemkey IN WF_ITEMS.ITEM_KEY%TYPE
2827 ) return varchar2 is
2828
2829 dummy VARCHAR2(10);
2830
2831 begin
2832
2833
2834 select 'X' into dummy from hr_api_transaction_steps ts, hr_api_transactions hat
2835 where hat.item_type = itemtype
2836 and hat.item_key = itemkey
2837 and ts.transaction_id = hat.transaction_id
2838 and rownum <= 1;
2839 dummy := null;
2840
2841 return 'N';
2842 exception
2843 when NO_DATA_FOUND then
2844
2845 return 'Y';
2846 end check_trans_step_exists;
2847
2848 END HR_UTIL_MISC_SS;