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.
No comments:
Post a Comment