Saturday, September 12, 2020

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:

Type

Parameter

Description

IN

p_update_person

PREPARER OR APPROVER OR REQUESTOR

IN

p_old_personid

old person id from per_all_people_f

IN

p_new_personid

new person id from per_all_people_f

IN

p_document_type

INTERNAL or PURCHASE

IN

p_document_no_from

Requisition number from

IN

p_document_no_to

Requisition number To

IN

p_date_from

 

IN

p_date_to

 

IN

p_commit_interval

 

OUT

p_msg_data

Actual message in encoded format

OUT

p_msg_count

number of messages in the API list

OUT

p_return_status

Return status  - S or E or U

  

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;

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

Post a Comment

Disclaimer

The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.