What is FNDLOAD in Oracle Apps

In this article, you will understand FNDLOAD Commands in Oracle Apps. Also you will see how this is used to move Oracle EBS R12 FND objects between database using text based (.ldt) file. The loader reads a configuration (.lct) file to determine what data to access. Moreover, it can be used with several different entities/data types within Oracle e-Business Suite(Oracle eBS).

Table of Contents

FNDLOAD Commands in Oracle Apps using Text File
Overview of FNDLOAD Commands in Oracle Apps R12
FNDLOAD Commands in Oracle Apps R12 for XML Template
FNDLOAD Commands in Oracle Apps for Data Definition and Template
Important Point of FNDLOAD Commands in Oracle Apps R12
Main Features of FNDLOAD Commands in Oracle Apps R12

Examples in FNDLOAD Commands in Oracle Apps include

• FNDLOAD request group for concurrent program
• FNDLOAD for Concurrent Programs
• Download and upload Forms Personalization using FNDLOAD
• FNDLOAD for Data Definition

Oracle Apps FNDLOAD can be used to migrate Concurrent Programs, Request Sets, Request Group, Key FlexFields, Descriptive Flexfields, Users, Responsibilities and almost every other FND entity.

FNDLOAD Commands in Oracle Apps using Text File

• Initially, it extracts database information into a human-readable and portable text file, allowing for easy examination and modification using any standard text editor.
• Subsequently, it utilizes a “developer key,” such as the PROFILE_OPTION_NAME, to identify records within the Profiles configuration file, ensuring accurate record identification.

Upload Text File using FNDLOAD Scripts in Oracle Apps R12

• Firstly, it facilitates the seamless transfer of information from a text file back into the database.
• Additionally, enable the update of existing rows with different attributes and the insertion of new rows. However, note that deletion of rows is not supported.

Facilitate Data Propagation Across Databases:

• Moreover, this functionality supports the easy dissemination of profile value information from one database to others, proving particularly useful for delivering Oracle E-Business Suite seed data to customers. Additionally, it facilitates the replication of customer profile definitions across multiple sites.

Enhance Efficiency in Bulk Editing Operations:

• Leverage the text file version of profile value data for streamlined bulk editing operations, optimizing efficiency through text editor modifications instead of using a traditional form.

Ensure Data Preservation and Update Handling:

• Safeguard data integrity by utilizing attributes like OWNER and LAST_UPDATE_DATE for determining data overwrite, insertion, or update while uploading or downloading.
• Also, it enforces specific rules to manage entity insertion and update based on file and database attributes, ensuring data consistency.

Support Online Patching with Seed Data Manager:

• Additionally, it implements editioned storage of seed data tables to support online patching. Furthermore, it employs the Seed Data Manager for centralized logic, facilitating the implementation of online seed data patching. This approach is instrumental in upgrading seed data tables during Oracle E-Business Suite Release 12.2 upgrade.

Integration with FNDLOAD and Loader Configurations:

• Moreover, it integrates seamlessly with the Seed Data Manager’s “Prepare” API to signal upcoming uploads and specify tables requiring patch edition data copies.
• Additionally, it extends FNDLOAD functionality to accommodate the new PREPARE statement for diverse loader configurations.

Operate as a Concurrent Program:

This is also function as a concurrent program named FNDLOAD, accepting specific parameters for uploading or downloading data, including the mode, configuration file, data file, and additional parameters.

File Specifications and Examples:

• Firstly. It defines the file specifications for configuration and data files, providing both native paths and application-specific formats.
• Also, it offers detailed examples showcasing downloading and uploading processes using FNDLOAD commands.

Configuration File Structure and Entity Definition:

Firstly, it governs the operation of the Generic Loader through a structured configuration file. Also, it is encompassing DEFINE, DOWNLOAD, and UPLOAD blocks to delineate data structure and access methods. Moreover, define entity attributes within the configuration file using KEY, BASE/TRANS/CTX attributes, nested entity definitions, and foreign key references to establish master-detail relationships and data types.

FNDLOAD Scripts in Oracle Apps R12

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 …]

Example of FNDLOAD Commands in Oracle Apps R12

#Download FNDLOAD Commands in Oracle Apps R12
FNDLOAD apps/APPS_PASSWORD 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct fndloadconcurrentfile.ldt PROGRAM CONCURRENT_PROGRAM_NAME= <concurrent_program_short_name> APPLICATION_SHORT_NAME=<application_short_name>

#Upload FNDLOAD Commands in Oracle Apps R12
FNDLOAD apps/APPS_PASSWORD 0 Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct fndloadconcurrentfile.ldt - CUSTOM_MODE=FORCE parameter

Overview of FNDLOAD Commands in Oracle Apps R12

FNDLOAD Request Group for Concurrent Program:

Download FNDLOAD Request Group for Concurrent Program

FNDLOAD apps/$APPS_PASSWORD O Y DOWNLOAD FND_TOP/patch/115/import/afcpreqg.lct<br>XX_REQ_GRP.ldt REQUEST_GROUP REQUEST_GROUP_NAME="XX_REPORT_GROUP_SRT_NAME" APPLICATION_SHORT_NAME="XXGMS"

Upload FNDLOAD Request Group for Concurrent Program

#FNDLOAD Script for Request Group
FNDLOAD apps/$APPS_PASSWORD  O Y UPLOAD $FND_TOP/patch/115/import/afcpreqg.lct

FNDLOAD Script to Download Concurrent Program

Download Script for Concurrent Program in Oracle EBS R12

#Download FNDLOAD Script for Concurrent Program
FNDLOAD apps/$APPS_PASSWORD  O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct
XX_CONC_PROG.ldt PROGRAM APPLICATION_SHORT_NAME="XXDEAS"
CONCURRENT_PROGRAM_NAME="XX_CONC_PROG_SRT_NAME"

Upload Script for Concurrent Program in Oracle EBS R12

#Upload FNDLOAD Script for Concurrent Program
FNDLOAD apps/$APPS_PASSWORD  O Y UPLOAD $FND_TOP/patch/115/import/afcpreqg.lct XX_CONC_PROG.ldt

FNDLOAD Script for Value Set:

FNDLOAD for Value sets with out any values

FNDLOAD apps/$APPS_PASSWORD  0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct XXENO_VALUE_SET_NAME.ldt VALUE_SET FLEX_VALUE_SET_NAME="<VALUE_SET_NAME>"

FNDLOAD apps/$APPS_PASSWORD 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct XXENO_VALUE_SET_NAME.ldt

FNDLOAD for Value sets with values

FNDLOAD apps/$APPS_PASSWORD 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct XXENO_VALUE_SET_VALUES.ldt VALUE_SET_VALUE FLEX_VALUE_SET_NAME="<VALUE_SET_NAME>"

FNDLOAD apps/$APPS_PASSWORD 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct XXENO_VALUE_SET_VALUES.ldt

FNDLOAD Descriptive Flexfields:

Query to Find the Name of DFF in Oracle Apps :

SELECT application_id, DESCRIPTIVE_FLEXFIELD_NAME, application_table_name
  FROM fnd_descriptive_flexs_vl
 WHERE APPLICATION_TABLE_NAME like '%' || upper('&tab_name') || '%'
ORDER BY APPLICATION_TABLE_NAME

FNDLOAD download Script for Descriptive Flexfield

FNDLOAD apps/$APPS_PASSWORD 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct
XX_PO_REQ_HEADERS_DFF.ldt DESC_FLEX APPLICATION_SHORT_NAME=PO
DESCRIPTIVE_FLEXFIELD_NAME='PO_REQUISITION_HEADERS'

FNDLOAD Upload Script for Descriptive Flexfield

FNDLOAD apps/$APPS_PASSWORD 0 Y UPLOAD $FND_TOP/patch/115/import/afffload.lct
XX_PO_REQ_HEADERS_DFF.ldt

FNDLOAD Command for Lookup:

FNDLOAD apps/$APPS_PASSWORD 0 Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct XX_ENO_ACCT_TYPES.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME="XXENO" LOOKUP_TYPE="XX_ENO_ACCT_TYPES"

FNDLOAD apps/$APPS_PASSWORD 0 Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct XX_ENO_ACCT_TYPES.ldt

FNDLOAD apps/$APPS_PASSWORD 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct
XX_FND_COMMON_LOOKUPS_DFF.ldt DESC_FLEX APPLICATION_SHORT_NAME=FND
DESCRIPTIVE_FLEXFIELD_NAME='FND_COMMON_LOOKUPS'

FNDLOAD Commands for Menu

Download FNDLOAD Commands for Menus

FNDLOAD apps/$APPS_PASSWORD O Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct
ICX_POR_SSP_HOME.ldt MENU MENU_NAME="ICX_POR_SSP_HOME"

