Skip to main content
Question

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

  • 14 September 2023
  • 2 replies
  • 117 views

Forum|alt.badge.img+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

dcomerford
Captain I
Forum|alt.badge.img+15
  • Captain I
  • 596 replies
  • September 14, 2023

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


Naveen Boga
Captain II
Forum|alt.badge.img+19
  • Captain II
  • 3381 replies
  • September 18, 2023

@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


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings