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

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
/

Query to Build the Relationship Hierarchy (HZ_RELATIONSHIP) in Oracle Apps

SELECT DISTINCT LPAD (' ', LEVEL * 2) || LEVEL
              , CONNECT_BY_ROOT hp1.party_name AS ROOT
              , SYS_CONNECT_BY_PATH(hp1.party_name, ':') AS CHAIN            
              , hp1.party_name parent_party
              , hp1.party_id parent_party_id
              , hp1.party_number parent_party_number
              , hp.party_name child_party
              , hp.party_id child_party_id
              , hp.party_number child_party_number
           FROM hz_relationships hr
              , hz_parties hp
              , hz_parties hp1
          WHERE object_id = hp.party_id
            AND hp1.party_id = hr.subject_id
            AND hp.status = 'A'
            AND hr.status = 'A'
            AND hr.direction_code = 'P'
            AND relationship_type = :p_hierarchy_type 
     START WITH hp1.party_id = :p_parent_party_id 
            AND relationship_type = :p_hierarchy_type 
            AND hr.status = 'A'
     CONNECT BY NOCYCLE PRIOR hr.object_id = hr.subject_id
            AND hr.object_type = PRIOR hr.subject_type
            AND hr.object_table_name = PRIOR hr.subject_table_name
            AND hr.direction_code = 'P'
            AND hr.status = 'A'
       ORDER SIBLINGS BY hp.party_name

SQL to Delete Duplicate Rows in a Table

DELETE FROM xx_table 
 WHERE ROWID NOT IN (SELECT MIN(ROWID) 
                       FROM xx_table 
                      GROUP BY col1, col2....);

Convert LONG datatypes to LOB's or Query on the Long Data type Columns

CREATE TABLE XX_LOG_STG(C1 NUMBER, C2 LONG)
/
INSERT INTO  XX_LOG_STG VALUES (1, 'LONG data to convert to CLOB')
/
-- Insert LONG into LOB column
CREATE TABLE XX_LOB_STG(C1 NUMBER, C2 CLOB);

-- Use TO_LOB function to convert LONG to LOB...
INSERT INTO  XX_LOB_STG
       SELECT C1, TO_LOB(C2) FROM XX_LOG_STG;

CREATE TABLE XX_DBA_TRIGGERS AS SELECT TRIGGER_NAME, TO_LOB(TRIGGER_BODY) AS TRIGGER_BODY

FROM DBA_TRIGGERS ;

-- Query on the Long Data type Column i.e. Trigger Body as below




SELECT * FROM XX_DBA_TRIGGERS WHERE UPPER(TRIGGER_BODY) LIKE '%FND_REQUEST%' ;