Setting Up and Importing Spreadsheet Journals

Pages Used to Set Up and Import Spreadsheet Journals

Page Name	Definition Name	Navigation	Usage
GC Journal Import Home	ExcelToCI-GC.xls	Open the ExcelToCI-GC.xls file, click the Home button if necessary.	Use the GC Spreadsheet Journal Import Home page.
Setup	ExcelToCI-GC.xls	Click the Setup button on the GC Spreadsheet Journal Import Home page.	Set up connection parameters for the PeopleSoft EPM database.
Journal Defaults	ExcelToCI-GC.xls	Click the Defaults button on the GC Spreadsheet Journal Import Home page.	Define journal defaults.
Map Excel Columns to Template Fields	ExcelToCI-GC.xls	Click the Map Fields button on the GC Spreadsheet Journal Import Home page.	Map worksheet columns.
Journal Sheet	ExcelToCI-GC.xls	Click the New or Edit button on the GC Spreadsheet Journal Import Home page to create or edit a journal sheet.	Create and import a journal worksheet.
Import Log	ExcelToCI-GC.xls	Click the Import Log button on the GC Spreadsheet Journal Import Home page.	Review messages about the journal import process.

GC Journal Import Home Page

Use the GC Journal Import Home page (ExcelToCI-GC.xls) to use the GC Spreadsheet Journal Import Home page.

Navigation

Open the ExcelToCI-GC.xls file, click the Home button if necessary.

Image: GC Spreadsheet Journal Import Home page

This example illustrates the fields and controls on the GC Spreadsheet Journal Import Home page. You can find definitions for the fields and controls later on this page.

The control buttons on the GC Spreadsheet Journal Import Home page are categorized by function into the following group boxes:

Setup
Journal Worksheet
Import Journals

Home Page: Setup

The Setup group box enables you to define options and defaults for this workbook and to establish connection parameters.

Setup

Click to set up connection parameters to communicate with the PeopleSoft EPM database. These parameters enable the worksheet to retrieve valid values for various journal fields, such as ChartField/Dimension values, and to complete the import process.

Defaults

Click to define journal defaults.

Map Fields

Click to map the GC ledger template fields to the worksheet.

Home Page: Journal Worksheet

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 in the workbook. Enter the new worksheet name in the dialog box, then click OK.

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 a journal sheet in the workbook. Enter the worksheet name in the dialog box, then click OK.

Delete

Click to delete one or more journal sheets in the workbook. Enter the worksheet name in the dialog box, then click OK.

Copy

Click to copy a journal sheet to a new journal sheet saved under a new name.

Home page: Import Journals

The Import Journals group box enables you to import one or more of the journal sheets and view messages about the import process.

Import Now

Click to import one or more journal sheets.

Import Log

Click to review messages about the journal import process.

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

Setting up Connection Parameters

Use the Setup page (ExcelToCI-GC.xls) to set up connection parameters for the PeopleSoft EPM database.

Navigation

Click the Setup button on the GC Spreadsheet Journal Import Home page.

Before you start entering journals, you must specify connection parameters to the PeopleSoft EPM database using the Setup page. These parameters enable the worksheet to interface with PeopleSoft Global Consolidations in order to determine the ledger template structure, retrieve valid values for various journal fields, and import the worksheet journal data into the appropriate records.

Image: Setup page

This example illustrates the fields and controls on the Setup page. You can find definitions for the fields and controls later on this page.

Connection Setup

Complete the fields within this section to establish communication with the PeopleSoft Enterprise Performance Management database.

WebServer Machine

The name of the PeopleSoft web server to which you are connecting.

Protocol

The protocol used to access the web server. The default is http. The preferred protocol is https.

HTTP Port

The HTTP port number that the web server uses. The default is 80.

Portal

The name of the portal you are using. EMPLOYEE is a default portal shipped with PeopleSoft.

PeopleSoft Site Name

The PeopleSoft site name that you entered when you installed the PeopleSoft Internet Architecture.

Node

The PeopleSoft default local node name. The default is PT_LOCAL.

Chunking Factor

The number of rows of data to be transmitted to the database at one time. The default is 40.

Error Threshold

The total number of errors that are permitted before submission to the database ceases. When the error threshold is exceeded, an error message appears and submission to the database stops.

Action

Select Create.

Template Information

The values in these fields are unavailable for entry, and are populated with the required values for the template. You should not modify these fields unless you need to use a different component interface.

Modify Template

Select to enable edits to the Template Information fields.

User ID

Enter the user ID to access the PeopleSoft database.

Password

Enter the password associated with the PeopleSoft user ID.

CI Name

Enter the name of the component interface (CI).

Configure

Click to update the worksheet using the new CI.

XML Link Address

Enter the location of XML link using the following format: http://<webserver>/xmllink/<site>/

For example, if the webserver is rtdc70000opsmt.us.companyname.com:80 and the site is pf910database, then you would enter the following for the XML link address:

http://rtdc70000opsmt.us.companyname.com:80/xmllink/pf910database/

Generate Log

Click to enable the worksheet to create a message log for the import process.

Journal Defaults Page

Click the Defaults button on the GC Spreadsheet Journal Import Home to access the Journal Defaults page.

Image: Journal Defaults page

This example illustrates the fields and controls on the Journal Defaults page. You can find definitions for the fields and controls later on this page.

Enter the default values to use for the journal sheets in your workbook. Business Unit and Scenario ID are required, the remaining fields are optional. When you create a new journal worksheet, the values that you provide using this page are automatically populated into their respective fields. Defining these defaults speeds up data entry. If you do not provide a value for an optional field, you can enter it manually when you create the journal.

BU Lov Load (Business Unit List of Values Load)

Click to load the list of values for the business unit and scenario ID. A confirmation message appears when the load is complete. After you click this button, you can double-click in the business unit and scenario ID cells to select them from a list.

Journal ID

Enter the journal ID.

Journal Date

Enter the journal date.

Business Unit

Enter the business unit. You can double-click this cell to select from a list of values after you click the BU Lov Load button.

Scenario ID

Enter the scenario ID. You can double-click this cell to select from a list of values after you click the BU Lov Load button.

Journal Type

Enter M for a manual journal, or R for a recurring journal template.

Journal Source Code

Enter the source.

Draft Only

Enter Y for a draft journal, otherwise enter N.

Ledger Business Unit

Enter the ledger business unit.

Home

Click to return to the GC Spreadsheet Journal Import Home page.

Map Excel Columns to Template Fields Page

Use the Map Excel Columns to Template Fields page (ExcelToCI-GC.xls) to map worksheet columns.

Navigation

Click the Map Fields button on the GC Spreadsheet Journal Import Home page.

Image: Map Excel Columns to Template Fields page

This example illustrates the fields and controls on the Map Excel Columns to Template Fields page. You can find definitions for the fields and controls later on this page.

Use this page to specify which worksheet field (for the journal header) or column (for journal lines) each of the GC CI (component interface) fields is associated with. Only mapped fields will appear on the journal worksheet. Some fields are optional.

To specify the CI Field, click a cell in the CI Field column, then click the dropdown arrow to the right of the cell and select the value from the list. Continue the process until all of the necessary CI Fields are been defined.

Home

Click to return to the GC Spreadsheet Journal Import Home page.

Update Template

Click to update the worksheet after you have finished mapping the CI fields, or if you subsequently make changes to the mapping. When the utility builds a journal worksheet, the header and journal line sections are created using the CI fields from the map definition and labels them accordingly.

Defaults

Click to access the Journal Defaults page.

Load Prompt

Select the Load Prompt check box for each CI field that you want a list of values generated for in the journal worksheet, or click All to include a list of values for all possible fields. The check box is unavailable if a list of values doesn't apply to that field. Click the Load Prompt button to load in the values. This enables you to select from a list of valid values for these fields when creating journal entries. A confirmation message appears when the load is complete.

Creating and Importing a Journal Worksheet

Click the New button on the GC Spreadsheet Journal Import Home page to create a new journal sheet and access the Journal Sheet page.

A new journal with 2 journal lines is created, using the layout you defined using the Map Excel Columns to Template Fields page. The header and line field values populate with the values that you established on the Journal Defaults page. When you move to each cell to enter a value, if a dropdown arrow appears, you can click it to select from a list of values.

You can insert additional journal lines above the 'Totals' row (which is the last row) to add more lines to the journal. The journal is balanced if the Total, which is the sum of the values in the Foreign Amount fields, is zero.

Home

Click to access the GC Spreadsheet Journal Import Home page.

Import

Click to import the journal. When the import process is complete, the Import button is disabled, and the Imported check box is selected.

To reuse a journal sheet you can enter a different Journal ID and update the other fields as needed, then deselect the Imported check box. Then click Import to import the journal.

The following example shows how the journal sheet page might appear when the journal entry is complete and has been imported.

Image: Journal Sheet page example

This example illustrates the fields and controls on the Journal Sheet page example. You can find definitions for the fields and controls later on this page.

To import several journal worksheets, access the Home page and click Import Now.

Import Log Page

Use the Import Log page (ExcelToCI-GC.xls) to review messages about the journal import process.

Navigation

Click the Import Log button on the GC Spreadsheet Journal Import Home page.

Image: Import Log page

This example illustrates the fields and controls on the Import Log page. You can find definitions for the fields and controls later on this page.

The log contains message generated by the Excel to CI utility during the import process. Each message appears on a separate line with the following information:

Workbook name
Journal worksheet name

Click the sheet name to access it.
Date time stamp
Status
Log message

Clear Log

Click to erase the message log content.