Oracle EBS - Script to Update the Purchase Order Buyer using Oracle Pubic API

Requirement:

Need 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 

PLSQL:

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 '6148';

  ln_valid_buyer       NUMBER;

  lv_document_no       VARCHAR2(200) DEFAULT '600338'; 

  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 => 26406, 

                              resp_id => 50769, 

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

  DBMS_OUTPUT.PUT_LINE('--API Call--');

  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;