I need to get the infomation of such items:
We have qty on hand>0 And We didn’t sell for a long time like 90 days.
I can get this done by joining INsitestatus and ARTran table.
The key of my solution is that Acumatica will by default get the maximum of trandate after I group by inventoyID
And I can use a “second filter” on GI results like below to get the information I wanted.
It is a solution.
However， it is quite time consuming because ARtran table is quite huge.
Can I replace ARtran with another table (Where I can still get the last sell date infomation and join much less lines) ?
Or is there a better way to fulfill my need.
Best answer by Gabriel Michaud
The INItemSiteHist and INItemSiteHistD tables will give you sales statistics per item/warehouse. The first one is aggregated by the financial period, the second one by day. This will be much faster than looking at individual transactions in ARTran.
Make sure to include InventoryID, SubItemID, and SiteID in your join condition for optimal performance.