Loading Ledger Data into the Operational Warehouse Enriched (OWE) Tables

There are many methods for loading subsidiary ledger data into the OWE tables.

This section provides an overview of loading ledger data into the OWE tables and discusses how to:

Load data using ETL jobs.
Import source files.
Manually edit and enter ledger data.
Use the Excel-to-CI spreadsheet upload utility.

After the source subsidiary ledger data has been loaded into the OWE tables, the data can be used for ledger preparation processing, which applies mapping rules to convert each set of ledger data to the consolidation ledger structure.

Pages Used to Enter Spreadsheet and Manual Ledger Data

Page Name	Definition Name	Navigation	Usage
Define Source File	GC_SRCFILE_DFN	select Global Consolidations, then select Define Consolidations, then select Data Acquisition Parameters, then select Define Source File	Define the source file type, file layout, and target table that the source file import component uses for loading source ledger data from a spreadsheet.
Source Data Load	GC_RUN_FILE_IMP	select Global Consolidations, then select Prepare Data for Consolidation, then select Acquire Data, then select Source Data Load	The run control page for the source file import application engine. You specify the source file definition and attach the file to import.
Define Ledger View-View Definition	GC_LEDVIEW_DFN	select Global Consolidations, then select Define Consolidations, then select Data Acquisition Parameters, then select Define Ledger View, then select View Definition	Define ledger views by business units for the manual ledger data entry process.
Define Ledger View-Chartfield Options	GC_LEDVIEW_OPT	select Global Consolidations, then select Define Consolidations, then select Data Acquisition Parameters, then select Define Ledger View, then select Chartfield Options	This preference page enables you to customize the data entry grid on the Manual Ledger Entries page to display only those fields that you want to update, and specify default values for specific ChartFields.
Define Ledger View-Amount Options	GC_LEDVIEW_AMT	select Global Consolidations, then select Define Consolidations, then select Data Acquisition Parameters, then select Define Ledger View, then select Amount Options	Enables you to display the sum total for the posted base currency amount and posted transaction amount on the Manual Ledger Entries page.
Search for Manual Ledger Entries	GC_LEDMANL_SRCH	select Global Consolidations, then select Prepare Data for Consolidation, then select Acquire Data, then select Manual Ledger Data Entry, then select Search for Manual Ledger Entries	Enables you to filter the ledger entries by Ledger Business Unit, Ledger ID, Fiscal Year, and Accounting Period. The Ledger ID determines what ledger scenario combination should be used for the ledger entries. You can specify a ledger view definition to use with the ledger entries. If you do not specify a ledger view, the system displays all of the ChartFields, amount fields, and currency on the Manual Ledger Entries grid based on the manual entries (GCLEDMANL) Ledger Template settings. Without a defined ledger view, there are no default values for the ChartFields, and no summed monetary amounts.
Manual Ledger Entries	GC_LEDMANL_ENTRY	select Global Consolidations, then select Prepare Data for Consolidation, then select Acquire Data, then select Manual Ledger Data Entry, then select Manual Ledger Entries	Use to enter ledger data manually or to update ledger data that has been either entered manually or uploaded from a spreadsheet.

Loading Data Using Extract, Transform and Load (ETL) Jobs

Use ETL jobs to move the data from the OWS layer to the PeopleSoft EPM OWE tables. When the OWS tables are moved into the OWE, separate ledger tables that contain unique keys and share the same structure can be brought into one physical table. To accomplish this, you need to create multiple ETL jobs that contain each OWS source table and the same OWE target table. This reduces the number of physical ledger tables that you need to maintain, as well as the amount of metadata that you need to define.

For example, if you have four database instances of PeopleSoft General Ledger from your subsidiaries, two of which have their own modified ChartFields, and two that are in standard PeopleSoft format, then you would need to use separate tables for those that have modified ChartFields. As long as the two standard PeopleSoft General Ledger format ledgers do not contain the same data for key fields, such as business unit, they can be combined into one physical table at this stage.

In addition, you can aggregate and enhance the data as needed. This reduces the level of detail carried into the OWE tables, improving processing time for ledger preparation and consolidations. For example, you can combine the balances of several related accounts receivable accounts into one combined account. If you don't need to carry through the level of detail in your source data, the ETL process enables you to reduce the total number of records that are brought into the OWE.

