We can create
a custom Descriptive Flexfield on custom tables and use it in custom forms.
I
have illustrated the steps required to create the DFF and register it in Oracle
Apps.
Step 1: Create the custom table
We 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)
)
We wanted the
columns, attribute1..attribute5 to be registered as descriptive flexfields.
Step 2: Register the table in Oracle Apps
We shall register the custom table using the
standard API, AD_DD.
DECLARE
v_appl_short_name VARCHAR2 (40) := 'XXMS';
v_tab_name VARCHAR2 (32) := 'XX_SUPPLIER_BLACKLIST'; -- 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 => 'XXMS', 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;
Front end Screen:
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)
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.
The list of
reference fields are displayed here. We do not have any at the moment.
Save and
close the DFF form.
Step 4: 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.