Wednesday, December 17, 2014

Concurrent & Responsibilities query


Salam Alaikum,

   I share with you guys this query that i use to get report information, it can also give you all the reports available for a specific user and by which responsibilities. And it can be used to get all the reports attached to a certain responsibility via its request group.

SELECT a.user_concurrent_program_name,
       b.request_group_name,
       e.responsibility_name
  FROM fnd_concurrent_programs_tl a,
       fnd_request_groups b,
       fnd_request_group_units c,
       fnd_responsibility d,
       fnd_responsibility_tl e,
       fnd_user_resp_groups_direct f,
       fnd_user g,
       fnd_concurrent_programs h
 WHERE     a.concurrent_program_id = c.request_unit_id
       AND a.concurrent_program_id = h.concurrent_program_id
       AND b.request_group_id = c.request_group_id
       AND b.request_group_id = d.request_group_id
       AND d.responsibility_id = e.responsibility_id
       AND b.application_id = c.application_id
       AND d.application_id = e.application_id
       AND f.user_id = g.user_id
       AND f.responsibility_id = e.responsibility_id
       AND UPPER (a.user_concurrent_program_name) LIKE
            '%' || UPPER (:p_concurrent_program_name) || '%'
       AND g.user_name = NVL (UPPER (:p_user_name), g.user_name)
       AND e.language = NVL (UPPER (:p_language), USERENV ('LANG'))
       AND SYSDATE BETWEEN NVL (
                            f.start_date,
                            fnd_date.canonical_to_date (
                             '0001/01/01 00:00:00'))
                       AND NVL (
                            f.end_date,
                            fnd_date.canonical_to_date (
                             '4000/12/31 23:59:59'));

Hope you found it useful guys, feel free to leave a comment if have any note on the subject. 

1 comment:

  1. Regards
    Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
    LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
    Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, SQL, PL/SQL, D2K at training@oracleappstechnical.com | +91 - 9581017828.

    ReplyDelete