Payroll-Related Tables
This topic discusses:
Payroll-related tables.
Employee data input tables.
Effect of database changes.
Payroll process input tables.
Paycheck output tables.
Payroll for North America is a set of input tables, processes, and output tables. You set up your input tables, run a process, and the process, in turn, writes new data to the output tables.
This table lists examples of Payroll for North America input tables with their associated processes and output tables
Input Tables |
Processes |
Output Tables |
---|---|---|
Employee Data |
Payroll |
Paycheck |
Job |
Create Paysheet |
Checks |
Benefits |
Pay Calculation |
Deductions |
Deductions |
Pay Confirmation |
Taxes |
|
Actuals Distribution |
Balances |
Pay Process |
Reversal Processing |
Messages |
Earnings |
Calendar Build |
|
Deductions |
Delete Balances (U.S.), Delete Balances (Canada) |
|
Taxes |
Check Reprint |
|
|
Unconfirm Pay |
|
|
Payroll Unsheet |
|
|
|
|
|
Benefits |
|
|
Calculate Deductions |
|
|
Confirm Deductions |
|
|
Savings Management Balances |
|
|
Leave Accrual |
|
|
|
|
|
Human Resources |
|
|
Batch Encumbrance Process |
|
|
Tenure/Flexible Service Calc'n (tenure/flexible service calculation) |
|
Note: This chart doesn't list actual table names, but are generic classifications for the various tables in the system.
Running Payroll for North America processes might be labeled a quiet task. The processes start, run, and stop, rarely putting out more than five or ten lines of output, which typically consist of messages to the effect that the process began at a certain time, a certain pay group is being processed, and finally, that the run has finished.
The system writes all results of the Payroll for North America processes back into the database. Any tangible outputs from these processes, such as paychecks, are all reports based on database tables. To get the physical paychecks, for example, you run an Structured Query Report (SQR) against the database.
So what does a typical Payroll for North America process do? It reads some input from various tables, writes new data back into the database, and goes away. A process might send a message that says "Check Messages!!!". That means it wrote at least one message into the message table. In that case, you must go back and run a report against the message table to get the messages, or view the messages online on the Paysheets page, Payroll Messages page.
Employee data input tables can be classified as employee jobs, benefits, and deductions. Online, some of these tables are found in the Workforce Administration menu, others in the Employee Pay Data menu, and some in the Benefits menu.
You hire employees and set up their job data; enroll them in benefits programs and set up their benefits data; set them up for general deductions and enter this information on the database. You insert rows into their various job, benefits, and deduction tables:
This table lists the Payroll for North America employee data input tables and the pages where you enter the data.
Table Name |
Online Page Name |
---|---|
ADDL_PAY_DATA ADDL_PAY_EFFDT ADDL_PAY_ERNCD |
Additional Pay |
DIRECT_DEPOSIT DIR_DEP_DISTRIB |
Direct Deposit |
EMPL_DED_PROC |
General Deduction Override |
GENL_DEDUCTION GENL_DED_CD |
General Deduction Data |
GARN_SPEC GARN_SCHED GARN_RULE GARN_EMPL_DED |
Garnishment Specification Data |
TAX_DIST_EFFDT TAX_DISTRIB |
Update Tax Distribution |
JOB |
Job |
PAYROLL_DATA ADDRESSES |
Payroll Data |
PERS_DATA_EFFDT PERSON ADDRESSES PERSONAL_PHONE EMAIL_ADDRESSES |
Personal Data |
BEN_PROG_PARTIC |
Assign to Benefit Program |
DEP_BEN DEP_BEN_EFF DEP_BEN_NAME DEP_BEN_ADDR |
Dependent/Beneficiary Data |
DISABILITY_BEN BENEFIT_PARTIC |
Disability Benefits |
FSA_BENEFIT BENEFIT_PARTIC |
FSA Benefits |
HEALTH_BENEFIT BENEFIT_PARTIC |
Health Benefits |
LEAVE_ACCRUAL |
Leave Accruals |
LEAVE_PLAN BENEFIT_PARTIC |
Leave Plans |
LIFE_ADD_BEN BENEFIT_PARTIC |
Life and AD/D Benefits |
RTRMNT_PLAN BENEFIT_PARTIC |
Retirement Plans |
SAVINGS_PLAN SAVINGS_BENEFIC SAVINGS_INVEST BENEFIT_PARTIC |
Savings Plans |
VACATION_BEN BENEFIT_PARTIC |
Vacation Benefit |
PENSION_BENEFC PENSION_PLAN |
Pension Plans |
CAN_TAX_DATA CAN_TAX_PRVDATA EMPL_WAGELS_CAN |
Canadian Income Tax Data |
FED_TAX_DATA STATE_TAX_DATA LOCAL_TAX_DATA TAX_DIST_EFFDT TAX_DISTRIB TREATY_EARN_TBL (E&G only) |
Federal Tax Data State Tax Data Local Tax Data Update Tax Distribution |
CONTRACT |
Contract Pay Data |
EG_FLX_SVC_DT EG_PRIOR_CREDIT EG_SVC_HIST EG_SVC_HIST_FLX EG_TENURE_DATA |
Flexible Service Data Prior Credit Data Service History Data Flexible Service History Data Tenure Data |
Employee data tables all have EMPLID as a key, so if you must find them all, a good way to start is to look at those tables containing EMPLID. To do this, use the Records and Fields PeopleTools Cross Reference Report (XRFFLRC).
One of the advantages of Payroll for North America is that you can use PeopleTools to easily configure the payroll tables. However, you keep in mind how any changes you make might move through the database, particularly considering the interdependency not only of tables, but also of the batch processes that call them.
Suppose you decide you have to add a column (a field) to PERSONAL_DATA. PeopleSoft batch processes never issue a SQL SELECT statement against a table. The system always selects only the columns that it wants from the table. If it wants to retrieve or update rows of the employee names, for example, it specifically asks for the NAME. It never makes an assumption about what columns are on that table.
Consequently, if you add a column to PERSONAL_DATA, it doesn't affect batch payroll processes, because if a batch process wants something from PERSONAL_DATA, such as the employee's address, it asks for the address by column name. So, in effect, you can add multiple columns, and if you do not need or want your new columns to affect batch processing, that's OK, because the processes ignores them. Any tables that you modify, of course, are maintained by the online system, so you must modify and store the appropriate records (and possibly pages).
You must be more careful when modifying existing columns that are used by the batch processes. For example, you might decide to alter the HOURLY_RT column on the JOB table. You need seven decimal places instead of the six provided in PeopleSoft. Now, the batch Payroll system is set up to read the HOURLY_RT column from the JOB table; and when it does so, it makes the assumption that it's reading the HOURLY_RT as defined in the standard application. In this case, modifying an existing column affects payroll processing, because the COBOL programs must know the format of the data to be mapped. Because the HOURLY_RT column is used by Payroll, you also have to modify some of the batch processes.
Remember also that if you change the name of the HOURLY_RT column, the batch processes won't be able to find it unless you modify the batch processes accordingly.
Some of the tables—particularly the output tables—are maintained by batch processes (DEDUCTION_BAL, the Deduction Balance table, for example). During normal processing, the system inserts, updates, and possibly even deletes individual rows. When you add new or modify existing columns on these tables, you have to make the corresponding COBOL changes.
For payroll process input tables, you set up much of the controlling information for running payrolls, information having to do with the specifics of particular earnings, deductions, or taxes. You can find these tables online in the
for North America menu, unless otherwise indicated.The fact that an employee works in the state of New York is employee-level data stored in the employee data input tables. But to calculate that employee's taxes, the system checks the appropriate pay process input table that defines tax rates for employees who work in New York:
This table lists the Payroll for North America payroll process input tables and the pages where you enter the data
Table Name |
Online Page Name |
---|---|
ACCT_CD_TBL PYCF_VERTGRID_WK |
Account Code Table |
BALANCE_ID_TBL BALANCE_ID_DEF BALANCE_ID_QTR |
Balance ID Table |
BANK_EC_TBL BANK_BRANCH_TBL |
Bank Table |
COMP_RATECD_TBL |
Comp Rate Code Table |
DEDUCTION_TBL DEDUCTION_CLASS DEDUCTION_FREQ EARNS_SPCL_DEDN DED_SUBSET_CD |
Deduction Table |
DEPT_BUDGET DEPT_BUDGET_DT DEPT_BUDGET_ERN DEPT_BUDGET_DED DEPT_BUDGET_TAX DEPT_BUDGET_CTX DEPTBDGT_SUSPNS |
Department Budget Table (found online in the setup menu for the Commitment Accounting business process) |
DED_SUBSET_TBL |
Deduction Subset Table |
EARNINGS_TBL EARNINGS_ACCRL EARNINGS_SPCL |
Earnings Table |
ENCUMB_DEFN_TBL ENCUMB_DEFN_DED ENCUMB_DEFN_TAX |
Encumbrance Definition Table (found online in the setup menu for the Commitment Accounting business process) |
ERN_PROGRAM_TBL ERN_PROGRAM_DEF |
Earnings Program Table |
VENDOR |
Garnishment Payee Table |
GARN_RULE_TBL GARN_DE_EARN GARN_EXEMPT_TBL GARN_OPERANDS |
Garnishment Rules Table |
GARN_DE_DEFN GARN_DE_DED GARN_DE_DEF_LNG |
Garnishment DE Definition Garnishment DE Definition Related Language Table |
GARN_DE_DED |
Garnishment Disposable Earnings Deduction Table |
GARN_DE_EARN |
Garnishment DE Earnings Table |
GARN_EXEMPT_TBL |
Garnishment Exemption Table |
GARN_OPERANDS |
Garnishment Operands Table |
GARN_PRORATE_DF |
Garn Prorate Defn |
GARN_PRORATE_RL |
Garnishment Proration Rules |
GENL_DED_TBL |
General Deduction Table |
HOLIDAY_TBL HOLIDAY_DATE |
Holiday Schedule Table |
LIMIT_TBL LIMIT_EXCLD_TBL LIMIT_INCLD_TBL LIMIT_IMPIN_TBL LIMIT_COORD_TBL |
Limit Table (found online in the Benefits menu) |
PAY_FORM_TBL |
Form Table |
PAYGROUP_TBL PAYGRP_EMPLTYPE PY_PAYGRP_TASK |
Pay Group Table |
PAY_CALENDAR |
Pay Calendar Table |
PAY_MESSAGE_TBL |
Pay Message Table |
PAY_OL_PRNTCTL |
Configure Online Printing |
PAY_RUN_TBL |
Pay Run Table |
COMPRT_GRP_TBL |
Rate Code Groups |
SAVINGS_PLN_TBL SAVINGS_INV_TBL |
Savings Table (found online in the Benefits menu) |
SHIFT_TBL |
Shift Table |
SPCL_EARNS_TBL |
Special Accumulator Table |
TERM_ACTN_RSN |
Final Check Action/Reason |
TERM_PGM_TBL |
Final Check Program Table |
EG_AUDIT |
Application Trace
|
EG_CAL_DEFN_TBL EG_CAL_DETP_TBL |
Calendar
|
EG_DATE_CNV_OPT |
Date Conversion Table
|
EG_DURATION_OPT |
Duration Option Table
|
EG_DATE_RND_OPT |
Date Round Option Table
|
EG_DFN_SCAT_CAT EG_DFN_SVC |
Service Rules Table
|
EG_HR_ACTION |
HR Action/Reason Category Table
|
Tax Tables |
Online Page Name |
---|---|
EG_CALC_GRP_TBL EG_FNC_RSLT_DFN EG_FUNCTN_RESLT |
Calculation Group Table |
CO_LOCALTAX_TBL |
Company Local Tax Table |
CO_STATETAX_TBL |
Company State Tax Table |
LOCTX_RECIP_TBL |
Local Tax Reciprocity Table |
LCLWK_TXRCP_TBL |
Local Work-Work Reciprocity Table |
LOCAL_TAX_TBL |
Local Tax Table |
LOCAL_TAX_TBL2 |
Local Tax Table - User defined fields |
US_SOC_TBL |
U.S. SOC Table |
STATE_TAX_TBL STATE_TAXRT_TBL ST_OTH_TAX_TBL |
Federal/State Tax Table |
STTAX_RECIP_TBL |
State Tax Reciprocity Table |
SWT_MARSTAT_TBL |
SWT Marital Status Table |
TAXFORM_TBL TAXFORM_BOX TAXFORM_DED TAXFORM_ERN |
Tax Form Definition Table
|
TAXGR_BASE_TBL TAXGR_DEFN_TBL TAXGR_CMPNT_TBL |
Taxable Gross Definition Table |
TREATY_NRA_TBL TREATY_EARN_TBL |
Treaty/Non-Resident Alien Table |
TAX_LOCATION1 TAX_LOCATION2 |
Tax Location Table |
VENDOR |
Tax Collector Table |
CO_UI_RPTCD_TBL |
UI Report Code Table |
VDI_ADMIN_TBL |
VDI/FLI Administrator Table |
CAN_USR_TAX_TBL CAN_USR_TAX_PRV |
Canadian Company Tax Table |
CAN_TAX_CITLUMP CAN_TAX_CITRATE CAN_TAX_PROV CAN_TAX_PROVNCT CAN_TAX_PROVTHR CAN_TAX_QITLUMP CAN_TAX_QITRATE CAN_TAX_TBL |
Canadian Tax Table |
WAGELS_PLAN_TBL WGLS_LIA_GL_ACC WGLS_EXP_GL_ACC |
Canadian Wage Loss Plan Table |
CAN_WCB_PROV CAN_WCB_RT_GRP CAN_WCB_CU |
Canadian WCB Classifications |
BANK_BRANCH_TBL |
Bank Branch Table |
Paycheck output tables are result tables written to and maintained by the batch processes. Some paycheck output tables are, in turn, inputs to other batch processes:
This table lists the Payroll for North American payroll input tables (paysheets) and their corresponding output, or balance, tables.
Payroll Input Tables (Paysheets) |
Payroll Output Tables (Balance Tables) |
---|---|
PAY_CTX_OVRD |
PAY_CHECK |
PAY_EARNINGS |
PAY_DEDUCTION |
PAY_GARN_OVRD |
PAY_DISTRIBUTN |
PAY_LINE |
PAY_EARNINGS |
PAY_ONE_TIME |
PAY_GARNISH |
PAY_OTH_EARNS |
PAY_INS_EARNS (CAN ONLY) |
PAY_PAGE |
PAY_MESSAGE |
PAY_TAX_OVRD |
PAY_OTH_EARNS |
|
PAY_SPCL_EARNS |
|
PAY_TAX |
|
PAY_TAX_1042 |
|
PAY_TAX_CAN |
The tables on the left are payroll input tables. Often, they are populated with data during the Create Paysheet process, so in that sense they are output tables. But after they are created, they serve as inputs to the other payroll batch processes, such as Pay Calculation and Pay Confirmation. Create Paysheet is a process that goes through the database and forms a proposal to pay someone; it creates the paysheets. When you view paysheets online, you're looking at data from the payroll input tables. When you make one-time tax, deduction, or garnishment changes to an employee's paysheet, you are writing data to these tables, data that the batch processes uses in the next step of converting input to output.
The tables on the right are payroll output tables; most of them also have corresponding balance tables. These tables are the result of running one of the payroll batch processes. These tables are maintained by the Payroll for North America system. That is, these are tables that the system uses to write back to the database during processing.
Notice that the PAY_EARNINGS table functions both as a payroll input table and as a payroll output table. The Create Paysheet process initially generates information in PAY_EARNINGS. Your online users might then update the table. However, the Pay Calculation and Pay Confirmation processes subsequently write results back into PAY_EARNINGS, because PAY_EARNINGS also serves as the history record of the employee's earnings.
PAY_EARNINGS before and after pay confirmation is the same table, but there is a difference. The PeopleCode behind the data-entry paysheets pages doesn't read any data that you've already confirmed. That is, the system considers information on a confirmed PAY_EARNINGS row ineligible to display on a paysheet. The information, however, is still there; it exists until you purge it.
PAY_OTH_EARNS is treated in a similar fashion.