I was asked to restrict results of the Add Item button of the SOOrderEntry screen to only non-stock items and stock items associated to the current branch, filtering out items of other branches that are not extended to the current branch.
My PXProjection inherits from SOSiteStatusSelected and uses PXSubstitute to utilize my select instead of the one in the base projection. This seems to work well, except when I apply a filter to the Warehouse (Site ID) column in the dialog’s grid. Other columns seem to work fine, but not Warehouse. I get the following error:
This is because filtering the result grid injects the following into the underlying SQL statement which obviously won’t work because the projection’s class isn’t in the database.
AND ((SELECT TOP (1) [INSiteExt].[siteCD]
FROM [INSite] AS [INSiteExt]
WHERE ([INSiteExt].[CompanyID] = 4)
AND ([INSiteExt].[BranchID] IS NULL
OR ([INSiteExt].[BranchID] IN (75, 79, 78, 80, 81, 82)))
AND [INSiteExt].[SiteID] = [SOSiteStatusSelectedExt].[SiteID]) <> @P9))
The custom projection is shown below. The change is noted between the /***/ markers. The only other change is, of course, extra closing brackets to support my additions within the syntax.
namespace SSCS.SO
{
[Serializable]
[PXSubstitute]
[PXCacheName("SSCS.SO.SOSiteStatusSelected")]
[PXProjection(typeof(Select2<InventoryItem,
LeftJoin<INSiteStatus,
On<INSiteStatus.inventoryID, Equal<InventoryItem.inventoryID>,
And<InventoryItem.stkItem, Equal<boolTrue>,
And<INSiteStatus.siteID, NotEqual<SiteAttribute.transitSiteID>>>>,
LeftJoin<INSubItem,
On<INSiteStatus.FK.SubItem>,
LeftJoin<INSite,
On2<INSiteStatus.FK.Site,
And<INSite.baseCuryID, EqualBaseCuryID<Current2<SOOrder.branchID>>>>,
LeftJoin<INItemXRef,
On<INItemXRef.inventoryID, Equal<InventoryItem.inventoryID>,
And2<Where<INItemXRef.subItemID, Equal<INSiteStatus.subItemID>,
Or<INSiteStatus.subItemID, IsNull>>,
And<Where<CurrentValue<SOSiteStatusFilter.barCode>, IsNotNull,
And<INItemXRef.alternateType, In3<INAlternateType.barcode, INAlternateType.gIN>>>>>>,
LeftJoin<INItemPartNumber,
On<INItemPartNumber.inventoryID, Equal<InventoryItem.inventoryID>,
And<INItemPartNumber.alternateID, Like<CurrentValue<SOSiteStatusFilter.inventory_Wildcard>>,
And2<Where<INItemPartNumber.bAccountID, Equal<Zero>,
Or<INItemPartNumber.bAccountID, Equal<CurrentValue<SOOrder.customerID>>,
Or<INItemPartNumber.alternateType, Equal<INAlternateType.vPN>>>>,
And<Where<INItemPartNumber.subItemID, Equal<INSiteStatus.subItemID>,
Or<INSiteStatus.subItemID, IsNull>>>>>>,
LeftJoin<INItemClass,
On<InventoryItem.FK.ItemClass>,
LeftJoin<INPriceClass,
On<INPriceClass.priceClassID, Equal<InventoryItem.priceClassID>>,
LeftJoin<InventoryItemCurySettings,
On<InventoryItemCurySettings.inventoryID, Equal<InventoryItem.inventoryID>,
And<InventoryItemCurySettings.curyID, EqualBaseCuryID<Current2<SOOrder.branchID>>>>,
LeftJoin<BAccountR,
On<BAccountR.bAccountID, Equal<InventoryItemCurySettings.preferredVendorID>>,
LeftJoin<INItemCustSalesStats,
On<CurrentValue<SOSiteStatusFilter.mode>, Equal<SOAddItemMode.byCustomer>,
And<INItemCustSalesStats.inventoryID, Equal<InventoryItem.inventoryID>,
And<INItemCustSalesStats.subItemID, Equal<INSiteStatus.subItemID>,
And<INItemCustSalesStats.siteID, Equal<INSiteStatus.siteID>,
And<INItemCustSalesStats.bAccountID, Equal<CurrentValue<SOOrder.customerID>>,
And<Where<INItemCustSalesStats.lastDate, GreaterEqual<CurrentValue<SOSiteStatusFilter.historyDate>>,
Or<CurrentValue<SOSiteStatusFilter.dropShipSales>, Equal<True>,
And<INItemCustSalesStats.dropShipLastDate, GreaterEqual<CurrentValue<SOSiteStatusFilter.historyDate>>>>>>>>>>>,
LeftJoin<INUnit,
On<INUnit.inventoryID, Equal<InventoryItem.inventoryID>,
And<INUnit.unitType, Equal<INUnitType.inventoryItem>,
And<INUnit.fromUnit, Equal<InventoryItem.salesUnit>,
And<INUnit.toUnit, Equal<InventoryItem.baseUnit>>>>>
>>>>>>>>>>>,
Where<CurrentValue<SOOrder.customerID>, IsNotNull,
And2<CurrentMatch<InventoryItem, AccessInfo.userName>,
And2<Where<INSiteStatus.siteID, IsNull, Or<INSite.branchID, IsNotNull, And2<CurrentMatch<INSite, AccessInfo.userName>,
And<Where2<FeatureInstalled<FeaturesSet.interBranch>,
Or2<SameOrganizationBranch<INSite.branchID, Current<SOOrder.branchID>>,
Or<CurrentValue<SOOrder.behavior>, Equal<SOBehavior.qT>>>>>>>>,
And2<Where<INSiteStatus.subItemID, IsNull, Or<CurrentMatch<INSubItem, AccessInfo.userName>>>,
And2<Where<CurrentValue<INSiteStatusFilter.onlyAvailable>, Equal<boolFalse>,
Or<INSiteStatus.qtyAvail, Greater<PX.Objects.CS.decimal0>>>,
And2<Where<CurrentValue<SOSiteStatusFilter.mode>, Equal<SOAddItemMode.bySite>,
Or<INItemCustSalesStats.lastQty, Greater<decimal0>,
Or<CurrentValue<SOSiteStatusFilter.dropShipSales>, Equal<True>, And<INItemCustSalesStats.dropShipLastQty, Greater<decimal0>>>>>,
/***/
And2<Where2<Where<INSite.branchID, IsNull, And<InventoryItem.stkItem, Equal<False>>>,
Or<Where<INSite.branchID, IsNotNull, And<INSite.branchID, Equal<CurrentValue<AccessInfo.branchID>>>>>>,
And2<Where<CurrentValue<SSINSetup.assetClassID>, IsNull, Or<InventoryItem.itemClassID, NotEqual<CurrentValue<SSINSetup.assetClassID>>>>,
/***/
And<InventoryItem.isTemplate, Equal<False>,
And<InventoryItem.itemStatus, NotIn3<
InventoryItemStatus.unknown,
InventoryItemStatus.inactive,
InventoryItemStatus.markedForDeletion,
InventoryItemStatus.noSales>>>>>>>>>>>>), Persistent = false)]
public sealed class SOSiteStatusSelectedExt : PX.Objects.SO.SOSiteStatusSelected
{
public static bool IsActive() =>
SSCSAccess.FeatureInstalled<SSCSFeaturesSet.main>();
}
}
Since this seems to come into play only when applying a filter to the grid column, is this an Acumatica bug, or have I missed something in the custom projection?
This is on Build 23.110.0025.
