Oracle FNDLOAD Script Examples

Some of the examples in this FNDLOAD article include:-
FNDLOAD to transfer Request Groups
FNDLOAD for moving Concurrent Programs
FNDLOAD to download and upload Forms Personalizations ( or Personalisations depending on where you are located )

To FNDLOAD Web ADI, visit the link Web ADI FNDLOAD

Use FNDLOAD for transferring value set definitions.
-->Please note that when transferring Key Flex Fields and Descriptive flex fields the respective value sets against each segment will be extracted and loaded automatically.

Also, FNDLOAD can be used to migrate Key FlexFields, Descriptive Flexfields, Responsibilities and almost every other FND entity.

Please note that the text written down here could get wrapped in the browser.
Hence you may have to use \ to continue the single line command on Unix, in case you find the lines wrapping
In my case I am ensuring that $CLIENT_APPS_PWD has the apps password before running the scripts


------------------------------------------------------------------------------------------


##To FNDLOAD Request groups
FNDLOAD apps/$CLIENT_APPS_PWD O 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="XXGMS"
##Note that
##---------
## <> will be your Application Shortname where request group is registered
## XX_MY_REPORT_GROUP_NAME
Will be the name of your request group
## ##To upload this Request Group in other environment after having transferred the ldt file

FNDLOAD apps/$CLIENT_APPS_PWD O Y UPLOAD $FND_TOP/patch/115/import/afcpreqg.lct


------------------------------------------------------------------------------------------


##To FNDLOAD Concurrent Programs
FNDLOAD apps/$CLIENT_APPS_PWD O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct XX_CUSTOM_ORACLE_INTERFACE_PROG.ldt PROGRAM APPLICATION_SHORT_NAME="XXGMS" CONCURRENT_PROGRAM_NAME="XX_CUSTOM_ORACLE_INTERFACE_PROG"
##Note that
##---------
## XXGMS will be your custom GMS Application Shortname where concurrent program is registered
## XX_CUSTOM_ORACLE_INTERFACE_PROG
Will be the name of your request group
## XX_CUSTOM_ORACLE_INTERFACE_PROG.ldt is the file where concurrent program definition will be extracted
## ##To upload
FNDLOAD apps/$CLIENT_APPS_PWD O Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct XX_CUSTOM_ORACLE_INTERFACE_PROG.ldt


------------------------------------------------------------------------------------------


##To FNDLOAD Oracle Descriptive Flexfields
$FND_TOP/bin/FNDLOAD apps/$CLIENT_APPS_PWD 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'
##Note that
##---------
## PO is the Application Shortname against which descriptive flexfield against PO Headers is registered
## PO_REQUISITION_HEADERS
is the name of Descriptive Flexfield against PO Requisition Headers
## Use the SQL below to find the name of DFF, rather than logging into the screen (ooops via jinitiator)
########----->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
########----->/
## To upload into another environment
$FND_TOP/bin/FNDLOAD apps/$CLIENT_APPS_PWD 0 Y UPLOAD $FND_TOP/patch/115/import/afffload.lct XX_PO_REQ_HEADERS_DFF.ldt

## OK another example for DFF against FND_LOOKUPS
FNDLOAD apps/$CLIENT_APPS_PWD 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'
## OK another example for DFF against Project Accounting Expenditure Types
FNDLOAD apps/$CLIENT_APPS_PWD 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct XX_PA_EXPENDITURE_TYPES_DESC_FLEX_DFF.ldt DESC_FLEX APPLICATION_SHORT_NAME=PA DESCRIPTIVE_FLEXFIELD_NAME='PA_EXPENDITURE_TYPES_DESC_FLEX'



------------------------------------------------------------------------------------------


##To FNDLOAD Oracle Menus
$FND_TOP/bin/FNDLOAD apps/$CLIENT_APPS_PWD O Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct ICX_POR_SSP_HOME.ldt MENU MENU_NAME="ICX_POR_SSP_HOME"
##Note that
##---------
## Oracle Menus are not attached to applications. Hence no need to include application short name
## ICX_POR_SSP_HOME is the menu name. This can be validated via below SQL
## select user_menu_name from fnd_menus_vl where menu_name = 'ICX_POR_SSP_HOME' ;
## Also note that we do not pass in the User_menu_name in this example
## OK, now to upload this file
$FND_TOP/bin/FNDLOAD apps/$CLIENT_APPS_PWD O Y UPLOAD $FND_TOP/patch/115/import/afsload.lct ICX_POR_SSP_HOME.ldt


