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
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:
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