Skip to content

Can You Explain How Elliott Stores Serial Numbers in Its Database?

← Systems Manager
Q - Can you explain the various Lot/Serial files (IMLSHST, IMLSTRXS, IMSERREF, etc) - how/when they are populated/updated by the Elliott application and how they relate to other files?

A - Here is a highlight of each one of the Elliott database tables that are related to serial number.

Serial Database

IMLSTRXS - I/M Lot/Serial Transaction File
There are two types of records stored in this table based on field INV_LS_STATUS:
  • If INV_LS_STATUS = "A", then this is a Stock Serial/Lot Record; or 
  • If INV_LS_STATUS = "I", then this is a History Serial/Lot Record.
When a serial record is issued and posted.  The INV_LS_STATUS is then changed from "A" to "I."  The history record is a legacy implementation. You will find better history information stored in IMLSHST table.  For backward compatibility purposes, Elliott does not stop creating "I" records even though history is now available through the IMLSHST table. We recommend that users purge the "I" records periodically through I/M -> Reports -> Serial/Lot Issue History Report.  Enter All to print all serial history records, and answer "Y" to "Purge File."  You can add this as a deferred processing function so the system performs the purge of "I" records every night automatically.

IMSERREF - I/M Serial Number Reference File
In the legacy IMLSTRXS file design, there are certain shortcomings. First, it allows duplicate stock serial numbers (same item and same serial number) to be received to different locations. Second, there's no way to look up a stock serial record by its serial number only. Since we did not want to modify the IMLSTRXS index structure to provide backward compatibility, we added the IMSERREF table as an auxiliary index table to IMLSTRXS. When a record is added or deleted from the IMLSTRXS table, its corresponding record in the IMSERREF table is also added or deleted.  To resolve potential database integrity problems, we provided the following utility program in I/M -> Processing -> Serial/Lot/Bin Processing -> Serial/Lot -> Serial# Reference File.  This utility reads through all "A" types of records in the IMLSTRXS table to recreate the IMSERREF table.  Generally speaking, you don't need to do this unless there are issues with looking up serial numbers.

IMLSHST - I/M Lot/Serial History File
This table was introduced to provide better serial history in the following ways:
  • Both receiving and issue history are stored in this table.  On the other hand, IMLSTRXS stores only issue history.
  • You may look up a serial history record by item+serial number, serial number, or partial serial number.
  • IMLSHST is linked to other history tables.  For example, you may drill down to the serial numbers from Invoice History Inquiry when highlighting a line item.
IMTRXSER - I/M Transaction Serial File
The parent table of this one is IMTRXFIL (I/M Transaction Processing File). When the user chooses to add issue, receive or transfer transactions in I/M Inventory Transaction Processing, the corresponding serial record is stored in this table.  When the Inventory Transaction is posted, the corresponding "A" record in IMLSTRXS is converted to "I."  A new record will be added to IMLSHST. The corresponding record will be deleted from the IMSERREF table.

IMINVSRH - I/M Transaction Serial Audit File
The parent table of this one is IMINVTRX (I/M Inventory Transaction Audit File), i.e. your inventory history. This table is used to track the relationship of bin number and serial number when WMS (Warehouse Management System) and Multi-bin are in used.  If WMS and Multi-Bin is not in used, then you can find the corresponding serial history of the IMINVTRX in IMLSHST table.

IMTAGSER - I/M Count Tag Serial File
The parent table of this one is IMTAGFIL (I/M Physical Count Tag File). It is used to store the serial number of the physical count tag.  Where there's a discrepancy between IMTAGSER and IMLSTRX table.  The particular physical count tag is not allowed to post.  You will have to use processing like Inventory Transaction Processing to adjust the serial inventory to match the count tag before that count tag can be posted.

IMCNTSER - I/M Count Serial File
This table is used in WMS Physical or Cycle Count which is highly designed toward barcode scanning processing. When you go to I/M -> Processing -> Physical Count Processing -> Utility -> Interface WMS Scan, the record in IMCNTSER will be transferred to IMTAGSER.

IMTSFSER - I/M Transfer Serial File
The parent table of this one is IMTSFTRX (I/M Transfer Transaction File).  The serial number of a transfer batch item is stored in this table. 

CPORDLS - COP Order Line Serial/Lot File
The parent table of this one is CPORDLIN (COP Order Line Item File).  It is used to store the serial number of a particular order line item.  Once the invoice is posted, the record in this table is transferred to IMLSHST.  The corresponding "A" record in IMLSTRXS is changed to "I".

CPBOXSER - COP Box Ship Serial File
The parent table of this one is CPBOXSHP (COP Box Ship File).  It is used to store serial numbers collected during shipping verification or a particular order, box and item.  In order inquiry, user can use F3 key to drill down to shipping data and the serial number stored in this table before the invoice is posted.  The record in this table is then transferred to CPBOXHSR when the invoice is posted.

