Ledger

Introduction

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

FIN_Export

Field	Type	Index	Description
Id	int identity	PK	Primary key, unique identifier
PayDate	datetime		Payment date
UserId	int		User ID
LedgerCount	int		Ledger count
ProviderCount	int		Provider count
ClaimCount	int		Claim count
TotalAmount	decimal(18, 4)		Total amount
CareAmount	decimal(18, 4)		Care amount
GarnishmentAmount	decimal(18, 4)		Garnishment amount
AdjustmentAmount	decimal(18, 4)		Adjustment amount
Memo	nvarchar(max)		Memo
Programe	int		Program (0: CDE, 1: Stage 1)
Status	int		Status
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
FundingSource	nvarchar(1024)		Funding source
CreateTime	datetimeoffset		Record creation time
UpdateTime	datetimeoffset		Record update time
ExportDate	datetimeoffset		Export date
ExportParam	nvarchar(max)		Export parameters
Entities	nvarchar(max)		Entities
FamilyFeeAmount	decimal(38, 6)		Family fee amount
RegisterFeeAmount	decimal(38, 6)		Register fee amount
ProviderAmountZeroCount	int		Provider amount zero count
Meta	nvarchar(max)		Metadata
Steps	nvarchar(max)		Steps
SummaryAmount	nvarchar(max)		Summary amount
ExportVersion	nvarchar(2048)		Export version

FIN_Ledger

Field	Type	Index	Description
LegerId	int identity	PK	Primary key, unique identifier
ProviderID	int	IX	Foreign key to provider
LedgerCode	nvarchar(100)	IX	Ledger code
CustomerName	nvarchar(200)		Customer name
CustomerCode	nvarchar(100)		Customer code
CreateDate	datetime		Create date
PayMethod	nvarchar(32)		Payment method
Amount	decimal(18, 2)		Amount
Memo	nvarchar(max)		Memo
PayDate	datetime	IX	Payment date
Status	int		Status
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
CustomerType	int		Customer type
ContractID	int		Contract ID
ExportId	int	IX	Export ID
EdocId	int		Edoc ID
CreateTime	datetimeoffset		Record creation time
UpdateTime	datetimeoffset		Record update time
FamilyFeeAmount	decimal(38, 6)		Family fee amount
RegisterFeeAmount	decimal(38, 6)		Register fee amount
AdjustmentAmount	decimal(38, 6)		Adjustment amount
ClaimZeroCount	int		Claim zero count
Meta	nvarchar(max)		Metadata
PaymentAmount	decimal(38, 6)		Payment amount
ExportDate	datetimeoffset	IX	Export date
SummaryAmount	nvarchar(max)		Summary amount
ProviderName	nvarchar(2048)		Provider name
ContractCode	nvarchar(2048)		Contract code

FIN_SubLedger

