GLTRANSACTION
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| ID | uniqueidentifier | ||
| TRANSACTIONTYPECODE | tinyint | ||
| ACCOUNT | nvarchar(100) | ||
| AMOUNT | money | ||
| PROJECT | nvarchar(100) | ||
| REFERENCE | nvarchar(255) | ||
| JOURNAL | nvarchar(255) | ||
| POSTSTATUSCODE | tinyint | ||
| POSTDATE | datetime | yes | |
| REVERSEDGLTRANSACTIONID | uniqueidentifier | yes | |
| REVERSEDATE | datetime | yes | |
| BATCHID | uniqueidentifier | yes | |
| ADDEDBYID | uniqueidentifier | ||
| CHANGEDBYID | uniqueidentifier | ||
| DATEADDED | datetime | ||
| DATECHANGED | datetime | ||
| TS | timestamp | ||
| TSLONG | bigint | yes | |
| TRANSACTIONTYPE | nvarchar(6) | yes | |
| GLACCOUNTID | uniqueidentifier | yes | |
| POSTSTATUS | nvarchar(11) | yes | |
| BASECURRENCYID | uniqueidentifier | yes | |
| ORGANIZATIONAMOUNT | money | ||
| ORGANIZATIONEXCHANGERATEID | uniqueidentifier | yes | |
| TRANSACTIONAMOUNT | money | ||
| TRANSACTIONCURRENCYID | uniqueidentifier | yes | |
| BASEEXCHANGERATEID | uniqueidentifier | yes | |
| SYSTEMDISTRIBUTION | bit | yes | |
| PAYMENTMETHODCODE | tinyint |
Definition
Copy
CREATE view dbo.GLTRANSACTION with schemabinding as
select
JOURNALENTRY.ID,
JOURNALENTRY.TRANSACTIONTYPECODE,
isnull(coalesce(GLACCOUNT.ACCOUNTNUMBER,JOURNALENTRY_EXT.ACCOUNT,''),'') as ACCOUNT,
JOURNALENTRY.BASEAMOUNT as AMOUNT,
JOURNALENTRY_EXT.PROJECT,
JOURNALENTRY.COMMENT as REFERENCE,
JOURNALENTRY_EXT.JOURNAL,
isnull(cast(case isnull(FINANCIALTRANSACTIONLINEITEM.POSTSTATUSCODE ,JOURNALENTRY_EXT.PRECALCPOSTSTATUSCODE)
when 1 then 1
when 2 then 0
when 3 then 2
end as tinyint ),'') as POSTSTATUSCODE,
cast(FINANCIALTRANSACTIONLINEITEM.POSTDATE as datetime) as POSTDATE,
JOURNALENTRY_EXT.REVERSEDGLTRANSACTIONID,
JOURNALENTRY_EXT.REVERSEDATE,
JOURNALENTRY_EXT.BATCHID,
JOURNALENTRY.ADDEDBYID,
JOURNALENTRY.CHANGEDBYID,
JOURNALENTRY.DATEADDED,
JOURNALENTRY.DATECHANGED,
JOURNALENTRY.TS,
JOURNALENTRY.TSLONG,
case JOURNALENTRY.TRANSACTIONTYPECODE
when 0 then N'Debit'
when 1 then N'Credit'
end as TRANSACTIONTYPE,
JOURNALENTRY.GLACCOUNTID,
case isnull(FINANCIALTRANSACTIONLINEITEM.POSTSTATUSCODE,JOURNALENTRY_EXT.PRECALCPOSTSTATUSCODE)
when 1 then N'Not posted'
when 2 then N'Posted'
when 3 then N'Do not post'
end as POSTSTATUS,
case V.BASECURRENCYID
when '00000000-0000-0000-0000-000000000000' then null
else V.BASECURRENCYID end as BASECURRENCYID,
JOURNALENTRY.ORGAMOUNT as ORGANIZATIONAMOUNT,
JOURNALENTRY_EXT.PRECALCORGANIZATIONEXCHANGERATEID as ORGANIZATIONEXCHANGERATEID,
JOURNALENTRY.TRANSACTIONAMOUNT as TRANSACTIONAMOUNT,
case JOURNALENTRY.TRANSACTIONCURRENCYID
when '00000000-0000-0000-0000-000000000000' then null
else JOURNALENTRY.TRANSACTIONCURRENCYID end
as TRANSACTIONCURRENCYID,
JOURNALENTRY_EXT.PRECALCBASEEXCHANGERATEID as BASEEXCHANGERATEID,
cast ((case when JOURNALENTRY.TYPECODE = 0 then 0 else 1 end) as bit) SYSTEMDISTRIBUTION,
JOURNALENTRY_EXT.PAYMENTMETHODCODE
from dbo.JOURNALENTRY
inner join dbo.JOURNALENTRY_EXT on JOURNALENTRY.ID = JOURNALENTRY_EXT.ID
left outer join dbo.FINANCIALTRANSACTIONLINEITEM on JOURNALENTRY.FINANCIALTRANSACTIONLINEITEMID = FINANCIALTRANSACTIONLINEITEM.ID
left outer join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
left join dbo.V_BASECURRENCYFORFINANCIALTRANSACTION_I V with (noexpand) on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = V.FINANCIALTRANSACTIONID
left outer join dbo.GLACCOUNT on JOURNALENTRY.GLACCOUNTID = GLACCOUNT.ID