Account Generator Custom Error Messages

One of my top 8 Oracle Applications (eBiz) developer hooks is workflow.

Most commonly workflow is used for Approvals and Account Generators.

The Account Generators are very specific little critters with a couple of subtleties, one of which is how they pass back error messages to the user. It ain't hard, just a straightforward 1-2-3 procedure.

1. Set the Error Message node attribute of the "Abort Generating Code Combination" item attribute "Error Message"
2. Ensure the workflow goes to "Abort Generating Code Combination" when an error occurs in your custom code
3. Make sure your code calls FND_MESSAGE and sets the ERROR_MESSAGE item attribute (internal identifier) to fnd_message.get_encoded

Lets do it for the Purchase Order (PO) Account Generator POWFPOAG, process Generate Default Charge Account.

1. Abort Generating Code Combination Error Message attribute.

• Open up the PO Account Generator in Workflow Builder
• Navigate to and open process "Generate Default Charge Account".
• Find and right click to get properties of "Abort Generating Code Combination" after the flow from Generate Default Expense AccountClick the Node Attributes tab
• Select Error Message from the Name dropdown
• Choose Type Item Attribute
• Choose Value "Error Message". NB: This is not in alphabetical order.
• Save

2. and 3. Code the "Failure" and set the message in your custom code.

In your custom procedure that generates the account, ensure you have code that looks something like the following code. Note we "borrow" the existing generic FND message ERROR_MESSAGE to save having to create a new message and generate message files. Also note that in this specific example we are generating the account based on some component of the Employee's default expense account, which if not setup correctly will set success = 'N'. Of course your logic will be different here, and I don't recommend multiple returns in a function, but this code is presented for simplicity.

if (success = 'N') then
 FND_MESSAGE.set_name('FND', 'ERROR_MESSAGE');
 FND_MESSAGE.set_token('MESSAGE',
   'The PO Account Generator failed to generate the default account. ' ||
   'Please ask your System Administrator to check your ' ||
   'Employee Default Expense Account is setup correctly.');
 wf_engine.SetItemAttrText
         ( itemtype=> itemtype,
           itemkey => itemkey,
           aname   => 'ERROR_MESSAGE',
           avalue  => fnd_message.get_encoded);
 result := 'COMPLETE:FAILURE';
 return;
end if;

All done, test it out! You should see something like the second screenshot when you hit an error.

PS. Persistence and Account Generators showing in Status Monitor

As a side note account generators persistence is "temporary" and also they don't appear under the Status Monitor in Workflow Administrator unless you switch a couple of debug profile options to Yes:

• PO: Set Debug Workflow ON
• Account Generator:Run in Debug Mode

PPS. Show workflows to all users with Workflow Admininstrator

For development and test environments, the following may come in handy to enable ALL users with access to Workflow Administrator Status Monitor to see ALL permanent workflows.

prompt Remember what the value was so we can set it back if needed.
select text
from   wf_resources
where  name = 'WF_ADMIN_ROLE';

TEXT
--------------------------------------------------------------------------------
SYSADMIN

update wf_resources
set    text = '*'
where  name = 'WF_ADMIN_ROLE';

1 row updated.

commit;

Happy Account Generating!

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.

Query: eBiz Setup of Books/Chart of Accounts keyflex segments

Thought I'd post a few of the common queries I run to save some retyping... here's the first! When I want a quick overview of the structure of clients chart of accounts, the following query comes in handy. Shows chart of accounts, set of books, accounting flexfield segments in use and their attributes, value sets for the key flexfield segments. Looks big, but quite a bit of repetition here to get the segment attributes.

