, , ,

How to use CUSTOM.pll in Oracle Apps

Requirement: change the Executable field into uppercase within the Concurrent Program Executable screen
Follow these steps to make the Executable field uppercase using CUSTOM.pll:
1.    Enable show custom events: Log in to the Application Developer responsibility and enable Show Custom Events. Check the blog post for enabling custom events:

Custom events can be enabled through the below navigation:

Once you enable show custom events, oracle will display the name of each and every event that can be trapped using CUSTOM.pll. Using this technique, you can identify the custom event that you need to trap to achieve the desired result.

 Custom Events


2.    Gather information: A message box will appear when this form is opened, indicating that the form name is FNDCPMPE, the block name is FND_EXECUTABLES, the field name is USER_EXECUTABLE_NAME, and the event name is WHEN-NEW-FORM-INSTANCE. Note down the names of these components as these will be required in programming CUSTOM.pll. To double-check the name of field, navigate to the Executable field and use the Help/Diagnostics/Examine menu to see the name.
3.    Write code in CUSTOM.pll: Open Oracle Forms Builder, highlight PL/SQL Libraries, and navigate from the menu to File | Open. Open the CUSTOM.pll file and expand the nodes. Within the node Attached Libraries, you will see FNDSQF and APPCORE2. Write your logic in a procedure called event, using the following sample syntax:
procedure event(event_name varchar2) is
form_name varchar2(30) := name_in(‘system.current_form’);
block_name varchar2(30) := name_in(‘system.cursor_block’);
if form_name=’FNDCPMPE’ then
if event_name=’WHEN-NEW-FORM-INSTANCE’ then
end if;
end if;
end event;
OR you can even write your extensions in a package.event(event_name) and call this procedure in package body of custom.event.
After making the programming changes to CUSTOM.pll, scroll down to the bottom of the package body Custom and make these changes to the version history of CUSTOM.pll:
This code should appear as a sublisting under #3fdrcsid(‘$Header: CUSTOM.pld 120.1 2009/03/03 18:43:00 appldev ship $’);
4.    Transfer CUSTOM.pll to $AU_TOP/resource: Log in to the forms server and change the directory to $AU_TOP/resource. Ensure you have a backup of CUSTOM.pll either in the source control system or on the file system. Next, transfer CUSTOM.pll from your desktop to $AU_TOP/resource.
5.    Generate CUSTOM.pll: Use the command frmcmp_batch, replacing the apps password with the relevant value on your system. After running this command, CUSTOM.plx will be created in $AU_TOP/resource:
cd $AU_TOP/resource
##For R12 use frmcmp_batch
frmcmp_batch module=CUSTOM.pll userid=apps/appspassword output_
file=./CUSTOM.plx compile_all=special module_type=LIBRARY
##For 11i Use f60gen command as shown below
f60gen module=CUSTOM.pll userid=apps/appspassword output_file=./
CUSTOM.plx module_type=LIBRARY
6.    Test your changes: Log out and log back in to the Concurrent Program Executable screen. You will notice that it is no longer possible to enter the concurrent program executable name using lowercase letters.

Concepts: Using the Custom Library

Customizing Oracle E-Business Suite with the CUSTOM Library

The CUSTOM library allows extension of Oracle E-Business Suite without modification of Oracle E-Business Suite code. You can use the CUSTOM library for customizations such as Zoom (such as moving to another form and querying up specific records), enforcing business rules (for example, vendor name must be in uppercase letters), and disabling fields that do not apply for your site.
You write code in the CUSTOM library, within the procedure shells that are provided. All logic must branch based on the form and block for which you want it to run. Oracle E-Business Suite sends events to the CUSTOM library. Your custom code can take effect based on these events.
Important: The CUSTOM library is provided for the exclusive use of Oracle E-Business Suite customers. The Oracle E-Business Suite products do not supply any predefined logic in the CUSTOM library other than the procedure shells described here.

Writing Code for the CUSTOM Library

The CUSTOM library is an Oracle Forms Developer PL/SQL library. It allows you to take full advantage of all the capabilities of Oracle Forms Developer, and integrate your code directly with Oracle E-Business Suite without making changes to Oracle E-Business Suite code.
The as-shipped CUSTOM library is located in the AU_TOP/resource directory (or platform equivalent). Place the CUSTOM library you modify in the AU_TOP/resource directory in order for your code to take effect.
After you write code in the CUSTOM procedures, compile and generate the library using Oracle Forms. Then place this library into $AU_TOP/resource directory (or platform equivalent). Subsequent invocations of Oracle E-Business Suite will then run this new code.

