本帖最后由 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