SQL Query to Fetch Day-Wise Timecard Details in Oracle Fusion HCM
Learn how to fetch day-wise timecard details in Oracle Fusion HCM using SQL. Complete query with examples for employee hours, time types, and payroll integration.
Managing employee timecards efficiently is one of the most important tasks in Oracle Fusion HCM Time and Labor. Whether you’re building a BI Publisher report, validating payroll hours, auditing time entries, or integrating data with external systems, having a reliable SQL query can save hours of effort.
This comprehensive guide explores a powerful SQL query that extracts day-wise employee timecard details directly from Oracle Fusion HCM tables with complete examples and optimization tips.
What You’ll Get From This Query
This SQL query retrieves comprehensive timecard information including:
✅ Employee Number and Details ✅ Timecard Date (Day-wise) ✅ Hours Worked Per Day ✅ Time Type Name ✅ Payroll Time Type ✅ Absence Type Details ✅ Legal Employer Information ✅ Legislative Data Group (LDG) ✅ Work Terms Assignment Number ✅ Processed Timecard Status
If you’re working on Oracle Fusion Time and Labor reporting, this query will be extremely useful for your projects and can significantly reduce development time.
Why This Query Is Critical
In Oracle Fusion HCM, timecard information is stored across multiple Workforce Management (HWM) tables. Extracting meaningful and user-friendly timecard details requires joining several complex tables together.
This SQL query helps you:
✅ Build accurate BI Publisher reports ✅ Audit employee time entries ✅ Validate payroll processing ✅ Analyze absence hours ✅ Create integrations with external payroll systems ✅ Fetch processed timecard details with complete details ✅ Reconcile time and labor data across the organization
Complete Production-Ready SQL Query
/******************************************************************************
RICEF ID : GCS_REPORT_004
REPORT NAME : REPORT TO FETCH DAY-WISE TIMECARD DETAILS
REPORT TYPE : BIP REPORT
DESCRIPTION : THIS REPORT WILL FETCH THE DAY-WISE TIMECARD DETAILS
FROM ORACLE FUSION.
CHANGE HISTORY:
NAME DATE VERSION COMMENTS
GrowCloudSkills 01-MAY-2026 V1.0 INITIAL VERSION
******************************************************************************/
WITH ATTRS AS (
SELECT A.TM_REC_ID,
MAX(CASE WHEN A.ATTRIBUTE_NAME = 'PayrollTimeType'
THEN A.ATTRIBUTE_VALUE END) AS PAYROLL_TIME_TYPE,
MAX(CASE WHEN A.ATTRIBUTE_NAME = 'AbsenceType'
THEN A.ATTRIBUTE_VALUE END) AS ABSENCE_TYPE,
MAX(CASE WHEN A.ATTRIBUTE_NAME = 'PJC_EXPENDITURE_TYPE_NAME'
THEN (A.ATTRIBUTE_VALUE) END) AS PJC_EXPENDITURE_TYPE_NAME
FROM HWM_TM_REP_RECORD_ATRBS_V A
WHERE A.ATTRIBUTE_NAME IN ('PJC_PROJECT_ID',
'PJC_EXPENDITURE_TYPE_NAME',
'PayrollTimeType',
'AbsenceType')
GROUP BY A.TM_REC_ID
)
SELECT
PAPF.PERSON_NUMBER EMP_NUMBER,
TO_CHAR(HTR.ACTUAL_DATE, 'MM/DD/YYYY') TIMECARD_ACTUAL_DATE,
HTR.MEASURE HOURS,
COALESCE((
SELECT MAX(HNVT.ALT_NAME_VALUE)
FROM HWM_TE_ALT_NAME_VALS_B HNVB,
HWM_TE_ALT_NAME_VALS_TL HNVT
WHERE HNVB.TE_ALT_NAME_VAL_ID = HNVT.TE_ALT_NAME_VAL_ID
AND HNVB.ATTRIBUTE2 = ATTRS.PJC_EXPENDITURE_TYPE_NAME
AND HNVT.LANGUAGE = 'US'
AND HNVT.SOURCE_LANG = 'US'
AND ROWNUM = 1
), (
SELECT MAX(HNVT.ALT_NAME_VALUE)
FROM HWM_TE_ALT_NAME_VALS_B HNVB,
HWM_TE_ALT_NAME_VALS_TL HNVT
WHERE HNVB.TE_ALT_NAME_VAL_ID = HNVT.TE_ALT_NAME_VAL_ID
AND TO_CHAR(HNVB.ATTRIBUTE3) = TO_CHAR(ATTRS.ABSENCE_TYPE)
AND HNVT.LANGUAGE = 'US'
AND HNVT.SOURCE_LANG = 'US'
AND ROWNUM = 1
), (
SELECT MAX(HNVT.ALT_NAME_VALUE)
FROM HWM_TE_ALT_NAME_VALS_B HNVB,
HWM_TE_ALT_NAME_VALS_TL HNVT
WHERE HNVB.TE_ALT_NAME_VAL_ID = HNVT.TE_ALT_NAME_VAL_ID
AND SUBSTR(HNVB.ATTRIBUTE1, INSTR(HNVB.ATTRIBUTE1, '_') + 1) = ATTRS.PAYROLL_TIME_TYPE
AND HNVT.LANGUAGE = 'US'
AND HNVT.SOURCE_LANG = 'US'
AND ROWNUM = 1
)) AS TIME_TYPE_NAME,
PLE.NAME LEGAL_ENTITY_NAME,
PLDGT.NAME LDG_NAME,
PAAM1.ASSIGNMENT_NUMBER WORK_TERM_NUMBER,
ATTRS.PAYROLL_TIME_TYPE,
ATTRS.ABSENCE_TYPE,
ATTRS.PJC_EXPENDITURE_TYPE_NAME
FROM PER_ALL_PEOPLE_F PAPF,
PER_PERSON_NAMES_F PPNF,
PER_ALL_ASSIGNMENTS_M PAAM,
PER_PERIODS_OF_SERVICE PPOS,
HWM_TM_REC HTR,
HWM_TM_REC_GRP_USAGES HTRGU,
HWM_TM_REC_GRP HTRG,
HWM_TM_REC_GRP HTRG1,
HWM_GRP_TYPE HGT,
HWM_TM_REC_GRP_SUM HTM,
ATTRS,
PER_LEGAL_EMPLOYERS PLE,
PER_LEGISLATIVE_DATA_GROUPS PLDG,
PER_LEGISLATIVE_DATA_GROUPS_TL PLDGT,
HR_ORGANIZATION_INFORMATION_F PSU_INF,
PER_ALL_ASSIGNMENTS_M PAAM1
WHERE 1=1
AND PAPF.PERSON_ID = PPNF.PERSON_ID
AND PPNF.NAME_TYPE = 'GLOBAL'
AND SYSDATE BETWEEN TRUNC(PAPF.EFFECTIVE_START_DATE) AND TRUNC(PAPF.EFFECTIVE_END_DATE)
AND SYSDATE BETWEEN TRUNC(PPNF.EFFECTIVE_START_DATE) AND TRUNC(PPNF.EFFECTIVE_END_DATE)
AND PAPF.PERSON_ID = PAAM.PERSON_ID
AND PAAM.ASSIGNMENT_STATUS_TYPE = 'ACTIVE'
AND PAAM.PRIMARY_ASSIGNMENT_FLAG = 'Y'
AND PAAM.EFFECTIVE_LATEST_CHANGE = 'Y'
AND SYSDATE BETWEEN TRUNC(PAAM.EFFECTIVE_START_DATE) AND TRUNC(PAAM.EFFECTIVE_END_DATE)
AND PAPF.PERSON_ID = PPOS.PERSON_ID
AND PAAM.PERIOD_OF_SERVICE_ID = PPOS.PERIOD_OF_SERVICE_ID
AND PPOS.PERIOD_TYPE = 'E'
AND PPOS.PRIMARY_FLAG = 'Y'
AND PAPF.PERSON_ID = HTR.RESOURCE_ID
AND HTR.DELETE_FLAG IS NULL
AND HTR.LATEST_VERSION = 'Y'
AND HTR.LAYER_CODE = 'TIME_RPTD'
AND UPPER(HTR.UNIT_OF_MEASURE) = UPPER('HR')
AND HTM.STATUS = 'APPROVED'
AND HTRGU.TM_REC_GRP_ID = HTRG.TM_REC_GRP_ID
AND HTRGU.TM_REC_GRP_VERSION = HTRG.TM_REC_GRP_VERSION
AND HTRG.PARENT_TM_REC_GRP_ID = HTRG1.TM_REC_GRP_ID
AND HTRG.PARENT_TM_REC_GRP_VERSION = HTRG1.TM_REC_GRP_VERSION
AND HTR.TM_REC_ID = HTRGU.TM_REC_ID
AND HTR.TM_REC_VERSION = HTRGU.TM_REC_VERSION
AND HTRG1.GRP_TYPE_ID = HGT.GRP_TYPE_ID
AND UPPER(HGT.NAME) = 'PROCESSED TIMECARD'
AND HTM.RESOURCE_ID = HTR.RESOURCE_ID
AND HTM.SUBRESOURCE_ID = HTR.SUBRESOURCE_ID
AND HTR.START_TIME BETWEEN HTM.START_TIME AND HTM.STOP_TIME
AND HTR.TM_REC_ID = ATTRS.TM_REC_ID
AND PAAM.LEGAL_ENTITY_ID = PLE.ORGANIZATION_ID
AND PLE.STATUS = 'A'
AND SYSDATE BETWEEN TRUNC(PLE.EFFECTIVE_START_DATE) AND TRUNC(PLE.EFFECTIVE_END_DATE)
AND PLDG.LEGISLATIVE_DATA_GROUP_ID = PLDGT.LEGISLATIVE_DATA_GROUP_ID
AND PLDGT.LANGUAGE = 'US'
AND PSU_INF.ORG_INFORMATION_CONTEXT = 'PER_PSU_PAYROLL_INFO'
AND PSU_INF.ORG_INFORMATION1 = PLDGT.LEGISLATIVE_DATA_GROUP_ID
AND PSU_INF.ORGANIZATION_ID = PLE.ORGANIZATION_ID
AND PAAM.WORK_TERMS_ASSIGNMENT_ID = PAAM1.ASSIGNMENT_ID
-- CUSTOMIZE THESE FILTERS FOR YOUR NEEDS
AND PAPF.PERSON_NUMBER = '1234'
AND TRUNC(HTR.ACTUAL_DATE) BETWEEN TO_DATE('01-01-2026','DD-MM-YYYY')
AND TO_DATE('01-02-2026','DD-MM-YYYY')
ORDER BY PAPF.PERSON_NUMBER, HTR.ACTUAL_DATE;
Query Customization Guide
1. Fetch Data for All Employees
Remove this line:
AND PAPF.PERSON_NUMBER = '1234'
This will return timecard data for all active employees.
2. Modify Date Filters
Adjust the date range for your reporting needs:
AND TRUNC(HTR.ACTUAL_DATE) BETWEEN TO_DATE('01-01-2026','DD-MM-YYYY')
AND TO_DATE('31-12-2026','DD-MM-YYYY')
Date filters are highly recommended for performance optimization.
3. Include Only Approved Timecards
This line is already in the query:
AND HTM.STATUS = 'APPROVED'
To include all statuses (including rejected/draft), remove this condition.
4. Add Legal Entity Filters
AND PLE.ORGANIZATION_ID = 123456
This restricts results to a specific legal employer.
Key Tables and Their Purpose
Understanding the tables used in this query helps with modifications and troubleshooting:
| Table Name | Purpose |
|---|---|
| HWM_TM_REC | Core time entry records with actual timecard data |
| HWM_TM_REP_RECORD_ATRBS_V | Timecard attributes (payroll type, absence type) |
| HWM_TM_REC_GRP | Timecard grouping and hierarchy |
| HWM_GRP_TYPE | Grouping type definitions (processed, submitted) |
| HWM_TM_REC_GRP_SUM | Timecard group summaries with status |
| PER_ALL_PEOPLE_F | Employee master data |
| PER_ALL_ASSIGNMENTS_M | Employee assignment details |
| PER_LEGAL_EMPLOYERS | Legal entity information |
| PER_LEGISLATIVE_DATA_GROUPS_TL | LDG names and translations |
Performance Optimization Tips
In large Oracle Fusion environments, timecard tables can contain millions of records. To ensure optimal performance:
✅ Always use date filters - Critical for large datasets ✅ Filter by specific employees - Avoid pulling unnecessary data ✅ Use bind variables - Improves query plan caching ✅ Include approval status filters - Reduces unnecessary joins ✅ Avoid unnecessary columns - Select only required fields ✅ Test queries in non-production - Validate before running on prod
Real-Time Business Use Cases
This query is commonly used for:
✅ Payroll reconciliation and validation ✅ Time and Labor reporting dashboards ✅ BI Publisher automated reports ✅ Oracle Fusion HCM integrations with external systems ✅ Employee attendance tracking and analysis ✅ Overtime and extra hours identification ✅ Absence hour calculations ✅ Project costing and resource allocation reports
FAQ: Common Questions About This Query
Which Oracle Fusion module is this for?
This query primarily uses:
- Oracle Fusion Time and Labor (OTL) - for timecard management
- Workforce Management (HWM) - backend tables storing time data
- Core HR - for employee and assignment information
Can this query be used in BI Publisher?
Yes, absolutely! This query is fully suitable for:
- BI Publisher Data Models
- OTBI custom SQL
- HCM Extract validations
- Custom Oracle Fusion reports
Just ensure proper parameter mapping for employee filters and date ranges.
What if I need to include rejected timecards?
Remove or modify this condition:
AND HTM.STATUS = 'APPROVED'
Replace with:
AND HTM.STATUS IN ('APPROVED', 'REJECTED', 'DRAFT')
How can I export results to Excel?
Use Oracle SQL*Plus, SQL Developer, or your BI tool’s export feature. The query returns standard Oracle result sets compatible with all export formats.
Conclusion
Extracting day-wise timecard details in Oracle Fusion HCM requires understanding the complex HWM data model. This production-ready SQL query simplifies the process by:
- Combining employee, assignment, and timecard data
- Extracting payroll and absence attributes
- Providing complete legal entity and LDG information
- Delivering processed timecard details only
Whether you’re building reports, validating data, or integrating with external systems, this query provides a solid foundation for Oracle Fusion Time and Labor projects.
Master this query and you’ll significantly improve your ability to troubleshoot, report, and manage timecard data in Oracle Fusion!
💡 Master Oracle Fusion Timecard Management
Learning to query timecard data effectively is essential for:
- Building accurate payroll reports
- Troubleshooting time entry issues
- Creating real-time dashboards
- Validating data integrity
- Improving user adoption
🚀 Keep Learning and Growing
- Subscribe to GrowCloudSkills for more advanced SQL queries and technical guides
- Follow us on LinkedIn for daily Oracle Fusion tips and best practices
- Watch our video tutorials on YouTube for visual, step-by-step learning
About GrowCloudSkills
GrowCloudSkills is your trusted partner for mastering Oracle Fusion Cloud Applications through:
✅ Production-ready SQL queries you can use immediately ✅ Real-world implementation examples from actual projects ✅ Complete step-by-step guides for complex technical topics ✅ Industry best practices based on years of consulting experience ✅ Supportive community of Oracle Fusion professionals
Whether you’re a junior consultant learning the foundations or a senior architect designing enterprise solutions, we’re committed to helping you succeed.
Connect With Us
Have questions about timecard queries or other Oracle Fusion topics? Drop a comment below or reach out on LinkedIn. We’d love to help you succeed in your Oracle Fusion journey!
Happy querying and master Oracle Fusion Time and Labor! 🚀