Warning: If there is a .plx (compiled code only) for a library, Oracle Forms Developer always uses the .plx over the .pll. Therefore, either delete the .plx file (so your code runs directly from the .pll file) or create your own .plx file using the Oracle Forms compiler. Using the .plx file will provide better preformance than using the .pll file. Depending on your operating system, a .plx may not be created when you compile and save using the Oracle Forms Developer. Form Builder. In this case, you must generate the library using the Oracle Forms Developer compiler from the command line (using the parameter COMPILE_ALL set to Yes). 

Be aware of the open form environment in which Oracle E-Business Suite operates. Also, each running form has its own database connection.

The following considerations and restrictions apply to the CUSTOM library and any libraries you attach to CUSTOM:

  • You cannot use any SQL in the library. However, you can use a record group to issue SELECT statements, and you can use calls to stored procedures for any other DML operations such as updates, inserts, or deletes.
  • Oracle Forms global variables in your code are visible to all running forms.

Altering Oracle E-Business Suite Code

Frequently you need to know the names of blocks and items within Oracle E-Business Suite forms for your CUSTOM logic. You should use the Examine feature available on the Help->Diagnostics menu while running the form of interest; it will give you easy access to all object names. You should not open Oracle E-Business Suite forms in the Oracle Forms Developer to learn this information.
You should exercise caution when changing any properties or values of items in the form from which CUSTOM logic is invoked.

The CUSTOM library is intended to be a mechanism to augment Oracle code with your own. Using the CUSTOM library to alter Oracle code at runtime may bypass important validation logic and may jeopardize the integrity of your data. You should thoroughly test all logic you add to the CUSTOM library before using it in a production environment. 

, ,

Solved: Internet explorer has closed this webpage to help protect your computer

Most of users face issue running Oracle forms on Microsoft Internet Explorer 8 (IE8) or later versions which causes the page to redirect to following url:


Internet explorer has closed this webpage to help protect your computer
A malfunctioning or malicious add-on has caused Internet Explorer to close this webpage.

We were unable to return you to the page you were viewing. Internet Explorer has stopped trying to restore this website. It appears that the website continues to have a problem.

1) Take backup of jvm.dll located at c:Program FilesOracleJInitiator1.3.1.xbinhotspot directory. (Depending on JInitiator version you have)
2) Copy jvm.dll from C:Program FilesJavajre1.6.0_07binclient (or whichever highest version of jre you have) to c:Program FilesOracleJInitiator 1.3.1.xbinhotspot
3) Clear browser cache and also Oracle Jar cache
For XP:
(Remove all files from c:documents and settings<username>Oracle Jar cache)

For Vista/7/8
(Remove all files from c:users<username>Oracle Jar cache)
4) Close all the browsers and restart.

In case if you are unable to find the jvm.dll in that location or if the above solution didn’t work for you, you can download the working jvm.dll from here.

I hope your issue should have been resolved now !

, , ,

How to Use Flexfields in Custom forms

There are several complicated things you may need to do with flexfields in your forms but one of them is simple and very common: create a foreign key on the accounting flexfield. It is easy to get lost in the Oracle Application Developer’s Guide because it tries to cover comprehensively what can be done with flexfields. But there is nothing to show you the basics step-by-step, which is what we will attempt in this article.
The end result of what we show is illustrated in Figure 1: one field that brings up the standard flexfield popup when the user asks for a list of values.
Figure 1: The end result of this step-by-step article.
A basic conceptual understanding of what flexfields are is necessary in order to understand what follows. There is a very good 2-3 page explanation in the chapter “Flexfields” of the Oracle Application Developer’s Guide.

Step 1: Create a FK Column

First you need to define a column in the base table maintained by your form. This column will be the foreign key on the unique ID of the combination table, in our case: GL_CODE_COMBINATIONS. So you need a number(38) mandatory column in your table.

Step 2: Create a Flexfield hidden item

