Oracle e business suite, Generic Loader - FNDLOAD

[Kashif Manzoor]

Abstract:
We will examine FNDLOAD configuration management process in Oracle E-Business Suite. We will briefly examine concepts for configuration,
customization. This note will describe how to use the FNDLOAD and java XMLImporter and XDOLoader utilities. Along with example commands and sample unix scripts, we will review how to download and upload Oracle Application data.

Data Synchronization?

Data Synchronization is a process in which some setup data would be synchronized, and this would be more important when you are working in Oracle application development/implementation project. The equally important that ATG data Migration takes place necessary to synchronize the data across databases instance during :

· Installations (New descriptive flex field creations etc)

· Upgrades (Apps upgrade etc.)

· Maintenance (Value set changes etc.
FNDLOAD -- The FNDLOAD utility is documented in Oracle Applications 11.5.7, 11.5.8, 11.5.9 System Administration Guides, Appendix C. Starting with 11.5.10, the documentation has been expanded in the System Administration Guide – Configuration manual, Appendix B. The documentation for Release 12 has 3 new pages for Folders Configuration File. Metalink notes are useful when you encounter an error or are not sure of the correct syntax or format for a specific use of FNDLOAD. Other good sources of documentation are Oracle blogs which can easily be found by entering the FNDLOAD command or error in google.

“FNDLOAD is a Generic Loader” - FNDLOAD can be used to download application data from an Oracle Application instance into editable text file(.ldt file). The data, in the FNDLOAD .ldt file can then be uploaded into another Oracle Application instance. This .ldt file contains not only the
actual data representing the object(s) being downloaded, but it also contains metadata about the structure of that data.
The following diagram illustrates how FNDLOAD downloads data from an Oracle Application database according to a configuration (lct) file, and converts the data into a data file (ldt file) for subsequent uploading to another Application database.

What can be done?

These are the extensive list which can be done through FNDLOAD
· Concurrent Programs, Executables
· Request Groups, Request Sets
· Profile Options
· Key and Descriptive Flexfields
· Menus and Responsibilities
· Forms and Form Functions/Personalizations
· Attachments
· Messages
· Value Sets and Values
· Lookup Types
· User Responsibilities
· Printer Definitions
· FND Dictionary
· Help Configuration
· Document Sequences
· Alerts
· Concurrent Manager Schedules
The FNDLOAD: Concurrent Program – FNDLOAD can be executed as a concurrent program.

FNDLOAD
operates in one of two modes:
Download or Upload
In the download mode, data is downloaded from the database to a text file.
In the upload mode, data is uploaded from a text file to the database.
In both downloading and uploading, the structure of the data involved is described by the configuration file (lct file) and also the access methods to use to copy the data into or out of the database file. The same lct file is used for both uploading and downloading. Data structures
supported by the loader include master-detail relationships and foreign key reference relationships.
The FNDLOAD executable can be found in $FND_TOP/bin and associated lct files are located in $FND_TOP/admin/import/ or $FND_TOP/patch/115/import for the most current version.

FNDLOAD can be executed as a Concurrent Program or as a UNIX command line utility with the following format and parameters, as documented in the Oracle Application System Administrator’s Guide - Configuration:
FNDLOAD apps/pwd 0 Y mode configfile datafile entity [parameter....]
Where:
Apps schema / password
< 0 Y > Concurrent Program flags mode UPLOAD
or DOWNLOAD
Configuration lct file to use.
See the FNDLOAD configuration table below for names of the lct files by seed data types.
Name of ldt data file written out
when the FNDLOAD runs. This file is then used in an FNDLOAD upload to load the downloaded data into another application instance.
Entities to upload or download.
Specify a “-” to upload all entities on uploads.
<[param]> Zero or more additional parameters used to provide bind values in the access SQL. Each parameter is in the form NAME=Value (Look at the specific .lct file being used for more details for these parameters.)

Some advantages when using FNDLOAD

1. Because downloaded data is stored in a text file, version administration is possible
2. There is nothing to worry to go to purchase because Investment = 0$
3. No learning curve. this is relief for developer and dba
4. Fully supported and recommended by Oracle
5. Capture the migrations in a file and use it during installations, clones etc. to migrate in batch
6. Pin-point when something happened and where (database) easily
7. Your AOL data migration process is now simplified and streamlined goal attained

Some disadvantages when using FNDLOAD

1. Applications patching mechanisms use FNDLOAD heavily possibility of negative impact is not zero
2. UPLOAD_MODE=REPLACE only for menus
3. No validation against migrating database/instance sensitive data

Some sample examples:
MENU:
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct menu.ldt MENU MENU_NAME=BOM_NAV

FUNCTION:
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct function.ldt FUNCTION FUNCTION_NAME=INV_INVTTMTX

FORM:
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct form.ldt FORM FORM_NAME=${shortname}

PERSONALIZATION:
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct formpersonalization.ldt FND_FORM_CUSTOM_RULES function_name=${shortname}

PRINTER:
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcppstl.lct printer.ldt STYLE PRINTER_STYLE_NAME=${shortname}

CONCURRENT:
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct concprog.ldt PROGRAM
CONCURRENT_PROGRAM_NAME=${shortname}

LOOKUP:
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct lookup.ldt
FND_LOOKUP_TYPE APPLICATION_SHORT_NAME="FND"
LOOKUP_TYPE=${shortname}

RESPONSIBILITY:
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/admin/import/afscursp.lct responsible.ldt
FND_RESPONSIBILITY RESP_KEY="${respkey}"

DESCFLEX:
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct ${shortname}_flex.ldt
DESC_FLEX DESCRIPTIVE_FLEXFIELD_NAME=${shortname}

KEYFLEX:
FNDLOAD apps/$PASSWD O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct keyflex.ldt KEY_FLEX
P_LEVEL='COL_ALL:FQL_ALL:SQL_ALL:STR_ONE:WFP_ALL:SHA_ALL:
CVR_ALL:SEG_ALL'
APPLICATION_SHORT_NAME=${appshortname}
ID_FLEX_CODE=MCAT
P_STRUCTURE_CODE=${shortname}

VALUE:
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct valset.ldt VALUE_SET
FLEX_VALUE_SET_NAME=${shortname}

PROFILE:
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct profile.ldt PROFILE
PROFILE_NAME=${shortname}

MESSAGE:
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afmdmsg.lct message.ldt
FND_NEW_MESSAGES MESSAGE_NAME=${shortname}

REQUEST GROUP:
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct reqgrp.ldt
REQUEST_GROUP REQUEST_GROUP_NAME=${shortname}

REQUEST SET:
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct rqstset.ldt REQ_SET
APPLICATION_SHORT_NAME="FND" REQUEST_SET_NAME=${shortname}

WEBADI:
FNDLOAD apps/apps 0 Y DOWNLOAD $BNE_TOP/admin/import/bnelay.lct XX_C_O_F_T.ldt BNE_LAYOUTS
LAYOUT_ASN="PER" LAYOUT_CODE="XX_C_O_F_T"

Blob:
With Release 12.1.1, FNDLOAD supports BLOB data (upload / download ) to better serve content-rich applications.
FNDLOAD apps/pwd 0 Y mode configfile datafile entity [ param ... ]

Several different errors can occur when using FNDLOAD, so use attentiveness to review results and logs when using this utility. The FNDLOAD program can fail when the ldt file used to download and the corresponding lct file used to upload are not compatible.
Other Oracle Utilities – Similar to FNDLOAD, other Oracle Development tools, like the XML Publisher and JDeveloper, also have Oracle Application utilities that can be used to download and/or upload application data.
These tools are supported by the java XML and XDO Loader utilities, which can be used to migrate additional types of Application
entities among your instances. Finally, AD administration tasks are sometimes needed to support post-upload requirements for some entities.
Developers can use the XML Publisher to create XML reports for Oracle Applications. In the Apps, navigate to the XML Publisher
Administrator responsibility, then to Templates and Data Definition web pages. Use these web pages to register custom XML reports and upload your report's physical template file from your client, or you can use two command line utilities to automate moving XML report registration and the report’s physical files among instances. FNDLOAD will download and upload XML report data definitions and template metadata. The XDOLoader utility is a Java-based command line program to download and/or upload the physical template (RTF, PDF,
XSL-FO, XML, and XSD) files from/to the XML Publisher database tables. The following examples show the FNDLOAD and XDO Loader commands to download/upload XML report data:
Data definition and template registration:
FNDLOAD apps/apps O Y DOWNLOAD $XDO_TOP/patch/115/import/xdotmpl.lct tmpl.ldt
XDO_DS_DEFINITIONS
APPLICATION_SHORT_NAME=custom application name
DATA_SOURCE_CODE=code
name
Physical template files:
java
oracle.apps.xdo.oa.util.XDOLoader DOWNLOAD -DB_USERNAME apps -
DB_PASSWORD xxxx -JDBC_CONNECTION
'(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=hostname)(PORT=1561))(CONNECT_DATA
=(SERVICE_NAME=SID)))'
-LOB_TYPE TEMPLATE -APPS_SHORT_NAME XXLM -LANGUAGE en
-lct_FILE
$XDO_TOP/patch/115/import/xdotmpl.lct
Developers can use JDeveloper to modify existing OA Framework pages or create new custom web pages. The web pages can then be downloaded and/or uploaded from one Oracle Application instance to another using the Java Exporter and/or Importer utility. OA Framework pages also have file system class and jar files which can be migrated by zipping your custom directories, using ftp to copy these files, and unzipping them into your other instance's $APPL_TOP before running the XML Importer.
The following is a sample Java XML Importer command to load xml pages:
XML PAGES:
java
oracle.jrad.tools.xml.importer.XMLImporter
$XXLM_TOP/oracle/apps/xxlm/${shortname}/webui/
-username apps -password
$password -rootdir
$XXLM_TOP -DBCONNECTION "hostname:dbportid"
-jdk13 -
mmddir $OA_HTML/jrad
Once uploaded and prior to being used, some entities require AD administration tasks to be executed. For example, uploading messages requires the maintenance step of ‘Generate Message Files’. Before AD administration can be used non-interactively, a defaults file must be created interactively. eg: adadmin defaultsfile=$APPL_TOP/admin/testdb1/adadmindef.txt
Once created, this defaults file is used in the non-interactive call to adadmin:
adadmin interactive=n defaultsfile=$APPL_TOP/admin/testdb1/adadmindef.txt
For details, see “Oracle Applications Maintenance Procedures.”
Utility download script – Running FNDLOAD, and other similar utilities, from a UNIX script allows you to easily integrate the use of the download/upload utilities for your Oracle Applications data, e.g., registration of forms/reports/value sets, etc., for code promotion with the configuration management and promotion of your UNIX file system objects, e.g, your forms (fmb), reports (rdf), etc.
Any execution of FNDLOAD assumes the Oracle Application environment has previously been set.
The code segment below is one example of how to call FNDLOAD within a korn shell script.
##
## assumes APPS environment is properly set
## and other shell variables previously defined
##
##
## Prompt for type and parameter value
##
echo "Enter a category
(MENU,FUNCTION,PERSONALIZATION,PRINTER,CONCURRENT,LOOKUP,RESPONSIBILITY,FORM, DESCFLEX, KEYFLEX, VALUE, PROFILE, REQGROUP, REQSET,MESSAGE,XML,XDO,IMPORTER):"
read category
if [ -z
"$category" ] ; then
exit
else
typeset -u category
echo "Using
category" $category | tee -a ${LOGFILE}
fi
##
## determine appropriate user input prompt
case $category in
'REQGROUP')
parm_prompt="Please
enter a request group."
;;
'RESPONSIBILITY')
parm_prompt="Please enter a responsibility key (include _ underscore for spaces)." ;;*)
parm_prompt="Entera short name."
esac
## Get input from user
echo $parm_prompt
read shortname
if [ -z
"$shortname" ] ; then
echo $parm_prompt
exit
fi
respkey=$shortname
## log files from
FNDLOAD execution are not ‘well named’
## this is one attempt to identify a new file
## Get initial list of
*log files
ls *log >
${WORK_FILE1} 2>/dev/null
##
## execute appropriate form of FNDLOAD
##
case $category in
'MENU')
FNDLOAD apps/$PASSWD O
Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct
${shortname}_menu.ldt
MENU MENU_NAME=${shortname}
;;
'PERSONALIZATION')
FNDLOAD apps/$PASSWD O
Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct
${shortname}_formpersonalization.ldt
FND_FORM_CUSTOM_RULES
function_name=${shortname}
;;
'XML')
FNDLOAD apps/$PASSWD O
Y DOWNLOAD $XDO_TOP/patch/115/import/xdotmpl.lct
${shortname}_tmpl.ldt
XDO_DS_DEFINITIONS APPLICATION_SHORT_NAME=XXLM
DATA_SOURCE_CODE=${shortname}
;;
'XDO')
java
oracle.apps.xdo.oa.util.XDOLoader DOWNLOAD -DB_USERNAME apps -
DB_PASSWORD $PASSWD
-JDBC_CONNECTION
'(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=$EMACH)(PORT=$EPORT))(CONNECT_DATA
=(SERVICE_NAME=$TWO_TASK)))'
-LOB_TYPE TEMPLATE_SOURCE -APPS_SHORT_NAME XXLM
-LOB_CODE ${shortname}
-LANGUAGE en -TERRITORY US -XDO_FILE_TYPE RTF -
FILE_CONTENT_TYPE
'application/rtf' -FILE_NAME ${shortname}.rtf -NLS_LANG
ENGLISH_UNITED_STATES.WE8ISO8859P1
$XDO_TOP/patch/115/import/xdotmpl.lct
;;
##
## additional FNDLOAD command lines for each of the different
## categories of applications entities
##
*)
echo
echo You entered an incorrect category. Valid categories are:
echo MENU, FUNCTION, PRINTER, CONCURRENT, LOOKUP, RESPONSIBILITY DESCFLEX, KEYFLEX, VALUE, PROFILE, REQGROUP, REQSET, XML, XDO;;
esac
sleep 1
## Additional work to attempt to identify the most recently
## created log file
##
## get new list of log
files
ls *log >
${WORK_FILE2}
## find name of newly
created log file (from concurrent mgr process)
FND_LOG=`diff
${WORK_FILE1} ${WORK_FILE2} | grep \> | cut -c3-`
echo "Logfile
is:" ${FND_LOG} | tee -a ${LOGFILE}
## look for obvious
success or failure tokens
grep
"successfully" ${FND_LOG} | tee -a ${LOGFILE}
grep "ORA-"
${FND_LOG} | tee -a ${LOGFILE}

This code interactively prompts the user for the type and name of the entity that will be downloaded. It also uses the name (‘shortname’ parameter) to help label the created .ldt file along with its entity type. This naming convention helps to keep track of which ldt files contain which entities. An upload script for these files would look very similar. The script could be extended or used in conjunction with other scripts to implement a simple configuration process.


References:
Note: 274667.1 FNDLOAD Commands to Download Different Seed Data Types.
Note: 287417.1 Parameters Of Different Configuration Files To Download And Upload Seed Data Types.
Note: 256573.1 How To Download/Upload Descriptive Flexfield With Name $SRS$
EBS Release 11.5.10.2+ : Oracle Applications System Administrator's Guide - Configuration
EBS Release 12.0.4+ : Oracle Applications System Administrator's Guide - Configuration
EBS Release 12.1.3 : Oracle Applications System Administrator's Guide - Configuration
Customization Survival Guide: How to Use E-Business Utilities to Migrate Your Custom Code, Brad Simmons and Donna Campbell,Los Alamos
National Laboratory document
Tips About FNDLOAD [ID 735338.1]
Is It Possible To Use FNDLOAD To Migrate Configurations Or Data To Another Instance? [ID 1316916.1]
How To Use XDOLoader to Manage, Download and Upload Files? [ID 469585.1]