Query : GL - COA

on

📊 Oracle Cloud ERP GL Chart of Accounts (COA) Queries

Two essential queries to explore your GL Chart of Accounts structure and segment value sets. Perfect for understanding your COA segments (Acct, LE, Emp, etc.).

📋 Query 1: COA Segment Values

Lists all active values for your COA segments (Account, Entity, Cost Center, etc.) 

SELECT 
    ffvs.flex_value_set_id,
    ffvs.flex_value_set_name,
    ffvs.description              AS set_description,
    ffvs.validation_type,
    ffv.flex_value,
    ffvt.description              AS value_description,
    ffv.enabled_flag,
    ffv.start_date_active,
    ffv.end_date_active

FROM 
    fnd_flex_value_sets   ffvs,
    fnd_flex_values       ffv,
    fnd_flex_values_tl    ffvt

WHERE 1=1
    AND ffvs.flex_value_set_id = ffv.flex_value_set_id
    AND ffv.flex_value_id      = ffvt.flex_value_id
    AND ffvt.language          = USERENV('LANG')
    AND flex_value_set_name IN (
        'Account', 'Entity', 'Spare', 'CostCenter', 'Project', 'Intercompany', 
        'Vendor', 'Future1', 'Future2'
    )

ORDER BY flex_value ASC, flex_value_set_name ASC;

🏗️ Query 2: COA Structure & Segments

Shows your Chart of Accounts structure, segments, and value sets per Business Unit/Ledger 

SELECT 
    hou.name                    AS BU_NAME,
    led.NAME                    AS Ledger_Name,
    Str.STRUCTURE_CODE          AS CHART_OF_ACCOUNTS_Name,
    SegInSt.SEGMENT_CODE        AS COA_SEGMENT_NAME,
    vs.value_set_code,
    vs.description              AS value_set_desc

FROM 
    hr_operating_units        hou,
    xla_gl_ledgers            led,
    fnd_kf_structures_b       Str,
    fnd_kf_str_instances_b    StrInSt,
    fnd_kf_segment_instances  SegInSt,
    fnd_vs_value_sets         vs

WHERE 1=1
    AND led.ledger_id              = hou.set_of_books_id
    AND led.CHART_OF_ACCOUNTS_ID   = Str.Structure_id
    AND Str.KEY_FLEXFIELD_CODE     = 'GL#'
    AND Str.Structure_id           = StrInSt.Structure_id
    AND SegInSt.Structure_inStance_id = StrInSt.Structure_inStance_id
    AND vs.VALUE_SET_ID            = SegInSt.VALUE_SET_ID;
💡 Pro Tip: Run Query 1 first to see your segment values, then Query 2 to understand the structure linking Business Units → Ledgers → COA Segments.
📝 Tables Used: FND_FLEX_* (Value Sets), XLA_GL_LEDGERS, HR_OPERATING_UNITS

Comments

Post a Comment