How to use Flexfield on customized forms

How to add a Descriptive Flexfield (DFF) in a custom Oracle Apps form

Step 1: Create the custom table

We have created a custom table with the following script,

CREATE TABLE xx_supplier_blacklist ( blacklist_id NUMBER NOT NULL PRIMARY KEY,

                            supp_number VARCHAR2(30),

                            supp_name VARCHAR2(240),

                            address   VARCHAR2(1000),

                            LOCATION VARCHAR2(2),

                            supp_lob      VARCHAR2(240),

                            blklist_flag  VARCHAR2(3),

                            reason_blklist  VARCHAR2(240),

                            reason_details VARCHAR2(1000),

                            date_blklist   DATE,

                            date_remove  DATE,

                            reason_blk_remove VARCHAR2(240),

                            detail_reason_remove VARCHAR2(1000),

                            linkage VARCHAR2(3) ,

                            attribute_category  VARCHAR2(150),

                            attribute1  VARCHAR2(240),

                            attribute2  VARCHAR2(240),

                            attribute3  VARCHAR2(240),

                            attribute4  VARCHAR2(240),

                            attribute5  VARCHAR2(240)

                            )

Step 2: Register the custom table

We need to register the custom table in Oracle since we need to add the attributes, attribute1..attribute5 as DFF.

Execute the table registration API.

Declare

v_appl_short_name   VARCHAR2 (40) := 'XXCUST';

   v_tab_name          VARCHAR2 (32) := 'XX_USER_TABLE'; -- Change the table name if you require

   v_tab_type          VARCHAR2 (50) := 'T';

   v_next_extent       NUMBER        := 512;

   v_pct_free          NUMBER;

   v_pct_used          NUMBER;

BEGIN

   -- Unregister the custom table if it exists

   ad_dd.delete_table (p_appl_short_name             => 'XXCUST', p_tab_name => v_tab_name);

   -- Register the custom table

   FOR tab_details IN (SELECT table_name, tablespace_name, pct_free, pct_used, ini_trans, max_trans, initial_extent, next_extent

                         FROM dba_tables

                        WHERE table_name = v_tab_name)

   LOOP

      ad_dd.register_table (p_appl_short_name             => v_appl_short_name,

                            p_tab_name                    => tab_details.table_name,

                            p_tab_type                    => v_tab_type,

                            p_next_extent                 => NVL (tab_details.next_extent, 512),

                            p_pct_free                    => NVL (tab_details.pct_free, 10),

                            p_pct_used                    => NVL (tab_details.pct_used, 70)

                           );

   END LOOP;

   -- Register the columns of custom table

   FOR all_tab_cols IN (SELECT column_name, column_id, data_type, data_length, nullable

                          FROM all_tab_columns

                         WHERE table_name = v_tab_name)

   LOOP

      ad_dd.register_column (p_appl_short_name             => v_appl_short_name,

                             p_tab_name                    => v_tab_name,

                             p_col_name                    => all_tab_cols.column_name,

                             p_col_seq                     => all_tab_cols.column_id,

                             p_col_type                    => all_tab_cols.data_type,

                             p_col_width                   => all_tab_cols.data_length,

                             p_nullable                    => all_tab_cols.nullable,

                             p_translate                   => 'N',

                             p_precision                   => NULL,

                             p_scale                       => NULL

                            );

   END LOOP;

   FOR all_keys IN (SELECT constraint_name, table_name, constraint_type

                      FROM all_constraints

                     WHERE constraint_type = 'P' AND table_name = v_tab_name)

   LOOP

      ad_dd.register_primary_key (p_appl_short_name             => v_appl_short_name,

                                  p_key_name                    => all_keys.constraint_name,

                                  p_tab_name                    => all_keys.table_name,

                                  p_description                 => 'Register primary key',

                                  p_key_type                    => 'S',

                                  p_audit_flag                  => 'N',

                                  p_enabled_flag                => 'Y'

                                 );

      FOR all_columns IN (SELECT column_name, POSITION

                            FROM dba_cons_columns

                           WHERE table_name = all_keys.table_name AND constraint_name = all_keys.constraint_name)

      LOOP

         ad_dd.register_primary_key_column (p_appl_short_name             => v_appl_short_name,

                                            p_key_name                    => all_keys.constraint_name,

                                            p_tab_name                    => all_keys.table_name,

                                            p_col_name                    => all_columns.column_name,

                                            p_col_sequence                => all_columns.POSITION

                                           );

      END LOOP;

   END LOOP;

   COMMIT;

END;

Once the table registration API completes successfully, log in to Oracle Apps.

Responsibility: Application Developer

Navigation: Application > Database > Table

Query for the custom table, XX_USER_TABLE

Step 3: Register the DFF in Oracle Apps

Responsibility: Application Developer

Navigation: Flexfield > Descriptive > Register

Enter the values as,

Application: Custom Applications

Title: Blacklist

Table Application: Custom Applications

Name: BLACKLIST_FLEX

Description: Blacklist context flexfield

Table Name: XX_SUPPLIER_BLACKLIST

Context Prompt: Context Value

