Moving a report from one instance to another instance would become a bit tedious job as we need to perform all the series of actions carefully and same as we did in the other instance. It would become more tedious if we don’t have access to target instance as DBA help would be required. In this kind of situations FNDLOAD Utility will help us to move the things smoothly but what is this FNDLOAD Utility? FNDLOAD is generic Oracle Unix utility/API that allows for the transfer of a wide range of Oracle Foundation (FND) data from one instance to another.
How does FNDLOAD work?
FNDLOAD can download data from an application into a portable text file. This generated text file can then be uploaded into any other database. This utility downloads data from a database according to a configuration (.lct) file, and converts the data into a data file (.ldt file). The generated ldt file can then upload data to another database using a configuration file. The loader operates in one of two modes:
- Download mode
- Upload mode
In the download mode, data is downloaded from the database to a text file and in the upload mode, data is uploaded from a text file to the database.
FNDLOAD Syntax:
The Generic Loader is a concurrent program named FNDLOAD. The concurrent executable takes the following parameters:
FNDLOAD apps/pwd 0 Y mode configfile datafile entity [ param … ]
Where,
- <apps/pwd> : The APPS schema and password in the form username/password[@connect_string].
- < 0 Y > : Concurrent program flags
- mode : UPLOAD or DOWNLOAD. UPLOAD causes the datafile to be uploaded to the database. DOWNLOAD causes the loader tofetch rows and write them to the datafile.
- <configfile> : The configuration file to use (usually with a suffix of .lct, but not enforced or supplied by the loader)
- <datafile> : The data file to write (usually with a suffix of .ldt, but not enforced or supplied by the loader). If the data file already exists, it will be overwritten.
- <entity> : The entity(ies) to upload or download. When uploading, you should always upload all entities, so specify a “-” to upload all entities.
- < [param] > : Zero or more additional parameters are used to provide bind values in the access SQL (both UPLOAD and DOWNLOAD). Each parameter is in the form NAME=VALUE. NAME should not conflict with an attribute name for the entities being loaded.
Example
Lets consider an example of moving a report to TEST instance to PROD. Generally every report has a ‘Concurrent Program’, ‘Template’ (if we are using XMLP), ‘Data Definition’ and the report has to be assigned to ‘Request Group’, ‘Application’. To port these four things we need to execute four different FNDLOAD commands.
To Download from TEST instance
for example, lets consider the short names of Application, Concurrent Program, Template and Request Group as follows
APPL=”XX_CUST”
CONC_PROG_NAME=”XX_TEST_REPORT”
TEMP_CODE=”XX_TEST_REPORT”
REQ_GRP_NAME1=”All Inclusive GUI”
REQ_GRP_APPL1=”INV”
PASSWD=”APPS_PASSWD”
you need to execute the following commands on your UNIX box to create .ldt files
# Download the Concurrent Program
FNDLOAD apps/${PASSWD} 0 Y DOWNLOAD @fnd:patch/115/import/afcpprog.lct ${CONC_PROG_NAME}.ldt PROGRAM CONCURRENT_PROGRAM_NAME=${CONC_PROG_NAME} APPLICATION_SHORT_NAME=${APPL}
# Download the Request Groups
FNDLOAD apps/${PASSWD} 0 Y DOWNLOAD @fnd:patch/115/import/afcpreqg.lct REQ_GRP_${CONC_PROG_NAME}_1.ldt REQUEST_GROUP REQUEST_GROUP_UNIT REQUEST_GROUP_NAME=”${REQ_GRP_NAME1}” APPLICATION_SHORT_NAME=${REQ_GRP_APPL1} UNIT_NAME=”${CONC_PROG_NAME}”
# Download the Data definition and Template
FNDLOAD apps/${PASSWD} 0 Y DOWNLOAD $XDO_TOP/patch/115/import/xdotmpl.lct TEMPLATE_${CONC_PROG_NAME}.ldt XDO_DS_DEFINITIONS DATA_SOURCE_CODE=${CONC_PROG_NAME} APPLICATION_SHORT_NAME=${APPL}
you need to upload these .ldt files to PROD instance by executing the following commands.
To Upload ldt files to PROD instance
MOD=”XX_TEST_REPORT”
APPL=”XX_CUST”
PASSWD=”APPS_PASSWD”
# Upload Concurrent Program
FNDLOAD apps/${PASSWD} 0 Y UPLOAD @fnd:patch/115/import/afcpprog.lct ${MOD}.ldt CUSTOM_MODE=FORCE
# Upload the Request Groups
FNDLOAD apps/${PASSWD} 0 Y UPLOAD @fnd:patch/115/import/afcpreqg.lct REQ_GRP_${MOD}_1.ldt CUSTOM_MODE=FORCE
# Upload Template and Data definition for main module
FNDLOAD apps/${PASSWD} 0 Y UPLOAD $XDO_TOP/patch/115/import/xdotmpl.lct TEMPLATE_${MOD}.ldt CUSTOM_MODE=FORCE
You can make the above commands into two shell scripts namely download.sh and upload.sh. The execution of above commands creates a Executable, Concurrent Program and its Parameters, assigns the report to a request group. It also create the Data template and its Definition but the Template(.rft) file has to be uploaded manually by going to ‘XML Publisher Responsibility’.
Read the Article: FNDLOAD Scripts to Migrate AOL Entities for using FNDLOAD utility for other AOL Entities