We are trying to pull data on the last time we have used a part number in the last 3 years. I have written a BAQ to pull data, but I am getting back every time the same part number has been used ( every transaction in this time period )
I want only a single line for each part number in the past 3 years or to show every part number that has been used since say 02/01/2019 and show nothing pior to it.
Here is the code I am working work:
select top (50000)
[Part].[PartNum] as [Part_PartNum],
[Part].[PartDescription] as [Part_PartDescription],
[Part].[ClassID] as [Part_ClassID],
[PartTran].[TranType] as [PartTran_TranType],
[PartTran].[TranDate] as [PartTran_TranDate]
from Erp.PartTran as PartTran
inner join Erp.Part as Part on
PartTran.Company = Part.Company
and PartTran.PartNum = Part.PartNum
where (PartTran.SysDate >= ‘02/01/2019’ and PartTran.TranType = @TranType)
Currently I can put in my Trans Type ( PUR-STK, MTL-STK ect… ) and pull that data from 02/01/2019 to Current, but I am getting every transaction within that span of time.
I would use the out of the box inventory reports instead of trying to recreate it. You can edit what the reports return by using the Stock Provision Report Format
Sorry I am new to this - I don’t have a ton of experince in Epicor with Reports and where they are located. I was randomly tasked by my team to do this - like run head first.
Can you assist me where I would locate what you are talking about please?
Under Inventory Management/Reports there is the Excess Stock Provision, Slow Moving Stock, and Stock Aging Report. All of these use the Stock Provision Report Format under Inventory Management/Setup. They use the code but are called something else on each report dialog.
These reports are intended to calculate a dollar value to post to the GL to account for inventory valuation and future scrapping of material from inventory. However, they can also be used for your need.
I would go to the screens I mentioned and pull up the Application Help to get more information.
