I’ve got a version of this working. Thanks to everyone who has chipped in with their tips and background info.
I have run into a peculiar thing, though. When I view Time Phase for a part, I see a list of POs and then along with each PO is a Cancel PO, that effectively undoes all the POs. Those Cancelled POs don’t show up in PartDtl. Any idea where they are coming from?
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 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.
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.
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