TILADAX - Locked in Journal

Dynamics AX has a setting in the chart of accounts that allows one to restrict posting to certain accounts. By using this facility one can ensure that only entries from say a subsidiary ledger can be posted to this account. Now anyone who has tried to reconcile a subsidiary account to the ledger control account will know that this is a good idea. During the setup process one would select certain of the ledger accounts to be control accounts. I will use the Accounts Payable module as an example. Under your setup of the Vendor Posting profiles you can select the summary accounts for each vendor group which will act as the control account in the ledger. The problem that I found was that there is no sure fire way of making sure that what is set up in the posting profiles is in fact then locked in journal in the COA's. By missing this one could easily end up having a reconciliation nightmare later on. To this end I created a whole bunch of scripts that tests all my posting profiles across Accounts Payable, Account Receivable, Bank, Tax and Fixed Assets to the COA to make sure that they are locked in journal.

Using my AP example I found the table that holds the accounts and then joined this to the COA table. Here is the script:

select * from
( select distinct sumaccount as Accounts
from dbo.VENDLEDGERACCOUNTS) as a
left join dbo.LEDGERTABLE b
on a.Accounts=b.AccountNum
where b.BlockedInJournal=0
or b.AccountNum is null

This will test for either the account not being locked in journal or the account missing from the COA. (I did not join on DataAreaId as my COA is shared amongst all the companies).

In order to fully test your system you would also need to write a similar script for the following tables:

BankAccountTable
AssetLedgerAccounts
CustLedgerAccounts
LedgerIntercompany
TaxLedgerAccountGroup

The list is not exhaustive but should get you going.

You will have to use the pivot function on the inter company and tax tables to test all of them.

- Paul Steynberg