Report Inventory Valuation/ SQL Query Alternative

herrlutz

see newest version below

herrlutz

see newest Version below

herrlutz

Please find the newest Version of my SQL Script, working for NAV5.0 and NAV2009., giving all features of Report 1001 (Expected Costs, Decreases&Increases), but will be much faster

As we are working without "Variant Code" concerning items, "Variant Code" has not been considered in this Report.

i did one improvement: the Expected Costs- rows will show only Expected Costs and not the Actual Costs included.

Parameters are:
@S = Starting Date
@D = Ending Date

it can be called by ADO as described in the links to that Blog-entry:

http://mibuso.com/blogs/ara3n/2009/07/0 ... amics-nav/
___________________________________________________________________________________________

USE [myDatabase]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[InventoryValuation] @S AS DATE,@D AS DATE
AS
BEGIN

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON

Set @S=ISNULL(@S,'1753-01-01')

-- first get all value entries being part of the Report scope and put it into temporary table #myTemp
SELECT DISTINCT
dbo.[MyCompany$Value Entry].[Entry No_] ,
CASE WHEN [MyCompany$Value Entry].[Item Ledger Entry Type] = 0 THEN CAST(1 AS BIT)
WHEN [MyCompany$Value Entry].[Item Ledger Entry Type] = 2 THEN CAST(1 AS BIT)
WHEN [MyCompany$Value Entry].[Item Ledger Entry Type] = 6 THEN CAST(1 AS BIT)
WHEN [MyCompany$Value Entry].[Item Ledger Entry Type] = 4 THEN CASE WHEN [MyCompany$Value Entry].[Valued Quantity] < 0 THEN CAST(0 AS BIT)
ELSE CASE WHEN [MyCompany$Item Application Entry].[Item Ledger Entry No_] IS NULL THEN CAST(1 AS BIT)
ELSE CASE WHEN [MyCompany$Item Ledger Entry].[Location Code] = [MyCompany$Value Entry].[Location Code] THEN CAST(0 AS BIT)
ELSE CAST(1 AS BIT)
END
END
END
ELSE CAST(0 AS BIT)
END AS Positive ,
0 AS [Cost Amount (Expected) Start] ,
0 AS [Cost Amount (Actual) Start] ,
0 AS [Cost Posted to GL Start] ,
0 AS [Quantity Start incl Expected] ,
0 AS [Expected Cost Posted to G_L Start] ,
0 AS [Quantity Start Invoiced] ,
dbo.[MyCompany$Value Entry].[Cost Amount (Expected)] ,
dbo.[MyCompany$Value Entry].[Cost Amount (Actual)] ,
dbo.[MyCompany$Value Entry].[Cost Posted to G_L] ,
dbo.[MyCompany$Value Entry].[Item Ledger Entry Quantity] ,
dbo.[MyCompany$Value Entry].[Expected Cost Posted to G_L] ,
dbo.[MyCompany$Value Entry].[Valued Quantity] ,
dbo.[MyCompany$Value Entry].[Invoiced Quantity] ,
dbo.[MyCompany$Value Entry].[Location Code] ,
dbo.[MyCompany$Value Entry].[Posting Date] AS [Posting Date] ,
dbo.[MyCompany$Value Entry].[Item No_]
into #myTemp FROM dbo.[MyCompany$Value Entry]
LEFT OUTER JOIN dbo.[MyCompany$Item Application Entry] ON dbo.[MyCompany$Value Entry].[Item Ledger Entry No_] = dbo.[MyCompany$Item Application Entry].[Item Ledger Entry No_]
LEFT OUTER JOIN dbo.[MyCompany$Item Ledger Entry] ON dbo.[MyCompany$Item Application Entry].[Outbound Item Entry No_] = dbo.[MyCompany$Item Ledger Entry].[Entry No_]
WHERE dbo.[MyCompany$Value Entry].[Posting Date] <= ISNULL(@D,dbo.[MyCompany$Value Entry].[Posting Date]) AND dbo.[MyCompany$Value Entry].[Posting Date] >= @S
/* and (dbo.[MyCompany$Value Entry].[Location Code]=isnull(cast(@LOCATION as varchar),dbo.[MyCompany$Value Entry].[Location Code]))*/
UNION
SELECT dbo.[MyCompany$Value Entry].[Entry No_] ,
0 AS Positive ,
dbo.[MyCompany$Value Entry].[Cost Amount (Expected)] AS [Cost Amount (Expected) Start] ,
dbo.[MyCompany$Value Entry].[Cost Amount (Actual)] AS [Cost Amount (Actual) Start] ,
dbo.[MyCompany$Value Entry].[Cost Posted to G_L] AS [Cost Posted to GL Start] ,
dbo.[MyCompany$Value Entry].[Item Ledger Entry Quantity] AS [Quantity Start incl Expected] ,
dbo.[MyCompany$Value Entry].[Expected Cost Posted to G_L] AS [Expected Cost Posted to G_L Start] ,
dbo.[MyCompany$Value Entry].[Invoiced Quantity] AS [Quantity Start Invoiced] ,
dbo.[MyCompany$Value Entry].[Cost Amount (Expected)] ,
dbo.[MyCompany$Value Entry].[Cost Amount (Actual)] ,
dbo.[MyCompany$Value Entry].[Cost Posted to G_L] ,
dbo.[MyCompany$Value Entry].[Item Ledger Entry Quantity] ,
dbo.[MyCompany$Value Entry].[Expected Cost Posted to G_L] ,
dbo.[MyCompany$Value Entry].[Valued Quantity] ,
dbo.[MyCompany$Value Entry].[Invoiced Quantity] ,
dbo.[MyCompany$Value Entry].[Location Code] ,
dbo.[MyCompany$Value Entry].[Posting Date] AS [Posting Date] ,
dbo.[MyCompany$Value Entry].[Item No_]
FROM dbo.[MyCompany$Value Entry]
WHERE dbo.[MyCompany$Value Entry].[Posting Date] < @S
/* and (dbo.[MyCompany$Value Entry].[Location Code]=isnull(cast(@LOCATION as varchar),dbo.[MyCompany$Value Entry].[Location Code]))*/


