Oracle Payroll Queries

SELECT   prr.run_result_id,

         element_name,

         classification_name,

         units,

         TO_NUMBER (result_value) result_value,

         paf.assignment_id,

         DECODE (classification_name,

                 'Earnings', '1',

                 'Voluntary Deductions', '2',

                 'Information', '5'

                ) class_id,

         DECODE (element_name,

                 'Basic Salary', '1',

                 'Social Insurance Company Share', '7',

                 '2'

                ) elm_id,

         pav.period_name,

         pav.TYPE

    FROM pay_run_results prr,

         pay_assignment_actions_v pav,

         per_all_assignments_f paf,

         per_all_people_f pap,

         pay_run_result_values_v prv,

         pay_element_types_f ppt,

         pay_element_classifications pec

   WHERE prr.assignment_action_id = pav.assignment_action_id

     AND paf.assignment_id = pav.assignment_id

     AND prr.run_result_id = prv.run_result_id

     AND prr.element_type_id = ppt.element_type_id

     AND pec.classification_id = ppt.classification_id

     AND UPPER (TRIM (prv.NAME)) = 'PAY VALUE'

     AND pap.person_id = paf.person_id

     AND TO_NUMBER (TO_CHAR (pap.effective_end_date, 'RRRR')) > 4000

     AND TO_NUMBER (TO_CHAR (paf.effective_end_date, 'RRRR')) > 4000

--AND ELEMENT_NAME <> 'Tax'

     AND UPPER (TRIM (ppt.element_name)) NOT IN

                       ('SOCIAL INSURANCE', 'SOCIAL INSURANCE COMPANY SHARE')

     AND UPPER (pec.classification_name) IN

                              (UPPER ('Voluntary Deductions'), 'INFORMATION')

----

UNION

----

SELECT   prr.run_result_id,

         DECODE (prv.NAME,

                 'Basic', 'Basic Social',

                 'Variable', 'Variable Social '

                ) NAME,

         classification_name,

         prv.units,

         TO_NUMBER (prv.result_value) result_value,

         paf.assignment_id,

         '4' class_id,

         DECODE (prv.NAME, 'Basic', '4', 'Variable', '5', '99') elm_id,

         pav.period_name,

         pav.TYPE NAME

    FROM pay_run_results prr,

         pay_assignment_actions_v pav,

         per_all_assignments_f paf,

         per_all_people_f pap,

         pay_run_result_values_v prv,

         pay_element_types_f ppt,

         pay_element_classifications pec

   WHERE prr.assignment_action_id = pav.assignment_action_id

     AND paf.assignment_id = pav.assignment_id

     AND pap.person_id = paf.person_id

     AND prr.element_type_id = ppt.element_type_id

     AND pec.classification_id = ppt.classification_id

     AND TO_NUMBER (TO_CHAR (pap.effective_end_date, 'RRRR')) > 4000

     AND TO_NUMBER (TO_CHAR (paf.effective_end_date, 'RRRR')) > 4000

     AND UPPER (TRIM (ppt.element_name)) IN ('SOCIAL INSURANCE')

     AND UPPER (classification_name) IN (UPPER ('Voluntary Deductions'))

     AND prr.run_result_id = prv.run_result_id

     AND UPPER (prv.NAME) IN ('BASIC', 'VARIABLE')

ORDER BYclass_id,

         elm_id

------------------------------------------------------------------------------------

SELECT pap.effective_start_date,

       employee_number,

       first_name || ' ' || middle_names || ' ' || last_name full_name,

       bg.NAME company,

       hou.NAME branch,

       ppg.group_name department,

       pj.NAME job,

       paa.assignment_id,

       pap.business_group_id,

       pap.attribute14,

       ass_attribute1

  FROM per_all_people_f pap,

       per_all_assignments_f paa,

       pay_people_groups ppg,

       per_jobs pj,

       hr_all_organization_units hou,

       hr_all_organization_units bg

 WHERE pap.person_id = paa.person_id

   AND TO_NUMBER (TO_CHAR (pap.effective_end_date, 'RRRR')) > 4000

   AND TO_NUMBER (TO_CHAR (paa.effective_end_date, 'RRRR')) > 4000

   AND paa.people_group_id = ppg.people_group_id

   AND paa.job_id = pj.job_id

   AND paa.organization_id = hou.organization_id

   AND paa.business_group_id = bg.business_group_id

   AND bg.business_group_id = bg.organization_id        

---------------------------------------------------------------------------------------  

SELECT result_value social_co_val,

       assignment_id,

       pav.period_name,

       pav.TYPE

  FROM pay_run_results prr,

       pay_assignment_actions_v pav,

       pay_element_types_f ppt,

       pay_element_classifications pec,

       pay_run_result_values rrv

 WHERE prr.assignment_action_id = pav.assignment_action_id

   AND prr.element_type_id = ppt.element_type_id

   AND pec.classification_id = ppt.classification_id

   AND prr.run_result_id = rrv.run_result_id

   AND element_name IN ('Social Insurance Company Share')

-----------------------------------------------------------------------------------  

SELECT prr.run_result_id,

       element_name,

       classification_name,

       units,

       TO_NUMBER (result_value) result_value,

       paf.assignment_id,

       DECODE (classification_name,

               'Earnings', '1',

               'Voluntary Deductions', '2',

               'Information', '5'

              ) class_id,

       DECODE (element_name,

               'Basic Salary', '1',

               'Social Insurance Company Share', '7',

               '2'

              ) elm_id,

       pav.period_name,

       pav.TYPE

  FROM pay_run_results prr,

       pay_assignment_actions_v pav,

       per_all_assignments_f paf,

       per_all_people_f pap,

       pay_run_result_values_v prv,

       pay_element_types_f ppt,

       pay_element_classifications pec

 WHERE prr.assignment_action_id = pav.assignment_action_id

   AND paf.assignment_id = pav.assignment_id

   AND pap.person_id = paf.person_id

   AND prr.run_result_id = prv.run_result_id

   AND prr.element_type_id = ppt.element_type_id

   AND pec.classification_id = ppt.classification_id

   AND UPPER (TRIM (prv.NAME)) = 'PAY VALUE'

   AND (pap.employee_number = :v_employee_number OR :v_employee_number IS NULL )

   AND pap.business_group_id = :v_business_group

   AND TO_NUMBER (TO_CHAR (pap.effective_end_date, 'RRRR')) > 4000

   AND TO_NUMBER (TO_CHAR (paf.effective_end_date, 'RRRR')) > 4000

   AND UPPER (classification_name) NOT IN (UPPER ('Voluntary Deductions'))

  

----------------------------------------------------------------------------------   --

SELECT pap.effective_start_date,

       employee_number,

       first_name || ' ' || middle_names || ' ' || last_name full_name,

       bg.NAME company,

       hou.NAME branch,

       ppg.group_name department,

       paa.assignment_id,

       ass_attribute1,

       pap.business_group_id,

       pap.attribute14,

       pj.NAME job_name

  FROM per_all_people_f pap,

       per_all_assignments_f paa,

       pay_people_groups ppg,

       hr_all_organization_units hou,

       hr_all_organization_units bg,

       per_jobs pj

 WHERE pap.person_id = paa.person_id

   AND (pap.employee_number = :v_employee_number OR :v_employee_number IS NULL

       )

   AND TO_NUMBER (TO_CHAR (pap.effective_end_date, 'RRRR')) > 4000

   AND TO_NUMBER (TO_CHAR (paa.effective_end_date, 'RRRR')) > 4000

   AND pj.job_id = paa.job_id

   AND paa.people_group_id = ppg.people_group_id

   AND paa.organization_id = hou.organization_id

   AND paa.business_group_id = bg.business_group_id

   AND bg.business_group_id = bg.organization_id

  

----------------------------------------------------------------------------------   --

SELECT   SUM (TO_NUMBER (result_value)) gross_sal,

         assignment_id,

         TYPE,

         period_name

    FROM (SELECT prr.run_result_id,

                 rrv.result_value,

                 assignment_id,

                 pav.TYPE,

                 pav.period_name

            FROM pay_run_results prr,

                 pay_assignment_actions_v pav,

                 pay_element_types_f ppt,

                 pay_element_classifications pec,

                 pay_run_result_values rrv

           WHERE prr.assignment_action_id = pav.assignment_action_id

             AND prr.element_type_id = ppt.element_type_id

             AND pec.classification_id = ppt.classification_id

             AND prr.run_result_id = rrv.run_result_id

             AND pec.classification_name IN ('Earnings')

             AND (   (    ppt.element_name NOT IN

                             ('Capital Loan', 'Bonus', 'Profits',

                              'Profits with Tax', 'Travel Allowance', 'Tax',

                              'Social Insurance', 'SB')

                      AND pav.business_group_id NOT IN

                                                    (406, 425, 155, 821, 825)

                     )

                  OR (    ppt.element_name NOT IN

                             ('Capital Loan', 'Bonus', 'Profits',

                              'Profits with Tax', 'Travel Allowance', 'Tax',

                              'Social Insurance', 'SB', 'Social Increase')

                      AND pav.business_group_id IN (406, 425, 155, 821, 825)

                     )

                  OR (    ppt.element_name = 'Profits with Tax'

                      AND pav.business_group_id IN (401, 183, 361, 512)

                     )

                 ))

GROUP BYassignment_id,

         TYPE,

         period_name

        

----------------------------------------------------------------------------------