Skip to content

What Information Is Stored in System 12 Months Table (SY12MONS)?

← Systems Manager
Release Date: 12/5/17

Updated: 8/8/18

Many users have noticed the existence of the SY12MONS (System 12 Months) table and would like to know what information is stored there and how to access it. This document is an attempt to explain what information is stored in this table, how you can access it, and how it gets updated.

What Kind of Data Are Stored in the SY12MONS Table?

SY12MONS - The System 12 Months Table is designed to store 12 months of information for many master tables. This includes Customer, Salesman, Vendor...etc.  Netcellent began to populate the SY12MONS table starting in early 2016.  Depending on when you updated your version of Elliott, you may have data in the SY12MONS table from as early as 2016.  So far, we have only limited user interface -- like Export Processor -- in Elliott to access the information in this table.  Therefore, you may consider using a third-party tool -- like Crystal Report -- to access Elliott SY12MONS data through ODBC.

The primary key index of the IMLOCHST table consists of the following columns:
  • File Name – SY12MONS_FILENAME
  • Reference - SY12MONS_REF_ID
  • Record Type – SY12MONS_TYPE
  • Year - SY12MONS_YEAR
File Name - The column file name designates the master file of the 12 months of information and can have the following value:
  • APVENFIL - AP Vendor File, Reference = Vendor Number
  • ARCDETAX - AR Tax Code File, Reference = Tax Code
  • ARCDETRM - AR Terms Code File, Reference = Terms Code
  • ARCDEVIA - AR Ship Via File, Reference = Ship Via Code
  • ARCUSFIL - AR Customer File, Reference = Customer Number
  • ARCUSTYP - AR Customer Type File, Reference = Customer Type
  • ARSLMFIL - AR Salesman File, Reference = Salesman ID
  • BMOPERID - BOMP Operation File, Reference = Operation-ID
  • BMOPITEM - BOMP Operation Item File, Reference = Operation-ID + Item Number
  • BMOPPCAT - BOMP Operation Category File, Reference = Operaton-ID + Prod Category
  • BMWCFIL - BOMP Work Center File, Reference = Work Center
  • CPSHPFIL - COP Ship To File, Reference = Customer Number + Ship-To Number
  • IMCATFIL - IM Category File, Reference = Produce Category
  • IMITMFIL - IM Item File, Reference = Item Number
  • IMLOCFIL - IM Location File, Reference = Location Code
  • IMLOCTYP - IM Location Customer Type File, Reference = Item-No + Loc + Customer Type
Reference - The reference is the primary key of the master file.  For example, for the customer file, the reference value is the customer number, and for the vendor file, the reference value is the vendor number.

Record Type - The record type can have a value from 1 to 4. Each SY12MONS record can store up to 4 different types of 12-month information.  If there are more than 4 different types of 12-month information to be stored for a master file, then we start to use record type 2, 3, 4, and so on and so forth.

Year - The year is the 4-digit year, like 2016, 2017...etc.

Because this table tends to be large, when you try to access it through ODBC, ADO.NET or Crystal Report, you must specify the file name for best performance. For example, if you want to get all customer information in 2017, you can use the following statement:

      SELECT * FROM SY12MONS where SY12MONS_FILENAME = 'ARCUSFIL' and SY12MONS_YEAR = 2017

If you want to get 12-month information for a certain customer -- say customer number 000013 for year 2017 -- then you can use the following statement:

      SELECT * FROM SY12MONS where SY12MONS_FILENAME = 'ARCUSFIL' and SY12MONS_REFERENCE = '000013' and SY12MONS_YEAR = 2017

How Are Records Created in SY12MONS?
The records in SY12MONS are currently created through the following processes: 
  • New AP Trx Posting
  • AP Check Posting
  • COP Sales Journal posting
  • BOMP Plus Work Order Processing
Take COP Sales Journal Posting as an example. When an invoice is posted, the system will determine whether to create a new record or to modify an existing record for a customer by checking to see whether the matching record for the customer and year exists. If the corresponding record does not exist, then a new record is created. If the record does exist, then it adds to the existing record the proper month based on invoice date. Compared to the implementation of customer accumulators like YTD, PTD, Last Year Sales and Cost Amount, SY12MONS has many benefits, such as:
  • It is self-maintained and there’s no month-end or year-end procedure to clear the accumulator.
  • It updates to the right bucket based on document date (i.e., invoice date). Therefore, if you have back-dated posting (over the month-end or year-end), it will not cause your SY12MONS history to mismatch with your sub module or general ledger.
  • It provides complete 12-month history breakdown.
  • You can store as many years' worth of history as you want.

Type of History Information Stored in SY12MONS

Each SY12MONS record type stores up to 4 different types of history information with the following column names:
  • SY12MONS_AMT1_1 - 12
  • SY12MONS_AMT2_1 - 12
  • SY12MONS_AMT3_1 - 12
  • SY12MONS_AMT4_1 - 12
