NUFinancials General Ledger FMS714
Uploading Journal Spreadsheets This training guide describes the process for uploading journal spreadsheets (flat files) into NUFinancials to be processed for posting to the General Ledger. For this process, text file and flat file have the same meaning (are the same thing). Registered users will use an Excel template to record journal lines. These journal lines may include any combination of ChartFields for general purposes (including balance sheet transactions and adjusting and correcting entries); but there can only be one journal purpose per spreadsheet journal entry. The journal spreadsheet may contain hundreds of lines, but will be processed as one journal. After the journal has been approved by Accounting Services, the Excel file is converted into a text file (flat file) and is uploaded into NUFinancials. The journal must be edited and budget checked before it can post.
Before you begin . . . Upon completion of this guide you will be able to: • Convert the spreadsheet template into a flat file • Upload the flat file into NUFinancials • Process the flat file journal • Recognize and troubleshoot errors
Who has access? • Journal spreadsheets can be submitted by a limited number of registered users in the university community. • Access to convert the excel template into and upload these flat file journals is given to Accounting Services.
Things you need to know before attempting this process: • Knowledge of NUFinancials ChartFields and chart strings • Knowledge of correct configuration of journal lines • Your department’s Journal mask (if applicable) Additional Resources • Visit the FFRA website for additional training materials http://ffra.northwestern.edu/training/ • Visit the NUPortal to access the Chart of Accounts crosswalk or Chart of Accounts Quick Reference Guide. • Review the 90-day policy for journal entries http://www.northwestern.edu/financial-operations/policiesprocedures/forms/90-Day_Memo_Form_and_Instructions.pdf Where do I get help? For assistance, contact NUIT Support Center at 847-491-HELP (4357) or e-mail
[email protected]. Uploading Journal Spreadsheets 5/5/2014
© 2014 Northwestern University GL714
1
NUFinancials General Ledger FMS714
Step 1: The person who uploads the Journal Spreadsheet will do the following 1. Retrieve the emailed spreadsheet from the Upload Journal mailbox. ‘Mask’ should prefix in subject line of email and first three letters of spreadsheet file name. 2. Name the file and save it in the designated place (follow Accounting Services business processes). 3. Open the Excel file. 4. Note the Journal Mask (if a mask is listed on the spreadsheet, you will enter it into NUFinancials in a later step.) A journal mask/department mask is a 3-digit code that identifies your department as the owner of the spreadsheet. 5. Verify Journal Spreadsheet meets the following criteria: •
Journal Total box = $0.00. This means that the debits and credits entered in the lines total 0.00 and are balanced.
•
Required Fields Check = OK
•
Journal Date is within the correct accounting period.
•
Header Description is unique.
•
Line Descriptions are unique, detailed, and any dates meet the 90 day rule.
•
Journal Spreadsheet meets 15 lines minimum (except Balance Sheet journals).
•
ISJ’s must contain 7 and 5 series accounts; only one chartstring may contain a 5 series account
•
Correction Journal accounts are all 4 or 7 series.
•
Transfer journal accounts begin with 8.
•
Balance Sheet Journal accounts begin with 1 or 2.
Uploading Journal Spreadsheets 5/8/2014 - rb
© 2014 Northwestern University
GL714
2
NUFinancials General Ledger FMS714
Step 2: Enable and Run the macros
This Excel file requires the use of macros. Your security level must be low enough to allow macros to operate. If your security level is too high, a dialog box appears. Follow the instructions in the box to lower your macro security level.
2. Click 3. Click •
The Save As box appears. Uploading Journal Spreadsheets 5/8/2014 - rb
© 2014 Northwestern University
GL714
3
NUFinancials General Ledger FMS714
Step 3: Save the spreadsheet as a text file
• •
Remember where you save the file. You will need to find the file later to upload it into NUFinancials. Keep the Excel file until you have successfully imported the text file into NUFinancials.
Uploading Journal Spreadsheets 5/8/2014 - rb
© 2014 Northwestern University
GL714
4
NUFinancials General Ledger FMS714
Step 4: Create a new/find an existing Run Control ID Navigate to the file upload area in NUFinancials: General Ledger > Journals > Import Journals > External Flat File > Add a New Value
Reusing Run Control IDs 1. If you already have a Run Control ID setup for flat file uploads, click the Find an Existing Value tab, and then click Search 2. Click the name of the flat file run control. 3. Click Delete to delete the old file, and then click Add to add a new file to the upload. 4. Browse for a new file and proceed with the upload process.
Uploading Journal Spreadsheets 5/8/2014 - rb
© 2014 Northwestern University
GL714
5
NUFinancials General Ledger FMS714
Step 4a: Attach the file to the Run Control
Uploading Journal Spreadsheets 5/8/2014 - rb
© 2014 Northwestern University
GL714
6
NUFinancials General Ledger FMS714
Step 4b: Attach the file to the Run Control (continued)
•
The Flat File Journal Import Request page appears.
Uploading Journal Spreadsheets 5/8/2014 - rb
© 2014 Northwestern University
GL714
7
NUFinancials General Ledger FMS714
Step 5: Run the process to import the journal
•
The Attached File name should be the name of the file you want to import. If it is not, click Delete, and then click Add upload the correct file.
Uploading Journal Spreadsheets 5/8/2014 - rb
© 2014 Northwestern University
GL714
8
NUFinancials General Ledger FMS714
Step 5a: Schedule the process
Uploading Journal Spreadsheets 5/8/2014 - rb
© 2014 Northwestern University
GL714
9
NUFinancials General Ledger FMS714
Step 5b: Note the Process Instance and Open the Process Monitor
•
The Process Instance number is used to track the scheduled process and for troubleshooting, if needed.
Uploading Journal Spreadsheets 5/8/2014 - rb
© 2014 Northwestern University
GL714
10
NUFinancials General Ledger FMS714
Step 6: Monitor the Run Status
• • •
If The Run Status for your instance says Success, then the journal was imported. If the Run Status says No Success, there were errors in the flat file and the journal was not created. See Troubleshooting Flat File Upload Error Messages at the end of this guide.
Uploading Journal Spreadsheets 5/8/2014 - rb
© 2014 Northwestern University
GL714
11
NUFinancials General Ledger FMS714
Step 7: View the Message Log
Uploading Journal Spreadsheets 5/8/2014 - rb
© 2014 Northwestern University
GL714
12
NUFinancials General Ledger FMS714
Step 7a: View the message log (continued)
Uploading Journal Spreadsheets 5/8/2014 - rb
© 2014 Northwestern University
GL714
13
NUFinancials General Ledger FMS714
Step 8: Check log for the Journal ID number The Process Detail page appears:
Uploading Journal Spreadsheets 5/8/2014 - rb
The view/trace log file appears:
© 2014 Northwestern University
GL714
14
NUFinancials General Ledger FMS714
Step 9: Find the Imported Journal Navigate to the Journal Entry area in NUFinancials: General Ledger > Journals > Journal Entry > Create/Update Journal Entries
Uploading Journal Spreadsheets 5/8/2014 - rb
© 2014 Northwestern University
GL714
15
NUFinancials General Ledger FMS714
Step 10: Attach Files
Uploading Journal Spreadsheets 5/8/2014 - rb
© 2014 Northwestern University
GL714
16
NUFinancials General Ledger FMS714
Step 10a: Attach Files (continued)
The file will appear in the Details section:
•
To remove a file, click the – minus symbol.
•
Click Save (shown on previous page 16)
Uploading Journal Spreadsheets 5/8/2014 - rb
© 2014 Northwestern University
GL714
17
NUFinancials General Ledger FMS714
Step 11: Edit Journal
The Edit Journal Process also performs a Budget Check. If successful, the Journal Status and Budget Status will update to “V” (valid). Uploading Journal Spreadsheets 5/8/2014 - rb
© 2014 Northwestern University
GL714
18
NUFinancials General Ledger FMS714
Step 12: Approval
What happens next? • Follow the Accounting Services business process for communicating the Journal ID to the journal creator. • If the journal should be posted immediately, see the Manually Batch Editing and Posting Actual Journals training guide to learn how to create a Run Control for batch posting. • For more information about the Excel journal spreadsheet see the Creating a Journal Spreadsheet training guide.
Uploading Journal Spreadsheets 5/8/2014 - rb
© 2014 Northwestern University
GL714
19
NUFinancials General Ledger FMS714
Troubleshooting Flat File Upload Error Messages If the Run Status for your instance number says No Success, there were errors in the flat file or no flat file was uploaded. If there was an error, a journal was not created. No journal lines were imported. The error must be corrected in the Excel journal spreadsheet and the regenerated text file must be uploaded again. 1. Check the View/Log Trace to see what errors occurred. • From the Process Detail page (the page where the message log is accessed), click Details View/Log Trace • From the File List, click on the file name GL_Journal_IMP_xxxxx.log (where xxxxx = the number of the instance of the run process). • The log lists the error messages for the flat file. Use the table below to guide you in fixing the error. Error or Message
Description
Action
Journal Line errors
When the file is imported, the ChartField value is checked. If the value is not valid, this error message is generated. The import process does not check for valid chart string combinations.
Update the Excel spreadsheet with correct ChartField values, and then regenerate the text file and repeat the flat file upload process.
Total lines do not match control total.
Total Journal line numbers total debits and/or credits are not correct.
Correct errors in the Excel template then regenerate the text file and repeat the flat file upload process.
File not found
There is no file attached to the run control for the upload process.
Upload a new file and attach it to the run control, repeat the upload process.
2. Fix the error and rerun the process.
Uploading Journal Spreadsheets 5/8/2014 - rb
© 2014 Northwestern University
GL714
20