Each source ledger must include these fields:

BUSINESS_UNIT
LEDGER or SCENARIO

Note: The ledger must be included for non-EPM ledgers. For EPM ledgers, the ledger is specified in the scenario definition.
FISCAL_YEAR
ACCOUNTING_PERIOD

In addition, PeopleSoft requires that all ledgers conform to these rules for currency mapping:

For trial balance format ledgers, there should not be an equity account that stores YTD retained earnings. If there is, it should be filtered out during ledger preparation.

Instead, the system derives YTD retained earnings on the balance sheet from the total of the revenue and expense accounts.
Debits and credits should be stored as +/-, respectively.
The source ledger row that contains the posted amount for the source ledger's base currency must have the currency code set to the same value as the base currency.
Period 0 (beginning balances) must be supplied in the source ledgers for trial balance format source ledgers.

When you run ledger preparation for the common consolidation business unit for accounting period 1, period 0 (beginning balances) are also prepared and loaded into the consolidation ledger. The currency conversion and balancing of period 1 is done separately from period 0. For other accounting periods, only that specific accounting period is processed.

Note: If you are using Global Consolidations close processing to close or roll forward source balances, do one of the following: Do not send period zero (beginning balances) to the OWE, or, send period zero balances to the OWE, then define a view or constraint to exclude source period zero from ledger preparation processing.

Importing Source Files

Ledger data that is stored in spreadsheets can be imported into the OWE for consolidation processing. PeopleTools supports reading and writing to files that have a format based on a File Layout definition that you create with PeopleSoft Application Designer. To facilitate this approach, you need to convert spreadsheets into a CSV (comma separated variable) file format. An application engine program loads the CSV spreadsheets based on the associated File Layout definition.

To facilitate the file upload process, a system administrator needs to set up the URL for the file server. The URL for the Global Consolidations source file upload is located in the GC_FTP_URL. Use the PeopleTools URL Maintenance page to modify the delivered URL address according to your file server setup.

Here are the steps for importing a source ledger data file.

Use, modify, or create file layout definitions.

Global Consolidations delivers file layout definitions for you to use. Use PeopleSoft Application Designer to view the existing definitions. This is a one time step.
Define the source file and layout to use.

The Define Source File page enables you to specify a file layout to use with a source file. From this page, you can also generate a sample spreadsheet that can be used as a template. This is a one time step.
Enter your ledger data into a spreadsheet and save it using a CSV file format.
Run the Source File Import process.

The source file import process uploads the ledger data CSV file and logs any data loading errors to the engine message log.

Each source must specify a file layout definition and the target table name which in turn is tied to the record metadata to use for this source file. The target metadata includes a record that must be one of the record segments used by the file layout. The record metadata includes the specification of a temporary record that the application engine uses when loading the source file.

See Setting Up Record Metadata.

Note: The file that you select on the Source Data Load page needs to conform to the file layout (defined with PeopleSoft Application Designer) for a specified source. For example, if the files you are loading do not conform to the file layout definition, all of the business units are invalid and no source data is loaded. If necessary, use PeopleSoft Application Designer to create or modify an existing file layout definition that conforms to your source files or modify your source files to conform to the file layout definition.

You can confirm the file layout definition by opening a File Layout definition in PeopleSoft Application Designer. Use the File Layout name specified on the Define Source File page to open the file layout definition currently associated with your source file definition.

Image: GC_LEDMANL_FIL File Layout DefinitionFile Layout Definition example

Here is an example of the delivered sample file layout for the ledger upload process in PeopleSoft Application Designer:

The properties explain the file type and layout that your source files need to posses to load successfully. For example, the GC_LEDMANL_FIL file layout maps to the target GC_LEDMANL_F00 record. It uses a CSV file with a comma delimiter in an Excel format. The definition page shows you the columns and column order defined for the file layout.

Manually Editing and Entering Ledger Data

After you upload spreadsheet data using the Source File Import process, you can use the Manual Ledger Data Entry component to manually edit the source data in your ledger. Alternatively, you can enter ledger data manually with the Manual Ledger Data Entry component. You can optionally create a ledger view to specify which fields to show and the default values that display on the Manual Ledger Entries page for a business unit.

Define Source File Page

