Employment Info
Please note that these views can only display what our HR department has entered into the system. Some people's termination record is not entered until their last payroll has been run.
HR_NBRJOBS_MAX_EFF_DATE_V_L [XLS : 15KB] (owner: WEBFOCUS_VIEW_OWNER): This contains lots of job data by employee id: organization, hire date, person ID, birthdates, age, termination dates, etc. Includes active and inactive records, but none that have been terminated (i.e. don't use this to find terminated employes). To pull active employees you would use <where PEBEMPL_EMPL_STATUS = 'A' and NBRJOBS_STATUS = 'A'>. However, that will not pull employees who have their termination date already entered for a future termination date (temps).
HR_NBRJOBS_MAX_EFF_DATE_V [XLS : 15KB] (owner: WEBFOCUS_VIEW_OWNER): This is the same as HR_NBRJOBS_MAX_EFF_DATE_V_L except that it pulls only the greatest effective dated (NBRJOBS_EFFECTIVE_DATE) record on NBRJOBS for each type of job (JOBS_TYPE - EPA, SPA, NPE).
BS_HR_EMPLOYEE_BY_POSN [XLS: 15KB] (owner: WEBFOCUS_VIEW_OWNER): This has most of the same details as HR_NBRJOBS_MAX_EFF_DATE_V_L. Displays only active employees; omitting employees with no active job. This still includes employees whose termination date has already been entered but is in the future. Filters out all one time pay jobs (ex.: employee bonuses) that you wouldn't want for salary computations. There will be multiple records for employees who have multiple jobs (some people's jobs are separated between two departments).
BS_HR_EMPLOYEE_ALL [XLS: 14KB] (owner: WEBFOCUS_VIEW_OWNER): This contains much fewer columns of data only including: ID, last name, first name, middle name, organization, username, and ecls code. Includes all active employees with an active job. Useful for employee type counts and to look up active employees (what kind of employee and where they work). You probably want to filter on employee class: PEBEMPL_ECLS_CODE. This will include employees who already have a termination date entered, but it's in the future.
BS_HR_EMPLOYEE_FT_PERM_ONLY [XLS: 14KB] (owner: WEBFOCUS_VIEW_OWNER): Selects all full-time employees (i.e. people eligible for benefits, 3/4 time is included- useful to student people to determine who can waive their tuition) by ecls_code (which helps determines your benefits and designates between faculty, staff, spa, epa, etc.) from pebempl. This will include employees who already have a termination date entered, but it's in the future. Displayes: ID, Last name, first name, mi, department (using ftvorgn_title), username, home_org, ecls (employee class from pebempl), and pidm. Selects one record per person and only 3/4 to full-time employees.
PZREMTY (PRODRPT) (owner: ASU_ODS) - this is a great table to help you make sense of the ECLS codes
Employee Personal Info
SPRADDR_PR_ASU_V [XLS: 14KB], SPRTELE_PR_ASU_V[XLS: 14KB] (owner: WEBFOCUS_VIEW_OWNER): Pulls the currently active address/telephone for the PR-permanent address/telephone type. Only persons with an active PR address/telephone will show up on this view. HR and Alumni use this address. Everyone in Banner is supposed to have this type of address.
SPRADDR_AB_ASU_V[XLS: 14KB] (owner: WEBFOCUS_VIEW_OWNER): Pulls the currently active address for the AB-ASU Box address type. Only persons with an active AB address will show up on this view. AB - asu box. Only students have this address, but not all students.
SPRADDR_SC_ASU_V[XLS: 14KB], SPRTELE_SC_ASU_V[XLS: 13KB] (owner: WEBFOCUS_VIEW_OWNER): Pulls the currently active address/telephone for the SC-school/campus/employee address/telephone type. Only persons with an active SC address/telephone will show up on this view. Most emloyees have this address, but not all employees. It also might not be a clean address because people could edit it in the past.
Employee Organization Information
- Where does an employee pick up their "check stubs" or W2 forms? Look at the Check Distribution code on PEBEMPL (PEBEMPL_ORGN_CODE_DIST***).
- Which organization does an employee report their time to? Use the Timesheet org on NBRJOBS (NBRJOBS_ORGN_CODE_TS***).
- Which department employs the employee? Use the Home Org on PEBEMPL (PEBEMPL_ORGN_CODE_HOME***).
- What department and building is an employee in? Use the SC campus address on SPRADDR (where spraddr_atyp_code = 'SC') (might not be up to date for all employees).
- What department is responsbile for an employee's pay? Use the FOAP org on NBRJLBD (NBRJLBD_ORGN_CODE) or NHRDIST (NHRDIST_ORGN_CODE) seldom used outside of Finance purposes).
- ***These fields are included in the HR_NBRJOBS_MAX_EFF_DATE_V_L view.