EXACT Reporting Views is a framework to enable SQL reporting on an EXACT ISAM database(s).

Install files for EXACT Reporting Views are here: https://hsone.myget.org/feed/corporates/package/nuget/exact-urv

Pre-requisite - SQL Server & SQL Management Studio need to be installed first:

NOTE: SQL Lite/Express is NOT Supported.

How to install EXACT Reporting Views

You can install the EXACT Reporting Views from the command line easily using Chocolatey.

You can install the latest version available, or choose an older version. Once a version is selected, simply click on the Chocolatey option then the Copy to clipboard link:

Paste this command into an Elevated (Run as Administrator) CMD Window, and press enter
-then press A, then enter to accept all and start the EXACT Reporting Views install:

Manual Install of EXACT Reporting Views

Double Click on the ReportingViewsInstaller.exe

Follow the on-screen prompt(s):

Enter the name of the SQLServer the Reporting Views db should be added to:

Enter Install path - Leave as default:

SSRS Config:

Click Next, Install will begin:

EXACT Reporting Views (URV) Installation should then Complete:

You can open and check SSMS to verify that the SOEI_Reporting db was created successfully:

SoE-Admin Tool Installation

To install SOE-ADMIN tool separately, visit this page, You can install the latest version available, or choose an older version: https://hsone.myget.org/feed/corporates/package/nuget/soe-admin

Once a version is selected, simply click on the Chocolatey option then the Copy to clipboard link.

Paste this command into an Elevated (Run as Administrator) CMD Window, and press enter to start the SoE-Admin installation. NOTE: This only copies binaries, does not install anything under Start>Programs.

Upsizing an EXACT Database - Technical Info

There are a fair few technical steps required in order to transform an ISAM db into an SQL db.

SQL Server (2008 or later) needs to be installed, in order to hold the SQL server databases created from the ISAM datasets.

Express/Lite versions are not supported. SQL server must use Windows Authentication. Named Pipes and TCP/IP should be enabled.

The EXACT Reporting Views program needs to be installed.

The deployment package contains the folder ‘EXACT Reporting Views\ReportingViewsInstaller.exe.
This has an installer that is used to set up various programs and stored procedures related to Reporting Views, as well as the Reporting Configuration Database, which is used to manage the reporting/upsized databases in the configuration.

Once the URV tool has been installed, for each ISAM database TWO SQL databases will need to be created:

Upsized Database: Also known as the ‘staging’ database, which is used to hold the data converted to SQL from the EXACT ISAM data files;
Reporting Database: this is the database which follows the reporting database schema, which can be used to generate reports.

IMPORTANT: Enable Pipe and TCP/IP options within SQL

Go To: Start \ SQL Server \ Configuration Tools \ SQL Server Configuration Manager

Make sure 'Protocols for MSSQLSERVERName' Pipe & TCP/IP are enabled.

NB: The SQL Windows Service will need to be restarted:

EXACT Reporting Views Database Creation

After installing Reporting Views, for each ISAM database you need to create TWO corresponding SQL databases.

These databases need to be created using the executable ReportingViewsDeployDb.exe which is found in the EXACT Reporting Views directory for the Reporting Views package.

Launch a command prompt with administrative privileges.
Using the Change Directory 'CD' command switch & Navigate to %REPORTING VIEWS INSTALL FOLDER%.

For each ISAM database, the following will need to be executed to setup the SQL databases:

ReportingViewsDeployDb.exe /ReportingConfigDBServer <SQLSvrConfig> /Action CREATE_RPT_DB /ReportingDbServer <SQLSvr> /ReportingDbName <ReportingDBName> /StagingDbName <UpsizedDBName>

<SQLSvrConfig> = SQL server that holds the Reporting Views configuration database. <SQLSvr> = SQL Server instance that is to hold these reporting/upsized databases

<ReportingDBName> = The reporting database name. <StagingDBName> = The database for the EXACT upsized database.

Example: For the ISAM db called CelebsNZ and a SQL Server named SHABBABOX the command would be:

ReportingViewsDeployDb.exe /ReportingConfigDBServer SHABBABOX /Action CREATE_RPT_DB /ReportingDbServer SHABBABOX /ReportingDbName CelebsNZ_Reporting /StagingDbName CelebsNZ_upsized

After running this command, the newly created reporting/upsized databases will be linked into this reporting configuration, and referenced in the Reporting Configuration database; SOEI_ReportingConfig (assuming you did not choose a different name when installing).

This can be seen by viewing the SOEI_ReportingDatabases table in the SQL configuration database.

When you have created the x2 SQL db's (Upsized and Reporting) you may need to tweak the db SQL settings:

In SQL Server Management Studio, right click on the newly created database and go into the properties, select file and change the initial size of the database to be the same size as the ISAM database being upsized and change the autogrowth to be grown by 10%.

Ensure you do the same for the log settings, for BOTH DATABASES.

ReportingViewsDeployDb.exe - available CMD Line Parameters

Once you have Installed EXACT Reporting Views, configured SQL Server & added Upsized/Reporting databases to transform the ISAM data into, you can then run an Upsize.

Performing an EXACT Reporting Views Upsize

SoeAdmin.exe, which is normally located under the %SOE_ADMIN_TOOL_DIR% is used to invoke the process of creating the Reporting Views database from the EXACT database.

This tool may be run in one of two modes:

Command Line Mode

It is possible to run SoeAdmin for a single database by passing all required parameters on the command line.

See above for SoE-Admin Tool Installation instructions

Before the Upsize will work, in either mode, SQL Databases need to be created first - See Above.

An example of the SoeAdmin command and parameters:

SoeAdmin.exe AutoSQLUpsize <EXACT_Db_NAME> <EXACTSERVERNAME> <Upsized_Db_Name_in_SQL> <SQLSERVERNAME> <Reporting_Db_NAME> "<EXACT_Db_WINDOWS_LOCATION_PATH>"

Working Example:

Command Line Parameter List

SoeAdmin accepts the following command line parameter list:

AutoSQLUpsize	If a user wants to run SQL up-size automatically, the parameter has to be appended right after the string SoeAdmin.exe.
ISAM Database key	The name of the database being upsized
Server machine name	The name of the machine where the ISAM database is located
SQL database name	The SQL database name for the ‘upsized database’, as described above in section , Database Creation
SQL server name	The SQL server instance name
Reporting database	The reporting database name, as described above in section, Database Creation.
Dataset Path	The location of the ISAM dataset. The string should be wrapped in double quotes and end with “\\”. E.g. "C:\Source\Professional\Databases\Dataset1\data\\”
Checkdata [optional]	This is an optional parameter, where once added, the application will check the upsized data against the original data and log errors if there are any. Checks are recommended for the first upsize only since this check will extend the time required for the overall conversion process.
EnableUidCotMapping	This is an optional parameter which should be added to the command line after running the tool ‘Populate duplicate treatment plan mapping’ in EXACT (under Tools > Other Tools > ‘Populate duplicate treatment plan mapping’). This will then allow all charting and associated records to be included in the upsize process even if the uidcot was less than 1000

Configuration File (XML) Mode

In this mode, SoeAdmin is run as follows:

SoeAdmin AutoSQLUpsize <XMLConfigFile> [ <dataset name>] where <XMLConfigFile> is the full pathname of an appropriate XML configuration file, and <dataset name> is an optional parameter which refers to one of the datasets listed in the configuration file.

TIP: A sample XML configuration file is provided in %SOE_ADMIN_TOOL_DIR%, SoeAdminTemplateConfig.xml. This can be edited as appropriate.

For example, the following command could be used with a suitable XML configuration file:

SoeAdmin AutoSQLUpsize SoeAdminCfg.xml

This command will run the process on each of the databases listed in the configuration file, in the sequence they are given in the <databaselist> element.

A similar command:

SoeAdmin AutoSQLUpsize SoeAdminCfg.xml Dataset1

Would only run the process on the single dataset, ‘Dataset1’, even if there are several datasets given in the configuration file.

If present, the third parameter to SoeAdmin in configuration file mode must match the ‘<name>’ element of one of the databases listed in the file.

The ‘<parameters>’ section in the configuration file for each dataset is used to set the various parameter elements needed to run the upsize/reporting process on that dataset.

Note that the <SQLUpsizedDB> and <SQLReportingDB> elements should represent the actual SQL Server database names for the upsized and reporting databases, respectively.

Log Files

After running the SoeAdmin command in either mode, a log file will be created, currently in the same directory as the SoeAdmin command, which indicates the results of running the process for a particular dataset. The log file names are of the form <datasetName>Log.html.

One log file will exist for each upsized/reporting database pair that has been processed by the command (e.g. if SoeAdmin was run using the configuration file mode, with no dataset specified, several upsized/reporting database pairs may be processed, depending on the contents of the XML config file).

Each log file contains information about the SQL upsize and reporting database population process.

Return Codes

In both modes, SoeAdmin returns a status code. A value of 0 indicates that the command ran successfully, and nonzero indicates an error.

In the case of the SoeAdmin command run in ‘Command Line Parameter Mode’, or ‘Configuration File Mode’ where a specific dataset has been specified, the nonzero error status code can be one of the following values:

Return Code	Information
1	Failed without extra information
2	Failed to connect to SQL database
4	Failed to locate the executable to launch the conversion
5	Failed to start the conversion process
6	Error while executing the spSOEI_RunMain stored procedure in the reporting database. Refer to the associated dataset logfile (<datasetName>Log.html) for more information.
7	Failed to start the reporting views stored procedure.
8	SQL Upsize process failed

If the SoeAdmin command is run in ‘Configuration File Mode’, but no specific dataset has been specified (i.e. all datasets given in the XML configuration file have been processed), the nonzero status code will indicate the number of datasets on which the process completed with an error.

EXACT Reporting Views - Database Schema

The following describe the tables used for reporting in the EXACT reporting database.
Each table has a short description of its purpose and a field listing with descriptions.

SOEI_Adjustment

This table contains patient and payor transaction adjustments.

Column	DataType	Description	Foreign Key on Table	Primary key
adjustRecId	varchar(25)	Unique record identifier
adjustedTmsld	varchar(25)	The Invoice/Receipt number that was adjusted
patientId	varchar(25)	Record identifier from Patient file	SOEI_Patient.patientId
payorId	varchar(25)	Record identifier from Payor file	SOEI_Payor.payorId
locationId	varchar(11)	Record identifier from Location file	SOEI_Location.locationId
providerId	varchar(11)	Record identifier from Provider file	SOEI_Provider.providerId
adjustOtherId	int
adjustDate	datetime	The date the adjustment was made
adjustTypeId	varchar(50)	Type code for the adjustment
amount	numeric(18,4)	Amount of the adjustment
enteredBy	Varchar(11)	Logon id of the user creating this adjustment
adjustedItemId	varchar(25)	The id of the invoice or receipt that this adjusts
adjustedItemIdFormatted	varchar(25)	A formatted version of adjustedItemId
depositId	varchar(25)	Deposit that this adjustment belongs to, if any (NULL if not in a deposit).	SOEI_Deposit.depositId
gstAmount	numeric(18,4)	GST amount of the adjustment
referenceNumber	varchar(21)	Transaction number associated with third party payments

SOEI_Allocation

This table contains records of transaction allocations.

Column	DataType	Description	Foreign Key on Table	Primary key
allocId	varchar(25)	Unique record identifier		
allocDate	datetime	Date of allocation
allocSourceId	varchar(25)	Identifier of the source transaction	One of Invoice.invoiceId /receipt.receiptId /adjustment.adjustId
allocDestId	varchar(25)	Identifier of the recieving transaction	One of Invoice.invoiceId /receipt.receiptId /adjustment.adjustId
allocSource	varchar(25)	Verbose description of the source transaction type
allocDest	varchar(25)	Verbose description of the destination transaction type
coverType	int
amount	numeric(18,4)	Allocation amount
allocTypeId	int	Type of the allocation	SOEI_LOOKUP_AllocationType .allocation_typeid

SOEI_Appointment

This table contains patient appointment details.

Column	DataType	Description	Foreign Key on Table	Primary key
appointmentId	varchar(25)	Unique record identifier		
patientId	varchar(25)	Record identifier from Patient file	SOEI_Patient. patientId
locationId	varchar(10)	Record identifier from Location file	SOEI_Location.locationId
providerId	varchar(20)	Record identifier from Provider file	SOEI_Provider.providerId
roomId	varchar(40)	Record identifier from Room file	SOEI_Room.roomId
appointmentCreatedDateTime	datetime	Date and time that the appointment was created
appointmentDateTime	datetime	Date and time of the appointment
arrivalDateTime	datetime	Date/time patient arrived
seatedDateTime	datetime	Date/time patient was seated in the chair
completedDateTime	datetime	Date/time patient treatment completed
length	int	Appointment length
serviceText	varchar(50)	Any text attached to the treatment
category	Int	Treatment category code
status	int	Current status of the appointment
newPatient	tinyint	System flag for new patient. This is set from the ‘1^st Visit’ checkbox on an appointment in the application (1 = 1^st visit, 0 = return visit)
numOfRebooks	Int	Counter for the number of rebookings made
estimatedCost	Numeric(18,4)	Estimated treatment cost for the appointment
cancelReason	Varchar(48)	Reason the appt was cancelled
bookingLocationId	Varchar(11)	Location this appt was booked from	SOEI_Location.locationId
actualWaitingMinutes	int	The difference between the arrival and seated times. If arrival time is before the appt time, and seated time is after, the appt time is used as the starting point
isAuditAppt	bit	TRUE if this is an ‘audit’ appointment (e.g. moved / deleted appts), as indicated by the ‘Show Audit’ checkbox in the SOE application.
appointmentCommonId	Varchar(25)	An identifier which is common to all appointments in an ‘appointment set’, which includes all audit appointments related to a particular appointment.
isMostRecentNonAuditAppt	bit	TRUE if this is the most recently added appointment which is NOT an audit appt (i.e. isAuditAppt = FALSE). Represents the most recent appointment for a given ‘appointment set’.
apptPayorCode	Varchar(11)	The payor relating to this appointment. Defaults to the patient’s payor code, unless overridden	SOEI_Payor.code
isSeatedDateTimeValid	bit	Whether the appointment seatedDateTime value has been set (1) or not (0)
actualWaitingOrEarlyMinutes	int	Similar to field actualWaitingMinutes, but can contain negative values, if the patient was seen early for an appt, whereas actualWaitingMinutes is only where patients had to wait
appointmentDateOnly	datetime	The date only portion of field appointmentDateTime, i.e. without the time element set.
apptBookingMethodId	smallint	Indication of the type of booking method used for this appointment	SOEI_LOOKUP_ApptBookingMethod.apptBookingMethod

SOEI_Appointment_Duties

This table contains details for appointment co-ordinated duties.

Column	DataType	Description	Foreign Key on Table	Primary key
appointmentDutyId	int	Unique record identifier		
appointmentId	varchar(25)	Appointment record ID	SOEI_Appointment.appointmentId
providerId	varchar(11)	Record identifier from Provider file	SOEI_Provider.providerId
dutyType	varchar(25)	Name of duty
startoffset	smallint	Time offset between coordinated appointments
dutyLength	smallint	Length of duty
serviceNumber	smallint	The service number this duty is part of

SOEI_Appointment_Duties_Service_Lengths

This table contains details for appointment co-ordinated duties.

Column	DataType	Description	Foreign Key on Table	Primary key
serviceLengthId	int	Unique record identifier		
appointmentId	varchar(25)	Appointment record ID	SOEI_Appointment.appointmentId
serviceNumber	smallint	The service number within the appointment
serviceLength	smallint	Length of the service

SOEI_BillingSummary

This table contains Categories by which service items are grouped – used by the Invoice Treatments report

Column

DataType

Description

Foreign Key on Table

Primary key

billingSummaryId

varchar(25)

Unique record identifier



description

varchar(50)

Text for invoice type

SOEI_CasemixData

This table contains data from the ‘casemix’ custom screen, which should be the standard casemix screen with title either ‘UDA Weighting Record’ or ‘Special Care Casemix’ in the application.

Column	DataType	Description	Foreign Key on Table	Primary key
casemixDataId	bigint	Unique record identifier		
cotId	varchar(25)	COT this casemix screen relates to	SOEI_Treatment_Plan.cotId
CustomScreenId	varchar(25)	Unique identifier of this particular casemix custom screen entry	SOEI_Treatment_Planstep.CustomScreenId
isLatestEntry	bit	Whether this is the most recent casemix entry for this particular COT
AbilityToCommunicate	varchar(10)	Casemix value – 0 / A / B / C / N, or a number representing the score
AbilityToCooperate	varchar(10)	Casemix value – 0 / A / B / C / N, or a number representing the score
MedicalStatus	varchar(10)	Casemix value – 0 / A / B / C / N, or a number representing the score
OralRiskFactors	varchar(10)	Casemix value – 0 / A / B / C / N, or a number representing the score
AccessToOralCare	varchar(10)	Casemix value – 0 / A / B / C / N, or a number representing the score
LegalEthicalBarriers	varchar(10)	Casemix value – 0 / A / B / C / N, or a number representing the score
Comments	varchar(100)	Text comments from the casemix screen
AbilityToCommunicateScore	tinyint	Integer score value, based on the casemix value – 0 / 2 / 4/ 8
AbilityToCooperateScore	tinyint	Integer score value, based on the casemix value – 0 / 3 / 6 / 12
MedicalStatusScore	tinyint	Integer score value, based on the casemix value – 0 / 3 / 6 / 12
OralRiskFactorsScore	tinyint	Integer score value, based on the casemix value – 0 / 2 / 4/ 8
AccessToOralCareScore	tinyint	Integer score value, based on the casemix value – 0 / 3 / 6 / 12
LegalEthicalBarriersScore	tinyint	Integer score value, based on the casemix value – 0 / 2 / 4/ 8
TotalScore	smallint	Total of all the score columns
MaxScore	smallint	The max value of the score columns
TotalScoreBand	varchar(20)	The band that the total score value falls in – 0, 1-9, 10-19, 20-29 or 30+
MaxScoreBand	varchar(20)	The band that the max score value falls in – 0, 1-9, 10-19, 20-29 or 30+
bAllValuesEqual	bit	Whether all of the casemix values are equal (1) or not (0)

SOEI_DailyActivitySummary

This table contains a summary of treatment items and transactions, arranged by date. Not all columns will be populated for every row. For example, for planned treatment, there is no invoice, and so the invoice / invoice amount columns will not be populated.

Column	DataType	Description	Foreign Key on Table	Primary key
activityItemId	bigint	Unique record identifier		
activityDate	datetime	Date the activity took place
activityTypeId	smallint	Indicates the type of the activity (planned treatment, completed treatment, receipt/adjustment)	SOEI_LOOKUP_DailyActivityType.activityTypeId
patientId	Varchar(25)	Patient identifier
providerId	Varchar(25)	Provider code asociated with the activity	SOEI_Provider.providerCode
payorCode	Varchar(11)	Payor asociated with the activity	SOEI_Payor.payorCode
treatmentPlanNumber	Varchar(25)	A formatted version of the treatment plan (COT) number. If there are multiple site ids, the number will be followed by the site id.
treatmentStepPlannedAmt	Numeric(18,4)	The amount of planned treatment for this treatment step
treatmentStepCompletedAmt	Numeric(18,4)	The amount of completed treatment for this treatment step.
itemServiceId	Varchar(25)	The service code for the activity item (e.g. EXAM).
invoicedPatientAmt	Numeric(18,4)	The patient invoiced amount for this treatment item
invoicedPayorAmt	Numeric(18,4)	The payor invoiced amount for this treatment item
discountedPayorAmt	Numeric(18,4)	The payor discounted amount for this treatment item
receivedAmt	Numeric(18,4)	The amount of a receipt
adjustedAmt	Numeric(18,4)	The amount of an adjustment
transactionRefNumber	Varchar(25)	A formatted text indication of the transaction that this activity is associated with. E.g. an invoice or receipt number. For adjustments, this shows the adjusted invoice/receipt number
transactionLocationId	Varchar(11)	The location code for the activity transaction	SOEI_Location.locationId
transactionEnteredBy	Varchar(11)	The user logged on to SH who entered the transaction.
treatmentPlanId	Varchar(25)	A link to the treatment plan record	SOEI_Treatment_Plan.treatmentPlanId
treatmentPlanstepId	Varchar(25)	A link to the treatment planstep record	SOEI_Treatment_Planstep.treatment_planstepId
invoiceLineId	Varchar(25)	A link to the invoice line item record	SOEI_InvLine.invLineId
invoiceRecId	Varchar(25)	A link to the invoice record	SOEI_Invoice.invoiceRecId
adjustRecId	Varchar(25)	A link to the adjustment record	SOEI_Adjustment.adjustRecId
receiptRecId	Varchar(25)	A link to the receipt record	SOEI_Receipt.receiptRecId
payorId	Varchar(25)	A link to the payor record	SOEI_Payor.payorId
resubmittedDate	datetime	For an activity type of INV_PAT_RESUBMIT, the date the invoice was resubmitted.

SOEI_Deposit

This table contains information about deposit transactions. Deposits are effectively ‘bundles’ of receipts/adjustments, and the receipts/adjustments that are part of a deposit bundle reference the particular deposit transaction via their ‘depositId’ field.

Column	DataType	Description	Foreign Key on Table	Primary key
depositRecId	varchar(25)	Unique record identifier		
depositId	varchar(25)	Deposit number
locationId	varchar(11)	Location code the deposit was made from	SOEI_Location.locationId
depositDate	datetime	Date the deposit bundle was created
amount	Numeric(18,4)	Total amount of the deposit
enteredBy	varchar(11)	User code of user creating the deposit
depositIdFormatted	varchar(25)	Formatted version of depositId

SOEI_DimDate

This table is a ‘date dimension’, which holds all dates from 1/1/1990 up until 10 years after the current date. Various different descriptive attributes are held for a given date.

Column	DataType	Description	Foreign Key on Table	Primary key
DateKey	int	Unique record identifier, an integer in the form YYYYMMDD		
FullDateAlternateKey	date	Equivalent of DateKey in DATE type form
DateFormatted_DDMMYYYY	varchar(20)	Date in DD/MM/YYYY format
DayNumberOfWeek	tinyint	Day number of the week, with 1 = Sun, 7 = Sat
DayNameOfWeek	varchar(20)	Full day name of the week
DayNumberOfMonth	tinyint	Day number of month, 1 - 31
DayNumberOfYear	smallint	Day number of year, 1 - 366
MonthNameFull	varchar(20)	Full month name – January
MonthNameShort	varchar(10)	Short month name – Jan, Feb
MonthNumberOfYear	tinyint	Month number of year, 1 - 12
CalendarQuarter	tinyint	Quarter number, 1 (Jan-Mar) to 4 (Oct-Dec)
CalendarQuarterName	varchar(12)	Q + CalendarQuarter number, Q1
CalendarYear	smallint	Year number - 2014
FinancialQuarter	tinyint	Financial year quarter number 1 (Apr-Jun) to 4 (Jan-Mar)
FinancialQuarterName	varchar(12)	FQ + FinancialQuarter, FQ1
FinancialYearNumberStart	smallint	Year number where the current financial year starts
FinancialYearNumberEnd	smallint	Year number where the current financial year starts
FinancialYearName	varchar(20)	Text describing financial year, e.g. 2013-14
MonthStartDate	date	Date of the start of the month containing this date
MonthEndDate	date	Date of the end of the month containing this date
PrevMonthStartDate	date	Date of the start of the month previous to the month containing this date
PrevMonthEndDate	date	Date of the end of the month previous to the month containing this date
IsEndOfMonth	bit	1 if this date is the end of a month, 0 otherwise
IsStartOfMonth	bit	1 if this date is the start of a month, 0 otherwise
CalendarYearStartDate	date	Date of the start of the calendar year containing this date
CalendarYearEndDate	date	Date of the end of the calendar year containing this date
FinancialYearStartDate	date	Date of the start of the financial year containing this date
FinancialYearEndDate	date	Date of the end of the financial year containing this date
WeekdayIndicator	varchar(20)	Text indicating if this date is a weekend or weekday – ‘Weekend’ or ‘Weekday’
IsWeekday	bit	1 if this date is a weekday, 0 if a weekend

SOEI_DimTimeOfDayMinutes

This table is a ‘time dimension’, which holds all minutes in the day from 0 (midnight 00:00) up to 1439 (23:59)

Column	DataType	Description	Foreign Key on Table	Primary key
minuteId	int	Unique record identifier, an integer in range 0 - 1439
timeOfDay	time	Represents the actual time of day for this minute
numMinutes	int	1 for each minute entry. Can be used to count minutes in a timeslot, using SUM(numMinutes)
timeFormatted_24HHMM	Varchar(5)	Time formatted in HH:MM format, using the 24hr clock, e.g. 13:47
timeFormatted_12HHMM	Varchar(10)	Time formatted in HH:MM format, using the 12hr clock with AM/PM indicator, e.g. 01:34 PM
hourNumberOfDay	int	Hour number of the hour containing this minute, in range 0 - 23
halfHourNumberOfDay	int	Half hour number of the half-hour containing this minute, in range 0 - 46
AM_PM_Indicator	Varchar(2)	Text indicating AM/PM - AM \| PM
morningAfternoonIndicator	Varchar(10)	Text indicating AM/PM – Morning \| Afternoon
hourIntervalStart	time	Time of the start of the hour interval containing this minute
halfHourIntervalStart	time	Time of the start of the half-hour interval containing this minute
quarterHourIntervalStart	time	Time of the start of the quarter-hour interval containing this minute
tenMinuteIntervalStart	time	Time of the start of the 10-minute interval containing this minute
fiveMinuteIntervalStart	time	Time of the start of the 5-minute interval containing this minute

SOEI_DMF_Static

This table contains details of recorded DMF scores.

Column	DataType	Description	Foreign Key on Table	Primary key
dmfId	varchar(25)	Unique record identifier
patientId	varchar(25)	Record identifier from Patient file	SOEI_Patient.patientId
cotId	varchar(25)	ID assigned to Course of Treatment
examDate	Datetime	Date/time of the examination
teethNotPresentDecid	Int	Number of absent deciduous teeth
teethNotPresentPerm	int	Number of absent permanent teeth
decayDecidTeeth	smallint	Number of decayed deciduous teeth
decayPermTeeth	smallint	Number of decayed permanent teeth
missingDecidTeeth	smallint	Number of missing deciduous teeth
missingPermTeeth	smallint	Number of missing permanent teeth
filledDecidTeeth	smallint	Number of deciduous teeth with fillings
filledPermTeeth	smallint	Number of permanent teeth with fillings
surfacesNotPresentDecid	smallint	Surfaces Not Present for deciduous teeth
surfacesNotPresentPerm	smallint	Surfaces Not Present for permenant teeth
decayDecidSurfaces	smallint	Decay on deciduous surfaces
decayPermSurfaces	smallint	Decay on permanent surfaces
missingDecidSurfaces	smallint	Missing decidous Surfaces
missingPermSurfaces	smallint	Missing decidous Surfaces
filledDecidSurfaces	smallint	Deciduous teeth surfaces with fillings
filledPermSurfaces	smallint	Permanent teeth surfaces with fillings
rootDecaySurfaces	smallint	Root decay surfaces
rootFilledSurfaces	smallint	Root filled surfaces
rootDecayTeeth	smallint	Root decay teeth
rootFilledTeeth	smallint	Root filled teeth
fissureSealTeeth	smallint	Teeth with fissure seal
fissureSealSurfaces	smallint	Surfaces with fissure seal

SOEI_Estimate

This table contains information about estimates created for patients, from the ‘Estimates’ tab on the chart screen.

Column	DataType	Description	Foreign Key on Table	Primary key
estimateRecId	varchar(25)	Unique record identifier		
estimateId	varchar(25)	Estimate number
patientId	varchar(25)	The patient this estimate is for	SOEI_Patient.patientId
locationId	varchar(11)	Location code where the estimate was created	SOEI_Location.locationId
providerId	varchar(11)	Provider creating the estimate	SOEI_Provider.providerId
estimateDate	datetime	Date the deposit bundle was created
amount	Numeric(18,4)	Total amount of the estimate
enteredBy	varchar(11)	User code of user creating the deposit

SOEI_ExternalReference

This table contains details of external references linked to by the patient record, for example doctors, GDPs and external sources of referral (advertising, self, etc).

Column	DataType	Description	Foreign Key on Table	Primary key
externalReferenceId	varchar(25)	Unique record identifier		
refTypeId	int	Reference type	SOEI_LOOKUP_ExternalRefType.refTypeId
code	varchar(11)	Code for this reference
title	varchar(6)	Reference’s title (if applicable)
firstName	varchar(36)	Code for this reference
lastName	varchar(36)	Code for this reference
addr1	varchar(51)	Reference address line 1
addr2	varchar(51)	Reference address line 2
addr3	varchar(51)	Reference address line 3
addr4	varchar(51)	Reference address line 4
addr5	varchar(51)	Reference address line 5
postCode	varchar(16)	Reference postcode
phoneNumber	varchar(21)	Reference phone
faxNumber	varchar(21)	Reference fax
emailAddr	varchar(100)	Reference email
specialty	varchar(31)	Reference specialty description

SOEI_FACT_ActivePatientNumbersHistory

This table contains data which can provide a historical count of how many patient records were valid in the practice at a given historical ‘as of’ date, where this date can be any date within the past 2 years from the current date.

Column	DataType	Description	Foreign Key on Table	Primary key
dateAsOf	datetime	The historical as of date to use
patPayorId	varchar(25)	Patient’s payor record (e.g. will indicate if a patient was NHS or private)	SOEI_Payor. payorId
numPtsCreatedToday	datetime	Number of patients of this type created on this as of date
numPtsWithFirstTrtToday	int	Number of patients of this type who had their first completed treatment on this as of date
numPtsWithoutCompletedTrt	int	Number of patients of this type who had no completed treatment as of this date
numAllPtsWithCompletedTrt	int	Number of patients of this type who had completed treatment as of this date
numPtsWithCompletedTrt_0_3m	int	Number of patients of this type who had completed treatment within 3 months of this as of date
numPtsWithCompletedTrt_3_6m	int	Number of patients of this type who had their last completed treatment between 3-6 months from this as of date
numPtsWithCompletedTrt_6_9m	int	Number of patients of this type who had their last completed treatment between 6-9 months from this as of date
numPtsWithCompletedTrt_9_12m	int	Number of patients of this type who had their last completed treatment between 9-12 months from this as of date
numPtsWithCompletedTrt_12_15m	int	Number of patients of this type who had their last completed treatment between 12-15 months from this as of date
numPtsWithCompletedTrt_15_18m	int	Number of patients of this type who had their last completed treatment between 15-18 months from this as of date
numPtsWithCompletedTrt_18_21m	int	Number of patients of this type who had their last completed treatment between 18-21 months from this as of date
numPtsWithCompletedTrt_21_24m	int	Number of patients of this type who had their last completed treatment between 21-24 months from this as of date
numPtsWithCompletedTrt_over24m	int	Number of patients of this type who had their last completed treatment over 24 months from this as of date

SOEI_FACT_AppointAnalysisProvider

This table contains data which can provide a similar breakdown of information as is provided in the application’s Appointment Analysis report. Contains a breakdown of appointment data.

Column	DataType	Description	Foreign Key on Table	Primary key
appointAnalysisProviderId	bigint	Unique record identifier		
providerId	varchar(20)	Provider for this appointment data	SOEI_Provider. providerId
locationId	varchar(20)	Blank location code	SOEI_Location. locationId
apptDate	datetime	Date on which the appointments occurred
days	int	Number of days – will be 1 for a specific apptDate, but can be used when summing by provider
ptsSeen	int	The number of patients seen on this date by this provider
hrsAvail	Numeric(13,5)	Number of hours available on this date
hrsAvailFormatted	varchar(5)	HH:MM formatted string of available hours
unbookedHrs	Numeric(10,2)	Number of hours unbooked on this date
unbookedHrsFormatted	varchar(5)	HH:MM formatted string of unbooked hours
extraHours	Numeric(10,2)	Number of hours worked outside normal hours on this date
extraHrsFormatted	varchar(5)	HH:MM formatted string of extra hours
hrsBooked	Numeric(10,2)	Number of hours from booked appts on this date
hrsBookedFormatted	varchar(5)	HH:MM formatted string of booked hours
ptsFailed	int	Number of appts that failed on this date
hrsFailed	Numeric(10,2)	Number of hours due to failed appts
hrsFailedFormatted	varchar(5)	HH:MM formatted string of failed hours
valFailed	Numeric(10,2)	Estimated value of failed appts, from planned treatment estimated cost
ptsCancelled	int	Number of appts that cancelled on this date
hrsCancelled	Numeric(10,2)	Number of hours due to cancelled appts
hrsCancelledFormatted	varchar(5)	HH:MM formatted string of cancelled hours
valCancelled	Numeric(10,2)	Estimated value of cancelled appts, from planned treatment estimated cost
ptsNew	int	Number of appts that were ticked as ‘new patients’ on this date
valNew	Numeric(10,2)	Estimated value of new patient appts, from planned treatment estimated cost
estValGross	Numeric(10,2)	Estimated value of all appts, from planned treatment estimated cost
totalGross	Numeric(10,2)	Actual value of invoices created for this date
minsWaited	Numeric(10,2)	Average number of minutes waited for appointments on this date
minsWaitedFormatted	Char(5)	HH:MM formatted string of average minutes waited

