select
x.report_name,
x.category,
xxen_util.client_time(nvl(xrh.creation_date,x.last_update_date)) last_update_date,
x.file_name,
x.upload_date,
case when xrh.creation_date>x.upload_date then trunc(xrh.creation_date-x.upload_date) end days_outdated,
length(x.file_data) file_size,
x.file_count,
x.file_id,
x.language,
x.oracle_charset,
case when x.file_count>1 or x.report_id_ is null then 'delete fnd_lobs fl where fl.file_id='||x.file_id||';' end delete_text,
x.report_id_
from
(
select
xrv.*,
fl.*,
(select xwrt.web_report_name from xxen_web_reports_tl xwrt where xrv.report_id=xwrt.report_id and xwrt.language='US') web_report_name,
nvl2(fl.report_id,row_number() over (partition by fl.report_id order by fl.upload_date desc),null) file_count,
xrv.report_id report_id_
from
(
select
(select xrt.report_id from xxen_reports_tl xrt where xrt.language='US' and
(
fl.file_name like trim(regexp_replace(xrt.report_name,'\W+',' '))||' __-___-____ ______.xls_' or
fl.file_name like trim(regexp_replace(xrt.report_name,'\W+',' '))||' - % __-___-____ ______.xls_'
)
) report_id,
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
) fl
full join
(select xrv.* from xxen_reports_v xrv where 1=1) xrv
on
fl.report_id=xrv.report_id
) x,
(select xrh.report_id, max(xrh.creation_date) creation_date from xxen_reports_h xrh group by xrh.report_id) xrh
where
2=2 and
x.report_id_=xrh.report_id(+)
order by
nvl2(x.file_id,1,2),
x.report_name,
x.upload_date desc |
