APHDR Data Sets

---

Overview

1.Cover Letter
2.COPS_MPRS-BLANK-USAPHC Atr

---

Sheet 1: 1.Cover Letter

This tab, 1.Cover Letter, descripes the information in the following tab of this document.
Tab		Description
2.COPS_MPRS-BLANK-USAPHC Atr		Report on the elements available for selection. Explaination of the report column heads appears below, titled Column Definitions.
	Column Definitions
Column	Name	Description
A	Source	Name of Data Source
B	SourceTabOrd	Ordinal Position of Table in Data Dictionary received from Source System-used internally
C	SourceTable	Name of the Table from which the data element is extracted from Source System
D	SourceFieldOrd	Ordinal Position of the data element in a Table in the Source System Data Dictionary-used internally
E	FieldName	Name of the data element (please use the literal format as in the column)
F	PII/HIPAA Sensitive	Please note YES, NO or DI (De-Identification)for the element PII or HIPAA sensitivity. If the element is PII or HIPAA sensitive and it will be de-identified, enter DI. A blank enter will be considered the same as a NO.
G	NIMH (or USAPHC(Prov)) Approval	User has accepted this as a valid element for their use-drop down list-Yes or No
H	NIMH (or USAPHC(Prov)) SP2Delta	Date the data element requested in Spiral-2. This will help in revising your existing extract routines
I	FieldType	This is the data type.
J	Nullability	This is the Nullability condition
K	Primary Key	Indicator if data element is a primary key (PK) or foreign key (FK) in this table
L	Title	Data Element Name
M	Desc	Dictionary meaning of the data element and enumerated values if applicable
N	Values	Information on values the element may have
O	Notes	Notes concerning the element
P	FDM Comments	Please add any comments that will help us understand the output extract.
Q	ADS Comment	Please add any comments on authoritativeness
R	Code Table Reference	This field is a concatenation of 4 fields supplied by COPS, containing information on certain lookup tables. The 4 fields are: Code Table Reference, Lookup Element/Primary Key, Lookup Value Element, and Lookup Value. These fields are separated by ";"s.
Note: Please see the 6 attached xlsx files titled "COPS_Lookup_Country", "COPS_Lookup_Grade_Rank", "COPS_Lookup_Offense","COPS_Lookup_States", "COPS_Lookup_UIC", and "MPRS_Lookup_DIBRS_Location" for detailed information on these code tables.

---

Sheet 2: 2.COPS_MPRS-BLANK-USAPHC Atr

Source

Source Tab Ord

SourceTable

Source Field Ord

FieldName

PII/HIPAA Sensitive

USAPHC (Prov) Approval

USAPHC (Prov) SP2Delta

FieldType

Nullability

Primary Key

Title

Desc

Values

Notes

FDM Comments

ADS Comment

Code Table Reference

COPS/VRS

dbo.COPS_Violations

1

violation_id

int

NOT NULL

COPS/VRS

dbo.COPS_Violations

2

violation_number_txt

varchar(25)

NOT NULL

COPS/VRS

dbo.COPS_Violations

3

issuing_installation_id

Y

int

NULL

COPS/VRS

dbo.COPS_Violations

4

issuing_officer_id

int

NULL

COPS/VRS

dbo.COPS_Violations

5

issuing_dt

datetime

NULL

COPS/VRS

dbo.COPS_Violations

6

issuing_time_txt

varchar(4)

NULL

COPS/VRS

dbo.COPS_Violations

7

incident_dt

Y

datetime

NULL

COPS/VRS

dbo.COPS_Violations

8

incident_time_txt

Y

varchar(4)

NULL

COPS/VRS

dbo.COPS_Violations

9

disposition_id

Y

smallint

NULL

COPS/VRS

dbo.COPS_Violations

10

on_off_post_id

Y

tinyint

NULL

COPS/VRS

dbo.COPS_Violations

11

location_id

int

NULL

COPS/VRS

dbo.COPS_Violations

12

location_cd_txt

varchar(200)

NULL

COPS/VRS

dbo.COPS_Violations

13

cross_location_id

int

NULL

COPS/VRS

dbo.COPS_Violations

14

cross_location_txt

varchar(200)

NULL

COPS/VRS

dbo.COPS_Violations

15

slippery_pavement_id

int

NULL

COPS/VRS

dbo.COPS_Violations

16

darkness_id

int

NULL

COPS/VRS

dbo.COPS_Violations

17

other_traffic_id

int

NULL

COPS/VRS

dbo.COPS_Violations

18

dodge_id

int

NULL

COPS/VRS

dbo.COPS_Violations

19

area_id

int

NULL

COPS/VRS

dbo.COPS_Violations

20

highway_type_id

int

NULL

COPS/VRS

dbo.COPS_Violations

21

traffic_accident_type_id

Y

int

NULL

COPS/VRS

dbo.COPS_Violations

22

revocation_id

int

