# Oracle EBS GL_BALANCES Query with Code Combinations and Segment Descriptions

> Source: https://netray.co/tools/oracle-ebs-gl-balances-code-combination-sql-generator
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

Generates a trial balance query joining GL_BALANCES, GL_CODE_COMBINATIONS_KFV and GL_LEDGERS, a segment description query, and a PL/SQL CCID lookup.

## Category

oracle

## Erp

Oracle E-Business Suite

## Audience

- Oracle Apps technical consultant
- GL accountant
- Financial systems analyst

## Tool

**Kind:** template

**Output:** code

**Language:** sql

**Fields:**

#### Ledger name

**Key:** ledger

**Type:** text

**Default:** Vision Operations (USA)

#### Period name

**Key:** period

**Type:** text

**Default:** SEP-26

#### Currency code

**Key:** ccy

**Type:** text

**Default:** USD

**Help:** Ledger currency for normal reporting.

#### Balance type (ACTUAL_FLAG)

**Key:** flag

**Type:** select

**Options:**

- A
- B
- E

**Default:** A

**Help:** A actual, B budget (also filter budget_version_id), E encumbrance (also filter encumbrance_type_id).

#### Translated flag filter

**Key:** translated

**Type:** select

**Options:**

- AND b.translated_flag IS NULL
- AND b.translated_flag = 'Y'

**Default:** AND b.translated_flag IS NULL

**Help:** IS NULL for ledger currency totals; Y for translated balances. Check with the diagnostic query.

#### Account segment column

**Key:** seg

**Type:** text

**Default:** SEGMENT3

#### Account from

**Key:** from

**Type:** text

**Default:** 1000

#### Account to

**Key:** to

**Type:** text

**Default:** 9999

#### Segment number (for GET_DESCRIPTION_SQL)

**Key:** seg_num

**Type:** number

**Default:** 3

#### Concatenated segments for CCID lookup

**Key:** concat

**Type:** text

**Default:** 01-000-1110-0000-000

**Blocks:**

#### Query 1: trial balance for one period

**Body:** SELECT cc.concatenated_segments,
       cc.{{seg}} AS account,
       cc.account_type,
       SUM(NVL(b.begin_balance_dr, 0) - NVL(b.begin_balance_cr, 0)) AS begin_balance,
       SUM(NVL(b.period_net_dr, 0)    - NVL(b.period_net_cr, 0))    AS period_activity,
       SUM(NVL(b.begin_balance_dr, 0) - NVL(b.begin_balance_cr, 0)
         + NVL(b.period_net_dr, 0)    - NVL(b.period_net_cr, 0))    AS ending_balance
FROM   gl_balances b
JOIN   gl_ledgers l
       ON l.ledger_id = b.ledger_id
JOIN   gl_code_combinations_kfv cc
       ON cc.code_combination_id = b.code_combination_id
WHERE  l.name          = '{{ledger}}'
AND    b.period_name   = '{{period}}'
AND    b.currency_code = '{{ccy}}'
AND    b.actual_flag   = '{{flag}}'
{{translated}}
AND    b.template_id IS NULL                 -- exclude summary account balances
AND    cc.{{seg}} BETWEEN '{{from}}' AND '{{to}}'
-- For actual_flag B add: AND b.budget_version_id = <GL_BUDGET_VERSIONS.BUDGET_VERSION_ID>
-- For actual_flag E add: AND b.encumbrance_type_id = <encumbrance type id>
GROUP  BY cc.concatenated_segments, cc.{{seg}}, cc.account_type
ORDER  BY cc.concatenated_segments;

-- Diagnostic: which currency / translated_flag combinations exist for this ledger and period
SELECT b.currency_code, b.translated_flag, b.actual_flag, COUNT(*) AS row_count
FROM   gl_balances b
JOIN   gl_ledgers l ON l.ledger_id = b.ledger_id
WHERE  l.name = '{{ledger}}'
AND    b.period_name = '{{period}}'
GROUP  BY b.currency_code, b.translated_flag, b.actual_flag
ORDER  BY 1, 2, 3;