Field	Type	Index	Description
SubLedgerId	int identity	PK	Primary key, unique identifier
LegerID	int	IX	Foreign key to ledger
ExportId	int	IX	Foreign key to export
Type	int	IX	Type (0: Claim, 1: Adjustment, 2: Garnishment)
ClaimId	int	IX	Foreign key to claim
ChildID	int		Foreign key to child
ProviderID	int		Foreign key to provider
CaseID	int	IX	Foreign key to case
ClaimCode	nvarchar(100)		Claim code
ClaimStartDate	datetime		Claim start date
ClaimEndDate	datetime		Claim end date
Title	nvarchar(200)		Title
Price	decimal(18, 4)		Price
Quantity	decimal(18, 4)		Quantity
Amount	decimal(18, 4)		Amount
Memo	nvarchar(200)		Memo
Status	int		Status
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
ContractID	int		Contract ID
IsPay2Parent	bit		Is pay to parent (Y/N)
FamilyFee	decimal(18, 4)		Family fee
PVTotalAmount	decimal(18, 4)	IX	PV total amount
Familyid	int		Foreign key to family
CreateTime	datetimeoffset		Record creation time
UpdateTime	datetimeoffset		Record update time
ContractCode	nvarchar(100)		Contract code
FamilyName	nvarchar(2048)		Family name
ChildName	nvarchar(2048)		Child name
ContractName	nvarchar(2048)		Contract name
ProviderName	nvarchar(2048)		Provider name
TypeName	nvarchar(2048)		Type name
Meta	nvarchar(max)		Metadata
Source	nvarchar(2048)		Source
ExportDate	datetimeoffset	IX	Export date
ItemCode	nvarchar(2048)		Item code
RegistrationFee	decimal(38, 6)		Registration fee
ClaimExternalId	nvarchar(2048)		Claim external ID
FamilyExternalId	nvarchar(2048)		Family external ID
ChildExternalId	nvarchar(2048)		Child external ID
ProviderExternalId	nvarchar(2048)		Provider external ID
ExportExternalId	nvarchar(2048)		Export external ID
ClaimContractID	int		Claim contract ID
ClaimContractCode	nvarchar(100)		Claim contract code
ClaimContractName	nvarchar(2048)		Claim contract name
OtherAmount	decimal(38, 6)		Other amount
PayDate	datetime	IX	Payment date

FIN_SubLedgerItem

Field	Type	Index	Description
Id	int identity	PK	Primary key, unique identifier
ExportId	int		Foreign key to export
Source	nvarchar(2048)		Source
ClaimId	nvarchar(2048)		Claim ID
StartDate	datetime		Start date
EndDate	datetime		End date
ActualAgeGroup	int		Actual age group
ActualRateType	nvarchar(2048)		Actual rate type
ActualRate	decimal(38, 6)		Actual rate
ActualQty	decimal(38, 6)		Actual quantity
ActualAmount	decimal(38, 6)		Actual amount
EwFactor	decimal(38, 6)		EW factor
Discount	decimal(38, 6)		Discount
AgeGroupDisplay	nvarchar(2048)		Age group display
Disable	bit		Disabled status (0 No, 1 Yes)
CreateUserId	bigint		ID of the user who created
CreateTime	datetimeoffset		Record creation time
UpdateUserId	bigint		ID of the user who updated
UpdateTime	datetimeoffset		Record update time
Version	bigint		Version

FIN_Provider

Field	Type	Index	Description
ProviderID	int	PK	Primary key, unique identifier
LastExportDate	datetimeoffset		Last export date
EffectiveDate	datetimeoffset		Effective date
ProviderNO	nvarchar(30)		Provider number
VenderNO	nvarchar(500)		Vendor number
ProviderName	nvarchar(1000)		Provider name
ProviderCategory	nvarchar(32)		Provider category (DICT PC Individual;Corporation)
ProviderType	int		Provider type
FirstName	nvarchar(500)		First name
LastName	nvarchar(500)		Last name
MiddleInitial	nvarchar(10)		Middle initial
MailZip	nvarchar(10)		Mailing zip code
MailAddress	nvarchar(300)		Mailing address
MailUnit	nvarchar(300)		Mailing unit
MailCity	nvarchar(30)		Mailing city
MailCounty	nvarchar(30)		Mailing county
MailState	nvarchar(30)		Mailing state
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
Phone2	nvarchar(35)		Secondary phone
Cell	nvarchar(35)		Cell phone
Fax	nvarchar(20)		Fax
SSN	nvarchar(32)		Social Security Number
TaxID	nvarchar(20)		Tax ID
OtherIDType	nvarchar(32)		Other ID type (DICT PE :0:Driver Lic)
OtherID	nvarchar(100)		Other ID
TaxLegalFirstName	nvarchar(50)		Tax legal first name
TaxLegalMI	nvarchar(50)		Tax legal middle initial
TaxLegalName	nvarchar(300)		Tax legal name
TrustLineStatus	nvarchar(32)		Trust line status
PaymentMethod	nvarchar(32)		Payment method (DICT PL: 0: Check, 1: Debit Card, 2: Direct Deposit)
ProviderManager	int		Provider manager
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
IRSExportDate	datetime		IRS export date
LastProviderCategory	nvarchar(32)		Last provider category
AddressChangeDate	datetimeoffset		Address change date
CreateTime	datetimeoffset		Record creation time
UpdateTime	datetimeoffset		Record update time

