DBA: Oracle Discoverer 2

2008 November 5 at 4:23 pm Leave a comment

Ini salah satu pendukung di tugas masa percobaan gw di ecogreen batam: Doain ya supaya gw lulus aamien🙂 paling enggak udah sharing daripada nyari2 kaya gw sampai 2 minggu, tema yang gw ambil  untuk masa percobaan ini “HEALTH CARE ORACLE DISCOVERER REPORT

Query SQL DBA untuk monitoring Oracle Discoverer activity

  • Folder WorkBooks Field

Select ex_to_par_name Folder,ownr.user_name owner ,doc_name WorkBooks,Ex.EX_TO_NAME Field_Header,ex.EX_TO_DEVKEY Field_Name
From eul5_documents doc, eul5_elem_xrefs ex, apps.fnd_user ownr
Where doc.doc_id = ex.ex_from_id
And doc.DOC_CREATED_BY=’#’||ownr.user_id
and doc_name=’Suppression – EOM’

  • Business Area Table

select ba_name “Business Area”,ba_created_by “Creator”,ba_created_date “Creation Date”
,ba_updated_by “Updated By “,ba_updated_date “Last Update Date”,ba_id
from EUL5_bas

  • Work Books Table

SELECT doc_name “Work Books”, doc_created_by, doc_created_date FROM eul5_documents;

  • Folder dari Business Area (Folder of Business Area)

select b.ba_name,f.obj_name folder_name,f.obj_id,f.obj_ext_owner Owner
from EUL5_objs f, EUL5_ba_obj_links l, EUL5_bas b
where 1=1
and f.obj_id= l.bol_obj_id
and b.ba_id= l.bol_ba_id
order by b.ba_name,f.obj_name

  • Relasi Business Area dan Responsibilities (Business Area and Responsibilities Relationships)

SELECT distinct resp.responsibility_name, BA.BA_NAME, user_name
FROM EUL5_ACCESS_PRIVS prv, EUL5_BAS ba, EUL5_EUL_USERS sh_usr,
apps.fnd_responsibility_vl resp,apps.fnd_user crtd_by
WHERE ( ( sh_usr.EU_ID = PRV.AP_EU_ID ) AND
( BA.BA_ID =PRV.GBA_BA_ID ) )
AND resp.responsibility_id = SUBSTR(sh_usr.EU_USERNAME,2,INSTR(sh_usr.EU_USERNAME,’#’,2)-2)
AND prv.AP_EU_ID = sh_usr.EU_ID
And resp.responsibility_name=’ABM Intelligence’
ORDER BY BA.BA_NAME ASC;

  • Relasi Work Books dan Responsibilities (Work Books and Responsibilities Relationships)

SELECT user_name||’.’|| doc.DOC_NAME Workbook_Name, resp.responsibility_name
FROM eul5_eul_users sh_usr, eul5_access_privs prv, eul5_documents doc,eul5_eul_users own_usr,
apps.fnd_responsibility_vl resp,apps.fnd_user crtd_by
WHERE prv.AP_TYPE = ‘GD’
AND prv.AP_EU_ID = sh_usr.EU_ID
AND prv.GD_DOC_ID = doc.DOC_ID
AND own_usr.EU_ID = doc.DOC_EU_ID
AND resp.responsibility_id = SUBSTR(sh_usr.EU_USERNAME,2,INSTR(sh_usr.EU_USERNAME,’#’,2)-2)
AND crtd_by.USER_ID=SUBSTR(doc.doc_Created_by,2)
AND own_usr.EU_USERNAME not in (‘EUL4_US’,’PUBLIC’)

  • Discoverer user Access Table

SELECT substr(qs_created_by,2) user_id, user_name,qs_doc_owner owner, qs_doc_name doc_name, qs_doc_details sheet_name, qs_created_date query_time, QS_ACT_ELAP_TIME elap_time,
QS_NUM_ROWS num_rows–,QS_STATE ,QS_OBJECT_USE_KEY
FROM eul5_qpp_stats,(select max(qs_created_date) last_accs from eul5_qpp_stats) last_acc,apps.fnd_user usr
where eul5_qpp_stats.qs_created_date between last_acc.last_accs -1 and last_acc.last_accs
and ‘#’||usr.user_id=qs_created_by
order by QS_ACT_ELAP_TIME desc –qs_created_date desc

Entry filed under: DISCOVERER. Tags: .

DBA: Oracle Discoverer 1 DBA 3: Sharing Work Books Oracle9i Discoverer

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Trackback this post  |  Subscribe to the comments via RSS Feed


Categories


%d bloggers like this: