Using Spreadsheet Journal Import

This chapter provides an overview of spreadsheet journal import and discusses how to set up and import spreadsheet journals.

Understanding Spreadsheet Journal Import

This section describes the prerequisites for importing spreadsheet journals into General Ledger and presents an overview of the spreadsheet journal import process.

Prerequisites

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

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

There are no additional .dll files to be installed on your workstation when using this set of files for Excel 2007.

Note. Use the JRNL1.xls or the JRNL1.xlsm file to enter your journals, depending upon which version of Excel you are using. You must set up Microsoft Excel to accept macros, by navigating to Tools, Macro, Security, and selecting Medium or Low on the Security Level tab.

To import journals in online mode from the spreadsheet, your system administrator must grant you permission to the following web libraries:

• WEBLIB_XMLLINK

• WEBLIB_GL

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.

Spreadsheet Journal Import Overview

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. After importing a journal from a spreadsheet, 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 yet have all journal lines and values populated by the various automatic features. They should not be edited online. 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 that the journals display the final entries for such things as interunit, separate debit credit reversals, and multibook lines.

PeopleSoft Journal Workbook: JRNL1.xls

The JRNL1.xls 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. There is a built-in utility that you can use 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.xls, 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.

User ID

When the User ID is blank on the spreadsheet journal header it defaults to the import process User ID. For online import the User ID defaults to the Import Now login user ID, and for batch import, it defaults to the user ID of the run control. 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.

Business Unit

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.

NEXT Journal ID

When you create a new journal spreadsheet header, the default is to the value NEXT for the Journal ID field and 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 opening 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.

System ID

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.

Journal Header Reference

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.

SpeedTypes

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 both batch or 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.

Validation

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.

In addition, after importing a journal from a spreadsheet, 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 yet have all journal lines and values populated by the various automatic features. After importing journals, if you then open them online you get a warning message stating that they must be edited first. It is only after the batch edit 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 system indicates in the process monitor if any error or warning message 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.

Online Versus Batch Mode Import

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_JRNL) 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

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.

Setting Up and Importing Spreadsheet Journals

To set up and import spreadsheet journals, use the JRNL1.xls file delivered with your PeopleSoft products.

This section discusses how to:

• Use the Spreadsheet Journal Import Control page.

• Set up workbook defaults.

• Create a new journal worksheet.

• Enter journals using journal sheets.

• Import journal sheets in online mode.

• Import journal sheets in batch mode.

Pages Used to Set Up and Import Spreadsheet Journals

Using the Spreadsheet Journal Import Control Page

Access the Spreadsheet Journal Import control page by opening JRNL1.xls. If prompted, choose to Enable Macros.

The control buttons on the Control page are grouped into three group boxes:

• General

• Journal Sheet

• Import Journals

Control Page: General

The General group box enables you to define options and defaults for this workbook and to enter any notes or calculations concerning this import.

Control Page: Journal Sheets

The Journal Sheets group box enables you to insert a new journal sheet, or edit, delete, or copy an existing journal sheet.

Control Page: Import Journals

The Import Journals group box enables you to import one or more of the journal sheets and save journal sheets to a file.

Note. You can use Shift and Ctrl keys to select multiple journal sheets.

Setting Up Workbook 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, click the Setup button to display the Define Options and Defaults dialog box.

Define Options and Defaults: Header Defaults

Enter default values for the Business Unit, Date, Ledger Group, Source fields, and the following fields as necessary.

User ID	Enter a default (operator) user ID for the journal header.
Enable Multibook	Select this field to make the Ledger field in the New Journal Header page unavailable for selection. This ledger field displays when you click the Add button in the Spreadsheet Journal Import - Header section of the worksheet. The Ledger field on the journal header is optional. You only use it when you want to enter journals for ledger groups that are not set to Keep Ledgers in Sync and for which you do want to specify the ledger name on the header. If you select this check box, the Ledger field is not enabled on the journal header.
AutoGen Lines (automatically generate lines)	This field is related to the Keep Ledgers in Sync (KLS) option for multibook ledgers on the Detail Ledger Group page. The KLS option determines if a transaction is posted to one or to all ledgers in a group. If you select this option, the system automatically generates journal lines to support transaction detail for all ledgers in a ledger group. For example, if you enter a two-line journal import for a ledger group that contains three ledgers, the journal edit process generates an additional four lines—two lines for each additional ledger. If you do not select AutoGen Lines, and the ledger group of the journal is defined as KLS, then the Journal Edit process overrides your choice and still generates the corresponding lines for all ledgers in the group.

Define Options and Defaults: Message Options

Define the message log options based on the following information:

Define Options and Defaults: Document Sequencing

Define document sequencing options based on the following information:

Define Options and Defaults: Online Import Control

Define options and behavior of online import mode based on the following information:

See Validation topic.

Chartfield Configuration in Spreadsheet Journal Import

Click the Configure button on the Define Options and Defaults page to access the Chartfield Configuration dialog box, in which you can include, exclude, or rearrange columns, as well as alter field labels and column formats for a worksheet.

You can alter the contents of your spreadsheet one column at a time. The column that you intend to edit is highlighted in blue. The Chartfield Configuration - Column page enables you to select a column and control its appearance.

Chartfield Configuration - Column Tab

Use this dialog box to change the format, ChartFields, and column layout of your spreadsheet.

