BANKACCOUNTTRANSACTION
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| ID | uniqueidentifier | ||
| AMOUNT | money | ||
| TRANSACTIONDATE | datetime | yes | |
| TRANSACTIONNUMBER | int | ||
| TRANSACTIONTYPECODE | tinyint | ||
| REFERENCE | nvarchar(100) | ||
| TRANSACTIONFLAGCODE | tinyint | ||
| POSTSTATUSCODE | tinyint | ||
| POSTDATE | datetime | yes | |
| DELETED | bit | ||
| DELETEDDATE | datetime | yes | |
| ADDEDBYID | uniqueidentifier | ||
| CHANGEDBYID | uniqueidentifier | ||
| DATEADDED | datetime | ||
| DATECHANGED | datetime | ||
| TS | timestamp | ||
| TSLONG | bigint | yes | |
| BANKACCOUNTID | uniqueidentifier | ||
| TRANSFERBANKACCOUNTID | uniqueidentifier | yes | |
| GENERALLEDGERTRANSACTION | int | ||
| ORIGINATINGID | uniqueidentifier | yes | |
| TRANSACTIONTYPE | nvarchar(23) | yes | |
| TRANSACTIONFLAG | nvarchar(18) | yes | |
| PROCESSING | bit | ||
| POSTSTATUS | nvarchar(11) | yes | |
| STATUSCODE | tinyint | ||
| STATUS | nvarchar(11) | yes | |
| CLEAREDDATE | datetime | yes | |
| VOIDDATETYPECODE | tinyint | ||
| VOIDDATETYPE | nvarchar(17) | yes | |
| VOIDDATE | datetime | yes | |
| REVERSALPOSTDATE | datetime | yes | |
| REVERSALPOSTDATETYPECODE | tinyint | ||
| REVERSALPOSTDATETYPE | nvarchar(17) | yes | |
| REVERSALPOSTSTATUSCODE | tinyint | ||
| REVERSALPOSTSTATUS | nvarchar(10) | yes | |
| BASECURRENCYID | uniqueidentifier | yes | |
| ORGANIZATIONAMOUNT | money | ||
| ORGANIZATIONEXCHANGERATEID | uniqueidentifier | yes | |
| TRANSACTIONAMOUNT | money | ||
| BASEEXCHANGERATEID | uniqueidentifier | yes |
Definition
Copy
CREATE view BANKACCOUNTTRANSACTION
with schemabinding as select
EXT.ID
,FT.BASEAMOUNT as AMOUNT
,cast(FT.[DATE] as datetime) as TRANSACTIONDATE
,EXT.TRANSACTIONNUMBER
,EXT.TRANSACTIONTYPECODE
,isnull(cast(left(FT.DESCRIPTION,100) as nvarchar(100)),'') as REFERENCE
,EXT.TRANSACTIONFLAGCODE
,isnull(cast(case FT.POSTSTATUSCODE when 1 then 1 when 2 then 0 when 3 then 2 end as tinyint), 2 ) as POSTSTATUSCODE
,cast(FT.POSTDATE as datetime) as POSTDATE
,isnull(cast(case when FT.DELETEDON IS null then 0 else 1 end as bit ),1) as DELETED
,FT.DELETEDON as DELETEDDATE
,EXT.ADDEDBYID
,EXT.CHANGEDBYID
,EXT.DATEADDED
,EXT.DATECHANGED
,FT.TS
,FT.TSLONG
,EXT.BANKACCOUNTID
,EXT.TRANSFERBANKACCOUNTID
,isnull(cast(case when FT.TYPECODE in (10,11,14) then 1 else 2 end as int),2) as GENERALLEDGERTRANSACTION
,EXT.ORIGINATINGID
,cast(EXT.TRANSACTIONTYPE as nvarchar(23)) as TRANSACTIONTYPE
,EXT.TRANSACTIONFLAG
,EXT.PROCESSING
,FT.POSTSTATUS
,EXT.STATUSCODE
,EXT.STATUS
,EXT.CLEAREDDATE
,EXT.VOIDDATETYPECODE
,EXT.VOIDDATETYPE
,EXT.VOIDDATE
,EXT.REVERSALPOSTDATE
,EXT.REVERSALPOSTDATETYPECODE
,EXT.REVERSALPOSTDATETYPE
,EXT.REVERSALPOSTSTATUSCODE
,EXT.REVERSALPOSTSTATUS
,case when FT.DELETEDON is null then V.BASECURRENCYID else null end as BASECURRENCYID
,FT.ORGAMOUNT as ORGANIZATIONAMOUNT
,FT.ORGEXCHANGERATEID as ORGANIZATIONEXCHANGERATEID
,FT.TRANSACTIONAMOUNT as TRANSACTIONAMOUNT
,FT.BASEEXCHANGERATEID as BASEEXCHANGERATEID
-- These are deprecated fields.
from dbo.BANKACCOUNTTRANSACTION_EXT as EXT
inner join dbo.FINANCIALTRANSACTION as FT
on EXT.ID = FT.ID
inner join dbo.V_BASECURRENCYFORFINANCIALTRANSACTION_I V with (noexpand) on FT.ID = V.FINANCIALTRANSACTIONID