This article collects the changes made to the database schema as part of the Campus.2323 release pack. Campus provides this information to enable users to update queries and scripts that may be affected by these changes.

Infinite Campus will be removing all NOLOCK (and its equivalent, READUNCOMMITTED) query hints and READ UNCOMMITTED settings from the Infinite Campus code base with Campus.2331.

To mitigate the risk of query result inconsistencies, please be prepared to remove any NOLOCK (and READUNCOMMITTED) query hints from your own custom SQL code when you take Release Pack .2331 (July 2023). Removing them before or after taking this release could cause inconsistencies in your query results.

Data Definition Language Scripts (DDL)

This section lists data structures that were created, altered, or dropped, based on the vocabulary used to define data structures in SQL Server.

New Tables

Case Number	Table	Column	Data Type
SIS-159502	BOIConfiguration	boiConfigurationID	int
		lockStaffNumber	bit
		lockStaffDemographics	bit
		lockStaffEmploymentAssignment	bit
		lockStaffEmployment	bit
		lockStaffHousehold	bit
		lockStaffUI	bit
		syncStaffHousehold	bit
		updatedDate	datetime2(0)
		updatedByID	int
SIS-159502	BOIErrorLog	boiErrorLogID	int
		createdDate	datetime2(0)
		action	varchar(10)
		api	varchar(200)
		errorMessage	varchar(200)
		requestJson	varchar(3000)
SIS-159502	BOINotificationRecipient	boiNotificationRecipientID	int
		boiNotificationType	varchar(30)
		userID	int
SIS-166719	ConstraintTeacherTeam	constraintID	int
		calendarID	int
		structureID	int
		personID	int
		schedGroupID	int
SIS-165494	OneRosterSeqEnrollmentV1P1	seq	int
		dataID	int
		isStudent	in
SIS-165494	OneRosterSeqEnrollmentV1P1Active	seq	int
		dataID	int
		isStudent	bit
SIS-165494	OneRosterSeqEnrollmentV1P1Role	isStudent	bit
		seq	int
		dataID	int
SIS-165494	OneRosterSeqEnrollmentV1P1RoleActive	isStudent	bit
		seq	int
		dataID	int
SIS-165494	OneRosterSeqEnrollmentV1P1School	schoolID	int
		seq	int
		dataID	int
		isStudent	bit
SIS-165494	OneRosterSeqEnrollmentV1P1SchoolActive	schoolID	int
		seq	int
		dataID	int
		isStudent	bit
SIS-165494	OneRosterSeqEnrollmentV1P1SchoolRole	schoolID	int
		isStudent	bit
		seq	int
		dataID	int
SIS-165494	OneRosterSeqEnrollmentV1P1SchoolRoleActive	schoolID	int
		isStudent	bit
		seq	int
		dataID	int
SIS-165494	OneRosterSeqEnrollmentV1P2	seq	int
		dataID	int
		isStudent	bit
SIS-165494	OneRosterSeqEnrollmentV1P2Active	seq	int
		dataID	int
		isStudent	bit
SIS-165494	OneRosterSeqEnrollmentV1P2Role	isStudent	bit
		seq	int
		dataID	int
SIS-165494	OneRosterSeqEnrollmentV1P2RoleActive	isStudent	bit
		seq	int
		dataID	int
SIS-165494	OneRosterSeqEnrollmentV1P2School	schoolID	int
		seq	int
		dataID	int
		isStudent	bit
SIS-165494	OneRosterSeqEnrollmentV1P2SchoolActive	schoolID	int
		seq	int
		dataID	int
		isStudent	bit
SIS-165494	OneRosterSeqEnrollmentV1P2SchoolRole	schoolID	int
		isStudent	bit
		seq	int
		dataID	int
SIS-165494	OneRosterSeqEnrollmentV1P2SchoolRoleActive	schoolID	int
		isStudent	bit
		seq	int
		dataID	int
SIS-159502	StaffMemberAddressSync	staffMemberAddressSyncID	int
		staffMemberHouseholdLocationSyncID	int
		number	varchar(12)
		street	varchar(30)
		tag	varchar(20)
		prefix	varchar(10)
		dir	varchar(10)
		apt	varchar(17)
		city	varchar(24)
		state	varchar(2)
		zip	varchar(10)
		location_code	varchar(40)
		county	varchar(50)
		postOfficeBox	bit
		tract	varchar(10)
		block	varchar(12)
		fullStreetAddress	varchar(100)
SIS-159502	StaffMemberEmploymentSync	staffMemberEmploymentSyncID	int
		staffMemberSyncID	int
		hireDate	datetime2(0)
		terminationDate	datetime2(0)
SIS-159502	StaffMemberHouseholdLocationSync	staffMemberHouseholdLocationSyncID	int
		staffMemberHouseholdSyncID	int
		startDate	datetime2(0)
		endDate	datetime2(0)
		private	bit
		secondary	bit
		mailing	bit
		physical	bit
SIS-159502	StaffMemberHouseholdSync	staffMemberHouseholdSyncID	int
		staffMemberSyncID	int
		name	varchar(50)
		membershipStartDate	datetime2(0)
SIS-159502	StaffMemberPositionSync	staffMemberPositionSyncID	int
		staffMemberSyncID	int
		titleCode	varchar(15)
		titleDescription	varchar(100)
		assignmentCode	varchar(6)
		startDate	datetime2(0)
		endDate	datetime2(0)
		schoolNumber	varchar(7)
SIS-159502	StaffMemberSync	staffMemberSyncID	int
		firstName	varchar(50)
		middleName	varchar(50)
		lastName	varchar(50)
		staffNumber	varchar(15)
		gender	varchar(1)
		ssn	varchar(9)
		birthDate	date
		workEmail	varchar(100)
		personalEmail	varchar(100)
		workPhone	varchar(25)
		cellPhone	varchar(25)
		otherPhone	varchar(25)
SIS-159502	StaffMemberSyncControl	staffMemberSyncControlID	int
		name	varchar(20)
		syncStarted	bit
		syncStartedDate	datetime2(0)
		syncEnded	bit
		syncEndedDate	datetime2(0)
		createdByID	int
		syncCommitted	bit
		syncCommittedDate	datetime2(0)
		syncJobSubmitted	bit
		syncJobSubmittedDate	datetime2(0)
		syncValidated	bit
		syncTransferFromFile	bit
		syncIncludesPositions	bit
SIS-169445	TransportationSyncErrorLog	transportationSyncErrorLogID	int
		timestamp	datetime2(0)
		message	varchar(500)
SIS-169445	TransportationSyncSettings	transportationSyncSettingsID	int
		systemName	varchar(100)
		tenet	varchar(250)
		tylerTechApiKey	varchar(250)
		active	bit

Modified Tables

Added

Case Number	Table	Column	Data Type
SIS-121418	POSApplication	active	bit
SIS-123541	POSAccountAccess	displayComments	varchar(255)
SIS-143933	OnlineMealBenefitHouseholdMember	childFlag	bit
SIS-159502	Household	staffHousehold	bit
SIS-161273	AssessmentScoreImportColumnMapping	applyDateToImport	bit
SIS-163769	FreeReducedSchoolYearPreference	modifiedByID	int
SIS-163769	FreeReducedSchoolYearPreference	modifiedDate	datetime2(0)
SIS-166980	SectionStaffHistory	minutes	int
SIS-167131	Trial	fullySchedStatistic	varchar(22)
SIS-167131	TrialUserViewSettings	collapsedDepartmentsStaffView	varchar(90)
SIS-167131	TrialUserViewSettings	collapsedDepartmentsCourseView	varchar(90)
SIS-167295	HealthCondition	credentialNumber	varchar(20)
SIS-167295	HealthCondition	credentialEnumDate	date
SIS-167295	HealthCondition	providerID	int
SIS-167295	HealthCondition	primaryTaxonomy	varchar(50)
SIS-167295	HealthCondition	credentialSuffix	varchar(10)
SIS-167295	MedicaidServiceProvider	credentialEnumDate	date
SIS-167295	MedicaidServiceProvider	providerNumber	varchar(100)
SIS-167295	MedicaidServiceProvider	credentialType	varchar(15)
SIS-167295	MedicaidServiceProvider	credentialRevocationDate	date
SIS-167295	MedicaidServiceProvider	primaryTaxonomy	varchar(50)
SIS-167295	MedicaidServiceProvider	credentialSuffix	varchar(15)
SIS-167295	MedicaidServiceProvider	type	varchar(20)
SIS-167677	SchoolAttribute	twentyFirstCCLC	bit
SIS-168244	FollowUpData	districtIRNCTE	varchar(15)
SIS-168244	FollowUpData	districtIRNSWD	varchar(15)
SIS-168244	FollowUpData	districtIRNGraduate	varchar(15)
SIS-168464	EarlyInterventionService	participation	varchar(15)
SIS-168641	CareerReadiness	credentialOrDegree	varchar(8000)
SIS-168641	RecordsTransfer	careerReadinessFOP	varchar(100)
SIS-168641	RecordsTransfer	careerReadinessXML	varchar(max)
SIS-168850	PlanEditor	eSignEnabled	tinyint
SIS-168850	PlanState	servingSchoolNumber	varchar(7)

