Batch Numbers: Writing a Recall Report in SAP Business One.

While Batch numbers are not difficult to add into SAP Business One, they are not easy to get out. There are reports you can use, but for valuable reports for your business, you'll need custom reports and queries. For example, batch numbers are critical in recalls. You might want a report to know every customer that bought a specific batch number.?

SAP Business One buries batch and serial numbers under a series of tables. Let's put together a basic recall report to understand how to use reporting with batch numbers.?

There are three tables to get a batch number and then a fourth table to give the batch number context. We'll start with the OITL table, the?Inventory transactions log. This table records all transactions which use batch or serial numbers. I'll make a query that provides us with the most critical information.?

SELECT T0.LogEntry,T0.ItemCode, T0.ApplyType, 
   T0.ApplyLine, T0.ApplyEntry, T0.ManagedBy 
FROM OITL T0        

Execute this, you get a table.?

LogEntry is the primary key,?and we'll use Itemcode to identify the lot number of interest.?

The next three columns indicate what document created these batch numbers. First is OITL.ApplyType, and this is the object number for the Application table, Some common typesyou'll find are:

OITL.ApplyEntry is the DocEntry or equivalent for that table. To drill down further into table rows, OITL.ApplyLine gives you the row line number.?

Finally, there's OITL.ManagedBy, which indicates if the transaction allocated Serial numbers or batch numbers. The batch number will have a ManagedBy value of 10000044.?

Though no one ever wants to do it, one great use for batch numbers is listing customers for contact during a product's recall. Suppose I recalled Pizza bites. I can filter this query with a WHERE Clause looking for Delivery documents, batch numbers, and item code SDL-01.?

SELECT T0.LogEntry,T0.ItemCode, T0.ApplyType, 
   T0.ApplyLine, T0.ApplyEntry, T0.ManagedBy, T0.CardCode

FROM?

??OITL T0?

WHERE?

??T0.ManagedBy = 10000044 AND?

??T0.ApplyType = 15 AND

??T0.ItemCode = 'SDL-01'


?        

Which get you this:

A single transaction may have multiple batch numbers associated with it. Because of this, OITL has a rows table ITL1. You join the ITL1 table to get the key to the table that contains the batch numbers, OBTN. That key is SysNumber. SO I could expand my query like this:

SELECT

??T0.LogEntry,T0.ItemCode, T0.ApplyType,
  T0.ApplyLine, T0.ApplyEntry, T0.ManagedBy, T0.CardCode,

??T1.SysNumber

FROM?

??OITL T0?

??INNER JOIN ITL1 T1 ON T0.LogEntry = T1.LogEntry?

WHERE?

??T0.ManagedBy = 10000044 AND?

??T0.ApplyType = 15 AND

??T0.ItemCode = 'SDL-01'



?        

Finally,?OBTN contains the batch numbers. You need two keys to get the row you want.

First, you use the ItemCode, and each item code has a series of Sysnumbers attached to it. Once you join OBTN, the DistNumber contains the batch number. I'll add to my query like this.?

SELECT

??T0.LogEntry,T0.ItemCode, T0.ApplyType,
  T0.ApplyLine, T0.ApplyEntry, T0.ManagedBy, T0.CardCode, T0.CardName,

??T1.SysNumber,

??T2.DistNumber

FROM?

??OITL T0?

??INNER JOIN ITL1 T1 ON T0.LogEntry = T1.LogEntry?

??INNER JOIN OBTN T2 ON t2.Itemcode = T1.ItemCode
                    AND t2.SysNumber = t1.SysNumber

WHERE?

??T0.ManagedBy = 10000044 AND?

??T0.ApplyType = 15 AND

??T0.ItemCode = 'SDL-01'

?        

and that gives me

If you only want the business partner for all assigned batch numbers, you are done. However, I'll often want more information from delivery documents about the shipment. So I'll use the OITL.ApplyEntry to join the delivery document ODLN.?

I'm also interested in only one lot number, 22052, for a recall. So I'll include that in my query.?

SELECT

??T0.LogEntry,T0.ItemCode, T0.ApplyType,
  T0.ApplyLine, T0.ApplyEntry, T0.ManagedBy, T0.CardCode, T0.CardName,

??T1.SysNumber,

??T2.DistNumber,

??T3.Cardcode, T3.CardName, T3.CntctCode

FROM?

??OITL T0?

??INNER JOIN ITL1 T1 ON T0.LogEntry = T1.LogEntry?

??INNER JOIN OBTN T2 ON t2.Itemcode = T1.ItemCode 
                     AND t2.SysNumber = t1.SysNumber

?INNER JOIN ODLN T3 ON T3.DocEntry = T0.ApplyEntry

WHERE?

??T0.ManagedBy = 10000044 AND?

??T0.ApplyType = 15 AND

??T0.ItemCode = 'SDL-01' AND

??T2.DistNumber = '22052'

?        

I added a lot of extra stuff here to explain the architecture of this query. I can simplify this to include necessary information only and change the item code and batch number to parameters.?

SELECT

??T0.ItemCode,

??T2.DistNumber,

??T3.DocEntry as "Delivery #",

??T3.Cardcode, T3.CardName,T3.CntctCode

FROM?

??OITL T0?

??INNER JOIN ITL1 T1 ON T0.LogEntry = T1.LogEntry?

??INNER JOIN OBTN T2 ON t2.Itemcode = T1.ItemCode 
                    AND t2.SysNumber = t1.SysNumber

?INNER JOIN ODLN T3 ON T3.DocEntry = T0.ApplyEntry

WHERE?

??T0.ManagedBy = 10000044 AND?

??T0.ApplyType = 15 AND

??T0.ItemCode = [%0] AND?

??T2.DistNumber = [%1]

?        

And that gets me this:

That is a basic recall list you can use. You link OITL, ITL1, and OBTN to get the data you need, and then use OITL.ApplyType and OITL.ApplyEntry to get information about the recall from the proper documents.?

I may want more information to send out an e-mail blast or other mass communication, needing more information about the business partner. I have the ODLN.CntctCode, but how do I use it? I'll dive into getting good addresses from the system in our next newsletter.?