Decision Support- HR CMAC, May 2008
Case Date
# Number Contact Title Description Size Prioritzed Priority Request Type Status
1 20070911_820 Greenwalt, Add Relationship Description to the In order to assist users in determining the relationship of the emergency Medium 1/16/2008 2 Enhancement Closed on
Karen EDW Emergency Contact Information contact data to the employee, they may utilize the 3/28/2008
Emergency Contact Relationship Code on the EDW Emergency Contact
Information table. However the Relationship Code
contains only the possible values of 1-9 and A-Z. It would be helpful to
add the Banner description of this code. The
corresponding descriptions include details such as "Spouse", "Father",
"Stepson", "Sister-in-law", etc.
The Emergency Contact Relationship Code Description resides in the
Banner table STVRELT. This table is not currently
sourced into the EDW.
2 20080306_206 Pollard, Mark Universe Only Change: Correct It was found during the creation of a BO report that adding the HR Payroll Small Fast Track Closed
T_FIN_PGM_CD_HIST.CII in HR_PAY object "Acctg Detl Financial Pgm Title" to an on
universe existing report made the results return 0 rows, whereas rows were 3/28/2008
populated before this addition.
In the BO logic for using the program title's source table
(T_FIN_PGM_CD_HIST), current info indicator = 'Y' is necessary
since this is a history table and only current rows should be returned. The
logic instead has T_FIN_PGM_CD_HIST.PGM_CD
= 'Y'. This is incorrect and not viable for output, therefore it results in 0
rows returned. Financial Program Code should be
changed to Current Info Indicator column in order for the report to run
correctly.
3 20071030_277 Thibadeau, Add Supervisor Information to EDW and In order to track information pertaining to which employees work for a Medium 1/16/2008 3 Enhancement Closed
Joy PJH Universe particular supervisor, on 4/4/2008
a request has been made to add supervisor information to the PJH
Universe. Request has been made to add a supervisor uin,
first, last name, middle initial, supervisor name, supervisor job suffix
supervisor position title and supervisor position number.
4 20080417_421 Ghadeer, Remove Inner Joins in Employee Admin When Visa information and faculty information were queried on, it Medium Defect Closed on
Amira Universe appeared 5/6/2008
to be a problem with the joins, but it was actually corrected by filtering the
data.
13b3cbcb-6c06-450b-a59e-c937e61fe70e.xlsx-HR 1 11/15/2011 @ 9:55 PM
Decision Support- HR CMAC, May 2008
Case Date
# Number Contact Title Description Size Prioritzed Priority Request Type Status
5 20061106_424 Rosario, Labor Distribution addition The requestor would like DS to add the CFOAPAL Amount from the Medium 7/19/2007 5 Enhancement Scheduled
Edgar BANNER NZIESUM screen to the EDW. It is a Release Date
calculated amount. The field may be named 'CFOAPAL Salary Amount'. 5/15/2008
It should be added to the T_JOB_LBR_DISTR table
6 20080221_171 Pollard, Mark and the had several Clarify cases universe.
Improve Usability of the Payroll Universe We haveHR Position Job Hierarchy in the past several years regarding the Medium Defect Scheduled
poor performance around the Payroll Universe. We need to do a Release Date
comprehensive analysis of all the options around how to improve the 6/19/2008
performance of V_Payroll_Acctg_Detl.
7 20040428_53 Wilson, Primary Job Indicator on T_JOB_HIST The Primary Job Indicator is fed from a checkbox on the Banner form, but Large 5/17/2007 1 Enhancement Pending
Theresa not working as the University designed it is not being used correctly or consistently by the
University community. However, the Primary Job Indicator is the only
way to limit a person to one of the several jobs they
may have. This is used for counting employees on mandated reports.
8 20070131_381 Rascher, Create Job Years of Service There is a need for users to be able to retrieve data on how long a person Small 3/15/2007 1 Enhancement Pending
Barbara has been in a particular job. It is especially
important to have this information when reporting on visiting academic
professionals. Also it is useful in comparing how long
an employee has been in a particular job versus how long they have been
a university employee.
Job Begin Date is available for reporting, but the users still need to
calculate how long the employee has been in that job. A
Job Years of Service calculation based on the Job Begin Date would be
very helpful in saving user time and reducing possible
calculation errors. The calculation for Job Years of Service would be
similar to the Employee Years of Service object
calculation in the Employee Administration universe. This new field would
be on the same table as Job Begin and End Date
(T_JOB_HIST). It would also be added to the HR Position and Job
Hierarchy universe.
9 20070514_147 Gerini, Susan Derive data related to FMLA usage A standard report created by HR details all the FMLA usage by employee Large 1/16/2008 4 Enhancement Pending
within a year time frame. This report has required
HR to create many derived variables from the EDW – HR Time and
Attendance universe. With the universe improvement
project splitting the universes into two new universes, the derived variables
13b3cbcb-6c06-450b-a59e-c937e61fe70e.xlsx-HR 2 11/15/2011 @ 9:55 PM
Decision Support- HR CMAC, May 2008
Case Date
# Number Contact Title Description Size Prioritzed Priority Request Type Status
10 20040102_247 Reynen, Access in the DW to Charitable amounts The Foundation would like to create a report that shows actual deductions Large Enhancement Open
William to the Foundation from employees paychecks. We would need to set up a specific Security
Group that only gave them access to the Foundation Charity Deduction
Code.
11 20040331_862 Neild, Nancy Create additional Event tables in HR/Pay The HRIS team is often writing reports in which they need to identify Large Enhancement Open
changes to the data in the above tables. Event tables, which identify
columns that have changed, will allow the team to easily run reports these
reports. Add event tables for T_POSN_HIST,
12 20040730_295 Mitchell, Dale EDW Chg Request: Develop table T_POSN_CLS_HIST, T_PERS_RACE_ETH_HIST.
Create a table (or new field) that holds the staff encumbrances that are Large Enhancement Open
similar to Interim Payroll Tables published in the View Direct reports after each payday.
13 20050705_446 Gerini, Susan T_BNFT_PERS_HIST needs to be The table T_BNFT_PERS_HIST contains the information on dependant Medium 3/13/2008 5 Enhancement Open
updated to populate all fields for all rows beneficiaries. This contains the benefit person name as
well as sex code, birth date and SSN. If the benefit person is a UI
employee or UI student, this data is blank in the table.
14 20060110_819 Gable, Phil Correct deleted rows from There is no identifer on due to a null constraint violation on
The ETL failed on 1/7/06t_bnft_pers_hist to capture the benefit person Small InfoQlty Open
t_job_detl_hist t_job_detl_event where 13 rows were rejected. The actual
problem appears to be due to expired rows that were deleted from
t_job_detl_hist when Banner deleted the source row. ETL
should only delete the last row in the table and leave the other expired
rows as expired.
15 20060118_546 Arend, Nick Correct/Redesign t_bnft_covg_hist table The ETL design for table t_bnft_covg_hist is incorrect. The effect of the Medium 1/16/2007 1 Defect Open
current design is that all rows are marked as current
and there are overlapping effective/expiration dates. The new design may
need to be sourced from a different Banner table as
well.
13b3cbcb-6c06-450b-a59e-c937e61fe70e.xlsx-HR 3 11/15/2011 @ 9:55 PM
Decision Support- HR CMAC, May 2008
Case Date
# Number Contact Title Description Size Prioritzed Priority Request Type Status
16 20060207_171 Pollard, Mark Drop table T_VISA_DOC_HIST and In production there are only 9 rows in T_VISA_DOC_HIST. This table Medium Enhancement Open
associated views. does not contain the data it was expected to contain.
As this table does not have useful data, it can be dropped from the EDW.
There has been limited usage on the table and its
views.
The views affected are v_adm_visa_doc_hist, v_empee_visa_doc_hist_5,
v_recruit_visa_doc_hist.
Universes affected are HR_EDW, HR_HURTT, HR_RPTS, CRNT_EMP,
HR_EMP
17 20060323_491 Arend, Nick Drop Column job_detl_enc_hours from It has been determined that the source for column Medium InfoQlty Open
t_job_detl_hist and v_job_detl_hist_1 t_job_detl_hist.job_detl_enc_hours will not contain useful data. The source
is always null or zero. As such, we will drop this column from
t_job_detl_hist and its view v_job_detl_hist_1.
18 20060425_579 Greenwalt, Adding Service Center categories to the The trend of departments using Service Centers is increasing. A Service Large Enhancement Open
Karen EDW. Center is a central department that performs all HR
tasks for several departments. Users are now creating reports based on
Service Center. Currently the users need to create
the logic to run these reports. This request is to add the logic to the EDW
to assist them in reporting this data by Service
Center.
19 20070828_871 Rascher, Position L/D - Job L/D Mismatch In the annual salary planning process, users confirm that Position Labor Large 1/16/2008 5 Enhancement Open
Barbara Indicator Distribution matches Job Labor Distribution. It would
be helpful for users to have an indicators available to them which would
signify mis matches between the CFOAPAs on job
and position labor distributions.
13b3cbcb-6c06-450b-a59e-c937e61fe70e.xlsx-HR 4 11/15/2011 @ 9:55 PM
Decision Support- HR CMAC, May 2008
Case Date
# Number Contact Title Description Size Prioritzed Priority Request Type Status
20 20071001_407 McEnery, Add DSD to T_PERS_LIC_CERT_HIST UIC is starting to add licences and certification into Banner. The only way Medium Enhancement Open
John & EA Universe to obtain valid licences in effect right now is by using
the EDW Expiration Date. To be consistent with the rest of the HR tables,
it would be helpful to have Data Status Description on
the Table (T_PERS_LIC_CERT_HIST) in the Employee Admin Universe.
21 20071203_467 Pollard, Mark Add CMS Salary and FTE data to the A new table needs to be sourced from Banner into the EDW. Banner has Medium 3/13/2008 1 Enhancement Open
EDW created a new table to handle a problem with Central Management
Services Information . There was a rules calculation difference between
salary information from CMS vs. Banner. This s
22 20071214_527 Pollard, Mark Add Admin Level 2 code and Description User needs the admin level 2 code and the admin level 2 description Small Enhancement Open
to EA and PJH added to the Employee Administration Universe and the
Position Job Hierarchy Universe. The user wants these objects added to
both the employee detail and the job detail of the
two universes. With this added information, it can be determined what
Vice Chancellor the employee reports to.
23 20071220_242 Pollard, Mark Add Job L/D Amounts to Requesting that two columns be added to the T_JOB_LBR_DISTR table Medium Enhancement Open
T_JOB_LBR_DISTR & PJH Universe and two objects be added to the Position Job
Hierarchy Universe.
The first column and field represent the Pay Period CFOAP Contribution
Amount. It is derived by multipying together the
Job Detail Pay Period Salary and the L/D Contribution Percent.
The second column and object represent the Annual CFOAP Contribution
Amount.It is derived by multipying together the
Job Detail Annual Salary and the L/D Contribution Percent.
13b3cbcb-6c06-450b-a59e-c937e61fe70e.xlsx-HR 5 11/15/2011 @ 9:55 PM
Decision Support- HR CMAC, May 2008
Case Date
# Number Contact Title Description Size Prioritzed Priority Request Type Status
24 20080103_881 Pollard, Mark Remove Head Count Objects from HR Per FAC Request, Head Count Class and Object should be removed Medium Defect Open
Universes from the following HR Universes.
Deductions Admin,
Employee Admin,
Payroll and
Position Job Hierarchy
25 20080205_328 Rascher, Add Effective Dates to Leave Tracking In order for users to do historical reporting on employee leave in the HR Medium Enhancement Open
Barbara Universe Leave Tracking universe, it would be helpful to
add the following position and job effective date objects:
Position Effective Date, Position Data Expiration Date, Job Effective Date,
Job Data Expiration Date, Job Detail Effective Date,
Job Detail Data Expiration Date.
These 6 objects were available in the HR Time and Attendance universe
and allowed users to accomplish this type of
reporting. The HR Time and Attendance universe will eventually be
removed due to universe re-design and HR Leave
Tracking/HR Reported Time universes will take its place.
26 20080304_181 Arend, Nick T_emerg_contact dates Normally in the EDW each table has two standard date columns: effective Medium Defect Open
date and posted date. The first of these is the date
the data went into effect in the source (usually activity date) while the
second is the date the data was added to the EDW.
On table t_emerg_contact there is only one column (for posted date) but
the source is activity date. We need to add an
effective date to this table and source both columns appropriately.
It is possible this case could have some user impact for any queries on
posted date. However, such queries should be
reviewed anyways as the results returned today may not be as intended.
13b3cbcb-6c06-450b-a59e-c937e61fe70e.xlsx-HR 6 11/15/2011 @ 9:55 PM
Decision Support- HR CMAC, May 2008
Case Date
# Number Contact Title Description Size Prioritzed Priority Request Type Status
27 20080328_212 AMATI, Add Academic Year and Academic Susan requested that we add Academic Year and Academic Term to the Medium Enhancement Open
SUSAN Term Code to Payroll Reconciliation Time Folder. She would like this information to better able to track
payments to Grad Students who should be paid only for a particular
Academic Year and Term.
28 20080505_172 Overmier, Net Additional NetId Fields Two new objects need to be added to the Employee Administration Medium Enhancement Open
Mark Universe.
The first object will be called Enterprise Id Identifcation Flag. This will
allow user to easily exclude enterprise ids from their
queries.
The second object will be created by being derived. It will be called Home
Domain Flag.
The Home Domain Flag object can be set to 'Y' or 'N'. This is based on the
employee's
home campus matching their domain.
This enhancement will allow the user to create lists of people with a single
NetId and domain.
13b3cbcb-6c06-450b-a59e-c937e61fe70e.xlsx-HR 7 11/15/2011 @ 9:55 PM