Oracle EBS: Hiding Import Spreadsheet/Export Spreadsheet in Oracle Internet Expenses

So your company wants to implement a web-only version of Oracle Internet Expenses (i.e. no off-line completing expenses in Excel). The problem is that whenever a user logs into OIE they are presented with three buttons; Create Expense Report, Import Spreadsheet, and Export Spreadsheet (see below)

Figure 1: Expense Report Buttons (default view)

You'll notice that there are a lot of "personalize" links in Figure 1. In order to turn on personalisation you need to following the instructions in my "Changing Justification to Description" blog to update the system profile setting (click here).

In order to start the Personalization process click "Personalize Page" (top right):

Figure 2: Personalize Page

This screen shows you all the options you have when personalizing the screen. There are a lot of them. In order to find the one of we want to alter press Ctrl-F (Internet Explorer/ Firefox "Find") and enter "Import" as shown below:

Figure 3: Internet Explorer Find Dialog

After you click "Next" it will jump to the bottom of the screen and display:

Figure 4: Button Settings

As you can probably just make out from the screen shot (isn't the resizing in Blogger terrible?!) there are options for two buttons; "Button: Import Spreadsheet", and "Button: Export Spreadsheet". Click on the little pencil (edit) in the first line:

Figure 5: Available Personalization Options

Whilst this screen looks complicated it really isn't. What you basically have is options down the left side and across the top you have permission levels. You can change the settings just for you, for all users of your company, for all users of the site, for all users of the system, etc. Wherever you see "Inherit" the option is "use the default".

The bit we need to change is "Rendered". At the moment this is set to "true", we need to change this to "false". However, when you make the change a "warning" will appear that is usefully titled "Error" (just to really worry you):

Figure 6: Error when Changing Rendered Property (really a warning)

What this telling you is that if there are any children for the object you are making invisible they will be made invisible to. For buttons there aren't any children so this error is really just to put the wind up you and doesn't really have any effect.

Now you've made the change for this button, make the change for the other button and then go back to the Expenses page and as if by magic both buttons will be gone.

Oracle EBS: Changing "Justification" to "Description" in Internet Expenses

Now this is a pretty obscure one, I'd be amazed if there were too many people out there who had this problem!

Basically the issue is that I work for a company that rather than having the traditional pyramid structure of management with the people at the top never talking to anyone we have a much flatter structure and if you are brand new in through the door and fancy talking to the MD about something ... then that's fine (even encouraged).

In light of this different culture people who tested our i-Expenses system commented that the word "Justification" wasn't really in keeping with the companies culture. We also didn't require a "justification" to make an expense claim, but a Description would sure help us authorise it. It's a fairly simple change so why not?!

First of all you need to login and select the "System Administrator" responsibility, then go into "Profiles > System" and query for "Personalize Self-Service Defn%". By default this is set to "No", you need to set it to "Yes" (and remember to switch it off after you're done!):

Figure 1: Setting System Profile Options

Next you need to log out and choose the "Internet Expenses" responsibility and then go into the website. You'll notice that there is a "Personalize" link at the the top right of the page and various other links through the website. You can use these to change the way the page displays. You can also use these to make the site completely unusable - so be careful!

The "Description" we are trying to change is on the "Receipt-based Expenses" page. So you need to create a new expense claim and click through to that page (we won't be submitting, so it doesn't matter what you enter). Your screen will look something like this:

Figure 2: Cash and Other Expenses Page

A new link has appeared above the table beginning "Personalize" ... Clicking this link gets you to the field you need to edit quicker, but clicking any link and drilling down will work just as well (well, I'm assuming here ...!).

Now you will need to move down the list of options until you find "Message Text Input: Justification", then click the pencil (edit) to the right:

Figure 3: Editing "Justification"

Clicking on the pencil will display the following screen:

Figure 4: Changing Text

I have highlighted the entry boxes to change "Justification" for your entire organisation. If you just want to change it for your site (or even just for you) then use the boxes immediately to their left.

Clicking on the circular arrow next to the entry boxes restores them to "Inherit" which in effect deletes your change.

NOTE: When you look for these items again (say you wanted to change back to "Justification") then in Figure 3 where it shows "Justification" it would now show "Description".

Oracle EBS: Editing the FA Account Generator Process (Oracle Financials)

1. Editing Workflows using Oracle Workflow BuilderInstall "Oracle Workflow Builder" from the Oracle.com website. The version you will need depends on the version of Oracle Applications you are currently using. If you are unsure which version to use you can always raise a Service Request (SR) via Metalink and ask Oracle.

To open the workflow select “File > Open” from the menu:

Figure 1: Oracle Workflow Builder, Open Dialog

User and password are for the Oracle APPS account.

If you want a version of the Workflow other than the current version you can enter a date in the “Effective” entry box and it will give you the version of the workflow that was in effect on that date. Unless you’re doing a rollback to a previous version you can leave this blank.

Clicking “OK” will connect to the database and then display the “Show Item Types” dialog:

Figure 2: Oracle Workflow Builder, Show Item Types

The Workflow to edit is called “FA Account Generator”, in order to edit it you need to highlight it in the right-hand list (Hidden) and move it into the left-hand list (Visible) – as shown in Figure 2. Once you’ve selected it click “OK”.

NOW THIS IS IMPORTANT: If you work on a workflow while connected to the database any change you make is immediately committed to the server. As soon as you have opened a Workflow for editing you should save the Workflow locally and work on the local copy rather than working against a database (you can then open the local file and save it back to the database when you’re done).

The Workflow builder uses an MDI interface, a window will open titled “Navigator”:

Figure 3: Oracle Workflow Builder, Navigator

All the items shown in the figure above (beginning "Generate ...") are the workflows provided by Oracle. You should never edit these, if you do there is a risk (well, a practical certainty) of them being overwritten during an upgrade. If you need to make changes to a standard item (i.e. the process “Generate Book Level Account” in Figure 3) then you must create a copy of that item by right-clicking it, selecting “Copy” and then “Paste”. When prompted to enter the details prefix the name with a short-code, for example your company name or initials (there are length limitations, if you go over the length just remove characters from the end until it fits).

Fixing problems with the “Generate Accounts” process requires that you open (double-click) the “XXXX Generate Book Level Account” process (where XXXX is your chosen prefix). The original version (i.e. the “Generate Book Level Account” process) is below:

Figure 4: Generate Book Level Account Process

As you can see the "out of the box" process is fairly straight forward. The most common changes are usually to create "custom" branches coming off the "Get Book Account Name" process (where it shows "" above the arrow). Looking at the company I currently work for our customised process is:

Figure 5: Customised Generate Book Level Account Process

This shows separate flows for Proceeds of Sale (Loss/ Gain/ Clearing), Cost of Removal (Loss/ Gain/ Clearing) and Net Book Value Retired (Gain/ Loss). If you check the first “Assign to Value …” in each of the three flows you’ll see that these route to hard-coded constants:

Figure 6: Process Flow Hard-Coded Values (009) .

If a new branch needs to be created then you will need to copy most of the values from an existing branch (unfortunately there is no “copy/paste” for processes so you need to create new processes by dragging and dropping from the navigator and then set their properties).

2. Configuring Apps to use the New Workflow
After the work flows have been altered and you have uploaded them into Apps you need to configure Oracle E-Business Suite to stop using the Default workflow and switch to the customised version.

To do this you need to connect to Apps and switch to the “Fixed Assets Manager” responsibility. Expand the Financials > Flexfields > Key option in the tree view and select “Accounts” (see below):

Figure 7: Showing the Account Generation Options

Once you have selected this you are presented with an empty grid:

Figure 8: Account Generator Processes

Click on the “Find” button on the toolbar and search for “General Ledger%” and click “Find”. Select the entry with the correct structure (usually the company name)

Click “OK”.

The main Account Generator Process window will now populate with a list of Item Types and Processes. Scroll down and highlight the “FA Account Generator” Item Type:

Figure 9: Selecting the FA Account Generator

Click on the “…” (in the Process Name field) and you are presented with a list of processes from your workflow. Select the correct process (most likely “XXXX Generate Default Account”) and then click the save button on the toolbar.

Oracle PL/SQL: Searching All VARCHAR2 Fields In A Schema

How frustrating is that? I've entered the data into the Oracle Front-end, the workflow has kicked off, an email has been sent, and *somewhere* in all this mess is a record of an error e-mail being received.

After 4 hours of trying to track down the problem (we actually have 3 emails being received - all "address not found") every 3 minutes ... for the past month and a half. We've got 48,000 emails in the Inbox at the moment. It takes 15 minutes to open in Outlook.

Where do you start?

Now I know the email address that is being used. I also know that the problem persists despite the server being taken down (for backup) every week therefore it *must* be stored somewhere in the database.

The following script runs through every single table in the database that contains a VARCHAR2 column and tries to find a specific string. It takes a while against an Oracle 11i schema (best to leave overnight ... maybe over a weekend if you have that many modules installed!).

declare 
  c_SEARCHTEXT constant varchar2(255) := 'SEARCH TEXT GOES IN HERE';

  cursor c_Tables is 
    select distinct atc.owner, atc.table_name
    from all_tab_columns atc
    where data_type = 'VARCHAR2'
    and DATA_LENGTH >= length(c_SEARCHTEXT)
    and not exists (select 'X' from all_views av where av.owner = atc.owner and av.view_name = atc.table_name);
  
  cursor c_Columns (p_Owner varchar2, p_TableName varchar2) is
    select distinct column_name
    from all_tab_columns
    where owner = p_Owner
    and data_type = 'VARCHAR2'
    and DATA_LENGTH >= length(c_SEARCHTEXT)
    and table_name = p_TableName;
  
  TYPE cv_typ IS REF CURSOR;
  cv cv_typ;
  record_count integer;
  v_SQL varchar2(8124);
begin
  for v_Table in c_Tables loop
    for v_Columns in c_Columns(v_table.owner, v_table.table_name) loop
      v_SQL := 
        'select count(*) ' || chr(13) ||
        'from ' || v_table.owner || '.' || v_Table.table_name || chr(13) ||
        'where upper(' || v_Columns.column_name || ') like upper(''%' || c_SEARCHTEXT || '%'')' || chr(13);
      open cv for
        v_SQL;
      fetch cv into record_count;
      if record_count > 0 then
        dbms_output.put_Line(v_Table.table_name || '.' || v_Columns.column_name || '***** FOUND *****');
      end if;
      close cv;
    end loop;
  end loop;
end;

Now this is unoptimised so it will be sloooooooow. You can always change the initial select to prioritise schemas you are interested in, or add in a "length" check to make sure data of the correct length exists, but the biggest saving will be replacing the "dbms_output" call with something that will send you e-mail messages when it finds something (rather than waiting until the end when it's done!).

Oracle PL/SQL: Using Dynamic SQL to Build an INSERT ... INTO Statement From Any Query

At the moment I'm writing test script for an Oracle Internet Expenses implementation. A fairly simple need has arisen to take the result of a SELECT ... FROM statement and convert it into an INSERT ... INTO - basically this will allow some of the tests to be repeatable (i.e. they are creating users, assigning responsibilities, etc).

The following PL/SQL script generates insert statements using the standard DBMS_OUTPUT package:

declare
  -- Script to convert a SQL Statement into an INSERT statement 
  -- (useful for generating test scripts)

  v_Spacing     varchar2(10) := '  '; -- used to split "levels" in SQL
  v_Table       all_tab_cols.table_name%TYPE := upper('wf_local_user_roles');
  v_Owner       all_tab_cols.owner%TYPE := upper('APPLSYS');
  v_WhereClause varchar2(2048) := 'where user_name = '''' and role_orig_system_id = 22918';
  v_QuerySQL    varchar2(2048);
  v_Result      varchar2(512);
  v_RowID       ROWID;

  v_ColumnCount number := 0;

  TYPE ref_cur_typ IS REF CURSOR;
  ref_cur  ref_cur_typ;
  data_cur ref_cur_typ;

  cursor c_Columns is
    select atc.column_name, atc.data_type
      from all_tab_cols atc
     where atc.owner = v_Owner
       and atc.table_name = v_Table
     order by atc.column_id;
begin
  v_QuerySQL := 'select ROWID from ' || v_Owner || '.' || v_Table || ' ' ||
                v_WhereClause;

  open ref_cur for v_QuerySQL;
  loop
    -- Get the ROW ID (unique identifier) for each row we wish to add as an insert
    fetch ref_cur
      into v_RowID;
    EXIT WHEN ref_cur%NOTFOUND;
    dbms_output.put_line(v_Spacing || 'insert into ' || v_Owner || '.' ||
                         v_Table);
    dbms_output.put_line(v_Spacing || 'select');
    v_ColumnCount := 0;
    for v_Column in c_Columns loop
      -- Loop through the columns in the table, for each row
      v_ColumnCount := v_ColumnCount + 1;
      if v_Column.data_type in ('VARCHAR2', 'CHAR') then
        v_QuerySQL := 'select ' || v_Column.Column_Name || ' from ' ||
                      v_Owner || '.' || v_Table || ' where rowid = ''' ||
                      v_RowID || '''';
        open data_cur for v_QuerySQL;
        fetch data_cur
          into v_Result;
        close data_cur;
      
        dbms_output.put(v_Spacing || v_Spacing);
        if v_ColumnCount > 1 then
          dbms_output.put(',');
        end if;  
        dbms_output.put_line('''' || v_Result || '''');
      elsif v_Column.data_type in ('FLOAT', 'NUMBER') then
        v_QuerySQL := 'select to_char(' || v_Column.Column_Name || ') from ' ||
                      v_Owner || '.' || v_Table || ' where rowid = ''' ||
                      v_RowID || '''';
        open data_cur for v_QuerySQL;
        fetch data_cur
          into v_Result;
        close data_cur;
      
        dbms_output.put(v_Spacing || v_Spacing);
        if v_ColumnCount > 1 then
          dbms_output.put(',');
        end if;  
        dbms_output.put_line(v_Result);
      elsif v_Column.data_type in ('DATE') then
        v_QuerySQL := 'select to_char(' || v_Column.Column_Name || ', ''DD-MON-YYYY HH24:MI:SS'') from ' ||
                      v_Owner || '.' || v_Table || ' where rowid = ''' ||
                      v_RowID || '''';
        open data_cur for v_QuerySQL;
        fetch data_cur
          into v_Result;
        close data_cur;
      
        dbms_output.put(v_Spacing || v_Spacing);
        if v_ColumnCount > 1 then
          dbms_output.put(',');
        end if;  
        dbms_output.put_line('to_date(''' || v_Result || ''', ''DD-MON-YYYY HH24:MI:SS'')');
      end if;
    end loop;
    dbms_output.put_line(v_Spacing || 'from dual;');
  end loop;
  close ref_cur;
end;

This will only handle tables where all the columns are of one of the specified data types (DATE, VARCHAR2, NUMBER, etc). The resulting insert statements are written to the standard output channel so if you have a lot of records you might want to enlarge it beyond the 10,000 character (or so) default!

Oracle PL/SQL: Stripping Comments From PL/SQL Packages

Now I've worked in several places with different coding policies. Some have said "comment your code where the meaning isn't clear" (which makes sense) and others have said that your code should include a (commented out) complete history of changes. Clearly if you adopt the second approach after a few years and several changes your code is going to go from being 90% code 10% comments to 90% comments 10% code.

It's at that stage you change your policy and switch to using a source control system to track changes - but what to do with code?!

I'm a big fan of the "start again" approach and so I wrote this little PL/SQL routine to find the source code of a package body in Oracle and remove *almost* all the comments. The comments it will keep are those that exist on a line of code. For example if we have the code block;

/* This is a standard block
comment */
-- prepare to increment loop counter
v_Int := v_Int + 1; -- increment loop counter

Then this routine will strip out the block comment and the point where the line starts with "--" but *not* the comment that comes after the line of code (the -- increment ...).

Of course you can tailor this to your heart's content.

Now, a little note on execution. I use PL/SQL Developer from All Round Automations to do my editing. This has a nice feature called a "Test Window". This allows you to pass parameters to/from a script. I've used this feature with this script to generate a script that populates two parameters, one with the original source code and the other with the "edited" version. If you don't use PL/SQL developer you'll need to find some other way of achieving this.

Anyway, here is the script, you'll need to replace &XXXXX with your package name:

declare 
  -- Local variables here
  v_CharNo number := 1;
  v_CharCount number;
  v_InComment boolean;
  v_AddChar boolean;
  v_SourceCode clob;
  v_Chars varchar2(2);
  
  v_Text all_source.text%TYPE;
  
  cursor c_GetSource is
    select text
    from all_source
    where name = '&XXXXX'
    and type = 'PACKAGE BODY';
    procedure addToCLOB(v_Text in varchar2) as
    begin
      dbms_lob.writeappend(v_SourceCode, length(v_Text), v_Text);
    end;
begin
  -- Test statements here
  dbms_lob.createtemporary(lob_loc => v_SourceCode, cache => False);
      
  :old_data := '';
  for v_Line in c_GetSource loop
    v_Text := trim(v_Line.Text);
    if substr(v_text, 1, 2) != '--' then
      addToCLOB(v_Line.Text);
    end if;
  end loop;
  :old_data := v_SourceCode;
    
  v_CharCount := length(:old_data);
  v_InComment := False;
  while v_CharNo <= v_CharCount loop
    if (not v_InComment) and (substr(:old_data, v_CharNo, 2) = '/*') then
      v_InComment := True;
    end if;
    
    v_AddChar := not v_InComment;
    if v_AddChar then
      :new_data := :new_data || substr(:old_data, v_CharNo, 1); 
    end if;
    if (v_InComment) and (substr(:old_data, v_CharNo-2, 2) = '*/') then
      v_InComment := False;
    end if;
    v_CharNo := v_CharNo + 1;
  end loop;
end;

Pretty simple stuff, if you have any questions drop me a comment ...

Oracle EBS: Scripting the Creation of Event-based Oracle Alerts in Oracle e-Business Suite

This was possibly one of the hardest things I've ever had to do in a long time. Not because it's technically challenging, but because there was just not enough information out there. If you search for Oracle Alerts you can see that using the FND_LOAD package there are a couple of example of how to migrate alerts from one server to another ... But not really anything on scripting their creation from scratch. You know you're in trouble when a Google search for some of the API's you're using returns no results. Naturally Oracles Metalink provided less than nothing, although someone called Margaret helped a great deal in relation to the SR I raised during this work!

Ok, a bit of background first. The aim here is to, using a PL/SQL script, create an Event-based Oracle Alert on a custom table within the Oracle database. The reasoning behind this is that I work in the Pharmaceutical industry and we use Oracle Manufacturing. Needless to say when you're dealing with drugs you can't have "random" changes being made to your database. Our environment is very tightly controlled.

At the moment we have a production server and five test/ development servers. We actually have an excellent cloning process that means if we need another copy from production it takes about a day to do.

The purpose behind this work was to enable us to write a script to implement an Oracle Alert that we could develop on our development instance, then run on our test instance and confirm it works before finally running it against production.

At the moment we are implementing Oracle Internet Expenses and are on our third refresh of our development server. so there will also be some time saving should we go onto our fourth or even fifth refresh!

Without this script we would have had to go in and make manual changes in the Oracle UI. This introduces human error and is very time consuming.

History lesson over, let's make a start.

First off let's assume we have the table CCL (stands for Credit Card Loader if anyone cares) which is created using the following script:

create table CCL
(
  PROCESS_ID NUMBER not null,
  LINE_ID    NUMBER not null,
  TEXT       VARCHAR2(2048) not null,
  VALID_BOO  VARCHAR2(1) default 'T'
);

This receives a credit card transaction in a Barclaycard feed (it's part of our i-Expenses implementation) - each line in the file is inserted into this table. A trigger populates the VALID_BOO with either T or F depending on the data in TEXT.

In order to register this table with Oracle applications (we're going to register it as part of payables - the same as Internet Expenses) use this script to perform the registration. The script uses Oracles AD_DD package (REGISTER_TABLE, REGISTER_COLUMN) to make APPS aware of the new table.

The key things for you to change to get this to work for you are both variables at the top of the file. v_TableName is the name of the table you wish to add, and v_AppShortName is the short name for the application (look in fnd_application_tl to get the application ID for your application, and then lookup the short code in fnd_application - Payables is SQLAP).

Whilst the script does remove existing registered columns if you change the delete a column from the table and then re-run the script you'll find yourself at the mercy of whatever it is Oracle does with the orphaned record ... Maybe delete? Maybe leave ... who knows? I for one have not tested that!

To keep this as simple as possible at this stage you need to create a concurrent request. It doesn't matter what it is just that you call it "CCL" and make sure it takes no parameters - you can do anything you like. Me personally I'd create one that writes a random record into a log table of some sort. Anyway, that part is down to you.

Next we use the ALR_ALERTS_PKG package to create an alert;

alr_alerts_pkg.load_row(x_application_short_name       => 'SQLAP',
                          x_alert_name                   => 'CCL_NEW',
                          x_owner                        => null,
                          x_alert_condition_type         => 'E',
                          x_enabled_flag                 => 'Y',
                          x_start_date_active            => sysdate,
                          x_end_date_active              => null,
                          x_table_application_short_name => 'SQLAP',
                          x_description                  => 'Starts the second credit card loading process after a successful load',
                          x_frequency_type               => 'O',
                          x_weekly_check_day             => null,
                          x_monthly_check_day_num        => null,
                          x_days_between_checks          => null,
                          x_check_begin_date             => null,
                          x_date_last_checked            => null,
                          x_insert_flag                  => 'Y',
                          x_update_flag                  => 'N',
                          x_delete_flag                  => null,
                          x_maintain_history_days        => 0,
                          x_check_time                   => null,
                          x_check_start_time             => null,
                          x_check_end_time               => null,
                          x_seconds_between_checks       => null,
                          x_check_once_daily_flag        => null,
                          x_sql_statement_text           => 'select process_id into &PROCESSID from ccl where text like ''TRLR%'' and rowid = :ROWID',
                          x_one_time_only_flag           => null,
                          x_table_name                   => 'CCL',
                          x_last_update_date             => null,
                          x_custom_mode                  => null);

Now this calls the LOAD_ROW package (rather than INSERT_ROW) because if the row already exists this will update it.

As you can see the huge bulk of values inserted are null, these are used mostly for periodic alerts rather than event-driven alerts. The values passed in are pretty self-explanatory - if you are lost for some of these values then you can always read this whole document, create the alert manually in Oracle (as the Alert Manager responsibility) and then look in the corresponding table and see what the values should be. Oracle have stuck with a fairly clear naming convention for these packages; ALR_ALERTS_PKG will manage data in the ALR_ALERTS table.

NB: The most likely causes of errors running this code are due to the table application short name and/or table being incorrect.

As far as I'm aware these next steps can be carried out in any order (but I have only tested them in the order they're listed here).

Setting up Alert Installations

This uses the following API;

  alr_alert_installations_pkg.load_row(x_application_short_name => 'SQLAP',
                                       x_alert_name => 'CCL_NEW',
                                       x_oracle_username => 'APPS',
                                       x_data_group_name => '&XXXX',
                                       x_owner => null,
                                       x_enabled_flag => 'Y',
                                       x_last_update_date => null,
                                       x_custom_mode => null);

This is very important, it took me quite a while to work out why my alerts weren't firing and it ended up being due to not having added the APPS user as an Alert Installation. In the example code above where it says "&XXXX" you need to specify the group name for the APPS login.

Now this ONLY work if you have the APPS environment initialised for the login. You can do this by executing the command;

  APPS.FND_GLOBAL.APPS_INITIALIZE(user_id      => 102,
                                    resp_id      => 2304,
                                    resp_appl_id => 17);

You'll need to specify your own values for user, responsibility id and responsibility application id. if everything else looks ok (and you can see the Check Event Alert Concurrent Request starting but not your request) then this is likely the cause of your problem.

Next use the ALR_ACTIONS_PKG package to create an action. This will be to run the concurrent request we configured earlier.

alr_actions_pkg.load_row(x_application_short_name   => 'SQLAP',
                           x_alert_name               => 'CCL_NEW',
                           x_action_name              => 'CCL_CONC_REQ',
                           x_action_end_date_active   => null,
                           x_owner                    => null,
                           x_action_type              => 'C',
                           x_enabled_flag             => 'Y',
                           x_description              => 'Run a concurrent request',
                           x_action_level_type        => 'D',
                           x_date_last_executed       => null,
                           x_file_name                => null,
                           x_argument_string          => null,
                           x_program_application_name => 'SQLAP',
                           x_concurrent_program_name  => 'CCL',
                           x_list_application_name    => null,
                           x_list_name                => null,
                           x_to_recipients            => null,
                           x_cc_recipients            => null,
                           x_bcc_recipients           => null,
                           x_print_recipients         => null,
                           x_printer                  => null,
                           x_subject                  => null,
                           x_reply_to                 => null,
                           x_response_set_name        => null,
                           x_follow_up_after_days     => null,
                           x_column_wrap_flag         => 'Y',
                           x_maximum_summary_message  => null,
                           x_body                     => null,
                           x_version_number           => 1,
                           x_last_update_date         => null,
                           x_custom_mode              => null);

As you can see all the options are here, again it might be easier if you created your alert in the database and then queried the tables directly to find the necessary values for all these API calls. the key things to note are that action type "C" is Concurrent Request and action level type "D" is Detail (as you would see in the GUI).

Next you need to create an action set for your action. This is done using the ALR_ACTION_SETS_PKG package;

  alr_action_sets_pkg.load_row(x_application_short_name    => 'SQLAP',
                               x_alert_name                => 'CCL_NEW',
                               x_name                      => 'CCL_REQSET',
                               x_owner                     => null,
                               x_end_date_active           => null,
                               x_enabled_flag              => 'Y',
                               x_recipients_view_only_flag => 'N',
                               x_description               => 'Request set for CCL_NEW',
                               x_suppress_flag             => 'N',
                               x_suppress_days             => null,
                               x_sequence                  => 1,
                               x_last_update_date          => null,
                               x_custom_mode               => null);

Now you have an alert, an action and an action set you can configure the outputs for all three together using these three API calls. Note that in the SQL for the alert I made use of a PROCESSID output value, these calls correctly register this with the system.

  alr_action_outputs_pkg.load_row(x_application_short_name => 'SQLAP',
                                  x_alert_name             => 'CCL_NEW',
                                  x_action_name            => 'CCL_CONC_REQ',
                                  x_action_end_date_active => null,
                                  x_action_out_name        => 'PROCESSID',
                                  x_owner                  => null,
                                  x_critical_flag          => 'N',
                                  x_end_date_active        => null,
                                  x_last_update_date       => null,
                                  x_custom_mode            => null);

  alr_alert_outputs_pkg.load_row(x_application_short_name => 'SQLAP',
                                 x_alert_name             => 'CCL_NEW',
                                 x_name                   => 'PROCESSID',
                                 x_owner                  => null,
                                 x_sequence               => 1,
                                 x_enabled_flag           => 'Y',
                                 x_start_date_active      => sysdate,
                                 x_end_date_active        => null,
                                 x_title                  => 'PROCESSID',
                                 x_detail_max_len         => null,
                                 x_summary_max_len        => null,
                                 x_default_suppress_flag  => 'Y',
                                 x_format_mask            => null,
                                 x_last_update_date       => null,
                                 x_custom_mode            => null);

  alr_action_set_outputs_pkg.load_row(x_application_short_name => 'SQLAP',
                                      x_alert_name             => 'CCL_NEW',
                                      x_name                   => 'CCL_REQSET',
                                      x_action_set_output_name => 'PROCESSID',
                                      x_owner                  => null,
                                      x_sequence               => 1,
                                      x_suppress_flag          => 'Y',
                                      x_last_update_date       => null,
                                      x_custom_mode            => null);

Next now the outputs are configured we can setup the action set members;

  alr_action_set_members_pkg.load_row(x_application_short_name => 'SQLAP',
                                      x_alert_name             => 'CCL_NEW',
                                      x_name                   => 'CCL_REQSET',
                                      x_owner                  => null,
                                      x_action_name            => 'CCL_CONC_REQ',
                                      x_group_name             => null,
                                      x_group_type             => null,
                                      x_sequence               => 1,
                                      x_end_date_active        => null,
                                      x_enabled_flag           => 'Y',
                                      x_summary_threshold      => null,
                                      x_abort_flag             => 'A',
                                      x_error_action_sequence  => null,
                                      x_last_update_date       => null,
                                      x_custom_mode            => null);

Now this is the alert setup and ready to go - you can go into the Oracle GUI and check and everything should be there. Unfortunately it's possible for some of these calls to "silently" fail so it's necessary to perform this basic validation.

The final thing that's missing is the system-generated trigger on the CCL table that will fire off the alert. This API uses a different format than all the others so you need to put together a block of code;

declare
    cursor c_AlertDetails is
      select a.application_id,
             a.alert_id,
             a.table_application_id,
             a.table_id
        from applsys.alr_alerts a
       where a.alert_name = 'CCL_NEW';
  begin
    dbms_output.put_line('Creating a tigger on CCL for the alert (on insert) ...');
    for v_Alert in c_AlertDetails loop
      alr_dbtrigger.create_event_db_trigger(appl_id     => v_Alert.application_id,
                                            alr_id      => v_Alert.alert_id,
                                            tbl_applid  => v_Alert.table_application_id,
                                            tbl_name    => 'CCL',
                                            oid         => null,
                                            insert_flag => 'Y',
                                            update_flag => 'N',
                                            delete_flag => 'N',
                                            is_enable   => 'N');
    end loop;
    dbms_output.put_line('... Done');
  end;

If you query the database using PL/SQL Developer, TOAD, etc then you'll see the trigger is now available on the table.

A quick test;

begin
  -- Test statements here
    APPS.FND_GLOBAL.APPS_INITIALIZE(user_id      => 102,
                                    resp_id      => 2304,
                                    resp_appl_id => 17);
  delete from ccl where process_id = 41 and line_id = 3804;
  insert into ccl (process_id, line_id,text)
  values (41, 3804, 
    'TRLR{');
  commit;
end;

If you log in as a System Administrator and look at concurrent requests you should see the CCL (Check Event Alert) start up and then a few second later your concurrent request should start to run.

Should you fancy looking at my script it's available here.

Hope this saves you the 2 days it took me to get this working!

NOTE: You may freely take my scripts and do whatever you want with them, so long as you don't try and pin anything they do (or don't do) on me.