Open Data Gateway Tables

NOTE: MCE refreshes the ODG tables daily at 5 AM AST.

Table ID: 5EB6819A-BC0A-4CC2-ACE5-3E0077673A41
Table Name: AMOUNT_PERCENTAGE
Description: This dataset contains a list of manual percentage that can be billed.
Domain: NA

#	COLUMN NAME	DESCRIPTION	RETURN TYPE	DATABASE TYPE
1	ENGLISH_DESCRIPTION	The English name and description of this code	string	nvarchar(720) not null
1	FRENCH_DESCRIPTION	The French name and description of this code	string	nvarchar(720) not null
2	CODE	The code value	string	nvarchar(50) not null
3	FIRST_PERCENTAGE	The first percentage value to apply	string	decimal(18,0)
4	SUBSEQUENT_PERCENTAGE	The second percentage value to apply	string	decimal(18,0)
5	LAST_UPDATED_DATE_TIME	The last date time this record was updated	string	datetime

Table ID: D86E15C7-D094-4849-ABEC-A394B236D85B
Table Name: ANAESTHETIST_MODIFIER
Description: This data set contains the anaesthetist modifiers type codes that are eligible for submission to Medicare. This dataset also contains the English and French descriptions for the anaesthetist modifiers. This information is used to indicate whether or not the modifier is applied to the claim for certain pre-existing health conditions of a patient which changes the entitlement amount allowed for general anesthesia charges under a service code.
Domain: NA

#	COLUMN NAME	DESCRIPTION	RETURN TYPE	DATABASE TYPE
1	ANAESTHESIA_MODIFIER	The associated anaesthesia modifier code	string	numeric(9,0) not null
2	ENGLISH_DESCRIPTION	English description	string	nvarchar(720) not null
3	FRENCH_DESCRIPTION	French description	string	nvarchar(720) not null
4	MODIFIER_UNITS	Unit value for this modifier	string	int
5	EFFECTIVE_DATE	Date the modifier units are effective	string	datetime
6	TERMINATION_DATE	Date the modifier units are terminated	string	datetime

Table ID: 61E94878-2315-419B-B94B-2AD057C76C31
Table Name: CANCELLED_SURGERY_REASON
Description: Reasons for Cancelled Surgery
Domain: NA

#	COLUMN NAME	DESCRIPTION	RETURN TYPE	DATABASE TYPE
1	CODE	The associated cancelled surgery reason code	string	numeric(9,0) not null
2	ENGLISH_DESCRIPTION	English description	string	varchar(180) not null
3	FRENCH_DESCRIPTION	French description	string	nvarchar(180) not null

Table ID: 26E07BA1-9895-4FFB-BF18-FC15E68087A8
Table Name: DATE_TIME_BASED_SERVICE_CODES
Description: This data set is a list of service codes that identify whether or not start/end date and time is required on the claim.
Domain: NA

#	COLUMN NAME	DESCRIPTION	RETURN TYPE	DATABASE TYPE
1	SERVICE_CODE	The service code	string	int
2	START_TIME_REQUIRED	Indicator whether or not start time is required	string	int
3	END_DATE_TIME_REQUIRED	Indicator whether or not end date and time are required	string	int
4	START_DATE_TIME_REQUIRED	Indicator whether or not start date and time are required	string	int

Table ID: B0BBB32E-4DB6-48DA-8EF5-79C6FB34F1F2
Table Name: EXCLUSIVE_MEDICARE_NUMBERS
Description: List of special medicare numbers
Domain: NA

#	COLUMN NAME	DESCRIPTION	RETURN TYPE	DATABASE TYPE
1	MEDICARE_NUMBER	The medicare number	string	bigint

Table ID: B3438C24-EBEF-4BA6-B371-68E0CD16DD2A
Table Name: GENDER
Description: This data set contains the genders that are eligible for submission to Medicare. This dataset also contains the English and French descriptions for the genders. This information is used to identify the gender of the patient.
Domain: NA