----------------------------------------------------------------------------------------------------------------------------

## Well, now for FND Messages to download a single message
FNDLOAD apps/$CLIENT_APPS_PWD 0 Y DOWNLOAD $FND_TOP/patch/115/import/afmdmsg.lct \
XX_ICX_POR_LIFECYCLE_PAY_TIP.ldt FND_NEW_MESSAGES APPLICATION_SHORT_NAME='ICX' MESSAGE_NAME=XX_ICX_POR_LIFECYCLE_PAY_TIP

## Or you may as well download all the messages within an application
FNDLOAD apps/$CLIENT_APPS_PWD 0 Y DOWNLOAD $FND_TOP/patch/115/import/afmdmsg.lct \
XX_ALL_GMS_MESSAGES_00.ldt FND_NEW_MESSAGES APPLICATION_SHORT_NAME='XXGMS'

## now to upload using FNDLOAD
FNDLOAD apps/$CLIENT_APPS_PWD 0 Y UPLOAD $FND_TOP/patch/115/import/afmdmsg.lct XX_ICX_POR_LIFECYCLE_PAY_TIP.ldt


----------------------------------------------------------------------------------------------------------------------------


## Now it's the turn of Lookup values. Again, its not a rocket science
FNDLOAD apps/$CLIENT_APPS_PWD 0 Y DOWNLOAD aflvmlu.lct XX_TRX_BATCH_STATUS.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME ='XXGMS' LOOKUP_TYPE="XX_TRX_BATCH_STATUS"
## Note that
## XX_TRX_BATCH_STATUS is the name of FND Lookup Type in this example
## This will download all the lookup codes within the defined lookup
## To upload
FNDLOAD apps/$CLIENT_APPS_PWD 0 Y UPLOAD aflvmlu.lct XX_TRX_BATCH_STATUS.ldt

----------------------------------------------------------------------------------------------------------------------------

## You can also move the User definitions from FND_USER FNDLOAD apps/$CLIENT_APPS_PWD 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct ./XX_FND_USER_PASSI.ldt FND_USER USER_NAME='ANILPASSI'
#Do not worry about your password being extracted, it will be encrypted as below in ldt file
#BEGIN FND_USER "ANILPASSI"
# OWNER = "PASSIA"
# LAST_UPDATE_DATE = "2005/10/19"
# ENCRYPTED_USER_PASSWORD = "ZGE45A8A9BE5CF4339596C625B99CAEDF136C34FEA244DC7A"
# SESSION_NUMBER = "0"To upload the FND_USER using FNDLOAD command use 
FNDLOAD apps/$CLIENT_APPS_PWD 0 Y UPLOAD $FND_TOP/patch/115/import/afscursp.lct ./XX_FND_USER_PASSI.ldt 
Notes for using FNDLOAD against FND_USER:-
1. After uploading using FNDLOAD, user will be promoted to change their password again during their next signon attempt.
2. All the responsibilities will be extracted by FNDLOAD alongwith User Definition in FND_USER 
3. In the Target Environment , make sure that you have done FNDLOAD for new responsibilities prior to running FNDLOAD on users. 
----------------------------------------------------------------------------------------------------------------------------


## Now lets have a look at the profile option using oracle's FNDLOAD
FNDLOAD apps/$CLIENT_APPS_PWD O Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct POR_ENABLE_REQ_HEADER_CUST.ldt PROFILE PROFILE_NAME="POR_ENABLE_REQ_HEADER_CUST" APPLICATION_SHORT_NAME="ICX"
## Note that
## POR_ENABLE_REQ_HEADER_CUST is the short name of profile option
## We aren't passing the user profile option name in this case. Validate using ...
########----->select application_id, PROFILE_OPTION_NAME || '==>' || profile_option_id || '==>' ||
########----->USER_PROFILE_OPTION_NAME
########----->from FND_PROFILE_OPTIONS_VL
########----->where PROFILE_OPTION_NAME like '%' || upper('&profile_option_name') || '%'
########----->order by PROFILE_OPTION_NAME
########----->/
## Now to upload
FNDLOAD apps/$CLIENT_APPS_PWD O Y UPLOAD $FND_TOP/patch/115/import/afscprof.lct POR_ENABLE_REQ_HEADER_CUST.ldt


----------------------------------------------------------------------------------------------------------------------------


## Now for the request sets that contain the stages and links for underlying concurrent programs
## For this you will be firstly required to download the request set definition.
## Next you will be required to download the Sets Linkage definition
## Well, lets be clear here, the above sequence is more important while uploading
FNDLOAD apps/$CLIENT_APPS_PWD 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct XX_GL_MY_INTERFACE_SET.ldt REQ_SET REQUEST_SET_NAME="FNDRSSUB4610101_Will_look_like_this"
FNDLOAD apps/$CLIENT_APPS_PWD 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct XX_GL_MY_INTERFACE_SET_LINK.ldt REQ_SET_LINKS REQUEST_SET_NAME="FNDRSSUB4610101_Will_look_like_this"
## Note that FNDRSSUB4610101 can be found by doing an examine on the
########----->select request_set_name from fnd_request_sets_vl
########----->where user_request_set_name = 'User visible name for the request set here'
## Now for uploading the request set, execute the below commands
FNDLOAD apps/$CLIENT_APPS_PWD 0 Y UPLOAD $FND_TOP/patch/115/import/afcprset.lct XX_GL_MY_INTERFACE_SET.ldt
FNDLOAD apps/$CLIENT_APPS_PWD 0 Y UPLOAD $FND_TOP/patch/115/import/afcprset.lct XX_GL_MY_INTERFACE_SET_LINK.ldt


----------------------------------------------------------------------------------------------------------------------------


## Now for the responsibility
FNDLOAD apps/$CLIENT_APPS_PWD O Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct XX_PERSON_RESPY.ldt FND_RESPONSIBILITY RESP_KEY="XX_PERSON_RESPY"
## note that XX_PERSON_RESPY is the responsibility key
## Now to upload
FNDLOAD apps/$CLIENT_APPS_PWD O Y UPLOAD $FND_TOP/patch/115/import/afscursp.lct XX_PERSON_RESPY.ldt


----------------------------------------------------------------------------------------------------------------------------
## OK, now for the forms personalizations
## For the forms personalizations, I have given three examples as below.
FNDLOAD apps/$CLIENT_APPS_PWD 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct XX_PERWSHRG.ldt FND_FORM_CUSTOM_RULES function_name="PERWSHRG-404"
FNDLOAD apps/$CLIENT_APPS_PWD 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct XX_HZ_ARXCUDCI_STD.ldt FND_FORM_CUSTOM_RULES function_name="HZ_ARXCUDCI_STD"
FNDLOAD apps/$CLIENT_APPS_PWD 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct XX_AP_APXVDMVD.ldt FND_FORM_CUSTOM_RULES function_name="AP_APXVDMVD"
## Note that the function name above is the function short name as seen in the Function Definition Screen
## Now to upload the forms personalizations that are defined against these forms functions....
FNDLOAD apps/$CLIENT_APPS_PWD 0 Y UPLOAD $FND_TOP/patch/115/import/affrmcus.lct XX_PERWSHRG.ldt
FNDLOAD apps/$CLIENT_APPS_PWD 0 Y UPLOAD $FND_TOP/patch/115/import/affrmcus.lct XX_HZ_ARXCUDCI_STD.ldt
FNDLOAD apps/$CLIENT_APPS_PWD 0 Y UPLOAD $FND_TOP/patch/115/import/affrmcus.lct XX_AP_APXVDMVD.ldt


----------------------------------------------------------------------------------------------------------------------------


Notes :
1. Give special attention when downloading Menus or Responsibilities.
In case your client has several developers modifying Responsibilities and Menus, then be ultra carefull. Not being carefull will mean that untested Forms and Functions will become available in your clients Production environment besides 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. Please amend the above examples as desired for applying appropriate filterations.

4. The list of examples by no mean cover all possible FNDLOAD entities.

5. FNDLOAD is very reliable and stable, if used properly. This happens to by one of my favourite Oracle utilities.

4. Last but not the least, please test your FNDLOAD properly, so as to ensure that you do not get any unexpected data. In past I have noticed undesired results when the Lookup gets modified manually directly on production, and then the FNDLOAD is run for similar changes. If possible, try to follow a good practice of modifying FNDLOADable data only by FNDLOAD on production environment.

5. As the name suggests, FNDLOAD is useful for FND Related objects. However in any implementation, you will be required to migrate the Setups in Financials and Oracle HRMS from one environment to another. For this you can use iSetup. "Oracle iSetup".
Some of the things that can be migrated using Oracle iSetup are
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.

Attachments API - FND_DOCUMENTS_PKG, FND_ATTACHED_DOCUMENTS_PKG

Working on attachments need some knowledge of how an attachment functions (Definitions, Entity, Document Category, Entities and Blocks, Primary Keys in attachments (If exists)) in Oracle Apps. To understand the below API and what it actually does its advisable to read the Attachments Chapter in Developers Guide.

Primary Key information that uniquely identifies the product (such as the product_ID).

Below API takes single file from a shared drive and inserts into fnd_lobs. Then it’s been attached to a particular Id (like order_id, invoice_id...) using the combination of pk1_value, entity name and category. In this example i have used PDF as attachment. Querying the table fnd_document_datatypes will give different attachment types.

This sample shows how an attachment API works. You can use it to customize/enhance to requirement.

DECLARE

l_rowid ROWID;

l_attached_document_id NUMBER;

l_document_id NUMBER;

l_media_id NUMBER;

l_category_id NUMBER;

l_pk1_value fnd_attached_documents.pk1_value%TYPE:=<Primary Key information that uniquely identifies the product (such as the product_ID)>;

l_description fnd_documents_tl.description%TYPE:='Test Attachment';

l_filename VARCHAR2(240) := '<File Name>';

l_seq_num NUMBER;

l_blob_data BLOB;

l_blob BLOB;

l_bfile BFILE;

l_byte NUMBER;

l_fnd_user_id NUMBER;

l_short_datatype_id NUMBER;

x_blob BLOB;

fils BFILE;

blob_length INTEGER;

l_entity_name VARCHAR2(100) := <entity_name>;

l_category_name VARCHAR2(100) := <category_name>;

BEGIN

fnd_global.apps_initialize (<userid>, <applid>,<appluserid>);

SELECT fnd_documents_s.NEXTVAL

INTO l_document_id

FROM DUAL;

SELECT fnd_attached_documents_s.NEXTVAL

INTO l_attached_document_id

FROM DUAL;

SELECT NVL (MAX (seq_num), 0) + 10

INTO l_seq_num

FROM fnd_attached_documents

WHERE pk1_value = l_pk1_value AND entity_name = l_entity_name;

-- Select User_id

SELECT user_id

INTO l_fnd_user_id

FROM apps.fnd_user

WHERE user_name = <user_name>;

-- Get Data type id for Short Text types of attachments

SELECT datatype_id

INTO l_short_datatype_id

FROM apps.fnd_document_datatypes

WHERE NAME = 'FILE';

-- Select Category id for Attachments

SELECT category_id

INTO l_category_id

FROM apps.fnd_document_categories_vl

WHERE USER_NAME = l_category_name;

-- Select nexvalues of document id, attached document id and

-- l_media_id

SELECT apps.fnd_documents_s.NEXTVAL,

apps.fnd_attached_documents_s.NEXTVAL

--apps.fnd_documents_long_text_s.NEXTVAL

INTO l_document_id,

l_attached_document_id

--l_media_id

FROM DUAL;

SELECT MAX (file_id) + 1

INTO l_media_id

FROM fnd_lobs;

fils := BFILENAME (<FLIE PATH>, l_filename);

-- Obtain the size of the blob file