NULL

COPS/VRS

dbo.COPS_Violations

23

violation_form_type_id

Y

int

NULL

COPS/VRS

dbo.COPS_Violations

24

id_type_id

int

NULL

COPS/VRS

dbo.COPS_Violations

25

id_type_value_txt

Y

Y

varchar(15)

NULL

COPS/VRS

dbo.COPS_Violations

26

subject_last_name_txt

Y

varchar(50)

NULL

COPS/VRS

dbo.COPS_Violations

27

subject_first_name_txt

Y

varchar(50)

NULL

COPS/VRS

dbo.COPS_Violations

28

subject_middle_name_txt

Y

varchar(50)

NULL

COPS/VRS

dbo.COPS_Violations

29

subject_suffix_cd

Y

varchar(6)

NULL

COPS/VRS

dbo.COPS_Violations

30

subject_category_id

Y

int

NULL

COPS/VRS

dbo.COPS_Violations

31

subject_grade_id

Y

Y

int

NULL

COPS/VRS

dbo.COPS_Violations

32

subject_dob_dt

Y

datetime

NULL

COPS/VRS

dbo.COPS_Violations

33

subject_installation_id

Y

int

NULL

COPS/VRS

dbo.COPS_Violations

34

subject_uic_id

Y

Y

int

NULL

COPS/VRS

dbo.COPS_Violations

35

subject_uic_txt

Y

Y

varchar(25)

NULL

COPS/VRS

dbo.COPS_Violations

36

subject_organization_txt

Y

Y

varchar(100)

NULL

COPS/VRS

dbo.COPS_Violations

37

subject_driver_license_txt

varchar(25)

NULL

COPS/VRS

dbo.COPS_Violations

38

subject_driver_license_state_id

smallint

NULL

COPS/VRS

dbo.COPS_Violations

39

subject_driver_license_country_id

int

NULL

COPS/VRS

dbo.COPS_Violations

40

subject_address1_txt

varchar(100)

NULL

COPS/VRS

dbo.COPS_Violations

41

subject_address2_txt

varchar(100)

NULL

COPS/VRS

dbo.COPS_Violations

42

subject_city_txt

varchar(50)

NULL

COPS/VRS

dbo.COPS_Violations

43

subject_state_id

smallint

NULL

COPS/VRS

dbo.COPS_Violations

44

subject_zip_txt

varchar(15)

NULL

COPS/VRS

dbo.COPS_Violations

45

subject_country_id

int

NULL

COPS/VRS

dbo.COPS_Violations

46

subject_home_phone_txt

varchar(25)

NULL

COPS/VRS

dbo.COPS_Violations

47

vehicle_make_id

Y

smallint

NULL

COPS/VRS

dbo.COPS_Violations

48

vehicle_model_txt

Y

varchar(25)

NULL

COPS/VRS

dbo.COPS_Violations

49

vehicle_body_style_id

Y

smallint

NULL

COPS/VRS

dbo.COPS_Violations

50

vehicle_color_txt

varchar(25)

NULL

COPS/VRS

dbo.COPS_Violations

51

vehicle_year_txt

varchar(4)

NULL

COPS/VRS

dbo.COPS_Violations

52

vehicle_vin_txt

varchar(25)

NULL

COPS/VRS

dbo.COPS_Violations

53

vehicle_tag_number_txt

char(18)

NULL

COPS/VRS

dbo.COPS_Violations

54

vehicle_tag_state_id

smallint

NULL

COPS/VRS

dbo.COPS_Violations

55

vehicle_tag_country_id

int

NULL

COPS/VRS

dbo.COPS_Violations

56

vehicle_decal_number_txt

varchar(10)

NULL

COPS/VRS

dbo.COPS_Violations

57

adjudication_points_assessed_id

Y

int

NULL

COPS/VRS

dbo.COPS_Violations

58

adjudication_dt

Y

datetime

NULL

COPS/VRS

dbo.COPS_Violations

59

adjudication_cmdr_action_id

Y

int

NULL

COPS/VRS

dbo.COPS_Violations

60

adjudication_cmdr_action_dt

Y

datetime

NULL

COPS/VRS

dbo.COPS_Violations

61

adjudication_remarks_txt

Y

Y

varchar(1000)

NULL

COPS/VRS

dbo.COPS_Violations

62

adjudication_fine_txt

Y

varchar(10)

NULL

COPS/VRS

dbo.COPS_Violations

63

adjudication_suspense1_dt

datetime

NULL

COPS/VRS

dbo.COPS_Violations

64

adjudication_suspense2_dt

datetime

NULL

COPS/VRS

dbo.COPS_Violations

65

adjudication_suspense3_dt

datetime

NULL

COPS/VRS

dbo.COPS_Violations

66

condition_remarks_txt

varchar(1000)

NULL

COPS/VRS

dbo.COPS_Violations

67

organization_txt

varchar(50)

NULL

