ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN E811F942597A2075E0530100007FF4F2 Blitz Upload Mode Y select xxen_upload.action_create id, xxen_upload.action_meaning(xxen_upload.action_create) value, null description from dual union all select xxen_upload.action_create || ',' || xxen_upload.action_update id, xxen_upload.action_meaning(xxen_upload.action_create)||', '||xxen_upload.action_meaning(xxen_upload.action_update) value, null description from dual US 8E2FF36EDEA679D2E0530100007F1FF2 Yes Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual US B8D2F4A6C0E1937584A6B8C0D2E4F607Y select null action_, null status_, null message_, null modified_columns_, x.row_type, x.application, x.responsibility_key, x.responsibility_name, x.description, x.effective_date_from, x.effective_date_to, x.available_from, x.data_group, x.data_group_application, x.menu, x.request_group, x.request_group_application, x.web_host_name, x.web_agent_name, x.exclusion_type, x.exclusion_name, x.exclude, row_number() over (order by decode(x.row_type,'Responsibility',1,2),x.application,x.responsibility_key) upload_row from ( select 'Responsibility' row_type, fav.application_name application, fr.responsibility_key, frt.responsibility_name, frt.description, fr.start_date effective_date_from, fr.end_date effective_date_to, decode(fr.version,'4','Oracle Applications','W','Oracle Self Service Web Applications','M','Oracle Mobile Applications') available_from, fdg.data_group_name data_group, fav_dg.application_name data_group_application, fmv.user_menu_name menu, frg.request_group_name request_group, fav_rg.application_name request_group_application, fr.web_host_name, fr.web_agent_name, to_char(null) exclusion_type, to_char(null) exclusion_name, to_char(null) exclude from fnd_responsibility fr, fnd_responsibility_tl frt, fnd_application_vl fav, fnd_data_groups_standard_view fdg, fnd_application_vl fav_dg, fnd_menus_vl fmv, fnd_request_groups frg, fnd_application_vl fav_rg where 1=1 and fr.application_id=fav.application_id and frt.responsibility_id=fr.responsibility_id and frt.application_id=fr.application_id and frt.language=userenv('lang') and fr.data_group_id=fdg.data_group_id and fr.data_group_application_id=fav_dg.application_id and fr.menu_id=fmv.menu_id and fr.request_group_id=frg.request_group_id(+) and fr.group_application_id=frg.application_id(+) and frg.application_id=fav_rg.application_id(+) union all select 'Menu/Function Exclusion' row_type, fav.application_name application, fr.responsibility_key, to_char(null) responsibility_name, to_char(null) description, to_date(null) effective_date_from, to_date(null) effective_date_to, to_char(null) available_from, to_char(null) data_group, to_char(null) data_group_application, to_char(null) menu, to_char(null) request_group, to_char(null) request_group_application, to_char(null) web_host_name, to_char(null) web_agent_name, xxen_util.meaning(rf.rule_type,'FND_RESP_FUNC_RULE_TYPE',0) exclusion_type, case rf.rule_type when 'F' then (select fffv.user_function_name from fnd_form_functions_vl fffv where fffv.function_id=rf.action_id) when 'M' then (select fmv.user_menu_name from fnd_menus_vl fmv where fmv.menu_id=rf.action_id) end exclusion_name, to_char(null) exclude from fnd_resp_functions rf, fnd_responsibility fr, fnd_responsibility_tl frt, fnd_application_vl fav where 2=2 and rf.application_id=fr.application_id and rf.responsibility_id=fr.responsibility_id and frt.responsibility_id=fr.responsibility_id and frt.application_id=fr.application_id and frt.language=userenv('lang') and fr.application_id=fav.application_id ) x Initial version. Create/update FND responsibilities (FND_RESPONSIBILITY_PKG) and add/remove menu/function exclusions (FND_RESP_FUNCTIONS_PKG) in one multi-entity upload. U A xxen_fnd_upload.create_update_responsibility US FND Responsibility Upload Create and update Oracle E-Business Suite responsibilities, and maintain their menu/function exclusions, replicating the System Administrator Responsibilities form (FNDSCRSP). Each row is one of two entity types, chosen in the Row Type column: - Responsibility: creates or updates the responsibility definition (Name, Application, Key, Description, Effective Dates, Available From, Data Group, Menu, Request Group, Web Host/Agent Name). Responsibilities are identified by Application + Responsibility Key. A responsibility is created when that combination does not yet exist, otherwise it is updated. The Responsibility Key is the stable identifier and is not editable once created - to rename a responsibility change its Name, not its Key. - Menu/Function Exclusion: adds (or, with Exclude = Yes, removes) a menu or function exclusion for the responsibility identified by Application + Responsibility Key. Exclusion Type selects Function or Menu; Exclusion Name selects the item to exclude. When adding a new responsibility and its exclusions in the same upload, place the Responsibility row before its exclusion rows. Upload Modes: - Create opens an empty template. - Create, Update downloads existing responsibilities (and their exclusions) matching the Application / Responsibility filters for review and update. Responsibilities are not deleted by this upload; set an Effective Date To to disable one, matching Oracle best practice. Enginatics Upload 1=1 2=2 1 10 1=1 :p_upload_mode like '%' || xxen_upload.action_update LOV Blitz Upload Mode E811F942597A2075E0530100007FF4F2 Y select xxen_upload.action_create id, xxen_upload.action_meaning(xxen_upload.action_create) value, null description from dual union all select xxen_upload.action_create || ',' || xxen_upload.action_update id, xxen_upload.action_meaning(xxen_upload.action_create)||', '||xxen_upload.action_meaning(xxen_upload.action_update) value, null description from dual C Y US Upload Mode 2 2=2 :p_upload_mode like '%' || xxen_upload.action_update US Upload Mode 3 20 1=1 fav.application_name=:application_name LOV custom Y select fav.application_name value, fav.application_short_name description from fnd_application_vl fav order by fav.application_name US Application 4 2=2 fav.application_name=:application_name US Application 5 30 1=1 frt.responsibility_name=:responsibility_name LOV custom Y select frv.responsibility_name value, fav.application_name description from fnd_responsibility_vl frv, fnd_application_vl fav where frv.application_id=fav.application_id order by frv.responsibility_name US Responsibility Name 6 2=2 frt.responsibility_name=:responsibility_name US Responsibility Name 1 ACTION_ Char varchar2 2 STATUS_ Char varchar2 3 MESSAGE_ Char varchar2 4 MODIFIED_COLUMNS_ Char varchar2 Y 5 ROW_TYPE LOV custom varchar2 30 Y select 'Responsibility' value, null description from dual union all select 'Menu/Function Exclusion' value, null description from dual Responsibility Y 6 APPLICATION LOV custom varchar2 240 Y select fav.application_id id, fav.application_name value, fav.application_short_name description from fnd_application_vl fav order by fav.application_name Y 7 RESPONSIBILITY_KEY Char varchar2 30 Y 8 RESPONSIBILITY_NAME Char varchar2 100 9 DESCRIPTION Char varchar2 240 10 EFFECTIVE_DATE_FROM Date date trunc(sysdate) 11 EFFECTIVE_DATE_TO Date date 12 AVAILABLE_FROM LOV custom varchar2 40 Y select '4' id, 'Oracle Applications' value, null description from dual union all select 'W' id, 'Oracle Self Service Web Applications' value, null description from dual union all select 'M' id, 'Oracle Mobile Applications' value, null description from dual Oracle Applications 13 DATA_GROUP LOV custom varchar2 240 Y select fdg.data_group_id id, fdg.data_group_name value, fdg.description from fnd_data_groups_standard_view fdg order by fdg.data_group_name Standard 14 DATA_GROUP_APPLICATION LOV custom varchar2 240 Y select fav.application_id id, fav.application_name value, fou.oracle_username description from fnd_data_group_units fdgu, fnd_data_groups_standard_view fdg, fnd_application_vl fav, fnd_oracle_userid fou where fdg.data_group_name=:$column$.data_group and fdgu.data_group_id=fdg.data_group_id and fdgu.application_id=fav.application_id and fdgu.oracle_id=fou.oracle_id order by fav.application_name 15 MENU LOV custom varchar2 80 Y select fmv.menu_id id, fmv.user_menu_name value, fmv.menu_name description from fnd_menus_vl fmv where fmv.user_menu_name is not null order by fmv.user_menu_name 16 REQUEST_GROUP LOV custom varchar2 60 Y select rg.request_group_name value, fav.application_name description from fnd_request_groups rg, fnd_application_vl fav where rg.application_id=fav.application_id order by rg.request_group_name 17 REQUEST_GROUP_APPLICATION LOV custom varchar2 240 Y select fav.application_name value, null description from fnd_request_groups rg, fnd_application_vl fav where rg.application_id=fav.application_id and rg.request_group_name=:$column$.request_group order by fav.application_name select fav.application_name from fnd_request_groups rg, fnd_application_vl fav where rg.application_id=fav.application_id and rg.request_group_name=:$column$.request_group 18 WEB_HOST_NAME Char varchar2 80 19 WEB_AGENT_NAME Char varchar2 80 20 EXCLUSION_TYPE LOV custom varchar2 30 Y select flvv.lookup_code id, flvv.meaning value, flvv.description from fnd_lookup_values_vl flvv where flvv.lookup_type='FND_RESP_FUNC_RULE_TYPE' and flvv.security_group_id=0 and flvv.enabled_flag='Y' order by flvv.meaning 21 EXCLUSION_NAME LOV custom varchar2 80 Y select fffv.function_id id, fffv.user_function_name value, fffv.function_name description from fnd_form_functions_vl fffv where xxen_util.lookup_code(:$column$.exclusion_type,'FND_RESP_FUNC_RULE_TYPE',0)='F' union all select fmv.menu_id id, fmv.user_menu_name value, fmv.menu_name description from fnd_menus_vl fmv where xxen_util.lookup_code(:$column$.exclusion_type,'FND_RESP_FUNC_RULE_TYPE',0)='M' order by value 22 EXCLUDE LOV varchar2 Yes 8E2FF36EDEA679D2E0530100007F1FF2 Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual 23 UPLOAD_ROW Number number Y U 1 status_ out Char STATUS_CODE_ U 2 message_ out Char MESSAGE_ U 3 p_row_type in Char ROW_TYPE U 4 p_application_id in Number APPLICATION U 5 p_responsibility_key in Char RESPONSIBILITY_KEY U 6 p_responsibility_name in Char RESPONSIBILITY_NAME U 7 p_description in Char DESCRIPTION U 8 p_start_date in Date EFFECTIVE_DATE_FROM U 9 p_end_date in Date EFFECTIVE_DATE_TO U 10 p_version in Char AVAILABLE_FROM U 11 p_data_group_id in Number DATA_GROUP U 12 p_data_group_application_id in Number DATA_GROUP_APPLICATION U 13 p_menu_id in Number MENU U 14 p_request_group in Char REQUEST_GROUP U 15 p_request_group_application in Char REQUEST_GROUP_APPLICATION U 16 p_web_host_name in Char WEB_HOST_NAME U 17 p_web_agent_name in Char WEB_AGENT_NAME U 18 p_rule_type in Char EXCLUSION_TYPE U 19 p_action_id in Number EXCLUSION_NAME U 20 p_exclude in Char EXCLUDE Error SQL Order By upload_row, application, responsibility_key, decode(row_type,'Responsibility',1,2), exclusion_name Success SQL select xu.action_, xu.status_, xu.message_, xu.modified_columns_, xu.row_type, nvl(fav.application_name,xu.application) application, nvl(fr.responsibility_key,xu.responsibility_key) responsibility_key, nvl(frt.responsibility_name,xu.responsibility_name) responsibility_name, nvl(frt.description,xu.description) description, fr.start_date effective_date_from, fr.end_date effective_date_to, decode(fr.version,'4','Oracle Applications','W','Oracle Self Service Web Applications','M','Oracle Mobile Applications') available_from, fdg.data_group_name data_group, fav_dg.application_name data_group_application, fmv.user_menu_name menu, frg.request_group_name request_group, fav_rg.application_name request_group_application, fr.web_host_name, fr.web_agent_name, to_char(null) exclusion_type, to_char(null) exclusion_name, to_char(null) exclude, to_number(xu.upload_row) upload_row from xxen_fnd_responsibility_1750_u xu, fnd_application_vl fav, fnd_responsibility fr, fnd_responsibility_tl frt, fnd_data_groups_standard_view fdg, fnd_application_vl fav_dg, fnd_menus_vl fmv, fnd_request_groups frg, fnd_application_vl fav_rg where xu.status_code_='S' and xu.row_type='Responsibility' and xu.application=fav.application_name and fav.application_id=fr.application_id and fr.responsibility_key=upper(xu.responsibility_key) and fr.responsibility_id=frt.responsibility_id(+) and fr.application_id=frt.application_id(+) and frt.language(+)=userenv('lang') and fr.data_group_id=fdg.data_group_id(+) and fr.data_group_application_id=fav_dg.application_id(+) and fr.menu_id=fmv.menu_id(+) and fr.request_group_id=frg.request_group_id(+) and fr.group_application_id=frg.application_id(+) and frg.application_id=fav_rg.application_id(+) union all select xu.action_, xu.status_, xu.message_, xu.modified_columns_, xu.row_type, xu.application, nvl(fr.responsibility_key,xu.responsibility_key) responsibility_key, to_char(null) responsibility_name, to_char(null) description, to_date(null) effective_date_from, to_date(null) effective_date_to, to_char(null) available_from, to_char(null) data_group, to_char(null) data_group_application, to_char(null) menu, to_char(null) request_group, to_char(null) request_group_application, to_char(null) web_host_name, to_char(null) web_agent_name, xu.exclusion_type, xu.exclusion_name, xu.exclude, to_number(xu.upload_row) upload_row from ( select xu.action_, xu.status_, xu.message_, xu.modified_columns_, xu.row_type, nvl(fav.application_name,xu.application) application, xu.responsibility_key, xu.exclusion_type, xu.exclusion_name, xu.exclude, xu.upload_row, fav.application_id from xxen_fnd_responsibility_1750_u xu, fnd_application_vl fav where xu.status_code_='S' and xu.row_type='Menu/Function Exclusion' and xu.application=fav.application_name(+) ) xu, --before 12c an outer joined table may reference at most one other table, so the driving tables are wrapped fnd_responsibility fr where xu.application_id=fr.application_id(+) and upper(xu.responsibility_key)=fr.responsibility_key(+)