INV Safety Stocks – Oracle EBS SQL Report
Oracle E-Business Suite SQL report from the Enginatics Library powered by Blitz Report™. Oracle EBS module: Inventory · all Inventory reports
Overview
Master data report for inventory safety stock with inventory org code, item, effective date, UOM,safety stock quantity, safety stock method, forecast , safety stock percent, service level and creation information.
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
Item, Organization Code
Oracle EBS Tables Used
hr_all_organization_units_vl, mtl_parameters, mtl_safety_stocks, mtl_system_items_vl, mtl_units_of_measure_vl, pa_projects_all, pjm_seiban_numbers, pa_tasks, org_organization_definitions, xxen_report_org_access
Report Categories
Related Reports
INV Onhand Quantities, ONT Orders and Lines, WIP Entities, ONT Order Holds, INV Material Transactions, PPF_WP3_OM_DETAILS, MRP Item Forecast
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 | INV Safety Stocks 18-Jan-2018 222727.xlsx |
| Blitz Report™ XML Import | INV_Safety_Stocks.xml |
| SQL Source | INV Safety Stocks.sql |
| Full Report on Enginatics | www.enginatics.com/reports/inv-safety-stocks/ |
INV Safety Stocks - Case Study & Technical Analysis
Executive Summary
The INV Safety Stocks report is a master data report that lists the currently defined safety stock levels for items in an organization. It is the “System of Record” view, showing what the planning engine (MRP/ASCP) sees as the required buffer.
Business Challenge
Planners need to verify that the system is using the correct safety stock figures.
- Verification: “I uploaded the new safety stocks, but did they take effect?”
- Method Visibility: “Is this item using a fixed quantity (e.g., 100 units) or a dynamic calculation (e.g., 5 days of supply)?”
- Audit: “Why did MRP suggest we buy so much? Oh, the safety stock was set to 10,000 by mistake.”
Solution
The INV Safety Stocks report dumps the contents of the safety stock table. It shows the method, quantity, and effectivity dates for each item.
Key Features:
- Method Display: Shows if the safety stock is User Defined (MRP Planned) or Non-MRP Planned.
- Time Phasing: Shows the effective date range for the safety stock entry.
- Project Details: Includes Project and Task references for project-specific safety stock.
Technical Architecture
The report is a direct query of the safety stock definition table.
Key Tables and Views
MTL_SAFETY_STOCKS: The primary table.MTL_SYSTEM_ITEMS_VL: Item details.MTL_PARAMETERS: Organization details.
Core Logic
- Retrieval: Selects all records from
MTL_SAFETY_STOCKSfor the given organization. - Decoding: Translates the
SAFETY_STOCK_CODEinto readable text (e.g., 1 = User Defined, 2 = MRP Calculated). - Formatting: Presents the data in a list format suitable for review.
Business Impact
- Data Quality: Helps identify outliers (e.g., negative safety stocks or impossibly high numbers).
- Planning Accuracy: Ensures that the inputs to the planning engine are correct.
- Transparency: Provides visibility into one of the key drivers of inventory investment.
Useful Links
- Blitz Report™ – World’s Fastest Oracle EBS Reporting Tool
- Oracle Discoverer Replacement – Import Worksheets into Blitz Report™
- Oracle EBS Reporting Toolkits by Blitz Report™
- Blitz Report™ FAQ & Community Q&A
- Supply Chain Hub by Blitz Report™
- Blitz Report™ Customer Case Studies
- Oracle EBS Reporting Blog
- Oracle EBS Reporting Resource Centre
© Enginatics GmbH