Fin_ExternalInvoice

Field	Type	Index	Description
ExternalInvoiceId	char(36)	PK	Primary key, unique identifier
TransId	char(36)	IX	Transaction ID
SequenceNo	bigint		Sequence number
PaymentEntityId	int	IX	Payment entity ID
CollectionAccountId	char(36)		Collection account ID
PostDate	varchar(8)	IX	Post date
DueDate	varchar(8)		Due date
PeriodStart	varchar(8)		Period start
PeriodEnd	varchar(8)		Period end
ParentId	int		Parent ID
InvoiceComment	varchar(1024)		Invoice comment
Status	int default ‘0’		Status
BatchNo	int	IX	Batch number
PaidOutOfBand	bit		Paid out of band
Amount	decimal(18, 2)		Amount
OnlinePay	bit		Online payment
ToEmail	varchar(300)		To email
CollectionAccountCode	varchar(300)		Collection account code
Disable	bit		Disabled status (0 No, 1 Yes)
CreateUserId	int		ID of the user who created
UpdateUserId	int		ID of the user who updated
CreateTime	datetimeoffset	IX	Record creation time
UpdateTime	datetimeoffset	IX	Record update time
Version	int default ‘1’		Version
PortalInvoiceIdentityId	varchar(500)	IX	Portal invoice identity ID
PaymentReceiptNo	varchar(500)	IX	Payment receipt number
LastPullFromPortalTime	datetimeoffset		Last pull from portal time
LastSyncToPortalTime	datetimeoffset		Last sync to portal time

Pay_RegistrationFee

Field	Type	Index	Description
Id	int identity	PK	Primary key, unique identifier
ProviderId	int		Foreign key to provider
ChildId	int	IX	Foreign key to child
FamilyId	int		Foreign key to family
Amount	decimal(18, 4)		Registration fee amount
Balance	decimal(18, 4)		Balance amount
StartDate	datetime		Start date
EndDate	datetime		End date
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
RegistrationFeeType	nvarchar(2048)		Registration fee type
CreateTime	datetimeoffset		Record creation time
UpdateTime	datetimeoffset		Record update time

Pay_RegistrationFeePayment

Field	Type	Index	Description
Id	int identity	PK	Primary key, unique identifier
RegistrationFeeId	int	IX	Foreign key to registration fee
ClaimId	int		Foreign key to claim
ClaimCode	int		Claim code
ClaimDate	datetime		Claim date
Amount	decimal(18, 4)		Payment amount
Balance	decimal(18, 4)		Balance amount
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
Comment	nvarchar(2048)		Comment
CreateTime	datetimeoffset		Record creation time
UpdateTime	datetimeoffset		Record update time
ExportId	int		Export ID
ProviderId	int		Foreign key to provider
ChildId	int		Foreign key to child
FamilyId	int		Foreign key to family

PRO_GarnishmentPayment

Field	Type	Index	Description
Id	int identity	PK	Primary key, unique identifier
ProviderId	int	IX	Foreign key to provider
GarnishmentId	int	IX	Foreign key to garnishment
ExportDate	datetime		Export date
ProcessingFee	decimal(18, 4)		Processing fee
Amount	decimal(18, 4)		Amount
Payment	decimal(18, 4)		Payment
Balance	decimal(18, 4)		Balance
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
ExportId	int		Export ID
Disable	bit		Disabled status (0 No, 1 Yes)
LedgerId	int		Foreign key to ledger