SQL Script – Default Item Class GL Accounts

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