USE [TWO]
GO
/****** Object: View [dbo].[SmartList_Journal_History] Script Date: 09/09/2011 08:22:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE VIEW [dbo].[SmartList_Journal_History] AS
SELECT
['Account Transaction History'].HSTYEAR AS [History Year]
, ['Account Transaction History'].PERIODID AS [Period]
, ['Account Transaction History'].JRNENTRY AS [Journal Entry]
, CASE WHEN ['Source Document Master'].SDOCDSCR IS NULL THEN
['Account Transaction History'].SOURCDOC
ELSE
['Source Document Master'].SDOCDSCR
END AS [Source Document]
,['Account Transaction History'].REFRENCE AS [Journal Reference]
,['Account Transaction History'].DSCRIPTN AS [Distribution Reference]
,['Account Transaction History'].TRXDATE AS [Transaction Date]
,['Account Transaction History'].TRXSORCE AS [Transaction Reference]
,['Account Transaction History'].USWHPSTD AS [User Who Posted]
, CASE ['Account Transaction History'].SERIES
WHEN 2 THEN 'Financial'
WHEN 3 THEN 'Sales'
WHEN 4 THEN 'Purchasing'
WHEN 5 THEN 'Inventory'
WHEN 6 THEN 'Payroll - US'
WHEN 7 THEN 'Project'
END AS [Series]
,['Account Transaction History'].CRDTAMNT AS [Credit]
,['Account Transaction History'].DEBITAMT AS [Debit]
,['Account Index Master'].ACTNUMST AS [Account Number]
,['Account Master'].ACTDESCR AS [Account Description]
,['Account Category Master'].ACCATDSC AS [Account Category]
,['Account Master'].USERDEF1 AS [User Defined 1]
,['Account Master'].USERDEF2 AS [User Defined 2]
,['Account Master'].USRDEFS1 AS [User Defined 3]
,['Account Master'].USRDEFS2 AS [User Defined 4]
,CASE ['Account Master'].PSTNGTYP
WHEN 0 THEN 'Balance Sheet'
ELSE 'Profit & Loss'
END AS [Posting Type]
, CASE ['Account Master'].TPCLBLNC
WHEN 0 THEN 'Debit'
ELSE 'Credit'
END AS [Typical Balance]
FROM
GL30000 AS ['Account Transaction History'] (NOLOCK)
INNER JOIN
GL00100 AS ['Account Master'] (NOLOCK) ON ['Account Master'].ACTINDX = ['Account Transaction History'].ACTINDX
INNER JOIN
GL00105 AS ['Account Index Master'] (NOLOCK) ON ['Account Index Master'].ACTINDX = ['Account Transaction History'].ACTINDX
INNER JOIN
GL00102 AS ['Account Category Master'] (NOLOCK) ON ['Account Category Master'].ACCATNUM = ['Account Master'].ACCATNUM
LEFT JOIN
SY00900 AS ['Source Document Master'] (NOLOCK) ON ['Source Document Master'].SOURCDOC = ['Account Transaction History'].SOURCDOC