CREDITGLDISTRIBUTION

Fields

Field	Field Type	Null
ID	uniqueidentifier
REFERENCE	nvarchar(255)
AMOUNT	money
ACCOUNT	nvarchar(100)
TRANSACTIONTYPECODE	tinyint
GLTRANSACTIONID	uniqueidentifier	yes
CREDITITEMID	uniqueidentifier	yes
ADDEDBYID	uniqueidentifier
CHANGEDBYID	uniqueidentifier
DATEADDED	datetime
DATECHANGED	datetime
TS	timestamp
TSLONG	bigint	yes
OUTDATED	bit
REVENUEID	uniqueidentifier	yes
TRANSACTIONTYPE	nvarchar(6)	yes
CREDITPAYMENTID	uniqueidentifier	yes
DISCOUNTCREDITITEMID	uniqueidentifier	yes
Definition

Copy

    CREATE view dbo.CREDITGLDISTRIBUTION with schemabinding 
    as 
      select 
        JOURNALENTRY_EXT.GLTRANSACTIONID as ID
        ,isnull(convert(nvarchar(255),JOURNALENTRY.COMMENT),'') as REFERENCE
        ,JOURNALENTRY.TRANSACTIONAMOUNT as AMOUNT
        ,isnull(GLACCOUNT.ACCOUNTNUMBER,'') as ACCOUNT
        ,JOURNALENTRY.TRANSACTIONTYPECODE
        --,case when JOURNALENTRY.ID  = '00000000-0000-0000-0000-000000000000' then null else JOURNALENTRY.ID end as GLTRANSACTIONID
        ,JOURNALENTRY.ID as GLTRANSACTIONID
        ,FINANCIALTRANSACTIONLINEITEM.ID [CREDITITEMID]
        ,JOURNALENTRY_EXT.ADDEDBYID
        ,JOURNALENTRY_EXT.CHANGEDBYID
        ,JOURNALENTRY_EXT.DATEADDED
        ,JOURNALENTRY_EXT.DATECHANGED
        ,JOURNALENTRY_EXT.TS
        ,JOURNALENTRY_EXT.TSLONG
        ,JOURNALENTRY_EXT.OUTDATED
        ,FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID as REVENUEID
        ,cast(case JOURNALENTRY.TRANSACTIONTYPECODE when 0 then 'Debit' else 'Credit' end as nvarchar(6)) as TRANSACTIONTYPE
        ,JOURNALENTRY_EXT.CREDITPAYMENTID
        ,JOURNALENTRY_EXT.DISCOUNTCREDITITEMID
    from 
      dbo.JOURNALENTRY     
      inner join dbo.JOURNALENTRY_EXT 
            on JOURNALENTRY.ID = JOURNALENTRY_EXT.ID
      inner join dbo.FINANCIALTRANSACTIONLINEITEM 
            on JOURNALENTRY.FINANCIALTRANSACTIONLINEITEMID = FINANCIALTRANSACTIONLINEITEM.ID and FINANCIALTRANSACTIONLINEITEM.TYPECODE = 5
            inner join dbo.FINANCIALTRANSACTION 
                        on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID and FINANCIALTRANSACTION.TYPECODE = 5
      left join dbo.GLACCOUNT 
            on JOURNALENTRY.GLACCOUNTID = GLACCOUNT.ID
  union
    select 
        isnull(JOURNALENTRY_EXT.DISTRIBUTIONTABLEID,JOURNALENTRY_EXT.ID) as ID
        ,isnull(convert(nvarchar(255),JOURNALENTRY.COMMENT),'')
        ,JOURNALENTRY.TRANSACTIONAMOUNT as AMOUNT
        ,isnull(GLACCOUNT.ACCOUNTNUMBER,'')
        ,JOURNALENTRY.TRANSACTIONTYPECODE
        ,case when JOURNALENTRY.ID  = '00000000-0000-0000-0000-000000000000' then null else JOURNALENTRY.ID end as GLTRANSACTIONID
        ,case when FINANCIALTRANSACTIONLINEITEM.TYPECODE = 5 or JOURNALENTRY_EXT.DISCOUNTCREDITITEMID is not null then JOURNALENTRY_EXT.CREDITITEMID else null end [CREDITITEMID]
        ,JOURNALENTRY_EXT.ADDEDBYID
        ,JOURNALENTRY_EXT.CHANGEDBYID
        ,JOURNALENTRY_EXT.DATEADDED
        ,JOURNALENTRY_EXT.DATECHANGED
        ,JOURNALENTRY_EXT.TS
        ,JOURNALENTRY_EXT.TSLONG
        ,JOURNALENTRY_EXT.OUTDATED
        ,NULL REVENUEID
        ,case JOURNALENTRY.TRANSACTIONTYPECODE when 0 then 'Debit' else 'Credit' end as TRANSACTIONTYPE
        ,JOURNALENTRY_EXT.CREDITPAYMENTID
        ,JOURNALENTRY_EXT.DISCOUNTCREDITITEMID
    from dbo.JOURNALENTRY 
    inner join dbo.JOURNALENTRY_EXT on JOURNALENTRY.ID = JOURNALENTRY_EXT.ID
    inner join dbo.FINANCIALTRANSACTIONLINEITEM on JOURNALENTRY.FINANCIALTRANSACTIONLINEITEMID = FINANCIALTRANSACTIONLINEITEM.ID
    inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID and FINANCIALTRANSACTION.TYPECODE = 23
    left join dbo.GLACCOUNT on JOURNALENTRY.GLACCOUNTID = GLACCOUNT.ID
  union
    select 
        isnull(JOURNALENTRY_EXT.DISTRIBUTIONTABLEID,JOURNALENTRY_EXT.ID) as ID
        ,isnull(convert(nvarchar(255),JOURNALENTRY.COMMENT),'')
        ,JOURNALENTRY.TRANSACTIONAMOUNT as AMOUNT
        ,isnull(GLACCOUNT.ACCOUNTNUMBER,'')
        ,JOURNALENTRY.TRANSACTIONTYPECODE
        ,case when JOURNALENTRY.ID  = '00000000-0000-0000-0000-000000000000' then null else JOURNALENTRY.ID end as GLTRANSACTIONID
        ,JOURNALENTRY_EXT.CREDITITEMID
        ,JOURNALENTRY_EXT.ADDEDBYID
        ,JOURNALENTRY_EXT.CHANGEDBYID
        ,JOURNALENTRY_EXT.DATEADDED
        ,JOURNALENTRY_EXT.DATECHANGED
        ,JOURNALENTRY_EXT.TS
        ,JOURNALENTRY_EXT.TSLONG
        ,JOURNALENTRY_EXT.OUTDATED
        ,JOURNALENTRY_EXT.LOGICALREVENUEID as REVENUEID
        ,case JOURNALENTRY.TRANSACTIONTYPECODE when 0 then 'Debit' else 'Credit' end as TRANSACTIONTYPE
        ,JOURNALENTRY_EXT.CREDITPAYMENTID
        ,JOURNALENTRY_EXT.DISCOUNTCREDITITEMID
    from dbo.JOURNALENTRY 
    inner join dbo.JOURNALENTRY_EXT on JOURNALENTRY.ID = JOURNALENTRY_EXT.ID and JOURNALENTRY_EXT.TABLENAMECODE = 6
    inner join dbo.FINANCIALTRANSACTIONLINEITEM on JOURNALENTRY.FINANCIALTRANSACTIONLINEITEMID = FINANCIALTRANSACTIONLINEITEM.ID
    inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID and FINANCIALTRANSACTION.TYPECODE = 99
    left join dbo.GLACCOUNT on JOURNALENTRY.GLACCOUNTID = GLACCOUNT.ID