stoneridgesoftware: I Can’t Delete An Account In My Dynamics AX Chart of Accounts

[Blog bot]

Источник: https://stoneridgesoftware.com/i-can...t-of-accounts/
==============

When building your chart of accounts in Microsoft Dynamics AX, you’ll likely find that accounts may need to be deleted because it’s no longer needed or was entered in error. If transactions exist for the account, deleting the account is not an option. However, if there’s no transactions for the account you’d like to delete, you may see an error message that prevents deleting the account. Here’s an example of this scenario, and a couple of options to remove the account from the chart of accounts.

Let’s say we notice there’s a duplicate “Petty Cash” account. When I attempt to delete the account, I receive an error stating “A financial dimension value is based on the 110181 record and has been used on a transaction. You cannot delete the 110181 record.”



If I review the posted transactions of the account, I can see no transactions have been posted this account. The reason AX is not allowing me to delete this account is because it was either selected on a posting profile, such as Accounts for automatic transactions, Customer/Vendor posting profiles, or Inventory posting (to name a few), or it was selected on a journal line – even if the line was deleted and the journal was never posted.

When an account is defined in a configuration, the system generates records in the following tables that prevent the account from being deleted:

• DIMENSIONATTRIBUTEVALUEGROUPCOMBINATION• DIMENSIONATTRIBUTEVALUEGROUP• DIMENSIONATTRIBUTELEVELVALUE• DIMENSIONATTRIBUTEVALUE• DIMENSIONATTRIBUTEVALUECOMBINATIONThe following queries will confirm no transactions have been posted on the account to be deleted, and will show the records in the above tables preventing deletion. Replace 110181 with the account number that’s to be deleted.

