/* $Id$ */
/*============================================================================*
 |                 Copyright (c) 2013 Applaud Solutions UK Ltd                |
 |                             All rights reserved                            |
 *===========================================================================*/

SET VERIFY OFF
SET FEEDBACK OFF
WHENEVER OSERROR  EXIT FAILURE ROLLBACK
WHENEVER SQLERROR EXIT FAILURE ROLLBACK

CREATE OR REPLACE VIEW xxas_assignment_history_v
AS
SELECT *
FROM (SELECT paaf.assignment_id
            ,paaf.assignment_number
            ,paaf.organization_id
            ,paaf.position_id
            ,paaf.location_id
            ,paaf.pay_basis_id
            ,paaf.job_id
            ,paaf.business_group_id
            ,paaf.grade_id
            ,paaf.payroll_id
            ,paaf.object_version_number
            ,paaf.normal_hours
            ,paaf.employment_category assignment_category
            ,lag(paaf.normal_hours, 1) OVER (PARTITION BY paaf.assignment_id ORDER BY paaf.effective_start_date) AS normal_hours_prev
            ,paaf.time_normal_start
            ,paaf.time_normal_finish
            ,null transaction_id
            ,lag(paaf.assignment_number, 1) OVER (PARTITION BY paaf.assignment_id ORDER BY paaf.effective_start_date) AS assignment_number_prev
            ,paaf.effective_start_date start_date
            ,lag(paaf.effective_start_date, 1) OVER (PARTITION BY paaf.assignment_id ORDER BY paaf.effective_start_date) AS start_date_prev
            ,decode
               (paaf.effective_end_date
               ,to_date('31/12/4712','DD/MM/RRRR')
               ,to_date(NULL)
               ,paaf.effective_end_date) end_date
            ,lag(paaf.effective_end_date, 1) OVER (PARTITION BY paaf.assignment_id ORDER BY paaf.effective_start_date) AS end_date_prev
            ,nvl(asta.user_status, ast.user_status) assignment_status
            ,lag(nvl(asta.user_status, ast.user_status), 1) OVER (PARTITION BY paaf.assignment_id ORDER BY paaf.effective_start_date) AS assignment_status_prev
            ,decode
              (paaf.assignment_type
              ,'C', hr_general.decode_lookup('CWK_ASSIGN_REASON', paaf.change_reason)
              ,'A', hr_general.decode_lookup('APL_ASSIGN_REASON', paaf.change_reason)
              ,hr_general.decode_lookup('EMP_ASSIGN_REASON', paaf.change_reason)) change_reason
            ,sup.person_id supervisor_id
            ,xxas_util_pkg.get_formatted_name
               (sup.full_name, sup.global_name, sup.local_name
               ,sup.first_name, sup.last_name, sup.known_as) supervisor
            ,lag
              (xxas_util_pkg.get_formatted_name
               (sup.full_name, sup.global_name, sup.local_name
               ,sup.first_name, sup.last_name, sup.known_as)
              ,1) OVER (PARTITION BY paaf.assignment_id ORDER BY paaf.effective_start_date) AS supervisor_prev
             ,bg.name business_group
            ,hr_util_misc_ss.getObjectName('JOB', paaf.job_id, papf.business_group_id, job.name) job
            ,lag
              (hr_util_misc_ss.getObjectName('JOB', paaf.job_id, papf.business_group_id, job.name)
              ,1) OVER (PARTITION BY paaf.assignment_id ORDER BY paaf.effective_start_date) AS job_prev
            ,hr_general.decode_position_latest_name
              (paaf.position_id, paaf.effective_start_date) position
            ,lag
              (hr_general.decode_position_latest_name(paaf.position_id, paaf.effective_start_date)
              ,1) OVER (PARTITION BY paaf.assignment_id ORDER BY paaf.effective_start_date) AS position_prev
            ,hr_util_misc_ss.getObjectName
              ('GRADE', paaf.grade_id, papf.business_group_id, grade.name) grade
            ,lag
              (hr_util_misc_ss.getObjectName('GRADE', paaf.grade_id, papf.business_group_id, grade.name)
              ,1) OVER (PARTITION BY paaf.assignment_id ORDER BY paaf.effective_start_date) AS grade_prev
            ,dept.name department
            ,lag(dept.name) OVER (PARTITION BY paaf.assignment_id ORDER BY paaf.effective_start_date) AS department_prev
            ,loc.location_code location
            ,lag(loc.location_code) OVER (PARTITION BY paaf.assignment_id ORDER BY paaf.effective_start_date) AS location_prev
            ,ppg.group_name people_group
            ,lag(ppg.group_name) OVER (PARTITION BY paaf.assignment_id ORDER BY paaf.effective_start_date) AS people_group_prev
            ,papf.original_date_of_hire hire_date
            ,nvl(pps.date_start, pop.date_start) latest_hire_date
            ,nvl(pps.actual_termination_date, pop.actual_termination_date) termdate
            ,decode
              (paaf.assignment_type
              ,'C', hr_general.decode_lookup('HR_CWK_TERMINATION_REASONS', pop.termination_reason)
              ,hr_general.decode_lookup('LEAV_REAS', pps.leaving_reason)) term_reason
            ,decode
              (papf.rehire_recommendation
              ,'N', null
              ,hr_general.decode_lookup('YES_NO', papf.rehire_recommendation)) rehire
            ,decode
              (paaf.assignment_type
              ,'C', hr_general.decode_lookup('CWK_ASG_CATEGORY', paaf.employment_category)
              ,hr_general.decode_lookup('EMP_CAT', paaf.employment_category)) employment_category
            ,lag
              (decode
                (paaf.assignment_type
                ,'C', hr_general.decode_lookup('CWK_ASG_CATEGORY', paaf.employment_category)
                ,hr_general.decode_lookup('EMP_CAT', paaf.employment_category)))
              OVER (PARTITION BY paaf.assignment_id ORDER BY paaf.effective_start_date) AS employment_category_prev
            ,papf.email_address
            ,payroll.payroll_name payroll
            ,lag(payroll.payroll_name) OVER (PARTITION BY paaf.assignment_id ORDER BY paaf.effective_start_date) AS payroll_prev
            ,paaf.normal_hours work_hours
            ,lag(paaf.normal_hours) OVER (PARTITION BY paaf.assignment_id ORDER BY paaf.effective_start_date) AS work_hours_prev
            ,paaf.person_id
            ,hr_person_type_usage_info.get_user_person_type
              (paaf.effective_end_date, papf.person_id) person_type
            ,paaf.primary_flag
            ,paaf.ass_attribute_category
            ,paaf.ass_attribute1 
            ,paaf.ass_attribute2 
            ,paaf.ass_attribute3 
            ,paaf.ass_attribute4 
            ,paaf.ass_attribute5 
            ,paaf.ass_attribute6 
            ,paaf.ass_attribute7 
            ,paaf.ass_attribute8 
            ,paaf.ass_attribute9 
            ,paaf.ass_attribute10 
            ,paaf.ass_attribute11 
            ,paaf.ass_attribute12 
            ,paaf.ass_attribute13 
            ,paaf.ass_attribute14 
            ,paaf.ass_attribute15 
            ,paaf.ass_attribute16 
            ,paaf.ass_attribute17 
            ,paaf.ass_attribute18 
            ,paaf.ass_attribute19 
            ,paaf.ass_attribute20 
            ,paaf.ass_attribute21 
            ,paaf.ass_attribute22 
            ,paaf.ass_attribute23 
            ,paaf.ass_attribute24 
            ,paaf.ass_attribute25 
            ,paaf.ass_attribute26 
            ,paaf.ass_attribute27 
            ,paaf.ass_attribute28 
            ,paaf.ass_attribute29 
            ,paaf.ass_attribute30 
            ,papf.effective_start_date period_start_date
            ,pps.attribute_category
            ,pps.attribute1
            ,pps.attribute2
            ,pps.attribute3
            ,pps.attribute4
            ,pps.attribute5
            ,pps.attribute6
            ,pps.attribute7
            ,pps.attribute8
            ,pps.attribute9
            ,pps.attribute10
            ,pps.attribute11
            ,pps.attribute12
            ,pps.attribute13
            ,pps.attribute14
            ,pps.attribute15
            ,pps.attribute16
            ,pps.attribute17
            ,pps.attribute18
            ,pps.attribute19
            ,pps.attribute20
            ,pop.attribute_category pop_attribute_category
            ,pop.attribute1 pop_attribute1
            ,pop.attribute2 pop_attribute2
            ,pop.attribute3 pop_attribute3
            ,pop.attribute4 pop_attribute4
            ,pop.attribute5 pop_attribute5
            ,pop.attribute6 pop_attribute6
            ,pop.attribute7 pop_attribute7
            ,pop.attribute8 pop_attribute8
            ,pop.attribute9 pop_attribute9
            ,pop.attribute10 pop_attribute10
            ,pop.attribute11 pop_attribute11
            ,pop.attribute12 pop_attribute12
            ,pop.attribute13 pop_attribute13
            ,pop.attribute14 pop_attribute14
            ,pop.attribute15 pop_attribute15
            ,pop.attribute16 pop_attribute16
            ,pop.attribute17 pop_attribute17
            ,pop.attribute18 pop_attribute18
            ,pop.attribute19 pop_attribute19
            ,pop.attribute20 pop_attribute20
            ,paaf.assignment_type
            ,hr_general.decode_lookup('YES_NO', paaf.primary_flag) prim_flag_display
            ,papf.rehire_reason
            ,loc.derived_locale
            ,hr_general.decode_lookup('FREQUENCY', paaf.frequency) work_frequency
            ,lag(hr_general.decode_lookup('FREQUENCY', paaf.frequency))
              OVER (PARTITION BY paaf.assignment_id ORDER BY paaf.effective_start_date) AS work_frequency_prev
            ,fx.concatenated_segments agreement_grade
            ,pps.adjusted_svc_date adjusted_service_date
      FROM   per_all_assignments_f paaf
            ,per_assignment_status_types ast
            ,per_assignment_status_types_tl astl
            ,per_ass_status_type_amends asta
            ,per_ass_status_type_amends_tl astatl
            ,per_all_people_f papf
            ,per_periods_of_service pps
            ,per_periods_of_placement pop
            ,per_all_people_f sup
            ,per_jobs_tl job
            ,hr_all_positions_f_tl pos
            ,hr_locations_all_vl loc
            ,per_grades_tl grade
            ,pay_all_payrolls_f payroll
            ,hr_all_organization_units_tl dept
            ,hr_all_organization_units_tl bg
            ,pay_people_groups ppg
            ,per_cagr_grades_def fx
      WHERE paaf.person_id = papf.person_id
      AND   ast.assignment_status_type_id = astl.assignment_status_type_id
      AND   astl.language = userenv('LANG')
      AND   asta.ass_status_type_amend_id = astatl.ass_status_type_amend_id (+)
      AND   decode(astatl.ass_status_type_amend_id,NULL,'1',astatl.language) =
            decode(astatl.ass_status_type_amend_id,NULL,'1',userenv('LANG'))
      AND   paaf.assignment_status_type_id = ast.assignment_status_type_id
      and   paaf.assignment_status_type_id = asta.assignment_status_type_id (+)
      and   paaf.business_group_id = asta.business_group_id (+)
      AND   paaf.assignment_type IN ('E', 'C')
      AND   nvl(papf.current_employee_flag, nvl(papf.current_npw_flag,'N')) = 'Y'
      AND   paaf.assignment_status_type_id = ast.assignment_status_type_id(+)
      AND   astl.language(+) = userenv('LANG')
      AND   paaf.period_of_service_id = pps.period_of_service_id(+)
      AND   paaf.period_of_placement_date_start = pop.date_start(+)
      AND   paaf.person_id = pop.person_id(+)
      AND   paaf.effective_start_date BETWEEN
            papf.effective_start_date AND papf.effective_end_date
      AND   paaf.effective_start_date BETWEEN
            sup.effective_start_date(+) AND sup.effective_end_date(+)
      AND   paaf.supervisor_id  = sup.person_id(+)
      AND   paaf.people_group_id = ppg.people_group_id(+)
      AND   paaf.payroll_id = payroll.payroll_id(+)
      AND   paaf.effective_start_date BETWEEN
            payroll.effective_start_date(+) AND payroll.effective_end_date(+)
      AND   paaf.job_id = job.job_id(+)
      AND   job.language(+) = userenv('LANG')
      AND   paaf.position_id = pos.position_id(+)
      AND   pos.language(+) = userenv('LANG')
      AND   paaf.location_id = loc.location_id(+)
      AND   paaf.grade_id = grade.grade_id(+)
      AND   grade.language(+) = userenv('LANG')
      AND   paaf.organization_id   = dept.organization_id(+)
      AND   dept.language(+) = userenv('LANG')
      AND   paaf.business_group_id = bg.organization_id(+)
      AND   bg.language(+) = userenv('LANG')
      AND   fx.cagr_grade_def_id(+) = paaf.cagr_grade_def_id
    ) qrslt
WHERE start_date <= SYSDATE
ORDER BY start_date DESC;

BEGIN
  xxas_fnd_dictionary_pkg.add_view
   (p_application_short_name => xxas_util_pkg.app
   ,p_view_name              => 'XXAS_ASSIGNMENT_HISTORY_V'
   ,p_description            => 'Applaud Assignment History');
END;
/

COMMIT;
EXIT;
