SQL 供應商銀行帳號資料

要看這個銀行帳號沒有被任何 supplier使用的話，可以用 Bank Account ID來查詢
iby.iby_pmt_instr_uses_all，看看有沒有資料存在

SELECT SUPP.VENDOR_NAME,
       supp.segment1 verndor_number,
       ssite.vendor_site_code,
       bank.bank_number,
       bank.bank_name,
       branch.branch_number,
       branch.bank_branch_name,
       acct.foreign_payment_use_flag,
       acct.bank_account_num,
       acct.bank_account_name,
       ssite.org_id,
   
       uses.last_update_date
       ,fu.user_name  created_by
  FROM iby.iby_ext_bank_accounts   acct,
       apps.iby_ext_bank_branches_v branch,
       apps.iby_ext_banks_v         bank,
       iby.iby_pmt_instr_uses_all  uses,
       iby.iby_external_payees_all payee,
       ap.ap_suppliers            supp,
       ap.ap_supplier_sites_all   ssite
       ,APPLSYS.fnd_user                fu
 WHERE acct.branch_id = branch.branch_party_id(+)
   AND acct.bank_id = bank.bank_party_id(+)
   AND acct.ext_bank_account_id = uses.instrument_id
   AND uses.instrument_type = 'BANKACCOUNT'
   AND uses.ext_pmt_party_id = payee.ext_payee_id
   AND payee.supplier_site_id = ssite.vendor_site_id
   AND ssite.vendor_id = supp.vendor_id
     AND uses.created_by = fu.user_id


或者


SELECT SUPP.VENDOR_NAME,
       supp.segment1 verndor_number,
       ssite.vendor_site_code,
       bank.bank_number,
       bank.bank_name,
       branch.branch_number,
       branch.bank_branch_name,
       acct.foreign_payment_use_flag,
       acct.bank_account_num,
       acct.bank_account_name,
       ssite.org_id,
 
       uses.last_update_date
       ,fu.user_name  created_by
  FROM iby.iby_ext_bank_accounts   acct,
       apps.iby_ext_bank_branches_v branch,
       apps.iby_ext_banks_v         bank,
       iby.iby_pmt_instr_uses_all  uses,
       iby.iby_external_payees_all payee,
       ap.ap_suppliers            supp,
       ap.ap_supplier_sites_all   ssite
       ,APPLSYS.fnd_user                fu
 WHERE acct.branch_id = branch.branch_party_id(+)
   AND acct.bank_id = bank.bank_party_id(+)
   AND acct.ext_bank_account_id = uses.instrument_id
   AND uses.instrument_type = 'BANKACCOUNT'
   AND uses.ext_pmt_party_id = payee.ext_payee_id
   AND payee.supplier_site_id = ssite.vendor_site_id(+)
   AND supp.party_id = payee.payee_party_id
   AND uses.created_by = fu.user_id