Skip to main content
Question

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

  • 14 September 2023
  • 2 replies
  • 91 views

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

dcomerford
Rank
Userlevel 7
Badge +14

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

Dermot Comerford
Naveen Boga
Rank
Userlevel 7
Badge +19

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

 

 

Naveen B

Reply


Terms & ConditionsCookie settings