Budget variance report not reflecting latest forecast changes despite ETL job completion

ERP Oracle JD Edwards
, , , , , , , ,
1

Our finance team updated budget forecasts in F0902 (Budget File) yesterday, but the budget variance report still shows the old forecast values. The ETL job that feeds our BI Publisher reports shows as completed successfully in the job logs, but the variance calculations are using stale forecast data.

We’ve verified that F0902 contains the correct updated forecasts - we can see them when querying the table directly. However, when we run the budget variance report that compares actuals to forecasts, it’s still using last month’s forecast numbers. This is causing major issues for our budgeting errors and variance analysis.

The ETL job may have failed silently, or there’s a disconnect between the ETL completion and the report data refresh. Has anyone dealt with similar issues where forecasts updated in F0902 don’t flow through to variance reports?

2

That custom timestamp field probably needs a database trigger to auto-update when F0902 records are modified. Without a trigger, manual updates to budget amounts won’t touch the timestamp, and your incremental ETL will miss them.

Create an AFTER UPDATE trigger on F0902:

CREATE TRIGGER F0902_UPDATE_TS
AFTER UPDATE ON F0902
FOR EACH ROW
BEGIN
  UPDATE F0902 SET last_modified = SYSDATE
  WHERE rowid = :NEW.rowid;
END;

This ensures the timestamp updates whenever any F0902 field changes, keeping your incremental ETL in sync.

3

Your issue is a classic ETL pattern problem - incremental loads require proper change data capture (CDC), and your custom timestamp field isn’t being maintained correctly. Here’s the complete solution:

1. Root Cause Analysis Forecasts updated in F0902, but the variance report queries F0902_STAGE. The ETL job that populates the staging table uses incremental loading based on a custom timestamp field. When forecasts were updated, the timestamp wasn’t refreshed, so the ETL’s WHERE clause excluded those records.

The ETL job may have failed silently in the sense that it completed without errors but didn’t capture the changed data.

2. Immediate Fix - Force Full Refresh For immediate relief, manually trigger a full ETL refresh:

// Pseudocode - Full refresh process:

  1. Backup F0902_STAGE table
  2. Truncate F0902_STAGE
  3. Modify ETL job to disable timestamp filter temporarily
  4. Run ETL job to load all F0902 records
  5. Restore timestamp filter after completion // This loads all current forecasts including recent updates

Or if your ETL has a parameter-driven full refresh option, execute:


Run ETL_BUDGET_VARIANCE
  Mode=FULL_REFRESH
  SourceTable=F0902
  TargetTable=F0902_STAGE

3. Implement Proper Change Detection The custom timestamp field needs to update automatically. Add a database trigger:

CREATE OR REPLACE TRIGGER F0902_CDC_TRIGGER
BEFORE UPDATE ON PRODDTA.F0902
FOR EACH ROW
BEGIN
  :NEW.CUSTOM_TIMESTAMP := SYSTIMESTAMP;
  :NEW.CUSTOM_USER := USER;
END;

This ensures every F0902 update refreshes the timestamp field, making incremental ETL reliable.

4. Alternative: Use JDE Audit Fields Instead of custom timestamp, leverage JDE’s standard audit fields if they exist in F0902:

  • UPMT (Update Time)
  • UPDT (Update Date)
  • USER (User ID)

Modify your ETL WHERE clause:

WHERE TO_DATE(UPDT || UPMT, 'YYYYMMDD HH24MISS') >
      (SELECT MAX(last_extract_time) FROM ETL_CONTROL)

This uses native JDE fields that update automatically with every change.

5. ETL Job Monitoring Enhancement The ETL showed success but loaded zero new records. Enhance monitoring to catch this:

// Pseudocode - ETL validation logic:

  1. Count records in F0902 with recent updates
  2. Count records loaded to F0902_STAGE in this run
  3. If source_count > 0 but loaded_count = 0, flag as warning
  4. Send alert: “ETL completed but no data loaded despite source changes”
  5. Include source record count and staging record count in log // This detects silent failures where ETL runs but misses data

Implement this check in your ETL job’s completion section.

6. Report Cache Invalidation After ETL loads new data, ensure BI Publisher clears cached report results:

EXEC DBMS_MVIEW.REFRESH('BUDGET_VARIANCE_MV', 'C');

Or if using BI Publisher cache, call the cache clear API:


BIPlatform.clearReportCache(
  reportPath='/Budget/VarianceReport',
  refreshData=true
);

7. Orchestration Pattern for Reliability Implement end-to-end orchestration to ensure data flows correctly:

// Pseudocode - Complete orchestration:

  1. Monitor F0902 for updates (check timestamp or audit fields)
  2. When updates detected, trigger ETL job
  3. Validate ETL loaded expected record count
  4. Refresh BI Publisher cache/materialized views
  5. Send notification to budget team that report is updated
  6. Log complete data lineage from F0902 to report // This creates reliable, auditable data pipeline

8. Testing and Validation After implementing the fix, validate the complete flow:

a) Update a forecast in F0902:

UPDATE F0902
SET GBOBJ = 5000000
WHERE GBMCU = '1000' AND GBFY = 2025;
COMMIT;

b) Verify timestamp updated (if using trigger):

SELECT GBMCU, GBFY, GBOBJ, CUSTOM_TIMESTAMP
FROM F0902
WHERE GBMCU = '1000' AND GBFY = 2025;

c) Run ETL job and verify staging table updated:

SELECT COUNT(*) FROM F0902_STAGE
WHERE load_timestamp > SYSDATE - 1/24;

d) Run variance report and confirm new forecast values appear.

9. Long-term Architecture Recommendation Consider moving away from staging tables to direct queries if performance allows. This eliminates ETL lag entirely:

  • Create indexed views on F0902 for report performance
  • Have BI Publisher query F0902 directly via views
  • Eliminates ETL as failure point
  • Provides real-time data to reports

Only retain staging tables if F0902 is too large for direct query performance.

The immediate fix is running a full ETL refresh. The permanent solution is implementing the trigger to maintain the timestamp field, combined with enhanced ETL monitoring to catch similar issues in the future. This ensures forecasts updated in F0902 flow through to your variance reports reliably.

4

Found it! The ETL has an incremental load filter that checks a last-modified timestamp field. When we updated the forecasts in F0902, we modified the budget amounts but the system didn’t update the timestamp field (it’s not a standard JDE field, must be custom).

So the ETL correctly ran, but its WHERE clause excluded our updated records because the timestamp was older than the last ETL run. How do we force the timestamp to update when forecast amounts change?