Creating PO lines in Oracle E-Business Suite R12 is not done by a single public PL/SQL API call. The correct, supported method is to use the Purchasing Documents Open Interface (PDOI). With PDOI, you populate interface tables for the PO header, PO lines, line shipments, and distributions, and then run the seeded concurrent program Import Standard Purchase Orders (POXPOPDOI). This approach is stable, supportable, and upgrade-safe.
This tutorial rewrites the earlier guidance and shows—step by step—how to Create Purchase Order Lines with API in Oracle Apps R12 using PDOI, including a complete PL/SQL example and practical troubleshooting tips.
What You Will Use
- PO_HEADERS_INTERFACE – Header (supplier, buyer, currency, OU, approval status).
- PO_LINES_INTERFACE – Core line data (item, quantity, UOM, unit price, category, line type).
- PO_LINE_LOCATIONS_INTERFACE – Line shipments/schedules (ship-to location, need-by date, quantity).
- PO_DISTRIBUTIONS_INTERFACE – Accounting distributions (charge account, deliver-to, quantity/percent).
- PO_INTERFACE_ERRORS – Detailed error messages if import fails.
Use a common INTERFACE_HEADER_ID (and typically BATCH_ID) to tie header, lines, shipments, and distributions together.
When to Insert the Header
Even though this article focuses on lines, PDOI expects a header record to exist in PO_HEADERS_INTERFACE for the same INTERFACE_HEADER_ID. If you are adding lines to a new PO, insert the header together with the lines. If you are adding lines to a header you have already staged (and not yet imported), reuse the same INTERFACE_HEADER_ID and BATCH_ID.
Required Linking Keys (Very Important)
PO_LINES_INTERFACE.INTERFACE_HEADER_ID→ links each line to its header.
PO_LINE_LOCATIONS_INTERFACE.INTERFACE_LINE_ID→ links each shipment to its line.
PO_DISTRIBUTIONS_INTERFACE.INTERFACE_LINE_LOCATION_ID→ links each distribution to its shipment.
These relationships must be correct or the import will reject your data.
Typical Data You Must Provide
In PO_LINES_INTERFACE
INTERFACE_HEADER_ID,INTERFACE_LINE_ID,LINE_NUMLINE_TYPE_ID(e.g., Goods/Services)ITEM_IDorITEM_DESCRIPTIONQUANTITY,UNIT_OF_MEASURE,UNIT_PRICE- (Optional)
CATEGORY_ID,NEED_BY_DATE(usually on shipment)
In PO_LINE_LOCATIONS_INTERFACE
INTERFACE_LINE_LOCATION_ID,INTERFACE_LINE_IDSHIP_TO_LOCATION_ID(and/orSHIP_TO_ORGANIZATION_IDper your setup)NEED_BY_DATE,QUANTITY
In PO_DISTRIBUTIONS_INTERFACE
INTERFACE_DISTRIBUTION_ID,INTERFACE_LINE_LOCATION_IDDISTRIBUTION_NUMQUANTITY_ORDERED(orDISTRIBUTION_PERCENT)CHARGE_ACCOUNT_ID(your GL CCID)- (Often used)
DELIVER_TO_LOCATION_ID, project/task attributes if Projects is used
End-to-End Process
1 Generate IDs: Get unique values for INTERFACE_HEADER_ID, INTERFACE_LINE_ID, INTERFACE_LINE_LOCATION_ID, and INTERFACE_DISTRIBUTION_ID. Commonly, you use the seeded sequences:
PO_HEADERS_INTERFACE_S.NEXTVALPO_LINES_INTERFACE_S.NEXTVALPO_LINE_LOCATIONS_INTERFACE_S.NEXTVALPO_DISTRIBUTIONS_INTERFACE_S.NEXTVAL
2 Populate Interface Tables: Insert header (if new), lines, shipments, and distributions using the IDs above. Keep BATCH_ID consistent across all rows you intend to import together.
3 Run the Concurrent Program: Submit Import Standard Purchase Orders (POXPOPDOI) via FND_REQUEST.SUBMIT_REQUEST, passing your BATCH_ID and Operating Unit ID. The process validates the data and creates the PO in base tables.
4 Error Handling and Validation
- Check
PO_INTERFACE_ERRORSfor row-level issues. - Review
PROCESS_CODEandREQUEST_IDin the interface tables. - On success, review
PO_HEADERS_ALL/PO_LINES_ALLfor the created document.
Complete PL/SQL Example (Two Lines, Multiple Shipments/Distributions)
DECLARE
-- Generate interface keys
l_hdr_id NUMBER := po_headers_interface_s.NEXTVAL;
l_batch_id NUMBER := l_hdr_id;
l_line_id_1 NUMBER := po_lines_interface_s.NEXTVAL;
l_line_id_2 NUMBER := po_lines_interface_s.NEXTVAL;
l_loc_id_11 NUMBER := po_line_locations_interface_s.NEXTVAL; -- line 1, shipment 1
l_loc_id_12 NUMBER := po_line_locations_interface_s.NEXTVAL; -- line 1, shipment 2
l_loc_id_21 NUMBER := po_line_locations_interface_s.NEXTVAL; -- line 2, shipment 1
l_dist_id_111 NUMBER := po_distributions_interface_s.NEXTVAL; -- line1/ship1/dist1
l_dist_id_112 NUMBER := po_distributions_interface_s.NEXTVAL; -- line1/ship1/dist2
l_dist_id_121 NUMBER := po_distributions_interface_s.NEXTVAL; -- line1/ship2/dist1
l_dist_id_211 NUMBER := po_distributions_interface_s.NEXTVAL; -- line2/ship1/dist1
l_request_id NUMBER;
-- Substitute these bind variables with real values or constants
p_org_id NUMBER := :p_org_id;
p_vendor_id NUMBER := :p_vendor_id;
p_vendor_site_id NUMBER := :p_vendor_site_id;
p_buyer_id NUMBER := :p_buyer_id;
p_currency VARCHAR2(15) := :p_currency;
p_item_id_g1 NUMBER := :p_item_id_g1; -- goods item for line 1
p_item_id_g2 NUMBER := :p_item_id_g2; -- goods item for line 2
p_line_type_id_goods NUMBER := :p_line_type_id_goods;
p_ship_to_loc_id NUMBER := :p_ship_to_location_id;
p_ship_to_org_id NUMBER := :p_ship_to_org_id;
p_ccid_ops NUMBER := :p_charge_account_ops; -- GL account CCID
p_ccid_it NUMBER := :p_charge_account_it; -- another CCID
p_deliver_to_loc_id NUMBER := :p_deliver_to_location_id;
BEGIN
------------------------------------------------------------
-- 1) HEADER (required for this INTERFACE_HEADER_ID)
------------------------------------------------------------
INSERT INTO po_headers_interface (
interface_header_id, batch_id, org_id, action,
document_type_code, vendor_id, vendor_site_id,
currency_code, agent_id, approval_status, comments
) VALUES (
l_hdr_id, l_batch_id, p_org_id, 'INSERT',
'STANDARD', p_vendor_id, p_vendor_site_id,
p_currency, p_buyer_id, 'INCOMPLETE', 'Created via PDOI (PO lines demo)'
);
------------------------------------------------------------
-- 2) LINES
------------------------------------------------------------
-- Line 1
INSERT INTO po_lines_interface (
interface_line_id, interface_header_id, line_num,
line_type_id, item_id, quantity, unit_of_measure, unit_price, category_id
) VALUES (
l_line_id_1, l_hdr_id, 1,
p_line_type_id_goods, p_item_id_g1, 100, 'Each', 50, NULL
);
-- Line 2 (free-text or different item)
INSERT INTO po_lines_interface (
interface_line_id, interface_header_id, line_num,
line_type_id, item_id, item_description, quantity, unit_of_measure, unit_price
) VALUES (
l_line_id_2, l_hdr_id, 2,
p_line_type_id_goods, p_item_id_g2, 'Spare parts kit', 25, 'Each', 120
);
------------------------------------------------------------
-- 3) SHIPMENTS (line locations)
------------------------------------------------------------
-- Line 1, Shipment 1
INSERT INTO po_line_locations_interface (
interface_line_location_id, interface_line_id,
ship_to_location_id, ship_to_organization_id,
quantity, need_by_date
) VALUES (
l_loc_id_11, l_line_id_1,
p_ship_to_loc_id, p_ship_to_org_id,
60, SYSDATE + 10
);
-- Line 1, Shipment 2
INSERT INTO po_line_locations_interface (
interface_line_location_id, interface_line_id,
ship_to_location_id, ship_to_organization_id,
quantity, need_by_date
) VALUES (
l_loc_id_12, l_line_id_1,
p_ship_to_loc_id, p_ship_to_org_id,
40, SYSDATE + 25
);
-- Line 2, Shipment 1
INSERT INTO po_line_locations_interface (
interface_line_location_id, interface_line_id,
ship_to_location_id, ship_to_organization_id,
quantity, need_by_date
) VALUES (
l_loc_id_21, l_line_id_2,
p_ship_to_loc_id, p_ship_to_org_id,
25, SYSDATE + 15
);
------------------------------------------------------------
-- 4) DISTRIBUTIONS (accounting)
------------------------------------------------------------
-- Line 1, Shipment 1 split 70/30 across two accounts
INSERT INTO po_distributions_interface (
interface_distribution_id, interface_line_location_id,
distribution_num, quantity_ordered, charge_account_id, deliver_to_location_id
) VALUES (
l_dist_id_111, l_loc_id_11, 1, 42, p_ccid_ops, p_deliver_to_loc_id -- 70% of 60
);
INSERT INTO po_distributions_interface (
interface_distribution_id, interface_line_location_id,
distribution_num, quantity_ordered, charge_account_id, deliver_to_location_id
) VALUES (
l_dist_id_112, l_loc_id_11, 2, 18, p_ccid_it, p_deliver_to_loc_id -- 30% of 60
);
-- Line 1, Shipment 2 (single distribution)
INSERT INTO po_distributions_interface (
interface_distribution_id, interface_line_location_id,
distribution_num, quantity_ordered, charge_account_id, deliver_to_location_id
) VALUES (
l_dist_id_121, l_loc_id_12, 1, 40, p_ccid_ops, p_deliver_to_loc_id
);
-- Line 2, Shipment 1 (single distribution)
INSERT INTO po_distributions_interface (
interface_distribution_id, interface_line_location_id,
distribution_num, quantity_ordered, charge_account_id, deliver_to_location_id
) VALUES (
l_dist_id_211, l_loc_id_21, 1, 25, p_ccid_ops, p_deliver_to_loc_id
);
COMMIT;
------------------------------------------------------------
-- 5) RUN THE SEEDED IMPORT PROGRAM (POXPOPDOI)
------------------------------------------------------------
l_request_id := fnd_request.submit_request(
application => 'PO',
program => 'POXPOPDOI', -- Import Standard Purchase Orders
description => NULL,
start_time => NULL,
sub_request => FALSE,
-- Common parameter pattern; confirm in your instance:
-- Buyer, Doc Type, Subtype, Process Items?, Create Sourcing Rule?,
-- Approval Status, Release Gen Method, Reserved, OU ID, Batch ID
argument1 => NULL,
argument2 => 'STANDARD',
argument3 => NULL,
argument4 => 'N',
argument5 => 'N',
argument6 => 'INCOMPLETE',
argument7 => NULL,
argument8 => NULL,
argument9 => p_org_id,
argument10 => l_batch_id
);
COMMIT;
END;
/
Notes:
- Quantities across distributions must equal the shipment quantity (or use percentages instead of quantities if preferred).
- If you want the PO to route to approval automatically, set the header
APPROVAL_STATUSand program parameter accordingly (e.g.,INITIATE APPROVAL), consistent with your workflow setup.
Troubleshooting and Error Handling
- Check the request log of
POXPOPDOIto see high-level success/failure. - Query
PO_INTERFACE_ERRORSfor specific row errors (byINTERFACE_HEADER_ID,INTERFACE_LINE_ID, etc.). - Verify
PROCESS_CODEin the interface tables—successful rows typically change toACCEPTED.
Common pitfalls:
- Wrong or inactive line type, UOM, item, or category.
- Missing ship-to location/organization or invalid need-by date.
- Invalid charge account (CCID not enabled or not costable).
- Mismatched quantities between line, shipments, and distributions.
- Using different
BATCH_IDs for related rows.
Best Practices
- Keep batches atomic: group related header/line/shipment/distribution rows under a single
BATCH_ID. - Use staging/wrapper code: validate data before inserting into interface tables.
- Log everything: capture generated IDs and
REQUEST_IDfor audit and troubleshooting. - Start simple: create one line and one shipment first; add splits and additional lines later.
- Align setups: confirm buyer, OU, item orgs, locations, and GL accounts are valid and active.
Conclusion
The supported way to Create Purchase Order Lines with API in Oracle Apps R12 is to use PDOI: populate PO_LINES_INTERFACE, PO_LINE_LOCATIONS_INTERFACE, and PO_DISTRIBUTIONS_INTERFACE, tie them to your INTERFACE_HEADER_ID, and run POXPOPDOI. This method provides robust validation, clear error reporting, and long-term supportability—everything you need for reliable automation of PO line creation.
The post How to Create Purchase Order Lines with API in Oracle Apps R12 appeared first on Vinish.Dev.