SQL Query to Get the Supplier Contact Details

Suppliers & Supplier Sites - Details

SELECT hp.party_name                    supplier_name,
       ps.segment1                      supplier_number,
       ps.vendor_type_lookup_code       supplier_type,
       ps.organization_type_lookup_code supplier_tax_org_type,
       ps.business_relationship,
       hop.duns_number_c                supplier_duns_number,
       ps.customer_num,
       ps.standard_industry_class       supplier_sic,
       hop.party_number                 supplier_registry_id,
       hop.year_established,
       hop.mission_statement,
       psp.income_tax_id                supplier_taxpayer_id,
       hps.party_site_name              address_name,
       hp.address1,
       hp.address2,
       hp.city,
       hp.state,
       hp.county,
       hp_contact.person_last_name
       ||', '
       ||hp_contact.person_pre_name_adjunct
       ||' '
       ||hp_contact.person_first_name   contact_person
FROM   poz_suppliers ps
       join hz_parties hp
         ON hp.party_id = ps.party_id
       join hz_organization_profiles hop
         ON hop.party_id = ps.party_id
       left join poz_suppliers_pii psp
              ON psp.vendor_id = ps.vendor_id
       left join hz_party_sites hps
              ON hps.party_site_id = hp.iden_addr_party_site_id
       left join hz_parties hp_contact
              ON hp_contact.party_id = hp.preferred_contact_person_id

Suppliers & Supplier Sites - Contact Details

SELECT hpo.party_name              supplier_name,

       hpp.person_pre_name_adjunct title,

       hpp.person_first_name,

       hpp.person_last_name,

       hpp.status                  contact_status,

       pu.username,

       hc.job_title,

       hps.party_site_name,

       email.email_address,

       email.last_updated_by,

       email.last_update_date,

       phone.phone_area_code,

       phone.phone_number

FROM   hz_parties hpo

       join hz_relationships hzr

         ON hzr.object_id = hpo.party_id

       join hz_parties hpp

         ON hpp.party_id = hzr.subject_id

       join hz_org_contacts hc

         ON hc.party_relationship_id = hzr.relationship_id

       left join hz_org_contact_roles hor

              ON hor.org_contact_id = hc.org_contact_id

       left join poz_supplier_contacts poc

              ON poc.relationship_id = hzr.relationship_id

       left join hz_party_sites hps

              ON hps.party_site_id = poc.party_site_id

       left join poz_suppliers ps

              ON ps.party_id = hpo.party_id

       left join per_users pu

              ON pu.user_guid = hpp.user_guid

       left join (SELECT

                                              email_address,

                         owner_table_id,

                         last_updated_by,

                         To_char(last_update_date, 'YYYY-MM-DD HH24:MI:SS')

                                              last_update_date

                  FROM   hz_contact_points hcp

                  WHERE  hcp.contact_point_type = 'EMAIL'

                         AND hcp.owner_table_name = 'HZ_PARTIES') email

              ON email.owner_table_id = hpp.party_id

       left join (SELECT phone_country_code,

                         phone_area_code,

                         phone_number,

                         phone_extension,

                         owner_table_id

                  FROM   hz_contact_points hcp

                  WHERE  hcp.contact_point_type = 'PHONE'

                         AND hcp.owner_table_name = 'HZ_PARTIES'

                         AND hcp.phone_line_type = 'GEN') phone

              ON phone.owner_table_id = hpp.party_id

WHERE  1 = 1

       AND hpo.party_type = 'ORGANIZATION'

       AND hpp.party_type = 'PERSON'

       AND hzr.relationship_code = 'CONTACT_OF'

ORDER  BY email.last_update_date DESC