Query to Extract Absence and Long Leave details in Oracle Fusion HCM
Query:
SELECT empl_leave_trxn_id,
employee_id,
leave_name,
leave_type_id,
To_char(start_datetime, ‘DD-MON-YYYY’) from_date,
To_char(end_datetime, ‘DD-MON-YYYY’) to_date
– ,CASE WHEN absence_type =’Permission Leave’ THEN TO_CHAR(START_DATETIME, ’DD-MON-YYYY HH24:MI:SS’) ELSE from_date END from_date
– ,CASE WHEN absence_type =’Permission Leave’ THEN TO_CHAR(END_DATETIME, ’DD-MON-YYYY HH24:MI:SS’) ELSE TO_date END to_date
,
absence_type
–,CASE WHEN absence_type !=’Permission Leave’ THEN days ELSE NULL END days
,
CASE
WHEN absence_type NOT LIKE ‘Permission%’ THEN days
ELSE NULL
END days
– ,reason
– ,CASE WHEN absence_type =’Permission Leave’ THEN (days *60) ELSE NULL END permission_minutes
– ,CASE WHEN absence_type LIKE ’Permission%’ THEN (days *60) ELSE NULL END permission_minutes
,
CASE
WHEN absence_type LIKE ‘Permission%’ THEN Round(( days * 60 ), 0)
ELSE NULL
END permission_minutes,
reason,
transaction_date,
comments_and_description,
CASE
WHEN absence_status_cd = ‘ORA_WITHDRAWN’ THEN ‘WITHDRAWN’
ELSE leave_status
END leave_Status,
applied_by,
CASE
WHEN leave_status IN ( ‘APPROVED’, ‘DENIED’ ) THEN
(SELECT comm.wfcomment
FROM
fa_fusion_soainfra.wfcomments_view comm,
fa_fusion_soainfra.wftask task
WHERE 1 = 1
AND
comm.taskid = task.taskid
AND
task.identificationkey = To_char(empl_leave_trxn_id)
–‘300000023933205’
AND rownum < 2
AND
comm.version = (SELECT Max(comm.version)
FROM fa_fusion_soainfra.wfcomments_view comm,
fa_fusion_soainfra.wftask task
WHERE 1 = 1
AND comm.taskid = task.taskid
AND task.identificationkey = To_char(
empl_leave_trxn_id)
–‘300000023933205’
))
ELSE NULL
END Approver_Comments,
CASE
WHEN leave_status IN ( ‘APPROVED’, ‘DENIED’ ) THEN
Nvl((SELECT Upper(task.approvers) –UPPER(comm.UPDATEDBY)
FROM fa_fusion_soainfra.wftask task
– ,fa_fusion_soainfra.wfcomments_view comm,
WHERE 1 = 1
–AND comm.TASKID=task.TASKID
AND task.identificationkey = To_char(empl_leave_trxn_id)
–‘300000023933205’
–AND comm.VERSION =
AND rownum < 2
AND task.version = (SELECT Max(task.version) –MAX(comm.VERSION)
FROM fa_fusion_soainfra.wftask task
–,fa_fusion_soainfra.wfcomments_view comm,
WHERE 1 = 1
– AND comm.TASKID=task.TASKID
AND task.identificationkey = To_char(
empl_leave_trxn_id)
–‘300000023933205’
)), approved_by)
ELSE NULL
END Approved_By,
CASE
WHEN leave_status IN ( ‘APPROVED’, ‘DENIED’ ) THEN
Nvl((SELECT To_char(comm.commentdate, ‘DD-MON-YYYY HH24:MI:SS’)
FROM fa_fusion_soainfra.wfcomments_view comm,
fa_fusion_soainfra.wftask task
WHERE 1 = 1
AND comm.taskid = task.taskid
AND task.identificationkey = To_char(empl_leave_trxn_id)
–‘300000023933205’
AND rownum < 2
AND comm.version = (SELECT Max(comm.version)
FROM fa_fusion_soainfra.wfcomments_view comm
,
fa_fusion_soainfra.wftask task
WHERE 1 = 1
AND comm.taskid = task.taskid
AND task.identificationkey = To_char(
empl_leave_trxn_id)
–‘300000023933205’
)), approved_date)
ELSE NULL
END Approved_Date
— ,CASE WHEN LEAVE_STATUS IN (‘APPROVED’,’DENIED’) THEN NULL ELSE NULL END Approver_Comments
— ,CASE WHEN LEAVE_STATUS IN (‘APPROVED’,’DENIED’) THEN APPROVED_BY ELSE NULL END Approved_By
— ,CASE WHEN LEAVE_STATUS IN (‘APPROVED’,’DENIED’) THEN Approved_Date ELSE NULL END Approved_Date
,
absence_status_cd,
long_leave_dt_from
– ,Long_leave_dt_to
,
long_leave_comments,
long_leave_creation_dt,
long_leave_created_by
FROM (SELECT apae.per_absence_entry_id
Empl_Leave_Trxn_ID,
per.person_number
Employee_ID,
abs.absence_type_id
leave_type_id,
abs.NAME
absence_type,
To_char(apae.start_date, ‘DD-MON-YYYY’)
from_date –,’nls_date_language = american’) from_date
,
To_char(apae.end_date, ‘DD-MON-YYYY’)
to_date –,’nls_date_language = american’) to_date
,
duration
days,
(SELECT absence_reason_id
FROM anc_absence_type_reasons_f abtr
WHERE 1 = 1
AND abtr.absence_type_reason_id
= apae.absence_type_reason_id)
reason,
NULL
permission_minutes
–,null transactionmode
,
To_char(apae.creation_date, ‘DD-MON-YYYY HH24:MI:SS’)
transaction_date,
apae.comments
comments_and_description,
apae.approval_status_cd
leave_Status,
apae.created_by
Applied_By,
NULL
Approver_Comments,
apae.last_updated_by
Approved_By,
To_char(apae.last_update_date, ‘DD-MON-YYYY HH24:MI:SS’)
Approved_Date,
start_datetime,
end_datetime,
absence_status_cd –
,
NULL
leave_name,
NULL
Long_leave_dt_from
– ,NULL Long_leave_dt_to
,
NULL
Long_leave_comments,
NULL
Long_leave_creation_dt,
NULL
Long_leave_created_by
FROM anc_per_abs_entries apae,
per_periods_of_service pps,
per_all_people_f per,
anc_absence_types_vl abs,—
(SELECT hao.organization_id
FROM hr_all_organization_units_f hao,
hr_organization_units_f_tl haot
WHERE hao.organization_id = haot.organization_id
AND hao.effective_start_date = haot.effective_start_date
AND hao.effective_end_date = haot.effective_end_date
AND Trunc(sysdate) BETWEEN hao.effective_start_date AND
hao.effective_end_date
AND haot.language = ‘US’
AND haot.NAME = XX Health Private Limited’
AND organization_code = ‘XX001’)ind_emp
WHERE 1 = 1
AND apae.period_of_service_id = pps.period_of_service_id
AND apae.absence_type_id = abs.absence_type_id
AND pps.person_id = per.person_id
AND Trunc(sysdate) BETWEEN abs.effective_start_date AND
abs.effective_end_date
AND Trunc(sysdate) BETWEEN per.effective_start_date AND
per.effective_end_date
– and per.person_number IN (‘101071’)– (‘AR06947’)
AND To_char (apae.creation_date, ‘YYYY-MM-DD’) >= ‘2022-07-20’
– Added CUT OFF DATE logic as per Padhu on 25July22
AND ind_emp.organization_id = apae.legal_entity_id
AND absence_status_cd <> ‘SAVED’
–AND ABSENCE_STATUS_CD NOT IN (‘SAVED’,’ORA_WITHDRAWN’)
–AND apae.comments NOT IN (‘Migrated Absence Entry’,’As per HR request’)
AND Nvl(apae.comments, ‘X’) NOT IN (
‘Migrated Absence Entry’, ‘As per HR request’ )
–AND to_char(apae.start_date,’DD-MM-YYYY’ ) >’20-07-2022′
AND To_char(apae.start_date, ‘YYYY-MM-DD’) > ‘2022-07-20’
–AND (apae.last_update_date )>= TO_DATE (:p_last_run_dt, ’YYYY-MM-DD”T”HH24:MI:SS’)
AND ( ( apae.creation_date ) >=
To_date (:p_last_run_dt, ‘YYYY-MM-DD”T”HH24:MI:SS’)
OR ( apae.last_update_date ) >=
To_date (:p_last_run_dt, ‘YYYY-MM-DD”T”HH24:MI:SS’) )
–OR (abs.creation_date)>= TO_DATE (:p_last_run_dt, ’YYYY-MM-DD”T”HH24:MI:SS’) OR (abs.last_update_date )>= TO_DATE (:p_last_run_dt, ’YYYY-MM-DD”T”HH24:MI:SS’))
UNION
/*SELECT
NULL Empl_Leave_Trxn_ID
,(select person_number from per_all_people_f where person_id=doc.person_id) Employee_ID
,NULL leave_type_id
,NULL absence_type
,NULL from_date
,NULL to_date
,NULL days
,NULL reason
,NULL permission_minutes
,NULL transaction_date
,NULL comments_and_description
,NULL leave_Status
,NULL Applied_By
,NULL Approver_Comments
,NULL Approved_By
,NULL Approved_Date
,NULL START_DATETIME
,NULL END_DATETIME
,NULL absence_status_cd
–,DOCUMENT_NAME leave_name
,’Long Leave’ leave_name
,to_char(doc.DATE_FROM,’DD-MON-YYYY’) Long_leave_dt_from
– ,to_char(doc.DATE_TO,’DD-MON-YYYY’) Long_leave_dt_to
,COMMENTS Long_leave_comments
,to_char(doc.creation_date,’DD-MON-YYYY HH24:MI:SS’) Long_leave_creation_dt
,doc.CREATED_BY Long_leave_created_by
FROM hr_documents_of_record doc
WHERE 1=1
–AND UPPER(doc.document_name) = ’LONG LEAVE’
AND TO_CHAR (doc.creation_date, ’YYYY-MM-DD’) >= ’2022-07-20’ – Added CUT OFF DATE logic as per Padhu on 25July22
AND document_type_id in (SELECT document_type_id
FROM hr_document_types_tl
WHERE 1=1
AND DOCUMENT_TYPE = ’Long Leave Application’)
–AND TRUNC(doc.creation_date)=TRUNC(SYSDATE) */
SELECT NULL
Empl_Leave_Trxn_ID,
(SELECT person_number
FROM per_all_people_f
WHERE
person_id =
Extractvalue(txnh.xml_data_cache, ‘/TRANSACTION/BUSINESSDATA/AM/TXN/EO/DocumentsOfRecordEORow/PersonId/DATA’)) person_id,
NULL
leave_type_id,
NULL
absence_type,
NULL
from_date,
NULL
to_date,
NULL
days,
NULL
reason,
NULL
permission_minutes,
NULL
transaction_date,
NULL
comments_and_description,
NULL
leave_Status,
NULL
Applied_By,
NULL
Approver_Comments,
NULL
Approved_By,
NULL
Approved_Date,
NULL
START_DATETIME,
NULL
END_DATETIME,
NULL
absence_status_cd
–,DOCUMENT_NAME leave_name
,
‘Long Leave’
leave_name
–,to_char(doc.DATE_FROM,’DD-MON-YYYY’) Long_leave_dt_from
– ,to_char(doc.DATE_TO,’DD-MON-YYYY’) Long_leave_dt_to
,
Extractvalue(txnh.xml_data_cache, ‘/TRANSACTION/BUSINESSDATA/AM/TXN/EO/DocumentsOfRecordEORow/DateFrom/DATA’) Long_leave_dt_from
,
Extractvalue(txnh.xml_data_cache, ‘/TRANSACTION/BUSINESSDATA/AM/TXN/EO/DocumentsOfRecordEORow/Comments/DATA’) Long_leave_comments,
To_char(txnh.creation_date, ‘DD-MON-YYYY HH24:MI:SS’)
Long_leave_creation_dt,
txnh.created_by
Long_leave_created_by
FROM fusion.hrc_txn_console_entry txnc,
fusion.hrc_txn_header txnh,
fusion.hrc_txn_data txnd
WHERE 1 = 1
AND txnh.transaction_id = txnd.transaction_id
AND txnd.transaction_id = txnc.transaction_id
AND txnc.object_name LIKE ‘IN%LONG%LEAVE%’
AND txnd.status IN ( ‘PENDING’, ‘APPROVED’ )
AND ( txnh.creation_date ) >=
To_date (:p_last_run_dt, ‘YYYY-MM-DD”T”HH24:MI:SS’))
ORDER BY employee_id
