APPENDICES

Appendix A: Contact Persons for Questions about the WIN-202 System

Chris Boudreau (Director of Employment Data Systems) | (207) 621-5186 | email: Christopher.L.Boudreau@Maine.gov

Appendix B: Record Layout of Key Tables in the WIN-202 System

You may want to query the WIN-202 database to answer specific questions or solve problems. The following record layouts are the most frequently used tables. Click on the "Table Name" to open the table as an Excel spreadsheet.

• admin This file contains the administrative information for each account such as address, telephone #, etc.
• quarterly This file contains codes and employment and wages for a particular quarter.  Each quarter, new info is added by the extract
• control-file Contains old codes and refile data not on admin record
• macnaics Macro NAICS data

Each of the above tables has four elements that describe the data contained within them:

1. Field Name—Is the name used to describe the field in the database.
2. Data Type—Indicates what kind of information is stored in the field. The data types used most often in the WIN-202 system are character (containing letters, numbers, and special characters—the numbers are not used in calculations), integer (for numeric data that may be used in calculations), date (for holding dates) and logical (for holding yes or no answers).
3. Format—Describes what the data will look like when it is output.
4. Description—Gives more information about the field.

Appendix C: File Formats

The following file formats provide information by data element name, start/end position, and field length. Click on the "Table Name" to view the information in html format.

Quarterly Processing Load Screen File Formats:

• Admin Load File Format Used to load administrative data for accounts. Includes: UI/RUN, EIN, legal name, dba, liability dates, pred/succ accounts, address information, tax rate, and NAICS, county, ownership, residence, and zone codes.
• Quarterly Load File Format Used to load quarterly data for accounts. Includes: UI/RUN, year/qtr, status, county code, ownership code, NAICS, coverage type, town code, MEEI, tax rate, total wages, taxable wages, contributions due, monthly employment, monthly female employment, geographic codes, and state use data.
• EDI Load File Format See Appendix N - MWR File Layouts in the QCEW Operating Manual
• NAICS Load File Format Used to load NAICS data. Includes: UI/RUN, refile year, response code, batch ID, sequence, new NAICS, new county, new town, new ownership etc.
• CES Load File Format Used to load data from the Current Employment Statistics program. Includes: MSA, cell, size, state code, report number, ownership, county, township, key reporter, atypical flag, size class, UI/RUN, firm line, schedule, record status, current & prior year, month, employees, weekly pay, overtime, avgas. work hours & earnings, etc.
• Wage Record Load File Format Includes: UI account number, year/qtr, total wages, and unique SSN counter.
• MAG Media Data File Format See Appendix N - MWR File Layouts in the QCEW Operating Manual
• TRS Data File Format Used to load the touchtone response file data. Includes: FIPS, UI account number, CMI, refile year, ARS response code, and fax number.
• Geo Coded Data Load File Format Used to load geography coded data for accounts. Includes: PLA address, longitude, latitude, location code, place code, class code, and census ID.
• Wage Record Predecessor Successor Load File Format Includes: Associated year, associated quarter, first UI number, second UI number, and SSN count.
• CARS Load File Format Used only by states processing their ARCS forms centrally through Pearson. Includes: state FIPS code, UI account number, refile year, ars response code, batch number, and sequence number.

Miscellaneous File Formats:

• CIPSEA Short File Format A layout file of the micro records for the selected parameters. Includes: UI/RUN, check digit, year/qtr, county code, ownership, NAICS, zone, dba, legal name, physical address, UI address, phone number, status code, employment, wages, contributions due, comments codes, etc.
• CCS File Format Layout for the ccsfile2.txt file (detailed ccs file) generated using the Quarterly Processing, ARCS tab. This file is used in the CES program.
• Macro File Format Includes: FIPS, ownership code, NAICS, year/qtr, UI number of largest firm, largest firm's 3rd month employment, number of firms, number of units, month's sum of employment, sum of total wages, sum of taxable wages, sum of contributions, and comment codes.
• MWR File Format (For Import) Used to load multiple worksite report data. Includes: Program code, record type, UI/RUN, EIN, trade name, address, delivery point barcode, year/qtr, legal name, employment, wages, comments, FIPS, township code, and ownership code, etc.
• Quarterly Transaction File Format Includes: UI/RUN, date/time/type of transaction, year/qtr, batch program or user ID, status, FIPS, ownership code, NAICS, coverage type, geographic code, NSTA, MEEI, tax rate, employment, employment indicator, total quarterly wages, taxable quarterly wages, quarterly contributions, and comment codes.
• Regional Data Analysis File Format—Annual (Delimited [;]) Annual import format for RDAT tool. Includes: Year, description type, county or residence or zone code, 3-, 4-, or 6-digit NAICS, 4th quarter units, average monthly employment, total wages, and confidentiality indicator (if applicable)
• Regional Data Analysis File Format—Quarterly (Delimited [;]) Quarterly import format for RDAT tool. Includes: Year, description type, county or residence or zone code, 3-, 4-, or 6-digit NAICS, quarter units, average monthly employment, total quarterly wages, and confidentiality indicator (if applicable)
• Subset Input File Format Includes: FIPS, UI/RUN, and year/qtr.

Appendix D: NAICS/ALMIS File Structures

• Annual NAICS Publications - semicolon delimited
• Quarterly NAICS Publications - semicolon delimited
• ALMIS Type 1 - comma delimited
• ALMIS Type 2 - column formatted

Appendix E: Setting Dates Using Status Codes

The following outlines the steps involved:

if Initial Liability Date <> ? and End of Liability Date <> ? and Reactivation Date <> ? THEN RUN all three

if Initial Liability Date  = ? and End of Liability Date <> ? and Reactivation Date <> ? THEN RUN init blank

if Initial Liability Date <> ? and End of Liability Date = ? and Reactivation Date <> ? THEN RUN eol blank

if Initial Liability Date <> ? and End of Liability Date <> ? and Reactivation Date = ? THEN RUN react blank

if Initial Liability Date <> ? and End of Liability Date = ? and Reactivation Date = ? THEN RUN init only

if Initial Liability Date = ? and End of Liability Date <> ? and Reactivation Date = ? THEN RUN eol only

if Initial Liability Date = ? and End of Liability Date = ? and Reactivation Date <> ? THEN RUN react only

if Initial Liability Date = ? and End of Liability Date = ? and Reactivation Date = ? THEN RUN no date

 

no date

all three dates are ?

all quarters get Status Code = 9

 

react only

only the Reactivation Date is present

quarters prior to the Reactivation Date get Status Code = 9

quarters including and past the Reactivation Date get Status Code = 1

 

eol only

only the End of Liability Date is present

quarters prior to and including the End of Liability Date get Status Code = 9

quarters past the End of Liability Date get Status Code = 2

 

init only

only the Initial Liability Date is present

quarters prior to the Initial Liability Date get Status Code = 9

quarters including and past the Initial Liability Date get Status Code = 1

 

react blank

Initial Liability Date and End of Liability Date are present

if Initial Liability Date > End of Liability Date THEN

quarters prior to the Initial Liability Date get Status Code = 9

quarters including and past the Initial Liability Date get Status Code = 1

if End of Liability Date > Initial Liability Date THEN

quarters prior to the Initial Liability Date get Status Code = 9

quarters between and including Initial Liability date and the End of Liability Date get Status Code = 1

quarters after the End of Liability Date get Status Code = 2

if Initial Liability Date = End of Liability Date THEN

all quarters are Status Code = 2

 

eol blank

Initial Liability Date and Reactivation Date are present

if Initial Liability Date > Reactivation Date THEN

quarters prior to the Reactivation Date get Status Code = 9

quarters including and past the Reactivation Date get Status Code = 1

if Reactivation Date > Initial Liability Date THEN

quarters prior to the Initial Liability date get Status Code = 9

quarters including and past the Initial Liability date = 1

if Initial Liability Date = Reactivation date THEN

quarters prior to the Initial Liability date get Status Code = 9

quarters including and past the Initial Liability date = 1

 

init blank

Reactivation Date and End of Liability Date are present

if Reactivation Date > End of Liability Date THEN

quarters prior to and including the End of Liability date get Status Code = 9

quarters past the End of Liability Date and up to the Reactivation Date get Status Code = 2

quarters including and past the Reactivation Date get Status Code = 1

if End of Liability Date > Reactivation Date THEN

quarters prior to the Reactivation Date get Status Code = 9

quarters between and including the Reactivation Date and the End of Liability get Status Code = 1

quarters after the End of Liability Date  get Status Code = 2

if Reactivation Date  = End of Liability Date THEN

all quarters get Status Code = 2

 

all three

all three dates are present

The following matrix was used

I=E=R    I=R=E    E=I=R    E=R=I    R=E=I    R=I=E

I=E<R    E=I<R    R>E=I    R>I=E

I=E>R    R<I=E    E=I>R    R<E=I

I>E=R    E=R<I    I>R=E    R=E<I

I<E=R    E=R>I    I<R=E    R=E>I

I=R<E    E>I=R    E>R=I    R=I<E

I=R>E    E<I=R    E<R=I    R=I>E

 

I>E>R    R<E<I

I>R>E    E<R<I

E>R>I    I<R<E

E>I>R    R<I<E

R>I>E    E<I<R

R>E>I    I<E<R

 

I>E<R    R>E<I COVERED BY I>R>E OR R>I>E OR I=R>E

I<E>R    R<E>I COVERED BY E>I>R OR E>R>I OR I=R<E

I>R<E    E>R<I COVERED BY I>E>R OR E>I>R OR I=E>R

I<R>E    E<R>I COVERED BY R>I>E OR R>E>I OR I=E<R

E<I>R    R<I>E COVERED BY I>R>E OR I>E>R OR R=E<I

E>I<R    R>I<E COVERED BY E>R>I OR R>E>I OR R=E>I

 

I=E=R

if Initial Liability Date = End of Liability Date = Reactivation Date THEN

quarters are Status Code = 2

 

I=E<R

if Initial Liability Date = End of Liability Date and End of Liability Date < Reactivation Date THEN

quarters prior to the End of Liability Date are Status Code = 2

quarters including and after the Reactivation Date are Status Code = 1

 

I=E>R

if Initial Liability Date = End of Liability Date and End of Liability Date > Reactivation Date THEN

quarters are Status Code = 2

 

I>E=R

if Initial Liability Date > End of Liability Date and End of Liability Date = Reactivation Date THEN

quarters prior to the Initial Liability Date are Status Code = 2

quarters including and after the Initial Liability Date are Status Code = 1

 

I<E=R

if Initial Liability Date < End of Liability Date and End of Liability Date = Reactivation Date THEN

quarters prior to the Initial Liability Date are Status Code = 9

quarters including and after the Initial Liability Date are Status Code = 1

 

I=R<E

if Initial Liability Date = Reactivation Date and Reactivation Date < End of Liability Date THEN

quarters prior to the Initial Liability Date are Status Code = 9

quarters between and including the Initial Liability Date and the End of Liability are Status Code = 1

quarters after the End of Liability Date are Status Code = 2

 

I=R>E

if Initial Liability Date = Reactivation Date and Reactivation Date > End of Liability Date THEN

quarters prior to the Initial Liability Date are Status Code = 2

quarters including and after the Initial Liability Date are Status Code = 1

 

I>E>R

if Initial Liability Date > End of Liability Date and End of Liability Date > Reactivation Date THEN

quarters prior to the Reactivation Date are Status Code = 2

quarters between and including the Reactivation Date and the End of Liability Date are Status Code = 1

quarters after the End of Liability Date and prior to the Initial Liability Date are Status Code = 2

quarters after and including the Initial Liability Date are Status Code = 1

 

I>R>E

if Initial Liability Date > Reactivation Date and Reactivation Date > End of Liability Date THEN

quarters prior to the Reactivation Date are Status Code = 2

quarters including and after the Reactivation Date are Status Code = 1

 

E>R>I

if End of Liability Date > Reactivation Date and Reactivation Date > Initial Liability Date THEN

quarters prior to the Initial Liability Date are Status Code = 9

quarters between and including the Initial Liability Date and End of Liability Date are Status Code = 1

quarters after the End of Liability Date are Status Code = 2

 

E>I>R

if End of Liability Date > Initial Liability Date and Initial Liability Date > Reactivation Date THEN

quarters prior to the Reactivation Date are Status Code = 9

quarters between and including the Reactivation Date and End of Liability Date are Status Code = 1

quarters after the End of Liability Date are Status Code = 2

 

R>I>E

if Reactivation Date > Initial Liability Date and Initial Liability Date > End of Liability Date THEN

quarters prior to the Initial Liability Date are Status Code = 2

quarters including and after the Initial Liability Date are Status Code = 1

 

R>E>I

if Reactivation Date > End of Liability Date and End of Liability Date > Initial Liability Date THEN

quarters prior to Initial Liability Date are Status Code = 9

quarters between and including the Initial Liability Date and End of Liability Date are Status Code = 1

quarters after the End of Liability Date and prior to the Reactivation Date are Status Code = 2

quarters including and after the Reactivation Date are Status Code = 1