Wednesday, 19 August 2015

SQL Query to get payslip of an employee

/* Formatted on 8/19/2015 6:36:17 PM (QP5 v5.240.12305.39446) */
  SELECT ppa.date_earned,
         per.full_name,
         per.employee_number,
         NVL (pet.reporting_name, pet.element_name),
         piv.NAME,
         prrv.result_value,
         ptp.period_name
    FROM pay_payroll_actions ppa,
         pay_assignment_actions pac,
         per_all_assignments_f ass,
         per_all_people_f per,
         pay_run_results prr,
         pay_element_types_f pet,
         pay_input_values_f piv,
         pay_run_result_values prrv,
         per_time_periods_v ptp
   WHERE     ppa.payroll_action_id = pac.payroll_action_id
         AND pac.assignment_id = ass.assignment_id
         AND ass.effective_end_date = TO_DATE ('12/31/4712', 'MM/DD/RRRR')
         AND ass.person_id = per.person_id
         AND per.effective_end_date = TO_DATE ('12/31/4712', 'MM/DD/RRRR')
         AND pac.assignment_action_id = prr.assignment_action_id
         AND prr.element_type_id = pet.element_type_id
         AND prr.run_result_id = prrv.run_result_id
         AND pet.element_type_id = piv.element_type_id
         AND piv.input_value_id = prrv.input_value_id
         AND ppa.time_period_id = ptp.time_period_id
         AND pet.element_name = 'Basic Salary'
         --     AND piv.NAME = 'Pay Value'
         AND per.employee_number = '91314'
         AND ptp.period_name LIKE '6 2008 Calendar Month'
ORDER BY 1;

1 comment:

  1. Regards
    Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
    LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
    Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, Oracle Manufacturing, BI Publisher, OAF, ADF, SQL, PL/SQL, D2K at sridevikoduru@oracleappstechnical.com | +91 - 9581017828.

    ReplyDelete