Idera AI Documentation Assistant

When the Oracle Applications Tier Collectors have been installed, post-installation tasks are required. Depending on your application settings, you may need to stop all Insight Savvies that are currently running on the respective server and restart them. In addition, you need to grant permissions to schema user as described below and activate an Interpoint for Oracle Applications, as well as configure the Interpoint for Oracle Applications for the purpose of data collection.

Granting permissions to schema user

Connect to the Oracle instance on which the corresponding collector is installed as DBA user and run query:

grant execute on DBMS_PIPE to <collector_schema_user>

Activating the Interpoint for Oracle Applications

To activate an Interpoint for Oracle Applications

  1. Log in to the server and run the following script to install the Interpoint:
    Windows
         \products\oracle\SID\schema\install_oracle_apps.bat
    UNIX
         ./products/oracle/SID/schema/install_oracle_apps.sh
    where <precise_root> is the name of the Precise root folder.
  2. To verify that the installation was successful, run the following script:
    Windows
         verify_install_oracle_apps.bat
    UNIX
         verify install oracle apps.sh
  3. Configure the Oracle Applications application.
    For more information on how to configure the Oracle Applications application, see Configuring an Interpoint for Oracle Applications for data collection.
  4. Restart the Precise for Oracle Collector agent for this instance.

Configuring an Interpoint for Oracle applications for data collection

The Interpoint for Oracle Applications identifies activities of Oracle Applications by monitoring the events that are called by forms. Configuring an Interpoint for Oracle Applications to collect the relevant data involves these tasks:

Manually updating the customization library

To have the Interpoint for Oracle Applications collect forms data, you must manually add some information to the customization library of the Oracle Applications forms. This information includes user, form, and application names.

The customization library consists of the following files:

  • CUSTOM.pld
  • CUSTOM.pll
  • CUSTOM.plx

You can only modify the CUSTOM.pld file. This file does not exist by default but can be extracted from the CUSTOM.pll file. For the changes to take a global effect in the customization library after they are implemented in the CUSTOM.pld file, you need to regenerate the CUSTOM.pll and CUSTOM.plx files from the CUSTOM.pld file.

The file names may appear lowercase or uppercase (for example CUSTOM.pll). In the following task, all filenames are uppercase. If you copy-paste this information, verify that the case matches the actual file names on your system.

To update the customization library on UNIX

  1. Find the CUSTOM.pll and CUSTOM.plx files, usually located in your Oracle Applications folder.
  2. Back up the CUSTOM.pll and CUSTOM.plx files and, if it exists, also the CUSTOM.pld file.
  3. Verify that no Oracle Applications user is currently logged on to the database or any other database using the CUSTOM.pll and CUSTOM.plx files.
  4. Change to the folder that contains the CUSTOM.pll file. This folder is pointed to by the application variable $FORMS60_PATH.

  5. To extract the CUSTOM.pld file, run the following command using the Oracle account of Oracle Applications (such as APPS):
         f60gen Module=CUSTOM.pll Module_Type=LIBRARY userid=user/password script=yes

    For Oracle Applications version R12, replace f60gen command with frmcmp_batch.

  6. Run the following command to edit the file:
         vi CUSTOM.pld
  7. Find the EVENT procedure, locate the first BEGIN after the commented sample code, and insert the following code, starting with BEGIN.
    Do not insert the script into commented sample code. You can easily mistake sample code for real code.
    begin
    if (event_name='WHEN-NEW-FORM-INSTANCE')
    then declare cursor_name integer; rows_processed integer; del char(1) := substr(get_application_property(CURRENT_FORM),1,1);
    begin
    if (del != '/')
    then del := '\';
    end if;
    cursor_name := dbms_sql.open_cursor;
    dbms_sql.parse(cursor_name,
    'begin VERITAS_APPS_LOG( ''F'' , :username ,
    :application , :form , NULL) ; end ;' ,1); dbms_sql.bind_variable(cursor_name,':username' ,
    FND_PROFILE.VALUE('USERNAME')); dbms_sql.bind_variable(cursor_name,':application' ,
    substr(get_application_property(CURRENT_FORM), instr(get_application_property(CURRENT_FORM),del ,
    -1)+1, length(substr( get_application_property(CURRENT_FORM),
    instr(get_application_property(CURRENT_FORM), del,-1)+1))-4));
    dbms_sql.bind_variable(cursor_name,':form' , Name_In('system.current_form'));
    rows_processed := dbms_sql.execute(cursor_name);
    dbms_sql.close_cursor(cursor_name);
    exception
    when others then begin dbms_sql.close_cursor(cursor_name);
    exception
    when others then null;
    end; end; end if;
    exception
    when others then null;
    end;
  8. To regenerate the CUSTOM.pll file from the CUSTOM.PLD file, run the following command using the Oracle account of Oracle Applications (such as APPS):
    1. For Oracle Application version under R12 (exclusive):
      f60gen Module=CUSTOM.pld Module_Type=LIBRARY userid=user/password parse=yes
    2. For Oracle Applications version R12:
      frmcmp_batch Module=CUSTOM Module_Type=LIBRARY userid=user/password parse=yes

  9. To regenerate the CUSTOM.plx file from the CUSTOM.pll file, run the following command using the Oracle account of Oracle Applications (such as APPS):
         f60gen Module=CUSTOM.pll Module_Type=LIBRARY userid=user/password compile_all=yes

    For Oracle Applications version R12, replace f60gen command with frmcmp_batch.

  10. To verify the CUSTOM library change, run the following script:
         <precise_root>/products/oracle/<sid>/schema/pss_verify_oa_customer_pll.sh