--VALIDATES NO LEDGER TRANSACTIONS HAVE BEEN POSTED.SELECT * FROM GENERALJOURNALACCOUNTENTRY GJAE INNER JOIN DIMENSIONATTRIBUTEVALUECOMBINATION DAVC ON GJAE.LEDGERDIMENSION = DAVC.RECID INNER JOIN MAINACCOUNT MA ON DAVC.MAINACCOUNT = MA.RECID INNER JOIN LEDGERCHARTOFACCOUNTS LCOA ON MA.LEDGERCHARTOFACCOUNTS = LCOA.RECID INNER JOIN LEDGER L ON L.CHARTOFACCOUNTS = LCOA.RECIDWHERE MA.MAINACCOUNTID IN ('110181')--After the above returns no results, we can select the records that need to be deleted using the following queries:--SELECTS DIMENSIONATTRIBUTEVALUEGROUPCOMBINATION RECORDSSELECT * FROM DIMENSIONATTRIBUTELEVELVALUE DALV INNER JOIN DIMENSIONATTRIBUTEVALUE DAV ON DALV.DIMENSIONATTRIBUTEVALUE = DAV.RECID INNER JOIN DIMENSIONATTRIBUTEVALUEGROUP DAVG ON DALV.DIMENSIONATTRIBUTEVALUEGROUP = DAVG.RECID INNER JOIN DIMENSIONATTRIBUTEVALUEGROUPCOMBINATION DAVGC ON DAVGC.DIMENSIONATTRIBUTEVALUEGROUP = DAVG.RECIDWHERE DAV.RECID IN ( SELECT RECID FROM DIMENSIONATTRIBUTEVALUE WHERE ENTITYINSTANCE IN ( SELECT MA.RECID FROM MAINACCOUNT MA INNER JOIN LEDGERCHARTOFACCOUNTS LCOA ON MA.LEDGERCHARTOFACCOUNTS = LCOA.RECID INNER JOIN LEDGER L ON L.CHARTOFACCOUNTS = LCOA.RECID WHERE MA.MAINACCOUNTID IN ('110181') ) )--SELECTS DIMENSIONATTRIBUTEVALUEGROUP RECORDSSELECT * FROM DIMENSIONATTRIBUTELEVELVALUE DALV INNER JOIN DIMENSIONATTRIBUTEVALUE DAV ON DALV.DIMENSIONATTRIBUTEVALUE = DAV.RECID INNER JOIN DIMENSIONATTRIBUTEVALUEGROUP DAVG ON DALV.DIMENSIONATTRIBUTEVALUEGROUP = DAVG.RECIDWHERE DAV.RECID IN ( SELECT RECID FROM DIMENSIONATTRIBUTEVALUE WHERE ENTITYINSTANCE IN ( SELECT MA.RECID FROM MAINACCOUNT MA INNER JOIN LEDGERCHARTOFACCOUNTS LCOA ON MA.LEDGERCHARTOFACCOUNTS = LCOA.RECID INNER JOIN LEDGER L ON L.CHARTOFACCOUNTS = LCOA.RECID WHERE MA.MAINACCOUNTID IN ('110181') ) )--SELECTS DIMENSIONATTRIBUTELEVELVALUE RECORDSSELECT * FROM DIMENSIONATTRIBUTELEVELVALUE DALV INNER JOIN DIMENSIONATTRIBUTEVALUE DAV ON DALV.DIMENSIONATTRIBUTEVALUE = DAV.RECIDWHERE DAV.RECID IN ( SELECT RECID FROM DIMENSIONATTRIBUTEVALUE WHERE ENTITYINSTANCE IN ( SELECT MA.RECID FROM MAINACCOUNT MA INNER JOIN LEDGERCHARTOFACCOUNTS LCOA ON MA.LEDGERCHARTOFACCOUNTS = LCOA.RECID INNER JOIN LEDGER L ON L.CHARTOFACCOUNTS = LCOA.RECID WHERE MA.MAINACCOUNTID IN ('110181') ) )--SELECTS DIMENSIONATTRIBUTEVALUE RECORDSSELECT * FROM DIMENSIONATTRIBUTEVALUE WHERE ENTITYINSTANCE IN ( SELECT MA.RECID FROM MAINACCOUNT MA INNER JOIN LEDGERCHARTOFACCOUNTS LCOA ON MA.LEDGERCHARTOFACCOUNTS = LCOA.RECID INNER JOIN LEDGER L ON L.CHARTOFACCOUNTS = LCOA.RECID WHERE MA.MAINACCOUNTID IN ('110181') )--SELECTS DIMENSIONATTRIBUTEVALUECOMBINATION RECORDSSELECT * FROM DIMENSIONATTRIBUTEVALUECOMBINATION DAVC INNER JOIN MAINACCOUNT MA ON DAVC.MAINACCOUNT = MA.RECIDWHERE MA.RECID IN ( SELECT MA.RECID FROM MAINACCOUNT MA INNER JOIN LEDGERCHARTOFACCOUNTS LCOA ON MA.LEDGERCHARTOFACCOUNTS = LCOA.RECID INNER JOIN LEDGER L ON L.CHARTOFACCOUNTS = LCOA.RECID WHERE MA.MAINACCOUNTID IN ('110181') )--The above shows the records to be deleted – spot check the results to ensure there’s nothing out of the ordinary.Only if the first query returns no results should you consider proceeding to the next scripts. If the DIMENSION tables return any results, these records must be deleted in order to allow deleting the main account. Always be sure to use a development environment and backup the databases before deleting records. The following will delete the records preventing deletion of the main account:

