Provider

Introduction

This section introduces the structure of the ‘provider’ table and its relationships within the database. It specifically focuses on the connections between family members, such as the relationship between parents and children. This documentation aims to provide an overview of how family entities are interconnected.

PRO_Provider

Field	Type	Index	Description
ProviderID	int identity	PK	Primary key, unique identifier
ProviderNO	nvarchar(30)	IX	Provider number
VenderNO	nvarchar(500)	IX	Vendor number
ProviderName	nvarchar(1000)	_dta	Provider name
ProviderCategory	nvarchar(32)		Provider category
ProviderType	int	IX	Provider type
FirstName	nvarchar(500)	IX	First name
LastName	nvarchar(500)	IX	Last name
MiddleInitial	nvarchar(10)		Middle initial
Gender	nvarchar(32)		Gender
DateOfBirth	datetime		Date of birth
Zip	nvarchar(10)		Zip code
Address	nvarchar(300)		Address
Unit	nvarchar(300)		Unit number
City	nvarchar(30)		City name
County	nvarchar(50)		County name
State	nvarchar(30)		State name
CityID	int		City ID
CountyID	int		County ID
StateID	int		State ID
SameAddress	bit		Same address flag (Y/N)
MailZip	nvarchar(10)		Mailing zip code
MailAddress	nvarchar(300)		Mailing address
MailUnit	nvarchar(300)		Mailing unit number
MailCity	nvarchar(30)		Mailing city name
MailCounty	nvarchar(50)		Mailing county name
MailState	nvarchar(30)		Mailing state name
MailCityID	int		Mailing city ID
MailCountyID	int		Mailing county ID
MailStateID	int		Mailing state ID
EMailAddress	nvarchar(80)		Email address
Phone1	nvarchar(35)		Primary phone number
Phone2	nvarchar(35)		Secondary phone number
Cell	nvarchar(35)		Cell phone number
Fax	nvarchar(20)		Fax number
RcvMsg	bit		Receive messages (Y/N)
RegistrationFeeType	nvarchar(32)		Registration fee type
SSN	nvarchar(32)		Social Security Number
TaxID	nvarchar(20)		Tax ID
OtherIDType	nvarchar(32)		Other ID type
OtherID	nvarchar(100)		Other ID
TaxLegalName	nvarchar(300)		Tax legal name
ExceptionalNeeds	bit		Exceptional needs (Y/N)
SeverelyHandicapped	bit		Severely handicapped (Y/N)
PayAbsence	varchar(50)		Pay absence
FTHours	decimal(18, 2)		Full-time hours
FollwAgencyHoliday	bit		Follow agency holiday (Y/N)
ResidentChildren	int		Number of resident children
TrustLineStatus	nvarchar(32)		TrustLine status
TrustLineComment	nvarchar(200)		TrustLine comment
TrustLineRegDate	datetime		TrustLine registration date
TrustLineLiveScanDate	datetime		TrustLine live scan date
TrustLineRRDate	datetime		TrustLine RR date
TrustLineResultDate	datetime		TrustLine result date
TransitionFlag	bit		Transition flag (Y/N)
SameFamilyID	int	IX	Same family ID
Prohibit	bit		Prohibit (Y/N)
ProhibitReson	nvarchar(32)		Prohibit reason
IsOnProbation	bit		Is on probation (Y/N)
ProviderManager	int		Provider manager
Language	int		Language ID
OwnerID	int	_dta	Owner ID
OrgID	int		Organization ID
Status	int		Status
Comments	nvarchar(max)		Comments
StartDate	datetime		Start date
EndDate	datetime		End date
History	int		History version
Disable	bit	IX	Disabled status (0 No, 1 Yes)
CreateUserID	int		ID of the user who created
UpateUserID	int		ID of the user who updated
Version	int		Version
TaxLegalFirstName	nvarchar(50)		Tax legal first name
TaxLegalMI	nvarchar(50)		Tax legal middle initial
PaymentMethod	nvarchar(50)		Payment method
PVCountyID	nvarchar(100)	IX	Provider county ID
IsAffiadvit	bit		Is affidavit (Y/N)
AffiadvitDate	datetime		Affidavit date
TrustLineReceiveDate	datetime		TrustLine receive date
DriversLicense	nvarchar(20)		Driver’s license number
DriversExpDate	datetime		Driver’s license expiration date
HoldStatus	int		Hold status (0 release, 1 hold new cares, 2 hold export)
HoldReleaseDate	datetime		Hold release date
ProviderModel	int		Provider model
CommunicationMethod	nvarchar(100)		Communication method
IsFTHours	bit		Is full-time hours (Y/N)
CreateTime	datetimeoffset		Record creation time
UpdateTime	datetimeoffset		Record update time
SpokenLanguage	varchar(500)		Spoken language
SSNEncrypted	nvarchar(100)		Encrypted Social Security Number
TaxIDEncrypted	nvarchar(100)		Encrypted Tax ID

PRO_ProviderExtension

Provider extension fields, for custom fields and additional information.

PRO_Contact

Field	Type	Index	Description
ContactID	int identity	PK	Primary key, unique identifier
ProviderID	int	IX	Foreign key to provider
Title	nvarchar(200)		Title
LastName	nvarchar(30)		Last name
FirstName	nvarchar(30)		First name
MiddleInitial	nvarchar(10)		Middle initial
DateOfBirth	datetime		Date of birth
Gender	nvarchar(32)		Gender
SSNumber	nvarchar(30)		Social Security Number
PublicSSNumber	bit		Is SSN public (Y/N)
Language	int		Language ID
LimitedEnglish	bit		Limited English proficiency (Y/N)
HomePhone	nvarchar(35)		Home phone number
WorkPhone	nvarchar(35)		Work phone number
Cell	nvarchar(35)		Cell phone number
Email	nvarchar(50)		Email address
OtherIDType	nvarchar(32)		Other ID type (e.g., Driver Lic)
OtherID	nvarchar(100)		Other ID
Status	int		Status
Comments	nvarchar(max)		Comments
StartDate	datetime		Start date
EndDate	datetime		End date
History	int		History version
Disable	bit		Disabled status (0 No, 1 Yes)
CreateUserID	int		ID of the user who created
UpateUserID	int		ID of the user who updated
Version	int		Version
CreateTime	datetimeoffset		Record creation time
UpdateTime	datetimeoffset		Record update time

PRO_Address

Field	Type	Index	Description
Id	int identity	PK	Primary key, unique identifier
ProviderId	int	IX	Foreign key to provider
StartDate	datetime		Start date
EndDate	datetime		End date
IsEffective	bit		Is effective (Y/N)
Zip	nvarchar(10)		Zip code
Address	nvarchar(300)		Address
Unit	nvarchar(300)		Unit number
City	nvarchar(30)		City name
County	nvarchar(30)		County name
State	nvarchar(30)		State name
CityID	int		City ID
CountyID	int		County ID
StateID	int		State ID
SameAddress	bit		Same address flag (0 No, 1 Yes)
MailZip	nvarchar(10)		Mailing zip code
MailAddress	nvarchar(300)		Mailing address
MailUnit	nvarchar(300)		Mailing unit number
MailCity	nvarchar(30)		Mailing city name
MailCounty	nvarchar(30)		Mailing county name
MailState	nvarchar(30)		Mailing state name
MailCityID	int		Mailing city ID
MailCountyID	int		Mailing county ID
MailStateID	int		Mailing state ID
EMailAddress	nvarchar(80)		Email address
Status	int		Status
Comment	nvarchar(max)		Comments
Disable	bit		Disabled status (0 No, 1 Yes)
CreateUserID	int		ID of the user who created
UpateUserID	int		ID of the user who updated
Version	int		Version
FullAddress	nvarchar(1024)		Full address
FullMailAddress	nvarchar(1024)		Full mailing address
CreateTime	datetimeoffset		Record creation time
UpdateTime	datetimeoffset		Record update time

PRO_Agreement