--Group data and Transfer into #myTemp2
SELECT Positive ,
SUM([Cost Amount (Expected)]) AS [Cost Amount (Expected)] ,
SUM([Quantity Start Invoiced]) AS [Quantity Start Invoiced] ,
SUM([Quantity Start incl Expected]) AS [Quantity Start incl Expected] ,
SUM([Cost Amount (Actual)]) AS [Cost Amount (Actual)] ,
SUM([Cost Amount (Actual) Start]) AS [Cost Amount (Actual) Start] ,
SUM([Cost Amount (Expected) Start]) AS [Cost Amount (Expected) Start] ,
SUM([Cost Posted to GL Start]) AS [Cost Posted to GL Start] ,
SUM([Expected Cost Posted to G_L Start]) AS [Expected Cost Posted to G_L Start] ,
SUM([Cost Posted to G_L]) AS [Cost Posted to G_L] ,
SUM([Item Ledger Entry Quantity]) AS [Item Ledger Entry Quantity] ,
SUM([Expected Cost Posted to G_L]) AS [Expected Cost Posted to G_L] ,
CASE WHEN ( [Posting Date] <= ISNULL(@D,[Posting Date]) AND [Posting Date] >= @S ) THEN CASE WHEN Positive = 1 THEN CAST(SUM([Cost Amount (Expected)]) AS DECIMAL(38,20))
END
END AS IncCostExpected ,
CASE WHEN ( [Posting Date] <= ISNULL(@D,[Posting Date]) AND [Posting Date] >= @S ) THEN CASE WHEN Positive = 1 THEN CAST(SUM([Item Ledger Entry Quantity]) AS DECIMAL(38,20))
END
END AS RcdIncreases ,
CASE WHEN ( [Posting Date] <= ISNULL(@D,[Posting Date]) AND [Posting Date] >= @S ) THEN CASE WHEN Positive = 1 THEN CAST(SUM([Cost Amount (Actual)]) AS DECIMAL(38,20))
END
END AS IncCostActual ,
CASE WHEN ( [Posting Date] <= ISNULL(@D,[Posting Date]) AND [Posting Date] >= @S ) THEN CASE WHEN Positive = 1 THEN CAST(SUM([Invoiced Quantity]) AS DECIMAL(38,20))
END
END AS InvIncreases ,
CASE WHEN ( [Posting Date] <= ISNULL(@D,[Posting Date]) AND [Posting Date] >= @S ) THEN CASE WHEN Positive = 0 THEN CAST(SUM([Cost Amount (Expected)] * -1) AS DECIMAL(38,20))
END
END AS DecCostExpected ,
CASE WHEN ( [Posting Date] <= ISNULL(@D,[Posting Date]) AND [Posting Date] >= @S ) THEN CASE WHEN Positive = 0 THEN CAST(SUM([Item Ledger Entry Quantity] * -1) AS DECIMAL(38,20))
END
END AS ShipDecreases ,
CASE WHEN ( [Posting Date] <= ISNULL(@D,[Posting Date]) AND [Posting Date] >= @S ) THEN CASE WHEN Positive = 0 THEN CAST(SUM([Cost Amount (Actual)] * -1) AS DECIMAL(38,20))
END
END AS DecCostActual ,
CASE WHEN ( [Posting Date] <= ISNULL(@D,[Posting Date]) AND [Posting Date] >= @S ) THEN CASE WHEN Positive = 0 THEN CAST(SUM([Invoiced Quantity] * -1) AS DECIMAL(38,20))
END
END AS InvDecreases ,
SUM([Valued Quantity]) AS [Valued Quantity] ,
SUM([Invoiced Quantity]) AS [Invoiced Quantity] ,
[Location Code] AS LocationCode ,
[Item No_]
into #myTemp2 FROM #myTemp AS DetailA
GROUP BY [Location Code] ,
[Item No_] ,
Positive ,
[Posting Date]


drop table #myTemp

