Query to find active responsibilities in oracle apps

Query to find active responsibilities in oracle apps

In this post , We will be discuss about Query to find active responsibilities in oracle apps. This sql query will help to extract the list of active responsibilities in oracle apps. We will be able to find out the list of all active responsibilities in oracle apps. This is one of the useful sql query to get the details of responsibilities in oracle apps. Her below is the detail about Query to find active responsibilities in oracle apps.

Query to find active responsibilities in oracle apps

3 Important Table used by sql query to find active responsibilities in Oracle Apps

1.fnd_responsibility_vl

2.fnd_user fu,

3.fnd_user_resp_groups_direct

Detail SQL Query to find active responsibilities in oracle apps

SELECT frv.responsibility_name,

       frv.responsibility_key,

       TO_CHAR (frv.start_date, 'DD-MON-YYYY') "START_DATE",

       TO_CHAR (frv.end_date, 'DD-MON-YYYY') "END_DATE"

FROM fnd_responsibility_vl frv

WHERE  frv.start_date                  <= sysdate

AND NVL(frv.end_date, sysdate + 1)   > sysdate;

SELECT fu.user_name,

       frv.responsibility_name,

       frv.responsibility_key,

       TO_CHAR (furgd.start_date, 'DD-MON-YYYY') "START_DATE",

       TO_CHAR (furgd.end_date, 'DD-MON-YYYY') "END_DATE"

FROM fnd_user fu,

  fnd_user_resp_groups_direct furgd,

  fnd_responsibility_vl frv

WHERE fu.user_id                     = furgd.user_id

AND furgd.responsibility_id          = frv.responsibility_id

AND furgd.end_date IS NULL

AND furgd.start_date                <= sysdate

AND NVL(furgd.end_date, sysdate + 1) > sysdate

AND fu.start_date                   <= sysdate

AND NVL(fu.end_date, sysdate + 1)    > sysdate

AND frv.start_date                  <= sysdate

AND NVL(frv.end_date, sysdate + 1)   > sysdate;