WITH payroll AS(
	SELECT 
	pf.person_id,
	CASE WHEN  UPPER(piv.name)='VACATION TRANSACTIONS'
		THEN ABS(TO_NUMBER(prrv.result_value))
		ELSE TO_NUMBER(prrv.result_value) END  result_value,
	payrel.assignment_id
	
	FROM
		per_person_secured_list_v pf,	
		(SELECT PAA1.*,
		RANK() OVER (PARTITION BY paa1.PERSON_ID ORDER BY paa1.EFFECTIVE_START_DATE DESC) ASSIGN_RANK
		FROM per_all_assignments_f paa1
		WHERE 1=1
                AND paa1.SYSTEM_PERSON_TYPE = 'EMP'
		AND paa1.assignment_status_type = 'ACTIVE')paa,
		pay_pay_relationships_dn prel,
		pay_rel_groups_dn payrel,
		pay_assigned_payrolls_dn papd,
		pay_payroll_secured_list_v pp,
		pay_payroll_actions ppa,
		pay_payroll_rel_actions ppra,
		pay_run_results prr,
		pay_input_values_vl piv,
		pay_run_result_values prrv,
		pay_element_types_vl ele,
		pay_time_periods ptp,
		pay_ele_classifications_vl pec

	WHERE 1=1
AND paa.ASSIGN_RANK=1
--AND pf.PERSON_ID='300000010666308'
		AND pf.person_id=paa.person_id 
		--AND paa.primary_flag = 'Y'
		--AND paa.assignment_status_type = 'ACTIVE'
		AND pf.person_id=prel.person_id
		AND prel.payroll_relationship_id = payrel.payroll_relationship_id
		AND prel.payroll_relationship_id = ppra.payroll_relationship_id
		AND payrel.parent_rel_group_id = papd.payroll_term_id
		AND payrel.assignment_id = paa.assignment_id
		AND papd.payroll_id = pp.payroll_id
		AND papd.payroll_id = ppa.payroll_id
		AND ppa.payroll_action_id = ppra.payroll_action_id
		AND ppra.payroll_rel_action_id = prr.payroll_rel_action_id
		AND prr.run_result_id = prrv.run_result_id
		AND prr.enterprise_id = prrv.enterprise_id
		AND prr.element_type_id = ele.element_type_id
		AND prr.element_type_id = piv.element_type_id
		AND ptp.time_period_id = ppa.earn_time_period_id --payroll period for employee action
		AND ptp.period_category = 'E' --earning period category
		AND piv.input_value_id = prrv.input_value_id --input value is a out put parameter for each element default is pay value
		AND UPPER(ele.element_name)='ANNUAL LEAVE PROVISION'
		AND UPPER(piv.name) IN ('ENDING ACCRUAL BALANCE','VACATION TRANSACTIONS')
		AND UPPER(pec.classification_name) ='INFORMATION'
		AND ppra.action_status = 'C' --payroll run completed
		AND ppa.action_type in ('R', 'Q') --types of payroll run(payroll calculation r, quick pay q)
		AND ppa.action_status = 'C' --payroll run completed
		AND ppra.retro_component_id IS NULL --retro payment
		AND ele.classification_id = pec.classification_id
		AND payrel.group_type = 'A'
		
		AND ppa.effective_date  BETWEEN  ptp.start_date AND  ptp.end_date
		AND  ppa.effective_date BETWEEN TRUNC(:p_calculation_date, 'MM') AND LAST_DAY(:p_calculation_date)
			
		AND ppa.effective_date BETWEEN pf.effective_start_date AND LAST_DAY(pf.effective_end_date)
		AND ppa.effective_date BETWEEN prel.start_date AND prel.end_date
		AND ppa.effective_date BETWEEN payrel.start_date AND payrel.end_date
		AND ppa.effective_date BETWEEN papd.start_date AND papd.end_date
		AND ppa.effective_date BETWEEN pp.effective_start_date AND pp.effective_end_date
		AND ppa.effective_date BETWEEN piv.effective_start_date AND piv.effective_end_date
		AND ppa.effective_date BETWEEN ele.effective_start_date AND ele.effective_end_date
)
SELECT papf.person_number,
       papf.person_id                                      person,
       ppnf.display_name                                   Employee_Name,
       bu.name                                             Business_unit,
       dept.name                                           Department,
       pptvl.user_person_type                              Person_Type,
       asg_act.assignment_status_type                      Assignment_Status,
       To_char(ppos.date_start, 'DD/MM/YYYY')              Hire_date,
       To_char(ppos.actual_termination_date, 'DD/MM/YYYY') Termination_date,
       aapf.name                                           Plan_Name,
	   aapf.absence_plan_id,
       To_char(:p_calculation_date, 'DD/MM/YYYY')
       Last_Balance_Calculation_Period,
	   MAX(apaed.procd_date) 							  Procd_date,
       payroll_name.payroll_name ,
	   assignment_category.c1 assignment_category,asg_act.assignment_id
	   ,CASE WHEN aapf.name='Annual Leave' 
	   THEN NVL((SELECT SUM(PAYROLL.RESULT_VALUE)
	   FROM PAYROLL
	   WHERE  papf.person_id=PAYROLL.person_id 
		AND asg_act.assignment_id=PAYROLL.assignment_id
	   ),0) 
		ELSE  0 END RESULT_VALUE

FROM   per_person_secured_list_v papf,--changed to Secured View used on 25/08/2022
       per_person_names_f ppnf,
       per_all_assignments_f asg_act,
       hr_organization_units bu,
       hr_organization_units dept,
       per_person_types_vl pptvl,
       per_periods_of_service ppos,
       anc_per_acrl_entry_dtls apaed,
       anc_absence_plans_f_tl aapf,
       anc_per_abs_entries apae,
	   anc_per_plan_enrollment appe,
       per_position_secured_list_v hap,
       --changed to Secured View used on 25/08/2022 
       per_job_secured_list_v pj,---changed to Secured View used on 25/08/2022 
       per_location_secured_list_v pldf,
       ---changed to Secured View used on 25/08/2022 
       per_grade_secured_list_v pg,
       ---changed to Secured View used on 25/08/2022 
       hr_organization_units hou,
	   ---------------------assignment_category----------
	   (SELECT T492958.c61309499 AS c1,
                T492958.c36361321 AS c2
         FROM   (SELECT V483283710.meaning     AS C61309499,
                        V483283710.lookup_code AS C36361321,
                        V483283710.lookup_type AS C319008066
                 FROM   hcm_lookups V483283710
                 WHERE  ((( V483283710.lookup_type = 'EMP_CAT' )))) T492958)assignment_category,
	   ---------------------PAyroll Name COde-------------
       (SELECT  pay_rule.payroll_name,
                        papf.person_id
		FROM   per_all_assignments_f paaf,
               pay_assigned_payrolls_dn papd,
               pay_rel_groups_dn prgd,
               pay_all_payrolls_f pay_rule,
               per_person_secured_list_v papf
        WHERE  papd.payroll_id = pay_rule.payroll_id
               AND paaf.assignment_id = prgd.assignment_id
               AND papd.payroll_term_id = prgd.parent_rel_group_id
               AND papf.person_id = paaf.person_id
               AND Upper (Trim (prgd.group_type(+))) = Upper (Trim ('A'))
               --active group
               AND Upper (paaf.primary_flag) = Upper ('Y')
               AND Upper (paaf.effective_latest_change) = Upper ('Y')
               AND Trunc (SYSDATE) BETWEEN Trunc (pay_rule.effective_start_date)
                                           AND
                                           Trunc (
                                           pay_rule.effective_end_date)
               AND Trunc (SYSDATE) BETWEEN Trunc (prgd.start_date) AND
                                           Trunc (prgd.end_date)
               AND Trunc (SYSDATE) BETWEEN Trunc (papd.start_date) AND
                                           Trunc (papd.end_date)
               AND Trunc (SYSDATE) BETWEEN Trunc (papf.effective_start_date) AND
                                           Trunc (papf.effective_end_date)
               AND Trunc (SYSDATE) BETWEEN Trunc (paaf.effective_start_date) AND
                                           Trunc (paaf.effective_end_date)
        ORDER  BY pay_rule.payroll_name)payroll_name
		
WHERE  papf.person_id = ppnf.person_id(+)
       AND papf.person_id = asg_act.person_id(+)
       AND bu.organization_id(+) = asg_act.business_unit_id
       AND asg_act.organization_id = dept.organization_id(+)
       AND asg_act.person_type_id = pptvl.person_type_id(+)
       AND ppos.person_id(+) = asg_act.person_id
       AND ppos.period_of_service_id(+) = asg_act.period_of_service_id
       AND apaed.person_id(+) = papf.person_id
       AND apaed.work_term_asg_id(+) = asg_act.work_terms_assignment_id
       AND apaed.assignment_id(+) = asg_act.assignment_id
       AND apaed.pl_id = aapf.absence_plan_id(+) 
       AND apae.person_id(+) = papf.person_id
	   AND papf.person_id = appe.person_id(+)
	   AND appe.plan_id = aapf.absence_plan_id
       AND asg_act.position_id = hap.position_id(+) --data may not exist
       AND asg_act.job_id = pj.job_id(+) --data may not exist
       AND pldf.location_id (+) = asg_act.location_id
       --To get all employee records
       AND pg.grade_id (+) = asg_act.grade_id
       AND hou.organization_id(+) = asg_act.legal_entity_id
	   AND assignment_category.c2(+) = asg_act.employment_category
	   AND payroll_name.person_id(+) = papf.person_id
	   AND ppos.date_start = (SELECT MAX(ppos1.date_start)FROM per_periods_of_service ppos1,
														per_all_assignments_f asg_act1, 
														per_all_people_f papf1
								WHERE papf1.person_id = asg_act1.person_id
								AND asg_act1.person_id = ppos1.person_id
								AND asg_act1.period_of_service_id = ppos1.period_of_service_id
								AND papf1.person_id = papf.person_id
								AND Upper (asg_act1.primary_flag) = Upper ('Y') -- should fetch only primary assignment
								AND asg_act1.assignment_status_type = 'ACTIVE'
								AND Upper (asg_act1.effective_latest_change) = Upper ('Y') ---If required add this to get Active employees only---
								AND Upper (asg_act1.assignment_type) = Upper ('E') --should fetch this assignment type
								AND Trunc(Nvl(ppos1.actual_termination_date,sysdate)) BETWEEN Trunc(Nvl(asg_act1.effective_start_date,sysdate))AND Trunc(Nvl(asg_act1.effective_end_date,sysdate))
								AND Trunc(Nvl(ppos1.actual_termination_date,sysdate)) BETWEEN Trunc(Nvl(papf1.effective_start_date,sysdate)) AND Trunc(Nvl(papf1.effective_end_date,sysdate)))							  
		
       AND ppnf.name_type = 'GLOBAL'
       AND Upper (asg_act.primary_flag) = Upper ('Y')
       AND Upper (asg_act.effective_latest_change) = Upper ('Y')
       AND asg_act.assignment_type = 'E' -------to Pick the Employee type only
       AND asg_act.assignment_status_type = 'ACTIVE' ---ACTIVE STATUS
       AND aapf.LANGUAGE (+) = 'US' -- FOR SOURCE LANG
       AND aapf.source_lang (+) = 'US' -- FOR SOURCE LANG
	   AND Apaed.type IN ('ACRL','ORA_ANC_ROLLOVER','COVR')
	   AND appe.status='A'
	   
	
       AND Trunc(SYSDATE) BETWEEN Trunc(
           Nvl(asg_act.effective_start_date, SYSDATE))AND
                                      Trunc(
                                      Nvl(asg_act.effective_end_date, SYSDATE))
       AND Trunc(SYSDATE) BETWEEN Trunc(Nvl(papf.effective_start_date, SYSDATE))
                                  AND
Trunc(Nvl(papf.effective_end_date, SYSDATE))
AND Trunc(SYSDATE) BETWEEN Trunc(Nvl(ppnf.effective_start_date, SYSDATE)) AND
Trunc(Nvl(ppnf.effective_end_date, SYSDATE))
AND Trunc(SYSDATE) BETWEEN Trunc(Nvl(aapf.effective_start_date, SYSDATE)) AND
Trunc(Nvl(aapf.effective_end_date, SYSDATE))
AND Trunc(SYSDATE) BETWEEN Trunc(Nvl(appe.enrt_st_dt,SYSDATE))AND
Trunc(Nvl(appe.enrt_end_dt,SYSDATE))
AND Trunc(SYSDATE) BETWEEN Trunc(Nvl(hap.effective_start_date, SYSDATE))AND
Trunc(Nvl(hap.effective_end_date(+), SYSDATE))
-------------------------------------------Parameters---------------------------------  
AND ( papf.person_number IN ( :p_employee_number )
OR 'ALL' IN ( :p_employee_number
|| 'ALL' ) )
AND ( dept.name IN ( :p_department )
OR 'ALL' IN ( :p_department
|| 'ALL' ) )
AND ( hap.position_code IN ( :p_position )
OR 'ALL' IN ( :p_position
|| 'ALL' ) )
AND ( pj.job_code IN ( :p_job )
OR 'ALL' IN ( :p_job
|| 'ALL' ) )
AND ( pldf.internal_location_code IN ( :p_location )
OR 'ALL' IN ( :p_location
|| 'ALL' ) )
AND ( pg.grade_code IN ( :p_grade )
OR 'ALL' IN ( :p_grade
|| 'ALL' ) )
AND ( bu.name IN ( :P_Business_Unit )
OR 'ALL' IN ( 'ALL'
||:P_Business_Unit ) )
AND ( hou.name IN ( :P_Legal_Entity_Name )
OR 'ALL' IN ( 'ALL'
||:P_Legal_Entity_Name ) )
AND ( assignment_category.c1 IN ( :P_Assignment_Category )
OR 'ALL' IN ( 'ALL'
||:P_Assignment_Category ) )
GROUP  BY papf.person_number,
          papf.person_id,
          ppnf.display_name,
          bu.name,
          dept.name,
          pptvl.user_person_type,
          asg_act.employment_category,
          asg_act.assignment_status_type,
          ppos.date_start,
          ppos.actual_termination_date,
          aapf.name,
		  aapf.absence_plan_id,
          payroll_name.payroll_name,
		  assignment_category.c1,
asg_act.assignment_id
ORDER  BY papf.person_number