1 PACKAGE BODY WSH_ITM_USERS_PKG AS
2 /* $Header: WSHITUTB.pls 115.6 2002/12/12 12:07:21 bradha ship $ */
3 -- Global constant holding the package name
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'WSH_ITM_USERS_PKG';
5
6 /*----------------------------------------------------------*/
7 /* Insert_Row Procedure */
8 /*----------------------------------------------------------*/
9 /* --
10 -- Purpose
11 -- Insert a row into WSH_ITM_USERS_SETUP entity
12 --
13 -- Input Parameters
14 -- p_api_version
15 -- API version number (current version is 1.0)
16 -- p_init_msg_list (optional, default FND_API.G_FALSE)
17 -- Valid values: FND_API.G_FALSE or FND_API.G_TRUE.
18 -- if set to FND_API.G_TRUE
19 -- initialize error message list
20 -- if set to FND_API.G_FALSE - not initialize error
21 -- message list
22 -- p_commit (optional, default FND_API.G_FALSE)
23 -- whether or not to commit the changes to database
24 --
25 -- Input parameters for clear cross users informations
26 -- p_VENDOR_ID -- Vendor Id
27 -- p_APPLICATION_ID --Application ID added.
28 -- p_MASTER_ORGANIZATION_ID - Master Org
29 -- p_ORGANIZATION_ID - Org
30 -- p_APPLICATION_USER_ID - Application User
31 -- p_USER_ID - ITM User
32 -- p_ENCRYPTED_USER_PASSWORD - Password
33 --
34 -- Output Parameters
35 -- x_return_status
36 -- if the process succeeds, the value is
37 -- fnd_api.g_ret_sts_success;
38 -- if there is an expected error, the value is
39 -- fnd_api.g_ret_sts_error;
40 -- if there is an unexpected error, the value is
41 -- fnd_api.g_ret_sts_unexp_error;
42 -- x_msg_count
43 -- if there is one or more errors, the number of error messages
44 -- in the buffer
45 -- x_msg_data
46 -- if there is one and only one error, the error message
47 -- (See fnd_api package for more details about the above output parameters)
48 -- p_ITM_USER_SEQUENCE_ID - ITM sequence Id ( PK)
49 --*/
50
51 PROCEDURE Insert_Row
52 (
53 p_api_version IN NUMBER ,
54 p_init_msg_list IN VARCHAR2 := fnd_api.g_false ,
55 p_commit IN VARCHAR2 := fnd_api.g_false ,
56 x_return_status OUT NOCOPY VARCHAR2 ,
57 x_msg_count OUT NOCOPY NUMBER ,
58 x_msg_data OUT NOCOPY VARCHAR2 ,
59 p_VENDOR_ID IN NUMBER,
60 p_APPLICATION_ID IN NUMBER,
61 p_MASTER_ORGANIZATION_ID IN NUMBER,
62 p_ORGANIZATION_ID IN NUMBER ,
63 p_APPLICATION_USER_ID IN NUMBER ,
64 p_USER_ID IN VARCHAR2,
65 p_ENCRYPTED_USER_PASSWORD IN VARCHAR2,
66 p_ITM_USER_SEQUENCE_ID OUT NOCOPY NUMBER
67 )
68 IS
69 l_ITM_USER_SEQUENCE_ID NUMBER ;
70 l_api_name CONSTANT VARCHAR2(30) := 'Insert_Row';
71 l_api_version number := 1.0;
72 begin
73 --dbms_output.put_line('begin api');
74 -- Standard Start of API savepoint
75 SAVEPOINT WSH_ITM_USERS_PKG;
76 -- Standard call to check for call compatibility.
77 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
78 p_api_version ,
79 l_api_name ,
80 G_PKG_NAME )
81 THEN
82 FND_MESSAGE.SET_NAME('WSH', 'WSH_INCOMPATIBLE_API_CALL');
83 FND_MSG_PUB.ADD;
84 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
85 END IF;
86 -- Initialize message list if p_init_msg_list is set to TRUE.
87 IF FND_API.to_Boolean( p_init_msg_list ) THEN
88 FND_MSG_PUB.initialize;
89 END IF;
90 -- Initialize API return status to success
91 x_return_status := FND_API.G_RET_STS_SUCCESS;
92 --dbms_output.put_line('begin api-2');
93
94 select wsh_itm_user_setups_s.nextval into l_ITM_USER_SEQUENCE_ID from dual;
95 /* Validate input parameters if any */
96
97 -- Insert a row into wsh_itm_users entity with all detail information
98 insert into wsh_itm_user_setups
99 ( ITM_USER_SEQUENCE_ID
100 ,VENDOR_ID
101 ,APPLICATION_ID
102 ,MASTER_ORGANIZATION_ID
103 ,ORGANIZATION_ID
104 ,APPLICATION_USER_ID
105 ,USER_ID
106 ,ENCRYPTED_USER_PASSWORD
107 ,LAST_UPDATE_DATE
108 ,LAST_UPDATED_BY
109 ,CREATION_DATE
110 ,CREATED_BY
111 ,LAST_UPDATE_LOGIN
112 )
113 values (
114 l_ITM_USER_SEQUENCE_ID
115 ,p_VENDOR_ID
116 ,p_APPLICATION_ID
117 ,p_MASTER_ORGANIZATION_ID
118 ,p_ORGANIZATION_ID
119 ,p_APPLICATION_USER_ID
120 ,p_USER_ID
121 ,p_ENCRYPTED_USER_PASSWORD
122 ,sysdate
123 ,FND_GLOBAL.user_id
124 ,sysdate
125 ,FND_GLOBAL.user_id
126 ,FND_GLOBAL.login_id
127 ) ;
128 IF SQL%NOTFOUND THEN
129 FND_MESSAGE.SET_NAME('WSH', 'WSH_INSERT_FAILED');
130 FND_MSG_PUB.ADD;
131 x_return_status := FND_API.G_RET_STS_ERROR;
132 RAISE FND_API.G_EXC_ERROR ;
133 END IF;
134 --dbms_output.put_line('Seq Id got it '||l_ITM_USER_SEQUENCE_ID||'success');
135 x_return_status := fnd_api.g_ret_sts_success;
136 p_ITM_USER_SEQUENCE_ID := l_ITM_USER_SEQUENCE_ID;
137 -- End of API body
138 -- Standard check of p_commit.
139 IF FND_API.To_Boolean( p_commit ) THEN
140 COMMIT WORK;
141 END IF;
142 -- Standard call to get message count and if count is 1,
143 -- get message info.
144 FND_MSG_PUB.Count_And_Get
145 ( p_count => x_msg_count,
146 p_data => x_msg_data
147 );
148
149 EXCEPTION
150 WHEN FND_API.G_EXC_ERROR THEN
151 ROLLBACK TO WSH_ITM_USERS_PKG;
152 x_return_status := FND_API.G_RET_STS_ERROR;
153 FND_MSG_PUB.Count_And_Get
154 ( p_count => x_msg_count,
155 p_data => x_msg_data
156 );
157 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
158 ROLLBACK TO WSH_ITM_USERS_PKG;
159 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
160 FND_MSG_PUB.Count_And_Get
161 ( p_count => x_msg_count,
162 p_data => x_msg_data
163 );
164 WHEN OTHERS THEN
165 ROLLBACK TO WSH_ITM_USERS_PKG;
166 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
167 IF FND_MSG_PUB.Check_Msg_Level
168 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
169 THEN
170 FND_MSG_PUB.Add_Exc_Msg
171 ( G_PKG_NAME ,
172 l_api_name
173 );
174 END IF;
175 FND_MSG_PUB.Count_And_Get
176 ( p_count => x_msg_count,
177 p_data => x_msg_data
178 );
179 End Insert_Row;
180
181
182 /*----------------------------------------------------------*/
183 /* Update_Row Procedure */
184 /*----------------------------------------------------------*/
185 /* --
186 -- Purpose
187 -- Update a row into WS_ITM_USERS entity for the given cc seq id
188 --
189 -- Input Parameters
190 -- p_api_version
191 -- API version number (current version is 1.0)
192 -- p_init_msg_list (optional, default FND_API.G_FALSE)
193 -- Valid values: FND_API.G_FALSE or FND_API.G_TRUE.
194 -- if set to FND_API.G_TRUE
195 -- initialize error message list
196 -- if set to FND_API.G_FALSE - not initialize error
197 -- message list
198 -- p_commit (optional, default FND_API.G_FALSE)
199 -- whether or not to commit the changes to database
200 --
201 -- Input parameters for clear cross users informations
202 -- p_VENDOR_ID
203 -- p_APPLICATION_ID --Application added
204 -- p_MASTER_ORGANIZATION_ID -- Master Org
205 -- p_ORGANIZATION_ID --Org
206 -- p_APPLICATION_USER_ID -- Application User
207 -- p_USER_ID -- Clear Cross User
208 -- p_ENCRYPTED_USER_PASSWORD -- Password
209 -- p_ITM_USER_SEQUENCE_ID -- CC Seq Id
210 --
211 --
212 -- Output Parameters
213 -- x_return_status
214 -- if the process succeeds, the value is
215 -- fnd_api.g_ret_sts_success;
216 -- if there is an expected error, the value is
217 -- fnd_api.g_ret_sts_error;
218 -- if there is an unexpected error, the value is
219 -- fnd_api.g_ret_sts_unexp_error;
220 -- x_msg_count
221 -- if there is one or more errors, the number of error messages
222 -- in the buffer
223 -- x_msg_data
224 -- if there is one and only one error, the error message
225 -- (See fnd_api package for more details about the above output parameters)
226
227 --*/
228 PROCEDURE Update_Row
229 (
230 p_api_version IN NUMBER ,
231 p_init_msg_list IN VARCHAR2 := fnd_api.g_false ,
232 p_commit IN VARCHAR2 := fnd_api.g_false ,
233 x_return_status OUT NOCOPY VARCHAR2 ,
234 x_msg_count OUT NOCOPY NUMBER ,
235 x_msg_data OUT NOCOPY VARCHAR2 ,
236 p_VENDOR_ID IN NUMBER,
237 p_APPLICATION_ID IN NUMBER,
238 p_MASTER_ORGANIZATION_ID IN NUMBER,
239 p_ORGANIZATION_ID IN NUMBER,
240 p_APPLICATION_USER_ID IN NUMBER,
241 p_USER_ID IN VARCHAR2,
242 p_ENCRYPTED_USER_PASSWORD IN VARCHAR2,
243 p_ITM_USER_SEQUENCE_ID IN NUMBER
244 )
245 IS
246 l_api_name CONSTANT VARCHAR2(30) := 'Update_Row' ;
247 l_api_version number := 1.0;
248
249 BEGIN
250 --dbms_output.put_line('begin');
251 -- Standard Start of API savepoint
252 SAVEPOINT WSH_ITM_USERS_PKG;
253 -- Standard call to check for call compatibility.
254 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
255 p_api_version ,
256 l_api_name ,
257 G_PKG_NAME )
258 THEN
259 FND_MESSAGE.SET_NAME('WSH', 'WSH_INCOMPATIBLE_API_CALL');
260 FND_MSG_PUB.ADD;
261 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
262 END IF;
263
264 -- Initialize message list if p_init_msg_list is set to TRUE.
265 IF FND_API.to_Boolean( p_init_msg_list ) THEN
266 FND_MSG_PUB.initialize;
267 END IF;
268
269 -- Initialize API return status to success
270 x_return_status := FND_API.G_RET_STS_SUCCESS;
271
272
273 -- Update a row into wsh_itm_users entity with all detail information
274 -- for the given seq id
275
276 update wsh_itm_user_setups
277 SET
278 VENDOR_ID = p_VENDOR_ID
279 ,APPLICATION_ID = p_APPLICATION_ID
280 ,MASTER_ORGANIZATION_ID = p_MASTER_ORGANIZATION_ID
281 ,ORGANIZATION_ID = p_ORGANIZATION_ID
282 ,APPLICATION_USER_ID = p_APPLICATION_USER_ID
283 ,USER_ID = p_USER_ID
284 ,ENCRYPTED_USER_PASSWORD = p_ENCRYPTED_USER_PASSWORD
285 ,LAST_UPDATE_DATE = sysdate
286 ,LAST_UPDATED_BY = FND_GLOBAL.user_id
287 ,LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
288 where itm_user_sequence_id = p_itm_user_sequence_id;
289
290
291 IF SQL%NOTFOUND THEN
292 FND_MESSAGE.SET_NAME('WSH', 'WSH_UPDATE_FAILED');
293 FND_MSG_PUB.ADD;
294 x_return_status := FND_API.G_RET_STS_ERROR;
295 RAISE FND_API.G_EXC_ERROR ;
296 END IF;
297
298 x_return_status := fnd_api.g_ret_sts_success;
299
300 -- End of API body
301 -- Standard check of p_commit.
302
303 IF FND_API.To_Boolean( p_commit ) THEN
304 COMMIT WORK;
305 END IF;
306
307 -- Standard call to get message count and if count is 1,
308 -- get message info.
309
310 FND_MSG_PUB.Count_And_Get
311 ( p_count => x_msg_count,
312 p_data => x_msg_data
313 );
314
315 EXCEPTION
316 WHEN FND_API.G_EXC_ERROR THEN
317 ROLLBACK TO WSH_ITM_USERS_PKG;
318 x_return_status := FND_API.G_RET_STS_ERROR;
319 FND_MSG_PUB.Count_And_Get
320 ( p_count => x_msg_count,
321 p_data => x_msg_data
322 );
323
324 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
325 ROLLBACK TO WSH_ITM_USERS_PKG;
326 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
327 FND_MSG_PUB.Count_And_Get
328 ( p_count => x_msg_count,
329 p_data => x_msg_data
330 );
331
332 WHEN OTHERS THEN
333 ROLLBACK TO WSH_ITM_USERS_PKG;
334 x_return_status := 'W';
335 IF FND_MSG_PUB.Check_Msg_Level
336 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
337 THEN
338 FND_MSG_PUB.Add_Exc_Msg
339 ( G_PKG_NAME ,
340 l_api_name
341 );
342 END IF;
343
344 FND_MSG_PUB.Count_And_Get
345 ( p_count => x_msg_count,
346 p_data => x_msg_data
347 );
348
349 End Update_Row;
350
351 /*----------------------------------------------------------*/
352 /* Delete_Row Procedure */
353 /*----------------------------------------------------------*/
354 /* --
355 -- Purpose
356 -- Delete a row from WSH_ITM_USERS entity for the given cc seq id
357 --
358 -- Input Parameters
359 -- p_api_version
360 -- API version number (current version is 1.0)
361 -- p_init_msg_list (optional, default FND_API.G_FALSE)
362 -- Valid values: FND_API.G_FALSE or FND_API.G_TRUE.
363 -- if set to FND_API.G_TRUE
364 -- initialize error message list
365 -- if set to FND_API.G_FALSE - not initialize error
366 -- message list
367 -- p_commit (optional, default FND_API.G_FALSE)
368 -- whether or not to commit the changes to database
369 --
370 -- Input parameters for clear cross users informations
371 -- p_ITM_USER_SEQUENCE_ID -- ITM Seq Id
372 --
373 --
374 -- Output Parameters
375 -- x_return_status
376 -- if the process succeeds, the value is
377 -- fnd_api.g_ret_sts_success;
378 -- if there is an expected error, the value is
379 -- fnd_api.g_ret_sts_error;
380 -- if there is an unexpected error, the value is
381 -- fnd_api.g_ret_sts_unexp_error;
382 -- x_msg_count
383 -- if there is one or more errors, the number of error messages
384 -- in the buffer
385 -- x_msg_data
386 -- if there is one and only one error, the error message
387 -- (See fnd_api package for more details about the above output parameters)
388
389 --*/
390 PROCEDURE Delete_Row
391 (
395 x_return_status OUT NOCOPY VARCHAR2 ,
392 p_api_version IN NUMBER ,
393 p_init_msg_list IN VARCHAR2 := fnd_api.g_false ,
394 p_commit IN VARCHAR2 := fnd_api.g_false ,
396 x_msg_count OUT NOCOPY NUMBER ,
397 x_msg_data OUT NOCOPY VARCHAR2 ,
398 p_ITM_USER_SEQUENCE_ID IN NUMBER
399 )
400 IS
401 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Row' ;
402 l_api_version number := 1.0;
403 BEGIN
404 --dbms_output.put_line('begin');
405 -- Standard Start of API savepoint
406 SAVEPOINT WSH_ITM_USERS_PKG;
407 -- Standard call to check for call compatibility.
408 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
409 p_api_version ,
410 l_api_name ,
411 G_PKG_NAME )
412 THEN
413 FND_MESSAGE.SET_NAME('WSH', 'WSH_INCOMPATIBLE_API_CALL');
414 FND_MSG_PUB.ADD;
415 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
416 END IF;
417 -- Initialize message list if p_init_msg_list is set to TRUE.
418 IF FND_API.to_Boolean( p_init_msg_list ) THEN
419 FND_MSG_PUB.initialize;
420 END IF;
421 -- Initialize API return status to success
422 x_return_status := FND_API.G_RET_STS_SUCCESS;
423
424 -- Delete a row from wsh_itm_users entity
425 -- for the given cc seq id
426
427 DELETE from wsh_itm_user_setups
428 WHERE itm_user_sequence_id = p_itm_user_sequence_id ;
429 IF SQL%NOTFOUND THEN
430 FND_MESSAGE.SET_NAME('WSH', 'WSH_DELETE_FAILED');
431 FND_MSG_PUB.ADD;
432 x_return_status := FND_API.G_RET_STS_ERROR;
433 RAISE FND_API.G_EXC_ERROR ;
434 END IF;
435 --dbms_output.put_line('begin-5');
436 x_return_status := fnd_api.g_ret_sts_success;
437
438 -- End of API body
439 -- Standard check of p_commit.
440 IF FND_API.To_Boolean( p_commit ) THEN
441 COMMIT WORK;
442 END IF;
443 -- Standard call to get message count and if count is 1,
444 -- get message info.
445 FND_MSG_PUB.Count_And_Get
446 ( p_count => x_msg_count,
447 p_data => x_msg_data
448 );
449
450 EXCEPTION
451 WHEN FND_API.G_EXC_ERROR THEN
452 --dbms_output.put_line(sqlerrm);
453 ROLLBACK TO WSH_ITM_USERS_PKG;
454 x_return_status := FND_API.G_RET_STS_ERROR;
455 FND_MSG_PUB.Count_And_Get
456 ( p_count => x_msg_count,
457 p_data => x_msg_data
458 );
459 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
460 --dbms_output.put_line(sqlerrm);
461 ROLLBACK TO WSH_ITM_USERS_PKG;
462 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
463 FND_MSG_PUB.Count_And_Get
464 ( p_count => x_msg_count,
465 p_data => x_msg_data
466 );
467 WHEN OTHERS THEN
468 --dbms_output.put_line(sqlerrm);
469 ROLLBACK TO WSH_ITM_USERS_PKG;
470 x_return_status := 'W';
471 IF FND_MSG_PUB.Check_Msg_Level
472 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
473 THEN
474 FND_MSG_PUB.Add_Exc_Msg
475 ( G_PKG_NAME ,
476 l_api_name
477 );
478 END IF;
479 FND_MSG_PUB.Count_And_Get
480 ( p_count => x_msg_count,
481 p_data => x_msg_data
482 );
483
484 End Delete_Row;
485 /*----------------------------------------------------------*/
486 /*----------------------------------------------------------*/
487 /* Lock_Row Procedure */
488 /*----------------------------------------------------------*/
489 /* --
490 -- Purpose
491 -- Lock a row into WSH_ITM_USERS entity for the given cc seq id
492 --
493 -- Input Parameters
494 -- p_api_version
495 -- API version number (current version is 1.0)
496 -- p_init_msg_list (optional, default FND_API.G_FALSE)
497 -- Valid values: FND_API.G_FALSE or FND_API.G_TRUE.
498 -- if set to FND_API.G_TRUE
499 -- initialize error message list
500 -- if set to FND_API.G_FALSE - not initialize error
501 -- message list
502 -- p_commit (optional, default FND_API.G_FALSE)
503 -- whether or not to commit the changes to database
504 --
505 -- Input parameters for clear cross users informations
506 -- p_VENDOR_ID --Vendor Id
507 -- p_MASTER_ORGANIZATION_ID -- Master Org
508 -- p_APPLICATION_ID --Application_id added.
509 -- p_ORGANIZATION_ID --Org
510 -- p_APPLICATION_USER_ID -- Application User
511 -- p_USER_ID -- ITM User ID
512 -- p_ENCRYPTED_USER_PASSWORD -- Password
513 -- p_ITM_SEQUENCE_ID -- ITM Seq Id
514 --
515 --
516 -- Output Parameters
517 -- x_return_status
518 -- if the process succeeds, the value is
519 -- fnd_api.g_ret_sts_success;
520 -- if there is an expected error, the value is
521 -- fnd_api.g_ret_sts_error;
522 -- if there is an unexpected error, the value is
523 -- fnd_api.g_ret_sts_unexp_error;
524 -- x_msg_count
525 -- if there is one or more errors, the number of error messages
526 -- in the buffer
527 -- x_msg_data
528 -- if there is one and only one error, the error message
529 -- (See fnd_api package for more details about the above output parameters)
530
531 --*/
532 PROCEDURE Lock_Row
533 (
537 x_return_status OUT NOCOPY VARCHAR2 ,
534 p_api_version IN NUMBER ,
535 p_init_msg_list IN VARCHAR2 := fnd_api.g_false ,
536 p_commit IN VARCHAR2 := fnd_api.g_false ,
538 x_msg_count OUT NOCOPY NUMBER ,
539 x_msg_data OUT NOCOPY VARCHAR2 ,
540 p_VENDOR_ID IN NUMBER,
541 p_APPLICATION_ID IN NUMBER,
542 p_MASTER_ORGANIZATION_ID IN NUMBER,
543 p_ORGANIZATION_ID IN NUMBER ,
544 p_APPLICATION_USER_ID IN NUMBER ,
545 p_USER_ID IN VARCHAR2,
546 p_ENCRYPTED_USER_PASSWORD IN VARCHAR2,
547 p_ITM_USER_SEQUENCE_ID IN NUMBER,
548 p_rowid IN VARCHAR2
549 )
550 IS
551 CURSOR lock_row IS
552 SELECT *
553 FROM wsh_itm_user_setups
554 WHERE ITM_USER_SEQUENCE_ID = p_ITM_USER_SEQUENCE_ID
555 FOR UPDATE OF ITM_USER_SEQUENCE_ID NOWAIT;
556
557 Recinfo lock_row%ROWTYPE;
558
559 l_api_name CONSTANT VARCHAR2(30) := 'Lock_Row' ;
560 l_api_version number := 1.0;
561
562 BEGIN
563
564 -- Standard Start of API savepoint
565 SAVEPOINT WSH_ITM_USERS_PKG;
566 -- Standard call to check for call compatibility.
567 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
568 p_api_version ,
569 l_api_name ,
570 G_PKG_NAME )
571 THEN
572 FND_MESSAGE.SET_NAME('WSH', 'WSH_INCOMPATIBLE_API_CALL');
573 FND_MSG_PUB.ADD;
574 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
575 END IF;
576
577 -- Initialize message list if p_init_msg_list is set to TRUE.
578 IF FND_API.to_Boolean( p_init_msg_list ) THEN
579 FND_MSG_PUB.initialize;
580 END IF;
581
582 -- Initialize API return status to success
583 x_return_status := FND_API.G_RET_STS_SUCCESS;
584
585 -- Check Lock a row into wsh_itm_users entity with all detail information
586
587 OPEN lock_row;
588 FETCH lock_row into Recinfo;
589
590 IF (lock_row%NOTFOUND) THEN
591 CLOSE lock_row;
592 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
593 app_exception.raise_exception;
594 END IF;
595
596 CLOSE lock_row;
597
598 IF ((Recinfo.ITM_USER_SEQUENCE_ID = p_ITM_USER_SEQUENCE_ID)
599 AND
600 (Recinfo.vendor_id = p_vendor_id)
601 AND ((Recinfo.master_organization_id =p_master_organization_id)
602 OR
603 ((Recinfo.master_organization_id is null)
604 AND (p_master_organization_id is null )))
605 AND ((Recinfo.organization_id =p_organization_id)
606 OR
607 ((Recinfo.organization_id is null)
608 AND (p_organization_id is null )))
609 AND (Recinfo.user_id =p_user_id)
610 AND ((Recinfo.application_user_id =p_application_user_id)
611 OR
612 ((Recinfo.application_user_id is null)
613 AND (p_application_user_id is null )))
614 AND (Recinfo.encrypted_user_password =p_encrypted_user_password)
615 AND (Recinfo.application_id =p_APPLICATION_ID)) THEN
616 RETURN;
617 ELSE
618 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
619 app_exception.raise_exception;
620 END IF;
621
622 EXCEPTION
623
624 WHEN app_exception.application_exception or app_exception.record_lock_exception THEN
625 if (lock_row%ISOPEN) then
626 close lock_row;
627 end if;
628 RAISE;
629
630 WHEN others THEN
631 if (lock_row%ISOPEN) then
632 close lock_row;
633 end if;
634 FND_MESSAGE.SET_NAME('WSH','WSH_UNEXP_ERROR');
635 FND_MESSAGE.Set_Token('PACKAGE', 'WSH_ITM_USERS_PKG.LOCK_ROW');
636 FND_MESSAGE.Set_Token('ORA_ERROR',sqlcode);
637 FND_MESSAGE.Set_Token('ORA_TEXT',sqlerrm);
638
639 RAISE;
640
641
642 End Lock_Row;
643 END WSH_ITM_USERS_PKG;
[Home] [Help]