Our contract management reports in OTBI are displaying incorrect data compared to what we see when we open the actual contract records. Specifically, contract values, expiration dates, and renewal terms shown in the report don’t match what’s in the contract header. This is creating serious compliance issues as we rely on these reports for contract renewal notifications and spend analysis.
We’re on ofc-22d and this issue seems to affect about 30% of our contracts. The report data source appears to be pulling from the right tables, and our report filters are set to show all active contracts. When I drill down into specific contracts from the report and compare with the Contract Details page, the discrepancies are obvious. Has anyone experienced similar data integrity issues with contract reporting?
Excellent detective work! Let me provide a comprehensive solution addressing all three key areas:
Report Data Source Validation:
Your data source configuration needs careful review:
Verify you’re using the correct subject area: ‘Procurement - Contracts Real Time’ for current data
Check the underlying tables in your analysis:
POR_CONTRACT_HEADERS_ALL (header-level data)
POR_CONTRACT_LINES_ALL (line-level details)
POR_CONTRACT_TERMS_ALL (terms and conditions)
Ensure proper table relationships:
Use INNER JOIN between headers and lines only if you need line details
Use LEFT OUTER JOIN if you want to include contracts without lines
Always join on CONTRACT_ID and ensure version filtering is applied before the join
Add explicit version filtering in your data source:
Include condition: CONTRACT_HEADERS.CURRENT_VERSION_FLAG = ‘Y’
Or use: CONTRACT_HEADERS.VERSION_NUM = (SELECT MAX(VERSION_NUM) FROM POR_CONTRACT_VERSIONS WHERE CONTRACT_ID = CONTRACT_HEADERS.CONTRACT_ID)
Refresh schedule for real-time subject areas:
Navigate to Administration > Manage BI Publisher Reports
Check the cache refresh schedule for Procurement subject areas
Recommend: Set to refresh every 4 hours for contract data
For critical reports, use ‘Real Time’ mode which bypasses cache
Contract Details Accuracy:
To ensure you’re capturing accurate contract information:
Verify field mappings for key attributes:
Contract Value: Use CONTRACT_AMOUNT from headers, not sum of line amounts (which may include options)
Expiration Date: Use CONTRACT_END_DATE from current version only
Renewal Terms: Pull from CONTRACT_TERMS table with TERM_TYPE = ‘RENEWAL’
Handle amendments correctly:
Amendments create new versions with updated values
Your report must filter for CURRENT_VERSION_FLAG = ‘Y’
Historical reports should allow version selection as parameter
Address the 30% affected contracts:
These likely have multiple versions or complex line structures
Create a validation query:
SELECT ch.CONTRACT_NUMBER,
ch.CONTRACT_AMOUNT as HEADER_AMOUNT,
SUM(cl.LINE_AMOUNT) as TOTAL_LINE_AMOUNT,
COUNT(DISTINCT ch.VERSION_NUM) as VERSION_COUNT
FROM POR_CONTRACT_HEADERS_ALL ch
LEFT JOIN POR_CONTRACT_LINES_ALL cl ON ch.CONTRACT_ID = cl.CONTRACT_ID
WHERE ch.CURRENT_VERSION_FLAG = 'Y'
GROUP BY ch.CONTRACT_NUMBER, ch.CONTRACT_AMOUNT
HAVING ch.CONTRACT_AMOUNT != SUM(cl.LINE_AMOUNT)
This identifies contracts where header and line totals don’t match
Report Filters Configuration:
Correct filter setup is critical:
Status Filtering:
Use CONTRACT_STATUS_CODE at header level for overall contract status
Don’t mix header status with line status in the same filter
Effective Date Handling:
Add parameter: As of Date (defaults to CURRENT_DATE)
Filter: CONTRACT_START_DATE <= :AS_OF_DATE AND (CONTRACT_END_DATE >= :AS_OF_DATE OR CONTRACT_END_DATE IS NULL)
This ensures you see contracts active as of your reporting date
Version Control Filter:
Mandatory filter: CURRENT_VERSION_FLAG = ‘Y’
For historical analysis, add: VERSION_NUM parameter to allow specific version selection
Organization and Business Unit Filters:
Ensure you’re filtering by the correct ORG_ID
Contract visibility may be restricted by organization assignment
Aggregation Rules:
When including line details, always use proper GROUP BY:
GROUP BY
ch.CONTRACT_NUMBER,
ch.CONTRACT_AMOUNT,
ch.CONTRACT_START_DATE,
ch.CONTRACT_END_DATE,
ch.CONTRACT_STATUS_CODE
Use DISTINCT or MAX() for header-level attributes when lines are joined
Compliance and Monitoring:
To prevent future data quality issues:
Create a reconciliation report that runs weekly:
Compare report output with source transaction tables
Flag discrepancies for review
Alert procurement team of contracts approaching expiration
Implement data validation rules:
Contract amount must equal sum of active line amounts (within tolerance)
End date must be >= start date
Current version flag must exist for each contract
Schedule the ‘Contract Data Validation’ diagnostic report monthly
Document your report’s data model and filter logic for future reference
Implementing these corrections will resolve your data mismatch issues and provide reliable contract reporting for compliance and spend analysis purposes.
This draft is based on general Oracle Fusion Cloud knowledge. It has not been verified against your specific version and environment. Practitioners: verify the steps and share your experience below.
This sounds like a caching or refresh issue with the OTBI subject area. Contract data in Fusion is stored across multiple tables and the OTBI subject areas use materialized views that need periodic refresh. Check when your ‘Procurement - Contracts Real Time’ subject area was last refreshed. You might need to schedule more frequent refreshes or manually trigger one to see if the data synchronizes.
Also verify which version of the contract data your report is pulling. Contracts in Fusion maintain version history, and if your report isn’t explicitly filtering for the current version, it might be showing data from previous contract versions or amendments. Add a filter for CONTRACT_VERSION_NUM = (SELECT MAX(VERSION_NUM) FROM CONTRACT_VERSIONS WHERE CONTRACT_ID = BASE.CONTRACT_ID) or use the current version indicator flag if available.
The version history angle makes sense - we do have contracts with multiple amendments. I’ll check if the report is filtering for current versions. The subject area refresh timing could also be a factor.
Another possibility is that your report is pulling from the wrong contract line level. Contract headers, lines, and deliverables all have separate attributes. If your report joins multiple levels without proper aggregation, you might see duplicated or incorrect values. For example, if you’re summing contract line amounts without grouping by contract header, you’ll get inflated totals. Check your report’s table joins and aggregation logic carefully. Make sure you’re using SUM or MAX functions appropriately when rolling up line-level data to header level.
Don’t overlook the report filter configuration itself. In ofc-22d, the contract status filters can be tricky. A contract might be ‘Active’ at the header level but have lines in different statuses. If your filter is checking line status instead of header status, or vice versa, you’ll get inconsistent results. Also check if you’re filtering by effective dates - contracts might have future-dated amendments that affect what data appears depending on your report’s effective date parameter.
Confirmed this resolves our data mismatch by switching to the ‘Procurement - Contracts Real Time’ subject area and correcting the JOIN logic between POR_CONTRACT_HEADERS_ALL and POR_CONTRACT_LINES_ALL.
Found that we are indeed pulling multiple versions in some cases. The aggregation logic also seems problematic - we’re not properly grouping at the header level. This explains the inflated values we’re seeing.