select sob.name sob_name
,      sob.set_of_books_id sob_id
,      sob.chart_of_accounts_id coa_id
,      fifst.id_flex_structure_name struct_name
,      ifs.segment_name
,      ifs.application_column_name column_name
,      sav1.attribute_value  BALANCING
,      sav2.attribute_value COST_CENTER
,      sav3.attribute_value NATURAL_ACCOUNT
,      sav4.attribute_value  INTERCOMPANY
,      sav5.attribute_value SECONDARY_TRACKING
,      sav6.attribute_value GLOBAL
,      ffvs.flex_value_set_name
,      ffvs.flex_value_set_id
from   fnd_id_flex_structures fifs
,      fnd_id_flex_structures_tl fifst
,      fnd_segment_attribute_values  sav1
,      fnd_segment_attribute_values sav2
,      fnd_segment_attribute_values sav3
,      fnd_segment_attribute_values  sav4
,      fnd_segment_attribute_values sav5
,      fnd_segment_attribute_values sav6
,      fnd_id_flex_segments ifs
,      fnd_flex_value_sets ffvs
,      gl_sets_of_books sob
where  1=1
and    fifs.id_flex_code = 'GL#'
and    fifs.application_id =  fifst.application_id
and    fifs.id_flex_code = fifst.id_flex_code
and    fifs.id_flex_num = fifst.id_flex_num
and    fifs.application_id =  ifs.application_id
and    fifs.id_flex_code = ifs.id_flex_code
and    fifs.id_flex_num = ifs.id_flex_num
and    sav1.application_id =  ifs.application_id
and    sav1.id_flex_code = ifs.id_flex_code
and    sav1.id_flex_num = ifs.id_flex_num
and    sav1.application_column_name =  ifs.application_column_name
and    sav2.application_id =  ifs.application_id
and    sav2.id_flex_code = ifs.id_flex_code
and    sav2.id_flex_num = ifs.id_flex_num
and    sav2.application_column_name =  ifs.application_column_name
and    sav3.application_id =  ifs.application_id
and    sav3.id_flex_code = ifs.id_flex_code
and    sav3.id_flex_num = ifs.id_flex_num
and    sav3.application_column_name =  ifs.application_column_name
and    sav4.application_id =  ifs.application_id
and    sav4.id_flex_code = ifs.id_flex_code
and    sav4.id_flex_num = ifs.id_flex_num
and    sav4.application_column_name =  ifs.application_column_name
and    sav5.application_id =  ifs.application_id
and    sav5.id_flex_code = ifs.id_flex_code
and    sav5.id_flex_num = ifs.id_flex_num
and    sav5.application_column_name =  ifs.application_column_name
and    sav6.application_id =  ifs.application_id
and    sav6.id_flex_code = ifs.id_flex_code
and    sav6.id_flex_num = ifs.id_flex_num
and    sav6.application_column_name =  ifs.application_column_name
and    sav1.segment_attribute_type =  'GL_BALANCING'
and    sav2.segment_attribute_type = 'FA_COST_CTR'
and    sav3.segment_attribute_type = 'GL_ACCOUNT'
and    sav4.segment_attribute_type  = 'GL_INTERCOMPANY'
and    sav5.segment_attribute_type =  'GL_SECONDARY_TRACKING'
and    sav6.segment_attribute_type =  'GL_GLOBAL'
and    ifs.id_flex_num = sob.chart_of_accounts_id
and    ifs.flex_value_set_id = ffvs.flex_value_set_id
-- comment the next expression to show all books
-- currently it show the info for the site level set profile option value
and    sob.set_of_books_id = 
        nvl(fnd_profile.value('GL_SET_OF_BKS_ID'),sob.set_of_books_id)
order by  sob.name, sob.chart_of_accounts_id, ifs.application_column_name;

And just for good measure here's the sample output from 11.5.10.2 Vision Demo instance.

SOB_NAME                           SOB_ID     COA_ID STRUCT_NAME                    SEGMENT_NAME                   COLUMN_NAME                    B C N I S G FLEX_VALUE_SET_NAME                                          FLEX_VALUE_SET_ID
------------------------------ ---------- ---------- ------------------------------ ------------------------------ ------------------------------ - - - - - - ------------------------------------------------------------ -----------------
Vision Operations (USA)                 1        101 Operations Accounting Flex     Company                        SEGMENT1                       Y N N Y N Y Operations Company                                                     1002470
Vision Operations (USA)                 1        101 Operations Accounting Flex     Department                     SEGMENT2                       N Y N N N Y Operations Department                                                  1002471
Vision Operations (USA)                 1        101 Operations Accounting Flex     Account                        SEGMENT3                       N N Y N N Y Operations Account                                                     1002472
Vision Operations (USA)                 1        101 Operations Accounting Flex     Sub-Account                    SEGMENT4                       N N N N N Y Operations Sub-Account                                                 1002473
Vision Operations (USA)                 1        101 Operations Accounting Flex     Product                        SEGMENT5                       N N N N N Y Operations Product                                                     1002474

Nice.