欢迎使用优快云-markdown编辑器

供应商银行信息查询SQL：

SELECT t3.meaning                 site_vendor_type,
             pay.ext_payee_id,
             vd.vendor_type_lookup_code vendor_type_code,
             t1.meaning                 vendor_type, --供应商类型
             vd.vendor_name, --供应商名称
             pb.c_ext_attr1             org_dept_code, --组织机构代码
             pb.c_ext_attr8             vendor_classify_code,
             --cux_vendor_print_pkg.get_flex_value_meaning('CUX_JD_GYS_FL' , pb.c_ext_attr8) vendor_classify, --供应商类别
             --ffv.flex_value_meaning vendor_classify,
             pb.c_ext_attr3 ledger_name, --法人代表
             pb.c_ext_attr4 ledger_card_num, --法人身份证号
             pb.c_ext_attr2 business_num, --营业执照号
             pb.d_ext_attr1 business_end_date, --营业执照有效期
             ft.territory_short_name site_country, --国家
             vs.org_id,
             ou.name ou_name, --JD核算机构
             vs.vendor_site_code, --地址名称
             vs.address_line2, --详细地址
             hpc.person_last_name, --姓名
             hcpp.phone_number, --电话号码
             hcpf.phone_number tax_number, --传真
             hcpe.email_address, --邮箱
             ft2.territory_short_name bank_country, --国家
             ieb.bank_name, --银行名称
             iebb.bank_branch_name, --分行名称
             iebb.branch_number, --银联号
             acc.bank_account_name, --开户名
             acc.bank_account_num, --账号
             t2.meaning registration_type, --默认申报类型
             vd.vat_registration_num, --税号
             hca.class_code, --税率
             p.payment_method_name, --付款方法
             decode(vs.hold_all_payments_flag, 'N', '否', 'Y', '是') hold_all_payments --付款冻结
    FROM ap_suppliers          vd,
             ap_supplier_sites_all vs,
             fnd_lookup_values_vl  t1,
             fnd_lookup_values_vl  t2,
             fnd_lookup_values_vl  t3,
             hr_operating_units    ou,
             fnd_territories_vl    ft,
             fnd_territories_vl    ft2,
             pos_supp_prof_ext_b   pb,
             --fnd_flex_values_vl      ffv,
             --fnd_flex_value_sets     ffs,
             iby_external_payees_all pay,
             iby_payment_methods_vl  p,
             iby_ext_bank_accounts   acc,
             iby_pmt_instr_uses_all  uses,
             iby_ext_bank_branches_v iebb,
             iby_ext_banks_v         ieb,
             zx_party_tax_profile    zpt,
             hz_code_assignments     hca,
             hz_parties              hpc,
             hz_contact_points       hcpp,
             hz_contact_points       hcpf,
             hz_contact_points       hcpe,
             hz_relationships        hr,
             hz_contact_points       hcppa,
             hz_contact_points       hcppw,
             ap_supplier_contacts    vc,
             zx_party_tax_profile    zpt2
 WHERE vd.vendor_id = vs.vendor_id(+)
     AND nvl(vs.inactive_date(+), SYSDATE) >= SYSDATE
     AND t1.lookup_type(+) = 'VENDOR TYPE'
     AND t1.enabled_flag(+) = 'Y'
     AND SYSDATE BETWEEN nvl(t1.start_date_active(+), trunc(SYSDATE)) AND
             nvl(t1.end_date_active(+), SYSDATE + 1)
     AND vd.vendor_type_lookup_code = t1.lookup_code(+)
     AND t3.lookup_type(+) = 'VENDOR TYPE'
     AND t3.enabled_flag(+) = 'Y'
     AND SYSDATE BETWEEN nvl(t3.start_date_active(+), trunc(SYSDATE)) AND
             nvl(t3.end_date_active(+), SYSDATE + 1)
     AND t3.lookup_code(+) = vs.attribute1
     AND ou.organization_id(+) = vs.org_id
     AND ft.territory_code(+) = vs.country
     AND pb.attr_group_id(+) = 231 --
     AND pb.pk1_value(+) = vs.party_site_id
     AND pb.pk2_value(+) = vs.vendor_site_id
            --AND ffv.flex_value_set_id = ffs.flex_value_set_id(+)
            --AND ffs.flex_value_set_name(+) = 'CUX_JD_GYS_FL'
            --AND ffv.ENABLED_FLAG(+) = 'Y'
            --AND SYSDATE BETWEEN nvl(ffv.start_date_active(+), SYSDATE) AND nvl(ffv.end_date_active(+), SYSDATE)
            --AND pb.c_ext_attr8 = ffv.flex_value(+)
     AND pay.supplier_site_id(+) = vs.vendor_site_id
     AND p.payment_method_code(+) = pay.default_payment_method_code
     AND nvl(p.inactive_date(+), SYSDATE) >= SYSDATE
     AND acc.ext_bank_account_id(+) = uses.instrument_id
     AND (uses.instrument_type(+) = 'BANKACCOUNT' OR
             uses.instrument_type(+) IS NULL)
     AND uses.ext_pmt_party_id(+) = pay.ext_payee_id
     AND SYSDATE BETWEEN nvl(uses.start_date(+), SYSDATE) AND
             nvl(uses.end_date(+), SYSDATE + 1)
     AND iebb.branch_party_id(+) = acc.branch_id
     AND ft2.territory_code(+) = acc.country_code
     AND ieb.bank_party_id(+) = acc.bank_id
     AND zpt.party_id(+) = vs.party_site_id
     AND zpt.party_type_code = 'THIRD_PARTY_SITE'
     AND zpt2.party_id = vd.party_id
     AND t2.lookup_code(+) = zpt2.registration_type_code
     AND t2.lookup_type(+) = 'ZX_REGISTRATIONS_TYPE'
     AND t2.enabled_flag(+) = 'Y'
     AND SYSDATE BETWEEN nvl(t2.start_date_active(+), trunc(SYSDATE)) AND
             nvl(t2.end_date_active(+), SYSDATE + 1)
     AND hca.owner_table_name(+) = 'ZX_PARTY_TAX_PROFILE'
     AND trunc(SYSDATE) >= nvl(trunc(hca.end_date_active(+)), trunc(SYSDATE))
     AND hca.owner_table_id(+) = zpt.party_tax_profile_id
     AND SYSDATE BETWEEN nvl(hca.start_date_active(+), SYSDATE) AND
             nvl(hca.end_date_active(+), SYSDATE + 1)
     AND hcpp.owner_table_name(+) = 'HZ_PARTIES'
     AND hcpp.owner_table_id(+) = hr.party_id
     AND hcpp.phone_line_type(+) = 'GEN'
     AND hcpp.contact_point_type(+) = 'PHONE'
     AND hcpf.owner_table_name(+) = 'HZ_PARTIES'
     AND hcpf.owner_table_id(+) = hr.party_id
     AND hcpf.phone_line_type(+) = 'FAX'
     AND hcpf.contact_point_type(+) = 'PHONE'
     AND hcpe.owner_table_name(+) = 'HZ_PARTIES'
     AND hcpe.owner_table_id(+) = hr.party_id
     AND hcpe.contact_point_type(+) = 'EMAIL'
     AND hcppw.owner_table_id(+) = hr.party_id
     AND hcppw.owner_table_name(+) = 'HZ_PARTIES'
     AND hcppw.status(+) = 'A'
     AND hcppw.contact_point_type(+) = 'WEB'
     AND hcppa.owner_table_id(+) = hr.party_id
     AND hcppa.owner_table_name(+) = 'HZ_PARTIES'
     AND hcppa.status(+) = 'A'
     AND hcppa.contact_point_type(+) = 'PHONE'
     AND hcppa.phone_line_type(+) = 'GEN'
     AND hcppa.primary_flag(+) = 'N'
     AND hr.object_id(+) = hpc.party_id
     AND hr.subject_type(+) = 'ORGANIZATION'
     AND hr.subject_table_name(+) = 'HZ_PARTIES'
     AND hr.object_table_name(+) = 'HZ_PARTIES'
     AND hr.object_type(+) = 'PERSON'
     AND hr.relationship_code(+) = 'CONTACT'
     AND hr.directional_flag(+) = 'B'
     AND hr.relationship_type(+) = 'CONTACT'
     AND SYSDATE BETWEEN nvl(hr.start_date(+), SYSDATE) AND
             nvl(hr.end_date(+), SYSDATE + 1)
     AND hcpe.status(+) = 'A'
     AND hcpe.primary_flag(+) = 'Y'
     AND hcpp.status(+) = 'A'
     AND hcpp.primary_flag(+) = 'Y'
     AND hcpf.status(+) = 'A'
     AND vc.org_party_site_id(+) = vs.party_site_id
     AND hpc.party_id(+) = vc.per_party_id;