Blitz Report Execution History

➜ Back to Library
Description
Categories: Enginatics
Repository: Github
History of Blitz Report executions
Run Blitz Report Execution History and other Oracle EBS reports with Blitz Report™ on our demo environment
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,
x.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,
y.parameters,
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(xrr.active_session,'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(xrr.active_session,sysdate,fcr.actual_completion_date))-nvl(xrr.start_date,xrr.creation_date))*86400) seconds,
xrr.row_count,
xrr.file_size,
fcr.actual_completion_date,
xxen_api.category(xrv.report_id) category,
xrr.type,
xrr.run_id,
xrr.report_id
from
(
select
(select 'Y' from gv$session gs where gs.action like xrr.run_id||'|%' and gs.module like 'XXEN_REPORT - %' and gs.status='ACTIVE' and rownum=1) active_session,
xrr.*
from
xxen_report_runs xrr) xrr,
xxen_reports_v xrv,
fnd_concurrent_requests fcr,
fnd_responsibility_tl frt,
(
select distinct
x.run_id,
listagg(nvl(xrpt.parameter_name,'('||x.rank||')')||': '||case when x.dupl_count>1 then '('||x.value||')' else x.value end,chr(10)) within group (order by nvl(xrp.display_sequence,x.parameter_id)) over (partition by x.run_id) parameters
from
(
select distinct
xrrpv.run_id,
xrrpv.parameter_id,
rank() over (partition by xrrpv.run_id order by xrrpv.parameter_id) rank,
count(*) over (partition by xrrpv.run_id,xrrpv.parameter_id) dupl_count,
listagg(xrrpv.value,';') within group (order by xrrpv.value) over (partition by xrrpv.run_id,xrrpv.parameter_id) value
from
(
select
z.*
from
(
select
sum(length(xrrpv.value)+2) over (partition by xrrpv.run_id,xrrpv.parameter_id order by dbms_lob.substr(xrrpv.value,4000,1) rows between unbounded preceding and current row) listagg_length,
xrrpv.*
from
xxen_report_run_param_values xrrpv
where
xrrpv.parameter_id>0
) z
where
z.listagg_length<3500
) xrrpv
) x,
xxen_report_parameters xrp,
xxen_report_parameters_tl xrpt
where
x.parameter_id=xrp.parameter_id(+) and
x.parameter_id=xrpt.parameter_id(+) and
xrpt.language(+)=userenv('lang')
) y,
(
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=y.run_id(+) and
xrr.run_id=xrrpv0.run_id(+)
) x
order by
x.run_id desc
Parameter Name SQL text Validation
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)||'%'
)
 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
 DateTime
Start Date To 
xrr.creation_date<=:start_date_to
 DateTime
Running or Errored 
(xrr.completion_date is null or xrr.completion_message is not null)
 LOV
Exclude System Reports 
nvl(xrr.type,'x')<>'S'
 LOV
Exclude Submissions from User 
xrr.created_by<>xxen_util.user_id(:exclude_user)
 LOV
Download
 
Related Blitz Reports
GL Account Analysis (Distributions) EIS Execution History FND Concurrent Requests 11i Blitz Reports GL Account Analysis FND Concurrent Requests INV Inventory Item Upload DIS End User Layers XXAP Supplier Sites Audit
Blitz Report™

Blitz Report™ provides multiple benefits: