Mastering Inventory API in Oracle Apps R12: An Interface Guide

Inventory API in Oracle Apps R12

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 NameCommon Use CaseCore Table Impacted
PROCESS_TRANSACTIONSMiscellaneous & Subinventory TxnMTL_TRANSACTIONS_INTERFACE
PROCESS_MOVE_ORDERSubinventory Move RequestMTL_TXN_REQUEST_HEADERS
LINE_DETAILS_PUBAlllocations of Move OrdersMTL_MATERIAL_TRANSACTIONS
CREATE_RESERVATIONSales Order & WIP ReservationsMTL_RESERVATIONS
QUERY_QUANTITIESReal-time On-hand CheckRead-only from inventory tables
PICK_CONFIRMSales Order Shipment PickingWSH_DELIVERY_DETAILS
PICK_RELEASEDelivery Pick Release
Physical Inventory AdjustmentsPhysical Inventory AdjustmentsMTL_MATERIAL_TRANSACTIONS
Cycle Count AdjustmentsCycle Count AdjustmentsMTL_CYCLE_COUNTS
DELETE_RESERVATIONDelete a ReservationMTL_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

Populate mtl_transactions_interface table
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 table
INV_TXN_MANAGER_PUB.PROCESS_TRANSACTIONS
DECLARE
   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_TRANSACTIONS
SQL
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;
SQL

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.

INV_MOVE_ORDER_PUB.Create_Move_Order_Header
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_Header

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.

INV_MOVE_ORDER_PUB.Create_Move_Order_Lines
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_Lines
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_pub
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_Reservation

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.

INV_QUANTITY_TREE_PUB.query_quantities
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_quantities
SQL
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;
SQL
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_ACTION

You need to follow the below steps to insert data into the interface table and initiate processing.

You need to insert the cycle count entries in MTL_CC_ENTRIES_INTERFACE as per the below PL/SQL code snippet.

Process Cycle Count Entries in Oracle Apps R12
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

“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.

  1. Check Interface Status
    SELECT * FROM MTL_CC_ENTRIES_INTERFACE;
    SELECT * FROM MTL_CC_INTERFACE_ERRORS;
  2. Review Logs
    • Log of “Import Cycle Count Entries from Open Interface”
    • Log of “Cycle Count Entries Open Interface Worker”
SQL
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;
SQL
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_RESERVATION

Item Unit of measure conversion can be done using Oracle Inventory API inv_convert.create_uom_conversion

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_conversion

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.

inv_material_status_pub-update_status
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_status

You can use API INV_LOC_WMS_PUB.Create_Locator to create inventory stock locators in both WMS and non-WMS organizations.

inv_loc_wms_pub-create_locator
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_locator

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.

EGO_ITEM_PUB.PROCESS_ITEMS: Create or Update Item
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_ITEMS

15. 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.

INV_ITEM_CATEGORY_PUB.Create_Category_Assignment
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_Assignment
INV_ITEM_CATEGORY_PUB.Delete_Category_Assignment
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 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_Assignment

16. 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.

INV_ITEM_CATEGORY_PUB.Create_Category
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_Category

INV_ITEM_CATEGORY_PUB.Delete_Category: Delete Item Category

You can use INV_ITEM_CATEGORY_PUB.Delete_Category api to delete item category.

INV_ITEM_CATEGORY_PUB.Delete_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

This Post Has 2 Comments

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.