供应商，地址，业务实体，地点关联银行账户
供应商维护银行界面共有四个层级，依次为：供应商， 地址，地址-业务实体，地点.
四个层级分别可以关联银行账户，且结合发票工作台和付款工作台界面， 其在录入供应商、业务实体、地点之后，会自动带出对应的银行账户，且以此从四个层级中查找。
，有结合IBY_EXTERNAL_PAYEES_ALL 这个表，个人觉得这个表才是真正四个层级关联银行的 关键点，从这个表入手，找出供应商四个层级关联的所有银行信息
-- EMPLOYEE SELECT asp.vendor_id AS 供应商id, asp.vendor_name AS 供应商名称, asp.segment1 AS 供应商编号, asp.vendor_type_lookup_code AS 供应商类型, asp.start_date_active AS 供应商起始日期, asp.enabled_flag AS 供应商启用标识, asp.end_date_active AS 供应商终止日期, asp.party_id, ieb.ext_bank_account_id, ieb.bank_party_id, ieb.bank_name AS 银行, ieb.bank_branch_name AS 分行, ieb.branch_party_id AS 分行id, ieb.bank_account_id, ieb.bank_account_number AS 银行账户, ieb.primary_acct_owner_party_id AS 账户主要责任人id, ieb.primary_acct_owner_name AS 账户主要责任人, iao.end_date AS 账户责任人终止日期, iao.primary_flag AS 账户主要责任人标识, ieb.start_date AS 银行起始日期, ieb.end_date AS 银行终止日期, (SELECT t.start_date FROM iby_ext_bank_branches_v t WHERE t.branch_party_id = ieb.branch_party_id) AS 分行起始日期, (SELECT t.end_date FROM iby_ext_bank_branches_v t WHERE t.branch_party_id = ieb.branch_party_id) AS 分行终止日期, iep.payee_party_id, iep.payment_function, iep.party_site_id, NULL AS 地址名称, NULL AS 地址是否有效, ass.org_id AS 业务实体id, (SELECT t.description FROM fnd_flex_values_vl t, fnd_flex_value_sets s WHERE 1 = 1 AND t.flex_value_set_id = s.flex_value_set_id AND s.flex_value_set_name = 'XXX-COMPANY' AND t.flex_value = (SELECT substr(hou.short_code, 4) FROM hr_operating_units hou WHERE hou.organization_id = ass.org_id) AND t.enabled_flag = 'Y' AND SYSDATE < nvl(t.end_date_active, SYSDATE + 1)) AS 业务实体名称, (SELECT hou.date_from FROM hr_organization_units hou WHERE hou.organization_id = ass.org_id) AS 业务实体生效日期, (SELECT hou.date_to FROM hr_organization_units hou WHERE hou.organization_id = ass.org_id) AS 业务实体失效日期, ass.vendor_site_id, ass.vendor_site_code AS 地点名称, ass.inactive_date AS 地点失效日期, uses.instrument_payment_use_id, uses.ext_pmt_party_id, uses.instrument_id, uses.payment_function, uses.start_date AS 银行账户起始日期, uses.end_date AS 银行账户终止日期 FROM ap_suppliers asp, iby_ext_bank_accounts_v ieb, iby_external_payees_all iep, iby_pmt_instr_uses_all uses, iby_account_owners iao, ap_supplier_sites_all ass WHERE 1 = 1 AND iep.ext_payee_id = uses.ext_pmt_party_id AND iep.payment_function = 'PAYABLES_DISB' AND uses.instrument_id = ieb.ext_bank_account_id AND iep.payee_party_id = asp.party_id AND iep.party_site_id IS NULL AND iep.supplier_site_id IS NULL AND iep.org_id IS NULL AND asp.vendor_type_lookup_code = 'EMPLOYEE' AND ass.vendor_id = asp.vendor_id AND uses.instrument_type = 'BANKACCOUNT' AND iao.account_owner_party_id = asp.party_id AND iao.ext_bank_account_id(+) = ieb.ext_bank_account_id AND asp.vendor_name = '&VENDOR_NAME' UNION ALL --VENDOR --第一层（供应商关联银行） SELECT asp.vendor_id AS 供应商id, asp.vendor_name AS 供应商名称, asp.segment1 AS 供应商编号, asp.vendor_type_lookup_code AS 供应商类型, asp.start_date_active AS 供应商起始日期, asp.enabled_flag AS 供应商启用标识, asp.end_date_active AS 供应商终止日期, asp.party_id, ieb.ext_bank_account_id, ieb.bank_party_id, ieb.bank_name AS 银行, ieb.bank_branch_name AS 分行, ieb.branch_party_id AS 分行id, ieb.bank_account_id, ieb.bank_account_number AS 银行账户, ieb.primary_acct_owner_party_id AS 账户主要责任人id, ieb.primary_acct_owner_name AS 账户主要责任人, iao.end_date AS 账户责任人终止日期, iao.primary_flag AS 账户主要责任人标识, ieb.start_date AS 银行起始日期, ieb.end_date AS 银行终止日期, (SELECT t.start_date FROM iby_ext_bank_branches_v t WHERE t.branch_party_id = ieb.branch_party_id) AS 分行起始日期, (SELECT t.end_date FROM iby_ext_bank_branches_v t WHERE t.branch_party_id = ieb.branch_party_id) AS 分行终止日期, iep.payee_party_id, iep.payment_function, iep.party_site_id, NULL AS 地址名称, NULL AS 地址是否有效, iep.org_id AS 业务实体id, NULL AS 业务实体名称, NULL AS 业务实体生效日期, NULL AS 业务实体失效日期, iep.supplier_site_id, NULL, NULL, uses.instrument_payment_use_id, uses.ext_pmt_party_id, uses.instrument_id, uses.payment_function, uses.start_date AS 银行账户起始日期, uses.end_date AS 银行账户终止日期 FROM ap_suppliers asp, iby_ext_bank_accounts_v ieb, iby_external_payees_all iep, iby_account_owners iao, iby_pmt_instr_uses_all uses WHERE 1 = 1 AND iep.ext_payee_id = uses.ext_pmt_party_id AND iep.payment_function = 'PAYABLES_DISB' AND uses.instrument_id = ieb.ext_bank_account_id AND iep.payee_party_id = asp.party_id AND iep.party_site_id IS NULL AND iep.supplier_site_id IS NULL AND iep.org_id IS NULL AND asp.vendor_type_lookup_code = 'VENDOR' AND uses.instrument_type = 'BANKACCOUNT' AND iao.account_owner_party_id = asp.party_id AND iao.ext_bank_account_id(+) = ieb.ext_bank_account_id AND asp.vendor_name = '&VENDOR_NAME' UNION ALL --第二层（地址关联银行） SELECT asp.vendor_id AS 供应商id, asp.vendor_name AS 供应商名称, asp.segment1 AS 供应商编号, asp.vendor_type_lookup_code AS 供应商类型, asp.start_date_active AS 供应商起始日期, asp.enabled_flag AS 供应商启用标识, asp.end_date_active AS 供应商终止日期, asp.party_id, ieb.ext_bank_account_id, ieb.bank_party_id, ieb.bank_name AS 银行, ieb.bank_branch_name AS 分行, ieb.branch_party_id AS 分行id, ieb.bank_account_id, ieb.bank_account_number AS 银行账户, ieb.primary_acct_owner_party_id AS 账户主要责任人id, ieb.primary_acct_owner_name AS 账户主要责任人, iao.end_date AS 账户责任人终止日期, iao.primary_flag AS 账户主要责任人标识, ieb.start_date AS 银行起始日期, ieb.end_date AS 银行终止日期, (SELECT t.start_date FROM iby_ext_bank_branches_v t WHERE t.branch_party_id = ieb.branch_party_id) AS 分行起始日期, (SELECT t.end_date FROM iby_ext_bank_branches_v t WHERE t.branch_party_id = ieb.branch_party_id) AS 分行终止日期, iep.payee_party_id, iep.payment_function, iep.party_site_id, (SELECT hps.party_site_name FROM hz_party_sites hps WHERE iep.party_site_id = hps.party_site_id --AND HPS.STATUS='A' AND EXISTS (SELECT 1 FROM hz_party_sites hps, hz_party_site_uses purchase, hz_party_site_uses pay WHERE hps.party_site_id = purchase.party_site_id AND hps.party_site_id = pay.party_site_id AND purchase.site_use_type = 'PURCHASING' AND pay.site_use_type = 'PAY')) AS 地址名称, decode((SELECT hps.status FROM hz_party_sites hps WHERE iep.party_site_id = hps.party_site_id --AND HPS.STATUS='A' AND EXISTS (SELECT 1 FROM hz_party_sites hps, hz_party_site_uses purchase, hz_party_site_uses pay WHERE hps.party_site_id = purchase.party_site_id AND hps.party_site_id = pay.party_site_id AND purchase.site_use_type = 'PURCHASING' AND pay.site_use_type = 'PAY')), 'A', '有效', '无效') AS 地址是否有效, iep.org_id, NULL AS 业务实体, NULL AS 业务实体生效日期, NULL AS 业务实体失效日期, iep.supplier_site_id, NULL, NULL, uses.instrument_payment_use_id, uses.ext_pmt_party_id, uses.instrument_id, uses.payment_function, uses.start_date AS 银行账户起始日期, uses.end_date AS 银行账户终止日期 FROM ap_suppliers asp, iby_ext_bank_accounts_v ieb, iby_external_payees_all iep, iby_account_owners iao, iby_pmt_instr_uses_all uses WHERE 1 = 1 AND iep.ext_payee_id = uses.ext_pmt_party_id AND iep.payment_function = 'PAYABLES_DISB' AND uses.instrument_id = ieb.ext_bank_account_id AND iep.payee_party_id = asp.party_id AND iep.party_site_id IS NOT NULL AND iep.supplier_site_id IS NULL AND iep.org_id IS NULL AND asp.vendor_type_lookup_code = 'VENDOR' AND uses.instrument_type = 'BANKACCOUNT' AND iao.account_owner_party_id = asp.party_id AND iao.ext_bank_account_id(+) = ieb.ext_bank_account_id AND asp.vendor_name = '&VENDOR_NAME' UNION ALL --第三层（地址-业务实体关联银行） SELECT asp.vendor_id AS 供应商id, asp.vendor_name AS 供应商名称, asp.segment1 AS 供应商编号, asp.vendor_type_lookup_code AS 供应商类型, asp.start_date_active AS 供应商起始日期, asp.enabled_flag AS 供应商启用标识, asp.end_date_active AS 供应商终止日期, asp.party_id, ieb.ext_bank_account_id, ieb.bank_party_id, ieb.bank_name AS 银行, ieb.bank_branch_name AS 分行, ieb.branch_party_id AS 分行id, ieb.bank_account_id, ieb.bank_account_number AS 银行账户, ieb.primary_acct_owner_party_id AS 账户主要责任人id, ieb.primary_acct_owner_name AS 账户主要责任人, iao.end_date AS 账户责任人终止日期, iao.primary_flag AS 账户主要责任人标识, ieb.start_date AS 银行起始日期, ieb.end_date AS 银行终止日期, (SELECT t.start_date FROM iby_ext_bank_branches_v t WHERE t.branch_party_id = ieb.branch_party_id) AS 分行起始日期, (SELECT t.end_date FROM iby_ext_bank_branches_v t WHERE t.branch_party_id = ieb.branch_party_id) AS 分行终止日期, iep.payee_party_id, iep.payment_function, iep.party_site_id, (SELECT hps.party_site_name FROM hz_party_sites hps WHERE iep.party_site_id = hps.party_site_id --AND HPS.STATUS='A' AND EXISTS (SELECT 1 FROM hz_party_sites hps, hz_party_site_uses purchase, hz_party_site_uses pay WHERE hps.party_site_id = purchase.party_site_id AND hps.party_site_id = pay.party_site_id AND purchase.site_use_type = 'PURCHASING' AND pay.site_use_type = 'PAY')) AS 地址名称, decode((SELECT hps.status FROM hz_party_sites hps WHERE iep.party_site_id = hps.party_site_id --AND HPS.STATUS='A' AND EXISTS (SELECT 1 FROM hz_party_sites hps, hz_party_site_uses purchase, hz_party_site_uses pay WHERE hps.party_site_id = purchase.party_site_id AND hps.party_site_id = pay.party_site_id AND purchase.site_use_type = 'PURCHASING' AND pay.site_use_type = 'PAY')), 'A', '有效', '无效') AS 地址是否有效, iep.org_id, (SELECT TRIM(substr(hou.name, 4)) FROM hr_organization_units hou WHERE hou.organization_id = iep.org_id) --AND SYSDATE BETWEEN NVL(HOU.DATE_FROM,SYSDATE-1) AND NVL(HOU.DATE_TO,SYSDATE+1) AS 业务实体, (SELECT hou.date_from FROM hr_organization_units hou WHERE hou.organization_id = iep.org_id) AS 业务实体生效日期, (SELECT hou.date_to FROM hr_organization_units hou WHERE hou.organization_id = iep.org_id) AS 业务实体失效日期, iep.supplier_site_id, NULL AS 地点, NULL AS 地点失效日期, uses.instrument_payment_use_id, uses.ext_pmt_party_id, uses.instrument_id, uses.payment_function, uses.start_date AS 银行账户起始日期, uses.end_date AS 银行账户终止日期 FROM ap_suppliers asp, iby_ext_bank_accounts_v ieb, iby_external_payees_all iep, iby_account_owners iao, iby_pmt_instr_uses_all uses WHERE 1 = 1 AND iep.ext_payee_id = uses.ext_pmt_party_id AND iep.payment_function = 'PAYABLES_DISB' AND uses.instrument_id = ieb.ext_bank_account_id AND iep.payee_party_id = asp.party_id AND iep.party_site_id IS NOT NULL AND iep.supplier_site_id IS NULL AND iep.org_id IS NOT NULL AND uses.instrument_type = 'BANKACCOUNT' AND asp.vendor_type_lookup_code = 'VENDOR' AND iao.account_owner_party_id = asp.party_id AND iao.ext_bank_account_id(+) = ieb.ext_bank_account_id AND asp.vendor_name = '&VENDOR_NAME' UNION ALL --第四层（地点关联银行） SELECT asp.vendor_id AS 供应商id, asp.vendor_name AS 供应商名称, asp.segment1 AS 供应商编号, asp.vendor_type_lookup_code AS 供应商类型, asp.start_date_active AS 供应商起始日期, asp.enabled_flag AS 供应商启用标识, asp.end_date_active AS 供应商终止日期, asp.party_id, ieb.ext_bank_account_id, ieb.bank_party_id, ieb.bank_name AS 银行, ieb.bank_branch_name AS 分行, ieb.branch_party_id AS 分行id, ieb.bank_account_id, ieb.bank_account_number AS 银行账户, ieb.primary_acct_owner_party_id AS 账户主要责任人id, ieb.primary_acct_owner_name AS 账户主要责任人, iao.end_date AS 账户责任人终止日期, iao.primary_flag AS 账户主要责任人标识, ieb.start_date AS 银行起始日期, ieb.end_date AS 银行终止日期, (SELECT t.start_date FROM iby_ext_bank_branches_v t WHERE t.branch_party_id = ieb.branch_party_id) AS 分行起始日期, (SELECT t.end_date FROM iby_ext_bank_branches_v t WHERE t.branch_party_id = ieb.branch_party_id) AS 分行终止日期, iep.payee_party_id, iep.payment_function, iep.party_site_id, (SELECT hps.party_site_name FROM hz_party_sites hps WHERE iep.party_site_id = hps.party_site_id --AND HPS.STATUS='A' AND EXISTS (SELECT 1 FROM hz_party_sites hps, hz_party_site_uses purchase, hz_party_site_uses pay WHERE hps.party_site_id = purchase.party_site_id AND hps.party_site_id = pay.party_site_id AND purchase.site_use_type = 'PURCHASING' AND pay.site_use_type = 'PAY')) AS 地址名称, decode((SELECT hps.status FROM hz_party_sites hps WHERE iep.party_site_id = hps.party_site_id --AND HPS.STATUS='A' AND EXISTS (SELECT 1 FROM hz_party_sites hps, hz_party_site_uses purchase, hz_party_site_uses pay WHERE hps.party_site_id = purchase.party_site_id AND hps.party_site_id = pay.party_site_id AND purchase.site_use_type = 'PURCHASING' AND pay.site_use_type = 'PAY')), 'A', '有效', '无效') AS 地址是否有效, iep.org_id, (SELECT TRIM(substr(hou.name, 4)) FROM hr_organization_units hou WHERE hou.organization_id = iep.org_id) --AND SYSDATE BETWEEN NVL(HOU.DATE_FROM,SYSDATE-1) AND NVL(HOU.DATE_TO,SYSDATE+1) AS 业务实体, (SELECT hou.date_from FROM hr_organization_units hou WHERE hou.organization_id = iep.org_id) AS 业务实体生效日期, (SELECT hou.date_to FROM hr_organization_units hou WHERE hou.organization_id = iep.org_id) AS 业务实体失效日期, iep.supplier_site_id, (SELECT ass.vendor_site_code FROM ap_supplier_sites_all ass WHERE ass.vendor_site_id = iep.supplier_site_id AND ass.purchasing_site_flag = 'Y' AND ass.pay_site_flag = 'Y') AS 地点, (SELECT ass.inactive_date FROM ap_supplier_sites_all ass WHERE ass.vendor_site_id = iep.supplier_site_id AND ass.purchasing_site_flag = 'Y' AND ass.pay_site_flag = 'Y') AS 地点失效日期, uses.instrument_payment_use_id, uses.ext_pmt_party_id, uses.instrument_id, uses.payment_function, uses.start_date AS 银行账户起始日期, uses.end_date AS 银行账户终止日期 FROM ap_suppliers asp, iby_ext_bank_accounts_v ieb, iby_external_payees_all iep, iby_account_owners iao, iby_pmt_instr_uses_all uses WHERE 1 = 1 AND asp.vendor_type_lookup_code = 'VENDOR' AND iep.ext_payee_id = uses.ext_pmt_party_id AND iep.payment_function = 'PAYABLES_DISB' AND uses.instrument_id = ieb.ext_bank_account_id AND iep.payee_party_id = asp.party_id AND iep.party_site_id IS NOT NULL AND iep.supplier_site_id IS NOT NULL AND iep.org_id IS NOT NULL AND uses.instrument_type = 'BANKACCOUNT' AND iao.account_owner_party_id = asp.party_id AND iao.ext_bank_account_id(+) = ieb.ext_bank_account_id AND asp.vendor_name = '&VENDOR_NAME';
原文链接: http://www.itpub.net/thread-1603741-1-1.html
