Claim

Introduction

This section introduces the structure of the ‘claim’ 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.

PAY_Claim

Field	Type	Index	Description
ActualAmount	decimal(18, 2)		Actual amount
ActualFamilyFee	decimal(18, 2)		Actual family fee
AdjustmentAmount	decimal(18, 2)		Adjustment amount
AdjustmentComment	nvarchar(max)		Adjustment comment
AdjustmentReason	varchar(max)		Adjustment reason
AdjustmentSeq	int		Adjustment sequence
Amount	decimal(18, 2)		Amount
CalculateUser	int		User who calculated
CaseID	int		Case ID
CheckUser	int		User who checked
ChildID	int	IX	Child ID
ClaimCode	nvarchar(100)	IX	Claim code (yyyymmdd+ID(5))
ClaimID	int identity	PK	Primary key, unique identifier
Comment	nvarchar(max)		Comment
ContractID	int	IX	Contract ID
CreateUserID	int		ID of the user who created
Disable	bit	IX	Disabled status (0 No, 1 Yes)
EndDate	datetime	IX	End date
FamilyFee	decimal(18, 2)		Family fee
FamilyID	int		Family ID
FFCollected	decimal(18, 2)		Family fee collected
FFOverPayment	bit		Family fee overpayment
FFPlan	bit		Family fee plan
HasAdjustment	bit		Has adjustment (0 No, 1 Yes)
IsPay2Parent	bit		Is pay to parent (0 No, 1 Yes)
IsReplaced	bit		Is replaced
IsSDM	bit		Is SDM (0 No, 1 Yes)
Month	int		Month
OwnerId	int		Owner ID
ProviderID	int	IX	Provider ID
Quarter	int		Quarter
SDMComment	nvarchar(max)		SDM comment
SDMDays	int		SDM days
StartDate	datetime	IX	Start date
Status	int	IX	Status (0 New, 1 Request Adjustment, 2 Calculated, 3 Return)
TotalDays	int		Total days
UpateUserID	int		ID of the user who updated
Version	int		Version
WeekNo	int		Week number
Year	int		Year
RegistrationFee	decimal(18, 4)		Registration fee
FullTimePlusFee	decimal(18, 4)		Full-time plus fee
DropInCareFee	decimal(18, 4)		Drop-in care fee
FTPlusAmount	decimal(18, 2)		Full-time plus amount
ActualFTPlusAmount	decimal(18, 2)		Actual full-time plus amount
DropInAmount	decimal(18, 2)		Drop-in amount
ActualDropInAmount	decimal(18, 2)		Actual drop-in amount
CareID	varchar(50)		Care ID
AuthType	int		Authorization type
SDMIsSys	bit		SDM is system
AdjustmentSeqNew	int		New adjustment sequence
IsCap	bit		Is capped
PeriodStart	datetime	IX	Period start
PeriodEnd	datetime	IX	Period end
ClaimInfo	nvarchar(max)		Claim information
FFReciptNo	nvarchar(100)		Family fee receipt number
IsLicense	bit		Is licensed
PvFullTimePlusHours	decimal(18, 4)		Provider full-time plus hours
HasChange	bit		Has change
ManualAdjustAmount	decimal(18, 2)		Manual adjustment amount
PayDate	datetime	IX	Payment date
RegistrationFee_Sys	decimal(18, 2)		System registration fee
RegistrationFee_Manual	decimal(18, 2)		Manual registration fee
PVTotalAdjustmentAmount	decimal(18, 2)		Provider total adjustment amount
FFAdjustmentAmount	decimal(18, 2)		Family fee adjustment amount
IsUDF	int		Is user-defined field
ClassroomId	int		Classroom ID
TotalAdjustDay	decimal(18, 4)		Total adjustment days
ActualAdjustDay	decimal(18, 4)		Actual adjustment days
OriginalAmount	decimal(18, 4)		Original amount
SpecialNeedFactor	decimal(18, 4)		Special need factor
HoldReason	nvarchar(max)		Hold reason
TotalAttHours	decimal(18, 2)		Total attendance hours
DigitalSubmission	bit	IX	Digital submission
SubmitDate	datetime	IX	Submission date
MissingDailySiguature	int		Missing daily signature
MissingConfirmSiguature	int		Missing confirmation signature
AttendanceSource	int		Attendance source (1: System, 2: Manual Input, 3: Import)
ChildCareAmount	decimal(18, 2)		Childcare amount
QAOwnerId	int		QA owner ID
RowVersion	timestamp		Row version
ClaimVersion	int default 1		Claim version
CreateTime	datetimeoffset		Record creation time
UpdateTime	datetimeoffset		Record update time
Meta	nvarchar(max)		Metadata
InvoiceAmount	decimal(38, 6)		Invoice amount
ContractCode	nvarchar(200)	IX	Contract code
ReturnDate	datetimeoffset		Return date
OtherAmount	decimal(38, 6)		Other amount
OtherAmountDescription	nvarchar(max)		Other amount description
ReturnReason	nvarchar(2048)		Return reason
OtherReason	nvarchar(max)		Other reason

Pay_ClaimExtension

Extends claim information for custom fields

Pay_ClaimAmount

Field	Type	Index	Description
ClaimAmountId	int identity	PK	Primary key, unique identifier
ClaimId	int		Foreign key to claim
ChildId	int		Foreign key to child
ProviderId	int		Foreign key to provider
IsSystem	bit		Is system generated (Y/N)
RateCategory	int		Rate category
RateType	int		Rate type
TotalAmount	decimal(18, 2)		Total amount
IsPreferred	bit		Is preferred rate (Y/N)
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
RateCategoryDisplay	nvarchar(50)		Display name for rate category
RateTypeDisplay	nvarchar(50)		Display name for rate type
CreateTime	datetimeoffset		Record creation time
UpdateTime	datetimeoffset		Record update time
CareId	int		Foreign key to care

PAY_ClaimWeek

Field	Type	Index	Description
ClaimWeekId	int identity	PK	Primary key, unique identifier
ClaimItemId	int	IX	Foreign key to claim item
ClaimID	int	IX	Foreign key to claim
ChildID	int		Foreign key to child
ProviderID	int		Foreign key to provider
StartDate	datetime		Start date
EndDate	datetime		End date
RMREW	int		RMR EW
RMRAgeGroup	int		RMR age group
RMRRateType	nvarchar(32)		RMR rate type
RMRRate	decimal(18, 10)		RMR rate
RMRQty	decimal(18, 10)		RMR quantity
RMRFee	decimal(18, 2)		RMR fee
PVEW	int		PV EW
PVAgeGroup	int		PV age group
PVRateType	nvarchar(32)		PV rate type
PVRate	decimal(18, 10)		PV rate
PVQty	decimal(18, 10)		PV quantity
PVFee	decimal(18, 2)		PV fee
UsePV	int		Use PV
EW	int		EW
AgeGroup	int		Age group
RateType	nvarchar(32)		Rate type
Rate	decimal(18, 10)		Rate
Qty	decimal(18, 10)		Quantity
Amount	decimal(18, 10)		Amount
ActualUsePV	int		Actual use PV
ActualEW	int		Actual EW
ActualAgeGroup	int		Actual age group
ActualRateType	nvarchar(500)		Actual rate type
ActualRate	decimal(18, 10)		Actual rate
ActualQty	decimal(18, 10)		Actual quantity
ActualAmount	decimal(18, 10)		Actual amount
Proration	decimal(18, 4)		Proration
EWFactor	decimal(18, 4)		EW factor
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
ClaimAmountId	int	IX	Foreign key to claim amount
IsSystem	int		Is system
Discount	decimal(18, 4)		Discount
Adjustment	decimal(18, 2)		Adjustment
AgeGroupDisplay	varchar(500)		Age group display name
RMRAgeGroupDisplay	varchar(500)		RMR age group display name
CreateTime	datetimeoffset		Record creation time
UpdateTime	datetimeoffset		Record update time
CareId	int		Foreign key to care
RateCategory	nvarchar(2048)		Rate category

PAY_ScheduledDate

Field	Type	Index	Description
ScheduleDayID	uniqueidentifier	PK	Primary key, unique identifier
CareID	int	IX	Foreign key to care
ChildID	int	IX	Foreign key to child
ProviderID	int	IX	Foreign key to provider
Date	datetime	IX	Date
Scheduled	bit		Scheduled status (Y/N)
NormalHours	decimal(18, 2)		Normal hours
AfterHours	decimal(18, 2)		After hours
TotalHours	decimal(18, 2)		Total hours
TimeStamp	datetime		Timestamp
DayAttendanceType	nvarchar(32)		Day attendance type
Status	int		Status
Comment	nvarchar(max)		Comment
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
ClaimID	int	IX	Foreign key to claim
ClassroomId	int		Classroom ID
CreateTime	datetimeoffset		Record creation time
UpdateTime	datetimeoffset		Record update time

PAY_ScheduleDaily

Field	Type	Index	Description
ScheduleDailyID	uniqueidentifier	PK	Primary key, unique identifier
CareID	int	IX	Foreign key to care
ChildID	int	IX	Foreign key to child
ProviderID	int	IX	Foreign key to provider
Date	datetime	IX	Date
WeekyNo	int		Weekly number
DayOfWeek	int		Day of the week
SignIn	datetime	IX	Sign in time
SignOut	datetime	IX	Sign out time
Status	int		Status
NormalHours	decimal(18, 2)		Normal hours
AfterHours	decimal(18, 2)		After hours
DayAttendanceType	nvarchar(32)		Day attendance type
Comment	nvarchar(max)		Comment
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
ClaimID	int	IX	Foreign key to claim
ClassroomId	int		Classroom ID
IsDropInCare	bit		Is drop-in care
DropInHours	decimal(18, 2)		Drop-in hours
CreateTime	datetimeoffset		Record creation time
UpdateTime	datetimeoffset		Record update time

Pay_ClaimELS

Field	Type	Index	Description
ClaimELSId	int identity	PK	Primary key, unique identifier
ClaimId	int		Foreign key to claim
FundType	int		Fund type
Description	nvarchar(500)		Description
OtherAmount	decimal(18, 2)		Other amount
PostAmount	decimal(18, 2)		Post amount
OrderNo	int		Order number
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
OriginalAmount	decimal(38, 6)		Original amount

Pay_FFAssignment

Field	Type	Index	Description
FFAId	int identity	PK	Primary key, unique identifier
CaseId	int default 0		Foreign key to case
ChildId	int		Foreign key to child
CareId	int	IX	Foreign key to care
Month	date	IX	Month of assignment
FF	varchar(2)		Family fee
TotalHours	int		Total hours
Disable	bit	IX	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
FamilyId	int default 0		Foreign key to family
CreateTime	datetimeoffset		Record creation time
UpdateTime	datetimeoffset		Record update time