@jblomstedt is correct… it took me YEARS to find this table (I guess I should have asked someone). PartDtl has a link back to each source record (Sales, Purchasing, jobs, transfer orders, etc) and can be used to build your own custom dashboard wtih this info. I have also used it, along with the MRP PEGGING tables to create an even more robust set of info in a dashboard so you can click on one of the PartDtl records, and see a single level pegging.
Tim - Any chance you could share the dashboard/BAQ as a file or solution?
We’re on 10.0.700.4, and finding randomly where time phase doesn’t match demand qty. We’d like to see proactively what parts are affected, before our monthly off-hours run of Refresh Part Bin QOH and Refresh Part Quantities and Allocations.
@askulte, Sorry, can’t distribute for multiple reasons, but mainly because it also involves mulitple UD Tables. Someday, I will write one that can be distributed… or better yet, Epicor will be putting this solution into base code (already promoting this).
Just wondering if you could share some ideas on how to build a dashboard similar to the time phase using the partdtl record and the multi-level pegging information like you mentioned that you have built in EPicor
Quantity = Case when PartDtl.RequirementFlag=0 then PartDtl.Quantity else (-1*PartDtl.Quantity) end
RunningSum=Sum(Quantity) over (partition by PartDtl.PartNum order by PartDtl.DueDate)+isnull(OnHand,0)
Hi @Gnewsom, I think this is a brilliant code. However, it doesn’t work well when the RunningSum value = 0, then the RunningSum value on the next line is not accurate.
I realized this post is from 5 yrs ago but I hope you have a solution for this issue?
I use the following in a calculated field in a subquery, passing
Partnum, Plant, OrderNum, OrderLine, OrderRelNum From OrderRel table
SUM( (case when PartDtl.RequirementFlag = 1 then PartDtl.Quantity * -1 else PartDtl.Quantity end)) OVER (PARTITION BY PartDtl.Company, PartDtl.Plant, PartDtl.PartNum ORDER BY PartDtl.DueDate, PartDtl.RequirementFlag,PartDtl.PartDtlSeq ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
The result I have is the same as TimePhase… as qty in stock available for a specific release row…
The subquery only have PartDtl table with the following filters:
Glad you found a solution. The way I wrote it, all the supply and demand on the same date gets lumped together due to the way ORDER BY works. Another possible way to fix it would be to include more variables in ORDER BY to break the ties i.e. job number and PO number.
Also, I wanted to add a filter where the BAQ results only show the primary bin number. but since this is an external field, I couldnt find it under the PartWhse table in the BAQ. Would you know how this can be done? @Hogardy
Just to add some to this thread that helped me in the past. I recently created a pivoted view of the time phase data as a forward outlook tools with the dates across the top. Using PartDtl is your best friend with Part Bin, however to get the balance correct for each month I needed to use a running total expression in SSRS.
Item to note this SSRS report is outside of Epicor.