SELECT person_number,
           full_name,
	   assignment_number,
	   PAYROLL_NAME,
	   base_element_name,
	   element_start_date,
	   element_end_date,
	   Transport_Type,
	   start_date,
	   end_date,
	   Allowance_City,
	   Purpose,
	   Periodicity,
	   Pay_Value
 FROM 
(
SELECT 
    pap.person_number,
    ppn.first_name
    ||' '
    ||ppn.last_name full_name,
	paaf.assignment_number,
	papf.PAYROLL_NAME,
	ptp.PERIOD_NAME,
    petf.base_element_name,
	TO_CHAR (to_date(peef.effective_start_date,'YYYY-MM-DD'),'DD-MON-YYYY') element_start_date,
	TO_CHAR (to_date(peef.effective_end_date,'YYYY-MM-DD'),'DD-MON-YYYY') element_end_date,
	---to_number(NVL(prrv.result_value,0)) Pay_value,
	pivf.base_name entry_name, 
	peevf.screen_entry_value entry_value,
    asg.PARENT_REL_GROUP_ID,
	ppa.action_type,
	ppa.earn_time_period_id,
    paaf.assignment_id,
	ptp.time_period_id,
	petf.element_type_id,
	prr.run_result_id,
	(select to_number(NVL(prrv.result_value,0)) 
	 from 
	 PAY_RUN_RESULT_VALUES prrv,
	 PAY_INPUT_VALUES_F pivf_rr
	 WHERE prrv.run_result_id=prr.run_result_id
	 AND prrv.input_value_id=pivf_rr.input_value_id
	 AND pivf_rr.base_name                ='Pay Value'
	 )Pay_Value
  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,
	pay_element_entries_f peef,
	pay_element_entry_values_f peevf
  WHERE 1=1
  AND pap.person_number=NVL(:P_person_number,pap.person_number)---10001212
  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 ppa.date_earned BETWEEN peef.effective_start_date AND peef.effective_end_date
  AND ppa.date_earned between peevf.EFFECTIVE_START_DATE and peevf.EFFECTIVE_END_DATE
  AND paaf.person_id     = pap.person_id
  AND pap.person_id=ppn.person_id
  AND ppn.person_id = peef.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 peef.ENTRY_TYPE='E'
  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 to_char(petf.element_type_id)= to_char(peef.element_type_id)
  AND peef.element_entry_id = peevf.element_entry_id
  AND peevf.input_value_id      = pivf.input_value_id
  --AND pivf_rr.base_name                ='Pay Value'
  AND pivf.base_name IN ( 'Amount', 'Start Date', 'End Date', 'Transport Type', 'Purpose','Allowance City', 'Periodicity')
  AND petf.base_element_name ='Transport Allowance'
  AND ptp.time_period_id = NVL(:P_time_period_id,ptp.time_period_id)----300000107753213
ORDER BY
    pap.person_id,
    prr.run_result_id
 ) PIVOT (
                MAX ( entry_value )
                FOR entry_name
                IN ( 'Transport Type'AS Transport_Type, 'Start Date' AS start_date, 'End Date' AS end_date, 'Allowance City' AS Allowance_City,
                'Purpose' AS Purpose, 'Periodicity' AS Periodicity
				    )
		 )