Does Epicor have a canned BAQ for the Time Phase Inquiry?
Or does anyone have a BAQ that replicates it? I want to use my resources before I spend time building up a BAQ.
I am trying to make a custom report and the Time Phase Inquiry is my starting point.
Thanks!
Take a look at the PartDtl table. That is where almost all of this info is from.
@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.
Thanks a ton!
@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).
Hi Tim,
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
Hi Jay and Andrew,
Here’s how I recreated a rudimentary timephase:
Add criteria to PartDtl Type <> Sub
Calculated Fields:
OnHand= sum(PartBin.OnhandQty)
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)
Result:
Thanks Gary! I am going to give it a try.
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?
Here’s what I got:
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:
and the release table has the following:
Hope it helps…
Pierre
@Hogardy Hi Pierre,
I modified the Original code with yours and now I have what I wanted
RunningSum=Sum(Quantity) over (partition by PartDtl.PartNum order by PartDtl.DueDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)+isnull(OnHand,0)
Thanks a lot for your help !
Hi Himal,
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.
Gary
Thanks @Gnewsom Gary,
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
Thanks
Primary bin can be found with PlantWhse which show for each plant, the Primary warehouse, then via PlantWhse you get the primary bin…
Pierre
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.
Hi thanks that sorted it
Hi Sorry to add on but it relevant to this topic.
Does anybody know where the table is for source suggestions as highlighted in the image.
I was just working on a project and needed to find this as well. TimePhas is empty
PartDtl.SourceFile
"Indicates the record type that created this record.
JH-JobHead, JA-JobAsmbl, JM-JobMtl, JO-JobOper, PO-PoRels, OR-OrderRels, FC-ForCast "
Hopefully that helps a bit. I imagine there is some other calcs that go on to translate.
TimephaseSQL.txt (7.9 KB)
Here’s a SQL I put together a number of years ago and seems to do the trick.
