[Home] [Help]
PACKAGE BODY: APPS.FND_SOS_SERVICE
Source
1 PACKAGE BODY FND_SOS_SERVICE AS
2 /* $Header: fndsosb.pls 120.0.12010000.1 2009/08/17 03:07:14 snellepa noship $ */
3 installed_languages VARCHAR2(3000);
4 database_id VARCHAR2(4500);
5
6 PROCEDURE getAllInstalledlanguages
7 IS
8 t1 varchar2(4);
9 CURSOR c1 IS
10 SELECT LANGUAGE_CODE
11 FROM fnd_languages
12 WHERE INSTALLED_FLAG='I';
13
14 BEGIN
15 FOR t1 IN c1
16 LOOP
17 FND_SOS_SERVICE.installed_languages := t1.LANGUAGE_CODE || ':'|| FND_SOS_SERVICE.installed_languages ;
18 END LOOP;
19 END getAllInstalledlanguages;
20
21
22 procedure getDatabaseID
23 is
24 begin
25 database_id :=FND_WEB_CONFIG.DATABASE_ID;
26 end;
27
28 PROCEDURE AUTHENTICATE( p_user_name IN VARCHAR2 ,
29 p_password IN VARCHAR2 ,
30 login_status OUT NOCOPY VARCHAR2,
31 user_id OUT NOCOPY NUMBER,
32 description out NOCOPY VARCHAR2,
33 user_status OUT NOCOPY VARCHAR2,
34 languages OUT NOCOPY VARCHAR2,
35 session_id OUT NOCOPY NUMBER,
36 xsid OUT NOCOPY VARCHAR2 ,
37 apps_database_id out NOCOPY VARCHAR2
38
39 )
40 IS
41 ret VARCHAR2(1);
42 uid NUMBER;
43 sess_id NUMBER;
44 enc_pwd FND_USER.ENCRYPTED_USER_PASSWORD%TYPE;
45 pwd_expired varchar2(1);
46 p_lang fnd_languages.nls_language%type;
47
48 BEGIN
49 --++++ validate login
50 ret :=fnd_web_sec.validate_login(p_user_name, p_password);
51 IF( ret <>'Y') THEN
52 login_status:='INVALID_LOGIN';
53 END IF;
54
55 login_status:='VALID_LOGIN';
56
57 --+++ get user details
58
59
60 SELECT user_id,description,ENCRYPTED_USER_PASSWORD
61 INTO user_id,description,enc_pwd
62 FROM fnd_user
63 WHERE UPPER(user_name)=UPPER(p_user_name);
64
65 IF enc_pwd ='INVALID' THEN
66 user_status:='LOCKED';
67 ELSE
68 fnd_signon.is_pwd_expired(user_id, pwd_expired);
69 If pwd_expired ='Y' then
70 user_status:='PASSWORD_EXPIRED';
71 ELSE
72 user_status:='ACTIVE';
73 END IF;
74 END IF;
75
76 --+++get session details
77 sess_id := fnd_session_management.createSession(p_user_id=>user_id ) ;
78 SELECT xsid
79 INTO xsid
80 FROM icx_sessions
81 WHERE session_id=sess_id;
82
83 session_id:=sess_id;
84
85 --+++get language details
86 p_lang:=fnd_profile.value_specific(NAME=>'ICX_LANGUAGE',USER_ID=>user_id);
87
88 SELECT language_code
89 INTO languages
90 FROM fnd_languages
91 WHERE nls_language = upper(p_lang);
92
93 IF installed_languages IS NULL THEN
94 getAllInstalledlanguages;
95 END IF;
96 languages:=languages
97 ||':'|| FND_SOS_SERVICE.installed_languages;
98
99 --+++get database id
100 If FND_SOS_SERVICE.database_id is Null Then
101 getDatabaseID;
102 End If;
103 apps_database_id:=FND_SOS_SERVICE.database_id;
104
105
106
107 END authenticate;
108
109 FUNCTION validate_user_cookie(p_user_name VARCHAR2 ,
110 p_password VARCHAR2)
111 RETURN VARCHAR2
112 IS
113 retval VARCHAR2(1) :='N';
114 sess_id NUMBER;
115 CURSOR check_cookie IS
116 SELECT session_id
117 FROM icx_sessions
118 WHERE user_id=
119 (SELECT user_id
120 FROM fnd_user
121 WHERE user_name=UPPER(p_user_name)
122 )
123 AND xsid =p_password
124 AND disabled_flag='N';
125
126 BEGIN
127 OPEN check_cookie;
128 FETCH check_cookie
129 INTO sess_id;
130
131 IF check_cookie%notfound THEN
132 retval:='N';
133 ELSE
134 retval:='Y';
135 END IF;
136 CLOSE check_cookie;
137 RETURN retval;
138 END validate_user_cookie;
139
140
141 END fnd_sos_service;