Upload FNDLOAD Commands for Menus

FNDLOAD apps/$APPS_PASSWORD O Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct
ICX_POR_SSP_HOME.ldt MENU MENU_NAME="ICX_POR_SSP_HOME"

FNDLOAD Script for Request Sets

As, request sets contain the stages and links for underlying concurrent programs. So migrating request sets we firstly need to download the request set definition. After that we need to download the Sets Linkage definition. Well, lets be clear here, the above sequence is more important while FNDLOAD of Request set upload.

SQL to Find the Request Set Name

SELECT request_set_name
  FROM fnd_request_sets_vl
 WHERE user_request_set_name = 'request set name'

FNDLOAD Download Script for Requet Sets

#Download Definition
FNDLOAD apps/$APPS_PASSWORD 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct
XX_GL_REQ_SET.ldt REQ_SET REQUEST_SET_NAME="FNDRSSUB4610101"

#Download set linkage
FNDLOAD apps/$APPS_PASSWORD 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct
XX_GL_MY_INTERFACE_SET_LINK.ldt REQ_SET_LINKS
REQUEST_SET_NAME="FNDRSSUB4610101"

FNDLOAD upload Script for Requet Sets

#Upload Definition
FNDLOAD apps/$APPS_PASSWORD 0 Y UPLOAD $FND_TOP/patch/115/import/afcprset.lct
XX_GL_MY_INTERFACE_SET.ldt

#Upload set linkage
FNDLOAD apps/$APPS_PASSWORD 0 Y UPLOAD $FND_TOP/patch/115/import/afcprset.lct
XX_GL_MY_INTERFACE_SET_LINK.ldt

FNDLOAD Commands in Oracle Apps Responsibilities

Download FNDLOAD for responsibility in Oracle Apps R12

#Download FNDLOAD script for responsibility
FNDLOAD apps/$APPS_PASSWORD O Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct
XX_RESPY.ldt FND_RESPONSIBILITY RESP_KEY="XX_PERSON_RESPY"

FNDLOAD Upload Command for Responsibility

#Upload FNDLOAD script for responsibility
FNDLOAD apps/$APPS_PASSWORD O Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct
XX_RESPY.ldt FND_RESPONSIBILITY RESP_KEY="XX_PERSON_RESPY"

Scripts for FNDLOAD Form Personalization:

Download FNDLOAD Script for Form Personalization

#Personalization for first function
FNDLOAD apps/$APPS_PASSWORD 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct
XX_PERWSHRG.ldt FND_FORM_CUSTOM_RULES function_name="PERWSHRG-404"

#Personalization for second function
FNDLOAD apps/$APPS_PASSWORD 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct
XX_HZ_ARXCUDCI_STD.ldt FND_FORM_CUSTOM_RULES function_name="HZ_ARXCUDCI_STD"

#Personalization for last function
FNDLOAD apps/$APPS_PASSWORD 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct
XX_AP_APXVDMVD.ldt FND_FORM_CUSTOM_RULES function_name="AP_APXVDMVD"

FNDLOAD Upload Script for Form Personalization

#Personalization for First function
FNDLOAD apps/$APPS_PASSWORD 0 Y UPLOAD $FND_TOP/patch/115/import/affrmcus.lct XX_PERWSHRG.ldt

#Personalization for second function

FNDLOAD apps/$APPS_PASSWORD 0 Y UPLOAD $FND_TOP/patch/115/import/affrmcus.lct
XX_HZ_ARXCUDCI_STD.ldt

#Personalization for Last function
FNDLOAD apps/$APPS_PASSWORD 0 Y UPLOAD $FND_TOP/patch/115/import/affrmcus.lct XX_AP_APXVDMVD.ldt

FNDLOAD Commands in Oracle Apps for Forms, Messages and Alert

FNDLOAD Commands in Oracle Apps for Forms

FNDLOAD apps/$APPS_PASSWORD O Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct XXENO_FORM_NAME.ldt FORM APPLICATION_SHORT_NAME="XXENO" FORM_NAME="XXENO_FORM_NAME"

FNDLOAD apps/$APPS_PASSWORD O Y UPLOAD $FND_TOP/patch/115/import/afsload.lct XXENO_FORM_NAME.ldt

FNDLOAD Commands in Oracle Apps for Form Functions

