Logic: Call main function FND_FLEX_KEYVAL.VALIDATE_SEGS first, then call function FND_FLEX_KEYVAL.COMBINATION_ID to return code_combination_id if validation return true or call function FND_FLEX_KEYVAL.error_message to return error message if validation return false.
Function FND_FLEX_KEYVAL.VALIDATE_SEGS: These functions are called with either the key flexfield segments or combination id, respecitvely. They look up or create the desired combination and return TRUE if everything is ok, or FALSE on error. The results and/or error messages are not returned directly, but rather are stored in PLSQL package globals whose contents can be accessed by the remaining functions in this package. The global variables are reset upon each call to validate_segs() or validate_ccid() so the calling function must get all needed results before passing in the next combination.
The global variable access function combination_id is then called to return code_combination_id based on the result of VALIDATE_SEGS call.
The global variable access function error_massage is then called to return error massage based on the result of VALIDATE_SEGS call.
-- CODE
-- Creating function
FUNCTION create_ccid
( p_concat_segs in VARCHAR2)
RETURN VARCHAR2 IS
l_status BOOLEAN;
l_coa_id NUMBER;
BEGIN
SELECT chart_of_accounts_id into l_coa_id
FROM GL_SETS_OF_BOOKS
--WHERE SET_OF_BOOKS_ID = set_of_books_id;
WHERE name = ledger_name;
-- either of above two where condition.
l_status := FND_FLEX_KEYVAL.VALIDATE_SEGS( -- Oracle standard function
'CREATE_COMBINATION',
'SQLGL',
'GL#',
l_coa_id,
p_concat_segs,
'V',
SYSDATE,
'ALL', NULL, NULL, NULL, NULL,
FALSE,FALSE, NULL, NULL, NULL);
IF l_status THEN
RETURN 'S';
ELSE
RETURN 'F';
END IF;
END create_ccid;
/
--Calling the function
DECLARE
RETVAL VARCHAR2(200);
p_occ VARCHAR2(200); /* '1001.0000.131000.9999.00000.0000.0000' THIS IS the COMBINATION TO CREATE or VALIDATE*/
BEGIN
RETVAL := create_ccid(p_occ);
IF (RETVAL = 'S') THEN
l_ccid := FND_FLEX_KEYVAL.COMBINATION_ID; -- Oracle standard function, get ccid based on the result of function VALIDATE_SEGS call;
DBMS_OUTPUT.PUT_LINE('CCID is: '||l_ccid);
ELSIF (RETVAL = 'F') THEN
l_err_msg := FND_FLEX_KEYVAL.error_message; -- Oracle standard function, get error message based on the result of function VALIDATE_SEGS call;
DBMS_OUTPUT.PUT_LINE('Error with validation rule: '||l_err_msg);
ELSE
DBMS_OUTPUT.PUT_LINE('Other error Message.');
END IF;
END;
