ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 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 maintain the values of profile option Blitz Report Access at user and responsibility level. Users of this upload can set the access of other users and responsibilities up to their own access level. With User Admin access, for example, the value can be set to User or User Admin, but not to Developer or System. Clearing Access on a downloaded row deletes the profile option value at that level, so the value of the next level applies. 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 :p_upload_mode like '%' || xxen_upload.action_update LOV Blitz Upload Mode E811F942597A2075E0530100007FF4F2 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 3 20 1=1 decode(:level_, 'User', 10004, 'Responsibility', 10003)=fpov.level_id LOV Blitz Report Access Level 12E95C50531C90BDE0630100007FACBB select 'User' value, null description from dual union all select 'Responsibility' value, null description from dual US Level 4 30 1=1 decode(fpov.level_id, 10003, frv.responsibility_name, 10004, fu.user_name)=:value LOV custom 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 F31BAE6BA864457089E9EDF78677E60F Default All columns. ENGINATICS 5 ACCESS_ 4 DESCRIPTION Y 2 LEVEL_ 1 PROFILE_OPTION_NAME 3 VALUE Upload Mode C,U S Site F31BAE6BA864457089E9EDF78677E60F 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 select 'User' value, null description from dual union all select 'Responsibility' value, null description from dual Y 7 VALUE LOV custom varchar2 100 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 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 xm.action_, xm.status_, 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.action_, xm.status_, 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 decode(xm.level_,'User',xm.user_id,'Responsibility',xm.responsibility_id)=fpov.level_value(+) and decode(xm.level_,'User',10004,'Responsibility',10003)=fpov.level_id(+) and xm.application_id=fpov.application_id(+) and xm.profile_option_id=fpov.profile_option_id(+)