Solved

Qty on Hand as of a certain date


Userlevel 3
Badge

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.

icon

Best answer by podonnell11 20 October 2021, 20:25

View original

12 replies

Userlevel 7
Badge +17

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

 

 

 

 

 

 

 

 

 

Userlevel 7
Badge +9

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

 

Userlevel 6
Badge +5

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

Userlevel 3
Badge

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. 

 

 

Userlevel 7
Badge +9

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.

 

 

Userlevel 1

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?

 

 

Userlevel 1

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 

Userlevel 1

Thank you, Do you have one i can copy?

 

Userlevel 7
Badge +9

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

Userlevel 7
Badge +9

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

 

 

Userlevel 7
Badge +17

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.

Userlevel 3
Badge

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.

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 — 2024  Acumatica, Inc. All rights reserved