Your issue is a combination of patch-related schema changes and data model configuration problems. Let me address each area systematically:
BI Publisher Data Model Alignment:
The 22D update introduced significant changes to HCM data architecture, particularly around Time and Labor. Oracle shifted from direct transactional table access to a replicated reporting model for better performance and scalability. Your custom data models need realignment.
First, verify which schema your data model is querying. In the BI Publisher data model SQL, check the table owner/schema prefix. If you’re querying FUSION schema tables directly (HWM_TM_TRANSACTIONS, HWM_TIME_CARD_DETAILS), you need to switch to the HCM_ANALYTICS schema equivalents. The new reporting tables follow a different naming pattern: HWM_TM_TRANS_F (fact table) and related dimension tables.
Update your data model to use these tables:
- HWM_TM_TRANS_F (replaces HWM_TM_TRANSACTIONS)
- HWM_TIME_CARD_DETAILS_F (replaces HWM_TIME_CARD_DETAILS)
- PER_PERSON_NAMES_F_V (for person information)
- HWM_TIME_CATEGORIES_VL (for time entry categories)
These reporting tables are optimized for analytics and include proper denormalization. They also have better indexing for common report queries. Modify your SQL to join through these structures rather than the operational tables.
Additionally, check if you’re using any deprecated columns. The 22D patch removed several columns and added new ones with different data types. Review the HCM Schema Changes document in My Oracle Support (Doc ID 2857563.1 for 22D specifics). Pay attention to date/timestamp column changes - some were converted from DATE to TIMESTAMP WITH TIME ZONE, which can cause join failures if not handled properly.
Patch Impact on Base Tables:
The 22D quarterly update implemented a new data replication framework for HCM reporting. This was done to separate operational transaction processing from reporting queries, improving overall system performance. However, it introduces a synchronization delay that you’re experiencing.
The replication process works through HCM Extract ESS jobs that run on a schedule. By default, these are configured to run weekly, which explains your 3-4 day delay. To verify the current schedule:
- Navigate to Scheduled Processes
- Search for ‘HCM Extract’ jobs
- Check the schedule for ‘Extract Time Card Data for Reporting’
- Verify the last successful run timestamp
You’ll likely find this job is scheduled weekly. Change it to run daily during off-peak hours (typically 2-4 AM). For near-real-time reporting requirements, you can schedule it every 6-8 hours, but be aware this increases system load.
Another impact of the 22D patch: Oracle introduced partitioning on the reporting tables by time period. If your custom SQL doesn’t include date range predicates, queries will scan all partitions, causing poor performance. Always include a WHERE clause with date filters, ideally using bind variables for the reporting period.
Custom SQL Validation:
Your existing custom SQL needs comprehensive validation and likely rewriting. Here’s a systematic approach:
-
Test table accessibility: Verify your BI Publisher data source can access the new reporting schema. Some implementations have separate database users for BI Publisher with limited grants. Ensure the reporting schema tables are granted to your BI Publisher data source user.
-
Validate join conditions: The new reporting tables use different key structures. Old joins based on transaction IDs might not work. Use the Oracle-delivered HCM subject areas in OTBI as reference for proper join patterns. Even if you’re not using OTBI, the logical SQL in those subject areas shows the correct relationships.
-
Check for missing data: After updating your SQL to use reporting tables, compare record counts between the operational and reporting schemas. If there are significant discrepancies beyond the expected replication lag, investigate whether the HCM Extract job is completing successfully or encountering errors.
-
Review filter logic: Time zone handling changed in 22D. If your reports filter by submission date or approval date, ensure you’re converting timestamps to the appropriate time zone. Use FROM_TZ() and AT TIME ZONE functions to handle this correctly.
-
Update aggregation queries: If your custom reports perform aggregations (SUM, COUNT, etc.), verify that the new reporting tables don’t already provide pre-aggregated data. The HWM_TM_TRANS_F table includes measure columns that might eliminate the need for runtime aggregation.
Implement these validation steps in your development environment first. Create parallel versions of your reports - one using the old table structure and one using the new - and compare results for a known time period. This will help identify any data transformation issues introduced by the schema changes.
For immediate remediation while you update your data models, you can temporarily increase the HCM Extract job frequency to daily. This will reduce the sync delay to 24 hours maximum. However, the long-term solution is updating all custom SQL to use the proper reporting schema as Oracle designed it in 22D and beyond.
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.