Oracle FNDLOAD and Examples

Reference:

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

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 of FNDLOAD

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"

Concurrent Programs > Upload

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

Concurrent Programs: 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'

Request Groups: 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

 

 

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

Query for Profile: Utilities:Diagnostics

Click Find.

Set user level profile to 'Yes'. Save.

Query for Profile: Hide Diagnostics menu entry

Click Find.

Set user level profile to 'Yes'. 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 APPS.CE_PAYMENT_DOCUMENTS  pd,
       APPS.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;