SELECT pf.person_number emp_num,
	pn.first_name || (' ') || middle_names || (' ') || pn.last_name emp_name,
	dep_en.name department,
	pni.national_identifier_number  national_id,
	TO_CHAR(PPOS.DATE_START,'DD-MM-YY', 'nls_date_language=english') hire_d,
	hrl.meaning "nationality",
	hpt_en.name "position",
	pls.internal_location_code "region",
	TRUNC(MONTHS_BETWEEN(SYSDATE,pf.start_date)/12) ||' years, '||
    TRUNC(MOD(MONTHS_BETWEEN(SYSDATE,pf.start_date),12)) ||' months, '||
	TRUNC(SYSDATE-ADD_MONTHS(pf.start_date,TRUNC(MONTHS_BETWEEN(SYSDATE,pf.start_date)/12)*12+TRUNC(MOD(MONTHS_BETWEEN(SYSDATE,pf.start_date),12)))) ||' days' service_p,
	cs.salary_amount salary,
	aat_en.name abs_type,
	(SELECT COUNT(apaee.per_absence_entry_id) occur
	
	FROM 
		anc_per_abs_entries apaee
	
	WHERE 1=1
		
		AND apaee.person_id = apae.person_id
		AND apae.absence_type_id = apaee.absence_type_id
		AND apaee.approval_status_cd = 'APPROVED'
		AND apaee.absence_status_cd = 'SUBMITTED'
		AND apaee.start_date BETWEEN :p_from AND :p_to
		AND apaee.end_date BETWEEN :p_from AND :p_to
	) "occurrence",
	TO_CHAR(apae.start_date,'DD-MM-YY', 'nls_date_language=english')  start_d,
	TO_CHAR(apae.end_date,'DD-MM-YY', 'nls_date_language=english')  end_d,
	apae.duration || CASE WHEN UPPER(aat_en.name) LIKE '%HOUR%' THEN ' Hours' ELSE ' Days' END "duration"
	
	
FROM per_person_secured_list_v pf,
	per_person_names_f pn,
	per_all_assignments_m paa, 
	per_department_secured_list_v hou,
	hr_all_organization_units_vl dep_en,
	per_national_identifiers pni,
	per_citizenships cit,
	hr_lookups hrl,
	per_position_secured_list_v hp,
	hr_all_positions_f_vl hpt_en ,
	per_location_secured_list_v pls,
	cmp_salary_secured_list_v cs,
	anc_per_abs_entries apae,
    per_periods_of_service pps,
	anc_absence_types_f aat,
	anc_absence_types_vl aat_en,
	PER_PERIODS_OF_SERVICE PPOS
	
WHERE 1=1
	AND pf.person_id=pn.person_id
	AND pn.name_type='GLOBAL' 
	AND pf.person_id=paa.person_id 
	AND paa.primary_flag = 'Y'
	AND paa.organization_id=hou.organization_id(+)
	AND hou.organization_id = dep_en.organization_id(+)
	AND hou.effective_start_date = dep_en.effective_start_date(+)
	AND hou.effective_end_date = dep_en.effective_end_date(+)
	AND pf.person_id=pni.person_id(+)
	AND pf.primary_nid_id=pni.national_identifier_id(+)
	AND pf.person_id=cit.person_id(+)
	AND hrl.lookup_code(+)=cit.legislation_code
	AND hrl.lookup_type (+) = 'NATIONALITY' 
	AND paa.position_id = hp.position_id(+)
	AND hp.effective_start_date = hpt_en.effective_start_date(+)
	AND hp.effective_end_date = hpt_en.effective_end_date(+)
	AND hp.position_id = hpt_en.position_id(+)
	AND paa.location_id = pls.location_id(+)
	AND pf.person_id = cs.person_id(+)
	AND paa.assignment_id=cs.assignment_id(+)
	AND apae.person_id = pf.person_id
	AND apae.period_of_service_id = pps.period_of_service_id
    AND apae.absence_type_id = aat.absence_type_id
	AND apae.approval_status_cd = 'APPROVED'
	AND apae.absence_status_cd	='SUBMITTED'
	AND aat.absence_type_id = aat_en.absence_type_id(+)
	AND PAA.PERIOD_OF_SERVICE_ID=PPOS.PERIOD_OF_SERVICE_ID
	
	AND TRUNC(SYSDATE) BETWEEN pf.effective_start_date AND pf.effective_end_date
	AND TRUNC(SYSDATE) BETWEEN pn.effective_start_date AND pn.effective_end_date
	AND TRUNC(SYSDATE) BETWEEN paa.effective_start_date AND paa.effective_end_date
	AND TRUNC(SYSDATE) BETWEEN hou.effective_start_date(+) AND hou.effective_end_date(+)
	AND TRUNC(SYSDATE) BETWEEN hp.effective_start_date (+) AND hp.effective_end_date(+)
	AND TRUNC(SYSDATE) BETWEEN cs.date_from(+) AND cs.date_to(+)
	AND TRUNC(SYSDATE) BETWEEN aat.effective_start_date AND aat.effective_end_date
	AND TRUNC(SYSDATE) BETWEEN aat_en.effective_start_date AND aat_en.effective_end_date
	
	AND ((paa.legal_entity_id IN (:p_legal_id)) OR (COALESCE(:p_legal_id,NULL) IS NULL))
	AND ((paa.business_unit_id IN (:p_business_id)) OR (COALESCE(:p_business_id,NULL) IS NULL))
	AND ((paa.organization_id IN (:p_department_id)) OR (COALESCE(:p_department_id,NULL) IS NULL))
	AND ((apae.absence_type_id IN (:p_absence_id)) OR (COALESCE(:p_absence_id,NULL) IS NULL))
	AND apae.start_date BETWEEN :p_from AND :p_to
	AND apae.end_date BETWEEN :p_from AND :p_to
	AND ((hrl.lookup_code IN (:p_nationality_id)) OR (COALESCE(:p_nationality_id,NULL) IS NULL))
	AND ((hp.position_id IN (:p_position_id) )OR (COALESCE(:p_position_id,NULL) IS NULL))
	AND ((pls.internal_location_code IN (:p_region)) OR (COALESCE(:p_region,NULL) IS NULL))
	AND ((pf.person_number IN (:p_person_number )) OR (COALESCE(:p_person_number , NULL) IS NULL))

ORDER BY pf.person_number