Stock Report with Batch Number

[Former Member]

Hi ,

My Client requires stock report with Batch numbers .required report format is

Item Code,BatchNumber,OpenQty,OpenVal,PurchaseQty,PurchaseVal,SalesQty,SalesVal,ClosQty,CloseVal

I tried using query on wiki for stock report but that query is written in 2007 and Batch tables and OINM table is changes in 8.81.

http://wiki.sdn.sap.com/wiki/display/B1/SAPB1SQLH-INItemInventoryOpeningandClosingStockper+warehouse

can any one help with stock report- batches.

Thanks,

Preeti

[Former Member · Accepted solution]

Hi,

What is your inventory valuation method? That will lead to different tables in the query.

Thanks,

Gordon

[Former Member · Accepted solution]

Hi Gordon,

Thanks for your reply.

Inventory valudation method is moving average.

Thanks,

Preeti

[Former Member · Accepted solution]

Have you tried the query in the wiki? Although OINM become a view, it should still be runnable.

[Former Member · Accepted solution]

HI Gordon,

I tried query on wiki but it is not fetching data for batch details.

I require it to link to batch table to fetch batch information for Item , require similar report as inventory audit report with batch number.

Thanks,

Preeti

[Former Member · Accepted solution]

That is because previous Batch table also become a view. I do not have that in our environment. Hope someone with the right database to come up a new query.

[Former Member · Accepted solution]

Can anyone help with stock query including batch details?

Thanks,

Preeti

[former_member206488 · Accepted solution]

HI,

use below query as basis and mofiy the fields required. it shows linking in batch and transaction tables:

SELECT distinct T0.ItemCode,T0.ItemName ,T0.DocDate ,T0.DocType ,T0.DocNum,T0.LocCode ,t4.DistNumber as [BatchNumber], T1.Quantity ,T5.CalcPrice as [Price]   
 
 FROM  [OITL] T0  
		INNER  JOIN [ITL1] T1  ON  T1.[LogEntry] = T0.[LogEntry]  
		INNER JOIN  OBTN T4 on T1.MdAbsEntry=T4.AbsEntry 
		INNER  JOIN [OITM] T2  ON  T2.[ItemCode] = T0.[ItemCode]    
		Inner JOin OINM T5 on T0.ItemCode =T5.ItemCode and T0.LocCode = T5.Warehouse  and T0.AppDocNum = T5.BASE_REF 

Thanks,

Neetu

[Former Member · Accepted solution]

Hello folks!

I'm still new with SAP inventory transaction tables and I'm trying to create a query "Stock Report with Batch Number and Inventory Value" but we are using different valuation methods in SAP B1 8.82. Some products are configured with FIFO, some with Standard and some others with average.

I re-used the query provided by Navneet and I tried to tweak it, but I'm facing an issue with the T1.Quantity (ITL1): when an item has been shipped with more than one batch, it reports the total quantity and total Price for all the batchs.

Eg: order with product A (total qty: 200) with Batch 1 (80) and 2 (120).

The query above reports

PRODUCT  BATCH           QTY      TransferPrice      Inv. Value

A                1                     200         1                       200

A                2                     200         1                       200

Results expected

PRODUCT  BATCH           QTY      TransferPrice      Inv. Value

A                1                     80          1                       80

A                2                     120        1                       120

Based on Navneet's query, I did some adjustements

* In the SELECT Statement (but it's a dirty workaround and I'm sure I'm missing something with the relation between OITL/ITL1 & OINM)

* In the WHERE conditions:

AND (T0.BaseLine = T5.BaseLine OR DocType = 67)

T0.StockEff = 1

SELECT

  T0.ItemCode,

  T0.ItemName,

  T0.DocDate,

  T0.DocType,

  T0.DocNum,

  T0.LocCode,

  t4.DistNumber as BatchNumber,

  T5.CalcPrice as 'Transfer Price',

  -- IF more than one batch for an item

  CASE WHEN (SELECT COUNT(DISTINCT(SysNumber)) FROM ITL1 WHERE LogEntry = T1.LogEntry AND ItemCode = T1.ItemCode) > 1 THEN T1.QUantity

  ELSE (T5.InQty-T5.OutQty) END

  AS Quantity,

  -- IF more than one batch for an item

  CASE WHEN (SELECT COUNT(DISTINCT(SysNumber)) FROM ITL1 WHERE LogEntry = T1.LogEntry AND ItemCode = T1.ItemCode) > 1 THEN (T5.CalcPrice * T1.QUantity)

  ELSE T5.transvalue END

  AS 'Inventory Value',

  CASE WHEN T5.CostMethod = 'F' THEN 'FIFO' WHEN T5.CostMethod = 'S' THEN 'Standard' WHEN T5.CostMethod = 'A' THEN 'Average' ELSE 'Unknown' END As CostMethod

FROM  OITL T0 -- Inventory Transactions Log

INNER JOIN ITL1 T1 ON T1.LogEntry = T0.LogEntry -- Serial & Batch Details in Transac

INNER JOIN OBTN T4 ON T1.MdAbsEntry = T4.AbsEntry -- Batch Numbers Master Data

INNER JOIN OINM T5 ON T0.ItemCode = T5.ItemCode AND T0.LocCode = T5.Warehouse AND T0.AppDocNum = T5.BASE_REF AND (T0.BaseLine = T5.BaseLine OR DocType = 67) -- Wharehouse Journal

WHERE AND T0.StockEff = 1

ORDER BY T0.DocDate, t4.DistNumber

Any idea what would be the correct query to get "Stock Report with Batch Number and Inventory Value"?

Thank you for your help!

[kothandaraman_nagarajan · Accepted solution]

Hi,

Please create new discussion to get quick response.

Thanks