Altered

Case Number	Table	Column	Data Type
SIS-168395	EmploymentAssignment	contractType	varchar(40)
	TranscriptCourse	collegeCode	varchar(15)

Deprecated

Case Number	Table	Column
SIS-159502	FinanceAccount	personID
SIS-159502	FinanceAccount	dim12
SIS-159502	FinanceAccount	dim7
SIS-159502	FinanceAccount	fund
SIS-159502	FinanceAccount	fundDimensionID
SIS-159502	FinanceAccount	dim7subcode
SIS-159502	FinanceAccount	controlAccount
SIS-159502	FinanceAccount	dim12subcode
SIS-159502	FinanceAccount	dim10
SIS-159502	FinanceAccount	balancesheet
SIS-159502	FinanceAccount	dim11
SIS-159502	FinanceAccount	balancesheetsubcode
SIS-159502	FinanceAccount	dim10subcode
SIS-159502	FinanceAccount	dim9
SIS-159502	FinanceAccount	revenue
SIS-159502	FinanceAccount	dim8
SIS-159502	FinanceAccount	dim8subcode
SIS-159502	FinanceAccount	locationsubcode
SIS-159502	FinanceAccount	dim11subcode
SIS-159502	FinanceAccount	location
SIS-159502	FinanceAccount	stateAccountNumber
SIS-159502	FinanceAccount	closingAccount
SIS-159502	FinanceAccount	fundsubcode
SIS-159502	FinanceAccount	expense
SIS-159502	FinanceAccount	dim9subcode
SIS-159502	FinanceAccount	revenuesubcode
SIS-159502	FinanceAccount	programsubcode
SIS-159502	FinanceAccount	program
SIS-159502	FinanceAccount	expensesubcode
SIS-168464	EnrollmentNM	ceis

Data Manipulation Language Scripts (DML)

This section lists scripts to add, modify, query, or remove data from a SQL Server database.

Case Number	State	Description
SIS-121418		Non-enhanced apps to inactive
SIS-158400		Added email success and fail fields to MessageDeliveryStatus
SIS-163769		Added modifiedByID and modifiedDate
SIS-166683		Synced isSAMLAccount Flag with samlConfigurationID Flag
SIS-166719		Transfered Schedule Wizard tool rights to Course Planner tool rights
SIS-167131		Added job and trigger for UserToolOptionCleanupJob
SIS-168204	PA	Added Graduation Seal Dictionary Codes
SIS-168244	OH	Converted districtList to FollowupData
SIS-168591		Hid Deprecated POS 1 Tools
SIS-168641		Converted credentialOrDegree from CustomCareerReadiness
SIS-168672	SD	Added district info
SIS-168686	NM	EdFi attendance change campusObjectType
SIS-169006		Updated Course push timestamps
SIS-169235	MO	Added district
SIS-169304		Added Test Mobile Payments Password
SIS-169495	TX	EdFi converted stateID or staffStateID to edFiID
SIS-169710		Preference for BOI Staff

Technical Information - Release Pack Campus.2323