SELECT
[Inventory Posting Group] ,
[Location Code] ,
[Item No_] AS [Item No.] ,[Description],
[Quantity Start Invoiced] AS [Quantity Invoiced Start] ,
[Cost Amount (Actual) Start] ,
[Cost Posted to GL Start] ,
InvIncreases AS [Invoiced Quantity Increases] ,
IncCostActual AS [Cost Amount (Actual) Increases] ,
InvDecreases AS [Invoiced Quantity Decreases] ,
DecCostActual AS [Cost Amount (Actual) Decreases] ,
[Invoiced Quantity] AS [Invoiced Quantity End] ,
[Cost Amount (Actual)] AS [Cost Amount (Actual) End] ,
[Cost Posted to G/L] AS [Cost Posted to G/L End] ,
CAST(0 AS BIT) AS [Expected Cost]
FROM ( SELECT
[Inventory Posting Group] ,
LocationCode AS [Location Code] ,
[Item No_] ,[Description],
ISNULL(SUM([Quantity Start Invoiced]),0) AS [Quantity Start Invoiced] ,
ISNULL(SUM([Quantity Start incl Expected]),0) AS [Quantity Start incl Expected] ,
ISNULL(SUM([Cost Amount (Actual) Start]),0) AS [Cost Amount (Actual) Start] ,
ISNULL(SUM([Cost Posted to GL Start]),0) AS [Cost Posted to GL Start] ,
ISNULL(SUM([Expected Cost Posted to G_L Start]),0) AS [Expected Cost Posted to G_L Start] ,
ISNULL(SUM(InvIncreases),0) AS InvIncreases ,
ISNULL(SUM(IncCostActual),0) AS IncCostActual ,
ISNULL(SUM(IncCostExpected),0) AS IncCostExpected ,
ISNULL(SUM(InvDecreases),0) AS InvDecreases ,
ISNULL(SUM(DecCostActual),0) AS DecCostActual ,
ISNULL(SUM(DecCostExpected),0) AS DecCostExpected ,
ISNULL(SUM([Invoiced Quantity]),0) AS [Invoiced Quantity] ,
ISNULL(SUM([Cost Amount (Actual)]),0) AS [Cost Amount (Actual)] ,
ISNULL(SUM([Cost Posted to G_L]),0) AS [Cost Posted to G/L] ,
ISNULL(SUM([Item Ledger Entry Quantity]),0) AS [Quantity incl_ Expected] ,
ISNULL(SUM([Cost Amount (Expected)]),0) AS [Cost Amount (Expected)] ,
ISNULL(SUM([Expected Cost Posted to G_L]),0) AS [Expected Cost Posted to G/L] ,
CASE WHEN ( SUM(InvIncreases) <> SUM(RcdIncreases) OR SUM([Quantity Start Invoiced]) <> SUM([Quantity Start incl Expected]) OR SUM(InvDecreases) <> SUM(ShipDecreases) ) THEN CAST (1 AS BIT)
ELSE CAST(0 AS BIT)
END AS InvAndShipDiffers
FROM [MyCompany$Item] ,
#myTemp2 AS Updated
WHERE ( [MyCompany$Item].[No_] = Updated.[Item No_] ) /* and
([MyCompany$Item].[No_]=isNull (cast(@ITEMNR as varchar),[MyCompany$Item].[No_]))
([MyCompany$Item].[Inventory Posting Group]=ISNULL(cast(@IVPG as varchar),[MyCompany$Item].[Inventory Posting Group])) */
GROUP BY [Inventory Posting Group] ,

LocationCode ,
[Item No_],[Description] ) AS UpdatedII
WHERE ( UpdatedII.[Cost Amount (Actual)] <> 0 OR UpdatedII.[Cost Amount (Actual) Start] <> 0 OR UpdatedII.[Cost Amount (Expected)] <> 0 OR UpdatedII.[Cost Posted to G/L] <> 0 OR UpdatedII.[Cost Posted to GL Start] <> 0 OR UpdatedII.DecCostActual <> 0 OR UpdatedII.DecCostExpected <> 0 OR UpdatedII.[Expected Cost Posted to G/L] <> 0 OR UpdatedII.[Expected Cost Posted to G_L Start] <> 0 OR UpdatedII.IncCostActual <> 0 OR UpdatedII.IncCostExpected <> 0 OR UpdatedII.InvDecreases <> 0 OR UpdatedII.InvIncreases <> 0 OR UpdatedII.[Invoiced Quantity] <> 0 OR UpdatedII.[Quantity incl_ Expected] <> 0 OR UpdatedII.[Quantity Start incl Expected] <> 0 OR UpdatedII.[Quantity Start Invoiced] <> 0 )
UNION
SELECT
[Inventory Posting Group] ,
[Location Code] ,
[Item No_] AS [Item No.] ,[Description],
[Quantity Start incl Expected] - [Quantity Start Invoiced] AS [Quantity Invoiced Start] ,
[Cost Amount (Expected) Start] AS [Cost Amount (Actual) Start] ,
[Expected Cost Posted to G_L Start] AS [Cost Posted to GL Start] ,
RcdIncreases - InvIncreases AS [Invoiced Quantity Increases] ,
IncCostExpected AS [Cost Amount (Actual) Increases] ,
ShipDecreases - InvDecreases AS [Invoiced Quantity Decreases] ,
DecCostExpected AS [Cost Amount (Actual) Decreases] ,
[Quantity incl_ Expected] - [Invoiced Quantity] AS [Invoiced Quantity End] ,
[Cost Amount (Expected)] AS [Cost Amount (Actual) End] ,
[Expected Cost Posted to G/L] AS [Cost Posted to G/L End] ,
CAST(1 AS BIT) AS [Expected Cost]
FROM ( SELECT
[Inventory Posting Group] ,
LocationCode AS [Location Code] ,
[Item No_] ,[Description],
ISNULL(SUM([Quantity Start Invoiced]),0) AS [Quantity Start Invoiced] ,
ISNULL(SUM([Quantity Start incl Expected]),0) AS [Quantity Start incl Expected] ,
ISNULL(SUM([Cost Amount (Actual) Start]),0) AS [Cost Amount (Actual) Start] ,
ISNULL(SUM([Cost Posted to GL Start]),0) AS [Cost Posted to GL Start] ,
ISNULL(SUM([Expected Cost Posted to G_L Start]),0) AS [Expected Cost Posted to G_L Start] ,
ISNULL(SUM(InvIncreases),0) AS InvIncreases ,
ISNULL(SUM([Cost Amount (Expected) Start]),0) AS [Cost Amount (Expected) Start] ,
ISNULL(SUM(RcdIncreases),0) AS RcdIncreases ,
ISNULL(SUM(IncCostActual),0) AS IncCostActual ,
ISNULL(SUM(IncCostExpected),0) AS IncCostExpected ,
ISNULL(SUM(InvDecreases),0) AS InvDecreases ,
ISNULL(SUM(DecCostActual),0) AS DecCostActual ,
ISNULL(SUM(DecCostExpected),0) AS DecCostExpected ,
ISNULL(SUM(ShipDecreases),0) AS ShipDecreases ,
ISNULL(SUM([Invoiced Quantity]),0) AS [Invoiced Quantity] ,
ISNULL(SUM([Cost Amount (Actual)]),0) AS [Cost Amount (Actual)] ,
ISNULL(SUM([Cost Posted to G_L]),0) AS [Cost Posted to G/L] ,
ISNULL(SUM([Item Ledger Entry Quantity]),0) AS [Quantity incl_ Expected] ,
ISNULL(SUM([Cost Amount (Expected)]),0) AS [Cost Amount (Expected)] ,
ISNULL(SUM([Expected Cost Posted to G_L]),0) AS [Expected Cost Posted to G/L] ,
CASE WHEN ( SUM(InvIncreases) <> SUM(RcdIncreases) OR SUM([Quantity Start Invoiced]) <> SUM([Quantity Start incl Expected]) OR SUM(InvDecreases) <> SUM(ShipDecreases) ) THEN CAST (1 AS BIT)
ELSE CAST(0 AS BIT)
END AS InvAndShipDiffers
FROM [MyCompany$Item] ,
#myTemp2 AS UpdatedA
WHERE ( [MyCompany$Item].[No_] = UpdatedA.[Item No_] ) /* and
([MyCompany$Item].[No_]=isnull(cast(@ITEMNR as varchar),[MyCompany$Item].[No_]))
([MyCompany$Item].[Inventory Posting Group]=ISNULL(cast(@IVPG as varchar),[MyCompany$Item].[Inventory Posting Group])) */
GROUP BY
LocationCode ,
[Item No_],[Description] ) AS UpdatedIIA
WHERE UpdatedIIA.InvAndShipDiffers = 1
ORDER BY [Inventory Posting Group] ,
[Location Code] ,
[Item No_] ,
[Expected Cost]
END

