FNDLOAD commands to upload and download setups in R12 Oracle apps instance

FNDLOAD utility is provided by oracle to migrate setups from one instance to another. This is capable of migrating most of the common setups in an Oracle apps instance. This covers most of the setups stored in FND schema. Steps is, perform the setups manually in the source instance (Eg: Development or test instance) and download the ldt file. This is a regular text file – not any executable or so. We can view the file and validate/correct the data if required. Then we can FTP this file in ASCII mode to the target instance and then upload this ldt file with a valid command. Few of the commonly used components migrated using FNDLOAD utility are as follows (detailed list is mentioned towards the end of the post):

1.       Concurrent Programs

2.       Request Groups

3.       Request Sets

4.       Menus

5.       Responsibilities

6.       Form Registration

7.       Function

8.       Valueset

9.       Profiles

10.   Descriptive Flex Fields

11.   Key Flex Fields

12.   Messages

13.   AK Region and Attributes

14.   JTF Meta data

15.   Form Personalization

16.   Printer Styles

17.   User Definitions

18.   Alert

19.   XMLP Template definitions

20.   Attachments

21.   FND Dictionary

22.   HELP Configuration

23.   Document sequences

24.   Concurrent Manager Schedules

Concept:

FNDLOAD utility is located in $FND_TOP/bin folder. Oracle has provided configuration files for downloading each of these setups. These configuration files end with extension .lct. These are located in $FND_TOP/patch/115/import/ folder.

Syntax:

FNDLOAD $APPSLOGIN 0 Y <Mode> <lct file> <ldt data file> <Option> <Parameters>

$APPSLOGIN environment variable stores the apps username and password. If not replace this with apps/<pwd>@SID

Mode is either DOWNLOAD or UPLOAD

Lct file is the full path to the configuration file

Ldt file is the data file that will be downloaded or uploaded

Option is the setup that we want to download

Parameters if we want to certain part of the setup (may not be applicable for all)

Few notes:

1.       If the target instance already has a value and you are trying to delete it, the FNDLOAD may not help, it only updates or inserts data, not delete. Those might have to be done manually

2.       As said, it can migrate FND setups, for those of financials or HRMS setups migration we might have to use Oracle iSetup

3.       Be careful while downloading the values, might move some of the dev/test instance values to PROD by mistake!

4.       I recommend downloading the ldts from PROD, load to dev, then make required changes on top to ensure valid changes are present

5.       While migrating request group etc, I recommend migrating only the unit that was added rather than full request group. This will avoid moving redundant data if someone had added other programs as well to the request group in the dev/test environment

6.       Form personalizations – try to download all personalizations. It generally overwrites personalizations in the form

Example commands:

1.       Concurrent Programs

FNDLOAD $APPSLOGIN O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct XX_CP.ldt PROGRAM APPLICATION_SHORT_NAME="APP_SHORT_NAME" CONCURRENT_PROGRAM_NAME="CP_SHORT_NAME"

2.       Request Groups

a.       Complete Request Group

FNDLOAD $APPSLOGIN 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct XX_MY_REPORT_GROUP_NAME.ldt REQUEST_GROUP REQUEST_GROUP_NAME="XX_MY_REPORT_GROUP_NAME" APPLICATION_SHORT_NAME="APP_SHORT_NAME"

b.      One unit in the Request Group

FNDLOAD $APPSLOGIN  0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct XXReqgrp_XX_MY_REPORT.ldt REQUEST_GROUP REQUEST_GROUP_NAME="XX_MY_REPORT_GROUP_NAME" UNIT_APP=APP_SHORT_NAME UNIT_NAME= XX_MY_REPORT

3.       Request Sets

a.       Entire Request Set

FNDLOAD $APPSLOGIN 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct XX_ REPORTS_SET.ldt REQ_SET REQUEST_SET_NAME="XX_REPORTS_SET"

b.      Request Set Stage

FNDLOAD $APPSLOGIN 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct XX_ REPORTS_SET.ldt  REQ_SET_LINKS REQUEST_SET_NAME="XX_REPORTS_SET" STAGE_NAME=”XX_STAGE_NAME”

c.       Program in a Request set

