1 Reply Latest reply on Nov 7, 2014 8:23 AM by Migs_Isip

    Cannot Update PO promised Date

    Migs_Isip

      Hi to all! i've been having problems trying to update the Promised Date of a PO using "po_change_api1_s.update_po" API.
      I've checked out numerous sample scripts, but they all get the error ORA-06531:

      Reference to uninitialized collection

      The weird thing is, it runs the first time, but it states that

      "The document is currently locked by another user. please try again later".

      I re-ran the script after that, now it gets "Reference to uninitialized collection"

      I've tried passing the value of the x_api_errors to another collection, but it still comes out as an error.

       

      please see below for script 1 (from PO Change API ~ Welcome to Oracle ERP)

      SET SERVEROUTPUT ON;

      DECLARE

          v_result        NUMBER;
          type error_text is table of varchar2(2000);
          type v_api_errors is record
          (
              error_message   error_text
          );
          v_api_error_msg    v_api_errors;    
          l_api_errors    po_api_errors_rec_type;       
          v_revision_num  po_headers_all.revision_num%TYPE;
          v_price         po_lines_all.unit_price%TYPE;
          v_quantity      po_line_locations_all.quantity%TYPE;
          v_po_number     po_headers_all.segment1%TYPE;
          v_line_num      po_lines_all.line_num%TYPE;
          v_shipment_num  po_line_locations_all.shipment_num%TYPE;
          v_promised_date DATE;   
          v_need_by_date  DATE;
          v_org_id        NUMBER;
          v_context       VARCHAR2(10);
         
          cursor po_dtl is (  select  rownum count,
                                      pha.segment1 po_number,
                                      pha.revision_num,
                                      pla.line_num,
                                      plla.promised_date,
                                      plla.need_by_date,
                                      plla.quantity,
                                      pha.org_id,
                                      shipment_num,
                                      unit_price PRICE
                              from    po_headers_all            pha,
                                      po_lines_all              pla,
                                      po_line_locations_all     plla
                              where   pla.po_header_id = pha.po_header_id
                              and     pla.po_line_id = plla.po_line_id
                              and     pha.po_header_id = plla.po_header_id
                              and     pha.segment1 = '6456920');   
                             
          x_num   number := 1;                                    
        
          FUNCTION set_context( i_user_name    IN  VARCHAR2
                               ,i_resp_name    IN  VARCHAR2
                               ,i_org_id       IN  NUMBER)
          RETURN VARCHAR2
          IS
          v_user_id             NUMBER;
          v_resp_id             NUMBER;
          v_resp_appl_id        NUMBER;
          v_lang                VARCHAR2(100);
          v_session_lang        VARCHAR2(100):=fnd_global.current_language;
          v_return              VARCHAR2(10):='T';
          v_nls_lang            VARCHAR2(100);
          v_org_id              NUMBER :=i_org_id;
          /* Cursor to get the user id information based on the input user name */
          CURSOR cur_user
          IS
              SELECT     user_id
              FROM       fnd_user
              WHERE      user_name  =  i_user_name;
          /* Cursor to get the responsibility information */
          CURSOR cur_resp
          IS
              SELECT     responsibility_id
                               ,application_id
                              ,language
              FROM       fnd_responsibility_tl
              WHERE      responsibility_name  =  i_resp_name;
          /* Cursor to get the nls language information for setting the language context */
          CURSOR cur_lang(p_lang_code VARCHAR2)
          IS
              SELECT    nls_language
              FROM      fnd_languages
              WHERE     language_code  = p_lang_code;
          BEGIN
              /* To get the user id details */
              OPEN cur_user;
              FETCH cur_user INTO v_user_id;
              IF cur_user%NOTFOUND
              THEN
                  v_return:='F';
            
              END IF; --IF cur_user%NOTFOUND
              CLOSE cur_user;

              /* To get the responsibility and responsibility application id */
              OPEN cur_resp;
              FETCH cur_resp INTO v_resp_id, v_resp_appl_id,v_lang;
              IF cur_resp%NOTFOUND
              THEN
                  v_return:='F';
            
              END IF; --IF cur_resp%NOTFOUND
              CLOSE cur_resp;

              /* Setting the oracle applications context for the particular session */
              fnd_global.apps_initialize ( user_id      => v_user_id
                                          ,resp_id      => v_resp_id
                                          ,resp_appl_id => v_resp_appl_id);

              /* Setting the org context for the particular session */
              mo_global.set_policy_context('S',v_org_id);

              /* setting the nls context for the particular session */
              IF v_session_lang != v_lang
              THEN
                  OPEN cur_lang(v_lang);
                  FETCH cur_lang INTO v_nls_lang;
                  CLOSE cur_lang;
                  fnd_global.set_nls_context(v_nls_lang);
              END IF; --IF v_session_lang != v_lang

              RETURN v_return;
          EXCEPTION
          WHEN OTHERS THEN
              RETURN 'F';
          END set_context;
          
        
      BEGIN
         
          v_context := set_context ( i_user_name    => 'XXUSER'
                                    ,i_resp_name    => XX System Administrator'
                                    ,i_org_id       => 101);

         
          v_api_error_msg.error_message := error_text();
          --l_api_errors.Message_text     := po_tbl_varchar2000();
         
          IF v_context = 'F'   THEN  
         
              DBMS_OUTPUT.PUT_LINE ('Error while setting the context');

          END IF;

          MO_GLOBAL.INIT ('PO');

          --- context done ------------
         
          DBMS_OUTPUT.put_line ('Calling API To Update PO');

          for c1 in po_dtl loop

              v_api_error_msg.error_message.extend(x_num);
                                
              v_result :=    PO_CHANGE_API1_S.UPDATE_PO (x_po_number          => c1.po_number,          
                                                         x_release_number     => NULL,          
                                                         x_revision_number    => c1.revision_num,          
                                                         x_line_number        => c1.line_num,          
                                                         x_shipment_number    => c1.shipment_num,          
                                                         new_quantity         => c1.quantity,          
                                                         new_price            => c1.price,          
                                                         new_promised_date    => TRUNC(SYSDATE)+10,          
                                                         new_need_by_date     => c1.need_by_date,          
                                                         launch_approvals_flag=> 'Y',         
                                                         update_source        => NULL,         
                                                         VERSION              => '1.0',         
                                                         x_override_date      => NULL,         
                                                         x_api_errors         => l_api_errors,         
                                                         p_buyer_name         => NULL,         
                                                         p_secondary_quantity => NULL,         
                                                         p_preferred_grade    => NULL,         
                                                         p_org_id             => c1.org_id);
                                                        
      --        COMMIT;                                                  

              x_num   := x_num + 1 ;       

              DBMS_OUTPUT.put_line ('RESULT :' ||v_result);

              v_api_error_msg.error_message(x_num) := l_api_errors.Message_text(x_num);   
                               
              IF (v_result = 1) THEN
                  DBMS_OUTPUT.put_line('Updating PO is Successful ');
              ELSE
                  DBMS_OUTPUT.put_line ('Updating PO failed');
                             
                FOR i IN 1 .. v_api_error_msg.error_message.count LOOP 
                      DBMS_OUTPUT.put_line (l_api_errors.Message_text(i));            
                END LOOP;
                ROLLBACK;       
              END IF;                      
         
          END LOOP; 
         
      exception
          when others then
              DBMS_OUTPUT.PUT_LINE(sqlerrm);
              null;
      END;

       

      please see below for script 2  (from Updating PO using PO_CHANGE_API1_S.Update_PO : Oracle ERP Apps Guide)

       

       

      SET SERVEROUTPUT ON;

       

      DECLARE

       

      CURSOR po_date_update IS (SELECT pha.segment1 po_number, pha.revision_num,pha.po_header_id,

       

      pha.authorization_status, pla.po_line_id, pla.line_num, pha.org_id,

       

      pla.unit_price, pola.line_location_id, pola.shipment_num,

       

      pola.quantity, pola.promised_date, pola.need_by_date,

       

      pha.closed_code

       

      FROM po_headers_all pha, po_lines_all pla,

       

      po_line_locations_all pola

       

      WHERE pha.po_header_id = pla.po_header_id

       

      AND pla.po_line_id = pola.po_line_id

       

      AND pha.segment1 = '6456920'

       

      AND pha.type_lookup_code = 'STANDARD'

       

      AND pha.org_id = 101);

       

      l_user_id NUMBER;

       

      l_resp_id NUMBER;

       

      l_appl_id NUMBER;

       

      l_result NUMBER;

       

      l_api_errors po_api_errors_rec_type;

       

      l_revision_num NUMBER;

       

      l_promised_date DATE;

       

      l_need_by_date DATE;

       

      l_price po_lines_all.unit_price%TYPE;

       

      l_quantity po_line_locations_all.quantity%TYPE;

       

      BEGIN

       

      SELECT user_id

       

      INTO l_user_id

       

      FROM fnd_user

       

      WHERE user_name = 'XXUSER'

       

      SELECT responsibility_id, application_id

       

      INTO l_resp_id, l_appl_id

       

      FROM fnd_responsibility_vl

       

      WHERE responsibility_name = 'XX System Administrator';

       

      fnd_global.apps_initialize (l_user_id, l_resp_id, l_appl_id);

       

      FOR i IN po_date_update LOOP

       

      mo_global.set_policy_context ('S', i.org_id);

       

      mo_global.init ('PO');

       

      l_promised_date := i.promised_date + 30;

       

      l_need_by_date := i.need_by_date;

       

      l_quantity := i.quantity;

       

      l_price := i.unit_price;

       

      DBMS_OUTPUT.put_line ('Calling po_change_api1_s.update_po To Update PO');

       

      DBMS_OUTPUT.put_line ('===================================');

       

      DBMS_OUTPUT.put_line ('Retrieving the Current Revision Number of PO');

       

      select revision_num

       

      into l_revision_num

       

      from po_headers_all

       

      where segment1 = i.po_number;

       

      l_result := po_change_api1_s.update_po (x_po_number => i.po_number, --Enter the PO Number

       

      x_release_number => NULL, --Enter the Release Num

       

      x_revision_number => l_revision_num, --Enter the Revision Number

       

      x_line_number => i.line_num, --Enter the Line Number

       

      x_shipment_number => i.shipment_num, --Enter the Shipment Number

       

      new_quantity => l_quantity, --Enter the new quantity

       

      new_price => l_price, --Enter the new price,

       

      new_promised_date => l_promised_date, --Enter the new promised date,

       

      new_need_by_date => l_need_by_date, --Enter the new need by date,

       

      launch_approvals_flag => 'Y',

       

      update_source => NULL,

       

      VERSION => '1.0',

       

      x_override_date => NULL,

       

      x_api_errors => l_api_errors,

       

      p_buyer_name => NULL,

       

      p_secondary_quantity => NULL,

       

      p_preferred_grade => NULL,

       

      p_org_id => i.org_id

       

      );

       

      DBMS_OUTPUT.put_line (l_result);

       

      IF (l_result = 1)

       

      THEN

       

      DBMS_OUTPUT.put_line('Successfully update the PO :=>');

       

      END IF;

       

      IF (l_result <> 1) THEN

       

      DBMS_OUTPUT.put_line ('Failed to update the PO Due to Following Reasons');

       

      -- Display the errors

       

      FOR j IN 1 .. l_api_errors.MESSAGE_TEXT.COUNT

       

      LOOP

       

      DBMS_OUTPUT.put_line (l_api_errors.MESSAGE_TEXT (j));

       

      END LOOP;

       

      END IF;

       

      END LOOP;

       

      END;

       

      any ideas? getting frustrated on how to make this API work.

      thank you.