1 PACKAGE BODY PO_EMPLOYEES_SV as
2 /*$Header: POXEMEMB.pls 120.5.12020000.2 2013/02/10 13:10:54 vegajula ship $*/
3
4 -- Read the profile option that enables/disables the debug log
5 g_asn_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('PO_RVCTP_ENABLE_TRACE'),'N');
6
7 -- Bug 4664653 START
8 g_emp_id NUMBER;
9 g_emp_name PER_EMPLOYEES_CURRENT_X.FULL_NAME%TYPE;
10 g_location_id NUMBER;
11 g_location_code HR_LOCATIONS.LOCATION_CODE%TYPE;
12 g_is_buyer BOOLEAN;
13 g_emp_flag BOOLEAN;
14 g_user_id NUMBER;
15 -- Bug 4664653 END
16
17
18
19 /*=============================================================================
20 Name: GET_EMPLOYEE() : PO Employee Request Information
21 Desc: Returns information relevant to employees
22 Args: emp_id - OUT : employee id
23 emp_name - OUT : employee name
24 location_id - OUT : location id
25 location_code - OUT : location code
26 is_buyer - OUT : is employee a buyer?
27 emp_flag - OUT : returns true if user is an employee
28 else returns false
29 Reqs: No preconditions and no input variables
30 Err : Returns FALSE if error. Error message written on message stack
31 Algr: get user id of current user
32 get emp_id, emp_name and location_id from hr_employees_cur_v
33 if NO ROWS return false
34 get location_code from hr_locations
35 check if employee is a buyer
36 Note: If user is an employee then emp_flag is TRUE
37 else emp_flag will be FALSE.
38 If the employee has a location then location_id will point to his
39 location id else location_id will be 0.
40 =============================================================================*/
41
42 FUNCTION get_employee (emp_id OUT NOCOPY number,
43 emp_name OUT NOCOPY varchar2,
44 location_id OUT NOCOPY number,
45 location_code OUT NOCOPY varchar2,
46 is_buyer OUT NOCOPY BOOLEAN,
47 emp_flag OUT NOCOPY BOOLEAN
48 )
49 RETURN BOOLEAN IS
50
51 X_user_id varchar2(80); /* stores the user id */
52 X_emp_id NUMBER := 0 ; /* stores the employee_id */
53 X_location_id NUMBER := 0 ; /* stores the location_id */
54 X_emp_name VARCHAR2(240) := '' ; /* stores the employee_name */
55 l_cwk_profile VARCHAR2(1);
56
57 /** PO UTF8 Column Expansion Project 9/18/2002 tpoon **/
58 /** Changed X_location_code to use %TYPE **/
59 -- X_location_code VARCHAR2(20) := '' ; /* stores the employee location */
60 X_location_code hr_locations_all.location_code%TYPE := ''; /* stores the employee location */
61
62 X_buyer_code VARCHAR2(1) := 'Y' ; /* dummy, stores buyer status */
63 mesg_buffer VARCHAR2(2000) := '' ; /* for handling error messages */
64 X_progress varchar2(3) := '';
65
66 BEGIN
67 /* get user id */
68
69 FND_PROFILE.GET('USER_ID', X_user_id);
70 if X_user_id is null then
71 -- dbms_output.put_line('Xuserid is Null');
72 -- po_message_s.app_error('PO_ALL_SQL_ERROR');
73 return False;
74 end if;
75
76
77 -- Bug 4664653 START
78 -- Return the global variables, if the user_id is already cached.
79 IF X_user_id = Nvl(g_user_id,-99) THEN
80 emp_id := g_emp_id;
81 emp_name := g_emp_name;
82 location_id := g_location_id;
83 location_code := g_location_code;
84 is_buyer := g_is_buyer;
85 emp_flag := g_emp_flag;
86
87 -- Retrun itself.
88 RETURN TRUE;
89 END IF;
90 -- Bug 4664653 END
91
92
93 BEGIN
94
95
96 X_progress := '010';
97
98 --<R12 CWK Enhancemment start>
99 FND_PROFILE.GET('HR_TREAT_CWK_AS_EMP', l_cwk_profile);
100
101 IF l_cwk_profile = 'N' then
102
103 /* get emp_id, emp_name and location_id */
104
105 -- Bug 4664653
106 -- Add to_number to x_user_id
107
108 --Bug 13552967
109 --Included Exception block to avoid failure of below
110 --sql by bug 10413227
111 BEGIN
112 SELECT HR.EMPLOYEE_ID,
113 HR.FULL_NAME,
114 NVL(hr.LOCATION_ID,0)
115 INTO X_emp_id,
116 X_emp_name,
117 X_location_id
118 FROM FND_USER FND, hr_operating_units org, (SELECT p.person_id employee_id, p.full_name, a.SET_OF_BOOKS_ID , a.location_id
119 FROM PER_PEOPLE_F P,
120 PER_ALL_ASSIGNMENTS_F A,
121 PER_PERIODS_OF_SERVICE B
122 WHERE A.PERSON_ID = P.PERSON_ID
123 --AND A.PRIMARY_FLAG = 'Y'
124 AND A.ASSIGNMENT_TYPE = 'E'
125 AND A.PERIOD_OF_SERVICE_ID = B.PERIOD_OF_SERVICE_ID
126 AND TRUNC(SYSDATE) BETWEEN P.EFFECTIVE_START_DATE AND P.EFFECTIVE_END_DATE
127 AND TRUNC(SYSDATE) BETWEEN A.EFFECTIVE_START_DATE AND A.EFFECTIVE_END_DATE
128 AND (B.ACTUAL_TERMINATION_DATE>= trunc(sysdate) or B.ACTUAL_TERMINATION_DATE is null)
129 AND P.EMPLOYEE_NUMBER IS NOT NULL) hr
130 WHERE FND.USER_ID = X_user_id
131 AND FND.EMPLOYEE_ID = hr.EMPLOYEE_ID
132 AND hr.set_of_books_id = org.set_of_books_id
133 AND org.organization_id = fnd_global.org_id
134 AND ROWNUM = 1;
135 EXCEPTION
136 WHEN OTHERS THEN
137 x_location_id := 0;
138 END;
139
140
141 IF (X_location_id = 0) THEN
142
143
144 SELECT HR.EMPLOYEE_ID,
145 HR.FULL_NAME,
146 NVL(HR.LOCATION_ID,0)
147 INTO X_emp_id,
148 X_emp_name,
149 X_location_id
150 FROM FND_USER FND, PER_EMPLOYEES_CURRENT_X HR
151 WHERE FND.USER_ID = TO_NUMBER(X_user_id)
152 AND FND.EMPLOYEE_ID = HR.EMPLOYEE_ID
153 AND ROWNUM = 1;
154
155 END IF ;
156
157 /* DEBUG:
158 ** GK:This column has been obsoleted by AOL. Need to check
159 ** on the implications of this change
160 */
161 --AND FND.PERSON_TYPE = 'E'
162
163 /* if no rows selected
164 then user is not an employee
165 else user is an employee */
166
167 ELSE
168
169 -- Bug 4664653
170 -- Add to_number to x_user_id
171
172 --Bug 13552967
173 --Included Exception block to avoid failure of below
174 --sql by bug 10413227
175 -- subquery merged with main queyr as part of bug:15924594 to improve the performance.
176 BEGIN
177 SELECT HR.EMPLOYEE_ID,
178 HR.FULL_NAME,
179 NVL(HR.LOCATION_ID,0)
180 INTO X_emp_id,
181 X_emp_name,
182 X_location_id
183 FROM (SELECT p.person_id employee_id,
184 p.full_name,
185 a.set_of_books_id,
186 a.location_id
187 FROM per_people_f p,
188 per_all_assignments_f a,
189 per_periods_of_service ps,
190 fnd_user fnd,
191 hr_operating_units org
192 WHERE a.person_id = p.person_id
193 AND a.person_id = ps.person_id
194 AND a.assignment_type = 'E'
195 AND p.employee_number IS NOT NULL
196 AND a.period_of_service_id = ps.period_of_service_id
197 -- AND a.primary_flag = 'Y'
198 AND TRUNC(SYSDATE) BETWEEN p.effective_start_date AND p.effective_end_date
199 AND TRUNC(SYSDATE) BETWEEN a.effective_start_date AND a.effective_end_date
200 AND ( ps.actual_termination_date >= TRUNC(SYSDATE)
201 OR ps.actual_termination_date IS NULL )
202 AND fnd.user_id = TO_NUMBER(X_user_id)
203 AND fnd.employee_id = p.person_id
204 AND a.set_of_books_id = org.set_of_books_id
205 AND org.organization_id = fnd_global.org_id
206 UNION ALL
207 SELECT p.person_id employee_id,
208 p.full_name,
209 a.set_of_books_id,
210 a.location_id
211 FROM per_people_f p,
212 per_all_assignments_f a,
213 per_periods_of_placement pp,
214 fnd_user fnd,
215 hr_operating_units org
216 WHERE a.person_id = p.person_id
217 AND a.person_id = pp.person_id
218 AND a.assignment_type = 'C'
219 AND p.npw_number IS NOT NULL
220 AND a.period_of_placement_date_start = pp.date_start
221 -- AND a.primary_flag = 'Y'
222 AND TRUNC(SYSDATE) BETWEEN p.effective_start_date AND p.effective_end_date
223 AND TRUNC(SYSDATE) BETWEEN a.effective_start_date AND a.effective_end_date
224 AND ( pp.actual_termination_date >= TRUNC(SYSDATE)
225 OR pp.actual_termination_date IS NULL )
226 AND fnd.user_id = TO_NUMBER(X_user_id)
227 AND fnd.employee_id = p.person_id
228 AND a.set_of_books_id = org.set_of_books_id
229 AND org.organization_id = fnd_global.org_id
230 ) HR
231 WHERE ROWNUM = 1;
232 EXCEPTION
233 WHEN OTHERS THEN
234 x_location_id := 0;
235 END;
236
237 IF (X_location_id = 0) THEN
238
239
240 SELECT HR.PERSON_ID,
241 HR.FULL_NAME,
242 NVL(HR.LOCATION_ID,0)
243 INTO X_emp_id,
244 X_emp_name,
245 X_location_id
246 FROM FND_USER FND, PER_WORKFORCE_CURRENT_X HR
247 WHERE FND.USER_ID = TO_NUMBER(X_user_id)
248 AND FND.EMPLOYEE_ID = HR.PERSON_ID
249 AND ROWNUM = 1;
250
251 END IF;
252
253 END IF;
254 --<R12 CWK Enhancemment End>
255
256 -- R12 CWK Enhancemment
257 -- emp_flag would now refelect whether either an employee
258 -- or Contigent Worker setup as employee is valid
259 emp_flag := TRUE;
260
261 EXCEPTION
262 WHEN NO_DATA_FOUND THEN
263 /* the user is not an employee */
264 emp_flag := FALSE ;
265
266 -- Bug 4664653. Need not return at this stage.
267 -- Go ahead and complete the procedure.
268 -- return(TRUE) ;
269
270 WHEN OTHERS THEN
271 po_message_s.sql_error('poxpoeri',X_progress,sqlcode);
272 raise;
273 END ;
274
275
276 /* get location_code */
277
278 IF (X_location_id <> 0) THEN
279 BEGIN
280
281 X_Progress := '020';
282
283 /* if location id belongs to an org
284 if the org is in the current set of books
285 return location code
286 else
287 return location id is 0
288
289 */
290 --<R12 MOAC removed FSP from the query>
291 SELECT HR.LOCATION_CODE
292 INTO X_location_code
293 FROM HR_LOCATIONS HR,
294 ORG_ORGANIZATION_DEFINITIONS OOD
295 WHERE HR.LOCATION_ID = X_location_id
296 AND HR.INVENTORY_ORGANIZATION_ID = OOD.ORGANIZATION_ID (+) ;
297
298 EXCEPTION
299 WHEN NO_DATA_FOUND THEN
300 X_location_id := 0 ;
301 WHEN OTHERS THEN
302 po_message_s.sql_error('poxpoeri',X_progress,sqlcode);
303 raise;
304 END ;
305 END IF ;
306
307 /* check if employee is a buyer */
308 -- Bug 4664653. Check for buyer only if employee id is not null
309 --
310 IF (NVL(x_emp_id, 0) <> 0) THEN
311 BEGIN
312
313 X_progress := '030';
314
315 SELECT 'Y'
316 INTO X_buyer_code
317 FROM PO_AGENTS
318 WHERE agent_id = X_emp_id
319 AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE - 1)
320 AND NVL(END_DATE_ACTIVE, SYSDATE + 1);
321
322 /* if no rows returned
323 then user is not a buyer
324 else user is a buyer */
325
326 is_buyer := TRUE ;
327
328 EXCEPTION
329 WHEN NO_DATA_FOUND THEN
330 /* user is not a buyer */
331 is_buyer := FALSE ;
332 WHEN OTHERS THEN
333 po_message_s.sql_error('get_employee',X_progress,sqlcode);
334 raise;
335 END ;
336 END IF;
337
338
339 /* assign all the local variables to the parameters */
340
341 emp_id := X_emp_id;
342 emp_name := X_emp_name ;
343
344
345 IF (X_location_id <> 0) THEN
346 location_id := X_location_id ;
347 location_code := X_location_code ;
348 ELSE
349 /*Bug 2105925. We should not be passing 0 for the location_id if it is
350 not found. Instead we have to return null.
351 */
352 location_id := '' ;
353 location_code := '' ;
354 END IF ;
355
356 -- Bug 4664653 START
357 -- Cache the information in global variables for later use
358 --
359 g_emp_id := emp_id;
360 g_emp_name := emp_name;
361 g_location_id := location_id;
362 g_location_code := location_code;
363 g_is_buyer := is_buyer;
364 g_emp_flag := emp_flag;
365 g_user_id := X_user_id;
366
367 -- Bug 4664653 END
368 return(TRUE);
369
370
371
372 exception
373 when others then
374 po_message_s.sql_error('get_employee','000', sqlcode);
375 raise;
376
377 END get_employee ;
378
379 /*===========================================================================
380
381 PROCEDURE NAME: test_online_user
382
383 ===========================================================================*/
384
385 PROCEDURE test_online_user (x_person_id NUMBER) IS
386 x_online_user BOOLEAN;
387 x_str VARCHAR2(30);
388 BEGIN
389
390 --dbms_output.put_line('before call');
391
392
393 x_online_user := po_employees_sv.online_user(x_person_id);
394
395 --dbms_output.put_line('after call');
396 If x_online_user = TRUE THEN
397 x_str := 'TRUE';
398 ELSE
399 x_str := 'FALSE';
400 END IF;
401
402 --dbms_output.put_line('Return Value =' ||x_str);
403
404 END;
405
406 /*===========================================================================
407
408 PROCEDURE NAME: online_user
409
410 ===========================================================================*/
411
412 FUNCTION online_user (x_person_id NUMBER) RETURN BOOLEAN IS
413 x_progress VARCHAR2(3) := '';
414 x_data_exists NUMBER := 0;
415 BEGIN
416
417 IF x_person_id IS NOT NULL THEN
418
419 x_progress := '010';
420
421 SELECT count(*)
422 INTO x_data_exists
423 FROM fnd_user
424 WHERE employee_id = x_person_id
425 AND sysdate < nvl(end_date, sysdate + 1);
426 /* DEBUG:
427 ** GK:This column has been obsoleted by AOL. Need to check
428 ** on the implications of this change
429 */
430 -- AND person_type = 'E'
431
432 ELSE
433 return(FALSE);
434 END IF;
435
436 x_progress := '020';
437 IF x_data_exists > 0 THEN
438 return TRUE;
439 ELSE
440 return FALSE;
441 END IF;
442
443 EXCEPTION
444 WHEN OTHERS THEN
445 --dbms_output.put_line('In Exception');
446 PO_MESSAGE_S.SQL_ERROR('PO_EMPLOYEES_SV.ONLINE_USER', x_progress, sqlcode);
447 RAISE;
448 END;
449
450 /*===========================================================================
451
452 PROCEDURE NAME: test_get_employee_name
453
454 ===========================================================================*/
455
456 PROCEDURE test_get_employee_name (x_emp_id IN NUMBER) IS
457 x_emp_name VARCHAR2(30) := '';
458 BEGIN
459
460 --dbms_output.put_line('before call');
461
462 get_employee_name (x_emp_id, x_emp_name) ;
463
464
465 --dbms_output.put_line('after call');
466 --dbms_output.put_line('Employee Name ='||x_emp_name);
467
468 END;
469
470 /*===========================================================================
471
472 PROCEDURE NAME: get_employee_name
473
474 ===========================================================================*/
475
476 PROCEDURE get_employee_name (x_emp_id IN NUMBER,
477 x_emp_name OUT NOCOPY VARCHAR2) IS
478 x_progress VARCHAR2(3) := '';
479 BEGIN
480
481 IF x_emp_id IS NOT NULL THEN
482
483 x_progress := '010';
484
485 /* bug 1845314 replaced hr_employees with per_employees_current_x
486 for global supervisor support */
487 SELECT full_name
488 INTO x_emp_name
489 FROM po_workforce_current_x --<BUG 6615913>
490 WHERE person_id = x_emp_id;
491
492 ELSE
493 x_progress := '015';
494 x_emp_name := '';
495 END IF;
496
497 EXCEPTION
498 WHEN OTHERS THEN
499 --dbms_output.put_line('In Exception');
500 PO_MESSAGE_S.SQL_ERROR('GET_EMPLOYEE_NAME', x_progress, sqlcode);
501 RAISE;
502 END get_employee_name;
503
504 /*===========================================================================
505
506 PROCEDURE NAME: derive_employee_info()
507
508 ===========================================================================*/
509
510 PROCEDURE derive_employee_info (
511 p_emp_record IN OUT NOCOPY RCV_SHIPMENT_OBJECT_SV.Employee_id_record_type) IS
512
513 cid INTEGER;
514 rows_processed INTEGER;
515 sql_str VARCHAR2(2000);
516
517 Emp_name_null BOOLEAN := TRUE;
518 Emp_id_null BOOLEAN := TRUE;
519
520 BEGIN
521
522 sql_str := 'select hr_employees.full_name, hr_employees.employee_id from hr_employees where ';
523
524 IF p_emp_record.employee_id IS NULL and
525 p_emp_record.employee_name IS NULL THEN
526
527 p_emp_record.error_record.error_status := 'W';
528 RETURN;
529
530 END IF;
531
532 IF p_emp_record.employee_name IS NOT NULL and
533 p_emp_record.employee_id IS NOT NULL THEN
534
535 p_emp_record.error_record.error_status := 'S';
536 RETURN;
537
538 END IF;
539
540 IF p_emp_record.employee_name IS NOT NULL THEN
541
542 sql_str := sql_str || ' full_name = :v_emp_name and';
543 emp_name_null := FALSE;
544
545 END IF;
546
547 IF p_emp_record.employee_id IS NOT NULL THEN
548
549 sql_str := sql_str || ' employee_id = :v_emp_id and';
550 emp_id_null := FALSE;
551
552 END IF;
553
554 sql_str := substr(sql_str,1,length(sql_str)-3);
555
556 --dbms_output.put_line(substr(sql_str,1,255));
557 --dbms_output.put_line(substr(sql_str,256,255));
558 --dbms_output.put_line(substr(sql_str,513,255));
559
560 cid := dbms_sql.open_cursor;
561
562 dbms_sql.parse(cid, sql_str , dbms_sql.native);
563
564 dbms_sql.define_column(cid,1,p_emp_record.employee_name,255);
565 dbms_sql.define_column(cid,2,p_emp_record.employee_id);
566
567 IF NOT emp_name_null THEN
568
569 dbms_sql.bind_variable(cid,'v_emp_name',p_emp_record.employee_name);
570
571 END IF;
572
573 IF NOT emp_id_null THEN
574
575 dbms_sql.bind_variable(cid,'v_emp_id',p_emp_record.employee_id);
576
577 END IF;
578
579 rows_processed := dbms_sql.execute_and_fetch(cid);
580
581 IF rows_processed = 1 THEN
582
583 IF emp_name_null THEN
584 dbms_sql.column_value(cid,1,p_emp_record.employee_name);
585 END IF;
586
587 IF emp_id_null THEN
588 dbms_sql.column_value(cid,2,p_emp_record.employee_id);
589 END IF;
590
591 p_emp_record.error_record.error_status := 'S';
592
593 ELSIF rows_processed = 0 THEN
594
595 p_emp_record.error_record.error_status := 'W';
596
597 ELSE
598
599 p_emp_record.error_record.error_status := 'W';
600
601 END IF;
602
603 IF dbms_sql.is_open(cid) THEN
604 dbms_sql.close_cursor(cid);
605 END IF;
606
607 EXCEPTION
608 WHEN others THEN
609
610 IF dbms_sql.is_open(cid) THEN
611 dbms_sql.close_cursor(cid);
612 END IF;
613
614 p_emp_record.error_record.error_status := 'U';
615 p_emp_record.error_record.error_message := sqlerrm;
616 IF (g_asn_debug = 'Y') THEN
617 asn_debug.put_line(p_emp_record.error_record.error_message);
618 END IF;
619
620 END derive_employee_info;
621
622
623 /*===========================================================================
624
625 PROCEDURE NAME: validate_employee_info()
626
627 ===========================================================================*/
628
629 PROCEDURE validate_employee_info (
630 p_emp_record IN OUT NOCOPY RCV_SHIPMENT_OBJECT_SV.Employee_id_record_type) IS
631
632 X_cid INTEGER;
633 X_rows_processed INTEGER;
634 X_sql_str VARCHAR2(2000);
635
636 X_Emp_name_null BOOLEAN := TRUE;
637 X_Emp_id_null BOOLEAN := TRUE;
638
639 begin
640
641 X_sql_str := 'select hr_employees.full_name, hr_employees.employee_id from hr_employees where ';
642
643 IF p_emp_record.employee_id IS NULL and
644 p_emp_record.employee_name IS NULL THEN
645
646 --dbms_output.put_line('ALLNULL');
647 p_emp_record.error_record.error_status := 'E';
648 p_emp_record.error_record.error_message := 'ALLNULL';
649 RETURN;
650
651 END IF;
652
653
654 IF p_emp_record.employee_name IS NOT NULL THEN
655
656 X_sql_str := X_sql_str || ' full_name = :v_emp_name and';
657 X_emp_name_null := FALSE;
658
659 END IF;
660
661 IF p_emp_record.employee_id IS NOT NULL THEN
662
663 X_sql_str := X_sql_str || ' employee_id = :v_emp_id and';
664 X_emp_id_null := FALSE;
665
666 END IF;
667
668 X_sql_str := substr(X_sql_str,1,length(X_sql_str)-3);
669
670 --dbms_output.put_line(substr(X_sql_str,1,255));
671 --dbms_output.put_line(substr(X_sql_str,256,255));
672 --dbms_output.put_line(substr(X_sql_str,513,255));
673
674 X_cid := dbms_sql.open_cursor;
675
676 dbms_sql.parse(X_cid, X_sql_str , dbms_sql.native);
677
678 dbms_sql.define_column(X_cid,1,p_emp_record.employee_name,255);
679 dbms_sql.define_column(X_cid,2,p_emp_record.employee_id);
680
681 IF NOT X_emp_name_null THEN
682
683 dbms_sql.bind_variable(X_cid,'v_emp_name',p_emp_record.employee_name);
684
685 END IF;
686
687 IF NOT X_emp_id_null THEN
688
689 dbms_sql.bind_variable(X_cid,'v_emp_id',p_emp_record.employee_id);
690
691 END IF;
692
693 X_rows_processed := dbms_sql.execute_and_fetch(X_cid);
694
695 IF X_rows_processed = 1 THEN
696
697 dbms_sql.column_value(X_cid,1,p_emp_record.employee_name);
698 dbms_sql.column_value(X_cid,2,p_emp_record.employee_id);
699
700 p_emp_record.error_record.error_status := 'S';
701 p_emp_record.error_record.error_message := NULL;
702
703 ELSIF X_rows_processed = 0 THEN
704
705 p_emp_record.error_record.error_status := 'E';
706 p_emp_record.error_record.error_message := 'RECEIVER_ID';
707
708 IF dbms_sql.is_open(X_cid) THEN
709 dbms_sql.close_cursor(X_cid);
710 END IF;
711 RETURN;
712
713 ELSE
714
715 p_emp_record.error_record.error_status := 'E';
716 p_emp_record.error_record.error_message := 'TOOMANYROWS';
717
718 IF dbms_sql.is_open(X_cid) THEN
719 dbms_sql.close_cursor(X_cid);
720 END IF;
721 RETURN;
722
723 END IF;
724
725 IF dbms_sql.is_open(X_cid) THEN
726 dbms_sql.close_cursor(X_cid);
727 END IF;
728
729 EXCEPTION
730 WHEN others THEN
731
732 IF dbms_sql.is_open(X_cid) THEN
733 dbms_sql.close_cursor(X_cid);
734 END IF;
735
736 p_emp_record.error_record.error_status := 'U';
737 p_emp_record.error_record.error_message := sqlerrm;
738 IF (g_asn_debug = 'Y') THEN
739 asn_debug.put_line(p_emp_record.error_record.error_message);
740 END IF;
741
742 END validate_employee_info;
743
744 /**************************************************************************
745 * This function returns the full name of the employee *
746 * added for bug 2228195
747 **************************************************************************/
748 FUNCTION get_emp_name(x_person_id IN NUMBER) RETURN VARCHAR2 IS
749
750 x_value VARCHAR2(1000) := '';
751 x_date date;
752
753 cursor c1 is
754 select distinct prf.full_name,prf.effective_start_date
755 from per_all_people_f prf
756 where prf.person_id = x_person_id
757 order by prf.effective_start_date desc;
758
759 begin
760
761 /*
762 * (1) Even the approver is no longer with the org, still need to retieve his/her
763 * Full Name.
764 * (2) Suppose a person has multiple employing history with the org, there're
765 * multiple records for the person in per_all_people_f table.
766 */
767
768 open c1;
769 fetch c1 into x_value,x_date;
770 close c1;
771
772 return x_value;
773
774 exception
775 when others then
776 return null;
777
778 end get_emp_name;
779
780 END PO_EMPLOYEES_SV;
[Home] [Help]