Inventory As Of Inquiry
- Janice Dannenberg (Unlicensed)
- Emma Greenwood
- Tom Williams
The purpose of this page is to search and view Inventory Lot balances as of a specific date. An Inventory Lot defines the quantity of product that is unique by combination of Item, Site, Production Batch, Warehouse Lot and Owner.
This inquiry also contains columns for each Attribute that is set up for the 'Items' attribute type, 'Production Batches' attribute type, and the 'Warehouse Lots' attribute type. Because these are user defined, they are not included in the default view but can be added to a saved layout. Attributes are not included in the help documentation.
Software Version: 2024.12.16 and newer
Page Header
Search
| Search Options | Description |
|---|---|
| As Of Date | Used to select the transaction date. All transactions occurring on or before the specified date will be included. This is a date picker that is required to return results. It defaults to the current date. |
| Item | Used to select one or more Items. This is a multi-select standard lookup that is not required to return results. It defaults as blank. Item IDs are the unique identifier for Items. The lookup contains a list of all Items in the Items master file. |
| Item Class | Used to select one or more Item Classes. This is a multi-select standard lookup that is not required to return results. It defaults as blank. Item Classes are used to identify default values that will be applied to new Inventory Items and to group Items together for reporting purposes. The lookup contains a list of all Item Classes. |
| Lot Owner | Used to select one or more inventory Owners. This is a multi-select standard lookup that is not required to return results. It defaults as blank. Owners are used to identify who owns the inventory. Additional Owners are typically added for companies that do custom processing and need to keep that inventory separate from their own. Owner is one of the 5 components that make up a unique Inventory Lot and it is required on all Inventory Lots. The lookup contains a list of all Lot Owners. |
| Search | A non-required text box that defaults to blank and is used to search across multiple fields. When the page is 'Refreshed', the data returned will be limited by what was included in the Search box (in addition to any other Search Options). Refer to this FAQ for advanced search options. The Search field can be used to search across the following fields:
|
| Site | Used to select one or more Sites. This is a multi-select standard lookup that is not required to return results. It defaults as blank. Sites represent Plants, Warehouses, Distribution Centers, or virtual objects that are used to store and/or maintain perpetual inventory balances. The lookup contains a list of all active Sites. |
| Summarize By | Used to specify how the search results will be grouped. This is a single-select standard lookup that is required. It defaults to "Inventory Lot". The lookup contains the following options:
|
| Saved Searches |  Displays the saved searches lookup and related icons. This tool allows users to select previously saved search options, create a new set of search options, save changes to existing search options, forget/delete saved search options and/or assign a saved search option as the default for the page. Once changes are made to the search options, the user can click the Save Options icon and save the changes. Saving search options will also save the preferences for 'Auto Refresh on Open' and 'Auto Hide Filters'. The new search option can also be set as the default. Default searches can be removed by clicking the 'Set as Default' button again when the current default search is selected. Refer to this FAQ for advanced search options. |
Page Detail
| Toolbar Icons | Description |
|---|---|
| Column Filter Show/Hide |
|
| Excel |  Exports the contents of the grid to an Excel file that will automatically download. The contents that export are those that are visible to the user. Therefore, if the user has filtered the view, or excluded columns, only the contents in the grid will be exported. |
| Favorites |   Clicking on the star icon when it is an outline, adds the page to the 'My Shortcuts' tab and fills in the star. Clicking on the star icon when it is filled in (solid color), removes the page from the 'My Shortcuts' tab and returns the icon to an outline.
|
| Group By Show/Hide |  Toggles between displaying and hiding a row in the grid above the column headers to enable grouping by one or more column(s). Once displayed, users may drag and drop column headers to group by a specific column. Once the grid is grouped by a column(s), a subtotal footer row will display in the grouping for columns that have a page footer total. |
| Help |  Opens the online WIKI help page for the current page. |
| Quick Column Filter |  Used to filter across all columns in the grid. When the user types text into the Quick Column Filter text box, the grid's Filter Row will be automatically hidden. The text in the Quick Column Filter text box automatically replaces any existing Filter Row filters. The results of the data immediately refresh as you enter text. |
| Refresh |
|
| Saved Layouts |
Please note that existing saved grid layouts will display the Filter Row as hidden by default. |
| Search Options |  Toggles between displaying and hiding the Search Options portion of the page. |
| Fields | Description |
| Actual Cost | Displays the Actual Cost, which is the sum of Actual Base Cost and Actual Layer Costs, assigned to the lot. This can be edited using the Mass Update dialog box on the Update Costs List View or the 'Update Costs From BOM' toolbar icon on the Update Costs List View. The Actual Cost reflects the cost that will post to the General Ledger for Items with a 'Costing Method' of "Active". For some items this may be set from a default or "standard" cost rate and for other items this may reflect that actual amount paid to purchase or incurred to produce the product. For each individual LotSK, this is calculated as the [Actual Base Cost Rate] + sum of all the [Actual Cost Layer Rates]. Database Field: ERPx_IMLotCostHistory.Cost |
| As Of Date | Identifies the cutoff transaction date to view Inventory balances that occur on and before this date. This can be edited from the 'As Of Date' in the Search Options. Database Fields: ERPx_IMTransactionHeader.TransactionDate, ERPx_SOorderHeader.OrderDate |
| Cost Group | Displays the cost group attached to the item. This can be edited from the 'Cost Group' property on the 'Properties' tab of the Item Record View. Cost Groups are used to associate items that share the same cost model. Database Field: ERPx_IMCostGroup.CostGroupID |
| Date Produced | Displays the date on which the Production Patch was produced. This can be edited from the 'Batch Date' field on the Properties tab of the Production Batch record view. When the 'Summarize By' inquiry parameter is set to "Item", "Item and Owner", "Item and Warehouse Lot" or "Item, Owner and Warehouse Lot", this field will be displayed as "**Not Included". Database Field: ERPx_IMProductionLot.DateProduced |
| Extended Actual Cost | Displays the Extended Actual Cost, which is calculated as [Actual Cost] * [On Hand Unit/Weight]. This can be edited using the Mass Update dialog box on the Update Costs List View or the 'Update Costs From BOM' toolbar icon on the Update Costs List View. This can also be edited by updating On Hand Unit or On Hand Weight quantities. This value is not stored in the database. The Actual Cost reflects the cost that will post to the General Ledger for Items with a 'Costing Method' of "Active". For some items this may be set from a default or "standard" cost rate and for other items this may reflect that actual amount paid to purchase or incurred to produce the product. For each individual LotSK, this is calculated as the [Actual Base Cost Rate] + sum of all the [Actual Cost Layer Rates].
|
| Extended Lower of Cost or Market Cost | Displays either the Extended Standard Cost or the Extended Market Cost, whichever is lower. This can be edited using the Mass Update dialog box on the Update Costs List View or the 'Update Costs From BOM' toolbar icon on the Update Costs List View. This can also be edited by updating On Hand Unit or On Hand Weight quantities. This value is not stored in the database. The Standard Cost reflects the pre-determined "target" value for the inventory. For each individual LotSK, this is calculated as the [Standard Base Cost Rate] + sum of all the [Standard Cost Layer Rates]. Standard Costs are typically managed in companies where items have a planned or target cost amount and any variances to actual costs are reflected as expenses on the P&L and not in the Inventory Balance Sheet accounts. The Market Cost reflects the estimated "market" value for the inventory. Market Costs are typically set by taking Actual costs and adding additional components such as delivered freight, brokerage/rebate amounts, and desired profit margin. For each individual LotSK, this is calculated as the [Market Base Cost Rate] + sum of all the [Market Cost Layer Rates]. Market Costs are typically maintained in companies where costs and/or margin needs to be shown/used in Sales Order based on the current market value of the item, not the Actual costs. |
| Extended Market Cost | Displays the Extended Market Cost, which is calculated as [Market Cost] * [On Hand Unit/Weight]. This can be edited using the Mass Update dialog box on the Update Costs List View or the 'Update Costs From BOM' toolbar icon on the Update Costs List View. This can also be edited by updating On Hand Unit or On Hand Weight quantities. This value is not stored in the database. The Market Cost reflects the estimated "market" value for the inventory. Market Costs are typically set by taking Actual costs and adding additional components such as delivered freight, brokerage/rebate amounts, and desired profit margin. For each individual LotSK, this is calculated as the [Market Base Cost Rate] + sum of all the [Market Cost Layer Rates]. Market Costs are typically maintained in companies where costs and/or margin needs to be shown/used in Sales Order based on the current market value of the item, not the Actual costs.
|
| Extended Standard Cost | Displays the Extended Standard Cost, which is calculated as [Standard Cost] * [On Hand Unit/Weight]. This can be edited using the Mass Update dialog box on the Update Costs List View or the 'Update Costs From BOM' toolbar icon on the Update Costs List View. This can also be edited by updating On Hand Unit or On Hand Weight quantities. This value is not stored in the database. The Standard Cost reflects the pre-determined "target" value for the inventory. For each individual LotSK, this is calculated as the [Standard Base Cost Rate] + sum of all the [Standard Cost Layer Rates]. Standard Costs are typically managed in companies where items have a planned or target cost amount and any variances to actual costs are reflected as expenses on the P&L and not in the Inventory Balance Sheet accounts.
|
| Item | Identifies the Item, displayed as Item ID: Item Description. This can be edited from the 'Item Description' property on the 'Properties' tab of the Item Record View. Database Field: ERPx_IMItem.ItemDescription
|
| Lower of Cost or Market Cost | Displays either the Standard Cost or the Market Cost, whichever is lower. This can be edited using the Mass Update dialog box on the Update Costs List View or the 'Update Costs From BOM' toolbar icon on the Update Costs List View. The Standard Cost reflects the pre-determined "target" value for the inventory. For each individual LotSK, this is calculated as the [Standard Base Cost Rate] + sum of all the [Standard Cost Layer Rates]. Standard Costs are typically managed in companies where items have a planned or target cost amount and any variances to actual costs are reflected as expenses on the P&L and not in the Inventory Balance Sheet accounts. The Market Cost reflects the estimated "market" value for the inventory. Market Costs are typically set by taking Actual costs and adding additional components such as delivered freight, brokerage/rebate amounts, and desired profit margin. For each individual LotSK, this is calculated as the [Market Base Cost Rate] + sum of all the [Market Cost Layer Rates]. Market Costs are typically maintained in companies where costs and/or margin needs to be shown/used in Sales Order based on the current market value of the item, not the Actual costs. Database Field: ERPx_IMLotCostHistory.StandardCost or ERPx_IMLotCostHistory.MarketCost |
| Market Cost | Displays the Market Cost, which is the sum of Market Base Cost and Market Layer Costs, assigned to the lot. This can be edited using the Mass Update dialog box on the Update Costs List View or the 'Update Costs From BOM' toolbar icon on the Update Costs List View. The Market Cost reflects the estimated "market" value for the inventory. Market Costs are typically set by taking Actual costs and adding additional components such as delivered freight, brokerage/rebate amounts, and desired profit margin. For each individual LotSK, this is calculated as the [Market Base Cost Rate] + sum of all the [Market Cost Layer Rates]. Market Costs are typically maintained in companies where costs and/or margin needs to be shown/used in Sales Order based on the current market value of the item, not the Actual costs. Database Field: ERPx_IMLotCostHistory.MarketCost |
| On Hand Units | Displays the On Hand units of the item at the corresponding Site or Inventory Lot. This can be updated when units for an item on an inventory transaction has been Posted or units for an allocated item on a sales order has been Posted. This is a calculated field and not stored in the database. The On Hand balance is intended to reflect the quantity of an Inventory Lot that is physically in the facility. On-Hand balances are maintained for all items with an 'Item Type' of "Sales Inventory" whether they are Lot Tracked or not. On Hand balances are updated when the 'Status' of Inventory and Sales transactions is changed to "Ready To Post". Note: This field does not take the "Include Shipped & Approved Sales Orders in On Hand balances" Inventory Preference into account when calculating On Hand balances. |
| On Hand Weight | Displays the On Hand weight of the item at the corresponding Site or Inventory Lot. This can be updated when weight for an item on an inventory transaction has been Posted or weight for an allocated item on a sales order has been Posted. This is a calculated field and not stored in the database. The On Hand balance is intended to reflect the quantity of an Inventory Lot that is physically in the facility. On-Hand balances are maintained for all items with an 'Item Type' of "Sales Inventory" whether they are Lot Tracked or not. On Hand balances are updated when the 'Status' of Inventory and Sales transactions is changed to "Ready To Post". Note: This field does not take the "Include Shipped & Approved Sales Orders in On Hand balances" Inventory Preference into account when calculating On Hand balances. |
| Owner Name | Identifies the Lot Owner of the Inventory Lot. This comes from the 'Owner' field for a line item on the Items grid on an Inventory transaction. This is not editable after the transaction has been Saved. When the 'Summarize By' inquiry parameter is set to "Item" or "Item and Warehouse Lot", this field will be displayed as "**Not Included". Owners are used to identify who owns the inventory. Additional Owners are typically added for companies that do custom processing and need to keep that inventory separate from their own. Owner is one of the 5 components that make up a unique Inventory Lot and it is required on all Inventory Lots. Database Field: ERPx_IMLotOwner.OwnerName |
| Production Batch | Identifies the Production Batch ID of the Lot. This can be edited from the 'Production Batch' field on the Properties tab of the Production Batch record view. When the 'Summarize By' inquiry parameter is set to "Item", "Item and Owner", "Item and Warehouse Lot" or "Item, Owner and Warehouse Lot", this field will be displayed as "**Not Included". A Production Batch is the identifier that contains all the Traceability information (Production Batch Properties and Attributes) for Lot Tracked Items. This number stays attached to the product, even if it is transferred to another Site. Production Batch is one of the 5 components that make up a unique Inventory Lot and it is required when entering a transaction line for a Lot Tracked Item. This value will be blank and/or disabled for all non-Lot Tracked items on screens that include a Production Batch field. An Inventory Lot is the most granular level that item balances (quantities of Units and/or Weight) are stored within NorthScope. Inventory Lots are created for all Inventory Items assigned an Item Type of “Inventory', regardless of whether the Item is Lot Tracked (i.e., balances are maintained by Production Batch) or the Site containing the product is 'Warehouse Lot' tracked or not. An Inventory Lot is unique by combination of:
Database Field: ERPx_IMProductionLot.ProductionLotID |
| Site | Identifies the unique Site ID (warehouse ID) of the Site the transaction, shipment, item, or lot is associated with. This comes from from the 'Site' field on the 'Home' tab of the Inventory transaction or the 'Default Site' field on the 'Home' tab of the Sales Order transaction. This is not editable after the transaction has been Saved. Sites represent Plants, Warehouses, Distribution Centers, or virtual objects that are used to store and/or maintain perpetual inventory balances. Database Fields: ERPx_MFSite.SiteID, ERPx_IMLotBalance.SiteSK |
| Standard Cost | Displays the Standard Cost, which is the sum of Standard Base Cost and Standard Layer Costs, assigned to the lot. This can be edited using the Mass Update dialog box on the Update Costs List View or the 'Update Costs From BOM' toolbar icon on the Update Costs List View. The Standard Cost reflects the pre-determined "target" value for the inventory. For each individual LotSK, this is calculated as the [Standard Base Cost Rate] + sum of all the [Standard Cost Layer Rates]. Standard Costs are typically managed in companies where items have a planned or target cost amount and any variances to actual costs are reflected as expenses on the P&L and not in the Inventory Balance Sheet accounts. Database Field: ERPx_IMLotCostHistory.StandardCost |
| Summarize By | Identifies how the search results are grouped. This comes from the 'Summarize By' inquiry parameter field in the 'Filters' tab. This is not stored in the database. |
| Unit UOM | Identifies the default inventory unit of measure that the units are stored in (boxes, Bags, Cases, Totes, etc.) for the item. This field can be edited from the 'Inventory Unit UOM' property on the 'Properties' tab of the Item record view. A UOM is a quantity used as a standard of measurement. It identifies how much makes up "1" of the measurement. Examples include Bag, Box, Each, Kg, Lb, ext. Database Field: ERPX_IMItem.DefaultIMUnitUOMSK |
| Warehouse Lot | Identifies the Warehouse Lot to which the selected item or inventory lot belongs. This can be edited from the 'Warehouse Lot' field on the Warehouse Lots list view. When the 'Summarize By' inquiry parameter is set to "Item" or "Item and Owner", this field will be displayed as "**Not Included". A Warehouse Lot (aka Storage Lot) is used by 3rd party distribution sites to identify a unique quantity of product they are storing. Unlike a Production Batch number that never changes, Warehouse Lots will change as product is transferred to other Sites and/or transferred into exiting Warehouse Lots within the same Site. The 'Warehouse Lot' is one of the 5 components that make up a unique Inventory Lot and it is required when entering a transaction line on a transaction for a Warehouse Lot Tracked Site. This value will be blank and/or disabled for all non-Warehouse Lot Tracked Sites on screens that include a Warehouse Lot field. An Inventory Lot is the most granular level that item balances (quantities of Units and/or Weight) are stored within NorthScope. Inventory Lots are created for all Inventory Items assigned an Item Type of “Inventory', regardless of whether the Item is Lot Tracked (i.e., balances are maintained by Production Batch) or the Site containing the product is 'Warehouse Lot' tracked or not. An Inventory Lot is unique by combination of:
Database Fields: ERPx_IMWarehouseLot.WarehouseLotID, ERPx_IMLotBalance.WarehouseLotSK |
| Weight UOM | Identifies the default inventory unit of measure that the weight is stored in (Lbs, Kg, Oz, Ton, etc.) for the item. This field can be edited from the 'Inventory Weight UOM' property on the 'Properties' tab of the Item record view. A UOM is a quantity used as a standard of measurement. It identifies how much makes up "1" of the measurement. Examples include Bag, Box, Each, Kg, Lb, ext. Database Field: ERPx_IMLotBalance.WeightUOMSK |
| Year Produced | Displays the year which the Production Patch was produced. This can be edited from the 'Batch Date' field on the Properties tab of the Production Batch record view. When the 'Summarize By' inquiry parameter is set to "Item", "Item and Owner", "Item and Warehouse Lot" or "Item, Owner and Warehouse Lot", this field will be displayed as "**Not Included". Database Field: ERPx_IMProductionLot.DateProduced |
