Oracle FNDLOAD and Examples

Reference

Most of its contents were taken from 'Dibyajyoti Koch: A Blog on Oracle Application' website but were modified for my own personal use. Please visit https://imdjkoch.wordpress.com/tag/fndload for its original contents along with many other useful ones.

What is FNDLOAD?

• FNDLOAD is a developer tool provided by Oracle that migrates setup/configuration data from one instance to another
• This program is located in $FND_TOP/bin directory
• It is a human-readable file that is executed as a UNIX command line
• The program downloads a .ldt file from the Source instance. The program, then, uploads the .ldt file in the Target instance
  
  

What objects can be migrated by FNDLOAD?

1. Concurrent Programs
2. Request Groups
3. Request Sets
4. Responsibilities
5. Forms
6. Forms Personalizations
7. Key Flexfields (KFF)
8. Descriptive Flexfields (DFF)
9. Functions
10. Menus
11. Lookups
12. Value Sets
13. Profile Options
14. FND Users
15. Printer Styles

Examples

1. Concurrent Programs

• Download

FNDLOAD apps/$pswd 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct XX_CONC_PROG.ldt PROGRAM APPLICATION_SHORT_NAME="SQLAP" CONCURRENT_PROGRAM_NAME="XX_CONC_PROG_SHORT_NAME"

• Upload

FNDLOAD apps/$pswd 0 Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct XX_CONC_PROG.ldt

• Upload Partial

FNDLOAD apps/$pswd 0 Y UPLOAD_PARTIAL $FND_TOP/patch/115/import/afcpprog.lct XX_CONC_PROG.ldt PROGRAM CONCURRENT_PROGRAM_NAME="XX_CONC_PROG_SHORT_NAME" APPLICATION_SHORT_NAME="SQLAP"

2. Request Groups

• Download

FNDLOAD apps/$pswd 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct XX_CONC_PROG_REQ_GRP.ldt REQUEST_GROUP REQUEST_GROUP_NAME='Payables Admin Reports' APPLICATION_SHORT_NAME='SQLAP' REQUEST_GROUP_UNIT UNIT_APP='SQLAP' UNIT_TYPE='P' UNIT_NAME='XX_CONC_PROG_SHORT_NAME'

• Upload

FNDLOAD apps/$pswd 0 Y UPLOAD_PARTIAL $FND_TOP/patch/115/import/afcpreqg.lct XX_CONC_PROG_REQ_GRP.ldt REQUEST_GROUP REQUEST_GROUP_NAME='Payables Admin Reports' APPLICATION_SHORT_NAME='SQLAP'

3. Request Sets

• Download

FNDLOAD apps/$pswd

Query to find State and State Code in Oracle Applications

 Query to find State and State Code

SELECT g.geography_code    state_code,

       gi.identifier_value state_name

  FROM HZ_GEOGRAPHIES             g,

       (SELECT t.geography_id,

               t.identifier_value

          FROM HZ_GEOGRAPHY_IDENTIFIERS t

         WHERE t.identifier_subtype = 'STANDARD_NAME'

           AND t.application_id = 222

           AND t.language_code = 'US'

           AND t.geography_type = 'STATE'

           AND t.primary_flag = 'N'

           AND TRUNC (t.last_update_date) =

               (SELECT TRUNC (MAX (t2.last_update_date))

                  FROM HZ_GEOGRAPHY_IDENTIFIERS t2

                 WHERE t2.geography_id = t.geography_id)) gi

 WHERE g.geography_id = gi.geography_id

   AND g.country_code = 'US'

   AND g.application_id = 222

   AND g.geography_type = 'STATE'

 ORDER BY gi.identifier_value;

How to enable Help Diagnostics Examine Menu in Oracle Application

Reference:

Oracle Doc ID: 1300872.1
Help Diagnostics Examine is Missing from the Help Menu

If your Oracle Applications does not show the Help > Diagnostics > Examine menu in Oracle Applications (as pictured below), please contact your System Administrator or if you have 'System Administrator' responsibility, then follow the instructions below.

Navigation:

System Administrator > Profile > System

Profile 1: 

Query for Profile, Utilities:Diagnostics. Click Find.

Set user level profile to 'Yes'. Save.

Profile 2:

Query for Profile, Hide Diagnostics menu entry. Click Find.

Set user level profile to 'No'. Save.

Log out of the Applications.

Log back in and verify that Help > Diagnostics is now available.

Query to find Check Number Setups

SELECT pd.payment_document_name               "Payment Document Name",
       pd.first_available_document_num        "Check Number Begin",
       pd.last_available_document_number      "Check Number End",
       (pd.last_available_document_number -
        pd.first_available_document_num) + 1  "Num of Total Checks",
       pd.last_issued_document_number         "Last Issued Check Number",
       (pd.last_issued_document_number -
        pd.first_available_document_num) + 1  "Num Of Checks Used",
       (pd.last_available_document_number -
        pd.last_issued_document_number)       "Num Of Checks Remaining",
       pd.format_code                         "Format Code",
       ba.bank_account_name                   "Bank Account Name"
  FROM CE_PAYMENT_DOCUMENTS  pd,
       CE_BANK_ACCOUNTS      ba
 WHERE TRUNC (NVL (pd.inactive_date, SYSDATE)) >= TRUNC (SYSDATE)  -- active ones
   AND pd.internal_bank_account_id = ba.bank_account_id
 ORDER BY pd.payment_document_name;

Add System Admin responsibility to a user

BEGIN
   FND_USER_PKG.ADDRESP(
      USERNAME        =>  UPPER('AMOHSIN'),  -- username
      RESP_APP        =>  'SYSADMIN',
      RESP_KEY        =>  'SYSTEM_ADMINISTRATOR',
      SECURITY_GROUP  =>  'STANDARD',
      DESCRIPTION     =>  'DESCRIPTION',
      START_DATE      =>  SYSDATE,
      END_DATE        =>  NULL);

   COMMIT;

   DBMS_OUTPUT.PUT_LINE('Responsibility Added Successfully');

EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Responsibility is not added due to' ||
      SQLCODE || SUBSTR(SQLERRM, 1, 100));
      
      ROLLBACK;
END;