Script to Submit the Workflow Background Processor from Backend

DECLARE
   l_errbuf                                          VARCHAR2 (1000);
   l_retcode                                         VARCHAR2 (2000);
BEGIN
   wf_engine.backgroundconcurrent (l_errbuf, l_retcode, 'OEOL', NULL, NULL, 'Y', 'Y', NULL);
   DBMS_OUTPUT.put_line ('Errbuf : ' || l_errbuf || ' Retcode : ' || l_retcode);
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('UNhandled Error : ' || SUBSTR (SQLERRM, 1, 250));
END;
/

Script To Send an Email in HTML Format using Oracle PL/SQL

DECLARE
   p_from                                            VARCHAR2 (2000) := 'xyz@xyz.com';
   p_to                                              VARCHAR2 (2000) := 'xyz@xyz.com';
   p_subject                                         VARCHAR2 (2000) := 'Test Mail';
   p_text_msg                                        VARCHAR2 (2000);
   p_smtp_host                                       VARCHAR2 (2000) := 'localhost';
   p_smtp_port                                       NUMBER := 25;
   p_html_msg                                        VARCHAR2 (4000);
   l_mail_conn                                       UTL_SMTP.connection;
   l_boundary                                        VARCHAR2 (50) := '----=*#abc1234321cba#*=';
BEGIN
      p_html_msg                                              :=
            'Hi All, <br><br> Please Find below the Exception Details.<br>
                    <hr>
                    <table>
                    <tr>
                    <th align="left">Source System  </th>
                    <th align="center"> : </th>
                    <td>'
         || 'p_source_system' -- Column value need to Print
         || '</td>
                    </tr>
                    <tr>
                    <th align="left">Error Type</th>
                    <th align="center"> : </th>
                    <td> <font color="#FF0000">'
         || 'ERROR/WARNING/SUCCESS' -- Column value need to Print
         || '</font> </td>
                    </tr>
                    <tr>
                    <th align="left">Reason</th>
                    <th align="center"> : </th>
                    <td>'
         || 'p_reason' -- Column value need to Print
         || '</td>
                    </tr>
                    </table>
                    <hr>
                    <table>
                    <tr>
                    <th align="left">Additional Information</th>
                    <th align="center"> : </th>
                    <td>'
         || 'p_additional_information' -- Column value need to Print
         || '</td>
                    </tr>
                    </table>
                    <hr> <br><br>
                    Thanks,<br>';


   l_mail_conn                                              := UTL_SMTP.open_connection (p_smtp_host, p_smtp_port);
   UTL_SMTP.helo (l_mail_conn, p_smtp_host);
   UTL_SMTP.mail (l_mail_conn, p_from);
   UTL_SMTP.rcpt (l_mail_conn, p_to);
   UTL_SMTP.open_data (l_mail_conn);
   UTL_SMTP.write_data (l_mail_conn, 'Date: ' || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || UTL_TCP.crlf);
   UTL_SMTP.write_data (l_mail_conn, 'To: ' || p_to || UTL_TCP.crlf);
   UTL_SMTP.write_data (l_mail_conn, 'From: ' || p_from || UTL_TCP.crlf);
   UTL_SMTP.write_data (l_mail_conn, 'Subject: ' || p_subject || UTL_TCP.crlf);
   UTL_SMTP.write_data (l_mail_conn, 'Reply-To: ' || p_from || UTL_TCP.crlf);
   UTL_SMTP.write_data (l_mail_conn, 'MIME-Version: 1.0' || UTL_TCP.crlf);
   UTL_SMTP.write_data (l_mail_conn
                      , 'Content-Type: multipart/alternative; boundary="' || l_boundary || '"' || UTL_TCP.crlf
                        || UTL_TCP.crlf
                       );

   IF p_text_msg IS NOT NULL
   THEN
      UTL_SMTP.write_data (l_mail_conn, '--' || l_boundary || UTL_TCP.crlf);
      UTL_SMTP.write_data (l_mail_conn, 'Content-Type: text/plain; charset="iso-8859-1"' || UTL_TCP.crlf || UTL_TCP.crlf);
      UTL_SMTP.write_data (l_mail_conn, p_text_msg);
      UTL_SMTP.write_data (l_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf);
   END IF;

   IF p_html_msg IS NOT NULL
   THEN
      UTL_SMTP.write_data (l_mail_conn, '--' || l_boundary || UTL_TCP.crlf);
      UTL_SMTP.write_data (l_mail_conn, 'Content-Type: text/html; charset="iso-8859-1"' || UTL_TCP.crlf || UTL_TCP.crlf);
      UTL_SMTP.write_data (l_mail_conn, p_html_msg);
      UTL_SMTP.write_data (l_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf);
   END IF;

   UTL_SMTP.write_data (l_mail_conn, '--' || l_boundary || '--' || UTL_TCP.crlf);
   UTL_SMTP.close_data (l_mail_conn);
   UTL_SMTP.quit (l_mail_conn);
END;
/

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