Introduction:
This SQL query is used to fetching the data of Employees Assignment changes like Manager change, location change 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 Assignment 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 Assignment changes details.
SQL Query:
SELECT
papf.person_number,
(
SELECT
to_char(ppos.date_start, ‘DD-MM-YYYY’)
FROM
per_periods_of_service ppos
WHERE
1 = 1
AND ppos.person_id = papf.person_id
AND ppos.period_type in (‘E’,’C’)
AND ppos.primary_flag = ‘Y’
) hire_date,
to_char((paam.last_update_date), ‘DD-MM-YYYY HH24:MI:SS’) updatetime,
paam.last_updated_by updatedby,
paam.ass_attribute3 profile,
to_char(paam.effective_start_date, ‘DD-MM-YYYY’) Effective_start_date,
to_char(paam.effective_end_date, ‘DD-MM-YYYY’) Effective_end_date,
(
SELECT
( manager.person_number )
FROM
per_all_people_f manager,
per_assignment_supervisors_f pasf
WHERE
papf.person_id = pasf.person_id
AND pasf.manager_type = ‘LINE_MANAGER’
AND manager.person_id = pasf.manager_id
AND paam.assignment_id = pasf.assignment_id
AND trunc(sysdate) BETWEEN trunc(manager.effective_start_date) AND trunc(manager.effective_end_date)
AND trunc(sysdate) BETWEEN trunc(pasf.effective_start_date) AND trunc(pasf.effective_end_date)
) reporting_authority,
decode (paam.assignment_type, ‘P’, ‘Pending Worker’, ‘E’, ‘Employee’,
‘C’, ‘Contingent Worker’, ‘N’, ‘Nonworker’, ‘O’,
‘Offer’) worker_type,
CASE
WHEN paam.full_part_time = ‘FULL_TIME’ THEN
‘FULL TIME’
WHEN paam.full_part_time = ‘PART_TIME’ THEN
‘PART TIME’
ELSE
NULL
END employmenttype,
paam.ass_attribute2 experiencecategory,
CASE
WHEN paam.employee_category = ‘WO’ THEN
‘Permanent’
WHEN paam.employee_category = ‘PR’ THEN
‘Probationer’
WHEN paam.employee_category IS NULL THEN
NULL
END employmentstatus,
haou.name buname,
haou.organization_id buid,
(
SELECT
location_name
FROM
per_location_details_f_vl
WHERE
location_id = paam.location_id
AND trunc(sysdate) BETWEEN trunc(nvl(effective_start_date(+), sysdate)) AND trunc(nvl(effective_end_date, sysdate))
) facility_location,
hl.location_id facilitylocationid,
hl.town_or_city facilitycity
FROM
per_all_people_f papf,
per_all_assignments_m paam,
hr_all_organization_units haou,
hr_locations hl
WHERE
trunc(sysdate) BETWEEN trunc(nvl(papf.effective_start_date, sysdate)) AND trunc(nvl(papf.effective_end_date, sysdate))
AND 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 haou.organization_id = paam.business_unit_id
AND paam.location_id = hl.location_id
AND EXISTS (
SELECT
1
FROM
per_assignment_status_types_tl pasttl
WHERE
pasttl.assignment_status_type_id = paam.assignment_status_type_id
AND pasttl.business_group_id = paam.business_group_id
AND language = userenv(‘LANG’)
AND upper(pasttl.user_status) IN (‘ACTIVE – PAYROLL ELIGIBLE’)
)
