Time Phase Inquiry BAQ

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!

1 Like

Take a look at the PartDtl table. That is where almost all of this info is from.

1 Like

@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.

3 Likes

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!

1 Like

@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).

2 Likes

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:
image

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:

9 Likes

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:
image

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

4 Likes

@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

1 Like

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

1 Like

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.

1 Like

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.

1 Like

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.

1 Like

TimephaseSQL.txt (7.9 KB)
Here’s a SQL I put together a number of years ago and seems to do the trick.

2 Likes