SELECT hou.name, pv.vendor_name 供应商, pv.party_id, pvs.vendor_site_id, pvs.terms_id, pv.vendor_name_alt 供应商别名, hp.known_as 别名, cux_rebate_pub.get_lookup_meaning(p_lookup_type => 'VENDOR TYPE', p_lookup_code => pv.vendor_type_lookup_code) 供应商类型, decode(upper(pv.vendor_type_lookup_code), 'EMPLOYEE', papf.national_identifier, decode(pv.organization_type_lookup_code, 'INDIVIDUAL', pv.individual_1099, 'FOREIGN INDIVIDUAL', pv.individual_1099, hp.jgzz_fiscal_code)) 纳税人标识, pv.attribute1 是否内部供应商, pv.attribute2 快递收件人电话, pv.attribute3 快递收件人地址, bank.party_name 银行名称, branch.party_name 银行分行名称, ieba.bank_account_num 银行账号, hop.principal_name 法人代表, vendor_site.party_site_name 供应商地点, vendor_site.vendor_address 地址, vendor_site.city 城市, vendor_site.postal_code 邮编, hpc.person_last_name 联系人姓名, /*hcpp.phone_area_code ||'-'||*/ hcpp.phone_number 电话号码, /*hcpf.phone_area_code ||'-'||*/ cux_inv_item_category_pkg.get_gl_concat_segments(p_ccid => pvs.accts_pay_code_combination_id) 应付票据, cux_inv_item_category_pkg.get_gl_concat_segments(p_ccid => pvs.prepay_code_combination_id) 预付款, hcpf.phone_number 传真号码, zft.classification_type_code 会计分类类型代码, hca.class_code 会计分类代码, hca.start_date_active 有效日期自, ipm.payment_method_name 付款方法, att.name 付款条件, gcc1.concatenated_segments 负债账户, gcc2.concatenated_segments 预付款账户 FROM po_vendors pv, hz_parties hp, per_all_people_f papf, hz_organization_profiles hop, (SELECT hps.party_id, hps.party_site_id, hps.party_site_name, hl.city, hl.postal_code, hl.address1 || ' ' || hl.address2 || ' ' || hl.address3 || ' ' || hl.address4 vendor_address FROM hz_party_sites hps, hz_locations hl WHERE hps.status = 'A' AND hl.location_id = hps.location_id AND NOT EXISTS (SELECT 1 FROM pos_address_requests par, pos_supplier_mappings psm WHERE psm.party_id = hps.party_id AND psm.mapping_id = par.mapping_id AND party_site_id = hps.party_site_id AND request_status = 'PENDING' AND request_type IN ('UPDATE', 'DELETE')) UNION ALL SELECT psm.party_id, par.party_site_id, par.party_site_name, par.city, par.postal_code, par.address_line1 || ' ' || par.address_line2 || ' ' || par.address_line3 || ' ' || par.address_line4 vendor_address FROM pos_address_requests par, pos_supplier_mappings psm WHERE par.mapping_id = psm.mapping_id AND par.request_status = 'PENDING' AND par.request_type = 'ADD') vendor_site, hz_relationships hr, hz_parties hpc, hz_contact_points hcpp, hz_contact_points hcpf, hz_party_sites hps, zx_party_tax_profile ptp, hz_code_assignments hca, zx_fc_types_b zft, po_vendor_sites_all pvs, hr_operating_units hou, iby_external_payees_all payee, iby_ext_party_pmt_mthds ppm, iby_payment_methods_vl ipm, ap_terms_tl att, gl_code_combinations_kfv gcc1, gl_code_combinations_kfv gcc2, iby_account_owners iao, --账号与供应商关系表 iby_ext_bank_accounts ieba, --账号账号表 hz_parties bank, --银行表 hz_parties branch --银行分行表 WHERE 1 = 1 AND pv.party_id = hp.party_id AND pv.employee_id = papf.person_id(+) AND hop.party_id = hp.party_id AND hop.effective_end_date IS NULL AND vendor_site.party_id(+) = hp.party_id AND hp.party_id = hr.subject_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 hr.object_id = hpc.party_id(+) AND hcpp.owner_table_id(+) = hr.party_id AND hcpp.owner_table_name(+) = 'HZ_PARTIES' AND hcpp.phone_line_type(+) = 'GEN' AND hcpp.contact_point_type(+) = 'PHONE' AND hcpf.owner_table_id(+) = hr.party_id AND hcpf.owner_table_name(+) = 'HZ_PARTIES' AND hcpf.phone_line_type(+) = 'FAX' AND hcpf.contact_point_type(+) = 'PHONE' AND hp.party_id = hps.party_id(+) AND ptp.party_id(+) = hps.party_site_id AND ptp.party_type_code(+) = 'THIRD_PARTY_SITE' AND hca.owner_table_name(+) = 'ZX_PARTY_TAX_PROFILE' AND hca.owner_table_id(+) = ptp.party_tax_profile_id AND hca.class_category = zft.owner_id_char(+) AND pv.vendor_id = pvs.vendor_id AND pvs.org_id = hou.organization_id AND payee.org_id(+) = pvs.org_id AND payee.party_site_id(+) = pvs.party_site_id AND payee.supplier_site_id(+) = pvs.vendor_site_id AND ppm.ext_pmt_party_id(+) = payee.ext_payee_id AND ppm.payment_flow(+) = 'DISBURSEMENTS' AND (ppm.inactive_date(+) IS NULL OR ppm.inactive_date(+) >= trunc(SYSDATE)) AND ppm.payment_method_code = ipm.payment_method_code(+) AND pvs.terms_id = att.term_id(+) AND att.language(+) = 'ZHS' AND att.enabled_flag(+) = 'Y' AND pvs.accts_pay_code_combination_id = gcc1.code_combination_id(+) AND pvs.prepay_code_combination_id = gcc2.code_combination_id(+) AND pv.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(+) --AND pv.vendor_id = 15 ORDER BY pv.vendor_name; /* SELECT COUNT(1) FROM AP_SUPPLIER_SITES_ALL; */
供应商信息全SQL
转载本文章为转载内容,我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题,欢迎原作者联系我们进行内容更正或删除文章。
提问和评论都可以,用心的回复会被更多人看到
评论
发布评论
相关文章