Oracle Applications DBA Blog

FNDLOAD

Last week i had spent a considerable amount of time working with quieries involving the FNDLOAD utility.

The FNDLOAD utility also know as the Generic loader in Oracle Applications is a concurrent program that can move data across applications instances using database and text file representations.

The FNDLOAD downloads the data from the database using a oracle predefined configuration file (.lct) and stores the data in a converted data file (.ldt).Similarly while using FNDLOAD to upload data, the loader uploads the data from the data file created by the previous download based on the configuration file.

The FNDLOAD does the download and upload based on the instructions given the configuration files(.lct),these are predefined by oracle and can be located at $FND_TOP/patch/115/import.

You could also create custom configuration files and place them here.

The data files created by the download process and used for the upload are in the form of of text files and hence portable. It is not recommended to modify the data files manually.

Oracle currently supports the migration of the following types of data using FNDLOAD

• Printers / Print queues / Executables Printers / Print queues / Executables.
• Roles / Responsibilities / Forms Roles / Responsibilities / Forms.
• Menus / Users / Request Sets Menus / Users / Request Sets.
• Request Groups / Request Queues Request Groups / Request Queues.
• Work shifts / Programs / Libraries Work shifts / Programs / Libraries.
• Attachments / Help Files Attachments / Help Files.
• Mime Types Mime Types.
• Security Information.

I have recently read a post on migrating oracle alerts using FNDLOAD also. You might like to check more about this at
http://technology.amis.nl/blog/?p=1246

FNDLOAD utility uses the OWNER and LAST_UPDATE_DATE attributes to ensure data preservation. The data with OWNER=CUSTOM is always given preference over OWENR=SEED. That is the CUSTOM data will never be overwritten by the SEED data. In a case where the OWNERS are same the LAST_UPDATE_DATE is given preference that is a row with the LAST_UPDATE_DTE higher is inserted.

The FNDLOAD executable can be located at $FND_TOP/bin.

syntax

FNDLOAD [username/password] 0 Y [mode] [configuration file] [target data file] [object type key] [object Identifier]

Where

0 Y represent your concurrent program flags.
[username/password] your apps username and password
[mode] might be download or upload
[configuration file] might be the .lct file like afcpprog.lct
[target data file] this the .ldt file created while download(or used for upload) like someprg.ldt[object type key] this would represent you object type like CONCURRENT_PROGRAM
[object Identifier] this would be your object name like CONCURRENT_PROGRAM_NAME='XXACMEPRT'

I am presenting a few FNDLOAD commands used frequently by me

Some of the below examples here are orignally from Anil Passi's site and a full list can be found at
(http://oracle.anilpassi.com/oracle-fndload-script-examples.html)

Concurrent Program

$FND_TOP/bin/FNDLOAD apps/apps@DB_INSTANCE 0 Y DOWNLOAD $FND_TOP/admin/import/afcpprog.lct XXSAM_PRG.ldt PROGRAM
APPLICATION_SHORT_NAME='XSPRT' CONCURRENT_PROGRAM_NAME='XX_SAM_CUSTOM_PRT'

$FND_TOP/bin/FNDLOAD apps/apps@DB_INSTANCE 0 Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct XXSAM_PRG.ldt

Request Sets

This will happen in two stages first for the request set defination and then the underlying links.

$FND_TOP/bin/FNDLOAD apps/apps@DB_INSTANCE DOWNLOAD 0 Y $FND_TOP/patch/115/import/afcprset.lct XX_SAM_REQ_SET_DEF.ldt REQ_SET
REQUEST_SET_NAME="XX_SAM_REQ_SET"

$FND_TOP/bin/FNDLOAD apps/apps@DB_INSTANCE DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct XX_SAM_REQ_SET_LK.ldt
REQ_SET_LINKS REQUEST_SET_NAME="XX_SAM_REQ_SET"

Now upload the defination first and then the links

$FND_TOP/bin/FNDLOAD apps/apps@DB_INSTANCE 0 Y UPLOAD $FND_TOP/patch/115/import/afcprset.lct XX_SAM_REQ_SET_DEF.ldt

$FND_TOP/bin/FNDLOAD apps/apps@DB_INSTANCE 0 Y UPLOAD $FND_TOP/patch/115/import/afcprset.lct XX_SAM_REQ_SET_LK.ldt

Value Set

$FND_TOP/bin/FNDLOAD apps/apps@DB_INSTANCE 0 Y DOWNLOAD $FND_TOP/admin/import/afffload.lct XXSAMVALUESET.ldt VALUE_SET
FLEX_VALUE_SET_NAME='XX_SAM_VALUE_SET'

$FND_TOP/bin/FNDLOAD apps/apps@DB_INSTANCE 0 Y UPLOAD $FND_TOP/patch/115/import/afffload.lct XXSAMVALUESET.ldt

Request Groups

$FND_TOP/bin/FNDLOAD apps/apps@DB_INSTANCE O Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct XX_SAM_GROUP.ldt REQUEST_GROUP
REQUEST_GROUP_NAME="XX_SAM_GROUP" APPLICATION_SHORT_NAME="XXSAM"

$FND_TOP/bin/FNDLOAD apps/apps@DB_INSTANCE O Y UPLOAD $FND_TOP/patch/115/import/afcpreqg.lct XX_SAM_GROUP.ldt

Responsiblity

$FND_TOP/bin/FNDLOAD apps/apps@DB_INSTANCE 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct XXSAMCUSTRESP.ldt
FND_RESPONSIBILITY RESP_KEY="XX_SAM_CUSTRESP"

$FND_TOP/bin/FNDLOAD apps/apps@DB_INSTANCE O Y UPLOAD $FND_TOP/patch/115/import/afscursp.lct XXSAMCUSTRESP.ldt

Menus

$FND_TOP/bin/FNDLOAD apps/apps@DB_INSTANCE 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct XXSAM_MENU.ldt MENU
MENU_NAME="XX_SAM_MENU"

$FND_TOP/bin/FNDLOAD apps/apps@DB_INSTANCE O Y UPLOAD $FND_TOP/patch/115/import/afsload.lct XXSAM_MENU.ldt

Messages

This will download all messages under the custom application XXFL

$FND_TOP/bin/FNDLOAD apps/apps@DB_INSTANCE 0 Y DOWNLOAD $FND_TOP/patch/115/import/afmdmsg.lct XX_FL_MSG.ldt FND_NEW_MESSAGES
APPLICATION_SHORT_NAME='XXFL'

$FND_TOP/bin/FNDLOAD apps/apps@DB_INSTANCE O Y UPLOAD $FND_TOP/patch/115/import/afmdmsg.lct XX_FL_MSG.ldt

You may also download and upload indivdual messages.

Lookup Values

$FND_TOP/bin/FNDLOAD apps/apps@DB_INSTANCE 0 Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct XX_SAM_PO_VAL.ldt
FND_LOOKUP_TYPE APPLICATION_SHORT_NAME ='XXPO' LOOKUP_TYPE="XX_SAM_PO_VAL"

$FND_TOP/bin/FNDLOAD apps/apps@DB_INSTANCE O Y UPLOAD $FND_TOP/patch/115/import/aflvmlu.lct XX_SAM_PO_VAL.ldt

Profile Options

$FND_TOP/bin/FNDLOAD apps/apps@DB_INSTANCE $FND_TOP/patch/115/import/afscprof.lct SAMPRF.ldt PROFILE PROFILE_NAME="profile option" APPLICATION_SHORT_NAME="FND"

$FND_TOP/bin/FNDLOAD apps/apps@DB_INSTANCE UPLOAD
$FND_TOP/patch/115/import/afscprof.lct SAMPRF.ldt

Refer the metalink note Parameters Of Different Configuration Files To Download And Upload Seed Data Types (287417.1)
For a complete list of FNDLOAD examples vist Anil Passi's post on FNDLOAD