select    person_number,full_name,pay_period,
        nvl(Basic_Salary,0) as "Basic Salary",
		nvl(Transport_Allowance,0) as "Transport Allowance"
FROM 
( 
WITH periods AS
  (SELECT ptp.start_date,
	TO_CHAR (to_date(ptp.start_date,'YYYY-MM-DD'),'MON-YYYY') Pay_period,
    COUNT(*) OVER () AS count_period
  FROM   
  PAY_TIME_PERIODS ptp,
  pay_all_payrolls_f papf
  WHERE ptp.payroll_id=papf.Payroll_id
  AND papf.payroll_name='UK Monthly Payroll'
  AND sysdate between papf.EFFECTIVE_START_DATE and papf.EFFECTIVE_END_DATE
  AND ptp.period_category='E'
  AND to_date(TO_CHAR (to_date(ptp.start_date,'YYYY-MM-DD'),'DD-MON-YYYY'),'DD-MM-YYYY') between to_date('01-12-2024','DD-MM-YYYY') and to_date('28-02-2025','DD-MM-YYYY')
  )
SELECT qry.payroll_name,
  periods.Pay_period,
  qry.person_number,
  qry.full_name,
  qry.element_name,
  TO_CHAR(periods.start_date,'MM') period_mon,
  TO_CHAR(periods.start_date,'YYYY') period_year,
  count_period,
  SUM(
  CASE
    WHEN periods.Pay_period = qry.Pay_period
    AND periods.start_date  =qry.start_date
    THEN qry.Pay_value
    ELSE 1
  END )Pay_value
  FROM
  ( SELECT papf.payroll_name,
    ptp.start_date,
	TO_CHAR (to_date(ptp.start_date,'YYYY-MM-DD'),'MON-YYYY') Pay_period,
    pap.person_number,
    ppn.first_name
    ||' '
    ||ppn.last_name full_name,
    petf.base_element_name element_name,
	to_number(NVL(prrv.result_value,0)) 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
  WHERE 1=1
  AND pap.person_number='10001212'-----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 papf.payroll_name='UK Monthly Payroll'
  AND petf.base_element_name in ('Basic Salary UK','Transport Allowance') -------can add many elements
  AND to_date(TO_CHAR (to_date(ptp.start_date,'YYYY-MM-DD'),'DD-MON-YYYY'),'DD-MM-YYYY') between to_date('01-12-2024','DD-MM-YYYY') and to_date('28-02-2025','DD-MM-YYYY')
  ) qry,periods
group by qry.payroll_name,
  periods.Pay_period,
  qry.person_number,
  qry.full_name,
  qry.element_name,
  TO_CHAR(periods.start_date,'MM') ,
  TO_CHAR(periods.start_date,'YYYY') ,
  count_period
ORDER BY qry.person_number,
  to_number(TO_CHAR(periods.start_date,'MM')),
  to_number(TO_CHAR(periods.start_date,'YYYY')),
  qry.element_name
  )
   PIVOT
    (sum(nvl(Pay_value,0)) for element_name IN ('Basic Salary UK' as Basic_Salary,'Transport Allowance' as Transport_Allowance)----can add many elements 
    ) 
order by to_number(period_year),to_number(period_mon)