ORACLE TECH CENTER: Query Absence in Oracle Fusion

SELECT distinct * FROM(

SELECT papf.person_id,

papf.person_number,

ppnf.display_name

employee_name,

pasf_Sup.manager_name

manager_name,

pjft.name

job_name,

pgft.name

grade_name,

--abt.absence_type_id,

abt.name

absence_type,

(SELECT DISTINCT( pd.name )

FROM per_departments pd

WHERE pd.organization_id = paam.organization_id

AND pd.effective_start_date =

(SELECT Max(pd1.effective_start_date)

FROM per_departments pd1

WHERE

pd1.organization_id = pd.organization_id

))

department_name,

paam.ass_attribute2 sector,

paam.ass_attribute1

unit,

To_char(apae.start_date, 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = AMERICAN')

start_date,

To_char(apae.end_date, 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = AMERICAN')

end_date,

apae.duration,

apae.start_date order_by_date,

apae.attribute1 Delegated_Person

FROM anc_per_abs_entries apae,

per_periods_of_service pps,

anc_absence_types_vl abt,

per_all_people_f papf,

per_person_names_f ppnf,

(SELECT ppnf2.full_name manager_name,

pasf.person_id

FROM per_assignment_supervisors_f pasf,

per_person_names_f ppnf2

WHERE pasf.manager_id = ppnf2.person_id

AND pasf.manager_type = 'LINE_MANAGER'

AND Trunc(SYSDATE) BETWEEN pasf.effective_start_date AND

pasf.effective_end_date

AND Trunc(SYSDATE) BETWEEN ppnf2.effective_start_date AND

ppnf2.effective_end_date

AND ppnf2.name_type = 'GLOBAL') pasf_Sup,

per_all_assignments_m paam,

per_jobs_f_vl pjft,

per_grades_f_vl pgft,

hr_organization_units bu,

hr_organization_units le,

per_asg_responsibilities resp

--hr_organization_units dept

WHERE apae.period_of_service_id = pps.period_of_service_id

AND apae.absence_type_id = abt.absence_type_id

AND apae.ABSENCE_STATUS_CD <> 'ORA_WITHDRAWN'

AND apae.APPROVAL_STATUS_CD = 'APPROVED'

AND abt.name = 'Tentative Leave Plan'

AND pps.person_id = papf.person_id

AND papf.person_id = ppnf.person_id

AND papf.person_id = pasf_Sup.person_id(+)

AND papf.person_id = paam.person_id(+)

AND paam.primary_assignment_flag(+) = 'Y'

--AND paam.assignment_type = 'E'

--AND paam.effective_latest_change = 'Y'

AND paam.job_id = pjft.job_id (+)

AND paam.grade_id = pgft.grade_id (+)

AND paam.business_unit_id = bu.organization_id

AND paam.legal_entity_id = le.organization_id

AND resp.person_id = HRC_SESSION_UTIL.get_user_personid

AND resp.responsibility_type ='HR_REP'

AND resp.status = 'Active'

AND resp.legal_entity_id = paam.legal_entity_id

--AND resp.BUSINESS_UNIT_ID=paam.BUSINESS_UNIT_ID

--AND resp.organization_id =paam.organization_id

AND TRUNC(sysdate) BETWEEN resp.start_date AND NVL(resp.end_date,TRUNC(sysdate))

AND Trunc(SYSDATE) BETWEEN pjft.effective_start_date(+) AND

pjft.effective_end_date(+)

AND Trunc(SYSDATE) BETWEEN pgft.effective_start_date(+) AND

pgft.effective_end_date(+)

AND Trunc(SYSDATE) BETWEEN paam.effective_start_date AND

paam.effective_end_date

AND Trunc(SYSDATE) BETWEEN abt.effective_start_date AND

abt.effective_end_date

AND Trunc(SYSDATE) BETWEEN papf.effective_start_date AND

papf.effective_end_date

AND Trunc(SYSDATE) BETWEEN ppnf.effective_start_date AND

ppnf.effective_end_date

AND ppnf.name_type = 'GLOBAL'

--AND papf.person_number IN ( '0015', '0033' )

AND to_number(Extract(year FROM apae.start_date)) = to_number(:p_date)

UNION