#	COLUMN NAME	DESCRIPTION	RETURN TYPE	DATABASE TYPE
1	GENDER	The gender	string	nvarchar(20) not null
2	ENGLISH_DESCRIPTION	English description	string	nvarchar(720) not null
3	FRENCH_DESCRIPTION	French description	string	nvarchar(720) not null

Table ID: DBD4F093-ADC7-4B4F-88C1-D4EBD264B27B
Table Name: ICD10_DIAGNOSIS_EXCLUDED_SERVICE_CODES
Description: Services codes that do not require an ICD10 diagnosis code.
Domain: NA

#	COLUMN NAME	DESCRIPTION	RETURN TYPE	DATABASE TYPE
1	VALUE	The service code	string	int

Table ID: 947C575D-3787-4BA8-BBB3-8BD529C15430
Table Name: IMMUNIZATION_PRODUCT
Description: This data set contains the immunization product codes that are eligible for submission to Medicare. This dataset also contains the associated product names and service codes for the immunization product code. This information is used to identify the products associated with the selected immunization service.
Domain: NA

#	COLUMN NAME	DESCRIPTION	RETURN TYPE	DATABASE TYPE
1	IMM_PRODUCT_CODE	The immunization product code identifier	string	nvarchar(20) not null
2	ENGLISH_DESCRIPTION	English description	string	nvarchar(720) not null
3	FRENCH_DESCRIPTION	French description	string	nvarchar(720) not null
4	SERVICE_CODE	The service code	string	bigint not null
5	INACTIVE_DATE	The inactive date of the immunization product for a service code	string	datetime

Table ID: 5D7A3F70-0211-4C5A-8BDA-198B1333CD8F
Table Name: MEDICAL_SITE
Description: This data set contains medical site codes which are eligible for claim submission to Medicare. The dataset also contains the English and French descriptions for the medical site's codes. This information is used to identify the site number where the service was performed. (i.e. Hospital, Nursing Home, CHC, HCS, Jails, After Hours Clinic, Telemedicine)
Domain: NA

#	COLUMN NAME	DESCRIPTION	RETURN TYPE	DATABASE TYPE
1	SITE	The site code	string	nvarchar(20) not null
2	ENGLISH_DESCRIPTION	English description	string	nvarchar(720) not null
3	FRENCH_DESCRIPTION	French description	string	nvarchar(720) not null

Table ID: 50110D8C-F64A-4956-AB54-CBF22569DB8E
Table Name: PREMIUM_RATE
Description: Premiums and their rates used in unit calculations
Domain: NA

#	COLUMN NAME	DESCRIPTION	RETURN TYPE	DATABASE TYPE
1	PREMIUM_RATE_TYPE_CODE	The numeric code of the premium rate type	string	numeric(9,0) not null
2	EFFECTIVE_DATE	Date the premium rate is effective	string	numeric(9,0) not null
3	TERMINATION_DATE	Date the premium rate is terminated	string	nvarchar(720) not null
4	RATE	The premium rate value	string	numeric(7,4)

Table ID: A1653FB3-40CF-4AF9-AA67-104D3E66F7CE
Table Name: PREMIUM_RATE_TYPE
Description: List of available premium rate types
Domain: NA

#	COLUMN NAME	DESCRIPTION	RETURN TYPE	DATABASE TYPE
1	CODE	The numeric code of the premium rate type	string	numeric(9,0) not null
2	ENGLISH_DESCRIPTION	English description	string	nvarchar(180) not null
3	FRENCH_DESCRIPTION	French description	string	nvarchar(180) not null

Table ID: BE67435B-61CD-4E94-814B-1A359F707D3D
Table Name: PROVIDER_ROLE
Description: This data set contains the provider role codes eligible for claims submission to Medicare. The dataset also contains the English and French description. This information is used to indicate the service provider role in providing a service.
Domain: NA

#	COLUMN NAME	DESCRIPTION	RETURN TYPE	DATABASE TYPE
1	PROVIDER_ROLE	The provider role code	string	nvarchar(20) not null
2	ENGLISH_DESCRIPTION	English description	string	nvarchar(720) not null
3	FRENCH_DESCRIPTION	French description	string	nvarchar(720) not null

