Hello everyone,
Is it possible to make a BAQ that can inquire the Time Phased Inquiry of all parts at once?
Currently, Epicor’s Time Phased Inquiry must be inquired for each part. So I was trying to make a BAQ where I could see all the parts at once.
I want to make something like the example below. But there is no DB Field for that field, so I don’t know how to create it. I sincerely ask for your help.
The PartDtl table is the best place to started. You can get all supply and demand records here. You’ll need to get creative in your BAQ to include a Qty On Hand row and calculating the running total.
We developed this from PartDtl (it’s in Epicor, too, but it looks way better in color). I made it, but the idea was from someone smarter. Obviously I blanked out sensitive info for the pic.
This aggregates over 2 million rows of data into, what, 2 dozen columns of “when are we in trouble for this part?”
In hindsight, the columns that I called “supply” are confusing. It’s really the incoming qty and you add that to the OH to get total supply at that point in time. Of course, you could do the opposite and pre-add it. I like it this way because it shows me what parts have no POs for them whatsoever and I can say “What are you doing?! Do you think this part will buy itself?” Anyway, I digress, it’s all subjective, I just wanted you to know why my math stinks.
My point in sharing this is, this is the eternal challenge - users want to know everything but then they hate information overload. I thought this was a really good balance of the two.
I guess there’s nothing sensitive in it, so I’ll share it.
Some quick thoughts:
I archive a copy every single week. So that when people say “Epicor never told me I needed to order this,” I will say, “Oh really? Let’s go see.” And oh are they surprised.
DemandAndSupply_SQL-friendly5.baq (91.7 KB)
There is Time Phase in Epicor which your screen shot shows but also Time Phased Material Report. This allows you to run open i.e. no filter and can be output to CSV:
I am not sure of your BAQ skills but I am definitely having a look at @JasonMcD BAQ as well.
Thank you for sharing your valuable information. When I am active in this community, I am most happy to meet someone who shares query sources like this. Of course, there are cases where we cannot share the sensitive information. Sharing these things seems to be Epicor’s greatest strength. Anyway, I imported your shared information, and it seems that we can use it with a little modification by me.
Your words “users want to know everything but then they hate information overload”
This is a word that resonates with me so much.
Hello,
I have created a BAQ with your Query and it is useful. I have a question for your BQA Query. Your Query only shows the parts that have Demand or Supply.
How can I make the parts that do not have Demand or Supply appear as well?
I’m a BAQ beginner, so I’m sorry to bother you.
You need to change the join. I think it would be a “right inner join” in SQL parlance, but at some point in (10.2.x, I think) Epicor changed the wording of this dropdown to plain English.
For extra credit, I personally like to change the order of the tables where 1 is the main thing (Part in this case). I have no idea if that matters or not.
Also, you need to change the part number field. I apparently grabbed it from PartDtl, but since you want all parts, you’ll need to grab it from the Part table.
Sorry to keep bothering you. Thanks to your advice, I made all the parts appear.
By the way, only parts with supply or demand show OH Qty.
How can I make On-Hand Qty appear even for parts with no supply or demand?
There is some kind of on-hand subquery in there already. So you can just add it (again) to the top-level. Join it to Part like last time (“all rows from part”). You’ll have to create the Table Relations (Company and PartNum). And then show the new OH qty field and hide the old one.
“you can just add it (again) to the top-level.”
How do I get this to work?
