Making a DFF Segment Read Only

Many times We are faced with a requirement of making one or more segments of a DFF as Read Only.

This can be achieved by configuring a special value set which when attached to a segment of the value set renders it Read Only

Create a Value set as below :








Attached the Value Set to the DFF Segment

Check the DFF in Front End

DFF CUSTOMIZATION STEPS

DFF CUSTOMIZATION: 

1)Open the form where DFF is Implemented.

 and Query the Data

2)find the DFF name from Help Menu 

              =>Diagnastcs =>Examine
    Select  $DESCRIPTIVE_FLEXFIELD$ in the Block field
    select  DFF name in the "Field"
    we will get the DFF Title in the Value field.
 Copy the DFF title 

3)goto Application Developer Responsibility.
 flexfield=>Descriptive=>register

4)Query the records by using the  DFF Title.
 Select columns button and find wether Attribute
  columns are enabled or disabled.

5)Goto Segments form and Query the records 

   by using Title.

6)Uncheck the Check box called 

   "freeze Flexfield Defination"

7)Select Segents button and enter

   the new fields and save transactions.

8)Check the check box called

 "freeze Flexfield Defination" and click the 

  Compile Button.

9)System will change the DFF structure and creates 
a descriptive flexfield View.

How to delete a DFF field in oracle apps

--*******************************************
--* Delete a descriptive flexfield
--*******************************************
SET ECHO OFF
SET FEEDBACK OFF
SET SERVEROUTPUT ON SIZE 1000000

/* Formatted on 11/14/2016 2:08:03 PM (QP5 v5.114.809.3010) */
DECLARE
   l_application_id               NUMBER := 0;
   l_descriptive_flexfield_name   VARCHAR2 (100) := 'FND_COMMON_LOOKUPS';
   l_descriptive_flex_context_cod VARCHAR2 (100)
         := 'XFND_CLWW_PURGE_FOLDER' ;
BEGIN
                #####################################################
 --FND_DESCRIPTIVE_FLEXS_PKG --this package is for DFF
   --FND_DESCR_FLEX_CONTEXTS_PKG --this package is for DFF Context
   --FND_DESCR_FLEX_COL_USAGE_PKG --this package is for DFF Column useage
   --When creating a new DFF Context, it will check the DFF Column usage if the context is already used.
   --so when deleting a DFF Context, both the context and column usage should be deleted.
                   #####################################################         
   FOR c
   IN (SELECT   application_column_name
         FROM   fnd_descr_flex_column_usages
        WHERE   application_id = l_application_id
                AND descriptive_flexfield_name = l_descriptive_flexfield_name
                AND descriptive_flex_context_code =
                      l_descriptive_flex_context_cod)
   LOOP
      fnd_descr_flex_col_usage_pkg.delete_row (
         x_application_id                 => l_application_id,
         x_descriptive_flexfield_name     => l_descriptive_flexfield_name,
         x_descriptive_flex_context_cod   => l_descriptive_flex_context_cod,
         x_application_column_name        => c.application_column_name
      );
   END LOOP;

   fnd_descr_flex_contexts_pkg.delete_row (
      x_application_id                 => l_application_id,
      x_descriptive_flexfield_name     => l_descriptive_flexfield_name,
      x_descriptive_flex_context_cod   => l_descriptive_flex_context_cod
   );
--commit;
END;

Query to find DFF

Let's say, we need to find Descriptive Flexfield (DFF) called, "Further Job Information". In the following example, I am trying to get all the information for "US" context code.

The following query will display the DFF related information. You can try changing "fdfv.title" value too see different DFF.

/* Formatted on 4/29/2016 5:11:40 PM (QP5 v5.114.809.3010) */
  SELECT   fdf.title "DFF Title",
           fdf.application_table_name "Application Table",
           fdf.context_column_name "Context Column Name",
           --
           fdfcu.descriptive_flex_context_code "DFF Context Code",
           fdfcu.column_seq_num "Sequence",
           fdfcu.end_user_column_name "Segment Name",
           fdfcu.application_column_name "Column Name",
           --
           ffv.flex_value_set_name "Value Set Name"
    FROM   fnd_descr_flex_col_usage_vl fdfcu,
           fnd_descriptive_flexs_vl fdf,
           fnd_flex_value_sets ffv
   WHERE       1 = 1
           --
           AND fdf.title = 'Further Job Information'            -- <change it>
           AND fdfcu.descriptive_flex_context_code = 'US'       -- <change it>
           AND fdfcu.enabled_flag = 'Y'
           --
           AND fdfcu.flex_value_set_id = ffv.flex_value_set_id
           AND fdfcu.descriptive_flexfield_name =
                 fdf.descriptive_flexfield_name
           AND fdfcu.application_id = fdf.application_id
--
ORDER BY   fdfcu.descriptive_flexfield_name,
           fdfcu.descriptive_flex_context_code,
           fdfcu.column_seq_num;

Query to find KEY FLEX FIELDS (KFF)

/* Formatted on 7/4/2014 12:46:34 PM (QP5 v5.115.810.9015) */
SELECT fif.application_id,
       fif.id_flex_code,
       fif.id_flex_name,
       fif.application_table_name,
       fif.description,
       fifs.id_flex_num,
       fifs.id_flex_structure_code,
       fifse.segment_name,
       fifse.segment_num,
       fifse.flex_value_set_id
FROM fnd_id_flexs fif,
     fnd_id_flex_structures fifs,
     fnd_id_flex_segments fifse
WHERE     fif.application_id = fifs.application_id
      AND fif.id_flex_code = fifs.id_flex_code
      AND fifse.application_id = fif.application_id
      AND fifse.id_flex_code = fif.id_flex_code
      AND fifse.id_flex_num = fifs.id_flex_num
      AND fif.id_flex_code LIKE 'GL#'
      AND fif.id_flex_name LIKE 'Accounting Flexfield';

Query to find DFF

Let's say, we need to find Descriptive Flexfield (DFF) called, "Further Job Information". In the following example, I am trying to get all the information for "US" context code.

The following query will display the DFF related information. You can try changing "fdfv.title" value too see different DFF.

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

-- Query to find DFF information

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

SELECT

       fdf.title                             "DFF Title",

       fdf.application_table_name            "Application Table",

       fdf.context_column_name               "Context Column Name",

       --

       fdfcu.descriptive_flex_context_code   "DFF Context Code",

       fdfcu.column_seq_num                  "Sequence",

       fdfcu.end_user_column_name            "Segment Name",

       fdfcu.application_column_name         "Column Name",

       --

       ffv.flex_value_set_name               "Value Set Name"

  FROM

       fnd_descr_flex_col_usage_vl   fdfcu,

       fnd_descriptive_flexs_vl      fdf,

       fnd_flex_value_sets           ffv

 WHERE

       1 = 1

   --

   AND fdf.title = 'Further Job Information'        -- <change it>

   AND fdfcu.descriptive_flex_context_code = 'US'   -- <change it>

   AND fdfcu.enabled_flag = 'Y'

   --

   AND fdfcu.flex_value_set_id = ffv.flex_value_set_id

   AND fdfcu.descriptive_flexfield_name = fdf.descriptive_flexfield_name

   AND fdfcu.application_id = fdf.application_id

   --

 ORDER BY

       fdfcu.descriptive_flexfield_name,

       fdfcu.descriptive_flex_context_code,

       fdfcu.column_seq_num;

Create and register a custom Descriptive Flexfield (DFF)

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,

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

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. The table is registered using the script from this article. From the script we only need to change the line,

1	v_tab_name VARCHAR2 (32) := 'XX_CUSTOM_TABLE';

to

1	v_tab_name VARCHAR2 (32) := 'XX_SUPPLIER_BLACKLIST';

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.

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.