Understanding Reporting Tables
In general, you must consider two data storage areas when reporting against planning and budgeting data:
The planning model tables, also known as the activity detail tables, where activity details are stored during the budgeting process.
While the budgeting process is under way, the activity details are stored in a series of tables. These are the tables that get updated after a staging process has been performed, and when users are entering budget data during the budget process.
The source/final tables, also known as the OWE (Operational Warehouse Enriched) tables, where both the source and final (master version) data are stored.
Once the budget is finalized, the coordinator will want to run the export process for line item or position activity data, the master data is exported to the OWE tables.
Planning Model Tables
The planning model tables – known as the activity detail tables – are populated by the data staging process or by end users updating budget/plan data. These activity detail tables contain all version data, and are actively updated by users when an activity scenario is in a released state. You should evaluate the contents of each of the tables to determine what tables you need to combine in order to generate meaningful reports. For example, combining two sets of tables, you could create a query-based PS/nVision report that joins the Line Item Activity table (PS_BP_LI_TBL) with the Dimension Detail Key table (PS_BP_DIM_DTL_TBL) to produce a report for total method amounts. Of course, it would not contain the total budget amount unless you also included the Line Item Adjustments table (PS_BP_LI_ADJ_TBL).
|
Line Item Data |
Table Name |
|---|---|
|
Line Item Activity (contains total amounts for methods and allocations) |
PS_BP_LI_TBL |
|
Line Item Period Details |
PS_BP_LI_PRD_DTL |
|
Line Item Adjustments |
PS_BP_LI_ADJ_TBL |
|
Line Item Adjustment Period Details |
PS_BP_LI_ADJ_DTL |
|
Historical Scenarios |
PS_BP_LI_HISTORY |
|
Line Item Notes |
PS_BP_LI_NOTES |
|
Itemizations (ITM Method details) |
PS_BP_LI_ITEMIZE |
|
Additional Staging Data |
Table Name |
|---|---|
|
Dimension Detail Key Table |
PS_BP_DIM_DTL_TBL |
|
Dimension Members |
PS_BP_CF_MBR_TBL |
|
Dimension Exceptions |
PS_BP_CF_ERR_TBL |
|
Currency Conversion Rates |
PS_BP_ACTV_SCEN_RT |
|
Scenario Budget Periods |
PS_BP_SCEN_BUD_PER |
|
Activity Workflow Tables |
PS_BP_WRKLIST_TBL |
|
PS_BP_WRKLIST_VER |
|
|
Model Status Table |
PS_BP_MDL_STATUS |
|
Activity Scenario Status Table |
PS_BP_MDL_STAT_DTL |
The position activity data is stored in the following tables. The distribution tables listed for salary, earnings, benefits, and tax represent the annual amount of expense by effective date, whereas if you were looking for the breakdown of the annual amount by budget period, you would find that information in the period detail table referred to as the Employee/Default Expense by Period table (PS_BP_PBD_CALC_TBL).
|
Position Activity Data |
Table Name |
|---|---|
|
Job Details/Attributes |
PS_BP_JOB_TBL |
|
Position Details/Attributes |
PS_BP_POSITION_TBL |
|
Salary Distributions – Total Amounts |
PS_BP_SAL_DIS_TBL |
|
Benefit Distributions – Total Amounts |
PS_BP_BNFT_DIS_TBL |
|
Earnings Distributions – Total Amounts |
PS_BP_EARN_DIS_TBL |
|
Employer Paid Tax Distributions – Total Amounts |
PS_BP_TAX_DIS_TBL |
|
Employee/Default Expense by Period |
PS_BP_PBD_CALC_TBL |
|
Position Default Amounts |
PS_BP_PBD_CLCD_TBL |
The two main tables in which asset data is stored are the asset detail table containing information like cost, and the depreciation table containing the corresponding depreciation rows by period for the asset:
|
Asset Activity Data |
Table Name |
|---|---|
|
Asset Details and Cost |
PS_BP_ASSET |
|
Asset Depreciation |
PS_BP_ASSET_DEPR |
The asset catalog data for default details is stored in the PS_BP_ASSET_ITEMS table. Additionally, you can find the capital acquisition plan and corresponding details in the PS_CAP and PS_CAP_DET tables.
Finally, there are several views used in Planning and Budgeting to generate the analytic or analysis (ACE) reports. If you choose to write your own custom analytic reports, or modify the analysis reports, the following table provides a list of views that can be useful in extracting the information you require for reporting. A read-only lock based on your security access is performed on the data.
|
Description |
Related Activity Type |
Table Name |
|---|---|---|
|
Budgeted Line Item Data |
Line Items |
PS_BP_LI_INQ_VW |
|
Historical Line Item Data |
Line Items |
PS_BP_LIHIS_INQ_VW |
|
Relates Account to Account Type |
Line Items |
PS_BP_ACE_ACCT_VW |
|
Currency Conversion for an Account Type |
Line Items |
PS_BP_ACE_RATE_VW |
|
Position Data |
Positions |
PS_BP_PBD_ACE_VW |
|
Asset Cost |
Assets |
PS_BP_AST_ACE_VW |
|
Asset Depreciation Data |
Assets |
PS_BP_AST_ACE_VW2 |
Note: All of the views have the PROBINST field from the PS_BP_SLICE_TBL and PS_BP_SLICE_DTL records as a foreign key. For purposes of custom reporting, PROBINST is a surrogate key for the following fields (where applicable): BP_BUDGET_CENTER1 and BP_BUDGET_VERSION.
Source/Final Tables
The source/final tables used by Planning and Budgeting are the same as the OWE (Operational Warehouse Enriched) tables, and are used as both a source of data to be staged into the planning model, and a final or target table when data is exported back from the planning model.
The following table provides the table names where financial ledger data is stored. The data located in these tables (OWE) can be populated by your ETL jobs from your source general ledger system, or by exporting a planning model's line item activity scenario back to the source budget ledger (master version). When you decide to export the data back to the general ledger, a different export table is used as a staging table (PS_BP_LEDGER_BDEXP). This table is used in conjunction with an ETL job to move data back to the general ledger source system.
|
Financial Data |
Source/Final (OWE) Tables |
|---|---|
|
Actual General Ledger |
PS_LEDGER_F00 Note: Data cannot be exported from the planning model to the actual ledger. Actual ledger data cannot be anything other than a history scenario type, which does not allow update or revision. |
|
Standard Budget Ledger |
PS_BP_LED_BUDG_F00 |
|
Project Budget Ledger |
PS_BP_LED_PROJ_F00 |
|
Commitment Control Ledger |
PS_BP_LED_KK_F00 |
|
Planning and Budgeting Export/Staging Ledger |
PS_BP_LEDGER_BDEXP |
The following table provides a list of the primary table names used where position-related data is stored. The data located in these tables (OWE) can be populated by your ETL jobs from your source human resource system, or by exporting a planning model's position activity scenario back to the source position-related tables (master version). When you decide to export the data back to the human resource system, the same tables are used in conjunction with ETL jobs and views that move data back to the source system.
|
Position-Related Data |
Source/Final (OWE) Tables |
|---|---|
|
Job Data |
PS_BP_JOB_F00 |
|
Position Data |
PS_BP_POSITION_D00 |
|
Compensation Data |
PS_BP_COMP_F00 |
There is no delivered export process to move asset and asset depreciation data back to your source tables or asset management system. You may only keep your capital acquisition plan (CAP) data synchronized between PeopleSoft Asset Management and Planning and Budgeting by activating the PeopleSoft Application Messaging Enterprise Integration Point (EIP) for BUDGET_CAP_SYNC using PeopleSoft Application Designer. The CAP tables that are updated by this EIP are listed in the following table.
|
Capital Acquisition Plan Data |
Source/Final (OWE) Tables |
|---|---|
|
Capital Acquisition Plan |
PS_CAP |
|
Capital Acquisition Plan Details |
PS_CAP_DET |