2 Replies Latest reply: Dec 1, 2014 9:17 AM by clifford_d RSS

    Discoverer Workbooks

    Jonathan_Ditchfield_1985

      Hi,

       

      I am trying to run a query against the Oracle Database to find all of my Discoverer Documents that have not been shared.

      If I run the following:


      SELECT *

      FROM    EUL.EUL5_DOCUMENTS

       

      This brings me back all documents that users have created on Discoverer.  However some of these documents have been shared and showing in the table: EUL.EUL5_ACCESS_PRIVS.  Is it possible to find the documents that have not been shared.

      Logically I have to find a way to say that if the GD_DOC_ID field does not exist on the EUL.EUL5_ACCESS_PRIVS table and the DOC_ID does exist on the EUL.EUL5_DOCUMENTS table then the one on the DOCUMENTS table must not be shared.

      I just cannot figure out the SQL Command that would find this.

       

      Please help me

       

      Regards,

       

      Jon Ditchfield

        • 1. Re: Discoverer Workbooks
          clifford_d

          You could try the following and see if it will work for you.  Let me know.

          SELECT

          O179537.AP_CREATED_BY, O179537.AP_CREATED_DATE,

          DECODE(EUL_ADMIN.EUL5_GET_ISITAPPS_EUL,1,DECODE(O179556.EU_ROLE_FLAG,1,EUL_ADMIN.EUL5_GET_APPS_USERRESP(O179556.EU_USERNAME,'R'),DECODE(O179556.EU_USERNAME,NULL,'Document Not Shared',EUL_ADMIN.EUL5_GET_APPS_USERRESP(O179556.EU_USERNAME))),NVL(O179556.EU_USERNAME,'Document Not Shared')), O179553.DOC_BATCH, DECODE(EUL_ADMIN.EUL5_GET_ISITAPPS_EUL,1,EUL_ADMIN.EUL5_GET_APPS_USERRESP(O179553.DOC_CREATED_BY),O179553.DOC_CREATED_BY), O179553.DOC_CREATED_DATE, O179553.DOC_DESCRIPTION,

          O179553.DOC_DEVELOPER_KEY, O179553.DOC_ID, O179553.DOC_LENGTH/1024, O179553.DOC_NAME,

          DECODE(EUL_ADMIN.EUL5_GET_ISITAPPS_EUL,1,EUL_ADMIN.EUL5_GET_APPS_USERRESP(O179553.DOC_UPDATED_BY),

          O179553.DOC_UPDATED_BY), O179553.DOC_UPDATED_DATE

          FROM EUL_ADMIN.EUL5_ACCESS_PRIVS O179537,

          EUL_ADMIN.EUL5_DOCUMENTS O179553,

          EUL_ADMIN.EUL5_EUL_USERS O179556

          WHERE

          ( ( O179553.DOC_ID = O179537.GD_DOC_ID(+) ) AND ( O179556.EU_ID(+) = O179537.AP_EU_ID ) ) AND ( O179553.DOC_CONTENT_TYPE = 'application/vnd.oracle-disco.wb' ) AND ( O179553.DOC_CONTENT_TYPE = 'application/vnd.oracle-disco.wb' ) AND ( UPPER(DECODE(EUL_ADMIN.EUL5_GET_ISITAPPS_EUL,1,DECODE(O179556.EU_ROLE_FLAG,1,EUL_ADMIN.EUL5_GET_APPS_USERRESP(O179556.EU_USERNAME,'R'),

          DECODE(O179556.EU_USERNAME,NULL,'Document Not Shared',

          EUL_ADMIN.EUL5_GET_APPS_USERRESP(O179556.EU_USERNAME))),

          NVL(O179556.EU_USERNAME,'Document Not Shared'))) LIKE 'DOC%' )


          • 2. Re: Discoverer Workbooks
            clifford_d

            Try this.

             

            Move the one column that has the 'Document Not Shared' in it up to page items and get the ones that say that.. Or add another condition using that column.

             

            This is a modification of one of the reports that comes with the eul.

             

             

            O320036.AP_CREATED_DATE,

            EUL_ADMIN.EUL5_GET_ISITAPPS_EUL,1,DECODE(O320055.EU_ROLE_FLAG,1,EUL_ADMIN.EUL5_GET_APPS_USERRESP(O320055.EU_USERNAME,'R') ,

            O320055.EU_USERNAME,NULL,'Document Not Shared',EUL_ADMIN.EUL5_GET_APPS_USERRESP(O320055.EU_USERNAME))),

            O320055.EU_USERNAME,'Document Not Shared')) ds,

            EUL_ADMIN.EUL5_GET_ISITAPPS_EUL,1,EUL_ADMIN.EUL5_GET_APPS_USERRESP(O320052.DOC_CREATED_BY),O320052.DOC_CREATED_BY), O320052.DOC_CREATED_DATE, O320052.DOC_LENGTH/1024, O320052.DOC_NAME, 'Shared with ===>'

            .EUL5_ACCESS_PRIVS O320036, EUL_ADMIN.EUL5_DOCUMENTS O320052, EUL_ADMIN.EUL5_EUL_USERS O320055

            ( ( O320052.DOC_ID = O320036.GD_DOC_ID ) AND ( O320055.EU_ID = O320036.AP_EU_ID ) ) AND

            O320052.DOC_CONTENT_TYPE = 'application/vnd.oracle-disco.wb' ) AND

            O320052.DOC_CONTENT_TYPE = 'application/vnd.oracle-disco.wb' )

            BY O320052.DOC_NAME ASC, O320052.DOC_CREATED_DATE ASC, 'Shared with ===>' ASC,

            EUL_ADMIN.EUL5_GET_ISITAPPS_EUL,1,DECODE(O320055.EU_ROLE_FLAG,1,EUL_ADMIN.EUL5_GET_APPS_USERRESP(O320055.EU_USERNAME,'R'),DECODE(O320055.EU_USERNAME,NULL,'Document Not Shared',EUL_ADMIN.EUL5_GET_APPS_USERRESP(O320055.EU_USERNAME))),NVL(O320055.EU_USERNAME,'Document Not Shared'))