WITH payroll AS(
	SELECT DISTINCT prrv.run_result_id,
	pf.person_id,
	ele.element_name element_name,
	TO_NUMBER(prrv.result_value) result_value,
	pec.classification_name class_name,
	ptp.period_name,
	piv.name input_v,
	ppa.effective_date ppa_eff,
	ppa.payroll_action_id,
	payrel.assignment_id,
	prel.payroll_relationship_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=paa.person_id 
		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(piv.name) IN ('PAY VALUE','NET PAY')
		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 ((pp.payroll_id IN (:p_payroll_name)) OR (COALESCE(:p_payroll_name,NULL) IS NULL))
		AND ((ptp.period_name IN (:p_payroll_period_id)) OR  (COALESCE(:p_payroll_period_id, NULL) IS NULL))
		AND ppa.effective_date BETWEEN  ptp.start_date AND  ptp.end_date
		AND ((ppa.action_type IN (:p_Run_Type )) OR (COALESCE(:p_Run_Type,NULL ) IS NULL))
		AND ((ppa.CONSOLIDATION_SET_ID IN (:P_CONS_GROUP )) OR (COALESCE(:p_Cons_Group,NULL ) IS NULL))
		AND ((pf.person_number IN (:p_person_number )) OR (COALESCE(:p_person_number,NULL ) IS NULL))
		AND(coalesce(:P_PAY_FLOW_ID, NULL) IS NULL OR PPA.PAYROLL_ACTION_ID IN(:P_PAY_FLOW_ID))
		
			
		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 ROW_NUMBER() OVER(ORDER BY emp_id )Ser_Num,PERSON_ID,
	emp_id,
	fname Emp_Name,
	Emp_IBAN,
	Emp_Bank,
	Emp_N_ID,
	NVL(Basic_Salary,0) B_Salary,
	NVL(Housing_Allowance,0) H_Allowance,
	NVL(other_e,0) other_e,
	NVL(deductions,0) deductions,	
	NVL(Basic_Salary,0)+NVL(Housing_Allowance,0)+NVL(other_e,0)-NVL(deductions,0) total_S,
	pay_desc,
	Benefitiari,
	mol_estab_id
	FROM (SELECT DISTINCT 
	pf.person_number emp_id,
	pn.full_name  fname,
	CASE  WHEN pn.middle_names IS NOT NULL THEN pn.middle_names ||(' ') END mname,
	pn.last_name lname,
	pba.iban_number Emp_IBAN ,
	pba.BANK_NUMBER Emp_Bank,
	pni.national_identifier_number Emp_N_ID,
	CASE 
		WHEN Upper(payroll.element_name)LIKE '%BASIC SALARY%'THEN 'Basic Salary'
		WHEN Upper(payroll.element_name)LIKE '%HOUSING ALLOWANCE%'THEN 'Housing Allowance'
		END element_name ,
	NVL(payroll.result_value,0) result_value,
	(SELECT SUM(earnings.result_value) FROM payroll earnings
	WHERE UPPER(earnings.element_name) NOT LIKE '%BASIC SALARY%'
	AND UPPER(earnings.element_name) NOT LIKE '%HOUSING ALLOWANCE%'
	AND UPPER(earnings.class_name)='STANDARD EARNINGS'
	AND UPPER(earnings.input_v) ='PAY VALUE'
	AND pf.person_id=earnings.person_id
	AND earnings.payroll_action_id=payroll.payroll_action_id) other_e,
	(SELECT SUM(deduction.result_value) FROM payroll deduction
	WHERE 1=1
	AND UPPER(deduction.class_name) LIKE '%DEDUCTION%'
	AND UPPER(deduction.input_v) ='PAY VALUE'
	AND pf.person_id=deduction.person_id
	AND deduction.payroll_action_id=payroll.payroll_action_id)
	+(NVL((-1)*(SELECT SUM(absences.result_value) FROM payroll absences
		WHERE UPPER(absences.element_name)='SICK LEAVE ENTITLEMENT RESULT'
		AND UPPER(absences.class_name)='ABSENCES'
		AND UPPER(absences.input_v) ='NET PAY'
		AND pf.person_id=absences.person_id 
		AND absences.payroll_action_id=payroll.payroll_action_id),0))deductions, 
	payroll.period_name pay_desc,
	'SA' Benefitiari,
	paa.ass_attribute2 mol_estab_id,
	paa.legal_entity_id leg_id,paa.PERSON_ID,payroll.assignment_id
,pppm.*
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,
	
	per_person_names_f pn,
	pay_pay_relationships_dn prel,
	pay_person_pay_methods_f pppm,
	pay_bank_accounts pba,
	per_national_identifiers pni,
	payroll,
	per_department_secured_list_v leg_entity,
	hr_all_organization_units_vl leg_entity_en
WHERE 1=1
AND paa.ASSIGN_RANK=1
	AND pf.person_id=paa.person_id 
	AND payroll.assignment_id=paa.assignment_id
	AND pf.person_id=pn.person_id
	AND pn.name_type='GLOBAL' 
	AND pf.person_id=prel.person_id(+)
	AND prel.payroll_relationship_id=payroll.payroll_relationship_id
	AND prel.payroll_relationship_id=pppm.payroll_relationship_id(+)
	AND pppm.bank_account_id =pba.bank_account_id(+)
	AND pf.person_id=pni.person_id(+)
	AND pf.primary_nid_id=pni.national_identifier_id(+)
	AND pf.person_id=payroll.person_id
	AND UPPER(payroll.input_v) ='PAY VALUE'
	AND UPPER(payroll.class_name)='STANDARD EARNINGS'
	AND paa.legal_entity_id=leg_entity.organization_id(+)
	AND leg_entity.organization_id = leg_entity_en.organization_id(+)
	
	AND ((paa.legal_entity_id IN (:p_leg_id)) OR (COALESCE(:p_leg_id,NULL) IS NULL))
	AND ((paa.business_unit_id IN (:p_bu_id)) OR (COALESCE(:p_bu_id,NULL) IS NULL))
	AND ((paa.organization_id IN (:p_dep_id)) OR (COALESCE(:p_dep_id,NULL) IS NULL))
	AND ((pppm.org_payment_method_id IN (:p_payment_method)) OR (COALESCE(:p_payment_method,NULL) IS NULL))
	AND ((paa.ass_attribute2 IN (:p_mol_estab_id)) OR (COALESCE(:p_mol_estab_id,NULL) IS NULL))
	AND ((paa.ass_attribute4 IN (:p_estab_id)) OR (COALESCE(:p_estab_id,NULL) IS NULL))
	
	AND ppa_eff BETWEEN pf.effective_start_date AND LAST_DAY(pf.effective_end_date)
	AND ppa_eff BETWEEN pn.effective_start_date AND pn.effective_end_date
	AND ppa_eff  BETWEEN leg_entity.effective_start_date(+) AND leg_entity.effective_end_date(+)
	AND ppa_eff  BETWEEN leg_entity_en.effective_start_date(+) AND leg_entity_en.effective_end_date(+)
	
	
	AND ppa_eff BETWEEN prel.start_date(+) AND prel.end_date(+)
	AND ppa_eff BETWEEN pppm.effective_start_date(+) AND pppm.effective_end_date(+)
	AND ppa_eff BETWEEN pba.start_date(+) AND NVL(pba.end_date(+),SYSDATE)
ORDER BY pf.person_number
	)
PIVOT(
	SUM(result_value)
	FOR element_name IN ('Basic Salary' Basic_Salary,'Housing Allowance' Housing_Allowance)
)