DBMS_LOB.fileopen (fils, DBMS_LOB.file_readonly);

blob_length := DBMS_LOB.getlength (fils);

DBMS_LOB.fileclose (fils);

-- Insert a new record into the table containing the

-- filename you have specified and a LOB LOCATOR.

-- Return the LOB LOCATOR and assign it to x_blob.

INSERT INTO fnd_lobs

(file_id, file_name, file_content_type, upload_date,

expiration_date, program_name, program_tag, file_data,

LANGUAGE, oracle_charset, file_format

)

VALUES (l_media_id, l_filename, 'application/pdf',--'text/plain',--application/pdf

SYSDATE,

NULL, 'FNDATTCH', NULL, EMPTY_BLOB (), --l_blob_data,

'US', 'UTF8', 'binary'

)

RETURNING file_data

INTO x_blob;

-- Load the file into the database as a BLOB

DBMS_LOB.OPEN (fils, DBMS_LOB.lob_readonly);

DBMS_LOB.OPEN (x_blob, DBMS_LOB.lob_readwrite);

DBMS_LOB.loadfromfile (x_blob, fils, blob_length);

-- Close handles to blob and file

DBMS_LOB.CLOSE (x_blob);

DBMS_LOB.CLOSE (fils);

DBMS_OUTPUT.put_line ('FND_LOBS File Id Created is ' || l_media_id);

COMMIT;

-- This package allows user to share file across multiple orgs or restrict to single org

fnd_documents_pkg.insert_row

(x_rowid => l_rowid,

x_document_id => l_document_id,

x_creation_date => SYSDATE,

x_created_by => l_fnd_user_id,-- fnd_profile.value('USER_ID')

x_last_update_date => SYSDATE,

x_last_updated_by => l_fnd_user_id,-- fnd_profile.value('USER_ID')

x_last_update_login => fnd_profile.VALUE('LOGIN_ID'),

x_datatype_id => l_short_datatype_id, -- FILE

X_security_id => <security ID defined in your Attchments, Usaully SOB ID/ORG_ID..>,

x_publish_flag => 'N', --This flag allow the file to share across multiple organization

x_category_id => l_category_id,

x_security_type => 1,

x_usage_type => 'S',

x_language => 'US',

x_description => l_filename,--l_description,

x_file_name => l_filename,

x_media_id => l_media_id

);

commit;

fnd_documents_pkg.insert_tl_row

(x_document_id => l_document_id,

x_creation_date => SYSDATE,

x_created_by => l_fnd_user_id,--fnd_profile.VALUE('USER_ID'),

x_last_update_date => SYSDATE,

x_last_updated_by => l_fnd_user_id,--fnd_profile.VALUE('USER_ID'),

x_last_update_login => fnd_profile.VALUE('LOGIN_ID'),

x_language => 'US',

x_description => l_filename--l_description

);

COMMIT;

fnd_attached_documents_pkg.insert_row

(x_rowid => l_rowid,

x_attached_document_id => l_attached_document_id,

x_document_id => l_document_id,

x_creation_date => SYSDATE,

x_created_by => l_fnd_user_id,--fnd_profile.VALUE('USER_ID'),

x_last_update_date => SYSDATE,

x_last_updated_by => l_fnd_user_id,--fnd_profile.VALUE('USER_ID'),

x_last_update_login => fnd_profile.VALUE('LOGIN_ID'),

x_seq_num => l_seq_num,

x_entity_name => l_entity_name,

x_column1 => NULL,

x_pk1_value => l_pk1_value,

x_pk2_value => NULL,

x_pk3_value => NULL,

x_pk4_value => NULL,

x_pk5_value => NULL,

x_automatically_added_flag => 'N',

x_datatype_id => 6,

x_category_id => l_category_id,

x_security_type => 1,

X_security_id => <security ID defined in your Attchments, Usaully SOB ID/ORG_ID..>,

x_publish_flag => 'Y',

x_language => 'US',

x_description => l_filename,--l_description,

x_file_name => l_filename,

x_media_id => l_media_id

);

COMMIT;

DBMS_OUTPUT.put_line ('MEDIA ID CREATED IS ' || l_media_id);

END;

/