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