Displaying Serial Numbers / Batch Details on Invoice Part I.

If you using serial numbers or batches, you may be faced the following problem: How to display serial numbers and batches on the A/R Invoice. Of course if you issue the Invoice without a delivery note, you can use build in PLD format: Invoice Including Batch/SN (System). This layout displays the serial numbers / 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.


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 Serial Numbers


1.  Determine what is your Unique Serial Number Type?

- By B1 Client,  Open Administration\System Initialization\General Settings and locate the Inventory tab - Unique Serial Numbers by field.


- By SQL: the Unique serial number  settings can be determined by the following query:

SELECT case T0.[SriUniqFld] 
  when 0 then 'Settings: None - Field Name: SysSerial' 
  when 2 then 'Settings: Manufacturer Serial Number - Field Name: SuppSerial' 
  when 3 then 'Settings: Serail Number - Field Name: IntSerial' 
  when 4 then 'Settings: Lot Number - Using bacthes' 

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 serial numbers separated by comma (,). The relations between A/R Invoice and Delivery note is defined on A/R Invoice Form. in columns 45, 46, and 43 

declare @serno as nvarchar(100)
declare @txt nvarchar(max)
set @txt = ''

declare P cursor for
 T0 INNER JOIN SRI1 T1 ON T0.ItemCode = T1.ItemCode and T0.SysSerial = T1.SysSerial
 T1.[BaseType] = $[$38.43.0]
 and T1.[BaseEntry] =$[$38.45.0]
 and T1.[BaseLinNum] =$[$38.46.0]

open P
fetch next from P into @serno
while @@fetch_status = 0
  set @txt = @txt + @serno +','
  fetch next from P into @serno
close P
deallocate P
select @txt

Of Course, if your Unique Serial Number settings different than my example,  you should change the T0.[IntrSerial] field to the correct field name.

3. Define the formatted search on the A/R Invoice

Now as the final step, define the Formatted Search, on the UDF created for Serial numbers with the following options:
- Auto Refresh: Yes
- when Item Number column is altered.

Will be continued with displaying batches.....