mibuso.com

Microsoft Business Solutions online community
It is currently Thu May 23, 2013 11:03 pm

View unanswered posts | View active topics


Board index » Microsoft Dynamics NAV (formerly Microsoft Business Solutions-Navision) » NAV Tips & Tricks

All times are UTC + 1 hour [ DST ]



Post new topic Reply to topic  Page 1 of 1
  [ 3 posts ] 
  What's this MUID? [MUID:EUGE] Previous topic | Next topic 
Author Message
rmv_RU
 Post subject: Sql scripts for ledger tables checking
PostPosted: Mon Jan 17, 2011 2:59 pm 
Offline

Joined: Fri Jun 04, 2010 2:38 pm
Posts: 54
Location: Moscow
Country: Russia (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
Code: Select all
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.
Top
 Profile E-mail  
 
rmv_RU
 Post subject: Re: Sql scripts for ledger tables checking
PostPosted: Mon Jan 17, 2011 3:00 pm 
Offline

Joined: Fri Jun 04, 2010 2:38 pm
Posts: 54
Location: Moscow
Country: Russia (ru)
2. Item Ledger Entry Consistency check.
Sum(quantity) must be equal to Sum([Remaining Quantity] by Item No..
The script shows problem Items.
Code: Select all
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.
Top
 Profile E-mail  
 
rmv_RU
 Post subject: Re: Sql scripts for ledger tables checking
PostPosted: Mon Jan 17, 2011 3:01 pm 
Offline

Joined: Fri Jun 04, 2010 2:38 pm
Posts: 54
Location: Moscow
Country: Russia (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.
Code: Select all
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.
Top
 Profile E-mail  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  Page 1 of 1
  [ 3 posts ] 

Board index » Microsoft Dynamics NAV (formerly Microsoft Business Solutions-Navision) » NAV Tips & Tricks

All times are UTC + 1 hour [ DST ]

Who is online

Users browsing this forum: No registered users and 2 guests

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum


Search for:
Jump to: