sia.hackernoon.com

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 or ITEM_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 in PO_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.

The Complete Workflow for Automating Requisitions in Oracle R12