Total Pageviews

Saturday, March 9, 2013


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%');

No comments:

Post a Comment