Friday, June 14, 2013

Discoverer Digression: Changing The Owner of a Workbook in a Development Environment

When it comes to this blog, I try to consistently blog on just Noetix related issues.

Today, I thought it would be helpful to some people to document how I change ownership of Discoverer workbooks quickly in a Development environment (The protocol that Oracle advices to do this is to use the Discoverer Administrator). 

Whenever you are touching the EUL schema tables, it is always important to be cognizant that it is best practice (and supported) to use the Discoverer Administrator to perform the task you are intending to perform because you can easily render the EUL unusable).

Here is my script:

update eul5_us.eul5_documents
set doc_eu_id =  :target_eu_id
doc_eu_id in
eul5_us.eul5_eul_users euluser,
applsys.fnd_user fu
-- oracle apps Disco implementation
euluser.eu_username = '#'|| to_char(fu.user_id)
and fu.user_name in (list of "from" users));

Notes: one can identify the target user id by using this statement:

NOETIX_SYS@erptst> variable target varchar2(100);
NOETIX_SYS@erptst> exec :target := 'DISCOADMIN'

PL/SQL procedure successfully completed.

NOETIX_SYS@erptst> select
  2  fu.user_name oracle_app_user,
  3  euluser.eu_id
  4  from
  5  eul5_us.eul5_eul_users euluser,
  6  applsys.fnd_user fu
  7  where
  8  euluser.eu_username = '#'|| to_char(fu.user_id)
  9  and fu.user_name in (:target /* e.g. 'discoadmin'*/)
 10  /

DISCOADMIN                  308285

Of course, your EUL owner schema would more than likely be different from mine above.

That is all.