COPS/VRS

dbo.COPS_Violations

68

pd_type_cd

?

int

NULL

COPS/VRS

dbo.COPS_Violations

69

warning_cd

Y

int

NULL

COPS/VRS

dbo.COPS_Violations

70

pi_type_cd

?

int

NULL

COPS/VRS

dbo.COPS_Violations

71

mandatory_cd

Y

int

NULL

COPS/VRS

dbo.COPS_Violations

72

court_date_dt

Y

datetime

NULL

COPS/VRS

dbo.COPS_Violations

73

court_fine_txt

Y

varchar(10)

NULL

COPS/VRS

dbo.COPS_Violations

74

mpr_number_txt

?

varchar(50)

NULL

Comments on Registration Data

COPS/VRS

1

dbo.VRS_Applicant_Installation

1

applicant_id

Yes

??

numeric(5,0)

NOT NULL

applicant_id

COPS/VRS

1

dbo.VRS_Applicant_Installation

2

installation_id

??

numeric(5,0)

NOT NULL

installation_id

COPS/VRS

2

dbo.VRS_Barred_List

1

Barred_id

??

decimal(18,0)

NOT NULL

Barred_id

COPS/VRS

2

dbo.VRS_Barred_List

2

registrant_number_id

Yes

decimal(18,0)

NOT NULL

registrant_number_id

COPS/VRS

2

dbo.VRS_Barred_List

3

reason_id

Yes

int

NOT NULL

reason_id

COPS/VRS

2

dbo.VRS_Barred_List

4

barring_installation_id

Yes

decimal(18,0)

NOT NULL

barring_installation_id

COPS/VRS

2

dbo.VRS_Barred_List

5

effective_dt

Yes

datetime

NOT NULL

effective_dt

COPS/VRS

2

dbo.VRS_Barred_List

6

termination_dt

Yes

datetime

NOT NULL

termination_dt

COPS/VRS

2

dbo.VRS_Barred_List

7

poc_name_txt

Yes

varchar(100)

NOT NULL

poc_name_txt

COPS/VRS

2

dbo.VRS_Barred_List

8

poc_comm_txt

Yes

varchar(25)

NOT NULL

poc_comm_txt

COPS/VRS

2

dbo.VRS_Barred_List

9

poc_dsn_txt

Yes

varchar(25)

NOT NULL

poc_dsn_txt

COPS/VRS

2

dbo.VRS_Barred_List

10

mpr_txt

varchar(25)

NOT NULL

mpr_txt

COPS/VRS

2

dbo.VRS_Barred_List

11

limited_bln

tinyint

NOT NULL

limited_bln

COPS/VRS

2

dbo.VRS_Barred_List

12

limited_to_txt

varchar(50)

NOT NULL

limited_to_txt

COPS/VRS

3

dbo.VRS_Functional_Area_Permissions

1

func_area_id

smallint

NOT NULL

func_area_id

COPS/VRS

3

dbo.VRS_Functional_Area_Permissions

2

permission_id

smallint

NOT NULL

permission_id

COPS/VRS

8

dbo.VRS_Lookup_Decal_Replacement_Reason

1

replacement_reason_id

numeric(18,0)

IDENTITY

replacement_reason_id

COPS/VRS

8

dbo.VRS_Lookup_Decal_Replacement_Reason

2

replacement_reason_txt

varchar(50)

NULL

replacement_reason_txt

COPS/VRS

8

dbo.VRS_Lookup_Decal_Replacement_Reason

3

deleted_bln

tinyint

NULL

deleted_bln

DEFAULT 0

COPS/VRS

9

dbo.VRS_Lookup_Decal_Returned_Reason

1

returned_reason_id

int

IDENTITY

returned_reason_id

COPS/VRS

9

dbo.VRS_Lookup_Decal_Returned_Reason

2

returned_reason_txt

varchar(50)

NOT NULL

returned_reason_txt

COPS/VRS

9

dbo.VRS_Lookup_Decal_Returned_Reason

3

deleted_bln

tinyint

NOT NULL

deleted_bln

DEFAULT 0

COPS/VRS

10

dbo.VRS_Lookup_Functional_Areas

1

func_area_id

smallint

NOT NULL

func_area_id

COPS/VRS

10

dbo.VRS_Lookup_Functional_Areas

2

func_area_txt

varchar(50)

NOT NULL

func_area_txt

COPS/VRS

11

dbo.VRS_Lookup_Image_Type

1

image_type_id

int

IDENTITY

image_type_id

COPS/VRS

11

dbo.VRS_Lookup_Image_Type

2

image_type_txt

char(25)

NULL

image_type_txt

COPS/VRS

17

dbo.VRS_Lookup_Reports

1

report_id

??

smallint

NOT NULL

report_id

COPS/VRS

17

dbo.VRS_Lookup_Reports

2

report_name_txt

??

varchar(50)

NOT NULL

report_name_txt

