cancel
Showing results for 
Search instead for 
Did you mean: 

Inactive Items query with batch number

former_member351438
Participant
0 Kudos

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

Accepted Solutions (0)

Answers (3)

Answers (3)

edy_simon
Active Contributor
0 Kudos

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

former_member351438
Participant
0 Kudos

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?

edy_simon
Active Contributor
0 Kudos

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

former_member351438
Participant
0 Kudos

Hi Edy,

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

THanks

edy_simon
Active Contributor
0 Kudos

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

former_member351438
Participant
0 Kudos

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

former_member351438
Participant
0 Kudos

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.

edy_simon
Active Contributor
0 Kudos

Hi Dect,

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

former_member351438
Participant
0 Kudos

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

former_member351438
Participant
0 Kudos

Which also filtering 2014 admission date and 2015

and end of the report

edy_simon
Active Contributor
0 Kudos

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


Regards
Edy

former_member351438
Participant
0 Kudos

Hi Edy,

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

edy_simon
Active Contributor
0 Kudos

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

former_member351438
Participant
0 Kudos

Hi Edy,

I checked the query with the user. And we checked both reports, it seems for him the most trusted report is the SAP inactive report. But the problem is, that report is not showing the batch numbers. is there any possible way to make a query the same as the SAP inactive report?

former_member351438
Participant
0 Kudos

But with Batch Number

edy_simon
Active Contributor
0 Kudos

Hi Dect,

Just join the OITM to OBTN table

Regards
Edy

former_member351438
Participant
0 Kudos

Hi Edy,

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

edy_simon
Active Contributor
0 Kudos

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

former_member351438
Participant
0 Kudos

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?