Formula rollup report shows inconsistent values after BOM update

PLM Oracle Agile PLM
, , , , , , , ,
1

We’re experiencing inconsistent rollup values in our formula management reports after BOM updates. The issue started after upgrading to 9.3.4. When engineers modify BOM structures, the formula rollup calculations don’t reflect changes immediately - sometimes taking hours to update.

I’ve checked the rollup calculation job status in the scheduler, and it shows completed successfully. However, the PX event triggers for BOM changes seem to fire inconsistently. Our regulatory reporting depends on accurate rollup data for compliance calculations.

Here’s what we see in the logs:


INFO: BOM update completed for item F-2401
WARN: Formula rollup job queued but cache not invalidated
ERROR: Stale data detected in rollup report RR-COMP-001

Has anyone encountered similar rollup calculation issues? We need these reports to update within minutes, not hours, for our compliance workflows.

system 2

Your specific issue involves the interaction between three systems that need to work in sequence. Looking at your logs and the regulatory reporting requirement, here’s what’s happening and how to fix it:

Rollup Calculation Job Status Issue: The job shows completed because it finished its calculation phase, but it’s not triggering the downstream cache invalidation. In agile.properties, verify:


com.agile.formula.rollup.autoInvalidateCache=true
com.agile.formula.rollup.syncMode=true

The syncMode parameter forces synchronous execution, which is critical for compliance reporting.

PX Event Triggers for BOM Changes: Your PX configuration needs to explicitly handle the cache invalidation. Add this to your BOM change event handler:

// Clear formula rollup cache after BOM update
FormulaRollupCache.invalidate(bomItem.getNumber());
ReportingCache.clearDependentReports("FORMULA_ROLLUP");

Reporting Cache Invalidation: The core problem is that SQL Reports maintains its own cache layer separate from the formula rollup cache. You need to configure cascade invalidation in reporting-config.xml:

<cache-policy name="formula-reports" cascade="true">
  <invalidate-on-event type="BOM_UPDATE"/>
  <invalidate-on-event type="FORMULA_RECALC"/>
</cache-policy>

Immediate Fix: For your current situation, run this SQL to manually clear stale report data:

DELETE FROM agile.report_cache
WHERE report_type='FORMULA_ROLLUP'
AND last_update < (SELECT max(modified_date) FROM agile.bom WHERE item_number LIKE 'F-%');

Long-term Solution: Implement a monitoring query that alerts when rollup data age exceeds your compliance threshold (you mentioned minutes). This ensures you catch cache issues before they impact regulatory submissions. The synchronous mode will add 2-3 seconds to BOM update operations, but that’s acceptable for compliance-critical workflows.

Test this configuration in your staging environment first, as synchronous rollup can impact performance for bulk BOM operations.

This draft is based on general Oracle Agile PLM knowledge. It has not been verified against your specific version and environment. Practitioners: verify the steps and share your experience below.

3

I’ve seen this before. The rollup calculation job completing successfully doesn’t guarantee the reporting cache gets invalidated. Check your PX configuration for the BOM change event - it should trigger both the rollup recalculation AND the cache clear. Are you using custom PX extensions? Sometimes they intercept the standard event chain and break the cache invalidation sequence.

4

The warning about cache not being invalidated is your smoking gun. In 9.3.4, there’s a known timing issue where the rollup job completes before the cache invalidation service processes the request. We had the same problem with our material compliance reports. You need to verify two things: first, that your cache invalidation settings in the reporting framework are configured correctly, and second, that the PX event sequence includes the cache clear step. The default configuration sometimes misses this in formula management specifically.

joseph_coder 5

“Confirmed this resolves the stale rollup values in our compliance reports — setting com.agile.formula.rollup.syncMode=true in agile.properties forced the cache invalidation to complete before our regulatory export ran.”

6

Check your SQL Reports configuration too. If you’re using custom SQL for the rollup reports, they might be querying cached views instead of live data. We modified our reports to use FORCE_REFRESH hints and saw immediate improvement. Also, the rollup calculation job status showing ‘completed’ doesn’t mean the data propagated to all reporting layers - there’s often a lag in the aggregation tables that feed the reports.

7

For regulatory reporting, you can’t rely on eventual consistency. We implemented a two-part solution: immediate recalculation triggers for critical formulas, and a verification step that compares rollup values before and after BOM changes. The PX event triggers need to be synchronous for compliance-critical calculations, not queued asynchronously like the default behavior. This solved our FDA reporting issues where stale data was unacceptable.

8

The error message about stale data detection is interesting - that means Agile knows the data is stale but isn’t refreshing it automatically. This points to a configuration gap in your refresh policies. In the reporting scheduler, there should be a dependency chain: BOM update → formula recalc → cache invalidate → report refresh. If any link is missing or asynchronous when it should be synchronous, you get the delays you’re seeing. Check the event subscription configuration for each step.