Query showing where Item was last used on a AP Invoice and AR Invoice

[former_member268870]

Dear Experts,

I need to create a query that shows the Item code and the latest date where it was used on any Document (AR, AP and Inventory movements and Production....

Is there a quick way to do this?

If not, I would appreciate you showing me the right direction by only using 2 document types....

Example:

SELECT T0.[ItemCode], T0.[ItemName], 
****Last DocDate whether that was on a AP or AR Invoice**** 
FROM OITM T0  

[former_member312729]

Hi ,

Try this

SELECT Distinct  T0.[ItemCode], T0.[Dscription],max(T0.DocDate) , Case  when T0.Transtype ='13' then 'ARInvoice'  when T0.Transtype ='18' then 'APInvoice' else '-' end  as 'TransType'
FROM OINM T0 
where transtype in ('13','18') 
GROUP BY  T0.[ItemCode], T0.[Dscription],T0.Transtype

Regards:

Balaji.S

[former_member268870]

Balaji,

Thank you! This really helped me...

I used the OINM table in the new query, as linking to each document type to get last activity date will be cumbersome....

I do have a question on the OINM table - this lists only the activity against the Items that resulted in a Journal Entry, right? Not Sales Orders and Purchase Orders?

SELECT 
	T0.[ItemCode],   
	T0.[ItemName], 
	T1.[ItmsGrpNam], 
	T0.[InvntItem], 
	T0.[SellItem], 
	T0.[PrchseItem], 
	max(T3.[DocDate]) AS 'Last Actvity (Inventory, Sale, Purchase)',
	T0.[OnHand] AS 'Quantity in Warehouses',
	T2.[TrnspName] AS 'Shipping Type', 
	T0.[validFor] AS 'Active' 


FROM OITM T0  INNER JOIN 
	OITB T1 ON T0.[ItmsGrpCod] = T1.[ItmsGrpCod] INNER JOIN 
	OSHP T2 ON T0.[ShipType] = T2.[TrnspCode] LEFT OUTER JOIN 
	OINM T3 ON T0.[ItemCode] = T3.[ItemCode] 


--WHERE T0.[ItemCode] = 'A00001'
GROUP BY 
	T0.[ItemCode], 
	T0.[ItemName], 
	T1.[ItmsGrpNam],
	T0.[InvntItem], 
	T0.[SellItem], 
	T0.[PrchseItem],
	T0.[OnHand],
	T2.[TrnspName], 
	T0.[validFor]

[former_member268870]

I have found that the OINM table does not pick up on Non-Inventory items that was used on documents....

Is there another table for non-inventory items last activity date?

[former_member312729]

Try this

SELECT Distinct  T1.[ItemCode], T1.[Dscription],max(T0.DocDate) ,  'AR Invoice' as 'TransType'
FROM OINV T0  inner join inv1 T1 on T0.DocEntry = T1.DocEntry
GROUP BY  T1.[ItemCode], T1.[Dscription]
Union All
SELECT Distinct  T1.[ItemCode], T1.[Dscription],max(T0.DocDate) ,  'AP Invoice' as 'TransType'
FROM OPCH T0  inner join PCh1 T1 on T0.DocEntry = T1.DocEntry
GROUP BY  T1.[ItemCode], T1.[Dscription]

[former_member312729]

Hi ,

Yes OINM will not record the Sale order or purchase order since not related to Inventory movement

Regards:

Balaji.S