oracle认证ebs,oracle ebs 账户组合验证

DECLARE

l_segment1 GL_CODE_COMBINATIONS.SEGMENT1%TYPE;

l_segment2 GL_CODE_COMBINATIONS.SEGMENT2%TYPE;

l_segment3 GL_CODE_COMBINATIONS.SEGMENT3%TYPE;

l_segment4 GL_CODE_COMBINATIONS.SEGMENT4%TYPE;

l_segment5 GL_CODE_COMBINATIONS.SEGMENT5%TYPE;

l_segment6 GL_CODE_COMBINATIONS.SEGMENT6%TYPE;

l_valid_combination BOOLEAN;

l_cr_combination BOOLEAN;

l_ccid GL_CODE_COMBINATIONS_KFV.code_combination_id%TYPE;

l_structure_num FND_ID_FLEX_STRUCTURES.ID_FLEX_NUM%TYPE;

l_conc_segs GL_CODE_COMBINATIONS_KFV.CONCATENATED_SEGMENTS%TYPE;

p_error_msg1 VARCHAR2(240);

p_error_msg2 VARCHAR2(240);

BEGIN

l_segment1 := '00101';

l_segment2 := '28506';

l_segment3 := '00000';

l_segment4 := '14302';

l_segment5 := '00455';

l_segment6 := '00000';

l_conc_segs := l_segment1||'.'||l_segment2||'.'||l_segment3||'.'||l_segment4||'.'||l_segment5||'.'||l_segment6 ;

BEGIN

SELECT id_flex_num

INTO l_structure_num

FROM apps.fnd_id_flex_structures

WHERE id_flex_code = 'GL#'

AND id_flex_structure_code='EPC_GL_ACCOUNTING_FLEXFIELD';

EXCEPTION

WHEN OTHERS THEN

l_structure_num:=NULL;

END;

---------------Check if CCID exits with the above Concatenated Segments---------------

BEGIN

SELECT code_combination_id

INTO l_ccid

FROM apps.gl_code_combinations_kfv

WHERE concatenated_segments = l_conc_segs;

EXCEPTION

WHEN OTHERS THEN

l_ccid:=NULL;

END;

IF l_ccid IS NOT NULL THEN

------------------------The CCID is Available----------------------

DBMS_OUTPUT.PUT_LINE('COMBINATION_ID= ' ||l_ccid);

ELSE

DBMS_OUTPUT.PUT_LINE('This is a New Combination. Validation Starts....');

------------Validate the New Combination--------------------------

l_valid_combination := APPS.FND_FLEX_KEYVAL.VALIDATE_SEGS

(

operation => 'CHECK_COMBINATION',

appl_short_name => 'SQLGL',

key_flex_code => 'GL#',

structure_number => L_STRUCTURE_NUM,

concat_segments => L_CONC_SEGS

);

p_error_msg1 := FND_FLEX_KEYVAL.ERROR_MESSAGE;

IF l_valid_combination then

DBMS_OUTPUT.PUT_LINE('Validation Successful! Creating the Combination...');

-------------------Create the New CCID--------------------------

L_CR_COMBINATION := APPS.FND_FLEX_KEYVAL.VALIDATE_SEGS

(

operation => 'CREATE_COMBINATION',

appl_short_name => 'SQLGL',

key_flex_code => 'GL#',

structure_number => L_STRUCTURE_NUM,

concat_segments => L_CONC_SEGS );

p_error_msg2 := FND_FLEX_KEYVAL.ERROR_MESSAGE;

IF l_cr_combination THEN

-------------------Fetch the New CCID--------------------------

SELECT code_combination_id

INTO l_ccid

FROM apps.gl_code_combinations_kfv

WHERE concatenated_segments = l_conc_segs;

DBMS_OUTPUT.PUT_LINE('NEW COMBINATION_ID = ' || l_ccid);

ELSE

-------------Error in creating a combination-----------------

DBMS_OUTPUT.PUT_LINE('Error in creating the combination: '||p_error_msg2);

END IF;

ELSE

--------The segments in the account string are not defined in gl value set----------

DBMS_OUTPUT.PUT_LINE('Error in validating the combination: '||p_error_msg1);

END IF;

END IF;

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE(SQLCODE||' '||SQLERRM);

END;

如上 验证ccid的仅能验证是否符合交叉规则、科目段值是否有效。

需要添加段值是否含有父值逻辑：

l_valid_combination := APPS.FND_FLEX_KEYVAL.VALIDATE_SEGS(operation => 'CHECK_COMBINATION',

appl_short_name => 'SQLGL',

key_flex_code => 'GL#',

structure_number => L_STRUCTURE_NUM,

concat_segments => L_CONC_SEGS,

vrule => '\nSUMMARY_FLAG\nI\n' || 'APPL=SQLGL;NAME=GL_JE_SUMMARY_ACCOUNT\nN\0');

p_error_msg1 := FND_FLEX_KEYVAL.ERROR_MESSAGE;