Oracle Blogs

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

descriptive flexfields in oracle apps r12 customization
Written by admin

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

Descriptive Flexfield

Before  reading this informative article if you forget to take your coffee please have one as it will  increase your concentration. If you are a coffee lover then you can view very  informative blog post about coffee beans,

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 NULLPRIMARY 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 ANDconstraint_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

The query for the custom table, XX_USER_TABLE

descriptive flexfield in oracle fusion

Step 3: Register the DFF in Oracle Apps

Responsibility: Application Developer

Navigation: Flexfield > Descriptive > Register

kff in oracle apps

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)

flexfields in oracle fusion

Click on Columns

flexfields in oracle fusion

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

flexfield qualifiers in oracle apps r12

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

how to create key flexfield in oracle apps

Query for the Title = Blacklist

oracle eff

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

attribute_category in oracle apps

Select a column and a value set.

how to add dff in oracle forms

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

global attributes in oracle apps

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

flexfields in oracle apps basic concepts

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.

accounting flexfield in oracle apps r12

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

For more detail, you can review the form customization article.

Notice the DFF text item on the canvas.

key flexfields in oracle apps r12 inventory

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;

oracle fusion eff

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

how to enable dff in oracle apps

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

how to make dff segment mandatory

The code in the procedure is,

how to create value set in oracle fusion

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

difference between dff and eff

Description: Supplier Blacklist form

Register the form function

Navigation: Application > Function

descriptive flexfield in oracle fusion hcm

Description Tab

Enter the following:

Function: XX_SUPPLIER_BLACKLIST

User Function Name: Supplier Blacklist Func

Description: Supplier Blacklist function

Properties Tab

dff personalization in oracle apps r12

Type: Form

Form Tab

dff personalization

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).

flexfield in oracle

Note the Menu name. It is AP_NAVIGATE_GUI12.

Navigate to Application > Menu.

Query for User Menu Name = AP_NAVIGATE_GUI12.

dff segment

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

Enter,

Seq: 55

Prompt: Blacklist

Function: Supplier Blacklist Func

dff in oracle apps

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).

flexfields in oracle apps

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.

flexfields

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.
  1. 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
  1. 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
  1. Write an item level trigger WHEN-NEW-ITEM-INSTANCE on BTL_KFF as

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

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

FND_FLEX.EVENT(‘KEY-EDIT’);

  1. 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.

Please do not forget to share knowledge, share this on your social network. Hope you subscribe for email news letter as well, thanks.

Any questions? or Suggestion leave a comment below.

About the author

admin

11 Comments

  • After reading your post, you have a great website with interesting content. But I think you can improve your current google ranks by using SEO website traffic net. My friend uses it and it works great. Just google it, it’s very nice tool to bring you a lot of new readers on a daily basis. Keep up the quality work!

  • Excellent items from you, man. I’ve take into accout your stuff previous to and you are just extremely wonderful. I actually like what you have obtained here, really like what you are saying and the way wherein you assert it. You are making it enjoyable and you continue to take care of to stay it smart. I cant wait to learn much more from you. That is actually a terrific web site.

  • Aw, this was a really nice post. In concept I want to put in writing like this moreover – taking time and precise effort to make an excellent article… however what can I say… I procrastinate alot and not at all seem to get something done.

  • There are some attention-grabbing points in time on this article but I don’t know if I see all of them center to heart. There may be some validity however I’ll take maintain opinion till I look into it further. Good article , thanks and we wish more! Added to FeedBurner as effectively

  • I wanted to thank you for this great read!! I definitely enjoying every little bit of it I have you bookmarked to check out new stuff you post…

Leave a Reply

%d bloggers like this: