Skip to content

SQL Views – Active Chart of Accounts

June 22, 2010

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.

/****** Object:  View [dbo].[tspvActiveChartofAccounts]    Script Date: 06/17/2010 15:32:10 ******/
CREATE VIEW [dbo].[tspvActiveChartofAccounts]
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’

From → Dynamics GP

  1. Why don’t you join to GL00105 on ACTINDEX then you have a fully formatted Account Numbber String.


  2. Bron Tamulis permalink

    I just added the actindx field and now I use it like I would the GL00105 if needed in report writing.

    Thank you for posting – your SQL views are always helpful and appreciated.

    I have noticed a total lack of GP Manufacturing Related SQL Views posted on any of the forums and blogs.

    I should get a blog going since most of my experience is in the mfg area.

  3. Ron permalink

    Thanks for the JOIN. I completely missed that table when writing the query. That definitely would have made the code easier to read.

    Thanks for you kind comments. I think that a MFG blog would be useful. We are a distribution company who has a small manufacturing arm…I would love to see some Dynamics GP / MFG related articles.


Trackbacks & Pingbacks

  1. SQL Views – Active Chart of Accounts -

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: