Serial Numbers Query in the Connector - JrnlSNum Table

Hi all,

I just got this all figured out and figured I'd document this here for future me and anyone else who might need it.

I typically use the Sage 50 .dat file documentation when writing SQL queries in the connector:

However, there are some missing tables that are not included in that list that are very necessary to use.  For instance, InventoryCosts and JrnlSNum are two that I've personally ran across.

A tip for finding tables that might not be listed there is to, inside of the Connector too, start an SQL query and then click Insert --> Insert Table.  This will show you all available tables.

I am trying to write a query that will show all of our serialized items.  Since there is no documentation for the JrnlSNum table here is what I've found:

TransactionClass - This is an integer from 0 - 5 in our system and indicates the type of transaction.

0 - Added into system via Work Ticket or Assembly

1 - Added into system via Purchase Journal or Inventory Adjustment

2 - Added into system via a negative quantity on a Sales Journal or in a Credit Memo

3 - Removed from system by Sales Journal

4 - Removed from system by Vendor Credit Memo

5 - Removed from system by Inventory Adjustment 

  • 0

    Here is what I used to create a table with all available Serial Numbers:

    -- The JrnlSNum table shows entries for each time a serial number is manipulated. A transaction class of 1-2 shows it being entered into the system.
    -- A transaction class greater than 2 (3-5) shows it leaving the system. By joining a table with all of the 1-2 transaction classes and a table with all of the >2s,
    -- You can see which transactions have a 1-2 class, but do not have a corresponding >2 class. This results in a Null on the second table which means that serial number is in our inventory.
    -- There are some scenarios where there is a 1, 2, and 3 class. This means the item was received, sold, and then credit memo'd so it is back in inventory.
    -- When the Count of the 1/2s is greater than the 3/4/5s then the item is also in inventory.

    SELECT SNT12.ItemRecordNumber AS ItemRecordNumber, SNT12.SerialNumber AS SerialNumber FROM
    (SELECT COUNT(TransactionClass) TC12, SerialNumber, ItemRecordNumber FROM JrnlSNum
    WHERE TransactionClass > 0 AND TransactionClass < 3
    GROUP BY SerialNumber, ItemRecordNumber) SNT12


    (SELECT COUNT(TransactionClass) TC345, SerialNumber, ItemRecordNumber FROM JrnlSNum
    WHERE TransactionClass > 2
    GROUP BY SerialNumber, ItemRecordNumber) SNT345

    ON SNT12.SerialNumber = SNT345.SerialNumber AND SNT12.ItemRecordNumber = SNT345.ItemRecordNumber

    WHERE TC12 - TC345 > 0 OR TC345 IS NULL

  • 0 in reply to Ben Baum

    So, what I found out is that any record in JrnlSNum that has a TransactionClass of 0 also has another record with a TransactionClass of 1.  The query ignores the 0 values so that items are not double counted into inventory.  

    The SNT12 table creates a union table that contains all JrnlSNum records with a TransactionClass of 1 or 2.  These are the classes that can adjust a serial number into inventory.  The SNT345 creates a table for all TransactionClass = 3, 4, or 5.  These classes can take a serial number out of inventory.  A count of each of the 1/2s and 3/4/5s is grabbed.  If the number of 1/2 records are greater than the number of 3/4/5 records then the serial number is considered on hand.  In situations where there is a 1/2 record, but no 3/4/5 records that class will be null and means that the serial number is on hand.