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;
*/
