Loading Ledger Data and Establishing Required Record Objects

This chapter provides an overview of the Global Consolidations architecture and discusses how to:

Load ledger data into the operational warehouse staging (OWS) tables.
Load ledger data into the operational warehouse enriched (OWE) tables.
Establish required record objects.

Note. The information in this chapter assumes that you are familiar with PeopleSoft Application Designer and PeopleSoft Enterprise Performance Management Warehouse metadata.

Understanding the PeopleSoft Enterprise Global Consolidations Architecture

This section discusses:

PeopleSoft Enterprise Performance Management Warehouse components.
Ledger record objects.
Metadata.
Ledger data loading steps.

PeopleSoft Enterprise Performance Management Warehouse Components

One of the first procedures that you need to complete when implementing Global Consolidations is loading the supporting subsidiary ledger data that is consolidated into PeopleSoft Enterprise Performance Management Warehouse. The main components of PeopleSoft Enterprise Performance Management Warehouse architecture are:

OWS.

The operational warehouse staging area mirrors the data in your transactional system; it is optimized for reporting and used for staging data.
OWE.

The operational warehouse enriched area stores the data that is used within the various PeopleSoft analytic applications, including Global Consolidations.
MDW.

The Multidimensional Warehouse tables are subject-oriented sets of denormalized tables that are used for multidimensional analysis and in certified reporting solutions to produce reports.

You use Extract, Transform, and Load (ETL) jobs to load source subsidiary ledger data into the operational warehouse (OW): first to the OWS, and then to the OWE tables. For detailed information about the PeopleSoft Enterprise Performance Management Warehouse architecture and use of these tools, please refer to the PeopleSoft Enterprise Performance Management Warehouse 8.9 PeopleBook.

You can also import subsidiary ledger data from spreadsheets to the new OWE Spreadsheet/Manual Entry Ledger table (GC_LEDMANL_F00). This table also handles manual data entry with the new Manual Ledger Data Entry component. These methods of data import load data directly from an external ledger system and directly into the OWE. The tools you can use to enter external data into the OWE without using the ETL process is as follows:

Source File Import component

A batch process to import source files

Note. Files must be CSV (comma separated values).
Manual Ledger Data Entry component.

Enables you to enter external ledger data through an online screen.
PeopleTools spreadsheet upload ExcelToCI (excel to component interface) tool

Once the source subsidiary ledger data has been loaded into the operational warehouse 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.

Ledger Record Objects

There are several ledger record objects used within the Global Consolidations architecture, which are defined in this table. You must create these ledger record objects by using PeopleSoft Application Designer before you can use them to store data:

Ledger Record	Description
Spreadsheet/Manual Entry Ledger	This record contains the subsidiary ledger data that you import or enter manually into the OWE. The (GC_LEDMANL_F00) ledger record contains all of the current source ledger definition columns that (PS_LEDGER_F00) contains with additional dimensions such as CUST_ID, CHANNEL_ID. You can define your own manual entry record definition as long as it contains the minimum set of the fields that Global Consolidations requires.
Subsidiary Source Ledger	This record contains the subsidiary ledger data that you extract and load into the OWS, then move this data into the operational warehouse enriched tables by using another set of ETL jobs. Typically there are multiple subsidiary source ledgers per consolidation ledger.
Source Staging Ledger (SLED)	This record is a copy of your subsidiary source ledger record, with several additional fields added that are required by Global Consolidations. Any online pages that navigate to the source data use this ledger record object. Typically there are multiple subsidiary source staging ledgers per consolidation ledger.
Mapped Ledger (MLED)	This record stores the ledger data after the ledger preparation process maps the subsidiary ledger data to the common consolidation structure. There is a single mapped ledger for each consolidation ledger.
Consolidation Ledger (CLED)	This record stores the ledger data at the completion of ledger preparation processing. The system uses this record as input into the various consolidation processes.

These diagrams show how each of the ledger record objects is populated:

Ledger Record Objects (1 of 2)

Ledger Record Objects (2 of 2)

Metadata

Anytime you add or revise a record for use with Global Consolidations, you must create or update its associated metadata. You create the records by using PeopleSoft Application Designer, then define the EPM metadata by using several PeopleSoft Enterprise Performance Management Warehouse pages. You use record metadata to define any new records that you create. If you make changes to a record, such as adding a field, you must change its data map to reflect the changes that you made in PeopleSoft Application Designer. You must also recompile its associated record metadata, table map, data map, filters, constraints, and data mapper rules.

Note. When you define record metadata for any records that will be used for Global Consolidations, you must set the Owner/Source field to Global Consolidation.

See Setting Up Record Metadata.

Ledger Data Loading Steps

To load your data, you:

Create the source and target ledger record object definitions by using PeopleSoft Application Designer, and define record metadata for these tables: OWS target ledger records and OWE target ledger records.

(This assumes that the OWS target ledger record is used as the OWE source ledger record. If that is not the case, then define that ledger record, as well.)
Load data into the OWS.
Move data from the OWS staging tables into the OWE.
Create or clone additional ledger record objects (SLEDs, MLEDs, and CLEDs) and other record objects needed by Global Consolidation processes, by using PeopleSoft Application Designer.

Define record metadata, tablemaps, and datamaps for all of them, and append required subrecords.

When you run the ledger preparation process, the SLED, MLED, and CLED tables are populated.

Loading Ledger Data into the OWS Tables

This section discusses how to load subsidiary ledger data into the Operational Warehouse staging (OWS) tables.

Use extract, transform, and load (ETL) processing to load source ledgers into the OWS layer of the PeopleSoft Enterprise Performance Management Warehouse. Every ledger that you plan to use in consolidation processing needs to be brought into the OWS by using ETL. You must create a record by using PeopleSoft Application Designer for each source and target ledger record that will be in the OWS and the Operational Warehouse Enriched (OWE) tables, then build record metadata, table maps, and data maps for all of them.

The ledger amounts that you load can use these formats: period activity or year-to-date (YTD) balances, depending on your consolidation ledger format.

Note. When you define each ledger, you specify by account type which amounts are YTD.

See Understanding Ledgers.

For source ledgers that are in PeopleSoft General Ledger format, you can use the ETL maps available on the PeopleSoft Customer Connection website.

For source ledgers in other formats, create your own maps or modify the existing maps. In many cases you can use the delivered target maps, modifying them to conform to your specific record structure. However, you need to establish specific source maps.

Each physical source ledger table must move into its own physical table in the OWS. You can use clones of PS_LEDGER as the OWS target tables for source ledgers that are in standard PeopleSoft format.

Loading Ledger Data into the Operational Warehouse Enriched Tables

There are many methods for loading subsidiary ledger data into the operational warehouse enriched tables.

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

Import source files.
Load spreadsheet and manual ledger data .
Use the Excel-to-CI spreadsheet upload facility.

Understanding Extract Transform and Loading (ETL) Jobs

Use ETL Jobs to move the data from the OWS layer to the PeopleSoft Enterprise Performance Management Warehouse Operational Warehouse Enriched (OWE) tables. When the OWS tables are moved into the OWE, separate ledger tables that share the same structure can be brought into one physical table—however, only if keys are unique. To accomplish this, you need to create multiple ETL maps —one for each OWS ledger table—that use the same target table. 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. 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.

Also, you can aggregate and enhance the data as needed. For example, you can combine the balances of several related accounts receivable accounts into one combined account. This reduces the level of detail carried into the OWE tables, improving processing time for ledger preparation and consolidations. 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.

Each source ledger must include these fields:

BUSINESS_UNIT
LEDGER

Note. Must be included for non-Enterprise Performance Management (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:

Specifically for trial balance format source ledgers, there must not be an equity account that stores YTD retained earnings.

Instead, the system derives YTD retained earnings on the balance sheet from the total of the revenue and expense accounts.
Statistic account ledger balances must not contain a value in the source base currency field.
Debit/credit stored as +/-.
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) is 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.

Importing Source Files

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, .csv) file format. An application engine program loads the CSV spreadsheets along with the associated File Layout definition.

To facilitate the file upload, 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.

Save the ledger spreadsheet in a CSV file format.
Use, modify or create file definitions.

Global Consolidations delivers file layout definitions for you to use. Use PeopleSoft Application Designer to use or create the file layout definitions. Use
The Source File Import process uploads the ledger data CSV file and logs any data loading errors to the engine message log.

The source file definition page enables you to specify a file layout to use with a source. Each source has a source type. Source types include Ledger, Flow and Dimension.

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 (set up in PeopleSoft Enterprise Performance Warehouse) includes the specification of a temporary record that the application engine uses when loading the source file.

See Setting Up Record Metadata.

Note. The Source Data Load files need to conform to the file layout (defined with PeopleTools 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, create or modify an existing file layout definition to conform 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 PeopleTools Application Designer. Use the File Layout name specified on the Source File Definition page to open the file layout definition currently associated with your source file definition.

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

GC_LEDMANL_FIL File Layout Definition

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 PS_GC_LEDMANL_F00 record. It takes a CSV file with comma delimiter in an Excel Format. The definition page shows you the columns and column order defined for the file layout.

Loading Spreadsheet and Manual Ledger Data

You can use the Manual Ledger Data Entry component to manually enter source data into your ledger. Alternatively, you can upload your spreadsheet data and then edit it manually with the Manual Ledger Data Entry component. A ledger view preference page allows you to create views that specify the fields to show and default values for a manual ledger input page. You create ledger views for select business units.

Pages Used to Enter Spreadsheet and Manual Ledger Data

Page Name	Object Name	Navigation	Usage
Define Source File	GC_SRCFILE_DFN	Global Consolidations, Define Consolidations, Data Acquisition Parameters, 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.
Define Ledger View-View Definition	GC_LEDVIEW_DFN	Global Consolidations, Define Consolidations, Data Acquisition Parameters, Define Ledger View, View Definition	Define ledger views by business units for the source file import process.
Define Ledger View-Chartfield Options	GC_LEDVIEW_OPT	Global Consolidations, Define Consolidations, Data Acquisition Parameters, Define Ledger View, Chartfield Options	This preference page enables you to customize your manual entries data entry grid to display only those fields you want to update, default values for ChartFields.
Define Ledger View-Amount Options	GC_LEDVIEW_AMT	Global Consolidations, Define Consolidations, Data Acquisition Parameters, Define Ledger View, 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.
Source File Import-Source Data Load	GC_RUN_FILE_IMP	Global Consolidations, Prepare Data for Consolidation, Acquire Data, Source Data Load, 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.
Search for Manual Ledger Entries	GC_LEDMANL_SRCH	Global Consolidations, Prepare Data for Consolidation, Acquire Data, Manual Ledger Data Entry, 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 to 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	Global Consolidations, Prepare Data for Consolidation, Acquire Data, Manual Ledger Data Entry, Manual Ledger Entries	Use to create or update ledger data manually.

Defining Source Files

Access the Define Source File 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 PeopleTools Application Designer.

Target Table Name

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.

First Data Line

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

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

Loading Source Data

Access the Source Data Load 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 2.

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 or Skip. 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 caught 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.

Defining Ledger Views

Access the Define Ledger View-Chartfield Options page.

After selecting a business unit that you want to assign a ledger view for the manual ledger entries page, access the Define Ledger View-Chartfield Options 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 make regular manual data entries for a specific account or department. 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 the select ChartFields.

Access the Define Ledger View-Amounts Options 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.

Creating Manual Ledger Data Entries

Access the Manual Ledger Entries page.

Insert one or more rows to enter your ledger entries. The ChartFields and any default values associated with them are set up on with the Ledger View component. You can click the Calculate Sum button to calculate the sum for posted amounts.

Prior to entering data, the Manual Ledger Entries search page allows you to filter your 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 to 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.

Using the ExcelToCI Spreadsheet Upload Facility

PeopleTools delivers an ExcelToCI (Excel to component interface) utility available in the PeopleTools installation directory. The utility includes 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.

Establishing Required Record Objects

This section discusses:

Required ledger record objects.
ChartField subrecords.
Ledger record object modifications.
Sample data records.

You use PeopleSoft Application Designer to create or modify several of the record objects that are required by PeopleSoft Global Consolidations. These objects include ledger record objects, ChartField subrecord objects, journal record objects, and other objects required for Global Consolidation functionality. In most cases you can “clone” the delivered system data record by loading it, making any required changes, then using the File, Save As menu in Application Designer to save it as a new object. You must rebuild any changed records, and define the metadata for them.

Required Ledger Record Objects

Create these ledger record objects and their associated work and temp tables:

Consolidation Ledger (CLED)	Create a consolidation ledger record for each different ledger structure that you plan to consolidate. This does not mean that you have to create a separate CLED for each consolidation model. Because the CLED record includes the scenario field as a key, multiple consolidation models with the same ledger structure can share a single CLED. You should clone the delivered CLED record.
Mapped Ledger (MLED)	Create one mapped ledger record for each consolidation ledger record (CLED). You should clone the delivered record.
Source Staging Ledgers (SLED)	Create one source staging ledger record for each source subsidiary ledger in the Data Warehouse that will be consolidated. These records should be created by cloning the associated data warehouse source subsidiary ledger, and appending several subrecords. See the table at the end of this chapter for the specific list. The SLED is used as the source for Data Mapper rules, which map ChartField values to the consolidation ledger.

ChartField Subrecords

Several of the record objects include a subrecord for ChartFields. You need to modify the ChartField subrecords only if your organization's ChartFields differ from those that PeopleSoft delivers.

The PeopleSoft Global Consolidations subrecord objects that store user-defined ChartFields are:

GC_CLED_UC1_SBR	Key ChartField subrecord.
GC_CLED_UC1NSBR	Non-key ChartField subrecord used by the TSE table for journal lines. This is a copy of the GC_CLED_UC1_SBR subrecord, but differs in that all fields have been changed to non-key fields. (The key attribute is cleared for all the fields.)
PF_SP_UC_SBR	ChartField subrecord used specifically by the Manual Journal component; it is a superset of ChartFields from all ledgers that use journal entry. You must modify this subrecord directly.
GC_MNL_UC1_KSBR	User-defined ChartField manual ledger.
GC_MNL_UC1_SBR	Non-key ChartField subrecord used by the ledger TSE table. This is a copy of the GC_MNL_UC1_KSBR subrecord, but differs in that all fields have been changed to non-key fields.

This example shows the delivered GC_CLED_UC1_SBR ChartField subrecord:

Delivered ChartField subrecord

To modify PF_SP_UC_SBR, open the record in PeopleSoft Application Designer, make your changes, and save the record directly. You must save it to the same name.

Note. You cannot build subrecords on a database so you must find all of the definition references for the subrecord and rebuild the records using your modified subrecord.

Here is an example of a definition reference search result for PF_SP_UC_SBR using Application Designer.

PF_SP_UC_SBR Definition Reference Search Result

Use one of these methods to modify the GC_CLED_UC1_SBR and GC_CLED_UC1NSBR subrecords:

If all of your consolidation ledger records have the same structure (that is, they use the same ChartFields), it is recommended that you modify the subrecord directly (do not clone it, use the original record, make your changes, and save), then rebuild each record that includes it.
If you plan to do multiple consolidations, and the associated consolidation ledgers use ChartFields that differ, then you need more than one version of the ChartField subrecord—one for each unique structure.

In that case, it is recommended that you clone the subrecord for each version that you need, make the required modifications to the ChartFields, then replace the delivered ChartField subrecord in the associated ledger record objects with your cloned version, and rebuild each record.

The record objects that use the GC_CLED_UC1_SBR ChartField subrecord are:

Change Log (GC_DIFF_MGT_TBL).
Change Log Temp (GC_DIFF_MGT_T).
Close Input Log (GC_CIN_MGT_TBL).
Close Input Log Temp (GC_CIN_MGT_T).
Close Input Log Work (GC_CIN_MG2_T).
Close Output Log (GC_COUT_MGT_TBL).
Close Output Log Temp (GC_COUT_MGT_T).
Consolidation Ledger (GC_CLED_MGT_F00).
Consolidation Ledger Temp (GC_CLED_MGT_T).
A temporary work table for GC_CLED_MGT_F00 (GC_CLDMGTMP_T).
A temporary work table for GC_CLED_MGT_F00 (GC_MTCH_MGT_T).
Consolidation Ledger temporary Work (GC_CLED_MG1_T).

A temporary work table for GC_CLED_MGT_F00 used in PF_POST engine while posting the journals into ledger. This temp record contains SEQ_NUM as additional key.
Consolidation Ledger temporary Work (GC_CLED_MG2_T).

A temporary work table for GC_CLED_MGT_F00 used in PF_POST engine while posting the journals into ledger.
Consolidation Ledger temporary Work (GC_CLED_MG3_T).

A temporary work table for GC_CLED_MGT_F00 used in PF_POST engine while posting the journals into ledger.
Consolidation Ledger temporary Work (GC_CLED_MG4_T).

A temporary work table for GC_CLED_MGT_F00 used in PF_POST engine while posting the journals into ledger.
Consolidation Ledger View for PSn/Vision (GC_CLED_MGT_VW).
Consolidation Ledger Views for reconciliation (GC_CLED_MGT_VW2).

The view of the Global Consolidations ledger for management reporting. It's chart of account structure is based on the PS General Ledger.
Consolidation Ledger Views temporary work record (GC_CLED_MGT_V).

The temporary work table for GC_CLED_MGT_VW2.
Edit and Post Variables Records for the Consolidation Ledger.
- GC_CLED_MG1_T
- GC_CLED_MG2_T
- GC_CLED_MG3_T
- GC_CLED_MG4_T
Input Log (GC_ILOG_MGT_TBL).
Input Log Temp (GC_ILOG_MGT_T).
Input Log Work (GC_ILOG_MG2_T).
Input Log Work2 (GC_ILOG_MG3_T).
Output Log (GC_OLOG_MGT_TBL).
Output Log Temp (GC_OLOG_MGT_T).
Journal (GC_JRNL_MGT_F00).
Journal Temp (GC_JRNL_MGT_T).
Journal - Errors (GC_JRNL_MGT_E00).
Translation Adjustment Log (GC_FX_MGT_TBL).
Translation Adjustment Log Temp (GC_FX_MGT_T).
User-Defined Chartfield Subrec (GC_CLED_UC1_SBR)
Transaction set edit view (TSE_GCJ1_FLD_VW).

Transaction Set Edit Work record for PF Summary process.

The record objects that use the GC_CLED_UC1NSBR ChartField subrecord are:

Mapped Ledger (GC_MLED_MGT_TBL).
Mapped Ledger Temp Record (GC_MLED_MGT_T).
Mapped Ledger Work Record (GC_MLED_WRK_T)

Tthe work table used in the currency conversion program. It is a staging table for the currency conversion process.
Mapped Ledger Work Record (GC_MLED_MG2_T).

The temporary work table for GC_MLED_MGT_TBL used for the GC_PREP_CURR engine while creating Currency Translation Adjustment.
Transaction set edit table for journal lines (TSE_GCJ1_FLD).
GC Accounting Entry (GC_ACCT_ENTRY).

GC Specific Accounting Entry table for Journal Generator is used to send accounting entries to GL or any other system from Consolidations.
Preview DataMap target table (PF_MAP_PRV_TGT).

The record objects that use the PF_SP_UC_SBR ChartField subrecord are:

Journal Line table (PF_JRNL_LN_TBL).

Used for Journal Line Entries within EPM.
Journal Line temporary table (GC_JRNL_LN_T).
Journal Line temporary table Recurring Journal (GC_JRNL_LNX_T)

Consolidations Journal Line temporary table for Recurring Journal engine results.
Journal Line temporary table for OWS (PF_JRNL_L_TAO).

The record objects that use the GC_MNL_UC1_KSBR ChartField subrecord are:

GC_LEDMANL_F00
GC_LEDMANL_T
GC_SLED_MANL
GC_SLEDMANL_T

The GC_TSE_MANL_FLD uses the GC_MNL_UC1_SBR chartfield subrecord.

Ledger Record Object Modifications

This table lists the record objects used by a single consolidation ledger that you might need to create or modify, and describes what modifications you may need to make. It also lists the naming convention recommended for these records. In your environment, XXX, XX, or X should be replaced with a naming convention that you devise to describe your specific records. For example, you could replace XXX with MGT to signify management consolidation records, and BUD to signify budgeting consolidation records.

Note. Remember, you must also define the record metadata, tablemaps, and datamaps for these records.

See Managing Your Implementation and Configurations with the Metadata Console.

The following table lists the required ledger related record objects and modifications for customizations:

Record Object	Description	Delivered Record Object	Recommended Naming Convention	Modifications Needed
Spreadsheet/Manual Entry Ledger	This record contains the subsidiary ledger data that you import or enter manually into the OWE. The spreadsheet/manual ledger record contains all of the current source ledger definition columns that (PS_LEDGER_F00) contains with additional dimensions such as CUST_ID, CHANNEL_ID. You can define your own manual entry record definition as long as it contains the minimum set of the fields that Global Consolidations requires.	GC_LEDMANL_F00	Modify existing F00 and save or rename after customizing: GC_LEDMXXX_F00.	Create a temp record and ledger template for a new ledger.
Manual Ledger Temp Record	A copy or clone of the Manual Ledger Record.	GC_LEDMANL_T	GC_LEDMXXX_T	Change all key fields to non-key fields.
Source Staging Ledger Record	A copy or clone of the Subsidiary Source Ledger record, to which additional fields are added for PeopleSoft Global Consolidations usage to enable navigating to the source. In Global Consolidations, when you use any online pages that navigate to the source, the system uses this ledger record object. You will have multiple source staging ledger records for each consolidation ledger record.	GC_SLED_PGL_TBL, which is based on PS_LEDGER_F00 If you use PF_LEDGER_F00 as a source, the delivered record GC_SLED_PF_TBL is based on its structure.	GC_SLED_XXX_TBL	Must append these subrecords to this record: GC_SLED_KEY_SBR: Must be added as the first field. GC_SLED_FLD_SBR: Must be added as the last field.
Source Staging Ledger Temp Record	Must be a clone of its associated GC_SLED_XXX_TBL (including the GC_SLED_KEY_SBR and GC_SLED_FLD_SBR subrecords). Each subsidiary source ledger record needs this corresponding temp record.	GC_SLED_PGL_T GC_SLEDMANL_T GC_SLED_PF_T	GC_SLED_XXX_T	Change all key fields to non-key fields.
ChartField Subrecord	Stores the consolidation ledger ChartField keys.	GC_CLED_UC1_SBR	GC_CLED_UCX_SBR	Modify when your ChartFields are different from those delivered.
Non-key ChartField Subrecord for the Journal Lines TSE Table	Contains the ChartFields used by the TSE journal lines table.	GC_CLED_UC1NSBR	GC_CLED_UC1XNSBR	Modify when your ChartFields are different from those delivered. To create it, clone your version of the non-key ChartField subrecord object GC_CLED_UCNXSBR.
ChartField Subrecord for manual journal entries	Contains the ChartFields used when entering manual journals.	PF_SP_UC_SBR	None, you must use the delivered object.	Modify the delivered object to include additional ChartFields if needed.
Mapped Ledger Record	Stores the ledger data after the Global Consolidation Ledger Preparation process maps the subsidiary ledger data to the common consolidation structure.	GC_MLED_MGT_TBL	GC_MLED_XXX_TBL	Must modify if you have nonstandard ChartFields: If you created a new modified non-key ChartField subrecord object, replace GC_CLED_UC1NSBR with your subrecord, and rebuild the table. If you modified the delivered system ChartField subrecord (GC_CLED_UC1NSBR), then you must rebuild the base table that uses this subrecord.
Mapped Ledger Temp Record	Required work record. Must be a clone of its associated GC_MLED_XXX_TBL.	GC_MLED_MGT_T GC_MLED_MG2_T	GC_MLED_XXX_T GC_MLED_XX2_T	Must modify if you have nonstandard ChartFields: If you created a new modified non-key ChartField subrecord object, replace GC_CLED_UC1NSBR with your subrecord, and rebuild the table. If you modified the delivered system ChartField subrecord (GC_CLED_UC1NSBR), then you must rebuild the base table that uses this subrecord.
Mapped Ledger Work Record	This is a required work record used during processing.	GC_MLED_WRK_T	GC_MLED_WXX_T	Must modify if you have nonstandard ChartFields: If you created a new modified non-key ChartField subrecord object, replace GC_CLED_UC1NSBR with your subrecord, and rebuild the table. If you modified the delivered system ChartField subrecord (GC_CLED_UC1NSBR), then you must rebuild this table.
Consolidation Ledger Record	Stores the ledger data at the completion of the Global Consolidation Ledger Preparation process.	GC_CLED_MGT_F00	GC_CLED_XXX_F00	Must modify if you have nonstandard ChartFields: If you created a new modified ChartField subrecord object, replace GC_CLED_UC1_SBR with your subrecord, and rebuild the table. If you modified the delivered system ChartField subrecord (GC_CLED_UC1_SBR), then you must rebuild the base table that uses this subrecord.
Consolidation Ledger View Record for PSn/Vision	A view of the consolidation ledger used for PSn/Vision.	GC_CLED_MGT_VW	GC_CLED_XXX_VW	Must modify if you have nonstandard ChartFields: If you created a new modified ChartField subrecord object, replace GC_CLED_UC1_SBR with your subrecord, and rebuild the table. If you modified the delivered system ChartField subrecord (GC_CLED_UC1_SBR), then you must rebuild the base table that uses this subrecord.
Consolidation Ledger View Records	Views of the consolidation ledger used for reconciliation across scenarios. Two different view records are used.	GC_CLED_MGT_V GC_CLED_MGT_VW2	GC_CLED_XXX_V GC_CLED_XXX_VW2	Must modify if you have nonstandard ChartFields: If you created a new modified ChartField subrecord object, replace GC_CLED_UC1_SBR with your subrecord, and rebuild the table. If you modified the delivered system ChartField subrecord (GC_CLED_UC1_SBR), then you must rebuild the base table that uses this subrecord.
Edit and Post Variable Records for Consolidation Ledger	Required objects for consolidation processing. Four different variable records are used.	GC_CLED_MG1_T GC_CLED_MG2_T GC_CLED_MG3_T GC_CLED_MG4_T	GC_CLED_XX1_T GC_CLED_XX2_T GC_CLED_XX3_T GC_CLED_XX4_T	Must modify if you have nonstandard ChartFields: If you created a new modified ChartField subrecord object, replace GC_CLED_UC1_SBR with your subrecord in each of these records, and rebuild each table. If you modified the delivered system ChartField subrecord (GC_CLED_UC1_SBR), then you must rebuild the base table that uses this subrecord.
Input Log (ILOG) Record	Keeps track of the lines of the ledger that are used as input for a particular consolidation run. Used in conjunction with the OLOG to navigate to source staging ledger data, to generate a consolidation audit, and for online inquiries.	GC_ILOG_MGT_TBL	GC_ILOG_XXX_TBL	Must modify if you have nonstandard ChartFields: If you created a new modified non-key ChartField subrecord object, replace GC_CLED_UC1NSBR with your subrecord, and rebuild the table. If you modified the delivered system ChartField subrecord (GC_CLED_UC1NSBR), then you must rebuild the base table. that uses this subrecord.
Input Log Temp Record	Required temp table. Must be a clone of its associated GC_ILOG_XXX_TBL.	GC_ILOG_MGT_T	GC_ILOG_XXX_T	Must modify if you have nonstandard ChartFields: Clone your GC_ILOG_XXX_TBL object to create this record object. Then within CLED_TMP_SBR only, change all key fields to non-key fields.
Input Log Work Record	Required work table. Must be a clone of its associated GC_ILOG_XXX_T.	GC_ILOG_MG2_T	GC_ILOG_XX2_T	Must modify if you have nonstandard ChartFields: Clone your GC_ILOG_XXX_T object to create this record object.
Input Log Work2 Record	Required work table. Must be a clone of its associated GC_ILOG_XXX_T.	GC_ILOG_MG3_T	GC_ILOG_XX3_T	Must modify if you have nonstandard ChartFields: Clone your GC_ILOG_XXX_T object to create this record object.
Output Log (OLOG) Record	Stores the output for a particular consolidation run. Used in conjunction with the ILOG to navigate to source staging ledger data, to generate a consolidation audit, and for online inquiries.	GC_OLOG_MGT_TBL	GC_OLOG_XXX_TBL	Must modify if you have nonstandard ChartFields: If you created a new modified non-key ChartField subrecord object, replace GC_CLED_UC1NSBR with your subrecord, and rebuild the table. If you modified the delivered system ChartField subrecord (GC_CLED_UC1NSBR), then you must rebuild the base table that uses this subrecord.
Output Log Temp Record	Required temp table. Must be a clone of its associated GC_OLOG_XXX_TBL.	GC_OLOG_MGT_T	GC_OLOG_XXX_T	Must modify if you have nonstandard ChartFields: Clone your GC_OLOG_XXX_TBL object to create this record object. Then within CLED_TMP_SBR only, change all key fields to non-key fields.
Change Log Record	Stores differences between consolidation processing runs. Used for analyzing consolidations.	GC_DIFF_MGT_TBL	GC_DIFF_XXX_TBL	Must modify if you have nonstandard ChartFields: If you created a new modified ChartField subrecord object, replace GC_CLED_UC1_SBR with your subrecord, and rebuild the table. If you modified the delivered system ChartField subrecord (GC_CLED_UC1_SBR), then you must rebuild the base table that uses this subrecord.
Change Log Temp Record	Required temp table. Used for analyzing differences between consolidation processing runs.	GC_DIFF_MGT_T	GC_DIFF_XXX_T	Must modify if you have nonstandard ChartFields: If you created a new modified ChartField subrecord object, replace GC_CLED_UC1_SBR with your subrecord, and rebuild the table. If you modified the delivered system ChartField subrecord (GC_CLED_UC1_SBR), then you must rebuild the base table that uses this subrecord.
Journal Record	Stores journal lines that result from consolidation processing.	GC_JRNL_MGT_F00	GC_JRNL_XXX_F00	Must modify if you have nonstandard ChartFields: If you created a new modified ChartField subrecord object, replace GC_CLED_UC1_SBR with your subrecord, and rebuild the table. If you modified the delivered system ChartField subrecord (GC_CLED_UC1_SBR), then you must rebuild the base table that uses this subrecord.
Journal Temp Record	Required temp table.	GC_JRNL_MGT_T	GC_JRNL_XXX_T	Clone your GC_JRNL_XXX_F00 object to create this record object. Then within CLED_TMP_SBR only, change all key fields to non-key fields.
Journal - Errors Record	Required error table. Used in the record metadata definition for the journal fact table GC_JRNL_XXX_F00.	GC_JRNL_MGT_E00	GC_JRNL_XXX_E00	Clone your GC_JRNL_XXX_F00 to create this record object.
Transaction Set Edit Table for journal lines	Required TSE table. Used in the record metadata definition for the journal fact table GC_JRNL_XXX_F00.	TSE_GCJ1_FLD	TSE_GCJX_FLD When you clone this record, it is recommended that you replace X with your own naming convention. You specify the name of this TSE table when you define the record metadata for the journal table.	Clone TSE_GCJ1_FLD to create this record object. Must modify if you have nonstandard ChartFields. This record uses the GC_CLED_UC1NSBR non-key ChartField subrecord. If you created a new modified non-key ChartField subrecord object, replace GC_CLED_UC1NSBR with your subrecord, and rebuild the table. If you modified the delivered system non-key ChartField subrecord object (GC_CLED_UC1NSBR), then you must rebuild the base table that uses this subrecord.
Transaction Set Edit View Record	Used for journal lines.	TSE_GCJ1_FLD_VW	TSE_GCJX_FLD_VW When you clone this record, it is recommended that you replace X with your own naming convention. When you define the record metadata for the GC_JRNL_XXX_F00, you specify the TSE table name.	Clone TSE_GCJ1_VW to create this record object. Must modify if you have nonstandard ChartFields. This record uses the GC_CLED_UC1_SBR ChartField subrecord. If you created a new modified ChartField subrecord object, replace GC_CLED_UC1_SBR with your subrecord, and rebuild the base table that uses this subrecord. If you modified the delivered system ChartField subrecord (GC_CLED_UC1_SBR), then you must rebuild the base table that uses this subrecord.
Transaction Set Edit Manual Ledger Field Log record	Used for tracking chartfield validation errors that occur during the ledger verification process.	GC_TSE_MANL_FLD	GC_TSE_XXXX_FLD When you clone this record, it is recommended that you replace X with your own naming convention. The manual ledger TSE record is specified on the Ledger Template.	Must modify if you have nonstandard ChartFields. This record uses the following subrecords: TSE_FLD GC_TSE_KSBR GC_MNL_CF_KSBR GC_MNL_UC1_KSBR If you created new modified ChartField subrecord objects, replace the above subrecords with your subrecords, and rebuild the base table that uses this subrecord. If you modified the delivered system ChartField subrecords above, then you must rebuild the base table that uses these subrecords.
Matching Report Temp Record	Used when creating a matching report.	GC_MTCH_MGT_T	None, you must use the delivered object.	Must modify if you have nonstandard ChartFields: If you created a new modified non-key ChartField subrecord object, replace GC_CLED_UC1NSBR with your subrecord, and rebuild the table. If you modified the delivered system ChartField subrecord (GC_CLED_UC1NSBR), then you must rebuild the base table that uses this subrecord.
Manual Journal Record	Stores manual journal lines.	PF_JRNL_LN_TBL	None, you must use the delivered object.	Must modify if you have nonstandard ChartFields. This record uses the PF_SP_UC_SBR ChartField subrecord. Update PF_SP_UC_SBR with your ChartFields, then rebuild this record.
Manual Journal - Errors Record	Required error table.	PF_JRNL_L_TAO	None, you must use the delivered object.	Must modify if you have nonstandard ChartFields. This record uses the PF_SP_UC_SBR ChartField subrecord. Update PF_SP_UC_SBR with your ChartFields, then rebuild this record. Cloned from the base table, (PF_JRNL_LN_TBL), with keys changed to non-key values.

Warning! It is vital that you rebuild any records that you modify directly or indirectly (through subrecord changes), and define or update the record metadata, tablemaps, and datamaps for these records.

Note. If you alter the ChartFields, and plan to use the delivered multidimensional warehouse tables for the PeopleSoft Enterprise Financials Warehouse that use the consolidation ledger, you need to update and recompile the associated metadata, and use the delivered ETL jobs to update the associated multidimensional warehouse tables.

Sample Data Records

The delivered system database contains both a simple and complex consolidation model. This table summarizes the records that these models use:

Record Type	Simple Consolidation Model	Complex Consolidation Model
Source ledger	PS_LEDGER_F00	PS_PF_LEDGER_F00
Source Ledger (spreadsheet or manual entry)	PS_GC_LEDMANL_F00	NA
Source Staging Ledger (SLED)	PS_GC_SLED_PGL_TBL	PS_GC_SLED_PF_TBL
Mapping Ledger (MLED)	PS_GC_MLED_MGT_TBL	PS_GC_MLED_MGT_TBL
Consolidation Ledger (CLED)	PS_GC_CLED_MGT_F00	PS_GC_CLED_MGT_F00

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 PeopleTools Application Designer.
Target Table Name	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.
First Data Line	Enter a row number to specify the first row available for loading data. The default value is 2.
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

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 2. 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 or Skip. 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 caught 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.

	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 make regular manual data entries for a specific account or department. 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 the select ChartFields.