DBA 3: Sharing Work Books Oracle9i Discoverer

2008 November 21 at 3:07 pm Leave a comment

  • Individual

select user_name owner, doc_name, to_char(doc_created_date,’YYYY-mm-dd’) Crt_Dt,
to_char(min(qs_created_date),’YYYY-mm-dd’) First_Acc,to_char(max(qs_created_date),’YYYY-mm-dd’) Last_Acc
from eul5_documents docs
Inner Join apps.fnd_user usr on ‘#’||usr.user_id=docs.DOC_CREATED_BY
Left Join eul5_access_privs privs on privs.gd_doc_id=docs.doc_id
Left Join eul5_qpp_stats qs on qs.qs_doc_name=doc_name and  qs_doc_owner=user_name And docs.doc_created_date<qs.qs_created_date
where privs.gd_doc_id is NULL
Group By user_name , doc_name, to_char(doc_created_date,’YYYY-mm-dd’)

  • Share to RESPONSIBILITY & USER

SELECT usr.user_name Owner, doc.doc_name “Work Book”,min(QS_ACT_ELAP_TIME)”Fastest”,max(QS_ACT_ELAP_TIME)”Slowest”,
round(avg(QS_ACT_ELAP_TIME),2) “AVG (s)”,round(avg(QS_ACT_ELAP_TIME)/60,2) “AVG (m)”, count(*) “Often”,
to_char(doc.DOC_CREATED_DATE,’YYYY-mm-dd’) “Created_Dt”,
to_char(min(acc.QS_CREATED_DATE),’YYYY-mm-dd’) “First”, to_char(max(acc.QS_CREATED_DATE),’YYYY-mm-dd’)”Last Access”
FROM eul5_Documents doc, apps.fnd_user usr,
eul5_qpp_stats acc,(select distinct gd_doc_id From eul5_access_privs ) privs
where ‘#’||usr.user_id=doc.DOC_CREATED_BY
And doc.doc_name=acc.qs_doc_name
And privs.gd_doc_id = doc.doc_id
And usr.user_name = upper(acc.QS_DOC_OWNER)
And doc.doc_created_date<acc.qs_created_date
Group By usr.user_name, doc_name,to_char(doc.DOC_CREATED_DATE,’YYYY-mm-dd’);

  • Share to Resp. Only

