API to CREATE GL Code combination FND_FLEX_KEYVAL.VALIDATE_SEGS in R12 ~ Oracle Apps Knowledge Sharing

Sunday, August 25, 2019

API to CREATE GL Code combination FND_FLEX_KEYVAL.VALIDATE_SEGS in R12

In this post, we have given a sample script to create a new code combination based on concatenated segment values.

This API will first check whether the given combination already created by checking in GL_CODE_COMBINATION table, if so, it will simply return the code combination id from the table. If given combination is not already created, it will create the code combination id for the given combination provided it satisfies all segment level validations including cross validation rules.

Test instance: R12.2.4

Script:

SET SERVEROUTPUT ON;

DECLARE

lv_r12_ccd           VARCHAR2(2000) DEFAULT SHARE.ABCDEF.2901.000000';

lv_ledger_name       VARCHAR2(300) DEFAULT 'SHARE Ledger';

ln_structure_number NUMBER;

lb_sucess            BOOLEAN;

BEGIN

SELECT chart_of_accounts_id

    INTO ln_structure_number

    FROM gl_ledgers

   WHERE name = lv_ledger_name;

lb_sucess := fnd_flex_keyval.validate_segs

            (

             operation           => 'CREATE_COMBINATION',

             appl_short_name     => 'SQLGL',

             key_flex_code       => 'GL#',

             structure_number    => ln_structure_number,

             concat_segments     => lv_r12_ccd,

             validation_date     => SYSDATE

            );

IF lb_sucess

THEN

    DBMS_OUTPUT.PUT_LINE('Successful. Code Combination ID: '||fnd_flex_keyval.combination_id());

ELSE

    DBMS_OUTPUT.PUT_LINE('Error creating a Code combination id for ' ||lv_r12_ccd||' Error:'|| fnd_flex_keyval.error_message());

END IF;



EXCEPTION

WHEN OTHERS THEN

    DBMS_OUTPUT.PUT_LINE('Unable to create a code combination (OTHERS EXCEPTION) for ' ||lv_r12_ccd||' Error:'|| SQLERRM);

END;