2.19 Database Changes

Contents ShowHide

Changes made to the SPIRIT WIC database in this release of the software are detailed below.

ENH-5

Added the following stored procedure(s):

Stored Procedure

dbo.uspReportGetFoodInstrumentFiscalRedemptionsSummary

Added the following view(s):

View

dbo.V_FOODINSTRUMENT_FISCAL_REDEMPTIONS

Added the following user defined function(s):

User Defined Function

dbo.FederalFiscalYears

dbo.FederalFiscalQuarters

dbo.FederalFiscalMonths

ENH-15

Added a new table: BFHistory

Column Name	Data Type	Constraint
StateWICID	varchar(8)	PK
ChangedDTTM	datetime	PK
AgeCategory	char(1)	NOT NULL
EverBreastfed	char(1)	NULL
CurrentlyBreastfed	char(1)	NULL
AmountBreastfeeding	char(1)	NULL
BreastFeedingBegan	datetime	NULL
BreastFeedingEnd	datetime	NULL
SuppFeedBeganDate	datetime	NULL
CurrentlyBreastfedBeyondOneYear	char(1)	NULL
CreateUserID	varchar(20)	NULL
CreateDTTM	datetime	NULL

Added a new table: BFReasonsStoppedHistory

Column Name	Data Type	Constraint
StateWICID	varchar(8)	PK
ChangedDTTM	datetime	PK
StoppedBFReason	char(1)	PK
CreateUserID	varchar(20)	NULL
CreateDTTM	datetime	NULL

Added the following stored procedure(s):

Stored Procedure

uspInsertIntoBFHistory

uspInsertIntoBFReasonsStoppedHistory

Added the following user defined function(s):

User Defined Function

GetAgeCategoryByStateWicId

ENH-36

Added the following stored procedure(s):

Stored Procedure

dbo.uspReportGetReportedParticipationList

Added the following view(s):

View

dbo.V_HOUSEHOLD_CHANGES

dbo.V_CASELOADCOUNT_REPORTED_PARTICIPATION

Deleted the following view(s):

View

dbo.V_CASELOADCOUNT_REPORTED_PARTICIPATION

Added the following user defined function(s):

User Defined Function

dbo.GetParticipantCategoryDescription

Deleted the following user defined function(s):

User Defined Function

dbo.GetParticipantCategoryDescription

ENH-113

Added the following stored procedure(s):

Stored Procedure

dbo.GetBenefitPickup

dbo.GetCertificationDue

Deleted the following view(s):

View

dbo.V_CERTIFICATIONDUEDETAILS

Added index IX_MEMBER_BIRTHDATE to dbo.MEMBER.

ENH-240

Added the following stored procedure(s):

Stored Procedure

dbo.uspReportGetParticipationVsMonthlyEnrollment

ENH-242

Added the following stored procedure(s):

Stored Procedure

dbo.uspReportGetWicParticipationPerFNS798

ENH-245

Added the following stored procedure(s):

Stored Procedure

dbo.uspReportGetBreastfeedingInitiation

Deleted the following view(s):

View

dbo.V_BREASTFEEDING_INITIATION

Modified the following user defined function(s):

User Defined Function

[dbo].[FormatDateTime]

ENH-258

Added a new table: DuplicateParticipantConfiguration

Column Name	Data Type	Constraint
FieldCriteriaID	int	pk_duplicateparticipantconfiguration
DisplayName	varchar
TableName	varchar
ColumnName	varchar
DataType	varchar
IncludeInDisplay	char
SortOrder	int
IncludeInCompare	char
CharacterMatchLimit	int
DateMatchVariance	int
RemoveSpacesAndSpecial	char
TypeOfCompare	varchar
CREATEUSERID	varchar
CREATEDTTM	datetime
MODIFYUSERID	varchar
MODIFYDTTM	datetime

Added a new table: DuplicateParticipants

Column Name	Data Type	Constraint
DuplicateParticipantID	varchar	pk_duplicateparticipant
StateWICID1	varchar
StateWICID2	varchar
PercentMatch	int
IsDuplicate	char
CREATEUSERID	varchar
CREATEDTTM	datetime
MODIFYUSERID	varchar
MODIFYDTTM	datetime

The following row was added to the DuplicateParticipantConfiguration table:

Column Name	Row Value
FieldCriteriaID	1
DisplayName	Date of Birth
TableName	Member
ColumnName	DateOfBirth
DataType	Date
IncludeInDisplay	Y
SortOrder	1
IncludeInCompare	N
CharacterMatchLimit	NULL
DateMatchVariance	NULL
RemoveSpacesAndSpecial	N
TypeOfCompare	Exact
CREATEUSERID	ENH-258
CREATEDTTM	GETDATE()
MODIFYUSERID	ENH-258
MODIFYDTTM	GETDATE()

The following row was added to the DuplicateParticipantConfiguration table:

Column Name	Row Value
FieldCriteriaID	2
DisplayName	First Name
TableName	Member
ColumnName	FirstName
DataType	String
IncludeInDisplay	Y
SortOrder	2
IncludeInCompare	N
CharacterMatchLimit	NULL
DateMatchVariance	NULL
RemoveSpacesAndSpecial	N
TypeOfCompare	Exact
CREATEUSERID	ENH-258
CREATEDTTM	GETDATE()
MODIFYUSERID	ENH-258
MODIFYDTTM	GETDATE()

The following row was added to the DuplicateParticipantConfiguration table:

Column Name	Row Value
FieldCriteriaID	3
DisplayName	Middle Initial
TableName	Member
ColumnName	MiddleInitial
DataType	String
IncludeInDisplay	Y
SortOrder	3
IncludeInCompare	N
CharacterMatchLimit	NULL
DateMatchVariance	NULL
RemoveSpacesAndSpecial	N
TypeOfCompare	Exact
CREATEUSERID	ENH-258
CREATEDTTM	GETDATE()
MODIFYUSERID	ENH-258
MODIFYDTTM	GETDATE()

The following row was added to the DuplicateParticipantConfiguration table:

Column Name	Row Value
FieldCriteriaID	4
DisplayName	Last Name
TableName	Member
ColumnName	LastName
DataType	String
IncludeInDisplay	Y
SortOrder	4
IncludeInCompare	N
CharacterMatchLimit	NULL
DateMatchVariance	NULL
RemoveSpacesAndSpecial	N
TypeOfCompare	Exact
CREATEUSERID	ENH-258
CREATEDTTM	GETDATE()
MODIFYUSERID	ENH-258
MODIFYDTTM	GETDATE()

The following row was added to the DuplicateParticipantConfiguration table:

Column Name	Row Value
FieldCriteriaID	5
DisplayName	Address
TableName	HouseHold
ColumnName	Address
DataType	String
IncludeInDisplay	Y
SortOrder	5
IncludeInCompare	N
CharacterMatchLimit	NULL
DateMatchVariance	NULL
RemoveSpacesAndSpecial	N
TypeOfCompare	Exact
CREATEUSERID	ENH-258
CREATEDTTM	GETDATE()
MODIFYUSERID	ENH-258
MODIFYDTTM	GETDATE()

The following row was added to the DuplicateParticipantConfiguration table:

Column Name	Row Value
FieldCriteriaID	6
DisplayName	City
TableName	LEGALMUNICIPALITY
ColumnName	Name
DataType	String
IncludeInDisplay	Y
SortOrder	6
IncludeInCompare	N
CharacterMatchLimit	NULL
DateMatchVariance	NULL
RemoveSpacesAndSpecial	N
TypeOfCompare	Exact
CREATEUSERID	ENH-258
CREATEDTTM	GETDATE()
MODIFYUSERID	ENH-258
MODIFYDTTM	GETDATE()

The following row was added to the DuplicateParticipantConfiguration table:

Column Name	Row Value
FieldCriteriaID	7
DisplayName	State
TableName	HouseHold
ColumnName	StateCd
DataType	String
IncludeInDisplay	Y
SortOrder	7
IncludeInCompare	N
CharacterMatchLimit	NULL
DateMatchVariance	NULL
RemoveSpacesAndSpecial	N
TypeOfCompare	Exact
CREATEUSERID	ENH-258
CREATEDTTM	GETDATE()
MODIFYUSERID	ENH-258
MODIFYDTTM	GETDATE()

The following row was added to the DuplicateParticipantConfiguration table:

Column Name	Row Value
FieldCriteriaID	8
DisplayName	Zip
TableName	HouseHold
ColumnName	ZipCode
DataType	String
IncludeInDisplay	Y
SortOrder	8
IncludeInCompare	N
CharacterMatchLimit	NULL
DateMatchVariance	NULL
RemoveSpacesAndSpecial	N
TypeOfCompare	Exact
CREATEUSERID	ENH-258
CREATEDTTM	GETDATE()
MODIFYUSERID	ENH-258
MODIFYDTTM	GETDATE()

The following row was added to the DuplicateParticipantConfiguration table:

Column Name	Row Value
FieldCriteriaID	9
DisplayName	Telephone 1
TableName	HouseHold
ColumnName	Telephone1
DataType	String
IncludeInDisplay	Y
SortOrder	9
IncludeInCompare	N
CharacterMatchLimit	NULL
DateMatchVariance	NULL
RemoveSpacesAndSpecial	N
TypeOfCompare	Exact
CREATEUSERID	ENH-258
CREATEDTTM	GETDATE()
MODIFYUSERID	ENH-258
MODIFYDTTM	GETDATE()

The following row was added to the DuplicateParticipantConfiguration table:

Column Name	Row Value
FieldCriteriaID	10
DisplayName	Telephone 2
TableName	HouseHold
ColumnName	Telephone2
DataType	String
IncludeInDisplay	Y
SortOrder	10
IncludeInCompare	N
CharacterMatchLimit	NULL
DateMatchVariance	NULL
RemoveSpacesAndSpecial	N
TypeOfCompare	Exact
CREATEUSERID	ENH-258
CREATEDTTM	GETDATE()
MODIFYUSERID	ENH-258
MODIFYDTTM	GETDATE()

The following row was added to the DuplicateParticipantConfiguration table:

Column Name	Row Value
FieldCriteriaID	11
DisplayName	SSN
TableName	Member
ColumnName	SSN
DataType	String
IncludeInDisplay	Y
SortOrder	11
IncludeInCompare	N
CharacterMatchLimit	NULL
DateMatchVariance	NULL
RemoveSpacesAndSpecial	N
TypeOfCompare	Exact
CREATEUSERID	ENH-258
CREATEDTTM	GETDATE()
MODIFYUSERID	ENH-258
MODIFYDTTM	GETDATE()

The following row was added to the DuplicateParticipantConfiguration table:

Column Name	Row Value
FieldCriteriaID	12
DisplayName	RaceEthnicity
TableName	RaceDescription
ColumnName	RaceEthnicity
DataType	String
IncludeInDisplay	Y
SortOrder	12
IncludeInCompare	N
CharacterMatchLimit	NULL
DateMatchVariance	NULL
RemoveSpacesAndSpecial	N
TypeOfCompare	Exact
CREATEUSERID	ENH-258
CREATEDTTM	GETDATE()
MODIFYUSERID	ENH-258
MODIFYDTTM	GETDATE()

The following row was added to the DuplicateParticipantConfiguration table:

Column Name	Row Value
FieldCriteriaID	13
DisplayName	RaceEthnicity
TableName	EthnicityDescription
ColumnName	RaceEthnicity
DataType	String
IncludeInDisplay	Y
SortOrder	13
IncludeInCompare	N
CharacterMatchLimit	NULL
DateMatchVariance	NULL
RemoveSpacesAndSpecial	N
TypeOfCompare	Exact
CREATEUSERID	ENH-258
CREATEDTTM	GETDATE()
MODIFYUSERID	ENH-258
MODIFYDTTM	GETDATE()

The following row was added to the DuplicateParticipantConfiguration table:

Column Name	Row Value
FieldCriteriaID	14
DisplayName	Maiden First Name
TableName	Member
ColumnName	MotherFirstName
DataType	String
IncludeInDisplay	Y
SortOrder	14
IncludeInCompare	N
CharacterMatchLimit	NULL
DateMatchVariance	NULL
RemoveSpacesAndSpecial	N
TypeOfCompare	Exact
CREATEUSERID	ENH-258
CREATEDTTM	GETDATE()
MODIFYUSERID	ENH-258
MODIFYDTTM	GETDATE()

The following row was added to the DuplicateParticipantConfiguration table:

Column Name	Row Value
FieldCriteriaID	15
DisplayName	Maiden MI
TableName	Member
ColumnName	MotherMiddleInitial
DataType	String
IncludeInDisplay	Y
SortOrder	15
IncludeInCompare	N
CharacterMatchLimit	NULL
DateMatchVariance	NULL
RemoveSpacesAndSpecial	N
TypeOfCompare	Exact
CREATEUSERID	ENH-258
CREATEDTTM	GETDATE()
MODIFYUSERID	ENH-258
MODIFYDTTM	GETDATE()

The following row was added to the DuplicateParticipantConfiguration table:

Column Name	Row Value
FieldCriteriaID	16
DisplayName	Maiden Last Name
TableName	Member
ColumnName	MotherLastName
DataType	String
IncludeInDisplay	Y
SortOrder	16
IncludeInCompare	N
CharacterMatchLimit	NULL
DateMatchVariance	NULL
RemoveSpacesAndSpecial	N
TypeOfCompare	Exact
CREATEUSERID	ENH-258
CREATEDTTM	GETDATE()
MODIFYUSERID	ENH-258
MODIFYDTTM	GETDATE()

The following row was added to the DuplicateParticipantConfiguration table:

Column Name	Row Value
FieldCriteriaID	17
DisplayName	Last Cet Eff Date
TableName	CertContact
ColumnName	CertEffectiveDate
DataType	Date
IncludeInDisplay	Y
SortOrder	17
IncludeInCompare	N
CharacterMatchLimit	NULL
DateMatchVariance	NULL
RemoveSpacesAndSpecial	N
TypeOfCompare	Exact
CREATEUSERID	ENH-258
CREATEDTTM	GETDATE()
MODIFYUSERID	ENH-258
MODIFYDTTM	GETDATE()

The following row was added to the DuplicateParticipantConfiguration table:

Column Name	Row Value
FieldCriteriaID	18
DisplayName	Last LDTU
TableName	FoodInstrumentSet
ColumnName	LastUseDate
DataType	Date
IncludeInDisplay	Y
SortOrder	18
IncludeInCompare	N
CharacterMatchLimit	NULL
DateMatchVariance	NULL
RemoveSpacesAndSpecial	N
TypeOfCompare	Exact
CREATEUSERID	ENH-258
CREATEDTTM	GETDATE()
MODIFYUSERID	ENH-258
MODIFYDTTM	GETDATE()

The following row was added to the DuplicateParticipantConfiguration table:

Column Name	Row Value
FieldCriteriaID	19
DisplayName	Agency
TableName	Member
ColumnName	AgencyID
DataType	String
IncludeInDisplay	Y
SortOrder	19
IncludeInCompare	N
CharacterMatchLimit	NULL
DateMatchVariance	NULL
RemoveSpacesAndSpecial	N
TypeOfCompare	Exact
CREATEUSERID	ENH-258
CREATEDTTM	GETDATE()
MODIFYUSERID	ENH-258
MODIFYDTTM	GETDATE()

The following row was added to the DuplicateParticipantConfiguration table:

Column Name	Row Value
FieldCriteriaID	20
DisplayName	Clinic
TableName	Member
ColumnName	ServiceSiteID
DataType	String
IncludeInDisplay	Y
SortOrder	20
IncludeInCompare	N
CharacterMatchLimit	NULL
DateMatchVariance	NULL
RemoveSpacesAndSpecial	N
TypeOfCompare	Exact
CREATEUSERID	ENH-258
CREATEDTTM	GETDATE()
MODIFYUSERID	ENH-258
MODIFYDTTM	GETDATE()

The following row was added to the DuplicateParticipantConfiguration table:

Column Name	Row Value
FieldCriteriaID	21
DisplayName	Household ID
TableName	Member
ColumnName	HouseHoldID
DataType	String
IncludeInDisplay	Y
SortOrder	21
IncludeInCompare	N
CharacterMatchLimit	NULL
DateMatchVariance	NULL
RemoveSpacesAndSpecial	N
TypeOfCompare	Exact
CREATEUSERID	ENH-258
CREATEDTTM	GETDATE()
MODIFYUSERID	ENH-258
MODIFYDTTM	GETDATE()

The following row was added to the DuplicateParticipantConfiguration table:

Column Name	Row Value
FieldCriteriaID	22
DisplayName	Gender
TableName	Member
ColumnName	Gender
DataType	String
IncludeInDisplay	Y
SortOrder	22
IncludeInCompare	N
CharacterMatchLimit	NULL
DateMatchVariance	NULL
RemoveSpacesAndSpecial	N
TypeOfCompare	Exact
CREATEUSERID	ENH-258
CREATEDTTM	GETDATE()
MODIFYUSERID	ENH-258
MODIFYDTTM	GETDATE()

The following row was added to the ReferenceDictionary table:

Column Name	Row Value
CATEGORY	TERMREASON
EXTERNALID	$
DESCRIPTION	Duplicate Participant
SORTVALUE	0
ACTIVE	Y
SYSTEMDEFINED	Y
UPDATERECORD	NULL
CREATEUSERID	NULL
CREATEDTTM	GETDATE()
MODIFYUSERID	NULL
MODIFYDTTM	GETDATE()
HIDDEN	Y
CREATEUSERID	ENH-258
CREATEDTTM	GETDATE()
MODIFYUSERID	ENH-258
MODIFYDTTM	GETDATE()

The following row was added to the DuplicateParticipantConfiguration table:

Column Name	Row Value
FieldCriteriaID	4
DisplayName	Last Name
TableName	Member
ColumnName	LastName
DataType	String
IncludeInDisplay	Y
SortOrder	4
IncludeInCompare	N
CharacterMatchLimit	NULL
DateMatchVariance	NULL
RemoveSpacesAndSpecial	N
TypeOfCompare	Exact
CREATEUSERID	ENH-258
CREATEDTTM	GETDATE()
MODIFYUSERID	ENH-258
MODIFYDTTM	GETDATE()

The following row was added to the DuplicateParticipantConfiguration table:

Column Name	Row Value
FieldCriteriaID	5
DisplayName	Address
TableName	HouseHold
ColumnName	Address
DataType	String
IncludeInDisplay	Y
SortOrder	5
IncludeInCompare	N
CharacterMatchLimit	NULL
DateMatchVariance	NULL
RemoveSpacesAndSpecial	N
TypeOfCompare	Exact
CREATEUSERID	ENH-258
CREATEDTTM	GETDATE()
MODIFYUSERID	ENH-258
MODIFYDTTM	GETDATE()

ENH-265

Added a new table: StateUseCaptionHistory

Column Name	Data Type	Constraint
StateUseCaptionHistoryID	numeric	pk_stateusecaptionhistory
CaptionID	numeric	fk_stateusecaptionhistory_captionid
Description	varchar
SUModifyUserID	varchar
SUModifyDTTM	datetime
CreateUserID	varchar
CreateDTTM	datetime
ModifyUserID	varchar
ModifyDTTM	datetime

Added a new table: StateUseCodeHistory

Column Name	Data Type	Constraint
StateUseCodeHistoryID	numeric	pk_stateusecodehistory
ValueSetID	numeric
ValueID	numeric	fk_stateusecode_valueid
Description	varchar
SUModifyUserID	varchar
SUModifyDTTM	datetime
CreateUserID	varchar
CreateDTTM	datetime
ModifyUserID	varchar
ModifyDTTM	datetime

Added a new table: LocalUseCaptionHistory

Column Name	Data Type	Constraint
LocalUseCaptionHistoryID	numeric	pk_localusecaptionhistory
CaptionID	numeric	fk_localusecaptionhistory_captionid
Description	varchar
LUModifyUserID	varchar
LUModifyDTTM	datetime
CreateUserID	varchar
CreateDTTM	datetime
ModifyUserID	varchar
ModifyDTTM	datetime

Added a new table: LocalUseCodeHistory

Column Name	Data Type	Constraint
LocalUseCodeHistoryID	numeric	pk_localusecodehistory
ValueSetID	numeric
ValueID	numeric	fk_localusecode_valueid
Description	varchar
LUModifyUserID	varchar
LUModifyDTTM	datetime
CreateUserID	varchar
CreateDTTM	datetime
ModifyUserID	varchar
ModifyDTTM	datetime

Added the following columns to the StateUseCaption table:

Column Name	Data Type	Constraint
REQUIRED	varchar
DATERESET	datetime

Added the following columns to the LocalUseCaption table:

Column Name	Data Type	Constraint
REQUIRED	varchar
DATERESET	datetime

The following row was added to the RefDictionaryCategory table:

Column Name	Row Value
CATEGORY	USECODEREQUIRED
DESCRIPTION	Use Code Requirement
STATEDEFINED	Y
VALUETYPE	A
VALUELENGTH	3
CREATEUSERID	ENH-265
CREATEDTTM	GETDATE()
MODIFYUSERID	NULL
MODIFYDTTM	GETDATE()

The following row was added to the ReferenceDictionary table:

Column Name	Row Value
CATEGORY	USECODEREQUIRED
EXTERNALID	AWS
DESCRIPTION	Always
SORTVALUE	3
ACTIVE	Y
SYSTEMDEFINED	Y
UPDATERECORD	NULL
CREATEUSERID	ENH-265
CREATEDTTM	GETDATE()
MODIFYUSERID	NULL
MODIFYDTTM	GETDATE()
HIDDEN	N

The following row was added to the ReferenceDictionary table:

Column Name	Row Value
CATEGORY	USECODEREQUIRED
EXTERNALID	CGS
DESCRIPTION	During a Certification Attempt
SORTVALUE	2
ACTIVE	Y
SYSTEMDEFINED	Y
UPDATERECORD	NULL
CREATEUSERID	ENH-265
CREATEDTTM	GETDATE()
MODIFYUSERID	NULL
MODIFYDTTM	GETDATE()
HIDDEN	N

The following row was added to the ReferenceDictionary table:

Column Name	Row Value
CATEGORY	USECODEREQUIRED
EXTERNALID	NVR
DESCRIPTION	Never
SORTVALUE	1
ACTIVE	Y
SYSTEMDEFINED	Y
UPDATERECORD	NULL
CREATEUSERID	ENH-265
CREATEDTTM	GETDATE()
MODIFYUSERID	NULL
MODIFYDTTM	GETDATE()
HIDDEN	N

Added the following stored procedure(s):

Stored Procedure

uspSaveLocalSurveyAnswer

uspSaveStateSurveyAnswer

uspGetLocalAnswerHistoryID

uspGetLocalQuestionHistoryID

uspGetStateAnswerHistoryID

uspGetStateQuestionHistoryID

ENH-267

Added the following column to the Child table:

Column Name	Data Type	Constraint
CurrentlyBreastfedBeyondOneYear	char(1)	Y or N

Added the following column to the STATEBUSINESSRULES table:

Column Name	Data Type	Constraint
DEFAULT	varchar(40)	NULL

ENH-269

Added the following column to the Pregnancy table:

Column Name	Data Type	Constraint
PrepregBMI	numeric	NULL

Added the following column to the Postpartum table:

Column Name	Data Type	Constraint
PrepregBMI	numeric	NULL

ENH-285

Added the following columns to the EDUCATIONCONTACT table:

Column Name	Data Type	Constraint
NOSHOW	VARCHAR(1)	DF_EDUCATIONCONTACT_NOSHOW
REFUSED	VARCHAR(1)	DF_EDUCATIONCONTACT_REFUSED

Added the following stored procedure(s):

Stored Procedure

dbo.uspReferralContactItemsSave

dbo.uspPeerCounselorReferralItemsSave

dbo.uspEducationContactItemsSave

dbo.uspGoalContactItemsSave

dbo.uspMaterialContactItemsSave

Modified the following view(s):

View

dbo.V_REFERRAL_CONTACT_LISTITEMS

Added the following user defined function(s):

User Defined Function

dbo.MonthDiff

ENH-291

Added the following columns to the AnthropContact table:

Column Name	Data Type	Constraint
MeasurementSource	char(2)	NULL
DoNotPlot	char(1)	NULL

ENH-292

Added the following columns to the CertContact table:

Column Name	Data Type	Constraint
ResolveHighRisk	char(1)	Not Null

ENH-295

Added the following stored procedure(s):

Stored Procedure

dbo.BFDurationNotBF

dbo.BFDurationIsBF

Deleted the following view(s):

View

dbo.V_BF_DISCONTINUANCE

ENH-301

Added the following columns to the dbo.BFCOUNSELORCONTACT table:

Column Name	Data Type	Constraint
ContactType	varchar(10)	NULL

The following row was added to the dbo.REFDICTIONARYCATEGORY table:

Column Name	Row Value
CATEGORY	CounselorContactType
DESCRIPTION	Type of contact made by a peer counselor
STATEDEFINED	Y
CREATEUSERID	ENH-301
CREATEDTTM	GETDATE()

The following row was added to the dbo.REFERENCEDICTIONARY table:

Column Name	Row Value
CATEGORY	CounselorContactType
EXTERNALID	1
DESCRIPTION	Home Visit
SORTVALUE	1
CREATEUSERID	ENH-301
CREATEDTTM	GETDATE()

The following row was added to the dbo.REFERENCEDICTIONARY table:

Column Name	Row Value
CATEGORY	CounselorContactType
EXTERNALID	2
DESCRIPTION	Office Visit
SORTVALUE	2
CREATEUSERID	ENH-301
CREATEDTTM	GETDATE()

The following row was added to the dbo.REFERENCEDICTIONARY table:

Column Name	Row Value
CATEGORY	CounselorContactType
EXTERNALID	3
DESCRIPTION	Phone Call
SORTVALUE	3
CREATEUSERID	ENH-301
CREATEDTTM	GETDATE()

Modified the following stored procedure(s):

Stored Procedure

dbo.GetCounselorContactTopics

Modified the following view(s):

View

dbo.V_BFCOUNSELOR_CONTACT_TOPICS_PT1

dbo.V_BFCONTACTTOPICS

Deleted the following view(s):

View

dbo.V_BFCOUNSELOR_CONTACT_TOPICS

ENH-304

Added the following stored procedure(s):

Stored Procedure

dbo.GetCounselorContactTopics

Modified the following view(s):

View

dbo.V_BFCOUNSELOR_CONTACT_TOPICS_PT1

ENH-305

Added the following stored procedure(s):

Stored Procedure

dbo.GetPCContactFreqBf

The following rows were deleted from the dbo.STATEBUSINESSRULES table:

Column Name	Row Value
CATEGORYID	BFCOUNSELORCONTACTPROTOCOL1
CATEGORYID	BFCOUNSELORCONTACTPROTOCOL2
CATEGORYID	BFCOUNSELORCONTACTPROTOCOL3

ENH-306

Added the following stored procedure(s):

Stored Procedure

dbo.GetPCContactPendingBf

Deleted the following view(s):

View

dbo.V_BF_WOMAN_CONTATCS

V_BF_WOMAN_CONTATCS

The following row was deleted from the dbo.STATEBUSINESSRULES table:

Column Name	Row Value
CATEGORYID	RPT_BFWOMENPEERCOUNCONTACT_ENABLED

ENH-307

Added the following stored procedure(s):

Stored Procedure

dbo.GetPCContactPendingPn

Deleted the following view(s):

View

dbo.V_PENDING_PRENATAL_CONTACTS

ENH-308

Added the following stored procedure(s):

Stored Procedure

dbo.GetPCContactFreqPn

ENH-335

The following row was added to the STATEBUSINESSRULES table:

Column Name	Row Value
CATEGORYID	CLN_ALLOWDELAYBLOODWORK
VALUE	Y
DESCRIPTION	Allow Delayed Blood Work

ENH-339

Added a new table: Abuse

Column Name	Data Type	Constraint
AbuseID	numeric(10,0)	Primary Key
VendorID	varchar(6)	Foreign Key
MonitorAreaID	varchar(3)	NULL
CreateUserID	varchar(20)	NULL
CreateDtTm	datetime	NULL
ModifyUserID	varchar(20)	NULL
ModifyDtTm	datetime	NULL

Added a new table: MonitorArea

Column Name	Data Type	Constraint
MonitorAreaID	varchar(3)	Primary Key
Description	varchar(135)	NULL
SortValue	numeric(3,0)	NULL
Active	char(1)	NULL
ViolationTypeID	varchar(3)	NULL
CreateUserID	varchar(20)	NULL
CreateDtTm	datetime	NULL
ModifyUserID	varchar(20)	NULL
ModifyDtTm	datetime	NULL

Added the following columns to the MONITORINGAREA table:

Column Name	Data Type	Constraint
ViolatonFound	char(1)	NULL
AbuseID	nunmeric(10,0)	NULL

Removed the following columns from the MONITORINGAREA table:

Column Name

UPDATERECORD

Removed the following columns from the VIOLATIONTYPE table:

Column Name

UPDATERECORD

Removed the following columns from the VIOLATION table:

Column Name

UPDATERECORD

Added the following stored procedure(s):

Stored Procedure

uspGetMonitorAreaData

uspUpdateInsertMonitorAreaData

uspDeleteMonitorAreaData

ENH-377

The following row was added to the STATEBUSINESSRULES table:

Column Name	Row Value
CATEGORYID	CLN_UseCrystalReportCheckLayout
VALUE	N
DESCRIPTION	Use Crystal Report Check Layout for Printed Checks

Added a new table: CheckLayout

Column Name	Data Type	Constraint
LayoutID	numeric	PK
Description	varchar (200)	NULL
ChecksPerPage	numeric (2,0)	NULL
CheckHeight	numeric (4,0)	NULL
CheckItemOffset	numeric (4,0)	NULL
CreateUserID	varchar (20)	NULL
CreateDttm	datetime	NULL
ModifyUserID	varchar (20)	NULL
ModifyDttm	datetime	NULL

Added a new table: CheckLayoutDetail

Column Name	Data Type	Constraint
FieldID	numeric	PK
LayoutID	numeric	FK
Description	varchar (50)	NULL
XPosition	numeric (4,0)	NULL
YPosition	numeric (4,0)	NULL
FontID	numeric	FK
RightJustified	char (1)	Y/N
CheckPropertyName	varchar (50)	NULL
TestCharacters	varchar (200)	NULL
CreateUserID	varchar (20)	NULL
CreateDttm	datetime	NULL
ModifyUserID	varchar (20)	NULL
ModifyDttm	datetime	NULL

Added a new table: CheckFonts

Column Name	Data Type	Constraint
FontID	numeric	PK
FontFamily	varchar (50)	NULL
Description	varchar (50)	NULL
PrinterFontCode	varchar (20)	NULL
FontDisplaySize	numeric (2,0)	NULL
CreateUserID	varchar (20)	NULL
CreateDttm	datetime	NULL
ModifyUserID	varchar (20)	NULL
ModifyDttm	datetime	NULL

ENH-379

Added the following columns to the FOODITEM table:

Column Name	Data Type	Constraint
MinimumProrateQtyEnabled	char(1)	DEFAULT 'N' NOT NULL
MinimumProrateQty	numeric(6,2)	NULL
ProrationRule	char(1)	NULL

The following row was added to the REFERENCEDICTIONARYCATEGORY table:

