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
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â€
FNDLOAD apps/…… 0 Y UPLOAD ALR_ALERT.LCT ALR_ALERT.UPL UPLOAD_MODE=’REPLACE’
![Compare tables with the minus operator, even if they contain clobs and/or blobs.]({"aperture":"0","credit":"","camera":"","caption":"","created_timestamp":"0","copyright":"http:\/\/seattletimes.nwsource.com\/ABPub\/2012\/08\/02\/2018836700.jpg","focal_length":"0","iso":"0","shutter_speed":"0","title":"","orientation":"0"} "Compare tables with the minus operator, even if they contain clobs and/or blobs.")
Hi,
It is an really G8 work, can you share that lct file.
Thanks in Advance,
Shishu Paul.
Hi,
can you please send the lct file.
Thanks in advance
Gabriel
Hi,
Great Work…
Could you please provide me the full configuration file?
Thanks
Selvakumar K
Hello, great work! I’d be very interested in a copy of the alert .lct thanks.
Regards
Dominic
Hi,
Can you please send the alert.lct file ? Many thanks in advance
Regards
Suneel
Hi. I found this information very usefull, but I’m fighting wih the pakages ¿Can you share me the configuration file? I appreciate it. Thanks
Hi there
I found the following Meta link Note, have a look.
Meta Link: 400295.1
Oracle Alert – Version: 11.5.10.2
Information in this document applies to any platform.
Goal
Q: How to autodeploy alerts from one instance to another instance
using a command line script?
Solution
A: Steps which needs to be followed
1. Command to download
FNDLOAD apps_user_name/apps_password 0 Y DOWNLOAD
$ALR_TOP/patch/115/import/alr.lct my_file.ldt ALR_ALERTS
APPLICATION_SHORT_NAME=FND ALERT_NAME=Alert name to download
2. Command to upload
FNDLOAD apps_user_name/apps_password 0 Y UPLOAD
$ALR_TOP/patch/115/import/alr.lct my_file.ldt
Regards
ASIM
Hi Bas
Great Work.
Could you please send me a lct file at my email, I have to migrate Alert another instance.
asimking420@yahoo.com
Thanks
ASIM
I just came across your article. Great job. I am working on migrating pay_user_tables and its child tables using FNDLOAD. The lct file comes with Oracle does not work for some reason. Please email me your configuration file alert.lct if you don’t mind. Thanks.
Great job! Will you please send me a copy of complete alert lct? Is there anything unique for R12/ Thanks.
I need to transfer the menu & Submenu details from Oracle application to excel file.Could you please suggest the process.
Can you please provide complete alert.lct
Hi Bas, Thanks for sharing this. Can you please email me a copy of the entire package and instructiosn to run it. Thanks
Hello Bas,
Great job. Thanks a lot for solving this puzzle for us. Could you please email me the complete file Alert.lct ?
Thanks
Hi Bas,
Thanks a lot for your job.Could you please E-mail me the complete config file alert.lct .Thanks
Please email me complete config file alert.lct
Thanks.
Great Info. A copy of your alert.lct file will be appreciated. Thanks.
Bas, would you be so kind as to send me your alert.lct file? Thanks for the posting this.
Great info just knowing that Alerts can be migrated via FNDLOAD. A copy of you Alert.lct file would be greatly appreciated. Thx
Hi Gr8 work. I need to migrate Alerts urgently. Can you pl. share the Alert.lct file? Thanks & Regards uttam kumar nargotra
Thanks a lot for your job.
I would like to receive a copy of your file “alert.lct”.
Best regards.
Cezar Ferraciolli
Hi Bas,
I am still a novice to oracle apps. The info which you posted helped me in understanding the working of FNDLOAD.
Famous quote: THE BEST AMONGST YOU IS ONE WHO GATHERS KNOWLEDGE AND SHARES IT.
you are truly the BEST
Thanks a lot !!
Hi Boss,
That was really good work and really appreciate for sharing the work with us.I have been working on it and got your thread while searching on net.If you are comfortable please mail me the config file that you have created na.
Thanks & Regards,
Prudhvi M
Hi! Is their any other way apart from FNDLOAD, through which we can import alert between instances.If yes then can you please update me with the steps.That Would be a real help from your side.Thank you.
Please send me the LCT file, so tha ti can use the same to download and upload the alert definition.
Thanks in advance.
-Shipra
Hi This is really superb.Actually we want to migrate some custom alerts from 11.5.9 to 11.5.10.2 so it wud be really gr8
if u cud send the .lct file.The email is jobyjohn31@gmail.com.Thanks in advance
Hi
Gr8 work. I need to migrate Alerts urgently. Can you pl. share the Alert.lct file?
Thanks & Regards
Anita
Can you send me the complete alert.lct please? This is great work and will really help us to transfer out custom Alerts. Thanks.
Hi Bas,
This is a great effort done, and the best thing you share this with us.
Many thanks to you.
If you don’t mind sending the alert.lct file at mohamed_attya@hotmail.com, hope not bothering you and thanks again.
This is good work done and shared as well. Can you send me alert.lct file at venkatesh_sasi@yahoo.co.in
thanks for sharing such a nice work. It must definitely have taken a lot of effort to develop this functionality. May I have a copy of the configuration file you created for this purpose. You could send it at dkpathak@gmail.com at your convenience.
This is good work done and shared as well. Can you send me alert.lct file at farhana@qasco.com.qa
Good work. I am looking for the same file. Please share it across if you are willing 😉 at univermal@gmail.com. Thanks.
Wow! very nice work. Can you send me the complete alert.lct. Again, great job.
Hi,
We have similar need to transfer Alerts from one instance to another.
We also have a need to maintain All the Alert information and set up.
Is there any way to do so?
Can you just give us the config file alert.lct
hi
your explanation was great
but can you please explaing me how can i pass values from command line when i have an IN clause in the download query
example
download SAMPLE
” select empno,ename,job from emp where empno in (:EMPLOYEE_NUMBER) ”
is the query correct
please help me on this
Hi,
Oracle provided config file alr.lct to migrate oracle alerts.
you can get it at $ALR_TOP/patch/115/import location.
and command will be modify as
FNDLOAD apps_user_name/apps_password O Y DOWNLOAD $ALR_TOP/patch/115/import/alr.lct my_file.ldt ALR_ALERTS APPLICATION_SHORT_NAME=”INV” ALERT_NAME=”Alert_to_download”
It is working.
Thanks
That’s great work. I have a need to download/upload alerts. I was wondering if you would send me the lct file you created so that I can use it with fndload.
My email is markramos@ramos-group.net.
Wow, finally an easy solution to moving alerts. the DBlink method is really only a half fix. Would relly appreciate having a copy of the alrt lct.
Hi,
This is great. I’m researching this for SOX compliance. Can you forward me a copy of the complete config file alert.lct ? Email me: avgjoe27@aol. Thanks so much
Hi,
Great stuff! We are looking at migrating alerts with this features and are wondering if you could share the code? Thanks,
Gaby
Hi,
Very interesting. I was just looking for this file as I’m trying to copy alerts accross instances and I found your blog.
Could you please send me the file ?
Thanks
Emmanuelle
Hi Bas,
You did a great job , it is very useful for me , in our project i have some custom alert , and also i need transfer them to product environment . so would you please send me the alert.lct ?
Many thanks
Andy
It is really a good job, and it is appreciate to give me a complete config file. Thank you.
My Email: wfgDlut@msn.com
HI Bas,
This is interesting stuff. We actually have a need to transfer alerts urgently. Would it be possible to receive the entire package of yours and instructions, please?
Thanks.
Jim
bas,
Thats some gr8 info.Thanx for sharing ur research.
Regards,
Kris
Bas,
Thanks for sharing all the results of your investigation! This must have taken you quite some time and to make the outcome available is very generous! Great work.
best regards,
Lucas