[Home] [Help]
PACKAGE BODY: APPS.IBU_DYN_USER_GROUPS_PKG
Source
1 Package Body IBU_DYN_USER_GROUPS_PKG AS
2 /* $Header: ibudyugb.pls 120.3 2007/12/18 11:23:37 mpathani ship $ */
3 -- --------- ------ ------------------------------------------
4 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
5 G_LOGIN_ID NUMBER := FND_GLOBAL.LOGIN_ID;
6
7 -- =================================================================================================================
8 Procedure Log_Message(p_Message IN VARCHAR2)
9 IS
10 now VARCHAR2(60);
11 Begin
12 --select to_char(sysdate, 'mm-dd-yyyy hh24:mi:ss') into now from dual;
13 --dbms_output.put_line( p_Message || ' ' || now);
14 --FND_FILE.PUT_LINE(FND_FILE.LOG, p_Message || ' ' || now);
15 FND_FILE.PUT_LINE(FND_FILE.LOG, p_Message);
16 --insert into ibu_usrgrp_results values (ibu_usrgrp_results_s.nextval , p_Message, now);
17 End;
18
19 -- =================================================================================================================
20 Procedure Status_Log_Message(p_Message IN VARCHAR2, p_return_status varchar2, p_msg_count number, p_msg_data varchar2 )
21 IS
22 now VARCHAR2(60);
23 Begin
24 Log_Message( p_Message);
25 Log_Message( 'Return Status = ' || p_return_status || ' ;Msg count ' || p_msg_count);
26 FOR i IN 1 .. p_msg_count LOOP
27 Log_Message( ' Fnd Msg in loop = ' || fnd_msg_pub.get(i, 'F'));
28 END LOOP;
29 --Log_Message( ' Msg data ' || p_msg_data );
30 FND_MSG_PUB.Initialize;
31 End;
32 -- =================================================================================================================
33 PROCEDURE get_curr_usr_resouce_info
34 (
35 x_resource_id OUT NOCOPY jtf_rs_resource_extns.resource_id%TYPE,
36 x_resource_number OUT NOCOPY jtf_rs_resource_extns.resource_number%TYPE
37 )
38 IS
39 l_current_user_id NUMBER := FND_GLOBAL.User_Id;
40
41 -- Temporary variables
42 l_creby_source_id jtf_rs_resource_extns.source_id%TYPE;
43 l_creby_empid fnd_user.employee_id%TYPE;
44 l_creby_custid fnd_user.customer_id%TYPE;
45 l_creby_supid fnd_user.supplier_id%TYPE;
46 l_creby_category jtf_rs_resource_extns.category%TYPE;
47 l_creby_res_cnt NUMBER;
48 l_category varchar2(50);
49
50 Begin
51 begin
52 select employee_id, customer_id, supplier_id
53 into l_creby_empid, l_creby_custid, l_creby_supid
54 from fnd_user
55 where user_id = l_current_user_id;
56
57 If l_creby_empid is not null Then
58 l_creby_category := 'EMPLOYEE';
59 l_creby_source_id := l_creby_empid ;
60 ElsIf l_creby_custid is not null Then
61 l_creby_category := 'PARTY';
62 l_creby_source_id := l_creby_custid ;
63 ElsIf l_creby_supid is not null Then
64 l_creby_category := 'SUPPLIER_CONTACT';
65 l_creby_source_id := l_creby_supid ;
66 End If;
67 exception
68 When no_data_found then
69 l_creby_category := 'CREATED_BY_UNKNOWN';
70 l_creby_source_id := 0;
71 When others then
72 raise;
73 end;
74 begin
75 select resource_id, resource_number
76 into x_resource_id, x_resource_number
77 from jtf_rs_resource_extns
78 where source_id = l_creby_source_id
79 and category = l_creby_category
80 and ( (end_date_active is null) or (end_date_active > sysdate) );
81 exception
82 When no_data_found then
83 x_resource_id := 0;
84 x_resource_number := 0;
85 When others then
86 raise;
87 end;
88 END;
89 -- =================================================================================================================
90 -- For a given source_id, it returns count>0 if atleast there is one fnd_user record which is not end_dated.
91 Procedure GetFndUserActiveCnt (p_source_id IN jtf_rs_resource_extns.source_id%TYPE,
92 p_category IN jtf_rs_resource_extns.category%TYPE,
93 x_Cnt OUT NOCOPY NUMBER )
94 IS
95 Begin
96 If p_category = 'EMPLOYEE' Then
97 select count(*) into x_cnt
98 from fnd_user
99 where employee_id = p_source_id
100 and ( ( end_date is null) or (end_date > sysdate) ) ;
101 ElsIf p_category = 'PARTY' Then
102 select max(user_id) into x_cnt
103 from fnd_user
104 where customer_id = p_source_id
105 and ( ( end_date is null) or (end_date > sysdate) ) ;
106 ElsIf p_category = 'SUPPLIER_CONTACT' Then
107 select max(user_id) into x_cnt
108 from fnd_user
109 where supplier_id = p_source_id
110 and ( ( end_date is null) or (end_date > sysdate) ) ;
111 Else
112 x_cnt := 0;
113 End If;
114
115 End;
116 -- =================================================================================================================
117 Procedure Get_UserID (p_source_id IN jtf_rs_resource_extns.source_id%TYPE,
118 p_category IN jtf_rs_resource_extns.category%TYPE,
119 x_user_id OUT NOCOPY fnd_user.user_id%TYPE )
120 IS
121 l_cnt NUMBER;
122 Begin
123 -- Find out user_id for the source_id, we need to have a link for user_id, source_id, category in jtf_rs_resource_extns table
124 If p_category = 'EMPLOYEE' Then
125 select max(user_id) into x_user_id
126 from fnd_user
127 where employee_id = p_source_id;
128 ElsIf p_category = 'PARTY' Then
129 select max(user_id) into x_user_id
130 from fnd_user
131 where customer_id = p_source_id;
132 ElsIf p_category = 'SUPPLIER_CONTACT' Then
133 select max(user_id) into x_user_id
134 from fnd_user
135 where supplier_id = p_source_id;
136 Else
137 x_user_id := 0;
138 End If;
139 End;
140 -- =================================================================================================================
141 Procedure Get_Category (p_sql_text IN varchar2, x_category OUT NOCOPY varchar2)
142 IS
143 l_q_str jtf_rs_dynamic_groups_vl.sql_text%TYPE;
144 l_num NUMBER;
145 l_party_num NUMBER;
146 l_emp_num NUMBER;
147 l_substring VARCHAR2(2000);
148 Begin
149 Log_Message('Query in Get_Category = { ' || p_sql_text || ' } ');
150
151 SELECT UPPER(p_sql_text) into l_q_str from DUAL;
152
153 SELECT INSTR (l_q_str, 'FROM', 1, 1) INTO l_num FROM DUAL;
154 SELECT SUBSTR (l_q_str, 1, l_num) INTO l_substring FROM DUAL;
155
156 SELECT INSTR (l_substring, 'PARTY_ID', 1, 1) INTO l_party_num FROM DUAL;
157 IF (l_party_num <> 0) THEN
158 x_category := 'PARTY';
159 Else
160 SELECT INSTR (l_substring, 'EMPLOYEE_ID', 1, 1) INTO l_emp_num FROM DUAL;
161 IF (l_emp_num <> 0) THEN
162 x_category := 'EMPLOYEE';
163 Else
164 x_category := 'UNKNOWN';
165 END IF;
166 END IF;
167 Log_Message('Category output = ' || x_category );
168 End ;
169
170 -- =================================================================================================================
171
172 Procedure Insert_Temp_Table (p_sql_text varchar2, p_category varchar2)
173 IS
174 cid INTEGER;
175 l_ins_qry varchar2(2000);
176 l_cnt NUMBER;
177 l_rows_processed NUMBER;
178
179 l_q_str varchar2(2000);
180 l_first_substring varchar2(2000);
181 l_last_substring varchar2(2000);
182
183 l_num NUMBER;
184 l_length NUMBER;
185 BEGIN
186
187 SELECT UPPER(p_sql_text) into l_q_str from DUAL;
188 SELECT INSTR (l_q_str, 'FROM', 1, 1) INTO l_num FROM DUAL;
189 SELECT SUBSTR (l_q_str, 1, l_num-1) INTO l_first_substring FROM DUAL;
190 SELECT length (l_q_str) INTO l_length FROM DUAL;
191 --SELECT SUBSTR (l_q_str, l_num, l_length) INTO l_last_substring FROM DUAL;
192 -- For case sensitive queries
193 SELECT SUBSTR (p_sql_text, l_num, l_length) INTO l_last_substring FROM DUAL;
194
195 l_first_substring := l_first_substring || ' , ' || '''' || p_category || '''' || ' , sysdate, ' || FND_GLOBAL.USER_ID || ' , sysdate, ' || FND_GLOBAL.USER_ID || ' ' ;
196 l_q_str := l_first_substring || l_last_substring;
197 l_ins_qry := 'insert into ibu_usergroups_temp(source_id, category,creation_date, created_by, last_update_date, last_updated_by ) ';
198 l_ins_qry := l_ins_qry || l_q_str;
199 Log_Message(' Insert Temp table query = { ' || l_ins_qry || ' } ');
200
201 cid := dbms_sql.open_cursor;
202
203 -- Fixed Bug# 6641845
204 -- Delete the old records before inserting the new records in table ibu_usergroups_temp
205
206 dbms_sql.parse(cid, 'delete from ibu_usergroups_temp', dbms_sql.v7);
207 l_rows_processed := dbms_sql.execute(cid);
208 Log_Message(' Insert Temp table rows deleted = ' || l_rows_processed);
209
210 dbms_sql.parse(cid, l_ins_qry, dbms_sql.v7);
211 l_rows_processed := dbms_sql.execute(cid);
212 Log_Message(' Insert Temp table rows inserted = ' || l_rows_processed);
213
214 --dbms_sql.parse(cid, 'commit' , dbms_sql.v7);
215 dbms_sql.close_cursor(cid);
216
217 select count(*) into l_cnt from ibu_usergroups_temp;
218 Log_Message('Count in ibu_usergroups_temp = ' || to_char(l_cnt) );
219 EXCEPTION
220 WHEN OTHERS THEN
221 Log_Message('Error in Insert_Temp_Table' ||TO_CHAR(SQLCODE)||': '||SQLERRM );
222 dbms_sql.close_cursor(cid);
223 raise;
224 End ;
225
226 -- =================================================================================================================
227 Procedure Update_Category_SourceID (p_category varchar2)
228 Is
229 l_emp_id fnd_user.employee_id%TYPE;
230
231 Cursor l_source_csr IS
232 select source_id
233 from ibu_usergroups_temp ;
234 BEGIN
235 -- Some of the customers could be valid employees, so update the category column to 'EMPLOYEE' for those customers and change the sourcE_id to employee_id instead of customer_id.
236 If p_category = 'PARTY' Then
237 For l_source_rec in l_source_csr Loop
238
239 select max(employee_id) into l_emp_id
240 from fnd_user A
241 Where A.customer_id = l_source_rec.source_id
242 and A.employee_id is not null
243 and exists ( select person_id
244 from per_workforce_current_x
245 Where person_id = A.employee_id)
246 and ( (A.end_date is null) OR (A.end_date > sysdate) );
247
248 If l_emp_id is not null Then
249 Update ibu_usergroups_temp
250 Set Category = 'EMPLOYEE', Source_id = l_emp_id
251 Where source_id = l_source_rec.source_id;
252 End If;
253
254 End Loop;
255 End If;
256 EXCEPTION
257 WHEN OTHERS THEN
258 Log_Message('Error in Update_Category_SourceID' ||TO_CHAR(SQLCODE)||': '||SQLERRM );
259 raise;
260 End ;
261
262 -- =================================================================================================================
263
264 PROCEDURE IBU_USER_GROUP_UPD
265 IS
266 -- A.last_update_date should be checked with the date the concurrent program was last run
267 l_return_status varchar2(1);
268 l_msg_count number;
269 l_msg_data varchar2(2000);
270
271 -- select B.group_id, B.group_name, B.group_number,
272 -- A.start_date_active, A.end_date_active,
273 -- B.exclusive_flag, B.group_desc, B.object_version_number,
274 -- A.sql_text
275 -- from jtf_rs_dynamic_groups_vl A, jtf_rs_groups_vl B
276 -- Where A.group_name || '(' || A.group_number || ')' = B.GROUP_NAME
277 -- and ( ( A.end_date_active is null ) or ( A.end_date_active > sysdate ) )
278 -- and A.Usage = 'ISUPPORT';
279
280 -- Updated the group_name to match the (11111) patterns only
281 -- see bug 2925331, when user updated the group name
282 -- the original code created a new static group instead of updating
283 -- the old one
284
285 -- Cursor l_upd_usrgrp_csr IS
286 -- select B.group_id, A.group_name || '(' || A.group_number || ')' group_name, B.group_number,
287 -- A.start_date_active, A.end_date_active,
288 -- B.exclusive_flag, B.group_desc, B.object_version_number,
289 -- A.sql_text
290 -- from jtf_rs_dynamic_groups_vl A, jtf_rs_groups_vl B
291 -- Where B.GROUP_NAME like '%(' || A.group_number || ')'
292 -- and ( ( A.end_date_active is null ) or ( A.end_date_active > sysdate ) )
293 -- and A.Usage = 'ISUPPORT';
294 --and A.last_update_date > Sysdate - 1;
295
296 -- bug 4861793 : performance fix
297 -- this leaves a FTS on jtf_rs_dynamic_groups_b since it has no index except the
298 -- key column. should be ok for us, since this is a small setup table.
299 Cursor l_upd_usrgrp_csr IS
300 select
301 B.group_id,
302 ATL.group_name || '(' || A.group_number || ')' group_name,
303 B.group_number,
304 A.start_date_active,
305 A.end_date_active,
306 B.exclusive_flag,
307 BTL.group_desc,
308 B.object_version_number,
309 A.sql_text
310 from
311 jtf_rs_dynamic_groups_b A,
312 jtf_rs_dynamic_groups_tl ATL,
313 jtf_rs_groups_b B,
314 jtf_rs_groups_tl BTL
315 Where
316 B.group_id = BTL.group_id
317 and
318 A.group_id = ATL.group_id
319 and
320 BTL.language = userenv('LANG')
321 and
322 ATL.language = BTL.language
323 and
324 BTL.GROUP_NAME = ATL.group_name || '(' || A.group_number || ')'
325 and
326 nvl(a.end_date_active,trunc(sysdate)) >= trunc(sysdate)
327 and
328 A.Usage = 'ISUPPORT' ;
329
330
331 l_group_id jtf_rs_groups_vl.group_id%TYPE;
332 l_group_name jtf_rs_groups_vl.group_name%TYPE;
333 l_group_number jtf_rs_groups_vl.group_number%TYPE;
334 l_start_date_active jtf_rs_dynamic_groups_vl.start_date_active%TYPE;
335 l_end_date_active jtf_rs_dynamic_groups_vl.end_date_active%TYPE;
336 l_exclusive_flag jtf_rs_groups_vl.exclusive_flag%TYPE;
337 l_group_desc jtf_rs_groups_vl.group_desc%TYPE;
338 l_obj_grp_version_num jtf_rs_groups_vl.object_version_number%TYPE;
339 l_sql_text jtf_rs_dynamic_groups_vl.sql_text%TYPE;
340 l_category varchar2(50);
341 l_role_relate_id jtf_rs_role_relations.role_relate_id%TYPE;
342 l_object_version_number jtf_rs_role_relations.object_version_number%TYPE;
343 l_user_id fnd_user.user_id%TYPE;
344
345 Cursor l_del_mem_csr(p_group_id jtf_rs_groups_vl.group_id%TYPE)
346 IS
347 select A.group_member_id,
348 B.resource_id,
349 B.resource_number ,
350 B.source_id,
351 A.object_version_number
352 from jtf_rs_group_members_vl A, jtf_rs_resource_extns B
353 where group_id = p_group_id
354 and A.resource_id = B.resource_id
355 and A.delete_flag = 'N'
356 and A.resource_id not in ( select resource_id
357 from jtf_rs_resource_extns C, ibu_usergroups_temp D
358 where C.source_id = D.source_id
359 and C.category = D.category);
360
361 Cursor l_cre_res_csr
362 IS
363 select source_id, category
364 from ibu_usergroups_temp A
365 where not exists (select source_id
366 from jtf_rs_resource_extns
367 where source_id = A.source_id
368 and category = A.category
369 and ( (end_date_active is null ) OR (end_date_active > sysdate) )
370 );
371
372 -- Do not create members whose resource records are end dated
373 Cursor l_cre_csr (p_group_id jtf_rs_groups_vl.group_id%TYPE)
374 IS
375 select B.resource_id, B.resource_number
376 from ibu_usergroups_temp A, jtf_rs_resource_extns B
377 where A.source_id = B.source_id
378 and A.category = B.category
379 and not exists ( select resource_id
380 from jtf_rs_group_members
381 where resource_id = B.resource_id
382 and group_id = p_group_id
383 and delete_flag = 'N')
384 and ( ( B.end_date_active is null ) or (B.end_date_active > sysdate) ) ;
385
386 l_resource_cnt number;
387 l_resource_id jtf_rs_resource_extns.resource_id%TYPE;
388 l_resource_number jtf_rs_resource_extns.resource_number%TYPE;
389 l_group_member_id jtf_rs_group_members.group_member_id%TYPE;
390
391 l_fndactive_cnt NUMBER;
392 l_res_start_date_active jtf_rs_resource_extns.start_date_active%TYPE;
393 l_res_end_date_active jtf_rs_resource_extns.end_date_active%TYPE;
394 l_source_name jtf_rs_resource_extns.source_name%TYPE;
395 l_dbl_res_cnt NUMBER;
396
397 -- bug 3032219
398 -- to get the first start date of the employee
399 CURSOR c_emp_start_date(c_person_id IN NUMBER) IS
400 SELECT date_start
401 FROM per_periods_of_service
402 WHERE person_id = c_person_id
403 ORDER BY date_start asc;
404
405 -- to get the person detail of an employee
406 CURSOR c_emp_dtls(c_person_id IN NUMBER) IS
407 SELECT full_name
408 FROM per_all_people_f
409 WHERE person_id = c_person_id
410 ORDER BY effective_start_date desc;
411
412 begin
413
414 Log_Message('Begin ibu_user_group_upd procedure.... ');
415 For l_upd_rec in l_upd_usrgrp_csr Loop
416
417 /* Find category for the SQL_TEXT */
418 get_category(l_upd_rec.sql_text, l_category);
419
420 If ( ( l_category = 'PARTY' ) OR ( l_category = 'EMPLOYEE' ) ) Then
421 l_group_id := l_upd_rec.group_id;
422 l_group_name := l_upd_rec.group_name;
423 l_group_number := l_upd_rec.group_number;
424 l_start_date_active := l_upd_rec.start_date_active;
425 l_end_date_active := l_upd_rec.end_date_active;
426 l_exclusive_flag := l_upd_rec.exclusive_flag;
427 l_group_desc := l_upd_rec.group_desc;
428 l_obj_grp_version_num := l_upd_rec.object_version_number;
429 l_sql_text := l_upd_rec.sql_text;
430 Log_Message( '+----------------------------------------------------------------------+');
431 Log_Message( '%%%Start Update Processing for Group Name = ' || l_group_name || '%%%');
432 Log_Message( 'Group Id = ' || l_group_id);
433 Log_Message( 'Group Number = ' || l_group_number);
434
435 /* call update role_relations */
436 select role_relate_id, object_version_number
437 into l_role_relate_id, l_object_version_number
438 from jtf_rs_role_relations
439 where role_resource_type = 'RS_GROUP'
440 and role_resource_id = l_group_id;
441 jtf_rs_role_relate_pub.UPDATE_RESOURCE_ROLE_RELATE (
442 P_API_VERSION => 1.0,
443 P_INIT_MSG_LIST => fnd_api.g_false,
444 P_COMMIT => FND_API.G_FALSE,
445 P_ROLE_RELATE_ID => l_role_relate_id,
446 P_START_DATE_ACTIVE => l_start_date_active,
447 P_END_DATE_ACTIVE => l_end_date_active,
448 P_OBJECT_VERSION_NUM => l_object_version_number,
449 X_RETURN_STATUS => l_return_status,
450 X_MSG_COUNT => l_msg_count,
451 X_MSG_DATA => l_msg_data
452 );
453 Status_Log_Message('Return status of JTF_RS_ROLE_RELATE_PUB.UPDATE_RESOURCE_ROLE_RELATE api ',
454 l_return_status , l_msg_count , l_msg_data );
455
456 /* Update Resource Group.*/
457 JTF_RS_GROUPS_PUB.UPDATE_RESOURCE_GROUP(
458 P_API_VERSION => 1,
459 P_INIT_MSG_LIST => FND_API.G_FALSE,
460 P_COMMIT => FND_API.G_FALSE,
461 P_GROUP_ID => l_group_id,
462 P_GROUP_NUMBER => l_group_number,
463 P_GROUP_NAME => l_group_name,
464 P_GROUP_DESC => l_group_desc,
465 P_EXCLUSIVE_FLAG => l_exclusive_flag,
466 P_EMAIL_ADDRESS => NULL,
467 P_START_DATE_ACTIVE => l_start_date_active,
468 P_END_DATE_ACTIVE => l_end_date_active,
469 P_ACCOUNTING_CODE => NULL,
470 P_OBJECT_VERSION_NUM => l_obj_grp_version_num,
471 X_RETURN_STATUS => l_return_status,
472 X_MSG_COUNT => l_msg_count,
473 X_MSG_DATA => l_msg_data
474 );
475 Status_Log_Message('Return status of JTF_RS_GROUPS_PUB.UPDATE_RESOURCE_GROUP api ',
476 l_return_status , l_msg_count , l_msg_data );
477
478 /* Insert into temporary table, results of user group 'dynamic' query. */
479 insert_temp_table(l_sql_text, l_category);
480
481 /* In case of customer user group, Some of the customers could be valid employees, so update the category column to 'EMPLOYEE' for those customers, source_id column to employee_id */
482 Update_Category_SourceId(l_category);
483
484 /* Delete existing members in the group who should not be part of this user group. Reason : because of change in the user group query */
485 For l_del_mem_rec IN l_del_mem_csr(l_group_id) Loop
486 JTF_RS_GROUP_MEMBERS_PUB.delete_resource_group_members
487 (P_API_VERSION => 1,
488 P_INIT_MSG_LIST =>FND_API.G_FALSE,
489 P_COMMIT =>FND_API.G_FALSE,
490 P_GROUP_ID =>l_upd_rec.group_id ,
491 P_GROUP_NUMBER => l_upd_rec.group_number,
492 P_RESOURCE_ID => l_del_mem_rec.resource_id,
493 P_RESOURCE_NUMBER => l_del_mem_rec.resource_number,
494 P_OBJECT_VERSION_NUM => l_del_mem_rec.object_version_number,
495 X_RETURN_STATUS => l_return_status,
496 X_MSG_COUNT => l_msg_count,
497 X_MSG_DATA => l_msg_data
498 );
499 Status_Log_Message('Return status of JTF_RS_GROUP_MEMBERS_PUB.delete_resource_group_members api ' ||
500 ' l_mem_id = ' || to_char(l_del_mem_rec.group_member_id) || ' ',
501 l_return_status , l_msg_count , l_msg_data );
502 End Loop;
503
504 /* Create resources for new members if they are not already resources */
505
506 For l_cre_res_rec IN l_cre_res_csr Loop
507 select count(*) into l_resource_cnt
508 from jtf_rs_resource_extns
509 where source_id = l_cre_res_rec.source_id
510 and category = l_cre_res_rec.category;
511
512 If l_resource_cnt = 0 then
513
514 If ( l_cre_res_rec.category = 'EMPLOYEE') Then
515 -- bug 3032219
516 OPEN c_emp_start_date(l_cre_res_rec.source_id);
517 FETCH c_emp_start_date INTO l_res_start_date_active;
518 IF c_emp_start_date%NOTFOUND THEN
519 l_res_start_date_active := sysdate;
520 END IF; -- for employee existence check
521 CLOSE c_emp_start_date;
522
523 -- source name
524 OPEN c_emp_dtls(l_cre_res_rec.source_id);
525 FETCH c_emp_dtls into l_source_name;
526 IF c_emp_dtls%NOTFOUND THEN
527 l_source_name := null;
528 END IF;
529 CLOSE c_emp_dtls;
530
531 else
532 -- party
533 BEGIN
534 SELECT party_name,creation_date
535 INTO l_source_name, l_res_start_date_active
536 FROM hz_parties
537 WHERE party_id = l_cre_res_rec.source_id;
538 EXCEPTION
539 WHEN NO_DATA_FOUND THEN
540 l_source_name := null;
541 l_res_start_date_active := sysdate;
542 WHEN OTHERS THEN
543 l_source_name := null;
544 l_res_start_date_active := sysdate;
545 END;
546 end if;
547
548 JTF_RS_RESOURCE_PUB.CREATE_RESOURCE(
549 P_API_VERSION => 1.0,
550 P_INIT_MSG_LIST => FND_API.G_FALSE,
551 P_COMMIT => FND_API.G_FALSE,
552 P_CATEGORY => l_cre_res_rec.category,
553 P_SOURCE_ID => l_cre_res_rec.source_id,
554 P_SOURCE_NAME => l_source_name,
555 P_START_DATE_ACTIVE => l_res_start_date_active,
556 X_RETURN_STATUS => l_return_status,
557 X_MSG_COUNT => l_msg_count,
558 X_MSG_DATA => l_msg_data,
559 X_RESOURCE_ID => l_resource_id,
560 X_RESOURCE_NUMBER => l_resource_number
561 );
562 If (l_return_status <> fnd_api.g_ret_sts_success) Then
563 Status_Log_Message('Return status of JTF_RS_RESOURCE_PUB.create_resource api ' ||
564 ' l_cre_res_rec.category = ' || l_cre_res_rec.category ||
565 ' l_source_name = ' || l_source_name ||
566 ' l_res_start_date_active = ' || l_res_start_date_active ||
567 ' l_cre_rec.source_id = ' || to_char(l_cre_res_rec.source_id) || ' ',
568 l_return_status , l_msg_count , l_msg_data );
569 End If;
570 Else -- If l_resource_cnt = 0 then
571 select count(*)
572 into l_dbl_res_cnt
573 from jtf_rs_resource_extns
574 where source_id = l_cre_res_rec.source_id
575 and category = l_cre_res_rec.category
576 and ( (end_date_active is null) or (end_date_active > sysdate) );
577 If l_dbl_res_cnt > 1 Then
578 -- Multiple active resource records for one fnd_user record.
579 Log_Message( 'This user has not been added to user group. This fnd_user record has mutltiple active resource records. Please correct this.' ||
580 ' source_id = ' || l_cre_res_rec.source_id ||
581 ' category = ' || l_cre_res_rec.category);
582 ElsIf l_dbl_res_cnt = 0 Then
583 -- All the resource records are end_dated
584 select resource_id, resource_number, object_version_number, end_date_active
585 into l_resource_id, l_resource_number , l_object_version_number, l_res_end_date_active
586 from jtf_rs_resource_extns
587 where source_id = l_cre_res_rec.source_id
588 and category = l_cre_res_rec.category
589 and resource_id = (select max(resource_id)
590 from jtf_rs_resource_extns
591 Where source_id = l_cre_res_rec.source_id
592 and category = l_cre_res_rec.category);
593 -- Check if resource records are end dated
594 GetFndUserActiveCnt(l_cre_res_rec.source_id, l_cre_res_rec.category, l_fndactive_cnt);
595 If l_fndactive_cnt = 0 Then
596 Log_Message( ' resource_id = ' || to_char(l_resource_id) ||
597 ' resource_number = ' || l_resource_number ||
598 ' ; source_id = ' || to_char(l_cre_res_rec.source_id) ||
599 ' ; category = ' || l_cre_res_rec.category ||
600 ' Safely skip this record, all fnd_user records belonging to this source_id are' ||
601 ' end_dated. So, resource record is also end dated ;'
602 );
603 Else
604 -- Atleast one fnd_user record is still active. So, Log_Message a message that Resource is end_dated even though fnd_user is valid
605 Log_Message(' * resource_id = ' || to_char(l_resource_id) ||
606 ' resource_number = ' || l_resource_number ||
607 ' ; source_id = ' || to_char(l_cre_res_rec.source_id) ||
608 ' ; category = ' || l_cre_res_rec.category ||
609 ' This user has not been added to user group. Fnd User record is active.' ||
610 ' But resource record is end dated. ' ||
611 ' Correct the end_date_active of resource record' );
612 End If;
613 End If; -- If l_dbl_res_cnt > 1 Then
614 End If; --If l_resource_cnt = 0 then
615 End Loop;
616
617 /* create news members in the group who are not part of this user group. Reason : because of change in the user group query */
618 For l_cre_rec IN l_cre_csr(l_group_id) Loop
619 JTF_RS_GROUP_MEMBERS_PUB.create_resource_group_members(P_API_VERSION => 1.0,
620 P_INIT_MSG_LIST => FND_API.G_FALSE,
621 P_COMMIT => FND_API.G_FALSE,
622 P_GROUP_ID => l_group_id,
623 P_GROUP_NUMBER => l_group_number,
624 P_RESOURCE_ID => l_cre_rec.resource_id,
625 P_RESOURCE_NUMBER => l_cre_rec.resource_number,
626 X_RETURN_STATUS => l_return_status,
627 X_MSG_COUNT => l_msg_count,
628 X_MSG_DATA => l_msg_data,
629 X_GROUP_MEMBER_ID => l_group_member_id
630 );
631 If (l_return_status <> fnd_api.g_ret_sts_success) Then
632 Status_Log_Message('Return status of JTF_RS_GROUP_MEMBERS_PUB.create_resource_group_members api ' ||
633 ' l_cre_rec.resource_id = ' || to_char(l_cre_rec.resource_id) || ' ',
634 l_return_status , l_msg_count , l_msg_data );
635 End If;
636 End Loop;
637 Log_Message( '%%%End Update Processing for Group Name = ' || l_group_name || '%%%');
638 Log_Message( '+----------------------------------------------------------------------+');
639 Else
640 Log_Message( '%%%Not a Valid SQL : Update Processing for Group Name = ' || l_group_name || '%%%');
641 End If; --( ( l_category = 'PARTY' ) OR ( l_category = 'EMPLOYEE' ) )
642 commit;
643 End Loop; -- End of l_upd_csr loop
644 Log_Message('End ibu_user_group_upd procedure.... ');
645 Exception
646 When Others Then
647 Log_Message(' Error in IBU_USER_GROUP_UPD' || TO_CHAR(SQLCODE)||': '||SQLERRM );
648 raise;
649 End;
650 -- =================================================================================================================
651 PROCEDURE IBU_USER_GROUP_CRE
652 IS
653 -- A.last_update_date should be checked with the date the concurrent program was last run
654 l_return_status varchar2(1);
655 l_msg_count number;
656 l_msg_data varchar2(2000);
657
658 Cursor l_cre_usrgrp_csr IS
659 select A.group_id, A.group_name, A.group_number,
660 A.group_name || '(' || A.group_number || ')' static_group_name,
661 A.start_date_active, A.end_date_active,
662 A.group_desc,
663 A.sql_text,
664 A.created_by
665 from jtf_rs_dynamic_groups_vl A
666 Where not exists ( Select B.group_name
667 from jtf_rs_groups_vl B
668 Where B.group_name = A.group_name || '(' || A.group_number || ')' )
669 and ( ( A.end_date_active is null ) or ( A.end_date_active > sysdate ) )
670 and A.Usage = 'ISUPPORT';
671
672 Cursor l_cre_mem_csr IS
673 select source_id, category
674 from ibu_usergroups_temp ;
675
676 -- bug 3032219
677 -- to get the first start date of the employee
678 CURSOR c_emp_start_date(c_person_id IN NUMBER) IS
679 SELECT date_start
680 FROM per_periods_of_service
681 WHERE person_id = c_person_id
682 ORDER BY date_start asc;
683
684 -- to get the person detail of an employee
685 CURSOR c_emp_dtls(c_person_id IN NUMBER) IS
686 SELECT full_name
687 FROM per_all_people_f
688 WHERE person_id = c_person_id
689 ORDER BY effective_start_date desc;
690
691 createResGrpExp Exception;
692 creByFndUserExp Exception;
693
694 -- IN/OUT variables for APIs
695 l_group_id jtf_rs_groups_vl.group_id%type;
696 l_group_number jtf_rs_groups_vl.group_number%type;
697 l_resource_id jtf_rs_resource_extns.resource_id%TYPE;
698 l_resource_number jtf_rs_resource_extns.resource_number%TYPE;
699 l_channel_rec AMV_CHANNEL_PVT.AMV_CHANNEL_OBJ_TYPE;
700
701 -- constants in this program
702 l_exclusive_flag jtf_rs_groups_vl.exclusive_flag%TYPE DEFAULT 'N';
703 l_role_code jtf_rs_role_relations_vl.ROLE_TYPE_CODE%TYPE := 'IBUUG';
704 l_usage JTF_RS_GROUP_USAGES.USAGE%TYPE := 'ISUPPORT';
705
706 -- Temporary variables
707 l_creby_source_id jtf_rs_resource_extns.source_id%TYPE;
708 l_creby_empid fnd_user.employee_id%TYPE;
709 l_creby_custid fnd_user.customer_id%TYPE;
710 l_creby_supid fnd_user.supplier_id%TYPE;
711 l_creby_category jtf_rs_resource_extns.category%TYPE;
712 l_creby_res_cnt NUMBER;
713 l_creby_resource_id jtf_rs_resource_extns.resource_id%TYPE;
714 l_creby_resource_number jtf_rs_resource_extns.resource_number%TYPE;
715 l_creby_obj_ver_num jtf_rs_resource_extns.object_version_number%TYPE;
716 l_channel_id NUMBER;
717 l_role_relate_id JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE;
718 l_group_usage_id JTF_RS_GROUP_USAGES.GROUP_USAGE_ID%TYPE;
719 l_category varchar2(50);
720 l_res_cnt NUMBER;
721 l_group_member_id JTF_RS_GROUP_MEMBERS.GROUP_MEMBER_ID%TYPE;
722 l_object_version_number jtf_rs_resource_extns.object_version_number%TYPE;
723 l_user_id fnd_user.user_id%TYPE;
724
725 l_fndactive_cnt NUMBER;
726 l_res_end_date_active jtf_rs_resource_extns.end_date_active%TYPE;
727 l_res_start_date_active jtf_rs_resource_extns.start_date_active%TYPE;
728
729 l_source_name jtf_rs_resource_extns.source_name%TYPE;
730 l_address_id jtf_rs_resource_extns.address_id%TYPE;
731 l_temp_cnt NUMBER;
732 l_dbl_res_cnt NUMBER;
733 Begin
734 Log_Message('Begin ibu_user_group_cre procedure.... ');
735 For l_cre_rec IN l_cre_usrgrp_csr Loop
736
737 /* Find category for the SQL_TEXT */
738 get_category(l_cre_rec.sql_text, l_category);
739
740 If ( ( l_category = 'PARTY' ) OR ( l_category = 'EMPLOYEE' ) ) Then
741 Log_Message( '+----------------------------------------------------------------------+');
742 Log_Message( '%%%Start Create Processing for Group Name = ' || l_cre_rec.static_group_name || '%%%');
743 /* Call the Resource Group API, to create the group and it's members */
744 jtf_rs_groups_pub.create_resource_group
745 (P_API_VERSION => 1.0,
746 P_INIT_MSG_LIST => FND_API.G_FALSE,
747 P_COMMIT => FND_API.G_FALSE,
748 P_GROUP_NAME => l_cre_rec.static_group_name,
749 P_GROUP_DESC => l_cre_rec.group_desc,
750 P_EXCLUSIVE_FLAG => l_exclusive_flag, --'N'
751 P_START_DATE_ACTIVE => l_cre_rec.start_date_active,
752 P_END_DATE_ACTIVE => l_cre_rec.end_date_active,
753 P_ACCOUNTING_CODE => NULL,
754 X_RETURN_STATUS => l_return_status,
755 X_MSG_COUNT => l_msg_count,
756 X_MSG_DATA => l_msg_data,
757 X_GROUP_ID => l_group_id,
758 X_GROUP_NUMBER => l_group_number
759 );
760 Status_Log_Message('Return status of jtf_rs_groups_pub.create_resource_group api ' ||
761 ' l_cre_rec.static_group_name = ' || l_cre_rec.static_group_name ||
762 ' ; l_group_id = ' || to_char(l_group_id) || ' ',
763 l_return_status , l_msg_count , l_msg_data );
764 IF NOT (l_return_status = fnd_api.g_ret_sts_success) THEN
765 raise createResGrpExp;
766 End If;
767
768 /* for MES channel_rec, to pass a valid resource_id for owner id.
769 First check if, resource_id exists for that user_id.
770 If, not then create a resource. But before that, check if resource, is an employee, vendor or customer. Accordingly, populate category and source_id */
771 -- get the sysadmin's resource_id, resource_number
772 get_curr_usr_resouce_info(l_creby_resource_id, l_creby_resource_number);
773
774 /* Call the MES Channel API to Create a Channel for the Group */
775 l_channel_rec.CHANNEL_ID := FND_API.G_MISS_NUM;
776 l_channel_rec.OBJECT_VERSION_NUMBER := FND_API.G_MISS_NUM;
777 l_channel_rec.CHANNEL_NAME := FND_API.G_MISS_CHAR;
778 l_channel_rec.DESCRIPTION := FND_API.G_MISS_CHAR;
779 l_channel_rec.CHANNEL_TYPE := 'GROUP';
780 l_channel_rec.CHANNEL_CATEGORY_ID := FND_API.G_MISS_NUM;
781 l_channel_rec.STATUS := FND_API.G_MISS_CHAR;
782 l_channel_rec.OWNER_USER_ID := l_creby_resource_id;
783 l_channel_rec.DEFAULT_APPROVER_USER_ID:=l_creby_resource_id;
784 l_channel_rec.EFFECTIVE_START_DATE := FND_API.G_MISS_DATE;
785 l_channel_rec.EXPIRATION_DATE := FND_API.G_MISS_DATE;
786 l_channel_rec.ACCESS_LEVEL_TYPE := 'PUBLIC';
787 l_channel_rec.PUB_NEED_APPROVAL_FLAG := FND_API.G_FALSE;
788 l_channel_rec.SUB_NEED_APPROVAL_FLAG := FND_API.G_FALSE;
789 l_channel_rec.MATCH_ON_ALL_CRITERIA_FLAG := FND_API.G_FALSE;
790 l_channel_rec.MATCH_ON_KEYWORD_FLAG := FND_API.G_FALSE;
791 l_channel_rec.MATCH_ON_AUTHOR_FLAG := FND_API.G_FALSE;
792 l_channel_rec.MATCH_ON_PERSPECTIVE_FLAG := FND_API.G_FALSE;
793 l_channel_rec.MATCH_ON_ITEM_TYPE_FLAG := FND_API.G_FALSE;
794 l_channel_rec.MATCH_ON_CONTENT_TYPE_FLAG := FND_API.G_FALSE;
795 l_channel_rec.MATCH_ON_TIME_FLAG := FND_API.G_FALSE;
796 l_channel_rec.APPLICATION_ID := 170;
797 l_channel_rec.EXTERNAL_ACCESS_FLAG := FND_API.G_FALSE;
798 l_channel_rec.ITEM_MATCH_COUNT := 0;
799 l_channel_rec.LAST_MATCH_TIME := null;
800 l_channel_rec.NOTIFICATION_INTERVAL_TYPE := null;
801 l_channel_rec.LAST_NOTIFICATION_TIME := null;
802 l_channel_rec.ATTRIBUTE_CATEGORY := null;
803 l_channel_rec.ATTRIBUTE1 := null;
804 l_channel_rec.ATTRIBUTE2 := null;
805 l_channel_rec.ATTRIBUTE3 := null;
806 l_channel_rec.ATTRIBUTE4 := null;
807 l_channel_rec.ATTRIBUTE5 := null;
808 l_channel_rec.ATTRIBUTE6 := null;
809 l_channel_rec.ATTRIBUTE7 := null;
810 l_channel_rec.ATTRIBUTE8 := null;
811 l_channel_rec.ATTRIBUTE9 := null;
812 l_channel_rec.ATTRIBUTE10 := null;
813 l_channel_rec.ATTRIBUTE11 := null;
814 l_channel_rec.ATTRIBUTE12 := null;
815 l_channel_rec.ATTRIBUTE13 := null;
816 l_channel_rec.ATTRIBUTE14 := null;
817 l_channel_rec.ATTRIBUTE15 := null;
818
819 AMV_CHANNEL_GRP.ADD_GROUPCHANNEL ( P_API_VERSION => 1.0,
820 P_INIT_MSG_LIST => FND_API.G_FALSE,
821 P_COMMIT => FND_API.G_FALSE,
822 P_CHECK_LOGIN_USER => fnd_api.g_false,
823 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
824 P_GROUP_ID => l_group_id,
825 P_CHANNEL_RECORD => l_channel_rec,
826 X_RETURN_STATUS => l_return_status,
827 X_MSG_COUNT => l_msg_count,
828 X_MSG_DATA => l_msg_data,
829 X_CHANNEL_ID => l_channel_id
830 ) ;
831 Status_Log_Message('Return status of AMV_CHANNEL_GRP.add_groupchannel api ' ||
832 ' l_group_id = ' || to_char(l_group_id) ||
833 ' {OWNER_USER_ID} -> l_creby_resource_id = ' || to_char(l_creby_resource_id) || ' ',
834 l_return_status , l_msg_count , l_msg_data );
835
836 /* Call the resource roles API to create a role relationship for the group. */
837 jtf_rs_role_relate_pub.create_resource_role_relate
838 (P_API_VERSION => 1,
839 P_INIT_MSG_LIST => fnd_api.g_false,
840 P_COMMIT => fnd_api.g_false,
841 P_ROLE_RESOURCE_TYPE => 'RS_GROUP',
842 P_ROLE_RESOURCE_ID => l_group_id,
843 P_ROLE_ID => NULL,
844 P_ROLE_CODE => l_role_code, --'IBUUG'
845 P_START_DATE_ACTIVE => l_cre_rec.start_date_active,
846 P_END_DATE_ACTIVE => l_cre_rec.end_date_active,
847 X_RETURN_STATUS => l_return_status,
848 X_MSG_COUNT => l_msg_count,
849 X_MSG_DATA => l_msg_data,
850 X_ROLE_RELATE_ID => l_role_relate_id
851 );
852 Status_Log_Message('Return status of jtf_rs_role_relate_pub.create_resource_role_relate api ' ||
853 ' l_group_id = ' || to_char(l_group_id) ||
854 ' l_role_relate_id = ' || to_char(l_role_relate_id) || ' ',
855 l_return_status , l_msg_count , l_msg_data );
856
857 /* Call the Usage API to create a relationship between the usage and Group */
858 jtf_rs_group_usages_pub.create_group_usage
859 (P_API_VERSION => 1,
860 P_INIT_MSG_LIST => FND_API.G_FALSE,
861 P_COMMIT => FND_API.G_FALSE,
862 P_GROUP_ID => l_group_id,
863 P_GROUP_NUMBER => l_group_number,
864 P_USAGE => l_usage,
865 X_RETURN_STATUS => l_return_status,
866 X_MSG_COUNT => l_msg_count,
867 X_MSG_DATA => l_msg_data,
868 X_GROUP_USAGE_ID =>l_group_usage_id
869 );
870 Status_Log_Message('Return status of jtf_rs_group_usages_pub.create_group_usage api ' ||
871 ' l_group_id = ' || to_char(l_group_id) ||
872 ' l_group_usage_id = ' || to_char(l_group_usage_id) || ' ',
873 l_return_status , l_msg_count , l_msg_data );
874
875 /* Insert into temporary table, results of user group 'dynamic' query. */
876 insert_temp_table(l_cre_rec.sql_text, l_category);
877
878 /* In case of customer user group, Some of the customers could be valid employees, so update the category column to 'EMPLOYEE' for those customers, source_id column to employee_id */
879 Update_Category_SourceId(l_category);
880
881 /* Check for each member, a resouce exists, if not create a resource. Then make this user member of the group*/
882 For l_cre_mem_rec IN l_cre_mem_csr Loop
883 -- check if resource needs to be created - begin
884 SELECT count(*) INTO l_res_cnt
885 FROM jtf_rs_resource_extns a
886 WHERE a.source_id = l_cre_mem_rec.source_id
887 and category = l_cre_mem_rec.category;
888
889 If l_res_cnt = 0 Then
890
891 If ( l_cre_mem_rec.category = 'EMPLOYEE') Then
892 -- bug 3032219
893 OPEN c_emp_start_date(l_cre_mem_rec.source_id);
894 FETCH c_emp_start_date INTO l_res_start_date_active;
895 IF c_emp_start_date%NOTFOUND THEN
896 l_res_start_date_active := sysdate;
897 END IF; -- for employee existence check
898 CLOSE c_emp_start_date;
899
900 -- source name
901 OPEN c_emp_dtls(l_cre_mem_rec.source_id);
902 FETCH c_emp_dtls into l_source_name;
903 IF c_emp_dtls%NOTFOUND THEN
904 l_source_name := null;
905 END IF;
906 CLOSE c_emp_dtls;
907
908 else
909 -- party
910 BEGIN
911 SELECT party_name,creation_date
912 INTO l_source_name, l_res_start_date_active
913 FROM hz_parties
914 WHERE party_id = l_cre_mem_rec.source_id;
915 EXCEPTION
916 WHEN NO_DATA_FOUND THEN
917 l_source_name := null;
918 l_res_start_date_active := sysdate;
919 WHEN OTHERS THEN
920 l_source_name := null;
921 l_res_start_date_active := sysdate;
922 END;
923 end if;
924
925 -- create a resource
926 JTF_RS_RESOURCE_PUB.CREATE_RESOURCE(
927 P_API_VERSION => 1.0,
928 P_INIT_MSG_LIST => FND_API.G_FALSE,
929 P_COMMIT => FND_API.G_FALSE,
930 P_CATEGORY => l_cre_mem_rec.category,
931 P_SOURCE_ID => l_cre_mem_rec.source_id,
932 P_SOURCE_NAME => l_source_name,
933 P_START_DATE_ACTIVE => l_res_start_date_active,
934 X_RETURN_STATUS => l_return_status,
935 X_MSG_COUNT => l_msg_count,
936 X_MSG_DATA => l_msg_data,
937 X_RESOURCE_ID => l_resource_id,
938 X_RESOURCE_NUMBER => l_resource_number
939 );
940 If (l_return_status <> fnd_api.g_ret_sts_success) Then
941 Status_Log_Message('Return status of JTF_RS_RESOURCE_PUB.create_resource api ' ||
942 ' l_cre_mem_rec.category = ' || l_cre_mem_rec.category ||
943 ' l_source_name = ' || l_source_name ||
944 ' l_res_start_date_active = ' || l_res_start_date_active ||
945 ' l_cre_mem_rec.source_id = ' || to_char(l_cre_mem_rec.source_id) || ' ',
946 l_return_status , l_msg_count , l_msg_data );
947 End If;
948 Else -- Resource already exists -- If l_res_cnt = 0 Then
949 select count(*)
950 into l_dbl_res_cnt
951 from jtf_rs_resource_extns
952 where source_id = l_cre_mem_rec.source_id
953 and category = l_cre_mem_rec.category
954 and ( (end_date_active is null ) OR (end_date_active > sysdate) );
955
956 If l_dbl_res_cnt = 0 Then
957 -- No active resources. So, see if there are active fnd user records.
958 select resource_id, resource_number, object_version_number, end_date_active
959 into l_resource_id, l_resource_number, l_object_version_number , l_res_end_date_active
960 from jtf_rs_resource_extns
961 where source_id = l_cre_mem_rec.source_id
962 and category = l_cre_mem_rec.category
963 and resource_id = (select max(resource_id)
964 from jtf_rs_resource_extns
965 Where source_id = l_cre_mem_rec.source_id
966 and category = l_cre_mem_rec.category);
967 GetFndUserActiveCnt(l_cre_mem_rec.source_id, l_cre_mem_rec.category, l_fndactive_cnt);
968 If l_fndactive_cnt = 0 Then
969 Log_Message( ' resource_number = ' || l_resource_number ||
970 ' ; source_id = ' || to_char(l_cre_mem_rec.source_id) ||
971 ' ; category = ' || l_cre_mem_rec.category ||
972 ' Safely skip this record, all fnd_user records belonging to this source_id are' ||
973 ' end_dated. So, resource record is also end dated ;'
974 );
975 Else
976 -- Atleast one fnd_user record is still active. So, Log_Message a message that Resource is end_dated even though fnd_user is valid
977 Log_Message(' * resource_number = ' || l_resource_number ||
978 ' ; source_id = ' || to_char(l_cre_mem_rec.source_id) ||
979 ' ; category = ' || l_cre_mem_rec.category ||
980 ' This user has not been added to user group. Fnd User record is active.' ||
981 ' But resource record is end dated. ' ||
982 ' Correct the end_date_active of resource record' );
983 End If;
984 l_resource_id := 0;
985 l_resource_number := 0;
986 ElsIf l_dbl_res_cnt > 1 Then -- If l_dbl_res_cnt = 0 Then
987 -- Mulitple active resources
988 log_message('This user has not been added to user group. ' ||
989 'l_cre_mem_rec.source_id = ' || l_cre_mem_rec.source_id ||
990 ' l_cre_mem_rec.category = ' || l_cre_mem_rec.category ||
991 ' multiple active resource record are found. please correct it and rerun the concurrent program'
992 );
993 log_message ( ' Use this following query to find the duplicate resource records for this user');
994 log_message ( 'select resource_number, end_date_active, start_date ' ||
995 ' from jtf_rs_resource_extns ' ||
996 ' where source_id = ? ' ||
997 ' and category_id = ? ' );
998 l_resource_id := 0;
999 l_resource_number := 0;
1000 Else
1001 -- only one resource is active.
1002 select resource_id, resource_number, object_version_number, end_date_active
1003 into l_resource_id, l_resource_number, l_object_version_number , l_res_end_date_active
1004 from jtf_rs_resource_extns
1005 where source_id = l_cre_mem_rec.source_id
1006 and category = l_cre_mem_rec.category
1007 and ( (end_date_active is null ) OR (end_date_active > sysdate) );
1008 End If;
1009 End If; -- End of (l_res_cnt = 0)
1010 -- check if resource needs to be created - end
1011 -- Resource exists by now, so create resource member.
1012
1013 If l_resource_id <> 0 Then
1014 /* resource already exists and it is active. So, call create group members api */
1015 jtf_rs_group_members_pub.create_resource_group_members
1016 (P_API_VERSION => 1.0,
1017 P_INIT_MSG_LIST => FND_API.G_FALSE,
1018 P_COMMIT => FND_API.G_FALSE,
1019 P_GROUP_ID => l_group_id,
1020 P_GROUP_NUMBER => l_group_number,
1021 P_RESOURCE_ID => l_resource_id,
1022 P_RESOURCE_NUMBER => l_resource_number,
1023 X_RETURN_STATUS => l_return_status,
1024 X_MSG_COUNT => l_msg_count,
1025 X_MSG_DATA => l_msg_data,
1026 X_GROUP_MEMBER_ID => l_group_member_id
1027 );
1028 If (l_return_status <> fnd_api.g_ret_sts_success) Then
1029 select count(*) into l_temp_cnt
1030 from jtf_rs_group_members
1031 Where group_id = l_group_id
1032 and resource_id = l_resource_id
1033 and delete_flag = 'N';
1034 If l_temp_cnt = 0 Then
1035 Status_Log_Message('Return status of JTF_RS_GROUP_MEMBERS_PUB.create_resource_group_members api ' ||
1036 ' l_resource_id = ' || to_char(l_resource_id) || ' ',
1037 l_return_status , l_msg_count , l_msg_data );
1038 End If;
1039 End If;
1040 End If; -- End of l_resource_id <> 0
1041
1042 End Loop; -- End of l_cre_mem_csr
1043 Log_Message( '%%%End Create Processing for Group Name = ' || l_cre_rec.static_group_name || '%%%');
1044 Log_Message( '+----------------------------------------------------------------------+');
1045 Else
1046 Log_Message( '%%%Not a valid SQL for Create Processing for Group Name = ' || l_cre_rec.static_group_name || '%%%');
1047 End If; -- End of ( ( l_category = 'PARTY' ) OR ( l_category = 'EMPLOYEE' ) )
1048 commit;
1049 End Loop; -- End of l_cre_usrgrp_csr
1050 Log_Message('End ibu_user_group_cre procedure.... ');
1051 Exception
1052 When Others Then
1053 Log_Message(' Error in IBU_USER_GROUP_CRE ' || TO_CHAR(SQLCODE)||': '||SQLERRM );
1054 raise;
1055 End;
1056 -- =================================================================================================================
1057 PROCEDURE IBU_USER_GROUP_DEL
1058 IS
1059 -- A.last_update_date should be checked with the date the concurrent program was last run
1060 l_return_status varchar2(1);
1061 l_msg_count number;
1062 l_msg_data varchar2(2000);
1063
1064 Cursor l_del_usrgrp_csr IS
1065 select A.group_id dyn_group_id,
1066 A.group_name dyn_group_name,
1067 A.group_number dyn_group_number,
1068 B.group_id static_group_id,
1069 B.group_name static_group_name,
1070 B.group_number static_group_number,
1071 A.start_date_active, A.end_date_active, A.sql_text,
1072 B.exclusive_flag, B.group_desc, B.object_version_number
1073 from jtf_rs_dynamic_groups_vl A, jtf_rs_groups_vl B
1074 Where A.group_name || '(' || A.group_number || ')' = B.GROUP_NAME
1075 and ( ( B.end_date_active is null ) OR ( B.end_date_active > sysdate ) )
1076 and ( ( A.end_date_active is not null ) AND ( A.end_date_active <= sysdate ) )
1077 and A.usage = 'ISUPPORT';
1078
1079 Cursor l_del_chnl_csr (p_group_name amv_c_channels_vl.channel_name%TYPE ) IS
1080 select channel_id
1081 from amv_c_channels_vl
1082 where channel_name = p_group_name;
1083
1084 Cursor l_del_mem_csr(p_group_id jtf_rs_group_members.group_id%TYPE )
1085 IS
1086 select B.group_member_id,
1087 A.group_id,
1088 A.group_number,
1089 B.object_version_number,
1090 B.resource_id,
1091 C.resource_number
1092 from jtf_rs_groups_vl A, jtf_rs_group_members B, jtf_rs_resource_extns C
1093 Where A.group_id = p_group_id
1094 and A.group_id = B.group_id
1095 and B.resource_id = C.resource_id
1096 and B.delete_flag = 'N' ;
1097
1098 -- Temporary variables
1099 l_creby_source_id jtf_rs_resource_extns.source_id%TYPE;
1100 l_creby_empid fnd_user.employee_id%TYPE;
1101 l_creby_custid fnd_user.customer_id%TYPE;
1102 l_creby_supid fnd_user.supplier_id%TYPE;
1103 l_creby_category jtf_rs_resource_extns.category%TYPE;
1104 l_creby_res_cnt NUMBER;
1105 l_creby_resource_id jtf_rs_resource_extns.resource_id%TYPE;
1106 l_creby_resource_number jtf_rs_resource_extns.resource_number%TYPE;
1107 l_creby_obj_ver_num jtf_rs_resource_extns.object_version_number%TYPE;
1108 l_category varchar2(50);
1109 l_role_relate_id jtf_rs_role_relations.role_relate_id%TYPE;
1110 l_object_version_number jtf_rs_role_relations.object_version_number%TYPE;
1111
1112 -- constant
1113 l_current_user_id NUMBER := FND_GLOBAL.User_Id;
1114
1115 l_res_end_date_active jtf_rs_resource_extns.end_date_active%TYPE;
1116 l_source_name jtf_rs_resource_extns.source_name%TYPE;
1117 l_address_id jtf_rs_resource_extns.address_id%TYPE;
1118
1119 creByFndUserExp Exception;
1120 Begin
1121 Log_Message('Inside ibu_user_group_del procedure.... ');
1122 For l_del_rec IN l_del_usrgrp_csr Loop
1123 Log_Message( '+----------------------------------------------------------------------+');
1124 Log_Message( '%%%Start Delete Processing for Group Name = ' || l_del_rec.static_group_name || '%%%');
1125
1126 /* call the delete channel api of MES */
1127 For l_del_chnl_rec IN l_del_chnl_csr(l_del_rec.static_group_name) Loop
1128 AMV_CHANNEL_GRP.DELETE_CHANNEL( P_API_VERSION => 1.0,
1129 P_INIT_MSG_LIST => FND_API.G_FALSE,
1130 P_COMMIT => FND_API.G_FALSE,
1131 P_VALIDATION_LEVEL =>FND_API.G_VALID_LEVEL_FULL,
1132 P_CHECK_LOGIN_USER =>FND_API.G_FALSE,
1133 P_CHANNEL_ID => l_del_chnl_rec.channel_id,
1134 X_RETURN_STATUS => l_return_status,
1135 X_MSG_COUNT => l_msg_count,
1136 X_MSG_DATA =>l_msg_data
1137 );
1138 Status_Log_Message('Return status of AMV_CHANNEL_GRP.DELETE_CHANNEL api ' || ' l_del_chnl_rec.channel_id = ' ||
1139 to_char(l_del_chnl_rec.channel_id) || ' ',
1140 l_return_status , l_msg_count , l_msg_data );
1141 End Loop; -- End of l_del_chnl_csr
1142
1143 /* call update role_relations */
1144 Begin
1145 select role_relate_id, object_version_number
1146 into l_role_relate_id, l_object_version_number
1147 from jtf_rs_role_relations
1148 where role_resource_type = 'RS_GROUP'
1149 and role_resource_id = l_del_rec.static_group_id;
1150 Exception
1151 When no_data_found then
1152 l_role_relate_id := 0;
1153 l_object_version_number := 0;
1154 When others then
1155 raise;
1156 End;
1157 If l_role_relate_id <> 0 then
1158 jtf_rs_role_relate_pub.UPDATE_RESOURCE_ROLE_RELATE( P_API_VERSION => 1.0,
1159 P_INIT_MSG_LIST => fnd_api.g_false,
1160 P_COMMIT => FND_API.G_FALSE,
1161 P_ROLE_RELATE_ID => l_role_relate_id,
1162 P_START_DATE_ACTIVE => l_del_rec.start_date_active,
1163 P_END_DATE_ACTIVE => l_del_rec.end_date_active,
1164 P_OBJECT_VERSION_NUM => l_object_version_number,
1165 X_RETURN_STATUS => l_return_status,
1166 X_MSG_COUNT => l_msg_count,
1167 X_MSG_DATA => l_msg_data
1168 );
1169 End If;
1170 Status_Log_Message('Return status of jtf_rs_role_relate_pub.update_resource_role_relate api ',
1171 l_return_status , l_msg_count , l_msg_data );
1172
1173 /* call update resource group */
1174 jtf_rs_groups_pub.update_resource_group
1175 (P_API_VERSION => 1,
1176 P_INIT_MSG_LIST => fnd_api.g_false,
1177 P_COMMIT => FND_API.G_FALSE,
1178 P_GROUP_ID => l_del_rec.static_group_id,
1179 P_GROUP_NUMBER => l_del_rec.static_group_number,
1180 P_GROUP_NAME => l_del_rec.static_group_name,
1181 P_GROUP_DESC => l_del_rec.group_desc,
1182 P_EXCLUSIVE_FLAG => l_del_rec.exclusive_flag,
1183 P_START_DATE_ACTIVE => l_del_rec.start_date_active,
1184 P_END_DATE_ACTIVE => l_del_rec.end_date_active,
1185 P_ACCOUNTING_CODE => NULL,
1186 P_OBJECT_VERSION_NUM => l_del_rec.object_version_number,
1187 X_RETURN_STATUS => l_return_status,
1188 X_MSG_COUNT => l_msg_count,
1189 X_MSG_DATA => l_msg_data
1190 );
1191 Status_Log_Message('Return status of JTF_RS_GROUPS_PUB.UPDATE_RESOURCE_GROUP api ',
1192 l_return_status , l_msg_count , l_msg_data );
1193
1194 /* Find category for the SQL_TEXT */
1195 get_category(l_del_rec.sql_text, l_category);
1196
1197 For l_del_mem_rec IN l_del_mem_csr(l_del_rec.static_group_id) Loop
1198 jtf_rs_group_members_pub.delete_resource_group_members(P_API_VERSION => 1.0,
1199 P_INIT_MSG_LIST => FND_API.G_FALSE,
1200 P_COMMIT => FND_API.G_FALSE,
1201 P_GROUP_ID => l_del_mem_rec.group_id,
1202 P_GROUP_NUMBER => l_del_mem_rec.group_number,
1203 P_RESOURCE_ID => l_del_mem_rec.resource_id,
1204 P_RESOURCE_NUMBER => l_del_mem_rec.resource_number,
1205 P_OBJECT_VERSION_NUM => l_del_mem_rec.object_version_number,
1206 X_RETURN_STATUS => l_return_status,
1207 X_MSG_COUNT => l_msg_count,
1208 X_MSG_DATA => l_msg_data
1209 );
1210 If (l_return_status <> fnd_api.g_ret_sts_success) Then
1211 Status_Log_Message('Return status of JTF_RS_GROUP_MEMBERS_PUB.delete_resource_group_members api ' ||
1212 ' l_mem_id = ' || to_char(l_del_mem_rec.group_member_id) || ' ',
1213 l_return_status , l_msg_count , l_msg_data );
1214 End If;
1215 End Loop; -- End of l_del_mem_csr loop
1216 Log_Message( '%%%End Delete Processing for Group Name = ' || l_del_rec.static_group_name || '%%%');
1217 Log_Message( '+----------------------------------------------------------------------+');
1218 commit;
1219 End Loop; -- End of l_del_csr loop
1220 Log_Message('End ibu_user_group_del procedure.... ');
1221 Exception
1222 When Others Then
1223 Log_Message(' Error in IBU_USER_GROUP_DEL ' || TO_CHAR(SQLCODE)||': '||SQLERRM );
1224 raise;
1225 End;
1226 -- =================================================================================================================
1227 PROCEDURE check_resource_setup
1228 (
1229 x_resource_id OUT NOCOPY jtf_rs_resource_extns.resource_id%TYPE,
1230 x_resource_number OUT NOCOPY jtf_rs_resource_extns.resource_number%TYPE,
1231 x_setup_success OUT NOCOPY NUMBER
1232 )
1233 IS
1234 l_current_user_id NUMBER := FND_GLOBAL.User_Id;
1235
1236 -- Temporary variables
1237 l_creby_source_id jtf_rs_resource_extns.source_id%TYPE;
1238 l_creby_empid fnd_user.employee_id%TYPE;
1239 l_creby_custid fnd_user.customer_id%TYPE;
1240 l_creby_supid fnd_user.supplier_id%TYPE;
1241 l_creby_category jtf_rs_resource_extns.category%TYPE;
1242 l_creby_res_cnt NUMBER;
1243 l_creby_resource_id jtf_rs_resource_extns.resource_id%TYPE;
1244 l_creby_resource_number jtf_rs_resource_extns.resource_number%TYPE;
1245 l_creby_obj_ver_num jtf_rs_resource_extns.object_version_number%TYPE;
1246 l_category varchar2(50);
1247 l_role_relate_id jtf_rs_role_relations.role_relate_id%TYPE;
1248 l_object_version_number jtf_rs_role_relations.object_version_number%TYPE;
1249
1250 l_return_status varchar2(1);
1251 l_msg_count number;
1252 l_msg_data varchar2(2000);
1253 l_dbl_res_cnt number;
1254
1255 l_res_start_date_active jtf_rs_resource_extns.start_date_active%TYPE;
1256 l_source_name jtf_rs_resource_extns.source_name%TYPE;
1257
1258 -- bug 3032219
1259 -- to get the first start date of the employee
1260 CURSOR c_emp_start_date(c_person_id IN NUMBER) IS
1261 SELECT date_start
1262 FROM per_periods_of_service
1263 WHERE person_id = c_person_id
1264 ORDER BY date_start asc;
1265
1266 -- to get the person detail of an employee
1267 CURSOR c_emp_dtls(c_person_id IN NUMBER) IS
1268 SELECT full_name
1269 FROM per_all_people_f
1270 WHERE person_id = c_person_id
1271 ORDER BY effective_start_date desc;
1272
1273
1274 Begin
1275 x_setup_success := 1;
1276 select employee_id, customer_id, supplier_id into l_creby_empid, l_creby_custid, l_creby_supid
1277 from fnd_user
1278 where user_id = l_current_user_id;
1279 If l_creby_empid is not null Then
1280 l_creby_category := 'EMPLOYEE';
1281 l_creby_source_id := l_creby_empid ;
1282 ElsIf l_creby_custid is not null Then
1283 l_creby_category := 'PARTY';
1284 l_creby_source_id := l_creby_custid ;
1285 ElsIf l_creby_supid is not null Then
1286 l_creby_category := 'SUPPLIER_CONTACT';
1287 l_creby_source_id := l_creby_supid ;
1288 Else
1289 l_creby_category := 'CREATED_BY_UNKNOWN';
1290 l_creby_source_id := 0;
1291 End If;
1292
1293 select count(*) into l_creby_res_cnt
1294 from jtf_rs_resource_extns
1295 where source_id = l_creby_source_id
1296 and category = l_creby_category;
1297
1298 If l_creby_res_cnt = 0 then
1299
1300 if (l_creby_category = 'EMPLOYEE') then
1301
1302 -- bug 3032219
1303 OPEN c_emp_start_date(l_creby_empid);
1304 FETCH c_emp_start_date INTO l_res_start_date_active;
1305 IF c_emp_start_date%NOTFOUND THEN
1306 l_res_start_date_active := sysdate;
1307 END IF; -- for employee existence check
1308 CLOSE c_emp_start_date;
1309
1310 -- source name
1311 OPEN c_emp_dtls(l_creby_empid);
1312 FETCH c_emp_dtls into l_source_name;
1313 IF c_emp_dtls%NOTFOUND THEN
1314 l_source_name := null;
1315 END IF;
1316 CLOSE c_emp_dtls;
1317 ElsIf (l_creby_category = 'PARTY') then
1318
1319 -- party
1320 BEGIN
1321 SELECT party_name,creation_date
1322 INTO l_source_name, l_res_start_date_active
1323 FROM hz_parties
1324 WHERE party_id = l_creby_custid;
1325 EXCEPTION
1326 WHEN NO_DATA_FOUND THEN
1327 l_source_name := null;
1328 l_res_start_date_active := sysdate;
1329 WHEN OTHERS THEN
1330 l_source_name := null;
1331 l_res_start_date_active := sysdate;
1332 END;
1333 elsif (l_creby_category = 'SUPPLIER_CONTACT') then
1334 BEGIN
1335 SELECT POC.LAST_NAME || ' , ' || POC.MIDDLE_NAME ||' '||
1336 POC.FIRST_NAME|| ' - '|| POV.VENDOR_NAME, nvl(POC.CREATION_DATE,sysdate)
1337 INTO l_source_name, l_res_start_date_active
1338 FROM PO_VENDOR_CONTACTS POC,
1339 PO_VENDOR_SITES_ALL POS,
1340 PO_VENDORS POV
1341 WHERE POC.VENDOR_CONTACT_ID = l_creby_supid
1342 AND POC.VENDOR_SITE_ID = POS.VENDOR_SITE_ID
1343 AND POS.VENDOR_ID = POV.VENDOR_ID;
1344 EXCEPTION
1345 WHEN NO_DATA_FOUND THEN
1346 l_res_start_date_active := sysdate;
1347 l_source_name := null;
1348 WHEN OTHERS THEN
1349 l_res_start_date_active := sysdate;
1350 l_source_name := null;
1351 END;
1352 else
1353 l_res_start_date_active := sysdate;
1354 l_source_name := null;
1355 end if;
1356
1357 -- Resource needs to be created for created_by
1358 JTF_RS_RESOURCE_PUB.CREATE_RESOURCE(
1359 P_API_VERSION => 1.0,
1360 P_INIT_MSG_LIST => FND_API.G_FALSE,
1361 P_COMMIT => FND_API.G_FALSE,
1362 P_CATEGORY => l_creby_category,
1363 P_SOURCE_ID => l_creby_source_id,
1364 P_SOURCE_NAME => l_source_name,
1365 P_START_DATE_ACTIVE => l_res_start_date_active,
1366 P_USER_ID => l_current_user_id,
1367 X_RETURN_STATUS => l_return_status,
1368 X_MSG_COUNT => l_msg_count,
1369 X_MSG_DATA => l_msg_data,
1370 X_RESOURCE_ID => l_creby_resource_id,
1371 X_RESOURCE_NUMBER => l_creby_resource_number
1372 );
1373 Status_Log_Message('Return status of JTF_RS_RESOURCE_PUB.create_resource api ' ||
1374 ' l_creby_source_id = ' || to_char(l_creby_source_id) ||
1375 ' l_creby_category = ' || l_creby_category ||
1376 ' l_res_start_date_active = ' || l_res_start_date_active ||
1377 ' l_source_name = ' || l_source_name ||
1378 ' l_creby_resource_id = ' || to_char(l_creby_resource_id) || ' ',
1379 l_return_status , l_msg_count , l_msg_data );
1380 Else
1381 select count(*)
1382 into l_dbl_res_cnt
1383 from jtf_rs_resource_extns
1384 where source_id = l_creby_source_id
1385 and category = l_creby_category
1386 and ( (end_date_active is null) or (end_date_active > sysdate) );
1387
1388 If l_dbl_res_cnt = 0 Then
1389 -- resource records are enddated
1390 x_setup_success := 0;
1391 log_message( 'CURRENT USER RESOURCE RECORD IS END DATED. please correct it and rerun the concurrent program. ' );
1392 log_message('Current User_id = ' || l_current_user_id ||
1393 ' source_id = ' || l_creby_source_id ||
1394 ' category = ' || l_creby_category
1395 );
1396 log_message ( 'In sqlplus Use this following query to find the resource numbers for the current login user');
1397 log_message ( 'select resource_number, end_date_active, start_date ' ||
1398 ' from jtf_rs_resource_extns ' ||
1399 ' where source_id = ' || l_creby_source_id ||
1400 ' and category = ' || '''' || l_creby_category || '''' );
1401 log_message('Log in to FORMS env with responsibility - CRM Resource Manager, Vision Enterprises');
1402 log_message(' Maintain Resources -> Resources -> Enter resource_number from the above query -> Un End date the resource record or Create a new Resource record');
1403 ElsIf l_dbl_res_cnt > 1 Then
1404 -- multiple resource records are active - please correct it.
1405 x_setup_success := 0;
1406 log_message('1. MULTIPLE ACTIVE RESOURCE RECORDS ARE FOUND FOR THE CURRENT USER. please correct it and rerun the concurrent program. ' );
1407 log_message(' Current User_id = ' || l_current_user_id ||
1408 ' source_id = ' || l_creby_source_id ||
1409 ' category = ' || l_creby_category
1410 );
1411 log_message ( '2. In sqlplus Use this following query to find the duplicate resource records for the current login user');
1412 log_message ( 'select resource_number, end_date_active, start_date ' ||
1413 ' from jtf_rs_resource_extns ' ||
1414 ' where source_id = ' || l_creby_source_id ||
1415 ' and category = ' || '''' || l_creby_category || '''' );
1416 log_message('3. Log in to FORMS env with responsibility - CRM Resource Manager, Vision Enterprises');
1417 log_message(' Maintain Resources -> Resources -> Enter resource_number from the above query -> End date the unwanted resource record');
1418 Else
1419 select resource_id, resource_number
1420 into x_resource_id, x_resource_number
1421 from jtf_rs_resource_extns
1422 where source_id = l_creby_source_id
1423 and category = l_creby_category
1424 and ( (end_date_active is null) or (end_date_active > sysdate) );
1425
1426 /* MES Delete Channel API expects sysadmin to have MES_ADMIN, MES_SETUP_CHANNEL role_codes */
1427 Log_Message ('Assigning MES_ADMIN, MES_SETUP_CHANNEL roles to current login user. Ignore exceptions raised by these APIs');
1428 jtf_rs_role_relate_pub.create_resource_role_relate
1429 (P_API_VERSION => 1,
1430 P_INIT_MSG_LIST => fnd_api.g_false,
1431 P_COMMIT => fnd_api.g_false,
1432 P_ROLE_RESOURCE_TYPE => 'RS_INDIVIDUAL',
1433 P_ROLE_RESOURCE_ID => x_resource_id,
1434 P_ROLE_ID => NULL,
1435 P_ROLE_CODE => 'MES_ADMIN',
1436 P_START_DATE_ACTIVE => sysdate,
1437 X_RETURN_STATUS => l_return_status,
1438 X_MSG_COUNT => l_msg_count,
1439 X_MSG_DATA => l_msg_data,
1440 X_ROLE_RELATE_ID => l_role_relate_id
1441 );
1442 Status_Log_Message('Return status of jtf_rs_role_relate_pub.create_resource_role_relate api MES_ADMIN',
1443 l_return_status , l_msg_count , l_msg_data );
1444 jtf_rs_role_relate_pub.create_resource_role_relate
1445 (P_API_VERSION => 1,
1446 P_INIT_MSG_LIST => fnd_api.g_false,
1447 P_COMMIT => fnd_api.g_false,
1448 P_ROLE_RESOURCE_TYPE => 'RS_INDIVIDUAL',
1449 P_ROLE_RESOURCE_ID => x_resource_id,
1450 P_ROLE_ID => NULL,
1451 P_ROLE_CODE => 'MES_SETUP_CHANNEL',
1452 P_START_DATE_ACTIVE => sysdate,
1453 X_RETURN_STATUS => l_return_status,
1454 X_MSG_COUNT => l_msg_count,
1455 X_MSG_DATA => l_msg_data,
1456 X_ROLE_RELATE_ID => l_role_relate_id
1457 );
1458 Status_Log_Message('Return status of jtf_rs_role_relate_pub.create_resource_role_relate api MES_SETUP_CHANNEL',
1459 l_return_status , l_msg_count , l_msg_data );
1460 End If; -- If l_dbl_res_cnt = 0 Then
1461 End If; -- l_creby_res_cnt = 0
1462 END;
1463
1464 -- =================================================================================================================
1465 PROCEDURE run_conc_prog
1466 (
1467 ERRBUF OUT NOCOPY VARCHAR2,
1468 RETCODE OUT NOCOPY NUMBER
1469 )
1470 IS
1471 x_resource_id jtf_rs_resource_extns.resource_id%TYPE;
1472 x_resource_number jtf_rs_resource_extns.resource_number%TYPE;
1473 x_setup_success NUMBER;
1474
1475 mesSysAdminSetupException Exception;
1476 Begin
1477
1478 FND_MSG_PUB.Initialize;
1479 check_resource_setup( x_resource_id , x_resource_number , x_setup_success );
1480 If x_setup_success = 1 Then
1481 Log_Message('******************Begin run_conc_prog********************' );
1482 ibu_user_group_upd;
1483 ibu_user_group_cre;
1484 ibu_user_group_del;
1485 Log_Message('******************End run_conc_prog********************' );
1486 Else
1487 raise mesSysAdminSetupException;
1488 End If;
1489
1490 commit;
1491 Exception
1492 When mesSysAdminSetupException Then
1493 Log_Message(' Problem with Sysadmin resource record : Verify this log file for more details : ' || TO_CHAR(SQLCODE)||': '||SQLERRM );
1494 raise;
1495 When Others Then
1496 Log_Message(' Error in run_conc_prog ' || TO_CHAR(SQLCODE)||': '||SQLERRM );
1497 raise;
1498 End;
1499
1500 -- =================================================================================================================
1501 END; -- Package Body IBU_DYN_USER_GROUPS_PKG