Question

Lot/serial tracing - need an inquiry to support product recall efforts

  • 14 September 2023
  • 2 replies
  • 88 views

Userlevel 5
Badge +1

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!


2 replies

Userlevel 7
Badge +13

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

Userlevel 7
Badge +18

@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.

 

 

Reply


About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved