PAY Earnings Audit

SELECT DISTINCT pcs.consolidation_set_name consolidation_set
,      pcs.consolidation_set_id   consolidation_set_id
,      ptp.start_date             period_start_date 
,      ptp.end_date               period_end_date
,      pap.payroll_name           payroll_name
,      hr_payrolls.display_period_name(ppa.payroll_action_id)        period_name
,      ptp.time_period_id         time_period_id1
,      pap.payroll_id             payroll_id
,      ppa.effective_date         processing_date
FROM   pay_payrolls_f                   pap
,      per_time_periods                     ptp
,      pay_payroll_actions                  ppa
,      pay_consolidation_sets               pcs
WHERE  ( (:P_PAYROLL_ACTION_ID IS NULL
          AND :P_CONSOLIDATION_SET_ID IS NULL
          AND :P_PAYROLL_ID IS NOT NULL
          AND :P_TIME_PERIOD_ID IS NOT NULL
          AND pap.payroll_id = :P_PAYROLL_ID
          AND ppa.payroll_id = pap.payroll_id
          AND ppa.action_type in ('R','Q')
          AND ptp.payroll_id = pap.payroll_id
          AND ppa.date_earned BETWEEN ptp.start_date AND ptp.end_date
          AND ptp.time_period_id = :P_TIME_PERIOD_ID)
        OR 
         (((:P_PAYROLL_ACTION_ID IS NOT NULL
            AND :P_CONSOLIDATION_SET_ID IS NULL
            AND :P_PAYROLL_ID IS NULL
            AND :P_TIME_PERIOD_ID IS NULL
            and ppa.payroll_action_id = :P_PAYROLL_ACTION_ID)
            OR
           (:P_PAYROLL_ACTION_ID IS NULL
            AND :P_CONSOLIDATION_SET_ID IS NOT NULL
            AND :P_PAYROLL_ID IS NULL
            AND :P_TIME_PERIOD_ID IS NULL
            AND ppa.consolidation_set_id = :P_CONSOLIDATION_SET_ID)
            OR
           (:P_PAYROLL_ACTION_ID IS NULL
            AND :P_CONSOLIDATION_SET_ID IS NOT NULL
            AND :P_PAYROLL_ID IS NULL
            AND :P_TIME_PERIOD_ID IS NOT NULL
            AND  ptp.time_period_id = :P_TIME_PERIOD_ID
            AND ppa.consolidation_set_id = :P_CONSOLIDATION_SET_ID))
           AND ppa.action_type in ('R','Q')
           AND pap.payroll_id = ppa.payroll_id
           AND ptp.payroll_id = pap.payroll_id 
           AND ppa.date_earned BETWEEN                ptp.start_date and ptp.end_date ))
AND    ppa.effective_date between pap.effective_start_date 
                              and pap.effective_end_date
AND    pcs.consolidation_set_id = ppa.consolidation_set_id
AND EXISTS (SELECT *
FROM   
      pay_run_results             prr
,      pay_element_types_f         pet
,      pay_element_classifications pec
,      per_assignments_f           pas
,      pay_assignment_actions      paa
WHERE    paa.payroll_action_id = ppa.payroll_action_id
AND    prr.assignment_action_id = paa.assignment_action_id
AND    pas.assignment_id = paa.assignment_id
AND    paa.tax_unit_id =  nvl(:P_TAX_UNIT_ID, paa.tax_unit_id)
AND    ppa.date_earned BETWEEN pas.effective_start_date
                              AND pas.effective_end_date
AND    pet.element_type_id = prr.element_type_id
AND    pet.element_type_id = nvl(:P_ELEMENT_TYPE_ID, pet.element_type_id)
AND    pet.classification_id = pec.classification_id
AND    pec.classification_name IN ( 'Earnings'
                                   ,'Supplemental Earnings'
                                   ,'Imputed Earnings' )
AND    pec.legislation_code = 'US' 
AND    ppa.effective_date BETWEEN pet.effective_start_date
                              AND pet.effective_end_date)
ORDER  BY pap.payroll_name, ppa.effective_date