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 21/2 page explanation in the chapter "Flexfields" of the Oracle Application Developer's Guide.
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.
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.
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.
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.
procedure initialize is
cursor get_cao is
where id_flex_structure_code = 'ACCOUNTING_FLEXFIELD'
and id_flex_code = 'GL#';
fetch get_cao into v_cao;
if get_cao%notfound then
v_cao := '101';
TITLE=>'____ Your nice user friendly title here _____',
updateable => '',
where_clause => 'summary_flag != ''Y''',
Listing A: Dynamic Flexfield definition
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.
if ( :system.mode = 'NORMAL' ) then
fnd_flex.event( 'WHEN-VALIDATE-ITEM' );
--Loads the flexfields (in our case, it populates
--the concatenated field on execute query).
--If you don't do this, whatever query criteria you may enter in
-- the concatenated flex field, it is not taken into account.
Listing B: Event programming for the Flexfields.
Et voilà! With only these 5 steps you can achieve the result illustrated in figure 1. What we have shown only scratches the surface of what can be done with Oracle Application flexfields but it provides an easy-to-follow introduction.
Hervé Deschamps is a Technical Manager with Oracle Corporation. Over the years he has developed a number of applications using Oracle Development Tools and others. He has an extensive experience of all phases of the development life cycle. He is also known in the technical community for his article publications centered around best custom development practices and for his user group presentations. You can reach him by e-mail at email@example.com. He also maintains a web site full of articles, scripts, tips and techniques at http://www.iherve.com.