Monday, December 8, 2008

HR Expenditures Replaces Pay Earnings with Redistributions

The "Pay Earnings with Redistributions.bqy" query has been decommissioned. Please use the "HR Expenditures.bqy" query instead. HR Expenditures takes advantage of custom views created to avoid the complexities of a union query. See Three New Human Resource Expenditure Views!

Friday, November 21, 2008

11/19/2008 ASU EPM 9.0.36 Release Notes

New or Changed Tables and Views Operational Warehouse Staging (OWS) Tables/Views -Student Records




  • PS_ASU_AI0018_CMTE



Changes to EPM Business Rules and Logic - None

Friday, September 12, 2008

EPM 9.0.34 Release Notes

09/10/2008 ASU EPM 9.0.34 Release Notes - available 09/11/2008

New or Changed Tables and Views

Changed: Operational Warehouse Staging (OWS) Tables/Views. These record definitions were altered to match changes in PeopleSoft SAPRD:

PS_ASU_AI0008_PLAN (CRM #312173)
PS_AUDIT_ASU_AI10 (CRM #313183)

New: Operational Warehouse Staging (OWS) Tables/Views in Student Records mart:


Changes to EPM Business Rules and Logic

Multi-Dimensional Warehouse Tables/Views

PS_ASU_D_PERSON and PS_ASU_D_PERSON_VW: resolved delivered Oracle issue preventing future-dated person attributes from being reflected consistently (CRM #313577)

Budget Position Control Dashboard Search Enhancement

The Budget Position Control 2009 dashboard search capabilities have been enhanced. You now have the ability to search by department, account, position, or employee id from any dashboard page, not just the initial page.

Friday, September 5, 2008

Budget Position Control Status Update

An update on Budget Position Control:


Three new views and one new table have been added to the Human Resources Data Warehouse:


The ASU_BPC_VW views contain BPC related information consolidated from several sources. Data includes Fiscal Year, Account, Position Number, Employee ID, Original Budget, Budget Adjustments, Expense, Encumbrance, and budgeted FTE. The Original Budget and budgeted FTE are sourced from STAR. The Expense and Encumbrance amounts originate from the PeopleSoft. The Budget Adjustments come from the Advantage fed Financial data warehouse. The ASU_BPC_VW combines all fiscal years in a single view.

ST_ORIGINAL_BUDGET contains the data that has been interfaced from STAR into the Human Resource Data Warehouse that was used for the ASU_BPC_VW_2009.
As of today these views and table are available to My Reports users; however, the budget amounts are not yet complete. State budgets are loaded but not yet finalized. Local budgets are expected to be loaded during September.


A new query, Budget Position Control.bqy is available in the Human Resource folder. Some enhancements have been added to the BPC 2008 query. Fund, ABOR classification, and Grade have been added.


The BPC 2009 dashboard went into Production 9/4/2008 at The BPC 2008 dashboard has been removed from the main dashboard menu but can be accessed from within the BPC 2009 dashboard.

Wednesday, August 27, 2008

Firefox 3.0.1 and My Reports

Firefox 3.0.1 does not currently work with My Reports. Do not upgrade to this version of the Firefox browser at this time if you are using My Reports.

Monday, August 18, 2008

RA TA Health and Tuition.bqy Revised

The RA TA Health and Tuition.bqy in the My Reports Human Resource folder has been revised to reflect the new rates for fiscal year 2009. The new rate for Health is 7.75% and the new rate for Tuition is 41.28%. In 2009, these rates are applicable to all earnings codes for RAs and TAs, not only GRD and GCP earnings codes.

Tuesday, August 12, 2008

BPC 2008 Modifications

Some behind the scenes modifications to the Budget Position Control (BPC ) 2008 dashboard and My Reports query have been made in preparation for the fiscal year 2009 version of Budget Position Control. One of the changes was the renaming of the PS_POSITION_NBR field in the ASU_BPC_VW to POSITION_NBR. The dashboard and the My Reports query, BPC_2008.bqy have been modified for this change. If you have stored a copy of BPC_2008.bqy in your user folder, you'll need get a revised copy from the Human Resource folder.

Thursday, August 7, 2008

PeopleSoft Security Role Assignments

Losing track of who has what PeopleSoft security roles in your department? Check out the new query in the Human Resource folder, PeopleSoft Security Role Assignments. The query is based on a table just added to the data warehouse, PS_S_ROLEUSER_VW.

Wednesday, July 23, 2008

Effort Reporting Tables in Human Resource Data Warhouse

You may have noticed many new tables have been added to the Human Resource Data Warehouse that begin with PS_ER or PS_ASU_ER. These tables contain Effort Reporting data. Following the standard table naming convention, the PS_ tables are delivered PeopleSoft tables and the tables beginning with PS_ASU are custom ASU developed tables.

Tuesday, July 15, 2008

EPM Release Notes: 9.0.30

New tables:



  • PS_ASU_AI0008_CTVW

  • PS_ASU_AI0008_V1

  • PS_ASU_AI0008_V2

Changed tables:

  • PS_ASU_AI0008_REQ: added ASU_REQ_CRSE_ID to table and primary key

Monday, June 30, 2008

How is the primary job determined?

The JOB_INDICATOR code on the PS_JOB table is used to indicate if the job is the employee's primary job or not. Some ASU employees have more than one job at ASU. An employee's primary job record in the PS_JOB table has a "P" in the JOB_INDICATOR code. Other jobs on PS_JOB table for the employee have either an "N" or an "S".

The current logic for setting the JOB_INDICATOR is:

If there is only one job for the employee, that is the primary job. If the employee has multiple jobs, the primary job is the job with the highest annual salary that is not terminated or retired (PS_JOB.EMPL_STATUS = R or T). If there is more than one job with the same annual salary, then the job with the lowest PS_JOB.EMPL_RCD is be set to be the primary job.

What is the difference between N and S?

Currently, non-primary jobs are assigned a job indicator code = N. In the past, non-primary jobs were assigned a job indicator code = S. There were some challenges with early versions of the job indicator assignment program. When the current version of the program was rolled out on June 11, 2008, it was decided not to fix historical records but to begin anew with a different coding structure.

Wednesday, May 28, 2008

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.

Thursday, May 22, 2008

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 Changes will be reflected in the ASU Data Warehouse the next day.

Friday, May 16, 2008

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,

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 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.

Thursday, May 15, 2008

"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.

Wednesday, May 14, 2008


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.

Wednesday, May 7, 2008

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.

Tuesday, May 6, 2008

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.

Friday, May 2, 2008

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


Thursday, May 1, 2008

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.