Follow us: Subscribe via RSS Feed Connect on YouTube Connect on YouTube

Pages

Sunday, 5 February 2017

Query to get Employee Probation and Notice Period

SELECT   papf_sup.employee_number, pjt.NAME job, pgt.NAME grade,
         paaf.assignment_number, date_probation_end, probation_period,
         notice_period,
         DECODE (probation_unit,
                 'D', 'DAYS',
                 'W', 'WEEKS',
                 'H', 'HOURS',
                 'M', 'MONTHS',
                 'Y', 'YEARS'
                ) probation_unit,
         DECODE (notice_period_uom,
                 'D', 'DAYS',
                 'W', 'WEEKS',
                 'H', 'HOURS',
                 'M', 'MONTHS',
                 'Y', 'YEARS'
                ) notice_period_uom,
         paaf.employment_category, paaf.assignment_type, hl_freq.meaning,
         paaf.effective_start_date, paaf.effective_end_date,
         ppb.NAME salary_basis, papf_sup.full_name supervisor,
         paaf.normal_hours working_hours, hl_freq.meaning frequency,
         paaf.time_normal_finish normal_time_from,
         paaf.time_normal_start normal_time_to, paaf.title billing_title,
         paaf.project_title,
         DECODE (   gcc.segment1
                 || '.'
                 || gcc.segment2
                 || '.'
                 || gcc.segment3
                 || '.'
                 || gcc.segment4
                 || '.'
                 || gcc.segment5,
                 '....', NULL,
                    gcc.segment1
                 || '.'
                 || gcc.segment2
                 || '.'
                 || gcc.segment3
                 || '.'
                 || gcc.segment4
                 || '.'
                 || gcc.segment5
                ) purchase_default_account
    FROM per_all_assignments_f paaf,
         per_jobs_tl pjt,
         per_grades_tl pgt,
         per_pay_bases ppb,
         per_all_people_f papf_sup,
         hr_lookups hl_freq,
         gl_code_combinations gcc
   WHERE 1 = 1
--AND PAPF_SUP.EMPLOYEE_NUMBER = '30987'
     AND paaf.person_id = papf_sup.person_id
     AND paaf.business_group_id = papf_sup.business_group_id
     AND pgt.grade_id(+) = paaf.grade_id
     AND pjt.job_id = paaf.job_id
     AND paaf.pay_basis_id = ppb.pay_basis_id(+)
     AND paaf.frequency = hl_freq.lookup_code
     AND TRUNC (SYSDATE) BETWEEN paaf.effective_start_date
                             AND paaf.effective_end_date
     AND TRUNC (SYSDATE) BETWEEN papf_sup.effective_start_date
                             AND papf_sup.effective_end_date
     AND hl_freq.lookup_type = 'FREQUENCY'
     AND paaf.default_code_comb_id = gcc.code_combination_id(+)
     AND probation_period IS NOT NULL
GROUP BY papf_sup.employee_number,
         pjt.NAME,
         pgt.NAME,
         paaf.assignment_number,
         date_probation_end,
         probation_period,
         notice_period,
         DECODE (probation_unit,
                 'D', 'DAYS',
                 'W', 'WEEKS',
                 'H', 'HOURS',
                 'M', 'MONTHS',
                 'Y', 'YEARS'
                ),
         DECODE (notice_period_uom,
                 'D', 'DAYS',
                 'W', 'WEEKS',
                 'H', 'HOURS',
                 'M', 'MONTHS',
                 'Y', 'YEARS'
                ),
         paaf.employment_category,
         paaf.assignment_type,
         hl_freq.meaning,
         paaf.effective_start_date,
         paaf.effective_end_date,
         ppb.NAME,
         papf_sup.full_name,
         paaf.normal_hours,
         hl_freq.meaning,
         paaf.time_normal_finish,
         paaf.time_normal_start,
         paaf.title,
         paaf.project_title,
         DECODE (   gcc.segment1
                 || '.'
                 || gcc.segment2
                 || '.'
                 || gcc.segment3
                 || '.'
                 || gcc.segment4
                 || '.'
                 || gcc.segment5,
                 '....', NULL,
                    gcc.segment1
                 || '.'
                 || gcc.segment2
                 || '.'
                 || gcc.segment3
                 || '.'
                 || gcc.segment4
                 || '.'
                 || gcc.segment5

                );
Share this article :

0 comments:

Post a Comment