1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Details of Discoverer Report by sql query

Discussion in 'Other Development Tools' started by malli_18arjun, Apr 30, 2009.

  1. malli_18arjun

    malli_18arjun Active Member

    Messages:
    36
    Likes Received:
    3
    Trophy Points:
    110
    Location:
    Hyderabad
    By using the following query ,we can get the following details of Discoverer Reports

    1. creation date
    2. shared responsibity or owner
    3. user name
    4. shared name /responsibility

    Code (SQL):
    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%';
     
  2. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    very nice...
     
  3. Ori Tal

    Ori Tal Guest

    Hi, The subquery for retrieving the responsibility_name returns more than 1 value.:mad:
    Any suggestions ?
    Anyway, Here is a Query that I use :

    Code (SQL):
      SELECT   ACCESS_PRIVS.AP_CREATED_DATE,
               DECODE (
                  EUL_US.EUL5_GET_ISITAPPS_EUL,
                  1,
                  DECODE (
                     USERS.EU_ROLE_FLAG,
                     1,
                     EUL_US.EUL5_GET_APPS_USERRESP (USERS.EU_USERNAME, 'R'),
                     DECODE (USERS.EU_USERNAME,
                             NULL, 'Document Not Shared',
                             EUL_US.EUL5_GET_APPS_USERRESP (USERS.EU_USERNAME))
                  ),
                  NVL (USERS.EU_USERNAME, 'Document Not Shared')
               ),
               DECODE (EUL_US.EUL5_GET_ISITAPPS_EUL,
                       1, EUL_US.EUL5_GET_APPS_USERRESP (DOCUMENTS.DOC_CREATED_BY),
                       DOCUMENTS.DOC_CREATED_BY),
               DOCUMENTS.DOC_DESCRIPTION,
               DOCUMENTS.DOC_NAME
        FROM   EUL_US.EUL5_ACCESS_PRIVS ACCESS_PRIVS,
               EUL_US.EUL5_DOCUMENTS DOCUMENTS,
               EUL_US.EUL5_EUL_USERS USERS
       WHERE   ( (DOCUMENTS.DOC_ID = ACCESS_PRIVS.GD_DOC_ID(+))
                AND (USERS.EU_ID(+) = ACCESS_PRIVS.AP_EU_ID))
               AND (DOCUMENTS.DOC_CONTENT_TYPE = 'application/vnd.oracle-disco.wb')
               AND (DOCUMENTS.DOC_CONTENT_TYPE = 'application/vnd.oracle-disco.wb')
               AND (DECODE (
                       EUL_US.EUL5_GET_ISITAPPS_EUL,
                       1,
                       DECODE (
                          USERS.EU_ROLE_FLAG,
                          1,
                          EUL_US.EUL5_GET_APPS_USERRESP (USERS.EU_USERNAME, 'R'),
                          DECODE (
                             USERS.EU_USERNAME,
                             NULL,
                             'Document Not Shared',
                             EUL_US.EUL5_GET_APPS_USERRESP (USERS.EU_USERNAME)
                          )
                       ),
                       NVL (USERS.EU_USERNAME, 'Document Not Shared')
                    ) = &Responsibility_name)
    ORDER BY   DOCUMENTS.DOC_NAME ASC,
               DOCUMENTS.DOC_CREATED_DATE ASC,
               DECODE (
                  EUL_US.EUL5_GET_ISITAPPS_EUL,
                  1,
                  DECODE (
                     USERS.EU_ROLE_FLAG,
                     1,
                     EUL_US.EUL5_GET_APPS_USERRESP (USERS.EU_USERNAME, 'R'),
                     DECODE (USERS.EU_USERNAME,
                             NULL, 'Document Not Shared',
                             EUL_US.EUL5_GET_APPS_USERRESP (USERS.EU_USERNAME))
                  ),
                  NVL (USERS.EU_USERNAME, 'Document Not Shared')
               ) ASC;
     
  4. Awesome! Works for Discoverer 4 if you change eul5 to eul4. Many thanks.
     
  5. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    "EUL_US.EUL5_GET_APPS_USERRESP" is not found...
     
  6. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    I believe you will have to replace EUL5 with EUL4

    look for this table EUL4_GET_APPS_USERRESP in your schema if it's different from default EUL_US
     
  7. mani_jal06

    mani_jal06 Guest


    Hi, The subquery for retrieving the responsibility_name returns more than 1 value.:mad:

    Yes the subquery returns more than one values but if you add in the where clause of the subquery (SELECT resp.responsibility_name
    FROM fnd_responsibility_tl resp
    WHERE resp.responsibility_id =
    SUBSTR (disco_users.eu_username, 2, 5) and language='US') like this it will work fine.
    Again an excellent query..thanx..malli_18arjun very nice query.:)
     
  8. bobanna02

    bobanna02 Guest

    Does anyone have a query to fins users that have the Scheduling Privilege assigned to them?