1 PACKAGE BODY IBE_USER_PVT AS
2 /* $Header: IBEVUSRB.pls 120.5 2005/10/17 02:40:44 abairy ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'IBE_USER_PVT';
5
6 /*+====================================================================
7 | PROCEDURE NAME
8 | Create_User
9 |
10 | DESCRIPTION
11 | This API is called in java layer by
12 | oracle.apps.ibe.um.UserManager.createUser
13 |
14 | USAGE
15 | - Creates FND User
16 |
17 | REFERENCED APIS
18 | This API calls the following APIs
19 | - FND_USER_PKG.CreatePendingUser
20 +======================================================================*/
21 Procedure Create_User(
22 p_user_name IN VARCHAR2,
23 p_password IN VARCHAR2,
24 p_start_date IN DATE,
25 p_end_date IN DATE,
26 p_password_date IN DATE,
27 p_email_address IN VARCHAR2,
28 p_customer_id IN NUMBER,
29 x_user_id OUT NOCOPY NUMBER ) IS
30
31 Cursor c_get_user_st_end_date(c_user_name VARCHAR2) IS
32 select to_char(u.start_date,'yyyy-mm-dd'), to_char(u.end_date,'yyyy-mm-dd')
33 from fnd_user u where user_name=upper(c_user_name);
34
35 l_start_date VARCHAR2(30);
36 l_end_date VARCHAR2(30);
37
38 BEGIN
39 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
40 IBE_UTIL.debug('enter ibe_user_pvt.create_user');
41 END IF;
42
43 --Ceates an FND_USER and Link the fnd_user table
44 --and hz_parties by setting the customer_id column in fnd_user table
45
46
47 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
48 IBE_UTIL.debug('Call FND_USER_PKG.CreatePendingUser API to create a user');
49 END IF;
50
51 IF p_password is not null THEN
52
53 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
54 IBE_UTIL.debug('Before Call to FND_USER_PKG.CreatePendingUser API: p_password is not null');
55 END IF;
56
57 x_user_id := FND_USER_PKG.createPendingUser (
58 x_user_name =>p_user_name,
59 x_owner => 'CUST',
60 x_unencrypted_password => p_password,
61 x_password_date => p_password_date,
62 x_email_address => p_email_address
63 );
64
65
66 ELSE
67 -- OID record exists; create FND record and relink
68 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
69 IBE_UTIL.debug('Before Call to FND_USER_PKG.CreatePendingUser API: p_password is null');
70 END IF;
71
72 x_user_id := FND_USER_PKG.createPendingUser (
73 x_user_name =>p_user_name,
74 x_owner => 'CUST',
75 x_email_address => p_email_address
76 );
77
78 END IF;
79
80 open c_get_user_st_end_date(p_user_name);
81 fetch c_get_user_st_end_date into l_start_date, l_end_date;
82 close c_get_user_st_end_date;
83 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
84 IBE_UTIL.debug('start_date = '||l_start_date||' : end_date = '||l_end_date);
85 END IF;
86
87 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
88 IBE_UTIL.debug('After Call to FND_USER_PKG.CreatePendingUser API userid :'||x_user_id);
89 IBE_UTIL.debug('Before Call to FND_USER_PKG.updateUser API for updating customer_id');
90 END IF;
91
92
93 FND_USER_PKG.updateUser (
94 x_user_name => p_user_name,
95 x_owner => 'CUST',
96 x_customer_id => p_customer_id
97 );
98
99 open c_get_user_st_end_date(p_user_name);
100 fetch c_get_user_st_end_date into l_start_date, l_end_date;
101 close c_get_user_st_end_date;
102 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
103 IBE_UTIL.debug('start_date = '||l_start_date||' : end_date = '||l_end_date);
104 END IF;
105
106 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
107 IBE_UTIL.debug('After Call to FND_USER_PKG.updateUser API for updating customer_id');
108 END IF;
109
110
111 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
112 IBE_UTIL.debug('exit ibe_user_pvt.create_user ');
113 END IF;
114
115 End create_user;
116
117
118 /*+====================================================================
119 | PROCEDURE NAME
120 | Update_User
121 |
122 | DESCRIPTION
123 | This API is called by User Management when Contact Detail is updated
124 |
125 | USAGE
126 | - Updates FND User
127 |
128 | REFERENCED APIS
129 | This API calls the following APIs
130 | - FND_USER_PKG.UpdateUser
131 +======================================================================*/
132 Procedure Update_User(
133 p_user_name IN VARCHAR2,
134 p_password IN VARCHAR2,
135 p_start_date IN DATE,
136 p_end_date IN DATE,
137 p_old_password IN VARCHAR2,
138 p_party_id IN NUMBER) IS
139 l_end_date date;
140 BEGIN
141
142 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
143 IBE_UTIL.debug('enter ibe_user_pvt.update_user');
144 END IF;
145
146 --Ceates an FND_USER and Link the fnd_user table
147 --and hz_parties by setting the customer_id column in fnd_user table
148 if (p_end_date is null) then
149 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
150 IBE_UTIL.debug('p_end_date is null');
151 END IF;
152 l_end_date := null_date;
153 else
154 l_end_date := p_end_date;
155 end if;
156
157 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
158 IBE_UTIL.debug('Call FND_USER_PKG.UpdateUser API to updated a user');
159 END IF;
160
161 FND_USER_PKG.UpdateUser (
162 x_user_name => p_user_name,
163 x_owner => 'CUST',
164 x_unencrypted_password => p_password,
165 x_start_date => p_start_date,
166 x_end_date => l_end_date,
167 x_old_password => p_old_password,
168 x_customer_id => p_party_id
169 );
170
171 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
172 IBE_UTIL.debug('exit enter ibe_user_pvt.update_user ');
173 END IF;
174
175 End update_user;
176
177 /*+====================================================================
178 | PROCEDURE NAME
179 | Create_User
180 |
181 | DESCRIPTION
182 | This API is called by while revoking sites from user in User Management
183 |
184 | USAGE
185 | - This API calls FND_USER_RESP_GROUPS_API and end dates the responsibility
186 |
187 |
188 | REFERENCED APIS
189 | This API calls the following APIs
190 | - FND_USER_RESP_GROUPS_API.update_assignmets
191 +======================================================================*/
192 Procedure Update_Assignment(
193 p_user_id IN NUMBER,
194 p_responsibility_id IN NUMBER,
195 p_resp_application_id IN NUMBER,
196 p_security_group_id IN NUMBER ,
197 p_start_date IN DATE,
198 p_end_date IN DATE,
199 p_description IN VARCHAR2) IS
200
201 l_start_date Date;
202
203 BEGIN
204 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
205 IBE_UTIL.debug('enter ibe_user_pvt.Update_Assignment');
206 END IF;
207
208 --Ceates an FND_USER and Link the fnd_user table
209 --and hz_parties by setting the customer_id column in fnd_user table
210
211 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
212 IBE_UTIL.debug('Call FND_USER_RESP_API.Update_Assignment API to revoke responsibility');
213 END IF;
214
215 if (p_start_date is null) then
216 l_start_date := sysdate;
217 end if;
218
219 FND_USER_RESP_GROUPS_API.Update_Assignment (
220 user_id =>p_user_id,
221 responsibility_id => p_responsibility_id,
222 responsibility_application_id => p_resp_application_id,
223 security_group_id => p_security_group_id,
224 start_date => l_start_date,
225 end_date => p_end_date,
226 description => p_description
227 );
228
229
230 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
231 IBE_UTIL.debug('After FND_USER_RESP_API.Update_Assignment API to revoke responsibility');
232 IBE_UTIL.debug('exit enter ibe_user_pvt.Update_Assignment ');
233 END IF;
234
235 End Update_Assignment;
236
237 /*+====================================================================
238 | FUNCTION NAME
239 | TestUserName
240 |
241 | DESCRIPTION
242 | This api test whether a username exists in FND and/or in OID.
243 |
244 | USAGE
245 | - This API is called for validating the username ion Registration
246 |
247 | REFERENCED APIS
248 | This API calls the following APIs
249 | - FND_USER_PKG.TestUserName
250 +======================================================================*/
251
252
253 Function TestUserName(p_user_name in varchar2) return pls_integer
254 is
255 retval pls_integer:=0;
256 -- cur BINARY_INTEGER := DBMS_SQL.OPEN_CURSOR;
257 -- fdbk BINARY_INTEGER;
258 l_block varchar2(2000);
259 Begin
260
261 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
262 IBE_UTIL.debug('enter ibe_user_pvt.TestUserName API');
263 IBE_UTIL.debug('calling fnd_user_pkg.TestUserName API');
264 END IF;
265
266
267 begin
268 l_block :=
269 'begin :result := fnd_user_pkg.testUsername(:1); end;';
270 execute immediate l_block using out retval, in p_user_name;
271 exception
272 when others then
273 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
274 IBE_UTIL.debug('fnd_user_pkg.TestUserName API returns '|| retval);
275 END IF;
276 raise;
277 end;
278
279
280 /*
281 IBE_UTIL.debug('Parsing IBE_USER_PVT.testUsername ' );
282
283 DBMS_SQL.PARSE (cur, 'DECLARE user_name_test_status INTEGER; ' ||
284 ' BEGIN ' ||
285 ' user_name_test_status := fnd_user_pkg.testUsername(:username); ' ||
286 ' :status := user_name_test_status; ' ||
287 ' END;', DBMS_SQL.NATIVE);
288
289
290 IBE_UTIL.debug('Binding username ' );
291 DBMS_SQL.BIND_VARIABLE (cur, 'username', p_user_name);
292
293 IBE_UTIL.debug('Binding status ' );
294 DBMS_SQL.BIND_VARIABLE (cur, 'status', retval);
295
296 IBE_UTIL.debug('Executing ' );
297 fdbk := DBMS_SQL.EXECUTE (cur);
298
299 IBE_UTIL.debug('Retrieving status' );
300 DBMS_SQL.VARIABLE_VALUE (cur, 'status', retval);
301
302 DBMS_SQL.CLOSE_CURSOR (cur);
303
304 IBE_UTIL.debug('After calling TestUserName : retval =' || retval);
305
306 -- retval := FND_USER_PKG.TestUserName(p_user_name);
307 */
308
309 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
310 IBE_UTIL.debug('fnd_user_pkg.TestUserName API returns '|| retval);
311 IBE_UTIL.debug('exit ibe_user_pvt.TestUserName API');
312 END IF;
313
314
315 return (retval);
316
317 End TestUserName;
318
319 END IBE_USER_PVT;