Oracle Apps Knowledge Sharing

API to change Requester or Approver of a Purchase Requisition in Oracle Applications R12 (PO_MASS_UPDATE_REQ_GRP)

            In this post, we have given a script which can update requester/preparer or approver of standard purchase requisition.

Business Case: This is pretty much useful when a preparer of current purchase requisition must be changed due to various business reasons like preparer left organization or moving to another department or moving higher the ladder etc.

Tested instance: R12.2.4

API: PO_MASS_UPDATE_REQ_GRP.UPDATE_PERSONS

Parameters:

Script:

SET SERVEROUTPUT ON;
DECLARE
  lv_msg_data             VARCHAR2(100);
  ln_msg_count            NUMBER;
  lv_return_status        VARCHAR2(1); 
  ln_org_id               NUMBER;
  ln_changed_preparer_id  NUMBER;
  ln_current_preparer_id  NUMBER;
  ln_new_preparer_id      NUMBER DEFAULT '88';
  ln_valid_employee       NUMBER;
  lv_document_no          VARCHAR2(200) DEFAULT '100008225'; 
  le_custom_exception     EXCEPTION;
BEGIN 

  -- check whether the given person is a valid preparer
  SELECT COUNT(1)
    INTO ln_valid_employee
    FROM per_all_people_f 
   WHERE person_id = ln_new_preparer_id
     AND current_employee_flag = 'Y'
	 AND SYSDATE BETWEEN effective_from_date AND effective_to_date;
   
  IF ln_valid_employee = 0
  THEN
    DBMS_OUTPUT.PUT_LINE('Error: The given "to person id" is not a valid employee');
    RAISE le_custom_exception;
  END IF;

  SELECT org_id,
         preparer_id        
    INTO ln_org_id,
         ln_current_preparer_id
    FROM po_requisition_headers_all pha 
   WHERE pha.segment1 = lv_document_no;
   
  IF ln_current_preparer_id = ln_new_preparer_id
  THEN
    DBMS_OUTPUT.PUT_LINE('Error: The given "to person id" is already a preparer');
    RAISE le_custom_exception;
  END IF; 
  
  fnd_global.apps_initialize(user_id      => 178,
                             resp_id      => 5739, 
                             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_REQ_GRP.UPDATE_PERSONS
           (
            p_update_person       => 'PREPARER',
            p_old_personid        => ln_current_preparer_id,
            p_new_personid        => ln_new_preparer_id,
            p_document_type       => 'PURCHASE',
            p_document_no_from    => lv_document_no,
            p_document_no_to      => lv_document_no,
            p_date_from           => NULL,
            p_date_to             => NULL,
            p_commit_interval     => 1,
            p_msg_data            => lv_msg_data,
            p_msg_count           => ln_msg_count,
            p_return_status       => lv_return_status
          );
          
   SELECT preparer_id 
     INTO ln_current_preparer_id
     FROM po_requisition_headers_all pha 
    WHERE pha.segment1 = lv_document_no;
  
  IF ln_new_preparer_id <> ln_current_preparer_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;