Table ID: 2916CE06-31DB-4D1D-B2F1-82006E67395F
Table Name: PROVIDER_TYPE
Description: This dataset contains the Provider Type codes. The dataset also contains the English and French descriptions for the Provider Type codes. This information is used indicate the Service Provider's type within the list of Referring Service Providers.
Domain: NA

#	COLUMN NAME	DESCRIPTION	RETURN TYPE	DATABASE TYPE
1	PROVIDER_TYPE	Provider type code	string	numeric(9,0) not null
2	ENGLISH_DESCRIPTION	English description	string	nvarchar(720) not null
3	FRENCH_DESCRIPTION	French description	string	nvarchar(720) not null

Table ID: 260EA464-C8D7-4A51-B5F9-85BF668087DF
Table Name: PROVINCE
Description: This data set contains the province codes that are eligible for submission to Medicare. This dataset also contains the English and French descriptions for the province codes. This information is used to identify the patient’s province.
Domain: NA

#	COLUMN NAME	DESCRIPTION	RETURN TYPE	DATABASE TYPE
1	PROVINCE_CODE	The province code	string	nvarchar(20) not null
2	ENGLISH_DESCRIPTION	English description	string	nvarchar(720) not null
3	FRENCH_DESCRIPTION	French description	string	nvarchar(720) not null

Table ID: F0382A9B-71CB-4301-8CB0-3265B52E3473
Table Name: REFERRAL_TYPE
Description: This data set contains the referral type codes that are eligible for submission to Medicare. This dataset also contains the English and French descriptions for the referral type codes. This information is used when a referral provider is present, the code will indicate which situation prevailed.
Domain: NA

#	COLUMN NAME	DESCRIPTION	RETURN TYPE	DATABASE TYPE
1	REFERRAL_TYPE	The referral type code	string	int not null
2	ENGLISH_DESCRIPTION	English description	string	nvarchar(720) not null
3	FRENCH_DESCRIPTION	French description	string	nvarchar(720) not null

Table ID: 9F3A5B15-055C-44BE-B7CE-F155D7793162
Table Name: REFERRING_SERVICE_PROVIDER
Description: This data set contains Service Providers that are eligible for referral. This dataset also contains the last, first and middle names of the Service Providers. This information is used to identify the referring Service Provider number on a claim.
Domain: NA

#	COLUMN NAME	DESCRIPTION	RETURN TYPE	DATABASE TYPE
1	CODE_VALUE	The service provider number	string	nvarchar(20) not null
2	DESC_EN	The service provider name	string	nvarchar(402)
3	DESC_FR	The service provider name	string	nvarchar(402)
4	PROVIDER_TYPE_CODE	The provider type code	string	int

Table ID: 18564A31-8758-47E7-89CF-73FA31D6AAE2
Table Name: ROLE_3_EXEMPT_CODES
Description:
Domain: NA

#	COLUMN NAME	DESCRIPTION	RETURN TYPE	DATABASE TYPE
1	SERVICE_CODE	The service code that is exempt from role 3	string	big int

Table ID: 989778D5-5E06-4412-90F9-A4C4FEE4DAE8
Table Name: ROLE_6_EXEMPT_CODES
Description:
Domain: NA

#	COLUMN NAME	DESCRIPTION	RETURN TYPE	DATABASE TYPE
1	SERVICE_CODE	The service code that is exempt from role 6	string	big int

Table ID: 79FA9F47-DB01-4B3F-A4A4-2C05BA5352A6
Table Name: SERVICE_BASE_CODE
Description: Associates service codes to a list of base service codes that determines the base units.
Domain: NA

