Skip to Content
Author's profile photo Former Member

Displaying Serial Numbers / Batch Details on Invoice Part I.

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.

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 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'
  end
  FROM OADM T0

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
 SELECT T0.[IntrSerial] FROM OSRI
 T0 INNER JOIN SRI1 T1 ON T0.ItemCode = T1.ItemCode and T0.SysSerial = T1.SysSerial
WHERE
 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
begin
  set @txt = @txt + @serno +','
  fetch next from P into @serno
end
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…..

Assigned Tags

      3 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member
      Thank You for this information, this document is really useful for my purpose.
      I have the same problem in the issue for production. how can I change the formatted search so I can read the serial numbers  selected to go out in "issue for production" transaction?
      Author's profile photo Former Member
      Former Member
      Great help with Invoice with based DR Documents. But  if the customer needs to print it also in Delivery Document. 1 Item with 50 or more serial numbers using default PLD that display serial per line really can eat a lot of paper. Anyway, this trick helps a lot. More power.
      Author's profile photo Former Member
      Former Member

      Hello, When i try to do this i get the error:

      1). [Microsoft][SQL Server Native Client 10.0][SQL Server]A cursor with the name 'P' already exists.  'Blanket Agreement' (OOAT)

      i have tried changing P to another letter and it still has the same error.

      Any ideas?