Monday, June 24, 2013

HRMS Incomes Query



   SELECT ppeo.EMPLOYEE_NUMBER,
          ppeo.FULL_NAME NAME,
          ety.ELEMENT_NAME,
          pact.Effective_DATE,
          NVL (TO_NUMBER (rrv.RESULT_VALUE), 0)
     FROM pay_run_results rrs,
          pay_run_result_values rrv,
          pay_element_types_f ety,
          pay_element_classifications cla,
          pay_assignment_actions asact,
          pay_payroll_actions pact,
          per_all_assignments_f assm,
          per_all_people_f ppeo,
          pay_input_values_f inv
    WHERE rrs.RUN_RESULT_ID = rrv.RUN_RESULT_ID
          AND rrs.ELEMENT_TYPE_ID = ety.ELEMENT_TYPE_ID
          -- AND rrv.RESULT_VALUE NOT IN ('0')
          -- and  ety.ELEMENT_NAME like '%Abse%'
          -- AND cla.CLASSIFICATION_NAME IN ('Earnings')
          AND ety.CLASSIFICATION_ID = cla.CLASSIFICATION_ID
          AND rrs.ASSIGNMENT_ACTION_ID = asact.ASSIGNMENT_ACTION_ID
          AND TO_NUMBER (pact.PAYROLL_ACTION_ID) = TO_NUMBER (asact.PAYROLL_ACTION_ID)
          AND asact.ASSIGNMENT_ID = assm.ASSIGNMENT_ID
          AND assm.EFFECTIVE_END_DATE > SYSDATE
          AND assm.PERSON_ID = ppeo.PERSON_ID
          AND ppeo.EFFECTIVE_END_DATE > SYSDATE
          AND inv.EFFECTIVE_END_DATE > SYSDATE
          AND rrv.INPUT_VALUE_ID = inv.INPUT_VALUE_ID        
          AND  pact.Effective_DATE between '' and ''
          AND inv.name like '%%'

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect