Wednesday 20 February 2013

Suppliers and their Bank Details in Oracle Apps R12

SELECT                          
             /*Supplier Information*/          
              aps.segment1                       oracle_supplier_number
             ,aps.vendor_id
             ,aps.vendor_name                    supplier_name
             ,aps.party_id                       supplier_party_id
             ,iepa.remit_advice_fax              remit_advice_fax
             ,iepa.remit_advice_email            remit_advice_email
             /* Supplier Site Information */
             ,assa.vendor_site_id
             ,assa.party_site_id                 supplier_party_site_id
             ,assa.vendor_site_code              vendor_site_code
             ,assa.pay_site_flag                 pay_site_flag
             ,assa.purchasing_site_flag          purchasing_site_flag
             ,assa.rfq_only_site_flag            rfq_only_site_flag
             /* Bank Information*/
             ,ieba.ext_bank_account_id
             ,hp.party_name                      Bank_party_name
             ,ieba.bank_account_num              bank_account_num
             ,ieba.bank_account_name             bank_account_name
             ,ieba.country_code                  bank_acct_country_code
             ,ieba.currency_code                 bank_acct_currency_code
             /* Bank Address */
             ,hp.address1                        bank_address_line1
             ,hp.address2                        bank_address_line2
             ,hp.address3                        bank_address_line3
             ,hp.city                            bank_address_city
             ,hp.state                           bank_address_state
             ,hp.postal_code                     bank_address_zip
             ,hp.country                         bank_address_country
             /* Bank Branch Address */
             ,hp1.address1                       branch_address_line1
             ,hp1.address2                       branch_address_line2
             ,hp1.address3                       branch_address_line3
             ,hp1.city                           branch_address_city
             ,hp1.state                          branch_address_state
             ,hp1.postal_code                    branch_address_zip
             ,hp1.country                        branch_address_country
FROM          ap_supplier_sites_all              assa
             ,hz_parties                         hp          
             ,iby_ext_bank_accounts              ieba
             ,iby_external_payees_all            iepa
             ,iby_pmt_instr_uses_all             ipiua           
             ,ap_suppliers                       aps
             ,hz_parties                         hp1          
WHERE        assa.vendor_site_id         =      iepa.supplier_site_id
AND          hp.party_id                 =      ieba.bank_id
AND          ipiua.instrument_id         =      ieba.ext_bank_account_id
AND          ipiua.ext_pmt_party_id      =      iepa.ext_payee_id
AND          assa.vendor_id              =      aps.vendor_id
AND          ieba.branch_id              =      hp1.party_id
AND          ipiua.instrument_type       =      'BANKACCOUNT'
AND          ipiua.payment_flow          =      'DISBURSEMENTS'
AND          ipiua.order_of_preference   =      1;

No comments:

Post a Comment