Description:
One of the distinct steps is to find if all the necessary columns are available in the EUL, and this can be visually done through scanning the Business Areas and Folders in Discoverer.
Query #1 – To List the Discoverer Reports
SELECT DOC_ID,
DOC_NAME,
DOC_DEVELOPER_KEY,
DOC_CREATED_BY
FROM EUL5_US.EUL5_DOCUMENTS
–EUL_APPS.EUL4_DOCUMENTS
Query #2 – To List the Discoverer Reports and users having access to them
SELECT ACCESS_PRIVS.AP_CREATED_DATE,
DECODE (
EUL_US.EUL5_GET_ISITAPPS_EUL,
1,
DECODE (
USERS.EU_ROLE_FLAG,
1,
EUL_US.EUL5_GET_APPS_USERRESP (USERS.EU_USERNAME, ‘R’),
DECODE (USERS.EU_USERNAME,
NULL, ‘Document Not Shared’,
EUL_US.EUL5_GET_APPS_USERRESP (USERS.EU_USERNAME))
),
NVL (USERS.EU_USERNAME, ‘Document Not Shared’)
),
DECODE (EUL_US.EUL5_GET_ISITAPPS_EUL,
1, EUL_US.EUL5_GET_APPS_USERRESP (DOCUMENTS.DOC_CREATED_BY),
DOCUMENTS.DOC_CREATED_BY),
DOCUMENTS.DOC_DESCRIPTION,
DOCUMENTS.DOC_NAME
FROM EUL_US.EUL5_ACCESS_PRIVS ACCESS_PRIVS,
EUL_US.EUL5_DOCUMENTS DOCUMENTS,
EUL_US.EUL5_EUL_USERS USERS
WHERE ( (DOCUMENTS.DOC_ID = ACCESS_PRIVS.GD_DOC_ID(+))
AND (USERS.EU_ID(+) = ACCESS_PRIVS.AP_EU_ID))
AND (DOCUMENTS.DOC_CONTENT_TYPE = ‘application/vnd.oracle-disco.wb’)
AND (DOCUMENTS.DOC_CONTENT_TYPE = ‘application/vnd.oracle-disco.wb’)
AND (DECODE (
EUL_US.EUL5_GET_ISITAPPS_EUL,
1,
DECODE (
USERS.EU_ROLE_FLAG,
1,
EUL_US.EUL5_GET_APPS_USERRESP (USERS.EU_USERNAME, ‘R’),
DECODE (
USERS.EU_USERNAME,
NULL,
‘Document Not Shared’,
EUL_US.EUL5_GET_APPS_USERRESP (USERS.EU_USERNAME)
)
),
NVL (USERS.EU_USERNAME, ‘Document Not Shared’)
) = &Responsibility_name)
ORDER BY DOCUMENTS.DOC_NAME ASC,
DOCUMENTS.DOC_CREATED_DATE ASC,
DECODE (
EUL_US.EUL5_GET_ISITAPPS_EUL,
1,
DECODE (
USERS.EU_ROLE_FLAG,
1,
EUL_US.EUL5_GET_APPS_USERRESP (USERS.EU_USERNAME, ‘R’),
DECODE (USERS.EU_USERNAME,
NULL, ‘Document Not Shared’,
EUL_US.EUL5_GET_APPS_USERRESP (USERS.EU_USERNAME))
),
NVL (USERS.EU_USERNAME, ‘Document Not Shared’)
) ASC;
Query #3 – To List the Discoverer Reports along with the Folder names, Tables used and User details
SELECT DISTINCT b.doc_developer_key document_developer_key,
b.doc_name document_name, a.qs_doc_details worksheet_name,
c.obj_name folder_name,
c.obj_developer_key folder_developer_key,
d.ba_name business_unit, fu.user_name created_by
FROM eul5_us.eul5_qpp_stats a,
eul5_us.eul5_documents b,
eul5_us.eul5_objs c,
eul5_us.eul5_bas d,
eul5_us.eul5_eul_users e,
eul5_us.eul5_ba_obj_links f,
fnd_user fu
WHERE a.qs_doc_name = b.doc_name
AND a.qs_doc_owner = e.eu_username
AND INSTR (a.qs_object_use_key, c.obj_id) <> 0
AND c.obj_id = f.bol_obj_id
AND d.ba_id = f.bol_ba_id
AND TO_CHAR (fu.user_id) = SUBSTR (b.doc_updated_by, 2)
ORDER BY b.doc_developer_key;
Query #4 – To List the Discoverer Workbooks along with the Responsibility details and Users having access
SELECT access_privs.ap_updated_date last_updated,
documents.doc_name workbook_name,
documents.doc_developer_key workbook_key,
documents.doc_description workbook_descr,
resp.responsibility_name shared_with_respo
FROM eul_apps.eul5_access_privs access_privs,
eul_apps.eul5_documents documents,
eul_apps.eul5_eul_users users,
apps.fnd_responsibility_tl resp
WHERE documents.doc_id = access_privs.gd_doc_id
AND users.eu_id = access_privs.ap_eu_id
AND documents.doc_content_type = ‘application/vnd.oracle-disco.wb’
AND users.eu_role_flag = 1
AND users.eu_username =
‘#’ || resp.responsibility_id || ‘#’ || resp.application_id
AND resp.LANGUAGE = ‘US’;
Summary:
This Post described the script Create Project party in oracle projects accounting using useful discoverer Queries in oracle EBS.
Queries?
Do drop a note by writing us at doyen.ebiz@gmail.comor use the comment section below to ask your questions.
