Script to Raise the Business Event In Oracle Applications

Below is the Sample Script to raise the Business Event .

DECLARE
   l_event_name                                      VARCHAR2 (240) := 'xx.apps.ar.hz.Organization.upsert';
   l_event_parameter_list                            wf_parameter_list_t := wf_parameter_list_t ();
BEGIN
   wf_event.addparametertolist (p_name                        => '<Parameter Name>'
                              , p_value                       => '<Parameter Value>'
                              , p_parameterlist               => l_event_parameter_list
                               );
   wf_event.addparametertolist (p_name                        => '<Parameter Name1>'
                              , p_value                       => '<Parameter Value1>'
                              , p_parameterlist               => l_event_parameter_list
                               );
   wf_event.addparametertolist (p_name                        => '<Parameter Name2>'
                              , p_value                       => '<Parameter Value2>'
                              , p_parameterlist               => l_event_parameter_list
                               );

   BEGIN
      wf_event.RAISE (p_event_name => l_event_name, p_event_key => SYS_GUID (), p_parameters => l_event_parameter_list);
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line (' Unexpected Error  : ' || SUBSTR (SQLERRM, 1, 250));
   END;

   l_event_parameter_list.DELETE;
EXCEPTION
   WHEN OTHERS
   THEN
      NULL;
END;
/***************************************************************************/
                                                      OR use Below Script
/***************************************************************************/

DECLARE
   l_error                                           VARCHAR2 (2000);
   l_event_parameter_list                            wf_parameter_list_t;
   l_param                                           wf_parameter_t;
   l_event_name                                      VARCHAR2 (100) := 'oracle.apps.ar.hz.Party.merge';
   l_parameter_index                                 NUMBER := 0;
   l_error_msg                                       VARCHAR2 (2000);
BEGIN
   --1st parameter to the Event if exists
   l_event_parameter_list                                   := wf_parameter_list_t ();
   l_param                                                  := wf_parameter_t (NULL, NULL);
   l_event_parameter_list.EXTEND;
   l_param.setname ('batch_id');
   l_param.setvalue (132054);
   l_parameter_index                                        := l_parameter_index + 1;
   l_event_parameter_list (l_parameter_index)               := l_param;
   --2nd parameter to the Event if exists
   l_param                                                  := wf_parameter_t (NULL, NULL);
   l_event_parameter_list.EXTEND;
   l_param.setname ('PARMA_2');
   l_param.setvalue ('VALUE_FOR_PARMA_2');
   l_parameter_index                                        := l_parameter_index + 1;
   l_event_parameter_list (l_parameter_index)               := l_param;
   --3rd parameter to the Event if exists
   l_param                                                  := wf_parameter_t (NULL, NULL);
   l_event_parameter_list.EXTEND;
   l_param.setname ('PARAM3');
   l_param.setvalue ('VALUE_FOR_PARMA_3');
   l_parameter_index                                        := l_parameter_index + 1;
   l_event_parameter_list (l_parameter_index)               := l_param;

   BEGIN
      wf_event.RAISE (p_event_name                  => l_event_name
                    , p_event_key                   => 'oracle.apps.ar.hz.Party.merge132054'
                    , p_parameters                  => l_event_parameter_list
                    , p_send_date                   => SYSDATE
                     );
      COMMIT;
   EXCEPTION
      WHEN OTHERS
      THEN
         l_error_msg                                              := 'Unexpected Error : ' || SUBSTR (SQLERRM, 1, 250);
         DBMS_OUTPUT.put_line (l_error_msg);
   END;
EXCEPTION
   WHEN OTHERS
   THEN
      l_error_msg                                              := 'Unexpected Error In main : ' || SUBSTR (SQLERRM, 1, 250);
      DBMS_OUTPUT.put_line (l_error_msg);
END;
/

Important Unix Scripts

1.  Script To Find the Line Numbers In a Files For a Particular String

Syntax :- sed -n '/String_to_search/=' file_name.txt

Ex:  sed -n '/LAST_UPDATE_DATE/=' XX_CONC_PROG.txt

2. Script To Change/replace the Line in a File
The Script is used to replace a line in a File for a Particular string in the Line

Syntax : sed -i '/String_in_a_line/ c\New_String_to_be_added ' XX_FILE_NAME.txt

 Ex: sed -i '/LAST_UPDATE_DATE =/ c\LAST_UPDATE_DATE = "2013/07/17"' XX_FILE_NAME.txt

3. Script To replace a String in a File
The script is used to replace a String in the File.

Syntax: sed -i 's/String to Stearch/string_to_replaced/g' file_name.txt

Ex : sed -i 's/LAST_UPDATE_DATE =/LAST_UPDATE_DATE=SYS/g' XX_FILE_NAME.txt

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.