In your form you need to create a hidden item for the FK column. This field needs to be hidden, that is set to canvas null. It should use the TEXT_ITEM property class which comes from the Oracle Application template. Set the query length to 2000 just to be on the safe side. Figure 2 shows an example of such hidden ID.
Figure 2: Hidden field to hold the Flexfield reference. 
Figure 2: Hidden field to hold the Flexfield reference.

Step 3: Create a Flexfield display item

Now we want to create a non-database text item that will display the concatenated values of the segments of the accounting field. This item should use the TEXT_ITEM property class and be assigned to the appropriate canvas where you want users to see it. You then need to assign to it the dummy LOV ‘ENABLE_LIST_LAMP’ which comes from the Oracle Application template. Make sure that property ‘Validate from list’ is set to No. This ensures that the List lamp works properly for your flexfield. Figure 3 shows an example of such displayed field. We only show the most relevant item properties.
Figure 3: Displayed field to show the concatenated values of the Flexfield. 
Figure 3: Displayed field to show the concatenated values of the Flexfield.

Step 4: Create the Flexfield definition

Then there is a little bit of PL/SQL to do in order to define your flexfield. This is done with the WHEN-NEW-FORM-INSTANCE trigger. It is always a good practice to do the actual work in a procedure or package as is illustrated in listing A. There are several reasons for this but this is out of the scope of this article. Note: the code in listing A is an abbreviated version of the real code: we have left out the comments and the error handling in order to keep this article as short as possible.
Listing A: Dynamic Flexfield definition

procedure initialize is     cursor get_cao is        select  to_char(id_flex_num)        from    fnd_id_flex_structures        where   id_flex_structure_code = ‘ACCOUNTING_FLEXFIELD’                and id_flex_code = ‘GL#’;     v_cao    varchar2(1000);            begin    open get_cao;    fetch get_cao into v_cao;    if get_cao%notfound then        v_cao := ‘101’;    end if;    close get_cao;       app_standard.event(‘WHEN-NEW-FORM-INSTANCE’);    fnd_key_flex.define(      BLOCK=>’COMBO’,      FIELD=>’ACCOUNT_FLEXFIELD’,      APPL_SHORT_NAME=>’SQLGL’,      CODE=>’GL#’,      NUM=>v_cao,      ID=>’CODE_COMBINATION_ID’,      DESCRIPTION=>”,      TITLE=>’____ Your nice user friendly title here _____’,      VALIDATE=>’FULL’,      QBE_IN=>’Y’,      DERIVE_ALWAYS=>’Y’,      updateable => ”,      VRULE=>’\nSUMMARY_FLAG\nI\nAPPL=SQLGL;NAME=GL_NO_PARENT_SEGMENT_ALLOWED\nN’,      where_clause => ‘summary_flag != ”Y”’,      QUERY_SECURITY =>’Y’);  end initialize;

Step 5: Code several event triggers

Then, all that is left to do is to program a few events:
The code is shown in Listing B.
We always try to put this code in the form level triggers as it is more convenient and consistent than doing it at block or item level, especially when you have more than one foreign key flexfield in the form. Only in the case of very large and complicated forms would we do otherwise.
If you are updating someone else’s form, you may need to check that no block or item triggers are overriding your form level triggers. Check also the execution style of your form level triggers. Whether your trigger should fire in mode Before, After or Override will depend on your context.
Listing B: Event programming for the Flexfields.

WHEN-VALIDATE-ITEM    if ( :system.mode = ‘NORMAL’ ) then       fnd_flex.event( ‘WHEN-VALIDATE-ITEM’ );    end if;WHEN-NEW-ITEM-INSTANCE    app_standard.event(‘WHEN-NEW-ITEM-INSTANCE’);    fnd_flex.event(‘WHEN-NEW-ITEM-INSTANCE’ );POST-QUERY–Loads the flexfields (in our case, it populates–the concatenated field on execute query).    FND_FLEX.EVENT(‘POST-QUERY’);PRE-QUERY–If you don’t do this, whatever query criteria you may enter in— the concatenated flex field, it is not taken into account.    FND_FLEX.EVENT(‘PRE-QUERY’ );KEY-LISTVAL    APP_STANDARD.EVENT(‘KEY-LISTVAL’);    FND_FLEX.EVENT(‘KEY-LISTVAL’ );