1 PACKAGE BODY PO_EMPLOYEES_SV as
2 /*$Header: POXEMEMB.pls 120.3.12010000.1 2008/07/24 14:12:40 appldev 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 SELECT HR.EMPLOYEE_ID,
108 HR.FULL_NAME,
109 NVL(HR.LOCATION_ID,0)
110 INTO X_emp_id,
111 X_emp_name,
112 X_location_id
113 FROM FND_USER FND, PER_EMPLOYEES_CURRENT_X HR
114 WHERE FND.USER_ID = TO_NUMBER(X_user_id)
115 AND FND.EMPLOYEE_ID = HR.EMPLOYEE_ID
116 AND ROWNUM = 1;
117 /* DEBUG:
118 ** GK:This column has been obsoleted by AOL. Need to check
119 ** on the implications of this change
120 */
121 --AND FND.PERSON_TYPE = 'E'
122
123 /* if no rows selected
124 then user is not an employee
125 else user is an employee */
126
127 ELSE
128
129 -- Bug 4664653
130 -- Add to_number to x_user_id
131 SELECT HR.PERSON_ID,
132 HR.FULL_NAME,
133 NVL(HR.LOCATION_ID,0)
134 INTO X_emp_id,
135 X_emp_name,
136 X_location_id
137 FROM FND_USER FND, PER_WORKFORCE_CURRENT_X HR
138 WHERE FND.USER_ID = TO_NUMBER(X_user_id)
139 AND FND.EMPLOYEE_ID = HR.PERSON_ID
140 AND ROWNUM = 1;
141
142 END IF;
143 --<R12 CWK Enhancemment End>
144
145 -- R12 CWK Enhancemment
146 -- emp_flag would now refelect whether either an employee
147 -- or Contigent Worker setup as employee is valid
148 emp_flag := TRUE;
149
150 EXCEPTION
151 WHEN NO_DATA_FOUND THEN
152 /* the user is not an employee */
153 emp_flag := FALSE ;
154
155 -- Bug 4664653. Need not return at this stage.
156 -- Go ahead and complete the procedure.
157 -- return(TRUE) ;
158
159 WHEN OTHERS THEN
160 po_message_s.sql_error('poxpoeri',X_progress,sqlcode);
161 raise;
162 END ;
163
164
165 /* get location_code */
166
167 IF (X_location_id <> 0) THEN
168 BEGIN
169
170 X_Progress := '020';
171
172 /* if location id belongs to an org
173 if the org is in the current set of books
174 return location code
175 else
176 return location id is 0
177
178 */
179 --<R12 MOAC removed FSP from the query>
180 SELECT HR.LOCATION_CODE
181 INTO X_location_code
182 FROM HR_LOCATIONS HR,
183 ORG_ORGANIZATION_DEFINITIONS OOD
184 WHERE HR.LOCATION_ID = X_location_id
185 AND HR.INVENTORY_ORGANIZATION_ID = OOD.ORGANIZATION_ID (+) ;
186
187 EXCEPTION
188 WHEN NO_DATA_FOUND THEN
189 X_location_id := 0 ;
190 WHEN OTHERS THEN
191 po_message_s.sql_error('poxpoeri',X_progress,sqlcode);
192 raise;
193 END ;
194 END IF ;
195
196 /* check if employee is a buyer */
197 -- Bug 4664653. Check for buyer only if employee id is not null
198 --
199 IF (NVL(x_emp_id, 0) <> 0) THEN
200 BEGIN
201
202 X_progress := '030';
203
204 SELECT 'Y'
205 INTO X_buyer_code
206 FROM PO_AGENTS
207 WHERE agent_id = X_emp_id
208 AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE - 1)
209 AND NVL(END_DATE_ACTIVE, SYSDATE + 1);
210
211 /* if no rows returned
212 then user is not a buyer
213 else user is a buyer */
214
215 is_buyer := TRUE ;
216
217 EXCEPTION
218 WHEN NO_DATA_FOUND THEN
219 /* user is not a buyer */
220 is_buyer := FALSE ;
221 WHEN OTHERS THEN
222 po_message_s.sql_error('get_employee',X_progress,sqlcode);
223 raise;
224 END ;
225 END IF;
226
227
228 /* assign all the local variables to the parameters */
229
230 emp_id := X_emp_id;
231 emp_name := X_emp_name ;
232
233
234 IF (X_location_id <> 0) THEN
235 location_id := X_location_id ;
236 location_code := X_location_code ;
237 ELSE
238 /*Bug 2105925. We should not be passing 0 for the location_id if it is
239 not found. Instead we have to return null.
240 */
241 location_id := '' ;
242 location_code := '' ;
243 END IF ;
244
245 -- Bug 4664653 START
246 -- Cache the information in global variables for later use
247 --
248 g_emp_id := emp_id;
249 g_emp_name := emp_name;
250 g_location_id := location_id;
251 g_location_code := location_code;
252 g_is_buyer := is_buyer;
253 g_emp_flag := emp_flag;
254 g_user_id := X_user_id;
255
256 -- Bug 4664653 END
257 return(TRUE);
258
259
260
261 exception
262 when others then
263 po_message_s.sql_error('get_employee','000', sqlcode);
264 raise;
265
266 END get_employee ;
267
268 /*===========================================================================
269
270 PROCEDURE NAME: test_online_user
271
272 ===========================================================================*/
273
274 PROCEDURE test_online_user (x_person_id NUMBER) IS
275 x_online_user BOOLEAN;
276 x_str VARCHAR2(30);
277 BEGIN
278
279 --dbms_output.put_line('before call');
280
281
282 x_online_user := po_employees_sv.online_user(x_person_id);
283
284 --dbms_output.put_line('after call');
285 If x_online_user = TRUE THEN
286 x_str := 'TRUE';
287 ELSE
288 x_str := 'FALSE';
289 END IF;
290
291 --dbms_output.put_line('Return Value =' ||x_str);
292
293 END;
294
295 /*===========================================================================
296
297 PROCEDURE NAME: online_user
298
299 ===========================================================================*/
300
301 FUNCTION online_user (x_person_id NUMBER) RETURN BOOLEAN IS
302 x_progress VARCHAR2(3) := '';
303 x_data_exists NUMBER := 0;
304 BEGIN
305
306 IF x_person_id IS NOT NULL THEN
307
308 x_progress := '010';
309
310 SELECT count(*)
311 INTO x_data_exists
312 FROM fnd_user
313 WHERE employee_id = x_person_id
314 AND sysdate < nvl(end_date, sysdate + 1);
315 /* DEBUG:
316 ** GK:This column has been obsoleted by AOL. Need to check
317 ** on the implications of this change
318 */
319 -- AND person_type = 'E'
320
321 ELSE
322 return(FALSE);
323 END IF;
324
325 x_progress := '020';
326 IF x_data_exists > 0 THEN
327 return TRUE;
328 ELSE
329 return FALSE;
330 END IF;
331
332 EXCEPTION
333 WHEN OTHERS THEN
334 --dbms_output.put_line('In Exception');
335 PO_MESSAGE_S.SQL_ERROR('PO_EMPLOYEES_SV.ONLINE_USER', x_progress, sqlcode);
336 RAISE;
337 END;
338
339 /*===========================================================================
340
341 PROCEDURE NAME: test_get_employee_name
342
343 ===========================================================================*/
344
345 PROCEDURE test_get_employee_name (x_emp_id IN NUMBER) IS
346 x_emp_name VARCHAR2(30) := '';
347 BEGIN
348
349 --dbms_output.put_line('before call');
350
351 get_employee_name (x_emp_id, x_emp_name) ;
352
353
354 --dbms_output.put_line('after call');
355 --dbms_output.put_line('Employee Name ='||x_emp_name);
356
357 END;
358
359 /*===========================================================================
360
361 PROCEDURE NAME: get_employee_name
362
363 ===========================================================================*/
364
365 PROCEDURE get_employee_name (x_emp_id IN NUMBER,
366 x_emp_name OUT NOCOPY VARCHAR2) IS
367 x_progress VARCHAR2(3) := '';
368 BEGIN
369
370 IF x_emp_id IS NOT NULL THEN
371
372 x_progress := '010';
373
374 /* bug 1845314 replaced hr_employees with per_employees_current_x
375 for global supervisor support */
376 SELECT full_name
377 INTO x_emp_name
378 FROM po_workforce_current_x --<BUG 6615913>
379 WHERE person_id = x_emp_id;
380
381 ELSE
382 x_progress := '015';
383 x_emp_name := '';
384 END IF;
385
386 EXCEPTION
387 WHEN OTHERS THEN
388 --dbms_output.put_line('In Exception');
389 PO_MESSAGE_S.SQL_ERROR('GET_EMPLOYEE_NAME', x_progress, sqlcode);
390 RAISE;
391 END get_employee_name;
392
393 /*===========================================================================
394
395 PROCEDURE NAME: derive_employee_info()
396
397 ===========================================================================*/
398
399 PROCEDURE derive_employee_info (
400 p_emp_record IN OUT NOCOPY RCV_SHIPMENT_OBJECT_SV.Employee_id_record_type) IS
401
402 cid INTEGER;
403 rows_processed INTEGER;
404 sql_str VARCHAR2(2000);
405
406 Emp_name_null BOOLEAN := TRUE;
407 Emp_id_null BOOLEAN := TRUE;
408
409 BEGIN
410
411 sql_str := 'select hr_employees.full_name, hr_employees.employee_id from hr_employees where ';
412
413 IF p_emp_record.employee_id IS NULL and
414 p_emp_record.employee_name IS NULL THEN
415
416 p_emp_record.error_record.error_status := 'W';
417 RETURN;
418
419 END IF;
420
421 IF p_emp_record.employee_name IS NOT NULL and
422 p_emp_record.employee_id IS NOT NULL THEN
423
424 p_emp_record.error_record.error_status := 'S';
425 RETURN;
426
427 END IF;
428
429 IF p_emp_record.employee_name IS NOT NULL THEN
430
431 sql_str := sql_str || ' full_name = :v_emp_name and';
432 emp_name_null := FALSE;
433
434 END IF;
435
436 IF p_emp_record.employee_id IS NOT NULL THEN
437
438 sql_str := sql_str || ' employee_id = :v_emp_id and';
439 emp_id_null := FALSE;
440
441 END IF;
442
443 sql_str := substr(sql_str,1,length(sql_str)-3);
444
445 --dbms_output.put_line(substr(sql_str,1,255));
446 --dbms_output.put_line(substr(sql_str,256,255));
447 --dbms_output.put_line(substr(sql_str,513,255));
448
449 cid := dbms_sql.open_cursor;
450
451 dbms_sql.parse(cid, sql_str , dbms_sql.native);
452
453 dbms_sql.define_column(cid,1,p_emp_record.employee_name,255);
454 dbms_sql.define_column(cid,2,p_emp_record.employee_id);
455
456 IF NOT emp_name_null THEN
457
458 dbms_sql.bind_variable(cid,'v_emp_name',p_emp_record.employee_name);
459
460 END IF;
461
462 IF NOT emp_id_null THEN
463
464 dbms_sql.bind_variable(cid,'v_emp_id',p_emp_record.employee_id);
465
466 END IF;
467
468 rows_processed := dbms_sql.execute_and_fetch(cid);
469
470 IF rows_processed = 1 THEN
471
472 IF emp_name_null THEN
473 dbms_sql.column_value(cid,1,p_emp_record.employee_name);
474 END IF;
475
476 IF emp_id_null THEN
477 dbms_sql.column_value(cid,2,p_emp_record.employee_id);
478 END IF;
479
480 p_emp_record.error_record.error_status := 'S';
481
482 ELSIF rows_processed = 0 THEN
483
484 p_emp_record.error_record.error_status := 'W';
485
486 ELSE
487
488 p_emp_record.error_record.error_status := 'W';
489
490 END IF;
491
492 IF dbms_sql.is_open(cid) THEN
493 dbms_sql.close_cursor(cid);
494 END IF;
495
496 EXCEPTION
497 WHEN others THEN
498
499 IF dbms_sql.is_open(cid) THEN
500 dbms_sql.close_cursor(cid);
501 END IF;
502
503 p_emp_record.error_record.error_status := 'U';
504 p_emp_record.error_record.error_message := sqlerrm;
505 IF (g_asn_debug = 'Y') THEN
506 asn_debug.put_line(p_emp_record.error_record.error_message);
507 END IF;
508
509 END derive_employee_info;
510
511
512 /*===========================================================================
513
514 PROCEDURE NAME: validate_employee_info()
515
516 ===========================================================================*/
517
518 PROCEDURE validate_employee_info (
519 p_emp_record IN OUT NOCOPY RCV_SHIPMENT_OBJECT_SV.Employee_id_record_type) IS
520
521 X_cid INTEGER;
522 X_rows_processed INTEGER;
523 X_sql_str VARCHAR2(2000);
524
525 X_Emp_name_null BOOLEAN := TRUE;
526 X_Emp_id_null BOOLEAN := TRUE;
527
528 begin
529
530 X_sql_str := 'select hr_employees.full_name, hr_employees.employee_id from hr_employees where ';
531
532 IF p_emp_record.employee_id IS NULL and
533 p_emp_record.employee_name IS NULL THEN
534
535 --dbms_output.put_line('ALLNULL');
536 p_emp_record.error_record.error_status := 'E';
537 p_emp_record.error_record.error_message := 'ALLNULL';
538 RETURN;
539
540 END IF;
541
542
543 IF p_emp_record.employee_name IS NOT NULL THEN
544
545 X_sql_str := X_sql_str || ' full_name = :v_emp_name and';
546 X_emp_name_null := FALSE;
547
548 END IF;
549
550 IF p_emp_record.employee_id IS NOT NULL THEN
551
552 X_sql_str := X_sql_str || ' employee_id = :v_emp_id and';
553 X_emp_id_null := FALSE;
554
555 END IF;
556
557 X_sql_str := substr(X_sql_str,1,length(X_sql_str)-3);
558
559 --dbms_output.put_line(substr(X_sql_str,1,255));
560 --dbms_output.put_line(substr(X_sql_str,256,255));
561 --dbms_output.put_line(substr(X_sql_str,513,255));
562
563 X_cid := dbms_sql.open_cursor;
564
565 dbms_sql.parse(X_cid, X_sql_str , dbms_sql.native);
566
567 dbms_sql.define_column(X_cid,1,p_emp_record.employee_name,255);
568 dbms_sql.define_column(X_cid,2,p_emp_record.employee_id);
569
570 IF NOT X_emp_name_null THEN
571
572 dbms_sql.bind_variable(X_cid,'v_emp_name',p_emp_record.employee_name);
573
574 END IF;
575
576 IF NOT X_emp_id_null THEN
577
578 dbms_sql.bind_variable(X_cid,'v_emp_id',p_emp_record.employee_id);
579
580 END IF;
581
582 X_rows_processed := dbms_sql.execute_and_fetch(X_cid);
583
584 IF X_rows_processed = 1 THEN
585
586 dbms_sql.column_value(X_cid,1,p_emp_record.employee_name);
587 dbms_sql.column_value(X_cid,2,p_emp_record.employee_id);
588
589 p_emp_record.error_record.error_status := 'S';
590 p_emp_record.error_record.error_message := NULL;
591
592 ELSIF X_rows_processed = 0 THEN
593
594 p_emp_record.error_record.error_status := 'E';
595 p_emp_record.error_record.error_message := 'RECEIVER_ID';
596
597 IF dbms_sql.is_open(X_cid) THEN
598 dbms_sql.close_cursor(X_cid);
599 END IF;
600 RETURN;
601
602 ELSE
603
604 p_emp_record.error_record.error_status := 'E';
605 p_emp_record.error_record.error_message := 'TOOMANYROWS';
606
607 IF dbms_sql.is_open(X_cid) THEN
608 dbms_sql.close_cursor(X_cid);
609 END IF;
610 RETURN;
611
612 END IF;
613
614 IF dbms_sql.is_open(X_cid) THEN
615 dbms_sql.close_cursor(X_cid);
616 END IF;
617
618 EXCEPTION
619 WHEN others THEN
620
621 IF dbms_sql.is_open(X_cid) THEN
622 dbms_sql.close_cursor(X_cid);
623 END IF;
624
625 p_emp_record.error_record.error_status := 'U';
626 p_emp_record.error_record.error_message := sqlerrm;
627 IF (g_asn_debug = 'Y') THEN
628 asn_debug.put_line(p_emp_record.error_record.error_message);
629 END IF;
630
631 END validate_employee_info;
632
633 /**************************************************************************
634 * This function returns the full name of the employee *
635 * added for bug 2228195
636 **************************************************************************/
637 FUNCTION get_emp_name(x_person_id IN NUMBER) RETURN VARCHAR2 IS
638
639 x_value VARCHAR2(1000) := '';
640 x_date date;
641
642 cursor c1 is
643 select distinct prf.full_name,prf.effective_start_date
644 from per_all_people_f prf
645 where prf.person_id = x_person_id
646 order by prf.effective_start_date desc;
647
648 begin
649
650 /*
651 * (1) Even the approver is no longer with the org, still need to retieve his/her
652 * Full Name.
653 * (2) Suppose a person has multiple employing history with the org, there're
654 * multiple records for the person in per_all_people_f table.
655 */
656
657 open c1;
658 fetch c1 into x_value,x_date;
659 close c1;
660
661 return x_value;
662
663 exception
664 when others then
665 return null;
666
667 end get_emp_name;
668
669 END PO_EMPLOYEES_SV;
[Home] [Help]