EB/EJ - Banner Report Issues

EB/EJ Annual Due Date	Opens Nov, due Jan 31
Document status	In Progress We learned most of these in 2020 for MIS Term 209
Document owner	@Jordan Lamb
Tech lead	@ludmilag
Functional User(s)	Keila Reynosa, Linda Reis, Norma Chavez, Khantee See
Report Manager(s)	John Bratsch, Jennifer Vega La Serna

Objective

Detail where reports are deficient and steps to resolve and prioritize issues:

Status	Error/Quality Check	Solution	Presumed Benefits	References/Comments	Proposed User	Resources

Status	Error/Quality Check	Solution	Presumed Benefits	References/Comments	Proposed User	Resources
Pending	EJ08 - Quality Check	Audit FTE on non-instructional employees in particular.	Could create reports off this accurate data. Faculty productivity, FON audit. Could protect us against future audits, or the state using this data for an increased purpose.	FTE seems to be listed at 1 for Primary and Secondary assignments on NBAJOBS. For non-instructional assignments that leads to some employees with >2 Full Time Equivalent loads.	Keila Reynosa, Linda Reis	EJ DED
Pending	EB06 - Null Values	Populate GOAMEDI>General Medical Information>"Primary Disability"	Emergency response, accommodation studies. Not having to manually look this up each cycle.		Keila Reynosa, Linda Reis	EB DED
Pending	EJ08 - WEXP on Student Side		We may be reporting WEXP FTE inaccurately.	How should WEXP FTE be reported? Having the answer to this may have impacts on Scheduling. EB-EJ Error Report Reference has specific examples with PII on Issue ID “EJ08-1”	@Jordan Lamb
Pending	EJ08 - NURS Scheduling		Would decrease manual edits.	Our SQL that takes the table and converts it to the file seems wrong at the least for NURS sections. Seems like the FTE is summing for each section and putting them on each separate session record. EB-EJ Error Report Reference has specific examples with PII on Issue ID “EJ08-2”	@Jordan Lamb , @Joshua Metry
Pending	EB11 Crosswalk	Audit PTRECLS, EEO “6-Unreported”. Currently crosswalks to EB11 “1-Less than 9 months” on PTVEEOG		need to re-engage on this, HR needs to create more position class codes to relate more to contract duration than to hours per day (pt/ft)	Keila Reynosa, Linda Reis	EB DED

Resolved Issues:

Status	Error/Quality Check	Solution	Presumed Benefits	References/Comments	Proposed User	Resources

Status

Error/Quality Check

Solution

Presumed Benefits

References/Comments

Proposed User

Resources

Done

EJ03 - INVALID TOP CODE (Non-Instructional)

Valid ASA codes required on NBAJOBS>CA Pension Tab for reportable employees without a teaching assignment

Mary Schaefer in Payroll graciously populated values for us before we realized that some BAM values are invalid.

Values must comply with DED and BAM. 6000 - 7900

HR Banner Consultant Meeting on 5/24/2022 recommended that Applications script populate ASA codes off the Program code. It does not appear that the Program code of every FOAP lines up with an allowed ASA code:

SELECT      a.NBRJLBD_PIDM,
            F_GETSPRIDENID(a.NBRJLBD_PIDM) AS BANNER_ID,
            a.NBRJLBD_POSN,
            a.NBRJLBD_SUFF,
            a.NBRJLBD_PROG_CODE,
            a.NBRJLBD_EFFECTIVE_DATE,
            NBRJOBS_DESC
FROM        NBRJLBD a
JOIN        NBRJOBS ON NBRJLBD_PIDM = NBRJOBS_PIDM
AND         NBRJLBD_POSN = NBRJOBS_POSN
AND         NBRJLBD_SUFF = NBRJOBS_SUFF
JOIN        PEBEMPL ON NBRJLBD_PIDM = PEBEMPL_PIDM
JOIN        NBBPOSN ON NBBPOSN_POSN = NBRJLBD_POSN
INNER JOIN  (
                SELECT      NBRJLBD_PIDM,
                            MAX(NBRJLBD_EFFECTIVE_DATE) AS NBRJLBD_EFFECTIVE_DATE
                FROM        NBRJLBD
                GROUP BY    NBRJLBD_PIDM
            ) b
            ON      a.NBRJLBD_PIDM = b.NBRJLBD_PIDM
            AND     a.NBRJLBD_EFFECTIVE_DATE = b.NBRJLBD_EFFECTIVE_DATE
WHERE       NBRJOBS_STATUS = 'A'
AND         PEBEMPL_EMPL_STATUS = 'A'
AND         NBRJOBS_SGRP_CODE LIKE '2022%'
AND         (NBRJLBD_PROG_CODE < '6000'
OR          NBRJLBD_PROG_CODE > '8000')
AND         NBBPOSN_STATUS = 'A'
AND         PEBEMPL_EGRP_CODE NOT IN ('O','XB','XR','XS','XT')
GROUP BY    a.NBRJLBD_PIDM, a.NBRJLBD_POSN, a.NBRJLBD_SUFF, a.NBRJLBD_PROG_CODE, a.NBRJLBD_EFFECTIVE_DATE, NBRJOBS_DESC
ORDER BY    NBRJLBD_POSN ASC;

