V_QUERY_BANKACCOUNTTRANSACTIONGLDISTRIBUTION
Provides the ability to query for bank account transaction GL distribution.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| ID | uniqueidentifier | Bank account transaction GL Distribution ID | |
| BANKACCOUNTTRANSACTIONID | uniqueidentifier | Bank account transaction ID | |
| TRANSACTIONTYPE | nvarchar(6) | yes | Transaction type |
| ACCOUNT | nvarchar(100) | Account | |
| PROJECT | nvarchar(100) | Project | |
| AMOUNT | money | yes | Amount |
| BASECURRENCYID | uniqueidentifier | yes | Base currency ID |
| BASEEXCHANGERATEID | uniqueidentifier | yes | Base exchange rate |
| ORGANIZATIONAMOUNT | money | yes | Amount (organization currency) |
| ORGANIZATIONEXCHANGERATEID | uniqueidentifier | yes | Organization exchange rate |
| TRANSACTIONAMOUNT | money | yes | Amount (transaction currency) |
| TRANSACTIONCURRENCYID | uniqueidentifier | yes | Transaction currency ID |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 3/19/2013 1:18:08 AM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=3.0.504.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_BANKACCOUNTTRANSACTIONGLDISTRIBUTION AS
select
isnull(JOURNALENTRY_EXT.DISTRIBUTIONTABLEID, '00000000-0000-0000-0000-000000000000') as ID
,FINANCIALTRANSACTION.ID AS BANKACCOUNTTRANSACTIONID
,JOURNALENTRY.TRANSACTIONTYPE
,GLACCOUNT.ACCOUNTNUMBER as ACCOUNT
,JOURNALENTRY_EXT.PROJECT
,case when JOURNALENTRY.TYPECODE <> 0 then nullif(JOURNALENTRY.BASEAMOUNT, 0) else JOURNALENTRY.BASEAMOUNT end [AMOUNT]
,case CURRENCYSET.BASECURRENCYID
when '00000000-0000-0000-0000-000000000000' then null
else CURRENCYSET.BASECURRENCYID end as BASECURRENCYID
,JOURNALENTRY_EXT.PRECALCBASEEXCHANGERATEID BASEEXCHANGERATEID
,case when JOURNALENTRY.TYPECODE <> 0 then nullif(JOURNALENTRY.ORGAMOUNT, 0) else JOURNALENTRY.ORGAMOUNT end [ORGANIZATIONAMOUNT]
,JOURNALENTRY_EXT.PRECALCORGANIZATIONEXCHANGERATEID as ORGANIZATIONEXCHANGERATEID
,case when JOURNALENTRY.TYPECODE <> 0 then nullif(JOURNALENTRY.TRANSACTIONAMOUNT, 0) else JOURNALENTRY.TRANSACTIONAMOUNT end [TRANSACTIONAMOUNT]
,case JOURNALENTRY.TYPECODE when 0 then JOURNALENTRY.TRANSACTIONCURRENCYID else null end as TRANSACTIONCURRENCYID
from dbo.JOURNALENTRY
inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.ID = JOURNALENTRY.FINANCIALTRANSACTIONLINEITEMID
inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
inner join dbo.JOURNALENTRY_EXT on JOURNALENTRY.ID = JOURNALENTRY_EXT.ID and JOURNALENTRY_EXT.TABLENAMECODE = 4
inner join dbo.GLACCOUNT on JOURNALENTRY.GLACCOUNTID = GLACCOUNT.ID
inner join dbo.PDACCOUNTSYSTEM on FINANCIALTRANSACTION.PDACCOUNTSYSTEMID = PDACCOUNTSYSTEM.ID
inner join dbo.CURRENCYSET on PDACCOUNTSYSTEM.CURRENCYSETID = CURRENCYSET.ID