供应商相关的查询

–供应商查询：

SELECT ass.vendor_id                  vendor_id,ass.party_id                   party_id,ass.segment1                   vendor_code,ass.vendor_name                vendor_name,ass.vendor_name                vendor_short_name,ass.vendor_type_lookup_code    vendor_type,flv.meaning                    vendor_type_meaning,hp.tax_reference               tax_registered_name,ass.payment_method_lookup_code payment_method,att.name                       term_name,att.enabled_flag               enabled_flag,att.end_date_active            end_date_active,ass.creation_date              creation_date,ass.created_by                 created_by,ass.last_update_date           last_update_date,ass.last_updated_by            last_updated_by,ass.last_update_login          last_update_login
FROM   ap_suppliers      ass,fnd_lookup_values flv,hz_parties        hp,ap_terms_tl       att
WHERE  ass.vendor_type_lookup_code = flv.lookup_code(+)
AND    flv.lookup_type(+) = 'VENDOR TYPE'
AND    flv.language(+) = userenv('LANG')
AND    ass.party_id = hp.party_id
AND    att.language = userenv('LANG')
AND    ass.terms_id = att.term_id(+);

供应商银行信息：

SELECT ass.vendor_id         vendor_id,ass.party_id          party_id,bank.party_id         bank_id,bank.party_name       bank_name,branch.party_id       branch_id,branch.party_name     bank_branch_name,ieba.bank_account_num bank_account_num
FROM   ap_suppliers          ass,hz_parties            hp,iby_account_owners    iao,iby_ext_bank_accounts ieba,hz_parties            bank,hz_parties            branch
WHERE  ass.party_id = hp.party_id
AND    hp.party_id = iao.account_owner_party_id(+)
AND    iao.ext_bank_account_id = ieba.ext_bank_account_id(+)
AND    ieba.bank_id = bank.party_id(+)
AND    ieba.branch_id = branch.party_id(+)
ORDER  BY ieba.creation_date;

供应商开户行地址信息

SELECT hps.party_id      party_id,hps.party_site_id party_site_id,hl.location_id    location_id,hl.country        country,hl.province       province,hl.city           city,hl.address1       address1,hl.address2       address2,hl.address3       address3,hl.address4       address4
FROM   hz_party_sites hps,hz_locations   hl
WHERE  hps.location_id = hl.location_id
ORDER  BY hps.creation_date;

–供应商联系人信息

SELECT hr.subject_id subject_id,hr.object_id object_id,hr.party_id party_id,hp.person_last_name || ' ' || hp.person_middle_name || ' ' ||  hp.person_first_name contact_person,hcpp.phone_area_code phone_area_code,hcpp.phone_number phone_number,hcpp.phone_extension phone_extension,hcpf.phone_area_code fax_phone_area_code,hcpf.phone_number fax_phone_number,hcpe.email_address email_address
FROM   hz_relationships  hr,hz_contact_points hcpp,hz_contact_points hcpf,hz_contact_points hcpe,hz_parties        hp
WHERE  hr.object_id = hp.party_id
AND    hcpp.owner_table_id(+) = hr.party_id
AND    hcpf.owner_table_id(+) = hr.party_id
AND    hcpe.owner_table_id(+) = hr.party_id
AND    hr.object_type = 'PERSON'
AND    hr.relationship_code(+) = 'CONTACT'
AND    hcpp.owner_table_name(+) = 'HZ_PARTIES'
AND    hcpf.owner_table_name(+) = 'HZ_PARTIES'
AND    hcpe.owner_table_name(+) = 'HZ_PARTIES'
AND    hcpp.contact_point_type(+) = 'PHONE'
AND    hcpp.phone_line_type(+) = 'GEN'
AND    hcpf.contact_point_type(+) = 'PHONE'
AND    hcpf.phone_line_type(+) = 'FAX'
AND    hcpe.contact_point_type(+) = 'EMAIL'
AND    hcpe.phone_line_type IS NULL
ORDER  BY hr.creation_date;

供应商地址信息

SELECT assa.vendor_site_id       vendor_site_id,assa.vendor_id            vendor_id,assa.vendor_site_code     vendor_code,assa.vendor_site_code     address_short_name,assa.address_line1        address_line1,assa.address_line2        address_line2,assa.address_line3        address_line3,assa.address_line4        address_line4,assa.org_id               org_id,assa.country              country,assa.province             province,assa.city                 city,assa.county               county,assa.zip                  zip,assa.pay_site_flag        pay_site_flag,assa.purchasing_site_flag purchasing_site_flag,assa.inactive_date        inactive_date,assa.creation_date        creation_date,assa.created_by           created_by,assa.last_update_date     last_update_date,assa.last_updated_by      last_updated_by,assa.last_update_login    last_update_login
FROM   ap_suppliers          ass,ap_supplier_sites_all assa
WHERE  assa.vendor_id = ass.vendor_id;

供应商地址联系人信息： 电话phone、传真fax 和 邮件Email

SELECT hcpp.phone_area_code phone_area_code,hcpp.phone_number    phone_number,hcpp.phone_extension phone_extension,hcpf.phone_area_code fax_phone_area_code,hcpf.phone_number    fax_phone_number,hcpe.email_address   email_address
FROM   ap_supplier_sites_all assa,hz_contact_points     hcpp,hz_contact_points     hcpf,hz_contact_points     hcpe,hz_party_sites        hps
WHERE  assa.party_site_id = hps.party_site_id
AND    hcpp.owner_table_id(+) = assa.party_site_id
AND    hcpf.owner_table_id(+) = assa.party_site_id
AND    hcpe.owner_table_id(+) = assa.party_site_id
AND    hcpp.owner_table_name(+) = 'HZ_PARTY_SITES'
AND    hcpf.owner_table_name(+) = 'HZ_PARTY_SITES'
AND    hcpe.owner_table_name(+) = 'HZ_PARTY_SITES'
AND    hcpp.contact_point_type(+) = 'PHONE'
AND    hcpp.phone_line_type(+) = 'GEN'
AND    hcpf.contact_point_type(+) = 'PHONE'
AND    hcpf.phone_line_type(+) = 'FAX'
AND    hcpe.contact_point_type(+) = 'EMAIL'
AND    hcpe.phone_line_type IS NULL;

供应商地址收件人信息

SELECT assa.party_site_id FROM ap_supplier_sites_all assa;

根据 party_site_id 得到供应商地址的收件人名称

SELECT hps.addressee FROM hz_party_sites hps;

供应商银行帐户分配层次关系

SELECT * FROM iby_pmt_instr_uses_all;

供应商银行帐户分配层次关系明细 ( 不包括供应商层的分配信息 ):

SELECT * FROM iby_external_payees_all;

供应商信息

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