{$page}

This SQL function can be used to return a tree'd representation of the GL Accounts. It should be used as the basis for any financial report that uses the GL Account structure.

None. This is a selection query and no data is modified in the running of it.

code_formatsql

–declare @SpacesPerLevel int; – Set this to a value to insert that many spaces per child level

–set @SpacesPerLevel = 4;

create function GLAccountTree (@SpacesPerLevel as int)

returns @GLTree table (

ID int,

AccountName VarChar(100),

Hierarchy VarChar(500),

ParentID int,

Level int,

GLClassificationType int,

SortIndex int,

IsActive bit,

IsGroup bit,

Level1Name VarChar(100),

Level2Name VarChar(100),

Level3Name VarChar(100),

Level4Name VarChar(100),

Level5Name VarChar(100),

Level1ID int,

Level2ID int,

Level3ID int,

Level4ID int,

Level5ID int,

SortHierarchy VarChar(100)

)

as

begin

declare @ShowGroups   bit;  -- Set this to 1 to include the groups 

declare @ShowAccounts bit;  -- Set this to 1 to include the accounts

set @ShowGroups     = 1;

set @ShowAccounts   = 1;

1. - Build Level 1

insert into @GLTree

  select GLAccount.ID, GLAccount.AccountName, GLAccount.AccountName, NULL, 1, 

		 GLAccount.GLClassificationType, GLAccount.SortIndex, GLAccount.IsActive, 1, 

		 NULL, NULL, NULL, NULL, NULL,

		 NULL, NULL, NULL, NULL, NULL,

		 Right('000000000'+cast(round(coalesce(GLAccount.SortIndex,0)*1000, 0) as VarChar(9)), 9)

  from GLAccount 

  where 

	GLAccount.ID > 0 

	and GLAccount.ClassTypeID = 8000 

	and ( GLAccount.AccountGroupID is NULL or GLAccount.AccountGroupID not in (Select ID from GLAccount) )

1. - Build Levels 2 through 5

declare @Depth int;

set @Depth = 2;

while (@Depth  0) 

begin

	update @GLTree

	set AccountName = Space((Level-1)*@SpacesPerLevel) + AccountName;

end

return

end

code

This code will pull all the accounts and compute their GL Sum for a time period.

code_formatsql

select ID, AccountName, Hierarchy,

     ( select Sum(Amount) 

       from GL 

       where GL.GLAccountID = GLTree.ID

         and GL.EntryDateTime < '1/1/2010'

     ) as EOY2009Balance,

     SortHierarchy 

from GLAccountTree(4) GLTree

order by SortHierarchy

code

• Entered : //2009
• Version :

• Backlinks include_pagepage_componentbacklinks