SQL Code for GL Trial Balance – GP 10

Guys

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_GLTrialBalancev10]')
                    AND OBJECTPROPERTY(id, N'IsView') = 1 )
    DROP VIEW [dbo].[vw_GLTrialBalancev10]
GO
 
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
 
CREATE VIEW dbo.vw_GLTrialBalancev10
AS  
SELECT  A.OPENYEAR AS [FISCALYEAR],
        A.JRNENTRY AS [JVNUMBER],
        A.REFRENCE AS REFERENCE,
        A.DSCRIPTN AS [DISTREFERENCE],
        A.TRXDATE AS [JVDATE],
        WEEKNO = CASE WHEN YEAR(A.TRXDATE) = A.OPENYEAR
                      THEN 'Week ' + REPLICATE('0',
                                               2 - LEN({fn WEEK(A.TRXDATE)}))
                           + LTRIM(RTRIM(STR({fn WEEK(A.TRXDATE)})))
                      ELSE '00:BBF'''
                           + SUBSTRING(LTRIM(RTRIM(STR(YEAR(A.TRXDATE)))), 3,
                                       2)
                 END,
        PERIOD = CASE WHEN YEAR(A.TRXDATE) = A.OPENYEAR
                      THEN REPLICATE('0', 2 - LEN(MONTH(A.TRXDATE)))
                           + ( LTRIM(RTRIM(STR(MONTH(A.TRXDATE)))) + ':'
                               + ( LEFT({fn MONTHNAME(A.TRXDATE)}, 3) ) + ''''
                               + SUBSTRING(LTRIM(RTRIM(STR(YEAR(A.TRXDATE)))),
                                           3, 2) )
                      ELSE '00:BBF'''
                           + SUBSTRING(LTRIM(RTRIM(STR(YEAR(A.TRXDATE)))), 3,
                                       2)
                 END,
        QUARTERNO = CASE WHEN YEAR(A.TRXDATE) = A.OPENYEAR
                              AND {fn QUARTER(A.TRXDATE)} = 1
                         THEN 'Q1:Jan' + ''''
                              + SUBSTRING(LTRIM(RTRIM(STR(YEAR(A.TRXDATE)))),
                                          3, 2) + '-Mar' + ''''
                              + SUBSTRING(LTRIM(RTRIM(STR(YEAR(A.TRXDATE)))),
                                          3, 2)
                         WHEN YEAR(A.TRXDATE) = A.OPENYEAR
                              AND {fn QUARTER(A.TRXDATE)} = 2
                         THEN 'Q2:Apr' + ''''
                              + SUBSTRING(LTRIM(RTRIM(STR(YEAR(A.TRXDATE)))),
                                          3, 2) + '-Jun' + ''''
                              + SUBSTRING(LTRIM(RTRIM(STR(YEAR(A.TRXDATE)))),
                                          3, 2)
                         WHEN YEAR(A.TRXDATE) = A.OPENYEAR
                              AND {fn QUARTER(A.TRXDATE)} = 3
                         THEN 'Q3:Jul' + ''''
                              + SUBSTRING(LTRIM(RTRIM(STR(YEAR(A.TRXDATE)))),
                                          3, 2) + '-Sep' + ''''
                              + SUBSTRING(LTRIM(RTRIM(STR(YEAR(A.TRXDATE)))),
                                          3, 2)
                         WHEN YEAR(A.TRXDATE) = A.OPENYEAR
                              AND {fn QUARTER(A.TRXDATE)} = 4
                         THEN 'Q4:Oct' + ''''
                              + SUBSTRING(LTRIM(RTRIM(STR(YEAR(A.TRXDATE)))),
                                          3, 2) + '-Dec' + ''''
                              + SUBSTRING(LTRIM(RTRIM(STR(YEAR(A.TRXDATE)))),
                                          3, 2)
                         ELSE '00:BBF'''
                              + SUBSTRING(LTRIM(RTRIM(STR(YEAR(A.TRXDATE)))),
                                          3, 2)
                    END,
        D.ACTNUMST AS [ACCOUNTNUMBER],
        B.ACTNUMBR_1 AS SEGMENT1,
        B.ACTNUMBR_2 AS SEGMENT2,
        B.ACTNUMBR_3 AS SEGMENT3,
        B.ACTDESCR AS ACCOUNTDESCRIPTION,
        C.ACCATDSC AS CATEGORY,
        CASE B.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 B.ACTIVE
          WHEN 1 THEN 'Active'
          WHEN 0 THEN 'Inactive'
        END AS [STATUS],
        CASE B.PSTNGTYP
          WHEN 0 THEN 'Balance Sheet'
          WHEN 1 THEN 'Profit & Loss'
        END AS [POSTINGTYPE],
        A.TRXSORCE AS [AUDITTRAIL],
        A.USWHPSTD AS [POSTEDUSER],
        CASE A.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,
        A.ORDOCNUM AS [ORIGDOCNUMBER],
        A.ORMSTRNM AS [ORIGMASTERNAME],
        A.ORTRXSRC AS [ORIGAUDITTRAIL],
        A.CURNCYID AS [CURRENCYID],
        A.ORDBTAMT AS [ORIGDEBIT],
        A.ORCRDAMT AS [ORIGCREDIT],
        ( A.ORDBTAMT - A.ORCRDAMT ) ORIGNETAMOUNT,
        A.DEBITAMT AS [FUNCDEBIT],
        A.CRDTAMNT AS [FUNCCREDIT],
        ( A.DEBITAMT - A.CRDTAMNT ) FUNCNETAMOUNT,
        A.SOURCDOC,
        E.GROUPID ANALYSISGROUPID,
        E.GROUPAMT ANALYSISGROUPAMOUNT,
        F.CODEID ANALYSISCODEID,
        F.POSTDESC ANALYSISPOSTINGDESC,
        F.CODEAMT ANALYSISCODEAMOUNT
FROM    dbo.GL20000 A
        INNER JOIN dbo.GL00100 B ON A.ACTINDX = B.ACTINDX
        INNER JOIN dbo.GL00102 C ON B.ACCATNUM = C.ACCATNUM
        INNER JOIN dbo.GL00105 D ON B.ACTINDX = D.ACTINDX
        LEFT OUTER JOIN dbo.DTA10100 E ON E.JRNENTRY = A.JRNENTRY
                                          AND E.ACTINDX = A.ACTINDX
        LEFT OUTER JOIN dbo.DTA10200 F ON F.DTAREF = E.DTAREF
UNION ALL
SELECT  A.HSTYEAR AS [FISCALYEAR],
        A.JRNENTRY AS [JVNUMBER],
        A.REFRENCE AS REFERENCE,
        A.DSCRIPTN AS [DISTREFERENCE],
        A.TRXDATE AS [JVDATE],
        WEEKNO = CASE WHEN YEAR(A.TRXDATE) = A.HSTYEAR
                      THEN 'Week ' + REPLICATE('0',
                                               2 - LEN({fn WEEK(A.TRXDATE)}))
                           + LTRIM(RTRIM(STR({fn WEEK(A.TRXDATE)})))
                      ELSE '00:BBF'''
                           + SUBSTRING(LTRIM(RTRIM(STR(YEAR(A.TRXDATE)))), 3,
                                       2)
                 END,
        PERIOD = CASE WHEN YEAR(A.TRXDATE) = A.HSTYEAR
                      THEN REPLICATE('0', 2 - LEN(MONTH(A.TRXDATE)))
                           + ( LTRIM(RTRIM(STR(MONTH(A.TRXDATE)))) + ':'
                               + ( LEFT({fn MONTHNAME(A.TRXDATE)}, 3) ) + ''''
                               + SUBSTRING(LTRIM(RTRIM(STR(YEAR(A.TRXDATE)))),
                                           3, 2) )
                      ELSE '00:BBF'''
                           + SUBSTRING(LTRIM(RTRIM(STR(YEAR(A.TRXDATE)))), 3,
                                       2)
                 END,
        QUARTERNO = CASE WHEN YEAR(A.TRXDATE) = A.HSTYEAR
                              AND {fn QUARTER(A.TRXDATE)} = 1
                         THEN 'Q1:Jan' + ''''
                              + SUBSTRING(LTRIM(RTRIM(STR(YEAR(A.TRXDATE)))),
                                          3, 2) + '-Mar' + ''''
                              + SUBSTRING(LTRIM(RTRIM(STR(YEAR(A.TRXDATE)))),
                                          3, 2)
                         WHEN YEAR(A.TRXDATE) = A.HSTYEAR
                              AND {fn QUARTER(A.TRXDATE)} = 2
                         THEN 'Q2:Apr' + ''''
                              + SUBSTRING(LTRIM(RTRIM(STR(YEAR(A.TRXDATE)))),
                                          3, 2) + '-Jun' + ''''
                              + SUBSTRING(LTRIM(RTRIM(STR(YEAR(A.TRXDATE)))),
                                          3, 2)
                         WHEN YEAR(A.TRXDATE) = A.HSTYEAR
                              AND {fn QUARTER(A.TRXDATE)} = 3
                         THEN 'Q3:Jul' + ''''
                              + SUBSTRING(LTRIM(RTRIM(STR(YEAR(A.TRXDATE)))),
                                          3, 2) + '-Sep' + ''''
                              + SUBSTRING(LTRIM(RTRIM(STR(YEAR(A.TRXDATE)))),
                                          3, 2)
                         WHEN YEAR(A.TRXDATE) = A.HSTYEAR
                              AND {fn QUARTER(A.TRXDATE)} = 4
                         THEN 'Q4:Oct' + ''''
                              + SUBSTRING(LTRIM(RTRIM(STR(YEAR(A.TRXDATE)))),
                                          3, 2) + '-Dec' + ''''
                              + SUBSTRING(LTRIM(RTRIM(STR(YEAR(A.TRXDATE)))),
                                          3, 2)
                         ELSE '00:BBF'''
                              + SUBSTRING(LTRIM(RTRIM(STR(YEAR(A.TRXDATE)))),
                                          3, 2)
                    END,
        D.ACTNUMST AS [ACCOUNTNUMBER],
        B.ACTNUMBR_1 AS SEGMENT1,
        B.ACTNUMBR_2 AS SEGMENT2,
        B.ACTNUMBR_3 AS SEGMENT3,
        B.ACTDESCR AS ACCOUNTDESCRIPTION,
        C.ACCATDSC AS CATEGORY,
        CASE B.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 B.ACTIVE
          WHEN 1 THEN 'Active'
          WHEN 0 THEN 'Inactive'
        END AS [STATUS],
        CASE B.PSTNGTYP
          WHEN 0 THEN 'Balance Sheet'
          WHEN 1 THEN 'Profit & Loss'
        END AS [POSTINGTYPE],
        A.TRXSORCE AS [AUDITTRAIL],
        A.USWHPSTD AS [POSTEDUSER],
        CASE A.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,
        A.ORDOCNUM AS [ORIGDOCNUMBER],
        A.ORMSTRNM AS [ORIGMASTERNAME],
        A.ORTRXSRC AS [ORIGAUDITTRAIL],
        A.CURNCYID AS [CURRENCYID],
        A.ORDBTAMT AS [ORIGDEBIT],
        A.ORCRDAMT AS [ORIGCREDIT],
        ( A.ORDBTAMT - A.ORCRDAMT ) ORIGNETAMOUNT,
        A.DEBITAMT AS [FUNCDEBIT],
        A.CRDTAMNT AS [FUNCCREDIT],
        ( A.DEBITAMT - A.CRDTAMNT ) FUNCNETAMOUNT,
        A.SOURCDOC,
        E.GROUPID ANALYSISGROUPID,
        E.GROUPAMT ANALYSISGROUPAMOUNT,
        F.CODEID ANALYSISCODEID,
        F.POSTDESC ANALYSISPOSTINGDESC,
        F.CODEAMT ANALYSISCODEAMOUNT
FROM    dbo.GL30000 A
        INNER JOIN dbo.GL00100 B ON A.ACTINDX = B.ACTINDX
        INNER JOIN dbo.GL00102 C ON B.ACCATNUM = C.ACCATNUM
        INNER JOIN dbo.GL00105 D ON B.ACTINDX = D.ACTINDX
        LEFT OUTER JOIN dbo.DTA10100 E ON E.JRNENTRY = A.JRNENTRY
                                          AND E.ACTINDX = A.ACTINDX
        LEFT OUTER JOIN dbo.DTA10200 F ON F.DTAREF = E.DTAREF
 
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
 
GRANT SELECT ON vw_GLTrialBalancev10 TO DYNGRP

Note: This query works on the calendar month. It can be further extended to work according to the fiscal period setup in GP as well, by joining it with the fiscal period table (SY40100).

Using this view, you can do some analysis on the General Ledger – Sub Ledger reconcilation and some cool pivot reports for General Ledger.

Note: I am working on extending this view to bring information from Analytical Accounting as well, and will post the updates once done.

Until next post…

Siva

Rating: 0.0/10 (0 votes cast)

Rating: 0 (from 0 votes)

April 26, 2009 · veeyeskay · 7 Comments
Tags: General Ledger, GL Trial Balance · Posted in: Dynamics, General Ledger, Great Plains, SQL Server, SQL Server 2000, SQL Server 2005, SQL Server 2008 Total Views: 1,895

Santosh

Hi Siva,

Your this query helps a lot when we are planning to do year end closing or taking TB summary. Thanks once again for doing this hard work and shaking this information (query).

I would like to know can the query be modify with date parameter to generate same GP reports. (Reports>>Financial >> Trail Balance >> Summary). So colum will be Account, Opening, Debit, credit, ending balance…etc Just my thought.. let me know if you have any idea on such report using SQL query.

VA:F [1.9.13_1145]
Please wait while your rating is cast...
Rating: 0.0/5 (0 votes cast)
VA:F [1.9.13_1145]
Rating: 0 (from 0 votes)
veeyeskay

Thanks for the update. I will have my SQL script updated to include MDA details too.

As a matter of fact, I am working on a script to include AA info as well. Keep watching this blog for that article soon…

Cheers…

VA:F [1.9.13_1145]
Please wait while your rating is cast...
Rating: 0.0/5 (0 votes cast)
VA:F [1.9.13_1145]
Rating: 0 (from 0 votes)

M	T	W	T	F	S	S
« Mar				May »
		1	2	3	4	5
6	7	8	9	10	11	12
13	14	15	16	17	18	19
20	21	22	23	24	25	26
27	28	29	30

Interesting Findings & Knowledge Sharing

Scribblings of a Microsoft Dynamics GP MVP

SQL Code for GL Trial Balance – GP 10

Search This Blog

Categories

Events

Visitors Map

Administration

Recent Posts

Popular Posts

Interesting Links

Interesting Findings & Knowledge Sharing

Scribblings of a Microsoft Dynamics GP MVP

SQL Code for GL Trial Balance – GP 10

Search This Blog

Categories

Events

Visitors Map

Administration

Recent Posts

Popular Posts

Keywords

Interesting Links