COPS/VRS

17

dbo.VRS_Lookup_Reports

3

report_desc_txt

??

varchar(50)

NULL

report_desc_txt

COPS/VRS

17

dbo.VRS_Lookup_Reports

4

report_url

??

varchar(100)

NULL

report_url

COPS/VRS

17

dbo.VRS_Lookup_Reports

5

type_id

??

smallint

NOT NULL

type_id

COPS/VRS

17

dbo.VRS_Lookup_Reports

6

report_search_bln

??

int

NOT NULL,

report_search_bln

DEFAULT 1

COPS/VRS

18

dbo.VRS_Lookup_Reports_Criteria

1

criteria_id

??

smallint

NOT NULL

criteria_id

COPS/VRS

18

dbo.VRS_Lookup_Reports_Criteria

2

criteria_label_txt

??

varchar(50)

NOT NULL

criteria_label_txt

COPS/VRS

18

dbo.VRS_Lookup_Reports_Criteria

3

criteria_html_txt

??

varchar(7500)

NOT NULL

criteria_html_txt

COPS/VRS

22

dbo.VRS_Lookup_Vehicle_Status

1

Vehicle_Status_id

Yes

smallint

IDENTITY

Vehicle_Status_id

COPS/VRS

22

dbo.VRS_Lookup_Vehicle_Status

2

Vehicle_Status_txt

Yes

char(15)

NOT NULL

Vehicle_Status_txt

COPS/VRS

26

dbo.VRS_Registered_Decals

1

decal_id

int

IDENTITY

decal_id

COPS/VRS

26

dbo.VRS_Registered_Decals

2

registrant_number_id

int

NULL

registrant_number_id

COPS/VRS

26

dbo.VRS_Registered_Decals

3

installation_id

int

NULL

installation_id

COPS/VRS

26

dbo.VRS_Registered_Decals

4

vehicle_id

int

NULL

vehicle_id

COPS/VRS

26

dbo.VRS_Registered_Decals

5

vehicle_year_num

int

NULL

vehicle_year_num

COPS/VRS

26

dbo.VRS_Registered_Decals

6

vehicle_make_id

int

NULL

vehicle_make_id

COPS/VRS

26

dbo.VRS_Registered_Decals

7

vehicle_model_txt

varchar(20)

NULL

vehicle_model_txt

COPS/VRS

26

dbo.VRS_Registered_Decals

8

vehicle_decal_number_txt

varchar(7)

NULL

vehicle_decal_number_txt

COPS/VRS

26

dbo.VRS_Registered_Decals

9

vehicle_decal_replacement_reason_id

int

NULL

vehicle_decal_replacement_reason_id

COPS/VRS

26

dbo.VRS_Registered_Decals

10

vehicle_decal_updated_dt

datetime

NULL

vehicle_decal_updated_dt

COPS/VRS

26

dbo.VRS_Registered_Decals

11

vehicle_decal_updated_by_user_id

int

NULL

vehicle_decal_updated_by_user_id

COPS/VRS

26

dbo.VRS_Registered_Decals

12

recovered_bln

int

NULL

recovered_bln

COPS/VRS

26

dbo.VRS_Registered_Decals

13

vehicle_decal_returned_reason_id

int

NULL

vehicle_decal_returned_reason_id

DEFAULT 0

COPS/VRS

27

dbo.VRS_Registered_Images

1

image_id

numeric(18,0)

IDENTITY

image_id

COPS/VRS

27

dbo.VRS_Registered_Images

2

registrant_number_id

numeric(18,0)

NOT NULL

registrant_number_id

COPS/VRS

27

dbo.VRS_Registered_Images

3

image_img

image

NULL

image_img

COPS/VRS

27

dbo.VRS_Registered_Images

4

image_type_id

int

NULL

image_type_id

COPS/VRS

27

dbo.VRS_Registered_Images

5

image_description_txt

varchar(100)

NULL

image_description_txt

COPS/VRS

27

dbo.VRS_Registered_Images

6

image_created_dt

datetime

NULL

image_created_dt

COPS/VRS

27

dbo.VRS_Registered_Images

7

image_created_id

int

NULL

image_created_id

COPS/VRS

29

dbo.VRS_Registered_Personnel

1

registrant_number_id

Yes

int

IDENTITY

registrant_number_id

COPS/VRS

29

dbo.VRS_Registered_Personnel

2

registrant_installation_id

Yes

int

NULL

registrant_installation_id

COPS/VRS

29

dbo.VRS_Registered_Personnel

3

registrant_id_type_id

Yes

int

NULL

registrant_id_type_id

COPS/VRS

29

dbo.VRS_Registered_Personnel

4

registrant_id_value_txt

Yes

Yes

varchar(25)

NULL

registrant_id_value_txt

COPS/VRS

29

dbo.VRS_Registered_Personnel

5

registrant_last_name_txt

Yes

Yes

varchar(50)

NULL

registrant_last_name_txt

COPS/VRS

29

dbo.VRS_Registered_Personnel

6

registrant_first_name_txt

Yes

Yes

varchar(50)

NULL

registrant_first_name_txt

COPS/VRS

29

dbo.VRS_Registered_Personnel

7

registrant_middle_name_txt

Yes

Yes

varchar(50)

NULL

registrant_middle_name_txt

COPS/VRS

29

dbo.VRS_Registered_Personnel

8

registrant_suffix_cd

Yes

varchar(6)

NULL

registrant_suffix_cd

COPS/VRS

29

dbo.VRS_Registered_Personnel

9

registrant_operator_number_txt

varchar(25)

NULL

registrant_operator_number_txt

COPS/VRS

29

dbo.VRS_Registered_Personnel

10

registrant_operator_state_id

int

NULL

registrant_operator_state_id

COPS/VRS

29

dbo.VRS_Registered_Personnel

11

registrant_category_id

int

NULL

registrant_category_id

COPS/VRS

29

dbo.VRS_Registered_Personnel

12

registrant_grade_rank_id

DI

Yes

int

NULL

registrant_grade_rank_id

COPS/VRS

29

dbo.VRS_Registered_Personnel

13

registrant_birth_dt

Yes

datetime

NULL

registrant_birth_dt

COPS/VRS

29

dbo.VRS_Registered_Personnel

14

registrant_ets_dt

datetime

NULL

registrant_ets_dt

COPS/VRS

29

dbo.VRS_Registered_Personnel

15

registrant_component_id

int

NULL

registrant_component_id

COPS/VRS

29

dbo.VRS_Registered_Personnel

16

registrant_gender_id

int

NULL

registrant_gender_id

COPS/VRS

29

dbo.VRS_Registered_Personnel

17

registrant_height_txt

varchar(10)

NULL

registrant_height_txt

COPS/VRS

29

dbo.VRS_Registered_Personnel

18

registrant_weight_txt

varchar(5)

NULL

registrant_weight_txt

COPS/VRS

29

dbo.VRS_Registered_Personnel

19

registrant_eye_color_id

int

NULL

registrant_eye_color_id

COPS/VRS

29

dbo.VRS_Registered_Personnel

20

registrant_hair_color_id

int

NULL

registrant_hair_color_id

COPS/VRS

29

dbo.VRS_Registered_Personnel

21

registrant_marital_status_id

int

NULL

registrant_marital_status_id

COPS/VRS

29

dbo.VRS_Registered_Personnel

22

registrant_ethnicity_id

int

NULL

registrant_ethnicity_id

COPS/VRS

29

dbo.VRS_Registered_Personnel

23

registrant_race_id

int

NULL

registrant_race_id

COPS/VRS

29

dbo.VRS_Registered_Personnel

24

registrant_home_address_txt

Yes

varchar(100)

NULL

registrant_home_address_txt

COPS/VRS

29

dbo.VRS_Registered_Personnel

25

registrant_home_city_txt

varchar(50)

NULL

registrant_home_city_txt

COPS/VRS

29

dbo.VRS_Registered_Personnel

26

registrant_home_state_id

int

NULL

registrant_home_state_id

COPS/VRS

29

dbo.VRS_Registered_Personnel

27

registrant_home_zipcode_txt

varchar(15)

NULL

registrant_home_zipcode_txt

COPS/VRS

29

dbo.VRS_Registered_Personnel

28

registrant_home_country_id

int

NULL

registrant_home_country_id

COPS/VRS

29

dbo.VRS_Registered_Personnel

29

registrant_home_phone_txt

Yes

varchar(15)

NULL

registrant_home_phone_txt

COPS/VRS

29

dbo.VRS_Registered_Personnel

30

registrant_uic_id

DI

Yes

int

NULL

registrant_uic_id

COPS/VRS

29

dbo.VRS_Registered_Personnel

31

registrant_unit_address_txt

Yes

varchar(100)

NULL

registrant_unit_address_txt

COPS/VRS

29

dbo.VRS_Registered_Personnel

32

registrant_unit_city_txt

varchar(50)

NULL

registrant_unit_city_txt

COPS/VRS

29

dbo.VRS_Registered_Personnel

33

registrant_unit_state_id

int

NULL

registrant_unit_state_id

COPS/VRS

29

dbo.VRS_Registered_Personnel

34

registrant_unit_zipcode_txt

varchar(15)

NULL

registrant_unit_zipcode_txt

COPS/VRS

29

dbo.VRS_Registered_Personnel

35

registrant_unit_country_id

int

NULL

registrant_unit_country_id

COPS/VRS

29

dbo.VRS_Registered_Personnel

36

registrant_unit_phone_txt

Yes

varchar(25)

NULL

registrant_unit_phone_txt

COPS/VRS

29

dbo.VRS_Registered_Personnel

37

registrant_unit_phone_ext_txt

Yes

varchar(10)

NULL

registrant_unit_phone_ext_txt

COPS/VRS

29

dbo.VRS_Registered_Personnel

38

registrant_remarks_txt

Yes

varchar(100)

NULL

registrant_remarks_txt

COPS/VRS

29

dbo.VRS_Registered_Personnel

39

registrant_sponsor_id_value_id

DI

int

NULL

registrant_sponsor_id_value_id

COPS/VRS

29

dbo.VRS_Registered_Personnel

40

registrant_sponsor_id_value_txt

Yes

varchar(15)

NULL

registrant_sponsor_id_value_txt

COPS/VRS

29

dbo.VRS_Registered_Personnel

41

registrant_sponsor_last_name_txt

Yes

varchar(50)

NULL

registrant_sponsor_last_name_txt

COPS/VRS

29

dbo.VRS_Registered_Personnel

42

registrant_sponsor_first_name_txt

Yes

varchar(50)

NULL

registrant_sponsor_first_name_txt

COPS/VRS

29

dbo.VRS_Registered_Personnel

43

registrant_sponsor_relationship_id

int

NULL

registrant_sponsor_relationship_id

COPS/VRS

29

dbo.VRS_Registered_Personnel

44

registrant_sponsor_home_phone_txt

Yes

varchar(25)

NULL

registrant_sponsor_home_phone_txt

COPS/VRS

29

dbo.VRS_Registered_Personnel

45

registrant_created_dt

datetime

NULL

registrant_created_dt

COPS/VRS

29

dbo.VRS_Registered_Personnel

46

registrant_created_by_user_id

int

NULL

registrant_created_by_user_id

COPS/VRS

29

dbo.VRS_Registered_Personnel

47

registrant_last_updated_id

int

NULL

registrant_last_updated_id

COPS/VRS

29

dbo.VRS_Registered_Personnel

48

registrant_last_updated_dt

datetime

NULL

registrant_last_updated_dt

COPS/VRS

29

dbo.VRS_Registered_Personnel

49

registrant_sponsor_organization_id

Yes

int

NULL

registrant_sponsor_organization_id

COPS/VRS

29

dbo.VRS_Registered_Personnel

50

registrant_security_group_txt

varchar(10)

NULL

registrant_security_group_txt

COPS/VRS

29

dbo.VRS_Registered_Personnel

51

registrant_sponsor_deceased_bln

tinyint

NULL

registrant_sponsor_deceased_bln

COPS/VRS

33

dbo.VRS_Registered_Vehicles

1

vehicle_id

int

IDENTITY

vehicle_id

COPS/VRS

33

dbo.VRS_Registered_Vehicles

2

registrant_number_id

??

int

NOT NULL

registrant_number_id

COPS/VRS

33

dbo.VRS_Registered_Vehicles

3

vehicle_vin_txt

varchar(30)

NULL

vehicle_vin_txt

COPS/VRS

33

dbo.VRS_Registered_Vehicles

4

vehicle_decal_number_txt

varchar(7)

NULL

vehicle_decal_number_txt

COPS/VRS

33

dbo.VRS_Registered_Vehicles

5

vehicle_decal_expiration_dt

datetime

NULL

vehicle_decal_expiration_dt

COPS/VRS

33

dbo.VRS_Registered_Vehicles

6

vehicle_registration_dt

datetime

NULL

vehicle_registration_dt

COPS/VRS

33

dbo.VRS_Registered_Vehicles

7

vehicle_year_num

int

NULL

vehicle_year_num

COPS/VRS

33

dbo.VRS_Registered_Vehicles

8

vehicle_make_id

??

int

NULL

vehicle_make_id

COPS/VRS

33

dbo.VRS_Registered_Vehicles

9

vehicle_model_txt

??

varchar(20)

NULL

vehicle_model_txt

COPS/VRS

33

dbo.VRS_Registered_Vehicles

10

vehicle_body_style_id

int

NULL

vehicle_body_style_id

COPS/VRS

33

dbo.VRS_Registered_Vehicles

11

vehicle_color_txt

varchar(20)

NULL

vehicle_color_txt

COPS/VRS

33

dbo.VRS_Registered_Vehicles

12

vehicle_license_state_id

int

NULL

vehicle_license_state_id

COPS/VRS

33

dbo.VRS_Registered_Vehicles

13

vehicle_license_country_id

int

NULL

vehicle_license_country_id

COPS/VRS

33

dbo.VRS_Registered_Vehicles

14

vehicle_license_number_txt

Yes

varchar(15)

NULL

vehicle_license_number_txt

COPS/VRS

33

dbo.VRS_Registered_Vehicles

15

vehicle_license_year_num

int

NULL

vehicle_license_year_num

COPS/VRS

33

dbo.VRS_Registered_Vehicles

16

vehicle_signature_dt

datetime

NULL

vehicle_signature_dt

COPS/VRS

