How to Search all of the Profile Options for a Specific Value

  SELECT p.profile_option_name SHORT_NAME
       , n.user_profile_option_name NAME
       , DECODE (
            v.level_id
          , 10001, 'Site'
          , 10002, 'Application'
          , 10003, 'Responsibility'
          , 10004, 'User'
          , 10005, 'Server'
          , 'UnDef'
         )
            LEVEL_SET
       , DECODE (
            TO_CHAR (v.level_id)
          , '10001', ''
          , '10002', app.application_short_name
          , '10003', rsp.responsibility_key
          , '10005', svr.node_name
          , '10006', org.name
          , '10004', usr.user_name
          , 'UnDef'
         )
            "CONTEXT"
       , v.profile_option_value VALUE
    FROM fnd_profile_options p
       , fnd_profile_option_values v
       , fnd_profile_options_tl n
       , fnd_user usr
       , fnd_application app
       , fnd_responsibility rsp
       , fnd_nodes svr
       , hr_operating_units org
   WHERE p.profile_option_id = v.profile_option_id(+)
     AND p.profile_option_name = n.profile_option_name
     AND usr.user_id(+) = v.level_value
     AND rsp.application_id(+) = v.level_value_application_id
     AND rsp.responsibility_id(+) = v.level_value
     AND app.application_id(+) = v.level_value
     AND svr.node_id(+) = v.level_value
     AND org.organization_id(+) = v.level_value
     AND v.PROFILE_OPTION_VALUE LIKE '%PASS YOUR PROFILE VALUE HERE%'
ORDER BY short_name, level_set;

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;
/