rmv_RU

Have you tested the script on large databases?
First of all - it's not necessary to transfer all value entries to temporary table, also Valuation Date not always equal Posting Date.

SELECT 
DISTINCT 

Distinct is unnecessary.

LEFT OUTER JOIN dbo.[MyCompany$Item Application Entry] ON dbo.[MyCompany$Value Entry].[Item Ledger Entry No_] = dbo.[MyCompany$Item Application Entry].[Item Ledger Entry No_]
LEFT OUTER JOIN dbo.[MyCompany$Item Ledger Entry] ON dbo.[MyCompany$Item Application Entry].[Outbound Item Entry No_] = dbo.[MyCompany$Item Ledger Entry].[Entry No_]

Left joins can greatly slow script

herrlutz

Hey man, i never promised to provide the best program ever. My Intention is just to provide something useful to the community for free :-)

regarding "Have you tested the script on large databases?":
i´ve tested it on our 100GB database with 9.174.285 Value Entries. Navision Report 1001 takes forever, this SQL Script provides the result in 3-5 minutes.

In regard to "First of all - it's not necessary to transfer all value entries to temporary table, also Valuation Date not always equal Posting Date.":
As written above, the purpose of the SQL Script is to provide a 100% Programm equivaluent to Report 1001 as this Report is frequently used to reconcile G/L Inventory Accounts.
This also requires to consider all Value Entries between Posting Date of "Starting Date" and "Ending Date". Report 1001 does not care about the "Valuation Date".

rmv_RU

herrlutz wrote:

Hey man, i never promised to provide the best program ever. My Intention is just to provide something useful to the community for free :-)

regarding "Have you tested the script on large databases?":
i´ve tested it on our 100GB database with 9.174.285 Value Entries. Navision Report 1001 takes forever, this SQL Script provides the result in 3-5 minutes.

Be easy . If you lay out the script for everyone to see - be prepared for criticism.
This script may be rewrited to use only Value Entry without any joins and unions

herrlutz

have you ever looked into Report 1001 and see what it does with "Expected Costs"?

How about this:
If you make it to provide a script to the community which is significantly faster than the one above and giving 100% same results as Report 1001 including Expected Costs, then i´ll invite you for dinner on whatever international conference our ways may cross. Otherwise you invite me.
??

rmv_RU

herrlutz wrote:

have you ever looked into Report 1001 and see what it does with "Expected Costs"?
How about this:
If you make it to provide a script to the community which is significantly faster than the one above and giving 100% same results as Report 1001 including Expected Costs, then i´ll invite you for dinner on whatever international conference our ways may cross. Otherwise you invite me.
??

So... On my clothes will be written "Work for food" . Deal!
Small condition - testing should be done with different period options, including year between start and end date and you should lay out estimated and actual execution plan for every run.

herrlutz

Great !
i´m not 100% sure which conclusions the estimated and actual execution plan will give in regard to Performance of both scripts. Maybe you can then instruct me on the concrete instance.
We might schedule a Webmeeting so we can run the comparism on our infrastructure

looking forward your program :-)

rmv_RU

herrlutz wrote:

Great !
i´m not 100% sure which conclusions the estimated and actual execution plan will give in regard to Performance of both scripts. Maybe you can then instruct me on the concrete instance.
We might schedule a Webmeeting so we can run the comparism on our infrastructure

looking forward your program :-)

I use variables like in report 1001. Disputable point is how i find sign for transfer entries, if you find example with no application entries and positive valued quantity - i will analize it.
Code is below.

declare @s smalldatetime
declare @e smalldatetime

set @s='130101'
set @e='131231'


select [Location Code], [Item No_]
, SUM(QtyOnHand) as QtyOnHand
, SUM(RcdIncreases) as RcdIncreases
, SUM(ShipDecreases) as ShipDecreases

, SUM(case when [Posting Date]<@s then [Cost Amount (Expected)] else 0 end) as ValueOfQtyOnHand
, SUM(case when [Posting Date]<@s then [Cost Amount (Actual)] else 0 end) as ValueOfInvoicedQty
, SUM(case when [Posting Date]<@s then [Invoiced Quantity] else 0 end) as InvoicedQty

, SUM(case when [Posting Date]>=@s and ve_sign=1 then [Cost Amount (Expected)] else 0 end) as ValueOfRcdIncreases
, SUM(case when [Posting Date]>=@s and ve_sign=1 then [Cost Amount (Actual)] else 0 end) as ValueOfInvIncreases
, SUM(case when [Posting Date]>=@s and ve_sign=1 then [Invoiced Quantity] else 0 end) as InvIncreases

