学步园

以下是我整理的和供应商相关的表结构和重要信息的SQL语句：

 1.  供应商主表数据：

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

2. 供应商银行信息：

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

3. 供应商开户行地址信息:

SELECT hps.party_id party_id,<br /> hps.party_site_id party_site_id,<br /> hl.location_id location_id,<br /> hl.country country,<br /> hl.province province,<br /> hl.city city,<br /> hl.address1 address1,<br /> hl.address2 address2,<br /> hl.address3 address3,<br /> hl.address4 address4<br /> FROM hz_party_sites hps,<br /> hz_locations hl<br /> WHERE hps.location_id = hl.location_id<br /> AND hps.party_id = p_bank_id<br /> ORDER BY hps.creation_date

4. 供应商联系人信息：

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

5. 供应商地址主信息：

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

6. 供应商地址联系人信息：phone、fax和Email

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

7. 供应商地址收件人信息：

SELECT assa.party_site_id<br /> INTO l_party_site_id<br /> FROM ap_supplier_sites_all assa<br /> WHERE assa.vendor_site_id = p_vendor_site_id;<br /> --根据party_site_id得到供应商地址的收件人名称<br /> SELECT hps.addressee<br /> INTO l_site_person<br /> FROM hz_party_sites hps<br /> WHERE hps.party_site_id = l_party_site_id;<br /> RETURN l_site_person;

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

SELECT * FROM iby_pmt_instr_uses_all

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

SELECT * FROM iby_external_payees_all;