Monday, September 2, 2013

Query to get Unpaid Quickpay in the System

SELECT *
  FROM (SELECT per.employee_number,ppa.effective_date,
               NVL (
                  (SELECT 'Paid'
                     FROM pay_payroll_actions paa1
                    WHERE paa1.target_payroll_action_id =
                             ppa.payroll_action_id
                          AND paa1.action_type = 'U'),
                  'Unpaid')
                  payment_status
          FROM pay_payroll_actions ppa,
               pay_assignment_actions paa,
               per_assignments_x paaf,
               per_people_x per
         WHERE     ppa.payroll_action_id = paa.payroll_action_id
               AND ppa.action_type = 'Q'
               AND paa.assignment_id = paaf.assignment_id
               AND paaf.person_id = per.person_id
               AND paaf.assignment_type = 'E'
               AND paaf.assignment_status_type_id = 1
               AND paaf.primary_flag = 'Y')
 WHERE payment_status = 'Unpaid'

No comments:

Post a Comment