#	COLUMN NAME	DESCRIPTION	RETURN TYPE	DATABASE TYPE
1	SERVICE_CODE	The service code that has the BASE_CODE_PERCENT set in Service Eligibility	string	bigint not null
2	EFFECTIVE_DATE	Indicates the start date	string	datetime not null
3	TERMINATION_DATE	Indicates the end date for this version of the service code	string	datetime not null
4	BASE_SERVICE_CODE	The base service code that supplies the base units for the percentage calculation	string	bigint not null
5	LAST_UPDATED_DATE_TIME	The date this record was last updated	string	datetime not null

Table ID: 1DAA172D-26DF-4B23-91EB-2D92E85A1259
Table Name: SERVICE_CODE
Description: This data set contains service codes that are eligible for claim submission to Medicare. This dataset also contains the English and French service descriptions for each of the service codes. This information is used to indicate the service code applicable to the service rendered.
Domain: NA

#	COLUMN NAME	DESCRIPTION	RETURN TYPE	DATABASE TYPE
1	SERVICE_CODE	The service code. This information can be used to select corresponding service eligibility	string	bigint not null
2	ENGLISH_DESCRIPTION	English description	string	nvarchar(260)
3	FRENCH_DESCRIPTION	French description	string	nvarchar(260)
4	PROVIDER_TYPE_CODE	The provider type code. Dentists may have an alternate basic unit in MCE	string	int

Table ID: A6BECB34-03EB-406B-95E4-13AE1407A861
Table Name: SERVICE_ELIGIBILITY
Description: This data set contains the service eligibility details for the service codes that are eligible for claim submission to Medicare. Service Eligibility is determined from the Service Code and Date of Service inputs.
Domain: NA

#	COLUMN NAME	DESCRIPTION	RETURN TYPE	DATABASE TYPE
1	SERVICE_CODE_ID	The service code	string	bigint not null
2	EFFECTIVE_DATE	Indicates the start date for this version of the service code	string	datetime not null
3	TERMINATION_DATE	Indicates the end date for this version of the service code	string	datetime not null
4	BASIC_UNITS	Unit value for this service	string	decimal(18,0)
5	ANAESTHETIST_UNITS	Unit value for this service, when the provider role is equal to 2 (ANESTHETIST)	string	decimal(18,0)
6	ANAESTHESIA_MODIFIER_ALLOWED	Indicates whether or not the service code can have an anaesthesia modifier applied	string	bit
7	SERVICE_MAXIMUM	Maximum Service Count allowed across all claims in claim history	string	decimal(18,0)
8	BASIC_PREMIUM_RATE	% premium over the basic rate (BASIC_UNITS). This information is used for after-hours service provision	string	decimal(7,4)
9	ANAESTHETIST_PREMIUM_MINIMUM	Minimum payable anaesthetist premium	string	decimal(18,0)
10	ANAESTHETIST_PREMIUM_MAXIMUM	Maximum payable anaesthetist premium	string	decimal(18,0)
11	CANCER_PREMIUM_RATE	% premium over the basic rate (BASIC_UNITS). The information is used for service provision cancer patients	string	decimal(7,4)
12	BASIC_GENERIC_RATE	Certain Services are paid at this “generic” dollar rate regardless of the Specialty involved	string	decimal(7,4)
13	EXCLUDE_DURATION	Identifies whether or not to exclude the Anaesthesia calculations	string	bigint
14	SERVICE_INTERVAL	Identifies the duration, in minutes, used for payments in service codes that use the Anaesthesia Duration in calculations, but do not follow the Basic Anaesthesia Duration calculation	string	int
15	BASE_CODE_PERCENT	The percentage to be used when calculating the units from the selected base service	string	numeric(3,0)
16	ANAESTHETIST_PREMIUM_ALLOWED	Indicates whether or not the service code can have an anaesthesia premium applied	string	bit
17	BASIC_PREMIUM_ALLOWED	Indicates whether or not the service code can have a basic premium applied	string	bit
18	CANCER_PREMIUM_ALLOWED	Indicates whether or not the service code can have a cancer premium applied	string	bit

Table ID: 4848DA8A-44DD-458D-80E8-939784F344EA
Table Name: SERVICE_LOCATION_TYPE
Description: This data set contains the service locations that are eligible for submission to Medicare. This dataset also contains the English and French descriptions for the locations. This information is used to identify the service location where the service was rendered
Domain: NA

#	COLUMN NAME	DESCRIPTION	RETURN TYPE	DATABASE TYPE
1	SERVICE_LOCATION	The service location code	string	nvarchar(20) not null
2	ENGLISH_DESCRIPTION	English description	string	nvarchar(720) not null
3	FRENCH_DESCRIPTION	French description	string	nvarchar(720)

Table ID: 01AFA67A-387F-4C6C-ADAD-536A2E39A69B
Table Name: SERVICE_MODIFIER_TYPE
Description: This data set contains the service modifier codes that are eligible for submission to Medicare. This dataset also contains the English and French descriptions for the service modifier codes. This information is used to qualify exact billing data re anatomical areas/locations or, surgical approaches for the purpose of applying accurate payment % for multiple same day procedures/surgeries
Domain: NA

#	COLUMN NAME	DESCRIPTION	RETURN TYPE	DATABASE TYPE
1	SERVICE_MODIFIER_TYPE	The service modifier code	string	nvarchar(20) not null
2	ENGLISH_DESCRIPTION	English description	string	nvarchar(720) not null
3	FRENCH_DESCRIPTION	French description	string	nvarchar(720)

Table ID: 27172DF8-2ED8-4A88-8F76-6BB6C1EA3E04
Table Name: SPECIALTY_RATE
Description: The table lists the specialty ID code, description(English/French), effective date, basis rate and Anaes rate for each specialty
Domain: NA

#	COLUMN NAME	DESCRIPTION	RETURN TYPE	DATABASE TYPE
1	SPECIALTY	An alpha ID used within Medicare to identify a specialty in medicine	string	varchar(50) not null
2	ENGLISH_DESCRIPTION	English description	string	nvarchar(180) not null
3	FRENCH_DESCRIPTION	French description	string	nvarchar(180)
4	EFFECTIVE_DATE	The start date on which the basic rate and/or anaes rate is used to calculate payment	string	datetime2 not null
5	BASIC_RATE	The dollar ($) rate per basic unit	string	numeric(7,4) not null
6	ANAESTHETIST_RATE	The dollar ($) rate per anaes unit- role 2 claims only	string	numeric(7,4) not null

Table ID: C5B6298D-9E9E-4C40-BB72-1B843C460D46
Table Name: VACCINE_LOT
Description: This data set contains vaccine lot identifiers that are eligible for submission to Medicare. This dataset also contains the description for the identifiers. This information is used to identify the vaccine lots associated to the immunization product selected
Domain: NA

#	COLUMN NAME	DESCRIPTION	RETURN TYPE	DATABASE TYPE
1	VACCINE_LOT_CODE	The unique identifier for the vaccine lot number	string	nvarchar(20) not null
2	ENGLISH_DESCRIPTION	English description	string	nvarchar(720) not null
3	FRENCH_DESCRIPTION	French description	string	nvarchar(720)
4	IMM_PRODUCT_CODE	The immunization product code associated with the vaccine lot	string	int not null
5	SERVICE_CODE	The service code associated with the vaccine lot	string	bigint not null
6	EFFECTIVE_DATE	The effective date of the vaccine lot number	string	datetime not null
7	TERMINATION_DATE	The termination date of the vaccine lot number	string	datetime not null

Table ID: 78195421-9FAC-487C-9201-1EE20FF4AFC8
Table Name: VALID_ROTATIONS
Description: List of eligible rotation codes for submission to Medicare. This information is used to indicate the rotation code on the claim when required
Domain: NA

#	COLUMN NAME	DESCRIPTION	RETURN TYPE	DATABASE TYPE
1	VALUE	The rotation value	string	nvarchar(120)

Table ID: 54203A8E-8738-4834-8930-982F7DEA77C6
Table Name: SERVICE_CODES_REQUIRING_ROTATION_CODE
Description: List of service codes that require a rotation code
Domain: NA

