1 PACKAGE body WF_SOA_CTX_PKG AS
2 /* $Header: WFSOACTXB.pls 120.0.12010000.9 2009/07/26 07:09:15 snellepa noship $ */
3
4
5 pAppsBaseLang fnd_languages.nls_language%type;
6
7 /* private function to get apps base Language Code */
8 function getAppsBaseLang
9 return varchar2
10 is
11 begin
12
13 IF WF_SOA_CTX_PKG.pAppsBaseLang IS NULL THEN
14 SELECT language_code
15 INTO WF_SOA_CTX_PKG.pAppsBaseLang
16 FROM fnd_languages
17 WHERE Installed_flag = 'B';
18 END IF;
19
20 return WF_SOA_CTX_PKG.pAppsBaseLang;
21
22 end;
23 /* private procedure to get User specific Language Details*/
24
25 procedure getUserLang( userId Number, lang_code OUT NOCOPY varchar2)
26 is
27 pTemp fnd_languages.nls_language%type;
28 begin
29 pTemp:=fnd_profile.value_specific('ICX_LANGUAGE',user_id=>userId);
30 SELECT language_code
31 INTO lang_code
32 FROM fnd_languages
33 WHERE nls_language = pTemp
34 and Installed_flag in ('B','I');
35 exception
36 WHEN No_Data_found THEN
37 lang_code :=WF_SOA_CTX_PKG.getAppsBaseLang;
38 end;
39
40
41
42 PROCEDURE setNLSContext(userId VARCHAR2,
43 languageCode VARCHAR2)
44 IS
45 L_LANGUAGE VARCHAR2(50);
46 L_LANGUAGE_CODE VARCHAR2(50);
47 L_DATE_FORMAT VARCHAR2(50);
48 L_DATE_LANGUAGE VARCHAR2(50);
49 L_NUMERIC_CHARACTERS VARCHAR2(50);
50 L_NLS_SORT VARCHAR2(50);
51 L_NLS_TERRITORY VARCHAR2(50);
52 L_LIMIT_TIME NUMBER(15);
53 L_LIMIT_CONNECTS NUMBER(15);
54 L_ORG_ID VARCHAR2(50);
55 L_TIMEOUT NUMBER(15);
56 BEGIN
57 -- get the parameters to be passed to fnd_global from fnd_session_management
58 FND_SESSION_MANAGEMENT.SETUSERNLS(userId, languageCode, L_LANGUAGE, L_LANGUAGE_CODE, L_DATE_FORMAT, L_DATE_LANGUAGE, L_NUMERIC_CHARACTERS, L_NLS_SORT, L_NLS_TERRITORY, L_LIMIT_TIME, L_LIMIT_CONNECTS, L_ORG_ID, L_TIMEOUT);
59 --- set the values through fnd_global
60 FND_GLOBAL.SET_NLS_CONTEXT( L_LANGUAGE, L_DATE_FORMAT, L_DATE_LANGUAGE, L_NUMERIC_CHARACTERS, L_NLS_SORT, L_NLS_TERRITORY);
61 END setNLSContext ;
62 /* */
63 PROCEDURE SETCONTEXT_ID(pUserID NUMBER,
64 pRespID NUMBER,
65 pRespAppID NUMBER,
66 pRespAppName VARCHAR2,
67 pSecGrpID NUMBER,
68 pLangCode VARCHAR2,
69 pOrgID NUMBER)
70 IS
71
72 pTemp VARCHAR2(1);
73 BEGIN
74
75 -- Initialize APPS
76 FND_GLOBAL.APPS_INITIALIZE(pUserID, pRespID, pRespAppID, pSecGrpID);
77 IF(pRespAppID <> -1) THEN
78 MO_GLOBAL.Init(pRespAppName);
79 -- Set MOAC Values
80 IF pOrgID IS NOT NULL THEN
81 BEGIN
82 SELECT 'X'
83 INTO pTemp
84 FROM hr_operating_units
85 WHERE usable_flag IS NULL
86 AND ORGANIZATION_ID =pOrgID;
87
88 MO_GLOBAL.set_policy_context('S',pOrgID);
89 EXCEPTION
90 WHEN NO_DATA_FOUND THEN
91 raise_application_error(-20001,'INVALID_ORGID');
92 END;
93 END IF;
94 END IF;
95 --Set NLS
96 setNLSContext(pUserID, pLangCode);
97 END SETCONTEXT_ID;
98 /* */
99 PROCEDURE GETCONTEXT_ID( pUserName VARCHAR2,
100 pResp VARCHAR2,
101 pRespApp VARCHAR2,
102 pSecurityGroup VARCHAR2,
103 pLang VARCHAR2,
104 pIsLangCode NUMBER default 0,
105 pUserID OUT NOCOPY NUMBER,
106 pRespID OUT NOCOPY NUMBER,
107 pRespAppID OUT NOCOPY NUMBER,
108 pSecurityGroupID OUT NOCOPY NUMBER,
109 pLangCode OUT NOCOPY VARCHAR2 ,
110 x_status_code OUT NOCOPY VARCHAR2,
111 x_error_code OUT NOCOPY VARCHAR2
112 )
113 IS
114 pStage NUMBER;
115 pTemp varchar2(100);
116 nTemp NUMBER;
117 BEGIN
118 --initialize to default values
119 pUserID:=-1;
120 pRespID:=-1;
121 pRespAppID:=-1;
122 pSecurityGroupID:=0;
123 pLangCode:='US';
124
125 x_status_code:='F'; -- Initially FAILURE
126 x_error_code:=NULL;
127 --Start Username->UserID
128 IF pUserName IS NULL THEN
129 raise_application_error(-20001,'INVALID_USER_NAME');
130 END IF;
131 pStage:=1;
132 SELECT user_id
133 INTO pUserID
134 FROM fnd_user
135 WHERE user_name = upper(pUserName);
136
137 --End Username->UserID
138 pStage:=2;
139 --Start LangCode
140 IF ( pLang IS NULL ) THEN
141 --No language specified get the details from profile, else default to US
142 WF_SOA_CTX_PKG.getUserLang( pUserID, pLangCode);
143 ELSE -- language is specifed
144 IF pIsLangCode=1 THEN -- and it has been specified as Code
145 SELECT language_code
146 INTO pLangCode
147 FROM fnd_languages
148 WHERE language_code = upper(pLang)
149 and Installed_flag in ('B','I');
150
151 ELSE -- it has been specied as Name ( in English !!)
152 SELECT language_code
153 INTO pLangCode
154 FROM fnd_languages
155 WHERE nls_language = upper(pLang)
156 and Installed_flag in ('B','I');
157
158
159 END IF;
160 END IF;
161 --To derive Resp Name , we need App Name also..
162
163 IF ( pRespApp IS NOT NULL ) THEN
164
165 pStage:=3;
166 SELECT B.APPLICATION_ID
167 INTO pRespAppID
168 FROM FND_APPLICATION B
169 WHERE B.APPLICATION_SHORT_NAME=Upper(pRespApp);
170 END IF;
171
172
173 If pResp is NOT NULL Then
174
175 IF pRespAppID <> -1 Then
176 pStage :=4;
177 IF ( InStr(pResp,'{key}')=1) THEN
178 --get from key
179 SELECT RESPONSIBILITY_ID
180 INTO pRespID
181 FROM fnd_responsibility
182 WHERE APPLICATION_ID =pRespAppID
183 AND RESPONSIBILITY_KEY=SUBSTR(pResp,6);
184
185 ELSE
186 --get from name
187 SELECT B.RESPONSIBILITY_ID
188 INTO pRespID
189 FROM FND_RESPONSIBILITY_TL T,
190 FND_RESPONSIBILITY B
191 WHERE B.RESPONSIBILITY_ID = T.RESPONSIBILITY_ID
192 AND B.APPLICATION_ID = T.APPLICATION_ID
193 AND T.LANGUAGE = pLangCode
194 AND b.application_id = pRespAppID
195 AND T.RESPONSIBILITY_NAME= pResp;
196
197 END IF;
198 ELSE
199
200 --Start Resp name->ID
201 pStage :=4;
202 IF ( InStr(pResp,'{key}')=1) THEN
203 --get from key
204 SELECT count(*)
205 INTO nTemp
206 FROM fnd_responsibility
207 WHERE RESPONSIBILITY_KEY=SUBSTR(pResp,6);
208 If nTemp =0 Then
209 Raise No_data_Found;
210 elsif nTemp=1 Then
211 SELECT RESPONSIBILITY_ID,APPLICATION_ID
212 INTO pRespID, pRespAppID
213 FROM fnd_responsibility
214 WHERE RESPONSIBILITY_KEY=SUBSTR(pResp,6);
215 else
216 pStage:=3;
217 Raise No_data_found;
218 End If;
219
220 ELSE
221
222 --SELECT B.RESPONSIBILITY_ID
223 SELECT COUNT(*)
224 INTO nTemp
225 FROM FND_RESPONSIBILITY_TL T,
226 FND_RESPONSIBILITY B
227 WHERE B.RESPONSIBILITY_ID = T.RESPONSIBILITY_ID
228 AND B.APPLICATION_ID = T.APPLICATION_ID
229 AND T.LANGUAGE = pLangCode
230 AND T.RESPONSIBILITY_NAME= pResp;
231
232 If nTemp =0 Then
233 Raise No_data_Found;
234 elsif nTemp=1 Then
235 SELECT B.RESPONSIBILITY_ID, B.APPLICATION_ID
236 INTO pRespID, pRespAppID
237 FROM FND_RESPONSIBILITY_TL T,
238 FND_RESPONSIBILITY B
239 WHERE B.RESPONSIBILITY_ID = T.RESPONSIBILITY_ID
240 AND B.APPLICATION_ID = T.APPLICATION_ID
241 AND T.LANGUAGE = pLangCode
242 AND T.RESPONSIBILITY_NAME= pResp;
243 else
244 pStage:=3;
245 Raise No_data_found;
246 End If;
247
248 END IF;
249
250 END IF;
251
252
253 END IF;
254
255 -- changing from security group name to security group key
256 -- based on comments from Abhishek.verma
257
258 pStage:=5;
259 SELECT SECURITY_GROUP_ID
260 INTO pSecurityGroupID
261 FROM FND_SECURITY_GROUPS
262 WHERE upper(SECURITY_GROUP_KEY) = upper(nvl(pSecurityGroup,'STANDARD'));
263
264 -- changes for bug 8492785
265 -- need to check for user-resp combination here
266 -- as SOA Java code calls only getcontext_id
267 pStage:=6;
268 IF pRespID <> -1 THEN
269 If fnd_profile.value_specific('ENABLE_SECURITY_GROUPS',RESPONSIBILITY_ID=> pRespID, APPLICATION_ID=>pRespAppID )='Y' Then
270 SELECT 1
271 INTO nTemp
272 FROM FND_USER_RESP_GROUPS
273 WHERE user_id=pUserID
274 AND RESPONSIBILITY_ID=pRespID
275 and SECURITY_GROUP_ID=pSecurityGroupID
276 AND rownum <2;
277 Else
278 SELECT 1
279 INTO nTemp
280 FROM FND_USER_RESP_GROUPS
281 WHERE user_id=pUserID
282 AND RESPONSIBILITY_ID=pRespID
283 AND rownum <2;
284 End if;
285
286 END IF;
287
288 If x_error_code is Null then
289 x_status_code:='S';
290 End If;
291
292 EXCEPTION
293 WHEN No_Data_found THEN
294 IF pStage=1 THEN
295 x_error_code:='INVALID_USER_NAME';
296 ELSIF pStage=2 THEN
297 x_error_code:='INVALID_LANGCODE';
298 WF_SOA_CTX_PKG.getUserLang( pUserID, pLangCode);
299 ELSIF pStage=3 THEN
300 x_error_code:='INVALID_RESP_APP_NAME';
301 ELSIF pStage=4 THEN
302 x_error_code:='INVALID_RESP_NAME';
303 ELSIF pStage=5 THEN
304 x_error_code:='INVALID_SECGRP';
305 ELSIF pStage=6 THEN
306 x_error_code:='RESP_NOT_ASSIGNED_TO_USER';
307 END IF;
308 END GETCONTEXT_ID;
309
310 /* */
311 PROCEDURE setContext(pUserName VARCHAR2,
312 pResp VARCHAR2,
313 pRespApp VARCHAR2,
314 pSecurityGroup VARCHAR2,
315 pnlslanguage VARCHAR2,
316 pIsLangCode NUMBER default 0,
317 pOrgId NUMBER)
318 IS
319 pLangCode fnd_languages.language_code%TYPE;
320 pUserId fnd_user.user_id%TYPE;
321 pAppId fnd_application.application_id%TYPE;
322 pRespId fnd_responsibility.responsibility_id%TYPE;
323 pSecGrpId fnd_security_groups.security_group_id%TYPE;
324 x_status_code VARCHAR2(1);
325 x_error_code varchar2(50);
326 BEGIN
327 GETCONTEXT_ID(pUserName, pResp,pRespApp,pSecurityGroup, pnlslanguage,pIsLangCode, pUserId,pRespId,pAppId,pSecGrpId,pLangCode,x_status_code,x_error_code);
328 IF x_status_code ='S' THEN
329 SETCONTEXT_ID(pUserId, pRespID, pAppId ,pRespApp, pSecGrpId , pLangCode , pOrgId);
330 ELSE
331 RAISE_APPLICATION_ERROR(-20001, x_error_code);
332 END IF;
333 END setContext;
334
335
336
337
338
339 END WF_SOA_CTX_PKG;
[Home] [Help]