Physical Inventory

Hi!

I wrote this sql query to get the inventory level for a date in the past.

------------------------------------------------------------------------------------------------------ USE [m0851System]

GO

/****** Object: StoredProcedure [dbo].[sp_RptRetailInvASAT] Script Date:

01/04/2008 16:02:32 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- ===========================================-- Author: Julien Bonnier

-- Create date: 26 juillet 2007

-- Description: Rpt Niveau d'inv Ret AS AT

-- ===========================================ALTER PROCEDURE [dbo].[sp_RptRetailInvASAT]

-- Add the parameters for the stored procedure here

@Date char(8)

AS

SET NOCOUNT ON;

SELECT t1.Item, t1.StoreName, t1.StoreID, t1.ItemDescription, SUM(t1.Quantity) AS QTY, MAX(t1.Price) AS SalePrice, MAX(t1.Cost) AS Cost

FROM

(

/* TRANSFERTS */

SELECT

Store.Name as StoreName,

Store.ID as StoreID,

Item.ItemLookupCode AS Item,

Item.Description AS ItemDescription,

InventoryTransferLog.ReferenceID AS ReferenceID,

InventoryTransferLog.ReferenceEntryID AS ReferenceEntryID,

InventoryTransferLog.Type AS Type,

(InventoryTransferLog.Quantity * -1) AS Quantity,

InventoryTransferLog.DateTransferred AS DateTransferred,

PurchaseOrder.PONumber AS PONumber,

CASE InventoryTransferlog.Type WHEN 2 THEN InventoryTransferLog.ReferenceID ELSE NULL END AS TransactionNumber,

0 AS Price,

0 AS Cost

FROM [MS-SERVER\HQSERVER].HQM0851.dbo.InventoryTransferLog AS InventoryTransferLog

LEFT JOIN [MS-SERVER\HQSERVER].HQM0851.dbo.Item AS Item WITH(NOLOCK) ON InventoryTransferLog.ItemID = Item.ID

LEFT JOIN [MS-SERVER\HQSERVER].HQM0851.dbo.Department AS Department WITH(NOLOCK) ON Item.DepartmentID = Department.ID

LEFT JOIN [MS-SERVER\HQSERVER].HQM0851.dbo.Category AS Category ON Item.CategoryID = Category.ID

LEFT JOIN [MS-SERVER\HQSERVER].HQM0851.dbo.Store AS Store WITH(NOLOCK) ON InventoryTransferLog.StoreID = Store.ID

LEFT JOIN [MS-SERVER\HQSERVER].HQM0851.dbo.Serial AS Serial WITH(NOLOCK) ON InventoryTransferLog.DetailID = Serial.ID AND InventoryTransferLog.StoreID Serial.StoreID

LEFT JOIN [MS-SERVER\HQSERVER].HQM0851.dbo.PurchaseOrder AS PurchaseOrder WITH(NOLOCK) ON InventoryTransferLog.ReferenceID = PurchaseOrder.ID

AND (InventoryTransferLog.Type = 1 OR InventoryTransferLog.Type = 3)

AND InventoryTransferLog.StoreID = PurchaseOrder.StoreID

WHERE Item.Itemtype7 AND InventoryTransferLog.DateTransferred >=@Date

UNION ALL

/* VENTES & RETOURS */

SELECT

Store.Name as StoreName,

Store.ID as StoreID,

Item.ItemLookupCode AS Item,

Item.Description AS ItemDescription,

TransactionEntry.TransactionNumber AS ReferenceID,

TransactionEntry.ID AS ReferenceEntryID,

99 AS Type,

TransactionEntry.Quantity AS Quantity,

[Transaction].Time AS DateTransferred,

'' AS PONumber,

[Transaction].TransactionNumber AS TransactionNumber,

0 AS Price,

0 AS Cost

FROM [MS-SERVER\HQSERVER].HQM0851.dbo.TransactionEntry AS TransactionEntry

LEFT JOIN [MS-SERVER\HQSERVER].HQM0851.dbo.Item AS Item WITH(NOLOCK) ON TransactionEntry.ItemID = Item.ID

LEFT JOIN [MS-SERVER\HQSERVER].HQM0851.dbo.Department AS Department WITH(NOLOCK) ON Item.DepartmentID = Department.ID

LEFT JOIN [MS-SERVER\HQSERVER].HQM0851.dbo.Category AS Category ON Item.CategoryID = Category.ID

LEFT JOIN [MS-SERVER\HQSERVER].HQM0851.dbo.Store AS Store WITH(NOLOCK) ON TransactionEntry.StoreID = Store.ID

LEFT JOIN [MS-SERVER\HQSERVER].HQM0851.dbo.[Transaction] AS [Transaction] WITH(NOLOCK) ON TransactionEntry.TransactionNumber [Transaction].TransactionNumber AND [Transaction].StoreID = Store.ID

LEFT JOIN [MS-SERVER\HQSERVER].HQM0851.dbo.Serial AS Serial WITH(NOLOCK) ON TransactionEntry.DetailID = Serial.ID AND Serial.StoreID = Store.ID

WHERE Item.Itemtype7 AND [Transaction].Time >=@Date

UNION ALL

/* INVENTAIRE ACTUEL */

SELECT

Store.Name as StoreName,

Store.ID as StoreID,

Item.ItemLookupCode AS Item,

Item.Description AS ItemDescription,

'' AS ReferenceID,

'' AS ReferenceEntryID,

-1 AS Type,

ItemDynamic.Quantity AS Quantity,

'' AS DateTransferred,

'' AS PONumber,

'' AS TransactionNumber,

Item.Price AS Price,

Item.Cost AS Cost

FROM [MS-SERVER\HQSERVER].HQM0851.dbo.ItemDynamic AS ItemDynamic

LEFT JOIN [MS-SERVER\HQSERVER].HQM0851.dbo.Item AS Item WITH(NOLOCK) ON ItemDynamic.ItemID = Item.ID

LEFT JOIN [MS-SERVER\HQSERVER].HQM0851.dbo.Store AS Store WITH(NOLOCK) ON Store.ID = ItemDynamic.StoreID

WHERE Item.Itemtype7

) AS t1

GROUP BY t1.StoreID, t1.Item, t1.StoreName, t1.ItemDescription

HAVING SUM(t1.Quantity)0

ORDER BY t1.StoreName, t1.Item

------------------------------------------------------------------------------------------------------ Note that my server name and database name are hardcoded in this stored procedure.

Hope it can help.

Regards,

Julien

------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------

I'm not sure if this gonna work in HQ Admin.... I use it in MS SQL SERVER

2005 SP2 Management Studio...

Give it a try...

------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------

I re-read my QRY and no it's not gonna work in hqa.

To make it work you gonna have to remove every single "[MS-SERVER\HQSERVER].HQM0851.dbo"

You also have to replace the @Date with the date you want to know the stock level.

And also you have to discard this part : ALTER PROCEDURE [dbo].[sp_RptRetailInvASAT]

-- Add the parameters for the stored procedure here

@Date char(8)

AS SET NOCOUNT ON;"

If you do all these steps it should work.

Have fun.

JB

------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------