Query to find AP Pay Group

Below is the SQL query to find the Pay Groups in Oracle Accounts Payables (AP). 

Navigation:

Payables Manager -> Setup -> Lookups -> Purchasing -> Search for 'PAY GROUP' in Type field

SELECT lc.lookup_type              "Lookup Type",

       lc.lookup_code              "Lookup Code",

       lc.displayed_field          "Displayed Field",

       lc.description              "Description",

       TRUNC (lc.creation_date)    "Creation Date",

       usr_cr.user_name            "Created By",

       TRUNC (lc.last_update_date) "Last Update Date",

       usr_up.user_name            "Last Updated By"

  FROM PO_LOOKUP_CODES  lc,

       JPLFND_USER_V    usr_cr,

       JPLFND_USER_V    usr_up

 WHERE lc.created_by = usr_cr.user_id

   AND lc.last_updated_by = usr_up.user_id

   AND lc.lookup_type = 'PAY GROUP'

   AND lc.enabled_flag = 'Y'

   AND SYSDATE <= NVL (lc.inactive_date, SYSDATE)

 ORDER BY lc.creation_date;

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

4. Alert

• Download

FNDLOAD apps/$pswd 0 Y DOWNLOAD $ALR_TOP/patch/115/import/alr.lct XX_ALERT_ALR.ldt ALR_ALERTS APPLICATION_SHORT_NAME=SQLAP ALERT_NAME="XX_ALERT_ALR"

• Upload

FNDLOAD apps/$pswd 0 Y UPLOAD $ALR_TOP/patch/115/import/alr.lct XX_ALERT_ALR.ldt CUSTOM_MODE=FORCE

Query to find State and State Code in Oracle Applications

 Query to find State and State Code

SELECT hg.geography_code     state_code, 

       hgi.identifier_value  state_name

  FROM (SELECT gi.geography_id,

               gi.identifier_value,

               ROW_NUMBER() OVER (PARTITION BY gi.geography_id ORDER BY gi.last_update_date DESC) rn

          FROM hz_geography_identifiers gi

         WHERE gi.identifier_type = 'NAME'

           AND gi.identifier_subtype = 'STANDARD_NAME'

           AND gi.geography_type = 'STATE'

           AND gi.language_code = 'US'

           AND gi.primary_flag = 'N') hgi

  JOIN hz_geographies hg

    ON hg.geography_id = hgi.geography_id

 WHERE hg.geography_type = 'STATE'

   AND hgi.rn = 1

 ORDER BY hgi.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;