SELECT 
    pap.person_number,
    ppn.first_name
    ||' '
    ||ppn.last_name full_name,
	paaf.assignment_number,
	papf.PAYROLL_NAME,
	ptp.PERIOD_NAME,
	TO_CHAR (to_date(ptp.start_date,'YYYY-MM-DD'),'DD-MON-YYYY') period_start_date,
	TO_CHAR (to_date(ptp.end_date,'YYYY-MM-DD'),'DD-MON-YYYY') period_end_date,
	petf.base_element_name,
	to_number(NVL(prrv.result_value,0)) Pay_value,
    asg.PARENT_REL_GROUP_ID,
	ppa.action_type,
	ppa.earn_time_period_id,
    paaf.assignment_id,
	ptp.time_period_id
  FROM 
    per_all_assignments_f paaf ,
    PER_ALL_PEOPLE_F pap ,
	PER_PERSON_NAMES_F ppn,
	PAY_REL_GROUPS_DN asg,
	PAY_ASSIGNED_PAYROLLS_DN papd,
	pay_all_payrolls_f papf,
	pay_payroll_rel_actions  ppra,
	pay_payroll_actions      ppa,
	PAY_RUN_RESULTS prr,
	PAY_TIME_PERIODS ptp,
	PAY_ELEMENT_TYPES_F petf,
	PAY_RUN_RESULT_VALUES prrv,
	PAY_INPUT_VALUES_F pivf
  WHERE 1=1
  AND pap.person_number=NVL(:p_person_number,pap.person_number)
  AND ppa.date_earned BETWEEN ppn.effective_start_date AND ppn.effective_end_date
  AND ppa.date_earned BETWEEN pap.effective_start_date AND pap.effective_end_date
  AND ppa.date_earned BETWEEN paaf.effective_start_date AND paaf.effective_end_date
  AND ppa.date_earned BETWEEN petf.effective_start_date AND petf.effective_end_date
  AND ppa.date_earned between papf.EFFECTIVE_START_DATE and papf.EFFECTIVE_END_DATE
  AND paaf.person_id     = pap.person_id
  AND pap.person_id=ppn.person_id
  AND paaf.assignment_type='E'
  AND ppn.NAME_TYPE = 'GLOBAL'
  AND paaf.assignment_id = asg.assignment_id
  AND asg.PARENT_REL_GROUP_ID = papd.payroll_term_id
  AND papd.payroll_id = papf.payroll_id
  AND asg.payroll_relationship_id=ppra.payroll_relationship_id
  AND ppra.source_action_id IS NOT NULL
  AND ppra.action_status='C'
  AND ppra.payroll_rel_action_id = prr.payroll_rel_action_id
  AND papd.payroll_term_id = prr.payroll_term_id
  AND papf.payroll_id = ptp.payroll_id
  AND ptp.payroll_id = ppa.payroll_id
  AND ppa.payroll_action_id=ppra.payroll_action_id
  AND ppa.action_type IN ('R','Q')
  AND ppa.earn_time_period_id = ptp.time_period_id
  AND petf.element_type_id =prr.element_type_id
  AND prr.run_result_id        = prrv.run_result_id
  AND prrv.input_value_id      = pivf.input_value_id
  AND pivf.base_name                ='Pay Value'
  AND ptp.period_category='E'
  AND ptp.time_period_id =NVL(:p_time_period_id,ptp.time_period_id)