Skip to Content
0

inventory audit with bin

Dec 31, 2016 at 12:14 AM

160

avatar image

Dear All Master,

Any master give me a hand to make a custom report as follow format ?

| Item code | Binloc | whs| Qty | Unit price | Cost |

thanks b4.

Best regards,

harry

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

3 Answers

Best Answer
DIEGO LOTHER Jan 03, 2017 at 10:23 AM
0

Hi Harry,

Check if this query helps you(if you need more information tell me and I will help you):

SELECT 
	T0.ItemCode, 
	T5.ItemName, 
	T0.WhsCode, 
	T4.DistNumber,
	SUM(T3.OnHandQty) as 'Batch Quantity',
	SUM(T2.OnHandQty) as 'Total Stock Bin',
	SUM(T0.OnHand) AS 'Total Stock Warehouse',
	T1.BinCode
FROM 
	OITW T0
	INNER JOIN OBIN T1 ON T0.WhsCode = T1.WhsCode
	INNER JOIN OIBQ T2 ON T2.WhsCode = T0.WhsCode AND T1.AbsEntry = T2.BinAbs AND T0.ItemCode = T2.ItemCode
	INNER JOIN OBBQ T3 ON T3.ItemCode = T0.ItemCode AND T3.BinAbs = T1.AbsEntry AND T3.WhsCode = T2.WhsCode
	INNER JOIN OBTN T4 ON T4.AbsEntry = T3.SnBMDAbs
	INNER JOIN OITM T5 ON T5.ItemCode = T0.ItemCode
WHERE 
	T2.OnHandQty > 0 
	AND T1.BinCode between [%0] and [%1] 
	AND T3.OnHandQty > 0
	AND T2.OnHandQty > 0
GROUP BY 
	T0.ItemCode, T5.ItemName, T0.WhsCode, 
	T0.WhsCode, T1.BinCode, T4.DistNumber
ORDER BY T0.ItemCode, T0.WhsCode, T1.BinCode

Source: https://answers.sap.com/questions/84872/index.html

Kind Regards,

Diego Lother

Show 5 Share
10 |10000 characters needed characters left characters exceeded

Dear Diego Lother,

Thanks for your answer.

God Bless u

Thanks lot.

Best regards,

harry

0

Dear Mr. Dieqo lother,

how i can get the unit price n total cost ? wich from table n colum i take for that ?

Thanks lot.

Best regards,

harry

0

Hi Harry,

Try this:

SELECT 
	T0.ItemCode, 
	T5.ItemName, 
	T0.WhsCode, 
	T4.DistNumber,
	SUM(T3.OnHandQty) as 'Batch Quantity',
	SUM(T2.OnHandQty) as 'Total Stock Bin',
	SUM(T0.OnHand) AS 'Total Stock Warehouse',
	T1.BinCode,
	MAX(T0.AvgPrice) AS 'Unit Cost',
	MAX(T0.AvgPrice) * SUM(T2.OnHandQty) AS 'Total Cost by Bin',
	MAX(T0.StockValue) AS 'Total Cost by Warehouse'
FROM 
	OITW T0
	INNER JOIN OBIN T1 ON T0.WhsCode = T1.WhsCode
	INNER JOIN OIBQ T2 ON T2.WhsCode = T0.WhsCode AND T1.AbsEntry = T2.BinAbs AND T0.ItemCode = T2.ItemCode
	INNER JOIN OBBQ T3 ON T3.ItemCode = T0.ItemCode AND T3.BinAbs = T1.AbsEntry AND T3.WhsCode = T2.WhsCode
	INNER JOIN OBTN T4 ON T4.AbsEntry = T3.SnBMDAbs
	INNER JOIN OITM T5 ON T5.ItemCode = T0.ItemCode
WHERE 
	T2.OnHandQty > 0 
	AND T1.BinCode between [%0] and [%1] 
	AND T3.OnHandQty > 0
	AND T2.OnHandQty > 0
GROUP BY 
	T0.ItemCode, T5.ItemName, T0.WhsCode, 
	T0.WhsCode, T1.BinCode, T4.DistNumber
ORDER BY T0.ItemCode, T0.WhsCode, T1.BinCode

Use the field AvgPrice from OITW to know the average price of your item in the warehouse.

Use the field StockValue from OITW to know the total cost of item in the warehouse.

Hope it helps.

Kind Regards,

Diego Lother

1

Hi Mr. Diego Lother,

Thanks for your respond and solved.

Best regards,

harry

0

Hi Harry,

Great.

Don't forget to click on the accept button below my answer.

Kind Regards,

Diego Lother

0
Nagarajan K Dec 31, 2016 at 04:40 AM
0

Hi,

Please check this thread to write own query,

https://archive.sap.com/discussions/thread/3932035

Regards,

Nagarajan

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Dear Nagarajan,

i have cheked , but the value of "DftBinAbs" is NULL, so could display the Query

any idea for it please?

Thx.

Best regards,

Harry.P

0
Harry Pramana Jan 02, 2017 at 11:55 PM
0

Dear Nagarajan K,

I didnt find yet for this format, any other ideas master ?

Thanks

Best regards,

Share
10 |10000 characters needed characters left characters exceeded