01-11-2017 12:07 PM - edited 02-03-2024 5:51 PM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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?
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
Hi Dect,
Just join the OITM to OBTN table
Regards
Edy
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
User | Count |
---|---|
89 | |
7 | |
6 | |
3 | |
3 | |
3 | |
3 | |
3 | |
3 | |
2 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.