Question

Qty on Hand as of a certain date

  • 20 March 2021
  • 3 replies
  • 78 views

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.


3 replies

Userlevel 6
Badge +3

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.

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.

Userlevel 6
Badge +3

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

 

 

 

 

 

 

 

 

 

Reply


About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2020  Acumatica, Inc. All rights reserved