HRBAIRS Data Dictionary - EMPLOYEE_DIM.PRIMARY_APPT_INDEX

An indexable value to be used in choosing a single primary job (appointment) from among a single employee's multiple jobs (appointments) active at any given date (as-of date, or "snapshot" date).

The proper use of this index value requires careful construction of a query's SQL. Sample SQL is given below.

A common use of this index might be to generate headcounts, where each employee - including those holding multiple appointments - is counted only once, in the department and title corresponding to the single appointment consider "primary."

For an employee with one appointment (job), that appointment is consider her/his primary one.

For those with multiple appointments (jobs), a hierarchy of comparisons between fields pertaining to the different jobs is made. The comparisons are performed, in order, until a single appointment (job) "floats" to the top; that appointment is to be considered primary. The hierarchy of comparisons is outlined below.

Sample SQL, to determine the Primary Appointment held on a single, user-specified date (MMM DD YYYY):

SELECT 
FROM HRMS.DBO.EMPLOYEE_DIM_JOB_V E
WHERE E.DW_FEFF_DT <= 'MMM DD YYYY'
AND E.DW_LEFF_DT >= 'MMM DD YYYY'
AND E.PS_EMP_APPT_EFF_SEQ =
  (
  SELECT MAX(B.PS_EMP_APPT_EFF_SEQ)
  FROM HRMS.DBO.EMPLOYEE_DIM_AA_V B
  WHERE E.EMPL_ID = B.EMPL_ID
  AND E.EMPL_RCD_NO = B.EMPL_RCD_NO
  AND E.PS_EMP_APPT_EFF_DT=B.PS_EMP_APPT_EFF_DT
  )
AND E.PRIMARY_APPT_INDEX =
  (
  SELECT MAX(C.PRIMARY_APPT_INDEX)
  FROM HRMS.DBO.EMPLOYEE_DIM_AA_V C
  WHERE E.EMPL_ID = C.EMPL_ID
  AND C.DW_FEFF_DT <= 'MMM DD YYYY'
  AND C.DW_LEFF_DT >= 'MMM DD YYYY'
  AND C.PS_EMP_APPT_EFF_SEQ =
    (
    SELECT MAX(D.PS_EMP_APPT_EFF_SEQ)
    FROM HRMS.DBO.EMPLOYEE_DIM_AA_V D
    WHERE C.EMPL_ID = D.EMPL_ID
    AND C.EMPL_RCD_NO = D.EMPL_RCD_NO
    AND C.PS_EMP_APPT_EFF_DT=D.PS_EMP_APPT_EFF_DT
    )
  AND C.JOB_ROW_CATEGORY_CD = 'P'
  )

Comparisons used to determine Primary Appt:

For general reporting purposes, a single employee's appointments are compared using the following criteria until only one appointment can be considered primary. These criteria were developed by representatives from the Office of Human Resources and the Academic Personnel Office, whose intent was to implement a workable, general-purpose algorithm for deriving primary appointment.

If application of the first criterion to the multiple appointments under consideration fails to yield a single primary appointment, the next criterion is applied, etc., until a single appointment remains - this single appointment is considered primary among the multiple appointments considered.

INTERCAMPUS APPOINTMENTS: Note that intercampus appointments are considered a LOWER priority in this hierarchy than any other type of appointment: criteria below apply to NON-INTERCAMPUS appointemnts only. Intercampus appointments are identifiable, according to functional users in APO and Payroll, as those with Org_Node = "ZMBSU" and PERSONNEL_STATUS_CD="E."

1. Senior Management Group, Salary Admin Plan (SMG)

2. Appointment Type:

   1. X-Faculty:

   2. 2-Regular/Career

   3. X-Faculty, Appointment Duration with budget end date

   4. Y-Academic Non-Tenured Faculty

      1. Appointment Duration:

         1. Indefinite (B or I)

         2. With budget end date (W)

      2. Job Code (lowest outranks highest)

   5. 7-Partial Year Career

3. FTE %

4. Appointment Type:

   1. 3-Limited

   2. 1-Contract

   3. 6-Per Diem

   4. Z-Academic Student

   5. 4-staff Student

   6. 8-TAP Floater

5. Staff SAP

   1. MSP

   2. Salary Admin Plan Bargaining Unit (any two digit SAP is a Barg. Unit SAP)

   3. PSS

   4. BYA

6. Max Pay rate

7. Earliest begin date

8. Lowest Employee Record Number (EMPL_REC_NO)