Useful Scripts for E-Business Suite Applications Analysts

1. How to check if a patch is applied?

select * from ad_bugs
where bug_number = :bug_number;

select * from ad_applied_patches
where patch_name = :bug_number;

SELECT DISTINCT a.bug_number, e.patch_name, c.end_date, b.applied_flag
FROM ad_bugs a,
  ad_patch_run_bugs b,
  ad_patch_runs c,
  ad_patch_drivers d,
  ad_applied_patches e
WHERE a.bug_id = b.bug_id
AND b.patch_run_id = c.patch_run_id
AND c.patch_driver_id = d.patch_driver_id
AND d.applied_patch_id = e.applied_patch_id
AND a.bug_number LIKE ':bug_number'
ORDER BY 1 DESC ;

2. How to find the patch set level for an application

SELECT SUBSTR (aa.application_short_name, 1, 20) "Product"
     , a.patch_level "Patch Level"
  FROM fnd_product_installations a
     , fnd_application aa
 WHERE a.application_id = aa.application_id
   AND aa.application_short_name LIKE '%appl_short_name%';

3. How to find instance name, host name, apps and RDBMS versions of the instance user is logged into

SELECT i.instance_name
     , i.host_name
     , f.release_name release
     , i.VERSION
  FROM v$instance i
     , fnd_product_groups f
 WHERE UPPER (SUBSTR (i.instance_name, 1, 4)) = UPPER (SUBSTR (f.applications_system_name, 1, 4));

5. How to find the latest version of a file on a given instance

SELECT   sub.filename
       , sub.VERSION
    FROM (SELECT adf.filename filename
               , afv.VERSION VERSION
               , RANK () OVER (PARTITION BY adf.filename ORDER BY afv.version_segment1 DESC
                , afv.version_segment2 DESC
                , afv.version_segment3 DESC
                , afv.version_segment4 DESC
                , afv.version_segment5 DESC
                , afv.version_segment6 DESC
                , afv.version_segment7 DESC
                , afv.version_segment8 DESC
                , afv.version_segment9 DESC
                , afv.version_segment10 DESC
                , afv.translation_level DESC) AS rank1
            FROM ad_file_versions afv
               , (SELECT filename
                       , app_short_name
                       , subdir
                       , file_id
                    FROM ad_files
                   WHERE UPPER (filename) LIKE UPPER ('%&filename%')) adf
           WHERE adf.file_id = afv.file_id) sub
   WHERE rank1 = 1
ORDER BY 1

You can enter partial file names and the search is not case sensitive.

For example you can search on "glxjeent" for the form "GLXJEENT.fmb" or "frmsheet1" for java file "FrmSheet1VBA.class".

Note: This script works for the following file types:

- .class, .drvx, .fmb, .htm, .lct, .ldt, .o, .odf, .pkb, .pkh, .pls, .rdf, .rtf, .sql, .xml.
It doens't work for .lpc, .lc files, etc. 

6. How to find the applications in the system that are either installed shared?

SELECT fat.application_id
     , fat.application_name
     , fdi.status
     , fdi.patch_level
  FROM fnd_application_tl fat
     , fnd_product_installations fdi
 WHERE fdi.application_id = fat.application_id
   AND fdi.status IN ('I', 'S')

Note: Status 'I' meaning installed and status 'S' meaning shared.

  

7. How to get the header file versions for an executable in Unix

Example 1

Log into UNIX.
> cd $AP_TOP/bin
> strings -a APXXTR |grep Header

Example 2

> cd $RG_TOP/bin
> Strings -a RGRARG |grep Header

The above will provide the versions of all the header files in those executable. 

Note: the command adident (in unix, windows and other OS) can also be used to provide the file versions.

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)
                )