HR by linzhengnd


									                                                                               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

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

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

      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

                                                                            The views affected are v_adm_visa_doc_hist, v_empee_visa_doc_hist_5,

                                                                           Universes affected are HR_EDW, HR_HURTT, HR_RPTS, CRNT_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

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

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

To top