ARRA Reporting

1. ARRA Reporting December 9th, 2009

2. Introductions • Brandy Manek (OSF – Budget) • Brenda Bolander (OSF - DCAR) • Steve Funck (OSF - DCAR) • Lisa Hodges (SAI) • Sammy Paulson (OSF - ISD) • Janice Wolfenbarger (OSF - ISD) • Lisa McKeithan (OSF - ISD) • Linda Belinski (OSF – Security)

3. Agenda • Introductions • Recap of 09/30/2009 Reporting Period • Looking forward to 12/31/2009 Reporting Period • CSV Files • FTP Process • Load Control Reporting • federalreporting.gov • Questions and Answers

4. Thank You! • Good Job! • Kudos!

5. Recap of 09/30/2009…Federal • Unprecedented collaborative effort between the federal agencies and states (as well as between federal agencies!) • Unprecedented release of funds into communities • This type of transparency will likely be used as model moving forward i.e. FFATA

6. Recap of 09/30/2009…Federal Things to improve upon: • Jobs calculations continue to be an issue and agencies are receiving conflicting information • States have requested no major changes in this reporting period due to holidays, furloughs, etc. • OMB is considering minor changes for this reporting period. • Too many people were providing conflicting information during the review period.

7. Recap of 09/30/2009…Federal Things to improve upon (continued): • Individual federal agencies were interpreting the federal guidelines themselves • No written responses from feds and states were getting conflicting information from the feds with no written backup • HelpDesk training • Communications – focus is on jobs impact, is that really fair?

8. Recap of 09/30/2009…State • There were a few reporting glitches, but overall – great job! • OSF continued to add edits to the load program as errors were detected and that proved beneficial. • Recovery.gov statistics

9. Looking Ahead – Federal • Feds will perform edits checks on data • Updated guidance is expected from OMB covering: • Jobs with examples from various areas (DOJ, DOE, etc) • ‘W2’ for awards (std format) • Ability to fix and republish data into Feb & March

10. Looking Ahead Goal: Streamlined Process for Agencies • Submit files to FTP site (test or prod). • Review Load Control Reports to see rejections or Transparency Reports to see Award details • Review federalreporting.gov (using State DUNS# 809929821) to review submitted reports

11. Looking Ahead – CSV Files Lessons Learned • After a file is saved, you can verify the contents by opening it in Notepad or a similar tool. Check for hard returns, spaces, leading zeros. • You might need to put a single quote in front of a leading zero so it will be retained. • Check the number of characters in a column to insure you do not exceed the allowable maximum. To do this, copy the data and paste it in Word and then go to File Properties and then to the Statistics tab to check.

12. Looking Ahead – CSV Files Lessons Learned • Changes in key fields will create a new row rather than overwrite the existing row. • Recipient file data (File1) must load successfully before Sub Recipient (File2) or Vendors (File3) may be loaded.

13. Looking Ahead – CSV Files Lessons Learned • Passthroughs – we had a problem with the load program allowing the recipient receiving the passthrough to overwrite data from the Prime Recipient. We are fixing this. But, this will mean that for any given reporting period, the Prime Recipient must load its award information before the recipient receiving the passthrough can load its information.

14. Looking Ahead – CSV Files Lessons Learned – Passthroughs (continued) • Agency receiving the award is the ‘recipient’. If they provide the information to another state agency, the OMB guidelines state that the information should be reported as a ‘prime’ also. OSF has asked that each agency report its information separately and the detail information will be available for transparency reporting but the information will be combined for federal reporting. • The text fields (award/project/job description, cfda, etc.) will be reported to the feds based on the information submitted by the agency receiving the award. • The amount fields will be combined from each agency’s records for federal reporting. • For example: • Agency A receives an award for $100 and gives $40 to agency B. • Each agency reports its data separately as follows:

15. Looking Ahead – CSV Files Lessons Learned – Passthroughs (continued)

16. Looking Ahead – CSV Files Lessons Learned – Passthroughs (continued)

17. Looking Ahead – CSV Files Lessons Learned – Passthroughs (continued) • The agency(ies) receiving money from the primary agency will put ‘PASSTHROUGH’ in the Recipient Activity field (file1). This allows the system to identify the primary agency from the passthrough agencies in order to accurately report the information to the feds. • As data is loaded, only the ‘primary’ agency will be able to update the award information (funding agency, cfda, awarding agency, TAS code, etc). • If the file1 has ‘PASSTHROUGH’ in the Recipient Account field, then the award must already exist before the information will be inserted/updated in the data warehouse. If the award does not exist, then an error will be generated.

18. Looking Ahead – CSV Files • Division Codes: Must be created in the system before it can be used. Submit a help desk case noting the new division and the contact information for the division. • New fields (April 2010): • Deactivate flag: deactivates information so that it will not be submitted to the feds. • Activity Codes: Up to 10 codes allowed. NOTE: For the January reporting period, please file a help desk case and we will make manual adjustments (like we did in the first reporting period). • New file type for non-1512 reporting (File4):

19. Looking Ahead – FTP Process New Test Environment - Only accessibly through FTP • Host: www.ok.gov • Userid: osfxfert • Password: n39pqrxZ Once connected, you will see the file Testing_site.txt. This is confirmation that you are on the TEST site.

20. Looking Ahead – FTP Process Production Environment – If you have not received your userid and password, contact the OSF HelpDesk and ask for ARRA Online Filing access. See ‘Help’ tab on Load Control Reports for more information.

21. Looking Ahead – Load Control Reports Test Environment url – https://arradev.ok.gov Production Environment url - https://arra.ok.gov

22. Looking Ahead – Load Control Reports To request a userid for Load Control Reports, download OSF Form 301BIAPP, Security Access Form from the CORE Web site: http://www.ok.gov/OSF/CORE/Security/index.html

23. Looking Ahead – Load Control Reports Demo • Load Control Reports • Data Load Summary Tab • Agency Data Load Summary by Awards • L-8 Incoming Data Files Reports (downloads) • L-1 Agency Contact List • Data File Issues Tab • L-6 Award Recipient Rejections Summary • L-6 Sub Recipient Rejections Summary • L-6 Vendor Payment Rejections Summary • ‘Help’ tab NOTE: We are still verifying data on reports, so if you notice any errors, let us know and we will try to get them fixed.

24. Looking Ahead – federalreporting.gov In January, once the awards have been loaded to the federal Web site, each agency must review its reports on federalreporting.gov to insure the information is complete and accurate. Register using DUNS number: 809929821 If you already have an account setup, go to Quick Links > Administration > My Account. Select the ‘Edit Information’ button and change the DUNS number, click the ‘Submit’ button.

25. ARRA Reporting – Next Steps • January 1, 2010 is a Friday and a holiday. • January 10, 2010 is a Sunday. • This means that Jan 4 – 8 are the only ‘working days’ during that reporting period. • Start planning now! • You may upload data to the production site starting ??/??/?? • Higher Ed – what is your schedule?

26. ARRA Reporting – Q & A Questions and Answers