Oracle Apps Knowledge Sharing

API to update Buyer of a Purchase Order in Oracle Apps R12 (PO_MASS_UPDATE_PO_GRP)

        In this post, we have given a script which can update buyer of standard purchase order. This is pretty much useful when buyer of current purchase has to be changed due to various business reasons like buyer left organization or moving to another department or moving higher the ladder etc.

Tested instance: R12.2.4

API: PO_MASS_UPDATE_PO_GRP.UPDATE_PERSONS

Script:

SET SERVEROUTPUT ON;
DECLARE
  lv_msg_data          VARCHAR2(100);
  ln_msg_count         NUMBER;
  lv_return_status     VARCHAR2(1); 
  ln_org_id            NUMBER;
  ln_vendor_id         NUMBER;
  ln_changed_buyer_id  NUMBER;
  ln_current_buyer_id  NUMBER;
  ln_new_buyer_id      NUMBER DEFAULT '115';
  ln_valid_buyer       NUMBER;
  lv_document_no       VARCHAR2(200) DEFAULT '1001001238'; 
  le_custom_exception  EXCEPTION;
BEGIN 

  -- check whether the given person is a valid buyer
  SELECT COUNT(1)
    INTO ln_valid_buyer
    FROM po_buyers_val_v
   WHERE employee_id = ln_new_buyer_id;
   
  IF ln_valid_buyer = 0
  THEN
    DBMS_OUTPUT.PUT_LINE('Error: The given "to person id" is not a valid buyer');
    RAISE le_custom_exception;
  END IF;

  SELECT org_id,
         vendor_id,
         agent_id        
    INTO ln_org_id,
         ln_vendor_id,
         ln_current_buyer_id
    FROM po_headers_all pha 
   WHERE pha.segment1 = lv_document_no;
  
  -- check whether the given person is already a buyer of the document
  IF ln_current_buyer_id = ln_new_buyer_id
  THEN
    DBMS_OUTPUT.PUT_LINE('Error: The "to person id" is already a buyer of document');
    RAISE le_custom_exception;
  END IF; 
  
  fnd_global.apps_initialize(user_id      => 118,
                             resp_id      => 5089, -- PURCHASING_BUYER
                             resp_appl_id => 201
                            );
                            
  mo_global.init('PO'); 
  fnd_client_info.set_org_context(ln_org_id);
  mo_global.set_policy_context ('S', ln_org_id);
  
  PO_MASS_UPDATE_PO_GRP.UPDATE_PERSONS
           (
            p_update_person       => 'BUYER',
            p_old_personid        => ln_current_buyer_id,
            p_new_personid        => ln_new_buyer_id,
            p_document_type       => 'STANDARD',
            p_document_no_from    => lv_document_no,
            p_document_no_to      => lv_document_no,
            p_date_from           => NULL,
            p_date_to             => NULL,
            p_supplier_id         => NULL,
            p_include_close_po    => 'YES',
            p_commit_interval     => 1,
            p_msg_data            => lv_msg_data,
            p_msg_count           => ln_msg_count,
            p_return_status       => lv_return_status
          );
          
   SELECT agent_id 
     INTO ln_changed_buyer_id
     FROM po_headers_all pha 
    WHERE pha.segment1 = lv_document_no;
  
  IF ln_new_buyer_id <> ln_changed_buyer_id
  THEN 
    DBMS_OUTPUT.PUT_LINE('msg_data'|| lv_msg_data);
    DBMS_OUTPUT.PUT_LINE('msg_count'|| ln_msg_count);
    DBMS_OUTPUT.PUT_LINE('return_status'|| lv_return_status);                            
  ELSE 
    DBMS_OUTPUT.PUT_LINE('Successfully Updated');
  END IF;
EXCEPTION 
  WHEN le_custom_exception THEN 
    DBMS_OUTPUT.PUT_LINE('Custom Error. Hence, action Aborted');
  WHEN OTHERS THEN 
    DBMS_OUTPUT.PUT_LINE('Error: '||SQLERRM);
END;