Creating a requisition in Oracle Apps R12 is an important process for organizations that need to manage and control their purchasing cycle effectively. While requisitions can be entered manually from the application front-end, many businesses prefer to automate the process by using APIs and open interfaces. This ensures bulk data uploads, integration with external systems, and better accuracy.
In this tutorial, we will cover how to create requisition using API in Oracle Apps R12 with a focus on the Purchasing Requisition Open Interface. By the end of this guide, you will understand the interface tables involved, how to populate them, and how to submit the concurrent program to complete the process.
Understanding the Requisition Creation Process
Creating requisitions via API in Oracle R12 follows a structured approach:
- Populate Interface Table – Insert requisition data into the correct interface table.
- Submit Concurrent Program – Run the program that processes and imports the data into Oracle base tables.
- Monitor the Results – Check for success or errors in processing.
This process makes use of the Purchasing Requisition Open Interface, designed specifically for importing requisitions from external or custom systems.
Interface Tables for Requisition Creation
When you create a requisition programmatically, you primarily work with one key interface table:
PO_REQUISITIONS_INTERFACE_ALL
- This table holds the details of each requisition line.
- Each record represents one requisition line, and the same requisition number can group multiple lines together.
Important columns include:
REQUISITION_TYPE
→ Type of requisition (e.g., PURCHASE).LINE_TYPE_ID
→ Line type (Goods, Services).ITEM_ID
orITEM_DESCRIPTION
→ Item being requested.QUANTITY
→ Required quantity.DELIVER_TO_LOCATION_ID
→ Delivery location.CHARGE_ACCOUNT_ID
→ Account to charge.NEED_BY_DATE
→ Required delivery date.
Concurrent Program for Requisition Import
After inserting data into the interface table, you must run the Requisition Import concurrent program.
- Program Name: Requisition Import
- Short Name: POXPOREL
- Function: Reads requisition data from
PO_REQUISITIONS_INTERFACE_ALL
, validates it, and inserts it into the base tables.
This step is crucial because the concurrent program ensures Oracle validations (e.g., item setup, account setup, approval rules) are applied before requisitions are created.
Step-by-Step Example
Below is an example workflow to create requisition using API in Oracle Apps R12:
1. Generate Unique Identifiers
Use a sequence or custom logic to generate a unique INTERFACE_SOURCE_CODE
and INTERFACE_LINE_ID
.
SELECT po_requisitions_interface_s.NEXTVAL
INTO v_interface_line_id
FROM dual;
2. Insert Data into Interface Table
INSERT INTO PO_REQUISITIONS_INTERFACE_ALL
(
INTERFACE_SOURCE_CODE,
INTERFACE_LINE_ID,
REQUISITION_TYPE,
LINE_TYPE_ID,
ITEM_ID,
ITEM_DESCRIPTION,
QUANTITY,
UNIT_OF_MEASURE,
DELIVER_TO_LOCATION_ID,
CHARGE_ACCOUNT_ID,
NEED_BY_DATE,
CREATION_DATE,
CREATED_BY
)
VALUES
(
'API_REQ_TEST',
v_interface_line_id,
'PURCHASE',
1, -- Goods line type
1010, -- Item ID
'Laptop',
5,
'EACH',
204, -- Deliver-to Location
55555, -- Charge Account ID
SYSDATE + 7,
SYSDATE,
fnd_global.user_id
);
3. Submit Concurrent Program
Use the FND API to submit the Requisition Import program:
DECLARE
l_request_id NUMBER;
BEGIN
l_request_id := fnd_request.submit_request(
application => 'PO',
program => 'POXPOREL',
description => 'Requisition Import via API',
argument1 => 'API_REQ_TEST'); -- Source Code
COMMIT;
DBMS_OUTPUT.put_line('Request ID: ' || l_request_id);
END;
4. Monitor the Results
- Check the concurrent request log for completion status.
- Verify the
PROCESS_CODE
column inPO_REQUISITIONS_INTERFACE_ALL
: - COMPLETED → Successfully created.
- ERROR → Import failed (check log for details).
Error Handling and Validation
If the requisition import fails:
- Review the log file of the concurrent request for detailed validation errors.
- Verify mandatory fields in
PO_REQUISITIONS_INTERFACE_ALL
were populated correctly. - Ensure the item, charge account, and location IDs exist and are valid in Oracle.
- Re-insert or update interface records and resubmit the program.
Benefits of Using API for Requisition Creation
Automating requisition creation through APIs offers several advantages:
- Bulk Data Processing – Handle large volumes of requisitions quickly.
- Integration with External Systems – Link Oracle with ERP, procurement, or custom apps.
- Reduced Manual Effort – Less data entry, fewer human errors.
- Consistency – Ensures compliance with Oracle validation rules.
Conclusion
Creating requisitions programmatically in Oracle Apps R12 is best achieved through the Purchasing Requisition Open Interface. By inserting requisition details into the PO_REQUISITIONS_INTERFACE_ALL
table and running the Requisition Import (POXPOREL) concurrent program, organizations can automate the entire process, saving time and ensuring data accuracy.
This approach is widely used in Oracle implementations to support system integration, streamline procurement workflows, and enhance operational efficiency.
The post How to Create Requisition Using API in Oracle Apps R12 appeared first on Vinish.Dev.