今天接到一個需求,想要知道有哪些客製報表在一年內沒有執行過。
幸好我們的Purge Concurrent Request and/or Manager Data有設定保留一年,
不然也沒有資料可以比對,所以寫了一個簡易查詢的SQL如下:
(我們的客製報表名稱最前面都是CTS開頭)
select application_id,concurrent_program_id,concurrent_program_name,
last_update_date,creation_date ,user_concurrent_program_name
from FND_CONCURRENT_PROGRAMS_VL fcrv
where concurrent_program_id not in
(select concurrent_program_id
from FND_CONC_REQ_SUMMARY_V
where phase_code='C'
and status_code='C'
and user_concurrent_program_name like 'CTS%'
group by concurrent_program_id,user_concurrent_program_name
having max(request_date) >= (sysdate -365)
and fcrv.concurrent_program_name like 'CTS%' ;