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;

    }

}

Adding a tip on OAF page Programatically

         OAWebBean ntfDetailsFn = webBean.findChildRecursive("NtfDetailsFn");
                                 if (ntfDetailsFn != null) {
                                     OATipBean tip =
                                         (OATipBean)createWebBean(pageContext, OAWebBeanConstants.TIP_BEAN, null, "aName");
                                     OAStaticStyledTextBean tipText =
                                         (OAStaticStyledTextBean)createWebBean(pageContext, OAWebBeanConstants.STATIC_STYLED_TEXT_BEAN,
                                                                               null, "anotherName");
                                     tipText.setText("For Invoice Details please click on the link 'View Additional Invoice Details'");

                                     CSSStyle customStyle = new CSSStyle();
                                     customStyle.setProperty("color", "#FF0000");
                                     customStyle.setProperty("font-weight", "bold");
                                     customStyle.setProperty("font-size", "10pt");

                                     tipText.setInlineStyle(customStyle);

                                     // Add the tip text to the tip bean.
                                     tip.addIndexedChild(tipText);
                                     ntfDetailsFn.addIndexedChild(tip);
                                 }

Assigning Responsibility to many users in bulk

CREATE OR REPLACE PROCEDURE XX_bulk_resp_assignment AS

  l_responsibility_key   VARCHAR2(250) := 'XX_AP_INQUIRY';
  l_resp_app             VARCHAR2(30) := 'SQLAP';
  l_security_group_key   VARCHAR2(100) := 'STANDARD';
  l_processed_count      NUMBER := 0;
  l_new_asg_count        NUMBER := 0;
  l_reactivate_asg_count NUMBER := 0;
  l_no_action_count      NUMBER := 0;
  l_asg_exists_count     NUMBER := 0;

  l_error_count NUMBER := 0;

  CURSOR csr_act_emps IS
    SELECT user_name FROM XXhr_active_employees emps WHERE emps.assignment_primary_flag = 'Y' ORDER BY user_name;

  CURSOR csr_chk_resp_exists(p_user_name VARCHAR2) IS
    SELECT 'x' exist,
           decode(sign(nvl(c.end_date, SYSDATE + 1) - SYSDATE), 1, 'A', 'I') user_status,
           decode(sign(nvl(a.end_date, SYSDATE + 1) - SYSDATE), 1, 'A', 'I') asg_status,
           c.user_name,
           a.start_date
      FROM fnd_user_resp_groups_direct a,
           fnd_responsibility_vl       b,
           fnd_user                    c,
           XXhr_active_employees      d
     WHERE a.responsibility_id = b.responsibility_id
       AND b.responsibility_key = l_responsibility_key
       AND c.user_id = a.user_id
       AND c.user_name = p_user_name
       AND d.user_name = c.user_name;

  r_exists csr_chk_resp_exists%ROWTYPE;

  l_file       utl_file.file_type;
  l_output_dir VARCHAR2(30);

BEGIN


  SELECT instance_name INTO l_output_dir FROM v$instance;
  l_output_dir := '/applcsf/' || l_output_dir || '/temp';

  l_file := utl_file.fopen(l_output_dir, 'XX_bulk_resp_asg2.log', 'A');
  
  IF (l_file.id IS NOT NULL) THEN
    dbms_output.put_line('file opened');
  ELSE 
    dbms_output.put_line('error opening file');
  END IF;
    
  
  -- Sync program required to be run in Prod before running this procedure;

  /*BEGIN
    utl_file.put_line(l_file, 'calling sync prgram ...');
    WF_LOCAL_SYNCH.BulkSynchronization('ALL');
    
    utl_file.put_line(l_file, 'Sync completed ...');
    COMMIT;
  EXCEPTION
     WHEN OTHERS THEN
        utl_file.put_line(l_file, 'ERROR: '||SQLERRM); 
  END;*/
  utl_file.fflush(l_file);

  FOR r1 IN csr_act_emps LOOP
    r_exists := NULL;
  
    OPEN csr_chk_resp_exists(r1.user_name);
    FETCH csr_chk_resp_exists
      INTO r_exists;
    CLOSE csr_chk_resp_exists;
  
    utl_file.put(l_file, r1.user_name || ',');
  
    BEGIN
    
      IF r_exists.exist IS NULL THEN
      
        utl_file.put(l_file, 'New Assignment' || chr(10));
      
        fnd_user_pkg.addresp(username       => r1.user_name,
                             resp_app       => l_resp_app,
                             resp_key       => l_responsibility_key,
                             security_group => l_security_group_key,
                             description    => NULL,
                             start_date     => SYSDATE,
                             end_date       => NULL);
        l_new_asg_count := l_new_asg_count + 1;
      
      ELSIF r_exists.user_status = 'A' AND r_exists.asg_status = 'I' THEN
      
        utl_file.put(l_file, 'Updating Existing Assignment' || chr(10));
      
        fnd_user_pkg.addresp(username       => r1.user_name,
                             resp_app       => l_resp_app,
                             resp_key       => l_responsibility_key,
                             security_group => l_security_group_key,
                             description    => NULL,
                             start_date     => r_exists.start_date,
                             end_date       => NULL);
        l_reactivate_asg_count := l_reactivate_asg_count + 1;
      
      ELSIF r_exists.user_status = 'A' AND r_exists.asg_status = 'A' THEN
        utl_file.put(l_file, 'No Action Active Resp Assignment already exists' ||chr(10));
        l_no_action_count := l_no_action_count + 1;
      
      ELSIF r_exists.user_status = 'I' THEN
        utl_file.put(l_file, 'No Action, Inactive User'|| chr(10));
        l_asg_exists_count := l_asg_exists_count + 1;
      
      END IF;
      
    EXCEPTION
      WHEN OTHERS THEN
        utl_file.put_line(l_file, 'Error: ' || SQLERRM);
      
        l_error_count := l_error_count + 1;

        IF l_error_count >= 10 THEN
          EXIT;
        END IF;
      
    END;
  
    l_processed_count := l_processed_count + 1;
    
    ROLLBACK;
    
    utl_file.fflush(l_file);
    
  END LOOP;

  utl_file.put_line(l_file, '');
  utl_file.put_line(l_file,'l_new_asg_count: ' || l_new_asg_count);
  utl_file.put_line(l_file,'l_reactivate_asg_count: ' || l_reactivate_asg_count);
  utl_file.put_line(l_file,'l_no_action_count: ' || l_no_action_count);
  utl_file.put_line(l_file,'l_asg_exists_count: ' || l_asg_exists_count);
  utl_file.put_line(l_file,'l_processed_count: ' || l_processed_count);
  utl_file.put_line(l_file,'l_error_count: ' || l_error_count);

  
  utl_file.fclose(l_file);

  --COMMIT;
END;

Query to retrieve Invoice Holds

SELECT vnd.vendor_name, aia.invoice_num, pha.segment1 po_number, pla.line_num, ah.hold_lookup_code, ah.release_lookup_code,

   (nvl((pda.quantity_ordered - nvl(pda.quantity_cancelled, 0)) * pll.price_override, 0)) AS ordered_amount,

   (pda.amount_billed) AS billed_amount,

   (pda.amount_billed - (nvl((pda.quantity_ordered - nvl(pda.quantity_cancelled, 0)) * pll.price_override, 0))) difference,

   pda.quantity_ordered, pda.quantity_delivered

FROM

    apps.ap_holds_all ah,

    apps.po_line_locations_all pll,

    apps.ap_invoices_all aia,

    apps.po_distributions_all pda,

    apps.po_lines_all pla,

    apps.po_headers_all pha,

    apps.ap_suppliers vnd

WHERE ah.line_location_id = pll.line_location_id

    AND ah.invoice_id = aia.invoice_id

    AND pla.po_line_id = pll.po_line_id

    AND pda.line_location_id = pll.line_location_id

    AND pha.po_header_id = pla.po_header_id

    AND ah.creation_date BETWEEN (SYSDATE - 120) AND SYSDATE

    AND vnd.vendor_id = pha.vendor_id

    ORDER BY vendor_name, invoice_num, pha.segment1, pla.line_num;

Query to find iSupplier Users list

Query to find iSupplier Users list


SELECT WEB_USER_ID, USR.USER_NAME, USR.EMAIL_ADDRESS, AP_SUPP.VENDOR_NAME
  FROM AK_WEB_USER_SEC_ATTR_VALUES A, AP_SUPPLIERS AP_SUPP, FND_USER USR
 WHERE ATTRIBUTE_CODE = 'ICX_SUPPLIER_ORG_ID'
   AND ATTRIBUTE_APPLICATION_ID = 177
   AND NUMBER_VALUE = AP_SUPP.VENDOR_ID
   AND USR.USER_ID = A.WEB_USER_ID;

BI Publisher: Apply formatting on Group of rows based on data

Output Needed:

Quick Summary of Steps:

Detail of each step:

1. InitVarBIDNO
<?xdoxslt:set_variable($_XDOCTX, 'PREV_BIDNO', '0')?>


2. InitVarCount: 
<?xdoxslt:set_variable($_XDOCTX, 'XXCOUNT', 1)?>


3. For Each
<?for-each:G_BOARD_DOC?>  
<?sort:MASTER_RFQ_NUM;'ascending';data-type='text'?> 
<?sort:BDOC_TYPE;'descending';data-type='text'?> 
<?sort:VENDOR_NAME;'ascending';data-type='text'?>

4.UpdateBdocCount
<?if:xdoxslt:get_variable($_XDOCTX,'PREV_BIDNO')!=MASTER_RFQ_NUM?><?xdoxslt:set_variable($_XDOCTX, 'XXCOUNT', xdoxslt:get_variable($_XDOCTX,'XXCOUNT')+1)?><?end if?>


5. IfSameBdocAsPreviousRow
<?if@row:xdoxslt:get_variable($_XDOCTX,'XXCOUNT') mod 2=0?>
<xsl:attribute name="color" xdofo:ctx="incontext">rgb(153, 0, 0)</xsl:attribute>
<?end if?>


6. UpdatePrevValue
<?xdoxslt:set_variable($_XDOCTX, 'PREV_BIDNO',MASTER_RFQ_NUM)?>