Skip to main content
Solved

Qty on Hand as of a certain date


Forum|alt.badge.img

Hello everyone,

 

I am in the process of creating a generic inquiry that shows an inventory valuation by date. The reports that are pre-built with Acumatica only show by financial period. The most important column is the quantity on hand (which is used to calculate the value of the inventory).

Here are my joins:

 

Most of my joins are on inventoryID or siteID.

For quantity on hand I am using this:

[INLocationStatus.QtyOnHand]

The problem is that the quantity on hand that I am picking up is simply the latest quantity on hand, rather than the quantity on hand as of the date chosen in the parameter:

 

 

Does this seem to be a conditions/parameter issue? I am in the process of testing with trial and error on the conditions and parameters. Maybe there is another table I should use for qty on hand?

 

Thank you all for any input.

Best answer by podonnell11

I ended up using the INTranSplit table for the report and made do with some conditional statements in the results grid. It works for about 99.9% of the items we are using. 

 

 

View original
Did this topic help you find an answer to your question?

12 replies

Naveen Boga
Captain II
Forum|alt.badge.img+19
  • Captain II
  • 3377 replies
  • March 20, 2021

Hi, @podonnell11 , Please correct me if I’m wrong. I assuming below is your requirement.

You want to know the “Qty. OnHand” of each Inventory on the particular date.

 

Could you please share your GI in XML format? I may help you If I found anything.


Forum|alt.badge.img
  • Author
  • Semi-Pro II
  • 40 replies
  • March 20, 2021
Naveen B wrote:

Hi, @podonnell11 , Please correct me if I’m wrong. I assuming below is your requirement.

You want to know the “Qty. OnHand” of each Inventory on the particular date.

 

Could you please share your GI in XML format? I may help you If I found anything.

 

 

Naveen,

 

Yes this is correct. For example let’s say I want to see the quantity of “X Product” on February 10th. Then when I change the date to March 10th, the GI will display the quantity as of that date (let’s say 10 of X product were added to inventory since then).

 

I have attached the XML.

 

I think it may have to do with either the right parameters or a difference in the joins/relations.


Naveen Boga
Captain II
Forum|alt.badge.img+19
  • Captain II
  • 3377 replies
  • March 21, 2021

Hi @podonnell11 

 

I have created SQL Query and scalar function to fetch the QtyOnHand based on the date and I verified and it seems working fine. Please verify and if that works fine then create an SQL view for that create a DAC and then create a Generic Inquiry.

 

There might be a simple but I tried like below. Hope this helps!!
 

SQL Query

Select I.CompanyID, I.InventoryID,ISS.SiteID ,  (Select [dbo].GetOnHandQty(I.CompanyID, I.InventoryID, ISS.SiteID, GETDATE()-460)) OnHandQty from InventoryItem I 
Inner Join INSiteStatus ISS on I.CompanyID=ISS.CompanyID and I.InventoryID=ISS.InventoryID
Inner Join INTran IT on I.CompanyID =IT.CompanyID and IT.InventoryID= I.InventoryID and IT.SiteID = ISS.SiteID
Where I.CompanyID=2  and I.InventoryID=11694 and ISS.SiteID=1537
Group by I.CompanyID, I.InventoryID, ISS.SiteID

Scalar Function

Create function [dbo].GetOnHandQty(@CompanyID int, @InventoryItem int, @siteID int, @INTranCreatedTime datetime)
RETURNS decimal(19,4)
as
begin

Declare @GetONHand decimal(19,4)
Declare @ReceiptyQty decimal(19,4)
Declare @ASYQty decimal(19,4)
Declare @CRMQty decimal(19,4)
Declare @DSYQty decimal(19,4)
Declare @INVQty decimal(19,4)
Declare @IIIQty decimal(19,4)

Set @ReceiptyQty = (Select ISNULL(Sum(IT.Qty),0) ReceiptyQty from INTran IT where IT.CompanyID=2 and IT.TranType='RCP' and IT.InventoryID=@InventoryItem and IT.SiteID= @siteID and IT.CreatedDateTime <= @INTranCreatedTime
Group by IT.CompanyID, IT.InventoryID, IT.SiteID)

