V_QUERY_POSTTOGLPROCESSSTANDARD_7
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| GLTRANSACTIONID | uniqueidentifier | ||
| ACCOUNTSTRING | nvarchar(100) | ||
| ACCOUNTALIAS | nvarchar(100) | yes | |
| ACCOUNTNUMBER | nvarchar(100) | ||
| POSTDATE | date | yes | |
| ENCUMBRANCESTATUS | nvarchar(7) | yes | |
| TYPE | nvarchar(7) | yes | |
| DEBITCREDIT | varchar(1) | ||
| TRANTYPE | varchar(1) | ||
| AMOUNT | money | ||
| JOURNAL | nvarchar(255) | yes | |
| REFERENCE | nvarchar(255) | ||
| PROJECT | nvarchar(100) | yes | |
| GLACCOUNTID | uniqueidentifier | ||
| TRANSACTIONLINK | varchar(89) | yes |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 1/30/2020 5:55:48 PM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=4.0.2308.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_POSTTOGLPROCESSSTANDARD_7 AS
select
JOURNALENTRY.ID as GLTRANSACTIONID,
GLACCOUNT.ACCOUNTNUMBER as ACCOUNTSTRING,
nullif(GLACCOUNT.ACCOUNTALIAS,'') as ACCOUNTALIAS,
isnull(nullif(GLACCOUNT.ACCOUNTALIAS,''), GLACCOUNT.ACCOUNTNUMBER) as ACCOUNTNUMBER,
FINANCIALTRANSACTIONLINEITEM.POSTDATE,
convert(nvarchar(7),'Regular') as ENCUMBRANCESTATUS,
convert(nvarchar(7),case JOURNALENTRY.TRANSACTIONTYPECODE when 0 then 'Debit' else 'Credit' end) as TYPE,
case JOURNALENTRY.TRANSACTIONTYPECODE when 0 then 'D' else 'C' end as DEBITCREDIT,
'R' as TRANTYPE,
JOURNALENTRY.TRANSACTIONAMOUNT as AMOUNT,
coalesce((select top 1 nullif(CUSTOMJOURNAL,'') from dbo.POSTTOGLPROCESSCUSTOMJOURNAL), (select top 1 nullif(PRODUCT,'') from dbo.INSTALLATIONINFO), JOURNALENTRY_EXT.JOURNAL) as JOURNAL,
JOURNALENTRY.COMMENT as REFERENCE, nullif(JOURNALENTRY_EXT.PROJECT,'') as PROJECT,
GLACCOUNT.ID as GLACCOUNTID,
'pageID=387f861b-6c03-486c-9ff5-9cc5bb7a5275&recordID=' + convert(varchar(36),FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID) as TRANSACTIONLINK
from dbo.JOURNALENTRY inner join dbo.GLACCOUNT on JOURNALENTRY.GLACCOUNTID = GLACCOUNT.ID
left join dbo.FINANCIALTRANSACTIONLINEITEM on JOURNALENTRY.FINANCIALTRANSACTIONLINEITEMID = FINANCIALTRANSACTIONLINEITEM.ID
left join JOURNALENTRY_EXT on JOURNALENTRY.ID = JOURNALENTRY_EXT.ID;