FNDLOAD apps/$APPS_PASSWORD O Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct XXPO_FUNCTION.ldt FUNCTION FUNC_APP_SHORT_NAME="XXPO" FUNCTION_NAME="XXPO_FUNCTION"

FNDLOAD apps/$APPS_PASSWORD O Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct XXPO_FUNCTION.ldt 

FNDLOAD Commands in Oracle Apps for Messages

FNDLOAD apps/$APPS_PASSWORD 0 Y DOWNLOAD $FND_TOP/patch/115/import/afmdmsg.lct XX_PO_POR_RECEIPT_ERR01.ldt FND_NEW_MESSAGES APPLICATION_SHORT_NAME='PO' MESSAGE_NAME=XX_PO_POR_RECEIPT_ERR01

FNDLOAD apps/$APPS_PASSWORD 0 Y DOWNLOAD $FND_TOP/patch/115/import/afmdmsg.lct XXENO_ALL_MESSAGES_00.ldt FND_NEW_MESSAGES APPLICATION_SHORT_NAME='XXENO'

FNDLOAD apps/$APPS_PASSWORD 0 Y UPLOAD $FND_TOP/patch/115/import/afmdmsg.lct XXENO_ALL_MESSAGES_00.ldt

FNDLOAD Commands in Oracle Apps for Alerts

FNDLOAD apps/$APPS_PASSWORD 0 Y DOWNLOAD $ALR_TOP/patch/115/import/alr.lct PO_Receipts.ldt ALR_ALERTS APPLICATION_SHORT_NAME='INV' ALERT_NAME='PO Receiving Notification'

FNDLOAD apps/$APPS_PASSWORD 0 Y DOWNLOAD $ALR_TOP/patch/115/import/alr.lct PO_Receipts.ldt

FNDLOAD Commands in Oracle Apps for Data Definition and Template

Download FNDLOAD Script for Data Definition and Template

FNDLOAD apps/$APPS_PASSWORD 0 Y DOWNLOAD $XDO_TOP/patch/115/import/xdotmpl.lct XXBOM_MULTILVL_BOM_RPT_XDO.ldt XDO_DS_DEFINITIONS APPLICATION_SHORT_NAME= "XXDEAS" DATA_SOURCE_CODE="XXBOM_MULTILVL_BOM_RPT" TMPL_APP_SHORT_NAME= "XXBOM" TEMPLATE_CODE="XXBOM_MULTILVL_BOM_RPT"

Upload FNDLOAD Script for Data Definition and Template

FNDLOAD apps/$APPS_PASSWORD 0 Y DOWNLOAD $XDO_TOP/patch/115/import/xdotmpl.lct XXBOM_MULTILVL_BOM_RPT_XDO.ldt

FNDLOAD Commands in Oracle Apps R12 for XML Template 

Since, the XDOLoader utility, a Java-based command line tool, this utility serves the purpose of loading various file types—such as RTF, PDF, and XSL-FO—alongside XML and XSD files into the XML Publisher database tables. Moreover, it provides a practical means to seamlessly transfer files from one instance to another.

This utility operates in two distinctive modes:

1. File Download Only Mode: In this mode, facilitates the downloading of files from the XDO_LOBS table. Moreover, users can specify the target LOB_CODE, APPS_SHORT_NAME, LOB_TYPE, LANGUAGE, and TERRITORY to retrieve all files that match the specified criteria.
2. File Download and LDT/DRVX Generation Mode: In this mode, files are downloaded from the XDO_LOBS tables, and subsequently, an LDT file for the downloaded file is created.

Download FNDLOAD Script for XML Template 

java oracle.apps.xdo.oa.util.XDOLoader DOWNLOAD \
-DB_USERNAME <db_username> \
-DB_PASSWORD <db_password> \
-JDBC_CONNECTION $p_host:$p_port:$p_instance \
-LOB_TYPE <lob_type> \
-APPS_SHORT_NAME <application_short_name> \
-LOB_CODE <lob_code> \
-LANGUAGE <language> \
-TERRITORY <territory> \
-LOG_FILE <log file>

#Example of XDOLoader
java oracle.apps.xdo.oa.util.XDOLoader DOWNLOAD \
-DB_USERNAME apps \
-DB_PASSWORD apps \
-JDBC_CONNECTION enodeas05:1521:dev \
-LOB_TYPE TEMPLATE \
-APPS_SHORT_NAME XDO \
-LOB_CODE XDOTMPL1 \
-LANGUAGE en \
-TERRITORY US

