Creating a table extension on INSiteStatusByCostCenter causes an error when Releasing IN Receipts

Question

I thought I would be clever with a synchronization project. I need to know when the last time I synced the data in a INSiteStatusByCostCenter record. So I created a separate table called INSiteStatusByCostCenterTPSync. This is the DAC:

  [Serializable]
  [PXTable(typeof(INSiteStatusByCostCenter.inventoryID), typeof(INSiteStatusByCostCenter.subItemID), typeof(INSiteStatusByCostCenter.siteID), typeof(INSiteStatusByCostCenter.costCenterID), IsOptional = true)]
  public class INSiteStatusByCostCenterTPSync : PXCacheExtension<PX.Objects.IN.INSiteStatusByCostCenter>
  {

    #region InventoryID
    [PXDBInt(IsKey = true)]
    public virtual Int32? InventoryID { get; set; }
    public abstract class inventoryID : BqlInt.Field<inventoryID> { }
    #endregion

    #region SubItemID
    [PXDBInt(IsKey = true)]
    public virtual Int32? SubItemID { get; set; }
    public abstract class subItemID : BqlInt.Field<subItemID> { }
    #endregion

    #region SiteID
    [PXDBInt(IsKey = true)]
    public virtual Int32? SiteID { get; set; }
    public abstract class siteID : BqlInt.Field<siteID> { }
    #endregion

    #region CostCenterID
    public abstract class costCenterID : PX.Data.BQL.BqlInt.Field<costCenterID> { }

    [PXDBInt(IsKey = true)]
    [PXDefault]
    public virtual int? CostCenterID
    {
      get;
      set;
    }
    #endregion

    #region LastTPSync
    [PXDBDateAndTime(UseTimeZone = true)]
    [PXUIField(DisplayName = "Last Record Sync", Enabled = false)]
    [PXDefault(PersistingCheck = PXPersistingCheck.Nothing)]
    public virtual DateTime? LastTPSync { get; set; }
    public abstract class lastTPSync : PX.Data.BQL.BqlDateTime.Field<lastTPSync> { }
    #endregion
  }

Everything seemed to work well and, by using a projection, I can update records in this table without impacting the main INSiteStatusByCostCenter table.

However. In doing testing I received some product and tried to release an IN Receipt. I get the following error:

Invalid column name [SiteStatusByCostCenter].[LastTPSync]

A SQL trace sees that the graph is making this SQL call (which I’ve shortened):

SELECT /* IN.30.10.00, 202303D8 */TOP (1) [SiteStatusByCostCenter].[SubItemID], [SiteStatusByCostCenter].[QtyOnHand], [SiteStatusByCostCenter].[QtyNotAvail], [SiteStatusByCostCenter].[QtyAvail], 

...

[SiteStatusByCostCenter].[InventoryID], [SiteStatusByCostCenter].[SiteID], [SiteStatusByCostCenter].[CostCenterID], [SiteStatusByCostCenter].[LastTPSync]
FROM [INSiteStatusByCostCenter] [SiteStatusByCostCenter]
WHERE (

...

The SQL isn’t joining INSiteStatusByCostCenter to INSiteStatusByCostCenterTPSync like it should. Something is generating the SQL in a way that LastTPSync is expected to be found within the INSiteStatusByCostCenter table.

Maybe I’ve done something wrong within my declarations?

Steve Milner · Accepted Answer

@Django your declarations are fine. The problem is the DAC that release actually uses.

INReleaseProcess doesn't work with INSiteStatusByCostCenter directly. It uses SiteStatusByCostCenter, a derived DAC decorated with [Accumulator(BqlTable = typeof(INSiteStatusByCostCenter))]. That's the alias in your trace. Your extension is inherited by that derived DAC, so LastTPSync becomes a bound field in its cache.

PXTable, PXAccumulator and PXProjection all derive from PXDBInterceptorAttribute, and a cache only gets one interceptor. When the DAC carries its own, that one wins and your PXTable gets attached as its Child. The cache then asks the interceptor for the table command, which is where the join to the extension table would come from. PXAccumulatorAttribute returns null for it, and the only interceptor that ever reads Child is PXProjection. So on an accumulator DAC the field is still in the column list, but nothing builds the join. You get exactly the SQL you traced.

The select itself is most likely SiteStatusByCostCenter.PK.Find in ReplanBackOrders. TransitSiteStatusByCostCenter is built the same way, so transfers would hit it too.

I'd drop the extension and make INSiteStatusByCostCenterTPSync a standalone DAC with the same four keys. You're already writing to it through a projection, so you can join to it there and in your sync query, and the release caches never see the field.

That's from the 2026 R1 code. I haven't published the standalone version to test it.

Django · Answer

Thank you ​@Steve Milnerfor the explanation - it makes sense and I’d also never have figured that out on my own. Cheers!

Sign up

Social Login

Login to the community

Social Login