FINANCIALTRANSACTION
All financial transaction records.
Primary Key
| Primary Key | Field Type |
|---|---|
| ID | uniqueidentifier |
Fields
| Field | Field Type | Null | Notes | Description |
|---|---|---|---|---|
| USERDEFINEDID | nvarchar(100) | Default = '' | User defined identification number | |
| TYPECODE | tinyint | Default = 101 | Transaction type | |
| TRANSACTIONAMOUNT | money | Default = 0 | Amount of transaction | |
| DATE | datetimeoffset | Date of transaction | ||
| DATEADDED | datetime | Default = getdate() | Indicates the date this record was added. | |
| DATECHANGED | datetime | Default = getdate() | Indicates the date this record was last changed. | |
| TS | timestamp | Timestamp. | ||
| TSLONG | bigint (Computed) | yes | CONVERT(bigint, TS) | Numeric representation of the timestamp. |
| DESCRIPTION | nvarchar(700) | Default = '' | User defined description for a transaction. | |
| POSTDATE | date | yes | Post date of transaction | |
| POSTSTATUSCODE | tinyint | Default = 1 | Post status | |
| POSTSTATUS | nvarchar(11) (Computed) | yes | CASE [POSTSTATUSCODE] WHEN 1 THEN N'Not posted' WHEN 2 THEN N'Posted' WHEN 3 THEN N'Do not post' END | Provides a translation for the 'POSTSTATUSCODE' field. |
| DELETEDON | datetime | yes | Date and time of when the transaction was deleted | |
| BASEAMOUNT | money | Default = 0 | ||
| ORGAMOUNT | money | Default = 0 | ||
| SEQUENCEGENERATORID | int | |||
| TYPE | nvarchar(27) (Computed) | yes | CASE [TYPECODE] WHEN 101 THEN N'Invoice' WHEN 102 THEN N'Credit memo' WHEN 255 THEN N'System Transaction' WHEN 103 THEN N'Journal entry batch' WHEN 254 THEN N'Reversal' WHEN 104 THEN N'Charge' WHEN 105 THEN N'AP Payment' WHEN 106 THEN N'Credit' WHEN 0 THEN N'Payment' WHEN 1 THEN N'Pledge' WHEN 2 THEN N'Recurring gift' WHEN 3 THEN N'Matching gift claim' WHEN 4 THEN N'Planned gift' WHEN 5 THEN N'Order' WHEN 6 THEN N'Grant award' WHEN 7 THEN N'Auction donation' WHEN 8 THEN N'Donor challenge claim' WHEN 28 THEN N'Computer check' WHEN 9 THEN N'Pending Gift' WHEN 10 THEN N'Deposit' WHEN 11 THEN N'Adjustment deposit' WHEN 12 THEN N'Adjustment payment' WHEN 13 THEN N'Adjustment transfer out' WHEN 14 THEN N'Adjustment transfer in' WHEN 15 THEN N'Membership installment plan' WHEN 20 THEN N'Write off' WHEN 21 THEN N'Sold stock' WHEN 22 THEN N'Sold property' WHEN 23 THEN N'Refund' WHEN 24 THEN N'Deposit Correction Short' WHEN 25 THEN N'Deposit Correction Over' WHEN 26 THEN N'Payout' WHEN 27 THEN N'Sold Gift In Kind' WHEN 99 THEN N'Deleted Revenue' END | |
| CALCULATEDUSERDEFINEDID | nvarchar(100) (Computed) | yes | (case when [TYPECODE]<(10) or [TYPECODE] = 15 then case len([USERDEFINEDID]) when (0) then 'rev-'+CONVERT([nvarchar](20),[SEQUENCEGENERATORID],(0)) else [USERDEFINEDID] end else [USERDEFINEDID] end) | |
| CALCULATEDDATE | date (Computed) | yes | convert(date,[DATE]) |
Foreign Keys
| Foreign Key | Field Type | Null | Notes | Description |
|---|---|---|---|---|
| CONSTITUENTID | uniqueidentifier | yes | CONSTITUENT.LOCALID | Foreign Key to the Constituent Table |
| ADDEDBYID | uniqueidentifier | CHANGEAGENT.ID | FK to CHANGEAGENT. | |
| CHANGEDBYID | uniqueidentifier | CHANGEAGENT.ID | FK to CHANGEAGENT. | |
| PARENTID | uniqueidentifier | yes | FINANCIALTRANSACTION.ID | Points to the parent transaction. |
| TRANSACTIONCURRENCYID | uniqueidentifier | CURRENCY.ID | FK to CURRENCY | |
| BASEEXCHANGERATEID | uniqueidentifier | yes | CURRENCYEXCHANGERATE.ID | FK to CURRENCYEXCHANGERATE |
| ORGEXCHANGERATEID | uniqueidentifier | yes | CURRENCYEXCHANGERATE.ID | FK to CURRENCYEXCHANGERATE |
| PDACCOUNTSYSTEMID | uniqueidentifier | yes | PDACCOUNTSYSTEM.ID | FK to PDACCOUNTSYSTEM |
| APPUSERID | uniqueidentifier | yes | APPUSER.ID |
Indexes
| Index Name | Fields | Unique | Primary | Clustered |
|---|---|---|---|---|
| IX_FINANCIALTRANSACTION_CALCULATEDDATE | CALCULATEDDATE | |||
| IX_FINANCIALTRANSACTION_CALCULATEDUSERDEFINEDID | CALCULATEDUSERDEFINEDID | |||
| IX_FINANCIALTRANSACTION_CONSTITUENTID_TYPECODE_DATE | CONSTITUENTID, TYPECODE, DATE | |||
| IX_FINANCIALTRANSACTION_DATE | DATE | |||
| IX_FINANCIALTRANSACTION_DATEADDED | DATEADDED | yes | ||
| IX_FINANCIALTRANSACTION_DATECHANGED | DATECHANGED | |||
| IX_FINANCIALTRANSACTION_DATECHANGED_CONSTITUENTID | DATECHANGED, CONSTITUENTID | |||
| IX_FINANCIALTRANSACTION_DELETEDON_CONSTITUENTID | DELETEDON, CONSTITUENTID | |||
| IX_FINANCIALTRANSACTION_DELETEDON_DATE | DELETEDON, DATE | |||
| IX_FINANCIALTRANSACTION_ID_TYPECODE | ID, TYPECODE | |||
| IX_FINANCIALTRANSACTION_PARENTID | PARENTID | |||
| IX_FINANCIALTRANSACTION_PDACCOUNTSYSTEMID_TYPECODE_POSTSTATUSCODE | PDACCOUNTSYSTEMID, TYPECODE, POSTSTATUSCODE | |||
| IX_FINANCIALTRANSACTION_TYPECODE_DELETEDON_PARENTID_TRANSACTIONAMOUNT_BASEAMOUNT | TYPECODE, DELETEDON, PARENTID, TRANSACTIONAMOUNT, BASEAMOUNT | |||
| IX_FINANCIALTRANSACTION_USERDEFINEDID | USERDEFINEDID | |||
| IX_FINANCIALTRANSACTION_SEQUENCEGENERATORID | SEQUENCEGENERATORID | yes | ||
| PK_FINANCIALTRANSACTION | ID | yes | yes | |
| UC_FINANCIALTRANSACTION_USERDEFINEDID | USERDEFINEDID | yes |