Oracle EBS GL_BALANCES Query with Code Combinations and Segment Descriptions

SELECT cc.concatenated_segments,
       cc.SEGMENT3 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          = 'Vision Operations (USA)'
AND    b.period_name   = 'SEP-26'
AND    b.currency_code = 'USD'
AND    b.actual_flag   = 'A'
AND b.translated_flag IS NULL
AND    b.template_id IS NULL                 -- exclude summary account balances
AND    cc.SEGMENT3 BETWEEN '1000' AND '9999'
-- 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.SEGMENT3, 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 = 'Vision Operations (USA)'
AND    b.period_name = 'SEP-26'
GROUP  BY b.currency_code, b.translated_flag, b.actual_flag
ORDER  BY 1, 2, 3;

-- Join method: works for independent value sets
SELECT cc.code_combination_id,
       cc.concatenated_segments,
       cc.SEGMENT3 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 = 'SEGMENT3'
JOIN   fnd_flex_values_vl fv
       ON  fv.flex_value_set_id = s.flex_value_set_id
       AND fv.flex_value        = cc.SEGMENT3
WHERE  l.name = 'Vision Operations (USA)'
AND    cc.SEGMENT3 BETWEEN '1000' AND '9999'
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, 3, cc.SEGMENT3) AS account_description
-- FROM   gl_code_combinations_kfv cc ...

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 = 'Vision Operations (USA)';

  -- 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'),
                                  '01-000-1110-0000-000');
  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 = 'Vision Operations (USA)'
AND    cc.concatenated_segments = '01-000-1110-0000-000';