Stock by Warehouse-Non-Batch Item

[Former Member]

Hi All,

My Client Both Batch & Non-batch items,

I'm trying to create stock report for items warehouse-wise w/o Batch Management

SELECT T5.FirmName as "Firm_Name", T0.ItemName as "Item_Name", T4.[ItmsGrpNam] as "Group_Name", T0.[U_EA_Packgroup] as "Pack_Group", T3.[BLength1] as "Length", T3.[BWidth1] as "Width", T3.[BHeight1] as "Height", 

(sum(isnull(t6.inqty,0)) - sum(isnull(t6.outqty,0)) ) as "Avl_In_Wh_Qty", SUM(T1.Quantity) as "Avl_In_Wh_Sqm"

FROM OBTN AS T0  

LEFT OUTER JOIN dbo.OBTQ AS T1 ON T0.ItemCode = T1.ItemCode AND T0.SysNumber = T1.SysNumber  

INNER JOIN dbo.OBTW AS T2 ON T0.ItemCode = T2.ItemCode AND

T0.SysNumber = T2.SysNumber AND T1.WhsCode = T2.WhsCode

INNER JOIN OITM T3 ON T1.[ItemCode] = T3.[ItemCode] INNER JOIN OITB T4 ON T3.[ItmsGrpCod] = T4.[ItmsGrpCod]

INNER JOIN OMRC T5 ON T3.[FirmCode] = T5.[FirmCode]

INNER JOIN OINM T6 ON T3.[ItemCode] = T6.[ItemCode]

where T3.[ManBtchNum] = 'N' and T1.Quantity <>0 and T2.WhsCode <> 'Port' and T2.WhsCode <> 'HIGH SEA' 

group by T5.FirmName, T0.ItemName, T4.[ItmsGrpNam], T0.[U_EA_Packgroup], T3.[BLength1], T3.[BWidth1], T3.[BHeight1]

I tried above query, query execution is successful but no result. Kindly help.

Thanks in advance!

-Hari

[kothandaraman_nagarajan · Accepted solution]

Hi,

For non-batch items, why do you need OBTN, OBTW?

You can use OITM table to get non-batch items in required warehouses.

Thanks & Regards,

Nagarajan

[Former Member · Accepted solution]

Your write, I failed to notice. Its solved.

Thank you!

[kothandaraman_nagarajan · Accepted solution]

Please close this thread if you got answer.