1 PACKAGE BODY JTF_COMMON_PVT
2 /* $Header: jtfvcmnb.pls 120.2 2006/06/29 07:26:46 abraina ship $ */
3 AS
4
5 FUNCTION GetUserInfo
6 /*******************************************************************************
7 ** Given a USER_ID the function will return the username/partyname. This
8 ** Function is used to display the CREATED_BY who column information on JTF
9 ** transaction pages.
10 *******************************************************************************/
11 (p_user_id IN NUMBER
12 )RETURN VARCHAR2
13 IS
14 CURSOR c_user
15 /****************************************************************************
16 ** Cursor used to fetch the foreign keys needed to access the source tables
17 ****************************************************************************/
18 (b_user_id IN NUMBER
19 )IS SELECT employee_id
20 , customer_id
21 , supplier_id
22 , user_name
23 FROM fnd_user
24 WHERE user_id = b_user_id;
25
26 CURSOR c_employee_active
27 /****************************************************************************
28 ** Cursor used to fetch the employee name in case the foreign key is to an
29 ** Employee
30 ****************************************************************************/
31 (b_employee_id IN NUMBER
32 )IS SELECT full_name
33 , employee_number
34 FROM per_all_people_f
35 WHERE person_id = b_employee_id
36 AND trunc(SYSDATE) BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;
37
38 CURSOR c_employee
39 /****************************************************************************
40 ** Cursor used to fetch the employee name in case the foreign key is to an
41 ** Employee
42 ****************************************************************************/
43 (b_employee_id IN NUMBER
44 )IS SELECT full_name
45 , employee_number
46 FROM per_all_people_f
47 WHERE person_id = b_employee_id;
48
49 CURSOR c_party
50 /****************************************************************************
51 ** Cursor used to fetch the party name in case the foreign key is to a
52 ** Customer or Supplier
53 ****************************************************************************/
54 (b_party_id IN NUMBER
55 )IS SELECT party_name
56 , party_number
57 FROM hz_parties
58 WHERE party_id = b_party_id;
59
60 l_employee_id NUMBER;
61 l_customer_id NUMBER;
62 l_supplier_id NUMBER;
63 -- For bug 5360709. Increased the variable length.
64 l_user_name VARCHAR2(1000);
65
66 l_number VARCHAR2(30);
67 l_name VARCHAR2(500);
68 l_display_info VARCHAR2(500);
69
70
71 BEGIN
72 /*****************************************************************************
73 ** Get the foreigh keys to the user information
74 *****************************************************************************/
75 IF c_user%ISOPEN
76 THEN
77 CLOSE c_user;
78 END IF;
79
80 OPEN c_user(p_user_id);
81
82 FETCH c_user INTO l_employee_id,l_customer_id,l_supplier_id,l_user_name;
83
84 IF c_user%ISOPEN
85 THEN
86 CLOSE c_user;
87 END IF;
88
89 IF (l_employee_id IS NOT NULL)
90 THEN
91 -- get the employee information
92 --first see if there are any active records
93 IF c_employee_active%ISOPEN
94 THEN
95 CLOSE c_employee_active;
96 END IF;
97
98 OPEN c_employee_active(l_employee_id);
99
100 FETCH c_employee_active INTO l_name,l_number;
101
102 IF c_employee_active%NOTFOUND
103 THEN
104 IF c_employee%ISOPEN
105 THEN
106 CLOSE c_employee;
107 END IF;
108
109 OPEN c_employee(l_employee_id);
110
111 FETCH c_employee INTO l_name,l_number;
112
113 IF c_employee%ISOPEN
114 THEN
115 CLOSE c_employee;
116 END IF;
117 END IF;
118
119 IF c_employee_active%ISOPEN
120 THEN
121 CLOSE c_employee_active;
122 END IF;
123
124 ELSIF (l_customer_id IS NOT NULL)
125 THEN
126 -- get the customer information
127 IF c_party%ISOPEN
128 THEN
129 CLOSE c_party;
130 END IF;
131
132 OPEN c_party(l_customer_id);
133
134 FETCH c_party INTO l_name, l_number;
135
136 IF c_party%ISOPEN
137 THEN
138 CLOSE c_party;
139 END IF;
140
141 ELSIF (l_supplier_id IS NOT NULL)
142 THEN
143 -- get the supplier information
144 IF c_party%ISOPEN
145 THEN
146 CLOSE c_party;
147 END IF;
148
149 OPEN c_party(l_supplier_id);
150
151 FETCH c_party INTO l_name, l_number;
152
153 IF c_party%ISOPEN
154 THEN
155 CLOSE c_party;
156 END IF;
157 END IF;
158
159 IF l_name IS NULL
160 THEN
161 RETURN l_user_name;
162 ELSE
163 RETURN l_name||'('||l_user_name||','||l_number||')';
164 END IF;
165
166 EXCEPTION
167 WHEN OTHERS
168 THEN
169 IF c_employee%ISOPEN
170 THEN
171 CLOSE c_employee;
172 END IF;
173
174 IF c_party%ISOPEN
175 THEN
176 CLOSE c_party;
177 END IF;
178 RETURN 'Not Found';
179 END GetUserInfo;
180
181 END JTF_COMMON_PVT;
[Home] [Help]