add

About Me

My photo
Oracle Apps - Techno Functional consultant

Friday, March 9

To Find Oracle Discoverer Reprt Name

SELECT DISTINCT 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 eul4_us.eul5_documents disco_docs,
                eul4_us.eul5_access_privs disco_shares,
                eul4_us.eul5_eul_users disco_users
          WHERE 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 disco_docs.doc_name LIKE '%Account%';

No comments: