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

1

dbo.COPS_Violations

1

id_type_value_txt

DI

Yes

12/21/10

varchar(15)

NULL

If type was SSN, this is the actual SSN

COPS

1

dbo.COPS_Violations

2

issuing_installation_id

Yes

12/21/10

int

NULL

Installation for which law enforcement officer works. USAPHC is interested in generic location of where violation occurred, ie the specific Fort

COPS

1

dbo.COPS_Violations

3

incident_dt

Yes

12/21/10

datetime

NULL

Date of the violation

COPS

1

dbo.COPS_Violations

4

incident_time_txt

Yes

12/21/10

varchar(4)

NULL

COPS

1

dbo.COPS_Violations

5

disposition_id

Yes

12/21/10

smallint

NULL

Lookup table associated. - Closed, Dismissed, Failure to appear, Ticket paid, others

COPS

1

dbo.COPS_Violations

6

on_off_post_id

Yes

12/21/10

tinyint

NULL

Yes, No

Y/N value

COPS

1

dbo.COPS_Violations

7

traffic_accident_type_id

Yes

12/21/10

int

NULL

Indicates fatal, vehicle/vehicle, fixed object, etc. Look up table.

COPS

1

dbo.COPS_Violations

8

revocation_id

Yes

12/21/10

int

NULL

Indicates privileges revoked. Can revoke different types of privileges. Revocation ID links to another table with additoinal information - reason why revoked, effective date, time of revocation, reinsteatement. A revocation is based on a violation, whereas a barring is more likely based on something lesser that may not be a crime, where revocation is based on a moving violation. USAPHC is not interested in details of the revocation, just need to know it occured. Assuming that is indicated in disposition.

COPS

1

dbo.COPS_Violations

9

violation_form_type_id

Yes

12/21/10

int

NULL

1408, CVB

Two types - 1408 (warning) CVB (federal magistrate ticket)

COPS

1

dbo.COPS_Violations

10

subject_last_name_txt

Yes

Yes

12/21/10

varchar(50)

NULL

COPS

1

dbo.COPS_Violations

11

subject_first_name_txt

Yes

Yes

12/21/10

varchar(50)

NULL

COPS

1

dbo.COPS_Violations

12

subject_middle_name_txt

Yes

Yes

12/21/10

varchar(50)

NULL

COPS

1

dbo.COPS_Violations

13

subject_suffix_cd

Yes

Yes

12/21/10

varchar(6)

NULL

COPS

1

dbo.COPS_Violations

14

subject_category_id

Yes

12/21/10

int

NULL

Helps define what the grade id is. Categories are Army, Navy, Coast Guard, etc. Then grade is the grades in that category. IE grade is O1, rank is LT.

COPS

1

dbo.COPS_Violations

15

subject_grade_id

DI

Yes

12/21/10

int

NULL

Will need to collapse grade similar to the rank at ADCF for STARRS

COPS

1

dbo.COPS_Violations

16

subject_dob_dt

Yes

12/21/10

datetime

NULL

COPS

1

dbo.COPS_Violations

17

subject_installation_id

Yes

12/21/10

int

NULL

COPS

1

dbo.COPS_Violations

18

subject_uic_id

DI

Yes

12/21/10

int

NULL

UIC Number

COPS

1

dbo.COPS_Violations

19

subject_uic_txt

Yes

Yes

12/21/10

varchar(25)

NULL

Spells out UIC

COPS

1

dbo.COPS_Violations

20

subject_organization_txt

Yes

12/21/10

varchar(100)

NULL

Subject organization is a common field for the various people tracked in the system and refers to the group that provides administrative support to person. Examples are: 18th Airborne Corp, 42nd MP Company

COPS

1

dbo.COPS_Violations

21

vehicle_make_id

Yes

12/21/10

smallint

NULL

COPS

1

dbo.COPS_Violations

22

vehicle_model_txt

Yes

12/21/10

varchar(25)

NULL

COPS

1

dbo.COPS_Violations

23

vehicle_body_style_id

Yes

12/21/10

smallint

NULL

This distinguishes whether it’s a Motorcylce/Moped, Car, etc.

COPS

1

dbo.COPS_Violations

24

adjudication_points_assessed_id

Yes

12/21/10

int

NULL

COPS

1

dbo.COPS_Violations

25

adjudication_dt

Yes

12/21/10

datetime

NULL

COPS

1

dbo.COPS_Violations

26

COPS_Lookup_CmdrAction.cmdr_action_desc_txt

Yes

12/21/10

int

NULL

adjudication_cmdr_action_id is just a number, COPS will provide the data field cmdr_action_desc_txt instead. This will have the actual description of the Commander Action

This is a text field that will contain entries like 'Letter of Reprimand', 'Article 15', 'Verbal Counseling', etc. The name will be changed to adjudication_cmdr_action.

COPS

1

dbo.COPS_Violations

27

adjudication_cmdr_action_dt

Yes

12/21/10

datetime

NULL

COPS

1

dbo.COPS_Violations

28

adjudication_remarks_txt

Yes

Yes

12/21/10

varchar(1000)

NULL

COPS

1

dbo.COPS_Violations

29

adjudication_fine_txt

Yes

12/21/10

varchar(10)

NULL

Dollar amount. If commander gave the soldier a fine, the disposition would be Paid, whether commander or judge required it.

COPS

1

dbo.COPS_Violations

30

warning_cd

Yes

12/21/10

int

NULL

Yes, No

A 1408 is a warning, they are instructed to id it as that. So this is a yes/no.

Warning Code - Yes/No field indicating whether a warning was issued or not

COPS

1

dbo.COPS_Violations

31

pi_type_cd

Yes

12/21/10

int

NULL

Yes, No

Yes/No field indicating whether there was personal injury involved in the accident.

COPS

1

dbo.COPS_Violations

32

court_fine

Yes

12/21/10

varchar(10)

NULL

The matching field that we have in our database in court_fine_txt.

This is the fine imposed by the court and is in addition to any other fines that may have been assessed. Court fines are based on state and federal violations, and adjudication fines are assessed by the commander based on the UCMJ.

COPS

1

dbo.COPS_Violations

33

mpr_number_txt

Yes

12/21/10

varchar(50)

NULL

If part of an MPR case, this is the link to the MPR. If USAPHC and STARRS currently getting the MPR number, this could be useful.

If an MPR has been created for the violation, this is the reference number to access it.