Like this:

When creating a GL data mart one of the most important components is the Chart of Accounts (COA). Here is one way to create dimension tables for each level in the COA hierarchy.

Create a view called GL_COA_V, replacing the flex_value_set_name values for your specific COA:

CREATE OR REPLACE FORCE VIEW gl_stage.gl_coa_v (coa_flex_value_id,
                                                coa_flex_value_meaning,
                                                coa_description,
                                                coa_flex_value_set_name
                                               )
AS
   SELECT DISTINCT fvt.flex_value_id coa_flex_value_id,
                   fvt.flex_value_meaning coa_flex_value_meaning,
                   fvt.description coa_description,
                   fvs.flex_value_set_name coa_flex_value_set_name
              FROM gl_fnd_flex_values_s fv,
                   gl_fnd_flex_values_tl_s fvt,
                   gl_fnd_flex_value_sets_s fvs
             WHERE fv.flex_value_id = fvt.flex_value_id
               AND fvs.flex_value_set_id = fv.flex_value_set_id
               AND fvs.flex_value_set_name IN
                      ('XX_COMPANY',
                       'XX_COMMERCIAL_UNIT',
                       'XX_BRANCH',
                       'XX_DEPARTMENT',
                       'XX_ACCOUNT'
                      )
               AND fvt.source_lang = 'US'

This view can be used as the single data source for each dimension table mapping. All you need is a filter to get the required hierarchy level for the dimension that you are mapping.

Here’s an example of a mapping for a dimension table called GL_ACCOUNT_D. Mappings for all other COA dimensions will be the same except for the filter value.

Advertisement