[Home] [Help]
PACKAGE BODY: APPS.ARI_SELF_SERVICE_PKG
Source
4 /*=======================================================================+
1 package BODY ARI_SELF_SERVICE_PKG AS
2 /* $Header: ARISSPGB.pls 120.2.12020000.4 2013/01/17 09:38:57 melapaku ship $ */
3
5 | Global Constants
6 +=======================================================================*/
7
8 G_PKG_NAME CONSTANT VARCHAR2(30) := 'ARI_SELF_SERVICE_PKG';
9 G_CREATED_BY_MODULE CONSTANT VARCHAR2(5) := 'ARI';
10 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
11
15
12 --------------------------------------------------------------------------
13 -- Local Procedure Signature
14 --------------------------------------------------------------------------
16 PROCEDURE InformSysAdminError(p_procedure_name IN VARCHAR2,
17 p_debug_info IN VARCHAR2,
18 p_error IN VARCHAR2);
19
20
21 FUNCTION CheckResponsibilityExist(p_user_id in number,
22 p_responsibility_id in number,
23 p_resp_appl_id in number,
24 p_security_group_id in number default null) return varchar2
25 ---------------------------------------------------------------------------
26 IS
27 l_resp_exists boolean;
28 l_count NUMBER;
29 begin
30
31 l_resp_exists := fnd_user_resp_groups_api.Assignment_Exists(
32 user_id => p_user_id,
33 responsibility_id => p_responsibility_id,
34 responsibility_application_id => p_resp_appl_id,
35 security_group_id => p_security_group_id);
36 if l_resp_exists then
37 select count(*) into l_count from fnd_user_resp_groups where user_id=p_user_id and responsibility_id=p_responsibility_id;
38 if l_count <= 0 then
39 return 'N';
40 else
41 return 'Y';
42 end if;
43 else
44 return 'N';
45 end if;
46
47 EXCEPTION
48 WHEN OTHERS THEN
49 APP_EXCEPTION.RAISE_EXCEPTION;
50 END CheckResponsibilityExist;
51
52 PROCEDURE AssignResponsibility(p_user_id in number,
53 p_responsibility_id in number,
54 p_resp_appl_id in number,
55 p_security_group_id in number default null)
56 ---------------------------------------------------------------------------
57 IS
58 l_resp_exists boolean;
59 BEGIN
60
61 l_resp_exists := fnd_user_resp_groups_api.Assignment_Exists(
62 user_id => p_user_id,
63 responsibility_id => p_responsibility_id,
64 responsibility_application_id => p_resp_appl_id,
65 security_group_id => p_security_group_id);
66
67 if NOT l_resp_exists then
68 -- add the appropriate responsibility
69 fnd_user_resp_groups_api.Insert_Assignment
70 (user_id => p_user_id
71 ,responsibility_id => p_responsibility_id
72 ,responsibility_application_id => p_resp_appl_id
73 ,security_group_id => p_security_group_id
74 ,start_date => trunc(sysdate)
75 ,end_date => null
76 ,description => ' ' -- ### description was supposed to default
77 -- to null... but does not look like it has
78 );
79 else
80 fnd_user_resp_groups_api.Update_Assignment
81 (user_id => p_user_id
82 ,responsibility_id => p_responsibility_id
83 ,responsibility_application_id => p_resp_appl_id
84 ,security_group_id => p_security_group_id
85 ,start_date => trunc(sysdate)
86 ,end_date => null
87 ,description => ' ' -- ### description was supposed to default
88 -- to null... but does not look like it has
89 );
90 end if;
91
92 EXCEPTION
93 WHEN OTHERS THEN
94 APP_EXCEPTION.RAISE_EXCEPTION;
95
96 END AssignResponsibility;
97
98 FUNCTION ValidateLogin(
99 p_user IN VARCHAR2,
100 p_pwd IN VARCHAR2,
101 p_disable in varchar2) return VARCHAR2 is
102 --
103 user VARCHAR2(100) := upper(p_user);
104 userID NUMBER := -1;
105 l_result varchar2(10) := 'N';
106 l_loginID number;
107 l_expired varchar2(10);
108 --
109 BEGIN
110 hr_utility.set_location('calling validate_login user_name='||p_user||' and pwd:'||p_pwd||' and disable='||p_disable,10);
111 l_result := fnd_web_sec.validate_login(p_user => p_user,
112 p_pwd => p_pwd);
113 hr_utility.set_location('result='||l_result,20);
114
115 if l_result = 'N' and p_disable = 'Y' then
116 begin
117 hr_utility.set_location('calling disable user',30);
118 select user_id into userID
119 from fnd_user
120 where user_name = user AND
121 user_id <> 6 and
122 (start_date <= sysdate) AND
123 (end_date is null or end_date > sysdate);
124 fnd_web_sec.unsuccessful_login(userID);
125 hr_utility.set_location('called disable user',40);
126 exception
127 when no_data_found then
128 hr_utility.set_location('No data found',10);
129 return 'N';
130 end;
131 end if;
132 if l_result = 'Y' and p_disable = 'Y'then
133 hr_utility.set_location('creating new session loginid='||l_loginID||' expired='||l_expired,10);
134 -- fnd_signon.new_icx_session(userID, l_loginID, l_expired);
135 end if;
136 hr_utility.set_location('final result:='||l_result,40);
137 --
138 return l_result;
139 --
140 END ValidateLogin;
141
142 /* =======================================================================
143 | PROCEDURE LogAccessRequest
144 |
145 | DESCRIPTION Inserts a record into umx_reg_requests table on the initiation
146 |of the UMX Add Access Flow
147 |
148 |
149 | PARAMETERS
150 |
151 |
152 * ======================================================================*/
153 ---------------------------------------------------------------------------
154 PROCEDURE LogAccessRequest(p_reg_request_id IN OUT NOCOPY NUMBER,
155 p_reg_service_code IN VARCHAR2 )
159 l_procedure_name VARCHAR2(30) := '.LogAccessRequest';
156 ---------------------------------------------------------------------------
157 IS
158 l_rowid VARCHAR2(100);
160 l_debug_info VARCHAR2(200);
161 l_reg_service_type VARCHAR2(200);
162 l_wf_role_name VARCHAR2(200);
163 l_reg_request_id VARCHAR2(200) := null;
164 l_justification VARCHAR2(200);
165 l_ame_transaction_type_id VARCHAR2(200);
166
170
167 CURSOR C1 IS SELECT rowid FROM UMX_REG_REQUESTS
168 WHERE REG_REQUEST_ID = p_reg_request_id;
169 CURSOR C2 IS SELECT UMX_REG_REQUESTS_S.nextval FROM sys.dual;
171 CURSOR C3 IS SELECT Reg_Request_Id
172 FROM UMX_REG_REQUESTS
173 WHERE Created_By = nvl(FND_GLOBAL.user_id,-1)
174 AND Reg_Service_Code = p_reg_service_code
175 AND Status_Code = 'UNASSIGNED';
176
177 BEGIN
178 ----------------------------------------------------------------------------
179 l_debug_info := 'In debug mode, log we have entered this procedure';
180 ----------------------------------------------------------------------------
181 IF (PG_DEBUG = 'Y') THEN
182 arp_standard.debug(G_PKG_NAME || l_procedure_name || '+');
183 END IF;
184
185
186 IF (p_reg_request_id is NULL) THEN
187 OPEN C2;
188 FETCH C2 INTO p_reg_request_id;
189 CLOSE C2;
190 END IF;
191
192 ----------------------------------------------------------------------------
193 l_debug_info := 'Fetched the Reg Request Id value';
194 ----------------------------------------------------------------------------
195 IF (PG_DEBUG = 'Y') THEN
196 arp_standard.debug(l_debug_info||' '||p_reg_request_id);
197 END IF;
198
199 ----------------------------------------------------------------------------
200 l_debug_info := 'Insert New UMX Reg Request Record';
201 ----------------------------------------------------------------------------
202 IF (PG_DEBUG = 'Y') THEN
203 arp_standard.debug(l_debug_info);
204 END IF;
205
206 SELECT REG_SERVICE_TYPE,WF_ROLE_NAME,AME_TRANSACTION_TYPE_ID INTO l_reg_service_type,l_wf_role_name,l_ame_transaction_type_id
207 FROM umx_reg_services_vl
208 WHERE REG_SERVICE_CODE = p_reg_service_code;
209
210 ----------------------------------------------------------------------------
211 l_debug_info := 'Service Type and Role Name values obtained';
212 ----------------------------------------------------------------------------
213 IF (PG_DEBUG = 'Y') THEN
214 arp_standard.debug(l_debug_info||' '||l_reg_service_type||', '||l_wf_role_name);
215 END IF;
216
217 OPEN C3;
218 IF (C3%NOTFOUND) THEN
219 CLOSE C3;
220 ELSE
221 FETCH C3 INTO l_reg_request_id;
222 CLOSE C3;
223 END IF;
224
225
226
227 ----------------------------------------------------------------------------
228 l_debug_info := 'Request Id already exists against reg_service code passed for the user';
229 ----------------------------------------------------------------------------
230 IF (PG_DEBUG = 'Y') THEN
231 arp_standard.debug(l_debug_info||' '||l_reg_request_id);
232 END IF;
233
234 --------------------------------------------------------------------
235 l_debug_info := 'Retrieve Justification Message';
236 -------------------------------------------------------------------
237 FND_MESSAGE.Set_Name('AR','ARI_ADD_RESP_JUSTIFY');
238
239 l_justification := FND_MESSAGE.Get;
240
241 IF (PG_DEBUG = 'Y') THEN
242 arp_standard.debug(l_debug_info||' '||l_justification);
243 END IF;
244
245 IF (l_reg_request_id IS NULL) THEN
246 INSERT INTO UMX_REG_REQUESTS(
247 Reg_Request_Id,
248 Reg_Service_Type,
249 Status_Code,
250 Requested_Start_Date,
251 Created_By,
252 Creation_Date,
253 Last_Updated_By,
254 Last_Update_Date,
255 Last_Update_Login,
256 Requested_By_User_Id,
257 Requested_For_User_Id,
258 Wf_Role_Name,
259 Reg_Service_Code,
260 Ame_Application_Id,
261 Ame_Transaction_Type_Id,
262 Justification
263 ) VALUES (
264 p_reg_request_id,
265 l_reg_service_type,
266 'UNASSIGNED',
267 sysdate,
268 nvl(FND_GLOBAL.user_id,-1),
269 sysdate,
273 nvl(FND_GLOBAL.user_id,-1),
270 nvl(FND_GLOBAL.user_id,-1),
271 sysdate,
272 nvl(FND_GLOBAL.conc_login_id,FND_GLOBAL.login_id),
274 nvl(FND_GLOBAL.user_id,-1),
275 l_wf_role_name,
276 p_reg_service_code,
277 nvl(FND_GLOBAL.resp_appl_id,-1),
278 l_ame_transaction_type_id,
279 l_justification
280 );
281 OPEN C1;
282 FETCH C1 INTO l_rowid;
283
284 IF (C1%NOTFOUND) THEN
285 CLOSE C1;
286 RAISE NO_DATA_FOUND;
287 END IF;
288
289 CLOSE C1;
290
291 COMMIT;
292
293 ELSE
294 p_reg_request_id := l_reg_request_id;
295 END IF;
296 ----------------------------------------------------------------------------
297 l_debug_info := 'In debug mode, log that we have exited this procedure';
298 ----------------------------------------------------------------------------
299 IF (PG_DEBUG = 'Y') THEN
300 arp_standard.debug(G_PKG_NAME || l_procedure_name || '-');
301 END IF;
302
303 EXCEPTION
304 WHEN OTHERS THEN
305 IF (SQLCODE <> -20001) THEN
306 IF (PG_DEBUG = 'Y') THEN
307 arp_standard.debug('Unexpected Exception in ' || G_PKG_NAME || l_procedure_name);
308 arp_standard.debug('Debug Info: ' || l_debug_info);
309 arp_standard.debug(SQLERRM);
310 END IF;
311 FND_MESSAGE.SET_NAME ('AR','ARI_REG_DISPLAY_UNEXP_ERROR');
312 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debug_info);
313 FND_MESSAGE.SET_TOKEN('PROCEDURE', G_PKG_NAME || l_procedure_name);
314 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
315 FND_MSG_PUB.ADD;
316 InformSysAdminError(p_procedure_name => l_procedure_name,
317 p_debug_info => l_debug_info,
318 p_error => SQLERRM);
319 END IF;
320 APP_EXCEPTION.RAISE_EXCEPTION;
321 END LogAccessRequest;
322
323 /* =======================================================================
324 | PROCEDURE InformSysAdminError
325 |
326 | DESCRIPTION
327 |
328 |
329 | PARAMETERS
330 |
331 * ======================================================================*/
332 --------------------------------------------------------------------------
333 PROCEDURE InformSysAdminError(p_procedure_name IN VARCHAR2,
337 IS
334 p_debug_info IN VARCHAR2,
335 p_error IN VARCHAR2)
336 --------------------------------------------------------------------------
338 l_pkg_name VARCHAR2(30) := G_PKG_NAME;
339 l_procedure_name VARCHAR2(30) := 'InformSysAdminError';
340 l_debug_info VARCHAR2(200);
341 BEGIN
342
343 --------------------------------------------------------------------
344 l_debug_info := 'Initiate Inform Sysadmin Workflow';
345 -------------------------------------------------------------------
346
347 EXCEPTION
348 WHEN OTHERS THEN
349 IF (SQLCODE <> -20001) THEN
350 IF (PG_DEBUG = 'Y') THEN
351 arp_standard.debug('Unexpected Exception in ' || G_PKG_NAME || l_procedure_name);
352 arp_standard.debug('Debug Info: ' || l_debug_info);
353 arp_standard.debug(SQLERRM);
354 END IF;
355 FND_MESSAGE.SET_NAME ('AR','ARI_REG_DISPLAY_UNEXP_ERROR');
356 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debug_info);
357 FND_MESSAGE.SET_TOKEN('PROCEDURE', G_PKG_NAME || l_procedure_name);
358 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
359 FND_MSG_PUB.ADD;
360 END IF;
361 APP_EXCEPTION.RAISE_EXCEPTION;
362 END InformSysAdminError;
363
364 END ARI_SELF_SERVICE_PKG;