Understanding Spreadsheet Journal Import

This table lists the files that PeopleSoft provides for the spreadsheet journal import user interface. Copy all files to the same folder on your workstation.

Microsoft Excel 2007 (and subsequent versions)

Description

JRNL1_WS.xlsm

This is the journal workbook that you use to create and import journals with Excel 2007 and versions above. You can rename this file using a meaningful name.

JRNLMCRO_WS.xlam

This is the Visual Basic code library and dialog control used with Excel 2007 and subsequent versions.

GLLOG.xlt

This is the Message log template.

Note: If using the JRNL1_WS.xlsm file (Excel 2007), you may have to enable the macros before you can begin using the file. If the macros are not yet enabled, you will see a Security Warning at the top of the page. If so, click the Options button that appears near the Security Warning. Select the Enable this Content radio button, and click OK.

No additional .dll files need to be installed on your workstation when using the Excel 2007 files.

In addition to the previously mentioned three files, Spreadsheet Journal online import mode expects the Microsoft delivered XML library file MSXML6.dll to be installed on your workstation.

To import journals in online mode from the spreadsheet, your system administrator must associate you with all a role that contains the EPGL9000 permission list.

To import journals in batch mode, your system administrator must set up the correct environmental variable (PS_FILEDIR) on the process scheduler server.

See Importing Journal Entries.

The Spreadsheet Journal workbook enables you to enter journals offline using Microsoft Excel and then import the journals into your PeopleSoft database. It supports regular journals and standard budget journals. Spreadsheet journal import also supports commitment control adjustments for each of the commitment control amount types:

  • Actuals and Recognized

  • Actuals, Recognize, and Collect

  • Collected Revenue

  • Encumbrance

  • Planned

  • Pre-encumbrance

When a journal with a commitment control ledger group and an invalid commitment control amount type is imported, the system assigns the correct commitment control amount type during import and issues a warning message.

Spreadsheet journal entry does not support commitment control budget journals.

Note: Commitment control budget journals can be imported using the flat file feature.

See Importing Budget Journals from a Flat File.

Spreadsheet Journal Import does not support user entered value-added tax (VAT) information. After you import the journals from the spreadsheet, the Journal Edit process creates VAT defaults and amounts based on the country code, if VAT is enabled.

Spreadsheet Journal Import partially supports multibook journals. You can specify a primary or secondary ledger on the multibook journal lines, but you cannot enter multiple ledger information (multibook) for the same line. When you run the Journal Edit process, it creates the additional multibook lines.

Spreadsheet Journal Import does not allow the use of control accounts. By definition, control accounts originate in the subsystems, such as accounts receivable and accounts payable. Spreadsheet Journal Import rejects control accounts during the import process.

Note: When importing a journal from a spreadsheet, you can select to run the Journal Edit process during the import process or you can run the Journal Edit batch process on the journal using the Edit Journal Request page. If you do not run the Journal Edit process during the Import and you open a journal online, you receive a warning message indicating that the journal must be edited first. In this case, it is only after the batch edit that the journals display the final entries for items such as interunit, separate debit credit reversals, and multibook lines.

Importing journal lines process varies based on the journal line numbers as follows:

  • For importing journal lines less than or equal to 200, processing is done on the application server.

  • For journal lines between 200 to 2500, processing is done via the Process Scheduler. Online import will show all import logs, and through batch upload the log file is created. Program progress is monitored via the process monitor.

  • For journal lines more than 2500, processing is done via the Process Scheduler. However, online import will have the process instance number prompted to the user. User has to login to the process monitor to see the progress, and the import logs.

The JRNL1_WS.xlsm workbook is the PeopleSoft Spreadsheet Journal Import user interface. You use it to prepare and enter journals, group and manage journals in journal sheets, and import them into your PeopleSoft database using various pages, dialog boxes, and buttons. PeopleSoft provides a utility to move ChartFields and rearrange columns in your journal workbook. The workbook contains one or more journal sheets, and each journal sheet can contain one or more journals.

When you open the file JRNL1_WS.xlsm, you begin with the Control page. The Control page contains three sets of buttons that enable you to:

  • Set workbook defaults, configure ChartFields, and rearrange columns.

  • Maintain the journal sheets in the workbook.

  • Import the journal sheets from the workbook.

You use the journal sheet page to prepare journals. There are buttons and dialog boxes for you to add, delete, and copy journals. Because you are entering data offline in the spreadsheet, there is no validation of the values that you are entering. Validation of journals takes place when you import the journal and during the Journal Edit process.

However, while there is no data validation, spreadsheet journal import provides offline validation in that journal IDs cannot contain an apostrophe and open item key values cannot contain spaces.

The business unit that you specify on the spreadsheet journal header is defaulted to those spreadsheet lines for which you have left the business Unit blank when you import the spreadsheet.

