APHDR Data Sets

---

Overview

1. Cover Letter
2. DTMS-CHPPM Atr
3. Provider Data Schema

---

Sheet 1: 1. Cover Letter

This tab, 1.Cover Letter, descripes the information in the other 2 tabs in this file.
Tab		Description
2. DTMS-CHPPM Atr.		Report on the attributes the CHPPM selected. Explaination of the report column heads appears below, titled Column Definitions.
3. Provider Data Schema		Mapping of data elements from the 9 DTMS source tables to the 4 tables that will be provided.
	Column Definitions
Column	Name	Description
A	Source	Name of Data Source
B	TabOrd	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	FieldOrd	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	CHPPM (or NIMH) Approval	User has accepted this as a valid element for their use-drop down list-Yes or No
G	CHPPM (or NIMH) SP2Delta	Date the data element requested in Spiral-2. This will help in revising your existing extract routines
H	FieldType	This is the data type and not populated at this time. It will be whatever you have in the source Tables
I	Nullability	This is the Nullability condition and not populated at this time. It will be determined when your extract is received
J	Primary Key	Indicator if data element is a primary key (PK) or foreign key (FK) in this table
K	Title	Data Element Name
L	Desc	Dictionary meaning of the data element and enumerated values if applicable
M	FDM Comments	Please add any comments that will help us understand the output extract.
N	PII/HIPPA Sensitive	Please note YES or NO if the element is PII or HIPPA sensitive for de-identification
O	ADS Comment	Please add any comments on authoritativeness
P	Code Table Reference	Reference to code table.

---

Sheet 2: 2. DTMS-CHPPM Atr

Source

Source Tab Ord

SourceTable

Source Field Ord

FieldName

CHPPM Approval

CHPPM SP2Delta

FieldType

Nullability

Primary Key

Title

Desc

FDM Comments

PII/HIPPA Sensitive

ADS Comment

Code Table Reference

DTMS

1

tblPerson

1

SSN(4)

Yes

VARCHAR(11)

NO

PK

Unique identifier

Yes

DTMS

1

tblPerson

1

SSN(3)

Yes

VARCHAR(11)

NO

PK

Unique identifier

Yes

DTMS

1

tblPerson

1

SSN(2)

Yes

VARCHAR(11)

NO

PK

Unique identifier

Yes

DTMS

1

tblPerson

1

SSN

Yes

VARCHAR(11)

NO

PK

Unique identifier

Yes

DTMS

2

tblMemberAPFT

1

APFTDate

Yes

DATETIME

NO

PK

Date of APFT test

DTMS

2

tblMemberAPFT

2

ScorePU

Yes

INT

YES

Push up score

DTMS

2

tblMemberAPFT

3

ScoreSU

Yes

INT

YES

Sit up score

DTMS

2

tblMemberAPFT

4

ScoreRun

Yes

INT

YES

Run score

DTMS

3

dtms_tblMemberAPFT

1

ForRecord

Yes

BIT

YES

Is this a record or a diagnostic test

DTMS

4

tblMemberAPFT

1

Comments

Yes

VARCHAR(100)

YES

Comments

Yes

DTMS

5

tblMemberHeightWeight

1

HeightWeightDate

Yes

DATETIME

NO

PK

Date of Height / Weight measurements

DTMS

5

tblMemberHeightWeight

2

Height

Yes

FLOAT

YES

Height

DTMS

5

tblMemberHeightWeight

3

Weight

Yes

FLOAT

YES

Weight

DTMS

5

tblMemberHeightWeight

4

HeightWeightPass

Yes

BIT

YES

Pass or fail

DTMS

5

tblMemberHeightWeight

5

BodyFatPercentage

Yes

FLOAT

YES

Body Fat Percentage

DTMS

5

tblMemberHeightWeight

6

BodyFatPass

Yes

BIT

YES

Pass or fail

DTMS

5

tblMemberHeightWeight

7

Comments

Yes

VARCHAR(250)

YES

Comments

Yes

DTMS

6

tblMemberWeaponSkill

1

QualificationDate

Yes

DATETIME

NO

PK

Date of weapon qualification test

DTMS

7

tblWeaponSkillLevel

1

WeaponSkillLevel

Yes

VARCHAR(50)

YES

Proficiency level

DTMS

8

tblTask

1

TaskNumber

Yes

VARCHAR(11)

YES

Identifier for task

DTMS

8

tblTask

2

TaskTitle

Yes

VARCHAR(250)

NO

Title

DTMS

9

tblPersonTask

1

TaskDate

Yes

DATETIME

NO

PK

Date or scheduled date of training

DTMS

9

tblPersonTask

2

Pass

Yes

BIT

YES

NULL indicates scheduled but not evaluated

NOTE: SSN appears multiple times to account for there specific occurances in a specific provider table.

---

Sheet 3: 3. Provider Data Schema

	Suicide Mitigation Data Schema by Provider for CHPPM
	DTMS - Digital Training Management System									9/25/2009
	Provide Table			FieldName		FieldType	Nullability	Primary Key	Source Ord	SourceTable
1	APTF
		1		SSN		VARCHAR(11)	NO	PK	1. 1	tblPerson
		2		APFTDate		DATETIME	NO	PK	2. 1	tblMemberAPFT
		3		ScorePU		INT	YES		2. 2	tblMemberAPFT
		4		ScoreSU		INT	YES		2. 3	tblMemberAPFT
		5		ScoreRun		INT	YES		2. 4	tblMemberAPFT
		6		ForRecord		BIT	YES		3. 1	dtms_tblMemberAPFT
		7		Comments		VARCHAR(100)	YES		4. 1	tblMemberAPFT
2	HeightWeight
		1		SSN(2)		VARCHAR(11)	NO	PK	1. 1	tblPerson
		2		HeightWeightDate		DATETIME	NO	PK	5. 1	tblMemberHeightWeight
		3		Height		FLOAT	YES		5. 2	tblMemberHeightWeight
		4		Weight		FLOAT	YES		5. 3	tblMemberHeightWeight
		5		HeightWeightPass		BIT	YES		5. 4	tblMemberHeightWeight
		6		BodyFatPercentage		FLOAT	YES		5. 5	tblMemberHeightWeight
		7		BodyFatPass		BIT	YES		5. 6	tblMemberHeightWeight
		8		Comments		VARCHAR(250)	YES		5. 7	tblMemberHeightWeight
3	WeaponSkill
		1		SSN(3)		VARCHAR(11)	NO	PK	1. 1	tblPerson
		2		QualificationDate		DATETIME	NO	PK	6. 1	tblMemberWeaponSkill
		3		WeaponSkillLevel		VARCHAR(50)	YES		7. 1	tblWeaponSkillLevel
4	Training
		1		SSN(4)		VARCHAR(11)	NO	PK	1. 1	tblPerson
		2		TaskNumber		VARCHAR(11)	YES		8. 1	tblTask
		3		TaskTitle		VARCHAR(250)	NO		8. 2	tblTask
		4		TaskDate		DATETIME	NO	PK	9. 1	tblPersonTask
		5		Pass		BIT	YES		9. 2	tblPersonTask
				Total Number of Attributes		23