Script To Get the application usernames and passwords

-- Package Specification

CREATE OR REPLACE PACKAGE get_user_password
AS
   FUNCTION decrypt (
      KEY                        IN             VARCHAR2
    , VALUE                      IN             VARCHAR2
   )
      RETURN VARCHAR2;
END get_user_password;

-- Package Body 

CREATE OR REPLACE PACKAGE BODY get_user_password
AS
   FUNCTION decrypt (
      KEY                        IN             VARCHAR2
    , VALUE                      IN             VARCHAR2
   )
      RETURN VARCHAR2
   AS
      LANGUAGE JAVA
      NAME 'oracle.apps.fnd.security.WebSessionManagerProc.decrypt(java.lang.String,java.lang.String) return java.lang.String';
END get_user_password;




/** Run this on toad, Get the application usernames and passwords */
SELECT usertable.user_name
     , (SELECT get_user_password.decrypt
                  (UPPER
                      ((SELECT (SELECT get_user_password.decrypt (UPPER ((SELECT UPPER (fnd_profile.VALUE ('GUEST_USER_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_profile.VALUE ('GUEST_USER_PWD')
                                                       , 1
                                                       , INSTR (fnd_profile.VALUE ('GUEST_USER_PWD'), '/') - 1
                                                        )
                                            FROM DUAL)
                                        ))
                      )
                 , usertable.encrypted_user_password
                  )
          FROM DUAL) AS encrypted_user_password
  FROM fnd_user usertable
 WHERE usertable.user_name LIKE UPPER ('&username')   -- SYSADMIN

/** Run this on toad, Get the DB apps password */
SELECT (SELECT get_user_password.decrypt (UPPER ((SELECT UPPER (fnd_profile.VALUE ('GUEST_USER_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_profile.VALUE ('GUEST_USER_PWD')
                               , 1
                               , INSTR (fnd_profile.VALUE ('GUEST_USER_PWD'), '/') - 1
                                )
                    FROM DUAL)
                )

API To Add Responsibilty To User

/* Grant Particular Responsibility to a User */
SET serveroutput on
DECLARE
   v_user_name                                       VARCHAR2 (30) := UPPER ('Oracle FND User');
   v_resp                                            VARCHAR2 (255) := 'Application Administrator';
   v_resp_key                                        VARCHAR2 (30);
   v_app_short_name                                  VARCHAR2 (50);
BEGIN
   SELECT r.responsibility_key
        , a.application_short_name
     INTO v_resp_key
        , v_app_short_name
     FROM apps.fnd_responsibility_vl r
        , apps.fnd_application_vl a
    WHERE r.application_id = a.application_id
      AND UPPER (r.responsibility_name) = UPPER (v_resp);
   apps.fnd_user_pkg.addresp (username                      => v_user_name
                            , resp_app                      => v_app_short_name
                            , resp_key                      => v_resp_key
                            , security_group                => 'STANDARD'
                            , description                   => NULL
                            , start_date                    => SYSDATE
                            , end_date                      => NULL
                             );
   COMMIT;
   DBMS_OUTPUT.put_line ('Responsibility:' || v_resp || ' ' || 'is added to the User:' || v_user_name);
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Unable to add the responsibility due to' || SQLCODE || ' ' || SUBSTR (SQLERRM, 1, 100));
      ROLLBACK;
END;
/

ORA-29861: domain index is marked LOADING/FAILED/UNUSABLE

1. Run the Below Script

SELECT OWNER, INDEX_NAME, INDEX_TYPE, STATUS, DOMIDX_STATUS, DOMIDX_OPSTATUS ,TABLE_NAME FROM DBA_INDEXES
WHERE INDEX_TYPE='DOMAIN'
AND INDEX_NAME LIKE 'HZ%'

the result of the above query will be

OWNER    INDEX_NAME    INDEX_TYPE    STATUS    DOMIDX_STATUS    DOMIDX_OPSTATUS    TABLE_NAME

AR    HZ_STAGE_PARTIES_T1    DOMAIN    VALID    VALID    FAILED    HZ_STAGED_PARTIES

2. Drop the Invalid Index.

3. Create script as mentioned below

CREATE INDEX AR.HZ_STAGE_CONTACT_T1 ON AR.HZ_STAGED_CONTACTS
    (CONCAT_COL) INDEXTYPE IS CTXSYS.CONTEXT

It will resolve the ORA-29861

Script To Delete the Business Events and Subscriptions

SELECT we.guid event_guid
     , wes.guid subscription_guid
     , wes.rule_function
  FROM wf_events we
     , wf_event_subscriptions wes
 WHERE we.NAME = 'oracle.apps.ar.hz.CustAccount.create'
   AND wes.event_filter_guid = we.guid
   AND wes.status = 'ENABLED';

EXEC WF_EVENTS_PKG.DELETE_ROW(:event_guid);

EXEC WF_EVENT_SUBSCRIPTIONS_PKG.DELETE_ROW(:subscription_guid);

COMMIT;

Convert Number to Words

SELECT DECODE (SIGN (:p_number),  -1, 'Negative ',  0, 'Zero',  NULL)
       || DECODE (SIGN (ABS (:p_number)), +1, TO_CHAR ( TO_DATE ( ABS (:p_number), 'J'), 'Jsp'))
  FROM DUAL
/