Details of Discoverer Report by sql query

in ; By using the following query ,we can get the following details of Discoverer Reports creation date shared responsibity or owner ...
+ Post Reply + Post New Topic
Results 1 to 8 of 8
  1. #1
    malli_18arjun is offline Member
    Points: 1,140, Level: 18
    Join Date
    30 Apr 2009
    Location
    Hyderabad
    Posts
    33
    Points
    1,140

    Thumbs down Details of Discoverer Report by sql query

    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%';
    Sadik says Thanks.


  2. #2
    Sadik's Avatar
    Sadik is offline Administrator
    Points: 35,660, Level: 100
    Join Date
    03 Aug 2008
    Location
    Kolkata, India
    Posts
    1,862
    Points
    35,660

    Re: Details of Discoverer Report by sql query

    very nice...
    Learn Oracle with Oracle forum. Check out General Discussion Forums

  3. #3
    Ori Tal is offline Junior Member
    Points: 10, Level: 1
    Join Date
    23 Jul 2009
    Posts
    1
    Points
    10

    Re: Details of Discoverer Report by sql query

    Hi, The subquery for retrieving the responsibility_name returns more than 1 value.
    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. #4
    paul.s.richards is offline Junior Member
    Points: 10, Level: 1
    Join Date
    22 Jan 2010
    Posts
    1
    Points
    10

    Smile Re: Details of Discoverer Report by sql query

    Awesome! Works for Discoverer 4 if you change eul5 to eul4. Many thanks.

  5. #5
    kiran.marla's Avatar
    kiran.marla is offline Forum Genius
    Points: 8,000, Level: 60
    Join Date
    12 Dec 2009
    Location
    Khammam
    Posts
    397
    Points
    8,000
    "EUL_US.EUL5_GET_APPS_USERRESP" is not found...

  6. #6
    Sadik's Avatar
    Sadik is offline Administrator
    Points: 35,660, Level: 100
    Join Date
    03 Aug 2008
    Location
    Kolkata, India
    Posts
    1,862
    Points
    35,660

    Re: Details of Discoverer Report by sql query

    Quote Originally Posted by kiran.marla View Post
    "EUL_US.EUL5_GET_APPS_USERRESP" is not found...
    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
    Learn Oracle with Oracle forum. Check out General Discussion Forums

  7. #7
    mani_jal06 is offline Junior Member
    Points: 10, Level: 1
    Join Date
    17 Sep 2011
    Posts
    1
    Points
    10

    Re: Details of Discoverer Report by sql query

    Quote Originally Posted by Ori Tal View Post
    Hi, The subquery for retrieving the responsibility_name returns more than 1 value.
    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;

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

    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. #8
    bobanna02 is offline Junior Member
    Points: 10, Level: 1
    Join Date
    16 Jul 2014
    Posts
    1
    Points
    10
    Does anyone have a query to fins users that have the Scheduling Privilege assigned to them?

Other Solutions
  1. custom query report for expirycontent
    By karnakar in forum SQL PL/SQL
    Replies: 1
    Last Post: 05-17-2010, 02:18 PM
  2. adding new types in discoverer report
    By vamsioracle in forum Oracle Discoverer
    Replies: 0
    Last Post: 08-03-2009, 09:21 PM
  3. Registering a discoverer Report
    By vamsioracle in forum Oracle Discoverer
    Replies: 2
    Last Post: 05-14-2009, 10:51 AM
  4. Register Discoverer Report in Oracle Applications
    By vamsioracle in forum Oracle Discoverer
    Replies: 8
    Last Post: 03-25-2009, 01:43 PM
  5. Replies: 1
    Last Post: 02-02-2009, 06:03 PM