Query to fetch Dependent Value Set Values for given Independent Value Set in Oracle APPS

  SELECT ffvs_major.flex_value_set_id   major_flex_value_set_id,
         ffvs_major.flex_value_set_name major_flex_value_set_name,
         ffvs_major.description         major_set_description,
         ffvs_major.validation_type     major_validation_type,
         ffv_major.flex_value           major_flex_value,
         ffvt_major.description         major_value_description,
         ffv_major.enabled_flag         major_enabled_flag,
         ffv_major.last_update_date     major_last_update_date,
         ffv_major.last_updated_by      major_last_updated_by,
         ffv_major.attribute1           major_attribute1,
         ffv_major.attribute2           major_attribute2,
         ffv_major.attribute3           major_attribute3
        ,ffvs_minor.flex_value_set_id   minor_flex_value_set_id,
         ffvs_minor.flex_value_set_name minor_flex_value_set_name,
         ffvs_minor.description         minor_set_description,
         ffvs_minor.validation_type     minor_validation_type,
         ffv_major.flex_value             minor_flex_value,
         ffvt_minor.description         minor_value_description,
         ffv_minor.enabled_flag         minor_enabled_flag,
         ffv_minor.last_update_date     minor_last_update_date,
         ffv_minor.last_updated_by         minor_last_updated_by,
         ffv_minor.attribute1             minor_attribute1,
         ffv_minor.attribute2             minor_attribute2,
         ffv_minor.attribute3             minor_attribute3
    FROM fnd_flex_value_sets ffvs_major
        ,fnd_flex_values ffv_major
        ,fnd_flex_values_tl ffvt_major
        ,fnd_flex_value_sets ffvs_minor
        ,fnd_flex_values ffv_minor
        ,fnd_flex_values_tl ffvt_minor
   WHERE ffvs_major.flex_value_set_id = ffv_major.flex_value_set_id
     AND ffv_major.flex_value_id = ffvt_major.flex_value_id
     AND ffvt_major.language = USERENV ('LANG')
     AND UPPER(ffvs_major.flex_value_set_name) = 'XX_INDEPENDENT_VS'--<<Independent Value Set Name>>
     AND ffvs_major.flex_value_set_id = ffvs_minor.parent_flex_value_set_id
     AND ffv_major.flex_value = 'XX_IND_VALUE_SET_VALUE'--<<Independent Value Set Value>>
     AND ffv_major.flex_value = ffv_minor.parent_flex_value_low
     AND ffvs_minor.flex_value_set_id = ffv_minor.flex_value_set_id
     AND ffv_minor.flex_value_id = ffvt_minor.flex_value_id
     AND ffvt_minor.language = USERENV ('LANG')
ORDER BY ffv_major.flex_value ASC

Why COA Structure Separated from the Instance(Oracle EBS GL vs Oracle Fusion GL)

• In R12 EBS, Data Access Sets were limited to ledgers that shared the same COA and Calendar.We intended to expand Data Access Sets to be set at the COA structure level where you have the same shape, but you might have different value sets for different countries.

• Best practice is to have a one to one relationship between the chart of accounts structure and charts of accounts instance.

• Do NOT have multiple COA instances share the same structure.