Blitz Report Example XLSX Files

Description
All Blitz Report example XLSX files shown on the report library.
Column delete_text can be used to remove obsolete, either duplicate or orphan excel files.
Days Outdated shows the number of days for records where the SQL was updated after creation of the example file.
select
y.*,
case when y.files_count>1 or y.report_name is null then 'delete fnd_lobs fl where fl.file_id='||y.file_id||';' end delete_text 
from
(
select
x.report_name,
xxen_api.category(xrt.report_id) category,
xrh.creation_date last_update_date,
x.file_name,
x.upload_date,
x.file_id,
x.language,
x.oracle_charset,
nvl2(x.report_name,row_number() over (partition by x.report_name order by x.upload_date desc),null) files_count,
case when xrh.creation_date>x.upload_date then trunc(xrh.creation_date-x.upload_date) end days_outdated
from
(
select
(select xrt.report_name from xxen_reports_tl xrt where xrt.language='US' and fl.file_name like regexp_replace(xrt.report_name,'\W+','_')||' __-___-____ ______.xlsx') report_name,
fl.*
from
fnd_lobs fl
where
fl.file_content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' and
fl.upload_date>to_date('11-05-2017','dd-mm-yyyy') and
fl.program_name='FNDATTCH' and
fl.expiration_date is null
) x,
xxen_reports_tl xrt,
(select xrh.report_id, max(xrh.creation_date) creation_date from xxen_reports_h xrh group by xrh.report_id) xrh
where
x.report_name=xrt.report_name(+) and
xrt.language(+)='US' and
xrt.report_id=xrh.report_id(+)
) y
order by
y.report_name,
y.upload_date desc