Dear all
I want to create Inventory available by batch and amount in aduit report query select by posting date.
(SAP B1)
Please advise me
Need Columns :
#First : I try with code below but some case DocLine are same when join it will be duplicate data
SELECT *
FROM OITL T0
INNER JOIN ITL1 T1 ON T1.LogEntry = T0.LogEntry
INNER JOIN OBTN T4 ON T1.MdAbsEntry= T4.AbsEntry
LEFT JOIN (
SELECT DISTINCT ItemCode,Warehouse,BASE_REF,DocLineNum,CalcPrice,
CASE WHEN InQty = 0 THEN -(OutQty) ELSE InQty END AS 'Quantity'
FROM OINM) T5 ON T0.ItemCode = T5.ItemCode AND T0.LocCode = T5.Warehouse AND T0.AppDocNum = T5.BASE_REF AND T0.DocLine = T5.DocLineNum
WHERE T0.Itemcode LIKE [ItemCode] AND T0.LocCode LIKE [WhCode]
AND T1.Quantity <> 0 AND T0.DocDate <= EOMONTH(DATEADD(MONTH,-1,GETDATE()))
#Second : I add Quantity condition to join but some case data not match because OINM split data
SELECT * FROM OITL T0
INNER JOIN ITL1 T1 ON T1.LogEntry = T0.LogEntry
INNER JOIN OBTN T4 ON T1.MdAbsEntry= T4.AbsEntry
LEFT JOIN (
SELECT DISTINCT ItemCode,Warehouse,BASE_REF,DocLineNum,CalcPrice,
CASE WHEN InQty = 0 THEN -(OutQty) ELSE InQty END AS 'Quantity'
FROM OINM) T5 ON T0.ItemCode = T5.ItemCode AND T0.LocCode = T5.Warehouse AND
T0.AppDocNum = T5.BASE_REF AND T0.DocLine = T5.DocLineNum AND T1.Quantity = T5.Quantity
WHERE T0.Itemcode LIKE [ItemCode] AND T0.LocCode LIKE [WhCode]
AND T1.Quantity <> 0 AND T0.DocDate <= EOMONTH(DATEADD(MONTH,-1,GETDATE()))
#Third : I add group by to OINM for some case that OINM split data but I finded some case ITL1 split but OINM
combine dataSELECT *,
CASE WHEN T5.Quantity is null THEN T6.Quantity_6ELSE T5.Quantity END AS 'TotalQuantity'
FROM OITL T0
INNER JOIN ITL1 T1 ON T1.LogEntry = T0.LogEntry
INNER JOIN OBTN T4 ON T1.MdAbsEntry= T4.AbsEntry
LEFT JOIN (
SELECT DISTINCT ItemCode,Warehouse,BASE_REF,DocLineNum,CalcPrice,
CASE WHEN InQty = 0 THEN -(OutQty) ELSE InQty END AS 'Quantity'
FROM OINM) T5 ON T0.ItemCode = T5.ItemCode AND T0.LocCode = T5.Warehouse AND T0.AppDocNum = T5.BASE_REF AND T0.DocLine = T5.DocLineNum AND
T1.Quantity = T5.Quantity
LEFT JOIN (
SELECT DISTINCT ItemCode AS 'ItemCode_6',Warehouse AS 'Warehouse_6',
BASE_REF AS 'BASE_REF_6' ,DocLineNum AS 'DocLineNum_6',Avg(CalcPrice) AS 'CalcPrice_6',
Sum(CASE WHEN InQty = 0 THEN -(OutQty) ELSE InQty END) AS 'Quantity_6'
FROM OINM
GROUP BY ItemCode,Warehouse,BASE_REF,DocLineNum) T6 ON T0.ItemCode = T6.ItemCode_6
AND T0.LocCode = T6.Warehouse_6 AND T0.AppDocNum = T6.BASE_REF_6 AND T0.DocLine = T6.DocLineNum_6 AND T1.Quantity = T6.Quantity_6
WHERE T0.Itemcode LIKE [ItemCode] AND T0.LocCode LIKE [WhCode]
AND T1.Quantity <> 0AND T0.DocDate <= EOMONTH(DATEADD(MONTH,-1,GETDATE()))