SOEI_FACT_AppointAnalysisProviderDayBreakdown

This table contains data which shows the complete breakdown of a provider’s day, in a series of non-overlapping periods of time. These time periods can be blocked out time (e.g. start/end of day), appointment time, or non-booked time (whitespace). Appointment timeslots can be for booked/completed appointments or cancelled/failed appointments.

Available time or other timeslot categories can be based either on time that is not associated with blocked out time of type ‘Block’ (columns that use the suffix ‘NoBlock’), or time that is not associated with blocked out time of type ‘Block’ OR ‘AdHoc’ (columns with suffix ‘NoBlockAdHoc’). The choice of which to use depends on whether ‘AdHoc’ blocks are considered as ‘available’ or ‘unavailable’ time. The default in Exact (e.g. in the Exact appointment analysis report) is to view blocked out time of ‘Block’ or ‘AdHoc’ as unavailable.

When viewed in order for a particular provider and apptDate, these timeslots:

Should have timeslot 1 begin at minuteId 0, and the last timeslot end at minuteId 1439;
The minuteEndId of each timeslot will be one minute befor the minuteStartId of the next timeslot;
Have a timeslot that accounts for every minute of the day, i.e. there are no ‘gaps’

Column	DataType	Description	Foreign Key on Table	Pkey
apptAnalysisProviderDayBreakdownId	bigint	Unique record identifier		
providerId	varchar(20)	Provider for this appointment data	SOEI_Provider. providerId
apptDate	datetime	Date on which the appointments occurred	SOEI_DimDate. FullDateAlternateKey
minuteStartId	int	Minute for the start of this timeslot	SOEI_DimTimeOfDayMinute. minuteId
minuteEndId	int	Minute for the end of this timeslot	SOEI_DimTimeOfDayMinute. minuteId
providerTimesId	Varchar(25)	If this timeslot is for a period of blocked out time, a link to this block, NULL otherwise	SOEI_Provider_Times. providerTimesId
appointmentId	Varchar(25)	If this timeslot is for an appointment time, a link to this appointment, NULL otherwise	SOEI_LOOKUP_ApptStatus. apptStatusId
apptStatusId	int	An indication of the status of this appointment, NULL if no appointment	SOEI_LOOKUP_ApptStatus. apptStatusId
patientId	Varchar(25)	If this timeslot is for an appointment time, a link to the patient for this appointment, NULL otherwise	SOEI_Patient. patientId
rowId	int	Number of this timeslot for this provider & apptDate
numPtsSeen	int	Number of appointment timeslots which were for completed appointments
numMinsInTimeslot	int	How many minutes were in this timeslot
numHrsInTimeslot	Numeric(13,5)	How many hours were in this timeslot
numMinsBlockTypeBlock	int	Number of minutes in this timeslot if this was blocked time of type ‘Block’, 0 otherwise
numHrsBlockTypeBlock	Numeric(13,5)	Number of hours in this timeslot if this was blocked time of type ‘Block’, 0 otherwise
numMinsBlockTypeAdHoc	int	Number of minutes in this timeslot if this was blocked time of type ‘AdHoc, 0 otherwise
numHrsBlockTypeAdHoc	Numeric(13,5)	Number of hours in this timeslot if this was blocked time of type ‘AdHoc, 0 otherwise
numMinsBookedTotal	int	Number of minutes in this timeslot if this was booked/completed appointment time, 0 otherwise
numHrsBookedTotal	Numeric(13,5)	Number of hours in this timeslot if this was booked/completed appointment time, 0 otherwise
numMinsCancelledTotal	int	Number of minutes in this timeslot if this was cancelled appointment time, 0 otherwise
numHrsCancelledTotal	Numeric(13,5)	Number of hours in this timeslot if this was cancelled appointment time, 0 otherwise
numMinsFailedTotal	int	Number of minutes in this timeslot if this was failed appointment time, 0 otherwise
numHrsFailedTotal	Numeric(13,5)	Number of hours in this timeslot if this was failed appointment time, 0 otherwise
numMinsAvailNoBlock	int	Number of minutes in this timeslot if this was NOT associated with blocked out time of type ‘Block’, 0 otherwise
numHrsAvailNoBlock	Numeric(13,5)	Number of hours in this timeslot if this was NOT associated with blocked out time of type ‘Block’, 0 otherwise
numMinsBookedNoBlock	int	Number of minutes in this timeslot if this was booked/completed appointment time and NOT associated with blocked out time of type ‘Block’, 0 otherwise
numHrsBookedNoBlock	Numeric(13,5)	Number of hours in this timeslot if this was booked/completed appointment time and NOT associated with blocked out time of type ‘Block’, 0 otherwise
numMinsUnbookedNoBlock	int	Number of minutes in this timeslot if this was never an appointment time and NOT associated with blocked out time of type ‘Block’, 0 otherwise
numHrsUnbookedNoBlock	Numeric(13,5)	Number of hours in this timeslot if this was never an appointment time and NOT associated with blocked out time of type ‘Block’, 0 otherwise
numMinsCancelledNoBlock	int	Number of minutes in this timeslot if this was cancelled appointment time and NOT associated with blocked out time of type ‘Block’, 0 otherwise
numHrsCancelledNoBlock	Numeric(13,5)	Number of hours in this timeslot if this was cancelled appointment time and NOT associated with blocked out time of type ‘Block’, 0 otherwise
numMinsFailedNoBlock	int	Number of minutes in this timeslot if this was failed appointment time and NOT associated with blocked out time of type ‘Block’, 0 otherwise
numHrsFailedNoBlock	Numeric(13,5)	Number of hours in this timeslot if this was failed appointment time and NOT associated with blocked out time of type ‘Block’, 0 otherwise
numMinsAvailNoBlockAdHoc	int	Number of minutes in this timeslot if this was NOT associated with blocked out time of type ‘Block’ or ‘AdHoc’, 0 otherwise
numHrsAvailNoBlockAdHoc	Numeric(13,5)	Number of hours in this timeslot if this was NOT associated with blocked out time of type ‘Block’ or ‘AdHoc’, 0 otherwise
numMinsBookedNoBlockAdHoc	int	Number of minutes in this timeslot if this was booked/completed appointment time and NOT associated with blocked out time of type ‘Block’ or ‘AdHoc’, 0 otherwise
numHrsBookedNoBlockAdHoc	Numeric(13,5)	Number of hours in this timeslot if this was booked/completed appointment time and NOT associated with blocked out time of type ‘Block’ or ‘AdHoc’, 0 otherwise
numMinsUnbookedNoBlockAdHoc	int	Number of minutes in this timeslot if this was never an appointment time and NOT associated with blocked out time of type ‘Block’ or ‘AdHoc’, 0 otherwise
numHrsUnbookedNoBlockAdHoc	Numeric(13,5)	Number of hours in this timeslot if this was never an appointment time and NOT associated with blocked out time of type ‘Block’ or ‘AdHoc’, 0 otherwise
numMinsCancelledNoBlockAdHoc	int	Number of minutes in this timeslot if this was cancelled appointment time and NOT associated with blocked out time of type ‘Block’ or ‘AdHoc’, 0 otherwise
numHrsCancelledNoBlockAdHoc	Numeric(13,5)	Number of hours in this timeslot if this was cancelled appointment time and NOT associated with blocked out time of type ‘Block’ or ‘AdHoc’, 0 otherwise
numMinsFailedNoBlockAdHoc	int	Number of minutes in this timeslot if this was failed appointment time and NOT associated with blocked out time of type ‘Block’ or ‘AdHoc’, 0 otherwise
numHrsFailedNoBlockAdHoc	Numeric(13,5)	Number of hours in this timeslot if this was failed appointment time and NOT associated with blocked out time of type ‘Block’ or ‘AdHoc’, 0 otherwise

SOEI_FACT_AppointAnalysisProviderHrsWorked

This table is similar to that in SOEI_FACT_AppointAnalysisProvider, but only shows data for booked/cancelled/failed appointments, and also allows this data to be broken down by patient payor or any other SOEI_Patient attributes.

Column	DataType	Description	Foreign Key on Table	Primary key
appointAnalysisProvider HrsWorkedId	bigint	Unique record identifier		
patientId	varchar(25)	Patient for this appointment data	SOEI_Patient. patientId
patPayorId	varchar(25)	Patient’s payor	SOEI_Payor. payorId
providerId	varchar(20)	Provider for this appointment data	SOEI_Provider. providerId
locationId	varchar(20)	Blank location code	SOEI_Location. locationId
apptDate	datetime	Date on which the appointments occurred
ptsSeen	int	The number of patients seen on this date by this provider
hrsBooked	Numeric(10,2)	Number of hours from booked appts on this date
hrsBookedFormatted	varchar(5)	HH:MM formatted string of booked hours
ptsFailed	int	Number of appts that failed on this date
hrsFailed	Numeric(10,2)	Number of hours due to failed appts
hrsFailedFormatted	varchar(5)	HH:MM formatted string of failed hours
valFailed	Numeric(10,2)	Estimated value of failed appts, from planned treatment estimated cost
ptsCancelled	int	Number of appts that cancelled on this date
hrsCancelled	Numeric(10,2)	Number of hours due to cancelled appts
hrsCancelledFormatted	varchar(5)	HH:MM formatted string of cancelled hours
valCancelled	Numeric(10,2)	Estimated value of cancelled appts, from planned treatment estimated cost
ptsNew	int	Number of appts that were ticked as ‘new patients’ on this date
valNew	Numeric(10,2)	Estimated value of new patient appts, from planned treatment estimated cost
estValGross	Numeric(10,2)	Estimated value of all appts, from planned treatment estimated cost
totalGross	Numeric(10,2)	Actual value of invoices created for this date

SOEI_FACT_InvoicedTreatments

This table contains data which can provide a breakdown of treatment service items that have been invoiced for.

Column	DataType	Description	Foreign Key on Table	Primary key
itemDate	datetime	Date on which the invoice for the item was generated		
serviceId	varchar(25)	The service item which has been invoiced	SOEI_Service.serviceId
patPayorId	varchar(25)	The patient payor code for this invoice item.	SOEI_Payor.payorId
invPayorId	varchar(25)	The invoiced payor code for this invoice item	SOEI_Payor.payorId
providerId	varchar(20)	The provider to which this item line relates	SOEI_Provider.providerId
patientId	varchar(25)	The patient to which this item line relates	SOEI_Patient.patientId
locationId	varchar(11)	The location to which this item line relates	SOEI_Location. locationId
serviceNumItems	int	The quantity of these service items invoiced for
serviceTotalFeeAmount	varchar(20)	The total fee for these service items
serviceCostAmount	varchar(20)	The total cost to provide these service items (relies on this being configured in Exact)
timeTakenMinutes	int	Time in minutes recorded from the chart screen in Exact when this treatment was being carried out
timeTakenHours	numeric(18,2)	timeTakenMinutes expressed as hours

SOEI_FACT_PatientRecallBreakdown

This table contains a summary of patient recall counts, which enables these to be broken down by patient, provider or date attributes. Note that this table contains data for different types of provider breakdown – so a value of recallBreakdownProviderTypeId must be used when querying this table.

Column	DataType	Description	Foreign Key on Table	Primary key
dateOfRecall	datetime	Date the recall is due
recallTypeId	tinyint	Indication of the recall type (Dentist, Hygienist)	SOEI_LOOKUP_ PatientRecallType .recallTypeId
recallBreakdownProviderTypeId	tinyint	Indication of the recall breakdown type – by last seen or patient provider	SOEI_LOOKUP_ RecallBreakdownProviderType .recallTypeId
providerId	varchar(11)	The provider associated with this recall, based on the value in recallBreakdownProviderTypeId	SOEI_Provider.providerId
patientId	varchar(25)	The patient’s provider	SOEI_Patient.patientId
numTotal	int	The total number of recalls for this date, patient and provider combination
numInactive	int	The total number of inactive recalls for this date, patient and provider combination
numDoNotRecall	int	The total number of ‘do not recall’ recalls for this date, patient and provider combination
numCompleted	int	The total number of ‘completed’ recalls for this date, patient and provider combination
numMovedOn	int	The total number of ‘moveds on’ recalls for this date, patient and provider combination
numBooked	int	The total number of ‘booked’ recalls for this date, patient and provider combination
numFuture	int	The total number of ‘future’ recalls for this date, patient and provider combination
numFailed	int	The total number of ‘failed’ recalls for this date, patient and provider combination
numTotalComplete	int	The total number of recalls that are successful. Defined as the total of numCompleted + numBooked
numTotalIncomplete	int	The total number of recalls that are not successful. Defined as the total of numFuture + numFailed
numPhoneContacts	smallint	Of all the contact messages linked with these recalls, how many were phone calls
numEmailContacts	smallint	Number of email contacts linked with this recall
numSMSContacts	smallint	Number of SMS/Text contacts linked with these recalls
numLetterContacts	smallint	Number of letter contacts linked with these recalls
numEasypostLetterContacts	smallint	Number of Easypost letter contacts linked with these recalls
numEasypostCardContacts	smallint	Number of Easypost card contacts linked with these recalls
numTotalContacts	smallint	Number of email contacts linked with these recalls

SOEI_FACT_PeriodicPatientActivitySummary

This table contains data on the number of patients being processed for a given period, in the context of a new patient record being created, that patient receiving their first and subsequent treatment, and possibly then becoming ‘lapsed’, i.e. not having completed treatment for some time. Several of the fields in this table refer to the ‘Lapsed Period’ (LP). This refers to the amount of time since a patient’s last completed treatment after which they are then considered as ‘lapsed’. This period is currently set as 24 months.

Column	DataType	Description	Foreign Key on Table	Primary key
periodType	varchar(10)	Indicator of type of period – MONTH, WEEK
datePrevPeriodEnd	date	The date of the end of the previous period of this type
datePeriodStart	date	Date of the start of this period
datePeriodEnd	date	Date of the end of this period
patPayorId	varchar(25)	The patient’s payor (NHS, Denplan, etc)	SOEI_Payor. payorId
patDentistId	varchar(11)	The patient’s dentist	SOEI_Provider.providerId
patHygienistId	varchar(11)	The patient’s hygienist	SOEI_Provider.providerId
numStartPtsWithoutTrt	int	No. of patients as of the end of the previous period without any completed treatment – ‘prospects’
numStartPtsWithTrt_lessLP	int	No. of patients as of the end of the previous period with completed treatment within the lapsed period (LP) – ‘active’
numStartPtsWithTrt_overLP	int	No. of patients as of the end of the previous period with completed treatment over the lapsed period – ‘lapsed’
numStartPtsInactive	int	No. of patients as of the end of the previous period who had been marked as inactive
numPtsCreatedInPeriod	int	No. of patient records created within the period (new prospects)
numPtsWithFirstTrtInPeriod	int	No. of patients having their first completed treatment within the period (activated)
numPtsWithTrtInPeriod_prevTrtLessLP	int	No. of patients having some completed treatment in the period, who as of the end of the previous period had treatment within the LP (returning active)
numPtsWithTrtInPeriod_prevTrtOverLP	int	No. of patients having some completed treatment in the period, who as of the end of the previous period had treatment over the LP (returning lapsed)
numPtsWithoutTrtInPeriod_prevTrtNowOverLP	int	No. of patients who don’t have completed treatment in the period, and whose last completed treatment is over the LP by the period end date (lapsed in period)
numPtsMadeInactiveInPeriod_endWithoutTrt	int	No. of patients made inactive within the period, who as of the end date did not have any completed treatment (inactivated prospects)
numPtsMadeInactiveInPeriod_endWithTrt	int	No. of patients made inactive within the period, who as of the end date had some completed treatment (inactivated active)
numEndPtsWithoutTrt	int	No. of patients as of the end date who did not have completed treatment
numEndPtsWithTrt_lessLP	int	No. of patients as of the end date who had completed treatment within the LP
numEndPtsWithTrt_overLP	int	No. of patients as of the end date whose last completed treatment was over the LP
numEndPtsInactive	int	No. of patients who as of the end date had been marked as inactive
diffNumEndPtsWithoutTrt	int	Change over the period in patients without treatment (prospects)
diffNumEndPtsWithTrt_lessLP	int	Change over the period in patients whose last treatment was within the LP (active)
diffNumEndPtsWithTrt_overLP	int	Change over the period in patients whose last treatment was over the LP (lapsed)
diffNumEndPtsInactive	int	Change over the period in patients who were marked as inactive
numPtsCreatedInPastLP	int	No. of patients created within the period of LP months back from the end date (a ‘to date’ count)
numPtsWithFirstTrtInPastLP	int	No. of patients with their first treatment within the period of LP months back from the end date
numPtsWithTrtInPastLP_prevTrtLessLP	int	No. of patients with some completed treatment in the past LP months period from end date, who as of the start of this period had some treatment within the previous LP months – returning active
numPtsWithTrtInPastLP_prevTrtOverLP	int	No. of patients with some completed treatment in the past LP months period, who as of the start of this period had no treatment within the previous LP months – returning lapsed
numPtsWithoutTrtInPastLP_prevTrtNowOverLP	int	No. of patients with no completed treatment in the past LP months period, who as of the end date had their last treatment over LP months ago – lapsed in period
numPtsMadeInactiveInPastLP	int	No. of patients made inactive in the period of LP months back from the end date

