Skip to content

SQL Script – Default Item Class GL Accounts

June 23, 2010

Have you ever need to take a look at what your Item Class General Ledger accounts are, but didn’t want to go through them one at a time?  Here is a helpful little script that will allow you to see all Item Classes and what GL accounts are populated in the “Item Class Account Setup” screen.

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 Account_Num,

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

It should also be noted that the Sort column can be ignored.  It is only for sorting the idx column by the default sort in the Item Class Account Setup screen. 

This script looks a little different than most because it does not start with SELECT *….  Please copy and paste it into SQL Server Management Studio and execute on your main company database like normal (including the ; at the beginning).

;
WITH    CTE
          AS ( SELECT   itmclscd,
                        itmclsdc,
                        account,
                        idx
               FROM     iv40400 pvt UNPIVOT ( account FOR idx IN ( ivivindx , ivivofix , ivcogsix , ivslsidx , ivsldsix , ivslrnix , ivinusix , ivinsvix , ivdmgidx , ivvaridx , dpshpidx , purpvidx , uppvidx , ivretidx , asmvridx ) ) as unpvt)
    SELECT  [Item_Class]=a.itmclscd,
            [Class_Description]=a.itmclsdc,
            CASE WHEN a.idx = ‘ivivindx’ THEN ‘Inventory on Hand’
                 WHEN a.idx = ‘ivivofix’ THEN ‘Inventory Offset’
                 WHEN a.idx = ‘ivcogsix’ THEN ‘Cost of Good Sold’
                 WHEN a.idx = ‘ivslsidx’ THEN ‘Sales’
                 WHEN a.idx = ‘ivsldsix’ THEN ‘Markdowns’
                 WHEN a.idx = ‘ivslrnix’ THEN ‘Sales Returns’
                 WHEN a.idx = ‘ivinusix’ THEN ‘In Use’
                 WHEN a.idx = ‘ivinsvix’ THEN ‘In Service’
                 WHEN a.idx = ‘ivdmgidx’ THEN ‘Damaged’
                 WHEN a.idx = ‘ivvaridx’ THEN ‘Variance’
                 WHEN a.idx = ‘dpshpidx’ THEN ‘Drop Ship Items’
                 WHEN a.idx = ‘purpvidx’ THEN ‘Purchase Price Variance’
                 WHEN a.idx = ‘uppvidx’ THEN ‘Unrealized Purchase Price Var’
                 WHEN a.idx = ‘ivretidx’ THEN ‘Inventory Returns’
                 WHEN a.idx = ‘asmvridx’ THEN ‘Assembly Variance’
            END AS idx,
            CASE WHEN a.idx = ‘ivivindx’ THEN ‘a’
                 WHEN a.idx = ‘ivivofix’ THEN ‘b’
                 WHEN a.idx = ‘ivcogsix’ THEN ‘c’
                 WHEN a.idx = ‘ivslsidx’ THEN ‘d’
                 WHEN a.idx = ‘ivsldsix’ THEN ‘e’
                 WHEN a.idx = ‘ivslrnix’ THEN ‘f’
                 WHEN a.idx = ‘ivinusix’ THEN ‘g’
                 WHEN a.idx = ‘ivinsvix’ THEN ‘h’
                 WHEN a.idx = ‘ivdmgidx’ THEN ‘i’
                 WHEN a.idx = ‘ivvaridx’ THEN ‘j’
                 WHEN a.idx = ‘dpshpidx’ THEN ‘k’
                 WHEN a.idx = ‘purpvidx’ THEN ‘l’
                 WHEN a.idx = ‘uppvidx’ THEN ‘m’
                 WHEN a.idx = ‘ivretidx’ THEN ‘n’
                 WHEN a.idx = ‘asmvridx’ THEN ‘o’
            END AS sort,
            ( LTRIM(RTRIM(b.actnumbr_1)) + ‘-‘ + LTRIM(RTRIM(b.actnumbr_2))
            + ‘-‘ + LTRIM(RTRIM(b.actnumbr_3)) ) AS Account_Num,
            [Account_Description]=b.actdescr,
            [Notes]=c.txtfield
    FROM    CTE a
    LEFT JOIN gl00100 b ON a.account = b.actindx
    LEFT JOIN tspvGPNotes c ON b.noteindx = c.noteindx
    ORDER BY itmclscd,
            sort

About these ads

From → Dynamics GP

7 Comments
  1. Hi

    I tried to run this query in SQL 2000 Enterprise edition and it gave me errors as follows

    Server: Msg 156, Level 15, State 1, Line 1
    Incorrect syntax near the keyword ‘WITH’.
    Server: Msg 170, Level 15, State 1, Line 6
    Line 6: Incorrect syntax near ‘UNPIVOT’.

    Then I tried to run it in SQL 2005 SQL Server Management studio. There it says

    Msg 208, Level 16, State 1, Line 1
    Invalid object name ‘tspvGPNotes’.

    Any chance to get some help

    Thank you

    Abdul Rahman

    • Ron permalink

      Abdul,

      I am sorry but you need to creat the view for tspvGPNotes first. Have you created that yet? I must have not included it in the required views list.

      I am out of town right now with limited Internet access so I will check it out when I get back. Let me know if that works for you.

      Ron

      Sent from my iPhone

  2. Is this script not compatible with GP9. I just ran it and got the following error:

    Msg 102, Level 15, State 1, Line 10
    Incorrect syntax near ‘‘’.
    Msg 102, Level 15, State 1, Line 20
    Incorrect syntax near ‘Ship’.

  3. Matt,

    Have you installed the tspvGPNotes view?

    Ron

  4. I believe this is among the most significant information for me.
    And i am glad reading your article. But wanna statement oon some general issues,
    Thhe website taste is great, the articles is truly nice : D.
    Excellent activity, cheers

Trackbacks & Pingbacks

  1. SQL Script – Default Item Class GL Accounts - DynamicAccounting.net
  2. SmartList Builder based on SQL View not returning data - DynamicAccounting.net

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 28 other followers

%d bloggers like this: