SQL Code for GL Trial Balance

Today, I just decided to post a simple code snippet to generate a detailed GL Trial Balance with reference to the various sub ledgers, including the multi-dimensional information.

IF EXISTS ( SELECT * FROM dbo.sysobjects
WHERE id = OBJECT_ID(N'[dbo].[vw_GLTrialBalance]') AND OBJECTPROPERTY(id, N'IsView') = 1 )
DROP VIEW [dbo].[vw_GLTrialBalance]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE VIEW dbo.vw_GLTrialBalance
AS SELECT dbo.GL00105.ACTNUMST AS [ACCOUNTNUMBER],
dbo.GL00100.ACTDESCR AS ACCOUNTDESCRIPTION,
dbo.GL00102.ACCATDSC AS CATEGORY,
dbo.GL00100.MNACSGMT AS [MAINACCOUNT],
CASE dbo.GL00100.ACCTTYPE
WHEN 1 THEN 'POSTING ACCOUNT'
WHEN 2 THEN 'UNIT ACCOUNT'
WHEN 3 THEN 'POSTING ALLOCATION ACCOUNT'
WHEN 4 THEN 'UNIT ALLOCATION ACCOUNT'
END AS [ACCOUNTTYPE],
CASE dbo.GL00100.ACTIVE
WHEN 1 THEN 'ACTIVE'
WHEN 0 THEN 'INACTIVE'
END AS [STATUS],
CASE dbo.GL00100.PSTNGTYP
WHEN 0 THEN 'BALANCE SHEET'
WHEN 1 THEN 'PROFT AND LOSS'
END AS [POSTINGTYPE],
CASE dbo.GL00100.TPCLBLNC
WHEN 0 THEN 'DEBIT'
WHEN 1 THEN 'CREDIT'
END AS [TYPICALBALANCE],
dbo.GL00100.ACTNUMBR_1 AS SEGMENT1,
dbo.GL00100.ACTNUMBR_2 AS SEGMENT2,
dbo.GL00100.ACTNUMBR_3 AS SEGMENT3,
dbo.GL00100.ACTNUMBR_4 AS SEGMENT4,
dbo.GL00100.ACTNUMBR_5 AS SEGMENT5,
dbo.GL00100.ACTNUMBR_6 AS SEGMENT6,
dbo.GL00100.ACTNUMBR_7 AS SEGMENT7,
dbo.GL00100.ACTNUMBR_8 AS SEGMENT8,
dbo.GL00100.ACTNUMBR_9 AS SEGMENT9,
dbo.GL00100.ACTNUMBR_10 AS SEGMENT10,
dbo.GL20000.OPENYEAR AS [FISCALYEAR],
dbo.GL20000.JRNENTRY AS [JVNUMBER],
dbo.GL20000.REFRENCE AS REFERENCE,
dbo.GL20000.DSCRIPTN AS [ADDLDESCRIPTION],
dbo.GL20000.TRXDATE AS [JVDATE],
dbo.GL20000.TRXSORCE AS [AUDITTRAIL],
dbo.GL20000.LASTUSER AS [USERID],
CASE dbo.GL20000.SERIES
WHEN 1 THEN 'ALL'
WHEN 2 THEN 'FINANCIAL'
WHEN 3 THEN 'SALES'
WHEN 4 THEN 'PURCHASING'
WHEN 5 THEN 'INVENTORY'
WHEN 6 THEN 'PAYROLL'
WHEN 7 THEN 'PROJECT'
END AS SERIES,
dbo.GL20000.ORDOCNUM AS [ORIGDOCNUMBER],
dbo.GL20000.ORMSTRNM AS [ORIGMASTERNAME],
dbo.GL20000.ORTRXSRC AS [ORIGAUDITTRAIL],
dbo.GL20000.CURNCYID AS [CURRENCYID],
dbo.GL20000.CRDTAMNT AS [CREDITAMOUNT],
dbo.GL20000.DEBITAMT AS [DEBITAMOUNT],
( dbo.GL20000.DEBITAMT - dbo.GL20000.CRDTAMNT ) NETAMOUNT,
dbo.GL20000.VOIDED AS [VOIDSTATUS],
dbo.GL20000.Back_Out_JE AS [BACKOUTJV],
dbo.GL20000.Back_Out_JE_Year AS [BACKOUTJVYEAR],
dbo.GL20000.Correcting_JE AS [CORRECTINGJV],
dbo.GL20000.Correcting_JE_Year AS [CORRECTINGJVYEAR],
dbo.GL20000.Original_JE AS [ORIGINALJV],
dbo.GL20000.Original_JE_Year AS [ORIGINALJVYEAR],
CASE MONTH(dbo.GL20000.TRXDATE)
WHEN 1 THEN 'JAN - ' + ltrim(rtrim(str(dbo.GL20000.OPENYEAR)))
WHEN 2 THEN 'FEB - ' + ltrim(rtrim(str(dbo.GL20000.OPENYEAR)))
WHEN 3 THEN 'MAR - ' + ltrim(rtrim(str(dbo.GL20000.OPENYEAR)))
WHEN 4 THEN 'APR - ' + ltrim(rtrim(str(dbo.GL20000.OPENYEAR)))
WHEN 5 THEN 'MAY - ' + ltrim(rtrim(str(dbo.GL20000.OPENYEAR)))
WHEN 6 THEN 'JUN - ' + ltrim(rtrim(str(dbo.GL20000.OPENYEAR)))
WHEN 7 THEN 'JUL - ' + ltrim(rtrim(str(dbo.GL20000.OPENYEAR)))
WHEN 8 THEN 'AUG - ' + ltrim(rtrim(str(dbo.GL20000.OPENYEAR)))
WHEN 9 THEN 'SEP - ' + ltrim(rtrim(str(dbo.GL20000.OPENYEAR)))
WHEN 10 THEN 'OCT - ' + ltrim(rtrim(str(dbo.GL20000.OPENYEAR)))
WHEN 11 THEN 'NOV - ' + ltrim(rtrim(str(dbo.GL20000.OPENYEAR)))
WHEN 12 THEN 'DEC - ' + ltrim(rtrim(str(dbo.GL20000.OPENYEAR)))
END AS [JVMONTH],
dbo.GL20000.PERIODID,
dbo.GL20000.SOURCDOC,
dbo.DTA10100.GROUPID ANALYSISGROUPID,
dbo.DTA10100.GROUPAMT ANALYSISGROUPAMOUNT,
dbo.DTA10200.CODEID ANALYSISCODEID,
dbo.DTA10200.POSTDESC ANALYSISPOSTINGDESC,
dbo.DTA10200.CODEAMT ANALYSISCODEAMOUNT
FROM dbo.GL20000
INNER JOIN dbo.GL00100 ON dbo.GL20000.ACTINDX = dbo.GL00100.ACTINDX
INNER JOIN dbo.GL00102 ON dbo.GL00100.ACCATNUM = dbo.GL00102.ACCATNUM
INNER JOIN dbo.GL00105 ON dbo.GL00100.ACTINDX = dbo.GL00105.ACTINDX
LEFT OUTER JOIN dbo.DTA10100 ON dbo.DTA10100.JRNENTRY = dbo.GL20000.JRNENTRY
AND dbo.DTA10100.ACTINDX = dbo.GL20000.ACTINDX
LEFT OUTER JOIN dbo.DTA10200 ON dbo.DTA10200.DTAREF = dbo.DTA10100.DTAREF
UNION
SELECT dbo.GL00105.ACTNUMST AS [ACCOUNTNUMBER],
dbo.GL00100.ACTDESCR AS ACCOUNTDESCRIPTION,
dbo.GL00102.ACCATDSC AS CATEGORY,
dbo.GL00100.MNACSGMT AS [MAINACCOUNT],
CASE dbo.GL00100.ACCTTYPE
WHEN 1 THEN 'POSTING ACCOUNT'
WHEN 2 THEN 'UNIT ACCOUNT'
WHEN 3 THEN 'POSTING ALLOCATION ACCOUNT'
WHEN 4 THEN 'UNIT ALLOCATION ACCOUNT'
END AS [ACCOUNTTYPE],
CASE dbo.GL00100.ACTIVE
WHEN 1 THEN 'ACTIVE'
WHEN 0 THEN 'INACTIVE'
END AS [STATUS],
CASE dbo.GL00100.PSTNGTYP
WHEN 0 THEN 'BALANCE SHEET'
WHEN 1 THEN 'PROFT AND LOSS'
END AS [POSTINGTYPE],
CASE dbo.GL00100.TPCLBLNC
WHEN 0 THEN 'DEBIT'
WHEN 1 THEN 'CREDIT'
END AS [TYPICALBALANCE],
dbo.GL00100.ACTNUMBR_1 AS SEGMENT1,
dbo.GL00100.ACTNUMBR_2 AS SEGMENT2,
dbo.GL00100.ACTNUMBR_3 AS SEGMENT3,
dbo.GL00100.ACTNUMBR_4 AS SEGMENT4,
dbo.GL00100.ACTNUMBR_5 AS SEGMENT5,
dbo.GL00100.ACTNUMBR_6 AS SEGMENT6,
dbo.GL00100.ACTNUMBR_7 AS SEGMENT7,
dbo.GL00100.ACTNUMBR_8 AS SEGMENT8,
dbo.GL00100.ACTNUMBR_9 AS SEGMENT9,
dbo.GL00100.ACTNUMBR_10 AS SEGMENT10,
dbo.GL30000.HSTYEAR AS [FISCALYEAR],
dbo.GL30000.JRNENTRY AS [JVNUMBER],
dbo.GL30000.REFRENCE AS REFERENCE,
dbo.GL30000.DSCRIPTN AS [ADDLDESCRIPTION],
dbo.GL30000.TRXDATE AS [JVDATE],
dbo.GL30000.TRXSORCE AS [AUDITTRAIL],
dbo.GL30000.LASTUSER AS [USERID],
CASE dbo.GL30000.SERIES
WHEN 1 THEN 'ALL'
WHEN 2 THEN 'FINANCIAL'
WHEN 3 THEN 'SALES'
WHEN 4 THEN 'PURCHASING'
WHEN 5 THEN 'INVENTORY'
WHEN 6 THEN 'PAYROLL'
WHEN 7 THEN 'PROJECT'
END AS SERIES,
dbo.GL30000.ORDOCNUM AS [ORIGDOCNUMBER],
dbo.GL30000.ORMSTRNM AS [ORIGMASTERNAME],
dbo.GL30000.ORTRXSRC AS [ORIGAUDITTRAIL],
dbo.GL30000.CURNCYID AS [CURRENCYID],
dbo.GL30000.CRDTAMNT AS [CREDITAMOUNT],
dbo.GL30000.DEBITAMT AS [DEBITAMOUNT],
( dbo.GL30000.DEBITAMT - dbo.GL30000.CRDTAMNT ) NETAMOUNT,
dbo.GL30000.VOIDED AS [VOIDSTATUS],
dbo.GL30000.Back_Out_JE AS [BACKOUTJV],
dbo.GL30000.Back_Out_JE_Year AS [BACKOUTJVYEAR],
dbo.GL30000.Correcting_JE AS [CORRECTINGJV],
dbo.GL30000.Correcting_JE_Year AS [CORRECTINGJVYEAR],
dbo.GL30000.Original_JE AS [ORIGINALJV],
dbo.GL30000.Original_JE_Year AS [ORIGINALJVYEAR],
CASE MONTH(dbo.GL30000.TRXDATE)
WHEN 1 THEN 'JAN - ' + ltrim(rtrim(str(dbo.GL30000.HSTYEAR)))
WHEN 2 THEN 'FEB - ' + ltrim(rtrim(str(dbo.GL30000.HSTYEAR)))
WHEN 3 THEN 'MAR - ' + ltrim(rtrim(str(dbo.GL30000.HSTYEAR)))
WHEN 4 THEN 'APR - ' + ltrim(rtrim(str(dbo.GL30000.HSTYEAR)))
WHEN 5 THEN 'MAY - ' + ltrim(rtrim(str(dbo.GL30000.HSTYEAR)))
WHEN 6 THEN 'JUN - ' + ltrim(rtrim(str(dbo.GL30000.HSTYEAR)))
WHEN 7 THEN 'JUL - ' + ltrim(rtrim(str(dbo.GL30000.HSTYEAR)))
WHEN 8 THEN 'AUG - ' + ltrim(rtrim(str(dbo.GL30000.HSTYEAR)))
WHEN 9 THEN 'SEP - ' + ltrim(rtrim(str(dbo.GL30000.HSTYEAR)))
WHEN 10 THEN 'OCT - ' + ltrim(rtrim(str(dbo.GL30000.HSTYEAR)))
WHEN 11 THEN 'NOV - ' + ltrim(rtrim(str(dbo.GL30000.HSTYEAR)))
WHEN 12 THEN 'DEC - ' + ltrim(rtrim(str(dbo.GL30000.HSTYEAR)))
END AS [JVMONTH],
dbo.GL30000.PERIODID,
dbo.GL30000.SOURCDOC,
dbo.DTA10100.GROUPID ANALYSISGROUPID,
dbo.DTA10100.GROUPAMT ANALYSISGROUPAMOUNT,
dbo.DTA10200.CODEID ANALYSISCODEID,
dbo.DTA10200.POSTDESC ANALYSISPOSTINGDESC,
dbo.DTA10200.CODEAMT ANALYSISCODEAMOUNT
FROM dbo.GL30000
INNER JOIN dbo.GL00100 ON dbo.GL30000.ACTINDX = dbo.GL00100.ACTINDX
INNER JOIN dbo.GL00102 ON dbo.GL00100.ACCATNUM = dbo.GL00102.ACCATNUM
INNER JOIN dbo.GL00105 ON dbo.GL00100.ACTINDX = dbo.GL00105.ACTINDX
LEFT OUTER JOIN dbo.DTA10100 ON dbo.DTA10100.JRNENTRY = dbo.GL30000.JRNENTRY
AND dbo.DTA10100.ACTINDX = dbo.GL30000.ACTINDX
LEFT OUTER JOIN dbo.DTA10200 ON dbo.DTA10200.DTAREF = dbo.DTA10100.DTAREF

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

GRANT SELECT ON vw_GLTrialBalance TO DYNGRP

http://cvakumar.com/msdynamics/2009/04/26/sql-code-for-gl-trial-balance/