NaturalOracle DataGaurd tutorial Series Coming Soon!! Natural
For Any queries, please mail us at support@funoracleapps.com

End Date a Responsibility for Users

1 comment
If we need to end date a responsibility for a user then we can use the below syntax.

Syntax:

DECLARE
   p_user_name           VARCHAR2 (50);
   p_resp_name           VARCHAR2 (50) := 'Application Developer'; /*Responsibility Name*/
   v_user_id             NUMBER (10) := 0;
   v_responsibility_id   NUMBER (10) := 0;
   v_application_id      NUMBER (10) := 0;
BEGIN
   BEGIN
      SELECT application_id, responsibility_id
        INTO v_application_id, v_responsibility_id
        FROM fnd_responsibility_vl
       WHERE UPPER (responsibility_name) = UPPER (p_resp_name);
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         DBMS_OUTPUT.put_line ('Responsibility not found.');
         RAISE;
      WHEN TOO_MANY_ROWS
      THEN
         DBMS_OUTPUT.
         put_line ('More than one responsibility found with this name.');
         RAISE;
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line ('Error finding responsibility.');
         RAISE;
   END;


   /*DBMS_OUTPUT.
   put_line (
         'Responsibility ID'
      || v_responsibility_id
      || 'and'
      || 'Application ID'
      || v_application_id);*/
  

   FOR u
      IN (SELECT fu.user_id, furg.start_date,furg.description
            FROM fnd_user_resp_groups_direct furg,
                 fnd_user fu,
                 fnd_responsibility_tl fr
           WHERE     fr.responsibility_name = p_resp_name
                 AND furg.user_id = fu.user_id
                 AND fu.user_name <> 'SYSADMIN'
                 AND furg.responsibility_id = fr.responsibility_id
                 AND fr.language = USERENV ('LANG'))
   LOOP
      v_user_id := u.user_id;

      /*DBMS_OUTPUT.put_line ('User_name' || v_user_id);*/

      BEGIN
         DBMS_OUTPUT.put_line ('Initializing The Application');

         fnd_global.
         apps_initialize (user_id        => v_user_id,
                          resp_id        => v_responsibility_id,
                          resp_appl_id   => v_application_id);

         DBMS_OUTPUT.
         put_line (
            'Calling FND_USER_RESP_GROUPS_API API To Insert/Update Resp');

         fnd_user_resp_groups_api.
         update_assignment (
            user_id                         => v_user_id,
            responsibility_id               => v_responsibility_id,
            responsibility_application_id   => v_application_id,
            security_group_id               => 0,
            start_date                      => u.start_date,
            end_date                        => TRUNC (SYSDATE) - 1,
            description                     => u.description);

         DBMS_OUTPUT.
         put_line ('The End Date has been set for responsibility');
         COMMIT;
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line ('Error calling the API');
            RAISE;
      END;
   END LOOP;
END;

1 comment :

  1. Your article gives me another approach on the subject.

    ReplyDelete