[Home] [Help]
1: package body Fnd_User_Resp_Groups_api as
2: /* $Header: AFSCURGB.pls 120.14.12000000.3 2007/02/12 16:36:58 jvalenti ship $ */
3:
4: C_PKG_NAME CONSTANT VARCHAR2(30) := 'FND_USER_RESP_GROUPS_API';
5: C_LOG_HEAD CONSTANT VARCHAR2(240)
1: package body Fnd_User_Resp_Groups_api as
2: /* $Header: AFSCURGB.pls 120.14.12000000.3 2007/02/12 16:36:58 jvalenti ship $ */
3:
4: C_PKG_NAME CONSTANT VARCHAR2(30) := 'FND_USER_RESP_GROUPS_API';
5: C_LOG_HEAD CONSTANT VARCHAR2(240)
6: := 'fnd.plsql.FND_USER_RESP_GROUPS_API.';
7:
8: -- This is a one level cache used in check_secgrp_enabled.
2: /* $Header: AFSCURGB.pls 120.14.12000000.3 2007/02/12 16:36:58 jvalenti ship $ */
3:
4: C_PKG_NAME CONSTANT VARCHAR2(30) := 'FND_USER_RESP_GROUPS_API';
5: C_LOG_HEAD CONSTANT VARCHAR2(240)
6: := 'fnd.plsql.FND_USER_RESP_GROUPS_API.';
7:
8: -- This is a one level cache used in check_secgrp_enabled.
9: G_ENABLED_RESPID NUMBER := null;
10: G_ENABLED_APPID NUMBER := null;
142: x_secgrp_id in number)
143: return varchar2 is
144: begin
145:
146: return fnd_user_resp_groups_api.Role_Name_from_Resp(
147: x_resp_id,
148: x_resp_appl_id,
149: x_secgrp_id);
150:
163: new_role_name varchar2(255);
164: begin
165:
166: begin
167: new_role_name := fnd_user_resp_groups_api.Role_Name_from_Resp(
168: x_resp_id => respid,
169: x_resp_appl_id => appid,
170: x_secgrp_id => 0);
171:
206: exception
207: when no_data_found then
208: result := FALSE;
209: when others then
210: Generic_Error('FND_USER_RESP_GROUPS_API.ASSIGNMENT_CHECK(E)',
211: sqlcode, sqlerrm);
212: end;
213: elsif (direct_flag = 'D') then
214: begin
223: exception
224: when no_data_found then
225: result := FALSE;
226: when others then
227: Generic_Error('FND_USER_RESP_GROUPS_API.ASSIGNMENT_CHECK(D)',
228: sqlcode, sqlerrm);
229: end;
230: elsif (direct_flag = 'I') then
231: begin
240: exception
241: when no_data_found then
242: result := FALSE;
243: when others then
244: Generic_Error('FND_USER_RESP_GROUPS_API.ASSIGNMENT_CHECK(I)',
245: sqlcode, sqlerrm);
246: end;
247: end if;
248: return result;
296:
297: begin
298: select user_name
299: into username
300: from fnd_user
301: where user_id = assignment_exists.user_id;
302:
303: rolename := role_name_from_resp(responsibility_id,
304: responsibility_application_id,
317: l_direct_flag);
318:
319: exception
320: when others then
321: Generic_Error('FND_USER_RESP_GROUPS_API.ASSIGNMENT_EXISTS',
322: sqlcode, sqlerrm);
323: end Assignment_Exists;
324:
325:
344: x_status out nocopy varchar2)
345: is
346: begin
347: /* # Someday this routine could be reimplemented against the database */
348: /* objects underlying the fnd_user_resp_groups view, but not needed now.*/
349: x_status := 'N';
350:
351: select 'Y'
352: into x_status
352: into x_status
353: from dual
354: where exists
355: (select null
356: from fnd_user u,
357: fnd_user_resp_groups urg,
358: fnd_responsibility r
359: where u.user_id = p_user_id
360: and sysdate between u.start_date and nvl(u.end_date, sysdate)
353: from dual
354: where exists
355: (select null
356: from fnd_user u,
357: fnd_user_resp_groups urg,
358: fnd_responsibility r
359: where u.user_id = p_user_id
360: and sysdate between u.start_date and nvl(u.end_date, sysdate)
361: and urg.user_id = u.user_id
407: begin
408:
409: select user_name
410: into username
411: from fnd_user
412: where user_id = x_user_id;
413:
414: rolename := role_name_from_resp(x_responsibility_id,
415: x_resp_application_id,
490: ':'||responsibility_id;
491:
492: select user_name
493: into username
494: from fnd_user
495: where user_id = Insert_assignment.user_id;
496:
497:
498: select application_short_name
508: fnd_message.set_name('FND', 'FND_CANT_INSERT_USER_ROLE');
509: fnd_message.set_token('USERNAME', username);
510: fnd_message.set_token('ROLENAME', rolename);
511: fnd_message.set_token('ROUTINE',
512: 'FND_USER_RESP_GROUPS_API.Insert_Assignment');
513: app_exception.raise_exception;
514: end if;
515:
516:
524: /* Should never happen but be safe in case sync wasn't perfect in past */
525: /* # Should we remove this code and just trust the bulk sync? */
526: if( (l_user_orig_system is NULL)
527: and (l_user_orig_system_id is NULL)) then
528: fnd_user_pkg.user_synch(username);
529: wf_directory.GetRoleOrigSysInfo(username,
530: l_user_orig_system,
531: l_user_orig_system_id);
532: end if;
582: Insert_Assignment.responsibility_id,
583: null, null);
584: --we have to raise a differnt event as for USER_INFO_CACHE
585: --the key should be just the user_id
586: wf_event.addparametertolist(p_name => 'FND_USER_ID',
587: p_value => Insert_Assignment.user_id,
588: p_parameterlist => l_parameters);
589:
590: wf_event.addparametertolist(p_name => 'FND_RESPONSIBILITY_ID',
598: wf_event.addparametertolist(p_name => 'FND_RESPONSIBILITY_APPS_ID',
599: p_value => Insert_Assignment.responsibility_application_id,
600: p_parameterlist => l_parameters);
601:
602: wf_event.raise(p_event_name => 'oracle.apps.fnd.user.role.insert',
603: p_event_key => to_char(Insert_Assignment.user_id)||':'||to_char(Insert_Assignment.responsibility_id)||':'||appsname||':'||to_char(Insert_Assignment.responsibility_application_id),
604: p_event_data => NULL,
605: p_parameters => l_parameters,
606: p_send_date => Sysdate);
606: p_send_date => Sysdate);
607:
608: exception
609: when others then
610: Generic_Error('FND_USER_RESP_GROUPS_API.INSERT_ASSIGNMENT',
611: sqlcode, sqlerrm);
612: end Insert_Assignment;
613:
614: --
678: ':'||responsibility_id;
679:
680: select user_name
681: into username
682: from fnd_user
683: where user_id = Update_assignment.user_id;
684:
685:
686: select application_short_name
697: fnd_message.set_name('FND', 'FND_CANT_UPDATE_USER_ROLE');
698: fnd_message.set_token('USERNAME', username);
699: fnd_message.set_token('ROLENAME', rolename);
700: fnd_message.set_token('ROUTINE',
701: 'FND_USER_RESP_GROUPS_API.Update_Assignment');
702: app_exception.raise_exception;
703: end if;
704:
705: /* We can't just assume that the orig system is FND_USR. It could */
712: /* Should never happen but be safe in case sync wasn't perfect in past */
713: /* # Should we remove this code and just trust bulk sync?*/
714: if( (l_user_orig_system is NULL)
715: and (l_user_orig_system_id is NULL)) then
716: fnd_user_pkg.user_synch(username);
717: wf_directory.GetRoleOrigSysInfo(username,
718: l_user_orig_system,
719: l_user_orig_system_id);
720: end if;
813: --subscriptions are attcahed to the wf event.
814: --o.k putting our event (but we need to change the b3664848.ldt
815: --and the event-subscription file to attach this event to the
816: --bes control group.
817: wf_event.addparametertolist(p_name => 'FND_USER_ID',
818: p_value => Update_Assignment.user_id,
819: p_parameterlist => l_parameters);
820:
821: wf_event.addparametertolist(p_name => 'FND_RESPONSIBILITY_ID',
829: wf_event.addparametertolist(p_name => 'FND_RESPONSIBILITY_APPS_ID',
830: p_value => Update_Assignment.responsibility_application_id,
831: p_parameterlist => l_parameters);
832:
833: wf_event.raise(p_event_name => 'oracle.apps.fnd.user.role.update',
834: p_event_key => to_char(Update_Assignment.user_id)||':'||to_char(Update_Assignment.responsibility_id)||':'||appsname||':'||to_char(Update_Assignment.responsibility_application_id),
835: p_event_data => NULL,
836: p_parameters => l_parameters,
837: p_send_date => Sysdate);
837: p_send_date => Sysdate);
838:
839: exception
840: when others then
841: Generic_Error('FND_USER_RESP_GROUPS_API.UPDATE_ASSIGNMENT',
842: sqlcode, sqlerrm);
843:
844: end Update_Assignment;
845:
865: old_rolename varchar2(4000);
866:
867: begin
868: select user_id into u_id
869: from fnd_user
870: where user_name = X_USER_NAME;
871:
872: select application_id into app_id
873: from fnd_application
892: -- bug3649874 Modified to use fnd_load_util to get the owner_id
893:
894: l_owner := fnd_load_util.owner_id(X_OWNER);
895:
896: fnd_user_resp_groups_api.UPLOAD_ASSIGNMENT(
897: USER_ID => u_id,
898: RESPONSIBILITY_ID => resp_id,
899: RESPONSIBILITY_APPLICATION_ID => app_id,
900: SECURITY_GROUP_ID => sgroup_id,
1010: else
1011: sgid := security_group_id;
1012: end if;
1013:
1014: if (Fnd_User_Resp_Groups_Api.Assignment_Exists(
1015: Upload_Assignment.user_id,
1016: Upload_Assignment.responsibility_id,
1017: Upload_Assignment.responsibility_application_id,
1018: Upload_Assignment.sgid,
1017: Upload_Assignment.responsibility_application_id,
1018: Upload_Assignment.sgid,
1019: 'D'))
1020: then
1021: Fnd_User_Resp_Groups_Api.Update_Assignment(
1022: Upload_Assignment.user_id,
1023: Upload_Assignment.responsibility_id,
1024: Upload_Assignment.responsibility_application_id,
1025: Upload_Assignment.sgid,
1027: Upload_Assignment.end_date,
1028: Upload_Assignment.description,
1029: update_who_columns);
1030: else
1031: Fnd_User_Resp_Groups_Api.Insert_Assignment(
1032: Upload_Assignment.user_id,
1033: Upload_Assignment.responsibility_id,
1034: Upload_Assignment.responsibility_application_id,
1035: Upload_Assignment.sgid,
1038: Upload_Assignment.description);
1039: end if;
1040: exception
1041: when others then
1042: Generic_Error('FND_USER_RESP_GROUPS_API.UPLOAD_ASSIGNMENT',
1043: sqlcode, sqlerrm);
1044: end Upload_Assignment;
1045:
1046: --
1121: into applsname
1122: from fnd_application
1123: where application_id = appid;
1124:
1125: role_name := fnd_user_resp_groups_api.role_name_from_resp_name(
1126: respkey, applsname, secgrpkey);
1127:
1128: exception
1129: when no_data_found then
1422: end sync_roles_all_resp_secgrps;
1423:
1424:
1425: --
1426: -- Moves old data from fnd_user_resp_groups table to new workflow tables.
1427: -- This routine is exposed so that it can be called from a one time
1428: -- upgrade script, and it should never need to be run after that.
1429: -- Running it unnecessarily might invalidate work that
1430: -- admins have done to split assignments out into roles
1459: furgo.creation_date,
1460: furgo.last_updated_by,
1461: furgo.last_update_date,
1462: furgo.last_update_login
1463: from fnd_user_resp_groups_old furgo,
1464: fnd_user fu,
1465: fnd_application app,
1466: fnd_responsibility resp,
1467: fnd_security_groups secgrp
1460: furgo.last_updated_by,
1461: furgo.last_update_date,
1462: furgo.last_update_login
1463: from fnd_user_resp_groups_old furgo,
1464: fnd_user fu,
1465: fnd_application app,
1466: fnd_responsibility resp,
1467: fnd_security_groups secgrp
1468: where furgo.user_id = fu.user_id
1514: fnd_log.string(FND_LOG.LEVEL_STATEMENT,
1515: c_log_head || l_api_name || '.need_to_create_role',
1516: 'Creating role:'|| rolename);
1517: end if;
1518: fnd_user_resp_groups_api.sync_roles_one_resp_secgrp(
1519: rowrec.responsibility_id,
1520: rowrec.responsibility_application_id,
1521: rowrec.responsibility_key,
1522: rowrec.security_group_id,
1561: c_log_head || l_api_name || '.orig_notfound',
1562: 'Orig system and id not found. Trying to sync user:'||
1563: rowrec.user_name);
1564: end if;
1565: fnd_user_pkg.user_synch(rowrec.user_name);
1566: wf_directory.GetRoleOrigSysInfo(rowrec.user_name,
1567: l_user_orig_system,
1568: l_user_orig_system_id);
1569: end if;
1704:
1705: end sync_roles_all_secgrps_int;
1706:
1707:
1708: end Fnd_User_Resp_Groups_Api;