on 09-25-2017 1:40 PM
Hello,
I'm trying to achieve the following for SAP B1: get a stock report in different bin locations for a date in the past. I already found 2 queries for either of these problems:
1. SELECT T1.WhsName, T0.ItemCode, Max(T0.Dscription) 'Item Name', SUM(T0.InQty-T0.OutQty) 'On Hand'
FROM OINM T0
JOIN OWHS T1 ON T1.WhsCode=T0.Warehouse
WHERE T0.DocDate <= [%0]
GROUP BY T1.WhsName,T0.ItemCode
Having SUM(T0.InQty-T0.OutQty) > 0
Which gives me the stock on a date in the past.
2. select t0.ItemCode, t1.ItemName, isnull(t3.OnHandQty,0) as "On Hand", t2.BinCode, t2.SL1Code, t2.SL2Code, t0.WhsCode
from OITW T0
inner join OITM t1 on t0.ItemCode = t1.ItemCode
inner join OIBQ t3 on t0.ItemCode=t3.ItemCode and t0.WhsCode = t3.WhsCode
inner join OBIN T2 on t2.AbsEntry = t3.BinAbs
order by t2.SL1Code, t2.SL2Code, t0.ItemCode
Which gives me the stock on a particular bin location
Somehow these 2 queries should be combined (i think) to make my report. However, lacking some knowledge of SQL I'm having trouble doing this.
Thanks
Hello.
Try this Query, Fisrt part por batch Items, second par for not batch Items.
SELECT T2.ItemCode
, T6.BinCode AS "Ubicación"
, T5.DistNumber AS "Lote"
,SUM(IIF(T2.ActionType IN('1','19'), T4.Quantity,IIF(T2.ActionType IN('2','20'),(T4.Quantity *-1), 0)))AS "Cantidad 2"
FROM OILM T2
INNER JOIN ILM1 T3 ON (T2.[MessageID] = T3.[MessageID])-- Tabla Numero de Serie y Lotes de Log Inventario
INNER JOIN OBTL T4 -- Tabla Log de Ubicaciones
ON ( T2.MessageID = T4.MessageID
AND T3.[MdAbsEntry] = T4.[SnBMDAbs]
)
INNER JOIN OBTN T5 ON (T3.[MdAbsEntry] = T5.[AbsEntry])-- Tabla Lotes
INNER JOIN OBIN T6 ON (T4.BinAbs = T6.AbsEntry)-- Tabla Ubicaciones
INNER JOIN OITM T7 ON (T2.ItemCode = T7.ItemCode)-- Tabla Articulos
WHERE (1=1)
AND(T2.DocDate <='[%1]')
AND(T7.ManBtchNum ='Y')
GROUP BY
T2.[ItemCode]
, T5.[DistNumber]
, T6.[BinCode]
HAVING (SUM(IIF(T2.ActionType IN('1','19'), T4.Quantity,IIF(T2.ActionType IN('2','20'),(T4.Quantity *-1), 0)))<> 0)
UNION ALL
SELECT T2.ItemCode
, T6.BinCode AS "Ubicación"
,NULL AS "Lote"
,SUM(IIF(T2.ActionType IN('1','19'), T4.Quantity,IIF(T2.ActionType IN('2','20'),(T4.Quantity *-1), 0)))AS "Cantidad 2"
FROM OILM T2
INNER JOIN OBTL T4 -- Tabla Log de Ubicaciones
ON ( T2.MessageID = T4.MessageID
)
INNER JOIN OBIN T6 ON (T4.BinAbs = T6.AbsEntry)-- Tabla Ubicaciones
INNER JOIN OITM T7 ON (T2.ItemCode = T7.ItemCode)-- Tabla Articulos
WHERE (1 = 1)
AND(T2.DocDate <='[%1]')
AND(T7.ManBtchNum ='N')
GROUP BY
T2.[ItemCode]
, T6.[BinCode]
HAVING (SUM(IIF(T2.ActionType IN('1','19'), T4.Quantity,IIF(T2.ActionType IN('2','20'),(T4.Quantity *-1), 0)))<> 0)
ORDERBY 1,2,3
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Thanks, you suggestion was invaluable
User | Count |
---|---|
91 | |
8 | |
7 | |
4 | |
4 | |
3 | |
3 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.