Skip to Content
0
Dec 27, 2022 at 10:16 AM

How to create Inventory available by batch and amount in aduit report query select by posting date

126 Views

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 :

  • DocDate (Posting Date)
  • ItemCode
  • DistNumbe (Batch)
  • Warehouse
  • Quantity (Available Quantity at Select Posting Data)
  • Quantity*CalPrice Or TransValue

#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 data
SELECT *,
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()))