Report Inventory Valuation/ SQL Query Alternative

herrlutzherrlutz Member Posts: 58
see newest version below

Comments

  • herrlutzherrlutz Member Posts: 58
    edited 2013-12-07
    see newest Version below
  • herrlutzherrlutz Member Posts: 58
    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_RUrmv_RU Member Posts: 119
    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
    Looking for part-time work.
    Nav, T-SQL.
  • herrlutzherrlutz Member Posts: 58
    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_RUrmv_RU Member Posts: 119
    edited 2013-12-09
    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
    Looking for part-time work.
    Nav, T-SQL.
  • herrlutzherrlutz Member Posts: 58
    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_RUrmv_RU Member Posts: 119
    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.
    Looking for part-time work.
    Nav, T-SQL.
  • herrlutzherrlutz Member Posts: 58
    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_RUrmv_RU Member Posts: 119
    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_]
    
    
    
    Looking for part-time work.
    Nav, T-SQL.
  • herrlutzherrlutz Member Posts: 58
    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_RUrmv_RU Member Posts: 119
    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.
    Looking for part-time work.
    Nav, T-SQL.
  • herrlutzherrlutz Member Posts: 58
    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_RUrmv_RU Member Posts: 119
    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?
    Looking for part-time work.
    Nav, T-SQL.
  • rmv_RUrmv_RU Member Posts: 119
    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'
    
    
    Looking for part-time work.
    Nav, T-SQL.
  • herrlutzherrlutz Member Posts: 58
    not sure if i have time today to check... will come back to it latest wednesday night
  • Miklos_HollenderMiklos_Hollender Member Posts: 1,598
    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_HollenderMiklos_Hollender Member Posts: 1,598
    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_RUrmv_RU Member Posts: 119
    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.
    Looking for part-time work.
    Nav, T-SQL.
  • herrlutzherrlutz Member Posts: 58
    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
    
    
  • gafuentesgafuentes Member Posts: 1
    --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
Sign In or Register to comment.