CODE TO GET THE QUERY FORMED BY INTERFACE USING META DATA(ODI-10G)

--THIS WILL WORK WITH ODI-10G

declare
interface_name varchar2(40)
:='RILO_GL_JOURNALS_FACT_INT';--GIVE YOUR INTERFACE NAME HERE
cursor columns_temp is
select
sp.pop_name interface_name,sst1.table_name source_table_name_1,sst1.src_tab_alias,spc.col_name source_column_names,st.txt column_txt,st.txt_ord
from snp_pop sp ,snp_source_tab sst1,--snp_source_tab sst2,
snp_pop_col spc
,snp_src_set sss,snp_txt st
where 1=1
and sst1.i_pop=sp.i_pop
and spc.i_pop=sp.i_pop
and spc.i_source_tab=sst1.i_source_tab
and sss.i_pop=sp.i_pop
and sss.i_src_set=sst1.i_src_set
and sss.i_src_set=spc.i_src_set
and sp.pop_name=interface_name
and st.i_txt=spc.i_txt_map
group by sp.pop_name,sst1.table_name,sst1.src_tab_alias,spc.col_name,st.txt,st.txt_ord
order by 4,6;
cursor table_temp is
--only tables
select
sp.pop_name interface_name,sst1.table_name  source_table,sst1.src_tab_alias alias_name
from snp_pop sp ,snp_source_tab sst1
where 1=1
and sst1.i_pop=sp.i_pop
and sp.pop_name=interface_name
group by sp.pop_name,sst1.table_name,sst1.src_tab_alias--,sst2.table_name,st.txt
order by 1,2;

cursor where_temp is
--only tables and where clause
select
sp.pop_name interface_name,sst1.table_name  source_table,sst2.table_name  joined_with,st.txt where_clause
from snp_pop sp ,snp_source_tab sst1,snp_source_tab sst2,snp_pop_clause spcl,
snp_txt st
where 1=1
and sst1.i_pop=sp.i_pop
and sst1.i_source_tab=spcl.i_table1
and sst2.i_source_tab(+)=spcl.i_table2
and spcl.i_pop=sp.i_pop
and sp.pop_name=interface_name
and spcl.i_txt_sql=st.i_txt
group by sp.pop_name,sst1.table_name,sst2.table_name,st.txt  order by 1,2;


begin
dbms_output.put_line('------------'||interface_name);
null;
dbms_output.put_line('select ');
for columns_temp_c in columns_temp
loop
dbms_output.put_line(columns_temp_c.column_txt||',');
end loop;
dbms_output.put_line('from ');
for table_temp_c in table_temp
loop
dbms_output.put_line(table_temp_c.source_table||' '||table_temp_c.alias_name||',');
end loop;
dbms_output.put_line('where ');
for where_temp_c in where_temp
loop
dbms_output.put_line(where_temp_c.where_clause||' and ');
end loop;

exception
when others then
dbms_output.put_line('some error in the anonymous block');
end;

/

--REMOVE THE EXTRA COMMAS,AND WHICH FROM THE RESULT