The following is a list of what kind of information is stoerd in AMT1, 2, 3 and 4.
  • APVENFIL, Type 1, AMT1=Purchase Amount, AMT2=Amount pay, AMT3=Discount Taken
  • ARCDETAX - Type 1, AMT1=Sales, AMT2=Tax Amt, AMT3=Misc Amt, AMT4=Frieght Amt
  • ARCDETRM - Type 1, AMT1=Sales, AMT2=Number of Invoice
  • ARCDEVIA - Type 1, AMT1=Sales, AMT2=Weight, AMT3=Volume, AMT4=Number of Invoice
  • ARCUSFIL, Record 1, AMT1=Sales, AMT2=Cost, AMT3=Number of Invoice
  • ARCUSTYP, Record 1, AMT1=Sales, AMT2=Cost, AMT3=Number of Invoice
  • ARSLMFIL, Record 1, AMT1=Sales, AMT2=Cost, AMT3=Commission, AMT4=Number of Invoice
  • BMOPERID, Record 1, AMT1=Std Direct Labor Hour, AMT2=Actual Direct Labor Hour, AMT3=Std Shared Labor Hour, AMT4=Actual Shared Labor Hour
  • BMOPERID, Record 2, AMT1=Std Machine Hours, AMT2=Actual Machine Hours, AMT3=Std Burden Cost, AMT4=Actual Burden Cost
  • BMOPERID, Record 3, ATM1=Std Direct Labor Cost, AMT2=Actual Direct Labor Cost, AMT4=Std Shared Labor Cost, AMT4=Actual Shared Labor Cost
  • BMOPERID, Record 4, AMT1=Complete Qty, AMT2=Scrap Qty, AMT3=Std Matieral Cost, AMT4=Actual Material Cost
  • BMOPITEM, Record 1, AMT1=Std Direct Labor Hour, AMT2=Actual Direct Labor Hour, AMT3=Std Shared Labor Hour, AMT4=Actual Shared Labor Hour
  • BMOPITEM, Record 2, AMT1=Std Machine Hours, AMT2=Actual Machine Hours, AMT3=Std Burden Cost, AMT4=Actual Burden Cost
  • BMOPITEM, Record 3, ATM1=Std Direct Labor Cost, AMT2=Actual Direct Labor Cost, AMT4=Std Shared Labor Cost, AMT4=Actual Shared Labor Cost
  • BMOPITEM, Record 4, AMT1=Complete Qty, AMT2=Scrap Qty, AMT3=Std Matieral Cost, AMT4=Actual Material Cost
  • BMOPPCAT, Record 1, AMT1=Std Direct Labor Hour, AMT2=Actual Direct Labor Hour, AMT3=Std Shared Labor Hour, AMT4=Actual Shared Labor Hour
  • BMOPPCAT, Record 2, AMT1=Std Machine Hours, AMT2=Actual Machine Hours, AMT3=Std Burden Cost, AMT4=Actual Burden Cost
  • BMOPPCAT, Record 3, ATM1=Std Direct Labor Cost, AMT2=Actual Direct Labor Cost, AMT4=Std Shared Labor Cost, AMT4=Actual Shared Labor Cost
  • BMOPPCAT, Record 4, AMT1=Complete Qty, AMT2=Scrap Qty, AMT3=Std Matieral Cost, AMT4=Actual Material Cost
  • BMWCFIL, Record 1, AMT1=Std Material Cost, AMT2=Actual Material Cost, AMT3=Std Direct Labor Cost, AMT4=Actual Direct Labor Cost
  • BMWCFIL, Record 2, AMT1=Std Shared Labor Cost, AMT2=Actual Shared Labor Cost, AMT3=Std Burden Cost, AMT4=Actual Burden Cost.
  • BMWCFIL, Record 3, AMT1=Std Direct Labor Hour, AMT2=Actual Direct Laobr Hour, AM3=Std Shared Labor Hour, AMT4=Actual Shared Labor Hour
  • BMWCFIL, Record 4, AMT1=Std Machine Hour, AMT2=Actual Machine Hour, AMT3=Complete Qty, AMT4=Scrap Qty
  • CPSHPFIL, Record 1, AMT1=Sales, AMT2=Cost, AMT3=Number of Invoice
  • IMCATFIL, Record 1, AMT1=Sales, AMT2=Cost
  • IMITMFIL, Record 1, AMT1=Days Out of Stock (for All Locations, thus different from IMLOCHST)
  • IMLOCFIL, Record 1, AMT1=Sales, AMT2=Cost
  • IMLOCTYP, Record 1, AMT1=Sales, AMT2=Cost, AMT3=Number of Invoices
  • IMLOCTYP, Record 2, ATM1=Sales Desk Time Out of Stock, AMT2=SD Times Quoted, AMT3=SD Qty Quoted
  • IMLOCTYP, Record 3, AMT1 = Sales Desk Number Wis List, AMT2=Sales Desk Wish List Qty

Access 12 Months of History from Export Processor

After Elliott V8.2, you can access 12-month history information for certain master tables from the Elliott Export Processor, including:
  • Customer
  • Vendor
  • Customer Type
  • Ship Via
  • Tax Code
  • Terms Code
  • Ship To
  • Product Category
  • Location
  • Operation ID
  • Work Center
The following is an example of customer history that can be exported in the Customer category in Export Processor. You can go to the Export tab and find the Customer History node, which you can expand and export. See sample below:


Regenerating the SY12MONS Table

Certain master table information in SY12MONS can be regenerated for whatever reason. To do so, you can go to Global Setup-> Utilities-> Generate Statistical Info-> Generate-> Generate IMLOCHST Sales Information (IMGENLHP). The program initializes IMLOCHST and SY12MONS data and rebuilds it using data in the CPHSTTRX file. 

Previously, only IMLOCTYP records were added to the SY12MONS file with this utility. We now also support the regeneration of ARCUSFIL, ARCUSTYP, CPSHPFIL, IMCATFIL, and IMLOCFIL.


EMK

Feedback and Knowledge Base

(thinking…)