Using Oracle Applications Flexfields in your custom forms:

Using Oracle Applications Flexfields in your custom forms: step-by-step.

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^1/2 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.

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.

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.

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;

Listing A: Dynamic Flexfield definition

Step 5: Code several event triggers

Then, all that is left to do is to program a few events:

• WHEN-VALIDATE-ITEM
• WHEN-NEW-ITEM-INSTANCE
• POST-QUERY
• PRE-QUERY
• KEY-LISTVAL

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.

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

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.