1 PACKAGE BODY AP_NOTES_PVT AS
2 /* $Header: apwnotvb.pls 120.2 2006/01/09 21:35:58 rlangi noship $ */
3
4
5 PKG_NAME CONSTANT VARCHAR2(30):='AP_NOTES_PVT';
6
7
8 /*===========================================================================*/
9 -- Start of comments
10 --
11 -- API NAME : Get_User_Full_Name
12 -- TYPE : Public
13 -- PURPOSE : Given a USER_ID the function will return the
14 -- full name of the user.
15 -- PRE_REQS : None
16 --
17 -- PARAMETERS :
18 -- IN -
19 -- OUT -
20 -- IN OUT NO COPY -
21 --
22 -- MODIFICATION HISTORY :
23 -- Date Author Description of Changes
24 -- 11-Nov-2003 V Nama Created
25 --
26 -- NOTES : Based on API - JTF_COMMON_PVT.GetUserInfo
27 --
28 -- End of comments
29 /*===========================================================================*/
30 FUNCTION Get_User_Full_Name (
31 p_user_id IN NUMBER
32 )
33 RETURN VARCHAR2
34 IS
35
36 CURSOR c_user
37 /****************************************************************************
38 ** Cursor used to fetch the foreign keys needed to access the source tables
39 ****************************************************************************/
40 (b_user_id IN NUMBER
41 )IS SELECT employee_id
42 , customer_id
43 , supplier_id
44 , description
45 , user_name
46 FROM fnd_user
47 WHERE user_id = b_user_id;
48
49 CURSOR c_employee
50 /****************************************************************************
51 ** Cursor used to fetch the employee name in case the foreign key is to an
52 ** Employee
53 ****************************************************************************/
54 (b_employee_id IN NUMBER
55 )IS SELECT full_name
56 , employee_number
57 FROM per_people_x -- Bug 4730292/4890523
58 WHERE person_id = b_employee_id;
59
60 CURSOR c_party
61 /****************************************************************************
62 ** Cursor used to fetch the party name in case the foreign key is to a
63 ** Customer or Supplier
64 ****************************************************************************/
65 (b_party_id IN NUMBER
66 )IS SELECT party_name
67 , party_number
68 FROM hz_parties
69 WHERE party_id = b_party_id;
70
71 l_employee_id NUMBER;
72 l_customer_id NUMBER;
73 l_supplier_id NUMBER;
74 l_description VARCHAR2(360);
75 l_user_name VARCHAR2(360);
76
77 l_number VARCHAR2(30);
78 l_name VARCHAR2(240);
79 l_display_info VARCHAR2(500);
80
81 BEGIN
82 /*****************************************************************************
83 ** Get the foreigh keys to the user information
84 *****************************************************************************/
85 IF c_user%ISOPEN
86 THEN
87 CLOSE c_user;
88 END IF;
89
90
91 -- get info from fnd_user
92 OPEN c_user(p_user_id);
93
94 FETCH c_user
95 INTO l_employee_id,l_customer_id,l_supplier_id,l_description,l_user_name;
96
97 IF c_user%ISOPEN
98 THEN
99 CLOSE c_user;
100 END IF;
101
102
103 --check employee based name
104 IF (l_employee_id IS NOT NULL)
105 THEN
106 -- get the employee information
107 IF c_employee%ISOPEN
108 THEN
109 CLOSE c_employee;
110 END IF;
111
112 OPEN c_employee(l_employee_id);
113
114 FETCH c_employee INTO l_name,l_number;
115
116 IF c_employee%ISOPEN
117 THEN
118 CLOSE c_employee;
119 END IF;
120 END IF;
121
122
123 --return
124 IF l_name IS NOT NULL
125 THEN
126 RETURN l_name;
127 ELSE
128 IF l_description IS NOT NULL
129 THEN
130 RETURN l_description;
131 ELSE
132 RETURN l_user_name;
133 END IF;
134 END IF;
135
136
137 EXCEPTION
138 WHEN OTHERS
139 THEN
140 IF c_employee%ISOPEN
141 THEN
142 CLOSE c_employee;
143 END IF;
144
145 IF c_party%ISOPEN
146 THEN
147 CLOSE c_party;
148 END IF;
149 RETURN 'Not Found';
150 END Get_User_Full_Name;
151
152
153
154 END AP_NOTES_PVT;
[Home] [Help]