DFV View Name: (This value is left blank. If we set up a database view later on we shall add its name here)

Click on Columns

The list of columns are displayed here. If the box named, Enabled, is checked then the column is set to be part of DFF. Notice that Oracle has checked the ATTRIBUTE columns as DFF by default. If you want to add any column, you may check the Enabled box.

Note:
The column ATTRIBUTE_CATEGORY is not displayed in this list of columns as is already declared as the DFF Structure column in the previous screen.

Close this form and go back to Descriptive Flexfields form.

Click on Reference Fields

The list of reference fields are displayed here. We do not have any at the moment. Save and close the DFF form.

Configure the DFF segments

Responsibility: Application Developer

Navigation: Flexfield > Descriptive > Segments

Query for the Title = Blacklist

Click on Segments

Create a new Segment

Number: 10

Name: Parent Supplier

Window Prompt: Parent Supplier

Click on Column to pull up the LOV with the columns

Select a column and a value set.

Then Freeze the flexfield by checking the box on the main DFF Segments form.

Save the form so that Oracle compiles the DFF. Once the compilation is over the DFF is ready to be used.

Step 4: Configure Forms Builder

Forms builder has to be configured for developing Oracle Apps forms. 

Step 5: Develop the custom form for Oracle Apps

We have created a new block named, XX_SUPPLIER_BLACKLIST. Add a new field in the block for the DFF.

We have named the field, DFF.

The important properties to be set are,

  
Subclass Information: TEXT_ITEM_DESC_FLEX

Required: No

Canvas: <Set the name of the Canvas manually since the item was created manually>

Database Item: No

Insert Allowed: Yes

Update Allowed: Yes

List of Values: ENABLE_LIST_LAMP

Validate From List: No

Notice the DFF text item on the canvas.

Now create a procedure in the form as given below,

PROCEDURE xx_supplier_blacklist_dff (event VARCHAR2)

IS

BEGIN

   IF (event = 'WHEN-NEW-FORM-INSTANCE')

   THEN

      fnd_descr_flex.define (BLOCK                         => 'XX_SUPPLIER_BLACKLIST', -- Name of the DFF

                             FIELD                         => 'DFF',

                             appl_short_name               => 'CUSTOM',

                             desc_flex_name                => 'BLACKLIST_FLEX'

                            );

   ELSE

      NULL;

   END IF;

END;

Now we need to add the call to this function when the form is initiated. Therefore we need to call the procedure from WHEN-NEW-FORM-INSTANCE. Open the WHEN-NEW-FORM-INSTANCE trigger.

Add the following line to call the inbuilt procedure,

xx_supplier_blacklist_dff ('WHEN-NEW-FORM-INSTANCE');

Finally we need to raise the event to call the DFF when the DFF field is clicked on. This will ensure that the DFF segments will be displayed.

Create a trigger, WHEN-NEW-ITEM-INSTANCE, on the block item named, DFF, and add the following line,

FND_FLEX.EVENT('WHEN-NEW-ITEM-INSTANCE');

Save the form and compile it on the server.

Note:
Oracle apps provides the API named, FND_FLEX, for Descriptive flexfield events. You can to write all the block level triggers to have consistent normal behaviour of the descriptive flexfield.

WHEN-VALIDATE-ITEM: FND_FLEX.EVENT(‘WHEN-VALIDATE-ITEM ‘);

PRE-QUERY: FND_FLEX.EVENT(‘PRE-QUERY’);

POST-QUERY: FND_FLEX.EVENT(‘POST-QUERY’);

WHEN-VALIDATE-ITEM: FND_FLEX.EVENT(‘WHEN-VALIDATE-RECORD’);

WHEN-NEW-ITEM-INSTANCE: FND_FLEX.EVENT(‘WHEN-NEW-ITEM-INSTANCE’);

PRE-INSERT: FND_FLEX.EVENT(‘PRE-INSERT’);

PRE-UPDATE: FND_FLEX.EVENT(‘PRE-UPDATE’);

You will find a new procedure in the Program Units section

The code in the procedure is,

PROCEDURE xx_supplier_blacklist_dff (event VARCHAR2)

IS

BEGIN

IF (event = ‘WHEN-NEW-FORM-INSTANCE’)

THEN

fnd_descr_flex.define (BLOCK => ‘XX_SUPPLIER_BLACKLIST’, — Name of the DFF

FIELD => ‘DFF’, — Field name

appl_short_name => ‘XXCUST’, — Custom Applications

desc_flex_name => ‘BLACKLIST_FLEX’

);

ELSE

NULL;

END IF;

END;

This procedure has been generated by Oracle for invoking the flex field window and its functionality.

Step 6: Register the form

Now we need to register the form in Oracle Applications

Responsibility: Application Developer

Navigation: Application > Form

Enter the form details

Form: XX_SUPPLIER_BLACKLIST

Application: Custom Applications

User Form Name: Supplier Blacklist form

Description: Supplier Blacklist form

Register the form function

Navigation: Application > Function

Description Tab

Enter the following:

Function: XX_SUPPLIER_BLACKLIST

User Function Name: Supplier Blacklist Func

