PO SHIPMENT LINE UPDATE USING API
Objective:
The purpose of this document is to provide a comprehensive guide on how to update or split Purchase Order (PO) shipment lines using the relevant API. This includes a detailed explanation of the API endpoints, required and optional parameters, data structures, and sample requests and responses. The goal is to enable developers and system integrators to efficiently and accurately manage PO shipment lines within their applications line using API.
Scenario:
- To achieve the goal of splitting the existing PO shipment lines into multiple lines via an API in Oracle, here’s a step-by-step outline of the process. This will include the general approach and considerations needed for splitting the shipment lines as described
- Currently below is the PO shipment line available in oracle, we would like to split the first shipment as 2 lines and second shipment line as 3 lines.
| PO Number | PO Line Number | Item Number | Shipment Line number | Org | Quantity | Promised Date | Need by Date |
| 100001 | 1 | ABC1 | 1 | USA | 50 | 01-JUL-2022 | |
| 100001 | 2 | DCG2 | 2 | USA | 50 | 01-JUL-2022 |
Sample Data File to be prepare and load in to staging table
| PO_NUMBER | PO_LINE_NUMBER | PO_SHIPMENT_NUMBER | QUANTITY | NEED_BY_DATE |
| 100001 | 1 | 1 | 25 | 1-Jul-22 |
| 100001 | 1 | 2 | 25 | 30-Jul-22 |
| 100001 | 2 | 1 | 20 | 1-Jul-22 |
| 100001 | 2 | 2 | 20 | 15-Jul-22 |
| 100001 | 2 | 3 | 10 | 30-Jul-22 |
Steps:
ü Compile the data base objects.
ü Load the prepared data into staging table.
ü Run the Main procedure.
Objects:
CREATE TABLE XX_PO_SHIPMENT_INT_STG (
PO_NUMBER VARCHAR2(200),
PO_LINE_NUMBER NUMBER,
PO_SHIPMENT_NUMBER NUMBER,
QUANTITY NUMBER,
NEED_BY_DATE DATE ,
PROCESS_FLAG VARCHAR2(50),
LAST_UPDATE_DATE DATE,
LAST_UPDATED_BY NUMBER,
CREATION_DATE DATE,
CREATED_BY NUMBER,
UPLOAD_ID NUMBER,
ERR_MSG VARCHAR2(4000))
PROCEDURE main (
errbuf OUT VARCHAR2,
retcode OUT NUMBER,
p_email_address VARCHAR2
)
IS
CURSOR cur_hdr
IS
SELECT DISTINCT po_number, upload_id
FROM bolinf.xx_po_shipment_int_stg
WHERE process_flag = ‘N’;
CURSOR cur_lne (p_po_number VARCHAR2)
IS
SELECT DISTINCT po_number, po_line_number, upload_id
FROM bolinf.xx_po_shipment_int_stg
WHERE process_flag = ‘N’ AND po_number = p_po_number;
CURSOR cur (p_po_num VARCHAR2, p_po_line_number NUMBER)
IS
SELECT xps.*, xps.ROWID
FROM bolinf.xx_po_shipment_int_stg xps
WHERE xps.process_flag = ‘N’
AND xps.po_number = p_po_num
AND xps.po_line_number = p_po_line_number
ORDER BY po_line_number, po_shipment_number;
CURSOR crpt (p_upload_id NUMBER)
IS
SELECT *
FROM bolinf.xx_po_shipment_int_stg
WHERE upload_id = p_upload_id;
l_ship_to_location_id NUMBER;
l_ship_to_organization_id NUMBER;
l_po_header_id NUMBER;
l_po_line_id NUMBER;
l_line_location_id NUMBER;
l_err_flag VARCHAR2 (1);
l_err_msg VARCHAR2 (4000);
l_new_line VARCHAR2 (1);
l_user_id NUMBER
:= NVL (fnd_profile.VALUE (‘USER_ID’),
-1);
l_revision_number NUMBER (4, 2);
l_line_qty NUMBER;
l_ship_qty NUMBER;
l_po_line_loc po_line_locations_all%ROWTYPE;
l_po_line_loc1 po_line_locations_all%ROWTYPE;
l_result NUMBER;
l_errors apps.po_api_errors_rec_type;
l_upload_id NUMBER;
l_cancel_flag VARCHAR2 (10);
l_closed_date DATE;
l_lne_cancel_flag VARCHAR2 (10);
l_lne_closed_date DATE;
l_cnt NUMBER := 0;
l_rct_cnt NUMBER;
l_drp_ship NUMBER;
l_chg apps.po_changes_rec_type;
l_chg1 apps.po_changes_rec_type;
l_return_status VARCHAR2 (30);
l_agent_id NUMBER;
l_parent_loc_location_id NUMBER;
v_item_key VARCHAR2 (100);
l_shipment_qty NUMBER := NULL;
l_vendor_id NUMBER;
l_vnd_exists VARCHAR2 (10);
l_org_id NUMBER;
BEGIN
SELECT fnd_profile.VALUE (‘CONC_REQUEST_ID’)
INTO l_upload_id
FROM DUAL;
fnd_file.put_line (fnd_file.LOG, ‘ Upload ID ‘ || l_upload_id);
UPDATE bolinf.xx_po_shipment_int_stg
SET upload_id = l_upload_id –xx_po_ship_seq.nextval
WHERE process_flag = ‘N’;
COMMIT;
FOR rec_hdr IN cur_hdr
LOOP
l_po_header_id := NULL;
l_revision_number := NULL;
l_err_flag := ‘N’;
l_drp_ship := 0;
l_chg := l_chg1;
l_agent_id := 0;
l_err_msg := NULL;
l_vendor_id := NULL;
l_vnd_exists := ‘N’;
l_org_id := NULL;
BEGIN
SELECT po_header_id, revision_num, NVL (cancel_flag, ‘N’),
closed_date, agent_id, vendor_id, org_id
INTO l_po_header_id, l_revision_number, l_cancel_flag,
l_closed_date, l_agent_id, l_vendor_id, l_org_id
FROM po_headers_all
WHERE segment1 = rec_hdr.po_number
AND authorization_status = ‘APPROVED’
AND type_lookup_code = ‘STANDARD’;
EXCEPTION
WHEN OTHERS
THEN
l_err_flag := ‘E’;
l_err_msg := ‘Invalid Purchase Order Number’;
END;
IF l_cancel_flag = ‘Y’ OR l_closed_date IS NOT NULL
THEN
l_err_flag := ‘E’;
l_err_msg := ‘Purchase Order is cancelled or closed’;
END IF;
IF l_err_flag != ‘E’
THEN
l_chg :=
apps.po_changes_rec_type.create_object
(p_po_header_id => l_po_header_id,
p_po_release_id => NULL
);
FOR rec_po IN cur_lne (rec_hdr.po_number)
LOOP
l_ship_to_location_id := NULL;
l_ship_to_organization_id := NULL;
l_po_line_id := NULL;
l_line_location_id := NULL;
l_err_flag := NULL;
l_err_msg := NULL;
l_new_line := ‘N’;
l_line_qty := NULL;
l_ship_qty := NULL;
l_po_line_loc := l_po_line_loc1;
l_err_flag := ‘N’;
l_lne_cancel_flag := NULL;
l_lne_closed_date := NULL;
l_cnt := 0;
l_rct_cnt := 0;
l_shipment_qty := NULL;
SELECT COUNT (1)
INTO l_cnt
FROM bolinf.xx_po_shipment_int_stg
WHERE po_number = rec_po.po_number
AND po_line_number = rec_po.po_line_number
AND upload_id = rec_po.upload_id
AND process_flag = ‘E’;
IF l_cnt > 0
THEN
l_err_flag := ‘E’;
l_err_msg := l_err_msg || ‘, One of the PO Line is in error’;
END IF;
BEGIN
SELECT po_line_id, quantity, cancel_flag,
closed_date
INTO l_po_line_id, l_line_qty, l_lne_cancel_flag,
l_lne_closed_date
FROM po_lines_all
WHERE po_header_id = l_po_header_id
AND line_num = rec_po.po_line_number;
EXCEPTION
WHEN OTHERS
THEN
l_err_flag := ‘E’;
l_err_msg :=
l_err_msg || ‘, Invalid Purchase order line Number’;
END;
IF l_lne_cancel_flag = ‘Y’ OR l_lne_closed_date IS NOT NULL
THEN
l_err_flag := ‘E’;
l_err_msg :=
l_err_msg
|| ‘, Purchase Order line has cancelled or closed’;
END IF;
l_lne_cancel_flag := ‘N’;
l_lne_closed_date := NULL;
SELECT COUNT (1)
INTO l_rct_cnt
FROM po_line_locations_all
WHERE po_header_id = l_po_header_id
AND po_line_id = l_po_line_id
AND (quantity_received > 0 OR quantity_billed > 0);
IF l_rct_cnt > 0
THEN
l_err_flag := ‘E’;
l_err_msg :=
l_err_msg
|| ‘, Purchase Order line quantity has received or billed’;
END IF;
SELECT SUM (quantity)
INTO l_ship_qty
FROM bolinf.xx_po_shipment_int_stg
WHERE po_number = rec_po.po_number
AND po_line_number = rec_po.po_line_number
AND upload_id = rec_po.upload_id;
l_parent_loc_location_id := NULL;
l_line_location_id := NULL;
IF l_err_flag != ‘E’
THEN
FOR rec_cur IN cur (rec_po.po_number, rec_po.po_line_number)
LOOP
l_err_flag := ‘N’;
l_err_msg := NULL;
IF l_line_location_id IS NOT NULL
THEN
l_parent_loc_location_id := l_line_location_id;
END IF;
BEGIN
SELECT line_location_id, ship_to_location_id,
quantity, ship_to_organization_id,
cancel_flag, closed_date
INTO l_line_location_id, l_ship_to_location_id,
l_shipment_qty, l_ship_to_organization_id,
l_lne_cancel_flag, l_lne_closed_date
FROM po_line_locations_all
WHERE po_header_id = l_po_header_id
AND po_line_id = l_po_line_id
AND shipment_num = rec_cur.po_shipment_number;
EXCEPTION
WHEN OTHERS
THEN
l_new_line := ‘Y’;
END;
IF l_lne_cancel_flag = ‘Y’ OR l_lne_closed_date IS NOT NULL
THEN
l_err_flag := ‘E’;
l_err_msg :=
l_err_msg
|| ‘, Purchase Order shipment line is cancelled or closed’;
END IF;
IF l_shipment_qty <> l_ship_qty
THEN
l_err_flag := ‘E’;
l_err_msg :=
l_err_msg
|| ‘, PO Line Quantity and new Shipment lines Quantity is not matching’;
END IF;
DBMS_OUTPUT.put_line ( ‘l_new_line ‘
|| l_new_line
|| ‘l_err_flag ‘
|| l_err_flag
);
IF l_err_flag != ‘E’
THEN
IF l_new_line != ‘Y’
THEN
l_chg.shipment_changes.add_change
(p_po_line_location_id => l_line_location_id,
p_quantity => rec_cur.quantity,
p_need_by_date => rec_cur.need_by_date
);
ELSE
l_chg.shipment_changes.add_change
(p_po_line_location_id => NULL,
p_quantity => rec_cur.quantity,
p_split_shipment_num => rec_cur.po_shipment_number,
p_need_by_date => rec_cur.need_by_date,
p_parent_line_location_id => l_parent_loc_location_id
);
END IF;
— commit;
ELSE
ROLLBACK;
UPDATE bolinf.xx_po_shipment_int_stg
SET process_flag = ‘E’,
err_msg = l_err_msg
WHERE ROWID = rec_cur.ROWID;
COMMIT;
fnd_file.put_line (fnd_file.LOG,
‘After error update: ‘ || l_err_msg
);
END IF;
END LOOP;
END IF;
END LOOP;
IF l_err_flag != ‘E’
THEN
apps.po_document_update_grp.update_document
(p_api_version => 1.0,
— pass this as 1.0
p_init_msg_list => fnd_api.g_true,
— pass this as TRUE
x_return_status => l_return_status,
— returns the result of execution
p_changes => l_chg,
— changes obj. contains all changes intended to be made on document
p_run_submission_checks => fnd_api.g_false,
— set to TRUE if want to perform submission check
p_launch_approvals_flag => fnd_api.g_false,
— set to TRUE if want to launch approval work flow after making the changes
p_buyer_id => l_agent_id,
— buyer id
p_update_source => NULL,
p_override_date => NULL,
x_api_errors => l_errors,
p_mass_update_releases => NULL
);
IF l_errors IS NOT NULL
THEN
ROLLBACK;
FOR i IN 1 .. l_errors.MESSAGE_TEXT.COUNT
LOOP
l_err_flag := ‘E’;
l_err_msg := l_err_msg || ‘, ‘ || l_errors.MESSAGE_TEXT (i);
DBMS_OUTPUT.put_line ( ‘ Error is ‘
|| l_errors.MESSAGE_TEXT (i)
|| ‘ – name’
|| l_errors.message_name (i)
);
END LOOP;
END IF;
END IF;
IF l_err_flag = ‘E’
THEN
ROLLBACK;
UPDATE bolinf.xx_po_shipment_int_stg
SET process_flag = ‘E’,
err_msg = l_err_msg
WHERE po_number = rec_hdr.po_number
AND upload_id = rec_hdr.upload_id;
COMMIT;
ELSE
fnd_file.put_line (fnd_file.LOG,
‘Before error update: ‘ || l_err_msg
);
UPDATE bolinf.xx_po_shipment_int_stg
SET process_flag = ‘P’,
err_msg = ‘Successfully Processed’
WHERE po_number = rec_hdr.po_number
AND upload_id = rec_hdr.upload_id;
COMMIT;
END IF;
ELSE
ROLLBACK;
UPDATE bolinf.xx_po_shipment_int_stg
SET process_flag = ‘E’,
err_msg = l_err_msg
WHERE po_number = rec_hdr.po_number
AND upload_id = rec_hdr.upload_id;
COMMIT;
END IF;
SELECT l_po_header_id || ‘-‘ || TO_CHAR (po_wf_itemkey_s.NEXTVAL)
INTO v_item_key
FROM DUAL;
po_reqapproval_init1.start_wf_process
(itemtype => ‘POAPPRV’,
itemkey => v_item_key,
workflowprocess => ‘POAPPRV_TOP’,
actionoriginatedfrom => ‘PO_FORM’,
documentid => l_po_header_id
— po_header_id
,
documentnumber => rec_hdr.po_number
— Purchase Order Number
,
preparerid => l_agent_id
— Buyer/Preparer_id
,
documenttypecode => ‘PO’,
documentsubtype => ‘STANDARD’,
submitteraction => ‘APPROVE’,
forwardtoid => NULL,
forwardfromid => NULL,
defaultapprovalpathid => NULL,
note => NULL,
printflag => ‘N’,
faxflag => ‘N’,
faxnumber => NULL,
emailflag => ‘N’,
emailaddress => NULL,
createsourcingrule => ‘N’,
releasegenmethod => ‘N’,
updatesourcingrule => ‘N’,
massupdatereleases => ‘N’,
retroactivepricechange => ‘N’,
orgassignchange => ‘N’,
communicatepricechange => ‘N’,
p_background_flag => ‘N’
);
COMMIT;
END LOOP;
fnd_file.put_line (fnd_file.output,
RPAD (‘po_number’, 15, ‘ ‘)
|| RPAD (‘po_line_number’, 20, ‘ ‘)
|| RPAD (‘po_shipment_number’, 20, ‘ ‘)
|| ‘Message ‘
);
FOR rpt IN crpt (l_upload_id)
LOOP
— commit;
fnd_file.put_line (fnd_file.output,
RPAD (rpt.po_number, 15, ‘ ‘)
|| RPAD (rpt.po_line_number, 20, ‘ ‘)
|| RPAD (rpt.po_shipment_number, 20, ‘ ‘)
|| rpt.err_msg
);
END LOOP;
fnd_file.put_line (fnd_file.LOG, ‘ l_err_msg :’ || l_err_msg);
DBMS_OUTPUT.put_line (‘ l_err_msg :’ || l_err_msg);
IF p_email_address IS NOT NULL
THEN
/* Calling procedure to send a report output as an attachment to user */
xx_po_shipment_excl_rpt (l_upload_id, p_email_address);
–‘srujan.k@ealliancecorp.com’
END IF;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG, ‘Unexpected Error ‘ || SQLERRM);
DBMS_OUTPUT.put_line (‘Unexpected Error ‘ || SQLERRM);
END;
