Integrating with PeopleSoft General Ledger

This section provides overviews of using data from PeopleSoft General Ledger, reporting itemization and notes data from the planning model, and exporting general ledger budget data from the planning model and discusses how to:

Export budget data from the planning model.
Move budget data back to PeopleSoft General Ledger.
Import data into PeopleSoft General Ledger.

Page Used to Integrate with PeopleSoft General Ledger

Page Name	Definition Name	Navigation	Usage
Export to General Ledger	BP_EXPORT	select Planning and Budgeting, then select Data Integration, then select General Ledger Integration, then select Export to General Ledger	Export budget ledger data from a planning model to the Planning and Budgeting ledger or the general ledger staging table in the PeopleSoft EPM database.

Understanding Using Data from PeopleSoft General Ledger

In Planning and Budgeting, you can use data from the following ledgers in PeopleSoft General Ledger to seed a budget or to use as a reference during the planning and budgeting process:

General ledger - Actuals (PS_LEDGER)
Standard budget ledger (PS_LEDGER_BUDG)
Project budget ledger (PS_LEDGER_PROJ)
Control budget ledger (PS_LEDGER_KK)

Before importing data from General Ledger using the delivered ETL tool, identify the ledger data that you want to import.

After the data is imported into the PeopleSoft EPM Warehouses, it is the input source during the planning model stage process.

Data staging recognizes these sources identified within the planning model by the Planning and Budgeting scenario group, which contains a collection of scenarios that are available for use. These scenarios are associated with the line item activity type, and they are also used on the Data Source page in the model to define a source/seed and comparison for the line-item activity scenario in the planning model.

After you complete a budget, you can export the budget data from Planning and Budgeting to the EPM database or directly into a staging table to move it into the Financial Management database using ETL.

Note: If you are using PeopleSoft Commitment Control data, Planning and Budgeting does not validate a child budget to verify that it does not exceed the parent budget. To validate this data and identify errors, use the journal posting process in the Financial Management database.

Understanding Reporting Itemization and Notes Data from the Planning Model

When end users work with line item activities, they can enter public or private notes that apply to line item details, or they can use the itemization method (ITM) to provide underlying details to a line item amount. This information is not exported back to PeopleSoft Financial Management. The notes and itemization data are stored in the EPM database, which is accessible through custom reporting. These line item notes and itemization data are stored in PS_BP_LI_NOTES and PS_BP_LI_ITEMIZE, respectively.

Note: The notes and itemization data are not used as an input source for staging line item activities.

Understanding Exporting General Ledger Budget Data from the Planning Model

After you complete the budget, you can export the data from the planning model into a stage table for transfer to PeopleSoft Financial Management or to the source budget ledger table in the PeopleSoft EPM database. To export the financial line-item budget data from the planning model, run the Export to General Ledger process (BP_EXP).

Export to General Ledger Budgeting Data Process (BP_EXP)

The Export to General Ledger process (BP_EXP) is a PeopleSoft Application Engine process that extracts line item data from the planning model into PeopleSoft EPM database tables, such as the staging table PS_BP_LEDGER_BDEXP or the budget F00 ledger used by Planning and Budgeting. Based on the request parameters that you define, the system exports planning model data that is located in the master version of the line-item activity scenario at the time of the export. For data being exported back to the general ledger source system, only activities that have the Export to GL option selected on the Activity page are exported to the staging table (PS_BP_LEDGER_BDEXP), and then to the general ledger.

If you export to PeopleSoft Planning and Budgeting, these are the results in the PeopleSoft EPM database:

The system extracts planning model data from the master version and places it in the corresponding budget F00 ledger used by PeopleSoft Planning and Budgeting.
Depending on the budgeting type defined by the scenario group that is associated with the planning model, PeopleSoft Planning and Budgeting updates the relevant budget ledger F00 table located in the PeopleSoft EPM database—PS_BP_LED_BUDG_F00 (standard budget ledger), PS_BP_LED_KK_F00 (control budget ledger), or PS_BP_LED_PROJ_F00 (project budget ledger).
For data that is associated with a balance sheet planning activity, when you export a multiyear budget back to the F00 ledger (and optionally to PeopleSoft General Ledger), the system generates the missing intermediate starting balance (the first chronological period). For example, when exporting a proposed 2008–2009 budget, the system generates the missing starting balance for 2009.
A history table, PS_BP_EXPORT_HIST, tracks each individual activity scenario that is exported to the Planning and Budgeting database.

This allows for incremental loading of activity data by scenario.

If you export to PeopleSoft General Ledger, the scenario in the planning model data is extracted from the master version and placed into the PS_BP_LEDGER_BDEXP staging table in the EPM database in preparation for transfer to the PeopleSoft Financial Management database.

Note: The export process does not move data directly to the PeopleSoft Financial Management database. After data is placed in the staging table, you need to use the ETL tool to transfer the data to the financial database.

During the export process, the system uses the following logic:

The system rounds amount values to the nearest decimal level based on the currency code defined in the Currency Code table.