SOEI_FACT_PeriodicPatientActivityDetail

This table contains the detailed information for the counts held in the related table SOEI_FACT_PeriodicPatientActivitySummary, for patients with activity in the defined period.

Column	DataType	Description	Foreign Key on Table	Primary key
periodicPatientActivityDetailId	int	Unique record id
periodType	varchar(10)	Indicator of type of period – MONTH, WEEK
datePrevPeriodEnd	date	The date of the end of the previous period of this type
datePeriodStart	date	Date of the start of this period
datePeriodEnd	date	Date of the end of this period
patPayorId	varchar(25)	The patient’s payor (NHS, Denplan, etc)	SOEI_Payor. payorId
patientId	varchar(25)	Id of this patinet	SOEI_Patient.patientId
numPtsCreatedInPeriod	int	1 if this patient record was created in this period, 0 otherwise
numPtsWithFirstTrtInPeriod	int	1 if this patient had their first treatment in this period, 0 otherwise
numPtsWithTrtInPeriod_prevTrtLessLP	int	1 if this patient had treatment in this period AND as of the end of the previous period had completed treatment within the LP, 0 otherwise
numPtsWithTrtInPeriod_prevTrtOverLP	int	1 if this patient had treatment in this period AND as of the end of the previous period had no completed treatment within the LP, 0 otherwise
numPtsWithoutTrtInPeriod_prevTrtNowOverLP	int	1 if this patient had no treatment in this period AND as of the end of the period had their last completed treatment over the LP, 0 otherwise
numPtsMadeInctiveInPeriod_endWithoutTrt	int	1 if this patient record was made inactive in this period AND as of the end date had no completed treatment, 0 otherwise
numPtsMadeInctiveInPeriod_endWithTrt	int	1 if this patient record was made inactive in this period AND as of the end date had some completed treatment, 0 otherwise

SOEI_FACT_ReceiptAnalysis

This table contains data which can provide a similar breakdown of information as is provided in the application’s Receipt Analysis report. Contains a breakdown of how the amount of the receipt was allocated.

Column	DataType	Description	Foreign Key on Table	Primary key
receiptAnalysisItemId	bigint	Unique record identifier		
rowTypeIndicator	varchar(15)	Indicates what type of row this data is for – one of ALLOCATED, UNALLOCATED, ADJUSTMENT
receiptRecId	varchar(25)	The receipt to which this item line relates	SOEI_Receipt.receiptRecId
receiptId	varchar(25)	The receipt to which this item line relates	SOEI_Receipt.receiptId
transactionDate	datetime	Date of the receipt or adjustment date for adjustments
providerId	varchar(20)	Provider for this allocation line	SOEI_Provider.providerId
locationId	varchar(20)	Location of the receipt or adjustment	SOEI_Location.locationId
patientId	varchar(25)	Patient who paid the receipt	SOEI_Patient.patientId
payorId	varchar(25)	Payor who paid the receipt	SOEI_Payor.payorId
paymentMethod	varchar(25)	Way the receipt was paid
allocationPayorCode	varchar(11)	The payor relating to this allocation	SOEI_Payor.code
allocationCategory	varchar(80)	Description of the allocation type (NHS/Private/Stock, etc)
allocationAmount	numeric(18,4)	Amount for this allocation line

SOEI_Fee_Schedule

This table contains Fee Schedule information.

Column	DataType	Description	Foreign Key on Table	Primary key
scheduleId	varchar(25)	Unique record identifier		
name	varchar(50)	Name of fee schedule
hasPreviousFees	bit	Whether the hasPreviousFees for this schedule is something other than zero (1) or not (0)
pointTariff	numeric(18,4)	Point tariff
effectiveDate	datetime	Date that this fee schedule becomes active

SOEI_Holiday

This table contains Holiday date and description information

Column	DataType	Description	Foreign Key on Table	Primary key
HolidayId	varchar(25)	Unique record identifier		
dateHoliday	datetime	Date of Holiday
description	varchar(51)	Description of Holiday

SOEI_InvLine

This table contains the Invoice Line breakdown

Column	DataType	Description	Foreign Key on Table	Primary key
invLineId	varchar(25)	Unique record identifier		
invoiceId	varchar(25)	Record identifier from the invoice file	SOEI_Invoice.invoiceRecId
invLineNumber	varchar(25)	Invoice line number
serviceId	varchar(25)	Service charged for	SOEI_Service.serviceId
adaCode	varchar(25)
itemCount	int	Number of items
amount	numeric(18,4)	Invoice amount
patientAmount	numeric(18,4)	Patient amount
gstAmount	numeric(18,4)	GST/VAT amount
patientGstAmount	numeric(18,4)	Patient GST/VAT amount
serviceCost	numeric(18,4)	Service cost amount
passCostToPatient	smallint
origAmount	numeric(18,4)	Original amount of the item on the invoice line
treatmentPlanstepId	varchar(25)	Link to the treatment planstep item	SOEI_Treatment_Planstep.treatment_planstepId

SOEI_Invoice

This table contains Invoice headers

Column	DataType	Description	Foreign Key on Table	Primary key
invoiceRecId	varchar(25)	Unique record identifier		
invoiceId	varchar(25)	Actual invoice code
patientId	varchar(25)	Record identifier from Patient file	SOEI_Patient.patientId
payorId	varchar(25)	Record identifier from Payor file	SOEI_Payor.payorId
locationId	varchar(11)	Location ID used for replication	SOEI_Location. locationId
providerId	varchar(11)	Record identifier from Provider file	SOEI_Provider. providerId
invoiceDate	datetime	Date invoice was issued
invoiceOtherId	int	Indication of the type of invoice
resubmitted	bit	Invoice reissued
amount	numeric(18,4)	Invoice amount
cotId	varchar(25)	The treatment plan this invoice is associated with.	SOEI_Treatment_Plan. cotId
enteredBy	Varchar(11)	The SH login of the user who created this invoice.
invoiceIdFormatted	varchar(25)	A formatted version of the invoice number (e.g. 1093). May also include the site id.
resubmittedDate	datetime	If the invoice has been resubmitted, the date this resubmission occurred.
outstandingAmount	numeric(18,4)	The invoice amount, less any amounts allocated from it, plus any amounts allocated to it. Should correspond with the ‘Open Amt’ in the application.
bHasOutstandingAmount	bit	Whether the outstandingAmount for this invoice is something other than zero (1) or not (0)
invoiceTypeId	int	Indication of the type of invoice	SOEI_Invoice_Type. invoiceTypeId
treatmentPatientId	Varchar(25)	The patientId of the patient to whom the treatment for this invoice relates.	SOEI_Patient. patientId
gstAmount	numeric(18,4)	GST amount of the invoice amount

SOEI_Invoice_Type

This table contains invoice type code records.

Column	DataType	Description	Foreign Key on Table	Primary key
invoiceTypeId	int	Unique record identifier		
invoiceLegacyCode	int	Invoice type code used in the production database (as stored in SOEI_Invoice. invoiceOtherId)
invoiceTypeDescription	varchar(50)	Text for invoice type
sortable	smallint	Sortable reference for reports

SOEI_Location

This table contains location type code records.

Column	DataType	Description	Foreign Key on Table	Primary key
locationId	varchar(11)	Unique record identifier		
locationName	varchar(50)	Text to describe location
town	varchar(31)	Town/City name
zipCode	varchar(16)	Postcode for town
IsInactive	BIT	Whether this location is inactive (1) or not (0)
locationType	Varchar(11)	A code indicating the type of this location, as set up in the application’s location screen.
locationTypeDesc	Varchar(50)	A description of the type of this location, as set up in the application’s location screen
areaCode	varchar(16)	The area code derived from the postcode (zipCode field), i.e. the first part of the postcode.
NHSLocationNumber	Varchar(10)	The NHS location number for this site if applicable, blank otherwise
locationOrganisationName	Varchar(50)	The organisation name (e.g. PCT name) applicable for this site

SOEI_LOOKUP_AdjustmentType

This table contains Adjustment Type identifiers, their verbose descriptions and whether or not it’s a banking adjustment

Column	DataType	Description	Foreign Key on Table	Primary key
adjustmentTypeId	varchar(15)	Unique record identifier		
adjustmentTypeDescription	varchar(51)	Description of adjustment type
bankingAdjustment	bit	Banking adjustment to be made

SOEI_LOOKUP_AllocationType

This table all the different Allocation permutations

Column	DataType	Description	Foreign Key on Table	Primary key
allocation_typeId	int	Unique record identifier		
alloc_type_desc	varchar(50)	Description
alloc_source	varchar(20)	Allocation source
alloc_destination	varchar(20)	Allocation destination
legacy_source_code	numeric(9,0)	Source code from ‘old’ transaction
legacy_dest_code	numeric(9,0)	Destination code from ‘old’ transaction

SOEI_LOOKUP_AppointCategory

This table contains key and verbose descriptions for Appointment Categories

Column	DataType	Description	Foreign Key on Table	Primary key
appointmentCategoryId	int	Unique record identifier		
appointmentCategoryName	varchar(50)	Appointment Category description

SOEI_LOOKUP_ApptBookingMethod

This table contains codes and descriptions for the various Appointment booking methods

Column	DataType	Description	Foreign Key on Table	Primary key
apptBookingMethodId	smallint	Unique record identifier		
code	varchar(30)	Booking method short code
description	varchar(100)	Booking method description
isOnlineBookingMethod	bit	Flag indicating whether this method represents an online booking method (1) or not (0)

SOEI_LOOKUP_ApptStatus

This table contains codes and descriptions for the various Appointment Status

Column	DataType	Description	Foreign Key on Table	Primary key
apptStatusId	int	Unique record identifier		
description	varchar(100)	Appointment Status Description
isStatusFailed	bit	Flag indicating whether this status represents a failed appt (1) or not (0)
isStatusCancelled	bit	Flag indicating whether this status represents a cancelled appt (1) or not (0)
isStatusAudit	bit	Flag indicating whether this status represents an audit appt (1) or not (0) – e.g. an appointment record that exists only for audit purposes
isStatusValidBooked	bit	Flag indicating whether this status represents an appt that is not cancelled, failed, or audit (1) or not (0)
isStatusFutureBooked	bit	Flag indicating whether this status represents an appt that as of the refresh date was still marked as ‘booked’ or ‘family booked’ (1) or not (0)

SOEI_LOOKUP_CaregroupCode

This table contains the description for clinical data set category codes

Column	DataType	Description	Foreign Key on Table	Primary key
caregroupId	int	Unique record identifier		
caregroupCode	varchar(11)	Caregroup code id
caregroupDescription	varchar(51)	Description of the caregroup

SOEI_LOOKUP_ClinicalDataSet

This table contains the description for clinical data set category codes

Column	DataType	Description	Foreign Key on Table	Primary key
clinicalDataSetCategoryId	numeric(10,0)	Unique record identifier		
categoryDescription	varchar(51)	Description of the clinical data set category

SOEI_LOOKUP_ContactMessageType

This table contains the description for clinical data set category codes

Column	DataType	Description	Foreign Key on Table	Primary key
contactMessageTypeId	smallint	Unique record identifier		
contactMessageType	varchar(20)	Short descriptive code for the contact message type (e.g. PHONE, LETTER, EMAIL, SMS).
contactMessageTypeDescription	varchar(100)	Longer description of the contact message type.

SOEI_LOOKUP_DailyActivityType

This table contains codes and descriptions for activities listed in the SOEI_DailyActivitySummary table. Examples of the activityCodes are TRT_PLANNED, TRT_COMPLETE, REC_PATIENT (patient receipts).

Column	DataType	Description	Foreign Key on Table	Primary key
activityTypeId	smallint	Unique record identifier		
activityCode	Varchar(20)	Code describing the activity
activityDesc	varchar(100)	Longer description of the type of activity.

SOEI_LOOKUP_DentateState

This table contains codes and descriptions for the various dentate states

Column	DataType	Description	Foreign Key on Table	Primary key
dentateStateId	int	Unique record identifier		
description	varchar(100)	Dentate state description

SOEI_LOOKUP_Ethnicity

This table contains codes and descriptions for various ethnicity types.

Column	DataType	Description	Foreign Key on Table	Primary key
ethnicityId	varchar(25)	Unique record identifier		
ethnicityName	varchar(50)	Ethnicity name code
ethnicityDescription	varchar(200)	Ethnicity description
ethnicityNHSCode	numeric(10,0)	NHS code number for this ethnicity type

SOEI_LOOKUP_ContactPreference

This table contains codes and descriptions for various contact preference types.

Column	DataType	Description	Foreign Key on Table	Primary key
contactPreferenceId	int	Unique record identifier		
contactPreferenceDescription	varchar(32)	Description

SOEI_LOOKUP_ExternalRefType

This table contains codes and descriptions for the various patient external references

Column	DataType	Description	Foreign Key on Table	Primary key
refTypeId	int	Unique record identifier		
refTypeName	varchar(51)	Reference type description (e.g. Doctor, GDP, KC64)

SOEI_LOOKUP_ExemptionCategory

This table contains codes and associated descriptions for the various NHS exemption categories

Column	DataType	Description	Foreign Key on Table	Primary key
ExemptionCategoryId	smallint	Unique record identifier. Code 0 indicates the ‘not exempt’ category.		
ExemptionDescription	Varchar(200)	Description of the exemption category
ExemptionDescription	Varchar(200)	Shorter description of the exemption category

SOEI_LOOKUP_FlorideStatus

This table contains records for fluoride usage status.:

Column	DataType	Description	Foreign Key on Table	Primary key
florideStatusId	int	Unique record identifier		
description	varchar(100)	Fluoride status description

SOEI_LOOKUP_FreeFormatLabels

This table contains the labels for the patient details free format fields

Column	DataType	Description	Foreign Key on Table	Primary key
labelId	int	Unique record identifier		
labelName	varchar(51)	Label name

SOEI_LOOKUP_HicapsStatus

This table contains the status value settings for HICAPS transactions

Column	DataType	Description	Foreign Key on Table	Primary key
HicapsStatusId	tinyint	Unique record identifier		
HicapsStatusDescription	varchar(25)	Status description

SOEI_LOOKUP_O4CBand

This table contains O4 band codes and their descriptions

Column	DataType	Description	Foreign Key on Table	Primary key
O4CBandId	varchar(11)	Unique record identifier		
bandDescription	varchar(45)	Description of this O4C band

SOEI_LOOKUP_PatientRecallType

This table contains details of the different patient recall types – e.g. Dentist, Hygienist

Column	DataType	Description	Foreign Key on Table	Primary key
recallTypeId	tinyint	Unique record identifier		
recallTypeName	varchar(32)	Recall type description

SOEI_LOOKUP_PatientType1

This table contains User defined patient type 1 detail

Column	DataType	Description	Foreign Key on Table	Primary key
patientTypeId	varchar(5)	Unique record identifier		
patientTypeDescription	varchar(51)	User defined patient type 1 description

SOEI_LOOKUP_PatientType2

This table contains User defined patient type 2 detail