#### Query 2: account segment descriptions

**Body:** -- Join method: works for independent value sets
SELECT cc.code_combination_id,
       cc.concatenated_segments,
       cc.{{seg}} AS account,
       fv.description AS account_description
FROM   gl_code_combinations_kfv cc
JOIN   gl_ledgers l
       ON l.chart_of_accounts_id = cc.chart_of_accounts_id
JOIN   fnd_id_flex_segments s
       ON  s.application_id          = 101
       AND s.id_flex_code            = 'GL#'
       AND s.id_flex_num             = cc.chart_of_accounts_id
       AND s.application_column_name = '{{seg}}'
JOIN   fnd_flex_values_vl fv
       ON  fv.flex_value_set_id = s.flex_value_set_id
       AND fv.flex_value        = cc.{{seg}}
WHERE  l.name = '{{ledger}}'
AND    cc.{{seg}} BETWEEN '{{from}}' AND '{{to}}'
AND    cc.summary_flag = 'N'
ORDER  BY cc.concatenated_segments;

-- Function method (verify the segment numbering on your chart of accounts):
-- SELECT cc.concatenated_segments,
--        GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL(cc.chart_of_accounts_id, {{seg_num}}, cc.{{seg}}) AS account_description
-- FROM   gl_code_combinations_kfv cc ...

#### Block 3: find or create a CCID from a concatenated string

**Body:** SET SERVEROUTPUT ON
DECLARE
  l_coa_id NUMBER;
  l_ccid   NUMBER;
BEGIN
  SELECT chart_of_accounts_id INTO l_coa_id
  FROM   gl_ledgers
  WHERE  name = '{{ledger}}';

  -- Creates the combination if it does not exist and Dynamic Inserts is allowed
  l_ccid := FND_FLEX_EXT.GET_CCID('SQLGL', 'GL#', l_coa_id,
                                  TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS'),
                                  '{{concat}}');
  IF l_ccid = 0 THEN
    DBMS_OUTPUT.PUT_LINE('Invalid combination: ' || FND_FLEX_EXT.GET_MESSAGE);
  ELSE
    DBMS_OUTPUT.PUT_LINE('CCID: ' || l_ccid);
  END IF;
END;
/

-- Look up only, never create:
SELECT cc.code_combination_id, cc.enabled_flag, cc.start_date_active, cc.end_date_active
FROM   gl_code_combinations_kfv cc
JOIN   gl_ledgers l ON l.chart_of_accounts_id = cc.chart_of_accounts_id
WHERE  l.name = '{{ledger}}'
AND    cc.concatenated_segments = '{{concat}}';

**Notes:** Verify: the TRANSLATED_FLAG values present for your ledger with the diagnostic query, the account segment column in FND_ID_FLEX_SEGMENTS, the segment delimiter used in the concatenated string, and that GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL exists on your release before uncommenting it. Dependent value sets need the parent value in the description join.

## Content

**Intro:** This generator writes the General Ledger queries Oracle E-Business Suite R12 developers keep rewriting: a trial balance from GL_BALANCES for one ledger and period, account descriptions from the FND value sets, and a PL/SQL lookup that turns a concatenated account string into a CODE_COMBINATION_ID.

**Sections:**

#### How to use it

