PO and IR Receipt Creation Program in Oracle Apps R12
Manual receiving can be especially challenging in high-volume operations, as it requires numerous clicks, increasing the likelihood of user fatigue and frequent errors. Whether you’re handling Purchase Orders (PO Receipt) or Internal Requisitions (IR Receipt), the process can be slow and prone to mistakes.
Let’s fix that.
In Oracle EBS R12, PO receipts record goods received from suppliers. IR receipts (from Internal Sales Orders) track inter-org transfers within your enterprise.
Why automating Receiving Process Needed?
- Save time
- Reduce human error
- Handle bulk loads
- Enforce business rules
- Improve compliance
This blog is your roadmap. We’ll walk through how to build a IR and PO receipt creation program in Oracle Apps R12.
You’ll learn:
- Key APIs and tables
- Design patterns
- Error handling
- Real-world considerations
Understanding the Standard Receiving Process in Oracle R12
Purchase Order Receiving (PO Receipt)
In standard Oracle, PO receiving is done through the Enter Receipts form or Mobile Supply Chain Applications (MSCA) handheld devices.
Key entities:
- PO Headers
- PO Lines
- PO Shipments (Line Locations)
Common transactions:
- Receive
- Deliver
- Return to Supplier
- Correction
Receiving affects Inventory, Costing, and Payables. The Receiving Open Interface (ROI) is the standard tool for automation.
Internal Requisition / Internal Sales Order Receiving (IR Receipt)
This process handles inter-org transfers. You receive goods transferred from another org via IR/ISO.
Key entities:
- Internal Requisition
- Internal Sales Order
- Shipment
Transactions are similar: Receive and Deliver.
Inventory updates occur in both the sending and receiving orgs. ROI supports this flow too.
When is a Custom Program Necessary?
Standard tools don’t always fit. You may need a custom program when:
- You’re processing thousands of transactions a day
- You have complex validation rules
- You need integration with 3PL or supplier portals
- You must enrich data before receipt (e.g., assign locators)
- Exceptions need auto-handling and notifications
- You need a custom UI beyond standard concurrent requests
Designing the Custom Receipt Creation Program
Scope Definition
Define what the program should handle:
- POs, IRs, or both?
- Standard POs, Blanket Releases, Internal Orders?
- Direct vs. Standard routing?
Support for:
- Lot and serial controlled items
- License Plate Numbers (LPNs)
- Inspection requirements (Accept/Reject)
- One-step or two-step receiving
Input Parameters / Data Source
Decide how data will enter the program:
- Manual parameters: PO number, IR number, Supplier, Date range
- Input file (CSV, XML)
- Staging table populated by other systems
Each receipt line must include:
- Item, Quantity, UOM
- PO/IR details (Line, Line Location)
- Subinventory, Locator
- Receipt Date
Technical Approach & Key Components
1. Data Validation Module
- Verify input parameters and data source
- Check PO/IR status and open quantity
- Validate item, UOM, org, subinventory, locator
- Cross-reference supplier/customer
- Apply business rules
2. Core Logic – Oracle Receiving Open Interfaces
Use the Receiving Open Interface (ROI):
- Tables: RCV_HEADERS_INTERFACE, RCV_TRANSACTIONS_INTERFACE
- Lot/Serial: MTL_TRANSACTION_LOTS_INTERFACE, MTL_SERIAL_NUMBERS_INTERFACE
Populate key columns:
- PROCESSING_STATUS_CODE, TRANSACTION_TYPE, AUTO_TRANSACT_CODE
- DOCUMENT_NUM, ITEM_ID, QUANTITY, UNIT_OF_MEASURE
- PO_LINE_ID, PO_LINE_LOCATION_ID, SHIPMENT_LINE_ID
Invoke the Receiving Receiving Transaction Processor concurrent program.
Avoid direct inserts into base tables(RCV_SHIPMENT_HEADERS, RCV_TRANSACTIONS). It’s risky and unsupported.
3. Error Handling and Logging Module
- Trap and log errors clearly
- Use custom log tables or FND_FILE
- Let users review/reprocess errors via interface status updates
4. Notification Module (Optional)
- Send email alerts for success/failure
- Use Oracle Workflow for approvals or escalations
5. Post-Processing (Optional)
- Trigger automatic putaway
- Update custom status tables
- Generate summary reports
Program Structure (Example: PL/SQL Concurrent Program)
Structure:
Procedures:
- Parameter validation
- Fetching data
- Looping through records
- Inserting into interface tables
- Submitting Receiving Transaction Processor
- Checking interface errors
- Logging results
Performance Tips
- Use bulk operations (FORALL)
- Optimize SQLs for data fetching
- Commit in batches
- Index key columns in staging tables
Development Steps & Code Snippets (Conceptual)
Setting up Concurrent Program
- Create Executable
- Define Program and Parameters
- Assign to a Request Group
You can review our article for defining PL/SQL Concurrent Program.
B. PL/SQL Snippets
Populate interface table:
SQL
INSERT INTO rcv_transactions_interface (
interface_transaction_id,
transaction_type,
processing_mode_code,
auto_transact_code,
po_header_id,
po_line_id,
quantity,
unit_of_measure,
shipment_line_id,
organization_id,
...
) VALUES (...);Submit Receiving Processor
Receiving Transaction Processor
l_request_id := fnd_request.submit_request
(application => 'PO',
program => 'RVCTP',
description => 'Receiving Transaction Processor',
request => FALSE,
argument1 => 'BATCH',
argument2 => p_group_id
);Check for errors:
SQL
SELECT * FROM po_interface_errors
WHERE interface_transaction_id = :txn_id;C. Lots and Serials
Insert lots:
mtl_transaction_lots_interface
INSERT INTO mtl_transaction_lots_interface (...) VALUES (...);Insert serials:
mtl_serial_numbers_interface
INSERT INTO mtl_serial_numbers_interface (...) VALUES (...);Testing Strategy
- Begin with unit tests for individual PL/SQL procedures/functions.
- Validate the program using various PO types like Standard and Blanket.
- Include IR transactions in your testing cycle.
- Ensure coverage for different item types—non-controlled, lot-controlled, and serial-controlled.
- Use valid and invalid data to trigger error handling. Test edge cases: partial vs. full receipt.
- Business users should validate scenarios during UAT. Run load tests for bulk data.
Deployment and Maintenance
- Migrate code(FNDLOAD) and definitions between environments
- Monitor using FND logs or custom tables
- Troubleshoot interface and performance issues
- Plan version upgrades and improvements
Best Practices
- Always use ROI – Oracle-supported and safe
- Secure the program – Assign the right responsibility
- Validate all input – Never trust unchecked data
- Design modular code – Easier to maintain and extend
- Document well – For current and future developers
- Understand downstream effects – Inventory, Costing, Payables
- Let Oracle handle commits – Your job: feed interface tables
Conclusion
A custom receipt creation program can simplify operations. It saves time, reduces errors, and enforces your business rules.
We’ve walked through scope, design, APIs, and testing. Use this as your blueprint.
Have questions or stories to share? Drop them in the comments below!
Appendix
Header Staging Tables
Receipt Header Staging Table
CREATE TABLE XXPO.XXPO_IR_RCPT_HDR_STG
( header_id NUMBER GENERATED ALWAYS AS IDENTITY ( START WITH 1000)
, message_id VARCHAR2(60)
, receipt_number VARCHAR2(30)
, warehouse VARCHAR2(3)
, ship_from_org VARCHAR2(25)
, order_type VARCHAR2(30)
, shipment_number VARCHAR2(30)
, inv_type VARCHAR2(30)
, receiver VARCHAR2(30)
, record_status VARCHAR2(30) DEFAULT ON NULL ('N')
, created_by NUMBER
, creation_date DATE
, last_updated_by NUMBER
, last_update_date DATE
, last_update_login NUMBER
, request_id NUMBER
);PO IR Receipt Lines Staging Table
Receipt Line Staging Table
CREATE TABLE XXPO.XXPO_IR_RCPT_LINE_STG
( header_id NUMBER
, line_id NUMBER GENERATED ALWAYS AS IDENTITY (START WITH 1000)
, poir_number NUMBER
, order_type VARCHAR2(30)
, order_line_number NUMBER
, received_quantity NUMBER
, item_number VARCHAR2(80)
, subinventory VARCHAR2(20)
, uom VARCHAR2(25)
, trx_type VARCHAR2(25)
, location_code VARCHAR2(35)
, transaction_date DATE
, shipment_line_number NUMBER
, receipt_action_type VARCHAR2(50)
, lot_number VARCHAR2(50)
, lot_expiry_date DATE
, record_status VARCHAR2(30) DEFAULT ON NULL ('N')
, created_by NUMBER
, creation_date DATE
, last_updated_by NUMBER
, last_update_date DATE
, last_update_login NUMBER
);PO IR Receipt Serial Staging Table
Receipt Serial Staging Table
CREATE TABLE XXPO.XXPO_IR_RCPT_SRN_STG
( header_id NUMBER
, line_id NUMBER
, order_line_number NUMBER
, shipment_line_number NUMBER
, serial_number VARCHAR2(50)
, record_status VARCHAR2(30) DEFAULT ON NULL ('N')
, created_by NUMBER
, creation_date DATE
, last_updated_by NUMBER
, last_update_date DATE
, last_update_login NUMBER
);Package Specification (Record Type Declaration)
Receipt Header Record
Receipt Header PL/SQL Record
TYPE rcpt_hdr_rec IS RECORD(
receipt_number VARCHAR2(30),
warehouse VARCHAR2(3),
ship_from_org VARCHAR2(25),
order_type VARCHAR2(30),
shipment_number VARCHAR2(30),
inv_type VARCHAR2(30),
receiver VARCHAR2(30),
header_id NUMBER,
organization_id NUMBER,
org_id NUMBER,
ship_from_org_id NUMBER,
vendor_id NUMBER,
vendor_site_id NUMBER
);PO Receipt Line Record Type
Receipt Line PL/SQL Record
TYPE rcpt_line_rec IS RECORD(
poir_number NUMBER,
order_type VARCHAR2(30),
order_line_number NUMBER,
received_quantity NUMBER,
item_number VARCHAR2(80),
subinventory VARCHAR2(20),
uom VARCHAR2(25),
trx_type VARCHAR2(25),
location_code VARCHAR2(35),
transaction_date DATE,
shipment_line_number NUMBER,
receipt_action_type VARCHAR2(50),
lot_number VARCHAR2(50),
lot_expiry_date DATE,
header_id NUMBER,
line_id NUMBER,
item_id NUMBER,
lot_control_code NUMBER,
serial_control_code NUMBER,
location_id NUMBER,
locator_id NUMBER,
po_header_id NUMBER,
po_line_id NUMBER,
po_line_location_id NUMBER,
po_distribution_id NUMBER,
req_header_id NUMBER,
requisition_line_id NUMBER,
req_distribution_id NUMBER,
shipment_line_id NUMBER
);
TYPE xxpo_rcpt_line_tbl IS TABLE OF rcpt_line_rec INDEX BY BINARY_INTEGER;PO Receipt Serial Record Type
Receipt Serial PL/SQL Record
TYPE rcpt_serial_rec IS RECORD(
order_line_number NUMBER,
shipment_line_number NUMBER,
serial_number VARCHAR2(50),
header_id NUMBER,
line_id NUMBER
);
TYPE xxpo_rcpt_serial_tbl IS TABLE OF rcpt_serial_rec INDEX BY BINARY_INTEGER;Cursors to Fetch Records from Staging
You can see SQL*Loader program load the data from external system into staging tables. Then use PL/SQL Cursors to fetch data from staging tables and load the PL/SQL Records/table type.
Create the cursor structure same as the define receipt record types above. Use the below code in the main procedure.
Cursor to fetch Receipt Header Record
--Cursor to fetch Header
CURSOR c_rcpt_headers
IS
SELECT receipt_number,
warehouse,
ship_from_org,
order_type,
shipment_number,
bol_number,
inv_type,
receiver,
header_id,
NULL organization_id,
NULL org_id,
NULL ship_from_org_id,
NULL vendor_id,
NULL vendor_site_id
FROM xxpo_ir_rcpt_hdr_stg hdr
WHERE warehouse = p_warehouse
AND record_status = 'N';
l_rcpt_header_rec c_rcpt_headers%ROWTYPE;
--
--Cursor to fetch Receipt Lines
CURSOR c_rcpt_lines(p_header_id IN NUMBER)
IS
SELECT poir_number,
order_type,
order_line_number,
received_quantity,
item_number,
subinventory,
uom,
trx_type,
location_code,
transaction_date,
shipment_line_number,
receipt_action_type,
lot_number,
lot_expiry_date,
header_id,
line_id,
- NULL item_id,
NULL lot_control_code,
NULL serial_control_cod,
NULL location_id,
NULL locator_id,
NULL po_header_id,
NULL po_line_id,
NULL po_line_location_id,
NULL po_distribution_id,
NULL req_header_id,
NULL requisition_line_id,
NULL req_distribution_id,
NULL shipment_line_id
FROM xxpo_ir_rcpt_line_stg
WHERE header_id = p_header_id
AND record_status != 'S';
l_rcpt_lines_rec c_rcpt_lines%ROWTYPE;
--
--Cursor to fetch serial deatils
CURSOR c_receipt_serials(p_header_id IN NUMBER)
IS
SELECT order_line_number,
shipment_line_number,
serial_number,
header_id,
line_id
FROM xxpo_ir_rcpt_srn_stg
WHERE header_id = p_header_id
AND record_status != 'S';
l_rcpt_serials_rec c_receipt_serials%ROWTYPE;
--
l_rcpt_lines_tbl xxpo_rcpt_line_tbl;
l_rcpt_serials_tbl xxpo_rcpt_serial_tbl;
l_rcpt_headers xxpo_rcpt_hdr_rec := NULL;ISO Shipment and Requisition Details Cursors
This section demonstrates how to retrieve internal requisition and ISO shipment data using PL/SQL cursors in Oracle Apps R12. These queries are crucial for tracking inter-org material transfers and validating procurement flow.
Fetch shipment information using requisition header
------------------------------------------------------------------
-- Cursor: c_shipment_info
-- Purpose: Fetch shipment information using requisition header ID, org ID, and shipment number
-------------------------------------------------------------------
CURSOR c_shipment_info(p_req_hdr_id IN NUMBER,
p_org_id IN NUMBER,
p_shipment_num IN VARCHAR2)
IS
SELECT rsh.shipment_num,
porl.to_person_id,
rsl.shipment_header_id,
rsl.from_organization_id
FROM po_requisition_lines_all porl,
rcv_shipment_headers rsh,
rcv_shipment_lines rsl
WHERE rsh.shipment_num = p_shipment_num
AND rsl.shipment_header_id = rsh.shipment_header_id
AND rsl.requisition_line_id = porl.requisition_line_id
AND porl.requisition_header_id = p_req_hdr_id
AND porl.org_id = p_org_id
AND ROWNUM = 1;
l_shipment_info c_shipment_info%ROWTYPE;
------------------------------------------------------------------
-- Cursor: c_req_details
-- Purpose: Get requisition and shipment line details using shipment header and requisition header
------------------------------------------------------------------
CURSOR c_req_details(p_ship_hdr_id IN NUMBER,
p_req_hdr_id IN NUMBER,
p_line_num IN NUMBER,
p_org_id IN NUMBER)
IS
SELECT rsl.shipment_header_id,
rsl.shipment_line_id,
rsl.requisition_line_id,
rsl.req_distribution_id,
porl.destination_organization_id,
porl.unit_meas_lookup_code,
rsl.quantity_shipped quantity,
porl.to_person_id,
porl.deliver_to_location_id,
rsl.quantity_received,
porl.line_num
FROM po_requisition_lines_all porl,
rcv_shipment_headers rsh,
rcv_shipment_lines rsl
WHERE rsh.shipment_header_id = p_ship_hdr_id
AND rsl.shipment_header_id = rsh.shipment_header_id
AND rsl.requisition_line_id = porl.requisition_line_id
AND porl.requisition_header_id = p_req_hdr_id
AND rsl.line_num = p_line_num
AND porl.org_id = p_org_id;
l_req_details c_req_details%ROWTYPE;Routing and Import Error Cursors
Here we define cursors to fetch direct delivery routing information and interface import errors. These are vital for debugging failed receipts and automating receipt routing processes in Oracle Receiving.
Fetch error messages and details from the PO interface error table for a group ID
-------------------------------------------------------------------
-- Cursor: c_routing_direct_delivery
-- Purpose: Get routing ID for 'Direct Delivery' route
-------------------------------------------------------------------
CURSOR c_routing_direct_delivery
IS
SELECT routing_header_id, 1 routing_step_id
FROM rcv_routing_headers
WHERE routing_name = 'Direct Delivery';
l_routing_direct_delivery c_routing_direct_delivery%ROWTYPE;
-------------------------------------------------------------------
-- Cursor: c_get_import_errs
-- Purpose: Fetch error messages and details from the interface error table for a group ID
-------------------------------------------------------------------
CURSOR c_get_import_errs(p_group_id IN NUMBER)
IS
SELECT pie.error_message,
pie.table_name,
pie.column_name,
rti.po_line_id,
rti.po_line_location_id,
rti.requisition_line_id,
rti.shipment_line_id
FROM po_interface_errors pie,
rcv_transactions_interface rti
WHERE pie.batch_id = p_group_id
AND pie.interface_line_id = rti.interface_transaction_id;Vendor and Internal Requisition Info Cursors
This block includes cursors for extracting supplier and internal requisition header details. These are useful when building validations, reports, or integrations with Oracle PO and Vendor Master Data.
Retrieve vendor and vendor site information for a given PO number and org
-------------------------------------------------------------------
-- Cursor: c_vendor_info
-- Purpose: Retrieve vendor and vendor site information for a given PO number and org
-------------------------------------------------------------------
CURSOR c_vendor_info(p_po_number IN VARCHAR2,
p_org_id IN NUMBER)
IS
SELECT pha.po_header_id,
pha.agent_id,
pvsa.vendor_id,
pvsa.vendor_site_id,
pha.authorization_status,
pha.cancel_flag
FROM po_headers_all pha,
po_vendors pv,
po_vendor_sites_all pvsa
WHERE pha.segment1 = p_po_number
AND pha.org_id = p_org_id
AND pv.vendor_id = pha.vendor_id
AND pvsa.vendor_id = pv.vendor_id
AND pvsa.vendor_site_id = pha.vendor_site_id
AND pv.enabled_flag = 'Y'
AND pvsa.purchasing_site_flag = 'Y'
AND pvsa.status = 'A';
l_vendor_rec c_vendor_info%ROWTYPE;
-------------------------------------------------------------------
-- Cursor: c_ir_info
-- Purpose: Fetch requisition header ID for internal requisitions
-------------------------------------------------------------------
CURSOR c_ir_info(p_ir_num IN VARCHAR2,
p_org_id IN NUMBER)
IS
SELECT porh.requisition_header_id
FROM po_requisition_headers_all porh
WHERE porh.type_lookup_code = 'INTERNAL'
AND porh.segment1 = p_ir_num
AND porh.org_id = p_org_id;
l_req_header_id po_requisition_headers_all.requisition_header_id%TYPE;PO Header and Receipt Quantity Cursors
This section provides cursors to retrieve PO header IDs and receipt quantities for validating shipment closures and exceptions in Oracle Purchasing. Ideal for monitoring procurement and receiving KPIs.
Get quantity and receipt status for PO lines and shipments
-------------------------------------------------------------------
-- Cursor: c_po_header
-- Purpose: Fetch PO header ID using PO number and org
-------------------------------------------------------------------
CURSOR c_po_header(p_po_number IN VARCHAR2,
p_org_id IN NUMBER)
IS
SELECT po_header_id
FROM po_headers_all
WHERE segment1 = p_po_number
AND org_id = p_org_id;
-------------------------------------------------------------------
-- Cursor: c_po_rcv_qty
-- Purpose: Get quantity and receipt status for PO lines and shipments
-------------------------------------------------------------------
CURSOR c_po_rcv_qty(p_po_hdr_id IN NUMBER,
p_line_num IN VARCHAR2,
p_shipment_num IN VARCHAR2)
IS
SELECT pol.quantity,
poll.closed_code,
poll.quantity_received,
poll.cancel_flag,
poll.qty_rcv_exception_code,
poll.qty_rcv_tolerance
FROM po_lines_all pol,
po_line_locations_all poll
WHERE pol.po_header_id = p_po_hdr_id
AND pol.line_num = p_line_num
AND poll.po_header_id = pol.po_header_id
AND poll.po_line_id = pol.po_line_id
AND poll.shipment_num = NVL(p_shipment_num, poll.shipment_num);
l_po_rcv_qty c_po_rcv_qty%ROWTYPE;Procedure for IR & PO Receipt – validate_and_import
PO & IR Receipt Open Interface Validation procedure
-- Validate Receipt Number
IF l_receipt_hdr_rec.receipt_number IS NULL THEN
l_error_code := 'E';
l_error_msg := 'Receipt number is NULL';
END IF;
-- Validate Warehouse
IF l_org_code IS NULL THEN
l_error_code := 'E';
g_error_code := l_error_code;
l_error_msg := 'Warehouse IS NULL';
ELSE
BEGIN
SELECT organization_id, operating_unit, set_of_books_id
INTO l_organization_id, l_org_id, l_sob_id
FROM org_organization_definitions
WHERE organization_code = l_org_code;
l_receipt_hdr_rec.organization_id := l_organization_id;
l_receipt_hdr_rec.org_id := l_org_id;
EXCEPTION
WHEN OTHERS THEN
l_error_code := 'E';
g_error_code := l_error_code;
l_error_msg := 'INVALID ORG';
END;
END IF;
-- Validate Ship From Org
IF l_receipt_hdr_rec.ship_from_org IS NOT NULL THEN
BEGIN
SELECT organization_id
INTO l_ship_from_org_id
FROM org_organization_definitions
WHERE organization_code = l_receipt_hdr_rec.ship_from_org;
l_receipt_hdr_rec.ship_from_org_id := l_ship_from_org_id;
EXCEPTION
WHEN OTHERS THEN
l_error_code := 'E';
g_error_code := l_error_code;
l_error_msg := 'INVALID SHIP_FROM_ORG';
END;
END IF;
-- Validate Order Type
IF l_receipt_hdr_rec.order_type IS NULL THEN
l_error_code := 'E';
g_error_code := l_error_code;
l_error_msg := 'Order Type is NULL';
ELSE
IF l_receipt_hdr_rec.order_type NOT IN ('PO', 'IR') THEN
l_error_code := 'E';
g_error_code := l_error_code;
l_error_msg := 'INVALID Order Type ' || l_receipt_hdr_rec.order_type;
END IF;
END IF;
-- Validate Shipment Number
IF l_receipt_hdr_rec.shipment_number IS NULL
THEN
l_error_code := 'E';
g_error_code := l_error_code;
l_error_msg := 'Shipment Number is NULL';
ELSE
IF l_receipt_hdr_rec.order_type = 'PO'
THEN
l_po_number := l_receipt_line_tbl(1).poir_number;
OPEN c_vendor_info(l_po_number, l_receipt_hdr_rec.org_id);
FETCH c_vendor_info INTO l_vendor_rec;
CLOSE c_vendor_info;
l_receipt_hdr_rec.vendor_id := l_vendor_rec.vendor_id;
l_receipt_hdr_rec.vendor_site_id := l_vendor_rec.vendor_site_id;
l_receipt_source_code := g_receipt_source_code_po;
l_source_document_code := g_source_document_code_po;
l_ir_number := NULL;
IF NVL(l_vendor_rec.authorization_status, 'INCOMPLETE')
<> 'APPROVED' THEN
l_error_code := 'E';
g_error_code := l_error_code;
l_error_msg := 'Invalid authorization status';
END IF;
IF NVL(l_vendor_rec.cancel_flag, 'N') <> 'N'
THEN
l_error_code := 'E';
g_error_code := l_error_code;
l_error_msg := 'PO Cancelled';
END IF;
ELSIF l_receipt_hdr_rec.order_type = 'IR'
THEN
l_ir_number := l_receipt_line_tbl(1).poir_number;
l_po_number := NULL;
OPEN c_ir_info(l_ir_number, l_org_id);
FETCH c_ir_info INTO l_req_header_id;
CLOSE c_ir_info;
OPEN c_shipment_info(l_req_header_id, l_org_id, l_receipt_hdr_rec.shipment_number);
FETCH c_shipment_info INTO l_shipment_info;
CLOSE c_shipment_info;
l_receipt_source_code := g_receipt_source_code_ir;
l_source_document_code := g_source_document_code_ir;
l_receipt_hdr_rec.ship_from_org_id := l_shipment_info.from_organization_id;
ELSE
l_po_number := NULL;
l_ir_number := NULL;
END IF;
END IF;
-- Check for Duplicate Receipt Number
IF l_receipt_hdr_rec.receipt_number IS NOT NULL
THEN
BEGIN
SELECT 'Y'
INTO l_rcpt_num
FROM rcv_shipment_headers
WHERE receipt_num = l_receipt_hdr_rec.receipt_number
AND receipt_source_code = 'VENDOR';
l_error_code := 'E';
g_error_code := l_error_code;
l_error_msg := 'Duplicate Receipt Number';
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END IF;
-- Validate Inventory Type
IF l_vendor_rec.vendor_id IS NULL AND l_req_header_id IS NULL
THEN
l_error_code := 'E';
g_error_code := l_error_code;
l_error_msg := 'Invalid Order Number';
END IF;
g_error_code := 'S';
-- Update Staging
l_header_id := l_receipt_hdr_rec.header_id;
update_staging(
p_header_id => l_receipt_hdr_rec.header_id,
p_error_code => l_error_code,
p_error_msg => l_error_msg,
p_record_type => 'Header'
);
-- Final Error Flag for Header
IF l_error_code = 'E' THEN
l_error_record := 'Header';
END IF;PO Receipt Iine Validation
PO IR REceipt Interface Line Validation
FOR i in 1..l_receipt_line_tbl.COUNT
LOOP
l_error_msg := NULL;
g_line_id := l_receipt_line_tbl(i).line_id;
l_item := l_receipt_line_tbl(i).item_number;
l_line_number := l_receipt_line_tbl(i).order_line_number;
l_ship_line_num := l_receipt_line_tbl(i).shipment_line_number;
l_serial_qty := 0;
l_shp_serials_qty := 0;
l_shp_serials_cnt := 0;
--Validate PO/IR Number
IF l_receipt_hdr_rec.order_type = 'PO'
THEN
l_po_number := l_receipt_line_tbl(i).poir_number;
OPEN c_po_header(l_po_number, l_org_id);
FETCH c_po_header INTO l_po_header_id;
CLOSE c_po_header;
l_receipt_line_tbl(i).po_header_id := l_po_header_id;
IF l_po_header_id IS NULL
THEN
l_error_code := 'E';
g_error_code := l_error_code;
l_error_msg := 'INVALID PO Number';
END IF;
ELSE
l_ir_number := l_receipt_line_tbl(i).poir_number;
OPEN c_ir_info(l_ir_number, l_org_id);
FETCH c_ir_info INTO l_req_header_id;
CLOSE c_ir_info;
l_receipt_line_tbl(i).req_header_id := l_req_header_id;
IF l_req_header_id IS NULL
THEN
l_error_code := 'E';
g_error_code := l_error_code;
l_error_msg := 'INVALID IR Number';
END IF;
END IF;
--Validate PO Num, Line_Num, Item, Quantity
IF l_receipt_hdr_rec.order_type = 'PO'
THEN
BEGIN
SELECT 'Y'
INTO l_po_line_item_comb_val
FROM po_headers_all pha,
po_lines_all pla,
po_line_locations_all plla
WHERE pla.po_header_id = pha.po_header_id
AND plla.po_header_id = pla.po_header_id
AND plla.po_line_id = pla.po_line_id
AND pha.org_id = l_org_id
AND pha.po_header_id= l_po_header_id
AND pla.line_num = l_receipt_line_tbl(i).order_line_number
AND pla.item_id = l_receipt_line_tbl(i).item_id
AND ROWNUM =1;
EXCEPTION
WHEN OTHERS THEN
l_error_code := 'E';
g_error_code := l_error_code;
l_error_msg := 'Invalid PO, Line, Item Combination';
END;
ELSE
BEGIN
SELECT 'Y'
INTO l_po_line_item_comb_val
FROM po_requisition_lines_all porl,
rcv_shipment_headers rsh,
rcv_shipment_lines rsl
WHERE rsh.shipment_header_id = l_shipment_info.shipment_header_id
AND rsl.shipment_header_id = rsh.shipment_header_id
AND rsl.requisition_line_id = porl.requisition_line_id
AND porl.requisition_header_id =l_req_header_id
AND rsl.line_num = l_receipt_line_tbl(i).order_line_number
AND rsl.item_id = l_receipt_line_tbl(i).item_id
AND porl.org_id = l_org_id
;
EXCEPTION
WHEN OTHERS THEN
l_error_code := 'E';
g_error_code := l_error_cod
l_error_msg := 'Invalid ASN, Line Number and Item Combination';
END;
END
update_staging(p_header_id => l_receipt_line_tbl(i).header_id,
p_line_id => l_receipt_line_tbl(i).line_id,
p_error_code => l_error_code,
p_error_msg => l_error_msg,
p_record_type => 'Line'
);
g_procedure_name := l_procedure_name;
IF l_error_code = g_error_code_e AND l_error_record IS NULL
THEN
l_error_record := 'Line';
END IF;
--
IF l_serial_control != 1
THEN
FOR j in 1..l_receipt_serial_tbl.COUNT
LOOP
IF l_receipt_serial_tbl(j).order_line_number = l_line_number
THEN
l_error_msg := NULL;
--Populate data into serial interface table
IF l_receipt_hdr_rec.order_type = 'IR'
THEN
SELECT COUNT(1)
INTO l_shp_serials_cnt
FROM rcv_serials_supply
WHERE supply_type_code = 'SHIPMENT'
AND shipment_line_id = l_req_details.shipment_line_id
AND serial_num = l_receipt_serial_tbl(j).serial_number;
IF l_shp_serials_cnt = 1
THEN
l_shp_serials_qty := l_shp_serials_qty + 1;
END IF;
END IF;
l_serial_qty := l_serial_qty + 1;
l_receipt_line_tbl(i).serial_control_code := 2;
END IF;
END LOOP;
IF l_receipt_hdr_rec.order_type = 'PO' AND l_serial_control = 6
THEN
l_serial_qty := l_receipt_line_tbl(i).received_quantity;
END IF;
--Validate Serial Number Qty
IF l_serial_qty != l_receipt_line_tbl(i).received_quantity
THEN
l_error_code := 'E';
g_error_code := l_error_code;
l_error_msg := 'INVALID SERIAL QTY';
END IF;
IF l_receipt_hdr_rec.order_type = 'IR' AND l_serial_qty != l_shp_serials_qty
THEN
l_error_code := 'E';
g_error_code := l_error_code;
l_error_msg := 'Provided serials are not valid for this shipment';
END IF;
update_staging(p_header_id => l_receipt_line_tbl(i).header_id,
p_line_id => l_receipt_line_tbl(i).line_id,
p_error_code => l_error_code,
p_error_msg => l_error_msg,
p_record_type => 'Serial'
);
IF l_error_code = g_error_code_e AND l_error_record IS NULL
THEN
l_error_record := 'Serial';
END IF;
END IF;
--
l_txn_id := l_header_id;
--
--Populate Receiving interface record
IF l_error_code != g_error_code_e
THEN
SELECT rcv_interface_groups_s.NEXTVAL INTO l_group_id FROM dual;
SELECT rcv_headers_interface_s.NEXTVAL INTO l_rcpt_hdr_intf_id FROM dual;
l_shipment_num := l_shipment_info.shipment_num;
l_rcv_hdr_interface.header_interface_id := l_rcpt_hdr_intf_id;
l_rcv_hdr_interface.group_id := l_group_id;
l_rcv_hdr_interface.processing_status_code := 'PENDING';
l_rcv_hdr_interface.receipt_source_code := l_receipt_source_code;
l_rcv_hdr_interface.transaction_type := g_header_transaction_type;
l_rcv_hdr_interface.last_update_date := sysdate;
l_rcv_hdr_interface.last_updated_by := l_user_id;
l_rcv_hdr_interface.creation_date := sysdate;
l_rcv_hdr_interface.created_by := l_user_id;
l_rcv_hdr_interface.shipment_num := l_shipment_num;
l_rcv_hdr_interface.receipt_num := l_receipt_hdr_rec.receipt_number;
l_rcv_hdr_interface.vendor_id := l_vendor_rec.vendor_id;
l_rcv_hdr_interface.vendor_site_id := l_vendor_rec.vendor_site_id;
l_rcv_hdr_interface.auto_transact_code := g_auto_transact_code;
IF l_receipt_hdr_rec.order_type = 'IR'
THEN
l_rcv_hdr_interface.from_organization_id := l_receipt_hdr_rec.ship_from_org_id;
END IF;
l_rcv_hdr_interface.employee_id := NVL(l_vendor_rec.agent_id, l_shipment_info.to_person_id) ;
l_rcv_hdr_interface.ship_to_organization_id := l_organization_id;
l_rcv_hdr_interface.shipped_date := sysdate;
l_rcv_hdr_interface.expected_receipt_date := sysdate;
l_rcv_hdr_interface.validation_flag := 'Y';
--Populating Receiving Headers Interface Table
populate_rcv_hdr_intf(p_rcv_hdr_int_rec => l_rcv_hdr_interface,
x_error_code => l_error_code,
x_error_msg => l_error_msg
);
g_procedure_name := l_procedure_name;
END IF;
l_txn_id := NVL(l_rcpt_hdr_intf_id, l_txn_id);
--
--Populate inventory transaction interface record
IF l_error_code != g_error_code_e
THEN
FOR i in 1..l_receipt_line_tbl.COUNT
LOOP
SELECT rcv_transactions_interface_s.NEXTVAL
INTO l_rcpt_txn_intf_id
FROM dual;
l_line_number := l_receipt_line_tbl(i).order_line_number;
l_ship_line_num := l_receipt_line_tbl(i).shipment_line_number;
IF l_receipt_line_tbl(i).trx_type != 'CORRECT'
THEN
l_parent_transaction_id := NULL;
l_shipment_line_id := NULL;
l_shipment_header_id := NULL;
END IF;
--Get PO line and Shipment Information
IF l_receipt_hdr_rec.order_type = 'PO'
THEN
OPEN c_routing_direct_delivery;
FETCH c_routing_direct_delivery INTO l_routing_direct_delivery;
CLOSE c_routing_direct_delivery;
OPEN c_po_line(l_receipt_line_tbl(i).po_header_id, l_line_number, l_ship_line_num);
FETCH c_po_line INTO l_po_line_rec;
CLOSE c_po_line;
l_receipt_line_tbl(i).po_line_id := l_po_line_rec.po_line_id;
l_receipt_line_tbl(i).po_line_location_id := l_po_line_rec.line_location_id;
l_receipt_line_tbl(i).po_distribution_id := l_po_line_rec.po_distribution_id;
ELSE
OPEN c_req_details(l_shipment_info.shipment_header_id, l_receipt_line_tbl(i).req_header_id, l_line_number, l_org_id);
FETCH c_req_details INTO l_req_details;
CLOSE c_req_details;
l_shipment_header_id := l_req_details.shipment_header_id;
l_shipment_line_id := l_req_details.shipment_line_id;
l_receipt_line_tbl(i).requisition_line_id := l_req_details.requisition_line_id;
l_receipt_line_tbl(i).req_distribution_id := l_req_details.req_distribution_id;
END IF;
l_rcv_trx_interface.interface_transaction_id := l_rcpt_txn_intf_id;
l_rcv_trx_interface.group_id := l_group_id;
l_rcv_trx_interface.last_update_date := SYSDATE;
l_rcv_trx_interface.last_updated_by := l_user_id;
l_rcv_trx_interface.creation_date := SYSDATE;
l_rcv_trx_interface.created_by := l_user_id;
l_rcv_trx_interface.transaction_type := g_line_transaction_type;
l_rcv_trx_interface.transaction_date := SYSDATE;
l_rcv_trx_interface.processing_status_code := 'PENDING';
l_rcv_trx_interface.processing_mode_code := g_processing_mode_code;
l_rcv_trx_interface.transaction_status_code := 'PENDING';
l_rcv_trx_interface.quantity := l_receipt_line_tbl(i).received_quantity;
l_rcv_trx_interface.unit_of_measure := NVL(l_po_line_rec.unit_meas_lookup_code, l_req_details.unit_meas_lookup_code);
l_rcv_trx_interface.auto_transact_code := g_auto_transact_code;
l_rcv_trx_interface.destination_type_code := g_destination_type_inv;--added
l_rcv_trx_interface.primary_unit_of_measure := l_receipt_line_tbl(i).uom;
l_rcv_trx_interface.receipt_source_code := l_receipt_source_code;
l_rcv_trx_interface.to_organization_id := NVL(l_po_line_rec.ship_to_organization_id, l_req_details.destination_organization_id);
l_rcv_trx_interface.location_id := l_receipt_line_tbl(i).location_id;
l_rcv_trx_interface.source_document_code := l_source_document_code;
l_rcv_trx_interface.po_header_id := l_receipt_line_tbl(i).po_header_id;
l_rcv_trx_interface.po_line_id := l_po_line_rec.po_line_id;
l_rcv_trx_interface.po_line_location_id := l_po_line_rec.line_location_id;
l_rcv_trx_interface.po_distribution_id := l_po_line_rec.po_distribution_id;
l_rcv_trx_interface.item_id := l_receipt_line_tbl(i).item_id;
l_rcv_trx_interface.expected_receipt_date := l_receipt_line_tbl(i).transaction_date;
l_rcv_trx_interface.header_interface_id := l_rcpt_hdr_intf_id;
l_rcv_trx_interface.item_num := l_receipt_line_tbl(i).item_number;
l_rcv_trx_interface.document_num := l_receipt_line_tbl(i).poir_number;
l_rcv_trx_interface.ship_to_location_code := NULL;
l_rcv_trx_interface.validation_flag := g_validation_flag;
--l_rcv_trx_interface.quantity_invoiced := 0;
l_rcv_trx_interface.subinventory := l_subinv_code;
IF l_receipt_hdr_rec.order_type = 'IR'
THEN
l_rcv_trx_interface.document_line_num := l_req_details.line_num;
ELSE
l_rcv_trx_interface.document_line_num := l_receipt_line_tbl(i).order_line_number;
l_rcv_trx_interface.routing_header_id := l_routing_direct_delivery.routing_header_id;
l_rcv_trx_interface.routing_step_id := l_routing_direct_delivery.routing_step_id;
END IF;
l_rcv_trx_interface.document_shipment_line_num:= l_receipt_line_tbl(i).shipment_line_number;
l_rcv_trx_interface.locator_id := l_receipt_line_tbl(i).locator_id;
l_rcv_trx_interface.vendor_id := l_vendor_rec.vendor_id;
l_rcv_trx_interface.vendor_site_id := l_vendor_rec.vendor_site_id;
l_rcv_trx_interface.org_id := l_org_id;
l_rcv_trx_interface.parent_transaction_id := l_parent_transaction_id;
l_rcv_trx_interface.shipment_header_id := l_shipment_header_id;
l_rcv_trx_interface.shipment_line_id := l_shipment_line_id;
l_rcv_trx_interface.requisition_line_id := l_req_details.requisition_line_id;
l_rcv_trx_interface.req_distribution_id := l_req_details.req_distribution_id;
--
--Populating Receiving Transaction Interface Table
populate_rcv_trx_intf(p_rcv_trx_int_rec => l_rcv_trx_interface,
x_error_code => l_error_code,
x_error_msg => l_error_msg
);
g_procedure_name := l_procedure_name;
IF l_receipt_line_tbl(i).lot_control_code = 2 OR l_receipt_line_tbl(i).serial_control_code != 1
THEN
l_serial_temp_seq := mtl_material_transactions_s.NEXTVAL;
END IF;
IF l_receipt_line_tbl(i).lot_control_code = 2
THEN
l_lot_interface.product_code := g_interface_product_code;
l_lot_interface.transaction_interface_id := mtl_material_transactions_s.NEXTVAL;
l_lot_interface.product_transaction_id := l_rcpt_txn_intf_id;
--l_lot_interface.source_code := l_receipt_source_code;
l_lot_interface.lot_number := l_receipt_line_tbl(i).lot_number;
l_lot_interface.lot_expiration_date := l_receipt_line_tbl(i).lot_expiry_date;
l_lot_interface.transaction_quantity := l_receipt_line_tbl(i).received_quantity;
l_lot_interface.c_attribute1 := l_receipt_line_tbl(i).item_id;
IF l_receipt_line_tbl(i).serial_control_code != 1 AND l_receipt_hdr_rec.order_type = 'IR'
THEN
l_lot_interface.serial_transaction_temp_id:= l_serial_temp_seq;
ELSE
l_lot_interface.serial_transaction_temp_id:= NULL;
END IF;
l_lot_interface.last_update_date := SYSDATE;
l_lot_interface.last_updated_by := l_user_id;
l_lot_interface.creation_date := SYSDATE;
l_lot_interface.created_by := l_user_id;
--
--Call populate_lot_intf to insert into lots transaction interface table
populate_lot_intf(p_lot_intf_rec => l_lot_interface,
x_error_code => l_error_code,
x_error_msg => l_error_msg
);
g_procedure_name := l_procedure_name;
END IF;
l_serial_count := 0;
IF l_receipt_line_tbl(i).serial_control_code != 1 AND l_receipt_serial_tbl.COUNT > 0
THEN
FOR j in 1..l_receipt_serial_tbl.COUNT
LOOP
IF l_receipt_serial_tbl(j).order_line_number = l_receipt_line_tbl(i).order_line_number
THEN
l_serial_interface.transaction_interface_id := l_serial_temp_seq;
l_serial_interface.product_transaction_id := l_rcpt_txn_intf_id;
l_serial_interface.product_code := g_interface_product_code;
l_serial_interface.fm_serial_number := l_receipt_serial_tbl(j).serial_number;
l_serial_interface.to_serial_number := l_receipt_serial_tbl(j).serial_number;
l_serial_interface.last_update_date := SYSDATE;
l_serial_interface.last_updated_by := l_user_id;
l_serial_interface.creation_date := SYSDATE;
l_serial_interface.created_by := l_user_id;
--
--Call populate_serial_intf to insert into serial transaction interface table
populate_serial_intf(p_serial_intf_rec => l_serial_interface,
x_error_code => l_error_code,
x_error_msg => l_error_msg
);
l_serial_count := l_serial_count + 1;
END IF;
END LOOP;
END IF;
END LOOP;
END IF;Now call Receiving Transaction Processor to process the IR/PO Receipt records from interface tables.
PL/SQL Package for IR & PO Receipt:
main procedure PO/IR Receipt Import
FOR rec_rcpt_headers IN c_rcpt_headers
LOOP
--Fetching Receipt Lines
OPEN c_rcpt_lines(rec_rcpt_headers.header_id);
FETCH c_rcpt_lines BULK COLLECT INTO l_rcpt_lines_tbl;
CLOSE c_rcpt_lines;
--
--Fetching Receipt Serials
OPEN c_receipt_serials(rec_rcpt_headers.header_id);
FETCH c_receipt_serials BULK COLLECT INTO l_rcpt_serials_tbl;
CLOSE c_receipt_serials;
validate_and_import
(p_rcpt_hdr => rec_rcpt_headers,
p_rcpt_line => l_rcpt_lines_tbl,
p_rcpt_serial => l_rcpt_serials_tbl,
x_error_code => l_error_code,
x_error_msg => l_error_msg
);
END IF;Useful SQL:
SQL
SELECT * FROM po_interface_errors
WHERE interface_transaction_id = :txn_id;
SELECT * FROM rcv_transactions_interface
WHERE processing_status_code = 'ERROR';
Pingback: PO Tables in Oracle Apps R12: A Complete Guide