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,
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,
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 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