Sql scripts for ledger tables checking

rmv_RU

Hi all, let me share some useful sql scripts, it really helps me in my daily routine.

1. GL Consistency check.
The script shows a list of posted documents, which are not balanced

select [Document No_], [Posting Date],
sum(Amount) as Amount, sum([Additional-Currency Amount]) as [Additional-Currency Amount]
from [Company$G_L Entry]
group by
[Document No_], [Posting Date]
having
sum(Amount) <>0
or sum([Additional-Currency Amount])<>0

How to run:
1. Copy text into a Query Analizer or a SQL Server Management Studio.
2. Replace $Company with an actual company name.
3. Run the script.

rmv_RU

2. Item Ledger Entry Consistency check.
Sum(quantity) must be equal to Sum([Remaining Quantity] by Item No..
The script shows problem Items.

select [Item No_], sum(Quantity) as Qty, sum([Remaining Quantity]) as RemQty
from [Company$item ledger entry] ile
group by [Item No_]
having 
sum(Quantity) <> sum([Remaining Quantity])

How to run:
1. Copy text into a Query Analizer or a SQL Server Management Studio.
2. Replace $Company with an actual company name.
3. Run the script.

rmv_RU

3. Item Ledger Entry and Item Application Entry Consistency check.
"Item Ledger Entry"."Remaining Quantity" of positive(inbound) entry must be equal to a sum("Item Application Entry".Quantity) by the entry.
The script shows problem item ledger entries.

select * from
(
select ILE.[Entry No_], ILE.[Document No_], ILE.[Posting Date], 
ILE.[Item No_], 
cast(ILE.[Quantity] as money) as quantity, 
cast(ILE.[Remaining Quantity] as money) as [Remaining Quantity], 
(select cast(sum(Quantity) as money) from [Company$Item Application Entry] IAE
where IAE.[Inbound Item Entry No_]=ILE.[Entry No_])  as AppQty
from
[Company$Item Ledger Entry] ILE 
where
ILE.Positive=1
) t
where [Remaining Quantity]<>AppQty
order by [Posting Date]

How to run:
1. Copy text into a Query Analizer or a SQL Server Management Studio.
2. Replace $Company with an actual company name.
3. Run the script.