This Timecard details query displays timecard layout details for each employee by organization as of today, helping administrators manage and audit time and attendance records.
#sqlquery
SELECT
--SQL4Fusion (An Orbit Analytics Project)
--Displays employee timecard layouts and dates for attendance tracking and approvals.
HAOUT.NAME ORGANIZATION_NAME,
-- Employee Information
PAPF.PERSON_NUMBER EMPLOYEE_NUMBER,
PPNFV.FULL_NAME EMPLOYEE_NAME,
-- Timecard Details
HTM.TC_START_DATE TIMECARD_START_DATE,
HTM.TC_END_DATE TIMECARD_END_DATE,
HTM.BLD_BLK_TYPE BLOCK_TYPE,
HTM.UNIT_OF_MEASURE,
HTM.ATTRIBUTE_ALT_NAME10 REASON,
-- Identifiers
HTM.TM_HEADER_ID TIMECARD_HEADER_ID,
HTM.TM_MTRX_ROW_ID TIMECARD_ROW_ID
FROM
HR_ALL_ORGANIZATION_UNITS_TL HAOUT,
PER_PERSON_NAMES_F_V PPNFV,
PER_ALL_PEOPLE_F PAPF,
PER_ALL_ASSIGNMENTS_M PAAM,
HXT_TM_MTRX HTM
WHERE
PAPF.PERSON_ID = PAAM.PERSON_ID
AND PAPF.PERSON_ID = PPNFV.PERSON_ID
AND HTM.RESOURCE_ID = PPNFV.PERSON_ID
AND PAAM.ORGANIZATION_ID = HAOUT.ORGANIZATION_ID
AND HAOUT.LANGUAGE = USERENV('LANG')
AND TRUNC(SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN PPNFV.EFFECTIVE_START_DATE AND PPNFV.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN PAAM.EFFECTIVE_START_DATE AND PAAM.EFFECTIVE_END_DATE;