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

                );