Oracle R12 Supplier Headers, Sites and Contacts Query

  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