Get Unused Financial Dimensions in AX 2012

Below is the code for getting the unused financial dimensions in AX 2012.
Here I wrote code for Department.

static void finacialDimValues(Args _args)
{
    DimensionAttribute dimAttribute;
    DimensionAttributeValue dimAttributeValue;
    DimensionAttributeLevelValue dimAttributeLevelValue;
    DimensionAttributeValueGroup dimAttributeValueGroup;
    DimensionHierarchy dimHierarchy;
    Dictionary dict = new Dictionary();
    DictEnum   dictEnum = new DictEnum(enumNum(OMOperatingUnitType));
    TableId         entityId;
    RecId           entityInstance;
    DataAreaId      dataAreaId;
    RecId           financialTagCategoryId;
    TableId roleViewId;
    OMOperatingUnit oMOperatingUnit;
    DimensionAttributeValueSetItem  dimAttributeValueSetItem;
    while select oMOperatingUnit where oMOperatingUnit.OMOperatingUnitType == OMOperatingUnitType::OMDepartment
    {
        roleViewId = dict.tableName2Id(strFmt('DimAttribute%1', dictEnum.index2Symbol(oMOperatingUnit.OMOperatingUnitType)));
        [entityId, entityInstance, dataAreaId, financialTagCategoryId] = DimensionEnabledType::getFieldsForDeleteOrRename(oMOperatingUnit, roleViewId);
       // select firstOnly dimAttributeValueSetItem where dimAttributeValueSetItem.DisplayValue == oMOperatingUnit.OMOperatingUnitNumber;
         select firstonly RecId from dimAttributeLevelValue
        exists join dimAttributeValue where
            dimAttributeValue.RecId == dimAttributeLevelValue.DimensionAttributeValue &&
            dimAttributeValue.EntityInstance == entityInstance &&
            dimAttributeValue.IsDeleted == false
        exists join dimAttribute where
            dimAttribute.RecId == dimAttributeValue.DimensionAttribute &&
            dimAttribute.BackingEntityType == entityId
        exists join dimAttributeValueGroup where
            dimAttributeValueGroup.RecId == dimAttributeLevelValue.DimensionAttributeValueGroup
        notExists join dimHierarchy where
            dimHierarchy.RecId == dimAttributeValueGroup.DimensionHierarchy &&
            dimHierarchy.StructureType == DimensionHierarchyType::Focus;
        if(dimAttributeLevelValue)//dimAttributeValueSetItem)
        {
            continue;
        }
        else if(!OMMultipleHierarchy::checkExistenceInHierarchy(omOperatingUnit.RecId))
            info(oMOperatingUnit.OMOperatingUnitNumber);
    }
}


SQL query:

select * from DIMATTRIBUTEOMDEPARTMENT where not exists(
select * from DimensionAttributeLevelValue
           join DimensionAttributeValue on
            DimensionAttributeValue.RecId = DimensionAttributeLevelValue.DimensionAttributeValue and
            DimensionAttributeValue.EntityInstance = DIMATTRIBUTEOMDEPARTMENT.RECID and
            DimensionAttributeValue.IsDeleted = 0
          join DimensionAttribute on
            DimensionAttribute.RecId = DimensionAttributeValue.DimensionAttribute and
            DimensionAttribute.BackingEntityType = 11765
         join DimensionAttributeValueGroup on
            DimensionAttributeValueGroup.RecId = DimensionAttributeLevelValue.DimensionAttributeValueGroup where
        not exists(select * from DimensionHierarchy where
            DimensionHierarchy.RecId = DimensionAttributeValueGroup.DIMENSIONHIERARCHY and

            DimensionHierarchy.StructureType = 6))

@Rahul Talasila

Get unused Project categories In AX 2012

Below is the code for getting the unused project dimensions in AX.

Note : Please create a table(Test) with two string fields.

static void ProjCategory(Args _args)

{

    ProjUnpostedTransView   projUnpostedTransView;

    ProjPostTransView       projPostTransView;

    CategoryTable           categoryTable;

    Test           test;

    int iCounter;

    delete_from test;

    while select * from categoryTable

    {

        test.clear();

        select firstOnly projPostTransView where projPostTransView.CategoryId ==  categoryTable.CategoryId;

        if(projPostTransView)

        {

            continue;

        }

      //  else if(!projPostTransView)

      //  {

        select firstonly projUnpostedTransView where projUnpostedTransView.CategoryId ==  categoryTable.CategoryId;

        if(projUnpostedTransView)

        {

            continue;

        }

        else

        {

            test.AccountNum = categoryTable.CategoryId;

            test.Name = categoryTable.CategoryName;

            test.insert();

            iCounter++;

        }

SQL Query:

select * from CATEGORYTABLE where not exists

( select * from projPostTransView where CATEGORYTABLE.CategoryId =  projPostTransView.CategoryId) and 

not exists (select * from ProjLedgerJournalTransUnpostedView where CATEGORYTABLE.CategoryId =  ProjLedgerJournalTransUnpostedView.CATEGORYID)

@Rahul Talasila

Get Unused Main Accounts In AX 2012

Below is the AX code for getting the unused main accounts for a legal entity.

Note : Please create a table (TestMainaccounts) with two string fields.

static void Ledgeraccounts(Args _args)
{
    DimensionAttribute dimAttribute;
    DimensionAttributeValue dimAttributeValue;
    DimensionAttributeLevelValue dimAttributeLevelValue;
    TestMainaccounts  test;
    Ledger  Ledger;
    LedgerChartOfAccounts   ledgerChartOfAccounts;
    FiscalCalendarPeriod    fiscalCalendarPeriod;
    //OGILedgerDimensionTable oGILedgerDimensionTable;
        MainAccount                         mainAccount, mainAccountLoc; //Holds the main accounts
    GeneralJournalEntry                 generalJournalEntry; //Used to hold Ledger transactions
    GeneralJournalAccountEntry          generalJournalAccountEntry; //Used to hold Ledger transactions
    SubledgerJournalEntry               subLedgerJournalEntry; //Used to hold sub Ledger transactions (Like sales/purch invoice etc.)
    SubledgerJournalAccountEntry        subLedgerJournalAccountEntry;  //Used to hold sub Ledger transactions (Like sales/purch invoice etc.)
    DimensionAttributeValueCombination  dimAttrValueComb; //Used to store the combination of main accounts and dimensions
    int i;
    container conCompanies ;//= [ 'cm1', 'cm2', 'dat' ];
    CompanyInfo companyInfo;
    container   concreteCompanies;
    int         concreteCompanyCount;
    CompanyId   currentConcreteCompany;
    boolean     canDelete;

    SysDictTable tableToCheck;

    TableId         entityId;
    RecId           entityInstance;
    DataAreaId      dataAreaId;
    RecId           financialTagCategoryId;
    Common  _table;
    TableId _roleViewId = 0;
   // --VALIDATES NO LEDGER TRANSACTIONS HAVE BEEN POSTED.
    delete_from test;
    while select mainAccountLoc
    {
        _table = mainAccountLoc;
        [entityId, entityInstance, dataAreaId, financialTagCategoryId] = DimensionEnabledType::getFieldsForDeleteOrRename(_table, _roleViewId);

      select * FROM generalJournalAccountEntry
            JOIN dimAttrValueComb where generalJournalAccountEntry.LedgerDimension == dimAttrValueComb.RecId
            JOIN mainAccount where dimAttrValueComb.MainAccount == mainAccount.RecId
                && mainAccount.MainAccountId == mainAccountLoc.MainAccountId//"10604100"
            JOIN ledgerChartOfAccounts where mainAccount.LedgerChartOfAccounts == ledgerChartOfAccounts.RecId
            JOIN Ledger where Ledger.ChartOfAccounts == ledgerChartOfAccounts.RecId;

         select firstonly RecId from dimAttributeLevelValue
        exists join dimAttributeValue where
            dimAttributeValue.RecId == dimAttributeLevelValue.DimensionAttributeValue &&
            dimAttributeValue.EntityInstance == entityInstance &&
            dimAttributeValue.IsDeleted == false
        exists join dimAttribute where
            dimAttribute.RecId == dimAttributeValue.DimensionAttribute &&
            dimAttribute.BackingEntityType == entityId;

        if(!generalJournalAccountEntry && !dimAttrValueComb && !dimAttributeLevelValue)// && !ledgerChartOfAccounts && !Ledger)
        {
            test.clear();
            test.AccountNum = mainAccountLoc.MainAccountId;
            test.Name = LedgerChartOfAccounts::find(mainAccountLoc.LedgerChartOfAccounts).Name;
            test.insert();
            i++;
        }
    }
    info(strFmt("%1 total records", i));
}



SQL Query :
--------------

select * from MAINACCOUNT as a where not exists(
select * FROM generalJournalAccountEntry
            JOIN DimensionAttributeValueCombination on generalJournalAccountEntry.LedgerDimension = DimensionAttributeValueCombination.RecId
            JOIN MAINACCOUNT  as b on DimensionAttributeValueCombination.MainAccount = b.RecId and
b.MAINACCOUNTID = a.MAINACCOUNTID
            JOIN ledgerChartOfAccounts on b.LedgerChartOfAccounts = ledgerChartOfAccounts.RecId
            JOIN Ledger on Ledger.ChartOfAccounts = ledgerChartOfAccounts.RecId) and
        not exists( select * from DimensionAttributeLevelValue
join DimensionAttributeValue on
DimensionAttributeValue.RecId = DimensionAttributeLevelValue.DimensionAttributeValue and
DimensionAttributeValue.EntityInstance = a.RECID and
DimensionAttributeValue.IsDeleted = 0
join DimensionAttribute on
DimensionAttribute.RecId = DimensionAttributeValue.DimensionAttribute
and DimensionAttribute.BackingEntityType = 11762)

@Rahul Talasila