INV Stock Locator Upload – Oracle EBS SQL Report

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

Overview

Create, update and delete Stock Locators.

Note on entering a locator as a concatenated segment value: if a segment value contains the KFF delimiter character or a backslash (), each such character must be preceded with a backslash. For example, with delimiter ‘.’, the segment value ‘1\4\4.’ must be written as ‘1\4\4.’

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, Organization Code, Subinventory, Locator, Locator From, Locator To, Locator Type, Status

Oracle EBS Tables Used

mtl_item_locations_kfv, mtl_parameters, mtl_material_statuses_vl, org_access_view

Report Categories

Enginatics, Upload

INV Physical Inventory Locator Count Sheet, INV Onhand Quantities, INV Items, INV Stock Locators, INV Item Upload, INV Item Upload 11i, CAC Inventory Lot and Locator OPM Value (Period-End), INV Serial Numbers, INV Subinventory Upload

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_Stock_Locator_Upload.xml
SQL Source INV Stock Locator Upload.sql
Full Report on Enginatics www.enginatics.com/reports/inv-stock-locator-upload/

Case Study & Technical Analysis: INV Stock Locator Upload

Executive Summary

INV Stock Locator Upload creates, updates and deletes stock locators in Oracle Inventory, the physical positions inside a subinventory where material is stored. It is used by warehouse managers, inventory administrators and implementation teams who need to set up rack, bin and aisle structures in bulk, change locator attributes such as picking order and capacity, or retire locators that are no longer used. The upload downloads existing locators with their segments, descriptions and physical attributes, and writes changes back through the standard locator setup.

Business Challenge

The Solution

Technical Architecture (High Level)

Primary Tables

Logical Relationships

  1. Each locator in MTL_ITEM_LOCATIONS_KFV is joined to its organization in MTL_PARAMETERS on the organization identifier.
  2. The organization is restricted to those returned by ORG_ACCESS_VIEW for the current responsibility.
  3. The locator status identifier is outer joined to MTL_MATERIAL_STATUSES_VL, so the status name is returned.
  4. The concatenated locator and each segment, up to twenty, are produced by the flexfield expansion function, with the number of segments limiting how many segment columns are populated.
  5. The locator type code is converted to its meaning through the Blitz Report™ meaning function.

Parameters & Filtering

Performance & Optimization

FAQ

Q: How do I enter a locator whose segment contains the delimiter character? A: Precede each delimiter character and each backslash in the segment value with a backslash. The description gives an example with a period as the delimiter.

Q: Can I end a locator without losing its record? A: Use the disable date column to end the locator, rather than deleting it, so the record remains for history.

Q: How do I see the capacity of each locator? A: The maximum units, maximum volume and maximum weight columns return the capacity values held on each locator, with their units of measure.

© Enginatics GmbH