Wednesday, March 7, 2012

How FNDLOAD Utility is useful for Oracle Apps DBA

Hi,
FNDLOAD(Generic Loader) utility is very useful for Apps DBA.Let us try to understand how it works and how we can utilize this utility well.

Understanding FNDLOAD utility:
The Generic Loader (FNDLOAD) is a concurrent program that can download data from an application entity into a portable,editable text file. This file can then be uploaded into any other database to copy the data.
Data structures supported by the Loader include master- detail relationships and foreign key relationships.
FNDLOAD uses script to ensure consistent migration of objects within Oracle Applications

FNDLOAD utility Modes of Operation:

The FNDLOAD(Generic Loader) utility operates in 2 modes:
1)Download mode or
2)Upload mode
In the download mode data is downloaded from a database according to a configuration (.lct) file and then converts the data into a Data (.ldt) file. This data file can be uploaded to a different database.In both downloading and uploading, the structure of the data involved is described by a configuration file.
The configuration file describes the structure of the data and also the access methods use to copy the data into or out of the database.
The same configuration file may be used for both uploading and downloading.
When downloading,the Generic Loader creates a second file, called the data file that contains the structured data selected for downloading.
The data file has a standard syntax for representing the data that has been downloaded.

When uploading,the Generic Loader reads a data file to get the data that it is to upload.In most cases, the data file was produced by a previous download, but may have come from another source.
The data file cannot be interpreted without the corresponding configuration file available.


FNDLOAD utility syntax:

DOWNLOAD COMMAND SYNTAX:

FNDLOAD 0 Y DOWNLOAD <${FND_TOP}/patch/115/import/

UPLOAD COMMAND SYNTAX:

FNDLOAD 0 Y UPLOAD <${FND_TOP}/patch/115/import/

FNDLOAD Usage Details:

FNDLOAD can be used to migrate the following system administrator objects between instances
1.Printer Styles
2.Lookup Types and codes
3.Descriptive Flexfield (DFF)
4.Key Flexfield (KFF)
5.Concurrent programs with the parameters
6.Request Sets (when the programs are not triggered based on success)
7.Value Sets and Value set Values
8.Profiles
9.Request Groups
10.Responsibilities
11.Forms
12.Functions
13.Menus
14.Messages

Merits of FNDLOAD utility:

1.Need to maintain a baseline environment (Source for Clone) and update it on a regular basis
2.Base environment can have issues and the cloning strategy made totally ineffective. In such a case every new environment created will have to be updated with a lot of changes
3.Cloning /Refresh not possible in short intervals
4.Selective replication of setups and AOL objects not possible with cloning
5.Environments not delivered as per timeline and affecting the Testing schedules
6.Manually maintaining Environments at different level of configuration is tedious
7.Time consuming to manually update multiple environment with defect fixes and error prone
8.FNDLOAD is fully supported and recommended by Oracle for migration of FND objects. Requires 0 learning curve and 0 investment.

Demerits of FNDLOAD utility:


1.This utility can be only used for FND (System administrator) objects only.
2.Application Patching mechanisms use FNDLOAD heavily. There is a possibility of negative impact.
3.There is no validation of sensitive data that is being migrated by the FNDLOAD tool itself.

Examples of FNDLOAD utility:

Download: FNDLOAD apps/$pwd O Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct lookup_techops_aris.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME="CN" LOOKUP_TYPE="XXTEST_TECHOPS_ARIS_SITES"

Download: FNDLOAD apps/$pwd O Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct lookup_techops_points.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME="CN" LOOKUP_TYPE="XXTEST_TECHOPS_POINTS_SITES"

Download: FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct concprg_XXTEST_techops_points_procedure.ldt PROGRAM APPLICATION_SHORT_NAME="XXTEST" CONCURRENT_PROGRAM_NAME="XXTEST_TECHOPS_POINTS_PRCS_EAST"

Download: FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct concprg_XXTEST_techops_points_load.ldt PROGRAM APPLICATION_SHORT_NAME="XXTEST" CONCURRENT_PROGRAM_NAME="XXTEST_TECHOPS_POINTS_EAST_LOAD"

Download: FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct concprg_XXTEST_techops_points_load.ldt PROGRAM APPLICATION_SHORT_NAME="XXTEST" CONCURRENT_PROGRAM_NAME="XXTEST_TECHOPS_POINTS_MW_LOAD"

Download: FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct concprg_XXTEST_techops_mttr_load.ldt PROGRAM APPLICATION_SHORT_NAME="XXTEST" CONCURRENT_PROGRAM_NAME="XXTEST_TECHOPS_MTTR_LOAD"

Download: FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct concprg_XXTEST_techops_mttr_process.ldt PROGRAM APPLICATION_SHORT_NAME="XXTEST" CONCURRENT_PROGRAM_NAME="XXTEST_TECHOPS_MTTR_DATA_PROCES"


Upload: FNDLOAD apps/$pwd O Y UPLOAD $FND_TOP/patch/115/import/aflvmlu.lct lookup_techops_aris.ldt

Upload: FNDLOAD apps/$pwd O Y UPLOAD $FND_TOP/patch/115/import/aflvmlu.lct lookup_techops_points.ldt

Upload: FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct concprg_xxtest_techops_points_procedure.ldt

Upload: FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct concprg_xxtest_techops_points_load.ldt

Upload: FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct concprg_xxtest_techops_points_load.ldt

Upload: FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct concprg_xxtest_techops_mttr_load.ldt

Upload: FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct concprg_xxtest_techops_mttr_process.ldt


Enjoy and Happy Oracle Apps DBA learning.

Hope it helps...


Best regards,

Rafi.

3 comments:

  1. FNDLOAD is a powerful utility. But it still requires a significant manual effort to have the migrations commands defined and prepared, moving generated file, checking log files etc.. That is where MigrationLogiK - GUI app for FNDLOAD comes into place. It allows to shrink migration time into seconds while having all migrations organized into clear folder based structure. Minimal maintenance effort and high intuitiveness attracts Oracle users and DBAs. Check http://migrationlogik.com

    ReplyDelete
  2. Rafi - good article but 1 quick question. In your examples it looks like you're using an "O" instead of a "0" to precede the Y. Is this correct?

    ReplyDelete
  3. Hi Michael,

    It is "0" actually not "O".Thanks for noticing.

    ReplyDelete