33

dbo.VRS_Registered_Vehicles

17

vehicle_comments_txt

Yes

varchar(1000)

NULL

vehicle_comments_txt

COPS/VRS

33

dbo.VRS_Registered_Vehicles

18

vehicle_status_id

??

int

NULL

vehicle_status_id

COPS/VRS

33

dbo.VRS_Registered_Vehicles

19

vehicle_state_emissions_bln

tinyint

NULL

vehicle_state_emissions_bln

COPS/VRS

33

dbo.VRS_Registered_Vehicles

20

vehicle_insurance_req_bln

tinyint

NULL

vehicle_insurance_req_bln

COPS/VRS

33

dbo.VRS_Registered_Vehicles

21

vehicle_insurance_company_name_txt

varchar(30)

NULL

vehicle_insurance_company_name_txt

COPS/VRS

33

dbo.VRS_Registered_Vehicles

22

vehicle_insurance_policy_num_txt

Yes

varchar(30)

NULL

vehicle_insurance_policy_num_txt

COPS/VRS

33

dbo.VRS_Registered_Vehicles

23

vehicle_insured_policy_expiration_dt

datetime

NULL

vehicle_insured_policy_expiration_dt

COPS/VRS

33

dbo.VRS_Registered_Vehicles

24

vehicle_rfid_number_txt

varchar(30)

NULL

vehicle_rfid_number_txt

COPS/VRS

33

dbo.VRS_Registered_Vehicles

25

vehicle_created_dt

datetime

NULL

vehicle_created_dt

COPS/VRS

33

dbo.VRS_Registered_Vehicles

26

vehicle_updated_dt

datetime

NULL

vehicle_updated_dt

COPS/VRS

33

dbo.VRS_Registered_Vehicles

27

vehicle_created_by_userid

int

NULL

vehicle_created_by_userid

COPS/VRS

33

dbo.VRS_Registered_Vehicles

28

vehicle_last_updated_userid

int

NULL

vehicle_last_updated_userid

COPS/VRS

37

dbo.VRS_Report_Adhoc

1

report_adhoc_Registrant_bln

tinyint

NOT NULL

report_adhoc_Registrant_bln

COPS/VRS

37

dbo.VRS_Report_Adhoc

2

report_adhoc_Vehicle_bln

tinyint

NOT NULL

report_adhoc_Vehicle_bln

COPS/VRS

37

dbo.VRS_Report_Adhoc

6

report_adhoc_join_txt

varchar(1500)

NOT NULL

report_adhoc_join_txt

COPS/VRS

38

dbo.VRS_Report_Adhoc_saved_queries

1

query_id

numeric(18,0)

IDENTITY

query_id

COPS/VRS

38

dbo.VRS_Report_Adhoc_saved_queries

2

user_id

Yes

numeric(18,0)

NULL

user_id

COPS/VRS

38

dbo.VRS_Report_Adhoc_saved_queries

3

query_name_txt

varchar(50)

NULL

query_name_txt

COPS/VRS

38

dbo.VRS_Report_Adhoc_saved_queries

4

query_txt

text

NULL

query_txt

COPS/VRS

39

dbo.VRS_Reports_Criteria

1

report_id

numeric(18,0)

NOT NULL

report_id

COPS/VRS

39

dbo.VRS_Reports_Criteria

2

criteria_id

numeric(18,0)

NOT NULL

criteria_id

COPS/VRS

41

dbo.VRS_User_Installation

1

user_id

int

NOT NULL

user_id

COPS/VRS

41

dbo.VRS_User_Installation

2

installation_id

int

NOT NULL

installation_id

COPS/VRS

41

dbo.VRS_User_Installation

3

last_updated_user_id

int

NULL

last_updated_user_id

COPS/VRS

43

dbo.VRS_User_Permissions

1

user_id

int

NOT NULL

user_id

COPS/VRS

43

dbo.VRS_User_Permissions

2

func_area_id

int

NOT NULL

func_area_id

COPS/VRS

43

dbo.VRS_User_Permissions

3

permission_id

int

NULL

permission_id

COPS/VRS

43

dbo.VRS_User_Permissions

4

last_updated_user_id

int

NULL

last_updated_user_id

COPS/VRS

40

dbo.VRS_Temporary_Passes

1

pass_id

int

IDENTITY

pass_id

COPS/VRS

40

dbo.VRS_Temporary_Passes

2

pass_num_txt

varchar(15)

NOT NULL

pass_num_txt

COPS/VRS

40

dbo.VRS_Temporary_Passes

3

registrant_installation_id

smallint

NULL

registrant_installation_id

COPS/VRS

40

dbo.VRS_Temporary_Passes

4

issuing_installation_id

smallint

NULL

issuing_installation_id

COPS/VRS

40

dbo.VRS_Temporary_Passes

5

registrant_number_id

int

NULL

registrant_number_id

COPS/VRS

40

