Creating purchase orders programmatically in Oracle Applications R12 can be done in a reliable and supported way using the Purchasing Documents Open Interface (PDOI). Many developers mistakenly assume that private packages like PO_PDOI_PVT should be called directly, but Oracle does not recommend this approach because it is an internal package that may change across patches and releases.
The correct, supported method is to insert data into PDOI interface tables and then run the seeded concurrent program Import Standard Purchase Orders (short name: POXPOPDOI). This ensures data validation, supportability, and alignment with Oracle best practices.
In this tutorial, you will learn step by step how to Create a Purchase Order Using API in Oracle Apps R12 by leveraging PDOI interface tables, and how to run the import process that converts this data into a fully created Purchase Order.
Understanding PDOI (Purchasing Documents Open Interface)
The PDOI is Oracle’s standard interface for creating or updating purchase orders and releases. It consists of a set of interface tables where you load header, line, shipment, and distribution data. Once populated, the concurrent program validates the data and moves it into the base Purchasing tables.
Key Interface Tables
- PO_HEADERS_INTERFACE – Stores PO header information like supplier, document type, buyer, currency, and operating unit.
- PO_LINES_INTERFACE – Stores line-level details such as item, description, category, quantity, unit price, and UOM.
- PO_LINE_LOCATIONS_INTERFACE – Stores shipment or schedule information including ship-to organization, need-by date, and shipment quantity.
- PO_DISTRIBUTIONS_INTERFACE – Stores accounting distribution details for the PO lines (charge accounts, percentages, etc.).
- PO_INTERFACE_ERRORS – Stores errors if validation fails during the import program.
Steps to Create a Purchase Order Using API in Oracle Apps R12
The process involves three main stages:
- Populate the PDOI interface tables with PO data.
- Submit the Import Standard Purchase Orders program.
- Validate the results and check for errors.
Example: Inserting Data into PDOI Interface Tables
Below is a simplified PL/SQL example showing how to insert a single purchase order with one line, one shipment, and one distribution.
DECLARE
l_iface_hdr_id NUMBER := po_headers_interface_s.NEXTVAL;
l_iface_line_id NUMBER := po_lines_interface_s.NEXTVAL;
l_iface_loc_id NUMBER := po_line_locations_interface_s.NEXTVAL;
l_iface_dist_id NUMBER := po_distributions_interface_s.NEXTVAL;
l_batch_id NUMBER := l_iface_hdr_id;
l_request_id NUMBER;
BEGIN
-- Insert Header
INSERT INTO po_headers_interface (
interface_header_id, batch_id, action, org_id,
document_type_code, vendor_id, vendor_site_id,
currency_code, agent_id, approval_status, comments
) VALUES (
l_iface_hdr_id, l_batch_id, 'INSERT', :p_org_id,
'STANDARD', :p_vendor_id, :p_vendor_site_id,
:p_currency, :p_buyer_id, 'INCOMPLETE', 'Created via PDOI'
);
-- Insert Line
INSERT INTO po_lines_interface (
interface_line_id, interface_header_id, line_num,
item_id, category_id, unit_of_measure, quantity, unit_price, line_type_id
) VALUES (
l_iface_line_id, l_iface_hdr_id, 1,
:p_item_id, :p_category_id, :p_uom, :p_qty, :p_price, :p_line_type_id
);
-- Insert Shipment
INSERT INTO po_line_locations_interface (
interface_line_location_id, interface_line_id,
ship_to_organization_id, quantity, need_by_date
) VALUES (
l_iface_loc_id, l_iface_line_id,
:p_ship_to_org_id, :p_qty, :p_need_by_date
);
-- Insert Distribution
INSERT INTO po_distributions_interface (
interface_distribution_id, interface_line_location_id,
distribution_num, quantity_ordered, code_combination_id
) VALUES (
l_iface_dist_id, l_iface_loc_id,
1, :p_qty, :p_charge_account_ccid
);
COMMIT;
-- Submit Import Standard Purchase Orders Program
l_request_id := fnd_request.submit_request(
application => 'PO',
program => 'POXPOPDOI',
description => NULL,
start_time => NULL,
sub_request => FALSE,
argument1 => NULL, -- Buyer ID
argument2 => 'STANDARD', -- Document Type
argument3 => NULL, -- Document Subtype
argument4 => 'N', -- Process Items Flag
argument5 => 'N', -- Create Sourcing Rule
argument6 => 'INCOMPLETE', -- Approval Status
argument7 => NULL, -- Release Generation Method
argument8 => NULL, -- Reserved
argument9 => :p_org_id, -- Operating Unit ID
argument10 => l_batch_id -- Batch ID
);
COMMIT;
END;
Validating the Results
Once the concurrent program finishes:
- Check the Request Log for the Import Standard Purchase Orders program to confirm success.
- If errors occurred, query the PO_INTERFACE_ERRORS table to see detailed messages.
- On success, you can query the base tables like PO_HEADERS_ALL and PO_LINES_ALL to see the newly created PO.
Benefits of Using PDOI for PO Creation
- Supported by Oracle – Unlike private APIs, PDOI is documented, supported, and stable across releases.
- Error Handling – All validation errors are logged in PO_INTERFACE_ERRORS for easy troubleshooting.
- Scalability – Can process single POs or large batches in the same way.
- Flexibility – Supports both creating and updating POs, and works for different document types (Standard, Blanket, etc.).
- Approval Workflow Integration – You can control approval status (INCOMPLETE, APPROVED, or INITIATE APPROVAL).
Conclusion
The right way to Create a Purchase Order Using API in Oracle Apps R12 is through the Purchasing Documents Open Interface (PDOI). By inserting data into the interface tables and running the Import Standard Purchase Orders program, you ensure compliance with Oracle standards and gain the benefits of validation, error handling, and long-term support.
While internal packages like PO_PDOI_PVT exist, they are not recommended because Oracle does not support direct usage of private APIs. Using PDOI keeps your solution robust, maintainable, and upgrade-safe.
The post How to Create a Purchase Order Using API in Oracle Apps R12 appeared first on Vinish.Dev.