PAY Earnings Audit

Description
Categories: BI Publisher
Columns: Consolidation Set, Consolidation Set Id, Period Start Date, Period End Date, Payroll Name, Period Name, Time Period Id1, Payroll Id, Processing Date ...
Application: Payroll
Source: Earnings Audit Report (XML)
Short Name: PAYRPEAR_XML
DB package: PAY_PAYUSEAR_XMLP_PKG
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
Parameter Name SQL text Validation
Consolidation Set Name
 
LOV Oracle
Payroll Name
 
LOV Oracle
Time Period Name
 
LOV Oracle
Government Reporting Entity
 
LOV Oracle
Earnings Element
 
LOV Oracle
Business Group Name
 
Number
Not_Consolidation_Set_ID
 
Not_Payroll_ID