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’
47 thoughts on “Using FNDLOAD to transfer Oracle Alert”
It is an really G8 work, can you share that lct file.
Thanks in Advance,
can you please send the lct file.
Thanks in advance
Could you please provide me the full configuration file?
Hello, great work! I’d be very interested in a copy of the alert .lct thanks.
Can you please send the alert.lct file ? Many thanks in advance
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
I found the following Meta link Note, have a look.
Meta Link: 400295.1
Oracle Alert – Version: 18.104.22.168
Information in this document applies to any platform.
Q: How to autodeploy alerts from one instance to another instance
using a command line script?
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
Could you please send me a lct file at my email, I have to migrate Alert another instance.
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
Great job. Thanks a lot for solving this puzzle for us. Could you please email me the complete file Alert.lct ?
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
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”.
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 !!
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,
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.
Hi This is really superb.Actually we want to migrate some custom alerts from 11.5.9 to 22.214.171.124 so it wud be really gr8
if u cud send the .lct file.The email is firstname.lastname@example.org.Thanks in advance
Gr8 work. I need to migrate Alerts urgently. Can you pl. share the Alert.lct file?
Thanks & Regards
Can you send me the complete alert.lct please? This is great work and will really help us to transfer out custom Alerts. Thanks.
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 email@example.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 firstname.lastname@example.org
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 email@example.com at your convenience.
This is good work done and shared as well. Can you send me alert.lct file at firstname.lastname@example.org
Good work. I am looking for the same file. Please share it across if you are willing 😉 at email@example.com. Thanks.
Wow! very nice work. Can you send me the complete alert.lct. Again, great job.
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
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
” select empno,ename,job from emp where empno in (:EMPLOYEE_NUMBER) ”
is the query correct
please help me on this
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.
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 firstname.lastname@example.org.
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.
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
Great stuff! We are looking at migrating alerts with this features and are wondering if you could share the code? Thanks,
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 ?
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 ?
It is really a good job, and it is appreciate to give me a complete config file. Thank you.
My Email: wfgDlut@msn.com
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?
Thats some gr8 info.Thanx for sharing ur research.
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.
Comments are closed.