Hi Friends,
Today i am posting Query to find Supplier, Sites and Contact Information.
AP_SUPPLIERS
AP_SUPPLIER_SITES_ALL
AP_SUPPLIER_CONTACTS
In R12, Contact person information is stored in HZ_PARTIES table not in ap_supplier_contacts.
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
AND asp.vendor_name = 'LS Supplier';
Today i am posting Query to find Supplier, Sites and Contact Information.
AP_SUPPLIERS
AP_SUPPLIER_SITES_ALL
AP_SUPPLIER_CONTACTS
In R12, Contact person information is stored in HZ_PARTIES table not in ap_supplier_contacts.
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
AND asp.vendor_name = 'LS Supplier';
Thanks.
ReplyDeleteThank you, saved a day's work
ReplyDeletenice posting. thanks
ReplyDeleteYou note that "In R12, Contact person information is stored in HZ_PARTIES table not in ap_supplier_contacts." but I see you use ap_supplier_contacts table in your SQL. Is there a reason for that? Do you know if Oracle is still using that table because we have an issue with supplier lookup in iProcurement showing the same supplier site 5 times and the only difference is that the creation date in the ap_supplier_contacts table.
ReplyDeletewe don't need the inactive contacts ,, what condition can we add?
ReplyDeleteWOnderful, thanks
ReplyDeleteExcellent !
ReplyDeleteThanks...
ReplyDeleteThis is missing an outer join for when no contacts exist for the supplier site.
ReplyDeleteThis comment has been removed by a blog administrator.
ReplyDelete