Introduction:
This SQL query is used to fetching the data of Employees Work Schedule changes like Work Schedule id, Name etc… In this Last update date is used as a parameter so based on the parameter passing data will display.
Cause of the issue:
Business wants a report that contains work schedule changes of an employee from Fusion HCM module. So, business wants to create a custom report to pull the required data.
How do we solve:
Create a report in BI Publisher using below SQL query to extract the work schedule details.
SQL Query:
SELECT
papf.person_number,
to_char(greatest(paam.last_update_date,psa.last_update_date),’DD-MM-YYYY HH24:MI:SS’) updatedatetime,
psa.last_updated_by updatedby,
psa.schedule_id workscheduleid,
psa.primary_flag workscheduleprimary,
(
SELECT
k.schedule_name
FROM
zmm_sr_schedules_tl k
WHERE
k.schedule_id = psa.schedule_id
) work_schedule_name,
to_char(psa.start_date, ‘DD-MM-YYYY’) empschedulestartdate,
to_char(psa.end_date, ‘DD-MM-YYYY’) empscheduleenddate,
nvl((
SELECT
LISTAGG(to_char(pce.start_date_time, ‘YYYY-MM-DD’), ‘,’) WITHIN GROUP (
ORDER BY
start_date_time )
FROM
per_calendar_events pce,
per_calendar_events_tl pcet,
per_schedule_exceptions pse,
per_schedule_assignments psa,
zmm_sr_schedules_tl zss
WHERE
pce.calendar_event_id = pcet.calendar_event_id
AND pce.calendar_event_id = pse.exception_id
AND trunc(start_date_time) >= trunc(sysdate)
AND pse.schedule_id = zss.schedule_id
AND psa.resource_id = paam.assignment_id
AND psa.resource_type = ‘ASSIGN’
AND psa.schedule_id = zss.schedule_id
AND sysdate BETWEEN psa.start_date AND psa.end_date
),
(
SELECT
LISTAGG(to_char(pce.start_date_time, ‘YYYY-MM-DD’), ‘,’) WITHIN GROUP(
ORDER BY
start_date_time
)
FROM
per_calendar_events pce,
per_calendar_events_tl pcet
WHERE
pce.calendar_event_id = pcet.calendar_event_id
AND trunc(start_date_time) >= trunc(sysdate)
AND category IN(
SELECT
pse.exception_code
FROM
per_schedule_exceptions pse,
per_schedule_assignments psa1,
zmm_sr_schedules_tl zss
WHERE
1 = 1
AND pse.schedule_id = zss.schedule_id
AND psa1.resource_id = paam.assignment_id
AND psa1.schedule_id = zss.schedule_id
AND pse.exception_code IS NOT NULL
AND sysdate BETWEEN psa1.start_date AND psa1.end_date
)
)) calendarholidaydt,
(
SELECT
LISTAGG (nvl(in_pcec.override_name, pce.name), ‘,’) WITHIN GROUP (
ORDER BY
nvl(in_pcec.override_name, pce.name)
) Holiday
FROM
per_calendar_events_vl pce,
per_calendar_events_tl pcet,
per_cal_event_coverage in_pcec
WHERE
pce.calendar_event_id = pcet.calendar_event_id
AND trunc(start_date_time) >= trunc(sysdate)
AND pce.calendar_event_id = in_pcec.calendar_event_id
AND category IN (
SELECT
pse.exception_code
FROM
per_schedule_exceptions pse,
per_schedule_assignments psa1,
zmm_sr_schedules_tl zss
WHERE
1 = 1
AND pse.schedule_id = zss.schedule_id
AND psa1.resource_id = paam.assignment_id
AND psa1.schedule_id = zss.schedule_id
AND pse.exception_code IS NOT NULL
AND sysdate BETWEEN psa1.start_date AND psa1.end_date
)
) calendereventcategory
FROM
per_all_people_f papf,
per_all_assignments_m paam,
per_schedule_assignments psa,
per_cal_event_coverage in_pcec,
per_calendar_events_vl pce
WHERE
trunc(sysdate) BETWEEN trunc(nvl(papf.effective_start_date, sysdate)) AND trunc(nvl(papf.effective_end_date, sysdate))
AND ( psa.last_update_date >= to_date(:p_last_run_dt, ‘YYYY-MM-DD”T”HH24:MI:SS’)
OR paam.last_update_date >= to_date(:p_last_run_dt, ‘YYYY-MM-DD”T”HH24:MI:SS’))
AND paam.person_id = papf.person_id
AND trunc(sysdate) BETWEEN trunc(nvl(paam.effective_start_date(+), sysdate)) AND trunc(nvl(paam.effective_end_date, sysdate))
AND upper(paam.primary_flag(+)) = (‘Y’)
AND paam.assignment_type IN (
‘E’,
‘C’
)
AND paam.effective_latest_change = ‘Y’
AND psa.resource_id = paam.assignment_id
AND psa.resource_type = ‘ASSIGN’
AND trunc(sysdate) BETWEEN trunc(nvl(psa.start_date, sysdate)) AND trunc(nvl(psa.end_date, sysdate))
AND EXISTS (
SELECT
1
FROM
per_assignment_status_types_tl pastt
WHERE
pastt.assignment_status_type_id = paam.assignment_status_type_id
AND pastt.business_group_id = paam.business_group_id
AND language = userenv(‘LANG’)
AND upper(pastt.user_status) IN (‘ACTIVE – PAYROLL ELIGIBLE’)
)
