Using FNDLOAD to transfer Alerts

When you use Oracle Alert, this is a module from the Oracle EBS, you probably work with custom alerts instead of 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 is 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.

FNDLOAD can be used to upload or download.

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, and cannot be adjusted by end users

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

/afs292/appl/fnd/11.5.0/patch/115/import
> ls *.lct
afattach.lct afdict.lct afpinit.lct afumsaru.lct fndstd.lct
afcpexec.lct afdskob.lct afplist.lct afwfds.lct m57hrupv2.lct
afcplibr.lct affaudit.lct afprfma.lct afwfload.lct makhtml.lct
afcpmime.lct afffload.lct afpvalue.lct fndatttm.lct mappsnav.lct
afcppinf.lct affrmcus.lct afrole.lct fnddmrepos.lct umxrgsvc.lct
afcpprnt.lct afindust.lct afscapp.lct fnddmsec.lct wfdpart.lct
afcpprog.lct aflvmlu.lct afscprof.lct fndfwkmsg.lct wfdpview.lct
afcppstl.lct afmdmsg.lct afscursp.lct fndobjt.lct wfdrole.lct
afcpque.lct afmlhelp.lct afsload.lct fndpiagp.lct wfmlrp.lct
afcpreqg.lct afmoinit.lct afsncat.lct fndpicla.lct wfmlrt.lct
afcprset.lct afnls.lct afsvcc.lct fndpidef.lct
afcpsch.lct afoamcgp.lct afsvcct.lct fndpipra.lct
afcpsrvs.lct afoamdw.lct aftz.lct fndpipur.lct
afcpwksh.lct afoammet.lct afums.lct fndprtdep.lct

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 download 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
ALT_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 eleven pages long, so I will only show one packages. 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….

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)
BASE TABLE_ID NUMBER
BASE TABLE_APPLICATION_ID NUMBER
TRANS DESCRIPTION VARCHAR2(240)
BASE FREQUENCY_TYPE VARCHAR2(1)
BASE WEEKLY_CHECK_DAY VARCHAR2(3)
BASE MONTHLY_CHECK_DAY_NUM NUMBER
BASE DAYS_BETWEEN_CHECKS NUMBER
BASE CHECK_BEGIN_DATE VARCHAR2(20)
BASE DATE_LAST_CHECKED VARCHAR2(20)
BASE INSERT_FLAG VARCHAR2(1)
BASE UPDATE_FLAG VARCHAR2(1)
BASE DELETE_FLAG VARCHAR2(1)
BASE MAINTAIN_HISTORY_DAYS NUMBER
BASE CHECK_TIME NUMBER
BASE CHECK_START_TIME NUMBER
BASE CHECK_END_TIME NUMBER
BASE SECONDS_BETWEEN_CHECKS NUMBER
BASE CHECK_ONCE_DAILY_FLAG VARCHAR2(1)
BASE SQL_STATEMENT_TEXT VARCHAR2(4000)
BASE ONE_TIME_ONLY_FLAG NUMBER
BASE TABLE_NAME VARCHAR2(31)

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.

The upload 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,
X_ENABLED_FLAG => :ENABLED_FLAG,
X_START_DATE_ACTIVE => :START_DATE_ACTIVE,
X_END_DATE_ACTIVE => :END_DATE_ACTIVE,
X_TABLE_APPLICATION_SHORT_NAME => NULL,
X_DESCRIPTION => :DESCRIPTION,
X_FREQUENCY_TYPE => :FREQUENCY_TYPE,
X_WEEKLY_CHECK_DAY => :WEEKLY_CHECK_DAY,
X_MONTHLY_CHECK_DAY_NUM => :MONTHLY_CHECK_DAY_NUM,
X_DAYS_BETWEEN_CHECKS => :DAYS_BETWEEN_CHECKS,
X_CHECK_BEGIN_DATE => :CHECK_BEGIN_DATE,
X_DATE_LAST_CHECKED => :DATE_LAST_CHECKED,
X_INSERT_FLAG => :INSERT_FLAG,
X_UPDATE_FLAG => :UPDATE_FLAG,
X_DELETE_FLAG => :DELETE_FLAG,
X_MAINTAIN_HISTORY_DAYS => :MAINTAIN_HISTORY_DAYS,
X_CHECK_TIME => :CHECK_TIME,
X_CHECK_START_TIME => :CHECK_START_TIME,
X_CHECK_END_TIME => :CHECK_END_TIME,
X_SECONDS_BETWEEN_CHECKS => :SECONDS_BETWEEN_CHECKS,
X_CHECK_ONCE_DAILY_FLAG => :CHECK_ONCE_DAILY_FLAG,
X_SQL_STATEMENT_TEXT => :SQL_STATEMENT_TEXT,
X_ONE_TIME_ONLY_FLAG => :ONE_TIME_ONLY_FLAG,
X_TABLE_NAME => :TABLE_NAME );
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.

Downloaded data

Well, hoes the downloaded data looks like.
Again, I will only look at the downloaded data from alr_alerts.
To download alert data use the following syntax :
FNDLOAD apps/….. 0 Y DOWNLOAD ALERT.LCT ALR_ALERT.UPL ALERTNAME=”TEST_BAS”

When downloading you should always tell FNDLOAD which entity should be downloaded.

BEGIN ALR_ALERTS "XXOF" "101060" "TEST_BAS" "P"
ENABLED_FLAG = "N"
START_DATE_ACTIVE = "1997/12/10 00:00:00"
DESCRIPTION =
"Dagelijkse Nachtverwerking voor BAS, Transfer Ap=>GL, Naportaal AP en Rapportages"
FREQUENCY_TYPE = "C"
DAYS_BETWEEN_CHECKS = "1"
MAINTAIN_HISTORY_DAYS = "0"
CHECK_START_TIME = "900"
CHECK_ONCE_DAILY_FLAG = "Y"
SQL_STATEMENT_TEXT = "select * from global_name"

Upload data

To upload the downloaded into another database, simply use the following command with the settings of the new database.

FNDLOAD apps/…… 0 Y UPLOAD ALR_ALERT.LCT ALR_ALERT.UPL

When uploading data, al entities from the configuration file will be uploaded.