To update the customization library on Windows

  1. Find the CUSTOM.pll and CUSTOM.plx files, usually located in your Oracle Applications folder.
  2. Back up the CUSTOM.pll and CUSTOM.plx files.
  3. Verify that no Oracle Applications user is currently logged on to the database or any other database using the CUSTOM.pll and CUSTOM.plx files.

  4. Run the %ORACLE_HOME%\bin\if60gen.exe tool (sometimes called ifbld60.exe).

    For Oracle Applications version R12, replace f60gen command with frmcmp_batch.

  5. In the welcome wizard, select Open an existing form, followed by OK.
  6. Choose custom.pll and click Open.
  7. Under PL/SQL Libraries>Custom>Program Units right-click on Package body and select the PL/SQL Editor.
  8. Find the EVENT procedure, locate the first BEGIN after the commented sample code, and insert the following code, starting with BEGIN.
    Verify that the script was not inserted into commented sample code. You can easily mistake sample code for real code.
    begin
    if (event_name='WHEN-NEW-FORM-INSTANCE')
    then declare cursor_name integer; rows_processed integer; del char(1) := substr(get_application_property(CURRENT_FORM),1,1);
    begin
    if (del != '/')
    then del := '\';
    end if;
    cursor_name := dbms_sql.open_cursor;
    dbms_sql.parse(cursor_name,
    'begin VERITAS_APPS_LOG( ''F'' , :username ,
    :application , :form , NULL) ; end ;' ,1); dbms_sql.bind_variable(cursor_name,':username' ,
    FND_PROFILE.VALUE('USERNAME')); dbms_sql.bind_variable(cursor_name,':application' ,
    substr(get_application_property(CURRENT_FORM), instr(get_application_property(CURRENT_FORM),del ,
    -1)+1, length(substr( get_application_property(CURRENT_FORM),
    instr(get_application_property(CURRENT_FORM), del,-1)+1))-4));
    dbms_sql.bind_variable(cursor_name,':form' , Name_In('system.current_form'));
    rows_processed := dbms_sql.execute(cursor_name);
    dbms_sql.close_cursor(cursor_name);
    exception
    when others then begin dbms_sql.close_cursor(cursor_name);
    exception
    when others then null;
    end; end; end if;
    exception
    when others then null;
    end;
  9. Click File>Save.
  10. Run %ORACLE_HOME%\bin\ifcmp60.exe.
  11. Fill in the following fields:
    1. File. CUSTOM.PLL (that we changed before)
    2. Userid. APPS
    3. Password. APPS password
    4. Database. database name
    5. Module Type. LIBRARY
    6. Module Access. FILE
  12. Click OK.

Manually updating Oracle Applications’ system profile (Oracle Applications 11.5 or higher)

To have an Interpoint for Oracle Applications identify interactive forms in Oracle Applications 11.5 or higher, you must manually update the system profile of Oracle Applications with site-specific initialization code. As a result, Oracle Applications forwards the required information - the user, form, and application name for interactive work and the concurrent program and application name for batch work - to an Interpoint for Oracle Applications.

Before updating Oracle Applications’ system profile, you must verify that an Interpoint for Oracle Applications was installed successfully. Otherwise, updating the profile may halt Oracle Applications.

To avoid interfering with anyone’s work before changing the system profile, you can alternatively change the profile for only one user to first test the outcome.

To verify that an Interpoint for Oracle Applications was installed successfully

  • Run the following script:
    Windows
         \products\oracle\SID\schema\verify_install_oracle_apps.bat
    UNIX
         ./products/oracle/SID/schema/verify install oracle apps.sh

