EBS 供应商银行相关SQL（银行失效，优先级）

界面

SELECT accts.ext_bank_account_id
      ,accts.bank_account_name
      ,accts.masked_iban                 AS iban
      ,accts.currency_code
      ,uses.order_of_preference
      ,uses.start_date
      ,uses.end_date
      ,fc.name                           AS currency_name
      ,bank.party_name                   AS bank_name
      ,accts.masked_bank_account_num     AS bank_account_number
      ,branch.bank_branch_name
      ,branch.branch_number
      ,bankprofile.bank_or_branch_number AS bank_number
      ,branch.eft_swift_code
      ,accts.bank_account_type
  FROM iby_pmt_instr_uses_all   uses
      ,iby_external_payees_all  payee
      ,iby_ext_bank_accounts    accts
      ,fnd_currencies_vl        fc
      ,hz_parties               bank
      ,hz_organization_profiles bankprofile
      ,ce_bank_branches_v       branch
 WHERE uses.instrument_type = 'BANKACCOUNT'
   AND payee.ext_payee_id = uses.ext_pmt_party_id
   AND payee.payee_party_id = :1
   AND payee.payment_function = 'PAYABLES_DISB'
   AND (payee.party_site_id = :2 OR
       (payee.party_site_id IS NULL AND :3 IS NULL))
   AND ((payee.org_id = :4 AND payee.org_id IS NOT NULL AND :5 IS NOT NULL) OR
       (payee.org_id IS NULL AND :6 IS NULL))
   AND (payee.supplier_site_id = :7 OR
       (payee.supplier_site_id IS NULL AND :8 IS NULL))
   AND uses.instrument_id = accts.ext_bank_account_id
   AND fc.currency_code(+) = accts.currency_code
   AND SYSDATE BETWEEN nvl(accts.start_date
                          ,SYSDATE) AND
       nvl(accts.end_date
          ,SYSDATE)
   AND accts.bank_id = bank.party_id(+)
   AND accts.bank_id = bankprofile.party_id(+)
   AND accts.branch_id = branch.branch_party_id(+)
   AND SYSDATE BETWEEN trunc(bankprofile.effective_start_date(+)) AND
       nvl(trunc(bankprofile.effective_end_date(+))
          ,SYSDATE + 1)