on 12-28-2011 1:34 AM
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
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.