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.