ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 12E95C50531C90BDE0630100007FACBB Blitz Report Access Level Y select 'User' value, null description from dual union all select 'Responsibility' value, null description from dual US 0F0A135237213D43E0630100007F1319Y select null action_, null status_, null message_, null modified_columns_, fpo.user_profile_option_name profile_option_name, decode(fpov.level_id, 10003, 'Responsibility', 10004, 'User') level_, decode(fpov.level_id, 10003, frv.responsibility_name, 10004, fu.user_name) value, decode(fpov.level_id, 10003, frv.description, 10004, fu.description) description, case when fpov.profile_option_value='U' then 'User' when fpov.profile_option_value='A' then 'User Admin' when fpov.profile_option_value='D' then 'Developer' when fpov.profile_option_value='S' then 'System' end access_, to_number(null) upload_row from fnd_profile_options_vl fpo, fnd_profile_option_values fpov, fnd_user fu, fnd_responsibility_vl frv, fnd_application_vl fav, fnd_application_vl favr where 1=1 and fpov.level_id in (10003,10004) and fpov.level_value=fu.user_id(+) and fpov.level_value=frv.responsibility_id(+) and frv.application_id=favr.application_id(+) and fav.application_id=fpo.application_id and fpo.profile_option_id=fpov.profile_option_id and fpo.application_id=fpov.application_id and fpo.profile_option_name='XXEN_REPORT_ACCESS' Convert result SQL from the legacy lexical style to the standard Error/Success/Order By style; add the hidden upload_row column; order the result by upload_row (the order rows were uploaded). U A xxen_fnd_upload.update_blitz_access US Blitz Report Access Upload Upload to update value for profile option 'Blitz Report Access' at user or responsibility level. Someone having this upload assigned, can maintain the profile option values for other users or responsibilities up to their own access level. He could, for example, set the value to 'User' for other users, but not to anything higher, such as 'Developer'. Someone with Developer access could set the value for other users and developers, but not to the highest level 'System'. Enginatics Upload 1=1 1 -10 1=1 :current_user_access=:current_user_access Char fnd_profile.value('XXEN_REPORT_ACCESS') US Current User Access 2 10 1=1 decode(:level_, 'User', 10004, 'Responsibility', 10003)=fpov.level_id LOV Blitz Report Access Level 12E95C50531C90BDE0630100007FACBB Y select 'User' value, null description from dual union all select 'Responsibility' value, null description from dual US Level 3 20 1=1 decode(fpov.level_id, 10003, frv.responsibility_name, 10004, fu.user_name)=:value LOV custom Y select fu.user_name value, fu.description from fnd_user fu where trunc(nvl(fu.end_date,sysdate+1))>trunc(sysdate) and :$flex$.level='User' union all select frv.responsibility_name value, frv.description from fnd_responsibility_vl frv where trunc(nvl(frv.end_date,sysdate+1))>trunc(sysdate) and :$flex$.level='Responsibility' US Value 1 ACTION_ Char varchar2 2 STATUS_ Char varchar2 3 MESSAGE_ Char varchar2 4 MODIFIED_COLUMNS_ Char varchar2 Y 5 PROFILE_OPTION_NAME Char varchar2 240 select fpov.user_profile_option_name from fnd_profile_options_vl fpov where fpov.profile_option_name='XXEN_REPORT_ACCESS' 6 LEVEL_ LOV varchar2 14 Blitz Report Access Level 12E95C50531C90BDE0630100007FACBB Y select 'User' value, null description from dual union all select 'Responsibility' value, null description from dual Y 7 VALUE LOV custom varchar2 100 Y select fu.user_name value, fu.description from fnd_user fu where trunc(nvl(fu.end_date,sysdate+1))>trunc(sysdate) and :$column$.level_='User' union all select frv.responsibility_name value, frv.description from fnd_responsibility_vl frv where trunc(nvl(frv.end_date,sysdate+1))>trunc(sysdate) and :$column$.level_='Responsibility' select fu.user_id id, fu.user_name value from fnd_user fu where :$column$.level_='User' union all select frv.responsibility_id id, frv.responsibility_name value from fnd_responsibility_vl frv where :$column$.level_='Responsibility' 8 DESCRIPTION Char varchar2 240 select fu.description from fnd_user fu where trunc(nvl(fu.end_date,sysdate+1))>trunc(sysdate) and :$column$.level_='User' and :$column$.value=fu.user_name union all select frv.description from fnd_responsibility_vl frv where trunc(nvl(frv.end_date,sysdate+1))>trunc(sysdate) and :$column$.level_='Responsibility' and :$column$.value=frv.responsibility_name 9 ACCESS_ LOV custom varchar2 10 Y select x.value, x.description from ( select 'User' value, null description, 1 level_rank from dual union all select 'User Admin' value, null description, 2 level_rank from dual union all select 'Developer' value, null description, 3 level_rank from dual union all select 'System' value, null description, 4 level_rank from dual ) x where x.level_rank<=decode(:$flex$.current_user_access,'U', 1, 'A', 2, 'D', 3, 'S',4) select 'U' id, 'User' value from dual union all select 'A' id, 'User Admin' value from dual union all select 'D' id, 'Developer' value from dual union all select 'S' id, 'System' value from dual 10 UPLOAD_ROW Number number Y U 1 p_level in Char LEVEL_ U 2 p_value in Number VALUE U 3 p_profile_option_value in Char ACCESS_ U 4 action_ in Char ACTION_CODE_ U 5 status_ out Char STATUS_CODE_ U 6 message_ out Char MESSAGE_ Error SQL Order By upload_row, profile_option_name, level_ Success SQL select xxen_upload.action_meaning(xxen_upload.action_update) action_, xxen_upload.status_meaning(xxen_upload.status_success) action_, xm.message_, xm.modified_columns_, nvl(xm.user_profile_option_name,xm.profile_option_name) profile_option_name, nvl(decode(fpov.level_id, 10003, 'Responsibility', 10004, 'User'), xm.level_) level_, nvl(decode(fpov.level_id, 10003, xm.responsibility_name, 10004, xm.user_name),xm.value) value, nvl(decode(fpov.level_id, 10003, xm.responsibility_description, 10004, xm.user_description),xm.description) description, nvl( case when fpov.profile_option_value='U' then 'User' when fpov.profile_option_value='A' then 'User Admin' when fpov.profile_option_value='D' then 'Developer' when fpov.profile_option_value='S' then 'System' end, xm.access_) access_, xm.upload_row from ( select xm.message_, xm.modified_columns_, xm.profile_option_name, xm.level_, xm.value, xm.description, xm.access_, xm.upload_row, fpo.user_profile_option_name, fpo.application_id, fpo.profile_option_id, fu.user_id, fu.user_name, fu.description user_description, frv.responsibility_id, frv.responsibility_name, frv.description responsibility_description from xxen_blitz_report_acces_2812_u xm, fnd_profile_options_vl fpo, fnd_user fu, fnd_responsibility_vl frv, fnd_application_vl fav, fnd_application_vl favr where xm.status_code_=xxen_upload.status_success and xm.value=fu.user_name(+)and xm.value=frv.responsibility_name(+)and frv.application_id=favr.application_id(+) and fpo.application_id =fav.application_id(+) and fpo.profile_option_name(+)='XXEN_REPORT_ACCESS' ) xm, --before 12c an outer joined table may reference at most one other table, so the driving tables are wrapped fnd_profile_option_values fpov where xm.user_id=fpov.level_value(+) and xm.responsibility_id=fpov.level_value(+) and xm.application_id=fpov.application_id(+) and xm.profile_option_id=fpov.profile_option_id(+)