Column	DataType	Description	Foreign Key on Table	Primary key
patientTypeId	varchar(5)	Unique record identifier		
patientTypeDescription	varchar(51)	User defined patient type 2 description

SOEI_LOOKUP_ProviderCategory

This table contains Provider category lookup information

Column	DataType	Description	Foreign Key on Table	Primary key
providerCategoryId	int	Unique record identifier		
providerCategoryDescription	varchar(45)	Provider category description

SOEI_LOOKUP_ProviderType

This table contains Provider type lookup information

Column	DataType	Description	Foreign Key on Table	Primary key
providerTypeId	int	Unique record identifier		
providerTypeName	varchar(31)	Provider type description

SOEI_LOOKUP_RecallBreakdownProviderType

This table contains an indication of the different types of recall data breakdown – e.g. by last seen provider or patient provider.

Column	DataType	Description	Foreign Key on Table	Primary key
recallBreakdownProviderTypeId	tinyint	Unique record identifier		
recallBreakdownProviderTypeName	varchar(32)	Recall breakdown type description

SOEI_LOOKUP_RiskStatus

This table contains risk status code and description information

Column	DataType	Description	Foreign Key on Table	Primary key
riskStatusId	int	Unique record identifier		
description	varchar(100)	Risk status description

SOEI_LOOKUP_ServiceBehaviour

This table contains a code and description for the type of service carried out:

Column	DataType	Description	Foreign Key on Table	Primary key
serviceBehaviourId	int	Unique record identifier		
description	varchar(50)	Service behaviour description

SOEI_LOOKUP_ToothNumbers

This table contains a ‘map’ of tooth identification codes.

Column	DataType	Description	Foreign Key on Table	Primary key
toothId	int	Unique record identifier		
numberingScheme	varchar(10)	Code for numbering scheme used
toothNumber	varchar(10)	Tooth number code, appropriate to the numbering scheme
upperLowerType	varchar(1)	Character indicating whether the tooth is upper ‘U’, or lower ‘L’
permanentDeciduousType	varchar(1)	Character indicating whether the tooth is permanent ‘P’, or deciduous ‘D’
incisorMolarType	varchar(1)	Character indicating whether the tooth is an incisor ‘I’, premolar ‘P’, or molar ‘M’

SOEI_LOOKUP_TreatmentPlanStatus

This table contains a set of statuses, which a treatment plan can be in.

Column	DataType	Description	Foreign Key on Table	Primary key
treatmentPlanStatusId	smallint	Unique record identifier		
treatmentPlanStatus	varchar(30)	Indication of treatment plan status – blank, planned, TCd, transmitted, confirmed, disputed
treatmentPlanStatusDesc	varchar(200)	Long text description of the status

SOEI_LOOKUP_UDATransactionType

This table contains UDA Claim Status

Column	DataType	Description	Foreign Key on Table	Primary key
transactionTypeId	smallint	Unique record identifier		
transactionType	VARCHAR(30)	Indication of the UDA transaction type
transactionTypeCategory	VARCHAR(30)	Whether the transaction relates to udas: CLAIMED, PAID or TRANSFER
transactionTypeDesc	VARCHAR(200)	Long description of the transaction type

SOEI_LOOKUP_Weekends

This table contains weekend dates – used for the waitlist reports

Column	DataType	Description	Foreign Key on Table	Primary key
holidayDate	datetime	Unique record identifier		

SOEI_LOOKUP_WaitlistConfig

This table contains configuration values for waiting lists, such as the unavailability grace period, guarantee limit, etc.

