SQL Views – Active Chart of Accounts

The following view returns all the active accounts in your General Ledger Chart of Accounts.  It also combines the full account number and account description.

It also needs noting that this view will only return 3 account segments (actnumbr_1, actnumbr_2, and actnumbr_3).  If you want or need to add more than the first 3 account segments, then you will need to add them here:

ltrim(rtrim(actnumbr_1)) + ‘-’ + ltrim(rtrim(actnumbr_2)) + ‘-’ + ltrim(rtrim(actnumbr_3)) ***add account here*** as Full_Account_Num,

by adding + ‘-‘ + ltrim(rtrim(actnumbr_x)) for each account segment you wish to add in the ***add account here*** section above.

You can name the view whatever you want by changing the [tspvActiveChartofAccounts] below.  Also the USE [TSP] tells the script to only execute on my database named TSP.  You will need to modify the [TSP] to be whatever your database name is.

USE [TSP]
GO
/****** Object:  View [dbo].[tspvActiveChartofAccounts]    Script Date: 06/17/2010 15:32:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[tspvActiveChartofAccounts]
AS 
select  creatddt as Created,
        modifdt as Last_Modified,
        ltrim(rtrim(actnumbr_1)) + ‘-’ + ltrim(rtrim(actnumbr_2)) + ‘-’
        + ltrim(rtrim(actnumbr_3)) as Full_Account_Num,
        actdescr as Account_Description,
        case when accttype = ’1′ then ‘Posting Account’
             when accttype = ’2′ then ‘Unit Account’
             when accttype = ’3′ then ‘Posting Allocation Account’
             when accttype = ’4′ then ‘Unit Allocation Account’
        end as Account_Type,
        case when pstngtyp = ’0′ then ‘Balance Sheet’
             when pstngtyp = ’1′ then ‘Profit and Loss’
        end as Posting_Type,
        case when fxdorvar = ’0′ then ‘Fixed Allocation’
             when fxdorvar = ’1′ then ‘Varialbe Allocation’
        end as Allocation_Type,
        txtfield as Notes,
        date1 as Note_Date
from    gl00100 a
left join sy03900 b on a.noteindx = b.noteindx
where   active = ’1′