1 PACKAGE BODY AP_WEB_DB_HR_INT_PKG AS
2 /* $Header: apwdbhrb.pls 120.31.12010000.5 2009/10/21 09:53:59 dsadipir ship $ */
3
4 --------------------------------------------------------------------------------
5 FUNCTION GetUserIdForEmpCursor(
6 p_emp_id IN fndUser_employeeID,
7 p_user_id_ref_cursor OUT NOCOPY UserIdRefCursor
8 ) RETURN BOOLEAN IS
9 --------------------------------------------------------------------------------
10 BEGIN
11 OPEN p_user_id_ref_cursor FOR
12 SELECT user_id
13 FROM fnd_user
14 WHERE employee_id = p_emp_id
15 ORDER BY creation_date;
16
17 RETURN TRUE;
18
19 EXCEPTION
20 WHEN NO_DATA_FOUND THEN
21 RETURN FALSE;
22
23 WHEN OTHERS THEN
24 AP_WEB_DB_UTIL_PKG.RaiseException( 'GetUserIdForEmpCursor' );
25 APP_EXCEPTION.RAISE_EXCEPTION;
26 return FALSE;
27
28 END GetUserIdForEmpCursor;
29
30 --------------------------------------------------------------------------------
31 FUNCTION GetAllEmpListForWebUserCursor(
32 p_web_user_id IN usrSecAttr_webUserID,
33 p_emp_name_cursor OUT NOCOPY EmpNameCursor
34 ) RETURN BOOLEAN IS
35 -- 3176205: Inactive Employees and Contingent Workers changes
36 -- This function will return a cursor to all employees, whether
37 -- current or inactive. It will not return any contingent workers.
38 --------------------------------------------------------------------------------
39 BEGIN
40 OPEN p_emp_name_cursor FOR
41 SELECT h.employee_id, h.full_name ||' ('||h.employee_num||') ' nameNum
42 FROM per_employees_x h,
43 ak_web_user_sec_attr_values a
44 WHERE a.attribute_code = 'ICX_HR_PERSON_ID'
45 AND a.web_user_id = p_web_user_id
46 AND h.employee_id = a.number_value
47 ORDER BY UPPER(h.full_name);
48
49 return TRUE;
50
51 EXCEPTION
52 WHEN NO_DATA_FOUND THEN
53 RETURN FALSE;
54
55 WHEN OTHERS THEN
56 AP_WEB_DB_UTIL_PKG.RaiseException( 'GetWorkerListForWebUserCursor' );
57 APP_EXCEPTION.RAISE_EXCEPTION;
58 return FALSE;
59
60 END GetAllEmpListForWebUserCursor;
61
62 --------------------------------------------------------------------------------
63 FUNCTION GetEmpListForWebUserCursor(
64 p_web_user_id IN usrSecAttr_webUserID,
65 p_emp_name_cursor OUT NOCOPY EmpNameCursor
66 ) RETURN BOOLEAN IS
67 -- This function will return a cursor to current employees only.
68 -- It will not include terminated employees.
69 --------------------------------------------------------------------------------
70 BEGIN
71 OPEN p_emp_name_cursor FOR
72 SELECT h.employee_id, h.full_name ||' ('||h.employee_num||') ' nameNum
73 FROM per_employees_current_x h,
74 ak_web_user_sec_attr_values a
75 WHERE a.attribute_code = 'ICX_HR_PERSON_ID'
76 AND a.web_user_id = p_web_user_id
77 AND h.employee_id = a.number_value
78 ORDER BY UPPER(h.full_name);
79
80 return TRUE;
81
82 EXCEPTION
83 WHEN NO_DATA_FOUND THEN
84 RETURN FALSE;
85
86 WHEN OTHERS THEN
87 AP_WEB_DB_UTIL_PKG.RaiseException( 'GetEmpListForWebUserCursor' );
88 APP_EXCEPTION.RAISE_EXCEPTION;
89 return FALSE;
90
91 END GetEmpListForWebUserCursor;
92
93 --------------------------------------------------------------------------------
94 FUNCTION GetEmployeeInfoCursor(
95 p_name_str IN VARCHAR2,
96 p_upper_approver_name_fuzzy IN empCurrent_fullName,
97 p_emp_info_cursor OUT NOCOPY EmpInfoCursor
98 ) RETURN BOOLEAN IS
99 --------------------------------------------------------------------------------
100 /*
101 l_1st_char_approver_name VARCHAR2(4);
102 l_2nd_char_approver_name VARCHAR2(4);
103 l_1st_like_constant VARCHAR2(9);
104 l_2nd_like_constant VARCHAR2(9);
105 l_3rd_like_constant VARCHAR2(9);
106 l_4th_like_constant VARCHAR2(9);
107 */
108
109 BEGIN
110
111 -- Bug 3818881 : commented
112 /*
113 -- chiho: know why the substr is used but not substrb?:'cause that's what we intended to do here, extract exactly one single char from the source string:
114 l_1st_char_approver_name := substr( p_name_str, 1, 1 );
115 l_2nd_char_approver_name := substr( p_name_str, 2, 1 );
116
117 l_1st_like_constant := UPPER(l_1st_char_approver_name || l_2nd_char_approver_name) || '%';
118 l_2nd_like_constant := LOWER(l_1st_char_approver_name || l_2nd_char_approver_name) || '%';
119 l_3rd_like_constant := UPPER(l_1st_char_approver_name) || LOWER(l_2nd_char_approver_name) || '%';
120 l_4th_like_constant := LOWER(l_1st_char_approver_name) || UPPER(l_2nd_char_approver_name) || '%';
121 */
122
123 -- Bug 1363739, Added order by clause inorder to catch
124 -- "Frost, Mr. Jamie" in the following example:
125 -- Frost, Mr. Jamie
126 -- Frost, Mr. Jamie K.
127 -- Frost, Mr. Jamie M.
128
129 OPEN p_emp_info_cursor FOR
130 -- 3176205: Inactive Employees and Contingent Workers changes
131 -- This query should only return ACTIVE
132 -- employees and contingent workers, depicted by the statuses
133 -- ACTIVE_ASSIGN and ACTIVE_CWK.
134 -- Bug 3818881 : Changing the query for performance reasons
135 SELECT emp.person_id, full_name
136 FROM
137 (
138 SELECT employee_id person_id, full_name
139 FROM per_employees_x emp
140 WHERE upper(full_name) like upper(p_upper_approver_name_fuzzy)
141 AND NOT AP_WEB_DB_HR_INT_PKG.ispersoncwk(emp.employee_id)='Y'
142 UNION ALL
143 SELECT person_id, full_name
144 FROM per_cont_workers_current_x emp
145 WHERE upper(full_name) like upper(p_upper_approver_name_fuzzy)
146 ) emp,
147 per_assignments_f pera,
148 per_assignment_status_types peras
149 WHERE emp.person_id = pera.person_id
150 AND pera.assignment_status_type_id = peras.assignment_status_type_id
151 AND trunc(sysdate) between pera.effective_start_date and pera.effective_end_date
152 AND pera.assignment_type in ('C', 'E')
153 AND pera.primary_flag='Y'
154 AND peras.per_system_status in ('ACTIVE_ASSIGN', 'ACTIVE_CWK')
155 AND rownum < 3;
156
157
158 return TRUE;
159
160 EXCEPTION
161 WHEN NO_DATA_FOUND THEN
162 RETURN FALSE;
163
164 WHEN OTHERS THEN
165 AP_WEB_DB_UTIL_PKG.RaiseException( 'GetEmployeeInfoCursor' );
166 APP_EXCEPTION.RAISE_EXCEPTION;
167 return FALSE;
168
169 END GetEmployeeInfoCursor;
170
171 -------------------------------------------------------------------
172 FUNCTION GetNumOfEmpForWebUser(
173 p_attribute_code IN usrSecAttr_attrCode,
174 p_web_user_id IN usrSecAttr_webUserID,
175 p_count OUT NOCOPY NUMBER
176 )
177 RETURN BOOLEAN IS
178 -------------------------------------------------------------------
179
180 BEGIN
181 -- This query should count all contingent workers and employees
182 -- No need to restrict to current employees
183 -- 3176205: Inactive Employees and Contingent Workers changes
184 -- This query selects from PER_PEOPLE_X, because that view will
185 -- return one row per person_id. PER_WORKFORCE_X could return 2 rows.
186 SELECT count(*)
187 INTO p_count
188 FROM per_people_x h,
189 ak_web_user_sec_attr_values a
190 WHERE a.attribute_code = p_attribute_code
191 AND a.web_user_id = p_web_user_id
192 AND h.person_id = a.number_value;
193
194 RETURN TRUE;
195
196 EXCEPTION
197 WHEN NO_DATA_FOUND THEN
198 RETURN FALSE;
199
200 WHEN OTHERS THEN
201 AP_WEB_DB_UTIL_PKG.RaiseException( 'GetNumOfEmpForWebUser' );
202 APP_EXCEPTION.RAISE_EXCEPTION;
203 return FALSE;
204
205 END GetNumOfEmpForWebUser;
206
207
208 -------------------------------------------------------------------
209 FUNCTION GetSupervisorID(
210 p_employee_id IN perEmp_employeeID,
211 p_manager_id OUT NOCOPY perEmp_supervisorID
212 ) RETURN BOOLEAN IS
213 p_manager_name VARCHAR2(240);
214 p_manager_org_id NUMBER;
215 -------------------------------------------------------------------
216 BEGIN
217 /* Bug 3003105 : Should not retrieve the manager if his record
218 has been end dated. */
219 /* Bug 3282097 : Should get the manager of terminated employees
220 and contingent workers. */
221
222 RETURN (GetSupervisorInfo(p_employee_id, p_manager_id, p_manager_name, p_manager_org_id));
223
224 EXCEPTION
225 WHEN NO_DATA_FOUND THEN
226 RETURN FALSE;
227
228 WHEN OTHERS THEN
229 AP_WEB_DB_UTIL_PKG.RaiseException( 'GetSupervisorID' );
230 APP_EXCEPTION.RAISE_EXCEPTION;
231 return FALSE;
232
233 END GetSupervisorID;
234
235
236 --------------------------------------------------------------------------------
237 FUNCTION GetEmpOrgId(
238 P_EmployeeID IN empCurrent_employeeID,
239 p_org_id OUT NOCOPY empCurrent_orgID
240 ) RETURN BOOLEAN IS
241 --------------------------------------------------------------------------------
242 BEGIN
243 -- 3176205: Contingent Workers and Inactive Employees.
244 -- Should not return terminated contingent workers or
245 -- ex employees that became contingent workers
246 -- 4042775: Changed query to not use per_workforce_x
247
248 select ORGANIZATION_ID
249 into p_org_id
250 from
251 ( select organization_id
252 from per_employees_x emp
253 where employee_id = P_EmployeeID
254 and not AP_WEB_DB_HR_INT_PKG.isPersonCwk(emp.employee_id)='Y'
255 union all
256 select organization_id
257 from per_cont_workers_current_x emp
258 where person_id = P_EmployeeID);
259 return TRUE;
260
261 EXCEPTION
262 WHEN NO_DATA_FOUND THEN
263 RETURN FALSE;
264
265 WHEN OTHERS THEN
266 AP_WEB_DB_UTIL_PKG.RaiseException( 'GetEmpOrgId' );
267 APP_EXCEPTION.RAISE_EXCEPTION;
268 return FALSE;
269 END GetEmpOrgId;
270
271 -------------------------------------------------------------------
272 FUNCTION GetEmployeeInfo(p_employee_id IN empCurrent_employeeID,
273 p_emp_info_rec OUT NOCOPY EmployeeInfoRec
274 ) RETURN BOOLEAN IS
275 -------------------------------------------------------------------
276 BEGIN
277 -- 3176205: Congtingent workers and inactive employees
278 -- This query includes all workers except for
279 -- terminated contingent workers and ex employees who become
280 -- contingent workers.
281 -- Bug 3818881 : Changed the query for performance reasons
282 /* SELECT emp1.full_name, emp1.employee_num, emp1.default_code_combination_id
283 INTO p_emp_info_rec.employee_name,
284 p_emp_info_rec.employee_num,
285 p_emp_info_rec.emp_ccid
286 FROM
287 (
288 SELECT emp.full_name,
289 emp.employee_num,
290 emp.default_code_combination_id,
291 employee_id person_id
292 FROM per_employees_x emp
293 WHERE emp.employee_id = p_employee_id
294 AND NOT AP_WEB_DB_HR_INT_PKG.ispersoncwk(emp.employee_id)='Y'
295 UNION ALL
296 SELECT cwk.full_name,
297 cwk.npw_number employee_num,
298 cwk.default_code_combination_id,
299 cwk. person_id
300 FROM per_cont_workers_current_x cwk
301 WHERE cwk.person_id = p_employee_id
302 ) emp1;
303 */
304 -- Bug: 7284573, Latest Assignment being picked up for employees terminated in the future.
305 SELECT emp1.full_name, emp1.employee_num, pera.default_code_comb_id
306 INTO p_emp_info_rec.employee_name,
307 p_emp_info_rec.employee_num,
308 p_emp_info_rec.emp_ccid
309 FROM
310 (
311 SELECT emp.full_name,
312 emp.employee_num,
313 emp.default_code_combination_id,
314 employee_id person_id
315 FROM per_employees_x emp
316 WHERE emp.employee_id = p_employee_id
317 AND NOT AP_WEB_DB_HR_INT_PKG.ispersoncwk(emp.employee_id)='Y'
318 UNION ALL
319 SELECT cwk.full_name,
320 cwk.npw_number employee_num,
321 cwk.default_code_combination_id,
322 cwk. person_id
323 FROM per_cont_workers_current_x cwk
324 WHERE cwk.person_id = p_employee_id
325 ) emp1,
326 per_assignments_f pera
327 -- per_assignment_status_types peras
328 WHERE emp1.person_id = pera.person_id
329 -- AND pera.assignment_status_type_id = peras.assignment_status_type_id
330 AND trunc(sysdate) between trunc(pera.effective_start_date) and trunc(pera.effective_end_date)
331 AND pera.assignment_type in ('C', 'E')
332 AND pera.primary_flag='Y';
333 -- Bug# 8996584 - Inactive Employees or Contigent Workers should be shown until the Final Process date is entered
334 -- AND peras.per_system_status in ('ACTIVE_ASSIGN', 'ACTIVE_CWK');
335
336 RETURN TRUE;
337
338 EXCEPTION
339 WHEN NO_DATA_FOUND THEN
340 RETURN FALSE;
341
342 WHEN OTHERS THEN
343 AP_WEB_DB_UTIL_PKG.RaiseException( 'GetEmployeeInfo' );
344 APP_EXCEPTION.RAISE_EXCEPTION;
345 return FALSE;
346
347 END GetEmployeeInfo;
348
349
350 --------------------------------------------------------------------------------
351 FUNCTION GetEmpIdForUser(
352 p_user_id IN fndUser_userID,
353 p_emp_id OUT NOCOPY fndUser_employeeID
354 ) RETURN BOOLEAN IS
355 --------------------------------------------------------------------------------
356 BEGIN
357 SELECT employee_id
358 INTO p_emp_id
359 FROM fnd_user
360 WHERE user_id = p_user_id;
361
362 return TRUE;
363
364 EXCEPTION
365 WHEN NO_DATA_FOUND THEN
366 RETURN FALSE;
367
368 WHEN OTHERS THEN
369 AP_WEB_DB_UTIL_PKG.RaiseException( 'GetEmpIdForUser' );
370 APP_EXCEPTION.RAISE_EXCEPTION;
371 return FALSE;
372
373 END GetEmpIdForUser;
374
375
376 FUNCTION getEmployeeID return number IS
377
378 l_user_id NUMBER;
379 l_employee_id NUMBER;
380
381 BEGIN
382
383 -- Bug 1480911. Replaced this code with a workaround
384 -- because it would always return -1 in New UI
385
386 -- return icx_sec.getID(icx_sec.PV_INI_CONTACT_ID);
387
388 l_user_id := FND_PROFILE.VALUE('USER_ID');
389
390 if (NOT GetEmpIdForUser (l_user_id, l_employee_id))
391 then
392 raise NO_DATA_FOUND;
393 end if;
394
395 return (l_employee_id);
396 END;
397
398
399 FUNCTION GetSecurAttrCount(
400 P_WebUserID IN usrSecAttr_webUserID
401 ) RETURN NUMBER IS
402 l_security_attr_cnt NUMBER := 0;
403 BEGIN
404 SELECT COUNT(*)
405 INTO l_security_attr_cnt
406 FROM hr_employees_current_v h,
407 ak_web_user_sec_attr_values a
408 WHERE a.attribute_code = 'ICX_HR_PERSON_ID'
409 AND a.web_user_id = P_WebUserID
410 AND h.employee_id = a.number_value;
411
412 RETURN(l_security_attr_cnt);
413 EXCEPTION
414 WHEN NO_DATA_FOUND THEN
415 RETURN 0;
416
417 WHEN OTHERS THEN
418 AP_WEB_DB_UTIL_PKG.RaiseException( 'GetSecurAttrCount' );
419 APP_EXCEPTION.RAISE_EXCEPTION;
420 return 0;
421 END;
422
423 /*------------------------------------------------------------+
424 Created By: Amulya Mishra
425 Bug 1347380: Created a function to get the supervisor name
426 which will be displayed in the Review Page.
427 +-------------------------------------------------------------*/
428
429 FUNCTION GetSupervisorName(
430 p_employee_id IN NUMBER
431 ) RETURN VARCHAR2 IS
432 p_manager_id NUMBER;
433 p_manager_name PER_WORKFORCE_CURRENT_X.FULL_NAME%TYPE;
434 p_manager_org_id NUMBER;
435
436 ------------------------------------------------------------------_
437 BEGIN
438 -- 3176205: Contingent workers and inactive employees
439 -- The supervisor returned should be an active contingent worker
440 -- or employee.
441 IF (GetSupervisorInfo(p_employee_id, p_manager_id, p_manager_name, p_manager_org_id)) THEN
442 RETURN p_manager_name;
443 ELSE
444 RETURN NULL;
445 END IF;
446
447 EXCEPTION
448 WHEN NO_DATA_FOUND THEN
449 RETURN NULL;
450
451 WHEN OTHERS THEN
452 AP_WEB_DB_UTIL_PKG.RaiseException( 'GetSupervisorName' );
453 APP_EXCEPTION.RAISE_EXCEPTION;
454 RETURN NULL;
455
456 END GetSupervisorName;
457
458 PROCEDURE GetSupervisorDetails(
459 p_employee_id IN NUMBER,
460 p_supervisor_id OUT NOCOPY NUMBER,
461 p_supervisor_name OUT NOCOPY VARCHAR2
462 )IS
463
464 p_manager_id NUMBER;
465 p_manager_name PER_WORKFORCE_CURRENT_X.FULL_NAME%TYPE;
466 p_manager_org_id NUMBER;
467
468 ------------------------------------------------------------------_
469 BEGIN
470 -- 3176205: Contingent workers and inactive employees
471 -- The supervisor returned should be an active contingent worker
472 -- or employee.
473 IF (GetSupervisorInfo(p_employee_id, p_manager_id, p_manager_name, p_manager_org_id)) THEN
474 p_supervisor_id:=p_manager_id;
475 p_supervisor_name:=p_manager_name;
476 END IF;
477
478 EXCEPTION
479 WHEN NO_DATA_FOUND THEN
480 p_supervisor_id :=NULL;
481 p_supervisor_name :=NULL;
482 WHEN OTHERS THEN
483 AP_WEB_DB_UTIL_PKG.RaiseException( 'GetSupervisorDetails' );
484 APP_EXCEPTION.RAISE_EXCEPTION;
485
486
487 END GetSupervisorDetails;
488
489
490 -- Use GetManagerIdAndStatus if you need to get the supervisor info
491 -- (Manager_id, name and status) of supervisor of any status
492 --------------------------------------------------------------------------------
493 FUNCTION GetSupervisorInfo(
494 p_employee_id IN NUMBER,
495 p_manager_id OUT NOCOPY NUMBER,
496 p_manager_name OUT NOCOPY VARCHAR2,
497 p_manager_org_id OUT NOCOPY NUMBER
498 ) RETURN BOOLEAN IS
499 --------------------------------------------------------------------------------
500 BEGIN
501 -- 3176205: Contingent workers and inactive employees
502 -- The supervisor returned should be an active contingent worker
503 -- or employee.
504
505 -- The subquery should allow any person's
506 -- supervisor to be selected except for
507 -- terminated cwk's or ex-employees that
508 -- are a current contingent worker.
509 -- bug 3650767 : changed the suquery get the supervisor from
510 -- per_employees_x, per_cont_workers_current_x instead of per_workforce_x
511 SELECT mgr.person_id, mgr.full_name, pera.business_group_id
512 INTO p_manager_id, p_manager_name, p_manager_org_id
513 FROM per_people_x mgr,
514 per_assignments_f pera,
515 per_assignment_status_types peras
516 WHERE mgr.person_id = (
517 SELECT emp.supervisor_id
518 FROM per_employees_x emp
519 WHERE emp.employee_id = p_employee_id
520 AND NOT AP_WEB_DB_HR_INT_PKG.ispersoncwk(emp.employee_id)='Y'
521 UNION ALL
522 SELECT emp.supervisor_id
523 FROM per_cont_workers_current_x emp
524 WHERE emp.person_id = p_employee_id
525 )
526 AND mgr.person_id = pera.person_id
527 AND pera.assignment_status_type_id = peras.assignment_status_type_id
528 AND trunc(sysdate) between pera.effective_start_date and pera.effective_end_date
529 AND pera.assignment_type in ('C', 'E')
530 AND pera.primary_flag='Y'
531 AND peras.per_system_status in ('ACTIVE_ASSIGN', 'ACTIVE_CWK');
532
533 RETURN TRUE;
534
535
536 EXCEPTION
537 WHEN NO_DATA_FOUND THEN
538 RETURN FALSE;
539
540 WHEN OTHERS THEN
541 AP_WEB_DB_UTIL_PKG.RaiseException( 'GetSupervisorInfo' );
542 APP_EXCEPTION.RAISE_EXCEPTION;
543 return FALSE;
544
545 END GetSupervisorInfo;
546
547 --
548 -- IsPersonActive
549 -- Author: Kristian Widjaja
550 -- Purpose: To determine whether a person is an active employee
551 -- or active contingent worker.
552 -- Bug 3215993: Inactive Employees and Contingent Workers project
553 --
554 -- Input: p_person_id
555 --
556 -- Output: 'Y' (Yes) or 'N' (No)
557 --
558
559 FUNCTION IsPersonActive (p_person_id IN NUMBER) return VARCHAR2
560 IS
561 v_numRows NUMBER := 0;
562 BEGIN
563 -- This query returns rows if there exist an active employee or cwk
564 -- record for the given person ID.
565 SELECT 1
566 INTO v_numRows
567 FROM DUAL
568 WHERE exists
569 (SELECT 1
570 FROM
571 per_people_f p,
572 per_assignments_f a,
573 per_assignment_status_types past
574 WHERE a.person_id = p.person_id
575 AND p.person_id = p_person_id
576 AND a.assignment_status_type_id = past.assignment_status_type_id
577 AND a.primary_flag = 'Y'
578 AND trunc(sysdate) between p.effective_start_date AND p.effective_end_date
579 AND trunc(sysdate) between a.effective_start_date AND a.effective_end_date
580 AND a.assignment_type in ('E', 'C')
581 AND past.per_system_status in ('ACTIVE_ASSIGN', 'ACTIVE_CWK', 'SUSP_ASSIGN', 'SUSP_CWK_ASG')); -- Bug 8357892(sodash) Added SUSP_ASSIGN for active suspended employees
582
583 -- Return true if there were rows, return false otherwise
584 IF v_numRows = 1 THEN
585 RETURN 'Y';
586 ELSE
587 RETURN 'N';
588 END IF;
589
590 EXCEPTION
591 WHEN no_data_found THEN
592 return('N');
593 WHEN OTHERS THEN
594 raise;
595 END IsPersonActive;
596
597 --
598 -- IsPersonCwk
599 -- Author: Kristian Widjaja
600 -- Purpose: To determine whether a person is a contingent worker or not.
601 -- Bug 3215993: Inactive Employees and Contingent Workers project
602 --
603 -- Input: p_person_id
604 --
605 -- Output: 'Y' (Yes) or 'N' (No)
606 --
607
608 FUNCTION IsPersonCwk (p_person_id IN NUMBER) return VARCHAR2
609 IS
610 v_numRows NUMBER := 0;
611 BEGIN
612 -- This query returns rows if there exist an active employee or cwk
613 -- record for the given person ID.
614 SELECT 1
615 INTO v_numRows
616 FROM DUAL
617 WHERE EXISTS
618 (SELECT 1
619 FROM
620 per_cont_workers_current_x p
621 WHERE
622 p.person_id = p_person_id);
623
624 -- Return true if there were rows, return false otherwise
625 IF v_numRows = 1 THEN
626 RETURN 'Y';
627 ELSE
628 RETURN 'N';
629 END IF;
630
631 EXCEPTION
632 WHEN no_data_found THEN
633 return('N');
634 WHEN OTHERS THEN
635 raise;
636 END IsPersonCwk;
637
638 --
639 -- Procedure: GetVendorAndVendorSite
640 -- Author: Kristian Widjaja
641 -- Purpose: This procedure gets the vendor and vendor site
642 -- of a person's primary assignment
643 --
644 -- Input: p_person_id
645 -- p_effective_date
646 --
647 -- Output: p_vendor_id, p_vendor_site_id
648 --
649 -- Notes: Bug 3215993
650 -- Inactive Employees and Contingent Workers project
651
652 PROCEDURE GetVendorAndVendorSite
653 (p_person_id IN NUMBER
654 ,p_effective_date IN DATE
655 ,p_vendor_id OUT NOCOPY NUMBER
656 ,p_vendor_site_id OUT NOCOPY NUMBER)
657 IS
658 BEGIN
659 -- Call HR API
660 HR_PO_INFO.get_vendor_for_primary_asg(p_person_id,
661 p_effective_date,
662 p_vendor_id,
663 p_vendor_site_id);
664
665 END GetVendorAndVendorSite;
666
667 --
668 -- IsPersonTerminated
669 -- Author: Kristian Widjaja
670 -- Purpose: To determine whether a person is a terminated person
671 -- Bug 3251447: Pending employee approval for terminated employee.
672 --
673 -- Input: p_person_id
674 --
675 -- Output: 'Y' (Yes) or 'N' (No)
676 --
677
678 FUNCTION IsPersonTerminated (p_person_id IN NUMBER) return VARCHAR2
679 IS
680 v_numRows NUMBER := 0;
681 BEGIN
682 -- This query returns rows if there exist any active or suspended
683 -- employee or cwk record for the given person ID.
684
685 SELECT 1
686 INTO v_numRows
687 FROM DUAL
688 WHERE exists
689 (SELECT 1
690 FROM
691 per_people_f p,
692 per_assignments_f a,
693 per_assignment_status_types past
694 WHERE a.person_id = p.person_id
695 AND p.person_id = p_person_id
696 AND a.assignment_status_type_id = past.assignment_status_type_id
697 AND a.primary_flag = 'Y'
698 AND trunc(sysdate) between p.effective_start_date AND p.effective_end_date
699 AND trunc(sysdate) between a.effective_start_date AND a.effective_end_date
700 AND a.assignment_type in ('E', 'C')
701 AND past.per_system_status in ('ACTIVE_ASSIGN', 'ACTIVE_CWK',
702 'SUSP_ASSIGN', 'SUSP_CWK_ASG'));
703
704 -- Return false if there were rows, return true otherwise
705 IF v_numRows = 1 THEN
706 RETURN 'N';
707 ELSE
708 RETURN 'Y';
709 END IF;
710
711 EXCEPTION
712 WHEN no_data_found THEN
713 return('Y');
714 WHEN OTHERS THEN
715 raise;
716 END IsPersonTerminated;
717
718 FUNCTION GetEmpInactiveDate(p_employee_id IN NUMBER)
719 RETURN Date IS
720 l_date Date;
721 BEGIN
722 SELECT inactive_date
723 INTO l_date
724 FROM per_employees_x
725 WHERE employee_id = p_employee_id;
726 RETURN l_date;
727 EXCEPTION
728 WHEN OTHERS THEN
729 raise;
730 END GetEmpInactiveDate;
731
732 --
733 -- GetManagerIdAndStatus
734 -- Author: skoukunt
735 -- Purpose: Get the manager ID, name and status
736 -- of an active/terminated/suspended employee
737 -- or contingent worker
738 -- Bug 3257576
739 --
740 -- Input: p_employee_id
741 --
742 -- Output: p_manager_id - Manager ID
743 -- p_manager_name - Manager Name
744 -- p_manager_status - Manager Status (SUSP_ASSIGN, SUSP_CWK_ASG
745 -- TERM_ASSIGN, ACTIVE_ASSIGN, ACTIVE_CWK)
746 --
747 -- use GetSupervisorInfo if you need to get supervisor info for
748 -- only active contingent worker or employee
749 --
750 --------------------------------------------------------------------------------
751 PROCEDURE GetManagerIdAndStatus(
752 p_employee_id IN NUMBER,
753 p_manager_id OUT NOCOPY NUMBER,
754 p_manager_name OUT NOCOPY VARCHAR2,
755 p_manager_status OUT NOCOPY VARCHAR2
756 ) IS
757 --------------------------------------------------------------------------------
758 BEGIN
759 SELECT mgr.person_id, mgr.full_name, peras.per_system_status
760 INTO p_manager_id, p_manager_name, p_manager_status
761 FROM per_people_x mgr,
762 per_assignments_f pera,
763 per_assignment_status_types peras
764 WHERE mgr.person_id = (
765 SELECT emp.supervisor_id
766 FROM per_employees_x emp
767 WHERE emp.employee_id = p_employee_id
768 AND NOT AP_WEB_DB_HR_INT_PKG.ispersoncwk(emp.employee_id)='Y'
769 UNION ALL
770 SELECT emp.supervisor_id
771 FROM per_cont_workers_current_x emp
772 WHERE emp.person_id = p_employee_id
773 )
774 AND mgr.person_id = pera.person_id
775 AND pera.assignment_status_type_id = peras.assignment_status_type_id
776 AND trunc(sysdate) between pera.effective_start_date and pera.effective_end_date
777 AND pera.assignment_type in ('C', 'E')
778 AND pera.primary_flag='Y'
779 AND rownum =1;
780
781 EXCEPTION
782 WHEN NO_DATA_FOUND THEN
783 null;
784
785 WHEN OTHERS THEN
786 AP_WEB_DB_UTIL_PKG.RaiseException( 'GetSupervisorInfo' );
787 APP_EXCEPTION.RAISE_EXCEPTION;
788
789 END GetManagerIdAndStatus;
790
791 FUNCTION GetEmployeeName(
792 p_employee_id IN NUMBER
793 ) RETURN VARCHAR2 IS
794 l_employee_name wf_users.name%type;
795 l_employee_display_name wf_users.display_name%type;
796
797 BEGIN
798
799 WF_DIRECTORY.GetUserName('PER',
800 p_employee_id,
801 l_employee_name,
802 l_employee_display_name);
803 RETURN l_employee_display_name;
804
805 END GetEmployeeName;
806
807 FUNCTION GetEmpOrgId(
808 p_employee_id IN empCurrent_employeeID,
809 p_effective_date IN Date,
810 P_organization_id OUT NOCOPY empCurrent_orgID
811 ) RETURN BOOLEAN IS
812 --------------------------------------------------------------------------------
813 BEGIN
814
815 SELECT asg.organization_id
816 INTO p_organization_id
817 FROM per_assignments_f asg
818 WHERE asg.person_id = p_employee_id
819 AND asg.assignment_type in ('E','C')
820 AND asg.primary_flag='Y'
821 AND TRUNC(p_effective_date)
822 BETWEEN asg.effective_start_date
823 AND asg.effective_end_date;
824
825 return TRUE;
826
827 EXCEPTION
828 WHEN NO_DATA_FOUND THEN
829 RETURN FALSE;
830
831 WHEN OTHERS THEN
832 AP_WEB_DB_UTIL_PKG.RaiseException( 'GetEmpOrgId' );
833 APP_EXCEPTION.RAISE_EXCEPTION;
834 return FALSE;
835 END GetEmpOrgId;
836
837 --
838 -- HasValidFndUserAndWfAccount
839 -- Author: Maulik Vadera
840 -- Purpose: To determine whether a person is validate USER and has validate
841 -- Workflow account.
842 --
843 -- Input: p_emp_id
844 --
845 -- Output: 'Y' (Yes) or 'N' (No)
846 --
847 ---
848 FUNCTION HasValidFndUserAndWfAccount(p_emp_id IN NUMBER) return VARCHAR2
849 IS
850 v_numRows NUMBER := 0;
851 BEGIN
852
853 SELECT 1 into v_numRows
854 FROM DUAL
855 WHERE EXISTS
856 (SELECT 1 FROM fnd_user fnd, wf_users wf
857 WHERE fnd.user_name = wf.name and employee_id = p_emp_id
858 AND ORIG_SYSTEM not in ('HZ_PARTY','CUST_CONT')
859 AND status = 'ACTIVE')
860 and rownum=1;
861
862 -- Return true if there were rows, return false otherwise
863 IF v_numRows = 1 THEN
864 RETURN 'Y';
865 ELSE
866 RETURN 'N';
867 END IF;
868
869 EXCEPTION
870 WHEN no_data_found THEN
871 return('N');
872 WHEN OTHERS THEN
873 raise;
874 END HasValidFndUserAndWfAccount;
875
876 /*-----------------------------------------------------------------------
877 Bug 4387063 - returns final active manager.
878 returns null if active manager does not exist.
879 -----------------------------------------------------------------------*/
880 FUNCTION getFinalActiveManager(p_employee_id IN NUMBER) RETURN NUMBER IS
881 -------------------------------------------------------------------------
882 l_debug_info VARCHAR2(200);
883 l_manager_id NUMBER := NULL;
884 l_manager_name per_workforce_x.full_name%TYPE;
885 l_manager_status per_assignment_status_types.per_system_status%Type;
886
887 BEGIN
888
889
890 AP_WEB_DB_HR_INT_PKG.GetManagerIdAndStatus(
891 p_employee_id,
892 l_manager_id,
893 l_manager_name,
894 l_manager_status);
895
896 if l_manager_id is null then
897 return l_manager_id;
898 end if;
899
900 --------------------------------------------------------------
901 l_debug_info := l_manager_id || ' Status ' || l_manager_status;
902 --------------------------------------------------------------
903 if l_manager_status in ('ACTIVE_ASSIGN', 'ACTIVE_CWK') then
904 return l_manager_id;
905 else
906 --------------------------------------------------------------
907 l_debug_info := 'Calling getFinalActiveManager';
908 --------------------------------------------------------------
909 return (getFinalActiveManager(l_manager_id));
910 end if;
911
912 EXCEPTION
913 WHEN OTHERS THEN
914 AP_WEB_DB_UTIL_PKG.RaiseException('AP_WEB_CUST_AME_PKG.getFinalActiveManager',
915 l_debug_info);
916 APP_EXCEPTION.RAISE_EXCEPTION;
917 END getFinalActiveManager;
918
919 PROCEDURE GetEmpNameNumber(
920 p_employee_id IN NUMBER,
921 p_employee_number OUT NOCOPY VARCHAR2,
922 p_employee_name OUT NOCOPY VARCHAR2
923 ) AS
924
925 l_debug_info VARCHAR2(200);
926 l_emp_info_rec EmployeeInfoRec;
927
928 BEGIN
929
930 IF GetEmployeeInfo(p_employee_id, l_emp_info_rec) THEN
931 p_employee_number := l_emp_info_rec.employee_num;
932 p_employee_name := l_emp_info_rec.employee_name;
933 END IF;
934
935 EXCEPTION
936 WHEN OTHERS THEN
937 AP_WEB_DB_UTIL_PKG.RaiseException('AP_WEB_DB_HR_INT_PKG.GetEmpNameNumber',
938 l_debug_info);
939 APP_EXCEPTION.RAISE_EXCEPTION;
940 END GetEmpNameNumber;
941
942 PROCEDURE GetUserIdFromName(
943 p_user_name IN VARCHAR2,
944 p_user_id OUT NOCOPY NUMBER
945 ) IS
946 ------------------------------------------------------------------
947 BEGIN
948
949 SELECT user_id INTO p_user_id
950 FROM fnd_user
951 WHERE user_name = p_user_name;
952
953 EXCEPTION
954 WHEN NO_DATA_FOUND THEN
955 p_user_id := -1;
956 WHEN OTHERS THEN
957 AP_WEB_DB_UTIL_PKG.RaiseException( 'GetUserIdFromName' );
958 APP_EXCEPTION.RAISE_EXCEPTION;
959 END GetUserIdFromName;
960
961 END AP_WEB_DB_HR_INT_PKG;
[Home] [Help]