How to See Supplier Contact Information Using SQL in R12

The method of storing information for supplier contacts is very much different in R12 than it is
in 11i. Most of the information is stored in various TCA tables.

[@more@]

The method of storing information for supplier contacts is very much different in R12 than it is
in 11i. Most of the information is stored in various TCA tables. The table AP_SUPPLIER_CONTACTS
is a linking table between the supplier address and the various TCA tables if the contact is
assigned to an address in the form or during Import. For Contacts created in R12 columns such as
Name and Address elements are not populated in AP_SUPPLIER_CONTACTS. Contacts that were directly upgraded from 11i will have their legacy values in this table, but the form and other processes
that use or display this information for contacts use the TCA tables.

When a Contact is created, two rows are created in HZ_PARTIES (though it is possible that only one is created if the name already existed in the system when the contact was entered). One is of type PERSON, and the other is of type PARTY_RELATIONSHIP. The PERSON type party stores name information and the like, while address elements and contact information (phone number, for example) are stored in the PARTY_RELATIONSHIP type party. These are linked to the ORGANIZATION type Party of the Supplier by a row in HZ_RELATIONSHIPS. The kind of contact (Manager, Sales Rep, etc) is stored in HZ_ORG_CONTACTS. When you assign a contact to a supplier address you create a row in AP_SUPPLIER_CONTACTS that links all things together and maps it back to the party site created as the address.

The link column in AP_SUPPLIER_CONTACTS map tp the following tables/columns

Per_Party_ID = Party_ID of PERSON Party in HZ_PARTIES
Relationship_ID = Relationship_ID of Rows in HZ_RELATIONHIPS
Rel_Party_ID = Party_ID of PARTY_RELATIONSHIP Party in HZ_PARTIES
Party_Site_ID = Maps to the Party Site created for the PARTY_RELATIONSHIP Party
Org_Contact_ID = Org_Contact_ID from HZ_ORG_CONTACTS
Org_Party_Site_ID = Party_Site_ID of the Supplier Site row.

Assuming you know the party_site_id for an address (which is the value in
AP_SUPPLIER_SITES_ALL.party_Site_ID for a site that is associated with that address) you can use
this query to get the contact for that address.

SELECT PERSON.person_first_name, PERSON.person_last_name, PTY_REL.address1,
PTY_REL.City, PTY_REL.state, PTY_REL.country,
PTY_REL.Primary_phone_area_code, PTY_REL.primary_phone_number
FROM hz_parties PERSON, hz_parties PTY_REL, ap_supplier_contacts APSC
WHERE APSC.per_party_id = PERSON.party_id
AND APSC.rel_party_id = PTY_REL.party_id
AND APSC.org_party_site_id =