**Body:** Enter the ledger name as it appears in GL_LEDGERS.NAME, the period name (for example SEP-26), the currency and the balance type. Pick the translated flag filter: ledger currency totals use translated_flag IS NULL; run the diagnostic query first if your ledger has foreign currency or translated balances. Enter the column that holds the natural account in your chart (find it in FND_ID_FLEX_SEGMENTS for id_flex_code GL#) and an account range. Query 1 gives begin, activity and ending balance per combination; Query 2 adds descriptions; Block 3 resolves a string to a CCID.

**Bullets:**

- Ledger name: SELECT name, ledger_id, currency_code FROM gl_ledgers
- Account column: FND_ID_FLEX_SEGMENTS.APPLICATION_COLUMN_NAME
- Period names: GL_PERIOD_STATUSES for application 101 and your ledger

#### How GL_BALANCES stores balances

**Body:** GL_BALANCES has one row per ledger, code combination, currency, period, balance type and, for budgets and encumbrances, budget version or encumbrance type. BEGIN_BALANCE_DR and BEGIN_BALANCE_CR are the opening balance for the period, PERIOD_NET_DR and PERIOD_NET_CR the posted activity in the period. Ending balance is begin plus net. Balances are updated only by posting, so unposted journals are not here. Income and expense accounts reset their begin balance at the first period of a new year after the year-end roll, which is why year-to-date P&L is begin plus activity in any period of the year. TEMPLATE_ID is populated on summary account balances, so excluding it avoids double counting.

#### Worked example

**Body:** For Vision Operations (USA), SEP-26, USD actuals with translated_flag IS NULL and accounts 1000 to 9999, Query 1 returns one row per combination with begin, activity and ending balance. Summing ending_balance across all accounts should give zero for a balanced ledger; a non-zero total points to a missing filter, usually including both entered and translated rows or a summary template. Query 2 adds the account description from FND_FLEX_VALUES_VL, joined through the value set attached to the account segment of your chart. Block 3 with 01-000-1110-0000-000 prints the CCID, or the flexfield error if the combination is invalid or cross-validation rules reject it.

#### Gotchas

**Body:** The segment column is chart specific: SEGMENT3 is common but not universal. Period names follow the accounting calendar, so check GL_PERIODS rather than guessing the format. FND_FLEX_EXT.GET_CCID creates a combination when Dynamic Inserts is enabled, which is a write to GL_CODE_COMBINATIONS; use the lookup-only query when you only need to check. Budget balances need BUDGET_VERSION_ID or you add every budget together. For multiple ledgers sharing a chart, always filter by ledger, not just chart_of_accounts_id.

**Cta:** If you want finance staff to ask for a trial balance or an account movement in plain English and get the GL answer from your own EBS instance on your servers, we can set that up.

**Faq:**

#### How do I join GL_BALANCES and GL_LEDGERS in R12?

**Answer:** Join on GL_BALANCES.LEDGER_ID = GL_LEDGERS.LEDGER_ID (in 11i the column was SET_OF_BOOKS_ID against GL_SETS_OF_BOOKS). Filter by ledger name, period name, currency code, actual_flag and the translated flag, then join GL_CODE_COMBINATIONS_KFV on CODE_COMBINATION_ID for the account string.

#### How do I get the GL account with its description?

**Answer:** Find the value set of the account segment in FND_ID_FLEX_SEGMENTS (application_id 101, id_flex_code GL#, id_flex_num = chart_of_accounts_id, application_column_name = your segment), then join FND_FLEX_VALUES_VL on flex_value_set_id and flex_value = the segment value. Query 2 does exactly this.

#### What is GL_CODE_COMBINATIONS_KFV?

**Answer:** A view over GL_CODE_COMBINATIONS that adds CONCATENATED_SEGMENTS, the full account string with your chart's delimiter, built from SEGMENT1 to SEGMENT30. Use it when you need the readable account; use the base table with individual segment columns when filtering performance matters.

#### Why does my trial balance not net to zero?

**Answer:** Typical causes: summary account balances included (filter template_id IS NULL), translated and entered rows mixed (filter translated_flag), more than one ledger, budget rows without a version filter, or an account range that cuts off part of the ledger. Run the diagnostic query to see which combinations exist.

## Search terms

oracle ebs gl_balances, gl_balances gl_ledgers join oracle ebs r12, oracle ebs query for gl code with description, query to get gl segment in oracle ebs, oracle ebs gl_code_combinations_kfv, oracle ebs gl balances table, fnd_flex_ext.get_ccid

---
Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json