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.
Use the Spreadsheet Journal workbook 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. However, 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 Journals Request Page. If you do not run the Journal Edit process during the Import process, 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:
When importing less than or equal to 200 journal lines, processing is done on the application server.
When importing between 200 to 2500 journal lines, processing is done via the Process Scheduler.
Online import shows all import logs, and the log file is created through batch upload. Use the process monitor to view the progress.
When importing more than 2500 journal lines, processing is done via the Process Scheduler.
However, the user must enter the process instance number. Use the process monitor to view the progress and import logs.
The system configurations for journal import should be in accordance with the expected concurrent users using the import utility.
Below are the recommended system configurations that should be followed for concurrent batch processing:
Max API Aware - Enter the maximum number of API-aware tasks that can run concurrently on the server. Set this value to a positive number, in accordance to the system capacity and environment configuration.
Max Concurrent - Enter the maximum number of processes belonging to the corresponding process category that can run concurrently on the server. This value should be a positive number, set as per your hardware capacity, environment configuration, and the expected concurrent batch processes.
Application Engine - Priority for this process has to be changed based on the value provided for the Max Concurrent field, and the expected concurrent run for this utility. Recommended priority is Medium or High.
GL_JRNL_UPLD process definition - For this process definition you have to provide the Max Concurrent value, to limit concurrent run of the program.
Process Scheduler’s Application Engine Server (PSAESRV) count has to be incremented in the Process Scheduler configuration file, based on the expected concurrent run of the process. After the increment, restart the Process Scheduler.
See the product documentation for PeopleTools: Creating Server Definitions and PeopleTools: Defining Process Definitions.
After creating your spreadsheet, you can import data into your PeopleSoft database using Spreadsheet Journal Import using online mode or batch mode.
Online Mode
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 JRNL1.xls or JRN1.xlsm, the online import method uses XML link technology and sends data over the internet, processes the import request, and then replies to the Microsoft Excel interface.
Using JRN1_WS.xlsm, the online import method uses Web services technology and sends data over the internet, processes the import request, and then replies to the Microsoft Excel interface.
This method has additional features such as the ability to edit and submit the journal during the import process.
Batch Mode
The logical unit of work for a batch load is a file:
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.
The batch import method requires that you write the data to a file, submit and upload one or more files through the browser, and then initiate the Spreadsheet Journal Batch Import process (GL_EXCL_BATC) to process the import.
The GL_EXCL_BATC process has additional features such as the ability to edit and submit the journal during the import process.
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
The JRNL1_WS.xlsm workbook is the PeopleSoft Spreadsheet Journal Import user interface. 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 the business unit to the next line that you entered on the previous spreadsheet line. In other words, if the copy down check box is selected for Unit, the system automatically copies the business unit from the previous line instead of defaulting 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 Journal ID default depends on whether you selected the Use Next Journal ID check box in the Journal Entry Options section of the User Preferences - General Ledger page.
If the Use Next Journal ID is not selected for your user ID, you can change the value by manually entering a journal ID value in the dialog box for a new spreadsheet header.
If the Use Next Journal ID is selected for your user ID, the Journal ID field is always populated with NEXT and the field is unavailable for change or to manually enter of 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 occurs during load time. The system determines the user ID according to these rules:
If a user ID field is populated on the spreadsheet journal header, the system uses that user ID when checking user preferences.
If the user ID field is not populated on the spreadsheet journal header, the system uses the user ID of the individual loading the spreadsheet journal when checking user preferences.
You can load a journal using NEXT as the journal ID during online import and batch import.
When using 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.
When using 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 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 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. 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 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 receive 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.
These special characters are supported for all journal-character fields for the online interface and batch import: !@#$%^&*()_-=+\|[]{}:;/?.><',".
During the import process, each special character is replaced with a three-digit encoded character, which can potentially increase field length by three times. For example, importing a business unit value of US&IN is converted to US%26IN during the encoding process, and the field length is increased from five to seven. The encoded value is decoded before insertion into target tables. Due to the encoding process, the spreadsheet import file layout field length should be increased.
After a ChartField is configured, you should replace the length of the corresponding field in the GL_EXCL_BATC file layouts to three times the size of the character’s original length. For example, if the length of ChartField1 is 12, then the ChartField1 length in the file layout should be 36 (12 x 3).
Note: Files generated from JRNL1.xls or JRN1.xlsm, and JRN1_WS.xlsm must only be used with the corresponding process: GL_EXCL_BATC.
It is important to understand that the edit process does not validate or support:
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.
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.