We have an epicor BAQ that pulls warranty cases. For example, case #922 was a condenser warranty that shipped with a standard of $922, back on 6/6/2021. Now, the standard is $1180. In the report, we want to see the Standard Price at time of creation, but instead it is always giving us the current standard. Anyone know where I can find a field that shows a history of prices at creation? the sales order doesnt seem to have what we need either.
I placed my popcorn down because I don’t think I can help you, but I’m interested in the answer.
I’m replying to make sure I understand the question.
Are you looking for the standard price a customer would pay for the warranty at a certain point
in time?
The field we are currently using is called Standard Material, so I assume that is the cost we are paying at that time.
DA-WarrantySummaryQuery.baq (29.0 KB)
Below is the info the case I talked about which is a good example of how the cost in the BAQ for a case is the current standard cost and not the cost of the part when the case was created in May of 2021. Basically every time we run the BAQ it pulls the latest standard costs instead of the cost at the time the part was sent to the customer. In the BAQ shown below you can see the standard material cost is $1124.92 for the compressor (530-274) for case #922. It should be $887.86 per the PO closest to the case date.
What we need is to be able to run the BAQ every quarter and have the standard cost of a part at the time of the case entry and not the current cost of the part based off of the last purchase price.
Case #922 – created 5/6/21
Compressor 530-274 was replaced under warranty
Compressor Cost
PO# 214381 (4/7/21) - $887.86
PO# 227380 (3/27/23) - $1124.92
/*
* Disclaimer!!!
* This is not a real query being executed, but a simplified version for general vision.
* Executing it with any other tool may produce a different result.
*/
select
[HDCase].[HDCaseNum] as [HDCase_HDCaseNum],
[HDCase].[TopicID1] as [HDCase_TopicID1],
[HDCase].[TopicID2] as [HDCase_TopicID2],
[HDCase].[TopicID5] as [HDCase_TopicID5],
[HDCase].[TopicID6] as [HDCase_TopicID6],
[HDCase].[TopicID7] as [HDCase_TopicID7],
[HDCase].[TopicID8] as [HDCase_TopicID8],
[HDCase].[TopicID3] as [HDCase_TopicID3],
[HDCase].[TopicID4] as [HDCase_TopicID4],
[HDCase].[TopicID9] as [HDCase_TopicID9],
[HDCase].[TopicID10] as [HDCase_TopicID10],
[HDCase].[CreatedDate] as [HDCase_CreatedDate],
[HDCase].[Description] as [HDCase_Description],
[HDCase].[CaseTopics] as [HDCase_CaseTopics],
[HDCase].[PartDescription] as [HDCase_PartDescription],
[HDCase].[PartNum] as [HDCase_PartNum],
[HDCase].[PublishedItem] as [HDCase_PublishedItem],
[HDCase].[PublishedText] as [HDCase_PublishedText],
[HDCase].[ResolutionText] as [HDCase_ResolutionText],
[HDCase].[CaseTypeID] as [HDCase_CaseTypeID],
[OrderDtl].[OrderNum] as [OrderDtl_OrderNum],
[OrderHed].[OrderNum] as [OrderHed_OrderNum],
[OrderHed].[DocOrderAmt] as [OrderHed_DocOrderAmt],
[OrderDtl].[PartNum] as [OrderDtl_PartNum],
[OrderDtl].[UnitPrice] as [OrderDtl_UnitPrice],
[OrderDtl].[OrderQty] as [OrderDtl_OrderQty],
[PartCost].[StdMaterialCost] as [PartCost_StdMaterialCost]
from Erp.HDCase as HDCase
inner join Erp.OrderHed as OrderHed on
HDCase.Company = OrderHed.Company
and HDCase.HDCaseNum = OrderHed.HDCaseNum
inner join Erp.OrderDtl as OrderDtl on
OrderHed.Company = OrderDtl.Company
and OrderHed.OrderNum = OrderDtl.OrderNum
full outer join Erp.PartCost as PartCost on
OrderDtl.PartNum = PartCost.PartNum
where (HDCase.TopicID1 like 'NEW FAILURE' and HDCase.TopicID2 like 'w%')
It’s the join with PartCost - that always shows today’s costs. You probably want to look at PartTran. First, look in Part Transaction History Tracker and identify which transaction type applies to your case. Then, filter PartTran by that type followed by order number, line, and whatever you need.
Also, any chance you lot or serial track?
I don’t really deal with most of the inner workings so I wouldn’t know 100%. I don’t believe we do lot tracking. There might be a good chance we do serial tracking.
If you serial track, you can get back to the original price pretty easily.
Yes
Ok so doing it that way how would I grab that information?
You should be able to trace back to the job it was built on and see the cost of the parts issued to the job.
Something like grabbing the Jobs for the part number and grabbing the closest date to the creation data of the case?
You want to start with the Serial Number. That SN is tied to a Job. Once you have the Job, you know what was issued to the Job at what cost it was. You will have to use the PartTranSNTran table.
If I add the PartTranSNTran, and match it with OrderDtl where part numbers match, it doesn’t show anything for serial or part.
I think you might be in over your head on this one. To get the cost at time of manufacture you need to go back to the job and see the cost of the issued material. If you do serial track parts, that would be the easiest way to do it. But it is even possible without a SN. Is there someone you can talk to that knows how your system is set up? I’m afraid I cannot really help much more without having detailed knowledge of your processes.
What you are trying to do is possible though.
Probably but not like I have a choice. There is someone that knows more but they are in our parent company and it isn’t easy to get a hold of them
Do you know the shipment that it went out on? You should be able to get the cost from the STK-CUS transaction in part tran for that shipment.
I don’t know the shipment it went out on…just the PO’s…I did notice that if I lookup PO 214381 (the old one from 2021), I do see a unit price under my line item. That seems to be the correct price for that timeline…can I pull that one in?
You could I guess. Are you standard costed? That means the “inventory cost” that you associate with a part can be different than the PO price, but it depends on what you actually want out of this report.