Monday, February 25, 2013

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 BY class_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 BY assignment_id,
         TYPE,
         period_name
        
----------------------------------------------------------------------------------        


No comments:

Post a Comment

Best Blogger TipsGet Flower Effect