New Query: ReportsTo.bqy

There is a new query available for Human Resource data warehouse users, ReportsTo.bqy. This query shows who employees report to as well as their account distributions. The query is set up to select by department, but Human Resource data warehouse users can save a copy of the bqy to their user folder and modify it as desired. The bqy include some helpful pivot views of the results as well.

Thanks to Denise DeSouza and Elaine Moore for developing this new query.

Emergency Contact.bqy Revised

The Emergency Contact.bqy in the Human Resource folder was revised. The Query Primary Emergency Contact was changed to also list employees without a primary emergency contact. A second query was added to the bqy file, Query All Emergency Contacts which lists all emergency contacts for employees, not just the primary contact.

Employees can update their emergency contact information via ASU Interactive > Employee Self Service > Personal Information at https://www.asu.edu/go/personalinformation/. Changes will be reflected in the ASU Data Warehouse the next day.

ERE Encumbrances on Sponsored Accounts Changes

Some enhancements to the process used to calculate ERE encumbrances on sponsored accounts are expected to be operational on May 16, 2008.

As you know ERE encumbrances are applied to Sponsored Program's Accounts only. The encumbrance amount is calculated by using the actuals and encumbrance JVs that are interfaced between HRIS and Advantage, so these files are retained after each payroll cycle. The amount that is encumbered is calculated using the following formula:

ERE Rate = (ERE Actuals) / (Salaries)

ERE Encumbrance amount = (Encumbrances) X (ERE Rate)

This process is performed in a job that runs after both the Expenses (Actuals) and Encumbrances have interfaced into Advantage. The process also zeros out the previous encumbrance amount, so it is a full reset.

Some recent changes that have been made:

1. Provide for a merge of RA/TA expenses (which are currently entered by way of a file provided by FS), so that those expenses are part of the calculation.
2. Off-cycle ERE encumbrance calculations have been "off" because the actuals are limited to only those accounts with activity, yet we get a full encumbrance file. The solution, for now, is to retain the calculated rates from the On-cycles and then apply those rates to the encumbrances during off-cycle processing.

Additional information on Human Resource related accounting topics can be found on Financial Services' Commitment Accounting web page, http://www.asu.edu/fs/commitment_accounting.html

Three New Human Resource Expenditure Views!

Three new view tables have been added that will make reporting on Human Resource expenditures easier and more flexible. The new tables are:

• PS_ASU_PAY_EXP_VW
• PS_ASU_PAY_ERN_VW
• PS_ASU_PAY_ERE_VW

PS_ASU_PAY_EXP_VW contains salaries, wages, and ERE expenditures. PS_ASU_PAY_ERN_VW contains only salaries and wages; and PS_ASU_PAY_ERE_VW contains only ERE.

All three views contain both original payroll distribution records as well as redistribution transactions that have been approved.

The existing Human Resource myReport query, HR Expenditures.bqy has been modified to use the new views.

All three views contain the same columns of data:

• Distrib Type
• Source Cd
• PayGroup
• Pay End Dt
• Off Cycle
• Page Num
• Line Num
• Spechk
• Erncd
• Fiscal Year
• Accounting Period
• Acct Cd
• Budget Data
• Position Nbr
• Name
• Emplid
• EmplRcd
• Amount
• Transaction Nbr
• ASU AgcyOrg
• Fund Code
• ASU Sorg
• ASU Expenditure Cd
• Approval Status
• Deptid
• Paycheck Option
• Approval Oprid

A big thank you to Laura Lee for making this happen.

"Lost" Data Warehouse Access Requests

A note of warning: email generated by the data warehouse access request workflow can be diverted by your junk email filter. If you or your supervisor do not receive anticipated notifications from the workflow, check your junk email folder.

New View - PS_ASU_PAYCK_AR_VW

A new table, PS_ASU_PAYCK_AR_VW is now available to Human Resource data warehouse users. PS_ASU_PAYCK_AR_VW is a view of the PS_PAY_CHECK table that does not include employee home address information.

The batch query output, Output from Daily(cycle) of job Pay Check, which was in the Human Resource/Content Underdevelopment folder will no longer be produced. Human Resource data warehouse users should now use PS_ASU_PAYCK_AR_VW as an alternative source of this information.

Pay Distribution Batch Query Discontinued

Running of the batch query, Pay Distribution, will be discontinued. This batch query was originally developed because the tables showing position accounting distributions were not in the ASU Data Warehouse. The batch query was run nightly against the operational system and the results stored in myReports in order to give data warehouse users access to this data. The PS_S_DEPT_BUDGET and the PS_DEPT_BUDGET_ERN tables have been added to the ASU Data Warehouse and running the batch query against the operational system is no longer necessary.

Employee Primary Emergency Contact Information

Employee primary emergency contact information is now available in the ASU Data Warehouse table, PS_ASU_D_EMP_AR_VW. See fields Emerg_Cntct_Nm and Emerg_Phone_Num.

Previously, if an employee selected the option that the primary emergency contact's phone number was as the same as the employee's home phone number, Emerg_Phone_Num would be blank in PS_ASU_D_EMP_AR_VW. This has been changed so that the employee's home phone number now appears in the Emerg_Phone_Num.

A query named "Emergency Contact.bqy" is available in the myReports Human Resource folder.

More complete emergency contact information is available in the PS_EMERGENCY_CNTCT table.

EPM Release Notes for 9.0.27

A new set of PeopleSoft tables have been added to the ASU Data Warehouse. These new or changed tables and views include:

Student Records

• PS_ASU_SF0037_AGCY

Human Resources

• PS_CONTRACT
• PS_ASU_BEN_PRE_FRQ
• PS_ASU_BEN_PRE_TRA
• PS_ASU_PC_ENROLL
• PS_ASU_PRE_COLLECT
• PS_HRS_JO_POSTING

DARS

• DA_MASTREF
• DA_MASTREF_ADDRESS
• DA_MASTREF_SEG
• DA_TCA_MASTER
• DA_TCA_RULE
• DA_TCA_SOURCE
• DA_TCA_TARGET

Two Helpful New HR Tables Available

Two new tables are available to HR data warehouse users: PS_ASU_HR158_VW and PS_ASU_CUR_JOB_VW. These "views" will make query development easier since they eliminate the need to do effective date related subqueries. The new tables only contain the most current record. Past and future effective dated records have been filtered out.

The PS_ASU_HR158_VW was inspired by the legacy data warehouse's tblHR158, hence the name. The goal was to provide current job information including position funding sources. Based on the PS_JOB table, the view pulls in data from ten other tables including the current position distribution, job code salary ranges, tenure information, and vacation plan information.

Some of the column names require some explanation:

• DEPTID2 is the home department which should be the department of an employee's primary job.
• DEPTID_LD is the description of the home department.
• DESCR is the title associated with the job code.
• DEPTID is the department associated with the job.
• DESCR2 is the job department's description
• Amount is Annual Rate * position distribution percentage

We plan to create a view on top of this view that will have more descriptive column names, but do not want to do this until you all have had a chance to use the existing view and point out all its shortcomings. So please, let us know if you see data that looks incorrect.

PS_ASU_CUR_JOB_VW was created along the way as a convenience while working on the PS_ASU_HR158_VW table. It is simply the PS_JOB table filtered to only the current record.

Kudos to Mark Monninger for developing these helpful new tables and to Robin Gonzalez for defining the requirements.