DCP

Wednesday, 28 June 2017

Duplicate PO Emails being sent out to Supplier that is registered on iSupplier Portal



Known Oracle Issue: Metalink Note References:
How Suppliers Can Avoid E-mail Notifications? (Doc ID 461963.1)

Fix: Customize PO Approval Workflow:

Open up Email PO Process.


This Process calls Function: Send Notification all web Suppliers










CREATE OR REPLACE PACKAGE XXPO_REQAPPROVAL_INIT1 AS
procedure Send_WS_Notif_Yes_No(     itemtype        in varchar2,
                              itemkey         in varchar2,
                              actid           in number,
                              funcmode        in varchar2,
                              resultout       out NOCOPY varchar2    ) ;
END;
/
                           
CREATE OR REPLACE PACKAGE BODY XXPO_REQAPPROVAL_INIT1 AS
procedure Send_WS_Notif_Yes_No(     itemtype        in varchar2,
                              itemkey         in varchar2,
                              actid           in number,
                              funcmode        in varchar2,
                              resultout       out NOCOPY varchar2    )  IS
l_send_notif varchar2(1):= 'N';                             
BEGIN                             
   l_send_notif := 'N'; 
   resultout := wf_engine.eng_completed || ':' || l_send_notif ;
END;
END;
/

  


Friday, 16 June 2017

Extract PO Attachments

CREATE OR REPLACE PROCEDURE dsdpo_extract_po_files
AS

  v_file     utl_file.file_type;
  v_line     varchar2(1000);
  v_blob_len number;
  v_pos      number;
  v_buffer   raw(32764);
  v_amt      binary_integer := 32764;

  cursor cur_files
  is
    SELECT ad.entity_name, ad.seq_num Seq , ad.pk1_value, h.segment1, fl.file_name, fl.file_data
    FROM fnd_documents d,
    fnd_attached_documents ad,
    fnd_documents_vl dtl,
    po_headers_all h,
    fnd_lobs fl,
    fnd_document_datatypes fdd
    WHERE d.document_id = ad.document_id
    AND ad.entity_name = 'PO_HEAD'
    AND ad.pk1_value = to_char(h.po_header_id)
--    AND h.segment1 = '436223'
    AND d.document_id = dtl.document_id
    AND fl.file_id = d.media_id
    AND fdd.user_name = 'File'
    and h.po_header_id in(

    select po_header_id from apps.po_headers_all
    where vendor_id =7951
    and creation_date >'01-JAN-2012' and type_lookup_code='STANDARD')
    and dtl.file_name like '%.pdf';

begin
  -- File Attachments
  for c_file in cur_files
  loop
    v_file := utl_file.fopen('/applcsf/ebsdev/temp','dsd_'||c_file.file_name, 'wb', 32764);

    v_blob_len := dbms_lob.getlength(c_file.file_data);
    v_pos := 1;
    while v_pos < v_blob_len
    loop
      dbms_lob.read(c_file.file_data,v_amt,v_pos,v_buffer);
      utl_file.put_raw(v_file,v_buffer,true);
      v_pos := v_pos + v_amt;
    end loop;
    utl_file.fclose(v_file);
  end loop;

end;

Thursday, 13 April 2017

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;

    }

}

Thursday, 2 March 2017

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);
                                 }

Tuesday, 6 December 2016

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;