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;

Query to find Supplier, Site, Bank, Payment Method in Oracle AP R12

SELECT -----------------------------------------------------------------

       -- Supplier

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

       s.PARTY_ID,

       s.segment1                       supp_number,

       s.vendor_name                    supp_name,

       TRUNC (s.creation_date)          supp_creation_date,

       s_cr.user_name                   supp_created_by,

       TRUNC (s.last_update_date)       supp_update_date,

       s_upd.user_name                  supp_updated_by,

       (SELECT plc.displayed_field

          FROM PO_LOOKUP_CODES plc

         WHERE plc.lookup_type = 'VENDOR TYPE'

           AND plc.lookup_code = s.vendor_type_lookup_code)  supp_type,

       s.pay_group_lookup_code          supp_pay_group,

       s.organization_type_lookup_code  supp_org_type,

       s.standard_industry_class        supp_sic,

       s.tca_sync_num_1099              supp_tax_id,

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

       -- Business Classification

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

       (SELECT LISTAGG (lv.meaning, ', ') WITHIN GROUP (ORDER BY lv.meaning)

          FROM POS_BUS_CLASS_ATTR  bc,

               FND_LOOKUP_VALUES   lv

         WHERE bc.lookup_code = lv.lookup_code (+)

           AND bc.class_status = 'APPROVED'

           AND lv.lookup_type = 'POS_BUSINESS_CLASSIFICATIONS'

           AND lv.enabled_flag = 'Y'

           AND TRUNC (SYSDATE) BETWEEN TRUNC (lv.start_date_active) AND TRUNC (NVL (lv.end_date_active, SYSDATE+1))

           AND TRUNC (SYSDATE) BETWEEN TRUNC (bc.start_date_active) AND TRUNC (NVL (bc.end_date_active, SYSDATE+1))

           AND bc.party_id (+) = s.party_id

       )  bus_class,

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

       -- Supplier Site

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

       st.vendor_site_code              site_code,

       TRUNC (st.creation_date)         site_creation_date,

       st_cr.user_name                  site_created_by,

       TRUNC (st.last_update_date)      site_update_date,

       st_upd.user_name                 site_updated_by,

       st.pay_group_lookup_code         site_pay_group,

       st.pay_site_flag                 pay_site_flag,

       st.purchasing_site_flag          purchasing_site_flag,

       (SELECT territory_short_name

          FROM FND_TERRITORIES_VL v

         WHERE territory_code = st.country)  site_country,

       st.state                         site_state,

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

       -- Supplier Site Payment Method

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

       pm.payment_method_code           payment_method,

       pm.remit_advice_delivery_method  delivery_method,

       pm.remit_advice_email            remittance_email,

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

       -- Supplier Site Bank Account, Bank and Bank Branches

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

       ba.bank_account_num              bank_account_num,

       ba.start_date                    bank_account_start_date,

       ba.branch_name                   branch_name,

       ba.branch_number                 branch_number,

       ba.bank_name                     bank_name,

       ba.bank_number                   bank_number,

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

       -- Bank Account Owner Name

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

       (SELECT hp.party_name

          FROM HZ_PARTIES          hp,

               IBY_ACCOUNT_OWNERS  iao

         WHERE iao.account_owner_party_id = hp.party_id

           AND iao.ext_bank_account_id = ba.ext_bank_account_id

           AND iao.primary_flag = 'Y')  account_owner_name

  FROM AP_SUPPLIERS            s,

       AP_SUPPLIER_SITES_ALL   st,

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

       -- bank acocunt query

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

       (SELECT iep.supplier_site_id,

               ipi.start_date,

               ipi.end_date,

               iao.ext_bank_account_id,

               -- iep.ext_payee_id,

               ieb.bank_account_num,

               cbbv.bank_branch_name    branch_name,

               cbbv.branch_number,

               cbbv.bank_name,

               cbbv.bank_number

          FROM IBY_EXTERNAL_PAYEES_ALL  iep,

               IBY_PMT_INSTR_USES_ALL   ipi,

               IBY_ACCOUNT_OWNERS       iao,

               IBY_EXT_BANK_ACCOUNTS    ieb,

               CE_BANK_BRANCHES_V       cbbv

         WHERE iao.ext_bank_account_id = ieb.ext_bank_account_id(+)

           AND iao.ext_bank_account_id(+) = ipi.instrument_id

           AND iep.ext_payee_id = ipi.ext_pmt_party_id(+)

           AND iep.payee_party_id = iao.account_owner_party_id

           AND cbbv.branch_party_id(+) = ieb.branch_id

           AND ipi.instrument_type = 'BANKACCOUNT'

           AND TRUNC (SYSDATE) < TRUNC (NVL (ipi.end_date, SYSDATE + 1))

           AND iep.payment_function = 'PAYABLES_DISB')  ba,

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

       -- payment method, remittance email query

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

       (SELECT ppm.payment_method_code,

               iep.remit_advice_delivery_method,

               iep.remit_advice_email,

               iep.supplier_site_id,

               iep.inactive_Date,

               ppm.inactive_date inactive_date2,

               MAX (ppm.object_version_number)

          FROM IBY_EXTERNAL_PAYEES_ALL  iep,

               IBY_EXT_PARTY_PMT_MTHDS  ppm

         WHERE iep.ext_payee_id = ppm.ext_pmt_party_id

           AND ppm.primary_flag = 'Y'

           AND NVL (ppm.inactive_date, SYSDATE+1) >= SYSDATE

           AND NVL (iep.inactive_date, SYSDATE+1) >= SYSDATE

         GROUP BY ppm.payment_method_code,

               iep.remit_advice_delivery_method,

               iep.remit_advice_email,

               iep.supplier_site_id,

               iep.inactive_Date,

               ppm.inactive_date

       ) pm,

       --

       FND_USER  s_cr,   -- supplier create

       FND_USER  s_upd,  -- supplier update

       FND_USER  st_cr,  -- site create

       FND_USER  st_upd  -- site update

 WHERE s.enabled_flag = 'Y'

   AND UPPER (NVL (s.vendor_type_lookup_code, 'X')) NOT IN ('EMPLOYEE') -- No Employee supplier type

   --

   AND TRUNC (SYSDATE) < TRUNC (NVL (st.inactive_date, SYSDATE + 1))  -- Active Supplier Site

   AND TRUNC (SYSDATE) < TRUNC (NVL (s.end_date_active, SYSDATE + 1)) -- Active Supplier

   --

   AND st.last_updated_by = st_upd.user_id(+)

   AND st.created_by = st_cr.user_id(+)

   AND s.last_updated_by = s_upd.user_id

   AND s.created_by = s_cr.user_id

   --

   AND st.vendor_site_id = pm.supplier_site_id(+)

   AND st.vendor_site_id = ba.supplier_site_id(+)

   AND s.vendor_id = st.vendor_id(+)