Using FNDLOAD to transfer Oracle Alert

Bas Klaassen June 23, 2006 47
0 0
Read Time:6 Minute, 59 Second

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”

FNDLOAD apps/……  0 Y UPLOAD ALR_ALERT.LCT ALR_ALERT.UPL UPLOAD_MODE=’REPLACE’

About Post Author

Bas Klaassen

Oracle Consultant at AMIS
Bas.Klaassen@amis.nl
Happy
Happy
0 %
Sad
Sad
0 %
Excited
Excited
0 %
Sleepy
Sleepy
0 %
Angry
Angry
0 %
Surprise
Surprise
0 %

Related posts:

Upgrading EBS 11i database (9.2.0.6) to 10GR2 database First patch for Oracle E-Business Suite R12 Default ThumbnailOracle E-Business Suite R12 with OEM10G OAUG Fusion Middleware BootCamp Chicago – Part I DBA Conference Holland & XMLDB Whitepaper

Average Rating

5 Star
0%
4 Star
0%
3 Star
0%
2 Star
0%
1 Star
0%
(Add your review)

47 thoughts on “Using FNDLOAD to transfer Oracle Alert”

  1. Hi,
    It is an really G8 work, can you share that lct file.
    Thanks in Advance,
    Shishu Paul.

  3. Hi,

    Great Work…

    Could you please provide me the full configuration file?

    Thanks
    Selvakumar K

  4. Hello, great work! I’d be very interested in a copy of the alert .lct thanks.

    Regards
    Dominic

  6. 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

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

  9. 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.

  10. Great job! Will you please send me a copy of complete alert lct? Is there anything unique for R12/ Thanks.

  13. Hi Bas, Thanks for sharing this. Can you please email me a copy of the entire package and instructiosn to run it. Thanks

  14. Hello Bas,
    Great job. Thanks a lot for solving this puzzle for us. Could you please email me the complete file Alert.lct ?
    Thanks

  15. Hi Bas,

    Thanks a lot for your job.Could you please E-mail me the complete config file alert.lct .Thanks

  19. Great info just knowing that Alerts can be migrated via FNDLOAD. A copy of you Alert.lct file would be greatly appreciated. Thx

  20. Hi Gr8 work. I need to migrate Alerts urgently. Can you pl. share the Alert.lct file? Thanks & Regards uttam kumar nargotra

  22. 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 !!

  23. 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

  24. 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.

  25. Please send me the LCT file, so tha ti can use the same to download and upload the alert definition.
    Thanks in advance.
    -Shipra

  27. Hi
    Gr8 work. I need to migrate Alerts urgently. Can you pl. share the Alert.lct file?
    Thanks & Regards
    Anita

  28. Can you send me the complete alert.lct please? This is great work and will really help us to transfer out custom Alerts. Thanks.

  29. 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.

  31. 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.

  35. 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

  36. 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

  37. 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

  39. 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.

  40. 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

  41. Hi,

    Great stuff! We are looking at migrating alerts with this features and are wondering if you could share the code? Thanks,
    Gaby

  42. 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

  43. 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

  45. 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

  47. 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

Comments are closed.

Next Post

Implementing an FTP server in PL/SQL

Fri Jun 23 , 2006
The problem: I recently had to resolve a problem at a client’s site where an automated database provisioning process was regularly failing due to LAN outages. Under normal circumstances, a DOS batch file was scheduled to fetch a list of files from an FTP server and then call Oracle stored […]

You May Like

%d bloggers like this: