Home » Listing Details
Top Websites
  1. Dynamics GP Help
    Over 6100 resources listed.
  2. Mark Polino's DynamicAccounting.net
    Over 5100 resources listed.
  3. Rose Business Solutions Blog New
    Over 2200 resources listed.
  4. Developing for Dynamics GP - By David Musgrave and the MS GP Dev Support Team
    Over 1100 resources listed.
  5. Mariano Gomez at The Dynamics GP Blogster
    Over 1000 resources listed.
  6. Microsoft Dynamics Partner Community Blog
    Over 900 resources listed.
  7. Christina Phillips, Steve Endow & Lorren Zemke at Dynamics GP Land
    Over 700 resources listed.
  8. Mohammad Daoud's Dynamics GP Blog
    Over 600 resources listed.
  9. Vaidy Mohan at Dynamics GP - Learn & Discuss
    Over 500 resources listed.
  10. Inside Microsoft Dynamics GP Official Blog
    Over 500 resources listed.
  11. eOne Business Solutions Blog
    Over 400 resources listed.
  12. About Dynamics, Development and Life
    Over 300 resources listed.
  13. Frank Hamelly at GP2theMax
    Over 300 resources listed.
  14. Dynamics CPM
    Over 300 resources listed.
  15. BKD Dynamics GP Insights Blog
    Over 200 resources listed.
  16. Leslie Vail at Dynamics Confessor Blogspot
    Over 200 resources listed.
  17. Victoria Yudin's Dynamics GP Website
    Over 200 resources listed.
    Victoria Yudin
  18. Janakiram M.P. at DynamicsBlogger
    Over 100 resources listed.
  19. VS Tools Forum
    Over 100 resources listed.
    Your Resource for Visual Studio Tools for Dynamics GP
  20. Inside Microsoft Dynamics GP Official Blog
    Over 100 resources listed.
  21. US Dynamics GP Field Team Blog
    Over 100 resources listed.
  22. Catherine Eibner MBS Developer Evangelist
    Over 100 resources listed.
  23. Sivakumar Venkataraman at Interesting Findings & Knowledge Sharing
    Over 100 resources listed.
  24. Dynamics Small Business
    Over 100 resources listed.
  25. Belinda, The GP CSI
    Over 100 resources listed.

ID:11450
Title:GL Trial Balance Script
URL:http://vstoolsforum.com/blogs/dynamicsgp/archive/2010/04/30/gl-trial-balance-script.aspx
Description:

Note:

 Like all scripts, this one is provided *as is*, without any warrantee for its suitability for your purpose. There is no substitute for due diligence

-- =============================================
-- Written by Steve Gray
-- 941-747-3669
-- =============================================


-- Drop stored procedure if it already exists
IF EXISTS (
  SELECT * 
    FROM INFORMATION_SCHEMA.ROUTINES 
   WHERE SPECIFIC_SCHEMA = N'dbo'
     AND SPECIFIC_NAME = N'FP_TrialBalanceSummary' 
)
   DROP PROCEDURE FP_TrialBalanceSummary
GO
CREATE PROCEDURE FP_TrialBalanceSummary
-- FP_TrialBalanceSummary '1/1/2009','10/31/2009','4010-01','no','2009','yes','yes','yes','yes','Segment1'


@StartDate datetime,
@EndDate datetime,
@GLAccountNumber varchar(17), 
@HistoryYear varchar(3), /* yes/no*/
@Year int,
@IncludeInactiveAccounts varchar(3), /* yes/no */
@IncludePostingAccounts varchar(3), /* yes/no */
@IncludeUnitAccounts  varchar(3), /* yes/no */
@IncludeZeroBalanceAccounts   varchar(3), /* yes/no */
@SortBy varchar(10) /* the account segment to sort by*/


AS


-- validate
if YEAR(@startdate) <> @Year or YEAR(@EndDate) <> @Year begin
    select @StartDate = '1/1/1900', @EndDate = '1/1/1900'
end


--============================================================================
-- handle account type
--============================================================================
declare @accountTypes table (accountType int)
insert into @accounttypes (accountType) values (3)
insert into @accounttypes (accountType) values (4)
if @IncludeUnitAccounts = 'yes' begin
    insert into @accounttypes (accountType) values (2)
end
if @IncludePostingAccounts = 'yes' begin
    insert into @accounttypes (accountType) values (1)
end


--============================================================================
-- main query
--============================================================================
if @HistoryYear = 'No' begin
    /* create a cte of all the accounts 
        the way the query is currently set, this will always return one account index, but
        usually we put a union with account segment parameters and we get back more than
        one account number
    */
    with accountCTE (ACTINDX, active,ACTDESCR,PSTNGTYP,ACCTTYPE) as 
    (
        select am.ACTINDX ,am.active,am.ACTDESCR,am.PSTNGTYP,am.ACCTTYPE
            from GL00100 am (nolock)
                join GL00105 aim (nolock) on aim.ACTINDX = am.ACTINDX
                join @accountTypes at on at.accountType = am.ACCTTYPE
            where aim.ACTNUMST = @GLAccountNumber
                and am.ACTIVE = case when @IncludeInactiveAccounts = 'yes' then am.ACTIVE else 1 end
    ) 
    /* main query */
    select 
            am.ACTINDX,
            case when am.active = 0 then 'X' else '' end as Active,
            rtrim(aim.ACTNUMST) as ACTNUMST,
            RTRIM(am.ACTDESCR) AS ACTDESCR,
            am.PSTNGTYP,
            am.ACCTTYPE,
            pd.Period,
            pd.PERIODID,
            pd.YEAR1,
            isnull(bb.Beg_Bal,0) as Beg_Bal,
            isnull(pd.debitamt,0) as DEBITAMTD,
            isnull(pd.CRDTAMNTD,0) as CRDTAMNTD,
            isnull(pd.debitamt,0) - isnull(pd.CRDTAMNTD,0) as NetChange,
            case when @SortBy = 'Segment1' then aim.ACTNUMBR_1
                when @SortBy = 'Segment2' then aim.ACTNUMBR_2
                when @SortBy = 'Segment3' then aim.ACTNUMBR_3
                else aim.ACTNUMBR_4 end as SortBy


        from accountCTE am (nolock) 
            join GL00105 aim (nolock) on am.ACTINDX = aim.ACTINDX
            /* get the beginning balances */
            left join (
                select am.ACTINDX,
                        sum(gt.debitamt - gt.CRDTAMNT ) as Beg_Bal
                    from GL20000 gt (nolock)
                        join accountCTE am (nolock) on am.ACTINDX = gt.ACTINDX
                    where gt.TRXDATE < @StartDate
                        and gt.VOIDED = 0
                    group by  am.ACTINDX
                        ) bb on bb.ACTINDX = am.ACTINDX 
            /* sum the period data. */
            left join (
                select gt.ACTINDX,
                        rtrim(pd.PERNAME) as Period,
                        pd.PERIODID,
                        pd.year1,
                        sum(gt.debitamt ) as debitamt,
                        sum(gt.CRDTAMNT ) as CRDTAMNTD
                    from GL20000 gt (nolock)
                        join accountCTE am (nolock) on am.ACTINDX = gt.ACTINDX
                        join SY40100 pd (nolock) on gt.TRXDATE between pd.PERIODDT and pd.PERDENDT and pd.SERIES = 0
                    where gt.TRXDATE between @StartDate and @EndDate
                        and gt.VOIDED = 0
                    group by  gt.ACTINDX, pd.PERNAME, pd.PERIODID,pd.YEAR1
                    ) pd on pd.ACTINDX = am.ACTINDX 
        where (@IncludeZeroBalanceAccounts = 'Yes' 
            or 
            (isnull(bb.Beg_Bal,0) + isnull(pd.debitamt,0) - isnull(pd.CRDTAMNTD,0) <> 0) and (isnull(pd.debitamt,0) - isnull(pd.CRDTAMNTD,0) <> 0)
            )
  
end else begin
    /* the history query is the same as the above, but in the GL30000 table. no sense in 
    writing that now, just copy the above after it is working */
    
    --History year
    select 
        0 as ACTINDX
end
GO
grant exec on FP_TrialBalanceSummary to public
GO

Category:TIPS AND TRICKS BY MODULES: GL (General Ledger)
Link Owner:
Date Added:June 17, 2010 06:14:30 PM
Number Hits:36
RatingsAverage rating: (0 votes)
Reviews

No Reviews Yet.

 
GPWindow.com

Thank you for your support for GPWindow. It helps us cover part of the hosting costs for GPWindow.