1 PACKAGE BODY AP_NOTES_PVT AS
2 /* $Header: apwnotvb.pls 120.4 2012/02/09 14:47:56 dsadipir ship $ */
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 -- Bug: 12882504, Approval notes not displayed for non supervisory AME rules
153 FUNCTION Get_User_Full_Name (
154 p_user_id IN NUMBER,
155 p_source_object_id IN NUMBER
156 )
157 RETURN VARCHAR2
158 IS
159 l_approval_type AP_EXPENSE_REPORT_HEADERS_ALL.APPROVAL_TYPE%TYPE;
160 l_display_name WF_ROLES.DISPLAY_NAME%TYPE;
161 BEGIN
162 BEGIN
163 l_display_name := NULL;
164 SELECT approval_type INTO l_approval_type
165 FROM AP_EXPENSE_REPORT_HEADERS_ALL
166 WHERE report_header_id = p_source_object_id;
167
168 -- Bug 13696856: Author should be shown in the approval notes
169 IF(NVL(l_approval_type, 'PER') <> 'PER') THEN
170 SELECT display_name
171 INTO l_display_name
172 FROM wf_roles
173 WHERE orig_system = l_approval_type
174 AND orig_system_id = p_user_id
175 AND rownum = 1;
176 END IF;
177
178 IF(l_display_name IS NULL) THEN
179 l_display_name := Get_User_Full_Name(p_user_id);
180 END IF;
181 EXCEPTION
182 WHEN OTHERS THEN
183 l_approval_type := null;
184 l_display_name := NULL;
185 END;
186
187 RETURN l_display_name;
188
189 END Get_User_Full_Name;
190
191 END AP_NOTES_PVT;
[Home] [Help]