Sunday, September 6, 2020

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



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;

0 Responses to “API to update Buyer of a Purchase Order in Oracle Apps R12 (PO_MASS_UPDATE_PO_GRP)”

Post a Comment


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.