OTM BI Report Field Not Updating Despite Front-End and Backend Changes

Solving the mystery of outdated report values despite successful database updates

---

🩺 Symptoms

• Field updates (e.g., remarks/refnum) are visible in OTM's UI and confirmed in the backend (e.g., SHIPMENT_REFNUM or SHIPMENT_REMARK tables)

• Report output still displays outdated values even after regeneration

🧪 Steps to Reproduce

1. Update a shipment's remarks/refnum in the OTM front-end

2. Verify the update in the database via SQL query

3. Run/refresh the BI Publisher report – old value persists

🔍 Investigation

Report Logic Check

• Reviewed report XML/data source – no hardcoded filters or stale joins

Permissions Confirmed

• User roles have write/edit access to the field

Data Model Test

• Ran the report query directly in BI Publisher's Data Model tab – data updates correctly

• Identified discrepancy between live data and cached report output

⚙️ Root Cause

BI Publisher (BIP) Caching:

• The report template had "Enable Data Caching" turned on, causing it to serve stale data even after backend updates

• Caching settings were overriding real-time data pulls from OTM

💡 Solution

Disable BI Publisher Caching for Real-Time Data

1. Log in to Oracle BI Publisher

2. Navigate to your report's Format Template

3. Click Edit on the template

4. Go to Properties → Caching section

5. Adjust settings:

   • Set Enable Data Caching → True & set Caching Duration to 0 minutes

   • Uncheck:

     • User Level Caching

     • Document Caching

   • Check:

     • User Can Refresh Report Data

6. Click OK and save the template

✅ Results Achieved

• Report now reflects real-time updates from OTM/database

📘 Lessons Learned

• Cache ≠ Truth: BI Publisher caching can silently override live data – always validate cache settings for time-sensitive reports

• Test Beyond the Data Model: Even if the query works in isolation, report-level configurations (like caching) can break end-to-end functionality

🧠 Why This Matters

• Hidden Time Sink: Cache issues can waste hours if overlooked, as updates appear successful in UI/database

• Critical for Real-Time Reports: Supply chain visibility depends on accurate, up-to-date data – caching misconfigurations erode trust

🔍 Pro Tip

For reports requiring real-time data, use configuration shared in solution.

📌 Key Takeaway

Always ask: "Is this report cached?" before assuming the problem is in OTM.

---

Complexity Level: ⭐⭐ (Medium)

(Seemed simple but required tracing OTM-BIP integration layers.)

---

Tags: #OTMReportingGremlins #CacheIsKing #BIPublisherPitfalls