Query Supplier Information Oracle R12

Hei..heeeiii…Hari ini saya mau posting Query tentang informasi supplier di oracle R12, kita ngambil dari tabel berikut.

AP_SUPPLIERS                   
AP_SUPPLIER_SITES_ALL
AP_SUPPLIER_CONTACTS
HZ_PARTIES
HZ_OPERATING_UNIT

Di R12 kontak Supplier berada di HZ_PARTIES, AP_SUPPLIER_CONTACTS cuma ID nya saja.

SELECT asp.vendor_id ,
asp.segment1 “Supplier Num” ,
asp.vendor_name “Supplier Name” ,
ass.vendor_site_code “site name” ,
hou.name “Operating Unit Name” ,
ass.address_line1 ,
ass.city ,
ass.state ,
ass.zip ,
ass.country ,
ass.phone ,
person.person_first_name ,
person.person_last_name ,
pty_rel.primary_phone_number ,
pty_rel.email_address
FROM ap_suppliers asp ,
ap_supplier_sites_all ass ,
ap_supplier_contacts apsc ,
hz_parties person ,
hz_parties pty_rel,
hr_operating_units hou
WHERE ass.vendor_id = asp.vendor_id
AND apsc.per_party_id = person.party_id
AND apsc.rel_party_id = pty_rel.party_id
AND ass.org_id = hou.organization_id
AND apsc.org_party_site_id = ass.party_site_id;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s