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 |
