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(+)

 

Oracle Applications Java Color Scheme

Have you ever experienced an accidental mistake when you thought you were testing something in Test instances (DEV, UAT, etc.), but in fact, it was in Production? Then, you gasped in fear, rushed to cancel the test you did in Production, and/or asked  the EBS technical/functional guys to come up with an urgent 'datafix' to reverse your mistake (test entry) in Production.

Well, personally I have not. But in case you ever did or feel that it may happen to you in future, there is an easy remedy to avoid this. Hence, one of the good reasons why Oracle Applications offer you different color schemes for different Oracle Application instances. Note that, this only applies to Oracle (Java) form.

I use the following color schemes to differentiate different instances from each other.

1. TEAL
• Use this color for Development type of instances
• Green color suggests that you are free to make any changes in this instance
2. RED
• Use this color for Test type of instances
• Red color suggests that you should proceed carefully in this instance
3. SWAN
• Defaulted color used primarily in Production
• Swan color suggests that your entries are permanent 
• This color theme profile typically gets copied over to other instances after the Clone

Oracle provides many other color options to choose from. Apply as you see fit or makes sense for your applications.

Blue Khaki Olive Purple Red Swan Teal Titanium

Navigation:

Any Responsibility > Edit > Preferences > Profiles

Press 'F11' in the Profile Name field. Type 'Java Color Scheme'. 

Press Ctrl+F11. From "User Value" drop down list, choose one of the values.

 

Blue

Khaki

Olive

Purple

Red

Swan

Teal

Titanium

REGEXP_SUBSTR: Create a list from a comma separated line

You can create a numbered list from a comma separated string line using REGEXP_SUBSTR function.

WITH t AS

   (SELECT 'PAYABLES, RECEIVABLES, GENERAL LEDGER, INVENTORY, PURCHASING'  str,

           '[^|,]+'  sep

      FROM dual)

 SELECT level, TRIM (REGEXP_SUBSTR (t.str, t.sep, 1, LEVEL)) parsed_str

   FROM t

CONNECT BY LEVEL <= REGEXP_COUNT (t.str, t.sep);

Query to find AP Supplier Remittance Email Addresses

This following query will display all the active vendors' email addresses by their associated sites. This will work only in Oracle R12.

SELECT

       -- s.vendor_id,

       -- st.vendor_site_id,

       -- s.party_id,

       -- st.party_site_id,

       s.vendor_name              "Vendor Name",

       s.segment1                 "Vendor Number",

       s.vendor_type_lookup_code  "Vendor Type",

       st.vendor_site_code        "Vendor Site Code",

       ou.name                    "Operating Unit",

       --

       iepa.remit_advice_delivery_method  "Remittance Delivery Method",

       iepa.remit_advice_email            "Remittance Advice Email"

  FROM

       ap_suppliers              s,

       ap_supplier_sites_all     st,

       hr_operating_units        ou,

       iby_external_payees_all   iepa

 WHERE

       1=1

   --

   -- AND s.vendor_type_lookup_code = 'EMPLOYEE'

   --

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

   AND s.enabled_flag = 'Y'

   --

   AND iepa.supplier_site_id = st.vendor_site_id

   AND iepa.payee_party_id = s.party_id

   --

   AND st.org_id = ou.organization_id

   AND st.vendor_id = s.vendor_id

   --

 ORDER BY s.vendor_name, st.vendor_site_code;