UNEMPLOYMENT INSURANCE
DATA VALIDATION HANDBOOK
Contribution Reports
October 2002


CONTENTS

Introduction
A. Overview of Data Validation: Principles and Approach
Principles
Approach
B. Data Errors Identified Through Validation
C. Data Sources for Federal Reporting and Validation
D. Basic Validation Approach
E. Units of Analysis
F. Handbook Overview
Modules
Appendices
G. Walkthrough of the Data Validation Methodology
H. Overview: Preparation for Data Validation — Task 1
Module 1: Report Validation – Item Count
A. Specifications for Creation of Report Validation (Extract) Files
1. Transactions to be Included in the Extract File
2. Layout of Records in Data Validation Extract Files
3. Importing Extract Files into UI Tax Data Validation Software
4. Report Validation File Specifications
5. Report Quarter Terminology
6. Issues to Consider When Producing RV Files
B. Overview: Module 1
C. Reporting Data Validation Findings
1. Report Validation (RV) Reports
2. Process for Completing the RV Spreadsheets
D. Example — Data Validation Reports
E. Final Results
Module 2: Data Element Validation
Module 2.1 — Error detection
A. Overview of Error Checks
B. Detailed Information on Screening Tests
1. Duplicate Detection
2. Parameters for Inclusion in Report Validation File
Module 2.2 — File Integrity Validation (FIV)
A. Steps in File Integrity Validation
B. Overview of Module 2.2
Module 2.3 — Range Validation
Module 2.4 — Corrective Action
Module 3: Federal Definitions and State-Specific Validation Instructions
Module 4: Tax Performance System: Acceptance Sample Validation
Tasks to Complete Acceptance Sample Validation
Recording the Results of Acceptance Sample Validation
Module 5: Wage Item Validation
Methodology for Completing Wage Item Validation
Example of a Wage Item Validation Worksheet
Appendix A: Report Validation Specifications
Part I: File Record Layouts for Extract Files by Population
Part II: Subpopulation Specifications: Tables for Each Population.
Population 1
Population 2
Population 3
Population 4
Population 5
Appendix B: Independent Count

INTRODUCTION

A. OVERVIEW OF DATA VALIDATION: PRINCIPLES AND APPROACH

Principles

States regularly report to the U.S. Department of Labor (DOL) under the Unemployment Insurance Required Reports (UIRR) system. In particular, states submit a quarterly report on their activities collecting Unemployment Insurance (UI) employer contributions (taxes). This is the Employment and Training Administration (ETA) 581 report entitled “Contribution Operations.”

Data from the ETA 581 report are used for three critical purposes: (1) allocation of UI administrative funding based on state workload, (2) performance measurement to ensure the quality of state Unemployment Insurance program operations, and (3) calculation of state and national economic statistics. Exhibit I.1 summarizes the types and use of the data. Exhibit I.2 displays the ETA 581 report.

EXHIBIT I.1
TYPES AND USES OF ETA 581 DATA

ETA Logo
   Search : 
Advanced Search
About ETAFind Job & Career InfoBusiness & IndustryWorkforce ProfessionalsGrants & ContnewractsETA LibraryForeign Labor CertificationPerformance & ResultsRegions & States
 
Home > DV > Data Validation Handbook
 
Sitemap  Printer Friendly Version
Data Type Funding/
Workload
Performance/Tax
Performance System
(TPS) Computed
Measures
Economic
Statistics
Active Employers X X X
Report Filing X
Status Determinations X X
Accounts Receivable X X
Field Audits X
Wage Items X X

Because the data have these critical uses, it is essential that states report their activities accurately and uniformly. Data validation is intended to assure accurate reporting of employer contribution activities. Two principles underlie a comprehensive data validation process:

  1. If data are collected, they should be thoroughly validated to ensure that they are valid and usable.
  2. To be practical for national implementation, the validation approach must be efficient and cost effective.

Exhibit 1.2 Form ETA-581

Exhibit 1.2 - Form ETA 581 Contribution Operations

These two principles have been used to develop this system for validating data that states report to the U.S. Department of Labor on the ETA-581 form. The comprehensive data validation program described in this handbook ensures the accuracy of the UIRR data. It validates most items on the ETA 581 report using a process that is highly automated and complements existing quality control programs such as the Tax Performance System (TPS).

This handbook explains in detail how to do data validation. In addition to the handbook, DOL provides a software application that processes state-produced extract files, then uses them to validate the ETA 581 report.

Approach

The basic approach used in data validation is to reconstruct the numbers that should have been reported on the ETA 581 form. Because state UI records are highly automated, states can develop computer programs that go through their electronic databases and extract all transactions that should have been on the report. Automation reduces the burden on validators and state data processing staff as they extract records from state files, assemble those records for analysis, and assess validation results. Once transactions are extracted, they are subjected to a series of “logic rules.” These rules test the accuracy of the reconstructed data, assuring that states have used the most definitive source of information and have adhered to Federal definitions. After it is determined that the extract data meet the logic rules, they are used to produce validation counts that are compared to what the state has reported. If the validation counts confirm what was reported, within plus or minus 2 percent, then the reporting system is judged valid.

Modules 1, 2, and 3 of this handbook explain how to create and test reconstruction files. Although the basic approach is standard for all states, the instructions are state-specific in that they present many details of the validation process using terminology and data elements familiar to each individual state.

The instructions guide the state as it goes back to its automated databases to select transactions for analysis. Following the specifications in the handbook, the state extracts all records that should be counted on the Federal report.

Once the state has developed an accurate reconstruction of the transactions that should have been reported on the ETA 581, the handbook guides the state in the use of UI Tax Data Validation software to compare the reconstructed counts to the values that were actually reported.

Modules 4 and 5 of this handbook provide instructions for two validation tasks that do not use an automated reconstruction approach. Module 4 tests the procedures for selecting samples used in the Tax Performance System. Module 5 describes procedures for validating counts of wage items processed.

B. DATA ERRORS IDENTIFIED THROUGH VALIDATION

Validation is intended to ensure that data on the ETA 581 report are error free. Thus the design of the data validation system is grounded in an understanding of likely sources of reporting error — both systematic errors and random errors. Systematic errors involve faulty design or execution of reporting procedures or the automated programs that generate reported counts. Random errors are mistakes in judgment or data entry that corrupt the information entered in data systems or recorded on reports. The validation design addresses both types of error.

Many of the more common random, judgment, and definitional errors can be detected through existing Tax Performance System (TPS) reviews. TPS acceptance samples for status determinations and field audits are used to evaluate the accuracy of transaction classification and posting. TPS system reviews look for strong supervision, up-to-date documentation, and other controls that limit human error. Data validation does not attempt to repeat TPS reviews. Rather, it supplements TPS with a review of systematic errors while remaining alert to additional sources of random error.

Consistent and accurate reporting requires both good data and accurate systems for reporting the data. Data validation and TPS together test whether data are entered accurately and whether they are counted correctly.

C. DATA SOURCES FOR FEDERAL REPORTING AND VALIDATION

States use different methods to prepare the ETA 581 report. Some states produce the Federal reports directly from the employer contribution database. Computer programs scan the entire database to select, classify, and count transactions. Other states produce a database extract or statistical file as transactions are processed, essentially keeping a running count of items to be tabulated for the report. Still other states use a combination of these methods. The basic approach to data validation is the same no matter how the report is developed — states reconstruct the transactions that should have been reported and do so using standard national criteria.

The validation methodology is flexible in accommodating the different approaches used by states. However, validation is most effective when validation data are produced directly from the employer contributions database. For cost reasons and to minimize changes in data over time, some states prefer to use daily, weekly, or monthly statistical extract files instead. When extract files are used, other types of system errors may occur. Reportable transactions may be improperly excluded from the employer master file. Furthermore, the statistical file may contain corrupt data. The statistical file is not used as part of the daily tax system and, therefore, errors may not be detected and corrected through routine agency business.

The only way to test for these problems is to independently reconstruct or query the employer master file. States that produce validation data from the same extract files used to produce the ETA 581, rather than directly from the database, must ensure that the extract files contain all the appropriate employers or transactions. The way to do this is to recreate the logic used to produce the reports. This handbook includes a validation tool, “independent count validation,” specifically for this purpose. The specific type of independent count (simple query, multiple queries, cross tabulation) must be determined by state programming staff.1

Exhibit I.3 outlines variations in the validation methodology, based on typical state approaches to ETA 581 reporting and data validation reconstruction. To identify the specific validation methodology to be implemented, the state validator or Federal representative should identify the state’s ETA 581 report source and validation reconstruction source for each population to be validated.

D. BASIC VALIDATION APPROACH

States themselves perform validation and report the results to DOL. The UI Tax Data Validation software provided by DOL processes data extract files produced by the state and generates all required validation reports.

Data validation provides a reconstruction or audit trail to support the counts and classifications of transactions that were submitted on the ETA 581 report. Through this audit trail, the state proves that its UIRR data have been correctly counted and reported. For example, if a state reports 5,000 reimbursable employers at the end of the quarter, then the state must create a file listing all 5,000 employers as well as relevant characteristics, such as the Employer Account Number (EAN), employer type, liability date, number of liable quarters, and sum of wages in those quarters. Analysis of these characteristics can assure validators that the file contains 5,000 correctly classified employers. The reported number is proved and the report is considered valid.

To assure that the reconstruction of report counts has been done correctly, UI Tax Data Validation software tests the accuracy of the reconstruction process:

There are two additional data edits that are only partially automated:

These checks build validators’ confidence in the reconstruction count. Thus, when they tabulate the numbers of transactions in the validation file and compare them to the reported numbers, they can feel sure that they are accurately testing the validity of the reports.

EXHIBIT I.3
VARIATIONS IN VALIDATION METHODOLOGIES BASED ON STATE
APPROACHES TO REPORTING AND RECONSTRUCTION

Scenario Transactions Overwritten on Database ETA 581 Data Validation Independent Count Required Source Documentation Review Required Comments
Program Type Source Timing Program Type Source Timing
1 No Count Database Snapshot DRE Database Snapshot No No Best scenario because comparing snapshots eliminates timing discrepancies
2 No Count Statistical file Daily DRE Database Snapshot No No Database is only reconstruction source. There could be changes in transaction characteristics(but will find all transactions).
3 No DRE Database Snapshot DRE Database Snapshot Yes No Reporting and validation are the same program. Independent count may mirror that program.
4 No DRE Statistical file Daily DRE Statistical file Daily Yes Yes Since transactions are not overwritten, states should be able to do Scenario 2 instead.
5 Yes DRE Statistical file Daily DRE Statistical file Daily NA NA No alternative validation source. Cannot reconstruct from the database. Not thorough validation.
6 Yes Count Statistical file Daily Must create daily extract a NA NA NA NA Cannot reconstruct from database. Must change reporting process to Scenario 5.

NOTE: Snapshot is oft the last day of the reporting period.
DRE= Detail Recocrd Extract
NA= Not Available

After states complete validation, the Federal partner may audit the results. To facilitate the Federal audit, the state must prepare and maintain a validation package. This package enables the Federal auditor to easily follow the validator’s work, without requiring the state to print out entire reconstruction files. The components of the package are discussed in more detail in Modules 1 and 2.2

E. UNITS OF ANALYSIS

There are 50 ETA 581 report items to validate.3 Each item has its own set of definitions, rules, and validation requirements. To minimize the burden of reconstructing item counts, the validation process is streamlined by breaking it down into manageable components and providing a software package that does much of the analysis. The data to be validated are grouped into mutually exclusive populations and subpopulations. The validation process itself is organized into a series of modules that are the logical steps in the process. This structure lets the state validation team focus on one type of data at a time, and validate each type of data one step at a time.

A single employer account transaction may be counted in several different ETA 581 report items. For example, a contributions report that is filed timely is counted in two items for the current report quarter (timely reports and reports secured) and in one item in the following report quarter (reports resolved).

Validation reconstructs and analyzes each transaction only once, even if it is counted in multiple cells on the report. Employers or transactions are classified into mutually exclusive groups — specifically, five types of employers or transactions (populations), which are further divided into 46 mutually exclusive groups (subpopulations). All validation counts are built from these subpopulations. The five populations are: (1) Active Employers, (2) Report Filing, (3) Status Determinations, (4) Accounts Receivable, and (5) Fields Audits.

Exhibit I.4 lists the ETA 581 populations and subpopulations that are reconstructed and the number of report items being validated for each. It also describes the dimensions along which populations are sorted as they are divided into subpopulations.

EXHIBIT I.4
ETA 581 REPORT, BY TRANSACTION POPULATION

Transaction
Population
ETA 581
Line
Numbers
Dimensions Used to
Distinguish Subpopulations
Number of
Report Items
Number of
Subpopulations
1. Active Employers 101 Employer status
  • contributory
  • reimbursing
3 2
2. Report Filing 201 Timing of report receipt and resolution
  • timely
  • secured within the quarter
  • resolved within two quarters
6 16
3. Status Determinations 301 Type of status determination
  • new
  • successor
  • inactive
  • terminated
Time lapse of the determination
7 8
4. Accounts Receivable 401
402
403
404
Type of receivable processing
  • amounts established
  • liquidated
  • declared uncollectible
  • removed from the report
  • outstanding debt.
22 16
5. Field Audits 501
502
Employer size
  • small
  • large
Audit result
  • change
  • no change
11 4
Wage Items Processed 101   1 N/A

F. HANDBOOK OVERVIEW

State staff produce a series of extract files that contain all relevant data for each population to be validated. These extract files are imported to the UI Tax Data Validation software which reads these files, checks for errors, eliminates invalid records, sorts populations into subpopulations, counts transactions, compares validation counts to reported counts, and displays the results in spreadsheet formats. The UI Tax Data Validation software also produces a list of invalid records and the type of error that caused each record to be rejected.

To assure that reported data are accurate and meet Federal reporting definitions, there are five validation processes or “modules.” These modules provide all the tools to be used in validating the quantity and quality of Federally reported data.

Modules

Appendices

G. WALKTHROUGH OF THE DATA VALIDATION METHODOLOGY

Exhibit I.5 illustrates the data validation process detailed in the handbook modules and appendixes, using ETA 581 active employers as an example.4

  1. The “UI Employer Database” represents the state’s master file, or perhaps several databases storing data on different parts of the tax operation. As states manage employer contributions, they enter data in the database. There may also be times when the system automatically places data in employer accounts, for example, when an automated flag is placed in an employer file to indicate that a report is delinquent.

    As Exhibit I.5 shows graphically, the state may view data from the database in several different ways. A state may query the database, for example, by referring to a query screen such as the “Tax Transcript” at the top right of the figure. The state may also produce more formal reports such as the ETA 581, a portion of which is shown at the top left.


  2. To validate the official report, the state data validation staff develops a detailed record extract, or reconstruction file — a list of all transactions on the state database that should be reported for a single item on the ETA 581. The file is displayed for review by validators (middle figure in left column).

    The UI Tax Data Validation software displays the file for review by validators, compares the reconstructed count (here 6) to the reported count (also 6), and checks transactions for duplicates and other errors.

    The state should concurrently generate the ETA 581 and the validation file (the reconstructed “audit trail”) from the employer database. At the same time the state should produce supporting documentation from the employer database (for example, query screens) for File Integrity Validation (FIV) (Module 2).5 Alternatively, the state may generate the ETA 581 and/or the validation file from a detail record extract statistical file (such as a TPS universe).

    The validator assembles a package of materials — electronic and hard copy listings of the beginning and end of reconstruction files, population spreadsheets, worksheets, and screens — to be used during validation and for review by an auditor from the U.S. Department of Labor.


  3. The validator, following the “step” numbers in each column heading on the reconstruction specification, tests the integrity of the reconstructed data. The bottom right portion of Exhibit I.5 shows a page of Module 3. The “Steps” in Module 3 contain state-specific instructions for checking that the reconstruction files have been built correctly.


  4. The handbook refers to state source documentation (usually query screens) that the validator compares to the reconstruction file to complete FIV.


  5. The validator, again guided by step numbers in each column heading, refers to specific steps in the state-specific validation handbook to validate that the individual data elements have been accurately classified and match the Federal definitions.


  6. If necessary, after reconstruction files have been tested and corrected, the validator makes a final comparison between the reconstruction counts and the report counts.
Figure entitled Exhibit I.5 Overview of Unemployment Tax Data Validatin Methodology

H. OVERVIEW: PREPARATION FOR DATA VALIDATION — TASK 1

From time to time this handbook provides recommendations for managing the validation process. These recommendations are described in “Task” exhibits. For each task the handbook provides a listing of activities to be completed and the staff who are likely to take the lead on each activity. Staff roles and responsibilities for preparing for data validation are summarized in Exhibit I.6 below. Staff roles will be divided among:

EXHIBIT I.6
TASK 1: PREPARING FOR DATA VALIDATION

Activity Roles
Assemble data validation team. Managers
Review handbook. Validators, ADP
Attend training. Share training with staff who did not attend. Validators, ADP
Review and update state-specific information in Module 3 of the handbook. Send U.S. DOL and its contractor a copy of the module with any needed changes clearly marked. ADP, with help from validators
Develop a data validation plan with: • Schedule for completing data processing and validation review for each population. • Staff assignments for each step in the data validation process. Managers, validators, ADP

The remainder of this handbook guides users through the data validation process. Modules 1, 2, and 3 describe the major steps that states must follow when conducting data validation. Appendices provide the forms and specifications needed as the state proceeds through these steps. The general process is that the state produces a series of files that list all transactions that are to be counted on the ETA 581 report. The files are imported into UI Tax Data Validation software which reads transactions, compares validation counts to reported counts, and displays the results in a spreadsheet format.

1 There is no way to accurately reconstruct the report count when the statistical file contains transactions that are no longer present in the database (e.g., when it includes status determinations deleted from the main database after a corrected status determination for the same employer).

2 This handbook provides detailed validation instructions for each state to ensure that state and Federal staff understand all relevant aspects of the state’s employer contributions reporting system. In specifying how to reconstruct reported transactions, the methodology explains the criteria that states should use in their Federal employer contributions reporting. Thus in addition to guiding the states through the validation process, this handbook provides technical guidance on Federal ETA 581 reporting requirements.

3 Wage items processed (item 5 on the ETA 581) are validated but through a less comprehensive process. They are not included as a reconstruction population.

4 The validation file, sort file, and state-specific handbook have been modified slightly in Exhibit I.5 for presentation purposes. Utah’s Tax Transcript screen and handbook are shown.

5 Given the highly automated nature of tax data validation, database screens are generally the only supporting documentation needed. Therefore, this handbook refers to screens, rather than to supporting documentation, throughout. To prevent inconsistencies due to timing, screens to validate the accuracy of transfer of data from the database should be printed at the same time as the reconstruction file is created.

Module 1

REPORT VALIDATION – ITEM COUNT

Module 1, Item Count, describes the process that state staff use to produce reconstruction/extract files. These files contain all transactions from the state Unemployment Insurance database that should be counted for a single quarter on the Federal report.

Reconstruction files are created for five different categories of data, referred to as data populations. The populations are listed in Exhibit 1.1 along with their corresponding sections of the ETA 581 report.

EXHIBIT 1.1
SUMMARY OF REPORT VALIDATION POPULATION FILES

File Specification Population ETA 581 Line Number
1 Active employers 101
2 Report filing 201
3 Status determinations 301
4 Accounts receivable 401, 402, 403, 404
5 Field audits 501, 502

Once created, the reconstruction files are imported into the UI Tax Data Validation software. That software reconstructs counts of transactions or employers and compares those counts to the numbers that were submitted on the ETA 581 report for the comparable quarter.

Before the data counts and validation results are judged to be final, the validation files are subjected to both automated and manual error-checking procedures. These checks eliminate invalid cases, ensuring that the reconstruction is based on an accurate and unduplicated count of transactions.

A. Specifications for Creation of Report Validation (Extract) Files

Appendix A contains instructions that specify how extract files are to be created for each population and subpopulation:

1. Transactions to be Included in the Extract File.

To create the report validation file, the state must go back to its source files (database) and select every transaction that meets the criteria for inclusion. These criteria can be found in Appendix A, Part I. An example of the inclusion criteria for Population 1 appears in Exhibit 1.2.

The narrative in this section describes which transactions or employers to select for inclusion in the extract file for each population. Inclusion criteria should be used in conjunction with the information in the column entitled “Module 3 Reference.” That column refers the reader to a “step” in Module 3 of this handbook. If you turn to that step, you will find more detailed information regarding the definition of the data element being used by the inclusion criteria.

Example (see Exhibit 1.2):

The instructions tell the state that the extract file for Population 1 should include only employers that are active as of the last day of the report quarter (RQ). If the state needs more information on what is meant by an active employer, this is provided in Step 3A of Module 3.

2. Layout of Records in Data Validation Extract Files

Transactions must be extracted from the state database in the specified standard format. For each transaction selected, the state will list many data elements. Those elements are the characteristics of the transaction that are necessary to determine the subpopulation (and hence reporting item) to which it should be assigned.

Record layouts for each of five populations are included in both the software and the User Guide. The User Guide also provides instructions for using the record layouts. Inclusion criteria and record layout for Population 1 are presented here to illustrate the process. States should create an ASCII file with one column for each field listed in the record layout. The fields should be listed in the order they appear in the layout. Commas should be entered to separate each field.

EXHIBIT 1.2
POPULATION ONE INCLUSION CRITERIA

Population 1 should include all employers who were active as of the last day of
the report quarter.
To be included:
  • The employer must have an employer type that is active (Step 3A).


  • The most recent liability date, initial or reopen, must be prior to the
    end of the quarter and must be later than any inactive/terminated date
    that appears in the employer’s file (Steps 4 & 5).


  • The employer must have submitted at least one report indicating
    wages paid in the eight consecutive quarters ending with RQ. (Step
    7A).
  • An example of the second part of the record layout specifications for Population One is found in Exhibit 1.3. To help you understand the exhibit let us discuss each column.

    Number and Field Name identify the characteristics or fields that should be listed for each transaction.

    Module 3 Reference guides state staff to a portion of the handbook where they can find more detailed instructions for the information to be entered in the field. Module 3 of this handbook is organized by steps. Turn to Module 3 and you will see step numbers prominently displayed at the bottom and top of each page.

    When you turn to the step indicated in the record layout, two types of information will be provided regarding the field. First, there will be an annotated version of the Federal definition for the field. Second, at the start of the step will be a section labeled File Integrity Validation. In that section there will be a document and rule that indicate where the information for the field can be found in the state database. The location will be identified by reference to fields in query screens used in the state’s UI tax automated system (“document”) and particular fields on each screen (“rule”). .

    Field Description provides a narrative explanation of the data to be recorded in the field.

    Data Type/Format specifies how the data should be displayed. Where the data are numeric, the word “ Number” will appear. Numerical fields will accept ten digits. Fields containing dollar amounts will accept numerical entries of any size. Of dollar amounts are listed showing cents, the decimal points should be included. All other numerical fields should be whole integers with no decimal points.

    If the field is alphanumeric the word “Text” will appear. The listing also indicates the alpha codes that must be used. The alpha codes are the generic indicators that allow common coding across states. States should also list their own indicators. The listing should include both the generic and state codes separated by a dash, for example, C-01, where ‘C’ is the generic code and ‘01' is the state code for employer type that is listed in the database for this transaction.

    Exhibit 1.4 displays a sample printout of a portion of an extract file in the prescribed format.

    EXHIBIT 1.3
    SAMPLE RECORD LAYOUT FOR POPULATION ONE

    Extract file type is: ASCII, comma delimited columns. The record layout should be used to create a reconstruction file of all active employers on the last day of the Report Quarter (RQ) covered by the ETA 581 being validated. Reconstruction should be done at the end of the RQ being validated (when the ETA 581 report program is run). The module 3 reference indicates the step where the state-specific values are documented. Data must be in the order and format listed in the Data Type/Format column. The Data Type/Format column also indicates the generic values for text fields. These must be followed by a dash and the statespecific value. All fields listed as mandatory are required fields. All other field values are required only for specific types of employers as noted. If a state declares employers inactive after less than eight liable quarters of zero wages, the header of the reconstruction file should indicate the maximum number of liable quarters of zero wages that are permitted.

    Number Field
    Name
    Module 3
    Reference
    Field Description Data Type/Format
    1 OBS   Sequential number, start at 1. Number - 00000000 (Mandatory)
    2 EAN Step 1A Employer Account Number Number - 00000000 (Mandatory)
    3 Employer Status
    Indicator
    Step 3A Indicate that the employer is an active employer. Text – A (Mandatory)
    4 Employer Type Step 2A
    Step 2B
    Indicate whether the employer type is
    contributory or reimbursable.
    Text – C; R (Mandatory)
    5 Liability Date
    (Initial)
    Step 4A Indicate the date on which the employing unit
    meets the state’s definition of an employer
    and is registered and required to file reports.
    Date – MM/DD/YYYY (Mandatory)
    6 Liability Date
    (Reopen)
    Step 4B Indicate the date an employing unit which
    was previously inactivate or terminated again
    meets the definition of employer in the state
    unemployment compensation laws.
    Date – MM/DD/YYYY
    (Mandatory if Liability Date
    Initial blank)
    7 Inactive/
    Terminated
    “as of” Date
    Step5 Indicate the effective date for the termination
    or inactivation status of the employer.
    Date – MM/DD/YYYY
    8 Activation
    Processing
    Date
    Step 15 Indicate the date on which an account was
    established on state’s system for an
    “employer,” under the state unemployment
    compensation law.
    Date – MM/DD/YYYY
    9 Number of
    Liable
    Quarters
    Step 7B Indicate the number of consecutive quarters
    between the date the employer was last
    activated on state’s system and the report
    quarter. If the number of liable quarters is
    eight or more, the value should be reported as eight.
    Number – 0
    (Mandatory)
    10 Wages in
    Quarter 1
    Step 7A Total wages for the employer in the quarter
    prior to the report quarter. Field is unlimited
    length.
    Number – 000000000000.00
    (Mandatory)
    11 Wages in
    Quarter 2
    Step 7A Total wages for the employer in the second
    quarter prior to the report quarter. Field is unlimited length.
    Number – 000000000000.00
    (Mandatory)
    12 Wages in
    Quarter 3
    Step 7A Total wages for the employer in the third quarter prior to the report quarter. Field is unlimited length. Number – 000000000000.00
    (Mandatory
    13 Wages in
    Quarter 4
    Step 7A Total wages for the employer in the fourth quarter prior to the report quarter. Field is unlimited length. Number – 000000000000.00
    (Mandatory
    14 Wages in
    Quarter 5
    Step 7A Total wages for the employer in the fifth quarter prior to the report quarter. Field is unlimited length. Number – 000000000000.00
    (Mandatory
    15 Wages in
    Quarter 6
    Step 7A Total wages for the employer in the sixth quarter prior to the report quarter. Field is unlimited length. Number – 000000000000.00
    (Mandatory
    16 Wages in
    Quarter 7
    Step 7A Total wages for the employer in the seventh quarter prior to the report quarter. Field is unlimited length. Number – 000000000000.00
    (Mandatory
    17 Wages in
    Quarter 8
    Step 7A Total wages for the employer in the eighth quarter prior to the report quarter. Field is unlimited length. Number – 000000000000.00
    (Mandatory
    18 User Field   User-defined field. Text (Optional)

    EXHIBIT 1.4
    SAMPLE EXTRACT FILE FOR POPULATION FOUR

    Figure entitled EXHIBIT 1.4 SampleExtract File for Population Four

    3. Importing Extract Files into UI Tax Data Validation software

    Once the file is arrayed according to the standard format it can be imported into the UI Tax Data Validation software. Instructions for how to import the file can be found in the User Guide. The software will screen each record in the file, reject it if it is invalid and assign it to a proper counting category if it is valid. Module 2 discusses the data tests that occur as the file is imported.

    4. Report Validation File Specifications

    Once the extract file for each population has been imported, the UI Tax Data Validation software organizes each population into its component subpopulations. These subpopulations are designed to correspond to the basic counting categories for reporting. For example, the extract file imported into the software for Population 5 will contain all field audits completed during a report quarter. The population will be automatically divided into subpopulations such as large employer audits with “no change” because the Federal report counts the number of large employers audited and the number of those audits that resulted in changes to an employer’s contribution report.

    Even though states can allow the UI Tax Data Validation software to sort populations into subpopulations, it is important that they understand how the software makes those assignments. Subpopulations are the basic counting categories that will be used to judge whether state reports are valid.

    Understanding the concept of subpopulations will also help you understand the population’s contents. Remember that the records included in each subpopulation are mutually exclusive. Each transaction is to be listed in one and only one subpopulation. The population is a combination of the records from all subpopulations. By examining the specifications for each subpopulation, state staff can understand the pieces that make up the population as a whole, and they can do so one piece at a time.

    The structure of the subpopulations is specified in tables contained in Appendix A, Part II. Exhibit 1.5 is a copy of the first page of the reconstruction file specifications for population 1, Active Employers.2 It may be helpful to walk through the key features of the specifications, by the numbers.

    1. Appendix A, Part II, includes a table for each of five transaction populations. Each population captures a single category of data.

      Example: This table is for population 1, active employers.


    2. Each transaction population is further divided into subpopulations to match the types of transactions that are reported on ETA 581. For example, population 1 is subdivided into two subpopulations by type of employer (contributory and reimbursing). The first column in the table lists the subpopulation numbers.

      Each row in the table provides the specifications for the entire reconstruction file for the subpopulation. This subpopulation will be a portion of the reconstruction file for the whole population. The state produces the extract file with the full set of data elements required by this specification. The UI Tax Data Validation software sorts the transactions into appropriate subpopulations.

      Example: This row shows that the UI Tax Data Validation software selects appropriate records from the extract file to create a list of all contributory employers that were liable at the end of the report quarter (subpopulation 1.1). Other employers from the extract file (i.e., reimbursing employer) are assigned to subpopulation 1.2.


    3. Written descriptions of the subpopulations follow each table in Appendix A, Part II. These descriptions are the first place to look to understand the subpopulations. Once the narrative description has made the conceptual framework clear, it should be easier to understand the more cryptic symbols in the grid.


    4. The second column in the specification indicates the ETA 581 item(s) to which this subpopulation count is compared. In population 1, there is a one-to-one match between subpopulation counts and report items. However, in other populations one subpopulation may be used in the validation of two or more report items.

      Example. In population 2, the validation count for Total Reports Secured is calculated by adding together validation counts for two subpopulations — subpopulation 2.1 (reports received timely) and subpopulation 2.2 (non-timely reports secured by the end of the report quarter). It is this sum that is compared to the value found on ETA 581 for Item 7, Secured reports. The validation count for subpopulation 2.1, by itself, is also the comparison value for 581 Item 6, reports received Timely.


    5. In the table, the non-blank columns provide the specifications to determine which transactions or employers should be included in the subpopulation.

      Example: To be included in subpopulation 1.1 an employer must: (a) be Active at the end of the quarter, (b) be a Contributory employer, (c, d) have an initial liability or reactivation (reopen) date prior to the end of the report quarter (RQ), (e) not have a termination date unless it either preceded the most recent reopen date or did not occur until after the end of the report quarter, and (f, g) not have filed reports for eight or more consecutive liable quarters or reported zero wages for the last eight of them.

    Exhibit 1.5
    Reconstruction File Specifications, Active Employers

    Figure entitled Exhibit 1.5 Reconstruction File Specifications, Active Employers

    5. Report Quarter Terminology

    The specifications in this handbook use a shorthand terminology to refer to report quarters. Exhibit 1.6 on page I-14 is a time line illustrating how terms and symbols are used.

    6. Issues to Consider When Producing RV Files

    Timing. The ETA 581 report is a snapshot of performance at one point in time. Like the well-advertised “Kodak moment,” if you miss the moment, the picture may be gone. Depending on the state’s system, employer records may be continually changing as employers are terminated or added to the rolls, payments and adjustments modify account balances, long-delinquent reports finally show up, and so forth. When this happens, state data systems may overwrite earlier records, making them inaccessible.

    EXHIBIT 1.6 QUARTERLY TIMELINE

    Figure entitled EXHIBIT 1.6 QUARTERLY TIMELINE

    States should produce the RV reconstruction files (i.e., extract files) at the same time they produce the ETA 581 report. This will eliminate the possibility that validation counts will differ from the report simply because transactions were added to or removed from the employer account in the interval between running the ETA 581 report and the data validation reconstruction. If there is a slight difference in the timing of the two runs, the data “as of” the time when the ETA 581 report was run can sometimes be reconstructed if the state has a complete audit trail. Theoretically the validator could use the audit trail to verify that a transaction was correct at the time of reporting. However, this would be cumbersome. It is better to download data simultaneously for reconstruction files and the ETA 581 report.

    Capture files. There is another, more troublesome problem related to the production of the RV files — certain transactions will be overwritten or changed in some state databases. Thus, if the original record is not captured and saved before it is overwritten, it will disappear and will not be available to validators. In data validation, overwritten transactions are a problem primarily in two populations:

    It may be necessary to create special capture files to ensure that the reconstruction files have access to all transactions. These new files will capture and store all transactions for each employer account. By capturing this information, states can maintain records of status determinations or other overwritten transaction types even if the transactions are later canceled, adjusted, or superceded on the main tax system.

    If states need to develop special files to capture an audit trail of all transactions, the capture program must run for one full reporting period before validation can be done.

    Listing the reconstruction files. The UI Tax Data Validation software will produce complete reports for each reconstruction file. The state validator and the Federal validation auditor may view these reports on screen.

    B. OVERVIEW: Module 1

    At this point in Module 1, you have completed the instructions for producing report validation (RV) files. Before continuing with the discussion of Module 1, it may be helpful to review the steps the validation team will need to complete as the RV files are constructed. That review is presented as Exhibit 1.7.

    EXHIBIT 1.7
    TASK 2: CREATING RECONSTRUCTION (RV) FILES

    Activity Roles
    Review specifications, product requirements, and schedules. ADP, Validators
    Convert handbook specifications into programming specifications. ADP
    Develop “capture” programs if needed. ADP
    Develop and run file extract programs. ADP
    Import extracted files into the UI Tax Data Validation software. Review errors both by referring to error reports and by inspection of listings. Validators
    Modify programs to correct any problems identified. ADP
    Schedule data validation extract programs to run at the same time ETA 581 programs are executed. Also arrange to print any screens needed for Module 2 at the same time. Managers, ADP, Validators

    C. Reporting Data Validation Findings

    1. Report Validation (RV) Reports

    The UI Tax Data Validation software produces five reports in spreadsheet format comparing ETA 581 reported counts and validation findings. The spreadsheets compare subpopulation counts with Federal report item counts and the sort counts for each of the five transaction populations.4

    The spreadsheet automatically calculates validation counts from the report validation files. The values from the Federal reports must be entered manually by state staff. They do so using the Enter Reported Counts screen available through the UI Tax Data Validation software (go to File – Report Validation). An example of that screen is shown in Exhibit 1.8.

    Figure entitled Exhibit 1.8 Sample: Enter Reported Counts Screen for Population 3

    2. Process for Completing the RV Spreadsheets

    a. Using the Enter Report Counts screen, state staff enter reported counts and dollar amounts from the ETA 581 Contribution Operations report.

    b. The UI Tax Data Validation software fills in the counts or dollar totals from each subpopulation on the RV file in the Validation Count/$ column of the RV spreadsheet.5

    c. When there is not a one-to-one relationship between the validation counts and the ETA 581 counts, the UI Tax Data Validation software automatically adds or subtracts validation counts for different subpopulations as necessary to make the proper match. For example, counts for subpopulations 2.1 through 2.8 must be added together to match the reported count for resolved reports for contributory employers during a report quarter (ETA 581 item 8).

    e. The Count Difference and the Count % Difference between the count from the ETA 581 report and the comparable validation count(s) are automatically calculated at the subpopulation and the population levels. If the Count % Difference is greater than plus or minus 2 percent, the Count Pass/Fail column will indicate “Fail.”6

    D. EXAMPLE — VALIDATION REPORTS

    Exhibit 1.9 is a sample of a portion of an RV file for population 1 (in the UI Tax Data Validation software, go to Report Validation – View Source Table) . Population 1 has two subpopulations and the RV file shows the transactions (employers) that the state programmer included in subpopulations 1.1 and 1.2. Subpopulation 1.1 represents active contributory employers and subpopulation 1.2 represents active reimbursing employers. The software assigned employers to their appropriate subpopulations. There are 38,222 valid transactions listed in subpopulation 1.1 and 793 valid transactions in 1.2.

    Exhibit 1.10 is a sample RV spreadsheet generated by the UI Tax Data Validation software for population 1 (go to Report Validation –Report Validation Summary) Here the software has calculated the validation counts of 38,222 for subpopulation 1.1 and 793 for subpopulation 1.2. State staff have entered the reported counts of 38,222 for ETA 581 item 1 and 793 for ETA 581 item 2. When the validation counts are compared to the reported counts for items 1 and 2 on the ETA 581, the UI Tax Data Validation software calculates that there is a count difference of zero percent, and displays, a “Pass” next to each ETA 581 report item.

    Figure entitled Exhibit 1.9 Sample RV File for Population 1 Figure entitled Exhibit 1.10 Sample RV File for Population 1

    E. FINAL RESULTS

    The RV spreadsheet generated by the UI Tax Data Validation software documents, by type of error, discrepancies between the RV files and the Federal ETA 581 report. This allows the validator to identify trends and systematic errors. The validator should further research any “Fail” indicators to determine the source of errors and should document findings in the Comments column on the RV spreadsheet.

    See Module 2.4 for a discussion of required corrective action when validation identifies errors.

    1 The User Guide for the software is a separate document that can be downloaded from the Federal web site: www.ows.doleta.gov/dv.

    2 The entire specification can be found in Appendix A, page A10.

    3 The information is probably retained in an employer history file but is more difficult to locate and reconstruct.

    4 States should treat the final results as provisional until they have completed Module 2, verifying the accuracy of the reconstruction. The provisional entry will identify any large differences between validation counts and reported counts, thereby alerting validators to potential errors in the program that the state has created to produce extract files. Once Module 2 is completed and the validation data are fully checked, the validator will return to the spreadsheet to enter final values.

    5 The dollar amounts entered on the spreadsheet are the sum of all amounts listed in a column for all subpopulations in the reconstruction file.

    6 For time lapse measures, the tolerance will be 1%.

    Module 2 DATA ELEMENT VALIDATION

    Before validators use reconstruction files to assess reported counts, they must assure themselves that those files are as accurate as possible. Module 2 focuses on the elimination of errors that occur as the State builds its reconstruction (extract) files. It discusses the processes used to test that individual transactions have been correctly selected for inclusion in the validation counts.

    Module 2.1, Error Detection, reviews key data tests used by the UI Tax Data Validation software as it screens transactions for inclusion in the validation counts.

    Module 2.2, File Integrity Validation (FIV), checks that the correct information was brought over from the database to build the reconstruction file.

    Module 2.3, Range Validation, presents additional validity tests that examine whether characteristics associated with a transaction are in the correct range for the particular population in which the transaction has been placed.

    Once these checks are complete and any problems resolved, the state validation team can be confident that the validation counts are correct. If, in the end, there are differences between the validation counts and the report counts, states will conclude that the reported counts are not valid.

    Module 2.4, Corrective Action Plan (CAP), reviews the actions states must take when reported data prove to be invalid.

    MODULE 2.1 — ERROR DETECTION

    A. Overview of Error Checks

    Module 2.1 tests that all transactions contain complete and usable information, and that all transactions meet the specifications for inclusion in the population extract file to which they have been assigned. All tests described in this section are done by the UI Tax Data Validation software, which automatically eliminates records of any transactions that do not meet established standards. This section describes the basis of each test to assist the state in anticipating and resolving any problems.

    Data validation software tests the data in four ways and eliminates data that fail any one of the tests. It tests that:

    If a transaction fails to pass any of these tests, it will be removed from the analysis file and not included in the count of transactions. A report will be provided identifying the transactions that failed each test and the reason for failure (See example in Exhibit 2.1). If the UI Tax Data Validation software generates large or systematic exclusions of transactions from the original extract file, the state should review and revise the procedures used to produce the extract file. Any problems must be corrected and another version of the file produced. The new version of the extract file should again be loaded into the UI Tax Data Validation software for testing.1

    Similarly, after the extract file is successfully loaded into the UI Tax Data Validation software, a large or systematic difference between the validation count and the count reported on the Federal report may indicate that the extract file needs refinement. In this instance as well, states should review and revise the procedures for creating the extract file, then begin the extract process again.

    States should work to correct systematic problems identified by data validation even when the amounts in question appear small. It is expected that the procedures developed now will be used again for data validation in future years. It is easier to correct the problems now while design decisions are fresh in mind.

    Figure entitled EXHIBIT 2.1 SAMPLE ERROR REPORT

    B. DETAILED INFORMATION ON SCREENING TESTS

    This section provides details on the parameters used in the screening tests that the UI Tax Data Validation software uses when it determines which transactions belong in each data validation population. The section reviews two sets of parameters: (1) data elements used to identify duplicates, and (2) parameters used to determine whether transactions are included in the file.

    1. Duplicate Detection

    A basic tenet of the data validation design is that no transaction or entity should be counted more than once. The UI Tax Data Validation software provided by the U.S. Department of Labor identifies any duplicate transactions as it creates the report validation file. When state ADP staff first build the extract files for data validation, they are instructed to exclude duplicate records. Thus it is not expected that many duplicates will need to be excluded by the analysis software. The test for duplicates is included mostly for completeness.

    As the extract file is loaded into the UI Tax Data Validation software, a check for duplicates is conducted.2 After this is completed, the software produces a report that counts the number of potential duplicate cases found and lists each duplicate record. The report lists all data elements present in the extract for each duplicate record. States may want to examine this report closely for two reasons. First, it will help identify the source of duplicates so they can be eliminated systematically in the future. Second, the review may show records that are identical on the test data elements but different elsewhere. This will allow the state to identify apparent duplicates that are really legitimate separate records.

    Exhibit 2.2 lists the data elements that are examined to determine whether transactions are duplicates. For each set of elements, the exhibit also lists the criteria for review of duplicates.

    EXHIBIT 2.2
    CRITERIA FOR IDENTIFYING DUPLICATE TRANSACTIONS

    Population Data Element Criterion
    1. Active Employers Employer Account Number (EAN) No employer should be counted twice. Since each employer should have a unique EAN, no EAN should appear twice. (Multi-unit employers are counted as one employer.)
    2. Report Filing EAN,
    Employer Report Quarter (ERQ)
    Each employer owes only one report for each quarter. The EAN should not appear twice for a single ERQ. (If an EAN has reports for multiple employer report quarters (ERQs), only the report for the ERQ immediately preceding the report quarter (RQ) is countable. Reports from multi-unit employers are counted as one report.)
    3. Status Determinations EAN,
    Status Determination Type Indicator,
    Status Determination Date,
    Status Determination Processing Date
    (if different from determination date),
    Predecessor Account Number
    No single status determination transaction should appear twice. Individual EANs may appear more than once. For example, there might be two transactions listed for a single EAN if an employer acquires two businesses at different times during the quarter, resulting in two successorship determinations. Multiple determinations may be legitimate, as long as they do not reflect clerical errors.
    4. Accounts Receivable Established Date, Employer Report Quarter (cont.), Balance at end
    of Quarter, Due Date (reimb).
    Transaction Date, Transaction Type,
    Transaction Amount
    (established, liquidated, uncollectible)
    No transaction should be listed more than once. ADP staff should ensure that the extract file does not include duplicate transaction. Currently, the UI Tax Data Validation software does not check for duplicates in Population 4.

    Two transactions of the same amount, type, date and original quarter due will be assumed to be duplicates. No employer’s account balance should be listed more than once. Two transactions established in the same quarter, for the same quarter, and with the same balance will be assumed to be duplicates.
    5. Field Audits EAN,
    Audit ID#
    The same employer ID and Audit Identification Number should not appear twice.

    2. Parameters for Inclusion in Report Validation File

    As it loads information from the extract file that the state has produced, the UI Tax Data Validation software checks that transactions are within allowable ranges. Most often this involves tests that reported transactions took place within the report quarter. The software will produce a list of all transactions that do not fall within the allowable range. States should review the report and, as necessary, correct their procedures for creating their extract files.

    Exhibit 2.3 lists the parameters that determine whether transactions are included in each population. The UI Tax Data Validation software checks that all transactions meet these parameters and then assigns each transaction to its subpopulation. The specifications for assignment to subpopulations appear in Appendix A, Part II.

    EXHIBIT 2.3
    PARAMETERS FOR INCLUSION
    TESTED BY UI TAX DATA VALIDATION SOFTWARE

    Population 1: Active Employers
    • All employers must be active as of the end of the quarter.


    • Liability dates must precede the end of the quarter.


    • If the employer is inactive at any time during the quarter there must be a subsequent reopening of liability within the report quarter.


    • An employer should be included as active only if it has submitted at least one report indicating wages paid in one of the last eight consecutive quarters of liability .
    Population 2: Report Filing
    • Reports must be received on time, secured by the end of the report quarter or resolved by the end of the quarter following the report quarter.


    • For subpopulations 2.3 through 2.6, 2.8, 2.12 through 2.14, and 2.16 there must be a processing date during the quarter (Assessment date, date that liability status was corrected).


    • In subpopulations 2.7 and 2.13, the suspended as of quarter must equal the ERQ.
    Population 3: Status Determinations
    • All transactions must have a status determination made during the report quarter.


    • All associated processing dates must be within the report quarter.


    • “Successor” determinations must have an associated predecessor account number. (May not be applicable in all states.)
    Population 4: Accounts Receivable
    • Any transaction date that is present must be within the report quarter.


    • The record must indicate either an accounting transaction that establishes, liquidates or declares uncollectible a past due amount, or a balance at the end of the quarter.
    Population 5: Field Audits
    • All listed audits must have a completion date within the report quarter.

    MODULE 2.2 — FILE INTEGRITY VALIDATION (FIV)

    Module 2.1 reviewed data checks that were automatically performed on the data validation extract files produced by the state. Module 2.2 presents several additional tests. Completion of these tests requires action by state validators. The primary test is File Integrity Validation (FIV). State staff examine data items in the validation files to assure that they accurately represent information in the state data system.

    A. Steps in File Integrity Validation

    Module 2.2 checks whether each transaction in the extract file, and each data element listed with the transaction, is an accurate representation of the information in the state UI contributions database. Remember, this is a test of whether the extract file has been created accurately. State validators compare each data element in the transaction record to the source information in the state database. This ensures that the correct data have been used to assemble the record. The review is done in a series of simple steps.

    1. Obtain a copy of the FIV Samples Worksheet listing the data items for two transactions in each subpopulation.

    The sampling function of the UI Tax Data Validation software should be used to select two transactions from each subpopulation (Go to FIV/DEV – FIV Samples worksheet). For each of these transactions, the software will provide an FIV Samples Worksheet listing all data items for that transaction (see example in Exhibit 2.4). For each data element in these two transactions, the validator will compare the entry to source documents. Based on that comparison, the validator will record whether the entry matches what is in the state database.

    The FIV validation check can be done using a very small number of transactions because the process that states use to build the extract files is highly automated. Automated processes are repetitive. If, for example, a certain field in the employer history file is extracted and placed in the fifth column of the reconstruction file for one transaction, that same field will be used for the fifth column of every transaction. Thus, if we know that all data elements have been transferred correctly for the two transactions, we can be reasonably assured that all similar transactions are done correctly.

    Figure entitled EXHIBIT 2.4 SAMPLE FIV SAMPLES WORKSHEET

    2. Validate the selected transactions on the worksheet by checking each item (column) against the corresponding field on the database screens printed from the employer master file.

    The validator compares each characteristic listed for the two selected transactions to supporting documentation in the state’s database. By checking the data in every column the validator ensures that the full complement of data in the reconstruction file is accurate. The source data can be found by referring to query screens from the state data system. These screens display information on transactions and the status of employer accounts. It is strongly recommended that the necessary screens be printed at the time the reconstruction file is originally created.3

    3. Follow the “step” number in the column heading of the reconstruction printout to find the appropriate page in the state-specific segment of this handbook (Module 3).

    Exhibit 2.5 is a sample page of Module 3. For each step listed in Module 3, File Integrity Validation Instructions are provided. These instructions help the validator locate and compare specific pieces of information from the supporting documentation with the corresponding data on the reconstruction file, and to determine the validity of the information (pass or fail).

    The instructions for each step or substep identify the supporting documentation (screens and fields) that the validator will need to examine. A set of logic tests, called validation rules, determines the accuracy of each characteristic of a given transaction. A subsection, called function, explains the purpose of each rule.

    Definitions listed within each step in Module 3 give the Federal definition of the item being validated. This definition is followed by further information on the data element – examples, includes (situations falling within the definition), and excludes. Where state and Federal definitions differ, be sure to follow the Federal rules as required by the reporting instructions.

    Definitional Issues describes known discrepancies between state and Federal definitions. This section serves an important role in systematically documenting validation issues in advance, letting validators and auditors know when problems are anticipated. State staff were interviewed during the design of data validation. Known issues were listed at that time and additional issues will be added by states as they are identified during the validation process.

    Comments provide additional information that state staff or Federal auditors may need in order to handle unusual situations.

    EXHIBIT 2.5 SAMPLE PAGE FROM MODULE 3

    4. Using the Pass/Fail column of the FIV worksheet record whether each data element passes (matches the source file) or does not pass.

    Put a zero (0) on the review sheet next to each data element that successfully passes a step. Place a one (1) if a data element does not pass the step.

    Based upon the pass/fail entries, the worksheet will provide an item-by item count of the number of data elements that failed.

    5. If the FIV process shows errors, reprogram the report validation file.

    The reconstruction file is the basis of all validation exercises and must be proved valid before proceeding any further.

    6. Save the printout with 0 or 1 (pass/fail) entered next to each validated item.

    They will be used during the Federal monitoring review of the validation process.

    B. OVERVIEW OF MODULE 2.2

    Module 2.2 provides tools to test that the data used to create RV files accurately reflect the information in the state’s UI database. Exhibit 2.6 summarizes the steps in the File Integrity Validation Process.

    EXHIBIT 2.6
    TASK 3: FILE INTEGRITY VALIDATION

    Activity Roles
    Secure File Integrity Validation Samples Worksheet listing all data elements for two records from the RV file for each subpopulation. Validator, ADP
    Produce necessary query screens at the same time reconstruction file is created. Validator or ADP
    Following the steps indicated in Module 3, review and validate every item (column) on the printout for the two selected records. The review compares information listed in the reconstruction file to source documentation, typically query screens on the UI database. Validator
    Record the results on the File Integrity Validation screen of the UI Tax Data Validation software. Validator
    If invalid data were used in the creation of the reconstruction file, correct the file and begin this task over again. This is obviously a step that should be done well before the scheduled date for the actual validation. ADP
    If the first Report Validation file is incorrect, conduct File Integrity Validation for corrected versions of the RV file. Validator

    MODULE 2.3 — RANGE VALIDATION

    Module 2.3 presents additional validity tests. They examine whether characteristics associated with a transaction are in the correct range for the particular population and subpopulation in which the transaction has been placed.

    The UI Tax Data Validation software assigns transactions to subpopulations. It does so using the generic codes used by all states, e.g. ‘C’ to mean contributory. As discussed in Module 2.1 the software also conducts automated edit checks. The tests ensure that, based on these generic codes, all transactions are listed in the correct population.

    In some states there are additional data that can be used to determine whether data elements are in the correct range.

    Exhibit 2.7
    Subjectivity Reason Codes

    This exhibit is an actual listing, now out of date, of codes a state used to indicate the reason employers were subject to the provisions of unemployment insurance law. In data validation these would be the codes to which the state would refer to identify that a status determination was as a ‘new’ employer or as a ‘successor.’ This state had an equally long list of codes indicating inactivations and terminations.

    Code Reason
    01 Payroll
    02 Employment 13th week
    03 FUTA
    04 Whole Successor allowed
    05 Part Successor
    06 Consolidation allowed
    07 Revived with new number
    08 Payroll domestic
    09 Payroll agriculture
    10 Employment agricultural
    11 Whole successor denied (No notice)
    12 Whole successor denied (Predecessor delinquent)
    13 Consolidation denied (No notice)
    14 Consolidation denied (Predecessor delinquent)
    15 Multi-predecessor consolidation
    16 Refund only

    The additional data can be examined as another test of whether the data validation files have placed the record in the correct subpopulation.

    This additional information can help validators determine whether the validation files are built correctly. However, the DOL-provided software cannot use this information since the meaning of codes is different in each state. States must conduct the range validation tests following the methodology listed below. Range validation is to be done only in states that have relevant codes that provide more information than the generic codes.

    The validation approach involves sorting the records in the extract file so state validators can easily see if data are in the correct range. Once the records are sorted it is easy for the validator to spot records that are out of range. Sorting is easy to do since it can occur at a click of a button once the file is imported into the UI Tax Data Validation software.

    Methodology.

    1. Examine the range validation criteria in Exhibit 2.8. For each potential sort look at the column entitled “When to Do this Range Validation Sort” to determine if the sort is applicable in the state. The sort will only be used when the state data provide more information than the single generic indicator. 2. To begin range validation, log into the UI Tax Data Validation software using the population to be reviewed. Click the tab labeled “Report Validation” then the tab labeled “View Report Validation Table.” A summary report validation table will appear. 3. Using Exhibit 2.8, identify the subpopulations to test (under the “Subpopulations Sorted” column) and the data element by which the file will be sorted (under “Test data element”).

    4. Apply the “test criterion” from Exhibit 2.8 to identify whether or not any transactions are out of range.

    5. If problems are identified, correct the extract file.

    6. Record the results of each sort using the UI Tax Data Validation Software. To do so click the "FIV/DEV" button on the menu bar. Then click the button that indicates "Enter Data Element Validation Counts." Enter the number of records you have sorted and the number of errors found. The results will be displayed in the Summary and Analytic Report.

    7. Save a copy of the first two pages and last page of each sort conducted. These materials will be used in the Federal review of the validation process.

    Module 2.4 — Corrective Action

    Validation is not an end in itself; it is a means toward correct reporting. If validation identifies reporting errors, the state should correct them as soon as possible.

    Corrective Action Plan. To document the steps required for corrective action and the timetable for completion, the state must provide to its ETA Regional Office a brief Corrective Action Plan (CAP) in accordance with the annual State Quality Service Plan (SQSP) containing the following information on every validated report element found to be in error by more than validation limits:

    Timing of CAP. The CAP should be submitted within one month of submitting the Validation Summary Report. CAPs are considered additions to the SQSP. If the state is conducting the validation in segments, e.g., Benefits first, then Tax, and a CAP is required based on a segment’s validation results, the CAP should be prepared within a month of the completion of that segment.

    Revalidation. Every element in error by more than the stated validation limit must be revalidated the following year. A “clean” validation confirms the success of the corrective action, or, if the state has not completed corrective action, identifies the current extent of error.

    Errors Discovered Outside the Validation Process. During the validation process, errors in reporting may be identified that are outside the scope of the validation program. Such errors should be included in the state’s Validation Summary Report in the comments section and included in the CAP if warranted.

    Exhibit 2.8
    Range Validation Criteria

    Population Sort Sub
    populations Sorted
    When to Do Range Validation Test Data
    Element
    Test Criteria Module 3 References
    1 S1.1 1.1 When the value of an employer’s account number indicates whether it is contributory or reimbursing EAN All EANs must be in ranges allocated to contributory employers Step 1A
    1 S1.2 1.2 When the value of an employer’s account number indicates whether it is contributory or reimbursing EAN All EANs must be in ranges allocated to reimbursing employers Step 1A
    1 S1.3 1.1 and 1.2 When different employer status codes are used to represent different ‘active’ employer statuses Employer
    Status
    Indicator
    All status codes must represent active employers Step 3A
    1 S1.4 1.1 When states have more than one code that represents a ‘contributory’ employer type Employer
    Type
    Indicator
    All employer type codes must represent contributory employers Step 2A
    1 S1.5 1.2 When states have more than one code that represents a ‘reimbursing’ employer type Employer
    Type Indicator
    All employer type codes must represent reimbursing employers Step 2B
    2 S2.1 2.1- 2.8 When the value of an employer’s account number indicates whether it is contributory or reimbursing EAN All EANs must be in ranges allocated to contributory employers Step 1A
    2 S2.2 2.9-2.18 When the value of an employer’s account number indicates whether it is contributory or reimbursing EAN All EANs must be in ranges allocated to reimbursing employers Step 1A
    2 S2.3 2.1-2.8 When there are multiple codes, other than EAN, to indicate whether the employer type is contributory Employer type All employer type codes must represent contributory employers. Step 2A
    2 S2.4 2.9-2.18 When there are multiple codes, other than EAN, to indicate whether the employer type is reimbursing Employer Type All employer type codes must represent reimbursing employers. Step 2B
    3 S3.1 3.1, 3.3 When the state uses more than one code to indicate that status determination type is new. Status Determination Type All status determination type codes must represent ‘new’ status determination type Step 11A
    3 S3.2 3.4, 3.6 When the state uses more than one code to indicate that status determination type is successor. Status Determination Type All status determination type codes must represent ‘successor’ status determination type Step 11B
    3 S3.3 3.7 When the state uses more than one code to indicate that status determination type is inactivation. Status Determination Type All status determination type codes must represent ‘inactivation’ status determination type Step 11C
    3 S3.4 3.8 When the state uses more than one code to indicate that status determination type is termination. Status Determination Type All status determination type codes must represent ‘termination’ status determination type Step 11D
    4 S4.1 4.1, 4.9 When the state uses more than one code to indicate that the transaction type is establishment Transaction Type Indicator All transactions must be establishment of accounts receivable Step 21A
    4 S4.2 4.2, 4.10 When the state uses more than one code to indicate that the transaction type is liquidation Transaction Type Indicator All transactions must be liquidations of accounts receivable Step 21B
    4 S4.3 4.3, 4.4, 4.11, 4.12 When the state uses more than one code to indicate that the transaction type is declared uncollectible Transaction Type Indicator All transactions must be accounts receivable declared uncollectible Step 21B

    1 Building the extract file may require more than one iteration. In preparing the file, states must follow the record layout precisely. To produce final and correct extract files, states may need to make several trial runs.

    2 The software checks for duplicates in Populations 1,2,3,and 5. It currently does not check for duplicates in Population 4 (accounts receivable).

    3 Elements requiring data from multiple fields pose a greater risk of reconstruction error. For example, the reactivation date for status determinations may not come directly from one field in a state’s database, but instead from a combination of a transaction code and a transaction date field. There may be a series of applicable transaction codes representing reactivations. In these instances, the state or region may want to examine the elements in greater detail.

    Module 3

    FEDERAL DEFINITIONS AND STATE-SPECIFIC
    VALIDATION INSTRUCTIONS

    The inclusion of state-specific information in this module is not to be deemed to be a finding that such information is in compliance with Federal reporting data definitions.

    Module 4

    TAX PERFORMANCE SYSTEM
    ACCEPTANCE SAMPLE VALIDATION

    Tax Performance System (TPS) validation reviews sample selection procedures used by TPS (formerly RQC, Revenue Quality Control). It ensures that the samples drawn to assess status determination and field audit quality are randomly selected from the correct populations.

    There are two basic approaches to selecting samples. The first is a conventional interval sample: the programmer (or a utility program) divides the size of the desired sample (say 30) into the size of the population (say 300) and derives the sample interval (every 10th observation). The programmer or the utility program then selects a random start point (in this instance) between 1 and 10 and selects every tenth case from that point. The second approach is to use a sampling utility program that randomizes the file and selects the first 30 observations. This approach is somewhat more difficult to validate, but could involve a review of the sample against the source file or review of the utility program specifications.

    Tasks to Complete Acceptance Sample Validation

    1. Obtain copies of the universe files for Status Determinations and Field Audits. The universe listings should cover all quarters for which the acceptance sample was drawn. For status determinations there will be three TPS universes: (1) New, (2) Successor, and (3) Inactive/Terminated.


    2. Compare the total count of the three status determination universes and one field audit universe for the quarter to the count reported on the ETA 581 for that three-month period. This validates that the correct universe was used.


    3. Determine if an interval sample was drawn (and how it was drawn) or if the file was randomized such that the first set of cases could be selected without establishing intervals.


    4. If an interval sample was drawn, check to see that the proper cases were selected (that is, if the random start was 10 and the interval was every 40th case, check to see that cases 50, 90, 130, and so forth were selected). The validator can identify the sampled cases from the quality review documentation.


    5. If the sample was drawn from a randomized file, print the file and ensure that it was not ordered by date, employer, or some other nonrandom means. The validator can compare the printout with the way the file was ordered prior to randomization to ensure that the file was randomly reordered.

    Recording the Results of Acceptance Sample Validation

    Upon completing the review the validators should record their results. Forms for entering findings are provided as part of the UI Tax Data Validation software on the Enter TPS comments screen. There is one form for each of the four universes. An example can be found in Exhibit 4.1.

    On the form the validator will find room to enter:

    If the sampling method was not correct or was not implemented properly, the validator should discuss the problems with the programmer. If the programmer confirms that the process was incorrect, the validator should record the problems on the TPS comments form. The Enter TPS Comments screen is found on the FIV/DEV menu.

    Exhibit 4.1 Sample TPS Comments Screen

    MODULE 5

    WAGE ITEM VALIDATION

    Wage item validation verifies that wage item transactions processed in the report quarter are accurately reported on the ETA 581. This helps ensure equitable funding when this item is used to determine state workload. A wage record is the listing of an individual’s earnings in covered employment. Each individual employee’s earnings are listed by social security number (SSN) and are submitted by employers each quarter. Employers may submit wage records as paper records or as computerized files stored on magnetic tapes, diskettes, CD-ROMs, or files transmitted over the Internet.

    Wage item validation tests that the ETA 581 report contains a correct count of wage items processed during the report quarter. Validators test that every wage item is counted and that the count does not include:

    Methodology for Completing Wage Item Validation

    1. Identify the specific modes of data capture used for processing wage items and list them on the Wage Item Validation Worksheet provided in the UI Tax Data Validation software (a sample hard copy is presented in Appendix C).


    2. Choose the procedure you will use to select wage items for review.
    3. States have two choices for selecting wage items for review. They may choose to select five batches of wage items or they may select wage items processed during a representative period of time. The selection procedure for each approach is as follows:

    4. For each of the applicable modes on the Wage Item Validation Worksheet, enter the number of wage items reported in the ETA 581 count for the particular batch being examined. This information must be obtained from the system used to compile the wage item count for the ETA 581.


    5. Recount the number of wage items in each of the batches or time periods, for each mode, using the Federal definition for a countable wage item.


    6. Ensure that there are no duplicate entries — that each wage record is counted only once.


    7. The validator must count only wage items that are complete. This means each processed entry should include the following criteria:

      - Employee Identifier (Name or SSN)
      - Employer Identifier (Name or EAN)
      - Wage dollar amount

      If a wage record is incomplete, count only those records containing a dollar amount and another element that positively identifies the worker either by name or SSN and by employer name and account number.


    8. 7. Corrected wage items are counted only if they were not previously included.


    9. 8. Enter the total number of wage items in the recount on the Wage Item Validation Worksheet. If any duplicates or errors have been identified, the validator indicates these errors in the appropriate columns on the worksheet.


    10. 9. The UI Tax Data Validation software will calculate any differences between the validation and reported counts.


    11. 10. If the wage item validation identifies errors, the validator should discuss the problems with the programmer or individual responsible for wage item processing, and the necessary efforts should be made to determine if the error may affect other batches of wage items as well.

    Example of a Wage Item Validation Worksheet

    Exhibit 5.1 shows an example of a Wage Item Validation Worksheet, listing a number of possible modes of wage item processing in the first column. In this particular state, the validator has chosen only the applicable modes and selected an appropriate time period for each mode. The column labeled “581 Count for Batch” has been filled in with the number of wage items processed in this batch as reported on the ETA 581. Once the validator has recounted the wage items for each of the modes, this number is reported in the column labeled “Recount for Batch.” In this example, the wage items that were electronically transferred and processed using CD-ROMs show no discrepancies between the two counts and are therefore proven to be valid. The recount of the magnetic tape processing, however, indicates a duplicate wage item, and therefore the counts do not match. This requires further research to establish the reason for the miscount and to correct any other errors caused by the use of this mode of processing.

    Exhibit 5.1 Wage Item Validation Worksheet

    APPENDIX A



    Part I
    Inclusion Criteria for
    Extract Files by Population



    Part II
    Subpopulation Specifications
    Tables for Each Population

    APPENDIX A

    REPORT VALIDATION SPECIFICATIONS

    INTRODUCTION

    As described in Module 1 of the handbook, the first step in the data validation process is to create report validation (RV) files (also referred to as extract or reconstruction files). These files list all transactions of a single type that are to be reported on the ETA 581 report. Each transaction is listed in a single population and in only one subpopulation within the population.

    Part I of this appendix defines the inclusion criteria for each population. It specifies the type of employer or transaction that should be included in the population. Tables 1 through 5 in Part II of Appendix A specify how the populations are to be divided into subpopulations. Each row of the table is the specification for a single, mutually exclusive subpopulation. At the end of each table is a written description of each subpopulation. This should help readers orient themselves to the information in the table.

    Each column header includes a step number that refers to the state-specific portion of the handbook in Module 3. Validators and programmers should refer to the indicated step number for detailed instructions on how to validate the data in that column, as well as for the definition of the data element. Each specification includes a column and/or row entitled “ETA 581 Item #’s,” which indicates the Item number on the ETA 581 that the count or dollar amount in the column or row is compared with on the RV spreadsheet.

    States should reconstruct each population as specified for a recent ETA 581 report quarter (RQ). In addition, states that administer unemployment insurance together with other taxes should capture tax type, to distinguish between the taxes being validated on the ETA 581 and others which are not countable on the report.1

    1 Some states may have other unique types of data elements which should be captured in the reconstruction file to facilitate validation. For example, some states may have an indicator for seasonal employers which would be helpful in validating subpopulations 2.7 and 2.15 on Table 2.

    Abbreviations:
    RQ     ETA 581 report quarter
    ERQ     Employer Report Quarter (quarter covered by employer’s contribution report)
    FDRQ     First day of the report quarter
    LDRQ   Last day of the report quarter
    FD(RQ+1)     First day of the quarter after the report quarter
    FD(RQ-1)     First day of the quarter before the report quarter
    DD     Report due date
    A &  nbsp; Active
    N     Newly Liable
    S     Successor
    I     Inactivation/inactive
    T     Termination/terminated
    C     Contributory Employer
    R     Reimbursing Employer
    OBS     Observation number
    >     After the date or quarter specified, e.g., >RQ means “after the report quarter.”
    <     Before the date or quarter specified, e.g., < RQ means “prior to the report quarter.”

    Calculating quarters with a time line: in the example below, if the report quarter being validated is the fourth quarter of 2001, then RQ-8 is the fourth quarter of 1999.

    Timeline

    Part I

    Inclusion Criteria

    Extract Files by Population

    Population 1: Active Employers
    Inclusion Criteria

    Population 1 should include all employers who were active as of the last day of the report quarter.

    To be included:

    Population 2: Report Filing
    Inclusion Criteria

    States should create a reconstruction file of all employers owing contributions or required reports for employer report quarter (ERQ), due in RQ, which were received timely or secured in the RQ, or reported as resolved in RQ+1. The entire population extract can be run at the end of RQ+1.

    The reconstruction file includes:

    Population 3: Status Determination
    Inclusion Criteria

    Population 3 includes all status determinations made during the quarter. More than one status determination may be made and reported during the report quarter for a single employer. To be included:

    Population 4: Accounts Receivable
    Inclusion Criteria

    Population 4 includes all accounting transactions made during the report quarter that establish or modify a receivable (past due taxes owed) for an employer account. There may be more than one such transaction for a single employer during the report quarter. To be included:

    Population 4 also includes all receivable amounts in accounts that have a balance due at the end of the quarter (Step 26). Amounts in this population include those that were ‘removed from the report’ during the report quarter. Included are accounts that:

    Population 5: Field Audits
    Inclusion Criteria

    Population 5 includes all field audits completed during the reported quarter. To be included an audit must have an audit completion date during the report quarter (Step 30).

    Part II

    Subpopulation Specifications

    Tables for Each Population

    Table 1: Reconstruction File Specifications, Active Employers

    Report Quarter:
    Date:

    These subpopulations constitute the unique subgroups of all active employers on the last day of the Report Quarter (RQ) covered by the ETA 581.
    Reconstruction should be done at the end of the RQ being validated (when the ETA 581 report program is run).

    1 (Step 1A) 2 (Step 3A) 3 (Step 2A)
    (Step 2B)
    4 (Step 4A) 5 (Step 4B) 6 (Step 5) 7 (Step 15) 8 (Step 7B) 9 (Step 7A)
    Subpopulation Reported in
    581 Item #’s
    Employer Account # (EAN) Employer
    Status
    Indicator
    A/I/T
    Employer
    Type
    C/R
    Liability Date
    (Initial)
    Liability Date
    (Reopen)
    Inactive/
    Terminated “as of” Date
    Activation
    Processing
    Date
    Number of
    Liable
    Quarters
    Sum of Wages
    (Last 8 Q’s)
    1.1 1 A C <=RQ <=RQ >RQ, or < liability date (reopen),or none (If col. 8=8) >$0
    1.2 2 A R <=RQ <=RQ >RQ, or < liability date (reopen),or none (If col. 8=8) >$0

    Notes
    1) Analysis will treat columns 4 and 5 as a single date, using the most recent. One of the two must be a date earlier than the end of the report quarter.
    2) Column 8 reports the consecutive number of liable quarters ending with the report quarter. If the number is > 8, simply list 8.
    3) In column 9 add together the reported wages for the last 8 quarters. The record layout for the software specifies the states list all 8 quarters. The software does the addition automatically.

    Subpopulation descriptions: 1.1 Active contributory employers liable by the end of the report quarter.
    1.2 Active reimbursable employers liable by the end of the report quarter.

    Table 2: Reconstruction File Specifications, Report Filing

    Report Quarter (ETA 581):
    Date:
    Employer Report Due Date: The Due Date (DD) is defined as the date after which the state can impose penalty and/or interest, whichever is first applicable. It is a state-specific date.

    These subpopulations constitute the unique subgroups of all employers owing contributions or required reports during the same ETA 581 report quarter (RQ), which were received timely or secured in the RQ, or reported as resolved in RQ+1. Programmers and validators should note that timely, secured, and resolved are here defined as discrete filing statuses, whereas the ETA 581 reports cumulative counts for these categories. Because of the static nature of the received date, which is a key data element for subpopulations 2.1, 2.2, 2.3, 2.9, 2.10, and 2.11, the entire population extract can be run at the end of RQ+1. The validation counts in subpopulations 2.1, 2.2, 2.9, and 2.10 are compared with ETA 581 counts for the RQ; all subpopulation validation counts are compared with reported counts for RQ+1 (see 581 item # references below.)

    1
    (Step 1B)
    2
    (Step 1B)
    3
    (Step 2A)
    (Step 2B)
    4
    (Step 9)
    5
    (Step 10)
    6
    (Step 4A)
    (Step 4B)
    7
    (Step 14)
    8
    (Step 5)
    9
    (Step 5)
    10
    (Step 6A)
    (Step 6B)
    (Step 6C)
    Sub-
    population
    Reported in
    581 Item #’s
    Employer
    Account #
    (EAN)
    Employer Report Q (ERQ) Employer
    Type
    C/R
    Received Date Final Assessment
    Date
    Liability
    Date
    (Initial or
    Reopen)
    Liability
    Date
    (Met Threshold)
    Inactive/
    Terminated
    “as of” Date
    Suspended
    “as of” Qtr.
    Inactivation/
    Termination
    Processing
    Date
    2.1 6,7,(8 in RQ+1 RQ-1 C <=DD none
    2.2 7,(8 in RQ+1) RQ-1 C > DD but within RQ none
    2.3 8 in RQ+1 RQ-1 C within RQ+1 none
    2.4 8 in RQ+1 RQ-1 C none w/in RQ or RQ+1
    2.5 8 in RQ+1 RQ-1 C none < RQ-1 within RQ or RQ+1
    2.6 8 in RQ+1 RQ-1 C none >=RQ >RQ and >liability date, or none
    2.7 8 in RQ+1 RQ-1 C none (RQ-1)
    2..8 8 in RQ+1 RQ-1 C none =col 8 date =col 6 date Within RQ or RQ+1
    2.9 9,10,(11 in RQ+1) RQ-1 R <=DD none
    2.10 10,(11 in RQ+1) RQ-1 R > DD but <=LDRQ none
    2.11 11in RQ+1 RQ-1 R within RQ+1 none
    2.12 11in RQ+1 RQ-1 R none <=LDRQ+1
    2.13 11in RQ+1 RQ-1 R none within RQ or RQ+1
    2.14 11in RQ+1 RQ-1 R none >=RQ >RQ and >liability date, or none
    2.15 11in RQ+1 RQ-1 R none (RQ-1)
    2.16 11in RQ+1 RQ-1 R none =col 8 date =col 6 date Within RQ or RQ+1

    Notes:
    1) A few states resolve reports for seasonal employers by suspending the report filing requirement in off seasons (subpopulations 2.7 and 2.15). Other states will have no entry in this column.
    2) States may identify all contributory and reimbursing employers who were subject to file a required report covering the quarter prior to the ETA 581 report quarter, on the last day of the quarter prior to the ETA 581 report quarter. That data file can then be used in the validation reconstruction, even though not every report owed will be resolved. (If this approach is workable for states, it can also be done every quarter to program the ETA 581.)
    3) Some states may use a delinquency flag instead of the preferred received date; this creates audit trail issues to be reviewed on a statespecific basis.
    4) If an employer has more than one resolved date under columns 4, 5, 8, or 9, the software assigns the record to the first subpopulation for which it meets the criteria.

    Subpopulation descriptions: The software assigns records to the first subpopulation for which it meets the subpopulation criteria. Each record is compared to the requirements for subpopulation 1 and the software determines if the record meets the subpopulation 1 criteria. If it does, the record is assigned to subpopulation 1. If it does not, the software then compares the record to the requirements for subpopulation 2 and determines if the record meets the subpopulation 2 criteria. This process continues as necessary comparing each record to the requirements for each successive subpopulation.

    2.1 Contributory employers owing contributions reports for activities in the quarter prior to the report quarter, who filed contribution reports timely during the report quarter.
    2.2 Contributory employers owing contributions reports for activities in the quarter prior to the report quarter, who filed untimely contribution reports by the end of the report quarter (secured, but not timely).
    2.3 Contributory employers owing contributions reports for activities in the quarter prior to the report quarter, who filed contribution reports during the quarter after the report quarter (resolved, neither secured nor timely).
    2.4 Contributory employers owing contributions reports for activities in the quarter prior to the report quarter, who did not submit a report but received a final assessment by the end of the quarter after the report quarter (resolved, neither secured nor timely).
    2.5 Contributory employers owing contributions reports for activities in the quarter prior to the report quarter, who were made inactive during the report quarter, or during the quarter after the report quarter (resolved, neither secured nor timely).
    2.6 Contributory employers owing contributions reports for activities in the quarter prior to the report quarter, whose liability date (met threshold) was changed from prior to the report quarter, to during or after the report quarter (resolved, neither secured nor timely).
    2.7 Contributory employers owing contributions reports for activities in the quarter prior to the report quarter, who were suspended from filing contribution reports due in the report quarter by virtue of being seasonal employers, an administrative decision not to pursue report filing, for domestic employers who elect to file annually, or for other reasons (resolved, neither secured nor timely).
    2.8 Contributory employers owing contributions reports for activities in the quarter prior to the report quarter, whose accounts were withdrawn by making the liability date and the inactive/terminated “as of” date equal (resolved, neither secured nor timely). This includes canceled, withdrawn, closed, dropped, etc. accounts.
    2.9 Reimbursable employers owing required reports for activities in the quarter prior to the report quarter, who filed required reports timely during the report quarter.
    2.10Reimbursable employers owing required reports for activities in the quarter prior to the report quarter, who filed untimely required reports by the end of the report quarter (secured, but not timely).
    2.11Reimbursable employers owing required reports for activities in the quarter prior to the report quarter, who filed required reports during the quarter after the report quarter (resolved, neither secured nor timely).
    2.12Reimbursable employers owing required reports for activities in the quarter prior to the report quarter, who did not submit a report but received a final assessment by the end of the quarter after the report quarter (resolved, neither secured nor timely).
    2.13Reimbursable employers owing required reports for activities in the quarter prior to the report quarter, who were made inactive during the report quarter, or during the quarter after the report quarter (resolved, neither secured nor timely).
    2.14Reimbursable employers owing required reports for activities in the quarter prior to the report quarter, whose liability date (met threshold) was changed from prior to the report quarter, to during or after the report quarter (resolved, neither secured nor timely).
    2.15Reimbursable employers owing required reports for activities in the quarter prior to the report quarter, who were suspended from filing required reports due in the report quarter by virtue of being seasonal employers, an administrative decision not to pursue report filing, for domestic employers who elect to file annually, or for other reasons (resolved, neither secured nor timely).
    2.16Reimbursable employers owing required reports for activities in the quarter prior to the report quarter, whose accounts were withdrawn by making the liability date and the inactive/terminated “as of” date equal (resolved, neither secured nor timely). This includes canceled, withdrawn, closed, dropped, etc. accounts.

    Table 3: Reconstruction File Specifications, Status Determinations Entered within Report quarter (RQ)

    Report Quarter:
    Date:

    These subpopulations constitute the unique subgroups of all status determinations made by the state during the ETA 581 Report Quarter (RQ). States that overwrite status determinations on their master tax file may use the TPS universe for reconstruction. Programmers and validators should note that time lapse categories are discrete subpopulations, whereas the ETA 581 reports time lapse cumulatively.

        1
    (Step 1C)
    2
    (Step 2A) (Step 2B)
    3
    (Step 11A)
    (Step 11B)
    (Step 11C)
    (Step 11D)
    4
    (Step 12)
    5
    (Step 13)
    6
    (Step 14)
    7
    (Step 14)
    8
    (Step 15)
    9
    (Step 16)
    10
    (Step 17)
    11
    (Step 18)
    12
    (Step 6A)
    or
    (Step 6B)
    13
    (Step 6A)
    or
    (Step 6C)
    Sub- population ETA 581 Item #’s EAN Employer
    Type
    C/R
    Status
    Deterinm.
    Type
    Indicator
    Time Lapse
    (Calculated)
    Status
    Determin.
    Date(s)
    Liability
    Date
    (Met
    Threshold)
    End of
    Liable
    Quarter
    (Calculated)
    Activation
    process date
    Reactivation
    process
    date(s)
    Successor-
    ship process
    date(s)
    Prede-
    cessor account
    number
    Inactivation
    process
    date(s)
    Termination
    process
    date(s)
    3.1 14,15,16     New <=90 days within RQ     within RQ, or < column 9 date within RQ, or none     < active/ reactivation date, or blank < active/ reactivation date, or blank
    3.2 14,16     New >=91 but <=180 days within RQ     within RQ, or < column 9 date within RQ, or none     < active/ reactivation date, or blank < active/ reactivation date, or blank
    3.3 14     New >=181 days within RQ     within RQ, or < column 9 date within RQ, or none     < active/ reactivation date, or blank < active/ reactivation date, or blank
    3.4 17,18,19     Successor <=90 days within RQ     <= successor- ship date <= successor- ship date, or none within RQ non-blank    
    3.5 17,19     Successor >=91 but <=180 days within RQ     <= successor - ship date <= successor - ship date none within RQ non-blank    
    3.6 17     Successor >=181 days within RQ     <= successor - ship date <= successor - ship date none within RQ non-blank    
    3.7 20     Inactivations n/a within RQ n/a n/a n/a n/a n/a   within RQa blank
    3.8 20     Termina-
    tions
    n/a within RQ n/a n/a n/a n/a n/a   blank within RQ

    a There is the same issue as under Population #1, where the employer could be inactive based on 8 quarters of no wages (or fewer depending on the state’s threshold), but for some reason the inactivation date/flag was not triggered. We may be able to cross-reference by EAN (by programming or on the printout) the employers identified as falling in this category from the Population #1 specifications, since they are identical, as long as the same RQ is validated.

    Notes:
    1) States that prefer to validate contributory and reimbursing employer status determinations separately may do so by replicating the eight subpopulations (one set of eight subpopulations for each type of employer). States may prefer to validate the two types of employers separately if they are processed in very different ways.
    2) Time Lapse is the difference, in days, between the last day of the liable quarter and the status determination date.

    Subpopulation Descriptions: 3.1 Status determinations of new employers made during the report quarter, which were made within 90 days of the end of the quarter in which the employer became liable. (Employers changing from contributory to reimbursing status and vice versa are included in subpopulations 3.1 - 3.3.)
    3.2 Status determinations of new employers made during the report quarter, which were made between 91 and 180 days of the end of the quarter in which the employer became liable.
    3.3 Status determinations of new employers made during the report quarter, which were made 181 days or later from the end of the quarter in which the employer became liable.
    3.4 Status determinations of successor employers made during the report quarter, which were made within 90 days of the end of the quarter in which the employer became liable.
    3.5 Status determinations of successor employers made during the report quarter, which were made between 91 and 180 days of the end of the quarter in which the employer became liable.
    3.6 Status determinations of successor employers made during the report quarter, which were made 181 days or later from the end of the quarter in which the employer became liable.
    3.7 I Inactivations of employers made during the report quarter.
    3.8 Terminations of employers made during the report quarter.

    Table 4: Reconstruction File Specifications, Accounts Receivable

    Report Quarter:
    Date:

      1
    (Step 1D)
    2
    (Step 2A)
    (Step 2B)
    3
    (Step 19A)
    4
    (Step 19B)
    5
    (Step 1D)
    6
    (Step 20)
    7
    (Step 21A)
    (Step 21B)
    (Step 21C)
    8
    (Step 22)
    9
    (Step 23)
    10
    (Step 24)
    11
    (Step 25)
    12
    (Step 26)
    13
    (Step 27A)
    (Step 27B)
    Sub- popula- tion EAN Employer
    Type
    C/R
    Transac-
    tion Date
    Estab-
    lished
    Q/Date
    Employer
    Report
    Quarter
    (ERQ)
    Due Date
    (DD)
    Transaction
    Type/Indicator
    Amount
    Established in RQ
    Liquidated
    (Pay/Adj)
    Uncollect
    -ible
    Removed Balance at
    end of RQ
    Age
    4.1 C RQ Establishment $ blank blank blank blank blank
    4.2 C RQ blank Liquidation blank $ blank blank blank blank
    4.3 C RQ >RQ-8 Uncollectible blank blank $ blank blank blank
    4.4 C RQ >RQ-3 <=RQ-8 Uncollectible blank blank $ blank blank blank
    4.5 C blank RQ-8 blank blank blank blank blank $ blank
    4.6 C blank RQ-2 <=RQ-8 blank blank blank blank blank $ blank
    4.7 C blank >RQ-8 blank blank blank blank blank blank $
    4.8 C blank >RQ-2 <=RQ-8 blank blank blank blank blank blank $
    ETA Item # 22 23 24 25 26
    4.9 R RQ Establishment $ blank blank blank blank blank
    4.1 R RQ blank Liquidation blank $ blank blank blank blank
    4.11 R RQ >RQ-7 Uncollectible blank blank $ blank blank blank
    4.12 R RQ >RQ-3 # RQ-7 Uncollectible blank blank $ blank blank blank
    4.13 R blank blank RQ-7 blank blank blank blank $ blank
    4.14 R blank RQ-2 blank # RQ-7 blank blank blank blank $ blank
    4.15 R blank blank >RQ-7 blank blank blank blank blank $
    4.16 R blank >RQ-2 blank # RQ-7 blank blank blank blank blank $
    ETA Item # 34 35 36 37 38

    Notes:
    Values in column 8 for all observations in subpopulations 4.1 - 4.8 should be totaled, for comparison to ETA Item #22.
    Values in column 9 for all observations in subpopulations 4.1 - 4.8 should be totaled, for comparison to ETA Item #23.
    Values in column 10 for all observations in subpopulations 4.1 - 4.8 should be totaled, for comparison to ETA Item #24.
    Values in column 11 for all observations in subpopulations 4.1 - 4.8 should be totaled, for comparison to ETA Item #25.
    Values in column 12 for all observations in subpopulations 4.1 - 4.8 should be totaled, for comparison to ETA Item #26.
    Values in column 8 for all observations in subpopulations 4.9 - 4.16 should be totaled, for comparison to ETA Item #34.
    Values in column 9 for all observations in subpopulations 4.9 - 4.16 should be totaled, for comparison to ETA Item #35.
    Values in column 10 for all observations in subpopulations 4.9 - 4.16 should be totaled, for comparison to ETA Item #36.
    Values in column 11 for all observations in subpopulations 4.9 - 4.16 should be totaled, for comparison to ETA Item #37.
    Values in column 12 for all observations in subpopulations 4.9 - 4.16 should be totaled, for comparison to ETA Item #38.

    Subpopulation descriptions:
    4.1 Receivable amounts established as past due in the report quarter for contributory employers.
    4.2 Receivable amounts liquidated during the report quarter for contributory employers.
    4.3 Receivable amounts declared uncollectible during the report quarter for contributory employers where the receivable is less than eight quarters old.
    4.4 Receivable amounts declared uncollectible during the report quarter for contributory employers where the receivable is more than seven quarters old but was established within the report quarter or the two preceding quarters. The establishment date parameter is used to confirm that these transactions have not yet been removed.
    4.5 Receivables removed during the report quarter for contributory employers where the receivable is eight quarters old and was established prior to two quarters before the report quarter.
    4.6 Receivables removed during the report quarter for contributory employers where the receivable was at least eight quarters old and was established two quarters prior to the report quarter.
    4.7 Receivable balances at the end of the report quarter for contributory employers which were less than eight quarters old. (The receivable was not yet old enough to be removed.)
    4.8 Receivable balances at the end of the report quarter for contributory employers which were more than eight quarters old but which were established within the report quarter or the preceding quarter. (The receivable is old enough to be removed but is not removed because it has not yet sat for 2 quarters in the ‘greater than 15 months’ aging category.)
    4.9 Receivable amounts established as past due in the report quarter for reimbursable employers.
    4.10 Receivable amounts liquidated during the report quarter for reimbursable employers.
    4.11 Receivable amounts declared uncollectible during the report quarter for reimbursable employers where the receivable is less than eight quarters old.
    4.12 Receivable amounts declared uncollectible during the report quarter for reimbursable employers where the receivable is more than seven quarters old but was established within the report quarter or the two preceding quarters.
    4.13 Receivables removed during the report quarter for reimbursable employers where the receivable is eight quarters old and was established prior to two quarters before the report quarter.
    4.14 Receivables removed during the report quarter for reimbursable employers where the receivable was at least eight quarters old and was established two quarters prior to the report quarter.
    4.15 Receivable balances at the end of the report quarter for reimbursable employers which were less than eight quarters old. (The receivable was not yet old enough to be removed.)
    4.16 Receivable balances at the end of the report quarter for reimbursable employers which were more than eight quarters old but which were established within the report quarter or the two preceding quarters. (The receivable is old enough to be removed but is not removed because it has not yet sat for 2 quarters in the ‘greater than 15 months’ aging category.)

    Table 5: Reconstruction File Specifications, Field Audits

    Report Quarter:
    Date:

    These subpopulations constitute the unique subgroups of all field audits completed during the ETA 581 Report Quarter (RQ). Data elements specified on the file specification may not be captured on the state’s system when they are not reported on the 581. They are however included in the auditor’s file. When states cannot capture such information automatically, the column can be completed from the auditor’s paper files during the validation for the selected cases.

      Total Wages Taxable Wages Contributions
    1
    (Step 1E)
    2
    (Step 1E)
    3
    (Step 28A)
    (Step 28B)
    4
    (Step 29A)
    (Step 29B)
    5
    (Step 30)
    6
    (Step 31A)
    7
    (Step 31B
    8
    (Step 31C)
    9
    (Step 31D)
    10
    (Step 31E)
    11
    (Step 32A)
    12
    (Step 32B)
    13
    (Step 32C)
    14
    (Step 32D)
    15
    (Step 32E)
    16
    (Step 33A)
    17
    (Step 33B)
    18
    (Step 33C)
    19
    (Step 33D)
    20
    (Step 33E)
    Sub-
    population
    ETA 581
    Items #’s
    EAN Audit
    ID #
    Employer
    size
    L/S
    Change
    audit
    Y/N
    Audit
    completion
    date
    Pre Post Under Over Total
    reconcil-
    iation amount
    Pre Post Under Over Tax
    reconcil-
    iation
    amount
    Pre Post Onder Over Cont.
    reconcil-
    iation amount
    5.1 45, 46, 47 L Y within RQ T1$ T2$ T3$ T4$ 0 X1$ X2$ X3$ X4$ 0 C1$ C2$ C3$ C4$ 0
    5.2 45, 47 L N within RQ
    5.3 46, 47 S Y within RQ
    5.4 47 S N within RQ
    ETA Item# 49 50 53 56 54 57 55 58

    Notes:
    1) Some states may want to capture and store in the data file the number of employees pre-and post-audit. Some states allocate a percentage of their UI receipts to special funds or programs; if so, the employer’s discount rate and amount discounted should be included on the printout.
    2) Post audit figures for total wages, taxable wages and contributions reflect the net increase or decrease of under and over reporting identified during the audit, even though the netted figures are not reportable on the ETA 581. Referring to the specification:

    Subtract the positive net of (T3 - T4) from the positive net of (T1 - T2). The result in column 10 should be zero.
    Subtract the positive net of (X3 - X4) from the positive net of (X1 - X2). The result in column 15 should be zero.
    Subtract the positive net of (C3 - C4) from the positive net of (C1 - C2). The result in column 20 should be zero.


    For example, if Employer A under reported total wages by $5000 and also over reported total wages by $1000, the Employer’s post-audit total wages would increase by $4000. So, if the validator nets the under and over reported wages the result is $4000, and nets pre and post audit wages the result is $4000. These two results should always reconcile to zero. Referring again to the printout specification:

    If TI = $10,000, T2 = $14,000, T3 = $5,000, T4 = $1,000, then ($10,000 - $14,000) - ($5,000 - $1,000) = 0.
    Also, if TI = $10,000, T2 = $6,000, T3 = $1,000, T4 = $5,000, then ($10,000 - $6,000) - ($1,000 - $5,000) = 0.

    3) The number of observations in all four subpopulations should be totaled, for comparison to ETA Item #47.

    Dollar values in column 6 for all observations in all four subpopulations should be totaled, for comparison to ETA Item #49.
    Dollar values in column 7 for all observations in all four subpopulations should be totaled, for comparison to ETA Item #50.
    Dollar values in column 8 for all observations in all four subpopulations should be totaled, for comparison to ETA Item #53.
    Dollar values in column 9 for all observations in all four subpopulations should be totaled, for comparison to ETA Item #56.
    Dollar values in column 13 for all observations in all four subpopulations should be totaled, for comparison to ETA Item #54.
    Dollar values in column 14 for all observations in all four subpopulations should be totaled, for comparison to ETA Item #57.
    Dollar values in column 18 for all observations in all four subpopulations should be totaled, for comparison to ETA Item #55.
    Dollar values in column 19 for all observations in all four subpopulations should be totaled, for comparison to ETA Item #58.

    Subpopulation descriptions:
    5.1 Large employer audits completed during the report quarter, which were change audits.
    5.2 Large employer audits completed during the report quarter, which were not change audits.
    5.3 Small employer audits completed during the report quarter, which were change audits.
    5.4 Small employer audits completed during the report quarter, which were not change audits.

    APPENDIX B
    INDEPENDENT COUNT


    APPENDIX C IS ONLY APPLICABLE TO POPULATIONS FOR WHICH THE STATE HAS PRODUCED THE RV FILE FROM THE SAME
    EXTRACT FILES USED TO PRODUCE THE ETA 581 REPORT.

    A. PURPOSE

    The validation exercises described in Modules 1.1 and 1.2, and those outlined in Module 2, address the validation of all UI contributions transactions that have been included in the ETA 581 report. However, it is also important to confirm that no transactions have been improperly or systematically excluded from the Federal report. Although this problem is a difficult one, it is important to ensure that funding, economic statistics, and performance outcomes are not biased by the systematic elimination of particular types of transactions.

    This module is not applicable for states that produce the RV file directly from the employer contributions database, because the RV process itself constitutes an independent count through the process of reconstruction. When the RV file is produced from the same file used to produce the ETA 581 report, it is necessary to conduct an independent count in order to identify any errors that may have occurred in the ETA 581 report since these errors will be duplicated in the reconstruction file.

    It is also not possible to perform an independent count when the database does not contain all of the reported transactions. In these circumstances, the statistical file is the only source of data to reconstruct reported counts on the ETA 581 report. It is unlikely that any state will need to perform an independent count for 581 validation as explained in Exhibit C.2 (it is more relevant to validating Federal benefits reports). This procedure is included in this handbook to ensure that states are aware of the possible problems with using statistical files for both reporting and validation when database files could be used.

    B. MATERIALS (ADP STAFF)

    1. Independent Count Files

    ADP staff create independent total counts of transactions from the main database for comparison with counts generated on the extract files used to create the ETA 581. In general, the independent count is created opposite to the way the RV file is created. The RV file should be programmed from the bottom up, by selecting only the codes and criteria indicated on the file specification in Appendix A. However, the independent count should be programmed from the top down, by including all codes relevant to a population and then subtracting observations related to those not indicated on the file specification.

    Exhibit C.1 indicates when independent count validation is required. There are six typical scenarios for how states produce the ETA 581 report and reconstruct counts for validation. The ETA 581 Report Source column indicates for each scenario the source files that states use to generate report counts. States may use different source files for different types of transactions. The Validation Reconstruction Source column indicates for each scenario the source files that states use to reconstruct lists of transactions for validation.

    The Independent Count Required column of Exhibit C.1 indicates whether the state should conduct independent count validation for transaction types that match the report and validation scenario.

    Exhibit C.2 describes independent count criteria for each population.

    Exhibit C.3 shows a spreadsheet to record the results of an independent count if one proves necessary.

    EXHIBIT B.1
    ETA 581 REPORTING AND VALIDATION CONFIGURATIONS

    Scenario Transactions Overwritten on Database ETA 581 Data Validation Indepen- dent Count Required
    Program Type Source Timing Program Type Source Timing
    1 No Count Database Snapshot (for reporting period) Detail Record Extract (DRE) Database Snapshot No
    2 No Count Stat file Daily DRE Database Snapshot No
    3 No DRE Database Snapshot (for reporting period) DRE Database Snapshot Yes
    4 No DRE Stat file Daily DRE Stat file Daily Yes
    5 Yes DRE Stat file Daily DRE Stat file Daily NA
    6 Yes Count Stat file Daily must create a daily extract NA NA NA

    EXHIBIT B.2

    INDEPENDENT COUNT CRITERIA, BY POPULATION (USING QUERY CAPABILITY)

    Population Description Independent Count Criteria
    1 Active Employers States should not use statistical files to validate active employers because the count should be taken from the database as a snapshot at the end of the month. If states do not use this approach for reporting (if they instead derive the number from changes in status over the quarter), they must use it for validation (they cannot recreate the active employer population from the status changes). Therefore, there is no situation which would require an independent count.
    2 Report Filing States generally use data files containing a record for each employer quarter for both reporting and reconstructing counts of employer report statuses. Therefore, there is not likely to be a situation where statistical files are used for reporting or validation. If a state uses a statistical file for validation, it should create a frequency distribution of received dates for every employer with a received date for the quarter being validated. This count can be used to validate that the statistical file data matches the data base for all timely and secured reports and for all reports which are resolved by receipt of report. This will validate subpopulations 2.1, 2.2, 2.3, 2.9, 2.10 and 2.11, which will be sufficient to demonstrate that the statistical file is valid.
    3 Status Determinations States often use statistical files for reporting status determinations when their system stores only the most recent status determination for each employer account and thus overwrites or overlays some status determinations. These files are often called “RQC files” because they were developed to provide a universe of determinations from which to derive the RQC sample. These states cannot perform an independent count from the database to validate the statistical file because the database will not contain records for all of the status determinations. Therefore, an independent count is not required for status determinations, because it is not possible to create such a count in states which use statistical files.
    4 Accounts Receivable All states must use a transaction history file or audit trail to correctly reconstruct payments (amounts liquidated), because only such files show the date that each payment was made. Transaction history files are also the source for receivable amounts established and amounts declared uncollectible in some states. There is only one source file for such transactions, so an independent count is not relevant. All states must use “employer quarter files” to reconstruct balances for reporting amounts removed and amounts outstanding at the end of the quarter. Some states use such balances for reporting amounts declared uncollectible. These balances are always captured as a “snapshot” at the end of the quarter from the database, so an independent count is not relevant.
    5 Field Audits States do not maintain more than one file with field audit results, thus an independent count is not relevant.


    Created: March 29, 2004

    Updated: September 24, 2009