To Find Profile option values - R12 Query

SELECT fpo.profile_option_name SHORT_NAME,
         fpot.user_profile_option_name NAME,
         DECODE (fpov.level_id,
                 10001, 'Site',
                 10002, 'Application',
                 10003, 'Responsibility',
                 10004, 'User',
                 10005, 'Server',
                 'UnDef')
            LEVEL_SET,
         DECODE (TO_CHAR (fpov.level_id),
                 '10001', '',
                 '10002', fap.application_short_name,
                 '10003', frsp.responsibility_key,
                 '10005', fnod.node_name,
                 '10006', hou.name,
                 '10004', fu.user_name,
                 'UnDef')
            "CONTEXT",
         fpov.profile_option_value VALUE
    FROM fnd_profile_options fpo,
         fnd_profile_option_values fpov,
         fnd_profile_options_tl fpot,
         fnd_user fu,
         fnd_application fap,
         fnd_responsibility frsp,
         fnd_nodes fnod,
         hr_operating_units hou
   WHERE     fpo.profile_option_id = fpov.profile_option_id(+)
         AND fpo.profile_option_name = fpot.profile_option_name
         AND fu.user_id(+) = fpov.level_value
         AND frsp.application_id(+) = fpov.level_value_application_id
         AND frsp.responsibility_id(+) = fpov.level_value
         AND fap.application_id(+) = fpov.level_value
         AND fnod.node_id(+) = fpov.level_value
         AND hou.organization_id(+) = fpov.level_value
         AND fpot.user_profile_option_name IN ('&User_Profile_Option_Name')
ORDER BY short_name;

Delete lookup values - API

DECLARE

  CURSOR c1 IS

    SELECT lookup_type, lookup_code, security_group_id, view_application_id

      FROM fnd_lookup_values_vl

     WHERE lookup_type = 'mylookupname'

       AND lookup_code LIKE 'mylookupcode';

BEGIN

  FOR i IN c1

  LOOP

    BEGIN

      fnd_lookup_values_pkg.delete_row(x_lookup_type => i.lookup_type,

                        x_security_group_id   => i.security_group_id,

                        x_view_application_id => i.view_application_id,

                        x_lookup_code         => i.lookup_code);

      COMMIT;

  

      DBMS_OUTPUT.put_line(i.lookup_code || ' has been Purged !!!');

    EXCEPTION

      WHEN OTHERS THEN

        DBMS_OUTPUT.put_line('Inner Exception: ' || SQLERRM);

    END;

  END LOOP;

EXCEPTION

  WHEN OTHERS THEN

    DBMS_OUTPUT.put_line('Main Exception ' || SQLERRM);

END;

Delete ValueSet Values - API

DECLARE

  l_err_msg VARCHAR2(500) := NULL;

  CURSOR c1 IS

    SELECT ffv.flex_value_id, ffv.flex_value

      FROM fnd_flex_value_sets ffvs,

           fnd_flex_values     ffv,

           fnd_flex_values_tl  ffvt

     WHERE flex_value_set_name = ':myvalueset' -- Value Set Name

       AND ffv.flex_value=:flex_val

       AND ffv.flex_value_set_id = ffvs.flex_value_set_id

       AND ffvt.flex_value_id = ffv.flex_value_id

       AND ffvs.flex_value_set_id = ffv.flex_value_set_id

       AND ffvt.language = 'US'

       AND ffv.enabled_flag = 'Y'

       AND ffv.summary_flag = 'N';

BEGIN

  FOR i IN c1

  LOOP

    fnd_flex_values_pkg.delete_row(i.flex_value_id);

    COMMIT;

    DBMS_OUTPUT.put_line(i.flex_value_id || ' Deleted  Successfully !!!!');

  END LOOP;

EXCEPTION

  WHEN OTHERS THEN

    l_err_msg := SQLERRM;

    DBMS_OUTPUT.put_line('Exception: ' || l_err_msg);

 

END;

Below statement used to deleted DFFS

DECLARE

  l_application_id               NUMBER := 0;

  l_descriptive_flexfield_name   VARCHAR2(100) := 'FND_FLEX_VALUES';

  l_descriptive_flex_context_cod VARCHAR2(100) := 'context code';

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;

  begin

    fnd_descr_flex_contexts_pkg.delete_row(0,

                                           l_descriptive_flexfield_name,

                                           l_descriptive_flex_context_cod);

  end;

  commit;

end;