Customizing Journal Import Template

Purpose: Use this document as a reference for understanding how to set user defaults and customize the Journal Import Template in ctcLink.

Audience: Financial Staff/Supervisors.

You must have at least one of these local college managed security roles:

  • ZZ GL Journal Entry

You must also set these User Preference Definitions:

If you need assistance with the above security roles or User Preference Definitions, please contact your local college supervisor or IT Admin to request role access.

PREREQUISITE: In order to customize the journal import template, the following files must be present on your workstation (For Microsoft Excel 2007 and subsequent versions) and be saved to the same folder on your computer. Please see QRG Journal Spreadsheet Template for step-by-step directions how to download the files to your workstation:

  • JRNL1_WS.xlsm – This is the journal workbook that you use to create and import journals.
  • JRNLMCRO_WS.xlam – This is a Macro; the Visual Basic code library and dialog control.
  • GLLOG.XLT - This is a Macro; the Visual Basic code log control.

Spreadsheet Journal Import Control Overview

PREREQUISITE: Use the JRNL1_WS.xlsm file to enter your journals. You must set up Microsoft Excel to accept macros, by navigating to File > Options > Trust Center > Trust Center Settings > Macro Settings and selecting either Disable all macros with notification, or Enable all macros.

Open the JRNL1_WS Excel file to view the control buttons on the Control page are grouped into three group boxes:

  • General - Setup workbook defaults, configure ChartFields, and rearrange columns.
  • Journal Sheets- Maintain the journal sheets in the workbook.
  • Import Journals - Imports the journal sheets from the workbook.
  1. The General group box enables you to define options and defaults for this workbook and to enter any notes or calculations concerning imports.
    • Setup & Defaults – Select to set up access to the Define Options and Defaults dialog box. Use to set journal header defaults, message logging options, document sequencing options, online import controls, and so on. You can also access the ChartField Configuration dialog box from here.
    • Notes – Select to access a scratch pad in the workbook. Use the scratch pad for instructions, calculations, notes, and so on. From the Notes page select the Home icon to return to the Spreadsheet Journal Import control page.
  2. The Journal Sheets group box enables you to insert a new journal sheet; or edit, delete, or copy an existing journal sheet.
    • New Sheet – Select to insert a new journal sheet. A workbook may contain as many journal sheets as needed, and each journal sheet may contain as many journals as desired.
    • Edit Sheet – Select to edit one journal sheet in the workbook.
    • Delete Sheet – Select to delete one or more journal sheets in the workbook.
    • Copy Sheet – Select to copy one journal sheet to a new journal sheet saved under a new name.
  3. The Import Journals group box enables you to import one or more of the journal sheets and save journal sheets to a file. There are two options to import a journal spreadsheet into ctcLink PeopleSoft;
    • Import Now – Select to initiate online import of one or more journal sheets. The system imports only journals that are marked as import. This process requires a pre-configured user ID and password.
    • Write to File – Select to save selected journal sheets to a text (txt) file. After saving one or more files, you must run the upload process to complete the text file upload process. This process does not require a user ID and password however, it requires you to run an additional process within ctcLink.
Spreadsheet journal import header page

Before you start entering journals, you must specify the options, defaults, and settings for the journal sheets in your workbook.

Setup & Defaults

Before you start entering journals, you must specify the options, defaults, and settings for the journal sheets in your workbook. From the Spreadsheet Journal Import control page, select the Setup & Defaults button to display the Define Options and Defaults dialog box.

  1. From the Spreadsheet Journal Import control page, select the Setup & Defaults button. The Define Options and Defaults dialog box displays. Note some of the options are auto-filled by default. Some of the following options may have been configured for your college.
  2. Enter the following Header and spreadsheet default options;
    1. Business Unit: enter your Business unit.
    2. Date: enter a default Journal date. The date can be overridden on the headers on the individual sheets.
    3. Ledger Group: enter 'ACTUALS'. Majority of journal imports will be 'Actuals' however, ledger group may differ if directed by SBCTC.
    4. Source: enter 'EXT'. Majority of journal imports will be EXT however, there are a few exceptions and if directed by SBCTC.
    5. User ID: enter your user ID for the journal header.
    6. Do not check Enable Multibook.
  3. Recommended to mark the boxes: Edit Journal(s) and Budget Check Journals.
  4. Choose from one of the two Message Options radio button:
    • Log Error Messages Only: The system logs import messages to JRNLLOG.xls only when errors occur.
    • Log Successful and Error Messages: The system logs all import messages to JRNLLOG.xls regardless of the import process being successful (RECOMMENDED).
  5. Do not select the Enable Document Sequencing checkbox or enter a Default Document Type.
  6. Complete the following fields for Online Import Control: Address: the address will already be populated with the correct information, i.e. Production address: https://fsprd.ctclink.us:443/
  7. In the After Successful Import section:
    • Change Import Status to ‘Do Not Import’: The system changes import status of journals that are imported successfully. This prevents reimport of the journals when you attempt to import them a second time.
    • Keep Import Status as ‘Import’: Select this if you do not want the system to change import status so that you can reimport it later. You can override journal import status at the journal sheet level anytime.
    • Skip If Journal Already Exits: Select this option and online load does not update already existing journals. The option is intended to prevent duplicate journals when a user is unaware that a journal has already been processed. This option is the default and is only applicable for online import using the Import Now functionality.
    • Skip If Journal Has Error: Select this option and online load does not load invalid journals. This option is the default and is only applicable for online import using the Import Now functionality. A message is logged providing details of journals that have invalid data. If the option is not selected then the online load does not load either valid or invalid journals for that journal sheet. Online load then proceeds to the next journal sheet and loads all journals in the next journal sheet if all the journals in the next journal sheet are valid. If the option is not selected, online load updates already existing journals with valid journal data.
  8. Select the OK button to save any changes.
  9. On the Define Options and Defaults window, select the Configure button to navigate to the Journal Entry Sheet.
