JOURNALENTRY_EXT
Primary Key
| Primary Key | Field Type |
|---|---|
| ID | uniqueidentifier |
Fields
| Field | Field Type | Null | Notes | Description |
|---|---|---|---|---|
| PROJECT | nvarchar(100) | Default = '' | ||
| JOURNAL | nvarchar(255) | Default = '' | ||
| TABLENAMECODE | tinyint | Default = 1 | ||
| REVERSEDATE | datetime | yes | ||
| DISTRIBUTIONTABLEID | uniqueidentifier | yes | ||
| OUTDATED | bit | Default = 0 | ||
| PAYMENTMETHODCODE | tinyint | Default = 1 | ||
| DATEADDED | datetime | Default = getdate() | ||
| DATECHANGED | datetime | Default = getdate() | ||
| TS | timestamp | |||
| TSLONG | bigint (Computed) | yes | CONVERT(bigint, TS) | |
| TABLENAME | nvarchar(42) (Computed) | yes | CASE [TABLENAMECODE] WHEN 0 THEN N'GLTRANSACTION' WHEN 1 THEN N'REVENUEGLDISTRIBUTION' WHEN 2 THEN N'AUCTIONPURCHASEGLDISTRIBUTION' WHEN 3 THEN N'BANKACCOUNTDEPOSITCORRECTIONGLDISTRIBUTION' WHEN 4 THEN N'BANKACCOUNTTRANSACTIONGLDISTRIBUTION' WHEN 5 THEN N'BENEFITGLDISTRIBUTION' WHEN 6 THEN N'CREDITGLDISTRIBUTION' WHEN 7 THEN N'GIFTAIDGLDISTRIBUTION' WHEN 8 THEN N'GIFTFEEGLDISTRIBUTION' WHEN 9 THEN N'PLANNEDGIFTPAYOUTGLDISTRIBUTION' WHEN 10 THEN N'PROPERTYDETAILGLDISTRIBUTION' WHEN 11 THEN N'STOCKSALEGLDISTRIBUTION' WHEN 12 THEN N'WRITEOFFGLDISTRIBUTION' WHEN 13 THEN N'GIFTINKINDSALEGLDISTRIBUTION' WHEN 14 THEN N'UNREALIZEDGAINLOSSGLDISTRIBUTION' END | |
| LOGICALREVENUEID | uniqueidentifier | yes | ||
| REVENUESPLITGIFTFEEID | uniqueidentifier | yes | ||
| REVENUESPLITGIFTAIDID | uniqueidentifier | yes | ||
| ACCOUNT | nvarchar(100) | Default = '' | ||
| PRECALCPOSTSTATUSCODE | tinyint | Default = 1 | ||
| PRECALCPOSTSTATUS | nvarchar(11) (Computed) | yes | CASE [PRECALCPOSTSTATUSCODE] WHEN 1 THEN N'Not posted' WHEN 2 THEN N'Posted' WHEN 3 THEN N'Do not post' END | |
| REVENUEPURCHASEID | uniqueidentifier | yes | ||
| FULLYPAIDSTATUSCODE | tinyint | Default = 0 | ||
| FULLYPAIDSTATUS | nvarchar(18) (Computed) | yes | CASE [FULLYPAIDSTATUSCODE] WHEN 0 THEN N'none' WHEN 1 THEN N'Fully paid' WHEN 2 THEN N'Fully paid backout' END | |
| BENEFITTYPECODE | tinyint | Default = 4 | ||
| BENEFITTYPE | nvarchar(9) (Computed) | yes | CASE [BENEFITTYPECODE] WHEN 1 THEN N'Expense' WHEN 2 THEN N'Liability' WHEN 3 THEN N'Backout' WHEN 4 THEN N'Not Used' END | |
| GLTRANSACTIONID | uniqueidentifier (Computed) | isnull([DISTRIBUTIONTABLEID],[ID]) | ||
| PAYMENTMETHOD | nvarchar(35) (Computed) | yes | CASE [PAYMENTMETHODCODE] WHEN 0 THEN N'Cash' WHEN 1 THEN N'Check' WHEN 2 THEN N'Credit card' WHEN 3 THEN N'Direct debit' WHEN 4 THEN N'Stock' WHEN 5 THEN N'Property' WHEN 6 THEN N'Gift-in-kind' WHEN 7 THEN N'Sold stock' WHEN 8 THEN N'Sold property' WHEN 9 THEN N'None' WHEN 10 THEN N'Other' WHEN 11 THEN N'Standing order' WHEN 12 THEN N'Sold gift-in-kind' WHEN 101 THEN N'PayPal' WHEN 102 THEN N'Venmo' WHEN 200 THEN N'Sold stock gain' WHEN 201 THEN N'Sold property gain' WHEN 202 THEN N'Sold stock loss' WHEN 203 THEN N'Sold property loss' WHEN 204 THEN N'Fees' WHEN 205 THEN N'Write off' WHEN 206 THEN N'All' WHEN 207 THEN N'Sold gift-in-kind revenue addition' WHEN 208 THEN N'Sold gift-in-kind revenue reduction' END |
Foreign Keys
| Foreign Key | Field Type | Null | Notes | Description |
|---|---|---|---|---|
| ID | uniqueidentifier | JOURNALENTRY.ID | ||
| BATCHID | uniqueidentifier | yes | GLBATCH.ID | |
| REVERSEDGLTRANSACTIONID | uniqueidentifier | yes | JOURNALENTRY.ID | |
| REVENUEID | uniqueidentifier | yes | FINANCIALTRANSACTION.ID | |
| GLPAYMENTMETHODREVENUETYPEMAPPINGID | uniqueidentifier | yes | GLPAYMENTMETHODREVENUETYPEMAPPING.ID | |
| WRITEOFFID | uniqueidentifier | yes | FINANCIALTRANSACTION.ID | |
| PROPERTYDETAILID | uniqueidentifier | yes | FINANCIALTRANSACTION.ID | |
| STOCKSALEID | uniqueidentifier | yes | FINANCIALTRANSACTION.ID | |
| ADDEDBYID | uniqueidentifier | CHANGEAGENT.ID | ||
| CHANGEDBYID | uniqueidentifier | CHANGEAGENT.ID | ||
| GIFTINKINDSALEID | uniqueidentifier | yes | FINANCIALTRANSACTION.ID | |
| CREDITITEMID | uniqueidentifier | yes | FINANCIALTRANSACTIONLINEITEM.ID | |
| CREDITPAYMENTID | uniqueidentifier | yes | CREDITPAYMENT.ID | |
| DISCOUNTCREDITITEMID | uniqueidentifier | yes | FINANCIALTRANSACTIONLINEITEM.ID | |
| PLANNEDGIFTPAYOUTID | uniqueidentifier | yes | FINANCIALTRANSACTION.ID | |
| PRECALCORGANIZATIONEXCHANGERATEID | uniqueidentifier | yes | CURRENCYEXCHANGERATE.ID | |
| PRECALCBASEEXCHANGERATEID | uniqueidentifier | yes | CURRENCYEXCHANGERATE.ID |
Indexes
| Index Name | Fields | Unique | Primary | Clustered |
|---|---|---|---|---|
| IX_JOURNALENTRY_EXT_CREDITITEMID | CREDITITEMID | |||
| IX_JOURNALENTRY_EXT_CREDITPAYMENTID | CREDITPAYMENTID | |||
| IX_JOURNALENTRY_EXT_DATEADDED | DATEADDED | yes | ||
| IX_JOURNALENTRY_EXT_DATECHANGED | DATECHANGED | |||
| IX_JOURNALENTRY_EXT_DISCOUNTCREDITITEMID | DISCOUNTCREDITITEMID | |||
| IX_JOURNALENTRY_EXT_DISTRIBUTIONTABLEID | DISTRIBUTIONTABLEID | |||
| IX_JOURNALENTRY_EXT_GIFTINKINDSALEID | GIFTINKINDSALEID | |||
| IX_JOURNALENTRY_EXT_GLTRANSACTIONID | GLTRANSACTIONID | |||
| IX_JOURNALENTRY_EXT_ID | ID | |||
| IX_JOURNALENTRY_EXT_PLANNEDGIFTPAYOUTID | PLANNEDGIFTPAYOUTID | |||
| IX_JOURNALENTRY_EXT_PROPERTYDETAILID | PROPERTYDETAILID | |||
| IX_JOURNALENTRY_EXT_REVENUEID_TABLENAMECODE | REVENUEID, TABLENAMECODE | |||
| IX_JOURNALENTRY_EXT_REVERSEDGLTRANSACTIONID | REVERSEDGLTRANSACTIONID | |||
| IX_JOURNALENTRY_EXT_STOCKSALEID | STOCKSALEID | |||
| IX_JOURNALENTRY_EXT_TABLENAMECODE_ID | TABLENAMECODE, ID | |||
| IX_JOURNALENTRY_EXT_WRITEOFFID | WRITEOFFID | |||
| PK_JOURNALENTRY_EXT | ID | yes | yes |
Triggers
| Trigger Name | Description |
|---|---|
| TR_JOURNALENTRY_EXT_AUDIT_UPDATE | |
| TR_JOURNALENTRY_EXT_AUDIT_DELETE |