UPDATE PO SHIPMENT LINE USING API
Objective:
The intent of this document is to understand how we can update or split the PO Shipment line using API
Scenario:
- 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;