Spreadsheet journal import control page
  1. The Journal Entry Sheet displays with the Chartfield configuration dialog box window.
  2. Let's first explore the Column tab options. Select anywhere inside the spreadsheet to view the selected or highlighted column.
    • Select Column section selected the column to perform additional customization:
      • Left Yellow Arrow icon - moves the selected column (grayed out) field to the left.
      • Right Yellow Arrow icon - moves the selected column (grayed out) field to the right.
    • Column section provides options to perform actions to the selected column:
      • Use the Column left button icon to shift selected column to the left.
      • Use the Column insert/delete button icon to insert/delete a new column.
      • Use the Column right button icon to shift selected column to the right.
      • Width plus [+] or minus [-] icons widens or narrows the selected column.
    • Select the Exit button to close and save changes. It also returns you to the Spreadsheet Journal Import control page.

Note: The configuration dialog box prevents you from deleting or modifying certain system required fields.

Using the column options
  1. Next select the Field Format tab. use the Field Format dialog box to control the content and format of the columns on your spreadsheet such as:
    • Field Name: You must use a valid database field name from the Journal tables. If you misspell a field name or enter an invalid field name, you will not receive an error message until you attempt to import the journal sheet.
    • Label: Enter the column label for the spreadsheet journal workbook. Optional to locally update.
    • Format: Specify the cell format.
    • Apply: You must select to save this format for the journal sheet.
  2. Select the Home icon to return to the Spreadsheet Journal Import control page.
Field format options
Journal Header & Line Functionality

If you are brand new to Journal Importing, select the Edit Sheet button. The Edit Journal Sheet window displays with an example to help you. Select the EXAMPLE-TEST sheet and then select the OK button to open the Journal Entry Sheet for editing.

Edit Sheet provides an example

The Journal Entry Sheet displays. Let's next review the Journal Entry Sheet, Header, and Lines Functionality.

Journal Entry Sheet example

Journal Entry Sheet & Journal Header functions:

  1. The Import Journals or table with arrow icon enables one to upload journals using the 'Import Journals Now' dialog box.
  2. Journal Home or house icon returns you to the Spreadsheet Journal Import control page.
  3. The plus [+] icon adds a new Journal Header. The New Journal Header dialog box displays. A sheet can contain more than one Journal. Each Header is associated with it's corresponding Lines by the Sys ID. Verify the header information is correct or as needed update the following fields:
    • Update the Journal ID, if not using 'NEXT'.
    • Entered the desired Journal Date.
    • Enter a Description for the journal.
    • Select the Reversal option.
    • Select OK to save changes and close the New Journal Header dialog box.
New Journal Header dialog box
  1. The delete [X] icon deletes a Journal Header.
  2. The pencil icon edits a Journal Header.
  3. The two papers icon copies a Journal Header.
  4. The arrow icon selects a Journal Header.
  5. The checks and lines icon allows one to change the import status.
Journal Header icon descriptions

Journal Lines functions:

  1. The plus [+] icon adds a Journal Line.
  2. The delete [X] icon deletes a Journal Line.
  3. The arrow icon selects a Journal Line.
  4. The paper with a plus [+] icon inserts multiple lines. A dialog box opens for one to enter From/To Line and select the Insert button to add multiple lines.
  5. The paper with an [X] icon deletes multiple lines. A dialog box opens for one to enter From/To Line and select the Delete button to delete blocks of existing lines.
  6. The check icon verifies the Sys ID.
Select the New sheet icon for new sheets

To create a new sheet, select the New Sheet button. The New Journal Sheet window displays. Per your local business practice decisions, enter a new journal sheet name and select OK to open the Journal Entry Sheet for editing.

Create a new sheet by selecting New Sheet

Once your setup and defaults are saved, continue to the following QRGs:

 

Video Tutorial

The video below demonstrates the process actions described in steps listed above.  There is no audio included with this video.  Select the play button to start the video.

Video Tutorial via Panopto

View the link to Customizing Journal Import Template. This link will open in a new tab/window.

0 Comments

Add your comment

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.