PAY STP W1 and W2 Reporting

Description
Categories: BI Publisher
Imported from BI Publisher
Application: Payroll
Source: STP W1 and W2 Reporting
Short Name: PYAUSTPWRPT_XML
DB package:
Run PAY STP W1 and W2 Reporting and other Oracle EBS reports with Blitz Report™ on our demo environment
select fnd_date.date_to_displaydate(to_date(pyevnt.X_PAID_UPDATE_DATE, 'YYYY-MM-DD')) SUBMISSION_DATE,
         sum(pay_au_stp_archive.get_archive_value('X_STP_PERIOD_GROSS_PAYMENT',paa.assignment_action_id)) W1,
         sum(pay_au_stp_archive.get_archive_value('X_STP_PERIOD_TOTAL_INB_PAYGW',paa.assignment_action_id)) W2
from pay_au_stp_payevnt_v pyevnt, 
        pay_payroll_actions ppa,
        pay_payroll_actions ppar,
        pay_assignment_actions paa,
        pay_assignment_actions paar,
        pay_action_interlocks pai
where pyevnt.REGISTERED_EMPLOYER = :LE_ID
and to_date(pyevnt.X_PAID_UPDATE_DATE, 'YYYY-MM-DD') between :P_START_DATE and :P_END_DATE
and pay_core_utils.get_parameter ('PAYROLL',ppa.legislative_parameters) = :PAYROLL_ID
and pyevnt.payroll_action_id = ppa.payroll_action_id
and paa.payroll_action_id = ppa.payroll_action_id
and pai.locked_action_id = paa.assignment_action_id
and pai.locking_action_id = paar.assignment_action_id
and paar.payroll_action_id = ppar.payroll_action_id
and ppar.action_type = 'X'
and ppar.action_status = 'C'
and ppar.report_type ='AU_STP_REPORT'
and pay_core_utils.get_parameter ('MODE',ppar.legislative_parameters) = 'P'
and pay_core_utils.get_parameter ('REPLACE',ppar.legislative_parameters) = 'N'
and paar.action_status = 'C'
and paa.action_status = 'C'
having (sum(pay_au_stp_archive.get_archive_value('X_STP_PERIOD_GROSS_PAYMENT',paa.assignment_action_id)) > 0 or sum(pay_au_stp_archive.get_archive_value('X_STP_PERIOD_TOTAL_INB_PAYGW',paa.assignment_action_id)) > 0)
group by fnd_date.date_to_displaydate(to_date(pyevnt.X_PAID_UPDATE_DATE, 'YYYY-MM-DD'))
Parameter Name SQL text Validation
Start Date
 
Date
End Date
 
Date
Legal Employer
 
LOV Oracle
Payroll
 
LOV Oracle