R12中怎么找供应商地点对应的银行

zuibunan

在R12中找供应商层的银行，比较好找，但要找供应商地址下的银行，需要怎么找？

zuibunan

1.2011-03-11 供应商所属银行
---------------------------
SELECT hp1.party_name AS bank_name,
       hp2.party_name AS branch_name,
       ieba.bank_account_num,
       hp1.party_id AS bank_id,
       hp2.party_id AS branch_id,
       ieba.ext_bank_account_id
  FROM ap_suppliers          aps,
       iby_account_owners    iao,
       iby_ext_bank_accounts ieba,
       hz_parties            hp1,
       hz_parties            hp2
WHERE aps.party_id = iao.account_owner_party_id
   AND iao.ext_bank_account_id = ieba.ext_bank_account_id
   AND ieba.bank_id = hp1.party_id
   AND ieba.branch_id = hp2.party_id
   --AND aps.vendor_id = :p_vendor_id
---------------------------------------------------------------
2.2011-03-11 供应商地止所属银行
-------------------------------
SELECT accts.ext_bank_account_id,
       apss.vendor_site_code,
       payee.payee_party_id,--供应商层
       payee.party_site_id,--供应商地址层
       payee.org_id,--业务实体
       payee.supplier_site_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,
       ap.ap_supplier_sites_all apss
WHERE uses.instrument_type = 'BANKACCOUNT'
   AND payee.ext_payee_id = uses.ext_pmt_party_id
   AND payee.payment_function = 'PAYABLES_DISB'
   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)
   AND payee.party_site_id = apss.party_site_id
   --AND apss.vendor_site_id = :p_vendor_site_id

shitou2461

太好了，收录了额，谢谢，

lily_8384

谢谢分享,收藏了

笨蛋老公余

拿来使用了，谢谢分享

weist

谢谢了。无私啊。

wilson1966

R12 TEST OK,It's Good!