Lot/serial tracing - need an inquiry to support product recall efforts
I’m surprised this hasn’t been questioned/requested here before.
Every customer of mine that uses Lot/Serial tracking has asked for an inquiry that tells them every product a lot or serial number when into. The only tool that’s available is the Inventory Lot/Serial History inquiry which is only a list of all of the transactions for that lot/serial number.
The user has to open every transaction to determine where that product went, which can be hundreds of transactions in the case of a lot number.
I’ve had conversations with VERY frustrated customers over the fact that there’s not an easy way to know what customers they need to contact if there is a product issue. That’s the whole point of tracking the items (their words).
Am I overlooking a tool that already exists? Or does anyone have an inquiry they’ve developed for this purpose?
Thank you!
Page 1 / 1
This was the basis of a DAC based on a SQL view I had worked on before for another customer to print the Lot/Serial numbers on the Customer Sales Invoice. It also handles dropships lots hence why we look at the POReceiptline. You could try it out by creating a View and DAC and GI from it.
select it.ARDocType as Type, it.ARRefNbr as InvoiceNbr, it.ARLineNbr as InvLineNbr, it.SOShipmentType as ShipmentType, it.SOShipmentNbr as ShipmentNbr, it.SOShipmentNbr as ShipmentLine , it.SOOrderType as OrderType, it.SOOrderNbr as OrderNbr, it.SOOrderLineNbr as OrderLineNbr, i.InventoryCD as InventoryCD, case when it.SOShipmentType = 'H' then pols.LotSerialNbr when it.LotSerialNbr is null then sols.LotSerialNbr else it.LotSerialNbr end as LotSerial, cu.AcctCD from INTran as it with (nolock) join InventoryItem as i with (nolock) on i.InventoryID = it.InventoryID and i.CompanyID = it.CompanyID left join POReceiptLine as pol with (nolock) on pol.ReceiptType = it.POReceiptType and pol.ReceiptNbr = it.POReceiptNbr and pol.LineNbr = it.POReceiptLineNbr and pol.CompanyID = it.CompanyID left join POReceiptLineSplit as pols with (nolock) on pol.ReceiptType = pols.ReceiptType and pol.ReceiptNbr = pols.ReceiptNbr and pol.LineNbr = pols.LineNbr and pol.CompanyID = pols.CompanyID left join SOShipLine as sol with (nolock) on sol.ShipmentNbr = it.SOShipmentNbr and sol.ShipmentType = it.SOShipmentType and sol.LineNbr = it.SOShipmentLineNbr and sol.CompanyID = it.CompanyID left join SOShipLineSplit as sols with (nolock) on sols.ShipmentNbr = sol.ShipmentNbr and sols.LineNbr = sol.LineNbr and sols.CompanyID = sol.CompanyID left join BAccount as cu with (nolock) on cu.BAccountID = it.BAccountID and cu.CompanyID = it.CompanyID where it.SOOrderType is not null and it.ARDocType is not null order by it.ARRefNbr desc
@donnadeskins55 I assume you were anticipating the following behavior: the ability to fetch details for Lot Serial without requiring manual entry. I've made some minor code adjustments, and I can now retrieve the details with Lot Serial, although I haven't extensively verified it yet.
