Blitz Report Execution History

Description
Categories: Enginatics
Repository: Github
History of Blitz Report executions
select
x.request_id,
xxen_util.client_time(x.start_date) start_date,
x.report_name,
x.category,
decode(x.type,'P','Protected','S','System','U','Upload','F','FSG') type,
x.user_name,
x.responsibility_name responsibility,
x.options,
&parameters
x.status,
xxen_util.client_time(x.completion_date) completion_date,
xxen_util.time(x.seconds) time,
x.seconds,
x.row_count,
round(x.row_count/decode(x.seconds,0,0.25,x.seconds),2) rows_second,
x.file_size,
xxen_util.client_time(x.actual_completion_date) request_completion_date,
(x.actual_completion_date-x.completion_date)*86400 file_writing_seconds,
x.run_id,
x.report_id
from
(
select
case when xrr.request_id>-1 then xrr.request_id end request_id,
xxen_util.user_name(xrr.created_by) user_name,
frt.responsibility_name,
coalesce(xrv.report_name,
(select distinct min(xrh.report_name||' (deleted)') keep (dense_rank last order by xrh.creation_date) over () report_name from xxen_reports_h xrh where xrr.report_id=xrh.report_id and xrr.creation_date>xrh.creation_date),
(select fcr.argument1||' (deleted)' from fnd_concurrent_requests fcr where xrr.request_id=fcr.request_id)
) report_name,
xrrpv0.options,
coalesce(xrr.completion_message,
nvl2(fcr.request_id,
decode(fcr.phase_code,'P','Pending - ','R','Running - ')||trim(
xxen_util.meaning(case when not (fcr.phase_code='C' and fcr.status_code='C') then
decode(fcr.phase_code,'P',decode(fcr.hold_flag,'Y','H',decode(sign(fcr.requested_start_date-sysdate),1,'P',fcr.status_code)),'R',decode(fcr.hold_flag,'Y','S',decode(fcr.status_code,'Q', 'B','I', 'B',fcr.status_code)),fcr.status_code)
end,'CP_STATUS_CODE',0)
),
nvl2(xrr.completion_date,null,nvl2(gs.run_id,'Running - Online','Error'))),
'Completed') status,
nvl(xrr.start_date,xrr.creation_date) start_date,
xrr.completion_date completion_date,
round((nvl(xrr.completion_date,nvl2(gs.run_id,sysdate,fcr.actual_completion_date))-nvl(xrr.start_date,xrr.creation_date))*86400) seconds,
xrr.row_count,
xrr.file_size,
fcr.actual_completion_date,
xrv.category,
xrr.type,
xrr.run_id,
xrr.report_id
from
xxen_report_runs xrr,
xxen_reports_v xrv,
fnd_concurrent_requests fcr,
fnd_responsibility_tl frt,
(select distinct xxen_util.instring(gs.action,'|',1) run_id from gv$session gs where regexp_like(gs.action,'^\d+\|.*') and gs.module like 'XXEN_REPORT - %' and gs.status='ACTIVE') gs,
(
select distinct
x.run_id,
listagg(x.parameter_name||': '||x.value,chr(10)) within group (order by x.parameter_id desc) over (partition by x.run_id) options
from
(
select
xrrpv.run_id,
xrrpv.parameter_id,
(select flv.description from fnd_lookup_values flv where xrrpv.parameter_id=flv.tag and flv.lookup_type like 'XXEN_REPORT_TRANSLATIONS' and flv.meaning like 'RUNTIME_OPTIONS.%' and flv.language=userenv('lang') and flv.view_application_id=0) parameter_name,
xrrpv.value
from
xxen_report_run_param_values xrrpv
where
xrrpv.parameter_id<0
) x
) xrrpv0
where
1=1 and
xrr.report_id=xrv.report_id(+) and
xrr.request_id=fcr.request_id(+) and
xrr.responsibility_application_id=frt.application_id(+) and
xrr.responsibility_id=frt.responsibility_id(+) and
frt.language(+)=userenv('lang') and
xrr.run_id=gs.run_id(+) and
xrr.run_id=xrrpv0.run_id(+)
) x
order by
x.run_id desc
Parameter NameSQL textValidation
Report Name
xrr.report_id in (
select xrv0.report_id from xxen_reports_v xrv0 where lower(xrv0.report_name) like lower(:report_name) union
select xrh.report_id from xxen_reports_h xrh where lower(xrh.report_name) like lower(:report_name)
)
LOV
Category
xrr.report_id in (
select
xrca.report_id
from
xxen_report_category_assigns xrca,
xxen_report_categories_v xrcv
where
xrcv.category=:category and
xrcv.category_id=xrca.category_id
)
LOV
Report Name starts with
xrr.report_id in (
select xrv0.report_id from xxen_reports_v xrv0 where lower(xrv0.report_name) like lower(:report_name)||'%' union
select xrh.report_id from xxen_reports_h xrh where lower(xrh.report_name) like lower(:report_name_starts_with)||'%'
)
Char
Submitted by User
xrr.created_by=xxen_util.user_id(:created_by)
LOV
Started within Days
xrr.creation_date>sysdate-:days
Number
Start Date From
xrr.creation_date>=:start_date_from
Date
Start Date To
xrr.creation_date<:start_date_to+1
Date
Show Parameters
xxen_api.parameters(x.run_id) parameters,
LOV
Running or Errored
(xrr.completion_date is null or xrr.completion_message is not null)
LOV
Request Id
xrr.request_id=:request_id
LOV
Run Id
xrr.run_id=:run_id_
LOV
Exclude System Reports
nvl(xrr.type,'x')<>'S'
LOV
Exclude Submissions from User
xrr.created_by<>xxen_util.user_id(:exclude_user)
LOV