Account Combination Creation API

Account Combination Creation API:

There are 2 flexfield APIs which you can use to either validation the existing given code_combination_id (ccid) or to validate and create a new code combination (Account).
1. fnd_flex_keyval.VALIDATE_SEGS() - To validate and if required can create Account code combination.
2. fnd_flex_keyval.validate_ccid() - The API used to validate the code combination id value. If the code combination valid then this API will returns true else false.

1. fnd_flex_keyval.VALIDATE_SEGS():

------------------------------------------------------------------------------------------------------------
create or replace function create_ccid
( p_concat_segs in varchar2
) return varchar2
is
  -- pragma autonomous_transaction; -- if you need autonomy!
  l_keyval_status     BOOLEAN;
  l_coa_id            NUMBER;
begin
  begin
    select chart_of_accounts_id
    into   l_coa_id
    from   gl_sets_of_books
    where  set_of_books_id = fnd_profile.value('GL_SET_OF_BKS_ID');
  exception
    when no_data_found then
      dbms_output.put_line('Chart of Accounts ID not found from profile option GL_SET_OF_BKS_ID');
      dbms_output.put_line('Try setting up your environment with fnd_global.apps_initialize');
      raise;
  end;
  -- keyval_mode can be one of CREATE_COMBINATION CHECK_COMBINATION FIND_COMBINATION
  -- create will only work if dynamic inserts on and cross validation rules not broken
  l_keyval_status := fnd_flex_keyval.validate_segs(
                                           'CREATE_COMBINATION',
                                           'SQLGL',
                                           'GL#',
                                           l_coa_id,
                                           p_concat_segs,
                                           'V',
                                           sysdate,
                                           'ALL', NULL, NULL, NULL, NULL,
                                           FALSE,FALSE, NULL, NULL, NULL);
  if l_keyval_status then
    return 'S';
  else
    return 'F';
  end if;
end create_ccid;
------------------------------------------------------------------------------------------------------------
declare
  l_status varchar2(1);
begin
  l_status := create_ccid('1-9999-9999-9999-9999');
  dbms_output.put_line('Returned: ' || l_status);
  commit;
end;
------------------------------------------------------------------------------------------------------------
select gcc.code_combination_id
,      gcc.concatenated_segments
,      gcc.enabled_flag
,      gcc.start_date_active
,      gcc.end_date_active
,      gcc.detail_posting_allowed
,      gcc.detail_budgeting_allowed
from   gl_code_combinations_kfv gcc
where  concatenated_segments = '1-9999-9999-9999-9999';
------------------------------------------------------------------------------------------------------------
Here Operation can be :

'FIND_COMBINATION' - Combination must already exist.
'CREATE_COMBINATION' - Combination is created if doesn't exist.
'CREATE_COMB_NO_AT' - same as create_combination but does not use an autonomous transaction.
'CHECK_COMBINATION' - Checks if combination valid, doesn't create.
'DEFAULT_COMBINATION' - Returns minimal default combination.
'CHECK_SEGMENTS' - Validates segments individually.

If the above function returns FALSE then use fnd_flex_keyval.error_message() to get the exact FND FLEXFIELD error message to work on.
Another very important function - fnd_flex_keyval.combination_id() returns the ccid of the newly created code combination by the above API.

2. fnd_flex_keyval.validate_ccid():
------------------------------------------------------------------------------------------------------------
Begin
name VARCHAR(2000) := NULL;
result BOOLEAN := TRUE;
begin
IF :from_locator_id IS NULL THEN
RETURN '';
END IF;
result := FND_FLEX_KEYVAL.validate_ccid(appl_short_name=>'INV',
                                                                        key_flex_code=>:P_LOCATOR_FLEX_CODE,
                                                                        structure_number=>:LP_STRUCTURE_NUM,
                                                                        combination_id=>:from_locator_id,
                                                                        data_set=>:ORGANIZATION_ID);

IF result THEN
name := FND_FLEX_KEYVAL.concatenated_values;
END IF;
dbms_output.put_line(name);
end;

------------------------------------------------------------------------------------------------------------
FND_FLEX_KEYVAL.concatenated_values is the API used to concatenate segments of code combination id which was passed in the validate_ccid API.