Machine Data

 SELECT 

         OTL.EMPLOYEE_ID ERP_NUMBER,

         PAPF.FULL_NAME  EMPLOYEE_NAME,

         TO_CHAR(OTL.ATTD_DATE, 'DD-MON-YYYY') ATTD_DATE,

--         OTL.ATTD_TIME,

         TO_CHAR(OTL.ATTD_TIME, 'HH24:MI:SS AM') ATTD_TIME,

--         (SELECT NG_ORGANIZATION_TYPE (PAAF.ORGANIZATION_ID)

--          FROM DUAL) ORG_TYPE,

--         (SELECT NG_ORGANIZATION_NAME (PAAF.ORGANIZATION_ID)

--          FROM DUAL) ORG_NAME,

         (SELECT NG_DESIGNATION (PAAF.POSITION_ID)

          FROM DUAL) DESIGNATION,

         REGEXP_SUBSTR (HAOU.NAME, '(\S*)(\.)') DEPARTMENT,

         HAOU.NAME UNIT_NAME

--         HAOU.ORGANIZATION_ID

FROM 

        PER_ALL_PEOPLE_F            PAPF 

       ,PER_ALL_ASSIGNMENTS_F       PAAF    

       ,TEMP_OTL_MACHINE_DATA       OTL

       ,HR_ALL_ORGANIZATION_UNITS   HAOU

WHERE 

     1 = 1

AND  PAAF.PERSON_ID       =     PAPF.PERSON_ID

AND  PAPF.EMPLOYEE_NUMBER =     OTL.EMPLOYEE_ID

AND SYSDATE BETWEEN PAAF.EFFECTIVE_START_DATE   AND PAAF.EFFECTIVE_END_DATE 

AND SYSDATE BETWEEN PAPF.EFFECTIVE_START_DATE   AND PAPF.EFFECTIVE_END_DATE 

AND PAAF.ORGANIZATION_ID    =   HAOU.ORGANIZATION_ID

--AND OTL.EMPLOYEE_ID       =   4328

AND OTL.UNIT_NAME = 'Gulshan Office Chairman House Unit 2'

AND OTL.ATTD_DATE between '06-jan-2021' and '06-jan-2021'

--AND OTL.ATTD_DATE = '10-dec-2020'

ORDER BY  DEPARTMENT, ERP_NUMBER