Using FNDLOAD to transfer Oracle Alert

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.

1. There is no version administration, you do not know what version is in the production database.
2. What if somebody is changing the Alert in the Test environment……then this change is also transferred to production.
3. 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 :

1. Because downloaded data is stored in a text file, version administration is possible
2. When uploading data, the original data is updated. So, no need for deleting the alert first
3. 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

 FNDLOAD Syntax

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.

ALR_ACTION_GROUPS_PKG

ALR_ACTIONS_PKG

ALR_ALERT_INPUTS_PKG

ALR_ALERT_INSTALLATIONS_PKG

ALR_ACTION_OUTPUTS_PKG

ALR_ALERT_OUTPUTS_PKG

ALR_ACTION_SET_OUTPUTS_PKG

ALR_ACTION_SET_INPUTS_PKG

ALR_ACTION_SETS_PKG

ALR_ACTION_SET_MEMBERS_PKG

ALR_ALERTS_PKG

ALR_RESPONSE_SETS_PKG

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.

DEFINE ALR_ALERTS

  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 :

DOWNLOAD 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 …. 

UPLOAD ALR_ALERTS

BEGIN

"begin

        if (:UPLOAD_MODE = ‘NLS’) then

           null; /* There are no translated columns in NLS mode, so nothing*/

                 /* to do in NLS mode. */

        else

        alr_alerts_pkg.load_row(

  X_APPLICATION_SHORT_NAME => :APPLICATION_SHORT_NAME,

  X_ALERT_NAME => :ALERT_NAME,

  X_OWNER => NULL,

  X_ALERT_CONDITION_TYPE => :ALERT_CONDITION_TYPE,

 ….

);

end if;

end; "

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’

      then

            delete from alr_actions

            where alert_id =  (select alert_id from alr_alerts

                              where alert_name like :alert_name);

      end if;

Download/Upload data

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”