I’ll make this quick. I am using QuoteHdr to pull the most recent Quote Number. I’m using QuoteHdr to pull the DateQuoted. I’m using QuoteDtl to pull the QuoteComment, QuoteLine and PartNum. When I run this BAQ I receive the most recent quote and date. BUT - When I add the Table QuoteQty in order to pull the SellingQuantity and DocUnitPrice I receive several quote numbers for particular parts.
Why is QuoteQty acting this way?
What does your join look like between QuoteDtl and QuoteQty?
Right now QuoteQty is a subquery the join between QuoteQty and QuoteDtl is Calculated_LatestQTNum from QuoteQty to QuoteDtl1’s QuoteNum.
Matching rows from Qty and QuoteDtl1
Are there more than 1 QuoteQty record per QuoteDtl? I feel like the QuoteQty is supposed to give you different quantity breaks to quote on the same quote line. So by nature it would not be a 1:1 relationship. It would be 1:many
I’m having a hard time answering your question. Yes, there can be multiple qty’s on a quote for a part. But what am I doing wrong when the BAQ returns multiple Quote numbers for a part rather than the last quote made for a part?
It is 1:many as you may have multiple records in QuoteQty for 1 in QuoteDtl. Why not use fields from QuoteDtl and get rid of table QuoteQty from your query ? You could use SellingExpectedQty or OrderQty for quantities and ExpUnitPrice for unit price.
QuoteQty represents the Quantity Breaks on the Quote. If you link to this table without any other criteria applied, you will return multiple records if you have multiple quantity breaks per line.
I want to make sure I understood this right. I assumed you meant that you returned several of the same quote numbers for a given part. As in appearing to be duplicates… However, if you mean that you are getting several different quote numbers for the same part, I would check your join and make sure you have the joins correct. And/or, another anomaly that happens sometimes is that it will sort itself in a way that you wouldn’t have expected… such as instead of by quote number and line, by part number and line. Thus giving the appearance of many quote numbers per quote line… All of this is just to confirm what your specific problem is.
It might be better to tell us what you’re trying to do and then we can work backward to meet you where you’re at. As @Dragos suggested, you may not need the QuoteQty table at all (just depends on your goal).
Unfortunately fields SellingExpectedQty and ExpUnitPrice are not populated fields in our environment. The Qty and Price are within the QuoteQty table.
Without SellingExpectedQty, how would you know which Quantity Break to reference? It’s one thing if you only ever have a single one, but if there are multiples, you need some way to choose which one you want.
We use the QuoteQty table: Fields, DocUnitPrice and SellingQuantity
for price breaks.
At this time our quotes are not linked to our Sales Orders.
You are saying both of these fields are 0 or blank??
Well, if there is more than one row in that quantities box, you will get multiple rows in you BAQ as well. Isn’t any way around that unless you know which qty break you want to select or if you want to arbitrarily pick one (like the first or last).
If you are getting more than one in the BAQ even though there is only one qty break entered, I would think you have something wrong with your join, like perhaps only joining on quote number and not quote line as well.
The issue isn’t the number of lines returned from the quote it’s the fact that it isn’t returning the most recent quote, it returns several quotes.
I think you’ll need to post your full BAQ for us have a better time understanding whats going on.
The end goal is to get the most recent price quoted per part?
Sorry for the delay, got wrapped up in some other things.
I have done this BAQ so many different ways that I’ve got my self confused. The BAQ that I have attached is NOT working correctly, I have changed my logic around (in hopes that it would work) and so at this point I am using: Max(QuoteHed.DateQuoted) it is returning several Quote Numbers associated with a particular part number rather than the most recent Quote Date.
My goal: List Part numbers and their most recent Quote (whether that be by date or Quote Number doesn’t matter as long as it is the most recent).
I would like to add QuoteQty fields but I’m not sure it will play nicely with the QuoteHed and QuoteDtl tables.
I’m leaving work in a bit, so if you reply I won’t see it until tomorrow -
Thank you for taking a look at this.
select
[SubQuery2].[QuoteDtl_PartNum] as [QuoteDtl_PartNum],
[SubQuery2].[QuoteDtl_QuoteNum] as [QuoteDtl_QuoteNum],
[SubQuery2].[Calculated_LastDateQTd] as [Calculated_LastDateQTd],
[SubQuery2].[QuoteDtl_QuoteComment] as [QuoteDtl_QuoteComment]
from Erp.QuoteHed as QuoteHed1
inner join (select
[QuoteDtl].[QuoteNum] as [QuoteDtl_QuoteNum],
[QuoteDtl].[PartNum] as [QuoteDtl_PartNum],
[QuoteDtl].[QuoteComment] as [QuoteDtl_QuoteComment],
(max( QuoteHed.DateQuoted )) as [Calculated_LastDateQTd]
from Erp.QuoteHed as QuoteHed
inner join Erp.QuoteDtl as QuoteDtl on
QuoteHed.Company = QuoteDtl.Company
and QuoteHed.QuoteNum = QuoteDtl.QuoteNum
and ( QuoteDtl.PartNum = ‘10060’ and QuoteDtl.CurrentStage = ‘OPPO’ and QuoteDtl.Quoted = TRUE )
where (not QuoteHed.ReasonType = ‘L’ and QuoteHed.Quoted = TRUE)
group by [QuoteDtl].[QuoteNum],
[QuoteDtl].[PartNum],
[QuoteDtl].[QuoteComment]) as SubQuery2 on
QuoteHed1.DateQuoted = SubQuery2.Calculated_LastDateQTd
QuoteHed1.QuoteNum = SubQuery2.QuoteDtl_QuoteNum
inner join Erp.Customer as Customer on
Customer.Company = QuoteHed1.Company
and Customer.CustNum = QuoteHed1.CustNum
where (QuoteHed1.Quoted = TRUE and not QuoteHed1.ReasonType = ‘L’)
group by [SubQuery2].[QuoteDtl_PartNum],
[SubQuery2].[QuoteDtl_QuoteNum],
[SubQuery2].[Calculated_LastDateQTd],
[SubQuery2].[QuoteDtl_QuoteComment]
I believe this Query should get you the latest Quote Date for your parts.select
[QuoteDtl].[PartNum] as [QuoteDtl_PartNum],
[QuoteDtl].[LineDesc] as [QuoteDtl_LineDesc],
(max(QuoteHed.DateQuoted)) as [Calculated_LastQDate]
from Erp.QuoteDtl as [QuoteDtl]
inner join Erp.QuoteHed as [QuoteHed] on
QuoteDtl.Company = QuoteHed.Company
and QuoteDtl.QuoteNum = QuoteHed.QuoteNum
group by
[QuoteDtl].[PartNum],
[QuoteDtl].[LineDesc]
Phew - finally I can get back to this!
You are so awesome thank you! Ummm…unfortunately I’m not SQL savvy and I’m not sure how to convert this into Epicor’s GUI interface (so embarrassed),
Any chance you can send the BAQ so that I can import it?
If not, I understand and will try to build it through the GUI interface.
