ODI META DATA QUERY TO GET THE LIST OF PROCEDURES BEING USED IN DAILY RUNNING ETLS(ODI-10G)

--WORKS IN ODI-10G
select
spj.project_name PROjECT_NAME,
SF.FOLDER_NAME FOLDERNAME,
st.trt_name procedure_name,
stx.txt code
from snp_trt st,snp_folder sf,snp_project spj,snp_txt stx
where 1=1
and sf.i_folder=st.i_folder
and sf.i_project=spj.i_project
--and spj.project_name='RILO_FINANCIALS_ETL'
and stx.i_txt=st.i_txt_trt_txt

AND st.trt_name NOT IN

(SELECT DISTINCT
SSS.STEP_NAME procedure_name
--SSS.STEP_TYPE
from
snp_session ss,snp_sess_step sss,snp_session pss
where ss.sess_no=sss.sess_no
and pss.sess_no=ss.parent_sess_no
and TRUNC(SS.SESS_BEG)='12-JAN-15'
--and trunc(SS.SESS_END)='20-NOV-14'
AND SSS.step_type='T'
AND SS.PARENT_SESS_NO IN
(
164111002,
164112002,
164113002,
164114002,
164115002,
164116002,
164117002,
164118002,
164119002,
164120002,
164121002,
164122002,
164123002,
164124002,
164125002,
164126002)
)--CHANGE THE PARENT SESSION NUMBERS TO YOUR ETL SESSION NUMBERS
order by 1,2;