Displaying Serial Numbers / Batch Details on Invoice Part II.
Displaying Serial Numbers / Batch Details on Invoice Part I. I have described a scenario to print out serial numbers on invoices with formatted search. Now let's continue in the same scenario with Batch details.
If you working by the following scenario: printing a delivery note first, then printing the invoice, you can not display serial numbers / batch details on the A/R Invoice created from Delivery note. In this blog I would like to give an idea / workaround for this problem.
Background
It is Possible to create a User Defined Field on A/R Invoice row level, which holds the Serial Numbers/Batch Details. This field can be populated by Formatted Search when the A/R Invoice is recorded (created from delivery note). The tricky point is here: How to populate a result of the query (several rows) into one field when we need to display more than one serial number / batch details?Steps to do:
- Create an User Defined Field on Document Row Level:
Name: Serial / Batch Details
Type: Alphanumeric
Structure: Text
- Create a Formatted Search which will populate the data from Delivery note when A/R Invoice recorded
- Put the UDF into the Print Layout of A/R Invoice
Displaying Batch Details
1. Decide with information is needed from Batch details to be printed out on invoices.
On Bacth Details form (InventoryItem ManagementBatchesBatch Details) we can see all the possible details. Of course here user defined field also can be implemented. In my scenario the following information should be printed on Invoice:
- Batch Number
- Delivered Quantity
- Expiration Date
2. Build the formatted search SQL command.
By a SQL cursor driven result set we can build a text which contains the list of the delivered batch details separated by new lines. The relations between A/R Invoice and Delivery note is defined on A/R Invoice Form. in columns 45, 46, and 43
declare @batch as nvarchar(100)
declare @txt nvarchar(max)
set @txt = ''
declare P cursor for SELECT T0.[BatchNum] + space(1)
+ cast(cast(T1.[Quantity] as decimal(19,2)) as nvarchar)
+ space(1) + case when T0.[ExpDate] is null then '' else convert(nvarchar,T0.[ExpDate],102) end
FROM OIBT T0 INNER JOIN IBT1 T1 ON T0.ItemCode = T1.ItemCode and T0.WhsCode = T1.WhsCode
and T0.BatchNUm = T1.BatchNum
WHERE T1.[BaseEntry] =$[$38.45.0] and T1.[BaseLinNum] =$[$38.46.0]
open P
fetch next from P into @batch
while @@fetch_status = 0
begin
set @txt = @txt +'
'+@batch +','
fetch next from P into @batch
end
close P
deallocate P
select @txt
Please note: here i am used an advantage of PLD: if you enter
characters in a text, it will be printed as new line.
Of Course,if you need more information i have selected in this example, you should change the T0.[IntrSerial] field to the correct field name.
3. Defined the formatted search on the A/R Invoice
Summary
Formatted searches and a little SQL knowlegde enables a perfcet flexibilty in SAP B1 to display serial numbers and batch details on delivery note based invoices.
These sql statements are based on 2007 version and also works with 8.8. In 8.8 the tables containg Serial numbers and Batches had been replaced with views.
