SQL Script to Generate GL Trial Balance Detail for Current Year
This SQL Script below will allow you to generate the Trial Balance Detail for the Current Year. This is an expansion of the SQL script used for generating the GL Trial Balance Summary for Current Year. Please refer to the SQL script to generate GL Trial Balance Summary for the current year article.
Compatibility
Microsoft Dynamics GP 10
Microsoft Dynamics GP 2010
Microsoft Dynamics GP 2013
How To Use
There are two (2) variables that you will need to change depending on your company’s account format -> @StartAccount and @EndAccount. The example below is based on Fabrikam Inc’s account format: 3-4-2.
SET @StartAccount = ' - - '
SET @EndAccount = 'ÿÿÿ-ÿÿÿÿ-ÿÿ'
After changing the Start and End Accounts, run the script against your company database to show the results.
Note
Use of this script may use up server resources. If you have thousands of posting accounts, it is suggested to run this during non-peak hours.
--------------------------------------------
/**
Name: ALBA_Generate_Trial_Balance_Detail_Current_Year
Description: This SQL Scripts shows the trial balance detail for the
current year for all posting accounts only using a temporary table.
This should be run against the GP company database.
Created by: Alba Spectrum Corporation.
Date Created: December 10, 2013.
**/
DECLARE @O_mUnitAccountTotal numeric(19,5)
DECLARE @O_SQL_Error_State int
DECLARE @StartAccount varchar(100)
DECLARE @EndAccount varchar(100)
DECLARE @StartDate datetime
DECLARE @EndDate datetime
DECLARE @CurrentYear int
/** SET THE VARIABLES FOR THE STARTING AND ENDING ACCOUNT NUMBERS **/
SET @StartAccount = ' - - '
SET @EndAccount = 'ÿÿÿ-ÿÿÿÿ-ÿÿ'
/** THE DATE RANGE BELOW IS SET FOR WHOLE YEAR **/
SET @StartDate = DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)
SET @EndDate = DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE())+1,0)))
SET @CurrentYear = YEAR(GetDate())
CREATE TABLE #ALB_GL_DTL_TEMP(
[YEAR1] [smallint] NOT NULL,
[JRNENTRY] [int] NOT NULL,
[RCTRXSEQ] [numeric](19, 5) NOT NULL,
[SOURCDOC] [char](11) NOT NULL,
[REFRENCE] [char](31) NOT NULL,
[DSCRIPTN] [char](31) NOT NULL,
[TRXDATE] [datetime] NOT NULL,
[TRXSORCE] [char](13) NOT NULL,
[ACTINDX] [int] NOT NULL,
[POLLDTRX] [tinyint] NOT NULL,
[LASTUSER] [char](15) NOT NULL,
[LSTDTEDT] [datetime] NOT NULL,
[USWHPSTD] [char](15) NOT NULL,
[ORGNTSRC] [char](15) NOT NULL,
[ORGNATYP] [smallint] NOT NULL,
[QKOFSET] [smallint] NOT NULL,
[SERIES] [smallint] NOT NULL,
[ORTRXTYP] [smallint] NOT NULL,
[ORCTRNUM] [char](21) NOT NULL,
[ORMSTRID] [char](31) NOT NULL,
[ORMSTRNM] [char](65) NOT NULL,
[ORDOCNUM] [char](21) NOT NULL,
[ORPSTDDT] [datetime] NOT NULL,
[ORTRXSRC] [char](13) NOT NULL,
[OrigDTASeries] [smallint] NOT NULL,
[OrigSeqNum] [int] NOT NULL,
[SEQNUMBR] [int] NOT NULL,
[DTA_GL_Status] [smallint] NOT NULL,
[DTA_Index] [numeric](19, 5) NOT NULL,
[CURNCYID] [char](15) NOT NULL,
[CURRNIDX] [smallint] NOT NULL,
[RATETPID] [char](15) NOT NULL,
[EXGTBLID] [char](15) NOT NULL,
[XCHGRATE] [numeric](19, 7) NOT NULL,
[EXCHDATE] [datetime] NOT NULL,
[TIME1] [datetime] NOT NULL,
[RTCLCMTD] [smallint] NOT NULL,
[NOTEINDX] [numeric](19, 5) NOT NULL,
[ICTRX] [tinyint] NOT NULL,
[ORCOMID] [char](5) NOT NULL,
[ORIGINJE] [int] NOT NULL,
[PERIODID] [smallint] NOT NULL,
[CRDTAMNT] [numeric](19, 5) NOT NULL,
[DEBITAMT] [numeric](19, 5) NOT NULL,
[ORCRDAMT] [numeric](19, 5) NOT NULL,
[ORDBTAMT] [numeric](19, 5) NOT NULL,
[DOCDATE] [datetime] NOT NULL,
[PSTGNMBR] [int] NOT NULL,
[PPSGNMBR] [int] NOT NULL,
[DENXRATE] [numeric](19, 7) NOT NULL,
[MCTRXSTT] [smallint] NOT NULL,
[CorrespondingUnit] [char](5) NOT NULL,
[PERINDX] [smallint] NOT NULL,
[PERNAME] [char](21) NOT NULL,
[CURIDKEY] [char](15) NOT NULL,
[Ledger_ID] [smallint] NOT NULL,
[DEX_ROW_ID] [int] IDENTITY(1,1) NOT NULL)
CREATE TABLE #ALB_GL_HDR_TEMP(
[ACTINDX] [int] NOT NULL,
[ACTNUMBR_1] [char](7) NOT NULL,
[ACTNUMBR_2] [char](7) NOT NULL,
[ACTNUMBR_3] [char](7) NOT NULL,
[ACTNUMBR_4] [char](7) NOT NULL,
[ACTNUMBR_5] [char](7) NOT NULL,
[ACTNUMBR_6] [char](7) NOT NULL,
[ACTNUMBR_7] [char](7) NOT NULL,
[ACTNUMBR_8] [char](7) NOT NULL,
[ACTNUMBR_9] [char](7) NOT NULL,
[ACTNUMBR_10] [char](7) NOT NULL,
[ACTNUMST] [char](129) NOT NULL,
[Identity_Column] [int] NOT NULL,
[BGNGBAL] [numeric](19, 5) NOT NULL,
[ENDNGBAL] [numeric](19, 5) NOT NULL,
[HSTYEAR] [smallint] NOT NULL,
[STRTNGDT] [datetime] NOT NULL,
[ENDINGDT] [datetime] NOT NULL,
[TOTDEB] [numeric](19, 5) NOT NULL,
[TOTCRED] [numeric](19, 5) NOT NULL,
[PERNAME] [char](21) NOT NULL,
[PERINDX] [smallint] NOT NULL,
[DEBITAMT] [numeric](19, 5) NOT NULL,
[CRDTAMNT] [numeric](19, 5) NOT NULL,
[NETAMNT] [numeric](19, 5) NOT NULL,
[NMBRACTS] [int] NOT NULL,
[CURNCYID] [char](15) NOT NULL,
[CURRNIDX] [smallint] NOT NULL,
[ORDBTAMT] [numeric](19, 5) NOT NULL,
[ORCRDAMT] [numeric](19, 5) NOT NULL,
[ORNETAMT] [numeric](19, 5) NOT NULL,
[DEX_ROW_ID] [int] IDENTITY(1,1) NOT NULL)
EXECUTE glPrintTrialBalanceReport
'',
1,
'#ALB_GL_HDR_TEMP',
'#ALB_GL_DTL_TEMP',
0,
1,
0,
0,
0,
@StartAccount,
@EndAccount,
@StartDate,
@EndDate,
'1',
'12',
@StartDate,
'1',
@CurrentYear,
@StartDate,
'0',
'2',
'1',
'1',
'0',
'0.0000000',
'1',
'2',
'0',
'0',
'',
'þþþþþþþþþþþþþþþ',
0x01000000,
@O_mUnitAccountTotal output,
@O_SQL_Error_State output
SELECT * FROM #ALB_GL_HDR_TEMP ORDER BY ACTINDX
SELECT ACTINDX, TRXDATE, JRNENTRY, ORTRXSRC, DSCRIPTN, ORDOCNUM, ORMSTRNM, DEBITAMT, CRDTAMNT FROM GL20000
WHERE OPENYEAR = @CurrentYear AND TRXDATE >= @STARTDATE AND TRXDATE <= @ENDDATE AND VOIDED = 0
ORDER BY ACTINDX, TRXDATE
DROP TABLE #ALB_GL_HDR_TEMP
DROP TABLE #ALB_GL_DTL_TEMP
GO
Sample output:
