When you use Oracle Alert, a module from the Oracle EBS, you probably work with custom alerts next to the standard alerts provided by Oracle.
When working with custom alerts, their will be changes to these alerts and those changes must also be applied to your production environment. Once you have tested the new/adjusted Alert, Oracle offers you a way to transfer the new/adjusted Alert from your test database to your production database. When signing in the Oracle Alert module as the Alert manager, there is a function called â€˜Transfer Alertâ€™, which allows you the transfer an Alert from one database to the other. This transfer works with a database link.
There are a few disadvantages when using this method to transfer Alerts to the production database.
- There is no version administration, you do not know what version is in the production database.
- What if somebody is changing the Alert in the Test environmentâ€¦â€¦then this change is also transferred to production.
- Before uploading an Alert, you must first delete the Alert in the target database
I was asked to find out if it was possible to transfer these alerts using the FNDLOAD tool from Oracle.
FNDLOAD is a concurrent program that can move Oracle Applications data between database and text file. FNDLOAD can download data from an application entity into an editable text file, which can be uploaded to another database. Conversion between database format and text file format is specified by a configuration file. But i could not find anything regarding upload/download of an Oracle Alert. So, my conclusion was that i must be possible to use FNDLOAD to transfer Alerts, but that there is no configuration file provided by Oracle. I had to create a configuration file myself.
Advantages when using FNDLOAD are :
- Because downloaded data is stored in a text file, version administration is possible
- When uploading data, the original data is updated. So, no need for deleting the alert first
- The adjusted Alert for production is saved in a text file on the server, you don’t have to worry about changes by end users in the application
To use FNDLOAD, the following syntax is needed.
FNDLOAD apps/appspwd 0 Y mode configfile datafile entity [parameter1â€¦..]
As you can see, some info is needed.
- First you will need to know the Apps password.
- The mode is either DOWNLOAD or UPLOAD.
- The configfile is the file that Fndload needs to download op upload data.
- The data file is the output file, in which the downloaded data is written
- the entity is the entity you want to download, for example alr_alerts
- parameters van be passed to download a certain Alert
By default Oracle delivers some configuration files you can use to download certain entities. You can find these config files in $FND_TOP/patch/115/import
Config file for Alerts
Unfortunately Oracle did not create a standard config file for downloading Alerts. I had to make one myself. From the documentation I learned that these config files always had the same layout. At the beginning of the file you have the define part. Here you define the tables you want to download the data from. Next is the download section, which is actually a group of select statements. The final part of the config file is the upload section. The upload section uses standard Oracle packages, so the upload always checks whether the data must be updated or inserted. I used the following packages to download one alert.
The complete config file (alert.lct) I made is 12 pages long, so I will only show one package ( if anyone is interested in the complete config file alert.lct, please let me know..). Each package is an entity in the config file, but because of the data structure in Alerts, alr_alerts is the main entity and all the others belong to this one. You get a sort of nested define sectionâ€¦.
A part of the define section of alr_alerts looks like this.
KEY APPLICATION_SHORT_NAME VARCHAR2(50)
KEY ALERT_ID NUMBER
KEY ALERT_NAME VARCHAR2(50)
KEY ALERT_CONDITION_TYPE VARCHAR2(1)
BASE ENABLED_FLAG VARCHAR2(1)
BASE START_DATE_ACTIVE VARCHAR2(20)
BASE END_DATE_ACTIVE VARCHAR2(20)
The end alr_alerts should be at the end of the define, but in this case the other entities are defined within the main entity alr_alerts, so the end alr_alerts is after the last entity.
The download part of alr_alerts :
" select APPLICATION_SHORT_NAME, ALERT_ID, ALERT_NAME, ALERT_CONDITION_TYPE, alr.ENABLED_FLAG, to_char(START_DATE_ACTIVE,’YYYY/MM/DD HH24:MI:SS’), to_char(END_DATE_ACTIVE,’YYYY/MM/DD HH24:MI:SS’), TABLE_ID, TABLE_APPLICATION_ID, DESCRIPTION, FREQUENCY_TYPE, WEEKLY_CHECK_DAY, MONTHLY_CHECK_DAY_NUM, DAYS_BETWEEN_CHECKS, to_char(CHECK_BEGIN_DATE,’YYYY/MM/DD HH24:MI:SS’), to_char(DATE_LAST_CHECKED,’YYYY/MM/DD HH24:MI:SS’), INSERT_FLAG, UPDATE_FLAG, DELETE_FLAG, MAINTAIN_HISTORY_DAYS, CHECK_TIME, CHECK_START_TIME, CHECK_END_TIME, SECONDS_BETWEEN_CHECKS, CHECK_ONCE_DAILY_FLAG, SQL_STATEMENT_TEXT, ONE_TIME_ONLY_FLAG, TABLE_NAME from alr_alerts alr, fnd_application fab where alert_name like :ALERTNAME and alr.application_id = fab.application_id "
The upload section looks like this â€¦.
if (:UPLOAD_MODE = ‘NLS’) then
null; /* There are no translated columns in NLS mode, so nothing*/
/* to do in NLS mode. */
X_APPLICATION_SHORT_NAME => :APPLICATION_SHORT_NAME,
X_ALERT_NAME => :ALERT_NAME,
X_OWNER => NULL,
X_ALERT_CONDITION_TYPE => :ALERT_CONDITION_TYPE,
In the upload you see that the load_row function from the alr_alerts_pkg is used. The load_row function is checking if the row must be inserted or updated. For each package mentioned above, you should create a define, download and upload section. For the upload to work with the nested define section, a ‘ BEGIN’ is needed before the first upload. No ‘END’ is needed in the script…(???)
When the change is deleting a record, I found out that FNDLOAD does not delete records automaticly. Therefore I adjusted the config file and added an extra parameter to the FNDLOAD command. The extra parameter is UPLOAD_MODE=â€™REPLACEâ€™ and the extra code in the config file
if :UPLOAD_MODE = ‘REPLACE’
delete from alr_actions
where alert_id = (select alert_id from alr_alerts
where alert_name like :alert_name);
Finally, to retrieve Alert data and upload it to another database use the following commands
FNDLOAD apps/â€¦.. 0 Y DOWNLOAD ALERT.LCT ALR_ALERT.UPL ALERTNAME=â€ALERT_NAMEâ€
FNDLOAD apps/â€¦â€¦ 0 Y UPLOAD ALR_ALERT.LCT ALR_ALERT.UPL UPLOAD_MODE=’REPLACE’