INV Physical Inventory Upload – Oracle EBS SQL Report

Oracle E-Business Suite SQL report from the Enginatics Library powered by Blitz Report™.

Overview

This upload supports the creation of new Physical Inventories and the update of existing Physical Inventories that have not yet been frozen.

The upload supports the specification of the subinventories to be included in the Physical Inventory.

When updating an existing Physical Inventory, the Delete Subinventory upload column allows for individual subinventories to be removed from the Physical Inventory subinventory list.

Replace Existing Subinventories

When the Replace Existing Subinventories report parameter is set to Yes, the existing physical inventory subinventories will be replaced by those specified in the upload.

When set to No, the upload will update the existing Physical Inventory subinventories, create additional subinventories, or delete specific subinventories as determined from the uploaded data.

Freeze Physical Inventory

When set to Yes, the Freeze Physical Inventory concurrent program will be submitted after the upload is complete to freeze the uploaded Physical Inventories.

This will only occur for each uploaded Physical Inventory if no upload error has occurred in any upload row for that Physical Inventory.

Once a Physical Inventory has been frozen, it can no longer be updated by this upload.

The Snapshot Complete and Freeze Date columns in the upload excel are display only columns. They will be displayed after the upload when the Freeze Physical Inventory option is selected.

Report Parameters

Upload Mode, Replace Existing Subinventories, Freeze Physical Inventory, Organization Code, Physical Inventory

Oracle EBS Tables Used

mtl_physical_inventories_v, mtl_physical_subinventories, mtl_parameters, org_access_view

Report Categories

Enginatics, R12 only, Upload

INV Item Upload, INV Items, INV Physical Inventory Purge Upload, CAC Recost Cost of Goods Sold, CAC Deferred COGS Out-of-Balance, INV Material Transactions

Running This SQL Without Blitz Report

Some Oracle EBS SQL reports in this library require functions from the utility package xxen_util. Install it before running the SQL directly against your Oracle EBS database.

Download & Import Options

Resource Link
Excel Example Output None
Blitz Report™ XML Import INV_Physical_Inventory_Upload.xml
Full SQL on Enginatics www.enginatics.com/reports/inv-physical-inventory-upload/

INV Physical Inventory Upload - Case Study & Technical Analysis

Executive Summary

The INV Physical Inventory Upload is a setup and maintenance tool for the physical inventory process. It allows users to create the “Header” definition of a physical inventory and assign subinventories to it in bulk. This is particularly useful for large organizations that run multiple physical inventories simultaneously or have hundreds of subinventories to assign.

Business Challenge

Setting up a physical inventory in Oracle involves:

  1. Defining the name and date.
  2. Manually selecting which subinventories are included.
  3. Freezing the inventory (taking the snapshot). Doing this manually for 50 warehouses is tedious and prone to setup errors (e.g., forgetting to include the “Returns” subinventory).

Solution

The INV Physical Inventory Upload automates the definition phase. Users can define the scope of the count in Excel and upload it.

Key Features:

Technical Architecture

The tool interacts with the physical inventory definition tables and submits concurrent requests.

Key Tables and Views

Core Logic

  1. Upload: Reads the configuration from Excel.
  2. Creation: Inserts records into MTL_PHYSICAL_INVENTORIES and MTL_PHYSICAL_SUBINVENTORIES.
  3. Process Submission: If “Freeze” is selected, it submits the INV_PHY_INV_SNAPSHOT concurrent program.

Business Impact

© 2026 Enginatics