SAP B1 query inventory

[former_member485103]

I need a query for inventory with batch number, current quantity on hand and original receipt date. I can't find a table with batch numbers.

[former_member491159]

SELECT
	T0.[ItemCode]
   ,T1.[ItemName]
   ,T2.[DistNumber]
   ,T3.[BinCode]
   ,T0.[OnHandQty]
,T1.[SalUnitMsr]
   ,T0.[WhsCode]
   ,T4.[WhsName]
FROM OBBQ T0
INNER JOIN OITM T1
	ON T0.ItemCode = T1.ItemCode
INNER JOIN OBTN T2
	ON T0.SnBMDAbs = T2.AbsEntry
INNER JOIN OBIN T3
	ON T0.BinAbs = T3.AbsEntry
INNER JOIN OWHS T4
	ON T0.WhsCode = T4.WhsCode
	where T0.[OnHandQty] <> '0' 
	order by T0.[ItemCode]

[former_member620963]

Hi kathy.walker2, please check these tables:

• OIBT - Batch No. for Item
• IBT1 - Batch Number Transactions
  
  and try this query

• SELECT B2.*,
         B3."Quantity"
  FROM   (SELECT B0."SysNumber",
                 B0."ItemCode",
                 B0."BatchNum",
                 B0."WhsCode",
                 B1."BaseType"     AS "BaseType",
                 MIN(B1."DocDate") AS "InDate"
          FROM   "OIBT" B0
                 INNER JOIN "IBT1" B1
                         ON B0."ItemCode" = B1."ItemCode"
                            AND B0."BatchNum" = B1."BatchNum"
                            AND B0."WhsCode" = B1."WhsCode"
          WHERE  B1."BaseType" IN( 18, 67 )
                 AND B1."Direction" = 0
          GROUP  BY B0."SysNumber",
                    B0."ItemCode",
                    B0."BatchNum",
                    B0."WhsCode",
                    B1."BaseType",
                    B1."DocDate"
          ORDER  BY B1."DocDate") B2
         LEFT JOIN (SELECT B0."SysNumber",
                           B0."ItemCode",
                           B0."BatchNum",
                           B0."WhsCode",
                           SUM(CASE WHEN B1."Direction" = 0 THEN B1."Quantity" ELSE( -1 ) * B1."Quantity" END) AS "Quantity"
                    FROM   "OIBT" B0
                           INNER JOIN "IBT1" B1
                                   ON B0."ItemCode" = B1."ItemCode"
                                      AND B0."BatchNum" = B1."BatchNum"
                                      AND B0."WhsCode" = B1."WhsCode"
                    GROUP  BY B0."SysNumber",
                              B0."ItemCode",
                              B0."BatchNum",
                              B0."WhsCode") B3
                ON B2."SysNumber" = B3."SysNumber"
                   AND B2."ItemCode" = B3."ItemCode"
                   AND B2."BatchNum" = B3."BatchNum"
                   AND B2."WhsCode" = B3."WhsCode"
  ORDER  BY B2."InDate" 
  

[ernu]

can you describe your needs more clearly. like what parameters do you need then what information do you want to appear?