/* $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_training_history_v 
AS
SELECT pep.employee_number
      ,pep.person_id
      ,pep.full_name
      ,gr.name grade
      ,org.name org_name
      ,evt.title course_name
      ,evt.course_start_date
      ,evt.course_end_date
      ,evt.duration
      ,evt.duration_units
      ,evt.event_id
      ,oot.offering_id
      ,evt.activity_version_id
      ,sts.name enrollment_status
      ,prv.name training_provider
      ,trn_loc.town_or_city training_city
FROM   per_all_people_f pep
      ,per_all_assignments_f asg
      ,ota_delegate_bookings tdb
      ,ota_events evt
      ,hr_all_organization_units org
      ,ota_booking_status_types sts
      ,hr_all_organization_units prv
      ,per_grades gr
      ,hr_locations_all trn_loc
      ,ota_offerings_tl oot
WHERE  pep.person_id              = asg.person_id
AND    pep.person_id              = tdb.delegate_person_id
AND    tdb.delegate_assignment_id = asg.assignment_id
AND    tdb.event_id               = evt.event_id
AND    evt.parent_offering_id     = oot.offering_id
AND    oot.language               = userenv('LANG')
AND    gr.grade_id(+)             = asg.grade_id
AND    evt.location_id            = trn_loc.location_id(+)
AND    evt.training_center_id     = prv.organization_id(+)
AND    sts.booking_status_type_id = tdb.booking_status_type_id
AND    org.organization_id        = asg.organization_id
AND    trunc(sysdate) BETWEEN
       pep.effective_start_date AND pep.effective_end_date
AND    trunc(sysdate) BETWEEN
       asg.effective_start_date AND asg.effective_end_date
and    oot.offering_id IS NOT NULL;

BEGIN
  xxas_fnd_dictionary_pkg.add_view
   (p_application_short_name => xxas_util_pkg.app
   ,p_view_name              => 'XXAS_TRAINING_HISTORY_V'
   ,p_description            => 'Training History');
END;
/

COMMIT;
EXIT;