The parameters are described in the following table:

Upload FNDLOAD Script for XML Template

java oracle.apps.xdo.oa.util.XDOLoader UPLOAD \
 -DB_USERNAME apps \
 -DB_PASSWORD $p_apps_pwd \
 -JDBC_CONNECTION $p_host:$p_port:$p_instance \
 -LOB_TYPE TEMPLATE \
 -APPS_SHORT_NAME AR \
 -LOB_CODE ARBRFMTFLCR \
 -LANGUAGE en \
 -TERRITORY 00 \
 -XDO_FILE_TYPE RTF \
 -FILE_NAME ARBRFMTFLCR_Template.rtf \
 -CUSTOM_MODE FORCE \

Important Point of FNDLOAD Commands in Oracle Apps R12

1. In case your client has several developers modifying Responsibilities and Menus, it’s imperative to be ultra careful. So, if you are not careful, it might result in untested Forms and Functions becoming available in your client’s Production environment, alongside your tested forms, functions, and menus.

2. Be very careful when downloading flexfields that reference value sets with independent values for GL Segment Codes. By doing so, you will download and extract all the test data in GL Codes that might not be applicable for production.

3. There are several variations possible for FNDLOAD, for example you can restrict the download and uploads to specific segments within Descriptive Flex Fields.

4. As the name suggests, FNDLOAD is indeed a valuable tool for handling FND-related objects. However, in the course of virtually any implementation, the need to migrate setups in Financials and Oracle HRMS from one environment to another inevitably arises. For this purpose, you can fortunately turn to iSetup, also known as ‘Oracle iSetup’. Moreover, iSetup empowers you to migrate a wide array of crucial elements, including:

• GL Set of Books
• HR Organization Structures
• HRMS Employees
• Profile Options Setup
• Suppliers
• Customers
• Tax Codes & Tax Rates
• Financials Setup
• Accounting Calendars
• Chart of Accounts
• GL Currencies

Consequently, by effectively leveraging iSetup, you can ensure seamless and efficient migration of essential setups within your Oracle environments.

Main Features of FNDLOAD Commands in Oracle Apps

Seamless Data Transfer

Seamless data transfer orchestrates effortless movement of data between application entities by extracting and converting database information into an editable text file. This process significantly simplifies data handling across various entities and applications. Furthermore, this method ensures the swift and secure transfer of information from one platform to another.

Configuration-Driven Transformation

Driven by a configuration file, this transformation process specifies conversion procedures, guiding database-to-file and file-to-database transformations. Consequently, this approach guarantees precision and accuracy in data transformations. This method streamlines the transfer process, ensuring that data is accurately transformed and transferred as needed.

Dual-Mode Operations

Operating in two distinctive modes—download and upload—this system expertly manages the flow of data. In the download mode, data moves from the database to a text file. Subsequently, in the upload mode, data transfers from a text file back into the database. This duality offers exceptional flexibility and control over data transfer activities.

Support for Diverse Data Structures

Capable of handling intricate data structures, including master-detail relationships and foreign key references, it ensures comprehensive data transfer capabilities. This support actively maintains the integrity and completeness of transferred data. Moreover, by accommodating various data structures, it guarantees a smooth transfer process regardless of complexity.

Comprehensive Configuration File Usage

Leveraging a single configuration file for both uploading and downloading tasks streamlines processes. This comprehensive file usage optimizes data management efficiency. As a result, it simplifies the process for managing and manipulating data effectively.

Structured Data File Creation

During download operations, the system generates a structured data file representing selected data in a standardized syntax. This structured file creation ensures easily interpretable data for future actions. By organizing data systematically, it facilitates swift and accurate actions.

Data Retrieval in Uploads

While performing upload operations, the system reads the data file to retrieve the necessary information for transfer back into the database. This process ensures the accuracy and completeness of the data being uploaded. Through meticulous retrieval, it guarantees the integrity of the uploaded data.

Interdependence of Data and Configuration Files

Firstly, highlighting the necessity of the configuration file in interpreting data file contents for successful data transfer. Also, this interdependence ensures the seamless integrity of data throughout the transfer cycle. Additionally, this synergy between files is critical for maintaining data coherence.

Flexibility in Data File Origin

Recognizing the primary source of the data file from previous downloads, it can potentially originate from alternative sources. This flexibility allows adaptation to diverse data sources, thereby enhancing overall adaptability and compatibility.