供应商，地址，业务实体，地点关联银行账户

供应商维护银行界面共有四个层级，依次为：供应商， 地址，地址-业务实体，地点.

四个层级分别可以关联银行账户，且结合发票工作台和付款工作台界面， 其在录入供应商、业务实体、地点之后，会自动带出对应的银行账户，且以此从四个层级中查找。
，有结合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