/* $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_prev_v
  AS
  SELECT log_entry_id
  ,      fnd_date.canonical_to_date(extractValue(data_action_details, 
                                                '/logEntry/startDateTime'))                start_date_time_prev
  ,      fnd_date.canonical_to_date(extractValue(data_action_details, 
                                                '/logEntry/endDateTime'))                  end_date_time_prev
  ,      TO_NUMBER(extractValue(data_action_details, '/logEntry/elementTypeId'))           element_type_id_prev
  ,      fnd_number.canonical_to_number(extractValue(data_action_details
                                                    ,'/logEntry/durationHours'))           duration_hours_prev
  ,      extractValue(data_action_details, '/logEntry/field1')                             field1_prev
  ,      extractValue(data_action_details, '/logEntry/field2')                             field2_prev
  ,      extractValue(data_action_details, '/logEntry/field3')                             field3_prev
  ,      extractValue(data_action_details, '/logEntry/field4')                             field4_prev
  ,      extractValue(data_action_details, '/logEntry/field5')                             field5_prev
  ,      TO_NUMBER(extractValue(data_action_details, '/logEntry/costAllocationKeyflexId')) cost_allocation_keyflex_idprev
  ,      extractValue(data_action_details, '/logEntry/overrideApproverRole')               override_approver_role_prev
  FROM   (SELECT CASE WHEN xcda.current_state_code  = 'D' AND rank() OVER (PARTITION BY xcda.data_id ORDER BY action_id desc) = 1
                      THEN xcda.data_id
                      WHEN xcda.current_state_code  != 'D' AND rank() OVER (PARTITION BY xcda.data_id ORDER BY action_id desc) = 2
                      THEN xcda.data_id
                      ELSE NULL
                 END log_entry_id
          ,      xcda.data_action_details
          FROM   xxas_com_data_actions_v xcda
          WHERE  xcda.data_type           = 1
          AND    xcda.action_code         = 'S'
          AND    xcda.current_ins_upd_del = 'U')
  WHERE  log_entry_id IS NOT NULL;

COMMIT;
EXIT;
