Forum Stats

  • 3,855,345 Users
  • 2,264,499 Discussions
  • 7,905,975 Comments

Discussions

Oracle Discoverer Workbook Results share USER Responsibilities 11g

edab
edab Member Posts: 39
edited Oct 23, 2012 12:25PM in Discoverer
Hello,

I need to know if discoverer workbook results set is share to the user or responsibilities. I don't want to see any workbook is share to user or responsibilities, just the Results Sets.

I am using eul5_batch_reports table to but i don't know which column should i join now I am getting workbook and workbook results set.

Oracle Application EUL (11.5.10.2)
End User Layer 5.1.1.0.0.0
End User Layer Library - 11.1.1.3.0


I am trying to use the following query but i am not able to make join with Schedule EUL table.

SELECT DISTINCT docs.doc_name,priv.ap_type,gd_doc_id,GP_APP_ID,AP_EU_ID,ebrpt.BR_ID,ebrpt.br_name,
fnd.responsibility_name,
DECODE (usr.eu_role_flag, 0, 'user', 1, 'role') user_role,
usr.eu_username,
CASE
WHEN INSTR (usr.eu_username, '#') = 0 THEN
usr.eu_username
WHEN INSTR (usr.eu_username, '#') > 0
AND INSTR (usr.eu_username, '#', 2) = 0 THEN
(SELECT fu.user_name
FROM fnd_user fu
WHERE fu.user_id = SUBSTR (usr.eu_username, 2, 5))
ELSE
(SELECT resp.responsibility_name
FROM fnd_responsibility_tl resp
WHERE resp.responsibility_id =
SUBSTR (usr.eu_username, 2, 5)
AND language = 'US')
END
AS "Shared Name / Responsibility"
FROM eul5_documents docs,
eul5_access_privs priv,
eul5_eul_users usr,
fnd_responsibility_tl fnd,
eul5_batch_reports ebrpt
WHERE docs.doc_id = priv.gd_doc_id(+)
AND priv.ap_eu_id = usr.eu_id(+)
AND usr.eu_username =
'#' || fnd.responsibility_id(+) || '#' || fnd.application_id(+)
AND priv.ap_type = 'GD'
AND docs.doc_name LIKE '%XXTEST%'
AND ebrpt.br_name = docs.doc_name
AND docs.doc_eu_id = ebrpt.br_eu_id

Any help appreciated.
Ed

Best Answer

  • asimshafi
    asimshafi Member Posts: 195
    Hello,
    Try this.


    SELECT DISTINCT docs.doc_name,ebrpt.br_name,
    fnd.responsibility_name,
    DECODE (usr.eu_role_flag, 0, 'user', 1, 'role') user_role,
    usr.eu_username,
    CASE
    WHEN INSTR (usr.eu_username, '#') = 0 THEN
    usr.eu_username
    WHEN INSTR (usr.eu_username, '#') > 0
    AND INSTR (usr.eu_username, '#', 2) = 0 THEN
    (SELECT fu.user_name
    FROM fnd_user fu
    WHERE fu.user_id = SUBSTR (usr.eu_username, 2, 5))
    ELSE
    (SELECT resp.responsibility_name
    FROM fnd_responsibility_tl resp
    WHERE resp.responsibility_id =
    SUBSTR (usr.eu_username, 2, 5)
    AND language = 'US')
    END
    AS "Shared Name / Responsibility"
    FROM euldiscadmin_us.eul5_documents docs,
    euldiscadmin_us.eul5_access_privs priv,
    euldiscadmin_us.eul5_eul_users usr,
    fnd_responsibility_tl fnd,
    euldiscadmin_us.eul5_batch_reports ebrpt
    WHERE docs.doc_id = priv.gd_doc_id(+)
    AND priv.ap_eu_id = usr.eu_id(+)
    AND usr.eu_username =
    '#' || fnd.responsibility_id(+) || '#' || fnd.application_id(+)
    AND priv.ap_type = 'GD'
    AND ebrpt.br_name = docs.doc_name
    AND docs.doc_eu_id = ebrpt.br_eu_id
    AND docs.doc_batch=1
    AND EXISTS (SELECT 'X'
    FROM
    EULDISCADMIN_US.EUL5_BR_RUNS brrun
    WHERE brrun.BRR_BR_ID=ebrpt.BR_ID)

    Cheers
    Asim

Answers

  • asimshafi
    asimshafi Member Posts: 195
    Hello,
    Try this.


    SELECT DISTINCT docs.doc_name,ebrpt.br_name,
    fnd.responsibility_name,
    DECODE (usr.eu_role_flag, 0, 'user', 1, 'role') user_role,
    usr.eu_username,
    CASE
    WHEN INSTR (usr.eu_username, '#') = 0 THEN
    usr.eu_username
    WHEN INSTR (usr.eu_username, '#') > 0
    AND INSTR (usr.eu_username, '#', 2) = 0 THEN
    (SELECT fu.user_name
    FROM fnd_user fu
    WHERE fu.user_id = SUBSTR (usr.eu_username, 2, 5))
    ELSE
    (SELECT resp.responsibility_name
    FROM fnd_responsibility_tl resp
    WHERE resp.responsibility_id =
    SUBSTR (usr.eu_username, 2, 5)
    AND language = 'US')
    END
    AS "Shared Name / Responsibility"
    FROM euldiscadmin_us.eul5_documents docs,
    euldiscadmin_us.eul5_access_privs priv,
    euldiscadmin_us.eul5_eul_users usr,
    fnd_responsibility_tl fnd,
    euldiscadmin_us.eul5_batch_reports ebrpt
    WHERE docs.doc_id = priv.gd_doc_id(+)
    AND priv.ap_eu_id = usr.eu_id(+)
    AND usr.eu_username =
    '#' || fnd.responsibility_id(+) || '#' || fnd.application_id(+)
    AND priv.ap_type = 'GD'
    AND ebrpt.br_name = docs.doc_name
    AND docs.doc_eu_id = ebrpt.br_eu_id
    AND docs.doc_batch=1
    AND EXISTS (SELECT 'X'
    FROM
    EULDISCADMIN_US.EUL5_BR_RUNS brrun
    WHERE brrun.BRR_BR_ID=ebrpt.BR_ID)

    Cheers
    Asim
This discussion has been closed.