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 0F0A135237213D43E0630100007F1319 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_ 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' &not_use_first_block &report_table_select &report_table_name &report_table_where_clause &success_records &processed_run order by 5,6 Y 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 &not_use_first_block &processed_run &report_table_name &report_table_select &report_table_where_clause &success_records 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 :$flex$.level Level 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 select fpov.user_profile_option_name from fnd_profile_options_vl fpov where fpov.profile_option_name='XXEN_REPORT_ACCESS' 6 LEVEL_ LOV varchar2 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 Y 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 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 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 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_ Lexical &not_use_first_block and 1=0 Lexical &processed_run and 'Y'='Y' Lexical &report_table_name xxen_blitz_report_acces_2812_u Lexical &report_table_select union all select xm.action_, xm.status_code_, xm.message_, xm.modified_columns_, xm.profile_option_name, xm.level_, xm.value, xm.description, xm.access_ from Lexical &report_table_where_clause xm where xm.status_code_=xxen_upload.status_error Lexical &success_records union all 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(fpo.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, frv.responsibility_name, 10004, fu.user_name),xm.value) value, nvl(decode(fpov.level_id, 10003, frv.description, 10004, fu.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_ from &report_table_name xm, 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 xm.status_code_=xxen_upload.status_success and xm.value=fu.user_name(+)and xm.value=frv.responsibility_name(+)and fu.user_id=fpov.level_value(+) and frv.responsibility_id=fpov.level_value(+) and frv.application_id=favr.application_id(+) and fpo.application_id=fpov.application_id(+) and fpo.application_id =fav.application_id(+) and fpo.profile_option_id=fpov.profile_option_id(+) and fpo.profile_option_name(+)='XXEN_REPORT_ACCESS' LOV query :$flex$.current_user_access Current User Access|1=1| ACCESS_ Default value :$column$.level_ LEVEL_ DESCRIPTION Default value :$column$.value VALUE DESCRIPTION LOV query :$column$.level_ LEVEL_ VALUE Value to id query :$column$.level_ LEVEL_ p_value