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;