1 PACKAGE BODY IGP_AD_USERID_PKG AS
2 /* $Header: IGSPADAB.pls 120.7 2006/05/04 22:49:44 bmerugu noship $ */
3 /*
4 || Created By : nsidana
5 || Created On : 1/28/2004
6 || Purpose : Main package for Portfolio user creation and deactivation.
7 || Known limitations, enhancements or remarks :
8 || Change History :
9 || Who When What
10 || pkpatel 21-JUN-2004 Bug 3690449 (Removed the reference of ACCOUNT_WF_STATUS and made the USER_NAME as UPPER)
11 || (reverse chronological order - newest change first)
12 */
13 PROCEDURE RECORD_DATA(itemtype IN VARCHAR2,
14 itemkey IN VARCHAR2,
15 actid IN NUMBER,
16 funcmode IN VARCHAR2,
17 resultout OUT NOCOPY VARCHAR2 )
18 AS
19 /*
20 || Created By : nsidana
21 || Created On : 1/28/2004
22 || Purpose : Records the data in the interface table.
23 || Known limitations, enhancements or remarks :
24 || Change History :
25 || Who When What
26 || (reverse chronological order - newest change first)
27 */
28 -- Get the details of
29 CURSOR c_get_val IS
30 SELECT to_char(sysdate,'dd-mm-yyyy-HH24-MI-SS')
31 FROM dual;
32
33 CURSOR c_get_fnd_user(cp_user_id fnd_user.user_id%TYPE) IS
34 SELECT user_name
35 FROM fnd_user
36 WHERE user_id=cp_user_id;
37
38 CURSOR c_get_party_name(cp_party_id hz_parties.party_id%TYPE) IS
39 SELECT party_name ,party_number,email_address
40 FROM hz_parties
41 WHERE party_id=cp_party_id;
42
43 -- Get the details of email of approver.
44 CURSOR c_get_approver_email(cp_user_name VARCHAR2) IS
45 SELECT email_address
46 FROM fnd_user
47 WHERE user_name=cp_user_name;
48
49 CURSOR c_get_req_dets(cp_req_id number)
50 is
51 select party_name, email_address
52 from hz_parties
53 where party_id=cp_req_id;
54
55 -- get requestor fnd user_name
56 CURSOR c_get_req_fnd_user_name(cp_req_id number)
57 is
58 SELECT user_name
59 FROM fnd_user
60 WHERE PERSON_PARTY_ID=cp_req_id;
61
62
63 l_party_id hz_parties.party_id%TYPE;
64 l_user_id fnd_user.user_id%TYPE;
65 l_user_name fnd_user.user_name%TYPE;
66 l_email_address fnd_user.email_address%TYPE;
67 l_person_name hz_parties.party_name%TYPE;
68 l_org hz_parties.party_name%TYPE;
69 l_approver_email fnd_user.email_address%TYPE;
70 l_item_key wf_items.item_key%TYPE;
71 l_classification_cd igp_ac_acc_classes.acc_classification_code%TYPE;
72 l_expiration_dt DATE;
73 l_requestor VARCHAR2(240);
74 l_approver VARCHAR2(240);
75 l_val VARCHAR2(240);
76 l_err VARCHAR2(2000);
77 l_message_text VARCHAR2(2000);
78 l_rowid VARCHAR2(30);
79 l_result VARCHAR2(240);
80 l_approver_mail_url VARCHAR2(1000);
81 l_url_part1 VARCHAR2(1000);
82 l_url_part2 VARCHAR2(1000);
83 l_url VARCHAR2(2000);
84 l_party_det c_get_party_name%ROWTYPE;
85 l_req_email hz_parties.email_address%TYPE;
86 c_get_req_dets_rec c_get_req_dets%ROWTYPE;
87 l_req_id number;
88
89 BEGIN
90
91 IF (funcmode = 'RUN') THEN
92 l_requestor := NULL;
93 l_approver := NULL;
94
95 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
96 fnd_log.string(fnd_log.level_procedure,'igs.plsql.igp_ad_user_id.record_data','itemkey:'||itemkey);
97 END IF;
98
99 l_item_key := itemkey;
100 l_party_id := Wf_Engine.GetItemAttrNumber(itemtype,itemkey,'P_PARTY_ID');
101 l_classification_cd := Wf_Engine.GetItemAttrText(itemtype,itemkey,'P_CLASSIFICATION_CD');
102 l_expiration_dt := Wf_Engine.GetItemAttrDate(itemtype,itemkey,'P_EXPIRATION_DT');
103 l_user_id := Wf_Engine.GetItemAttrNumber(itemtype,itemkey,'P_USER_ID');
104 l_user_name := Wf_Engine.GetItemAttrText(itemtype,itemkey,'P_USER_NAME');
105 l_email_address :=Wf_Engine.GetItemAttrText(itemtype,itemkey,'P_EMAIL_ADDRESS');
106 l_org :=Wf_Engine.GetItemAttrText(itemtype,itemkey,'P_ORGANIZATION');
107 l_req_id :=Wf_Engine.GetItemAttrText(itemtype,itemkey,'P_REQ_ID');
108 l_req_email :=null;
109
110 l_user_name := UPPER(l_user_name);
111 wf_engine.SetItemAttrText(itemtype,itemkey,'P_USER_NAME', l_user_name);
112
113 -- Start : setting req date
114 OPEN c_get_req_dets(l_req_id);
115 FETCH c_get_req_dets INTO c_get_req_dets_rec;
116 CLOSE c_get_req_dets;
117 l_req_email:='<a href=mailto:'||c_get_req_dets_rec.party_name||'>'||c_get_req_dets_rec.email_address||'</a>';
118 wf_engine.SetItemAttrText(itemtype,itemkey,'P_REQ_NAME', c_get_req_dets_rec.party_name);
119 wf_engine.SetItemAttrText(itemtype,itemkey,'P_REQ_EMAIL', l_req_email);
120 -- End : setting req date
121
122 OPEN c_get_party_name(l_party_id);
123 FETCH c_get_party_name INTO l_party_det;
124 CLOSE c_get_party_name;
125
126 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
127 fnd_log.string(fnd_log.level_procedure,'igs.plsql.igp_ad_user_id.record_data','l_party_det.name :'||l_party_det.party_name);
128 END IF;
129
130 wf_engine.SetItemAttrText(itemtype,itemkey,'P_PERSON_NUMBER', l_party_det.party_number);
131 wf_engine.SetItemAttrText(itemtype,itemkey,'P_PERSON_NAME', l_party_det.party_name);
132
133 IF (l_user_id IS NOT NULL) THEN
134
135 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
136 fnd_log.string(fnd_log.level_procedure,'igs.plsql.igp_ad_user_id.record_data','UserId passed is Not Null' ||l_user_id);
137 END IF;
138 wf_engine.SetItemAttrText(itemtype,itemkey,'P_ACTION', 'ACCOUNT'); -- Already a FND user. Only Portfolio a/c is reqd.
139 ELSE
140
141 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
142 fnd_log.string(fnd_log.level_procedure,'igs.plsql.igp_ad_user_id.record_data','UserId passed is Null');
143 END IF;
144 wf_engine.SetItemAttrText(itemtype,itemkey,'P_ACTION', 'BOTH'); -- Both FND and Portfolio a/c have to be created.
145 END IF;
146
147
148 IF (l_user_id IS NULL) THEN -- For a new FND user.
149 BEGIN
150 INSERT INTO igp_ac_account_ints (item_key,
151 int_account_id,
152 party_id,
153 acc_classification_code,
154 access_expiration_date,
155 user_id,
156 user_name,
157 created_by,
158 creation_date,
159 last_updated_by,
160 last_update_date,
161 last_update_login)
162 VALUES(
163 l_item_key,
164 igp_ac_account_ints_s.NEXTVAL,
165 l_party_id,
166 l_classification_cd,
167 l_expiration_dt,
168 null,
169 l_user_name,
170 fnd_global.user_id,
171 sysdate,
172 fnd_global.user_id,
173 sysdate,
174 fnd_global.user_id
175 );
176 EXCEPTION
177 WHEN OTHERS THEN
178 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
179 fnd_log.string(fnd_log.level_exception,'igs.plsql.igp_ad_user_id.record_data','Exception while recording data in interface table.'||SQLERRM);
180 END IF;
181 resultout := 'COMPLETE:IGP_FAIL';
182 RAISE;
183 END;
184 -- Start : Setting requestor fnd user name
185 OPEN c_get_req_fnd_user_name(l_req_id);
186 FETCH c_get_req_fnd_user_name INTO l_requestor;
187 CLOSE c_get_req_fnd_user_name;
188 -- End : Setting requestor fnd user name
189
190 wf_engine.SetItemAttrText(itemtype,itemkey,'P_REQUESTOR', l_requestor); -- This attr holds the USER name to which the ntification needs to be sent.
191 ELSIF (l_user_id IS NOT NULL) THEN -- For an existing FND user.
192 BEGIN
193 INSERT INTO igp_ac_account_ints (item_key,
194 int_account_id,
195 party_id,
196 acc_classification_code,
197 access_expiration_date,
198 user_id,
199 user_name,
200 created_by,
201 creation_date,
202 last_updated_by,
203 last_update_date,
204 last_update_login)
205 VALUES(
206 l_item_key,
207 igp_ac_account_ints_s.NEXTVAL,
208 l_party_id,
209 l_classification_cd,
210 l_expiration_dt,
211 l_user_id,
212 null,
213 fnd_global.user_id,
214 sysdate,
215 fnd_global.user_id,
216 sysdate,
217 fnd_global.user_id
218 );
219 EXCEPTION
220 WHEN OTHERS THEN
221 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
222 fnd_log.string(fnd_log.level_exception,'igs.plsql.igp_ad_user_id.record_data','For User: ' || l_user_id || ' Exception while recording data in interface table.'||SQLERRM);
223 END IF;
224 resultout := 'COMPLETE:IGP_FAIL';
225 RAISE;
226 END;
227
228 OPEN c_get_fnd_user(l_user_id); -- Get FND user name from user_id for notifications.
229 FETCH c_get_fnd_user INTO l_requestor;
230 CLOSE c_get_fnd_user;
231
232 wf_engine.SetItemAttrText(itemtype,itemkey,'P_USER_NAME', l_requestor);
233 wf_engine.SetItemAttrText(itemtype,itemkey,'P_REQUESTOR', l_requestor);
234
235 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
236 fnd_log.string(fnd_log.level_procedure,'igs.plsql.igp_ad_user_id.record_data','Fetching user name for existing user l_requestor'||l_requestor);
237 END IF;
238
239 END IF;
240 -- Set the message of the approval message based on the parameters.
241 IF (l_org IS NOT NULL) THEN -- ext account request
242 IF (l_expiration_dt IS NOT NULL) THEN
243 fnd_message.set_name('IGS','IGP_AD_EXT_NTF_WTH_EX_DT'); -- ext account request with an exp date.
244 fnd_message.set_token('CONTACT_NAME',Wf_Engine.GetItemAttrText(itemtype,itemkey,'P_PERSON_NAME'));
245 fnd_message.set_token('ORG_NAME',l_org);
246 fnd_message.set_token('EXP_DT',l_expiration_dt);
247 l_message_text := fnd_message.get;
248 ELSE
249 fnd_message.set_name('IGS','IGP_AD_EXT_NTF_WT_EX_DT'); -- ext account request without an exp date.
250 fnd_message.set_token('CONTACT_NAME',Wf_Engine.GetItemAttrText(itemtype,itemkey,'P_PERSON_NAME'));
251 fnd_message.set_token('ORG_NAME',l_org);
252 l_message_text := fnd_message.get;
253 END IF;
254 ELSIF (l_org IS NULL) THEN -- int account.
255 IF (l_expiration_dt IS NOT NULL) THEN
256 fnd_message.set_name('IGS','IGP_AD_INT_NTF_WTH_EX_DT'); -- int account request with an exp date.
257 fnd_message.set_token('PERSON_NAME',Wf_Engine.GetItemAttrText(itemtype,itemkey,'P_PERSON_NAME'));
258 fnd_message.set_token('EXP_DT',l_expiration_dt);
259 fnd_message.set_token('ACC_TYPE',l_classification_cd);
260 l_message_text := fnd_message.get;
261 ELSE
262 fnd_message.set_name('IGS','IGP_AD_INT_NTF_WT_EX_DT'); -- int account request without an exp date.
263 fnd_message.set_token('PERSON_NAME',Wf_Engine.GetItemAttrText(itemtype,itemkey,'P_PERSON_NAME'));
264 fnd_message.set_token('ACC_TYPE',l_classification_cd);
265 l_message_text := fnd_message.get;
266 END IF;
267 END IF;
268 wf_engine.SetItemAttrText(itemtype,itemkey,'P_MESSAGE_TEXT', l_message_text); -- Set the parameter of the workflow to contain the approval message text.
269
270 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
271 fnd_log.string(fnd_log.level_procedure,'igs.plsql.igp_ad_user_id.record_data','l_message_text :'||l_message_text);
272 END IF;
273
274 l_approver:=fnd_profile.value('IGP_ADMIN_WF_APPROVER');
275
276 IF (l_approver IS NULL) THEN
277 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
278 fnd_log.string(fnd_log.level_procedure,'igs.plsql.igp_ad_user_id.record_data','approver not defined');
279 END IF;
280 resultout := 'COMPLETE:IGP_FAIL'; -- Approver not defined.
281 ELSE
282 OPEN c_get_approver_email(l_approver);
283 FETCH c_get_approver_email INTO l_approver_email;
284 CLOSE c_get_approver_email;
285 l_approver_mail_url:='<a href=mailto:'||l_approver_email||'>'||l_approver_email||'</a>';
286 wf_engine.SetItemAttrText(itemtype,itemkey,'P_APPROVER', l_approver); -- Set the value for the approver, picked from profile.
287 wf_engine.SetItemAttrText(itemtype,itemkey,'P_APPROVER_EMAIL', l_approver_mail_url);
288 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
289 fnd_log.string(fnd_log.level_procedure,'igs.plsql.igp_ad_user_id.record_data','approver '||l_approver);
290 END IF;
291 IF (l_classification_cd = 'EXTERNAL') THEN
292 fnd_message.set_name('IGS','IGP_AD_EXTERNAL_NOTE');
293 wf_engine.SetItemAttrText(itemtype,itemkey,'P_ACC_TYPE',fnd_message.get);
294 wf_engine.SetItemAttrText(itemtype,itemkey,'P_ACC_SUB_TYPE',null);
295 ELSE
296 fnd_message.set_name('IGS','IGP_AD_INTERNAL_NOTE');
297 wf_engine.SetItemAttrText(itemtype,itemkey,'P_ACC_TYPE', fnd_message.get);
298 wf_engine.SetItemAttrText(itemtype,itemkey,'P_ACC_SUB_TYPE', l_classification_cd);
299 END IF;
300 select SUBSTR(FND_PROFILE.VALUE('ICX_FORMS_LAUNCHER'),1,INSTR(FND_PROFILE.VALUE('ICX_FORMS_LAUNCHER'),'/',1,3)) INTO l_url_part1 FROM dual;
301 select FND_PROFILE.VALUE('ICX_OA_HTML') INTO l_url_part2 FROM dual;
302 l_url:=l_url_part1||l_url_part2||'/AppsLocalLogin.jsp';
303 l_url:='<a href='||l_url||'>'||l_url||'</a>';
304 wf_engine.SetItemAttrText(itemtype,itemkey,'P_URL', l_url);
305 resultout := 'COMPLETE:IGP_SUCCESS';
306 END IF;
307 END IF; -- for if funcmode='RUN'
308 EXCEPTION
309 WHEN others THEN
310 resultout := 'COMPLETE:IGP_FAIL';
311 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
312 fnd_log.string(fnd_log.level_exception,'igs.plsql.igp_ad_userid_pkg.record_data','Unhandled Exception in Record Data ' ||sqlerrm);
313 END IF;
314 END RECORD_DATA;
315
316 PROCEDURE CHECK_EXISTING_ACCOUNT(itemtype IN VARCHAR2,
317 itemkey IN VARCHAR2,
318 actid IN NUMBER,
319 funcmode IN VARCHAR2,
320 resultout OUT NOCOPY VARCHAR2 )
321 AS
322 /*
323 || Created By : nsidana
324 || Created On : 1/28/2004
325 || Purpose : Checks for the existance of a Portfolio a/c..
326 || Known limitations, enhancements or remarks :
327 || Change History :
328 || Who When What
329 || (reverse chronological order - newest change first)
330 */
331 -- Get the details of
332 CURSOR c_exists_acc(cp_acc_type VARCHAR2,cp_user_id fnd_user.user_id%TYPE)
333 IS
334 SELECT 'Y'
335 FROM igp_ac_accounts ac,
336 igp_ac_acc_classes acc
337 WHERE ac.user_id=cp_user_id AND
338 acc.acc_classification_code=cp_acc_type AND
339 ac.account_id=acc.account_id;
340
341 l_user_id fnd_user.user_id%TYPE;
342 l_acc_type igp_ac_acc_classes.acc_classification_code%TYPE;
343 l_exists VARCHAR2(1);
344
345 BEGIN
346 IF (funcmode = 'RUN') THEN
347 l_exists :='N';
348 l_acc_type := Wf_Engine.GetItemAttrText(itemtype,itemkey,'P_CLASSIFICATION_CD');
349 l_user_id := Wf_Engine.GetItemAttrNumber(itemtype,itemkey,'P_USER_ID');
350 IF (l_user_id IS NOT NULL) THEN
351 l_exists := 'N';
352 OPEN c_exists_acc(l_acc_type,l_user_id);
353 FETCH c_exists_acc INTO l_exists;
354 CLOSE c_exists_acc;
355 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
356 fnd_log.string(fnd_log.level_procedure,'igs.plsql.igp_ad_userid_pkg.check_existing_account','l_exists :'||l_exists);
357 END IF;
358 IF (l_exists = 'Y') THEN
359 resultout := 'COMPLETE:Y';
360 BEGIN
361 DELETE FROM igp_ac_account_ints WHERE item_key=itemkey;
362 EXCEPTION
363 WHEN others THEN
364 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
365 fnd_log.string(fnd_log.level_exception,'igs.plsql.igp_ad_userid_pkg.check_action','While deleting record from Interface table :'||sqlerrm);
366 END IF;
367 END;
368 ELSE
369 resultout := 'COMPLETE:N';
370 END IF;
371 ELSE -- If user id is null. This is for a new FND user account.
372 resultout := 'COMPLETE:N';
373 END IF;
374 END IF; --funcmode
375 EXCEPTION
376 WHEN others THEN
377 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
378 fnd_log.string(fnd_log.level_exception,'igs.plsql.igp_ad_userid_pkg.check_action','Unhandled exception :'||sqlerrm);
379 END IF;
380 END CHECK_EXISTING_ACCOUNT;
381
382 PROCEDURE CHECK_ACTION(itemtype IN VARCHAR2,
383 itemkey IN VARCHAR2,
384 actid IN NUMBER,
385 funcmode IN VARCHAR2,
386 resultout OUT NOCOPY VARCHAR2 )
387 AS
388 /*
389 || Created By : nsidana
390 || Created On : 1/28/2004
391 || Purpose : Checks the p_action and returns the lookup code for lookup type action.
392 || Known limitations, enhancements or remarks :
393 || Change History :
394 || Who When What
395 || (reverse chronological order - newest change first)
396 */
397 l_action VARCHAR2(30);
398
399 BEGIN
400 IF (funcmode = 'RUN') THEN
401 l_action := Wf_Engine.GetItemAttrText(itemtype,itemkey,'P_ACTION');
402 IF (l_action = 'ACCOUNT' ) THEN
403 resultout := 'COMPLETE:ACCOUNT';
404 ELSIF (l_action='BOTH')THEN
405 resultout := 'COMPLETE:BOTH';
406 END IF;
407 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
408 fnd_log.string(fnd_log.level_procedure,'igs.plsql.igp_ad_userid_pkg.check_action','l_action :'||l_action);
409 END IF;
410 END IF;
411 EXCEPTION
412 WHEN others THEN
413 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
414 fnd_log.string(fnd_log.level_exception,'igs.plsql.igp_ad_userid_pkg.check_action','Unhandled exception :'||sqlerrm);
415 END IF;
416 END CHECK_ACTION;
417
418 PROCEDURE VALIDATE_USER_NAME(itemtype IN VARCHAR2,
419 itemkey IN VARCHAR2,
420 actid IN NUMBER,
421 funcmode IN VARCHAR2,
422
423 resultout OUT NOCOPY VARCHAR2 )
424 AS
425 /*
426 || Created By : nsidana
427 || Created On : 1/28/2004
428 || Purpose : Validates the user name and returns YES/NO.
429 || Known limitations, enhancements or remarks :
430 || Change History :
431 || Who When What
432 || (reverse chronological order - newest change first)
433 */
434 l_exists VARCHAR2(1);
435 l_user_name fnd_user.user_name%TYPE;
436 l_num pls_integer;
437 l_message_text VARCHAR2(2000);
438 BEGIN
439 IF (funcmode = 'RUN') THEN
440 l_exists :='N';
441 l_user_name :=NULL;
442 l_message_text := NULL;
443 l_user_name := Wf_Engine.GetItemAttrText(itemtype,itemkey,'P_USER_NAME');
444 l_exists:='N';
445 if FND_SSO_MANAGER.isUserCreateUpdateAllowed() then
446 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
447 fnd_log.string(fnd_log.level_procedure,'igs.plsql.igp_ad_userid_pkg.validate_user_name', ' User create and update allowed.');
448 END IF;
449
450 -- test user already exist, output of testusername
451 --@ USER_OK_CREATE constant pls_integer := 0;
452 --@ USER_INVALID_NAME constant pls_integer := 1;
453 --@ USER_EXISTS_IN_FND constant pls_integer := 2;
454 --@ USER_SYNCHED constant pls_integer := 3;
455 --@ USER_EXISTS_NO_LINK_ALLOWED constant pls_integer := 4;
456 l_num := fnd_user_pkg.testusername(x_user_name=>l_user_name);
457 if l_num = 0 then
458 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
459 fnd_log.string(fnd_log.level_procedure,'igs.plsql.igp_ad_userid_pkg.validate_user_name','l_exists :'||l_exists);
460 END IF;
461 resultout := 'COMPLETE:Y'; -- No FND user with this name exists.
462 elsif l_num = 1 then
463 fnd_message.set_name('IGS','IGP_AD_INVALID_USR_NAME'); -- Invalid user name
464 fnd_message.set_token('USERNAME',l_user_name);
465 l_message_text := fnd_message.get;
466
467 resultout := 'COMPLETE:N'; -- user name does not comply user name policy
468 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
469 fnd_log.string(fnd_log.level_procedure,'igs.plsql.igp_ad_userid_pkg.validate_user_name','l_user_name :'||l_user_name || ' does not comply user name policy.');
470 END IF;
471 elsif l_num = 2 then
472 fnd_message.set_name('IGS','IGP_AD_USR_ALREADY_REGISTERED'); -- User already registered in fnd
473 fnd_message.set_token('USERNAME',l_user_name);
474 l_message_text := fnd_message.get;
475
476 resultout := 'COMPLETE:N'; -- user name already exist in fnd_user.
477 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
478 fnd_log.string(fnd_log.level_procedure,'igs.plsql.igp_ad_userid_pkg.validate_user_name','l_user_name :'||l_user_name || ' user name already exist in fnd_user.');
479 END IF;
480 else -- if l_num = 3 or 4
481 fnd_message.set_name('IGS','IGP_AD_USR_ALREADY_REG_IN_OID'); -- User already registered in OID
482 fnd_message.set_token('USERNAME',l_user_name);
483 l_message_text := fnd_message.get;
484 resultout := 'COMPLETE:N'; -- user name is already in use with Oracle Internet Directory.
485 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
486 fnd_log.string(fnd_log.level_procedure,'igs.plsql.igp_ad_userid_pkg.validate_user_name','l_user_name :'||l_user_name||' is already in use with Oracle Internet Directory.');
487
488 END IF;
489 end if; -- FOR l_num = 0
490 else
491 l_num := -1;
492
493 fnd_message.set_name('IGS','IGP_AD_USR_CRT_UPD_NOT_ALOWED'); -- Creation or updating of a user is not allowed.
494 l_message_text := fnd_message.get;
495 resultout := 'COMPLETE:N'; -- Creation or updating of a user is not allowed.
496
497 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
498 fnd_log.string(fnd_log.level_procedure,'igs.plsql.igp_ad_userid_pkg.validate_user_name',' Creation or updating of a user is not allowed.');
499 END IF;
500 end if; -- FOR FND_SSO_MANAGER.isUserCreateUpdateAllowed()
501
502 -- clean up the record from igp_ac_account_ints
503 if l_num <> 0 then
504 BEGIN
505 DELETE FROM igp_ac_account_ints WHERE item_key=itemkey;
506 EXCEPTION
507 WHEN others THEN
508 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
509 fnd_log.string(fnd_log.level_exception,'igs.plsql.igp_ad_userid_pkg.validate_user_name','While deleting record from Interface table :'||sqlerrm);
510 END IF;
511 END;
512 end if;
513
514 wf_engine.SetItemAttrText(itemtype,itemkey,'P_MESSAGE_TEXT', l_message_text); -- Set the parameter of the workflow to contain the reason of not completing the create portfolio request.
515
516
517 END IF;
518 EXCEPTION
519 WHEN others THEN
520 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
521 fnd_log.string(fnd_log.level_exception,'igs.plsql.igp_ad_userid_pkg.validate_user_name','Unhandled exception :'||sqlerrm);
522 END IF;
523 END VALIDATE_USER_NAME;
524
525 PROCEDURE CREATE_FND_USER(itemtype IN VARCHAR2,
526 itemkey IN VARCHAR2,
527 actid IN NUMBER,
528 funcmode IN VARCHAR2,
529 resultout OUT NOCOPY VARCHAR2 )
530 AS
531 /*
532 || Created By : nsidana
533 || Created On : 1/28/2004
534 || Purpose : Procedure to create a new FND user.
535 || Known limitations, enhancements or remarks :
536 || Change History :
537 || Who When What
538 || (reverse chronological order - newest change first)
539 */
540
541 l_user_id fnd_user.user_id%TYPE;
542 l_user_name fnd_user.user_name%TYPE;
543 l_email_address fnd_user.email_address%TYPE;
544 l_party_id hz_parties.party_id%TYPE;
545 l_return_status VARCHAR2(1);
546 l_msg_count NUMBER;
547 l_msg_data VARCHAR2(2000);
548 l_user_password VARCHAR2(100);
549 l_classification_cd igp_ac_acc_classes.acc_classification_code%TYPE;
550 l_expiration_dt DATE;
551 l_resp_exists VARCHAR2(1);
552
553 BEGIN
554 IF (funcmode = 'RUN') THEN
555 l_resp_exists :='N';
556 -- Extract the attributes from the workflow. These are required to create the FND user.
557 l_party_id := wf_engine.GetItemAttrNumber(itemtype,itemkey,'P_PARTY_ID' );
558 l_user_name := wf_engine.GetItemAttrText(itemtype,itemkey,'P_USER_NAME' );
559 l_email_address := wf_engine.GetItemAttrText(itemtype,itemkey,'P_EMAIL_ADDRESS' );
560 l_user_password := wf_engine.GetItemAttrText(itemtype,itemkey,'P_PASSWORD' );
561 --Validate the password also.
562 IF (l_user_password IS NULL) OR (length(l_user_password)<5) THEN
563 l_user_password:=GENERATE_PASSWORD(l_party_id);
564 Wf_Engine.SetItemAttrText(itemtype,itemkey,'P_PASSWORD',l_user_password);
565 END IF;
566 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
567 fnd_log.string(fnd_log.level_procedure,'igs.plsql.igp_ad_userid_pkg.create_fnd_user','Creating user with values :'||l_party_id||' '||l_user_name||' '||l_email_address);
568 END IF;
569 -- Now, call FND package to create the FND user.
570 begin
571 fnd_user_pkg.CreateUser (
572 x_user_name => l_user_name,
573 x_owner => '',
574 x_session_number => '0',
575 x_start_date => sysdate,
576 x_unencrypted_password => l_user_password,
577 x_email_address => l_email_address,
578 x_password_date => sysdate,
579 x_customer_id => l_party_id);
580
581 select USER_ID into l_user_id
582 from FND_USER
583 where USER_NAME = l_user_name;
584
585 wf_engine.SetItemAttrNumber(itemtype,itemkey,'P_USER_ID', l_user_id); -- set the FND user ID. To be used later.
586 wf_engine.SetItemAttrText(itemtype,itemkey,'P_REQUESTOR', l_user_name);
587 resultout := 'COMPLETE:IGP_SUCCESS';
588 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
589 fnd_log.string(fnd_log.level_procedure,'igs.plsql.igp_ad_userid_pkg.create_fnd_user','l_user_id :'||l_user_id);
590 fnd_log.string(fnd_log.level_procedure,'igs.plsql.igp_ad_userid_pkg.create_fnd_user','l_user_name :'||l_user_name);
591 END IF;
592 exception
593 when others then
594 delete from igp_ac_account_ints where item_key= itemkey;
595 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
596 fnd_log.string(fnd_log.level_procedure,'igs.plsql.igp_ad_userid_pkg.create_fnd_user','error while creating FND user.');
597 END IF;
598 resultout := 'COMPLETE:IGP_FAIL';
599 end;
600
601 END IF; -- for funcmode='RUN'
602 EXCEPTION
603 WHEN others THEN
604 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
605 fnd_log.string(fnd_log.level_exception,'igs.plsql.igp_ad_userid_pkg.create_fnd_user','Unhandled Exception :'||sqlerrm);
606 END IF;
607 resultout := 'COMPLETE:IGP_FAIL';
608 END CREATE_FND_USER;
609
610 PROCEDURE CREATE_PORT_ACCOUNT(itemtype IN VARCHAR2,
611 itemkey IN VARCHAR2,
612 actid IN NUMBER,
613 funcmode IN VARCHAR2,
614 resultout OUT NOCOPY VARCHAR2 )
615 AS
616 /*
617 || Created By : nsidana
618 || Created On : 1/28/2004
619 || Purpose : Procedure to create a new Portfolio account.
620 || Known limitations, enhancements or remarks :
621 || Change History :
622 || Who When What
623 || (reverse chronological order - newest change first)
624 */
625
626 -- Get the details of responsibilities to be attached to the FND user because of a classification cd.
627 -- the resp would be different for IGS/IGP, All IGP resp would have TAG= IGP.
628 CURSOR c_get_resp(cp_class_cd VARCHAR2) IS
629 SELECT responsibility_id
630 FROM igp_as_resp_mappings
631 WHERE acc_classification_code=cp_class_cd
632 AND tag='IGP'
633 AND enable_flag='Y';
634
635 -- Cursor to check if a resp is already attached to the user.
636 CURSOR c_chk_resp(cp_user_id NUMBER,cp_resp NUMBER) IS
637 SELECT b.responsibility_id,b.end_date
638 FROM fnd_user a,
639 fnd_user_resp_groups_direct b
640 WHERE a.user_id=cp_user_id AND
641 a.user_id=b.user_id AND
642 b.responsibility_id=cp_resp;
643
644 -- Get the details of portfolio user.
645 CURSOR c_is_port_user(cp_party_id VARCHAR2) IS
646 SELECT account_id
647 FROM igp_ac_accounts a
648 WHERE party_id=cp_party_id;
649
650 CURSOR c_get_user_id(cp_user_name VARCHAR2) IS
651 SELECT user_id
652 FROM fnd_user
653 WHERE user_name=cp_user_name;
654
655 CURSOR c_get_resp_desc(cp_resp_id NUMBER) IS
656 SELECT description
657 FROM fnd_responsibility_tl
658 WHERE responsibility_id=cp_resp_id AND
659 language = USERENV('LANG');
660
661 l_resp NUMBER;
662 l_user_name fnd_user.user_name%TYPE;
663 l_user_id fnd_user.user_id%TYPE;
664 l_fnd_user_id fnd_user.user_id%TYPE;
665 l_party_id hz_parties.party_id%TYPE;
666 l_classification_cd igp_ac_acc_classes.acc_classification_code%TYPE;
667 l_account_id igp_ac_accounts.account_id%TYPE;
668 l_expiration_dt DATE;
669 l_desc fnd_responsibility_tl.description%TYPE;
670 l_exists VARCHAR2(1);
671 lv_rowid VARCHAR2(30);
672 l_acc_classification_id igp_ac_acc_classes.acc_classification_id%TYPE;
673 l_fnd_resp_end_dt DATE;
674 c_get_resp_rec c_get_resp%ROWTYPE;
675 c_chk_resp_rec c_chk_resp%ROWTYPE;
676
677 BEGIN
678 IF (funcmode = 'RUN') THEN
679 l_exists :='N';
680 l_party_id := wf_engine.GetItemAttrNumber(itemtype,itemkey,'P_PARTY_ID' ); -- HZ party ID.
681 l_fnd_user_id := wf_engine.GetItemAttrNumber(itemtype,itemkey,'P_USER_ID' ); -- FND user ID.
682 l_classification_cd := wf_engine.GetItemAttrText(itemtype,itemkey,'P_CLASSIFICATION_CD' ); -- Portfolio acc classification code...STAFF,FACULTY,STUDENT.
683 l_expiration_dt := wf_engine.GetItemAttrDate(itemtype,itemkey,'P_EXPIRATION_DT' ); -- Access expiration date.
684
685 -- check if already a Portfolio user.
686 l_exists:='N';
687 l_account_id:=null;
688 OPEN c_is_port_user(l_party_id);
689 FETCH c_is_port_user INTO l_account_id;
690 CLOSE c_is_port_user;
691
692 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
693 fnd_log.string(fnd_log.level_procedure,'igs.plsql.igp_ad_userid_pkg.create_port_account','Existing account ID is l_account_id :'||l_account_id);
694 END IF;
695
696 IF (l_account_id IS NOT NULL) THEN
697 wf_engine.SetItemAttrNumber(itemtype,itemkey,'P_ACCOUNT_ID', l_account_id); -- Portfolio a/c exists: Set the value of the account ID in the WF.
698 ELSIF (l_account_id IS NULL) THEN --Call the TBH of the IGP_AC_ACCOUNTS table to insert into the table.
699 BEGIN
700 igp_ac_accounts_pkg.insert_row (
701 x_mode => 'R',
702 x_rowid => lv_rowid, -- OUT param
703 x_account_id => l_account_id, -- OUT param
704 x_party_id => l_party_id, -- HZ party ID.
705 x_user_id => l_fnd_user_id, -- FND user ID.
706 x_object_version_number => 1 -- OVN is always 1 for a newly created record.
707 );
708 EXCEPTION
709 WHEN others THEN
710 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
711 fnd_log.string(fnd_log.level_exception,'igs.plsql.igp_ad_userid_pkg.create_port_account','Unhandled exception.');
712 END IF;
713 END;
714 wf_engine.SetItemAttrNumber(itemtype,itemkey,'P_ACCOUNT_ID', l_account_id); -- Collect the account_id for the new a/c created and set the value in WF.
715 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
716 fnd_log.string(fnd_log.level_procedure,'igs.plsql.igp_ad_userid_pkg.create_port_account','Newly created account ID : l_account_id :'||l_account_id);
717 END IF;
718 END IF;
719
720 FOR c_get_resp_rec IN c_get_resp(l_classification_cd)
721 LOOP -- For each resp, check if its already attached.
722 l_exists:='N';
723 c_chk_resp_rec:=null;
724 OPEN c_chk_resp(l_fnd_user_id,c_get_resp_rec.responsibility_id);
725 FETCH c_chk_resp INTO c_chk_resp_rec;
726 CLOSE c_chk_resp;
727
728 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
729 fnd_log.string(fnd_log.level_procedure,'igs.plsql.igp_ad_userid_pkg.create_port_account','Resp Id :'||c_chk_resp_rec.responsibility_id);
730 END IF;
731
732 IF (c_chk_resp_rec.responsibility_id IS NOT NULL) THEN --check if we need to update the end date of the FND resp.
733 l_fnd_resp_end_dt:=c_chk_resp_rec.end_date;
734 IF (l_fnd_resp_end_dt IS NOT NULL) THEN
735 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
736 fnd_log.string(fnd_log.level_procedure,'igs.plsql.igp_ad_userid_pkg.create_port_account','Resp end date :'||l_fnd_resp_end_dt);
737 END IF;
738 IF (l_expiration_dt IS NULL) THEN -- Update the FNd resp to be not end dated,.
739 OPEN c_get_resp_desc(c_get_resp_rec.responsibility_id); -- get the desc of the resp. reqd for API call.
740 FETCH c_get_resp_desc INTO l_desc;
741 CLOSE c_get_resp_desc;
742 fnd_user_resp_groups_api.update_assignment(
743 user_id => l_fnd_user_id, -- FND user ID.
744 responsibility_id =>c_get_resp_rec.responsibility_id, -- RESP ID.
745 responsibility_application_id =>8405,
746 security_group_id => 0,
747 start_date => sysdate,
748 end_date => l_expiration_dt, -- Expiration date.
749 description =>l_desc -- Description.
750 );
751
752
753 ELSIF (l_expiration_dt IS NOT NULL) THEN
754 IF (l_expiration_dt > l_fnd_resp_end_dt) THEN --Update the new FND resp end date.
755 OPEN c_get_resp_desc(c_get_resp_rec.responsibility_id); -- get the desc of the resp. reqd for API call.
756 FETCH c_get_resp_desc INTO l_desc;
757 CLOSE c_get_resp_desc;
758 BEGIN
759 fnd_user_resp_groups_api.update_assignment(
760 user_id => l_fnd_user_id, -- FND user ID.
761 responsibility_id =>c_get_resp_rec.responsibility_id, -- RESP ID.
762 responsibility_application_id =>8405,
763 security_group_id => 0,
764 start_date => sysdate,
765 end_date => l_expiration_dt, -- Expiration date.
766 description =>l_desc -- Description.
767 );
768 EXCEPTION
769 WHEN OTHERS THEN
770 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
771 fnd_log.string(fnd_log.level_exception,'igs.plsql.igp_ad_userid_pkg.create_port_account','Unhandled exception while creating user..');
772 END IF;
773 END;
774 END IF;
775 END IF;
776 END IF;
777 ELSIF (c_chk_resp_rec.responsibility_id IS NULL) THEN -- Attach the resp. Call the pkg fnd_user_resp_groups_api.
778 OPEN c_get_resp_desc(c_get_resp_rec.responsibility_id); -- get the desc of the resp. reqd for API call.
779 FETCH c_get_resp_desc INTO l_desc;
780 CLOSE c_get_resp_desc;
781
782 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
783 fnd_log.string(fnd_log.level_procedure,'igs.plsql.igp_ad_userid_pkg.create_port_account','Resp desc for new resp :'||l_desc);
784 END IF;
785 fnd_user_resp_groups_api.insert_assignment(
786 user_id => l_fnd_user_id, -- FND user ID.
787 responsibility_id =>c_get_resp_rec.responsibility_id, -- RESP ID.
788 responsibility_application_id =>8405,
789 security_group_id => 0,
790 start_date => sysdate,
791 end_date => l_expiration_dt, -- Expiration date.
792 description =>l_desc -- Description.
793 );
794 END IF;
795 END LOOP;
796 -- Insert a record in IGP_AC_ACC_CLASSES table.
797 l_account_id:= wf_engine.GetItemAttrNumber(itemtype,itemkey,'P_ACCOUNT_ID');
798
799 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
800 fnd_log.string(fnd_log.level_procedure,'igs.plsql.igp_ad_userid_pkg.create_port_account','Account ID :'||l_account_id);
801 END IF;
802
803 lv_rowid:=NULL;
804 BEGIN
805 igp_ac_acc_classes_pkg.insert_row ( x_mode => 'R',
806 x_rowid => lv_rowid,
807 x_acc_classification_id => l_acc_classification_id, -- PK of the table. OUT param.
808 x_account_id => l_account_id , -- FK to the account ID.
809 x_acc_classification_code => l_classification_cd, -- Acc classification code.
810 x_access_expiration_date => l_expiration_dt, -- Access expiration date.
811 x_object_version_number => 1
812 );
813 -- Delete from interface table.
814 DELETE FROM igp_ac_account_ints
815 WHERE party_id=l_party_id
816 AND acc_classification_code=l_classification_cd;
817 EXCEPTION
818 WHEN OTHERS THEN
819 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
820 fnd_log.string(fnd_log.level_exception,'igs.plsql.igp_ad_userid_pkg.create_port_account','Unhandled exception while creating user..');
821 END IF;
822 END;
823 resultout:='COMPLETE';
824 END IF;
825 EXCEPTION
826 WHEN OTHERS THEN
827 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
828 fnd_log.string(fnd_log.level_exception,'igs.plsql.igp_ad_userid_pkg.create_port_account','Exception :'||sqlerrm);
829 END IF;
830 END CREATE_PORT_ACCOUNT;
831
832 FUNCTION GENERATE_PASSWORD (p_party_id NUMBER) RETURN VARCHAR2
833 IS
834 BEGIN
835 DBMS_LOCK.SLEEP(1);
836 return fnd_crypto.RandomString(10);
837 END GENERATE_PASSWORD;
838
839 PROCEDURE SET_DATA(itemtype IN VARCHAR2,
840 itemkey IN VARCHAR2,
841 actid IN NUMBER,
842 funcmode IN VARCHAR2,
843 resultout OUT NOCOPY VARCHAR2 )
844 IS
845 CURSOR c_get_per_details(cp_user_id NUMBER) IS
846 SELECT hz.party_name,hz.party_number
847 FROM hz_parties hz,igp_ac_accounts ac
848 WHERE ac.user_id=cp_user_id AND
849 ac.party_id=hz.party_id;
850
851 CURSOR c_get_fnd_user(cp_user_id NUMBER) IS
852 SELECT user_name
853 FROM fnd_user
854 WHERE user_id=cp_user_id;
855
856 CURSOR c_get_requestor_det(cp_req_id NUMBER) IS
857 SELECT hz.person_last_name||', '||hz.person_first_name req_name,
858 fu.email_address req_email
859 FROM
860 hz_parties hz,
861 fnd_user fu,
862 igp_ac_Accounts acc
863 WHERE
864 hz.party_id=cp_req_id AND
865 hz.party_id=acc.party_id AND
866 acc.user_id=fu.user_id;
867
868
869 l_account_id NUMBER;
870 l_user_id fnd_user.user_id%TYPE;
871 l_classcode igp_ac_acc_classes.acc_classification_code%TYPE;
872 l_expiry_dt DATE;
873 l_user_name fnd_user.user_name%TYPE;
874 c_get_per_details_rec c_get_per_details%ROWTYPE;
875 l_requestor_id hz_parties.party_id%TYPE;
876 l_requestor_name hz_parties.party_name%TYPE;
877 l_requestor_email fnd_user.email_address%TYPE;
878 l_href_mailto VARCHAR2(1000);
879 c_get_requestor_det_rec c_get_requestor_det%ROWTYPE;
880
881
882 BEGIN
883 IF (funcmode = 'RUN') THEN
884 l_account_id := wf_engine.GetItemAttrNumber(itemtype,itemkey,'P_ACCOUNT_ID' );
885 l_user_id := wf_engine.GetItemAttrNumber(itemtype,itemkey,'P_USER_ID' );
886 l_classcode := wf_engine.GetItemAttrText(itemtype,itemkey,'P_CLASS_CODE' );
887 l_expiry_dt := wf_engine.GetItemAttrDate(itemtype,itemkey,'P_EXPIRY_DATE' );
888 l_requestor_id := wf_engine.GetItemAttrText(itemtype,itemkey,'P_REQUESTOR' );
889
890 OPEN c_get_requestor_det(l_requestor_id);
891 FETCH c_get_requestor_det INTO c_get_requestor_det_rec;
892 CLOSE c_get_requestor_det;
893
894 l_href_mailto :='<a href=mailto:'||c_get_requestor_det_rec.req_email||'</a>'||c_get_requestor_det_rec.req_email;
895
896 wf_engine.SetItemAttrText(itemtype,itemkey,'P_REQUESTOR_NAME',c_get_requestor_det_rec.req_name);
897 wf_engine.SetItemAttrText(itemtype,itemkey,'P_REQUESTOR_EMAIL',l_href_mailto);
898
899 IF (l_classcode = 'EXTERNAL') THEN
900 fnd_message.set_name('IGS','IGP_AD_EXTERNAL_NOTE');
901 wf_engine.SetItemAttrText(itemtype,itemkey,'P_ACC_TYPE',fnd_message.get);
902 ELSE
903 fnd_message.set_name('IGS','IGP_AD_INTERNAL_NOTE');
904 wf_engine.SetItemAttrText(itemtype,itemkey,'P_ACC_TYPE',fnd_message.get);
905 wf_engine.SetItemAttrText(itemtype,itemkey,'P_ACC_SUB_TYPE',l_classcode);
906 END IF;
907
908 OPEN c_get_per_details(l_user_id);
909 FETCH c_get_per_details INTO c_get_per_details_rec;
910 CLOSE c_get_per_details;
911
912 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
913 fnd_log.string(fnd_log.level_procedure,'igs.plsql.igp_ad_userid_pkg.set_data','person details Name :'||c_get_per_details_rec.party_name);
914 fnd_log.string(fnd_log.level_procedure,'igs.plsql.igp_ad_userid_pkg.set_data','person details Number :'||c_get_per_details_rec.party_number);
915 END IF;
916
917 wf_engine.SetItemAttrText(itemtype,itemkey,'P_PERSON_NAME', c_get_per_details_rec.party_name);
918 wf_engine.SetItemAttrText(itemtype,itemkey,'P_PERSON_NUMBER', c_get_per_details_rec.party_number);
919
920 OPEN c_get_fnd_user(l_user_id);
921 FETCH c_get_fnd_user INTO l_user_name;
922 CLOSE c_get_fnd_user;
923
924 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
925 fnd_log.string(fnd_log.level_procedure,'igs.plsql.igp_ad_userid_pkg.set_data','User Name :'||l_user_name);
926 END IF;
927
928 wf_engine.SetItemAttrText(itemtype,itemkey,'P_EMAIL_ADDRESS', l_user_name);
929 wf_engine.SetItemAttrText(itemtype,itemkey,'P_USER_NAME',l_user_name);
930
931 END IF;
932 END SET_DATA;
933
934 PROCEDURE CLEANUP(itemtype IN VARCHAR2,
935 itemkey IN VARCHAR2,
936 actid IN NUMBER,
937 funcmode IN VARCHAR2,
938 resultout OUT NOCOPY VARCHAR2 )
939 AS
940 BEGIN
941 IF (funcmode = 'RUN') THEN
942 BEGIN
943 DELETE FROM igp_ac_account_ints WHERE item_key=itemkey;
944 EXCEPTION
945 WHEN others THEN
946 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
947 fnd_log.string(fnd_log.level_exception,'igs.plsql.igp_ad_userid_pkg.cleanup','While deleting record from Interface table :'||sqlerrm);
948 END IF;
949 END;
950 END IF;
951 EXCEPTION
952 WHEN OTHERS THEN
953 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
954 fnd_log.string(fnd_log.level_exception,'igs.plsql.igp_ad_userid_pkg.cleanup','Unhandled exception while deleting record from Interface table :'||sqlerrm);
955 END IF;
956 END CLEANUP;
957 END igp_ad_userid_pkg;
[Home] [Help]