Purchase Order Interface in Oracle Apps R12
In large organizations, seamless procurement processes are vital. Yet, many businesses struggle with data inconsistencies and manual efforts when integrating external purchase order (PO) interface data into Oracle Apps R12. The Purchase Order Interface offers a powerful solution to automate PO creation, ensuring accuracy, efficiency, and better data governance.
The PO Interface in Oracle Apps R12 is a standardized method to import PO data—headers, lines, and distributions—from external systems or flat files into the Oracle Purchasing module. This interface is particularly crucial for companies migrating from legacy systems or automating procurement through third-party platforms.
Key Components of Purchase Order Interface
Staging Tables of Purchase Order Interface
You need to create staging tables to temporary stored the purchase orders data from the source system into Oracle Apps. Before loading the data you need to make sure to create the structure of the temporary(staging) tables similar to the source data files. You can use SQL*Loader to load raw data from external files into staging tables.
Interface Tables of Purchase Order Interface
You need to create a custom PL/SQL program to validate the data in staging tables. Once validated the PO data should be inserted into interface table;
- PO_HEADERS_INTERFACE: Contains header-level data such as vendor details, PO type, currency, and terms.
- PO_LINES_INTERFACE: Holds line and shipment level data including item, quantity, and price.
- PO_DISTRIBUTIONS_INTERFACE: Manages accounting details, charge accounts, and project-related information.
You can review the details of the PO interface tables in our another post.
Validation Program and Field Validation for Purchase Order Interface
Before loading staging data into interface tables, validate all mandatory fields using PL/SQL procedure:
Header Validation of Purchase Order Interface
Purchase Order Header Validation
-- 1. Buyer Name
SELECT agent_id
INTO l_agent_id
FROM po_agents_v
WHERE UPPER(agent_name) = UPPER(rec_po_headers.buyer_name)
AND (end_date_active IS NULL
OR end_date_active > SYSDATE
);
--
-- 2. Vendor Name
SELECT vendor_id
INTO l_vendor_id
FROM ap_suppliers
WHERE UPPER(vendor_name) = UPPER(rec_po_headers.supplier_name)
AND (end_date_active IS NULL
OR end_date_active > SYSDATE
);
--
-- 3. Vendor Site Code
SELECT vendor_site_id
INTO l_vendor_site_id
FROM ap_supplier_sites_all
WHERE UPPER(vendor_site_code) = UPPER(rec_po_headers.vendor_site_code)
AND vendor_id = l_vendor_id
AND org_id = l_org_id
AND (inactive_date IS NULL OR inactive_date > SYSDATE);
--
-- 4. Ship‑To Location Code
SELECT location_code
INTO l_ship_to_location
FROM hr_locations
WHERE UPPER(location_code) = UPPER(rec_po_headers.deliver_loc_code)
AND (inactive_date IS NULL OR inactive_date > SYSDATE);
--
-- 5. Vendor Contact
SELECT vendor_contact_id
INTO l_vendor_contact_id
FROM po_vendor_contacts
WHERE vendor_site_id = l_vendor_site_id
AND UPPER(first_name || ' ' || middle_name || ' ' || last_name) = UPPER(rec_po_headers.vendor_contact)
AND (inactive_date IS NULL OR inactive_date > SYSDATE);
--
-- 6. Bill‑To Location Code
SELECT location_id
INTO l_bill_to_location_id
FROM hr_locations
WHERE UPPER(location_code) = UPPER(rec_po_headers.bill_to_location_code)
AND (inactive_date IS NULL OR inactive_date > SYSDATE);Line Validation of Purchase Order Interface
Validation of PO Line level fields
--1. Validation of line type
SELECT line_type_id
INTO l_line_type_id
FROM po_line_types_val_v
WHERE UPPER (line_type) = UPPER (rec_lines.line_type);
--
--2. dest_org_code Validation
SELECT organization_id,
organization_code
INTO l_organization_id,
l_organization_code
FROM org_organization_definitions
WHERE UPPER(organization_code) = UPPER (rec_lines.dest_org_code);
--
--3. Item Validation
SELECT inventory_item_id
INTO l_item_id
FROM mtl_system_items_b
WHERE UPPER (segment1) = UPPER (rec_lines.item)
AND organization_id = l_organization_id
AND purchasing_enabled_flag = 'Y'
AND purchasing_item_flag = 'Y';
--
--4. Validation of Unit of Measure
SELECT unit_of_measure
INTO l_unit_of_measure
FROM mtl_units_of_measure_tl
WHERE UPPER (uom_code) = UPPER (rec_lines.uom_code)
AND LANGUAGE = 'US';
--
--5. Item Quantity must be greater than 0.You need to populate error_msg and record_status as Error/Success based on the validation.
PO Import Program in Oracle Apps R12
Import Standard Purchase Orders:
In earlier version Purchasing Document Open Interface program was the PO import program in Oracle apps R12 to import all types of purchase orders. But now you have two new concurrent programs Import Price Catalogs and Import Standard Purchase Orders. Firstly Import Price Catalogs concurrent program is used to import Catalog Quotations, Standard Quotations, and Blanket Purchase Agreements. Another program Import Standard Purchase Orders concurrent program is the new import PO program in Oracle apps R12.
This concurrent program validates and imports data from the interface tables into standard PO base tables like PO_HEADERS_ALL, PO_LINES_ALL, PO_LINE_LOCATIONS_ALL and PO_DISTRIBUTIONS_ALL.
Key Considerations of Purchase Order Interface
- Validation: Oracle validates data integrity and business rules before import.
- Mandatory Fields: Fields like ORD_ID, VENDOR_ID and DOCUMENT_TYPE_CODE must be populated correctly.
- PO Types Supported: Includes Standard, Blanket, and Contract POs.
Preparing Data for Purchase Order Interface in Oracle Apps R12
Source Data Identification
Common sources include:
- Legacy ERP systems
- Supplier portals
- Spreadsheets maintained by procurement teams
Ensure clear mapping between source data and Oracle interface columns.
Data Transformation and Cleansing of Purchase Order Interface
- Convert date formats to Oracle-compatible types.
- Normalize unit of measure codes.
- Translate external codes (e.g., supplier names to supplier IDs).
File Format and Structure
Use structured files like:
- CSV or delimited text files
Maintain strict order and column naming based on interface tables.
Populating Interface Tables of Purchase Order Interface
You can use below two options to populate the data into Purchase Order interface tables. You can see PL/SQL package to validate the purchase order data in staging tables. Then insert the validated PO data into interface tables.
- Insert Validate Data using Custom PL/SQL procedure
- Integration Middleware (e.g., Oracle Integration Cloud)
Running the “Import Standard Purchase Orders” Program
Once the validated PO staging table data inserted into PO Interface tables you need to run “Import Standard Purchase Orders” to import purchase orders into Oracle Apps R12.
Navigation of Purchase Order Interface
Oracle Applications > Purchasing Responsibility > Reports > Run
Select: Import Standard Purchase Orders
Key Parameters of Purchase Order Interface
- Operating Unit: Drives org-specific imports.
- Create or Update Items: Yes/No
- Approval Status: Yes/No
- Batch ID: Used to logically group interface records.
Monitoring the Purchase Order Interface in Oracle Apps R12 Request
Check the Concurrent Request log for success or failure. Use the PO_INTERFACE_ERRORS table to diagnose issues.
Common Errors and Resolutions
| Error Message | Possible Cause | Resolution |
|---|---|---|
| Invalid Vendor ID | Vendor not defined or inactive | Validate vendor setup in AP |
| Distribution account missing | Charge account not populated | Ensure valid segment combinations |
| Document type code invalid | Wrong or unsupported document type | Use values like ‘STANDARD’, ‘BLANKET’, ‘CONTRACT’ |
Best Practices and Tips for Successful PO Interface Implementation
- Accurate Data Mapping: Ensure every external data point maps correctly to Oracle fields.
- Validation Rules: Add pre-validation scripts to catch errors before loading data.
- Error Handling: Create logs and audit trails for traceability.
- Performance Optimization: Use batch commits and index key columns in staging tables.
- Security: Restrict access to sensitive procurement and supplier data during the process.
- Staging & Validation: Catch errors before interface load.
- Incremental Testing: Start small, then scale up. Use a subset of data to validate the end-to-end process.
Conclusion
The PO Interface in Oracle R12 is a robust tool that significantly enhances procurement integration with external systems. By understanding its components, preparing clean and well-mapped data, and implementing sound error-handling strategies, businesses can ensure seamless PO creation and reduce manual effort.
Whether you’re integrating data from a legacy ERP or automating PO entries via a third-party application, mastering the PO Interface can help you achieve procurement excellence.
Optional Additions
Real-World Scenarios of Purchase Order Interface
- Importing purchase orders from a supplier bidding portal: Once the bidding process concludes, the winning bids are exported into a specific file format, which is then transformed and loaded into the PO Interface tables.
- Uploading blanket purchase orders and creating automated releases: Facilitates the creation of releases against pre-negotiated agreements, streamlining recurring purchases.
Custom Enhancements
- Custom staging validations using Oracle Apex or PL/SQL packages: Provides more user-friendly error feedback before running the concurrent program, improving data quality.
- Auto-email notifications for PO import failures: Enables proactive monitoring and faster issue resolution, minimizing disruption to procurement processes.
Interconnected Modules for Purchase Order Interface
- Inventory: PO lines for stocked items integrate with inventory, ensuring accurate and timely updates to stock levels and preventing stockouts.
- Payables: Correctly created POs are crucial for accurate invoice matching, streamlining the payment process and avoiding discrepancies, which leads to better supplier relationships.
Staging Tables of Purchase Order Interface
PO Interface Staging Table Defination
--PO Headers Staging Table
CREATE TABLE xxpo_headers_stg(
header_id NUMBER
,batch_id NUMBER
,action VARCHAR2(25)
,document_type_code VARCHAR2(25)
,currency_code VARCHAR2(15)
,full_name VARCHAR2(240)
,vendor_name VARCHAR2(240)
,vendor_site_code VARCHAR2(15)
,ship_to_location VARCHAR2(60)
,bill_to_location VARCHAR2(60)
,approval_status VARCHAR2(25)
,freight_carrier VARCHAR2(25)
,fob VARCHAR2(25)
,freight_terms VARCHAR2(25)
,record_status VARCHAR2(1)
,error_msg VARCHAR2(5000)
);
--PO Lines Staging Table
CREATE TABLE xxpo_lines_stg
(
header_id NUMBER
,line_id NUMBER
,line_num NUMBER
,shipment_num NUMBER
,line_type VARCHAR2(25)
,item VARCHAR2(1000)
,item_description VARCHAR2(240)
,item_id NUMBER
,uom_code VARCHAR2(3)
,quantity NUMBER
,unit_price NUMBER
,ship_to_org VARCHAR2(3)
,ship_to_location VARCHAR2(60)
,list_price NUMBER
,record_status VARCHAR2(1)
,error_msg VARCHAR2(5000)
);
--PO Distributions Staging Table
CREATE TABLE xxpo_distributions_stg
(header_id NUMBER,
line_id NUMBER,
distribution_id NUMBER,
quantity_ordered NUMBER,
dest_org_id NUMBER,
set_of_books_id NUMBER,
charge_account_id VARCHAR2(2000),
distribution_num NUMBER,
record_status VARCHAR2(1),
error_msg VARCHAR2(5000)
);Staging Table Update Procedure
Update Staging Table
PROCEDURE update_stag(p_type IN VARCHAR2,
p_head_id IN NUMBER,
p_line_id IN NUMBER DEFAULT NULL,
p_dist_id IN NUMBER DEFAULT NULL,
p_rec_status IN VARCHAR2,
p_err_msg IN VARCHAR2 DEFAULT NULL
)
IS
BEGIN
IF p_type = 'H'
THEN
UPDATE xxpo_headers_stg
SET record_status=p_rec_status ,
error_msg=p_err_msg
WHERE header_id=p_head_id;
ELSIF p_type = 'L'
THEN
UPDATE xxpo_lines_stg
SET record_status=p_rec_status ,
error_msg=p_err_msg
WHERE header_id=p_head_id
AND line_id=p_line_id;
ELSE
UPDATE xxpo_distributions_stg
SET record_status=p_rec_status ,
error_msg=p_err_msg
WHERE header_id=p_head_id
AND line_id=p_line_id
AND distribution_id=p_dist_id;
END IF;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
fnd_file.put_line(fnd_file.LOG, SQLERRM );
END update_stag;PO Interface Validation Package
You can follow the below logic to create the validation logic. To remove the redandancy all the validation is not added here. Please check here for other validation and add accordingly.
Package for Populating PO Interface Tables
Purchase Order Validation Logic
CREATE OR REPLACE PACKAGE BODY XXPO_VALIDATION_PKG
IS
PROCEDURE po_import_main(
x_errbuf OUT VARCHAR2,
x_retcode OUT NUMBER
)
IS
-- Cursors for staging data
CURSOR c_po_headers
IS
SELECT *
FROM xxpo_headers_stg
WHERE record_status='N'
;
CURSOR c_lines(p_hdr_id NUMBER)
IS
SELECT *
FROM xxpo_lines_stg
WHERE header_id = p_hdr_id;
CURSOR c_dists(p_line_id NUMBER)
IS
SELECT *
FROM xxpo_distributions_stg
WHERE line_id = p_line_id;
-- Local variables
l_org_id NUMBER;
l_vendor_id NUMBER;
l_site_code VARCHAR2(100);
l_curr_code VARCHAR2(10);
l_doc_type VARCHAR2(25);
l_agent_id NUMBER;
l_item_id NUMBER;
l_error_msg VARCHAR2(5000);
l_record_status VARCHAR2(1) := 'S';
BEGIN
-- Resolve Organization ID
BEGIN
SELECT organization_id
INTO l_org_id
FROM hr_operating_units
WHERE name ='IN Operating Unit';
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_record_status := 'E';
l_error_msg := 'Invalid Operating Unit';
fnd_file.put_line(fnd_file.LOG, l_error_msg );
x_retcode := 1;
RETURN;
END;
-- Process each header record
FOR rec_po_headers IN c_po_headers
LOOP
-- Validate Vendor
BEGIN
SELECT vendor_id
INTO l_vendor_id
FROM po_vendors
WHERE vendor_name = rec_po_headers.vendor_name;
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_record_status := 'E';
l_error_msg := 'Invalid vendor_name = '||
rec_po_headers.vendor_name;
fnd_file.put_line(fnd_file.LOG, l_error_msg);
END;
-- Validate Vendor Site
BEGIN
SELECT vendor_site_code
INTO l_site_code
FROM po_vendor_sites_all
WHERE vendor_site_code = rec_po_headers.vendor_site_code;
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_record_status := 'E';
l_error_msg := 'Invalid vendor_site_code = ' ||
rec_po_headers.vendor_site_code;
fnd_file.put_line(fnd_file.LOG, l_error_msg);
END;
-- Validate Currency Code
BEGIN
SELECT currency_code
INTO l_curr_code
FROM fnd_currencies
WHERE currency_code = rec_po_headers.currency_code;
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_record_status := 'E';
l_error_msg := 'Invalid currency_code = ' || rec_po_headers.currency_code;
fnd_file.put_line(fnd_file.LOG, l_error_msg);
END;
--Validate Document Type
BEGIN
SELECT document_type_code
INTO l_doc_type
FROM po_document_types
WHERE document_type_code = rec_po_headers.document_type_code;
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_record_status := 'E';
l_error_msg := 'Invalid document_type_code = ' || rec_po_headers.document_type_code;
fnd_file.put_line(fnd_file.LOG, l_error_msg);
END;
-- Validate Buyer
BEGIN
SELECT person_id
INTO l_agent_id
FROM per_all_people_f
WHERE full_name = rec_po_headers.full_name;
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_record_status := 'E';
l_error_msg := 'Invalid buyer name = ' ||
rec_po_headers.full_name;
fnd_file.put_line(fnd_file.LOG, l_error_msg);
END;
IF l_record_status = 'E'
THEN
update_stag(p_type => 'H',
p_head_id => rec_po_headers.header_id,
p_rec_status => l_record_status ,
p_err_msg => l_error_msg
);
ELSE
-- Insert into po_headers_interface
INSERT INTO po_headers_interface(
interface_header_id,
batch_id,
org_id,
action,
document_type_code,
currency_code,
agent_id,
agent_name,
vendor_name,
vendor_site_code,
ship_to_location,
bill_to_location,
approval_status,
freight_terms,
fob,
freight_carrier,
created_by,creation_date, last_update_date,
last_updated_by)
VALUES (
po_headers_interface_s.NEXTVAL,
rec_po_headers.batch_id,
l_org_id,
rec_po_headers.action,
rec_po_headers.document_type_code,
rec_po_headers.currency_code,
l_agent_id,
rec_po_headers.full_name,
rec_po_headers.vendor_name,
rec_po_headers.vendor_site_code,
rec_po_headers.ship_to_location,
rec_po_headers.bill_to_location,
rec_po_headers.approval_status,
rec_po_headers.freight_terms,
rec_po_headers.fob, rec_po_headers.freight_carrier,
-1, SYSDATE, SYSDATE, -1
);
update_stag(p_type => 'H',
p_head_id => rec_po_headers.header_id,
p_rec_status => l_record_status
);
-- Process associated lines
FOR rec_lines IN c_lines(rec_po_headers.header_id)
LOOP
-- Validate Item ID
BEGIN
SELECT inventory_item_id
INTO l_item_id
FROM mtl_system_items_b
WHERE segment1 = rec_lines .item
AND organization_id = l_org_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_record_status := 'E';
l_error_msg := 'Invalid item = ' || rec_lines.item;
fnd_file.put_line(fnd_file.LOG, l_error_msg);
END;
IF l_record_status = 'E'
THEN
update_stag(p_type => 'L',
p_head_id => rec_po_headers.header_id,
p_line_id => rec_lines.line_id,
p_rec_status => l_record_status,
p_err_msg => l_error_msg
);
ELSE
-- Insert Line into Interface
INSERT INTO po_lines_interface (
interface_line_id, interface_header_id, line_num,
shipment_num, line_type, item, item_description,
item_id, uom_code, quantity, unit_price,
ship_to_organization_code, ship_to_location,
need_by_date, list_price_per_unit,
created_by, creation_date, last_update_date,
last_updated_by)
VALUES (
po_lines_interface_s.NEXTVAL,
po_headers_interface_s.CURRVAL,
rec_lines.line_num, rec_lines.shipment_num,
rec_lines.line_type, rec_lines.item,
rec_lines.item_description, l_item_id,
rec_lines.uom_code, rec_lines.quantity,
rec_lines.unit_price,
rec_lines.ship_to_organization_code,
rec_lines.ship_to_location,
SYSDATE, rec_lines.list_price_per_unit,
-1, SYSDATE, SYSDATE, -1
);
update_stag(p_type => 'L',
p_head_id => rec_lines.header_id,
p_line_id => rec_lines.line_id,
p_rec_status => l_record_status
);
-- Process distributions
FOR rec_dists IN c_dists(rec_lines.line_id)
LOOP
IF l_record_status = 'S'
THEN
INSERT INTO po_distributions_interface (
interface_header_id, interface_line_id,
interface_distribution_id, org_id,
quantity_ordered, destination_organization_id,
set_of_books_id, charge_account_id,
distribution_num, created_by,
creation_date, last_update_date, last_updated_by)
VALUES (
po_headers_interface_s.CURRVAL,
po_lines_interface_s.CURRVAL,
po_distributions_interface_s.NEXTVAL,
l_org_id, rec_dists.quantity_ordered,
rec_dists.destination_organization_id,
rec_dists.set_of_books_id, rec_dists.charge_account_id,
rec_dists.distribution_num,
-1, SYSDATE, SYSDATE, -1
);
update_stag(p_type => 'D',
p_head_id => rec_dists.header_id,
p_line_id => rec_dists.line_id,
p_dist_id => rec_dists.distribution_id,
p_rec_status => l_record_statuS
);
END IF;
END LOOP;
END IF;
END LOOP;
END IF;
END LOOP;
COMMIT;
x_retcode := 0;
EXCEPTION
WHEN OTHERS THEN
x_errbuf := SQLERRM;
x_retcode := -1;
END po_import_main;
END XXPO_VALIDATION_PKG;
Pingback: PO Tables in Oracle Apps R12: A Complete Guide