To update Oracle Application’s system profile

  1. Log in to Oracle Applications with a user that has system administrator privileges.
  2. Open the System Administrator application.
  3. Open the Navigator - System Administrator dialog box.
  4. On the Functions tab, select Profile>System; then click Open.
  5. In the Find System Profile Values form, select the Site check box and type the following value in the Profile text box: Init%SQL%Custom
  6. Click Find.
  7. In the System Profile Values form, type the following in the Site text box: begin veritas_custom_profile; end;
  8. Close the System Profile Values form; then open any form.
    If the form opens cleanly, you can start working with the Interpoint for Oracle Applications.
    If an issue occurs, verify again that the Interpoint for Oracle Applications was installed successfully. If it was installed successfully, most likely the issue was caused by erroneous text in the system profile. If the issue persists, contact Precise Customer Support.

To update the system profile for one user only

  1. Log in to Oracle Applications with a user that has system administrator privileges.
  2. Open the System Administrator application.
  3. Select Profile>System; then click Open.
  4. In the Find System Profile Values form, select the Site and User check boxes.
  5. To the right of the User check box, enter the name of the user whose profile you want to change.
  6. Type the following value in the Profile text box: Init%SQL%Custom
  7. Click Find.
  8. In the System Profile Values form, type the following in the User text box: begin veritas_custom_profile; end;
  9. Close the System Profile Values form and log in to Oracle Applications with the user selected in Step 8.
  10. Open any form.
    If the form opens cleanly, you can update the Oracle Applications’ system profile. 
    If an issue occurs, verify again that the Interpoint for Oracle Applications was installed successfully. If it was installed successfully, most likely the issue was caused by erroneous text in the system profile. If the issue persists, contact Precise Customer Support.

Correcting problematic profiles

When the Interpoint for Oracle Applications does not correlate some of its information with the Collector agent, the following scenario may be applicable:

The Interpoint for Oracle Applications collects some of its data from the Oracle Applications using the initialization of every form that is declared in the System Profile. The System Profile contains 4 profile levels, one for each level. Listed from the weakest to the strongest are: Site, Application, Responsibility, User. The Interpoints’ code is on the weakest link here, meaning, Site level. When you customize one or more profiles at a stronger level, the code that resides at Site level is hidden.

In this query we expect to have Interpoints’ code (begin veritas_custom_profile; end;) in all entries, otherwise there is no correlation.

To find problematic profiles

  1. Use the following query:
    select a.profile_option_value, DECODE(a.level_id, 10001, 'Site', 10002, 'Application', 10003, 'Responsibility', 10004, 'User', a.level_id)
    as "System Profile Level", a.level_value, to_char (a.last_update_date, 'YYYY-MON-DD') LAST_UPDATE_DATE
    from FND_PROFILE_OPTION_VALUES a, FND_PROFILE_OPTIONS b, fnd_profile_options_tl c
    where
    profile_option_id = b.profile_option_id and profile_option_name = c.profile_option_name and
    user_profile_option_name = 'Initialization SQL Statement - Custom' order by a.LEVEL_ID;
  2. Add the Interpoints’ code within the profile (By changing the relevant profile in Oracle Applications). I.e. verify that the profile_option_value looks like:
         begin veritas_custom_profile;
         <customer code goes here (or empty)>
         end;
    This query shows all relevant profiles affecting the code.

Correcting typing errors in the system profile

Typing errors in the system profile may prevent Oracle Applications forms from opening, including the system profile form. To validate that there is no other definition that overrides your profile definition and to correct any typing errors, you need to log in to the Oracle database externally using SQL*Plus.

To correct typing errors

  1. Log in to your Oracle database with the APPLSYS account.
  2. Run the following command to view erroneous text:
         Select * from FND_PROFILE_OPTION_VALUES where PROFILE_OPTION_VALUE like '%veritas%';
    You may search for any expression by replacing '%veritas%' in the where clause with the string you are looking for. For example, to search for the profiles that were entered on July 13, 1975, execute the following command:
         Select * from FND_PROFILE_OPTION_VALUES where last_update_date=to_date ('13-JUL-75', 'DD-MON-YY');
  3. Run the following command to correct the errors:
         update FND_PROFILE_OPTION_VALUES set PROFILE_OPTION_VALUE ='begin veritas_custom_profile; end;'
         where PROFILE_OPTION_VALUE like '%veritas%';
    You may update any expression by replacing '%veritas%' in the where clause with the string you are looking for. For example, to update all profiles that were entered on July 13, 1975, execute the following command:
         update FND_PROFILE_OPTION_VALUES set PROFILE_OPTION_VALUE='begin veritas_custom_profile;end;'
         where last_update_date=to_date ('13-JUL-75', DD-MON-YY');
  4. Commit the changes.


IDERA |  Products | Purchase | Support |  Community |  Resources |  About Us  | Legal