Discoverer Universal Query
SELECT DISTINCT d.ba_name business_area
,c.obj_name folder_name
,decode(c.obj_type, 'SOBJ','From Database', 'CUO', 'Custom Query', 'COBJ', 'Complex Folder') obj_type_desc
,c.sobj_ext_table view_name
,disco_docs.doc_name "Discoverer Workbook",
TRUNC (disco_docs.doc_created_date) "Workbook Create Date",
CASE
WHEN INSTR
(disco_docs.doc_created_by,
'#'
) = 0
THEN disco_docs.doc_created_by
WHEN INSTR (disco_docs.doc_created_by, '#') > 0
AND INSTR (disco_docs.doc_created_by, '#', 2) = 0
THEN (SELECT fu.user_name
FROM fnd_user fu
WHERE fu.user_id =
SUBSTR (disco_docs.doc_created_by, 2, 5))
ELSE NULL
END "Workbook Owner/Creator",
disco_users.eu_username,
CASE
WHEN INSTR
(disco_users.eu_username,
'#'
) = 0
THEN disco_users.eu_username
WHEN INSTR (disco_users.eu_username, '#') > 0
AND INSTR (disco_users.eu_username, '#', 2) = 0
THEN (SELECT fu.user_name
FROM fnd_user fu
WHERE fu.user_id =
SUBSTR (disco_users.eu_username, 2, 5))
ELSE (SELECT resp.responsibility_name
FROM fnd_responsibility_tl resp
WHERE resp.responsibility_id =
SUBSTR (disco_users.eu_username, 2, 5))
END AS "Shared Name / Responsibility"
FROM biseul_us.eul5_documents disco_docs,
biseul_us.eul5_access_privs disco_shares,
biseul_us.eul5_eul_users disco_users,
--
biseul_us.eul5_qpp_stats a,
biseul_us.eul5_bas d,
biseul_us.eul5_objs c,
biseul_us.eul5_ba_obj_links f
WHERE 1=1
AND a.qs_doc_name= disco_docs.doc_name
AND c.obj_id = SUBSTR (a.qs_object_use_key, 1, 6)
AND c.obj_id = f.bol_obj_id
AND d.ba_id = f.bol_ba_id
AND disco_docs.doc_id = disco_shares.gd_doc_id
AND disco_users.eu_username(+) NOT IN ('EUL5', 'PUBLIC')
AND DISCO_USERS.EU_ID(+) = DISCO_SHARES.AP_EU_ID
AND upper(disco_docs.doc_name) LIKE upper('% :Report sheet name here%');
,c.obj_name folder_name
,decode(c.obj_type, 'SOBJ','From Database', 'CUO', 'Custom Query', 'COBJ', 'Complex Folder') obj_type_desc
,c.sobj_ext_table view_name
,disco_docs.doc_name "Discoverer Workbook",
TRUNC (disco_docs.doc_created_date) "Workbook Create Date",
CASE
WHEN INSTR
(disco_docs.doc_created_by,
'#'
) = 0
THEN disco_docs.doc_created_by
WHEN INSTR (disco_docs.doc_created_by, '#') > 0
AND INSTR (disco_docs.doc_created_by, '#', 2) = 0
THEN (SELECT fu.user_name
FROM fnd_user fu
WHERE fu.user_id =
SUBSTR (disco_docs.doc_created_by, 2, 5))
ELSE NULL
END "Workbook Owner/Creator",
disco_users.eu_username,
CASE
WHEN INSTR
(disco_users.eu_username,
'#'
) = 0
THEN disco_users.eu_username
WHEN INSTR (disco_users.eu_username, '#') > 0
AND INSTR (disco_users.eu_username, '#', 2) = 0
THEN (SELECT fu.user_name
FROM fnd_user fu
WHERE fu.user_id =
SUBSTR (disco_users.eu_username, 2, 5))
ELSE (SELECT resp.responsibility_name
FROM fnd_responsibility_tl resp
WHERE resp.responsibility_id =
SUBSTR (disco_users.eu_username, 2, 5))
END AS "Shared Name / Responsibility"
FROM biseul_us.eul5_documents disco_docs,
biseul_us.eul5_access_privs disco_shares,
biseul_us.eul5_eul_users disco_users,
--
biseul_us.eul5_qpp_stats a,
biseul_us.eul5_bas d,
biseul_us.eul5_objs c,
biseul_us.eul5_ba_obj_links f
WHERE 1=1
AND a.qs_doc_name= disco_docs.doc_name
AND c.obj_id = SUBSTR (a.qs_object_use_key, 1, 6)
AND c.obj_id = f.bol_obj_id
AND d.ba_id = f.bol_ba_id
AND disco_docs.doc_id = disco_shares.gd_doc_id
AND disco_users.eu_username(+) NOT IN ('EUL5', 'PUBLIC')
AND DISCO_USERS.EU_ID(+) = DISCO_SHARES.AP_EU_ID
AND upper(disco_docs.doc_name) LIKE upper('% :Report sheet name here%');
No comments:
Post a Comment