dbo.VRS_Temporary_Passes

6

ssn_txt

DI

varchar(9)

NULL

ssn_txt

COPS/VRS

40

dbo.VRS_Temporary_Passes

7

last_name_txt

Yes

varchar(50)

NOT NULL

last_name_txt

COPS/VRS

40

dbo.VRS_Temporary_Passes

8

first_name_txt

Yes

varchar(50)

NOT NULL

first_name_txt

COPS/VRS

40

dbo.VRS_Temporary_Passes

9

middle_name_txt

Yes

varchar(50)

NULL

middle_name_txt

COPS/VRS

40

dbo.VRS_Temporary_Passes

10

vehicle_VIN_txt

varchar(30)

NULL

vehicle_VIN_txt

COPS/VRS

40

dbo.VRS_Temporary_Passes

11

vehicle_year_num

int

NULL

vehicle_year_num

COPS/VRS

40

dbo.VRS_Temporary_Passes

12

vehicle_make_id

int

NULL

vehicle_make_id

COPS/VRS

40

dbo.VRS_Temporary_Passes

13

vehicle_model_txt

varchar(20)

NULL

vehicle_model_txt

COPS/VRS

40

dbo.VRS_Temporary_Passes

14

vehicle_body_style_id

int

NULL

vehicle_body_style_id

COPS/VRS

40

dbo.VRS_Temporary_Passes

15

vehicle_color_txt

varchar(20)

NULL

vehicle_color_txt

COPS/VRS

40

dbo.VRS_Temporary_Passes

16

vehicle_license_number_txt

Yes

varchar(20)

NULL

vehicle_license_number_txt

COPS/VRS

40

dbo.VRS_Temporary_Passes

17

vehicle_license_state_id

int

NULL

vehicle_license_state_id

COPS/VRS

40

dbo.VRS_Temporary_Passes

18

vehicle_license_year_num

int

NULL

vehicle_license_year_num

COPS/VRS

40

dbo.VRS_Temporary_Passes

19

start_dt

datetime

NULL

start_dt

COPS/VRS

40

dbo.VRS_Temporary_Passes

20

destination_txt

varchar(50)

NULL

destination_txt

COPS/VRS

40

dbo.VRS_Temporary_Passes

21

no_of_days_num

int

NULL

no_of_days_num

COPS/VRS

40

dbo.VRS_Temporary_Passes

22

approved_by_txt

Yes

varchar(25)

NULL

approved_by_txt

COPS/VRS

40

dbo.VRS_Temporary_Passes

23

expired_dt

datetime

NULL

expired_dt

COPS/VRS

40

dbo.VRS_Temporary_Passes

24

operator_number_txt

Yes

varchar(25)

NULL

operator_number_txt

COPS/VRS

40

dbo.VRS_Temporary_Passes

25

operator_state_id

int

NULL

operator_state_id

COPS/VRS

40

dbo.VRS_Temporary_Passes

26

operator_country_id

int

NULL

operator_country_id

COPS/VRS

40

dbo.VRS_Temporary_Passes

27

birth_dt

datetime

NULL

birth_dt

COPS/VRS

40

dbo.VRS_Temporary_Passes

28

race_id

int

NULL

race_id

COPS/VRS

40

dbo.VRS_Temporary_Passes

29

ethnicity_id

int

NULL

ethnicity_id

COPS/VRS

40

dbo.VRS_Temporary_Passes

30

gender_id

int

NULL

gender_id

COPS/VRS

40

dbo.VRS_Temporary_Passes

31

height_txt

varchar(15)

NULL

height_txt

COPS/VRS

40

dbo.VRS_Temporary_Passes

32

weight_txt

varchar(15)

NULL

weight_txt

COPS/VRS

40

dbo.VRS_Temporary_Passes

33

eye_color_id

int

NULL

eye_color_id

COPS/VRS

40

dbo.VRS_Temporary_Passes

34

hair_color_id

int

NULL

hair_color_id

COPS/VRS

40

dbo.VRS_Temporary_Passes

35

poc_name_txt

Yes

varchar(40)

NULL

poc_name_txt

COPS/VRS

40

dbo.VRS_Temporary_Passes

36

poc_phone_number_txt

Yes

varchar(25)

NULL

poc_phone_number_txt

COPS/VRS

40

dbo.VRS_Temporary_Passes

37

visitor_type_txt

varchar(30)

NULL

visitor_type_txt

COPS/VRS

40

dbo.VRS_Temporary_Passes

38

additional_drivers_txt

Yes

varchar(500)

NULL

additional_drivers_txt

COPS/VRS

40

dbo.VRS_Temporary_Passes

39

rank_txt

DI

varchar(25)

NULL

rank_txt

COPS/VRS

40

dbo.VRS_Temporary_Passes

40

suffix_cd

Yes

varchar(6)

NULL

suffix_cd

Key:

Original Request

Removed Items

Added Items