1 Reply Latest reply: Nov 15, 2010 5:05 PM by Hussein Sawwan-Oracle RSS

    How to find which users only have a single and a specific responsibility

    747622
      Hi,
      I have a requirement in my organization that a password restriction have to be implemented. After implementing that, for some users, the password date will have to be nulled out, so that when they login, they will be forced to change their password following that new password rule.

      Oracle's "CP SQL*Plus Expire FND_USER Passwords" concurrent program can not be used as that will expire all the users password at the same time.

      For this, I have to find all the users which have a certain responsibility (check viewing responsibility) and only that responsibility. First day password date null out will be implemented for them only. For the other users (who have check printing responsibility) the password will be nulled out some other day.

      As I am using the following query, I see some of the users have multiple responsibility. How do I find out the users who have only a certain responsibility and nothing else?

      select usr.user_name, res.responsibility_name
      from apps.fnd_user usr, apps.FND_USER_RESP_GROUPS_DIRECT usrrep, apps.fnd_responsibility_tl res
      where usr.user_id=usrrep.user_id
      and usrrep.responsibility_id=res.RESPONSIBILITY_ID
      and usr.end_date is NULL
      group by usr.user_name,res.responsibility_name

      Any suggestion, idea will be very much appreciated.
        • 1. Re: How to find which users only have a single and a specific responsibility
          Hussein Sawwan-Oracle
          Hi,
          I have a requirement in my organization that a password restriction have to be implemented. After implementing that, for some users, the password date will have to be nulled out, so that when they login, they will be forced to change their password following that new password rule.

          Oracle's "CP SQL*Plus Expire FND_USER Passwords" concurrent program can not be used as that will expire all the users password at the same time.
          Use the same update statement in $FND_TOP/sql/AFCPEXPIRE.sql and include/exclude the usernames you want.
          For this, I have to find all the users which have a certain responsibility (check viewing responsibility) and only that responsibility. First day password date null out will be implemented for them only. For the other users (who have check printing responsibility) the password will be nulled out some other day.

          As I am using the following query, I see some of the users have multiple responsibility. How do I find out the users who have only a certain responsibility and nothing else?
          See (How To Find Out Which Responsibility Was Granted To A Specific User? [ID 304687.1]).

          Or, see old threads for similar topic (you will find many queries which should be helpful).

          http://forums.oracle.com/forums/search.jspa?threadID=&q=Users+responsibilities+query&objID=c3&dateRange=all&userID=&numResults=15&rankBy=10001
          select usr.user_name, res.responsibility_name
          from apps.fnd_user usr, apps.FND_USER_RESP_GROUPS_DIRECT usrrep, apps.fnd_responsibility_tl res
          where usr.user_id=usrrep.user_id
          and usrrep.responsibility_id=res.RESPONSIBILITY_ID
          and usr.end_date is NULL
          group by usr.user_name,res.responsibility_name
          Try this query ..
          SQL> select fu.user_name, count(frvl.responsibility_name)
          from fnd_user_resp_groups_direct furgd, fnd_responsibility_vl frvl, fnd_user fu
          where furgd.responsibility_id = frvl.responsibility_id
          and fu.user_id = furgd.user_id
          and (to_char(furgd.end_date) is null
          or furgd.end_date > sysdate)
          group by fu.user_name
          having count(frvl.responsibility_name) = 1;
          Thanks,
          Hussein