Monday, 14 November 2016

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;

No comments:

Post a Comment