How Supplier informations are stored in R12? (Query for fetching supplier info)
Supplier data in R12
Below query will tell you about the various tables used to store supplier information and their links with different modules like TCA, AP, E-Business Tax and Payment.
We tested this query in R12.1.1 instance.
SELECT hp.party_id
,hp.party_number
,hp.party_name
,hp.party_type
,hp.orig_system_reference
,hp.tax_reference
,hpua.party_usg_assignment_id
,hpua.party_usage_code
,hpua.effective_start_date
,hpua.effective_end_date
,hop.organization_profile_id
,hop.organization_name_phonetic
,hop.organization_type
,hps.party_site_id
,hps.party_site_number
,hps.orig_system_reference party_site_reference
,hps.party_site_name
,aps.vendor_id
,aps.vendor_name
,aps.vendor_name_alt
,aps.segment1
,aps.vendor_type_lookup_code
,aps.payment_currency_code
,aps.invoice_currency_code
,aps.hold_all_payments_flag
,aps.vat_registration_num
,aps.match_option
,apss.vendor_site_id
,apss.vendor_site_code
,apss.purchasing_site_flag
,apss.rfq_only_site_flag
,apss.pay_site_flag
,apss.address_line1
,apss.address_line2
,apss.zip
,apss.country
,apss.bill_to_location_id
,apss.accts_pay_code_combination_id
,iep.ext_payee_id
,iep.payment_function
,iep.org_type
,iep.default_payment_method_code
,iep.remit_advice_delivery_method
,iep.remit_advice_fax
,zxtp.party_type_code
,zxtp.rep_registration_number
FROM hz_parties hp
,hz_party_usg_assignments hpua
,hz_organization_profiles hop
,hz_party_sites hps
,ap_suppliers aps
,ap_supplier_sites_all apss
,iby_external_payees_all iep
,zx_party_tax_profile zxtp
WHERE 1 = 1
AND hp.party_id = hpua.party_id
AND hp.party_id = hop.party_id
AND hp.party_id = hps.party_id
AND hp.party_id = aps.party_id
AND aps.vendor_id = apss.vendor_id
AND hps.party_site_id = apss.party_site_id
AND hp.party_id = iep.payee_party_id
AND apss.vendor_site_id= iep.supplier_site_id
AND hps.party_site_id = iep.party_site_id
AND hp.party_id = zxtp.party_id
AND hp.party_id = 1234;
Comments
Post a Comment