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. create requisition using API in Oracle Apps R12 Purchasing Requisition Open Interface 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. Populate Interface Table – Insert requisition data into the correct interface table. Populate Interface Table Submit Concurrent Program – Run the program that processes and imports the data into Oracle base tables. Submit Concurrent Program Monitor the Results – Check for success or errors in processing. Monitor the Results This process makes use of the Purchasing Requisition Open Interface, designed specifically for importing requisitions from external or custom systems. Purchasing Requisition Open Interface Interface Tables for Requisition Creation When you create a requisition programmatically, you primarily work with one key interface table: PO_REQUISITIONS_INTERFACE_ALL 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. 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: 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. REQUISITION_TYPE → Type of requisition (e.g., PURCHASE). REQUISITION_TYPE LINE_TYPE_ID → Line type (Goods, Services). LINE_TYPE_ID ITEM_ID or ITEM_DESCRIPTION → Item being requested. ITEM_ID ITEM_DESCRIPTION QUANTITY → Required quantity. QUANTITY DELIVER_TO_LOCATION_ID → Delivery location. DELIVER_TO_LOCATION_ID CHARGE_ACCOUNT_ID → Account to charge. CHARGE_ACCOUNT_ID NEED_BY_DATE → Required delivery date. NEED_BY_DATE Concurrent Program for Requisition Import After inserting data into the interface table, you must run the Requisition Import concurrent program. Requisition Import Program Name: Requisition ImportShort Name: POXPORELFunction: Reads requisition data from PO_REQUISITIONS_INTERFACE_ALL, validates it, and inserts it into the base tables. Program Name: Requisition Import Requisition Import Short Name: POXPOREL POXPOREL Function: Reads requisition data from PO_REQUISITIONS_INTERFACE_ALL, validates it, and inserts it into the base tables. PO_REQUISITIONS_INTERFACE_ALL 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: 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. INTERFACE_SOURCE_CODE INTERFACE_LINE_ID SELECT po_requisitions_interface_s.NEXTVAL 
INTO   v_interface_line_id 
FROM   dual; 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
); 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: Requisition Import 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; 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). Check the concurrent request log for completion status. Verify the PROCESS_CODE column in PO_REQUISITIONS_INTERFACE_ALL: PROCESS_CODE PO_REQUISITIONS_INTERFACE_ALL COMPLETED → Successfully created. COMPLETED ERROR → Import failed (check log for details). ERROR Error Handling and Validation If the requisition import fails: 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. Review the log file of the concurrent request for detailed validation errors. log file Verify mandatory fields in PO_REQUISITIONS_INTERFACE_ALL were populated correctly. PO_REQUISITIONS_INTERFACE_ALL 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: 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. Bulk Data Processing – Handle large volumes of requisitions quickly. Bulk Data Processing Integration with External Systems – Link Oracle with ERP, procurement, or custom apps. Integration with External Systems Reduced Manual Effort – Less data entry, fewer human errors. Reduced Manual Effort Consistency – Ensures compliance with Oracle validation rules. Consistency 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. Purchasing Requisition Open Interface PO_REQUISITIONS_INTERFACE_ALL Requisition Import (POXPOREL) 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. How to Create Requisition Using API in Oracle Apps R12 Vinish.Dev

Walkthroughs, tutorials, guides, and tips. This story will teach you how to do something new or how to do something better.

How to Create Purchase Order Lines in Oracle R12 (with PL/SQL Examples)

Read My Stories

Too Long; Didn't Read

Make resilience your competitive advantage

The Complete Workflow for Automating Requisitions in Oracle R12

About Author

Comments

TOPICS

THIS ARTICLE WAS FEATURED IN

Related Stories

Untitled Story

4 Sure Ways to Display PDFs in Oracle Forms

How to Optimize Location-Specific Landing Pages That Actually Drive Sales

Meet Uzi World Digital, Winner of Startups of the year 2024 in Kolkata, West Bengal, India

The Future of Tractable Deep Generative Models

How to Automate Supplier Site Creation in Oracle R12 Using APIs

4 Sure Ways to Display PDFs in Oracle Forms

How to Optimize Location-Specific Landing Pages That Actually Drive Sales

Meet Uzi World Digital, Winner of Startups of the year 2024 in Kolkata, West Bengal, India

The Future of Tractable Deep Generative Models

How to Automate Supplier Site Creation in Oracle R12 Using APIs

Light-Mode

Classic

Newspaper

Dark-Mode

Neon Noir

Minty

HN StartUps