Data Prep Sums Physical Inventory records
When Data Prep is run, it is summing two Physical Inventory gauges from the same day.
For cases where a tank is gauged more than once a day and the Physical Inventory screen is being used to track daily or monthly Ending Inventory, two gauges on the same day for the same tank should not be summed. If certain fields on the Physical Inventory records are not set correctly, the two gauges will be summed resulting in an incorrect Ending Inventory for the day/month in the Leveled Volumes and eventually Allocated Results.
🌱 Solution
The Data Prep process matches Physical Inventory records by using the following six fields:
Effective From, Meter Number, Measurement Type Category, Product, Unit, Ticket Type
If there are any Physical Inventory records where these values match, the Data Prep process chooses the record with the latest Effective From Time and uses that to create the Leveled Volumes. What we have seen happen before is Physical Inventory records are sometimes uploaded with different Ticket Types. This causes the match to fail and the Data Prep process handles two records on the same day as different types of records, then sums them if only the Ticket Type is different. To correct this issue:
Verify all Ticket Types for Physical Inventory records are NULL
Utilize an Effective From Time on all Physical Inventory records. In case there are two gauges for one day where the 6 matching fields do match, only the one with the latest time will be used to create a Leveled Volume.