Saturday, August 7, 2010

Discoverer Query to Display Document Name, Folder, Business Unit

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)

No comments:

Post a Comment