Overview
The Database Table Summary below provides an excellent reference tool for learning and researching the PSPA application data model.
This summary organizes the application tables by their functional purpose and how they fit into the data flows of the pension processes.
Database Table Summary
| Pension Task | Rule Configuration | Non-Pension Inputs | Pension Data Inputs | Periodic Proc Outputs | Calculation Outputs |
|---|---|---|---|---|---|
| Plan Implementation | PA_PLAN_ADMIN PA_PLAN_ALIASES PA_PLAN_ORDER PA_PROC PA_PROC_USE PA_PROC_U_FLD_1 | BENEF_PLAN_TBL PERSON PERSONAL_DATA PERSONAL_DT_FST PERS_DATA_EFFDT NAMES PERSON_ADDRESS ORIG_HIR_PER_VW EMPLOYMENT JOB | PA_RUN_CNTL PA_RUN_CNTL_CLC PA_CALCULATION PA_CALC_GRP PA_CLC_PLN_INPT PA_CLC_ADJUST PA_CLC_PR_FLD_1 PA_CLC_PROCLIST PA_QDRODAT PA_PRIOR_PLAN | PA_PLAN_DT_TBL PA_PRDIC_PLAN PA_PRDIC_RSLT PA_PRDIC_ERR PA_EE_ERR_TBL PA_CALC_RESTART PA_AUDIT_TRACE MESSAGE_LOG MESSAGE_LOGPARM PA_ELIG_EMPRCD (Temp) | PA_CLC_SRCH_3 PA_CLC_DATES PA_CLC_EMP PA_CLC_EMP_JOB PA_CLC_DATES PA_CLC_ALS_VAL PA_SUMMARY_CALC PA_SUMMARY_PLAN PA_SUMMARY_EMP PA_CALC_RESTART PA_AUDIT_TRACE MESSAGE_LOG MESSAGE_LOGPARM PA_ELIG_EMPRCD (Temp) |
| Function Result | PA_FUNCTN_RESLT PA_FNC_RSLT_DFN PA_FNC_RSLT_NMS PA_FNC_RSLT_TBL PA_ALIAS_USE PA_FUNCTN (Static) PA_FUNCTN_FLD_1 (Static) | PA_CLC_VAL_CHAR PA_CLC_VAL_DEC PA_CLC_VAL_DT | |||
| Beneficiary | DEP_BEN DEP_BEN_NAME DEP_BEN_EFF DEP_BEN_ADDR | PA_CB_EMP_BEN | PA_CLC_PLN_BENE | ||
| Plan Eligibility | PA_CUSTOM PA_CUSTOM2 PA_CUSTOM3 (Static) | PA_PELG_HIST | PA_CLC_PEL_AY | ||
| Earnings Consolidation | PA_CONS_EARN PA_CONS_EARN_CDPA_CONS_EARN_RT PA_CONS_EARNEXC PA_CONS_EARNHIR PA_CONS_EARNST1 PA_CONS_EARNST2 PA_CONS_EARNTER | PAY_CHECK PAY_EARNINGS PAY_OTH_EARNS PAY_SPCL_EARNS EARNINGS_BAL W2_AMOUNTS YE_AMOUNTS | PA_HST_PAY_CHK PA_HST_PAY_EARN PA_HST_OTH_EARN PA_HST_SPL_EARN PA_HST_EARN_BAL PA_HST_W2_AMTS PA_HST_YE_AMTS | PA_CONS_ARRAY | PA_CLC_FAE PA_CLC_ACT_AY PA_CLC_EACCT_AY |
| Hours Consolidation | PA_CONS_HRS PA_CONS_HRS_CD PA_CONS_HRSTAT3 PA_CONS_HRSTAT4 PA_CONS_HOUREXC PA_CONS_HOURHIR PA_CONS_HOURTER | PAY_CHECK PAY_EARNINGS PAY_OTH_EARNS PAY_SPCL_EARNS EARNINGS_BAL W2_AMOUNTS YE_AMOUNTS | PA_HST_PAY_CHK PA_HST_PAY_EARN PA_HST_OTH_EARN PA_HST_SPL_EARN PA_HST_EARN_BAL PA_HST_W2_AMTS PA_HST_YE_AMTS | PA_CONS_HRS_AY | PA_CLC_SVC_AY |
| Contribution Consolidation | PA_CONS_DED | PAY_DEDUCTIONS | PA_HST_PAY_DED | PA_CONS_DED_AY | PA_CLC_EACCT_AY |
| Plan Participation | PA_DFN_PARTIC | PA_PRTC_PRTPN | PA_CLC_VAL_CHAR PA_CLC_VAL_DT | ||
| Service | PA_DFN_SVC PA_DFN_SVC_CNV PA_DFN_SVC_STAT PA_DFN_SCAT_CAT | JOB | PA_STRTUP_HISTO PA_SVC_TRANS | PA_SVC_HIST | PA_CLC_SVC_AY PA_CLC_VAL_DEC PA_CLC_VAL_CHAR PA_CLC_VAL_DT |
| Vesting | PA_DFN_VEST PA_DFN_VEST_AGE PA_DFN_VEST_RET PA_DFN_VESTSCHD PA_DFN_VESTSTEP | PA_EMP_PEN_VEST | PA_CLC_VAL_DEC | ||
| Benefit Eligibility | PA_DFN_BEN_ELIG | PA_CLC_VAL_CHAR | |||
| Final Average Earnings | PA_DFN_AVGEARN | PA_CLC_FAE_AY PA_CLC_VAL_DEC | |||
| Cash Balance Accounts | PA_DFN_ACCOUNT | PA_STRTUP_HISTO PA_CBAL_TRANS | PA_ACCUMS | PA_CLC_ACCT_AY PA_CLC_VAL_DEC | |
| Employee Accounts | PA_DFN_ACCOUNT | PA_STRTUP_HISTO EACCT_TRANS | PA_EACCT_ACCUM | PA_CLC_EACCT_AY PA_CLC_VAL_DEC | |
| Social Security | PA_DFN_PIA PA_TWB_AMT_TBL PA_USAW_AMT_TBL PA_CPI_PCT_TBL | PA_EMP_SS_EARNS | PA_CLC_SOC_AY PA_CLC_VAL_DEC PA_CLC_VAL_DT | ||
| Covered Compensation | PA_DFN_COV_COMP PA_TWB_AMT_TBL | PA_CLC_VAL_DEC | |||
| Early/Late Adjustments | PA_DFN_AGE PA_DFN_AGE_ADJ PA_DFN_AGE_TBL | PA_CLC_VAL_DEC | |||
| Death Coverage | PA_DFN_DEATH PA_DFN_DEATHAGE PA_DFN_DEATHCOV | PA_CVRGE_HSTRY | PA_CLC_VAL_DEC | ||
| Benefit Formula | PA_DFN_FORMULA PA_CUSTOM PA_CUSTOM2 PA_CUSTOM3 (Static) | PA_CLC_VAL_DEC PA_CLC_VAL_CHAR | |||
| Employee Paid Benefit | PA_DFN_EEBENEFI | PA_CLC_VAL_DEC PA_CLC_VAL_DT | |||
| Optional Forms | PA_DFN_OPT_SET PA_DFN_OPT_FORM PA_DFN_FORM_CD (Static) | PA_CLC_OFRM_AY | |||
| 415 Limits Parameters | PA_415_LIMIT PA_415_LIMIT3 PA_TL_415_LIMIT PA_TL_415_LIMDC PA_TL_OBRA93_TB | PA_CLC_415_AY PA_CLC_VAL_DEC | |||
| Self-Service Estimate | PA_SSV_DEFAULTS PA_SSV_DISCLAIM PA_SSV_OPFORM PA_SSV_PLN_INPT PA_SSV_PROCLIST | PA_I_ESTIMATE | PA_CLC* | ||
| Pension Status | PA_PENSION_STAT PA_CUSTOM PA_CUSTOM2 PA_CUSTOM3 (Static) | PA_EMP_PEN_STAT | |||
| Database Alias | PA_ALIAS_USE | PA_CLC_ALS_VAL | |||
| Date Alias | PA_DATE PA_ALIAS_USE | PA_CLC_ALS_VAL | |||
| Duration Alias | PA_DURATION PA_ALIAS_USE | PA_CLC_ALS_VAL | |||
| Table Lookup Alias | PA_TBL_LOOKUP PA_ALIAS_USE | PA_CLC_ALS_VAL | |||
| Function Result Alias | PA_ALIAS_USE | PA_CLC_ALS_VAL | |||
| Factor Alias | PA_FACTOR_SETUP PA_ALIAS_USE | PA_CLC_ALS_VAL | |||
| Duration Options | PA_DURATION_OPT | ||||
| Date Round Options | PA_DATE_RND_OPT | ||||
| Date Conversion Options | PA_DATE_CNV_OPT | ||||
| Projection Methods | PA_PROJECTION | ||||
| 401(a)17 Parameters | PA_401A17_PARMS | ||||
| Service Schedule | PA_SVC_SCHEDS | ||||
| Interest Method | PA_DFN_INTEREST | ||||
| Actuarial Assumptions | PA_DFN_FACTOR | ||||
| Mortaility Rates | PA_MORT_TABLES PA_MORT_RATES | ||||
| Custom Statement Alias | PA_CUSTOM PA_CUSTOM2 PA_CUSTOM3 (Static) | PA_CLC_ALS_VAL | |||
| Spouse Eligibility | PA_CUSTOM PA_CUSTOM2 PA_CUSTOM3 (Static) | ||||
| Job Event Categories | PA_HR_ACT PA_HR_ACT_EFFDT PA_HR_ACTION | ||||
| Actuarial Valuation Matrix | PA_PEN_VAL PA_PEN_VAL_MTX | ||||
| Active/Inactive Jobs | PA_ACT_JOBLIST | ||||
| Service Purchase | researching... | ||||
| Retiree Administration | researching... |
Database Table Classification
In the Database Table Summary below, the tables have been organized using the following classification questions:
- What types of Pension Administration functional requirements or tasks utilize the table?
- Does the table store configuration data or member data?
- Does the table an input, or an output, of the pension processes?
- Is the data stored in the table maintained by the PSPA application, or by another HCM application such as HR, Benefits, or Payroll?
- Is the table related to Periodic Process, Calculation, or another pension process?
Summary Table Columns
The table below organizes and classifies the PSPA application database tables. The following columns are used in the summary table:
- Pension Task – the pension function used to group related database tables in the summary table.
- Rule Configuration – tables that hold the rules configuration related to the pension task. These tables do not store member data.
- Non-Pension Inputs – HR, Benefits, and Payroll tables holding member data that are used by this pension task.
- Pension Inputs – Pension tables holding member data that are used by this pension task.
- Periodic Process Outputs – tables that hold the Periodic Processing output related to this pension task.
- Calculation Outputs – tables that hold the Calculation output related to this pension task.
Purpose
Some of the ways in which this Database Table Summary is useful are:
- Review in its entirety to develop a familiarity with the PSPA application data model.
- Search to find tables related to a particular pension function or type of configuration parameter.
- Search to find where a particular table is used in the pension process data flow.
- Review to find some of the possible input data sources for pension processes.
Configuration and Customization
The complete list of tables used in a pension process will depend on the process request details, the configuration, and any customizations.
Here are some hints to find additional tables that may be used in pension processes specific to your implementation:
- Review configured Database Aliases. Referenced tables can be queried using the ALIAS_TBL_NAME and ALIAS_FLD_NAME fields on the PA_ALIAS_USE table.
- Review customizations to COBOL Stored SQL Statements.
- Review a SQL trace file of your pension process.
Leave a Reply
You must be logged in to post a comment.