FND_USER QUERIES IN ORACLE APPS

Deleting Responsibility in User:

===============================

DECLARE

   v_user_name                  VARCHAR2 (100) := 'OPERATIONS';

   v_responsibility_name  VARCHAR2 (100) := 'User2_Report3_Resp';

   v_application_name      VARCHAR2 (100) := NULL;

   v_responsibility_key      VARCHAR2 (100) := NULL;

   v_security_group           VARCHAR2 (100) := NULL;

   v_description               VARCHAR2(100);

BEGIN

   SELECT fa.application_short_name,

                 fr.responsibility_key,

                 frg.security_group_key,                    

                 frt.description

      INTO  v_application_name,

                 v_responsibility_key,

                 v_security_group,

                 v_description

     FROM fnd_responsibility fr,

                 fnd_application fa,

                 fnd_security_groups frg,

                 fnd_responsibility_tl frt

    WHERE fr.application_id = fa.application_id

      AND    fr.data_group_id = frg.security_group_id

      AND    fr.responsibility_id = frt.responsibility_id

      AND    frt.LANGUAGE = USERENV ('LANG')

      AND    frt.responsibility_name = v_responsibility_name;

   fnd_user_pkg.delresp (username          => v_user_name,

                                              resp_app           => v_application_name,

                                              resp_key            => v_responsibility_key,

                                              security_group => v_security_group

                                             );

   COMMIT;

   DBMS_OUTPUT.put_line (   'Responsiblity '

                         || v_responsibility_name

                         || ' is removed from the user '

                         || v_user_name

                         || ' Successfully'

                        );

EXCEPTION

   WHEN OTHERS

   THEN

      DBMS_OUTPUT.put_line

         (   'Error encountered while deleting responsibilty from the user and the error is '

          || SQLERRM

         );

END;

To know the Counting of the Responisibilies in User:

===================================================

SELECT  count(1)

  FROM fnd_user_resp_groups furg,

              fnd_user fu,

              fnd_responsibility_tl frt

 WHERE furg.user_id = fu.user_id

   AND    furg.responsibility_id = frt.responsibility_id

   AND    fu.user_name = 'OPERATIONS'

   --AND    frt.responsibility_name = 'AZR_PROF_RESP1';

How to find the list of responsibilities assigned to user(s):

SELECT FURGA.USER_ID
, FU.USER_NAME
, FURGA.RESPONSIBILITY_ID
, FRTL.RESPONSIBILITY_NAME
, FURGA.RESPONSIBILITY_APPLICATION_ID
, FA.APPLICATION_SHORT_NAME
, FURGA.SECURITY_GROUP_ID
, FSG.SECURITY_GROUP_KEY
, FURGA.START_DATE
, FURGA.END_DATE
, FURGA.CREATED_BY
, FUCB.USER_NAME
, FURGA.CREATION_DATE
, FURGA.LAST_UPDATED_BY
, FULUB.USER_NAME
, FURGA.LAST_UPDATE_DATE
, FURGA.LAST_UPDATE_LOGIN
, FULUL.USER_NAME
FROM
FND_USER_RESP_GROUPS_ALL FURGA,
FND_USER FU,
FND_USER FUCB,
FND_USER FULUB,
FND_USER FULUL,
FND_APPLICATION FA,
FND_RESPONSIBILITY_TL FRTL,
FND_SECURITY_GROUPS FSG
WHERE
FURGA.USER_ID = FU.USER_ID (+)
AND FURGA.CREATED_BY = FUCB.USER_ID (+)
AND FURGA.LAST_UPDATED_BY = FULUB.USER_ID (+)
AND FURGA.LAST_UPDATE_LOGIN = FULUL.USER_ID (+)
AND FURGA.RESPONSIBILITY_APPLICATION_ID = FA.APPLICATION_ID (+)
AND FURGA.RESPONSIBILITY_ID = FRTL.RESPONSIBILITY_ID (+)
AND FRTL.LANGUAGE = ‘US’
AND FURGA.SECURITY_GROUP_ID = FSG.SECURITY_GROUP_ID (+)
ORDER BY START_DATE;
— ADD CONDITION ON START_DATE FOR RESPONSIBILITIES ADDED AFTER A SPECIFIC DATE LIKE “WHERE FURGA.START_DATE > TO_DATE(’22-JUL-2008′)”

TO KNOW THE LAST LOG ON DATE IN USER:

=====================================

Select User_name,to_char(last_logon_date,'DD-MON-YY HH24:MI') Last_Logon,last_update_date,email_address,description  from   fnd_user
where  user_name='OPERATIONS';
OR
SELECT User_name,to_char(last_logon_date,'DD-MON-YY HH24:MI') Last_Logon,last_update_date,email_address,description  from   fnd_user
WHERE trunc(last_logon_date) = trunc(sysdate)
and user_name='OPERATIONS'
order by to_char(last_logon_date,'DD-MON-YY HH24:MI') desc ;

1. Lock / Unlock from Application:

 Loing to Oracle EBS Application

 Login to Forms

 Security / User

 Go to user account 

 Lock/Unlock

 Save

2. Lock / Unlock from back end [connect to apps]:

SQL>

BEGIN

FND_USER_PKG.DisableUser(‘1234567’);

END;

/

PL/SQL procedure successfully completed.

SQL>

BEGIN

FND_USER_PKG.EnableUser(‘1234567’);

END;

/

PL/SQL procedure successfully completed.