However, just as with Ledger, SpeedType, and ChartFields, if you select the check box that is located below the Unit field on the spreadsheet lines page, the system copies to the next line the business unit that you entered on the previous spreadsheet line. That is to say, if the copy down check box is selected for Unit, the system immediately copies the business unit from the previous line and does not default the business unit entered on the spreadsheet journal header to the new line that you are adding.

In either instance you can manually add and change the business unit on spreadsheet lines.

When you create a new journal spreadsheet header, the default is to the value NEXT for the Journal ID field. If your user ID preference is not set up to always use NEXT, you can change the value by manually entering a journal ID value in the dialog box for a new spreadsheet header.

If your user ID is designated on the User Preference page to always use NEXT, the Journal ID field is always populated with NEXT and the field is unavailable for change or for the entry of a manual Journal ID in the add mode.

The user ID is not a required field for the spreadsheet journal header, so the check against user preferences for the user ID is at load time when the system derives the user ID according to the following rules:

  • If a user ID is populated on the spreadsheet journal header, the system considers that user ID to check for user preferences.

  • If the user ID field is not populated on the spreadsheet journal header, the system considers the user ID of the individual loading the spreadsheet journal to check for user preferences.

You can load a journal using NEXT as the journal ID through online import and batch import:

  • Batch Import: If you choose to use NEXT as the journal ID and write the journal data to a file, the text file in XML format has NEXT as the journal ID for each journal header.

  • Online Import: If you choose to import the spreadsheet journal online using the Import Now functionality, the NEXT journal ID on the spreadsheet is updated with an actual system generated journal ID number.

The system ID is a sequential number that is unique to each journal created across all journal sheets within a spreadsheet journal workbook file. System ID starts with a value of 1001 and recycles when it reached 9999.

The purpose of system ID is to avoid confusion when using NEXT as the journal ID where there are multiple journal headers in a journal spreadsheet with each showing the value NEXT. The system generates a separate system ID for each journal header as a visual indicator that makes it possible to identify different journal headers both while they are all showing NEXT and after the various journal IDs are generated. The system ID is called a visual indicator because it is not stored in the journal tables but is available to differentiate the journals within the spreadsheet interface and when performing various spreadsheet functions, such as:

  • Copy Journal

  • Delete Journal

  • Change Import Status

  • Edit Journal Header

  • Select Journal Header

The system ID is also included in error messages in addition to reference, business unit, journal ID, and date to more easily identify problem journals.

The journal header reference ID enables you to enter reference information for each journal header on the spreadsheet. The Journal Header Reference field on the journal header is updated with the spreadsheet value at upload.

The Journal Header Reference field identifies a document, person, invoice, date, or any other piece of information that is associated with a journal entry and is helpful when you need to trace back to the source of a transaction.

Error messages that are logged contain the journal header reference because it is helpful in researching the source of a transaction.

You can specify a SpeedType for a spreadsheet journal entry line. You can also copy the SpeedType to subsequent journal lines that you insert by selecting the copy down check box below SpeedType on the spreadsheet lines interface.

A spreadsheet journal user might specify a SpeedType, to which the user does not have access. At load time, the SpeedType that is entered on the Spreadsheet Journal is validated against the valid list of SpeedTypes available for the user ID that is specified in the Spreadsheet Journal Header.

If the user ID is not specified on the spreadsheet journal header, then the SpeedType is validated against the valid list of SpeedTypes available for the user ID of the user that imports the spreadsheet journal.

An error message is logged if an invalid SpeedType is specified on the Spreadsheet Journal. The error message is logged at import time for batch and online import.

At import, the system first populates the ChartField values based on the SpeedType but then can override a generated ChartField value with a ChartField value that is specified in the spreadsheet. For example, consider the SpeedType, Peripheral, which is available in the demo data that is defined to specify account 500000, department 212000, and product Config. If you populate a spreadsheet journal line using this SpeedType, Peripheral, but enter a department 10000, at import time the system populates the ChartFields with account 500000, department 10000, product Config.

Note: If the SpeedType is created under One User ID or One Permission list, the Spreadsheet will not differentiate, unlike the online journal where the selection is limited by Type of SpeedTypes. This is due to the fact that a prompt table (which is equivalent to a dropdown box in Excel) could potentially create a performance bottleneck due to the large volume of data that would have to be retrieved and displayed in the Spreadsheet.

Spreadsheet journal validation is limited and it is not intended to be as broad as the validation provided with journal entry using the Journal Entry page.

For example, validations can be done for Project ID and other project costing ChartFields for journals entered online using the Journal Entry page. However, the spreadsheet load process is not considered a replacement for online journal entry but is supplemental and is not recommended for journals where there is a need for broad project costing ChartField validation.

The spreadsheet journal import process allows you to perform the Edit process while journals are being imported. If these journals pass the Edit process, they are imported and assigned a Valid status. The Valid journals can be posted directly to the ledger. If an error occurs during the Edit process, the journal is not imported and an error is displayed in the message log file.