FNDLOAD $APPSLOGIN 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct XX_ REPORTS_SET.ldt  REQ_SET_LINKS REQUEST_SET_NAME="XX_REPORTS_SET" STAGE_NAME=”XX_STAGE_NAME” SEQUENCE=”XX_SEQUENCE"

4.       Menus

FNDLOAD $APPSLOGIN O Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct XX_MENU.ldt MENU MENU_NAME="XX_MENU"

5.       Responsibilities

FNDLOAD $APPSLOGIN O Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct XX_RESPY.ldt FND_RESPONSIBILITY RESP_KEY="XX_RESPY"

6.       Form Registration

FNDLOAD $APPSLOGIN O Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct XX_FORM.ldt FORM FORM_NAME="XX_FORM"

7.       Menu Function

FNDLOAD $APPSLOGIN O Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct XX_FUNCTION.ldt MENU PARENT_MENU_NAME=”XX_PARENT_MENU” FUNCTION_NAME=”XX_FUNCTION_NAME”

8.       Valueset

$FND_TOP/bin/FNDLOAD $APPSLOGIN 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct XX_VS.ldt VALUE_SET FLEX_VALUE_SET_NAME=XX_VS

9.       Profile Options

FNDLOAD $APPSLOGIN O Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct XX_PROF.ldt PROFILE PROFILE_NAME="XX_PROF" APPLICATION_SHORT_NAME="FND"

10.   Descriptive Flex Fields

a.       Full DFF

$FND_TOP/bin/FNDLOAD $APPSLOGIN 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct XX_DFF.ldt DESC_FLEX APPLICATION_SHORT_NAME=PO DESCRIPTIVE_FLEXFIELD_NAME='PO_REQUISITION_HEADERS'

b.      One context in a DFF

FNDLOAD $APPSLOGIN O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct <LDT FileName>.ldt DESC_FLEX APPLICATION_SHORT_NAME='FND' DESCRIPTIVE_FLEXFIELD_NAME='FND_FLEX_VALUES'  DESCRIPTIVE_FLEX_CONTEXT_CODE='Context Name'

11.   Key Flex Fields

FNDLOAD $APPSLOGIN O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt KEY_FLEX P_LEVEL=COL_ALL:FQL_ALL:SQL_ALL:STR_ONE:WFP_ALL:SHA_ALL:CVR_ALL:SEG_ALL APPLICATION_SHORT_NAME=”FND” ID_FLEX_CODE=”key flex code” P_STRUCTURE_CODE=”structure name”

12.   Messages

FNDLOAD $APPSLOGIN 0 Y DOWNLOAD $FND_TOP/patch/115/import/afmdmsg.lct

XX_MSG.ldt FND_NEW_MESSAGES APPLICATION_SHORT_NAME='FND' MESSAGE_NAME=XX_MSG

13.   AK Region and Attributes

14.   JTF Meta data

FNDLOAD $APPSLOGIN 0 Y DOWNLOAD $JTF_TOP/patch/115/import/jtfgrid.lct XX_DS.ldt JTF_GRID_DATASOURCES GRID_DATASOURCE_NAME=XX_DS

15.   Form Personalization

FNDLOAD $APPSLOGIN 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct XX_ARXTWMAI.ldt FND_FORM_CUSTOM_RULES function_name="XX_ARXTWMAI_10"

16.   Printer/Printer Styles

FNDLOAD $APPSLOGIN O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprnt.lct XX_PRINTER.ldt PRINTER PRINTER_NAME ="XXPRN”

FNDLOAD $APPSLOGIN O Y DOWNLOAD $FND_TOP/patch/115/import/afcppstl.lct XX_PS.ldt STYLE PRINTER_STYLE_NAME=”printer style name”

17.   User Definitions

FNDLOAD $APPSLOGIN 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct XX_USER.ldt FND_USER USER_NAME='XX_USER'

18.   Alert

FNDLOAD $APPSLOGIN 0 Y DOWNLOAD $ALR_TOP/patch/115/import/alr.lct XX_ALERT1.ldt ALR_ALERTS APPLICATION_SHORT_NAME=FND ALERT_NAME=XX_ALERT1

