/* $Id$ */
/*============================================================================*
 |                 Copyright (c) 2012 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 FORCE VIEW xxas_tcm_log_entries_v
  AS
  SELECT xtle.log_entry_id
  ,      xtle.assignment_id
  ,      xtpa.person_id
  ,      xtle.start_date_time
  ,      xtle.end_date_time
  ,      xtle.element_type_id
  ,      xtle.duration_hours
  ,      xtle.field1
  ,      xtle.field2
  ,      xtle.field3
  ,      xtle.field4
  ,      xtle.field5
  ,      xtle.cost_allocation_keyflex_id
  ,      xtle.override_approver_role
  ,      xtle.log_entry_details
  ,      xtle.last_updated_by log_entry_last_updated_by
  ,      xtpa.period_id
  ,      xtpa.period_name
  ,      xtpa.start_date    period_start_date
  ,      xtpa.end_date      period_end_date
  ,      xtpa.late_start    period_late_start
  ,      xtpa.early_finish  period_early_finish
  FROM   xxas_tcm_log_entries          xtle
  ,      xxas_tcm_period_assignments_v xtpa
  WHERE  xtle.assignment_id   = xtpa.assignment_id
  AND    xtle.start_date_time < NVL(xtpa.early_finish, xtpa.end_date)
  AND    xtle.end_date_time   > NVL(xtpa.late_start, xtpa.start_date);

COMMIT;
EXIT;
