Flat File Translation / BR Load

1. Flat File Translation / BR Load 2010 RCRAInfo National Users Conference

2. Lori FurrEPA – HeadquartersOffice of Resource Conservation and Recovery----------------------------------------------------------Brenda SixburyDPRA Incorporated

3. What is Translation? • Mechanism to provide data to RCRAInfo • Flat Files • XML

4. Why Do We Need Translation? • State Systems • Biennial Report

5. Flat File Formats • Fixed-format ASCII text files • Each submission contains flat files for a specific module • File formats / edit specifications • Flat File Translator Guide (Handler, Permitting, Corrective Action, CM&E, Financial Assurance, GIS) • 2009 Hazardous Waste File Specification Guide (Biennial Report)

6. Flat File Formats (cont.) • Flat files are “zipped” for submission • Zip file name – RCRssnnuuu.ZIP (ex. RCRFL01SIX.ZIP) • ss = State Postal Code • nn = Numeric Designation • uuu = RCRAInfo User ID

7. Translation Types • Full Replace • Available for Handler, CM&E, and 2009 Biennial Report • Deletes ALL existing data owned by your agency for the module specified (deletes ALL 2009 BR data) • Loads the data contained in the submission • TRANSACTION_CODE in all flat files must be blank (does not apply to BR submissions) • If the data does not meet the edit specifications, no data will be deleted or loaded

8. Translation Types (cont.) • Partial • Available for Handler, CM&E, and Biennial Report (Prior to 2009) • Same as Full Replace, except for identified handlers only • Deletes ALL existing data for the module specified owned by your agency for the handler(s) specified • Loads the data contained in the submission • TRANSACTION_CODE in all flat files must be blank (does not apply to BR submissions) • If the data does not meet the edit specifications, no data will be deleted or loaded

9. Translation Types (cont.) • Delete • Available for Biennial Report only (Prior to 2009) • Deletes ALL existing data for the Biennial Report cycle specified for the handler(s) specified • Submit SI1 file (Handler ID and Notes fields only) • If the data does not meet the edit specifiations, no data will be deleted

10. Translation Types (cont.) • Transactional • Processes submission on a record-by-record basis • Not available for Biennial Report • To Add or Update records, TRANSACTION_CODE = ‘A’ • To Delete records, TRANSACTION_CODE = ‘D’ • Records are processed in sequential order

11. Permissions • Implementer or Record (IOR) • IOR for the module (Fixed IOR) • IOR for the agency records (Variable IOR) • Permissions • Level 4 (Read/Add/Update/Delete) permissions for the module • Translation Role (CDXTRANSFER) • BR Load Flag (for BR loads only)

12. Uploading Data • RCRAInfo Main Menu / Upload Data • Specify Module Type

13. Uploading Data (cont.) • Specify Upload Type • Specify Report Cycle (BR uploads only)

14. Uploading Data (cont.) • Specify Flat File • Click “Load Data”

15. Uploading Data (cont.) • From the Upload as Flat Files Screen … • Verify the flat files in the zip file • Click “Initialize and Load Staging Tables”

16. Uploading Data (cont.) • Data is loading into the staging tables when …

17. Uploading Data (cont.) • Confirmation Screen – Click “Continue”

18. Uploading Data (cont.) • Confirmation Screen with Errors – Click “with errors” link to see error message

19. Uploading Data (cont.) • SQL Server Error Message - Click “Back to Upload Confirm”

20. Uploading Data (cont.) • Status Report – Click “Run Report”

21. Uploading Data (cont.) • Verify the number of records received

22. Uploading Data (cont.) • No errors found

23. Uploading Data (cont.) • Errors found

24. Uploading Data (cont.) • Data loaded into the RCRAInfo production tables

25. Uploading Data (cont.) • Errors messages

26. Uploading Data (cont.) • Translation has successfully completed • Translation has not successfully completed

27. Questions?