CPBOXHSR - COP Box Ship History Serial File
The parent table of this one is CPBOXHST (COP Box Ship History File). The record in this table is transferred from CPBOXSER when the invoice is posted.  In invoice history inquiry, user can use F3 key to drill down to shipping history and the serial number stored in this table.

CPSTGSER - COP Staging Serial File
The parent table of this one is CPSTGSUM (COP Staging Summary  File).  This table is only used with WMS staging processing to collect the serial number and later on be used in the shipping verification.

POTRXSER - PO Receiving Transaction Serial File
The parent table of this one is POTRXFIL (PO Receiving Trx File). When user enter the PO Receiving Transaction, the corresponding serial record is stored in this table.  When the PO Receiving Transaction is posted, the record in this table is then added to IMLSTRXS, IMSERREF and IMLSHST.

POWHRECS - PO Warehouse Receiving Serial File
The parent table of this one is POWHRECI (PO Warehouse Receiving Item File). When user enter the PO Warehouse Receiving or a particular item, the corresponding serial numbers are stored in this table.  After the posting of PO Warehouse Receiving, the record in this table is then added to IMLSTRXS, IMSERREF, IMLSHST and POWHHSTS.

POWHHSTS - PO Warehouse Receiving History Serial File
The parent table of this one is POWHHSTI (PO Warehouse Receiving History Item File).  The record in this table is transfered from POWHRECS upon the posting of PO Warehouse Receiving record.

BMORDSER - BOMP Production Order Serial/Lot File
This is a legacy table used to record serial number used by a production order after the fact.  It is no longer used in Elliott system.

BMTRXSER - BOMP Transaction Serial File
When user report production transaction in BOMP, the corresponding serial number is stored in this table.  The possible parent table for this one can be BMTRXFIL, BMPTXFIL depend on the type of work order (Material or Plus).

BMHSTSER - BOMP History Serial File
When the production transaction in BOMP is posted, the corresponding serial record is transferred from BMTRXSER table.  The parent table is BMHSTMTL. Also, upon posting of BOMP production, the corresponding record in IMLSTRXS, IMSERREF, IMLSHST will be updated too.

The Special Handling of Five Amigos for  Serial Records
In Elliott, Five Amigos refers to Notes, Contacts, Attributes, Links and Events.  Since these features' buttons are either enabled or disabled at the same time that a master record is being referenced or un-referrenced, we call them "Amigos." The master tables of Amigos are:
  • NOTE - Notes
  • SYCONREL - Contact Relationship
  • SYATTRIB - Attribute
  • SYLNKFIL - Links
  • SYEVTACT - Events
All these tables have keys beginning with FILENAME and Reference-ID.  For serial records, it doesn't matter whether this is a stock or history record, the corresponding FILENAME in the 5 Amigos is "IMLSHST." The reference id will be the Item# (15 digits) + Serial# (15 digits). For example, if you wish to write down the physical description of a serial number when you first receive it, you can use a Note to do it.  The Note record will be attached to the IMLSHST table. Even though when you bring up the stock record in the IMLSTRXS table, you can reference the same Note.  If you transfer between different warehouse locations, there's no need to change the 5 Amigos record since the reference ID is Item# + Serial# and no location information is included.  When the particular serial record is sold, you can refrence the same Note in IMLSHST, either highlighting the receiving history or the issue history.

Reports

Serial/Lot Stock Status Report
When you go to I/M -> Reports -> Serial/Lot Stock Status Report, it will look at the IMLSTRXS table for the "A" record to show you the serial number records that are currently in stock.

Serial/Lot Issue History Report
When go to to I/M -> Reports -> Serial/Lt Issue History Report, it will look at the IMLSTRXS table for the "I" record to show you the serial history in the selected range.  Since IMLSTRXS history is replaced by IMLSHST, we suggest that you to purge the "I" record from IMLSTRXS table, so this report is rarely used.

Invoice History Serial/Lot Report
When you go to COP -> Reports -> Invoice History Reports -> Invoice History Serial Lot Report, it will look at IMLSHST to show you the serial history in selected range. Not only will the serial number associated with invoice history show up, all serial history including the receiving and issue history of the serial number will show up. 

Inventory Transaction Audit Trail Report
When you go to I/M -> Reports -> Inventory Transaction Audit Trail Report, you may answer "Y" to Print Serial#."  The corresponding serial number in IMLSHST will be printed for each inventory transaction.

