ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 5C69C16CFAB27276E0630100007F9044Y select y.report_id "EIS Report ID", y.application, y.category, y.report_name, y.report_set, y.assignment_level, y.assignment_value, y.description, xxen_util.yes(y.active) active, xxen_util.yes(y.blitz_enabled) blitz_enabled, xxen_util.yes(y.xxeis_responsibility) "XXEIS Responsibility", xxen_util.yes(y.edit_enabled) edit_enabled, y.start_date, y.end_date, y.blitz_report, to_char(null) type, xxen_util.meaning('I','INCLUDE_EXCLUDE',0) include_exclude, to_char(null) users, to_char(null) form_block, to_char(null) parameter_name, to_char(null) block_name, to_char(null) item_name, to_char(null) "Id or Value", to_char(null) constant_value, xxen_util.yes(y.disabled) disabled, xxen_util.user_name(y.created_by) created_by, xxen_util.client_time(y.creation_date) creation_date, xxen_util.user_name(y.last_updated_by) last_updated_by, xxen_util.client_time(y.last_update_date) last_update_date, to_char(null) delete_ from ( select x.report_id, era.application_name application, errc.category_name category, err.report_name, err.seeded_flag, err.enable_report, errs_set.report_set_name report_set, x.assignment_level, x.user_id, x.responsibility_id, x.request_group_id, x.application_id, coalesce(fu.user_name,frv.responsibility_name,frg.request_group_name) assignment_value, case when x.assignment_level='User' then coalesce(trim(papf.first_name||' '||papf.last_name),fu.description,fu.email_address,papf.email_address) else fav.application_name end description, case when x.assignment_level<>'User' then z.active when sysdate between fu.start_date and nvl(fu.end_date,sysdate) then 'Y' end active, z.blitz_enabled, case when x.assignment_level='Responsibility' and fav.application_short_name='XXEIS' then 'Y' end xxeis_responsibility, x.edit_enabled, x.start_date, x.end_date, case when x.start_date>sysdate or x.end_date<=sysdate then 'Y' end disabled, coalesce(xrt_id.report_name,xrt_name.report_name) blitz_report, x.created_by, x.creation_date, x.last_updated_by, x.last_update_date from ( select coalesce(errs.report_id,errsu.report_id) report_id, case when errs.user_id is not null then 'User' when errs.responsibility_id is not null then 'Responsibility' else 'Request Group' end assignment_level, coalesce(errs.user_id,errs.responsibility_id,errs.request_group_id) id1, errs.report_set_id, errs.user_id, errs.responsibility_id, errs.request_group_id, errs.application_id, errs.edit_enabled, errs.start_date, errs.end_date, errs.created_by, errs.creation_date, errs.last_updated_by, errs.last_update_date from xxeis.eis_rs_report_security errs, xxeis.eis_rs_report_set_units errsu where errs.report_set_id=errsu.report_set_id(+) ) x, xxeis.eis_rs_reports err, xxeis.eis_rs_applications era, xxeis.eis_rs_report_categories errc, xxeis.eis_rs_report_sets errs_set, fnd_user fu, (select papf.* from per_all_people_f papf where sysdate between papf.effective_start_date and papf.effective_end_date) papf, fnd_responsibility_vl frv, fnd_request_groups frg, fnd_application_vl fav, ( select case when grouping(fr.responsibility_id)=0 then 'Responsibility' when grouping(fr.request_group_id)=0 then 'Request Group' else 'User' end assignment_level, case when grouping(fr.responsibility_id)=0 then fr.responsibility_id when grouping(fr.request_group_id)=0 then fr.request_group_id else furg.user_id end id1, case when grouping(fr.responsibility_id)=0 then fr.application_id when grouping(fr.request_group_id)=0 then fr.group_application_id else -1 end id2, max(case when sysdate between fr.start_date and nvl(fr.end_date,sysdate) then 'Y' end) active, max(case when sysdate between fr.start_date and nvl(fr.end_date,sysdate) and fcmf.menu_id is not null then 'Y' end) blitz_enabled from fnd_responsibility fr, (select distinct fcmf.menu_id from fnd_form_functions fff, fnd_compiled_menu_functions fcmf where fff.function_name like 'XXEN_REPORTS%' and fff.function_id=fcmf.function_id and fcmf.grant_flag='Y') fcmf, ( select furg.user_id, furg.responsibility_id, furg.responsibility_application_id from fnd_user_resp_groups furg where furg.user_id in (select errs.user_id from xxeis.eis_rs_report_security errs) and sysdate between furg.start_date and nvl(furg.end_date,sysdate) ) furg where fr.menu_id=fcmf.menu_id(+) and fr.responsibility_id=furg.responsibility_id(+) and fr.application_id=furg.responsibility_application_id(+) group by grouping sets ((fr.responsibility_id,fr.application_id),(fr.request_group_id,fr.group_application_id),furg.user_id) ) z, ( select err.report_id, min(xrt.report_id) keep (dense_rank first order by decode(xrt.report_name,err.report_name,1)) blitz_report_id from xxen_reports_tl xrt, xxeis.eis_rs_reports err where xrt.language=xxen_util.base_language and xrt.description like '%EIS Report ID: %' and to_number(regexp_substr(xrt.description,'^EIS Report ID: ([0-9]+)',1,1,'m',1))=err.report_id group by err.report_id ) xrt_eis, xxen_reports_tl xrt_id, xxen_reports_tl xrt_name where x.report_id=err.report_id and err.application_id=era.application_id(+) and err.category_id=errc.category_id(+) and x.report_set_id=errs_set.report_set_id(+) and x.user_id=fu.user_id(+) and fu.employee_id=papf.person_id(+) and x.responsibility_id=frv.responsibility_id(+) and x.application_id=frv.application_id(+) and x.request_group_id=frg.request_group_id(+) and x.application_id=frg.application_id(+) and x.application_id=fav.application_id(+) and x.assignment_level=z.assignment_level(+) and x.id1=z.id1(+) and nvl(x.application_id,-1)=z.id2(+) and err.report_id=xrt_eis.report_id(+) and xrt_eis.blitz_report_id=xrt_id.report_id(+) and xrt_id.language(+)=userenv('lang') and err.report_name=xrt_name.report_name(+) and xrt_name.language(+)=userenv('lang') ) y where 1=1 order by y.application, y.category, y.report_name, y.assignment_level, y.assignment_value New report: EIS eXpress report grants mapped to Blitz Report assignments, with a template in the column layout of the Blitz Report Assignment Upload. US EIS Report Security One row per EIS eXpress report grant, with report set grants expanded to the reports of the set, mapped to the Blitz Report assignment of the same level: User, Responsibility or Request Group. Description holds the application of a responsibility or request group, which the Blitz Report Assignment Upload needs to identify it. Active: the user or responsibility is active, or the request group belongs to an active responsibility. The Assignment Upload accepts only active values. Blitz Enabled: the grant reaches an active responsibility with Blitz Report (function XXEN_REPORTS) on its menu. Blitz Report assignments apply only in such responsibilities. XXEIS Responsibility: one of the responsibilities of EIS itself. Edit Enabled: the user may change the EIS report. Blitz Report has no per-report equivalent. Blitz Report: the report whose description carries the EIS Report ID line written by the EIS import, whatever its name, or else a Blitz Report of the same name. Template Assignment Upload lists the columns of the Blitz Report Assignment Upload in upload order, with the Blitz Report name in the upload's Report Name position, so that its output can be pasted into that upload. It restricts to active values and imported reports, whatever their run history, and leaves out XXEIS responsibilities. Type stays blank and Category carries the EIS category; the upload processes neither. A grant outside its start and end dates becomes a disabled assignment. Executed within Days keeps the reports run from eXpress or XL Connect in that window. EIS purges its run history; EIS Execution History shows the earliest retained run. EIS 1=1 1 10 1=1 y.application=:application LOV custom Y select era.application_name value, era.description from xxeis.eis_rs_applications era order by era.application_name US Application 2 20 1=1 y.category=:category LOV custom Y select distinct errc.category_name value, null description from xxeis.eis_rs_report_categories errc, xxeis.eis_rs_applications era where errc.application_id=era.application_id and (:$flex$.application is null or xxen_util.contains(:$flex$.application,era.application_name)='Y') order by errc.category_name US Category 3 30 1=1 y.report_name=:report_name LOV custom Y select err.report_name value, err.description from xxeis.eis_rs_reports err order by err.report_name US Report Name 4 40 1=1 y.seeded_flag='Y' LOV Oracle Yes_No Y select lookup_code id, meaning value, null description from fnd_lookups where fnd_lookups.lookup_type='YES_NO' order by value,description Y US Seeded 5 1=1 y.seeded_flag is null N US Seeded 6 50 1=1 y.enable_report=:enabled LOV Oracle Yes_No Y select lookup_code id, meaning value, null description from fnd_lookups where fnd_lookups.lookup_type='YES_NO' order by value,description US Enabled 7 60 1=1 y.report_id in (select /*+ no_unnest */ erp.report_id from xxeis.eis_rs_processes erp where erp.submission_source in ('eXpress','XL Connect') and erp.start_time>=sysdate-:executed_within_days) Number 365 US Executed within Days Reports run within this many days before today. Blank includes reports without runs. 8 70 1=1 ( y.user_id=xxen_util.user_id(:user_name) or (y.responsibility_id,y.application_id) in (select furg.responsibility_id, furg.responsibility_application_id from fnd_user_resp_groups furg where furg.user_id=xxen_util.user_id(:user_name) and sysdate between furg.start_date and nvl(furg.end_date,sysdate)) or (y.request_group_id,y.application_id) in (select fr.request_group_id, fr.group_application_id from fnd_user_resp_groups furg, fnd_responsibility fr where furg.user_id=xxen_util.user_id(:user_name) and sysdate between furg.start_date and nvl(furg.end_date,sysdate) and furg.responsibility_id=fr.responsibility_id and furg.responsibility_application_id=fr.application_id) ) LOV FND User Name 8E2FF36EDE8479D2E0530100007F1FF2 Y Y select fu.user_name value, trim(coalesce( trim(papf.first_name||' '||papf.last_name), fu.description, fu.email_address, papf.email_address )||fu.inactive) description from (select case when sysdate between fu.start_date and nvl(fu.end_date,sysdate) then null else ' (inactive)' end inactive, fu.* from fnd_user fu) fu, (select papf.* from per_all_people_f papf where sysdate between papf.effective_start_date and papf.effective_end_date) papf, ( select distinct furg.user_id, count(*) over (partition by furg.user_id) resp_count, max(fr.responsibility_key) over (partition by furg.user_id) max_responsibility_key from fnd_responsibility fr, fnd_user_resp_groups_direct furg where fr.responsibility_id=furg.responsibility_id and fr.application_id=furg.responsibility_application_id ) furg where fu.employee_id=papf.person_id(+) and fu.user_id=furg.user_id(+) and not (furg.resp_count=1 and furg.max_responsibility_key='IRC_EXT_CANDIDATE') order by fu.inactive desc, fu.user_name US User Grants that give the user access: on the user, on the user's responsibilities and on their request groups. 9 80 1=1 ( (y.responsibility_id,y.application_id) in (select frv.responsibility_id, frv.application_id from fnd_responsibility_vl frv where frv.responsibility_name=:responsibility) or (y.request_group_id,y.application_id) in (select frv.request_group_id, frv.group_application_id from fnd_responsibility_vl frv where frv.responsibility_name=:responsibility) ) LOV FND Responsibility Name 8E2FF36EDE9E79D2E0530100007F1FF2 Y select frv.responsibility_name value, frv.description from fnd_responsibility_vl frv order by frv.responsibility_name US Responsibility Grants on the responsibility and on its request group. 10 90 1=1 y.assignment_level=:assignment_level LOV custom Y select 'Responsibility' value, null description from dual union all select 'Request Group', null from dual union all select 'User', null from dual US Assignment Level 11 100 1=1 y.active='Y' LOV Oracle Yes_No Y select lookup_code id, meaning value, null description from fnd_lookups where fnd_lookups.lookup_type='YES_NO' order by value,description Y US Active Active users and responsibilities, and request groups of an active responsibility. 12 1=1 y.active is null N US Active 13 110 1=1 y.blitz_enabled='Y' LOV Oracle Yes_No Y select lookup_code id, meaning value, null description from fnd_lookups where fnd_lookups.lookup_type='YES_NO' order by value,description Y US Blitz Enabled Grants that reach an active responsibility with Blitz Report on its menu. 14 1=1 y.blitz_enabled is null N US Blitz Enabled 15 120 1=1 y.xxeis_responsibility='Y' LOV Oracle Yes_No Y select lookup_code id, meaning value, null description from fnd_lookups where fnd_lookups.lookup_type='YES_NO' order by value,description Y US XXEIS Responsibility Grants on responsibilities of the XXEIS application. 16 1=1 y.xxeis_responsibility is null N US XXEIS Responsibility 17 130 1=1 y.blitz_report is not null LOV Oracle Yes_No Y select lookup_code id, meaning value, null description from fnd_lookups where fnd_lookups.lookup_type='YES_NO' order by value,description Y US Imported Reports that exist in Blitz Report, created by the EIS import or of the same name. 18 1=1 y.blitz_report is null N US Imported