Script to get the Password of user account

 You can use below script to get the existing password of Oracle EBS user account.

SELECT usertable.user_name ,

(SELECT get_pwd.decrypt (UPPER (

(SELECT

(SELECT get_pwd.decrypt (UPPER (

(SELECT UPPER (FND_WEB_SEC.GET_GUEST_USERNAME_PWD) FROM DUAL

)), usertable.encrypted_foundation_password)

FROM DUAL

) AS apps_password

FROM fnd_user usertable

WHERE usertable.user_name LIKE UPPER (

(SELECT SUBSTR (FND_WEB_SEC.GET_GUEST_USERNAME_PWD,1, INSTR (FND_WEB_SEC.GET_GUEST_USERNAME_PWD, '/')- 1)

FROM DUAL

))

)),usertable.encrypted_user_password)

FROM DUAL

) AS encrypted_user_password

FROM fnd_user usertable

WHERE usertable.user_name LIKE UPPER (<Insert username here>);

Script to reset Oracle Apps User Password

You can use below script to reset user password.

SET serveroutput ON;

DECLARE

  v_user_name    VARCHAR2(30):= UPPER(<Insert user name here>);

  v_new_password VARCHAR2(30):= 'Welcome1'; -- give preferred password here

  v_status       BOOLEAN;

BEGIN

  v_status   := fnd_user_pkg.ChangePassword ( username => v_user_name,

                                              newpassword => v_new_password

                                            );

  IF v_status =TRUE THEN

    dbms_output.put_line ('The password reset successfully for the User:'||v_user_name);

    COMMIT;

  ELSE

    DBMS_OUTPUT.put_line ('Unable to reset password due to'||SQLCODE||' '||SUBSTR(SQLERRM, 1, 100));

    ROLLBACK;

  END IF;

END;

Note:

This script do not work in case if user account is locked.

API to create Stock Locator

 create or replace procedure XX_CREATE_STOCK_LOCATORS
is
l_msg_data              VARCHAR2(100);
l_msg_count             NUMBER;
l_return_status         VARCHAR2(1);
l_locator_id            NUMBER;
l_locator_exists        VARCHAR2(1);
l_org_id                NUMBER := 1350; /*Organization_id */
l_organization_code     VARCHAR2(10) := '201'; /*Organization_Code */
l_sub_code              VARCHAR2(10) ; /*Variable for Subinventory*/
l_concatenated_segments VARCHAR2(100); /*Variable for Locator Segment*/
--l_user_id NUMBER := 1262; /* User ID From FND_users Table */
--l_resp_id NUMBER := 20634; /*Responsibility Id*/
--l_resp_appl_id NUMBER := 401; /* Responsibility Application id */
CURSOR C1
IS
SELECT * FROM XX_STOCK_LOCATOR_STAGING;
BEGIN
/*
* APPS_INITIALIZE Required because indirectly use profile options
-- FND_GLOBAL.APPS_INITIALIZE(l_user_id, l_resp_id,l_resp_appl_id);
*/
FND_GLOBAL.APPS_INITIALIZE(1090,51007,401);
FND_MSG_PUB.INITIALIZE;
  FOR I IN C1 LOOP

    l_concatenated_segments := i.LOCATOR_CONCAT_SEGMENTS;
    l_sub_code := 'Main Store';
    l_org_id:= 1350;
    INV_LOC_WMS_PUB.CREATE_LOCATOR(
            x_return_status             => l_return_status,
            x_msg_count                 => l_msg_count,
            x_msg_data                  => l_msg_data,
            x_inventory_location_id     => l_locator_id,
            x_locator_exists            => l_locator_exists,
            p_organization_id           => l_org_id,
            p_organization_code         => l_organization_code,
            p_concatenated_segments     => l_concatenated_segments,
            p_description               => 'DEFAULT', /*You can also use here description of Your Locator Combination*/
            p_inventory_location_type   => 3,         -- Storage locator
            p_picking_order             => NULL,
            p_location_maximum_units    => NULL,
            p_subinventory_code         => l_sub_code, /*Subinventory Code */
            p_location_weight_uom_code  => NULL,
            p_max_weight                => NULL,
            p_volume_uom_code           => NULL,
            p_max_cubic_area            => NULL,
            p_x_coordinate              => NULL,
            p_y_coordinate              => NULL,
            p_z_coordinate              => NULL,
            p_physical_location_id      => NULL,
            p_pick_uom_code             => NULL,
            p_dimension_uom_code        => NULL,
            p_length                    => NULL,
            p_width                     => NULL,
            p_height                    => NULL,
            p_status_id                 => 1, -- Default status 'Active'
            p_dropping_order            => NULL
            );
            
            
       DBMS_OUTPUT.PUT_LINE('Return Status '||l_concatenated_segments||' - '||l_return_status);
       
            IF l_return_status IN ('E', 'U') THEN
            DBMS_OUTPUT.PUT_LINE('# of Errors '||l_msg_count);
            IF l_msg_count = 1 THEN
            DBMS_OUTPUT.PUT_LINE('Error '||l_msg_data);
            ELSE
            FOR i IN 1..l_msg_count LOOP
            DBMS_OUTPUT.PUT_LINE('Error '||FND_MSG_PUB.GET(i, 'F'));
            END LOOP;
            END IF;
            ELSE
            DBMS_OUTPUT.PUT_LINE('Locator Id is '||l_locator_id);
            END IF;
            
   END LOOP;