The rounding equates the total line amount to the sum of the line period detail amounts. Any decimal-rounding difference found is moved to the last budget period line.
If you select the Flip Sign setting defined for account types on the Account Type Options page in Planning and Budgeting, the system reverses the sign associated with data for the account type so that it is consistent with the ledger.
For any activity in the scenario marked as Export to GL, the BP_EXPORT_TO_GL field will contain a Y (yes) so that the ETL map to export the data back to the general ledger source system moves only the data rows to be included during export to general ledger.

Note: Running a process to export notes and itemizations created using PeopleSoft Planning and Budgeting is unnecessary because the system automatically stores the data in the PeopleSoft EPM Warehouses.

Export to General Ledger Page

Use the Export to General Ledger page (BP_EXPORT) to export budget ledger data from a planning model to the Planning and Budgeting ledger or the general ledger staging table in the PeopleSoft EPM database.

Navigation

select Planning and Budgeting, then select Data Integration, then select General Ledger Integration, then select Export to General Ledger

Image: Export to General Ledger page

This example illustrates the fields and controls on the Export to General Ledger page. You can find definitions for the fields and controls later on this page.

PeopleSoft Budgeting

Select to export data to the Planning and Budgeting ledger table in the EPM database.

If you select this check box, the Activity field is enabled, and the data can be exported by activity to the EPM Warehouse budget ledger. In this case, the Override GL Scenario field is enabled—that is, you can store the data in the EPM Warehouse budget ledger under a new scenario ID.

PeopleSoft General Ledger

Select to export data to the staging table in the EPM database in preparation for transference to the financial database.

If you select this check box, the Activity field is hidden and PeopleSoft Budgeting is selected automatically and made unavailable for entry. Then the entire scenario (only for those activities that have the Export to GL option selected on the Activity page) is exported to the General Ledger and EPM Warehouse budget ledger. In this case, the Override GL Scenario field is disabled—that is, you cannot choose to store the data in the EPM Warehouse budget ledger under a new GL scenario ID. The Override GL Scenario is never allowed for the activity scenarios that are exported to PeopleSoft General Ledger.

Business Unit, Planning Model ID, Scenario, and Activity

Enter values to define the model, scenario, and line item activity that you want to export.

Activity ID is allowed only when exporting to the EPM database when selecting the PeopleSoft Budgeting option.

Export Line Item Zero Amounts

Select to export line item rows that contain zero amount values.

Clear this check box to drop any rows containing a zero amount during the export process to the ledger or staging table.

Override GL Scenario

Select to export plan or budget data to the destination budget ledger table (*_F00 tables) using the scenario that you define on this page. Overriding the scenario lets you export planning and budgeting data to a different scenario (such as for reporting purposes).

Clear this check box to export data using the GL scenario that was originally associated with the proposed plan or budget, as part of the planning and budgeting scenario definition.

This check box appears only if you select PeopleSoft Budgeting as the export destination while the PeopleSoft General Ledger option is disabled.

Moving Budget Data Back to PeopleSoft General Ledger

Use the ETL tool to move the budget data back to the General Ledger. The ETL job moves data from the PS_BP_LEDGER_BDEXP staging table in EPM Warehouse to the table with the same name in General Ledger. A second ETL job is also required to move planning model information to the PS_BP_LEDG_DTL_EXP table in General Ledger. This table does not exist in the EPM Warehouse, but a view (BP_LEDG_DTL_VW) in EPM is leveraged by the ETL job to provide the criteria required in the source general ledger system to process the budget ledger data.

The criteria for running ETL back to General Ledger is destructive and based on the business unit and Planning and Budgeting scenario.

Note: The Ledger Name field (BP_LEDGER_NAME) of the staging table is equivalent to the Planning and Budgeting scenario field (BP_SCENARIO).

Importing Data into PeopleSoft General Ledger

If you exported data from the planning model into the staging table in the EPM database and ran the ETL job to transfer data to the PeopleSoft Financial Management database, you will now import this data back into the appropriate budget ledger table in PeopleSoft General Ledger by running the Import Budgeting Data (BPLEDGUPDATE) application engine process in the financial management database.

Note: The Planning and Budgeting scenario from the EPM database is the counterpart for ledger name on the run control in the PeopleSoft Financial Management database.

To run the Import Budgeting Data process in the Financial Management database, select the name of the ledger (Planning and Budgeting scenario) that you want to update.

To replace all ledger data associated with the selected business unit and ledger with the data in PS_BP_LEDGER_BDEXP, select the Replace All Ledger Data check box. If this check box is clear, the system updates only the budget ledger table rows that have been updated since the last import process.

Process Results

The Import Budgeting Data (BPLEDGUPDATE) application engine process moves a copy of the financial budget data from the ledger staging table (PS_BP_LEDGER_BDEXP) to the appropriate budget ledger (PS_LEDGER_BUDG, PS_LEDGER_BUDG_KK, or PS_LEDGER_PROJ) in the Financial Management database based on the budgeting type.

The criteria stored in the PS_BP_LEDG_DTL_EXP table is necessary to process the Import Budgeting Data application engine.

Note: For control budget types, the import process inserts data into the PS_LEDGER_BUDG_KK table. Then you must run the General Ledger Allocation process to create, edit, and post journal entries to PS_LEDGER_KK.

See PeopleSoft Commitment Control Document