How to Create Purchase Order Lines in Oracle R12 (with PL/SQL Examples)

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_NUM
LINE_TYPE_ID (e.g., Goods/Services)
ITEM_ID or ITEM_DESCRIPTION
QUANTITY, 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_ID
SHIP_TO_LOCATION_ID (and/or SHIP_TO_ORGANIZATION_ID per your setup)
NEED_BY_DATE, QUANTITY

In `PO_DISTRIBUTIONS_INTERFACE`

INTERFACE_DISTRIBUTION_ID, INTERFACE_LINE_LOCATION_ID
DISTRIBUTION_NUM
QUANTITY_ORDERED (or DISTRIBUTION_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.NEXTVAL
PO_LINES_INTERFACE_S.NEXTVAL
PO_LINE_LOCATIONS_INTERFACE_S.NEXTVAL
PO_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_ERRORS for row-level issues.
Review PROCESS_CODE and REQUEST_ID in the interface tables.
On success, review PO_HEADERS_ALL / PO_LINES_ALL for 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_STATUS and program parameter accordingly (e.g., INITIATE APPROVAL), consistent with your workflow setup.

Troubleshooting and Error Handling

Check the request log of POXPOPDOI to see high-level success/failure.
Query PO_INTERFACE_ERRORS for specific row errors (by INTERFACE_HEADER_ID, INTERFACE_LINE_ID, etc.).
Verify PROCESS_CODE in the interface tables—successful rows typically change to ACCEPTED.

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_ID for 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.