Covid-19 Reporting in Oracle HCM Cloud
Here is a list and pre-built reports to help Human Resources with COVID-19 Reporting with Oracle HCM Cloud. Hope this helps.
Oracle realizes that many of HCM Cloud users are busy responding to the current COVID-19 Coronavirus pandemic. Hope that this series of reports will provide relevant content designed to save you time.
Monitor Passport Expiry for Employees
Many organizations are trying to identify which employees have a passport that expires in the coming months. Here is a catalog file of a report that shows a list of those employees.
Steps to Use:
- Download the catalog file from this link
- Within the BI Catalog, locate the folder where you want to save the content, e.g. Shared Folders>Custom> YourFolder
- Unarchive the catalog file and you will see the new report
- Link to the original post on Customer Connect https://cloudcustomerconnect.oracle.com/posts/30396bfe04
Below is the Query to identify which employees have a passport that expires in the coming months.
SELECT
0 s_0,
"Workforce Management - Person Real Time"."Person Passport Details"."Country" s_1,
"Workforce Management - Person Real Time"."Person Passport Details"."Expiration Date" s_2,
"Workforce Management - Person Real Time"."Person Passport Details"."Issuing Authority" s_3,
"Workforce Management - Person Real Time"."Person Passport Details"."Issuing Country" s_4,
"Workforce Management - Person Real Time"."Worker"."Employee Display Name" s_5,
"Workforce Management - Person Real Time"."Worker"."Manager Name" s_6,
"Workforce Management - Person Real Time"."Worker"."Person Number" s_7,
DESCRIPTOR_IDOF("Workforce Management - Person Real Time"."Person Passport Details"."Country") s_8,
DESCRIPTOR_IDOF("Workforce Management - Person Real Time"."Person Passport Details"."Issuing Country") s_9,
TIMESTAMPDIFF(SQL_TSI_MONTH, CURRENT_DATE ,"Workforce Management - Person Real Time"."Person Passport Details"."Expiration Date") s_10,
COUNT(DISTINCT "Workforce Management - Person Real Time"."Worker"."Person ID") s_11,
REPORT_AGGREGATE(COUNT(DISTINCT "Workforce Management - Person Real Time"."Worker"."Person ID") BY ) s_12
FROM "Workforce Management - Person Real Time"
WHERE
(("Person Passport Details"."Expiration Date" IS NOT NULL) AND (TIMESTAMPDIFF(SQL_TSI_MONTH, CURRENT_DATE , "Person Passport Details"."Expiration Date") BETWEEN -7 AND 24) AND ("Worker"."Assignment Status Type Code" = 'ACTIVE'))
ORDER BY 1
FETCH FIRST 75001 ROWS ONLY
Query has been tested on Fusion 20A but applicable to earlier versions as well.