Set @ASYQty = (Select ISNULL(Sum(IT.Qty),0) ReceiptyQty from INTran IT where IT.CompanyID=2 and IT.TranType='ASY' and IT.InventoryID=@InventoryItem and IT.SiteID= @siteID and IT.CreatedDateTime <= @INTranCreatedTime
Group by IT.CompanyID, IT.InventoryID, IT.SiteID)

Set @CRMQty = (Select ISNULL(Sum(IT.Qty),0) ReceiptyQty from INTran IT where IT.CompanyID=2 and IT.TranType='CRM' and IT.InventoryID=@InventoryItem and IT.SiteID= @siteID and IT.CreatedDateTime <= @INTranCreatedTime
Group by IT.CompanyID, IT.InventoryID, IT.SiteID)

Set @DSYQty = (Select ISNULL(Sum(IT.Qty),0) ReceiptyQty from INTran IT where IT.CompanyID=2 and IT.TranType='DSY' and IT.InventoryID=@InventoryItem and IT.SiteID= @siteID and IT.CreatedDateTime <= @INTranCreatedTime
Group by IT.CompanyID, IT.InventoryID, IT.SiteID)

Set @INVQty = (Select ISNULL(Sum(IT.Qty),0) ReceiptyQty from INTran IT where IT.CompanyID=2 and IT.TranType='INV' and IT.InventoryID=@InventoryItem and IT.SiteID= @siteID and IT.CreatedDateTime <= @INTranCreatedTime
Group by IT.CompanyID, IT.InventoryID, IT.SiteID)

Set @IIIQty = (Select ISNULL(Sum(IT.Qty),0) ReceiptyQty from INTran IT where IT.CompanyID=2 and IT.TranType='III' and IT.InventoryID=@InventoryItem and IT.SiteID= @siteID and IT.CreatedDateTime <= @INTranCreatedTime
Group by IT.CompanyID, IT.InventoryID, IT.SiteID)

set @GetONHand =  ISNULL(@ReceiptyQty,0) - ISNULL(@ASYQty,0) - ISNULL(@CRMQty,0) - ISNULL(@DSYQty,0) - ISNULL(@INVQty,0) - ISNULL(@IIIQty,0)

Return @GetONHand


End

 

 

 

 

 

 

 

 

 


Forum|alt.badge.img+5
  • Captain II
  • 398 replies
  • May 26, 2021

@podonnell11 @Naveen B 
Hello, actually Acumatica itself has logged each day’s qty on hand.
Please find the table “INItemSiteHistD”


Forum|alt.badge.img+9
  • Semi-Pro I
  • 714 replies
  • July 15, 2021

Hi @podonnell11 You can also check the table INItemSiteHistDay for all the quantities, as shown in the below screenshot.

 

Note: All the documents related to inventory transaction for the numbers to be correct.

 

 


Forum|alt.badge.img
  • Author
  • Semi-Pro II
  • 40 replies
  • Answer
  • October 20, 2021

I ended up using the INTranSplit table for the report and made do with some conditional statements in the results grid. It works for about 99.9% of the items we are using. 

 

 


  • Freshman I
  • 5 replies
  • August 29, 2022

Hello was anyone able to get this done? I need to get a inventory based on certain days. All i need is:

 

Item #, Product Type, Warehouse, Unit Cost(landed), Ending Qty as of that Date , and extended cost( if possible)

 

Any help would be greatly appreciated. 

  @podonnell11 @Naveen Boga @ray20 @ChandrasekharM 


Forum|alt.badge.img+9
  • Semi-Pro I
  • 714 replies
  • August 29, 2022

Hi @martin29  Please try creating the generic inquiry based on the table - INItemSiteHistDay

 


  • Freshman I
  • 5 replies
  • August 29, 2022

Thank you, Do you have one i can copy?

 


Forum|alt.badge.img+9
  • Semi-Pro I
  • 714 replies
  • August 29, 2022

Hi @martin29 Please find the attached generic inquiry, You can import to your generic inquiries.


Forum|alt.badge.img+9
  • Semi-Pro I
  • 714 replies
  • August 29, 2022

Hi @martin29 Also, please find the attached xml import scenario and the below screenshot that shows you the required data from Inventory History.

 

 


  • Freshman I
  • 5 replies
  • August 31, 2022

thank you. i tried this, but how do i know what the ending inventory was on a specific date? it seems to only give the transactions?

 

 


Reply


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings