Monday, February 25, 2013

Payroll Query



SELECT a.*,
       NVL (DECODE (classification,
                    'Earnings', rr_val,
                    'Supplemental Earnings', rr_val,
                    'Information', rr_val,
                    'Deductions', rr_val * -1,
                    0
                   ),
            0
           ) rr_val_sign
  FROM (SELECT paa.assignment_id,
               DECODE (NVL (ppa.run_type_id, '63'),
                       '63', 'Standard',
                       '64', 'Main',
                       '61', 'Pay Separately'
                      ) process_type,
               asg.assignment_number,
               NVL (zain_hr_pkg.get_child_count (paa.assignment_id,
                                                 ppa.effective_date
                                                ),
                    0
                   ) no_child,
               zain_abs_pkg.get_entitlement (paa.assignment_id,
                                             ppa.effective_date
                                            ) leave_ent,
               ppa.payroll_id,
               paa.assignment_action_id,
               TO_CHAR (ppa.effective_date, 'Mon-yy') pay_period,
               TO_NUMBER (TO_CHAR (effective_date, 'YYYYMM')) pay_period_sort,
               ppa.action_type,
               ppa.effective_date,
               pet.element_name,
               piv.NAME value_name,
               prv.result_value,
               piv.uom,
               DECODE (piv.uom,
                       'D', TO_CHAR
                                 (fnd_date.canonical_to_date (prv.result_value)
                                 ),
                       prv.result_value
                      ) rr_val_a,
               NVL (DECODE (piv.uom,
                            'M', fnd_number.canonical_to_number
                                                             (prv.result_value),
                            'N', fnd_number.canonical_to_number
                                                             (prv.result_value),
                            0
                           ),
                    0
                   ) rr_val,
               NVL ((SELECT (NVL (rv1.result_value, 0))
                       FROM pay_run_result_values rv1,
                            pay_input_values_f iv1
                      WHERE rv1.input_value_id = iv1.input_value_id
                        AND rv1.run_result_id = prv.run_result_id
                        AND iv1.NAME = 'Normal Overtime Hours'),
                    'NA'
                   ) normal_overtime_hours,
               NVL ((SELECT (NVL (rv1.result_value, 0))
                       FROM pay_run_result_values rv1,
                            pay_input_values_f iv1
                      WHERE rv1.input_value_id = iv1.input_value_id
                        AND rv1.run_result_id = prv.run_result_id
                        AND iv1.NAME = 'Normal Overtime Minutes'),
                    'NA'
                   ) normal_overtime_minutess,
               NVL ((SELECT (NVL (rv1.result_value, 0))
                       FROM pay_run_result_values rv1,
                            pay_input_values_f iv1
                      WHERE rv1.input_value_id = iv1.input_value_id
                        AND rv1.run_result_id = prv.run_result_id
                        AND iv1.NAME = 'Holiday Overtime Hours'),
                    'NA'
                   ) holiday_overtime_hours,
               NVL ((SELECT (NVL (rv1.result_value, 0))
                       FROM pay_run_result_values rv1,
                            pay_input_values_f iv1
                      WHERE rv1.input_value_id = iv1.input_value_id
                        AND rv1.run_result_id = prv.run_result_id
                        AND iv1.NAME = 'Holiday Overtime Minutes'),
                    'NA'
                   ) holiday_overtime_minutes,
               pet.element_type_id element_type_id,
               CASE
                  WHEN pec.classification_name = 'Earnings'
                     THEN 'Earnings'
                  WHEN pec.classification_name = 'Supplemental Earnings'
                     THEN 'Supplemental Earnings'
                  WHEN INSTR (pec.classification_name, 'Deductions') >= 1
                     THEN 'Deductions'
                  WHEN INSTR (pec.classification_name, 'Information') >= 1
                     THEN 'Information'
                  ELSE pec.classification_name
               END classification,
               DECODE (pet.processing_type,
                       'R', 'Recurring',
                       'Non Recurring'
                      ) processing_type_meaning,
               pet.input_currency_code,
               pet.output_currency_code,
                --  prr.assignment_action_id,
               prr.element_entry_id,
               DECODE (ppa.action_type, 'Q', 'Quick Pay', 'R', 'Run')
                                                                     run_type
          FROM pay_payroll_actions ppa,
               pay_assignment_actions paa,
               pay_run_results prr,
               pay_run_result_values prv,
               pay_input_values_f piv,
               pay_element_types_f pet,
               apps.per_assignments_f2 asg,
               pay_element_classifications pec
         WHERE ppa.payroll_action_id = paa.payroll_action_id
           AND paa.assignment_action_id = prr.assignment_action_id
           AND prr.run_result_id = prv.run_result_id
           AND prr.element_type_id = pet.element_type_id
           AND piv.input_value_id = prv.input_value_id
           --   AND paa.action_status = 'C'
           AND ppa.effective_date BETWEEN pet.effective_start_date AND pet.effective_end_date
           AND ppa.payroll_id = 63
           AND ppa.business_group_id = 81
           AND asg.assignment_id = paa.assignment_id
           AND ppa.effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date
           AND pec.classification_id(+) = pet.classification_id
           AND piv.NAME IN ('Pay Value', 'Costed')
           -- and element_name like 'Over%'
           AND UPPER (piv.NAME) NOT LIKE '%FUTURE%') a

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect