navision inventory report

inf : http://www.mibuso.com/forum/viewtopic.php?t=47341

inf : http://www.mibuso.com/forum/viewtopic.php?t=47341


USE [MyDatabase]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

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

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET DATEFORMAT YMD

SELECT [Inventory Posting Group] ,
[Location Code] ,
[Item No_] AS [Item No_] ,
[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_] ,
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] ,
0 AS InvAndShipDiffers
FROM [MyCompany$Item] ,
( SELECT Positive , /******************************* bettwen date calculate *********************************************/
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 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] >= ISNULL(@S,'1753-01-01') ) AND ( [Item Ledger Posting Date] <= ISNULL(@D,[Item Ledger Posting Date])) 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] >= ISNULL(@S,'1753-01-01') ) AND ( [Item Ledger Posting Date] <= ISNULL(@D,[Item Ledger Posting Date])) 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] >= ISNULL(@S,'1753-01-01') ) AND ( [Item Ledger Posting Date] <= ISNULL(@D,[Item Ledger Posting Date]) ) 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] >= ISNULL(@S,'1753-01-01') ) AND ( [Item Ledger Posting Date] <= ISNULL(@D,[Item Ledger Posting Date])) 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] >= ISNULL(@S,'1753-01-01') ) AND ( [Item Ledger Posting Date] <= ISNULL(@D,[Item Ledger Posting Date])) 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] >= ISNULL(@S,'1753-01-01') ) AND ( [Item Ledger Posting Date] <= ISNULL(@D,[Item Ledger Posting Date]) ) 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] >= ISNULL(@S,'1753-01-01') ) AND ( [Item Ledger Posting Date] <= ISNULL(@D,[Item Ledger Posting Date]) ) 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] >= ISNULL(@S,'1753-01-01') ) AND ( [Item Ledger Posting Date] <= ISNULL(@D,[Item Ledger Posting Date])) 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_]
FROM ( 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)] ,
CASE
WHEN dbo.[MyCompany$Value Entry].[Expected Cost] = 0
THEN CAST(dbo.[MyCompany$Value Entry].[Cost Posted to G_L] AS DECIMAL(38,20))
ELSE CAST(0 AS DECIMAL(38,20))
END AS [Cost Posted to G_L] ,
IL.[Quantity] as [Item Ledger Entry Quantity] ,
0 AS [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] ,
IL.[Posting Date] AS [Item Ledger Posting Date] ,
dbo.[MyCompany$Value Entry].[Item No_]
FROM dbo.[MyCompany$Value Entry]
inner join dbo.[MyCompany$Item Ledger Entry] as IL on [MyCompany$Value Entry].[Item Ledger Entry No_]=IL.[Entry No_]
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 IL.[Posting Date] <= ISNULL(@D,IL.[Posting Date]) )
AND ( dbo.[MyCompany$Value Entry].[Posting Date] >= ISNULL(@S,'1753-01-01'))
/* 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] ,
CASE WHEN dbo.[MyCompany$Value Entry].[Expected Cost] = 0
THEN CAST(dbo.[MyCompany$Value Entry].[Cost Posted to G_L] AS DECIMAL(38,20))
ELSE CAST(0 AS DECIMAL(38,20))
END AS [Cost Posted to GL Start] ,
dbo.[MyCompany$Item Ledger Entry].[Quantity] AS [Quantity Start incl Expected] ,
0 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)] ,
CASE WHEN dbo.[MyCompany$Value Entry].[Expected Cost] = 0 THEN CAST(dbo.[MyCompany$Value Entry].[Cost Posted to G_L] AS DECIMAL(38,20))
ELSE CAST(0 AS DECIMAL(38,20))
END AS [Cost Posted to GL] ,
dbo.[MyCompany$Item Ledger Entry].[Quantity] as [Item Ledger Entry Quantity],
0 AS [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$Item Ledger Entry].[Posting Date] AS [Item Ledger Posting Date] ,
dbo.[MyCompany$Value Entry].[Item No_]
FROM dbo.[MyCompany$Value Entry]
inner join dbo.[MyCompany$Item Ledger Entry]
on dbo.[MyCompany$Value Entry].[Item Ledger Entry No_]=dbo.[MyCompany$Item Ledger Entry].[Entry No_]
WHERE ( dbo.[MyCompany$Value Entry].[Posting Date] < ISNULL(@S,'1753-01-01')
AND dbo.[MyCompany$Value Entry].[Posting Date] <= ISNULL(@D,dbo.[MyCompany$Value Entry].[Posting Date])
AND dbo.[MyCompany$Item Ledger Entry].[Posting Date] <= ISNULL(@D,dbo.[MyCompany$Item Ledger Entry].[Posting Date]) )
/* and (dbo.[MyCompany$Value Entry].[Location Code]=isnull(cast(@LOCATION as varchar),dbo.[MyCompany$Value Entry].[Location Code]))*/
) AS Detail
GROUP BY [Location Code] ,
[Item No_] ,
Positive ,
[Posting Date] ,
[Item Ledger Posting Date] ) 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_] ) AS UpdatedII
WHERE ( [Cost Amount (Actual)] <> 0 OR [Cost Amount (Actual) Start] <> 0 OR [Cost Posted to G_L] <> 0 OR [Cost Posted to GL Start] <> 0 OR DecCostActual <> 0 OR IncCostActual <> 0 OR InvDecreases <> 0 OR InvIncreases <> 0 OR [Invoiced Quantity] <> 0 OR [Quantity Start Invoiced] <> 0 )
UNION
SELECT [Inventory Posting Group] , /***********************************************************************/
[Location Code] ,
[Item No_] AS [Item No_] ,
ISNULL([Quantity Start incl Expected],0) - ISNULL([Quantity Start Invoiced],0) AS [Quantity Invoiced Start] ,
ISNULL([Cost Amount (Expected) Start],0)-ISNULL([Cost Amount (Actual) Start],0) AS [Cost Amount (Actual) Start] ,
ISNULL([Expected Cost Posted to G_L Start],0)-ISNULL([Cost Posted to GL Start],0) AS [Cost Posted to GL Start] ,
ISNULL(RcdIncreases,0) - ISNULL(InvIncreases,0) AS [Invoiced Quantity Increases] ,
ISNULL(IncCostExpected,0)-ISNULL(IncCostActual,0) AS [Cost Amount (Actual) Increases] ,
ISNULL(ShipDecreases,0) - ISNULL(InvDecreases,0) AS [Invoiced Quantity Decreases] ,
ISNULL(DecCostExpected,0)-ISNULL(DecCostActual,0) AS [Cost Amount (Actual) Decreases] ,
ISNULL([Quantity incl. Expected],0) - ISNULL([Invoiced Quantity],0) AS [Invoiced Quantity End] ,
ISNULL([Cost Amount (Expected)],0)-ISNULL([Cost Amount (Actual)],0) AS [Cost Amount (Actual) End] ,
ISNULL([Expected Cost Posted to G_L],0)-ISNULL([Cost Posted to G_L],0) AS [Cost Posted to G_L End] ,
CAST(1 AS BIT) AS [Expected Cost]
FROM ( SELECT [Inventory Posting Group] ,
[Location Code] ,
[Item No_] ,
SUM([Quantity Start Invoiced]) AS [Quantity Start Invoiced] ,
SUM([Quantity Start incl Expected]) AS [Quantity Start incl Expected] ,
SUM([Cost Amount (Actual) Start]) AS [Cost Amount (Actual) 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(InvIncreases) AS InvIncreases ,
SUM([Cost Amount (Expected) Start]) AS [Cost Amount (Expected) Start] ,
SUM(RcdIncreases) AS RcdIncreases ,
SUM(IncCostActual) AS IncCostActual ,
SUM(IncCostExpected) AS IncCostExpected ,
SUM(InvDecreases) AS InvDecreases ,
SUM(DecCostActual) AS DecCostActual ,
SUM(DecCostExpected) AS DecCostExpected ,
SUM(ShipDecreases) AS ShipDecreases ,
SUM([Invoiced Quantity]) AS [Invoiced Quantity] ,
SUM([Cost Amount (Actual)]) AS [Cost Amount (Actual)] ,
SUM([Cost Posted to G_L]) AS [Cost Posted to G_L] ,
SUM([Quantity incl. Expected]) AS [Quantity incl. Expected] ,
SUM([Cost Amount (Expected)]) AS [Cost Amount (Expected)] ,
SUM([Expected Cost Posted to G_L]) AS [Expected Cost Posted to G_L] ,
InvAndShipDiffers
FROM ( SELECT [Inventory Posting Group] ,
LocationCode AS [Location Code] ,
[Item No_] ,
ItemLedgerEntryNo ,
SUM([Quantity Start Invoiced]) AS [Quantity Start Invoiced] ,
SUM([Quantity Start incl Expected]) AS [Quantity Start incl Expected] ,
SUM([Cost Amount (Actual) Start]) AS [Cost Amount (Actual) 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(InvIncreases) AS InvIncreases ,
SUM([Cost Amount (Expected) Start]) AS [Cost Amount (Expected) Start] ,
SUM(RcdIncreases) AS RcdIncreases ,
SUM(IncCostActual) AS IncCostActual ,
SUM(IncCostExpected) AS IncCostExpected ,
SUM(InvDecreases) AS InvDecreases ,
SUM(DecCostActual) AS DecCostActual ,
SUM(DecCostExpected) AS DecCostExpected ,
SUM(ShipDecreases) AS ShipDecreases ,
SUM([Invoiced Quantity]) AS [Invoiced Quantity] ,
SUM([Cost Amount (Actual)]) AS [Cost Amount (Actual)] ,
SUM([Cost Posted to G_L]) AS [Cost Posted to G_L] ,
SUM([Item Ledger Entry Quantity]) AS [Quantity incl. Expected] ,
SUM([Cost Amount (Expected)]) AS [Cost Amount (Expected)] ,
SUM([Expected Cost Posted to G_L]) AS [Expected Cost Posted to G_L] ,
CASE
WHEN ((ISNULL(SUM(InvIncreases),0)
+ISNULL(SUM([Quantity Start Invoiced]),0)
-ISNULL(SUM(InvDecreases),0)) <> ((ISNULL(SUM(RcdIncreases),0)
+ ISNULL(SUM([Quantity Start incl Expected]),0)
- ISNULL(SUM(ShipDecreases),0))))
THEN CAST (1 AS BIT)
ELSE CAST(0 AS BIT)
END AS InvAndShipDiffers
FROM [MyCompany$Item] ,
( SELECT Positive ,
SUM([Cost Amount (Expected)]) AS [Cost Amount (Expected)] ,
ItemLedgerEntryNo ,
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] >= ISNULL(@S,'1753-01-01') )
AND ( [Item Ledger Posting Date] <= ISNULL(@D,[Item Ledger Posting Date]))
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] >= ISNULL(@S,'1753-01-01') )
AND ( [Item Ledger Posting Date] <= ISNULL(@D,[Item Ledger Posting Date]))
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] >= ISNULL(@S,'1753-01-01') )
AND ( [Item Ledger Posting Date] <= ISNULL(@D,[Item Ledger Posting Date]) )
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] >= ISNULL(@S,'1753-01-01') )
AND ( [Item Ledger Posting Date] <= ISNULL(@D,[Item Ledger Posting Date])) 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] >= ISNULL(@S,'1753-01-01') )
AND ( [Item Ledger Posting Date] <= ISNULL(@D,[Item Ledger Posting Date]))
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] >= ISNULL(@S,'1753-01-01') ) AND ( [Item Ledger Posting Date] <= ISNULL(@D,[Item Ledger Posting Date]) ) 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] >= ISNULL(@S,'1753-01-01') ) AND ( [Item Ledger Posting Date] <= ISNULL(@D,[Item Ledger Posting Date]) ) 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] >= ISNULL(@S,'1753-01-01') ) AND ( [Item Ledger Posting Date] <= ISNULL(@D,[Item Ledger Posting Date])) 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_]
FROM ( 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)] ,
CASE WHEN dbo.[MyCompany$Value Entry].[Expected Cost] = 1 THEN CAST(dbo.[MyCompany$Value Entry].[Cost Posted to G_L] AS DECIMAL(38,20))
ELSE CAST(0 AS DECIMAL(38,20))
END AS [Expected Cost Posted to G_L] ,
case when dbo.[MyCompany$Value Entry].[Entry No_] =
(Select MIN([Entry No_]) from dbo.[MyCompany$Value Entry] as GU
where GU.[Item Ledger Entry No_]=dbo.[MyCompany$Value Entry].[Item Ledger Entry No_])
THEN CAST(IL.[Quantity]AS DECIMAL(38,20))
END AS [Item Ledger Entry Quantity],
0 AS [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] ,
IL.[Posting Date] AS [Item Ledger Posting Date] ,
dbo.[MyCompany$Value Entry].[Item No_] ,
IL.[Entry No_] AS ItemLedgerEntryNo
FROM dbo.[MyCompany$Value Entry]
inner join dbo.[MyCompany$Item Ledger Entry] as IL on dbo.[MyCompany$Value Entry].[Item Ledger Entry No_]=IL.[Entry No_]
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 IL.[Posting Date] <= ISNULL(@D,IL.[Posting Date]) )
AND ( dbo.[MyCompany$Value Entry].[Posting Date] >= ISNULL(@S,'1753-01-01'))
/* 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] ,
0 AS [Cost Posted to G_L Start] ,
case when dbo.[MyCompany$Value Entry].[Entry No_] =
(Select MIN([Entry No_]) from dbo.[MyCompany$Value Entry] as GU
where GU.[Item Ledger Entry No_]=dbo.[MyCompany$Value Entry].[Item Ledger Entry No_])
THEN CAST(dbo.[MyCompany$Item Ledger Entry].[Quantity]AS DECIMAL(38,20))
END AS [Quantity Start incl Expected],
CASE WHEN dbo.[MyCompany$Value Entry].[Expected Cost] = 1
THEN CAST(dbo.[MyCompany$Value Entry].[Cost Posted to G_L] AS DECIMAL(38,20))
ELSE CAST(0 AS DECIMAL(38,20))
END AS [Expected Cost Posted to GL 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)] ,
CASE WHEN dbo.[MyCompany$Value Entry].[Expected Cost] = 1
THEN CAST(dbo.[MyCompany$Value Entry].[Cost Posted to G_L] AS DECIMAL(38,20))
ELSE CAST(0 AS DECIMAL(38,20))
END AS [Expected Cost Posted to G_L] ,
case when dbo.[MyCompany$Value Entry].[Entry No_] =
(Select MIN([Entry No_]) from dbo.[MyCompany$Value Entry] as GU
where GU.[Item Ledger Entry No_]=dbo.[MyCompany$Value Entry].[Item Ledger Entry No_])
THEN CAST(dbo.[MyCompany$Item Ledger Entry].[Quantity]AS DECIMAL(38,20))
END AS [Item Ledger Entry Quantity],
0 AS [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$Item Ledger Entry].[Posting Date] AS [Item Ledger Posting Date] ,
dbo.[MyCompany$Value Entry].[Item No_] ,
dbo.[MyCompany$Value Entry].[Item Ledger Entry No_] AS ItemLedgerEntryNo
FROM dbo.[MyCompany$Value Entry]
inner join dbo.[MyCompany$Item Ledger Entry] on dbo.[MyCompany$Value Entry].[Item Ledger Entry No_]=dbo.[MyCompany$Item Ledger Entry].[Entry No_]
WHERE ( dbo.[MyCompany$Value Entry].[Posting Date] < ISNULL(@S,'1753-01-01') AND dbo.[MyCompany$Value Entry].[Posting Date] <= ISNULL(@D,dbo.[MyCompany$Value Entry].[Posting Date]) AND dbo.[MyCompany$Item Ledger Entry].[Posting Date] <= ISNULL(@D,dbo.[MyCompany$Item Ledger Entry].[Posting Date]) )
/* and (dbo.[MyCompany$Value Entry].[Location Code]=isnull(cast(@LOCATION as varchar),dbo.[MyCompany$Value Entry].[Location Code]))*/ ) AS DetailA
GROUP BY [Location Code] ,
[Item No_] ,
Positive ,
[Posting Date] ,
[Item Ledger Posting Date] ,
ItemLedgerEntryNo ) 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 [Inventory Posting Group] ,
LocationCode ,
[Item No_] ,
ItemLedgerEntryNo ) AS UpdatedB /* 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] ,
[Location Code] ,
[Item No_] ,
InvAndShipDiffers
) AS UpdatedIIA
WHERE (UpdatedIIA.InvAndShipDiffers = 1) AND
( UpdatedIIA.[Cost Amount (Actual)] <> 0 OR UpdatedIIA.[Cost Amount (Actual) Start] <> 0 OR UpdatedIIA.[Cost Amount (Expected)] <> 0 OR UpdatedIIA.[Cost Posted to G_L] <> 0 OR UpdatedIIA.[Cost Posted to GL Start] <> 0 OR UpdatedIIA.DecCostActual <> 0 OR UpdatedIIA.DecCostExpected <> 0 OR UpdatedIIA.[Expected Cost Posted to G_L] <> 0 OR UpdatedIIA.[Expected Cost Posted to G_L Start] <> 0 OR UpdatedIIA.IncCostActual <> 0 OR UpdatedIIA.IncCostExpected <> 0 OR UpdatedIIA.InvDecreases <> 0 OR UpdatedIIA.InvIncreases <> 0 OR UpdatedIIA.[Invoiced Quantity] <> 0 OR UpdatedIIA.[Quantity incl. Expected] <> 0 OR UpdatedIIA.[Quantity Start incl Expected] <> 0 OR UpdatedIIA.[Quantity Start Invoiced] <> 0 )
ORDER BY [Inventory Posting Group] ,
[Location Code] ,
[Item No_] ,
[Expected Cost]
END