Quote to Order conversion flag?

Kinetic ERP
1

A Manger has asked me to create a BAQ that shows them the time from Quote or Order entry to ship date. I started the BAQ with QuoteHed, then added OrderDtl to link the Quote # and Order #. Unfortunately this doesn’t capture orders that were started in Order Entry without a quote. I tried to use the QuoteNum field from OrderDtl, but many of our orders have lines from quotes and lines added from Order entry after conversion. I’ll have 10 lines with the QuoteNum and 2 lines =0 that were added after conversion in Order entry

Is there a field I can reference in OrderHed or OrderDtl that indicates if the order was created in Sales Order entry?

select
[QuoteHed].[QuoteNum] as [QuoteHed_QuoteNum],
[QuoteHed].[DateQuoted] as [QuoteHed_DateQuoted],
[Customer].[Name] as [Customer_Name],
[QuoteHed].[CurrentStage] as [QuoteHed_CurrentStage],
[TerritoryID].[SalesTer_TerritoryDesc] as [SalesTer_TerritoryDesc],
[QuoteAmount].[Calculated_CustomerAmount] as [Calculated_CustomerAmount],
[SalesRep].[SalesRepCode] as [SalesRep_SalesRepCode],
[OrderHed].[OrderNum] as [OrderHed_OrderNum],
[OrderHed].[OrderDate] as [OrderHed_OrderDate],
((OrderHed.DocTotalCharges- OrderHed.DocTotalDiscount)+(OrderHed.DocTotalMisc+ OrderHed.DocTotalTax)) as [Calculated_OrderAmount],
[UDCodes].[CodeDesc] as [UDCodes_CodeDesc],
[QuoteHed].[EntryDate] as [QuoteHed_EntryDate],
[OrderHed].[NeedByDate] as [OrderHed_NeedByDate],
(datediff(day,QuoteHed.EntryDate,OrderHed.NeedByDate)) as [Calculated_DaysQuotetoShip],
(datediff(day, QuoteHed.EntryDate, Constants.Today)) as [Calculated_DaysQuoteOpen]
from Erp.OrderHed as OrderHed
left outer join (select
[OrderDtl2].[OrderNum] as [OrderDtl2_OrderNum],
[OrderDtl2].[QuoteNum] as [OrderDtl2_QuoteNum]
from Erp.OrderDtl as OrderDtl2
group by [OrderDtl2].[OrderNum],
[OrderDtl2].[QuoteNum]) as OrderNumQuoteNum on
OrderNumQuoteNum.OrderDtl2_OrderNum = OrderHed.OrderNum
right outer join Erp.QuoteHed as QuoteHed on
OrderNumQuoteNum.OrderDtl2_QuoteNum = QuoteHed.QuoteNum
left outer join Erp.Customer as Customer on
QuoteHed.Company = Customer.Company
and QuoteHed.CustNum = Customer.CustNum
inner join (select
[SalesTer].[TerritoryID] as [SalesTer_TerritoryID],
[SalesTer].[TerritoryDesc] as [SalesTer_TerritoryDesc]
from Erp.SalesTer as SalesTer
group by [SalesTer].[TerritoryID],
[SalesTer].[TerritoryDesc]) as TerritoryID on
QuoteHed.TerritoryID = TerritoryID.SalesTer_TerritoryID
inner join (select
[QuoteHed2].[QuoteNum] as [QuoteHed2_QuoteNum],
((QuoteHed2.DocTotalGrossValue- QuoteHed2.DocTotalDiscount)+(QuoteHed2.DocTotalMiscAmt+QuoteHed2.DocTotalTax)) as [Calculated_CustomerAmount]
from Erp.QuoteHed as QuoteHed2) as QuoteAmount on
QuoteAmount.QuoteHed2_QuoteNum = QuoteHed.QuoteNum
left outer join Erp.SalesRep as SalesRep on
SalesRep.SalesRepCode = QuoteHed.WorkforceID_c
inner join Ice.UDCodes as UDCodes on
QuoteHed.ProductLine_c = UDCodes.CodeID
and ( UDCodes.CodeTypeID = ‘ProductLin’ )

2

I’m not aware of a field that specifies this. I may have misunderstood the requirements, but could you not use the logic that if any of the lines in the OrderDtl table have a quote number then the order must have originated from a quote?

2 Likes
3

I could, but the Manager wants to know which orders were created from ONLY order entry and which orders were created from Converting quotes. He’s not interested in the line details.

4

If you group by OrderNum in the OrderDtl table, and return max(QuoteNum) as a calculated field, records without an associated quote will have max(QuoteNum) = 0.

2 Likes
5

Genius. Thank you :+1: