RCRAInfo Translator Guide V

United States Office of Environmental Protection Solid Waste Agency Washington, D.C. 20460 June 2005 ___________________________________________________________________ RCRAINFO TRANSLATOR GUIDE VERSION 3 TABLE OF CONTENTS Page 1. INTRODUCTION.........................................................................................................................................1 1.1 Overview of Document .........................................................................................................................1 1.2 Intended Audience................................................................................................................................1 1.3 Data Files..............................................................................................................................................2 1.4 Questions/Comments............................................................................................................................2 DATA SUBMISSION OVERVIEW ............................................................................................................3 2.1 Roles and Responsibilities....................................................................................................................3 2.2 RCRAInfo Load Processing .................................................................................................................4 2.3 Full Replacement Indicator...................................................................................................................4 2.4 Transaction Code..................................................................................................................................6 TECHNICAL SPECIFICATIONS..............................................................................................................7 3.1 Rules and Format Conventions Required for Data Flat Files ...............................................................7 3.1.1 Alphanumeric Fields ..............................................................................................................7 3.1.2 Integer Fields..........................................................................................................................7 3.1.3 Fixed Decimal Fields .............................................................................................................8 3.1.4 Date Fields .............................................................................................................................9 3.1.5 Negative Numbers..................................................................................................................9 3.1.6 Record Termination................................................................................................................9 3.1.7 Empty Fields ..........................................................................................................................9 GENERIC EDITS .......................................................................................................................................10 FLAT FILE TRANSFER (VIA CDX) .......................................................................................................11 VERSION 3 SUBMISSIONS .....................................................................................................................12 2. 3. 4. 5. 6. Appendix A - Flat File Specifications and Data Edits FLAT FILE ID# - CTL .............................................................................................................................. A-1 FLAT FILE ID# - HD1 .............................................................................................................................. A-2 HD1 Flat File Edit Specifications............................................................................................................... A-3 FLAT FILE ID# - HD2 .............................................................................................................................. A-4 HD2 Flat File Edit Specifications............................................................................................................. A-10 FLAT FILE ID# - HD3 ............................................................................................................................ A-16 HD3 Flat File Edit Specifications............................................................................................................. A-18 FLAT FILE ID# - HD4 ............................................................................................................................ A-19 HD4 Flat File Edit Specifications............................................................................................................. A-20 FLAT FILE ID# - HD5 ............................................................................................................................ A-21 HD5 Flat File Edit Specifications............................................................................................................. A-22 FLAT FILE ID# - HD6 ............................................................................................................................ A-23 HD6 Flat File Edit Specifications............................................................................................................. A-24 FLAT FILE ID# - HD7 ............................................................................................................................ A-25 HD7 Flat File Edit Specifications............................................................................................................. A-26 FLAT FILE ID# - HD8 ............................................................................................................................ A-27 HD8 Flat File Edit Specifications............................................................................................................. A-28 FLAT FILE ID# - HD9 ............................................................................................................................ A-29 HD9 Flat File Edit Specifications............................................................................................................. A-30 FLAT FILE ID# - HDA ........................................................................................................................... A-31 HDA Flat File Edit Specifications............................................................................................................ A-32 -ii- TABLE OF CONTENTS Page FLAT FILE ID# - PM1 ............................................................................................................................ A-33 PM1 Flat File Edit Specifications............................................................................................................. A-35 FLAT FILE ID# - PM2 ............................................................................................................................ A-36 PM2 Flat File Edit Specifications............................................................................................................. A-38 FLAT FILE ID# - PM3 ............................................................................................................................ A-40 PM3 Flat File Edit Specifications............................................................................................................. A-41 FLAT FILE ID# - PM4 ............................................................................................................................ A-42 PM4 Flat File Edit Specifications............................................................................................................. A-44 FLAT FILE ID# - PM5 ............................................................................................................................ A-45 PM5 Flat File Edit Specifications............................................................................................................. A-46 FLAT FILE ID# - PM6 ............................................................................................................................ A-47 PM6 Flat File Edit Specifications............................................................................................................. A-48 FLAT FILE ID# - CA1 ............................................................................................................................ A-49 CA1 Flat File Edit Specifications............................................................................................................. A-51 FLAT FILE ID# - CA2 ............................................................................................................................ A-52 CA2 Flat File Edit Specifications............................................................................................................. A-54 FLAT FILE ID# - CA3 ............................................................................................................................ A-55 CA3 Flat File Edit Specifications............................................................................................................. A-56 FLAT FILE ID# - CA4 ............................................................................................................................ A-57 CA4 Flat File Edit Specifications............................................................................................................. A-59 FLAT FILE ID# - CA5 ............................................................................................................................ A-61 CA5 Flat File Edit Specifications............................................................................................................. A-62 FLAT FILE ID# - CA6 ............................................................................................................................ A-63 CA6 Flat File Edit Specifications............................................................................................................. A-64 FLAT FILE ID# - CE1............................................................................................................................. A-65 CE1 Flat File Edit Specifications ............................................................................................................. A-67 FLAT FILE ID# - CE2............................................................................................................................. A-70 CE2 Flat File Edit Specifications ............................................................................................................. A-71 FLAT FILE ID# - CE3............................................................................................................................. A-72 CE3 Flat File Edit Specifications ............................................................................................................. A-73 FLAT FILE ID# - CE4............................................................................................................................. A-74 CE4 Flat File Edit Specifications ............................................................................................................. A-76 FLAT FILE ID# - CE5............................................................................................................................. A-78 CE5 Flat File Edit Specifications ............................................................................................................. A-79 FLAT FILE ID# - CE6............................................................................................................................. A-80 CE6 Flat File Edit Specifications ............................................................................................................. A-81 FLAT FILE ID# - CE7............................................................................................................................. A-82 CE7 Flat File Edit Specifications ............................................................................................................. A-83 FLAT FILE ID# - CE8............................................................................................................................. A-84 CE8 Flat File Edit Specifications ............................................................................................................. A-86 FLAT FILE ID# - CE9............................................................................................................................. A-88 CE9 Flat File Edit Specifications ............................................................................................................. A-89 FLAT FILE ID# - CEA ............................................................................................................................ A-90 CEA Flat File Edit Specifications ............................................................................................................ A-91 FLAT FILE ID# - CEB ............................................................................................................................ A-92 CEB Flat File Edit Specifications............................................................................................................. A-94 FLAT FILE ID# - CEC ............................................................................................................................ A-96 CEC Flat File Edit Specifications............................................................................................................. A-97 FLAT FILE ID# - CED ............................................................................................................................ A-98 CED Flat File Edit Specifications .......................................................................................................... A-100 -iii- TABLE OF CONTENTS Page FLAT FILE ID# - CEE .......................................................................................................................... A-102 CEE Flat File Edit Specifications........................................................................................................... A-103 FLAT FILE ID# - CEF........................................................................................................................... A-105 CEF Flat File Edit Specifications ........................................................................................................... A-107 FLAT FILE ID# - CEG .......................................................................................................................... A-109 CEG Flat File Edit Specifications .......................................................................................................... A-111 -iv- 1. INTRODUCTION RCRAInfo is a national system and database which the Environmental Protection Agency (EPA) and States use to record and track information provided by the regulated community concerning the generation, shipment, treatment, and disposal of hazardous wastes, as well as significant milestones of State/EPA activity supporting program planning, implementation, and accomplishments reporting. While RCRAInfo provides a data entry mechanism to enter data into the RCRAInfo database, States and EPA Regions have the option to use their own software and procedures to collect and track RCRAInfo data and to provide that data to RCRAInfo in electronic format. This document describes the file specifications for these States and Regions to supply RCRA data to RCRAInfo. This guide is not for use in developing flat files for implementers’ Biennial Hazardous Waste Report submissions. The file specifications for Biennial Hazardous Waste Report submissions can be found at http://www.epa.gov/cdx/rcraft/2003%20BR%20File%20Specification%20Guide.pdf. 1.1 Overview of Document The RCRAInfo Translator Guide - Version 3 document is divided into six sections: Section 1 (Introduction) defines the intended audience for this guide and describes the general purpose and outline of this document. Section 2 (Data Submission Overview) describes the overall characteristics for a data submission. Section 3 (Technical Specifications) discusses the technical details of the data files and programs necessary for data submission. Section 4 (Generic Edits) discusses the data standards used for each data submission. Section 5 (Flat File Transfer [Via CDX]) discusses the method of submitting a flat file load via the RCRA Central Data Exchange (CDX) application. Section 6 (Version 3 Data Submissions) discusses issues specific to Version 3. Several appendices are included with this document. These appendices provide background material as well as detailed technical information necessary to properly prepare file submissions. 1.2 Intended Audience The intended audience for this guide is any State or EPA Region that is using its own software and procedures to extract RCRA hazardous waste data for any or all of the RCRAInfo subject areas (Handler Identification, Permitting, Corrective Action, or Compliance Monitoring and Enforcement) from a State or Regional system for submission to EPA Headquarters (HQ) for inclusion in the RCRAInfo database. These States or Regions are called translators and are referred to as such throughout this document. This document was written assuming the reader 1) is familiar with the RCRAInfo database and 2) understands basic computer concepts and terminology. June 2005 1 RCRAInfo Translator Guide-V3 1.3 Data Files Translators submit RCRAInfo data to EPA HQ via a series of flat files. The structure and relationships of these flat files are detailed in this document. Oracle to Oracle data transfers are not supported. The remainder of this document describes in detail the files necessary to ensure a successful data submission, including the types of files that must be included with each submission and technical aspects of the file creation process. 1.4 Questions/Comments Questions about this document should be directed to the RCRAInfo help desk at rcrainfo.help@epa.gov. Questions submitted to the help desk must only concern the file specification for submission of data from the States or EPA Regions to the RCRAInfo database. June 2005 2 RCRAInfo Translator Guide-V3 2. DATA SUBMISSION OVERVIEW 2.1 Roles and Responsibilities The following section details the roles and responsibilities needed to ensure successful translation into the RCRAInfo database. State/Region Roles and Responsibilities The RCRAInfo translation process contains several steps that must all be successfully implemented to complete the process. These steps include: 1) producing the flat files, 2) uploading the flat files to the EPA server, 3) loading the files into RCRAInfo, 4) monitoring the load process, and 5) correcting load errors. The responsibilities for these steps may be shared by the State and Region. It is very important to assign responsibilities for each of these steps to ensure successful translation and to avoid mis-communications that could lead to the loading of erroneous data into the RCRAInfo database. Implementer of Record (IOR) is the term used to describe the agency (EPA or State) responsible for data entry and management of RCRAInfo handler identification and program activity data. Data entry and management responsibility does not equate to programmatic responsibility. The States and Regions are responsible for determining data ownership areas and accurately reflecting these agreements in RCRAInfo. Although all implementer data in RCRAInfo resides in the same physical database, each implementer owns and controls access to its data. Security functions implemented at both the database and application levels prevent unauthorized users from changing data. There are two types of IOR in RCRAInfo: fixed and variable. Fixed IOR is implemented for data in which one agency (State or Region) is wholly responsible. Variable IOR is implemented for data in which the responsibilities vary depending on the values of specific data elements. The RCRAInfo On-Line Help utility provides subject-area specific implementer of record documentation. The RCRAInfo Translator Flat File Load utility software enforces IOR security settings. Data not owned by the implementer submitting the translator files will not be loaded. Ownership is derived from the User ID of the implementer submitting the translator load and will be used to match each data record in the load against the database IOR settings. Implementer Roles and Responsibilities The translator data must provide an accurate representation of hazardous waste tracking and activity for that State. In addition, the translator’s data must pass a minimum set of data edits in order to provide information comparable to data in RCRAInfo and to be properly loaded into the RCRAInfo database. Data failing to conform to the appropriate data quality edits will result in the rejection of the record. After producing your files, you must move the files to a place where than can be access by the RCRAInfo load routines. For the Version 3 translator load program, you must upload your files through EPA’s Central Data Exchange (CDX). For more details, see Section 5.0 - Flat File Transfer (Via CDX) - of this document. June 2005 3 RCRAInfo Translator Guide-V3 EPA Headquarters Roles and Responsibilities Structural changes to RCRAInfo will be limited to once per year to ease burden on translator Regions and States. EPA Headquarters is responsible for providing flat file specifications and the load software when structural changes occur. EPA Region Roles and Responsibilities Regardless of how the roles and responsibilities for the step-by-step translator process are assigned between the State and the Region, it is the Region’s responsibility to ensure that regular updates of the data are successfully loaded into RCRAInfo. Currently updates are required at a minimum frequency of once a month (or more frequently if desired by the State and Region). Monthly updates should be complete in the first two weeks of the month. 2.2 RCRAInfo Load Processing The translator must include a “control” file with all RCRAInfo data submissions. The specifications for this file are included in the flat file specifications. The control file records contain information describing the flat files being submitted. One control file record is created for each submitted flat file. Flat files should be submitted only if they contain one or more data records. The control file should contain information only on flat files submitted. If a flat file is not submitted, no information for the file should be placed in the control file. 2.3 Full Replacement Indicator The control file contains a field called “Full_Replacement_Indicator.” This field controls by which of the two (2) methods the RCRAInfo data will be submitted: 1) Replacement method or 2) Change method. Details on each method are given below. The replacement method deletes all data (including child records) in RCRAInfo for the table designated by the flat file and replaces it with the data in the current submission. The replacement method is available for the following flat files only: HD2 (HHANDLER2), PM1 (PSERIES), PM3 (PUNIT), CA1 (AAREA), CA2 (ACA_AUTHORITY), CA4 (AEVENT), CE1 (CEVALUATION3), and CE9 (CENFORCEMENT3). The replacement method includes cascading deletes, meaning that RCRAInfo data will be deleted for the table specified and child records of that table. If a child record is linked to more than one parent record and all of the parent records are marked for deletion, the child record will also be deleted. If a child record is linked to more than one parent record but not all of the parent records are marked for deletion, the child record will NOT be deleted but the link between the child and the deleted parent will be removed. If the translator uses the replacement method for file PM1, for example, all PSERIES data will be deleted, as well as all PEVENT data and PLN_EVENT_UNIT that is linked exclusively to the PSERIES data to be deleted. Therefore, the translator’s submission must also include data for the PEVENT and PLN_EVENT_UNIT data to “replace” the data that was deleted. The following table (Exhibit 1) indicates the tables that will be affected during a full replacement delete operation, and the respective flat files that need to be provided to replace the deleted data. June 2005 4 RCRAInfo Translator Guide-V3 Full Replacement Submitted at: HD2 Parent Table Deleted HHANDLER2 Child Tables Affected (and corresponding flat files which must be provided to replace the deleted data) HOWNER_OPERATOR2 (HD3) HNAICS (HD4) HOTHER_PERMIT2 (HD5) HWASTE_CODE (HD6) HUNIVERSAL_WASTE (HD7) HCERTIFICATION (HD8) HSTATE_ACTIVITY (HD9) PEVENT (PM2) PLN_EVENT_UNIT (PM5) PUNIT_DETAIL (PM4) PLN_EVENT_UNIT (PM5) PUNIT_DETAIL_WASTE (PM6) ALN_AREA_EVENT (CA5) ASTATUTE_CITATION (CA3) ALN_EVENT_AUTHORITY (CA6) ALN_AREA_EVENT (CA5) ALN_EVENT_AUTHORITY (CA6) CLN_EVALUATION_COMMITMENT3(CE4) CVIOLATION3 (CE5) CCITATION3 (CE6) CLN_EVALUATION_VIOLATION3 (CE8) CENFORCEMENT3 (CE9) CLN_VIOLATION_ENFORCEMENT3 (CEA) CMEDIA3 (CEB) CSEP3 (CEC) CSNY_DATE3 (CED) CMILESTONE3 (CEE) CPENALTY3 (CEF) CPAYMENT3 (CEG) CLN_VIOLATION_ENFORCEMENT3 (CEA) CMEDIA3 (CEB) CSEP3 (CEC) CSNY_DATE3 (CED) CMILESTONE3 (CEE) CPENALTY3 (CEF) CPAYMENT3 (CEG) PM1 PM3 PSERIES PUNIT CA1 CA2 CA4 CE1 AAREA ACA_AUTHORITY AEVENT CEVALUATION3 CE9 CENFORCEMENT3 Exhibit 1. Tables affected by Full Replacement Delete June 2005 5 RCRAInfo Translator Guide-V3 The change method does NOT delete all data in RCRAInfo prior to processing. Rather, the change method processes in a transactional manner. However, delete transactions are cascading and the implications should be understood before processing delete transactions. Specific to the CM&E module, cascading deletes are not allowed for transactional processing if an enforcement action is linked to a violation. For example, if Evaluation “A” is marked for deletion then Violation “B” which is linked to Evaluation “A” will only be deleted if Violation “B” does not have any linked enforcement actions. If Violation “B” has linked enforcement actions, the enforcement actions must be deleted prior to the deletion of the violation (or cascading evaluation). Translators should fully understand the concepts and consequences of the replacement and change methods before submitting data files to RCRAInfo. A translator’s submission may include a combination of replacement and change methods. For example, the translator may specify the replacement method for the PM1 file, but the change method for the CA1 file. The files required for a submission vary depending on whether the replacement or change method is specified. The CTL (control file) and at least one other flat file must be provided with every submission. The load software will NOT allow implementers to delete data (either by full replacement or change method) that the implementer does not own or for which there are other programmatic issues that prevent the data from being deleted (i.e., other implementers having data linked to the data marked for deletion). 2.4 Transaction Code The translator can specify one of two transaction types for each record: 1) A (Add/Update) or 2) D (Delete). The Add/Update transaction will add a record to the RCRAInfo database if the primary key values do not currently exist in the database. If the primary key values do currently exist, the non-key values will be updated with the values in the submission. For add/update transactions, the translator must provide all required data associated with the record. The Delete transaction will delete the parent record and all child records from the RCRAInfo database. The delete transaction will be successful ONLY if the primary key value exists in the RCRAInfo database, all child records are owned by the translator, the child records are not linked to any other parent, and, in the case of CM&E data, other specific requirements (see section 2.3 above) have been met. For delete transactions, the translator must only provide the key fields necessary to identify the parent record to be deleted. Transactions are processed in sequential order as they appear in the flat file. If a delete transaction is processed, a subsequent add/update transaction can put the data back into RCRAInfo. June 2005 6 RCRAInfo Translator Guide-V3 3. TECHNICAL SPECIFICATIONS This section contains the standards that must be met when producing flat files for the RCRAInfo data submission. Failure to meet these specifications will result in failure to load the data into the RCRAInfo database. 3.1 Rules and Format Conventions Required for Data Flat Files The flat files comprising your submission must be provided using the following file naming convention: SSFFFNNN.FIL where SS = State Postal Code of the Activity Location for this submission Flat File Identifier (see Appendix A: Flat File Specifications and Data Edits) Sequence Indicator for the submission FFF = NNN = Note: The three-character flat file identifier distinguishes each flat file produced during the translation. In addition, the sequence number must be the same for ALL files provided for single submission. For example, a submission containing only HBASIC data for Kansas would contain the control file (KSCTL001.FIL) and the HBASIC file (KSHD1001.FIL). The following sections detail the correct field formats for the data in the flat files. 3.1.1 Alphanumeric Fields Alphanumeric fields are identified in Appendix A as Data Type “A” fields. Data Type “A” fields must be left-justified with all trailing spaces filled with the space character (ASCII HEX 0x20 or ASCII Decimal 32). Valid characters for alphanumeric fields are limited to: ~!#$%^*()_+-\=:;?,./’”@&1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ Invalid characters for alphanumeric fields include: {}[]<>| (concatenation character) As part of the RCRAInfo load routines, all lowercase letters (a-z) will be converted to uppercase characters (A-Z), except for in the “Notes” fields. Lowercase letters will not cause a submission to be rejected, however the lowercase letters will be converted to uppercase characters. 3.1.2 Integer Fields Integer fields are identified in the flat file specifications as Data Type “I” fields. Data Type “I” fields must be right-justified and leading spaces must be filled by the space character (ASCII HEX 0x20 or ASCII Decimal 32). June 2005 7 RCRAInfo Translator Guide-V3 Allowed values for Integer Fields are number 0-9, and the space characters (ASCII HEX 0x20 or ASCII Decimal 32). Examples of incorrect and correct entries for an integer field defined with a length of five (5) are presented in Exhibit 2 below. INCORRECT 1A 10,000 750.25 CORRECT 1 10000 750 Exhibit 2. Incorrect and Correct Integer Entries 3.1.3 Fixed Decimal Fields Fixed place decimal fields are identified in the flat file specifications as Data Type “D” fields. Leading spaces must be filled by the space character (ASCII HEX 0x20 or ASCII Decimal 32). For all “D” field entries, the flat file specifications indicate the total number of digits and the number of digits after the decimal that the data element is allowed. For example, “D11,6” indicates that the number may have up to 11 digits total: 6 digits after the decimal and the remaining 5 digits before the decimal (99999.999999). The period (.) character, representing the decimal, must be included for fixed decimal fields. The field length includes the decimal character. For D11,6 the field length would be 12 (the 11 digits plus the decimal). Allowed values for Fixed Decimal Fields are number 0-9, the decimal character (“.”), and the space character (ASCII Hex 0x20 or ASCII Decimal 32). Exhibit 3 shows incorrect and correct entries in a type “D7,2” field. INCORRECT 10,032.10 98765.000 CORRECT 10032.10 98765.00 Exhibit 3. Incorrect and Correct Fixed Decimal Entries June 2005 8 RCRAInfo Translator Guide-V3 3.1.4 Date Fields Date fields are identified in the flat file specifications as Data Type “Date” fields. Date fields must be provided in CCYYMMDD format where CC is the century, YY is the year, MM is the month and DD is the day. Leading zeros must be provided where appropriate. Exhibit 4 shows incorrect and correct entries for date fields. Date January 3, 1999 January 3, 2001 INCORRECT 990103 200113 CORRECT 19990103 20010103 Exhibit 4. Incorrect and Correct Date Entries 3.1.5 Negative Numbers Negative numbers are not allowed in the data submission. 3.1.6 Record Termination Each flat file record must be terminated by a line feed character (ASCII Hex 0x0A or ASCII Decimal 010), or a carriage return character (ASCII Hex 0x0D or ASCII Decimal 013) followed by a line feed character. 3.1.7 Empty Fields For fields that require no response (including alphanumeric, integer, fixed decimal, and date fields), the field should be filled with the space character (ASCII Hex 0x20 or ASCII Decimal 32). June 2005 9 RCRAInfo Translator Guide-V3 4. GENERIC EDITS The data provided in the flat file submission must meet data quality standards for each data element as specified in Appendix A. In addition, the following edits apply to all data elements and records in the flat file submission: • • • • • • • • A prohibited character was found in the flat file submission record. Invalid character found in numeric field. Invalid character found in date field. Invalid date found in date field. The first character of an alphanumeric field that is a primary key cannot be a space. Transaction code must equal “A” or “D”. Existing record to delete not found. Implementer must be IOR to add/change/delete data. June 2005 10 RCRAInfo Translator Guide-V3 5. FLAT FILE TRANSFER (VIA CDX) Flat files are transferred to the EPA Unix server via the RCRA Central Data Exchange (CDX) application. The data is transferred to the EPA in the form of Windows-compatible ZIP files. The RCRAInfo File Transfer Process extracts the files from these zip files and stores the data in Oracle database “staging” tables for further processing. The RCRA staging process is designed to run as a background process that continuously looks for files to process. Therefore, there is no user interaction required once the environment is configured and the process is started. CDX provides an interface for submitters to upload a ZIP file from their local file location (commonly the C:\ drive) to the CDX/RCRAInfo environments. The uploaded file must be in the form of a ZIP file and can contain a minimum of 2 files and a maximum of 40 files, including a mandatory control file that lists the names of the other files in the ZIP file. These constituent files should be: - flat files; - column-delimited; - formatted such that each record in the file is followed by a carriage return/line feed; and - formatted per OSW’s additional requirements. The ZIP file must conform to PKWARE’s (de facto) compression standard. The EPA has tested and approved two compression utilities for use by submitters. They are available for download at http://www.epa.gov/cdx/test/zipintro.htm. A submitter is limited to filing data for one State per ZIP file. Before the file is uploaded, the CDX interface will validate the zip file name by the following criteria: - the first 3 characters are the program abbreviation: RCR - the next 4 characters are program-specific: 2-letter State abbreviation followed by the submission number (e.g., MD01) - the TSSMS ID used for CDX Registration: (e.g., KDH) An example file name would be RCRMD01KDH.zip. NOTE: The activity location (state code) embedded in the file name must match the state code within the account of the submitter that is filing the information. If the file name does not pass validation, the CDX interface will provide the submitter with a web page describing the error and prompt the submitter to correct the error in order to complete the file upload process. The error will be described as one of the following issues: - a State code mismatch - a user name mismatch - a data flow indicator code mismatch (must be “RCR”) Any potential users of the RCRA File Transfer account must first be screened by the EPA program office and preregistered by the CDX Help Desk at http://cdx.epa.gov/Help.asp. The online user guide is located at http://www.epa.gov/cdx/rcraft/index.htm. June 2005 11 RCRAInfo Translator Guide-V3 6. VERSION 3 SUBMISSIONS The Handler Monitoring and Assistance Program Area Analysis recommended a full conversion of the CM&E data to reflect the new Version 3 codes and structures. Subsequently, the RCRAInfo Version 3 Design Team agreed with the HMA PAA recommendation of fully converting the existing CM&E data. All data residing in RCRAInfo, whether entered via the data entry application or through the translation software, will be converted to meet the Version 3 data specifications. Translators submitting CM&E data in Version 3 will need to modify their software to meet these specifications. The data specifications for all modules can be found in Appendix A. Please note that the data specifications for the CM&E module have changed significantly based on the Version 3 structure. It is important that all translator software be carefully reviewed to ensure that the translation meets the Version 3 data specifications. In addition, it is important that translators review the valid codes for Version 3 and convert existing codes to the V3 codes using the Version 3 Conversion Guidance as a reference. Translators submitting data via the Achange@ method (see Section 2.3 for explanation of methods) will need to revise their translator software to ensure that data being added or changed meets the Version 3 data specifications. Translators submitting data via the Areplacement@ method will need to convert ALL of their data to meet the Version 3 data specifications (structure changes and data value changes). June 2005 12 RCRAInfo Translator Guide-V3 APPENDIX A Flat File Specifications and Data Edits Appendix A - Flat File Specifications and Data Edits Notes: 1. The following specifications and edits show requirements for Add/Update transactions. The requirements for Delete transactions are listed in the footnotes below each table. 2. The translator does not provide activity location in any of the file specifications. The activity location is derived from the location indicated in the submission file. 3. The translator does not provide owner for any codes from look up tables. The owner is either HQ, US or derived from the location indicated in the submission file. FLAT FILE ID# - CTL Description: Control File - Name and statistical information for each flat file submitted. Field Number 1 2 3 4 5 Starting Column 1 4 5 13 19 Field Length 3 1 8 6 1 19 Data Type A I Date I A Description Flat File ID Software Version Number (Version 3 = ‘3’) Date Created (CCYYMMDD) Record Count Full Replacement Indicator (Yes = ‘Y’, No = ‘N’) Total Record Length: June 2005 A-1 RCRAInfo Translator Guide-V3 FLAT FILE ID# - HD1 Table Name: HBASIC CDX Staging Table Name: TR_HD1_V2 Description: Handler Basic Identification Field No. 1 2 3 4 5 Field Name TRANSACTION_CODE HANDLER_ID EXTRACT_FLAG FACILITY_IDENTIFIER NOTES Total Record Length: Starting Column 1 2 14 15 27 Field Length 1 12 1 12 240 266 Data Type A A A A A Description Transaction Code EPA Handler ID Extract Flag Facility Identifier Notes HD1-010, HD1-020 HD1-035 Edit Number(s) June 2005 A-2 RCRAInfo Translator Guide-V3 HD1 Flat File Edit Specifications Edit Number HD-000 HD1-010 HD1-020 HD1-035 Edit Description Translator is not implementer of record for the Handler Module. The first two characters of Handler ID must be a valid state postal code. Handler ID must be at least four characters and no more than twelve characters. Extract Flag must equal ‘X’ or blank. SUBSTR(HANDLER_ID,1,2) = valid state postal code (See Appendix B) LENGTH(HANDLER_ID) >= 4 and LENGTH(HANDLER_ID) <= 12 EXTRACT_FLAG = ‘X’ or ‘ ’ Select Logic June 2005 A-3 RCRAInfo Translator Guide-V3 FLAT FILE ID# - HD2 Table Name: HHANDLER2 CDX Staging Table Name: TR_HD2_V2 Description: Handler Identification and Activity Data Field No. 1 2 3 4 5 6 7 8 9 10 11 12 13 1 Field Name TRANSACTION_CODE1 HANDLER_ID1 SOURCE_TYPE1 SEQ_NUMBER1 RECEIVE_DATE SITE_NAME NON_NOTIFIER NUMBER_OF_EMPLOYEES LOCATION_STREET_NO LOCATION_STREET1 LOCATION_STREET2 LOCATION_CITY LOCATION_STATE Starting Column 1 2 14 15 21 29 69 70 79 91 121 151 176 Field Length 1 12 1 6 8 40 1 9 12 30 30 25 2 Data Type A A A I Date A A I A A A A A Description Transaction Code EPA Handler ID Source Type Handler Sequence Number Receive Date Site Name Non-Notifier Flag Number of Employees Location Street Number Location Street Address 1 Location Street Address 2 Location City Location State Edit Number(s) HD2-005, HD2-010, HD2-550, HD2-560 HD2-020, HD2-540 HD2-030 HD2-040 HD2-050 HD2-060 HD2-070 HD2-080 HD2-090 HD2-100 The fields that must be provided for Delete transactions (TRANSACTION_TYPE = ‘D’). June 2005 A-4 RCRAInfo Translator Guide-V3 FLAT FILE ID# - HD2 Table Name: HHANDLER2 CDX Staging Table Name: TR_HD2_V2 Description: Handler Identification and Activity Data Field No. 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 June 2005 Field Name LOCATION_ZIP LAND_TYPE STATE_DISTRICT MAIL_STREET_NO MAIL_STREET1 MAIL_STREET2 MAIL_CITY MAIL_STATE MAIL_ZIP MAIL_COUNTRY CONTACT_FIRST_NAME CONTACT_MIDDLE_INITIAL CONTACT_LAST_NAME CONTACT_STREET_NO CONTACT_STREET1 CONTACT_STREET2 Starting Column 178 192 193 203 215 245 275 300 302 316 318 333 334 349 361 391 Field Length 14 1 10 12 30 30 25 2 14 2 15 1 15 12 30 30 A-5 Data Type A A A A A A A A A A A A A A A A Description Location Zip Code Land Type State District Mailing Street Number Mailing Street Address 1 Mailing Street Address 2 Mailing City Mailing State Mailing Zip Code Mailing Country Contact First Name Contact Middle Initial Contact Last Name Contact Street Number Contact Street Address 1 Contact Street Address 2 Edit Number(s) HD2-110, HD2-120 HD2-130 HD2-140 HD2-150 HD2-160, HD2-165 HD2-170 HD2-180 HD2-190 HD2-200 RCRAInfo Translator Guide-V3 FLAT FILE ID# - HD2 Table Name: HHANDLER2 CDX Staging Table Name: TR_HD2_V2 Description: Handler Identification and Activity Data Field No. 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 June 2005 Field Name CONTACT_CITY CONTACT_STATE CONTACT_ZIP CONTACT_COUNTRY CONTACT_PHONE CONTACT_PHONE_EXT CONTACT_EMAIL_ADDRESS PCONTACT_FIRST_NAME PCONTACT_MIDDLE_INITIAL PCONTACT_LAST_NAME PCONTACT_STREET_NO PCONTACT_STREET1 PCONTACT_STREET2 PCONTACT_CITY PCONTACT_STATE PCONTACT_ZIP Starting Column 421 446 448 462 464 479 485 725 740 741 756 768 798 828 853 855 Field Length 25 2 14 2 15 6 240 15 1 15 12 30 30 25 2 14 A-6 Data Type A A A A A A A A A A A A A A A A Contact City Contact State Description Edit Number(s) HD2-220, HD2-225 Contact Zip Code Contact Country Code Contact Phone Contact Phone Extension Contact E-mail Address Permit Contact First Name Permit Contact Middle Initial Permit Contact Last Name Permit Contact Street Number Permit Contact Street Address 1 Permit Contact Street Address 2 Permit Contact City Permit Contact State Permit Contact Zip Code RCRAInfo Translator Guide-V3 HD2-230 HD2-210 HD2-240 HD2-250 HD2-270, HD2-275 FLAT FILE ID# - HD2 Table Name: HHANDLER2 CDX Staging Table Name: TR_HD2_V2 Description: Handler Identification and Activity Data Field No. 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 June 2005 Field Name PCONTACT_COUNTRY PCONTACT_PHONE PCONTACT_PHONE_EXT PCONTACT_EMAIL_ADDRESS GEOMETRIC_TYPE_CODE HORIZONTAL_ACCURACY_MEASURE HORIZONTAL_COLLECTION_METHOD Starting Column 869 871 886 892 1132 1135 1141 1144 1147 1158 1170 1173 1183 1184 1185 1186 Field Length 2 15 6 240 3 6 3 3 11 12 3 10 1 1 1 1 A-7 Data Type A A A A A A A A D10.6 D11.6 A I A A A A Description Permit Contact Country Code Permit Contact Phone Permit Contact Phone Extension Permit Contact E-mail Address Geometric Type Code Horizontal Accuracy Measure Horizontal Collection Method Horizontal Reference Datum Latitude Measure Longitude Measure Reference Point Code Source Map Scale Numbers Importer Activity Mixed Waste Generator Recycler Activity Transporter Activity Edit Number(s) HD2-280 HD2-260 HD2-290 HD2-300 HD2-310 HD2-320 HD2-330 HD2-340 HORIZONTAL_REFERENCE_DATUM LATITUDE_MEASURE LONGITUDE_MEASURE REFERENCE_POINT_CODE SOURCE_MAP_SCALE_NUMBERS IMPORTER_ACTIVITY MIXED_WASTE_GENERATOR RECYCLER_ACTIVITY TRANSPORTER_ACTIVITY HD2-350 HD2-360 HD2-370 HD2-380 RCRAInfo Translator Guide-V3 FLAT FILE ID# - HD2 Table Name: HHANDLER2 CDX Staging Table Name: TR_HD2_V2 Description: Handler Identification and Activity Data Field No. 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 June 2005 Field Name TSD_ACTIVITY UNDERGROUND_INJECTION_ACTIVITY Starting Column 1187 1188 1189 1190 1191 1192 1193 1194 1195 1196 1197 1198 1199 1200 1212 1220 Field Length 1 1 1 1 1 1 1 1 1 1 1 1 1 12 8 1 A-8 Data Type A A A TSD Activity Description Edit Number(s) HD2-390 HD2-400 HD2-410 Underground Injection Activity Universal Waste Destination Facility UNIVERSAL_WASTE_DEST_FACILITY **OBSOLETE FIELD** ONSITE_BURNER_EXEMPTION FURNACE_EXEMPTION USED_OIL_BURNER USED_OIL_PROCESSOR USED_OIL_REFINER USED_OIL_MARKET_BURNER USED_OIL_SPEC_MARKETER USED_OIL_TRANSFER_FACILITY USED_OIL_TRANSPORTER SECOND_ID **OBSOLETE FIELD** OFF_SITE_RECEIPT A A A A A A A A A A Onsite Burner Exemption Furnace Exemption Used Oil Fuel Burner Used Oil Processor Used Oil Refiner Used Oil Fuel Marketer to Burner Specification Used Oil Marketer Used Oil Transfer Facility Used Oil Transporter Other/Secondary Identification Number HD2-420 HD2-430 HD2-440 HD2-450 HD2-460 HD2-470 HD2-480 HD2-490 HD2-500 A Off-Site Waste Receipt RCRAInfo Translator Guide-V3 FLAT FILE ID# - HD2 Table Name: HHANDLER2 CDX Staging Table Name: TR_HD2_V2 Description: Handler Identification and Activity Data Field No. 78 79 80 81 81 83 84 85 Field Name ACCESSIBILITY **SYSTEM-GENERATED FIELD** COUNTY_CODE **SYSTEM-GENERATED FIELD** FED_WASTE_GENERATOR **SYSTEM-GENERATED FIELD** STATE_WASTE_GENERATOR NOTES Total Record Length: Starting Column 1221 1222 1224 1229 1231 1232 1234 1235 Field Length 1 2 5 2 1 2 1 2000 3234 A A A A Data Type A Accessibility County Owner – Will be set to ‘HQ’ in the database. County Code HD2-510 Description Edit Number(s) Federal Waste Generator Status Owner – Will be set to ‘HQ’ in the database. Federal Waste Generator Status Code HD2-520 State Waste Generator Status Owner – Will be set to your user agency in the database. State Waste Generator Status Code Notes HD2-530 June 2005 A-9 RCRAInfo Translator Guide-V3 HD2 Flat File Edit Specifications Edit Number HD-000 HD2-005 HD2-010 HD2-020 HD2-030 HD2-040 HD2-050 HD2-060 HD2-070 HD2-080 HD2-090 HD2-100 Edit Description Translator is not implementer of record for the Handler Module. Handler ID must be provided. Handler ID must exist in HBASIC or HD1. Source Type must equal ‘N’, ‘A’, ‘I’, ‘E’, or ‘T’. Sequence Number must be greater than zero. Receive Date must be provided and must be greater than or equal to January 1, 1980 and no later than today. Site Name must be provided. Non-Notifier must equal ‘E’, ‘X’, ‘O’, or blank. Number of Employees must be greater than zero or blank. Location Street 1 must be provided. Location City must be provided. If the first two characters of Handler ID equal ‘NN’ then Location State must be a valid state postal code. If the first two characters of Handler ID do not equal ‘NN’ then Location State must equal the first two characters of Handler ID. Location Zip Code must be provided. Location Zip Code must contain only the characters 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, or blank. HANDLER_ID <> ‘ ’ HANDLER_ID in HD2 must exist in HBASIC or HD1 SOURCE_TYPE = ‘N’ or ‘A’ or ‘I’ or ‘E’ or ‘T’ SEQ_NUMBER > 0 RECEIVE_DATE >= ‘19800101’ and RECEIVE_DATE <= today SITE_NAME <> ‘ ’ NON_NOTIFIER = ‘E’ or ‘X’ or ‘O’ or ‘ ’ NUMBER_OF_EMPLOYEES > 0 or NUMBER_OF_EMPLOYEES = ‘’ LOCATION_STREET1 <> ‘ ’ LOCATION_CITY <> ‘ ’ If SUBSTR(HANDLER_ID,1,2) = ‘NN’ Then LOCATION_STATE = valid state postal code If SUBSTR(HANDLER_ID,1,2) <> ‘NN’ Then LOCATION_STATE = SUBSTR(HANDLER_ID,1,2) LOCATION_ZIP <> ‘ ’ LOCATION_ZIP characters = ‘0’ or ‘1’ or ‘2’ or ‘3’ or ‘4’ or ‘5’ or ‘6’ or ‘7’ or ‘8’ or ‘9’ or ‘ ’ Select Logic HD2-110 HD2-120 June 2005 A-10 RCRAInfo Translator Guide-V3 HD2 Flat File Edit Specifications Edit Number HD2-130 Edit Description If Source Type equals ‘N’ or ‘A’ then Land Type must equal ‘P’, ‘C’, ‘D’, ‘F’, ‘I’, ‘M’, ‘S’, or ‘O’. If Source Type equals ‘I’, ‘E’, or ‘T’ then Land Type must equal ‘P’, ‘C’, ‘D’, ‘F’, ‘I’, ‘M’, ‘S’, ‘O’, or blank. If Source Type equals ‘N’ or ‘A’ then Mail Street 1 must be provided. If Source Type equals ‘N’ or ‘A’ then Mailing City must be provided. If Source Type equals ‘N’ or ‘A’ and Mailing Country equals blank or ‘US’ then Mailing State must equal a valid state postal code. If Source Type equals ‘I’, ‘E’, or ‘T’ and Mailing Country equals blank or ‘US’ then Mailing State must equal a valid state postal code or blank. If Mailing Country is not equal to blank or ‘US’ then Mailing State must equal blank. If Source Type equals ‘N’ or ‘A’ then Mailing Zip Code must be provided. Mailing Country must be a valid nationally defined value. If Mailing Country is blank, a value of ‘US’ will be assumed. If Source Type equals ‘N’ or ‘A’ then Contact First Name must be provided. If Source Type equals ‘N’ or ‘A’ then Contact Last Name must be provided. If Source Type equals ‘N’ or ‘A’ then Contact Phone must be provided. A-11 Select Logic If SOURCE_TYPE = ‘N’ or ‘A’ Then LAND_TYPE = ‘P’ or ‘C’ or ‘D’ or ‘F’ or ‘I’ or ‘M’ or ‘S’ or ‘O’ If SOURCE_TYPE = ‘I’ or ‘E’ or ‘T’ Then LAND_TYPE = ‘P’ or ‘C’ or ‘D’ or ‘F’ or ‘I’ or ‘M’ or ‘S’ or ‘O’ or ‘ ’ If SOURCE_TYPE = ‘N’ or ‘A’ Then MAIL_STREET1 <> ‘ ’ If SOURCE_TYPE = ‘N’ or ‘A’ Then MAIL_CITY <> ‘ ’ If SOURCE_TYPE = ‘N’ or ‘A’ and MAIL_COUNTRY = ‘ ’ or ‘US’ Then MAIL_STATE = valid state postal code If SOURCE_TYPE = ‘I’ or ‘E’ or ‘T’ and MAIL_COUNTRY = ‘ ’ or ‘US’ Then MAIL_STATE = valid state postal code or ‘ ’ HD2-140 HD2-150 HD2-160 HD2-165 HD2-170 HD2-180 HD2-190 HD2-200 HD2-210 If MAIL_COUNTRY <> ‘ ’ or ‘US’ Then MAIL_STATE = ‘ ’ If SOURCE_TYPE = ‘N’ or ‘A’ Then MAIL_ZIP <> ‘ ’ MAIL_COUNTRY = nationally defined country code provided in LU_COUNTRY or ‘ ’ If SOURCE_TYPE = ‘N’ or ‘A’ Then CONTACT_FIRST_NAME <> ‘’ If SOURCE_TYPE = ‘N’ or ‘A’ Then CONTACT_LAST_NAME <> ‘’ If SOURCE_TYPE = ‘N’ or ‘A’ Then CONTACT_PHONE <> ‘ ’ June 2005 RCRAInfo Translator Guide-V3 HD2 Flat File Edit Specifications Edit Number HD2-220 HD2-225 Edit Description If Contact Country Code is equals to blank or ‘US’ then Contact State must equal a valid state postal code or blank. If Contact Country Code is not equal to blank or ‘US’ then Contact State must equal blank. Contact Country Code must be a valid nationally defined value. If Contact Country Code is blank, a value of ‘UNITED STATES’ will be assumed. If Source Type equals ‘A’ then Permit Contact First Name must be provided. If Source Type equals ‘A’ then Permit Contact Last Name must be provided. If Source Type equals ‘A’ then Permit Contact Phone must be provided. If Permit Contact Country Code equals blank or ‘US’ then Permit Contact State must equal a valid state postal code or blank. If Permit Contact Country Code is not equal to blank or ‘US’ then Permit Contact State must equal blank. Permit Contact Country Code must be a valid nationally defined value. If Permit Contact Country Code is blank, a value of ‘UNITED STATES’ will be assumed. Geometric Type Code must equal ‘001', ‘002', ‘003', ‘004', ‘005', or blank. Select Logic If CONTACT_COUNTRY = ‘ ’ or ‘US’ Then CONTACT_STATE = valid state postal code or ‘ ’ If CONTACT_COUNTRY <> ‘ ’ or ‘US’ Then CONTACT_STATE = ‘’ CONTACT_COUNTRY = nationally defined country code provided in LU_COUNTRY or ‘ ’ If SOURCE_TYPE = ‘A’ Then PCONTACT_FIRST_NAME <> ‘ ’ If SOURCE_TYPE = ‘A’ Then PCONTACT_LAST_NAME <> ‘ ’ If SOURCE_TYPE = ‘A’ Then PCONTACT_PHONE <> ‘ ’ If PCONTACT_COUNTRY = ‘ ’ or ‘US’ Then PCONTACT_STATE = valid state postal code or ‘ ’ If PCONTACT_COUNTRY <> ‘ ’ or ‘US’ Then PCONTACT_STATE =‘’ PCONTACT_COUNTRY = nationally defined country code provided in LU_COUNTRY or ‘ ’ GEOMETRIC_TYPE_CODE = ‘001’ or ‘002’ or ‘003’ or ‘004’ or ‘005’ or ‘ ’ HD2-230 HD2-240 HD2-250 HD2-260 HD2-270 HD2-275 HD2-280 HD2-290 June 2005 A-12 RCRAInfo Translator Guide-V3 HD2 Flat File Edit Specifications Edit Number HD2-300 Edit Description Horizontal Collection Method must equal ‘001', ‘002', ‘003', ‘004', ‘005', ‘006', ‘007', ‘008', ‘009', ‘010', ‘011', ‘012', ‘013', ‘014', ‘015', ‘016', ‘017', ‘018', ‘019', ‘020', or blank. Horizontal Reference Datum must equal ‘001', ‘002', ‘003', or blank. Latitude Measure must be a valid latitude or blank. Longitude Measure must be a valid longitude or blank. Reference Point Code must equal ‘101', ‘102', ‘103', ‘104', ‘105', ‘106', ‘107', ‘108', or blank. If Source Type equals ‘N’ or ‘A’ then Importer Activity must equal ‘Y’ or ‘N’. If Source Type equals ‘I’, ‘E’, or ‘T’ then Importer Activity must equal ‘Y’, ‘N’, or ‘U’. If Source Type equals ‘N’ or ‘A’ then Mixed Waste Generator must equal ‘Y’ or ‘N’. If Source Type equals ‘I’, ‘E’, or ‘T’ then Mixed Waste Generator must equal ‘Y’, ‘N’, or ‘U’. If Source Type equals ‘N’ or ‘A’ then Recycler Activity must equal ‘Y’ or ‘N’. If Source Type equals ‘I’, ‘E’, or ‘T’ then Recycler Activity must equal ‘Y’, ‘N’, or ‘U’. If Source Type equals ‘N’ or ‘A’ then Transporter Activity must equal ‘Y’ or ‘N’. If Source Type equals ‘I’, ‘E’, or ‘T’ then Transporter Activity must equal ‘Y’, ‘N’, or ‘U’. Select Logic HORIZONTAL_COLLECTION_METHOD = ‘001’ or ‘002’ or ‘003’ or ‘004’ or ‘005’ or ‘006’ or ‘007’ or ‘008’ or ‘009’ or ‘010’ or ‘011’ or ‘012’ or ‘013’ or ‘014’ or ‘015’ or ‘016’ or ‘017’ or ‘018’ or ‘019’ or ‘020’ or ‘ ’ HORIZONTAL_REFERENCE_DATUM = ‘001’ or ‘002’ or ‘003’ or ‘’ LATITUDE_MEASURE >= 0 and <= 90.00000 or ‘ ’ LONGITUDE_MEASURE >= 0 and <= 180.00000 or ‘ ’ REFERENCE_POINT_CODE = ‘101’ or ‘102’ or ‘103’ or ‘104’ or ‘105’ or ‘106’ or ‘107’ or ‘108’ or ‘ ’ If SOURCE_TYPE = ‘N’ or ‘A’ Then IMPORTER_ACTIVITY = ‘Y’ or ‘N’ If SOURCE_TYPE = ‘I’ or ‘E’ or ‘T’ Then IMPORTER_ACTIVITY = ‘Y’ or ‘N’ or ‘U’ If SOURCE_TYPE = ‘N’ or ‘A’ Then MIXED_WASTE_GENERATOR = ‘Y’ or ‘N’ If SOURCE_TYPE = ‘I’ or ‘E’ or ‘T’ Then MIXED_WASTE_GENERATOR = ‘Y’ or ‘N’ or ‘U’ If SOURCE_TYPE = ‘N’ or ‘A’ Then RECYCLER_ACTIVITY = ‘Y’ or ‘N’ If SOURCE_TYPE = ‘I’ or ‘E’ or ‘T’ Then RECYCLER_ACTIVITY = ‘Y’ or ‘N’ or ‘U’ If SOURCE_TYPE = ‘N’ or ‘A’ Then TRANSPORTER_ACTIVITY = ‘Y’ or ‘N’ If SOURCE_TYPE = ‘I’ or ‘E’ or ‘T’ Then TRANSPORTER_ACTIVITY = ‘Y’ or ‘N’ or ‘U’ HD2-310 HD2-320 HD2-330 HD2-340 HD2-350 HD2-360 HD2-370 HD2-380 June 2005 A-13 RCRAInfo Translator Guide-V3 HD2 Flat File Edit Specifications Edit Number HD2-390 Edit Description If Source Type equals ‘N’ or ‘A’ then TSD Activity must equal ‘Y’ or ‘N’. If Source Type equals ‘I’, ‘E’, or ‘T’ then TSD Activity must equal ‘Y’, ‘N’, or ‘U’. If Source Type equals ‘N’ or ‘A’ then Underground Injection Activity must equal ‘Y’ or ‘N’. If Source Type equals ‘I’, ‘E’, or ‘T’ then Underground Injection Activity must equal ‘Y’, ‘N’, or ‘U’. If Source Type equals ‘N’ or ‘A’ then Universal Waste Destination Facility must equal ‘Y’ or ‘N’. If Source Type equals ‘I’, ‘E’, or ‘T’ then Universal Waste Destination Facility must equal ‘Y’, ‘N’, or ‘U’. If Source Type equals ‘N’ or ‘A’ then Small Quantity On-site Burner Exemption must equal ‘Y’ or ‘N’. If Source Type equals ‘I’, ‘E’, or ‘T’ then Small Quantity On-Site Burner Exemption must equal ‘Y’, ‘N’, or ‘U’. If Source Type equals ‘N’ or ‘A’ then Furnace Exemption must equal ‘Y’ or ‘N’. If Source Type equals ‘I’, ‘E’, or ‘T’ then Furnace Exemption must equal ‘Y’, ‘N’, or ‘U’. If Source Type equals ‘N’ or ‘A’ then Used Oil Fuel Burner must equal ‘Y’ or ‘N’. If Source Type equals ‘I’, ‘E’, or ‘T’ then Used Oil Fuel Burner must equal ‘Y’, ‘N’, or ‘U’. If Source Type equals ‘N’ or ‘A’ then Used Oil Processor must equal ‘Y’ or ‘N’. If Source Type equals ‘I’, ‘E’, or ‘T’ then Used Oil Processor must equal ‘Y’, ‘N’, or ‘U’. A-14 Select Logic If SOURCE_TYPE = ‘N’ or ‘A’ Then TSD_ACTIVITY = ‘Y’ or ‘N’ If SOURCE_TYPE = ‘I’ or ‘E’ or ‘T’ Then TSD_ACTIVITY = ‘Y’ or ‘N’ or ‘U’ If SOURCE_TYPE = ‘N’ or ‘A’ Then UNDERGROUND_INJECTION_ACTIVITY = ‘Y’ or ‘N’ If SOURCE_TYPE = ‘I’ or ‘E’ or ‘T’ Then UNDERGROUND_INJECTION_ACTIVITY = ‘Y’ or ‘N’ or ‘U’ If SOURCE_TYPE = ‘N’ or ‘A’ Then UNIVERSAL_WASTE_DEST_FACILITY = ‘Y’ or ‘N’ If SOURCE_TYPE = ‘I’ or ‘E’ or ‘T’ Then UNIVERSAL_WASTE_DEST_FACILITY = ‘Y’ or ‘N’ or ‘U’ If SOURCE_TYPE = ‘N’ or ‘A’ Then ONSITE_BURNER_EXEMPTION = ‘Y’ or ‘N’ If SOURCE_TYPE = ‘I’ or ‘E’ or ‘T’ Then ONSITE_BURNER_EXEMPTION = ‘Y’ or ‘N’ or ‘U’ If SOURCE_TYPE = ‘N’ or ‘A’ Then FURNACE_EXEMPTION = ‘Y’ or ‘N’ If SOURCE_TYPE = ‘I’ or ‘E’ or ‘T’ Then FURNACE_EXEMPTION = ‘Y’ or ‘N’ or ‘U’ If SOURCE_TYPE = ‘N’ or ‘A’ Then USED_OIL_BURNER = ‘Y’ or ‘N’ If SOURCE_TYPE = ‘I’ or ‘E’ or ‘T’ Then USED_OIL_BURNER = ‘Y’ or ‘N’ or ‘U’ If SOURCE_TYPE = ‘N’ or ‘A’ Then USED_OIL_PROCESSOR = ‘Y’ or ‘N’ If SOURCE_TYPE = ‘I’ or ‘E’ or ‘T’ Then USED_OIL_PROCESSOR = ‘Y’ or ‘N’ or ‘U’ RCRAInfo Translator Guide-V3 HD2-400 HD2-410 HD2-420 HD2-430 HD2-440 HD2-450 June 2005 HD2 Flat File Edit Specifications Edit Number HD2-460 Edit Description If Source Type equals ‘N’ or ‘A’ then Used Oil Refiner must equal ‘Y’ or ‘N’. If Source Type equals ‘I’, ‘E’, or ‘T’ then Used Oil Refiner must equal ‘Y’, ‘N’, or ‘U’. If Source Type equals ‘N’ or ‘A’ then Used Oil Fuel Marketer to Burner must equal ‘Y’ or ‘N’. If Source Type equals ‘I’, ‘E’, or ‘T’ then Used Oil Fuel Marketer to Burner must equal ‘Y’, ‘N’, or ‘U’. If Source Type equals ‘N’ or ‘A’ then Specification Used Oil Marketer must equal ‘Y’ or ‘N’. If Source Type equals ‘I’, ‘E’, or ‘T’ then Specification Used Oil Marketer must equal ‘Y’, ‘N’, or ‘U’. If Source Type equals ‘N’ or ‘A’ then Used Oil Transfer Facility must equal ‘Y’ or ‘N’. If Source Type equals ‘I’, ‘E’, or ‘T’ then Used Oil Transfer Facility must equal ‘Y’, ‘N’, or ‘U’. If Source Type equals ‘N’ or ‘A’ then Used Oil Transporter must equal ‘Y’ or ‘N’. If Source Type equals ‘I’, ‘E’, or ‘T’ then Used Oil Transporter must equal ‘Y’, ‘N’, or ‘U’. Location County Code must be a valid value. If Source Type equals ‘N’ or ‘A’ then Federal Waste Generator Status must equal ‘1', ‘2', ‘3', or ‘N’. If Source Type equals ‘I’, ‘E’, or ‘T’ then Federal Waste Generator Status must equal ‘1', ‘2', ‘3', ‘N’, or blank. Select Logic If SOURCE_TYPE = ‘N’ or ‘A’ Then USED_OIL_REFINER = ‘Y’ or ‘N’ If SOURCE_TYPE = ‘I’ or ‘E’ or ‘T’ Then USED_OIL_REFINER = ‘Y’ or ‘N’ or ‘U’ If SOURCE_TYPE = ‘N’ or ‘A’ Then USED_OIL_MARKET_BURNER = ‘Y’ or ‘N’ If SOURCE_TYPE = ‘I’ or ‘E’ or ‘T’ Then USED_OIL_MARKET_BURNER = ‘Y’ or ‘N’ or ‘U’ If SOURCE_TYPE = ‘N’ or ‘A’ Then USED_OIL_SPEC_MARKETER = ‘Y’ or ‘N’ If SOURCE_TYPE = ‘I’ or ‘E’ or ‘T’ Then USED_OIL_SPEC_MARKETER = ‘Y’ or ‘N’ or ‘U’ If SOURCE_TYPE = ‘N’ or ‘A’ Then USED_OIL_TRANSFER_FACILITY = ‘Y’ or ‘N’ If SOURCE_TYPE = ‘I’ or ‘E’ or ‘T’ Then USED_OIL_TRANSFER_FACILITY = ‘Y’ or ‘N’ or ‘U’ If SOURCE_TYPE = ‘N’ or ‘A’ Then USED_OIL_TRANSPORTER = ‘Y’ or ‘N’ If SOURCE_TYPE = ‘I’ or ‘E’ or ‘T’ Then USED_OIL_TRANSPORTER = ‘Y’ or ‘N’ or ‘U’ COUNTY_CODE = county code in LU_COUNTY If SOURCE_TYPE = ‘N’ or ‘A’ Then FED_WASTE_GENERATOR = ‘1’, ‘2’, ‘3’, or ‘N’ If SOURCE_TYPE = ‘I’ or ‘E’ or ‘T’ Then FED_WASTE_GENERATOR = ‘1’ or ‘2’ or ‘3’ or ‘N’ or ‘ ’ HD2-470 HD2-480 HD2-490 HD2-500 HD2-510 HD2-520 June 2005 A-15 RCRAInfo Translator Guide-V3 HD2 Flat File Edit Specifications Edit Number HD2-530 Edit Description If Source Type equals ‘N’ or ‘A’ then State Waste Generator Status must be a valid implementer defined value registered with the RCRAInfo database. If Source Type equals ‘I’, ‘E’, or ‘T’ then State Waste Generator Status must be a valid implementer defined value registered with the RCRAInfo database or blank. If Source Type equals ‘N’ or ‘A’ then must have at least one record in HNAICS or HD4 where NAICS Sequence Number equals 1. At least one record must exist in HCERTIFICATION or HD8. If RECEIVE_DATE is later than January 1, 2004 then at least one record must exist in HOWNER_OPERATOR2 or HD3 where Owner/Operator Indicator equals ‘CO’ and at least one record must exist in HOWNER_OPERATOR2 or HD3 where Owner/Operator Indicator equals ‘CP’. Select Logic If SOURCE_TYPE = ‘N’ or ‘A’ Then STATE_WASTE_GENERATOR = implementer defined waste generator status code in LU_GENERATOR_STATUS2 If SOURCE_TYPE = ‘I’, ‘E’, or ‘T’ Then STATE_WASTE_GENERATOR = implementer defined waste generator status code in LU_GENERATOR_STATUS2 or ‘ ’ If SOURCE_TYPE = ‘N’ or ‘A’ Then for HANDLER_ID, SOURCE_TYPE, SEQ_NUMBER in HD2 there must be a record in HNAICS or HD4 where NAICS_SEQ = 1 HANDLER_ID, SOURCE_TYPE, SEQ_NUMBER in HD2 must exist in HCERTIFICATION or HD8 If RECEIVE_DATE >= ‘20040101’ Then HANDLER_ID, SOURCE_TYPE, SEQ_NUMBER in HD2 must have at least one record in HOWNER_OPERATOR2 or HD3 where OWNER_OPERATOR_INDICATOR = ‘CO’ and at least one record in HOWNER_OPERATOR2 or HD3 where OWNER_OPERATOR_INDICATOR = ‘CP’ HD2-540 HD2-550 HD2-560 June 2005 A-16 RCRAInfo Translator Guide-V3 FLAT FILE ID# - HD3 Table Name: HOWNER_OPERATOR2 CDX Staging Table Name: TR_HD3_V2 Description: Handler Owner and Operator Information Field No. 1 2 3 4 5 6 7 8 9 10 11 12 1 Field Name TRANSACTION_CODE1 HANDLER_ID1 SOURCE_TYPE1 SEQ_NUMBER1 OWNER_OPERATOR_SEQ1 OWNER_OPERATOR_INDICATOR OWNER_OPERATOR_NAME OWNER_OPERATOR_TYPE DATE_BECAME_CURRENT DATE_ENDED_CURRENT STREET_NO STREET1 Starting Column 1 2 14 15 21 25 27 67 68 76 84 96 Field Length 1 12 1 6 4 2 40 1 8 8 12 30 Data Type A A A I I A A A Date Date A A Description Transaction Code EPA Handler ID Source Type Handler Sequence Number Owner/Operator Sequence Number Owner/Operator Indicator Owner/Operator Name Owner/Operator Type Date the Owner/Operator Became the Current Owner/Operator Date the Owner/Operator Ended Being the Current Owner/Operator Owner/Operator Street Number Owner/Operator Street Address 1 Edit Number(s) HD3-005, HD3-010 HD3-005, HD3-010 HD3-005, HD3-010 HD3-020 HD3-030 HD3-040 HD3-050 HD3-060 HD3-070 The fields that must be provided for Delete transactions (TRANSACTION_TYPE = ‘D’). June 2005 A-17 RCRAInfo Translator Guide-V3 FLAT FILE ID# - HD3 Table Name: HOWNER_OPERATOR2 CDX Staging Table Name: TR_HD3_V2 Description: Handler Owner and Operator Information Field No. 13 14 15 16 17 18 19 20 Field Name STREET2 CITY STATE COUNTRY ZIP PHONE DUNN_BRADSTREET_NUMBER NOTES Total Record Length: Starting Column 126 156 181 183 185 199 214 224 Field Length 30 25 2 2 14 15 10 240 463 Data Type A A A A A A A A Description Owner/Operator Street Address 2 Owner/Operator City Owner/Operator State Owner/Operator Country Code Owner/Operator Zip Code Owner/Operator Phone Dunn & Bradstreet Number Notes HD3-080 HD3-090 Edit Number(s) June 2005 A-18 RCRAInfo Translator Guide-V3 HD3 Flat File Edit Specifications Edit Number HD-000 HD3-005 HD3-010 HD3-020 HD3-030 HD3-040 HD3-050 HD3-060 HD3-070 Edit Description Translator is not implementer of record for the Handler Module. Handler ID, Source Type, and Sequence Number must be provided. Handler ID, Source Type, and Sequence Number must exist in HHANDLER2 or HD2. Owner/Operator Sequence Number must be greater than zero. Owner/Operator Indicator must equal ‘CO’ or ‘CP’. Owner/Operator Name must be provided. Owner/Operator Type must equal ‘P’, ‘C’, ‘D’, ‘F’, ‘I’, ‘M’, ‘S’, or ‘O’. Date Became Current must be a valid date greater than or equal to January 1, 1600 and no later than today. Date Ended Current must be a valid date greater than January 1, 1600 and no later than today, or blank. If Owner/Operator Country Code is equal to blank or ‘US’ then Owner/Operator State must be a valid state postal code or blank. Owner/Operator Country Code must be a valid nationally defined value. If Owner/Operator Country Code is blank, a value of ‘US’ will be assumed. HANDLER_ID <> ‘ ’ and SOURCE_TYPE <> ‘ ’ and SEQ_NUMBER <> ‘ ’ HANDLER_ID, SOURCE_TYPE, and SEQ_NUMBER in HD3 must exist in HHANDLER2 or HD2 OWNER_OPERATOR_SEQ > 0 OWNER_OPERATOR_INDICATOR = ‘CO’ or ‘CP’ OWNER_OPERATOR_NAME <> ‘ ’ OWNER_OPERATOR_TYPE = ‘P’ or ‘C’ or ‘D’ or ‘F’ or ‘I’ or ‘M’ or ‘S’ or ‘O’ DATE_BECAME_CURRENT >= ‘16000101’ and DATE_BECAME_CURRENT <= today (DATE_ENDED_CURRENT >= ‘16000101’ and DATE_ENDED_CURRENT <= today) or DATE_ENDED_CURRENT = ‘ ’ If COUNTRY = ‘ ’ or ‘US’ Then STATE = valid state postal code or ‘ ’ COUNTRY = nationally defined country code in LU_COUNTRY Select Logic HD3-080 HD3-090 June 2005 A-19 RCRAInfo Translator Guide-V3 FLAT FILE ID# - HD4 Table Name: HNAICS CDX Staging Table Name: TR_HD4_V2 Description: Handler North American Industry Classification System Information Field No. 1 2 3 4 5 6 7 8 Field Name TRANSACTION_CODE1 HANDLER_ID1 SOURCE_TYPE1 SEQ_NUMBER1 NAICS_SEQ1 **SYSTEM-GENERATED FIELD** NAICS_CODE NOTES Total Record Length: Starting Column 1 2 14 15 21 25 27 33 Field Length 1 12 1 6 4 2 6 240 272 A A Data Type A A A I I Description Transaction Code EPA Handler ID Source Type Handler Sequence Number NAICS Sequence Number HD4-005, HD4-010 HD4-005, HD4-010 HD4-005, HD4-010 HD4-020 Edit Number(s) NAICS Owner – Will be set to ‘HQ’ in the database. NAICS Code Notes HD4-030 1 The fields that must be provided for Delete transactions (TRANSACTION_TYPE = ‘D’). June 2005 A-20 RCRAInfo Translator Guide-V3 HD4 Flat File Edit Specifications Edit Number HD-000 HD4-005 HD4-010 HD4-020 HD4-030 Edit Description Translator is not implementer of record for the Handler Module. Handler ID, Source Type, and Sequence Number must be provided. Handler ID, Source Type, and Sequence Number must exist in HHANDLER2 or HD2. NAICS Sequence Number must be greater than zero. NAICS Code must be a valid nationally defined value registered in the RCRAInfo database. HANDLER_ID <> ‘ ’ and SOURCE_TYPE <> ‘ ’ and SEQ_NUMBER <> ‘ ’ HANDLER_ID, SOURCE_TYPE, and SEQ_NUMBER in HD4 must exist in HHANDLER2 or HD2 NAICS_SEQ > 0 NAICS_CODE = nationally defined NAICS code in LU_NAICS Select Logic June 2005 A-21 RCRAInfo Translator Guide-V3 FLAT FILE ID# - HD5 Table Name: HOTHER_PERMIT2 CDX Staging Table Name: TR_HD5_V2 Description: Handler Other Environmental Permits Information Field No. 1 2 3 4 5 6 7 8 9 Field Name TRANSACTION_CODE1 HANDLER_ID1 SOURCE_TYPE1 SEQ_NUMBER1 OTHER_PERMIT_NUMBER1 **SYSTEM-GENERATED FIELD** OTHER_PERMIT_TYPE OTHER_PERMIT_DESC NOTES Total Record Length: Starting Column 1 2 14 15 21 34 36 37 57 Field Length 1 12 1 6 13 2 1 20 240 296 A A A Data Type A A A I A Description Transaction Code EPA Handler ID Source Type Handler Sequence Number Other Permit Number HD5-005, HD5-010 HD5-005, HD5-010, HD5-020 HD5-005, HD5-010 HD5-030 Edit Number(s) Other Permit Type Owner – Will be set to your user agency in the database. Other Permit Type Other Permit Description Notes HD5-040 1 The fields that must be provided for Delete transactions (TRANSACTION_TYPE = ‘D’). June 2005 A-22 RCRAInfo Translator Guide-V3 HD5 Flat File Edit Specifications Edit Number HD-000 HD5-005 HD5-010 HD5-020 HD5-030 HD5-040 Edit Description Translator is not implementer of record for the Handler Module. Handler ID, Source Type, and Sequence Number must be provided. Handler ID, Source Type, and Sequence Number must exist in HHANDLER2 or HD2. Source Type must equal ‘A’. Other Permit Number must be provided. Other Permit Type must be a valid implementer defined value registered with the RCRAInfo database. HANDLER_ID <> ‘ ’ and SOURCE_TYPE <> ‘ ’ and SEQ_NUMBER <> ‘ ’ HANDLER_ID, SOURCE_TYPE, and SEQ_NUMBER in HD5 must exist in HHANDLER2 or HD2 SOURCE_TYPE = ‘A’ OTHER_PERMIT_NUMBER <> ‘ ’ OTHER_PERMIT_TYPE = implementer defined other permit type in LU_OTHER_PERMIT Select Logic June 2005 A-23 RCRAInfo Translator Guide-V3 FLAT FILE ID# - HD6 Table Name: HWASTE_CODE CDX Staging Table Name: TR_HD6_V2 Description: Handler Waste Code Information Field No. 1 2 3 4 5 Field Name TRANSACTION_CODE1 HANDLER_ID1 SOURCE_TYPE1 SEQ_NUMBER1 **SYSTEM-GENERATED FIELD** Starting Column 1 2 14 15 21 Field Length 1 12 1 6 2 Data Type A A A I Description Transaction Code EPA Handler ID Source Type Handler Sequence Number HD6-005, HD6-010 HD6-005, HD6-010 HD6-005, HD6-010 Edit Number(s) Waste Code Owner – If Waste Code is nationally defined, will be set to ‘HQ’ in the database. If Waste Code is implementer defined, will be set to user agency in the database. A Waste Code HD6-020 6 WASTE_CODE1 Total Record Length: 23 6 28 1 The fields that must be provided for Delete transactions (TRANSACTION_TYPE = ‘D’). June 2005 A-24 RCRAInfo Translator Guide-V3 HD6 Flat File Edit Specifications Edit Number HD-000 HD6-005 HD6-010 HD6-020 Edit Description Translator is not implementer of record for the Handler Module. Handler ID, Source Type, and Sequence Number must be provided. Handler ID, Source Type, and Sequence Number must exist in HHANDLER2 or HD2. Waste Code must be a valid nationally or implementer defined value registered with the RCRAInfo database. HANDLER_ID <> ‘ ’ and SOURCE_TYPE <> ‘ ’ and SEQ_NUMBER <> ‘ ’ HANDLER_ID, SOURCE_TYPE, and SEQ_NUMBER in HD6 must exist in HHANDLER2 or HD2 WASTE_CODE = nationally or implementer defined waste code in LU_WASTE_CODE Select Logic June 2005 A-25 RCRAInfo Translator Guide-V3 FLAT FILE ID# - HD7 Table Name: HUNIVERSAL_WASTE CDX Staging Table Name: TR_HD7_V2 Description: Handler Universal Waste Information Field No. 1 2 3 4 5 Field Name TRANSACTION_CODE1 HANDLER_ID1 SOURCE_TYPE1 SEQ_NUMBER1 **SYSTEM-GENERATED FIELD** Starting Column 1 2 14 15 21 Field Length 1 12 1 6 2 Data Type A A A I Description Transaction Code EPA Handler ID Source Type Handler Sequence Number HD7-005, HD7-010 HD7-005, HD7-010 HD7-005, HD7-010 Edit Number(s) Universal Waste Type Owner – If Universal Waste Type is nationally defined, will be set to ‘US’ in the database. If Universal Waste Type is implementer defined, will be set to user agency in the database. A A A A Universal Waste Type Accumulated Generated Notes HD7-020 HD7-030, HD7-060 HD7-040, HD7-050 6 7 8 9 UNIVERSAL_WASTE_TYPE1 ACCUMULATED GENERATED NOTES Total Record Length: 23 24 25 26 1 1 1 240 265 1 The fields that must be provided for Delete transactions (TRANSACTION_TYPE = ‘D’). June 2005 A-26 RCRAInfo Translator Guide-V3 HD7 Flat File Edit Specifications Edit Number HD-000 HD7-005 HD7-010 HD7-020 HD7-030 HD7-040 HD7-050 HD7-060 Edit Description Translator is not implementer of record for the Handler Module. Handler ID, Source Type, and Sequence Number must be provided. Handler ID, Source Type, and Sequence Number must exist in HHANDLER2 or HD2. Universal Waste Type must be a valid nationally or implementer defined value registered with the RCRAInfo database. Accumulated must equal ‘Y’ or ‘N’. Generated must equal ‘Y’ or ‘N’. If Accumulated is equal to ‘N’ then Generated must equal ‘Y’. If Generated is equal to ‘N’ then Accumulated must equal ‘Y’. HANDLER_ID <> ‘ ’ and SOURCE_TYPE <> ‘ ’ and SEQ_NUMBER <> ‘ ’ HANDLER_ID, SOURCE_TYPE, and SEQ_NUMBER in HD7 must exist in HHANDLER2 or HD2 UNIVERSAL_WASTE_TYPE = nationally or implementer defined universal waste type in LU_UNIVERSAL_WASTE ACCUMULATED = ‘Y’ or ‘N’ GENERATED = ‘Y’ or ‘N’ If ACCUMULATED = ‘N’ Then GENERATED = ‘Y’ If GENERATED = ‘N’ Then ACCUMULATED = ‘Y’ Select Logic June 2005 A-27 RCRAInfo Translator Guide-V3 FLAT FILE ID# - HD8 Table Name: HCERTIFICATION CDX Staging Table Name: TR_HD8_V2 Description: Handler Certification Information Field No. 1 2 3 4 5 6 7 8 9 10 11 Field Name TRANSACTION_CODE1 HANDLER_ID1 SOURCE_TYPE1 SEQ_NUMBER1 CERT_SEQ1 CERT_SIGNED_DATE CERT_TITLE CERT_FIRST_NAME CERT_MIDDLE_INITIAL CERT_LAST_NAME NOTES Total Record Length: Starting Column 1 2 14 15 21 29 37 82 97 98 113 Field Length 1 12 1 6 8 8 45 15 1 15 240 352 Data Type A A A I I Date A A A A A Description Transaction Code EPA Handler ID Source Type Handler Sequence Number Certification Sequence Number Certification Signed Date Certification Title Certification First Name Certification Middle Initial Certification Last Name Notes HD8-060 HD8-005, HD8-010 HD8-005, HD8-010 HD8-005, HD8-010 HD8-020 HD8-030 HD8-040 HD8-050 Edit Number(s) 1 The fields that must be provided for Delete transactions (TRANSACTION_TYPE = ‘D’). June 2005 A-28 RCRAInfo Translator Guide-V3 HD8 Flat File Edit Specifications Edit Number HD-000 HD8-005 HD8-010 HD8-020 HD8-030 HD8-040 HD8-050 HD8-060 Edit Description Translator is not implementer of record for the Handler Module. Handler ID, Source Type, and Sequence Number must be provided. Handler ID, Source Type, and Sequence Number must exist in HHANDLER2 or HD2. Certification Sequence Number must be greater than zero. Certification Signed Date must be a valid date greater than or equal to January 1, 1980 and no later than today. Certification Title must be provided. Certification First Name must be provided. Certification Last Name must be provided. HANDLER_ID <> ‘ ’ and SOURCE_TYPE <> ‘ ’ and SEQ_NUMBER <> ‘ ’ HANDLER_ID, SOURCE_TYPE, and SEQ_NUMBER in HD8 must exist in HHANDLER2 or HD2 CERT_SEQ > 0 CERT_SIGNED_DATE >= ‘19800101’ and CERT_SIGNED_DATE <= today CERT_TITLE <> ‘ ’ CERT_FIRST_NAME <> ‘ ’ CERT_LAST_NAME <> ‘ ’ Select Logic June 2005 A-29 RCRAInfo Translator Guide-V3 FLAT FILE ID# - HD9 Table Name: HSTATE_ACTIVITY CDX Staging Table Name: TR_HD9_V2 Description: Handler State Activity Information Field No. 1 2 3 4 5 6 7 Field Name TRANSACTION_CODE1 HANDLER_ID1 SOURCE_TYPE1 SEQ_NUMBER1 **SYSTEM-GENERATED FIELD** STATE_ACTIVITY_TYPE1 NOTES Total Record Length: Starting Column 1 2 14 15 21 23 28 Field Length 1 12 1 6 2 5 2000 2027 A A Data Type A A A I Description Transaction Code EPA Handler ID Source Type Handler Sequence Number HD9-005, HD9-010 HD9-005, HD9-010 HD9-005, HD9-010 Edit Number(s) State Activity Type Owner – Will be set to your user agency in the database. State Activity Type Notes HD9-020 1 The fields that must be provided for Delete transactions (TRANSACTION_TYPE = ‘D’). June 2005 A-30 RCRAInfo Translator Guide-V3 HD9 Flat File Edit Specifications Edit Number HD-000 HD9-005 HD9-010 HD9-020 Edit Description Translator is not implementer of record for the Handler Module. Handler ID, Source Type, and Sequence Number must be provided. Handler ID, Source Type, and Sequence Number must exist in HHANDLER2 or HD2. State Activity Type must be a valid implementer defined value registered with the RCRAInfo database. HANDLER_ID <> ‘ ’ and SOURCE_TYPE <> ‘ ’ and SEQ_NUMBER <> ‘ ’ HANDLER_ID, SOURCE_TYPE, and SEQ_NUMBER in HD9 must exist in HHANDLER2 or HD2 STATE_ACTIVITY_TYPE = implementer defined activity type in LU_STATE_ACTIVITY Select Logic June 2005 A-31 RCRAInfo Translator Guide-V3 FLAT FILE ID# - HDA Table Name: HPREVIOUS_ID CDX Staging Table Name: TR_HDA_V2 Description: Handler ID History Information Field No. 1 2 3 4 Field Name TRANSACTION_CODE1 HANDLER_ID1 PREVIOUS_ID1 NOTES Total Record Length: Starting Column 1 2 14 26 Field Length 1 12 12 480 505 Data Type A A A A Description Transaction Code EPA Handler ID Previous EPA Handler ID Notes HDA-005, HDA-010 HDA-020 Edit Number(s) 1 The fields that must be provided for Delete transactions (TRANSACTION_TYPE = ‘D’). June 2005 A-32 RCRAInfo Translator Guide-V3 HDA Flat File Edit Specifications Edit Number HD-000 HDA-005 HDA-010 HDA-020 Edit Description Translator is not implementer of record for the Handler Module. Handler ID must be provided. Handler ID must exist in HBASIC or HD1. Previous Handler ID must be provided. HANDLER_ID <> ‘ ’ HANDLER_ID must exist in HBASIC or HD1 PREVIOUS_ID <> ‘ ’ Select Logic June 2005 A-33 RCRAInfo Translator Guide-V3 FLAT FILE ID# - PM1 Table Name: PSERIES CDX Staging Table Name: TR_PM1_V2 Description: Permit Series Data Field No. 1 2 3 4 5 6 7 8 9 10 Field Name TRANSACTION_CODE1 FK_HBASICHANDLER_ID1 SERIES_SEQ1 SERIES_NAME **SYSTEM-GENERATED FIELD** FK_LU_STAFF_EPAPERSON_ID **SYSTEM-GENERATED FIELD** FK_LU_STAFF_STATEPERSON_ID **SYSTEM-GENERATED FIELD** FK_LU_STAFF_EPAATTYPERSON_ID Starting Column 1 2 14 18 30 32 37 39 44 46 Field Length 1 12 4 12 2 5 2 5 2 5 A A A Data Type A A I A Description Transaction Code EPA Handler ID Series Sequence Number Series Name PM1-010 PM1-020 PM1-030 Edit Number(s) EPA Responsible Person Owner – Will be set to your user agency’s EPA Region in the database. EPA Responsible Person ID PM1-040 State Responsible Person Owner – Will be set to your activity location in the database. State Responsible Person ID PM1-050 EPA Attorney ID Owner – Will be set to your user agency’s EPA Region in the database. EPA Attorney ID PM1-060 1 The fields that must be provided for Delete transactions (TRANSACTION_TYPE = ‘D’). June 2005 A-34 RCRAInfo Translator Guide-V3 FLAT FILE ID# - PM1 Table Name: PSERIES CDX Staging Table Name: TR_PM1_V2 Description: Permit Series Data Field No. 11 12 13 Field Name **SYSTEM-GENERATED FIELD** FK_LU_STAFF_STATEATTYPERSON_ID Starting Column 51 53 58 Field Length 2 5 240 297 Data Type Description Edit Number(s) State Attorney ID Owner – Will be set to your activity location in the database. A A State Attorney ID Notes PM1-070 NOTES Total Record Length: June 2005 A-35 RCRAInfo Translator Guide-V3 PM1 Flat File Edit Specifications Edit Number PM1-000 PM1-010 PM1-020 PM1-030 PM1-040 PM1-050 PM1-060 PM1-070 Edit Description Translator is not implementer of record for Permit Series Information for this agency. Handler ID must exist in HBASIC or HD1. Series Sequence Number must be greater than zero. Series Name must be provided. EPA Responsible Person ID must be a valid implementer defined value registered with the RCRAInfo database or blank. State Responsible Person ID must be a valid implementer defined value registered with the RCRAInfo database or blank. EPA Attorney ID must be a valid implementer defined value registered with the RCRAInfo database or blank. State Attorney ID must be a valid implementer defined value registered with the RCRAInfo database or blank. FK_HBASICHANDLER_ID in PM1 must exist in HBASIC or HD1 SERIES_SEQ > 0 SERIES_NAME <> ‘ ’ FK_LU_STAFF_EPAPERSON_ID = implementer defined person identifier in LU_STAFF or ‘ ’ FK_LU_STAFF_STATEPERSON_ID = implementer defined person identifier in LU_STAFF or ‘ ’ FK_LU_STAFF_EPAATTYPERSON_ID = implementer defined person identifier in LU_STAFF or ‘ ’ FK_LU_STAFF_STATEATTYPERSON_ID = implementer defined person identifier in LU_STAFF or ‘ ’ Select Logic June 2005 A-36 RCRAInfo Translator Guide-V3 FLAT FILE ID# - PM2 Table Name: PEVENT CDX Staging Table Name: TR_PM2_V2 Description: Permit Event Data Field No. 1 2 3 4 Field Name TRANSACTION_CODE 1 1 Starting Column 1 2 14 18 Field Length 1 12 4 2 Data Type A A I Description Transaction Code EPA Handler ID Series Sequence Number Edit Number(s) FK_PSERIESFK_HBASICHANDLER_ID FK_PSERIESSERIES_SEQ1 PM2-005, PM2-010 PM2-005, PM2-010 **SYSTEM-GENERATED FIELD** Permit Event Code Owner – If Permit Event Code is nationally defined and nationally required, will be set to ‘HQ’ in the database. If Permit Event Code is nationally defined and not nationally required, will be set to ‘US’ in the database. If Permit Event Code is implementer defined, will be set to your user agency in the database. A A I Date Date Date Permit Event Code Responsible Agency Event Sequence Number Actual Date of Event Original Scheduled Event Date New Scheduled Event Date PM2-020 PM2-030 PM2-040 PM2-050, PM2-080 PM2-060, PM2-090 PM2-070 5 6 7 8 9 10 11 12 1 FK_LU_PERMIT_EVPERMIT_EVENT_CO1 RESPONSIBLE_AGENCY1 EVENT_SEQ 1 20 27 28 31 39 47 55 57 7 1 3 8 8 8 2 5 ACTUAL_DATE SCHEDULE_DATE_ORIG SCHEDULE_DATE_NEW **SYSTEM-GENERATED FIELD** FK_LU_STAFFPERSON_IDENTIFIER Event Responsible Person Owner – Will be set to your user agency in the database. A Event Responsible Person ID PM2-100 The fields that must be provided for Delete transactions (TRANSACTION_TYPE = ‘D’). June 2005 A-37 RCRAInfo Translator Guide-V3 FLAT FILE ID# - PM2 Table Name: PEVENT CDX Staging Table Name: TR_PM2_V2 Description: Permit Event Data Field No. 13 14 15 Field Name Starting Column 62 64 74 Field Length 2 10 240 313 A A Data Type Description Edit Number(s) **SYSTEM-GENERATED FIELD** FK_LU_SUBORGANISUBORGANIZATION NOTES Total Record Length: Event Responsible Suborganization Owner – Will be set to your user agency in the database. Event Responsible Suborganization Notes PM2-110 June 2005 A-38 RCRAInfo Translator Guide-V3 PM2 Flat File Edit Specifications Edit Number PM2-000 PM2-005 PM2-010 PM2-020 Edit Description Translator is not implementer of record for Permit Event Information for this agency. Handler ID and Series Sequence Number must be provided. Handler ID and Series Sequence Number must exist in PSERIES or PM1. Permit Event Code must be a valid nationally or implementer defined value registered with the RCRAInfo database. Responsible Agency must equal ‘E’, ‘S’, or ‘J’. Event Sequence Number must be greater than zero. Actual Date must be a valid date greater than or equal to January 1, 1980 and no later than today, or blank. Original Scheduled Event Date must be a valid date greater than or equal to May 18, 1980 or blank. New Scheduled Event Date must be a valid date greater than or equal to January 1, 1980 or blank. If Original Scheduled Event Date is blank then Actual Date must be provided. If Actual Date is blank then Original Scheduled Event Date must be provided. FK_PSERIESFK_HBASICHANDLER_ID <> ‘ ’ and FK_PSERIESSERIES_SEQ <> ‘ ’ FK_PSERIESFK_HBASICHANDLER_ID and FK_PSERIESSERIES_SEQ in PM2 must exist in PSERIES or PM1 FK_LU_PERMIT_EVPERMIT_EVENT_CO = nationally or implementer defined permit event code in LU_PERMIT_EVENT_CODE RESPONSIBLE_AGENCY = ‘E’ or ‘S’ or ‘J’ EVENT_SEQ > 0 (ACTUAL_DATE >= ‘19800101’ and ACTUAL_DATE <= today) or ACTUAL_DATE = ‘ ’ SCHEDULE_DATE_ORIG >= ‘19800518’ or ‘ ’ SCHEDULE_DATE_NEW >= ‘19800101’ or ‘ ’ If SCHEDULE_DATE_ORIG = ‘ ’ Then ACTUAL_DATE <> ‘ ’ If ACTUAL_DATE = ‘ ’ Then SCHEDULE_DATE_ORIG <> ‘ ’ Select Logic PM2-030 PM2-040 PM2-050 PM2-060 PM2-070 PM2-080 PM2-090 June 2005 A-39 RCRAInfo Translator Guide-V3 PM2 Flat File Edit Specifications Edit Number PM2-100 PM2-110 Edit Description Event Responsible Person ID must be a valid implementer defined value registered with the RCRAInfo database or blank. Event Responsible Suborganization must be a valid implementer defined value registered with the RCRAInfo database or blank. Select Logic FK_LU_STAFFPERSON_IDENTIFIER = implementer defined person identifier in LU_STAFF or ‘ ’ FK_LU_SUBORGANISUBORGANIZATION = implementer defined suborganization in LU_SUBORGANIZATION or ‘ ’ June 2005 A-40 RCRAInfo Translator Guide-V3 FLAT FILE ID# - PM3 Table Name: PUNIT CDX Staging Table Name: TR_PM3_V2 Description: Permit Unit Data Field No. 1 2 3 4 5 Field Name TRANSACTION_CODE1 FK_HBASICHANDLER_ID1 UNIT_SEQ1 UNIT_NAME NOTES Total Record Length: Starting Column 1 2 14 18 36 Field Length 1 12 4 18 240 275 Data Type A A I A A Description Transaction Code EPA Handler ID Process Unit Sequence Number Process Unit Name Notes PM3-005, PM3-010 PM3-020 PM3-030 Edit Number(s) 1 The fields that must be provided for Delete transactions (TRANSACTION_TYPE = ‘D’). June 2005 A-41 RCRAInfo Translator Guide-V3 PM3 Flat File Edit Specifications Edit Number PM3-000 PM3-005 PM3-010 PM3-020 PM3-030 Edit Description Translator is not implementer of record for Permit Unit Information for this agency. Handler ID must be provided. Handler ID must exist in HBASIC or HD1. Process Unit Sequence Number must be greater than zero. Process Unit Name must be provided. FK_HBASICHANDLER_ID <> ‘ ’ FK_HBASICHANDLER_ID in PM3 must exist in HBASIC or HD1 UNIT_SEQ > 0 UNIT_NAME <> ‘ ’ Select Logic June 2005 A-42 RCRAInfo Translator Guide-V3 FLAT FILE ID# - PM4 Table Name: PUNIT_DETAIL CDX Staging Table Name: TR_PM4_V2 Description: Permit Unit Group Detail Data Field No. 1 2 3 4 5 6 7 8 9 10 11 12 13 Field Name TRANSACTION_CODE1 FK_PUNITFK_HBASICHANDLER_ID1 FK_PUNITUNIT_SEQ1 UNIT_DETAIL_SEQ1 **SYSTEM-GENERATED FIELD** FK_LU_PROCESS_CPROCESS_CODE **SYSTEM-GENERATED FIELD** FK_LU_PROCESS_CFK_LU_UOMTYPE EFFECTIVE_DATE CAPACITY **SYSTEM-GENERATED FIELD** FK_LU_CAPACITY_CAPACITY_TYPE NUMBER_OF_UNITS Starting Column 1 2 14 18 21 23 26 28 29 37 53 55 56 Field Length 1 12 4 3 2 3 2 1 8 16 2 1 7 A I A Date D15.3 A Data Type A A I I Description Transaction Code EPA Handler ID Process Unit Sequence Number Process Unit Detail Sequence Number PM4-005, PM4-010 PM4-005, PM4-010 PM4-020 Edit Number(s) Process Code Owner – Will be set to ‘HQ’ in the database. Process Code PM4-030 Unit of Measure Owner – Will be set to ‘HQ’ in the database. Unit of Measure Process Status Effective Date Process Capacity PM4-030 PM4-040 PM4-050 Capacity Type Owner – Will be set to ‘US’ in the database. Capacity Type Number of Units within Process Unit Group PM4-060 PM4-070 1 The fields that must be provided for Delete transactions (TRANSACTION_TYPE = ‘D’). June 2005 A-43 RCRAInfo Translator Guide-V3 FLAT FILE ID# - PM4 Table Name: PUNIT_DETAIL CDX Staging Table Name: TR_PM4_V2 Description: Permit Unit Group Detail Data Field No. 14 15 16 17 18 19 20 Field Name **SYSTEM-GENERATED FIELD** FK_LU_COMMERCIACOMMERCIAL_STAT Starting Column 63 65 66 68 72 74 77 Field Length 2 1 2 4 2 3 240 316 Data Type A A Description Edit Number(s) Commercial Status Owner – Will be set to ‘US’ in the database. Commercial Status PM4-080 **SYSTEM-GENERATED FIELD** FK_LU_LEGAL_OPELEG_OP_STATUS_C Legal/Operating Status Owner – Will be set to ‘HQ’ in the database. A Legal/Operating Status PM4-090 **OBSOLETE FIELD** **OBSOLETE FIELD** NOTES Total Record Length: A Notes June 2005 A-44 RCRAInfo Translator Guide-V3 PM4 Flat File Edit Specifications Edit Number PM4-000 PM4-005 PM4-010 PM4-020 PM4-030 Edit Description Translator is not implementer of record for Permit Unit Detail Information for this agency. Handler ID and Process Unit Sequence Number must be provided. Handler ID and Process Unit Sequence Number must exist in PUNIT or PM3. Process Unit Detail Sequence Number must be greater than zero. Process Code / Unit of Measure must be a valid nationally defined value registered in the RCRAInfo database. Process Status Effective Date must be greater than or equal to January 1, 1980 and no later than today. Process Capacity must be greater than zero. Capacity Type must be a valid nationally defined value registered in the RCRAInfo database. Number of Units must be greater than zero. Commercial Status must be a valid nationally defined value registered in the RCRAInfo database or blank. Legal/Operating Status must be a valid nationally defined value registered in the RCRAInfo database. FK_PUNITFK_HBASICHANDLER_ID <> ‘ ’ and FK_PUNITUNIT_SEQ <> ‘ ’ FK_PUNITFK_HBASICHANDLER_ID and FK_PUNITUNIT_SEQ in PM4 must exist in PUNIT or PM3 UNIT_DETAIL_SEQ > 0 FK_LU_PROCESS_CPROCESS_CODE = nationally defined process_code and FK_LU_PROCESS_CFK_LU_UOMTYPE = nationally defined unit of measure in LU_PROCESS_CODE EFFECTIVE_DATE >= ‘19800101’ and EFFECTIVE_DATE <= today CAPACITY > 0 FK_LU_CAPACITY_CAPACITY_TYPE = nationally defined capacity type in LU_CAPACITY_TYPE NUMBER_OF_UNITS > 0 FK_LU_COMMERCIACOMMERCIAL_STAT = nationally defined commercial status code in LU_COMMERCIAL_STATUS or ‘ ’ FK_LU_LEGAL_OPELEG_OP_STATUS_C = nationally defined legal/operating status code in LU_LEGAL_OPERATING_STATUS Select Logic PM4-040 PM4-050 PM4-060 PM4-070 PM4-080 PM4-090 June 2005 A-45 RCRAInfo Translator Guide-V3 FLAT FILE ID# - PM5 Table Name: PLN_EVENT_UNIT CDX Staging Table Name: TR_PM5_V2 Description: Linking Data for Permit Event and Unit Group Detail Field No. 1 2 3 4 Field Name TRANSACTION_CODE1 HANDLER_ID1 FK_PEVENTFK_PSERIESSERIES_SEQ1 **SYSTEM-GENERATED FIELD** Starting Column 1 2 14 18 Field Length 1 12 4 2 Data Type A A I Description Transaction Code EPA Handler ID Series Sequence Number PM5-005, PM5-010, PM5-020, PM5-040 PM5-005, PM5-010 Edit Number(s) Permit Event Code Owner – If Permit Event Code is nationally defined and nationally required, will be set to ‘HQ’ in the database. If Permit Event Code is nationally defined and not nationally required, will be set to ‘US’ in the database. If Permit Event Code is implementer defined, will be set to your user agency in the database. 5 6 7 8 9 FK_PEVENTFK_LU_PERMIT_EVPERMIT1 20 27 28 31 7 1 3 4 3 37 A A I I I Permit Event Code Event Responsible Agency Event Sequence Number Process Unit Sequence Number Process Unit Detail Sequence Number PM5-005, PM5-010 PM5-005, PM5-010 PM5-005, PM5-010 PM5-005, PM5-020, PM5-040 PM5-030, PM5-040 FK_PEVENTRESPONSIBLE_AGENCY1 FK_PEVENTEVENT_SEQ1 FK_PUNITUNIT_SEQ1 FK_PUNIT_DETAILUNIT_DETAIL_SEQ 1 35 Total Record Length: 1 The fields that must be provided for Delete transactions (TRANSACTION_TYPE = ‘D’). June 2005 A-46 RCRAInfo Translator Guide-V3 PM5 Flat File Edit Specifications Edit Number PM5-005 Edit Description Handler ID, Series Sequence Number, Permit Event Code, Event Responsible Agency, Event Sequence Number, and Process Unit Sequence Number must be provided. Select Logic FK_PEVENTFK_PSERIESFK_HBASICHA <> ‘ ’ and FK_PEVENTFK_PSERIESSERIES_SEQ <> ‘ ’ and FK_PEVENTFK_LU_PERMIT_EVPERMIT <> ‘ ’ and FK_PEVENTRESPONSIBLE_AGENCY <> ‘ ’ and FK_PEVENTEVENT_SEQ <> ‘ ’ and FK_PUNITUNIT_SEQ <> ‘ ’ FK_PEVENTFK_PSERIESFK_HBASICHA, FK_PEVENTFK_PSERIESSERIES_SEQ, FK_PEVENTFK_LU_PERMIT_EVPERMIT, FK_PEVENTRESPONSIBLE_AGENCY, FK_PEVENTEVENT_SEQ in PM5 must exist in PEVENT or PM2 FK_PEVENTFK_PSERIESFK_HBASICHA and FK_PUNITUNIT_SEQ in PM5 must exist in PUNIT or PM3 FK_PUNIT_DETAILUNIT_DETAIL_SEQ <> ‘ ’ FK_PEVENTFK_PSERIESFK_HBASICHA, FK_PUNITUNIT_SEQ, and FK_PUNIT_DETAILUNIT_DETAIL_SEQ in PM5 must exist in PUNIT_DETAIL or PM4 PM5-010 Handler ID, Series Sequence Number, Permit Event Code, Event Responsible Agency, and Event Sequence Number must exist in PEVENT or PM2. PM5-020 PM5-030 PM5-040 Handler ID and Process Unit Sequence Number must exist in PUNIT or PM3. Process Unit Detail Sequence Number must be provided. Handler ID, Process Unit Sequence Number, and Process Unit Detail Sequence Number must exist in PUNIT_DETAIL or PM4. June 2005 A-47 RCRAInfo Translator Guide-V3 FLAT FILE ID# - PM6 Table Name: PUNIT_DETAIL_WASTE CDX Staging Table Name: TR_PM6_V2 Description: Linking Data for Permit Unit Detail and Waste Code Field No. 1 2 3 4 5 Field Name TRANSACTION_CODE1 HANDLER_ID1 UNIT_SEQ1 UNIT_DETAIL_SEQ1 **SYSTEM-GENERATED FIELD** Starting Column 1 2 14 18 21 Field Length 1 12 4 3 2 Data Type A A I I Description Transaction Code EPA Handler ID Process Unit Sequence Number Process Unit Detail Sequence Number PM6-005, PM6-010 PM6-005, PM6-010 PM6-005, PM6-010 Edit Number(s) Waste Code Owner – If Waste Code is nationally defined, will be set to ‘HQ’ in the database. If Waste Code is implementer defined, will be set to your user agency in the database. A D15.5 A Waste Code Estimated Quantity Unit of Measure Type PM6-020 PM6-030 PM6-040 6 7 WASTE_CODE1 ESTIMATED_QUANTITY UOM_TYPE Total Record Length: 23 29 45 6 16 1 45 1 The fields that must be provided for Delete transactions (TRANSACTION_TYPE = ‘D’). June 2005 A-48 RCRAInfo Translator Guide-V3 PM6 Flat File Edit Specifications Edit Number PM6-005 PM6-010 PM6-020 PM6-030 PM6-040 Edit Description Handler ID, Process Unit Sequence Number, and Process Unit Detail Sequence Number must be provided. Handler ID, Process Unit Sequence Number, and Process Unit Detail Sequence Number must exist in PUNIT_DETAIL or PM4. Waste Code must be a valid nationally or implementer defined value registered in the RCRAInfo database. Estimated Quantity must be greater than zero. Unit of Measure Type must be a valid nationally defined value registered in the RCRAInfo database. Select Logic HANDLER_ID <> ‘ ’ and UNIT_SEQ <> ‘ ’ and UNIT_DETAIL_SEQ <> ‘ ’ HANDLER_ID, UNIT_SEQ, and UNIT_DETAIL_SEQ in PM6 must exist in PUNIT_DETAIL or PM4 WASTE_CODE = nationally or implementer defined waste code in LU_WASTE_CODE ESTIMATED_QUANTITY > 0 UOM_TYPE = nationally defined unit of measure in LU_UNIT_OF_MEASURE June 2005 A-49 RCRAInfo Translator Guide-V3 FLAT FILE ID# - CA1 Table Name: AAREA CDX Staging Table Name: TR_CA1_V2 Description: Corrective Action Area Data Field No. 1 2 3 4 5 6 7 8 9 10 11 12 13 Field Name TRANSACTION_CODE 1 Starting Column 1 2 14 18 19 59 60 61 62 63 64 66 71 Field Length 1 12 4 1 40 1 1 1 1 1 2 5 2 Data Type A A I A A A A A A A Description Transaction Code EPA Handler ID Area Sequence Number Area Facility-Wide Indicator Area Name Air Release Indicator Groundwater Release Indicator Soil Release Indicator Surface Water Release Indicator Regulated Unit Indicator Edit Number(s) FK_HBASICHANDLER_ID1 AREA_SEQ 1 CA1-005, CA1-010 CA1-020 CA1-030, CA1-040 CA1-050 CA1-060 CA1-070 CA1-080 CA1-090 CA1-100 AREA_FACILITYWIDE_IND AREA_NAME AIR_RELEASE_IND GROUNDWATER_RELEASE_IND SOIL_RELEASE_IND SURFACE_WATER_RELEASE_IND REGULATED_UNIT_IND **SYSTEM-GENERATED FIELD** FK_LU_STAFF_EPAPERSON_ID **SYSTEM-GENERATED FIELD** EPA Responsible Person Owner – Will be set to your user agency’s EPA Region in the database. A EPA Responsible Person ID CA1-110 State Responsible Person Owner – Will be set to your activity location in the database. 1 The fields that must be provided for Delete transactions (TRANSACTION_TYPE = ‘D’). June 2005 A-50 RCRAInfo Translator Guide-V3 FLAT FILE ID# - CA1 Table Name: AAREA CDX Staging Table Name: TR_CA1_V2 Description: Corrective Action Area Data Field No. 14 15 Field Name FK_LU_STAFF_STATEPERSON_ID NOTES Total Record Length: Starting Column 73 78 Field Length 5 240 317 Data Type A A Description State Responsible Person ID Notes Edit Number(s) CA1-120 June 2005 A-51 RCRAInfo Translator Guide-V3 CA1 Flat File Edit Specifications Edit Number CA1-000 CA1-005 CA1-010 CA1-020 CA1-030 CA1-040 Edit Description Translator is not implementer of record for Corrective Action Area Information for this agency. Handler ID must be provided. Handler ID must exist in HBASIC or HD1. Area Sequence Number must be greater than zero. Area Facility-Wide Indicator must be ‘Y’ or ‘N’. There cannot be more than one record for a facility where Area Facility-Wide Indicator is equal to ‘Y’. Area Name must be provided. Air Release Indicator must be ‘Y’, ‘N’, or blank. Groundwater Release Indicator must be ‘Y’, ‘N’, or blank. Soil Release Indicator must be ‘Y’, ‘N’, or blank. Surface Water Release Indicator must be ‘Y’, ‘N’, or blank. Regulated Unit Indicator must be ‘Y’, ‘N’, or ‘U’. EPA Responsible Person ID must be a valid implementer defined value registered with the RCRAInfo database or blank. State Responsible Person must be a valid implementer defined value registered with the RCRAInfo database or blank. FK_HBASICHANDLER_ID <> ‘ ’ FK_HBASICHANDLER_ID in CA1 must exist in HBASIC or HD1 AREA_SEQ > 0 AREA_FACILITYWIDE_IND = ‘Y’ or ‘N’ If AREA_FACILITYWIDE_IND = ‘Y’ Then there cannot be another record for FK_HBASICHANDLER_ID where AREA_FACILITYWIDE_IND = ‘Y’ AREA_NAME <> ‘ ’ AIR_RELEASE_IND = ‘Y’ or ‘N’ or ‘ ’ GROUNDWATER_RELEASE_IND = ‘Y’ or ‘N’ or ‘ ’ SOIL_RELEASE_IND = ‘Y’ or ‘N’ or ‘ ’ SURFACE_WATER_RELEASE_IND = ‘Y’ or ‘N’ or ‘ ’ REGULATED_UNIT_IND = ‘Y’ or ‘N’ or ‘U’ FK_LU_STAFF_EPAPERSON_ID = implementer defined person identifier in LU_STAFF or ‘ ’ FK_LU_STAFF_STATEPERSON_ID = implementer defined person identifier in LU_STAFF or ‘ ’ Select Logic CA1-050 CA1-060 CA1-070 CA1-080 CA1-090 CA1-100 CA1-110 CA1-120 June 2005 A-52 RCRAInfo Translator Guide-V3 FLAT FILE ID# - CA2 Table Name: ACA_AUTHORITY CDX Staging Table Name: TR_CA2_V2 Description: Corrective Action Authority Data Field No. 1 2 3 4 5 6 7 8 9 10 11 Field Name TRANSACTION_CODE1 FK_HBASICHANDLER_ID1 **SYSTEM-GENERATED FIELD** FK_LU_AUTHORITYAUTHORITY_TYPE1 Starting Column 1 2 14 16 17 18 26 34 42 43 45 Field Length 1 12 2 1 1 8 8 8 1 2 1 Data Type A A Description Transaction Code EPA Handler ID Edit Number(s) CA2-005, CA2-010, CA2-120 Authority Type Owner – Will be set to ‘HQ’ in the database. A A Date Date Date A Authority Type Responsible Agency Effective Date Issue Date Revoke Date Repository Established CA2-020, CA2-120 CA2-030. CA2-120 CA2-040, CA2-120 CA2-050 CA2-060 CA2-070 RESPONSIBLE_AGENCY1 EFFECTIVE_DATE1 ISSUE_DATE REVOKE_DATE REPOSITORY **SYSTEM-GENERATED FIELD** FK_LU_LEAD_PROGLEAD_PROGRAM Responsible Lead Program Owner – Will be set to your user agency in the database. A Responsible Lead Program CA2-080 1 The fields that must be provided for Delete transactions (TRANSACTION_TYPE = ‘D’). June 2005 A-53 RCRAInfo Translator Guide-V3 FLAT FILE ID# - CA2 Table Name: ACA_AUTHORITY CDX Staging Table Name: TR_CA2_V2 Description: Corrective Action Authority Data Field No. 12 13 14 15 16 17 18 Field Name **SYSTEM-GENERATED FIELD** FK_LU_SUBORGANISUBORGANIZATION Starting Column 46 48 58 60 65 67 72 Field Length 2 10 2 5 2 5 240 311 Data Type Description Edit Number(s) Responsible Suborganization Owner – Will be set to your user agency in the database. A Responsible Suborganization CA2-090 **SYSTEM-GENERATED FIELD** FK_LU_STAFFPERSON_IDENTIFIER **SYSTEM-GENERATED FIELD** FK_LU_STAFF_ATTYPERSON_ID NOTES Total Record Length: Responsible Person Owner – Will be set to your user agency in the database. A Responsible Person Identifier CA2-100 Attorney Identifier Owner – Will be set to your user agency in the database. A A Attorney Identifier Notes CA2-110 June 2005 A-54 RCRAInfo Translator Guide-V3 CA2 Flat File Edit Specifications Edit Number CA2-005 CA2-010 CA2-020 CA2-030 CA2-040 CA2-050 CA2-060 CA2-070 CA2-080 CA2-090 Edit Description Handler ID must be provided. Handler ID must exist in HBASIC or HD1. Authority Type must be a valid nationally defined value registered with the RCRAInfo database. Responsible Agency must equal ‘E’, ‘S’, or ‘J’. Effective Date must be a valid date greater than or equal to January 1, 1980. Issue Date must be a valid date greater than or equal to January 1, 1980 or blank. Revoke Date must be a valid date greater than or equal to January 1, 1980 or blank. Repository Established must equal ‘1', ‘2', ‘3', or blank. Responsible Lead Program must be a valid implementer defined value registered with the RCRAInfo database or blank. Responsible Suborganization must be a valid implementer defined value registered with the RCRAInfo database or blank. Select Logic FK_HBASICHANDLER_ID <> ‘ ’ FK_HBASICHANDLER_ID in CA2 must exist in HBASIC or HD1 FK_LU_AUTHORITYAUTHORITY_TYPE = nationally defined authority type in LU_AUTHORITY RESPONSIBLE_AGENCY = ‘E’ or ‘S’ or ‘J’ EFFECTIVE_DATE >= ‘19800101’ ISSUE_DATE >= ‘19800101’ or ‘ ’ REVOKE_DATE >= ‘19800101’ or ‘ ’ REPOSITORY = ‘1’ or ‘2’ or ‘3’ or ‘ ’ FK_LU_LEAD_PROGLEAD_PROGRAM = implementer defined lead program in LU_LEAD_PROGRAM or ‘ ’ FK_LU_SUBORGANISUBORGANIZATION = implementer defined suborganization in LU_SUBORGANIZATION or ‘ ’ June 2005 A-55 RCRAInfo Translator Guide-V3 CA2 Flat File Edit Specifications Edit Number CA2-100 CA2-110 CA2-120 Edit Description Responsible Person ID must be a valid implementer defined value registered with the RCRAInfo database or blank. Attorney ID must be a valid implementer defined value registered with the RCRAInfo database or blank. At least one record must exist in ASTATUTE_CITATION or CA3. Select Logic FK_LU_STAFFPERSON_IDENTIFIER = implementer defined person identifier in LU_STAFF or ‘ ’ FK_LU_STAFF_ATTYPERSON_ID = implementer defined person identifier in LU_STAFF or ‘ ’ FK_HBASICHANDLER_ID, FK_LU_AUTHORITYAUTHORITY_TYPE, RESPONSIBLE_AGENCY, and EFFECTIVE_DATE in CA2 must exist in ASTATUTE_CITATION or CA3 June 2005 A-56 RCRAInfo Translator Guide-V3 FLAT FILE ID# - CA3 Table Name: ASTATUTE_CITATION CDX Staging Table Name: TR_CA3_V2 Description: Corrective Action Statute / Citation Data Field No. 1 2 3 4 5 6 7 8 9 Field Name TRANSACTION_CODE1 FK_ACA_AUTHORITFK_HBASICHANDLE1 Starting Column 1 2 14 16 17 18 26 28 29 Field Length 1 12 2 1 1 8 2 1 240 268 Data Type A A Description Transaction Code EPA Handler ID Edit Number(s) CA3-005, CA3-010 **SYSTEM-GENERATED FIELD** FK_ACA_AUTHORITFK_LU_AUTHTYPE1 FK_ACA_AUTHORITRESPONSIBLE_AGE1 Authority Type Owner - Will be set to ‘HQ’ in the database. A A Date Authority Type Authority Responsible Agency Authority Effective Date CA3-005, CA3-010 CA3-005, CA3-010 CA3-005, CA3-010 FK_ACA_AUTHORITEFFECTIVE_DATE1 **SYSTEM-GENERATED FIELD** FK_LU_STATUTORYSTATUTORY_CITAT Statutory Citation Owner – Will be set to ‘US’ in the database. A A Statutory Citation Notes CA3-020 NOTES Total Record Length: 1 The fields that must be provided for Delete transactions (TRANSACTION_TYPE = ‘D’). June 2005 A-57 RCRAInfo Translator Guide-V3 CA3 Flat File Edit Specifications Edit Number CA3-005 Edit Description Handler ID, Authority Type, Authority Responsible Agency, and Effective Date must be provided. Select Logic FK_ACA_AUTHORITFK_HBASICHANDLE <> ‘ ’ and FK_ACA_AUTHORITFK_LU_AUTHTYPE <> ‘ ’ and FK_ACA_AUTHORITRESPONSIBLE_AGE <> ‘ ’ and FK_ACA_AUTHORITEFFECTIVE_DATE <> ‘ ’ FK_ACA_AUTHORITFK_HBASICHANDLE, FK_ACA_AUTHORITFK_LU_AUTHTYPE, FK_ACA_AUTHORITRESPONSIBLE_AGE, and FK_ACA_AUTHORITEFFECTIVE_DATE in CA3 must exist in ACA_AUTHORITY or CA2 FK_LU_STATUTORTYSTATUTORY_CITAT = nationally defined statutory citation in LU_STATUTORY_CITATION CA3-010 Handler ID, Authority Type, Authority Responsible Agency, and Effective Date must exist in ACA_AUTHORITY or CA2. CA3-020 Statutory Citation must be a valid nationally defined value registered with the RCRAInfo database. June 2005 A-58 RCRAInfo Translator Guide-V3 FLAT FILE ID# - CA4 Table Name: AEVENT CDX Staging Table Name: TR_CA4_V2 Description: Corrective Action Event Data Field No. 1 2 3 Field Name TRANSACTION_CODE 1 Starting Column 1 2 14 Field Length 1 12 2 Data Type A A Description Transaction Code EPA Handler ID Edit Number(s) FK_HBASICHANDLER_ID1 **SYSTEM-GENERATED FIELD** CA4-005, CA4-010, CA4-110, CA4-120 Corrective Action Event Code Owner – If Corrective Action Event Code is nationally defined and nationally required, will be set to ‘HQ’ in the database. If Corrective Action Event Code is nationally defined and not nationally required, will be set to ‘US’ in the database. If Corrective Action Event Code is implementer defined, will be set to your user agency in the database. A A I Date Date Date Corrective Action Event Code Event Responsible Agency Event Sequence Number Actual Date of Event Original Scheduled Event Date New Scheduled Event Date CA4-020, CA4-110, CA4-120 CA4-030, CA4-110, CA4-120 CA4-040, CA4-110, CA4-120 CA4-050, CA4-070 CA4-055, CA4-080 CA4-060 4 5 6 7 8 9 FK_LU_CA_EVENTCA_EVENT_CODE1 RESPONSIBLE_AGENCY1 EVENT_SEQ1 ACTUAL_DATE SCHEDULE_DATE_ORIG SCHEDULE_DATE_NEW 16 23 24 27 35 43 7 1 3 8 8 8 1 The fields that must be provided for Delete transactions (TRANSACTION_TYPE = ‘D’). June 2005 A-59 RCRAInfo Translator Guide-V3 FLAT FILE ID# - CA4 Table Name: AEVENT CDX Staging Table Name: TR_CA4_V2 Description: Corrective Action Event Data Field No. 10 11 12 13 14 Field Name **SYSTEM-GENERATED FIELD** FK_LU_SUBORGANISUBORGANIZATION **SYSTEM-GENERATED FIELD** FK_LU_STAFFPERSON_IDENTIFIER NOTES Total Record Length: Starting Column 51 53 63 65 70 Field Length 2 10 2 5 240 309 A A A Data Type Description Edit Number(s) Event Responsible Suborganization Owner – Will be set to your user agency in the database. Event Responsible Suborganization CA4-090 Event Responsible Person – Will be set to your user agency in the database. Event Responsible Person ID Notes CA4-100 June 2005 A-60 RCRAInfo Translator Guide-V3 CA4 Flat File Edit Specifications Edit Number CA4-000 CA4-005 CA4-010 CA4-020 Edit Description Translator is not implementer of record for Corrective Action Event Information for this agency. Handler ID must be provided. Handler ID must exist in HBASIC or HD1. Corrective Action Event Code must be a valid nationally or implementer defined value registered with the RCRAInfo database. Event Responsible Agency must equal ‘E’, ‘S’, or ‘J’. Event Sequence Number must be greater than zero. Actual Date must be greater than or equal to January 1, 1980 and no later than today, or blank. Original Scheduled Event Date must be a valid date greater than or equal to May 18, 1980 or blank. New Scheduled Event Date must be a valid date greater than or equal to January 1, 1980 or blank. If Original Scheduled Event Date is blank then Actual Date must be provided. If Actual Date is blank then Original Scheduled Event Date must be provided. Event Responsible Suborganization must be a valid implementer defined value registered with the RCRAInfo database or blank. FK_HBASICHANDLER_ID <> ‘ ’ FK_HBASICHANDLER_ID in CA4 must exist in HBASIC or HD1 FK_LU_CA_EVENTCA_EVENT_CODE = nationally or implementer defined CA event code in LU_CA_EVENT RESPONSIBLE_AGENCY = ‘E’ or ‘S’ or ‘J’ EVENT_SEQ > 0 (ACTUAL_DATE >= ‘19800101’ and ACTUAL_DATE <= today) or ACTUAL_DATE = ‘ ’ SCHEDULE_DATE_ORIG >= ‘19800518’ or ‘ ‘ SCHEDULE_DATE_NEW >= ‘19800101’ or ‘ ‘ Select Logic CA4-030 CA4-040 CA4-050 CA4-055 CA4-060 CA4-070 CA4-080 CA4-090 If SCHEDULE_DATE_ORIG = ‘ ’ Then ACTUAL_DATE <> ‘ ’ If ACTUAL_DATE = ‘ ’ Then SCHEDULE_DATE_ORIG <> ‘ ’ FK_LU_SUBORGANISUBORGANIZATION = implementer defined suborganization in LU_SUBORGANIZATION or ‘ ’ June 2005 A-61 RCRAInfo Translator Guide-V3 CA4 Flat File Edit Specifications Edit Number CA4-100 CA4-110 Edit Description Event Responsible Person ID must be a valid implementer defined value registered with the RCRAInfo database or blank. At least one record must exist in ALN_AREA_EVENT or CA5. Select Logic FK_LU_STAFFPERSON_IDENTIFIER = implementer defined person identifier in LU_STAFF or ‘ ’ FK_HBASICHANDLER_ID, FK_LU_CA_EVENTCA_EVENT_CODE, RESPONSIBLE_AGENCY, and EVENT_SEQ in CA4 must exist in ALN_AREA_EVENT or CA5 FK_HBASICHANDLER_ID, FK_LU_CA_EVENTCA_EVENT_CODE, RESPONSIBLE_AGENCY, and EVENT_SEQ in CA4 must exist in ALN_EVENT_AUTHORITY or CA6 CA4-120 At least one record must exist in ALN_EVENT_AUTHORITY or CA6. June 2005 A-62 RCRAInfo Translator Guide-V3 FLAT FILE ID# - CA5 Table Name: ALN_AREA_EVENT CDX Staging Table Name: TR_CA5_V2 Description: Linking Data for CA Event and CA Area Field No. 1 2 3 4 Field Name TRANSACTION_CODE1 HANDLER_ID1 FK_AAREAAREA_SEQ1 **SYSTEM-GENERATED FIELD** Starting Column 1 2 14 18 Field Length 1 12 4 2 Data Type A A I Description Transaction Code EPA Handler ID Area Sequence Number CA5-005, CA5-010, CA5-020 CA5-005, CA5-010 Edit Number(s) Corrective Action Event Code Owner – If Corrective Action Event Code is nationally defined and nationally required, will be set to ‘HQ’ in the database. If Corrective Action Event Code is nationally defined and not nationally required, will be set to ‘US’ in the database. If Corrective Action Event Code is implementer defined, will be set to your user agency in the database. 5 6 7 FK_AEVENTFK_LU_CA_EVENTCA_EVEN1 20 27 28 7 1 3 30 A A I Event Code Event Responsible Agency Event Sequence Number CA5-005, CA5-020 CA5-005, CA5-020 CA5-005, CA5-020 FK_AEVENTRESPONSIBLE_AGENCY1 FK_AEVENTEVENT_SEQ1 Total Record Length: 1 The fields that must be provided for Delete transactions (TRANSACTION_TYPE = ‘D’). June 2005 A-63 RCRAInfo Translator Guide-V3 CA5 Flat File Edit Specifications Edit Number CA5-005 Edit Description Handler ID, Area Sequence Number, Event Code, Event Responsible Agency, and Event Sequence Number must be provided. Select Logic FK_AAREAFK_HBASICHANDLER_ID <> ‘ ’ and FK_AAREAAREA_SEQ <> ‘ ’ and FK_AEVENTFK_LU_CA_EVENTCA_EVEN <> ‘ ’ and FK_AEVENTRESPONSIBLE_AGENCY <> ‘ ’ and FK_AEVENTEVENT_SEQ <> ‘ ’ FK_AAREAFK_HBASICHANDLER_ID and FK_AAREAAREA_SEQ in CA5 must exist in AAREA or CA1 FK_AEVENTFK_HBASICHANDLER_ID, FK_AEVENTFK_LU_CA_EVENTCA_EVEN, FK_AEVENTRESPONSIBLE_AGENCY, and FK_AEVENTEVENT_SEQ in CA5 must exist in AEVENT or CA4 CA5-010 CA5-020 Handler ID and Area Sequence Number must exist in AAREA or CA1. Handler ID, Event Code, Event Responsible Agency, and Event Sequence Number must exist in AEVENT or CA4. June 2005 A-64 RCRAInfo Translator Guide-V3 FLAT FILE ID# - CA6 Table Name: ALN_EVENT_AUTHORITY CDX Staging Table Name: TR_CA6_V2 Description: Linking Data for CA Event and CA Authority Field No. 1 2 3 Field Name TRANSACTION_CODE1 HANDLER_ID1 Starting Column 1 2 14 Field Length 1 12 2 Data Type A A Description Transaction Code EPA Handler ID Edit Number(s) CA6-005, CA6-010, CA6-020 **SYSTEM-GENERATED FIELD** Corrective Action Event Code Owner – If Corrective Action Event Code is nationally defined and nationally required, will be set to ‘HQ’ in the database. If Corrective Action Event Code is nationally defined and not nationally required, will be set to ‘US’ in the database. If Corrective Action Event Code is implementer defined, will be set to your user agency in the database. 4 5 6 7 8 9 10 FK_AEVENTFK_LU_CA_EVENTCA_EVEN1 16 23 24 27 29 30 31 7 1 3 2 1 1 8 38 A A I Event Code Event Responsible Agency Event Sequence Number CA6-005, CA6-010 CA6-005, CA6-010 CA6-005, CA6-010 FK_AEVENTRESPONSIBLE_AGENCY1 FK_AEVENTEVENT_SEQ1 **SYSTEM-GENERATED FIELD** FK_ACA_AUTHORITFK_LU_AUTHTYPE1 FK_ACA_AUTHORITRESPONSIBLE_AGE1 Authority Type Owner – Will be set to ‘HQ’ in the database. A A Date Authority Type Authority Responsible Agency Authority Effective Date CA6-005, CA6-020 CA6-005, CA6-020 CA6-005, CA6-020 FK_ACA_AUTHORITEFFECITVE_DATE1 Total Record Length: 1 The fields that must be provided for Delete transactions (TRANSACTION_TYPE = ‘D’). June 2005 A-65 RCRAInfo Translator Guide-V3 CA6 Flat File Edit Specifications Edit Number CA6-005 Edit Description Handler ID, Event Code, Event Responsible Agency, Event Sequence Number, Authority Type, Authority Responsible Agency, and Authority Effective Date must be provided. Select Logic FK_AEVENTFK_HBASICHANDLER_ID <> ‘ ’ and FK_AEVENTFK_LU_CA_EVENTCA_EVEN <> ‘ ’ and FK_AEVENTRESPONSIBLE_AGENCY <> ‘ ’ and FK_AEVENTEVENT_SEQ <> ‘ ’ and FK_ACA_AUTHORITFK_LU_AUTHTYPE <> ‘ ’ and FK_ACA_AUTHORITRESPONSIBLE_AGE <> ‘ ’ and FK_ACA_AUTHORITEFFECTIVE_DATE <> ‘ ’ FK_AEVENTFK_HBASICHANDLER_ID, FK_AEVENTFK_LU_CA_EVENTCA_EVEN, FK_AEVENTRESPONSIBLE_AGENCY, and FK_AEVENTEVENT_SEQ in CA6 must exist in AEVENT or CA4 FK_ACA_AUTHORITFK_HBASICHANDLE, FK_ACA_AUTHORITFK_LU_AUTHTYPE, FK_ACA_AUTHORITRESPONSIBLE_AGE, and FK_ACA_AUTHORITEFFECTIVE_DATE in CA6 must exist in ACA_AUTHORITY or CA2 CA6-010 Handler ID, Event Code, Event Responsible Agency, and Event Sequence Number must exist in AEVENT or CA4. CA6-020 Handler ID, Authority Type, Authority Responsible Agency, and Authority Effective Date must exist in ACA_AUTHORITY or CA2. June 2005 A-66 RCRAInfo Translator Guide-V3 FLAT FILE ID# - CE1 Table Name: CEVALUATION3 CDX Staging Table Name: TR_CE1_V3 Description: Compliance Monitoring and Enforcement Evaluation Data Field No. 1 2 3 4 Field Name TRANSACTION_CODE1 HANDLER_ID1 EVAL_IDENTIFIER1 EVAL_START_DATE1 EVAL_AGENCY1 Starting Column 1 2 14 17 Field Length 1 12 3 8 Data Type A A A Date Description Transaction Code EPA Handler ID Evaluation Identifier Evaluation Start Date CE1-005, CE1-010 CE1-030 CE1-020, CE1-240 CE1-040, CE1-210, CE1-240 CE1-060, CEI-065, CE1-070, CE1-080 CE1-050, CE1-110, CE1-120, CE1-125, CE1-130, CE1-200, CE1-240 CE1-090, CE1-120, CE1-125, CE1-200, CE1-220, CE1-230, CE1-240 Edit Number(s) 5 25 1 A Responsible Agency 6 7 DAY_ZERO FOUND_VIOLATION 26 34 8 1 Date A Date of the Last Non-Followup Evaluation Flag Indicating if a Violation was Found 8 EVAL_TYPE 35 3 A Evaluation Type Note: Evaluation Type Owner will be set to ‘HQ’ in the database. 1 The fields that must be provided for Delete transactions (TRANSACTION_TYPE = >D=). June 2005 A-67 RCRAInfo Translator Guide-V3 FLAT FILE ID# - CE1 Table Name: CEVALUATION3 CDX Staging Table Name: TR_CE1_V3 Description: Compliance Monitoring and Enforcement Evaluation Data Field No. 9 Field Name RESPONSIBLE_PERSON Starting Column 38 Field Length 5 Data Type A Description Responsible Person Note: Responsible Person Owner will be set to your user agency in the database. Responsible Suborganization Note: Responsible Suborganization will be set to your user agency in the database. The Inspection or Record Review was Initiated Because of a Tip/Complaint The Inspection or Record Review was Part of a Multimedia Inspection Activity The Inspection Conducted Included the Collection of Samples The Inspection Conducted Pursuant to RCRA 3007 or State Equivalent; Determination Made: Site is NonHazardous Waste Edit Number(s) CE1-150 10 SUBORGANIZATION 43 10 A CE1-140 11 12 CITIZEN_COMPLAINT MULTIMEDIA_INSPECTION 53 54 1 1 A A CE1-160 CE1-170 13 14 SAMPLING NOT_SUBTITLE_C 55 56 1 1 A A CE1-180 CE1-190 June 2005 A-68 RCRAInfo Translator Guide-V3 FLAT FILE ID# - CE1 Table Name: CEVALUATION3 CDX Staging Table Name: TR_CE1_V3 Description: Compliance Monitoring and Enforcement Evaluation Data Field No. 15 16 Field Name FOCUS_AREA NOTES Total Record Length: Starting Column 57 60 Field Length 3 2000 2059 Data Type A A Focus Area Notes Description Edit Number(s) CE1-220, CE1-230 June 2005 A-69 RCRAInfo Translator Guide-V3 CE1 Flat File Edit Specifications Edit Number CE1-000 CE1-005 CE1-010 CE1-020 CE1-030 CE1-040 CE1-050 CE1-060 CE1-065 Edit Description Translator is not implementer of record for Evaluation Information for this agency. Handler ID must be provided. Handler ID must exist in HBASIC or HD1. Evaluation Start Date must be a valid date greater than or equal to January 1, 1980 and no later than today. Evaluation Identifier must be provided. Responsible Agency must equal >E=, >S=, >B=, >X=, >C=, >T=, ‘L’, or ‘N’ If Responsible Agency is equal to >X= or >T= Then Found Violation must equal >N=. If Evaluation Type is not equal to ‘CDI’, ‘FUI’, ‘CSE’, ‘SNN’, or ‘SNY’, then Day Zero must equal the Evaluation Start Date. If Evaluation Type is equal to a follow-up evaluation (‘CDI’, ‘FUI’, or ‘CSE’) then Day Zero must be the date of a non-follow –up evaluation (‘CEI’, ‘CAV’, ‘CAC’, ‘GME’, ‘FRR’, ‘FDS’, ‘NRR’, ‘OAM’, ‘FCI’) by the same agency occurring on or before the follow-up evaluation. HANDLER_ID <> > = HANDLER_ID in CE1 must exist in HBASIC or HD1 EVAL_START_DATE >= >19800101= and EVAL_START_DATE <= today EVAL_IDENTIFIER <> > = EVAL_AGENCY = >E= or >S= or >B= or >X= or >C= or >T= or ‘L’ or ‘N’ If EVAL_AGENCY = >X= or >T= Then FOUND_VIOLATION = >N= If EVAL_TYPE <> ‘CDI’ and ‘FUI’ and ‘CSE’ and ‘SNN’ and ‘SNY’ Then DAY_ZERO = START_DATE If EVAL_TYPE = ‘CDI’ or ‘FUI’ or ‘CSE’ Then DAY_ZERO = START_DATE of Non-Follow-Up Evaluation (‘CEI’, ‘CAV’, ‘CAC’, ‘GME’, ‘FRR’, ‘FDS’, ‘NRR’, ‘OAM’, ‘FCI’) Where START_DATE of Non-Follow-Up Evaluation <= START_DATE of Follow-Up Evaluation and Agency of Non-Follow-Up Evaluation = Agency of Follow-Up Evaluation If EVAL_TYPE = >SNY= Then DAY_ZERO >= >19800101= and DAY_ZERO <= today If EVAL_TYPE = ‘SNN’ Then DAY_ZERO = ‘ ‘ Select Logic CE1-070 If Evaluation Type is equal to >SNY= then Day Zero must be a valid date greater than or equal to January 1, 1980 and no later than today. If Evaluation Type is equal to >SNN= then Day Zero must be blank. CE1-080 June 2005 A-70 RCRAInfo Translator Guide-V3 CE1 Flat File Edit Specifications Edit Number CE1-090 CE1-110 CE1-120 Edit Description Evaluation Type must be a valid nationally defined value registered with the RCRAInfo database. Found Violation must equal >Y=, >N=, or >U= If Found Violation is equal to >Y= and Evaluation Type is not equal to ‘CAV’ then there must be a least one corresponding record in CLN_EVALUATION_VIOLATION3 or file CE5. If Found Violation is equal to ‘Y’ and Evaluation Type is equal to ‘CAV’ then there cannot be corresponding records in CLN_EVALUATION_VIOLATION3 or CE5. If Found Violation is equal to >N= or >U= then there cannot be corresponding records in CLN_EVALUATION_VIOLATION3 or CE5. Responsible Suborganization must be a valid implementer defined value registered with the RCRAInfo database or blank. Responsible Person must be a valid implementer defined value registered with the RCRAInfo database or blank. Citizen Complaint must equal >Y= or >N=. Multimedia Inspection must equal >Y= or >N=. Sampling must equal >Y= or >N=. Not Subtitle C must equal >Y= or >N=. If Evaluation Type = ‘CAV’ then Found Violation must equal ‘Y’ or ‘N’. Select Logic EVAL_TYPE = nationally defined evaluation type in LU_EVALUATION_TYPE FOUND_VIOLATION = >Y= or >N= or >U= If FOUND_VIOLATION = >Y= and EVAL_TYPE <> ‘CAV’ Then HANDLER_ID, EVAL_IDENTIFIER, EVAL_START_DATE, and EVAL_AGENCY in CE1 must exist in CLN_EVALUATION_VIOLATION3 or CE5 If FOUND_VIOLATION = ‘Y’ and EVAL_TYPE = ‘CAV’ Then HANDLER_ID, EVAL_IDENTIFIER, EVAL_START_DATE, and EVAL_AGENCY in CE1 cannot exist in CLN_EVALUATION_VIOLATION3 or CE5 If FOUND_VIOLATION = >N= or >U= Then HANDLER_ID, EVAL_IDENTIFIER, EVAL_START_DATE, and EVAL_AGENCY in CE1 cannot exist in CLN_EVALUATION_VIOLATION3 or CE5 SUBORGANIZATION = implementer defined suborganization in LU_SUBORGANIZATION or > = RESPONSIBLE_PERSON = implementer defined person identifier in LU_STAFF or > = CITIZEN_COMPLAINT = >Y= or >N= MULTIMEDIA_INSPECTION = >Y= or >N= SAMPLING = >Y= or >N= NOT_SUBTITLE_C = >Y= or >N= If EVAL_TYPE = ‘CAV’ Then FOUND_VIOLATION = ‘Y’ or ‘N’ CE1-125 CE1-130 CE1-140 CE1-150 CE1-160 CE1-170 CE1-180 CE1-190 CE1-200 June 2005 A-71 RCRAInfo Translator Guide-V3 CE1 Flat File Edit Specifications Edit Number CE1-210 CE1-220 Edit Description If Evaluation Type is equal to >SNY= or >SNN= then Agency must equal >E= or >S= If Evaluation Type is equal to ‘FCI’ then Focus Area must be a valid nationally or implementer defined value registered with the RCRAInfo database. If Evaluation Type is not equal to ‘FCI’ then Focus Area must be blank. If Evaluation Type is equal to ‘SNY’ and violations exist in CVIOLATION3 or CE4 where the Responsible Agency is equal to the Agency of the SNY and the Violation Determined Date occurred on or before the SNY Evaluation Start Date then Found Violation must equal ‘Y’ Select Logic If EVAL_TYPE = >SNY= or >SNN= Then EVAL_AGENCY = >E= or >S= If EVAL_TYPE = ‘FCI’ Then FOCUS_AREA = nationally defined or implementer defined focus area in LU_FOCUS_AREA If EVAL_TYPE <> ‘FCI’ Then FOCUS_AREA = ‘ ’ If EVAL_TYPE = ‘SNY’ and EVAL_AGENCY = RESPONSIBLE_AGENCY in CVIOLATION3 or CE4 and EVAL_START_DATE >= DETERMINED_DATE in CVIOLATION3 or CE4 Then FOUND_VIOLATION = ‘Y’ CE1-230 CE1-240 Additional Business Rules and Guidance • The use of CEVALUATION3 is mandatory. June 2005 A-72 RCRAInfo Translator Guide-V3 FLAT FILE ID# - CE2 Table Name: CREQUEST3 CDX Staging Table Name: TR_CE2_V3 Description: Compliance Monitoring and Enforcement Request Data Field No. 1 2 3 4 5 6 7 8 9 10 Field Name TRANSACTION_CODE1 HANDLER_ID1 REQUEST_SEQ1 EVAL_IDENTIFIER EVAL_START_DATE EVAL_AGENCY DATE_OF_REQUEST DATE_RESPONSE_RECEIVED AGENCY NOTES Total Record Length: Starting Column 1 2 14 20 23 31 32 40 48 49 Field Length 1 12 6 3 8 1 8 8 1 2000 2048 Data Type A A I A Date A Date Date A A Description Transaction Code EPA Handler ID Request Sequence Number Evaluation Identifier Evaluation Start Date Evaluation Agency Date of Request Date the Response was Received Request Agency Notes CE2-005, CE2-010, CE2-030 CE2-020 CE2-030 CE2-030 CE2-030 CE2-040, CE2-056 CE2-050, CE2-055 CE2-060 Edit Number(s) 1 The fields that must be provided for Delete transactions (TRANSACTION_TYPE = >D=). June 2005 A-73 RCRAInfo Translator Guide-V3 CE2 Flat File Edit Specifications Edit Number CE2-005 CE2-010 CE2-020 CE2-030 Edit Description Handler ID must be provided. Handler ID must exist in HBASIC or HD1. Request Sequence Number must be greater than zero. If Handler ID, Evaluation Identifier, Evaluation Start Date, and Evaluation Agency are provided then a corresponding record must exist in CEVALUATION3 or CE1. Date of Request must be a valid date greater than January 1, 1980 and less than or equal to today, or blank. Date Response Received must be on or after Date of Request and less than or equal to today, or blank. If Date of Request is not provided then Date Response Received must be provided. If Date Response Received is not provided then Date of Request must be provided. Request Agency must equal >E=, ‘S’, ‘B’, ‘X’, ‘C’, ‘T’, ‘L’, or ‘N’. Select Logic HANDLER_ID <> > = HANDLER_ID in CE2 must exist in HBASIC or HD1 REQUEST_SEQ > 0 If HANDLER_ID <> > = and EVAL_IDENTIFIER <> > = and EVAL_START_DATE <> > = and EVAL_AGENCY <> > = Then HANDLER_ID, EVAL_IDENTIFIER, EVAL_START_DATE, and EVAL_AGENCY in CE2 must exist in CEVALUATION3 or CE1 (DATE_OF_REQUEST >= >19800101= and DATE_OF_REQUEST <= today) or DATE_OF_REQUEST = > = (DATE_RESPONSE_RECEIVED >= DATE_OF_REQUEST and DATE_RESPONSE_RECEIVED <= today) or DATE_RESPONSE_RECEIVED = > = If DATE_OF_REQUEST = > = Then DATE_RESPONSE_RECEIVED <> > = If DATE_RESPONSE_RECEIVED = > = Then DATE_OF_REQUEST <> > = AGENCY = >E= or >S= or ‘B’ or ‘X’ or ‘C’ or ‘T’ or ‘L’ or ‘N’ CE2-040 CE2-050 CE2-055 CE2-056 CE2-060 • • • Additional Business Rules and Guidance At a minimum, a request with a received date should be linked to a Non-Financial Record Review (‘NRR’) evaluation. One evaluation can be linked to multiple requests. The use of CREQUEST3 is optional. June 2005 A-74 RCRAInfo Translator Guide-V3 FLAT FILE ID# - CE3 Table Name: CLN_EVALUATION_COMMITMENT3 CDX Staging Table Name: TR_CE3_V3 Description: Linking Data for Commitment and Evaluation Field No. 1 2 3 4 5 6 7 Field Name TRANSACTION_CODE1 COM_LEAD1 COM_SEQ1 EVAL_HANDLER_ID1 EVAL_IDENTIFIER1 EVAL_START_DATE1 EVAL_AGENCY1 Total Record Length: Starting Column 1 2 4 10 22 25 33 Field Length 1 2 6 12 3 8 1 33 Data Type A A I A A Date A Description Transaction Code Commitment Lead Location Commitment Sequence Number EPA Handler ID Evaluation Identifier Evaluation Start Date Evaluation Agency CE3-005, CE3-010 CE3-005, CE3-010 CE3-005, CE3-020 CE3-005, CE3-020 CE3-005, CE3-020 CE3-005, CE3-020 Edit Number(s) 1 The fields that must be provided for Delete transactions (TRANSACTION_TYPE = >D=). June 2005 A-75 RCRAInfo Translator Guide-V3 CE3 Flat File Edit Specifications Edit Number CE3-005 Edit Description Commitment Lead Location, Commitment Sequence Number, Handler ID, Evaluation Identifier, Evaluation Start Date, and Evaluation Agency must be provided. Commitment Lead Location and Commitment Sequence Number must exist in COMMITMENT3 or CEG. Handler ID, Evaluation Identifier, Evaluation Start Date, and Evaluation Agency must exist in CEVALUATION3 or CE1. Select Logic COM_LEAD <> > = and COM_SEQ <> > = and HANDLER_ID <> > = and EVAL_IDENTIFIER <> > = and EVAL_START_DATE <> > = and EVAL_AGENCY <> > = COM_LEAD and COM_SEQ in CE3 must exist in COMMITMENT3 or CEG HANDLER_ID, EVAL_IDENTIFIER, EVAL_START_DATE, and EVAL_AGENCY in CE3 must exist in CEVALUATION3 or CE1 CE3-010 CE3-020 Additional Business Rules and Guidance • The use of CLN_EVALUATION_COMMITMENT3 is optional. June 2005 A-76 RCRAInfo Translator Guide-V3 FLAT FILE ID# - CE4 Table Name: CVIOLATION3 CDX Staging Table Name: TR_CE4_V3 Description: Compliance Monitoring and Enforcement Violation Data Field No. 1 2 3 4 Field Name TRANSACTION_CODE1 HANDLER_ID1 VIOL_SEQ1 VIOL_DETERMINED_BY_AGENCY1 Starting Column 1 2 14 18 Field Length 1 12 4 1 Data Type A A I A Description Transaction Code EPA Handler ID Violation Sequence Number Agency Which Determined the Violation Violation Type Note: Violation Type Owner will be set to ‘HQ’ in the database. Former Citation Date Violation Was Determined Actual Return to Compliance Date Return to Compliance Qualifier CE4-050 CE4-060, CE4-080 CE4-070, CE4-090 CE4-005, CE4-010 CE4-010, CE4-020 CE4-010, CE4-030, CE4-035, CE4-037, CE4-100 CE4-040 Edit Number(s) 5 VIOL_TYPE 19 10 A 6 7 8 9 FORMER_CITATION DETERMINED_DATE ACTUAL_RTC_DATE RTC_QUALIFIER 29 64 72 80 35 8 8 1 A Date Date A 1 The fields that must be provided for Delete transactions (TRANSACTION_TYPE = >D=). June 2005 A-77 RCRAInfo Translator Guide-V3 FLAT FILE ID# - CE4 Table Name: CVIOLATION3 CDX Staging Table Name: TR_CE4_V3 Description: Compliance Monitoring and Enforcement Violation Data Field No. 10 11 Field Name RESPONSIBLE_AGENCY NOTES Total Record Length: Starting Column 81 82 Field Length 1 2000 2081 Data Type A A Description Violation Responsible Agency Notes Edit Number(s) CE4-100, CE4-110 June 2005 A-78 RCRAInfo Translator Guide-V3 CE4 Flat File Edit Specifications Edit Number CE4-000 CE4-005 CE4-010 Edit Description Translator is not implementer of record for Violation Information for this agency. Handler ID must be provided. Handler ID, Violation Sequence Number, and Agency Which Determined the Violation must exist in CLN_EVALUATION_VIOLATION3 or CE5. Violation Sequence Number must be provided. Agency Which Determined the Violation must be equal to >E= or >S= If Evaluation Agency is equal to ‘L’ or ‘B’ then Agency Which Determined the Violation must equal ‘S’. If Evaluation Agency is equal to ‘N’ or ‘C’ then Agency Which Determined the Violation must equal ‘E’. Violation Type must be a nationally defined value registered with the RCRAInfo database. Date Violation Was Determined must be a valid date on or after Evaluation Start Date and no later than today. Actual Return to Compliance Date must be a valid date on or after Date Violation Was Determined and no later than today, or blank. Return to Compliance Qualifier must equal >D=, >O=, >N=, >U= or blank. HANDLER_ID <> > = HANDLER_ID, VIOL_SEQ and VIOL_DETERMINED_BY_AGENCY in CE4 must exist in CLN_EVALUATION_VIOLATION3 or CE5 VIOL_SEQ > 0 VIOL_DETERMINED_BY_AGENCY = >E= or >S= If EVAL_AGENCY (in CEVALUATION3 or CE1) = ‘L’ or ‘B’ Then VIOL_DETERMINED_BY_AGENCY = ‘S’ If EVAL_AGENCY (in CEVALUATION3 or CE1) = ‘N’ or ‘C’ Then VIOL_DETERMINED_BY_AGENCY = ‘E’ VIOL_TYPE = nationally defined violation type in LU_VIOLATION_TYPE DETERMINED_DATE >= START_DATE (in CEVALUATION3 or CE1) and DETERMINED_DATE <= today (ACTUAL_RTC_DATE >= DETERMINED_DATE and ACTUAL_RTC_DATE <= today) or ACTUAL_RTC_DATE = > = RTC_QUALIFIER = >D= or >O= or >N= or >U= or > = Select Logic CE4-020 CE4-030 CE4-035 CE4-037 CE4-040 CE4-050 CE4-060 CE4-070 June 2005 A-79 RCRAInfo Translator Guide-V3 CE4 Flat File Edit Specifications Edit Number CE4-080 CE4-090 CE4-100 Edit Description If Return to Compliance Qualifier is provided then Actual Return to Compliance Date must be provided. If Actual Return to Compliance Date is provided then Return to Compliance Qualifier must be provided. If there is no corresponding 810 or 820 enforcement action in CENFORCEMENT3 or CE8 then Responsible Agency must equal the Agency Which Determined the Violation. If there is a corresponding 810 and/or 820 enforcement action in CENFORCEMENT3 or CE8 then Responsible Agency = >E= if the most recent 810/820 action is an 810 and Responsible Agency = ‘S’ if the most recent 810/820 action is an 820. Select Logic If RTC_QUALIFIER <> > = Then ACTUAL_RTC_DATE <> > = If ACTUAL_RTC_DATE <> > = Then RTC_QUALIFIER <> > = If ENF_TYPE (in CENFORCEMENT3 or CE8) <> >810= or >820= Then RESPONSIBLE_AGENCY = VIOL_DETERMINED_BY_AGENCY If ENF_TYPE (in CENFORCEMENT3 or CE8) = >810= or ‘820’ and the most recent ENF_TYPE = ‘810’ Then RESPONSIBLE_AGENCY = >E= If ENF_TYPE (in CENFORCEMENT3 or CE8) = ‘810’ or ‘820’ and the most recent ENF_TYPE = ‘820’ Then RESPONSBILE_AGENCY = ‘S’ CE4-110 Additional Business Rules and Guidance • • A violation may be linked to one or more Citation Type/Citation combination as long as they are registered with the RCRAInfo database. The use of CVIOLATION3 is mandatory. June 2005 A-80 RCRAInfo Translator Guide-V3 FLAT FILE ID# - CE5 Table Name: CLN_EVALUATION_VIOLATION3 CDX Staging Table Name: TR_CE5_V3 Description: Linking Data for Evaluation and Violation Field No. 1 2 3 4 5 6 7 Field Name TRANSACTION_CODE1 HANDLER_ID1 EVAL_IDENTIFIER1 EVAL_START_DATE1 EVAL_AGENCY1 VIOL_SEQ1 VIOL_DETERMINED_BY_AGENCY1 Total Record Length: Starting Column 1 2 14 17 25 26 30 Field Length 1 12 3 8 1 4 1 30 Data Type A A A Date A I A Description Transaction Code EPA Handler ID Evaluation Identifier Evaluation Start Date Evaluation Agency Violation Sequence Number Agency Which Determined the Violation CE5-005, CE5-010, CE5-020 CE5-005, CE5-010 CE5-005, CE5-010 CE5-005, CE5-010, CE5-030, CE5-040 CE5-005, CE5-020 CE5-005, CE5-020, CE5-030, CE5-040 Edit Number(s) 1 The fields that must be provided for Delete transactions (TRANSACTION_TYPE = >D=). June 2005 A-81 RCRAInfo Translator Guide-V3 CE5 Flat File Edit Specifications Edit Number CE5-005 Edit Description Handler ID, Evaluation Identifier, Evaluation Start Date, Evaluation Agency, Violation Sequence Number, and Agency Which Determined the Violation must be provided. Handler ID, Evaluation Identifier, Evaluation Start Date, and Evaluation Agency must exist in CEVALUATION3 or CE1. Handler ID, Violation Sequence Number, and Agency Which Determined the Violation must exist in CVIOLATION3 or CE4. If Evaluation Agency is equal to ‘B’ or ‘L’ then Violation Determined By Agency must equal ‘S’. If Evaluation Agency is equal to ‘C’ or ‘N’ then Violation Determined By Agency must equal ‘E’. Select Logic HANDLER_ID <> > = and EVAL_IDENTIFIER <> > = and EVAL_START_DATE <> > = and EVAL_AGENCY and VIOL_SEQ <> > = and VIOL_DETERMINED_BY_AGENCY <> > = HANDLER_ID, EVAL_IDENTIFIER, EVAL_START_DATE, and EVAL_AGENCY in CE5 must exist in CEVALUATION3 or CE1 HANDLER_ID, VIOL_SEQ, and VIOL_DETERMINED_BY_AGENCY in CE5 must exist in CVIOLATION3 or CE4 If EVAL_AGENCY = ‘B’ or ‘L’ Then VIOL_DETERMINED_BY_AGENCY = ‘S’ If EVAL_AGENCY = ‘C’ or ‘N’ Then VIOL_DETERMINED_BY_AGENCY = ‘E’ CE5-010 CE5-020 CE5-030 CE5-040 Additional Business Rules and Guidance • The use of CLN_EVALUATION_VIOLATION3 is mandatory. June 2005 A-82 RCRAInfo Translator Guide-V3 FLAT FILE ID# - CE6 Table Name: CCITATION3 CDX Staging Table Name: TR_CE6_V3 Description: Compliance Monitoring and Enforcement Citation Data Field No. 1 2 3 4 5 6 Field Name TRANSACTION_CODE1 VIOL_HANDLER_ID1 VIOL_SEQ1 VIOL_DETERMINED_BY_AGENCY1 CITATION_SEQ1 VIOL_TYPE Starting Column 1 2 14 18 19 25 Field Length 1 12 4 1 6 10 Data Type A A I A I A Description Transaction Code EPA Handler ID Violation Sequence Number Agency Which Determined the Violation Citation Sequence Number Violation Type Note: Violation Type Owner will be set to ‘HQ’ in the database. Citation Note: Citation Owner will be set to your user agency in the database. Citation Type Notes CE6-005, CE6-010 CE6-005, CE6-010 CE6-005, CE6-010 CE6-030 CE6-020 Edit Number(s) 7 CITATION 35 35 A CE6-020 8 9 CITATION_TYPE NOTES Total Record Length: 70 72 2 2000 2071 A A CE6-020 1 The fields that must be provided for Delete transactions (TRANSACTION_TYPE = >D=). June 2005 A-83 RCRAInfo Translator Guide-V3 CE6 Flat File Edit Specifications Edit Number CE6-005 CE6-010 Edit Description Handler ID, Violation Sequence Number, and Agency Which Determined the Violation must be provided. Handler ID, Violation Sequence Number, and Agency Which Determined the Violation must exist in CVIOLATION3 or CE4. Violation Type, Citation, and Citation Type must exist in CLN_LU_VIOL_CITATION or CE7. Citation Sequence Number must be greater than zero. Select Logic HANDLER_ID <> > = and VIOL_SEQ <> > = and VIOL_DETERMINED_BY_AGENCY <> > = HANDLER_ID, VIOL_SEQ, and VIOL_DETERMINED_BY_AGENCY in CE6 must exist in CVIOLATION3 or CE4 VIOL_TYPE, CITATION, and CITATION_TYPE in CE6 must exist in CLN_LU_VIOL_CITATION or CE7 CITATION_SEQ > 0 CE6-020 CE6-030 Additional Business Rules and Guidance • • A Citation Type/Citation combination may be linked to more than one violation type. The use of CCITATION3 is optional. June 2005 A-84 RCRAInfo Translator Guide-V3 FLAT FILE ID# - CE7 Table Name: CLN_LU_VIOL_CITATION3 CDX Staging Table Name: TR_CE7_V3 Description: Linking Data for CCITATION3 and LU_VIOLATION_TYPE Field No. 1 2 Field Name TRANSACTION_CODE1 VIOL_TYPE1 Starting Column 1 2 Field Length 1 10 Data Type A A Description Transaction Code Violation Type Note: Violation Type Owner will be set to ‘HQ’ in the database. Citation Note: Citation Owner will be set to your user agency in the database. Citation Type CE7-005, CE7-010 Edit Number(s) 3 CITATION1 12 35 A CE7-005, CE7-020 4 CITATION_TYPE1 Total Record Length: 47 2 48 A CE7-005, CE7-020 1 The fields that must be provided for Delete transactions (TRANSACTION_TYPE = >D=). June 2005 A-85 RCRAInfo Translator Guide-V3 CE7 Flat File Edit Specifications Edit Number CE7-005 CE7-010 CE7-020 Edit Description Violation Type, Citation, and Citation Type must be provided. Violation Type must exist in LU_VIOLATION_TYPE. Citation and Citation Type must exist in LU_CITATION. Select Logic VIOL_TYPE <> > = and CITATION <> > = and CITATION_TYPE <> > = VIOL_TYPE in CE7 must exist in LU_VIOLATION_TYPE CITATION and CITATION_TYPE in CE7 must exist in LU_CITATION Additional Business Rules and Guidance • The use of CLN_LU_VIOL_CITATION3 is optional. June 2005 A-86 RCRAInfo Translator Guide-V3 FLAT FILE ID# - CE8 Table Name: CENFORCEMENT3 CDX Staging Table Name: TR_CE8_V3 Description: Compliance Monitoring and Enforcement Data Field No. 1 2 3 4 5 6 7 8 9 10 11 12 Field Name TRANSACTION_CODE1 HANDLER_ID1 ENF_IDENTIFIER1 ENF_ACTION_DATE1 ENF_AGENCY1 DOCKET_NUMBER ATTORNEY CA_COMPONENT CAFO_SEQ APPEAL_INITIATED_DATE APPEAL_RESOLVED_DATE DISPOSITION_STATUS_DATE Starting Column 1 2 14 17 25 26 41 46 47 53 61 69 Field Length 1 12 3 8 1 15 5 1 6 8 8 8 Data Type A A A Date A A A A I Date Date Date Description Transaction Code EPA Handler ID Enforcement Identifier Enforcement Action Date Enforcement Agency Docket Number Attorney Corrective Action Component Indicator CA/FO Sequence Number Appeal Initiated Date Appeal Resolved Date Disposition Status Date CE8-080 CE8-090 CE8-100 CE8-110 CE8-120, CE8-140 CE8-005, CE8-010 CE8-060 CE8-050 CE8-070 Edit Number(s) 1 The fields that must be provided for Delete transactions (TRANSACTION_TYPE = >D=). June 2005 A-87 RCRAInfo Translator Guide-V3 FLAT FILE ID# - CE8 Table Name: CENFORCEMENT3 CDX Staging Table Name: TR_CE8_V3 Description: Compliance Monitoring and Enforcement Data Field No. 13 Field Name DISPOSITION_STATUS Starting Column 77 Field Length 2 Data Type A Description Disposition Status Note: Disposition Status will be set to ‘US’ in the database. Enforcement Type Note: If Enforcement Type is nationally defined, Enforcement Type Owner will be set to ‘HQ’ in the database. If Enforcement Type is implementer defined, Enforcement Type Owner will be set to your user agency in the database. Responsible Person ID Note: Responsible Person Owner will be set to your user agency in the database. Responsible Suborganization Note: Responsible Suborganization Owner will be set to your user agency in the database. Notes Edit Number(s) CE8-130, CE8-150 14 ENF_TYPE 79 3 A CE8-020, CE8-160 15 RESPONSIBLE_PERSON 82 5 A CE8-040 16 SUBORGANIZATION 87 10 A CE8-030 NOTES Total Record Length: 97 2000 2096 A June 2005 A-88 RCRAInfo Translator Guide-V3 CE8 Flat File Edit Specifications Edit Number CE8-000 CE8-005 CE8-010 CE8-020 CE8-030 CE8-040 CE8-050 Edit Description Translator is not implementer of record for Enforcement Information for this agency. Handler ID must be provided. Handler ID must exist in HBASIC or HD1. Enforcement Type must be a valid nationally or implementer defined value registered with the RCRAInfo database. Responsible Suborganization must be a valid implementer defined value registered with the RCRAInfo database or blank. Responsible Person must be a valid implementer defined value registered with the RCRAInfo database or blank. Enforcement Action Date must be a valid date greater than or equal to most recent Violation Determined Date that this enforcement is linked to and no later than today. Enforcement Identifier must be provided. Enforcement Agency must equal >E= or >S=. Corrective Action Component Indicator must equal >Y= or >N=. If Enforcement Type equals >380= then CA/FO Sequence Number must be a valid implementer defined value registered with the RCRAInfo database. If Enforcement Type does not equal >280= or >380= then CA/FO Sequence Number must equal blank. HANDLER_ID <> > = HANDLER_ID must exist in HBASIC or HD1 ENF_TYPE = nationally or implementer defined enforcement type in LU_ENFORCEMENT_TYPE SUBORGANIZATION = implementer defined suborganization in LU_SUBORGANIZATION or > = RESPONSIBLE_PERSON = implementer defined person identifier in LU_STAFF or > = ENF_ACTION_DATE >= DETERMINED_DATE (in CVIOLATION3 or CE4) and ENF_ACTION_DATE <= today ENF_IDENTIFIER <> > = ENF_AGENCY = >E= or >S== CA_COMPONENT = >Y= or >N= If ENF_TYPE = >380= Then CAFO_SEQ = implementer defined CA/FO sequence in LU_CAFO If ENF_TYPE <> >280= or >380= Then CAFO_SEQ = > = Select Logic CE8-060 CE8-070 CE8-080 CE8-090 June 2005 A-89 RCRAInfo Translator Guide-V3 CE8 Flat File Edit Specifications Edit Number CE8-100 Edit Description Appeal Initiated Date must be a valid date on or after the Enforcement Action Date and no later than today, or blank. Appeal Resolved Date must be a valid date on or after the Appeal Initiated Date and no later than today, or blank. Disposition Status Date must be a valid date on or after the Enforcement Action Date and no later than today, or blank. Disposition Status must be a valid nationally defined value registered with the RCRAInfo database or blank. If Disposition Status is provided then Disposition Status Date must be provided. If Disposition Status Date is provided then Disposition Status must be provided. Enforcement Types 100-199 and 800-899 cannot impose a penalty. Select Logic (APPEAL_INITIATED_DATE >= ENF_ACTION_DATE and APPEAL_INITIATED_DATE <= today) or APPEAL_INITIATED_DATE = > = (APPEAL_RESOLVED_DATE >= APPEAL_INITIATED_DATE and APPEAL_RESOLVED_DATE <= today) or APPEAL_RESOLVED_DATE = > = (DISPOSITION_STATUS_DATE >= ENF_ACTION_DATE and DISPOSITION_STATUS_DATE <= today) or DISPOSITION_STATUS_DATE = > = DISPOSITION_STATUS = nationally defined disposition status in LU_DISPOSITION_STATUS or > = If DISPOSITION_STATUS <> > = Then DISPOSITION_STATUS_DATE <> > = If DISPOSITION_STATUS_DATE <> > = Then DISPOSITION_STATUS <> > = If ENF_TYPE >= >100= and <= >199= or ENF_TYPE >= >800= and <= >899= Then no record in CPENALTY3 or CEE. CE8-110 CE8-120 CE8-130 CE8-140 CE8-150 CE8-160 Additional Business Rules and Guidance • The use of CENFORCEMENT3 is mandatory. June 2005 A-90 RCRAInfo Translator Guide-V3 FLAT FILE ID# - CE9 Table Name: CLN_VIOLATION_ENFORCEMENT3 CDX Staging Table Name: TR_CE9_V3 Description: Linking Data for Violation and Enforcement Field No. 1 2 3 4 5 6 7 8 Field Name TRANSACTION_CODE1 HANDLER_ID1 VIOL_SEQ1 VIOL_DETERMINED_BY_AGENCY1 ENF_IDENTIFIER1 ENF_ACTION_DATE1 ENF_AGENCY1 SCHEDULED_COMPLIANCE_DATE Total Record Length: Starting Column 1 2 14 18 19 22 30 31 Field Length 1 12 4 1 3 8 1 8 38 Data Type A A I A A Date A Date Description Transaction Code EPA Handler ID Violation Sequence Number Agency Which Determined Violation Enforcement Identifier Enforcement Action Date Enforcement Agency Scheduled Compliance Date CE9-005, CE9-010, CE9-020 CE9-005, CE9-010 CE9-005, CE9-010 CE9-005, CE9-020 CE9-005, CE9-020 CE9-005, CE9-020 CE9-030 Edit Number(s) 1 The fields that must be provided for Delete transactions (TRANSACTION_TYPE = >D=). June 2005 A-91 RCRAInfo Translator Guide-V3 CE9 Flat File Edit Specifications Edit Number CE9-005 Edit Description Handler ID, Violation Sequence Number, Agency Which Determined Violation, Enforcement Identifier, Enforcement Action Date, and Enforcement Agency must be provided. Handler ID, Violation Sequence Number, and Agency Which Determined Violation must exist in CVIOLATION3 or CE4. Handler ID, Enforcement Identifier, Enforcement Action Date, and Enforcement Agency must exist in CENFORCEMENT3 or CE8. Scheduled Compliance Date must be a valid date greater than or equal to Enforcement Action Date or blank. Select Logic HANDLER_ID <> > = and VIOL_SEQ <> > = and VIOL_DETERMINED_BY_AGENCY <> > = and ENF_IDENTIFIER <> > = and ENF_ACTION_DATE <> > = and ENF_AGENCY <> > = HANDLER_ID, VIOL_SEQ and VIOL_DETERMINED_BY_AGENCY in CE9 must exist in CVIOLATION3 or CE4 HANDLER_ID, ENF_IDENTIFIER, ENF_ACTION_DATE, and ENF_AGENCY in CE9 must exist in CENFORCEMENT3 or CE8 SCHEDULED_COMPLIANCE_DATE >= ENF_ACTION_DATE or SCHEDULED_COMPLIANCE_DATE = > = CE9-010 CE9-020 CE9-030 Additional Business Rules and Guidance • The use of CLN_VIOLATION_ENFORCEMENT3 is mandatory. June 2005 A-92 RCRAInfo Translator Guide-V3 FLAT FILE ID# - CEA Table Name: CMEDIA3 CDX Staging Table Name: TR_CEA_V3 Description: Compliance Monitoring and Enforcement Multimedia Data Field No. 1 2 3 4 5 6 Field Name TRANSACTION_CODE1 HANDLER_ID1 ENF_IDENTIFIER1 ENF_ACTION_DATE1 ENF_AGENCY1 MEDIA_TYPE Starting Column 1 2 14 17 25 26 Field Length 1 12 3 8 1 3 Data Type A A A Date A A Description Transaction Code EPA Handler ID Enforcement Identifier Enforcement Action Date Enforcement Agency Multimedia Type Note: Multimedia Type Owner will be set to ‘HQ’ in the database. Notes CEA-005, CEA-010 CEA-005, CEA-010 CEA-005, CEA-010 CEA-005, CEA-010 CEA-020 Edit Number(s) 7 NOTES Total Record Length: 29 2000 2028 A 1 The fields that must be provided for Delete transactions (TRANSACTION_TYPE = >D=). June 2005 A-93 RCRAInfo Translator Guide-V3 CEA Flat File Edit Specifications Edit Number CEA-005 CEA-010 Edit Description Handler ID, Enforcement Identifier, Enforcement Action Date, and Enforcement Agency must be provided. Handler ID, Enforcement Identifier, Enforcement Action Date, and Enforcement Agency must exist in CENFORCEMENT3 or CE8. Multimedia Type must be a valid nationally defined value registered with the RCRAInfo database. Select Logic HANDLER_ID <> > = and ENF_IDENTIFIER <> > = and ENF_ACTION_DATE <> > = and ENF_AGENCY <> > = HANDLER_ID, ENF_IDENTIFIER, ENF_ACTION_DATE, and ENF_AGENCY in CEA must exist in CENFORCEMENT3 or CE8 MEDIA_TYPE = nationally defined media type in LU_MEDIA_TYPE CEA-020 Additional Business Rules and Guidance • The use of CMEDIA3 is optional. June 2005 A-94 RCRAInfo Translator Guide-V3 FLAT FILE ID# - CEB Table Name: CSEP3 CDX Staging Table Name: TR_CEB_V3 Description: Compliance Monitoring and Enforcement Supplemental Environmental Project Data Field No. 1 2 3 4 5 6 7 8 9 10 Field Name TRANSACTION_CODE1 HANDLER_ID1 ENF_IDENTIFIER1 ENF_ACTION_DATE1 ENF_AGENCY1 SEP_SEQ1 EXPENDITURE_AMOUNT SCHEDULED_COMPLETION_DATE ACTUAL_COMPLETION_DATE DEFAULTED_DATE Starting Column 1 2 14 17 25 26 28 41 49 57 Field Length 1 12 3 8 1 2 13 8 8 8 Data Type A A A Date A I D12,2 Date Date Date Description Transaction Code EPA Handler ID Enforcement Identifier Enforcement Action Date Enforcement Agency SEP Sequence Number Expenditure Amount SEP Scheduled Completion Date SEP Actual Completion Date Date the SEP Defaulted CEB-005, CEB-010 CEB-005, CEB-010 CEB-005, CEB-010 CEB-005, CEB-010 CEB-020 CEB-090, CEB-100 CEB-040, CEB-060 CEB-070, CEB-080 CEB-050, CEB-060, CEB-110 CEB-080, CEB-100, CEB-110, CEB-120 Edit Number(s) 1 The fields that must be provided for Delete transactions (TRANSACTION_TYPE = >D=). June 2005 A-95 RCRAInfo Translator Guide-V3 FLAT FILE ID# - CEB Table Name: CSEP3 CDX Staging Table Name: TR_CEB_V3 Description: Compliance Monitoring and Enforcement Supplemental Environmental Project Data Field No. 11 Field Name SEP_TYPE Starting Column 65 Field Length 3 Data Type A Description SEP Type Note: If SEP Type is nationally defined, SEP Type Owner will be set to ‘HQ’ in the database. If SEP Type is implementer defined, SEP Type Owner will be set to your user agency in the database. Notes Edit Number(s) CEB-030 12 NOTES Total Record Length: 68 2000 2067 A June 2005 A-96 RCRAInfo Translator Guide-V3 CEB Flat File Edit Specifications Edit Number CEB-005 CEB-010 Edit Description Handler ID, Enforcement Identifier, Enforcement Action Date, and Enforcement Agency must be provided. Handler ID, Enforcement Identifier, Enforcement Action Date, and Enforcement Agency must exist in CENFORCEMENT3 or CE8. SEP Sequence Number must be greater than zero. SEP Type must be a valid nationally or implementer defined value registered with the RCRAInfo database. SEP Scheduled Completion Date must be a valid date greater than or equal to the Enforcement Action Date or blank. SEP Actual Completion Date must be a valid date greater than or equal to the Enforcement Action Date and no later than today, or blank. If SEP Scheduled Completion Date is not provided then SEP Actual Completion Date must be provided. If SEP Actual Completion Date is not provided then SEP Scheduled Completion Date must be provided. Date the SEP Defaulted Date must be a valid date greater than the SEP Scheduled Completion Date and no later than today, or blank. Expenditure Amount must be greater than zero or blank. Select Logic HANDLER_ID <> > = and ENF_IDENTIFIER <> > = and ENF_ACTION_DATE <> > = and ENF_AGENCY <> > = HANDLER_ID, ENF_IDENTIFIER, ENF_ACTION_DATE, and ENF_AGENCY in CEB must exist in CENFORCEMENT3 or CE8 SEP_SEQ > 0 SEP_TYPE = nationally or implementer defined SEP type in LU_SEP_TYPE SCHEDULED_COMPLETION_DATE >= ENF_ACTION_DATE or SCHEDULED_COMPLETION_DATE = > = (ACTUAL_COMPLETION_DATE >= ENF_ACTION_DATE and ACTUAL_COMPLETION_DATE <= today) or ACTUAL_COMPLETION_DATE = > = If SCHEDULED_COMPLETION_DATE = > = Then ACTUAL_COMPLETION_DATE <> > = If ACTUAL_COMPLETION_DATE = > = Then SCHEDULED_COMPLETION_DATE <> > = (DEFAULTED_DATE > SCHEDULED_COMPLETION_DATE and DEFAULTED_DATE <= today) or DEFAULTED_DATE = > = EXPENDITURE_AMOUNT > 0 or > = CEB-020 CEB-030 CEB-040 CEB-050 CEB-060 CEB-070 CEB-080 CEB-090 June 2005 A-97 RCRAInfo Translator Guide-V3 CEB Flat File Edit Specifications Edit Number CEB-100 CEB-110 Edit Description If SEP Actual Completion Date or SEP Defaulted Date is provided then Expenditure Amount must be greater than zero. If Expenditure Amount greater than zero then SEP Actual Completion Date or SEP Defaulted Date must be provided. If SEP Defaulted Date is provided then SEP Actual Completion Date must be blank. Select Logic If ACTUAL_COMPLETION_DATE <> > = or DEFAULTED_DATE <> ‘ ’ Then EXPENDITURE_AMOUNT > 0 If EXPENDITURE_AMOUNT > 0 Then ACTUAL_COMPLETION_DATE <> > = or DEFAULTED_DATE <> ‘ ’ If DEFAULTED_DATE <> ‘ ’ Then ACTUAL_COMPLETION_DATE = ‘ ’ CEB-120 Additional Business Rules and Guidance • The use of CSEP3 is mandatory. June 2005 A-98 RCRAInfo Translator Guide-V3 FLAT FILE ID# - CEC Table Name: CSNY_DATE3 CDX Staging Table Name: TR_CEC_V3 Description: Compliance Monitoring and Enforcement Significant Non-Complier Date Data Field No. 1 2 3 4 5 6 Field Name TRANSACTION_CODE HANDLER_ID1 ENF_IDENTIFIER1 ENF_ACTION_DATE1 ENF_AGENCY1 SNY_DATE1 Total Record Length: Starting Column 1 2 14 17 25 26 Field Length 1 12 3 8 1 8 33 Data Type A A A Date A Date Description Transaction Code EPA Handler ID Enforcement Identifier Enforcement Action Date Enforcement Agency Date of the SNY that the Action is Addressing CEC-005, CEC-010 CEC-005, CEC-010 CEC-005, CEC-010 CEC-005, CEC-010 CEC-020 Edit Number(s) 1 The fields that must be provided for Delete transactions (TRANSACTION_TYPE = >D=). June 2005 A-99 RCRAInfo Translator Guide-V3 CEC Flat File Edit Specifications Edit Number CEC-005 CEC-010 Edit Description Handler ID, Enforcement Identifier, Enforcement Action Date, and Enforcement Agency must be provided. Handler ID, Enforcement Identifier, Enforcement Action Date, and Enforcement Agency must exist in CENFORCEMENT3 or CE8. Date of the SNY that the Action is Addressing must be a valid date associated with an SNY evaluation associated with this agency and must occur before or on the Enforcement Action Date Select Logic HANDLER_ID <> > = and ENF_IDENTIFIER <> > = and ENF_ACTION_DATE <> > = and ENF_AGENCY <> > = HANDLER_ID, ENF_IDENTIFIER, ENF_ACTION_DATE, and ENF_AGENCY in CEC must exist in CENFORCEMENT3 or CE8 SNY_DATE = EVAL_START_DATE (in CEVALUATION3 or CE1) of an SNY evaluation where ENF_AGENCY = EVAL_AGENCY (in CEVALUATION3 or CE1) and SNY_DATE <= ENF_ACTION_DATE CEC-020 Additional Business Rules and Guidance • The use of CSNY_DATE3 is mandatory. June 2005 A-100 RCRAInfo Translator Guide-V3 FLAT FILE ID# - CED Table Name: CMILESTONE3 CDX Staging Table Name: TR_CED_V3 Description: Compliance Monitoring and Enforcement Milestone Data Field No. 1 2 3 4 5 6 7 8 9 10 Field Name TRANSACTION_CODE1 HANDLER_ID1 ENF_IDENTIFIER1 ENF_ACTION_DATE1 ENF_AGENCY1 MILESTONE_SEQ1 TECHNICAL_REQUIREMENT_NUMBER Starting Column 1 2 14 17 25 26 32 82 282 290 Field Length 1 12 3 8 1 6 50 200 8 8 Data Type A A A Date A I A A Date Date Description Transaction Code EPA Handler ID Enforcement Identifier Enforcement Action Date Enforcement Agency Milestone Sequence Number Technical Requirement Number Technical Requirement Description Milestone Scheduled Completion Date Milestone Actual Completion Date Edit Number(s) CED-005, CED-010 CED-005, CED-010 CED-005, CED-010 CED-005, CED-010 CED-020 CED-030 CED-040 CED-050, CED-070, CED-080, CED-090 CED-060, CED-070 TECHNICAL_REQUIREMENT_DESC SCHEDULED_COMPLETION_DATE ACTUAL_COMPLETION_DATE 1 The fields that must be provided for Delete transactions (TRANSACTION_TYPE = >D=). June 2005 A-101 RCRAInfo Translator Guide-V3 FLAT FILE ID# - CED Table Name: CMILESTONE3 CDX Staging Table Name: TR_CED_V3 Description: Compliance Monitoring and Enforcement Milestone Data Field No. 11 12 Field Name DEFAULTED_DATE NOTES Total Record Length: Starting Column 298 306 Field Length 8 2000 2305 Data Type Date A Description Milestone Defaulted Date Notes Edit Number(s) CED-090 June 2005 A-102 RCRAInfo Translator Guide-V3 CED Flat File Edit Specifications Edit Number CED-005 CED-010 Edit Description Handler ID, Enforcement Identifier, Enforcement Action Date, and Enforcement Agency must be provided. Handler ID, Enforcement Identifier, Enforcement Action Date, and Enforcement Agency must exist in CENFORCEMENT3 or CE8. Milestone Sequence Number must be greater than zero. Technical Requirement Number must be provided. Technical Requirement Description must be provided. Milestone Scheduled Completion Date must be a valid date greater than or equal to the Enforcement Action, or blank. Milestone Actual Completion Date must be a valid date greater than or equal to the Enforcement Action Date and no later than today, or blank. If Milestone Scheduled Completion Date is not provided then Milestone Actual Completion Date must be provided. If Milestone Actual Completion Date is not provided then Milestone Scheduled Completion Date must be provided. Select Logic HANDLER_ID <> > = and ENF_IDENTIFIER <> > = and ENF_ACTION_DATE <> > = and ENF_AGENCY <> > = HANDLER_ID, ENF_IDENTIFIER, ENF_ACTION_DATE, and ENF_AGENCY in CED must exist in CENFORCEMENT3 or CE8 MILESTONE_SEQ > 0 TECHNICAL_REQUIREMENT_NUMBER <> > = TECHNICAL_REQUIREMENT_DESC <> > = SCHEDULED_COMPLETION_DATE >= ENF_ACTION_DATE or SCHEDULED_COMPLETION_DATE = > = (ACTUAL_COMPLETION_DATE >= ENF_ACTION_DATE and ACTUAL_COMPLETION_DATE <= today) or ACTUAL_COMPLETION_DATE = > = If SCHEDULED_COMPLETION_DATE = > = Then ACTUAL_COMPLETION_DATE <> > = If ACTUAL_COMPLETION_DATE = > = Then SCHEDULED_COMPLETION_DATE <> > = CED-020 CED-030 CED-040 CED-050 CED-060 CED-070 CED-080 June 2005 A-103 RCRAInfo Translator Guide-V3 CED Flat File Edit Specifications Edit Number CED-090 Edit Description Milestone Defaulted Date must be a valid date greater than the Milestone Schedule Completion Date and no later than today, or blank. If Milestone Defaulted Date is provided then Milestone Actual Completion Date must be blank. Select Logic (DEFAULTED_DATE >= SCHEDULED_COMPLETION_DATE and DEFAULTED_DATE <= today) or DEFAULTED_DATE = > = If DEFAULTED_DATE <> ‘ ‘ Then ACTUAL_COMPLETION_DATE = ‘ ‘ CED-100 Additional Business Rules and Guidance • The use of CMILESTONE3 is optional. June 2005 A-104 RCRAInfo Translator Guide-V3 FLAT FILE ID# - CEE Table Name: CPENALTY3 CDX Staging Table Name: TR_CEE_V3 Description: Compliance Monitoring and Enforcement Penalty Data Field No. 1 2 3 4 5 6 Field Name TRANSACTION_CODE1 HANDLER_ID1 ENF_IDENTIFIER1 ENF_ACTION_DATE1 ENF_AGENCY1 PENALTY_TYPE1 Starting Column 1 2 14 17 25 26 Field Length 1 12 3 8 1 3 Data Type A A A Date A A Description Transaction Code EPA Handler ID Enforcement Identifier Enforcement Action Date Enforcement Agency Penalty Type Note: Penalty Type Owner will be set to ‘HQ’ in the database. Penalty Amount Notes CEE-005, CEE-010, CEE-035 CEE-005, CEE-010 CEE-005, CEE-010 CEE-005, CEE-010 CEE-020, CEE-040, CEE-050, CEE-060, CEE-070, CEE-080, CEE-090, CEE-100 CEE-030 CEE-100 Edit Number(s) 7 8 AMOUNT NOTES Total Record Length: 29 42 13 2000 2041 D12,2 A 1 The fields that must be provided for Delete transactions (TRANSACTION_TYPE = >D=). June 2005 A-105 RCRAInfo Translator Guide-V3 CEE Flat File Edit Specifications Edit Number CEE-005 CEE-010 Edit Description Handler ID, Enforcement Identifier, Enforcement Action Date, and Enforcement Agency must be provided. Handler ID, Enforcement Identifier, Enforcement Action Date, and Enforcement Agency must exist in CENFORCEMENT3 or CE8. Penalty Type must be a valid nationally defined value registered with the RCRAInfo database. If Penalty Type = >ZAP= then Penalty Amount must equal zero. If Penalty Type <> >ZAP= then penalty amount must be greater than zero. Enforcement Types 100-199 and 800-899 cannot impose a penalty. Enforcement Types 200 - 299 can only impose a >PMP=, >FMP=, >FSC=, >SCR=, or ‘ZAP’ penalty type. Enforcement Types 300 - 399 can only impose a >PMP=, >FMP=, >FSC=, >SCR= or ‘ZAP’ penalty type. Enforcement Types 400 - 499 can only impose a >PMP= or ‘ZAP’ penalty type. Enforcement Types 500 - 599 can only impose a >PMP=, >FMP=, >FSC=, >SCR= or ‘ZAP’ penalty type. Select Logic HANDLER_ID <> > = and ENF_IDENTIFIER <> > = and ENF_ACTION_DATE <> > = and ENF_AGENCY <> > = HANDLER_ID, ENF_IDENTIFIER, ENF_ACTION_DATE, and ENF_AGENCY in CEE must exist in CENFORCEMENT3 or CE8 PENALTY_TYPE = nationally defined penalty type in LU_PENALTY_TYPE If PENALTY_TYPE = >ZAP= Then AMOUNT = 0 If PENALTY_TYPE <> >ZAP= Then AMOUNT > 0 If ENF_TYPE (in CENFORCEMENT3 or CE8) >= >100= and <= >199= or ENF_TYPE (in CENFORCEMENT3 or CE8) >= >800= and <= >899= Then no record in CPENALTY3 or CEE. If ENF_TYPE (in CENFORCEMENT3 or CE8) >= >200= and <= >299= Then PENALTY_TYPE = >PMP= or >FMP= or >FSC= or >SCR= or ‘ZAP’ If ENF_TYPE (in CENFORCEMENT3 or CE8) >= >300= and <= >399= Then PENALTY_TYPE = >PMP= or >FMP= or >FSC= or >SCR= or ‘ZAP’ If ENF_TYPE (in CENFORCEMENT3 or CE8) >= >400= and <= >499= Then PENALTY_TYPE = >PMP= or ‘ZAP’ If ENF_TYPE (in CENFORCEMENT3 or CE8) >= >500= and <= >599= Then PENALTY_TYPE = >PMP= or >FMP= or >FSC= or >SCR= or ‘ZAP’ CEE-020 CEE-030 CEE-035 CEE-040 CEE-050 CEE-060 CEE-070 June 2005 A-106 RCRAInfo Translator Guide-V3 CEE Flat File Edit Specifications Edit Number CEE-080 CEE-090 CEE-100 • Edit Description Enforcement Types 600 - 699 can only impose a >PMP=, >FMP=, >FSC=, >SCR= or ‘ZAP’ penalty type. Enforcement Types 700 - 799 can only impose a >FMP= or ‘ZAP’ penalty type. If Penalty Type is equal to ‘ZAP’ then Notes must be provided. Select Logic If ENF_TYPE (in CENFORCEMENT3 or CE8) >= >600= and <= >699= Then PENALTY_TYPE = >PMP= or >FMP= or >FSC= or >SCR= or ‘ZAP’ If ENF_TYPE (in CENFORCEMENT3 or CE8) >= >700= and <= >799= Then PENALTY_TYPE = >FMP= or ‘ZAP’ If PENALTY_TYPE = ‘ZAP’ Then NOTES <> ‘ ‘ Additional Business Rules and Guidance The use of CPENALTY3 is mandatory. June 2005 A-107 RCRAInfo Translator Guide-V3 FLAT FILE ID# - CEF Table Name: CPAYMENT3 CDX Staging Table Name: TR_CEF_V3 Description: Compliance Monitoring and Enforcement Payment Data Field No. 1 2 3 4 5 6 Field Name TRANSACTION_CODE1 HANDLER_ID1 ENF_IDENTIFIER1 ENF_ACTION_DATE1 ENF_AGENCY1 PENALTY_TYPE1 Starting Column 1 2 14 17 25 26 Field Length 1 12 3 8 1 3 Data Type A A A Date A A Description Transaction Code EPA Handler ID Enforcement Identifier Enforcement Action Date Enforcement Agency Penalty Type Note: Penalty Type Owner will be set to ‘HQ’ in the database. Payment Sequence Number Payment Scheduled Date CEF-005, CEF-010 CEF-005, CEF-010 CEF-005, CEF-010 CEF-005, CEF-010 CEF-005, CEF-010 Edit Number(s) 7 8 PAYMENT_SEQ1 SCHEDULED_DATE 29 31 2 8 I Date CEF-020 CEF-030, CEF-070, CEF-080, CEF-090, CEF-100, CEF-140 CEF-040, CEF-090 CEF-050, CEF-070, CEF-120, CEF-130 9 10 SCHEDULED_AMOUNT PAID_DATE 39 52 13 8 D12,2 Date Payment Scheduled Amount Payment Paid Date 1 The fields that must be provided for Delete transactions (TRANSACTION_TYPE = >D=). June 2005 A-108 RCRAInfo Translator Guide-V3 FLAT FILE ID# - CEF Table Name: CPAYMENT3 CDX Staging Table Name: TR_CEF_V3 Description: Compliance Monitoring and Enforcement Payment Data Field No. 11 12 13 Field Name PAID_AMOUNT DEFAULTED_DATE NOTES Total Record Length: Starting Column 60 73 81 Field Length 13 8 2000 2080 Data Type D12,2 Date A Description Payment Paid Amount Payment Defaulted Date Notes Edit Number(s) CEF-060, CEF-110 CEF-130, CEF-140 June 2005 A-109 RCRAInfo Translator Guide-V3 CEF Flat File Edit Specifications Edit Number CEF-005 Edit Description Handler ID, Enforcement Identifier, Enforcement Action Date, Enforcement Agency, and Penalty Type must be provided. Handler ID, Enforcement Identifier, Enforcement Action Date, Enforcement Agency, and Penalty Type must exist in CPENALTY3 or CEE. Payment Sequence Number must be greater than zero. Payment Scheduled Date must be greater than or equal to the Enforcement Action Date or blank. Payment Scheduled Amount must be greater than zero or blank. Payment Paid Date must be greater than or equal to the Enforcement Action Date and no later than today, or blank. Payment Paid Amount must be greater than zero or blank. If Payment Scheduled Date is not provided then Payment Paid Date must be provided. If Payment Paid Date is not provided then Payment Scheduled Date must be provided. If Payment Scheduled Date is provided then Payment Scheduled Amount must be provided. Select Logic HANDLER_ID <> > = and ENF_IDENTIFIER <> > = and ENF__ACTION_DATE <> > = and ENF_AGENCY <> > = and PENALTY_TYPE <> > = HANDLER_ID, ENF_IDENTIFIER, ENF_ACTION_DATE, ENF_AGENCY, and PENALTY_TYPE in CEF must exist in CPENALTY3 or CEE PAYMENT_SEQ > 0 SCHEDULED_DATE >= ENF_ACTION_DATE or > = SCHEDULED_AMOUNT >= 0 or > = (PAID_DATE >= ENF_ACTION_DATE and PAID_DATE <= today) or PAID_DATE = > = PAID_AMOUNT >= 0 or > = If SCHEDULED_DATE = > = Then PAID_DATE <> > = If PAID_DATE = > = Then SCHEDULED_DATE <> > = If SCHEDULED_DATE <> > = Then SCHEDULED_AMOUNT > 0 CEF-010 CEF-020 CEF-030 CEF-040 CEF-050 CEF-060 CEF-070 CEF-080 CEF-090 June 2005 A-110 RCRAInfo Translator Guide-V3 CEF Flat File Edit Specifications Edit Number CEF-100 CEF-110 CEF-120 CEF-130 CEF-140 Edit Description If Payment Scheduled Amount is provided then Payment Scheduled Date must be provided. If Payment Paid Date is provided then Payment Paid Amount must be provided. If Payment Paid Amount is provided then Payment Paid Date must be provided. If Payment Defaulted Date is provided then Payment Paid Date must be blank. Payment Defaulted Date must be greater the Payment Scheduled Date and not later than today, or blank. Select Logic If SCHEDULED_AMOUNT > 0 Then SCHEDULED_DATE <> > = If PAID_DATE <> > = Then PAID_AMOUNT > 0 If PAID_AMOUNT > 0 Then PAID_DATE <> > = If DEFAULTED_DATE <> ‘ ‘ Then PAID_DATE = ‘ ‘ (DEFAULTED_DATE >= SCHEDULED_DATE and DEFAULTED_DATE <= today) or DEFAULTED_DATE = ‘ ’ Additional Business Rules and Guidance • The use of CPAYMENT3 is optional. June 2005 A-111 RCRAInfo Translator Guide-V3 FLAT FILE ID# - CEG Table Name: COMMITMENT3 CDX Staging Table Name: TR_CEG_V3 Description: Compliance Monitoring and Enforcement Commitment Data Field No. 1 2 3 4 5 Field Name TRANSACTION_CODE1 COM_LEAD1 COM_SEQ1 HANDLER_ID COM_TYPE Starting Column 1 2 4 10 22 Field Length 1 2 6 12 20 Data Type A A I A A Transaction Code Commitment Lead Location Commitment Sequence Number EPA Handler ID Commitment Type Note: If Commitment Type is nationally defined, Commitment Type Owner will be set to ‘US’ in the database. If Commitment Type is implementer defined, Commitment Type Owner will be set to your user agency in the database. Federal Fiscal Year CEG-010 CEG-020 CEG-030 CEG-040 Description Edit Number(s) 6 FEDERAL_FY 42 4 A CEG-050, CEG-080, CEG-090 CEG-060, CEG-080, CEG-090 7 START_DATE 46 8 Date Commitment Start Date 1 The fields that must be provided for Delete transactions (TRANSACTION_TYPE = >D=). June 2005 A-112 RCRAInfo Translator Guide-V3 FLAT FILE ID# - CEG Table Name: COMMITMENT3 CDX Staging Table Name: TR_CEG_V3 Description: Compliance Monitoring and Enforcement Commitment Data Field No. 8 Field Name END_DATE Starting Column 54 Field Length 8 Data Type Date Description Commitment End Date Edit Number(s) CEG-070, CEG-080, CEG-090 9 NOTES Total Record Length: 62 2000 2061 A Notes June 2005 A-113 RCRAInfo Translator Guide-V3 CEG Flat File Edit Specifications Edit Number CEG-010 CEG-020 CEG-030 CEG-040 CEG-050 Edit Description Commitment Lead Location must equal the state of the submission or the region the state is contained in. Commitment Sequence Number must be greater than zero. If Handler ID is provided then Handler ID must exist in HBASIC or HD1. Commitment Type must be a valid nationally or implementer defined value registered with the RCRAInfo database. If Commitment Type is owned by an EPA Region or Headquarters then Federal Fiscal Year must be greater than or equal to >2005=. If Commitment Type is owned by a state then Federal Fiscal Year must be greater than or equal to >2005= or blank. Commitment Start Date must be a valid date or blank. Commitment End Date must be a valid date on or after Start Date or blank. If Federal Fiscal Year is not provided then Commitment Start Date and End Date must be provided. If Commitment Start Date and End Date are not provided then Federal Fiscal Year must be provided. Select Logic COM_LEAD = state of the submission or region the state is contained in. COM_SEQ > 0 If HANDLER_ID <> > = Then HANDLER_ID in CEG must exist in HBASIC or HD1 COM_TYPE = nationally or implementer defined commitment type in LU_COMMITMENT_TYPE If COM_OWNER = >HQ= or >01= or >02= or >03= or >04= or >05= or >06= or >07= or >08= or >09= or >10= Then FEDERAL_FY >= >2005= If COM_OWNER = valid state postal code Then FEDERAL_FY >= >2005= or > = START_DATE = valid date or > = END_DATE >= START_DATE or > = If FEDERAL_FY = ‘ ‘ Then START_DATE <> ‘ ‘ and END_DATE <> ‘ ‘ If START_DATE = ‘ ‘ or END_DATE = ‘ ‘ Then FEDERAL_FY <> ‘ ‘ CEG-060 CEG-070 CEG-080 CEG-090 Additional Business Rules and Guidance • • COMMITMENT3 data is released only when it has been linked to at least one evaluation in CEVALUATION3 or CE1. The use of COMMITMENT3 is optional. A-114 RCRAInfo Translator Guide-V3 June 2005 APPENDIX B State Postal Codes APPENDIX B - State Postal Codes State Name Alabama Alaska American Samoa Arizona Arkansas California Colorado Connecticut Delaware District of Columbia Florida Georgia Guam Hawaii Idaho Illinois Indiana Iowa Kansas Postal Code AL AK AS AZ AR CA CO CT DE DC FL GA GU HI ID IL IN IA KS State Name Kentucky Louisiana Maine Maryland Massachusetts Michigan Minnesota Mississippi Missouri Montana Navajo Nation Nebraska Nevada New Hampshire New Jersey New Mexico New York North Carolina North Dakota Postal Code KY LA ME MD MA MI MN MS MO MT NN NE NV NH NJ NM NY NC ND State Name Ohio Oklahoma Oregon Pennsylvania Puerto Rico Rhode Island South Carolina South Dakota Tennessee Texas Trust Territories Utah Vermont Virgin Islands Virginia Washington West Virginia Wisconsin Wyoming Postal Code OH OK OR PA PR RI SC SD TN TX TT UT VT VI VA WA WV WI WY June 2005 B-1 RCRAInfo Translator Guide-V3