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: https://help-sage50.na.sage.com/en-us/2019/Content/DDFs/A_List_of_Data_Files.htm

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

    LEFT JOIN

    (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.