oracle地点层,请教供应商地点层怎么和银行关联

本帖最后由 sigmond04 于 2011-11-14 09:08 编辑

如题，R12中如何关联供应商地点层和银行，最近在做报表的时候用到，网上找了下，有人发过sql，问题是查出来数据会有多条记录，请教下是否有人能提供正确的关联关系，谢谢！。

先把我找到的sql发出来吧  参考链接 http://www.erp100.com/forum.php?mod=viewthread&tid=96863

供应商地止所属银行

-------------------------------

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