Query to get oracle ebs roles and responsibilities

Hi friends, We are going to discuss about the query to get oracle ebs roles and responsibilities. We will share the detail sql query which will extract the complete oracle ebs roles and responsibilities details/information’s in oracle apps. Using this query, we will be able to develop the custom reports which helps to extract the oracle ebs user, roles and responsibilities details in oracle apps. This is one of the important sql query in oracle ebs related to oracle ebs roles and responsibilities. Please find below the complete query to get oracle ebs roles and responsibilities.

Query to get oracle ebs roles and responsibilities

Important table uses in the query to get oracle ebs roles and responsibilities.

1.fnd_responsibility_tl 

2. fnd_application   

3. fnd_user_resp_groups_direct

4. fnd_application_tl

5. fnd_user

6. fnd_responsibility

Detail Query to get oracle ebs roles and responsibilities

Here below is the sql query to extract the complete detail about oracle ebs user roles and responsibilities details .

 

SELECT fu.user_name               ,

       frt.responsibility_name   ,

       furg.start_date           ,

       furg.end_date           ,     

       fr.responsibility_key      ,

       fa.application_short_name

  FROM fnd_user_resp_groups_direct        furg,

       applsys.fnd_responsibility         fr,

       applsys.fnd_application_tl         fat,

       applsys.fnd_application            fa,

       applsys.fnd_user                   fu,

       applsys.fnd_responsibility_tl      frt

 WHERE furg.user_id             =  fu.user_id

   AND FU.END_DATE is null

   AND furg.END_DATE is null

   AND furg.responsibility_id   =  frt.responsibility_id

   AND fr.responsibility_id     =  frt.responsibility_id

   AND fa.application_id        =  fat.application_id

   AND fr.application_id        =  fat.application_id

 ORDER BY fu.user_name,frt.responsibility_name;

Query to get oracle ebs roles and responsibilities