Query to retrieve all Contacts associated with suppliers registered in iSupplier

SELECT party_id AS g1_party_id,
       segment1 AS g1_vendor_no,
       vendor_name AS g1_vendor_name,
       party_name AS g1_contact_name,
       primary_phone_country_code AS g1_cnt_cntry,
       primary_phone_area_code g1_cnt_area,
       primary_phone_number g1_cnt_phone,
       vendor_site_code AS g1_vendor_site_code,
       (SELECT MAX(start_time) last_logged_on FROM apps.fnd_logins log WHERE log.user_id = user_id) AS g1_last_login,
       email_address AS g1_email_address,
       vendor_type_lookup_code AS g1_vendor_type_lookup_code,
       decode(sign(nvl(end_date_active, SYSDATE + 1) - SYSDATE), 1, 'A', 0, 'A', 'I') AS g1_vendor_status,
       web_user_id AS g1_web_user_id,
       decode(web_user_id, NULL, 'N', 'Y') AS g1_isp_portal,
       end_date_active AS g1_end_date_active,
       creation_date AS g1_vnd_creation_date
  FROM (SELECT DISTINCT asu.party_id,
                        asu.segment1,
                        asu.vendor_name,
                        hpc.party_name,
                        hpr.primary_phone_country_code,
                        hpr.primary_phone_area_code,
                        hpr.primary_phone_number,
                        assa.vendor_site_code,
                        assa.vendor_site_id,
                        asco.vendor_contact_id,
                        usr.user_id,
                        usr.email_address,
                        asu.end_date_active,
                        aw.web_user_id,
                        asu.creation_date,
                        asu.vendor_type_lookup_code
          FROM hz_relationships                 hr,
               ap_suppliers                     asu,
               ap_supplier_sites_all            assa,
               ap_supplier_contacts             asco,
               hz_org_contacts                  hoc,
               hz_parties                       hpc,
               hz_parties                       hpr,
               hz_contact_points                hpcp,
               apps.ak_web_user_sec_attr_values aw,
               apps.fnd_user                    usr
         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 aw.number_value(+) = hpc.party_id
           AND usr.user_id(+) = aw.web_user_id
           AND aw.attribute_code(+) = 'ICX_CUSTOMER_CONTACT_ID'
           AND aw.attribute_application_id(+) = 178
           AND SYSDATE <= nvl(hr.end_date, SYSDATE + 1)
        --AND asu.segment1 = '10002'
        UNION ALL
        /*non site contacts*/
        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,
                        NULL, --,assa.vendor_site_code
                        NULL, --,assa.vendor_site_id
                        asco.vendor_contact_id,
                        usr.user_id,
                        usr.email_address,
                        asu.end_date_active,
                        aw.web_user_id,
                        asu.creation_date,
                        asu.vendor_type_lookup_code
          FROM hz_relationships hr,
               ap_suppliers     asu,
               --,ap_supplier_sites_all assa
               ap_supplier_contacts             asco,
               hz_org_contacts                  hoc,
               hz_parties                       hpc,
               hz_parties                       hpr,
               hz_contact_points                hpcp,
               apps.ak_web_user_sec_attr_values aw,
               apps.fnd_user                    usr
         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 NOT EXISTS (SELECT 1
                  FROM ap_supplier_contacts asco1
                 WHERE asco.vendor_contact_id = asco1.vendor_contact_id
                   AND asco.org_party_site_id IS NOT NULL)
              --AND asu.segment1 = '10002'
           AND aw.number_value(+) = hpc.party_id
           AND usr.user_id(+) = aw.web_user_id
           AND aw.attribute_code(+) = 'ICX_CUSTOMER_CONTACT_ID'
           AND aw.attribute_application_id(+) = 178
           AND SYSDATE <= nvl(hr.end_date, SYSDATE + 1))
           :p_where_vnd_contact_list = 1
           &p_where_active_inactive
 ORDER BY g1_vendor_no

Calling Google Directions API and parsing returned JSON output using Java.

private String getDistanceFromDirectionsSvc(String paramOrigin, String paramDestination) throws IOException {

    URL url =

        new URL("https://maps.googleapis.com/maps/api/directions/json?" + "origin=" + URLEncoder.encode(paramOrigin,

                "UTF-8") +

            "&destination=" + URLEncoder.encode(paramDestination, "UTF-8") + "&alternatives=true" +

            "&mode=DRIVING" + "&sensor=FALSE" + "&key=xyz");

    HttpURLConnection conn = (HttpURLConnection) url.openConnection();

    conn.setRequestMethod("GET");

    String line, outputString = "";

    BufferedReader reader = new BufferedReader(new InputStreamReader(conn.getInputStream()));

    while ((line = reader.readLine()) != null) {

        outputString += line;

    }

    System.out.println(outputString);

    JSONParser parser = new JSONParser();

    Object obj = null;

    try {

        obj = parser.parse(outputString);

    } catch (ParseException e) {

        e.printStackTrace();

    }

    JSONObject jsonObject = (JSONObject) obj;

    String status = (String) jsonObject.get("status");

    if (status.equals("OK")) {

        JSONArray wayPointsArray = (JSONArray) jsonObject.get("geocoded_waypoints");

        Iterator iter = (Iterator) wayPointsArray.iterator();

        String geoCoderStatus = "";

        String errorMessage = "";

        while (iter.hasNext()) {

            JSONObject wayPointObject = (JSONObject) iter.next();

            geoCoderStatus = wayPointObject.get("geocoder_status").toString();

            String partialMatch = "false";

            if (wayPointObject.containsKey("partial_match")) {

                partialMatch = wayPointObject.get("partial_match").toString();

            }

            if (!geoCoderStatus.equals("OK")) {

                errorMessage = "Invalid GeoCoder status returned = " + geoCoderStatus;

                geoCoderStatus = "FAILED";

            }

            if (partialMatch.equals("true")) {

                errorMessage = "Exact Address match not found. Please make sure Address is valid";

                geoCoderStatus = "FAILED";

            }

        }

        if (geoCoderStatus.equals("OK")) {

            JSONArray routesArray = (JSONArray) jsonObject.get("routes");

            Iterator i = (Iterator) routesArray.iterator();

            Integer shortestDistance = 0;

            while (i.hasNext()) {

                JSONObject routeObject = (JSONObject) i.next();

                JSONArray legsArray = (JSONArray) routeObject.get("legs");

                Iterator j = (Iterator) legsArray.iterator();

                while (j.hasNext()) {

                    JSONObject legsObject = (JSONObject) j.next();

                    JSONObject distanceObject = (JSONObject) legsObject.get("distance");

                    int distance = Integer.parseInt(distanceObject.get("value").toString());

                    // ++ Get the shortest of all Alternate Routes.

                    if (shortestDistance == 0 || distance < shortestDistance) {

                        shortestDistance = distance;

                    }

                }

            }

            Double distMiles = shortestDistance.doubleValue();

            distMiles = distMiles / 1609.34; //convert in miles

            DecimalFormat df = new DecimalFormat("#.##");

            return df.format(distMiles).toString();

        } else {

            OAException geoCodeException = new OAException(errorMessage);

            throw geoCodeException;

        }

    } else {

        OAException apiStatusException = new OAException("API Service Status Error = " + status);

        throw apiStatusException;

    }

}