Understanding historical inventory valuation for the data warehouse

The data warehouse ETL process includes data marts, two of which are a stores daily snapshot (SDS) and a parts daily snapshot (PDS). The SDS data mart captures daily summaries of the value of your inventory by store, organization, and part class. The PDS data mart captures daily summaries of the value of your inventory by part. However, PDS only captures daily summaries for the parts for which you have indicated to save historical data using the Save History check box on the Record View tab of the Parts page when creating parts.

This section describes the manner in which the SDS and PDS derive historical inventory valuation data for the data mart when executing the initial data load for the SDS and PDS and how the ETL process calculates historical inventory valuation for the data warehouse for different inventory scenarios.

Inventory valuation is normally calculated using the sum of stock value as it exists at the time that the ETL process is executed. Historical stock values are derived using stock transactions in reverse order and backing out the changes to stock quantities and prices. However, some historical stock value information is either not available or is impractical for use in calculating historical values.

Note: Because of the manner in which historical inventory value must be determined for the data warehouse, the calculation of historical inventory value for some of the inventory scenarios may result in an approximate value that is as close to the exact value as possible.

Updating the invoice price

In EAM, pricing updates are determined based on the PRICETIM installation parameter. If PRICETIM is set to I, then a new price is calculated upon approval of an invoice for the stock item.

When updating the invoice price, the ETL process treats data from schemas set to update prices at invoice time as though they were set to update prices upon receipt.

The data warehouse updates prices upon receipt due to the complexity of synchronizing transactions with invoices while re-establishing historical stock levels and prices.

Note: Large gaps between receipt and invoice time may result in inaccuracies in the precise time the price update occurred, which also affects the value of inventory in stores during that period. However, because daily inventory valuations are calculated at the store, part, and class level, small differences in price will not have a significant effect unless there is a large price difference on an expensive item or a large quantity of that item.

Recalculating pricing based on the average price type

Regardless of the selected price type for the store (Average, Standard, Last Price, LIFO, or FIFO), the ETL process assumes Average price type. Storeroom materials are priced at the storeroom level in EAM based on the setting of the PRICETYP installation parameter.

Accurate historic data for price types other than Average is not available. As receipt transactions are backed out, the part price is recalculated using the average pricing method, but the price is adjusted to the issue price whenever an issue or return to stock transaction occurs, unless you have selected LIFO or FIFO as the price type for a part. If you have selected LIFO or FIFO as your price type, SDS and PDS use average price for all transactions, including issues and returns, to ensure the accuracy of stock prices for transactions dates occurring a long time from the current system date.

Recalculating values for repairable spare parts

No historical data is available to calculate previous values for repairable spare parts that are currently being repaired internally and the value of the repairable spare currently being repaired by a supplier. These values are null for historical dates.

Recalculating prices for unapproved receipt transactions

EAM assumes a RECV transaction in the R5TRANSLINES table for parts received from a purchase order for which the receipt is not approved. However, EAM does not update the stock quantity and price until the receipt is approved. If there is a gap in time between the receipt and the approval of the receipt, it is possible that the quantities and prices inserted into the parts work table at the beginning of the process will not match the transaction. Therefore, only approved receipt transactions are reversed.

Adjusting stock quantities of child assets in an asset hierarchy

Normally if an asset is associated with dependent child assets in a hierarchy for which costs are tracked by asset, there are also spare parts associated with the assets in the hierarchy. EAM adjusts the stock quantity of the child assets when the parent is issued to a work order, and the structure of the assets is stored in the R5STRUCTURES table. However, the R5STRUCTURES table only shows the current relationship between the assets, not any historical relationship. Therefore, the ETL process does not consider the object hierarchy structure when calculating historical stock values.