19.   XMLP Template Data definitions

FNDLOAD $APPSLOGIN O Y DOWNLOAD $XDO_TOP/patch/115/import/xdotmpl.lct XX_ DATA_DEF_NAME.ldt XDO_DS_DEFINITIONS APPLICATION_SHORT_NAME=”XXAR” DATA_SOURCE_CODE=”XX_DATA_DEF_NAME”

a.       XMLP Templates needs to be moved via XDOLOADER utility

java oracle.app.do.ox.until.XDOLoader DOWNLOAD \
-DB_USERNAME $APPS_USER \
-DB_PASSWORD $APPS_PWD \
-JDBC_CONNECTION $JDBC_LOGIN \
-LOB_TYPE <TEMPLATE/XML_SCHEMA> \
-APPS_SHORT_NAME XX_AR \
-LOB_CODE <template code/ DD code> \
-LANGUAGE en \
-TERRITORY US

20.   Attachments

FNDLOAD $APPSLOGIN O Y DOWNLOAD $FND_TOP/patch/115/import/afattach.lct XX_ATTACMENT.ldt FND_ATTACHMENT_FUNCTIONS APPLICATION_SHORT_NAME="XXAR"

21.   FND Dictionary

FNDLOAD $APPSLOGIN O Y DOWNLOAD $FND_TOP/patch/115/import/afdict.lct XX_TABLE.ldt FND_TABLE APPLICATION_SHORT_NAME="XXAR"

22.   Document sequences

FNDLOAD $APPSLOGIN O Y DOWNLOAD $FND_TOP/patch/115/import/afsncat.lct XX_SEQ.ldt FND_DOC_SEQUENCE_CATEGORIES APP_SHORT_NAME ="XXAR”

23.   Concurrent Program Schedules

FNDLOAD $APPSLOGIN O Y DOWNLOAD $FND_TOP/patch/115/import/afcpsch.lct XX_SCHEDULE.ldt SCHEDULE APPLICATION_SHORT_NAME ="XXAR"

24.   Lookups

FNDLOAD $APPSLOGIN 0 Y DOWNLOAD aflvmlu.lct XX_LKP.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME ='XXAR' LOOKUP_TYPE="XX_LKP1"

25.   Currency/Territory/Language

FNDLOAD $APPSLOGIN O Y DOWNLOAD $FND_TOP/patch/115/import/afnls.lct XX_CURRENCY.ldt FND_CURRENCY CURR_CODE ="XXINR"

FNDLOAD $APPSLOGIN O Y DOWNLOAD $FND_TOP/patch/115/import/afnls.lct XX_CURRENCY.ldt FND_TERRITORY TERR_CODE ="XXIND"

FNDLOAD $APPSLOGIN O Y DOWNLOAD $FND_TOP/patch/115/import/afnls.lct XX_CURRENCY.ldt FND_LANGUAGE LANG_CODE ="XXKAN"

Reference for below table:

http://www.oracleappshub.com/oracle-application/great-fndload-part-ii/

Host Programs - calling shell script as concurrent program

There are multiple scenarios when we want to have a shell script as a concurrent program. Some of the very common reasons to go for shell scripts could be –

-          Involves file operations like loading a file and then processing it or generating an output file

-          Involves Encryption and or transmission

-          Involves multithreading

Disadvantages of having a Host Program:

-          Impact analysis is a little cumbersome since all our code is present in a host file. Searching through the text is required for analysis

-          Since it requires unix skill as well along with the database programming, effort required is more

Below are the steps to be followed to create host based programs:

1.       Create a shell script

Create a shell script which fulfills the business needs. The program is not required to be having extension .prog though it is the most commonly used extension. Once the script is ready, FTP the script in ASCII mode to corresponding application TOP’s bin folder ie if I’m developing under custom PO, I might place the file under $XXPO_TOP/bin location. The script should be given read and execute permission

2.       Create symbolic link to fndcpesr

To execute a script via Oracle apps, we need to pass 4 mandatory parameters. Alternatively (and most commonly) we create a link to the fndcpesr file present in $FND_TOP/bin. fndcpesr takes care of passing these 4 parameters to the script

