1 PACKAGE BODY AP_WEB_DB_HR_INT_PKG AS
2 /* $Header: apwdbhrb.pls 120.31.12010000.3 2008/11/10 10:19:39 rveliche 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 AND peras.per_system_status in ('ACTIVE_ASSIGN', 'ACTIVE_CWK');
334
335 RETURN TRUE;
336
337 EXCEPTION
338 WHEN NO_DATA_FOUND THEN
339 RETURN FALSE;
340
341 WHEN OTHERS THEN
342 AP_WEB_DB_UTIL_PKG.RaiseException( 'GetEmployeeInfo' );
343 APP_EXCEPTION.RAISE_EXCEPTION;
344 return FALSE;
345
346 END GetEmployeeInfo;
347
348
349 --------------------------------------------------------------------------------
350 FUNCTION GetEmpIdForUser(
351 p_user_id IN fndUser_userID,
352 p_emp_id OUT NOCOPY fndUser_employeeID
353 ) RETURN BOOLEAN IS
354 --------------------------------------------------------------------------------
355 BEGIN
356 SELECT employee_id
357 INTO p_emp_id
358 FROM fnd_user
359 WHERE user_id = p_user_id;
360
361 return TRUE;
362
363 EXCEPTION
364 WHEN NO_DATA_FOUND THEN
365 RETURN FALSE;
366
367 WHEN OTHERS THEN
368 AP_WEB_DB_UTIL_PKG.RaiseException( 'GetEmpIdForUser' );
369 APP_EXCEPTION.RAISE_EXCEPTION;
370 return FALSE;
371
372 END GetEmpIdForUser;
373
374
375 FUNCTION getEmployeeID return number IS
376
377 l_user_id NUMBER;
378 l_employee_id NUMBER;
379
380 BEGIN
381
382 -- Bug 1480911. Replaced this code with a workaround
383 -- because it would always return -1 in New UI
384
385 -- return icx_sec.getID(icx_sec.PV_INI_CONTACT_ID);
386
387 l_user_id := FND_PROFILE.VALUE('USER_ID');
388
389 if (NOT GetEmpIdForUser (l_user_id, l_employee_id))
390 then
391 raise NO_DATA_FOUND;
392 end if;
393
394 return (l_employee_id);
395 END;
396
397
398 FUNCTION GetSecurAttrCount(
399 P_WebUserID IN usrSecAttr_webUserID
400 ) RETURN NUMBER IS
401 l_security_attr_cnt NUMBER := 0;
402 BEGIN
403 SELECT COUNT(*)
404 INTO l_security_attr_cnt
405 FROM hr_employees_current_v h,
406 ak_web_user_sec_attr_values a
407 WHERE a.attribute_code = 'ICX_HR_PERSON_ID'
408 AND a.web_user_id = P_WebUserID
409 AND h.employee_id = a.number_value;
410
411 RETURN(l_security_attr_cnt);
412 EXCEPTION
413 WHEN NO_DATA_FOUND THEN
414 RETURN 0;
415
416 WHEN OTHERS THEN
417 AP_WEB_DB_UTIL_PKG.RaiseException( 'GetSecurAttrCount' );
418 APP_EXCEPTION.RAISE_EXCEPTION;
419 return 0;
420 END;
421
422 /*------------------------------------------------------------+
423 Created By: Amulya Mishra
424 Bug 1347380: Created a function to get the supervisor name
425 which will be displayed in the Review Page.
426 +-------------------------------------------------------------*/
427
428 FUNCTION GetSupervisorName(
429 p_employee_id IN NUMBER
430 ) RETURN VARCHAR2 IS
431 p_manager_id NUMBER;
432 p_manager_name PER_WORKFORCE_CURRENT_X.FULL_NAME%TYPE;
433 p_manager_org_id NUMBER;
434
435 ------------------------------------------------------------------_
436 BEGIN
437 -- 3176205: Contingent workers and inactive employees
438 -- The supervisor returned should be an active contingent worker
439 -- or employee.
440 IF (GetSupervisorInfo(p_employee_id, p_manager_id, p_manager_name, p_manager_org_id)) THEN
441 RETURN p_manager_name;
442 ELSE
443 RETURN NULL;
444 END IF;
445
446 EXCEPTION
447 WHEN NO_DATA_FOUND THEN
448 RETURN NULL;
449
450 WHEN OTHERS THEN
451 AP_WEB_DB_UTIL_PKG.RaiseException( 'GetSupervisorName' );
452 APP_EXCEPTION.RAISE_EXCEPTION;
453 RETURN NULL;
454
455 END GetSupervisorName;
456
457 PROCEDURE GetSupervisorDetails(
458 p_employee_id IN NUMBER,
459 p_supervisor_id OUT NOCOPY NUMBER,
460 p_supervisor_name OUT NOCOPY VARCHAR2
461 )IS
462
463 p_manager_id NUMBER;
464 p_manager_name PER_WORKFORCE_CURRENT_X.FULL_NAME%TYPE;
465 p_manager_org_id NUMBER;
466
467 ------------------------------------------------------------------_
468 BEGIN
469 -- 3176205: Contingent workers and inactive employees
470 -- The supervisor returned should be an active contingent worker
471 -- or employee.
472 IF (GetSupervisorInfo(p_employee_id, p_manager_id, p_manager_name, p_manager_org_id)) THEN
473 p_supervisor_id:=p_manager_id;
474 p_supervisor_name:=p_manager_name;
475 END IF;
476
477 EXCEPTION
478 WHEN NO_DATA_FOUND THEN
479 p_supervisor_id :=NULL;
480 p_supervisor_name :=NULL;
481 WHEN OTHERS THEN
482 AP_WEB_DB_UTIL_PKG.RaiseException( 'GetSupervisorDetails' );
483 APP_EXCEPTION.RAISE_EXCEPTION;
484
485
486 END GetSupervisorDetails;
487
488
489 -- Use GetManagerIdAndStatus if you need to get the supervisor info
490 -- (Manager_id, name and status) of supervisor of any status
491 --------------------------------------------------------------------------------
492 FUNCTION GetSupervisorInfo(
493 p_employee_id IN NUMBER,
494 p_manager_id OUT NOCOPY NUMBER,
495 p_manager_name OUT NOCOPY VARCHAR2,
496 p_manager_org_id OUT NOCOPY NUMBER
497 ) RETURN BOOLEAN IS
498 --------------------------------------------------------------------------------
499 BEGIN
500 -- 3176205: Contingent workers and inactive employees
501 -- The supervisor returned should be an active contingent worker
502 -- or employee.
503
504 -- The subquery should allow any person's
505 -- supervisor to be selected except for
506 -- terminated cwk's or ex-employees that
507 -- are a current contingent worker.
508 -- bug 3650767 : changed the suquery get the supervisor from
509 -- per_employees_x, per_cont_workers_current_x instead of per_workforce_x
510 SELECT mgr.person_id, mgr.full_name, pera.business_group_id
511 INTO p_manager_id, p_manager_name, p_manager_org_id
512 FROM per_people_x mgr,
513 per_assignments_f pera,
514 per_assignment_status_types peras
515 WHERE mgr.person_id = (
516 SELECT emp.supervisor_id
517 FROM per_employees_x emp
518 WHERE emp.employee_id = p_employee_id
519 AND NOT AP_WEB_DB_HR_INT_PKG.ispersoncwk(emp.employee_id)='Y'
520 UNION ALL
521 SELECT emp.supervisor_id
522 FROM per_cont_workers_current_x emp
523 WHERE emp.person_id = p_employee_id
524 )
525 AND mgr.person_id = pera.person_id
526 AND pera.assignment_status_type_id = peras.assignment_status_type_id
527 AND trunc(sysdate) between pera.effective_start_date and pera.effective_end_date
528 AND pera.assignment_type in ('C', 'E')
529 AND pera.primary_flag='Y'
530 AND peras.per_system_status in ('ACTIVE_ASSIGN', 'ACTIVE_CWK');
531
532 RETURN TRUE;
533
534
535 EXCEPTION
536 WHEN NO_DATA_FOUND THEN
537 RETURN FALSE;
538
539 WHEN OTHERS THEN
540 AP_WEB_DB_UTIL_PKG.RaiseException( 'GetSupervisorInfo' );
541 APP_EXCEPTION.RAISE_EXCEPTION;
542 return FALSE;
543
544 END GetSupervisorInfo;
545
546 --
547 -- IsPersonActive
548 -- Author: Kristian Widjaja
549 -- Purpose: To determine whether a person is an active employee
550 -- or active contingent worker.
551 -- Bug 3215993: Inactive Employees and Contingent Workers project
552 --
553 -- Input: p_person_id
554 --
555 -- Output: 'Y' (Yes) or 'N' (No)
556 --
557
558 FUNCTION IsPersonActive (p_person_id IN NUMBER) return VARCHAR2
559 IS
560 v_numRows NUMBER := 0;
561 BEGIN
562 -- This query returns rows if there exist an active employee or cwk
563 -- record for the given person ID.
564 SELECT 1
565 INTO v_numRows
566 FROM DUAL
567 WHERE exists
568 (SELECT 1
569 FROM
570 per_people_f p,
571 per_assignments_f a,
572 per_assignment_status_types past
573 WHERE a.person_id = p.person_id
574 AND p.person_id = p_person_id
575 AND a.assignment_status_type_id = past.assignment_status_type_id
576 AND a.primary_flag = 'Y'
577 AND trunc(sysdate) between p.effective_start_date AND p.effective_end_date
578 AND trunc(sysdate) between a.effective_start_date AND a.effective_end_date
579 AND a.assignment_type in ('E', 'C')
580 AND past.per_system_status in ('ACTIVE_ASSIGN', 'ACTIVE_CWK'));
581
582 -- Return true if there were rows, return false otherwise
583 IF v_numRows = 1 THEN
584 RETURN 'Y';
585 ELSE
586 RETURN 'N';
587 END IF;
588
589 EXCEPTION
590 WHEN no_data_found THEN
591 return('N');
592 WHEN OTHERS THEN
593 raise;
594 END IsPersonActive;
595
596 --
597 -- IsPersonCwk
598 -- Author: Kristian Widjaja
599 -- Purpose: To determine whether a person is a contingent worker or not.
600 -- Bug 3215993: Inactive Employees and Contingent Workers project
601 --
602 -- Input: p_person_id
603 --
604 -- Output: 'Y' (Yes) or 'N' (No)
605 --
606
607 FUNCTION IsPersonCwk (p_person_id IN NUMBER) return VARCHAR2
608 IS
609 v_numRows NUMBER := 0;
610 BEGIN
611 -- This query returns rows if there exist an active employee or cwk
612 -- record for the given person ID.
613 SELECT 1
614 INTO v_numRows
615 FROM DUAL
616 WHERE EXISTS
617 (SELECT 1
618 FROM
619 per_cont_workers_current_x p
620 WHERE
621 p.person_id = p_person_id);
622
623 -- Return true if there were rows, return false otherwise
624 IF v_numRows = 1 THEN
625 RETURN 'Y';
626 ELSE
627 RETURN 'N';
628 END IF;
629
630 EXCEPTION
631 WHEN no_data_found THEN
632 return('N');
633 WHEN OTHERS THEN
634 raise;
635 END IsPersonCwk;
636
637 --
638 -- Procedure: GetVendorAndVendorSite
639 -- Author: Kristian Widjaja
640 -- Purpose: This procedure gets the vendor and vendor site
641 -- of a person's primary assignment
642 --
643 -- Input: p_person_id
644 -- p_effective_date
645 --
646 -- Output: p_vendor_id, p_vendor_site_id
647 --
648 -- Notes: Bug 3215993
649 -- Inactive Employees and Contingent Workers project
650
651 PROCEDURE GetVendorAndVendorSite
652 (p_person_id IN NUMBER
653 ,p_effective_date IN DATE
654 ,p_vendor_id OUT NOCOPY NUMBER
655 ,p_vendor_site_id OUT NOCOPY NUMBER)
656 IS
657 BEGIN
658 -- Call HR API
659 HR_PO_INFO.get_vendor_for_primary_asg(p_person_id,
660 p_effective_date,
661 p_vendor_id,
662 p_vendor_site_id);
663
664 END GetVendorAndVendorSite;
665
666 --
667 -- IsPersonTerminated
668 -- Author: Kristian Widjaja
669 -- Purpose: To determine whether a person is a terminated person
670 -- Bug 3251447: Pending employee approval for terminated employee.
671 --
672 -- Input: p_person_id
673 --
674 -- Output: 'Y' (Yes) or 'N' (No)
675 --
676
677 FUNCTION IsPersonTerminated (p_person_id IN NUMBER) return VARCHAR2
678 IS
679 v_numRows NUMBER := 0;
680 BEGIN
681 -- This query returns rows if there exist any active or suspended
682 -- employee or cwk record for the given person ID.
683
684 SELECT 1
685 INTO v_numRows
686 FROM DUAL
687 WHERE exists
688 (SELECT 1
689 FROM
690 per_people_f p,
691 per_assignments_f a,
692 per_assignment_status_types past
693 WHERE a.person_id = p.person_id
694 AND p.person_id = p_person_id
695 AND a.assignment_status_type_id = past.assignment_status_type_id
696 AND a.primary_flag = 'Y'
697 AND trunc(sysdate) between p.effective_start_date AND p.effective_end_date
698 AND trunc(sysdate) between a.effective_start_date AND a.effective_end_date
699 AND a.assignment_type in ('E', 'C')
700 AND past.per_system_status in ('ACTIVE_ASSIGN', 'ACTIVE_CWK',
701 'SUSP_ASSIGN', 'SUSP_CWK_ASG'));
702
703 -- Return false if there were rows, return true otherwise
704 IF v_numRows = 1 THEN
705 RETURN 'N';
706 ELSE
707 RETURN 'Y';
708 END IF;
709
710 EXCEPTION
711 WHEN no_data_found THEN
712 return('Y');
713 WHEN OTHERS THEN
714 raise;
715 END IsPersonTerminated;
716
717 FUNCTION GetEmpInactiveDate(p_employee_id IN NUMBER)
718 RETURN Date IS
719 l_date Date;
720 BEGIN
721 SELECT inactive_date
722 INTO l_date
723 FROM per_employees_x
724 WHERE employee_id = p_employee_id;
725 RETURN l_date;
726 EXCEPTION
727 WHEN OTHERS THEN
728 raise;
729 END GetEmpInactiveDate;
730
731 --
732 -- GetManagerIdAndStatus
733 -- Author: skoukunt
734 -- Purpose: Get the manager ID, name and status
735 -- of an active/terminated/suspended employee
736 -- or contingent worker
737 -- Bug 3257576
738 --
739 -- Input: p_employee_id
740 --
741 -- Output: p_manager_id - Manager ID
742 -- p_manager_name - Manager Name
743 -- p_manager_status - Manager Status (SUSP_ASSIGN, SUSP_CWK_ASG
744 -- TERM_ASSIGN, ACTIVE_ASSIGN, ACTIVE_CWK)
745 --
746 -- use GetSupervisorInfo if you need to get supervisor info for
747 -- only active contingent worker or employee
748 --
749 --------------------------------------------------------------------------------
750 PROCEDURE GetManagerIdAndStatus(
751 p_employee_id IN NUMBER,
752 p_manager_id OUT NOCOPY NUMBER,
753 p_manager_name OUT NOCOPY VARCHAR2,
754 p_manager_status OUT NOCOPY VARCHAR2
755 ) IS
756 --------------------------------------------------------------------------------
757 BEGIN
758 SELECT mgr.person_id, mgr.full_name, peras.per_system_status
759 INTO p_manager_id, p_manager_name, p_manager_status
760 FROM per_people_x mgr,
761 per_assignments_f pera,
762 per_assignment_status_types peras
763 WHERE mgr.person_id = (
764 SELECT emp.supervisor_id
765 FROM per_employees_x emp
766 WHERE emp.employee_id = p_employee_id
767 AND NOT AP_WEB_DB_HR_INT_PKG.ispersoncwk(emp.employee_id)='Y'
768 UNION ALL
769 SELECT emp.supervisor_id
770 FROM per_cont_workers_current_x emp
771 WHERE emp.person_id = p_employee_id
772 )
773 AND mgr.person_id = pera.person_id
774 AND pera.assignment_status_type_id = peras.assignment_status_type_id
775 AND trunc(sysdate) between pera.effective_start_date and pera.effective_end_date
776 AND pera.assignment_type in ('C', 'E')
777 AND pera.primary_flag='Y'
778 AND rownum =1;
779
780 EXCEPTION
781 WHEN NO_DATA_FOUND THEN
782 null;
783
784 WHEN OTHERS THEN
785 AP_WEB_DB_UTIL_PKG.RaiseException( 'GetSupervisorInfo' );
786 APP_EXCEPTION.RAISE_EXCEPTION;
787
788 END GetManagerIdAndStatus;
789
790 FUNCTION GetEmployeeName(
791 p_employee_id IN NUMBER
792 ) RETURN VARCHAR2 IS
793 l_employee_name wf_users.name%type;
794 l_employee_display_name wf_users.display_name%type;
795
796 BEGIN
797
798 WF_DIRECTORY.GetUserName('PER',
799 p_employee_id,
800 l_employee_name,
801 l_employee_display_name);
802 RETURN l_employee_display_name;
803
804 END GetEmployeeName;
805
806 FUNCTION GetEmpOrgId(
807 p_employee_id IN empCurrent_employeeID,
808 p_effective_date IN Date,
809 P_organization_id OUT NOCOPY empCurrent_orgID
810 ) RETURN BOOLEAN IS
811 --------------------------------------------------------------------------------
812 BEGIN
813
814 SELECT asg.organization_id
815 INTO p_organization_id
816 FROM per_assignments_f asg
817 WHERE asg.person_id = p_employee_id
818 AND asg.assignment_type in ('E','C')
819 AND asg.primary_flag='Y'
820 AND TRUNC(p_effective_date)
821 BETWEEN asg.effective_start_date
822 AND asg.effective_end_date;
823
824 return TRUE;
825
826 EXCEPTION
827 WHEN NO_DATA_FOUND THEN
828 RETURN FALSE;
829
830 WHEN OTHERS THEN
831 AP_WEB_DB_UTIL_PKG.RaiseException( 'GetEmpOrgId' );
832 APP_EXCEPTION.RAISE_EXCEPTION;
833 return FALSE;
834 END GetEmpOrgId;
835
836 --
837 -- HasValidFndUserAndWfAccount
838 -- Author: Maulik Vadera
839 -- Purpose: To determine whether a person is validate USER and has validate
840 -- Workflow account.
841 --
842 -- Input: p_emp_id
843 --
844 -- Output: 'Y' (Yes) or 'N' (No)
845 --
846 ---
847 FUNCTION HasValidFndUserAndWfAccount(p_emp_id IN NUMBER) return VARCHAR2
848 IS
849 v_numRows NUMBER := 0;
850 BEGIN
851
852 SELECT 1 into v_numRows
853 FROM DUAL
854 WHERE EXISTS
855 (SELECT 1 FROM fnd_user fnd, wf_users wf
856 WHERE fnd.user_name = wf.name and employee_id = p_emp_id
857 AND ORIG_SYSTEM not in ('HZ_PARTY','CUST_CONT')
858 AND status = 'ACTIVE')
859 and rownum=1;
860
861 -- Return true if there were rows, return false otherwise
862 IF v_numRows = 1 THEN
863 RETURN 'Y';
864 ELSE
865 RETURN 'N';
866 END IF;
867
868 EXCEPTION
869 WHEN no_data_found THEN
870 return('N');
871 WHEN OTHERS THEN
872 raise;
873 END HasValidFndUserAndWfAccount;
874
875 /*-----------------------------------------------------------------------
876 Bug 4387063 - returns final active manager.
877 returns null if active manager does not exist.
878 -----------------------------------------------------------------------*/
879 FUNCTION getFinalActiveManager(p_employee_id IN NUMBER) RETURN NUMBER IS
880 -------------------------------------------------------------------------
881 l_debug_info VARCHAR2(200);
882 l_manager_id NUMBER := NULL;
883 l_manager_name per_workforce_x.full_name%TYPE;
884 l_manager_status per_assignment_status_types.per_system_status%Type;
885
886 BEGIN
887
888
889 AP_WEB_DB_HR_INT_PKG.GetManagerIdAndStatus(
890 p_employee_id,
891 l_manager_id,
892 l_manager_name,
893 l_manager_status);
894
895 if l_manager_id is null then
896 return l_manager_id;
897 end if;
898
899 --------------------------------------------------------------
900 l_debug_info := l_manager_id || ' Status ' || l_manager_status;
901 --------------------------------------------------------------
902 if l_manager_status in ('ACTIVE_ASSIGN', 'ACTIVE_CWK') then
903 return l_manager_id;
904 else
905 --------------------------------------------------------------
906 l_debug_info := 'Calling getFinalActiveManager';
907 --------------------------------------------------------------
908 return (getFinalActiveManager(l_manager_id));
909 end if;
910
911 EXCEPTION
912 WHEN OTHERS THEN
913 AP_WEB_DB_UTIL_PKG.RaiseException('AP_WEB_CUST_AME_PKG.getFinalActiveManager',
914 l_debug_info);
915 APP_EXCEPTION.RAISE_EXCEPTION;
916 END getFinalActiveManager;
917
918 PROCEDURE GetEmpNameNumber(
919 p_employee_id IN NUMBER,
920 p_employee_number OUT NOCOPY VARCHAR2,
921 p_employee_name OUT NOCOPY VARCHAR2
922 ) AS
923
924 l_debug_info VARCHAR2(200);
925 l_emp_info_rec EmployeeInfoRec;
926
927 BEGIN
928
929 IF GetEmployeeInfo(p_employee_id, l_emp_info_rec) THEN
930 p_employee_number := l_emp_info_rec.employee_num;
931 p_employee_name := l_emp_info_rec.employee_name;
932 END IF;
933
934 EXCEPTION
935 WHEN OTHERS THEN
936 AP_WEB_DB_UTIL_PKG.RaiseException('AP_WEB_DB_HR_INT_PKG.GetEmpNameNumber',
937 l_debug_info);
938 APP_EXCEPTION.RAISE_EXCEPTION;
939 END GetEmpNameNumber;
940
941 PROCEDURE GetUserIdFromName(
942 p_user_name IN VARCHAR2,
943 p_user_id OUT NOCOPY NUMBER
944 ) IS
945 ------------------------------------------------------------------
946 BEGIN
947
948 SELECT user_id INTO p_user_id
949 FROM fnd_user
950 WHERE user_name = p_user_name;
951
952 EXCEPTION
953 WHEN NO_DATA_FOUND THEN
954 p_user_id := -1;
955 WHEN OTHERS THEN
956 AP_WEB_DB_UTIL_PKG.RaiseException( 'GetUserIdFromName' );
957 APP_EXCEPTION.RAISE_EXCEPTION;
958 END GetUserIdFromName;
959
960 END AP_WEB_DB_HR_INT_PKG;
[Home] [Help]