Logic-based Attributes – University of Colorado Enterprise Directory Registry Update

Affiliation Build Logic

DIR_AFFILIATION table

The affiliation table is rebuilt after all of the daily loads into the registry. The goal is to always reflect a person’s most current affiliations. To accomplish this:

1) read the affiliation table and delete all entries with expiration_date = null or with expiration_date < today.

2) Create new entries in the affiliation table based on the logic below for the description, affiliation and primary affiliation attributes.

DIR_AFFILIATION - description, eduPersonAffiliation

For each AU_SPECIFIC and JOB record meeting the criteria for current affiliation, set AFFILIATED in AU_SPECIFIC and/or JOB to “Y”. If more than one AU_SPECIFIC record per uuid is current, then set AFFILIATED to Y based upon the following precedence: B1, B2, B3.

· If employeeNumber starts with T, description = “Sponsored” and eduPersonAffiliation = “Member”

· Check DIR_AU_SPECIFIC

o If term = control term and if enrollment_status_code = E and if withdraw_code = null or

o if expectedReturn >= current term and if enrollment_status_code not equal X and if withdraw_code = null then

AU value	Description	eduPersonAffiliation
AU ends in 1 (i.e., “B1”)	Student	Student
AU ends in 2 (i.e., “B2”)	Continuing Ed Credit Student	Student
AU ends in 3 (i.e., “B3”)	Continuing Ed Non-Credit Student	Affiliate

(note: enrollment_status = null means the record was rolled forward from prior semester but student has not yet had active current semester transactions. This student should not yet be considered a student affiliate for the current semester)

· Check DIR_JOB

o (If emplmnt_status_code = A, L, P, Q, R, or S AND appoint_end_date is not < today)
or (if emplmnt_status_code = W) then

job_code value	Description	eduPersonAffiliation(s)
starts with 11...	Faculty	Faculty, Employee
starts with 12…	Clinical Faculty	Faculty, Employee
starts with 13…	Research Faculty	Faculty, Employee
starts with 14…	Faculty	Faculty, Employee
starts with 15…	Student Faculty	Student, Faculty, Employee
= 1601 or 5102	Retiree Emeritus	Retiree, Member
= 2145 or 2151 or 2152 or 2153	Affiliate	Affiliate
starts with 21… and is not 2145 or 2151 or 2152 or 2153	Officer/Exempt Professional	Officer/Professional, Staff, Employee
starts with 31…	Medical Resident	Faculty, Employee
>= 3201 and <= 3204	Fellowship/Trainee	Faculty, Employee
= 3205 or 3206	Student Employee	Affiliate
>= 4100 and <= 4499	Student Employee	Employee
= 5101or 5103 or 5104 or 5106	Retiree	Retiree, Member
= 5105 or 5107 or 5108	Affiliate	Affiliate
= 5201 or 5202	Member Spouse	Affiliate
= 6100	Member	Affiliate
Contains alpha characters	Staff	Staff, Employee

DIR_AFFILIATION/campus

For each description value that is built:

· If from AU_SPECIFIC then

o If AU begins with B then campus = “Boulder Campus”

o If AU begins with C then campus = “Colorado Springs Campus”

o If AU begins with D then campus = “Denver Campus”

o If AU begins with H then campus = “Health Sciences Center”

· If from JOB then

o If Dept_ID begins with 1 then campus = “Boulder Campus”

o If Dept_ID begins with 2 then campus = “Health Sciences Center”

o If Dept_ID begins with 3 then campus = “Denver Campus”

o If Dept_ID begins with 4 then campus = “Colorado Springs Campus”

o If Dept_ID begins with 5 then campus = “CU System Office”

o if postalAddress like UCB then campus = “Boulder Campus” (to accommodate UCD Architecture faculty with UCB offices)

DIR_AFFILIATION/orgDN

If DIR_AFFILIATION/campus = “Boulder Campus” then orgDN = colorado.edu

If DIR_AFFILIATION/campus = “Denver Campus” then orgDN = cudenver.edu

If DIR_AFFILIATION/campus = “Colorado Springs Campus” then orgDN = uccs.edu

If DIR_AFFILIATION/campus = “Health Sciences Center” then orgDN = uchsc.edu

