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 three files PeopleSoft software provides for the spreadsheet journal import user interface. You must copy all three files to the same folder on your workstation.

JRNL1.XLS	Journal workbook that you work on to create and import journals. You can rename this file, if you wish.
JRNLMCRO.XLA	Visual Basic code library and dialog control.
GLLOG.XLT	Message log template.

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

You work on the JRNL1.XLS workbook (or a copy of it) to create and import journals. You must set up your Microsoft Excel to accept macros, by selecting 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 on the process scheduler server.

See Importing Journal Entries.

Spreadsheet Journal Import Overview

The Spreadsheet Journal workbook lets you enter journals offline using Microsoft Excel and then import the journals into your PeopleSoft database. It supports regular journals and standard budget journals. However, it does not support commitment control budget journals.

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 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 into journal sheets, and import them into your PeopleSoft database using various pages, dialog boxes and buttons. There is a built-in utility you use to configure 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 into the spreadsheet, there is no validation of the values 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 ID cannot contain an apostrophe and open item key values cannot contain spaces.

Online Versus Batch Mode Import

You can import data into your PeopleSoft database using Spreadsheet Journal Import in either online mode or batch mode.

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 run the Batch Import Process (GL_EXCL_JRNL) to import one or more journal files into your PeopleSoft database.

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

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

Page Name	Object Name	Navigation	Usage
Spreadsheet Journal Import - Control Page	JRNL1.XLS	Click the JRNL1.XLS file delivered with your PeopleSoft products to open the Spreadsheet Journal - Control Page.	Set the defaults and set up your spreadsheets for importing into General Ledger.
Define Options and Defaults	JRNL1.XLS	Click the Setup button on the Spreadsheet Journal Import - Control page.	Set up journal header defaults and options for message logging, document sequencing and general options, and specify online import controls for the workbook.
Chartfield Configuration	JRNL1.XLS	Click the Configure button.	Accesses a Chartfield Configuration secondary page for a journal sheet where you can configure the columns and field formats for different ChartFields.
Notes	JRNL1.XLS	Click the Notes button on the Control page.	Access a Notes sheet in the workbook to use for instructions, calculations, notes, and so on. In our example, the sheet is blank except for a heading and the control button that returns you to the control page.
Journal Sheet	JRNL1.XLS	Click the New or Edit button on the Control Page to create or edit a journal sheet.	Create and edit journal data.
Spreadsheet Journal Batch Import Request	GL_EXCL_JRNL	General Ledger, Journals, Import Journals, Spreadsheet Journals	Run the batch import of journal sheet files that you created from the Spreadsheet interface into your PeopleSoft database.

Using the Spreadsheet Journal Import Control Page

Access the Control page - Spreadsheet Journal Import by opening JRNL1.XLS. If prompted, choose to Enable Macros.

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.

Setup	Click to set up the button to access 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 ChartfField configuration dialog from here.
Notes	Click to access a scratchpad in the workbook. Use this for instructions, calculations, notes, and so on. In our example, the sheet is blank except for a heading and the control button that returns you to the control page.

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.

New	Click to insert a new journal sheet. A workbook can contain as many journal sheets as needed, and each journal sheet can contain as many journals as desired.
Edit	Click to edit one journal sheet in the workbook.
Delete	Click to delete one or more journal sheets in the workbook.
Copy	Click to copy one journal sheet to a new journal sheet saved under a new name.

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.

Import Now	Click to initiate online import of one or more journal sheets. The system only imports journals that are marked as import.
Write File	Click to save selected journal sheets to a file. After saving one or more files, you must run the batch import process (GL_EXCL_JRNL) to complete the file import process.

Note. You can use Shift and Control 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 defaults for Business Unit, Date, Ledger Group, Source and the following fields as necessary.

User ID

Enter the 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, and the Ledger Group of the journal is defined as KLS, then the Journal Edit process overrides your choice and generates the corresponding lines anyway for all ledgers in the group.

Define Options and Defaults: Message Options

Define the message log options based on the following information:

Message Options

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.

Display Messages Online

Select to show import error messages online. Otherwise, errors go only to the message log JRNLLOG.XLS.

Define Options and Defaults: Document Sequencing

Define document sequencing options based on the following information:

Enable Document Sequencing	Select to enable document sequencing for any business units that use it. You can track journals by document sequence number. Enabling this option allows you to specify Document Sequencing fields on the journal header.
Default Document Type	When document sequencing is enabled, specify a default document type—such as domestic customer invoices, customer credit memos, or customer debit memos—to indicate the business purpose of your transaction. You can enter a document type for each journal header at a later time.

Define Options and Defaults: Online Import Control

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

Address

Enter the URL of the PeopleSoft XMLlink web service. To use a secure connection, contact your Information Technology department to set up a secure socket layer (URL starts with https://).

User ID(identification)

Enter the database PeopleSoft logon User ID.

After Successful Import

Change Import Status to Do Not Import: The system changes import status of journals that are imported successfully. This prevents re-import 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 re-import it later.

You can override journal import status at the journal sheet level anytime.

Note. To verify that your URL address is correct, copy the address to a browser and press enter. You should be able to see a list of PeopleSoft xmllink services.

Chartfield Configuration in Spreadsheet Journal Import

Click the Configurate button on the Define Options and Defaults page to access the Chartfield Configuration dialog, 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

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

Select the buttons on the Chartfield Configuration dialog based on the following information:

	Moves the highlight one column to the left.
	Moves the highlight one column to the right.
	Shifts the highlighted column to the left.
	Shifts the highlighted column to the right.
	Inserts a new column to the left of the highlighted column.
	Click this button to increase the width of the highlighted column.
	Click this button to reduce the width of the highlighted column.

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

Chartfield Configuration - Field Format

Use the Chartfield Configuration - Field Format dialog 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.

Creating a New Journal Worksheet

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

Unit, Journal ID, Journal Date, Ledger Group, Source, and User ID

Enter the GL business unit and these other required fields . Fields that your organization chose not to implement are unavailable. For example, if you chose 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.

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 Auto Generate 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.

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:

	Creates a new journal header with its own default values.
	Selects the journal header on whose lines you want to work.
	Edits the journal header fields.
	Copies a journal.
	Deletes a journal.
	Changes import status of a journal.

Spreadsheet Journal Import - Lines Buttons

The buttons in the Lines section of the spreadsheet are:

(in the Lines section)	Adds a journal line in the current selected journal header.
(in the Line section)	Deletes a journal line. Position your cursor on the line and click this button.
	Copies a block of multiple lines.
	Deletes a block of multiple lines.
	Check the amount fields to verify that you have entered the number with the number of decimal points that you have setup. The default number of decimal points is 2. Click the button to check the number of decimal points before you import the journal.

Adding Journal Lines

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

Populate the journal line data using the TAB key or arrow keys to advance from one cell to another.
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.
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 by clicking Import Now on the Spreadsheet Journal Import control page.

Select the sheets you want to import into 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.

Click this button to display the Import Journals Now page.

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.

Select some or all journal sheets that you want to save and click OK. This stores the Journal Sheet data in the file you specify in File Name. 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.

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.

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.

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

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.