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