1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!
mike12345

R12 Vendor Contacts query 2017-09-27

R12 vendor contact query

  1. mike12345
    Code (SQL):
    SELECT DISTINCT
      asu.party_id,
      asu.segment1 Supp_Num ,
      asu.vendor_name ,
      hpc.party_name Contact_Name ,
      hpr.primary_phone_country_code cnt_cntry ,
      hpr.primary_phone_area_code cnt_area ,
      hpr.primary_phone_number cnt_phone ,
      assa.vendor_site_code ,
      assa.vendor_site_id ,
      asco.vendor_contact_id ,
      assa.party_site_id ,
      asco.org_party_site_id
    FROM
      apps.hz_relationships hr ,
      apps.ap_suppliers asu ,
      apps.ap_supplier_sites_all assa ,
      apps.ap_supplier_contacts asco ,
      apps.hz_org_contacts hoc ,
      apps.hz_parties hpc ,
      apps.hz_parties hpr ,
      apps.hz_contact_points hpcp
    WHERE
      hoc.party_relationship_id = hr.relationship_id
    AND hr.subject_id           = asu.party_id
    AND hr.relationship_code    = 'CONTACT'
    AND hr.object_table_name    = 'HZ_PARTIES'
    AND asu.vendor_id           = assa.vendor_id
    AND hr.object_id            = hpc.party_id
    AND hr.party_id             = hpr.party_id
    AND asco.relationship_id    = hoc.party_relationship_id
    AND assa.party_site_id      = asco.org_party_site_id
    AND hpr.party_type          ='PARTY_RELATIONSHIP'
    AND hpr.party_id            = hpcp.owner_table_id
    AND hpcp.owner_table_name   = 'HZ_PARTIES'
    AND assa.vendor_site_id     = asco.vendor_site_id
    AND asu.vendor_name         = '&vendor_name'