Sunday, 15 May 2016

Add responsibilities and change user password from back-end in Oracle apps


BEGIN
-- Update the User Password 
   FND_USER_PKG.UPDATEUSER (x_user_name              => 'USER_NAMEX',
                            x_owner                  => 'APPS',
                            x_unencrypted_password   => 'oracle123',
                            x_password_date          => SYSDATE);


   FOR user_rec
-- Can Add n number of user to alter there profile at once 
      IN (SELECT user_name
            FROM fnd_user
           WHERE     employee_id IN
                        (SELECT person_id
                           FROM per_all_people_f a
                          WHERE     a.person_id =
                                       (SELECT employee_id
                                          FROM fnd_user
                                         WHERE user_name = 'USER_NAMEX') /*USe your user name, if you want to add responsibilities only for you*/
                                AND TRUNC (SYSDATE) BETWEEN a.effective_start_date
                                                        AND a.effective_end_date)
                 AND SYSDATE BETWEEN NVL (start_date, SYSDATE - 1)
                                 AND NVL (end_date, SYSDATE + 1))
   LOOP
-- ADD Responsibilities 
      FOR resp_rec
         IN (SELECT b.APPLICATION_SHORT_NAME, c.RESPONSIBILITY_KEY
               FROM fnd_responsibility_tl a,
                    fnd_application b,
                    fnd_responsibility c
              WHERE     a.application_id = b.application_id
                    AND a.responsibility_id = c.responsibility_id
                    AND (   a.responsibility_name LIKE 'Purchasing Super User -%'
                         OR a.responsibility_name LIKE 'OM SUPERUSER%'
                         OR a.responsibility_name LIKE 'Inventory Super%User -%'
                         OR a.responsibility_name LIKE 'Internet Procurement%'
                         OR a.responsibility_name = 'Receivables Super User%'
                         OR a.responsibility_name = 'XML Publisher Administrator'
                         OR a.responsibility_name = 'System Administrator'
                         OR a.responsibility_name = 'HR Global View'
                         OR a.responsibility_name = 'Application Administrator'
                         OR a.responsibility_name like 'Application Su%'
                         OR a.responsibility_name = 'Alert Manager'
                         OR a.responsibility_name LIKE 'Workflow Admini%'
                         OR a.responsibility_name = 'General Ledger Super User%'
                         OR a.responsibility_name LIKE 'Data Management Manager%'))
      LOOP
         fnd_user_pkg.addresp (user_rec.user_name,
                               resp_rec.APPLICATION_SHORT_NAME,
                               resp_rec.RESPONSIBILITY_KEY,
                               'STANDARD',
                               'Add Responsibility to USER using pl/sql',
                               SYSDATE,
                               SYSDATE + 100);
      END LOOP;
   END LOOP;

   COMMIT;
END;

No comments:

Post a Comment