SQL Query for Oracle R12 Supplier Headers, Sites and Contacts

  SELECT DISTINCT
         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,
         (SELECT HCP5.PHONE_AREA_CODE
            FROM HZ_CONTACT_POINTS HCP5
           WHERE     HCP5.OWNER_TABLE_NAME = 'HZ_PARTIES'
                 AND APSC.REL_PARTY_ID = HCP5.OWNER_TABLE_ID
                 AND HCP5.CONTACT_POINT_TYPE = 'PHONE'
                 AND HCP5.PHONE_LINE_TYPE = 'FAX'
                 AND HCP5.PRIMARY_FLAG = 'N'
                 AND ROWNUM < 2)
            FAX_AREA_CODE,
         (SELECT HCP6.PHONE_NUMBER
            FROM HZ_CONTACT_POINTS HCP6
           WHERE     HCP6.OWNER_TABLE_NAME = 'HZ_PARTIES'
                 AND APSC.REL_PARTY_ID = HCP6.OWNER_TABLE_ID
                 AND HCP6.CONTACT_POINT_TYPE = 'PHONE'
                 AND HCP6.PHONE_LINE_TYPE = 'FAX'
                 AND HCP6.PRIMARY_FLAG = 'N'
                 AND ROWNUM < 2)
            FAX,
         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     1 = 1
         AND 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
ORDER BY hou.NAME