EXACT Reporting Views

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.

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.

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.

Default Install Path: C:\Program Files (x86)\Software of Excellence\EXACT Reporting Views

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:

1 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:

1 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:

Once an EXACT database(s) has/have been upsized, from ISAM to SQL, you will then be able to view the data within SQL Server Management Studio.

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:

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

Working Example:

1 SoeAdmin.exe AutoSQLUpsize CelebsNZ SHABBABOX CelebsNZ_upsized SHABBABOX CelebsNZ_Reporting "D:\Shabba\EXACT_Data\CelebsNZ\\"

NB: The EXACT_db_PATH needs to be enclosed in double quotes and end with a “\\”

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.

The XML configuration file can contain as many ‘<database>’ elements as necessary, one for each EXACT ISAM database that is to be used.

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.

If the XML configuration file is not well-formed, an error will be shown in file %SOE_ADMIN_TOOL_DIR%\LogConfigParseSummary.html.


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.

Once an EXACT database(s) has/have been upsized, you will then be able to view the data within SQL Server Management Studio.

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.

Once an EXACT database(s) has/have been upsized, you will then be able to view the data within SQL Server Management Studio.

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 ‘1st Visit’ checkbox on an appointment in the application (1 = 1st 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)

1st 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)

4th 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