Introduction:
This guide will explain how to create a PO purchase order in oracle apps r12 using Open interface tables.
Oracle PO open interface tables are used to create new PO purchase orders, whether from purchase requisition or imported from external systems.
PO Interface Tables In Oracle APPS R12 :
There are four interface tables in PO:
- PO_HEADERS_INTERFACE: Stores header information about the purchase order you create or load for import.
- PO_LINES_INTERFACE: Stores line information about the PO. Use it in conjunction with the PO_HEADERS_INTERFACE table.
- PO_LINE_LOCATIONS_INTERFACE: Stores shipment information about the PO. Use it in conjunction with the PO_LINES_INTERFACE & PO_HEADERS_INTERFACE table.
- PO_DISTRIBUTIONS_INTERFACE: Stores distributions information about the PO. Use it in conjunction with the PO_HEADERS_INTERFACE & PO_LINES_INTERFACE & PO_LINE_LOCATIONS_INTERFACE table.
PO Open Interface Error Table In Oracle APPS R12 :
After importing the PO, if something goes wrong due to incorrect data entered in the interface tables or due to standard validation failure before creating the PO, Oracle has a table to record the error details, which is:
- PO_INTERFACE_ERRORS: stores information about PO data from the interface tables, which Import Standard Purchase Orders could not process.
Note: you can purge data in this table using the Purge Purchasing Open Interface Processed Data Request.
Goal:
Create a new standard purchase order using interface tables.
Steps:
- Populate the required information into the PO Interface header (PO_HEADERS_INTERFACE).
- Populate required information into PO Lines, which is (PO_LINES_INTERFACE).
- Run the Import Standard Purchase Orders program to import PO from the PO interface to PO base tables.
Forms Or Pages:
Example:
Required Columns:
- PO_HEADERS_INTERFACE
INTERFACE_HEADER_ID (Required): Unique identifier for this po header within this batch. The same value should be populated in the po lines in the PO_LINES_INTERFACE table to identify the data belonging to the same purchase order.
BATCH_ID (Required): This column is used to give a unique identifier to the purchase order in the interface table that we can use later when importing the po within a particular patch instead of processing all data in the interface tables.
PROCESS_CODE (Required): All new records should take the value (“PENDING”) to let the import program take them.
ACTION (Required): In this column, we specify whether we need to update an existent PO or Add to an existent po, or it’s an entirely new po. Values (‘ORIGINAL‘, ‘UPDATE‘, ‘ADD‘)
ORG_ID (Optional): to specify the operating unit.
DOCUMENT_TYPE_CODE (Optional) : (STANDARD) is refer to the standard purchase order.
CURRENCY_CODE (Required): Currency code for the po.
AGENT_ID (Optional): The buyer defined the PO.
VENDOR_ID & VENDOR_SITE_ID (Required): VENDOR_ID is a unique identifier for a supplier, and VENDOR_SITE_ID is an Internal supplier site identifier.
SHIP_TO_LOCATION_ID (Optional): Shipping location for the purchase order.
BILL_TO_LOCATION_ID (Optional) : Billing location for the purchase order.
APPROVED_DATE (Optional): The date of the approval in case we need to create the PO in the “APPROVED” approval status.
DESCRIPTION (Optional): Enter the description that you want to assign to the PO created from this record.
ATTRIBUTE1 (Optional): Enter any value for your reference in the interface table to identify your records from other applications’ records.
- PO_LINES_INTERFACE
INTERFACE_LINE_ID (Required): Unique identifier for each po line.
INTERFACE_HEADER_ID (Required): Unique identifier for this po header within this batch. It will take the same generated value in the header because this is the link between the lines and its header.
LINE_NUM (Required): You can enter a unique number to identify the line.
LINE_TYPE (Required): It could be “Goods” if we purchase items or services. in our case, here it is Goods.
ITEM_ID (Optional): The inventory item identifier we need to purchase.
ITEM_DESCRIPTION (Optional): The description of the item.
UNIT_OF_MEASURE (Required): The item unit of measure.
QUANTITY (Required): The quantity of items we need to buy.
UNIT_PRICE (Required): The unit price for the item being ordered.
SHIP_TO_ORGANIZATION_ID (Optional): The organization identifier that belongs to the shipping location in the header.
NEED_BY_DATE (Optional): The date we need to receive the goods as a company.
PROMISED_DATE (Optional): The date the supplier can supply the goods for us.
Create a PO by Populating the Header & Lines Example Code:
/*
=======================================================================================================
Name : Create Standard Purchase Order
Created By : Hassan For Oraask.Com
Purpose : This plsql program is used to insert data into PO interface table to create Standard
Purchase Order through PDOI: Import Standard Purchase Order Concurrent Program
*/
DECLARE
L_PROCESS_CODE PO_HEADERS_INTERFACE.PROCESS_CODE%TYPE := 'PENDING';
L_ACTION PO_HEADERS_INTERFACE.ACTION%TYPE := 'ORIGINAL';
L_DOCUMENT_TYPE_CODE PO_HEADERS_INTERFACE.DOCUMENT_TYPE_CODE%TYPE := 'STANDARD';
---------------------------------------------------------------------------------
--Header level information
---------------------------------------------------------------------------------
--This provides batch id which is used to run particular set of data
L_INTERFACE_HEADER_ID PO_HEADERS_INTERFACE.INTERFACE_HEADER_ID%TYPE;
L_BATCH_ID PO_HEADERS_INTERFACE.BATCH_ID%TYPE := 10000;
L_ORG_ID PO_HEADERS_INTERFACE.ORG_ID%TYPE;
L_AGENT_NAME PER_ALL_PEOPLE_F.FULL_NAME%TYPE := 'Stock, Ms. Pat';
L_AGENT_ID PO_HEADERS_INTERFACE.AGENT_ID%TYPE := 25;
L_VENDOR_NAME AP_SUPPLIERS.VENDOR_NAME%TYPE := 'Dell Computers';
L_VENDOR_ID PO_HEADERS_INTERFACE.VENDOR_ID%TYPE;
L_VENDOR_SITE_ID PO_HEADERS_INTERFACE.VENDOR_SITE_ID%TYPE;
L_SHIP_TO_LOCATION_ID PO_HEADERS_INTERFACE.SHIP_TO_LOCATION_ID%TYPE;
L_BILL_TO_LOCATION_ID PO_HEADERS_INTERFACE.BILL_TO_LOCATION_ID%TYPE;
L_COMMENTS PO_HEADERS_INTERFACE.COMMENTS%TYPE := 'This is a Purchase Order Imported through Interface for Oraask.Com';
L_CURRENCY_CODE FND_CURRENCIES.CURRENCY_CODE%TYPE := 'USD';
-- Contains Attribute value which can be your username
-- which can be used to check records inserted by the user
L_ATTRIBUTE1 PO_HEADERS_INTERFACE.ATTRIBUTE1%TYPE := 'ORAASK POXPOPDOI IMPORT';
---------------------------------------------------------------------------------
--Line level information
---------------------------------------------------------------------------------
L_INTERFACE_LINE_ID PO_LINES_INTERFACE.INTERFACE_LINE_ID%TYPE;
L_LINE_TYPE PO_LINES_INTERFACE.LINE_TYPE%TYPE := 'Goods';
L_ITEM_NUMBER PO_LINES_INTERFACE.ITEM%TYPE := 'AS10000';
L_UOM_CODE PO_LINES_INTERFACE.UOM_CODE%TYPE := 'Ea';
L_QUANTITY PO_LINES_INTERFACE.QUANTITY%TYPE := 100;
L_UNIT_PRICE PO_LINES_INTERFACE.UNIT_PRICE%TYPE := 100;
L_NEED_BY_DATE DATE := SYSDATE;
L_PROMISED_DATE DATE := SYSDATE;
L_HEADER_COUNT NUMBER := 1;
L_LINE_COUNT NUMBER := 2;
L_ITEM_CNT NUMBER := 0;
L_UOM_CNT NUMBER := 0;
L_ERROR_MSG VARCHAR2 (4000);
BEGIN
DBMS_OUTPUT.PUT_LINE ('********************************');
DBMS_OUTPUT.PUT_LINE ('Start Get Vendor Identifier');
-- Get Vendor ID
BEGIN
SELECT VENDOR_ID
INTO L_VENDOR_ID
FROM AP_SUPPLIERS
WHERE VENDOR_NAME = L_VENDOR_NAME
AND ENABLED_FLAG = 'Y';
EXCEPTION
WHEN NO_DATA_FOUND THEN
FND_MESSAGE.SET_NAME ('FND', 'FND_GENERIC_MESSAGE');
FND_MESSAGE.SET_TOKEN ('MESSAGE', ' SEQ: 01' || ' - Unable to Get Vendor Information: ' || SQLERRM);
L_ERROR_MSG := SUBSTR (FND_MESSAGE.GET, 1, 4000);
RAISE FND_API.G_EXC_ERROR;
END;
DBMS_OUTPUT.PUT_LINE ('Start Get Vendor Site Informations');
-- Get Vendor Site ID
BEGIN
SELECT VENDOR_SITE_ID, SHIP_TO_LOCATION_ID, BILL_TO_LOCATION_ID
INTO L_VENDOR_SITE_ID, L_SHIP_TO_LOCATION_ID, L_BILL_TO_LOCATION_ID
FROM AP_SUPPLIER_SITES_ALL
WHERE VENDOR_ID = L_VENDOR_ID
AND ORG_ID = 204
AND VENDOR_SITE_CODE = 'DELL';
EXCEPTION
WHEN OTHERS THEN
FND_MESSAGE.SET_NAME ('FND', 'FND_GENERIC_MESSAGE');
FND_MESSAGE.SET_TOKEN ('MESSAGE', ' SEQ: 02' || ' - Unable to Get Vendor Site Information: ' || SQLERRM);
L_ERROR_MSG := SUBSTR (FND_MESSAGE.GET, 1, 4000);
RAISE FND_API.G_EXC_ERROR;
END;
-- Get Buyer Information
BEGIN
SELECT AGENT_ID
INTO L_AGENT_ID
FROM PO_AGENTS PA, PER_ALL_PEOPLE_F PPL
WHERE PA.AGENT_ID = PPL.PERSON_ID
AND UPPER (PPL.FULL_NAME) = UPPER (L_AGENT_NAME);
EXCEPTION
WHEN OTHERS THEN
FND_MESSAGE.SET_NAME ('FND', 'FND_GENERIC_MESSAGE');
FND_MESSAGE.SET_TOKEN ('MESSAGE', ' SEQ: 03' || ' - Unable to Get Buyer Identifier: ' || SQLERRM);
L_ERROR_MSG := SUBSTR (FND_MESSAGE.GET, 1, 4000);
RAISE FND_API.G_EXC_ERROR;
END;
DBMS_OUTPUT.PUT_LINE ('Start Validate Item Existence');
-- Validate Item Existence
BEGIN
SELECT COUNT (INVENTORY_ITEM_ID)
INTO L_ITEM_CNT
FROM MTL_SYSTEM_ITEMS_B
WHERE SEGMENT1 = L_ITEM_NUMBER
AND PURCHASING_ENABLED_FLAG = 'Y'
AND PURCHASING_ITEM_FLAG = 'Y';
IF (L_ITEM_CNT = 0) THEN
FND_MESSAGE.SET_NAME ('FND', 'FND_GENERIC_MESSAGE');
FND_MESSAGE.SET_TOKEN ('MESSAGE', ' SEQ: 04' || ' - Item ' || L_ITEM_NUMBER || ' Does not Exists');
L_ERROR_MSG := SUBSTR (FND_MESSAGE.GET, 1, 4000);
RAISE FND_API.G_EXC_ERROR;
END IF;
END;
BEGIN
SELECT COUNT (*)
INTO L_UOM_CNT
FROM MTL_UNITS_OF_MEASURE
WHERE UOM_CODE = L_UOM_CODE;
IF (L_UOM_CNT = 0) THEN
FND_MESSAGE.SET_NAME ('FND', 'FND_GENERIC_MESSAGE');
FND_MESSAGE.SET_TOKEN ('MESSAGE', ' SEQ: 05' || ' - Unit Of Measure ' || L_UOM_CODE || ' Does not Exists');
L_ERROR_MSG := SUBSTR (FND_MESSAGE.GET, 1, 4000);
RAISE FND_API.G_EXC_ERROR;
END IF;
END;
--Header Loop
FOR HDR_CNT IN 1 .. L_HEADER_COUNT
LOOP
DBMS_OUTPUT.PUT_LINE ('Start Insert Header Information');
L_INTERFACE_HEADER_ID := PO_HEADERS_INTERFACE_S.NEXTVAL;
INSERT INTO PO_HEADERS_INTERFACE (INTERFACE_HEADER_ID
,BATCH_ID
,PROCESS_CODE
,ACTION
,ORG_ID
,DOCUMENT_TYPE_CODE
,COMMENTS
,CURRENCY_CODE
,AGENT_ID
,VENDOR_ID
,VENDOR_SITE_ID
,SHIP_TO_LOCATION_ID
,BILL_TO_LOCATION_ID
,ATTRIBUTE1
,CREATION_DATE)
VALUES (L_INTERFACE_HEADER_ID
,L_BATCH_ID
,L_PROCESS_CODE
,L_ACTION
,L_ORG_ID
,L_DOCUMENT_TYPE_CODE
,L_COMMENTS
,L_CURRENCY_CODE
,L_AGENT_ID
,L_VENDOR_ID
,L_VENDOR_SITE_ID
,L_SHIP_TO_LOCATION_ID
,L_BILL_TO_LOCATION_ID
,L_ATTRIBUTE1
,SYSDATE);
---Line Loop
FOR LINE_CNT IN 1 .. L_LINE_COUNT
LOOP
DBMS_OUTPUT.PUT_LINE ('Start Insert Line (' || LINE_CNT || ') Information');
L_INTERFACE_LINE_ID := PO_LINES_INTERFACE_S.NEXTVAL;
INSERT INTO PO_LINES_INTERFACE (INTERFACE_LINE_ID
,INTERFACE_HEADER_ID
,ACTION
,LINE_NUM
,LINE_TYPE
,ITEM
,UOM_CODE
,QUANTITY
,UNIT_PRICE
,SHIP_TO_LOCATION_ID
,NEED_BY_DATE
,PROMISED_DATE
,CREATION_DATE
,LINE_LOC_POPULATED_FLAG)
VALUES (L_INTERFACE_LINE_ID
,L_INTERFACE_HEADER_ID
,L_ACTION
,LINE_CNT
,L_LINE_TYPE
,L_ITEM_NUMBER
,L_UOM_CODE
,L_QUANTITY
,L_UNIT_PRICE
,L_SHIP_TO_LOCATION_ID
,L_NEED_BY_DATE
,L_PROMISED_DATE
,SYSDATE
,'N');
END LOOP; ---End of Line Loop
END LOOP; ---End of Header Loop
DBMS_OUTPUT.PUT_LINE ('********************************');
--COMMIT;
EXCEPTION
WHEN FND_API.G_EXC_ERROR THEN
DBMS_OUTPUT.PUT_LINE (L_ERROR_MSG);
DBMS_OUTPUT.PUT_LINE ('********************************');
ROLLBACK;
WHEN OTHERS THEN
FND_MESSAGE.SET_NAME ('FND', 'FND_GENERIC_MESSAGE');
FND_MESSAGE.SET_TOKEN ('MESSAGE', 'Create Request Group Failed with Error: ' || SQLERRM);
L_ERROR_MSG := SUBSTR (FND_MESSAGE.GET, 1, 4000);
DBMS_OUTPUT.PUT_LINE (L_ERROR_MSG);
DBMS_OUTPUT.PUT_LINE ('********************************');
ROLLBACK;
END;Result:
Start Get Vendor Identifier
Start Get Vendor Site Informations
Start Validate Item Existence
Start Insert Header Information
Start Insert Line (1) Information
Start Insert Line (2) Information
Submit Import Standard Purchase Orders Program to Create the PO
After populating PO interface tables as per our need, now is the time to submit the import program to pull the data inserted into the interface table and create the PO.
For this step, we have two options which are:
- Submit Import Standard Purchase Orders Program from Backend
- Submit Import Standard Purchase Orders Program from the Application
Submit Import Standard Purchase Orders Program From Backend
DECLARE
L_DEFAULT_BUYER_ID NUMBER;
L_APPROVAL_STATUS VARCHAR2 (150) := 'INCOMPLETE';
L_BATCH_ID NUMBER := 1000;
L_ORG_ID NUMBER := 204;
L_REQUEST_ID NUMBER;
L_RESULT BOOLEAN;
L_PHASE VARCHAR2 (100);
L_STATUS VARCHAR2 (100);
L_DEV_PHASE VARCHAR2 (100);
L_DEV_STATUS VARCHAR2 (100);
L_MESSAGE VARCHAR2 (100);
L_INTERFACE_ERROR VARCHAR2 (4000);
L_ERROR_MSG VARCHAR2 (4000);
BEGIN
DBMS_OUTPUT.PUT_LINE ('********************************');
FND_GLOBAL.APPS_INITIALIZE (1318, 50578, 201);
MO_GLOBAL.INIT ('PO');
FND_REQUEST.SET_ORG_ID (L_ORG_ID);
-- SUBMIT IMPORT STANDARD PURCHASE ORDERS
L_REQUEST_ID :=
FND_REQUEST.SUBMIT_REQUEST (APPLICATION => 'PO'
,PROGRAM => 'POXPOPDOI'
,DESCRIPTION => NULL
,START_TIME => SYSDATE
,SUB_REQUEST => FALSE
,ARGUMENT1 => L_DEFAULT_BUYER_ID
,ARGUMENT2 => 'STANDARD' -- DOCUMENT TYPE
,ARGUMENT3 => NULL -- DOCUMENT SUBTYPE
,ARGUMENT4 => 'N' -- CREATE OR UPDATE ITEMS
,ARGUMENT5 => NULL -- CREATE SOURCING RULES
,ARGUMENT6 => L_APPROVAL_STATUS -- APPROVAL STATUS
,ARGUMENT7 => NULL
,ARGUMENT8 => L_BATCH_ID
,ARGUMENT9 => L_ORG_ID -- OPERATING UNIT
,ARGUMENT10 => NULL -- GLOBAL AGREEMENT
,ARGUMENT11 => NULL -- ENABLE SOURCING LEVEL
,ARGUMENT12 => NULL -- SOURCING LEVEL
,ARGUMENT13 => NULL -- INV ORG ENABLE
,ARGUMENT14 => NULL -- INVENTORY ORGANIZATION
,ARGUMENT15 => 'N'
,ARGUMENT16 => '5000' -- batch size
,ARGUMENT17 => 'N'); -- gather stats
COMMIT;
IF L_REQUEST_ID = 0 THEN
FND_MESSAGE.SET_NAME ('FND', 'FND_GENERIC_MESSAGE');
FND_MESSAGE.SET_TOKEN ('SQLERRM', 'Unable to create PO');
FND_MESSAGE.SET_TOKEN ('SEQ', '01');
L_ERROR_MSG := SUBSTR (FND_MESSAGE.GET, 1, 3999);
RAISE FND_API.G_EXC_ERROR;
ELSE
L_RESULT :=
FND_CONCURRENT.WAIT_FOR_REQUEST (L_REQUEST_ID
,1
,0
,L_PHASE
,L_STATUS
,L_DEV_PHASE
,L_DEV_STATUS
,L_MESSAGE);
END IF;
-- Check Interface Table Errors
BEGIN
SELECT SUBSTR (RTRIM (XMLAGG (XMLELEMENT (E, PIE.ERROR_MESSAGE || ',')).EXTRACT ('//text()'), ','), 1, 3950)
INTO L_INTERFACE_ERROR
FROM PO_INTERFACE_ERRORS PIE, PO.PO_HEADERS_INTERFACE PHI
WHERE PIE.INTERFACE_HEADER_ID = PHI.INTERFACE_HEADER_ID
AND PHI.BATCH_ID = L_BATCH_ID;
END;
IF L_INTERFACE_ERROR IS NOT NULL THEN
FND_MESSAGE.SET_NAME ('FND', 'FND_GENERIC_MESSAGE');
FND_MESSAGE.SET_TOKEN ('SQLERRM', L_INTERFACE_ERROR);
FND_MESSAGE.SET_TOKEN ('SEQ', '02');
L_ERROR_MSG := SUBSTR (FND_MESSAGE.GET, 1, 3999);
ELSE
DBMS_OUTPUT.PUT_LINE ('L_REQUEST_ID = ' || L_REQUEST_ID);
DBMS_OUTPUT.PUT_LINE ('L_ERROR_MSG = ' || L_ERROR_MSG);
END IF;
DBMS_OUTPUT.PUT_LINE ('********************************');
EXCEPTION
WHEN FND_API.G_EXC_ERROR THEN
DBMS_OUTPUT.PUT_LINE (L_ERROR_MSG);
DBMS_OUTPUT.PUT_LINE ('********************************');
WHEN OTHERS THEN
FND_MESSAGE.SET_NAME ('FND', 'FND_GENERIC_MESSAGE');
FND_MESSAGE.SET_TOKEN ('SQLERRM', SQLERRM);
FND_MESSAGE.SET_TOKEN ('SEQ', '03');
L_ERROR_MSG := SUBSTR ('Backtrace => ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE || ' - ' || FND_MESSAGE.GET, 1, 3999);
DBMS_OUTPUT.PUT_LINE ('********************************');
END;In the above script, we submitted the import standard purchase order program (POXPOPDOI) from the database by passing the arguments as follows:
- ARGUMENT1: This is an optional parameter that is the default buyer.
- ARGUMENT2: This is the required parameter which is the document type. In our case, it will be “STANDARD, ” the purchase order type.
- ARGUMENT4: This is the required parameter responsible for firing the event create or updating the item while creating the po. The values are (Y, N).
- ARGUMENT6: This is the required parameter, which is the approval status, basically how we want the approval status of the po after created. the possible values are (APPROVED, INCOMPLETE, and INITIATE APPROVAL).
- ARGUMENT8: This is an optional parameter, the batch ID we generated while populating the interface table.
- ARGUMENT9: This is an optional parameter which is the operating unit.
Submit Import Standard Purchase Orders Program From the Application
Switching to responsibility (Purchasing Super User) run the request (Import Standard Purchase Orders)
Navigation to run the request: Purchasing Super User → Reports → Run → Import Standard Purchase Orders
Final Result:
Purchase Orders Screen :
In this article, we have demonstrate how to Create PO using PO open interface .
Hopefully, it was clear and concise.
Please don’t use this code directly on production environment, instead test it on test environment first to make sure that API working correctly as per your requirements.
We are only giving one example of how to allocate one line as constant value.
