Oracle EBS供应商信息查询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,

             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