Click the buttons in the Chartfield Configuration dialog box based on the following information:

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

Chartfield Configuration - Field Format Tab

Use the Chartfield Configuration - Field Format dialog box to control the content and format of the columns on your spreadsheet.

Field Name	You must use a valid database field name from PeopleSoft 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.
Format	Specify the cell format.
Apply	You must click to save this format for the journal sheet.

See Also

Setting Up Ledgers

Configuring ChartFields

Defining Document Types

Creating a New Journal Worksheet

Click the New button on the Spreadsheet Journal Import control page to access the New Journal Header page.

Unit	Enter a business unit and it is used by the system at import time to populate spread sheet journal lines when no business unit is specified for the line either through direct entry or copy down from the previous line value that you entered.
Journal ID	You can specify a journal ID or use NEXT to derive the journal ID. See Spreadsheet Journal Import Overview.
Journal Date	Enter a date or use the current date supplied by the system.
AutoGen Lines (automatically generate lines)	Select this check box if the ledger group contains multiple ledgers. If you have selected the Keep Ledgers in Sync (KLS) option for multibook ledgers on the Detail Ledger Group - Definition page, you should always select the AutoGen Lines check box so that the system automatically generates journal lines to support transaction detail for each ledger in the group. For example, if you enter a two-line journal import for a ledger group that contains three ledgers, the journal edit process generates two lines for each ledger.
Adjusting Entry	Select this option and the adjustment period fields become available.

Note. Fields associated with functionality that your organization chooses not to implement are unavailable. For example, if you choose not to implement document sequencing, those fields are unavailable. A field may also be unavailable depending on the settings on the Define Options and Defaults page.

Currency Information

Do not enter values for both the rate type and the exchange rate. If you do, you receive an error message. Enter one or the other, but not both.

Entering Journals Using Journal Sheets

Access a new journal sheet.

	Imports this journal sheet immediately using online import mode.
	Returns to the Spreadsheet Journal Import control page.

Spreadsheet Journal Import - Header Buttons

The buttons in the header section of the spreadsheet are:

Spreadsheet Journal Import - Lines Buttons

The buttons in the lines section of the spreadsheet are:

Adding Journal Lines

Click the Add button in the line section to access and add a journal line.

1. Populate the journal line data using the Tab key or arrow keys to advance from one cell to another.

2. Insert additional lines by clicking the Add sign again to insert a line and automatically reposition your cursor in the first active cell of the new line.

   Scrolling left is unnecessary.

3. Select the check box for a field where you want the value of the field on that journal line repeated in the journal line that is added next.

   Otherwise, the value for that field on the succeeding line is blank.

Note. Use as many journals in a spreadsheet as you like. Note that when you insert lines, they carry the header displayed at the top of the sheet.

Importing Journal Sheets in Online Mode

You can import journal sheets online from either the control page or the journal sheet page.

Import Journals Online from the Spreadsheet Journal Control Page

Access the Import Journals Now dialog box by clicking Import Now on the Spreadsheet Journal Import control page.

Select the sheets that you want to import to the General Ledger database. Enter your PeopleSoft database user ID and password and click OK.

Import Journals Online from a Spreadsheet Journal Worksheet

Access a Spreadsheet Journal Import journal sheet.

The URL to your PeopleSoft database appears automatically. Enter your user ID and password and click OK to import the journals.

Importing Journal Sheets in Batch Mode

Some organizations create numerous journal sheets, which they store in flat files for processing at a later time. To do this, you must write the journal sheets to the individual flat files. When you are ready, you run the GL_EXCL_JRNL batch import process.

Writing Journals to a File

After completing your journal sheet activity, navigate to the Control page and click Write File to access the Write Journals to File dialog box.

The default for batch mode is for only one file to be generated for all journal sheets. However, you can create a separate XML file for each journal sheet.

To import one journal sheet at a time, you create a separate XML files for each journal sheet by selecting the Write One File per Journal Sheet check box on the Write Journals to File page. The system generates:

• One file for each journal sheet.

• An index file.

Select some or all journal sheets that you want to save and click OK.

This stores the journal sheet data in the file that you specify in theFile Namefield.

To import the journal file into the PeopleSoft database, you must run the PeopleSoft Spreadsheet Journal batch import process (GL_EXCL_JRNL).

Running the Spreadsheet Journal Import Batch Process (GL_EXCL_JRNL)

Access the Spreadsheet Journal Import Request page in General Ledger (General Ledger, Journals, Import Journals, Spreadsheet Journals, Spreadsheet Journal Import Request).

Number of Data Files	Single data file: The file you attached to this page is the data file containing journal data. Index file to other data files: The file you attached to this page is an index file containing file path and file names to one or more data files stored elsewhere.
Add	Click to add an attachment. Browse for the file you have written from the spreadsheet journal workbook, and then click Upload.
Delete	Click to delete an attachment.
View	Click to display the contents of the attached file.
Character Set	Select the character set of the flat file being imported. For example, you can specify ISO_8859-6 for Arabic or JIS_X_0208 for Japanese Kanji.
If Journal Already Exists	Select Abort , Skip, or Update.
If Journal is Invalid	Select Abort or Skip.
Default GL Document Type	If you use document sequencing, specify a default document type to indicate the business purpose for the transaction. You can enter a document type for each journal header at a later time.