
Mastering Oracle Inventory Transaction APIs: A Practical Guide
In Oracle E-Business Suite (Oracle EBS) the Inventory module stands as the central hub for all item stock quantities and stock movements. Automating inventory transactions is crucial for building robust integrations and streamlining business processes. At the heart of this automation are Inventory API in Oracle Apps R12 the Oracle’s powerful PL/SQL APIs.
These APIs provide a controlled and validated way to perform operations that you would otherwise do manually through the application forms. Consequently, understanding how to use them is essential for everything from custom interfaces to large-scale data migrations.
This guide breaks down the most common Oracle Inventory transactions APIs into simple, understandable explanations. We will explore what each API does, when to use it, and how it fits into the broader inventory ecosystem. Learning Inventory tables helps you understand how Oracle Inventory data is organized and processed.
1. Inventory Master Data Processing
You can create, update and assign inventory items using inventory api as well. You can create item category, category set using inventory APIs.
2. The Core Transaction Engine: INV_TXN_MANAGER_PUB.PROCESS_TRANSACTIONS
First and foremost, think of INV_TXN_MANAGER_PUB.PROCESS_TRANSACTIONS as the workhorse of Inventory API in Oracle Apps R12. This single, powerful API handles a wide variety of common inventory events. You use this API by first inserting your transaction data into a inventory interface table MTL_TRANSACTIONS_INTERFACE. Next you need to insert Lot and Serial Interface Tables. Then, you invoke the API to validate and process these records into the base transaction table MTL_MATERIAL_TRANSACTIONS.
- Primary Use: Performing miscellaneous receipts, miscellaneous issues, cycle count adjustments, and subinventory transfers, inter-org transfer.
- Key Insight: This API is your go-to for most basic inventory adjustments that don’t involve complex fulfillment or planning workflows.
3. Inventory API for Managing Internal Movement: Move Order API
When you need to manage the internal movement of goods, you will work with the Move Order APIs of Inventory API in Oracle Apps R12. This process typically involves two main steps.
First, you create a formal request for an item transfer using INV_MOVE_ORDER_PUB.CREATE_MOVE_ORDER. This API registers the need to move a certain quantity from one stock location to another, populating the MTL_TXN_REQUEST_HEADERS and MTL_TXN_REQUEST_LINES tables.
Next, after creating the move order, you must process it. The INV_REPLENISH_DETAIL_PUB.line_details_pub API allocates the move order and creating the detailed allocations required to complete the transfer. This step is often a prerequisite for the final pick release and shipping of items(automatically done by pick release process).
4. Pick Release Sales Orders: Pick Release API
You can call wsh_deliveries_pub.delivery_action API. Pick Release API can handle few activities like creating Move order line, allocate the move order, and change the sales order line status to “Released to warehouse”. You don’t need to use INV_MOVE_ORDER_PUB explicitly in this case.
5. Fulfilling Sales Orders: Pick Confirm API
After the sales order status changed to “Released to warehouse” user can ask the warehouse to start the fulfillment process. During the sales order fulfillment process, confirming the physical pick is a critical step. The INV_PICK_WAVE_PICK_CONFIRM_PUB.PICK_CONFIRM API of Inventory API in Oracle Apps R12 handles this task. It updates the system to reflect that items have been physically picked from their subinventory to be shipped. This action directly impacts shipping tables like WSH_DELIVERY_DETAILS and is a key integration point for order management systems.
6. Reserving and Querying Stock
Before you can transact, you often need to check or reserve available inventory. Oracle provides specific Inventory API in Oracle Apps R12 for these important functions.
To “earmark” or “soft-allocate” stock for a specific demand, such as a sales order or a work-in-progress job, you use INV_RESERVATION_PUB.CREATE_RESERVATION. This API creates a record in the MTL_RESERVATIONS table, preventing that quantity from being used by other demands.
For a simple, real-time check of on-hand stock, the INV_QUANTITY_TREE_PUB.QUERY_QUANTITIES Inventory API in Oracle Apps R12 is the perfect tool. You can get on hand quantities through this API. It efficiently retrieves the current on-hand quantity for an item within an organization. Since this is a read-only API, it is fast and safe to use in validations and custom reporting without impacting performance. You can release or delete reservation in case of that stock is required by any other urgent purpose.
7. Keeping Stock Levels Accurate: Adjustment and Count APIs
Maintaining accurate inventory records is vital. Oracle provides another Inventory API in Oracle Apps R12 to help with adjustments from physical and cycle counts.
Following a complete physical inventory audit, you use INV_PHYSICAL_ADJUST_PUB.PROCESS_PHYSICAL_ADJUST to post any variances. This API formalizes the adjustments needed to align your system’s on-hand quantity with the actual physical count.
For more frequent, periodic validations, you will use cycle counts. The Cycle Count Interface tables processes cycle count entries and posts the necessary adjustments, ensuring your inventory remains accurate over time.
8. Advanced and Utility APIs: Inventory API in Oracle Apps R12
Beyond the daily transactions, several other APIs serve crucial roles in planning and error correction.
- Automated Replenishment: The Auto Replishment is a key component of min-max planning automation. It helps you automatically generate requisitions to replenish stock in a subinventory when it falls below a certain level.
- Supply Chain Planning: For integrations with advanced planning tools like ASCP, the create supply is used to create expected supply records (e.g., from purchase orders or requisitions), which planning engines use for their calculations.
- Correcting Mistakes: If a transaction was posted in error, reverse transaction provides a way to cancel it. This process finds the original record in MTL_MATERIAL_TRANSACTIONS and creates a corresponding reversal transaction.
Quick Reference: Inventory API in Oracle Apps R12
Here is a summary table that connects each major API to its common use case and the primary database table it impacts.
API Name | Common Use Case | Core Table Impacted |
---|---|---|
PROCESS_TRANSACTIONS | Miscellaneous & Subinventory Txn | MTL_TRANSACTIONS_INTERFACE |
PROCESS_MOVE_ORDER | Subinventory Move Request | MTL_TXN_REQUEST_HEADERS |
LINE_DETAILS_PUB | Alllocations of Move Orders | MTL_MATERIAL_TRANSACTIONS |
CREATE_RESERVATION | Sales Order & WIP Reservations | MTL_RESERVATIONS |
QUERY_QUANTITIES | Real-time On-hand Check | Read-only from inventory tables |
PICK_CONFIRM | Sales Order Shipment Picking | WSH_DELIVERY_DETAILS |
PICK_RELEASE | Delivery Pick Release | |
Physical Inventory Adjustments | Physical Inventory Adjustments | MTL_MATERIAL_TRANSACTIONS |
Cycle Count Adjustments | Cycle Count Adjustments | MTL_CYCLE_COUNTS |
DELETE_RESERVATION | Delete a Reservation | MTL_RESERVATIONS |
Conclusion: Inventory API in Oracle Apps R12
Ultimately, mastering these Oracle Inventory APIs empowers you to create seamless, automated, and efficient solutions. By using these standard interfaces, you ensure data integrity, adhere to business rules, and unlock the full potential of the Oracle Inventory module. Whether you are building a simple integration or a complex warehouse management extension, these APIs are the foundational building blocks for success. You also explore TCA APIs for learning TCA APIs.
Inventory API in Oracle Apps R12 – Reference
1. 🟩 INV_TXN_MANAGER_PUB.PROCESS_TRANSACTIONS – Miscellaneous Receipt
PROCEDURE populate_mti_proc(p_trx_rec IN mtl_transactions_interface%ROWTYPE)
IS
l_txn_intf_rec mtl_transactions_interface%ROWTYPE := p_txn_intf_rec;
l_error_code VARCHAR2(10) := g_error_code_s;
BEGIN
DBMS_OUTPUT.PUT_LINE('Inserting data into transaction interface tables');
INSERT
INTO mtl_transactions_interface
(
transaction_uom,
transaction_date,
source_code,
source_line_id,
source_header_id,
process_flag,
transaction_mode,
lock_flag,
locator_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
inventory_item_id,
subinventory_code,
organization_id,
transaction_source_name,
transaction_source_id,
transaction_quantity,
primary_quantity,
transaction_type_id,
transaction_interface_id,
transaction_header_id,
transaction_reference,
revision,
reason_id,
transfer_organization,
transfer_subinventory,
transfer_locator
)
VALUES(
l_txn_intf_rec.transaction_uom,
l_txn_intf_rec.transaction_date,
l_txn_intf_rec.source_code,
l_txn_intf_rec.source_line_id,
l_txn_intf_rec.source_header_id,
l_txn_intf_rec.process_flag,
l_txn_intf_rec.transaction_mode,
l_txn_intf_rec.lock_flag,
l_txn_intf_rec.locator_id,
l_txn_intf_rec.last_update_date,
l_txn_intf_rec.last_updated_by,
l_txn_intf_rec.creation_date,
l_txn_intf_rec.created_by,
l_txn_intf_rec.inventory_item_id,
l_txn_intf_rec.subinventory_code,
l_txn_intf_rec.organization_id,
l_txn_intf_rec.transaction_source_name,
l_txn_intf_rec.transaction_source_id,
l_txn_intf_rec.transaction_quantity,
l_txn_intf_rec.primary_quantity,
l_txn_intf_rec.transaction_type_id ,
l_txn_intf_rec.transaction_interface_id,
l_txn_intf_rec.transaction_header_id,
l_txn_intf_rec.transaction_reference,
l_txn_intf_rec.revision,
l_txn_intf_rec.reason_id,
l_txn_intf_rec.transfer_organization,
l_txn_intf_rec.transfer_subinventory,
l_txn_intf_rec.transfer_locator
);
COMMIT;
DBMS_OUTPUT.PUT_LINE('Records inserted into transaction interface tables');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Main exception '||SQLERRM);
END populate_mti_proc;
Populate mtl_transactions_interface tableDECLARE
l_tnx_rec mtl_transactions_interface%ROWTYPE;
l_ctrl_rec transaction_control_rec_type;
l_txn_type_id NUMBER;
l_return_status VARCHAR2(1);
l_msg_count NUMBER;
l_msg_data VARCHAR2(4000);
l_transaction_id NUMBER;
BEGIN
-- Populate transaction record
l_tnx_rec.source_code := 'Manual';
l_tnx_rec.transaction_type_id := 2; -- Misc Receipt
l_tnx_rec.transaction_date := SYSDATE;
l_tnx_rec.inventory_item_id := 1001;
l_tnx_rec.organization_id := 204;
l_tnx_rec.subinventory_code := 'STORES';
l_tnx_rec.transaction_quantity := 10;
l_tnx_rec.transaction_uom := 'EA';
l_tnx_rec.process_flag := 1;
l_tnx_rec.last_update_date := SYSDATE;
l_tnx_rec.creation_date := SYSDATE;
l_tnx_rec.created_by := FND_GLOBAL.USER_ID;
l_tnx_rec.last_updated_by := FND_GLOBAL.USER_ID;
-- Call Procedure to populate mtl_transactions_interface
populate_mti_proc(l_tnx_rec);
-- Call API
INV_TXN_MANAGER_PUB.PROCESS_TRANSACTIONS(
p_api_version_number => 1.0,
p_init_msg_list => FND_API.G_TRUE,
p_commit => FND_API.G_FALSE,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_tnx_rec => l_tnx_rec,
p_tnx_ctrl_rec => l_ctrl_rec,
x_transaction_id => l_transaction_id
);
END;
INV_TXN_MANAGER_PUB.PROCESS_TRANSACTIONS2. 🟨 INV_MOVE_ORDER_PUB.Process_Move_Order – Create Move Order
DECLARE
l_header_rec inv.move_order_header_rec_type;
l_line_tbl inv.move_order_line_tbl_type;
l_line_rec inv.move_order_line_rec_type;
l_return_status VARCHAR2(1);
l_msg_data VARCHAR2(4000);
l_msg_count NUMBER;
l_header_id NUMBER;
BEGIN
-- Header
l_header_rec.date_required := sysdate + 2;
l_header_rec.organization_id := 204;
l_header_rec.from_subinventory_code := 'FG';
l_header_rec.to_subinventory_code := 'STORE';
l_header_rec.status_date := sysdate;
l_header_rec.request_number := mtl_txn_request_headers_s.nextval;
l_header_rec.header_status := INV_Globals.G_TO_STATUS_PREAPPROVED;
l_header_rec.transaction_type_id := INV_GLOBALS.G_TYPE_TRANSFER_ORDER_SUBXFR;
l_header_rec.move_order_type := INV_GLOBALS.G_MOVE_ORDER_REQUISITION;
l_header_rec.db_flag := FND_API.G_TRUE;
l_header_rec.operation := INV_GLOBALS.G_OPR_CREATE;
-- Line
l_line_rec.inventory_item_id := 1001;
l_line_rec.organization_id := 204;
l_line_rec.from_subinventory_code:= 'FG';
l_line_rec.to_subinventory_code := 'STORES';
l_line_rec.transaction_quantity := 10;
l_line_rec.uom_code := 'EA';
l_line_rec.line_status := 1;
l_line_tbl(1) := l_line_rec;
-- Call API
INV_MOVE_ORDER_PUB.Process_Move_Order(
p_api_version_number => 1.0,
p_init_msg_list => FND_API.G_TRUE,
p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
x_move_order_header_id => l_header_id,
p_header_rec => l_header_rec,
p_line_tbl => l_line_tbl
);
END;
SQLCreate Move Order Header(INV_MOVE_ORDER_PUB.Create_Move_Order_Header)
You can create move order header using INV_MOVE_ORDER_PUB.Create_Move_Order_Header. You need to populate the header record and call the api.
l_header_rec.date_required := sysdate + 2;
l_header_rec.organization_id := 207;
l_header_rec.from_subinventory_code := 'Stores';
l_header_rec.to_subinventory_code := 'FGI';
l_header_rec.status_date := sysdate;
INV_MOVE_ORDER_PUB.Create_Move_Order_Header
( p_api_version_number => '1.0'
, p_init_msg_list => FND_API.G_FALSE
, p_return_values => FND_API.G_FALSE
, p_commit => FND_API.G_FALSE
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_trohdr_rec => l_header_rec
, p_trohdr_val_rec => l_trohdr_val_rec
, x_trohdr_rec => x_Trohdr_Rec_Type
, x_trohdr_val_rec => x_Trohdr_Val_Rec_Type
, p_validation_flag => FND_API.G_TRUE
);
INV_MOVE_ORDER_PUB.Create_Move_Order_HeaderCreate Move Orders Lines (INV_MOVE_ORDER_PUB.Create_Move_Order_Lines)
You can create move order lines using INV_MOVE_ORDER_PUB.Create_Move_Order_Lines. You need to populate the lines PL/SQL table and call the api.
l_line_tbl(l_row_cnt).header_id := 4073038;
l_line_tbl(l_row_cnt).date_required := sysdate;
l_line_tbl(l_row_cnt).organization_id := 207;
l_line_tbl(l_row_cnt).inventory_item_id := 513963;
l_line_tbl(l_row_cnt).from_subinventory_code := 'Stores';
l_line_tbl(l_row_cnt).to_subinventory_code := 'FGI';
l_line_tbl(l_row_cnt).quantity := 2;
l_line_tbl(l_row_cnt).status_date := sysdate;
l_line_tbl(l_row_cnt).uom_code := 'Ea';
l_line_tbl(l_row_cnt).line_number := l_row_cnt;
l_line_tbl(l_row_cnt).line_status := inv_globals.g_to_status_preapproved;
l_line_tbl(l_row_cnt).db_flag := fnd_api.g_true;
l_line_tbl(l_row_cnt).operation := inv_globals.g_opr_create;
INV_MOVE_ORDER_PUB.Create_Move_Order_Lines
( p_api_version_number => '1.0'
, p_init_msg_list => FND_API.G_FALSE
, p_return_values => FND_API.G_FALSE
, p_commit => FND_API.G_FALSE
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_trolin_tbl => l_line_tbl
, p_trolin_val_tbl => L_trolin_val_tbl
, x_trolin_tbl => x_trolin_tbl
, x_trolin_val_tbl => x_trolin_val_tbl
, p_validation_flag => FND_API.G_TRUE
);
INV_MOVE_ORDER_PUB.Create_Move_Order_Lines3. 🟨 INV_REPLENISH_DETAIL_PUB.line_details_pub
BEGIN
inv_quantity_tree_pub.clear_quantity_cache;
mo_global.set_policy_context ('S', 101);
inv_globals.set_org_id (101);
mo_global.init ('INV');
INV_REPLENISH_DETAIL_PUB.line_details_pub(
p_line_id => l_mo_line_id
, x_number_of_rows => x_number_of_rows
, x_detailed_qty => x_detailed_qty
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, x_revision => x_revision
, x_locator_id => x_locator_id
, x_transfer_to_location => x_transfer_to_location
, x_lot_number => x_lot_number
, x_expiration_date => x_expiration_date
, x_transaction_temp_id => x_transaction_temp_id
, p_transaction_header_id => NULL
, p_transaction_mode => NULL
, p_move_order_type => l_move_order_type
, p_serial_flag => FND_API.G_FALSE
, p_plan_tasks => FALSE --FND_API.G_FALSE
, p_auto_pick_confirm => FALSE --FND_API.G_FALSE
, p_commit => FALSE --FND_API.G_FALSE
);
END;
INV_REPLENISH_DETAIL_PUB.line_details_pub4. 🟪 INV_RESERVATION_PUB.CREATE_RESERVATION
DECLARE
l_return_status VARCHAR2(1);
l_msg_count NUMBER;
l_msg_data VARCHAR2(4000);
l_reservation_id NUMBER;
-- API specific declarations
l_rsv_rec INV_RESERVATION_GLOBAL.MTL_RESERVATION_REC_TYPE;
l_serial_number INV_RESERVATION_GLOBAL.SERIAL_NUMBER_TBL_TYPE;
l_serial_number INV_RESERVATION_GLOBAL.SERIAL_NUMBER_TBL_TYPE;
l_partial_reservation_flag VARCHAR2(2) := FND_API.G_FALSE;
l_force_reservation_flag VARCHAR2(2) := FND_API.G_FALSE;
l_validation_flag VARCHAR2(2) := FND_API.G_TRUE;
l_partial_reservation_exists BOOLEAN := FALSE;
l_quantity_reserved NUMBER := 0;
l_reservation_id NUMBER := 0;
l_primary_reservation_qty NUMBER := 2; -- total qty
l_subinventory_code VARCHAR2(40) := NULL;-- will create a hard reservation
BEGIN
-- Initialize the variables
l_rsv_rec.organization_id := l_organization_id;
l_rsv_rec.inventory_item_id := l_inventory_item_id;
l_rsv_rec.requirement_date := sysdate + 3;
l_rsv_rec.demand_source_type_id:= INV_RESERVATION_GLOBAL.G_SOURCE_TYPE_INV;
l_rsv_rec.supply_source_type_id:= INV_RESERVATION_GLOBAL.G_SOURCE_TYPE_INV;
l_rsv_rec.demand_source_name := 'INV_RSV_'||l_item_number;
l_rsv_rec.primary_reservation_quantity := l_primary_reservation_qty;
l_rsv_rec.primary_uom_code := l_primary_uom_code;
l_rsv_rec.subinventory_code := l_subinventory_code;
INV_RESERVATION_PUB.Create_Reservation
(
P_API_VERSION_NUMBER => l_api_version
, P_INIT_MSG_LST => l_init_msg_list
, P_RSV_REC => l_rsv_rec
, P_SERIAL_NUMBER => l_serial_number
, P_PARTIAL_RESERVATION_FLAG => l_partial_reservation_flag
, P_FORCE_RESERVATION_FLAG => l_force_reservation_flag
, P_PARTIAL_RSV_EXISTS => l_partial_reservation_exists
, P_VALIDATION_FLAG => l_validation_flag
, X_SERIAL_NUMBER => l_serial_number
, X_RETURN_STATUS => l_return_status
, X_MSG_COUNT => l_msg_count
, X_MSG_DATA => l_msg_data
, X_QUANTITY_RESERVED => l_quantity_reserved
, X_RESERVATION_ID => l_reservation_id
);
END;
INV_RESERVATION_PUB.Create_Reservation5. 🟦 INV_QUANTITY_TREE_PUB.QUERY_QUANTITIES
You can easily get the onhand stock using Inventory API in Oracle Apps R12 INV_QUANTITY_TREE_PUB.query_quantities. You need to pass inventory Item id, organization, subinventory & locator to get the stock level. Get Inventory On Hand quantities through this API.
DECLARE
l_return_status VARCHAR2(1);
l_msg_count NUMBER;
l_msg_data VARCHAR2(4000);
l_quantity NUMBER;
l_return_status VARCHAR2(1);
BEGIN
INV_QUANTITY_TREE_PUB.query_quantities
(
p_api_version_number => 1.0
, p_init_msg_lst => fnd_api.g_false
, x_return_status => l_api_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_organization_id => l_organization_id
, p_inventory_item_id => l_item_id
, p_tree_mode => inv_quantity_tree_pub.g_transaction_mode
, p_onhand_source => 3
, p_is_revision_control=> FALSE
, p_is_lot_control => l_is_lot
, p_is_serial_control => l_is_serial
, p_revision => NULL
, p_lot_number => l_lot_num
, p_subinventory_code => l_subinv_code
, p_locator_id => l_locator_id
, x_qoh => l_qty_oh
, x_rqoh => l_qty_res_oh
, x_qr => l_qty_res
, x_qs => l_qty_sug
, x_att => l_qty_att
, x_atr => l_qty_atr
);
DBMS_OUTPUT.PUT_LINE('On-hand Quantity: ' || l_qty_atr);
END;
INV_QUANTITY_TREE_PUB.query_quantities6. 🟥 INV_PICK_WAVE_PICK_CONFIRM_PUB.PICK_CONFIRM
DECLARE
l_return_status VARCHAR2(1);
l_msg_count NUMBER;
l_msg_data VARCHAR2(4000);
l_delivery_detail_id NUMBER;
BEGIN
INV_PICK_WAVE_PICK_CONFIRM_PUB.PICK_CONFIRM(
p_api_version_number => 1.0,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_delivery_detail_id => l_delivery_detail_id,
p_quantity => 10,
p_user_id => FND_GLOBAL.USER_ID
);
END;
SQL7. 🟧 WSH_DELIVERIES_PUB.DELIVERY_ACTION
DECLARE
l_operation VARCHAR2(30) := 'Pick Release from Delivery';
l_return_status VARCHAR2(1) ;
l_msg_data VARCHAR2(2000);
l_msg_count NUMBER;
--Standard Parameters.
l_api_version_number NUMBER;
l_init_msg_list VARCHAR2(30);
l_msg_details VARCHAR2(3000);
l_msg_summary VARCHAR2(3000);
l_validation_level NUMBER;
l_commit VARCHAR2(30);
--Parameters for WSH_DELIVERIES_PUB.Delivery_Action.
l_action_code VARCHAR2(15);
l_delivery_id NUMBER;
l_delivery_name VARCHAR2(30);
l_sc_action_flag VARCHAR2(10);
l_sc_close_trip_flag VARCHAR2(10);
l_sc_create_bol_flag VARCHAR2(10);
l_sc_stage_del_flag VARCHAR2(10);
l_wv_override_flag VARCHAR2(10);
l_trip_id VARCHAR2(30);
l_trip_name VARCHAR2(30);
BEGIN
l_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
FND_GLOBAL.APPS_INITIALIZE(
user_id => 1234 -- User ID
,resp_id => 21623 -- Order Management Super User
,resp_appl_id => 660 -- Order Management
);
-- Delivery Action API (Pick Release)
l_action_code := 'PICK-RELEASE';
WSH_DELIVERIES_PUB.DELIVERY_ACTION
(
p_api_version_number => 1.0
, p_init_msg_list => init_msg_list
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_action_code => l_action_code
, p_delivery_id => l_delivery_id
, p_delivery_name => l_delivery_name
, p_sc_action_flag => l_sc_action_flag
, p_sc_close_trip_flag => l_sc_close_trip_flag
, p_sc_create_bol_flag => l_sc_create_bol_flag
, p_sc_stage_del_flag => l_sc_stage_del_flag
, p_wv_override_flag => l_wv_override_flag
, x_trip_id => l_trip_id
, x_trip_name => l_trip_name
);
IF x_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS
THEN
dbms_output.put_line( l_operation ||' done successfully.' ) ;
commit;
END IF ;
END ;
WSH_DELIVERIES_PUB.DELIVERY_ACTION8. 🟨 Step-by-Step: Insert and Process Cycle Count Entries
You need to follow the below steps to insert data into the interface table and initiate processing.
✅ Step 1: Insert Records into MTL_CC_ENTRIES_INTERFACE
You need to insert the cycle count entries in MTL_CC_ENTRIES_INTERFACE as per the below PL/SQL code snippet.
INSERT INTO mtl_cc_entries_interface (
cc_entry_interface_id,
organization_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
action_code,
cycle_count_header_id,
item_segment1,
revision,
subinventory,
locator_id,
lot_number,
count_date,
employee_id,
lock_flag,
process_flag,
process_mode,
valid_flag,
delete_flag,
status_flag,
transaction_reason_id,
reference,
count_uom,
count_quantity
) VALUES (
MTL_CC_ENTRIES_INTERFACE_S1.NEXTVAL,
l_organization_id, -- e.g., 207 for M1
SYSDATE,
l_user_id, -- e.g., 1068 for MFG
SYSDATE,
l_user_id,
14, -- G_PROCESS
l_cycle_count_id, -- Lookup in MTL_CYCLE_COUNT_HEADERS
l_item,
'',
l_Subinventory, -- e.g., 'FGI'
'',
'',
SYSDATE,
l_emp_id,
2, -- UNLOCK
1, -- READY
3, -- Background
1, -- VALID
2, -- Not Deleted
4, -- Marked for Reprocessing
'',
'Test API',
'Ea',
l_quantity
);
COMMIT;
Insert Cycle Count Entries in Oracle Apps R12✅ Step 2: Run the Concurrent Program
“Import Cycle Count Entries from Open Interface”
This will validate and process the entries. Ensure the cycle count name matches the one in MTL_CYCLE_COUNT_HEADERS.
🛠️ Debugging Tips
- Check Interface Status
SELECT * FROM MTL_CC_ENTRIES_INTERFACE;
SELECT * FROM MTL_CC_INTERFACE_ERRORS; - Review Logs
- Log of “Import Cycle Count Entries from Open Interface”
- Log of “Cycle Count Entries Open Interface Worker”
9. 🟦 INV_MATERIAL_TXN_PUB.CANCEL_TXN
BEGIN
INV_MATERIAL_TXN_PUB.CANCEL_TXN(
p_api_version_number => 1.0,
p_transaction_id => :txn_id,
x_return_status => :x_status,
x_msg_count => :x_count,
x_msg_data => :x_data
);
END;
SQL10. 🟩 INV_RESERVATION_PUB.DELETE_RESERVATION
DECLARE
l_resv_rec inv_reservation_global.mtl_reservation_rec_type;
l_return_status VARCHAR2(1);
l_msg_count NUMBER;
l_msg_data VARCHAR2(4000);
BEGIN
FND_GLOBAL.APPS_INITIALIZE(
user_id => 1234 -- User ID
,resp_id => 21623 -- Inventory
,resp_appl_id => 660 -- Inventory
);
l_resv_rec.reservation_id := p_reservation_id;
INV_RESERVATION_PUB.DELETE_RESERVATION
(p_api_version_number => 1.0,
p_init_msg_lst => fnd_api.g_true,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_rsv_rec => l_resv_rec,
p_serial_number => l_dummy_sn
);
IF l_status = fnd_api.g_ret_sts_success
THEN
COMMIT;
ELSE
IF l_msg_count >= 1
THEN
FOR i IN 1..l_msg_count
LOOP
l_msg_data := SUBSTR(fnd_msg_pub.get(p_encoded => fnd_api.g_false ),1, 255);
END LOOP;
END IF;
END IF;
END;
INV_RESERVATION_PUB.DELETE_RESERVATION11. inv_convert.create_uom_conversion
Item Unit of measure conversion can be done using Oracle Inventory API inv_convert.create_uom_conversion
DECLARE
l_from_uom varchar2(4);
l_to_uom varchar2(4);
l_item_id number;
l_uom_rate number := 3;
l_return_status varchar2(10);
l_error_code number;
l_msg_count number;
l_msg_data VARCHAR2(255);
l_item_number VARCHAR2(40) := 'SU_TEST_UOM_CONV1';
l_inventory_item_id number := 0;
l_primary_uom varchar2(20);
l_from_uom_code varchar2(20);
l_to_uom_code varchar2(20);
BEGIN
-- Get item information
BEGIN
select distinct msi.inventory_item_id,
msi.primary_uom_code
INTO l_inventory_item_id,
l_primary_uom
FROM mtl_system_items_b msi
WHERE segment1 = l_item_number;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Invalid Item');
END;
l_from_uom_code := 'Lbs';
l_to_uom_code := 'G';
-- Intra class (Within the same UOM class as the Primary UOM's class)
-- Source is the Base UOM of the Primary UOM's class
inv_convert.create_uom_conversion(
p_from_uom_code => l_from_uom_code,
p_to_uom_code => l_to_uom_code,
p_item_id => l_inventory_item_id ,
p_uom_rate => 0.1,
x_return_status => l_return_status
);
if (x_return_status <> fnd_api.g_ret_sts_success)
then
dbms_output.put_line('Error Message Count :'||fnd_msg_pub.count_msg);
x_msg_count := fnd_msg_pub.count_msg;
for cnt in 1..x_msg_count
loop
dbms_output.put_line('Index: '||cnt||' Error Message :'||fnd_msg_pub.get(cnt, 'T'));
end loop;
end if;
END;
inv_convert.create_uom_conversion12. INV_MATERIAL_STATUS_PUB.update_status: Change The Inventory Onhand Material Status Using Inventory API
You can update Onhand Material Status using Inventory API in Oracle Apps R12 (INV_MATERIAL_STATUS_PUB.update_status). Status is maintained at different levels like On-Hand, Subinventory, Locator, Lot & Serial. The object_type parameter can be used to update status at the appropriate level using a value of H, O, S, Z, or L.
BEGIN
-- Initialize variables
l_object_type := 'H';
-- 'O' = Lot , 'S' = Serial, 'Z' = Subinventory, 'L' = Locator, 'H' = Onhand
l_status_rec.organization_id := 143;
l_status_rec.inventory_item_id := 165432;
l_status_rec.lot_number := 'LOT2025';
l_status_rec.zone_code := 'RIP';
l_status_rec.locator_id := NULL;
l_status_rec.status_id := 1;
-- select status_id, status_code from mtl_material_statuses_vl;
l_status_rec.update_reason_id := 305;
l_status_rec.update_method := 2;
INV_MATERIAL_STATUS_PUB.update_status
( p_api_version_number => l_api_version
, p_init_msg_lst => l_init_msg_list
, p_commit => l_commit
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_object_type => l_object_type
, p_status_rec => l_status_rec
);
IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)
THEN
DBMS_OUTPUT.PUT_LINE('Error Message :'||x_msg_data);
END IF;
END;
inv_material_status_pub-update_status13. INV_LOC_WMS_PUB.CREATE_LOCATOR: Create a Locator
You can use API INV_LOC_WMS_PUB.Create_Locator to create inventory stock locators in both WMS and non-WMS organizations.
DECLARE
l_api_version NUMBER := 1.0;
l_return_status VARCHAR2(10);
l_msg_count NUMBER := 0;
l_msg_data VARCHAR2(255) ;
l_organization_id NUMBER := 83;
l_organization_code VARCHAR2(10) := 'ORG';
l_subinventory_code VARCHAR2(10) := 'FG';
l_loc_segments VARCHAR2(90) := 'TST1.TST2.TST3';
l_description VARCHAR2(200) := 'Test Locators';
l_locator_type VARCHAR2(60);
l_status_id NUMBER := NULL;
l_locator_id NUMBER := NULL;
l_locator_exists VARCHAR2(1) := 'N';
BEGIN
INV_LOC_WMS_PUB.CREATE_LOCATOR
( x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, x_inventory_location_id => l_locator_id
, x_locator_exists => l_locator_exists
, p_organization_id => l_organization_id
, p_organization_code => l_organization_code
, p_concatenated_segments => l_loc_segments
, p_description => l_description
, p_inventory_location_type=> l_locator_type
, p_subinventory_code => l_subinventory_code
, p_status_id => l_status_id
);
END;
inv_loc_wms_pub-create_locator14. EGO_ITEM_PUB.PROCESS_ITEMS: Create or Update Item
EGO_ITEM_PUB.PROCESS_ITEM is a convenient wrapper to Process_Item. You can use this API to create or update an item by passing only the most important and commonly used item attributes.
DECLARE
x_item_tbl EGO_ITEM_PUB.ITEM_TBL_TYPE;
l_message_list Error_Handler.Error_Tbl_Type;
l_return_status VARCHAR2(2);
l_msg_count NUMBER := 0;
l_user_id NUMBER := -1;
l_resp_id NUMBER := -1;
l_application_id NUMBER := -1;
l_rowcnt NUMBER := 1;
l_api_version NUMBER := 1.0;
l_init_msg_list VARCHAR2(2) := FND_API.G_TRUE;
l_commit VARCHAR2(2) := FND_API.G_FALSE;
l_item_tbl EGO_ITEM_PUB.ITEM_TBL_TYPE;
l_role_grant_tbl EGO_ITEM_PUB.ROLE_GRANT_TBL_TYPE;
l_user_name VARCHAR2(30):= 'SYSADMIN';
l_resp_name VARCHAR2(30):= 'Inventory';
l_item_catalog_group_id NUMBER := 0;
BEGIN
FND_GLOBAL.APPS_INITIALIZE(l_user_id, l_resp_id, l_application_id);
mo_global.set_policy_context('S', 81);
l_item_tbl(l_rowcnt).transaction_type := 'CREATE';
l_item_tbl(l_rowcnt).segment1 := 'TEST-ITEM2506';
l_item_tbl(l_rowcnt).description := 'Enodeas 5MM Item';
l_item_tbl(l_rowcnt).organization_code := 'MST';
l_item_tbl(l_rowcnt).primary_uom_code := 'EA';
l_item_tbl(l_rowcnt).template_name := 'MFG Finished Good';
l_item_tbl(l_rowcnt).inventory_item_status_code := 'Active';
-- call API to load Items
DBMS_OUTPUT.PUT_LINE('Calling EGO_ITEM_PUB.Process_Items API');
EGO_ITEM_PUB.PROCESS_ITEMS(
p_api_version => l_api_version
,p_init_msg_list => l_init_msg_list
,p_commit => l_commit
,p_item_tbl => l_item_tbl
,p_role_grant_tbl => l_role_grant_tbl
,x_item_tbl => x_item_tbl
,x_return_status => l_return_status
,x_msg_count => l_msg_count
);
IF (l_return_status = FND_API.G_RET_STS_SUCCESS)
THEN
FOR i IN 1..x_item_tbl.COUNT LOOP
DBMS_OUTPUT.PUT_LINE('Inventory Item Id :'||to_char(x_item_tbl(i).inventory_item_id));
END LOOP;
ELSE
Error_Handler.GET_MESSAGE_LIST
(x_message_list=>l_message_list);
FOR i IN 1..l_message_list.COUNT
LOOP
DBMS_OUTPUT.PUT_LINE(l_message_list(i).message_text);
END LOOP;
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Exception Occured:'||SQLERRM);
RAISE;
END;
Create or Update Item EGO_ITEM_PUB.PROCESS_ITEMS15. How to Create Category Set in Oracle Inventory?
In the below code snippet you can learn how to create category set in Oracle Apps R12 using Inventory API.
declare
l_return_status VARCHAR2(80);
l_error_code NUMBER;
l_msg_count NUMBER;
l_msg_data VARCHAR2(80);
l_category_id NUMBER;
l_category_set_id NUMBER;
l_Inventory_item_id NUMBER;
l_organization_id NUMBER;
begin
--Category Set
SELECT mcs_tl.CATEGORY_SET_ID
INTO l_category_set_id
FROM mtl_category_sets_tl mcs_tl
WHERE mcs_tl.CATEGORY_SET_NAME = 'HOME_INTERIOR';
--category id
SELECT mcb.CATEGORY_ID
INTO l_category_id
FROM mtl_categories_b mcb,
mtl_category_sets_b mcs_b,
mtl_category_sets_tl mcs_tl
WHERE mcb.SEGMENT1 = 'SOFA'
AND mcb.SEGMENT2 = 'MEDIUM'
AND mcb.STRUCTURE_ID = mcs_b.STRUCTURE_ID
AND mcs_b.CATEGORY_SET_ID = mcs_tl.CATEGORY_SET_ID
AND mcs_tl.CATEGORY_SET_NAME = 'HOME_INTERIOR';
--Item id
SELECT DISTINCT(INVENTORY_ITEM_ID)
INTO l_inventory_item_id
FROM mtl_system_items_kfv msik
WHERE msik.concatenated_segments = 'SOFA.MEDIUM';
--Get organization id
SELECT organization_id
INTO l_organization_id
FROM mtl_parameters
WHERE organization_code = 'V1';
--Call api to creta assignment
INV_ITEM_CATEGORY_PUB.Create_Category_Assignment
(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_TRUE,
x_return_status => l_return_status,
x_errorcode => l_error_code,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_category_id => l_category_id,
p_category_set_id => l_category_set_id,
p_inventory_item_id => l_Inventory_item_id,
p_organization_id => l_organization_id
);
END;
INV_ITEM_CATEGORY_PUB.Create_Category_Assignmentdeclare
l_return_status VARCHAR2(80);
l_error_code NUMBER;
l_msg_count NUMBER;
l_msg_data VARCHAR2(80);
l_category_id NUMBER;
l_category_set_id NUMBER;
l_Inventory_item_id NUMBER;
l_organization_id NUMBER;
begin
--Category Set
SELECT mcs_tl.CATEGORY_SET_ID
INTO l_category_set_id
FROM mtl_category_sets_tl mcs_tl
WHERE mcs_tl.CATEGORY_SET_NAME = 'HOME_INTERIOR';
--category id
SELECT mcb.CATEGORY_ID
INTO l_category_id
FROM mtl_categories_b mcb,
mtl_category_sets_b mcs_b,
mtl_category_sets_tl mcs_tl
WHERE mcb.SEGMENT1 = 'SOFA'
AND mcb.SEGMENT2 = 'MEDIUM'
AND mcb.STRUCTURE_ID = mcs_b.STRUCTURE_ID
AND mcs_b.CATEGORY_SET_ID = mcs_tl.CATEGORY_SET_ID
AND mcs_tl.CATEGORY_SET_NAME = 'HOME_INTERIOR';
--Item id
SELECT DISTINCT(INVENTORY_ITEM_ID)
INTO l_inventory_item_id
FROM mtl_system_items_kfv msik
WHERE msik.concatenated_segments = 'SOFA.MEDIUM';
--Get organization id
SELECT organization_id
INTO l_organization_id
FROM mtl_parameters
WHERE organization_code = 'V1';
--call api Delete_Category_Assignment
INV_ITEM_CATEGORY_PUB.Delete_Category_Assignment
(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_TRUE,
x_return_status => l_return_status,
x_errorcode => l_error_code,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_category_id => l_category_id,
p_category_set_id => l_category_set_id,
p_inventory_item_id => l_Inventory_item_id,
p_organization_id =>l_organization_id
);
END;
INV_ITEM_CATEGORY_PUB.Delete_Category_Assignment16. How to Create Item Category in Oracle Inventory?
How to Create Category and Category Set in Oracle Inventory? You can use INV_ITEM_CATEGORY_PUB.Create_Category for this task.
DECLARE
l_category_rec INV_ITEM_CATEGORY_PUB.CATEGORY_REC_TYPE;
l_return_status VARCHAR2(80);
l_error_code NUMBER;
l_msg_count NUMBER;
l_msg_data VARCHAR2(80);
l_category_id NUMBER;
BEGIN
l_category_rec.segment1 := 'SOFA';
l_category_rec.segment2 := 'MED';
SELECT f.ID_FLEX_NUM
INTO l_category_rec.structure_id
FROM FND_ID_FLEX_STRUCTURES f
WHERE f.ID_FLEX_STRUCTURE_CODE = 'HOME_INT';
l_category_rec.description := 'Medium Size Sofa';
INV_ITEM_CATEGORY_PUB.Create_Category
(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_TRUE,
x_return_status => l_return_status,
x_errorcode => l_error_code,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_category_rec => l_category_rec,
x_category_id => l_category_id
);
IF l_return_status = fnd_api.g_ret_sts_success THEN
COMMIT;
DBMS_OUTPUT.put_line ('Item Category Creation is Successful: '||l_category_id);
ELSE
DBMS_OUTPUT.put_line ('Item Category Creation Failed with the error :'||l_error_code);
ROLLBACK;
END IF;
END ;
INV_ITEM_CATEGORY_PUB.Create_CategoryINV_ITEM_CATEGORY_PUB.Delete_Category: Delete Item Category
You can use INV_ITEM_CATEGORY_PUB.Delete_Category api to delete item category.
DECLARE
l_return_status VARCHAR2(80);
l_error_code NUMBER;
l_msg_count NUMBER;
l_msg_data VARCHAR2(80);
l_category_id NUMBER;
BEGIN
SELECT mcb.CATEGORY_ID
INTO l_category_id
FROM mtl_categories_b mcb,
mtl_category_sets_b mcs_b,
mtl_category_sets_tl mcs_tl
WHERE mcb.SEGMENT1 = 'SOFA'
AND mcb.STRUCTURE_ID = mcs_b.STRUCTURE_ID
AND mcs_b.CATEGORY_SET_ID = mcs_tl.CATEGORY_SET_ID
AND mcs_tl.CATEGORY_SET_NAME = 'HOME INTERIOR';
--Call INV_ITEM_CATEGORY_PUB.Delete_Category api
INV_ITEM_CATEGORY_PUB.Delete_Category
(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_TRUE,
x_return_status => l_return_status,
x_errorcode => l_error_code,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_category_id => l_category_id);
IF l_return_status = fnd_api.g_ret_sts_success
THEN
COMMIT;
DBMS_OUTPUT.put_line ('Delete of Item Categoryis Successful: '||l_category_id);
ELSE
DBMS_OUTPUT.put_line ('tem Category Removal Failed with error :'||l_error_code);
ROLLBACK;
END IF;
END;
INV_ITEM_CATEGORY_PUB.Delete_Category
Pingback: Inventory Tables in Oracle Apps R12: Ultimate Guide
Pingback: Inventory Transactions in Oracle Apps R12: A Complete Guide