Given a list of Oracle GL account code segment values…

E.g. “11030-001-0000-00000000-INF1018-000000”

…it is better to use Oracle’s API’s to derive the corresponding code_combination_id (ccid). Don’t just search in the gl_code_combinations table – using the following API’s will allow Oracle GL’s cross-validation rules to assess the validity of a given account code combination, and, if GL is configured to allow dynamic insertion of new account codes (see below), a non-existant code combination will be created automatically in gl_code_combinations:

Resp: General Ledger Super User
Nav: Setup > Financials > Flexfields > Key > Segments > Allow Dynamic Inserts

Note: if you are testing from a client PC (using a developer tool such as Toad or SQL*Developer), ensure that the PC’s nls_language is set:. For example:

ALTER SESSION SET nls_language='American'

If the language is not set, the following kind of error occurs:

Error: Value 11030 for the flexfield segment Nominal does not exist in the value set C4GL_NOMINAL.

…even though the segment value exists in Oracle GL.

There are two public API’s available:

1. PROCEDURE: fnd_flex_ext.get_combination_id

DECLARE
  v_ccid    NUMBER;
  v_return  BOOLEAN;
  v_err     VARCHAR2(2000);
  segments  fnd_flex_ext.segmentarray;
  
BEGIN
  segments(1) := '11030';
  segments(2) := '001';
  segments(3) := '0000';
  segments(4) := '00000000';
  segments(5) := 'INF1018';
  segments(6) := '000000';
  
  v_return := fnd_flex_ext.get_combination_id
              (application_short_name => 'SQLGL'
              ,key_flex_code          => 'GL#'
              ,structure_number       => 50143
              ,validation_date        => SYSDATE
              ,n_segments             => 6
              ,segments               => segments
              ,combination_id         => v_ccid
              --,data_set               => -1
              );
              
  IF (v_return) THEN
    dbms_output.put_line('CCID is '||v_ccid); 
  ELSE
    v_err := fnd_flex_ext.get_message;
    dbms_output.put_line('Error: '||v_err);
  END IF; 
                                                   
END;

Input parameters: the “structure_number” is the chart-of-accounts ID, and the segments of the account code combination are passed in an array variable (fnd_flex_ext.segmentarray).

Output: the “combination_id” OUT parameter contains the ccid, if it can be derived. The fnd_flex_ext.get_message function can be used to display an error message, if an error occurs. For example:

Error: Value INF1018xx is longer than its maximum length of 7 characters.

2. FUNCTION: fnd_flex_ext.get_ccid

A shorthand version of the above procedure, which takes a concatenated list of segment values as an input string:

DECLARE
  v_ccid   NUMBER;
  v_err    VARCHAR2(2000);
BEGIN
  v_ccid := fnd_flex_ext.get_ccid
            (application_short_name => 'SQLGL'
            ,key_flex_code          => 'GL#'
            ,structure_number       => 50143
            ,validation_date        => TO_CHAR(SYSDATE,'DD-MON-YYYY')
            ,concatenated_segments  =>'11030-001-0000-00000000-INF1018-000000'
            );
            
  dbms_output.put_line('CCID is '||v_ccid);

  v_err := fnd_flex_ext.get_message;

  IF (v_err IS NOT NULL) THEN
    dbms_output.put_line('Error is '||v_err);
  END IF;
  
END;

The validation_date is actually passed as a VARCHAR2, so it is imperitive to use one of two possible date formats:

‘DD-MON-YYYY’, or
‘YYYY/MM/DD HH24:MI:SS’