If DIR_AFFILIATION/campus = “CU System Office” then orgDN = cusys.edu

DIR_PERSON/primaryAffiliation

Compare DIR_AFFILIATION/description or DIR_AFFILIATION/eduPersonAffiliation values for each uuid. Primary affiliation is set based upon this comparison.

· If uuid has only one Affiliation record, then primaryAffiliation = eduPersonAffiliation.

· If any eduPersonAffiliation = “Officer/Professional” then primaryAffiliation = “Officer/Professional”

· If eduPersonAffiliation = “Retiree” and no eduPersonAffiliation = “Officer/Professional” or “Student” then primaryAffiliation = “Retiree”

· If eduPersonAffiliation = “Faculty” and no eduPersonAffiliation = “Officer/Professional” or “Retiree” and description is not equal “Student Faculty” then primaryAffiliation = “Faculty”

· If eduPersonAffiliation = “Student” and no eduPersonAffiliation = “Officer/Professional” or “Faculty” or “Staff” (or if description = Student Faculty) then primaryAffiliation = “Student”

· If eduPersonAffiliation = “Staff” and no eduPersonAffiliation = “Officer/Professional” or “Faculty” or “Retiree” then primaryAffiliation = “Staff”

· If eduPersonAffiliation = “Member” and any other value other than “Member” or “Affiliate” then primaryAffiliation = that other value. If there is more than one other value, apply the logic described above for comparing values amongst Officer/Professional Exempt, Retiree, Faculty, Student, Staff.

· If eduPersonAffiliation = “Employee” and any other value other than “Member” or “Affiliate” then primaryAffiliation = that other value. If there is more than one other value, apply the logic described above for comparing values amongst Officer/Professional Exempt, Retiree, Faculty, Student, Staff.

· If eduPersonAffiliation = “Member” and “Affiliate” then primaryAffiliation = “Member”

· If eduPersonAffiliation = “Affiliate” and any other value, then primaryAffiliation = that other value. If there is more than one other value, apply the logic described above for comparing values amongst Officer/Professional Exempt, Retiree, Faculty, Student, Staff, Member.

DIR_COMMON_NAME/cn

For each unique value of cn from a source not equal Registry,

· If source = HR then do not change case of incoming values.

· Build a first middle last version of the name:
Strip the characters prior to the comma and move them to the end of the string and drop the comma;
source = Registry, update_date = current date

· Build a first last version of the name:
Strip the characters following the first space following the first string after the comma
Move the characters preceding the comma to the end of the remaining string
Drop the comma;
source = Registry, update_date = current date

· Build a last first version of the name:
Strip the comma, create a new cn, source = Registry, update_date = current date

· Build a first middle last version without spaces in the last name:
Remove spaces from the string(s) prior to the comma, move the resulting string to the end of the entire string and drop the comma; source = Registry, update_date = current date

DIR_PERSON/displayName

· Populate with the value in DIR_COMMON_NAME/cn with the source corresponding to the dir_person/primaryAffiliation, with the most recent update_date..

o if primaryAffiliation = Faculty, Staff, Officer/Professional or Retiree then use source = HR.

o if primaryAffiliation = Student then use source = SIS

o if primaryAffiliation = none of the above then use most current source not equal to Registry.

· Build so that the value is stored as first middle last suffix. To do this,

o strip off the characters preceding the first comma, and place them last in the string (or second to last if there is a suffix – see next bullet).

o If any portion of the name includes the string “Jr” or “Jr.” or “Sr.” or “II” or “III” or “IV” then move that string to be the final string of the displayName (preceded by a space).

o remove all commas.

· If the cn value used to populate displayName is all upper case, revise to upper/lower case (1^st character of each string should be upper case – with the exception of II, III or IV which should remain all upper case) before populating displayName

DIR_PERSON/principalName (dir_person)

Build principalName by concatenating the following attributes and literals:

dir_uid

“@colorado.edu

This build can be performed after the Uniquid load. uid from Uniquid should = identikey.

DIR_PERSON/o

Always populate with: “University of Colorado”

DIR_SEEALSO/seealso

seeAlso will be used to point from one campus’ directory entry to another campus’ directory entry for this person. This will not go live until the directory becomes enterprise-wide.