View Suresh Vaishya's profile
Following query can be used to display Document Name, Document Key, Folder Name, Folder Key, Documents Sheets, Business Unit, Document Created By in discoverer.
Please note that the query is in crude format and not tested, any suggestion to improve this is appreciated.
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 eul4_us.eul4_qpp_stats a
, eul4_us.eul4_documents b
, eul4_us.eul4_objs c
, eul4_us.eul4_bas d
, eul4_us.eul4_eul_users e
, eul4_us.eul4_ba_obj_links f
, fnd_user fu
WHERE a.qs_doc_name = b.doc_name
AND a.qs_doc_owner = e.eu_username
AND b.doc_eu_id = e.eu_id
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 TO_CHAR (fu.user_id) = SUBSTR (b.doc_updated_by, 2)
3 Comments:
Hi Suresh,
I have a requirement to schedule the discoverer report and email the output to the users. Scheduling can be done from Discoverer desktop but how to email the report output automatically once it is completed?
Can you please help me out in this?
Thanks in advance.
Query to get work book name, work sheet,, business area, folders used in the work book...etc
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 ENE_DISCO_US.eul5_qpp_stats a,
ENE_DISCO_US.eul5_documents b,
ENE_DISCO_US.eul5_objs c,
ENE_DISCO_US.eul5_bas d,
ENE_DISCO_US.eul5_eul_users e,
ENE_DISCO_US.eul5_ba_obj_links f,
fnd_user fu
WHERE b.doc_name = 'ENE-EAM-Work Order Cost Transaction'
AND 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;
fnd_user is table not found
we need to use eul5_eul_users;
Post a Comment