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’