I would love to see the RDL that does time phase for specific part numbers. Would greatly appreciate it. I am working on such a project right now.
1 Like
I got this working, here are some updates/improvements
Add PartDtl.AssemblySeq to the group by
Add PartDtl.Mtl to the group by, sometimes the same part is on the same bom twice
Not sure how it’s sorted because everything I try will not agree with time phase somewhere. So I just do DueDate followed by PartDtl.Requirement Flag to put POs first.
Time phase will not change running total if the job material is purchase direct so my Quantity calc is
Case
when PartDtl.StockTrans=0 then 0
when PartDtl.RequirementFlag=0 then PartDtl.Quantity
else (-1*PartDtl.Quantity)
end
i would definitely appreciate it.
1 Like
Although a lot of time has passed,
Would love to see this.
please. Thanks,
1 Like
I would love a copy of your RDL file if you still have it available. I am hoping to try to put together a re-created Time Phase report and dashboard that I would be happy to share out if I get it working.
1 Like
We have been using this for daily reporting, but no warranty. Please validate the data. There are some cases where Time Phase calculates Sales Orders/Jobs before the PO Qty on Lines for Running Total. I argued with Epicor about this but, for the most part you should get the same results as Time Phase from epicor.
Here is the RDL. You will have to define which part numbers you want to show in the parameter under default values, or modify it another way to make it produce the data you want.
TimePhaseRDL.zip (7.6 KB)
BONUS BAQ
I haven’t used this file for a long time, i dont know if it works or not.
kpEngineAvail_0517 (2021).baq (33.2 KB)
2 Likes
Here is an example of the PO being sorted incorrectly in Epicor Time Phase for calculating running total, but unable to win this argument with epicor.
1 Like
I dont totally understand your issue with the sort. It is designed to sort by Due Date. you have five POs arriving on the same date. Are you wanting it to also sort by the PO number too? I would probably agree that this is not normal to have multiple POs arriving on the same day, but also, this doesn’t drive any software behavior, but is a display issue at best.
I’ll find a better example but look closely at the PO vs Line # and Release #
Our purchasing department didnt like how it reorders PO numbers with the same dates. Newer PO should be at the bottom, which is what happens when you sort by ‘Source’ but the system has its own method for Balance (running total)
I believe the method epicor uses is Due Date-> PO Release # → PO Line # → PO Number
i would really appreciate it.
Gary, I am so appreciative of the detail you provided here. We are new to Epicor. I don’t know SQL well and things like this make magic happen. I created a Time Phase dashboard using the query you outline above. Made some happy co workers. My boss needs to create a predictive inventory forecast and this Time Phase query is being used as the basis for that work. There ia a gap that I am hoping you may be able to help me solve. There are some parts that are inventory that do not have any reciept or requirments. If you look at them in Time Phase Inquirey they just show on hand qty with no future activity. How could I get those parts to show in this query with their on hand qty shown? Again, thank you!
Does anyone have any ideas on this question? ANy help would be greatly appreciated.
The main criteria for a Time Phase is having demand somewhere. I think you could probably add parts that do not have any demand into there, but other tools would be better at taking care of this.
Part history transaction will tell you where the part came (purchased, manufactured, ADJ-QTY…) from or where it has been issued in the past.
With Gary’s tables, PartDtl would have to have records in it otherwise it will not show any information from the PartBin table. If you switch the Left joint to a Right join, you will probably get the data you want, and a lot more you do not want.
2 Likes
To have part show that do not have any requirements start your BAQ with the Part Table doing a left outer join to the PartDtl Table. You could do the same with the Part Table connecting to inventory.
I tried a variety of versions of this and no luck. Thank you for offering a suggestion.
I tried a variety of versions of this and had no luck. Feel like I am on a mystical treasure hunt. Thank you for offering a suggestion.
If you’re looking to add an extra row, you’ll need a union. You just need the same number and type of columns and then you can add extra rows. So if you want a row in your query with just on hand information, Do a union with a subquery that uses the part bin table and groups and sums the quantities to give you an on hand quantity.
1 Like
Thank you. Stuydying the manual on how to do Unions - never done that before. Pictures are greatly appreciated.
Thank you! Working on creating a union.