Supplier Bank Query in Fusion

supplier Bank query in Fusion

Hi friends, We are going to discuss about the supplier Bank query in Fusion. We will share the detail sql query which will help to extract the complete supplier bank account details in oracle fusion. This is the detail level sql query helps to extract the supplier banking information in oracle fusion. Supplier Bank informations do store in 'IBY' tables. All these supplier bank tables names starts with the IBY. This is one of the important sql query in terms of supplier details. If we do compare with Oracle Apps, then there is no change in the tables of supplier bank details. We will also share the important tables to store the Supplier bank details. Please find below the complete supplier Bank query in Fusion.

Supplier Bank Query in Fusion

6 Important Tables in supplier Bank query in Fusion

1.poz_supplier_sites_v
2.poz_suppliers_v
3.iby_ext_party_pmt_mthds
4.iby_external_payees_all
5.iby_pmt_instr_uses_all
6.iby_ext_bank_accounts_v

Detail Level of supplier Bank query in Fusion

Here below is the sql query helps to extract the supplier banking informations in Oracle Fusion

SELECT ps.vendor_site_id                  ,
 pv.VENDOR_NAME,
     pv.SEGMENT1,
     ixpmt.payment_method_code,
      (select INCOME_TAX_ID from  POZ_SUPPLIERS_PII WHERE VENDOR_ID=pv.vendor_id and rownum=1) TAX_PAYER_ID,
     pv.ORGANIZATION_TYPE_LOOKUP_CODE,
              pv.type_1099                            C_INCOME_TAX_TYPE,
      ps.vendor_site_code                    c_site_code,
       ps.address_line1||' '||ps.address_line2||' '||ps.address_line3||' '|| DECODE(ps.city,
              null, null, ps.city)||' '|| NVL(ps.state,ps.province)||' '||ps.zip||' '|| ps.country   C_address_concatenated,
      t.name                                 c_payment_terms,  --req--
       ps.pay_group_lookup_code               c_pay_grop_code, --req--
       ps.payment_priority                    c_payment_priority, --req--
       DECODE(ps.hold_unmatched_invoices_flag,
          'Y', 'Yes',
               'No')                     C_MATCHING_REQUIRED,  --REQ--
       DECODE(ps.hold_future_payments_flag,
          'Y', 'Yes',
             'No')                     C_HOLD_FUTURE_PAY,  --REQ--
       DECODE(ps.hold_all_payments_flag,
          'Y', 'Yes',
               'No')                     C_HOLD_ALL_PAY ,
               bankacct.BANK_NAME,
               bankacct.BANK_NUMBER,
               bankacct.BANK_BRANCH_NAME,
               bankacct.BRANCH_NUMBER,
               bankacct.BANK_ACCOUNT_NUMBER
FROM   poz_supplier_sites_v ps, 
       poz_suppliers_v pv,
       ap_terms t,
       iby_ext_party_pmt_mthds ixpmt,
       iby_external_payees_all ixpee,
       iby_pmt_instr_uses_all pmtins,
       iby_ext_bank_accounts_v bankacct
WHERE 1=1
  AND pv.vendor_id = ps.vendor_id
  AND ps.terms_id = t.term_id (+) 
  and ixpmt.primary_flag(+) = 'Y' /* B 9033691 start */
  and ixpee.EXT_PAYEE_ID = ixpmt.EXT_PMT_PARTY_ID(+)
   and ps.PARTY_SITE_ID = ixpee.PARTY_SITE_ID(+)
  and ps.VENDOR_SITE_ID   = ixpee.SUPPLIER_SITE_ID(+)
   AND bankacct.ext_bank_account_id = pmtins.instrument_id
   AND pmtins.ext_pmt_party_id = ixpee.ext_payee_id
  and ps.VENDOR_SITE_ID in (select VENDOR_SITE_ID from poz_site_assignments_all_m
WHERE BU_ID=:P_BU_ID)

Supplier Bank Query in Fusion