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.

Run this report in Excel in seconds: download Blitz Report™ free (your first 30 reports are free) or try it in the online demo.

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, Upload

INV Item Upload 11i, INV Items, INV Item Upload, INV Physical Inventory Purge Upload, INV Physical Inventory Locator Count Sheet, INV Material Transactions, INV Physical inventory accuracy analysis, INV Physical Inventory Tag Count Upload, GL Account Distribution Analysis

Running This SQL Without Blitz Report

The SQL source can be run in any SQL client. Some Oracle EBS SQL reports in this library use functions from the utility package xxen_util – install it first. Blitz Report™ adds parameters, Excel output with pivots, scheduling and security on top.

Download & Import Options

Resource Link
Excel Example Output Example
Blitz Report™ XML Import INV_Physical_Inventory_Upload.xml
SQL Source INV Physical Inventory Upload.sql
Full Report 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

© Enginatics GmbH