#	COLUMN NAME	DESCRIPTION	RETURN TYPE	DATABASE TYPE
1	SERVICE_CODE_ID	The service code	string	int

Table ID: 9076BD80-7942-4F1B-9EBF-1B1696E4C8F0
Table Name: SERVICE_CODES_REQUIRING_REFERRING_DOCTOR
Description: List of service codes that require a referring provider
Domain: NA

#	COLUMN NAME	DESCRIPTION	RETURN TYPE	DATABASE TYPE
1	SERVICE_CODE_ID	The service code	string	int

Table ID: 9C908939-63A4-4AEC-AC02-83BE1A234F7F
Table Name: SERVICE_CODES_REQUIRING_ONCALL_PATIENT
Description: List of service codes that require medicare number 111111118 (ONCALL PATIENT)
Domain: NA

#	COLUMN NAME	DESCRIPTION	RETURN TYPE	DATABASE TYPE
1	VALUE	The service code	string	int

Table ID: 5FBCD346-3015-4C7A-8C64-FDB6D36AA61D
Table Name: SERVICE_CODES_REQUIRING_ADMIN_PATIENT
Description: List of service codes that require medicare number 111111126 (ADMIN PATIENT)
Domain: NA

#	COLUMN NAME	DESCRIPTION	RETURN TYPE	DATABASE TYPE
1	VALUE	The service code	string	int

Table ID: 4EA612B0-6A90-40AB-B785-EF736A6EA74B
Table Name: SERVICE_CODES_THAT_ARE_PROCEDURES_CATEGORY_3000
Description: Procedural service codes eligible for claim submission to Medicare
Domain: NA

#	COLUMN NAME	DESCRIPTION	RETURN TYPE	DATABASE TYPE
1	VALUE	The service code	string	int

Table ID: 5F674C15-4076-4D0D-92C1-3B189CF2480F
Table Name: SERVICE_CODES_REQUIRING_DATE_TAKEN
Description: List of service codes that require a date taken
Domain: NA

#	COLUMN NAME	DESCRIPTION	RETURN TYPE	DATABASE TYPE
1	VALUE	The service code	string	int

Table ID: 83C22E64-53BF-45F8-BF6B-21980CEF831
Table Name: SURGICAL_MODIFIER_TYPE
Description: Surgical Modifier Types related to procedural service codes (category 3000)
Domain: NA

#	COLUMN NAME	DESCRIPTION	RETURN TYPE	DATABASE TYPE
1	CODE	The associated surgical modifier type code	string	numeric(9,0) not null
2	ENGLISH_DESCRIPTION	English description	string	varchar(180) not null
3	FRENCH_DESCRIPTION	French description	string	nvarchar(180) not null

Table ID: 1EB7B128-8A51-4D69-8C78-9ACCA59DD909
Table Name: PATIENT_LIST
Description: This data set contains the test patients eligible for claims submission to the Sandbox
Domain: NA

#	COLUMN NAME	DESCRIPTION	RETURN TYPE	DATABASE TYPE
1	FIRST_NAME	The patient’s first name	string	nvarchar(60)
2	LAST_NAME	The patient’s last name	string	nvarchar(100)
3	DATE_OF_BIRTH	The patient’s date of birth	string	datetime
4	GENDER_CODE	The patient’s sex	string	nvarchar(200)
5	MEDICARE_NUMBER	The patient’s Medicare number	string	bigint

Table ID: 7091D115-8386-40DA-B1B6-7628F6326CF4
Table Name: VERSIONED_PROPERTY_VALUE
Description:
Domain: NA

#	COLUMN NAME	DESCRIPTION	RETURN TYPE	DATABASE TYPE
1	VERSIONED_PROPERTY_CODE	The name/code/description of the versioned property	string	numeric(9,0) not null
2	EFFECTIVE_DATE	Date the versioned property is effective	string	nvarchar(720) not null
3	TERMINATION_DATE	Date the versioned property is terminated	string	nvarchar(720) not null
4	VALUE	Value of the versioned property	string	int