ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 8F5CB22868CE410293D91BC7A1A8C9C7Y select null action_, null status_, null message_, null modified_columns_, x.*, to_number(null) upload_row from ( select rowidtochar(fpov.rowid) row_id, fpo.user_profile_option_name profile_option, fpo.profile_option_name profile_option_code, decode(fpov.level_id,10001,'Site',10002,'Application',10003,'Responsibility',10004,'User',10005,'Server',10006,'Operating Unit',10007,decode(fpov.level_value,-1,'Server','Responsibility')) level_, decode(fpov.level_id,10002,fav.application_name,10003,frv.responsibility_name,10004,fu.user_name,10005,fn.node_name,10006,haouv.name,10007,nvl(frv.responsibility_name,fn.node_name)) level_value, fav_r.application_name responsibility_application, decode(fpov.level_id,10007,decode(fpov.level_value,-1,null,fn.node_name)) server, xxen_util.display_profile_option_value(fpov.application_id,fpov.profile_option_id,fpov.profile_option_value) profile_option_value from fnd_profile_options_vl fpo, fnd_profile_option_values fpov, fnd_application_vl fav, fnd_responsibility_vl frv, fnd_application_vl fav_r, fnd_user fu, fnd_nodes fn, hr_all_organization_units_vl haouv where fpo.start_date_active<=sysdate and nvl(fpo.end_date_active,sysdate)>=sysdate and fpo.application_id=fpov.application_id and fpo.profile_option_id=fpov.profile_option_id and decode(fpov.level_id,10002,fpov.level_value)=fav.application_id(+) and case when fpov.level_id in (10003,10007) then fpov.level_value end=frv.responsibility_id(+) and case when fpov.level_id in (10003,10007) then fpov.level_value_application_id end=frv.application_id(+) and frv.application_id=fav_r.application_id(+) and decode(fpov.level_id,10004,fpov.level_value)=fu.user_id(+) and decode(fpov.level_id,10005,fpov.level_value,10007,fpov.level_value2)=fn.node_id(+) and decode(fpov.level_id,10006,fpov.level_value)=haouv.organization_id(+) ) x where 1=1 Initial version U A xxen_fnd_upload.upload_profile_option_value US FND Profile Option Value Upload Upload to create, update and delete profile option values on site, application, responsibility, server, operating unit and user level, as in the System Profile Values form. Levels that the profile option definition does not allow to update are not offered. The Value column shows and accepts the visible value of profile options validated by a list of values, e.g. a responsibility name instead of its id. Its list of values is derived from each profile option's SQL validation. A stored value is accepted as well. A few validations cannot be evaluated outside the profile form, e.g. because they depend on the responsibility being updated. These offer no list, save the value as entered and say so in the row message. Clearing the Value of an existing row deletes the profile option value on that level. The Server column is used only for profile options with the server and responsibility hierarchy, to set a value for a responsibility on a specific server. Enginatics 1=1 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,U Y US Upload Mode 2 20 1=1 x.profile_option=:profile_option LOV FND User Profile Name 8E2FF36EDEF679D2E0530100007F1FF2 select fpov.user_profile_option_name value, fpov.profile_option_name description from fnd_profile_options_vl fpov order by fpov.user_profile_option_name US Profile Option 3 30 1=1 x.profile_option_code=:profile_option_code LOV FND System Profile Name 8E2FF36EDEF579D2E0530100007F1FF2 select fpov.profile_option_name value, fpov.user_profile_option_name description from fnd_profile_options_vl fpov order by fpov.profile_option_name US Profile Option Code 4 40 1=1 x.profile_option_code in (select fpo_a.profile_option_name from fnd_profile_options fpo_a, fnd_application_vl fav_a where fpo_a.application_id=fav_a.application_id and fav_a.application_name=:application_name) LOV FND Application Name 8E2FF36EDE9879D2E0530100007F1FF2 Y select fav.application_name value, fav.application_short_name description from fnd_application_vl fav order by fav.application_name US Application Application of the profile option. 5 50 1=1 x.level_=:setup_level LOV custom Y select 'Site' value, null description from dual union all select 'Application' value, null description from dual union all select 'Responsibility' value, null description from dual union all select 'Server' value, null description from dual union all select 'Operating Unit' value, null description from dual union all select 'User' value, null description from dual US Level 6 60 1=1 x.level_value=:level_value LOV custom Y select x.value, x.description from ( select fav.application_name value, fav.application_short_name description, 'Application' level_ from fnd_application_vl fav union all select frv.responsibility_name value, fav.application_name description, 'Responsibility' level_ from fnd_responsibility_vl frv, fnd_application_vl fav where frv.application_id=fav.application_id union all select fn.node_name value, null description, 'Server' level_ from fnd_nodes fn union all select hou.name value, null description, 'Operating Unit' level_ from hr_operating_units hou union all select fu.user_name value, fu.description, 'User' level_ from fnd_user fu ) x where x.level_=:$flex$.level order by x.value US Level Value 1F855DD1CCA843A2AEDF3E91927E1F0F Create Empty sheet to enter new profile option values. ENGINATICS 2 LEVEL_ 3 LEVEL_VALUE 1 PROFILE_OPTION Y 5 PROFILE_OPTION_VALUE 4 RESPONSIBILITY_APPLICATION Upload Mode C Y S Site Application Level Level Value Profile Option Profile Option Code 866FA99F479A420692A8E39EFCE8013C Default All columns. ENGINATICS 3 LEVEL_ Y 4 LEVEL_VALUE 1 PROFILE_OPTION 2 PROFILE_OPTION_CODE 7 PROFILE_OPTION_VALUE 5 RESPONSIBILITY_APPLICATION 6 SERVER Upload Mode C,U S Site 866FA99F479A420692A8E39EFCE8013C 1 ACTION_ Char varchar2 2 STATUS_ Char varchar2 3 MESSAGE_ Char varchar2 4 MODIFIED_COLUMNS_ Char varchar2 Y 5 ROW_ID Char varchar2 18 Y 6 PROFILE_OPTION LOV varchar2 240 FND User Profile Name 8E2FF36EDEF679D2E0530100007F1FF2 select fpov.user_profile_option_name value, fpov.profile_option_name description from fnd_profile_options_vl fpov order by fpov.user_profile_option_name Y 7 PROFILE_OPTION_CODE Char varchar2 80 select fpo.profile_option_name from fnd_profile_options_vl fpo where fpo.user_profile_option_name=:$column$.profile_option Y 8 LEVEL_ LOV custom varchar2 14 Y select x.id, x.value, null description from ( select 10001 id, 'Site' value from fnd_profile_options_vl fpo where fpo.user_profile_option_name=:$column$.profile_option and fpo.site_enabled_flag='Y' and fpo.site_update_allowed_flag='Y' union all select 10002 id, 'Application' value from fnd_profile_options_vl fpo where fpo.user_profile_option_name=:$column$.profile_option and fpo.app_enabled_flag='Y' and fpo.app_update_allowed_flag='Y' and fpo.hierarchy_type='SECURITY' union all select 10003 id, 'Responsibility' value from fnd_profile_options_vl fpo where fpo.user_profile_option_name=:$column$.profile_option and (fpo.resp_enabled_flag='Y' and fpo.resp_update_allowed_flag='Y' and fpo.hierarchy_type='SECURITY' or fpo.serverresp_enabled_flag='Y' and fpo.serverresp_update_allowed_flag='Y' and fpo.hierarchy_type='SERVRESP') union all select 10005 id, 'Server' value from fnd_profile_options_vl fpo where fpo.user_profile_option_name=:$column$.profile_option and (fpo.server_enabled_flag='Y' and fpo.server_update_allowed_flag='Y' and fpo.hierarchy_type='SERVER' or fpo.serverresp_enabled_flag='Y' and fpo.serverresp_update_allowed_flag='Y' and fpo.hierarchy_type='SERVRESP') union all select 10006 id, 'Operating Unit' value from fnd_profile_options_vl fpo where fpo.user_profile_option_name=:$column$.profile_option and fpo.org_enabled_flag='Y' and fpo.org_update_allowed_flag='Y' and fpo.hierarchy_type='ORG' union all select 10004 id, 'User' value from fnd_profile_options_vl fpo where fpo.user_profile_option_name=:$column$.profile_option and fpo.user_enabled_flag='Y' and fpo.user_update_allowed_flag='Y' ) x Y 9 LEVEL_VALUE LOV custom varchar2 240 Y select fav.application_name value, fav.application_short_name description from fnd_application_vl fav where :$column$.level_='Application' union all 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 and :$column$.level_='Responsibility' union all select fn.node_name value, null description from fnd_nodes fn where :$column$.level_='Server' union all select hou.name value, null description from hr_operating_units hou where :$column$.level_='Operating Unit' union all select fu.user_name value, fu.description from fnd_user fu where :$column$.level_='User' 10 RESPONSIBILITY_APPLICATION LOV custom varchar2 240 Y select fav.application_name value, fav.application_short_name description from fnd_responsibility_vl frv, fnd_application_vl fav where :$column$.level_='Responsibility' and frv.responsibility_name=:$column$.level_value and frv.application_id=fav.application_id select fav.application_name from fnd_responsibility_vl frv, fnd_application_vl fav where :$column$.level_='Responsibility' and frv.responsibility_name=:$column$.level_value and frv.application_id=fav.application_id 11 SERVER LOV custom varchar2 30 Y select fn.node_name value, null description from fnd_nodes fn where :$column$.level_='Responsibility' and (select fpo.hierarchy_type from fnd_profile_options_vl fpo where fpo.user_profile_option_name=:$column$.profile_option)='SERVRESP' order by fn.node_name 12 PROFILE_OPTION_VALUE LOV custom varchar2 240 select substrb(lov.column_value,instrb(lov.column_value,'~',1,1)+1) value, nullif(substrb(lov.column_value,1,instrb(lov.column_value,'~',1,1)-1),substrb(lov.column_value,instrb(lov.column_value,'~',1,1)+1)) description from fnd_profile_options_vl fpo, table(xxen_util.profile_option_value_lov(fpo.application_id,fpo.profile_option_id)) lov where fpo.user_profile_option_name=:$column$.profile_option 13 UPLOAD_ROW Number number Y U 1 p_profile_option in Char PROFILE_OPTION U 2 p_level_id in Number LEVEL_ U 3 p_level_value in Char LEVEL_VALUE U 4 p_responsibility_application in Char RESPONSIBILITY_APPLICATION U 5 p_server in Char SERVER U 6 p_profile_option_value in Char PROFILE_OPTION_VALUE U 7 p_row_id in/out Char ROW_ID U 8 status_ out Char STATUS_CODE_ U 9 message_ out Char MESSAGE_ Error SQL Order By upload_row, profile_option, decode(level_,'Site',1,'Application',2,'Responsibility',3,'Server',4,'Operating Unit',5,6), level_value Success SQL select xu.action_, xu.status_, xu.message_, xu.modified_columns_, xu.row_id, xu.profile_option, nvl(fpo.profile_option_name,xu.profile_option_code) profile_option_code, nvl(decode(fpov.level_id,10001,'Site',10002,'Application',10003,'Responsibility',10004,'User',10005,'Server',10006,'Operating Unit',10007,decode(fpov.level_value,-1,'Server','Responsibility')),xu.level_) level_, nvl(decode(fpov.level_id,10002,fav.application_name,10003,frv.responsibility_name,10004,fu.user_name,10005,fn.node_name,10006,haouv.name,10007,nvl(frv.responsibility_name,fn.node_name)),xu.level_value) level_value, nvl2(fpov.rowid,fav_r.application_name,xu.responsibility_application) responsibility_application, nvl2(fpov.rowid,decode(fpov.level_id,10007,decode(fpov.level_value,-1,null,fn.node_name)),xu.server) server, nvl(xxen_util.display_profile_option_value(fpov.application_id,fpov.profile_option_id,fpov.profile_option_value),xu.profile_option_value) profile_option_value, xu.upload_row from xxen_fnd_profile_option_7614_u xu, fnd_profile_option_values fpov, fnd_profile_options_vl fpo, fnd_application_vl fav, fnd_responsibility_vl frv, fnd_application_vl fav_r, fnd_user fu, fnd_nodes fn, hr_all_organization_units_vl haouv where xu.status_code_=xxen_upload.status_success and chartorowid(xu.row_id)=fpov.rowid(+) and fpov.application_id=fpo.application_id(+) and fpov.profile_option_id=fpo.profile_option_id(+) and decode(fpov.level_id,10002,fpov.level_value)=fav.application_id(+) and case when fpov.level_id in (10003,10007) then fpov.level_value end=frv.responsibility_id(+) and case when fpov.level_id in (10003,10007) then fpov.level_value_application_id end=frv.application_id(+) and frv.application_id=fav_r.application_id(+) and decode(fpov.level_id,10004,fpov.level_value)=fu.user_id(+) and decode(fpov.level_id,10005,fpov.level_value,10007,fpov.level_value2)=fn.node_id(+) and decode(fpov.level_id,10006,fpov.level_value)=haouv.organization_id(+)