Appendix: Source, Staging, and Target Tables for Planning and Budgeting

Planning and Budgeting is delivered with the Extract, Transform, and Load (ETL) jobs and maps used to import data into the PeopleSoft EPM Warehouse. This appendix lists:

• Source, staging, and target tables for PeopleSoft Asset Management, Project Costing and General Ledger applications data.

• Source, staging, and target tables for PeopleSoft Human Resource Management applications data.

• Tables used to move data from Planning and Budgeting to source systems.

• Source, staging, and target tables for EnterpriseOne applications data.

Note. The PeopleSoft system delivers close to 6,000 jobs and maps that are used to load data into the PeopleSoft EPM Warehouse.

See Also

Understanding Planning and Budgeting Integrations

Bringing Source Data Into EPM Using Extract, Transform, and Load (ETL)

Source, Staging and Target Tables for Financial Management Data

This section lists the source PeopleSoft Financial Management Solutions (FMS) tables, and PeopleSoft EPM Warehouse Staging (OWS) and target Operational Warehouse Enriched (OWE) tables for financial management-related data. The use of target tables here refers to the records and tables that are used by the Planning and Budgeting application. In some cases, you might use online maintenance pages to access and update the data. The list is organized by:

• General ledger records.

• Dimension (ChartField) records.

• Multiple currency records.

• Asset budgeting records.

• Commitment Control records.

• Combination edit records.

General Ledger Records

Note. There are no ETL jobs or maps for the Ledger Template records (PS_PF_TMPLT_TBL, PS_BP_TMPLT_TBL, and PS_GC_TMPLT_TBL) which are delivered as system data. If your organization uses different names, you can create new ledger template definitions in the EPM database.

Note. PeopleSoft Planning and Budgeting provides portal navigation that takes you directly to the Ledger For A Unit page within PeopleSoft Financial Management Solutions (FMS) where you can view setup information. Edit access in FMS is only allowed if your security permits it, but any changes would require you to rerun the map for the PS_BU_LED_GRP_TBL record.

This page does not exist in the EPM database, but the data is used in PeopleSoft Planning and Budgeting and is brought over during the ETL process. Use the Ledger For A Unit (FMS) link to view and understand data relationships; for example, if you are using combination editing and need to understand valid combination edit groups, scroll down to the budgeting ledger for your business unit, and then click Journal Edit Options. You can then determine the Combination Edit Process Groups that will be validated for that ledger. To access the Ledger For A Unit (FMS) link, navigate from EPM Foundation, EPM Setup, Ledger Setup, Ledgers menu. You must have single sign on set up to use this link.

Dimension (ChartField) Records

Note. You must ensure that the department data you load into the OWE target table (PS_DEPARTMENT_TBL) in the Enterprise Performance Management (EPM) database is the data that you want to use for planning and budgeting purposes. The ETL map you use to populate the PS_DEPARTMENT_TBL table with PeopleSoft HRMS department data is the same one used to populate the table with PeopleSoft Financial Management department data. This is because the respective ETL jobs for source HRMS and Financial Management department data store data in the same OWS table.

When the departments are the same in your HRMS and Financial Management source, choose one source to run your ETL job.

When the departments are unique for your HRMS and Financial Management source, run the ETL jobs from each source. Since the departments are unique, you will not be required to use Dimension Mapper.

Note. Planning and Budgeting does not use the PF_SCENARIO_DFN page or ETL maps; instead it supports scenarios from the PS_BD_SCENARIO_TBL source and target table, and any scenarios defined within the EPM database on the Scenario-Budget maintenance page.

Note. The budget ledgers used by Planning and Budgeting include three additional dimensions called Dimension 1, Dimension 2, and Dimension 3. These fields in the budget ledgers have no source record, are not stored in the source budget ledgers, and don't have any maintenance page; therefore, no ETL jobs are delivered for these dimensions. These fields (DIMENSION1, DIMENSION2, and DIMENSION3) can only be found in the following budget ledger records: PS_BP_LED_BUDG_F00, PS_BP_LED_PROJ_F00, and PS_BP_LED_KK_F00.

See Configuring Dimensions for Planning and Budgeting.

See Setting Up Single Sign On.

Note. PeopleSoft Planning and Budgeting uses the PS_PRODUCT_TBL table within the application, which is referred to as the Product-General Ledger menu item for maintenance. Planning and Budgeting does not use the PRODUCT_ID field in the PS_PRODUCT_D00 table in the EPM database.

Multiple Currency Records

Asset Budgeting Records

Commitment Control Records

Combination Edit Records

Source, Staging, and Target Tables for Human Resource Data

This section lists the source PeopleSoft Human Resource Management System (HRMS) tables and Enterprise Performance Management staging (OWS) and target (OWE) tables for human resource-related data. The use of target tables here refers to the records and tables that are used by the Planning and Budgeting application. In some cases, you might use online maintenance pages to access and update the data. The list is organized by:

• Job and Compensation.

• Jobcodes and Position.

• Salary Plan.

• Other Human Resource.

Job and Compensation Records

Jobcodes and Position Records

Salary Plan Records

Other Human Resource Records

Note. You must ensure that the department data you load into the OWE target table (PS_DEPARTMENT_TBL) in the EPM database is the data that you want to use for planning and budgeting purposes. The ETL map you use to populate the PS_DEPARTMENT_TBL table with HRMS department data is the same one used to populate the table with FMS department data. This is because the respective ETL jobs for source HRMS and FMS department data store data in the same OWS staging table.

When the departments are the same in your HRMS and FMS source, choose one source to run your ETL job.

When the departments are unique for your HRMS and FMS source, run the ETL jobs from each source. Since the departments are unique, you will not be required to use Dimension Mapper.

Tables Used to Move Data From Planning and Budgeting Back to Source Systems

After you have exported the data out of the planning model, you can use the ETL tool to move the data from Planning and Budgeting back to the source systems.

See Understanding Exporting General Ledger Budget Data from the Planning Model.

See Exporting Position Data from the Planning Model.

Records Used To Send Data Back to Financial Management System

Records Used to Send Data Back to Human Resources Management System

Note. For the PS_BP_COMP_F00 source record in the OWE for Planning and Budgeting, the ETL jobs to export data to HRMS use views to extract compensation distributions by category, they include: PS_BP_SAL_DIS_VW, PS_BP_BNFT_DIS_VW, PS_BP_EARN_DIS_VW, and PS_BP_TAX_DIS_VW.

Source, Staging, and Target Tables for EnterpriseOne Applications Data

This table lists the source EnterpriseOne tables, and PeopleSoft EPM Warehouse Staging (OWS) and target Operational Warehouse Enriched (OWE) tables for EnterpriseOne-related data. The use of target tables here refers to the records and tables that are used by the Planning and Budgeting application. In some cases, you might use online maintenance pages to access and update the data.

Note. There are no ETL jobs or maps for the following target OWE tables: Ledger Template and Setup (PS_PF_TMPLT_TBL, PS_LED_CF_TBL), and Currency and Exchange Rates (PS_CURRENCY_CD_TBL, PS_RT_INDEX_TBL, PS_CURR_QUOTE_TBL, and PS_RT_RATE_DEF_TBL).. You can define these in the EPM database.

For EnterpriseOne, the following target OWE tables have delivered ETL jobs, but the information is not used by the Planning and Budgeting application: PS_REF_ACCOUNT_E1 sourced from F0901, PS_UNITS_TBL sourced from F0005, and PS_TIME_SPANS.

The following target OWE tables are the only dimension-related ones that are delivered with ETL jobs to use from EnterpriseOne: PS_GL_ACCOUNT_TBL, PS_BUS_UNIT_E1_D00, PS_SUBSIDIARY_D00, PS_SUBLDGR_TYP_D00, PS_SUBLDGR_D00, and PS_STAT_TBL. Other dimension tables in the OWE can be used with EnterpriseOne planning and budgeting, but no data is sourced from there.

Note. Dimension mapper will be required for EnterpriseOne integration during setup and the ETL processing.

We don't deliver maps to send data back to the EnterpriseOne database.