Physical Inventory


R

randy

We are about to run physical inventory. How do we freeze the inventory in RMS
2.0?
 
Ad

Advertisements

C

convoluted

Hi Randy - creating the Physical Inventory file "freezes" the inventory - ie,
it takes a picture or "snapshot" of your current qties on hand....not sure if
this answers your question or not - repost if it doesn't-
 
J

Julien Bonnier

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.Itemtype<>7 AND InventoryTransferLog.DateTransferred >[email protected]

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.Itemtype<>7 AND [Transaction].Time >[email protected]


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.Itemtype<>7

) 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
 
A

Amy

how does this work? Do I copy it into HQ Admin new query window?

Julien Bonnier said:
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.Itemtype<>7 AND InventoryTransferLog.DateTransferred >[email protected]

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.Itemtype<>7 AND [Transaction].Time >[email protected]


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.Itemtype<>7

) 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


convoluted said:
Hi Randy - creating the Physical Inventory file "freezes" the inventory -
ie,
it takes a picture or "snapshot" of your current qties on hand....not sure
if
this answers your question or not - repost if it doesn't-
 
J

Julien Bonnier

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...


Amy said:
how does this work? Do I copy it into HQ Admin new query window?

Julien Bonnier said:
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.Itemtype<>7 AND InventoryTransferLog.DateTransferred >[email protected]

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.Itemtype<>7 AND [Transaction].Time >[email protected]


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.Itemtype<>7

) 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


convoluted said:
Hi Randy - creating the Physical Inventory file "freezes" the
inventory -
ie,
it takes a picture or "snapshot" of your current qties on hand....not
sure
if
this answers your question or not - repost if it doesn't-

:

We are about to run physical inventory. How do we freeze the inventory
in
RMS
2.0?
 
Ad

Advertisements

J

Julien Bonnier

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

Julien Bonnier said:
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...


Amy said:
how does this work? Do I copy it into HQ Admin new query window?

Julien Bonnier said:
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.Itemtype<>7 AND InventoryTransferLog.DateTransferred >[email protected]

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.Itemtype<>7 AND [Transaction].Time >[email protected]


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.Itemtype<>7

) 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


Hi Randy - creating the Physical Inventory file "freezes" the
inventory -
ie,
it takes a picture or "snapshot" of your current qties on hand....not
sure
if
this answers your question or not - repost if it doesn't-

:

We are about to run physical inventory. How do we freeze the
inventory in
RMS
2.0?
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

physical inventory 2
Physical Inventory 1
Physical Inventory 0
Physical Inventory 1
Physical Inventory 1
Physical Inventory 1
physical inventory 2
physical inventory 6

Top