Field	Type	Index	Description
AgreementID	int identity	PK	Primary key, unique identifier
EntityRequestID	uniqueidentifier		Entity request ID
RequestDate	datetime		Request date
RequestReason	nvarchar(32)		Request reason
TerminateDate	datetime		Termination date
ValidStatus	int		Valid status (0 No, 1 Yes)
ProviderType	int		Provider type (Interim Provider, Licensed Center-Based, etc.)
ProviderManager	int		Provider manager
RequestUser	int		Request user
FamilyID	int	IX	Foreign key to family
ProviderID	int	IX	Foreign key to provider
ContractID	nvarchar(max)		Contract ID
CareType	nvarchar(32)		Care type (Interim Provider, Licensed Center-Based, etc.)
TrustLineStatus	nvarchar(32)		TrustLine status
TrustLineComment	nvarchar(max)		TrustLine comment
IsInHome	bit		Is in home (0 No, 1 Yes)
IsRelative	bit		Is relative (0 No, 1 Yes)
Relationship	int		Relationship
OrientationDate	datetime		Orientation date
OrientationConfirm	bit		Orientation confirmed (0 No, 1 Yes)
OrientationResult	nvarchar(max)		Orientation result
PayTo	int		Pay to (0: Family, 1: Provider For In Home)
Status	int		Status (0: New, 9: Terminate)
CKAgeGroup	bit		Check age group (0 No, 1 Yes)
CKCapacity	bit		Check capacity (0 No, 1 Yes)
CKPvAge	bit		Check provider age (0 No, 1 Yes)
CKInPvHome	bit		Check care in provider home (0 No, 1 Yes)
CKAnotherChild	bit		Check provider has another child (0 No, 1 Yes)
CKSSN	bit		Check provider has SSN (0 No, 1 Yes)
Comments	nvarchar(max)		Comments
StartDate	datetime		Start date
EndDate	datetime		End date
ProviderHistory	int		Provider history version
Disable	bit		Disabled status (0 No, 1 Yes)
CreateUserID	int		ID of the user who created
UpateUserID	int		ID of the user who updated
Version	int		Version
IsOOA	bit		Is out of area (0 No, 1 Yes)
PVCountyID	nvarchar(100)		Provider county ID
CKWithPT	bit		Check with provider (0 No, 1 Yes)
TrustLineRetro	int		TrustLine retro
DenyReason	nvarchar(max)		Deny reason
RealProviderType	int		Real provider type
CreateTime	datetimeoffset		Record creation time
UpdateTime	datetimeoffset		Record update time

PRO_Garnishment

Field	Type	Index	Description
GarnishmentId	int identity	PK	Primary key, unique identifier
ProviderId	int	IX	Foreign key to provider
Name	nvarchar(500)		Name
GarnishmentEntityId	int		Garnishment entity ID
OriginalAmount	decimal(18, 4)		Original amount
Balance	decimal(18, 4)		Balance
StartDate	datetime		Start date
EndDate	datetime		End date
Status	int		Status (New; Active; Hold; Terminated)
ReactivateDate	datetime		Reactivate date
ProcessingFee	decimal(18, 4)		Processing fee
PaymentMethod	nvarchar(32)		Payment method (Set Amount, Set Percentage, etc.)
SetAmount	decimal(18, 4)		Set amount
SetPercentage	decimal(18, 4)		Set percentage
PaymentFrequency	nvarchar(32)		Payment frequency (Per Month, Per Check Run, etc.)
LastExportDate	datetime		Last export date
TerminateDate	datetime		Termination date
TerminateReason	nvarchar(32)		Termination reason (Paid in Full, Expired, etc.)
Comment	nvarchar(max)		Comments
Disable	bit		Disabled status (0 No, 1 Yes)
CreateUserId	int		ID of the user who created
UpateUserId	int		ID of the user who updated
Version	int		Version
Agency	nvarchar(100)		Agency
Code	nvarchar(100)		Code
Orderby	int		Order by
SetHigherAmount	decimal(18, 4)		Set higher amount
CreateTime	datetimeoffset		Record creation time
UpdateTime	datetimeoffset		Record update time

PRO_RateMain

Field	Type	Index	Description
MainRateId	int identity	PK	Primary key, unique identifier
ProviderID	int		Foreign key to provider
StartDate	datetime		Start date
EndDate	datetime		End date
Comments	nvarchar(max)		Comments
Disable	bit		Disabled status (0 No, 1 Yes)
CreateUserID	int		ID of the user who created
UpdateUserID	int		ID of the user who updated
Version	int		Version
IsFTHours	bit		Is full-time hours (Y/N)
FTHours	decimal(18, 2)		Full-time hours
CreateTime	datetimeoffset		Record creation time
UpdateTime	datetimeoffset		Record update time

PRO_Holiday

Field	Type	Index	Description
HolidayID	int identity	PK	Primary key, unique identifier
ProviderID	int	IX	Foreign key to provider
ProviderRuleID	int		Foreign key to provider rule
Date	datetime		Holiday date
HolidayType	nvarchar(200)		Type of holiday
IsPaid	bit		Is paid holiday (Y/N)
UpdateTime	datetimeoffset		Record update time

PRO_License

Field	Type	Index	Description
LicenseID	int identity	PK	Primary key, unique identifier
ProviderID	int		Foreign key to provider
LicenseNo	nvarchar(50)		License number
LicenseExpirationDate	datetime		License expiration date
LicenseIssueDate	datetime		License issue date
InsuranceAffidavit	nvarchar(50)		Insurance affidavit
MinAge	decimal(18, 2)		Minimum age
MaxAge	decimal(18, 2)		Maximum age
Capacity	int		Capacity
Status	int		Status
Comments	nvarchar(max)		Comments
History	int		History version
Disable	bit		Disabled status (0 No, 1 Yes)
CreateUserID	int		ID of the user who created
UpateUserID	int		ID of the user who updated
Version	int		Version
EDocID	nvarchar(20)		Electronic document ID
CreateTime	datetimeoffset		Record creation time
UpdateTime	datetimeoffset		Record update time

PRO_QRIS

Field	Type	Index	Description
Id	int identity	PK	Primary key, unique identifier
ProviderId	int	IX	Foreign key to provider
Code	nvarchar(30)		QRIS code
StartDate	datetime		Start date
EndDate	datetime		End date
Disable	bit		Disabled status (0 No, 1 Yes)
CreateUserId	int		ID of the user who created
UpdateUserId	int		ID of the user who updated
Version	int		Version
CreateTime	datetimeoffset		Record creation time
UpdateTime	datetimeoffset		Record update time

PRO_AccreditationStatus

Field	Type	Index	Description
Id	int identity	PK	Primary key, unique identifier
ProviderId	int	IX	Foreign key to provider
Code	nvarchar(30)		Accreditation status code
StartDate	datetime		Start date
EndDate	datetime		End date
Disable	bit		Disabled status (0 No, 1 Yes)
CreateUserId	int		ID of the user who created
UpdateUserId	int		ID of the user who updated
Version	int		Version
CreateTime	datetimeoffset		Record creation time
UpdateTime	datetimeoffset		Record update time

PRO_RegistrationFee

Field	Type	Index	Description
RegistrationFeeID	int identity	PK	Primary key, unique identifier
ProviderID	int	IX	Foreign key to provider
MinAge	decimal(18, 2)		Minimum age
MaxAge	decimal(18, 2)		Maximum age
RegistrationFee	decimal(18, 2)		Registration fee
Status	int		Status
Comments	nvarchar(max)		Comments
StartDate	datetime		Start date
EndDate	datetime		End date
History	int		History version
Disable	bit		Disabled status (0 No, 1 Yes)
CreateUserID	int		ID of the user who created
UpateUserID	int		ID of the user who updated
Version	int		Version
IsEffective	bit		Is effective
RegistrationFeeType	nvarchar(2048)		Registration fee type
CreateTime	datetimeoffset		Record creation time
UpdateTime	datetimeoffset		Record update time