Enabling Custom DFF in Custom Oracle Forms

We can create a custom Descriptive Flexfield on custom tables and use it in custom form. Here we 

have illustrated the steps required to create the DFF and register it in Oracle Apps.

Step1:  Create the custom table in Custom Schema

create table xx_order_details

(order_id number,

order_number number,

order_date date,

customer_id number,

attribute_category VARCHAR2(150),

attribute1 VARCHAR2(240),

attribute2 VARCHAR2(240),

attribute3 VARCHAR2(240),

attribute4 VARCHAR2(240),

attribute5 VARCHAR2(240)

)

We will use attribute1 to be registered as descriptive flexfields.

Step2 :   Grant Permission to Apps User

Grant all on xx_order_details to APPS.

Step 3: Create synonym in APPS

CREATE SYNONYM xx_order_details FOR <CUSTOM SCHEMA>.xx_order_details

Step 4: Register table with Apps using AD_DD Pakage

BEGIN

AD_DD.REGISTER_TABLE('FND','XX_ORDER_DETAILS','T');

END;

Step5 : Register Column with Apps Using AD_DD Package

BEGIN

AD_DD.REGISTER_COLUMN('FND','XX_ORDER_DETAILS','ORDER_ID',1,'NUMBER',10,'N','N');

AD_DD.REGISTER_COLUMN('FND','XX_ORDER_DETAILS','ORDER_NUMBER',2,'NUMBER',10,'N','N');

AD_DD.REGISTER_COLUMN('FND','XX_ORDER_DETAILS','ORDER_DATE',3,'DATE',9,'N','N');

AD_DD.REGISTER_COLUMN('FND','XX_ORDER_DETAILS','CUSTOMER_ID',4,'NUMBER',10,'N','N');

AD_DD.REGISTER_COLUMN('FND','XX_ORDER_DETAILS','ATTRIBUTE_CATEGORY',5,'VARCHAR2',

150,'N','N');

AD_DD.REGISTER_COLUMN('FND','XX_ORDER_DETAILS','ATTRIBUTE1',6,'VARCHAR2',240,'N','N');

AD_DD.REGISTER_COLUMN('FND','XX_ORDER_DETAILS','ATTRIBUTE2',7,'VARCHAR2',240,'N','N');

AD_DD.REGISTER_COLUMN('FND','XX_ORDER_DETAILS','ATTRIBUTE3',8,'VARCHAR2',240,'N','N');

AD_DD.REGISTER_COLUMN('FND','XX_ORDER_DETAILS','ATTRIBUTE4',9,'VARCHAR2',240,'N','N');

AD_DD.REGISTER_COLUMN('FND','XX_ORDER_DETAILS','ATTRIBUTE5',10,'VARCHAR2',240,'N','N');

END;

Step6 : Register DFF with APPS

Navigation Path : Application DeveloperàFlexfieldàDescriptiveàRegister

Note: Title should be Unique as it is required to Query the Flexfield for

creation or updation of Segments

Click on Columns button and check only ATTRIBUTE1 as shown below

To Create Segments , 

Navigation Path : Application DeveloperàFlexfieldàDescriptiveàSegment

Query the Flexfield by Pressing F11 and Copy the Title XX_ORDER_DETAILS in

Title field

Unfreeze Flexfield Definition by Unchecking the checkbox Freeze

Flexfield Definition

Click on Segments and we should define the window prompt name and the column 

assigned for DFF.

Finally Freeze the flexfield definition

Now Press ‘OK’

Now press ‘OK’

Step 7 : Create a Custom Form which will contain DFF

Download Template form from $AU_TOP/forms/US

Create a Window, Canvas

Create Datablock based on Custom Table XX_ORDER_DETAILS

Properties for ATTRIBUTE_CATEGORY

--------------------------------------------------------

Subclass Information : TEXT_ITEM_DESC_FLEX

LOV               : ENABLE_LIST_LAMP

Validate from List   : NO

Change Properties Visible to NO for ATTRIBUTE1

Package Specification under Program Units

PACKAGE XX_DFF_ORDER_PKG IS

   PROCEDURE XX_DFF_ORD_PROC (EVENT VARCHAR2); 

END;

PACKAGE BODY  XX_DFF_ORDER_PKG  IS

 PROCEDURE XX_DFF_ORD_PROC (EVENT VARCHAR2)

IS

 BEGIN

 IF EVENT = 'WHEN-NEW-FORM-INSTANCE' THEN

 FND_DESCR_FLEX.DEFINE(

 BLOCK => 'XX_ORDER_DETAILS',

 FIELD => 'ATTRIBUTE_CATEGORY',

 APPL_SHORT_NAME => 'FND',

 DESC_FLEX_NAME => 'XX_ORDER_DETAILS' );

 END IF;

 END ;

END;

Change the below triggers 

1. When-New-Form-Instance :

XX_DFF_ORDER_PKG.XX_DFF_ORD_PROC('WHEN-NEW-FORM-INSTANCE');

2. When-New-Item-Instance:

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

Step 8 : Register the form and add function to Menu

Step 9 : Run the form

Record can be queried from table