ln –fs $FND_TOP/bin/fndcpesr $XX_TOP/bin/XX_CUSTOM

Once the link is created first 4 parameters will always be passed to the shell script ie $1 to $4:

$1 -> User Password

$2 -> User ID

$3 -> User Name

$4 -> Request ID

All other parameters will be taken

3.       Create executable, concurrent program and assign it to the request group

Notes:

• If there are more than 5 parameters, then you might have to use {} to fetch the values Eg: xx_val=${10}
• We can connect to DB using below command:

Xx_value=`sqlplus -s <<EOSQL

$APPSLOGIN

SET SERVEROUTPUT ON SIZE 1000000

WHENEVER SQLERROR EXIT SQL.SQLCODE

DECLARE

….

BEGIN

…..

DBMS_OUTPUT.PUT_LINE(‘Y’); -- this is the value which will be assigned to the variable Xx_value from sqlplus

EXCEPTION

               WHEN OTHERS THEN

               DBMS_OUTPUT.PUT_LINE('SQLERRM: '||SQLERRM);

               END;

               /

EXIT

EOSQL`

• We can mark the program as success, error or warning by exiting with the value 0, 1 and 2 respectively Eg: exit 0 at the end of the code will complete the concurrent program with success
• We can use commonly used utilities like sqlldr or ftp or sftp or mail etc in the host scripts
• We can submit child concurrent programs from host programs in two ways. 

1. By connecting to DB and submitting the concurrent program via fnd_request.submit_request 
2. By using CONCSUB utility

Why upgrade to Oracle R12?

This is one of the common doubts in everyone's mind; Why we should upgrade to R12? Is it worth spending so much of money and effort? Following are few important reasons:

1. Oracle will no longer support 11i. As per one of the oracle releases, following are the support termination dates for different versions of 11i


2. R12 is the Global Business Release

• R12 enables you to think, work and manage systems globally
• Management can make more informed decisions because of better business intelligence capabilities
• Comprehensive integration capability by adopting industry standards like XML, messages, webservices and Java &amp;PL/SQL APIs
• Integrate Oracle, 3rd party and legacy applications via BPEL(Business Process Execution Language) to extend process automation
• Supports all languages and localizations in the same database. Scales to very large volumes via RAC and Grid computing

3. R12 is more comprehensive solution. It comes up with more than 2350 new capabilities/functionality. It has few new modules like Ebusiness Tax, Payment etc.

4. It has centralized the scattered data Eg: Banks Accounts, Suppliers, Tax etc. This has improved the ability to meet statutory requirements and provides more comprehensive reports. Centralized data maintenance has reduced the user load also allowed Oracle to introduce new functionality like AP/AR Netting, Refund etc

5. Improved user experience.

• Many screens have moved from Forms based UI to self service OAF pages. This has allowed better look and feel and has given more freedom to personalize the screen as per user likings
• Reduced pop-ups and duplicate screens
• Redesigned and streamlined UI for easier data entry and access
• Reduced number of steps to complete key activities. Guided navigation to assist even new users

6. Features like Multi Org Access Control or Data Access Sets etc improve user productivity significantly and also comply regulatory requirements

7. Business and Industry specific processes are streamlined and incorporated in R12. This helps customers to comply with International Regulations and Standards

8. There are more options to configure the system as per company processes, reducing the customizations

9. Keep yourself updated with changing technology trends and reap the benefits of latest technology via improved security, usability and productivity. R12 comes up with new technologies like XML Publisher, OAF, BPEL, OBIEE etc

10. R12 comes with significant application and architecture changes. Moving to R12 will build a foundation to prepare for Fusion, minimizing the Risk

11. R12 has centralized some of the scattered functions. Eg:

• E-Biz Tax module replaces individual tax modules within AR, AP, PO etc and acts as central repository for all tax data and integration with 3rd party tax service providers
• Sub Ledger Accounting module acts as accounting repository before interfacing to GL. This configurable module allows companies to implement complex accounting and reporting rules
• Payments module now consists of funds disbursement (previously part of AP) and funds capture (previously part of AR) functions
• Cash Management module is made as owner of all internal bank accounts and external (Customer and supplier) bank accounts are stored separately