Updating PO using PO_CHANGE_API1_S.Update_PO

Using the PO_CHANGE_API1_S.update_po, the following columns can be updated in a Purchase Order.

– Promised_date
– Need_by_date
– Quantity
– Unit_price

Importance of LAUNCH_APPROVALS_FLAG
=================================

– Indicates if you want to launch APPROVAL workflow after the update. Its value could be either ‘Y’ or ‘N’. If not provided, the default value is ‘N’.
– If we are passing launch_approvals_flag =>’N’, the PO is remaining in the Requires Reapproval status.

Post Update PO Validation:
====================
1. Set the PO status to REQUIRES REAPPROVAL.
2. Increment revision number if the PO was in APPROVED status before the update.
3. Launch the PO Approval workflow if LAUNCH_APPROVALS_FLAG = ‘Y’.
4. If the quantity was adjusted down to be equal to the total quantity received or billed, then set the appropriate closed code and roll up the closed code to line and header levels.

— R12 – PO – Sample Script to Update PO Using po_change_api1_s
–=================================================

DECLARE

CURSOR po_date_update
IS

SELECT pha.segment1 po_number, pha.revision_num,pha.po_header_id,
pha.authorization_status, pla.po_line_id, pla.line_num, pha.org_id,
pla.unit_price, pola.line_location_id, pola.shipment_num,
pola.quantity, pola.promised_date, pola.need_by_date,
pha.closed_code
FROM po_headers_all pha, po_lines_all pla,
po_line_locations_all pola
WHERE pha.po_header_id = pla.po_header_id
AND pla.po_line_id = pola.po_line_id
AND NVL (pha.approved_flag, ‘N’) = ‘Y’
AND NVL (pola.cancel_flag, ‘N’) <> ‘Y’
AND NVL (pola.closed_code, ‘OPEN’) = ‘OPEN’
AND NVL (pola.quantity_received, 0) = 0
AND NVL (pola.quantity_billed, 0) = 0
AND pha.segment1 = ‘11170007326’
AND pla.line_num = 1
AND pha.type_lookup_code = ‘STANDARD’
AND pha.org_id = 308;

l_user_id NUMBER;
l_resp_id NUMBER;
l_appl_id NUMBER;
l_result NUMBER;
l_api_errors po_api_errors_rec_type;
l_revision_num NUMBER;
l_promised_date DATE;
l_need_by_date DATE;
l_price po_lines_all.unit_price%TYPE;
l_quantity po_line_locations_all.quantity%TYPE;

BEGIN

SELECT user_id
INTO l_user_id
FROM fnd_user
WHERE user_name = ‘CONVERSION’;

SELECT responsibility_id, application_id
INTO l_resp_id, l_appl_id
FROM fnd_responsibility_vl
WHERE responsibility_name = ‘Purchasing Super User’;

fnd_global.apps_initialize (l_user_id, l_resp_id, l_appl_id);

FOR i IN po_date_update

LOOP

mo_global.set_policy_context (‘S’, i.org_id);
mo_global.init (‘PO’);

l_promised_date := i.promised_date + 30;
l_need_by_date := i.need_by_date + 35;
l_quantity := i.quantity + 100;
l_price := i.unit_price – 1;

DBMS_OUTPUT.put_line (‘Calling po_change_api1_s.update_po To Update PO’);
DBMS_OUTPUT.put_line (‘===================================’);
DBMS_OUTPUT.put_line (‘Retrieving the Current Revision Number of PO’);

select revision_num
into l_revision_num
from po_headers_all
where segment1 = i.po_number;

l_result :=
po_change_api1_s.update_po
(x_po_number => i.po_number, –Enter the PO Number
x_release_number => NULL, –Enter the Release Num
x_revision_number => l_revision_num, –Enter the Revision Number
x_line_number => i.line_num, –Enter the Line Number
x_shipment_number => i.shipment_num, –Enter the Shipment Number
new_quantity => l_quantity, –Enter the new quantity
new_price => l_price, –Enter the new price,
new_promised_date => l_promised_date, –Enter the new promised date,
new_need_by_date => l_need_by_date, –Enter the new need by date,
launch_approvals_flag => ‘Y’,
update_source => NULL,
VERSION => ‘1.0’,
x_override_date => NULL,
x_api_errors => l_api_errors,
p_buyer_name => NULL,
p_secondary_quantity => NULL,
p_preferred_grade => NULL,
p_org_id => i.org_id
);

DBMS_OUTPUT.put_line (l_result);

IF (l_result = 1)
THEN
DBMS_OUTPUT.put_line(‘Successfully update the PO :=>’);
END IF;

IF (l_result <> 1)
THEN
DBMS_OUTPUT.put_line (‘Failed to update the PO Due to Following Reasons’);
— Display the errors
FOR j IN 1 .. l_api_errors.MESSAGE_TEXT.COUNT
LOOP
DBMS_OUTPUT.put_line (l_api_errors.MESSAGE_TEXT (j));
END LOOP;
END IF;

END LOOP;

END;