V_QUERY_REVENUEGLDISTRIBUTION

Provides the ability to query on a revenue's GL distribution.
Fields

Field	Field Type	Null	Description
ID	uniqueidentifier		Revenue GL Distribution ID
REVENUEID	uniqueidentifier	yes	Revenue system 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
ACCOUNTALIAS	nvarchar(100)	yes
Definition

Copy
/*
Generated by Blackbaud AppFx Platform
Date:  3/19/2013 1:38:59 AM
Assembly Version:  Blackbaud.AppFx.Platform.SqlClr, Version=3.0.504.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_REVENUEGLDISTRIBUTION AS




with REVENUEGLDISTRIBUTION_CTE as (
    select 
        isnull(JOURNALENTRY_EXT.DISTRIBUTIONTABLEID,JOURNALENTRY_EXT.ID) as ID, 
        FINANCIALTRANSACTION.ID as REVENUEID, 
        case JOURNALENTRY.TRANSACTIONTYPECODE
            when 0 then N'Debit' 
            when 1 then N'Credit' 
        end as TRANSACTIONTYPE,
        isnull(coalesce(GLACCOUNT.ACCOUNTNUMBER,JOURNALENTRY_EXT.ACCOUNT,''),'') as ACCOUNT,
        JOURNALENTRY_EXT.PROJECT,
        case when JOURNALENTRY.TYPECODE <> 0 or JOURNALENTRY.TRANSACTIONCURRENCYID = '00000000-0000-0000-0000-000000000000' then nullif(JOURNALENTRY.BASEAMOUNT, 0) else JOURNALENTRY.BASEAMOUNT end as AMOUNT,
        case V.BASECURRENCYID 
            when '00000000-0000-0000-0000-000000000000' then null
            else V.BASECURRENCYID 
        end as BASECURRENCYID,
        JOURNALENTRY_EXT.PRECALCBASEEXCHANGERATEID as BASEEXCHANGERATEID,
        case when JOURNALENTRY.TYPECODE <> 0 or JOURNALENTRY.TRANSACTIONCURRENCYID = '00000000-0000-0000-0000-000000000000' then nullif(JOURNALENTRY.ORGAMOUNT, 0) else JOURNALENTRY.ORGAMOUNT end ORGANIZATIONAMOUNT,
        JOURNALENTRY_EXT.PRECALCORGANIZATIONEXCHANGERATEID as ORGANIZATIONEXCHANGERATEID,
        case when JOURNALENTRY.TYPECODE <> 0 or JOURNALENTRY.TRANSACTIONCURRENCYID = '00000000-0000-0000-0000-000000000000' then nullif(JOURNALENTRY.TRANSACTIONAMOUNT, 0) else JOURNALENTRY.TRANSACTIONAMOUNT end TRANSACTIONAMOUNT,
        case JOURNALENTRY.TYPECODE 
            when 0 then
                case JOURNALENTRY.TRANSACTIONCURRENCYID 
                    when '00000000-0000-0000-0000-000000000000' then null
                    else JOURNALENTRY.TRANSACTIONCURRENCYID end 
            else null    
        end    as TRANSACTIONCURRENCYID,
        GLACCOUNT.ACCOUNTALIAS,
        FINANCIALTRANSACTION.DELETEDON 
    from 
        dbo.JOURNALENTRY 
        inner join dbo.FINANCIALTRANSACTIONLINEITEM on JOURNALENTRY.FINANCIALTRANSACTIONLINEITEMID = FINANCIALTRANSACTIONLINEITEM.ID 
        inner join dbo.V_BASECURRENCYFORFINANCIALTRANSACTION_I V with (noexpand) on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = V.FINANCIALTRANSACTIONID
        inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID 
        inner join dbo.JOURNALENTRY_EXT on JOURNALENTRY.ID = JOURNALENTRY_EXT.ID and JOURNALENTRY_EXT.TABLENAMECODE = 1
        left outer join dbo.GLACCOUNT on JOURNALENTRY.GLACCOUNTID = GLACCOUNT.ID
    where
        OUTDATED = 0
)
select 
    REVENUEGLDISTRIBUTION_CTE.ID, 
    null REVENUEID, 
    REVENUEGLDISTRIBUTION_CTE.TRANSACTIONTYPE,
    REVENUEGLDISTRIBUTION_CTE.ACCOUNT,
    REVENUEGLDISTRIBUTION_CTE.PROJECT,
    REVENUEGLDISTRIBUTION_CTE.AMOUNT,
    REVENUEGLDISTRIBUTION_CTE.BASECURRENCYID,
    REVENUEGLDISTRIBUTION_CTE.BASEEXCHANGERATEID,
    REVENUEGLDISTRIBUTION_CTE.ORGANIZATIONAMOUNT,
    REVENUEGLDISTRIBUTION_CTE.ORGANIZATIONEXCHANGERATEID,
    REVENUEGLDISTRIBUTION_CTE.TRANSACTIONAMOUNT,
    REVENUEGLDISTRIBUTION_CTE.TRANSACTIONCURRENCYID,
    REVENUEGLDISTRIBUTION_CTE.ACCOUNTALIAS
from 
    REVENUEGLDISTRIBUTION_CTE
where 
    REVENUEGLDISTRIBUTION_CTE.DELETEDON is not null

union all

select 
    REVENUEGLDISTRIBUTION_CTE.ID, 
    REVENUEGLDISTRIBUTION_CTE.REVENUEID, 
    REVENUEGLDISTRIBUTION_CTE.TRANSACTIONTYPE,
    REVENUEGLDISTRIBUTION_CTE.ACCOUNT,
    REVENUEGLDISTRIBUTION_CTE.PROJECT,
    REVENUEGLDISTRIBUTION_CTE.AMOUNT,
    REVENUEGLDISTRIBUTION_CTE.BASECURRENCYID,
    REVENUEGLDISTRIBUTION_CTE.BASEEXCHANGERATEID,
    REVENUEGLDISTRIBUTION_CTE.ORGANIZATIONAMOUNT,
    REVENUEGLDISTRIBUTION_CTE.ORGANIZATIONEXCHANGERATEID,
    REVENUEGLDISTRIBUTION_CTE.TRANSACTIONAMOUNT,
    REVENUEGLDISTRIBUTION_CTE.TRANSACTIONCURRENCYID,
    REVENUEGLDISTRIBUTION_CTE.ACCOUNTALIAS
from 
    REVENUEGLDISTRIBUTION_CTE
where 
    REVENUEGLDISTRIBUTION_CTE.DELETEDON is null

union all

--Auction purchase GL distributions


select 
    isnull(JOURNALENTRY_EXT.DISTRIBUTIONTABLEID,JOURNALENTRY_EXT.ID) as ID, 
    JOURNALENTRY_EXT.REVENUEPURCHASEID, 
    case JOURNALENTRY.TRANSACTIONTYPECODE
      when 0 then N'Debit' 
      when 1 then N'Credit' 
    end as TRANSACTIONTYPE,
    GLACCOUNT.ACCOUNTNUMBER as ACCOUNT, 
    JOURNALENTRY_EXT.PROJECT,
    case when JOURNALENTRY.TYPECODE <> 0 or JOURNALENTRY.TRANSACTIONCURRENCYID = '00000000-0000-0000-0000-000000000000' then nullif(JOURNALENTRY.BASEAMOUNT, 0) else JOURNALENTRY.BASEAMOUNT end [AMOUNT],
    case when CURRENCYSET.BASECURRENCYID = '00000000-0000-0000-0000-000000000000' then null else CURRENCYSET.BASECURRENCYID end BASECURRENCYID,
    FINANCIALTRANSACTION.BASEEXCHANGERATEID,
    case when JOURNALENTRY.TYPECODE <> 0 or JOURNALENTRY.TRANSACTIONCURRENCYID = '00000000-0000-0000-0000-000000000000' then nullif(JOURNALENTRY.ORGAMOUNT, 0) else JOURNALENTRY.ORGAMOUNT end [ORGANIZATIONAMOUNT],
    FINANCIALTRANSACTION.ORGEXCHANGERATEID as ORGANIZATIONEXCHANGERATEID,
    case when JOURNALENTRY.TYPECODE <> 0 or JOURNALENTRY.TRANSACTIONCURRENCYID = '00000000-0000-0000-0000-000000000000' then nullif(JOURNALENTRY.TRANSACTIONAMOUNT, 0) else JOURNALENTRY.TRANSACTIONAMOUNT end [TRANSACTIONAMOUNT],
    JOURNALENTRY.TRANSACTIONCURRENCYID,
    GLACCOUNT.ACCOUNTALIAS
from dbo.JOURNALENTRY 
inner join dbo.FINANCIALTRANSACTIONLINEITEM on JOURNALENTRY.FINANCIALTRANSACTIONLINEITEMID = FINANCIALTRANSACTIONLINEITEM.ID 
inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID 
inner join dbo.JOURNALENTRY_EXT on JOURNALENTRY.ID = JOURNALENTRY_EXT.ID and JOURNALENTRY_EXT.TABLENAMECODE = 2
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
where OUTDATED = 0;