What is Subinventory Quantities report?
Form: INVIRSIQ
Use the Subinventory Quantities Report to show inventory item quantities by subinventory.
Report Submission
Use the On-hand Quantity Reports or Submit Requests window and enter Subinventory quantities report in the Name field to submit the report.
Report Parameters
Item Range
Choose one of the following options:
Full listing : Report all subinventories.
Partial list by : Report only those subinventories for a inventory item partialrange of items.
Specific : Report only the subinventory you subinventory specify.
Items From/To
Enter a beginning and ending item to restrict the report to a range of items. You can enter an item here only if you enter Partial list by inventory item in the Item Range field.
Subinventory
Enter a subinventory for which to report on-hand quantity. You can enter a value here only if you enter Specific subinventory in the Item
Range field.
The original report has the following columns:
1.Item
2.Description
3.Rev
4.Locator
5.UOM
6.Quantity
Customization:
–Add the price of every item.
–Calculate the price*qty=total
–Add the subinventory total
This would give us the value of the sub inventory.
Tables Used:
1.MTL_SYSTEM_ITEMS (msi)
2.MTL_ONHAND_QUANTITIES_DETAIL (moqd)
3.MTL_SECONDARY_INVENTORIES (si)
4.MTL_ITEM_LOCATIONS (mil)
The tables added:
1.CST_ITEMS_COST (cs)
MTL_SYSTEM_ITEMS:
MTL_SYSTEM_ITEMS_B is the definition table for items. This table holds the definitions for inventory items, engineering items, and purchasing items. You can specify item-related information in fields such as: Bill of Material, Costing, Purchasing, Receiving, Inventory,
Physical attributes, General Planning, MPS/MRP Planning, Lead times, Work in Process, Order Management, and Invoicing. You can set up the item with multiple segments, since it is implemented as a flexfield.
Use the standard ’System Items’ flexfield that is shipped with the product to configure your item flexfield. The flexfield code is MSTK. The primary key for an item is the INVENTORY_ITEM_ID and ORGANIZATION_ID. Therefore, the same item can be defined in more than one organization. Each item is initially defined in an item master organization. The user then assigns the item to other organizations that need to recognize this item; a row is inserted for each new organization the item is assigned to.
Many columns such as MTL_TRANSACTIONS_ENABLED_ FLAG and BOM_ENABLED_FLAG correspond to item attributes defined in the MTL_ITEM_ATTRIBUTES table. The attributes that are available to the user depend on which Oracle applications are installed. The table MTL_ATTR_APPL_DEPENDENCIES maintains the relationships between item attributes and Oracle applications. Two unit of measure columns are stored in MTL_SYSTEM_ITEMS table.
PRIMARY_UOM_CODE is the 3-character unit that is used throughout Oracle Manufacturing. PRIMARY_UNIT_OF_MEASURE is the 25-character unit that is used throughout Oracle Purchasing. Items now support multilingual description. MLS is implemented with a pair of tables: MTL_SYSTEM_ITEMS_B and MTL_SYSTEM_ITEMS_TL.
Translations table (MTL_SYSTEM_ITEMS_TL) holds item descriptions in multiple languages. DESCRIPTION column in the base table (MTL_SYSTEM_ITEMS_B) is for backward compatibility and is maintained in the installation base language only.
MTL_ONHAND_QUANTITIES_DETAIL
MTL_ONHAND_QUANTITIES stores quantity on hand information
by control level and location.
MTL_ONHAND_QUANTITIES is maintained as a stack of receipt
records, which are consumed by issue transactions in FIFO order. The quantity on hand of an item at any particular control level and location can be found by summing TRANSACTION_QUANTITY for all records that match the criteria. Note that any transactions which are
committed to the table MTL_MATERIAL_TRANSACTIONS_TEMP are
considered to be played out as far as quantity on hand is concerned in Inventory transaction forms. All our Inquiry forms and ABC compile are only based on MTL_ONHAND_QUANTITIES.MTL_ONHAND_QUANTITIES has two columns, CREATE_TRANSACTION_ID and UPDATE_TRANSACTION_IDs to join to MTL_MATERIAL_TRANSACTIONS.TRANSACTION_ID the
transactions that created the row and the transaction that last updated a row.
MTL_SECONDARY_INVENTORIES
MTL_SECONDARY_INVENTORIES is the definition table for the
subinventory. A subinventory is a section of inventory, i.e., raw material, finished goods, etc. Subinventories are assigned to items (in a many to one relationship), indicating a list of valid places where this
item will physically exist in inventory.
Other attributes include general ledger accounts, demand picking order, locator type, availability type, and reservable type. You can also specify whether this subinventory is an expense or asset subinventory (ASSET_INVENTORY), or whether quantities are tracked
(QUANTITY_TRACKED).
MTL_ITEM_LOCATIONS
MTL_ITEM_LOCATIONS is the definition table for stock locators. The associated attributes describe which subinventory this locator belongs to, what the locator physical capacity is, etc.
The locator is a key flexfield. The Flexfield Code is MTLL.
CST_ITEMS_COST
CST_ITEM_COSTS stores item cost control information by cost type.
For standard costing organizations, the item cost control information for the Frozen cost type is created when you enter a new item. For average cost organizations, item cost control information is created when you transact the item for the first time.
You can use the Item Costs window to enter cost control information.
Where clause of the original report:
1.si.secondary_inventory_name = moqd.subinventory_code(+)
2.si.organization_id = moqd.organization_id (+)
3.moqd.organization_id = msi.organization_id(+)
4.moqd.inventory_item_id = msi.inventory_item_id (+)
5.moqd.locator_id = mil.inventory_location_id(+)
6.moqd.organization_id = mil.organization_id(+)
Additional where clause added for the customized report:
1.cs.inventory_item_id = msi.inventory_item_id
2.cs.organization_id = msi.organization_id
Leave a Reply
Want to join the discussion?Feel free to contribute!