, SUM(case when [Posting Date]>=@s and ve_sign=-1 then [Cost Amount (Expected)] else 0 end) as CostOfShipDecreases
, SUM(case when [Posting Date]>=@s and ve_sign=-1 then [Cost Amount (Actual)] else 0 end) as CostOfInvDecreases
, SUM(case when [Posting Date]>=@s and ve_sign=-1 then [Invoiced Quantity] else 0 end) as InvDecreases
, SUM([Expected Cost Posted to G_L]) as ExpCostPostedToGL
, SUM([Cost Posted to G_L]) as InvCostPostedToGL

from 
( 
	select *
		, case when [Posting Date]<@s then [Item Ledger Entry Quantity] else 0 end as QtyOnHand
		, case when [Posting Date]>=@s and ve_sign=1 then [Item Ledger Entry Quantity] else 0 end as RcdIncreases
		, case when [Posting Date]>=@s and ve_sign=-1 then -[Item Ledger Entry Quantity] else 0 end as ShipDecreases
	from
	(select *, 
		case when [Item Ledger Entry Type] in (0, 2, 6) then 1 when [Item Ledger Entry Type] in (4) and [Valued Quantity]>0 then 1  else -1 end as ve_sign  
			from  [CRONUS International Ltd_$Value Entry] ve
			where ve.[Posting Date]<=@e 

			) ve2
) ve
group by [Location Code], [Item No_]

herrlutz

no doubt, it´s great simplification just to SUM the Value Entries without Joins like you do.
But in the assessment speed vs. reliablitly i chose the priority for reliability and actually tried to clone the logic of C/AL in Report 1001 without shortcuts, because our users use this Report to reconcile G/L.

This means in Detail:

1. Even though i don´t know a practical instance where it could become wrong, concerning sign for Transfer Entries i decided to go the secure way and joined the Item Application Entries
2. Expected Costs: in your script, the values of "Expected Costs posted to G/L" are just summarized. This might be working for databases, that began operations with NAV2009. But in databases that have already been operated with previous Versions, the "Expected Costs posted to G/L" often didn´t tie up after posting the shipment/receipt and the invoice. This is why Report 1001 uses "InvAndShipDiffers"- variable.


now we have to decide what does it mean in result to our bet :-)

rmv_RU

Actually i just looked into report 1001 in Nav 2009 R2 and wrote script, it's no my way to rewrite C/AL into SQL one to one.
1. Look into a posting codeunit, a report 1001 and turn logic on. If you have a transfer from a location to a another you have one positive entry and one negative entry, there are no other options. Negative entry always determined by the sign of "Valued Quantity" and on the other hand for positive entry we should have same criteria - isn't it? Another ways may double negative and don't increase positive amounts. I don't know why MS implemented report 1001 this way - i assume that report was not properly refactored or we have some features in reversed item ledger entries with negative "Valued Quantity" for negative entry and zero "Valued Quantity" for positive entry.
The second argument - only my implementation keeps balance between periods, i.e QtyOnHand_131201=QtyOnHand_131101 + Turnover_131101-131130.

2. Oh my god, I forgot the most important variable! Well, do you have any doubt that it is possible to calculate this value for each operation or for summarized data without any joins and unions?

Your move.

herrlutz

1. take a new item, post inventory Journal 100 PCS positive adjustment. Then use "Item Reclassification Journal", quanity 5, use identical Location and New Location but different old/new Dimensions.
--> Your Report: 105pcs. invoiced increases.
--> Report 1001: 100 PCs. invoiced increases

2. why are you ironic on that? Fact is, your Report currently gives different figures as Report 1001 (it Shows Expected Costs per Items only if "InvAndShipDiffers"=TRUE). And we don´t want to mislead our community, don´t we? :-)

rmv_RU

herrlutz wrote:

1. take a new item, post inventory Journal 100 PCS positive adjustment. Then use "Item Reclassification Journal", quanity 5, use identical Location and New Location but different old/new Dimensions.
--> Your Report: 105pcs. invoiced increases.
--> Report 1001: 100 PCs. invoiced increases

1. Which version do you use?
2. Can you share all variables for current and next periods?

rmv_RU

It seems to me that we miss reclassification flag in item ledger entries and value entries in standart. OK, lets check new script:

declare @s smalldatetime
declare @e smalldatetime

set @s='130101'
set @e='131231'


select [Location Code], [Item No_]
, SUM(QtyOnHand) as QtyOnHand
, SUM(RcdIncreases) as RcdIncreases
, SUM(ShipDecreases) as ShipDecreases

, SUM(case when [Posting Date]<@s then [Cost Amount (Expected)] else 0 end) as ValueOfQtyOnHand
, SUM(case when [Posting Date]<@s then [Cost Amount (Actual)] else 0 end) as ValueOfInvoicedQty
, SUM(case when [Posting Date]<@s then [Invoiced Quantity] else 0 end) as InvoicedQty

, SUM(case when [Posting Date]>=@s and ve_sign=1 then [Cost Amount (Expected)] else 0 end) as ValueOfRcdIncreases
, SUM(case when [Posting Date]>=@s and ve_sign=1 then [Cost Amount (Actual)] else 0 end) as ValueOfInvIncreases
, SUM(case when [Posting Date]>=@s and ve_sign=1 then [Invoiced Quantity] else 0 end) as InvIncreases

