GL Account Code Combinations on the fly: Key Flexfield API

Every once in a while I've had the need to validate code combinations, and dynamically create them on the fly. With a properly structured Account Generator workflow, you can just throw in the "Validate Code Combination" function from Standard Flexfield Workflow. But what about a nice neat, keep it all in PL/SQL scenario? Well, it ain't too hard, here's some code for the General Ledger Accounting Flexfield, shouldn't be too hard to adapt for other key flexfields. Remove the dbms_output calls and adjust as you need:

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  
    dbms_output.put_line('Success');
    return 'S';
  else
    dbms_output.put_line('Error message: ' || fnd_flex_keyval.error_message);
    return 'F';
  end if;
end create_ccid;
/

Gotta have an example of its use, even easier:

set serverout on size 1000000
declare
  l_status varchar2(1);
begin
  l_status := create_ccid('1-9999-9999-9999-9999');
  dbms_output.put_line('Returned: ' || l_status);
  commit;
end;
/

And of course remember to COMMIT; If you need to query back what you just created, how about:

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';

PS. Check out my posts on the GL Account Structure and fnd_global.apps_initialize if necessary.