From Gareth Roberts post.
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
return 'S';
else
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 posts on the
GL Account Structure and
fnd_global.apps_initialize if necessary.
