Skip to Content
0

Inactive Items query with batch number

Jan 11, 2017 at 12:07 PM

149

avatar image

Hi All,

My client wanted to make Inactive items query base on inventory with batch number, batch expiration and batch admission date. The existing Inactive items is not giving us our requirements.

Can any body help me in this regards

Thanks

Dect Lariosa

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

3 Answers

Edy Simon Jan 12, 2017 at 12:54 AM
0

Hi Dect,

Just join the OITM to OBTN table

Regards
Edy

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

Hi Edy,

How will i see the non moving items? lets say for 6 months

0

Hi Dect,

I see, i thought your inactive means inactive in the OITM table.
If your inactive means no movement for the past 6 months, you can filter out the OITM using OINM view.

Something like

WITH ActiveItem as (
SELECT DISTINCT ItemCode
FROM OINM T1 WHERE T1.DocDate >= DATEADD(month, -6, GETDATE()))
SELECT T0.ItemCode, T1.DistNumber, T1.Quantity
FROM OITM T0 JOIN OBTN T1 oN T0.ItemCode = T1.ItemCode
	LEFT JOIN ActiveItem T2 ON T0.ItemCode = T2.ItemCode 
WHERE T0.OnHand>0					--Items with onhand qty
	AND T2.ItemCode IS NULL				--Not Active item for the past 6 months
	--AND T2.ItemCode IS NOT NULL		--Active Item Only for the past 6 months

Regards

Edy

0

Hi Edy,

This what i see after running the query. Sorry im very new in SAP

and the end of query

In Inactive reports that is already in SAP. What is the source table that the report is getting?

sap.jpg (19.7 kB)
sap2.jpg (18.1 kB)
0
Edy Simon Jan 16, 2017 at 12:47 AM
0

Hi Dect,

I got the wrong quantity for the batch.
Try this instead :

WITH ActiveItem as (
SELECT DISTINCT ItemCode
FROM OINM T1 WHERE T1.DocDate >= DATEADD(month, -6, GETDATE()))
SELECT T0.ItemCode, T1.DistNumber, T3.Quantity
FROM OITM T0 JOIN OBTN T1 oN T0.ItemCode = T1.ItemCode
	JOIN OBTQ T3 ON T1.AbsEntry = T3.AbsEntry
	LEFT JOIN ActiveItem T2 ON T0.ItemCode = T2.ItemCode 
WHERE T0.OnHand>0 AND T3.Quantity>0		--Items with onhand qty
	AND T2.ItemCode IS NULL				--Not Active item for the past 6 months
	--AND T2.ItemCode IS NOT NULL		--Active Item Only for the past 6 months


As for the SAP standard Inactive Report, They query all item from OITM table, minus out the result from execution of a standard SP TmSp_FetchActiveItem.

Regards
Edy


capture.jpg (46.2 kB)
Show 10 Share
10 |10000 characters needed characters left characters exceeded

Hi,

Thanks for the great result. I was trying to put the Admission date and the expiry date on the query but its not coming out. Can you include that on your code?

Thanks again in advance

0

Hi,

also if it is ok with to you. Please add the warehouse code on which the inactive items are being stored.

Thanks alot my friend. On your code itself i learned alot.

0

Hi Dect,

Can you show me what you have tried and not coming out ?
Regards
Edy

0

Hi Edy,

I added some code like T1.ExpDate etc..

WITH ActiveItem as (
SELECT DISTINCT ItemCode
FROM OINM T1 WHERE T1.DocDate >= DATEADD(month, -6, GETDATE()))
SELECT T0.ItemCode, T1.DistNumber AS 'Batch Number',T1.Indate AS 'Admission Date',T1.ExpDate AS 'Expiry Date', T3.Quantity
FROM OITM T0 JOIN OBTN T1 oN T0.ItemCode = T1.ItemCode
	JOIN OBTQ T3 ON T1.AbsEntry = T3.AbsEntry
	LEFT JOIN ActiveItem T2 ON T0.ItemCode = T2.ItemCode 
WHERE T0.OnHand>0 AND T3.Quantity>0		--Items with onhand qty
	AND T2.ItemCode IS NULL				--Not Active item for the past 6 months
	--AND T2.ItemCode IS NOT NULL		--Active Item Only for the past 6 months

0

Which also filtering 2014 admission date and 2015

and end of the report

sasa11.jpg (22.7 kB)
sasa22.jpg (21.4 kB)
0

Hi Dect,
That should be correct. Is it not working for you ?


Regards
Edy

0

Hi Edy,

I just wonder why it showing 2014 and 2015 admission date?

0

Hi Dect,

That should not be the problem of the query.
You will need to investigate it your self. Maybe the user entered the wrong year ?

Regards
Edy

0
Show more comments
Edy Simon Jan 17, 2017 at 06:42 AM
0

Hi Dect,

Can you elaborate what is the difference, why your user said the SAP inactive report is more trusted ?
If you feel the admission date is wrong, how can you tell? the SAP standard report does not show you batch admission date.

Regards
Edy

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

Hi Edy,

Because in inactive report more items are showing. on the report that you made only few items are showing. Although your report is very well made. In technical aspect he cannot understand why i get the report in inventory rather that in sales. He said also the only missing ingredient in Inactive report is the batch numbers other than that Inactive report can be use.

Can you modify the query just like in Inactive reports but with batch number. Or any possibility to modify the report to include the batch number?

0

Hi Dect,

All the tables and joining of the tables are there.
You just need to filter out based on your requirement, current query is filtered by 6 month inactive in the inventory movement, ie includes no inventory transfer, no purchase and no sales.
Filter them out accordingly.

As for the standard SAP report, i am not sure what their logic is. Sorry no help there.

Regards
Edy

0

Hi Edy,

Thanks for the help, atleast you gave me an idea on how i will manage to have this report.

THanks

0