Reviewing and Uploading Spreadsheet Journal Entries

Overview

After Creating Spreadsheet Journal Entries, it's time to review and upload these entries. Spreadsheet journal entries are created by the agency GL processor when creating a payroll correction journal or payroll reclassification journal. The file created by the GL processor is sent to an agency GL approver who reviews the journal lines and approves the spreadsheet and uploads it. The method of sending the file to the agency GL approver varies by agency. Journal entries require double-sided entries (otherwise known as double-entry accounting).

Steps

• Open the GL_Journal_Spreadhseet_Upload_JRNL2_WS.xlsm Excel file

1. If Excel displays a Security Warning, choose to Enable Content.

• This warning is caused by macros contained in the file to make it work properly.

• The Spreadsheet Journal Import control page displays.

2. Click Edit under Journal Sheets heading.

• The Edit Journal Sheet window appears.

3. Select the journal sheet to review and upload.

4. Click OK.

• The spreadsheet opens in edit mode.

5. Verify the accuracy of the journal lines in the spreadsheet. Focus on verifying that:

1. • All journal lines balance.

   • Account coding is correct.

6. If errors exist, do one of the following:

1. • Locate the correct information and update the file.

   • Send an email to the GL journal processor requesting revisions.

7. When all journal lines have been corrected and reviewed, click Save.

8. Click the Home button within the spreadsheet.

• The main menu of the journal spreadsheet displays.

9. Click Write File in the Import Journals section.

• The Write Journals to File window opens.

• This is the beginning of the process to actually upload the spreadsheet. This action converts the spreadsheet data into a format that can be uploaded into OAKS FIN.

10. Select the spreadsheet(s) to convert for uploading.

• To select all, click All. Each agency decides where the spreadsheet files should be saved. Ask a supervisor about local procedures.

11. Verify that the File name at the bottom of the window displays the correct location for agency file uploads.

12. Click OK.

• https://ohid.ohio.gov/wps/myportal/gov/myohio/ > Financials > General Ledger Tile > Spreadsheet Journals

• Users who have uploaded a spreadsheet previously should skip to Step #15. Otherwise, follow these steps to create a Run Control ID:

13. Click the Add a New Value tab.

• The Add a New Value page displays.

• For Run Control ID, enter a short descriptive name that will be recognized later (up to 30 characters; do not use special characters - #, $, %, space, etc.

14. Click Add

• The Spreadsheet Journal Import Request page displays.

• Skip to Step #17.

15. For users who have uploaded a spreadsheet previously:

    1. Click the link of the desired Run Control ID displayed under Search Results.

    2. Click Search.

    3. If necessary, change the dropdown menu from default of "begins with" to "contains" or another variable for the partial ID entered.

    4. On the Find an Existing Value tab page, enter all or part of the existing Run Control ID.

• The Spreadsheet Journal Import Request page displays.

16. Verify or edit the If Journal Already Exists field. The value should be:

    • Update - If correcting a journal with errors.

    • Skip - Any other journal file.

• The Character Set and If Journal is Invalid fields are automatically populated.

17. Click Add to import the spreadsheet file.

• The File Attachment window opens.

18. Click Browse.

• The Choose File to Upload window displays.

19. Navigate to and select the spreadsheet journal(s) to be uploaded.

• Import only the files ending in ".txt"; do NOT import the files ending in ".xlsx."

20. Click Open.

• The File Attachment window fills in the path to the selected file.

21. Click Upload.

• OAKS FIN re-displays the Spreadsheet Journal Import Request page.

• The Attached File field lists the selected text filename(s).

22. Click Save.

23. Click Run.

24. Select process output types and run options according to agency-specified standards.

• Do not change the Server Name, Recurrence, or Time Zone fields. If these fields are changed, the process may not run when expected.

25. Click OK.

• The upload begins.

• The Process Instance number is generated and displays beneath the Process Monitor link.

  • Write down this number for tracking reference.

  •   The Process Instance number is used to track the results of the upload.

26. Click the Process Monitor link.

• The Process List tab of the Process Monitor page displays.

• The Process List tab lists all the processes that OAKS FIN is running for a specific user on a specific server in the Process List section.

27. Use the Process Instance number to find the job just created.

28. Click Refresh on the OAKS FIN web page (not the refresh or reload button of the web browser).  Repeat this about every 30 seconds or so until the Run Status field displays "Success" and the Distribution Status is "Posted."

    • If OAKS FIN displays several rows, use the process instance number to find the process.

    • The Refresh button is NOT the web browser's Refresh button. It is one on the web page. Avoid clicking it too frequently as this can overload the server.

29. After the process is successful and status is posted, click the Details link.

• The Process Detail window opens.

30. Click on View Log/Trace link.

• The View Log/Trace window opens.

31. Examine the messages in the ".LOG" file.

    1. Verify that all journals were imported.

    • Best practice is that any corrections to spreadsheet journals be made in the original spreadsheet journal and re-uploaded. This keeps files in sync with OAKS FIN for auditing purposes.

32. If necessary, correct the spreadsheet, save it, and return to the Excel Spreadsheet Journal Import page by completing the following steps:

    1. Click the Home button of the spreadsheet.

    2. The main menu of the journal spreadsheet displays.

    3. Click the Edit button.

    4. Make the required changes to the spreadsheet and save it.

    5. Click Write File.

    6. OAKS FIN automatically displays "Skip" in the If Journal Already field. When uploading a corrected spreadsheet from Excel to OAKS FIN change the If Journal Already Exists field to "Update." When Update is selected, OAKS FIN replaces the previous file with the new file.

Next steps are detailed in the topic Processing Spreadsheet Journals.