Script to add responsibilities to User in oracle Apps R12

DECLARE

   v_user_name        VARCHAR2 (20) := '&Enter_User_Name';

   v_req_resp_name    VARCHAR2 (50) := '&Enter_Required_Responsibility';

   v_description      VARCHAR2 (100) := 'Adding Responsibility to user using script';

   v_appl_shrt_name   VARCHAR2 (20);

   v_appl_name        VARCHAR2 (50);

   v_resp_key         VARCHAR2 (50);

BEGIN

   SELECT fav.application_short_name,

          fav.application_name,

          frv.responsibility_key

     INTO v_appl_shrt_name, v_appl_name, v_resp_key

     FROM FND_APPLICATION_VL fav, FND_RESPONSIBILITY_VL frv

    WHERE frv.application_id = fav.application_id

          AND frv.responsibility_name = v_req_resp_name;

   fnd_user_pkg.addresp (username         => v_user_name,

                         resp_app         => v_appl_shrt_name,

                         resp_key         => v_resp_key,

                         security_group   => 'STANDARD',

                         description      => v_description,

                         start_date       => SYSDATE,

                         end_date         => NULL);

   COMMIT;

   DBMS_OUTPUT.put_line ('The responsibility ' || v_req_resp_name || ' is added to the user ' || v_user_name);

EXCEPTION

   WHEN OTHERS THEN

      DBMS_OUTPUT.put_line ('Responsibility IS NOT added due to ' || SQLCODE || '; ' || SUBSTR (SQLERRM, 1, 250));

      ROLLBACK;

END;

Reset/update Password in Oracle Apps R12

DECLARE

   RetVal   BOOLEAN;

   NewPwd   VARCHAR2 (200) := 'welcome123';

   CURSOR c_user

   IS

      SELECT user_name

        FROM fnd_user

       WHERE employee_id IS NOT NULL

             AND (end_date IS NULL OR end_date > SYSDATE);

BEGIN

   FOR r_user IN c_user

   LOOP

      RetVal := APPS.FND_USER_PKG.CHANGEPASSWORD (r_user.user_name, NewPwd);

      COMMIT;

   END LOOP;

END;

Script to get scheduled concurrent programs in Oracle Apps R12

SELECT *

    FROM apps.FND_CONC_REQ_SUMMARY_V

   WHERE     phase_code = 'P'

         AND status_code IN ('I', 'Q')

         AND (NVL (request_type, 'X') != 'S')

         AND requested_start_date >= SYSDATE

ORDER BY program_short_name DESC;

• PHASE_CODE status P stands for Pending
• STATUS_CODE status I stands for Normal & status Q stands of Standby

Request_type != 'S' condition is to disallow Request Set Stage programs

Query to get failed concurrent program with log file path in Oracle Apps R12

  SELECT fcr.request_id,

         fu.user_name,

         fcp.user_concurrent_program_name,

         TO_CHAR (fcr.actual_start_date, 'DD-MON-YYYY HH24:MI:SS') actual_start_date,

         TO_CHAR (fcr.actual_completion_date, 'DD-MON-YYYY HH24:MI:SS') actual_completion_date,

         fcr.logfile_name

    FROM apps.fnd_concurrent_requests fcr,

         apps.fnd_concurrent_programs_tl fcp,

         apps.fnd_user fu

   WHERE     fcr.program_application_id = fcp.application_id

         AND fcr.concurrent_program_id = fcp.concurrent_program_id

         AND fcr.requested_by = fu.user_id

         AND fcr.phase_code = 'C'

         AND fcr.status_code = 'E'

         AND fcr.actual_completion_date > to_date('&Enter_Date','DD-MON-YYYY')

         AND fcp.user_concurrent_program_name NOT IN

                ('Request Set Stage', 'Report Set')

ORDER BY 1

Get Oracle Application url from Database in Oracle Apps R12

Find Oracle applications login url by running the below query from the database

SELECT home_url

  FROM icx_parameters;

When we have lot many instances(DEV/TEST/SIT/PROD) then we can use this query to get the application url for corresponding database