Friday, August 03, 2007

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.

5 comments:

Arivazhagan said...

Thanks for this post. How to trap the exceptions for this one?
I'm using FND_MESSAGE.get_encoded and only get following message "ORA-0000: normal, successful completion"

Regards
Arivazhagan

Gareth said...

Hi Arivazhagan, you should be able to trap oracle error 0000 with a pragma, and when clause, and ignore it.

Regards,
Gareth

Arivazhagan said...

Hi Gareth
Thanks for the update. I was actually passing an Invalid value(not defined in the valueset) for one of the segments. When passing the correct values, i am able to create the code combination successfully

Regards
Arivazhagan

NTU MJ said...
This comment has been removed by the author.
NTU MJ said...

Hi Gareth,

came across this post and tried it out.

I keep getting "Invalid Column Index" whenever i execute.

Regards,
Tiffany