How to create Parameterzied View?

Here is the simplest way to create Parameterzied view.

CREATE OR REPLACE VIEW xxdd_mtl_serial_numbers

AS

SELECT *

FROM mtl_serial_numbers

WHERE serial_number in ( USERENV ('client_info'));

exec dbms_application_info.set_client_info('49236920');

select * from xxdd_mtl_serial_numbers

ODI 12c RKM E-Business Suite does not pull Synonyms

When I ran RKM E-Business Suite in ODI to pull all %INTERFACE% tables it did not pull tables like MTL_SYSTEM_ITEMS_INTERFACE which is own by INV Schema.

If you run below query you can see that Oracle added # at the end in all_synonyms.table_name column for MTL_SYSTEM_ITEMS_INTERFACE#

select * from   all_synonyms where table_name  LIKE 'MTL%SYS%ITEM%INTERFACE%'

This was creating problem in all the RKM E-Business Suite queries which was using all_synonyms table.

After changing join t.table_name = syn.table_name to  t.table_name = rtrim(syn.table_name,'#') in RKM E-Business Suite Knowledge Module-->Task issue got resolved.