, SUM(case when [Posting Date]>=@s and ve_sign=-1 then -[Cost Amount (Expected)] else 0 end) as CostOfShipDecreases
, SUM(case when [Posting Date]>=@s and ve_sign=-1 then -[Cost Amount (Actual)] else 0 end) as CostOfInvDecreases
, SUM(case when [Posting Date]>=@s and ve_sign=-1 then -[Invoiced Quantity] else 0 end) as InvDecreases
, SUM([Expected Cost Posted to G_L]) as ExpCostPostedToGL
, SUM([Cost Posted to G_L]) as InvCostPostedToGL
, SUM([Invoiced Quantity]) as [Invoiced Quantity]
, SUM([Cost Amount (Actual)]) as [Cost Amount (Actual)]
, SUM([Item Ledger Entry Quantity]) as [Item Ledger Entry Quantity]
into #out
from 
( 
	select *
		, case when [Posting Date]<@s then [Item Ledger Entry Quantity] else 0 end as QtyOnHand
		, case when [Posting Date]>=@s and ve_sign=1 then [Item Ledger Entry Quantity] else 0 end as RcdIncreases
		, case when [Posting Date]>=@s and ve_sign=-1 then -[Item Ledger Entry Quantity] else 0 end as ShipDecreases
	from
	(select *, 
		case when [Item Ledger Entry Type] in (0, 2, 6) then 1 when [Item Ledger Entry Type] in (4) and [Valued Quantity]>0 and ISNULL(outbound_tranfer_entry_exists, -1)>0
			 then 1  else -1 end as ve_sign  
			from  
			(
			select ve.*, 
				(select top 1 ile.[Entry No_]  from [CRONUS International Ltd_$Item Application Entry] iae       
					inner join [CRONUS International Ltd_$Item Ledger Entry] ile on iae.[Item Ledger Entry No_]=ve.[Item Ledger Entry No_]
																				and iae.[Outbound Item Entry No_]=ile.[Entry No_]
																				where ve.[Entry Type]=4 and ve.[Posting Date]>=@s
				) as outbound_tranfer_entry_exists
				from [CRONUS International Ltd_$Value Entry] ve 
			) ve
			
			where ve.[Posting Date]<=@e 

			) ve2
) ve
group by [Location Code], [Item No_]


select *, 
  (QtyOnHand + RcdIncreases - ShipDecreases) -  (InvoicedQty + InvIncreases - InvDecreases) as QtyNotInvoiced
  into #out2
  from #out
order by [Item No_]


--select * from #out2
select * from (
select [Item No_], [Location Code], CAST(0 as bit) as Expected_Cost
	, InvoicedQty as start_Qty
	, ValueOfInvoicedQty as start_Value
	, InvIncreases as inc_Qty
	, ValueOfInvIncreases as inv_Value
	, InvIncreases as dec_Qty
	, CostOfInvDecreases as dec_Value
	, [Invoiced Quantity] as end_Qty
	, [Cost Amount (Actual)] as end_Cost
	, InvCostPostedToGL as cost_Posted2GL
from #out2
union all
select [Item No_], [Location Code], CAST(1 as bit) as Expected_Cost
	, qtyOnHand as start_Qty
	, ValueOfQtyOnHand as start_Value
	, RcdIncreases as inc_Qty
	, ValueOfRcdIncreases as inv_Value
	, ShipDecreases as dec_Qty
	, CostOfShipDecreases as dec_Value
	, [Item Ledger Entry Quantity] as end_Qty
	, ValueOfQtyOnHand + ValueOfRcdIncreases - CostOfShipDecreases as end_Cost
	, ExpCostPostedToGL + InvCostPostedToGL as cost_Posted2GL
from #Out2
where QtyNotInvoiced<>0
) t order by [Item No_], Expected_Cost

select * from #Out2

drop table #out
drop table #out2


--select * from [CRONUS International Ltd_$Value Entry] ve where [Item No_]='1920-S'

herrlutz

not sure if i have time today to check... will come back to it latest wednesday night

Miklos_Hollender

I just want to say I don't envy your job that you had to do this. It's a gigantic effort and, a heroic task and you end up with something your users have expected to work out of the box i.e. they expect standard reports not being unusably slow. (I saw tenders for software purchase where it was written in the tender that every query or report must run under 30 seconds. Of course it is not realistic, just this is what people expect.)

Anyway I admire this heroic task with a side of feeling of pity that someone had to do this

Miklos_Hollender

BTW I have no idea why Microsoft created this report to be so complicated - usually users are interested not in increases and decreases just total value i.e. SUM value entry actual, expected group by item No_ done

rmv_RU

Miklos Hollender wrote:

BTW I have no idea why Microsoft created this report to be so complicated - usually users are interested not in increases and decreases just total value i.e. SUM value entry actual, expected group by item No_ done

You can use this report to make sure that period was closed correctly.

I just want to say I don't envy your job that you had to do this. It's a gigantic effort and, a heroic task and you end up with something your users have expected to work out of the box i.e. they expect standard reports not being unusably slow. (I saw tenders for software purchase where it was written in the tender that every query or report must run under 30 seconds. Of course it is not realistic, just this is what people expect.)
Anyway I admire this heroic task with a side of feeling of pity that someone had to do this

Requirement defines the implementation and i see no reason why we should use slow Navision reports instead of fast SQL procedures and some tasks i'd never implemented with Navision reports.

herrlutz

you are right, the reconcilation of G/L entries using Report 1001 was a real pain and we were forced to find a Workaround cause this Report runs probably more than 10 hours in our biggest databases. But the good thing out of that has been, that we got used to T-SQL and These competences help us a lot in regard to NAV Performance Troubles in General, reporting requirements or data modifications.

In your script, still there were some corrections necessary especially in regard to Expected Costs and this complicated distinction between Increases/decreases.
Now i ran several compares with Report 1001 and all figures tie up now.

And finally the following script is actually 2 times faster than the one i posted a few days ago!
I owe you a dinner - i´ll send you a private email later.

ALTER PROCEDURE [dbo].[InventoryValuation] @S AS DATE,@D AS DATE
AS 
BEGIN 

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 
set nocount on

select [Location Code], [Item No_]
, SUM(QtyOnHand) as QtyOnHand
, SUM(RcdIncreases) as RcdIncreases
, SUM(ShipDecreases) as ShipDecreases

, SUM(case when [Posting Date]<@S then [Cost Amount (Expected)] else 0 end) as ValueOfQtyOnHand
, SUM(case when [Posting Date]<@S then [Cost Amount (Actual)] else 0 end) as ValueOfInvoicedQty
, SUM(case when [Posting Date]<@S then [Cost Posted to G_L] else 0 end) as CostGLStart
, SUM(case when [Posting Date]<@S then [Expected Cost Posted to G_L] else 0 end) as ExpCostGLStart
, SUM(case when [Posting Date]<@S then [Invoiced Quantity] else 0 end) as InvoicedQty

