Inventory on hand quantities accross two databases

[former_member476276]

I have two companies but we some of the same inventory.  I would like to be able to view the stock on hand in the Inventory Master Data from the other database.  What would be the best method for doing this?

[kothandaraman_nagarajan]

Hi,

Only possible by SQL query.

Try this query. Add your database name.

SELECT 'Corporate-'+ '' +T3.[WhsName] AS
'Loc/Whse',
T2.[ItmsGrpNam] AS 'Item Group',
T0.[ItemCode] AS
'Item',
T1.[ItemName] AS 'Item Description',
T0.[OnHand] AS 'On
Hand',
T0.[OnOrder] AS 'On Order'
FROM SBODemoUS.dbo.OITW T0

INNER JOIN SBODemoUS.dbo.OITM T1 ON T0.ItemCode =
T1.ItemCode
INNER JOIN SBODemoUS.dbo.OITB T2 ON T1.ItmsGrpCod =
T2.ItmsGrpCod
INNER JOIN SBODemoUS.dbo.OWHS T3 ON T0.WhsCode =
T3.WhsCode
WHERE T0.[ItemCode] = [%0] AND T0.[OnHand] > 0
UNION
ALL
SELECT 'Dallas-'+ '' +T3.[WhsName] AS
'Loc/Whse',
T2.[ItmsGrpNam] AS 'Item Group',
T0.[ItemCode] AS
'Item',
T1.[ItemName] AS 'Item Description',
T0.[OnHand] AS 'On
Hand',
T0.[OnOrder] AS 'On Order'
FROM SBODemo_Dallas.dbo.OITW
T0
INNER JOIN SBODemo_Dallas.dbo.OITM T1 ON T0.ItemCode = T1.ItemCode
INNER JOIN SBODemo_Dallas.dbo.OITB T2 ON T1.ItmsGrpCod
= T2.ItmsGrpCod
INNER JOIN SBODemo_Dallas.dbo.OWHS T3 ON
T0.WhsCode = T3.WhsCode
WHERE T0.[ItemCode] = [%0] AND T0.[OnHand]
> 0
UNION ALL
SELECT 'Irvine-'+ '' +T3.[WhsName] AS
'Loc/Whse',
T2.[ItmsGrpNam] AS 'Item Group',
T0.[ItemCode] AS
'Item',
T1.[ItemName] AS 'Item Description',
T0.[OnHand] AS 'On
Hand',
T0.[OnOrder] AS 'On Order'
FROM SBODemoIrvine.dbo.OITW
T0
INNER JOIN SBODemoIrvine.dbo.OITM T1 ON T0.ItemCode =
T1.ItemCode
INNER JOIN SBODemoIrvine.dbo.OITB T2 ON T1.ItmsGrpCod
= T2.ItmsGrpCod
INNER JOIN SBODemo_Dallas.dbo.OWHS T3 ON
T0.WhsCode = T3.WhsCode
WHERE T0.[ItemCode] = [%0] AND T0.[OnHand]
> 0

Thanks & Regards,

Nagarajan

[former_member186095]

Hi,

Currently, there is no inventory report option or feature that can be used to show the stock for two database.

You can use query SQL to do that or you can try to develop a report using crystal report.

Use budget comparison report as a reference.

Rgds,

Jimmy