Column	DataType	Description	Foreign Key on Table	Primary key
configKey	Varchar(40	Configuration item		
configValue	int	Configuration setting

SOEI_LOOKUP_WaitlistEntryDiagnosticCode

This table contains codes and descriptions for the various diagnostic codes used on wait list entries

Column	DataType	Description	Foreign Key on Table	Primary key
diagnosticCodeId	int	Unique record identifier		
diagnosticCode	varchar(11)	Diagnostic code string	SOEI_WaitlistEntry.diagnosticCode
description	varchar(51)	Diagnostic code description

SOEI_LOOKUP_WaitlistEntryOfferType

This table contains a mapping between an integer type representing a wait list entry offer, and a string description.

Column	DataType	Description	Foreign Key on Table	Primary key
offerTypeId	int	Unique identifier	SOEI_WaitlistEntryOffer.offerType	
description	Varchar(30)	Offer type description

SOEI_LOOKUP_WaitlistEntryOutcomeType

This table contains a mapping between an integer type representing a wait list entry offer outcome, and a string description.

Column	DataType	Description	Foreign Key on Table	Primary key
outcomeTypeId	int	Unique identifier	SOEI_WaitlistEntryOffer.outcomeType	
description	Varchar(30)	Outcome description

SOEI_LOOKUP_WaitlistEntryNonAttCategory

This table contains a mapping between an integer type representing a wait list entry non attendance category, and a string description.

Column	DataType	Description	Foreign Key on Table	Primary key
nonAttCategoryId	int	Unique identifier	SOEI_WaitlistEntryOffer.nonAttendanceCategory	
description	Varchar(30)	Non attendance category description

SOEI_LOOKUP_WaitlistEntryNonAttOutcome

This table contains a mapping between an integer type representing a wait list entry offer, and a string description.

Column	DataType	Description	Foreign Key on Table	Primary key
nonAttOutcomeId	int	Unique identifier	SOEI_WaitlistEntry.nonAttendanceOutcome	
description	Varchar(30)	Non attendance outcome description

SOEI_LOOKUP_WaitlistEntryReferralReason

This table contains the wait list entry referral reason codes and descriptions.

Column	DataType	Description	Foreign Key on Table	Primary key
referralReasonId	smallint	Unique identifier		
description	Varchar(51)	Referral reason description

SOEI_LOOKUP_WaitlistEntryReferralSource

This table contains the wait list entry referral sources and descriptions.

Column	DataType	Description	Foreign Key on Table	Primary key
referralSourceId	Varchar(64)	Unique identifier		
sourceDescription	Varchar(50)	Referral reason description
sourceCategory	Varchar(100)	Referral source category (Other / Recall / Self Referral, etc).

SOEI_LOOKUP_WaitlistEntryStatus

This table contains a mapping between an integer status code, its description and other details (these details may be changed from within the application, in the Wait List Management Configuration screen).

Column	DataType	Description	Foreign Key on Table	Primary key
statusId	int	Status Id code	SOEI_WaitlistEntryLog.status	
statusCode	Varchar(20)	Status code (internal code)
description	Varchar(20)	Status description
clockStatus	Varchar(20)	Clock state for this status (e.g. RUNNING, STOPPED etc)
isBookable	bit	Whether this is a bookable status (1) or not (0)
needsReason	bit	Whether this status code needs a reason when using it in the application

SOEI_LOOKUP_WaitlistEntryStatusReason

This table contains a mapping between an integer status code, its description and other details (these details may be changed from within the application, in the Wait List Management Configuration screen).

Column	DataType	Description	Foreign Key on Table	Primary key
statusReasonId	int	Unique identifier		
statusReasonCode	Varchar(11)	Status reason code (internal code)	SOEI_WaitlistEntry.statusReason
description	Varchar(51)	Status reason description

SOEI_LOOKUP_WaitlistType

This table contains a mapping between an integer type representing the type of a wait list, and a string description.

Column	DataType	Description	Foreign Key on Table	Primary key
waitlistTypeId	int	Unique identifier	SOEI_Waitlist.waitlistType	
description	Varchar(30)	Waitlist description

SOEI_LOOKUP_Weekends

This table contains weekend dates – used for the waitlist reports

Column	DataType	Description	Foreign Key on Table	Primary key
holidayDate	datetime	Unique record identifier		

SOEI_LOOKUP_XrayRequestIncorrectRefReason

This table contains the different reasons defined for incorrect X-ray referral reasons.

Column	DataType	Description	Foreign Key on Table	Primary key
xrayRequestIncorrectRefReasonId	int	Unique record identifier		
reasonDescription	Varchar(52)	Description of the incorrect referral reason

SOEI_LOOKUP_XrayRequestRepeatReason

This table contains the different reasons defined for repeat X-ray requests.

Column	DataType	Description	Foreign Key on Table	Primary key
xrayRequestRepeatReasonId	int	Unique record identifier		
reasonDescription	Varchar(52)	Description of the repeat reason

SOEI_LOOKUP_XrayRequestType

This table contains the different types of X-ray requests.

Column	DataType	Description	Foreign Key on Table	Primary key
xrayRequestTypeId	int	Unique record identifier		
typeDescription	Varchar(52)	Description of the type
isActive	bit	Whether the type is in use (active) – 1, or not (inactive) - 0

SOEI_NHSContract

This table contains details of the NHS contracts.

Column	DataType	Description	Foreign Key on Table	Primary key
NHSContractId	int	Unique record identifier		
NHSContractRecId	Varchar(25)	Unique record identifier in the EXACT database
contractNumber	Varchar(11)	Contract number, e.g. 3128430001
contractType	Varchar(5)	String indicating the type, one of ‘UDA’ or ‘UOA’
StartDate	datetime	Start date of this contract
EndDate	Datetime	End date of this contract
contractTarget	Int	Target number of UDAs/UOAs
achieved	Numeric(10,2)	Achieved (claimed) number of UDAs/UOAs. This corresponds to the ‘uda’ column value in the SOEI_Treatment_Plan table
achievedVerified	Numeric(10,2)	Achieved (verified) number of UDAs/UOAs. This corresponds to the ‘udaVerified’ column value in SOEI_Treatment_Plan

SOEI_NHSContractProvider

This table contains details of the providers associated with the NHS contracts.

Column	DataType	Description	Foreign Key on Table	Primary key
id	int	Unique record identifier		
NHSContractId	int	Contract this relates to	SOEI_NHSContract. NHSContractId
providerId	Varchar(11)	Provider code	SOEI_Provider.providerId
StartDate	datetime	Start date of this provider on this contract
EndDate	datetime	End date of this provider on this contract
providerTarget	int	UDA/UOA target for this provider on this contract
achieved	Numeric(10,2)	Achieved (claimed) number of UDAs/UOAs
achievedVerified	Numeric(10,2)	Achieved (verified) number of UDAs/UOAs
bIsVTProvider	bit	Whether this provider is a VT provider or not

SOEI_OCS_GPs

This table contains details of GPs imported from the national register (active if the internal.application.features.ocs data configuration in EXACT is set to 1).

Column	DataType	Description	Foreign Key on Table	Primary key
ocsGPId	varchar(25)	Unique record identifier		
code	varchar(11)	GP code
name	varchar(36)
roCode	varchar(11)
haCode	varchar(11)
address1	varchar(51)
address2	varchar(51)
address3	varchar(51)
address4	varchar(51)
address5	varchar(51)
postcode	varchar(9)
dtOpen	datetime
dtClose	datetime
status	varchar(2)
subTypeCode	varchar(2)
parentCode	varchar(11)
dtJoinParent	datetime
dtLeftParent	datetime
contactTelephone	varchar(16)
contactName	varchar(36)
addressType	varchar(11)
amendRec	varchar(2)
waveNumber	varchar(11)
gpFgPcgCode	varchar(11)
gpFhType	varchar(2)
area	varchar(5)
surname	varchar(36)	GP surname
initials	varchar(10)
townCounty	varchar(51)
location	varchar(11)	Location code for GP	SOEI_Location.locationId

SOEI_Patient

This table contains patient details:

Column	DataType	Description	Foreign Key on Table	PK
patientId	varchar(25)	Unique record identifier		
locationId	varchar(11)	Record identifier from Location file	SOEI_Location. locationId
dentistId	varchar(11)	Dentist identifier code
hygienistId	varchar(11)	Hygienist code
schoolId	varchar(11)	School ID
dentateStateId	int
title	varchar(36)	Patient saluation
firstName	varchar(36)	Patient first name
lastName	varchar(36)	Patient family name
previousName	varchar(51)	Patient previous family name
alsoKnownAs	varchar(36)	Patient also known as name
patientCode	varchar(21)	Patient code
altRef	varchar(16)	Patient NHS Number
NHSNumber	varchar(51)	Patient Alt Ref Number
homePhone	varchar(16)	Patient home phone
workPhone	varchar(16)	Patient work phone
mobilePhone	varchar(20)	Patient mobile phone
areaCode	varchar(16)	Patient area code (first section of postCode)
postCode	varchar(16)	Patient post code
languageSpoken	varchar(48)	Patient’s language
dob	datetime	Patient Date of Birth
payorCode	varchar(11)	Payor code
type1Id	varchar(5)		SOEI_LOOKUP_PatientType1 .patientTypeId
type2Id	varchar(5)		SOEI_LOOKUP_PatientType2 .patientTypeId
ethnicityId	Varchar(25)		SOEI_LOOKUP_Ethnicity .ethnicityId
familyCode	varchar(25)	Family Code
IsTransportRequired	bit	Whether the patient requires transport
IsDisabled	bit	Whether the patient is disabled
familyCode	varchar(25)	Family Code
doctorExtRefId	varchar(25)	Patient doctor details (if not using NHSOCS GP data)	SOEI_ExternalReference .externalReferenceId
GDPExtRefId	varchar(25)	Patient general dental practitioner details	SOEI_ExternalReference .externalReferenceId
referralSourceExtRefId	varchar(25)	Patient referral source details	SOEI_ExternalReference .externalReferenceId
OCSdoctorId	varchar(25)	Patient doctor details (if using NHSOCS GP data)	SOEI_OCS_GPs.ocsGPId
fundingAuthority	varchar(64)	Patient funding authority field
Gender	Varchar(1)	Patient Gender code (M/F)
addressLine1	Varchar(51)	1^st line of patient’s address
addressLine2	Varchar(51)	2nd line of patient’s address
addressLine3	Varchar(51)	3rd line of patient’s address
addressLine4	Varchar(51)	4^th line of patient’s address
currentBalance	Numeric(18,2)	The patient’s outstanding balance amount, as of the date the reporting database was refreshed.
IsInactive	bit	Whether the patient is inactive (1) or active (0)
inactiveReasonId	int	For patients that are inactive, the reason for inactivity	SOEI_LOOKUP_InactiveReasons. inactiveReasonId
KC64ExtRefId	Varchar(25)	The patient’s KC64 referral source, if applicable (as indicated on the SH patient details screen)	SOEI_ExternalReference. externalReferenceId
appointmentCreatedDateTime	datetime	Date and time that the patient record was created
HasBadDebt	bit	Flag indicating if the ‘Stop Credit Alert’ checkbox is set for this patient
emailAddress	varchar(100)	The patient email address that has been flagged with ‘send out-bound emails to this address’ set. If the patient has more than one such email address, one is chosen, using the preference: work, home then other.
bIsDentistNoRecallSet	bit	TRUE if the ‘No Recall’ checkbox is set to disable dentist recalls
dentistRecallIntervalMonths	tinyint	Period between dentist recall appts, in months
dateDentistRecall	datetime	Date of the next dentist recall appt
dateDentistLastRecallSent	datetime	Date the last dentist recall appt was booked
numDentistRecallsSent	tinyint	Number of times the patient has been recalled for the current recall appt
dentistRecallServiceId	varchar(11)	The service code to be applied to the dentist recall appt	SOEI_Service.serviceId
dentistRecallApptLengthMins	smallint	The required length of the dentist recall appt
dentistRecallProviderId	varchar(11)	The recall appt dentist	SOEI_Provider.providerId
bIsOHCPNoRecallSet	bit	TRUE if the ‘No Recall’ checkbox is set to disable OHCP recalls
OHCPRecallIntervalMonths	tinyint	Period between OHCP recall appts, in months
dateOHCPRecall	datetime	Date of the next OHCP recall appt
dateOHCPLastRecallSent	datetime	Date the last OHCP recall appt was booked
numOHCPRecallsSent	tinyint	Number of times the patient has been recalled for the current recall appt
OHCPRecallServiceId	varchar(11)	The service code to be applied to the OHCP recall appt	SOEI_Service.serviceId
OHCPRecallApptLengthMins	smallint	The required length of the OHCP recall appt
OHCPRecallProviderId	varchar(11)	The recall appt OHCP	SOEI_Provider.providerId
bIsPreventApptBookingSet	varchar(11)	TRUE if the ‘Prevent Appointment Booking’ checkbox is set, FALSE otherwise
IDNumber	Varchar(30)	Represents a specific patient ID number, depending on the country. NZ: NHI ID number; HK: ID number; SING: IC number; NLD: BSN number
bSmsMobile	bit	TRUE if this patient can receive SMS messages via their mobile phone.
contactPreferenceId	int	Patient’s preferred method of being contacted	SOEI_LOOKUP_ContactPreference.contactPreferenceId
dateFirstCompletedTreatment	datetime	The date of the patient’s earliest completed treatment item, or NULL if there is none
daysToFirstCompletedTreatment	datetime	Number of days from the patient created date to the first treatment date
monthsToFirstCompletedTreatment	datetime	Number of months from the patient created date to the first treatment date
dateLastCompletedTreatment	datetime	The date of the patient’s most recent completed treatment item, or NULL if there is none
daysSinceLastCompletedTreatment	datetime	Number of days from the last treatment date to the current date
monthsSinceLastCompletedTreatment	datetime	Number of months from the last treatment date to the current date
dateMadeInactive	date	Date this patient was ticked as being inactive, or NULL if not marked as inactive
patientCreatedDateOnly	date	Date this patient record was created (date only part)

SOEI_PatientActivityDates

This table contains distinct dates of different types for a given patient, such as the dates of that patient’s completed treatment. The dateActivity/datePeriodEnd values can be used as bounds so that the most recent activity of this type for any arbitrary date can be found.

Column	DataType	Description	Foreign Key on Table	Primary key
activityType	varchar(20)	Indicator of the activity type, one of: COMPLETED_TRT		
patientId	varchar(25)	Patient unique identifier	SOEI_Patient. patientId
itemId	int	Number of this patient’s activity date, starting at 1 for the earliest date
dateActivity	date	Date on which the activity took place, e.g. treatment completed
datePeriodEnd	date	Date of the end of the period. Either one day before the next date of this type, or a far future date (NOT NULL)

SOEI_PatientCareGroups

This table contains data for the various patient caregroups, entered from the patient details screen. The caregroups are linked to particular patients via the patientId column.

Column	DataType	Description	Foreign Key on Table	Primary key
Id	bigint	Unique record identifier		
patientId	varchar(25)	Patient unique identifier	SOEI_Patient.patientId
caregroupId	int	Caregroup code id	SOEI_LOOKUP_CaregroupCode. caregroupId

SOEI_PatientCareGroupSummary

This table contains a summary by patientId of all of the patien’ts caregroups

Column	DataType	Description	Foreign Key on Table	Primary key
patientId	varchar(25)	Patient unique identifier	SOEI_Patient.patientId
caregroups	Varchar(300)	Caregroups, represented as a string of caregroup Ids, delimited with semicolons. E.g. 4;5
caregroupCodes	Varchar(300)	Caregroup codess, represented as a string of caregroup codes, delimited with semicolons. E.g. PHOB; LD
caregroupDescriptions	Varchar(700)	Caregroup descriptions, represented as a string of caregroup descriptions, delimited with semicolons. E.g. Phobic; Learning Difficulties

SOEI_PatientContactMessage

This table contains details of the messages for a patient from the ‘Contacts’ tab on the patient details screen.

Column	DataType	Description	Foreign Key on Table	Primary key
contactMessageId	bigint	Message unique identifier		
patientId	Varchar(300)	Patient this message relates to	SOEI_Patient.patientId
createdDateTime	datetime	Date & time the message was created
sentDateTime	datetime	Date & time the message was sent. Currently only different from createdDateTime for letters, where the sentDateTime is the date the ‘sent’ button was pressed in the SOE application.
contactMessageTypeId	datetime	Indication of the type of this message.
locationId	Varchar(11)	Location this message was created at
userCode	Varchar(11)	User creating this message
messageComment	Varchar(200)	Short comment relating to this message. For phone messages this is the first line of the associated text box. For letters, this is the template name. For email/SMS messages, this is the message subject.
IsAppointmentReminder	bit	TRUE if this message is an appointment reminder, FALSE otherwise. Currently only TRUE for email/SMS messages which are automatic appointment reminders.
appointmentCommonId	Varchar(25)	For appointment reminders, indicates related appointments for this message	SOEI_Appointment. appointmentCommonId
mostRecentAppointmentId	Varchar(25)	For appointment reminders, the most recent non-audit appointment that this message relates to	SOEI_Appointment. appointmentId

SOEI_PatientEmailAddress

This table contains details of all email addresses held for patients. Each patient may have several email addresses defined in the EXACT patient details screen (e.g. work, home, etc), so there can be multiple rows in this table per patient.

Column	DataType	Description	Foreign Key on Table	Primary key
emailAddressRecId	Varchar(25)	Email address unique identifier		
patientId	Varchar(25)	Patient this message relates to	SOEI_Patient.patientId
emailAddress	Varchar(100)	Email address
useForSending	bit	Flag indicating if the this email should be used for sending (1) or not (0)
typeId	smallint	Numeric value indicating the type of email address. 0 = OTHER, 1 = HOME, 2 = WORK
typeDesc	Varchar(20)	Indication of the type of email address
contactDesc	Varchar(50)	Indication of the type of contact for this email address, e.g. ‘Self’

SOEI_PatientContactPermissions

This table contains details of the contact ‘opt-in’ permissions for various patient communications.

Column	DataType	Description	Foreign Key on Table	Primary key
contactPermissionRecId	Varchar(25)	Message unique identifier		
patientId	Varchar(25)	Patient this message relates to	SOEI_Patient.patientId
dateEdited	datetime	Date & time the contact permission was changed
isLatestEntry	bit	Indication of whether this is the latest entry for this patient (1) or a previous entry (0)
optinTypeId	smallint	Numerical indication of the communication type
optinTypeDesc	Varchar(40)	Description of the communication type – one of: REPUTATION_COM_EMAILS PRODUCT_SERVICE_INFO NEWSLETTERS IMPORTANT_NOTIFICATIONS ORAL_HEALTH_SURVEY_INFO
isOptedIn	bit	Indicates if the patient is opted in for this type of communication (1) or not (0)
enteredByUser	Varchar(30)	The user making this permission change

SOEI_PatientFreeFormatFields

This table contains data from the free format text fields entered from the patient details screen.

Column	DataType	Description	Foreign Key on Table	Primary key
id	bigint	Unique record identifier		
patientId	varchar(25)	Patient unique identifier	SOEI_Patient.patientId
labelIdId	int	Free format label unique identifier	SOEI_LOOKUP_FreeFormatLabels.labelId
fieldData	Varchar(51)	Free format field data

SOEI_PatientCustomTab

This table contains Patient custom tab data

Column	DataType	Description	Foreign Key on Table	Primary key
patientCustomTabId	varchar(25)	Unique record identifier		
patientId	varchar(25)	Record identifier from Patient file	SOEI_Patient.patientId
customScreenId	varchar(251)	custom screen unique identifier	SOEI_CUSTOM_* table where * is the custom screen table
entryDate	datetime	Date custom tab data was inserted
tabNumber	tinyint	Custom tab 1 or 2

SOEI_PatientRecall

This table contains an entry for each individual patient recall

Column	DataType	Description	Foreign Key on Table	Primary key
patientRecallId	varchar(25)	Unique record identifier		
recallTypeId	tinyint	Record identifier from Patient file	SOEI_LOOKUP_ PatientRecallType .recallTypeId
recallType	varchar(32)	Description of recall type – Dentist/Hygienist
patientProviderId	varchar(11)	The patient’s provider	SOEI_Provider.providerId
lastSeenProviderId	varchar(11)	The provider last seen	SOEI_Provider.providerId
patientId	varchar(25)	The patient’s provider	SOEI_Patient.patientId
dateOfRecall	datetime	Date the recall is due
bDoNotRecallPatient	bit	Whether the patient is flagged as not to recall (1) or not (0)
recallStatus	varchar(20)	A textual code indicating the recall status – one of DoNotRecall, MovedOn, Complete, Inactive, Failed, Future, Booked
recallAppointmentId	Varchar(25)	The appointment this recall is linked to, if this exists, NULL otherwise	SOEI_Appointment. appointmentId
recallAppointmentDate	datetime	The date of the appointment this recall is linked to
recallServiceLengthMinutes	tinyint	The length of the recall appointment
recallIntervalMonths	tinyint	The interval in months between recall dates
bHasRecallBeenAdvanced	tinyint	A flag indicating if this recall has been advanced (1) or not (0)
numPhoneContacts	smallint	Of all the contact messages linked with this recall, how many were phone calls
numEmailContacts	smallint	Number of email contacts linked with this recall
numSMSContacts	smallint	Number of SMS/Text contacts linked with this recall
numLetterContacts	smallint	Number of letter contacts linked with this recall
numEasypostLetterContacts	smallint	Number of Easypost letter contacts linked with this recall
numEasypostCardContacts	smallint	Number of Easypost card contacts linked with this recall
numTotalContacts	smallint	Number of email contacts linked with this recall

SOEI_Payor

This table contains payor information

Column	DataType	Description	Foreign Key on Table	Primary key
payorId	varchar(25)	Unique record identifier		
code	varchar(11)	Code to identify Payor
payorName	varchar(51)	Payor’s actual name
address1	varchar(31)	Payor address
bIsNHS	bit	Whether this is an NHS payor (1) or not (0)

SOEI_Perio_BPE

This table contains details of the Basic Perio Exams, from the ‘Perio’ tab (lower section) in EXACT.

Column	DataType	Description	Foreign Key on Table	Primary key
perioChartBPEId	bigint	Unique record identifier		
perioChartBPERecId	varchar(25)	Unique record identifier from SH database
dateBPE	datetime	Date of the BPE exam
patientId	varchar(25)	Patient the exam was for	SOEI_Patient.patientId
providerId	bit	Provider carrying out the exam (comes from the ‘User’ field on the SH BPE dialog)	SOEI_Provider.providerId
BPEScore_UR	smallint	BPE value for the Upper Right sector for this exam
BPEScore_UM	smallint	BPE value for the Upper Middle sector for this exam
BPEScore_UL	smallint	BPE value for the Upper Left sector for this exam
BPEScore_LR	smallint	BPE value for the Lower Right sector for this exam
BPEScore_LM	smallint	BPE value for the Lower Middle sector for this exam
BPEScore_LL	smallint	BPE value for the Lower Left sector for this exam
prevPerioChartBPEId	bigint	Link to the previous BPE exam before this one for this patient. NULL if this is the initial exam for this patient	SOEI_PerioChartBPE. perioChartBPEId

SOEI_Perio_DMF

This table contains details of the Decayed, Missing, Filled (DMF) Exams, from the ‘Perio’ tab (lower section) in EXACT.

Column	DataType	Description	Foreign Key on Table	Primary key
perioChartDMFId	bigint	Unique record identifier		
perioChartDMFRecId	varchar(25)	Unique record identifier from SH database
dateDMF	datetime	Date of the DMF exam
patientId	varchar(25)	Patient the exam was for	SOEI_Patient.patientId
providerId	bit	Provider carrying out the exam (comes from the ‘User’ field on the SH DMF dialog)	SOEI_Provider.providerId
decayed	smallint	Number of decayed teeth
missing	smallint	Number of missing teeth
filled	smallint	Number of filled teeth
sumDMF	smallint	Total of decayed, missing & filled
numTeeth	smallint	Total number of teeth
numSound	smallint	Total number of sound teeth, which is the total number of teeth less the decayed & filled teeth.
oralHygieneScore	smallint	Oral hygiene score for this exam
prevPerioChartDMFId	bigint	Link to the previous DMF exam before this one for this patient. NULL if this is the initial exam for this patient	SOEI_PerioChartDMF. perioChartDMFId

SOEI_Practice

This table contains Practice details

Column	DataType	Description	Foreign Key on Table	Primary key
practiceId	int	Unique record identifier		
practiceName	varchar(50)	Name of the Practice

SOEI_Prescription

This table contains information about prescriptions

Column	DataType	Description	Foreign Key on Table	Primary key
prescriptionId	varchar(25)	Unique record identifier		
prescriptionDate	datetime	Date the prescription was issued
patientId	varchar(25)	Patient the prescription was issued to	SOEI_Patient.patientId
providerId	varchar(11)	Provider issuing the prescription	SOEI_Provider.providerId
cotId	varchar(25)	Course of treatment this relates to, if applicable	SOEI_TreatmentPlan.cotId
numItems	tinyint	Number of items on this prescription (can be up to 3 items on the existing SH window)
numAntibioticItems	tinyint	How many of these 3 items were antibiotic items.

SOEI_ PrescriptionItem

This table contains Practice details

Column	DataType	Description	Foreign Key on Table	Primary key
prescriptionItemId	bigint	Unique record identifier		
prescriptionId	varchar(25)	The prescription this relates to	SOEI_Prescription.prescriptionId
prescriptionItemNumber	tinyint	The number of this item on the prescription (1-3)
batchNumber	varchar(8)	The batch number code for this item
itemDetailDescription	varchar(260)	Description of this item
bIsAntibiotic	bit	Whether this is an antibiotic item (1) or not (0)

SOEI_Provider

This table contains Provider details

Column	DataType	Description	Foreign Key on Table	Primary key
providerId	varchar(11)	Unique record identifier		
providerName	varchar(51)	Provider name
providerTypeId	int	Type code
gstNumber	varchar(21)	GST Number in NZ ACN Number in AUS

SOEI_ProviderCategories

This table contains details of the categories that providers belong to

Column	DataType	Description	Foreign Key on Table	Primary key
id	int	Unique record identifier		
providerId	varchar(11)	Provider code	SOEI_Provider.providerId
providerCategoryId	int	Provider category id	SOEI_LOOKUP_ProviderCategory. providerCategoryId

SOEI_Provider_Times

This table contains provider time allocations

Column	DataType	Description	Foreign Key on Table	Primary key
providerTimesId	varchar(25)	Unique record identifier		
providerId	varchar(11)	Record ID from Provider file	SOEI_Provider.providerId
roomId	varchar(40)	Record ID from Room file	SOEI_Room.roomId
blockType	varchar(15)	Type of time allocation
startDate	Datetime	Date and time the time allocation started
endDate	Datetime	Date and time the time allocation finished
reason	varchar(80)	Time allocation reason
lengthMinutes	bigint	Length of the allocation in minutes

SOEI_Receipt

This table contains information of receipts issued for payment received:

Column	DataType	Description	Foreign Key on Table	Primary key
receiptRecId	varchar(25)	Unique record identifier		
receiptId	varchar(25)	Actual receipt code
patientId	varchar(25)	Record identifier from Patient file	SOEI_Patient.patientId
payorId	varchar(25)	Record identifier from Payor file	SOEI_Payor.payorId
locationId	varchar(25)	Record identifier from Location file	SOEI_Location.locationId
providerId	varchar(11)	Record identifier from Provider file	SOEI_Provider.providerId
paymentMethod	varchar(25)	Method of payment
payerPayment	bit
receiptDate	datetime	Date receipt was issued
amount	numeric(18,4)	Receipt amount
enteredBy	Varchar(11)	The SH login of the user who created this invoice.
receiptIdFormatted	varchar(25)	A formatted version of the receipt number (e.g. 1093). May also include the site id.
depositId	varchar(25)	Deposit that this receipt belongs to, if any (NULL if not in a deposit).	SOEI_Deposit.depositId
openPayorId	varchar(25)	Indication of the payor this receipt should be associated with	SOEI_Payor.payorId
HicapsStatusId	tinyint	HICAPS status value	SOEI_LOOKUP_HicapsStatus.HicapsStatusId
gstAmount	Numeric(18,4)	GST amount of the receipt amount
referenceNumber	varchar(21)	Transaction number associated with third party payments

SOEI_RPT_InvoiceTransactionData

This table contains details of invoice transactions in a convenient table for reporting purposes.

Column	DataType	Description	Foreign Key on Table	Primary key
transactionId	INT	Invoice transaction id		
patientId	DATETIME	The patient associated with this invoice	SOEI_Patient.patientId
payorId	Varchar(25)	The payor for this invoice	SOEI_Payor.payorId
locationId	varchar(11)	Provider code this amount relates to	SOEI_Location.locationId
providerId	varchar(11)	Provider code this amount relates to	SOEI_Provider.providerId
transactionDate	Datetime	Invoice date
dataType	Vachar(20)	Indication of transction type, e.g. INVOICE
amount	Numeric(10,4)	Invoice amount
contractNumber	Varchar(11)	NHS contract number for this invoice
providerTypeName	Varchar(31)	Indication of the type of provider for this invoice
providerName	Varchar(51)	Name of the provider
firstName	Varchar(26)	Patient first name
lastName	Varchar(362)	Patient last name
payorCode	Varchar(11)	Payor code from the associated treatment plan (COT)
invoiceIdFormatted	Varchar(25)	Formatted version of the invoice number
cotId	Varchar(25)	Treatment plan number
outstandingAmount	Numeric(18,4)	Amount outstanding on this invoice

SOEI_RPT_NhsUdaPerformance

This table contains details of UDA amounts claimed and verified (confirmed), for a given NHS contract, provider and effective date. This data is taken from other tables (e.g. SOEI_UDA_DailySummary), and is provided for reporting convenience.

Column	DataType	Description	Foreign Key on Table	Primary key
effectiveDate	DATETIME	The date this UDA amount applied to (based on the treatment date)
NHSContractId	INT	The NHS contract this applies to	SOEI_NHSContract.NHSContractId
providerId	varchar(11)	Provider code this amount relates to	SOEI_Provider.providerId
UDADailySummaryId	bigint	UDA summary key	SOEI_UDA_DailySummary .UDADailySummaryId
udaClaimed	Numeric(10,2)	Total number of UDAs claimed for this date, contract & provider
udaVerified	Numeric(10,2)	Total number of UDAs verified (ie paid) for this date, contract & provider
udaClaimedTotalToDate	Numeric(10,2)	A running total of the UDAs claimed for this contract & provider
udaVerifiedTotalToDate	Numeric(10,2)	A running total of the UDAs verified for this contract & provider
contractNumber	Varchar(11)	NHS contract number for this UDA value
contractTarget	int	Target number of UDAs for this NHS contract
achieved	Numeric(10,2)	Total number of UDAs claimed for this contract
achievedVerified	Numeric(10,2)	Total number of UDAs verified (ie paid) for this contract
StartDate	Datetime	Start date of this contract
EndDate	Datetime	End date of this contract
ContractType	Varchar(5)	Indication of contract type – UDA or UOA

SOEI_Room

This table contains Povider room details

Column	DataType	Description	Foreign Key on Table	Primary key
roomId	varchar(40)	Unique record identifier		
locationCode	varchar(11)	Location of room

SOEI_Room_Times

This table contains room time allocations

Column	DataType	Description	Foreign Key on Table	Primary key
roomTimesId	varchar(25)	Unique record identifier		
roomId	varchar(40)	Record ID from Room file	SOEI_Room.roomId
providerId	varchar(25)	Record ID from Provider file
blockType	varchar(15)	Type of time allocation
startDate	datetime	Date and time start
endDate	datetime	Date and time finish
reason	varchar(80)
lengthMinutes	bigint	Length of the allocation in minutes

SOEI_Service

This table contains the list of services

Column	DataType	Description	Foreign Key on Table	Primary key
serviceId	varchar(25)	Unique record identifier		
serviceDesc	varchar(81)	Descriptive text
estimatedTime	numeric(10,0)	Estimated treatment time for this service item
payorCode	varchar(11)	Record ID from Payor file
pricingMethod	numeric(10,0)
pricingMethodDesc	varchar(30)	Description of the pricing method (Per Item, Surfaces 1, 2, By Jaw, etc)
dmfCountCompleted	Smallint
dmfCountCompletedDesc	varchar(30)	Description of the DMF count for completed items (No Score, Sound, F/Seal, etc)
dmfCountPlanned	Smallint
dmfCountPlannedDesc	varchar(30)	Description of the DMF count for planned items (No Score, Sound, F/Seal, etc)
dmfTreatAsWholeTooth	Bit	Value from the ‘Treat as whole tooth’ checkbox (1 = checked, 0 = unchecked)
serviceBehaviourId	Int	Service behaviour id	SOEI_LOOKUP_ServiceBehaviour. serviceBehaviourId
billingSummaryId	varchar(25)	Billing summary id	SOEI_BillingSummary. billingSummaryId
clinicalDataSetCategoryId	numeric(10,0)	Clinical data set category code for this service item	SOEI_LOOKUP_ClinicalDataSet. clinicalDataSetCategoryId

SOEI_Service_Fee

This table contains service fee information. The service pricing method (from table SOEI_Service) determines how many ADA codes and fee price(prices are GST Exclusive) are set.

Column	DataType	Description	Foreign Key on Table	Primary key
serviceId	varchar(25)	Unique record identifier		
scheduleId	varchar(25)	Schedule Id	SOEI_Fee_Schedule	
ADACode_0	varchar(11)	Code for the pricing
ADACode_1	varchar(11)	Code for the pricing
ADACode_2	varchar(11)	Code for the pricing
ADACode_3	varchar(11)	Code for the pricing
ADACode_4	varchar(11)	Code for the pricing
ADACode_5	varchar(11)	Code for the pricing
ADACode_6	varchar(11)	Code for the pricing
ADACode_7	varchar(11)	Code for the pricing
ADACode_8	varchar(11)	Code for the pricing
ADACode_9	varchar(11)	Code for the pricing
ADACode_10	varchar(11)	Code for the pricing
ADACode_11	varchar(11)	Code for the pricing
feePrice_0_0	numeric(18,4)	Lower tier price for the corresponding ADA code
feePrice_0_1	numeric(18,4)	Middle tier price for the corresponding ADA code
feePrice_0_2	numeric(18,4)	Upper tier price for the corresponding ADA code
feePrice_1_0	numeric(18,4)	Lower tier price for the corresponding ADA code
feePrice_1_1	numeric(18,4)	Middle tier price for the corresponding ADA code
feePrice_1_2	numeric(18,4)	Upper tier price for the corresponding ADA code
feePrice_2_0	numeric(18,4)	Lower tier price for the corresponding ADA code
feePrice_2_1	numeric(18,4)	Middle tier price for the corresponding ADA code
feePrice_2_2	numeric(18,4)	Upper tier price for the corresponding ADA code
feePrice_3_0	numeric(18,4)	Lower tier price for the corresponding ADA code
feePrice_3_1	numeric(18,4)	Middle tier price for the corresponding ADA code
feePrice_3_2	numeric(18,4)	Upper tier price for the corresponding ADA code
feePrice_4_0	numeric(18,4)	Lower tier price for the corresponding ADA code
feePrice_4_1	numeric(18,4)	Middle tier price for the corresponding ADA code
feePrice_4_2	numeric(18,4)	Upper tier price for the corresponding ADA code
feePrice_5_0	numeric(18,4)	Lower tier price for the corresponding ADA code
feePrice_5_1	numeric(18,4)	Middle tier price for the corresponding ADA code
feePrice_5_2	numeric(18,4)	Upper tier price for the corresponding ADA code
feePrice_6_0	numeric(18,4)	Lower tier price for the corresponding ADA code
feePrice_6_1	numeric(18,4)	Middle tier price for the corresponding ADA code
feePrice_6_2	numeric(18,4)	Upper tier price for the corresponding ADA code
feePrice_7_0	numeric(18,4)	Lower tier price for the corresponding ADA code
feePrice_7_1	numeric(18,4)	Middle tier price for the corresponding ADA code
feePrice_7_2	numeric(18,4)	Upper tier price for the corresponding ADA code
feePrice_8_0	numeric(18,4)	Lower tier price for the corresponding ADA code
feePrice_8_1	numeric(18,4)	Middle tier price for the corresponding ADA code
feePrice_8_2	numeric(18,4)	Upper tier price for the corresponding ADA code
feePrice_9_0	numeric(18,4)	Lower tier price for the corresponding ADA code
feePrice_9_1	numeric(18,4)	Middle tier price for the corresponding ADA code
feePrice_9_2	numeric(18,4)	Upper tier price for the corresponding ADA code
feePrice_10_0	numeric(18,4)	Lower tier price for the corresponding ADA code
feePrice_10_1	numeric(18,4)	Middle tier price for the corresponding ADA code
feePrice_10_2	numeric(18,4)	Upper tier price for the corresponding ADA code
feePrice_11_0	numeric(18,4)	Lower tier price for the corresponding ADA code
feePrice_11_1	numeric(18,4)	Middle tier price for the corresponding ADA code
feePrice_11_2	numeric(18,4)	Upper tier price for the corresponding ADA code

SOEI_Service_O4CBands

This table contains the link between service codes and their O4C bands, for a specific payor code.

Column	DataType	Description	Foreign Key on Table	Primary key
serviceBandId	int	Unique record identifier		
payorCode	Varchar(11)	Code for the payor	SOEI_Payor.code
serviceId	Varchar(25)	Service identifier	SOEI_Service.serviceId
O4CBandId	varchar(11)	Code for the O4C band	SOEI_LOOKUP_O4CBand

SOEI_Site

This table contains site information (e.g. schools, or other institutions)

Column	DataType	Description	Foreign Key on Table	Primary key
siteId	varchar(11)	Unique record identifier		
name	varchar(311)	Name of site
address1	varchar(51)	Address line 1
address2	varchar(51)	Address line 2
address3	varchar(51)	Address line 3
phone	varchar(16)	School phone number
locationId	varchar(11)	Record ID from Location file

SOEI_TransferTransaction

This table contains data about transactions that are transfers from a transaction to another patient or payor. It is typically used in conjunction with the SOEI_Allocation table, and provides the link between the transfer from and to transactions

Column	DataType	Description	Foreign Key on Table	Primary key
transferId	bigint	Unique record identifier		
transferFromRecId	varchar(25)	The id of the transfer from transaction
transferToRecId	varchar(25)	The id of the transfer to transaction
transferDate	varchar(51)	Date of the transfer
receiptId	varchar(25)	The receipt this transfer relates to	SOEI_Receipt.receiptId
invoiceId	varchar(25)	The receipt this transfer relates to	SOEI_Invoice.invoiceId
transferFromAmount	numeric(18,4)	The amount transferred from
transferToAmount	numeric(18,4)	The amount transferred from
cotId	varchar(25)	The treatment plan this relates to
patientId	varchar(25)	Patient being transferred to/from	SOEI_Patient.patientId
locationId	varchar(11)	Location code where transfer entered	SOEI_Location.locationId
providerId	varchar(11)	Provider code of the associated invoice/receipt	SOEI_Provider.providerId
enteredBy	varchar(11)	User code entering this transfer

SOEI_Treatment_Appointment

This table contains mid level course of treatment plan information – at the treatment appointment level

Column	DataType	Description	Foreign Key on Table	Primary key
treatment_appointmentId	Varchar(25)	Unique record identifier		
cotId	Varchar(25)	ID assigned to COT
apptNum	numeric(10,0)	Appointment number
providerId	Varchar(11)	Record ID from Provider file	SOEI_Provider.providerId
datePlanned	datetime	Date and time the treatment step was planned
dateComplete	datetime	Date and time the treatment step was completed
feeSchedule	Varchar(11)	Treatment Plan appointment fee schedule
itemDesc	Varchar(80)	Treatment Plan appointment description
chargeStatus	Varchar(10)	Treatment Plan appointment charge status
inActive	smallint	Treatment Plan appointment inactive flag
void	smallint	Treatment step void flag
priceCode	numeric(10,0)
apptPrice	numeric(18,4)	Total price of the treatment plan appointment
patientId	Varchar(25)	The patient for this treatment appt
plannedLocationId	Varchar(11)	Location where this treatment appt was planned
completeLocationId	Varchar(11)	Location where this treatment appt was completed
actualTimeTaken	datetime	The actual time taken for this treatment appt
plannedTime	datetime	The estimated time to be taken for this treatment appt
secondProviderId	Varchar(11)	The secondary provider logged in for this treatment appt

SOEI_Treatment_Plan

This table contains high level course of treatment plan information

Column	DataType	Description	Foreign Key on Table	Primary key
treatmentPlanId	varchar(25)	Unique record identifier		
cotId	varchar(25)	ID assigned to COT
patientId	varchar(25)	Record ID from Patient file	SOEI_Patient.patientId
dateComplete	datetime	Completion date for treatment
datePlanned	datetime	Date treatment originally planned
itemDesc	varchar(80)	Treatment plan description
chargeStatus	varchar(10)	Treatment plan charge status
inActive	smallint	Inactive treatment plan flag
void	smallint	Void treatment plan flag
payorCode	varchar(11)	Payor code
uda	numeric(10,2)	Number of UDAs claimed for this COT
udaVerified	numeric(10,2)	Number of UDAs claimed for this COT that have been verified
NHSProviderId	varchar(11)	Code for the provider who completed the treatment.	SOEI_Provider.providerId
plannedLocationId	Varchar(11)	Code for the treatment planned location	SOEI_Location.locationId
completeLocationId	Varchar(11)	Code for the treatment completed location	SOEI_Location.locationId
isFirstPlan	Bit	Whether this is the first treatment plan (COT) for this patient, based on the datePlanned date
NHSExemptionCategoryId	Smallint	Code for the exemption status of this treatment plan. 0 indicates ‘not exempt’	SOEI_LOOKUP_ExemptionCategory. ExemptionCategoryId
NHSContractId	Int	The NHS contract this plan relates to (may be NULL, eg for private treatment)	SOEI_NHSContract. NHSContractId
tcLocationId	Varchar(11)	The location where this COT was TC’d	SOEI_Location.locationId
numContacts	Int	The number of contacts (Treatment Appointments) in this COT
bIsNHS	bit	Whether this COT is NHS (1) or not (0)
bTC	bit	Whether this COT has been TC’d (1) or not (0).
cotNumberFormatted	Varchar(25)	A formatted text version of the cotId column. This is usually just a number, e.g. 1093, but if different site ids are present, the site id will be appended, eg. 1093 : 1
udaOpen	Numeric(10,2)	The total number of UDAs from this COT if in a PLANNED state (ie not completed/TCd).
NHSPaymentScheduleNumber	int	The NHS payment schedule number for this COT, if applicable
treatmentPlanStatusId	smallint	Indication of the current status of this COT	SOEI_LOOKUP_ TreatmentPlanStatus .treatmentPlanStatusId
dateTransmitted	datetime	For NHS COTs, if the COT has been transmitted, the date of transmission.
dateTCd	datetime	The date this COT was TC’d (when the TC button was pressed)
bTCdAsIncomplete	bit	Whether this COT was TC’d as incomplete, according to the FP17 checkbox (Treatment Incomplete)

SOEI_Treatment_Planstep

This table contains low level course of treatment plan information – down to the treatment step level

Column	DataType	Description	Foreign Key on Table	Primary key
treatment_planstepId	varchar(25)	Unique record identifier		
cotId	varchar(25)	ID assigned to COT
apptNum	numeric(10,0)	Appointment number
providerId	varchar(11)	Code for the provider of this treatment item	SOEI_Provider.providerId
secondProviderId	varchar(11)	Code for the secondary provider for this treatment item	SOEI_Provider.providerId
datePlanned	datetime	Date and time the treatment step was planned
dateComplete	datetime	Date and time the treatment step was completed
serviceCode	varchar(20)	Treatment step service code
plannedLocationId	varchar(11)	Location code for the treatment planned location	SOEI_Location.locationId
completeLocationId	varchar(11)	Location code for the treatment completed location	SOEI_Location.locationId
feeSchedule	varchar(11)	Treatment step fee schedule
itemDesc	varchar(80)	Service item description
chargeStatus	varchar(10)	Treatment step charge status
priceCode	numeric(10,0)	Treatment step price code
void	smallint	Treatment step void flag (0 = not void, 1 = void)
chartOnly	tinyint
planStepPrice	numeric(18,4)	Price for this plan step item
toothId	int	Placement of tooth concerned in plan step	SOEI_LOOKUP_ToothNumbers.toothId
serviceBehaviourId	int	Service behaviour id	SOEI_LOOKUP_ServiceBehaviour. serviceBehaviourId
surfaceMesial	bit	Mesial surface
surfaceDistal	bit	Distal surface
surfaceOcclusal	bit	Occlusal surface
surfaceIncisal	bit	Incisal surface
surfaceBuccal	bit	Buccal surface
surfacePalatal	bit	Palatal surface
customScreenId	varchar(25)	custom screen unique identifier	SOEI_CUSTOM_* table where * is the custom screen table
quantity	smallint	The quantity of this particular service to be provided
actualTimeTaken	datetime	Time taken to provide service(s)
plannedTime	datetime	Planned time to provide service(s)
O4CBandId	varchar(11)	Code for service O4CBand	SOEI_LOOKUP_O4CBand.O4CBandId
surfacesSummary	varchar(10)	String, such as ‘MOD’, summarising the surfaces affected on this planstep
patientId	varchar(25)	patientId this planstep relates to (use in the case of base chart items)	SOEI_Patient.patientId
labExtRefId	varchar(25)	Reference of the lab this planstep item refers to, if applicable	SOEI_ExternalReference.externalReferenceId
planStepPriceOrig	Numeric(18,4)	Original price for this plan step item
adaCode	Varchar(30)	The ADA code for this treatment item.

SOEI_Treatment_PlanstepSameTooth

This table contains a list of all subsequent treatment planstep items carried out for the same patient and on the same tooth as the original item. The later items are not base chart items, and ‘later’ means subsequent treatment – i.e. the treatment steps were added later to the chart. This table is intended to enable the identification of treatment subsequently carried out on a tooth that has had a particular service item.

Column	DataType	Description	Foreign Key on Table	Primary key
treatmentPlanstepSameToothId	varchar(25)	Unique record identifier		
treatmentPlanstepId	varchar(25)	The original treatment being considered	SOEI_Treatment_Planstep
laterTreatment_planstepId	varchar(25)	Id of a later planstep item, carried out on the same tooth	SOEI_Treatment_Planstep

SOEI_UDA_DailySummary

This table contains details of UDA amounts claimed and verified (confirmed), for a given NHS contract, provider and effective date. The UDA totals in this table take account of any internal UDA transfers made between providers.

Column	DataType	Description	Foreign Key on Table	Primary key
dailySummaryId	INT	Unique record identifier		
effectiveDate	DATETIME	The date this UDA amount applied to (based on the treatment date)
NHSContractId	INT	The NHS contract this applies to	SOEI_NHSContract.NHSContractId
providerId	varchar(11)	Provider code this amount relates to	SOEI_Provider.providerId
udaClaimed	Numeric(10,2)	Total number of UDAs claimed for this date, contract & provider
udaVerified	Numeric(10,2)	Total number of UDAs verified (ie paid) for this date, contract & provider
udaClaimedTotalToDate	Numeric(10,2)	A running total of the UDAs claimed for this contract & provider
udaVerifiedTotalToDate	Numeric(10,2)	A running total of the UDAs verified for this contract & provider

SOEI_UDA_Transaction

This table contains details for all UDA transactions that make up the totals in the SOEI_UDA_DailySummary table. These include information about UDAs claimed, withdrawn (resubmitted), paid and transferred. Any records that are for internal transfers also link to the SOEI_UDA_TransferDetail table, which shows the providers involved in the transfer.

Column	DataType	Description	Foreign Key on Table	Primary key
UDATransactionId	BIGINT	Unique record identifier		
UDATransactionTypeId	DATETIME	The type of transaction	SOEI_LOOKUP_UDATransactionType . UDATransactionTypeId
transactionDateTime	DATETIME	The date & time this transaction took place
effectiveDate	DATETIME	The date this UDA amount in this transaction applied to (based on the treatment date)
udaValue	Numeric(10,2)	The UDA value of this transaction
NHSPaymentScheduleNumber	INT	The NHS payment schedule this transaction relates to
NHSContractId	INT	The NHS contract this applies to	SOEI_NHSContract.NHSContractId
treatmentPlanId	varchar(25)	The treatment plan (COT) this transaction relates to	SOEI_TreatmentPlan. treatmentPlanId
providerId	varchar(11)	Provider code this amount relates to	SOEI_Provider.providerId
UDATransferId	BIGINT	If this transaction is an internal transfer, a link to the transfer record	SOEI_UDA_TransferDetail. UDATransferId

SOEI_UDA_TransferDetail

This table contains details of UDA amounts transferred internally between providers.

Column	DataType	Description	Foreign Key on Table	Primary key
UDATransferId	BIGINT	Unique record identifier		
transferDateTime	DATETIME	The date & time this transfer was made
effectiveDate	DATETIME	The date this UDA transfer applies to (based on the treatment date)
udaValue	Numeric(10,2)	Number of UDAs transferred
NHSContractId	INT	The NHS contract this applies to	SOEI_NHSContract.NHSContractId
treatmentPlanId	varchar(25)	Treatment plan (COT) this transfer is for	SOEI_TreatmentPlan. treatmentPlanId
fromProviderId	varchar(11)	Provider the UDAs are being transferred from	SOEI_Provider.providerId
toProviderId	varchar(11)	Provider the UDAs are being transferred to	SOEI_Provider.providerId

SOEI_Waitlist

This table contains waitlist information

Column	DataType	Description	Foreign Key on Table	Primary key
waitlistId	varchar(25)	Unique record identifier		
description	varchar(50)	Name of Waitlist
waitlistType	int	Type of waitlist (general, new patient, etc)	SOEI_LOOKUP_WaitlistType.waitlistTypeId
isActive	bit	Whether the waitlist is active (1) or not (0)
Is18WeekWL	bit	Whether the waitlist is an 18 Week waitlist (1) or not (0)

SOEI_WaitlistEntry

This table contains all the individual waitlist entries.

Column	DataType	Description	Foreign Key on Table	Primary key
waitlistEntryIntId	bigint	Unique record identifier		
waitlistEntryId	varchar(25)	Unique record identifier (text version)
waitlistId	varchar(25)		SOEI_Waitlist.waitlistId
patientId	varchar(25)	Record ID from Patient file
status	int	Current WL entry status, as of the RV refresh date	SOEI_LOOKUP _WaitlistEntryStatus.statusId
priority	int	Priority of this WL entry, an integer 1, 2 or 3
dateGuaranteeReference	datetime	The date (referred, received or assigned), used as the guarantee date calculation reference date
dateListed	datetime
dateRecall	datetime
dateAssigned	datetime
dateReferral	datetime
dateReceived	datetime
providerId	Varchar(11)	Provider code	SOEI_Provider.providerId
treatmentPlanId	varchar(25)	Course of treatment reference	SOEI_Treatment_Plan. treatmentPlanId
referralSource	varchar(64)	Code for the referral source for this WL entry	SOEI_LOOKUP _WaitlistEntryReferralSource .referralSourceId
referralSubSource	varchar(64)	Description of the referral sub-source for this WL Entry
diagnosticCode	varchar(11)	Short diagnostic code	SOEI_LOOKUP _WaitlistEntryDiagnosticCode. diagnosticCode
statusReason	varchar(11)	Status reason code	SOEI_LOOKUP _WaitlistEntryStatusReason. statusReasonId
guaranteeLimit	int	No. of days from guarantee ref date to guarantee date, as the Referral To Treatment (RTT) period
guaranteeLimitRTA	int	No. of days from guarantee ref date to guarantee date, as the Referral To Assessment/Appt (RTA) period.
reviewLimit	int	No. of days entry can be under review before triggering a warning
warningLimit	int	No. of days entry can be waiting before triggering a warning
waitlistEntryOfferId	varchar(25)	Link to offers associated with this waitlist entry	SOEI_WaitlistEntryOffer .waitlistEntryOffierId
referralReasonId	smallint	Id of referral reason	SOEI_LOOKUP _WaitlistEntryReferralReason .referralReasonId
dateFirstAppt	datetime	Date of first appt associated with this WL entry
dateFirstTreatment	datetime	Date of first TREATMENT status associated with this WL entry
dateCompleted	datetime	Date this WL entry was completed (ie clock stopped)
daysToFirstAppt	datetime	No of days between gurantee ref date and first appt date, taking into account unavailability days
daysToFirstTreatment	int	No of days between gurantee ref date and first treatment date, taking into account unavailability days
daysToCompleted	int	No of days between gurantee ref date and completed (clock stopped) date
pauseDaysFirstAppt	int	No of clock pause days between gurantee ref date and first appt date
pauseDaysFirstTreatment	int	No of clock pause days between gurantee ref date and first treatment date
pauseDaysCompleted	int	No of clock stopped days between gurantee ref date and completed (clock stopped) date
currDateGuaranteeReference	datetime	Current guarantee ref date used for the RTT guarantee date, as of the RV refresh date, taking into account any clock resets
currDateGuarantee	datetime	Current RTT guarantee date, as of the RV refresh date, measured from currDateGuaranteeReference
currDaysToCurrentDate	int	No of days waiting from currDateGuaranteeReference to the RV refresh date
currPauseDaysCurrentDate	int	No of clock pause days between the current gtee ref date and the RV refresh date
currDateGuaranteeReferenceRTA	datetime	Current guarantee ref daten used to calculate the RTA guarantee date, as of the RV refresh date, taking into account any clock resets
currDateGuaranteeRTA	datetime	Current RTA guarantee date, as of the RV refresh date, measured from currDateGuaranteeReferenceRTA
currPauseDaysRTA	int	No of clock pause days between the current RTA gtee ref date and the RV refresh date
requiredLocationId	Varchar(25)	The required location for this waitlist entry	SOEI_Location.locationId

SOEI_WaitlistEntryLog

This table contains audited logged entries of certain waitlist entry changes

Column	DataType	Description	Foreign Key on Table	Primary key
id	bigint	Unique record identifier		
waitlistEntryLogId	varchar(25)	Unique record identifier – text version
waitlistEntryId	varchar(25)	Record ID from Waitlst Entry	SOEI_WaitlistEntry. waitlistEntryId
waitlistEntryIntId	bigint	Record ID from Waitlst Entry	SOEI_WaitlistEntry. waitlistEntryIntId
Status	int	Integer status code	SOEI_LOOKUP_WaitlistEntryStatus.statusId
dateEffective	datetime	Date when this entry applies from
dateChanged	datetime
userCode	Varchar(11)	User who made this change
clockStatus	Varchar(20)	The clock status corresponding to the ‘status’ column. This is for convenience, the same value is also in SOEI_LOOKUP_WaitlistEntryStatus
statusCode	Varchar(20)	A status code description, again for convenience, the same value is in the above lookup waitlist entry status table.
nextEntry	bigint	Points to the next log entry in the sequence, i.e. the one occurring later in time.	SOEI_WaitlistEntryLog.id
nextDateEffective	datetime	The effective date of the next log entry.
isReset	bit	Flag indicating if this is a clock reset entry (1) or not (0)
isUnavailable	bit	Flag indicating if this is an unavailability entry (1) or not (0)
isClockPaused	bit	Flag indicating if this is a clock paused entry (1) or not (0)
isClockStopped	bit	Flag indicating if this is a clock stopped entry (1) or not (0)
isFirstPlanned	bit	Flag indicating if this is the first entry where the status was PLANNED (1) or not (0)
isFirstTreatment	bit	Flag indicating if this is the first entry where the status was TREATMENT (1) or not (0)
logIdOrder	Varchar(11)	User who made this change

SOEI_WaitlistEntryOffer

This table contains details of the offers made to a patient for a particular wait list entry (only if offer logging is turned on – see the Wait List Management Configuration screen)

Column	DataType	Description	Foreign Key on Table	Primary key
waitlistEntryOfferId	varchar(25)	Unique record identifier		
waitlistEntryId	varchar(25)	Record ID from Waitlst Entry	SOEI_WaitlistEntry.waitlistEntryId
dateOffer	datetime	Date the offer was made
dateApptOffered	datetime	Date proposed in the offer for an appointment
dateOutcome	datetime	Date there was some outcome to this offer
offerType	int	Code for the offer type (written, verbal, etc)	SOEI_LOOKUP_WaitlistEntryOfferType.offerTypeId
outcomeType	int	Code for the outcome type (accepted, no response, etc)	SOEI_LOOKUP_WaitlistEntryOutcomeType.outcomeTypeId
nonAttendanceCategory	int	Code for the non attendance category (could not attend, did not attend, etc)	SOEI_LOOKUP_WaitlistEntryNonAttCategory.nonAttCategoryId
nonAttendanceDate	datetime	Date of any non attendance
nonAttendanceOutcome	int	Code for the outcome following a non attendance (removed, remained, etc)	SOEI_LOOKUP_WaitlistEntryNonAttOutcome.nonAttOutcomeId

SOEI_XrayRequest

This table contains details of X-ray requests made for patients

Column	DataType	Description	Foreign Key on Table	Primary key
xrayRequestId	bigint	Unique record identifier		
patientId	varchar(25)	Patient identifier	SOEI_Patient.patientId
requestingProviderId	varchar(11)	Code of provider requesting X-ray	SOEI_Provider.providerId
authorisingProviderId	varchar(11)	Code of provider authorising X-ray	SOEI_Provider.providerId
requestingLocationId	varchar(11)	Code for location where request was made	SOEI_Location.locationId
xrayRequestTypeId	int	Type of request	SOEI_LOOKUP_XrayRequestType .xrayRequestTypeId
requestedDateTime	datetime	Date & time the request was made
requestStatus	Varchar(20)	Status of the request – one of: REQUESTED, STARTED, COMPLETED, VOID
requestPriority	int	Priority of the request, 1, 2 or 3
requestReason	Varchar(200)	Reason description for the request
fieldOfView	Varchar(200)	Field of view description
radiographerProviderId	varchar(11)	Code for the radiographer	SOEI_Provider.providerId
startedDateTime	datetime	Date & time the request was started
completedDateTime	datetime	Date & time the request was completed
numberTaken	int	Number of X-rays taken
comments	Varchar(200)	Any comments made
consultantProviderId	varchar(11)	Code for the consultant	SOEI_Provider.providerId
incorrectReferralReasonId	Int	Code indicating an incorrect referral reason code. NULL if no reason set.	SOEI_LOOKUP_XrayRequestIncorrectRefReason. xrayRequestIncorrectRefReasonId
isUserNotified	Bit	Was user notified (1) or not (0)

SOEI_XrayRequestRepeat

This table contains details of repeat X-ray requests made for patients

Column	DataType	Description	Foreign Key on Table	Primary key
xrayRequestRepeatId	bigint	Unique record identifier		
xrayRequestId	bigint	X-ray request to which this repeat request relates	SOEI_XrayRequest.xrayRequestId
repeatDateTime	datetime	Date & time when the repeat was made
radiographerProviderId	varchar(11)	Code of provider requesting X-ray	SOEI_Provider.providerId
repeatReasonId	int	Code for repeat reason	SOEI_LOOKUP_XrayRequestRepeatReason. xrayRequestRepeatReasonId
numberTaken	int	Number of X-rays taken on the repeat

Views

In addition to the tables, there are also some views that are created on these tables. This section describes the views that are created:

SOEI_VIEW_Site

This view is based on table SOEI_Site, and simply adds a new site named ‘All’ to this list. The columns are as for the SOEI_Site table.

SOEI_VIEW_Location

This view is based on table SOEI_Location, and simply adds a new site named ‘All’ to this list. The columns are as for the SOEI_Location table.

SOEI_VIEW_Provider

This view is based on table SOEI_Provider, and simply adds a new site named ‘All’ to this list. The columns are as for the SOEI_ Provider table.

Soei_View_Transactions

This view is based on tables SOEI_Invoice, SOEI_Receipt, and SOEI_Adjustment, and gives the set of all invoice, receipt & adjustment transactions. The columns are as follows:

Column	DataType	Description	Foreign Key on Table	Primary key
transactionId	varchar(25)	Unique record identifier for this transaction		
patientId	varchar(25)	Patient for this transaction	SOEI_Patient.patientId
locationId	varchar(11)	Transaction location code
providerId	varchar(11)	Transaction provider	SOEI_Provider.providerId
transactionDate	datetime	Transaction date
dataType	varchar(20)	Text code indicating the type of transaction, one of: INVOICE, RECEIPT, ADJUSTMENT
amount	Numeric(18,4)	The amount of the transaction.

SOEI_VIEW_ReceiptAdjustment

This view is based on tables SOEI_Adjustment and SOEI_LOOKUP_AdjustmentType, and shows adjustment transactions that are adjustments to receipts. The columns are as for the SOEI_Adjustment table.

SOEI_VIEW_InvoiceAdjustment

This view is based on tables SOEI_Adjustment and SOEI_LOOKUP_AdjustmentType, and shows adjustment transactions that are adjustments to invoices. The columns are as for the SOEI_Adjustment table.

SOEI_VIEW_CustomTables

This view shows the names of all of the the SOEI_CUSTOM screen tables that are created from custom screens.

Column	DataType	Description	Foreign Key on Table	Primary key
tableName	Varchar(128)	Name of the custom screen table, without the ‘SOEI_CUSTOM_’ prefix.

SOEI_VIEW_Holidays

This view is based on tables SOEI_LOOKUP_Weekends and SOEI_Holiday, and shows the combination of weekend and holiday dates.

Column	DataType	Description	Foreign Key on Table	Primary key
holidayDate	datetime	Date that is either a weekend date or a holiday date

List of EXACT Reporting Views Database Schema tables