Sap Business One - Committed stock report

[former_member666435]

Hi all,

Can someone give me a query for the requirement below:

"Committed stock report

This report asks the user to enter an item code and it reports back on all the open sales orders it is on sorted by date. this report is to help a customer to see what the committed stock levels are against an item and who they are committed to.

"Best Regards,Daniel

[silambarasan_RS]

Hi,

Did you check this report.

Inventory in Warehouse Report

Whenever an item is purchased, sold, produced, or used in production, you specify the issuing or receiving warehouse in the corresponding document. The system proposes the default warehouse automatically. You can change this if necessary. You can also define inventory transfers between warehouses in the system.

To ensure accurate inventory management by warehouse, it is essential to record the correct warehouse in every single document. If no warehouse is specified, SAP Business One proposes the defined default warehouse.

This report lists the current inventories by warehouse based on the information in these documents. You can then implement measures such as inventory transfers, for example, to supplement the inventory of a warehouse.

The information you specify here is displayed automatically the next time you call the report.

To open the window, choose Inventory Inventory Reports Inventory in Warehouse Report.

Thanks,

silambu

[former_member666435]

Hi Silambu,

Thanks for explanation, but i really need is a sql query to build a report would you be able to help on that?

Best Regards,

Daniel

[former_member666435]

I did this query can someone tell me if it matches the requirements as per my first post?

select  
t0.DocNum as "SO Number"
,t0.CardCode as "Customer Code"
,t0.CardName as "Customer Name"
,cast(t0.DocDate as date) as "Posting Date"
,t1.ItemCode as "Item Code"
,t1.Dscription as "Item Name"
,t1.OpenQty as "Commited"

from ORDR t0
left join RDR1 t1 on t1.DocEntry = t0.DocEntry

where DocStatus  = 'O'
and t1.ItemCode = [%0]


group by
t0.CardCode
,t0.CardName
,t0.DocNum 
,cast(t0.DocDate as date) 
,t1.ItemCode
,t1.Dscription 
,t1.OpenQty 

order by 
cast(t0.DocDate as date)

Best Regards,

Daniel

[mgregur · Accepted solution]

Hi Daniel,

your query:

select  
t0.DocNum as "SO Number"
,t0.CardCode as "Customer Code"
,t0.CardName as "Customer Name"
,cast(t0.DocDate as date) as "Posting Date"
,t1.ItemCode as "Item Code"
,t1.Dscription as "Item Name"
,t1.OpenQty as "Commited"

from ORDR t0
left join RDR1 t1 on t1.DocEntry = t0.DocEntry

where DocStatus  = 'O'
and t1.ItemCode = [%0]


group by
t0.CardCode
,t0.CardName
,t0.DocNum 
,cast(t0.DocDate as date) 
,t1.ItemCode
,t1.Dscription 
,t1.OpenQty 

order by 
cast(t0.DocDate as date)

is mostly fine, but I would also include t1.LineStatus in your WHERE clause, as a document can be opened but line closed (e.g. partial delivery was made). Also, I don't know why you are using cast on t0.DocDate, it should be shown as date in Query Manager.

BR,

Matija

[former_member666435 · Accepted solution]

Thanks for the suggestion Matija, i added it to my query.

Best Regards,

Daniel