, SUM(case when [Posting Date]>=@S and ve_sign=1 then [Cost Amount (Expected)] else 0 end) as ValueOfRcdIncreases
, SUM(case when [Posting Date]>=@S and ve_sign=1 then [Cost Amount (Actual)] else 0 end) as ValueOfInvIncreases
, SUM(case when [Posting Date]>=@S and ve_sign=1 then [Invoiced Quantity] else 0 end) as InvIncreases

, SUM(case when [Posting Date]>=@S and ve_sign=-1 then -[Cost Amount (Expected)] else 0 end) as CostOfShipDecreases
, SUM(case when [Posting Date]>=@S and ve_sign=-1 then -[Cost Amount (Actual)] else 0 end) as CostOfInvDecreases
, SUM(case when [Posting Date]>=@S and ve_sign=-1 then -[Invoiced Quantity] else 0 end) as InvDecreases
, SUM([Expected Cost Posted to G_L]) as ExpCostPostedToGL
, SUM([Cost Posted to G_L]) as InvCostPostedToGL
, SUM([Invoiced Quantity]) as [Invoiced Quantity]
, SUM([Cost Amount (Actual)]) as [Cost Amount (Actual)]
, SUM([Item Ledger Entry Quantity]) as [Item Ledger Entry Quantity]
into #out
from 
( 
   select *
      , case when [Posting Date]<@S then [Item Ledger Entry Quantity] else 0 end as QtyOnHand
      , case when [Posting Date]>=@S and ve_sign=1 then [Item Ledger Entry Quantity] else 0 end as RcdIncreases
      , case when [Posting Date]>=@S and ve_sign=-1 then -[Item Ledger Entry Quantity] else 0 end as ShipDecreases
   from
   (select *, 
      case when [Item Ledger Entry Type] in (0, 2, 6) then 1 when [Item Ledger Entry Type] in (4) and [Valued Quantity]>0 and ISNULL(outbound_tranfer_entry_exists, -1)<0
          then 1  else -1 end as ve_sign  
         from  
         (
         select ve.*, 
            (select top 1 ile.[Entry No_]  from [CRONUS International Ltd_$Item Application Entry] iae       
               inner join [CRONUS International Ltd_$Item Ledger Entry] ile on iae.[Item Ledger Entry No_]=ve.[Item Ledger Entry No_]
                                                            and iae.[Outbound Item Entry No_]=ile.[Entry No_]
                                                            and ile.[Location Code]=ve.[Location Code]
                                                            and ile.[Variant Code]=ve.[Variant Code]
                                                            and ile.[Item No_]=ve.[Item No_]
                                                            where ve.[Item Ledger Entry Type]=4 and ve.[Posting Date]>=@S and ve.[Valued Quantity]>0
            ) as outbound_tranfer_entry_exists
            from [CRONUS International Ltd_$Value Entry] ve 
         ) ve
         
         where ve.[Posting Date]<=@D 

         ) ve2
) ve
group by [Location Code], [Item No_]


select *, 
case when QtyOnHand<>InvoicedQty or RcdIncreases<>InvIncreases or ShipDecreases<>InvDecreases then 1 else 0 end
   as QtyNotInvoiced
  into #out2
  from #out
order by [Item No_]


--select * from #out2
select * from (
select [Item No_], [Location Code]
   , InvoicedQty as [Quantity Invoiced Start]
   , ValueOfInvoicedQty as [Cost Amount (Actual) Start]
   , CostGLStart as [Cost Posted to GL Start]
   , InvIncreases as [Invoiced Quantity Increases]
   , ValueOfInvIncreases as [Cost Amount (Actual) Increases]
   , InvDecreases as [Invoiced Quantity Decreases]
   , CostOfInvDecreases as [Cost Amount (Actual) Decreases]
   , [Invoiced Quantity] as [Invoiced Quantity End]
   , [Cost Amount (Actual)] as [Cost Amount (Actual) End]
   , InvCostPostedToGL as [Cost Posted to G/L End]
   , CAST(0 as bit) as Expected_Cost
from #out2
union all
select [Item No_], [Location Code]
   , QtyOnHand-InvoicedQty as [Quantity Invoiced Start]
   , ValueOfQtyOnHand as [Cost Amount (Actual) Start]
   , ExpCostGLStart as [Cost Posted to GL Start]   
   , RcdIncreases-InvIncreases as [Invoiced Quantity Increases]
   , ValueOfRcdIncreases as [Cost Amount (Actual) Increases]
   , ShipDecreases-InvDecreases as [Invoiced Quantity Decreases]
   , CostOfShipDecreases as [Cost Amount (Actual) Decreases]
   , [Item Ledger Entry Quantity]-[Invoiced Quantity] as [Invoiced Quantity End]
   , ValueOfQtyOnHand + ValueOfRcdIncreases - CostOfShipDecreases as [Cost Amount (Actual) End]
   , ExpCostPostedToGL as [Cost Posted to G/L End]
   , CAST(1 as bit) as Expected_Cost
from #out2
where QtyNotInvoiced<>0
) t order by [Item No_], Expected_Cost

--select * from #Out2

drop table #out
drop table #out2

END

gafuentes

--Looks good with NAV 2016 NA version CU8. Thank you again for the script.
--Select Database
--Create New Query
--Run this query to create PROCEDURE found in the Database->Stored Procedure
--Locate it and right mouse click to EXECUTE.
--Popup screen appears, enter the required data range:
-- @S = '2010-01-01 00:00:00.000'
-- @D = '2017-05-24 00:00:00.000'
-- Change the database name as needed and company name.

USE [Demo Database NAV (9-0)]
GO
/****** Object: StoredProcedure [dbo].[InventoryValuation] Script Date: 5/24/2017 10:06:22 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[InventoryValuation] @S AS DATE,@D AS DATE
AS
BEGIN

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
set nocount on

select [Location Code], [Item No_]
, SUM(QtyOnHand) as QtyOnHand
, SUM(RcdIncreases) as RcdIncreases
, SUM(ShipDecreases) as ShipDecreases

, SUM(case when [Posting Date]<@S then [Cost Amount (Expected)] else 0 end) as ValueOfQtyOnHand
, SUM(case when [Posting Date]<@S then [Cost Amount (Actual)] else 0 end) as ValueOfInvoicedQty
, SUM(case when [Posting Date]<@S then [Cost Posted to G_L] else 0 end) as CostGLStart
, SUM(case when [Posting Date]<@S then [Expected Cost Posted to G_L] else 0 end) as ExpCostGLStart
, SUM(case when [Posting Date]<@S then [Invoiced Quantity] else 0 end) as InvoicedQty

, SUM(case when [Posting Date]>=@S and ve_sign=1 then [Cost Amount (Expected)] else 0 end) as ValueOfRcdIncreases
, SUM(case when [Posting Date]>=@S and ve_sign=1 then [Cost Amount (Actual)] else 0 end) as ValueOfInvIncreases
, SUM(case when [Posting Date]>=@S and ve_sign=1 then [Invoiced Quantity] else 0 end) as InvIncreases

, SUM(case when [Posting Date]>=@S and ve_sign=-1 then -[Cost Amount (Expected)] else 0 end) as CostOfShipDecreases
, SUM(case when [Posting Date]>=@S and ve_sign=-1 then -[Cost Amount (Actual)] else 0 end) as CostOfInvDecreases
, SUM(case when [Posting Date]>=@S and ve_sign=-1 then -[Invoiced Quantity] else 0 end) as InvDecreases
, SUM([Expected Cost Posted to G_L]) as ExpCostPostedToGL
, SUM([Cost Posted to G_L]) as InvCostPostedToGL
, SUM([Invoiced Quantity]) as [Invoiced Quantity]
, SUM([Cost Amount (Actual)]) as [Cost Amount (Actual)]
, SUM([Item Ledger Entry Quantity]) as [Item Ledger Entry Quantity]
into #out
from
(
select *
, case when [Posting Date]<@S then [Item Ledger Entry Quantity] else 0 end as QtyOnHand
, case when [Posting Date]>=@S and ve_sign=1 then [Item Ledger Entry Quantity] else 0 end as RcdIncreases
, case when [Posting Date]>=@S and ve_sign=-1 then -[Item Ledger Entry Quantity] else 0 end as ShipDecreases
from
(select *,
case when [Item Ledger Entry Type] in (0, 2, 6) then 1 when [Item Ledger Entry Type] in (4) and [Valued Quantity]>0 and ISNULL(outbound_tranfer_entry_exists, -1)<0
then 1 else -1 end as ve_sign
from
(
select ve.*,
(select top 1 ile.[Entry No_] from [CRONUS USA, Inc_$Item Application Entry] iae
inner join [CRONUS USA, Inc_$Item Ledger Entry] ile on iae.[Item Ledger Entry No_]=ve.[Item Ledger Entry No_]
and iae.[Outbound Item Entry No_]=ile.[Entry No_]
and ile.[Location Code]=ve.[Location Code]
and ile.[Variant Code]=ve.[Variant Code]
and ile.[Item No_]=ve.[Item No_]
where ve.[Item Ledger Entry Type]=4 and ve.[Posting Date]>=@S and ve.[Valued Quantity]>0
) as outbound_tranfer_entry_exists
from [CRONUS USA, Inc_$Value Entry] ve
) ve

where ve.[Posting Date]<=@D

) ve2
) ve
group by [Location Code], [Item No_]


select *,
case when QtyOnHand<>InvoicedQty or RcdIncreases<>InvIncreases or ShipDecreases<>InvDecreases then 1 else 0 end
as QtyNotInvoiced
into #out2
from #out
order by [Item No_]


--select * from #out2
select * from (
select [Item No_], [Location Code]
, InvoicedQty as [Quantity Invoiced Start]
, ValueOfInvoicedQty as [Cost Amount (Actual) Start]
, CostGLStart as [Cost Posted to GL Start]
, InvIncreases as [Invoiced Quantity Increases]
, ValueOfInvIncreases as [Cost Amount (Actual) Increases]
, InvDecreases as [Invoiced Quantity Decreases]
, CostOfInvDecreases as [Cost Amount (Actual) Decreases]
, [Invoiced Quantity] as [Invoiced Quantity End]
, [Cost Amount (Actual)] as [Cost Amount (Actual) End]
, InvCostPostedToGL as [Cost Posted to G/L End]
, CAST(0 as bit) as Expected_Cost
from #out2
union all
select [Item No_], [Location Code]
, QtyOnHand-InvoicedQty as [Quantity Invoiced Start]
, ValueOfQtyOnHand as [Cost Amount (Actual) Start]
, ExpCostGLStart as [Cost Posted to GL Start]
, RcdIncreases-InvIncreases as [Invoiced Quantity Increases]
, ValueOfRcdIncreases as [Cost Amount (Actual) Increases]
, ShipDecreases-InvDecreases as [Invoiced Quantity Decreases]
, CostOfShipDecreases as [Cost Amount (Actual) Decreases]
, [Item Ledger Entry Quantity]-[Invoiced Quantity] as [Invoiced Quantity End]
, ValueOfQtyOnHand + ValueOfRcdIncreases - CostOfShipDecreases as [Cost Amount (Actual) End]
, ExpCostPostedToGL as [Cost Posted to G/L End]
, CAST(1 as bit) as Expected_Cost
from #out2
where QtyNotInvoiced<>0
) t order by [Item No_], Expected_Cost

--select * from #Out2

drop table #out
drop table #out2

END