Delete a Descriptive Flexfield Context Value

How often that you created a DFF context value, and then you were told that this context value required extensive changes? Oracle does not allow us to delete a DFF Context Value since it could be used by the application. I agree that if it is used, then we should not allow to delete it.  How about if it is not even been used? We are not able to rename the Context Value to something else so that this record can be reused meaningfully. It also causes a problem that the Attribute columns are occupied by this Context Value and cannot be assigned to other Context Value. What a waste. So here is a script to delete a particular DFF context value.

Warning: DO NOT use this script for the DFF Context Values used for SIT / EIT in Human Resource module since it does not take care the security side of it.

If you issue an commit, make sure you go back to the DFF Form, unfreeze/freeze the definition and compile the DFF again.

set serveroutput on
declare

-- Copy these values for your DDF Context Value
  var_AppName  VARCHAR2(100) := 'Order Management';
  var_dffTitle VARCHAR2(200) := 'Additional Line Attribute Information';
  var_dffCode  VARCHAR2(50)  := 'XXXXX';
  var_lang     VARCHAR2(5)   := 'US';
  
  num_appID      NUMBER;
  var_dffName    VARCHAR2(100);
  num_rowDeleted NUMBER;

begin
for rs in (
  select b.application_id
       , a.descriptive_flexfield_name
    from FND_DESCRIPTIVE_FLEXS_TL a
       , FND_APPLICATION_TL b
   where a.language=var_lang
     and b.language=var_lang
     and b.application_id   = a.application_id
     and b.application_name = var_AppName
     and a.title            = var_dffTitle
     ) loop

  num_appID := rs.application_id;
  var_dffName := rs.descriptive_flexfield_name;

end loop;

if num_appID is null or var_dffName is null then
  dbms_output.put_line('DFF not found');
  return;
end if;

delete from FND_DESCR_FLEX_COLUMN_USAGES
 where descriptive_flex_context_code = var_dffCode
  and descriptive_flexfield_name     = var_dffName
  and application_id                 = num_appID;

num_rowDeleted := SQL%ROWCOUNT;
dbms_output.put_line('No. of row delete in FND_DESCR_FLEX_COLUMN_USAGES : ' || num_rowDeleted);

delete from FND_DESCR_FLEX_COL_USAGE_TL
 where descriptive_flex_context_code = var_dffCode
  and descriptive_flexfield_name     = var_dffName
  and application_id                 = num_appID;

num_rowDeleted := SQL%ROWCOUNT;
dbms_output.put_line('No. of row delete in FND_DESCR_FLEX_COL_USAGE_TL  : ' || num_rowDeleted);

delete from FND_DESCR_FLEX_CONTEXTS
 where descriptive_flex_context_code = var_dffCode
  and descriptive_flexfield_name     = var_dffName
  and application_id                 = num_appID;

num_rowDeleted := SQL%ROWCOUNT;
dbms_output.put_line('No. of row delete in FND_DESCR_FLEX_CONTEXTS      : ' || num_rowDeleted);

delete from FND_DESCR_FLEX_CONTEXTS_TL
 where descriptive_flex_context_code = var_dffCode
  and descriptive_flexfield_name     = var_dffName
  and application_id                 = num_appID;

num_rowDeleted := SQL%ROWCOUNT;
dbms_output.put_line('No. of row delete in FND_DESCR_FLEX_CONTEXTS_TL   : ' || num_rowDeleted);

dbms_output.put_line('Issue a COMMIT to confirm or ROLLBACK to revert');
end;
/