Description: Supplier Blacklist function

Properties Tab

Type: Form

Form Tab

Form: Supplier Blacklist form

Save and close the form. Now the form is registered.

Step 7: Attach the form function to a menu/responsibility

After the form and the form functions are created we need to attach the form function to a menu that is attached to a responsibility. Once this is done the form will be accessible to us from that responsibility and other responsibilities which use that menu.

We would like to access the form from the responsibility named, IN AP Manager (HO). Let us open the responsibility form.

Responsibility: System Administrator

Navigation: Security > Responsibility > Define

Query for responsibility, IN AP Manager (HO).

Note the Menu name. It is AP_NAVIGATE_GUI12.

Navigate to Application > Menu.

Query for User Menu Name = AP_NAVIGATE_GUI12.

Scroll down to the bottom of the lines and add a line.

Enter,

Seq: 55

Prompt: Blacklist

Function: Supplier Blacklist Func

Description: Supplier Blacklist Function

Save and close the form. You will get a popup message saying that the menu us being recompiled. Now the form function is attached to the menu and will be accessible to us from the responsibility.

Test the form

Log in to Oracle and go to the responsibility, IN AP Manager (HO) as we had attached the form to the menu of this responsibility (Step 7).

Note the function, Blacklist, at the bottom of the navigation menu. Click on this function.

The form opens up. Now click on the DFF field on the bottom right.

Now the DFF form has also opened. Let us enter some values in the form as shown below.

Save the form. Query the data from the table and let us see if the data has been entered properly into the attribute columns. In this case we have setup only ATTRIBUTE1 as the DFF segment (Step 2).

How to use Key Flex Fields (KFF) in forms

These are the steps required to implement KFF in customization.

•  Define KFF fields in your database tables.

The custom table should contain a field named as XXX_ID (where XXX means the entity. For ex. PART_ID, CODE_COMBINATION_ID etc) to capture the selected code combination ID.

• Register the table with Oracle AOL.
• Register the KFF with Oracle AOL

Logon as

Application Developer -> Flex fields -> Key -> Register
Existing KFF can also be used. Ex. Accounting FF

• Create KFF in the custom form

1. Create a form based on the custom table.

2. In the block, create two non-base table text items of data type CHAR (2000). One text item is to store the code combinations (Say BTL_KFF ) and other one is to store description of the code (Say BTL_KFF_DESC. Make this item as non-updateable).

• Add KFF standard built-ins in the form to invoke KFF

The custom table should contain a field named as XXX_ID (where XXX means the entity. For ex. PART_ID, CODE_COMBINATION_ID etc) to capture the selected code combination ID.

this is typical registration of KFF. For Customization in custom development here are the steps:

1. Write a form level trigger WHEN-NEW-FORM-INSTANCE to invoke the KFF as

FND_KEY_FLEX.DEFINE(

block => 'XXBUC_FLEET_PLANNING',

Field => 'ACCOUNT_FLEXFIELD',

Description =>  'DESC',

ID => 'GL_CODE_COMBINATIONS',

Appl_short_name => 'SQLGL',

Code => 'GL#',

Num => '50608',

--VRULE => 'GL_GLOBAL\nDETAIL_POSTING_ALLOWED \nE\nAPPL=''SQLGL'';

VRULE=>'\\nSUMMARY_FLAG\\nI\\nAPPL=SQLGL;NAME=GL_NO_PARENT_SEGMENT_ALLOWED\\nN', where_clause => 'summary_flag != ''Y''', QUERY_SECURITY =>'Y');

In above defination take a note,

·         These arguments specify location

·         All fields must reside in same block

·         ID is for Key Flexfields only

·         These arguments indicate which flexfield is being used

·         Short name is application where flexfield is registered

·         SQLGL is Oracle General Ledger

·         SQLAP is Oracle Payables

·         Code identifies Key Flexfield

·         GL# is Accounting Flexfield

·         Num is Key Flexfield structure number. Default is 101

2. XXX_ID will store the code combination ID for each selection.

3.Invoke Flexfield functionality by calling FND_Flex.Event(event) from:

• PRE-QUERY
• POST-QUERY
• PRE-INSERT
• PRE-UPDATE
• WHEN-VALIDATE-RECORD
• WHEN-NEW-ITEM-INSTANCE
• WHEN-VALIDATE-ITEM

3. Write an item level trigger WHEN-NEW-ITEM-INSTANCE on BTL_KFF as

FND_FLEX.EVENT('WHEN-NEW-ITEM-INSTANCE')

4. Write an item level trigger KEY-EDIT on BTL_KFF as

FND_FLEX.EVENT('KEY-EDIT');

5. Write an item level trigger WHEN-VALIDATE-ITEM on BTL_KFF as

FND_FLEX.EVENT('WHEN-VALIDATE-ITEM');

IF :CUSTOM_BLOCK.XXX_ID = -1 THEN
FND_MESSAGE.SET_STRING('You Have Selected An Undefined Code Combination !');
FND_MESSAGE.SHOW;
RAISE FORM_TRIGGER_FAILURE;
END IF;

These steps makes you flexfield enable in your form.