If you import a journal from a spreadsheet and do not enable the edit process, you must run the Journal Edit batch process on the journal before you make corrections using the Create Journal Entries pages. Journal entries loaded to your system using spreadsheet journals import (or for that matter, flat file journal import) must be edited using batch edit only. This is important because imported journals do not have all journal lines and values populated by the various automatic features. After importing journals, if you open them online you get a warning message stating that they must be edited first. It is only after the batch edit runs that the journals display the final entries for such things as interunit, separate debit credit reversals, and multibook lines.

Spreadsheet journal entry provides consistent error messages across batch import and online import. For batch import the messages are provided in a separate log file and are not part of the message log. However the message log provides reference to the log file and incorporates the Reference ID field value in all the messages logged.

The Process Monitor informs you of error or warning messages and are written to the log file. The following are examples of error and warning messages that might be logged by the system:

  • Logs error if NEXT is not used where only NEXT should be used.

  • Logs journal header validation errors.

  • Logs journal line validation errors.

  • Logs message for skipped journal headers.

  • Logs message for skipped invalid journals.

  • Logs error message if specified SpeedType value does not exist.

  • Logs error message if a commitment control ledger group is used and the business unit is not set up for commitment control.

  • Logs error messages if the validation of the commitment control ledger against the commitment control ledger group is not valid.

  • Logs error if commitment control is not enabled for General Ledger.

Note: When enabling the Edit process during import, the journal is imported only if it has a Valid status and is free from edit errors such as journal balancing, combination rules, and so on. If an edit error exists, the journal is not loaded with the error status. When journals are imported with the edit process and have a Valid status, they can be posted directly to the ledger. See Limitations Using the Edit Process during Import below.

Field Length Validation

The field length at the journal line level, for a user entry field, is validated for most fields except amount and date type. The template has a built-in synchronization feature to synchronize the ChartField field length, which is compared to the value in the Field Length column of the Advanced Configuration Page.

The field length at the line level, including a ChartField, is validated against customizations (if they exist) in the system.

If these validations are violated, an error occurs and prevents online and offline journal import. It is recommended that you synchronize the template at the beginning of your session, when prompted. This saves time and manual effort during the import process.

It is important to understand that the edit process does not validate or support:

  • Suspense Handling

    Suspense lines are not created even if it’s enabled in Ledgers for A Unit. The journal lines should be balanced if using the edit functionality. If journal lines are not balanced, then a error is displayed. If suspense line journals need to be imported, you must disable the edit option on the import process and use the online batch edit process.

  • Commitment Control

    For journals that must be budget checked; you must run the Budget check journal process online after the journals are imported.

You can import data into your PeopleSoft database using Spreadsheet Journal Import in either online mode or batch mode. The logical unit of work for a batch load is a file and the logical unit of work for an online load is a journal sheet.

  • Using online mode, you open a journal sheet, enter data, and use the Import Journals Now button to import the data into your PeopleSoft database.

    Data is sent as XML documents over the internet and immediately imported into the PeopleSoft database.

  • Using batch mode, you can store your journal sheets by using the Write Journals to a File button, and then running the Batch Import Process (GL_EXCL_BATC) to import one or more journal files to your PeopleSoft database.

If you want to use the batch import process but at the same time want to import one journal sheet at a time, create separate files for each journal sheet. The option to write one file can be used to create a separate file for each journal sheet while writing a file. When the option to write one file for each journal sheet is selected, the system generates one file for each journal sheet and an index file.

Batch import mode has a feature to import multiple journal files at a time using an index file that points to multiple data files. For example, assume you have a text file INDEX.txt containing these four lines:

 H:\helen\JRNL1.xml
 H:\helen\JRNL2.xml
 H:\david\JRNL1.xml
 H:\Singapore\ProjectX.xml

You can attach INDEX.txt to the Spreadsheet Journal Import page and select Index file to other data files in the Number of Data Files field. The Spreadsheet Journal Import process searches for all four data files and imports them one by one. Be aware that the file paths are specified relative to the process scheduler where the import process runs. In this example, it is the H drive on the process scheduler.

If your process scheduler runs on a UNIX machine, then your index file may look like the following example. Remember, UNIX file names are case sensitive.

/tmp/usr/jrnl1.xml
/tmp/usr/jrnl2.xml
/tmp/usr/jrnl1.xml
/tmp/singapore/projectx.xml

Secondary translation lines default from primary lines for spreadsheet journal entry. This applies to translate lines where the secondary journal lines must be generated from the primary lines as in the case of an external import where only primary lines are entered.

Changes were made so that when the foreign currency of the primary line is the same as that of the base currency of the translate line, then the rate, type, and exchange rate are defaulted from the corresponding primary line when editing imported journal entries from flat file or spreadsheet journal entry. That is to say, if the transaction currency of the primary ledger and base currency of the translate ledger are the same, then the system defaults the exchange rate from the primary journal lines in spreadsheet journal entry just as the system does in online journal entry. If the foreign currency of the primary line is not the same as that of the base currency of the translate line, the system uses the exchange rate from the rate type specified on the ledger group of the Translate ledger.