11 Replies Latest reply: May 4, 2009 9:38 AM by user11045839 RSS

    Query to find responsibilities from backend ?

    439121
      hello,

      When I check the resp of a particular user thru Define User scrren, I am able to see the resp
      But when I am checking with the following query, it says "no rows selected"
      The same query works for other user

      Can someone please give the right query

      select a.USER_NAME, a.EMPLOYEE_ID, b.RESPONSIBILITY_ID, c.RESPONSIBILITY_NAME, b.START_DATE, b.END_DATE
      from apps.FND_USER a, apps.FND_USER_RESP_GROUPS b, apps.FND_RESPONSIBILITY_TL c
      where a.USER_ID=b.USER_ID and b.RESPONSIBILITY_ID=c.RESPONSIBILITY_ID
      and user_name = ' <user_name>';

      Thanks
        • 1. Re: Query to find responsibilities from backend ?
          439121
          This quesry works

          select u.user_name, a.application_name, r.responsibility_name, r.responsibility_id, wu.end_date
          from apps.fnd_user u, apps.fnd_responsibility_tl r, apps.fnd_application_tl a, apps.wf_local_roles ur,
          apps.wf_user_role_assignments wu
          where u.user_name=wu.user_name and wu.role_name=ur.name
          and ur.display_name=r.responsibility_name
          and a.application_id=r.application_id
          and wu.user_name='RBEHARI' and
          (wu.end_date is null or wu.end_date >= sysdate) and
          a.language='US'
          order by u.user_name, r.responsibility_name



          Just wanted to know why the responsibilities are returned when joining with wf_* tables (2nd query) ? How are these related to workflow ?
          • 2. Re: Query to find responsibilities from backend ?
            Hussein Sawwan-Oracle
            You do not have to join with any wf tables to get the list of responsibilities for a specific user. Just use the below query instead (note the line in bold):

            SQL> SELECT B.RESPONSIBILITY_NAME
            FROM FND_USER_RESP_GROUPS A,
            FND_RESPONSIBILITY_VL B,
            FND_USER C
            WHERE A.responsibility_id = B.responsibility_id AND
            C.user_id = A.user_id AND
            (to_char(A.end_date) IS NULL
            OR A.end_date > sysdate)

            AND C.user_name = '&user';

            Another way to get the above (from front end) is to use the concurrent request "Users of a Responsibility".
            • 3. Re: Query to find responsibilities from backend ?
              555456
              i see nothing wrong with your first query.
              Is that the extra space inside quotes making the difference ??
              • 4. Re: Query to find responsibilities from backend ?
                Hussein Sawwan-Oracle
                I did not check the 1st query before .. Yes it is working with some modifications (Use FND_RESPONSIBILITY_VL instead of FND_RESPONSIBILITY_TL):

                SQL> select a.USER_NAME, a.EMPLOYEE_ID, b.RESPONSIBILITY_ID, c.RESPONSIBILITY_NAME, b.START_DATE, b.END_DATE
                from apps.FND_USER a, apps.FND_USER_RESP_GROUPS b, apps.FND_RESPONSIBILITY_VL c
                where a.USER_ID=b.USER_ID and b.RESPONSIBILITY_ID=c.RESPONSIBILITY_ID
                and user_name = UPPER('&user_name');

                If you use 'FND_RESPONSIBILITY_TL' as in the first query you might have duplicate records (In case you have languages installed other than the base language).

                'FND_RESPONSIBILITY_TL' -- Translations for FND_RESPONSIBILITY
                'FND_RESPONSIBILITY_VL' -- View of 'FND_RESPONSIBILITY' & 'FND_RESPONSIBILITY_TL'
                • 5. Re: Query to find responsibilities from backend ?
                  439121
                  checked for extra space
                  and FND_RESPONSIBILITY_VL also didn't help
                  • 6. Re: Query to find responsibilities from backend ?
                    439121
                    USER_NAME APPLICATION_NAME RESPONSIBILITY_NAME RESPONSIBILITY_ID END_DATE
                    ---------- -------------------- ------------------------------ ----------------- ---------
                    RBEHARI Cisco Payables NL AP Inquiry 52907
                    RBEHARI Cisco General Ledger NL GL User 52918
                    RBEHARI Cisco Corporate Purc NL PO Global Procurement Inqui 52872
                    hasing ry


                    SQL> l
                    1 select u.user_name, a.application_name, r.responsibility_name, r.responsibility_id, wu.end_date
                    2 from apps.fnd_user u, apps.fnd_responsibility_vl r, apps.fnd_application_vl a, apps.wf_local_roles ur,
                    3 apps.wf_user_role_assignments wu
                    4 where u.user_name=wu.user_name and wu.role_name=ur.name
                    5 and ur.display_name=r.responsibility_name
                    6 and a.application_id=r.application_id
                    7 and wu.user_name='RBEHARI' and
                    8 (wu.end_date is null or wu.end_date >= sysdate)
                    9 order by u.user_name, r.responsibility_name
                    10*

                    SQL> /

                    no rows selected

                    SQL> l
                    1 select a.USER_NAME, a.EMPLOYEE_ID, b.RESPONSIBILITY_ID, c.RESPONSIBILITY_NAME, b.START_DATE, b.END_DATE
                    2 from apps.FND_USER a, apps.FND_USER_RESP_GROUPS b, apps.FND_RESPONSIBILITY_VL c
                    3 where a.USER_ID=b.USER_ID and b.RESPONSIBILITY_ID=c.RESPONSIBILITY_ID
                    4* and user_name = 'RBEHARI'

                    -----------------------------------------------------------------------------------------------------------

                    My user id returns rows in both queries

                    USER_NAME APPLICATION_NAME RESPONSIBILITY_NAME RESPONSIBILITY_ID END_DATE
                    ---------- -------------------- ------------------------------ ----------------- ---------
                    PNATHANI System Administratio System Administrator 20420
                    n


                    SQL> l
                    1 select u.user_name, a.application_name, r.responsibility_name, r.responsibility_id, wu.end_date
                    2 from apps.fnd_user u, apps.fnd_responsibility_vl r, apps.fnd_application_vl a, apps.wf_local_roles ur,
                    3 apps.wf_user_role_assignments wu
                    4 where u.user_name=wu.user_name and wu.role_name=ur.name
                    5 and ur.display_name=r.responsibility_name
                    6 and a.application_id=r.application_id
                    7 and wu.user_name='PNATHANI' and
                    8 (wu.end_date is null or wu.end_date >= sysdate)
                    9* order by u.user_name, r.responsibility_name

                    USER_NAME EMPLOYEE_ID RESPONSIBILITY_ID RESPONSIBILITY_NAME START_DAT END_DATE
                    ---------- ----------- ----------------- ------------------------------ --------- ---------
                    PNATHANI 173045 20420 System Administrator 01-JAN-00 01-JAN-99

                    SQL> l
                    1 select a.USER_NAME, a.EMPLOYEE_ID, b.RESPONSIBILITY_ID, c.RESPONSIBILITY_NAME, b.START_DATE, b.END_DATE
                    2 from apps.FND_USER a, apps.FND_USER_RESP_GROUPS b, apps.FND_RESPONSIBILITY_VL c
                    3 where a.USER_ID=b.USER_ID and b.RESPONSIBILITY_ID=c.RESPONSIBILITY_ID
                    4* and user_name = 'PNATHANI'
                    • 7. Re: Query to find responsibilities from backend ?
                      Hussein Sawwan-Oracle
                      What do you mean by it did not help?

                      Login as APPS user and try the query below:

                      SQL> select a.USER_NAME, a.EMPLOYEE_ID, b.RESPONSIBILITY_ID, c.RESPONSIBILITY_NAME, b.START_DATE, b.END_DATE
                      from apps.FND_USER a, apps.FND_USER_RESP_GROUPS b, apps.FND_RESPONSIBILITY_VL c
                      where a.USER_ID=b.USER_ID and b.RESPONSIBILITY_ID=c.RESPONSIBILITY_ID
                      and user_name = 'RBEHARI';

                      It should work.
                      • 8. Re: Query to find responsibilities from backend ?
                        13808
                        Is this query below return any data ?

                        select * from wf_local_user_roles where user_name = 'PNATHANI', and see whether any role_orig_system_id refer to your responsibility_id for system administrator.

                        The reason is wf_local_user_roles is used in view FND_USER_RESP_GROUPS, normally it should show also, since it's shown in your first query.

                        If the query above is returning any rows, you can try to copy the FND_USER_RESP_GROUPS source and run, try commenting one by one to see which line that caused it to fail returning the responsibility.
                        • 9. Re: Query to find responsibilities from backend ?
                          439121
                          Thanks a ton
                          Found the issue with USER_END_DATE column

                          SQL> select user_start_date, user_end_date , role_name from apps.wf_local_user_roles where user_name='RBEHARI' ;

                          USER_STAR USER_END_ ROLE_NAME
                          --------- --------- ----------------------------------------------------------------------
                          01-DEC-00 03-JAN-07 FND_RESP|XXCAP|NL AP Inquiry|STANDARD
                          01-DEC-00 03-JAN-07 FND_RESP|XXCGL|NL GL User|STANDARD
                          01-DEC-00 03-JAN-07 FND_RESP|XXCPO|NL CORPORATE INQUIRY|STANDARD


                          But, where do tables WF_LOCAL_USER_ROLES and WF_USER_ROLE_ASSIGNMENTS take the user_end_date from ?
                          I thought FND_USER is the master table which has the user information
                          Also, if the user is end dated as per WF* tables, then her login itself shouldn’t go thru successfully

                          Any API thru which we can change the user_end_date ---- which would update all the required WF* tables ?
                          • 10. Re: Query to find responsibilities from backend ?
                            555456
                            Looks your issue is related to Role Based Access Control. Go through the topic in metalink. It might be of some help.
                            • 11. Re: Query to find responsibilities from backend ?
                              user11045839
                              SELECT fu.USER_NAME, fur.user_id, fur.responsibility_id,frt.RESPONSIBILITY_NAME, fur.responsibility_application_id,
                              fur.start_date, fur.end_date
                              FROM fnd_user_resp_groups_direct fur, fnd_responsibility_tl frt,fnd_user fu
                              WHERE fur.user_id = '1011901' ---ur user id "operations"
                              AND frt.responsibility_id = fur.responsibility_id
                              and fu.USER_ID=fur.USER_ID