Useful Scripts

------------------------------------------------------------------
-- CREATE USER AND ATTACH SYS ADMIN
------------------------------------------------------------------

DECLARE

  v_session_id INTEGER := userenv('sessionid');

  v_user_name  VARCHAR2(30) := upper('&Enter_User_Name');

BEGIN

  --Note, can be executed only when you have apps password.

  -- Call the procedure to Creaet FND User

  fnd_user_pkg.createuser(x_user_name                  => v_user_name

    ,x_owner                      => ''

    ,x_unencrypted_password       => 'welcome123'

    ,x_session_number             => v_session_id

    ,x_start_date                 => SYSDATE - 10

    ,x_end_date                   => SYSDATE + 100

    ,x_last_logon_date            => SYSDATE - 10

    ,x_description                => 'test'

    ,x_password_date              => SYSDATE - 10

    ,x_password_accesses_left     => 10000

    ,x_password_lifespan_accesses => 10000

    ,x_password_lifespan_days     => 10000

    ,x_employee_id                => null /*Change this id by running below SQL*/

     /*   

     SELECT person_id

           ,full_name

     FROM   per_all_people_f

     WHERE  upper(full_name) LIKE '%' || upper('full_name') || '%'

     GROUP  BY person_id

              ,full_name

     */

    ,x_email_address => null

   ,x_fax           => ''

    ,x_customer_id   => ''

    ,x_supplier_id   => '');

  fnd_user_pkg.addresp(username       => v_user_name

                      ,resp_app       => 'SYSADMIN'

                      ,resp_key       => 'SYSTEM_ADMINISTRATOR'

                      ,security_group => 'STANDARD'

                      ,description    => 'Auto Assignment'

                      ,start_date     => SYSDATE - 10

                      ,end_date       => SYSDATE + 1000);

END;

------------------------------------------------------------------
-- DELETE MEDICAL ASSESSMENTS
------------------------------------------------------------------

DECLARE

CURSOR c IS
SELECT *
FROM per_medical_assessments;

BEGIN

FOR i IN c

LOOP

per_medical_assessment_api.delete_medical_assessment
(
p_medical_assessment_id => i.medical_assessment_id
,p_object_version_number => i.object_version_number
);
DBMS_OUTPUT.PUT_LINE('Success: person_id = '||i.person_id);

END LOOP;

END;