Use the Define Source File page (GC_SRCFILE_DFN) to define the source file type, file layout, and target table that the source file import component uses for loading source ledger data from a spreadsheet.

Navigation

select Global Consolidations, then select Define Consolidations, then select Data Acquisition Parameters, then select Define Source File

Image: Define Source File page

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

SourceType

Enables you to specify a file layout for use with a source. Each source has a source type. Your choices for source types include Ledger, Flow, and Dimension. The system does not validate if the source file is actually a specific source type.

File Layout

Select the associated file layout for your source file.

The delivered file layout for spreadsheet and manual entry ledger data entry is GC_LEDMANL_FIL File.

The delivered file layout for Flow Input entry is GC_FLIN_MGT_FIL.

The delivered file layout for GL Account Table or dimension entry is GL_ACCOUNT.

The spreadsheet column definition is based on the source file layout definition. You can modify or create your own file layout definition with PeopleSoft Application Designer.

First Data Line

Enter a row number to specify the first row available for loading data. The default value is 2.

Target Record

Select the target OWE table for loading your source files. This selection determines the record metadata such as the temporary record that the source file import application engine uses.

Display Sample Data

Click to launch a page displaying a sample layout for the input data. The first line of the sample report displays the column headers. A column prefixed with * designates a required field. The second line of the sample report displays the layout of the data to its maximum length. For the char type, the field displays an A to its maximum length. For numeric fields, the field displays N according to its format such as (NNN.NN). For date fields, it displays a sample date in mm/dd/yyyy format. For datetime, it displays a sample with PeopleSoft datetime

Source Data Load Page

Use the Source Data Load page (GC_RUN_FILE_IMP) to the run control page for the source file import application engine.

You specify the source file definition and attach the file to import.

Navigation

select Global Consolidations, then select Prepare Data for Consolidation, then select Acquire Data, then select Source Data Load

Image: Source Data Load page

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

File Definition

Select the source file definition that you are loading

Attach

Click to browse and select a source data file to upload.

First Data Line

This is an optional field. You can enter a number to designate the first row of data you want to load from your source data file. If you do not specify a first data line, the system will read from the first line of the spreadsheet.The default value is from the Source File Definition.

Note: If you do not want to upload an entire spreadsheet, you can optionally specify a range of data to load. If you do not specify a first data line, the system starts the import from the first line of the spreadsheet. If you do not specify a last data line, it imports data until the last line of the spreadsheet.

Last Data Line

This is an optional field. You can enter a number to designate the last row of data you want to load from your source data file. If you do not specify a last data line, it imports data until the last line of the spreadsheet.

Load Option

Select the load option you want to use.

You can specify what should happen if an entry already exists. Your choices of load options are Update or Overwrite. The behavior of the load options varies depending on the type of source file. For example, if it is a ledger source file type, the Overwrite load option replaces the data in the output with the new uploaded data for the same business unit, fiscal year, and accounting period. If it is a flow source file type, the Overwrite load option replaces the data of the output record with the new loaded data for the same business unit, scenario, fiscal year, accounting period, and ledger business unit. If it is a dimension source file type, the Overwrite load option replaces the data of the output record with the new uploaded data for the same SetID. The Update load option updates the target according to its key values. For example, it updates any existing rows and inserts a new row when the row does not exist.

On Load Error

Select either Abort run without loading data or Skip error rows and continue. The abort option aborts the run without loading any data. The skip option skips rows with error and continues loading source data into the target table.

Any data loading errors are logged to the engine message log. The system catches the following types of errors: field length error, data type error (e.g. use char in numeric field, invalid date format etc), required field missing data error, duplicate row error. For fatal database error, the system reports "fatal db error", prints error from db, and then abends.

Define Ledger View-View Definition Page

Use the Define Ledger View-View Definition page (GC_LEDVIEW_DFN) to define ledger views by business units for the manual ledger data entry process.

Navigation

select Global Consolidations, then select Define Consolidations, then select Data Acquisition Parameters, then select Define Ledger View, then select View Definition

Image: Define Ledger View - View Definition page

This example illustrates the fields and controls on the Define Ledger View - View Definition page. You can find definitions for the fields and controls later on this page.

In the View Details section, specify the Ledger Template to use.

Select the ChartField Options tab to access the Define Ledger View - Chartfield Options page.