Serial Integrity Check Report
When you go to I/M -> Processing -> Serial/Lot/Bin Processing -> Serial/Lot -> Process -> Serial File Qty OH Integrity Check, it will check the quantity on hand of an item at a location matching with the corresponding "A" record in the IMLSTRXS table. If the number of "A" records found is different than the quantity on hand, then this is consider to be a serious exception. This type of condition rarely happens.  But if it should happen, you should investigate and fix it right away.  For example, if a user changes the quantity on hand of an item, then this condition could happen.  We suggest that you use deferred processing to run this utility every night and check this report the next day.

Inquiry

You may drill down to serial records from various areas in the Elliott system:
  1. I/M -> Inquriy -> Serial/Lot/Bin Stock Status -> Stock Item Only
  2. I/M -> Inquiry -> Serial/Lot/Bin Stock Status -> Issued or Stock Items
  3. COP -> Inquiry -> Order Inquiry -> Order Inquiry by Customer/Order, for a selected or invoiced order, you can highlight the line item and press Enter to bring up the serial number that's assigned to the line item.
  4. COP -> Inquiry -> Invoice History Inquiry -> Invoice Inquiry by Invoice, you may highlight the line item and press Enter to bring up the associated serial number.
  5. COP -> Inquiry -> Invoice History Inquiry -> Invoice Inquiry by Serial/Lot No.  You may inquiry a serial number by first entering the item number, then the serial number.  You may also hit Enter at item number field and go directly to look up by serial number only.  If you enter a partial serial number, the system can find the beginning matching serial number for you. This is commonly used for service or return purposes.

Processing

PO Legacy Receiving
This is the legacy PO receiving method in PO -> Processing -> Receiving Processing . You can use this method to receive inventory by serial number.  The serial record is saved in POTRXSER table.

PO Warehouse Receiving
This process is designed so the warehouse can scan the item and serial number as it is received.  The serial record is saved in the POWHRECS table. Generally speaking, this process is more efficient than PO Legacy Receiving.  In recent years, we have noticed a lot more "bogus" scans of barcodes. This is usually due to poor quality of the barcode label. This problem is especially an issue for serial numbers.  When there's a bogus scan, users won't know unless they pay close attention to the screen, which slows down productivity.  We have  introduced two Global Setup flags in System -> Serial No Tracking Control:
  17. Reject Serial Char in Ser# During PO WH Recv?
  18. Match 1st Ser# Length During PO WH Recv?
We recommend that you answer "Y" to both flags.

PO Warehouse Receiving Transaction could be imported from a CSV file.  But at this moment, the CSV import does not support the receiving transactions by serial numbers.

I/M Inventory Transaction Processing
For Inventory Trx Processing, the serial record is saved in the IMTRXSER table. For receiving, the system will prompt you for the vendor number.  For issuing, the system will prompt you for the customer. It is possible you may receive from a customer or issue to a vendor. In that case, you will need to process the function key at the customer or vendor number field at the time to toggle. Generally speaking, you will use issue or receive transaction types to adjust your inventory only. For regular receiving, you should either receive through PO Legacy Receiving or Warehouse Receiving.  

Keep in mind that If you use this process to transfer serial numbers between two locations, the serial history won't be updated to the IMLSHST table.  This is because we consider this as a method used for transferring between "logical" locations. If you have transfers between external "physical" locations, we suggest that you use the "transfer order" feature, which is processed like a real order and goes through invoice posting.  Then the transfer serial number will show up in the IMLSHST table.

Physical Count Processing
During the physical count processing, if this is a serialized item, the system will prompt for the associated serial number for the count tag and save it in the IMTAGSER table. During the posting of the physical count tag, if there a discrepancy of the serial number entered vs. the serial number on file, then that count tag is not allowed to post.  You will have to use I/M Inventory Transaction Processing to either issue or receive to adjust the serial number on stock until it matches the count tag before it can be posted.

Order Entry
During Order Entry for "I" or "C" types of orders, the system will ask you for the associated serial record of the line item. The serial number will be saved in the CPORDLS table.

Order Billing Selection
During Order Billing Selection for "O" types of orders, the system will ask you for the associated serial record of the line item. The serial number will be saved in the CPORDLS table.

Create Credit Memo by Serial Number
You can go to Order Entry -> Change. At the order number field, press F3 to create a credit memo by serial number.  After the credit memo is created, you are in the Change mode of this credit memo and you may make additional changed if you need to. This is typically used when you receive a return serial number and you need to create the credit memo.

Sales Desk
If you create an  "I" type of order from Sales Desk, you will have to enter the associated serial number before Sales Desk can create the order for you.

Shipping Verification
Shipping Verification can collect serial numbers for you as it performs the verification.  The collected serial numbers will then be used for billing selection and written to the CPORDLS table.

BOMP Production Transaction
The BOMP Production Transaction will collect the serial numbers of the components.


EMK

Feedback and Knowledge Base

(thinking…)