--DELETES DIMENSIONATTRIBUTEVALUEGROUPCOMBINATION RECORDSDELETE DAVGC FROM DIMENSIONATTRIBUTELEVELVALUE DALV INNER JOIN DIMENSIONATTRIBUTEVALUE DAV ON DALV.DIMENSIONATTRIBUTEVALUE = DAV.RECID INNER JOIN DIMENSIONATTRIBUTEVALUEGROUP DAVG ON DALV.DIMENSIONATTRIBUTEVALUEGROUP = DAVG.RECID INNER JOIN DIMENSIONATTRIBUTEVALUEGROUPCOMBINATION DAVGC ON DAVGC.DIMENSIONATTRIBUTEVALUEGROUP = DAVG.RECIDWHERE DAV.RECID IN ( SELECT RECID FROM DIMENSIONATTRIBUTEVALUE WHERE ENTITYINSTANCE IN ( SELECT MA.RECID FROM MAINACCOUNT MA INNER JOIN LEDGERCHARTOFACCOUNTS LCOA ON MA.LEDGERCHARTOFACCOUNTS = LCOA.RECID INNER JOIN LEDGER L ON L.CHARTOFACCOUNTS = LCOA.RECID WHERE MA.MAINACCOUNTID IN ('110181') ) )--DELETES DIMENSIONATTRIBUTEVALUEGROUP RECORDSDELETE DAVG FROM DIMENSIONATTRIBUTELEVELVALUE DALV INNER JOIN DIMENSIONATTRIBUTEVALUE DAV ON DALV.DIMENSIONATTRIBUTEVALUE = DAV.RECID INNER JOIN DIMENSIONATTRIBUTEVALUEGROUP DAVG ON DALV.DIMENSIONATTRIBUTEVALUEGROUP = DAVG.RECIDWHERE DAV.RECID IN ( SELECT RECID FROM DIMENSIONATTRIBUTEVALUE WHERE ENTITYINSTANCE IN ( SELECT MA.RECID FROM MAINACCOUNT MA INNER JOIN LEDGERCHARTOFACCOUNTS LCOA ON MA.LEDGERCHARTOFACCOUNTS = LCOA.RECID INNER JOIN LEDGER L ON L.CHARTOFACCOUNTS = LCOA.RECID WHERE MA.MAINACCOUNTID IN ('110181') ) )--DELETES DIMENSIONATTRIBUTELEVELVALUE RECORDSDELETE DALV FROM DIMENSIONATTRIBUTELEVELVALUE DALV INNER JOIN DIMENSIONATTRIBUTEVALUE DAV ON DALV.DIMENSIONATTRIBUTEVALUE = DAV.RECIDWHERE DAV.RECID IN ( SELECT RECID FROM DIMENSIONATTRIBUTEVALUE WHERE ENTITYINSTANCE IN ( SELECT MA.RECID FROM MAINACCOUNT MA INNER JOIN LEDGERCHARTOFACCOUNTS LCOA ON MA.LEDGERCHARTOFACCOUNTS = LCOA.RECID INNER JOIN LEDGER L ON L.CHARTOFACCOUNTS = LCOA.RECID WHERE MA.MAINACCOUNTID IN ('110181') ) )--DELETES DIMENSIONATTRIBUTEVALUE RECORDSDELETE FROM DIMENSIONATTRIBUTEVALUE WHERE ENTITYINSTANCE IN ( SELECT MA.RECID FROM MAINACCOUNT MA INNER JOIN LEDGERCHARTOFACCOUNTS LCOA ON MA.LEDGERCHARTOFACCOUNTS = LCOA.RECID INNER JOIN LEDGER L ON L.CHARTOFACCOUNTS = LCOA.RECID WHERE MA.MAINACCOUNTID IN ('110181') )--DELETES DIMENSIONATTRIBUTEVALUECOMBINATION RECORDSDELETE DAVC FROM DIMENSIONATTRIBUTEVALUECOMBINATION DAVC INNER JOIN MAINACCOUNT MA ON DAVC.MAINACCOUNT = MA.RECIDWHERE MA.RECID IN ( SELECT MA.RECID FROM MAINACCOUNT MA INNER JOIN LEDGERCHARTOFACCOUNTS LCOA ON MA.LEDGERCHARTOFACCOUNTS = LCOA.RECID WHERE MA.MAINACCOUNTID IN ('110181') )Once these are deleted, I can now delete the ledger account. If the account being deleted still exists on a configuration somewhere, the account will need to be removed and the correct account assigned to the configuration.

*Please Note: Always be sure to use a development environment and backup the databases before deleting records.







Источник: https://stoneridgesoftware.com/i-can...t-of-accounts/