Specify which ChartFields you want to view and their default values.

Image: Define Ledger View - Chartfield Options page

This example illustrates the fields and controls on the Define Ledger View - Chartfield Options page. You can find definitions for the fields and controls later on this page.

Click to reload the ChartFields. Enables you to repopulate the ChartFields based on the ledger template and metadata definition page.

Unless you make modifications to your manual ledger template and record metadata, there is no need to repopulate the ChartFields values.

Show All Fields

Click to select all of the Show Field check boxes for each ChartField.

This determines which ChartField values appear on the Manual Ledger Entries page.

Hide All Fields

Click to deselect all of the Show Field check boxes for each ChartField.

This determines which ChartField values appear on the Manual Ledger Entries page.

Default Value

Enter a default value for the associated ChartField.

For example, you may always make the same manual data entries for a specific currency, department, or other ChartField. Rather than entering those values each time you enter ledger data on the Manual Ledger Entries page, you can enter a default value, which then contains the default value for that ChartField.

Select the Amount Options tab to access the Define Ledger View-Amounts Options page.

Image: Define Ledger View - Amount Options page

This example illustrates the fields and controls on the Define Ledger View - Amount Options page. You can find definitions for the fields and controls later on this page.

You have the option of displaying the sum total for Posted Base Currency Amount and Posted Transaction Amount on the Manual Ledger Entries page.

Search for Manual Ledger Entries Page

Use the Search for Manual Ledger Entries page (GC_LEDMANL_SRCH) to enables you to filter the ledger entries by Ledger Business Unit, Ledger ID, Fiscal Year, and Accounting Period.

The Ledger ID determines what ledger scenario combination should be used for the ledger entries. You can specify a ledger view definition to use with the ledger entries. If you do not specify a ledger view, the system displays all of the ChartFields, amount fields, and currency on the Manual Ledger Entries grid based on the manual entries (GCLEDMANL) Ledger Template settings. Without a defined ledger view, there are no default values for the ChartFields, and no summed monetary amounts.

Navigation

select Global Consolidations, then select Prepare Data for Consolidation, then select Acquire Data, then select Manual Ledger Data Entry, then select Search for Manual Ledger Entries

Image: Search for Manual Ledger Entries page

This example illustrates the fields and controls on the Search for Manual Ledger Entries page. You can find definitions for the fields and controls later on this page.

Enter the Ledger Business Unit, Ledger ID, Fiscal Year, and Accounting Period for which you want to enter or edit data. Optionally, you can select a Ledger View Name that you previously created to filter the ChartFields and display default values for the Ledger Business Unit that you specified.

You can save a set of defined field values to a named preference, which you can use to access the Manual Ledger Entries page. Click the Save Preference button after defining the field values, and enter a Preference ID and Description. To load a previously saved preference, click the Get Preference button, and select the preference. If a preference is currently active, its name appears in the Preference field.

Click OK to access the Manual Ledger Entries page.

Image: Manual Ledger Entries page

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

On the Manual Ledger Entries page, you can edit the data entered through importing a CSV file, or enter new data for a business unit. To edit previously entered data, type over it. To add new data, insert one or more rows to enter your ledger entries. The ChartFields and any default values associated with them are set up with the Ledger View component. You can click the Calculate button to calculate the sum for posted amounts.

Using the ExcelToCI Spreadsheet Upload Facility

PeopleTools delivers an Excel-To-CI (Excel to component interface) utility that is available in the PeopleTools installation directory. The utility provides an Excel Template with Visual Basic code that enables you to input and update data in a PeopleSoft application database. The utility enables you to build a template (based on a component interface definition), input the data, and then upload the data to a PeopleSoft application database.

To provide an additional solution for ledger data spreadsheet uploads, Global Consolidations has included the (GC_LEDMANL_F00) component interface.

Note: It is a good practice to make a copy and rename the delivered ExceltoCI.xls file from your PeopleTools Excel installation directory into a separate directory so that any changes you make to this file do not get overwritten by a PeopleTools upgrade.

Here is an example of where you can find the ExceltoCI.xls file on a client loaded with the PeopleTools application.

Image: ExcelToCI directory example

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

See the product documentation for PeopleTools: PeopleSoft Component Interfaces

Access My Oracle Support to review the "Global Consolidations Implementation Guide red paper."