• Dynamics GP Resources
• SQL View as a Dexterity Table
• SQL Script: Gen GL TB Detail for Current Year
• SQL Script: Gen TB Summary & Detail for Historical Year
• GP User not able to login after Password Reset
• MR Management Console: "Object reference not set..."
• Rebuild the Data Mart Integration in MR 2012
• Auto Fulfill Backorder Qty in GP

• SQL Server Resources
• Performance Issue with Left Outer Join on Large Tables Involving Character Field

• .NET Resources
• URL Routing in ASP.net 4.0
• Returning JSON from Ajax-Enabled WCF Service
• Error when publishing eConnect Web Service over the internet

SQL Script to Generate GL Trial Balance Summary and Detail for a Historical Year

We have created another script to view historical GL Trial Balance Summary and Detail for a Historical Year. The script below will show the trial balance for posting and unit accounts.

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 = 'ÿÿÿ-ÿÿÿÿ-ÿÿ'

Then update the start and end dates to the desired historical year:

SET @StartDate       = '01/01/2013'

SET @EndDate         = '12/31/2013'

SET @CurrentYear     = 2013

After changing the values, 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.

--------------------------------------------

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 **/

SET @StartAccount    = '   -    -  '

SET @EndAccount      = 'ÿÿÿ-ÿÿÿÿ-ÿÿ'

SET @StartDate       = '01/01/2013'

SET @EndDate         = '12/31/2013'

SET @CurrentYear     = 2013

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,

              2, --unit account

              1, --unit account

              0,

              @StartAccount,

              @EndAccount,

              @StartDate,

              @EndDate,

              '1',

              '12',

              @StartDate,

              '1',

              @CurrentYear,

              @StartDate,

              '1', --keeping summary

              '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 GL30000

WHERE HSTYEAR = @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 using the following query for display purposes:

The select statement below replaces the “SELECT * FROM #ALB_GL_HDR_TEMP ORDER BY ACTINDX” in the code above in order to show a subset of the results only.

SELECT ACTINDX as Account_Index,

              ACTNUMST as Account_Number,

              BGNGBAL as Beginning_Balance,

              ENDNGBAL as Ending_Balance,

              TOTDEB as Total_Debit,

              TOTCRED as Total_Credit

FROM #ALB_GL_HDR_TEMP

ORDER BY ACTINDX