maandag 16 november 2015

Supplier contacts

Suppliers and their contacts are also created in HZ_PARTIES. The supplier is an organization, where the contact is a person. They get a relationship in HZ_RELATIONSHIPS (usually type CONTACT_OF).
You can query the suppliers in hz_parties with their relationships as follows. They can be related to site or to top level.
Note that HZ_RELATIONSHIPS usually contains two records. One for the PERSON-ORGANIZATION relationship (type CONTACT_OF) and one for the ORGANIZATION-PERSON relationship (CONTACT usually). You can check on subject_type and object_type to make sure you fetch the right relationship.

select asup.vendor_name
      ,asup.segment1 vendor_number
      ,org_party.party_name  organization
      ,cont_party.party_name   contact_person
      ,cont_party.party_number contact_person_number
      , hr.status relation_status
      , org_party.status org_status
      , cont_party.status contact_status
      , hoc.status contact_status
from   hz_org_contacts hoc
      ,hz_relationships hr
      ,hz_parties org_party
      ,hz_parties cont_party
     ,ap_suppliers asup
where hr.relationship_id    = hoc.party_relationship_id
and   hr.subject_id         = cont_party.party_id
and   hr.subject_table_name = 'HZ_PARTIES'
and hr.subject_type = 'PERSON'
and hr.object_type = 'ORGANIZATION'
and   hr.relationship_code  = 'CONTACT_OF'
and   hr.object_id          = org_party.party_id
and   hr.object_table_name  = 'HZ_PARTIES'
and   asup.party_id         = org_party.party_id
and   hoc.party_site_id     IS NULL
and   org_party.status      = 'A'
and    cont_party.status    = 'A'
and   hoc.status            = 'A'

Geen opmerkingen:

Een reactie posten

Opmerking: Alleen leden van deze blog kunnen een reactie posten.