+4Hi! has anyone experienced this where for the same item if you create a GI with table INLocationStatus it returns a qty on hand different than querying for the same table using SSMS. I’m not doing any joins or anything complex. Simply selecting Inventory ID, Qty on hand from INLocationStatus:
Best answer by lauraj46
HI
From the DAC Schema Browser you can view the DAC Source Code. You also click on DAC Query to view the SQL query that Acumatica uses to retrieve the data.
Here is the DAC region:
#region QtyOnHand
public abstract class qtyOnHand : PX.Data.BQL.BqlDecimal.Field<qtyOnHand> { }
protected Decimal? _QtyOnHand;
[PXDBQuantity(BqlField = typeof(INLocationStatusByCostCenter.qtyOnHand))]
[PXDefault(TypeCode.Decimal,"0.0")]
[PXUIField(DisplayName="Qty. On Hand")]
public virtual Decimal? QtyOnHand
{
get
{
return this._QtyOnHand;
}
set
{
this._QtyOnHand = value;
}
}
#endregionAnd here is the SQL query:
SELECT
[INLocationStatus_INLocationStatusByCostCenter].[InventoryID] AS [InventoryID],
[INLocationStatus_INLocationStatusByCostCenter].[QtyOnHand] AS [QtyOnHand]
...
FROM
[INLocationStatusByCostCenter] [INLocationStatus_INLocationStatusByCostCenter]
WHERE
([INLocationStatus_INLocationStatusByCostCenter].[CostCenterID] = 0)Based on this you can see tha tINLocationStatus is actually a projection of INLocationStatusByCostCenter in the underlying database.
Hope this helps!
Laura
+8HI
From the DAC Schema Browser you can view the DAC Source Code. You also click on DAC Query to view the SQL query that Acumatica uses to retrieve the data.
Here is the DAC region:
#region QtyOnHand
public abstract class qtyOnHand : PX.Data.BQL.BqlDecimal.Field<qtyOnHand> { }
protected Decimal? _QtyOnHand;
[PXDBQuantity(BqlField = typeof(INLocationStatusByCostCenter.qtyOnHand))]
[PXDefault(TypeCode.Decimal,"0.0")]
[PXUIField(DisplayName="Qty. On Hand")]
public virtual Decimal? QtyOnHand
{
get
{
return this._QtyOnHand;
}
set
{
this._QtyOnHand = value;
}
}
#endregionAnd here is the SQL query:
SELECT
[INLocationStatus_INLocationStatusByCostCenter].[InventoryID] AS [InventoryID],
[INLocationStatus_INLocationStatusByCostCenter].[QtyOnHand] AS [QtyOnHand]
...
FROM
[INLocationStatusByCostCenter] [INLocationStatus_INLocationStatusByCostCenter]
WHERE
([INLocationStatus_INLocationStatusByCostCenter].[CostCenterID] = 0)Based on this you can see tha tINLocationStatus is actually a projection of INLocationStatusByCostCenter in the underlying database.
Hope this helps!
Laura
+4Thanks!
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.