Script to remove Descriptive Flexfield Segments

This is useful in cases where FNDLOAD does not perform a delete of segments. Hence such a script can be used for cleanup of the DFF. Simply replace the contents in "bold" text below to make this work on your environment

--Script to clean up DFF Segments

set serveroutput on size 100000;
DECLARE

l_flexfield_type fnd_flex_key_api.flexfield_type;l_structure_type fnd_flex_key_api.structure_type;l_segment_type fnd_flex_key_api.segment_type;l_flex_num VARCHAR2(1000);

BEGIN

FOR p_rec IN (SELECT end_user_column_name,descriptive_flexfield_name,descriptive_flex_context_code
FROM   fnd_descr_flex_col_usage_vl
WHERE (application_id = 100)
AND   (descriptive_flexfield_name = 'Person Info DDF')
AND   (descriptive_flex_context_code LIKE 'XX_DFF_CONTEXT_HERE'))

LOOP

fnd_flex_dsc_api.delete_segment
(appl_short_name => 'PER',
 flexfield_name  => p_rec.descriptive_flexfield_name,
 CONTEXT         => p_rec.descriptive_flex_context_code,
 SEGMENT         => p_rec.end_user_column_name);

dbms_output.put_line('Segment'||p_rec.end_user_column_name||deleted ');
END LOOP;
END;