Reporting Earnings from PeopleSoft
It’s necessary to provide a warning about reporting the distribution of earnings and employee related expenditures (ERE) which should be explained to the requesting parties:
PeopleSoft (PS) shows the distribution of earnings as expensed to Advantage. However, once those transactions are in the Advantage financial system, users REGULARLY submit “Payroll Expense Transfer” forms to shift expenses to a different funding source than what was submitted in the PS transaction. As a result, total expense distribution in PS IS NEVER in synch with the financial system. A true total of actual expenditures by funding source can only be obtained from Advantage.
The journal ledger fields from Advantage are stored in the PS account code table (Acct_CD_Tbl). The linking field in the Acct_Cd_Tbl is Acct_Cd.
1. You can link the Advantage fields to actual expenses directly in the Pay_ERN_Dist (or substitute DED/TAX) table by Acct_CD. In doing a JOIN either in SQL or PS, you only need to join by Acct_Cd. In PS queries the system wants to add DeptID to the join, but DeptID is used differently in the Acct_Cd_Tbl than it is elsewhere so delete that join if it’s added.
2. If you want to link the Advantage fields to “positions” in PeopleSoft, you first link Position_Data to the Dept_Budget_ERN (or substitute DED/TAX) table by DEPTID and POSITION_NBR. The Advantage fields are then linked to the Dept_Budget_ERN/Ded/Tax tables by Acct_Cd. In doing a JOIN either in SQL or PS, you only need to join by Acct_Cd. In PS queries the system wants to add DeptID to the join, but DeptID is used differently in the Acct_Cd_Tbl than it is elsewhere so delete that join if it’s added.
3. If you want to link the Advantage fields to employees in PeopleSoft, you first link JOB to the Dept_Budget_ERN (or substitute DED/TAX) table by DEPTID and POSITION_NBR. The Advantage fields are then linked to the Dept_Budget_ERN/Ded/Tax tables by Acct_Cd. In doing a JOIN either in SQL or PS, you only need to join by Acct_Cd. In PS queries the system wants to add DeptID to the join, but DeptID is used differently in the Acct_Cd_Tbl than it is elsewhere so delete that join if it’s added.
4. If users want “budgeted” information, refer them to the Budget Office.
Due to the size of the department budget tables, I often pull funding data and join it to JOB or Position Data in ACCESS.
Existing PS Queries
- N__NAU_BD_BF_STU_WKS_EARNINGS Stu/WorkSt with Fund-Input FY This pulls earnings rows with funding source for student wage and work study. Input the fiscal year (2004 for FY 03-04). You’ll have to eliminate the state/local yourself.
- N__NAU_BD_BF_BUDBOOK_FUNDING Budget Book Funding (pulls 7/1 rows which are locked and used “State Budget” rows for all positions.)
- N__NAU_BD_BF_ACTV_FUNDING_CKFY All Active funding – Check FY (pulls current funding row for all positions)
Translating Advantage terminology to PeopleSoft fields
In Advantage & PeopleSoft, the fields that indicate funding sources and expenditure types are:
Advantage PeopleSoft Description
Fund Fund_code Highest level – state/local/grant/restricted
Agency Program_code Identifies the “department” that has responsibility for the
account
Orgn (organization) Class_fld Identifies specific accounts within a department. There can
be multiple accounts at the state, local and grant/restr level
Object Code Account Identifies the expenditure type (e.g. salaries, student wage,
Other wages, work study, graduate assistant for salaries; FICA,
Health, federal taxes, state taxes, etc., etc, for deductions and
taxes known as ERE– basically all the deductions on paycheck.
Determining the “funding source”
Your requests will most often be for totals at the highest “fund” level. The “rules” for interpreting NAU’s system haven’t changed in 20 years. The first character of both the Fund/Fund_code and the first character of the Orgn/Class_field will tell you what type of funding source is used.
Fund Type First Character Fund Orgns
State Funds 1 1100 or 1200 11nn or 12nn
Local Funds 2 2000-2999 2nnn
4 4000-4999 4nnn
Grant/Restricted 3 3 alpha/numeric like 3R53 = Fund
R R alpha/numeric = Fund
Prop 301 T T alpha/numeric = Fund
Work Study Federal Funds 33R1 (Agency=FIN, Orgn=33R1) = Fund
Determining the “expense type”
The expense type is represented by the Advantage object code or PS Account (not to be confused with Acct_Cd). Earnings start with 71nn and ERE starts with 72nn.
Obj/Account Expense Type
7110 Benefit eligible earnings
7120 Temporary non-benefit eligible earnings
7130 Student Wage earnings
7140 Work Study earnings
7150 Graduate Assistant earnings
72nn ERE
There are zillions of these – one for each type of expenditure listed on the paycheck.
Before the expenditures are passed to Advantage 7211 is the default used in PS
Connecting Paygroup with Object Code
Providing the department selects the correct position for an employee, the following should be a translation of earnings type represented by the object code/Account to the PS PayGroup
Obj/Account Paygroup
7110 CLS – classified staff
CRG- contract pay
FAC-faculty over 12
APP-appointed over 12
7120 TMP – temps and part-time faculty
7130 STU – student wage
7140 WKS – work study student
7150 GRD – graduate asistant
Joining the Acct_Cd_Tbl to the distribution tables (Pay_ERN_Dist or DED/TAX)
Or the Budget Tables (Dept_Budget_ERN or DED/TAX)
There is only one row in the Acct_Cd_Tbl for each Advantage “key” in PeopleSoft, so you don’t have to worry about whether it is active or inactive or whether you’re getting the right date row – the other fields NEVER change. The table is populated nightly by an NAU mod which adds or inactivates the rows.
In doing a JOIN either in SQL or PS, you only need to join by Acct_Cd. The system wants to add DeptID to the join, but DeptID is used differently in the Acct_Cd_Tbl than it is elsewhere so delete that join if it’s added.
“Budget” information is located in the Dept_Budget_ERN/DED/TAX tables. The percentages in those tables can be applied against employee salaries (ANNL_BENEF_BASE_RT) to split out the budgeted salary.
Paid earnings are appropriately split to accounts in the Pay_ERN_Dist (DED/TAX) tables. These tables contain the fiscal year, so that you can easily sum up all earnings from these tables.