SELECT usr.user_name Owner, doc.doc_name “Work Book”,min(QS_ACT_ELAP_TIME)”Fastest”,max(QS_ACT_ELAP_TIME)”Slowest”,
round(avg(QS_ACT_ELAP_TIME),2) “AVG (s)”,round(avg(QS_ACT_ELAP_TIME)/60,2) “AVG (m)”, count(*) “Often”,
to_char(doc.DOC_CREATED_DATE,’YYYY-mm-dd’) “Created_Dt”,
to_char(min(acc.QS_CREATED_DATE),’YYYY-mm-dd’) “First”, to_char(max(acc.QS_CREATED_DATE),’YYYY-mm-dd’)”Last Access”
FROM eul5_Documents doc, apps.fnd_user usr,
eul5_qpp_stats acc,
(
select distinct gd_doc_id From eul5_eul_users sh_usr, eul5_access_privs prv
Where prv.AP_EU_ID = sh_usr.EU_ID
And INSTR(sh_usr.EU_USERNAME,’#’,2)>0
) privs
where ‘#’||usr.user_id=doc.DOC_CREATED_BY
And doc.doc_name=acc.qs_doc_name
And privs.gd_doc_id = doc.doc_id
And usr.user_name = upper(acc.QS_DOC_OWNER)
And doc.doc_created_date<acc.qs_created_date
Group By usr.user_name, doc_name,to_char(doc.DOC_CREATED_DATE,’YYYY-mm-dd’);

  • Share to user Only

SELECT usr.user_name Owner, doc.doc_name “Work Book”,min(QS_ACT_ELAP_TIME)”Fastest”,max(QS_ACT_ELAP_TIME)”Slowest”,
round(avg(QS_ACT_ELAP_TIME),2) “AVG (s)”,round(avg(QS_ACT_ELAP_TIME)/60,2) “AVG (m)”, count(*) “Often”,
to_char(doc.DOC_CREATED_DATE,’YYYY-mm-dd’) “Created_Dt”,
to_char(min(acc.QS_CREATED_DATE),’YYYY-mm-dd’) “First”, to_char(max(acc.QS_CREATED_DATE),’YYYY-mm-dd’)”Last Access”
FROM eul5_Documents doc, apps.fnd_user usr,
eul5_qpp_stats acc,
(
select distinct prv.gd_doc_id
From eul5_access_privs prv
inner Join eul5_eul_users sh_usr on prv.AP_EU_ID = sh_usr.EU_ID
Left Join
(    select distinct gd_doc_id From eul5_access_privs prv
,eul5_eul_users sh_usr
Where prv.AP_EU_ID = sh_usr.EU_ID
And INSTR(sh_usr.EU_USERNAME,’#’,2)>0
)shr On prv.gd_doc_id=shr.gd_doc_id
Where shr.gd_doc_id is Null
) privs
where  ‘#’||usr.user_id=doc.DOC_CREATED_BY
And doc.doc_name=acc.qs_doc_name
And privs.gd_doc_id = doc.doc_id
And usr.user_name = upper(acc.QS_DOC_OWNER)
And doc.doc_created_date<acc.qs_created_date
Group By usr.user_name, doc_name,to_char(doc.DOC_CREATED_DATE,’YYYY-mm-dd’);

  • Never Executeed

select user_name, doc_name, to_char(docs.DOC_CREATED_DATE,’YYYY-mm-dd’) Created_Date
From eul5_documents docs
Inner Join apps.fnd_user usr On ‘#’||usr.user_id=docs.DOC_CREATED_BY
Left Join eul5_qpp_stats qs On qs.qs_doc_name=docs.doc_name and usr.user_name=qs.QS_DOC_OWNER
And qs.QS_CREATED_DATE>docs.DOC_CREATED_DATE
where qs.QS_CREATED_DATE Is Null

  • Work Books of Business Area

SELECT BA.BA,usr.user_name, doc.doc_name “Work Book”,min(QS_ACT_ELAP_TIME)”Fastest”,max(QS_ACT_ELAP_TIME)”Slowest”,
round(avg(QS_ACT_ELAP_TIME),2) “AVG (s)”,round(avg(QS_ACT_ELAP_TIME)/60,2) “AVG (m)”, count(*) “Often”,
to_char(doc.DOC_CREATED_DATE,’YYYY-mm-dd’) “Created_Dt”,
to_char(min(acc.QS_CREATED_DATE),’YYYY-mm-dd’) “First”, to_char(max(acc.QS_CREATED_DATE),’YYYY-mm-dd’)”Last Access”
FROM eul5_Documents doc, apps.fnd_user usr,
eul5_qpp_stats acc,(select distinct gd_doc_id From eul5_access_privs ) privs
,(
SELECT distinct doc.doc_id,ba.ba_name BA
FROM eul5_documents doc
,eul5_elem_xrefs eex
,eul5_ba_obj_links bol
,eul5_objs obj
,eul5_bas ba
WHERE doc.doc_id = eex.ex_from_id
AND eex.ex_to_par_name = obj.obj_name
AND obj.obj_id = bol.bol_obj_id
AND bol.bol_ba_id = ba.ba_id
)BA
where  ‘#’||usr.user_id=doc.DOC_CREATED_BY
And doc.doc_name=acc.qs_doc_name
And privs.gd_doc_id = doc.doc_id
And usr.user_name = upper(acc.QS_DOC_OWNER)
And doc.doc_created_date<acc.qs_created_date
And doc.doc_id=BA.doc_id
Group By BA,usr.user_name, doc_name,to_char(doc.DOC_CREATED_DATE,’YYYY-mm-dd’)

Entry filed under: DISCOVERER. Tags: .

DBA: Oracle Discoverer 2 Indonesia di Mata Dunia

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: