[Home] [Help]
PACKAGE BODY: APPS.ICX_USER_PROFILE_PVT
Source
1 PACKAGE BODY ICX_User_Profile_PVT AS
2 -- $Header: ICXVUPFB.pls 120.1 2005/10/07 14:29:07 gjimenez noship $
3
4 PROCEDURE Create_Profile
5 ( p_api_version_number IN NUMBER,
6 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
7 p_simulate IN VARCHAR2 := FND_API.G_FALSE,
8 p_commit IN VARCHAR2 := FND_API.G_FALSE,
9 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
10 p_return_status OUT NOCOPY VARCHAR2,
11 p_msg_count OUT NOCOPY NUMBER,
12 p_msg_data OUT NOCOPY VARCHAR2,
13 p_user_id IN NUMBER,
14 p_days_needed_by IN NUMBER := NULL,
15 p_req_default_template IN VARCHAR2 := NULL,
16 p_req_override_loc_flag IN VARCHAR2 := NULL,
17 p_req_override_req_code IN VARCHAR2 := NULL,
18 p_created_by IN NUMBER,
19 p_creation_date IN DATE,
20 p_last_updated_by IN NUMBER,
21 p_last_update_date IN DATE,
22 p_last_update_login IN NUMBER
23 )
24 IS
25 l_api_name CONSTANT VARCHAR2(30) := 'Create_Profile';
26 l_api_version_number CONSTANT NUMBER := 1.0;
27 l_return_stat BOOLEAN;
28
29 BEGIN
30
31 -- Standard Start of API savepoint
32
33 SAVEPOINT Create_Profile_PVT;
34
35 -- Standard call to check for call compatibility.
36
37 if NOT FND_API.Compatible_API_Call
38 (
39 l_api_version_number,
40 p_api_version_number,
41 l_api_name,
42 G_PKG_NAME
43 )
44 then
45 raise FND_API.G_EXC_UNEXPECTED_ERROR;
46 end if;
47
48 -- Initialize message list if p_init_msg_list is set to TRUE.
49
50 if FND_API.to_Boolean( p_init_msg_list)
51 then
52 FND_MSG_PUB.initialize;
53 end if;
54
55 -- Initialize API return status to success
56
57 p_return_status := FND_API.G_RET_STS_SUCCESS;
58
59 l_return_stat := FND_PROFILE.SAVE(X_NAME =>'ICX_DAYS_NEEDED_BY',
60 X_VALUE => p_days_needed_by,
61 X_LEVEL_NAME =>'USER',
62 X_LEVEL_VALUE => p_user_id);
63
64 if l_return_stat = FALSE then
65 fnd_message.set_name('FND','SQL-NO INSERT');
66 fnd_message.set_token('TABLE','FND_USER');
67 fnd_msg_pub.Add;
68 raise FND_API.G_EXC_ERROR;
69 end if;
70
71 l_return_stat := FND_PROFILE.SAVE(X_NAME =>'ICX_REQ_DEFAULT_TEMPLATE',
72 X_VALUE => p_req_default_template,
73 X_LEVEL_NAME =>'USER',
74 X_LEVEL_VALUE => p_user_id);
75
76 if l_return_stat = FALSE then
77 fnd_message.set_name('FND','SQL-NO INSERT');
78 fnd_message.set_token('TABLE','FND_USER');
79 fnd_msg_pub.Add;
80 raise FND_API.G_EXC_ERROR;
81 end if;
82
83 l_return_stat := FND_PROFILE.SAVE(X_NAME =>'ICX_REQ_OVERRIDE_REQUESTOR_CODE',
84 X_VALUE => p_req_override_req_code,
85 X_LEVEL_NAME =>'USER',
86 X_LEVEL_VALUE => p_user_id);
87
88 if l_return_stat = FALSE then
89 fnd_message.set_name('FND','SQL-NO INSERT');
90 fnd_message.set_token('TABLE','FND_USER');
91 fnd_msg_pub.Add;
92 raise FND_API.G_EXC_ERROR;
93 end if;
94
95 l_return_stat := FND_PROFILE.SAVE(X_NAME =>'ICX_REQ_OVERRIDE_LOCATION_FLAG',
96 X_VALUE => p_req_override_loc_flag,
97 X_LEVEL_NAME =>'USER',
98 X_LEVEL_VALUE => p_user_id);
99
100 if l_return_stat = FALSE then
101 fnd_message.set_name('FND','SQL-NO INSERT');
102 fnd_message.set_token('TABLE','FND_USER');
103 fnd_msg_pub.Add;
104 raise FND_API.G_EXC_ERROR;
105 end if;
106
107 -- Standard check of p_simulate and p_commit parameters
108
109 if FND_API.To_Boolean( p_simulate)
110 then
111 ROLLBACK to Create_Profile_PVT;
112
113 elsif FND_API.To_Boolean( p_commit)
114 then
115 commit work;
116 end if;
117
118 -- Standard call to get message count and if count is 1, get message info.
119
120 FND_MSG_PUB.Count_And_Get
121 (
122 p_count => p_msg_count,
123 p_data => p_msg_data
124 );
125
126 EXCEPTION
127
128 WHEN FND_API.G_EXC_ERROR THEN
129
130 Rollback to Create_Profile_PVT;
131 p_return_status := FND_API.G_RET_STS_ERROR;
132
133 FND_MSG_PUB.Count_And_Get
134 (
135 p_count => p_msg_count,
136 p_data => p_msg_data
137 );
138
139 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
140
141 Rollback to Create_Profile_PVT;
142 p_return_status := FND_API.G_RET_STS_ERROR;
143
144 FND_MSG_PUB.Count_And_Get
145 (
146 p_count => p_msg_count,
147 p_data => p_msg_data
148 );
149
150 WHEN OTHERS THEN
151
152 Rollback to Create_Profile_PVT;
153 p_return_status := FND_API.G_RET_STS_ERROR;
154
155 if FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
156 then
157 FND_MSG_PUB.Add_Exc_Msg
158 (
159 G_FILE_NAME,
160 G_PKG_NAME,
161 l_api_name
162 );
163 end if;
164
165 FND_MSG_PUB.Count_And_Get
166 (
167 p_count => p_msg_count,
168 p_data => p_msg_data
169 );
170
171 end Create_Profile;
172
173
174 PROCEDURE Update_Profile
175 ( p_api_version_number IN NUMBER,
176 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
177 p_simulate IN VARCHAR2 := FND_API.G_FALSE,
178 p_commit IN VARCHAR2 := FND_API.G_FALSE,
179 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
180 p_return_status OUT NOCOPY VARCHAR2,
181 p_msg_count OUT NOCOPY NUMBER,
182 p_msg_data OUT NOCOPY VARCHAR2,
183 p_user_id IN NUMBER,
184 p_days_needed_by IN NUMBER := FND_API.G_MISS_NUM,
185 p_req_default_template IN VARCHAR2 := FND_API.G_MISS_CHAR,
186 p_req_override_loc_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
187 p_req_override_req_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
188 p_last_updated_by IN NUMBER,
189 p_last_update_date IN DATE,
190 p_last_update_login IN NUMBER
191 )
192 IS
193
194 l_api_name CONSTANT VARCHAR2(30) := 'Update_Profile';
195 l_api_version_number CONSTANT NUMBER := 1.0;
196 l_return_stat BOOLEAN;
197
198 BEGIN
199
200 -- Standard Start of API savepoint
201
202 SAVEPOINT Update_Profile_PVT;
203
204 -- Standard call to check for call compatibility.
205
206 if NOT FND_API.Compatible_API_Call
207 (
208 l_api_version_number,
209 p_api_version_number,
210 l_api_name,
211 G_PKG_NAME
212 )
213 then
214 raise FND_API.G_EXC_UNEXPECTED_ERROR;
215 end if;
216
217 -- Initialize message list if p_init_msg_list is set to TRUE.
218
219 if FND_API.to_Boolean( p_init_msg_list)
220 then
221 FND_MSG_PUB.initialize;
222 end if;
223
224 -- Initialize API return status to success
225
226 p_return_status := FND_API.G_RET_STS_SUCCESS;
227
228 -- ************
229 -- DAYS_NEEDED_BY
230 -- ************
231 if p_days_needed_by <> FND_API.G_MISS_NUM
232 then
233 l_return_stat := FND_PROFILE.SAVE(X_NAME =>'ICX_DAYS_NEEDED_BY',
234 X_VALUE => p_days_needed_by,
235 X_LEVEL_NAME =>'USER',
236 X_LEVEL_VALUE => p_user_id);
237
238 if l_return_stat = FALSE then
239 fnd_message.set_name('FND','SQL-NO INSERT');
240 fnd_message.set_token('TABLE','FND_USER');
241 fnd_msg_pub.Add;
242 raise FND_API.G_EXC_ERROR;
243 end if;
244 end if;
245 -- ************
246 -- REQ_DEFAULT_TEMPLATE
247 -- ************
248 if p_req_default_template <> FND_API.G_MISS_CHAR
249 then
250 l_return_stat := FND_PROFILE.SAVE(X_NAME =>'ICX_REQ_DEFAULT_TEMPLATE',
251 X_VALUE => p_req_default_template,
252 X_LEVEL_NAME =>'USER',
253 X_LEVEL_VALUE => p_user_id);
254
255 if l_return_stat = FALSE then
256 fnd_message.set_name('FND','SQL-NO INSERT');
257 fnd_message.set_token('TABLE','FND_USER');
258 fnd_msg_pub.Add;
259 raise FND_API.G_EXC_ERROR;
260 end if;
261 end if;
262 -- ************
263 -- REQ_OVERRIDE_LOC_FLAG
264 -- ************
265 if p_req_override_loc_flag <> FND_API.G_MISS_CHAR
266 then
267 l_return_stat := FND_PROFILE.SAVE(X_NAME =>'ICX_REQ_OVERRIDE_LOCATION_FLAG',
268 X_VALUE => p_req_override_loc_flag,
269 X_LEVEL_NAME =>'USER',
270 X_LEVEL_VALUE => p_user_id);
271
272 if l_return_stat = FALSE then
273 fnd_message.set_name('FND','SQL-NO INSERT');
274 fnd_message.set_token('TABLE','FND_USER');
275 fnd_msg_pub.Add;
276 raise FND_API.G_EXC_ERROR;
277 end if;
278 end if;
279 -- ************
280 -- REQ_OVERRIDE_REQ_CODE
281 -- ************
282 if p_req_override_req_code <> FND_API.G_MISS_CHAR
283 then
284 l_return_stat := FND_PROFILE.SAVE(X_NAME =>'ICX_REQ_OVERRIDE_REQUESTOR_CODE',
285 X_VALUE => p_req_override_req_code,
286 X_LEVEL_NAME =>'USER',
287 X_LEVEL_VALUE => p_user_id);
288
289 if l_return_stat = FALSE then
290 fnd_message.set_name('FND','SQL-NO INSERT');
291 fnd_message.set_token('TABLE','FND_USER');
292 fnd_msg_pub.Add;
293 raise FND_API.G_EXC_ERROR;
294 end if;
295 end if;
296
297 -- Standard check of p_simulate and p_commit parameters
298
299 if FND_API.To_Boolean( p_simulate)
300 then
301 ROLLBACK to Update_Profile_PVT;
302
303 elsif FND_API.To_Boolean( p_commit)
304 then
305 commit work;
306 end if;
307
308 -- Standard call to get message count and if count is 1, get message info.
309
310 FND_MSG_PUB.Count_And_Get
311 (
312 p_count => p_msg_count,
313 p_data => p_msg_data
314 );
315
316 EXCEPTION
317
318 WHEN FND_API.G_EXC_ERROR THEN
319
320 Rollback to Update_Profile_PVT;
321 p_return_status := FND_API.G_RET_STS_ERROR;
322
323 FND_MSG_PUB.Count_And_Get
324 (
325 p_count => p_msg_count,
326 p_data => p_msg_data
327 );
328
329 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
330
331 Rollback to Update_Profile_PVT;
332 p_return_status := FND_API.G_RET_STS_ERROR;
333
334 FND_MSG_PUB.Count_And_Get
335 (
336 p_count => p_msg_count,
337 p_data => p_msg_data
338 );
339
340 WHEN OTHERS THEN
341
342 Rollback to Update_Profile_PVT;
343 p_return_status := FND_API.G_RET_STS_ERROR;
344
345 if FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
346 then
347 FND_MSG_PUB.Add_Exc_Msg
348 (
349 G_FILE_NAME,
350 G_PKG_NAME,
351 l_api_name
352 );
353 end if;
354
355 FND_MSG_PUB.Count_And_Get
356 (
357 p_count => p_msg_count,
358 p_data => p_msg_data
359 );
360
361 end Update_Profile;
362
363
364
365 PROCEDURE Delete_Profile
366 ( p_api_version_number IN NUMBER,
367 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
368 p_simulate IN VARCHAR2 := FND_API.G_FALSE,
369 p_commit IN VARCHAR2 := FND_API.G_FALSE,
370 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
371 p_return_status OUT nocopy VARCHAR2,
372 p_msg_count OUT nocopy NUMBER,
373 p_msg_data OUT nocopy VARCHAR2,
374 p_user_id IN NUMBER
375 )
376 IS
377 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Profile';
378 l_api_version_number CONSTANT NUMBER := 1.0;
379
380 l_error_tab VARCHAR2(40);
381 l_error_col VARCHAR2(40);
382 l_error_val VARCHAR2(40);
383
384 e_cannot_delete EXCEPTION;
385 l_return_stat BOOLEAN;
386
387 BEGIN
388
389 -- Standard Start of API savepoint
390
391 SAVEPOINT Delete_Profile_PVT;
392
393 -- Standard call to check for call compatibility.
394
395 if NOT FND_API.Compatible_API_Call
396 (
397 l_api_version_number,
398 p_api_version_number,
399 l_api_name,
400 G_PKG_NAME
401 )
402 then
403 raise FND_API.G_EXC_UNEXPECTED_ERROR;
404 end if;
405
406 -- Initialize message list if p_init_msg_list is set to TRUE.
407
408 if FND_API.to_Boolean( p_init_msg_list)
409 then
410 FND_MSG_PUB.initialize;
411 end if;
412
413 -- Initialize API return status to success
414
415 p_return_status := FND_API.G_RET_STS_SUCCESS;
416
417
418 l_return_stat := FND_PROFILE.SAVE(X_NAME =>'ICX_DAYS_NEEDED_BY',
419 X_VALUE => '',
420 X_LEVEL_NAME =>'USER',
421 X_LEVEL_VALUE => p_user_id);
422
423 if l_return_stat = FALSE then
424 fnd_message.set_name('FND','SQL-NO INSERT');
425 fnd_message.set_token('TABLE','FND_USER');
426 fnd_msg_pub.Add;
427 raise FND_API.G_EXC_ERROR;
428 end if;
429
430 l_return_stat := FND_PROFILE.SAVE(X_NAME =>'ICX_REQ_DEFAULT_TEMPLATE',
431 X_VALUE => '',
432 X_LEVEL_NAME =>'USER',
433 X_LEVEL_VALUE => p_user_id);
434
435 if l_return_stat = FALSE then
436 fnd_message.set_name('FND','SQL-NO INSERT');
437 fnd_message.set_token('TABLE','FND_USER');
438 fnd_msg_pub.Add;
439 raise FND_API.G_EXC_ERROR;
440 end if;
441
442 l_return_stat := FND_PROFILE.SAVE(X_NAME =>'ICX_REQ_OVERRIDE_REQUESTOR_CODE',
443 X_VALUE => '',
444 X_LEVEL_NAME =>'USER',
445 X_LEVEL_VALUE => p_user_id);
446
447 if l_return_stat = FALSE then
448 fnd_message.set_name('FND','SQL-NO INSERT');
449 fnd_message.set_token('TABLE','FND_USER');
450 fnd_msg_pub.Add;
451 raise FND_API.G_EXC_ERROR;
452 end if;
453
454 l_return_stat := FND_PROFILE.SAVE(X_NAME =>'ICX_REQ_OVERRIDE_LOCATION_FLAG',
455 X_VALUE => '',
456 X_LEVEL_NAME =>'USER',
457 X_LEVEL_VALUE => p_user_id);
458
459 if l_return_stat = FALSE then
460 fnd_message.set_name('FND','SQL-NO INSERT');
461 fnd_message.set_token('TABLE','FND_USER');
462 fnd_msg_pub.Add;
463 raise FND_API.G_EXC_ERROR;
464 end if;
465
466 -- Standard check of p_simulate and p_commit parameters
467
468 if FND_API.To_Boolean( p_simulate)
469 then
470 ROLLBACK to Delete_Profile_PVT;
471
472 elsif FND_API.To_Boolean( p_commit)
473 then
474 commit work;
475 end if;
476
477 -- Standard call to get message count and if count is 1, get message info.
478
479 FND_MSG_PUB.Count_And_Get
480 (
481 p_count => p_msg_count,
482 p_data => p_msg_data
483 );
484
485 EXCEPTION
486
487 WHEN NO_DATA_FOUND or e_cannot_delete THEN
488
489 Rollback to Delete_Profile_PVT;
490 p_return_status := FND_API.G_RET_STS_ERROR;
491
492 fnd_message.set_name('FND','SQL-NO DELETE');
493 fnd_message.set_token('TABLE', 'ICX_USER_PROFILES');
494
495 fnd_msg_pub.Add;
496
497 FND_MSG_PUB.Count_And_Get
498 (
499 p_count => p_msg_count,
500 p_data => p_msg_data
501 );
502
503 WHEN FND_API.G_EXC_ERROR THEN
504
505 Rollback to Delete_Profile_PVT;
506 p_return_status := FND_API.G_RET_STS_ERROR;
507
508 FND_MSG_PUB.Count_And_Get
509 (
510 p_count => p_msg_count,
511 p_data => p_msg_data
512 );
513
514 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
515
516 Rollback to Delete_Profile_PVT;
517 p_return_status := FND_API.G_RET_STS_ERROR;
518
519 FND_MSG_PUB.Count_And_Get
520 (
521 p_count => p_msg_count,
522 p_data => p_msg_data
523 );
524
525 WHEN OTHERS THEN
526
527 Rollback to Delete_Profile_PVT;
528 p_return_status := FND_API.G_RET_STS_ERROR;
529
530 if FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
531 then
532 FND_MSG_PUB.Add_Exc_Msg
533 (
537 );
534 G_FILE_NAME,
535 G_PKG_NAME,
536 l_api_name
538 end if;
539
540 FND_MSG_PUB.Count_And_Get
541 (
542 p_count => p_msg_count,
543 p_data => p_msg_data
544 );
545
546 end Delete_Profile;
547
548
549 END ICX_User_Profile_PVT;