1 PACKAGE BODY JTF_IH_GETUSER_PVT AS
2 /* $Header: JTFIHUPB.pls 120.2 2005/07/08 11:26:41 sseshama ship $ */
3
4 FUNCTION GetUserInfo
5 /********************************************************************************* Given a USER_ID the function will return the username/partyname. This
6 ** Function is used to display the CREATED_BY who column information on JTF
7 ** transaction pages.
8 *******************************************************************************/
9 (p_user_id IN NUMBER
10 )RETURN VARCHAR2
11 IS
12 CURSOR c_user
13 /****************************************************************************
14 ** Cursor used to fetch the foreign keys needed to access the source tables
15 ****************************************************************************/
16 (b_user_id IN NUMBER
17 )IS SELECT employee_id
18 , person_party_id
19 , supplier_id
20 , user_name
21 FROM fnd_user
22 WHERE user_id = b_user_id;
23 CURSOR c_employee
24 /****************************************************************************
25 ** Cursor used to fetch the employee name in case the foreign key is to an
26 ** Employee
27 ****************************************************************************/
28 (b_employee_id IN NUMBER
29 )IS SELECT full_name
30 , employee_number
31 FROM per_all_people_f
32 WHERE person_id = b_employee_id;
33
34 CURSOR c_party
35 /****************************************************************************
36 ** Cursor used to fetch the party name in case the foreign key is to a
37 ** Customer or Supplier
38 ****************************************************************************/
39
40 (b_party_id IN NUMBER
41 )IS SELECT party_name
42 , party_number
43 FROM hz_parties
44 WHERE party_id = b_party_id;
45
46 l_employee_id NUMBER;
47 l_person_party_id NUMBER;
48 l_supplier_id NUMBER;
49 l_user_name VARCHAR2(360);
50 l_number VARCHAR2(30);
51 l_name VARCHAR2(240);
52 l_display_info VARCHAR2(500);
53 BEGIN
54 /*****************************************************************************
55 ** Get the foreigh keys to the user information
56 *****************************************************************************/
57 IF c_user%ISOPEN
58 THEN
59 CLOSE c_user;
60 END IF;
61 OPEN c_user(p_user_id);
62 FETCH c_user INTO l_employee_id,l_person_party_id,l_supplier_id,l_user_name;
63 IF c_user%ISOPEN
64 THEN
65 CLOSE c_user;
66 END IF;
67
68 IF (l_employee_id IS NOT NULL)
69 THEN
70 -- get the employee information
71 IF c_employee%ISOPEN
72 THEN
73 CLOSE c_employee;
74 END IF;
75
76 OPEN c_employee(l_employee_id);
77
78 FETCH c_employee INTO l_name,l_number;
79
80 IF c_employee%ISOPEN
81 THEN
82 CLOSE c_employee;
83 END IF;
84
85 ELSIF (l_person_party_id IS NOT NULL)
86
87 THEN
88
89 -- get the customer information
90
91 IF c_party%ISOPEN
92
93 THEN
94
95 CLOSE c_party;
96 END IF;
97
98 OPEN c_party(l_person_party_id);
99
100 FETCH c_party INTO l_name, l_number;
101
102 IF c_party%ISOPEN
103 THEN
104 CLOSE c_party;
105 END IF;
106
107 ELSIF (l_supplier_id IS NOT NULL)
108 THEN
109 -- get the supplier information
110 IF c_party%ISOPEN
111 THEN
112 CLOSE c_party;
113 END IF;
114
115 OPEN c_party(l_supplier_id);
116
117 FETCH c_party INTO l_name, l_number;
118
119 IF c_party%ISOPEN
120 THEN
121 CLOSE c_party;
122 END IF;
123 END IF;
124
125 IF l_name IS NULL
126 THEN
127 RETURN l_user_name;
128 ELSE
129 RETURN l_name||'('||l_user_name||','||l_number||')';
130 END IF;
131
132 EXCEPTION
133 WHEN OTHERS
134 THEN
135 IF c_employee%ISOPEN
136 THEN
137 CLOSE c_employee;
138 END IF;
139
140 IF c_party%ISOPEN
141 THEN
142 CLOSE c_party;
143 END IF;
144 RETURN 'Not Found';
145 END GetUserInfo;
146
147
148 END JTF_IH_GETUSER_PVT;
149
[Home] [Help]