This is what is used in the BAQ right now. Only thing wrong is that it is the cost we would pay now vs. what we paid when we made the case. We do use a standard costing method. We essentially want what we paid for the warrantied part at the time of the warranty case. So if the PO they gave us at the time was $887.86 vs a new PO on 3/27/2023 for $1124.92, we want the one associated with the case rather than the latest price. I don’t think the cost would get updated until we receive a new PO?
Depends on your processes. If you are running standard, it could be updated whenever finance wants. If it was Last then it would be updated when a po is received.
How did you get the PO number? Is it on the case? What transactions are associated with the case?
With standard, you may have paid 887.86 but it might have been issued to a job at 900.00 because that was the standard cost. It sounds like you want what it was actually purchased at.
I think you need to identify the transaction type you need first and then you can use PartTran. It can be that you need more than one type. Also, have a look at the HDCaseLink table - it may help you with that. We don’t use this module that much (we just add parts & serial numbers there) so I can’t give you more details.
We got the PO number because it was simply the PO right before the warranty case was created. The only thing attaching the PO and the Warranty Case is the part number.
We do want what is was actually purchased at. They confirmed it would be the price shown under the PO line item. I just need to know how to bring it in
What information do you enter on a case? If you want to get back to transactional data, you will need something whether that is Invoice, Pack Slip, Job, etc.
The only two things I see attached to the case is a sales order and an RMA
Sales Order - 210563
I don’t see anything linking the sales order to a PO, and the Unit Price within the Sales Order is $3,000, but I believe that is different then the price I am looking for.
I would suggest to whoever handles that part that they start capturing more data. Whether it be a serial number, invoice, pack slip, etc. You will need something to get back to the correct data.
In the meantime, I do not think you can supply a relevant report. You can try and build what you did manually by taking the repair date and finding the closest PO that the part you need was on. But that is not a concrete answer and could provide false data.
I will definitely bring that up to my quality manager and order entry people. If I want to find the closest PO based off of Part number and date…how would I go about that?
Once you know what date you can use (maybe the case date?), you can filter POs to only show POs with a date before the case and then do a MAX on the dates to get the one closest.
You will need a subquery for PartTran filtered by type PUR-STK (assuming you’re using buy-to-stock). Then, something to help you get the latest transaction only - I used ROW_NUMBER for that. Finally, link that again to the HDCase. See attached BAQ.
I used PartTran.TranNum to get the latest transaction, but you can switch to PartTran.TranDate if you like (or both).
LE: re-uploaded the BAQ as it was showing the wrong costs.
TEST.baq (38.2 KB)
So the BAQ shows the latest PO receipt (PUR-STK) with date less or equal than your HDCase created date. No data can mean you don’t have any PO receipts (manufactured/kit maybe) or you don’t use buy-to-stock (need a different tran type instead of PUR-STK). That is why I said you need to identify which transaction type(s) you want first.
Ah ok…I did some talking around yesterday and Because we would have items with no PO receipts, we wanted to go with PO creation date instead
Something similar then. Replace PartTran with POHeader and PODetail. The attached shows latest PO with HDCase.CreatedDate >= POHeader.OrderDate. If there are more than 1 PO in that date, it will pick up the one with the highest number (so latest added).
If you have manufactured parts, you can create another subquery, similar to sqPO but with JobHead and JobAsmbl to get the job costs. Or, use the first one I posted and replace PUR-STK with MFG-STK.
TEST2.baq (35.2 KB)
Thanks for the advice and help. What I ended up doing was matching my Order field to my PODetail and POHeader fields and grabbed the closest PO creation date to the Case creation date. Then from there I just grabbed the ‘DocUnitCost’ field and that got me where I need to be. Thanks for everyones help!
1 Like