cancel
Showing results for 
Search instead for 
Did you mean: 

Instock with Batch Numbers Query/Report

Former Member
0 Kudos

Hello Experts,

Can anyone help me with a query to generate Instock quantity with respective batch numbers for an Item. The item uses Moving Average costing method.

For Example: Say i have an Item A0001 with Qty 10 instock ( 5 with 4343 batch#'s, 5 with 6648). I need to generate a report as shown below:

Item Qty Batch

A0001 5 4343

A0001 5 6648

Accepted Solutions (0)

Answers (2)

Answers (2)

Former Member
0 Kudos

Dear parneeth,

As requested please try this Query to fulfill your requirement.

SELECT T0.docnum,

T2.BatchNum,T2.ItemCode,T2.Quantity,

T2.ItemName,T0.CardName as Supplier,

T2.SuppSerial,T2.IntrSerial,T2.ExpDate,T2.PrdDate,T2.InDate,T2.Located,

convert(varchar(8000),T2.Notes)

FROM OPDN T0 INNER JOIN PDN1 T1 ON T0.DocEntry=T1.DocEntry

inner join

OIBT T2 on T1.ItemCode = t2.ItemCode and T0.objtype=T2.basetype and T2.baseentry=T1.Docentry

inner join

oitm T5 on T1.Itemcode=T5.Itemcode

WHERE

T2.quantity>0

group by T0.docnum,T2.BatchNum,T2.ItemCode,T2.Quantity,

T2.ItemName,T0.CardName,

T2.SuppSerial,T2.IntrSerial,T2.ExpDate,T2.PrdDate,T2.InDate,T2.Located,

convert(varchar(8000),T2.Notes),T1.Linetotal,T1.Quantity

REGARDS

MANGESH PADHARE.

Former Member
0 Kudos

Hi,

Just use standard report of B1.

Go to Inventory >>> Inventory Reports >>> Batch Number Transaction Report. Input Criteria then execute report, this will show batch information of every item. There is a option "Display Batches with Zero Qty" to show/hide Batch info. of Zero Qty.

Hope this helps,

TVSon