This code returned 749 records on 5/25/2022.

key	summary	type	created	updated	due	assignee	reporter	priority	status	resolution

Loading...

Refresh

Fiscal will change Program on FOAP for non-Faculty. Per 7/20/2022 meeting.

More accurate IPEDs/Datamart. Could run reports off assignment classifications.

Keila Reynosa, Linda Reis

BAM, Argos MIS EJ03 Report

Done

EJ04 - Quality Check

Hours per Day seems to be given a blanket 1 or 8.

HR to assess on a per case basis. May require update to forms and will require getting that information from the hiring manager(s).

Could create reports to assess additional positions.

Could avoid the erroneous public perception (Datamart/IPEDs) that we have hundreds/thousands hours of extra hours per day being worked by our employees.

EMPLOYEE ASSIGNMENT WEEKLY HOURS if not in student, Hours per Day x Appointment Percent x 5(((NBRJOBS_HRS_DAY * NBRJOBS_APPT_PCT)/100) *5)

Assignments that rely on just NBAJOBS records seem to be inaccurate.

EJ04 has an integrity check to make sure it doesn’t exceed an allowed amount.

EB-EJ Error Report Reference has specific examples with PII on Issue ID “EJ04-1”

Keila Reynosa, Linda Reis

EJ DED

Done

EB07, EB08 - EEO6 AND EMPLOYMENT CLASS MUST AGREE

NTRPCLS>NTRPCLS_ESKL_CODE needs an audit for the following employee types: Police Academy instructors, Fire Academy instructors, assistant coaches, counselors, librarians, curriculum coordinator

HR will input Counselors and Librarians to SIAASGN (SIRNIST). Consultant needed to define Workload/FTE questions as they relate to faculty who also teach as part of their normal workload. -per 2/9/22 Meeting
HR to change MISC employees to “P” instead of listing them as adjunct on PEAEMPL. -per 2/9/22 Meeting
Nursing Lab Assistants will also be put in SIAASGN by HR. Utilizing code that Khantee and Keila agree on.

Query that looks for paid faculty that do not have a SIRASGN record w/ Percent of Responsibility > 0 for specified term has been added to the MIS EB/EJ Error Checking report.

Possible benefit of preventing adjunct from going overload.

If it’s decided that they should be non-teaching faculty, then EJ01 will have to be maintained for each of these employees on SIAASGN (SIRNIST). Recommendation is that Academic Services be responsible for that.

Khantee See, Keila Reynosa, Linda Reis

EB DED, EB-EJ Error Report Reference

Done

EB09 - Null Values

HR will add crosswalk field or “X-” invalid values. - per 2/9/22 Meeting

Keila Reynosa, Linda Reis

EB DED

Done

EJ - Assignment not properly terminated.https://sequoias.atlassian.net/browse/AP-105

SELECT *

FROM NBRJOBS a

INNER JOIN NBRBJOB

ON NBRBJOB_PIDM = a.NBRJOBS_PIDM AND NBRBJOB_POSN = a.NBRJOBS_POSN AND a.NBRJOBS_SUFF = NBRBJOB_SUFF

WHERE a.NBRJOBS_STATUS = 'A'

AND a.NBRJOBS_POSN NOT IN

(SELECT b.NBRJOBS_POSN

FROM NBRJOBS b

WHERE a.NBRJOBS_PIDM = b.NBRJOBS_PIDM AND a.NBRJOBS_SUFF = b.NBRJOBS_SUFF AND b.NBRJOBS_STATUS = 'T')

AND NBRBJOB_END_DATE < SYSDATE;

Prevent access/pay issues from possibly arising in the future.

Possibly related to records with NBRJOBS_DATA_ORIGIN like '%.sql'

@Jordan Lamb @ludmilag

Done

We have not been using MIS DED allowed codes for EB14 and there is no crosswalk table.

https://sequoias.atlassian.net/browse/AP-140

We’ll report more than just three unique values on EB14

Ellucian Case # 02413579

All HR

Done

EJ01, EJ02 - ASSIGNMENT TYPE AND LEAVE TYPE MUST AGREE

Keila (HR Technician) and Linda (HR Specialist) decided on Dec 12, 2023 to 1) not report when employees save bank time and 2) report employees who use bank time. From that decision Khantee (Executive Assistant - Academic Services) will:

For faculty saving bank time (OTHR code). HR will delete MIS EJ records that have 999999 TOPS codes.

Add valid TOPS codes on SIAASGN>Non-Instructional screen when a faculty member is using BANK time.

Can create backup for Khantee. Make the offline Excel document more resilient.

EB-EJ Error Report Reference has specific examples with PII on Issue ID “EJ01_1”

Khantee See

EJ DED