Main Accounts with Account Category

Main Accounts with Account Category

Submitted by Nathan Clouse on 09/24/15.

A list of all main accounts with their account category. Can we use to report against transactions by main account category rather than main account.

AX Version: 

Module: 

CREATE VIEW [dbo].[vw_ledgerMainAccountNumbersWithCategory] 
AS 
  SELECT davc.partition, 
         'MainAccountId' = davc.mainaccount, 
         'LedgerDimension' = davc.recid, 
         'MainAccount' = ma.mainaccountid, 
         'AccountCategory' = mac.accountcategory, 
         'displayValue' = davc.displayvalue 
  FROM   dimensionattributevaluecombination davc 
         INNER JOIN mainaccount ma 
                 ON davc.partition = ma.partition 
                    AND davc.mainaccount = ma.recid 
         LEFT JOIN ledgerchartofaccounts lca 
                ON ma.partition = lca.partition 
                   AND ma.ledgerchartofaccounts = lca.recid 
         LEFT JOIN mainaccountcategory mac 
                ON mac.partition = ma.partition 
                   AND mac.accountcategoryref = 
                       ma.accountcategoryref 

go