Column Name	Row Value
CATEGORY	ProrationRule
DESCRIPTION	Proration Rule
STATEDEFINED	N
CREATEUSERID	ENH-379
CREATEDTTM	GETDATE()
MODIFYUSERID	ENH-379
MODIFYDTTM	GETDATE()

The following row was added to the REFERENCEDICTIONARY table:

Column Name	Row Value
CATEGORY	ProrationRule
EXTERNALID	1
DESCRIPTION	Round Up to Minimum Proration Quantity
SORTVALUE	1
ACTIVE	Y
SYSTEMDEFINED	Y
CREATEUSERID	ENH-379
CREATEDTTM	GETDATE()
MODIFYUSERID	ENH-379
MODIFYDTTM	GETDATE()

The following row was added to the REFERENCEDICTIONARY table:

Column Name	Row Value
CATEGORY	ProrationRule
EXTERNALID	2
DESCRIPTION	Round Down to Zero
SORTVALUE	2
ACTIVE	Y
SYSTEMDEFINED	Y
CREATEUSERID	ENH-379
CREATEDTTM	GETDATE()
MODIFYUSERID	ENH-379
MODIFYDTTM	GETDATE()

ENH-389

Added a new table: RequiredFields

Column Name	Data Type	Constraint
FieldID	numeric(10,0)	NULL
Feature	varchar(25)	NULL
WICCategory	char(1)	NULL
DisplayText	varchar(50)	NULL
Certification Type	char(1)	NULL
Required	char(1)	NULL
CreateUserID	varchar(20)	NULL
CreateDttm	datetime	NULL
ModifyUserID	varchar(20)	NULL
ModifyDttm	datetime	NULL

The following row was deleted from the STATEBUSINESSRULES table:

Column Name	Row Value
CATEGORYID	CLINIC_EnableRegisterToVoteInfantsChildren

ENH-391

Added the following column(s) to the InitialContact table:

Column Name	Data Type	Constraint
WicStatus	\|	\|
InitialMigrant	\|	\|

The InitialMigrant value in the InitialContact table will not be tied to the Migrant column of the Household table that determines the participant’s migrant status.

ENH-396

Added the following columns to the FoodInstrumentSet table:

Column Name	Data Type	Constraint
CycleAdjust	char(1)	NULL
Added the following	char(1)	NULL
Replacement	char(1)	NULL

Added the following columns to the FoodInstrument table:

Column Name	Data Type	Constraint
Reprint	char(1)	NULL

Added the following columns to the EBTPENDING_FOODINSTRUMENTSET table:

Column Name	Data Type	Constraint
CycleAdjust	char(1)	NULL
Added	char(1)	NULL
Replacement	char(1)	NULL

Added the following columns to the EBTPENDING_FOODINSTRUMENT table:

Column Name	Data Type	Constraint
Reprint	char(1)	NULL

Added the following view(s):

View

V_FISET_HISTORY

V_FI_HISTORY

Deleted the following view(s):

View

V_CHECK_HISTORY_HEADER

ENH-399

The following row was added to the STATEBUSINESSRULES table:

Column Name	Row Value
CATEGORYID	CLN_BenefitsStartOnFirstDayOfMonth
VALUE	N
DESCRIPTION	Start All Benefits on the First Day of the Month

The following row was added to the STATEBUSINESSRULES table:

Column Name	Row Value
CATEGORYID	CLN_CreateInitialMonthToMonthBenefit
VALUE	N
DESCRIPTION	Create an Initial Benefit in Addition to the Normally-issued Benefits at the Start of Certification, if Necessary

The following row was added to the STATEBUSINESSRULES table:

Column Name	Row Value
CATEGORYID	CLN_SuggestLastBenefitForChildren
VALUE	N
DESCRIPTION	Suggest the Last Set of Benefits for Children Not Turning Five

NIN-36 / SUG-1863

Added the following stored procedure(s):

Stored Procedure

dbo.uspReportGetStatewideEducationSummary

Deleted the following view(s):

View

[dbo].[V_STATEWIDE_EDUCATION_CONTACTS]

[dbo].[V_STATEWIDE_EDUCATION_SUMMARY]

NIN-130 / SUG-437

Modified the following view(s):

View

V_NON_PARTICIPATION_REMAINDER_NOTICES

NIN-141 / SUG-1712

Modified the following view(s):

View

dbo.V_INVENTORY_ISSUED

NIN-181 / SUG-1382

Added the following stored procedure(s):

Stored Procedure

dbo.uspReportGetNutritionalRiskFactorsAtCertificationByCategory

Added the following user defined function(s):

User Defined Function

dbo.MonthDiff