Skip to main content

Im using this query to get data from InventorySummaryEnquiryResult


var query = new PXSelectReadonly<InventorySummaryEnquiryResult>(new PXGraph()).Select<InventorySummaryEnqFilter>().Where(s => s.SiteID == 13).FirstOrDefault();

 

But i have an error message in the app 
Invalid object name “InventorySummaryEnquiryResult”

Hi SadokHanini,

Change the PXSelectReadonly with PXSelect and try in your query, because PXSelectReadonly fetches the data directly from the Database but InventorySummaryEnquiryResult is not a Acumatica DB table. 

Hope this will helps you to fix the issue. 

 


Hi SadokHanini,

Change the PXSelectReadonly with PXSelect and try in your query, because PXSelectReadonly fetches the data directly from the Database but InventorySummaryEnquiryResult is not a Acumatica DB table. 

Hope this will helps you to fix the issue. 

 

Same Error 
PXResultset<InventorySummaryEnquiryResult> res = PXSelect<InventorySummaryEnquiryResult>.Select(new PXGraph());


Hi @SadokHanini,

You want to get the QtyAvailable value based on the warehouse and inventory?

If yes, the below code will help you to get the QtyAvailable value from the database

 

 PXSelectGroupBy<INSiteStatus, Where<INSiteStatus.inventoryID, Equal<Required<INSiteStatus.inventoryID>>>,                                                              Aggregate<GroupBy<INSiteStatus.inventoryID, Sum<INSiteStatus.qtyAvail>>>                                     .Select(graph, row.InventoryID);

 

Best Regards,
Naveen B

 


Hi @SadokHanini,

You want to get the QtyAvailable value based on the warehouse and inventory?

If yes, the below code will help you to get the QtyAvailable value from the database

 

 PXSelectGroupBy<INSiteStatus, Where<INSiteStatus.inventoryID, Equal<Required<INSiteStatus.inventoryID>>>,                                                              Aggregate<GroupBy<INSiteStatus.inventoryID, Sum<INSiteStatus.qtyAvail>>>                                     .Select(graph, row.InventoryID);

 

Best Regards,
Naveen B

 

Thanks :) 
I need also to get UnitCost
Do u have an idea how ?

thanks

 


Hi @SadokHanini ,

To get the unitcost of a Stock Item, please use the below code.

 

  INItemCost objINItemCost =PXSelect<INItemCost, Where<INItemCost.inventoryID, Equal<Required<INItemCost.inventoryID>>>>.Select(base, row.InventoryID);
                        if (objINItemCost != null)
                        {
                            var UnitCost = objINItemCost.LastCost;
                        }

 

Best Regards,

Naveen B


Hi @SadokHanini ! InventorySummaryEnquiryResult is an Acumatica DAC used on that screen, but isn’t in SQL, but created to show some data in a particular view.

As Naveen included in the above suggestions, if you are looking for the quantities of items then check INSiteStatus:

If you are looking for cost of items then I would check INCostStatus:

 

These are also SQL tables, so they can be found here to check the database values stored, but Acumatica DACs will not be found in SQL and to check their contents you can try loading the DAC in a GI by itself to check the values, though sometimes the nature of the DAC doesn’t really work well in a GI environment and is more intended use is the screen it is used on.

Alternatively, you can also check the Customization > Source Code > Data Access screen to check the contents of an Acumatica DAC to see other helpful information about it:

 


InventorySummaryEnquiryResult is an unbound DAC. Any select operation will fail because there’s no table bound to that DAC in database.

In graph InventorySummaryEnq the method iSERecordsFetch is executing select methods on other tables and dynamically creating InventorySummaryEnq records in memory with that data.

You can either ignore InventorySummaryEnquiryResult and write your own BQL queries from scratch or reverse engineer the queries in InventorySummaryEnq.iSERecordsFetch method (excerpt below) to understand how the data is fetched:

protected virtual IEnumerable iSERecordsFetch()
{
   r … ]

    PXSelectBase<INLotSerialStatus> cmd_lss = new PXSelectReadonly2<INLotSerialStatus,
        InnerJoin<INLocation,
            On<INLotSerialStatus.FK.Location>,
        InnerJoin<InventoryItem,
            On<InventoryItem.inventoryID, Equal<INLotSerialStatus.inventoryID>,
            And<Match<InventoryItem, Current<AccessInfo.userName>>>>,
        InnerJoin<INSite,
            On2<INLotSerialStatus.FK.Site,
            And<INSite.siteID, NotEqual<SiteAttribute.transitSiteID>,
            And<Match<INSite, Current<AccessInfo.userName>>>>>,
        InnerJoin<INSubItem,
            On<INLotSerialStatus.FK.SubItem>,
        LeftJoin<INLotSerClass,
            On<InventoryItem.FK.LotSerialClass>,
        LeftJoin<INLocationCostStatus,
            On<INLocationCostStatus.inventoryID, Equal<INLotSerialStatus.inventoryID>,
                And<INLocationCostStatus.subItemID, Equal<INLotSerialStatus.subItemID>,
                And<INLocationCostStatus.locationID, Equal<INLotSerialStatus.locationID>>>>,
        LeftJoin<INSiteCostStatus,
            On<INSiteCostStatus.inventoryID, Equal<INLotSerialStatus.inventoryID>,
                And<INSiteCostStatus.subItemID, Equal<INLotSerialStatus.subItemID>,
                And<INSiteCostStatus.siteID, Equal<INLotSerialStatus.siteID>>>>>>>>>>>,
        Where<INLotSerialStatus.inventoryID, Equal<Current<InventorySummaryEnqFilter.inventoryID>>,
            And<Where<Current<InventorySummaryEnqFilter.expandByLotSerialNbr>, Equal<True>, And<INLotSerClass.lotSerAssign, Equal<INLotSerAssign.whenReceived>,
            Or<InventoryItem.valMethod, Equal<INValMethod.specific>>>>>>>(this);

  ;…]

    return resultset
        .OrderBy(x => x.SubItem?.SubItemCD)
        .ThenBy(x => x.Site?.SiteCD)
        .ThenBy(x => x.Location?.LocationCD)
        .ThenBy(x => x.Result.LotSerialNbr)
        .Select(x => x.Result);
}


Reply