HRBAIRS Data Dictionary - 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'
  OR
    (
    C.EMPL_CLASS=’X’
    AND C.JOB_ROW_CATEGORY_CD = 'B'
    )
  )

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 AND AFFILIATE/NON-EMPLOYEE APPOINTMENTS: Note that intercampus appointments and affiliate/non-employee appointments are considered a LOWER priority in this hierarchy than any other type of appointment: criteria below apply to CAMPUS employee appointments only. Intercampus appointments are identifiable, according to functional users in APO and Payroll, as those with Org_L2 Node = "CENLD" and PERSONNEL_STATUS_CD="E." Affiliate/Non-Employee appointments, for the purposes of this hierarchy, are defined as Contingent Worker appointments (Person Org Relationship Cd = “CWR”) and appointments in Unpaid Staff Job Codes (9900U – Staff Volunteer, 9902U – Staff Emeritus).

I. Campus Appointments

1. Ladder Rank Faculty (Census L3/Job Subfunction Codes 1A1, 1A2, and 1A3)

2. Senior Management Group, Salary Admin Plan (SMG or SLCG)

3. Appointment Type:

   1. X-Faculty:

   1. Appointment Duration (<> "W-Budget End Date")

   1. Tenure (T)
   1. Lowest CTO Code
   1. Security of Appointment (S)
   1. Lowest CTO Code
   1. Indefinite (B or I)
   1. Lowest CTO Code
   1. Other (C or V)
   1. Lowest CTO Code

   1. Lowest CTO Code

   2. 2-Regular/Career

   3. X-Faculty, Appointment Duration "W-Budget End Date"

   4. Y-Academic Non-Tenured Faculty

      1. Appointment Duration:

         1. Tenure (T)

         2. Security of Appointment (S)

         3. Indefinite (B or I)

         4. Continuing or Visa (C or V)

         5. With budget end date (W)

      2. Job Code (lowest outranks highest)

   5. 7-Partial Year Career

4. FTE %

5. Appointment Type:

   1. 3-Limited

   2. 1-Contract

   3. 6-Per Diem

   4. Z-Academic Student

   5. 4-staff Student

   6. 8-TAP Floater

6. Staff Salary Admin Plan

   1. MSP

   2. Bargaining Unit (any two-character SAP is a Barg. Unit SAP)

   3. PSS

   4. BYA

7. Max Pay rate

8. Earliest begin date

9. Lowest Employee Record Number (EMPL_REC_NO)

II. Inter-Campus Appointments (in OrgL2 "CENTR")

III. Affiliate/Non-Employee Appointments (Person Org Relationship Cd = “CWR” or Job Code in (“9900U”, “9902U))